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, 
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   
from users u left join useractive ua on ua.user_id=u.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 = 'ORDER_CREATED' order by id desc) as a1 group by user_id) as ow on ow.user_id = u.id  
where lower(u.referrer) not like 'emp%' or u.referrer is null) 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()
        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, 'Brand Preference', boldStyle)
        worksheet.write(row, 7, 'Pricing Preference', boldStyle)
        worksheet.write(row, 8, 'User Created On', boldStyle)
        worksheet.write(row, 9, 'User Last Active On', boldStyle)
        worksheet.write(row, 10, 'Order Last Created On', boldStyle)
        worksheet.write(row, 11, 'Total Orders', boldStyle)
        worksheet.write(row, 12, 'Referrer', boldStyle)
        worksheet.write(row, 13, 'Campaign', boldStyle)
        worksheet.write(row, 14, 'Content', boldStyle)
        worksheet.write(row, 15, 'Term', boldStyle)
        worksheet.write(row, 16, 'Medium', boldStyle)
        worksheet.write(row, 17, 'Utm Source', boldStyle)
        worksheet.write(row, 18, 'Activation Flag', 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)
        sendmail(["rajneesh.arora@saholic.com", "amit.sirohi@saholic.com", "chaitnaya.vats@saholic.com", "shailesh.kumar@shop2020.in", "manoj.kumar@shop2020.in"], "", TMP_FILE, SUBJECT)
    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()