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

 Created on Mar 13, 2015
 @author: amit
 '''
 from datetime import date, datetime, timedelta
-from dtr.main import Store
+from dtr.main import Store, sourceMap
 from dtr.storage import Mysql
 from dtr.storage.Mysql import getOrdersAfterDate, getOrdersByTag
 from email import encoders
 from email.mime.base import MIMEBase
 from email.mime.multipart import MIMEMultipart
 SUBJECT = "DTR User Segmentation report for " + date.today().isoformat()
 SMTP_SERVER = "smtp.gmail.com"
 SMTP_PORT = 587
 XLS_FILENAME = "dtrsourcesreco.xls"
+XLS_O_FILENAME = "allorders.xls"
 boldStyle = xlwt.XFStyle()
 f = xlwt.Font()
 f.bold = True
 boldStyle.font = f
 i = -1
         affNotReconciledSheet.write(row, inc(), offer.get("conversionStatus"))
         affNotReconciledSheet.write(row, inc(), offer.get("ip"))
         workbook.save(XLS_FILENAME)
-def sendmail(email, message, title):
+def sendmail(email, message, title, fileName):
     if email == "":
         return
     mailServer = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)
     mailServer.ehlo()
     mailServer.starttls()
     html_msg = MIMEText(message, 'html')
     msg.attach(html_msg)
     #snapdeal more to be added here
     snapdeal = MIMEBase('application', 'vnd.ms-excel')
-    snapdeal.set_payload(file(XLS_FILENAME).read())
+    snapdeal.set_payload(file(fileName).read())
     encoders.encode_base64(snapdeal)
-    snapdeal.add_header('Content-Disposition', 'attachment;filename=' + XLS_FILENAME)
+    snapdeal.add_header('Content-Disposition', 'attachment;filename=' + fileName)
     msg.attach(snapdeal)
     email.append('amit.gupta@shop2020.in')
     MAILTO = email
     global i
     rb = open_workbook(XLS_FILENAME,formatting_info=True)
     wb = copy(rb)
     worksheet = wb.add_sheet("All Users")
     worksheet1 = wb.add_sheet("Suspected Users")
+    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%'
         ''',)
     orders = []
     orderH = []
     row = 0
     i=-1
     worksheet.write(row, inc(), 'User Id', boldStyle)
     worksheet1.write(row, i, 'User Id', boldStyle)
+    sh1.write(row, i, 'User Id', boldStyle)
     worksheet.write(row, inc(), 'User name', boldStyle)
     worksheet1.write(row, i, 'User name', boldStyle)
+    sh1.write(row, i, 'User name', boldStyle)
     worksheet.write(row, inc(), 'Order Id', boldStyle)
     worksheet1.write(row, i, 'Order Id', boldStyle)
+    sh1.write(row, i, 'Order Id', boldStyle)
     worksheet.write(row, inc(), 'Created On', boldStyle)
     worksheet1.write(row, i, 'Created On', boldStyle)
+    sh1.write(row, i, 'Created On', boldStyle)
     worksheet.write(row, inc(), 'Store Id', boldStyle)
     worksheet1.write(row, i, 'Store Id', boldStyle)
+    sh1.write(row, i, 'Store Id', boldStyle)
     worksheet.write(row, inc(), 'Merchant Order Id', boldStyle)
     worksheet1.write(row, i, 'Merchant Order Id', boldStyle)
+    sh1.write(row, i, 'Merchant Order Id', boldStyle)
     worksheet.write(row, inc(), 'Product title', boldStyle)
     worksheet1.write(row, i, 'Product title', boldStyle)
+    sh1.write(row, i, 'Product title', boldStyle)
     worksheet.write(row, inc(), 'Amount Paid', boldStyle)
     worksheet1.write(row, i, 'Amount Paid', boldStyle)
+    sh1.write(row, i, 'Amount Paid', boldStyle)
     worksheet.write(row, inc(), 'Cashback', boldStyle)
     worksheet1.write(row, i, 'Cashback', boldStyle)
+    sh1.write(row, i, 'Brand', boldStyle)
     worksheet.write(row, inc(), 'Order Status', boldStyle)
     worksheet1.write(row, i, 'Order Status', boldStyle)
+    sh1.write(row, i, 'Model', boldStyle)
     worksheet.write(row, inc(), 'Detailed Status', boldStyle)
     worksheet1.write(row, i, 'Detailed Status', boldStyle)
+    sh1.write(row, i, 'Category', boldStyle)
     worksheet.write(row, inc(), 'Cashback Status', boldStyle)
     worksheet1.write(row, i, 'Cashback Status', boldStyle)
     worksheet.write(row, inc(), 'Reconciled', boldStyle)
     worksheet1.write(row, i, 'Reconciled', boldStyle)
     worksheet.write(row, inc(), 'IP', boldStyle)
     worksheet1.write(row, i, 'IP', boldStyle)
     row=0
     row2=0
+    row3=0
+    db1 = client.Catalog
     for result in results:
         morder = db.merchantOrder.find_one({"orderId":result[0]})
         if morder is not None:
             subOrders = morder.get("subOrders")
             if subOrders is not None:
                 for  subOrder in subOrders:
+                    row3+=1
+                    i=-1
+                    sh1.write(row3, inc(), result[0])
+                    sh1.write(row3, inc(), result[-3])
+                    sh1.write(row3, inc(), result[1])
+                    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("productTitle"))
+                    inc()
+                    if int(subOrder.get("quantity")) > 0:
+                        sh1.write(row3, i, int(subOrder.get("amountPaid"))/int(subOrder.get("quantity")))
+                    res = db1.MasterData.find_one({"source_id":morder.get("storeId"), "identifier":subOrder.get("productCode")})
+                    if res is not None:
+                        sh1.write(row3, inc(), res.get("brand"))
+                        sh1.write(row3, inc(), res.get("model_name"))
+                        sh1.write(row3, inc(), subOrder.get("category"))
                     if subOrder.get("cashBackStatus") == Store.CB_APPROVED or subOrder.get("cashBackStatus") == Store.CB_PENDING:
                         row +=1
                         i=-1
                         worksheet.write(row, inc(), result[0])
                         worksheet.write(row, inc(), result[-3])
                             if morder.get("reconciled") and morder.get("storeId")==3:
                                 try:
                                     worksheet1.write(row2, inc(), db.snapdealOrderAffiliateInfo.find_one({"adId":morder.get("adId"), "reconciled":True}).get("ip"))
                                 except:
                                     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 inc():
     global i
     i+=1
     return i
+def addHeaders(sheet):
+    global i
+    i = 0
+    sheet.write(0, inc(), )
 def main():
     generateSnapDealReco()
     getUserOrders()
-    sendmail(["amit.gupta@shop2020.in", "rajneesh.arora@saholic.com"], "", "DTR Affiliate Reco")
+    sendmail(["amit.gupta@shop2020.in"], "", "DTR Affiliate Reco", XLS_FILENAME)
+    sendmail(["amit.gupta@shop2020.in"], "", "All Orders", XLS_O_FILENAME)
 if __name__ == '__main__':
     main()

Subversion Repositories SmartDukaan

(root)/trunk/PyDTR/src/dtr/reports/affiliatereco.py – Rev 14620 → 14632