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

import base64, re

from datetime import timedelta

from elixir import *

from sqlalchemy.sql import func

import urllib

import httplib

from xml.dom.minidom import parseString

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

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

from shop2020.clients.TransactionClient import TransactionClient

import sys

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, SpiceTransactionHistory, DtrBatchCreditTracker

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

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

    getRechargeClient, checkTransactionStatus, getAllTransactions, getRechargeStatus

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("-m", "--mobisoc", dest="mobisoc",

                      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

        try:

            newBalance = getBalance()

        except:

            mail_html("cnc.center@shop2020.in", "5h0p2o2o", ["amit.gupta@saholic.com", "kshitij.sood@saholic.com", "manoj.bajaj@spiceretail.co.in","amit.tyagi@spiceretail.co.in"], "Unable to fetch balance from spice digital api.Collection not created.", "", [])

            sys.exit(1)

        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)

    if options.mobisoc:

        reconcileWithMobisoc()

def reconcileWithMobisoc():

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

    endTime = datetime.datetime.now()

    startTime = startTime.replace(hour=00,minute=00,second=00)

    endTime = endTime.replace(hour=00,minute=00,second=00)

    websiteOrders = {}

    storeOrders = {}

    spiceOrders = {}

    rechargeOrders = RechargeOrder.query.filter(not_(RechargeOrder.status.in_([RechargeOrderStatus.INIT, RechargeOrderStatus.PAYMENT_FAILED, RechargeOrderStatus.PAYMENT_PENDING]))).filter(RechargeOrder.creationTimestamp.between(startTime, endTime)).all()

    rechargeTransactions = RechargeTransaction.query.filter(not_(RechargeTransaction.status.in_([RechargeOrderStatus.INIT, RechargeOrderStatus.PAYMENT_FAILED, RechargeOrderStatus.PAYMENT_PENDING]))).filter(RechargeTransaction.transactionTime.between(startTime, endTime)).all()

    for rechargeOrder in rechargeOrders:

        websiteOrders[int(rechargeOrder.id)] = rechargeOrder

    for rechargeTxn in rechargeTransactions:

        storeOrders[int(rechargeTxn.id)] = rechargeTxn

    transactions = getAllTransactions(startTime, endTime)

    for txn in transactions:

        try:

            print "in for loop"

            respCode = txn.getElementsByTagName('transactionResponseCode')[0].childNodes[0].data

            merchantTid = txn.getElementsByTagName('merchantTid')[0].childNodes[0].data

            spiceTid = txn.getElementsByTagName('spiceTid')[0].childNodes[0].data

            rechargeTime = txn.getElementsByTagName('rechargeDate')[0].childNodes[0].data

            sth = SpiceTransactionHistory()

            sth.rechargeTime = rechargeTime

            sth.orderId = merchantTid

            sth.spiceTID = spiceTid

            sth.reponseCode = respCode

            session.commit()

            spiceOrders[int(merchantTid)] = respCode

        except:

            continue

    print "Length of storeOrders : " + str(len(storeOrders.keys()))

    print "Length of websiteOrders : " + str(len(websiteOrders.keys()))

    print "Length of spiceOrders : " + str(len(spiceOrders.keys()))

    for id in spiceOrders.keys():

        spiceorder = spiceOrders.get(id)

        order = storeOrders.get(id)

        if order is None:

            order = websiteOrders.get(id)

        if order:

            if spiceorder == '00' and order.status in (5, 6):

                removed = storeOrders.pop(id, -1)

                print str(removed)

                if removed == -1:

                    removed = websiteOrders.pop(id, -1)

                if removed != -1:

                    removed = spiceOrders.pop(id, -1)

            elif spiceorder in ('STO','US', 'UP') and order.status in (3, 4, 5, 10, 6, 7, 8):

                removed = storeOrders.pop(id, -1)

                if removed == -1:

                    removed = websiteOrders.pop(id, -1)

                if removed != -1:

                    removed = spiceOrders.pop(id, -1)

            elif spiceorder not in ('STO','US', 'UP', '00') and order.status in (4, 6, 7, 8) :

                removed = storeOrders.pop(id, -1)

                if removed == -1:

                    removed = websiteOrders.pop(id, -1)

                if removed != -1:

                    removed = spiceOrders.pop(id, -1)

    print "Length of storeOrders : " + str(len(storeOrders.keys()))

    print "Length of websiteOrders : " + str(len(websiteOrders.keys()))

    print "Length of spiceOrders : " + str(len(spiceOrders.keys()))

    if len(websiteOrders.keys()) > 0:

        for woid in websiteOrders.keys():

            try:

                result = checkTransactionStatus("", str(woid))

            except Exception:

                continue

            if result[0]:

                removed = websiteOrders.pop(woid, -1)

    if len(storeOrders.keys()) > 0:

        for soid in storeOrders.keys():

            try:

                result = checkTransactionStatus("", str(soid))

            except Exception:

                continue

            if result[0]:

                removed = storeOrders.pop(soid, -1)

    if len(spiceOrders.keys()) > 0:

        for id in spiceOrders.keys():

            order = RechargeOrder.query.filter(RechargeOrder.id == id).first()

            if order is None:

                order = RechargeTransaction.query.filter(RechargeTransaction.id == id).first()

            if order:

                spiceorder = spiceOrders.get(id)

                if spiceorder == '00' and order.status in (5, 6):

                    removed = spiceOrders.pop(id, -1)

                elif spiceorder in ('STO','US', 'UP') and order.status in (3, 4, 10, 5, 6, 7, 8):

                    removed = spiceOrders.pop(id, -1)

                elif spiceorder not in ('STO','US', 'UP', '00') and order.status in (4, 6, 7, 8) :

                    removed = spiceOrders.pop(id, -1)

    maildata = "<html><body><b>Website Recharges</b><br><br>"

    for woid in websiteOrders.keys():

        wo = websiteOrders.get(woid)

        try:

            recharge_status, desc = getRechargeStatus('',wo.id)

        except:

            recharge_status, desc= "",""

        maildata = maildata + str(wo.id) + " : " +  str(RechargeOrderStatus._VALUES_TO_NAMES.get(wo.status)) + " : "+recharge_status+" "+desc+"<br>"

    maildata = maildata + "<br><br><b>Store recharges</b><br><br>"

    for soid in storeOrders.keys():

        so = storeOrders.get(soid)

        try:

            recharge_status, desc = getRechargeStatus('',so.id)

        except:

            recharge_status,desc = "",""

        maildata = maildata +  str(so.id) + " : " + str(RechargeOrderStatus._VALUES_TO_NAMES.get(wo.status)) + " : "+recharge_status+" "+desc+"<br>"

    maildata = maildata + "<br><br><b>Spice Orders</b><br><br>"

    for id in spiceOrders.keys():

        maildata = maildata + str(id) + " : " + str(spiceOrders.get(id)) + "<br>"

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

    mail_html("cnc.center@shop2020.in", "5h0p2o2o", ["anikendra.das@saholic.com", "kshitij.sood@shop2020.in"], "Mobisoc Recharge Reconciliation for Date:- " + startTime.strftime("%d-%m-%Y"), maildata, [])

def processRecon():

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

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

    endTime = startTime + timedelta(days=1)

    '''

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

    rv1 = session.query(func.sum(DtrBatchCreditTracker.amount)).filter(DtrBatchCreditTracker.creditedOn.between(startTime,endTime)).first()

    if rv1 and rv1[0]:

        X3 += int(rv1[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)), 2, 19) + 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) + pc.getPayments(to_java_date(startTime - datetime.timedelta(minutes = 30)), to_java_date(endTime + datetime.timedelta(minutes = 30)), 8, 19)

    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 = []

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

        mismatches += RechargeOrder.query.filter(RechargeOrder.status.in_([RechargeOrderStatus.RECHARGE_SUCCESSFUL, RechargeOrderStatus.RECHARGE_FAILED, RechargeOrderStatus.RECHARGE_UNKNOWN, RechargeOrderStatus.RECHARGE_FAILED_REFUNDED, RechargeOrderStatus.PAYMENT_SUCCESSFUL])).filter(RechargeOrder.responseTimestamp.between(startTime, endTime)).filter(RechargeOrder.creationTimestamp.between(startTime - timedelta(minutes = 60), startTime)).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", ["anikendra.das@saholic.com", "kshitij.sood@shop2020.in", "rajneesh.arora@shop2020.in", "amit.gupta@saholic.com"], "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, RechargeOrderStatus.RECHARGE_IN_PROCESS):

                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"

    inprocessOrders = RechargeTransaction.query.filter(RechargeTransaction.status == RechargeOrderStatus.RECHARGE_IN_PROCESS).all()

    for inprocessOrder in inprocessOrders:

        inprocessOrder.status = RechargeOrderStatus.RECHARGE_UNKNOWN

    session.commit()

    ## 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_UNKNOWN, RechargeOrderStatus.RECHARGE_IN_PROCESS])).filter(RechargeTransaction.transactionTime.between(todate, tomorrow)).group_by(RechargeTransaction.storeId, RechargeTransaction.payMethod, RechargeTransaction.status).order_by(RechargeTransaction.storeId).all()

    txns = txns + session.query(RechargeTransaction.storeId, RechargeTransaction.payMethod, RechargeTransaction.status, func.sum(RechargeTransaction.amount), func.sum(RechargeTransaction.discount)).filter(RechargeTransaction.status.in_([RechargeOrderStatus.RECHARGE_FAILED, RechargeOrderStatus.RECHARGE_FAILED_REFUNDED])).filter(RechargeTransaction.transactionTime.between(todate, tomorrow)).filter(not_(RechargeTransaction.responseTime.between(todate, 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])

        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.in_([RechargeOrderStatus.RECHARGE_FAILED, RechargeOrderStatus.RECHARGE_FAILED_REFUNDED])).filter(RechargeTransaction.responseTime.between(todate, tomorrow)).filter(not_(RechargeTransaction.transactionTime.between(todate, 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", ["anikendra.das@saholic.com", "kshitij.sood@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", ["amit.gupta@saholic.com", "kshitij.sood@saholic.com", "manoj.bajaj@spiceretail.co.in","amit.tyagi@spiceretail.co.in","pradeep.panwar@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", ["amit.gupta@saholic.com", "kshitij.sood@saholic.com", "manoj.bajaj@spiceretail.co.in","amit.tyagi@spiceretail.co.in","pradeep.panwar@spiceretail.co.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(["manoj.bajaj@spiceretail.co.in"], "cnc.center@shop2020.in", "No Recharge happened for these stores on " + str(date.today()-timedelta(days=1)), msg, "NRM", "NoRechargeMail", ["kshitij.sood@shop2020.in"], ["kshitij.sood@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("14.141.109.13")

        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", ["amit.gupta@shop2020.in", "kshitij.sood@shop2020.in", "rajneesh.arora@shop2020.in"], "Problem while pushing recharge collection to OCR", resp, [])

        else: 

            mail_html("cnc.center@shop2020.in", "5h0p2o2o", ["amit.gupta@shop2020.in", "kshitij.sood@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", ["amit.gupta@shop2020.in", "amit.tyagi@spiceretail.co.in"] , company.name + " wallet topped up by " +  str(amount) + " rupees.", "", [], [], [])

def compute_website_recharge_collection(cdate, oldBalance, newBalance):

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

    endTime = startTime + timedelta(days=1)

    tamount = 0

    txns = 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 txns and txns[0]:

        tamount += int(txns[0])

    otxns = 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 otxns and otxns[0]:

        tamount += int(otxns[0])

    reftxns = 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 reftxns and reftxns[0]:

        tamount -= int(reftxns[0])

    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", ["amit.gupta@saholic.com", "kshitij.sood@saholic.com", "manoj.bajaj@spiceretail.co.in","amit.tyagi@spiceretail.co.in","pradeep.panwar@spiceretail.co.in"], "MIS :- Saholic (Date - " + startTime.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 >= startTime).filter(WalletHistoryForCompany.amount >= 100000).one()

    if ramount[0]:

        rAmount = int(ramount[0])

    rcs = session.query(func.sum(RechargeCollection.grossAmount)).filter(RechargeCollection.reconDate == int(startTime.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", ["amit.gupta@saholic.com", "kshitij.sood@saholic.com", "manoj.bajaj@spiceretail.co.in","amit.tyagi@spiceretail.co.in","pradeep.panwar@spiceretail.co.in"], "MIS :- Wallet and Recharge (Date - " + startTime.strftime("%d-%m-%Y") + ")", maildata, [])

if __name__ == '__main__':

    main()