Subversion Repositories SmartDukaan

'''
Created on 25-Jun-2012

@author: anupam
'''

#!/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, timedelta

# Initialize db connection settings.
DB_HOST = "localhost"
DB_USER = "root"
DB_PASSWORD = "shop2020"
DB_NAME = "warehouse"

MAILTO = ['khushal.bhatia@shop2020.in', 'chaitnaya.vats@shop2020.in', 'rajneesh.arora@shop2020.in', 'chandan.kumar@shop2020.in', 'sandeep.sachdeva@shop2020.in', 'manish.sharma@shop2020.in', 'kshitij.sood@shop2020.in', 'manoj.kumar@shop2020.in', 'chandan.kumar@shop2020.in']
SENDER = "cnc.center@shop2020.in"
PASSWORD = "5h0p2o2o"
SUBJECT = "Vendor Fulfilment Report"
SMTP_SERVER = "smtp.gmail.com"
SMTP_PORT = 587    

TMP_FILE="/tmp/vendor_fulfilment.xls"

def getDbConnection():
    return MySQLdb.connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME)
  
def closeConnection(conn):
    conn.close()

def getProductSaleData(fromDate, toDate):
    selectSql = '''SELECT s.name, 
                          CONCAT_WS(' ', l.brand, ifnull(l.modelname, ''), l.modelnumber, ifnull(l.color, '')) AS Product, 
                          SUM(l.quantity) AS `Qty Ordered`, 
                          SUM(l.unfulfilledQuantity) AS `Qty Unfulfilled` 
                          FROM lineitem l 
                          JOIN purchaseorder p ON l.purchaseOrder_id = p.id 
                          JOIN supplier s ON p.supplierId = s.id 
                          WHERE l.createdAt BETWEEN \'''' + fromDate + '''\' AND \'''' + toDate + '''\' GROUP BY s.name, l.itemId;'''
    conn = getDbConnection()
    #data = {}
    try:
        # prepare a cursor object using cursor() method
        cursor = conn.cursor()
        # Execute the SQL command
        # Fetch source id.
        cursor.execute(selectSql)
        result = cursor.fetchall()
        
    except Exception as e:
        print "Error: unable to fetch data"
        print e
    
    return result

def createXlsReport(wb, data, sheetNumber):
    if sheetNumber == 1:
        sheetName = "Yesterday"
    if sheetNumber == 2:
        sheetName = "MTD"
    worksheet = wb.add_sheet(sheetName)
    boldStyle = XFStyle()
    f = Font()
    f.bold = True
    boldStyle.font = f
    
    row = 0
    
    worksheet.write(row, 0, "VENDOR", boldStyle)
    worksheet.write(row, 1, "PRODUCT", boldStyle)
    worksheet.write(row, 2, "QTY REQUIRED", boldStyle)
    worksheet.write(row, 3, "QTY UNFULFILLED", boldStyle)
    
    row += 2
    
    for datum in data:
        worksheet.write(row, 0, datum[0])
        worksheet.write(row, 1, datum[1])
        worksheet.write(row, 2, datum[2])
        worksheet.write(row, 3, datum[3])
        row += 1
        
    wb.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'] = "PO@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=Vendor-Fulfilment' + ' - ' + date.today().isoformat() + '.xls')
    msg.attach(fileMsg)

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

def main():
    workbook = Workbook()
    
    timeNow = datetime.datetime.now()
    toDate = timeNow.strftime('%Y-%m-%d %H:%M:%S')
    fromDate = (timeNow - timedelta(days=1)).strftime('%Y-%m-%d %H:%M:%S')
    data = getProductSaleData(fromDate, toDate)
    createXlsReport(workbook, data, 1)

    fromDate = datetime.datetime(datetime.datetime.now().year, datetime.datetime.now().month, 1, 0, 0, 0, 0)
    data = getProductSaleData(str(fromDate), str(toDate))
    createXlsReport(workbook, data, 2)
    
    sendmail()

if __name__ == '__main__':
    main()