| Line 41... |
Line 41... |
| 41 |
end
|
41 |
end
|
| 42 |
usersegment, final.*
|
42 |
usersegment, final.*
|
| 43 |
from (select u.id, u.username, u.first_name, u.mobile_number, u.mobile_verified,u.usergroup_id, ug.groupbasis,
|
43 |
from (select u.id, u.username, u.first_name, u.mobile_number, u.mobile_verified,u.usergroup_id, ug.groupbasis,
|
| 44 |
if(bp.user_id is null, 'FALSE', 'TRUE') as bpref,
|
44 |
if(bp.user_id is null, 'FALSE', 'TRUE') as bpref,
|
| 45 |
if(pp.user_id is null, 'FALSE', 'TRUE') as ppref, u.created ucreated, ua.last_active, ow.created, if(ow.total is null, 0, ow.total) as total,
|
45 |
if(pp.user_id is null, 'FALSE', 'TRUE') as ppref, u.created ucreated, ua.last_active, ow.created, if(ow.total is null, 0, ow.total) as total,
|
| 46 |
u.referrer,u.utm_campaign, u.utm_content, u.utm_term, u.utm_medium, u.utm_source, u.activated
|
46 |
u.referrer,u.utm_campaign, u.utm_content, u.utm_term, u.utm_medium, u.utm_source, u.activated, r.id as retailer_id, r.status
|
| 47 |
from users u left join useractive ua on ua.user_id=u.id left join usergroups ug on ug.id=u.usergroup_id
|
47 |
from users u left join useractive ua on ua.user_id=u.id left join usergroups ug on ug.id=u.usergroup_id
|
| 48 |
left join (select distinct user_id from price_preferences) pp on pp.user_id=u.id
|
48 |
left join (select distinct user_id from price_preferences) pp on pp.user_id=u.id
|
| 49 |
left join (select distinct user_id from brand_preferences) bp on bp.user_id=u.id
|
49 |
left join (select distinct user_id from brand_preferences) bp on bp.user_id=u.id
|
| 50 |
left join (select *, count(*) as total from (select user_id, created from order_view where status = 'ORDER_CREATED' order by id desc) as a1 group by user_id) as ow on ow.user_id = u.id
|
50 |
left join (select *, count(*) as total from (select user_id, created from order_view where status = 'ORDER_CREATED' union select user_id, created from flipkartorders ) as a1 group by user_id) as ow on ow.user_id = u.id
|
| 51 |
where lower(u.referrer) not like 'emp%' or u.referrer is null) final;
|
51 |
left join retailerlinks rl on rl.user_id=u.id left join retailers r on r.id=rl.retailer_id where lower(u.referrer) not like 'emp%' or u.referrer is null) final;
|
| 52 |
"""
|
52 |
"""
|
| 53 |
|
53 |
|
| 54 |
|
54 |
|
| 55 |
date_format = xlwt.XFStyle()
|
55 |
date_format = xlwt.XFStyle()
|
| 56 |
date_format.num_format_str = 'dd/mm/yyyy'
|
56 |
date_format.num_format_str = 'dd/mm/yyyy'
|
| Line 103... |
Line 103... |
| 103 |
worksheet.write(row, 16, 'Content', boldStyle)
|
103 |
worksheet.write(row, 16, 'Content', boldStyle)
|
| 104 |
worksheet.write(row, 17, 'Term', boldStyle)
|
104 |
worksheet.write(row, 17, 'Term', boldStyle)
|
| 105 |
worksheet.write(row, 18, 'Medium', boldStyle)
|
105 |
worksheet.write(row, 18, 'Medium', boldStyle)
|
| 106 |
worksheet.write(row, 19, 'Utm Source', boldStyle)
|
106 |
worksheet.write(row, 19, 'Utm Source', boldStyle)
|
| 107 |
worksheet.write(row, 20, 'Activation Flag', boldStyle)
|
107 |
worksheet.write(row, 20, 'Activation Flag', boldStyle)
|
| - |
|
108 |
worksheet.write(row, 21, 'Retailer Id', boldStyle)
|
| - |
|
109 |
worksheet.write(row, 22, 'Retailer Status', boldStyle)
|
| 108 |
|
110 |
|
| 109 |
for r in result:
|
111 |
for r in result:
|
| 110 |
row += 1
|
112 |
row += 1
|
| 111 |
column = 0
|
113 |
column = 0
|
| 112 |
for data in r :
|
114 |
for data in r :
|
| 113 |
worksheet.write(row, column, int(data) if type(data) is float else data, datetime_format if type(data) is datetime.datetime else default_format)
|
115 |
worksheet.write(row, column, int(data) if type(data) is float else data, datetime_format if type(data) is datetime.datetime else default_format)
|
| 114 |
column += 1
|
116 |
column += 1
|
| 115 |
workbook.save(TMP_FILE)
|
117 |
workbook.save(TMP_FILE)
|
| 116 |
sendmail(["rajneesh.arora@saholic.com", "chaitnaya.vats@saholic.com", ], "", TMP_FILE, SUBJECT)
|
118 |
sendmail(["rajneesh.arora@saholic.com"], "", TMP_FILE, SUBJECT)
|
| 117 |
except:
|
119 |
except:
|
| 118 |
traceback.print_exc()
|
120 |
traceback.print_exc()
|
| 119 |
print "Could not create report"
|
121 |
print "Could not create report"
|
| 120 |
|
122 |
|
| 121 |
def sendmail(email, message, fileName, title):
|
123 |
def sendmail(email, message, fileName, title):
|