Subversion Repositories SmartDukaan

Rev

Rev 17161 | Rev 17168 | 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
14524 amit.gupta 239 
    notReconciled = {}
14487 amit.gupta 240 
    matchedList = []
14450 amit.gupta 241 
    workbook = xlwt.Workbook()
14501 amit.gupta 242 
    worksheet = workbook.add_sheet("Snapdeal Reconciled")
243 
    worksheet1 = workbook.add_sheet("Snapdeal Reconciled All")
14508 amit.gupta 244 
    worksheet2 = workbook.add_sheet("Snapdeal Orders not reconciled")
16947 amit.gupta 245 
    worksheet3 = workbook.add_sheet("DateWise Sale Summary")
14508 amit.gupta 246 
    affNotReconciledSheet = workbook.add_sheet("Snapdeal Aff not reconciled")
16947 amit.gupta 247 
    setHeaders(worksheet, worksheet1, worksheet2, affNotReconciledSheet, worksheet3)
14450 amit.gupta 248 
    row = 0
14501 amit.gupta 249 
    anotherrow = 0
15577 amit.gupta 250 
    row5=0
14450 amit.gupta 251 
    for row1 in curs:
252 
        orderId = row1[0]
253 
        order = db.merchantOrder.find_one({"orderId":orderId})
254 
        if order is None:
255 
            continue
14496 amit.gupta 256 
        #saleTime = int(time.mktime(datetime.strptime(order['placedOn'], "%b %d, %Y, %I:%M %p").timetuple()))
16949 amit.gupta 257 
        try:
16951 amit.gupta 258 
            saleTime = datetime.strptime(order['placedOn'], "%a, %d %b, %Y").strftime("%Y-%m-%d")
16949 amit.gupta 259 
        except:
17001 amit.gupta 260 
            saleTime = datetime.strptime(order['placedOn'], "%b %d, %Y, %I:%M %p").strftime("%Y-%m-%d")
16947 amit.gupta 261 
 
14988 amit.gupta 262 
        try:
263 
            formattedTimestamp = order['placedOn']
264 
            timestamp1 = int(time.mktime(datetime.strptime(order['placedOn'], "%b %d, %Y, %I:%M %p").timetuple()))
265 
        except:
266 
            timestamp1 = int(time.mktime(row1[-4].timetuple()))
16947 amit.gupta 267 
            formattedTimestamp = datetime.strftime(utils.fromTimeStamp(timestamp1), "%b %d, %Y, %I:%M %p")
17167 amit.gupta 268 
 
269 
        affs = list(db.snapdealOrderAffiliateInfo1.find({"orderId":order["merchantOrderId"]}))
270 
        anotherrow += 1
271 
        if len(affs) > 0:
272 
            matchedList.append(order["merchantOrderId"])
14501 amit.gupta 273 
            order['reconciled'] = True
274 
            db.merchantOrder.save(order)
275 
            row += 1
276 
            i=-1
277 
            worksheet.write(row, inc(), orderId)
278 
            worksheet.write(row, inc(), row1[1])
14850 amit.gupta 279 
            worksheet.write(row, inc(), row1[-1])
280 
            worksheet.write(row, inc(), row1[-2])
14771 amit.gupta 281 
            worksheet.write(row, inc(), row1[-3])
14501 amit.gupta 282 
            worksheet.write(row, inc(), order['merchantOrderId'])
283 
            worksheet.write(row, inc(), order['subTagId'])
284 
            worksheet.write(row, inc(), order['placedOn'])
14988 amit.gupta 285 
            worksheet.write(row, inc(), formattedTimestamp)
14501 amit.gupta 286 
            worksheet.write(row, inc(), int(order['paidAmount']))
287 
            k = i
14504 amit.gupta 288 
            row -= 1
289 
            anotherrow -= 1
14501 amit.gupta 290 
            for subOrder in order['subOrders']:
291 
                i = k
14503 amit.gupta 292 
                row += 1
293 
                anotherrow += 1
17167 amit.gupta 294 
                for aff in affs:
295 
                    if subOrder['title']==aff['productCode']:
296 
                        worksheet.write(row, inc(), aff[0]['saleDate'])
297 
                        worksheet.write(row, inc(), aff[0]['saleAmount'])
298 
                        worksheet.write(row, inc(), aff[0]['payOut'])
299 
                        worksheet.write(row, inc(), subOrder['productTitle'])
300 
                        worksheet.write(row, inc(), subOrder['amountPaid'])
301 
                        worksheet.write(row, inc(), subOrder['quantity'])
302 
                        worksheet.write(row, inc(), subOrder['status'])
303 
                        worksheet.write(row, inc(), subOrder['cashBackStatus'])
304 
                        worksheet.write(row, inc(), subOrder['cashBackAmount'])
14504 amit.gupta 305 
 
14501 amit.gupta 306 
        else:
15577 amit.gupta 307 
            row5 += 1
308 
            i=-1
309 
            worksheet2.write(row5, inc(), order["orderId"])
310 
            worksheet2.write(row5, inc(), row1[1])
311 
            worksheet2.write(row5, inc(), row1[-1])
312 
            worksheet2.write(row5, inc(), row1[-2])
313 
            worksheet2.write(row5, inc(), row1[-3])
314 
            worksheet2.write(row5, inc(), order['merchantOrderId'])
315 
            worksheet2.write(row5, inc(), order['subTagId'])
316 
            worksheet2.write(row5, inc(), order['placedOn'])
317 
            worksheet2.write(row5, inc(), formattedTimestamp)
318 
            worksheet2.write(row5, inc(), int(order['paidAmount']))
319 
            row5 -=1
320 
            k=i
321 
            for subOrder in order['subOrders']:
322 
                i = k
15578 amit.gupta 323 
                row5 += 1
15577 amit.gupta 324 
                worksheet2.write(row5, inc(), subOrder['productTitle'])
325 
                worksheet2.write(row5, inc(), subOrder['amountPaid'])
326 
                worksheet2.write(row5, inc(), subOrder['quantity'])
327 
                worksheet2.write(row5, inc(), subOrder['status'])
328 
                worksheet2.write(row5, inc(), subOrder['cashBackStatus'])
15578 amit.gupta 329 
                worksheet2.write(row5, inc(), subOrder['cashBackAmount'])
14524 amit.gupta 330 
 
331 
    last30Days =  date.today() - timedelta(days=30)
332 
    int(time.mktime(last30Days.timetuple()))
333 
    row = 0
334 
    for offer in db.snapdealOrderAffiliateInfo.find({"reconciled":{"$exists":False},
335 
                                                     "saleTime":{"$gt":int(time.mktime(last30Days.timetuple())),
16953 amit.gupta 336 
                                                                 "$lt":int(time.mktime(date.today().timetuple()))}}, timeout=False ):
14524 amit.gupta 337 
        subTagId = offer.get("subTagId")
338 
        saleTime = offer.get("saleTime")
339 
        orders = getOrdersByTag(subTagId, 3)
340 
        for order in orders:
341 
            if notReconciled.has_key(order[0]):
342 
                (roww, column, paidAmount, timestamp1, oSubTagId) = notReconciled[order[0]]
343 
                if paidAmount==int(offer.get("saleAmount")) and timestamp1 <= saleTime and timestamp1 + 120 >= saleTime:
344 
                    i = column
14570 amit.gupta 345 
                    worksheet1.write(roww, inc(), offer.get("saleDate"))
14527 amit.gupta 346 
                    worksheet1.write(roww, inc(), offer.get("saleAmount"))
347 
                    worksheet1.write(roww, inc(), offer.get("payOut"))
348 
                    del notReconciled[order[0]]
14530 amit.gupta 349 
                    print "found match for user", order[1]
14610 amit.gupta 350 
                    db.snapdealOrderAffiliateInfo.update({"adId":offer.get("adId")}, {"$set":{"reconciled":True, "orderId":order[0]}}, multi=True)
351 
                    db.merchantOrder.update({"orderId":order[0]}, {"$set":{"reconciled":True, "adId":offer.get("adId")}}, multi=True)
14570 amit.gupta 352 
                    break
17010 amit.gupta 353 
 
14570 amit.gupta 354 
 
355 
 
356 
    for offer in db.snapdealOrderAffiliateInfo.find({"reconciled":{"$exists":False},
357 
                                                     "saleTime":{"$gt":int(time.mktime(last30Days.timetuple())),
358 
                                                                 "$lt":int(time.mktime(date.today().timetuple()))}} ):
359 
        row += 1
360 
        i=-1
361 
        affNotReconciledSheet.write(row, inc(), offer.get("adId"))
362 
        affNotReconciledSheet.write(row, inc(), offer.get("subTagId"))
363 
        affNotReconciledSheet.write(row, inc(), offer.get("saleDate"))
364 
        affNotReconciledSheet.write(row, inc(), offer.get("saleAmount"))
365 
        affNotReconciledSheet.write(row, inc(), offer.get("payOut"))
366 
        affNotReconciledSheet.write(row, inc(), offer.get("conversionStatus"))
14601 amit.gupta 367 
        affNotReconciledSheet.write(row, inc(), offer.get("ip"))
16947 amit.gupta 368 
 
17010 amit.gupta 369 
    mailBodyTemplate="""
370 
        <html>
371 
            <body>
372 
            <table cellspacing="0" border="1" style="text-align:right">
373 
                <thead>
374 
                    <tr>
375 
                        <th style="text-align:center">Date</th>
376 
                        <th  style="text-align:center">Sale</th>
377 
                        <th  style="text-align:center">Approved</th>
378 
                        <th  style="text-align:center">Approved %</th>
379 
                        <th  style="text-align:center">Rejected</th>
380 
                        <th  style="text-align:center">Rejected %</th>
17033 amit.gupta 381 
                        <th  style="text-align:center">Missing %</th>
382 
                        <th  style="text-align:center">Approved % against Sale</th>
17010 amit.gupta 383 
                    </tr>
384 
                </thead>
385 
                <tbody>
386 
                    {0}
387 
                </tbody>
388 
            </table>
389 
            </body>
390 
        </html>
391 
    """
392 
    tbody=[]
17033 amit.gupta 393 
    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 394 
    for offer in db.flSaleSnapshot.find().limit(61).sort("_id",-1):
395 
        approved = offer.get("approvedAmount")
396 
        rejected = offer.get("rejectedAmount")
17033 amit.gupta 397 
        sale = offer.get("paidAmount")
17010 amit.gupta 398 
        total = approved + rejected
399 
        if total ==0:
400 
            continue
17033 amit.gupta 401 
        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 402 
    message = mailBodyTemplate.format("".join(tbody))
15540 amit.gupta 403 
    workbook.save(XLS_FILENAME)
17024 amit.gupta 404 
    return message
17013 manish.sha 405 
 
406 
def generateHomeShopReco():
407 
    global i
408 
    curs = getOrdersAfterDate(datetime.now() - timedelta(days=30), 7)
409 
    db = client.Dtr
410 
    notReconciled = {}
411 
    matchedList = []
412 
    workbook = xlwt.Workbook()
413 
    worksheet = workbook.add_sheet("HomeShop18 Reconciled")
414 
    worksheet1 = workbook.add_sheet("HomeShop18 Reconciled All")
415 
    worksheet2 = workbook.add_sheet("HomeShop18 Orders not reconciled")
416 
    affNotReconciledSheet = workbook.add_sheet("HomeShop18 Aff not reconciled")
417 
    setHeaders(worksheet, worksheet1, worksheet2, affNotReconciledSheet)
418 
    row = 0
419 
    anotherrow = 0
420 
    row2 = 0
421 
    row5=0
422 
 
423 
    for row1 in curs:
424 
        orderId = row1[0]
425 
        order = db.merchantOrder.find_one({"orderId":orderId})
426 
        if order is None:
427 
            continue
428 
        #saleTime = int(time.mktime(datetime.strptime(order['placedOn'], "%b %d, %Y, %I:%M %p").timetuple()))  26 May 2015 02:12 PM
429 
        aff = list(db.shopcluesOrderAffiliateInfo.find({"merchantOrderId":order["merchantOrderId"]}))
430 
        anotherrow += 1
431 
        try:
432 
            formattedTimestamp = order['placedOn']
433 
            timestamp1 = int(time.mktime(datetime.strptime(order['placedOn'], "%d %b %Y %I:%M %p").timetuple()))
434 
        except:
435 
            timestamp1 = int(time.mktime(row1[-4].timetuple()))
436 
            formattedTimestamp = datetime.strftime(utils.fromTimeStamp(timestamp1), "%d %b %Y %I:%M %p")
437 
 
438 
        if len(aff) > 0 and order["subTagId"] not in matchedList and long(order["paidAmount"])==long(float(aff[0]["transactionValue"])):
439 
            matchedList.append(order["subTagId"])
440 
            db.shopcluesOrderAffiliateInfo.update({"merchantOrderId":aff[0].get("merchantOrderId")}, {"$set":{"reconciled":True, "orderId":orderId}}, multi=True)
441 
            order['reconciled'] = True
442 
            order['adId'] = aff[0].get("uniqueKey")
443 
            db.merchantOrder.save(order)
444 
            row += 1
445 
            i=-1
446 
 
447 
            worksheet.write(row, inc(), orderId)
448 
            worksheet1.write(anotherrow, i, orderId)
449 
            worksheet.write(row, inc(), row1[1])
450 
            worksheet1.write(anotherrow, i, row1[1])
451 
            worksheet.write(row, inc(), row1[-1])
452 
            worksheet1.write(anotherrow, i, row1[-1])
453 
            worksheet.write(row, inc(), row1[-2])
454 
            worksheet1.write(anotherrow, i, row1[-2])
455 
            worksheet.write(row, inc(), row1[-3])
456 
            worksheet1.write(anotherrow, i, row1[-3])
457 
            worksheet.write(row, inc(), order['merchantOrderId'])
458 
            worksheet1.write(anotherrow, i, order['merchantOrderId'])
459 
            worksheet.write(row, inc(), order['subTagId'])
460 
            worksheet1.write(anotherrow, i, order['subTagId'])
461 
            worksheet.write(row, inc(), order['placedOn'])
462 
            worksheet1.write(anotherrow, i, order['placedOn'])
463 
            worksheet.write(row, inc(), formattedTimestamp)
464 
            worksheet1.write(anotherrow, i, formattedTimestamp)
465 
            worksheet.write(row, inc(), int(order['paidAmount']))
466 
            worksheet1.write(anotherrow, i, int(order['paidAmount']))
467 
            worksheet.write(row, inc(), datetime.strftime(utils.fromTimeStamp(aff[0]['transactionTime']), "%d %b %Y %I:%M %p"))
468 
            worksheet1.write(anotherrow, i, datetime.strftime(utils.fromTimeStamp(aff[0]['transactionTime']), "%d %b %Y %I:%M %p"))
469 
            worksheet.write(row, inc(), aff[0]['transactionValue'])
470 
            worksheet1.write(anotherrow, i, aff[0]['transactionValue'])
471 
            worksheet.write(row, inc(), aff[0]['payOut'])
472 
            worksheet1.write(anotherrow, i, aff[0]['payOut'])
473 
            worksheet.write(row, inc(), "NA")
474 
            worksheet1.write(anotherrow, i, "NA")
475 
            k = i
476 
            row -= 1
477 
            anotherrow -= 1
478 
            for subOrder in order['subOrders']:
479 
                i = k
480 
                row += 1
481 
                anotherrow += 1
482 
                worksheet.write(row, inc(), subOrder['productTitle'])
483 
                worksheet1.write(anotherrow, i, subOrder['productTitle'])
484 
                worksheet.write(row, inc(), subOrder['amountPaid'])
485 
                worksheet1.write(anotherrow, i, subOrder['amountPaid'])
486 
                worksheet.write(row, inc(), subOrder['quantity'])
487 
                worksheet1.write(anotherrow, i, subOrder['quantity'])
488 
                worksheet.write(row, inc(), subOrder['status'])
489 
                worksheet1.write(anotherrow, i, subOrder['status'])
490 
                worksheet.write(row, inc(), subOrder['cashBackStatus'])
491 
                worksheet1.write(anotherrow, i, subOrder['cashBackStatus'])
492 
                worksheet.write(row, inc(), subOrder['cashBackAmount'])
493 
                worksheet1.write(anotherrow, i, subOrder['cashBackAmount'])
494 
 
495 
        else:
496 
            i=-1
497 
            worksheet1.write(anotherrow, inc(), orderId)
498 
            worksheet1.write(anotherrow, inc(), row1[1])
499 
            worksheet1.write(anotherrow, inc(), row1[-1])
500 
            worksheet1.write(anotherrow, inc(), row1[-2])
501 
            worksheet1.write(anotherrow, inc(), row1[-3])
502 
            worksheet1.write(anotherrow, inc(), order['merchantOrderId'])
503 
            worksheet1.write(anotherrow, inc(), order['subTagId'])
504 
            worksheet1.write(anotherrow, inc(), order['placedOn'])
505 
            worksheet1.write(anotherrow, inc(), formattedTimestamp)
506 
            worksheet1.write(anotherrow, inc(), int(order['paidAmount']))
507 
 
508 
            notReconciled[orderId] = (anotherrow, i, int(order['paidAmount']), timestamp1, order["subTagId"])
509 
            inc()
510 
            inc()
511 
            inc()
512 
            inc()
513 
            k = i
514 
            anotherrow -= 1
515 
            for subOrder in order['subOrders']:
516 
                anotherrow += 1
517 
                i = k
518 
                worksheet1.write(anotherrow, inc(), subOrder['productTitle'])
519 
                worksheet1.write(anotherrow, inc(), subOrder['amountPaid'])
520 
                worksheet1.write(anotherrow, inc(), subOrder['quantity'])
521 
                worksheet1.write(anotherrow, inc(), subOrder['status'])
522 
                worksheet1.write(anotherrow, inc(), subOrder['cashBackStatus'])
523 
                worksheet1.write(anotherrow, inc(), subOrder['cashBackAmount'])
524 
            row5 += 1
525 
            i=-1
526 
            worksheet2.write(row5, inc(), order["orderId"])
527 
            worksheet2.write(row5, inc(), row1[1])
528 
            worksheet2.write(row5, inc(), row1[-1])
529 
            worksheet2.write(row5, inc(), row1[-2])
530 
            worksheet2.write(row5, inc(), row1[-3])
531 
            worksheet2.write(row5, inc(), order['merchantOrderId'])
532 
            worksheet2.write(row5, inc(), order['subTagId'])
533 
            worksheet2.write(row5, inc(), order['placedOn'])
534 
            worksheet2.write(row5, inc(), formattedTimestamp)
535 
            worksheet2.write(row5, inc(), int(order['paidAmount']))
536 
            row5 -=1
537 
            k=i
538 
            for subOrder in order['subOrders']:
539 
                i = k
540 
                row5 += 1
541 
                worksheet2.write(row5, inc(), subOrder['productTitle'])
542 
                worksheet2.write(row5, inc(), subOrder['amountPaid'])
543 
                worksheet2.write(row5, inc(), subOrder['quantity'])
544 
                worksheet2.write(row5, inc(), subOrder['status'])
545 
                worksheet2.write(row5, inc(), subOrder['cashBackStatus'])
546 
                worksheet2.write(row5, inc(), subOrder['cashBackAmount'])
547 
    last30Days =  date.today() - timedelta(days=30)
548 
    int(time.mktime(last30Days.timetuple()))
549 
    row = 0
550 
    for offer in db.homeshopOrderAffiliateInfo.find({"reconciled":{"$exists":False},
551 
                                                     "transactionTime":{"$gt":int(time.mktime(last30Days.timetuple())),
552 
                                                                 "$lt":int(time.mktime(date.today().timetuple()))}} ):
553 
        subTagId = offer.get("subTagId")
554 
        saleTime = offer.get("transactionTime")
555 
        orders = getOrdersByTag(subTagId, 7)
556 
        for order in orders:
557 
            if notReconciled.has_key(order[0]):
558 
                (roww, column, paidAmount, timestamp1, oSubTagId) = notReconciled[order[0]]
559 
                if paidAmount==int(offer.get("transactionValue")) and timestamp1 <= saleTime and timestamp1 + 120 >= saleTime:
560 
                    i = column
561 
                    worksheet1.write(roww, inc(), datetime.strftime(utils.fromTimeStamp(offer.get("transactionTime")), "%d %b %Y %I:%M %p"))
562 
                    worksheet1.write(roww, inc(), offer.get("transactionValue"))
563 
                    worksheet1.write(roww, inc(), offer.get("payOut"))
564 
                    del notReconciled[order[0]]
565 
                    print "found match for user", order[1]
566 
                    db.homeshopOrderAffiliateInfo.update({"transactionId":offer.get("transactionId")}, {"$set":{"reconciled":True, "orderId":order[0]}}, multi=True)
567 
                    db.merchantOrder.update({"orderId":order[0]}, {"$set":{"reconciled":True, "adId":offer.get("uniqueKey")}}, multi=True)
568 
                    break
569 
 
570 
    unreconciledOrders = notReconciled.keys()
571 
 
572 
    for offer in db.homeshopOrderAffiliateInfo.find({"reconciled":{"$exists":False},
573 
                                                     "transactionTime":{"$gt":int(time.mktime(last30Days.timetuple())),
574 
                                                                 "$lt":int(time.mktime(date.today().timetuple()))}} ):
575 
        row += 1
576 
        i=-1
577 
        affNotReconciledSheet.write(row, inc(), offer.get("uniqueKey"))
578 
        affNotReconciledSheet.write(row, inc(), offer.get("subTagId"))
579 
        affNotReconciledSheet.write(row, inc(), datetime.strftime(utils.fromTimeStamp(offer.get("transactionTime")), "%d %b %Y %I:%M %p"))
580 
        affNotReconciledSheet.write(row, inc(), offer.get("transactionValue"))
581 
        affNotReconciledSheet.write(row, inc(), offer.get("payOut"))
582 
        affNotReconciledSheet.write(row, inc(), offer.get("status"))
583 
        affNotReconciledSheet.write(row, inc(), 'NA')
584 
 
585 
    workbook.save(XLS_HS_FILENAME)
586 
 
16995 amit.gupta 587 
 
15791 manish.sha 588 
def generateShopcluesReco():
589 
    global i
16238 manish.sha 590 
    curs = getOrdersAfterDate(datetime.now() - timedelta(days=30), 5)
15791 manish.sha 591 
    db = client.Dtr
592 
    notReconciled = {}
593 
    matchedList = []
594 
    workbook = xlwt.Workbook()
595 
    worksheet = workbook.add_sheet("Shopclues Reconciled")
596 
    worksheet1 = workbook.add_sheet("Shopclues Reconciled All")
597 
    worksheet2 = workbook.add_sheet("Shopclues Orders not reconciled")
598 
    affNotReconciledSheet = workbook.add_sheet("Shopclues Aff not reconciled")
599 
    setHeaders(worksheet, worksheet1, worksheet2, affNotReconciledSheet)
600 
    row = 0
601 
    anotherrow = 0
602 
    row2 = 0
603 
    row5=0
604 
 
605 
    for row1 in curs:
606 
        orderId = row1[0]
607 
        order = db.merchantOrder.find_one({"orderId":orderId})
608 
        if order is None:
609 
            continue
610 
        #saleTime = int(time.mktime(datetime.strptime(order['placedOn'], "%b %d, %Y, %I:%M %p").timetuple()))  26 May 2015 02:12 PM
16204 manish.sha 611 
        aff = list(db.shopcluesOrderAffiliateInfo.find({"merchantOrderId":order["merchantOrderId"]}))
15791 manish.sha 612 
        anotherrow += 1
613 
        try:
614 
            formattedTimestamp = order['placedOn']
615 
            timestamp1 = int(time.mktime(datetime.strptime(order['placedOn'], "%d %b %Y %I:%M %p").timetuple()))
616 
        except:
617 
            timestamp1 = int(time.mktime(row1[-4].timetuple()))
16947 amit.gupta 618 
            formattedTimestamp = datetime.strftime(utils.fromTimeStamp(timestamp1), "%d %b %Y %I:%M %p")
15791 manish.sha 619 
 
620 
        if len(aff) > 0 and order["subTagId"] not in matchedList and long(order["paidAmount"])==long(float(aff[0]["transactionValue"])):
621 
            matchedList.append(order["subTagId"])
16204 manish.sha 622 
            db.shopcluesOrderAffiliateInfo.update({"merchantOrderId":aff[0].get("merchantOrderId")}, {"$set":{"reconciled":True, "orderId":orderId}}, multi=True)
15791 manish.sha 623 
            order['reconciled'] = True
624 
            order['adId'] = aff[0].get("uniqueKey")
625 
            db.merchantOrder.save(order)
626 
            row += 1
627 
            i=-1
14450 amit.gupta 628 
 
15791 manish.sha 629 
            worksheet.write(row, inc(), orderId)
630 
            worksheet1.write(anotherrow, i, orderId)
631 
            worksheet.write(row, inc(), row1[1])
632 
            worksheet1.write(anotherrow, i, row1[1])
633 
            worksheet.write(row, inc(), row1[-1])
634 
            worksheet1.write(anotherrow, i, row1[-1])
635 
            worksheet.write(row, inc(), row1[-2])
636 
            worksheet1.write(anotherrow, i, row1[-2])
637 
            worksheet.write(row, inc(), row1[-3])
638 
            worksheet1.write(anotherrow, i, row1[-3])
639 
            worksheet.write(row, inc(), order['merchantOrderId'])
640 
            worksheet1.write(anotherrow, i, order['merchantOrderId'])
641 
            worksheet.write(row, inc(), order['subTagId'])
642 
            worksheet1.write(anotherrow, i, order['subTagId'])
643 
            worksheet.write(row, inc(), order['placedOn'])
644 
            worksheet1.write(anotherrow, i, order['placedOn'])
645 
            worksheet.write(row, inc(), formattedTimestamp)
646 
            worksheet1.write(anotherrow, i, formattedTimestamp)
647 
            worksheet.write(row, inc(), int(order['paidAmount']))
648 
            worksheet1.write(anotherrow, i, int(order['paidAmount']))
16947 amit.gupta 649 
            worksheet.write(row, inc(), datetime.strftime(utils.fromTimeStamp(aff[0]['transactionTime']), "%d %b %Y %I:%M %p"))
650 
            worksheet1.write(anotherrow, i, datetime.strftime(utils.fromTimeStamp(aff[0]['transactionTime']), "%d %b %Y %I:%M %p"))
15791 manish.sha 651 
            worksheet.write(row, inc(), aff[0]['transactionValue'])
652 
            worksheet1.write(anotherrow, i, aff[0]['transactionValue'])
653 
            worksheet.write(row, inc(), aff[0]['payOut'])
654 
            worksheet1.write(anotherrow, i, aff[0]['payOut'])
16238 manish.sha 655 
            worksheet.write(row, inc(), "NA")
656 
            worksheet1.write(anotherrow, i, "NA")
15791 manish.sha 657 
            k = i
658 
            row -= 1
659 
            anotherrow -= 1
660 
            for subOrder in order['subOrders']:
661 
                i = k
662 
                row += 1
663 
                anotherrow += 1
664 
                worksheet.write(row, inc(), subOrder['productTitle'])
665 
                worksheet1.write(anotherrow, i, subOrder['productTitle'])
666 
                worksheet.write(row, inc(), subOrder['amountPaid'])
667 
                worksheet1.write(anotherrow, i, subOrder['amountPaid'])
668 
                worksheet.write(row, inc(), subOrder['quantity'])
669 
                worksheet1.write(anotherrow, i, subOrder['quantity'])
670 
                worksheet.write(row, inc(), subOrder['status'])
671 
                worksheet1.write(anotherrow, i, subOrder['status'])
672 
                worksheet.write(row, inc(), subOrder['cashBackStatus'])
673 
                worksheet1.write(anotherrow, i, subOrder['cashBackStatus'])
674 
                worksheet.write(row, inc(), subOrder['cashBackAmount'])
675 
                worksheet1.write(anotherrow, i, subOrder['cashBackAmount'])
676 
 
677 
        else:
678 
            i=-1
679 
            worksheet1.write(anotherrow, inc(), orderId)
680 
            worksheet1.write(anotherrow, inc(), row1[1])
681 
            worksheet1.write(anotherrow, inc(), row1[-1])
682 
            worksheet1.write(anotherrow, inc(), row1[-2])
683 
            worksheet1.write(anotherrow, inc(), row1[-3])
684 
            worksheet1.write(anotherrow, inc(), order['merchantOrderId'])
685 
            worksheet1.write(anotherrow, inc(), order['subTagId'])
686 
            worksheet1.write(anotherrow, inc(), order['placedOn'])
687 
            worksheet1.write(anotherrow, inc(), formattedTimestamp)
688 
            worksheet1.write(anotherrow, inc(), int(order['paidAmount']))
689 
 
690 
            notReconciled[orderId] = (anotherrow, i, int(order['paidAmount']), timestamp1, order["subTagId"])
691 
            inc()
692 
            inc()
693 
            inc()
694 
            inc()
695 
            k = i
696 
            anotherrow -= 1
697 
            for subOrder in order['subOrders']:
698 
                anotherrow += 1
699 
                i = k
700 
                worksheet1.write(anotherrow, inc(), subOrder['productTitle'])
701 
                worksheet1.write(anotherrow, inc(), subOrder['amountPaid'])
702 
                worksheet1.write(anotherrow, inc(), subOrder['quantity'])
703 
                worksheet1.write(anotherrow, inc(), subOrder['status'])
704 
                worksheet1.write(anotherrow, inc(), subOrder['cashBackStatus'])
705 
                worksheet1.write(anotherrow, inc(), subOrder['cashBackAmount'])
706 
            row5 += 1
707 
            i=-1
708 
            worksheet2.write(row5, inc(), order["orderId"])
709 
            worksheet2.write(row5, inc(), row1[1])
710 
            worksheet2.write(row5, inc(), row1[-1])
711 
            worksheet2.write(row5, inc(), row1[-2])
712 
            worksheet2.write(row5, inc(), row1[-3])
713 
            worksheet2.write(row5, inc(), order['merchantOrderId'])
714 
            worksheet2.write(row5, inc(), order['subTagId'])
715 
            worksheet2.write(row5, inc(), order['placedOn'])
716 
            worksheet2.write(row5, inc(), formattedTimestamp)
717 
            worksheet2.write(row5, inc(), int(order['paidAmount']))
718 
            row5 -=1
719 
            k=i
720 
            for subOrder in order['subOrders']:
721 
                i = k
722 
                row5 += 1
723 
                worksheet2.write(row5, inc(), subOrder['productTitle'])
724 
                worksheet2.write(row5, inc(), subOrder['amountPaid'])
725 
                worksheet2.write(row5, inc(), subOrder['quantity'])
726 
                worksheet2.write(row5, inc(), subOrder['status'])
727 
                worksheet2.write(row5, inc(), subOrder['cashBackStatus'])
728 
                worksheet2.write(row5, inc(), subOrder['cashBackAmount'])
729 
 
730 
    last30Days =  date.today() - timedelta(days=30)
731 
    int(time.mktime(last30Days.timetuple()))
732 
    row = 0
733 
    for offer in db.shopcluesOrderAffiliateInfo.find({"reconciled":{"$exists":False},
734 
                                                     "transactionTime":{"$gt":int(time.mktime(last30Days.timetuple())),
735 
                                                                 "$lt":int(time.mktime(date.today().timetuple()))}} ):
736 
        subTagId = offer.get("subTagId")
737 
        saleTime = offer.get("transactionTime")
15794 manish.sha 738 
        orders = getOrdersByTag(subTagId, 5)
15791 manish.sha 739 
        for order in orders:
740 
            if notReconciled.has_key(order[0]):
741 
                (roww, column, paidAmount, timestamp1, oSubTagId) = notReconciled[order[0]]
742 
                if paidAmount==int(offer.get("transactionValue")) and timestamp1 <= saleTime and timestamp1 + 120 >= saleTime:
743 
                    i = column
16947 amit.gupta 744 
                    worksheet1.write(roww, inc(), datetime.strftime(utils.fromTimeStamp(offer.get("transactionTime")), "%d %b %Y %I:%M %p"))
15791 manish.sha 745 
                    worksheet1.write(roww, inc(), offer.get("transactionValue"))
746 
                    worksheet1.write(roww, inc(), offer.get("payOut"))
747 
                    del notReconciled[order[0]]
748 
                    print "found match for user", order[1]
749 
                    db.shopcluesOrderAffiliateInfo.update({"transactionId":offer.get("transactionId")}, {"$set":{"reconciled":True, "orderId":order[0]}}, multi=True)
750 
                    db.merchantOrder.update({"orderId":order[0]}, {"$set":{"reconciled":True, "adId":offer.get("uniqueKey")}}, multi=True)
751 
                    break
752 
 
753 
    unreconciledOrders = notReconciled.keys()
754 
 
755 
    for offer in db.shopcluesOrderAffiliateInfo.find({"reconciled":{"$exists":False},
756 
                                                     "transactionTime":{"$gt":int(time.mktime(last30Days.timetuple())),
757 
                                                                 "$lt":int(time.mktime(date.today().timetuple()))}} ):
758 
        row += 1
759 
        i=-1
760 
        affNotReconciledSheet.write(row, inc(), offer.get("uniqueKey"))
761 
        affNotReconciledSheet.write(row, inc(), offer.get("subTagId"))
16947 amit.gupta 762 
        affNotReconciledSheet.write(row, inc(), datetime.strftime(utils.fromTimeStamp(offer.get("transactionTime")), "%d %b %Y %I:%M %p"))
15791 manish.sha 763 
        affNotReconciledSheet.write(row, inc(), offer.get("transactionValue"))
764 
        affNotReconciledSheet.write(row, inc(), offer.get("payOut"))
765 
        affNotReconciledSheet.write(row, inc(), offer.get("status"))
766 
        affNotReconciledSheet.write(row, inc(), 'NA')
767 
 
768 
    workbook.save(XLS_SC_FILENAME)
769 
 
14647 amit.gupta 770 
def sendmail(email, message, title, *varargs):
14450 amit.gupta 771 
    if email == "":
772 
        return
773 
    mailServer = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)
774 
    mailServer.ehlo()
775 
    mailServer.starttls()
776 
    mailServer.ehlo()
777 
 
778 
    # Create the container (outer) email message.
779 
    msg = MIMEMultipart()
780 
    msg['Subject'] = title
781 
    msg.preamble = title
782 
    html_msg = MIMEText(message, 'html')
783 
    msg.attach(html_msg)
784 
 
785 
    #snapdeal more to be added here
14647 amit.gupta 786 
    for fileName in varargs:
787 
        snapdeal = MIMEBase('application', 'vnd.ms-excel')
788 
        snapdeal.set_payload(file(fileName).read())
789 
        encoders.encode_base64(snapdeal)
790 
        snapdeal.add_header('Content-Disposition', 'attachment;filename=' + fileName)
791 
        msg.attach(snapdeal)
14450 amit.gupta 792 
 
793 
 
794 
    email.append('amit.gupta@shop2020.in')
795 
    MAILTO = email
796 
    mailServer.login(SENDER, PASSWORD)
797 
    mailServer.sendmail(PASSWORD, MAILTO, msg.as_string())
798 
 
16958 amit.gupta 799 
def setHeaders(worksheet, worksheet1, worksheet2, affNotReconciledSheet, worksheet3=None):
14505 amit.gupta 800 
    boldStyle = xlwt.XFStyle()
801 
    f = xlwt.Font()
802 
    f.bold = True
803 
    boldStyle.font = f
14501 amit.gupta 804 
    row = 0
805 
    global i
806 
    i=-1
807 
    worksheet.write(row, inc(), 'Order Id', boldStyle)
808 
    worksheet.write(row, inc(), 'User Id', boldStyle)
14850 amit.gupta 809 
    worksheet.write(row, inc(), 'Username', boldStyle)
14601 amit.gupta 810 
    worksheet.write(row, inc(), 'Version Code', boldStyle)
14771 amit.gupta 811 
    worksheet.write(row, inc(), 'Device', boldStyle)
14501 amit.gupta 812 
    worksheet.write(row, inc(), 'Merchant Order Id', boldStyle)
813 
    worksheet.write(row, inc(), 'SubtagId', boldStyle)
814 
    worksheet.write(row, inc(), 'Sale Date', boldStyle)
14988 amit.gupta 815 
    worksheet.write(row, inc(), 'Dtr Sale Date', boldStyle)
14501 amit.gupta 816 
    worksheet.write(row, inc(), 'Sale Amount', boldStyle)
817 
    worksheet.write(row, inc(), 'Aff Sale Date', boldStyle)
818 
    worksheet.write(row, inc(), 'Aff Sale Amt', boldStyle)
819 
    worksheet.write(row, inc(), 'Aff PayOut', boldStyle)
14601 amit.gupta 820 
    worksheet.write(row, inc(), 'IP', boldStyle)
16718 amit.gupta 821 
    worksheet.write(row, inc(), 'Aff Status', boldStyle)
14501 amit.gupta 822 
    worksheet.write(row, inc(), 'Product Title', boldStyle)
823 
    worksheet.write(row, inc(), 'Price', boldStyle)
824 
    worksheet.write(row, inc(), 'Quantity', boldStyle)
825 
    worksheet.write(row, inc(), 'Status', boldStyle)
826 
    worksheet.write(row, inc(), 'Cashback Status', boldStyle)
827 
    worksheet.write(row, inc(), 'CashBack', boldStyle)
828 
 
14505 amit.gupta 829 
    i = -1
14501 amit.gupta 830 
    worksheet1.write(row, inc(), 'Order Id', boldStyle)
831 
    worksheet1.write(row, inc(), 'User Id', boldStyle)
14850 amit.gupta 832 
    worksheet1.write(row, inc(), 'Username', boldStyle)
14601 amit.gupta 833 
    worksheet1.write(row, inc(), 'Version Code', boldStyle)
14771 amit.gupta 834 
    worksheet1.write(row, inc(), 'Device', boldStyle)
14501 amit.gupta 835 
    worksheet1.write(row, inc(), 'Merchant Order Id', boldStyle)
836 
    worksheet1.write(row, inc(), 'SubtagId', boldStyle)
837 
    worksheet1.write(row, inc(), 'Sale Date', boldStyle)
14988 amit.gupta 838 
    worksheet1.write(row, inc(), 'Dtr Sale Date', boldStyle)
14501 amit.gupta 839 
    worksheet1.write(row, inc(), 'Sale Amount', boldStyle)
840 
    worksheet1.write(row, inc(), 'Aff Sale Date', boldStyle)
841 
    worksheet1.write(row, inc(), 'Aff Sale Amt', boldStyle)
842 
    worksheet1.write(row, inc(), 'Aff PayOut', boldStyle)
14601 amit.gupta 843 
    worksheet1.write(row, inc(), 'IP', boldStyle)
16718 amit.gupta 844 
    worksheet1.write(row, inc(), 'Aff Status', boldStyle)
14501 amit.gupta 845 
    worksheet1.write(row, inc(), 'Product Title', boldStyle)
846 
    worksheet1.write(row, inc(), 'Price', boldStyle)
847 
    worksheet1.write(row, inc(), 'Quantity', boldStyle)
848 
    worksheet1.write(row, inc(), 'Status', boldStyle)
849 
    worksheet1.write(row, inc(), 'Cashback Status', boldStyle)
850 
    worksheet1.write(row, inc(), 'CashBack', boldStyle)
14508 amit.gupta 851 
 
852 
    i = -1
853 
    worksheet2.write(row, inc(), 'Order Id', boldStyle)
854 
    worksheet2.write(row, inc(), 'User Id', boldStyle)
14850 amit.gupta 855 
    worksheet2.write(row, inc(), 'Username', boldStyle)
14605 amit.gupta 856 
    worksheet2.write(row, inc(), 'Version Code', boldStyle)
14771 amit.gupta 857 
    worksheet2.write(row, inc(), 'Device', boldStyle)
14508 amit.gupta 858 
    worksheet2.write(row, inc(), 'Merchant Order Id', boldStyle)
859 
    worksheet2.write(row, inc(), 'SubtagId', boldStyle)
860 
    worksheet2.write(row, inc(), 'Sale Date', boldStyle)
14988 amit.gupta 861 
    worksheet2.write(row, inc(), 'Dtr Sale Date', boldStyle)
14508 amit.gupta 862 
    worksheet2.write(row, inc(), 'Sale Amount', boldStyle)
863 
    worksheet2.write(row, inc(), 'Product Title', boldStyle)
864 
    worksheet2.write(row, inc(), 'Price', boldStyle)
865 
    worksheet2.write(row, inc(), 'Quantity', boldStyle)
866 
    worksheet2.write(row, inc(), 'Status', boldStyle)
867 
    worksheet2.write(row, inc(), 'Cashback Status', boldStyle)
868 
    worksheet2.write(row, inc(), 'CashBack', boldStyle)
14450 amit.gupta 869 
 
14508 amit.gupta 870 
    i =-1
871 
    affNotReconciledSheet.write(row, inc(), 'AdId', boldStyle)
872 
    affNotReconciledSheet.write(row, inc(), 'subTagId', boldStyle)
873 
    affNotReconciledSheet.write(row, inc(), 'Sale Date', boldStyle)
874 
    affNotReconciledSheet.write(row, inc(), 'Sale Amount', boldStyle)
875 
    affNotReconciledSheet.write(row, inc(), 'Pay Out', boldStyle)
876 
    affNotReconciledSheet.write(row, inc(), 'Status', boldStyle)
14601 amit.gupta 877 
    affNotReconciledSheet.write(row, inc(), 'IP', boldStyle)
14524 amit.gupta 878 
 
16958 amit.gupta 879 
    if worksheet3:
880 
        i =-1
881 
        worksheet3.write(row, inc(), 'Date', boldStyle)
882 
        worksheet3.write(row, inc(), 'Sale Amount', boldStyle)
883 
        worksheet3.write(row, inc(), 'Approved Sales', boldStyle)
884 
        worksheet3.write(row, inc(), 'Rejected Sales', boldStyle)
16947 amit.gupta 885 
 
14524 amit.gupta 886 
def getUserAmountReconciled():
887 
    pass
888 
 
14613 amit.gupta 889 
def getUserOrders():
890 
    global i
16726 amit.gupta 891 
    #worksheet1 = wb.add_sheet("Suspected Users")
17159 amit.gupta 892 
    workbook = xlwt.Workbook("ISO8859-1")
14632 amit.gupta 893 
    sh1 = workbook.add_sheet("All Orders")
15423 amit.gupta 894 
    sh2 = workbook.add_sheet("Flipkart Orders")
17159 amit.gupta 895 
    worksheet = workbook.add_sheet("All Users")
14632 amit.gupta 896 
 
14613 amit.gupta 897 
    db=client.Dtr
17167 amit.gupta 898 
    prevDate = date.today() - timedelta(days=31)
17159 amit.gupta 899 
    results = Mysql.fetchResult("select * from allorder where created_on > %s", prevDate)
14613 amit.gupta 900 
    row = 0
901 
    i=-1
902 
    worksheet.write(row, inc(), 'User Id', boldStyle)
903 
    worksheet.write(row, inc(), 'User name', boldStyle)
904 
    worksheet.write(row, inc(), 'Order Id', boldStyle)
905 
    worksheet.write(row, inc(), 'Created On', boldStyle)
906 
    worksheet.write(row, inc(), 'Store Id', boldStyle)
907 
    worksheet.write(row, inc(), 'Merchant Order Id', boldStyle)
16785 amit.gupta 908 
    worksheet.write(row, inc(), 'SubOrder Id', boldStyle)
14616 amit.gupta 909 
    worksheet.write(row, inc(), 'Product title', boldStyle)
14613 amit.gupta 910 
    worksheet.write(row, inc(), 'Amount Paid', boldStyle)
911 
    worksheet.write(row, inc(), 'Cashback', boldStyle)
912 
    worksheet.write(row, inc(), 'Order Status', boldStyle)
913 
    worksheet.write(row, inc(), 'Detailed Status', boldStyle)
914 
    worksheet.write(row, inc(), 'Cashback Status', boldStyle)
915 
    worksheet.write(row, inc(), 'Reconciled', boldStyle)
916 
    worksheet.write(row, inc(), 'IP', boldStyle)
16718 amit.gupta 917 
    worksheet.write(row, inc(), 'Aff Status', boldStyle)
14693 amit.gupta 918 
    i=-1
919 
    sh1.write(row, inc(), 'User Id', boldStyle)
920 
    sh1.write(row, inc(), 'User name', boldStyle)
921 
    sh1.write(row, inc(), 'Order Id', boldStyle)
922 
    sh1.write(row, inc(), 'Created On', boldStyle)
923 
    sh1.write(row, inc(), 'Store Id', boldStyle)
924 
    sh1.write(row, inc(), 'Merchant Order Id', boldStyle)
14771 amit.gupta 925 
    sh1.write(row, inc(), 'Status', boldStyle)
15932 amit.gupta 926 
    sh1.write(row, inc(), 'Detailed Status', boldStyle)
14693 amit.gupta 927 
    sh1.write(row, inc(), 'Product title', boldStyle)
928 
    sh1.write(row, inc(), 'Referrer', boldStyle)
929 
    sh1.write(row, inc(), 'Amount Paid', boldStyle)
14771 amit.gupta 930 
    sh1.write(row, inc(), 'Catalog Id', boldStyle)
14693 amit.gupta 931 
    sh1.write(row, inc(), 'Brand', boldStyle)
932 
    sh1.write(row, inc(), 'Model', boldStyle)
933 
    sh1.write(row, inc(), 'Category', boldStyle)
15932 amit.gupta 934 
    sh1.write(row, inc(), 'Deal Rank', boldStyle)
16286 amit.gupta 935 
    sh1.write(row, inc(), 'Max Nlc', boldStyle)
936 
    sh1.write(row, inc(), 'Min Nlc', boldStyle)
937 
    sh1.write(row, inc(), 'DP', boldStyle)
938 
    sh1.write(row, inc(), 'Item status', boldStyle)
15932 amit.gupta 939 
    sh1.write(row, inc(), 'City', boldStyle)
940 
    sh1.write(row, inc(), 'State', boldStyle)
941 
    sh1.write(row, inc(), 'Pincode', boldStyle)
17159 amit.gupta 942 
    sh1.write(row, inc(), 'SubOrder Id', boldStyle)
943 
    sh1.write(row, inc(), 'Cashback Status', boldStyle)
944 
    sh1.write(row, inc(), 'Cashback Amount', boldStyle)
15423 amit.gupta 945 
    i =-1
946 
    sh2.write(row, inc(), 'User Id', boldStyle)
947 
    sh2.write(row, inc(), 'User name', boldStyle)
948 
    sh2.write(row, inc(), 'Created On', boldStyle)
949 
    sh2.write(row, inc(), 'Store', boldStyle)
950 
    sh2.write(row, inc(), 'Status', boldStyle)
951 
    sh2.write(row, inc(), 'Product title', boldStyle)
952 
    sh2.write(row, inc(), 'Price', boldStyle)
953 
    sh2.write(row, inc(), 'Quantity', boldStyle)
954 
    sh2.write(row, inc(), 'Catalog Id', boldStyle)
955 
    sh2.write(row, inc(), 'Brand', boldStyle)
956 
    sh2.write(row, inc(), 'Model', boldStyle)
957 
    sh2.write(row, inc(), 'Category', boldStyle)
14613 amit.gupta 958 
 
17159 amit.gupta 959 
    default_format = xlwt.XFStyle()
960 
    date_format = xlwt.XFStyle()
961 
    date_format.num_format_str = 'dd/mm/yyyy'
962 
 
963 
    datetime_format = xlwt.XFStyle()
964 
    datetime_format.num_format_str = 'dd/mm/yyyy HH:MM AM/PM'
965 
 
966 
    number_format = xlwt.XFStyle()
967 
    number_format.num_format_str = '#,##0'
968 
 
969 
 
14613 amit.gupta 970 
    row=0
17159 amit.gupta 971 
    for r in results:
972 
        row += 1
973 
        column = 0
974 
        for data in r :
975 
            sh1.write(row, column, int(data) if type(data) is float else data, datetime_format if type(data) is datetime else default_format)
976 
            column += 1
977 
        i=-1
978 
        worksheet.write(row, inc(), r[0])
979 
        worksheet.write(row, inc(), r[1])
980 
        worksheet.write(row, inc(), r[2])
981 
        worksheet.write(row, inc(), r[3], datetime_format)
982 
        worksheet.write(row, inc(), r[4])
983 
        worksheet.write(row, inc(), r[5])
984 
        worksheet.write(row, inc(), r[23])
985 
        worksheet.write(row, inc(), r[8])
986 
        worksheet.write(row, inc(), r[10])
987 
        worksheet.write(row, inc(), r[25])
988 
        worksheet.write(row, inc(), r[6])
989 
        worksheet.write(row, inc(), r[7])
990 
        worksheet.write(row, inc(), r[24])
15423 amit.gupta 991 
 
15542 amit.gupta 992 
    flipkartOrders = session.query(FlipkartOrders).order_by(FlipkartOrders.created.desc()).all()
15540 amit.gupta 993 
    flipkartOrders.reverse()
15423 amit.gupta 994 
    row3=0
15430 amit.gupta 995 
    #try:
15540 amit.gupta 996 
    for order in flipkartOrders:
15423 amit.gupta 997 
        row3 += 1
998 
        i=-1
15540 amit.gupta 999 
        sh2.write(row3, inc(), order.user_id)
1000 
        sh2.write(row3, inc(), order.email)
1001 
        sh2.write(row3, inc(), order.created, datetime_format)
15423 amit.gupta 1002 
        sh2.write(row3, inc(), "Flipkart")
15540 amit.gupta 1003 
        sh2.write(row3, inc(), order.status)
1004 
        sh2.write(row3, inc(), order.title)
1005 
        sh2.write(row3, inc(), order.price)
1006 
        sh2.write(row3, inc(), order.quantity)
1007 
        sh2.write(row3, inc(), order.catalogId)
1008 
        sh2.write(row3, inc(), order.brand)
1009 
        sh2.write(row3, inc(), order.model)
1010 
        sh2.write(row3, inc(), order.category)
14632 amit.gupta 1011 
 
1012 
    workbook.save(XLS_O_FILENAME)
14647 amit.gupta 1013 
 
1014 
def getSkuData(storeId, identifier):
16492 amit.gupta 1015 
    if storeId in (1,2,4,5,6):
14647 amit.gupta 1016 
        skuData = client.Catalog.MasterData.find_one({'identifier':identifier, 'source_id':storeId})
1017 
    elif storeId == 3:
1018 
        skuData = client.Catalog.MasterData.find_one({'secondaryIdentifier':identifier, 'source_id':storeId})
1019 
    return skuData
1020 
 
14460 amit.gupta 1021 
def inc():
1022 
    global i
1023 
    i+=1
1024 
    return i
15540 amit.gupta 1025 
 
1026 
def migrateFlipkartOrders():
1027 
    db = client.Dtr
1028 
    info = reversed(list(db.flipkartOrderAffiliateInfo.find()))
1029 
    #try:
1030 
    for order in info:
1031 
        userId = None
1032 
        subTagId = None
1033 
        email = None
1034 
        subTagId = order.get("subTagId")
1035 
        if subTagId:
1036 
            click = session.query(Clicks).filter_by(tag = subTagId).first()
1037 
            if click is not None:
1038 
                userId= click.user_id
1039 
                user = session.query(Users.email).filter_by(id = userId).first()
1040 
                if user is not None:
1041 
                    email = user.email
1042 
 
1043 
        flipkartOrder = FlipkartOrders()
1044 
        flipkartOrder.user_id = userId
1045 
        flipkartOrder.identifier = order.get("identifier")
1046 
        flipkartOrder.email = email
1047 
        flipkartOrder.subtagId = order.get("subTagId")
1048 
        flipkartOrder.created = datetime.strptime(order.get("saleDate"), "%Y-%m-%d")
1049 
        flipkartOrder.status = order.get("conversionStatus")
1050 
        flipkartOrder.title = order.get("productTitle")
1051 
        flipkartOrder.price = order.get("price")
1052 
        flipkartOrder.quantity = order.get("quantity")
1053 
        flipkartOrder.productCode = order.get("productCode")
1054 
        skuData = getSkuData(2, order.get("productCode"))
1055 
        if skuData is not None:
1056 
            flipkartOrder.catalogId = skuData.get("skuBundleId")
1057 
            flipkartOrder.brand = skuData.get("brand")
1058 
            flipkartOrder.model = skuData.get("model_name")
1059 
            flipkartOrder.category =categoryMap.get(skuData.get("category_id"))
1060 
            flipkartOrder.title =skuData.get("source_product_name")
1061 
 
1062 
    session.commit()
16947 amit.gupta 1063 
 
1064 
 
1065 
def main1():
1066 
    db = client.Dtr
1067 
    for offer in db.snapdealOrderAffiliateInfo.find({}):
1068 
        print offer.get("adId"), offer.get("saleAmount"), type(offer.get("saleAmount"))
1069 
        #obj = offer.get(saleTime)
1070 
        #obj['paidAmount'] += order["paidAmount"]
14632 amit.gupta 1071 
def addHeaders(sheet):
1072 
    global i
1073 
    i = 0
1074 
    sheet.write(0, inc(), )
14450 amit.gupta 1075 
def main():
17161 amit.gupta 1076 
    message = generateSnapDealReco()
1077 
    generateFlipkartReco()
1078 
    generateShopcluesReco()
1079 
    generateAmazonReco(XLS_A_FILENAME)
17137 naman 1080 
    message1 = getdata.summaryByBrandAndStore()
14613 amit.gupta 1081 
    getUserOrders()
17137 naman 1082 
    #getdata.sendmail(["naman.kumar@shop2020.in"], message1, "Summary by brand and store")
1083 
    #sendmail(["naman.kumar@shop2020.in"], message, "Affiliate Reco", XLS_FILENAME, XLS_F_FILENAME, XLS_A_FILENAME, XLS_SC_FILENAME)
1084 
    #sendmail(["naman.kumar@shop2020.in"], message1, "All DTR Orders", XLS_O_FILENAME)
1085 
    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)
16995 amit.gupta 1086 
    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 1087 
    #sendmail(["amit.gupta@shop2020.in"], "", "Affiliate Reco", XLS_FILENAME, XLS_F_FILENAME, XLS_A_FILENAME, XLS_SC_FILENAME)
1088 
 
17053 amit.gupta 1089 
def main3():
1090 
    db = client.Dtr
1091 
    #main()
1092 
    mailBodyTemplate="""
1093 
        <html>
1094 
            <body>
1095 
            <table cellspacing="0" border="1" style="text-align:right">
1096 
                <thead>
1097 
                    <tr>
1098 
                        <th style="text-align:center">Date</th>
1099 
                        <th  style="text-align:center">Sale</th>
1100 
                        <th  style="text-align:center">Approved</th>
1101 
                        <th  style="text-align:center">Approved %</th>
1102 
                        <th  style="text-align:center">Rejected</th>
1103 
                        <th  style="text-align:center">Rejected %</th>
1104 
                        <th  style="text-align:center">Missing %</th>
1105 
                        <th  style="text-align:center">Approved % against Sale</th>
1106 
                    </tr>
1107 
                </thead>
1108 
                <tbody>
1109 
                    {0}
1110 
                </tbody>
1111 
            </table>
1112 
            </body>
1113 
        </html>
1114 
    """
1115 
    tbody=[]
1116 
    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>"
1117 
    for offer in db.flSaleSnapshot.find().limit(61).sort("_id",-1):
1118 
        approved = offer.get("approvedAmount")
1119 
        rejected = offer.get("rejectedAmount")
1120 
        sale = offer.get("paidAmount")
1121 
        total = approved + rejected
1122 
        if total ==0:
1123 
            continue
1124 
        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)))
1125 
    message = mailBodyTemplate.format("".join(tbody))
1126 
    print message
17001 amit.gupta 1127 
def main2():
1128 
    db = client.Dtr
17053 amit.gupta 1129 
    curs = getOrdersAfterDate(datetime.now() - timedelta(days=46), 3)
17001 amit.gupta 1130 
    datemap={}
1131 
    for row1 in curs:
1132 
        orderId = row1[0]
1133 
        order = db.merchantOrder.find_one({"orderId":orderId})
1134 
        if order is None:
1135 
            continue
1136 
        #saleTime = int(time.mktime(datetime.strptime(order['placedOn'], "%b %d, %Y, %I:%M %p").timetuple()))
1137 
        try:
1138 
            saleTime = datetime.strptime(order['placedOn'], "%a, %d %b, %Y").strftime("%Y-%m-%d")
1139 
        except:
1140 
            saleTime = datetime.strptime(order['placedOn'], "%b %d, %Y, %I:%M %p").strftime("%Y-%m-%d")
1141 
        print "%s\t%s\t%s\t%s"%(orderId, order['placedOn'], saleTime, order['paidAmount'])
1142 
        if not datemap.has_key(saleTime):
1143 
            datemap[saleTime] = {"totalAmount":0, "paidAmount":0, "rejectedAmount":0, "approvedAmount":0, "count":0}
1144 
 
1145 
        obj = datemap.get(saleTime)
1146 
        obj['paidAmount'] += order["paidAmount"]
1147 
        obj['count'] += 1
1148 
 
17053 amit.gupta 1149 
    for offer in db.snapdealOrderAffiliateInfo1.find({"saleDate":{"$gte":toTimeStamp(datetime.now() - timedelta(days=46))}}):
17001 amit.gupta 1150 
        saleTime =  utils.fromTimeStamp(offer.get('saleDate')).strftime("%Y-%m-%d")
1151 
        obj = datemap.get(saleTime)
1152 
        if obj is not None:
1153 
            try:
1154 
                if offer.get("payOut"):
1155 
                    obj['approvedAmount'] += offer["saleAmount"]
1156 
                else:
1157 
                    obj['rejectedAmount'] += offer["saleAmount"]
1158 
            except:
1159 
                print "Exception for orderId" , offer.get("orderId")
1160 
        else:
1161 
            print "Could not find sale date", saleTime
1162 
            continue
1163 
 
1164 
    for key,valmap in  datemap.iteritems():
1165 
        valmap['_id'] = key
1166 
        db.flSaleSnapshot.save(valmap)
14450 amit.gupta 1167 
 
1168 
if __name__ == '__main__':
17159 amit.gupta 1169 
    getdata.addToAllOrders(date.today()-timedelta(days=30))
1170 
    main2()
17008 amit.gupta 1171 
    main()