Subversion Repositories SmartDukaan

Rev

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

Rev 15057 Rev 15507
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,
43 
from (select u.id, u.username, u.first_name, u.mobile_number, u.mobile_verified,u.usergroup_id,
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
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
Line 88... Line 88...
88 
        worksheet.write(row, 1, 'User Id', boldStyle)
 88 
        worksheet.write(row, 1, 'User Id', boldStyle)
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, 'Brand Preference', boldStyle)
 94 
        worksheet.write(row, 7, 'Brand Preference', boldStyle)
94 
        worksheet.write(row, 7, 'Pricing Preference', boldStyle)
 95 
        worksheet.write(row, 8, 'Pricing Preference', boldStyle)
95 
        worksheet.write(row, 8, 'User Created On', boldStyle)
 96 
        worksheet.write(row, 9, 'User Created On', boldStyle)
96 
        worksheet.write(row, 9, 'User Last Active On', boldStyle)
 97 
        worksheet.write(row, 10, 'User Last Active On', boldStyle)
97 
        worksheet.write(row, 10, 'Order Last Created On', boldStyle)
 98 
        worksheet.write(row, 11, 'Order Last Created On', boldStyle)
98 
        worksheet.write(row, 11, 'Total Orders', boldStyle)
 99 
        worksheet.write(row, 12, 'Total Orders', boldStyle)
99 
        worksheet.write(row, 12, 'Referrer', boldStyle)
 100 
        worksheet.write(row, 13, 'Referrer', boldStyle)
100 
        worksheet.write(row, 13, 'Campaign', boldStyle)
 101 
        worksheet.write(row, 14, 'Campaign', boldStyle)
101 
        worksheet.write(row, 14, 'Content', boldStyle)
 102 
        worksheet.write(row, 15, 'Content', boldStyle)
102 
        worksheet.write(row, 15, 'Term', boldStyle)
 103 
        worksheet.write(row, 16, 'Term', boldStyle)
103 
        worksheet.write(row, 16, 'Medium', boldStyle)
 104 
        worksheet.write(row, 17, 'Medium', boldStyle)
104 
        worksheet.write(row, 17, 'Utm Source', boldStyle)
 105 
        worksheet.write(row, 18, 'Utm Source', boldStyle)
105 
        worksheet.write(row, 18, 'Activation Flag', boldStyle)
 106 
        worksheet.write(row, 19, 'Activation Flag', boldStyle)
106 
 
 107 
 
107 
        for r in result:
 108 
        for r in result:
108 
            row += 1
 109 
            row += 1
109 
            column = 0
 110 
            column = 0
110 
            for data in r :
 111 
            for data in r :
111 
                worksheet.write(row, column, int(data) if type(data) is float else data, datetime_format if type(data) is datetime.datetime else default_format)
 112 
                worksheet.write(row, column, int(data) if type(data) is float else data, datetime_format if type(data) is datetime.datetime else default_format)
112 
                column += 1
 113 
                column += 1
113 
        workbook.save(TMP_FILE)
 114 
        workbook.save(TMP_FILE)
114 
        sendmail(["rajneesh.arora@saholic.com", "amit.sirohi@saholic.com", "chaitnaya.vats@saholic.com", "shailesh.kumar@shop2020.in", "manoj.kumar@shop2020.in"], "", TMP_FILE, SUBJECT)
 115 
        sendmail(["rajneesh.arora@saholic.com", "chaitnaya.vats@saholic.com", ], "", TMP_FILE, SUBJECT)
115 
    except:
 116 
    except:
116 
        traceback.print_exc()
 117 
        traceback.print_exc()
117 
        print "Could not create report"
 118 
        print "Could not create report"
118 
 
 119 
 
119 
def sendmail(email, message, fileName, title):
 120 
def sendmail(email, message, fileName, title):