WebSVN – SmartDukaan – Diff – /trunk/PyDTR/src/scripts/ActivityReport.py

 from elixir import *
 from dtr.storage import DataService
 from dtr.storage.DataService import Orders, Users, CallHistory
 from sqlalchemy.sql.expression import func
 from operator import or_
+from time import strftime
+from dtr.utils import utils
 DB_HOST = "localhost"
 DB_USER = "root"
 DB_PASSWORD = "shop2020"
 con = None
 dateWiseOrderMap = {}
 weekWiseOrderMap = {}
 monthWiseOrderMap = {}
+dateWiseSaholicOrderMap = {}
+weekWiseSaholicOrderMap = {}
+monthWiseSaholicOrderMap = {}
 orderIds = []
 cutOff = 1425839400
 #cutOff = 1425234600
 oneDay = 86400
 monthCutOff = 1425148200
 weekCutOff = 1425839400
-#weekCutOff = 1425234600
+newWeekCutOff = 1425839400
+newMonthCutOff = 1425148200
 DataService.initialize()
 monthMap = {  '01':30,
             '02':28,
             '03':30,
     dnruSql = DNRU_QUERY
     dauSql = DAU_QUERY
     dabSql = DAB_QUERY
     dtoSql = DTO_QUERY
     conn = getDbConnection()
-    # prepare a cursor object using cursor() method
     cursor = conn.cursor()
-    # Execute the SQL command
-    # Fetch source id.
     cursor.execute(datesql)
     result = cursor.fetchall()
     global workbook
     workbook = xlwt.Workbook()
     worksheet = workbook.add_sheet("User")
     worksheet.write(row, 3, 'DAU', boldStyle)
     worksheet.write(row, 4, 'DAB', boldStyle)
     worksheet.write(row, 5, 'DTO', boldStyle)
     worksheet.write(row, 6, 'DVOL', boldStyle)
     worksheet.write(row, 7, 'DTV', boldStyle)
+    worksheet.write(row, 8, 'DOWNVOL', boldStyle)
+    worksheet.write(row, 9, 'DOWNVAL', boldStyle)
     for r in result:
         row += 1
         column = 0
         for data in r :
             worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
     result = cursor.fetchall()
     for r in result:
         row += 1
         column = 1
         for data in r :
-            print "Data" + str(data)
             sumdata=sumdata+data
-            print "Sum Data" + str(sumdata)
             worksheet.write(row, column, int(sumdata) if type(sumdata) is float else sumdata, date_format if type(sumdata) is date else default_format)
             column += 1
     row = 0
     cursor.execute(dnruSql)
         result = cursor.fetchall()
         for r in result:
             quantity= r[0]
             if quantity is None:
                 quantity=0
-        print 'Total Sub order quantity', dateWiseOrderMap.get(x).count,' Flipkart Order', quantity
         worksheet.write(row,column,(dateWiseOrderMap.get(x).count)+quantity)
         column += 1
     row =0
     for x in sorted(dateWiseOrderMap):
             if quantity is None:
                 quantity=0
         worksheet.write(row,column,(dateWiseOrderMap.get(x).value)+float(quantity))
         column += 1
+    #For Saholic
+    row=0
+    cursor = conn.cursor()
+    cursor.execute(datesql)
+    result = cursor.fetchall()
+    for x in sorted(dateWiseSaholicOrderMap):
+        row=0
+        for r in result:
+            for data in r :
+                row += 1
+                z= to_x_date(x)
+                if str(data).strip() == str(z).strip():
+                    column = 8
+                    worksheet.write(row,column,dateWiseSaholicOrderMap.get(x).count)
+                    column += 1
+    for x in sorted(dateWiseSaholicOrderMap):
+        row=0
+        for r in result:
+            for data in r :
+                row += 1
+                z= to_x_date(x)
+                if str(data).strip() == str(z).strip():
+                    column = 9
+                    worksheet.write(row,column,dateWiseSaholicOrderMap.get(x).value)
+                    column += 1
 def generateMonthlyReport():
     monthSql = MONTH_QUERY
     mnruSql = MNRU_QUERY
     mauSql = MAU_QUERY
     mabSql = MAB_QUERY
     worksheet.write(row, 3, 'MAU', boldStyle)
     worksheet.write(row, 4, 'MAB', boldStyle)
     worksheet.write(row, 5, 'MTO', boldStyle)
     worksheet.write(row, 6, 'MVOL', boldStyle)
     worksheet.write(row, 7, 'MTV', boldStyle)
+    worksheet.write(row, 8, 'MOWNVOL', boldStyle)
+    worksheet.write(row, 9, 'MOWNVAL', boldStyle)
     for r in result:
         row += 1
         column = 0
         for data in r :
     result = cursor.fetchall()
     for r in result:
         row += 1
         column = 1
         for data in r :
-            print "Data" + str(data)
             sumdata=sumdata+data
-            print "Sum Data" + str(sumdata)
             worksheet.write(row, column, int(sumdata) if type(sumdata) is float else sumdata, date_format if type(sumdata) is date else default_format)
             column += 1
     row = 0
     cursor.execute(mnruSql)
         result = cursor.fetchall()
         for r in result:
             quantity= r[0]
             if quantity is None:
                 quantity=0
-        print 'Total Sub order quantity Monthly', dateWiseOrderMap.get(x).count,' Flipkart Order', quantity
         worksheet.write(row,column,(monthWiseOrderMap.get(x).count)+quantity)
         column += 1
     row =0
     for x in sorted(monthWiseOrderMap):
         for r in result:
             quantity= r[0]
             if quantity is None:
                 quantity=0
         worksheet.write(row,column,(monthWiseOrderMap.get(x).value)+float(quantity))
         column += 1
+    row =0
+    for x in sorted(monthWiseSaholicOrderMap):
+        row += 1
+        column = 8
+        worksheet.write(row,column,monthWiseSaholicOrderMap.get(x).count)
+        column += 1
+    row =0
+    for x in sorted(monthWiseSaholicOrderMap):
+        row += 1
+        column = 9
+        worksheet.write(row,column,monthWiseSaholicOrderMap.get(x).value)
+        column += 1
     workbook.save(TMP_FILE)
-    sendmail(["manas.kapoor@shop2020.in","rajneesh.arora@saholic.com"], "", TMP_FILE, SUBJECT)
+    #sendmail(["manas.kapoor@shop2020.in","rajneesh.arora@saholic.com"], "", TMP_FILE, SUBJECT)
-    #sendmail(["manas.kapoor@shop2020.in"], "", TMP_FILE, SUBJECT)
+    sendmail(["manas.kapoor@shop2020.in"], "", TMP_FILE, SUBJECT)
 def generateWeeklyReport():
     weekSql = WEEK_QUERY
     wnruSql = WNRU_QUERY
     wauSql = WAU_QUERY
     worksheet.write(row, 3, 'WAU', boldStyle)
     worksheet.write(row, 4, 'WAB', boldStyle)
     worksheet.write(row, 5, 'WTO', boldStyle)
     worksheet.write(row, 6, 'WVOL', boldStyle)
     worksheet.write(row, 7, 'WTV', boldStyle)
+    worksheet.write(row, 8, 'WOWNVOL', boldStyle)
+    worksheet.write(row, 9, 'WOWNVAL', boldStyle)
     for r in result:
         row += 1
         column = 0
         for data in r :
     result = cursor.fetchall()
     for r in result:
         row += 1
         column = 1
         for data in r :
-            print "Data" + str(data)
             sumdata=sumdata+data
-            print "Sum Data" + str(sumdata)
             worksheet.write(row, column, int(sumdata) if type(sumdata) is float else sumdata, date_format if type(sumdata) is date else default_format)
             column += 1
     row = 0
     cursor.execute(wnruSql)
         result = cursor.fetchall()
         for r in result:
             quantity= r[0]
             if quantity is None:
                 quantity=0
-        print 'Total Sub order quantity Weekly', dateWiseOrderMap.get(x).count,' Flipkart Order', quantity
         worksheet.write(row,column,(weekWiseOrderMap.get(x).count)+quantity)
         column+=1
     row = 0
     for x in sorted(weekWiseOrderMap):
         row += 1
             quantity= r[0]
             if quantity is None:
                 quantity=0
         worksheet.write(row,column,(weekWiseOrderMap.get(x).value)+float(quantity))
         column+=1
+    row=0
+    for x in sorted(weekWiseSaholicOrderMap):
+        row += 1
+        column = 8
+        worksheet.write(row,column,weekWiseSaholicOrderMap.get(x).count)
+        column+=1
+    row=0
+    for x in sorted(weekWiseSaholicOrderMap):
+        row += 1
+        column = 9
+        worksheet.write(row,column,weekWiseSaholicOrderMap.get(x).value)
+        column+=1
 def sendmail(email, message, fileName, title):
     if email == "":
         return
     mailServer = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)
     mailServer.ehlo()
     fileMsg.set_payload(file(TMP_FILE).read())
     encoders.encode_base64(fileMsg)
     fileMsg.add_header('Content-Disposition', 'attachment;filename=' + fileName)
     msg.attach(fileMsg)
-    MAILTO = ['manas.kapoor@saholic.com','rajneesh.arora@saholic.com']
+    #MAILTO = ['manas.kapoor@saholic.com','rajneesh.arora@saholic.com']
-    #MAILTO = ['manas.kapoor@saholic.com']
+    MAILTO = ['manas.kapoor@saholic.com']
     mailServer.login(SENDER, PASSWORD)
     mailServer.sendmail(PASSWORD, MAILTO, msg.as_string())
 class __Order:
             print counter+1
             if monthCutOff > breakPoint:
                 monthWiseOrderMap[init * 1000] = __Order(quantity, amount)
                 print "Breaking inner while"
                 break
+def populateSaholicOrderMap():
+    global dateWiseSaholicOrderMap
+    allOrders = get_mongo_connection().Dtr.merchantOrder.find({'createdOnInt':{"$gte":cutOff},'storeId':{"$in":[4]}}).sort([('createdOnInt',pymongo.ASCENDING)])
+    for orders in allOrders:
+        if orders.get('orderId') not in order_ids:
+            continue
+        cdate = ((to_py_date(orders.get('createdOnInt') * 1000)))
+        millisec = to_java_date(datetime(cdate.year, cdate.month, cdate.day))
+        if dateWiseSaholicOrderMap.has_key(millisec):
+            orderObj = dateWiseSaholicOrderMap.get(millisec)
+            q, c = getSaholicSubOrderQuantity(orders.get('subOrders'))
+            orderObj.count += q
+            orderObj.value += c
+        else:
+            orderObj = __Order(None, None)
+            q, c = getSaholicSubOrderQuantity(orders.get('subOrders'))
+            orderObj.count = q
+            orderObj.value = c
+            dateWiseSaholicOrderMap[millisec] = orderObj
+def getSaholicSubOrderQuantity(subOrders):
+    q = 0
+    c = 0
+    if subOrders is None:
+        return q, c
+    for subOrder in subOrders:
+        q = q + int(subOrder.get('quantity'))
+        try:
+            c = c + float(subOrder.get('amountPaid').encode('utf-8'))
+        except AttributeError:
+            c = c + float(subOrder.get('amountPaid'))
+    return q, c
+def populateSaholicWeekWiseMap1():
+    global newWeekCutOff
+    while(True):
+        if newWeekCutOff *1000 >= to_java_date(datetime.now()):
+            break
+        init = newWeekCutOff
+        breakPoint = newWeekCutOff + (6 * oneDay)
+        quantity, amount = 0 , 0
+        while(True):
+            orderObj = dateWiseSaholicOrderMap.get(newWeekCutOff * 1000)
+            if orderObj is not None:
+                quantity += orderObj.count
+                amount += orderObj.value
+            else:
+                quantity +=0
+                amount +=0
+            newWeekCutOff = newWeekCutOff + oneDay
+            if newWeekCutOff > breakPoint:
+                weekWiseSaholicOrderMap[init * 1000] = __Order(quantity, amount)
+                break
+def populateSaholicMonthWiseMap1():
+    global newMonthCutOff
+    while(True):
+        print newMonthCutOff *1000
+        a=str(to_py_date(newMonthCutOff*1000))
+        print 'Now ' + str(to_java_date(datetime.now()))
+        print "**********************"
+        if newMonthCutOff *1000 >= to_java_date(datetime.now()):
+            print "Breaking outer while"
+            break
+        init = newMonthCutOff
+        breakPoint = newMonthCutOff + (monthMap.get(a[5:7]) * oneDay)
+        print breakPoint
+        quantity, amount = 0 , 0
+        while(True):
+            print to_py_date(newMonthCutOff*1000)
+            print "weekCutOff ",newMonthCutOff
+            print "breakPoint ",breakPoint
+            orderObj = dateWiseSaholicOrderMap.get(newMonthCutOff * 1000)
+            if orderObj is None:
+                print "None for ", to_py_date(newMonthCutOff * 1000)
+                quantity += 0
+                amount += 0
+            if orderObj is not None:
+                quantity += orderObj.count
+                amount += orderObj.value
+            newMonthCutOff = newMonthCutOff + oneDay
+            counter=0
+            print counter+1
+            if newMonthCutOff > breakPoint:
+                monthWiseSaholicOrderMap[init * 1000] = __Order(quantity, amount)
+                print "Breaking inner while"
+                break
 def populateValidOrders():
     global order_ids
     allOrders = session.query(Orders.id).join((Users,Orders.user_id==Users.id)).filter(or_(~(func.lower(Users.referrer)).like('emp%'),Users.utm_campaign != None)).all()
     order_ids = list(zip(*allOrders)[0])
 def main():
     populateValidOrders()
     populateOrderMap()
     populateWeekWiseMap1()
     populateMonthWiseMap1()
+    populateSaholicOrderMap()
+    populateSaholicWeekWiseMap1()
+    populateSaholicMonthWiseMap1()
     generateDailyReport()
     generateWeeklyReport()
     generateMonthlyReport()
+def to_x_date(java_timestamp):
+    try:
+        date = datetime.fromtimestamp(java_timestamp / 1e3)
+    except:
+        return None
+    return date.strftime('%Y-%m-%d')
 if __name__ == '__main__':
     main()

Subversion Repositories SmartDukaan

(root)/trunk/PyDTR/src/scripts/ActivityReport.py – Rev 16933 → 16964