Subversion Repositories SmartDukaan

'''

Created on Mar 13, 2015

@author: amit

'''

from datetime import date, datetime, timedelta

from dtr.main import Store, sourceMap

from dtr.reports.amazonreco import generateAmazonReco

from dtr.storage import Mysql, DataService

from dtr.storage.DataService import Users, Clicks, FlipkartOrders,\

    All_user_addresses

from dtr.storage.Mysql import getOrdersAfterDate, getOrdersByTag

from dtr.utils.utils import fromTimeStamp

from elixir import *

from sqlalchemy.sql.expression import func, func, or_

from email import encoders

from email.mime.base import MIMEBase

from email.mime.multipart import MIMEMultipart

from email.mime.text import MIMEText

from pymongo.mongo_client import MongoClient

from xlrd import open_workbook

from xlutils.copy import copy

from xlwt.Workbook import Workbook

import MySQLdb

import smtplib

import time

import xlwt

from dtr.utils import utils

#from xlwt import 

DataService.initialize()

client = MongoClient('mongodb://localhost:27017/')

SENDER = "cnc.center@shop2020.in"

PASSWORD = "5h0p2o2o"

SUBJECT = "DTR User Segmentation report for " + date.today().isoformat()

SMTP_SERVER = "smtp.gmail.com"

SMTP_PORT = 587    

XLS_FILENAME = "snapdealaffiliatereco.xls"

XLS_O_FILENAME = "allorders.xls"

XLS_F_FILENAME = "flipkartaffiliatereco.xls"

XLS_A_FILENAME = "amazonaffiliatereco.xls"

XLS_SC_FILENAME = "shopcluesaffiliatereco.xls"

boldStyle = xlwt.XFStyle()

f = xlwt.Font()

f.bold = True

boldStyle.font = f

i = -1

datetime_format = xlwt.XFStyle()

datetime_format.num_format_str = 'dd/mm/yyyy HH:MM AM/PM'

categoryMap = {3:"Mobiles", 5:"Tablets"}

def generateFlipkartReco():

    global i

    i = -1

    curs = getOrdersAfterDate(datetime.now() - timedelta(days=30), 2)

    db = client.Dtr

    wb = xlwt.Workbook()

    #rb = open_workbook(XLS_F_FILENAME)

    #wb = copy(rb)

    #wb = xlwt.Workbook()

    row = 0

    row2 = 0

    row3 = 0

    row4 = 0

    worksheet = wb.add_sheet("All Orders")

    worksheet1 = wb.add_sheet("Orders Reconciled")

    worksheet2 = wb.add_sheet("Orders not Reconciled")

    worksheet3 = wb.add_sheet("Aff not Reconciled")

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

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

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

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

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

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

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

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

    worksheet2.write(row, i, 'Username', boldStyle)

    worksheet.write(row, inc(), 'Version code', boldStyle)

    worksheet1.write(row, i, 'Version code', boldStyle)

    worksheet2.write(row, i, 'Version code', boldStyle)

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

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

    worksheet2.write(row, i, 'Device', boldStyle)

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

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

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

    worksheet.write(row, inc(), 'Product Title', boldStyle)

    worksheet1.write(row, i, 'Product Title', boldStyle)

    worksheet2.write(row, i, 'Product Title', boldStyle)

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

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

    worksheet2.write(row, i, 'Price', boldStyle)

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

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

    worksheet2.write(row, i, 'Quantity', boldStyle)

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

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

    worksheet2.write(row, i, 'Status', boldStyle)

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

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

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

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

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

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

    worksheet.write(row, inc(), 'Our CashBack', boldStyle)

    worksheet1.write(row, i, 'Our CashBack', boldStyle)

    worksheet2.write(row, i, 'Our CashBack', boldStyle)

    worksheet.write(row, inc(), 'Sale Date', boldStyle) 

    worksheet1.write(row, i, 'Sale Date', boldStyle)

    worksheet2.write(row, i, 'Sale Date', boldStyle)

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

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

    worksheet2.write(row, i, 'SubtagId', boldStyle)

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

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

    worksheet.write(row, inc(), 'Aff PayOut', boldStyle)

    worksheet1.write(row, i, 'Aff PayOut', boldStyle)

    worksheet.write(row, inc(), 'Aff Sale Amount', boldStyle)

    worksheet1.write(row, i, 'Aff Sale Amount', boldStyle)

    worksheet.write(row, inc(), 'Aff Sale Date', boldStyle)

    worksheet1.write(row, i, 'Aff Sale Date', boldStyle)

    for row1 in curs:

        orderId = row1[0]

        order = db.merchantOrder.find_one({"orderId":orderId})

        if order is None:

            continue

        saleTime = int(time.mktime(datetime.strptime(order['placedOn'], "%d %B, %Y %I:%M %p").replace(hour=0, minute=0, second=0, microsecond=0).timetuple()))

        #saleTime1 = int(time.mktime((datetime.strptime(order['placedOn'], "%b %d, %Y %I:%M %p") + timedelta(seconds=-60)).timetuple()))

        reconciled_affiliates = []

        for subOrder in order['subOrders']: 

            affs = list(db.flipkartOrderAffiliateInfo.find({"productCode":subOrder.get("productCode"), "saleDateInt":saleTime}))

            row += 1

            i=-1

            worksheet.write(row, inc(), orderId)

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

            worksheet.write(row, inc(), row1[-1])

            worksheet.write(row, inc(), row1[-2])

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

            worksheet.write(row, inc(), order['merchantOrderId'])

            worksheet.write(row, inc(), subOrder['productTitle'])

            worksheet.write(row, inc(), subOrder['amountPaid'])

            worksheet.write(row, inc(), subOrder['quantity'])

            worksheet.write(row, inc(), subOrder['status'])

            worksheet.write(row, inc(), subOrder['detailedStatus'])

            worksheet.write(row, inc(), subOrder['cashBackStatus'])

            worksheet.write(row, inc(), subOrder['cashBackAmount'])

            worksheet.write(row, inc(), subOrder['placedOn'])

            worksheet.write(row, inc(), order['subTagId'])

            for aff in affs:

                if aff["_id"] not in reconciled_affiliates and aff['subTagId']== order['subTagId']:

                    reconciled_affiliates.append(aff["_id"])

                    row2 += 1

                    i = -1 

                    worksheet1.write(row2, inc(), orderId)

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

                    worksheet1.write(row2, inc(), row1[-1])

                    worksheet1.write(row2, inc(), row1[-2])

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

                    worksheet1.write(row2, inc(), order['merchantOrderId'])

                    worksheet1.write(row2, inc(), subOrder['productTitle'])

                    worksheet1.write(row2, inc(), subOrder['amountPaid'])

                    worksheet1.write(row2, inc(), subOrder['quantity'])

                    worksheet1.write(row2, inc(), subOrder['status'])

                    worksheet1.write(row2, inc(), subOrder['detailedStatus'])

                    worksheet1.write(row2, inc(), subOrder['cashBackStatus'])

                    worksheet1.write(row2, inc(), subOrder['cashBackAmount'])

                    worksheet1.write(row2, inc(), subOrder['placedOn'])

                    worksheet1.write(row2, inc(), order['subTagId'])

                    db.flipkartOrderAffiliateInfo.update(

                        {"productCode":subOrder.get("productCode"), "saleDateInt":saleTime, "subTagId":aff['subTagId']}, 

                        {"$set":{"reconciled":True}})

                    db.merchantOrder.update({"merchantOrderId":order["merchantOrderId"]}, 

                                            {"$set":{"reconciled":True}})

                    worksheet.write(row, inc(), aff['category'])

                    worksheet1.write(row2, i, aff['category'])

                    worksheet.write(row, inc(), aff['payOut'])

                    worksheet1.write(row2, i, aff['payOut'])

                    worksheet.write(row, inc(), aff['saleAmount'])

                    worksheet1.write(row2, i, aff['saleAmount'])

                    worksheet.write(row, inc(), aff['saleDate'])

                    worksheet1.write(row2, i, aff['saleDate'])

                    break

            row3 += 1

            i=-1

            worksheet2.write(row3, inc(), orderId)

            worksheet2.write(row3, inc(), row1[1])

            worksheet2.write(row3, inc(), row1[-1])

            worksheet2.write(row3, inc(), row1[-2])

            worksheet2.write(row3, inc(), row1[-3])

            worksheet2.write(row3, inc(), order['merchantOrderId'])

            worksheet2.write(row3, inc(), subOrder['productTitle'])

            worksheet2.write(row3, inc(), subOrder['amountPaid'])

            worksheet2.write(row3, inc(), subOrder['quantity'])

            worksheet2.write(row3, inc(), subOrder['status'])

            worksheet2.write(row3, inc(), subOrder['detailedStatus'])

            worksheet2.write(row3, inc(), subOrder['cashBackStatus'])

            worksheet2.write(row3, inc(), subOrder['cashBackAmount'])

            worksheet2.write(row3, inc(), subOrder['placedOn'])

            worksheet2.write(row3, inc(), order['subTagId'])

    i=-1

    worksheet3.write(0, inc(), "category",boldStyle)    

    worksheet3.write(0, inc(), "conversionStatus",boldStyle)    

    worksheet3.write(0, inc(), "Product Title",boldStyle)    

    worksheet3.write(0, inc(), "price",boldStyle)    

    worksheet3.write(0, inc(), "quantity",boldStyle)    

    worksheet3.write(0, inc(), "payOut",boldStyle)    

    worksheet3.write(0, inc(), "saleDate",boldStyle)    

    worksheet3.write(0, inc(), "subTagId",boldStyle)

    for aff in db.flipkartOrderAffiliateInfo.find({"reconciled":{"$exists":False}}):

        row4 += 1

        i =-1

        worksheet3.write(row4, inc(), aff["category"])    

        worksheet3.write(row4, inc(), aff["conversionStatus"])    

        skuData = getSkuData(2, aff["productCode"])

        if skuData is None:    

            worksheet3.write(row4, inc(), aff["productTitle"])

        else:

            worksheet3.write(row4, inc(), skuData["product_name"])

        worksheet3.write(row4, inc(), aff['price'])    

        worksheet3.write(row4, inc(), aff['quantity'])    

        worksheet3.write(row4, inc(), aff['payOut'])    

        worksheet3.write(row4, inc(), aff['saleDate'])    

        worksheet3.write(row4, inc(), aff['subTagId'])    

    wb.save(XLS_F_FILENAME)

def generateSnapDealReco():

    global i

    curs = getOrdersAfterDate(datetime.now() - timedelta(days=30), 3)

    db = client.Dtr

    notReconciled = {}

    matchedList = []

    workbook = xlwt.Workbook()

    worksheet = workbook.add_sheet("Snapdeal Reconciled")

    worksheet1 = workbook.add_sheet("Snapdeal Reconciled All")

    worksheet2 = workbook.add_sheet("Snapdeal Orders not reconciled")

    affNotReconciledSheet = workbook.add_sheet("Snapdeal Aff not reconciled")

    setHeaders(worksheet, worksheet1, worksheet2, affNotReconciledSheet)

    row = 0

    anotherrow = 0

    row2 = 0

    row5=0

    for row1 in curs:

        orderId = row1[0]

        order = db.merchantOrder.find_one({"orderId":orderId})

        if order is None:

            continue

        #saleTime = int(time.mktime(datetime.strptime(order['placedOn'], "%b %d, %Y, %I:%M %p").timetuple()))

        aff = list(db.snapdealOrderAffiliateInfo.find({"subTagId":order["subTagId"]}))

        if len(aff) > 1:

            aff = [f for f in aff if f.get("payOut")>0]

        anotherrow += 1

        try:

            formattedTimestamp = order['placedOn']

            timestamp1 = int(time.mktime(datetime.strptime(order['placedOn'], "%b %d, %Y, %I:%M %p").timetuple()))

        except:

            timestamp1 = int(time.mktime(row1[-4].timetuple()))

            formattedTimestamp = datetime.strftime(fromTimeStamp(timestamp1), "%b %d, %Y, %I:%M %p")

        if len(aff) > 0 and order["subTagId"] not in matchedList and int(order["paidAmount"])==int(aff[0]["saleAmount"]):

            matchedList.append(order["subTagId"])

            db.snapdealOrderAffiliateInfo.update({"adId":aff[0].get("adId")}, {"$set":{"reconciled":True, "orderId":orderId}}, multi=True)

            order['reconciled'] = True

            order['adId'] = aff[0].get("adId")

            db.merchantOrder.save(order)

            row += 1

            i=-1 

            worksheet.write(row, inc(), orderId)

            worksheet1.write(anotherrow, i, orderId)

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

            worksheet1.write(anotherrow, i, row1[1])

            worksheet.write(row, inc(), row1[-1])

            worksheet1.write(anotherrow, i, row1[-1])

            worksheet.write(row, inc(), row1[-2])

            worksheet1.write(anotherrow, i, row1[-2])

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

            worksheet1.write(anotherrow, i, row1[-3])

            worksheet.write(row, inc(), order['merchantOrderId'])

            worksheet1.write(anotherrow, i, order['merchantOrderId'])

            worksheet.write(row, inc(), order['subTagId'])

            worksheet1.write(anotherrow, i, order['subTagId'])

            worksheet.write(row, inc(), order['placedOn'])

            worksheet1.write(anotherrow, i, order['placedOn'])

            worksheet.write(row, inc(), formattedTimestamp)

            worksheet1.write(anotherrow, i, formattedTimestamp)

            worksheet.write(row, inc(), int(order['paidAmount']))

            worksheet1.write(anotherrow, i, int(order['paidAmount']))

            worksheet.write(row, inc(), aff[0]['saleDate'])

            worksheet1.write(anotherrow, i, aff[0]['saleDate'])

            worksheet.write(row, inc(), aff[0]['saleAmount'])

            worksheet1.write(anotherrow, i, aff[0]['saleAmount'])

            worksheet.write(row, inc(), aff[0]['payOut'])

            worksheet1.write(anotherrow, i, aff[0]['payOut'])

            worksheet.write(row, inc(), aff[0]['ip'])

            worksheet1.write(anotherrow, i, aff[0]['ip'])

            worksheet.write(row, inc(), aff[0]['conversionStatus'])

            worksheet1.write(anotherrow, i, aff[0]['conversionStatus'])

            k = i

            row -= 1

            anotherrow -= 1

            for subOrder in order['subOrders']:

                i = k

                row += 1

                anotherrow += 1

                worksheet.write(row, inc(), subOrder['productTitle'])

                worksheet1.write(anotherrow, i, subOrder['productTitle'])

                worksheet.write(row, inc(), subOrder['amountPaid'])

                worksheet1.write(anotherrow, i, subOrder['amountPaid'])

                worksheet.write(row, inc(), subOrder['quantity'])

                worksheet1.write(anotherrow, i, subOrder['quantity'])

                worksheet.write(row, inc(), subOrder['status'])

                worksheet1.write(anotherrow, i, subOrder['status'])

                worksheet.write(row, inc(), subOrder['cashBackStatus'])

                worksheet1.write(anotherrow, i, subOrder['cashBackStatus'])

                worksheet.write(row, inc(), subOrder['cashBackAmount'])

                worksheet1.write(anotherrow, i, subOrder['cashBackAmount'])

        else:

            i=-1

            worksheet1.write(anotherrow, inc(), orderId)

            worksheet1.write(anotherrow, inc(), row1[1])

            worksheet1.write(anotherrow, inc(), row1[-1])

            worksheet1.write(anotherrow, inc(), row1[-2])

            worksheet1.write(anotherrow, inc(), row1[-3])

            worksheet1.write(anotherrow, inc(), order['merchantOrderId'])

            worksheet1.write(anotherrow, inc(), order['subTagId'])

            worksheet1.write(anotherrow, inc(), order['placedOn'])

            worksheet1.write(anotherrow, inc(), formattedTimestamp)

            worksheet1.write(anotherrow, inc(), int(order['paidAmount']))

            notReconciled[orderId] = (anotherrow, i, int(order['paidAmount']), timestamp1, order["subTagId"]) 

            inc()

            inc()

            inc()

            inc()

            inc()

            k = i

            anotherrow -= 1

            for subOrder in order['subOrders']:

                anotherrow += 1

                i = k

                worksheet1.write(anotherrow, inc(), subOrder['productTitle'])

                worksheet1.write(anotherrow, inc(), subOrder['amountPaid'])

                worksheet1.write(anotherrow, inc(), subOrder['quantity'])

                worksheet1.write(anotherrow, inc(), subOrder['status'])

                worksheet1.write(anotherrow, inc(), subOrder['cashBackStatus'])

                worksheet1.write(anotherrow, inc(), subOrder['cashBackAmount'])

            row5 += 1

            i=-1

            worksheet2.write(row5, inc(), order["orderId"])

            worksheet2.write(row5, inc(), row1[1])

            worksheet2.write(row5, inc(), row1[-1])

            worksheet2.write(row5, inc(), row1[-2])

            worksheet2.write(row5, inc(), row1[-3])

            worksheet2.write(row5, inc(), order['merchantOrderId'])

            worksheet2.write(row5, inc(), order['subTagId'])

            worksheet2.write(row5, inc(), order['placedOn'])

            worksheet2.write(row5, inc(), formattedTimestamp)

            worksheet2.write(row5, inc(), int(order['paidAmount']))

            row5 -=1

            k=i

            for subOrder in order['subOrders']:

                i = k

                row5 += 1

                worksheet2.write(row5, inc(), subOrder['productTitle'])

                worksheet2.write(row5, inc(), subOrder['amountPaid'])

                worksheet2.write(row5, inc(), subOrder['quantity'])

                worksheet2.write(row5, inc(), subOrder['status'])

                worksheet2.write(row5, inc(), subOrder['cashBackStatus'])

                worksheet2.write(row5, inc(), subOrder['cashBackAmount'])

    last30Days =  date.today() - timedelta(days=30)

    int(time.mktime(last30Days.timetuple()))

    row = 0

    for offer in db.snapdealOrderAffiliateInfo.find({"reconciled":{"$exists":False}, 

                                                     "saleTime":{"$gt":int(time.mktime(last30Days.timetuple())),

                                                                 "$lt":int(time.mktime(date.today().timetuple()))}} ):

        subTagId = offer.get("subTagId")

        saleTime = offer.get("saleTime")

        orders = getOrdersByTag(subTagId, 3)

        for order in orders:

            if notReconciled.has_key(order[0]):

                (roww, column, paidAmount, timestamp1, oSubTagId) = notReconciled[order[0]]

                if paidAmount==int(offer.get("saleAmount")) and timestamp1 <= saleTime and timestamp1 + 120 >= saleTime:

                    i = column

                    worksheet1.write(roww, inc(), offer.get("saleDate"))        

                    worksheet1.write(roww, inc(), offer.get("saleAmount"))        

                    worksheet1.write(roww, inc(), offer.get("payOut"))

                    del notReconciled[order[0]]

                    print "found match for user", order[1]

                    db.snapdealOrderAffiliateInfo.update({"adId":offer.get("adId")}, {"$set":{"reconciled":True, "orderId":order[0]}}, multi=True)

                    db.merchantOrder.update({"orderId":order[0]}, {"$set":{"reconciled":True, "adId":offer.get("adId")}}, multi=True)

                    break

    unreconciledOrders = notReconciled.keys()

    for offer in db.snapdealOrderAffiliateInfo.find({"reconciled":{"$exists":False}, 

                                                     "saleTime":{"$gt":int(time.mktime(last30Days.timetuple())),

                                                                 "$lt":int(time.mktime(date.today().timetuple()))}} ):

        row += 1

        i=-1

        affNotReconciledSheet.write(row, inc(), offer.get("adId")) 

        affNotReconciledSheet.write(row, inc(), offer.get("subTagId")) 

        affNotReconciledSheet.write(row, inc(), offer.get("saleDate")) 

        affNotReconciledSheet.write(row, inc(), offer.get("saleAmount")) 

        affNotReconciledSheet.write(row, inc(), offer.get("payOut")) 

        affNotReconciledSheet.write(row, inc(), offer.get("conversionStatus"))

        affNotReconciledSheet.write(row, inc(), offer.get("ip"))

    workbook.save(XLS_FILENAME)

def generateShopcluesReco():

    global i

    curs = getOrdersAfterDate(datetime.now() - timedelta(days=30), 5)

    db = client.Dtr

    notReconciled = {}

    matchedList = []

    workbook = xlwt.Workbook()

    worksheet = workbook.add_sheet("Shopclues Reconciled")

    worksheet1 = workbook.add_sheet("Shopclues Reconciled All")

    worksheet2 = workbook.add_sheet("Shopclues Orders not reconciled")

    affNotReconciledSheet = workbook.add_sheet("Shopclues Aff not reconciled")

    setHeaders(worksheet, worksheet1, worksheet2, affNotReconciledSheet)

    row = 0

    anotherrow = 0

    row2 = 0

    row5=0

    for row1 in curs:

        orderId = row1[0]

        order = db.merchantOrder.find_one({"orderId":orderId})

        if order is None:

            continue

        #saleTime = int(time.mktime(datetime.strptime(order['placedOn'], "%b %d, %Y, %I:%M %p").timetuple()))  26 May 2015 02:12 PM

        aff = list(db.shopcluesOrderAffiliateInfo.find({"merchantOrderId":order["merchantOrderId"]}))

        anotherrow += 1

        try:

            formattedTimestamp = order['placedOn']

            timestamp1 = int(time.mktime(datetime.strptime(order['placedOn'], "%d %b %Y %I:%M %p").timetuple()))

        except:

            timestamp1 = int(time.mktime(row1[-4].timetuple()))

            formattedTimestamp = datetime.strftime(fromTimeStamp(timestamp1), "%d %b %Y %I:%M %p")

        if len(aff) > 0 and order["subTagId"] not in matchedList and long(order["paidAmount"])==long(float(aff[0]["transactionValue"])): 

            matchedList.append(order["subTagId"])

            db.shopcluesOrderAffiliateInfo.update({"merchantOrderId":aff[0].get("merchantOrderId")}, {"$set":{"reconciled":True, "orderId":orderId}}, multi=True)

            order['reconciled'] = True

            order['adId'] = aff[0].get("uniqueKey")

            db.merchantOrder.save(order)

            row += 1

            i=-1 

            worksheet.write(row, inc(), orderId)

            worksheet1.write(anotherrow, i, orderId)

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

            worksheet1.write(anotherrow, i, row1[1])

            worksheet.write(row, inc(), row1[-1])

            worksheet1.write(anotherrow, i, row1[-1])

            worksheet.write(row, inc(), row1[-2])

            worksheet1.write(anotherrow, i, row1[-2])

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

            worksheet1.write(anotherrow, i, row1[-3])

            worksheet.write(row, inc(), order['merchantOrderId'])

            worksheet1.write(anotherrow, i, order['merchantOrderId'])

            worksheet.write(row, inc(), order['subTagId'])

            worksheet1.write(anotherrow, i, order['subTagId'])

            worksheet.write(row, inc(), order['placedOn'])

            worksheet1.write(anotherrow, i, order['placedOn'])

            worksheet.write(row, inc(), formattedTimestamp)

            worksheet1.write(anotherrow, i, formattedTimestamp)

            worksheet.write(row, inc(), int(order['paidAmount']))

            worksheet1.write(anotherrow, i, int(order['paidAmount']))

            worksheet.write(row, inc(), datetime.strftime(fromTimeStamp(aff[0]['transactionTime']), "%d %b %Y %I:%M %p"))

            worksheet1.write(anotherrow, i, datetime.strftime(fromTimeStamp(aff[0]['transactionTime']), "%d %b %Y %I:%M %p"))

            worksheet.write(row, inc(), aff[0]['transactionValue'])

            worksheet1.write(anotherrow, i, aff[0]['transactionValue'])

            worksheet.write(row, inc(), aff[0]['payOut'])

            worksheet1.write(anotherrow, i, aff[0]['payOut'])

            worksheet.write(row, inc(), "NA")

            worksheet1.write(anotherrow, i, "NA")

            k = i

            row -= 1

            anotherrow -= 1

            for subOrder in order['subOrders']:

                i = k

                row += 1

                anotherrow += 1

                worksheet.write(row, inc(), subOrder['productTitle'])

                worksheet1.write(anotherrow, i, subOrder['productTitle'])

                worksheet.write(row, inc(), subOrder['amountPaid'])

                worksheet1.write(anotherrow, i, subOrder['amountPaid'])

                worksheet.write(row, inc(), subOrder['quantity'])

                worksheet1.write(anotherrow, i, subOrder['quantity'])

                worksheet.write(row, inc(), subOrder['status'])

                worksheet1.write(anotherrow, i, subOrder['status'])

                worksheet.write(row, inc(), subOrder['cashBackStatus'])

                worksheet1.write(anotherrow, i, subOrder['cashBackStatus'])

                worksheet.write(row, inc(), subOrder['cashBackAmount'])

                worksheet1.write(anotherrow, i, subOrder['cashBackAmount'])

        else:

            i=-1

            worksheet1.write(anotherrow, inc(), orderId)

            worksheet1.write(anotherrow, inc(), row1[1])

            worksheet1.write(anotherrow, inc(), row1[-1])

            worksheet1.write(anotherrow, inc(), row1[-2])

            worksheet1.write(anotherrow, inc(), row1[-3])

            worksheet1.write(anotherrow, inc(), order['merchantOrderId'])

            worksheet1.write(anotherrow, inc(), order['subTagId'])

            worksheet1.write(anotherrow, inc(), order['placedOn'])

            worksheet1.write(anotherrow, inc(), formattedTimestamp)

            worksheet1.write(anotherrow, inc(), int(order['paidAmount']))

            notReconciled[orderId] = (anotherrow, i, int(order['paidAmount']), timestamp1, order["subTagId"]) 

            inc()

            inc()

            inc()

            inc()

            k = i

            anotherrow -= 1

            for subOrder in order['subOrders']:

                anotherrow += 1

                i = k

                worksheet1.write(anotherrow, inc(), subOrder['productTitle'])

                worksheet1.write(anotherrow, inc(), subOrder['amountPaid'])

                worksheet1.write(anotherrow, inc(), subOrder['quantity'])

                worksheet1.write(anotherrow, inc(), subOrder['status'])

                worksheet1.write(anotherrow, inc(), subOrder['cashBackStatus'])

                worksheet1.write(anotherrow, inc(), subOrder['cashBackAmount'])

            row5 += 1

            i=-1

            worksheet2.write(row5, inc(), order["orderId"])

            worksheet2.write(row5, inc(), row1[1])

            worksheet2.write(row5, inc(), row1[-1])

            worksheet2.write(row5, inc(), row1[-2])

            worksheet2.write(row5, inc(), row1[-3])

            worksheet2.write(row5, inc(), order['merchantOrderId'])

            worksheet2.write(row5, inc(), order['subTagId'])

            worksheet2.write(row5, inc(), order['placedOn'])

            worksheet2.write(row5, inc(), formattedTimestamp)

            worksheet2.write(row5, inc(), int(order['paidAmount']))

            row5 -=1

            k=i

            for subOrder in order['subOrders']:

                i = k

                row5 += 1

                worksheet2.write(row5, inc(), subOrder['productTitle'])

                worksheet2.write(row5, inc(), subOrder['amountPaid'])

                worksheet2.write(row5, inc(), subOrder['quantity'])

                worksheet2.write(row5, inc(), subOrder['status'])

                worksheet2.write(row5, inc(), subOrder['cashBackStatus'])

                worksheet2.write(row5, inc(), subOrder['cashBackAmount'])

    last30Days =  date.today() - timedelta(days=30)

    int(time.mktime(last30Days.timetuple()))

    row = 0

    for offer in db.shopcluesOrderAffiliateInfo.find({"reconciled":{"$exists":False}, 

                                                     "transactionTime":{"$gt":int(time.mktime(last30Days.timetuple())),

                                                                 "$lt":int(time.mktime(date.today().timetuple()))}} ):

        subTagId = offer.get("subTagId")

        saleTime = offer.get("transactionTime")

        orders = getOrdersByTag(subTagId, 5)

        for order in orders:

            if notReconciled.has_key(order[0]):

                (roww, column, paidAmount, timestamp1, oSubTagId) = notReconciled[order[0]]

                if paidAmount==int(offer.get("transactionValue")) and timestamp1 <= saleTime and timestamp1 + 120 >= saleTime:

                    i = column

                    worksheet1.write(roww, inc(), datetime.strftime(fromTimeStamp(offer.get("transactionTime")), "%d %b %Y %I:%M %p"))        

                    worksheet1.write(roww, inc(), offer.get("transactionValue"))        

                    worksheet1.write(roww, inc(), offer.get("payOut"))

                    del notReconciled[order[0]]

                    print "found match for user", order[1]

                    db.shopcluesOrderAffiliateInfo.update({"transactionId":offer.get("transactionId")}, {"$set":{"reconciled":True, "orderId":order[0]}}, multi=True)

                    db.merchantOrder.update({"orderId":order[0]}, {"$set":{"reconciled":True, "adId":offer.get("uniqueKey")}}, multi=True)

                    break

    unreconciledOrders = notReconciled.keys()

    for offer in db.shopcluesOrderAffiliateInfo.find({"reconciled":{"$exists":False}, 

                                                     "transactionTime":{"$gt":int(time.mktime(last30Days.timetuple())),

                                                                 "$lt":int(time.mktime(date.today().timetuple()))}} ):

        row += 1

        i=-1

        affNotReconciledSheet.write(row, inc(), offer.get("uniqueKey")) 

        affNotReconciledSheet.write(row, inc(), offer.get("subTagId")) 

        affNotReconciledSheet.write(row, inc(), datetime.strftime(fromTimeStamp(offer.get("transactionTime")), "%d %b %Y %I:%M %p")) 

        affNotReconciledSheet.write(row, inc(), offer.get("transactionValue")) 

        affNotReconciledSheet.write(row, inc(), offer.get("payOut")) 

        affNotReconciledSheet.write(row, inc(), offer.get("status"))

        affNotReconciledSheet.write(row, inc(), 'NA')

    workbook.save(XLS_SC_FILENAME)

def sendmail(email, message, title, *varargs):

    if email == "":

        return

    mailServer = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)

    mailServer.ehlo()

    mailServer.starttls()

    mailServer.ehlo()

    # Create the container (outer) email message.

    msg = MIMEMultipart()

    msg['Subject'] = title

    msg.preamble = title

    html_msg = MIMEText(message, 'html')

    msg.attach(html_msg)

    #snapdeal more to be added here

    for fileName in varargs:

        snapdeal = MIMEBase('application', 'vnd.ms-excel')

        snapdeal.set_payload(file(fileName).read())

        encoders.encode_base64(snapdeal)

        snapdeal.add_header('Content-Disposition', 'attachment;filename=' + fileName)

        msg.attach(snapdeal)

    email.append('amit.gupta@shop2020.in')

    MAILTO = email 

    mailServer.login(SENDER, PASSWORD)

    mailServer.sendmail(PASSWORD, MAILTO, msg.as_string())

def setHeaders(worksheet, worksheet1, worksheet2, affNotReconciledSheet):

    boldStyle = xlwt.XFStyle()

    f = xlwt.Font()

    f.bold = True

    boldStyle.font = f

    row = 0

    global i

    i=-1    

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

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

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

    worksheet.write(row, inc(), 'Version Code', boldStyle)

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

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

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

    worksheet.write(row, inc(), 'Sale Date', boldStyle)

    worksheet.write(row, inc(), 'Dtr Sale Date', boldStyle)

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

    worksheet.write(row, inc(), 'Aff Sale Date', boldStyle)

    worksheet.write(row, inc(), 'Aff Sale Amt', boldStyle)

    worksheet.write(row, inc(), 'Aff PayOut', boldStyle)

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

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

    worksheet.write(row, inc(), 'Product Title', boldStyle)

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

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

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

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

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

    i = -1

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

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

    worksheet1.write(row, inc(), 'Username', boldStyle)

    worksheet1.write(row, inc(), 'Version Code', boldStyle)

    worksheet1.write(row, inc(), 'Device', boldStyle)

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

    worksheet1.write(row, inc(), 'SubtagId', boldStyle)

    worksheet1.write(row, inc(), 'Sale Date', boldStyle)

    worksheet1.write(row, inc(), 'Dtr Sale Date', boldStyle)

    worksheet1.write(row, inc(), 'Sale Amount', boldStyle)

    worksheet1.write(row, inc(), 'Aff Sale Date', boldStyle)

    worksheet1.write(row, inc(), 'Aff Sale Amt', boldStyle)

    worksheet1.write(row, inc(), 'Aff PayOut', boldStyle)

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

    worksheet1.write(row, inc(), 'Aff Status', boldStyle)

    worksheet1.write(row, inc(), 'Product Title', boldStyle)

    worksheet1.write(row, inc(), 'Price', boldStyle)

    worksheet1.write(row, inc(), 'Quantity', boldStyle)

    worksheet1.write(row, inc(), 'Status', boldStyle)

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

    worksheet1.write(row, inc(), 'CashBack', boldStyle) 

    i = -1

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

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

    worksheet2.write(row, inc(), 'Username', boldStyle)

    worksheet2.write(row, inc(), 'Version Code', boldStyle)

    worksheet2.write(row, inc(), 'Device', boldStyle)

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

    worksheet2.write(row, inc(), 'SubtagId', boldStyle)

    worksheet2.write(row, inc(), 'Sale Date', boldStyle)

    worksheet2.write(row, inc(), 'Dtr Sale Date', boldStyle)

    worksheet2.write(row, inc(), 'Sale Amount', boldStyle)

    worksheet2.write(row, inc(), 'Product Title', boldStyle)

    worksheet2.write(row, inc(), 'Price', boldStyle)

    worksheet2.write(row, inc(), 'Quantity', boldStyle)

    worksheet2.write(row, inc(), 'Status', boldStyle)

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

    worksheet2.write(row, inc(), 'CashBack', boldStyle)

    i =-1

    affNotReconciledSheet.write(row, inc(), 'AdId', boldStyle) 

    affNotReconciledSheet.write(row, inc(), 'subTagId', boldStyle) 

    affNotReconciledSheet.write(row, inc(), 'Sale Date', boldStyle) 

    affNotReconciledSheet.write(row, inc(), 'Sale Amount', boldStyle) 

    affNotReconciledSheet.write(row, inc(), 'Pay Out', boldStyle) 

    affNotReconciledSheet.write(row, inc(), 'Status', boldStyle) 

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

def getUserAmountReconciled():

    pass

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

    workbook = xlwt.Workbook()

    sh1 = workbook.add_sheet("All Orders")

    sh2 = workbook.add_sheet("Flipkart Orders")

    db=client.Dtr

    results = Mysql.fetchResult('''

        select ow.*, u.username, crm1.order_count, u.referrer, aua.city,aua.state,aua.pincode 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 in ('ORDER_CREATED','DETAIL_CREATED')  group by user_id)as crm1 on ow.user_id = crm1.user_id

        left join (select * from (select * from all_user_addresses order by source) s group by user_id)aua on aua.user_id=u.id 

        where (lower(u.referrer) not like 'emp%' or u.utm_campaign is not null) and u.activated = 1 and ow.status in ('ORDER_CREATED','DETAIL_CREATED'); 

        ''',)

    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)

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

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

    worksheet.write(row, inc(), 'Product title', boldStyle)

    #worksheet1.write(row, i, 'Product title', 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)

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

    #worksheet1.write(row, i, 'Aff Status', boldStyle)

    i=-1

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

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

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

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

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

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

    sh1.write(row, inc(), 'Status', boldStyle)

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

    sh1.write(row, inc(), 'Product title', boldStyle)

    sh1.write(row, inc(), 'Referrer', boldStyle)

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

    sh1.write(row, inc(), 'Catalog Id', boldStyle)

    sh1.write(row, inc(), 'Brand', boldStyle)

    sh1.write(row, inc(), 'Model', boldStyle)

    sh1.write(row, inc(), 'Category', boldStyle)

    sh1.write(row, inc(), 'Deal Rank', boldStyle)

    sh1.write(row, inc(), 'Max Nlc', boldStyle)

    sh1.write(row, inc(), 'Min Nlc', boldStyle)

    sh1.write(row, inc(), 'DP', boldStyle)

    sh1.write(row, inc(), 'Item status', boldStyle)

    sh1.write(row, inc(), 'City', boldStyle)

    sh1.write(row, inc(), 'State', boldStyle)

    sh1.write(row, inc(), 'Pincode', boldStyle)

    i =-1

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

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

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

    sh2.write(row, inc(), 'Store', boldStyle)

    sh2.write(row, inc(), 'Status', boldStyle)

    sh2.write(row, inc(), 'Product title', boldStyle)

    sh2.write(row, inc(), 'Price', boldStyle)

    sh2.write(row, inc(), 'Quantity', boldStyle)

    sh2.write(row, inc(), 'Catalog Id', boldStyle)

    sh2.write(row, inc(), 'Brand', boldStyle)

    sh2.write(row, inc(), 'Model', boldStyle)

    sh2.write(row, inc(), 'Category', boldStyle)

    row=0

    row2=0

    row3=0

    db1 = client.Catalog

    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:

                    row3+=1

                    i=-1

                    sh1.write(row3, inc(), result[1])

                    sh1.write(row3, inc(), result[7])

                    sh1.write(row3, inc(), result[0])

                    sh1.write(row3, inc(), result[6], datetime_format)

                    sh1.write(row3, inc(), sourceMap.get(result[2]))

                    sh1.write(row3, inc(), morder.get("merchantOrderId"))

                    sh1.write(row3, inc(), subOrder.get("status"))

                    sh1.write(row3, inc(), subOrder.get("detailedStatus"))

                    sh1.write(row3, inc(), subOrder.get("productTitle"))

                    sh1.write(row3, inc(), result[9])

                    inc()

                    if int(subOrder.get("quantity")) > 0:

                        sh1.write(row3, i, int(subOrder.get("amountPaid"))/int(subOrder.get("quantity")))

                    skuData = getSkuData(morder.get("storeId"), subOrder.get("productCode"))

                    if morder.get("storeId") in (1,2,4,5,6):

                        skuData = db1.MasterData.find_one({'identifier':subOrder.get("productCode").strip(), 'source_id':morder.get("storeId")})

                    elif morder.get("storeId") == 3:

                        skuData = db1.MasterData.find_one({'secondaryIdentifier':subOrder.get("productCode").strip(), 'source_id':morder.get("storeId")})

                    if skuData is not None:

                        sh1.write(row3, inc(), skuData.get("skuBundleId"))

                        sh1.write(row3, inc(), skuData.get("brand"))

                        sh1.write(row3, inc(), skuData.get("model_name"))

                        sh1.write(row3, inc(), skuData.get("category"))

                        sh1.write(row3, inc(), subOrder.get("dealRank"))

                        sh1.write(row3, inc(), subOrder.get("maxNlc"))

                        sh1.write(row3, inc(), subOrder.get("minNlc"))

                        sh1.write(row3, inc(), utils.statusMap.get(subOrder.get("db")))

                        sh1.write(row3, inc(), subOrder.get("itemStatus"))

                    else:

                        sh1.write(row3, inc(), None)

                        sh1.write(row3, inc(), None)

                        sh1.write(row3, inc(), None)

                        sh1.write(row3, inc(), None)

                        sh1.write(row3, inc(), None)

                        sh1.write(row3, inc(), None)

                        sh1.write(row3, inc(), None)

                        sh1.write(row3, inc(), None)

                        sh1.write(row3, inc(), None)

                    sh1.write(row3, inc(), result[10])

                    sh1.write(row3, inc(), result[11])

                    sh1.write(row3, inc(), result[12])

                    #removed filter as requested by Rajneesh

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

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

                    worksheet.write(row, inc(), result[6], datetime_format)

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

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

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

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

                    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:

                            snapdealafinfo = db.snapdealOrderAffiliateInfo.find_one({"adId":morder.get("adId"), "reconciled":True})

                            worksheet.write(row, inc(), snapdealafinfo.get("ip"))

                            worksheet.write(row, inc(), snapdealafinfo.get("conversionStatus"))

                        except:

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

                    #if result[8] >= 10:

                    #    row2 +=1 

                    #    i=-1

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

                        #worksheet1.write(row2, inc(), result[7])

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

                        #worksheet1.write(row2, inc(), result[6], datetime_format)

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

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

                        #worksheet1.write(row2, inc(), subOrder.get("productTitle"))

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

                        #worksheet1.write(row2, inc(), )

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

                        #    try:

                        #        snapdealafinfo = db.snapdealOrderAffiliateInfo.find_one({"adId":morder.get("adId"), "reconciled":True})

                        #        worksheet1.write(row2, inc(), snapdealafinfo.get("ip"))

                        #        worksheet1.write(row2, inc(), snapdealafinfo.get("conversionStatus"))

                        #    except:

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

    flipkartOrders = session.query(FlipkartOrders).order_by(FlipkartOrders.created.desc()).all()

    flipkartOrders.reverse()

    row3=0

    #try:

    for order in flipkartOrders:

        row3 += 1

        i=-1

        sh2.write(row3, inc(), order.user_id)

        sh2.write(row3, inc(), order.email)

        sh2.write(row3, inc(), order.created, datetime_format)

        sh2.write(row3, inc(), "Flipkart")

        sh2.write(row3, inc(), order.status)

        sh2.write(row3, inc(), order.title)

        sh2.write(row3, inc(), order.price)

        sh2.write(row3, inc(), order.quantity)

        sh2.write(row3, inc(), order.catalogId)

        sh2.write(row3, inc(), order.brand)

        sh2.write(row3, inc(), order.model)

        sh2.write(row3, inc(), order.category)

    wb.save(XLS_FILENAME)

    workbook.save(XLS_O_FILENAME)

def getSkuData(storeId, identifier):

    if storeId in (1,2,4,5,6):

        skuData = client.Catalog.MasterData.find_one({'identifier':identifier, 'source_id':storeId})

    elif storeId == 3:

        skuData = client.Catalog.MasterData.find_one({'secondaryIdentifier':identifier, 'source_id':storeId})

    return skuData

def inc():

    global i

    i+=1

    return i

def migrateFlipkartOrders():

    db = client.Dtr

    info = reversed(list(db.flipkartOrderAffiliateInfo.find()))

    #try:

    for order in info:

        userId = None

        subTagId = None

        email = None

        subTagId = order.get("subTagId")

        if subTagId:

            click = session.query(Clicks).filter_by(tag = subTagId).first()

            if click is not None:

                userId= click.user_id 

                user = session.query(Users.email).filter_by(id = userId).first()