Subversion Repositories SmartDukaan

Rev

Rev 17169 | Rev 17234 | 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"}
17199 amit.gupta 58 
 
59 
curDate = date.today()
60 
cutOffMonth = (datetime.now().month + 10)%12
61 
cutOffYear = curDate.year - (0 if curDate.month-2 > 0 else 1)
62 
cutOff = datetime(cutOffYear, cutOffMonth, 1)
63 
 
64 
 
14450 amit.gupta 65 
def generateFlipkartReco():
14508 amit.gupta 66 
    global i
67 
    i = -1
14460 amit.gupta 68 
    curs = getOrdersAfterDate(datetime.now() - timedelta(days=30), 2)
69 
    db = client.Dtr
14647 amit.gupta 70 
    wb = xlwt.Workbook()
71 
    #rb = open_workbook(XLS_F_FILENAME)
72 
    #wb = copy(rb)
14460 amit.gupta 73 
    #wb = xlwt.Workbook()
74 
    row = 0
14647 amit.gupta 75 
    row2 = 0
76 
    row3 = 0
77 
    row4 = 0
78 
    worksheet = wb.add_sheet("All Orders")
79 
    worksheet1 = wb.add_sheet("Orders Reconciled")
80 
    worksheet2 = wb.add_sheet("Orders not Reconciled")
81 
    worksheet3 = wb.add_sheet("Aff not Reconciled")
14460 amit.gupta 82 
    worksheet.write(row, inc(), 'Order Id', boldStyle)
14647 amit.gupta 83 
    worksheet1.write(row, i, 'Order Id', boldStyle)
84 
    worksheet2.write(row, i, 'Order Id', boldStyle)
14460 amit.gupta 85 
    worksheet.write(row, inc(), 'User Id', boldStyle)
14647 amit.gupta 86 
    worksheet1.write(row, i, 'User Id', boldStyle)
87 
    worksheet2.write(row, i, 'User Id', boldStyle)
14474 amit.gupta 88 
    worksheet.write(row, inc(), 'Username', boldStyle)
14647 amit.gupta 89 
    worksheet1.write(row, i, 'Username', boldStyle)
90 
    worksheet2.write(row, i, 'Username', boldStyle)
14771 amit.gupta 91 
    worksheet.write(row, inc(), 'Version code', boldStyle)
92 
    worksheet1.write(row, i, 'Version code', boldStyle)
93 
    worksheet2.write(row, i, 'Version code', boldStyle)
94 
    worksheet.write(row, inc(), 'Device', boldStyle)
95 
    worksheet1.write(row, i, 'Device', boldStyle)
96 
    worksheet2.write(row, i, 'Device', boldStyle)
14460 amit.gupta 97 
    worksheet.write(row, inc(), 'Merchant Order Id', boldStyle)
14647 amit.gupta 98 
    worksheet1.write(row, i, 'Merchant Order Id', boldStyle)
99 
    worksheet2.write(row, i, 'Merchant Order Id', boldStyle)
14460 amit.gupta 100 
    worksheet.write(row, inc(), 'Product Title', boldStyle)
14647 amit.gupta 101 
    worksheet1.write(row, i, 'Product Title', boldStyle)
102 
    worksheet2.write(row, i, 'Product Title', boldStyle)
14460 amit.gupta 103 
    worksheet.write(row, inc(), 'Price', boldStyle)
14647 amit.gupta 104 
    worksheet1.write(row, i, 'Price', boldStyle)
105 
    worksheet2.write(row, i, 'Price', boldStyle)
14460 amit.gupta 106 
    worksheet.write(row, inc(), 'Quantity', boldStyle)
14647 amit.gupta 107 
    worksheet1.write(row, i, 'Quantity', boldStyle)
108 
    worksheet2.write(row, i, 'Quantity', boldStyle)
14460 amit.gupta 109 
    worksheet.write(row, inc(), 'Status', boldStyle)
14647 amit.gupta 110 
    worksheet1.write(row, i, 'Status', boldStyle)
111 
    worksheet2.write(row, i, 'Status', boldStyle)
14460 amit.gupta 112 
    worksheet.write(row, inc(), 'Detailed Status', boldStyle)
14647 amit.gupta 113 
    worksheet1.write(row, i, 'Detailed Status', boldStyle)
114 
    worksheet2.write(row, i, 'Detailed Status', boldStyle)
14460 amit.gupta 115 
    worksheet.write(row, inc(), 'Cashback Status', boldStyle)
14647 amit.gupta 116 
    worksheet1.write(row, i, 'Cashback Status', boldStyle)
117 
    worksheet2.write(row, i, 'Cashback Status', boldStyle)
14460 amit.gupta 118 
    worksheet.write(row, inc(), 'Our CashBack', boldStyle)
14647 amit.gupta 119 
    worksheet1.write(row, i, 'Our CashBack', boldStyle)
120 
    worksheet2.write(row, i, 'Our CashBack', boldStyle)
121 
    worksheet.write(row, inc(), 'Sale Date', boldStyle)
122 
    worksheet1.write(row, i, 'Sale Date', boldStyle)
123 
    worksheet2.write(row, i, 'Sale Date', boldStyle)
14460 amit.gupta 124 
    worksheet.write(row, inc(), 'SubtagId', boldStyle)
14647 amit.gupta 125 
    worksheet1.write(row, i, 'SubtagId', boldStyle)
126 
    worksheet2.write(row, i, 'SubtagId', boldStyle)
14460 amit.gupta 127 
    worksheet.write(row, inc(), 'Category', boldStyle)
14647 amit.gupta 128 
    worksheet1.write(row, i, 'Category', boldStyle)
14460 amit.gupta 129 
    worksheet.write(row, inc(), 'Aff PayOut', boldStyle)
14647 amit.gupta 130 
    worksheet1.write(row, i, 'Aff PayOut', boldStyle)
14460 amit.gupta 131 
    worksheet.write(row, inc(), 'Aff Sale Amount', boldStyle)
14647 amit.gupta 132 
    worksheet1.write(row, i, 'Aff Sale Amount', boldStyle)
14460 amit.gupta 133 
    worksheet.write(row, inc(), 'Aff Sale Date', boldStyle)
14647 amit.gupta 134 
    worksheet1.write(row, i, 'Aff Sale Date', boldStyle)
14460 amit.gupta 135 
    for row1 in curs:
136 
        orderId = row1[0]
137 
        order = db.merchantOrder.find_one({"orderId":orderId})
138 
        if order is None:
139 
            continue
140 
        saleTime = int(time.mktime(datetime.strptime(order['placedOn'], "%d %B, %Y %I:%M %p").replace(hour=0, minute=0, second=0, microsecond=0).timetuple()))
141 
        #saleTime1 = int(time.mktime((datetime.strptime(order['placedOn'], "%b %d, %Y %I:%M %p") + timedelta(seconds=-60)).timetuple()))
14647 amit.gupta 142 
        reconciled_affiliates = []
14460 amit.gupta 143 
        for subOrder in order['subOrders']:
14647 amit.gupta 144 
            affs = list(db.flipkartOrderAffiliateInfo.find({"productCode":subOrder.get("productCode"), "saleDateInt":saleTime}))
14460 amit.gupta 145 
            row += 1
146 
            i=-1
147 
            worksheet.write(row, inc(), orderId)
148 
            worksheet.write(row, inc(), row1[1])
14474 amit.gupta 149 
            worksheet.write(row, inc(), row1[-1])
14771 amit.gupta 150 
            worksheet.write(row, inc(), row1[-2])
151 
            worksheet.write(row, inc(), row1[-3])
14460 amit.gupta 152 
            worksheet.write(row, inc(), order['merchantOrderId'])
153 
            worksheet.write(row, inc(), subOrder['productTitle'])
154 
            worksheet.write(row, inc(), subOrder['amountPaid'])
155 
            worksheet.write(row, inc(), subOrder['quantity'])
156 
            worksheet.write(row, inc(), subOrder['status'])
157 
            worksheet.write(row, inc(), subOrder['detailedStatus'])
158 
            worksheet.write(row, inc(), subOrder['cashBackStatus'])
159 
            worksheet.write(row, inc(), subOrder['cashBackAmount'])
160 
            worksheet.write(row, inc(), subOrder['placedOn'])
161 
            worksheet.write(row, inc(), order['subTagId'])
162 
            for aff in affs:
14647 amit.gupta 163 
                if aff["_id"] not in reconciled_affiliates and aff['subTagId']== order['subTagId']:
164 
                    reconciled_affiliates.append(aff["_id"])
165 
                    row2 += 1
166 
                    i = -1
167 
                    worksheet1.write(row2, inc(), orderId)
168 
                    worksheet1.write(row2, inc(), row1[1])
169 
                    worksheet1.write(row2, inc(), row1[-1])
14771 amit.gupta 170 
                    worksheet1.write(row2, inc(), row1[-2])
171 
                    worksheet1.write(row2, inc(), row1[-3])
14647 amit.gupta 172 
                    worksheet1.write(row2, inc(), order['merchantOrderId'])
173 
                    worksheet1.write(row2, inc(), subOrder['productTitle'])
174 
                    worksheet1.write(row2, inc(), subOrder['amountPaid'])
175 
                    worksheet1.write(row2, inc(), subOrder['quantity'])
176 
                    worksheet1.write(row2, inc(), subOrder['status'])
177 
                    worksheet1.write(row2, inc(), subOrder['detailedStatus'])
178 
                    worksheet1.write(row2, inc(), subOrder['cashBackStatus'])
179 
                    worksheet1.write(row2, inc(), subOrder['cashBackAmount'])
180 
                    worksheet1.write(row2, inc(), subOrder['placedOn'])
181 
                    worksheet1.write(row2, inc(), order['subTagId'])
182 
                    db.flipkartOrderAffiliateInfo.update(
183 
                        {"productCode":subOrder.get("productCode"), "saleDateInt":saleTime, "subTagId":aff['subTagId']},
184 
                        {"$set":{"reconciled":True}})
185 
                    db.merchantOrder.update({"merchantOrderId":order["merchantOrderId"]},
186 
                                            {"$set":{"reconciled":True}})
14460 amit.gupta 187 
                    worksheet.write(row, inc(), aff['category'])
14647 amit.gupta 188 
                    worksheet1.write(row2, i, aff['category'])
14460 amit.gupta 189 
                    worksheet.write(row, inc(), aff['payOut'])
14647 amit.gupta 190 
                    worksheet1.write(row2, i, aff['payOut'])
14460 amit.gupta 191 
                    worksheet.write(row, inc(), aff['saleAmount'])
14647 amit.gupta 192 
                    worksheet1.write(row2, i, aff['saleAmount'])
14460 amit.gupta 193 
                    worksheet.write(row, inc(), aff['saleDate'])
14647 amit.gupta 194 
                    worksheet1.write(row2, i, aff['saleDate'])
14460 amit.gupta 195 
                    break
14647 amit.gupta 196 
            row3 += 1
197 
            i=-1
198 
            worksheet2.write(row3, inc(), orderId)
199 
            worksheet2.write(row3, inc(), row1[1])
200 
            worksheet2.write(row3, inc(), row1[-1])
14771 amit.gupta 201 
            worksheet2.write(row3, inc(), row1[-2])
202 
            worksheet2.write(row3, inc(), row1[-3])
14647 amit.gupta 203 
            worksheet2.write(row3, inc(), order['merchantOrderId'])
204 
            worksheet2.write(row3, inc(), subOrder['productTitle'])
205 
            worksheet2.write(row3, inc(), subOrder['amountPaid'])
206 
            worksheet2.write(row3, inc(), subOrder['quantity'])
207 
            worksheet2.write(row3, inc(), subOrder['status'])
208 
            worksheet2.write(row3, inc(), subOrder['detailedStatus'])
209 
            worksheet2.write(row3, inc(), subOrder['cashBackStatus'])
210 
            worksheet2.write(row3, inc(), subOrder['cashBackAmount'])
211 
            worksheet2.write(row3, inc(), subOrder['placedOn'])
212 
            worksheet2.write(row3, inc(), order['subTagId'])
213 
 
214 
    i=-1
215 
    worksheet3.write(0, inc(), "category",boldStyle)
216 
    worksheet3.write(0, inc(), "conversionStatus",boldStyle)
217 
    worksheet3.write(0, inc(), "Product Title",boldStyle)
218 
    worksheet3.write(0, inc(), "price",boldStyle)
219 
    worksheet3.write(0, inc(), "quantity",boldStyle)
220 
    worksheet3.write(0, inc(), "payOut",boldStyle)
221 
    worksheet3.write(0, inc(), "saleDate",boldStyle)
222 
    worksheet3.write(0, inc(), "subTagId",boldStyle)
223 
    for aff in db.flipkartOrderAffiliateInfo.find({"reconciled":{"$exists":False}}):
224 
        row4 += 1
225 
        i =-1
226 
        worksheet3.write(row4, inc(), aff["category"])
227 
        worksheet3.write(row4, inc(), aff["conversionStatus"])
228 
        skuData = getSkuData(2, aff["productCode"])
229 
        if skuData is None:
230 
            worksheet3.write(row4, inc(), aff["productTitle"])
231 
        else:
232 
            worksheet3.write(row4, inc(), skuData["product_name"])
233 
        worksheet3.write(row4, inc(), aff['price'])
234 
        worksheet3.write(row4, inc(), aff['quantity'])
235 
        worksheet3.write(row4, inc(), aff['payOut'])
236 
        worksheet3.write(row4, inc(), aff['saleDate'])
237 
        worksheet3.write(row4, inc(), aff['subTagId'])
238 
 
239 
 
240 
 
241 
    wb.save(XLS_F_FILENAME)
14450 amit.gupta 242 
def generateSnapDealReco():
14524 amit.gupta 243 
    global i
14460 amit.gupta 244 
    curs = getOrdersAfterDate(datetime.now() - timedelta(days=30), 3)
14450 amit.gupta 245 
    db = client.Dtr
14487 amit.gupta 246 
    matchedList = []
17168 amit.gupta 247 
 
14450 amit.gupta 248 
    workbook = xlwt.Workbook()
14501 amit.gupta 249 
    worksheet = workbook.add_sheet("Snapdeal Reconciled")
14508 amit.gupta 250 
    worksheet2 = workbook.add_sheet("Snapdeal Orders not reconciled")
17168 amit.gupta 251 
    affNotReconciledSheet = workbook.add_sheet("Snapdeal Aff not reconciled")
16947 amit.gupta 252 
    worksheet3 = workbook.add_sheet("DateWise Sale Summary")
17168 amit.gupta 253 
    worksheet1 = workbook.add_sheet("Snapdeal Reconciled All")
16947 amit.gupta 254 
    setHeaders(worksheet, worksheet1, worksheet2, affNotReconciledSheet, worksheet3)
14450 amit.gupta 255 
    row = 0
15577 amit.gupta 256 
    row5=0
14450 amit.gupta 257 
    for row1 in curs:
258 
        orderId = row1[0]
259 
        order = db.merchantOrder.find_one({"orderId":orderId})
260 
        if order is None:
261 
            continue
14496 amit.gupta 262 
        #saleTime = int(time.mktime(datetime.strptime(order['placedOn'], "%b %d, %Y, %I:%M %p").timetuple()))
16949 amit.gupta 263 
        try:
16951 amit.gupta 264 
            saleTime = datetime.strptime(order['placedOn'], "%a, %d %b, %Y").strftime("%Y-%m-%d")
16949 amit.gupta 265 
        except:
17001 amit.gupta 266 
            saleTime = datetime.strptime(order['placedOn'], "%b %d, %Y, %I:%M %p").strftime("%Y-%m-%d")
16947 amit.gupta 267 
 
14988 amit.gupta 268 
        try:
269 
            formattedTimestamp = order['placedOn']
270 
            timestamp1 = int(time.mktime(datetime.strptime(order['placedOn'], "%b %d, %Y, %I:%M %p").timetuple()))
271 
        except:
272 
            timestamp1 = int(time.mktime(row1[-4].timetuple()))
16947 amit.gupta 273 
            formattedTimestamp = datetime.strftime(utils.fromTimeStamp(timestamp1), "%b %d, %Y, %I:%M %p")
17167 amit.gupta 274 
 
275 
        affs = list(db.snapdealOrderAffiliateInfo1.find({"orderId":order["merchantOrderId"]}))
276 
        if len(affs) > 0:
17168 amit.gupta 277 
            for aff in affs:
278 
                if aff.get("missingOrder"):
279 
                    aff["missingOrder"] = False
280 
                    db.snapdealOrderAffiliateInfo1.save(aff)
17167 amit.gupta 281 
            matchedList.append(order["merchantOrderId"])
14501 amit.gupta 282 
            order['reconciled'] = True
283 
            db.merchantOrder.save(order)
284 
            row += 1
285 
            i=-1
286 
            worksheet.write(row, inc(), orderId)
287 
            worksheet.write(row, inc(), row1[1])
14850 amit.gupta 288 
            worksheet.write(row, inc(), row1[-1])
289 
            worksheet.write(row, inc(), row1[-2])
14771 amit.gupta 290 
            worksheet.write(row, inc(), row1[-3])
14501 amit.gupta 291 
            worksheet.write(row, inc(), order['merchantOrderId'])
292 
            worksheet.write(row, inc(), order['subTagId'])
293 
            worksheet.write(row, inc(), order['placedOn'])
14988 amit.gupta 294 
            worksheet.write(row, inc(), formattedTimestamp)
14501 amit.gupta 295 
            worksheet.write(row, inc(), int(order['paidAmount']))
296 
            k = i
14504 amit.gupta 297 
            row -= 1
14501 amit.gupta 298 
            for subOrder in order['subOrders']:
299 
                i = k
17168 amit.gupta 300 
                matched=False
14503 amit.gupta 301 
                row += 1
17167 amit.gupta 302 
                for aff in affs:
17168 amit.gupta 303 
                    if subOrder['productTitle']==aff['productCode']:
304 
                        worksheet.write(row, inc(), utils.fromTimeStamp(aff['saleDate']),datetime_format)
305 
                        worksheet.write(row, inc(), aff['unitPrice'])
306 
                        worksheet.write(row, inc(), 0 if not aff.get('payOut') else aff['payOut']/aff.get('quantity'))
307 
                        matched=True
308 
                        break
309 
                if not matched:
310 
                    inc()
311 
                    inc()
312 
                    inc()
313 
                worksheet.write(row, inc(), subOrder['productTitle'])
314 
                worksheet.write(row, inc(), subOrder['amountPaid'])
315 
                worksheet.write(row, inc(), subOrder['quantity'])
316 
                worksheet.write(row, inc(), subOrder['status'])
317 
                worksheet.write(row, inc(), subOrder['cashBackStatus'])
318 
                worksheet.write(row, inc(), subOrder['cashBackAmount'])
14504 amit.gupta 319 
 
14501 amit.gupta 320 
        else:
15577 amit.gupta 321 
            row5 += 1
322 
            i=-1
323 
            worksheet2.write(row5, inc(), order["orderId"])
324 
            worksheet2.write(row5, inc(), row1[1])
325 
            worksheet2.write(row5, inc(), row1[-1])
326 
            worksheet2.write(row5, inc(), row1[-2])
327 
            worksheet2.write(row5, inc(), row1[-3])
328 
            worksheet2.write(row5, inc(), order['merchantOrderId'])
329 
            worksheet2.write(row5, inc(), order['subTagId'])
330 
            worksheet2.write(row5, inc(), order['placedOn'])
331 
            worksheet2.write(row5, inc(), formattedTimestamp)
332 
            worksheet2.write(row5, inc(), int(order['paidAmount']))
333 
            row5 -=1
334 
            k=i
335 
            for subOrder in order['subOrders']:
336 
                i = k
15578 amit.gupta 337 
                row5 += 1
15577 amit.gupta 338 
                worksheet2.write(row5, inc(), subOrder['productTitle'])
339 
                worksheet2.write(row5, inc(), subOrder['amountPaid'])
340 
                worksheet2.write(row5, inc(), subOrder['quantity'])
341 
                worksheet2.write(row5, inc(), subOrder['status'])
342 
                worksheet2.write(row5, inc(), subOrder['cashBackStatus'])
15578 amit.gupta 343 
                worksheet2.write(row5, inc(), subOrder['cashBackAmount'])
14524 amit.gupta 344 
 
345 
    last30Days =  date.today() - timedelta(days=30)
346 
    int(time.mktime(last30Days.timetuple()))
347 
    row = 0
17168 amit.gupta 348 
    for offer in db.snapdealOrderAffiliateInfo1.find({"missingOrder":True,
349 
                                                     "saleDate":{"$gte":int(time.mktime(last30Days.timetuple())),
14570 amit.gupta 350 
                                                                 "$lt":int(time.mktime(date.today().timetuple()))}} ):
351 
        row += 1
352 
        i=-1
353 
        affNotReconciledSheet.write(row, inc(), offer.get("subTagId"))
17168 amit.gupta 354 
        affNotReconciledSheet.write(row, inc(), offer.get("orderId"))
355 
        affNotReconciledSheet.write(row, inc(), utils.fromTimeStamp(offer.get("saleDate")), datetime_format)
14570 amit.gupta 356 
        affNotReconciledSheet.write(row, inc(), offer.get("saleAmount"))
17168 amit.gupta 357 
        affNotReconciledSheet.write(row, inc(), offer.get("productCode"))
358 
        affNotReconciledSheet.write(row, inc(), offer.get("quantity"))
14570 amit.gupta 359 
        affNotReconciledSheet.write(row, inc(), offer.get("payOut"))
16947 amit.gupta 360 
 
17010 amit.gupta 361 
    mailBodyTemplate="""
362 
        <html>
363 
            <body>
364 
            <table cellspacing="0" border="1" style="text-align:right">
365 
                <thead>
366 
                    <tr>
367 
                        <th style="text-align:center">Date</th>
368 
                        <th  style="text-align:center">Sale</th>
369 
                        <th  style="text-align:center">Approved</th>
370 
                        <th  style="text-align:center">Approved %</th>
371 
                        <th  style="text-align:center">Rejected</th>
372 
                        <th  style="text-align:center">Rejected %</th>
17033 amit.gupta 373 
                        <th  style="text-align:center">Missing %</th>
374 
                        <th  style="text-align:center">Approved % against Sale</th>
17010 amit.gupta 375 
                    </tr>
376 
                </thead>
377 
                <tbody>
378 
                    {0}
379 
                </tbody>
380 
            </table>
381 
            </body>
382 
        </html>
383 
    """
384 
    tbody=[]
17033 amit.gupta 385 
    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 386 
    for offer in db.flSaleSnapshot.find().limit(61).sort("_id",-1):
387 
        approved = offer.get("approvedAmount")
388 
        rejected = offer.get("rejectedAmount")
17033 amit.gupta 389 
        sale = offer.get("paidAmount")
17010 amit.gupta 390 
        total = approved + rejected
391 
        if total ==0:
392 
            continue
17033 amit.gupta 393 
        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 394 
    message = mailBodyTemplate.format("".join(tbody))
15540 amit.gupta 395 
    workbook.save(XLS_FILENAME)
17024 amit.gupta 396 
    return message
17013 manish.sha 397 
 
398 
def generateHomeShopReco():
399 
    global i
400 
    curs = getOrdersAfterDate(datetime.now() - timedelta(days=30), 7)
401 
    db = client.Dtr
402 
    notReconciled = {}
403 
    matchedList = []
404 
    workbook = xlwt.Workbook()
405 
    worksheet = workbook.add_sheet("HomeShop18 Reconciled")
406 
    worksheet1 = workbook.add_sheet("HomeShop18 Reconciled All")
407 
    worksheet2 = workbook.add_sheet("HomeShop18 Orders not reconciled")
408 
    affNotReconciledSheet = workbook.add_sheet("HomeShop18 Aff not reconciled")
409 
    setHeaders(worksheet, worksheet1, worksheet2, affNotReconciledSheet)
410 
    row = 0
411 
    anotherrow = 0
412 
    row2 = 0
413 
    row5=0
414 
 
415 
    for row1 in curs:
416 
        orderId = row1[0]
417 
        order = db.merchantOrder.find_one({"orderId":orderId})
418 
        if order is None:
419 
            continue
420 
        #saleTime = int(time.mktime(datetime.strptime(order['placedOn'], "%b %d, %Y, %I:%M %p").timetuple()))  26 May 2015 02:12 PM
421 
        aff = list(db.shopcluesOrderAffiliateInfo.find({"merchantOrderId":order["merchantOrderId"]}))
422 
        anotherrow += 1
423 
        try:
424 
            formattedTimestamp = order['placedOn']
425 
            timestamp1 = int(time.mktime(datetime.strptime(order['placedOn'], "%d %b %Y %I:%M %p").timetuple()))
426 
        except:
427 
            timestamp1 = int(time.mktime(row1[-4].timetuple()))
428 
            formattedTimestamp = datetime.strftime(utils.fromTimeStamp(timestamp1), "%d %b %Y %I:%M %p")
429 
 
430 
        if len(aff) > 0 and order["subTagId"] not in matchedList and long(order["paidAmount"])==long(float(aff[0]["transactionValue"])):
431 
            matchedList.append(order["subTagId"])
432 
            db.shopcluesOrderAffiliateInfo.update({"merchantOrderId":aff[0].get("merchantOrderId")}, {"$set":{"reconciled":True, "orderId":orderId}}, multi=True)
433 
            order['reconciled'] = True
434 
            order['adId'] = aff[0].get("uniqueKey")
435 
            db.merchantOrder.save(order)
436 
            row += 1
437 
            i=-1
438 
 
439 
            worksheet.write(row, inc(), orderId)
440 
            worksheet1.write(anotherrow, i, orderId)
441 
            worksheet.write(row, inc(), row1[1])
442 
            worksheet1.write(anotherrow, i, row1[1])
443 
            worksheet.write(row, inc(), row1[-1])
444 
            worksheet1.write(anotherrow, i, row1[-1])
445 
            worksheet.write(row, inc(), row1[-2])
446 
            worksheet1.write(anotherrow, i, row1[-2])
447 
            worksheet.write(row, inc(), row1[-3])
448 
            worksheet1.write(anotherrow, i, row1[-3])
449 
            worksheet.write(row, inc(), order['merchantOrderId'])
450 
            worksheet1.write(anotherrow, i, order['merchantOrderId'])
451 
            worksheet.write(row, inc(), order['subTagId'])
452 
            worksheet1.write(anotherrow, i, order['subTagId'])
453 
            worksheet.write(row, inc(), order['placedOn'])
454 
            worksheet1.write(anotherrow, i, order['placedOn'])
455 
            worksheet.write(row, inc(), formattedTimestamp)
456 
            worksheet1.write(anotherrow, i, formattedTimestamp)
457 
            worksheet.write(row, inc(), int(order['paidAmount']))
458 
            worksheet1.write(anotherrow, i, int(order['paidAmount']))
459 
            worksheet.write(row, inc(), datetime.strftime(utils.fromTimeStamp(aff[0]['transactionTime']), "%d %b %Y %I:%M %p"))
460 
            worksheet1.write(anotherrow, i, datetime.strftime(utils.fromTimeStamp(aff[0]['transactionTime']), "%d %b %Y %I:%M %p"))
461 
            worksheet.write(row, inc(), aff[0]['transactionValue'])
462 
            worksheet1.write(anotherrow, i, aff[0]['transactionValue'])
463 
            worksheet.write(row, inc(), aff[0]['payOut'])
464 
            worksheet1.write(anotherrow, i, aff[0]['payOut'])
465 
            worksheet.write(row, inc(), "NA")
466 
            worksheet1.write(anotherrow, i, "NA")
467 
            k = i
468 
            row -= 1
469 
            anotherrow -= 1
470 
            for subOrder in order['subOrders']:
471 
                i = k
472 
                row += 1
473 
                anotherrow += 1
474 
                worksheet.write(row, inc(), subOrder['productTitle'])
475 
                worksheet1.write(anotherrow, i, subOrder['productTitle'])
476 
                worksheet.write(row, inc(), subOrder['amountPaid'])
477 
                worksheet1.write(anotherrow, i, subOrder['amountPaid'])
478 
                worksheet.write(row, inc(), subOrder['quantity'])
479 
                worksheet1.write(anotherrow, i, subOrder['quantity'])
480 
                worksheet.write(row, inc(), subOrder['status'])
481 
                worksheet1.write(anotherrow, i, subOrder['status'])
482 
                worksheet.write(row, inc(), subOrder['cashBackStatus'])
483 
                worksheet1.write(anotherrow, i, subOrder['cashBackStatus'])
484 
                worksheet.write(row, inc(), subOrder['cashBackAmount'])
485 
                worksheet1.write(anotherrow, i, subOrder['cashBackAmount'])
486 
 
487 
        else:
488 
            i=-1
489 
            worksheet1.write(anotherrow, inc(), orderId)
490 
            worksheet1.write(anotherrow, inc(), row1[1])
491 
            worksheet1.write(anotherrow, inc(), row1[-1])
492 
            worksheet1.write(anotherrow, inc(), row1[-2])
493 
            worksheet1.write(anotherrow, inc(), row1[-3])
494 
            worksheet1.write(anotherrow, inc(), order['merchantOrderId'])
495 
            worksheet1.write(anotherrow, inc(), order['subTagId'])
496 
            worksheet1.write(anotherrow, inc(), order['placedOn'])
497 
            worksheet1.write(anotherrow, inc(), formattedTimestamp)
498 
            worksheet1.write(anotherrow, inc(), int(order['paidAmount']))
499 
 
500 
            notReconciled[orderId] = (anotherrow, i, int(order['paidAmount']), timestamp1, order["subTagId"])
501 
            inc()
502 
            inc()
503 
            inc()
504 
            inc()
505 
            k = i
506 
            anotherrow -= 1
507 
            for subOrder in order['subOrders']:
508 
                anotherrow += 1
509 
                i = k
510 
                worksheet1.write(anotherrow, inc(), subOrder['productTitle'])
511 
                worksheet1.write(anotherrow, inc(), subOrder['amountPaid'])
512 
                worksheet1.write(anotherrow, inc(), subOrder['quantity'])
513 
                worksheet1.write(anotherrow, inc(), subOrder['status'])
514 
                worksheet1.write(anotherrow, inc(), subOrder['cashBackStatus'])
515 
                worksheet1.write(anotherrow, inc(), subOrder['cashBackAmount'])
516 
            row5 += 1
517 
            i=-1
518 
            worksheet2.write(row5, inc(), order["orderId"])
519 
            worksheet2.write(row5, inc(), row1[1])
520 
            worksheet2.write(row5, inc(), row1[-1])
521 
            worksheet2.write(row5, inc(), row1[-2])
522 
            worksheet2.write(row5, inc(), row1[-3])
523 
            worksheet2.write(row5, inc(), order['merchantOrderId'])
524 
            worksheet2.write(row5, inc(), order['subTagId'])
525 
            worksheet2.write(row5, inc(), order['placedOn'])
526 
            worksheet2.write(row5, inc(), formattedTimestamp)
527 
            worksheet2.write(row5, inc(), int(order['paidAmount']))
528 
            row5 -=1
529 
            k=i
530 
            for subOrder in order['subOrders']:
531 
                i = k
532 
                row5 += 1
533 
                worksheet2.write(row5, inc(), subOrder['productTitle'])
534 
                worksheet2.write(row5, inc(), subOrder['amountPaid'])
535 
                worksheet2.write(row5, inc(), subOrder['quantity'])
536 
                worksheet2.write(row5, inc(), subOrder['status'])
537 
                worksheet2.write(row5, inc(), subOrder['cashBackStatus'])
538 
                worksheet2.write(row5, inc(), subOrder['cashBackAmount'])
539 
    last30Days =  date.today() - timedelta(days=30)
540 
    int(time.mktime(last30Days.timetuple()))
541 
    row = 0
542 
    for offer in db.homeshopOrderAffiliateInfo.find({"reconciled":{"$exists":False},
543 
                                                     "transactionTime":{"$gt":int(time.mktime(last30Days.timetuple())),
544 
                                                                 "$lt":int(time.mktime(date.today().timetuple()))}} ):
545 
        subTagId = offer.get("subTagId")
546 
        saleTime = offer.get("transactionTime")
547 
        orders = getOrdersByTag(subTagId, 7)
548 
        for order in orders:
549 
            if notReconciled.has_key(order[0]):
550 
                (roww, column, paidAmount, timestamp1, oSubTagId) = notReconciled[order[0]]
551 
                if paidAmount==int(offer.get("transactionValue")) and timestamp1 <= saleTime and timestamp1 + 120 >= saleTime:
552 
                    i = column
553 
                    worksheet1.write(roww, inc(), datetime.strftime(utils.fromTimeStamp(offer.get("transactionTime")), "%d %b %Y %I:%M %p"))
554 
                    worksheet1.write(roww, inc(), offer.get("transactionValue"))
555 
                    worksheet1.write(roww, inc(), offer.get("payOut"))
556 
                    del notReconciled[order[0]]
557 
                    print "found match for user", order[1]
558 
                    db.homeshopOrderAffiliateInfo.update({"transactionId":offer.get("transactionId")}, {"$set":{"reconciled":True, "orderId":order[0]}}, multi=True)
559 
                    db.merchantOrder.update({"orderId":order[0]}, {"$set":{"reconciled":True, "adId":offer.get("uniqueKey")}}, multi=True)
560 
                    break
561 
 
562 
    unreconciledOrders = notReconciled.keys()
563 
 
564 
    for offer in db.homeshopOrderAffiliateInfo.find({"reconciled":{"$exists":False},
565 
                                                     "transactionTime":{"$gt":int(time.mktime(last30Days.timetuple())),
566 
                                                                 "$lt":int(time.mktime(date.today().timetuple()))}} ):
567 
        row += 1
568 
        i=-1
569 
        affNotReconciledSheet.write(row, inc(), offer.get("uniqueKey"))
570 
        affNotReconciledSheet.write(row, inc(), offer.get("subTagId"))
571 
        affNotReconciledSheet.write(row, inc(), datetime.strftime(utils.fromTimeStamp(offer.get("transactionTime")), "%d %b %Y %I:%M %p"))
572 
        affNotReconciledSheet.write(row, inc(), offer.get("transactionValue"))
573 
        affNotReconciledSheet.write(row, inc(), offer.get("payOut"))
574 
        affNotReconciledSheet.write(row, inc(), offer.get("status"))
575 
        affNotReconciledSheet.write(row, inc(), 'NA')
576 
 
577 
    workbook.save(XLS_HS_FILENAME)
578 
 
16995 amit.gupta 579 
 
15791 manish.sha 580 
def generateShopcluesReco():
581 
    global i
16238 manish.sha 582 
    curs = getOrdersAfterDate(datetime.now() - timedelta(days=30), 5)
15791 manish.sha 583 
    db = client.Dtr
584 
    notReconciled = {}
585 
    matchedList = []
586 
    workbook = xlwt.Workbook()
587 
    worksheet = workbook.add_sheet("Shopclues Reconciled")
588 
    worksheet1 = workbook.add_sheet("Shopclues Reconciled All")
589 
    worksheet2 = workbook.add_sheet("Shopclues Orders not reconciled")
590 
    affNotReconciledSheet = workbook.add_sheet("Shopclues Aff not reconciled")
591 
    setHeaders(worksheet, worksheet1, worksheet2, affNotReconciledSheet)
592 
    row = 0
593 
    anotherrow = 0
594 
    row2 = 0
595 
    row5=0
596 
 
597 
    for row1 in curs:
598 
        orderId = row1[0]
599 
        order = db.merchantOrder.find_one({"orderId":orderId})
600 
        if order is None:
601 
            continue
602 
        #saleTime = int(time.mktime(datetime.strptime(order['placedOn'], "%b %d, %Y, %I:%M %p").timetuple()))  26 May 2015 02:12 PM
16204 manish.sha 603 
        aff = list(db.shopcluesOrderAffiliateInfo.find({"merchantOrderId":order["merchantOrderId"]}))
15791 manish.sha 604 
        anotherrow += 1
605 
        try:
606 
            formattedTimestamp = order['placedOn']
607 
            timestamp1 = int(time.mktime(datetime.strptime(order['placedOn'], "%d %b %Y %I:%M %p").timetuple()))
608 
        except:
609 
            timestamp1 = int(time.mktime(row1[-4].timetuple()))
16947 amit.gupta 610 
            formattedTimestamp = datetime.strftime(utils.fromTimeStamp(timestamp1), "%d %b %Y %I:%M %p")
15791 manish.sha 611 
 
612 
        if len(aff) > 0 and order["subTagId"] not in matchedList and long(order["paidAmount"])==long(float(aff[0]["transactionValue"])):
613 
            matchedList.append(order["subTagId"])
16204 manish.sha 614 
            db.shopcluesOrderAffiliateInfo.update({"merchantOrderId":aff[0].get("merchantOrderId")}, {"$set":{"reconciled":True, "orderId":orderId}}, multi=True)
15791 manish.sha 615 
            order['reconciled'] = True
616 
            order['adId'] = aff[0].get("uniqueKey")
617 
            db.merchantOrder.save(order)
618 
            row += 1
619 
            i=-1
14450 amit.gupta 620 
 
15791 manish.sha 621 
            worksheet.write(row, inc(), orderId)
622 
            worksheet1.write(anotherrow, i, orderId)
623 
            worksheet.write(row, inc(), row1[1])
624 
            worksheet1.write(anotherrow, i, row1[1])
625 
            worksheet.write(row, inc(), row1[-1])
626 
            worksheet1.write(anotherrow, i, row1[-1])
627 
            worksheet.write(row, inc(), row1[-2])
628 
            worksheet1.write(anotherrow, i, row1[-2])
629 
            worksheet.write(row, inc(), row1[-3])
630 
            worksheet1.write(anotherrow, i, row1[-3])
631 
            worksheet.write(row, inc(), order['merchantOrderId'])
632 
            worksheet1.write(anotherrow, i, order['merchantOrderId'])
633 
            worksheet.write(row, inc(), order['subTagId'])
634 
            worksheet1.write(anotherrow, i, order['subTagId'])
635 
            worksheet.write(row, inc(), order['placedOn'])
636 
            worksheet1.write(anotherrow, i, order['placedOn'])
637 
            worksheet.write(row, inc(), formattedTimestamp)
638 
            worksheet1.write(anotherrow, i, formattedTimestamp)
639 
            worksheet.write(row, inc(), int(order['paidAmount']))
640 
            worksheet1.write(anotherrow, i, int(order['paidAmount']))
16947 amit.gupta 641 
            worksheet.write(row, inc(), datetime.strftime(utils.fromTimeStamp(aff[0]['transactionTime']), "%d %b %Y %I:%M %p"))
642 
            worksheet1.write(anotherrow, i, datetime.strftime(utils.fromTimeStamp(aff[0]['transactionTime']), "%d %b %Y %I:%M %p"))
15791 manish.sha 643 
            worksheet.write(row, inc(), aff[0]['transactionValue'])
644 
            worksheet1.write(anotherrow, i, aff[0]['transactionValue'])
645 
            worksheet.write(row, inc(), aff[0]['payOut'])
646 
            worksheet1.write(anotherrow, i, aff[0]['payOut'])
16238 manish.sha 647 
            worksheet.write(row, inc(), "NA")
648 
            worksheet1.write(anotherrow, i, "NA")
15791 manish.sha 649 
            k = i
650 
            row -= 1
651 
            anotherrow -= 1
652 
            for subOrder in order['subOrders']:
653 
                i = k
654 
                row += 1
655 
                anotherrow += 1
656 
                worksheet.write(row, inc(), subOrder['productTitle'])
657 
                worksheet1.write(anotherrow, i, subOrder['productTitle'])
658 
                worksheet.write(row, inc(), subOrder['amountPaid'])
659 
                worksheet1.write(anotherrow, i, subOrder['amountPaid'])
660 
                worksheet.write(row, inc(), subOrder['quantity'])
661 
                worksheet1.write(anotherrow, i, subOrder['quantity'])
662 
                worksheet.write(row, inc(), subOrder['status'])
663 
                worksheet1.write(anotherrow, i, subOrder['status'])
664 
                worksheet.write(row, inc(), subOrder['cashBackStatus'])
665 
                worksheet1.write(anotherrow, i, subOrder['cashBackStatus'])
666 
                worksheet.write(row, inc(), subOrder['cashBackAmount'])
667 
                worksheet1.write(anotherrow, i, subOrder['cashBackAmount'])
668 
 
669 
        else:
670 
            i=-1
671 
            worksheet1.write(anotherrow, inc(), orderId)
672 
            worksheet1.write(anotherrow, inc(), row1[1])
673 
            worksheet1.write(anotherrow, inc(), row1[-1])
674 
            worksheet1.write(anotherrow, inc(), row1[-2])
675 
            worksheet1.write(anotherrow, inc(), row1[-3])
676 
            worksheet1.write(anotherrow, inc(), order['merchantOrderId'])
677 
            worksheet1.write(anotherrow, inc(), order['subTagId'])
678 
            worksheet1.write(anotherrow, inc(), order['placedOn'])
679 
            worksheet1.write(anotherrow, inc(), formattedTimestamp)
680 
            worksheet1.write(anotherrow, inc(), int(order['paidAmount']))
681 
 
682 
            notReconciled[orderId] = (anotherrow, i, int(order['paidAmount']), timestamp1, order["subTagId"])
683 
            inc()
684 
            inc()
685 
            inc()
686 
            inc()
687 
            k = i
688 
            anotherrow -= 1
689 
            for subOrder in order['subOrders']:
690 
                anotherrow += 1
691 
                i = k
692 
                worksheet1.write(anotherrow, inc(), subOrder['productTitle'])
693 
                worksheet1.write(anotherrow, inc(), subOrder['amountPaid'])
694 
                worksheet1.write(anotherrow, inc(), subOrder['quantity'])
695 
                worksheet1.write(anotherrow, inc(), subOrder['status'])
696 
                worksheet1.write(anotherrow, inc(), subOrder['cashBackStatus'])
697 
                worksheet1.write(anotherrow, inc(), subOrder['cashBackAmount'])
698 
            row5 += 1
699 
            i=-1
700 
            worksheet2.write(row5, inc(), order["orderId"])
701 
            worksheet2.write(row5, inc(), row1[1])
702 
            worksheet2.write(row5, inc(), row1[-1])
703 
            worksheet2.write(row5, inc(), row1[-2])
704 
            worksheet2.write(row5, inc(), row1[-3])
705 
            worksheet2.write(row5, inc(), order['merchantOrderId'])
706 
            worksheet2.write(row5, inc(), order['subTagId'])
707 
            worksheet2.write(row5, inc(), order['placedOn'])
708 
            worksheet2.write(row5, inc(), formattedTimestamp)
709 
            worksheet2.write(row5, inc(), int(order['paidAmount']))
710 
            row5 -=1
711 
            k=i
712 
            for subOrder in order['subOrders']:
713 
                i = k
714 
                row5 += 1
715 
                worksheet2.write(row5, inc(), subOrder['productTitle'])
716 
                worksheet2.write(row5, inc(), subOrder['amountPaid'])
717 
                worksheet2.write(row5, inc(), subOrder['quantity'])
718 
                worksheet2.write(row5, inc(), subOrder['status'])
719 
                worksheet2.write(row5, inc(), subOrder['cashBackStatus'])
720 
                worksheet2.write(row5, inc(), subOrder['cashBackAmount'])
721 
 
722 
    last30Days =  date.today() - timedelta(days=30)
723 
    int(time.mktime(last30Days.timetuple()))
724 
    row = 0
725 
    for offer in db.shopcluesOrderAffiliateInfo.find({"reconciled":{"$exists":False},
726 
                                                     "transactionTime":{"$gt":int(time.mktime(last30Days.timetuple())),
727 
                                                                 "$lt":int(time.mktime(date.today().timetuple()))}} ):
728 
        subTagId = offer.get("subTagId")
729 
        saleTime = offer.get("transactionTime")
15794 manish.sha 730 
        orders = getOrdersByTag(subTagId, 5)
15791 manish.sha 731 
        for order in orders:
732 
            if notReconciled.has_key(order[0]):
733 
                (roww, column, paidAmount, timestamp1, oSubTagId) = notReconciled[order[0]]
734 
                if paidAmount==int(offer.get("transactionValue")) and timestamp1 <= saleTime and timestamp1 + 120 >= saleTime:
735 
                    i = column
16947 amit.gupta 736 
                    worksheet1.write(roww, inc(), datetime.strftime(utils.fromTimeStamp(offer.get("transactionTime")), "%d %b %Y %I:%M %p"))
15791 manish.sha 737 
                    worksheet1.write(roww, inc(), offer.get("transactionValue"))
738 
                    worksheet1.write(roww, inc(), offer.get("payOut"))
739 
                    del notReconciled[order[0]]
740 
                    print "found match for user", order[1]
741 
                    db.shopcluesOrderAffiliateInfo.update({"transactionId":offer.get("transactionId")}, {"$set":{"reconciled":True, "orderId":order[0]}}, multi=True)
742 
                    db.merchantOrder.update({"orderId":order[0]}, {"$set":{"reconciled":True, "adId":offer.get("uniqueKey")}}, multi=True)
743 
                    break
744 
 
745 
    unreconciledOrders = notReconciled.keys()
746 
 
747 
    for offer in db.shopcluesOrderAffiliateInfo.find({"reconciled":{"$exists":False},
748 
                                                     "transactionTime":{"$gt":int(time.mktime(last30Days.timetuple())),
749 
                                                                 "$lt":int(time.mktime(date.today().timetuple()))}} ):
750 
        row += 1
751 
        i=-1
752 
        affNotReconciledSheet.write(row, inc(), offer.get("uniqueKey"))
753 
        affNotReconciledSheet.write(row, inc(), offer.get("subTagId"))
16947 amit.gupta 754 
        affNotReconciledSheet.write(row, inc(), datetime.strftime(utils.fromTimeStamp(offer.get("transactionTime")), "%d %b %Y %I:%M %p"))
15791 manish.sha 755 
        affNotReconciledSheet.write(row, inc(), offer.get("transactionValue"))
756 
        affNotReconciledSheet.write(row, inc(), offer.get("payOut"))
757 
        affNotReconciledSheet.write(row, inc(), offer.get("status"))
758 
        affNotReconciledSheet.write(row, inc(), 'NA')
759 
 
760 
    workbook.save(XLS_SC_FILENAME)
761 
 
14647 amit.gupta 762 
def sendmail(email, message, title, *varargs):
14450 amit.gupta 763 
    if email == "":
764 
        return
765 
    mailServer = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)
766 
    mailServer.ehlo()
767 
    mailServer.starttls()
768 
    mailServer.ehlo()
769 
 
770 
    # Create the container (outer) email message.
771 
    msg = MIMEMultipart()
772 
    msg['Subject'] = title
773 
    msg.preamble = title
774 
    html_msg = MIMEText(message, 'html')
775 
    msg.attach(html_msg)
776 
 
777 
    #snapdeal more to be added here
14647 amit.gupta 778 
    for fileName in varargs:
779 
        snapdeal = MIMEBase('application', 'vnd.ms-excel')
780 
        snapdeal.set_payload(file(fileName).read())
781 
        encoders.encode_base64(snapdeal)
782 
        snapdeal.add_header('Content-Disposition', 'attachment;filename=' + fileName)
783 
        msg.attach(snapdeal)
14450 amit.gupta 784 
 
785 
 
786 
    email.append('amit.gupta@shop2020.in')
787 
    MAILTO = email
788 
    mailServer.login(SENDER, PASSWORD)
789 
    mailServer.sendmail(PASSWORD, MAILTO, msg.as_string())
790 
 
16958 amit.gupta 791 
def setHeaders(worksheet, worksheet1, worksheet2, affNotReconciledSheet, worksheet3=None):
14505 amit.gupta 792 
    boldStyle = xlwt.XFStyle()
793 
    f = xlwt.Font()
794 
    f.bold = True
795 
    boldStyle.font = f
14501 amit.gupta 796 
    row = 0
797 
    global i
798 
    i=-1
799 
    worksheet.write(row, inc(), 'Order Id', boldStyle)
800 
    worksheet.write(row, inc(), 'User Id', boldStyle)
14850 amit.gupta 801 
    worksheet.write(row, inc(), 'Username', boldStyle)
14601 amit.gupta 802 
    worksheet.write(row, inc(), 'Version Code', boldStyle)
14771 amit.gupta 803 
    worksheet.write(row, inc(), 'Device', boldStyle)
14501 amit.gupta 804 
    worksheet.write(row, inc(), 'Merchant Order Id', boldStyle)
805 
    worksheet.write(row, inc(), 'SubtagId', boldStyle)
806 
    worksheet.write(row, inc(), 'Sale Date', boldStyle)
14988 amit.gupta 807 
    worksheet.write(row, inc(), 'Dtr Sale Date', boldStyle)
14501 amit.gupta 808 
    worksheet.write(row, inc(), 'Sale Amount', boldStyle)
809 
    worksheet.write(row, inc(), 'Aff Sale Date', boldStyle)
810 
    worksheet.write(row, inc(), 'Aff Sale Amt', boldStyle)
811 
    worksheet.write(row, inc(), 'Aff PayOut', boldStyle)
812 
    worksheet.write(row, inc(), 'Product Title', boldStyle)
813 
    worksheet.write(row, inc(), 'Price', boldStyle)
814 
    worksheet.write(row, inc(), 'Quantity', boldStyle)
815 
    worksheet.write(row, inc(), 'Status', boldStyle)
816 
    worksheet.write(row, inc(), 'Cashback Status', boldStyle)
817 
    worksheet.write(row, inc(), 'CashBack', boldStyle)
818 
 
14508 amit.gupta 819 
 
820 
    i = -1
821 
    worksheet2.write(row, inc(), 'Order Id', boldStyle)
822 
    worksheet2.write(row, inc(), 'User Id', boldStyle)
14850 amit.gupta 823 
    worksheet2.write(row, inc(), 'Username', boldStyle)
14605 amit.gupta 824 
    worksheet2.write(row, inc(), 'Version Code', boldStyle)
14771 amit.gupta 825 
    worksheet2.write(row, inc(), 'Device', boldStyle)
14508 amit.gupta 826 
    worksheet2.write(row, inc(), 'Merchant Order Id', boldStyle)
827 
    worksheet2.write(row, inc(), 'SubtagId', boldStyle)
828 
    worksheet2.write(row, inc(), 'Sale Date', boldStyle)
14988 amit.gupta 829 
    worksheet2.write(row, inc(), 'Dtr Sale Date', boldStyle)
14508 amit.gupta 830 
    worksheet2.write(row, inc(), 'Sale Amount', boldStyle)
831 
    worksheet2.write(row, inc(), 'Product Title', boldStyle)
832 
    worksheet2.write(row, inc(), 'Price', boldStyle)
833 
    worksheet2.write(row, inc(), 'Quantity', boldStyle)
834 
    worksheet2.write(row, inc(), 'Status', boldStyle)
835 
    worksheet2.write(row, inc(), 'Cashback Status', boldStyle)
836 
    worksheet2.write(row, inc(), 'CashBack', boldStyle)
14450 amit.gupta 837 
 
14508 amit.gupta 838 
    i =-1
17168 amit.gupta 839 
    affNotReconciledSheet.write(row, inc(), 'Sub Tag Id', boldStyle)
840 
    affNotReconciledSheet.write(row, inc(), 'Order Id', boldStyle)
14508 amit.gupta 841 
    affNotReconciledSheet.write(row, inc(), 'Sale Date', boldStyle)
842 
    affNotReconciledSheet.write(row, inc(), 'Sale Amount', boldStyle)
17168 amit.gupta 843 
    affNotReconciledSheet.write(row, inc(), 'Product', boldStyle)
844 
    affNotReconciledSheet.write(row, inc(), 'Quantity', boldStyle)
14508 amit.gupta 845 
    affNotReconciledSheet.write(row, inc(), 'Pay Out', boldStyle)
14524 amit.gupta 846 
 
16958 amit.gupta 847 
    if worksheet3:
848 
        i =-1
849 
        worksheet3.write(row, inc(), 'Date', boldStyle)
850 
        worksheet3.write(row, inc(), 'Sale Amount', boldStyle)
851 
        worksheet3.write(row, inc(), 'Approved Sales', boldStyle)
852 
        worksheet3.write(row, inc(), 'Rejected Sales', boldStyle)
16947 amit.gupta 853 
 
14524 amit.gupta 854 
def getUserAmountReconciled():
855 
    pass
856 
 
14613 amit.gupta 857 
def getUserOrders():
858 
    global i
16726 amit.gupta 859 
    #worksheet1 = wb.add_sheet("Suspected Users")
17159 amit.gupta 860 
    workbook = xlwt.Workbook("ISO8859-1")
14632 amit.gupta 861 
    sh1 = workbook.add_sheet("All Orders")
15423 amit.gupta 862 
    sh2 = workbook.add_sheet("Flipkart Orders")
17159 amit.gupta 863 
    worksheet = workbook.add_sheet("All Users")
14632 amit.gupta 864 
 
14613 amit.gupta 865 
    db=client.Dtr
17199 amit.gupta 866 
    prevDate = cutOff
17159 amit.gupta 867 
    results = Mysql.fetchResult("select * from allorder where created_on > %s", prevDate)
14613 amit.gupta 868 
    row = 0
869 
    i=-1
870 
    worksheet.write(row, inc(), 'User Id', boldStyle)
871 
    worksheet.write(row, inc(), 'User name', boldStyle)
872 
    worksheet.write(row, inc(), 'Order Id', boldStyle)
873 
    worksheet.write(row, inc(), 'Created On', boldStyle)
874 
    worksheet.write(row, inc(), 'Store Id', boldStyle)
875 
    worksheet.write(row, inc(), 'Merchant Order Id', boldStyle)
16785 amit.gupta 876 
    worksheet.write(row, inc(), 'SubOrder Id', boldStyle)
14616 amit.gupta 877 
    worksheet.write(row, inc(), 'Product title', boldStyle)
14613 amit.gupta 878 
    worksheet.write(row, inc(), 'Amount Paid', boldStyle)
879 
    worksheet.write(row, inc(), 'Cashback', boldStyle)
880 
    worksheet.write(row, inc(), 'Order Status', boldStyle)
881 
    worksheet.write(row, inc(), 'Detailed Status', boldStyle)
882 
    worksheet.write(row, inc(), 'Cashback Status', boldStyle)
883 
    worksheet.write(row, inc(), 'Reconciled', boldStyle)
884 
    worksheet.write(row, inc(), 'IP', boldStyle)
16718 amit.gupta 885 
    worksheet.write(row, inc(), 'Aff Status', boldStyle)
14693 amit.gupta 886 
    i=-1
887 
    sh1.write(row, inc(), 'User Id', boldStyle)
888 
    sh1.write(row, inc(), 'User name', boldStyle)
889 
    sh1.write(row, inc(), 'Order Id', boldStyle)
890 
    sh1.write(row, inc(), 'Created On', boldStyle)
891 
    sh1.write(row, inc(), 'Store Id', boldStyle)
892 
    sh1.write(row, inc(), 'Merchant Order Id', boldStyle)
14771 amit.gupta 893 
    sh1.write(row, inc(), 'Status', boldStyle)
15932 amit.gupta 894 
    sh1.write(row, inc(), 'Detailed Status', boldStyle)
14693 amit.gupta 895 
    sh1.write(row, inc(), 'Product title', boldStyle)
896 
    sh1.write(row, inc(), 'Referrer', boldStyle)
897 
    sh1.write(row, inc(), 'Amount Paid', boldStyle)
14771 amit.gupta 898 
    sh1.write(row, inc(), 'Catalog Id', boldStyle)
14693 amit.gupta 899 
    sh1.write(row, inc(), 'Brand', boldStyle)
900 
    sh1.write(row, inc(), 'Model', boldStyle)
901 
    sh1.write(row, inc(), 'Category', boldStyle)
15932 amit.gupta 902 
    sh1.write(row, inc(), 'Deal Rank', boldStyle)
16286 amit.gupta 903 
    sh1.write(row, inc(), 'Max Nlc', boldStyle)
904 
    sh1.write(row, inc(), 'Min Nlc', boldStyle)
905 
    sh1.write(row, inc(), 'DP', boldStyle)
906 
    sh1.write(row, inc(), 'Item status', boldStyle)
15932 amit.gupta 907 
    sh1.write(row, inc(), 'City', boldStyle)
908 
    sh1.write(row, inc(), 'State', boldStyle)
909 
    sh1.write(row, inc(), 'Pincode', boldStyle)
17159 amit.gupta 910 
    sh1.write(row, inc(), 'SubOrder Id', boldStyle)
911 
    sh1.write(row, inc(), 'Cashback Status', boldStyle)
912 
    sh1.write(row, inc(), 'Cashback Amount', boldStyle)
15423 amit.gupta 913 
    i =-1
914 
    sh2.write(row, inc(), 'User Id', boldStyle)
915 
    sh2.write(row, inc(), 'User name', boldStyle)
916 
    sh2.write(row, inc(), 'Created On', boldStyle)
917 
    sh2.write(row, inc(), 'Store', boldStyle)
918 
    sh2.write(row, inc(), 'Status', boldStyle)
919 
    sh2.write(row, inc(), 'Product title', boldStyle)
920 
    sh2.write(row, inc(), 'Price', boldStyle)
921 
    sh2.write(row, inc(), 'Quantity', boldStyle)
922 
    sh2.write(row, inc(), 'Catalog Id', boldStyle)
923 
    sh2.write(row, inc(), 'Brand', boldStyle)
924 
    sh2.write(row, inc(), 'Model', boldStyle)
925 
    sh2.write(row, inc(), 'Category', boldStyle)
14613 amit.gupta 926 
 
17159 amit.gupta 927 
    default_format = xlwt.XFStyle()
928 
    date_format = xlwt.XFStyle()
929 
    date_format.num_format_str = 'dd/mm/yyyy'
930 
 
931 
    datetime_format = xlwt.XFStyle()
932 
    datetime_format.num_format_str = 'dd/mm/yyyy HH:MM AM/PM'
933 
 
934 
    number_format = xlwt.XFStyle()
935 
    number_format.num_format_str = '#,##0'
936 
 
937 
 
14613 amit.gupta 938 
    row=0
17159 amit.gupta 939 
    for r in results:
940 
        row += 1
941 
        column = 0
942 
        for data in r :
943 
            sh1.write(row, column, int(data) if type(data) is float else data, datetime_format if type(data) is datetime else default_format)
944 
            column += 1
945 
        i=-1
946 
        worksheet.write(row, inc(), r[0])
947 
        worksheet.write(row, inc(), r[1])
948 
        worksheet.write(row, inc(), r[2])
949 
        worksheet.write(row, inc(), r[3], datetime_format)
950 
        worksheet.write(row, inc(), r[4])
951 
        worksheet.write(row, inc(), r[5])
952 
        worksheet.write(row, inc(), r[23])
953 
        worksheet.write(row, inc(), r[8])
954 
        worksheet.write(row, inc(), r[10])
955 
        worksheet.write(row, inc(), r[25])
956 
        worksheet.write(row, inc(), r[6])
957 
        worksheet.write(row, inc(), r[7])
958 
        worksheet.write(row, inc(), r[24])
15423 amit.gupta 959 
 
15542 amit.gupta 960 
    flipkartOrders = session.query(FlipkartOrders).order_by(FlipkartOrders.created.desc()).all()
15540 amit.gupta 961 
    flipkartOrders.reverse()
15423 amit.gupta 962 
    row3=0
15430 amit.gupta 963 
    #try:
15540 amit.gupta 964 
    for order in flipkartOrders:
15423 amit.gupta 965 
        row3 += 1
966 
        i=-1
15540 amit.gupta 967 
        sh2.write(row3, inc(), order.user_id)
968 
        sh2.write(row3, inc(), order.email)
969 
        sh2.write(row3, inc(), order.created, datetime_format)
15423 amit.gupta 970 
        sh2.write(row3, inc(), "Flipkart")
15540 amit.gupta 971 
        sh2.write(row3, inc(), order.status)
972 
        sh2.write(row3, inc(), order.title)
973 
        sh2.write(row3, inc(), order.price)
974 
        sh2.write(row3, inc(), order.quantity)
975 
        sh2.write(row3, inc(), order.catalogId)
976 
        sh2.write(row3, inc(), order.brand)
977 
        sh2.write(row3, inc(), order.model)
978 
        sh2.write(row3, inc(), order.category)
14632 amit.gupta 979 
 
980 
    workbook.save(XLS_O_FILENAME)
14647 amit.gupta 981 
 
982 
def getSkuData(storeId, identifier):
16492 amit.gupta 983 
    if storeId in (1,2,4,5,6):
14647 amit.gupta 984 
        skuData = client.Catalog.MasterData.find_one({'identifier':identifier, 'source_id':storeId})
985 
    elif storeId == 3:
986 
        skuData = client.Catalog.MasterData.find_one({'secondaryIdentifier':identifier, 'source_id':storeId})
987 
    return skuData
988 
 
14460 amit.gupta 989 
def inc():
990 
    global i
991 
    i+=1
992 
    return i
15540 amit.gupta 993 
 
994 
def migrateFlipkartOrders():
995 
    db = client.Dtr
996 
    info = reversed(list(db.flipkartOrderAffiliateInfo.find()))
997 
    #try:
998 
    for order in info:
999 
        userId = None
1000 
        subTagId = None
1001 
        email = None
1002 
        subTagId = order.get("subTagId")
1003 
        if subTagId:
1004 
            click = session.query(Clicks).filter_by(tag = subTagId).first()
1005 
            if click is not None:
1006 
                userId= click.user_id
1007 
                user = session.query(Users.email).filter_by(id = userId).first()
1008 
                if user is not None:
1009 
                    email = user.email
1010 
 
1011 
        flipkartOrder = FlipkartOrders()
1012 
        flipkartOrder.user_id = userId
1013 
        flipkartOrder.identifier = order.get("identifier")
1014 
        flipkartOrder.email = email
1015 
        flipkartOrder.subtagId = order.get("subTagId")
1016 
        flipkartOrder.created = datetime.strptime(order.get("saleDate"), "%Y-%m-%d")
1017 
        flipkartOrder.status = order.get("conversionStatus")
1018 
        flipkartOrder.title = order.get("productTitle")
1019 
        flipkartOrder.price = order.get("price")
1020 
        flipkartOrder.quantity = order.get("quantity")
1021 
        flipkartOrder.productCode = order.get("productCode")
1022 
        skuData = getSkuData(2, order.get("productCode"))
1023 
        if skuData is not None:
1024 
            flipkartOrder.catalogId = skuData.get("skuBundleId")
1025 
            flipkartOrder.brand = skuData.get("brand")
1026 
            flipkartOrder.model = skuData.get("model_name")
1027 
            flipkartOrder.category =categoryMap.get(skuData.get("category_id"))
1028 
            flipkartOrder.title =skuData.get("source_product_name")
1029 
 
1030 
    session.commit()
16947 amit.gupta 1031 
 
1032 
 
1033 
def main1():
1034 
    db = client.Dtr
1035 
    for offer in db.snapdealOrderAffiliateInfo.find({}):
1036 
        print offer.get("adId"), offer.get("saleAmount"), type(offer.get("saleAmount"))
1037 
        #obj = offer.get(saleTime)
1038 
        #obj['paidAmount'] += order["paidAmount"]
14632 amit.gupta 1039 
def addHeaders(sheet):
1040 
    global i
1041 
    i = 0
1042 
    sheet.write(0, inc(), )
14450 amit.gupta 1043 
def main():
17161 amit.gupta 1044 
    message = generateSnapDealReco()
17169 amit.gupta 1045 
    generateFlipkartReco()
1046 
    generateShopcluesReco()
1047 
    generateAmazonReco(XLS_A_FILENAME)
1048 
    message1 = getdata.summaryByBrandAndStore()
1049 
    getUserOrders()
1050 
    #sendmail(["amit.gupta@shop2020.in"], message, "Affiliate Reco", XLS_FILENAME)
17137 naman 1051 
    #sendmail(["naman.kumar@shop2020.in"], message1, "All DTR Orders", XLS_O_FILENAME)
17169 amit.gupta 1052 
    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)
1053 
    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 1054 
    #sendmail(["amit.gupta@shop2020.in"], "", "Affiliate Reco", XLS_FILENAME, XLS_F_FILENAME, XLS_A_FILENAME, XLS_SC_FILENAME)
1055 
 
17053 amit.gupta 1056 
def main3():
1057 
    db = client.Dtr
1058 
    #main()
1059 
    mailBodyTemplate="""
1060 
        <html>
1061 
            <body>
1062 
            <table cellspacing="0" border="1" style="text-align:right">
1063 
                <thead>
1064 
                    <tr>
1065 
                        <th style="text-align:center">Date</th>
1066 
                        <th  style="text-align:center">Sale</th>
1067 
                        <th  style="text-align:center">Approved</th>
1068 
                        <th  style="text-align:center">Approved %</th>
1069 
                        <th  style="text-align:center">Rejected</th>
1070 
                        <th  style="text-align:center">Rejected %</th>
1071 
                        <th  style="text-align:center">Missing %</th>
1072 
                        <th  style="text-align:center">Approved % against Sale</th>
1073 
                    </tr>
1074 
                </thead>
1075 
                <tbody>
1076 
                    {0}
1077 
                </tbody>
1078 
            </table>
1079 
            </body>
1080 
        </html>
1081 
    """
1082 
    tbody=[]
1083 
    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>"
1084 
    for offer in db.flSaleSnapshot.find().limit(61).sort("_id",-1):
1085 
        approved = offer.get("approvedAmount")
1086 
        rejected = offer.get("rejectedAmount")
1087 
        sale = offer.get("paidAmount")
1088 
        total = approved + rejected
1089 
        if total ==0:
1090 
            continue
1091 
        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)))
1092 
    message = mailBodyTemplate.format("".join(tbody))
1093 
    print message
17001 amit.gupta 1094 
def main2():
1095 
    db = client.Dtr
17053 amit.gupta 1096 
    curs = getOrdersAfterDate(datetime.now() - timedelta(days=46), 3)
17001 amit.gupta 1097 
    datemap={}
1098 
    for row1 in curs:
1099 
        orderId = row1[0]
1100 
        order = db.merchantOrder.find_one({"orderId":orderId})
1101 
        if order is None:
1102 
            continue
1103 
        #saleTime = int(time.mktime(datetime.strptime(order['placedOn'], "%b %d, %Y, %I:%M %p").timetuple()))
1104 
        try:
1105 
            saleTime = datetime.strptime(order['placedOn'], "%a, %d %b, %Y").strftime("%Y-%m-%d")
1106 
        except:
1107 
            saleTime = datetime.strptime(order['placedOn'], "%b %d, %Y, %I:%M %p").strftime("%Y-%m-%d")
1108 
        print "%s\t%s\t%s\t%s"%(orderId, order['placedOn'], saleTime, order['paidAmount'])
1109 
        if not datemap.has_key(saleTime):
1110 
            datemap[saleTime] = {"totalAmount":0, "paidAmount":0, "rejectedAmount":0, "approvedAmount":0, "count":0}
1111 
 
1112 
        obj = datemap.get(saleTime)
1113 
        obj['paidAmount'] += order["paidAmount"]
1114 
        obj['count'] += 1
1115 
 
17053 amit.gupta 1116 
    for offer in db.snapdealOrderAffiliateInfo1.find({"saleDate":{"$gte":toTimeStamp(datetime.now() - timedelta(days=46))}}):
17001 amit.gupta 1117 
        saleTime =  utils.fromTimeStamp(offer.get('saleDate')).strftime("%Y-%m-%d")
1118 
        obj = datemap.get(saleTime)
1119 
        if obj is not None:
1120 
            try:
1121 
                if offer.get("payOut"):
1122 
                    obj['approvedAmount'] += offer["saleAmount"]
1123 
                else:
1124 
                    obj['rejectedAmount'] += offer["saleAmount"]
1125 
            except:
1126 
                print "Exception for orderId" , offer.get("orderId")
1127 
        else:
1128 
            print "Could not find sale date", saleTime
1129 
            continue
1130 
 
1131 
    for key,valmap in  datemap.iteritems():
1132 
        valmap['_id'] = key
1133 
        db.flSaleSnapshot.save(valmap)
14450 amit.gupta 1134 
 
1135 
if __name__ == '__main__':
17169 amit.gupta 1136 
    getdata.addToAllOrders(date.today()-timedelta(days=20))
1137 
    main2()
17008 amit.gupta 1138 
    main()