WebSVN – SmartDukaan – Diff – /trunk/PyDTR/src/dtr/reports/affiliatereco.py

 Created on Mar 13, 2015
 @author: amit
 '''
 from datetime import date, datetime, timedelta
-from dtr.storage.Mysql import getOrdersAfterDate
+from dtr.storage.Mysql import getOrdersAfterDate, \
+    getOrdersByTag
 from email import encoders
 from email.mime.base import MIMEBase
 from email.mime.multipart import MIMEMultipart
 from email.mime.text import MIMEText
 from pymongo.mongo_client import MongoClient
 from xlrd import open_workbook
+from xlutils.copy import copy
 from xlwt.Workbook import Workbook
 import MySQLdb
-from xlutils.copy import copy
 import smtplib
 import time
 import xlwt
 #from xlwt import
                     break
         wb.save(XLS_FILENAME)
 def generateSnapDealReco():
+    global i
     curs = getOrdersAfterDate(datetime.now() - timedelta(days=30), 3)
     db = client.Dtr
+    notReconciled = {}
     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")
         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 += 1
-        if len(aff) > 0 and order["subTagId"] not in matchedList:
+        if len(aff) > 0 and order["subTagId"] not in matchedList and int(order["paidAmount"])==int(aff[0]["saleAmount"]):
             matchedList.append(order["subTagId"])
             aff[0]['reconciled'] = True
-            db.snapdealOrderAffiliateInfo.save(aff[0])
+            db.snapdealOrderAffiliateInfo.update({"adId":aff[0].get("adId")}, {"$set":{"reconcile":True}}, multi=True)
             order['reconciled'] = True
             db.merchantOrder.save(order)
             row += 1
-            global i
             i=-1
             worksheet.write(row, inc(), orderId)
             worksheet1.write(anotherrow, i, orderId)
             worksheet.write(row, inc(), row1[1])
             worksheet1.write(anotherrow, i, row1[1])
             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'])
+            worksheet.write(row, inc(), aff[0]['saleDate'])
-                worksheet1.write(anotherrow, i, aff[0]['saleDate'])
+            worksheet1.write(anotherrow, i, aff[0]['saleDate'])
-                worksheet.write(row, inc(), aff[0]['saleAmount'])
+            worksheet.write(row, inc(), aff[0]['saleAmount'])
-                worksheet1.write(anotherrow, i, aff[0]['saleAmount'])
+            worksheet1.write(anotherrow, i, aff[0]['saleAmount'])
-                worksheet.write(row, inc(), aff[0]['payOut'])
+            worksheet.write(row, inc(), aff[0]['payOut'])
-                worksheet1.write(anotherrow, i, aff[0]['payOut'])
+            worksheet1.write(anotherrow, i, aff[0]['payOut'])
             k = i
             row -= 1
             anotherrow -= 1
             for subOrder in order['subOrders']:
                 i = k
                 worksheet.write(row, inc(), subOrder['cashBackAmount'])
                 worksheet1.write(anotherrow, i, subOrder['cashBackAmount'])
         else:
             row2 +=1
             i=-1
             worksheet1.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, 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']))
+            timestamp1 = int(time.mktime(datetime.strptime(order['placedOn'], "%b %d, %Y, %I:%M %p").timetuple()))
-            worksheet1.write(anotherrow, inc(), "")
+            notReconciled[orderId] = (anotherrow, i, int(order['paidAmount']), timestamp1, order["subTagId"])
+            inc()
-            worksheet1.write(anotherrow, inc(), "")
+            inc()
-            worksheet1.write(anotherrow, inc(), "")
+            inc()
             k = i
             anotherrow -= 1
             row2 -= 1
             for subOrder in order['subOrders']:
                 anotherrow += 1
                 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'])
+    last30Days =  date.today() - timedelta(days=30)
+    int(time.mktime(last30Days.timetuple()))
+    row = 0
+    for offer in db.snapdealOrderAffiliateInfo.find({"reconciled":{"$exists":False},
+                                                     "saleTime":{"$gt":int(time.mktime(last30Days.timetuple())),
+                                                                 "$lt":int(time.mktime(date.today().timetuple()))}} ):
+        row += 1
+        i=-1
+        affNotReconciledSheet.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"))
+        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 = column
+                    try:
+                        worksheet1.write(roww, inc(), subTagId)
+                        worksheet1.write(roww, inc(), offer.get("saleAmount"))
+                        worksheet1.write(roww, inc(), offer.get("payOut"))
+                        print "found match for user", order[1]
+                    except:
+                        print "found another match for user", order[1]
+                        i=17
+                        worksheet1.write(roww, inc(), subTagId)
+                        worksheet1.write(roww, inc(), offer.get("saleAmount"))
+                        worksheet1.write(roww, inc(), offer.get("payOut"))
         workbook.save(XLS_FILENAME)
 def sendmail(email, message, title):
     if email == "":
         return
     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.Dtr
-    last30Days =  date.today() - timedelta(days=30)
-    int(time.mktime(last30Days.timetuple()))
+def getUserAmountReconciled():
-    for offer in db.snapdealOrderAffiliateInfo.find({"reconciled":{"$exists":False},
-                                                     "saleTime":{"$gt":int(time.mktime(last30Days.timetuple())),
-                                                                 "$lt":int(time.mktime(date.today().timetuple()))}} ):
-        row += 1
-        i=-1
+    pass
-        affNotReconciledSheet.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 i
     i+=1
     return i
 def main():
     generateSnapDealReco()
-    generateFlipkartReco()
+    #generateFlipkartReco()
-    sendmail(["amit.gupta@shop2020.in"], "", "DTR Affiliate Reco")
+    #sendmail(["amit.gupta@shop2020.in"], "", "DTR Affiliate Reco")
 if __name__ == '__main__':
     main()

Subversion Repositories SmartDukaan

(root)/trunk/PyDTR/src/dtr/reports/affiliatereco.py – Rev 14512 → 14524