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