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

activeuser="""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;"""

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 user_urls 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 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(created) yearly,month(created) monthly  from user_urls 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.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 = {0:"Jan",1:"Feb",2:"Mar",3:"Apr",4:"May",5:"Jun",6:"Jul",7:"Aug",8:"Sep",9:"Oct",10:"Nov",11:"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([], "", "/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")%(row[0], months[row[1]])

        activityym =  ("%s %s")%(row[2], months[row[3]])

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