Subversion Repositories SmartDukaan

from dtr.main import Store

from dtr.storage.Mysql import getOrdersAfterDate, getOrdersByTag

def getUserOrders():

    global i

    rb = open_workbook(XLS_FILENAME,formatting_info=True)

    wb = copy(rb)

    worksheet = wb.add_sheet("All Users")

    worksheet1 = wb.add_sheet("Suspected Users")

    db=client.Dtr

    results = Mysql.fetchResult('''

        select ow.*, u.username, crm1.order_count, u.referrer from order_view ow left join users u on u.id = ow.user_id left join (select user_id, count(*) as order_count from order_view where status = 'ORDER_CREATED'  group by user_id) as crm1 on ow.user_id = crm1.user_id where lower(u.referrer) not like 'emp%' 

        ''',)

    orders = []

    orders.append("")

    orderH = []

    row = 0

    i=-1

    worksheet.write(row, inc(), 'User Id', boldStyle)

    worksheet1.write(row, i, 'User Id', boldStyle)

    worksheet.write(row, inc(), 'User name', boldStyle)

    worksheet1.write(row, i, 'User name', boldStyle)

    worksheet.write(row, inc(), 'Order Id', boldStyle)

    worksheet1.write(row, i, 'Order Id', boldStyle)

    worksheet.write(row, inc(), 'Created On', boldStyle)

    worksheet1.write(row, i, 'Created On', boldStyle)

    worksheet.write(row, inc(), 'Store Id', boldStyle)

    worksheet1.write(row, i, 'Store Id', boldStyle)

    worksheet.write(row, inc(), 'Merchant Order Id', boldStyle)

    worksheet1.write(row, i, 'Merchant Order Id', boldStyle)

    worksheet.write(row, inc(), 'Amount Paid', boldStyle)

    worksheet1.write(row, i, 'Amount Paid', boldStyle)

    worksheet.write(row, inc(), 'Cashback', boldStyle)

    worksheet1.write(row, i, 'Cashback', boldStyle)

    worksheet.write(row, inc(), 'Order Status', boldStyle)

    worksheet1.write(row, i, 'Order Status', boldStyle)

    worksheet.write(row, inc(), 'Detailed Status', boldStyle)

    worksheet1.write(row, i, 'Detailed Status', boldStyle)

    worksheet.write(row, inc(), 'Cashback Status', boldStyle)

    worksheet1.write(row, i, 'Cashback Status', boldStyle)

    worksheet.write(row, inc(), 'Reconciled', boldStyle)

    worksheet1.write(row, i, 'Reconciled', boldStyle)

    worksheet.write(row, inc(), 'IP', boldStyle)

    worksheet1.write(row, i, 'IP', boldStyle)

    row=0

    row2=0

    for result in results:

        morder = db.merchantOrder.find_one({"orderId":result[0]})

        if morder is not None:

            subOrders = morder.get("subOrders")

            if subOrders is not None:

                for  subOrder in subOrders:

                    if subOrder.get("cashBackStatus") == Store.CB_APPROVED or subOrder.get("cashBackStatus") == Store.CB_PENDING:

                        row +=1

                        i=-1

                        worksheet.write(row, inc(), result[0])

                        worksheet.write(row, inc(), result[-3])

                        worksheet.write(row, inc(), result[1])

                        worksheet.write(row, inc(), result[-4])

                        worksheet.write(row, inc(), result[2])

                        worksheet.write(row, inc(), morder.get("merchantOrderId"))

                        #order.append(subOrder.get("merchantSubOrderId"))

                        worksheet.write(row, inc(), subOrder.get("amountPaid"))

                        worksheet.write(row, inc(), subOrder.get("cashBackAmount"))

                        worksheet.write(row, inc(), subOrder.get("status"))

                        worksheet.write(row, inc(), subOrder.get("detailedStatus"))

                        worksheet.write(row, inc(), subOrder.get("cashBackStatus"))

                        worksheet.write(row, inc(), False if morder.get("reconciled") is None else True)

                        if morder.get("reconciled") and morder.get("storeId")==3:

                            try:

                                worksheet.write(row, inc(), db.snapdealOrderAffiliateInfo.find_one({"adId":morder.get("adId"), "reconciled":True}).get("ip"))

                            except:

                                print "Could not find", morder.get("adId"), "that is reconcired for order", morder.get("orderId")

                        if result[-2] >= 10 and result[-1].lower().startswith("crm"):

                            row2 +=1 

                            i=-1

                            worksheet1.write(row2, inc(), result[0])

                            worksheet1.write(row2, inc(), result[-3])

                            worksheet1.write(row2, inc(), result[1])

                            worksheet1.write(row2, inc(), result[-4])

                            worksheet1.write(row2, inc(), result[2])

                            worksheet1.write(row2, inc(), morder.get("merchantOrderId"))

                            worksheet1.write(row2, inc(), subOrder.get("amountPaid"))

                            worksheet1.write(row2, inc(), subOrder.get("cashBackAmount"))

                            worksheet1.write(row2, inc(), subOrder.get("status"))

                            worksheet1.write(row2, inc(), subOrder.get("detailedStatus"))

                            worksheet1.write(row2, inc(), subOrder.get("cashBackStatus"))

                            worksheet1.write(row2, inc(), False if morder.get("reconciled") is None else True)

                            if morder.get("reconciled") and morder.get("storeId")==3:

                                try:

                                    worksheet1.write(row2, inc(), db.snapdealOrderAffiliateInfo.find_one({"adId":morder.get("adId"), "reconciled":True}).get("ip"))

                                except:

                                    print "Could not find", morder.get("adId"), "that is reconcired for order", morder.get("orderId")

    wb.save(XLS_FILENAME)