Subversion Repositories SmartDukaan

Rev

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

Rev 17217 Rev 17265
Line 1... Line -...
1
'''
-
 
2
Created on Mar 10, 2015
-
 
3
 
-
 
4
'''
-
 
5
from datetime import date, datetime, timedelta, datetime
1
from datetime import date, datetime, timedelta, datetime
6
from dtr.storage import DataService, Mongo
2
from dtr.storage import DataService, Mongo
7
from dtr.storage.DataService import Orders, Users, CallHistory
3
from dtr.storage.DataService import Orders, Users, CallHistory
8
from dtr.storage.Mysql import getOrdersAfterDate, getOrdersByTag
4
from dtr.storage.Mysql import getOrdersAfterDate, getOrdersByTag
9
from dtr.utils import utils
5
from dtr.utils import utils
10
from dtr.utils.utils import toTimeStamp
6
from dtr.utils.utils import toTimeStamp, to_java_date, to_py_date
11
from elixir import *
7
from elixir import *
12
from email import encoders
8
from email import encoders
13
from email.mime.base import MIMEBase
9
from email.mime.base import MIMEBase
14
from email.mime.multipart import MIMEMultipart
10
from email.mime.multipart import MIMEMultipart
15
from email.mime.text import MIMEText
11
from email.mime.text import MIMEText
16
from operator import or_
12
from operator import or_
17
from pymongo.mongo_client import MongoClient
13
from pymongo.mongo_client import MongoClient
18
from shop2020.utils.Utils import to_py_date, to_java_date
-
 
19
from sqlalchemy.sql.expression import func, and_
14
from sqlalchemy.sql.expression import func, and_
20
from time import strftime
15
from time import strftime
21
from xlrd import open_workbook
16
from xlrd import open_workbook
22
from xlutils.copy import copy
17
from xlutils.copy import copy
23
from xlwt.Workbook import Workbook
18
from xlwt.Workbook import Workbook
Line 30... Line 25...
30
 
25
 
31
DB_HOST = "localhost"
26
DB_HOST = "localhost"
32
DB_USER = "root"
27
DB_USER = "root"
33
DB_PASSWORD = "shop2020"
28
DB_PASSWORD = "shop2020"
34
DB_NAME = "dtr"
29
DB_NAME = "dtr"
35
TMP_FILE = "User_Activity_Report.xls"  
30
TMP_FILE = "/home/manas/User_Activity_Report.xls"  
36
 
31
 
37
con = None
32
con = None
38
dateWiseOrderMap = {}
33
dateWiseOrderMap = {}
39
weekWiseOrderMap = {}
34
weekWiseOrderMap = {}
40
monthWiseOrderMap = {}
35
monthWiseOrderMap = {}
Line 286... Line 281...
286
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 
281
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 
287
AND date(s.created) >=%s 
282
AND date(s.created) >=%s 
288
GROUP by week(s.created)
283
GROUP by week(s.created)
289
order by week(s.created);
284
order by week(s.created);
290
"""
285
"""
-
 
286
WVOL_QUERY="""
-
 
287
SELECT sum(quantity),sum(amount_paid)  FROM  allorder o 
-
 
288
JOIN users u ON u.id = o.user_id WHERE  (LOWER(u.referrer) NOT LIKE  
-
 
289
'emp%%' OR u.utm_campaign is not NULL) AND u.activated =1 
-
 
290
AND date(o.created_on) >= %s GROUP BY WEEK(date(o.created_on)); 
291
 
291
"""
-
 
292
WOWN_QUERY="""
-
 
293
SELECT sum(quantity),sum(amount_paid)  FROM  allorder o 
-
 
294
JOIN users u ON u.id = o.user_id WHERE  (LOWER(u.referrer) NOT LIKE  
-
 
295
'emp%%' OR u.utm_campaign is not NULL) AND u.activated =1 
-
 
296
AND date(o.created_on) >= %s and store_id='spice' GROUP BY WEEK(date(o.created_on)); 
-
 
297
"""
292
DQ_QUERY="""
298
DQ_QUERY="""
293
select sum(quantity) from flipkartorders where date(created)='%s';
299
select sum(quantity) from flipkartorders where date(created)='%s';
294
"""
300
"""
295
DV_QUERY="""
301
DV_QUERY="""
296
select sum(quantity*price) from flipkartorders where date(created)='%s';
302
select sum(quantity*price) from flipkartorders where date(created)='%s';
Line 362... Line 368...
362
        column = 0
368
        column = 0
363
        for data in r :
369
        for data in r :
364
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
370
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
365
            dateMap[str(data)]=row
371
            dateMap[str(data)]=row
366
            column += 1
372
            column += 1
367
 
373
    
-
 
374
    cursor.execute(MTRU_QUERY,(cutOffDate))
-
 
375
    result = cursor.fetchall()
-
 
376
    for r in result:
-
 
377
        sumDa=r[0]
-
 
378
        
368
    row = 0
379
    row = 0
369
    cursor.execute(dnruSql,(cutOffDate,todaysDate()))
380
    cursor.execute(dnruSql,(cutOffDate,todaysDate()))
370
    result = cursor.fetchall()
381
    result = cursor.fetchall()
371
    for r in result:
382
    for r in result:
372
        row += 1
383
        row += 1
373
        column = 1
384
        column = 1
374
        for data in r :
385
        for data in r :
375
            sumdata=sumdata+data
386
            sumDa=sumDa+data
376
            worksheet.write(row, column, int(sumdata) if type(sumdata) is float else sumdata, date_format if type(sumdata) is date else default_format)
387
            worksheet.write(row, column, int(sumDa) if type(sumDa) is float else sumDa, date_format if type(sumDa) is date else default_format)
377
            column += 1
388
            column += 1
378
 
389
 
379
    row = 0
390
    row = 0
380
    cursor.execute(dnruSql,(cutOffDate,todaysDate()))
391
    cursor.execute(dnruSql,(cutOffDate,todaysDate()))
381
    result = cursor.fetchall()
392
    result = cursor.fetchall()
Line 622... Line 633...
622
    weekSql = WEEK_QUERY
633
    weekSql = WEEK_QUERY
623
    wnruSql = WNRU_QUERY
634
    wnruSql = WNRU_QUERY
624
    wauSql = WAU_QUERY
635
    wauSql = WAU_QUERY
625
    wabSql = WAB_QUERY
636
    wabSql = WAB_QUERY
626
    wtoSql = WTO_QUERY
637
    wtoSql = WTO_QUERY
-
 
638
    wownSql=WOWN_QUERY
627
  
639
    wvolSql=WVOL_QUERY
628
    conn = getDbConnection()
640
    conn = getDbConnection()
629
    
641
    
630
    cursor = conn.cursor()
642
    cursor = conn.cursor()
631
  
643
  
632
    cursor.execute(weekSql,(last_day(cutOffDate, 'sunday')))
644
    cursor.execute(weekSql,(last_day(cutOffDate, 'sunday')))
Line 638... Line 650...
638
    f = xlwt.Font()
650
    f = xlwt.Font()
639
    f.bold = True
651
    f.bold = True
640
    boldStyle.font = f
652
    boldStyle.font = f
641
    column = 0
653
    column = 0
642
    row = 0
654
    row = 0
643
    sumdata=17
-
 
644
    worksheet.write(row, 0, 'Week', boldStyle)
655
    worksheet.write(row, 0, 'Week', boldStyle)
645
    worksheet.write(row, 1, 'WTRU', boldStyle)
656
    worksheet.write(row, 1, 'WTRU', boldStyle)
646
    worksheet.write(row, 2, 'WNRU', boldStyle)
657
    worksheet.write(row, 2, 'WNRU', boldStyle)
647
    worksheet.write(row, 3, 'WAU', boldStyle)
658
    worksheet.write(row, 3, 'WAU', boldStyle)
648
    worksheet.write(row, 4, 'WAB', boldStyle)
659
    worksheet.write(row, 4, 'WAB', boldStyle)
Line 657... Line 668...
657
        column = 0
668
        column = 0
658
        for data in r :
669
        for data in r :
659
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
670
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
660
            column += 1
671
            column += 1
661
 
672
 
-
 
673
    cursor.execute(MTRU_QUERY,(cutOffDate))
-
 
674
    result = cursor.fetchall()
-
 
675
    for r in result:
-
 
676
        sumDa=r[0]
-
 
677
 
662
    row = 0
678
    row = 0
663
    cursor.execute(wnruSql,(last_day(cutOffDate, 'sunday')))
679
    cursor.execute(wnruSql,(cutOffDate))
664
    result = cursor.fetchall()
680
    result = cursor.fetchall()
665
    for r in result:
681
    for r in result:
666
        row += 1
682
        row += 1
667
        column = 1
683
        column = 1
668
        for data in r :
684
        for data in r :
669
            sumdata=sumdata+data
685
            sumDa=sumDa+data
670
            worksheet.write(row, column, int(sumdata) if type(sumdata) is float else sumdata, date_format if type(sumdata) is date else default_format)
686
            worksheet.write(row, column, int(sumDa) if type(sumDa) is float else sumDa, date_format if type(sumDa) is date else default_format)
671
            column += 1
687
            column += 1
672
 
688
 
673
    row = 0
689
    row = 0
674
    cursor.execute(wnruSql,(last_day(cutOffDate, 'sunday')))
690
    cursor.execute(wnruSql,(cutOffDate))
675
    result = cursor.fetchall()
691
    result = cursor.fetchall()
676
    for r in result:
692
    for r in result:
677
        row += 1
693
        row += 1
678
        column = 2
694
        column = 2
679
        for data in r :
695
        for data in r :
680
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
696
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
681
            column += 1        
697
            column += 1        
682
    
698
    
683
    row = 0
699
    row = 0
684
    cursor.execute(wauSql,(last_day(cutOffDate, 'sunday')))
700
    cursor.execute(wauSql,(cutOffDate))
685
    result = cursor.fetchall()
701
    result = cursor.fetchall()
686
    
702
    
687
    for r in result:
703
    for r in result:
688
        row += 1
704
        row += 1
689
        column = 3
705
        column = 3
690
        for data in r :
706
        for data in r :
691
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
707
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
692
            column += 1
708
            column += 1
693
 
709
 
694
    row = 0
710
    row = 0
695
    cursor.execute(wabSql,(last_day(cutOffDate, 'sunday')))
711
    cursor.execute(wabSql,(cutOffDate))
696
    result = cursor.fetchall()
712
    result = cursor.fetchall()
697
    
713
    
698
    for r in result:
714
    for r in result:
699
        row += 1
715
        row += 1
700
        column = 4
716
        column = 4
701
        for data in r :
717
        for data in r :
702
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
718
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
703
            column += 1
719
            column += 1
704
 
720
 
705
    row = 0            
721
    row = 0            
706
    cursor.execute(wtoSql,(last_day(cutOffDate, 'sunday')))
722
    cursor.execute(wtoSql,(cutOffDate))
707
    result = cursor.fetchall()
723
    result = cursor.fetchall()
708
    
724
    
709
    for r in result:
725
    for r in result:
710
        row += 1
726
        row += 1
711
        column = 5
727
        column = 5
712
        for data in r :
728
        for data in r :
713
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
729
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
714
            column += 1
730
            column += 1
715
            
731
    
716
    row = 0        
732
    row = 0            
-
 
733
    cursor.execute(wvolSql,(cutOffDate))
-
 
734
    result = cursor.fetchall()
-
 
735
    
717
    for x in sorted(weekWiseOrderMap):
736
    for r in result:
718
        row += 1
737
        row += 1
719
        column = 6
738
        column = 6
720
        d = str(datetime.fromtimestamp(x/1000.0))
-
 
721
        nextday=oneDay*7*1000+x
-
 
722
        dnext = str(datetime.fromtimestamp(nextday/1000.0))
-
 
723
        dq=WQ_QUERY % (d[:10],dnext[:10])
-
 
724
        cursor.execute(dq)
-
 
725
        result = cursor.fetchall()
-
 
726
        for r in result:
739
        for data in r :
727
            quantity= r[0]
-
 
728
            if quantity is None:
-
 
729
                quantity=0
-
 
730
        worksheet.write(row,column,(weekWiseOrderMap.get(x).count)+quantity)
740
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
731
        column+=1
741
            column += 1
732
    row = 0
-
 
733
    for x in sorted(weekWiseOrderMap):
-
 
734
        row += 1
742
            
735
        column = 7
743
    row = 0            
736
        d = str(datetime.fromtimestamp(x/1000.0))
-
 
737
        nextday=oneDay*7*1000+x
-
 
738
        dnext = str(datetime.fromtimestamp(nextday/1000.0))
-
 
739
        dq=WV_QUERY % (d[:10],dnext[:10])
-
 
740
        cursor.execute(dq)
744
    cursor.execute(wownSql,(cutOffDate))
741
        result = cursor.fetchall()
745
    result = cursor.fetchall()
742
        for r in result:
-
 
743
            quantity= r[0]
-
 
744
            if quantity is None:
-
 
745
                quantity=0
-
 
746
        worksheet.write(row,column,(weekWiseOrderMap.get(x).value)+float(quantity))
-
 
747
        column+=1    
-
 
748
    
746
    
749
    row=0
-
 
750
    for x in sorted(weekWiseSaholicOrderMap):
747
    for r in result:
751
        row += 1
748
        row += 1
752
        column = 8
749
        column = 8
-
 
750
        for data in r :
-
 
751
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
-
 
752
            column += 1
-
 
753
            
-
 
754
#     row = 0        
-
 
755
#     for x in sorted(weekWiseOrderMap):
-
 
756
#         row += 1
-
 
757
#         column = 6
-
 
758
#         d = str(datetime.fromtimestamp(x/1000.0))
-
 
759
#         nextday=oneDay*7*1000+x
-
 
760
#         dnext = str(datetime.fromtimestamp(nextday/1000.0))
-
 
761
#         dq=WQ_QUERY % (d[:10],dnext[:10])
-
 
762
#         cursor.execute(dq)
-
 
763
#         result = cursor.fetchall()
-
 
764
#         for r in result:
-
 
765
#             quantity= r[0]
-
 
766
#             if quantity is None:
-
 
767
#                 quantity=0
-
 
768
#         worksheet.write(row,column,(weekWiseOrderMap.get(x).count)+quantity)
-
 
769
#         column+=1
-
 
770
#     row = 0
-
 
771
#     for x in sorted(weekWiseOrderMap):
-
 
772
#         row += 1
-
 
773
#         column = 7
-
 
774
#         d = str(datetime.fromtimestamp(x/1000.0))
-
 
775
#         nextday=oneDay*7*1000+x
-
 
776
#         dnext = str(datetime.fromtimestamp(nextday/1000.0))
-
 
777
#         dq=WV_QUERY % (d[:10],dnext[:10])
-
 
778
#         cursor.execute(dq)
-
 
779
#         result = cursor.fetchall()
-
 
780
#         for r in result:
-
 
781
#             quantity= r[0]
-
 
782
#             if quantity is None:
-
 
783
#                 quantity=0
-
 
784
#         worksheet.write(row,column,(weekWiseOrderMap.get(x).value)+float(quantity))
-
 
785
#         column+=1    
-
 
786
#     
-
 
787
#     row=0
-
 
788
#     for x in sorted(weekWiseSaholicOrderMap):
-
 
789
#         row += 1
-
 
790
#         column = 8
753
        worksheet.write(row,column,weekWiseSaholicOrderMap.get(x).count)
791
#         worksheet.write(row,column,weekWiseSaholicOrderMap.get(x).count)
754
        column+=1
792
#         column+=1
755
    
793
#     
756
    row=0
794
#     row=0
757
    for x in sorted(weekWiseSaholicOrderMap):
795
#     for x in sorted(weekWiseSaholicOrderMap):
758
        row += 1
796
#         row += 1
759
        column = 9
797
#         column = 9
760
        worksheet.write(row,column,weekWiseSaholicOrderMap.get(x).value)
798
#         worksheet.write(row,column,weekWiseSaholicOrderMap.get(x).value)
761
        column+=1
799
#         column+=1
762
        
800
        
763
def sendmail(email, message, fileName, title):
801
def sendmail(email, message, fileName, title):
764
    if email == "":
802
    if email == "":
765
        return
803
        return
766
    mailServer = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)
804
    mailServer = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)
Line 1040... Line 1078...
1040
    target_day = days_of_week.index(day_name.lower())
1078
    target_day = days_of_week.index(day_name.lower())
1041
    delta_day = target_day - (d.isoweekday()%7)
1079
    delta_day = target_day - (d.isoweekday()%7)
1042
    return d + timedelta(days=delta_day)
1080
    return d + timedelta(days=delta_day)
1043
          
1081
          
1044
def main():
1082
def main():
1045
    populateYesterdayActiveUsers(24)
1083
    #populateYesterdayActiveUsers(24)
1046
    populateValidOrders()
1084
    populateValidOrders()
1047
    populateOrderMap()
1085
    populateOrderMap()
1048
    populateWeekWiseMap1()
1086
    populateWeekWiseMap1()
1049
    populateMonthWiseMap1()
1087
    populateMonthWiseMap1()
1050
    populateSaholicOrderMap()
1088
    populateSaholicOrderMap()
1051
    populateSaholicWeekWiseMap1()
1089
    populateSaholicWeekWiseMap1()
1052
    populateSaholicMonthWiseMap1()
1090
    populateSaholicMonthWiseMap1()
1053
    generateDailyReport()
1091
    generateDailyReport()
1054
    generateWeeklyReport()
1092
    generateWeeklyReport()
1055
    generateMonthlyReport()
1093
    generateMonthlyReport()
1056
    sendmail(["manas.kapoor@shop2020.in","rajneesh.arora@saholic.com", "amit.gupta@shop2020.in","chaitnaya.vats@shop2020.in"], "", TMP_FILE, SUBJECT)
1094
    #sendmail(["manas.kapoor@shop2020.in","rajneesh.arora@saholic.com", "amit.gupta@shop2020.in","chaitnaya.vats@shop2020.in"], "", TMP_FILE, SUBJECT)
1057
    #sendmail(["manas.kapoor@shop2020.in"], "", TMP_FILE, SUBJECT)
1095
    #sendmail(["manas.kapoor@shop2020.in"], "", TMP_FILE, SUBJECT)
1058
    
1096
    
-
 
1097
    
1059
def to_x_date(java_timestamp):
1098
def to_x_date(java_timestamp):
1060
    try:
1099
    try:
1061
        date = datetime.fromtimestamp(java_timestamp / 1e3)       
1100
        date = datetime.fromtimestamp(java_timestamp / 1e3)       
1062
    except:
1101
    except:
1063
        return None
1102
        return None