Subversion Repositories SmartDukaan

Rev

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