Rev 14616 | Rev 14632 | 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 Storefrom dtr.storage import Mysqlfrom dtr.storage.Mysql import getOrdersAfterDate, getOrdersByTagfrom 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 MySQLdbimport smtplibimport timeimport xlwt#from xlwt importclient = 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 = "dtrsourcesreco.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'def generateFlipkartReco():global ii = -1curs = getOrdersAfterDate(datetime.now() - timedelta(days=30), 2)db = client.Dtrrb = open_workbook(XLS_FILENAME)wb = copy(rb)#wb = xlwt.Workbook()row = 0worksheet = wb.add_sheet("Flipkart")worksheet.write(row, inc(), 'Order Id', boldStyle)worksheet.write(row, inc(), 'User Id', boldStyle)worksheet.write(row, inc(), 'Username', boldStyle)worksheet.write(row, inc(), 'Merchant Order Id', 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(), 'Detailed Status', boldStyle)worksheet.write(row, inc(), 'Cashback Status', boldStyle)worksheet.write(row, inc(), 'Our CashBack', boldStyle)worksheet.write(row, inc(), 'Sale Date', boldStyle)worksheet.write(row, inc(), 'SubtagId', boldStyle)worksheet.write(row, inc(), 'Category', boldStyle)worksheet.write(row, inc(), 'Aff PayOut', boldStyle)worksheet.write(row, inc(), 'Aff Sale Amount', boldStyle)worksheet.write(row, inc(), '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()))for subOrder in order['subOrders']:affs = list(db.flipkartOrderAffiliateInfo.find({"subOrders.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(), 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['subTagId']== subOrder['subTagId']:worksheet.write(row, inc(), aff['category'])worksheet.write(row, inc(), aff['payOut'])worksheet.write(row, inc(), aff['saleAmount'])worksheet.write(row, inc(), aff['saleDate'])breakwb.save(XLS_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")affNotReconciledSheet = workbook.add_sheet("Snapdeal Aff not reconciled")setHeaders(worksheet, worksheet1, worksheet2, affNotReconciledSheet)row = 0anotherrow = 0row2 = 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()))aff = list(db.snapdealOrderAffiliateInfo.find({"subTagId":order["subTagId"]}))anotherrow += 1if 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[-2])worksheet1.write(anotherrow, i, row1[-2])worksheet.write(row, inc(), row1[-1])worksheet1.write(anotherrow, i, row1[-1])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(), 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'])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[-2])worksheet1.write(anotherrow, inc(), row1[-1])worksheet1.write(anotherrow, inc(), order['merchantOrderId'])worksheet1.write(anotherrow, inc(), order['subTagId'])worksheet1.write(anotherrow, inc(), order['placedOn'])worksheet1.write(anotherrow, inc(), int(order['paidAmount']))timestamp1 = int(time.mktime(datetime.strptime(order['placedOn'], "%b %d, %Y, %I:%M %p").timetuple()))notReconciled[orderId] = (anotherrow, i, int(order['paidAmount']), timestamp1, order["subTagId"])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'])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()))}} ):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)breakunreconciledOrders = notReconciled.keys()row2=0for row1 in curs:if row1[0] in unreconciledOrders:for order in db.merchantOrder.find({"orderId":row1[0]}):row2 += 1i=-1worksheet2.write(row2, inc(), order["orderId"])worksheet2.write(row2, inc(), row1[1])worksheet2.write(row2, inc(), row1[-2])worksheet2.write(row2, inc(), row1[-1])worksheet2.write(row2, inc(), order['merchantOrderId'])worksheet2.write(row2, inc(), order['subTagId'])worksheet2.write(row2, inc(), order['placedOn'])worksheet2.write(row2, inc(), int(order['paidAmount']))row2 -=1k=ifor subOrder in order['subOrders']:i = krow2 += 1worksheet2.write(row2, inc(), subOrder['productTitle'])worksheet2.write(row2, inc(), subOrder['amountPaid'])worksheet2.write(row2, inc(), subOrder['quantity'])worksheet2.write(row2, inc(), subOrder['status'])worksheet2.write(row2, inc(), subOrder['cashBackStatus'])worksheet2.write(row2, inc(), subOrder['cashBackAmount'])for 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"))workbook.save(XLS_FILENAME)def sendmail(email, message, title):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 heresnapdeal = MIMEBase('application', 'vnd.ms-excel')snapdeal.set_payload(file(XLS_FILENAME).read())encoders.encode_base64(snapdeal)snapdeal.add_header('Content-Disposition', 'attachment;filename=' + XLS_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):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(), 'Version Code', boldStyle)worksheet.write(row, inc(), 'Username', 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(), '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(), '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(), 'Version Code', boldStyle)worksheet1.write(row, inc(), 'Username', 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(), '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(), '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(), 'Version Code', boldStyle)worksheet2.write(row, inc(), 'Username', 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(), '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)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")db=client.Dtrresults = Mysql.fetchResult('''select ow.*, u.username, crm1.order_count, u.referrer 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 = 'ORDER_CREATED' group by user_id) as crm1 on ow.user_id = crm1.user_id where lower(u.referrer) not like 'emp%'''',)orders = []orders.append("")orderH = []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)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)row=0row2=0for result in results:morder = db.merchantOrder.find_one({"orderId":result[0]})if morder is not None:subOrders = morder.get("subOrders")if subOrders is not None:for subOrder in subOrders:if subOrder.get("cashBackStatus") == Store.CB_APPROVED or subOrder.get("cashBackStatus") == Store.CB_PENDING:row +=1i=-1worksheet.write(row, inc(), result[0])worksheet.write(row, inc(), result[-3])worksheet.write(row, inc(), result[1])worksheet.write(row, inc(), result[-4], datetime_format)worksheet.write(row, inc(), result[2])worksheet.write(row, inc(), morder.get("merchantOrderId"))worksheet.write(row, inc(), subOrder.get("productTitle"))worksheet.write(row, inc(), subOrder.get("amountPaid"))worksheet.write(row, inc(), subOrder.get("cashBackAmount"))worksheet.write(row, inc(), subOrder.get("status"))worksheet.write(row, inc(), subOrder.get("detailedStatus"))worksheet.write(row, inc(), subOrder.get("cashBackStatus"))worksheet.write(row, inc(), False if morder.get("reconciled") is None else True)if morder.get("reconciled") and morder.get("storeId")==3:try:worksheet.write(row, inc(), db.snapdealOrderAffiliateInfo.find_one({"adId":morder.get("adId"), "reconciled":True}).get("ip"))except:print "Could not find", morder.get("adId"), "that is reconcired for order", morder.get("orderId")if result[-2] >= 10 and result[-1].lower().startswith("crm"):row2 +=1i=-1worksheet1.write(row2, inc(), result[0])worksheet1.write(row2, inc(), result[-3])worksheet1.write(row2, inc(), result[1])worksheet1.write(row2, inc(), result[-4], 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)if morder.get("reconciled") and morder.get("storeId")==3:try:worksheet1.write(row2, inc(), db.snapdealOrderAffiliateInfo.find_one({"adId":morder.get("adId"), "reconciled":True}).get("ip"))except:print "Could not find", morder.get("adId"), "that is reconcired for order", morder.get("orderId")wb.save(XLS_FILENAME)def inc():global ii+=1return idef main():generateSnapDealReco()getUserOrders()sendmail(["amit.gupta@shop2020.in", "rajneesh.arora@saholic.com"], "", "DTR Affiliate Reco")if __name__ == '__main__':main()