Subversion Repositories SmartDukaan

Rev

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

Rev 15507 Rev 15512
Line 38... Line 38...
38 
when ucreated <  date(now()) - interval 3 day and total = 0 and last_active <  date(now()) - interval 3 day then 'INU'
 38 
when ucreated <  date(now()) - interval 3 day and total = 0 and last_active <  date(now()) - interval 3 day then 'INU'
39 
when ucreated >=  date(now()) - interval 3 day and total = 0 and date(last_active) = date(ucreated) then 'FIU'
 39 
when ucreated >=  date(now()) - interval 3 day and total = 0 and date(last_active) = date(ucreated) then 'FIU'
40 
else 'OTH'
 40 
else 'OTH'
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,
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
47 
from users u left join useractive ua on ua.user_id=u.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' order by id desc) 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 
where lower(u.referrer) not like 'emp%' or u.referrer is null) final;
52 
"""
 52 
"""
Line 89... Line 89...
89 
        worksheet.write(row, 2, 'Username', boldStyle)
 89 
        worksheet.write(row, 2, 'Username', boldStyle)
90 
        worksheet.write(row, 3, 'Name', boldStyle)
 90 
        worksheet.write(row, 3, 'Name', boldStyle)
91 
        worksheet.write(row, 4, 'Mobile', boldStyle)
 91 
        worksheet.write(row, 4, 'Mobile', boldStyle)
92 
        worksheet.write(row, 5, 'Mobile Verified', boldStyle)
 92 
        worksheet.write(row, 5, 'Mobile Verified', boldStyle)
93 
        worksheet.write(row, 6, 'UserGroupId', boldStyle)
 93 
        worksheet.write(row, 6, 'UserGroupId', boldStyle)
- 
 
 94 
        worksheet.write(row, 7, 'Group basis', boldStyle)
94 
        worksheet.write(row, 7, 'Brand Preference', boldStyle)
 95 
        worksheet.write(row, 8, 'Brand Preference', boldStyle)
95 
        worksheet.write(row, 8, 'Pricing Preference', boldStyle)
 96 
        worksheet.write(row, 9, 'Pricing Preference', boldStyle)
96 
        worksheet.write(row, 9, 'User Created On', boldStyle)
 97 
        worksheet.write(row, 10, 'User Created On', boldStyle)
97 
        worksheet.write(row, 10, 'User Last Active On', boldStyle)
 98 
        worksheet.write(row, 11, 'User Last Active On', boldStyle)
98 
        worksheet.write(row, 11, 'Order Last Created On', boldStyle)
 99 
        worksheet.write(row, 12, 'Order Last Created On', boldStyle)
99 
        worksheet.write(row, 12, 'Total Orders', boldStyle)
 100 
        worksheet.write(row, 13, 'Total Orders', boldStyle)
100 
        worksheet.write(row, 13, 'Referrer', boldStyle)
 101 
        worksheet.write(row, 14, 'Referrer', boldStyle)
101 
        worksheet.write(row, 14, 'Campaign', boldStyle)
 102 
        worksheet.write(row, 15, 'Campaign', boldStyle)
102 
        worksheet.write(row, 15, 'Content', boldStyle)
 103 
        worksheet.write(row, 16, 'Content', boldStyle)
103 
        worksheet.write(row, 16, 'Term', boldStyle)
 104 
        worksheet.write(row, 17, 'Term', boldStyle)
104 
        worksheet.write(row, 17, 'Medium', boldStyle)
 105 
        worksheet.write(row, 18, 'Medium', boldStyle)
105 
        worksheet.write(row, 18, 'Utm Source', boldStyle)
 106 
        worksheet.write(row, 19, 'Utm Source', boldStyle)
106 
        worksheet.write(row, 19, 'Activation Flag', boldStyle)
 107 
        worksheet.write(row, 20, 'Activation Flag', boldStyle)
107 
 
 108 
 
108 
        for r in result:
 109 
        for r in result:
109 
            row += 1
 110 
            row += 1
110 
            column = 0
 111 
            column = 0
111 
            for data in r :
 112 
            for data in r :