Rev 14511 | Rev 14524 | 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.storage.Mysql import getOrdersAfterDatefrom 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 xlwt.Workbook import Workbookimport MySQLdbfrom xlutils.copy import copyimport 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 = -1def 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():curs = getOrdersAfterDate(datetime.now() - timedelta(days=30), 3)db = client.DtrmatchedList = []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:matchedList.append(order["subTagId"])aff[0]['reconciled'] = Truedb.snapdealOrderAffiliateInfo.save(aff[0])order['reconciled'] = Truedb.merchantOrder.save(order)row += 1global ii=-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(), 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']))if int(order["paidAmount"]) == aff[0]["saleAmount"]: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'])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:row2 +=1i=-1worksheet1.write(anotherrow, inc(), orderId)worksheet2.write(row2, i, orderId)worksheet1.write(anotherrow, inc(), row1[1])worksheet2.write(row2, i, row1[1])worksheet1.write(anotherrow, inc(), row1[-1])worksheet2.write(row2, i, row1[-1])worksheet1.write(anotherrow, inc(), order['merchantOrderId'])worksheet2.write(row2, i, order['merchantOrderId'])worksheet1.write(anotherrow, inc(), order['subTagId'])worksheet2.write(row2, i, order['subTagId'])worksheet1.write(anotherrow, inc(), order['placedOn'])worksheet2.write(row2, i, order['placedOn'])worksheet1.write(anotherrow, inc(), int(order['paidAmount']))worksheet2.write(row2, i, int(order['paidAmount']))worksheet1.write(anotherrow, inc(), "")worksheet1.write(anotherrow, inc(), "")worksheet1.write(anotherrow, inc(), "")k = ianotherrow -= 1row2 -= 1for subOrder in order['subOrders']:anotherrow += 1row2 += 1i = kworksheet1.write(anotherrow, inc(), subOrder['productTitle'])worksheet2.write(row2, i-3, subOrder['productTitle'])worksheet1.write(anotherrow, inc(), subOrder['amountPaid'])worksheet2.write(row2, i-3, subOrder['amountPaid'])worksheet1.write(anotherrow, inc(), subOrder['quantity'])worksheet2.write(row2, i-3, subOrder['quantity'])worksheet1.write(anotherrow, inc(), subOrder['status'])worksheet2.write(row2, i-3, subOrder['status'])worksheet1.write(anotherrow, inc(), subOrder['cashBackStatus'])worksheet2.write(row2, i-3, subOrder['cashBackStatus'])worksheet1.write(anotherrow, inc(), subOrder['cashBackAmount'])worksheet2.write(row2, i-3, subOrder['cashBackAmount'])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(), '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(), '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(), '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(), '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(), '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)db = client.Dtrlast30Days = date.today() - timedelta(days=30)int(time.mktime(last30Days.timetuple()))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"))def inc():global ii+=1return idef main():generateSnapDealReco()generateFlipkartReco()sendmail(["amit.gupta@shop2020.in"], "", "DTR Affiliate Reco")if __name__ == '__main__':main()