Subversion Repositories SmartDukaan

Rev

Rev 18021 | Rev 18155 | Go to most recent revision | Details | Compare with Previous | Last modification | View Log | RSS feed

Rev Author Line No. Line
17134 amit.gupta 1 
from datetime import date, datetime, timedelta, datetime
2 
from dtr.storage import DataService, Mongo
3 
from dtr.storage.DataService import Orders, Users, CallHistory
4 
from dtr.storage.Mysql import getOrdersAfterDate, getOrdersByTag
5 
from dtr.utils import utils
17265 manas 6 
from dtr.utils.utils import toTimeStamp, to_java_date, to_py_date
17134 amit.gupta 7 
from elixir import *
14772 kshitij.so 8 
from email import encoders
9 
from email.mime.base import MIMEBase
10 
from email.mime.multipart import MIMEMultipart
11 
from email.mime.text import MIMEText
17134 amit.gupta 12 
from operator import or_
14772 kshitij.so 13 
from pymongo.mongo_client import MongoClient
17209 amit.gupta 14 
from sqlalchemy.sql.expression import func, and_
17134 amit.gupta 15 
from time import strftime
14772 kshitij.so 16 
from xlrd import open_workbook
17 
from xlutils.copy import copy
18 
from xlwt.Workbook import Workbook
19 
import MySQLdb
17134 amit.gupta 20 
import pymongo
14772 kshitij.so 21 
import smtplib
17134 amit.gupta 22 
import sys
14772 kshitij.so 23 
import time
24 
import xlwt
25 
 
26 
DB_HOST = "localhost"
27 
DB_USER = "root"
28 
DB_PASSWORD = "shop2020"
29 
DB_NAME = "dtr"
17266 manas 30 
TMP_FILE = "User_Activity_Report.xls"
14772 kshitij.so 31 
 
15228 manas 32 
con = None
33 
dateWiseOrderMap = {}
34 
weekWiseOrderMap = {}
35 
monthWiseOrderMap = {}
16964 manas 36 
dateWiseSaholicOrderMap = {}
37 
weekWiseSaholicOrderMap = {}
38 
monthWiseSaholicOrderMap = {}
39 
 
15228 manas 40 
orderIds = []
17209 amit.gupta 41 
 
42 
curDate = date.today()
17396 manas 43 
cutOffMonth = (datetime.now().month + 10)%12
44 
cutOffYear = curDate.year - (0 if curDate.month-2 > 0 else 1)
17217 manas 45 
cutOff = to_java_date(datetime(cutOffYear, cutOffMonth, 1))/1000
46 
cutOffDate=datetime(cutOffYear, cutOffMonth, 1)
15228 manas 47 
#cutOff = 1425234600
48 
oneDay = 86400
17217 manas 49 
#monthCutOff = 1425148200
50 
#weekCutOff = 1425839400
51 
#newWeekCutOff = 1425839400
52 
#newMonthCutOff = 1425148200
53 
weekCutOff=cutOff
54 
newWeekCutOff = cutOff
55 
monthCutOff=cutOff
56 
newMonthCutOff=cutOff
15228 manas 57 
DataService.initialize()
58 
 
15551 manas 59 
monthMap = {  '01':30,
60 
            '02':28,
61 
            '03':30,
62 
            '04':29,
63 
            '05':30,
64 
            '06':29,
65 
            '07':30,
66 
            '08':30,
67 
            '09':29,
68 
            '10':30,
69 
            '11':29,
70 
            '12':30
71 
        }
15228 manas 72 
 
14772 kshitij.so 73 
# KEY NAMES
74 
SENDER = "cnc.center@shop2020.in"
75 
PASSWORD = "5h0p2o2o"
15551 manas 76 
SUBJECT = "User Activity Report for " + date.today().isoformat()
14772 kshitij.so 77 
SMTP_SERVER = "smtp.gmail.com"
78 
SMTP_PORT = 587
79 
 
17128 manas 80 
DATE_QUERY="""
81 
SELECT date(d.visited) from daily_visitors d
82 
join users u where u.id=d.user_id AND
17217 manas 83 
(LOWER(u.referrer) NOT LIKE  'emp%%' OR u.utm_campaign is not NULL) AND u.activated =1
84 
AND date(d.visited) BETWEEN %s AND %s group by visited ;
17128 manas 85 
"""
86 
 
14772 kshitij.so 87 
MONTH_QUERY="""
88 
SELECT month(d.visited) from daily_visitors d
15244 manas 89 
join users u on u.id=d.user_id where
17217 manas 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
18154 manas 92 
group by year(d.visited),month(d.visited);
14772 kshitij.so 93 
"""
94 
 
95 
WEEK_QUERY="""
18012 manas 96 
SELECT CONCAT(date(o.created), ' - ', date(o.created) + INTERVAL 6 DAY) AS week,WEEK(o.created)
14772 kshitij.so 97 
FROM  order_view o
15244 manas 98 
JOIN users u ON u.id = o.user_id WHERE
17217 manas 99 
(LOWER(u.referrer) NOT LIKE  'emp%%' OR u.utm_campaign is not NULL) AND u.activated =1
100 
AND date(o.created) >= %s
14772 kshitij.so 101 
GROUP BY WEEK(date(o.created))
18154 manas 102 
ORDER BY year(date(o.created)),WEEK(date(o.created))
14772 kshitij.so 103 
"""
104 
 
105 
DNRU_QUERY="""
106 
SELECT count(*)
15244 manas 107 
FROM users u WHERE
17217 manas 108 
(LOWER(u.referrer) NOT LIKE  'emp%%' OR u.utm_campaign is not NULL) AND u.activated =1
109 
AND date(u.activation_time) BETWEEN %s AND %s
17031 manas 110 
group by date(u.activation_time)
111 
order by date(u.activation_time)
14772 kshitij.so 112 
"""
113 
 
114 
DAU_QUERY="""
115 
SELECT count(distinct d.user_id)
15244 manas 116 
FROM daily_visitors d join users u WHERE u.id=d.user_id
17217 manas 117 
AND (LOWER(u.referrer) NOT LIKE  'emp%%' OR u.utm_campaign is not NULL) AND u.activated =1
118 
AND date(d.visited) BETWEEN %s AND %s group by visited ;
14772 kshitij.so 119 
"""
120 
 
15551 manas 121 
# DAB_QUERY="""
122 
# SELECT COUNT(DISTINCT o.user_id )
123 
# FROM  order_view o
124 
# JOIN users u ON u.id = o.user_id WHERE
125 
# (LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
126 
# AND (o.STATUS =  'ORDER_CREATED' OR o.STATUS ='DETAIL_CREATED')
127 
# AND date(o.created) > '2015-03-08'
128 
# group by date(o.created)
129 
# order by date(o.created)
130 
# """
14772 kshitij.so 131 
DAB_QUERY="""
15551 manas 132 
select count(DISTINCT s.user_id) from
133 
(SELECT created, id,user_id FROM  order_view where STATUS =  'ORDER_CREATED' OR STATUS ='DETAIL_CREATED'
134 
union  SELECT created, id,user_id FROM   flipkartorders where date(created)>'2015-03-22') s
135 
JOIN users u ON u.id = s.user_id WHERE
17217 manas 136 
(LOWER(u.referrer) NOT LIKE  'emp%%' OR u.utm_campaign is not NULL) AND u.activated = 1
137 
AND date(s.created) BETWEEN %s AND %s
15551 manas 138 
GROUP by date(s.created)
139 
ORDER by date(s.created);
14772 kshitij.so 140 
"""
141 
 
142 
DTO_QUERY="""
15551 manas 143 
select count(*) from
144 
(SELECT created, id,user_id FROM  order_view where STATUS =  'ORDER_CREATED' OR STATUS ='DETAIL_CREATED'
145 
union  SELECT created, id,ifnull(user_id,139) FROM   flipkartorders where date(created)>'2015-03-22') s
146 
JOIN users u ON u.id = s.user_id WHERE
17217 manas 147 
(LOWER(u.referrer) NOT LIKE  'emp%%' OR u.utm_campaign is not NULL) AND u.activated = 1
148 
AND date(s.created) BETWEEN %s AND %s
15551 manas 149 
GROUP by date(s.created)
150 
ORDER by date(s.created);
14772 kshitij.so 151 
"""
17396 manas 152 
 
153 
DVOL_QUERY="""
154 
select sum(quantity), sum(value) from  (SELECT date(o.created_on) as dateid,
155 
sum(quantity) as quantity,sum(amount_paid) as value  FROM  allorder o
156 
JOIN users u ON u.id = o.user_id WHERE (LOWER(u.referrer) NOT LIKE   'emp%%' OR u.utm_campaign is not NULL)
157 
AND u.activated =1  AND date(o.created_on) >=%s
158 
GROUP BY date(o.created_on) UNION
159 
select date(o.created) as dateid,sum(quantity) as bquantity,sum(price)
17765 manas 160 
from flipkartorders o JOIN users u ON u.id = ifnull(o.user_id,139)  WHERE  (LOWER(u.referrer) NOT LIKE
17396 manas 161 
'emp%%' OR u.utm_campaign is not NULL) AND  u.activated =1 AND date(o.created) >= %s
162 
GROUP BY date(o.created)) a group by dateid;
163 
"""
18021 manas 164 
DOWNO_QUERY="""
165 
SELECT date(created_on),count(distinct merchant_order_id)  FROM  allorder o
166 
JOIN users u ON u.id = o.user_id WHERE  (LOWER(u.referrer) NOT LIKE   'emp%%' OR
167 
u.utm_campaign is not NULL) AND u.activated =1  AND date(o.created_on) >=%s and store_id='spice'
168 
GROUP BY date(o.created_on)
169 
order by date(o.created_on);
170 
"""
17396 manas 171 
DOWN_QUERY="""
172 
SELECT date(created_on),sum(quantity),sum(amount_paid)  FROM  allorder o
173 
JOIN users u ON u.id = o.user_id WHERE  (LOWER(u.referrer) NOT LIKE
174 
'emp%%' OR u.utm_campaign is not NULL) AND u.activated =1
175 
AND date(o.created_on) >= %s and store_id='spice' GROUP BY date(o.created_on);
176 
"""
18012 manas 177 
 
178 
DACCSB_QUERY="""
179 
select date(created_on),count(distinct user_id) from allorder
180 
where (category='Accessories' or category='Accs') and
181 
date(created_on)>=%s and store_id='spice' group by date(created_on) order by date(created_on);
182 
"""
183 
 
18021 manas 184 
DACCSTO_QUERY="""
185 
SELECT date(created_on),count(distinct merchant_order_id)  FROM  allorder o  JOIN
186 
users u ON u.id = o.user_id WHERE  (LOWER(u.referrer) NOT LIKE   'emp%%' OR u.utm_campaign is not NULL)
187 
AND u.activated =1  AND date(o.created_on) >=%s and store_id='spice'
188 
and (category='Accs' or category='Accessories') GROUP BY date(o.created_on)
189 
order by date(o.created_on);
190 
"""
191 
 
18012 manas 192 
DACCSO_QUERY="""
193 
select date(created_on),sum(quantity),sum(amount_paid) from allorder
194 
where (category='Accessories' or category='Accs') and date(created_on)>=%s and store_id='spice'
195 
group by date(created_on) order by date(created_on);
196 
"""
197 
 
15551 manas 198 
# DTO_QUERY="""
199 
# SELECT date(o.created),COUNT(*)
200 
# FROM  order_view o
201 
# JOIN users u ON u.id = o.user_id WHERE
202 
# (LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
203 
# AND (o.STATUS =  'ORDER_CREATED' OR o.STATUS ='DETAIL_CREATED')
204 
# AND date(o.created) > '2015-03-08'
205 
# GROUP BY DATE(o.created)
206 
# order by date(o.created)
207 
#"""
17217 manas 208 
MTRU_QUERY="""
209 
SELECT count(*) FROM users u WHERE  (LOWER(u.referrer) NOT LIKE  'emp%%' OR u.utm_campaign is not NULL)
210 
AND u.activated =1
211 
AND date(u.activation_time) < %s;
212 
"""
14772 kshitij.so 213 
 
214 
MNRU_QUERY="""
15244 manas 215 
SELECT count(*) FROM users u WHERE
17217 manas 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
18154 manas 218 
group by year(u.activation_time),month(u.activation_time)
219 
order by year(u.activation_time),month(u.activation_time);
14772 kshitij.so 220 
"""
221 
 
222 
MAU_QUERY="""
223 
SELECT count(distinct d.user_id)
15244 manas 224 
FROM daily_visitors d join users u where u.id=d.user_id AND
17217 manas 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
18154 manas 227 
group by year(d.visited),month(d.visited)
228 
order by year(d.visited),month(d.visited);
14772 kshitij.so 229 
"""
230 
 
15551 manas 231 
# MAB_QUERY="""
232 
# SELECT COUNT(DISTINCT o.user_id )
233 
# FROM  order_view o
234 
# JOIN users u ON u.id = o.user_id where
235 
# (LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
236 
# AND (o.STATUS =  'ORDER_CREATED' OR o.STATUS ='DETAIL_CREATED')
237 
# AND date(o.created) > '2015-03-08'
238 
# group by month(o.created)
239 
# order by month(o.created);
240 
# """
241 
 
242 
# MTO_QUERY="""
243 
# SELECT COUNT( *)
244 
# FROM  order_view o
245 
# JOIN users u ON u.id = o.user_id where
246 
# (LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
247 
# AND (o.STATUS =  'ORDER_CREATED' OR o.STATUS ='DETAIL_CREATED')
248 
# AND date(o.created) > '2015-03-08'
249 
# GROUP BY MONTH(o.created)
250 
# order by month(o.created);
251 
# """
252 
 
14772 kshitij.so 253 
MAB_QUERY="""
15551 manas 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'
256 
union  SELECT created, id,user_id FROM   flipkartorders where date(created)>'2015-03-22') s
17217 manas 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
18154 manas 259 
GROUP by year(s.created),month(s.created)
260 
order by year(s.created),month(s.created);
14772 kshitij.so 261 
"""
262 
MTO_QUERY="""
15551 manas 263 
select count(*) from
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
17217 manas 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
18154 manas 268 
GROUP by year(s.created),month(s.created)
269 
order by year(s.created),month(s.created);
14772 kshitij.so 270 
"""
18021 manas 271 
 
17396 manas 272 
MVOL_QUERY="""
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
275 
JOIN users u ON u.id = o.user_id WHERE (LOWER(u.referrer) NOT LIKE   'emp%%' OR u.utm_campaign is not NULL)
276 
AND u.activated =1  AND date(o.created_on) >=%s
277 
GROUP BY month(date(o.created_on)) UNION
278 
select month(date(o.created)) as monthid,sum(quantity) as bquantity,sum(price)
17765 manas 279 
from flipkartorders o JOIN users u ON u.id = ifnull(o.user_id,139)  WHERE  (LOWER(u.referrer) NOT LIKE
17396 manas 280 
'emp%%' OR u.utm_campaign is not NULL) AND  u.activated =1 AND date(o.created) >= %s
281 
GROUP BY month(date(o.created))) a group by monthid;
282 
"""
18021 manas 283 
 
284 
MOWNO_QUERY="""
285 
SELECT month(created_on),count(distinct merchant_order_id)  FROM  allorder o
286 
JOIN users u ON u.id = o.user_id WHERE  (LOWER(u.referrer) NOT LIKE   'emp%%' OR
287 
u.utm_campaign is not NULL) AND u.activated =1  AND date(o.created_on) >=%s and store_id='spice'
288 
GROUP BY month(o.created_on)
289 
order by month(o.created_on);
290 
"""
291 
 
17396 manas 292 
MOWN_QUERY="""
293 
SELECT sum(quantity),sum(amount_paid)  FROM  allorder o
294 
JOIN users u ON u.id = o.user_id WHERE  (LOWER(u.referrer) NOT LIKE
295 
'emp%%' OR u.utm_campaign is not NULL) AND u.activated =1
296 
AND date(o.created_on) >= %s and store_id='spice' GROUP BY month(date(o.created_on));
297 
"""
18012 manas 298 
MACCSB_QUERY="""
299 
select month(created_on),count(distinct user_id) from allorder
300 
where (category='Accessories' or category='Accs') and
301 
date(created_on)>=%s and store_id='spice' group by month(created_on) order by month(created_on);
302 
"""
14772 kshitij.so 303 
 
18021 manas 304 
MACCSTO_QUERY="""
305 
SELECT month(created_on),count(distinct merchant_order_id)  FROM  allorder o  JOIN
306 
users u ON u.id = o.user_id WHERE  (LOWER(u.referrer) NOT LIKE   'emp%%' OR u.utm_campaign is not NULL)
307 
AND u.activated =1  AND date(o.created_on) >=%s and store_id='spice'
308 
and (category='Accs' or category='Accessories') GROUP BY month(o.created_on)
309 
order by month(o.created_on);
310 
"""
311 
 
18012 manas 312 
MACCSO_QUERY="""
313 
select month(created_on),sum(quantity),sum(amount_paid) from allorder
314 
where (category='Accessories' or category='Accs') and date(created_on)>=%s and store_id='spice'
315 
group by month(created_on) order by month(created_on);
316 
"""
317 
 
14772 kshitij.so 318 
WNRU_QUERY="""
319 
SELECT COUNT(*)
320 
FROM users u WHERE
17217 manas 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
17031 manas 323 
GROUP BY WEEK(date(u.activation_time))
18154 manas 324 
ORDER BY year(date(u.activation_time)),WEEK(date(u.activation_time))
14772 kshitij.so 325 
 
326 
"""
327 
WAU_QUERY="""
328 
SELECT  COUNT(distinct d.user_id) AS total
15244 manas 329 
FROM  daily_visitors d join users u where u.id=d.user_id AND
17217 manas 330 
(LOWER(u.referrer) NOT LIKE  'emp%%' OR u.utm_campaign is not NULL) AND u.activated =1
331 
AND date(d.visited) >= %s
14772 kshitij.so 332 
GROUP BY WEEK(d.visited)
18154 manas 333 
ORDER BY year(date(d.visited)),WEEK(d.visited)
14772 kshitij.so 334 
"""
335 
 
336 
 
15551 manas 337 
# WAB_QUERY="""
338 
# SELECT COUNT( DISTINCT o.user_id )
339 
# FROM  order_view o
340 
# JOIN users u ON u.id = o.user_id WHERE
341 
# (LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
342 
# AND (o.STATUS =  'ORDER_CREATED' OR o.STATUS ='DETAIL_CREATED')
343 
# AND date(o.created) > '2015-03-08'
344 
# GROUP BY WEEK(date(o.created))
345 
# ORDER BY WEEK(date(o.created))
346 
# """
347 
 
348 
# WTO_QUERY="""
349 
# SELECT COUNT(*)
350 
# FROM  order_view o
351 
# JOIN users u ON u.id = o.user_id WHERE
352 
# (LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
353 
# AND (o.STATUS =  'ORDER_CREATED' OR o.STATUS ='DETAIL_CREATED')
354 
# AND date(o.created) > '2015-03-08'
355 
# GROUP BY WEEK(date(o.created))
356 
# ORDER BY WEEK(date(o.created))
357 
# """
358 
 
14772 kshitij.so 359 
WAB_QUERY="""
15551 manas 360 
select count(distinct user_id) from
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
17217 manas 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
15551 manas 365 
GROUP by week(s.created)
18154 manas 366 
order by year(date(s.created)),week(s.created);
14772 kshitij.so 367 
"""
368 
 
369 
WTO_QUERY="""
15551 manas 370 
select count(*) from
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
17217 manas 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
15551 manas 375 
GROUP by week(s.created)
18154 manas 376 
order by year(date(s.created)),week(s.created);
14772 kshitij.so 377 
"""
17269 manas 378 
 
17265 manas 379 
WVOL_QUERY="""
17269 manas 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
382 
FROM  allorder o  JOIN users u ON u.id = o.user_id WHERE
383 
(LOWER(u.referrer) NOT LIKE   'emp%%' OR u.utm_campaign is not NULL)
384 
AND u.activated =1  AND date(o.created_on) >= %s
385 
GROUP BY WEEK(date(o.created_on)) UNION
386 
select WEEK(date(o.created)) as weekid,sum(quantity) as bquantity,sum(price)
17765 manas 387 
from flipkartorders o JOIN users u ON u.id = ifnull(o.user_id,139)
17269 manas 388 
WHERE  (LOWER(u.referrer) NOT LIKE  'emp%%' OR u.utm_campaign is not NULL) AND
18021 manas 389 
u.activated =1 AND date(o.created) >= %s GROUP BY WEEK(date(o.created))) a group by weekid;"""
390 
 
391 
WOWNO_QUERY="""
392 
SELECT week(created_on),count(distinct merchant_order_id)  FROM  allorder o
393 
JOIN users u ON u.id = o.user_id WHERE  (LOWER(u.referrer) NOT LIKE   'emp%%' OR
394 
u.utm_campaign is not NULL) AND u.activated =1  AND date(o.created_on) >=%s and store_id='spice'
395 
GROUP BY week(o.created_on)
396 
order by week(o.created_on);
397 
"""
398 
 
17265 manas 399 
WOWN_QUERY="""
400 
SELECT sum(quantity),sum(amount_paid)  FROM  allorder o
401 
JOIN users u ON u.id = o.user_id WHERE  (LOWER(u.referrer) NOT LIKE
402 
'emp%%' OR u.utm_campaign is not NULL) AND u.activated =1
403 
AND date(o.created_on) >= %s and store_id='spice' GROUP BY WEEK(date(o.created_on));
404 
"""
18021 manas 405 
 
18012 manas 406 
WACCSB_QUERY="""
407 
select WEEK(created_on) AS week, count(distinct user_id) from allorder
408 
where (category='Accessories' or category='Accs')
409 
and  date(created_on)>=%s and store_id='spice'
410 
group by week(created_on)
411 
order by WEEK(created_on);
412 
"""
18021 manas 413 
 
414 
WACCSTO_QUERY="""
415 
SELECT week(created_on),count(distinct merchant_order_id)  FROM  allorder o  JOIN
416 
users u ON u.id = o.user_id WHERE  (LOWER(u.referrer) NOT LIKE   'emp%%' OR u.utm_campaign is not NULL)
417 
AND u.activated =1  AND date(o.created_on) >=%s and store_id='spice'
418 
and (category='Accs' or category='Accessories') GROUP BY week(o.created_on)
419 
order by week(o.created_on);
420 
"""
421 
 
18012 manas 422 
WACCSO_QUERY="""
423 
select WEEK(created_on),sum(quantity),sum(amount_paid) from allorder
424 
where (category='Accessories' or category='Accs') and date(created_on)>=%s and store_id='spice'
425 
group by WEEK(created_on)
426 
order by WEEK(created_on);
427 
"""
428 
 
15551 manas 429 
DQ_QUERY="""
430 
select sum(quantity) from flipkartorders where date(created)='%s';
431 
"""
432 
DV_QUERY="""
433 
select sum(quantity*price) from flipkartorders where date(created)='%s';
434 
"""
435 
WQ_QUERY="""
436 
select sum(quantity) from flipkartorders where date(created) between '%s' and '%s';
437 
"""
438 
WV_QUERY="""
439 
select sum(quantity*price) from flipkartorders where date(created) between '%s' and '%s';
440 
"""
14772 kshitij.so 441 
 
442 
date_format = xlwt.XFStyle()
443 
date_format.num_format_str = 'yyyy/mm/dd'
444 
 
445 
datetime_format = xlwt.XFStyle()
446 
datetime_format.num_format_str = 'yyyy/mm/dd HH:MM AM/PM'
447 
 
448 
default_format = xlwt.XFStyle()
17134 amit.gupta 449 
#DTR_DATA_HOST = '45.33.50.227'
450 
DTR_DATA_HOST = '192.168.161.154'
14772 kshitij.so 451 
 
452 
 
453 
def getDbConnection():
454 
    return MySQLdb.connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME)
455 
 
17217 manas 456 
def initialDate(cutOff):
457 
    return cutOff.strftime('%Y-%m-%d')
14772 kshitij.so 458 
 
17217 manas 459 
def todaysDate():
460 
    dateToday = utils.toTimeStamp(date.today())
461 
    date1 = datetime.fromtimestamp(dateToday)
462 
    return date1.strftime('%Y-%m-%d')
463 
 
14772 kshitij.so 464 
def generateDailyReport():
17128 manas 465 
    datesql= DATE_QUERY
14772 kshitij.so 466 
    dnruSql = DNRU_QUERY
467 
    dauSql = DAU_QUERY
468 
    dabSql = DAB_QUERY
469 
    dtoSql = DTO_QUERY
17396 manas 470 
    dvolSql=DVOL_QUERY
471 
    downvolSql=DOWN_QUERY
18012 manas 472 
    daccsbSql=DACCSB_QUERY
473 
    daccsoSql = DACCSO_QUERY
18021 manas 474 
    downoSql=DOWNO_QUERY
475 
    daccstoSql=DACCSTO_QUERY
14772 kshitij.so 476 
    conn = getDbConnection()
16964 manas 477 
 
14772 kshitij.so 478 
    cursor = conn.cursor()
17217 manas 479 
    cursor.execute(datesql,(cutOffDate,todaysDate()))
17128 manas 480 
    result = cursor.fetchall()
14772 kshitij.so 481 
    global workbook
482 
    workbook = xlwt.Workbook()
483 
    worksheet = workbook.add_sheet("User")
484 
    boldStyle = xlwt.XFStyle()
485 
    f = xlwt.Font()
486 
    f.bold = True
487 
    boldStyle.font = f
488 
    column = 0
489 
    row = 0
490 
    sumdata=17
15228 manas 491 
    global z
14772 kshitij.so 492 
    worksheet.write(row, 0, 'Date', boldStyle)
493 
    worksheet.write(row, 1, 'TRU', boldStyle)
494 
    worksheet.write(row, 2, 'NRU', boldStyle)
495 
    worksheet.write(row, 3, 'DAU', boldStyle)
496 
    worksheet.write(row, 4, 'DAB', boldStyle)
497 
    worksheet.write(row, 5, 'DTO', boldStyle)
16933 manas 498 
    worksheet.write(row, 6, 'DVOL', boldStyle)
15228 manas 499 
    worksheet.write(row, 7, 'DTV', boldStyle)
18021 manas 500 
    worksheet.write(row, 8, 'DOWNORD', boldStyle)
501 
    worksheet.write(row, 9, 'DOWNVOL', boldStyle)
502 
    worksheet.write(row, 10, 'DOWNVAL', boldStyle)
503 
    worksheet.write(row, 11, 'DACCSU', boldStyle)
504 
    worksheet.write(row, 12, 'DACCSB', boldStyle)
505 
    worksheet.write(row, 13, 'DACCSORD', boldStyle)
506 
    worksheet.write(row, 14, 'DACCSOVOL', boldStyle)
507 
    worksheet.write(row, 15, 'DACCSOVAL', boldStyle)
17171 amit.gupta 508 
    dateMap={}
17128 manas 509 
    for r in result:
14772 kshitij.so 510 
        row += 1
511 
        column = 0
17128 manas 512 
        for data in r :
513 
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
17171 amit.gupta 514 
            dateMap[str(data)]=row
17128 manas 515 
            column += 1
17265 manas 516 
 
517 
    cursor.execute(MTRU_QUERY,(cutOffDate))
518 
    result = cursor.fetchall()
519 
    for r in result:
520 
        sumDa=r[0]
521 
 
14772 kshitij.so 522 
    row = 0
17217 manas 523 
    cursor.execute(dnruSql,(cutOffDate,todaysDate()))
14772 kshitij.so 524 
    result = cursor.fetchall()
525 
    for r in result:
526 
        row += 1
527 
        column = 1
528 
        for data in r :
17265 manas 529 
            sumDa=sumDa+data
530 
            worksheet.write(row, column, int(sumDa) if type(sumDa) is float else sumDa, date_format if type(sumDa) is date else default_format)
14772 kshitij.so 531 
            column += 1
532 
 
533 
    row = 0
17217 manas 534 
    cursor.execute(dnruSql,(cutOffDate,todaysDate()))
14772 kshitij.so 535 
    result = cursor.fetchall()
536 
    for r in result:
537 
        row += 1
538 
        column = 2
539 
        for data in r :
540 
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
541 
            column += 1
542 
 
543 
    row = 0
17217 manas 544 
    cursor.execute(dauSql,(cutOffDate,todaysDate()))
14772 kshitij.so 545 
    result = cursor.fetchall()
546 
 
547 
    for r in result:
548 
        row += 1
549 
        column = 3
550 
        for data in r :
551 
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
552 
            column += 1
553 
 
554 
    row = 0
17217 manas 555 
    cursor.execute(dabSql,(cutOffDate,todaysDate()))
14772 kshitij.so 556 
    result = cursor.fetchall()
557 
 
558 
    for r in result:
559 
        row += 1
560 
        column = 4
561 
        for data in r :
562 
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
563 
            column += 1
564 
 
565 
    row = 0
17217 manas 566 
    cursor.execute(dtoSql,(cutOffDate,todaysDate()))
14772 kshitij.so 567 
    result = cursor.fetchall()
568 
    for r in result:
569 
        row += 1
570 
        column = 5
571 
        for data in r :
572 
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
573 
            column += 1
15228 manas 574 
 
17396 manas 575 
    row = 0
576 
    cursor.execute(dvolSql,(cutOffDate,cutOffDate))
577 
    result = cursor.fetchall()
578 
 
579 
    for r in result:
580 
        row += 1
15228 manas 581 
        column = 6
17396 manas 582 
        for data in r :
583 
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
584 
            column += 1
585 
 
586 
    row = 0
18021 manas 587 
    cursor.execute(downoSql,(cutOffDate))
17396 manas 588 
    result = cursor.fetchall()
14772 kshitij.so 589 
 
17396 manas 590 
    for r in result:
591 
        column = 8
592 
        row=dateMap.get(str(r[0]))
17171 amit.gupta 593 
        if row:
17396 manas 594 
            worksheet.write(row, column, r[1])
18021 manas 595 
 
596 
    row = 0
597 
    cursor.execute(downvolSql,(cutOffDate))
598 
    result = cursor.fetchall()
599 
 
600 
    for r in result:
601 
        column = 9
602 
        row=dateMap.get(str(r[0]))
603 
        if row:
604 
            worksheet.write(row, column, r[1])
17396 manas 605 
            column += 1
606 
            worksheet.write(row, column, r[2])
18012 manas 607 
 
608 
    row = 1
18021 manas 609 
    column=11
18012 manas 610 
    breakDate = to_java_date(datetime.now())
611 
    currentDay = cutOff*1000
612 
    while True:
613 
        currentDay = currentDay + 86400000
614 
        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')
615 
        worksheet.write(row, column, len(result))
616 
        row=row+1
617 
        if currentDay>breakDate:
618 
            break
619 
 
620 
    row = 0
621 
    cursor.execute(daccsbSql,(cutOffDate))
622 
    result = cursor.fetchall()
623 
    for r in result:
18021 manas 624 
        column = 12
18012 manas 625 
        row=dateMap.get(str(r[0]))
626 
        if row:
627 
            worksheet.write(row, column, r[1])
628 
 
629 
    row = 0
18021 manas 630 
    cursor.execute(daccstoSql,(cutOffDate))
631 
    result = cursor.fetchall()
632 
 
633 
    for r in result:
634 
        column = 13
635 
        row=dateMap.get(str(r[0]))
636 
        if row:
637 
            worksheet.write(row, column, r[1])
638 
 
639 
    row = 0
18012 manas 640 
    cursor.execute(daccsoSql,(cutOffDate))
641 
    result = cursor.fetchall()
17396 manas 642 
 
18012 manas 643 
    for r in result:
18021 manas 644 
        column = 14
18012 manas 645 
        row=dateMap.get(str(r[0]))
646 
        if row:
647 
            worksheet.write(row, column, r[1])
648 
            column += 1
649 
            worksheet.write(row, column, r[2])
16964 manas 650 
 
14772 kshitij.so 651 
def generateMonthlyReport():
652 
    monthSql = MONTH_QUERY
653 
    mnruSql = MNRU_QUERY
654 
    mauSql = MAU_QUERY
655 
    mabSql = MAB_QUERY
656 
    mtoSql = MTO_QUERY
17396 manas 657 
    mvolSql=MVOL_QUERY
658 
    mownvolSql=MOWN_QUERY
18012 manas 659 
    maccsbSql= MACCSB_QUERY
18021 manas 660 
    maccsoSql= MACCSO_QUERY
661 
    mownoSql= MOWNO_QUERY
662 
    maccstoSql= MACCSTO_QUERY
18012 manas 663 
 
14772 kshitij.so 664 
    conn = getDbConnection()
665 
 
666 
    cursor = conn.cursor()
667 
 
17217 manas 668 
    cursor.execute(monthSql,(cutOffDate,todaysDate()))
14772 kshitij.so 669 
    result = cursor.fetchall()
670 
    rb = open_workbook(TMP_FILE)
671 
    wb = copy(rb)
672 
    worksheet = workbook.add_sheet("Monthly")
673 
    boldStyle = xlwt.XFStyle()
674 
    f = xlwt.Font()
675 
    f.bold = True
676 
    boldStyle.font = f
677 
    column = 0
678 
    row = 0
679 
    worksheet.write(row, 0, 'Month', boldStyle)
680 
    worksheet.write(row, 1, 'MTRU', boldStyle)
681 
    worksheet.write(row, 2, 'MNRU', boldStyle)
682 
    worksheet.write(row, 3, 'MAU', boldStyle)
683 
    worksheet.write(row, 4, 'MAB', boldStyle)
684 
    worksheet.write(row, 5, 'MTO', boldStyle)
16933 manas 685 
    worksheet.write(row, 6, 'MVOL', boldStyle)
15551 manas 686 
    worksheet.write(row, 7, 'MTV', boldStyle)
18021 manas 687 
    worksheet.write(row, 8, 'MOWNORD', boldStyle)
688 
    worksheet.write(row, 9, 'MOWNVOL', boldStyle)
689 
    worksheet.write(row, 10, 'MOWNVAL', boldStyle)
690 
    worksheet.write(row, 11, 'MACCSU', boldStyle)
691 
    worksheet.write(row, 12, 'MACCSB', boldStyle)
692 
    worksheet.write(row, 13, 'MACCSORD', boldStyle)
693 
    worksheet.write(row, 14, 'MACCSOVOL', boldStyle)
694 
    worksheet.write(row, 15, 'MACCSOVAL', boldStyle)
18012 manas 695 
 
696 
    monthMapDb = {}
14772 kshitij.so 697 
    for r in result:
698 
        row += 1
699 
        column = 0
700 
        for data in r :
701 
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
18012 manas 702 
            monthMapDb[str(data)]=row
14772 kshitij.so 703 
            column += 1
704 
 
17217 manas 705 
    cursor.execute(MTRU_QUERY,(cutOffDate))
706 
    result = cursor.fetchall()
707 
    for r in result:
708 
        sumDa=r[0]
709 
 
14772 kshitij.so 710 
    row = 0
17217 manas 711 
    cursor.execute(mnruSql,(cutOffDate,todaysDate()))
14772 kshitij.so 712 
    result = cursor.fetchall()
713 
    for r in result:
714 
        row += 1
715 
        column = 1
716 
        for data in r :
17217 manas 717 
            sumDa=sumDa+data
718 
            worksheet.write(row, column, int(sumDa) if type(sumDa) is float else sumDa, date_format if type(sumDa) is date else default_format)
14772 kshitij.so 719 
            column += 1
720 
 
721 
    row = 0
17217 manas 722 
    cursor.execute(mnruSql,(cutOffDate,todaysDate()))
14772 kshitij.so 723 
    result = cursor.fetchall()
724 
    for r in result:
725 
        row += 1
726 
        column = 2
727 
        for data in r :
728 
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
729 
            column += 1
730 
 
731 
    row = 0
17217 manas 732 
    cursor.execute(mauSql,(cutOffDate,todaysDate()))
14772 kshitij.so 733 
    result = cursor.fetchall()
734 
 
735 
    for r in result:
736 
        row += 1
737 
        column = 3
738 
        for data in r :
739 
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
740 
            column += 1
741 
 
742 
    row = 0
17217 manas 743 
    cursor.execute(mabSql,(cutOffDate,todaysDate()))
14772 kshitij.so 744 
    result = cursor.fetchall()
745 
 
746 
    for r in result:
747 
        row += 1
748 
        column = 4
749 
        for data in r :
750 
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
751 
            column += 1
752 
 
753 
    row = 0
17217 manas 754 
    cursor.execute(mtoSql,(cutOffDate,todaysDate()))
14772 kshitij.so 755 
    result = cursor.fetchall()
756 
 
757 
    for r in result:
758 
        row += 1
759 
        column = 5
760 
        for data in r :
761 
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
762 
            column += 1
15551 manas 763 
 
764 
    row=0
17396 manas 765 
    cursor.execute(mvolSql,(cutOffDate,cutOffDate))
766 
    result = cursor.fetchall()
767 
 
768 
    for r in result:
15551 manas 769 
        row += 1
770 
        column = 6
17396 manas 771 
        for data in r :
772 
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
773 
            column += 1
18021 manas 774 
 
17396 manas 775 
    row = 0
18021 manas 776 
    cursor.execute(mownoSql,(cutOffDate))
777 
    result = cursor.fetchall()
778 
    for r in result:
779 
        column = 8
780 
        row=monthMapDb.get(str(r[0]))
781 
        if row:
782 
            worksheet.write(row, column, r[1])
783 
 
784 
    row = 0
17396 manas 785 
    cursor.execute(mownvolSql,(cutOffDate))
786 
    result = cursor.fetchall()
787 
 
788 
    for r in result:
15551 manas 789 
        row += 1
18021 manas 790 
        column = 9
17396 manas 791 
        for data in r :
792 
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
793 
            column += 1
18012 manas 794 
 
795 
    row=1
18021 manas 796 
    column=11
18012 manas 797 
    breakDate = to_java_date(datetime.now())
798 
    currentDay = cutOff*1000
799 
    while True:
800 
        currentDay = currentDay + (monthMap.get(month_get(currentDay))*86400000)
801 
        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')
802 
        currentDay = currentDay+ 86400000
803 
        worksheet.write(row,column,len(result))
804 
        row=row+1
805 
        if currentDay>breakDate:
806 
            break
807 
 
808 
 
809 
    row = 0
810 
    cursor.execute(maccsbSql,(cutOffDate))
811 
    result = cursor.fetchall()
812 
    for r in result:
18021 manas 813 
        column = 12
18012 manas 814 
        row=monthMapDb.get(str(r[0]))
815 
        if row:
816 
            worksheet.write(row, column, r[1])
18021 manas 817 
 
818 
    row = 0
819 
    cursor.execute(maccstoSql,(cutOffDate))
820 
    result = cursor.fetchall()
821 
 
822 
    for r in result:
823 
        column = 13
824 
        row=monthMapDb.get(str(r[0]))
825 
        if row:
826 
            worksheet.write(row, column, r[1])
18012 manas 827 
 
828 
    row = 0
829 
    cursor.execute(maccsoSql,(cutOffDate))
830 
    result = cursor.fetchall()
831 
 
832 
    for r in result:
18021 manas 833 
        column = 14
18012 manas 834 
        row=monthMapDb.get(str(r[0]))
835 
        if row:
836 
            worksheet.write(row, column, r[1])
837 
            column += 1
838 
            worksheet.write(row, column, r[2])
839 
 
14772 kshitij.so 840 
    workbook.save(TMP_FILE)
17031 manas 841 
 
14772 kshitij.so 842 
def generateWeeklyReport():
843 
    weekSql = WEEK_QUERY
844 
    wnruSql = WNRU_QUERY
845 
    wauSql = WAU_QUERY
846 
    wabSql = WAB_QUERY
847 
    wtoSql = WTO_QUERY
17265 manas 848 
    wownSql=WOWN_QUERY
849 
    wvolSql=WVOL_QUERY
18012 manas 850 
    waccsbSql = WACCSB_QUERY
851 
    waccsoSql = WACCSO_QUERY
18021 manas 852 
    wownoSql=WOWNO_QUERY
853 
    waccstoSql=WACCSTO_QUERY
854 
 
14772 kshitij.so 855 
    conn = getDbConnection()
856 
 
857 
    cursor = conn.cursor()
858 
 
17217 manas 859 
    cursor.execute(weekSql,(last_day(cutOffDate, 'sunday')))
14772 kshitij.so 860 
    result = cursor.fetchall()
861 
    rb = open_workbook(TMP_FILE)
862 
    wb = copy(rb)
863 
    worksheet = workbook.add_sheet("Weekly")
864 
    boldStyle = xlwt.XFStyle()
865 
    f = xlwt.Font()
866 
    f.bold = True
867 
    boldStyle.font = f
868 
    column = 0
869 
    row = 0
870 
    worksheet.write(row, 0, 'Week', boldStyle)
871 
    worksheet.write(row, 1, 'WTRU', boldStyle)
872 
    worksheet.write(row, 2, 'WNRU', boldStyle)
873 
    worksheet.write(row, 3, 'WAU', boldStyle)
874 
    worksheet.write(row, 4, 'WAB', boldStyle)
875 
    worksheet.write(row, 5, 'WTO', boldStyle)
16933 manas 876 
    worksheet.write(row, 6, 'WVOL', boldStyle)
15228 manas 877 
    worksheet.write(row, 7, 'WTV', boldStyle)
18021 manas 878 
    worksheet.write(row, 8, 'WOWNORD', boldStyle)
879 
    worksheet.write(row, 9, 'WOWNVOL', boldStyle)
880 
    worksheet.write(row, 10, 'WOWNVAL', boldStyle)
881 
    worksheet.write(row, 11, 'WACCSU', boldStyle)
882 
    worksheet.write(row, 12, 'WACCSB', boldStyle)
883 
    worksheet.write(row, 13, 'WACCSORD', boldStyle)
884 
    worksheet.write(row, 14, 'WACCSVOL', boldStyle)
885 
    worksheet.write(row, 15, 'WACCSVAL', boldStyle)
886 
 
18012 manas 887 
    weekMap={}
18021 manas 888 
 
14772 kshitij.so 889 
    for r in result:
890 
        row += 1
891 
        column = 0
18012 manas 892 
        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)
893 
        weekMap[str(r[1])]=row
894 
        column += 1
14772 kshitij.so 895 
 
17265 manas 896 
    cursor.execute(MTRU_QUERY,(cutOffDate))
897 
    result = cursor.fetchall()
898 
    for r in result:
899 
        sumDa=r[0]
900 
 
14772 kshitij.so 901 
    row = 0
17265 manas 902 
    cursor.execute(wnruSql,(cutOffDate))
14772 kshitij.so 903 
    result = cursor.fetchall()
904 
    for r in result:
905 
        row += 1
906 
        column = 1
907 
        for data in r :
17265 manas 908 
            sumDa=sumDa+data
909 
            worksheet.write(row, column, int(sumDa) if type(sumDa) is float else sumDa, date_format if type(sumDa) is date else default_format)
14772 kshitij.so 910 
            column += 1
911 
 
912 
    row = 0
17265 manas 913 
    cursor.execute(wnruSql,(cutOffDate))
14772 kshitij.so 914 
    result = cursor.fetchall()
915 
    for r in result:
916 
        row += 1
917 
        column = 2
918 
        for data in r :
919 
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
920 
            column += 1
921 
 
922 
    row = 0
17265 manas 923 
    cursor.execute(wauSql,(cutOffDate))
14772 kshitij.so 924 
    result = cursor.fetchall()
925 
 
926 
    for r in result:
927 
        row += 1
928 
        column = 3
929 
        for data in r :
930 
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
931 
            column += 1
932 
 
933 
    row = 0
17265 manas 934 
    cursor.execute(wabSql,(cutOffDate))
14772 kshitij.so 935 
    result = cursor.fetchall()
936 
 
937 
    for r in result:
938 
        row += 1
939 
        column = 4
940 
        for data in r :
941 
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
942 
            column += 1
943 
 
944 
    row = 0
17265 manas 945 
    cursor.execute(wtoSql,(cutOffDate))
14772 kshitij.so 946 
    result = cursor.fetchall()
947 
 
948 
    for r in result:
949 
        row += 1
950 
        column = 5
951 
        for data in r :
952 
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
953 
            column += 1
17265 manas 954 
 
955 
    row = 0
17269 manas 956 
    cursor.execute(wvolSql,(cutOffDate,cutOffDate))
17265 manas 957 
    result = cursor.fetchall()
958 
 
959 
    for r in result:
15228 manas 960 
        row += 1
961 
        column = 6
17265 manas 962 
        for data in r :
963 
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
964 
            column += 1
18021 manas 965 
 
966 
    row = 0
967 
    cursor.execute(wownoSql,(cutOffDate))
968 
    result = cursor.fetchall()
969 
    for r in result:
970 
        column = 8
971 
        row=weekMap.get(str(r[0]))
972 
        if row:
973 
            worksheet.write(row, column, r[1])
17265 manas 974 
 
975 
    row = 0
976 
    cursor.execute(wownSql,(cutOffDate))
977 
    result = cursor.fetchall()
16964 manas 978 
 
17265 manas 979 
    for r in result:
16964 manas 980 
        row += 1
18021 manas 981 
        column = 9
17265 manas 982 
        for data in r :
983 
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
984 
            column += 1
18012 manas 985 
 
986 
    row=1
18021 manas 987 
    column=11
18012 manas 988 
    breakDate = to_java_date(datetime.now())
989 
    currentDay = to_java_date(last_day(cutOffDate, 'sunday'))
990 
    while True:
991 
        currentDay = currentDay + (7*86400000)
992 
        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')
993 
        worksheet.write(row,column,len(result))
994 
        row=row+1
995 
        if currentDay>breakDate:
996 
            break
18021 manas 997 
 
18012 manas 998 
    row = 0
999 
    cursor.execute(waccsbSql,(cutOffDate))
1000 
    result = cursor.fetchall()
1001 
    for r in result:
18021 manas 1002 
        column = 12
18012 manas 1003 
        row=weekMap.get(str(r[0]))
1004 
        if row:
1005 
            worksheet.write(row, column, r[1])
18021 manas 1006 
 
18012 manas 1007 
    row = 0
18021 manas 1008 
    cursor.execute(waccstoSql,(cutOffDate))
1009 
    result = cursor.fetchall()
1010 
 
1011 
    for r in result:
1012 
        column = 13
1013 
        row=weekMap.get(str(r[0]))
1014 
        if row:
1015 
            worksheet.write(row, column, r[1])
1016 
 
1017 
    row = 0
18012 manas 1018 
    cursor.execute(waccsoSql,(cutOffDate))
1019 
    result = cursor.fetchall()
1020 
 
1021 
    for r in result:
18021 manas 1022 
        column = 14
18012 manas 1023 
        row=weekMap.get(str(r[0]))
1024 
        if row:
1025 
            worksheet.write(row, column, r[1])
1026 
            column += 1
1027 
            worksheet.write(row, column, r[2])
17265 manas 1028 
 
16964 manas 1029 
 
14772 kshitij.so 1030 
def sendmail(email, message, fileName, title):
1031 
    if email == "":
1032 
        return
1033 
    mailServer = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)
1034 
    mailServer.ehlo()
1035 
    mailServer.starttls()
1036 
    mailServer.ehlo()
1037 
 
1038 
    # Create the container (outer) email message.
1039 
    msg = MIMEMultipart()
1040 
    msg['Subject'] = title
1041 
    msg.preamble = title
1042 
    html_msg = MIMEText(message, 'html')
1043 
    msg.attach(html_msg)
1044 
 
1045 
    fileMsg = MIMEBase('application', 'vnd.ms-excel')
1046 
    fileMsg.set_payload(file(TMP_FILE).read())
1047 
    encoders.encode_base64(fileMsg)
1048 
    fileMsg.add_header('Content-Disposition', 'attachment;filename=' + fileName)
1049 
    msg.attach(fileMsg)
14805 kshitij.so 1050 
 
18154 manas 1051 
    #MAILTO = ['manas.kapoor@saholic.com','rajneesh.arora@saholic.com', 'amit.gupta@saholic.com','chaitnaya.vats@shop2020.in']
1052 
    MAILTO = ['manas.kapoor@saholic.com']
14772 kshitij.so 1053 
    mailServer.login(SENDER, PASSWORD)
17443 manish.sha 1054 
    mailServer.sendmail(SENDER, MAILTO, msg.as_string())
14772 kshitij.so 1055 
 
15228 manas 1056 
class __Order:
1057 
 
1058 
    def __init__(self, count, value):
1059 
 
1060 
        self.count = count
1061 
        self.value = value
1062 
 
1063 
def get_mongo_connection(host='localhost', port=27017):
1064 
    global con
1065 
    if con is None:
1066 
        print "Establishing connection %s host and port %d" %(host,port)
1067 
        try:
1068 
            con = pymongo.MongoClient(host, port)
1069 
        except Exception, e:
1070 
            print e
1071 
            return None
1072 
    return con
1073 
 
1074 
def populateOrderMap():
1075 
    global dateWiseOrderMap
17171 amit.gupta 1076 
    allOrders = get_mongo_connection().Dtr.merchantOrder.find({'createdOnInt':{"$gte":cutOff},'storeId':{"$in":[1,3,4,5,6]}}, {"orderId":1,"createdOnInt":1,"subOrders":1}).sort([('createdOnInt',pymongo.ASCENDING)])
15228 manas 1077 
    for orders in allOrders:
1078 
        if orders.get('orderId') not in order_ids:
1079 
            continue
1080 
        cdate = ((to_py_date(orders.get('createdOnInt') * 1000)))
1081 
        millisec = to_java_date(datetime(cdate.year, cdate.month, cdate.day))
1082 
        if dateWiseOrderMap.has_key(millisec):
1083 
            orderObj = dateWiseOrderMap.get(millisec)
1084 
            q, c = getSubOrderQuantity(orders.get('subOrders'))
1085 
            orderObj.count += q
1086 
            orderObj.value += c
1087 
        else:
1088 
            orderObj = __Order(None, None)
1089 
            q, c = getSubOrderQuantity(orders.get('subOrders'))
1090 
            orderObj.count = q
1091 
            orderObj.value = c
1092 
            dateWiseOrderMap[millisec] = orderObj
1093 
 
15551 manas 1094 
 
1095 
 
15228 manas 1096 
def getSubOrderQuantity(subOrders):
1097 
    q = 0
1098 
    c = 0
1099 
    if subOrders is None:
1100 
        return q, c
1101 
    for subOrder in subOrders:
1102 
        q = q + int(subOrder.get('quantity'))
1103 
        try:
1104 
            c = c + float(subOrder.get('amountPaid').encode('utf-8'))
1105 
        except AttributeError:
1106 
            c = c + float(subOrder.get('amountPaid'))
1107 
    return q, c
1108 
 
1109 
def populateWeekWiseMap():
1110 
    global weekCutOff
1111 
    while(True):
1112 
        quantity, amount = 0 , 0
1113 
        for i in xrange(weekCutOff , (weekCutOff * 7)+1):
1114 
            orderObj = dateWiseOrderMap.get(i*1000)
1115 
            quantity += orderObj.count
1116 
            amount += orderObj.value
1117 
            i = i + oneDay
1118 
        weekWiseOrderMap[weekCutOff * 1000] = __Order(quantity, amount)
1119 
        weekCutOff = weekCutOff * 1000 * 7 + oneDay
1120 
        if weekCutOff >= to_py_date(datetime.now()):
1121 
            break
1122 
 
1123 
def populateWeekWiseMap1():
1124 
    global weekCutOff
1125 
    while(True):
1126 
        if weekCutOff *1000 >= to_java_date(datetime.now()):
1127 
            break
1128 
        init = weekCutOff
1129 
        breakPoint = weekCutOff + (6 * oneDay)
1130 
        quantity, amount = 0 , 0
1131 
        while(True):
1132 
            orderObj = dateWiseOrderMap.get(weekCutOff * 1000)
1133 
            if orderObj is not None:
1134 
                quantity += orderObj.count
1135 
                amount += orderObj.value
1136 
            weekCutOff = weekCutOff + oneDay
1137 
            if weekCutOff > breakPoint:
1138 
                weekWiseOrderMap[init * 1000] = __Order(quantity, amount)
1139 
                break
1140 
 
1141 
def populateMonthWiseMap():
1142 
    global monthCutOff
1143 
    while(True):
1144 
        quantity, amount = 0 , 0
1145 
        for i in xrange(monthCutOff , (monthCutOff * 30)+1):
1146 
            orderObj = dateWiseOrderMap.get(i*1000)
1147 
            quantity += orderObj.count
1148 
            amount += orderObj.value
1149 
            i = i + oneDay
1150 
        weekWiseOrderMap[monthCutOff * 1000] = __Order(quantity, amount)
1151 
        monthCutOff = monthCutOff * 1000 * 30 + oneDay
1152 
        if monthCutOff >= to_py_date(datetime.now()):
1153 
            break
1154 
 
1155 
def populateMonthWiseMap1():
15551 manas 1156 
    global monthCutOff
15228 manas 1157 
    while(True):
15551 manas 1158 
 
1159 
        print monthCutOff *1000
1160 
        a=str(to_py_date(monthCutOff*1000))
1161 
        print 'Now ' + str(to_java_date(datetime.now()))
15228 manas 1162 
        print "**********************"
15551 manas 1163 
        if monthCutOff *1000 >= to_java_date(datetime.now()):
15228 manas 1164 
            print "Breaking outer while"
1165 
            break
15551 manas 1166 
        init = monthCutOff
1167 
        breakPoint = monthCutOff + (monthMap.get(a[5:7]) * oneDay)
1168 
        print breakPoint
15228 manas 1169 
        quantity, amount = 0 , 0
1170 
        while(True):
15551 manas 1171 
            print to_py_date(monthCutOff*1000)
1172 
            print "weekCutOff ",monthCutOff
15228 manas 1173 
            print "breakPoint ",breakPoint
15551 manas 1174 
            orderObj = dateWiseOrderMap.get(monthCutOff * 1000)
15228 manas 1175 
            if orderObj is None:
15551 manas 1176 
                print "None for ", to_py_date(monthCutOff * 1000)
15228 manas 1177 
            if orderObj is not None:
1178 
                quantity += orderObj.count
1179 
                amount += orderObj.value
15551 manas 1180 
            monthCutOff = monthCutOff + oneDay
1181 
            counter=0
1182 
            print counter+1
1183 
            if monthCutOff > breakPoint:
1184 
                monthWiseOrderMap[init * 1000] = __Order(quantity, amount)
15228 manas 1185 
                print "Breaking inner while"
1186 
                break
16964 manas 1187 
 
1188 
def populateSaholicOrderMap():
1189 
    global dateWiseSaholicOrderMap
1190 
    allOrders = get_mongo_connection().Dtr.merchantOrder.find({'createdOnInt':{"$gte":cutOff},'storeId':{"$in":[4]}}).sort([('createdOnInt',pymongo.ASCENDING)])
1191 
    for orders in allOrders:
1192 
        if orders.get('orderId') not in order_ids:
1193 
            continue
1194 
        cdate = ((to_py_date(orders.get('createdOnInt') * 1000)))
1195 
        millisec = to_java_date(datetime(cdate.year, cdate.month, cdate.day))
1196 
        if dateWiseSaholicOrderMap.has_key(millisec):
1197 
            orderObj = dateWiseSaholicOrderMap.get(millisec)
1198 
            q, c = getSaholicSubOrderQuantity(orders.get('subOrders'))
1199 
            orderObj.count += q
1200 
            orderObj.value += c
1201 
        else:
1202 
            orderObj = __Order(None, None)
1203 
            q, c = getSaholicSubOrderQuantity(orders.get('subOrders'))
1204 
            orderObj.count = q
1205 
            orderObj.value = c
1206 
            dateWiseSaholicOrderMap[millisec] = orderObj
15228 manas 1207 
 
16964 manas 1208 
 
1209 
 
1210 
def getSaholicSubOrderQuantity(subOrders):
1211 
    q = 0
1212 
    c = 0
1213 
    if subOrders is None:
1214 
        return q, c
1215 
    for subOrder in subOrders:
1216 
        q = q + int(subOrder.get('quantity'))
1217 
        try:
1218 
            c = c + float(subOrder.get('amountPaid').encode('utf-8'))
1219 
        except AttributeError:
1220 
            c = c + float(subOrder.get('amountPaid'))
1221 
    return q, c
1222 
 
1223 
def populateSaholicWeekWiseMap1():
1224 
    global newWeekCutOff
1225 
    while(True):
1226 
        if newWeekCutOff *1000 >= to_java_date(datetime.now()):
1227 
            break
1228 
        init = newWeekCutOff
1229 
        breakPoint = newWeekCutOff + (6 * oneDay)
1230 
        quantity, amount = 0 , 0
1231 
        while(True):
1232 
            orderObj = dateWiseSaholicOrderMap.get(newWeekCutOff * 1000)
1233 
            if orderObj is not None:
1234 
                quantity += orderObj.count
1235 
                amount += orderObj.value
1236 
            else:
1237 
                quantity +=0
1238 
                amount +=0
1239 
            newWeekCutOff = newWeekCutOff + oneDay
1240 
            if newWeekCutOff > breakPoint:
1241 
                weekWiseSaholicOrderMap[init * 1000] = __Order(quantity, amount)
1242 
                break
17134 amit.gupta 1243 
def populateYesterdayActiveUsers(hours=4):
1244 
    con = Mongo.get_mongo_connection(DTR_DATA_HOST)
1245 
    db = con.User
1246 
    curtime = datetime.now()
1247 
    fourhrsprev = curtime - timedelta(hours=hours)
1248 
    pipeline = [{"$match":{"created":{"$lt":toTimeStamp(curtime)*1000, "$gt":toTimeStamp(fourhrsprev)*1000}}},
1249 
                {"$group":{"_id":{"user_id":"$user_id","created":{"$subtract":["$created",{"$mod":["$created",86400000]}]}}}}]
1250 
    alluserdates=db.browsinghistories.aggregate(pipeline)['result']
1251 
    insertq = "insert ignore into daily_visitors values (%s, %s)"
1252 
    conn = getDbConnection()
1253 
    try:
1254 
        for a in alluserdates:
1255 
            cursor = conn.cursor()
1256 
            # Execute the SQL command
1257 
            # Fetch source id.
1258 
            cursor.execute(insertq,(a['_id']['user_id'], utils.fromTimeStamp(int(a['_id']['created']/1000))))
1259 
            conn.commit()
1260 
    finally:
1261 
        conn.close()
16964 manas 1262 
 
1263 
def populateSaholicMonthWiseMap1():
1264 
    global newMonthCutOff
1265 
    while(True):
1266 
 
1267 
        print newMonthCutOff *1000
1268 
        a=str(to_py_date(newMonthCutOff*1000))
1269 
        print 'Now ' + str(to_java_date(datetime.now()))
1270 
        print "**********************"
1271 
        if newMonthCutOff *1000 >= to_java_date(datetime.now()):
1272 
            print "Breaking outer while"
1273 
            break
1274 
        init = newMonthCutOff
1275 
        breakPoint = newMonthCutOff + (monthMap.get(a[5:7]) * oneDay)
1276 
        print breakPoint
1277 
        quantity, amount = 0 , 0
1278 
        while(True):
1279 
            print to_py_date(newMonthCutOff*1000)
1280 
            print "weekCutOff ",newMonthCutOff
1281 
            print "breakPoint ",breakPoint
1282 
            orderObj = dateWiseSaholicOrderMap.get(newMonthCutOff * 1000)
1283 
            if orderObj is None:
1284 
                print "None for ", to_py_date(newMonthCutOff * 1000)
1285 
                quantity += 0
1286 
                amount += 0
1287 
            if orderObj is not None:
1288 
                quantity += orderObj.count
1289 
                amount += orderObj.value
1290 
            newMonthCutOff = newMonthCutOff + oneDay
1291 
            counter=0
1292 
            print counter+1
1293 
            if newMonthCutOff > breakPoint:
1294 
                monthWiseSaholicOrderMap[init * 1000] = __Order(quantity, amount)
1295 
                print "Breaking inner while"
1296 
                break
1297 
 
1298 
 
15228 manas 1299 
def populateValidOrders():
1300 
    global order_ids
17217 manas 1301 
    allOrders = session.query(Orders.id).join((Users,Orders.user_id==Users.id)).filter(or_(~(func.lower(Users.referrer)).like('emp%'),Users.utm_campaign != None)).filter(and_(Orders.created>=cutOffDate)).all()
15228 manas 1302 
    order_ids = list(zip(*allOrders)[0])
17217 manas 1303 
 
1304 
def last_day(d, day_name):
1305 
    days_of_week = ['sunday','monday','tuesday','wednesday',
1306 
                        'thursday','friday','saturday']
1307 
    target_day = days_of_week.index(day_name.lower())
1308 
    delta_day = target_day - (d.isoweekday()%7)
1309 
    return d + timedelta(days=delta_day)
18012 manas 1310 
 
1311 
def get_mongo_connection_new(host='45.33.50.227', port=27017):
1312 
    global con
1313 
    if con is None:
1314 
        print "Establishing connection %s host and port %d" %(host,port)
1315 
        try:
1316 
            con = pymongo.MongoClient(host, port)
1317 
        except Exception, e:
1318 
            print e
1319 
            return None
1320 
    return con
1321 
 
1322 
 
14772 kshitij.so 1323 
def main():
18154 manas 1324 
    #populateYesterdayActiveUsers(24)
17396 manas 1325 
    #populateValidOrders()
1326 
    #populateOrderMap()
17269 manas 1327 
    #populateWeekWiseMap1()
17396 manas 1328 
    #populateMonthWiseMap1()
1329 
    #populateSaholicOrderMap()
17269 manas 1330 
    #populateSaholicWeekWiseMap1()
17396 manas 1331 
    #populateSaholicMonthWiseMap1()
14772 kshitij.so 1332 
    generateDailyReport()
1333 
    generateWeeklyReport()
1334 
    generateMonthlyReport()
18154 manas 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)
15228 manas 1337 
 
17269 manas 1338 
 
16964 manas 1339 
def to_x_date(java_timestamp):
1340 
    try:
1341 
        date = datetime.fromtimestamp(java_timestamp / 1e3)
1342 
    except:
1343 
        return None
1344 
    return date.strftime('%Y-%m-%d')
15551 manas 1345 
 
18012 manas 1346 
def month_get(java_timestamp):
1347 
    try:
1348 
        date = datetime.fromtimestamp(java_timestamp / 1e3)
1349 
    except:
1350 
        return None
1351 
    return date.strftime('%m')
1352 
 
14772 kshitij.so 1353 
if __name__ == '__main__':
17158 amit.gupta 1354 
    if len(sys.argv)==1:
17134 amit.gupta 1355 
        main()
1356 
    else:
17135 amit.gupta 1357 
        populateYesterdayActiveUsers(int(sys.argv[1]))