Subversion Repositories SmartDukaan

Rev

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

Rev 14585 Rev 14721
Line 9... Line 9...
9 
from email.mime.multipart import MIMEMultipart
 9 
from email.mime.multipart import MIMEMultipart
10 
from email.mime.text import MIMEText
 10 
from email.mime.text import MIMEText
11 
import MySQLdb
 11 
import MySQLdb
12 
import datetime
 12 
import datetime
13 
import smtplib
 13 
import smtplib
- 
 
 14 
import traceback
14 
import xlwt
 15 
import xlwt
15 
 
 16 
 
16 
 
 17 
 
17 
 
 18 
 
18 
DB_HOST = "localhost"
 19 
DB_HOST = "localhost"
Line 37... Line 38...
37 
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'
38 
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'
39 
else 'OTH'
 40 
else 'OTH'
40 
end
 41 
end
41 
usersegment, final.*
 42 
usersegment, final.*
42 
from (select u.id, u.username, u.first_name, u.mobile_number, u.mobile_verified, u.referrer,
43 
from (select u.id, u.username, u.first_name, u.mobile_number, u.mobile_verified,
43 
if(bp.user_id is null, 'FALSE', 'TRUE') as bpref,
 44 
if(bp.user_id is null, 'FALSE', 'TRUE') as bpref,
44 
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
45 
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
46 
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
47 
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
48 
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
49 
where lower(u.referrer) not like 'emp%') final;
 51 
where lower(u.referrer) not like 'emp%') final;
Line 86... Line 88...
86 
        worksheet.write(row, 1, 'User Id', boldStyle)
 88 
        worksheet.write(row, 1, 'User Id', boldStyle)
87 
        worksheet.write(row, 2, 'Username', boldStyle)
 89 
        worksheet.write(row, 2, 'Username', boldStyle)
88 
        worksheet.write(row, 3, 'Name', boldStyle)
 90 
        worksheet.write(row, 3, 'Name', boldStyle)
89 
        worksheet.write(row, 4, 'Mobile', boldStyle)
 91 
        worksheet.write(row, 4, 'Mobile', boldStyle)
90 
        worksheet.write(row, 5, 'Mobile Verified', boldStyle)
 92 
        worksheet.write(row, 5, 'Mobile Verified', boldStyle)
91 
        worksheet.write(row, 6, 'Referrer', boldStyle)
 93 
        worksheet.write(row, 6, 'Brand Preference', boldStyle)
92 
        worksheet.write(row, 7, 'Brand Preference', boldStyle)
 94 
        worksheet.write(row, 7, 'Pricing Preference', boldStyle)
93 
        worksheet.write(row, 8, 'Pricing Preference', boldStyle)
 95 
        worksheet.write(row, 8, 'User Created On', boldStyle)
94 
        worksheet.write(row, 9, 'User Created On', boldStyle)
 96 
        worksheet.write(row, 9, 'User Last Active On', boldStyle)
95 
        worksheet.write(row, 10, 'User Last Active On', boldStyle)
 97 
        worksheet.write(row, 10, 'Order Last Created On', boldStyle)
96 
        worksheet.write(row, 11, 'Order Last Created On', boldStyle)
 98 
        worksheet.write(row, 11, 'Total Orders', boldStyle)
97 
        worksheet.write(row, 12, 'Total Orders', boldStyle)
 99 
        worksheet.write(row, 12, 'Referrer', boldStyle)
- 
 
 100 
        worksheet.write(row, 13, 'Campaign', boldStyle)
- 
 
 101 
        worksheet.write(row, 14, 'Content', boldStyle)
- 
 
 102 
        worksheet.write(row, 15, 'Term', boldStyle)
- 
 
 103 
        worksheet.write(row, 16, 'Medium', boldStyle)
- 
 
 104 
        worksheet.write(row, 17, 'Utm Source', boldStyle)
98 
 
 105 
 
99 
        for r in result:
 106 
        for r in result:
100 
            row += 1
 107 
            row += 1
101 
            column = 0
 108 
            column = 0
102 
            for data in r :
 109 
            for data in r :
103 
                worksheet.write(row, column, int(data) if type(data) is float else data, datetime_format if type(data) is datetime.datetime else default_format)
 110 
                worksheet.write(row, column, int(data) if type(data) is float else data, datetime_format if type(data) is datetime.datetime else default_format)
104 
                column += 1
 111 
                column += 1
105 
        workbook.save(TMP_FILE)
 112 
        workbook.save(TMP_FILE)
106 
        sendmail(["rajneesh.arora@saholic.com", "amit.sirohi@saholic.com", "chaitnaya.vats@saholic.com"], "", TMP_FILE, SUBJECT)
 113 
        sendmail(["rajneesh.arora@saholic.com", "amit.sirohi@saholic.com", "chaitnaya.vats@saholic.com"], "", TMP_FILE, SUBJECT)
107 
    except:
 114 
    except:
- 
 
 115 
        traceback.print_exc()
108 
        print "Could not create report"
 116 
        print "Could not create report"
109 
 
 117 
 
110 
def sendmail(email, message, fileName, title):
 118 
def sendmail(email, message, fileName, title):
111 
    if email == "":
 119 
    if email == "":
112 
        return
 120 
        return