Rev 17140 | Rev 17159 | Go to most recent revision | Blame | Compare with Previous | Last modification | View Log | RSS feed
'''Created on Mar 13, 2015@author: amit'''from datetime import date, datetime, timedeltafrom dtr.main import Store, sourceMapfrom dtr.reports.amazonreco import generateAmazonRecofrom dtr.storage import Mysql, DataServicefrom dtr.storage.DataService import Users, Clicks, FlipkartOrders,\All_user_addressesfrom dtr.storage.Mysql import getOrdersAfterDate, getOrdersByTagfrom dtr.utils import utilsfrom elixir import *from sqlalchemy.sql.expression import func, func, or_from email import encodersfrom email.mime.base import MIMEBasefrom email.mime.multipart import MIMEMultipartfrom email.mime.text import MIMETextfrom pymongo.mongo_client import MongoClientfrom xlrd import open_workbookfrom xlutils.copy import copyfrom xlwt.Workbook import Workbookimport _mysqlimport MySQLdbimport smtplibimport timeimport xlwtfrom dtr.utils import utilsfrom dtr.utils.utils import toTimeStampfrom dtr.reports import getdata#from xlwt importDataService.initialize()client = MongoClient('mongodb://localhost:27017/')SENDER = "cnc.center@shop2020.in"PASSWORD = "5h0p2o2o"SUBJECT = "DTR User Segmentation report for " + date.today().isoformat()SMTP_SERVER = "smtp.gmail.com"SMTP_PORT = 587XLS_FILENAME = "snapdealaffiliatereco.xls"XLS_O_FILENAME = "allorders.xls"XLS_F_FILENAME = "flipkartaffiliatereco.xls"XLS_A_FILENAME = "amazonaffiliatereco.xls"XLS_SC_FILENAME = "shopcluesaffiliatereco.xls"XLS_HS_FILENAME = "homeshopaffiliatereco.xls"boldStyle = xlwt.XFStyle()f = xlwt.Font()f.bold = TrueboldStyle.font = fi = -1datetime_format = xlwt.XFStyle()datetime_format.num_format_str = 'dd/mm/yyyy HH:MM AM/PM'categoryMap = {3:"Mobiles", 5:"Tablets"}def generateFlipkartReco():global ii = -1curs = getOrdersAfterDate(datetime.now() - timedelta(days=30), 2)db = client.Dtrwb = xlwt.Workbook()#rb = open_workbook(XLS_F_FILENAME)#wb = copy(rb)#wb = xlwt.Workbook()row = 0row2 = 0row3 = 0row4 = 0worksheet = wb.add_sheet("All Orders")worksheet1 = wb.add_sheet("Orders Reconciled")worksheet2 = wb.add_sheet("Orders not Reconciled")worksheet3 = wb.add_sheet("Aff not Reconciled")worksheet.write(row, inc(), 'Order Id', boldStyle)worksheet1.write(row, i, 'Order Id', boldStyle)worksheet2.write(row, i, 'Order Id', boldStyle)worksheet.write(row, inc(), 'User Id', boldStyle)worksheet1.write(row, i, 'User Id', boldStyle)worksheet2.write(row, i, 'User Id', boldStyle)worksheet.write(row, inc(), 'Username', boldStyle)worksheet1.write(row, i, 'Username', boldStyle)worksheet2.write(row, i, 'Username', boldStyle)worksheet.write(row, inc(), 'Version code', boldStyle)worksheet1.write(row, i, 'Version code', boldStyle)worksheet2.write(row, i, 'Version code', boldStyle)worksheet.write(row, inc(), 'Device', boldStyle)worksheet1.write(row, i, 'Device', boldStyle)worksheet2.write(row, i, 'Device', boldStyle)worksheet.write(row, inc(), 'Merchant Order Id', boldStyle)worksheet1.write(row, i, 'Merchant Order Id', boldStyle)worksheet2.write(row, i, 'Merchant Order Id', boldStyle)worksheet.write(row, inc(), 'Product Title', boldStyle)worksheet1.write(row, i, 'Product Title', boldStyle)worksheet2.write(row, i, 'Product Title', boldStyle)worksheet.write(row, inc(), 'Price', boldStyle)worksheet1.write(row, i, 'Price', boldStyle)worksheet2.write(row, i, 'Price', boldStyle)worksheet.write(row, inc(), 'Quantity', boldStyle)worksheet1.write(row, i, 'Quantity', boldStyle)worksheet2.write(row, i, 'Quantity', boldStyle)worksheet.write(row, inc(), 'Status', boldStyle)worksheet1.write(row, i, 'Status', boldStyle)worksheet2.write(row, i, 'Status', boldStyle)worksheet.write(row, inc(), 'Detailed Status', boldStyle)worksheet1.write(row, i, 'Detailed Status', boldStyle)worksheet2.write(row, i, 'Detailed Status', boldStyle)worksheet.write(row, inc(), 'Cashback Status', boldStyle)worksheet1.write(row, i, 'Cashback Status', boldStyle)worksheet2.write(row, i, 'Cashback Status', boldStyle)worksheet.write(row, inc(), 'Our CashBack', boldStyle)worksheet1.write(row, i, 'Our CashBack', boldStyle)worksheet2.write(row, i, 'Our CashBack', boldStyle)worksheet.write(row, inc(), 'Sale Date', boldStyle)worksheet1.write(row, i, 'Sale Date', boldStyle)worksheet2.write(row, i, 'Sale Date', boldStyle)worksheet.write(row, inc(), 'SubtagId', boldStyle)worksheet1.write(row, i, 'SubtagId', boldStyle)worksheet2.write(row, i, 'SubtagId', boldStyle)worksheet.write(row, inc(), 'Category', boldStyle)worksheet1.write(row, i, 'Category', boldStyle)worksheet.write(row, inc(), 'Aff PayOut', boldStyle)worksheet1.write(row, i, 'Aff PayOut', boldStyle)worksheet.write(row, inc(), 'Aff Sale Amount', boldStyle)worksheet1.write(row, i, 'Aff Sale Amount', boldStyle)worksheet.write(row, inc(), 'Aff Sale Date', boldStyle)worksheet1.write(row, i, 'Aff Sale Date', boldStyle)for row1 in curs:orderId = row1[0]order = db.merchantOrder.find_one({"orderId":orderId})if order is None:continuesaleTime = int(time.mktime(datetime.strptime(order['placedOn'], "%d %B, %Y %I:%M %p").replace(hour=0, minute=0, second=0, microsecond=0).timetuple()))#saleTime1 = int(time.mktime((datetime.strptime(order['placedOn'], "%b %d, %Y %I:%M %p") + timedelta(seconds=-60)).timetuple()))reconciled_affiliates = []for subOrder in order['subOrders']:affs = list(db.flipkartOrderAffiliateInfo.find({"productCode":subOrder.get("productCode"), "saleDateInt":saleTime}))row += 1i=-1worksheet.write(row, inc(), orderId)worksheet.write(row, inc(), row1[1])worksheet.write(row, inc(), row1[-1])worksheet.write(row, inc(), row1[-2])worksheet.write(row, inc(), row1[-3])worksheet.write(row, inc(), order['merchantOrderId'])worksheet.write(row, inc(), subOrder['productTitle'])worksheet.write(row, inc(), subOrder['amountPaid'])worksheet.write(row, inc(), subOrder['quantity'])worksheet.write(row, inc(), subOrder['status'])worksheet.write(row, inc(), subOrder['detailedStatus'])worksheet.write(row, inc(), subOrder['cashBackStatus'])worksheet.write(row, inc(), subOrder['cashBackAmount'])worksheet.write(row, inc(), subOrder['placedOn'])worksheet.write(row, inc(), order['subTagId'])for aff in affs:if aff["_id"] not in reconciled_affiliates and aff['subTagId']== order['subTagId']:reconciled_affiliates.append(aff["_id"])row2 += 1i = -1worksheet1.write(row2, inc(), orderId)worksheet1.write(row2, inc(), row1[1])worksheet1.write(row2, inc(), row1[-1])worksheet1.write(row2, inc(), row1[-2])worksheet1.write(row2, inc(), row1[-3])worksheet1.write(row2, inc(), order['merchantOrderId'])worksheet1.write(row2, inc(), subOrder['productTitle'])worksheet1.write(row2, inc(), subOrder['amountPaid'])worksheet1.write(row2, inc(), subOrder['quantity'])worksheet1.write(row2, inc(), subOrder['status'])worksheet1.write(row2, inc(), subOrder['detailedStatus'])worksheet1.write(row2, inc(), subOrder['cashBackStatus'])worksheet1.write(row2, inc(), subOrder['cashBackAmount'])worksheet1.write(row2, inc(), subOrder['placedOn'])worksheet1.write(row2, inc(), order['subTagId'])db.flipkartOrderAffiliateInfo.update({"productCode":subOrder.get("productCode"), "saleDateInt":saleTime, "subTagId":aff['subTagId']},{"$set":{"reconciled":True}})db.merchantOrder.update({"merchantOrderId":order["merchantOrderId"]},{"$set":{"reconciled":True}})worksheet.write(row, inc(), aff['category'])worksheet1.write(row2, i, aff['category'])worksheet.write(row, inc(), aff['payOut'])worksheet1.write(row2, i, aff['payOut'])worksheet.write(row, inc(), aff['saleAmount'])worksheet1.write(row2, i, aff['saleAmount'])worksheet.write(row, inc(), aff['saleDate'])worksheet1.write(row2, i, aff['saleDate'])breakrow3 += 1i=-1worksheet2.write(row3, inc(), orderId)worksheet2.write(row3, inc(), row1[1])worksheet2.write(row3, inc(), row1[-1])worksheet2.write(row3, inc(), row1[-2])worksheet2.write(row3, inc(), row1[-3])worksheet2.write(row3, inc(), order['merchantOrderId'])worksheet2.write(row3, inc(), subOrder['productTitle'])worksheet2.write(row3, inc(), subOrder['amountPaid'])worksheet2.write(row3, inc(), subOrder['quantity'])worksheet2.write(row3, inc(), subOrder['status'])worksheet2.write(row3, inc(), subOrder['detailedStatus'])worksheet2.write(row3, inc(), subOrder['cashBackStatus'])worksheet2.write(row3, inc(), subOrder['cashBackAmount'])worksheet2.write(row3, inc(), subOrder['placedOn'])worksheet2.write(row3, inc(), order['subTagId'])i=-1worksheet3.write(0, inc(), "category",boldStyle)worksheet3.write(0, inc(), "conversionStatus",boldStyle)worksheet3.write(0, inc(), "Product Title",boldStyle)worksheet3.write(0, inc(), "price",boldStyle)worksheet3.write(0, inc(), "quantity",boldStyle)worksheet3.write(0, inc(), "payOut",boldStyle)worksheet3.write(0, inc(), "saleDate",boldStyle)worksheet3.write(0, inc(), "subTagId",boldStyle)for aff in db.flipkartOrderAffiliateInfo.find({"reconciled":{"$exists":False}}):row4 += 1i =-1worksheet3.write(row4, inc(), aff["category"])worksheet3.write(row4, inc(), aff["conversionStatus"])skuData = getSkuData(2, aff["productCode"])if skuData is None:worksheet3.write(row4, inc(), aff["productTitle"])else:worksheet3.write(row4, inc(), skuData["product_name"])worksheet3.write(row4, inc(), aff['price'])worksheet3.write(row4, inc(), aff['quantity'])worksheet3.write(row4, inc(), aff['payOut'])worksheet3.write(row4, inc(), aff['saleDate'])worksheet3.write(row4, inc(), aff['subTagId'])wb.save(XLS_F_FILENAME)def generateSnapDealReco():global icurs = getOrdersAfterDate(datetime.now() - timedelta(days=30), 3)db = client.DtrnotReconciled = {}matchedList = []workbook = xlwt.Workbook()worksheet = workbook.add_sheet("Snapdeal Reconciled")worksheet1 = workbook.add_sheet("Snapdeal Reconciled All")worksheet2 = workbook.add_sheet("Snapdeal Orders not reconciled")worksheet3 = workbook.add_sheet("DateWise Sale Summary")affNotReconciledSheet = workbook.add_sheet("Snapdeal Aff not reconciled")setHeaders(worksheet, worksheet1, worksheet2, affNotReconciledSheet, worksheet3)row = 0anotherrow = 0row5=0datemap={}for row1 in curs:orderId = row1[0]order = db.merchantOrder.find_one({"orderId":orderId})if order is None:continue#saleTime = int(time.mktime(datetime.strptime(order['placedOn'], "%b %d, %Y, %I:%M %p").timetuple()))try:saleTime = datetime.strptime(order['placedOn'], "%a, %d %b, %Y").strftime("%Y-%m-%d")except:saleTime = datetime.strptime(order['placedOn'], "%b %d, %Y, %I:%M %p").strftime("%Y-%m-%d")aff = list(db.snapdealOrderAffiliateInfo.find({"subTagId":order["subTagId"]}))if len(aff) > 1:aff = [f for f in aff if f.get("payOut")>0]anotherrow += 1try:formattedTimestamp = order['placedOn']timestamp1 = int(time.mktime(datetime.strptime(order['placedOn'], "%b %d, %Y, %I:%M %p").timetuple()))except:timestamp1 = int(time.mktime(row1[-4].timetuple()))formattedTimestamp = datetime.strftime(utils.fromTimeStamp(timestamp1), "%b %d, %Y, %I:%M %p")if len(aff) > 0 and order["subTagId"] not in matchedList and int(order["paidAmount"])==int(aff[0]["saleAmount"]):matchedList.append(order["subTagId"])db.snapdealOrderAffiliateInfo.update({"adId":aff[0].get("adId")}, {"$set":{"reconciled":True, "orderId":orderId}}, multi=True)order['reconciled'] = Trueorder['adId'] = aff[0].get("adId")db.merchantOrder.save(order)row += 1i=-1worksheet.write(row, inc(), orderId)worksheet1.write(anotherrow, i, orderId)worksheet.write(row, inc(), row1[1])worksheet1.write(anotherrow, i, row1[1])worksheet.write(row, inc(), row1[-1])worksheet1.write(anotherrow, i, row1[-1])worksheet.write(row, inc(), row1[-2])worksheet1.write(anotherrow, i, row1[-2])worksheet.write(row, inc(), row1[-3])worksheet1.write(anotherrow, i, row1[-3])worksheet.write(row, inc(), order['merchantOrderId'])worksheet1.write(anotherrow, i, order['merchantOrderId'])worksheet.write(row, inc(), order['subTagId'])worksheet1.write(anotherrow, i, order['subTagId'])worksheet.write(row, inc(), order['placedOn'])worksheet1.write(anotherrow, i, order['placedOn'])worksheet.write(row, inc(), formattedTimestamp)worksheet1.write(anotherrow, i, formattedTimestamp)worksheet.write(row, inc(), int(order['paidAmount']))worksheet1.write(anotherrow, i, int(order['paidAmount']))worksheet.write(row, inc(), aff[0]['saleDate'])worksheet1.write(anotherrow, i, aff[0]['saleDate'])worksheet.write(row, inc(), aff[0]['saleAmount'])worksheet1.write(anotherrow, i, aff[0]['saleAmount'])worksheet.write(row, inc(), aff[0]['payOut'])worksheet1.write(anotherrow, i, aff[0]['payOut'])worksheet.write(row, inc(), aff[0]['ip'])worksheet1.write(anotherrow, i, aff[0]['ip'])worksheet.write(row, inc(), aff[0]['conversionStatus'])worksheet1.write(anotherrow, i, aff[0]['conversionStatus'])k = irow -= 1anotherrow -= 1for subOrder in order['subOrders']:i = krow += 1anotherrow += 1worksheet.write(row, inc(), subOrder['productTitle'])worksheet1.write(anotherrow, i, subOrder['productTitle'])worksheet.write(row, inc(), subOrder['amountPaid'])worksheet1.write(anotherrow, i, subOrder['amountPaid'])worksheet.write(row, inc(), subOrder['quantity'])worksheet1.write(anotherrow, i, subOrder['quantity'])worksheet.write(row, inc(), subOrder['status'])worksheet1.write(anotherrow, i, subOrder['status'])worksheet.write(row, inc(), subOrder['cashBackStatus'])worksheet1.write(anotherrow, i, subOrder['cashBackStatus'])worksheet.write(row, inc(), subOrder['cashBackAmount'])worksheet1.write(anotherrow, i, subOrder['cashBackAmount'])else:i=-1worksheet1.write(anotherrow, inc(), orderId)worksheet1.write(anotherrow, inc(), row1[1])worksheet1.write(anotherrow, inc(), row1[-1])worksheet1.write(anotherrow, inc(), row1[-2])worksheet1.write(anotherrow, inc(), row1[-3])worksheet1.write(anotherrow, inc(), order['merchantOrderId'])worksheet1.write(anotherrow, inc(), order['subTagId'])worksheet1.write(anotherrow, inc(), order['placedOn'])worksheet1.write(anotherrow, inc(), formattedTimestamp)worksheet1.write(anotherrow, inc(), int(order['paidAmount']))notReconciled[orderId] = (anotherrow, i, int(order['paidAmount']), timestamp1, order["subTagId"])inc()inc()inc()inc()inc()k = ianotherrow -= 1for subOrder in order['subOrders']:anotherrow += 1i = kworksheet1.write(anotherrow, inc(), subOrder['productTitle'])worksheet1.write(anotherrow, inc(), subOrder['amountPaid'])worksheet1.write(anotherrow, inc(), subOrder['quantity'])worksheet1.write(anotherrow, inc(), subOrder['status'])worksheet1.write(anotherrow, inc(), subOrder['cashBackStatus'])worksheet1.write(anotherrow, inc(), subOrder['cashBackAmount'])row5 += 1i=-1worksheet2.write(row5, inc(), order["orderId"])worksheet2.write(row5, inc(), row1[1])worksheet2.write(row5, inc(), row1[-1])worksheet2.write(row5, inc(), row1[-2])worksheet2.write(row5, inc(), row1[-3])worksheet2.write(row5, inc(), order['merchantOrderId'])worksheet2.write(row5, inc(), order['subTagId'])worksheet2.write(row5, inc(), order['placedOn'])worksheet2.write(row5, inc(), formattedTimestamp)worksheet2.write(row5, inc(), int(order['paidAmount']))row5 -=1k=ifor subOrder in order['subOrders']:i = krow5 += 1worksheet2.write(row5, inc(), subOrder['productTitle'])worksheet2.write(row5, inc(), subOrder['amountPaid'])worksheet2.write(row5, inc(), subOrder['quantity'])worksheet2.write(row5, inc(), subOrder['status'])worksheet2.write(row5, inc(), subOrder['cashBackStatus'])worksheet2.write(row5, inc(), subOrder['cashBackAmount'])last30Days = date.today() - timedelta(days=30)int(time.mktime(last30Days.timetuple()))row = 0for offer in db.snapdealOrderAffiliateInfo.find({"reconciled":{"$exists":False},"saleTime":{"$gt":int(time.mktime(last30Days.timetuple())),"$lt":int(time.mktime(date.today().timetuple()))}}, timeout=False ):subTagId = offer.get("subTagId")saleTime = offer.get("saleTime")orders = getOrdersByTag(subTagId, 3)for order in orders:if notReconciled.has_key(order[0]):(roww, column, paidAmount, timestamp1, oSubTagId) = notReconciled[order[0]]if paidAmount==int(offer.get("saleAmount")) and timestamp1 <= saleTime and timestamp1 + 120 >= saleTime:i = columnworksheet1.write(roww, inc(), offer.get("saleDate"))worksheet1.write(roww, inc(), offer.get("saleAmount"))worksheet1.write(roww, inc(), offer.get("payOut"))del notReconciled[order[0]]print "found match for user", order[1]db.snapdealOrderAffiliateInfo.update({"adId":offer.get("adId")}, {"$set":{"reconciled":True, "orderId":order[0]}}, multi=True)db.merchantOrder.update({"orderId":order[0]}, {"$set":{"reconciled":True, "adId":offer.get("adId")}}, multi=True)breakfor offer in db.snapdealOrderAffiliateInfo.find({"reconciled":{"$exists":False},"saleTime":{"$gt":int(time.mktime(last30Days.timetuple())),"$lt":int(time.mktime(date.today().timetuple()))}} ):row += 1i=-1affNotReconciledSheet.write(row, inc(), offer.get("adId"))affNotReconciledSheet.write(row, inc(), offer.get("subTagId"))affNotReconciledSheet.write(row, inc(), offer.get("saleDate"))affNotReconciledSheet.write(row, inc(), offer.get("saleAmount"))affNotReconciledSheet.write(row, inc(), offer.get("payOut"))affNotReconciledSheet.write(row, inc(), offer.get("conversionStatus"))affNotReconciledSheet.write(row, inc(), offer.get("ip"))mailBodyTemplate="""<html><body><table cellspacing="0" border="1" style="text-align:right"><thead><tr><th style="text-align:center">Date</th><th style="text-align:center">Sale</th><th style="text-align:center">Approved</th><th style="text-align:center">Approved %</th><th style="text-align:center">Rejected</th><th style="text-align:center">Rejected %</th><th style="text-align:center">Missing %</th><th style="text-align:center">Approved % against Sale</th></tr></thead><tbody>{0}</tbody></table></body></html>"""tbody=[]rowtemplate="<tr><td>{0}</td><td>{1}</td><td>{2}</td><td>{3}</td><td>{4}</td><td>{5}</td><td>{6}</td><td>{7}</td></tr>"for offer in db.flSaleSnapshot.find().limit(61).sort("_id",-1):approved = offer.get("approvedAmount")rejected = offer.get("rejectedAmount")sale = offer.get("paidAmount")total = approved + rejectedif total ==0:continuetbody.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)))message = mailBodyTemplate.format("".join(tbody))workbook.save(XLS_FILENAME)return messagedef generateHomeShopReco():global icurs = getOrdersAfterDate(datetime.now() - timedelta(days=30), 7)db = client.DtrnotReconciled = {}matchedList = []workbook = xlwt.Workbook()worksheet = workbook.add_sheet("HomeShop18 Reconciled")worksheet1 = workbook.add_sheet("HomeShop18 Reconciled All")worksheet2 = workbook.add_sheet("HomeShop18 Orders not reconciled")affNotReconciledSheet = workbook.add_sheet("HomeShop18 Aff not reconciled")setHeaders(worksheet, worksheet1, worksheet2, affNotReconciledSheet)row = 0anotherrow = 0row2 = 0row5=0for row1 in curs:orderId = row1[0]order = db.merchantOrder.find_one({"orderId":orderId})if order is None:continue#saleTime = int(time.mktime(datetime.strptime(order['placedOn'], "%b %d, %Y, %I:%M %p").timetuple())) 26 May 2015 02:12 PMaff = list(db.shopcluesOrderAffiliateInfo.find({"merchantOrderId":order["merchantOrderId"]}))anotherrow += 1try:formattedTimestamp = order['placedOn']timestamp1 = int(time.mktime(datetime.strptime(order['placedOn'], "%d %b %Y %I:%M %p").timetuple()))except:timestamp1 = int(time.mktime(row1[-4].timetuple()))formattedTimestamp = datetime.strftime(utils.fromTimeStamp(timestamp1), "%d %b %Y %I:%M %p")if len(aff) > 0 and order["subTagId"] not in matchedList and long(order["paidAmount"])==long(float(aff[0]["transactionValue"])):matchedList.append(order["subTagId"])db.shopcluesOrderAffiliateInfo.update({"merchantOrderId":aff[0].get("merchantOrderId")}, {"$set":{"reconciled":True, "orderId":orderId}}, multi=True)order['reconciled'] = Trueorder['adId'] = aff[0].get("uniqueKey")db.merchantOrder.save(order)row += 1i=-1worksheet.write(row, inc(), orderId)worksheet1.write(anotherrow, i, orderId)worksheet.write(row, inc(), row1[1])worksheet1.write(anotherrow, i, row1[1])worksheet.write(row, inc(), row1[-1])worksheet1.write(anotherrow, i, row1[-1])worksheet.write(row, inc(), row1[-2])worksheet1.write(anotherrow, i, row1[-2])worksheet.write(row, inc(), row1[-3])worksheet1.write(anotherrow, i, row1[-3])worksheet.write(row, inc(), order['merchantOrderId'])worksheet1.write(anotherrow, i, order['merchantOrderId'])worksheet.write(row, inc(), order['subTagId'])worksheet1.write(anotherrow, i, order['subTagId'])worksheet.write(row, inc(), order['placedOn'])worksheet1.write(anotherrow, i, order['placedOn'])worksheet.write(row, inc(), formattedTimestamp)worksheet1.write(anotherrow, i, formattedTimestamp)worksheet.write(row, inc(), int(order['paidAmount']))worksheet1.write(anotherrow, i, int(order['paidAmount']))worksheet.write(row, inc(), datetime.strftime(utils.fromTimeStamp(aff[0]['transactionTime']), "%d %b %Y %I:%M %p"))worksheet1.write(anotherrow, i, datetime.strftime(utils.fromTimeStamp(aff[0]['transactionTime']), "%d %b %Y %I:%M %p"))worksheet.write(row, inc(), aff[0]['transactionValue'])worksheet1.write(anotherrow, i, aff[0]['transactionValue'])worksheet.write(row, inc(), aff[0]['payOut'])worksheet1.write(anotherrow, i, aff[0]['payOut'])worksheet.write(row, inc(), "NA")worksheet1.write(anotherrow, i, "NA")k = irow -= 1anotherrow -= 1for subOrder in order['subOrders']:i = krow += 1anotherrow += 1worksheet.write(row, inc(), subOrder['productTitle'])worksheet1.write(anotherrow, i, subOrder['productTitle'])worksheet.write(row, inc(), subOrder['amountPaid'])worksheet1.write(anotherrow, i, subOrder['amountPaid'])worksheet.write(row, inc(), subOrder['quantity'])worksheet1.write(anotherrow, i, subOrder['quantity'])worksheet.write(row, inc(), subOrder['status'])worksheet1.write(anotherrow, i, subOrder['status'])worksheet.write(row, inc(), subOrder['cashBackStatus'])worksheet1.write(anotherrow, i, subOrder['cashBackStatus'])worksheet.write(row, inc(), subOrder['cashBackAmount'])worksheet1.write(anotherrow, i, subOrder['cashBackAmount'])else:i=-1worksheet1.write(anotherrow, inc(), orderId)worksheet1.write(anotherrow, inc(), row1[1])worksheet1.write(anotherrow, inc(), row1[-1])worksheet1.write(anotherrow, inc(), row1[-2])worksheet1.write(anotherrow, inc(), row1[-3])worksheet1.write(anotherrow, inc(), order['merchantOrderId'])worksheet1.write(anotherrow, inc(), order['subTagId'])worksheet1.write(anotherrow, inc(), order['placedOn'])worksheet1.write(anotherrow, inc(), formattedTimestamp)worksheet1.write(anotherrow, inc(), int(order['paidAmount']))notReconciled[orderId] = (anotherrow, i, int(order['paidAmount']), timestamp1, order["subTagId"])inc()inc()inc()inc()k = ianotherrow -= 1for subOrder in order['subOrders']:anotherrow += 1i = kworksheet1.write(anotherrow, inc(), subOrder['productTitle'])worksheet1.write(anotherrow, inc(), subOrder['amountPaid'])worksheet1.write(anotherrow, inc(), subOrder['quantity'])worksheet1.write(anotherrow, inc(), subOrder['status'])worksheet1.write(anotherrow, inc(), subOrder['cashBackStatus'])worksheet1.write(anotherrow, inc(), subOrder['cashBackAmount'])row5 += 1i=-1worksheet2.write(row5, inc(), order["orderId"])worksheet2.write(row5, inc(), row1[1])worksheet2.write(row5, inc(), row1[-1])worksheet2.write(row5, inc(), row1[-2])worksheet2.write(row5, inc(), row1[-3])worksheet2.write(row5, inc(), order['merchantOrderId'])worksheet2.write(row5, inc(), order['subTagId'])worksheet2.write(row5, inc(), order['placedOn'])worksheet2.write(row5, inc(), formattedTimestamp)worksheet2.write(row5, inc(), int(order['paidAmount']))row5 -=1k=ifor subOrder in order['subOrders']:i = krow5 += 1worksheet2.write(row5, inc(), subOrder['productTitle'])worksheet2.write(row5, inc(), subOrder['amountPaid'])worksheet2.write(row5, inc(), subOrder['quantity'])worksheet2.write(row5, inc(), subOrder['status'])worksheet2.write(row5, inc(), subOrder['cashBackStatus'])worksheet2.write(row5, inc(), subOrder['cashBackAmount'])last30Days = date.today() - timedelta(days=30)int(time.mktime(last30Days.timetuple()))row = 0for offer in db.homeshopOrderAffiliateInfo.find({"reconciled":{"$exists":False},"transactionTime":{"$gt":int(time.mktime(last30Days.timetuple())),"$lt":int(time.mktime(date.today().timetuple()))}} ):subTagId = offer.get("subTagId")saleTime = offer.get("transactionTime")orders = getOrdersByTag(subTagId, 7)for order in orders:if notReconciled.has_key(order[0]):(roww, column, paidAmount, timestamp1, oSubTagId) = notReconciled[order[0]]if paidAmount==int(offer.get("transactionValue")) and timestamp1 <= saleTime and timestamp1 + 120 >= saleTime:i = columnworksheet1.write(roww, inc(), datetime.strftime(utils.fromTimeStamp(offer.get("transactionTime")), "%d %b %Y %I:%M %p"))worksheet1.write(roww, inc(), offer.get("transactionValue"))worksheet1.write(roww, inc(), offer.get("payOut"))del notReconciled[order[0]]print "found match for user", order[1]db.homeshopOrderAffiliateInfo.update({"transactionId":offer.get("transactionId")}, {"$set":{"reconciled":True, "orderId":order[0]}}, multi=True)db.merchantOrder.update({"orderId":order[0]}, {"$set":{"reconciled":True, "adId":offer.get("uniqueKey")}}, multi=True)breakunreconciledOrders = notReconciled.keys()for offer in db.homeshopOrderAffiliateInfo.find({"reconciled":{"$exists":False},"transactionTime":{"$gt":int(time.mktime(last30Days.timetuple())),"$lt":int(time.mktime(date.today().timetuple()))}} ):row += 1i=-1affNotReconciledSheet.write(row, inc(), offer.get("uniqueKey"))affNotReconciledSheet.write(row, inc(), offer.get("subTagId"))affNotReconciledSheet.write(row, inc(), datetime.strftime(utils.fromTimeStamp(offer.get("transactionTime")), "%d %b %Y %I:%M %p"))affNotReconciledSheet.write(row, inc(), offer.get("transactionValue"))affNotReconciledSheet.write(row, inc(), offer.get("payOut"))affNotReconciledSheet.write(row, inc(), offer.get("status"))affNotReconciledSheet.write(row, inc(), 'NA')workbook.save(XLS_HS_FILENAME)def generateShopcluesReco():global icurs = getOrdersAfterDate(datetime.now() - timedelta(days=30), 5)db = client.DtrnotReconciled = {}matchedList = []workbook = xlwt.Workbook()worksheet = workbook.add_sheet("Shopclues Reconciled")worksheet1 = workbook.add_sheet("Shopclues Reconciled All")worksheet2 = workbook.add_sheet("Shopclues Orders not reconciled")affNotReconciledSheet = workbook.add_sheet("Shopclues Aff not reconciled")setHeaders(worksheet, worksheet1, worksheet2, affNotReconciledSheet)row = 0anotherrow = 0row2 = 0row5=0for row1 in curs:orderId = row1[0]order = db.merchantOrder.find_one({"orderId":orderId})if order is None:continue#saleTime = int(time.mktime(datetime.strptime(order['placedOn'], "%b %d, %Y, %I:%M %p").timetuple())) 26 May 2015 02:12 PMaff = list(db.shopcluesOrderAffiliateInfo.find({"merchantOrderId":order["merchantOrderId"]}))anotherrow += 1try:formattedTimestamp = order['placedOn']timestamp1 = int(time.mktime(datetime.strptime(order['placedOn'], "%d %b %Y %I:%M %p").timetuple()))except:timestamp1 = int(time.mktime(row1[-4].timetuple()))formattedTimestamp = datetime.strftime(utils.fromTimeStamp(timestamp1), "%d %b %Y %I:%M %p")if len(aff) > 0 and order["subTagId"] not in matchedList and long(order["paidAmount"])==long(float(aff[0]["transactionValue"])):matchedList.append(order["subTagId"])db.shopcluesOrderAffiliateInfo.update({"merchantOrderId":aff[0].get("merchantOrderId")}, {"$set":{"reconciled":True, "orderId":orderId}}, multi=True)order['reconciled'] = Trueorder['adId'] = aff[0].get("uniqueKey")db.merchantOrder.save(order)row += 1i=-1worksheet.write(row, inc(), orderId)worksheet1.write(anotherrow, i, orderId)worksheet.write(row, inc(), row1[1])worksheet1.write(anotherrow, i, row1[1])worksheet.write(row, inc(), row1[-1])worksheet1.write(anotherrow, i, row1[-1])worksheet.write(row, inc(), row1[-2])worksheet1.write(anotherrow, i, row1[-2])worksheet.write(row, inc(), row1[-3])worksheet1.write(anotherrow, i, row1[-3])worksheet.write(row, inc(), order['merchantOrderId'])worksheet1.write(anotherrow, i, order['merchantOrderId'])worksheet.write(row, inc(), order['subTagId'])worksheet1.write(anotherrow, i, order['subTagId'])worksheet.write(row, inc(), order['placedOn'])worksheet1.write(anotherrow, i, order['placedOn'])worksheet.write(row, inc(), formattedTimestamp)worksheet1.write(anotherrow, i, formattedTimestamp)worksheet.write(row, inc(), int(order['paidAmount']))worksheet1.write(anotherrow, i, int(order['paidAmount']))worksheet.write(row, inc(), datetime.strftime(utils.fromTimeStamp(aff[0]['transactionTime']), "%d %b %Y %I:%M %p"))worksheet1.write(anotherrow, i, datetime.strftime(utils.fromTimeStamp(aff[0]['transactionTime']), "%d %b %Y %I:%M %p"))worksheet.write(row, inc(), aff[0]['transactionValue'])worksheet1.write(anotherrow, i, aff[0]['transactionValue'])worksheet.write(row, inc(), aff[0]['payOut'])worksheet1.write(anotherrow, i, aff[0]['payOut'])worksheet.write(row, inc(), "NA")worksheet1.write(anotherrow, i, "NA")k = irow -= 1anotherrow -= 1for subOrder in order['subOrders']:i = krow += 1anotherrow += 1worksheet.write(row, inc(), subOrder['productTitle'])worksheet1.write(anotherrow, i, subOrder['productTitle'])worksheet.write(row, inc(), subOrder['amountPaid'])worksheet1.write(anotherrow, i, subOrder['amountPaid'])worksheet.write(row, inc(), subOrder['quantity'])worksheet1.write(anotherrow, i, subOrder['quantity'])worksheet.write(row, inc(), subOrder['status'])worksheet1.write(anotherrow, i, subOrder['status'])worksheet.write(row, inc(), subOrder['cashBackStatus'])worksheet1.write(anotherrow, i, subOrder['cashBackStatus'])worksheet.write(row, inc(), subOrder['cashBackAmount'])worksheet1.write(anotherrow, i, subOrder['cashBackAmount'])else:i=-1worksheet1.write(anotherrow, inc(), orderId)worksheet1.write(anotherrow, inc(), row1[1])worksheet1.write(anotherrow, inc(), row1[-1])worksheet1.write(anotherrow, inc(), row1[-2])worksheet1.write(anotherrow, inc(), row1[-3])worksheet1.write(anotherrow, inc(), order['merchantOrderId'])worksheet1.write(anotherrow, inc(), order['subTagId'])worksheet1.write(anotherrow, inc(), order['placedOn'])worksheet1.write(anotherrow, inc(), formattedTimestamp)worksheet1.write(anotherrow, inc(), int(order['paidAmount']))notReconciled[orderId] = (anotherrow, i, int(order['paidAmount']), timestamp1, order["subTagId"])inc()inc()inc()inc()k = ianotherrow -= 1for subOrder in order['subOrders']:anotherrow += 1i = kworksheet1.write(anotherrow, inc(), subOrder['productTitle'])worksheet1.write(anotherrow, inc(), subOrder['amountPaid'])worksheet1.write(anotherrow, inc(), subOrder['quantity'])worksheet1.write(anotherrow, inc(), subOrder['status'])worksheet1.write(anotherrow, inc(), subOrder['cashBackStatus'])worksheet1.write(anotherrow, inc(), subOrder['cashBackAmount'])row5 += 1i=-1worksheet2.write(row5, inc(), order["orderId"])worksheet2.write(row5, inc(), row1[1])worksheet2.write(row5, inc(), row1[-1])worksheet2.write(row5, inc(), row1[-2])worksheet2.write(row5, inc(), row1[-3])worksheet2.write(row5, inc(), order['merchantOrderId'])worksheet2.write(row5, inc(), order['subTagId'])worksheet2.write(row5, inc(), order['placedOn'])worksheet2.write(row5, inc(), formattedTimestamp)worksheet2.write(row5, inc(), int(order['paidAmount']))row5 -=1k=ifor subOrder in order['subOrders']:i = krow5 += 1worksheet2.write(row5, inc(), subOrder['productTitle'])worksheet2.write(row5, inc(), subOrder['amountPaid'])worksheet2.write(row5, inc(), subOrder['quantity'])worksheet2.write(row5, inc(), subOrder['status'])worksheet2.write(row5, inc(), subOrder['cashBackStatus'])worksheet2.write(row5, inc(), subOrder['cashBackAmount'])last30Days = date.today() - timedelta(days=30)int(time.mktime(last30Days.timetuple()))row = 0for offer in db.shopcluesOrderAffiliateInfo.find({"reconciled":{"$exists":False},"transactionTime":{"$gt":int(time.mktime(last30Days.timetuple())),"$lt":int(time.mktime(date.today().timetuple()))}} ):subTagId = offer.get("subTagId")saleTime = offer.get("transactionTime")orders = getOrdersByTag(subTagId, 5)for order in orders:if notReconciled.has_key(order[0]):(roww, column, paidAmount, timestamp1, oSubTagId) = notReconciled[order[0]]if paidAmount==int(offer.get("transactionValue")) and timestamp1 <= saleTime and timestamp1 + 120 >= saleTime:i = columnworksheet1.write(roww, inc(), datetime.strftime(utils.fromTimeStamp(offer.get("transactionTime")), "%d %b %Y %I:%M %p"))worksheet1.write(roww, inc(), offer.get("transactionValue"))worksheet1.write(roww, inc(), offer.get("payOut"))del notReconciled[order[0]]print "found match for user", order[1]db.shopcluesOrderAffiliateInfo.update({"transactionId":offer.get("transactionId")}, {"$set":{"reconciled":True, "orderId":order[0]}}, multi=True)db.merchantOrder.update({"orderId":order[0]}, {"$set":{"reconciled":True, "adId":offer.get("uniqueKey")}}, multi=True)breakunreconciledOrders = notReconciled.keys()for offer in db.shopcluesOrderAffiliateInfo.find({"reconciled":{"$exists":False},"transactionTime":{"$gt":int(time.mktime(last30Days.timetuple())),"$lt":int(time.mktime(date.today().timetuple()))}} ):row += 1i=-1affNotReconciledSheet.write(row, inc(), offer.get("uniqueKey"))affNotReconciledSheet.write(row, inc(), offer.get("subTagId"))affNotReconciledSheet.write(row, inc(), datetime.strftime(utils.fromTimeStamp(offer.get("transactionTime")), "%d %b %Y %I:%M %p"))affNotReconciledSheet.write(row, inc(), offer.get("transactionValue"))affNotReconciledSheet.write(row, inc(), offer.get("payOut"))affNotReconciledSheet.write(row, inc(), offer.get("status"))affNotReconciledSheet.write(row, inc(), 'NA')workbook.save(XLS_SC_FILENAME)def sendmail(email, message, title, *varargs):if email == "":returnmailServer = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)mailServer.ehlo()mailServer.starttls()mailServer.ehlo()# Create the container (outer) email message.msg = MIMEMultipart()msg['Subject'] = titlemsg.preamble = titlehtml_msg = MIMEText(message, 'html')msg.attach(html_msg)#snapdeal more to be added herefor fileName in varargs:snapdeal = MIMEBase('application', 'vnd.ms-excel')snapdeal.set_payload(file(fileName).read())encoders.encode_base64(snapdeal)snapdeal.add_header('Content-Disposition', 'attachment;filename=' + fileName)msg.attach(snapdeal)email.append('amit.gupta@shop2020.in')MAILTO = emailmailServer.login(SENDER, PASSWORD)mailServer.sendmail(PASSWORD, MAILTO, msg.as_string())def setHeaders(worksheet, worksheet1, worksheet2, affNotReconciledSheet, worksheet3=None):boldStyle = xlwt.XFStyle()f = xlwt.Font()f.bold = TrueboldStyle.font = frow = 0global ii=-1worksheet.write(row, inc(), 'Order Id', boldStyle)worksheet.write(row, inc(), 'User Id', boldStyle)worksheet.write(row, inc(), 'Username', boldStyle)worksheet.write(row, inc(), 'Version Code', boldStyle)worksheet.write(row, inc(), 'Device', boldStyle)worksheet.write(row, inc(), 'Merchant Order Id', boldStyle)worksheet.write(row, inc(), 'SubtagId', boldStyle)worksheet.write(row, inc(), 'Sale Date', boldStyle)worksheet.write(row, inc(), 'Dtr Sale Date', boldStyle)worksheet.write(row, inc(), 'Sale Amount', boldStyle)worksheet.write(row, inc(), 'Aff Sale Date', boldStyle)worksheet.write(row, inc(), 'Aff Sale Amt', boldStyle)worksheet.write(row, inc(), 'Aff PayOut', boldStyle)worksheet.write(row, inc(), 'IP', boldStyle)worksheet.write(row, inc(), 'Aff Status', boldStyle)worksheet.write(row, inc(), 'Product Title', boldStyle)worksheet.write(row, inc(), 'Price', boldStyle)worksheet.write(row, inc(), 'Quantity', boldStyle)worksheet.write(row, inc(), 'Status', boldStyle)worksheet.write(row, inc(), 'Cashback Status', boldStyle)worksheet.write(row, inc(), 'CashBack', boldStyle)i = -1worksheet1.write(row, inc(), 'Order Id', boldStyle)worksheet1.write(row, inc(), 'User Id', boldStyle)worksheet1.write(row, inc(), 'Username', boldStyle)worksheet1.write(row, inc(), 'Version Code', boldStyle)worksheet1.write(row, inc(), 'Device', boldStyle)worksheet1.write(row, inc(), 'Merchant Order Id', boldStyle)worksheet1.write(row, inc(), 'SubtagId', boldStyle)worksheet1.write(row, inc(), 'Sale Date', boldStyle)worksheet1.write(row, inc(), 'Dtr Sale Date', boldStyle)worksheet1.write(row, inc(), 'Sale Amount', boldStyle)worksheet1.write(row, inc(), 'Aff Sale Date', boldStyle)worksheet1.write(row, inc(), 'Aff Sale Amt', boldStyle)worksheet1.write(row, inc(), 'Aff PayOut', boldStyle)worksheet1.write(row, inc(), 'IP', boldStyle)worksheet1.write(row, inc(), 'Aff Status', boldStyle)worksheet1.write(row, inc(), 'Product Title', boldStyle)worksheet1.write(row, inc(), 'Price', boldStyle)worksheet1.write(row, inc(), 'Quantity', boldStyle)worksheet1.write(row, inc(), 'Status', boldStyle)worksheet1.write(row, inc(), 'Cashback Status', boldStyle)worksheet1.write(row, inc(), 'CashBack', boldStyle)i = -1worksheet2.write(row, inc(), 'Order Id', boldStyle)worksheet2.write(row, inc(), 'User Id', boldStyle)worksheet2.write(row, inc(), 'Username', boldStyle)worksheet2.write(row, inc(), 'Version Code', boldStyle)worksheet2.write(row, inc(), 'Device', boldStyle)worksheet2.write(row, inc(), 'Merchant Order Id', boldStyle)worksheet2.write(row, inc(), 'SubtagId', boldStyle)worksheet2.write(row, inc(), 'Sale Date', boldStyle)worksheet2.write(row, inc(), 'Dtr Sale Date', boldStyle)worksheet2.write(row, inc(), 'Sale Amount', boldStyle)worksheet2.write(row, inc(), 'Product Title', boldStyle)worksheet2.write(row, inc(), 'Price', boldStyle)worksheet2.write(row, inc(), 'Quantity', boldStyle)worksheet2.write(row, inc(), 'Status', boldStyle)worksheet2.write(row, inc(), 'Cashback Status', boldStyle)worksheet2.write(row, inc(), 'CashBack', boldStyle)i =-1affNotReconciledSheet.write(row, inc(), 'AdId', boldStyle)affNotReconciledSheet.write(row, inc(), 'subTagId', boldStyle)affNotReconciledSheet.write(row, inc(), 'Sale Date', boldStyle)affNotReconciledSheet.write(row, inc(), 'Sale Amount', boldStyle)affNotReconciledSheet.write(row, inc(), 'Pay Out', boldStyle)affNotReconciledSheet.write(row, inc(), 'Status', boldStyle)affNotReconciledSheet.write(row, inc(), 'IP', boldStyle)if worksheet3:i =-1worksheet3.write(row, inc(), 'Date', boldStyle)worksheet3.write(row, inc(), 'Sale Amount', boldStyle)worksheet3.write(row, inc(), 'Approved Sales', boldStyle)worksheet3.write(row, inc(), 'Rejected Sales', boldStyle)def getUserAmountReconciled():passdef getUserOrders():global irb = open_workbook(XLS_FILENAME,formatting_info=True)wb = copy(rb)worksheet = wb.add_sheet("All Users")#worksheet1 = wb.add_sheet("Suspected Users")workbook = xlwt.Workbook("utf8")sh1 = workbook.add_sheet("All Orders")sh2 = workbook.add_sheet("Flipkart Orders")db=client.Dtr# results = Mysql.fetchResult('''# select ow.*, u.username, crm1.order_count, u.referrer, aua.city,aua.state,aua.pincode from order_view ow# left join users u on u.id = ow.user_id# left join (select user_id, count(*) as order_count from order_view where status in ('ORDER_CREATED','DETAIL_CREATED') group by user_id)as crm1 on ow.user_id = crm1.user_id# left join (select * from (select * from all_user_addresses order by source) s group by user_id)aua on aua.user_id=u.id# where (lower(u.referrer) not like 'emp%' or u.utm_campaign is not null) and u.activated = 1 and ow.status in ('ORDER_CREATED','DETAIL_CREATED');## ''',)# con = MySQLdb.connect('localhost','root','shop2020','dtr')# cur = con.cursor()# cur.execute("select * from allorder")# results = cur.fetchall()results = Mysql.fetchResult("select * from allorder")row = 0i=-1worksheet.write(row, inc(), 'User Id', boldStyle)#worksheet1.write(row, i, 'User Id', boldStyle)worksheet.write(row, inc(), 'User name', boldStyle)#worksheet1.write(row, i, 'User name', boldStyle)worksheet.write(row, inc(), 'Order Id', boldStyle)#worksheet1.write(row, i, 'Order Id', boldStyle)worksheet.write(row, inc(), 'Created On', boldStyle)#worksheet1.write(row, i, 'Created On', boldStyle)worksheet.write(row, inc(), 'Store Id', boldStyle)#worksheet1.write(row, i, 'Store Id', boldStyle)worksheet.write(row, inc(), 'Merchant Order Id', boldStyle)worksheet.write(row, inc(), 'SubOrder Id', boldStyle)#worksheet1.write(row, i, 'Merchant Order Id', boldStyle)worksheet.write(row, inc(), 'Product title', boldStyle)#worksheet1.write(row, i, 'Product title', boldStyle)worksheet.write(row, inc(), 'Amount Paid', boldStyle)#worksheet1.write(row, i, 'Amount Paid', boldStyle)worksheet.write(row, inc(), 'Cashback', boldStyle)#worksheet1.write(row, i, 'Cashback', boldStyle)worksheet.write(row, inc(), 'Order Status', boldStyle)#worksheet1.write(row, i, 'Order Status', boldStyle)worksheet.write(row, inc(), 'Detailed Status', boldStyle)#worksheet1.write(row, i, 'Detailed Status', boldStyle)worksheet.write(row, inc(), 'Cashback Status', boldStyle)#worksheet1.write(row, i, 'Cashback Status', boldStyle)worksheet.write(row, inc(), 'Reconciled', boldStyle)#worksheet1.write(row, i, 'Reconciled', boldStyle)worksheet.write(row, inc(), 'IP', boldStyle)#worksheet1.write(row, i, 'IP', boldStyle)worksheet.write(row, inc(), 'Aff Status', boldStyle)#worksheet1.write(row, i, 'Aff Status', boldStyle)i=-1sh1.write(row, inc(), 'User Id', boldStyle)sh1.write(row, inc(), 'User name', boldStyle)sh1.write(row, inc(), 'Order Id', boldStyle)sh1.write(row, inc(), 'Created On', boldStyle)sh1.write(row, inc(), 'Store Id', boldStyle)sh1.write(row, inc(), 'Merchant Order Id', boldStyle)sh1.write(row, inc(), 'Status', boldStyle)sh1.write(row, inc(), 'Detailed Status', boldStyle)sh1.write(row, inc(), 'Product title', boldStyle)sh1.write(row, inc(), 'Referrer', boldStyle)sh1.write(row, inc(), 'Amount Paid', boldStyle)sh1.write(row, inc(), 'Catalog Id', boldStyle)sh1.write(row, inc(), 'Brand', boldStyle)sh1.write(row, inc(), 'Model', boldStyle)sh1.write(row, inc(), 'Category', boldStyle)sh1.write(row, inc(), 'Deal Rank', boldStyle)sh1.write(row, inc(), 'Max Nlc', boldStyle)sh1.write(row, inc(), 'Min Nlc', boldStyle)sh1.write(row, inc(), 'DP', boldStyle)sh1.write(row, inc(), 'Item status', boldStyle)sh1.write(row, inc(), 'City', boldStyle)sh1.write(row, inc(), 'State', boldStyle)sh1.write(row, inc(), 'Pincode', boldStyle)i =-1sh2.write(row, inc(), 'User Id', boldStyle)sh2.write(row, inc(), 'User name', boldStyle)sh2.write(row, inc(), 'Created On', boldStyle)sh2.write(row, inc(), 'Store', boldStyle)sh2.write(row, inc(), 'Status', boldStyle)sh2.write(row, inc(), 'Product title', boldStyle)sh2.write(row, inc(), 'Price', boldStyle)sh2.write(row, inc(), 'Quantity', boldStyle)sh2.write(row, inc(), 'Catalog Id', boldStyle)sh2.write(row, inc(), 'Brand', boldStyle)sh2.write(row, inc(), 'Model', boldStyle)sh2.write(row, inc(), 'Category', boldStyle)row=0row2=0row3=0db1 = client.Catalogfor result in results:morder = db.merchantOrder.find_one({"orderId":result[2]})if morder is not None:subOrders = morder.get("subOrders")if subOrders is not None:for subOrder in subOrders:row3+=1i=-1# sh1.write(row3, inc(), result[1])# sh1.write(row3, inc(), result[7])# sh1.write(row3, inc(), result[0])# sh1.write(row3, inc(), result[6], datetime_format)# sh1.write(row3, inc(), sourceMap.get(result[2]))# sh1.write(row3, inc(), morder.get("merchantOrderId"))# sh1.write(row3, inc(), subOrder.get("status"))# sh1.write(row3, inc(), subOrder.get("detailedStatus"))# sh1.write(row3, inc(), subOrder.get("productTitle"))# sh1.write(row3, inc(), result[9])sh1.write(row3, inc(), result[0])sh1.write(row3, inc(), result[1])sh1.write(row3, inc(), result[2])sh1.write(row3, inc(), result[3])sh1.write(row3, inc(), result[4])sh1.write(row3, inc(), result[5])sh1.write(row3, inc(), result[6])sh1.write(row3, inc(), result[7])sh1.write(row3, inc(), result[8])sh1.write(row3, inc(), result[9])sh1.write(row3, inc(), result[10])inc()# if int(subOrder.get("quantity")) > 0:# sh1.write(row3, i, int(subOrder.get("amountPaid"))/int(subOrder.get("quantity")))# skuData = getSkuData(morder.get("storeId"), subOrder.get("productCode"))# if morder.get("storeId") in (1,2,4,5,6):# skuData = db1.MasterData.find_one({'identifier':subOrder.get("productCode").strip(), 'source_id':morder.get("storeId")})# elif morder.get("storeId") == 3:# skuData = db1.MasterData.find_one({'secondaryIdentifier':subOrder.get("productCode").strip(), 'source_id':morder.get("storeId")})# if skuData is not None:# sh1.write(row3, inc(), skuData.get("skuBundleId"))# sh1.write(row3, inc(), skuData.get("brand"))# sh1.write(row3, inc(), skuData.get("model_name"))# sh1.write(row3, inc(), skuData.get("category"))# sh1.write(row3, inc(), subOrder.get("dealRank"))# sh1.write(row3, inc(), subOrder.get("maxNlc"))# sh1.write(row3, inc(), subOrder.get("minNlc"))# sh1.write(row3, inc(), utils.statusMap.get(subOrder.get("db")))## sh1.write(row3, inc(), subOrder.get("itemStatus"))# else:# sh1.write(row3, inc(), None)# sh1.write(row3, inc(), None)# sh1.write(row3, inc(), None)# sh1.write(row3, inc(), None)# sh1.write(row3, inc(), None)# sh1.write(row3, inc(), None)# sh1.write(row3, inc(), None)# sh1.write(row3, inc(), None)# sh1.write(row3, inc(), None)sh1.write(row3, inc(), result[11])sh1.write(row3, inc(), result[12])sh1.write(row3, inc(), result[13])sh1.write(row3, inc(), result[14])sh1.write(row3, inc(), result[15])sh1.write(row3, inc(), result[16])sh1.write(row3, inc(), result[17])sh1.write(row3, inc(), result[18])sh1.write(row3, inc(), result[19])# print result[10]# sh1.write(row3, inc(), unicode(result[10], errors='ignore') if result[10] else None)# sh1.write(row3, inc(), result[11])# sh1.write(row3, inc(), result[12])sh1.write(row3, inc(), unicode(result[20], errors='ignore') if result[20] else None)sh1.write(row3, inc(), result[21])sh1.write(row3, inc(), result[22])#removed filter as requested by Rajneesh#if subOrder.get("cashBackStatus") == Store.CB_APPROVED or subOrder.get("cashBackStatus") == Store.CB_PENDING:row +=1i=-1# worksheet.write(row, inc(), result[0])# worksheet.write(row, inc(), result[7])# worksheet.write(row, inc(), result[1])# worksheet.write(row, inc(), result[6], datetime_format)# worksheet.write(row, inc(), result[2])# worksheet.write(row, inc(), morder.get("merchantOrderId"))# worksheet.write(row, inc(), subOrder.get("merchantSubOrderId"))# worksheet.write(row, inc(), subOrder.get("productTitle"))# worksheet.write(row, inc(), subOrder.get("amountPaid"))# worksheet.write(row, inc(), subOrder.get("cashBackAmount"))# worksheet.write(row, inc(), subOrder.get("status"))# worksheet.write(row, inc(), subOrder.get("detailedStatus"))# worksheet.write(row, inc(), subOrder.get("cashBackStatus"))worksheet.write(row, inc(), result[2])worksheet.write(row, inc(), result[1])worksheet.write(row, inc(), result[0])worksheet.write(row, inc(), result[3])worksheet.write(row, inc(), result[4])worksheet.write(row, inc(), result[5])worksheet.write(row, inc(), result[23])worksheet.write(row, inc(), result[8])worksheet.write(row, inc(), result[10])worksheet.write(row, inc(), result[25])worksheet.write(row, inc(), result[6])worksheet.write(row, inc(), result[7])worksheet.write(row, inc(), result[24])worksheet.write(row, inc(), result[8])#worksheet.write(row, inc(), False if morder.get("reconciled") is None else True)#if morder.get("reconciled") and morder.get("storeId")==3:# try:# snapdealafinfo = db.snapdealOrderAffiliateInfo.find_one({"adId":morder.get("adId"), "reconciled":True})# worksheet.write(row, inc(), snapdealafinfo.get("ip"))# worksheet.write(row, inc(), snapdealafinfo.get("conversionStatus"))# except:# print "Could not find", morder.get("adId"), "that is reconciled for order", morder.get("orderId")#if result[8] >= 10:# row2 +=1# i=-1#worksheet1.write(row2, inc(), result[0])#worksheet1.write(row2, inc(), result[7])#worksheet1.write(row2, inc(), result[1])#worksheet1.write(row2, inc(), result[6], datetime_format)#worksheet1.write(row2, inc(), result[2])#worksheet1.write(row2, inc(), morder.get("merchantOrderId"))#worksheet1.write(row2, inc(), subOrder.get("productTitle"))#worksheet1.write(row2, inc(), subOrder.get("amountPaid"))#worksheet1.write(row2, inc(), subOrder.get("cashBackAmount"))#worksheet1.write(row2, inc(), subOrder.get("status"))#worksheet1.write(row2, inc(), subOrder.get("detailedStatus"))#worksheet1.write(row2, inc(), subOrder.get("cashBackStatus"))#worksheet1.write(row2, inc(), False if morder.get("reconciled") is None else True)#worksheet1.write(row2, inc(), )#if morder.get("reconciled") and morder.get("storeId")==3:# try:# snapdealafinfo = db.snapdealOrderAffiliateInfo.find_one({"adId":morder.get("adId"), "reconciled":True})# worksheet1.write(row2, inc(), snapdealafinfo.get("ip"))# worksheet1.write(row2, inc(), snapdealafinfo.get("conversionStatus"))# except:# print "Could not find", morder.get("adId"), "that is reconcired for order", morder.get("orderId")flipkartOrders = session.query(FlipkartOrders).order_by(FlipkartOrders.created.desc()).all()flipkartOrders.reverse()row3=0#try:for order in flipkartOrders:row3 += 1i=-1sh2.write(row3, inc(), order.user_id)sh2.write(row3, inc(), order.email)sh2.write(row3, inc(), order.created, datetime_format)sh2.write(row3, inc(), "Flipkart")sh2.write(row3, inc(), order.status)sh2.write(row3, inc(), order.title)sh2.write(row3, inc(), order.price)sh2.write(row3, inc(), order.quantity)sh2.write(row3, inc(), order.catalogId)sh2.write(row3, inc(), order.brand)sh2.write(row3, inc(), order.model)sh2.write(row3, inc(), order.category)wb.save(XLS_FILENAME)workbook.save(XLS_O_FILENAME)def getSkuData(storeId, identifier):if storeId in (1,2,4,5,6):skuData = client.Catalog.MasterData.find_one({'identifier':identifier, 'source_id':storeId})elif storeId == 3:skuData = client.Catalog.MasterData.find_one({'secondaryIdentifier':identifier, 'source_id':storeId})return skuDatadef inc():global ii+=1return idef migrateFlipkartOrders():db = client.Dtrinfo = reversed(list(db.flipkartOrderAffiliateInfo.find()))#try:for order in info:userId = NonesubTagId = Noneemail = NonesubTagId = order.get("subTagId")if subTagId:click = session.query(Clicks).filter_by(tag = subTagId).first()if click is not None:userId= click.user_iduser = session.query(Users.email).filter_by(id = userId).first()if user is not None:email = user.emailflipkartOrder = FlipkartOrders()flipkartOrder.user_id = userIdflipkartOrder.identifier = order.get("identifier")flipkartOrder.email = emailflipkartOrder.subtagId = order.get("subTagId")flipkartOrder.created = datetime.strptime(order.get("saleDate"), "%Y-%m-%d")flipkartOrder.status = order.get("conversionStatus")flipkartOrder.title = order.get("productTitle")flipkartOrder.price = order.get("price")flipkartOrder.quantity = order.get("quantity")flipkartOrder.productCode = order.get("productCode")skuData = getSkuData(2, order.get("productCode"))if skuData is not None:flipkartOrder.catalogId = skuData.get("skuBundleId")flipkartOrder.brand = skuData.get("brand")flipkartOrder.model = skuData.get("model_name")flipkartOrder.category =categoryMap.get(skuData.get("category_id"))flipkartOrder.title =skuData.get("source_product_name")session.commit()def main1():db = client.Dtrfor offer in db.snapdealOrderAffiliateInfo.find({}):print offer.get("adId"), offer.get("saleAmount"), type(offer.get("saleAmount"))#obj = offer.get(saleTime)#obj['paidAmount'] += order["paidAmount"]def addHeaders(sheet):global ii = 0sheet.write(0, inc(), )def main():message = generateSnapDealReco()# message ="Kuch nahi"generateFlipkartReco()generateShopcluesReco()generateAmazonReco(XLS_A_FILENAME)message1 = getdata.summaryByBrandAndStore()getUserOrders()#getdata.sendmail(["naman.kumar@shop2020.in"], message1, "Summary by brand and store")#sendmail(["naman.kumar@shop2020.in"], message, "Affiliate Reco", XLS_FILENAME, XLS_F_FILENAME, XLS_A_FILENAME, XLS_SC_FILENAME)#sendmail(["naman.kumar@shop2020.in"], message1, "All DTR Orders", XLS_O_FILENAME)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)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)#sendmail(["amit.gupta@shop2020.in"], "", "Affiliate Reco", XLS_FILENAME, XLS_F_FILENAME, XLS_A_FILENAME, XLS_SC_FILENAME)def main3():db = client.Dtr#main()mailBodyTemplate="""<html><body><table cellspacing="0" border="1" style="text-align:right"><thead><tr><th style="text-align:center">Date</th><th style="text-align:center">Sale</th><th style="text-align:center">Approved</th><th style="text-align:center">Approved %</th><th style="text-align:center">Rejected</th><th style="text-align:center">Rejected %</th><th style="text-align:center">Missing %</th><th style="text-align:center">Approved % against Sale</th></tr></thead><tbody>{0}</tbody></table></body></html>"""tbody=[]rowtemplate="<tr><td>{0}</td><td>{1}</td><td>{2}</td><td>{3}</td><td>{4}</td><td>{5}</td><td>{6}</td><td>{7}</td></tr>"for offer in db.flSaleSnapshot.find().limit(61).sort("_id",-1):approved = offer.get("approvedAmount")rejected = offer.get("rejectedAmount")sale = offer.get("paidAmount")total = approved + rejectedif total ==0:continuetbody.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)))message = mailBodyTemplate.format("".join(tbody))print messagedef main2():db = client.Dtrcurs = getOrdersAfterDate(datetime.now() - timedelta(days=46), 3)datemap={}for row1 in curs:orderId = row1[0]order = db.merchantOrder.find_one({"orderId":orderId})if order is None:continue#saleTime = int(time.mktime(datetime.strptime(order['placedOn'], "%b %d, %Y, %I:%M %p").timetuple()))try:saleTime = datetime.strptime(order['placedOn'], "%a, %d %b, %Y").strftime("%Y-%m-%d")except:saleTime = datetime.strptime(order['placedOn'], "%b %d, %Y, %I:%M %p").strftime("%Y-%m-%d")print "%s\t%s\t%s\t%s"%(orderId, order['placedOn'], saleTime, order['paidAmount'])if not datemap.has_key(saleTime):datemap[saleTime] = {"totalAmount":0, "paidAmount":0, "rejectedAmount":0, "approvedAmount":0, "count":0}obj = datemap.get(saleTime)obj['paidAmount'] += order["paidAmount"]obj['count'] += 1for offer in db.snapdealOrderAffiliateInfo1.find({"saleDate":{"$gte":toTimeStamp(datetime.now() - timedelta(days=46))}}):saleTime = utils.fromTimeStamp(offer.get('saleDate')).strftime("%Y-%m-%d")obj = datemap.get(saleTime)if obj is not None:try:if offer.get("payOut"):obj['approvedAmount'] += offer["saleAmount"]else:obj['rejectedAmount'] += offer["saleAmount"]except:print "Exception for orderId" , offer.get("orderId")else:print "Could not find sale date", saleTimecontinuefor key,valmap in datemap.iteritems():valmap['_id'] = keydb.flSaleSnapshot.save(valmap)if __name__ == '__main__':# getdata.addToAllOrders(date.today()-timedelta(days=30))# main2()main()