Subversion Repositories SmartDukaan

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, datetime

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

# 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 Accessory Sales Report"
SMTP_SERVER = "smtp.gmail.com"
SMTP_PORT = 587    

TMP_FILE="/home/anupam/repeat_sales.xls"

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

def getProductSaleData():
    selectSql = '''SELECT customer_id, quantity, left(created_timestamp, 10), total_amount 
                    FROM `order` o 
                    JOIN lineitem l 
                    ON o.id = l.order_id 
                    WHERE o.status = 12 and created_timestamp < '2012-07-01 00:00:00'
                    ORDER BY customer_id
                '''
    
    conn = getDbConnection()
    try:
        # prepare a cursor object using cursor() method
        cursor = conn.cursor()
        # Execute the SQL command
        # Fetch source id.
        cursor.execute(selectSql)
        result = cursor.fetchall()
        quantity = 1
        sum = 0
        sumOfDays = 0
        lastSeenId = 0
        listOfTuples = []
        myList = []
        numberOfDays = 1
        numberOfDaysSameDayInclusive = 1
        finalMap = {}
        for r in result:
            if(r[0] == lastSeenId) : 
                newDate = datetime.strptime(r[2], '%Y-%m-%d')
                diff =  newDate - lastDate
                sumOfDays += diff.days
                quantity += r[1]
                if diff.days != 0 :
                    numberOfDays += 1
                numberOfDaysSameDayInclusive += 1
                sum += r[3]
            else :
                #print lastSeenId, quantity, str(sum/quantity), str(sumOfDays/quantity)
                myList = [lastSeenId, quantity, sum/quantity, sumOfDays/numberOfDays, sumOfDays/numberOfDaysSameDayInclusive]
                listOfTuples.append(myList)
                sumOfDays = 0
                lastSeenId = r[0]
                sum = r[3]
                quantity = r[1]
                numberOfDays = 1
                numberOfDaysSameDayInclusive = 1
                lastDate = datetime.strptime(r[2], '%Y-%m-%d')
                
        #print listOfTuples
        for l in listOfTuples :
            #print l[0:4]
            if (finalMap.has_key(l[1])):
                temp = finalMap.get(l[1])
                temp[0] += 1
                temp[1] += l[2]
                temp[2] += l[3]
                temp[3] += l[4]
            else:
                finalMap[l[1]] = [1, l[2], l[3], l[4]]
        
    except Exception as e:
      print "Error: unable to fetch data"
      print e
    
    return finalMap

def createXlsReport(finalMap):
    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, 'Quantity', boldStyle)
    worksheet.write(row, 1, '# of Users', boldStyle)
    worksheet.write(row, 2, 'Avg Value', boldStyle)
    worksheet.write(row, 3, 'Avg Delay Excluding Same Day repeats', boldStyle)
    worksheet.write(row, 4, 'Avg Delay Including Same Day repeats', boldStyle)
    
    row = 1
    
    orderQuantities = finalMap.keys()
    orderQuantities.sort()
    for key in orderQuantities :
        temp1 = finalMap.get(key)
        temp1[1] = temp1[1]/temp1[0]
        temp1[2] = temp1[2]/temp1[0]
        temp1[3] = temp1[3]/temp1[0]
        worksheet.write(row, 0, int(key), boldStyle)
        worksheet.write(row, 1, int(temp1[0]))
        worksheet.write(row, 2, int(temp1[1]))
        worksheet.write(row, 3, int(temp1[2]))
        worksheet.write(row, 4, int(temp1[3]))
        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=Yesterday-Accessory-Sales' + ' - ' + date.today().isoformat() + '.xls')
    msg.attach(fileMsg)

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

def main():
    resultMap = getProductSaleData()
    createXlsReport(resultMap)
    #sendmail(message)

if __name__ == '__main__':
    main()