Subversion Repositories SmartDukaan

#!/usr/bin/python

import json

import urllib2, cookielib

import MySQLdb

import datetime

import sys

import smtplib

from email import encoders

from email.mime.text import MIMEText

from email.mime.base import MIMEBase

from email.mime.multipart import MIMEMultipart

from pyExcelerator import Workbook, Font, XFStyle

from datetime import date

# Initialize db connection settings.

DB_HOST = "localhost"

DB_USER = "root"

DB_PASSWORD = "shop2020"

DB_NAME = "sales"

# KEY NAMES

MONTHNAME = 'monthname'

DATES = 'dates'

MAILTO = ['rajneesharora@spiceretail.co.in', 'chandan.kumar@shop2020.in', 'khushal.bhatia@shop2020.in', 'yukti.jain@spiceretail.co.in', 'sandeep.sachdeva@shop2020.in', 'chaitnaya.vats@shop2020.in', 'anupam.singh@shop2020.in']

SENDER = "cnc.center@shop2020.in"

PASSWORD = "5h0p2o2o"

SUBJECT = "Product Report"

SMTP_SERVER = "smtp.gmail.com"

SMTP_PORT = 587    

TMP_FILE="/tmp/product_report.xls"

def getDbConnection():

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

def closeConnection(conn):

    conn.close()

def getProductSaleData():

    selectSql = '''select i.parent_category, i.category, i.brand, IFNULL(i.model_name, ''), IFNULL(i.model_number, ''), 

                          i.color, d.monthname, d.monthnumber, d.dayofmonth, sum(quantity) as quantity 

                          from sales s 

                          join item i on (i.id = s.item_id) 

                          join datedim d on (d.date_id = s.date_id)

                          join orderstatus os on (s.status = os.status) 

                          where d.fulldate <= curdate() and d.fulldate >= date_sub(curdate(), interval 2 month)

                          and os.statusGroup in ('In process', 'Delivered', 'Cancelled', 'Return in process', 'Reshipped', 'Refunded')

                          and os.statusSubGroup != 'Cod verification failed'

                          group by i.parent_category, i.category, i.brand, IFNULL(i.model_name, ''), 

                                   IFNULL(i.model_number, ''), i.color, d.monthname, d.monthnumber, d.dayofmonth

                '''

    conn = getDbConnection()

    monthdatesmap = {}

    prodsalesmap = {}

    try:

        # prepare a cursor object using cursor() method

        cursor = conn.cursor()

        # Execute the SQL command

        # Fetch source id.

        cursor.execute(selectSql)

        result = cursor.fetchall()

        for r in result:

            (parent, category, brand, model_name, model_number, color) = r[0:6]

            monthname, monthnumber = r[6:8]

            dayofmonth = r[8]

            if monthdatesmap.has_key(monthnumber):

                monthmap = monthdatesmap.get(monthnumber)

                monthmap.get(DATES).add(dayofmonth)

            else:

                monthmap = {}

                monthmap[MONTHNAME] = monthname

                dates = set([])

                dates.add(dayofmonth)

                monthmap[DATES] = dates

                monthdatesmap[monthnumber] = monthmap

            if prodsalesmap.has_key((parent, category, brand, model_name, model_number, color)):

                prodsales = prodsalesmap.get((parent, category, brand, model_name, model_number, color))

                if prodsales.has_key(monthnumber):

                    monthprodsales = prodsales.get(monthnumber)

                    monthprodsales[dayofmonth] = r[9]

                    monthprodsales[0] += r[9]

                else:

                    monthprodsales = {}

                    monthprodsales[dayofmonth] = r[9]

                    monthprodsales[0] = r[9]

                    prodsales[monthnumber] = monthprodsales

            else:

                prodsale = {}

                monthprodsales = {}

                monthprodsales[dayofmonth] = r[9]

                monthprodsales[0] = r[9]

                prodsale[monthnumber] = monthprodsales

                prodsalesmap[(parent, category, brand, model_name, model_number, color)] = prodsale

    except Exception as e:

      print "Error: unable to fetch data"

      print e

    return monthdatesmap, prodsalesmap

def createXlsReport(monthdatesmap, prodsalesmap):

    workbook = Workbook()

    worksheet = workbook.add_sheet("Sheet 1")

    boldStyle = XFStyle()

    f = Font()

    f.bold = True

    boldStyle.font = f

    datecolmap = {}

    col = 6

    for monthnumber in monthdatesmap.keys():

        monthname = monthdatesmap[monthnumber][MONTHNAME]

        worksheet.write(0, col, monthname, boldStyle)

        worksheet.write(1, col, 'Month', boldStyle)

        datemap ={}

        datemap[0] = col

        datecolmap[monthnumber] = datemap

        col += 1

        worksheet.write(1, 0, 'Category', boldStyle)

        worksheet.write(1, 1, 'Sub Category', boldStyle)

        worksheet.write(1, 2, 'Brand', boldStyle)

        worksheet.write(1, 3, 'Model Name', boldStyle)

        worksheet.write(1, 4, 'Model Number', boldStyle)

        worksheet.write(1, 5, 'Color', boldStyle)

        for dayofmonth in monthdatesmap[monthnumber][DATES]:

            worksheet.write(1, col, dayofmonth, boldStyle)

            datemap[dayofmonth] = col

            col += 1

    row = 2

    prodkeys = prodsalesmap.keys()

    prodkeys.sort()

    for prodsale in prodkeys:

        (parent, category, brand, model_name, model_number, color) = prodsale

        worksheet.write(row, 0, parent, boldStyle)

        worksheet.write(row, 1, category, boldStyle)

        worksheet.write(row, 2, brand, boldStyle)

        worksheet.write(row, 3, model_name if model_name is not None else '', boldStyle)

        worksheet.write(row, 4, model_number if model_number is not None else '', boldStyle)

        worksheet.write(row, 5, color if color is not None else 'NA', boldStyle)

        for monthnumber in prodsalesmap[prodsale].keys():

            for dayofmonth in prodsalesmap[prodsale][monthnumber]:

                quantity = prodsalesmap[prodsale][monthnumber][dayofmonth]

                worksheet.write(row, datecolmap[monthnumber][dayofmonth], quantity)

        row += 1

    worksheet.panes_frozen = True

    worksheet.horz_split_pos = 2

    worksheet.vert_split_pos = 6

    workbook.save(TMP_FILE)

def sendmail():

    mailServer = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)

    mailServer.ehlo()

    mailServer.starttls()

    mailServer.ehlo()

    # Create the container (outer) email message.

    msg = MIMEMultipart()

    msg['Subject'] = SUBJECT + ' - ' + date.today().isoformat()

    msg['From'] = "bi@saholic.com"

    msg['To'] = 'sku-recipients@saholic.com'

    msg.preamble = SUBJECT + ' - ' + date.today().isoformat() 

    fileMsg = MIMEBase('application','vnd.ms-excel')

    fileMsg.set_payload(file(TMP_FILE).read())

    encoders.encode_base64(fileMsg)

    fileMsg.add_header('Content-Disposition','attachment;filename=Product-Report' + ' - ' + date.today().isoformat() + '.xls')

    msg.attach(fileMsg)

    mailServer.login(SENDER, PASSWORD)

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

def main():

    (monthdatesmap, prodsalesmap) = getProductSaleData()

    createXlsReport(monthdatesmap, prodsalesmap)

    sendmail()

if __name__ == '__main__':

    main()