Subversion Repositories SmartDukaan

Rev

Rev 17126 | Rev 17134 | Go to most recent revision | Show entire file | Ignore whitespace | Details | Blame | Last modification | View Log | RSS feed

Rev 17126 Rev 17128
Line 72... Line 72...
72
PASSWORD = "5h0p2o2o"
72
PASSWORD = "5h0p2o2o"
73
SUBJECT = "User Activity Report for " + date.today().isoformat()
73
SUBJECT = "User Activity Report for " + date.today().isoformat()
74
SMTP_SERVER = "smtp.gmail.com"
74
SMTP_SERVER = "smtp.gmail.com"
75
SMTP_PORT = 587    
75
SMTP_PORT = 587    
76
 
76
 
-
 
77
DATE_QUERY="""
-
 
78
SELECT date(d.visited) from daily_visitors d 
-
 
79
join users u where u.id=d.user_id AND 
-
 
80
(LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
-
 
81
AND date(d.visited) > '2015-03-08' group by visited ;
-
 
82
"""
-
 
83
 
77
MONTH_QUERY="""
84
MONTH_QUERY="""
78
SELECT month(d.visited) from daily_visitors d 
85
SELECT month(d.visited) from daily_visitors d 
79
join users u on u.id=d.user_id where 
86
join users u on u.id=d.user_id where 
80
(LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
87
(LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
81
group by month(d.visited);
88
group by month(d.visited);
Line 293... Line 300...
293
def getDbConnection():
300
def getDbConnection():
294
    return MySQLdb.connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME)
301
    return MySQLdb.connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME)
295
 
302
 
296
 
303
 
297
def generateDailyReport():
304
def generateDailyReport():
-
 
305
    datesql= DATE_QUERY
298
    dnruSql = DNRU_QUERY
306
    dnruSql = DNRU_QUERY
299
    dauSql = DAU_QUERY
307
    dauSql = DAU_QUERY
300
    dabSql = DAB_QUERY
308
    dabSql = DAB_QUERY
301
    dtoSql = DTO_QUERY
309
    dtoSql = DTO_QUERY
302
    conn = getDbConnection()
310
    conn = getDbConnection()
303
 
311
 
304
    cursor = conn.cursor()
312
    cursor = conn.cursor()
-
 
313
    cursor.execute(datesql)
-
 
314
    result = cursor.fetchall()
305
    global workbook
315
    global workbook
306
    workbook = xlwt.Workbook()
316
    workbook = xlwt.Workbook()
307
    worksheet = workbook.add_sheet("User")
317
    worksheet = workbook.add_sheet("User")
308
    boldStyle = xlwt.XFStyle()
318
    boldStyle = xlwt.XFStyle()
309
    f = xlwt.Font()
319
    f = xlwt.Font()
Line 321... Line 331...
321
    worksheet.write(row, 5, 'DTO', boldStyle)
331
    worksheet.write(row, 5, 'DTO', boldStyle)
322
    worksheet.write(row, 6, 'DVOL', boldStyle)   
332
    worksheet.write(row, 6, 'DVOL', boldStyle)   
323
    worksheet.write(row, 7, 'DTV', boldStyle)
333
    worksheet.write(row, 7, 'DTV', boldStyle)
324
    worksheet.write(row, 8, 'DOWNVOL', boldStyle)
334
    worksheet.write(row, 8, 'DOWNVOL', boldStyle)
325
    worksheet.write(row, 9, 'DOWNVAL', boldStyle)
335
    worksheet.write(row, 9, 'DOWNVAL', boldStyle)
326
#     for r in result:
336
    for r in result:
327
#         row += 1
-
 
328
#         column = 0
-
 
329
#         for data in r :
-
 
330
#             print data
-
 
331
#             print type(data)
-
 
332
#             if data.strip() =='2015/09/29':
-
 
333
#                 worksheet.write(row,column)
-
 
334
#             worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
-
 
335
#             column += 1
-
 
336
 
-
 
337
    d = datetime(2015, 3, 8)
-
 
338
    i=1 
-
 
339
    a= datetime.today()
-
 
340
    todayDate = a.strftime('%Y/%m/%d')    
-
 
341
    while(True):
-
 
342
        row += 1
337
        row += 1
343
        column = 0
338
        column = 0
344
        predefinedDate = d+timedelta(days=i)
-
 
345
        predefinedDate =  predefinedDate.strftime('%Y/%m/%d')
-
 
346
        print predefinedDate
339
        for data in r :
347
        worksheet.write(row, column, predefinedDate)
340
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
348
        i=i+1
-
 
349
        if todayDate==predefinedDate:
-
 
350
            break
341
            column += 1
351
 
342
 
352
    row = 0
343
    row = 0
353
    cursor.execute(dnruSql)
344
    cursor.execute(dnruSql)
354
    result = cursor.fetchall()
345
    result = cursor.fetchall()
355
    for r in result:
346
    for r in result: