Subversion Repositories SmartDukaan

from dtr.storage import Mongo

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

DB_HOST = "localhost"

DB_USER = "root"

DB_PASSWORD = "shop2020"

DB_NAME = "dtr"

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

TMP_FILE = "User_Favorites_Report.xls"

SENDER = "cnc.center@shop2020.in"

PASSWORD = "5h0p2o2o"

SUBJECT = "User Favorite 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()

def getDbConnection():

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

PRODUCTS_USERS="""

select store_product_id,action,count(1) from 

user_actions group by store_product_id,action order by store_product_id;

"""

USER_FAVORITES="""

select ua.store_product_id,ua.user_id,u.email,ua.action 

from user_actions ua join users u on ua.user_id=u.id 

order by ua.user_id,ua.action;

"""

USER_COUNT_LIKES_DISLIKES="""

SELECT user_id,email,count(*),action 

FROM user_actions ua join users u where ua.user_id = u.id 

group by user_id,action order by user_id;

"""

def generateProductWiseReport():

    productName=''

    datesql= PRODUCTS_USERS

    conn = getDbConnection()

    global workbook

    workbook = xlwt.Workbook()

    worksheet = workbook.add_sheet("Product Wise")

    boldStyle = xlwt.XFStyle()

    newStyle= 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,'Store Product Id',style)

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

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

    worksheet.write(row,column+3,'Count of Users',style)

    cursor = conn.cursor()

    cursor.execute(datesql)

    result = cursor.fetchall()

    for r in result:

        row=row+1

        skuData = Mongo.getItem(r[0])

        for items in skuData:

            productName = items['source_product_name']

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

        worksheet.write(row,column+1,productName)

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

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

    conn.close()

    workbook.save(TMP_FILE)

def generateUserProductReport():

    productName=''

    datesql= USER_FAVORITES

    conn = getDbConnection()

    rb = open_workbook(TMP_FILE,formatting_info=True)

    workbook = copy(rb)

    worksheet = workbook.add_sheet("User Likes and Dislikes")

    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,'Store Product Id',style)

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

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

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

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

    cursor = conn.cursor()

    cursor.execute(datesql)

    result = cursor.fetchall()

    for r in result:

        row=row+1

        skuData = Mongo.getItem(r[0])

        for items in skuData:

            productName = items['source_product_name']

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

        worksheet.write(row,column+1,productName)

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

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

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

    conn.close()

    workbook.save(TMP_FILE)

def generateUserCountFavoritesReport():

    datesql= USER_COUNT_LIKES_DISLIKES

    conn = getDbConnection()

    rb = open_workbook(TMP_FILE,formatting_info=True)

    workbook = copy(rb)

    worksheet = workbook.add_sheet("User Count by Action")

    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,'Email',style)

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

    worksheet.write(row,column+3,'Action',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])

    conn.close()

    workbook.save(TMP_FILE)    

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 = ['rajender.singh@saholic.com']

    mailServer.login(SENDER, PASSWORD)

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

def main():

    generateProductWiseReport()

    generateUserCountFavoritesReport()

    generateUserProductReport()

    sendmail(["rajender.singh@shop2020.in"], "", TMP_FILE, SUBJECT)

if __name__=='__main__':

    main()