Subversion Repositories SmartDukaan

#!/usr/bin/python

from datetime import date
from email import encoders
from email.mime.base import MIMEBase
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from pyExcelerator import Workbook, Font, XFStyle
import MySQLdb
import datetime
import json
import smtplib
import sys
import urllib2
import cookielib


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

#MAILTO = ['rajneesharora@spiceretail.co.in', 'yukti.jain@spiceretail.co.in', 'sandeep.sachdeva@shop2020.in', 'chaitnaya.vats@shop2020.in', 'anupam.singh@shop2020.in']
#SENDER = "cnc.center@shop2020.in"
#PASSWORD = "5h0p2o2o"
#SUBJECT = "Product Report"
#SMTP_SERVER = "smtp.gmail.com"
#SMTP_PORT = 587    
#
#TMP_FILE="/tmp/wallet_ageing.xls"
def getDbConnection():
    return MySQLdb.connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME)
  
def closeConnection(conn):
    conn.close()

def getProductSaleData():
    selectSql = '''SELECT b.userId, b.id AS rechargeId, SUM(h.amount) AS AmountCredited, 
                   LEFT(b.creationTimestamp,10) AS createdOn, w.amount AS currentWalletAmount 
                   FROM baseorder b 
                   JOIN rechargeorder r ON b.id = r.immediateconsumedorder_digitalorder_baseorder_id 
                   JOIN userwallethistory h ON h.orderId = b.id 
                   JOIN userwallet w ON b.userId = w.userId 
                   WHERE w.amount > 0 
                   GROUP BY b.id 
                   HAVING SUM(h.amount) > 0 
                   ORDER BY b.userId, b.id DESC;
                '''
    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()
        activeUser = ''
        toRefund = 0
        amountLeft = 0
        for r in result:
            (userId, rechargeId, AmountCredited, createdOn, currentWalletAmount) = r[0:5]
            int_currentAmount = int(currentWalletAmount)
            int_amountCredited = int(AmountCredited)
            if activeUser != userId:
                if int_currentAmount > int_amountCredited :
                    amountLeft = int_currentAmount - int_amountCredited
                    toRefund = int_amountCredited
                else :
                    amountLeft = 0
                    toRefund = int_currentAmount
            else :
                if amountLeft > int_amountCredited :
                    amountLeft = amountLeft - int_amountCredited
                    toRefund = int_amountCredited
                else :
                    amountLeft = 0
                    toRefund = amountLeft
                
            print userId, rechargeId, AmountCredited, createdOn, toRefund
            activeUser = userId
            
    except Exception as e:
        print "Error: unable to fetch data"
        print e
    
#def createXlsReport(monthdatesmap, prodsalesmap):
#    workbook = Workbook()
#    worksheet = workbook.add_sheet("Sheet 1")
#    boldStyle = XFStyle()
#    f = Font()
#    f.bold = True
#    boldStyle.font = f
#    
#    datecolmap = {}
#    col = 6
#    for monthnumber in monthdatesmap.keys():
#        monthname = monthdatesmap[monthnumber][MONTHNAME]
#        worksheet.write(0, col, monthname, boldStyle)
#        worksheet.write(1, col, 'Month', boldStyle)
#        datemap ={}
#        datemap[0] = col
#        datecolmap[monthnumber] = datemap
#        col += 1
#        
#        worksheet.write(1, 0, 'Category', boldStyle)
#        worksheet.write(1, 1, 'Sub Category', boldStyle)
#        worksheet.write(1, 2, 'Brand', boldStyle)
#        worksheet.write(1, 3, 'Model Name', boldStyle)
#        worksheet.write(1, 4, 'Model Number', boldStyle)
#        worksheet.write(1, 5, 'Color', boldStyle)
#        for dayofmonth in monthdatesmap[monthnumber][DATES]:
#            worksheet.write(1, col, dayofmonth, boldStyle)
#            datemap[dayofmonth] = col
#            col += 1
#    
#    row = 2
#    prodkeys = prodsalesmap.keys()
#    prodkeys.sort()
#    for prodsale in prodkeys:
#        (parent, category, brand, model_name, model_number, color) = prodsale
#        worksheet.write(row, 0, parent, boldStyle)
#        worksheet.write(row, 1, category, boldStyle)
#        worksheet.write(row, 2, brand, boldStyle)
#        worksheet.write(row, 3, model_name if model_name is not None else '', boldStyle)
#        worksheet.write(row, 4, model_number if model_number is not None else '', boldStyle)
#        worksheet.write(row, 5, color if color is not None else 'NA', boldStyle)
#        for monthnumber in prodsalesmap[prodsale].keys():
#            for dayofmonth in prodsalesmap[prodsale][monthnumber]:
#                quantity = prodsalesmap[prodsale][monthnumber][dayofmonth]
#                worksheet.write(row, datecolmap[monthnumber][dayofmonth], quantity)
#        row += 1
#    
#    worksheet.panes_frozen = True
#    worksheet.horz_split_pos = 2
#    worksheet.vert_split_pos = 6
#    workbook.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'] = "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=Product-Report' + ' - ' + date.today().isoformat() + '.xls')
#    msg.attach(fileMsg)
#
#    mailServer.login(SENDER, PASSWORD)
#    mailServer.sendmail(PASSWORD, MAILTO, msg.as_string())

def main():
    getProductSaleData()

if __name__ == '__main__':
    main()