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

from datetime import datetime, timedelta

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

# KEY NAMES
MAILTO = ['abhishek.mathur@shop2020.in', 'sitakanta@mysmartprice.com', 'anupam.singh@shop2020.in']
SENDER = "cnc.center@shop2020.in"
PASSWORD = "5h0p2o2o"
SUBJECT = "MySmartPrice Report"
SMTP_SERVER = "smtp.gmail.com"
SMTP_PORT = 587    

TMP_FILE="/tmp/mysmartprice_report.xls"

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

def getCount(dateYesterday):
    selectCount = "select count(*) from productactivity where date_id = (select date_id from datedim where fulldate = '" + dateYesterday + "')"
                  
    conn = getDbConnection()
    try:
        # prepare a cursor object using cursor() method
        cursor = conn.cursor()
        # Execute the SQL command
        # Fetch source id.
        cursor.execute(selectCount)
        result = cursor.fetchall()
        for r in result:
            count = r[0]
            
    except Exception as e:
      print "Error: unable to fetch data"
      print e
    
    return count

def getProductSaleData():
    selectSql = '''select concat_ws(' ', catalog_item.brand, catalog_item.model_name, catalog_item.model_number) as Product,
                   sum(p.unique_view) as Views, sum(p.unique_add_to_cart) as AddToCarts 
                   from productactivity p 
                   join catalog_item catalog_item on (catalog_item.catalog_item_id = p.catalog_item_id) 
                   where date_id = (select MAX(date_id) from productactivity) and session_source_id in (980) group by p.catalog_item_id
                '''
    conn = getDbConnection()
    productTuples = []
    
    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:
            productTuple = r[0:3]
            productTuples.append(productTuple)
          
    except Exception as e:
      print "Error: unable to fetch data"
      print e
    
    return productTuples

def createXlsReport(productTuples, dateYesterday):
    workbook = Workbook()
    worksheet = workbook.add_sheet("Sheet 1")
    boldStyle = XFStyle()
    f = Font()
    f.bold = True
    boldStyle.font = f

    timeNow = datetime.now()
    worksheet.write(0, 0, dateYesterday, boldStyle)
    row = 3
    
    worksheet.write(row, 0, 'Product', boldStyle)
    worksheet.write(row, 1, 'Views', boldStyle)
    worksheet.write(row, 2, 'Add to Carts', boldStyle)
    
    for productTuple in productTuples:
        row += 1
        worksheet.write(row, 0, productTuple[0])
        worksheet.write(row, 1, str(productTuple[1]))
        worksheet.write(row, 2, str(productTuple[2]))
        
    workbook.save(TMP_FILE)
    print "Spreadsheet Saved"

def sendmail(dateYesterday):
    mailServer = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)
    mailServer.ehlo()
    mailServer.starttls()
    mailServer.ehlo()
    
    # Create the container (outer) email message.
    msg = MIMEMultipart()
    msg['Subject'] = SUBJECT + ' - ' + dateYesterday
    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=MySmartPrice-Report' + ' - ' + dateYesterday + '.xls')
    msg.attach(fileMsg)

    mailServer.login(SENDER, PASSWORD)
    mailServer.sendmail(PASSWORD, MAILTO, msg.as_string())
    print "Mail Sent"

def main():
    dateYesterday = str((datetime.now() - timedelta(days=1)).date())
    count = getCount(dateYesterday)
    if count == 0:
        print "No data found for date : " + dateYesterday
        exit(1)
    
    productTuples = getProductSaleData()
    print "Number of tuples on " + dateYesterday + " : " + str(len(productTuples))
    createXlsReport(productTuples, dateYesterday)
    sendmail(dateYesterday)
    
if __name__ == '__main__':
    main()