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.brand,x.category_id,x.count,x.source,x.weight 
  from
    (
    select c.user_id,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.user_id
    
    UNION
   
    select m.userId as user_id,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.userId
  
    UNION
  
    select fo.user_id,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.user_id
    
    UNION
    
    select bp.user_id,bp.brand,bp.category_id,count(1) as count,"preferences" as source,(uw.weightage* count(1)) as weight
    from brand_preferences bp join user_segmentation_weigthage uw on "preferences"=uw.weightage_type  
    where user_id!=0 and status='show' group by bp.category_id,bp.brand,bp.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=''
    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:
        user_id=r[0]
        brand=r[1]
        if r[2] == 'Mobiles':
            category_id=3
        elif r[2] == 'Tablets':
            category_id=5
        else:
            category_id=r[2] 
        count=r[3]
        source=r[4]
        weight=r[5]
        
        sql = "insert into userlinkssegmentation (user_id, brand, category_id, source, count,weight) values(%s, %s, %s, %s, %s,%s)"
        cursor.execute(sql,(user_id,brand, category_id, source, count,weight))
        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()