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 = ['manas.kapoor@saholic.com']
    mailServer.login(SENDER, PASSWORD)
    mailServer.sendmail(PASSWORD, MAILTO, msg.as_string())
        
def main():
    generateProductWiseReport()
    generateUserCountFavoritesReport()
    generateUserProductReport()
    sendmail(["manas.kapoor@shop2020.in"], "", TMP_FILE, SUBJECT)
    
if __name__=='__main__':
    main()