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

 PASSWORD = "5h0p2o2o"
 SUBJECT = "DTR User Segmentation report for " + date.today().isoformat()
 SMTP_SERVER = "smtp.gmail.com"
 SMTP_PORT = 587
-XLS_FILENAME = "dtrsourcesreco.xls"
+XLS_FILENAME = "snapdealaffiliatereco.xls"
 XLS_O_FILENAME = "allorders.xls"
+XLS_F_FILENAME = "flipkartaffiliatereco.xls"
 boldStyle = xlwt.XFStyle()
 f = xlwt.Font()
 f.bold = True
 boldStyle.font = f
 i = -1
 def generateFlipkartReco():
     global i
     i = -1
     curs = getOrdersAfterDate(datetime.now() - timedelta(days=30), 2)
     db = client.Dtr
+    wb = xlwt.Workbook()
-    rb = open_workbook(XLS_FILENAME)
+    #rb = open_workbook(XLS_F_FILENAME)
-    wb = copy(rb)
+    #wb = copy(rb)
     #wb = xlwt.Workbook()
     row = 0
+    row2 = 0
+    row3 = 0
+    row4 = 0
-    worksheet = wb.add_sheet("Flipkart")
+    worksheet = wb.add_sheet("All Orders")
+    worksheet1 = wb.add_sheet("Orders Reconciled")
+    worksheet2 = wb.add_sheet("Orders not Reconciled")
+    worksheet3 = wb.add_sheet("Aff not Reconciled")
     worksheet.write(row, inc(), 'Order Id', boldStyle)
+    worksheet1.write(row, i, 'Order Id', boldStyle)
+    worksheet2.write(row, i, 'Order Id', boldStyle)
     worksheet.write(row, inc(), 'User Id', boldStyle)
+    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(), '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)
+    worksheet2.write(row, i, 'Product Title', boldStyle)
     worksheet.write(row, inc(), 'Price', boldStyle)
+    worksheet1.write(row, i, 'Price', boldStyle)
+    worksheet2.write(row, i, 'Price', boldStyle)
     worksheet.write(row, inc(), 'Quantity', boldStyle)
+    worksheet1.write(row, i, 'Quantity', boldStyle)
+    worksheet2.write(row, i, 'Quantity', boldStyle)
     worksheet.write(row, inc(), 'Status', boldStyle)
+    worksheet1.write(row, i, 'Status', boldStyle)
+    worksheet2.write(row, i, 'Status', boldStyle)
     worksheet.write(row, inc(), 'Detailed Status', boldStyle)
+    worksheet1.write(row, i, 'Detailed Status', boldStyle)
+    worksheet2.write(row, i, 'Detailed Status', boldStyle)
     worksheet.write(row, inc(), 'Cashback Status', boldStyle)
+    worksheet1.write(row, i, 'Cashback Status', boldStyle)
+    worksheet2.write(row, i, 'Cashback Status', boldStyle)
     worksheet.write(row, inc(), 'Our CashBack', boldStyle)
+    worksheet1.write(row, i, 'Our CashBack', boldStyle)
+    worksheet2.write(row, i, 'Our CashBack', boldStyle)
     worksheet.write(row, inc(), 'Sale Date', boldStyle)
+    worksheet1.write(row, i, 'Sale Date', boldStyle)
+    worksheet2.write(row, i, 'Sale Date', boldStyle)
     worksheet.write(row, inc(), 'SubtagId', boldStyle)
+    worksheet1.write(row, i, 'SubtagId', boldStyle)
+    worksheet2.write(row, i, 'SubtagId', boldStyle)
     worksheet.write(row, inc(), 'Category', boldStyle)
+    worksheet1.write(row, i, 'Category', boldStyle)
     worksheet.write(row, inc(), 'Aff PayOut', boldStyle)
+    worksheet1.write(row, i, 'Aff PayOut', boldStyle)
     worksheet.write(row, inc(), 'Aff Sale Amount', boldStyle)
+    worksheet1.write(row, i, 'Aff Sale Amount', boldStyle)
     worksheet.write(row, inc(), 'Aff Sale Date', boldStyle)
+    worksheet1.write(row, i, 'Aff Sale Date', boldStyle)
     for row1 in curs:
         orderId = row1[0]
         order = db.merchantOrder.find_one({"orderId":orderId})
         if order is None:
             continue
         saleTime = int(time.mktime(datetime.strptime(order['placedOn'], "%d %B, %Y %I:%M %p").replace(hour=0, minute=0, second=0, microsecond=0).timetuple()))
         #saleTime1 = int(time.mktime((datetime.strptime(order['placedOn'], "%b %d, %Y %I:%M %p") + timedelta(seconds=-60)).timetuple()))
+        reconciled_affiliates = []
         for subOrder in order['subOrders']:
-            affs = list(db.flipkartOrderAffiliateInfo.find({"subOrders.productCode":subOrder.get("productCode"), "saleDateInt":saleTime}))
+            affs = list(db.flipkartOrderAffiliateInfo.find({"productCode":subOrder.get("productCode"), "saleDateInt":saleTime}))
             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(), subOrder['cashBackStatus'])
             worksheet.write(row, inc(), subOrder['cashBackAmount'])
             worksheet.write(row, inc(), subOrder['placedOn'])
             worksheet.write(row, inc(), order['subTagId'])
             for aff in affs:
+                if aff["_id"] not in reconciled_affiliates and aff['subTagId']== order['subTagId']:
+                    reconciled_affiliates.append(aff["_id"])
+                    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(), 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'])
+                    worksheet1.write(row2, inc(), subOrder['detailedStatus'])
+                    worksheet1.write(row2, inc(), subOrder['cashBackStatus'])
+                    worksheet1.write(row2, inc(), subOrder['cashBackAmount'])
+                    worksheet1.write(row2, inc(), subOrder['placedOn'])
-                if aff['subTagId']== subOrder['subTagId']:
+                    worksheet1.write(row2, inc(), order['subTagId'])
+                    db.flipkartOrderAffiliateInfo.update(
+                        {"productCode":subOrder.get("productCode"), "saleDateInt":saleTime, "subTagId":aff['subTagId']},
+                        {"$set":{"reconciled":True}})
+                    db.merchantOrder.update({"merchantOrderId":order["merchantOrderId"]},
+                                            {"$set":{"reconciled":True}})
                     worksheet.write(row, inc(), aff['category'])
+                    worksheet1.write(row2, i, aff['category'])
                     worksheet.write(row, inc(), aff['payOut'])
+                    worksheet1.write(row2, i, aff['payOut'])
                     worksheet.write(row, inc(), aff['saleAmount'])
+                    worksheet1.write(row2, i, aff['saleAmount'])
                     worksheet.write(row, inc(), aff['saleDate'])
+                    worksheet1.write(row2, i, aff['saleDate'])
                     break
+            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(), 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'])
+            worksheet2.write(row3, inc(), subOrder['detailedStatus'])
+            worksheet2.write(row3, inc(), subOrder['cashBackStatus'])
+            worksheet2.write(row3, inc(), subOrder['cashBackAmount'])
+            worksheet2.write(row3, inc(), subOrder['placedOn'])
+            worksheet2.write(row3, inc(), order['subTagId'])
+    i=-1
+    worksheet3.write(0, inc(), "category",boldStyle)
+    worksheet3.write(0, inc(), "conversionStatus",boldStyle)
+    worksheet3.write(0, inc(), "Product Title",boldStyle)
+    worksheet3.write(0, inc(), "price",boldStyle)
+    worksheet3.write(0, inc(), "quantity",boldStyle)
+    worksheet3.write(0, inc(), "payOut",boldStyle)
+    worksheet3.write(0, inc(), "saleDate",boldStyle)
+    worksheet3.write(0, inc(), "subTagId",boldStyle)
+    for aff in db.flipkartOrderAffiliateInfo.find({"reconciled":{"$exists":False}}):
+        row4 += 1
+        i =-1
+        worksheet3.write(row4, inc(), aff["category"])
+        worksheet3.write(row4, inc(), aff["conversionStatus"])
+        skuData = getSkuData(2, aff["productCode"])
+        if skuData is None:
+            worksheet3.write(row4, inc(), aff["productTitle"])
+        else:
+            worksheet3.write(row4, inc(), skuData["product_name"])
+        worksheet3.write(row4, inc(), aff['price'])
+        worksheet3.write(row4, inc(), aff['quantity'])
+        worksheet3.write(row4, inc(), aff['payOut'])
+        worksheet3.write(row4, inc(), aff['saleDate'])
+        worksheet3.write(row4, inc(), aff['subTagId'])
-        wb.save(XLS_FILENAME)
+    wb.save(XLS_F_FILENAME)
 def generateSnapDealReco():
     global i
     curs = getOrdersAfterDate(datetime.now() - timedelta(days=30), 3)
     db = client.Dtr
     notReconciled = {}
         affNotReconciledSheet.write(row, inc(), offer.get("conversionStatus"))
         affNotReconciledSheet.write(row, inc(), offer.get("ip"))
         workbook.save(XLS_FILENAME)
-def sendmail(email, message, title, fileName):
+def sendmail(email, message, title, *varargs):
     if email == "":
         return
     mailServer = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)
     mailServer.ehlo()
     mailServer.starttls()
     msg.preamble = title
     html_msg = MIMEText(message, 'html')
     msg.attach(html_msg)
     #snapdeal more to be added here
+    for fileName in varargs:
-    snapdeal = MIMEBase('application', 'vnd.ms-excel')
+        snapdeal = MIMEBase('application', 'vnd.ms-excel')
-    snapdeal.set_payload(file(fileName).read())
+        snapdeal.set_payload(file(fileName).read())
-    encoders.encode_base64(snapdeal)
+        encoders.encode_base64(snapdeal)
-    snapdeal.add_header('Content-Disposition', 'attachment;filename=' + fileName)
+        snapdeal.add_header('Content-Disposition', 'attachment;filename=' + fileName)
-    msg.attach(snapdeal)
+        msg.attach(snapdeal)
     email.append('amit.gupta@shop2020.in')
     MAILTO = email
     mailServer.login(SENDER, PASSWORD)
                     sh1.write(row3, inc(), morder.get("merchantOrderId"))
                     sh1.write(row3, inc(), subOrder.get("productTitle"))
                     inc()
                     if int(subOrder.get("quantity")) > 0:
                         sh1.write(row3, i, int(subOrder.get("amountPaid"))/int(subOrder.get("quantity")))
+                    skuData = getSkuData(morder.get("storeId"), subOrder.get("productCode"))
-                        if morder.get("storeId") in (1,2,4,5):
+                    if morder.get("storeId") in (1,2,4,5):
-                            skuData = db1.MasterData.find_one({'identifier':subOrder.get("productCode").strip(), 'source_id':morder.get("storeId")})
+                        skuData = db1.MasterData.find_one({'identifier':subOrder.get("productCode").strip(), 'source_id':morder.get("storeId")})
-                        elif morder.get("storeId") == 3:
+                    elif morder.get("storeId") == 3:
-                            skuData = db1.MasterData.find_one({'secondaryIdentifier':subOrder.get("productCode").strip(), 'source_id':morder.get("storeId")})
+                        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("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:
                                     print "Could not find", morder.get("adId"), "that is reconcired for order", morder.get("orderId")
     wb.save(XLS_FILENAME)
     workbook.save(XLS_O_FILENAME)
+def getSkuData(storeId, identifier):
+    if storeId in (1,2,4,5):
+        skuData = client.Catalog.MasterData.find_one({'identifier':identifier, 'source_id':storeId})
+    elif storeId == 3:
+        skuData = client.Catalog.MasterData.find_one({'secondaryIdentifier':identifier, 'source_id':storeId})
+    return skuData
 def inc():
     global i
     i+=1
     return i
 def addHeaders(sheet):
     global i
     i = 0
     sheet.write(0, inc(), )
 def main():
     generateSnapDealReco()
+    generateFlipkartReco()
     getUserOrders()
-    sendmail(["amit.gupta@shop2020.in", "rajneesh.arora@saholic.com"], "", "DTR Affiliate Reco", XLS_FILENAME)
+    sendmail(["amit.gupta@shop2020.in", "rajneesh.arora@saholic.com"], "", "Affiliate Reco", XLS_FILENAME, XLS_O_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 14634 → 14647