Subversion Repositories SmartDukaan

Rev

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

Rev 15849 Rev 15850
Line 33... Line 33...
33 
SENDER = "cnc.center@shop2020.in"
 33 
SENDER = "cnc.center@shop2020.in"
34 
PASSWORD = "5h0p2o2o"
 34 
PASSWORD = "5h0p2o2o"
35 
SUBJECT = "DTR User Segmentation report for " + date.today().isoformat()
 35 
SUBJECT = "DTR User Segmentation report for " + date.today().isoformat()
36 
SMTP_SERVER = "smtp.gmail.com"
 36 
SMTP_SERVER = "smtp.gmail.com"
37 
SMTP_PORT = 587
 37 
SMTP_PORT = 587
38 
activeuser="""select year(u.created) creationyear,month(u.created) creationmonth, uu.yearly, uu.monthly,count(*) from users u join (select user_id, year(created) yearly, month(created) monthly from (select user_id, created from order_view where status in ('ORDER_CREATED', 'DETAIL_CREATED') union all (select user_id, created from flipkartorders where date(created) >'2015-03-22') order by created desc) as a1 group by a1.user_id, year(a1.created),month(a1.created)) uu on u.id = uu.user_id where u.activated = 1 and (lower(u.referrer) not like 'emp%' or u.utm_campaign is not null) group by year(u.created), month(u.created), uu.yearly, uu.monthly;"""
 38 
buyer="""select year(u.created) creationyear,month(u.created) creationmonth, uu.yearly, uu.monthly,count(*) from users u join (select user_id, year(created) yearly, month(created) monthly from (select user_id, created from order_view where status in ('ORDER_CREATED', 'DETAIL_CREATED') union all (select user_id, created from flipkartorders where date(created) >'2015-03-22') order by created desc) as a1 group by a1.user_id, year(a1.created),month(a1.created)) uu on u.id = uu.user_id where u.activated = 1 and (lower(u.referrer) not like 'emp%' or u.utm_campaign is not null) group by year(u.created), month(u.created), uu.yearly, uu.monthly;"""
39 
 
 39 
 
40 
buyer="""select year(u.created) creationyear, month(u.created) creationmonth, uu.yearly, uu.monthly,count(*) from users u join (select user_id, year(created) yearly,month(created) monthly  from user_urls group by user_id, year(created), month(created)) uu on u.id = uu.user_id where u.activated = 1 and (lower(u.referrer) not like 'emp%' or u.utm_campaign is not null) group by year(u.created), month(u.created),uu.yearly, uu.monthly;"""
 40 
activeuser="""select year(u.created) creationyear, month(u.created) creationmonth, uu.yearly, uu.monthly,count(*) from users u join (select user_id, year(created) yearly,month(created) monthly  from user_urls group by user_id, year(created), month(created)) uu on u.id = uu.user_id where u.activated = 1 and (lower(u.referrer) not like 'emp%' or u.utm_campaign is not null) group by year(u.created), month(u.created),uu.yearly, uu.monthly;"""
41 
 
 41 
 
42 
activegroup = """select year(created) creationmonth,month(created) creationmonth, a.yearly, a.monthly, count(*) from (select u.usergroup_id, min(u.created) created, uu.yearly,uu.monthly from users u join (select user_id, year(created) yearly,month(created) monthly  from user_urls group by user_id,  year(created),month(created)) uu on u.id = uu.user_id where u.activated = 1 and (lower(u.referrer) not like 'emp%' or u.utm_campaign is not null)  group by u.usergroup_id, uu.yearly, uu.monthly) a group by year(created), month(created), a.yearly, a.monthly;"""
 42 
activegroup = """select year(created) creationmonth,month(created) creationmonth, a.yearly, a.monthly, count(*) from (select u.usergroup_id, min(u.created) created, uu.yearly,uu.monthly from users u join (select user_id, year(created) yearly,month(created) monthly  from user_urls group by user_id,  year(created),month(created)) uu on u.id = uu.user_id where u.activated = 1 and (lower(u.referrer) not like 'emp%' or u.utm_campaign is not null)  group by u.usergroup_id, uu.yearly, uu.monthly) a group by year(created), month(created), a.yearly, a.monthly;"""
43 
 
 43 
 
44 
 
 44 
 
45 
buyergroup = """select year(created) creationmonth, month(created) creationmonth, a.yearly, a.monthly, count(*) from (select u.usergroup_id, min(u.created) created, uu.yearly,uu.monthly from users u join (select user_id, year(created) yearly, month(created) monthly from order_view where status in ('ORDER_CREATED', 'DETAIL_CREATED') union all select user_id, year(created) yearly, month(created) monthly from flipkartorders where date(created) >'2015-03-22' group by user_id, year(created), month(created)) uu on u.id = uu.user_id where u.activated = 1 and (lower(u.referrer) not like 'emp%' or u.utm_campaign is not null)  group by u.usergroup_id, uu.monthly) a group by year(created), month(created),a.yearly,a.monthly;"""
 45 
buyergroup = """select year(created) creationmonth, month(created) creationmonth, a.yearly, a.monthly, count(*) from (select u.usergroup_id, min(u.created) created, uu.yearly,uu.monthly from users u join (select user_id, year(created) yearly, month(created) monthly from order_view where status in ('ORDER_CREATED', 'DETAIL_CREATED') union all select user_id, year(created) yearly, month(created) monthly from flipkartorders where date(created) >'2015-03-22' group by user_id, year(created), month(created)) uu on u.id = uu.user_id where u.activated = 1 and (lower(u.referrer) not like 'emp%' or u.utm_campaign is not null)  group by u.usergroup_id, uu.monthly) a group by year(created), month(created),a.yearly,a.monthly;"""
46 
 
 46 
 
47 
months = {0:"Jan",1:"Feb",2:"Mar",3:"Apr",4:"May",5:"Jun",6:"Jul",7:"Aug",8:"Sep",9:"Oct",10:"Nov",11:"Dec"}
 47 
months = {1:"Jan",2:"Feb",3:"Mar",4:"Apr",5:"May",6:"Jun",7:"Jul",8:"Aug",9:"Sep",10:"Oct",11:"Nov",12:"Dec"}
48 
 
 48 
 
49 
def getDbConnection():
 49 
def getDbConnection():
50 
    return MySQLdb.connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME)
 50 
    return MySQLdb.connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME)
51 
 
 51 
 
52 
boldStyle = xlwt.XFStyle()
 52 
boldStyle = xlwt.XFStyle()
Line 86... Line 86...
86 
    x=startrow
 86 
    x=startrow
87 
    y=0
 87 
    y=0
88 
    creationMap={}
 88 
    creationMap={}
89 
    activityMap={}
 89 
    activityMap={}
90 
    for row in result:
 90 
    for row in result:
91 
        creationym = ("%s %s")%(row[0], months[row[1]])
 91 
        creationym = ("%s %s")%(months[row[1]],row[0])
92 
        activityym =  ("%s %s")%(row[2], months[row[3]])
 92 
        activityym =  ("%s %s")%(months[row[3]], row[2])
93 
        if creationym not in creationMap:
 93 
        if creationym not in creationMap:
94 
            x += 1
 94 
            x += 1
95 
            creationMap[creationym] = x
 95 
            creationMap[creationym] = x
96 
            sheet.write(x,0, creationym, boldStyle)
 96 
            sheet.write(x,0, creationym, boldStyle)
97 
        if activityym not in activityMap:
 97 
        if activityym not in activityMap: