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

 PASSWORD = "5h0p2o2o"
 SUBJECT = "User Activity Report for " + date.today().isoformat()
 SMTP_SERVER = "smtp.gmail.com"
 SMTP_PORT = 587
+DATE_QUERY="""
+SELECT date(d.visited) from daily_visitors d
+join users u where u.id=d.user_id AND
+(LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
+AND date(d.visited) > '2015-03-08' group by visited ;
+"""
 MONTH_QUERY="""
 SELECT month(d.visited) from daily_visitors d
 join users u on u.id=d.user_id where
 (LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
 group by month(d.visited);
 def getDbConnection():
     return MySQLdb.connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME)
 def generateDailyReport():
+    datesql= DATE_QUERY
     dnruSql = DNRU_QUERY
     dauSql = DAU_QUERY
     dabSql = DAB_QUERY
     dtoSql = DTO_QUERY
     conn = getDbConnection()
     cursor = conn.cursor()
+    cursor.execute(datesql)
+    result = cursor.fetchall()
     global workbook
     workbook = xlwt.Workbook()
     worksheet = workbook.add_sheet("User")
     boldStyle = xlwt.XFStyle()
     f = xlwt.Font()
     worksheet.write(row, 5, 'DTO', boldStyle)
     worksheet.write(row, 6, 'DVOL', boldStyle)
     worksheet.write(row, 7, 'DTV', boldStyle)
     worksheet.write(row, 8, 'DOWNVOL', boldStyle)
     worksheet.write(row, 9, 'DOWNVAL', boldStyle)
-#     for r in result:
+    for r in result:
-#         row += 1
-#         column = 0
-#         for data in r :
-#             print data
-#             print type(data)
-#             if data.strip() =='2015/09/29':
-#                 worksheet.write(row,column)
-#             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
-    d = datetime(2015, 3, 8)
-    i=1
-    a= datetime.today()
-    todayDate = a.strftime('%Y/%m/%d')
-    while(True):
         row += 1
         column = 0
-        predefinedDate = d+timedelta(days=i)
-        predefinedDate =  predefinedDate.strftime('%Y/%m/%d')
-        print predefinedDate
+        for data in r :
-        worksheet.write(row, column, predefinedDate)
+            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
-        i=i+1
-        if todayDate==predefinedDate:
-            break
+            column += 1
     row = 0
     cursor.execute(dnruSql)
     result = cursor.fetchall()
     for r in result:

Subversion Repositories SmartDukaan

(root)/trunk/PyDTR/src/scripts/ActivityReport.py – Rev 17126 → 17128