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 = ['anupam.singh@shop2020.in', 'amar.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()

        for r in result:

            supplier = r[0:1][0]

            if data.get(supplier):

                data.get(supplier).append(r[1:4])

            else:

                data[supplier] = [r[1:4]]

    except Exception as e:

      print "Error: unable to fetch data"

      print e

    return data

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 = -3

    for supplier in data.keys():

        row += 3

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

        row += 2

        worksheet.write(row, 0, "PRODUCT", boldStyle)

        worksheet.write(row, 1, "QTY REQUIRED", boldStyle)

        worksheet.write(row, 2, "QTY UNFULFILLED", boldStyle) 

        lineitems = data.get(supplier)

        for lineitem in lineitems:

            row += 1

            worksheet.write(row, 0, lineitem[0])

            worksheet.write(row, 1, lineitem[1])

            worksheet.write(row, 2, lineitem[2])

    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()