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 import utils

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 dtr.utils.utils import toTimeStamp

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

    worksheet3 = workbook.add_sheet("DateWise Sale Summary")

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

    setHeaders(worksheet, worksheet1, worksheet2, affNotReconciledSheet, worksheet3)

    row = 0

    anotherrow = 0

    row5=0

    datemap={}

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

        try:

            saleTime = datetime.strptime(order['placedOn'], "%a, %d %b, %Y").strftime("%Y-%m-%d")

        except:

            saleTime = datetime.strptime(order['placedOn'], "%b %d, %Y, %I:%M %p").strftime("%Y-%m-%d")    

        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(utils.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()))}}, timeout=False ):

        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

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

    mailBodyTemplate="""

        <html>

            <body>

            <table cellspacing="0" border="1" style="text-align:right">

                <thead>

                    <tr>

                        <th style="text-align:center">Date</th>

                        <th  style="text-align:center">Sale</th>

                        <th  style="text-align:center">Approved</th>

                        <th  style="text-align:center">Approved %</th>

                        <th  style="text-align:center">Rejected</th>

                        <th  style="text-align:center">Rejected %</th>

                    </tr>

                </thead>

                <tbody>

                    {0}

                </tbody>

            </table>

            </body>

        </html>

    """

    tbody=[]

    rowtemplate="<tr><td>{0}</td><td>{1}</td><td>{2}</td><td>{3}</td><td>{4}</td><td>{5}</td></tr>"

    for offer in db.flSaleSnapshot.find().limit(61).sort("_id",-1):

        approved = offer.get("approvedAmount")

        rejected = offer.get("rejectedAmount")

        total = approved + rejected

        if total ==0:

            continue

        tbody.append(rowtemplate.format(offer.get("_id"), offer.get("paidAmount"), approved, int((approved*100/total)), rejected, int((rejected*100/total))))

    message = mailBodyTemplate.format("".join(tbody))

    workbook.save(XLS_FILENAME)

    return message

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(utils.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(utils.fromTimeStamp(aff[0]['transactionTime']), "%d %b %Y %I:%M %p"))

            worksheet1.write(anotherrow, i, datetime.strftime(utils.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(utils.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(utils.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, worksheet3=None):

    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)

    if worksheet3:

        i =-1

        worksheet3.write(row, inc(), 'Date', boldStyle) 

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

        worksheet3.write(row, inc(), 'Approved Sales', boldStyle) 

        worksheet3.write(row, inc(), 'Rejected Sales', 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("utf8")

    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)

                    print result[10]

                    sh1.write(row3, inc(), unicode(result[10], errors='ignore'))

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