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', '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):

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

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

if __name__ == '__main__':

    main()