WebSVN – SmartDukaan – Diff – /trunk/PyDTR/src/scripts/ActivityReport.py

 monthWiseSaholicOrderMap = {}
 orderIds = []
 curDate = date.today()
-cutOffMonth = (datetime.now().month + 11)%12
+cutOffMonth = (datetime.now().month + 10)%12
-cutOffYear = curDate.year - (0 if curDate.month-1 > 0 else 1)
+cutOffYear = curDate.year - (0 if curDate.month-2 > 0 else 1)
 cutOff = to_java_date(datetime(cutOffYear, cutOffMonth, 1))/1000
 cutOffDate=datetime(cutOffYear, cutOffMonth, 1)
 #cutOff = 1425234600
 oneDay = 86400
 #monthCutOff = 1425148200
 (LOWER(u.referrer) NOT LIKE  'emp%%' OR u.utm_campaign is not NULL) AND u.activated = 1
 AND date(s.created) BETWEEN %s AND %s
 GROUP by date(s.created)
 ORDER by date(s.created);
 """
+DVOL_QUERY="""
+select sum(quantity), sum(value) from  (SELECT date(o.created_on) as dateid,
+sum(quantity) as quantity,sum(amount_paid) as value  FROM  allorder o
+JOIN users u ON u.id = o.user_id WHERE (LOWER(u.referrer) NOT LIKE   'emp%%' OR u.utm_campaign is not NULL)
+AND u.activated =1  AND date(o.created_on) >=%s
+GROUP BY date(o.created_on) UNION
+select date(o.created) as dateid,sum(quantity) as bquantity,sum(price)
+from flipkartorders o JOIN users u ON u.id = o.user_id  WHERE  (LOWER(u.referrer) NOT LIKE
+'emp%%' OR u.utm_campaign is not NULL) AND  u.activated =1 AND date(o.created) >= %s
+GROUP BY date(o.created)) a group by dateid;
+"""
+DOWN_QUERY="""
+SELECT date(created_on),sum(quantity),sum(amount_paid)  FROM  allorder o
+JOIN users u ON u.id = o.user_id WHERE  (LOWER(u.referrer) NOT LIKE
+'emp%%' OR u.utm_campaign is not NULL) AND u.activated =1
+AND date(o.created_on) >= %s and store_id='spice' GROUP BY date(o.created_on);
+"""
 # DTO_QUERY="""
 # SELECT date(o.created),COUNT(*)
 # FROM  order_view o
 # JOIN users u ON u.id = o.user_id WHERE
 # (LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
 JOIN users u ON u.id = s.user_id WHERE   (LOWER(u.referrer) NOT LIKE  'emp%%' OR u.utm_campaign is not NULL) AND u.activated =1
 AND date(s.created) BETWEEN %s AND %s
 GROUP by month(s.created)
 order by month(s.created);
 """
+MVOL_QUERY="""
+select sum(quantity), sum(value) from  (SELECT month(date(o.created_on)) as monthid,
+sum(quantity) as quantity,sum(amount_paid) as value  FROM  allorder o
+JOIN users u ON u.id = o.user_id WHERE (LOWER(u.referrer) NOT LIKE   'emp%%' OR u.utm_campaign is not NULL)
+AND u.activated =1  AND date(o.created_on) >=%s
+GROUP BY month(date(o.created_on)) UNION
+select month(date(o.created)) as monthid,sum(quantity) as bquantity,sum(price)
+from flipkartorders o JOIN users u ON u.id = o.user_id  WHERE  (LOWER(u.referrer) NOT LIKE
+'emp%%' OR u.utm_campaign is not NULL) AND  u.activated =1 AND date(o.created) >= %s
+GROUP BY month(date(o.created))) a group by monthid;
+"""
+MOWN_QUERY="""
+SELECT sum(quantity),sum(amount_paid)  FROM  allorder o
+JOIN users u ON u.id = o.user_id WHERE  (LOWER(u.referrer) NOT LIKE
+'emp%%' OR u.utm_campaign is not NULL) AND u.activated =1
+AND date(o.created_on) >= %s and store_id='spice' GROUP BY month(date(o.created_on));
+"""
 WNRU_QUERY="""
 SELECT COUNT(*)
 FROM users u WHERE
 (LOWER(u.referrer) NOT LIKE  'emp%%' OR u.utm_campaign is not NULL) AND u.activated =1
     datesql= DATE_QUERY
     dnruSql = DNRU_QUERY
     dauSql = DAU_QUERY
     dabSql = DAB_QUERY
     dtoSql = DTO_QUERY
+    dvolSql=DVOL_QUERY
+    downvolSql=DOWN_QUERY
     conn = getDbConnection()
     cursor = conn.cursor()
     cursor.execute(datesql,(cutOffDate,todaysDate()))
     result = cursor.fetchall()
         column = 5
         for data in r :
             worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
             column += 1
-    row=0
+    row = 0
+    cursor.execute(dvolSql,(cutOffDate,cutOffDate))
+    result = cursor.fetchall()
-    z=0
+    for r in result:
-    for x in sorted(dateWiseOrderMap):
+        row += 1
         column = 6
-        d = datetime.fromtimestamp(x/1000.0)
-        row = dateMap[datetime.strftime(d,'%Y-%m-%d')]
-        dq=DQ_QUERY%str(d)[:10]
-        cursor.execute(dq)
-        result = cursor.fetchall()
-        for r in result:
+        for data in r :
-            quantity= r[0]
-            if quantity is None:
-                quantity=0
-        worksheet.write(row,column,(dateWiseOrderMap.get(x).count)+quantity)
+            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
-        column += 1
+            column += 1
-    row =0
-    for x in sorted(dateWiseOrderMap):
-        column = 7
-        d = datetime.fromtimestamp(x/1000.0)
-        row = dateMap[datetime.strftime(d, '%Y-%m-%d')]
-        dq=DV_QUERY%str(d)[:10]
-        cursor.execute(dq)
-        result = cursor.fetchall()
-        for r in result:
-            quantity= r[0]
-            if quantity is None:
-                quantity=0
-        worksheet.write(row,column,(dateWiseOrderMap.get(x).value)+float(quantity))
-    #For Saholic
-    cursor = conn.cursor()
+    row = 0
-    cursor.execute(datesql,(cutOffDate,todaysDate()))
+    cursor.execute(downvolSql,(cutOffDate))
     result = cursor.fetchall()
-    for x in sorted(dateWiseSaholicOrderMap):
-        z= to_x_date(x)
-        row = dateMap.get(z)
-        if row:
+    for r in result:
-            column = 8
+        column = 8
-            worksheet.write(row,column,dateWiseSaholicOrderMap.get(x).count)
-    for x in sorted(dateWiseSaholicOrderMap):
-        z= to_x_date(x)
-        row=dateMap.get(z)
+        row=dateMap.get(str(r[0]))
         if row:
+            worksheet.write(row, column, r[1])
+            column += 1
+            worksheet.write(row, column, r[2])
+#     z=0
+#     for x in sorted(dateWiseOrderMap):
+#         column = 6
+#         d = datetime.fromtimestamp(x/1000.0)
+#         row = dateMap[datetime.strftime(d,'%Y-%m-%d')]
+#         dq=DQ_QUERY%str(d)[:10]
+#         cursor.execute(dq)
+#         result = cursor.fetchall()
+#         for r in result:
+#             quantity= r[0]
+#             if quantity is None:
+#                 quantity=0
+#         worksheet.write(row,column,(dateWiseOrderMap.get(x).count)+quantity)
+#         column += 1
+#
+#     row =0
+#     for x in sorted(dateWiseOrderMap):
+#         column = 7
+#         d = datetime.fromtimestamp(x/1000.0)
+#         row = dateMap[datetime.strftime(d, '%Y-%m-%d')]
+#         dq=DV_QUERY%str(d)[:10]
+#         cursor.execute(dq)
+#         result = cursor.fetchall()
+#         for r in result:
+#             quantity= r[0]
+#             if quantity is None:
+#                 quantity=0
+#         worksheet.write(row,column,(dateWiseOrderMap.get(x).value)+float(quantity))
+#
+#     #For Saholic
+#     cursor = conn.cursor()
+#     cursor.execute(datesql,(cutOffDate,todaysDate()))
+#     result = cursor.fetchall()
+#     for x in sorted(dateWiseSaholicOrderMap):
+#         z= to_x_date(x)
+#         row = dateMap.get(z)
+#         if row:
+#             column = 8
+#             worksheet.write(row,column,dateWiseSaholicOrderMap.get(x).count)
+#
+#     for x in sorted(dateWiseSaholicOrderMap):
+#         z= to_x_date(x)
+#         row=dateMap.get(z)
+#         if row:
-                column = 9
+#                 column = 9
-                worksheet.write(row,column,dateWiseSaholicOrderMap.get(x).value)
+#                 worksheet.write(row,column,dateWiseSaholicOrderMap.get(x).value)
 def generateMonthlyReport():
     monthSql = MONTH_QUERY
     mnruSql = MNRU_QUERY
     mauSql = MAU_QUERY
     mabSql = MAB_QUERY
     mtoSql = MTO_QUERY
+    mvolSql=MVOL_QUERY
+    mownvolSql=MOWN_QUERY
     conn = getDbConnection()
     cursor = conn.cursor()
     cursor.execute(monthSql,(cutOffDate,todaysDate()))
         for data in r :
             worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
             column += 1
     row=0
-    z=0
-    for x in sorted(monthWiseOrderMap):
+    cursor.execute(mvolSql,(cutOffDate,cutOffDate))
-        d = str(datetime.fromtimestamp(x/1000.0))
+    result = cursor.fetchall()
-        nextday = (x/1000 + (monthMap.get(d[5:7]) * oneDay))*1000
-        dnext = str(datetime.fromtimestamp(nextday/1000.0))
+    for r in result:
         row += 1
         column = 6
-        dq=WQ_QUERY % (d[:10],dnext[:10])
-        cursor.execute(dq)
-        result = cursor.fetchall()
-        for r in result:
+        for data in r :
-            quantity= r[0]
-            if quantity is None:
-                quantity=0
-        worksheet.write(row,column,(monthWiseOrderMap.get(x).count)+quantity)
+            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
-        column += 1
+            column += 1
-    row =0
-    for x in sorted(monthWiseOrderMap):
-        row += 1
-        column = 7
+    row = 0
-        d = str(datetime.fromtimestamp(x/1000.0))
-        nextday = (x/1000 + (monthMap.get(d[5:7]) * oneDay))*1000
-        dnext = str(datetime.fromtimestamp(nextday/1000.0))
-        dq=WV_QUERY % (d[:10],dnext[:10])
-        cursor.execute(dq)
+    cursor.execute(mownvolSql,(cutOffDate))
-        result = cursor.fetchall()
+    result = cursor.fetchall()
-        for r in result:
-            quantity= r[0]
-            if quantity is None:
-                quantity=0
-        worksheet.write(row,column,(monthWiseOrderMap.get(x).value)+float(quantity))
-        column += 1
-    row =0
+    for r in result:
-    for x in sorted(monthWiseSaholicOrderMap):
         row += 1
         column = 8
+        for data in r :
+            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
+            column += 1
+#     z=0
+#     for x in sorted(monthWiseOrderMap):
+#         d = str(datetime.fromtimestamp(x/1000.0))
+#         nextday = (x/1000 + (monthMap.get(d[5:7]) * oneDay))*1000
+#         dnext = str(datetime.fromtimestamp(nextday/1000.0))
+#         row += 1
+#         column = 6
+#         dq=WQ_QUERY % (d[:10],dnext[:10])
+#         cursor.execute(dq)
+#         result = cursor.fetchall()
+#         for r in result:
+#             quantity= r[0]
+#             if quantity is None:
+#                 quantity=0
+#         worksheet.write(row,column,(monthWiseOrderMap.get(x).count)+quantity)
+#         column += 1
+#
+#     row =0
+#     for x in sorted(monthWiseOrderMap):
+#         row += 1
+#         column = 7
+#         d = str(datetime.fromtimestamp(x/1000.0))
+#         nextday = (x/1000 + (monthMap.get(d[5:7]) * oneDay))*1000
+#         dnext = str(datetime.fromtimestamp(nextday/1000.0))
+#         dq=WV_QUERY % (d[:10],dnext[:10])
+#         cursor.execute(dq)
+#         result = cursor.fetchall()
+#         for r in result:
+#             quantity= r[0]
+#             if quantity is None:
+#                 quantity=0
+#         worksheet.write(row,column,(monthWiseOrderMap.get(x).value)+float(quantity))
+#         column += 1
+#
+#     row =0
+#     for x in sorted(monthWiseSaholicOrderMap):
+#         row += 1
+#         column = 8
-        worksheet.write(row,column,monthWiseSaholicOrderMap.get(x).count)
+#         worksheet.write(row,column,monthWiseSaholicOrderMap.get(x).count)
-        column += 1
+#         column += 1
-    row =0
+#     row =0
-    for x in sorted(monthWiseSaholicOrderMap):
+#     for x in sorted(monthWiseSaholicOrderMap):
-        row += 1
+#         row += 1
-        column = 9
+#         column = 9
-        worksheet.write(row,column,monthWiseSaholicOrderMap.get(x).value)
+#         worksheet.write(row,column,monthWiseSaholicOrderMap.get(x).value)
-        column += 1
+#         column += 1
     workbook.save(TMP_FILE)
 def generateWeeklyReport():
     weekSql = WEEK_QUERY
     target_day = days_of_week.index(day_name.lower())
     delta_day = target_day - (d.isoweekday()%7)
     return d + timedelta(days=delta_day)
 def main():
-    populateYesterdayActiveUsers(24)
+    #populateYesterdayActiveUsers(24)
-    populateValidOrders()
+    #populateValidOrders()
-    populateOrderMap()
+    #populateOrderMap()
     #populateWeekWiseMap1()
-    populateMonthWiseMap1()
+    #populateMonthWiseMap1()
-    populateSaholicOrderMap()
+    #populateSaholicOrderMap()
     #populateSaholicWeekWiseMap1()
-    populateSaholicMonthWiseMap1()
+    #populateSaholicMonthWiseMap1()
     generateDailyReport()
     generateWeeklyReport()
     generateMonthlyReport()
     sendmail(["manas.kapoor@shop2020.in","rajneesh.arora@saholic.com", "amit.gupta@shop2020.in","chaitnaya.vats@shop2020.in"], "", TMP_FILE, SUBJECT)
     #sendmail(["manas.kapoor@shop2020.in"], "", TMP_FILE, SUBJECT)

Subversion Repositories SmartDukaan

(root)/trunk/PyDTR/src/scripts/ActivityReport.py – Rev 17275 → 17396