Subversion Repositories SmartDukaan

Rev

Rev 6713 | Blame | Compare with Previous | Last modification | View Log | RSS feed 

import json
import urllib2, cookielib
import MySQLdb
import datetime
import sys
import smtplib
import xlwt

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', 'chaitnaya.vats@shop2020.in', 'ashutosh.saxena@shop2020.in', 'chandan.kumar@shop2020.in', 'anupam.singh@shop2020.in']
SENDER = "cnc.center@shop2020.in"
PASSWORD = "5h0p2o2o"
SUBJECT = "Previous Day Accessory Sales Report"
SMTP_SERVER = "smtp.gmail.com"
SMTP_PORT = 587    

TMP_FILE="/tmp/previous_accessory_sales.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.brand, IFNULL(i.model_name, ''),
                    IFNULL(i.model_number, ''), i.color, s.source, sum(quantity) AS quantity, sum(total_amount) AS totalAmount
                    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 in (DATE_SUB(curdate(), INTERVAL 1 DAY)) 
                    AND os.statusGroup in ('Delivered', 'In process', 'Refunded', 'Return in process', 'Unused') 
                    AND i.parent_category = 'Mobile Accessories' 
                    AND os.statusSubGroup not in ('Cancellation pending') 
                    GROUP BY i.parent_category, i.category, i.brand, IFNULL(i.model_name, ''), IFNULL(i.model_number, ''), i.color, s.source
                    ORDER BY i.parent_category, i.brand;
                '''
    
    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()
        msg =   """\
                    <html>
                    <body>\n<h3>Website  ======WebsiteSale====== </h3><h3>Amazon  ======AmazonSale====== </h3>\n<table border="1">
                    \n<thead>\n
                    <th>Category\n</th>
                    <th>Brand\n</th>
                    <th>Model Name\n</th>
                    <th>Model Number\n</th>
                    <th>Color\n</th>
                    <th>Source\n</th>
                    <th>Quantity\n</th>
                    <th>Value\n</th>
                    \n</thead>
                """ 
        column = 0
        grossTotal = 0
        grossQuantity = 0
        websiteSale = 0
        amazonSale = 0
        websiteQty = 0
        amazonQty = 0
        msg = msg + '<tr><td colspan="5"><b>Total</b></td><td>======QuantityToBeReplaced======</td><td>======ValueToBeReplaced======</td></tr>'
        for r in result:
            msg = msg + '<tr>'
            source = 1
            for data in r:
                if column == 7 :
                    grossTotal += data
                    if source == 1:
                        websiteSale += data
                    if source == 3:
                        amazonSale += data
                if column == 6 :
                    grossQuantity += data
                    if source == 1:
                        websiteQty += data
                    if source == 3:
                        amazonQty += data
                if column == 5 :
                    source = data
                msg = msg + '<td>' + str(data) + '</td>'
                column += 1
            column = 0
            msg = msg + '</tr>'

        msg = msg + '</table>\n</body>\n</html>'
        msg = msg.replace('======QuantityToBeReplaced======', str(grossQuantity))
        msg = msg.replace('======ValueToBeReplaced======', str(grossTotal))
        #msg = msg.replace('======WebsiteQty=====', str(websiteQty))
        #msg = msg.replace('======AmazonQty=====', str(amazonQty))
        msg = msg.replace('======WebsiteSale======', "Sale - " + str(websiteSale) + "    Quantity - " + str(websiteQty))
        msg = msg.replace('======AmazonSale======', "Sale - " + str(amazonSale) + "    Quantity - " + str(amazonQty))
    except Exception as e:
      print "Error: unable to fetch data"
      print e
    
    return msg, result

def createXlsReport(result):
    workbook = xlwt.Workbook()
    worksheet = workbook.add_sheet("Sheet 1")
    boldStyle = xlwt.XFStyle()
    f = xlwt.Font()
    f.bold = True
    boldStyle.font = f
    
    datecolmap = {}
    column = 0
    row = 0
    
    worksheet.write(row, 0, 'Category', boldStyle)
    worksheet.write(row, 1, 'Brand', boldStyle)
    worksheet.write(row, 2, 'Model Name', boldStyle)
    worksheet.write(row, 3, 'Model Number', boldStyle)
    worksheet.write(row, 4, 'Color', boldStyle)
    worksheet.write(row, 5, 'Source', boldStyle)
    worksheet.write(row, 6, 'Quantity', boldStyle)
    worksheet.write(row, 7, 'Value', boldStyle)
    
    row = 2
    grossTotal = 0
    grossQuantity = 0
    
    for r in result:
        #(parent, category, brand, model_name, model_number, color) = r[0:6]
        #dayofmonth = r[8]
        for data in r :
            if column == 7 :
                grossTotal += data
            if column == 6 :
                grossQuantity += data
            worksheet.write(row, column, str(data))
            column += 1
        column = 0
        row += 1

    worksheet.write_merge(1, 1, 0, 4, 'Total')
    worksheet.write(1, 6, str(grossQuantity), boldStyle)
    worksheet.write(1, 7, str(grossTotal), boldStyle)
    workbook.save(TMP_FILE)

def sendmail(message):
    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()
    html_msg = MIMEText(message, 'html')
    msg.attach(html_msg)
    
    fileMsg = MIMEBase('application','vnd.ms-excel')
    fileMsg.set_payload(file(TMP_FILE).read())
    encoders.encode_base64(fileMsg)
    fileMsg.add_header('Content-Disposition','attachment;filename=Yesterday-Accessory-Sales' + ' - ' + date.today().isoformat() + '.xls')
    msg.attach(fileMsg)

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

def main():
    message, result = getProductSaleData()
    createXlsReport(result)
    sendmail(message)

if __name__ == '__main__':
    main()