Subversion Repositories SmartDukaan

Rev

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

Rev 20473 Rev 23554
Line 21... Line 21...
21 
@author: amit
 21 
@author: amit
22 
'''
 22 
'''
23 
 
 23 
 
24 
 
 24 
 
25 
 
 25 
 
26 
DB_HOST = "localhost"
 26 
DB_HOST = "127.0.0.1"
27 
DB_USER = "root"
 27 
DB_USER = "root"
28 
DB_PASSWORD = "shop2020"
 28 
DB_PASSWORD = "shop2020"
29 
DB_NAME = "dtr"
 29 
DB_NAME = "dtr"
30 
TMP_FILE = "/tmp/cohortreport.xls"
30 
TMP_FILE = "/tmp/cohortreport.xls"
31 
 
 31 
 
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 
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 order by creationyear, creationmonth;"""
 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 order by creationyear, creationmonth, uu.yearly, uu.monthly;"""
39 
 
 39 
 
40 
activeuser="""select year(u.created) creationyear, month(u.created) creationmonth, uu.yearly, uu.monthly,count(*) from users u join (select user_id, year(visited) yearly,month(visited) monthly  from daily_visitors group by user_id, year(visited), month(visited)) 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 order by creationyear, creationmonth;"""
 40 
activeuser="""select year(u.created) creationyear, month(u.created) creationmonth, uu.yearly, uu.monthly,count(*) from users u join (select user_id, year(visited) yearly,month(visited) monthly  from daily_visitors group by user_id, year(visited), month(visited)) 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 order by creationyear, creationmonth, uu.yearly, uu.monthly;"""
41 
 
 41 
 
42 
activegroup = """select year(created) creationyear,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(visited) yearly,month(visited) monthly  from daily_visitors group by user_id,  year(visited),month(visited)) 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 order by creationyear, creationmonth;"""
 42 
activegroup = """select year(created) creationyear,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(visited) yearly,month(visited) monthly  from daily_visitors group by user_id,  year(visited),month(visited)) 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 order by creationyear, creationmonth, uu.yearly, uu.monthly;"""
43 
 
 43 
 
44 
 
 44 
 
45 
buyergroup = """select year(created) creationyear, 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 order by creationyear, creationmonth;"""
 45 
buyergroup = """select year(created) creationyear, 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 order by creationyear, creationmonth, uu.yearly, uu.monthly;"""
46 
 
 46 
 
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"}
 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)