Subversion Repositories SmartDukaan

Rev

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

Rev 17765 Rev 18012
Line 91... Line 91...
91
AND date(d.visited) BETWEEN %s AND %s
91
AND date(d.visited) BETWEEN %s AND %s
92
group by month(d.visited);
92
group by month(d.visited);
93
"""
93
"""
94
 
94
 
95
WEEK_QUERY="""
95
WEEK_QUERY="""
96
SELECT CONCAT(date(o.created), ' - ', date(o.created) + INTERVAL 6 DAY) AS week 
96
SELECT CONCAT(date(o.created), ' - ', date(o.created) + INTERVAL 6 DAY) AS week,WEEK(o.created) 
97
FROM  order_view o
97
FROM  order_view o
98
JOIN users u ON u.id = o.user_id WHERE 
98
JOIN users u ON u.id = o.user_id WHERE 
99
(LOWER(u.referrer) NOT LIKE  'emp%%' OR u.utm_campaign is not NULL) AND u.activated =1
99
(LOWER(u.referrer) NOT LIKE  'emp%%' OR u.utm_campaign is not NULL) AND u.activated =1
100
AND date(o.created) >= %s 
100
AND date(o.created) >= %s 
101
GROUP BY WEEK(date(o.created))
101
GROUP BY WEEK(date(o.created))
Line 165... Line 165...
165
SELECT date(created_on),sum(quantity),sum(amount_paid)  FROM  allorder o 
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  
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 
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); 
168
AND date(o.created_on) >= %s and store_id='spice' GROUP BY date(o.created_on); 
169
"""
169
"""
-
 
170
 
-
 
171
DACCSB_QUERY="""
-
 
172
select date(created_on),count(distinct user_id) from allorder 
-
 
173
where (category='Accessories' or category='Accs') and 
-
 
174
date(created_on)>=%s and store_id='spice' group by date(created_on) order by date(created_on);
-
 
175
"""
-
 
176
 
-
 
177
DACCSO_QUERY="""
-
 
178
select date(created_on),sum(quantity),sum(amount_paid) from allorder 
-
 
179
where (category='Accessories' or category='Accs') and date(created_on)>=%s and store_id='spice'
-
 
180
group by date(created_on) order by date(created_on);
-
 
181
"""
-
 
182
 
170
# DTO_QUERY="""
183
# DTO_QUERY="""
171
# SELECT date(o.created),COUNT(*) 
184
# SELECT date(o.created),COUNT(*) 
172
# FROM  order_view o
185
# FROM  order_view o
173
# JOIN users u ON u.id = o.user_id WHERE 
186
# 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
187
# (LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
Line 255... Line 268...
255
SELECT sum(quantity),sum(amount_paid)  FROM  allorder o 
268
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  
269
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 
270
'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)); 
271
AND date(o.created_on) >= %s and store_id='spice' GROUP BY month(date(o.created_on)); 
259
"""
272
"""
-
 
273
MACCSB_QUERY="""
-
 
274
select month(created_on),count(distinct user_id) from allorder 
-
 
275
where (category='Accessories' or category='Accs') and 
-
 
276
date(created_on)>=%s and store_id='spice' group by month(created_on) order by month(created_on);
-
 
277
"""
-
 
278
 
-
 
279
MACCSO_QUERY="""
-
 
280
select month(created_on),sum(quantity),sum(amount_paid) from allorder 
-
 
281
where (category='Accessories' or category='Accs') and date(created_on)>=%s and store_id='spice'
-
 
282
group by month(created_on) order by month(created_on);
-
 
283
"""
260
 
284
 
261
WNRU_QUERY="""
285
WNRU_QUERY="""
262
SELECT COUNT(*)
286
SELECT COUNT(*)
263
FROM users u WHERE 
287
FROM users u WHERE 
264
(LOWER(u.referrer) NOT LIKE  'emp%%' OR u.utm_campaign is not NULL) AND u.activated =1
288
(LOWER(u.referrer) NOT LIKE  'emp%%' OR u.utm_campaign is not NULL) AND u.activated =1
Line 334... Line 358...
334
SELECT sum(quantity),sum(amount_paid)  FROM  allorder o 
358
SELECT sum(quantity),sum(amount_paid)  FROM  allorder o 
335
JOIN users u ON u.id = o.user_id WHERE  (LOWER(u.referrer) NOT LIKE  
359
JOIN users u ON u.id = o.user_id WHERE  (LOWER(u.referrer) NOT LIKE  
336
'emp%%' OR u.utm_campaign is not NULL) AND u.activated =1 
360
'emp%%' OR u.utm_campaign is not NULL) AND u.activated =1 
337
AND date(o.created_on) >= %s and store_id='spice' GROUP BY WEEK(date(o.created_on)); 
361
AND date(o.created_on) >= %s and store_id='spice' GROUP BY WEEK(date(o.created_on)); 
338
"""
362
"""
-
 
363
WACCSB_QUERY="""
-
 
364
select WEEK(created_on) AS week, count(distinct user_id) from allorder
-
 
365
where (category='Accessories' or category='Accs') 
-
 
366
and  date(created_on)>=%s and store_id='spice'
-
 
367
group by week(created_on) 
-
 
368
order by WEEK(created_on);
-
 
369
"""
-
 
370
WACCSO_QUERY="""
-
 
371
select WEEK(created_on),sum(quantity),sum(amount_paid) from allorder 
-
 
372
where (category='Accessories' or category='Accs') and date(created_on)>=%s and store_id='spice'
-
 
373
group by WEEK(created_on)
-
 
374
order by WEEK(created_on);
-
 
375
"""
-
 
376
 
339
DQ_QUERY="""
377
DQ_QUERY="""
340
select sum(quantity) from flipkartorders where date(created)='%s';
378
select sum(quantity) from flipkartorders where date(created)='%s';
341
"""
379
"""
342
DV_QUERY="""
380
DV_QUERY="""
343
select sum(quantity*price) from flipkartorders where date(created)='%s';
381
select sum(quantity*price) from flipkartorders where date(created)='%s';
Line 377... Line 415...
377
    dauSql = DAU_QUERY
415
    dauSql = DAU_QUERY
378
    dabSql = DAB_QUERY
416
    dabSql = DAB_QUERY
379
    dtoSql = DTO_QUERY
417
    dtoSql = DTO_QUERY
380
    dvolSql=DVOL_QUERY
418
    dvolSql=DVOL_QUERY
381
    downvolSql=DOWN_QUERY
419
    downvolSql=DOWN_QUERY
-
 
420
    daccsbSql=DACCSB_QUERY
-
 
421
    daccsoSql = DACCSO_QUERY
382
    conn = getDbConnection()
422
    conn = getDbConnection()
383
 
423
 
384
    cursor = conn.cursor()
424
    cursor = conn.cursor()
385
    cursor.execute(datesql,(cutOffDate,todaysDate()))
425
    cursor.execute(datesql,(cutOffDate,todaysDate()))
386
    result = cursor.fetchall()
426
    result = cursor.fetchall()
Line 403... Line 443...
403
    worksheet.write(row, 5, 'DTO', boldStyle)
443
    worksheet.write(row, 5, 'DTO', boldStyle)
404
    worksheet.write(row, 6, 'DVOL', boldStyle)   
444
    worksheet.write(row, 6, 'DVOL', boldStyle)   
405
    worksheet.write(row, 7, 'DTV', boldStyle)
445
    worksheet.write(row, 7, 'DTV', boldStyle)
406
    worksheet.write(row, 8, 'DOWNVOL', boldStyle)
446
    worksheet.write(row, 8, 'DOWNVOL', boldStyle)
407
    worksheet.write(row, 9, 'DOWNVAL', boldStyle)
447
    worksheet.write(row, 9, 'DOWNVAL', boldStyle)
-
 
448
    worksheet.write(row, 10, 'DACCSU', boldStyle)
-
 
449
    worksheet.write(row, 11, 'DACCSB', boldStyle)
-
 
450
    worksheet.write(row, 12, 'DACCSOVOL', boldStyle)
-
 
451
    worksheet.write(row, 13, 'DACCSOVAL', boldStyle)
408
    dateMap={}
452
    dateMap={}
409
    for r in result:
453
    for r in result:
410
        row += 1
454
        row += 1
411
        column = 0
455
        column = 0
412
        for data in r :
456
        for data in r :
Line 492... Line 536...
492
        row=dateMap.get(str(r[0]))
536
        row=dateMap.get(str(r[0]))
493
        if row:
537
        if row:
494
            worksheet.write(row, column, r[1])
538
            worksheet.write(row, column, r[1])
495
            column += 1
539
            column += 1
496
            worksheet.write(row, column, r[2])
540
            worksheet.write(row, column, r[2])
-
 
541
            
-
 
542
    row = 1
-
 
543
    column=10
-
 
544
    breakDate = to_java_date(datetime.now())
-
 
545
    currentDay = cutOff*1000
-
 
546
    while True:
-
 
547
        currentDay = currentDay + 86400000
-
 
548
        result = get_mongo_connection_new().User.browsinghistories.find({"$and":[{'created':{"$gte":currentDay-86400000}},{'created':{"$lte":currentDay}},{"url":{"$regex" : "http://api.profittill.com/category/6"}}]}).distinct('user_id')
-
 
549
        worksheet.write(row, column, len(result))
-
 
550
        row=row+1
-
 
551
        if currentDay>breakDate:
-
 
552
            break
-
 
553
        
-
 
554
    row = 0            
-
 
555
    cursor.execute(daccsbSql,(cutOffDate))
-
 
556
    result = cursor.fetchall()
-
 
557
    for r in result:
-
 
558
        column = 11
-
 
559
        row=dateMap.get(str(r[0]))
-
 
560
        if row:
-
 
561
            worksheet.write(row, column, r[1])
-
 
562
    
-
 
563
    row = 0            
-
 
564
    cursor.execute(daccsoSql,(cutOffDate))
-
 
565
    result = cursor.fetchall()
497
 
566
 
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:
567
    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
568
        column = 12
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)
569
        row=dateMap.get(str(r[0]))
534
#         if row:
570
        if row:
535
#             column = 8
-
 
536
#             worksheet.write(row,column,dateWiseSaholicOrderMap.get(x).count)
571
            worksheet.write(row, column, r[1])
537
#                     
-
 
538
#     for x in sorted(dateWiseSaholicOrderMap):
-
 
539
#         z= to_x_date(x)
-
 
540
#         row=dateMap.get(z)
-
 
541
#         if row:
-
 
542
#                 column = 9
572
            column += 1
543
#                 worksheet.write(row,column,dateWiseSaholicOrderMap.get(x).value)
573
            worksheet.write(row, column, r[2])                                    
544
                                        
-
 
545
    
574
    
546
def generateMonthlyReport():
575
def generateMonthlyReport():
547
    monthSql = MONTH_QUERY
576
    monthSql = MONTH_QUERY
548
    mnruSql = MNRU_QUERY
577
    mnruSql = MNRU_QUERY
549
    mauSql = MAU_QUERY
578
    mauSql = MAU_QUERY
550
    mabSql = MAB_QUERY
579
    mabSql = MAB_QUERY
551
    mtoSql = MTO_QUERY
580
    mtoSql = MTO_QUERY
552
    mvolSql=MVOL_QUERY
581
    mvolSql=MVOL_QUERY
553
    mownvolSql=MOWN_QUERY
582
    mownvolSql=MOWN_QUERY
-
 
583
    maccsbSql= MACCSB_QUERY
-
 
584
    maccsoSql= MACCSO_QUERY      
-
 
585
    
554
    conn = getDbConnection()
586
    conn = getDbConnection()
555
    
587
    
556
    cursor = conn.cursor()
588
    cursor = conn.cursor()
557
  
589
  
558
    cursor.execute(monthSql,(cutOffDate,todaysDate()))
590
    cursor.execute(monthSql,(cutOffDate,todaysDate()))
Line 574... Line 606...
574
    worksheet.write(row, 5, 'MTO', boldStyle)
606
    worksheet.write(row, 5, 'MTO', boldStyle)
575
    worksheet.write(row, 6, 'MVOL', boldStyle)
607
    worksheet.write(row, 6, 'MVOL', boldStyle)
576
    worksheet.write(row, 7, 'MTV', boldStyle)
608
    worksheet.write(row, 7, 'MTV', boldStyle)
577
    worksheet.write(row, 8, 'MOWNVOL', boldStyle)
609
    worksheet.write(row, 8, 'MOWNVOL', boldStyle)
578
    worksheet.write(row, 9, 'MOWNVAL', boldStyle)
610
    worksheet.write(row, 9, 'MOWNVAL', boldStyle)
-
 
611
    worksheet.write(row, 10, 'MACCSU', boldStyle)
-
 
612
    worksheet.write(row, 11, 'MACCSB', boldStyle)
-
 
613
    worksheet.write(row, 12, 'MACCSOVOL', boldStyle)
-
 
614
    worksheet.write(row, 13, 'MACCSOVAL', boldStyle)
579
    
615
 
-
 
616
    monthMapDb = {}
580
    for r in result:
617
    for r in result:
581
        row += 1
618
        row += 1
582
        column = 0
619
        column = 0
583
        for data in r :
620
        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)
621
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
-
 
622
            monthMapDb[str(data)]=row
585
            column += 1
623
            column += 1
586
 
624
 
587
    cursor.execute(MTRU_QUERY,(cutOffDate))
625
    cursor.execute(MTRU_QUERY,(cutOffDate))
588
    result = cursor.fetchall()
626
    result = cursor.fetchall()
589
    for r in result:
627
    for r in result:
Line 662... Line 700...
662
        row += 1
700
        row += 1
663
        column = 8
701
        column = 8
664
        for data in r :
702
        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)
703
            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
704
            column += 1
667
#     z=0    
705
    
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
706
    row=1
673
#         column = 6
707
    column=10
674
#         dq=WQ_QUERY % (d[:10],dnext[:10])
708
    breakDate = to_java_date(datetime.now())
675
#         cursor.execute(dq)
-
 
676
#         result = cursor.fetchall()
709
    currentDay = cutOff*1000
677
#         for r in result:
710
    while True:
678
#             quantity= r[0]
711
        currentDay = currentDay + (monthMap.get(month_get(currentDay))*86400000)
679
#             if quantity is None:
712
        result = get_mongo_connection_new().User.browsinghistories.find({"$and":[{'created':{"$gte":currentDay- (monthMap.get(month_get(currentDay))*86400000)}},{'created':{"$lte":currentDay}},{"url":{"$regex" : "http://api.profittill.com/category/6"}}]}).distinct('user_id')
680
#                 quantity=0
713
        currentDay = currentDay+ 86400000
681
#         worksheet.write(row,column,(monthWiseOrderMap.get(x).count)+quantity)
714
        worksheet.write(row,column,len(result))
682
#         column += 1
715
        row=row+1
-
 
716
        if currentDay>breakDate:
683
#         
717
            break
684
#     row =0
718
    
685
#     for x in sorted(monthWiseOrderMap):
-
 
686
#         row += 1
719
    
687
#         column = 7
720
    row = 0            
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)
721
    cursor.execute(maccsbSql,(cutOffDate))
693
#         result = cursor.fetchall()
722
    result = cursor.fetchall()
694
#         for r in result:
723
    for r in result:
695
#             quantity= r[0]
724
        column = 11
696
#             if quantity is None:
725
        row=monthMapDb.get(str(r[0]))
697
#                 quantity=0
726
        if row:
698
#         worksheet.write(row,column,(monthWiseOrderMap.get(x).value)+float(quantity))
727
            worksheet.write(row, column, r[1])
699
#         column += 1
-
 
700
#         
728
    
701
#     row =0    
729
    row = 0            
702
#     for x in sorted(monthWiseSaholicOrderMap):
730
    cursor.execute(maccsoSql,(cutOffDate))
703
#         row += 1
731
    result = cursor.fetchall()
-
 
732
 
704
#         column = 8
733
    for r in result:
705
#         worksheet.write(row,column,monthWiseSaholicOrderMap.get(x).count)
-
 
706
#         column += 1
734
        column = 12
707
#     row =0    
-
 
708
#     for x in sorted(monthWiseSaholicOrderMap):
735
        row=monthMapDb.get(str(r[0]))
709
#         row += 1
736
        if row:
710
#         column = 9
-
 
711
#         worksheet.write(row,column,monthWiseSaholicOrderMap.get(x).value)
737
            worksheet.write(row, column, r[1])
712
#         column += 1
738
            column += 1
-
 
739
            worksheet.write(row, column, r[2])                                    
713
          
740
 
714
    workbook.save(TMP_FILE)
741
    workbook.save(TMP_FILE)
715
    
742
    
716
def generateWeeklyReport():
743
def generateWeeklyReport():
717
    weekSql = WEEK_QUERY
744
    weekSql = WEEK_QUERY
718
    wnruSql = WNRU_QUERY
745
    wnruSql = WNRU_QUERY
719
    wauSql = WAU_QUERY
746
    wauSql = WAU_QUERY
720
    wabSql = WAB_QUERY
747
    wabSql = WAB_QUERY
721
    wtoSql = WTO_QUERY
748
    wtoSql = WTO_QUERY
722
    wownSql=WOWN_QUERY
749
    wownSql=WOWN_QUERY
723
    wvolSql=WVOL_QUERY
750
    wvolSql=WVOL_QUERY
-
 
751
    waccsbSql = WACCSB_QUERY
-
 
752
    waccsoSql = WACCSO_QUERY
724
    conn = getDbConnection()
753
    conn = getDbConnection()
725
    
754
    
726
    cursor = conn.cursor()
755
    cursor = conn.cursor()
727
  
756
  
728
    cursor.execute(weekSql,(last_day(cutOffDate, 'sunday')))
757
    cursor.execute(weekSql,(last_day(cutOffDate, 'sunday')))
Line 744... Line 773...
744
    worksheet.write(row, 5, 'WTO', boldStyle)
773
    worksheet.write(row, 5, 'WTO', boldStyle)
745
    worksheet.write(row, 6, 'WVOL', boldStyle)
774
    worksheet.write(row, 6, 'WVOL', boldStyle)
746
    worksheet.write(row, 7, 'WTV', boldStyle)
775
    worksheet.write(row, 7, 'WTV', boldStyle)
747
    worksheet.write(row, 8, 'WOWNVOL', boldStyle)
776
    worksheet.write(row, 8, 'WOWNVOL', boldStyle)
748
    worksheet.write(row, 9, 'WOWNVAL', boldStyle)
777
    worksheet.write(row, 9, 'WOWNVAL', boldStyle)
-
 
778
    worksheet.write(row, 10, 'WACCSU', boldStyle)
-
 
779
    worksheet.write(row, 11, 'WACCSB', boldStyle)
-
 
780
    worksheet.write(row, 12, 'WACCSVOL', boldStyle)
-
 
781
    worksheet.write(row, 13, 'WACCSVAL', boldStyle)
749
   
782
    weekMap={}
750
    for r in result:
783
    for r in result:
751
        row += 1
784
        row += 1
752
        column = 0
785
        column = 0
753
        for data in r :
-
 
754
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
786
        worksheet.write(row, column, int(r[0]) if type(r[0]) is float else r[0], date_format if type(r[0]) is date else default_format)
-
 
787
        weekMap[str(r[1])]=row
755
            column += 1
788
        column += 1
756
 
789
 
757
    cursor.execute(MTRU_QUERY,(cutOffDate))
790
    cursor.execute(MTRU_QUERY,(cutOffDate))
758
    result = cursor.fetchall()
791
    result = cursor.fetchall()
759
    for r in result:
792
    for r in result:
760
        sumDa=r[0]
793
        sumDa=r[0]
Line 832... Line 865...
832
        row += 1
865
        row += 1
833
        column = 8
866
        column = 8
834
        for data in r :
867
        for data in r :
835
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
868
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
836
            column += 1
869
            column += 1
-
 
870
 
-
 
871
    row=1
-
 
872
    column=10            
-
 
873
    breakDate = to_java_date(datetime.now())
-
 
874
    currentDay = to_java_date(last_day(cutOffDate, 'sunday'))
-
 
875
    while True:
-
 
876
        currentDay = currentDay + (7*86400000)
-
 
877
        result = get_mongo_connection_new().User.browsinghistories.find({"$and":[{'created':{"$gte":currentDay-(6*86400000)}},{'created':{"$lte":currentDay}},{"url":{"$regex" : "http://api.profittill.com/category/6"}}]}).distinct('user_id')
-
 
878
        worksheet.write(row,column,len(result))
-
 
879
        row=row+1
-
 
880
        if currentDay>breakDate:
-
 
881
            break
-
 
882
    row = 0            
-
 
883
    cursor.execute(waccsbSql,(cutOffDate))
-
 
884
    result = cursor.fetchall()
-
 
885
    for r in result:
-
 
886
        column = 11
-
 
887
        row=weekMap.get(str(r[0]))
-
 
888
        if row:
-
 
889
            worksheet.write(row, column, r[1])
-
 
890
    
-
 
891
    row = 0            
-
 
892
    cursor.execute(waccsoSql,(cutOffDate))
-
 
893
    result = cursor.fetchall()
-
 
894
 
-
 
895
    for r in result:
-
 
896
        column = 12
-
 
897
        row=weekMap.get(str(r[0]))
-
 
898
        if row:
-
 
899
            worksheet.write(row, column, r[1])
-
 
900
            column += 1
-
 
901
            worksheet.write(row, column, r[2])
837
            
902
            
838
#     row = 0        
-
 
839
#     for x in sorted(weekWiseOrderMap):
-
 
840
#         row += 1
-
 
841
#         column = 6
-
 
842
#         d = str(datetime.fromtimestamp(x/1000.0))
-
 
843
#         nextday=oneDay*7*1000+x
-
 
844
#         dnext = str(datetime.fromtimestamp(nextday/1000.0))
-
 
845
#         dq=WQ_QUERY % (d[:10],dnext[:10])
-
 
846
#         cursor.execute(dq)
-
 
847
#         result = cursor.fetchall()
-
 
848
#         for r in result:
-
 
849
#             quantity= r[0]
-
 
850
#             if quantity is None:
-
 
851
#                 quantity=0
-
 
852
#         worksheet.write(row,column,(weekWiseOrderMap.get(x).count)+quantity)
-
 
853
#         column+=1
-
 
854
#     row = 0
-
 
855
#     for x in sorted(weekWiseOrderMap):
-
 
856
#         row += 1
-
 
857
#         column = 7
-
 
858
#         d = str(datetime.fromtimestamp(x/1000.0))
-
 
859
#         nextday=oneDay*7*1000+x
-
 
860
#         dnext = str(datetime.fromtimestamp(nextday/1000.0))
-
 
861
#         dq=WV_QUERY % (d[:10],dnext[:10])
-
 
862
#         cursor.execute(dq)
-
 
863
#         result = cursor.fetchall()
-
 
864
#         for r in result:
-
 
865
#             quantity= r[0]
-
 
866
#             if quantity is None:
-
 
867
#                 quantity=0
-
 
868
#         worksheet.write(row,column,(weekWiseOrderMap.get(x).value)+float(quantity))
-
 
869
#         column+=1    
-
 
870
#     
-
 
871
#     row=0
-
 
872
#     for x in sorted(weekWiseSaholicOrderMap):
-
 
873
#         row += 1
-
 
874
#         column = 8
-
 
875
#         worksheet.write(row,column,weekWiseSaholicOrderMap.get(x).count)
-
 
876
#         column+=1
-
 
877
#     
-
 
878
#     row=0
-
 
879
#     for x in sorted(weekWiseSaholicOrderMap):
-
 
880
#         row += 1
-
 
881
#         column = 9
-
 
882
#         worksheet.write(row,column,weekWiseSaholicOrderMap.get(x).value)
-
 
883
#         column+=1
-
 
884
        
903
        
885
def sendmail(email, message, fileName, title):
904
def sendmail(email, message, fileName, title):
886
    if email == "":
905
    if email == "":
887
        return
906
        return
888
    mailServer = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)
907
    mailServer = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)
Line 1160... Line 1179...
1160
    days_of_week = ['sunday','monday','tuesday','wednesday',
1179
    days_of_week = ['sunday','monday','tuesday','wednesday',
1161
                        'thursday','friday','saturday']
1180
                        'thursday','friday','saturday']
1162
    target_day = days_of_week.index(day_name.lower())
1181
    target_day = days_of_week.index(day_name.lower())
1163
    delta_day = target_day - (d.isoweekday()%7)
1182
    delta_day = target_day - (d.isoweekday()%7)
1164
    return d + timedelta(days=delta_day)
1183
    return d + timedelta(days=delta_day)
-
 
1184
 
-
 
1185
def get_mongo_connection_new(host='45.33.50.227', port=27017):
-
 
1186
    global con
-
 
1187
    if con is None:
-
 
1188
        print "Establishing connection %s host and port %d" %(host,port)
1165
          
1189
        try:
-
 
1190
            con = pymongo.MongoClient(host, port)
-
 
1191
        except Exception, e:
-
 
1192
            print e
-
 
1193
            return None
-
 
1194
    return con
-
 
1195
 
-
 
1196
 
1166
def main():
1197
def main():
1167
    #populateYesterdayActiveUsers(24)
1198
    populateYesterdayActiveUsers(24)
1168
    #populateValidOrders()
1199
    #populateValidOrders()
1169
    #populateOrderMap()
1200
    #populateOrderMap()
1170
    #populateWeekWiseMap1()
1201
    #populateWeekWiseMap1()
1171
    #populateMonthWiseMap1()
1202
    #populateMonthWiseMap1()
1172
    #populateSaholicOrderMap()
1203
    #populateSaholicOrderMap()
Line 1184... Line 1215...
1184
        date = datetime.fromtimestamp(java_timestamp / 1e3)       
1215
        date = datetime.fromtimestamp(java_timestamp / 1e3)       
1185
    except:
1216
    except:
1186
        return None
1217
        return None
1187
    return date.strftime('%Y-%m-%d')
1218
    return date.strftime('%Y-%m-%d')
1188
    
1219
    
-
 
1220
def month_get(java_timestamp):
-
 
1221
    try:
-
 
1222
        date = datetime.fromtimestamp(java_timestamp / 1e3)       
-
 
1223
    except:
-
 
1224
        return None
-
 
1225
    return date.strftime('%m')
-
 
1226
    
1189
if __name__ == '__main__':
1227
if __name__ == '__main__':
1190
    if len(sys.argv)==1:
1228
    if len(sys.argv)==1:
1191
        main()
1229
        main()
1192
    else:
1230
    else:
1193
        populateYesterdayActiveUsers(int(sys.argv[1]))
1231
        populateYesterdayActiveUsers(int(sys.argv[1]))
1194
1232