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



SMTP_SERVER = "smtp.gmail.com"
SMTP_PORT = 587    
 
DATE_QUERY="""
SELECT date(d.visited) from daily_visitors d 
join users u where u.id=d.user_id AND LOWER(u.referrer) NOT LIKE  'emp%' AND u.activated =1
AND date(d.visited) > '2015-03-08' group by visited ;
"""
 
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%' AND u.activated =1
group by month(d.visited);
"""
 
WEEK_QUERY="""
SELECT CONCAT(date(o.created), ' - ', date(o.created) + INTERVAL 6 DAY) AS week 
FROM  order_view o
JOIN users u ON u.id = o.user_id
WHERE LOWER(u.referrer) NOT LIKE  'emp%' AND u.activated =1
AND date(o.created) > '2015-03-08' 
GROUP BY WEEK(date(o.created))
ORDER BY WEEK(date(o.created))
"""
 
DNRU_QUERY="""
SELECT count(*) 
FROM users u WHERE LOWER(u.referrer) NOT LIKE  'emp%' AND u.activated =1
AND date(u.created) > '2015-03-08'
group by date(u.created) 
order by date(u.created)
"""
 
DAU_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%' AND u.activated =1
AND date(d.visited) > '2015-03-08' group by visited ;
"""
 
DAB_QUERY="""
SELECT COUNT(DISTINCT o.user_id ) 
FROM  order_view o
JOIN users u ON u.id = o.user_id
WHERE LOWER(u.referrer) NOT LIKE  'emp%' AND u.activated =1
AND o.STATUS =  'ORDER_CREATED'
AND date(o.created) > '2015-03-08' 
group by date(o.created)
order by date(o.created)
"""
 
DTO_QUERY="""
SELECT COUNT( *) 
FROM  order_view o
JOIN users u ON u.id = o.user_id
WHERE LOWER(u.referrer) NOT LIKE  'emp%' AND u.activated =1
AND o.STATUS =  'ORDER_CREATED'
AND date(o.created) > '2015-03-08' 
GROUP BY DATE(o.created)
order by date(o.created) 
"""
 
MNRU_QUERY="""
SELECT count(*)
FROM users u WHERE LOWER(u.referrer) NOT LIKE  'emp%' AND u.activated =1
AND date(u.created) > '2015-03-08' 
group by month(u.created)
order by month(u.created) 
"""
 
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%' AND u.activated =1
AND date(d.visited) > '2015-03-08' 
group by month(d.visited)
order by month(d.visited); 
"""
 
MAB_QUERY="""
SELECT COUNT(DISTINCT o.user_id ) 
FROM  order_view o
JOIN users u ON u.id = o.user_id where
LOWER(u.referrer) NOT LIKE  'emp%' AND u.activated =1
AND o.STATUS =  'ORDER_CREATED'
AND date(o.created) > '2015-03-08' 
group by month(o.created)
order by month(o.created); 
"""
 
MTO_QUERY="""
SELECT COUNT( *) 
FROM  order_view o
JOIN users u ON u.id = o.user_id where
LOWER(u.referrer) NOT LIKE  'emp%' AND u.activated =1
AND o.STATUS =  'ORDER_CREATED'
AND date(o.created) > '2015-03-08' 
GROUP BY MONTH(o.created)
order by month(o.created); 
"""
 
WNRU_QUERY="""
SELECT COUNT(*)
FROM users u WHERE 
LOWER(u.referrer) NOT LIKE  'emp%' AND u.activated =1
AND date(u.created) > '2015-03-08' 
GROUP BY WEEK(date(u.created))
ORDER BY WEEK(date(u.created))
 
"""
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%' AND u.activated =1
AND date(d.visited) > '2015-03-08' 
GROUP BY WEEK(d.visited)
ORDER BY WEEK(d.visited)
"""
 
 
WAB_QUERY="""
SELECT COUNT( DISTINCT o.user_id ) 
FROM  order_view o
JOIN users u ON u.id = o.user_id
WHERE LOWER(u.referrer) NOT LIKE  'emp%' AND u.activated =1
AND o.STATUS =  'ORDER_CREATED'
AND date(o.created) > '2015-03-08' 
GROUP BY WEEK(date(o.created))
ORDER BY WEEK(date(o.created))
"""
 
WTO_QUERY="""
SELECT COUNT(*)
FROM  order_view o
JOIN users u ON u.id = o.user_id
WHERE LOWER(u.referrer) NOT LIKE  'emp%' AND u.activated =1
AND o.STATUS =  'ORDER_CREATED'
AND date(o.created) > '2015-03-08' 
GROUP BY WEEK(date(o.created))
ORDER BY WEEK(date(o.created))
"""

Rev 14805	Rev 14871
Line 33...	Line 33...
33	`SMTP_SERVER = "smtp.gmail.com"`	33	`SMTP_SERVER = "smtp.gmail.com"`
34	`SMTP_PORT = 587`	34	`SMTP_PORT = 587`
35		35
36	`DATE_QUERY="""`	36	`DATE_QUERY="""`
37	`SELECT date(d.visited) from daily_visitors d`	37	`SELECT date(d.visited) from daily_visitors d`
38	`join users u where u.id=d.user_id AND(LOWER(u.referrer) IS NULL AND u.id > 350 AND u.id NOT IN (387,399,418)) OR LOWER(u.referrer) NOT LIKE 'emp%'`	38	`join users u where u.id=d.user_id AND LOWER(u.referrer) NOT LIKE 'emp%' AND u.activated =1`
39	`AND date(d.visited) > '2015-03-08' group by visited ;`	39	`AND date(d.visited) > '2015-03-08' group by visited ;`
40	`"""`	40	`"""`
41		41
42	`MONTH_QUERY="""`	42	`MONTH_QUERY="""`
43	`SELECT month(d.visited) from daily_visitors d`	43	`SELECT month(d.visited) from daily_visitors d`
44	`join users u on u.id=d.user_id where (LOWER(u.referrer) IS NULL AND u.id > 350 AND u.id NOT IN (387,399,418)) OR LOWER(u.referrer) NOT LIKE 'emp%'`	44	`join users u on u.id=d.user_id where LOWER(u.referrer) NOT LIKE 'emp%' AND u.activated =1`
45	`group by month(d.visited);`	45	`group by month(d.visited);`
46	`"""`	46	`"""`
47		47
48	`WEEK_QUERY="""`	48	`WEEK_QUERY="""`
49	`SELECT CONCAT(date(o.created), ' - ', date(o.created) + INTERVAL 6 DAY) AS week`	49	`SELECT CONCAT(date(o.created), ' - ', date(o.created) + INTERVAL 6 DAY) AS week`
50	`FROM order_view o`	50	`FROM order_view o`
51	`JOIN users u ON u.id = o.user_id`	51	`JOIN users u ON u.id = o.user_id`
52	`WHERE (LOWER(u.referrer) IS NULL AND u.id > 350 AND u.id NOT IN (387,399,418)) OR LOWER(u.referrer) NOT LIKE 'emp%'`	52	`WHERE LOWER(u.referrer) NOT LIKE 'emp%' AND u.activated =1`
53	`AND date(o.created) > '2015-03-08'`	53	`AND date(o.created) > '2015-03-08'`
54	`GROUP BY WEEK(date(o.created))`	54	`GROUP BY WEEK(date(o.created))`
55	`ORDER BY WEEK(date(o.created))`	55	`ORDER BY WEEK(date(o.created))`
56	`"""`	56	`"""`
57		57
58	`DNRU_QUERY="""`	58	`DNRU_QUERY="""`
59	`SELECT count(*)`	59	`SELECT count(*)`
60	`FROM users u WHERE (LOWER(u.referrer) IS NULL AND u.id > 350 AND u.id NOT IN (387,399,418)) OR LOWER(u.referrer) NOT LIKE 'emp%'`	60	`FROM users u WHERE LOWER(u.referrer) NOT LIKE 'emp%' AND u.activated =1`
61	`AND date(u.created) > '2015-03-08'`	61	`AND date(u.created) > '2015-03-08'`
62	`group by date(u.created)`	62	`group by date(u.created)`
63	`order by date(u.created)`	63	`order by date(u.created)`
64	`"""`	64	`"""`
65		65
66	`DAU_QUERY="""`	66	`DAU_QUERY="""`
67	`SELECT count(distinct d.user_id)`	67	`SELECT count(distinct d.user_id)`
68	`FROM daily_visitors d join users u where u.id=d.user_id AND (LOWER(u.referrer) IS NULL AND u.id > 350 AND u.id NOT IN (387,399,418)) OR LOWER(u.referrer) NOT LIKE 'emp%'`	68	`FROM daily_visitors d join users u where u.id=d.user_id AND LOWER(u.referrer) NOT LIKE 'emp%' AND u.activated =1`
69	`AND date(d.visited) > '2015-03-08' group by visited ;`	69	`AND date(d.visited) > '2015-03-08' group by visited ;`
70	`"""`	70	`"""`
71		71
72	`DAB_QUERY="""`	72	`DAB_QUERY="""`
73	`SELECT COUNT(DISTINCT o.user_id )`	73	`SELECT COUNT(DISTINCT o.user_id )`
74	`FROM order_view o`	74	`FROM order_view o`
75	`JOIN users u ON u.id = o.user_id`	75	`JOIN users u ON u.id = o.user_id`
76	`WHERE (LOWER(u.referrer) IS NULL AND u.id > 350 AND u.id NOT IN (387,399,418)) OR LOWER(u.referrer) NOT LIKE 'emp%'`	76	`WHERE LOWER(u.referrer) NOT LIKE 'emp%' AND u.activated =1`
77	`AND o.STATUS = 'ORDER_CREATED'`	77	`AND o.STATUS = 'ORDER_CREATED'`
78	`AND date(o.created) > '2015-03-08'`	78	`AND date(o.created) > '2015-03-08'`
79	`group by date(o.created)`	79	`group by date(o.created)`
80	`order by date(o.created)`	80	`order by date(o.created)`
81	`"""`	81	`"""`
82		82
83	`DTO_QUERY="""`	83	`DTO_QUERY="""`
84	`SELECT COUNT( *)`	84	`SELECT COUNT( *)`
85	`FROM order_view o`	85	`FROM order_view o`
86	`JOIN users u ON u.id = o.user_id`	86	`JOIN users u ON u.id = o.user_id`
87	`WHERE (LOWER(u.referrer) IS NULL AND u.id > 350 AND u.id NOT IN (387,399,418)) OR LOWER(u.referrer) NOT LIKE 'emp%'`	87	`WHERE LOWER(u.referrer) NOT LIKE 'emp%' AND u.activated =1`
88	`AND o.STATUS = 'ORDER_CREATED'`	88	`AND o.STATUS = 'ORDER_CREATED'`
89	`AND date(o.created) > '2015-03-08'`	89	`AND date(o.created) > '2015-03-08'`
90	`GROUP BY DATE(o.created)`	90	`GROUP BY DATE(o.created)`
91	`order by date(o.created)`	91	`order by date(o.created)`
92	`"""`	92	`"""`
93		93
94	`MNRU_QUERY="""`	94	`MNRU_QUERY="""`
95	`SELECT count(*)`	95	`SELECT count(*)`
96	`FROM users u WHERE (LOWER(u.referrer) IS NULL AND u.id > 350 AND u.id NOT IN (387,399,418)) OR lower(u.referrer) not like 'emp%'`	96	`FROM users u WHERE LOWER(u.referrer) NOT LIKE 'emp%' AND u.activated =1`
97	`AND date(u.created) > '2015-03-08'`	97	`AND date(u.created) > '2015-03-08'`
98	`group by month(u.created)`	98	`group by month(u.created)`
99	`order by month(u.created)`	99	`order by month(u.created)`
100	`"""`	100	`"""`
101		101
102	`MAU_QUERY="""`	102	`MAU_QUERY="""`
103	`SELECT count(distinct d.user_id)`	103	`SELECT count(distinct d.user_id)`
104	`FROM daily_visitors d join users u where u.id=d.user_id AND (LOWER(u.referrer) IS NULL AND u.id > 350 AND u.id NOT IN (387,399,418)) OR LOWER(u.referrer) NOT LIKE 'emp%'`	104	`FROM daily_visitors d join users u where u.id=d.user_id AND LOWER(u.referrer) NOT LIKE 'emp%' AND u.activated =1`
105	`AND date(d.visited) > '2015-03-08'`	105	`AND date(d.visited) > '2015-03-08'`
106	`group by month(d.visited)`	106	`group by month(d.visited)`
107	`order by month(d.visited);`	107	`order by month(d.visited);`
108	`"""`	108	`"""`
109		109
110	`MAB_QUERY="""`	110	`MAB_QUERY="""`
111	`SELECT COUNT(DISTINCT o.user_id )`	111	`SELECT COUNT(DISTINCT o.user_id )`
112	`FROM order_view o`	112	`FROM order_view o`
113	`JOIN users u ON u.id = o.user_id where`	113	`JOIN users u ON u.id = o.user_id where`
114	`(LOWER(u.referrer) IS NULL AND u.id > 350 AND u.id NOT IN (387,399,418)) OR LOWER(u.referrer) NOT LIKE 'emp%'`	114	`LOWER(u.referrer) NOT LIKE 'emp%' AND u.activated =1`
115	`AND o.STATUS = 'ORDER_CREATED'`	115	`AND o.STATUS = 'ORDER_CREATED'`
116	`AND date(o.created) > '2015-03-08'`	116	`AND date(o.created) > '2015-03-08'`
117	`group by month(o.created)`	117	`group by month(o.created)`
118	`order by month(o.created);`	118	`order by month(o.created);`
119	`"""`	119	`"""`
120		120
121	`MTO_QUERY="""`	121	`MTO_QUERY="""`
122	`SELECT COUNT( *)`	122	`SELECT COUNT( *)`
123	`FROM order_view o`	123	`FROM order_view o`
124	`JOIN users u ON u.id = o.user_id where`	124	`JOIN users u ON u.id = o.user_id where`
125	`(LOWER(u.referrer) IS NULL AND u.id > 350 AND u.id NOT IN (387,399,418)) OR LOWER(u.referrer) NOT LIKE 'emp%'`	125	`LOWER(u.referrer) NOT LIKE 'emp%' AND u.activated =1`
126	`AND o.STATUS = 'ORDER_CREATED'`	126	`AND o.STATUS = 'ORDER_CREATED'`
127	`AND date(o.created) > '2015-03-08'`	127	`AND date(o.created) > '2015-03-08'`
128	`GROUP BY MONTH(o.created)`	128	`GROUP BY MONTH(o.created)`
129	`order by month(o.created);`	129	`order by month(o.created);`
130	`"""`	130	`"""`
131		131
132	`WNRU_QUERY="""`	132	`WNRU_QUERY="""`
133	`SELECT COUNT(*)`	133	`SELECT COUNT(*)`
134	`FROM users u WHERE`	134	`FROM users u WHERE`
135	`(LOWER(u.referrer) IS NULL AND u.id > 350 AND u.id NOT IN (387,399,418)) OR LOWER(u.referrer) NOT LIKE 'emp%'`	135	`LOWER(u.referrer) NOT LIKE 'emp%' AND u.activated =1`
136	`AND date(u.created) > '2015-03-08'`	136	`AND date(u.created) > '2015-03-08'`
137	`GROUP BY WEEK(date(u.created))`	137	`GROUP BY WEEK(date(u.created))`
138	`ORDER BY WEEK(date(u.created))`	138	`ORDER BY WEEK(date(u.created))`
139		139
140	`"""`	140	`"""`
141	`WAU_QUERY="""`	141	`WAU_QUERY="""`
142	`SELECT COUNT(distinct d.user_id) AS total`	142	`SELECT COUNT(distinct d.user_id) AS total`
143	`FROM daily_visitors d join users u where u.id=d.user_id AND (LOWER(u.referrer) IS NULL AND u.id > 350 AND u.id NOT IN (387,399,418)) OR LOWER(u.referrer) NOT LIKE 'emp%'`	143	`FROM daily_visitors d join users u where u.id=d.user_id AND LOWER(u.referrer) NOT LIKE 'emp%' AND u.activated =1`
144	`AND date(d.visited) > '2015-03-08'`	144	`AND date(d.visited) > '2015-03-08'`
145	`GROUP BY WEEK(d.visited)`	145	`GROUP BY WEEK(d.visited)`
146	`ORDER BY WEEK(d.visited)`	146	`ORDER BY WEEK(d.visited)`
147	`"""`	147	`"""`
148		148
149		149
150	`WAB_QUERY="""`	150	`WAB_QUERY="""`
151	`SELECT COUNT( DISTINCT o.user_id )`	151	`SELECT COUNT( DISTINCT o.user_id )`
152	`FROM order_view o`	152	`FROM order_view o`
153	`JOIN users u ON u.id = o.user_id`	153	`JOIN users u ON u.id = o.user_id`
154	`WHERE (LOWER(u.referrer) IS NULL AND u.id > 350 AND u.id NOT IN (387,399,418)) OR LOWER(u.referrer) NOT LIKE 'emp%'`	154	`WHERE LOWER(u.referrer) NOT LIKE 'emp%' AND u.activated =1`
155	`AND o.STATUS = 'ORDER_CREATED'`	155	`AND o.STATUS = 'ORDER_CREATED'`
156	`AND date(o.created) > '2015-03-08'`	156	`AND date(o.created) > '2015-03-08'`
157	`GROUP BY WEEK(date(o.created))`	157	`GROUP BY WEEK(date(o.created))`
158	`ORDER BY WEEK(date(o.created))`	158	`ORDER BY WEEK(date(o.created))`
159	`"""`	159	`"""`
160		160
161	`WTO_QUERY="""`	161	`WTO_QUERY="""`
162	`SELECT COUNT(*)`	162	`SELECT COUNT(*)`
163	`FROM order_view o`	163	`FROM order_view o`
164	`JOIN users u ON u.id = o.user_id`	164	`JOIN users u ON u.id = o.user_id`
165	`WHERE (LOWER(u.referrer) IS NULL AND u.id > 350 AND u.id NOT IN (387,399,418)) OR LOWER(u.referrer) NOT LIKE 'emp%'`	165	`WHERE LOWER(u.referrer) NOT LIKE 'emp%' AND u.activated =1`
166	`AND o.STATUS = 'ORDER_CREATED'`	166	`AND o.STATUS = 'ORDER_CREATED'`
167	`AND date(o.created) > '2015-03-08'`	167	`AND date(o.created) > '2015-03-08'`
168	`GROUP BY WEEK(date(o.created))`	168	`GROUP BY WEEK(date(o.created))`
169	`ORDER BY WEEK(date(o.created))`	169	`ORDER BY WEEK(date(o.created))`
170	`"""`	170	`"""`

Subversion Repositories SmartDukaan

(root)/trunk/PyDTR/src/scripts/ActivityReport.py – Rev 14805 → 14871