Subversion Repositories SmartDukaan

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

    RechargeVoucherTracker

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

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

                      action="store_true",

                      help="")

    if options.recon:

        processRecon()

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.

    '''

    A = 0

    B = 0

    C = 0

    X1 = 0

    X2 = 0

    X3 = 0

    X4 = 0

    X5 = 0

    D = 0

    sorder = session.query(func.sum(RechargeOrder.totalAmount - RechargeOrder.couponAmount), 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]:

        A = int(sorder[0])

    forder = session.query(func.sum(RechargeOrder.totalAmount - RechargeOrder.couponAmount), 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]:

        B = int(forder[0])

    rorder = session.query(func.sum(RechargeOrder.totalAmount - RechargeOrder.couponAmount), 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]:

        C = int(rorder[0])

    X1 = X1 + A + B - C

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

    if rv and rv[0]:

        X2 = int(rv[0])

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

    if uw and uw[0]:

        X3 = int(uw[0])

    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:  

            X4 = X4 + 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:

                X5 = X5 + payment.refundAmount

    D = X1+X3+X5-X2-X4

    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(A) + "</td></tr>"

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

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

    maildata += "<tr><td>X1=A+B-C</td><td>Net Recharge Amount</td><td>" + str(X1) + "</td></tr>"

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

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

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

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

    maildata += "<tr><td>D=X1+X3+X5-X2-X4</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, [])