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

 AND date(d.visited) BETWEEN %s AND %s
 group by month(d.visited);
 """
 WEEK_QUERY="""
-SELECT CONCAT(date(o.created), ' - ', date(o.created) + INTERVAL 6 DAY) AS week
+SELECT CONCAT(date(o.created), ' - ', date(o.created) + INTERVAL 6 DAY) AS week,WEEK(o.created)
 FROM  order_view o
 JOIN users u ON u.id = o.user_id WHERE
 (LOWER(u.referrer) NOT LIKE  'emp%%' OR u.utm_campaign is not NULL) AND u.activated =1
 AND date(o.created) >= %s
 GROUP BY WEEK(date(o.created))
 SELECT date(created_on),sum(quantity),sum(amount_paid)  FROM  allorder o
 JOIN users u ON u.id = o.user_id WHERE  (LOWER(u.referrer) NOT LIKE
 'emp%%' OR u.utm_campaign is not NULL) AND u.activated =1
 AND date(o.created_on) >= %s and store_id='spice' GROUP BY date(o.created_on);
 """
+DACCSB_QUERY="""
+select date(created_on),count(distinct user_id) from allorder
+where (category='Accessories' or category='Accs') and
+date(created_on)>=%s and store_id='spice' group by date(created_on) order by date(created_on);
+"""
+DACCSO_QUERY="""
+select date(created_on),sum(quantity),sum(amount_paid) from allorder
+where (category='Accessories' or category='Accs') and date(created_on)>=%s and store_id='spice'
+group by date(created_on) order by date(created_on);
+"""
 # DTO_QUERY="""
 # SELECT date(o.created),COUNT(*)
 # FROM  order_view o
 # JOIN users u ON u.id = o.user_id WHERE
 # (LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
 SELECT sum(quantity),sum(amount_paid)  FROM  allorder o
 JOIN users u ON u.id = o.user_id WHERE  (LOWER(u.referrer) NOT LIKE
 'emp%%' OR u.utm_campaign is not NULL) AND u.activated =1
 AND date(o.created_on) >= %s and store_id='spice' GROUP BY month(date(o.created_on));
 """
+MACCSB_QUERY="""
+select month(created_on),count(distinct user_id) from allorder
+where (category='Accessories' or category='Accs') and
+date(created_on)>=%s and store_id='spice' group by month(created_on) order by month(created_on);
+"""
+MACCSO_QUERY="""
+select month(created_on),sum(quantity),sum(amount_paid) from allorder
+where (category='Accessories' or category='Accs') and date(created_on)>=%s and store_id='spice'
+group by month(created_on) order by month(created_on);
+"""
 WNRU_QUERY="""
 SELECT COUNT(*)
 FROM users u WHERE
 (LOWER(u.referrer) NOT LIKE  'emp%%' OR u.utm_campaign is not NULL) AND u.activated =1
 SELECT sum(quantity),sum(amount_paid)  FROM  allorder o
 JOIN users u ON u.id = o.user_id WHERE  (LOWER(u.referrer) NOT LIKE
 'emp%%' OR u.utm_campaign is not NULL) AND u.activated =1
 AND date(o.created_on) >= %s and store_id='spice' GROUP BY WEEK(date(o.created_on));
 """
+WACCSB_QUERY="""
+select WEEK(created_on) AS week, count(distinct user_id) from allorder
+where (category='Accessories' or category='Accs')
+and  date(created_on)>=%s and store_id='spice'
+group by week(created_on)
+order by WEEK(created_on);
+"""
+WACCSO_QUERY="""
+select WEEK(created_on),sum(quantity),sum(amount_paid) from allorder
+where (category='Accessories' or category='Accs') and date(created_on)>=%s and store_id='spice'
+group by WEEK(created_on)
+order by WEEK(created_on);
+"""
 DQ_QUERY="""
 select sum(quantity) from flipkartorders where date(created)='%s';
 """
 DV_QUERY="""
 select sum(quantity*price) from flipkartorders where date(created)='%s';
     dauSql = DAU_QUERY
     dabSql = DAB_QUERY
     dtoSql = DTO_QUERY
     dvolSql=DVOL_QUERY
     downvolSql=DOWN_QUERY
+    daccsbSql=DACCSB_QUERY
+    daccsoSql = DACCSO_QUERY
     conn = getDbConnection()
     cursor = conn.cursor()
     cursor.execute(datesql,(cutOffDate,todaysDate()))
     result = cursor.fetchall()
     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)
+    worksheet.write(row, 10, 'DACCSU', boldStyle)
+    worksheet.write(row, 11, 'DACCSB', boldStyle)
+    worksheet.write(row, 12, 'DACCSOVOL', boldStyle)
+    worksheet.write(row, 13, 'DACCSOVAL', boldStyle)
     dateMap={}
     for r in result:
         row += 1
         column = 0
         for data in r :
         row=dateMap.get(str(r[0]))
         if row:
             worksheet.write(row, column, r[1])
             column += 1
             worksheet.write(row, column, r[2])
+    row = 1
+    column=10
+    breakDate = to_java_date(datetime.now())
+    currentDay = cutOff*1000
+    while True:
+        currentDay = currentDay + 86400000
+        result = get_mongo_connection_new().User.browsinghistories.find({"$and":[{'created':{"$gte":currentDay-86400000}},{'created':{"$lte":currentDay}},{"url":{"$regex" : "http://api.profittill.com/category/6"}}]}).distinct('user_id')
+        worksheet.write(row, column, len(result))
+        row=row+1
+        if currentDay>breakDate:
+            break
+    row = 0
+    cursor.execute(daccsbSql,(cutOffDate))
+    result = cursor.fetchall()
+    for r in result:
+        column = 11
+        row=dateMap.get(str(r[0]))
+        if row:
+            worksheet.write(row, column, r[1])
+    row = 0
+    cursor.execute(daccsoSql,(cutOffDate))
+    result = cursor.fetchall()
-#     z=0
-#     for x in sorted(dateWiseOrderMap):
-#         column = 6
-#         d = datetime.fromtimestamp(x/1000.0)
-#         row = dateMap[datetime.strftime(d,'%Y-%m-%d')]
-#         dq=DQ_QUERY%str(d)[:10]
-#         cursor.execute(dq)
-#         result = cursor.fetchall()
-#         for r in result:
+    for r in result:
-#             quantity= r[0]
-#             if quantity is None:
-#                 quantity=0
-#         worksheet.write(row,column,(dateWiseOrderMap.get(x).count)+quantity)
-#         column += 1
+        column = 12
-#
-#     row =0
-#     for x in sorted(dateWiseOrderMap):
-#         column = 7
-#         d = datetime.fromtimestamp(x/1000.0)
-#         row = dateMap[datetime.strftime(d, '%Y-%m-%d')]
-#         dq=DV_QUERY%str(d)[:10]
-#         cursor.execute(dq)
-#         result = cursor.fetchall()
-#         for r in result:
-#             quantity= r[0]
-#             if quantity is None:
-#                 quantity=0
-#         worksheet.write(row,column,(dateWiseOrderMap.get(x).value)+float(quantity))
-#
-#     #For Saholic
-#     cursor = conn.cursor()
-#     cursor.execute(datesql,(cutOffDate,todaysDate()))
-#     result = cursor.fetchall()
-#     for x in sorted(dateWiseSaholicOrderMap):
-#         z= to_x_date(x)
-#         row = dateMap.get(z)
+        row=dateMap.get(str(r[0]))
-#         if row:
+        if row:
-#             column = 8
-#             worksheet.write(row,column,dateWiseSaholicOrderMap.get(x).count)
+            worksheet.write(row, column, r[1])
-#
-#     for x in sorted(dateWiseSaholicOrderMap):
-#         z= to_x_date(x)
-#         row=dateMap.get(z)
-#         if row:
-#                 column = 9
+            column += 1
-#                 worksheet.write(row,column,dateWiseSaholicOrderMap.get(x).value)
+            worksheet.write(row, column, r[2])
 def generateMonthlyReport():
     monthSql = MONTH_QUERY
     mnruSql = MNRU_QUERY
     mauSql = MAU_QUERY
     mabSql = MAB_QUERY
     mtoSql = MTO_QUERY
     mvolSql=MVOL_QUERY
     mownvolSql=MOWN_QUERY
+    maccsbSql= MACCSB_QUERY
+    maccsoSql= MACCSO_QUERY
     conn = getDbConnection()
     cursor = conn.cursor()
     cursor.execute(monthSql,(cutOffDate,todaysDate()))
     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)
+    worksheet.write(row, 10, 'MACCSU', boldStyle)
+    worksheet.write(row, 11, 'MACCSB', boldStyle)
+    worksheet.write(row, 12, 'MACCSOVOL', boldStyle)
+    worksheet.write(row, 13, 'MACCSOVAL', boldStyle)
+    monthMapDb = {}
     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)
+            monthMapDb[str(data)]=row
             column += 1
     cursor.execute(MTRU_QUERY,(cutOffDate))
     result = cursor.fetchall()
     for r in result:
         row += 1
         column = 8
         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)
             column += 1
-#     z=0
-#     for x in sorted(monthWiseOrderMap):
-#         d = str(datetime.fromtimestamp(x/1000.0))
-#         nextday = (x/1000 + (monthMap.get(d[5:7]) * oneDay))*1000
-#         dnext = str(datetime.fromtimestamp(nextday/1000.0))
-#         row += 1
+    row=1
-#         column = 6
+    column=10
-#         dq=WQ_QUERY % (d[:10],dnext[:10])
+    breakDate = to_java_date(datetime.now())
-#         cursor.execute(dq)
-#         result = cursor.fetchall()
+    currentDay = cutOff*1000
-#         for r in result:
+    while True:
-#             quantity= r[0]
+        currentDay = currentDay + (monthMap.get(month_get(currentDay))*86400000)
-#             if quantity is None:
+        result = get_mongo_connection_new().User.browsinghistories.find({"$and":[{'created':{"$gte":currentDay- (monthMap.get(month_get(currentDay))*86400000)}},{'created':{"$lte":currentDay}},{"url":{"$regex" : "http://api.profittill.com/category/6"}}]}).distinct('user_id')
-#                 quantity=0
+        currentDay = currentDay+ 86400000
-#         worksheet.write(row,column,(monthWiseOrderMap.get(x).count)+quantity)
+        worksheet.write(row,column,len(result))
-#         column += 1
+        row=row+1
+        if currentDay>breakDate:
-#
+            break
-#     row =0
-#     for x in sorted(monthWiseOrderMap):
-#         row += 1
-#         column = 7
+    row = 0
-#         d = str(datetime.fromtimestamp(x/1000.0))
-#         nextday = (x/1000 + (monthMap.get(d[5:7]) * oneDay))*1000
-#         dnext = str(datetime.fromtimestamp(nextday/1000.0))
-#         dq=WV_QUERY % (d[:10],dnext[:10])
-#         cursor.execute(dq)
+    cursor.execute(maccsbSql,(cutOffDate))
-#         result = cursor.fetchall()
+    result = cursor.fetchall()
-#         for r in result:
+    for r in result:
-#             quantity= r[0]
+        column = 11
-#             if quantity is None:
+        row=monthMapDb.get(str(r[0]))
-#                 quantity=0
+        if row:
-#         worksheet.write(row,column,(monthWiseOrderMap.get(x).value)+float(quantity))
+            worksheet.write(row, column, r[1])
-#         column += 1
-#
-#     row =0
+    row = 0
-#     for x in sorted(monthWiseSaholicOrderMap):
+    cursor.execute(maccsoSql,(cutOffDate))
-#         row += 1
+    result = cursor.fetchall()
-#         column = 8
+    for r in result:
-#         worksheet.write(row,column,monthWiseSaholicOrderMap.get(x).count)
-#         column += 1
+        column = 12
-#     row =0
-#     for x in sorted(monthWiseSaholicOrderMap):
+        row=monthMapDb.get(str(r[0]))
-#         row += 1
+        if row:
-#         column = 9
-#         worksheet.write(row,column,monthWiseSaholicOrderMap.get(x).value)
+            worksheet.write(row, column, r[1])
-#         column += 1
+            column += 1
+            worksheet.write(row, column, r[2])
     workbook.save(TMP_FILE)
 def generateWeeklyReport():
     weekSql = WEEK_QUERY
     wnruSql = WNRU_QUERY
     wauSql = WAU_QUERY
     wabSql = WAB_QUERY
     wtoSql = WTO_QUERY
     wownSql=WOWN_QUERY
     wvolSql=WVOL_QUERY
+    waccsbSql = WACCSB_QUERY
+    waccsoSql = WACCSO_QUERY
     conn = getDbConnection()
     cursor = conn.cursor()
     cursor.execute(weekSql,(last_day(cutOffDate, 'sunday')))
     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)
+    worksheet.write(row, 10, 'WACCSU', boldStyle)
+    worksheet.write(row, 11, 'WACCSB', boldStyle)
+    worksheet.write(row, 12, 'WACCSVOL', boldStyle)
+    worksheet.write(row, 13, 'WACCSVAL', boldStyle)
+    weekMap={}
     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)
+        worksheet.write(row, column, int(r[0]) if type(r[0]) is float else r[0], date_format if type(r[0]) is date else default_format)
+        weekMap[str(r[1])]=row
-            column += 1
+        column += 1
     cursor.execute(MTRU_QUERY,(cutOffDate))
     result = cursor.fetchall()
     for r in result:
         sumDa=r[0]
         row += 1
         column = 8
         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)
             column += 1
+    row=1
+    column=10
+    breakDate = to_java_date(datetime.now())
+    currentDay = to_java_date(last_day(cutOffDate, 'sunday'))
+    while True:
+        currentDay = currentDay + (7*86400000)
+        result = get_mongo_connection_new().User.browsinghistories.find({"$and":[{'created':{"$gte":currentDay-(6*86400000)}},{'created':{"$lte":currentDay}},{"url":{"$regex" : "http://api.profittill.com/category/6"}}]}).distinct('user_id')
+        worksheet.write(row,column,len(result))
+        row=row+1
+        if currentDay>breakDate:
+            break
+    row = 0
+    cursor.execute(waccsbSql,(cutOffDate))
+    result = cursor.fetchall()
+    for r in result:
+        column = 11
+        row=weekMap.get(str(r[0]))
+        if row:
+            worksheet.write(row, column, r[1])
+    row = 0
+    cursor.execute(waccsoSql,(cutOffDate))
+    result = cursor.fetchall()
+    for r in result:
+        column = 12
+        row=weekMap.get(str(r[0]))
+        if row:
+            worksheet.write(row, column, r[1])
+            column += 1
+            worksheet.write(row, column, r[2])
-#     row = 0
-#     for x in sorted(weekWiseOrderMap):
-#         row += 1
-#         column = 6
-#         d = str(datetime.fromtimestamp(x/1000.0))
-#         nextday=oneDay*7*1000+x
-#         dnext = str(datetime.fromtimestamp(nextday/1000.0))
-#         dq=WQ_QUERY % (d[:10],dnext[:10])
-#         cursor.execute(dq)
-#         result = cursor.fetchall()
-#         for r in result:
-#             quantity= r[0]
-#             if quantity is None:
-#                 quantity=0
-#         worksheet.write(row,column,(weekWiseOrderMap.get(x).count)+quantity)
-#         column+=1
-#     row = 0
-#     for x in sorted(weekWiseOrderMap):
-#         row += 1
-#         column = 7
-#         d = str(datetime.fromtimestamp(x/1000.0))
-#         nextday=oneDay*7*1000+x
-#         dnext = str(datetime.fromtimestamp(nextday/1000.0))
-#         dq=WV_QUERY % (d[:10],dnext[:10])
-#         cursor.execute(dq)
-#         result = cursor.fetchall()
-#         for r in result:
-#             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)
     days_of_week = ['sunday','monday','tuesday','wednesday',
                         'thursday','friday','saturday']
     target_day = days_of_week.index(day_name.lower())
     delta_day = target_day - (d.isoweekday()%7)
     return d + timedelta(days=delta_day)
+def get_mongo_connection_new(host='45.33.50.227', port=27017):
+    global con
+    if con is None:
+        print "Establishing connection %s host and port %d" %(host,port)
+        try:
+            con = pymongo.MongoClient(host, port)
+        except Exception, e:
+            print e
+            return None
+    return con
 def main():
-    #populateYesterdayActiveUsers(24)
+    populateYesterdayActiveUsers(24)
     #populateValidOrders()
     #populateOrderMap()
     #populateWeekWiseMap1()
     #populateMonthWiseMap1()
     #populateSaholicOrderMap()
         date = datetime.fromtimestamp(java_timestamp / 1e3)
     except:
         return None
     return date.strftime('%Y-%m-%d')
+def month_get(java_timestamp):
+    try:
+        date = datetime.fromtimestamp(java_timestamp / 1e3)
+    except:
+        return None
+    return date.strftime('%m')
 if __name__ == '__main__':
     if len(sys.argv)==1:
         main()
     else:
         populateYesterdayActiveUsers(int(sys.argv[1]))

Subversion Repositories SmartDukaan

(root)/trunk/PyDTR/src/scripts/ActivityReport.py – Rev 17765 → 18012