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



MONTH_QUERY="""
SELECT month(d.visited) from daily_visitors d 
join users u on u.id=d.user_id where 
(LOWER(u.referrer) NOT LIKE  'emp%%' OR u.utm_campaign is not NULL) AND u.activated =1
AND date(d.visited) BETWEEN %s AND %s
group by year(d.visited),month(d.visited);
"""
 
WEEK_QUERY="""
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))
ORDER BY year(date(o.created)),WEEK(date(o.created))
"""
 
DNRU_QUERY="""
SELECT count(*) 
FROM users u WHERE 

 
MNRU_QUERY="""
SELECT count(*) FROM users u WHERE 
(LOWER(u.referrer) NOT LIKE  'emp%%' OR u.utm_campaign is not NULL) AND u.activated =1
AND date(u.activation_time) BETWEEN %s AND %s 
group by year(u.activation_time),month(u.activation_time)
order by year(u.activation_time),month(u.activation_time); 
"""
 
MAU_QUERY="""
SELECT count(distinct d.user_id) 
FROM daily_visitors d join users u where u.id=d.user_id AND 
(LOWER(u.referrer) NOT LIKE  'emp%%' OR u.utm_campaign is not NULL) AND u.activated =1
AND date(d.visited) BETWEEN %s AND %s 
group by year(d.visited),month(d.visited)
order by year(d.visited),month(d.visited); 
"""
 
# MAB_QUERY="""
# SELECT COUNT(DISTINCT o.user_id ) 
# FROM  order_view o

select count(DISTINCT s.user_id) from 
(SELECT created, id,user_id FROM  order_view where STATUS =  'ORDER_CREATED' OR STATUS ='DETAIL_CREATED' 
union  SELECT created, id,user_id FROM   flipkartorders where date(created)>'2015-03-22') s 
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 year(s.created),month(s.created)
order by year(s.created),month(s.created);
"""
MTO_QUERY="""
select count(*) from 
(SELECT created, id,user_id FROM  order_view where STATUS =  'ORDER_CREATED' OR STATUS ='DETAIL_CREATED' 
union  SELECT created, id,ifnull(user_id,139) FROM   flipkartorders where date(created)>'2015-03-22') s 
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 year(s.created),month(s.created)
order by year(s.created),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 

SELECT COUNT(*)
FROM users u WHERE 
(LOWER(u.referrer) NOT LIKE  'emp%%' OR u.utm_campaign is not NULL) AND u.activated =1
AND date(u.activation_time) >=%s 
GROUP BY WEEK(date(u.activation_time))
ORDER BY year(date(u.activation_time)),WEEK(date(u.activation_time))
 
"""
WAU_QUERY="""
SELECT  COUNT(distinct d.user_id) AS total
FROM  daily_visitors d join users u where u.id=d.user_id AND 
(LOWER(u.referrer) NOT LIKE  'emp%%' OR u.utm_campaign is not NULL) AND u.activated =1
AND date(d.visited) >= %s 
GROUP BY WEEK(d.visited)
ORDER BY year(date(d.visited)),WEEK(d.visited)
"""
 
 
# WAB_QUERY="""
# SELECT COUNT( DISTINCT o.user_id ) 

(SELECT created, id,user_id FROM  order_view where STATUS =  'ORDER_CREATED' OR STATUS ='DETAIL_CREATED' 
union  SELECT created, id,user_id FROM   flipkartorders where date(created)>'2015-03-22') s 
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) >= %s 
GROUP by week(s.created)
order by year(date(s.created)),week(s.created);
"""
 
WTO_QUERY="""
select count(*) from 
(SELECT created, id,user_id FROM  order_view where STATUS =  'ORDER_CREATED' OR STATUS ='DETAIL_CREATED' 
union  SELECT created, id,ifnull(user_id,139) FROM   flipkartorders where date(created)>'2015-03-22') s 
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) >=%s 
GROUP by week(s.created)
order by year(date(s.created)),week(s.created);
"""
 
WVOL_QUERY="""
select sum(quantity), sum(value) from 
(SELECT WEEK(date(o.created_on)) as weekid, sum(quantity) as quantity,sum(amount_paid) as value 

    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', 'amit.gupta@saholic.com','chaitnaya.vats@shop2020.in']
    MAILTO = ['manas.kapoor@saholic.com']
    mailServer.login(SENDER, PASSWORD)
    mailServer.sendmail(SENDER, MAILTO, msg.as_string())
 
class __Order:
    

            return None
    return con
 
 
def main():
    #populateYesterdayActiveUsers(24)
    #populateValidOrders()
    #populateOrderMap()
    #populateWeekWiseMap1()
    #populateMonthWiseMap1()
    #populateSaholicOrderMap()
    #populateSaholicWeekWiseMap1()
    #populateSaholicMonthWiseMap1()
    generateDailyReport()
    generateWeeklyReport()
    generateMonthlyReport()
    #sendmail(["manas.kapoor@shop2020.in","rajneesh.arora@saholic.com", "amit.gupta@shop2020.in","chaitnaya.vats@shop2020.in"], "", TMP_FILE, SUBJECT)
    sendmail(["manas.kapoor@shop2020.in"], "", TMP_FILE, SUBJECT)
    
                 
def to_x_date(java_timestamp):
    try:
        date = datetime.fromtimestamp(java_timestamp / 1e3)       

Rev 18021	Rev 18154
Line 87...	Line 87...
87	`MONTH_QUERY="""`	87	`MONTH_QUERY="""`
88	`SELECT month(d.visited) from daily_visitors d`	88	`SELECT month(d.visited) from daily_visitors d`
89	`join users u on u.id=d.user_id where`	89	`join users u on u.id=d.user_id where`
90	`(LOWER(u.referrer) NOT LIKE 'emp%%' OR u.utm_campaign is not NULL) AND u.activated =1`	90	`(LOWER(u.referrer) NOT LIKE 'emp%%' OR u.utm_campaign is not NULL) AND u.activated =1`
91	`AND date(d.visited) BETWEEN %s AND %s`	91	`AND date(d.visited) BETWEEN %s AND %s`
92	`group by month(d.visited);`	92	`group by year(d.visited),month(d.visited);`
93	`"""`	93	`"""`
94		94
95	`WEEK_QUERY="""`	95	`WEEK_QUERY="""`
96	`SELECT CONCAT(date(o.created), ' - ', date(o.created) + INTERVAL 6 DAY) AS week,WEEK(o.created)`	96	`SELECT CONCAT(date(o.created), ' - ', date(o.created) + INTERVAL 6 DAY) AS week,WEEK(o.created)`
97	`FROM order_view o`	97	`FROM order_view o`
98	`JOIN users u ON u.id = o.user_id WHERE`	98	`JOIN users u ON u.id = o.user_id WHERE`
99	`(LOWER(u.referrer) NOT LIKE 'emp%%' OR u.utm_campaign is not NULL) AND u.activated =1`	99	`(LOWER(u.referrer) NOT LIKE 'emp%%' OR u.utm_campaign is not NULL) AND u.activated =1`
100	`AND date(o.created) >= %s`	100	`AND date(o.created) >= %s`
101	`GROUP BY WEEK(date(o.created))`	101	`GROUP BY WEEK(date(o.created))`
102	`ORDER BY WEEK(date(o.created))`	102	`ORDER BY year(date(o.created)),WEEK(date(o.created))`
103	`"""`	103	`"""`
104		104
105	`DNRU_QUERY="""`	105	`DNRU_QUERY="""`
106	`SELECT count(*)`	106	`SELECT count(*)`
107	`FROM users u WHERE`	107	`FROM users u WHERE`
Line 213...	Line 213...
213		213
214	`MNRU_QUERY="""`	214	`MNRU_QUERY="""`
215	`SELECT count(*) FROM users u WHERE`	215	`SELECT count(*) FROM users u WHERE`
216	`(LOWER(u.referrer) NOT LIKE 'emp%%' OR u.utm_campaign is not NULL) AND u.activated =1`	216	`(LOWER(u.referrer) NOT LIKE 'emp%%' OR u.utm_campaign is not NULL) AND u.activated =1`
217	`AND date(u.activation_time) BETWEEN %s AND %s`	217	`AND date(u.activation_time) BETWEEN %s AND %s`
218	`group by month(u.activation_time)`	218	`group by year(u.activation_time),month(u.activation_time)`
219	`order by month(u.activation_time)`	219	`order by year(u.activation_time),month(u.activation_time);`
220	`"""`	220	`"""`
221		221
222	`MAU_QUERY="""`	222	`MAU_QUERY="""`
223	`SELECT count(distinct d.user_id)`	223	`SELECT count(distinct d.user_id)`
224	`FROM daily_visitors d join users u where u.id=d.user_id AND`	224	`FROM daily_visitors d join users u where u.id=d.user_id AND`
225	`(LOWER(u.referrer) NOT LIKE 'emp%%' OR u.utm_campaign is not NULL) AND u.activated =1`	225	`(LOWER(u.referrer) NOT LIKE 'emp%%' OR u.utm_campaign is not NULL) AND u.activated =1`
226	`AND date(d.visited) BETWEEN %s AND %s`	226	`AND date(d.visited) BETWEEN %s AND %s`
227	`group by month(d.visited)`	227	`group by year(d.visited),month(d.visited)`
228	`order by month(d.visited);`	228	`order by year(d.visited),month(d.visited);`
229	`"""`	229	`"""`
230		230
231	`# MAB_QUERY="""`	231	`# MAB_QUERY="""`
232	`# SELECT COUNT(DISTINCT o.user_id )`	232	`# SELECT COUNT(DISTINCT o.user_id )`
233	`# FROM order_view o`	233	`# FROM order_view o`
Line 254...	Line 254...
254	`select count(DISTINCT s.user_id) from`	254	`select count(DISTINCT s.user_id) from`
255	`(SELECT created, id,user_id FROM order_view where STATUS = 'ORDER_CREATED' OR STATUS ='DETAIL_CREATED'`	255	`(SELECT created, id,user_id FROM order_view where STATUS = 'ORDER_CREATED' OR STATUS ='DETAIL_CREATED'`
256	`union SELECT created, id,user_id FROM flipkartorders where date(created)>'2015-03-22') s`	256	`union SELECT created, id,user_id FROM flipkartorders where date(created)>'2015-03-22') s`
257	`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`	257	`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`
258	`AND date(s.created) BETWEEN %s AND %s`	258	`AND date(s.created) BETWEEN %s AND %s`
259	`GROUP by month(s.created)`	259	`GROUP by year(s.created),month(s.created)`
260	`order by month(s.created);`	260	`order by year(s.created),month(s.created);`
261	`"""`	261	`"""`
262	`MTO_QUERY="""`	262	`MTO_QUERY="""`
263	`select count(*) from`	263	`select count(*) from`
264	`(SELECT created, id,user_id FROM order_view where STATUS = 'ORDER_CREATED' OR STATUS ='DETAIL_CREATED'`	264	`(SELECT created, id,user_id FROM order_view where STATUS = 'ORDER_CREATED' OR STATUS ='DETAIL_CREATED'`
265	`union SELECT created, id,ifnull(user_id,139) FROM flipkartorders where date(created)>'2015-03-22') s`	265	`union SELECT created, id,ifnull(user_id,139) FROM flipkartorders where date(created)>'2015-03-22') s`
266	`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`	266	`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`
267	`AND date(s.created) BETWEEN %s AND %s`	267	`AND date(s.created) BETWEEN %s AND %s`
268	`GROUP by month(s.created)`	268	`GROUP by year(s.created),month(s.created)`
269	`order by month(s.created);`	269	`order by year(s.created),month(s.created);`
270	`"""`	270	`"""`
271		271
272	`MVOL_QUERY="""`	272	`MVOL_QUERY="""`
273	`select sum(quantity), sum(value) from (SELECT month(date(o.created_on)) as monthid,`	273	`select sum(quantity), sum(value) from (SELECT month(date(o.created_on)) as monthid,`
274	`sum(quantity) as quantity,sum(amount_paid) as value FROM allorder o`	274	`sum(quantity) as quantity,sum(amount_paid) as value FROM allorder o`
Line 319...	Line 319...
319	`SELECT COUNT(*)`	319	`SELECT COUNT(*)`
320	`FROM users u WHERE`	320	`FROM users u WHERE`
321	`(LOWER(u.referrer) NOT LIKE 'emp%%' OR u.utm_campaign is not NULL) AND u.activated =1`	321	`(LOWER(u.referrer) NOT LIKE 'emp%%' OR u.utm_campaign is not NULL) AND u.activated =1`
322	`AND date(u.activation_time) >=%s`	322	`AND date(u.activation_time) >=%s`
323	`GROUP BY WEEK(date(u.activation_time))`	323	`GROUP BY WEEK(date(u.activation_time))`
324	`ORDER BY WEEK(date(u.activation_time))`	324	`ORDER BY year(date(u.activation_time)),WEEK(date(u.activation_time))`
325		325
326	`"""`	326	`"""`
327	`WAU_QUERY="""`	327	`WAU_QUERY="""`
328	`SELECT COUNT(distinct d.user_id) AS total`	328	`SELECT COUNT(distinct d.user_id) AS total`
329	`FROM daily_visitors d join users u where u.id=d.user_id AND`	329	`FROM daily_visitors d join users u where u.id=d.user_id AND`
330	`(LOWER(u.referrer) NOT LIKE 'emp%%' OR u.utm_campaign is not NULL) AND u.activated =1`	330	`(LOWER(u.referrer) NOT LIKE 'emp%%' OR u.utm_campaign is not NULL) AND u.activated =1`
331	`AND date(d.visited) >= %s`	331	`AND date(d.visited) >= %s`
332	`GROUP BY WEEK(d.visited)`	332	`GROUP BY WEEK(d.visited)`
333	`ORDER BY WEEK(d.visited)`	333	`ORDER BY year(date(d.visited)),WEEK(d.visited)`
334	`"""`	334	`"""`
335		335
336		336
337	`# WAB_QUERY="""`	337	`# WAB_QUERY="""`
338	`# SELECT COUNT( DISTINCT o.user_id )`	338	`# SELECT COUNT( DISTINCT o.user_id )`
Line 361...	Line 361...
361	`(SELECT created, id,user_id FROM order_view where STATUS = 'ORDER_CREATED' OR STATUS ='DETAIL_CREATED'`	361	`(SELECT created, id,user_id FROM order_view where STATUS = 'ORDER_CREATED' OR STATUS ='DETAIL_CREATED'`
362	`union SELECT created, id,user_id FROM flipkartorders where date(created)>'2015-03-22') s`	362	`union SELECT created, id,user_id FROM flipkartorders where date(created)>'2015-03-22') s`
363	`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`	363	`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`
364	`AND date(s.created) >= %s`	364	`AND date(s.created) >= %s`
365	`GROUP by week(s.created)`	365	`GROUP by week(s.created)`
366	`order by week(s.created);`	366	`order by year(date(s.created)),week(s.created);`
367	`"""`	367	`"""`
368		368
369	`WTO_QUERY="""`	369	`WTO_QUERY="""`
370	`select count(*) from`	370	`select count(*) from`
371	`(SELECT created, id,user_id FROM order_view where STATUS = 'ORDER_CREATED' OR STATUS ='DETAIL_CREATED'`	371	`(SELECT created, id,user_id FROM order_view where STATUS = 'ORDER_CREATED' OR STATUS ='DETAIL_CREATED'`
372	`union SELECT created, id,ifnull(user_id,139) FROM flipkartorders where date(created)>'2015-03-22') s`	372	`union SELECT created, id,ifnull(user_id,139) FROM flipkartorders where date(created)>'2015-03-22') s`
373	`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`	373	`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`
374	`AND date(s.created) >=%s`	374	`AND date(s.created) >=%s`
375	`GROUP by week(s.created)`	375	`GROUP by week(s.created)`
376	`order by week(s.created);`	376	`order by year(date(s.created)),week(s.created);`
377	`"""`	377	`"""`
378		378
379	`WVOL_QUERY="""`	379	`WVOL_QUERY="""`
380	`select sum(quantity), sum(value) from`	380	`select sum(quantity), sum(value) from`
381	`(SELECT WEEK(date(o.created_on)) as weekid, sum(quantity) as quantity,sum(amount_paid) as value`	381	`(SELECT WEEK(date(o.created_on)) as weekid, sum(quantity) as quantity,sum(amount_paid) as value`
Line 1046...	Line 1046...
1046	`fileMsg.set_payload(file(TMP_FILE).read())`	1046	`fileMsg.set_payload(file(TMP_FILE).read())`
1047	`encoders.encode_base64(fileMsg)`	1047	`encoders.encode_base64(fileMsg)`
1048	`fileMsg.add_header('Content-Disposition', 'attachment;filename=' + fileName)`	1048	`fileMsg.add_header('Content-Disposition', 'attachment;filename=' + fileName)`
1049	`msg.attach(fileMsg)`	1049	`msg.attach(fileMsg)`
1050		1050
1051	`MAILTO = ['manas.kapoor@saholic.com','rajneesh.arora@saholic.com', 'amit.gupta@saholic.com','chaitnaya.vats@shop2020.in']`	1051	`#MAILTO = ['manas.kapoor@saholic.com','rajneesh.arora@saholic.com', 'amit.gupta@saholic.com','chaitnaya.vats@shop2020.in']`
1052	`#MAILTO = ['manas.kapoor@saholic.com']`	1052	`MAILTO = ['manas.kapoor@saholic.com']`
1053	`mailServer.login(SENDER, PASSWORD)`	1053	`mailServer.login(SENDER, PASSWORD)`
1054	`mailServer.sendmail(SENDER, MAILTO, msg.as_string())`	1054	`mailServer.sendmail(SENDER, MAILTO, msg.as_string())`
1055		1055
1056	`class __Order:`	1056	`class __Order:`
1057		1057
Line 1319...	Line 1319...
1319	`return None`	1319	`return None`
1320	`return con`	1320	`return con`
1321		1321
1322		1322
1323	`def main():`	1323	`def main():`
1324	`populateYesterdayActiveUsers(24)`	1324	`#populateYesterdayActiveUsers(24)`
1325	`#populateValidOrders()`	1325	`#populateValidOrders()`
1326	`#populateOrderMap()`	1326	`#populateOrderMap()`
1327	`#populateWeekWiseMap1()`	1327	`#populateWeekWiseMap1()`
1328	`#populateMonthWiseMap1()`	1328	`#populateMonthWiseMap1()`
1329	`#populateSaholicOrderMap()`	1329	`#populateSaholicOrderMap()`
1330	`#populateSaholicWeekWiseMap1()`	1330	`#populateSaholicWeekWiseMap1()`
1331	`#populateSaholicMonthWiseMap1()`	1331	`#populateSaholicMonthWiseMap1()`
1332	`generateDailyReport()`	1332	`generateDailyReport()`
1333	`generateWeeklyReport()`	1333	`generateWeeklyReport()`
1334	`generateMonthlyReport()`	1334	`generateMonthlyReport()`
1335	`sendmail(["manas.kapoor@shop2020.in","rajneesh.arora@saholic.com", "amit.gupta@shop2020.in","chaitnaya.vats@shop2020.in"], "", TMP_FILE, SUBJECT)`	1335	`#sendmail(["manas.kapoor@shop2020.in","rajneesh.arora@saholic.com", "amit.gupta@shop2020.in","chaitnaya.vats@shop2020.in"], "", TMP_FILE, SUBJECT)`
1336	`#sendmail(["manas.kapoor@shop2020.in"], "", TMP_FILE, SUBJECT)`	1336	`sendmail(["manas.kapoor@shop2020.in"], "", TMP_FILE, SUBJECT)`
1337		1337
1338		1338
1339	`def to_x_date(java_timestamp):`	1339	`def to_x_date(java_timestamp):`
1340	`try:`	1340	`try:`
1341	`date = datetime.fromtimestamp(java_timestamp / 1e3)`	1341	`date = datetime.fromtimestamp(java_timestamp / 1e3)`

Subversion Repositories SmartDukaan

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