| Line 37... |
Line 37... |
| 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, 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 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, 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(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, uu.yearly, uu.monthly;"""
|
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, a.yearly, a.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, uu.yearly, uu.monthly;"""
|
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, a.yearly, a.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)
|