Subversion Repositories SmartDukaan

import MySQLdb

import xlwt

from email import encoders

from email.mime.base import MIMEBase

from email.mime.multipart import MIMEMultipart

from xlrd import open_workbook

from xlutils.copy import copy

import smtplib

from dtr.storage.DataService import brand_preferences

DB_HOST = "localhost"

DB_USER = "root"

DB_PASSWORD = "shop2020"

DB_NAME = "dtr"

#TMP_FILE = "/home/kshitij/Downloads/User_Segmentation_Links.xls"

TMP_FILE = "User_Segmentation_Links.xls"

SENDER = "cnc.center@shop2020.in"

PASSWORD = "5h0p2o2o"

SUBJECT = "User Segmentation Report"

SMTP_SERVER = "smtp.gmail.com"

SMTP_PORT = 587    

date_format = xlwt.XFStyle()

date_format.num_format_str = 'yyyy/mm/dd'

datetime_format = xlwt.XFStyle()

datetime_format.num_format_str = 'yyyy/mm/dd HH:MM AM/PM'

default_format = xlwt.XFStyle()

USER_SEGMENTATION_LINK_QUERY="""

select x.user_id,x.product_name,x.brand,x.category_id,x.count,x.source,x.weight 

  from

    (

    select c.user_id,c.product_name,c.brand,c.category_id,count(c.id) as count,"clicks" as source,(uw.weightage * count(c.id)) as weight 

    from clicks c join user_segmentation_weigthage uw on "clicks"=uw.weightage_type

    where  c.category_id is not null and c.category_id !=0 and c.brand is not null and 

    c.brand not like '' and c.user_id!=0 group by c.category_id,c.brand,c.store_product_id,c.user_id

    UNION

    select m.userId as user_id,m.productName as product_name,m.brand,m.categoryId as category_id,count(1) as count,"orders" as source,(uw.weightage * count(1)) as weight

    from merchantsuborders m join user_segmentation_weigthage uw on "orders"=uw.weightage_type 

    where m.categoryId is not null and m.brand is not null and m.userId!=0 group by m.categoryId,m.brand,m.productCode,m.userId

    UNION

    select fo.user_id,fo.title as product_name,fo.brand,fo.category as category_id,count(fo.id) as count,"orders" as source, (uw.weightage * count(fo.id)) as weight

    from flipkartorders fo join user_segmentation_weigthage uw on "orders"=uw.weightage_type 

    where  fo.category is not null and fo.brand is not null and  fo.brand not like '' and fo.user_id!=0 and fo.user_id is not null group by fo.category,fo.brand,fo.catalogId,fo.user_id

    UNION

    select uf.user_id,uf.filters as product_name,"NULL" as brand,"NULL" as category_id,count(1) as count,

    "filters" as source,(uw.weightage * count(1)) as weight from user_filters uf join user_segmentation_weigthage uw on "filters"=uw.weightage_type  where uf.type='brand' group by uf.filters,uf.user_id

    ) as x 

"""

def getDbConnection():

    return MySQLdb.connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME)

def generateUserSegmentationLinksReport():

    datesql= USER_SEGMENTATION_LINK_QUERY

    conn = getDbConnection()

    global workbook

    workbook = xlwt.Workbook()

    worksheet = workbook.add_sheet("User Segmentation")

    boldStyle = xlwt.XFStyle()

    style = xlwt.XFStyle()

    pattern = xlwt.Pattern()

    pattern.pattern = xlwt.Pattern.SOLID_PATTERN

    pattern.pattern_fore_colour = xlwt.Style.colour_map['yellow']

    style.pattern = pattern

    f = xlwt.Font()

    f.bold = True

    boldStyle.font = f

    column = 0

    row = 0

    worksheet.write(row,column,'User Id',style)

    worksheet.write(row,column+1,'Product Name',style)

    worksheet.write(row,column+2,'Brand',style)

    worksheet.write(row,column+3,'Category Id',style)

    worksheet.write(row,column+4,'Count',style)

    worksheet.write(row,column+5,'Source',style)

    worksheet.write(row,column+6,'Weight',style)

    cursor = conn.cursor()

    cursor.execute(datesql)

    result = cursor.fetchall()

    for r in result:

        row=row+1

        worksheet.write(row,column,r[0])

        worksheet.write(row,column+1,r[1])

        worksheet.write(row,column+2,r[2])

        worksheet.write(row,column+3,r[3])

        worksheet.write(row,column+4,r[4])

        worksheet.write(row,column+5,r[5])

        worksheet.write(row,column+6,r[6])

    conn.close()

    workbook.save(TMP_FILE)   

def addUsersForLinks():

    user_id=''

    product_name=''

    brand=''

    category_id='' 

    source=''

    count=''

    weight=''

    datesql= USER_SEGMENTATION_LINK_QUERY

    conn = getDbConnection()

    cursor = conn.cursor()

    cursor.execute(datesql)

    result = cursor.fetchall()

    for r in result:

        print r[3]

        user_id=r[0]

        product_name=r[1]

        brand=r[2]

        if r[3] == 'Mobiles':

            category_id=3

        elif r[3] == 'Tablets':

            category_id=5

        else:

            category_id=r[3] 

        source=r[5]

        count=r[4]

        weight=r[6]

        sql = "insert into userlinkssegmentation (user_id, product_name, brand, category_id, source, count,weight) values(%s, '%s', '%s', %s, '%s', %s,%s)" %(user_id,product_name,brand, category_id, source, count,weight)

        print sql

        cursor.execute(sql)

        conn.commit()

    conn.close()

def deleteUserForLinks():

    conn = getDbConnection()

    cursor = conn.cursor()

    deleteAllUserGroups = "delete from userlinkssegmentation"

    try:

        cursor.execute(deleteAllUserGroups)

        conn.commit()

    except:

        conn.rollback()

    finally:

        conn.close()    

def main():

    #generateUserSegmentationLinksReport()

    deleteUserForLinks()

    addUsersForLinks()

def sendmail(email, message, fileName, title):

    if email == "":

        return

    mailServer = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)

    mailServer.ehlo()

    mailServer.starttls()

    mailServer.ehlo()

    msg = MIMEMultipart()

    msg['Subject'] = title

    msg.preamble = title

    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)

    MAILTO = ['manas.kapoor@saholic.com']

    mailServer.login(SENDER, PASSWORD)

    mailServer.sendmail(PASSWORD, MAILTO, msg.as_string())

if __name__=='__main__':

    main()