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

 '''
 from datetime import date, datetime, timedelta
 from dtr.main import Store, sourceMap
 from dtr.reports.amazonreco import generateAmazonReco
 from dtr.storage import Mysql, DataService
-from dtr.storage.DataService import Users, Clicks, FlipkartOrders
+from dtr.storage.DataService import Users, Clicks, FlipkartOrders,\
+    All_user_addresses
 from dtr.storage.Mysql import getOrdersAfterDate, getOrdersByTag
 from dtr.utils.utils import fromTimeStamp
 from elixir import *
 from sqlalchemy.sql.expression import func, func, or_
 from email import encoders
     sh1 = workbook.add_sheet("All Orders")
     sh2 = workbook.add_sheet("Flipkart Orders")
     db=client.Dtr
     results = Mysql.fetchResult('''
+        select ow.*, u.username, crm1.order_count, u.referrer, aua.city,aua.state,aua.pincode from order_view ow
+        left join users u on u.id = ow.user_id
-        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;
+        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
+        left join (select * from (select * from all_user_addresses order by source) s group by user_id)aua on aua.user_id=u.id
+        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');
         ''',)
     row = 0
     i=-1
     worksheet.write(row, inc(), 'User Id', boldStyle)
     worksheet1.write(row, i, 'User Id', 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(), 'Detailed 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)
+    sh1.write(row, inc(), 'Deal Rank', boldStyle)
+    sh1.write(row, inc(), 'City', boldStyle)
+    sh1.write(row, inc(), 'State', boldStyle)
+    sh1.write(row, inc(), 'Pincode', boldStyle)
     i =-1
     sh2.write(row, inc(), 'User Id', boldStyle)
     sh2.write(row, inc(), 'User name', boldStyle)
     sh2.write(row, inc(), 'Created On', boldStyle)
     sh2.write(row, inc(), 'Store', boldStyle)
             if subOrders is not None:
                 for  subOrder in subOrders:
                     row3+=1
                     i=-1
                     sh1.write(row3, inc(), result[1])
-                    sh1.write(row3, inc(), result[-3])
+                    sh1.write(row3, inc(), result[7])
                     sh1.write(row3, inc(), result[0])
-                    sh1.write(row3, inc(), result[-4], datetime_format)
+                    sh1.write(row3, inc(), result[6], 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("detailedStatus"))
                     sh1.write(row3, inc(), subOrder.get("productTitle"))
-                    sh1.write(row3, inc(), result[-1])
+                    sh1.write(row3, inc(), result[9])
                     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 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"))
+                        sh1.write(row3, inc(), subOrder.get("rank"))
+                    else:
+                        sh1.write(row3, inc(), None)
+                        sh1.write(row3, inc(), None)
+                        sh1.write(row3, inc(), None)
+                        sh1.write(row3, inc(), None)
+                        sh1.write(row3, inc(), None)
+                    sh1.write(row3, inc(), result[10])
+                    sh1.write(row3, inc(), result[11])
+                    sh1.write(row3, inc(), result[12])
                     #removed filter as requested by Rajneesh
                     #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])
+                    worksheet.write(row, inc(), result[7])
                     worksheet.write(row, inc(), result[1])
-                    worksheet.write(row, inc(), result[-4], datetime_format)
+                    worksheet.write(row, inc(), result[6], datetime_format)
                     worksheet.write(row, inc(), result[2])
                     worksheet.write(row, inc(), morder.get("merchantOrderId"))
                     worksheet.write(row, inc(), subOrder.get("productTitle"))
                     worksheet.write(row, inc(), subOrder.get("amountPaid"))
                     worksheet.write(row, inc(), subOrder.get("cashBackAmount"))
                     if morder.get("reconciled") and morder.get("storeId")==3:
                         try:
                             worksheet.write(row, 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")
-                    if result[-2] >= 10:
+                    if result[8] >= 10:
                         row2 +=1
                         i=-1
                         worksheet1.write(row2, inc(), result[0])
-                        worksheet1.write(row2, inc(), result[-3])
+                        worksheet1.write(row2, inc(), result[7])
                         worksheet1.write(row2, inc(), result[1])
-                        worksheet1.write(row2, inc(), result[-4], datetime_format)
+                        worksheet1.write(row2, inc(), result[6], datetime_format)
                         worksheet1.write(row2, inc(), result[2])
                         worksheet1.write(row2, inc(), morder.get("merchantOrderId"))
                         worksheet1.write(row2, inc(), subOrder.get("productTitle"))
                         worksheet1.write(row2, inc(), subOrder.get("amountPaid"))
                         worksheet1.write(row2, inc(), subOrder.get("cashBackAmount"))

Subversion Repositories SmartDukaan

(root)/trunk/PyDTR/src/dtr/reports/affiliatereco.py – Rev 15794 → 15932