Subversion Repositories SmartDukaan

'''
Created on Jul 10, 2015

@author: amit
'''
from datetime import date
from dtr.reports.usersegmentation import sendmail
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

'''
Created on Mar 10, 2015

@author: amit
'''



DB_HOST = "localhost"
DB_USER = "root"
DB_PASSWORD = "shop2020"
DB_NAME = "dtr"
TMP_FILE = "/tmp/cohortreport.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
buyer="""select year(u.created) creationyear,month(u.created) creationmonth, uu.yearly, uu.monthly,count(*) from users u join (select user_id, year(created) yearly, month(created) monthly 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 a1.user_id, year(a1.created),month(a1.created)) uu on u.id = uu.user_id where u.activated = 1 and (lower(u.referrer) not like 'emp%' or u.utm_campaign is not null) group by year(u.created), month(u.created), uu.yearly, uu.monthly;"""

activeuser="""select year(u.created) creationyear, month(u.created) creationmonth, uu.yearly, uu.monthly,count(*) from users u join (select user_id, year(visited) yearly,month(visited) monthly  from daily_visitors group by user_id, year(visited), month(visited)) uu on u.id = uu.user_id where u.activated = 1 and (lower(u.referrer) not like 'emp%' or u.utm_campaign is not null) group by year(u.created), month(u.created),uu.yearly, uu.monthly;"""

activegroup = """select year(created) creationmonth,month(created) creationmonth, a.yearly, a.monthly, count(*) from (select u.usergroup_id, min(u.created) created, uu.yearly,uu.monthly from users u join (select user_id, year(visited) yearly,month(visited) monthly  from daily_visitors group by user_id,  year(visited),month(visited)) uu on u.id = uu.user_id where u.activated = 1 and (lower(u.referrer) not like 'emp%' or u.utm_campaign is not null)  group by u.usergroup_id, uu.yearly, uu.monthly) a group by year(created), month(created), a.yearly, a.monthly;"""


buyergroup = """select year(created) creationmonth, month(created) creationmonth, a.yearly, a.monthly, count(*) from (select u.usergroup_id, min(u.created) created, uu.yearly,uu.monthly from users u join (select user_id, year(created) yearly, month(created) monthly from order_view where status in ('ORDER_CREATED', 'DETAIL_CREATED') union all select user_id, year(created) yearly, month(created) monthly from flipkartorders where date(created) >'2015-03-22' group by user_id, year(created), month(created)) uu on u.id = uu.user_id where u.activated = 1 and (lower(u.referrer) not like 'emp%' or u.utm_campaign is not null)  group by u.usergroup_id, uu.monthly) a group by year(created), month(created),a.yearly,a.monthly;"""

months = {1:"Jan",2:"Feb",3:"Mar",4:"Apr",5:"May",6:"Jun",7:"Jul",8:"Aug",9:"Sep",10:"Oct",11:"Nov",12:"Dec"}

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

boldStyle = xlwt.XFStyle()
f = xlwt.Font()
f.bold = True
boldStyle.font = f

def main():
    conn = getDbConnection()
    workbook = xlwt.Workbook()
    worksheet = workbook.add_sheet("Cohort Analysis")
    try:
        cursor = conn.cursor()
        cursor.execute(activeuser)
        result = cursor.fetchall()
        nextrow = parseResultToSheet(worksheet,result, "Active Users", 0)
        cursor = conn.cursor()
        cursor.execute(buyer)
        result = cursor.fetchall()
        nextrow = parseResultToSheet(worksheet,result, "Buyers", nextrow)
        cursor = conn.cursor()
        cursor.execute(activegroup)
        result = cursor.fetchall()
        nextrow = parseResultToSheet(worksheet,result, "Active Groups", nextrow)
        cursor = conn.cursor()
        cursor.execute(buyergroup)
        result = cursor.fetchall()
        parseResultToSheet(worksheet,result, "Buyer Groups", nextrow)
        
        workbook.save("/tmp/cohortreport.xls")
        sendmail(['rajneesh.arora@saholic.com','manas.kapoor@shop2020.in'], "", "/tmp/cohortreport.xls", "Report for Cohort analysis")
    finally:
        conn.close()

def parseResultToSheet(sheet, result, title, startrow):
    sheet.write(startrow, 0, title, boldStyle)
    x=startrow
    y=0
    creationMap={}
    activityMap={}
    for row in result:
        creationym = ("%s %s")%(months[row[1]],row[0])
        activityym =  ("%s %s")%(months[row[3]], row[2])
        if creationym not in creationMap:
            x += 1
            creationMap[creationym] = x
            sheet.write(x,0, creationym, boldStyle)
        if activityym not in activityMap:
            y+=1
            activityMap[activityym] = y
            sheet.write(startrow,y, activityym,boldStyle)
            
        sheet.write(creationMap[creationym],activityMap[activityym], row[4])
    return x+2

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

        
if __name__ == '__main__':
    main()