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', 'yukti.jain@spiceretail.co.in', 'sandeep.sachdeva@shop2020.in', 'chaitnaya.vats@shop2020.in', 'pankaj.kankar@shop2020.in', 'pankaj.jain@spiceglobal.com', 'ashutosh.saxena@shop2020.in', 'asghar.bilgrami@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.monthnumber in (month(now()), month(now())-1)
                          and d.year in (year(now()))
                          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()