Subversion Repositories SmartDukaan

Rev

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

Rev 17275 Rev 17396
Line 38... Line 38...
38
monthWiseSaholicOrderMap = {}
38
monthWiseSaholicOrderMap = {}
39
 
39
 
40
orderIds = []
40
orderIds = []
41
 
41
 
42
curDate = date.today()
42
curDate = date.today()
43
cutOffMonth = (datetime.now().month + 11)%12
43
cutOffMonth = (datetime.now().month + 10)%12
44
cutOffYear = curDate.year - (0 if curDate.month-1 > 0 else 1)
44
cutOffYear = curDate.year - (0 if curDate.month-2 > 0 else 1)
45
cutOff = to_java_date(datetime(cutOffYear, cutOffMonth, 1))/1000
45
cutOff = to_java_date(datetime(cutOffYear, cutOffMonth, 1))/1000
46
cutOffDate=datetime(cutOffYear, cutOffMonth, 1)
46
cutOffDate=datetime(cutOffYear, cutOffMonth, 1)
47
#cutOff = 1425234600
47
#cutOff = 1425234600
48
oneDay = 86400
48
oneDay = 86400
49
#monthCutOff = 1425148200
49
#monthCutOff = 1425148200
Line 147... Line 147...
147
(LOWER(u.referrer) NOT LIKE  'emp%%' OR u.utm_campaign is not NULL) AND u.activated = 1 
147
(LOWER(u.referrer) NOT LIKE  'emp%%' OR u.utm_campaign is not NULL) AND u.activated = 1 
148
AND date(s.created) BETWEEN %s AND %s 
148
AND date(s.created) BETWEEN %s AND %s 
149
GROUP by date(s.created)
149
GROUP by date(s.created)
150
ORDER by date(s.created);
150
ORDER by date(s.created);
151
"""
151
"""
-
 
152
 
-
 
153
DVOL_QUERY="""
-
 
154
select sum(quantity), sum(value) from  (SELECT date(o.created_on) as dateid, 
-
 
155
sum(quantity) as quantity,sum(amount_paid) as value  FROM  allorder o 
-
 
156
JOIN users u ON u.id = o.user_id WHERE (LOWER(u.referrer) NOT LIKE   'emp%%' OR u.utm_campaign is not NULL)
-
 
157
AND u.activated =1  AND date(o.created_on) >=%s  
-
 
158
GROUP BY date(o.created_on) UNION  
-
 
159
select date(o.created) as dateid,sum(quantity) as bquantity,sum(price) 
-
 
160
from flipkartorders o JOIN users u ON u.id = o.user_id  WHERE  (LOWER(u.referrer) NOT LIKE  
-
 
161
'emp%%' OR u.utm_campaign is not NULL) AND  u.activated =1 AND date(o.created) >= %s
-
 
162
GROUP BY date(o.created)) a group by dateid;
-
 
163
"""
-
 
164
DOWN_QUERY="""
-
 
165
SELECT date(created_on),sum(quantity),sum(amount_paid)  FROM  allorder o 
-
 
166
JOIN users u ON u.id = o.user_id WHERE  (LOWER(u.referrer) NOT LIKE  
-
 
167
'emp%%' OR u.utm_campaign is not NULL) AND u.activated =1 
-
 
168
AND date(o.created_on) >= %s and store_id='spice' GROUP BY date(o.created_on); 
-
 
169
"""
152
# DTO_QUERY="""
170
# DTO_QUERY="""
153
# SELECT date(o.created),COUNT(*) 
171
# SELECT date(o.created),COUNT(*) 
154
# FROM  order_view o
172
# FROM  order_view o
155
# JOIN users u ON u.id = o.user_id WHERE 
173
# JOIN users u ON u.id = o.user_id WHERE 
156
# (LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
174
# (LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
Line 220... Line 238...
220
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 
238
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 
221
AND date(s.created) BETWEEN %s AND %s
239
AND date(s.created) BETWEEN %s AND %s
222
GROUP by month(s.created)
240
GROUP by month(s.created)
223
order by month(s.created);
241
order by month(s.created);
224
"""
242
"""
-
 
243
MVOL_QUERY="""
-
 
244
select sum(quantity), sum(value) from  (SELECT month(date(o.created_on)) as monthid, 
-
 
245
sum(quantity) as quantity,sum(amount_paid) as value  FROM  allorder o 
-
 
246
JOIN users u ON u.id = o.user_id WHERE (LOWER(u.referrer) NOT LIKE   'emp%%' OR u.utm_campaign is not NULL)
-
 
247
AND u.activated =1  AND date(o.created_on) >=%s  
-
 
248
GROUP BY month(date(o.created_on)) UNION  
-
 
249
select month(date(o.created)) as monthid,sum(quantity) as bquantity,sum(price) 
-
 
250
from flipkartorders o JOIN users u ON u.id = o.user_id  WHERE  (LOWER(u.referrer) NOT LIKE  
-
 
251
'emp%%' OR u.utm_campaign is not NULL) AND  u.activated =1 AND date(o.created) >= %s
-
 
252
GROUP BY month(date(o.created))) a group by monthid;
-
 
253
"""
-
 
254
MOWN_QUERY="""
-
 
255
SELECT sum(quantity),sum(amount_paid)  FROM  allorder o 
-
 
256
JOIN users u ON u.id = o.user_id WHERE  (LOWER(u.referrer) NOT LIKE  
-
 
257
'emp%%' OR u.utm_campaign is not NULL) AND u.activated =1 
-
 
258
AND date(o.created_on) >= %s and store_id='spice' GROUP BY month(date(o.created_on)); 
-
 
259
"""
225
 
260
 
226
WNRU_QUERY="""
261
WNRU_QUERY="""
227
SELECT COUNT(*)
262
SELECT COUNT(*)
228
FROM users u WHERE 
263
FROM users u WHERE 
229
(LOWER(u.referrer) NOT LIKE  'emp%%' OR u.utm_campaign is not NULL) AND u.activated =1
264
(LOWER(u.referrer) NOT LIKE  'emp%%' OR u.utm_campaign is not NULL) AND u.activated =1
Line 340... Line 375...
340
    datesql= DATE_QUERY
375
    datesql= DATE_QUERY
341
    dnruSql = DNRU_QUERY
376
    dnruSql = DNRU_QUERY
342
    dauSql = DAU_QUERY
377
    dauSql = DAU_QUERY
343
    dabSql = DAB_QUERY
378
    dabSql = DAB_QUERY
344
    dtoSql = DTO_QUERY
379
    dtoSql = DTO_QUERY
-
 
380
    dvolSql=DVOL_QUERY
-
 
381
    downvolSql=DOWN_QUERY
345
    conn = getDbConnection()
382
    conn = getDbConnection()
346
 
383
 
347
    cursor = conn.cursor()
384
    cursor = conn.cursor()
348
    cursor.execute(datesql,(cutOffDate,todaysDate()))
385
    cursor.execute(datesql,(cutOffDate,todaysDate()))
349
    result = cursor.fetchall()
386
    result = cursor.fetchall()
Line 433... Line 470...
433
        column = 5
470
        column = 5
434
        for data in r :
471
        for data in r :
435
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
472
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
436
            column += 1
473
            column += 1
437
    
474
    
438
    row=0
475
    row = 0            
-
 
476
    cursor.execute(dvolSql,(cutOffDate,cutOffDate))
-
 
477
    result = cursor.fetchall()
439
 
478
    
440
    z=0    
479
    for r in result:
441
    for x in sorted(dateWiseOrderMap):
480
        row += 1
442
        column = 6
481
        column = 6
443
        d = datetime.fromtimestamp(x/1000.0)
-
 
444
        row = dateMap[datetime.strftime(d,'%Y-%m-%d')]
-
 
445
        dq=DQ_QUERY%str(d)[:10]
-
 
446
        cursor.execute(dq)
-
 
447
        result = cursor.fetchall()
-
 
448
        for r in result:
482
        for data in r :
449
            quantity= r[0]   
-
 
450
            if quantity is None:
-
 
451
                quantity=0
-
 
452
        worksheet.write(row,column,(dateWiseOrderMap.get(x).count)+quantity)
483
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
453
        column += 1
484
            column += 1
454
 
-
 
455
    row =0
-
 
456
    for x in sorted(dateWiseOrderMap):
-
 
457
        column = 7
-
 
458
        d = datetime.fromtimestamp(x/1000.0)
-
 
459
        row = dateMap[datetime.strftime(d, '%Y-%m-%d')]
-
 
460
        dq=DV_QUERY%str(d)[:10]
-
 
461
        cursor.execute(dq)
-
 
462
        result = cursor.fetchall()
-
 
463
        for r in result:
-
 
464
            quantity= r[0]   
-
 
465
            if quantity is None:
-
 
466
                quantity=0
-
 
467
        worksheet.write(row,column,(dateWiseOrderMap.get(x).value)+float(quantity))
-
 
468
        
485
            
469
    #For Saholic
-
 
470
    cursor = conn.cursor()
486
    row = 0            
471
    cursor.execute(datesql,(cutOffDate,todaysDate()))
487
    cursor.execute(downvolSql,(cutOffDate))
472
    result = cursor.fetchall()
488
    result = cursor.fetchall()
473
    for x in sorted(dateWiseSaholicOrderMap):
-
 
474
        z= to_x_date(x)
-
 
475
        row = dateMap.get(z)
-
 
-
 
489
 
476
        if row:
490
    for r in result:
477
            column = 8
491
        column = 8
478
            worksheet.write(row,column,dateWiseSaholicOrderMap.get(x).count)
-
 
479
                    
-
 
480
    for x in sorted(dateWiseSaholicOrderMap):
-
 
481
        z= to_x_date(x)
-
 
482
        row=dateMap.get(z)
492
        row=dateMap.get(str(r[0]))
483
        if row:
493
        if row:
-
 
494
            worksheet.write(row, column, r[1])
-
 
495
            column += 1
-
 
496
            worksheet.write(row, column, r[2])
-
 
497
 
-
 
498
#     z=0    
-
 
499
#     for x in sorted(dateWiseOrderMap):
-
 
500
#         column = 6
-
 
501
#         d = datetime.fromtimestamp(x/1000.0)
-
 
502
#         row = dateMap[datetime.strftime(d,'%Y-%m-%d')]
-
 
503
#         dq=DQ_QUERY%str(d)[:10]
-
 
504
#         cursor.execute(dq)
-
 
505
#         result = cursor.fetchall()
-
 
506
#         for r in result:
-
 
507
#             quantity= r[0]   
-
 
508
#             if quantity is None:
-
 
509
#                 quantity=0
-
 
510
#         worksheet.write(row,column,(dateWiseOrderMap.get(x).count)+quantity)
-
 
511
#         column += 1
-
 
512
# 
-
 
513
#     row =0
-
 
514
#     for x in sorted(dateWiseOrderMap):
-
 
515
#         column = 7
-
 
516
#         d = datetime.fromtimestamp(x/1000.0)
-
 
517
#         row = dateMap[datetime.strftime(d, '%Y-%m-%d')]
-
 
518
#         dq=DV_QUERY%str(d)[:10]
-
 
519
#         cursor.execute(dq)
-
 
520
#         result = cursor.fetchall()
-
 
521
#         for r in result:
-
 
522
#             quantity= r[0]   
-
 
523
#             if quantity is None:
-
 
524
#                 quantity=0
-
 
525
#         worksheet.write(row,column,(dateWiseOrderMap.get(x).value)+float(quantity))
-
 
526
#         
-
 
527
#     #For Saholic
-
 
528
#     cursor = conn.cursor()
-
 
529
#     cursor.execute(datesql,(cutOffDate,todaysDate()))
-
 
530
#     result = cursor.fetchall()
-
 
531
#     for x in sorted(dateWiseSaholicOrderMap):
-
 
532
#         z= to_x_date(x)
-
 
533
#         row = dateMap.get(z)
-
 
534
#         if row:
-
 
535
#             column = 8
-
 
536
#             worksheet.write(row,column,dateWiseSaholicOrderMap.get(x).count)
-
 
537
#                     
-
 
538
#     for x in sorted(dateWiseSaholicOrderMap):
-
 
539
#         z= to_x_date(x)
-
 
540
#         row=dateMap.get(z)
-
 
541
#         if row:
484
                column = 9
542
#                 column = 9
485
                worksheet.write(row,column,dateWiseSaholicOrderMap.get(x).value)
543
#                 worksheet.write(row,column,dateWiseSaholicOrderMap.get(x).value)
486
                                        
544
                                        
487
    
545
    
488
def generateMonthlyReport():
546
def generateMonthlyReport():
489
    monthSql = MONTH_QUERY
547
    monthSql = MONTH_QUERY
490
    mnruSql = MNRU_QUERY
548
    mnruSql = MNRU_QUERY
491
    mauSql = MAU_QUERY
549
    mauSql = MAU_QUERY
492
    mabSql = MAB_QUERY
550
    mabSql = MAB_QUERY
493
    mtoSql = MTO_QUERY
551
    mtoSql = MTO_QUERY
494
  
552
    mvolSql=MVOL_QUERY
-
 
553
    mownvolSql=MOWN_QUERY
495
    conn = getDbConnection()
554
    conn = getDbConnection()
496
    
555
    
497
    cursor = conn.cursor()
556
    cursor = conn.cursor()
498
  
557
  
499
    cursor.execute(monthSql,(cutOffDate,todaysDate()))
558
    cursor.execute(monthSql,(cutOffDate,todaysDate()))
Line 583... Line 642...
583
        for data in r :
642
        for data in r :
584
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
643
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
585
            column += 1
644
            column += 1
586
    
645
    
587
    row=0
646
    row=0
588
 
-
 
589
    z=0    
-
 
590
    for x in sorted(monthWiseOrderMap):
647
    cursor.execute(mvolSql,(cutOffDate,cutOffDate))
591
        d = str(datetime.fromtimestamp(x/1000.0))
648
    result = cursor.fetchall()
592
        nextday = (x/1000 + (monthMap.get(d[5:7]) * oneDay))*1000
-
 
-
 
649
    
593
        dnext = str(datetime.fromtimestamp(nextday/1000.0))
650
    for r in result:
594
        row += 1
651
        row += 1
595
        column = 6
652
        column = 6
596
        dq=WQ_QUERY % (d[:10],dnext[:10])
-
 
597
        cursor.execute(dq)
-
 
598
        result = cursor.fetchall()
-
 
599
        for r in result:
653
        for data in r :
600
            quantity= r[0]
-
 
601
            if quantity is None:
-
 
602
                quantity=0
-
 
603
        worksheet.write(row,column,(monthWiseOrderMap.get(x).count)+quantity)
654
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
604
        column += 1
655
            column += 1
605
        
656
            
606
    row =0
-
 
607
    for x in sorted(monthWiseOrderMap):
-
 
608
        row += 1
-
 
609
        column = 7
657
    row = 0            
610
        d = str(datetime.fromtimestamp(x/1000.0))
-
 
611
        nextday = (x/1000 + (monthMap.get(d[5:7]) * oneDay))*1000
-
 
612
        dnext = str(datetime.fromtimestamp(nextday/1000.0))
-
 
613
        dq=WV_QUERY % (d[:10],dnext[:10])
-
 
614
        cursor.execute(dq)
658
    cursor.execute(mownvolSql,(cutOffDate))
615
        result = cursor.fetchall()
659
    result = cursor.fetchall()
616
        for r in result:
-
 
617
            quantity= r[0]
-
 
618
            if quantity is None:
-
 
619
                quantity=0
-
 
620
        worksheet.write(row,column,(monthWiseOrderMap.get(x).value)+float(quantity))
-
 
621
        column += 1
-
 
622
        
660
    
623
    row =0    
661
    for r in result:
624
    for x in sorted(monthWiseSaholicOrderMap):
-
 
625
        row += 1
662
        row += 1
626
        column = 8
663
        column = 8
-
 
664
        for data in r :
-
 
665
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
-
 
666
            column += 1
-
 
667
#     z=0    
-
 
668
#     for x in sorted(monthWiseOrderMap):
-
 
669
#         d = str(datetime.fromtimestamp(x/1000.0))
-
 
670
#         nextday = (x/1000 + (monthMap.get(d[5:7]) * oneDay))*1000
-
 
671
#         dnext = str(datetime.fromtimestamp(nextday/1000.0))
-
 
672
#         row += 1
-
 
673
#         column = 6
-
 
674
#         dq=WQ_QUERY % (d[:10],dnext[:10])
-
 
675
#         cursor.execute(dq)
-
 
676
#         result = cursor.fetchall()
-
 
677
#         for r in result:
-
 
678
#             quantity= r[0]
-
 
679
#             if quantity is None:
-
 
680
#                 quantity=0
-
 
681
#         worksheet.write(row,column,(monthWiseOrderMap.get(x).count)+quantity)
-
 
682
#         column += 1
-
 
683
#         
-
 
684
#     row =0
-
 
685
#     for x in sorted(monthWiseOrderMap):
-
 
686
#         row += 1
-
 
687
#         column = 7
-
 
688
#         d = str(datetime.fromtimestamp(x/1000.0))
-
 
689
#         nextday = (x/1000 + (monthMap.get(d[5:7]) * oneDay))*1000
-
 
690
#         dnext = str(datetime.fromtimestamp(nextday/1000.0))
-
 
691
#         dq=WV_QUERY % (d[:10],dnext[:10])
-
 
692
#         cursor.execute(dq)
-
 
693
#         result = cursor.fetchall()
-
 
694
#         for r in result:
-
 
695
#             quantity= r[0]
-
 
696
#             if quantity is None:
-
 
697
#                 quantity=0
-
 
698
#         worksheet.write(row,column,(monthWiseOrderMap.get(x).value)+float(quantity))
-
 
699
#         column += 1
-
 
700
#         
-
 
701
#     row =0    
-
 
702
#     for x in sorted(monthWiseSaholicOrderMap):
-
 
703
#         row += 1
-
 
704
#         column = 8
627
        worksheet.write(row,column,monthWiseSaholicOrderMap.get(x).count)
705
#         worksheet.write(row,column,monthWiseSaholicOrderMap.get(x).count)
628
        column += 1
706
#         column += 1
629
    row =0    
707
#     row =0    
630
    for x in sorted(monthWiseSaholicOrderMap):
708
#     for x in sorted(monthWiseSaholicOrderMap):
631
        row += 1
709
#         row += 1
632
        column = 9
710
#         column = 9
633
        worksheet.write(row,column,monthWiseSaholicOrderMap.get(x).value)
711
#         worksheet.write(row,column,monthWiseSaholicOrderMap.get(x).value)
634
        column += 1
712
#         column += 1
635
          
713
          
636
    workbook.save(TMP_FILE)
714
    workbook.save(TMP_FILE)
637
    
715
    
638
def generateWeeklyReport():
716
def generateWeeklyReport():
639
    weekSql = WEEK_QUERY
717
    weekSql = WEEK_QUERY
Line 1084... Line 1162...
1084
    target_day = days_of_week.index(day_name.lower())
1162
    target_day = days_of_week.index(day_name.lower())
1085
    delta_day = target_day - (d.isoweekday()%7)
1163
    delta_day = target_day - (d.isoweekday()%7)
1086
    return d + timedelta(days=delta_day)
1164
    return d + timedelta(days=delta_day)
1087
          
1165
          
1088
def main():
1166
def main():
1089
    populateYesterdayActiveUsers(24)
1167
    #populateYesterdayActiveUsers(24)
1090
    populateValidOrders()
1168
    #populateValidOrders()
1091
    populateOrderMap()
1169
    #populateOrderMap()
1092
    #populateWeekWiseMap1()
1170
    #populateWeekWiseMap1()
1093
    populateMonthWiseMap1()
1171
    #populateMonthWiseMap1()
1094
    populateSaholicOrderMap()
1172
    #populateSaholicOrderMap()
1095
    #populateSaholicWeekWiseMap1()
1173
    #populateSaholicWeekWiseMap1()
1096
    populateSaholicMonthWiseMap1()
1174
    #populateSaholicMonthWiseMap1()
1097
    generateDailyReport()
1175
    generateDailyReport()
1098
    generateWeeklyReport()
1176
    generateWeeklyReport()
1099
    generateMonthlyReport()
1177
    generateMonthlyReport()
1100
    sendmail(["manas.kapoor@shop2020.in","rajneesh.arora@saholic.com", "amit.gupta@shop2020.in","chaitnaya.vats@shop2020.in"], "", TMP_FILE, SUBJECT)
1178
    sendmail(["manas.kapoor@shop2020.in","rajneesh.arora@saholic.com", "amit.gupta@shop2020.in","chaitnaya.vats@shop2020.in"], "", TMP_FILE, SUBJECT)
1101
    #sendmail(["manas.kapoor@shop2020.in"], "", TMP_FILE, SUBJECT)
1179
    #sendmail(["manas.kapoor@shop2020.in"], "", TMP_FILE, SUBJECT)