Subversion Repositories SmartDukaan

Rev

Rev 5653 | Go to most recent revision | Blame | Compare with Previous | Last modification | View Log | RSS feed 

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 = ['anupam.singh@shop2020.in']
#['rajneesharora@spiceretail.co.in', 'pankaj.kankar@shop2020.in', 'ashutosh.saxena@shop2020.in', 'anupam.singh@shop2020.in']
SENDER = "cnc.center@shop2020.in"
PASSWORD = "5h0p2o2o"
SUBJECT = "Previous Day Orders Report"
SMTP_SERVER = "smtp.gmail.com"
SMTP_PORT = 587    

TMP_FILE="/tmp/previous_orders_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, 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 in (DATE_SUB(curdate(), INTERVAL 1 DAY)) 
                    AND os.statusGroup in ('In process', 'Delivered', 'Cancelled', 'Return in process', 'Reshipped', 'Refunded') 
                    AND i.parent_category != 'Mobile Accessories' 
                    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;
                '''
    
    
    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<table border="1">
                    \n<thead>\n
                    <th>Category\n</th>
                    <th>Sub Category\n</th>
                    <th>Brand\n</th>
                    <th>Model Name\n</th>
                    <th>Model Number\n</th>
                    <th>Color\n</th>
                    <th>Quantity\n</th>
                    \n</thead>
                """ 
        for r in result:
            #(parent, category, brand, model_name, model_number, color) = r[0:6]
            #dayofmonth = r[8]
            msg = msg + '<tr>'
            for col in r:
                msg = msg + '<td>' + str(col) + '</td>'
            msg = msg + '</tr>'
    except Exception as e:
      print "Error: unable to fetch data"
      print e
    
    msg = msg + '</table>\n</body>\n</html>'
    return msg, result

def createXlsReport(result):
    workbook = Workbook()
    worksheet = workbook.add_sheet("Sheet 1")
    boldStyle = XFStyle()
    f = Font()
    f.bold = True
    boldStyle.font = f
    
    datecolmap = {}
    column = 0
    row = 0
    
    worksheet.write(row, 0, 'Category', boldStyle)
    worksheet.write(row, 1, 'Sub Category', boldStyle)
    worksheet.write(row, 2, 'Brand', boldStyle)
    worksheet.write(row, 3, 'Model Name', boldStyle)
    worksheet.write(row, 4, 'Model Number', boldStyle)
    worksheet.write(row, 5, 'Color', boldStyle)
    worksheet.write(row, 6, 'Quantity', boldStyle)
    
    row = 1
    
    for r in result:
        #(parent, category, brand, model_name, model_number, color) = r[0:6]
        #dayofmonth = r[8]
        for data in r :
            worksheet.write(row, column, str(data))
            column += 1
        column = 0
        row += 1
    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=Last-Day-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()