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

 select date(o.created) as dateid,sum(quantity) as bquantity,sum(price)
 from flipkartorders o JOIN users u ON u.id = ifnull(o.user_id,139)  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 date(o.created)) a group by dateid;
 """
+DOWNO_QUERY="""
+SELECT date(created_on),count(distinct merchant_order_id)  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)
+order by date(o.created_on);
+"""
 DOWN_QUERY="""
 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);
 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);
 """
+DACCSTO_QUERY="""
+SELECT date(created_on),count(distinct merchant_order_id)  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'
+and (category='Accs' or category='Accessories') GROUP BY date(o.created_on)
+order by date(o.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);
 """
 JOIN users u ON u.id = s.user_id WHERE   (LOWER(u.referrer) NOT LIKE  'emp%%' OR u.utm_campaign is not NULL) AND u.activated =1
 AND date(s.created) BETWEEN %s AND %s
 GROUP by month(s.created)
 order by month(s.created);
 """
 MVOL_QUERY="""
 select sum(quantity), sum(value) from  (SELECT month(date(o.created_on)) as monthid,
 sum(quantity) as quantity,sum(amount_paid) as value  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
 select month(date(o.created)) as monthid,sum(quantity) as bquantity,sum(price)
 from flipkartorders o JOIN users u ON u.id = ifnull(o.user_id,139)  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 month(date(o.created))) a group by monthid;
 """
+MOWNO_QUERY="""
+SELECT month(created_on),count(distinct merchant_order_id)  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(o.created_on)
+order by month(o.created_on);
+"""
 MOWN_QUERY="""
 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));
 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);
 """
+MACCSTO_QUERY="""
+SELECT month(created_on),count(distinct merchant_order_id)  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'
+and (category='Accs' or category='Accessories') GROUP BY month(o.created_on)
+order by month(o.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);
 """
 AND u.activated =1  AND date(o.created_on) >= %s
 GROUP BY WEEK(date(o.created_on)) UNION
 select WEEK(date(o.created)) as weekid,sum(quantity) as bquantity,sum(price)
 from flipkartorders o JOIN users u ON u.id = ifnull(o.user_id,139)
 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))) a group by weekid;"""
+u.activated =1 AND date(o.created) >= %s GROUP BY WEEK(date(o.created))) a group by weekid;"""
+WOWNO_QUERY="""
+SELECT week(created_on),count(distinct merchant_order_id)  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(o.created_on)
+order by week(o.created_on);
+"""
 WOWN_QUERY="""
 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);
 """
+WACCSTO_QUERY="""
+SELECT week(created_on),count(distinct merchant_order_id)  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'
+and (category='Accs' or category='Accessories') GROUP BY week(o.created_on)
+order by week(o.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);
     dtoSql = DTO_QUERY
     dvolSql=DVOL_QUERY
     downvolSql=DOWN_QUERY
     daccsbSql=DACCSB_QUERY
     daccsoSql = DACCSO_QUERY
+    downoSql=DOWNO_QUERY
+    daccstoSql=DACCSTO_QUERY
     conn = getDbConnection()
     cursor = conn.cursor()
     cursor.execute(datesql,(cutOffDate,todaysDate()))
     result = cursor.fetchall()
     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, 'DOWNORD', boldStyle)
-    worksheet.write(row, 8, 'DOWNVOL', boldStyle)
+    worksheet.write(row, 9, 'DOWNVOL', boldStyle)
-    worksheet.write(row, 9, 'DOWNVAL', boldStyle)
+    worksheet.write(row, 10, 'DOWNVAL', boldStyle)
-    worksheet.write(row, 10, 'DACCSU', boldStyle)
+    worksheet.write(row, 11, 'DACCSU', boldStyle)
-    worksheet.write(row, 11, 'DACCSB', boldStyle)
+    worksheet.write(row, 12, 'DACCSB', boldStyle)
+    worksheet.write(row, 13, 'DACCSORD', boldStyle)
-    worksheet.write(row, 12, 'DACCSOVOL', boldStyle)
+    worksheet.write(row, 14, 'DACCSOVOL', boldStyle)
-    worksheet.write(row, 13, 'DACCSOVAL', boldStyle)
+    worksheet.write(row, 15, 'DACCSOVAL', boldStyle)
     dateMap={}
     for r in result:
         row += 1
         column = 0
         for data in r :
         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 = 0
-    cursor.execute(downvolSql,(cutOffDate))
+    cursor.execute(downoSql,(cutOffDate))
     result = cursor.fetchall()
     for r in result:
         column = 8
         row=dateMap.get(str(r[0]))
         if row:
             worksheet.write(row, column, r[1])
+    row = 0
+    cursor.execute(downvolSql,(cutOffDate))
+    result = cursor.fetchall()
+    for r in result:
+        column = 9
+        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
+    column=11
     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')
     row = 0
     cursor.execute(daccsbSql,(cutOffDate))
     result = cursor.fetchall()
     for r in result:
-        column = 11
+        column = 12
         row=dateMap.get(str(r[0]))
         if row:
             worksheet.write(row, column, r[1])
     row = 0
+    cursor.execute(daccstoSql,(cutOffDate))
+    result = cursor.fetchall()
+    for r in result:
+        column = 13
+        row=dateMap.get(str(r[0]))
+        if row:
+            worksheet.write(row, column, r[1])
+    row = 0
     cursor.execute(daccsoSql,(cutOffDate))
     result = cursor.fetchall()
     for r in result:
-        column = 12
+        column = 14
         row=dateMap.get(str(r[0]))
         if row:
             worksheet.write(row, column, r[1])
             column += 1
             worksheet.write(row, column, r[2])
     mabSql = MAB_QUERY
     mtoSql = MTO_QUERY
     mvolSql=MVOL_QUERY
     mownvolSql=MOWN_QUERY
     maccsbSql= MACCSB_QUERY
+    maccsoSql= MACCSO_QUERY
+    mownoSql= MOWNO_QUERY
-    maccsoSql= MACCSO_QUERY
+    maccstoSql= MACCSTO_QUERY
     conn = getDbConnection()
     cursor = conn.cursor()
     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, 'MOWNORD', boldStyle)
-    worksheet.write(row, 8, 'MOWNVOL', boldStyle)
+    worksheet.write(row, 9, 'MOWNVOL', boldStyle)
-    worksheet.write(row, 9, 'MOWNVAL', boldStyle)
+    worksheet.write(row, 10, 'MOWNVAL', boldStyle)
-    worksheet.write(row, 10, 'MACCSU', boldStyle)
+    worksheet.write(row, 11, 'MACCSU', boldStyle)
-    worksheet.write(row, 11, 'MACCSB', boldStyle)
+    worksheet.write(row, 12, 'MACCSB', boldStyle)
+    worksheet.write(row, 13, 'MACCSORD', boldStyle)
-    worksheet.write(row, 12, 'MACCSOVOL', boldStyle)
+    worksheet.write(row, 14, 'MACCSOVOL', boldStyle)
-    worksheet.write(row, 13, 'MACCSOVAL', boldStyle)
+    worksheet.write(row, 15, 'MACCSOVAL', boldStyle)
     monthMapDb = {}
     for r in result:
         row += 1
         column = 0
         row += 1
         column = 6
         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 = 0
+    cursor.execute(mownoSql,(cutOffDate))
+    result = cursor.fetchall()
+    for r in result:
+        column = 8
+        row=monthMapDb.get(str(r[0]))
+        if row:
+            worksheet.write(row, column, r[1])
     row = 0
     cursor.execute(mownvolSql,(cutOffDate))
     result = cursor.fetchall()
     for r in result:
         row += 1
-        column = 8
+        column = 9
         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
+    column=11
     breakDate = to_java_date(datetime.now())
     currentDay = cutOff*1000
     while True:
         currentDay = currentDay + (monthMap.get(month_get(currentDay))*86400000)
         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')
     row = 0
     cursor.execute(maccsbSql,(cutOffDate))
     result = cursor.fetchall()
     for r in result:
-        column = 11
+        column = 12
+        row=monthMapDb.get(str(r[0]))
+        if row:
+            worksheet.write(row, column, r[1])
+    row = 0
+    cursor.execute(maccstoSql,(cutOffDate))
+    result = cursor.fetchall()
+    for r in result:
+        column = 13
         row=monthMapDb.get(str(r[0]))
         if row:
             worksheet.write(row, column, r[1])
     row = 0
     cursor.execute(maccsoSql,(cutOffDate))
     result = cursor.fetchall()
     for r in result:
-        column = 12
+        column = 14
         row=monthMapDb.get(str(r[0]))
         if row:
             worksheet.write(row, column, r[1])
             column += 1
             worksheet.write(row, column, r[2])
     wtoSql = WTO_QUERY
     wownSql=WOWN_QUERY
     wvolSql=WVOL_QUERY
     waccsbSql = WACCSB_QUERY
     waccsoSql = WACCSO_QUERY
+    wownoSql=WOWNO_QUERY
+    waccstoSql=WACCSTO_QUERY
     conn = getDbConnection()
     cursor = conn.cursor()
     cursor.execute(weekSql,(last_day(cutOffDate, 'sunday')))
     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, 'WOWNORD', boldStyle)
-    worksheet.write(row, 8, 'WOWNVOL', boldStyle)
+    worksheet.write(row, 9, 'WOWNVOL', boldStyle)
-    worksheet.write(row, 9, 'WOWNVAL', boldStyle)
+    worksheet.write(row, 10, 'WOWNVAL', boldStyle)
-    worksheet.write(row, 10, 'WACCSU', boldStyle)
+    worksheet.write(row, 11, 'WACCSU', boldStyle)
-    worksheet.write(row, 11, 'WACCSB', boldStyle)
+    worksheet.write(row, 12, 'WACCSB', boldStyle)
+    worksheet.write(row, 13, 'WACCSORD', boldStyle)
-    worksheet.write(row, 12, 'WACCSVOL', boldStyle)
+    worksheet.write(row, 14, 'WACCSVOL', boldStyle)
-    worksheet.write(row, 13, 'WACCSVAL', boldStyle)
+    worksheet.write(row, 15, 'WACCSVAL', boldStyle)
     weekMap={}
     for r in result:
         row += 1
         column = 0
         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
         row += 1
         column = 6
         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 = 0
+    cursor.execute(wownoSql,(cutOffDate))
+    result = cursor.fetchall()
+    for r in result:
+        column = 8
+        row=weekMap.get(str(r[0]))
+        if row:
+            worksheet.write(row, column, r[1])
     row = 0
     cursor.execute(wownSql,(cutOffDate))
     result = cursor.fetchall()
     for r in result:
         row += 1
-        column = 8
+        column = 9
         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
+    column=11
     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
+        column = 12
         row=weekMap.get(str(r[0]))
         if row:
             worksheet.write(row, column, r[1])
+    row = 0
+    cursor.execute(waccstoSql,(cutOffDate))
+    result = cursor.fetchall()
+    for r in result:
+        column = 13
+        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
+        column = 14
         row=weekMap.get(str(r[0]))
         if row:
             worksheet.write(row, column, r[1])
             column += 1
             worksheet.write(row, column, r[2])

Subversion Repositories SmartDukaan

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