Subversion Repositories SmartDukaan

Rev

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