WebSVN – SmartDukaan – Diff – /trunk/PyDTR/src/dtr/reports/affiliatereco.py



    worksheet1.write(row, i, 'User Id', boldStyle)
    worksheet2.write(row, i, 'User Id', boldStyle)
    worksheet.write(row, inc(), 'Username', boldStyle)
    worksheet1.write(row, i, 'Username', boldStyle)
    worksheet2.write(row, i, 'Username', boldStyle)
    worksheet.write(row, inc(), 'Version code', boldStyle)
    worksheet1.write(row, i, 'Version code', boldStyle)
    worksheet2.write(row, i, 'Version code', boldStyle)
    worksheet.write(row, inc(), 'Device', boldStyle)
    worksheet1.write(row, i, 'Device', boldStyle)
    worksheet2.write(row, i, 'Device', boldStyle)
    worksheet.write(row, inc(), 'Merchant Order Id', boldStyle)
    worksheet1.write(row, i, 'Merchant Order Id', boldStyle)
    worksheet2.write(row, i, 'Merchant Order Id', boldStyle)
    worksheet.write(row, inc(), 'Product Title', boldStyle)
    worksheet1.write(row, i, 'Product Title', boldStyle)

            row += 1
            i=-1
            worksheet.write(row, inc(), orderId)
            worksheet.write(row, inc(), row1[1])
            worksheet.write(row, inc(), row1[-1])
            worksheet.write(row, inc(), row1[-2])
            worksheet.write(row, inc(), row1[-3])
            worksheet.write(row, inc(), order['merchantOrderId'])
            worksheet.write(row, inc(), subOrder['productTitle'])
            worksheet.write(row, inc(), subOrder['amountPaid'])
            worksheet.write(row, inc(), subOrder['quantity'])
            worksheet.write(row, inc(), subOrder['status'])

                    row2 += 1
                    i = -1 
                    worksheet1.write(row2, inc(), orderId)
                    worksheet1.write(row2, inc(), row1[1])
                    worksheet1.write(row2, inc(), row1[-1])
                    worksheet1.write(row2, inc(), row1[-2])
                    worksheet1.write(row2, inc(), row1[-3])
                    worksheet1.write(row2, inc(), order['merchantOrderId'])
                    worksheet1.write(row2, inc(), subOrder['productTitle'])
                    worksheet1.write(row2, inc(), subOrder['amountPaid'])
                    worksheet1.write(row2, inc(), subOrder['quantity'])
                    worksheet1.write(row2, inc(), subOrder['status'])

            row3 += 1
            i=-1
            worksheet2.write(row3, inc(), orderId)
            worksheet2.write(row3, inc(), row1[1])
            worksheet2.write(row3, inc(), row1[-1])
            worksheet2.write(row3, inc(), row1[-2])
            worksheet2.write(row3, inc(), row1[-3])
            worksheet2.write(row3, inc(), order['merchantOrderId'])
            worksheet2.write(row3, inc(), subOrder['productTitle'])
            worksheet2.write(row3, inc(), subOrder['amountPaid'])
            worksheet2.write(row3, inc(), subOrder['quantity'])
            worksheet2.write(row3, inc(), subOrder['status'])

            i=-1 
            worksheet.write(row, inc(), orderId)
            worksheet1.write(anotherrow, i, orderId)
            worksheet.write(row, inc(), row1[1])
            worksheet1.write(anotherrow, i, row1[1])
            worksheet.write(row, inc(), row1[-3])
            worksheet1.write(anotherrow, i, row1[-3])
            worksheet.write(row, inc(), row1[-2])
            worksheet1.write(anotherrow, i, row1[-2])
            worksheet.write(row, inc(), row1[-1])
            worksheet1.write(anotherrow, i, row1[-1])
            worksheet.write(row, inc(), order['merchantOrderId'])

 
        else:
            i=-1
            worksheet1.write(anotherrow, inc(), orderId)
            worksheet1.write(anotherrow, inc(), row1[1])
            worksheet1.write(anotherrow, inc(), row1[-3])
            worksheet1.write(anotherrow, inc(), row1[-2])
            worksheet1.write(anotherrow, inc(), row1[-1])
            worksheet1.write(anotherrow, inc(), order['merchantOrderId'])
            worksheet1.write(anotherrow, inc(), order['subTagId'])
            worksheet1.write(anotherrow, inc(), order['placedOn'])

            timestamp1 = int(time.mktime(datetime.strptime(order['placedOn'], "%b %d, %Y, %I:%M %p").timetuple()))
            notReconciled[orderId] = (anotherrow, i, int(order['paidAmount']), timestamp1, order["subTagId"]) 
            inc()
            inc()
            inc()
            inc()
            k = i
            anotherrow -= 1
            for subOrder in order['subOrders']:
                anotherrow += 1
                i = k

            for order in db.merchantOrder.find({"orderId":row1[0]}):
                row2 += 1
                i=-1
                worksheet2.write(row2, inc(), order["orderId"])
                worksheet2.write(row2, inc(), row1[1])
                worksheet2.write(row2, inc(), row1[-3])
                worksheet2.write(row2, inc(), row1[-2])
                worksheet2.write(row2, inc(), row1[-1])
                worksheet2.write(row2, inc(), order['merchantOrderId'])
                worksheet2.write(row2, inc(), order['subTagId'])
                worksheet2.write(row2, inc(), order['placedOn'])

    global i
    i=-1    
    worksheet.write(row, inc(), 'Order Id', boldStyle)
    worksheet.write(row, inc(), 'User Id', boldStyle)
    worksheet.write(row, inc(), 'Version Code', boldStyle)
    worksheet.write(row, inc(), 'Device', boldStyle)
    worksheet.write(row, inc(), 'Username', boldStyle)
    worksheet.write(row, inc(), 'Merchant Order Id', boldStyle)
    worksheet.write(row, inc(), 'SubtagId', boldStyle)
    worksheet.write(row, inc(), 'Sale Date', boldStyle)
    worksheet.write(row, inc(), 'Sale Amount', boldStyle)

 
    i = -1
    worksheet1.write(row, inc(), 'Order Id', boldStyle)
    worksheet1.write(row, inc(), 'User Id', boldStyle)
    worksheet1.write(row, inc(), 'Version Code', boldStyle)
    worksheet1.write(row, inc(), 'Device', boldStyle)
    worksheet1.write(row, inc(), 'Username', boldStyle)
    worksheet1.write(row, inc(), 'Merchant Order Id', boldStyle)
    worksheet1.write(row, inc(), 'SubtagId', boldStyle)
    worksheet1.write(row, inc(), 'Sale Date', boldStyle)
    worksheet1.write(row, inc(), 'Sale Amount', boldStyle)

 
    i = -1
    worksheet2.write(row, inc(), 'Order Id', boldStyle)
    worksheet2.write(row, inc(), 'User Id', boldStyle)
    worksheet2.write(row, inc(), 'Version Code', boldStyle)
    worksheet2.write(row, inc(), 'Device', boldStyle)
    worksheet2.write(row, inc(), 'Username', boldStyle)
    worksheet2.write(row, inc(), 'Merchant Order Id', boldStyle)
    worksheet2.write(row, inc(), 'SubtagId', boldStyle)
    worksheet2.write(row, inc(), 'Sale Date', boldStyle)
    worksheet2.write(row, inc(), 'Sale Amount', boldStyle)

    workbook = xlwt.Workbook()
    sh1 = workbook.add_sheet("All Orders")
    
    db=client.Dtr
    results = Mysql.fetchResult('''
        select ow.*, u.username, crm1.order_count, u.referrer from order_view ow left join users u on u.id = ow.user_id left join (select user_id, count(*) as order_count from order_view where status = 'ORDER_CREATED'  group by user_id) as crm1 on ow.user_id = crm1.user_id where lower(u.referrer) not like 'emp%' or u.referrer is null; 
        ''',)
    row = 0
    i=-1
    worksheet.write(row, inc(), 'User Id', boldStyle)
    worksheet1.write(row, i, 'User Id', boldStyle)

    sh1.write(row, inc(), 'User name', boldStyle)
    sh1.write(row, inc(), 'Order Id', boldStyle)
    sh1.write(row, inc(), 'Created On', boldStyle)
    sh1.write(row, inc(), 'Store Id', boldStyle)
    sh1.write(row, inc(), 'Merchant Order Id', boldStyle)
    sh1.write(row, inc(), 'Status', boldStyle)
    sh1.write(row, inc(), 'Product title', boldStyle)
    sh1.write(row, inc(), 'Referrer', boldStyle)
    sh1.write(row, inc(), 'Amount Paid', boldStyle)
    sh1.write(row, inc(), 'Catalog Id', boldStyle)
    sh1.write(row, inc(), 'Brand', boldStyle)
    sh1.write(row, inc(), 'Model', boldStyle)
    sh1.write(row, inc(), 'Category', boldStyle)
    
    row=0

                    sh1.write(row3, inc(), result[-3])
                    sh1.write(row3, inc(), result[0])
                    sh1.write(row3, inc(), result[-4], datetime_format)
                    sh1.write(row3, inc(), sourceMap.get(result[2]))
                    sh1.write(row3, inc(), morder.get("merchantOrderId"))
                    sh1.write(row3, inc(), subOrder.get("status"))
                    sh1.write(row3, inc(), subOrder.get("productTitle"))
                    sh1.write(row3, inc(), result[-1])
                    inc()
                    if int(subOrder.get("quantity")) > 0:
                        sh1.write(row3, i, int(subOrder.get("amountPaid"))/int(subOrder.get("quantity")))

                    if morder.get("storeId") in (1,2,4,5):
                        skuData = db1.MasterData.find_one({'identifier':subOrder.get("productCode").strip(), 'source_id':morder.get("storeId")})
                    elif morder.get("storeId") == 3:
                        skuData = db1.MasterData.find_one({'secondaryIdentifier':subOrder.get("productCode").strip(), 'source_id':morder.get("storeId")})
                    if skuData is not None:
                        sh1.write(row3, inc(), skuData.get("skuBundleId"))
                        sh1.write(row3, inc(), skuData.get("brand"))
                        sh1.write(row3, inc(), skuData.get("model_name"))
                        sh1.write(row3, inc(), skuData.get("category"))
                    if subOrder.get("cashBackStatus") == Store.CB_APPROVED or subOrder.get("cashBackStatus") == Store.CB_PENDING:
                        row +=1

    
    generateSnapDealReco()
    generateFlipkartReco()
    generateAmazonReco(XLS_A_FILENAME)
    getUserOrders()
    #sendmail(["amit.gupta@shop2020.in","rajneesh.arora@saholic.com"], "", "Affiliate Reco", XLS_FILENAME, XLS_F_FILENAME, XLS_A_FILENAME)
    #sendmail(["amit.gupta@shop2020.in", "rajneesh.arora@saholic.com", "chaitnaya.vats@saholic.com", "manoj.kumar@saholic.com"], "", "All DTR Orders", XLS_O_FILENAME)
 
if __name__ == '__main__':
    main()
    


Subversion Repositories SmartDukaan

(root)/trunk/PyDTR/src/dtr/reports/affiliatereco.py – Rev 14740 → 14771

Rev 14740		Rev 14771
Line 67...		Line 67...
67	`worksheet1.write(row, i, 'User Id', boldStyle)`	67	`worksheet1.write(row, i, 'User Id', boldStyle)`
68	`worksheet2.write(row, i, 'User Id', boldStyle)`	68	`worksheet2.write(row, i, 'User Id', boldStyle)`
69	`worksheet.write(row, inc(), 'Username', boldStyle)`	69	`worksheet.write(row, inc(), 'Username', boldStyle)`
70	`worksheet1.write(row, i, 'Username', boldStyle)`	70	`worksheet1.write(row, i, 'Username', boldStyle)`
71	`worksheet2.write(row, i, 'Username', boldStyle)`	71	`worksheet2.write(row, i, 'Username', boldStyle)`
-		72	`worksheet.write(row, inc(), 'Version code', boldStyle)`
-		73	`worksheet1.write(row, i, 'Version code', boldStyle)`
-		74	`worksheet2.write(row, i, 'Version code', boldStyle)`
-		75	`worksheet.write(row, inc(), 'Device', boldStyle)`
-		76	`worksheet1.write(row, i, 'Device', boldStyle)`
-		77	`worksheet2.write(row, i, 'Device', boldStyle)`
72	`worksheet.write(row, inc(), 'Merchant Order Id', boldStyle)`	78	`worksheet.write(row, inc(), 'Merchant Order Id', boldStyle)`
73	`worksheet1.write(row, i, 'Merchant Order Id', boldStyle)`	79	`worksheet1.write(row, i, 'Merchant Order Id', boldStyle)`
74	`worksheet2.write(row, i, 'Merchant Order Id', boldStyle)`	80	`worksheet2.write(row, i, 'Merchant Order Id', boldStyle)`
75	`worksheet.write(row, inc(), 'Product Title', boldStyle)`	81	`worksheet.write(row, inc(), 'Product Title', boldStyle)`
76	`worksheet1.write(row, i, 'Product Title', boldStyle)`	82	`worksheet1.write(row, i, 'Product Title', boldStyle)`
Line 120...		Line 126...
120	`row += 1`	126	`row += 1`
121	`i=-1`	127	`i=-1`
122	`worksheet.write(row, inc(), orderId)`	128	`worksheet.write(row, inc(), orderId)`
123	`worksheet.write(row, inc(), row1[1])`	129	`worksheet.write(row, inc(), row1[1])`
124	`worksheet.write(row, inc(), row1[-1])`	130	`worksheet.write(row, inc(), row1[-1])`
-		131	`worksheet.write(row, inc(), row1[-2])`
-		132	`worksheet.write(row, inc(), row1[-3])`
125	`worksheet.write(row, inc(), order['merchantOrderId'])`	133	`worksheet.write(row, inc(), order['merchantOrderId'])`
126	`worksheet.write(row, inc(), subOrder['productTitle'])`	134	`worksheet.write(row, inc(), subOrder['productTitle'])`
127	`worksheet.write(row, inc(), subOrder['amountPaid'])`	135	`worksheet.write(row, inc(), subOrder['amountPaid'])`
128	`worksheet.write(row, inc(), subOrder['quantity'])`	136	`worksheet.write(row, inc(), subOrder['quantity'])`
129	`worksheet.write(row, inc(), subOrder['status'])`	137	`worksheet.write(row, inc(), subOrder['status'])`
Line 138...		Line 146...
138	`row2 += 1`	146	`row2 += 1`
139	`i = -1`	147	`i = -1`
140	`worksheet1.write(row2, inc(), orderId)`	148	`worksheet1.write(row2, inc(), orderId)`
141	`worksheet1.write(row2, inc(), row1[1])`	149	`worksheet1.write(row2, inc(), row1[1])`
142	`worksheet1.write(row2, inc(), row1[-1])`	150	`worksheet1.write(row2, inc(), row1[-1])`
-		151	`worksheet1.write(row2, inc(), row1[-2])`
-		152	`worksheet1.write(row2, inc(), row1[-3])`
143	`worksheet1.write(row2, inc(), order['merchantOrderId'])`	153	`worksheet1.write(row2, inc(), order['merchantOrderId'])`
144	`worksheet1.write(row2, inc(), subOrder['productTitle'])`	154	`worksheet1.write(row2, inc(), subOrder['productTitle'])`
145	`worksheet1.write(row2, inc(), subOrder['amountPaid'])`	155	`worksheet1.write(row2, inc(), subOrder['amountPaid'])`
146	`worksheet1.write(row2, inc(), subOrder['quantity'])`	156	`worksheet1.write(row2, inc(), subOrder['quantity'])`
147	`worksheet1.write(row2, inc(), subOrder['status'])`	157	`worksheet1.write(row2, inc(), subOrder['status'])`
Line 167...		Line 177...
167	`row3 += 1`	177	`row3 += 1`
168	`i=-1`	178	`i=-1`
169	`worksheet2.write(row3, inc(), orderId)`	179	`worksheet2.write(row3, inc(), orderId)`
170	`worksheet2.write(row3, inc(), row1[1])`	180	`worksheet2.write(row3, inc(), row1[1])`
171	`worksheet2.write(row3, inc(), row1[-1])`	181	`worksheet2.write(row3, inc(), row1[-1])`
-		182	`worksheet2.write(row3, inc(), row1[-2])`
-		183	`worksheet2.write(row3, inc(), row1[-3])`
172	`worksheet2.write(row3, inc(), order['merchantOrderId'])`	184	`worksheet2.write(row3, inc(), order['merchantOrderId'])`
173	`worksheet2.write(row3, inc(), subOrder['productTitle'])`	185	`worksheet2.write(row3, inc(), subOrder['productTitle'])`
174	`worksheet2.write(row3, inc(), subOrder['amountPaid'])`	186	`worksheet2.write(row3, inc(), subOrder['amountPaid'])`
175	`worksheet2.write(row3, inc(), subOrder['quantity'])`	187	`worksheet2.write(row3, inc(), subOrder['quantity'])`
176	`worksheet2.write(row3, inc(), subOrder['status'])`	188	`worksheet2.write(row3, inc(), subOrder['status'])`
Line 241...		Line 253...
241	`i=-1`	253	`i=-1`
242	`worksheet.write(row, inc(), orderId)`	254	`worksheet.write(row, inc(), orderId)`
243	`worksheet1.write(anotherrow, i, orderId)`	255	`worksheet1.write(anotherrow, i, orderId)`
244	`worksheet.write(row, inc(), row1[1])`	256	`worksheet.write(row, inc(), row1[1])`
245	`worksheet1.write(anotherrow, i, row1[1])`	257	`worksheet1.write(anotherrow, i, row1[1])`
-		258	`worksheet.write(row, inc(), row1[-3])`
-		259	`worksheet1.write(anotherrow, i, row1[-3])`
246	`worksheet.write(row, inc(), row1[-2])`	260	`worksheet.write(row, inc(), row1[-2])`
247	`worksheet1.write(anotherrow, i, row1[-2])`	261	`worksheet1.write(anotherrow, i, row1[-2])`
248	`worksheet.write(row, inc(), row1[-1])`	262	`worksheet.write(row, inc(), row1[-1])`
249	`worksheet1.write(anotherrow, i, row1[-1])`	263	`worksheet1.write(anotherrow, i, row1[-1])`
250	`worksheet.write(row, inc(), order['merchantOrderId'])`	264	`worksheet.write(row, inc(), order['merchantOrderId'])`
Line 285...		Line 299...
285		299
286	`else:`	300	`else:`
287	`i=-1`	301	`i=-1`
288	`worksheet1.write(anotherrow, inc(), orderId)`	302	`worksheet1.write(anotherrow, inc(), orderId)`
289	`worksheet1.write(anotherrow, inc(), row1[1])`	303	`worksheet1.write(anotherrow, inc(), row1[1])`
-		304	`worksheet1.write(anotherrow, inc(), row1[-3])`
290	`worksheet1.write(anotherrow, inc(), row1[-2])`	305	`worksheet1.write(anotherrow, inc(), row1[-2])`
291	`worksheet1.write(anotherrow, inc(), row1[-1])`	306	`worksheet1.write(anotherrow, inc(), row1[-1])`
292	`worksheet1.write(anotherrow, inc(), order['merchantOrderId'])`	307	`worksheet1.write(anotherrow, inc(), order['merchantOrderId'])`
293	`worksheet1.write(anotherrow, inc(), order['subTagId'])`	308	`worksheet1.write(anotherrow, inc(), order['subTagId'])`
294	`worksheet1.write(anotherrow, inc(), order['placedOn'])`	309	`worksheet1.write(anotherrow, inc(), order['placedOn'])`
Line 296...		Line 311...
296	`timestamp1 = int(time.mktime(datetime.strptime(order['placedOn'], "%b %d, %Y, %I:%M %p").timetuple()))`	311	`timestamp1 = int(time.mktime(datetime.strptime(order['placedOn'], "%b %d, %Y, %I:%M %p").timetuple()))`
297	`notReconciled[orderId] = (anotherrow, i, int(order['paidAmount']), timestamp1, order["subTagId"])`	312	`notReconciled[orderId] = (anotherrow, i, int(order['paidAmount']), timestamp1, order["subTagId"])`
298	`inc()`	313	`inc()`
299	`inc()`	314	`inc()`
300	`inc()`	315	`inc()`
-		316	`inc()`
301	`k = i`	317	`k = i`
302	`anotherrow -= 1`	318	`anotherrow -= 1`
303	`for subOrder in order['subOrders']:`	319	`for subOrder in order['subOrders']:`
304	`anotherrow += 1`	320	`anotherrow += 1`
305	`i = k`	321	`i = k`
Line 341...		Line 357...
341	`for order in db.merchantOrder.find({"orderId":row1[0]}):`	357	`for order in db.merchantOrder.find({"orderId":row1[0]}):`
342	`row2 += 1`	358	`row2 += 1`
343	`i=-1`	359	`i=-1`
344	`worksheet2.write(row2, inc(), order["orderId"])`	360	`worksheet2.write(row2, inc(), order["orderId"])`
345	`worksheet2.write(row2, inc(), row1[1])`	361	`worksheet2.write(row2, inc(), row1[1])`
-		362	`worksheet2.write(row2, inc(), row1[-3])`
346	`worksheet2.write(row2, inc(), row1[-2])`	363	`worksheet2.write(row2, inc(), row1[-2])`
347	`worksheet2.write(row2, inc(), row1[-1])`	364	`worksheet2.write(row2, inc(), row1[-1])`
348	`worksheet2.write(row2, inc(), order['merchantOrderId'])`	365	`worksheet2.write(row2, inc(), order['merchantOrderId'])`
349	`worksheet2.write(row2, inc(), order['subTagId'])`	366	`worksheet2.write(row2, inc(), order['subTagId'])`
350	`worksheet2.write(row2, inc(), order['placedOn'])`	367	`worksheet2.write(row2, inc(), order['placedOn'])`
Line 415...		Line 432...
415	`global i`	432	`global i`
416	`i=-1`	433	`i=-1`
417	`worksheet.write(row, inc(), 'Order Id', boldStyle)`	434	`worksheet.write(row, inc(), 'Order Id', boldStyle)`
418	`worksheet.write(row, inc(), 'User Id', boldStyle)`	435	`worksheet.write(row, inc(), 'User Id', boldStyle)`
419	`worksheet.write(row, inc(), 'Version Code', boldStyle)`	436	`worksheet.write(row, inc(), 'Version Code', boldStyle)`
-		437	`worksheet.write(row, inc(), 'Device', boldStyle)`
420	`worksheet.write(row, inc(), 'Username', boldStyle)`	438	`worksheet.write(row, inc(), 'Username', boldStyle)`
421	`worksheet.write(row, inc(), 'Merchant Order Id', boldStyle)`	439	`worksheet.write(row, inc(), 'Merchant Order Id', boldStyle)`
422	`worksheet.write(row, inc(), 'SubtagId', boldStyle)`	440	`worksheet.write(row, inc(), 'SubtagId', boldStyle)`
423	`worksheet.write(row, inc(), 'Sale Date', boldStyle)`	441	`worksheet.write(row, inc(), 'Sale Date', boldStyle)`
424	`worksheet.write(row, inc(), 'Sale Amount', boldStyle)`	442	`worksheet.write(row, inc(), 'Sale Amount', boldStyle)`
Line 435...		Line 453...
435		453
436	`i = -1`	454	`i = -1`
437	`worksheet1.write(row, inc(), 'Order Id', boldStyle)`	455	`worksheet1.write(row, inc(), 'Order Id', boldStyle)`
438	`worksheet1.write(row, inc(), 'User Id', boldStyle)`	456	`worksheet1.write(row, inc(), 'User Id', boldStyle)`
439	`worksheet1.write(row, inc(), 'Version Code', boldStyle)`	457	`worksheet1.write(row, inc(), 'Version Code', boldStyle)`
-		458	`worksheet1.write(row, inc(), 'Device', boldStyle)`
440	`worksheet1.write(row, inc(), 'Username', boldStyle)`	459	`worksheet1.write(row, inc(), 'Username', boldStyle)`
441	`worksheet1.write(row, inc(), 'Merchant Order Id', boldStyle)`	460	`worksheet1.write(row, inc(), 'Merchant Order Id', boldStyle)`
442	`worksheet1.write(row, inc(), 'SubtagId', boldStyle)`	461	`worksheet1.write(row, inc(), 'SubtagId', boldStyle)`
443	`worksheet1.write(row, inc(), 'Sale Date', boldStyle)`	462	`worksheet1.write(row, inc(), 'Sale Date', boldStyle)`
444	`worksheet1.write(row, inc(), 'Sale Amount', boldStyle)`	463	`worksheet1.write(row, inc(), 'Sale Amount', boldStyle)`
Line 455...		Line 474...
455		474
456	`i = -1`	475	`i = -1`
457	`worksheet2.write(row, inc(), 'Order Id', boldStyle)`	476	`worksheet2.write(row, inc(), 'Order Id', boldStyle)`
458	`worksheet2.write(row, inc(), 'User Id', boldStyle)`	477	`worksheet2.write(row, inc(), 'User Id', boldStyle)`
459	`worksheet2.write(row, inc(), 'Version Code', boldStyle)`	478	`worksheet2.write(row, inc(), 'Version Code', boldStyle)`
-		479	`worksheet2.write(row, inc(), 'Device', boldStyle)`
460	`worksheet2.write(row, inc(), 'Username', boldStyle)`	480	`worksheet2.write(row, inc(), 'Username', boldStyle)`
461	`worksheet2.write(row, inc(), 'Merchant Order Id', boldStyle)`	481	`worksheet2.write(row, inc(), 'Merchant Order Id', boldStyle)`
462	`worksheet2.write(row, inc(), 'SubtagId', boldStyle)`	482	`worksheet2.write(row, inc(), 'SubtagId', boldStyle)`
463	`worksheet2.write(row, inc(), 'Sale Date', boldStyle)`	483	`worksheet2.write(row, inc(), 'Sale Date', boldStyle)`
464	`worksheet2.write(row, inc(), 'Sale Amount', boldStyle)`	484	`worksheet2.write(row, inc(), 'Sale Amount', boldStyle)`
Line 490...		Line 510...
490	`workbook = xlwt.Workbook()`	510	`workbook = xlwt.Workbook()`
491	`sh1 = workbook.add_sheet("All Orders")`	511	`sh1 = workbook.add_sheet("All Orders")`
492		512
493	`db=client.Dtr`	513	`db=client.Dtr`
494	`results = Mysql.fetchResult('''`	514	`results = Mysql.fetchResult('''`
495	`select ow., u.username, crm1.order_count, u.referrer from order_view ow left join users u on u.id = ow.user_id left join (select user_id, count() as order_count from order_view where status = 'ORDER_CREATED' group by user_id) as crm1 on ow.user_id = crm1.user_id where lower(u.referrer) not like 'emp%'`	515	`select ow., u.username, crm1.order_count, u.referrer from order_view ow left join users u on u.id = ow.user_id left join (select user_id, count() as order_count from order_view where status = 'ORDER_CREATED' group by user_id) as crm1 on ow.user_id = crm1.user_id where lower(u.referrer) not like 'emp%' or u.referrer is null;`
496	`''',)`	516	`''',)`
497	`row = 0`	517	`row = 0`
498	`i=-1`	518	`i=-1`
499	`worksheet.write(row, inc(), 'User Id', boldStyle)`	519	`worksheet.write(row, inc(), 'User Id', boldStyle)`
500	`worksheet1.write(row, i, 'User Id', boldStyle)`	520	`worksheet1.write(row, i, 'User Id', boldStyle)`
Line 529...		Line 549...
529	`sh1.write(row, inc(), 'User name', boldStyle)`	549	`sh1.write(row, inc(), 'User name', boldStyle)`
530	`sh1.write(row, inc(), 'Order Id', boldStyle)`	550	`sh1.write(row, inc(), 'Order Id', boldStyle)`
531	`sh1.write(row, inc(), 'Created On', boldStyle)`	551	`sh1.write(row, inc(), 'Created On', boldStyle)`
532	`sh1.write(row, inc(), 'Store Id', boldStyle)`	552	`sh1.write(row, inc(), 'Store Id', boldStyle)`
533	`sh1.write(row, inc(), 'Merchant Order Id', boldStyle)`	553	`sh1.write(row, inc(), 'Merchant Order Id', boldStyle)`
-		554	`sh1.write(row, inc(), 'Status', boldStyle)`
534	`sh1.write(row, inc(), 'Product title', boldStyle)`	555	`sh1.write(row, inc(), 'Product title', boldStyle)`
535	`sh1.write(row, inc(), 'Referrer', boldStyle)`	556	`sh1.write(row, inc(), 'Referrer', boldStyle)`
536	`sh1.write(row, inc(), 'Amount Paid', boldStyle)`	557	`sh1.write(row, inc(), 'Amount Paid', boldStyle)`
-		558	`sh1.write(row, inc(), 'Catalog Id', boldStyle)`
537	`sh1.write(row, inc(), 'Brand', boldStyle)`	559	`sh1.write(row, inc(), 'Brand', boldStyle)`
538	`sh1.write(row, inc(), 'Model', boldStyle)`	560	`sh1.write(row, inc(), 'Model', boldStyle)`
539	`sh1.write(row, inc(), 'Category', boldStyle)`	561	`sh1.write(row, inc(), 'Category', boldStyle)`
540		562
541	`row=0`	563	`row=0`
Line 554...		Line 576...
554	`sh1.write(row3, inc(), result[-3])`	576	`sh1.write(row3, inc(), result[-3])`
555	`sh1.write(row3, inc(), result[0])`	577	`sh1.write(row3, inc(), result[0])`
556	`sh1.write(row3, inc(), result[-4], datetime_format)`	578	`sh1.write(row3, inc(), result[-4], datetime_format)`
557	`sh1.write(row3, inc(), sourceMap.get(result[2]))`	579	`sh1.write(row3, inc(), sourceMap.get(result[2]))`
558	`sh1.write(row3, inc(), morder.get("merchantOrderId"))`	580	`sh1.write(row3, inc(), morder.get("merchantOrderId"))`
-		581	`sh1.write(row3, inc(), subOrder.get("status"))`
559	`sh1.write(row3, inc(), subOrder.get("productTitle"))`	582	`sh1.write(row3, inc(), subOrder.get("productTitle"))`
560	`sh1.write(row3, inc(), result[-1])`	583	`sh1.write(row3, inc(), result[-1])`
561	`inc()`	584	`inc()`
562	`if int(subOrder.get("quantity")) > 0:`	585	`if int(subOrder.get("quantity")) > 0:`
563	`sh1.write(row3, i, int(subOrder.get("amountPaid"))/int(subOrder.get("quantity")))`	586	`sh1.write(row3, i, int(subOrder.get("amountPaid"))/int(subOrder.get("quantity")))`
Line 565...		Line 588...
565	`if morder.get("storeId") in (1,2,4,5):`	588	`if morder.get("storeId") in (1,2,4,5):`
566	`skuData = db1.MasterData.find_one({'identifier':subOrder.get("productCode").strip(), 'source_id':morder.get("storeId")})`	589	`skuData = db1.MasterData.find_one({'identifier':subOrder.get("productCode").strip(), 'source_id':morder.get("storeId")})`
567	`elif morder.get("storeId") == 3:`	590	`elif morder.get("storeId") == 3:`
568	`skuData = db1.MasterData.find_one({'secondaryIdentifier':subOrder.get("productCode").strip(), 'source_id':morder.get("storeId")})`	591	`skuData = db1.MasterData.find_one({'secondaryIdentifier':subOrder.get("productCode").strip(), 'source_id':morder.get("storeId")})`
569	`if skuData is not None:`	592	`if skuData is not None:`
-		593	`sh1.write(row3, inc(), skuData.get("skuBundleId"))`
570	`sh1.write(row3, inc(), skuData.get("brand"))`	594	`sh1.write(row3, inc(), skuData.get("brand"))`
571	`sh1.write(row3, inc(), skuData.get("model_name"))`	595	`sh1.write(row3, inc(), skuData.get("model_name"))`
572	`sh1.write(row3, inc(), skuData.get("category"))`	596	`sh1.write(row3, inc(), skuData.get("category"))`
573	`if subOrder.get("cashBackStatus") == Store.CB_APPROVED or subOrder.get("cashBackStatus") == Store.CB_PENDING:`	597	`if subOrder.get("cashBackStatus") == Store.CB_APPROVED or subOrder.get("cashBackStatus") == Store.CB_PENDING:`
574	`row +=1`	598	`row +=1`
Line 638...		Line 662...
638		662
639	`generateSnapDealReco()`	663	`generateSnapDealReco()`
640	`generateFlipkartReco()`	664	`generateFlipkartReco()`
641	`generateAmazonReco(XLS_A_FILENAME)`	665	`generateAmazonReco(XLS_A_FILENAME)`
642	`getUserOrders()`	666	`getUserOrders()`
643	`sendmail(["amit.gupta@shop2020.in","rajneesh.arora@saholic.com"], "", "Affiliate Reco", XLS_FILENAME, XLS_F_FILENAME, XLS_A_FILENAME)`	667	`#sendmail(["amit.gupta@shop2020.in","rajneesh.arora@saholic.com"], "", "Affiliate Reco", XLS_FILENAME, XLS_F_FILENAME, XLS_A_FILENAME)`
644	`sendmail(["amit.gupta@shop2020.in", "rajneesh.arora@saholic.com", "chaitnaya.vats@saholic.com", "manoj.kumar@saholic.com"], "", "All DTR Orders", XLS_O_FILENAME)`	668	`#sendmail(["amit.gupta@shop2020.in", "rajneesh.arora@saholic.com", "chaitnaya.vats@saholic.com", "manoj.kumar@saholic.com"], "", "All DTR Orders", XLS_O_FILENAME)`
645		669
646	`if __name__ == '__main__':`	670	`if __name__ == '__main__':`
647	`main()`	671	`main()`
648		672
649		673