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

-'''
-Created on Mar 10, 2015
-'''
 from datetime import date, datetime, timedelta, datetime
 from dtr.storage import DataService, Mongo
 from dtr.storage.DataService import Orders, Users, CallHistory
 from dtr.storage.Mysql import getOrdersAfterDate, getOrdersByTag
 from dtr.utils import utils
-from dtr.utils.utils import toTimeStamp
+from dtr.utils.utils import toTimeStamp, to_java_date, to_py_date
 from elixir import *
 from email import encoders
 from email.mime.base import MIMEBase
 from email.mime.multipart import MIMEMultipart
 from email.mime.text import MIMEText
 from operator import or_
 from pymongo.mongo_client import MongoClient
-from shop2020.utils.Utils import to_py_date, to_java_date
 from sqlalchemy.sql.expression import func, and_
 from time import strftime
 from xlrd import open_workbook
 from xlutils.copy import copy
 from xlwt.Workbook import Workbook
 DB_HOST = "localhost"
 DB_USER = "root"
 DB_PASSWORD = "shop2020"
 DB_NAME = "dtr"
-TMP_FILE = "User_Activity_Report.xls"
+TMP_FILE = "/home/manas/User_Activity_Report.xls"
 con = None
 dateWiseOrderMap = {}
 weekWiseOrderMap = {}
 monthWiseOrderMap = {}
 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) >=%s
 GROUP by week(s.created)
 order by week(s.created);
 """
+WVOL_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 GROUP BY WEEK(date(o.created_on));
+"""
+WOWN_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 WEEK(date(o.created_on));
+"""
 DQ_QUERY="""
 select sum(quantity) from flipkartorders where date(created)='%s';
 """
 DV_QUERY="""
 select sum(quantity*price) from flipkartorders where date(created)='%s';
         column = 0
         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)
             dateMap[str(data)]=row
             column += 1
+    cursor.execute(MTRU_QUERY,(cutOffDate))
+    result = cursor.fetchall()
+    for r in result:
+        sumDa=r[0]
     row = 0
     cursor.execute(dnruSql,(cutOffDate,todaysDate()))
     result = cursor.fetchall()
     for r in result:
         row += 1
         column = 1
         for data in r :
-            sumdata=sumdata+data
+            sumDa=sumDa+data
-            worksheet.write(row, column, int(sumdata) if type(sumdata) is float else sumdata, date_format if type(sumdata) is date else default_format)
+            worksheet.write(row, column, int(sumDa) if type(sumDa) is float else sumDa, date_format if type(sumDa) is date else default_format)
             column += 1
     row = 0
     cursor.execute(dnruSql,(cutOffDate,todaysDate()))
     result = cursor.fetchall()
     weekSql = WEEK_QUERY
     wnruSql = WNRU_QUERY
     wauSql = WAU_QUERY
     wabSql = WAB_QUERY
     wtoSql = WTO_QUERY
+    wownSql=WOWN_QUERY
+    wvolSql=WVOL_QUERY
     conn = getDbConnection()
     cursor = conn.cursor()
     cursor.execute(weekSql,(last_day(cutOffDate, 'sunday')))
     f = xlwt.Font()
     f.bold = True
     boldStyle.font = f
     column = 0
     row = 0
-    sumdata=17
     worksheet.write(row, 0, 'Week', boldStyle)
     worksheet.write(row, 1, 'WTRU', boldStyle)
     worksheet.write(row, 2, 'WNRU', boldStyle)
     worksheet.write(row, 3, 'WAU', boldStyle)
     worksheet.write(row, 4, 'WAB', boldStyle)
         column = 0
         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
+    cursor.execute(MTRU_QUERY,(cutOffDate))
+    result = cursor.fetchall()
+    for r in result:
+        sumDa=r[0]
     row = 0
-    cursor.execute(wnruSql,(last_day(cutOffDate, 'sunday')))
+    cursor.execute(wnruSql,(cutOffDate))
     result = cursor.fetchall()
     for r in result:
         row += 1
         column = 1
         for data in r :
-            sumdata=sumdata+data
+            sumDa=sumDa+data
-            worksheet.write(row, column, int(sumdata) if type(sumdata) is float else sumdata, date_format if type(sumdata) is date else default_format)
+            worksheet.write(row, column, int(sumDa) if type(sumDa) is float else sumDa, date_format if type(sumDa) is date else default_format)
             column += 1
     row = 0
-    cursor.execute(wnruSql,(last_day(cutOffDate, 'sunday')))
+    cursor.execute(wnruSql,(cutOffDate))
     result = cursor.fetchall()
     for r in result:
         row += 1
         column = 2
         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
-    cursor.execute(wauSql,(last_day(cutOffDate, 'sunday')))
+    cursor.execute(wauSql,(cutOffDate))
     result = cursor.fetchall()
     for r in result:
         row += 1
         column = 3
         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
-    cursor.execute(wabSql,(last_day(cutOffDate, 'sunday')))
+    cursor.execute(wabSql,(cutOffDate))
     result = cursor.fetchall()
     for r in result:
         row += 1
         column = 4
         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
-    cursor.execute(wtoSql,(last_day(cutOffDate, 'sunday')))
+    cursor.execute(wtoSql,(cutOffDate))
     result = cursor.fetchall()
     for r in result:
         row += 1
         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
+    cursor.execute(wvolSql,(cutOffDate))
+    result = cursor.fetchall()
-    for x in sorted(weekWiseOrderMap):
+    for r in result:
         row += 1
         column = 6
-        d = str(datetime.fromtimestamp(x/1000.0))
-        nextday=oneDay*7*1000+x
-        dnext = str(datetime.fromtimestamp(nextday/1000.0))
-        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,(weekWiseOrderMap.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(weekWiseOrderMap):
-        row += 1
-        column = 7
+    row = 0
-        d = str(datetime.fromtimestamp(x/1000.0))
-        nextday=oneDay*7*1000+x
-        dnext = str(datetime.fromtimestamp(nextday/1000.0))
-        dq=WV_QUERY % (d[:10],dnext[:10])
-        cursor.execute(dq)
+    cursor.execute(wownSql,(cutOffDate))
-        result = cursor.fetchall()
+    result = cursor.fetchall()
-        for r in result:
-            quantity= r[0]
-            if quantity is None:
-                quantity=0
-        worksheet.write(row,column,(weekWiseOrderMap.get(x).value)+float(quantity))
-        column+=1
-    row=0
-    for x in sorted(weekWiseSaholicOrderMap):
+    for r in result:
         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
+#     row = 0
+#     for x in sorted(weekWiseOrderMap):
+#         row += 1
+#         column = 6
+#         d = str(datetime.fromtimestamp(x/1000.0))
+#         nextday=oneDay*7*1000+x
+#         dnext = str(datetime.fromtimestamp(nextday/1000.0))
+#         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,(weekWiseOrderMap.get(x).count)+quantity)
+#         column+=1
+#     row = 0
+#     for x in sorted(weekWiseOrderMap):
+#         row += 1
+#         column = 7
+#         d = str(datetime.fromtimestamp(x/1000.0))
+#         nextday=oneDay*7*1000+x
+#         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,(weekWiseOrderMap.get(x).value)+float(quantity))
+#         column+=1
+#
+#     row=0
+#     for x in sorted(weekWiseSaholicOrderMap):
+#         row += 1
+#         column = 8
-        worksheet.write(row,column,weekWiseSaholicOrderMap.get(x).count)
+#         worksheet.write(row,column,weekWiseSaholicOrderMap.get(x).count)
-        column+=1
+#         column+=1
+#
-    row=0
+#     row=0
-    for x in sorted(weekWiseSaholicOrderMap):
+#     for x in sorted(weekWiseSaholicOrderMap):
-        row += 1
+#         row += 1
-        column = 9
+#         column = 9
-        worksheet.write(row,column,weekWiseSaholicOrderMap.get(x).value)
+#         worksheet.write(row,column,weekWiseSaholicOrderMap.get(x).value)
-        column+=1
+#         column+=1
 def sendmail(email, message, fileName, title):
     if email == "":
         return
     mailServer = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)
     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()
     populateOrderMap()
     populateWeekWiseMap1()
     populateMonthWiseMap1()
     populateSaholicOrderMap()
     populateSaholicWeekWiseMap1()
     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","rajneesh.arora@saholic.com", "amit.gupta@shop2020.in","chaitnaya.vats@shop2020.in"], "", TMP_FILE, SUBJECT)
     #sendmail(["manas.kapoor@shop2020.in"], "", TMP_FILE, SUBJECT)
 def to_x_date(java_timestamp):
     try:
         date = datetime.fromtimestamp(java_timestamp / 1e3)
     except:
         return None

Subversion Repositories SmartDukaan

(root)/trunk/PyDTR/src/scripts/ActivityReport.py – Rev 17217 → 17265