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