Subversion Repositories SmartDukaan

Rev

Rev 17140 | Rev 17159 | Go to most recent revision | Details | Compare with Previous | Last modification | View Log | RSS feed

Rev Author Line No. Line
14450 amit.gupta 1 
'''
2 
Created on Mar 13, 2015
3 
 
4 
@author: amit
5 
'''
6 
from datetime import date, datetime, timedelta
14632 amit.gupta 7 
from dtr.main import Store, sourceMap
14650 amit.gupta 8 
from dtr.reports.amazonreco import generateAmazonReco
15445 amit.gupta 9 
from dtr.storage import Mysql, DataService
15932 amit.gupta 10 
from dtr.storage.DataService import Users, Clicks, FlipkartOrders,\
11 
    All_user_addresses
14613 amit.gupta 12 
from dtr.storage.Mysql import getOrdersAfterDate, getOrdersByTag
16947 amit.gupta 13 
from dtr.utils import utils
15423 amit.gupta 14 
from elixir import *
15440 amit.gupta 15 
from sqlalchemy.sql.expression import func, func, or_
14450 amit.gupta 16 
from email import encoders
17 
from email.mime.base import MIMEBase
18 
from email.mime.multipart import MIMEMultipart
19 
from email.mime.text import MIMEText
20 
from pymongo.mongo_client import MongoClient
14460 amit.gupta 21 
from xlrd import open_workbook
14524 amit.gupta 22 
from xlutils.copy import copy
14460 amit.gupta 23 
from xlwt.Workbook import Workbook
17137 naman 24 
import _mysql
14450 amit.gupta 25 
import MySQLdb
26 
import smtplib
27 
import time
28 
import xlwt
16317 amit.gupta 29 
from dtr.utils import utils
17001 amit.gupta 30 
from dtr.utils.utils import toTimeStamp
17137 naman 31 
from dtr.reports import getdata
14460 amit.gupta 32 
#from xlwt import
14450 amit.gupta 33 
 
15445 amit.gupta 34 
DataService.initialize()
14450 amit.gupta 35 
client = MongoClient('mongodb://localhost:27017/')
36 
 
37 
SENDER = "cnc.center@shop2020.in"
38 
PASSWORD = "5h0p2o2o"
39 
SUBJECT = "DTR User Segmentation report for " + date.today().isoformat()
40 
SMTP_SERVER = "smtp.gmail.com"
41 
SMTP_PORT = 587
42 
 
14647 amit.gupta 43 
XLS_FILENAME = "snapdealaffiliatereco.xls"
14632 amit.gupta 44 
XLS_O_FILENAME = "allorders.xls"
14647 amit.gupta 45 
XLS_F_FILENAME = "flipkartaffiliatereco.xls"
14650 amit.gupta 46 
XLS_A_FILENAME = "amazonaffiliatereco.xls"
15791 manish.sha 47 
XLS_SC_FILENAME = "shopcluesaffiliatereco.xls"
17013 manish.sha 48 
XLS_HS_FILENAME = "homeshopaffiliatereco.xls"
14460 amit.gupta 49 
boldStyle = xlwt.XFStyle()
50 
f = xlwt.Font()
51 
f.bold = True
52 
boldStyle.font = f
53 
i = -1
14616 amit.gupta 54 
 
55 
datetime_format = xlwt.XFStyle()
56 
datetime_format.num_format_str = 'dd/mm/yyyy HH:MM AM/PM'
15540 amit.gupta 57 
categoryMap = {3:"Mobiles", 5:"Tablets"}
14450 amit.gupta 58 
def generateFlipkartReco():
14508 amit.gupta 59 
    global i
60 
    i = -1
14460 amit.gupta 61 
    curs = getOrdersAfterDate(datetime.now() - timedelta(days=30), 2)
62 
    db = client.Dtr
14647 amit.gupta 63 
    wb = xlwt.Workbook()
64 
    #rb = open_workbook(XLS_F_FILENAME)
65 
    #wb = copy(rb)
14460 amit.gupta 66 
    #wb = xlwt.Workbook()
67 
    row = 0
14647 amit.gupta 68 
    row2 = 0
69 
    row3 = 0
70 
    row4 = 0
71 
    worksheet = wb.add_sheet("All Orders")
72 
    worksheet1 = wb.add_sheet("Orders Reconciled")
73 
    worksheet2 = wb.add_sheet("Orders not Reconciled")
74 
    worksheet3 = wb.add_sheet("Aff not Reconciled")
14460 amit.gupta 75 
    worksheet.write(row, inc(), 'Order Id', boldStyle)
14647 amit.gupta 76 
    worksheet1.write(row, i, 'Order Id', boldStyle)
77 
    worksheet2.write(row, i, 'Order Id', boldStyle)
14460 amit.gupta 78 
    worksheet.write(row, inc(), 'User Id', boldStyle)
14647 amit.gupta 79 
    worksheet1.write(row, i, 'User Id', boldStyle)
80 
    worksheet2.write(row, i, 'User Id', boldStyle)
14474 amit.gupta 81 
    worksheet.write(row, inc(), 'Username', boldStyle)
14647 amit.gupta 82 
    worksheet1.write(row, i, 'Username', boldStyle)
83 
    worksheet2.write(row, i, 'Username', boldStyle)
14771 amit.gupta 84 
    worksheet.write(row, inc(), 'Version code', boldStyle)
85 
    worksheet1.write(row, i, 'Version code', boldStyle)
86 
    worksheet2.write(row, i, 'Version code', boldStyle)
87 
    worksheet.write(row, inc(), 'Device', boldStyle)
88 
    worksheet1.write(row, i, 'Device', boldStyle)
89 
    worksheet2.write(row, i, 'Device', boldStyle)
14460 amit.gupta 90 
    worksheet.write(row, inc(), 'Merchant Order Id', boldStyle)
14647 amit.gupta 91 
    worksheet1.write(row, i, 'Merchant Order Id', boldStyle)
92 
    worksheet2.write(row, i, 'Merchant Order Id', boldStyle)
14460 amit.gupta 93 
    worksheet.write(row, inc(), 'Product Title', boldStyle)
14647 amit.gupta 94 
    worksheet1.write(row, i, 'Product Title', boldStyle)
95 
    worksheet2.write(row, i, 'Product Title', boldStyle)
14460 amit.gupta 96 
    worksheet.write(row, inc(), 'Price', boldStyle)
14647 amit.gupta 97 
    worksheet1.write(row, i, 'Price', boldStyle)
98 
    worksheet2.write(row, i, 'Price', boldStyle)
14460 amit.gupta 99 
    worksheet.write(row, inc(), 'Quantity', boldStyle)
14647 amit.gupta 100 
    worksheet1.write(row, i, 'Quantity', boldStyle)
101 
    worksheet2.write(row, i, 'Quantity', boldStyle)
14460 amit.gupta 102 
    worksheet.write(row, inc(), 'Status', boldStyle)
14647 amit.gupta 103 
    worksheet1.write(row, i, 'Status', boldStyle)
104 
    worksheet2.write(row, i, 'Status', boldStyle)
14460 amit.gupta 105 
    worksheet.write(row, inc(), 'Detailed Status', boldStyle)
14647 amit.gupta 106 
    worksheet1.write(row, i, 'Detailed Status', boldStyle)
107 
    worksheet2.write(row, i, 'Detailed Status', boldStyle)
14460 amit.gupta 108 
    worksheet.write(row, inc(), 'Cashback Status', boldStyle)
14647 amit.gupta 109 
    worksheet1.write(row, i, 'Cashback Status', boldStyle)
110 
    worksheet2.write(row, i, 'Cashback Status', boldStyle)
14460 amit.gupta 111 
    worksheet.write(row, inc(), 'Our CashBack', boldStyle)
14647 amit.gupta 112 
    worksheet1.write(row, i, 'Our CashBack', boldStyle)
113 
    worksheet2.write(row, i, 'Our CashBack', boldStyle)
114 
    worksheet.write(row, inc(), 'Sale Date', boldStyle)
115 
    worksheet1.write(row, i, 'Sale Date', boldStyle)
116 
    worksheet2.write(row, i, 'Sale Date', boldStyle)
14460 amit.gupta 117 
    worksheet.write(row, inc(), 'SubtagId', boldStyle)
14647 amit.gupta 118 
    worksheet1.write(row, i, 'SubtagId', boldStyle)
119 
    worksheet2.write(row, i, 'SubtagId', boldStyle)
14460 amit.gupta 120 
    worksheet.write(row, inc(), 'Category', boldStyle)
14647 amit.gupta 121 
    worksheet1.write(row, i, 'Category', boldStyle)
14460 amit.gupta 122 
    worksheet.write(row, inc(), 'Aff PayOut', boldStyle)
14647 amit.gupta 123 
    worksheet1.write(row, i, 'Aff PayOut', boldStyle)
14460 amit.gupta 124 
    worksheet.write(row, inc(), 'Aff Sale Amount', boldStyle)
14647 amit.gupta 125 
    worksheet1.write(row, i, 'Aff Sale Amount', boldStyle)
14460 amit.gupta 126 
    worksheet.write(row, inc(), 'Aff Sale Date', boldStyle)
14647 amit.gupta 127 
    worksheet1.write(row, i, 'Aff Sale Date', boldStyle)
14460 amit.gupta 128 
    for row1 in curs:
129 
        orderId = row1[0]
130 
        order = db.merchantOrder.find_one({"orderId":orderId})
131 
        if order is None:
132 
            continue
133 
        saleTime = int(time.mktime(datetime.strptime(order['placedOn'], "%d %B, %Y %I:%M %p").replace(hour=0, minute=0, second=0, microsecond=0).timetuple()))
134 
        #saleTime1 = int(time.mktime((datetime.strptime(order['placedOn'], "%b %d, %Y %I:%M %p") + timedelta(seconds=-60)).timetuple()))
14647 amit.gupta 135 
        reconciled_affiliates = []
14460 amit.gupta 136 
        for subOrder in order['subOrders']:
14647 amit.gupta 137 
            affs = list(db.flipkartOrderAffiliateInfo.find({"productCode":subOrder.get("productCode"), "saleDateInt":saleTime}))
14460 amit.gupta 138 
            row += 1
139 
            i=-1
140 
            worksheet.write(row, inc(), orderId)
141 
            worksheet.write(row, inc(), row1[1])
14474 amit.gupta 142 
            worksheet.write(row, inc(), row1[-1])
14771 amit.gupta 143 
            worksheet.write(row, inc(), row1[-2])
144 
            worksheet.write(row, inc(), row1[-3])
14460 amit.gupta 145 
            worksheet.write(row, inc(), order['merchantOrderId'])
146 
            worksheet.write(row, inc(), subOrder['productTitle'])
147 
            worksheet.write(row, inc(), subOrder['amountPaid'])
148 
            worksheet.write(row, inc(), subOrder['quantity'])
149 
            worksheet.write(row, inc(), subOrder['status'])
150 
            worksheet.write(row, inc(), subOrder['detailedStatus'])
151 
            worksheet.write(row, inc(), subOrder['cashBackStatus'])
152 
            worksheet.write(row, inc(), subOrder['cashBackAmount'])
153 
            worksheet.write(row, inc(), subOrder['placedOn'])
154 
            worksheet.write(row, inc(), order['subTagId'])
155 
            for aff in affs:
14647 amit.gupta 156 
                if aff["_id"] not in reconciled_affiliates and aff['subTagId']== order['subTagId']:
157 
                    reconciled_affiliates.append(aff["_id"])
158 
                    row2 += 1
159 
                    i = -1
160 
                    worksheet1.write(row2, inc(), orderId)
161 
                    worksheet1.write(row2, inc(), row1[1])
162 
                    worksheet1.write(row2, inc(), row1[-1])
14771 amit.gupta 163 
                    worksheet1.write(row2, inc(), row1[-2])
164 
                    worksheet1.write(row2, inc(), row1[-3])
14647 amit.gupta 165 
                    worksheet1.write(row2, inc(), order['merchantOrderId'])
166 
                    worksheet1.write(row2, inc(), subOrder['productTitle'])
167 
                    worksheet1.write(row2, inc(), subOrder['amountPaid'])
168 
                    worksheet1.write(row2, inc(), subOrder['quantity'])
169 
                    worksheet1.write(row2, inc(), subOrder['status'])
170 
                    worksheet1.write(row2, inc(), subOrder['detailedStatus'])
171 
                    worksheet1.write(row2, inc(), subOrder['cashBackStatus'])
172 
                    worksheet1.write(row2, inc(), subOrder['cashBackAmount'])
173 
                    worksheet1.write(row2, inc(), subOrder['placedOn'])
174 
                    worksheet1.write(row2, inc(), order['subTagId'])
175 
                    db.flipkartOrderAffiliateInfo.update(
176 
                        {"productCode":subOrder.get("productCode"), "saleDateInt":saleTime, "subTagId":aff['subTagId']},
177 
                        {"$set":{"reconciled":True}})
178 
                    db.merchantOrder.update({"merchantOrderId":order["merchantOrderId"]},
179 
                                            {"$set":{"reconciled":True}})
14460 amit.gupta 180 
                    worksheet.write(row, inc(), aff['category'])
14647 amit.gupta 181 
                    worksheet1.write(row2, i, aff['category'])
14460 amit.gupta 182 
                    worksheet.write(row, inc(), aff['payOut'])
14647 amit.gupta 183 
                    worksheet1.write(row2, i, aff['payOut'])
14460 amit.gupta 184 
                    worksheet.write(row, inc(), aff['saleAmount'])
14647 amit.gupta 185 
                    worksheet1.write(row2, i, aff['saleAmount'])
14460 amit.gupta 186 
                    worksheet.write(row, inc(), aff['saleDate'])
14647 amit.gupta 187 
                    worksheet1.write(row2, i, aff['saleDate'])
14460 amit.gupta 188 
                    break
14647 amit.gupta 189 
            row3 += 1
190 
            i=-1
191 
            worksheet2.write(row3, inc(), orderId)
192 
            worksheet2.write(row3, inc(), row1[1])
193 
            worksheet2.write(row3, inc(), row1[-1])
14771 amit.gupta 194 
            worksheet2.write(row3, inc(), row1[-2])
195 
            worksheet2.write(row3, inc(), row1[-3])
14647 amit.gupta 196 
            worksheet2.write(row3, inc(), order['merchantOrderId'])
197 
            worksheet2.write(row3, inc(), subOrder['productTitle'])
198 
            worksheet2.write(row3, inc(), subOrder['amountPaid'])
199 
            worksheet2.write(row3, inc(), subOrder['quantity'])
200 
            worksheet2.write(row3, inc(), subOrder['status'])
201 
            worksheet2.write(row3, inc(), subOrder['detailedStatus'])
202 
            worksheet2.write(row3, inc(), subOrder['cashBackStatus'])
203 
            worksheet2.write(row3, inc(), subOrder['cashBackAmount'])
204 
            worksheet2.write(row3, inc(), subOrder['placedOn'])
205 
            worksheet2.write(row3, inc(), order['subTagId'])
206 
 
207 
    i=-1
208 
    worksheet3.write(0, inc(), "category",boldStyle)
209 
    worksheet3.write(0, inc(), "conversionStatus",boldStyle)
210 
    worksheet3.write(0, inc(), "Product Title",boldStyle)
211 
    worksheet3.write(0, inc(), "price",boldStyle)
212 
    worksheet3.write(0, inc(), "quantity",boldStyle)
213 
    worksheet3.write(0, inc(), "payOut",boldStyle)
214 
    worksheet3.write(0, inc(), "saleDate",boldStyle)
215 
    worksheet3.write(0, inc(), "subTagId",boldStyle)
216 
    for aff in db.flipkartOrderAffiliateInfo.find({"reconciled":{"$exists":False}}):
217 
        row4 += 1
218 
        i =-1
219 
        worksheet3.write(row4, inc(), aff["category"])
220 
        worksheet3.write(row4, inc(), aff["conversionStatus"])
221 
        skuData = getSkuData(2, aff["productCode"])
222 
        if skuData is None:
223 
            worksheet3.write(row4, inc(), aff["productTitle"])
224 
        else:
225 
            worksheet3.write(row4, inc(), skuData["product_name"])
226 
        worksheet3.write(row4, inc(), aff['price'])
227 
        worksheet3.write(row4, inc(), aff['quantity'])
228 
        worksheet3.write(row4, inc(), aff['payOut'])
229 
        worksheet3.write(row4, inc(), aff['saleDate'])
230 
        worksheet3.write(row4, inc(), aff['subTagId'])
231 
 
232 
 
233 
 
234 
    wb.save(XLS_F_FILENAME)
14450 amit.gupta 235 
def generateSnapDealReco():
14524 amit.gupta 236 
    global i
14460 amit.gupta 237 
    curs = getOrdersAfterDate(datetime.now() - timedelta(days=30), 3)
14450 amit.gupta 238 
    db = client.Dtr
14524 amit.gupta 239 
    notReconciled = {}
14487 amit.gupta 240 
    matchedList = []
14450 amit.gupta 241 
    workbook = xlwt.Workbook()
14501 amit.gupta 242 
    worksheet = workbook.add_sheet("Snapdeal Reconciled")
243 
    worksheet1 = workbook.add_sheet("Snapdeal Reconciled All")
14508 amit.gupta 244 
    worksheet2 = workbook.add_sheet("Snapdeal Orders not reconciled")
16947 amit.gupta 245 
    worksheet3 = workbook.add_sheet("DateWise Sale Summary")
14508 amit.gupta 246 
    affNotReconciledSheet = workbook.add_sheet("Snapdeal Aff not reconciled")
16947 amit.gupta 247 
    setHeaders(worksheet, worksheet1, worksheet2, affNotReconciledSheet, worksheet3)
14450 amit.gupta 248 
    row = 0
14501 amit.gupta 249 
    anotherrow = 0
15577 amit.gupta 250 
    row5=0
16947 amit.gupta 251 
    datemap={}
14450 amit.gupta 252 
    for row1 in curs:
253 
        orderId = row1[0]
254 
        order = db.merchantOrder.find_one({"orderId":orderId})
255 
        if order is None:
256 
            continue
14496 amit.gupta 257 
        #saleTime = int(time.mktime(datetime.strptime(order['placedOn'], "%b %d, %Y, %I:%M %p").timetuple()))
16949 amit.gupta 258 
        try:
16951 amit.gupta 259 
            saleTime = datetime.strptime(order['placedOn'], "%a, %d %b, %Y").strftime("%Y-%m-%d")
16949 amit.gupta 260 
        except:
17001 amit.gupta 261 
            saleTime = datetime.strptime(order['placedOn'], "%b %d, %Y, %I:%M %p").strftime("%Y-%m-%d")
16947 amit.gupta 262 
 
14450 amit.gupta 263 
        aff = list(db.snapdealOrderAffiliateInfo.find({"subTagId":order["subTagId"]}))
16718 amit.gupta 264 
        if len(aff) > 1:
265 
            aff = [f for f in aff if f.get("payOut")>0]
14501 amit.gupta 266 
        anotherrow += 1
14988 amit.gupta 267 
        try:
268 
            formattedTimestamp = order['placedOn']
269 
            timestamp1 = int(time.mktime(datetime.strptime(order['placedOn'], "%b %d, %Y, %I:%M %p").timetuple()))
270 
        except:
271 
            timestamp1 = int(time.mktime(row1[-4].timetuple()))
16947 amit.gupta 272 
            formattedTimestamp = datetime.strftime(utils.fromTimeStamp(timestamp1), "%b %d, %Y, %I:%M %p")
16718 amit.gupta 273 
        if len(aff) > 0 and order["subTagId"] not in matchedList and int(order["paidAmount"])==int(aff[0]["saleAmount"]):
274 
 
14501 amit.gupta 275 
            matchedList.append(order["subTagId"])
14610 amit.gupta 276 
            db.snapdealOrderAffiliateInfo.update({"adId":aff[0].get("adId")}, {"$set":{"reconciled":True, "orderId":orderId}}, multi=True)
14501 amit.gupta 277 
            order['reconciled'] = True
14610 amit.gupta 278 
            order['adId'] = aff[0].get("adId")
14501 amit.gupta 279 
            db.merchantOrder.save(order)
280 
            row += 1
281 
            i=-1
282 
            worksheet.write(row, inc(), orderId)
283 
            worksheet1.write(anotherrow, i, orderId)
284 
            worksheet.write(row, inc(), row1[1])
285 
            worksheet1.write(anotherrow, i, row1[1])
14850 amit.gupta 286 
            worksheet.write(row, inc(), row1[-1])
287 
            worksheet1.write(anotherrow, i, row1[-1])
288 
            worksheet.write(row, inc(), row1[-2])
289 
            worksheet1.write(anotherrow, i, row1[-2])
14771 amit.gupta 290 
            worksheet.write(row, inc(), row1[-3])
291 
            worksheet1.write(anotherrow, i, row1[-3])
14501 amit.gupta 292 
            worksheet.write(row, inc(), order['merchantOrderId'])
293 
            worksheet1.write(anotherrow, i, order['merchantOrderId'])
294 
            worksheet.write(row, inc(), order['subTagId'])
295 
            worksheet1.write(anotherrow, i, order['subTagId'])
296 
            worksheet.write(row, inc(), order['placedOn'])
297 
            worksheet1.write(anotherrow, i, order['placedOn'])
14988 amit.gupta 298 
            worksheet.write(row, inc(), formattedTimestamp)
299 
            worksheet1.write(anotherrow, i, formattedTimestamp)
14501 amit.gupta 300 
            worksheet.write(row, inc(), int(order['paidAmount']))
301 
            worksheet1.write(anotherrow, i, int(order['paidAmount']))
14524 amit.gupta 302 
            worksheet.write(row, inc(), aff[0]['saleDate'])
303 
            worksheet1.write(anotherrow, i, aff[0]['saleDate'])
304 
            worksheet.write(row, inc(), aff[0]['saleAmount'])
305 
            worksheet1.write(anotherrow, i, aff[0]['saleAmount'])
306 
            worksheet.write(row, inc(), aff[0]['payOut'])
307 
            worksheet1.write(anotherrow, i, aff[0]['payOut'])
14601 amit.gupta 308 
            worksheet.write(row, inc(), aff[0]['ip'])
309 
            worksheet1.write(anotherrow, i, aff[0]['ip'])
16718 amit.gupta 310 
            worksheet.write(row, inc(), aff[0]['conversionStatus'])
311 
            worksheet1.write(anotherrow, i, aff[0]['conversionStatus'])
14501 amit.gupta 312 
            k = i
14504 amit.gupta 313 
            row -= 1
314 
            anotherrow -= 1
14501 amit.gupta 315 
            for subOrder in order['subOrders']:
316 
                i = k
14503 amit.gupta 317 
                row += 1
318 
                anotherrow += 1
14501 amit.gupta 319 
                worksheet.write(row, inc(), subOrder['productTitle'])
320 
                worksheet1.write(anotherrow, i, subOrder['productTitle'])
321 
                worksheet.write(row, inc(), subOrder['amountPaid'])
322 
                worksheet1.write(anotherrow, i, subOrder['amountPaid'])
323 
                worksheet.write(row, inc(), subOrder['quantity'])
324 
                worksheet1.write(anotherrow, i, subOrder['quantity'])
325 
                worksheet.write(row, inc(), subOrder['status'])
326 
                worksheet1.write(anotherrow, i, subOrder['status'])
327 
                worksheet.write(row, inc(), subOrder['cashBackStatus'])
328 
                worksheet1.write(anotherrow, i, subOrder['cashBackStatus'])
329 
                worksheet.write(row, inc(), subOrder['cashBackAmount'])
330 
                worksheet1.write(anotherrow, i, subOrder['cashBackAmount'])
14504 amit.gupta 331 
 
14501 amit.gupta 332 
        else:
14524 amit.gupta 333 
            i=-1
14501 amit.gupta 334 
            worksheet1.write(anotherrow, inc(), orderId)
335 
            worksheet1.write(anotherrow, inc(), row1[1])
14850 amit.gupta 336 
            worksheet1.write(anotherrow, inc(), row1[-1])
337 
            worksheet1.write(anotherrow, inc(), row1[-2])
14771 amit.gupta 338 
            worksheet1.write(anotherrow, inc(), row1[-3])
14501 amit.gupta 339 
            worksheet1.write(anotherrow, inc(), order['merchantOrderId'])
340 
            worksheet1.write(anotherrow, inc(), order['subTagId'])
341 
            worksheet1.write(anotherrow, inc(), order['placedOn'])
15576 amit.gupta 342 
            worksheet1.write(anotherrow, inc(), formattedTimestamp)
14501 amit.gupta 343 
            worksheet1.write(anotherrow, inc(), int(order['paidAmount']))
14873 amit.gupta 344 
 
14524 amit.gupta 345 
            notReconciled[orderId] = (anotherrow, i, int(order['paidAmount']), timestamp1, order["subTagId"])
346 
            inc()
347 
            inc()
348 
            inc()
14771 amit.gupta 349 
            inc()
16718 amit.gupta 350 
            inc()
14501 amit.gupta 351 
            k = i
14504 amit.gupta 352 
            anotherrow -= 1
14501 amit.gupta 353 
            for subOrder in order['subOrders']:
14503 amit.gupta 354 
                anotherrow += 1
14501 amit.gupta 355 
                i = k
14505 amit.gupta 356 
                worksheet1.write(anotherrow, inc(), subOrder['productTitle'])
357 
                worksheet1.write(anotherrow, inc(), subOrder['amountPaid'])
358 
                worksheet1.write(anotherrow, inc(), subOrder['quantity'])
359 
                worksheet1.write(anotherrow, inc(), subOrder['status'])
360 
                worksheet1.write(anotherrow, inc(), subOrder['cashBackStatus'])
361 
                worksheet1.write(anotherrow, inc(), subOrder['cashBackAmount'])
15577 amit.gupta 362 
            row5 += 1
363 
            i=-1
364 
            worksheet2.write(row5, inc(), order["orderId"])
365 
            worksheet2.write(row5, inc(), row1[1])
366 
            worksheet2.write(row5, inc(), row1[-1])
367 
            worksheet2.write(row5, inc(), row1[-2])
368 
            worksheet2.write(row5, inc(), row1[-3])
369 
            worksheet2.write(row5, inc(), order['merchantOrderId'])
370 
            worksheet2.write(row5, inc(), order['subTagId'])
371 
            worksheet2.write(row5, inc(), order['placedOn'])
372 
            worksheet2.write(row5, inc(), formattedTimestamp)
373 
            worksheet2.write(row5, inc(), int(order['paidAmount']))
374 
            row5 -=1
375 
            k=i
376 
            for subOrder in order['subOrders']:
377 
                i = k
15578 amit.gupta 378 
                row5 += 1
15577 amit.gupta 379 
                worksheet2.write(row5, inc(), subOrder['productTitle'])
380 
                worksheet2.write(row5, inc(), subOrder['amountPaid'])
381 
                worksheet2.write(row5, inc(), subOrder['quantity'])
382 
                worksheet2.write(row5, inc(), subOrder['status'])
383 
                worksheet2.write(row5, inc(), subOrder['cashBackStatus'])
15578 amit.gupta 384 
                worksheet2.write(row5, inc(), subOrder['cashBackAmount'])
14524 amit.gupta 385 
 
386 
    last30Days =  date.today() - timedelta(days=30)
387 
    int(time.mktime(last30Days.timetuple()))
388 
    row = 0
389 
    for offer in db.snapdealOrderAffiliateInfo.find({"reconciled":{"$exists":False},
390 
                                                     "saleTime":{"$gt":int(time.mktime(last30Days.timetuple())),
16953 amit.gupta 391 
                                                                 "$lt":int(time.mktime(date.today().timetuple()))}}, timeout=False ):
14524 amit.gupta 392 
        subTagId = offer.get("subTagId")
393 
        saleTime = offer.get("saleTime")
394 
        orders = getOrdersByTag(subTagId, 3)
395 
        for order in orders:
396 
            if notReconciled.has_key(order[0]):
397 
                (roww, column, paidAmount, timestamp1, oSubTagId) = notReconciled[order[0]]
398 
                if paidAmount==int(offer.get("saleAmount")) and timestamp1 <= saleTime and timestamp1 + 120 >= saleTime:
399 
                    i = column
14570 amit.gupta 400 
                    worksheet1.write(roww, inc(), offer.get("saleDate"))
14527 amit.gupta 401 
                    worksheet1.write(roww, inc(), offer.get("saleAmount"))
402 
                    worksheet1.write(roww, inc(), offer.get("payOut"))
403 
                    del notReconciled[order[0]]
14530 amit.gupta 404 
                    print "found match for user", order[1]
14610 amit.gupta 405 
                    db.snapdealOrderAffiliateInfo.update({"adId":offer.get("adId")}, {"$set":{"reconciled":True, "orderId":order[0]}}, multi=True)
406 
                    db.merchantOrder.update({"orderId":order[0]}, {"$set":{"reconciled":True, "adId":offer.get("adId")}}, multi=True)
14570 amit.gupta 407 
                    break
17010 amit.gupta 408 
 
14570 amit.gupta 409 
 
410 
 
411 
    for offer in db.snapdealOrderAffiliateInfo.find({"reconciled":{"$exists":False},
412 
                                                     "saleTime":{"$gt":int(time.mktime(last30Days.timetuple())),
413 
                                                                 "$lt":int(time.mktime(date.today().timetuple()))}} ):
414 
        row += 1
415 
        i=-1
416 
        affNotReconciledSheet.write(row, inc(), offer.get("adId"))
417 
        affNotReconciledSheet.write(row, inc(), offer.get("subTagId"))
418 
        affNotReconciledSheet.write(row, inc(), offer.get("saleDate"))
419 
        affNotReconciledSheet.write(row, inc(), offer.get("saleAmount"))
420 
        affNotReconciledSheet.write(row, inc(), offer.get("payOut"))
421 
        affNotReconciledSheet.write(row, inc(), offer.get("conversionStatus"))
14601 amit.gupta 422 
        affNotReconciledSheet.write(row, inc(), offer.get("ip"))
16947 amit.gupta 423 
 
17010 amit.gupta 424 
    mailBodyTemplate="""
425 
        <html>
426 
            <body>
427 
            <table cellspacing="0" border="1" style="text-align:right">
428 
                <thead>
429 
                    <tr>
430 
                        <th style="text-align:center">Date</th>
431 
                        <th  style="text-align:center">Sale</th>
432 
                        <th  style="text-align:center">Approved</th>
433 
                        <th  style="text-align:center">Approved %</th>
434 
                        <th  style="text-align:center">Rejected</th>
435 
                        <th  style="text-align:center">Rejected %</th>
17033 amit.gupta 436 
                        <th  style="text-align:center">Missing %</th>
437 
                        <th  style="text-align:center">Approved % against Sale</th>
17010 amit.gupta 438 
                    </tr>
439 
                </thead>
440 
                <tbody>
441 
                    {0}
442 
                </tbody>
443 
            </table>
444 
            </body>
445 
        </html>
446 
    """
447 
    tbody=[]
17033 amit.gupta 448 
    rowtemplate="<tr><td>{0}</td><td>{1}</td><td>{2}</td><td>{3}</td><td>{4}</td><td>{5}</td><td>{6}</td><td>{7}</td></tr>"
17010 amit.gupta 449 
    for offer in db.flSaleSnapshot.find().limit(61).sort("_id",-1):
450 
        approved = offer.get("approvedAmount")
451 
        rejected = offer.get("rejectedAmount")
17033 amit.gupta 452 
        sale = offer.get("paidAmount")
17010 amit.gupta 453 
        total = approved + rejected
454 
        if total ==0:
455 
            continue
17033 amit.gupta 456 
        tbody.append(rowtemplate.format(offer.get("_id"), sale, approved, int((approved*100/total)), rejected, int((rejected*100/total)), int((sale-approved-rejected)*100/sale),int((approved)*100/sale)))
17010 amit.gupta 457 
    message = mailBodyTemplate.format("".join(tbody))
15540 amit.gupta 458 
    workbook.save(XLS_FILENAME)
17024 amit.gupta 459 
    return message
17013 manish.sha 460 
 
461 
def generateHomeShopReco():
462 
    global i
463 
    curs = getOrdersAfterDate(datetime.now() - timedelta(days=30), 7)
464 
    db = client.Dtr
465 
    notReconciled = {}
466 
    matchedList = []
467 
    workbook = xlwt.Workbook()
468 
    worksheet = workbook.add_sheet("HomeShop18 Reconciled")
469 
    worksheet1 = workbook.add_sheet("HomeShop18 Reconciled All")
470 
    worksheet2 = workbook.add_sheet("HomeShop18 Orders not reconciled")
471 
    affNotReconciledSheet = workbook.add_sheet("HomeShop18 Aff not reconciled")
472 
    setHeaders(worksheet, worksheet1, worksheet2, affNotReconciledSheet)
473 
    row = 0
474 
    anotherrow = 0
475 
    row2 = 0
476 
    row5=0
477 
 
478 
    for row1 in curs:
479 
        orderId = row1[0]
480 
        order = db.merchantOrder.find_one({"orderId":orderId})
481 
        if order is None:
482 
            continue
483 
        #saleTime = int(time.mktime(datetime.strptime(order['placedOn'], "%b %d, %Y, %I:%M %p").timetuple()))  26 May 2015 02:12 PM
484 
        aff = list(db.shopcluesOrderAffiliateInfo.find({"merchantOrderId":order["merchantOrderId"]}))
485 
        anotherrow += 1
486 
        try:
487 
            formattedTimestamp = order['placedOn']
488 
            timestamp1 = int(time.mktime(datetime.strptime(order['placedOn'], "%d %b %Y %I:%M %p").timetuple()))
489 
        except:
490 
            timestamp1 = int(time.mktime(row1[-4].timetuple()))
491 
            formattedTimestamp = datetime.strftime(utils.fromTimeStamp(timestamp1), "%d %b %Y %I:%M %p")
492 
 
493 
        if len(aff) > 0 and order["subTagId"] not in matchedList and long(order["paidAmount"])==long(float(aff[0]["transactionValue"])):
494 
            matchedList.append(order["subTagId"])
495 
            db.shopcluesOrderAffiliateInfo.update({"merchantOrderId":aff[0].get("merchantOrderId")}, {"$set":{"reconciled":True, "orderId":orderId}}, multi=True)
496 
            order['reconciled'] = True
497 
            order['adId'] = aff[0].get("uniqueKey")
498 
            db.merchantOrder.save(order)
499 
            row += 1
500 
            i=-1
501 
 
502 
            worksheet.write(row, inc(), orderId)
503 
            worksheet1.write(anotherrow, i, orderId)
504 
            worksheet.write(row, inc(), row1[1])
505 
            worksheet1.write(anotherrow, i, row1[1])
506 
            worksheet.write(row, inc(), row1[-1])
507 
            worksheet1.write(anotherrow, i, row1[-1])
508 
            worksheet.write(row, inc(), row1[-2])
509 
            worksheet1.write(anotherrow, i, row1[-2])
510 
            worksheet.write(row, inc(), row1[-3])
511 
            worksheet1.write(anotherrow, i, row1[-3])
512 
            worksheet.write(row, inc(), order['merchantOrderId'])
513 
            worksheet1.write(anotherrow, i, order['merchantOrderId'])
514 
            worksheet.write(row, inc(), order['subTagId'])
515 
            worksheet1.write(anotherrow, i, order['subTagId'])
516 
            worksheet.write(row, inc(), order['placedOn'])
517 
            worksheet1.write(anotherrow, i, order['placedOn'])
518 
            worksheet.write(row, inc(), formattedTimestamp)
519 
            worksheet1.write(anotherrow, i, formattedTimestamp)
520 
            worksheet.write(row, inc(), int(order['paidAmount']))
521 
            worksheet1.write(anotherrow, i, int(order['paidAmount']))
522 
            worksheet.write(row, inc(), datetime.strftime(utils.fromTimeStamp(aff[0]['transactionTime']), "%d %b %Y %I:%M %p"))
523 
            worksheet1.write(anotherrow, i, datetime.strftime(utils.fromTimeStamp(aff[0]['transactionTime']), "%d %b %Y %I:%M %p"))
524 
            worksheet.write(row, inc(), aff[0]['transactionValue'])
525 
            worksheet1.write(anotherrow, i, aff[0]['transactionValue'])
526 
            worksheet.write(row, inc(), aff[0]['payOut'])
527 
            worksheet1.write(anotherrow, i, aff[0]['payOut'])
528 
            worksheet.write(row, inc(), "NA")
529 
            worksheet1.write(anotherrow, i, "NA")
530 
            k = i
531 
            row -= 1
532 
            anotherrow -= 1
533 
            for subOrder in order['subOrders']:
534 
                i = k
535 
                row += 1
536 
                anotherrow += 1
537 
                worksheet.write(row, inc(), subOrder['productTitle'])
538 
                worksheet1.write(anotherrow, i, subOrder['productTitle'])
539 
                worksheet.write(row, inc(), subOrder['amountPaid'])
540 
                worksheet1.write(anotherrow, i, subOrder['amountPaid'])
541 
                worksheet.write(row, inc(), subOrder['quantity'])
542 
                worksheet1.write(anotherrow, i, subOrder['quantity'])
543 
                worksheet.write(row, inc(), subOrder['status'])
544 
                worksheet1.write(anotherrow, i, subOrder['status'])
545 
                worksheet.write(row, inc(), subOrder['cashBackStatus'])
546 
                worksheet1.write(anotherrow, i, subOrder['cashBackStatus'])
547 
                worksheet.write(row, inc(), subOrder['cashBackAmount'])
548 
                worksheet1.write(anotherrow, i, subOrder['cashBackAmount'])
549 
 
550 
        else:
551 
            i=-1
552 
            worksheet1.write(anotherrow, inc(), orderId)
553 
            worksheet1.write(anotherrow, inc(), row1[1])
554 
            worksheet1.write(anotherrow, inc(), row1[-1])
555 
            worksheet1.write(anotherrow, inc(), row1[-2])
556 
            worksheet1.write(anotherrow, inc(), row1[-3])
557 
            worksheet1.write(anotherrow, inc(), order['merchantOrderId'])
558 
            worksheet1.write(anotherrow, inc(), order['subTagId'])
559 
            worksheet1.write(anotherrow, inc(), order['placedOn'])
560 
            worksheet1.write(anotherrow, inc(), formattedTimestamp)
561 
            worksheet1.write(anotherrow, inc(), int(order['paidAmount']))
562 
 
563 
            notReconciled[orderId] = (anotherrow, i, int(order['paidAmount']), timestamp1, order["subTagId"])
564 
            inc()
565 
            inc()
566 
            inc()
567 
            inc()
568 
            k = i
569 
            anotherrow -= 1
570 
            for subOrder in order['subOrders']:
571 
                anotherrow += 1
572 
                i = k
573 
                worksheet1.write(anotherrow, inc(), subOrder['productTitle'])
574 
                worksheet1.write(anotherrow, inc(), subOrder['amountPaid'])
575 
                worksheet1.write(anotherrow, inc(), subOrder['quantity'])
576 
                worksheet1.write(anotherrow, inc(), subOrder['status'])
577 
                worksheet1.write(anotherrow, inc(), subOrder['cashBackStatus'])
578 
                worksheet1.write(anotherrow, inc(), subOrder['cashBackAmount'])
579 
            row5 += 1
580 
            i=-1
581 
            worksheet2.write(row5, inc(), order["orderId"])
582 
            worksheet2.write(row5, inc(), row1[1])
583 
            worksheet2.write(row5, inc(), row1[-1])
584 
            worksheet2.write(row5, inc(), row1[-2])
585 
            worksheet2.write(row5, inc(), row1[-3])
586 
            worksheet2.write(row5, inc(), order['merchantOrderId'])
587 
            worksheet2.write(row5, inc(), order['subTagId'])
588 
            worksheet2.write(row5, inc(), order['placedOn'])
589 
            worksheet2.write(row5, inc(), formattedTimestamp)
590 
            worksheet2.write(row5, inc(), int(order['paidAmount']))
591 
            row5 -=1
592 
            k=i
593 
            for subOrder in order['subOrders']:
594 
                i = k
595 
                row5 += 1
596 
                worksheet2.write(row5, inc(), subOrder['productTitle'])
597 
                worksheet2.write(row5, inc(), subOrder['amountPaid'])
598 
                worksheet2.write(row5, inc(), subOrder['quantity'])
599 
                worksheet2.write(row5, inc(), subOrder['status'])
600 
                worksheet2.write(row5, inc(), subOrder['cashBackStatus'])
601 
                worksheet2.write(row5, inc(), subOrder['cashBackAmount'])
602 
    last30Days =  date.today() - timedelta(days=30)
603 
    int(time.mktime(last30Days.timetuple()))
604 
    row = 0
605 
    for offer in db.homeshopOrderAffiliateInfo.find({"reconciled":{"$exists":False},
606 
                                                     "transactionTime":{"$gt":int(time.mktime(last30Days.timetuple())),
607 
                                                                 "$lt":int(time.mktime(date.today().timetuple()))}} ):
608 
        subTagId = offer.get("subTagId")
609 
        saleTime = offer.get("transactionTime")
610 
        orders = getOrdersByTag(subTagId, 7)
611 
        for order in orders:
612 
            if notReconciled.has_key(order[0]):
613 
                (roww, column, paidAmount, timestamp1, oSubTagId) = notReconciled[order[0]]
614 
                if paidAmount==int(offer.get("transactionValue")) and timestamp1 <= saleTime and timestamp1 + 120 >= saleTime:
615 
                    i = column
616 
                    worksheet1.write(roww, inc(), datetime.strftime(utils.fromTimeStamp(offer.get("transactionTime")), "%d %b %Y %I:%M %p"))
617 
                    worksheet1.write(roww, inc(), offer.get("transactionValue"))
618 
                    worksheet1.write(roww, inc(), offer.get("payOut"))
619 
                    del notReconciled[order[0]]
620 
                    print "found match for user", order[1]
621 
                    db.homeshopOrderAffiliateInfo.update({"transactionId":offer.get("transactionId")}, {"$set":{"reconciled":True, "orderId":order[0]}}, multi=True)
622 
                    db.merchantOrder.update({"orderId":order[0]}, {"$set":{"reconciled":True, "adId":offer.get("uniqueKey")}}, multi=True)
623 
                    break
624 
 
625 
    unreconciledOrders = notReconciled.keys()
626 
 
627 
    for offer in db.homeshopOrderAffiliateInfo.find({"reconciled":{"$exists":False},
628 
                                                     "transactionTime":{"$gt":int(time.mktime(last30Days.timetuple())),
629 
                                                                 "$lt":int(time.mktime(date.today().timetuple()))}} ):
630 
        row += 1
631 
        i=-1
632 
        affNotReconciledSheet.write(row, inc(), offer.get("uniqueKey"))
633 
        affNotReconciledSheet.write(row, inc(), offer.get("subTagId"))
634 
        affNotReconciledSheet.write(row, inc(), datetime.strftime(utils.fromTimeStamp(offer.get("transactionTime")), "%d %b %Y %I:%M %p"))
635 
        affNotReconciledSheet.write(row, inc(), offer.get("transactionValue"))
636 
        affNotReconciledSheet.write(row, inc(), offer.get("payOut"))
637 
        affNotReconciledSheet.write(row, inc(), offer.get("status"))
638 
        affNotReconciledSheet.write(row, inc(), 'NA')
639 
 
640 
    workbook.save(XLS_HS_FILENAME)
641 
 
16995 amit.gupta 642 
 
15791 manish.sha 643 
def generateShopcluesReco():
644 
    global i
16238 manish.sha 645 
    curs = getOrdersAfterDate(datetime.now() - timedelta(days=30), 5)
15791 manish.sha 646 
    db = client.Dtr
647 
    notReconciled = {}
648 
    matchedList = []
649 
    workbook = xlwt.Workbook()
650 
    worksheet = workbook.add_sheet("Shopclues Reconciled")
651 
    worksheet1 = workbook.add_sheet("Shopclues Reconciled All")
652 
    worksheet2 = workbook.add_sheet("Shopclues Orders not reconciled")
653 
    affNotReconciledSheet = workbook.add_sheet("Shopclues Aff not reconciled")
654 
    setHeaders(worksheet, worksheet1, worksheet2, affNotReconciledSheet)
655 
    row = 0
656 
    anotherrow = 0
657 
    row2 = 0
658 
    row5=0
659 
 
660 
    for row1 in curs:
661 
        orderId = row1[0]
662 
        order = db.merchantOrder.find_one({"orderId":orderId})
663 
        if order is None:
664 
            continue
665 
        #saleTime = int(time.mktime(datetime.strptime(order['placedOn'], "%b %d, %Y, %I:%M %p").timetuple()))  26 May 2015 02:12 PM
16204 manish.sha 666 
        aff = list(db.shopcluesOrderAffiliateInfo.find({"merchantOrderId":order["merchantOrderId"]}))
15791 manish.sha 667 
        anotherrow += 1
668 
        try:
669 
            formattedTimestamp = order['placedOn']
670 
            timestamp1 = int(time.mktime(datetime.strptime(order['placedOn'], "%d %b %Y %I:%M %p").timetuple()))
671 
        except:
672 
            timestamp1 = int(time.mktime(row1[-4].timetuple()))
16947 amit.gupta 673 
            formattedTimestamp = datetime.strftime(utils.fromTimeStamp(timestamp1), "%d %b %Y %I:%M %p")
15791 manish.sha 674 
 
675 
        if len(aff) > 0 and order["subTagId"] not in matchedList and long(order["paidAmount"])==long(float(aff[0]["transactionValue"])):
676 
            matchedList.append(order["subTagId"])
16204 manish.sha 677 
            db.shopcluesOrderAffiliateInfo.update({"merchantOrderId":aff[0].get("merchantOrderId")}, {"$set":{"reconciled":True, "orderId":orderId}}, multi=True)
15791 manish.sha 678 
            order['reconciled'] = True
679 
            order['adId'] = aff[0].get("uniqueKey")
680 
            db.merchantOrder.save(order)
681 
            row += 1
682 
            i=-1
14450 amit.gupta 683 
 
15791 manish.sha 684 
            worksheet.write(row, inc(), orderId)
685 
            worksheet1.write(anotherrow, i, orderId)
686 
            worksheet.write(row, inc(), row1[1])
687 
            worksheet1.write(anotherrow, i, row1[1])
688 
            worksheet.write(row, inc(), row1[-1])
689 
            worksheet1.write(anotherrow, i, row1[-1])
690 
            worksheet.write(row, inc(), row1[-2])
691 
            worksheet1.write(anotherrow, i, row1[-2])
692 
            worksheet.write(row, inc(), row1[-3])
693 
            worksheet1.write(anotherrow, i, row1[-3])
694 
            worksheet.write(row, inc(), order['merchantOrderId'])
695 
            worksheet1.write(anotherrow, i, order['merchantOrderId'])
696 
            worksheet.write(row, inc(), order['subTagId'])
697 
            worksheet1.write(anotherrow, i, order['subTagId'])
698 
            worksheet.write(row, inc(), order['placedOn'])
699 
            worksheet1.write(anotherrow, i, order['placedOn'])
700 
            worksheet.write(row, inc(), formattedTimestamp)
701 
            worksheet1.write(anotherrow, i, formattedTimestamp)
702 
            worksheet.write(row, inc(), int(order['paidAmount']))
703 
            worksheet1.write(anotherrow, i, int(order['paidAmount']))
16947 amit.gupta 704 
            worksheet.write(row, inc(), datetime.strftime(utils.fromTimeStamp(aff[0]['transactionTime']), "%d %b %Y %I:%M %p"))
705 
            worksheet1.write(anotherrow, i, datetime.strftime(utils.fromTimeStamp(aff[0]['transactionTime']), "%d %b %Y %I:%M %p"))
15791 manish.sha 706 
            worksheet.write(row, inc(), aff[0]['transactionValue'])
707 
            worksheet1.write(anotherrow, i, aff[0]['transactionValue'])
708 
            worksheet.write(row, inc(), aff[0]['payOut'])
709 
            worksheet1.write(anotherrow, i, aff[0]['payOut'])
16238 manish.sha 710 
            worksheet.write(row, inc(), "NA")
711 
            worksheet1.write(anotherrow, i, "NA")
15791 manish.sha 712 
            k = i
713 
            row -= 1
714 
            anotherrow -= 1
715 
            for subOrder in order['subOrders']:
716 
                i = k
717 
                row += 1
718 
                anotherrow += 1
719 
                worksheet.write(row, inc(), subOrder['productTitle'])
720 
                worksheet1.write(anotherrow, i, subOrder['productTitle'])
721 
                worksheet.write(row, inc(), subOrder['amountPaid'])
722 
                worksheet1.write(anotherrow, i, subOrder['amountPaid'])
723 
                worksheet.write(row, inc(), subOrder['quantity'])
724 
                worksheet1.write(anotherrow, i, subOrder['quantity'])
725 
                worksheet.write(row, inc(), subOrder['status'])
726 
                worksheet1.write(anotherrow, i, subOrder['status'])
727 
                worksheet.write(row, inc(), subOrder['cashBackStatus'])
728 
                worksheet1.write(anotherrow, i, subOrder['cashBackStatus'])
729 
                worksheet.write(row, inc(), subOrder['cashBackAmount'])
730 
                worksheet1.write(anotherrow, i, subOrder['cashBackAmount'])
731 
 
732 
        else:
733 
            i=-1
734 
            worksheet1.write(anotherrow, inc(), orderId)
735 
            worksheet1.write(anotherrow, inc(), row1[1])
736 
            worksheet1.write(anotherrow, inc(), row1[-1])
737 
            worksheet1.write(anotherrow, inc(), row1[-2])
738 
            worksheet1.write(anotherrow, inc(), row1[-3])
739 
            worksheet1.write(anotherrow, inc(), order['merchantOrderId'])
740 
            worksheet1.write(anotherrow, inc(), order['subTagId'])
741 
            worksheet1.write(anotherrow, inc(), order['placedOn'])
742 
            worksheet1.write(anotherrow, inc(), formattedTimestamp)
743 
            worksheet1.write(anotherrow, inc(), int(order['paidAmount']))
744 
 
745 
            notReconciled[orderId] = (anotherrow, i, int(order['paidAmount']), timestamp1, order["subTagId"])
746 
            inc()
747 
            inc()
748 
            inc()
749 
            inc()
750 
            k = i
751 
            anotherrow -= 1
752 
            for subOrder in order['subOrders']:
753 
                anotherrow += 1
754 
                i = k
755 
                worksheet1.write(anotherrow, inc(), subOrder['productTitle'])
756 
                worksheet1.write(anotherrow, inc(), subOrder['amountPaid'])
757 
                worksheet1.write(anotherrow, inc(), subOrder['quantity'])
758 
                worksheet1.write(anotherrow, inc(), subOrder['status'])
759 
                worksheet1.write(anotherrow, inc(), subOrder['cashBackStatus'])
760 
                worksheet1.write(anotherrow, inc(), subOrder['cashBackAmount'])
761 
            row5 += 1
762 
            i=-1
763 
            worksheet2.write(row5, inc(), order["orderId"])
764 
            worksheet2.write(row5, inc(), row1[1])
765 
            worksheet2.write(row5, inc(), row1[-1])
766 
            worksheet2.write(row5, inc(), row1[-2])
767 
            worksheet2.write(row5, inc(), row1[-3])
768 
            worksheet2.write(row5, inc(), order['merchantOrderId'])
769 
            worksheet2.write(row5, inc(), order['subTagId'])
770 
            worksheet2.write(row5, inc(), order['placedOn'])
771 
            worksheet2.write(row5, inc(), formattedTimestamp)
772 
            worksheet2.write(row5, inc(), int(order['paidAmount']))
773 
            row5 -=1
774 
            k=i
775 
            for subOrder in order['subOrders']:
776 
                i = k
777 
                row5 += 1
778 
                worksheet2.write(row5, inc(), subOrder['productTitle'])
779 
                worksheet2.write(row5, inc(), subOrder['amountPaid'])
780 
                worksheet2.write(row5, inc(), subOrder['quantity'])
781 
                worksheet2.write(row5, inc(), subOrder['status'])
782 
                worksheet2.write(row5, inc(), subOrder['cashBackStatus'])
783 
                worksheet2.write(row5, inc(), subOrder['cashBackAmount'])
784 
 
785 
    last30Days =  date.today() - timedelta(days=30)
786 
    int(time.mktime(last30Days.timetuple()))
787 
    row = 0
788 
    for offer in db.shopcluesOrderAffiliateInfo.find({"reconciled":{"$exists":False},
789 
                                                     "transactionTime":{"$gt":int(time.mktime(last30Days.timetuple())),
790 
                                                                 "$lt":int(time.mktime(date.today().timetuple()))}} ):
791 
        subTagId = offer.get("subTagId")
792 
        saleTime = offer.get("transactionTime")
15794 manish.sha 793 
        orders = getOrdersByTag(subTagId, 5)
15791 manish.sha 794 
        for order in orders:
795 
            if notReconciled.has_key(order[0]):
796 
                (roww, column, paidAmount, timestamp1, oSubTagId) = notReconciled[order[0]]
797 
                if paidAmount==int(offer.get("transactionValue")) and timestamp1 <= saleTime and timestamp1 + 120 >= saleTime:
798 
                    i = column
16947 amit.gupta 799 
                    worksheet1.write(roww, inc(), datetime.strftime(utils.fromTimeStamp(offer.get("transactionTime")), "%d %b %Y %I:%M %p"))
15791 manish.sha 800 
                    worksheet1.write(roww, inc(), offer.get("transactionValue"))
801 
                    worksheet1.write(roww, inc(), offer.get("payOut"))
802 
                    del notReconciled[order[0]]
803 
                    print "found match for user", order[1]
804 
                    db.shopcluesOrderAffiliateInfo.update({"transactionId":offer.get("transactionId")}, {"$set":{"reconciled":True, "orderId":order[0]}}, multi=True)
805 
                    db.merchantOrder.update({"orderId":order[0]}, {"$set":{"reconciled":True, "adId":offer.get("uniqueKey")}}, multi=True)
806 
                    break
807 
 
808 
    unreconciledOrders = notReconciled.keys()
809 
 
810 
    for offer in db.shopcluesOrderAffiliateInfo.find({"reconciled":{"$exists":False},
811 
                                                     "transactionTime":{"$gt":int(time.mktime(last30Days.timetuple())),
812 
                                                                 "$lt":int(time.mktime(date.today().timetuple()))}} ):
813 
        row += 1
814 
        i=-1
815 
        affNotReconciledSheet.write(row, inc(), offer.get("uniqueKey"))
816 
        affNotReconciledSheet.write(row, inc(), offer.get("subTagId"))
16947 amit.gupta 817 
        affNotReconciledSheet.write(row, inc(), datetime.strftime(utils.fromTimeStamp(offer.get("transactionTime")), "%d %b %Y %I:%M %p"))
15791 manish.sha 818 
        affNotReconciledSheet.write(row, inc(), offer.get("transactionValue"))
819 
        affNotReconciledSheet.write(row, inc(), offer.get("payOut"))
820 
        affNotReconciledSheet.write(row, inc(), offer.get("status"))
821 
        affNotReconciledSheet.write(row, inc(), 'NA')
822 
 
823 
    workbook.save(XLS_SC_FILENAME)
824 
 
14647 amit.gupta 825 
def sendmail(email, message, title, *varargs):
14450 amit.gupta 826 
    if email == "":
827 
        return
828 
    mailServer = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)
829 
    mailServer.ehlo()
830 
    mailServer.starttls()
831 
    mailServer.ehlo()
832 
 
833 
    # Create the container (outer) email message.
834 
    msg = MIMEMultipart()
835 
    msg['Subject'] = title
836 
    msg.preamble = title
837 
    html_msg = MIMEText(message, 'html')
838 
    msg.attach(html_msg)
839 
 
840 
    #snapdeal more to be added here
14647 amit.gupta 841 
    for fileName in varargs:
842 
        snapdeal = MIMEBase('application', 'vnd.ms-excel')
843 
        snapdeal.set_payload(file(fileName).read())
844 
        encoders.encode_base64(snapdeal)
845 
        snapdeal.add_header('Content-Disposition', 'attachment;filename=' + fileName)
846 
        msg.attach(snapdeal)
14450 amit.gupta 847 
 
848 
 
849 
    email.append('amit.gupta@shop2020.in')
850 
    MAILTO = email
851 
    mailServer.login(SENDER, PASSWORD)
852 
    mailServer.sendmail(PASSWORD, MAILTO, msg.as_string())
853 
 
16958 amit.gupta 854 
def setHeaders(worksheet, worksheet1, worksheet2, affNotReconciledSheet, worksheet3=None):
14505 amit.gupta 855 
    boldStyle = xlwt.XFStyle()
856 
    f = xlwt.Font()
857 
    f.bold = True
858 
    boldStyle.font = f
14501 amit.gupta 859 
    row = 0
860 
    global i
861 
    i=-1
862 
    worksheet.write(row, inc(), 'Order Id', boldStyle)
863 
    worksheet.write(row, inc(), 'User Id', boldStyle)
14850 amit.gupta 864 
    worksheet.write(row, inc(), 'Username', boldStyle)
14601 amit.gupta 865 
    worksheet.write(row, inc(), 'Version Code', boldStyle)
14771 amit.gupta 866 
    worksheet.write(row, inc(), 'Device', boldStyle)
14501 amit.gupta 867 
    worksheet.write(row, inc(), 'Merchant Order Id', boldStyle)
868 
    worksheet.write(row, inc(), 'SubtagId', boldStyle)
869 
    worksheet.write(row, inc(), 'Sale Date', boldStyle)
14988 amit.gupta 870 
    worksheet.write(row, inc(), 'Dtr Sale Date', boldStyle)
14501 amit.gupta 871 
    worksheet.write(row, inc(), 'Sale Amount', boldStyle)
872 
    worksheet.write(row, inc(), 'Aff Sale Date', boldStyle)
873 
    worksheet.write(row, inc(), 'Aff Sale Amt', boldStyle)
874 
    worksheet.write(row, inc(), 'Aff PayOut', boldStyle)
14601 amit.gupta 875 
    worksheet.write(row, inc(), 'IP', boldStyle)
16718 amit.gupta 876 
    worksheet.write(row, inc(), 'Aff Status', boldStyle)
14501 amit.gupta 877 
    worksheet.write(row, inc(), 'Product Title', boldStyle)
878 
    worksheet.write(row, inc(), 'Price', boldStyle)
879 
    worksheet.write(row, inc(), 'Quantity', boldStyle)
880 
    worksheet.write(row, inc(), 'Status', boldStyle)
881 
    worksheet.write(row, inc(), 'Cashback Status', boldStyle)
882 
    worksheet.write(row, inc(), 'CashBack', boldStyle)
883 
 
14505 amit.gupta 884 
    i = -1
14501 amit.gupta 885 
    worksheet1.write(row, inc(), 'Order Id', boldStyle)
886 
    worksheet1.write(row, inc(), 'User Id', boldStyle)
14850 amit.gupta 887 
    worksheet1.write(row, inc(), 'Username', boldStyle)
14601 amit.gupta 888 
    worksheet1.write(row, inc(), 'Version Code', boldStyle)
14771 amit.gupta 889 
    worksheet1.write(row, inc(), 'Device', boldStyle)
14501 amit.gupta 890 
    worksheet1.write(row, inc(), 'Merchant Order Id', boldStyle)
891 
    worksheet1.write(row, inc(), 'SubtagId', boldStyle)
892 
    worksheet1.write(row, inc(), 'Sale Date', boldStyle)
14988 amit.gupta 893 
    worksheet1.write(row, inc(), 'Dtr Sale Date', boldStyle)
14501 amit.gupta 894 
    worksheet1.write(row, inc(), 'Sale Amount', boldStyle)
895 
    worksheet1.write(row, inc(), 'Aff Sale Date', boldStyle)
896 
    worksheet1.write(row, inc(), 'Aff Sale Amt', boldStyle)
897 
    worksheet1.write(row, inc(), 'Aff PayOut', boldStyle)
14601 amit.gupta 898 
    worksheet1.write(row, inc(), 'IP', boldStyle)
16718 amit.gupta 899 
    worksheet1.write(row, inc(), 'Aff Status', boldStyle)
14501 amit.gupta 900 
    worksheet1.write(row, inc(), 'Product Title', boldStyle)
901 
    worksheet1.write(row, inc(), 'Price', boldStyle)
902 
    worksheet1.write(row, inc(), 'Quantity', boldStyle)
903 
    worksheet1.write(row, inc(), 'Status', boldStyle)
904 
    worksheet1.write(row, inc(), 'Cashback Status', boldStyle)
905 
    worksheet1.write(row, inc(), 'CashBack', boldStyle)
14508 amit.gupta 906 
 
907 
    i = -1
908 
    worksheet2.write(row, inc(), 'Order Id', boldStyle)
909 
    worksheet2.write(row, inc(), 'User Id', boldStyle)
14850 amit.gupta 910 
    worksheet2.write(row, inc(), 'Username', boldStyle)
14605 amit.gupta 911 
    worksheet2.write(row, inc(), 'Version Code', boldStyle)
14771 amit.gupta 912 
    worksheet2.write(row, inc(), 'Device', boldStyle)
14508 amit.gupta 913 
    worksheet2.write(row, inc(), 'Merchant Order Id', boldStyle)
914 
    worksheet2.write(row, inc(), 'SubtagId', boldStyle)
915 
    worksheet2.write(row, inc(), 'Sale Date', boldStyle)
14988 amit.gupta 916 
    worksheet2.write(row, inc(), 'Dtr Sale Date', boldStyle)
14508 amit.gupta 917 
    worksheet2.write(row, inc(), 'Sale Amount', boldStyle)
918 
    worksheet2.write(row, inc(), 'Product Title', boldStyle)
919 
    worksheet2.write(row, inc(), 'Price', boldStyle)
920 
    worksheet2.write(row, inc(), 'Quantity', boldStyle)
921 
    worksheet2.write(row, inc(), 'Status', boldStyle)
922 
    worksheet2.write(row, inc(), 'Cashback Status', boldStyle)
923 
    worksheet2.write(row, inc(), 'CashBack', boldStyle)
14450 amit.gupta 924 
 
14508 amit.gupta 925 
    i =-1
926 
    affNotReconciledSheet.write(row, inc(), 'AdId', boldStyle)
927 
    affNotReconciledSheet.write(row, inc(), 'subTagId', boldStyle)
928 
    affNotReconciledSheet.write(row, inc(), 'Sale Date', boldStyle)
929 
    affNotReconciledSheet.write(row, inc(), 'Sale Amount', boldStyle)
930 
    affNotReconciledSheet.write(row, inc(), 'Pay Out', boldStyle)
931 
    affNotReconciledSheet.write(row, inc(), 'Status', boldStyle)
14601 amit.gupta 932 
    affNotReconciledSheet.write(row, inc(), 'IP', boldStyle)
14524 amit.gupta 933 
 
16958 amit.gupta 934 
    if worksheet3:
935 
        i =-1
936 
        worksheet3.write(row, inc(), 'Date', boldStyle)
937 
        worksheet3.write(row, inc(), 'Sale Amount', boldStyle)
938 
        worksheet3.write(row, inc(), 'Approved Sales', boldStyle)
939 
        worksheet3.write(row, inc(), 'Rejected Sales', boldStyle)
16947 amit.gupta 940 
 
14524 amit.gupta 941 
def getUserAmountReconciled():
942 
    pass
943 
 
14613 amit.gupta 944 
def getUserOrders():
945 
    global i
946 
    rb = open_workbook(XLS_FILENAME,formatting_info=True)
947 
    wb = copy(rb)
948 
    worksheet = wb.add_sheet("All Users")
16726 amit.gupta 949 
    #worksheet1 = wb.add_sheet("Suspected Users")
17011 amit.gupta 950 
    workbook = xlwt.Workbook("utf8")
14632 amit.gupta 951 
    sh1 = workbook.add_sheet("All Orders")
15423 amit.gupta 952 
    sh2 = workbook.add_sheet("Flipkart Orders")
14632 amit.gupta 953 
 
14613 amit.gupta 954 
    db=client.Dtr
17137 naman 955 
#     results = Mysql.fetchResult('''
956 
#         select ow.*, u.username, crm1.order_count, u.referrer, aua.city,aua.state,aua.pincode from order_view ow
957 
#         left join users u on u.id = ow.user_id
958 
#         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
959 
#         left join (select * from (select * from all_user_addresses order by source) s group by user_id)aua on aua.user_id=u.id
960 
#         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');
961 
#
962 
#         ''',)
17140 naman 963 
#     con = MySQLdb.connect('localhost','root','shop2020','dtr')
964 
#     cur = con.cursor()
965 
#     cur.execute("select * from allorder")
966 
#     results = cur.fetchall()
967 
    results = Mysql.fetchResult("select * from allorder")
14613 amit.gupta 968 
    row = 0
969 
    i=-1
970 
    worksheet.write(row, inc(), 'User Id', boldStyle)
16726 amit.gupta 971 
    #worksheet1.write(row, i, 'User Id', boldStyle)
14613 amit.gupta 972 
    worksheet.write(row, inc(), 'User name', boldStyle)
16726 amit.gupta 973 
    #worksheet1.write(row, i, 'User name', boldStyle)
14613 amit.gupta 974 
    worksheet.write(row, inc(), 'Order Id', boldStyle)
16726 amit.gupta 975 
    #worksheet1.write(row, i, 'Order Id', boldStyle)
14613 amit.gupta 976 
    worksheet.write(row, inc(), 'Created On', boldStyle)
16726 amit.gupta 977 
    #worksheet1.write(row, i, 'Created On', boldStyle)
14613 amit.gupta 978 
    worksheet.write(row, inc(), 'Store Id', boldStyle)
16726 amit.gupta 979 
    #worksheet1.write(row, i, 'Store Id', boldStyle)
14613 amit.gupta 980 
    worksheet.write(row, inc(), 'Merchant Order Id', boldStyle)
16785 amit.gupta 981 
    worksheet.write(row, inc(), 'SubOrder Id', boldStyle)
16726 amit.gupta 982 
    #worksheet1.write(row, i, 'Merchant Order Id', boldStyle)
14616 amit.gupta 983 
    worksheet.write(row, inc(), 'Product title', boldStyle)
16726 amit.gupta 984 
    #worksheet1.write(row, i, 'Product title', boldStyle)
14613 amit.gupta 985 
    worksheet.write(row, inc(), 'Amount Paid', boldStyle)
16726 amit.gupta 986 
    #worksheet1.write(row, i, 'Amount Paid', boldStyle)
14613 amit.gupta 987 
    worksheet.write(row, inc(), 'Cashback', boldStyle)
16726 amit.gupta 988 
    #worksheet1.write(row, i, 'Cashback', boldStyle)
14613 amit.gupta 989 
    worksheet.write(row, inc(), 'Order Status', boldStyle)
16726 amit.gupta 990 
    #worksheet1.write(row, i, 'Order Status', boldStyle)
14613 amit.gupta 991 
    worksheet.write(row, inc(), 'Detailed Status', boldStyle)
16726 amit.gupta 992 
    #worksheet1.write(row, i, 'Detailed Status', boldStyle)
14613 amit.gupta 993 
    worksheet.write(row, inc(), 'Cashback Status', boldStyle)
16726 amit.gupta 994 
    #worksheet1.write(row, i, 'Cashback Status', boldStyle)
14613 amit.gupta 995 
    worksheet.write(row, inc(), 'Reconciled', boldStyle)
16726 amit.gupta 996 
    #worksheet1.write(row, i, 'Reconciled', boldStyle)
14613 amit.gupta 997 
    worksheet.write(row, inc(), 'IP', boldStyle)
16726 amit.gupta 998 
    #worksheet1.write(row, i, 'IP', boldStyle)
16718 amit.gupta 999 
    worksheet.write(row, inc(), 'Aff Status', boldStyle)
16726 amit.gupta 1000 
    #worksheet1.write(row, i, 'Aff Status', boldStyle)
14693 amit.gupta 1001 
    i=-1
1002 
    sh1.write(row, inc(), 'User Id', boldStyle)
1003 
    sh1.write(row, inc(), 'User name', boldStyle)
1004 
    sh1.write(row, inc(), 'Order Id', boldStyle)
1005 
    sh1.write(row, inc(), 'Created On', boldStyle)
1006 
    sh1.write(row, inc(), 'Store Id', boldStyle)
1007 
    sh1.write(row, inc(), 'Merchant Order Id', boldStyle)
14771 amit.gupta 1008 
    sh1.write(row, inc(), 'Status', boldStyle)
15932 amit.gupta 1009 
    sh1.write(row, inc(), 'Detailed Status', boldStyle)
14693 amit.gupta 1010 
    sh1.write(row, inc(), 'Product title', boldStyle)
1011 
    sh1.write(row, inc(), 'Referrer', boldStyle)
1012 
    sh1.write(row, inc(), 'Amount Paid', boldStyle)
14771 amit.gupta 1013 
    sh1.write(row, inc(), 'Catalog Id', boldStyle)
14693 amit.gupta 1014 
    sh1.write(row, inc(), 'Brand', boldStyle)
1015 
    sh1.write(row, inc(), 'Model', boldStyle)
1016 
    sh1.write(row, inc(), 'Category', boldStyle)
15932 amit.gupta 1017 
    sh1.write(row, inc(), 'Deal Rank', boldStyle)
16286 amit.gupta 1018 
    sh1.write(row, inc(), 'Max Nlc', boldStyle)
1019 
    sh1.write(row, inc(), 'Min Nlc', boldStyle)
1020 
    sh1.write(row, inc(), 'DP', boldStyle)
1021 
    sh1.write(row, inc(), 'Item status', boldStyle)
15932 amit.gupta 1022 
    sh1.write(row, inc(), 'City', boldStyle)
1023 
    sh1.write(row, inc(), 'State', boldStyle)
1024 
    sh1.write(row, inc(), 'Pincode', boldStyle)
15423 amit.gupta 1025 
    i =-1
1026 
    sh2.write(row, inc(), 'User Id', boldStyle)
1027 
    sh2.write(row, inc(), 'User name', boldStyle)
1028 
    sh2.write(row, inc(), 'Created On', boldStyle)
1029 
    sh2.write(row, inc(), 'Store', boldStyle)
1030 
    sh2.write(row, inc(), 'Status', boldStyle)
1031 
    sh2.write(row, inc(), 'Product title', boldStyle)
1032 
    sh2.write(row, inc(), 'Price', boldStyle)
1033 
    sh2.write(row, inc(), 'Quantity', boldStyle)
1034 
    sh2.write(row, inc(), 'Catalog Id', boldStyle)
1035 
    sh2.write(row, inc(), 'Brand', boldStyle)
1036 
    sh2.write(row, inc(), 'Model', boldStyle)
1037 
    sh2.write(row, inc(), 'Category', boldStyle)
14613 amit.gupta 1038 
 
1039 
    row=0
1040 
    row2=0
14632 amit.gupta 1041 
    row3=0
1042 
    db1 = client.Catalog
14613 amit.gupta 1043 
    for result in results:
17137 naman 1044 
        morder = db.merchantOrder.find_one({"orderId":result[2]})
14613 amit.gupta 1045 
        if morder is not None:
1046 
            subOrders = morder.get("subOrders")
1047 
            if subOrders is not None:
1048 
                for  subOrder in subOrders:
14632 amit.gupta 1049 
                    row3+=1
1050 
                    i=-1
17137 naman 1051 
#                     sh1.write(row3, inc(), result[1])
1052 
#                     sh1.write(row3, inc(), result[7])
1053 
#                     sh1.write(row3, inc(), result[0])
1054 
#                     sh1.write(row3, inc(), result[6], datetime_format)
1055 
#                     sh1.write(row3, inc(), sourceMap.get(result[2]))
1056 
#                     sh1.write(row3, inc(), morder.get("merchantOrderId"))
1057 
#                     sh1.write(row3, inc(), subOrder.get("status"))
1058 
#                     sh1.write(row3, inc(), subOrder.get("detailedStatus"))
1059 
#                     sh1.write(row3, inc(), subOrder.get("productTitle"))
1060 
#                     sh1.write(row3, inc(), result[9])
1061 
                    sh1.write(row3, inc(), result[0])
14699 amit.gupta 1062 
                    sh1.write(row3, inc(), result[1])
17137 naman 1063 
                    sh1.write(row3, inc(), result[2])
1064 
                    sh1.write(row3, inc(), result[3])
1065 
                    sh1.write(row3, inc(), result[4])
1066 
                    sh1.write(row3, inc(), result[5])
1067 
                    sh1.write(row3, inc(), result[6])
15932 amit.gupta 1068 
                    sh1.write(row3, inc(), result[7])
17137 naman 1069 
                    sh1.write(row3, inc(), result[8])
15932 amit.gupta 1070 
                    sh1.write(row3, inc(), result[9])
17137 naman 1071 
                    sh1.write(row3, inc(), result[10])
14632 amit.gupta 1072 
                    inc()
17137 naman 1073 
#                     if int(subOrder.get("quantity")) > 0:
1074 
#                         sh1.write(row3, i, int(subOrder.get("amountPaid"))/int(subOrder.get("quantity")))
1075 
#                     skuData = getSkuData(morder.get("storeId"), subOrder.get("productCode"))
1076 
#                     if morder.get("storeId") in (1,2,4,5,6):
1077 
#                         skuData = db1.MasterData.find_one({'identifier':subOrder.get("productCode").strip(), 'source_id':morder.get("storeId")})
1078 
#                     elif morder.get("storeId") == 3:
1079 
#                         skuData = db1.MasterData.find_one({'secondaryIdentifier':subOrder.get("productCode").strip(), 'source_id':morder.get("storeId")})
1080 
#                     if skuData is not None:
1081 
#                         sh1.write(row3, inc(), skuData.get("skuBundleId"))
1082 
#                         sh1.write(row3, inc(), skuData.get("brand"))
1083 
#                         sh1.write(row3, inc(), skuData.get("model_name"))
1084 
#                         sh1.write(row3, inc(), skuData.get("category"))
1085 
#                         sh1.write(row3, inc(), subOrder.get("dealRank"))
1086 
#                         sh1.write(row3, inc(), subOrder.get("maxNlc"))
1087 
#                         sh1.write(row3, inc(), subOrder.get("minNlc"))
1088 
#                         sh1.write(row3, inc(), utils.statusMap.get(subOrder.get("db")))
1089 
#
1090 
#                         sh1.write(row3, inc(), subOrder.get("itemStatus"))
1091 
#                     else:
1092 
#                         sh1.write(row3, inc(), None)
1093 
#                         sh1.write(row3, inc(), None)
1094 
#                         sh1.write(row3, inc(), None)
1095 
#                         sh1.write(row3, inc(), None)
1096 
#                         sh1.write(row3, inc(), None)
1097 
#                         sh1.write(row3, inc(), None)
1098 
#                         sh1.write(row3, inc(), None)
1099 
#                         sh1.write(row3, inc(), None)
1100 
#                         sh1.write(row3, inc(), None)
15932 amit.gupta 1101 
                    sh1.write(row3, inc(), result[11])
1102 
                    sh1.write(row3, inc(), result[12])
17137 naman 1103 
                    sh1.write(row3, inc(), result[13])
1104 
                    sh1.write(row3, inc(), result[14])
1105 
                    sh1.write(row3, inc(), result[15])
1106 
                    sh1.write(row3, inc(), result[16])
1107 
                    sh1.write(row3, inc(), result[17])
1108 
                    sh1.write(row3, inc(), result[18])
1109 
                    sh1.write(row3, inc(), result[19])
1110 
#                     print result[10]
1111 
#                     sh1.write(row3, inc(), unicode(result[10], errors='ignore') if result[10] else None)
1112 
#                     sh1.write(row3, inc(), result[11])
1113 
#                     sh1.write(row3, inc(), result[12])
17141 amit.gupta 1114 
 
1115 
                    sh1.write(row3, inc(), unicode(result[20], errors='ignore') if result[20] else None)
17137 naman 1116 
                    sh1.write(row3, inc(), result[21])
1117 
                    sh1.write(row3, inc(), result[22])
15932 amit.gupta 1118 
 
14881 amit.gupta 1119 
                    #removed filter as requested by Rajneesh
1120 
                    #if subOrder.get("cashBackStatus") == Store.CB_APPROVED or subOrder.get("cashBackStatus") == Store.CB_PENDING:
1121 
                    row +=1
1122 
                    i=-1
17137 naman 1123 
#                     worksheet.write(row, inc(), result[0])
1124 
#                     worksheet.write(row, inc(), result[7])
1125 
#                     worksheet.write(row, inc(), result[1])
1126 
#                     worksheet.write(row, inc(), result[6], datetime_format)
1127 
#                     worksheet.write(row, inc(), result[2])
1128 
#                     worksheet.write(row, inc(), morder.get("merchantOrderId"))
1129 
#                     worksheet.write(row, inc(), subOrder.get("merchantSubOrderId"))
1130 
#                     worksheet.write(row, inc(), subOrder.get("productTitle"))
1131 
#                     worksheet.write(row, inc(), subOrder.get("amountPaid"))
1132 
#                     worksheet.write(row, inc(), subOrder.get("cashBackAmount"))
1133 
#                     worksheet.write(row, inc(), subOrder.get("status"))
1134 
#                     worksheet.write(row, inc(), subOrder.get("detailedStatus"))
1135 
#                     worksheet.write(row, inc(), subOrder.get("cashBackStatus"))
1136 
 
1137 
                    worksheet.write(row, inc(), result[2])
1138 
                    worksheet.write(row, inc(), result[1])
14881 amit.gupta 1139 
                    worksheet.write(row, inc(), result[0])
17137 naman 1140 
                    worksheet.write(row, inc(), result[3])
1141 
                    worksheet.write(row, inc(), result[4])
1142 
                    worksheet.write(row, inc(), result[5])
1143 
                    worksheet.write(row, inc(), result[23])
1144 
                    worksheet.write(row, inc(), result[8])
1145 
                    worksheet.write(row, inc(), result[10])
1146 
                    worksheet.write(row, inc(), result[25])
1147 
                    worksheet.write(row, inc(), result[6])
15932 amit.gupta 1148 
                    worksheet.write(row, inc(), result[7])
17137 naman 1149 
                    worksheet.write(row, inc(), result[24])
1150 
                    worksheet.write(row, inc(), result[8])
1151 
 
1152 
                    #worksheet.write(row, inc(), False if morder.get("reconciled") is None else True)
1153 
                    #if morder.get("reconciled") and morder.get("storeId")==3:
1154 
                    #    try:
1155 
                    #        snapdealafinfo = db.snapdealOrderAffiliateInfo.find_one({"adId":morder.get("adId"), "reconciled":True})
1156 
                    #        worksheet.write(row, inc(), snapdealafinfo.get("ip"))
1157 
                    #        worksheet.write(row, inc(), snapdealafinfo.get("conversionStatus"))
1158 
                    #    except:
1159 
                    #        print "Could not find", morder.get("adId"), "that is reconciled for order", morder.get("orderId")
16726 amit.gupta 1160 
                    #if result[8] >= 10:
1161 
                    #    row2 +=1
1162 
                    #    i=-1
1163 
                        #worksheet1.write(row2, inc(), result[0])
1164 
                        #worksheet1.write(row2, inc(), result[7])
1165 
                        #worksheet1.write(row2, inc(), result[1])
1166 
                        #worksheet1.write(row2, inc(), result[6], datetime_format)
1167 
                        #worksheet1.write(row2, inc(), result[2])
1168 
                        #worksheet1.write(row2, inc(), morder.get("merchantOrderId"))
1169 
                        #worksheet1.write(row2, inc(), subOrder.get("productTitle"))
1170 
                        #worksheet1.write(row2, inc(), subOrder.get("amountPaid"))
1171 
                        #worksheet1.write(row2, inc(), subOrder.get("cashBackAmount"))
1172 
                        #worksheet1.write(row2, inc(), subOrder.get("status"))
1173 
                        #worksheet1.write(row2, inc(), subOrder.get("detailedStatus"))
1174 
                        #worksheet1.write(row2, inc(), subOrder.get("cashBackStatus"))
1175 
                        #worksheet1.write(row2, inc(), False if morder.get("reconciled") is None else True)
1176 
                        #worksheet1.write(row2, inc(), )
1177 
                        #if morder.get("reconciled") and morder.get("storeId")==3:
1178 
                        #    try:
1179 
                        #        snapdealafinfo = db.snapdealOrderAffiliateInfo.find_one({"adId":morder.get("adId"), "reconciled":True})
1180 
                        #        worksheet1.write(row2, inc(), snapdealafinfo.get("ip"))
1181 
                        #        worksheet1.write(row2, inc(), snapdealafinfo.get("conversionStatus"))
1182 
                        #    except:
1183 
                        #        print "Could not find", morder.get("adId"), "that is reconcired for order", morder.get("orderId")
15423 amit.gupta 1184 
 
15542 amit.gupta 1185 
    flipkartOrders = session.query(FlipkartOrders).order_by(FlipkartOrders.created.desc()).all()
15540 amit.gupta 1186 
    flipkartOrders.reverse()
15423 amit.gupta 1187 
    row3=0
15430 amit.gupta 1188 
    #try:
15540 amit.gupta 1189 
    for order in flipkartOrders:
15423 amit.gupta 1190 
        row3 += 1
1191 
        i=-1
15540 amit.gupta 1192 
        sh2.write(row3, inc(), order.user_id)
1193 
        sh2.write(row3, inc(), order.email)
1194 
        sh2.write(row3, inc(), order.created, datetime_format)
15423 amit.gupta 1195 
        sh2.write(row3, inc(), "Flipkart")
15540 amit.gupta 1196 
        sh2.write(row3, inc(), order.status)
1197 
        sh2.write(row3, inc(), order.title)
1198 
        sh2.write(row3, inc(), order.price)
1199 
        sh2.write(row3, inc(), order.quantity)
1200 
        sh2.write(row3, inc(), order.catalogId)
1201 
        sh2.write(row3, inc(), order.brand)
1202 
        sh2.write(row3, inc(), order.model)
1203 
        sh2.write(row3, inc(), order.category)
14632 amit.gupta 1204 
 
14613 amit.gupta 1205 
    wb.save(XLS_FILENAME)
14632 amit.gupta 1206 
    workbook.save(XLS_O_FILENAME)
14647 amit.gupta 1207 
 
1208 
def getSkuData(storeId, identifier):
16492 amit.gupta 1209 
    if storeId in (1,2,4,5,6):
14647 amit.gupta 1210 
        skuData = client.Catalog.MasterData.find_one({'identifier':identifier, 'source_id':storeId})
1211 
    elif storeId == 3:
1212 
        skuData = client.Catalog.MasterData.find_one({'secondaryIdentifier':identifier, 'source_id':storeId})
1213 
    return skuData
1214 
 
14460 amit.gupta 1215 
def inc():
1216 
    global i
1217 
    i+=1
1218 
    return i
15540 amit.gupta 1219 
 
1220 
def migrateFlipkartOrders():
1221 
    db = client.Dtr
1222 
    info = reversed(list(db.flipkartOrderAffiliateInfo.find()))
1223 
    #try:
1224 
    for order in info:
1225 
        userId = None
1226 
        subTagId = None
1227 
        email = None
1228 
        subTagId = order.get("subTagId")
1229 
        if subTagId:
1230 
            click = session.query(Clicks).filter_by(tag = subTagId).first()
1231 
            if click is not None:
1232 
                userId= click.user_id
1233 
                user = session.query(Users.email).filter_by(id = userId).first()
1234 
                if user is not None:
1235 
                    email = user.email
1236 
 
1237 
        flipkartOrder = FlipkartOrders()
1238 
        flipkartOrder.user_id = userId
1239 
        flipkartOrder.identifier = order.get("identifier")
1240 
        flipkartOrder.email = email
1241 
        flipkartOrder.subtagId = order.get("subTagId")
1242 
        flipkartOrder.created = datetime.strptime(order.get("saleDate"), "%Y-%m-%d")
1243 
        flipkartOrder.status = order.get("conversionStatus")
1244 
        flipkartOrder.title = order.get("productTitle")
1245 
        flipkartOrder.price = order.get("price")
1246 
        flipkartOrder.quantity = order.get("quantity")
1247 
        flipkartOrder.productCode = order.get("productCode")
1248 
        skuData = getSkuData(2, order.get("productCode"))
1249 
        if skuData is not None:
1250 
            flipkartOrder.catalogId = skuData.get("skuBundleId")
1251 
            flipkartOrder.brand = skuData.get("brand")
1252 
            flipkartOrder.model = skuData.get("model_name")
1253 
            flipkartOrder.category =categoryMap.get(skuData.get("category_id"))
1254 
            flipkartOrder.title =skuData.get("source_product_name")
1255 
 
1256 
    session.commit()
16947 amit.gupta 1257 
 
1258 
 
1259 
def main1():
1260 
    db = client.Dtr
1261 
    for offer in db.snapdealOrderAffiliateInfo.find({}):
1262 
        print offer.get("adId"), offer.get("saleAmount"), type(offer.get("saleAmount"))
1263 
        #obj = offer.get(saleTime)
1264 
        #obj['paidAmount'] += order["paidAmount"]
14632 amit.gupta 1265 
def addHeaders(sheet):
1266 
    global i
1267 
    i = 0
1268 
    sheet.write(0, inc(), )
14450 amit.gupta 1269 
def main():
14647 amit.gupta 1270 
 
16995 amit.gupta 1271 
    message = generateSnapDealReco()
17137 naman 1272 
#     message ="Kuch nahi"
14647 amit.gupta 1273 
    generateFlipkartReco()
15791 manish.sha 1274 
    generateShopcluesReco()
14650 amit.gupta 1275 
    generateAmazonReco(XLS_A_FILENAME)
17137 naman 1276 
    message1 = getdata.summaryByBrandAndStore()
14613 amit.gupta 1277 
    getUserOrders()
17137 naman 1278 
    #getdata.sendmail(["naman.kumar@shop2020.in"], message1, "Summary by brand and store")
1279 
    #sendmail(["naman.kumar@shop2020.in"], message, "Affiliate Reco", XLS_FILENAME, XLS_F_FILENAME, XLS_A_FILENAME, XLS_SC_FILENAME)
1280 
    #sendmail(["naman.kumar@shop2020.in"], message1, "All DTR Orders", XLS_O_FILENAME)
1281 
    sendmail(["amit.gupta@shop2020.in", "rajneesh.arora@saholic.com", "chaitnaya.vats@saholic.com", "manoj.kumar@saholic.com", "manish.sharma@shop2020.in"], message1, "All DTR Orders", XLS_O_FILENAME)
16995 amit.gupta 1282 
    sendmail(["amit.gupta@shop2020.in","rajneesh.arora@saholic.com","manish.sharma@shop2020.in"], message, "Affiliate Reco", XLS_FILENAME, XLS_F_FILENAME, XLS_A_FILENAME, XLS_SC_FILENAME)
17001 amit.gupta 1283 
    #sendmail(["amit.gupta@shop2020.in"], "", "Affiliate Reco", XLS_FILENAME, XLS_F_FILENAME, XLS_A_FILENAME, XLS_SC_FILENAME)
1284 
 
17053 amit.gupta 1285 
def main3():
1286 
    db = client.Dtr
1287 
    #main()
1288 
    mailBodyTemplate="""
1289 
        <html>
1290 
            <body>
1291 
            <table cellspacing="0" border="1" style="text-align:right">
1292 
                <thead>
1293 
                    <tr>
1294 
                        <th style="text-align:center">Date</th>
1295 
                        <th  style="text-align:center">Sale</th>
1296 
                        <th  style="text-align:center">Approved</th>
1297 
                        <th  style="text-align:center">Approved %</th>
1298 
                        <th  style="text-align:center">Rejected</th>
1299 
                        <th  style="text-align:center">Rejected %</th>
1300 
                        <th  style="text-align:center">Missing %</th>
1301 
                        <th  style="text-align:center">Approved % against Sale</th>
1302 
                    </tr>
1303 
                </thead>
1304 
                <tbody>
1305 
                    {0}
1306 
                </tbody>
1307 
            </table>
1308 
            </body>
1309 
        </html>
1310 
    """
1311 
    tbody=[]
1312 
    rowtemplate="<tr><td>{0}</td><td>{1}</td><td>{2}</td><td>{3}</td><td>{4}</td><td>{5}</td><td>{6}</td><td>{7}</td></tr>"
1313 
    for offer in db.flSaleSnapshot.find().limit(61).sort("_id",-1):
1314 
        approved = offer.get("approvedAmount")
1315 
        rejected = offer.get("rejectedAmount")
1316 
        sale = offer.get("paidAmount")
1317 
        total = approved + rejected
1318 
        if total ==0:
1319 
            continue
1320 
        tbody.append(rowtemplate.format(offer.get("_id"), sale, approved, int((approved*100/total)), rejected, int((rejected*100/total)), int((sale-approved-rejected)*100/sale),int((approved)*100/sale)))
1321 
    message = mailBodyTemplate.format("".join(tbody))
1322 
    print message
17001 amit.gupta 1323 
def main2():
1324 
    db = client.Dtr
17053 amit.gupta 1325 
    curs = getOrdersAfterDate(datetime.now() - timedelta(days=46), 3)
17001 amit.gupta 1326 
    datemap={}
1327 
    for row1 in curs:
1328 
        orderId = row1[0]
1329 
        order = db.merchantOrder.find_one({"orderId":orderId})
1330 
        if order is None:
1331 
            continue
1332 
        #saleTime = int(time.mktime(datetime.strptime(order['placedOn'], "%b %d, %Y, %I:%M %p").timetuple()))
1333 
        try:
1334 
            saleTime = datetime.strptime(order['placedOn'], "%a, %d %b, %Y").strftime("%Y-%m-%d")
1335 
        except:
1336 
            saleTime = datetime.strptime(order['placedOn'], "%b %d, %Y, %I:%M %p").strftime("%Y-%m-%d")
1337 
        print "%s\t%s\t%s\t%s"%(orderId, order['placedOn'], saleTime, order['paidAmount'])
1338 
        if not datemap.has_key(saleTime):
1339 
            datemap[saleTime] = {"totalAmount":0, "paidAmount":0, "rejectedAmount":0, "approvedAmount":0, "count":0}
1340 
 
1341 
        obj = datemap.get(saleTime)
1342 
        obj['paidAmount'] += order["paidAmount"]
1343 
        obj['count'] += 1
1344 
 
17053 amit.gupta 1345 
    for offer in db.snapdealOrderAffiliateInfo1.find({"saleDate":{"$gte":toTimeStamp(datetime.now() - timedelta(days=46))}}):
17001 amit.gupta 1346 
        saleTime =  utils.fromTimeStamp(offer.get('saleDate')).strftime("%Y-%m-%d")
1347 
        obj = datemap.get(saleTime)
1348 
        if obj is not None:
1349 
            try:
1350 
                if offer.get("payOut"):
1351 
                    obj['approvedAmount'] += offer["saleAmount"]
1352 
                else:
1353 
                    obj['rejectedAmount'] += offer["saleAmount"]
1354 
            except:
1355 
                print "Exception for orderId" , offer.get("orderId")
1356 
        else:
1357 
            print "Could not find sale date", saleTime
1358 
            continue
1359 
 
1360 
    for key,valmap in  datemap.iteritems():
1361 
        valmap['_id'] = key
1362 
        db.flSaleSnapshot.save(valmap)
14450 amit.gupta 1363 
 
1364 
if __name__ == '__main__':
17137 naman 1365 
#     getdata.addToAllOrders(date.today()-timedelta(days=30))
1366 
#     main2()
17008 amit.gupta 1367 
    main()