| Line 59... |
Line 59... |
| 59 |
when ucreated < date(now()) - interval 3 day and total = 0 and last_active < date(now()) - interval 3 day then 'INU'
|
59 |
when ucreated < date(now()) - interval 3 day and total = 0 and last_active < date(now()) - interval 3 day then 'INU'
|
| 60 |
when ucreated >= date(now()) - interval 3 day and total = 0 and date(last_active) = date(ucreated) then 'FIU'
|
60 |
when ucreated >= date(now()) - interval 3 day and total = 0 and date(last_active) = date(ucreated) then 'FIU'
|
| 61 |
else 'OTH'
|
61 |
else 'OTH'
|
| 62 |
end
|
62 |
end
|
| 63 |
usersegment, final.*
|
63 |
usersegment, final.*
|
| 64 |
from (select u.id, u.username, u.first_name, u.mobile_number, u.usergroup_id, ug.groupbasis,
|
64 |
from (select u.id, u.username, u.first_name, u.mobile_number, u.usergroup_id, ug.groupbasis, count(u.*)
|
| 65 |
max(u.created) ucreated, min(u.created) minucreated, max(ua.last_active) last_active, max(ow.created) created, if(sum(ow.total) is null, 0, sum(ow.total)) as total
|
65 |
max(u.created) ucreated, min(u.created) minucreated, max(ua.last_active) last_active, max(ow.created) created, if(sum(ow.total) is null, 0, sum(ow.total)) as total
|
| 66 |
from users u left join useractive ua on ua.user_id=u.id left join usergroups ug on ug.id=u.usergroup_id
|
66 |
from users u left join useractive ua on ua.user_id=u.id left join usergroups ug on ug.id=u.usergroup_id
|
| 67 |
left join (select *, count(*) as total from (select user_id, created from order_view where status in ('ORDER_CREATED', 'DETAIL_CREATED')
|
67 |
left join (select *, count(*) as total from (select user_id, created from order_view where status in ('ORDER_CREATED', 'DETAIL_CREATED')
|
| 68 |
union all (select user_id, created from flipkartorders where date(created) >'2015-03-22') order by created desc) as a1 group by user_id) as ow
|
68 |
union all (select user_id, created from flipkartorders where date(created) >'2015-03-22') order by created desc) as a1 group by user_id) as ow
|
| 69 |
on ow.user_id = u.id
|
69 |
on ow.user_id = u.id
|
| 70 |
where lower(u.referrer) not like 'emp%' or u.referrer is null group by usergroup_id) final;
|
70 |
where lower(u.referrer) not like 'emp%' and u.activated=1 group by usergroup_id) final;
|
| 71 |
"""
|
71 |
"""
|
| 72 |
|
72 |
|
| 73 |
|
73 |
|
| 74 |
date_format = xlwt.XFStyle()
|
74 |
date_format = xlwt.XFStyle()
|
| 75 |
date_format.num_format_str = 'dd/mm/yyyy'
|
75 |
date_format.num_format_str = 'dd/mm/yyyy'
|
| Line 163... |
Line 163... |
| 163 |
worksheet.write(row, 2, 'Username', boldStyle)
|
163 |
worksheet.write(row, 2, 'Username', boldStyle)
|
| 164 |
worksheet.write(row, 3, 'Name', boldStyle)
|
164 |
worksheet.write(row, 3, 'Name', boldStyle)
|
| 165 |
worksheet.write(row, 4, 'Mobile', boldStyle)
|
165 |
worksheet.write(row, 4, 'Mobile', boldStyle)
|
| 166 |
worksheet.write(row, 5, 'UserGroupId', boldStyle)
|
166 |
worksheet.write(row, 5, 'UserGroupId', boldStyle)
|
| 167 |
worksheet.write(row, 6, 'Group basis', boldStyle)
|
167 |
worksheet.write(row, 6, 'Group basis', boldStyle)
|
| - |
|
168 |
worksheet.write(row, 7, 'Users Count', boldStyle)
|
| 168 |
worksheet.write(row, 7, 'User last created', boldStyle)
|
169 |
worksheet.write(row, 8, 'User last created', boldStyle)
|
| 169 |
worksheet.write(row, 8, 'User first created', boldStyle)
|
170 |
worksheet.write(row, 9, 'User first created', boldStyle)
|
| 170 |
worksheet.write(row, 9, 'User Last Active On', boldStyle)
|
171 |
worksheet.write(row, 10, 'User Last Active On', boldStyle)
|
| 171 |
worksheet.write(row, 10, 'Order Last Created On', boldStyle)
|
172 |
worksheet.write(row, 11, 'Order Last Created On', boldStyle)
|
| 172 |
worksheet.write(row, 11, 'Total Orders', boldStyle)
|
173 |
worksheet.write(row, 12, 'Total Orders', boldStyle)
|
| 173 |
for r in result:
|
174 |
for r in result:
|
| 174 |
row += 1
|
175 |
row += 1
|
| 175 |
column = 0
|
176 |
column = 0
|
| 176 |
for data in r :
|
177 |
for data in r :
|
| 177 |
worksheet.write(row, column, int(data) if type(data) is float else data, datetime_format if type(data) is datetime.datetime else default_format)
|
178 |
worksheet.write(row, column, int(data) if type(data) is float else data, datetime_format if type(data) is datetime.datetime else default_format)
|