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