Subversion Repositories SmartDukaan

Rev

Rev 17167 | Rev 17169 | Go to most recent revision | Details | Compare with Previous | Last modification | View Log | RSS feed

Rev Author Line No. Line
14450 amit.gupta 1 
'''
2 
Created on Mar 13, 2015
3 
 
4 
@author: amit
5 
'''
6 
from datetime import date, datetime, timedelta
14632 amit.gupta 7 
from dtr.main import Store, sourceMap
14650 amit.gupta 8 
from dtr.reports.amazonreco import generateAmazonReco
15445 amit.gupta 9 
from dtr.storage import Mysql, DataService
15932 amit.gupta 10 
from dtr.storage.DataService import Users, Clicks, FlipkartOrders,\
11 
    All_user_addresses
14613 amit.gupta 12 
from dtr.storage.Mysql import getOrdersAfterDate, getOrdersByTag
16947 amit.gupta 13 
from dtr.utils import utils
15423 amit.gupta 14 
from elixir import *
15440 amit.gupta 15 
from sqlalchemy.sql.expression import func, func, or_
14450 amit.gupta 16 
from email import encoders
17 
from email.mime.base import MIMEBase
18 
from email.mime.multipart import MIMEMultipart
19 
from email.mime.text import MIMEText
20 
from pymongo.mongo_client import MongoClient
14460 amit.gupta 21 
from xlrd import open_workbook
14524 amit.gupta 22 
from xlutils.copy import copy
14460 amit.gupta 23 
from xlwt.Workbook import Workbook
17137 naman 24 
import _mysql
14450 amit.gupta 25 
import MySQLdb
26 
import smtplib
27 
import time
28 
import xlwt
16317 amit.gupta 29 
from dtr.utils import utils
17001 amit.gupta 30 
from dtr.utils.utils import toTimeStamp
17137 naman 31 
from dtr.reports import getdata
14460 amit.gupta 32 
#from xlwt import
14450 amit.gupta 33 
 
15445 amit.gupta 34 
DataService.initialize()
14450 amit.gupta 35 
client = MongoClient('mongodb://localhost:27017/')
36 
 
37 
SENDER = "cnc.center@shop2020.in"
38 
PASSWORD = "5h0p2o2o"
39 
SUBJECT = "DTR User Segmentation report for " + date.today().isoformat()
40 
SMTP_SERVER = "smtp.gmail.com"
41 
SMTP_PORT = 587
42 
 
14647 amit.gupta 43 
XLS_FILENAME = "snapdealaffiliatereco.xls"
14632 amit.gupta 44 
XLS_O_FILENAME = "allorders.xls"
14647 amit.gupta 45 
XLS_F_FILENAME = "flipkartaffiliatereco.xls"
14650 amit.gupta 46 
XLS_A_FILENAME = "amazonaffiliatereco.xls"
15791 manish.sha 47 
XLS_SC_FILENAME = "shopcluesaffiliatereco.xls"
17013 manish.sha 48 
XLS_HS_FILENAME = "homeshopaffiliatereco.xls"
14460 amit.gupta 49 
boldStyle = xlwt.XFStyle()
50 
f = xlwt.Font()
51 
f.bold = True
52 
boldStyle.font = f
53 
i = -1
14616 amit.gupta 54 
 
55 
datetime_format = xlwt.XFStyle()
56 
datetime_format.num_format_str = 'dd/mm/yyyy HH:MM AM/PM'
15540 amit.gupta 57 
categoryMap = {3:"Mobiles", 5:"Tablets"}
14450 amit.gupta 58 
def generateFlipkartReco():
14508 amit.gupta 59 
    global i
60 
    i = -1
14460 amit.gupta 61 
    curs = getOrdersAfterDate(datetime.now() - timedelta(days=30), 2)
62 
    db = client.Dtr
14647 amit.gupta 63 
    wb = xlwt.Workbook()
64 
    #rb = open_workbook(XLS_F_FILENAME)
65 
    #wb = copy(rb)
14460 amit.gupta 66 
    #wb = xlwt.Workbook()
67 
    row = 0
14647 amit.gupta 68 
    row2 = 0
69 
    row3 = 0
70 
    row4 = 0
71 
    worksheet = wb.add_sheet("All Orders")
72 
    worksheet1 = wb.add_sheet("Orders Reconciled")
73 
    worksheet2 = wb.add_sheet("Orders not Reconciled")
74 
    worksheet3 = wb.add_sheet("Aff not Reconciled")
14460 amit.gupta 75 
    worksheet.write(row, inc(), 'Order Id', boldStyle)
14647 amit.gupta 76 
    worksheet1.write(row, i, 'Order Id', boldStyle)
77 
    worksheet2.write(row, i, 'Order Id', boldStyle)
14460 amit.gupta 78 
    worksheet.write(row, inc(), 'User Id', boldStyle)
14647 amit.gupta 79 
    worksheet1.write(row, i, 'User Id', boldStyle)
80 
    worksheet2.write(row, i, 'User Id', boldStyle)
14474 amit.gupta 81 
    worksheet.write(row, inc(), 'Username', boldStyle)
14647 amit.gupta 82 
    worksheet1.write(row, i, 'Username', boldStyle)
83 
    worksheet2.write(row, i, 'Username', boldStyle)
14771 amit.gupta 84 
    worksheet.write(row, inc(), 'Version code', boldStyle)
85 
    worksheet1.write(row, i, 'Version code', boldStyle)
86 
    worksheet2.write(row, i, 'Version code', boldStyle)
87 
    worksheet.write(row, inc(), 'Device', boldStyle)
88 
    worksheet1.write(row, i, 'Device', boldStyle)
89 
    worksheet2.write(row, i, 'Device', boldStyle)
14460 amit.gupta 90 
    worksheet.write(row, inc(), 'Merchant Order Id', boldStyle)
14647 amit.gupta 91 
    worksheet1.write(row, i, 'Merchant Order Id', boldStyle)
92 
    worksheet2.write(row, i, 'Merchant Order Id', boldStyle)
14460 amit.gupta 93 
    worksheet.write(row, inc(), 'Product Title', boldStyle)
14647 amit.gupta 94 
    worksheet1.write(row, i, 'Product Title', boldStyle)
95 
    worksheet2.write(row, i, 'Product Title', boldStyle)
14460 amit.gupta 96 
    worksheet.write(row, inc(), 'Price', boldStyle)
14647 amit.gupta 97 
    worksheet1.write(row, i, 'Price', boldStyle)
98 
    worksheet2.write(row, i, 'Price', boldStyle)
14460 amit.gupta 99 
    worksheet.write(row, inc(), 'Quantity', boldStyle)
14647 amit.gupta 100 
    worksheet1.write(row, i, 'Quantity', boldStyle)
101 
    worksheet2.write(row, i, 'Quantity', boldStyle)
14460 amit.gupta 102 
    worksheet.write(row, inc(), 'Status', boldStyle)
14647 amit.gupta 103 
    worksheet1.write(row, i, 'Status', boldStyle)
104 
    worksheet2.write(row, i, 'Status', boldStyle)
14460 amit.gupta 105 
    worksheet.write(row, inc(), 'Detailed Status', boldStyle)
14647 amit.gupta 106 
    worksheet1.write(row, i, 'Detailed Status', boldStyle)
107 
    worksheet2.write(row, i, 'Detailed Status', boldStyle)
14460 amit.gupta 108 
    worksheet.write(row, inc(), 'Cashback Status', boldStyle)
14647 amit.gupta 109 
    worksheet1.write(row, i, 'Cashback Status', boldStyle)
110 
    worksheet2.write(row, i, 'Cashback Status', boldStyle)
14460 amit.gupta 111 
    worksheet.write(row, inc(), 'Our CashBack', boldStyle)
14647 amit.gupta 112 
    worksheet1.write(row, i, 'Our CashBack', boldStyle)
113 
    worksheet2.write(row, i, 'Our CashBack', boldStyle)
114 
    worksheet.write(row, inc(), 'Sale Date', boldStyle)
115 
    worksheet1.write(row, i, 'Sale Date', boldStyle)
116 
    worksheet2.write(row, i, 'Sale Date', boldStyle)
14460 amit.gupta 117 
    worksheet.write(row, inc(), 'SubtagId', boldStyle)
14647 amit.gupta 118 
    worksheet1.write(row, i, 'SubtagId', boldStyle)
119 
    worksheet2.write(row, i, 'SubtagId', boldStyle)
14460 amit.gupta 120 
    worksheet.write(row, inc(), 'Category', boldStyle)
14647 amit.gupta 121 
    worksheet1.write(row, i, 'Category', boldStyle)
14460 amit.gupta 122 
    worksheet.write(row, inc(), 'Aff PayOut', boldStyle)
14647 amit.gupta 123 
    worksheet1.write(row, i, 'Aff PayOut', boldStyle)
14460 amit.gupta 124 
    worksheet.write(row, inc(), 'Aff Sale Amount', boldStyle)
14647 amit.gupta 125 
    worksheet1.write(row, i, 'Aff Sale Amount', boldStyle)
14460 amit.gupta 126 
    worksheet.write(row, inc(), 'Aff Sale Date', boldStyle)
14647 amit.gupta 127 
    worksheet1.write(row, i, 'Aff Sale Date', boldStyle)
14460 amit.gupta 128 
    for row1 in curs:
129 
        orderId = row1[0]
130 
        order = db.merchantOrder.find_one({"orderId":orderId})
131 
        if order is None:
132 
            continue
133 
        saleTime = int(time.mktime(datetime.strptime(order['placedOn'], "%d %B, %Y %I:%M %p").replace(hour=0, minute=0, second=0, microsecond=0).timetuple()))
134 
        #saleTime1 = int(time.mktime((datetime.strptime(order['placedOn'], "%b %d, %Y %I:%M %p") + timedelta(seconds=-60)).timetuple()))
14647 amit.gupta 135 
        reconciled_affiliates = []
14460 amit.gupta 136 
        for subOrder in order['subOrders']:
14647 amit.gupta 137 
            affs = list(db.flipkartOrderAffiliateInfo.find({"productCode":subOrder.get("productCode"), "saleDateInt":saleTime}))
14460 amit.gupta 138 
            row += 1
139 
            i=-1
140 
            worksheet.write(row, inc(), orderId)
141 
            worksheet.write(row, inc(), row1[1])
14474 amit.gupta 142 
            worksheet.write(row, inc(), row1[-1])
14771 amit.gupta 143 
            worksheet.write(row, inc(), row1[-2])
144 
            worksheet.write(row, inc(), row1[-3])
14460 amit.gupta 145 
            worksheet.write(row, inc(), order['merchantOrderId'])
146 
            worksheet.write(row, inc(), subOrder['productTitle'])
147 
            worksheet.write(row, inc(), subOrder['amountPaid'])
148 
            worksheet.write(row, inc(), subOrder['quantity'])
149 
            worksheet.write(row, inc(), subOrder['status'])
150 
            worksheet.write(row, inc(), subOrder['detailedStatus'])
151 
            worksheet.write(row, inc(), subOrder['cashBackStatus'])
152 
            worksheet.write(row, inc(), subOrder['cashBackAmount'])
153 
            worksheet.write(row, inc(), subOrder['placedOn'])
154 
            worksheet.write(row, inc(), order['subTagId'])
155 
            for aff in affs:
14647 amit.gupta 156 
                if aff["_id"] not in reconciled_affiliates and aff['subTagId']== order['subTagId']:
157 
                    reconciled_affiliates.append(aff["_id"])
158 
                    row2 += 1
159 
                    i = -1
160 
                    worksheet1.write(row2, inc(), orderId)
161 
                    worksheet1.write(row2, inc(), row1[1])
162 
                    worksheet1.write(row2, inc(), row1[-1])
14771 amit.gupta 163 
                    worksheet1.write(row2, inc(), row1[-2])
164 
                    worksheet1.write(row2, inc(), row1[-3])
14647 amit.gupta 165 
                    worksheet1.write(row2, inc(), order['merchantOrderId'])
166 
                    worksheet1.write(row2, inc(), subOrder['productTitle'])
167 
                    worksheet1.write(row2, inc(), subOrder['amountPaid'])
168 
                    worksheet1.write(row2, inc(), subOrder['quantity'])
169 
                    worksheet1.write(row2, inc(), subOrder['status'])
170 
                    worksheet1.write(row2, inc(), subOrder['detailedStatus'])
171 
                    worksheet1.write(row2, inc(), subOrder['cashBackStatus'])
172 
                    worksheet1.write(row2, inc(), subOrder['cashBackAmount'])
173 
                    worksheet1.write(row2, inc(), subOrder['placedOn'])
174 
                    worksheet1.write(row2, inc(), order['subTagId'])
175 
                    db.flipkartOrderAffiliateInfo.update(
176 
                        {"productCode":subOrder.get("productCode"), "saleDateInt":saleTime, "subTagId":aff['subTagId']},
177 
                        {"$set":{"reconciled":True}})
178 
                    db.merchantOrder.update({"merchantOrderId":order["merchantOrderId"]},
179 
                                            {"$set":{"reconciled":True}})
14460 amit.gupta 180 
                    worksheet.write(row, inc(), aff['category'])
14647 amit.gupta 181 
                    worksheet1.write(row2, i, aff['category'])
14460 amit.gupta 182 
                    worksheet.write(row, inc(), aff['payOut'])
14647 amit.gupta 183 
                    worksheet1.write(row2, i, aff['payOut'])
14460 amit.gupta 184 
                    worksheet.write(row, inc(), aff['saleAmount'])
14647 amit.gupta 185 
                    worksheet1.write(row2, i, aff['saleAmount'])
14460 amit.gupta 186 
                    worksheet.write(row, inc(), aff['saleDate'])
14647 amit.gupta 187 
                    worksheet1.write(row2, i, aff['saleDate'])
14460 amit.gupta 188 
                    break
14647 amit.gupta 189 
            row3 += 1
190 
            i=-1
191 
            worksheet2.write(row3, inc(), orderId)
192 
            worksheet2.write(row3, inc(), row1[1])
193 
            worksheet2.write(row3, inc(), row1[-1])
14771 amit.gupta 194 
            worksheet2.write(row3, inc(), row1[-2])
195 
            worksheet2.write(row3, inc(), row1[-3])
14647 amit.gupta 196 
            worksheet2.write(row3, inc(), order['merchantOrderId'])
197 
            worksheet2.write(row3, inc(), subOrder['productTitle'])
198 
            worksheet2.write(row3, inc(), subOrder['amountPaid'])
199 
            worksheet2.write(row3, inc(), subOrder['quantity'])
200 
            worksheet2.write(row3, inc(), subOrder['status'])
201 
            worksheet2.write(row3, inc(), subOrder['detailedStatus'])
202 
            worksheet2.write(row3, inc(), subOrder['cashBackStatus'])
203 
            worksheet2.write(row3, inc(), subOrder['cashBackAmount'])
204 
            worksheet2.write(row3, inc(), subOrder['placedOn'])
205 
            worksheet2.write(row3, inc(), order['subTagId'])
206 
 
207 
    i=-1
208 
    worksheet3.write(0, inc(), "category",boldStyle)
209 
    worksheet3.write(0, inc(), "conversionStatus",boldStyle)
210 
    worksheet3.write(0, inc(), "Product Title",boldStyle)
211 
    worksheet3.write(0, inc(), "price",boldStyle)
212 
    worksheet3.write(0, inc(), "quantity",boldStyle)
213 
    worksheet3.write(0, inc(), "payOut",boldStyle)
214 
    worksheet3.write(0, inc(), "saleDate",boldStyle)
215 
    worksheet3.write(0, inc(), "subTagId",boldStyle)
216 
    for aff in db.flipkartOrderAffiliateInfo.find({"reconciled":{"$exists":False}}):
217 
        row4 += 1
218 
        i =-1
219 
        worksheet3.write(row4, inc(), aff["category"])
220 
        worksheet3.write(row4, inc(), aff["conversionStatus"])
221 
        skuData = getSkuData(2, aff["productCode"])
222 
        if skuData is None:
223 
            worksheet3.write(row4, inc(), aff["productTitle"])
224 
        else:
225 
            worksheet3.write(row4, inc(), skuData["product_name"])
226 
        worksheet3.write(row4, inc(), aff['price'])
227 
        worksheet3.write(row4, inc(), aff['quantity'])
228 
        worksheet3.write(row4, inc(), aff['payOut'])
229 
        worksheet3.write(row4, inc(), aff['saleDate'])
230 
        worksheet3.write(row4, inc(), aff['subTagId'])
231 
 
232 
 
233 
 
234 
    wb.save(XLS_F_FILENAME)
14450 amit.gupta 235 
def generateSnapDealReco():
14524 amit.gupta 236 
    global i
14460 amit.gupta 237 
    curs = getOrdersAfterDate(datetime.now() - timedelta(days=30), 3)
14450 amit.gupta 238 
    db = client.Dtr
14487 amit.gupta 239 
    matchedList = []
17168 amit.gupta 240 
 
14450 amit.gupta 241 
    workbook = xlwt.Workbook()
14501 amit.gupta 242 
    worksheet = workbook.add_sheet("Snapdeal Reconciled")
14508 amit.gupta 243 
    worksheet2 = workbook.add_sheet("Snapdeal Orders not reconciled")
17168 amit.gupta 244 
    affNotReconciledSheet = workbook.add_sheet("Snapdeal Aff not reconciled")
16947 amit.gupta 245 
    worksheet3 = workbook.add_sheet("DateWise Sale Summary")
17168 amit.gupta 246 
    worksheet1 = workbook.add_sheet("Snapdeal Reconciled All")
16947 amit.gupta 247 
    setHeaders(worksheet, worksheet1, worksheet2, affNotReconciledSheet, worksheet3)
14450 amit.gupta 248 
    row = 0
15577 amit.gupta 249 
    row5=0
14450 amit.gupta 250 
    for row1 in curs:
251 
        orderId = row1[0]
252 
        order = db.merchantOrder.find_one({"orderId":orderId})
253 
        if order is None:
254 
            continue
14496 amit.gupta 255 
        #saleTime = int(time.mktime(datetime.strptime(order['placedOn'], "%b %d, %Y, %I:%M %p").timetuple()))
16949 amit.gupta 256 
        try:
16951 amit.gupta 257 
            saleTime = datetime.strptime(order['placedOn'], "%a, %d %b, %Y").strftime("%Y-%m-%d")
16949 amit.gupta 258 
        except:
17001 amit.gupta 259 
            saleTime = datetime.strptime(order['placedOn'], "%b %d, %Y, %I:%M %p").strftime("%Y-%m-%d")
16947 amit.gupta 260 
 
14988 amit.gupta 261 
        try:
262 
            formattedTimestamp = order['placedOn']
263 
            timestamp1 = int(time.mktime(datetime.strptime(order['placedOn'], "%b %d, %Y, %I:%M %p").timetuple()))
264 
        except:
265 
            timestamp1 = int(time.mktime(row1[-4].timetuple()))
16947 amit.gupta 266 
            formattedTimestamp = datetime.strftime(utils.fromTimeStamp(timestamp1), "%b %d, %Y, %I:%M %p")
17167 amit.gupta 267 
 
268 
        affs = list(db.snapdealOrderAffiliateInfo1.find({"orderId":order["merchantOrderId"]}))
269 
        if len(affs) > 0:
17168 amit.gupta 270 
            for aff in affs:
271 
                if aff.get("missingOrder"):
272 
                    aff["missingOrder"] = False
273 
                    db.snapdealOrderAffiliateInfo1.save(aff)
17167 amit.gupta 274 
            matchedList.append(order["merchantOrderId"])
14501 amit.gupta 275 
            order['reconciled'] = True
276 
            db.merchantOrder.save(order)
277 
            row += 1
278 
            i=-1
279 
            worksheet.write(row, inc(), orderId)
280 
            worksheet.write(row, inc(), row1[1])
14850 amit.gupta 281 
            worksheet.write(row, inc(), row1[-1])
282 
            worksheet.write(row, inc(), row1[-2])
14771 amit.gupta 283 
            worksheet.write(row, inc(), row1[-3])
14501 amit.gupta 284 
            worksheet.write(row, inc(), order['merchantOrderId'])
285 
            worksheet.write(row, inc(), order['subTagId'])
286 
            worksheet.write(row, inc(), order['placedOn'])
14988 amit.gupta 287 
            worksheet.write(row, inc(), formattedTimestamp)
14501 amit.gupta 288 
            worksheet.write(row, inc(), int(order['paidAmount']))
289 
            k = i
14504 amit.gupta 290 
            row -= 1
14501 amit.gupta 291 
            for subOrder in order['subOrders']:
292 
                i = k
17168 amit.gupta 293 
                matched=False
14503 amit.gupta 294 
                row += 1
17167 amit.gupta 295 
                for aff in affs:
17168 amit.gupta 296 
                    if subOrder['productTitle']==aff['productCode']:
297 
                        worksheet.write(row, inc(), utils.fromTimeStamp(aff['saleDate']),datetime_format)
298 
                        worksheet.write(row, inc(), aff['unitPrice'])
299 
                        worksheet.write(row, inc(), 0 if not aff.get('payOut') else aff['payOut']/aff.get('quantity'))
300 
                        matched=True
301 
                        break
302 
                if not matched:
303 
                    inc()
304 
                    inc()
305 
                    inc()
306 
                worksheet.write(row, inc(), subOrder['productTitle'])
307 
                worksheet.write(row, inc(), subOrder['amountPaid'])
308 
                worksheet.write(row, inc(), subOrder['quantity'])
309 
                worksheet.write(row, inc(), subOrder['status'])
310 
                worksheet.write(row, inc(), subOrder['cashBackStatus'])
311 
                worksheet.write(row, inc(), subOrder['cashBackAmount'])
14504 amit.gupta 312 
 
14501 amit.gupta 313 
        else:
15577 amit.gupta 314 
            row5 += 1
315 
            i=-1
316 
            worksheet2.write(row5, inc(), order["orderId"])
317 
            worksheet2.write(row5, inc(), row1[1])
318 
            worksheet2.write(row5, inc(), row1[-1])
319 
            worksheet2.write(row5, inc(), row1[-2])
320 
            worksheet2.write(row5, inc(), row1[-3])
321 
            worksheet2.write(row5, inc(), order['merchantOrderId'])
322 
            worksheet2.write(row5, inc(), order['subTagId'])
323 
            worksheet2.write(row5, inc(), order['placedOn'])
324 
            worksheet2.write(row5, inc(), formattedTimestamp)
325 
            worksheet2.write(row5, inc(), int(order['paidAmount']))
326 
            row5 -=1
327 
            k=i
328 
            for subOrder in order['subOrders']:
329 
                i = k
15578 amit.gupta 330 
                row5 += 1
15577 amit.gupta 331 
                worksheet2.write(row5, inc(), subOrder['productTitle'])
332 
                worksheet2.write(row5, inc(), subOrder['amountPaid'])
333 
                worksheet2.write(row5, inc(), subOrder['quantity'])
334 
                worksheet2.write(row5, inc(), subOrder['status'])
335 
                worksheet2.write(row5, inc(), subOrder['cashBackStatus'])
15578 amit.gupta 336 
                worksheet2.write(row5, inc(), subOrder['cashBackAmount'])
14524 amit.gupta 337 
 
338 
    last30Days =  date.today() - timedelta(days=30)
339 
    int(time.mktime(last30Days.timetuple()))
340 
    row = 0
17168 amit.gupta 341 
    for offer in db.snapdealOrderAffiliateInfo1.find({"missingOrder":True,
342 
                                                     "saleDate":{"$gte":int(time.mktime(last30Days.timetuple())),
14570 amit.gupta 343 
                                                                 "$lt":int(time.mktime(date.today().timetuple()))}} ):
344 
        row += 1
345 
        i=-1
346 
        affNotReconciledSheet.write(row, inc(), offer.get("subTagId"))
17168 amit.gupta 347 
        affNotReconciledSheet.write(row, inc(), offer.get("orderId"))
348 
        affNotReconciledSheet.write(row, inc(), utils.fromTimeStamp(offer.get("saleDate")), datetime_format)
14570 amit.gupta 349 
        affNotReconciledSheet.write(row, inc(), offer.get("saleAmount"))
17168 amit.gupta 350 
        affNotReconciledSheet.write(row, inc(), offer.get("productCode"))
351 
        affNotReconciledSheet.write(row, inc(), offer.get("quantity"))
14570 amit.gupta 352 
        affNotReconciledSheet.write(row, inc(), offer.get("payOut"))
16947 amit.gupta 353 
 
17010 amit.gupta 354 
    mailBodyTemplate="""
355 
        <html>
356 
            <body>
357 
            <table cellspacing="0" border="1" style="text-align:right">
358 
                <thead>
359 
                    <tr>
360 
                        <th style="text-align:center">Date</th>
361 
                        <th  style="text-align:center">Sale</th>
362 
                        <th  style="text-align:center">Approved</th>
363 
                        <th  style="text-align:center">Approved %</th>
364 
                        <th  style="text-align:center">Rejected</th>
365 
                        <th  style="text-align:center">Rejected %</th>
17033 amit.gupta 366 
                        <th  style="text-align:center">Missing %</th>
367 
                        <th  style="text-align:center">Approved % against Sale</th>
17010 amit.gupta 368 
                    </tr>
369 
                </thead>
370 
                <tbody>
371 
                    {0}
372 
                </tbody>
373 
            </table>
374 
            </body>
375 
        </html>
376 
    """
377 
    tbody=[]
17033 amit.gupta 378 
    rowtemplate="<tr><td>{0}</td><td>{1}</td><td>{2}</td><td>{3}</td><td>{4}</td><td>{5}</td><td>{6}</td><td>{7}</td></tr>"
17010 amit.gupta 379 
    for offer in db.flSaleSnapshot.find().limit(61).sort("_id",-1):
380 
        approved = offer.get("approvedAmount")
381 
        rejected = offer.get("rejectedAmount")
17033 amit.gupta 382 
        sale = offer.get("paidAmount")
17010 amit.gupta 383 
        total = approved + rejected
384 
        if total ==0:
385 
            continue
17033 amit.gupta 386 
        tbody.append(rowtemplate.format(offer.get("_id"), sale, approved, int((approved*100/total)), rejected, int((rejected*100/total)), int((sale-approved-rejected)*100/sale),int((approved)*100/sale)))
17010 amit.gupta 387 
    message = mailBodyTemplate.format("".join(tbody))
15540 amit.gupta 388 
    workbook.save(XLS_FILENAME)
17024 amit.gupta 389 
    return message
17013 manish.sha 390 
 
391 
def generateHomeShopReco():
392 
    global i
393 
    curs = getOrdersAfterDate(datetime.now() - timedelta(days=30), 7)
394 
    db = client.Dtr
395 
    notReconciled = {}
396 
    matchedList = []
397 
    workbook = xlwt.Workbook()
398 
    worksheet = workbook.add_sheet("HomeShop18 Reconciled")
399 
    worksheet1 = workbook.add_sheet("HomeShop18 Reconciled All")
400 
    worksheet2 = workbook.add_sheet("HomeShop18 Orders not reconciled")
401 
    affNotReconciledSheet = workbook.add_sheet("HomeShop18 Aff not reconciled")
402 
    setHeaders(worksheet, worksheet1, worksheet2, affNotReconciledSheet)
403 
    row = 0
404 
    anotherrow = 0
405 
    row2 = 0
406 
    row5=0
407 
 
408 
    for row1 in curs:
409 
        orderId = row1[0]
410 
        order = db.merchantOrder.find_one({"orderId":orderId})
411 
        if order is None:
412 
            continue
413 
        #saleTime = int(time.mktime(datetime.strptime(order['placedOn'], "%b %d, %Y, %I:%M %p").timetuple()))  26 May 2015 02:12 PM
414 
        aff = list(db.shopcluesOrderAffiliateInfo.find({"merchantOrderId":order["merchantOrderId"]}))
415 
        anotherrow += 1
416 
        try:
417 
            formattedTimestamp = order['placedOn']
418 
            timestamp1 = int(time.mktime(datetime.strptime(order['placedOn'], "%d %b %Y %I:%M %p").timetuple()))
419 
        except:
420 
            timestamp1 = int(time.mktime(row1[-4].timetuple()))
421 
            formattedTimestamp = datetime.strftime(utils.fromTimeStamp(timestamp1), "%d %b %Y %I:%M %p")
422 
 
423 
        if len(aff) > 0 and order["subTagId"] not in matchedList and long(order["paidAmount"])==long(float(aff[0]["transactionValue"])):
424 
            matchedList.append(order["subTagId"])
425 
            db.shopcluesOrderAffiliateInfo.update({"merchantOrderId":aff[0].get("merchantOrderId")}, {"$set":{"reconciled":True, "orderId":orderId}}, multi=True)
426 
            order['reconciled'] = True
427 
            order['adId'] = aff[0].get("uniqueKey")
428 
            db.merchantOrder.save(order)
429 
            row += 1
430 
            i=-1
431 
 
432 
            worksheet.write(row, inc(), orderId)
433 
            worksheet1.write(anotherrow, i, orderId)
434 
            worksheet.write(row, inc(), row1[1])
435 
            worksheet1.write(anotherrow, i, row1[1])
436 
            worksheet.write(row, inc(), row1[-1])
437 
            worksheet1.write(anotherrow, i, row1[-1])
438 
            worksheet.write(row, inc(), row1[-2])
439 
            worksheet1.write(anotherrow, i, row1[-2])
440 
            worksheet.write(row, inc(), row1[-3])
441 
            worksheet1.write(anotherrow, i, row1[-3])
442 
            worksheet.write(row, inc(), order['merchantOrderId'])
443 
            worksheet1.write(anotherrow, i, order['merchantOrderId'])
444 
            worksheet.write(row, inc(), order['subTagId'])
445 
            worksheet1.write(anotherrow, i, order['subTagId'])
446 
            worksheet.write(row, inc(), order['placedOn'])
447 
            worksheet1.write(anotherrow, i, order['placedOn'])
448 
            worksheet.write(row, inc(), formattedTimestamp)
449 
            worksheet1.write(anotherrow, i, formattedTimestamp)
450 
            worksheet.write(row, inc(), int(order['paidAmount']))
451 
            worksheet1.write(anotherrow, i, int(order['paidAmount']))
452 
            worksheet.write(row, inc(), datetime.strftime(utils.fromTimeStamp(aff[0]['transactionTime']), "%d %b %Y %I:%M %p"))
453 
            worksheet1.write(anotherrow, i, datetime.strftime(utils.fromTimeStamp(aff[0]['transactionTime']), "%d %b %Y %I:%M %p"))
454 
            worksheet.write(row, inc(), aff[0]['transactionValue'])
455 
            worksheet1.write(anotherrow, i, aff[0]['transactionValue'])
456 
            worksheet.write(row, inc(), aff[0]['payOut'])
457 
            worksheet1.write(anotherrow, i, aff[0]['payOut'])
458 
            worksheet.write(row, inc(), "NA")
459 
            worksheet1.write(anotherrow, i, "NA")
460 
            k = i
461 
            row -= 1
462 
            anotherrow -= 1
463 
            for subOrder in order['subOrders']:
464 
                i = k
465 
                row += 1
466 
                anotherrow += 1
467 
                worksheet.write(row, inc(), subOrder['productTitle'])
468 
                worksheet1.write(anotherrow, i, subOrder['productTitle'])
469 
                worksheet.write(row, inc(), subOrder['amountPaid'])
470 
                worksheet1.write(anotherrow, i, subOrder['amountPaid'])
471 
                worksheet.write(row, inc(), subOrder['quantity'])
472 
                worksheet1.write(anotherrow, i, subOrder['quantity'])
473 
                worksheet.write(row, inc(), subOrder['status'])
474 
                worksheet1.write(anotherrow, i, subOrder['status'])
475 
                worksheet.write(row, inc(), subOrder['cashBackStatus'])
476 
                worksheet1.write(anotherrow, i, subOrder['cashBackStatus'])
477 
                worksheet.write(row, inc(), subOrder['cashBackAmount'])
478 
                worksheet1.write(anotherrow, i, subOrder['cashBackAmount'])
479 
 
480 
        else:
481 
            i=-1
482 
            worksheet1.write(anotherrow, inc(), orderId)
483 
            worksheet1.write(anotherrow, inc(), row1[1])
484 
            worksheet1.write(anotherrow, inc(), row1[-1])
485 
            worksheet1.write(anotherrow, inc(), row1[-2])
486 
            worksheet1.write(anotherrow, inc(), row1[-3])
487 
            worksheet1.write(anotherrow, inc(), order['merchantOrderId'])
488 
            worksheet1.write(anotherrow, inc(), order['subTagId'])
489 
            worksheet1.write(anotherrow, inc(), order['placedOn'])
490 
            worksheet1.write(anotherrow, inc(), formattedTimestamp)
491 
            worksheet1.write(anotherrow, inc(), int(order['paidAmount']))
492 
 
493 
            notReconciled[orderId] = (anotherrow, i, int(order['paidAmount']), timestamp1, order["subTagId"])
494 
            inc()
495 
            inc()
496 
            inc()
497 
            inc()
498 
            k = i
499 
            anotherrow -= 1
500 
            for subOrder in order['subOrders']:
501 
                anotherrow += 1
502 
                i = k
503 
                worksheet1.write(anotherrow, inc(), subOrder['productTitle'])
504 
                worksheet1.write(anotherrow, inc(), subOrder['amountPaid'])
505 
                worksheet1.write(anotherrow, inc(), subOrder['quantity'])
506 
                worksheet1.write(anotherrow, inc(), subOrder['status'])
507 
                worksheet1.write(anotherrow, inc(), subOrder['cashBackStatus'])
508 
                worksheet1.write(anotherrow, inc(), subOrder['cashBackAmount'])
509 
            row5 += 1
510 
            i=-1
511 
            worksheet2.write(row5, inc(), order["orderId"])
512 
            worksheet2.write(row5, inc(), row1[1])
513 
            worksheet2.write(row5, inc(), row1[-1])
514 
            worksheet2.write(row5, inc(), row1[-2])
515 
            worksheet2.write(row5, inc(), row1[-3])
516 
            worksheet2.write(row5, inc(), order['merchantOrderId'])
517 
            worksheet2.write(row5, inc(), order['subTagId'])
518 
            worksheet2.write(row5, inc(), order['placedOn'])
519 
            worksheet2.write(row5, inc(), formattedTimestamp)
520 
            worksheet2.write(row5, inc(), int(order['paidAmount']))
521 
            row5 -=1
522 
            k=i
523 
            for subOrder in order['subOrders']:
524 
                i = k
525 
                row5 += 1
526 
                worksheet2.write(row5, inc(), subOrder['productTitle'])
527 
                worksheet2.write(row5, inc(), subOrder['amountPaid'])
528 
                worksheet2.write(row5, inc(), subOrder['quantity'])
529 
                worksheet2.write(row5, inc(), subOrder['status'])
530 
                worksheet2.write(row5, inc(), subOrder['cashBackStatus'])
531 
                worksheet2.write(row5, inc(), subOrder['cashBackAmount'])
532 
    last30Days =  date.today() - timedelta(days=30)
533 
    int(time.mktime(last30Days.timetuple()))
534 
    row = 0
535 
    for offer in db.homeshopOrderAffiliateInfo.find({"reconciled":{"$exists":False},
536 
                                                     "transactionTime":{"$gt":int(time.mktime(last30Days.timetuple())),
537 
                                                                 "$lt":int(time.mktime(date.today().timetuple()))}} ):
538 
        subTagId = offer.get("subTagId")
539 
        saleTime = offer.get("transactionTime")
540 
        orders = getOrdersByTag(subTagId, 7)
541 
        for order in orders:
542 
            if notReconciled.has_key(order[0]):
543 
                (roww, column, paidAmount, timestamp1, oSubTagId) = notReconciled[order[0]]
544 
                if paidAmount==int(offer.get("transactionValue")) and timestamp1 <= saleTime and timestamp1 + 120 >= saleTime:
545 
                    i = column
546 
                    worksheet1.write(roww, inc(), datetime.strftime(utils.fromTimeStamp(offer.get("transactionTime")), "%d %b %Y %I:%M %p"))
547 
                    worksheet1.write(roww, inc(), offer.get("transactionValue"))
548 
                    worksheet1.write(roww, inc(), offer.get("payOut"))
549 
                    del notReconciled[order[0]]
550 
                    print "found match for user", order[1]
551 
                    db.homeshopOrderAffiliateInfo.update({"transactionId":offer.get("transactionId")}, {"$set":{"reconciled":True, "orderId":order[0]}}, multi=True)
552 
                    db.merchantOrder.update({"orderId":order[0]}, {"$set":{"reconciled":True, "adId":offer.get("uniqueKey")}}, multi=True)
553 
                    break
554 
 
555 
    unreconciledOrders = notReconciled.keys()
556 
 
557 
    for offer in db.homeshopOrderAffiliateInfo.find({"reconciled":{"$exists":False},
558 
                                                     "transactionTime":{"$gt":int(time.mktime(last30Days.timetuple())),
559 
                                                                 "$lt":int(time.mktime(date.today().timetuple()))}} ):
560 
        row += 1
561 
        i=-1
562 
        affNotReconciledSheet.write(row, inc(), offer.get("uniqueKey"))
563 
        affNotReconciledSheet.write(row, inc(), offer.get("subTagId"))
564 
        affNotReconciledSheet.write(row, inc(), datetime.strftime(utils.fromTimeStamp(offer.get("transactionTime")), "%d %b %Y %I:%M %p"))
565 
        affNotReconciledSheet.write(row, inc(), offer.get("transactionValue"))
566 
        affNotReconciledSheet.write(row, inc(), offer.get("payOut"))
567 
        affNotReconciledSheet.write(row, inc(), offer.get("status"))
568 
        affNotReconciledSheet.write(row, inc(), 'NA')
569 
 
570 
    workbook.save(XLS_HS_FILENAME)
571 
 
16995 amit.gupta 572 
 
15791 manish.sha 573 
def generateShopcluesReco():
574 
    global i
16238 manish.sha 575 
    curs = getOrdersAfterDate(datetime.now() - timedelta(days=30), 5)
15791 manish.sha 576 
    db = client.Dtr
577 
    notReconciled = {}
578 
    matchedList = []
579 
    workbook = xlwt.Workbook()
580 
    worksheet = workbook.add_sheet("Shopclues Reconciled")
581 
    worksheet1 = workbook.add_sheet("Shopclues Reconciled All")
582 
    worksheet2 = workbook.add_sheet("Shopclues Orders not reconciled")
583 
    affNotReconciledSheet = workbook.add_sheet("Shopclues Aff not reconciled")
584 
    setHeaders(worksheet, worksheet1, worksheet2, affNotReconciledSheet)
585 
    row = 0
586 
    anotherrow = 0
587 
    row2 = 0
588 
    row5=0
589 
 
590 
    for row1 in curs:
591 
        orderId = row1[0]
592 
        order = db.merchantOrder.find_one({"orderId":orderId})
593 
        if order is None:
594 
            continue
595 
        #saleTime = int(time.mktime(datetime.strptime(order['placedOn'], "%b %d, %Y, %I:%M %p").timetuple()))  26 May 2015 02:12 PM
16204 manish.sha 596 
        aff = list(db.shopcluesOrderAffiliateInfo.find({"merchantOrderId":order["merchantOrderId"]}))
15791 manish.sha 597 
        anotherrow += 1
598 
        try:
599 
            formattedTimestamp = order['placedOn']
600 
            timestamp1 = int(time.mktime(datetime.strptime(order['placedOn'], "%d %b %Y %I:%M %p").timetuple()))
601 
        except:
602 
            timestamp1 = int(time.mktime(row1[-4].timetuple()))
16947 amit.gupta 603 
            formattedTimestamp = datetime.strftime(utils.fromTimeStamp(timestamp1), "%d %b %Y %I:%M %p")
15791 manish.sha 604 
 
605 
        if len(aff) > 0 and order["subTagId"] not in matchedList and long(order["paidAmount"])==long(float(aff[0]["transactionValue"])):
606 
            matchedList.append(order["subTagId"])
16204 manish.sha 607 
            db.shopcluesOrderAffiliateInfo.update({"merchantOrderId":aff[0].get("merchantOrderId")}, {"$set":{"reconciled":True, "orderId":orderId}}, multi=True)
15791 manish.sha 608 
            order['reconciled'] = True
609 
            order['adId'] = aff[0].get("uniqueKey")
610 
            db.merchantOrder.save(order)
611 
            row += 1
612 
            i=-1
14450 amit.gupta 613 
 
15791 manish.sha 614 
            worksheet.write(row, inc(), orderId)
615 
            worksheet1.write(anotherrow, i, orderId)
616 
            worksheet.write(row, inc(), row1[1])
617 
            worksheet1.write(anotherrow, i, row1[1])
618 
            worksheet.write(row, inc(), row1[-1])
619 
            worksheet1.write(anotherrow, i, row1[-1])
620 
            worksheet.write(row, inc(), row1[-2])
621 
            worksheet1.write(anotherrow, i, row1[-2])
622 
            worksheet.write(row, inc(), row1[-3])
623 
            worksheet1.write(anotherrow, i, row1[-3])
624 
            worksheet.write(row, inc(), order['merchantOrderId'])
625 
            worksheet1.write(anotherrow, i, order['merchantOrderId'])
626 
            worksheet.write(row, inc(), order['subTagId'])
627 
            worksheet1.write(anotherrow, i, order['subTagId'])
628 
            worksheet.write(row, inc(), order['placedOn'])
629 
            worksheet1.write(anotherrow, i, order['placedOn'])
630 
            worksheet.write(row, inc(), formattedTimestamp)
631 
            worksheet1.write(anotherrow, i, formattedTimestamp)
632 
            worksheet.write(row, inc(), int(order['paidAmount']))
633 
            worksheet1.write(anotherrow, i, int(order['paidAmount']))
16947 amit.gupta 634 
            worksheet.write(row, inc(), datetime.strftime(utils.fromTimeStamp(aff[0]['transactionTime']), "%d %b %Y %I:%M %p"))
635 
            worksheet1.write(anotherrow, i, datetime.strftime(utils.fromTimeStamp(aff[0]['transactionTime']), "%d %b %Y %I:%M %p"))
15791 manish.sha 636 
            worksheet.write(row, inc(), aff[0]['transactionValue'])
637 
            worksheet1.write(anotherrow, i, aff[0]['transactionValue'])
638 
            worksheet.write(row, inc(), aff[0]['payOut'])
639 
            worksheet1.write(anotherrow, i, aff[0]['payOut'])
16238 manish.sha 640 
            worksheet.write(row, inc(), "NA")
641 
            worksheet1.write(anotherrow, i, "NA")
15791 manish.sha 642 
            k = i
643 
            row -= 1
644 
            anotherrow -= 1
645 
            for subOrder in order['subOrders']:
646 
                i = k
647 
                row += 1
648 
                anotherrow += 1
649 
                worksheet.write(row, inc(), subOrder['productTitle'])
650 
                worksheet1.write(anotherrow, i, subOrder['productTitle'])
651 
                worksheet.write(row, inc(), subOrder['amountPaid'])
652 
                worksheet1.write(anotherrow, i, subOrder['amountPaid'])
653 
                worksheet.write(row, inc(), subOrder['quantity'])
654 
                worksheet1.write(anotherrow, i, subOrder['quantity'])
655 
                worksheet.write(row, inc(), subOrder['status'])
656 
                worksheet1.write(anotherrow, i, subOrder['status'])
657 
                worksheet.write(row, inc(), subOrder['cashBackStatus'])
658 
                worksheet1.write(anotherrow, i, subOrder['cashBackStatus'])
659 
                worksheet.write(row, inc(), subOrder['cashBackAmount'])
660 
                worksheet1.write(anotherrow, i, subOrder['cashBackAmount'])
661 
 
662 
        else:
663 
            i=-1
664 
            worksheet1.write(anotherrow, inc(), orderId)
665 
            worksheet1.write(anotherrow, inc(), row1[1])
666 
            worksheet1.write(anotherrow, inc(), row1[-1])
667 
            worksheet1.write(anotherrow, inc(), row1[-2])
668 
            worksheet1.write(anotherrow, inc(), row1[-3])
669 
            worksheet1.write(anotherrow, inc(), order['merchantOrderId'])
670 
            worksheet1.write(anotherrow, inc(), order['subTagId'])
671 
            worksheet1.write(anotherrow, inc(), order['placedOn'])
672 
            worksheet1.write(anotherrow, inc(), formattedTimestamp)
673 
            worksheet1.write(anotherrow, inc(), int(order['paidAmount']))
674 
 
675 
            notReconciled[orderId] = (anotherrow, i, int(order['paidAmount']), timestamp1, order["subTagId"])
676 
            inc()
677 
            inc()
678 
            inc()
679 
            inc()
680 
            k = i
681 
            anotherrow -= 1
682 
            for subOrder in order['subOrders']:
683 
                anotherrow += 1
684 
                i = k
685 
                worksheet1.write(anotherrow, inc(), subOrder['productTitle'])
686 
                worksheet1.write(anotherrow, inc(), subOrder['amountPaid'])
687 
                worksheet1.write(anotherrow, inc(), subOrder['quantity'])
688 
                worksheet1.write(anotherrow, inc(), subOrder['status'])
689 
                worksheet1.write(anotherrow, inc(), subOrder['cashBackStatus'])
690 
                worksheet1.write(anotherrow, inc(), subOrder['cashBackAmount'])
691 
            row5 += 1
692 
            i=-1
693 
            worksheet2.write(row5, inc(), order["orderId"])
694 
            worksheet2.write(row5, inc(), row1[1])
695 
            worksheet2.write(row5, inc(), row1[-1])
696 
            worksheet2.write(row5, inc(), row1[-2])
697 
            worksheet2.write(row5, inc(), row1[-3])
698 
            worksheet2.write(row5, inc(), order['merchantOrderId'])
699 
            worksheet2.write(row5, inc(), order['subTagId'])
700 
            worksheet2.write(row5, inc(), order['placedOn'])
701 
            worksheet2.write(row5, inc(), formattedTimestamp)
702 
            worksheet2.write(row5, inc(), int(order['paidAmount']))
703 
            row5 -=1
704 
            k=i
705 
            for subOrder in order['subOrders']:
706 
                i = k
707 
                row5 += 1
708 
                worksheet2.write(row5, inc(), subOrder['productTitle'])
709 
                worksheet2.write(row5, inc(), subOrder['amountPaid'])
710 
                worksheet2.write(row5, inc(), subOrder['quantity'])
711 
                worksheet2.write(row5, inc(), subOrder['status'])
712 
                worksheet2.write(row5, inc(), subOrder['cashBackStatus'])
713 
                worksheet2.write(row5, inc(), subOrder['cashBackAmount'])
714 
 
715 
    last30Days =  date.today() - timedelta(days=30)
716 
    int(time.mktime(last30Days.timetuple()))
717 
    row = 0
718 
    for offer in db.shopcluesOrderAffiliateInfo.find({"reconciled":{"$exists":False},
719 
                                                     "transactionTime":{"$gt":int(time.mktime(last30Days.timetuple())),
720 
                                                                 "$lt":int(time.mktime(date.today().timetuple()))}} ):
721 
        subTagId = offer.get("subTagId")
722 
        saleTime = offer.get("transactionTime")
15794 manish.sha 723 
        orders = getOrdersByTag(subTagId, 5)
15791 manish.sha 724 
        for order in orders:
725 
            if notReconciled.has_key(order[0]):
726 
                (roww, column, paidAmount, timestamp1, oSubTagId) = notReconciled[order[0]]
727 
                if paidAmount==int(offer.get("transactionValue")) and timestamp1 <= saleTime and timestamp1 + 120 >= saleTime:
728 
                    i = column
16947 amit.gupta 729 
                    worksheet1.write(roww, inc(), datetime.strftime(utils.fromTimeStamp(offer.get("transactionTime")), "%d %b %Y %I:%M %p"))
15791 manish.sha 730 
                    worksheet1.write(roww, inc(), offer.get("transactionValue"))
731 
                    worksheet1.write(roww, inc(), offer.get("payOut"))
732 
                    del notReconciled[order[0]]
733 
                    print "found match for user", order[1]
734 
                    db.shopcluesOrderAffiliateInfo.update({"transactionId":offer.get("transactionId")}, {"$set":{"reconciled":True, "orderId":order[0]}}, multi=True)
735 
                    db.merchantOrder.update({"orderId":order[0]}, {"$set":{"reconciled":True, "adId":offer.get("uniqueKey")}}, multi=True)
736 
                    break
737 
 
738 
    unreconciledOrders = notReconciled.keys()
739 
 
740 
    for offer in db.shopcluesOrderAffiliateInfo.find({"reconciled":{"$exists":False},
741 
                                                     "transactionTime":{"$gt":int(time.mktime(last30Days.timetuple())),
742 
                                                                 "$lt":int(time.mktime(date.today().timetuple()))}} ):
743 
        row += 1
744 
        i=-1
745 
        affNotReconciledSheet.write(row, inc(), offer.get("uniqueKey"))
746 
        affNotReconciledSheet.write(row, inc(), offer.get("subTagId"))
16947 amit.gupta 747 
        affNotReconciledSheet.write(row, inc(), datetime.strftime(utils.fromTimeStamp(offer.get("transactionTime")), "%d %b %Y %I:%M %p"))
15791 manish.sha 748 
        affNotReconciledSheet.write(row, inc(), offer.get("transactionValue"))
749 
        affNotReconciledSheet.write(row, inc(), offer.get("payOut"))
750 
        affNotReconciledSheet.write(row, inc(), offer.get("status"))
751 
        affNotReconciledSheet.write(row, inc(), 'NA')
752 
 
753 
    workbook.save(XLS_SC_FILENAME)
754 
 
14647 amit.gupta 755 
def sendmail(email, message, title, *varargs):
14450 amit.gupta 756 
    if email == "":
757 
        return
758 
    mailServer = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)
759 
    mailServer.ehlo()
760 
    mailServer.starttls()
761 
    mailServer.ehlo()
762 
 
763 
    # Create the container (outer) email message.
764 
    msg = MIMEMultipart()
765 
    msg['Subject'] = title
766 
    msg.preamble = title
767 
    html_msg = MIMEText(message, 'html')
768 
    msg.attach(html_msg)
769 
 
770 
    #snapdeal more to be added here
14647 amit.gupta 771 
    for fileName in varargs:
772 
        snapdeal = MIMEBase('application', 'vnd.ms-excel')
773 
        snapdeal.set_payload(file(fileName).read())
774 
        encoders.encode_base64(snapdeal)
775 
        snapdeal.add_header('Content-Disposition', 'attachment;filename=' + fileName)
776 
        msg.attach(snapdeal)
14450 amit.gupta 777 
 
778 
 
779 
    email.append('amit.gupta@shop2020.in')
780 
    MAILTO = email
781 
    mailServer.login(SENDER, PASSWORD)
782 
    mailServer.sendmail(PASSWORD, MAILTO, msg.as_string())
783 
 
16958 amit.gupta 784 
def setHeaders(worksheet, worksheet1, worksheet2, affNotReconciledSheet, worksheet3=None):
14505 amit.gupta 785 
    boldStyle = xlwt.XFStyle()
786 
    f = xlwt.Font()
787 
    f.bold = True
788 
    boldStyle.font = f
14501 amit.gupta 789 
    row = 0
790 
    global i
791 
    i=-1
792 
    worksheet.write(row, inc(), 'Order Id', boldStyle)
793 
    worksheet.write(row, inc(), 'User Id', boldStyle)
14850 amit.gupta 794 
    worksheet.write(row, inc(), 'Username', boldStyle)
14601 amit.gupta 795 
    worksheet.write(row, inc(), 'Version Code', boldStyle)
14771 amit.gupta 796 
    worksheet.write(row, inc(), 'Device', boldStyle)
14501 amit.gupta 797 
    worksheet.write(row, inc(), 'Merchant Order Id', boldStyle)
798 
    worksheet.write(row, inc(), 'SubtagId', boldStyle)
799 
    worksheet.write(row, inc(), 'Sale Date', boldStyle)
14988 amit.gupta 800 
    worksheet.write(row, inc(), 'Dtr Sale Date', boldStyle)
14501 amit.gupta 801 
    worksheet.write(row, inc(), 'Sale Amount', boldStyle)
802 
    worksheet.write(row, inc(), 'Aff Sale Date', boldStyle)
803 
    worksheet.write(row, inc(), 'Aff Sale Amt', boldStyle)
804 
    worksheet.write(row, inc(), 'Aff PayOut', boldStyle)
805 
    worksheet.write(row, inc(), 'Product Title', boldStyle)
806 
    worksheet.write(row, inc(), 'Price', boldStyle)
807 
    worksheet.write(row, inc(), 'Quantity', boldStyle)
808 
    worksheet.write(row, inc(), 'Status', boldStyle)
809 
    worksheet.write(row, inc(), 'Cashback Status', boldStyle)
810 
    worksheet.write(row, inc(), 'CashBack', boldStyle)
811 
 
14508 amit.gupta 812 
 
813 
    i = -1
814 
    worksheet2.write(row, inc(), 'Order Id', boldStyle)
815 
    worksheet2.write(row, inc(), 'User Id', boldStyle)
14850 amit.gupta 816 
    worksheet2.write(row, inc(), 'Username', boldStyle)
14605 amit.gupta 817 
    worksheet2.write(row, inc(), 'Version Code', boldStyle)
14771 amit.gupta 818 
    worksheet2.write(row, inc(), 'Device', boldStyle)
14508 amit.gupta 819 
    worksheet2.write(row, inc(), 'Merchant Order Id', boldStyle)
820 
    worksheet2.write(row, inc(), 'SubtagId', boldStyle)
821 
    worksheet2.write(row, inc(), 'Sale Date', boldStyle)
14988 amit.gupta 822 
    worksheet2.write(row, inc(), 'Dtr Sale Date', boldStyle)
14508 amit.gupta 823 
    worksheet2.write(row, inc(), 'Sale Amount', boldStyle)
824 
    worksheet2.write(row, inc(), 'Product Title', boldStyle)
825 
    worksheet2.write(row, inc(), 'Price', boldStyle)
826 
    worksheet2.write(row, inc(), 'Quantity', boldStyle)
827 
    worksheet2.write(row, inc(), 'Status', boldStyle)
828 
    worksheet2.write(row, inc(), 'Cashback Status', boldStyle)
829 
    worksheet2.write(row, inc(), 'CashBack', boldStyle)
14450 amit.gupta 830 
 
14508 amit.gupta 831 
    i =-1
17168 amit.gupta 832 
    affNotReconciledSheet.write(row, inc(), 'Sub Tag Id', boldStyle)
833 
    affNotReconciledSheet.write(row, inc(), 'Order Id', boldStyle)
14508 amit.gupta 834 
    affNotReconciledSheet.write(row, inc(), 'Sale Date', boldStyle)
835 
    affNotReconciledSheet.write(row, inc(), 'Sale Amount', boldStyle)
17168 amit.gupta 836 
    affNotReconciledSheet.write(row, inc(), 'Product', boldStyle)
837 
    affNotReconciledSheet.write(row, inc(), 'Quantity', boldStyle)
14508 amit.gupta 838 
    affNotReconciledSheet.write(row, inc(), 'Pay Out', boldStyle)
14524 amit.gupta 839 
 
16958 amit.gupta 840 
    if worksheet3:
841 
        i =-1
842 
        worksheet3.write(row, inc(), 'Date', boldStyle)
843 
        worksheet3.write(row, inc(), 'Sale Amount', boldStyle)
844 
        worksheet3.write(row, inc(), 'Approved Sales', boldStyle)
845 
        worksheet3.write(row, inc(), 'Rejected Sales', boldStyle)
16947 amit.gupta 846 
 
14524 amit.gupta 847 
def getUserAmountReconciled():
848 
    pass
849 
 
14613 amit.gupta 850 
def getUserOrders():
851 
    global i
16726 amit.gupta 852 
    #worksheet1 = wb.add_sheet("Suspected Users")
17159 amit.gupta 853 
    workbook = xlwt.Workbook("ISO8859-1")
14632 amit.gupta 854 
    sh1 = workbook.add_sheet("All Orders")
15423 amit.gupta 855 
    sh2 = workbook.add_sheet("Flipkart Orders")
17159 amit.gupta 856 
    worksheet = workbook.add_sheet("All Users")
14632 amit.gupta 857 
 
14613 amit.gupta 858 
    db=client.Dtr
17167 amit.gupta 859 
    prevDate = date.today() - timedelta(days=31)
17159 amit.gupta 860 
    results = Mysql.fetchResult("select * from allorder where created_on > %s", prevDate)
14613 amit.gupta 861 
    row = 0
862 
    i=-1
863 
    worksheet.write(row, inc(), 'User Id', boldStyle)
864 
    worksheet.write(row, inc(), 'User name', boldStyle)
865 
    worksheet.write(row, inc(), 'Order Id', boldStyle)
866 
    worksheet.write(row, inc(), 'Created On', boldStyle)
867 
    worksheet.write(row, inc(), 'Store Id', boldStyle)
868 
    worksheet.write(row, inc(), 'Merchant Order Id', boldStyle)
16785 amit.gupta 869 
    worksheet.write(row, inc(), 'SubOrder Id', boldStyle)
14616 amit.gupta 870 
    worksheet.write(row, inc(), 'Product title', boldStyle)
14613 amit.gupta 871 
    worksheet.write(row, inc(), 'Amount Paid', boldStyle)
872 
    worksheet.write(row, inc(), 'Cashback', boldStyle)
873 
    worksheet.write(row, inc(), 'Order Status', boldStyle)
874 
    worksheet.write(row, inc(), 'Detailed Status', boldStyle)
875 
    worksheet.write(row, inc(), 'Cashback Status', boldStyle)
876 
    worksheet.write(row, inc(), 'Reconciled', boldStyle)
877 
    worksheet.write(row, inc(), 'IP', boldStyle)
16718 amit.gupta 878 
    worksheet.write(row, inc(), 'Aff Status', boldStyle)
14693 amit.gupta 879 
    i=-1
880 
    sh1.write(row, inc(), 'User Id', boldStyle)
881 
    sh1.write(row, inc(), 'User name', boldStyle)
882 
    sh1.write(row, inc(), 'Order Id', boldStyle)
883 
    sh1.write(row, inc(), 'Created On', boldStyle)
884 
    sh1.write(row, inc(), 'Store Id', boldStyle)
885 
    sh1.write(row, inc(), 'Merchant Order Id', boldStyle)
14771 amit.gupta 886 
    sh1.write(row, inc(), 'Status', boldStyle)
15932 amit.gupta 887 
    sh1.write(row, inc(), 'Detailed Status', boldStyle)
14693 amit.gupta 888 
    sh1.write(row, inc(), 'Product title', boldStyle)
889 
    sh1.write(row, inc(), 'Referrer', boldStyle)
890 
    sh1.write(row, inc(), 'Amount Paid', boldStyle)
14771 amit.gupta 891 
    sh1.write(row, inc(), 'Catalog Id', boldStyle)
14693 amit.gupta 892 
    sh1.write(row, inc(), 'Brand', boldStyle)
893 
    sh1.write(row, inc(), 'Model', boldStyle)
894 
    sh1.write(row, inc(), 'Category', boldStyle)
15932 amit.gupta 895 
    sh1.write(row, inc(), 'Deal Rank', boldStyle)
16286 amit.gupta 896 
    sh1.write(row, inc(), 'Max Nlc', boldStyle)
897 
    sh1.write(row, inc(), 'Min Nlc', boldStyle)
898 
    sh1.write(row, inc(), 'DP', boldStyle)
899 
    sh1.write(row, inc(), 'Item status', boldStyle)
15932 amit.gupta 900 
    sh1.write(row, inc(), 'City', boldStyle)
901 
    sh1.write(row, inc(), 'State', boldStyle)
902 
    sh1.write(row, inc(), 'Pincode', boldStyle)
17159 amit.gupta 903 
    sh1.write(row, inc(), 'SubOrder Id', boldStyle)
904 
    sh1.write(row, inc(), 'Cashback Status', boldStyle)
905 
    sh1.write(row, inc(), 'Cashback Amount', boldStyle)
15423 amit.gupta 906 
    i =-1
907 
    sh2.write(row, inc(), 'User Id', boldStyle)
908 
    sh2.write(row, inc(), 'User name', boldStyle)
909 
    sh2.write(row, inc(), 'Created On', boldStyle)
910 
    sh2.write(row, inc(), 'Store', boldStyle)
911 
    sh2.write(row, inc(), 'Status', boldStyle)
912 
    sh2.write(row, inc(), 'Product title', boldStyle)
913 
    sh2.write(row, inc(), 'Price', boldStyle)
914 
    sh2.write(row, inc(), 'Quantity', boldStyle)
915 
    sh2.write(row, inc(), 'Catalog Id', boldStyle)
916 
    sh2.write(row, inc(), 'Brand', boldStyle)
917 
    sh2.write(row, inc(), 'Model', boldStyle)
918 
    sh2.write(row, inc(), 'Category', boldStyle)
14613 amit.gupta 919 
 
17159 amit.gupta 920 
    default_format = xlwt.XFStyle()
921 
    date_format = xlwt.XFStyle()
922 
    date_format.num_format_str = 'dd/mm/yyyy'
923 
 
924 
    datetime_format = xlwt.XFStyle()
925 
    datetime_format.num_format_str = 'dd/mm/yyyy HH:MM AM/PM'
926 
 
927 
    number_format = xlwt.XFStyle()
928 
    number_format.num_format_str = '#,##0'
929 
 
930 
 
14613 amit.gupta 931 
    row=0
17159 amit.gupta 932 
    for r in results:
933 
        row += 1
934 
        column = 0
935 
        for data in r :
936 
            sh1.write(row, column, int(data) if type(data) is float else data, datetime_format if type(data) is datetime else default_format)
937 
            column += 1
938 
        i=-1
939 
        worksheet.write(row, inc(), r[0])
940 
        worksheet.write(row, inc(), r[1])
941 
        worksheet.write(row, inc(), r[2])
942 
        worksheet.write(row, inc(), r[3], datetime_format)
943 
        worksheet.write(row, inc(), r[4])
944 
        worksheet.write(row, inc(), r[5])
945 
        worksheet.write(row, inc(), r[23])
946 
        worksheet.write(row, inc(), r[8])
947 
        worksheet.write(row, inc(), r[10])
948 
        worksheet.write(row, inc(), r[25])
949 
        worksheet.write(row, inc(), r[6])
950 
        worksheet.write(row, inc(), r[7])
951 
        worksheet.write(row, inc(), r[24])
15423 amit.gupta 952 
 
15542 amit.gupta 953 
    flipkartOrders = session.query(FlipkartOrders).order_by(FlipkartOrders.created.desc()).all()
15540 amit.gupta 954 
    flipkartOrders.reverse()
15423 amit.gupta 955 
    row3=0
15430 amit.gupta 956 
    #try:
15540 amit.gupta 957 
    for order in flipkartOrders:
15423 amit.gupta 958 
        row3 += 1
959 
        i=-1
15540 amit.gupta 960 
        sh2.write(row3, inc(), order.user_id)
961 
        sh2.write(row3, inc(), order.email)
962 
        sh2.write(row3, inc(), order.created, datetime_format)
15423 amit.gupta 963 
        sh2.write(row3, inc(), "Flipkart")
15540 amit.gupta 964 
        sh2.write(row3, inc(), order.status)
965 
        sh2.write(row3, inc(), order.title)
966 
        sh2.write(row3, inc(), order.price)
967 
        sh2.write(row3, inc(), order.quantity)
968 
        sh2.write(row3, inc(), order.catalogId)
969 
        sh2.write(row3, inc(), order.brand)
970 
        sh2.write(row3, inc(), order.model)
971 
        sh2.write(row3, inc(), order.category)
14632 amit.gupta 972 
 
973 
    workbook.save(XLS_O_FILENAME)
14647 amit.gupta 974 
 
975 
def getSkuData(storeId, identifier):
16492 amit.gupta 976 
    if storeId in (1,2,4,5,6):
14647 amit.gupta 977 
        skuData = client.Catalog.MasterData.find_one({'identifier':identifier, 'source_id':storeId})
978 
    elif storeId == 3:
979 
        skuData = client.Catalog.MasterData.find_one({'secondaryIdentifier':identifier, 'source_id':storeId})
980 
    return skuData
981 
 
14460 amit.gupta 982 
def inc():
983 
    global i
984 
    i+=1
985 
    return i
15540 amit.gupta 986 
 
987 
def migrateFlipkartOrders():
988 
    db = client.Dtr
989 
    info = reversed(list(db.flipkartOrderAffiliateInfo.find()))
990 
    #try:
991 
    for order in info:
992 
        userId = None
993 
        subTagId = None
994 
        email = None
995 
        subTagId = order.get("subTagId")
996 
        if subTagId:
997 
            click = session.query(Clicks).filter_by(tag = subTagId).first()
998 
            if click is not None:
999 
                userId= click.user_id
1000 
                user = session.query(Users.email).filter_by(id = userId).first()
1001 
                if user is not None:
1002 
                    email = user.email
1003 
 
1004 
        flipkartOrder = FlipkartOrders()
1005 
        flipkartOrder.user_id = userId
1006 
        flipkartOrder.identifier = order.get("identifier")
1007 
        flipkartOrder.email = email
1008 
        flipkartOrder.subtagId = order.get("subTagId")
1009 
        flipkartOrder.created = datetime.strptime(order.get("saleDate"), "%Y-%m-%d")
1010 
        flipkartOrder.status = order.get("conversionStatus")
1011 
        flipkartOrder.title = order.get("productTitle")
1012 
        flipkartOrder.price = order.get("price")
1013 
        flipkartOrder.quantity = order.get("quantity")
1014 
        flipkartOrder.productCode = order.get("productCode")
1015 
        skuData = getSkuData(2, order.get("productCode"))
1016 
        if skuData is not None:
1017 
            flipkartOrder.catalogId = skuData.get("skuBundleId")
1018 
            flipkartOrder.brand = skuData.get("brand")
1019 
            flipkartOrder.model = skuData.get("model_name")
1020 
            flipkartOrder.category =categoryMap.get(skuData.get("category_id"))
1021 
            flipkartOrder.title =skuData.get("source_product_name")
1022 
 
1023 
    session.commit()
16947 amit.gupta 1024 
 
1025 
 
1026 
def main1():
1027 
    db = client.Dtr
1028 
    for offer in db.snapdealOrderAffiliateInfo.find({}):
1029 
        print offer.get("adId"), offer.get("saleAmount"), type(offer.get("saleAmount"))
1030 
        #obj = offer.get(saleTime)
1031 
        #obj['paidAmount'] += order["paidAmount"]
14632 amit.gupta 1032 
def addHeaders(sheet):
1033 
    global i
1034 
    i = 0
1035 
    sheet.write(0, inc(), )
14450 amit.gupta 1036 
def main():
17161 amit.gupta 1037 
    message = generateSnapDealReco()
17168 amit.gupta 1038 
    #generateFlipkartReco()
1039 
    #generateShopcluesReco()
1040 
    #generateAmazonReco(XLS_A_FILENAME)
1041 
    #message1 = getdata.summaryByBrandAndStore()
1042 
    #getUserOrders()
1043 
    sendmail(["amit.gupta@shop2020.in"], message, "Affiliate Reco", XLS_FILENAME)
17137 naman 1044 
    #sendmail(["naman.kumar@shop2020.in"], message1, "All DTR Orders", XLS_O_FILENAME)
17168 amit.gupta 1045 
    #sendmail(["amit.gupta@shop2020.in", "rajneesh.arora@saholic.com", "chaitnaya.vats@saholic.com", "manoj.kumar@saholic.com", "manish.sharma@shop2020.in"], message1, "All DTR Orders", XLS_O_FILENAME)
1046 
    #sendmail(["amit.gupta@shop2020.in","rajneesh.arora@saholic.com","manish.sharma@shop2020.in"], message, "Affiliate Reco", XLS_FILENAME, XLS_F_FILENAME, XLS_A_FILENAME, XLS_SC_FILENAME)
17001 amit.gupta 1047 
    #sendmail(["amit.gupta@shop2020.in"], "", "Affiliate Reco", XLS_FILENAME, XLS_F_FILENAME, XLS_A_FILENAME, XLS_SC_FILENAME)
1048 
 
17053 amit.gupta 1049 
def main3():
1050 
    db = client.Dtr
1051 
    #main()
1052 
    mailBodyTemplate="""
1053 
        <html>
1054 
            <body>
1055 
            <table cellspacing="0" border="1" style="text-align:right">
1056 
                <thead>
1057 
                    <tr>
1058 
                        <th style="text-align:center">Date</th>
1059 
                        <th  style="text-align:center">Sale</th>
1060 
                        <th  style="text-align:center">Approved</th>
1061 
                        <th  style="text-align:center">Approved %</th>
1062 
                        <th  style="text-align:center">Rejected</th>
1063 
                        <th  style="text-align:center">Rejected %</th>
1064 
                        <th  style="text-align:center">Missing %</th>
1065 
                        <th  style="text-align:center">Approved % against Sale</th>
1066 
                    </tr>
1067 
                </thead>
1068 
                <tbody>
1069 
                    {0}
1070 
                </tbody>
1071 
            </table>
1072 
            </body>
1073 
        </html>
1074 
    """
1075 
    tbody=[]
1076 
    rowtemplate="<tr><td>{0}</td><td>{1}</td><td>{2}</td><td>{3}</td><td>{4}</td><td>{5}</td><td>{6}</td><td>{7}</td></tr>"
1077 
    for offer in db.flSaleSnapshot.find().limit(61).sort("_id",-1):
1078 
        approved = offer.get("approvedAmount")
1079 
        rejected = offer.get("rejectedAmount")
1080 
        sale = offer.get("paidAmount")
1081 
        total = approved + rejected
1082 
        if total ==0:
1083 
            continue
1084 
        tbody.append(rowtemplate.format(offer.get("_id"), sale, approved, int((approved*100/total)), rejected, int((rejected*100/total)), int((sale-approved-rejected)*100/sale),int((approved)*100/sale)))
1085 
    message = mailBodyTemplate.format("".join(tbody))
1086 
    print message
17001 amit.gupta 1087 
def main2():
1088 
    db = client.Dtr
17053 amit.gupta 1089 
    curs = getOrdersAfterDate(datetime.now() - timedelta(days=46), 3)
17001 amit.gupta 1090 
    datemap={}
1091 
    for row1 in curs:
1092 
        orderId = row1[0]
1093 
        order = db.merchantOrder.find_one({"orderId":orderId})
1094 
        if order is None:
1095 
            continue
1096 
        #saleTime = int(time.mktime(datetime.strptime(order['placedOn'], "%b %d, %Y, %I:%M %p").timetuple()))
1097 
        try:
1098 
            saleTime = datetime.strptime(order['placedOn'], "%a, %d %b, %Y").strftime("%Y-%m-%d")
1099 
        except:
1100 
            saleTime = datetime.strptime(order['placedOn'], "%b %d, %Y, %I:%M %p").strftime("%Y-%m-%d")
1101 
        print "%s\t%s\t%s\t%s"%(orderId, order['placedOn'], saleTime, order['paidAmount'])
1102 
        if not datemap.has_key(saleTime):
1103 
            datemap[saleTime] = {"totalAmount":0, "paidAmount":0, "rejectedAmount":0, "approvedAmount":0, "count":0}
1104 
 
1105 
        obj = datemap.get(saleTime)
1106 
        obj['paidAmount'] += order["paidAmount"]
1107 
        obj['count'] += 1
1108 
 
17053 amit.gupta 1109 
    for offer in db.snapdealOrderAffiliateInfo1.find({"saleDate":{"$gte":toTimeStamp(datetime.now() - timedelta(days=46))}}):
17001 amit.gupta 1110 
        saleTime =  utils.fromTimeStamp(offer.get('saleDate')).strftime("%Y-%m-%d")
1111 
        obj = datemap.get(saleTime)
1112 
        if obj is not None:
1113 
            try:
1114 
                if offer.get("payOut"):
1115 
                    obj['approvedAmount'] += offer["saleAmount"]
1116 
                else:
1117 
                    obj['rejectedAmount'] += offer["saleAmount"]
1118 
            except:
1119 
                print "Exception for orderId" , offer.get("orderId")
1120 
        else:
1121 
            print "Could not find sale date", saleTime
1122 
            continue
1123 
 
1124 
    for key,valmap in  datemap.iteritems():
1125 
        valmap['_id'] = key
1126 
        db.flSaleSnapshot.save(valmap)
14450 amit.gupta 1127 
 
1128 
if __name__ == '__main__':
17168 amit.gupta 1129 
    #getdata.addToAllOrders(date.today()-timedelta(days=30))
1130 
    #main2()
17008 amit.gupta 1131 
    main()