Subversion Repositories SmartDukaan

#!/usr/bin/python

'''

It is used to process transactions for which the payment 

was received but the order was not processed.

@author: Rajveer

'''

import optparse

import sys

import datetime

from datetime import timedelta

from elixir import *

from sqlalchemy.sql import func

import urllib

import httplib

from shop2020.utils.EmailAttachmentSender import mail, mail_html

from shop2020.model.v1.order.impl.model.UserWalletHistory import UserWalletHistory

from shop2020.utils.Utils import to_java_date, to_py_date

from shop2020.clients.PaymentClient import PaymentClient

if __name__ == '__main__' and __package__ is None:

    import os

    sys.path.insert(0, os.getcwd())

from datetime import date, timedelta

from shop2020.clients.HelperClient import HelperClient

from shop2020.thriftpy.model.v1.order.ttypes import RechargeOrderStatus,\

    OrderType

from shop2020.model.v1.order.impl.DataAccessors import get_recharge_orders_for_status, update_recharge_order_status,\

    update_recharge_transaction_status, get_next_invoice_number

from shop2020.model.v1.order.impl import DataService

from shop2020.model.v1.order.impl.DataService import RechargeTransaction, HotspotStore,\

    WalletForCompany, WalletHistoryForCompany, RechargeCollection, Company, HotspotServiceMatrix,\

    RechargeVoucherTracker

from shop2020.model.v1.order.impl.model.RechargeOrder import RechargeOrder

from shop2020.model.v1.order.impl.RechargeService import checkTransactionStatus, getRefunds, getBalance 

from sqlalchemy.sql.expression import and_, or_, desc, not_, distinct, cast, between

def main():

    parser = optparse.OptionParser()

    parser.add_option("-H", "--host", dest="hostname",

                      default="localhost",

                      type="string", help="The HOST where the DB server is running",

                      metavar="HOST")

    parser.add_option("-r", "--refund", dest="refund",

                      action="store_true",

                      help="")

    parser.add_option("-u", "--unknown", dest="unknown",

                      action="store_true",

                      help="")

    parser.add_option("-a", "--authorized", dest="authorized",

                      action="store_true",

                      help="")

    parser.add_option("-c", "--collection", dest="collection",

                      action="store_true",

                      help="")

    parser.add_option("-R", "--recon", dest="recon",

                      action="store_true",

                      help="")

    parser.add_option("-T", "--topup", dest="topup",

                      action="store_true",

                      help="")    

    parser.add_option("-t", "--txn-id", dest="txn_id",

                   type="int",

                   help="mark the transaction(recharge order id) TXN_ID as successful",

                   metavar="TXN_ID")

    (options, args) = parser.parse_args()

    if len(args) != 0:

        parser.error("You've supplied extra arguments. Are you sure you want to run this program?")

    DataService.initialize(db_hostname=options.hostname, echoOn=True)

    if options.refund:

        processRefunds()

    if options.recon:

        processRecon()

    if options.unknown:

        processUnknownTransactions()

    if options.authorized:

        processAuthorizedTransactions(options.txn_id)

    if options.collection:

        wallet = WalletForCompany.query.filter(WalletForCompany.id == 3).one()

        oldBalance = wallet.amount

        newBalance = getBalance()  

        wallet.amount = newBalance

        session.commit()

        d = datetime.datetime.now()

        d = d + timedelta(days = -1)

        compute_recharge_collection(d)

        compute_website_recharge_collection(d, oldBalance, newBalance)

    if options.topup:

        topup_company_wallet(1,100000)

def processRecon():

    cdate = datetime.datetime.now() + timedelta(days = -1)

    startTime = datetime.datetime(cdate.year, cdate.month, cdate.day)

    endTime = startTime + timedelta(days=1)

    startTime = datetime.datetime.strptime('2012-10-10 00:00:00', "%Y-%m-%d %H:%M:%S");

    endTime = datetime.datetime.strptime('2013-11-13 17:43:43', "%Y-%m-%d %H:%M:%S");

    '''

    A - All such orders for which we have attempted the recharge and recharge is either successful or unknown.

    B - All such orders for which we have attempted the recharge and we received the refund after this time window.

    C - All such orders for which we have received the refund in this time window, although the recharge was attempted before this window.

    X1 = A + B - C         Net amount debited for all the customers in this time window.

    X2 - Total amount credited to all customers in their wallet under some promotion.

    X3 - Net difference in wallet amount between this time window.

    X4 - Payment received through gateway from all customer in this time window.

    X5 - Payment refunded through gateway by all customer in this time window.

    '''

    A1 = 0

    B1 = 0

    C1 = 0

    A2 = 0

    B2 = 0

    C2 = 0

    R = 0

    P = 0

    X1 = 0

    X2 = 0

    X3 = 0

    X4 = 0

    X5 = 0

    X6 = 0

    D = 0

    sorder = session.query(func.sum(RechargeOrder.totalAmount), func.sum(RechargeOrder.couponAmount)).filter(RechargeOrder.status.in_([RechargeOrderStatus.RECHARGE_SUCCESSFUL, RechargeOrderStatus.PAYMENT_SUCCESSFUL])).filter(RechargeOrder.creationTimestamp.between(startTime,endTime)).first()    

    if sorder and sorder[0]:

        A1 = int(sorder[0])

        A2 = int(sorder[1])

    forder = session.query(func.sum(RechargeOrder.totalAmount), func.sum(RechargeOrder.couponAmount)).filter(RechargeOrder.status.in_([RechargeOrderStatus.RECHARGE_FAILED, RechargeOrderStatus.RECHARGE_FAILED_REFUNDED, RechargeOrderStatus.REFUNDED, RechargeOrderStatus.PARTIALLY_REFUNDED])).filter(RechargeOrder.creationTimestamp.between(startTime,endTime)).filter(not_(RechargeOrder.responseTimestamp.between(startTime,endTime))).first()

    if forder and forder[0]:

        B1 = int(forder[0])

        B2 = int(forder[1])

    rorder = session.query(func.sum(RechargeOrder.totalAmount), func.sum(RechargeOrder.couponAmount)).filter(RechargeOrder.status.in_([RechargeOrderStatus.RECHARGE_FAILED, RechargeOrderStatus.RECHARGE_FAILED_REFUNDED, RechargeOrderStatus.REFUNDED, RechargeOrderStatus.PARTIALLY_REFUNDED])).filter(RechargeOrder.responseTimestamp.between(startTime,endTime)).filter(not_(RechargeOrder.creationTimestamp.between(startTime,endTime))).first()

    if rorder and rorder[0]:

        C1 = int(rorder[0])

        C2 = int(rorder[1])

    R = R + A1 + B1 - C1

    X2 = X2 + A2 + B2 - C2

    rv = session.query(func.sum(RechargeVoucherTracker.amount)).filter(RechargeVoucherTracker.issuedOn.between(startTime,endTime)).first()

    if rv and rv[0]:

        X3 = int(rv[0])

    uw = session.query(func.sum(UserWalletHistory.amount)).filter(UserWalletHistory.timestamp.between(startTime,endTime)).first()

    if uw and uw[0]:

        X4 = int(uw[0])

    #X4 = 191497

    pc = PaymentClient().get_client()

    payments = pc.getPayments(to_java_date(startTime - datetime.timedelta(minutes = 30)), to_java_date(endTime + datetime.timedelta(minutes = 30)), 2, 1) + pc.getPayments(to_java_date(startTime - datetime.timedelta(minutes = 30)), to_java_date(endTime + datetime.timedelta(minutes = 30)), 2, 2) + pc.getPayments(to_java_date(startTime - datetime.timedelta(minutes = 30)), to_java_date(endTime + datetime.timedelta(minutes = 30)), 8, 1) + pc.getPayments(to_java_date(startTime - datetime.timedelta(minutes = 30)), to_java_date(endTime + datetime.timedelta(minutes = 30)), 8, 2)

    for payment in payments:

        if payment.isDigital and to_py_date(payment.successTimestamp) >= startTime and to_py_date(payment.successTimestamp) <= endTime:  

            X5 = X5 + payment.amount

    refunds = RechargeOrder.query.filter(RechargeOrder.status.in_([RechargeOrderStatus.PARTIALLY_REFUNDED, RechargeOrderStatus.REFUNDED])).filter(RechargeOrder.refundTimestamp.between(startTime, endTime)).all()

    pc = PaymentClient().get_client()

    for refund in refunds:

        payments = pc.getPaymentForRechargeTxnId(refund.transaction_id)

        for payment in payments:

            if payment.gatewayId in (1,2) and payment.status == 8 and payment.isDigital:

                X6 = X6 + payment.refundAmount

    P = X2+X3+X5-X4-X6

    D = R - P

    maildata = "<html><body><table border='1'><thead><th>Symbol</th><th>Type</th><th>Amount</th></thead><tbody>"

    maildata += "<tr><td>A</td><td>Recharge Amount</td><td>" + str(A1) + "</td></tr>"

    maildata += "<tr><td>B</td><td>Recharge Amount (Refunded in Future)</td><td>" + str(B1) + "</td></tr>"

    maildata += "<tr><td>C</td><td>Recharge Refund Amount</td><td>" + str(C1) + "</td></tr>"

    maildata += "<tr><td>R=A1+B1-C1</td><td>Net Recharge Amount</td><td>" + str(R) + "</td></tr>"

    maildata += "<tr><td></td><td></td><td></td></tr>"

    maildata += "<tr><td>A</td><td>Recharge Coupon Amount</td><td>" + str(A2) + "</td></tr>"

    maildata += "<tr><td>B</td><td>Recharge Coupon Amount (Refunded in Future)</td><td>" + str(B2) + "</td></tr>"

    maildata += "<tr><td>C</td><td>Recharge Coupon Refund Amount</td><td>" + str(C2) + "</td></tr>"    

    maildata += "<tr><td>X2=A2+B2-C2</td><td>Net Coupon Amount</td><td>" + str(X2) + "</td></tr>"

    maildata += "<tr><td>X3</td><td>Gift Amount</td><td>" + str(X3) + "</td></tr>"

    maildata += "<tr><td>X4</td><td>Wallet Difference</td><td>" + str(X4) + "</td></tr>"

    maildata += "<tr><td>X5</td><td>Payment Amount</td><td>" + str(X5) + "</td></tr>"

    maildata += "<tr><td>X6</td><td>Payment Refund Amount</td><td>" + str(X6) + "</td></tr>"

    maildata += "<tr><td>P=X2+X3+X5-X4-X6</td><td>Net Payments</td><td>" + str(P) + "</td></tr>"

    maildata += "<tr><td></td><td></td><td></td></tr>"

    maildata += "<tr><td>D=R-P</td><td>Net Reconciliation Difference</td><td>" + str(D) + "</td></tr>"

    maildata += "</tbody></table>"

    if D != 0:

        mismatches = RechargeOrder.query.filter(RechargeOrder.status.in_([RechargeOrderStatus.RECHARGE_SUCCESSFUL, RechargeOrderStatus.PAYMENT_SUCCESSFUL])).filter(RechargeOrder.creationTimestamp.between(startTime, endTime)).filter(RechargeOrder.responseTimestamp.between(endTime, endTime + timedelta(minutes = 10))).all()

        if mismatches and len(mismatches) > 0:

            maildata += "<h3>Possible mismatch orders</h3><table style='margin-top:30px;' border='1'><thead><th>OrderId</th><th>Total Amount</th><th>Wallet Amount</th><th>Coupon Amount</th><th>Creation Time</th><th>Response Time</th></thead><tbody>"

            for mismatch in mismatches:

                maildata += "<tr><td>" + str(mismatch.id) + "</td><td>" + str(mismatch.totalAmount) + "</td><td>" + str(mismatch.walletAmount) + "</td><td>" + str(mismatch.couponAmount) + "</td><td>" + str(mismatch.creationTimestamp) + "</td><td>" + str(mismatch.responseTimestamp) + "</td></tr>"        

            maildata += "</tbody></table>"

    maildata += "</body></html>"

    mail_html("cnc.center@shop2020.in", "5h0p2o2o", ["rajveer.singh@shop2020.in", "anupam.singh@shop2020.in", "rajneesh.arora@shop2020.in"], "Customer Recharge Reconciliation for Date:- " + startTime.strftime("%d-%m-%Y"), maildata, [])

def processRefunds():

    todate = datetime.datetime.now()

    for i in range(10):

        orderDate = todate + datetime.timedelta(days= -i)

        refunds = getRefunds(orderDate)

        for key in refunds.keys():

            refund = refunds.get(key)

            refundAmount = refund[0]

            refundDate = refund[1]

            order = RechargeOrder.get_by(spiceTID = key)

            if order:

                amount = order.totalAmount

                isStoreOrder = False

            else:

                order = RechargeTransaction.get_by(spiceTID = key)

                if not order:

                    continue

                isStoreOrder = True

                amount = order.amount

            if order.status == RechargeOrderStatus.RECHARGE_FAILED_REFUNDED:

                print "Refund is already processed."

                continue

            if order.status not in (RechargeOrderStatus.RECHARGE_SUCCESSFUL, RechargeOrderStatus.PAYMENT_SUCCESSFUL, RechargeOrderStatus.RECHARGE_UNKNOWN):

                print "Recharge/Payment is not successful. There is something wrong."

                continue

            if amount != refundAmount:

                print "Refund amount is not same as transaction amount"

                continue

            if isStoreOrder:

                update_recharge_transaction_status(order.id, RechargeOrderStatus.RECHARGE_FAILED_REFUNDED)

            else:

                update_recharge_order_status(order.id, RechargeOrderStatus.RECHARGE_FAILED_REFUNDED)

def processUnknownTransactions():    

    orders = get_recharge_orders_for_status(RechargeOrderStatus.PAYMENT_SUCCESSFUL)

    for order in orders:

        try:

            if order.creationTimestamp + datetime.timedelta(minutes=10) < datetime.datetime.now():

                status, description = checkTransactionStatus('', str(order.id))

                print status, description

                if status:

                    update_recharge_order_status(order.id, RechargeOrderStatus.RECHARGE_SUCCESSFUL)

                else:

                    update_recharge_order_status(order.id, RechargeOrderStatus.RECHARGE_FAILED)

        except:

            print "Do Nothing"

    ## For store transactions

    rorders = RechargeTransaction.query.filter(RechargeTransaction.status.in_([RechargeOrderStatus.RECHARGE_UNKNOWN, RechargeOrderStatus.INIT])).all()

    for order in rorders:

        try:

            if order.transactionTime + datetime.timedelta(minutes=10) < datetime.datetime.now():

                status, description = checkTransactionStatus('', str(order.id))

                print status, description

                if status:

                    update_recharge_transaction_status(order.id, RechargeOrderStatus.RECHARGE_SUCCESSFUL)

                elif order.status == RechargeOrderStatus.INIT:

                    update_recharge_transaction_status(order.id, RechargeOrderStatus.RECHARGE_FAILED)

                else:

                    update_recharge_transaction_status(order.id, RechargeOrderStatus.RECHARGE_FAILED_REFUNDED)

        except:

            print "Do Nothing"

def processAuthorizedTransactions(txn_id):

    update_recharge_order_status(txn_id, RechargeOrderStatus.PAYMENT_SUCCESSFUL)

def compute_recharge_collection(cdate):

    todate = datetime.datetime(cdate.year, cdate.month, cdate.day)

    tomorrow = todate + timedelta(days=1)

    txns = session.query(RechargeTransaction.storeId, RechargeTransaction.payMethod, RechargeTransaction.status, func.sum(RechargeTransaction.amount), func.sum(RechargeTransaction.discount)).filter(RechargeTransaction.status.in_([RechargeOrderStatus.RECHARGE_SUCCESSFUL, RechargeOrderStatus.RECHARGE_FAILED_REFUNDED, RechargeOrderStatus.RECHARGE_UNKNOWN])).filter(RechargeTransaction.transactionTime >= todate).filter(RechargeTransaction.transactionTime < tomorrow).group_by(RechargeTransaction.storeId, RechargeTransaction.payMethod, RechargeTransaction.status).order_by(RechargeTransaction.storeId).all()

    storeData = {}

    for txn in txns:

        print txn

        if not storeData.has_key(txn[0]):

            data = [0,0,0,0,0]

            storeData[txn[0]] = data

        else:

            data = storeData[txn[0]]

        if txn[1] == 1:

            data[0] += int(txn[3]) - int(txn[4])

        if txn[1] == 2:

            data[1] += int(txn[3]) - int(txn[4])

        if txn[2] in (RechargeOrderStatus.RECHARGE_SUCCESSFUL, RechargeOrderStatus.RECHARGE_FAILED_REFUNDED, RechargeOrderStatus.RECHARGE_UNKNOWN):

            data[2] += int(txn[3])

            data[3] += int(txn[4])

            data[4] += int(txn[3]) - int(txn[4])

        storeData[txn[0]] = data

    reftxns = session.query(RechargeTransaction.storeId, RechargeTransaction.payMethod, RechargeTransaction.status, func.sum(RechargeTransaction.amount), func.sum(RechargeTransaction.discount)).filter(RechargeTransaction.status == RechargeOrderStatus.RECHARGE_FAILED_REFUNDED).filter(RechargeTransaction.responseTime >= todate).filter(RechargeTransaction.responseTime < tomorrow).group_by(RechargeTransaction.storeId, RechargeTransaction.payMethod, RechargeTransaction.status).order_by(RechargeTransaction.storeId).all()  

    for txn in reftxns:

        print txn

        if not storeData.has_key(txn[0]):

            data = [0,0,0,0,0]

            storeData[txn[0]] = data

        else:

            data = storeData[txn[0]]

        if txn[1] == 1:

            data[0] -= int(txn[3]) - int(txn[4])

        if txn[1] == 2:

            data[1] -= int(txn[3]) - int(txn[4])

        data[2] -= int(txn[3])

        data[3] -= int(txn[4])

        data[4] -= int(txn[3]) - int(txn[4])

    print storeData

    wallet = WalletForCompany.query.filter(WalletForCompany.id == 1).one()    

    dt = session.query(func.sum(RechargeTransaction.amount)).filter(RechargeTransaction.status.in_([RechargeOrderStatus.RECHARGE_SUCCESSFUL])).one()

#    if int(dt[0]) != wallet.amount:

#        mail("cnc.center@shop2020.in", "5h0p2o2o", ["rajveer.singh@shop2020.in", "anupam.singh@shop2020.in"], "Wallet amount: " + str(wallet.amount) + " does not match with transaction amount: " + str(int(dt[0])) , "", [], [], [])    

    maildata = "<html><body><table border='1'><thead><th>StoreId</th><th>Gross</th><th>Discount</th><th>Net</th></thead><tbody>"

    trecharge = 0

    hotspotServiceMatrices = HotspotServiceMatrix.query.all()

    hotspotServiceMatrixMap = {}

    for hotspotServiceMatrix in hotspotServiceMatrices:

        hotspotServiceMatrixMap[hotspotServiceMatrix.storeId] = hotspotServiceMatrix

    for storeId in storeData.keys():

        store = HotspotStore.get_by(id = storeId)

        if hotspotServiceMatrixMap.has_key(storeId):

            del hotspotServiceMatrixMap[storeId]

        store.collectedAmount = 0

        store.availableLimit = store.creditLimit

        session.commit()

        data = storeData.get(storeId)

        rc = RechargeCollection()

        rc.hotspotId = store.hotspotId

        rc.reconDate = int(todate.strftime("%Y%m%d"))

        rc.cash = data[0]

        rc.hdfc = data[1]

        rc.grossAmount = data[2]

        rc.discount = data[3]

        rc.netCollection = data[4]

        rc.addedAt = datetime.datetime.now()

        rc.pushedToOcr = False

        session.commit()

        maildata += "<tr><td>" + store.hotspotId + "</td><td>" + str(data[2]) + "</td><td>" + str(data[3]) + "</td><td>" + str(data[4]) + "</td></tr>"

        trecharge +=  data[2]

    dit = session.query(func.sum(RechargeCollection.grossAmount)).one()

    dit2 = session.query(func.sum(WalletHistoryForCompany.amount)).filter(WalletHistoryForCompany.walletId == 1).filter(WalletHistoryForCompany.amount >= 100000).one()

    wamt = int(dit2[0])- int(dit[0])

    wallet.amount = wamt

    session.commit()

    maildata += "</tbody></table></body></html>"

    mail_html("cnc.center@shop2020.in", "5h0p2o2o", ["rajveer.singh@shop2020.in", "anupam.singh@shop2020.in", "Ashwani.Kumar@spiceretail.co.in","parveen.mittal@spiceretail.co.in","pardeep.panwar@spiceretail.co.in","gagan.sharma@spiceretail.co.in","j.p.gupta@shop2020.in", "rajneesh.arora@shop2020.in", "amit.tyagi@spiceretail.co.in"], "MIS :- SpiceRetail  (Date - " + todate.strftime("%d-%m-%Y") + ")   (Wallet Amount - " + str(wallet.amount) + ")    (Total Recharge - " + str(trecharge) + ")", maildata, []) 

    try:

        push_recharge_collection_to_ocr()

    except:

        mail_html("cnc.center@shop2020.in", "5h0p2o2o", ["rajveer.singh@shop2020.in", "anupam.singh@shop2020.in", "rajneesh.arora@shop2020.in"], "Problem while pushing recharge collection to OCR", "", [])

    finally:

        msg = "<html><body>"

        for storeId in hotspotServiceMatrixMap.keys():

            if hotspotServiceMatrixMap.get(storeId).rechargeService:

                store = HotspotStore.get_by(id = storeId)

                msg = msg + str(store.hotspotId) + ' - ' + str(store.email) + '<br>'

        msg = msg + '</body></html>'

        helper_client = HelperClient().get_client()

        helper_client.saveUserEmailForSending(["gagan.sharma@spiceretail.co.in"], "cnc.center@shop2020.in", "No Recharge happened for these stores on " + str(date.today()-timedelta(days=1)), msg, "NRM", "NoRechargeMail", ["anupam.singh@shop2020.in"], ["anupam.singh@shop2020.in"], 1)

def push_recharge_collection_to_ocr():

    rcs = RechargeCollection.query.filter(RechargeCollection.pushedToOcr == False).all()

    for rc in rcs:

        store_string = "<Store>" + rc.hotspotId + "</Store>"

        date_string = "<ReconDate>" + str(rc.reconDate) + "</ReconDate>"

        cash_string = "<Cash>" + str(rc.cash) + "</Cash>"

        card_string = "<Hdfc>" + str(rc.hdfc) + "</Hdfc>"

        type_string = "<Type>RechargeSale</Type>"

        amount_string = "<GrossRechargeAmount>" + str(rc.grossAmount) + "</GrossRechargeAmount><Discount>" + str(rc.discount) +  "</Discount><NetCollection>" + str(rc.netCollection) + "</NetCollection>";

        #SaholicRechargeSaleTransfer(string Store, int ReconDate, decimal Cash, decimal Hdfc, string Type, decimal GrossRechargeAmount, decimal Discount, decimal NetCollection)

        conn = httplib.HTTPConnection("182.71.104.186")

        XML="""

        <soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">

          <soap:Body>

            <SaholicRechargeSaleTransfer xmlns="http://tempuri.org/">

        """

        XML = XML + store_string + date_string + cash_string + card_string + "<Cheque>0</Cheque>" + type_string + amount_string 

        footer = """

            </SaholicRechargeSaleTransfer>

          </soap:Body>

        </soap:Envelope>

        """

        XML = XML + footer

        print XML

        params = urllib.urlencode({'op': 'SaholicRechargeSaleTransfer'})

        headers = { "Content-type": "text/xml", "Content-Length": "%d" % len(XML)}

        conn.request("POST", "/loaddetect/Service.asmx?"+params, "", headers)

        conn.send(XML)

        response = conn.getresponse()

        print response.status, response.reason

        resp = response.read()

        conn.close()

        print resp

        if "Saved Successfully" in resp:

            rc.pushedAt = datetime.datetime.now()

            rc.pushedToOcr = True

            session.commit()

        elif "Error in Saving Data" in resp:

            mail_html("cnc.center@shop2020.in", "5h0p2o2o", ["rajveer.singh@shop2020.in", "anupam.singh@shop2020.in", "rajneesh.arora@shop2020.in"], "Problem while pushing recharge collection to OCR", resp, [])

        else: 

            mail_html("cnc.center@shop2020.in", "5h0p2o2o", ["rajveer.singh@shop2020.in", "anupam.singh@shop2020.in", "rajneesh.arora@shop2020.in"], "Problem while pushing recharge collection to OCR", resp, [])

def topup_company_wallet(companyId, amount):

    wallet = WalletForCompany.query.filter(WalletForCompany.id == companyId).with_lockmode("update").one()

    company = Company.get_by(id = companyId)

    wh = WalletHistoryForCompany()

    wh.walletId = wallet.id

    wh.openingBal = wallet.amount

    wh.closingBal = wallet.amount +  amount

    wh.amount = amount

    wh.transactionTime = datetime.datetime.now()

    wh.referenceNumber =  get_next_invoice_number(OrderType.WALLETCREDIT)

    wh.description = "Wallet Credited"

    wallet.amount += amount

    session.commit()

    mail("cnc.center@shop2020.in", "5h0p2o2o", ["rajveer.singh@shop2020.in", "anupam.singh@shop2020.in", "Ashwani.Kumar@spiceretail.co.in","parveen.mittal@spiceretail.co.in","pardeep.panwar@spiceretail.co.in","gagan.sharma@spiceretail.co.in","j.p.gupta@shop2020.in", "rajneesh.arora@shop2020.in", "amit.tyagi@spiceretail.co.in"] , company.name + " wallet topped up by " +  str(amount) + " rupees.", "", [], [], [])

def compute_website_recharge_collection(cdate, oldBalance, newBalance):

    todate = datetime.datetime(cdate.year, cdate.month, cdate.day)

    tomorrow = todate + timedelta(days=1)

    txns = session.query(RechargeOrder.status, func.sum(RechargeOrder.totalAmount)).filter(RechargeOrder.status.in_([RechargeOrderStatus.RECHARGE_SUCCESSFUL, RechargeOrderStatus.RECHARGE_FAILED_REFUNDED, RechargeOrderStatus.PAYMENT_SUCCESSFUL])).filter(RechargeOrder.creationTimestamp >= todate).filter(RechargeOrder.creationTimestamp < tomorrow).group_by(RechargeOrder.status).all()

    tamount = 0

    for txn in txns:

        tamount += int(txn[1])

    reftxns = session.query(RechargeOrder.status, func.sum(RechargeOrder.totalAmount)).filter(RechargeOrder.status == RechargeOrderStatus.RECHARGE_FAILED_REFUNDED).filter(RechargeOrder.responseTimestamp >= todate).filter(RechargeOrder.responseTimestamp < tomorrow).group_by(RechargeOrder.status).all()  

    for txn in reftxns:

        tamount -= int(txn[1])

    wallet = WalletForCompany.query.filter(WalletForCompany.id == 2).with_lockmode("update").one()

    d = datetime.datetime.now()

    wh = WalletHistoryForCompany()

    wh.walletId = wallet.id

    wh.openingBal = wallet.amount

    wh.closingBal = wallet.amount - tamount

    wh.amount = -tamount

    wh.transactionTime = d

    wh.referenceNumber =  int(d.strftime("%Y%m%d"))

    wh.description = "Wallet Credited"

    wallet.amount = wallet.amount - tamount

    session.commit()

    maildata = ""

    mail_html("cnc.center@shop2020.in", "5h0p2o2o", ["pardeep.panwar@spiceretail.co.in>", "amit.tyagi@spiceretail.co.in", "rajveer.singh@shop2020.in", "anupam.singh@shop2020.in", "j.p.gupta@shop2020.in", "rajneesh.arora@shop2020.in"], "MIS :- Saholic (Date - " + todate.strftime("%d-%m-%Y") + ")   (Wallet Amount - " + str(wallet.amount) + ")    (Total Recharge - " + str(tamount) + ")", maildata, []) 

    rAmount = 0

    ramount = session.query(func.sum(WalletHistoryForCompany.amount)).filter(WalletHistoryForCompany.transactionTime >= todate).filter(WalletHistoryForCompany.amount >= 100000).one()

    if ramount[0]:

        rAmount = int(ramount[0])

    rcs = session.query(func.sum(RechargeCollection.grossAmount)).filter(RechargeCollection.reconDate == int(todate.strftime("%Y%m%d"))).one()

    hamount = int(rcs[0])

    maildata = "(A) Old Wallet Amount is : " + str(oldBalance) + "<br>(B) New Wallet Amount is : " + str(newBalance) + "<br>(C) Recharge Amount is : " + str(rAmount) + "<br>---------------------------<br>(D) Total Debit Amount for Recharge(A-B+C) is : " + str(oldBalance - newBalance + rAmount) + "<br><br><br>(E) Saholic Recharge Amount is :" + str(tamount) + "<br>(F) Hotspot Recharge Amount is :" + str(hamount) + "<br>---------------------------<br>(G) Total Recharge Amount (E+F) is : " + str(tamount + hamount)

    mail_html("cnc.center@shop2020.in", "5h0p2o2o", ["pardeep.panwar@spiceretail.co.in>", "amit.tyagi@spiceretail.co.in", "rajveer.singh@shop2020.in", "anupam.singh@shop2020.in", "j.p.gupta@shop2020.in", "rajneesh.arora@shop2020.in"], "MIS :- Wallet and Recharge (Date - " + todate.strftime("%d-%m-%Y") + ")", maildata, [])

if __name__ == '__main__':

    main()