Subversion Repositories SmartDukaan

Rev

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

Rev 15244 Rev 15551
Line 45... Line 45...
45
weekCutOff = 1425839400
45
weekCutOff = 1425839400
46
#weekCutOff = 1425234600
46
#weekCutOff = 1425234600
47
 
47
 
48
DataService.initialize()
48
DataService.initialize()
49
 
49
 
-
 
50
monthMap = {  '01':30,
-
 
51
            '02':28,
-
 
52
            '03':30,
-
 
53
            '04':29,
-
 
54
            '05':30,
-
 
55
            '06':29,
-
 
56
            '07':30,
-
 
57
            '08':30,
-
 
58
            '09':29,
-
 
59
            '10':30,
-
 
60
            '11':29,
-
 
61
            '12':30
-
 
62
        }
50
 
63
 
51
# KEY NAMES
64
# KEY NAMES
52
SENDER = "cnc.center@shop2020.in"
65
SENDER = "cnc.center@shop2020.in"
53
PASSWORD = "5h0p2o2o"
66
PASSWORD = "5h0p2o2o"
54
SUBJECT = "User Activity Report for" + date.today().isoformat()
67
SUBJECT = "User Activity Report for " + date.today().isoformat()
55
SMTP_SERVER = "smtp.gmail.com"
68
SMTP_SERVER = "smtp.gmail.com"
56
SMTP_PORT = 587    
69
SMTP_PORT = 587    
57
 
70
 
58
DATE_QUERY="""
71
DATE_QUERY="""
59
SELECT date(d.visited) from daily_visitors d 
72
SELECT date(d.visited) from daily_visitors d 
Line 93... Line 106...
93
FROM daily_visitors d join users u WHERE u.id=d.user_id 
106
FROM daily_visitors d join users u WHERE u.id=d.user_id 
94
AND (LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
107
AND (LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
95
AND date(d.visited) > '2015-03-08' group by visited ;
108
AND date(d.visited) > '2015-03-08' group by visited ;
96
"""
109
"""
97
 
110
 
-
 
111
# DAB_QUERY="""
-
 
112
# SELECT COUNT(DISTINCT o.user_id ) 
-
 
113
# FROM  order_view o
-
 
114
# JOIN users u ON u.id = o.user_id WHERE 
-
 
115
# (LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
-
 
116
# AND (o.STATUS =  'ORDER_CREATED' OR o.STATUS ='DETAIL_CREATED')
-
 
117
# AND date(o.created) > '2015-03-08' 
-
 
118
# group by date(o.created)
-
 
119
# order by date(o.created)
-
 
120
# """
98
DAB_QUERY="""
121
DAB_QUERY="""
99
SELECT COUNT(DISTINCT o.user_id ) 
122
select count(DISTINCT s.user_id) from 
100
FROM  order_view o
123
(SELECT created, id,user_id FROM  order_view where STATUS =  'ORDER_CREATED' OR STATUS ='DETAIL_CREATED' 
-
 
124
union  SELECT created, id,user_id FROM   flipkartorders where date(created)>'2015-03-22') s 
101
JOIN users u ON u.id = o.user_id WHERE 
125
JOIN users u ON u.id = s.user_id WHERE   
102
(LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
126
(LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated = 1 
103
AND (o.STATUS =  'ORDER_CREATED' OR o.STATUS ='DETAIL_CREATED')
-
 
104
AND date(o.created) > '2015-03-08' 
127
AND date(s.created) > '2015-03-08' 
105
group by date(o.created)
128
GROUP by date(s.created)
106
order by date(o.created)
129
ORDER by date(s.created);
107
"""
130
"""
108
 
131
 
109
DTO_QUERY="""
132
DTO_QUERY="""
-
 
133
select count(*) from 
-
 
134
(SELECT created, id,user_id FROM  order_view where STATUS =  'ORDER_CREATED' OR STATUS ='DETAIL_CREATED' 
-
 
135
union  SELECT created, id,ifnull(user_id,139) FROM   flipkartorders where date(created)>'2015-03-22') s 
-
 
136
JOIN users u ON u.id = s.user_id WHERE   
-
 
137
(LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated = 1 
-
 
138
AND date(s.created) > '2015-03-08' 
-
 
139
GROUP by date(s.created)
-
 
140
ORDER by date(s.created);
-
 
141
"""
-
 
142
# DTO_QUERY="""
110
SELECT COUNT( *) 
143
# SELECT date(o.created),COUNT(*) 
111
FROM  order_view o
144
# FROM  order_view o
112
JOIN users u ON u.id = o.user_id WHERE 
145
# JOIN users u ON u.id = o.user_id WHERE 
113
(LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
146
# (LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
114
AND (o.STATUS =  'ORDER_CREATED' OR o.STATUS ='DETAIL_CREATED')
147
# AND (o.STATUS =  'ORDER_CREATED' OR o.STATUS ='DETAIL_CREATED')
115
AND date(o.created) > '2015-03-08' 
148
# AND date(o.created) > '2015-03-08' 
116
GROUP BY DATE(o.created)
149
# GROUP BY DATE(o.created)
117
order by date(o.created) 
150
# order by date(o.created) 
118
"""
151
#"""
119
 
152
 
120
MNRU_QUERY="""
153
MNRU_QUERY="""
121
SELECT count(*) FROM users u WHERE 
154
SELECT count(*) FROM users u WHERE 
122
(LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
155
(LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
123
AND date(u.created) > '2015-03-08' 
156
AND date(u.created) > '2015-03-08' 
Line 132... Line 165...
132
AND date(d.visited) > '2015-03-08' 
165
AND date(d.visited) > '2015-03-08' 
133
group by month(d.visited)
166
group by month(d.visited)
134
order by month(d.visited); 
167
order by month(d.visited); 
135
"""
168
"""
136
 
169
 
-
 
170
# MAB_QUERY="""
-
 
171
# SELECT COUNT(DISTINCT o.user_id ) 
-
 
172
# FROM  order_view o
-
 
173
# JOIN users u ON u.id = o.user_id where
-
 
174
# (LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
-
 
175
# AND (o.STATUS =  'ORDER_CREATED' OR o.STATUS ='DETAIL_CREATED')
-
 
176
# AND date(o.created) > '2015-03-08' 
-
 
177
# group by month(o.created)
-
 
178
# order by month(o.created); 
-
 
179
# """
-
 
180
 
-
 
181
# MTO_QUERY="""
-
 
182
# SELECT COUNT( *) 
-
 
183
# FROM  order_view o
-
 
184
# JOIN users u ON u.id = o.user_id where
-
 
185
# (LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
-
 
186
# AND (o.STATUS =  'ORDER_CREATED' OR o.STATUS ='DETAIL_CREATED')
-
 
187
# AND date(o.created) > '2015-03-08' 
-
 
188
# GROUP BY MONTH(o.created)
-
 
189
# order by month(o.created); 
-
 
190
# """
-
 
191
 
137
MAB_QUERY="""
192
MAB_QUERY="""
138
SELECT COUNT(DISTINCT o.user_id ) 
193
select count(DISTINCT s.user_id) from 
139
FROM  order_view o
194
(SELECT created, id,user_id FROM  order_view where STATUS =  'ORDER_CREATED' OR STATUS ='DETAIL_CREATED' 
140
JOIN users u ON u.id = o.user_id where
195
union  SELECT created, id,user_id FROM   flipkartorders where date(created)>'2015-03-22') s 
141
(LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
196
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 
142
AND (o.STATUS =  'ORDER_CREATED' OR o.STATUS ='DETAIL_CREATED')
-
 
143
AND date(o.created) > '2015-03-08' 
197
AND date(s.created) > '2015-03-08' 
144
group by month(o.created)
198
GROUP by month(s.created)
145
order by month(o.created); 
199
order by month(s.created);
146
"""
200
"""
147
 
-
 
148
MTO_QUERY="""
201
MTO_QUERY="""
149
SELECT COUNT( *) 
202
select count(*) from 
150
FROM  order_view o
203
(SELECT created, id,user_id FROM  order_view where STATUS =  'ORDER_CREATED' OR STATUS ='DETAIL_CREATED' 
151
JOIN users u ON u.id = o.user_id where
204
union  SELECT created, id,ifnull(user_id,139) FROM   flipkartorders where date(created)>'2015-03-22') s 
152
(LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
205
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 
153
AND (o.STATUS =  'ORDER_CREATED' OR o.STATUS ='DETAIL_CREATED')
-
 
154
AND date(o.created) > '2015-03-08' 
206
AND date(s.created) > '2015-03-08' 
155
GROUP BY MONTH(o.created)
207
GROUP by month(s.created)
156
order by month(o.created); 
208
order by month(s.created);
157
"""
209
"""
158
 
210
 
159
WNRU_QUERY="""
211
WNRU_QUERY="""
160
SELECT COUNT(*)
212
SELECT COUNT(*)
161
FROM users u WHERE 
213
FROM users u WHERE 
Line 173... Line 225...
173
GROUP BY WEEK(d.visited)
225
GROUP BY WEEK(d.visited)
174
ORDER BY WEEK(d.visited)
226
ORDER BY WEEK(d.visited)
175
"""
227
"""
176
 
228
 
177
 
229
 
-
 
230
# WAB_QUERY="""
-
 
231
# SELECT COUNT( DISTINCT o.user_id ) 
-
 
232
# FROM  order_view o
-
 
233
# JOIN users u ON u.id = o.user_id WHERE
-
 
234
# (LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
-
 
235
# AND (o.STATUS =  'ORDER_CREATED' OR o.STATUS ='DETAIL_CREATED')
-
 
236
# AND date(o.created) > '2015-03-08'  
-
 
237
# GROUP BY WEEK(date(o.created))
-
 
238
# ORDER BY WEEK(date(o.created))
-
 
239
# """
-
 
240
 
-
 
241
# WTO_QUERY="""
-
 
242
# SELECT COUNT(*)
-
 
243
# FROM  order_view o
-
 
244
# JOIN users u ON u.id = o.user_id WHERE 
-
 
245
# (LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
-
 
246
# AND (o.STATUS =  'ORDER_CREATED' OR o.STATUS ='DETAIL_CREATED')
-
 
247
# AND date(o.created) > '2015-03-08' 
-
 
248
# GROUP BY WEEK(date(o.created))
-
 
249
# ORDER BY WEEK(date(o.created))
-
 
250
# """
-
 
251
 
178
WAB_QUERY="""
252
WAB_QUERY="""
179
SELECT COUNT( DISTINCT o.user_id ) 
253
select count(distinct user_id) from 
180
FROM  order_view o
254
(SELECT created, id,user_id FROM  order_view where STATUS =  'ORDER_CREATED' OR STATUS ='DETAIL_CREATED' 
181
JOIN users u ON u.id = o.user_id WHERE
255
union  SELECT created, id,user_id FROM   flipkartorders where date(created)>'2015-03-22') s 
182
(LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
256
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 
183
AND (o.STATUS =  'ORDER_CREATED' OR o.STATUS ='DETAIL_CREATED')
-
 
184
AND date(o.created) > '2015-03-08'  
257
AND date(s.created) > '2015-03-08' 
185
GROUP BY WEEK(date(o.created))
258
GROUP by week(s.created)
186
ORDER BY WEEK(date(o.created))
259
order by week(s.created);
187
"""
260
"""
188
 
261
 
189
WTO_QUERY="""
262
WTO_QUERY="""
190
SELECT COUNT(*)
263
select count(*) from 
191
FROM  order_view o
264
(SELECT created, id,user_id FROM  order_view where STATUS =  'ORDER_CREATED' OR STATUS ='DETAIL_CREATED' 
192
JOIN users u ON u.id = o.user_id WHERE 
265
union  SELECT created, id,ifnull(user_id,139) FROM   flipkartorders where date(created)>'2015-03-22') s 
193
(LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
266
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 
194
AND (o.STATUS =  'ORDER_CREATED' OR o.STATUS ='DETAIL_CREATED')
-
 
195
AND date(o.created) > '2015-03-08' 
267
AND date(s.created) > '2015-03-08' 
196
GROUP BY WEEK(date(o.created))
268
GROUP by week(s.created)
197
ORDER BY WEEK(date(o.created))
269
order by week(s.created);
-
 
270
"""
-
 
271
 
-
 
272
DQ_QUERY="""
-
 
273
select sum(quantity) from flipkartorders where date(created)='%s';
-
 
274
"""
-
 
275
DV_QUERY="""
-
 
276
select sum(quantity*price) from flipkartorders where date(created)='%s';
-
 
277
"""
-
 
278
WQ_QUERY="""
-
 
279
select sum(quantity) from flipkartorders where date(created) between '%s' and '%s';
-
 
280
"""
-
 
281
WV_QUERY="""
-
 
282
select sum(quantity*price) from flipkartorders where date(created) between '%s' and '%s';
198
"""
283
"""
199
 
-
 
200
 
284
 
201
date_format = xlwt.XFStyle()
285
date_format = xlwt.XFStyle()
202
date_format.num_format_str = 'yyyy/mm/dd'
286
date_format.num_format_str = 'yyyy/mm/dd'
203
 
287
 
204
datetime_format = xlwt.XFStyle()
288
datetime_format = xlwt.XFStyle()
Line 297... Line 381...
297
            column += 1
381
            column += 1
298
 
382
 
299
    row = 0            
383
    row = 0            
300
    cursor.execute(dtoSql)
384
    cursor.execute(dtoSql)
301
    result = cursor.fetchall()
385
    result = cursor.fetchall()
302
    
-
 
303
    for r in result:
386
    for r in result:
304
        row += 1
387
        row += 1
305
        column = 5
388
        column = 5
306
        for data in r :
389
        for data in r :
307
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
390
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
Line 311... Line 394...
311
 
394
 
312
    z=0    
395
    z=0    
313
    for x in sorted(dateWiseOrderMap):
396
    for x in sorted(dateWiseOrderMap):
314
        row += 1
397
        row += 1
315
        column = 6
398
        column = 6
-
 
399
        d = str(datetime.fromtimestamp(x/1000.0))
-
 
400
        dq=DQ_QUERY%d[:10]
-
 
401
        cursor.execute(dq)
-
 
402
        result = cursor.fetchall()
-
 
403
        for r in result:
-
 
404
            quantity= r[0]   
-
 
405
            if quantity is None:
-
 
406
                quantity=0
316
        worksheet.write(row,column,dateWiseOrderMap.get(x).count)
407
        worksheet.write(row,column,(dateWiseOrderMap.get(x).count)+quantity)
317
        column += 1
408
        column += 1
318
 
409
 
319
    row =0
410
    row =0
320
    for x in sorted(dateWiseOrderMap):
411
    for x in sorted(dateWiseOrderMap):
321
        row += 1
412
        row += 1
322
        column = 7
413
        column = 7
-
 
414
        d = str(datetime.fromtimestamp(x/1000.0))
-
 
415
        dq=DV_QUERY%d[:10]
-
 
416
        cursor.execute(dq)
-
 
417
        result = cursor.fetchall()
-
 
418
        for r in result:
-
 
419
            quantity= r[0]   
-
 
420
            if quantity is None:
-
 
421
                quantity=0
323
        worksheet.write(row,column,dateWiseOrderMap.get(x).value)
422
        worksheet.write(row,column,(dateWiseOrderMap.get(x).value)+float(quantity))
324
        column += 1    
423
        column += 1    
325
        
424
        
326
def generateMonthlyReport():
425
def generateMonthlyReport():
327
    monthSql = MONTH_QUERY
426
    monthSql = MONTH_QUERY
328
    mnruSql = MNRU_QUERY
427
    mnruSql = MNRU_QUERY
Line 350... Line 449...
350
    worksheet.write(row, 1, 'MTRU', boldStyle)
449
    worksheet.write(row, 1, 'MTRU', boldStyle)
351
    worksheet.write(row, 2, 'MNRU', boldStyle)
450
    worksheet.write(row, 2, 'MNRU', boldStyle)
352
    worksheet.write(row, 3, 'MAU', boldStyle)
451
    worksheet.write(row, 3, 'MAU', boldStyle)
353
    worksheet.write(row, 4, 'MAB', boldStyle)
452
    worksheet.write(row, 4, 'MAB', boldStyle)
354
    worksheet.write(row, 5, 'MTO', boldStyle)
453
    worksheet.write(row, 5, 'MTO', boldStyle)
-
 
454
    worksheet.write(row, 6, 'MTS', boldStyle)
-
 
455
    worksheet.write(row, 7, 'MTV', boldStyle)
355
   
456
    
356
   
-
 
357
    for r in result:
457
    for r in result:
358
        row += 1
458
        row += 1
359
        column = 0
459
        column = 0
360
        for data in r :
460
        for data in r :
361
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
461
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
Line 414... Line 514...
414
        row += 1
514
        row += 1
415
        column = 5
515
        column = 5
416
        for data in r :
516
        for data in r :
417
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
517
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
418
            column += 1
518
            column += 1
-
 
519
    
-
 
520
    row=0
419
 
521
 
-
 
522
    z=0    
-
 
523
    for x in sorted(monthWiseOrderMap):
-
 
524
        d = str(datetime.fromtimestamp(x/1000.0))
-
 
525
        nextday = (x/1000 + (monthMap.get(d[5:7]) * oneDay))*1000
-
 
526
        dnext = str(datetime.fromtimestamp(nextday/1000.0))
-
 
527
        row += 1
-
 
528
        column = 6
-
 
529
        dq=WQ_QUERY % (d[:10],dnext[:10])
-
 
530
        cursor.execute(dq)
-
 
531
        result = cursor.fetchall()
-
 
532
        for r in result:
-
 
533
            quantity= r[0]
-
 
534
            if quantity is None:
-
 
535
                quantity=0
-
 
536
        worksheet.write(row,column,(monthWiseOrderMap.get(x).count)+quantity)
-
 
537
        column += 1
-
 
538
        
-
 
539
    row =0
-
 
540
    for x in sorted(monthWiseOrderMap):
-
 
541
        row += 1
-
 
542
        column = 7
-
 
543
        d = str(datetime.fromtimestamp(x/1000.0))
-
 
544
        nextday = (x/1000 + (monthMap.get(d[5:7]) * oneDay))*1000
-
 
545
        dnext = str(datetime.fromtimestamp(nextday/1000.0))
-
 
546
        dq=WV_QUERY % (d[:10],dnext[:10])
-
 
547
        cursor.execute(dq)
-
 
548
        result = cursor.fetchall()
-
 
549
        for r in result:
-
 
550
            quantity= r[0]
-
 
551
            if quantity is None:
-
 
552
                quantity=0
-
 
553
        worksheet.write(row,column,(monthWiseOrderMap.get(x).value)+float(quantity))
-
 
554
        column += 1  
420
    workbook.save(TMP_FILE)
555
    workbook.save(TMP_FILE)
421
    sendmail(["manas.kapoor@shop2020.in","rajneesh.arora@saholic.com"], "", TMP_FILE, SUBJECT)
556
    sendmail(["manas.kapoor@shop2020.in","rajneesh.arora@saholic.com"], "", TMP_FILE, SUBJECT)
422
    #sendmail(["manas.kapoor@shop2020.in"], "", TMP_FILE, SUBJECT)
557
    #sendmail(["manas.kapoor@shop2020.in"], "", TMP_FILE, SUBJECT)
423
 
558
 
424
def generateWeeklyReport():
559
def generateWeeklyReport():
Line 518... Line 653...
518
            
653
            
519
    row = 0        
654
    row = 0        
520
    for x in sorted(weekWiseOrderMap):
655
    for x in sorted(weekWiseOrderMap):
521
        row += 1
656
        row += 1
522
        column = 6
657
        column = 6
-
 
658
        d = str(datetime.fromtimestamp(x/1000.0))
-
 
659
        nextday=oneDay*7*1000+x
-
 
660
        dnext = str(datetime.fromtimestamp(nextday/1000.0))
-
 
661
        dq=WQ_QUERY % (d[:10],dnext[:10])
-
 
662
        cursor.execute(dq)
-
 
663
        result = cursor.fetchall()
-
 
664
        for r in result:
-
 
665
            quantity= r[0]
-
 
666
            if quantity is None:
-
 
667
                quantity=0
523
        worksheet.write(row,column,weekWiseOrderMap.get(x).count)
668
        worksheet.write(row,column,(weekWiseOrderMap.get(x).count)+quantity)
524
        column+=1
669
        column+=1
525
    row = 0
670
    row = 0
526
    for x in sorted(weekWiseOrderMap):
671
    for x in sorted(weekWiseOrderMap):
527
        row += 1
672
        row += 1
528
        column = 7
673
        column = 7
-
 
674
        d = str(datetime.fromtimestamp(x/1000.0))
-
 
675
        nextday=oneDay*7*1000+x
-
 
676
        dnext = str(datetime.fromtimestamp(nextday/1000.0))
-
 
677
        dq=WV_QUERY % (d[:10],dnext[:10])
-
 
678
        cursor.execute(dq)
-
 
679
        result = cursor.fetchall()
-
 
680
        for r in result:
-
 
681
            quantity= r[0]
-
 
682
            if quantity is None:
-
 
683
                quantity=0
529
        worksheet.write(row,column,weekWiseOrderMap.get(x).value)
684
        worksheet.write(row,column,(weekWiseOrderMap.get(x).value)+float(quantity))
530
        column+=1    
685
        column+=1    
531
 
686
 
532
 
687
 
533
def sendmail(email, message, fileName, title):
688
def sendmail(email, message, fileName, title):
534
    if email == "":
689
    if email == "":
Line 574... Line 729...
574
            return None
729
            return None
575
    return con
730
    return con
576
 
731
 
577
def populateOrderMap():
732
def populateOrderMap():
578
    global dateWiseOrderMap
733
    global dateWiseOrderMap
579
    allOrders = get_mongo_connection().Dtr.merchantOrder.find({'createdOnInt':{"$gte":cutOff}}).sort([('createdOnInt',pymongo.ASCENDING)])
734
    allOrders = get_mongo_connection().Dtr.merchantOrder.find({'createdOnInt':{"$gte":cutOff},'storeId':{"$in":[1,3,4]}}).sort([('createdOnInt',pymongo.ASCENDING)])
580
    for orders in allOrders:
735
    for orders in allOrders:
581
        if orders.get('orderId') not in order_ids:
736
        if orders.get('orderId') not in order_ids:
582
            continue
737
            continue
583
        cdate = ((to_py_date(orders.get('createdOnInt') * 1000)))
738
        cdate = ((to_py_date(orders.get('createdOnInt') * 1000)))
584
        millisec = to_java_date(datetime(cdate.year, cdate.month, cdate.day))
739
        millisec = to_java_date(datetime(cdate.year, cdate.month, cdate.day))
Line 592... Line 747...
592
            q, c = getSubOrderQuantity(orders.get('subOrders'))
747
            q, c = getSubOrderQuantity(orders.get('subOrders'))
593
            orderObj.count = q
748
            orderObj.count = q
594
            orderObj.value = c 
749
            orderObj.value = c 
595
            dateWiseOrderMap[millisec] = orderObj
750
            dateWiseOrderMap[millisec] = orderObj
596
            
751
            
-
 
752
    
-
 
753
            
597
def getSubOrderQuantity(subOrders):
754
def getSubOrderQuantity(subOrders):
598
    q = 0
755
    q = 0
599
    c = 0
756
    c = 0
600
    if subOrders is None:
757
    if subOrders is None:
601
        return q, c
758
        return q, c
Line 622... Line 779...
622
            break
779
            break
623
        
780
        
624
def populateWeekWiseMap1():
781
def populateWeekWiseMap1():
625
    global weekCutOff
782
    global weekCutOff
626
    while(True):
783
    while(True):
627
        #print weekCutOff *1000
-
 
628
        #print to_java_date(datetime.now())
-
 
629
        #print "**********************"
-
 
630
        if weekCutOff *1000 >= to_java_date(datetime.now()):
784
        if weekCutOff *1000 >= to_java_date(datetime.now()):
631
            #print "Breaking outer while"
-
 
632
            break
785
            break
633
        init = weekCutOff
786
        init = weekCutOff
634
        breakPoint = weekCutOff + (6 * oneDay)
787
        breakPoint = weekCutOff + (6 * oneDay)
635
        quantity, amount = 0 , 0
788
        quantity, amount = 0 , 0
636
        while(True):
789
        while(True):
637
            #print to_py_date(weekCutOff*1000)
-
 
638
            #print "weekCutOff ",weekCutOff
-
 
639
            #print "breakPoint ",breakPoint
-
 
640
            orderObj = dateWiseOrderMap.get(weekCutOff * 1000)
790
            orderObj = dateWiseOrderMap.get(weekCutOff * 1000)
641
            if orderObj is not None:
791
            if orderObj is not None:
642
                quantity += orderObj.count
792
                quantity += orderObj.count
643
                amount += orderObj.value
793
                amount += orderObj.value
644
            weekCutOff = weekCutOff + oneDay
794
            weekCutOff = weekCutOff + oneDay
645
            if weekCutOff > breakPoint:
795
            if weekCutOff > breakPoint:
646
                weekWiseOrderMap[init * 1000] = __Order(quantity, amount)
796
                weekWiseOrderMap[init * 1000] = __Order(quantity, amount)
647
                #print "Breaking inner while"
-
 
648
                break 
797
                break 
649
 
798
 
650
def populateMonthWiseMap():
799
def populateMonthWiseMap():
651
    global monthCutOff
800
    global monthCutOff
652
    while(True):
801
    while(True):
Line 660... Line 809...
660
        monthCutOff = monthCutOff * 1000 * 30 + oneDay
809
        monthCutOff = monthCutOff * 1000 * 30 + oneDay
661
        if monthCutOff >= to_py_date(datetime.now()):
810
        if monthCutOff >= to_py_date(datetime.now()):
662
            break
811
            break
663
 
812
 
664
def populateMonthWiseMap1():
813
def populateMonthWiseMap1():
665
    global weekCutOff
814
    global monthCutOff
666
    while(True):
815
    while(True):
-
 
816
        
667
        print weekCutOff *1000
817
        print monthCutOff *1000
-
 
818
        a=str(to_py_date(monthCutOff*1000))
668
        print to_java_date(datetime.now())
819
        print 'Now ' + str(to_java_date(datetime.now()))
669
        print "**********************"
820
        print "**********************"
670
        if weekCutOff *1000 >= to_java_date(datetime.now()):
821
        if monthCutOff *1000 >= to_java_date(datetime.now()):
671
            print "Breaking outer while"
822
            print "Breaking outer while"
672
            break
823
            break
673
        init = weekCutOff
824
        init = monthCutOff
674
        breakPoint = weekCutOff + (6 * oneDay)
825
        breakPoint = monthCutOff + (monthMap.get(a[5:7]) * oneDay)
-
 
826
        print breakPoint
675
        quantity, amount = 0 , 0
827
        quantity, amount = 0 , 0
676
        while(True):
828
        while(True):
677
            print to_py_date(weekCutOff*1000)
829
            print to_py_date(monthCutOff*1000)
678
            print "weekCutOff ",weekCutOff
830
            print "weekCutOff ",monthCutOff
679
            print "breakPoint ",breakPoint
831
            print "breakPoint ",breakPoint
680
            orderObj = dateWiseOrderMap.get(weekCutOff * 1000)
832
            orderObj = dateWiseOrderMap.get(monthCutOff * 1000)
681
            if orderObj is None:
833
            if orderObj is None:
682
                print "None for ", to_py_date(weekCutOff * 1000)
834
                print "None for ", to_py_date(monthCutOff * 1000)
683
            if orderObj is not None:
835
            if orderObj is not None:
684
                quantity += orderObj.count
836
                quantity += orderObj.count
685
                amount += orderObj.value
837
                amount += orderObj.value
686
            weekCutOff = weekCutOff + oneDay
838
            monthCutOff = monthCutOff + oneDay
-
 
839
            counter=0
-
 
840
            print counter+1
687
            if weekCutOff > breakPoint:
841
            if monthCutOff > breakPoint:
688
                weekWiseOrderMap[init * 1000] = __Order(quantity, amount)
842
                monthWiseOrderMap[init * 1000] = __Order(quantity, amount)
689
                print "Breaking inner while"
843
                print "Breaking inner while"
690
                break 
844
                break 
691
            
845
            
692
def populateValidOrders():
846
def populateValidOrders():
693
    global order_ids
847
    global order_ids
694
    #allOrders = session.query(Orders.id).join((Users,Orders.user_id==Users.id)).filter(~(func.lower(Users.referrer)).like('emp%')).all()
-
 
695
    allOrders = session.query(Orders.id).join((Users,Orders.user_id==Users.id)).filter(or_(~(func.lower(Users.referrer)).like('emp%'),Users.utm_campaign != None)).all()
848
    allOrders = session.query(Orders.id).join((Users,Orders.user_id==Users.id)).filter(or_(~(func.lower(Users.referrer)).like('emp%'),Users.utm_campaign != None)).all()
696
    order_ids = list(zip(*allOrders)[0])
849
    order_ids = list(zip(*allOrders)[0])
697
          
850
          
698
def main():
851
def main():
699
    #date = raw_input('Enter a date name: ')
-
 
700
    populateValidOrders()
852
    populateValidOrders()
701
    populateOrderMap()
853
    populateOrderMap()
702
    populateWeekWiseMap1()
854
    populateWeekWiseMap1()
-
 
855
    populateMonthWiseMap1()
703
    generateDailyReport()
856
    generateDailyReport()
704
    generateWeeklyReport()
857
    generateWeeklyReport()
705
    generateMonthlyReport()
858
    generateMonthlyReport()
706
    
859
    
-
 
860
    
707
if __name__ == '__main__':
861
if __name__ == '__main__':
708
    main()
862
    main()
709
 
-
 
710
 
-
 
711
863