WebSVN – SmartDukaan – /trunk/PyDTR/src/dtr/reports/usersegmentation.py

'''
Created on Mar 10, 2015

@author: amit
'''
from datetime import date
from email import encoders
from email.mime.base import MIMEBase
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
import MySQLdb
import datetime
import smtplib
import traceback
import xlwt



DB_HOST = "127.0.0.1"
DB_USER = "root"
DB_PASSWORD = "shop2020"
DB_NAME = "dtr"
TMP_FILE = "/tmp/usersegmentreport.xls"  

# KEY NAMES
SENDER = "cnc.center@shop2020.in"
PASSWORD = "5h0p2o2o"
SUBJECT = "DTR User Segmentation report for " + date.today().isoformat()
SMTP_SERVER = "smtp.gmail.com"
SMTP_PORT = 587    


SEGMENTATION_QUERY = """
select case  
when created >= date(now()) - interval 3 day and total > 0 then 'ABU'
when created <  date(now()) - interval 3 day and total > 0 then 'IBU'
when last_active >=  date(now()) - interval 3 day and total = 0 and date(last_active) <> date(ucreated) then 'ANBU'
when ucreated <  date(now()) - interval 3 day and total = 0 and last_active <  date(now()) - interval 3 day then 'INU'
when ucreated >=  date(now()) - interval 3 day and total = 0 and date(last_active) = date(ucreated) then 'FIU'
else 'OTH'
end
usersegment, final.*
from (select u.id, u.username, u.first_name, u.mobile_number, u.mobile_verified,u.usergroup_id, ug.groupbasis,
if(bp.user_id is null, 'FALSE', 'TRUE') as bpref,
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,
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, d.versioncode
, uas.comment, aua.store_name, aua.address,  aua.city, aua.pincode,aua.state, aua.source
from users u left join useractive ua on ua.user_id=u.id 
left join usergroups ug on ug.id=u.usergroup_id 
left join (select distinct user_id from price_preferences) pp on pp.user_id=u.id
left join (select distinct user_id from brand_preferences) bp on bp.user_id=u.id 
left join (select *, count(*) as total from (select user_id, created from order_view where status in ('ORDER_CREATED', 'DETAIL_CREATED') 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 on ow.user_id = u.id
left join user_activity_status uas on uas.user_id=u.id  
left join (select * from (select * from all_user_addresses order by source) s group by user_id)aua on aua.user_id=u.id
left join (select * from (select user_id, versioncode from devices order by created desc) c group by user_id) d on d.user_id=u.id
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.utm_campaign is not null) and u.activated = 1) final;
"""
GROUP_SEGMENTATION_QUERY = """
select case  
when created >= date(now()) - interval 3 day and total > 0 then 'ABU'
when created <  date(now()) - interval 3 day and total > 0 then 'IBU'
when last_active >=  date(now()) - interval 3 day and total = 0 and date(last_active) <> date(ucreated) then 'ANBU'
when ucreated <  date(now()) - interval 3 day and total = 0 and last_active <  date(now()) - interval 3 day then 'INU'
when ucreated >=  date(now()) - interval 3 day and total = 0 and date(last_active) = date(ucreated) then 'FIU'
else 'OTH'
end
usersegment, final.*  
from (select min(u.id), max(u.id), u.username, u.first_name, u.mobile_number, u.usergroup_id, ug.groupbasis, count(*),
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
from users u left join useractive ua on ua.user_id=u.id left join usergroups ug on ug.id=u.usergroup_id 
left join (select *, count(*) as total from (select user_id, created from order_view where status in ('ORDER_CREATED', 'DETAIL_CREATED') 
            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 
on ow.user_id = u.id  
where (lower(u.referrer) not like 'emp%' or u.utm_campaign is not null) and u.activated=1 group by usergroup_id) final;
"""


date_format = xlwt.XFStyle()
date_format.num_format_str = 'dd/mm/yyyy'

datetime_format = xlwt.XFStyle()
datetime_format.num_format_str = 'dd/mm/yyyy HH:MM AM/PM'

default_format = xlwt.XFStyle()


def getDbConnection():
    return MySQLdb.connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME)


def generateSegmentationReport():
    selectSql = SEGMENTATION_QUERY
    conn = getDbConnection()
    try:
        # prepare a cursor object using cursor() method
        cursor = conn.cursor()
        # Execute the SQL command
        # Fetch source id.
        cursor.execute(selectSql)
        result = cursor.fetchall()
        workbook = xlwt.Workbook("ISO-8859-1")
        worksheet = workbook.add_sheet("Segmented User")
        boldStyle = xlwt.XFStyle()
        f = xlwt.Font()
        f.bold = True
        boldStyle.font = f
        column = 0
        row = 0
        
        worksheet.write(row, 0, 'User Segment', boldStyle)
        worksheet.write(row, 1, 'User Id', boldStyle)
        worksheet.write(row, 2, 'Username', boldStyle)
        worksheet.write(row, 3, 'Name', boldStyle)
        worksheet.write(row, 4, 'Mobile', boldStyle)
        worksheet.write(row, 5, 'Mobile Verified', boldStyle)
        worksheet.write(row, 6, 'UserGroupId', boldStyle)
        worksheet.write(row, 7, 'Group basis', boldStyle)
        worksheet.write(row, 8, 'Brand Preference', boldStyle)
        worksheet.write(row, 9, 'Pricing Preference', boldStyle)
        worksheet.write(row, 10, 'User Created On', boldStyle)
        worksheet.write(row, 11, 'User Last Active On', boldStyle)
        worksheet.write(row, 12, 'Order Last Created On', boldStyle)
        worksheet.write(row, 13, 'Total Orders', boldStyle)
        worksheet.write(row, 14, 'Referrer', boldStyle)
        worksheet.write(row, 15, 'Campaign', boldStyle)
        worksheet.write(row, 16, 'Content', boldStyle)
        worksheet.write(row, 17, 'Term', boldStyle)
        worksheet.write(row, 18, 'Medium', boldStyle)
        worksheet.write(row, 19, 'Utm Source', boldStyle)
        worksheet.write(row, 20, 'Activation Flag', boldStyle)
        worksheet.write(row, 21, 'Retailer Id', boldStyle)
        worksheet.write(row, 22, 'Retailer Status', boldStyle)
        worksheet.write(row, 23, 'Version Code', boldStyle)
        worksheet.write(row, 24, 'Install', boldStyle)
        worksheet.write(row, 25, 'Store', boldStyle)
        worksheet.write(row, 26, 'Address', boldStyle)
        worksheet.write(row, 27, 'City', boldStyle)
        worksheet.write(row, 28, 'Pincode', boldStyle)
        worksheet.write(row, 29, 'State', boldStyle)
        worksheet.write(row, 30, 'Source', boldStyle)

        for r in result:
            row += 1
            column = 0
            for data in r :
                worksheet.write(row, column, int(data) if type(data) is float else data, datetime_format if type(data) is datetime.datetime else  date_format if type(data) is datetime.date else default_format)
                column += 1
        workbook.save(TMP_FILE)
        generateGroupSegmentationReport(workbook)
        sendmail(["amit.gupta@shop2020.in"], "", TMP_FILE, SUBJECT)
        #sendmail([], "", TMP_FILE, SUBJECT)
    except:
        traceback.print_exc()
        print "Could not create report"

def generateGroupSegmentationReport(workbook):
    selectSql = GROUP_SEGMENTATION_QUERY
    conn = getDbConnection()
    try:
        # prepare a cursor object using cursor() method
        cursor = conn.cursor()
        # Execute the SQL command
        # Fetch source id.
        cursor.execute(selectSql)
        result = cursor.fetchall()
        worksheet = workbook.add_sheet("Segmented User Group")
        boldStyle = xlwt.XFStyle()
        f = xlwt.Font()
        f.bold = True
        boldStyle.font = f
        column = 0
        row = 0
        worksheet.write(row, 0, 'User Segment', boldStyle)
        worksheet.write(row, 1, 'First Id', boldStyle)
        worksheet.write(row, 2, 'Last Id', boldStyle)
        worksheet.write(row, 3, 'Username', boldStyle)
        worksheet.write(row, 4, 'Name', boldStyle)
        worksheet.write(row, 5, 'Mobile', boldStyle)
        worksheet.write(row, 6, 'UserGroupId', boldStyle)
        worksheet.write(row, 7, 'Group basis', boldStyle)
        worksheet.write(row, 8, 'Users Count', boldStyle)
        worksheet.write(row, 9, 'User last created', boldStyle)
        worksheet.write(row, 10, 'User first created', boldStyle)
        worksheet.write(row, 11, 'User Last Active On', boldStyle)
        worksheet.write(row, 12, 'Order Last Created On', boldStyle)
        worksheet.write(row, 13, 'Total Orders', boldStyle)
        for r in result:
            row += 1
            column = 0
            for data in r :
                worksheet.write(row, column, int(data) if type(data) is float else data, datetime_format if type(data) is datetime.datetime else default_format)
                column += 1
        workbook.save(TMP_FILE)
    except:
        traceback.print_exc()
        print "Could not create report"

def sendmail(email, message, fileName, title):
    if email == "":
        return
    mailServer = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)
    mailServer.ehlo()
    mailServer.starttls()
    mailServer.ehlo()
    
    # Create the container (outer) email message.
    msg = MIMEMultipart()
    msg['Subject'] = title
    msg.preamble = title
    html_msg = MIMEText(message, 'html')
    msg.attach(html_msg)
    
    fileMsg = MIMEBase('application', 'vnd.ms-excel')
    fileMsg.set_payload(file(TMP_FILE).read())
    encoders.encode_base64(fileMsg)
    fileMsg.add_header('Content-Disposition', 'attachment;filename=' + fileName)
    msg.attach(fileMsg)


    email.append('amit.gupta@shop2020.in')
    MAILTO = email 
    mailServer.login(SENDER, PASSWORD)
    mailServer.sendmail(PASSWORD, MAILTO, msg.as_string())

def main():
    generateSegmentationReport()

if __name__ == '__main__':
    main()
Subversion Repositories SmartDukaan

(root)/trunk/PyDTR/src/dtr/reports/usersegmentation.py @ 23555 – Rev 22435
