Subversion Repositories SmartDukaan

'''

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 = "localhost"

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 default_format)

                column += 1

        workbook.save(TMP_FILE)

        generateGroupSegmentationReport(workbook)

        sendmail(["rajneesh.arora@saholic.com","khushal.bhatia@saholic.com"], "", 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()