Subversion Repositories SmartDukaan

Rev

Rev 18201 | Rev 18468 | 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="""
18309 manas 106 
SELECT date(u.activation_time),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:
18309 manas 529 
        row=dateMap.get(str(r[0]))
530 
        sumDa=sumDa+r[1]
14772 kshitij.so 531 
        column = 1
18309 manas 532 
        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 533 
 
534 
    row = 0
17217 manas 535 
    cursor.execute(dnruSql,(cutOffDate,todaysDate()))
14772 kshitij.so 536 
    result = cursor.fetchall()
537 
    for r in result:
18309 manas 538 
        row=dateMap.get(str(r[0]))
539 
        data=r[1]
14772 kshitij.so 540 
        column = 2
18309 manas 541 
        worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
14772 kshitij.so 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 
 
18201 manas 620 
    row = 1
621 
    column=12
622 
    breakDate = to_java_date(datetime.now())
623 
    currentDay = cutOff*1000
624 
    while True:
625 
        currentDay = currentDay + 86400000
626 
        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')
627 
        worksheet.write(row, column, len(result))
628 
        row=row+1
629 
        if currentDay>breakDate:
630 
            break
631 
 
18012 manas 632 
    row = 0
633 
    cursor.execute(daccsbSql,(cutOffDate))
634 
    result = cursor.fetchall()
635 
    for r in result:
18201 manas 636 
        column = 13
18012 manas 637 
        row=dateMap.get(str(r[0]))
638 
        if row:
639 
            worksheet.write(row, column, r[1])
640 
 
641 
    row = 0
18021 manas 642 
    cursor.execute(daccstoSql,(cutOffDate))
643 
    result = cursor.fetchall()
644 
 
645 
    for r in result:
18201 manas 646 
        column = 14
18021 manas 647 
        row=dateMap.get(str(r[0]))
648 
        if row:
649 
            worksheet.write(row, column, r[1])
650 
 
651 
    row = 0
18012 manas 652 
    cursor.execute(daccsoSql,(cutOffDate))
653 
    result = cursor.fetchall()
17396 manas 654 
 
18012 manas 655 
    for r in result:
18201 manas 656 
        column = 15
18012 manas 657 
        row=dateMap.get(str(r[0]))
658 
        if row:
659 
            worksheet.write(row, column, r[1])
660 
            column += 1
661 
            worksheet.write(row, column, r[2])
16964 manas 662 
 
14772 kshitij.so 663 
def generateMonthlyReport():
664 
    monthSql = MONTH_QUERY
665 
    mnruSql = MNRU_QUERY
666 
    mauSql = MAU_QUERY
667 
    mabSql = MAB_QUERY
668 
    mtoSql = MTO_QUERY
17396 manas 669 
    mvolSql=MVOL_QUERY
670 
    mownvolSql=MOWN_QUERY
18012 manas 671 
    maccsbSql= MACCSB_QUERY
18021 manas 672 
    maccsoSql= MACCSO_QUERY
673 
    mownoSql= MOWNO_QUERY
674 
    maccstoSql= MACCSTO_QUERY
18012 manas 675 
 
14772 kshitij.so 676 
    conn = getDbConnection()
677 
 
678 
    cursor = conn.cursor()
679 
 
17217 manas 680 
    cursor.execute(monthSql,(cutOffDate,todaysDate()))
14772 kshitij.so 681 
    result = cursor.fetchall()
682 
    rb = open_workbook(TMP_FILE)
683 
    wb = copy(rb)
684 
    worksheet = workbook.add_sheet("Monthly")
685 
    boldStyle = xlwt.XFStyle()
686 
    f = xlwt.Font()
687 
    f.bold = True
688 
    boldStyle.font = f
689 
    column = 0
690 
    row = 0
691 
    worksheet.write(row, 0, 'Month', boldStyle)
692 
    worksheet.write(row, 1, 'MTRU', boldStyle)
693 
    worksheet.write(row, 2, 'MNRU', boldStyle)
694 
    worksheet.write(row, 3, 'MAU', boldStyle)
695 
    worksheet.write(row, 4, 'MAB', boldStyle)
696 
    worksheet.write(row, 5, 'MTO', boldStyle)
16933 manas 697 
    worksheet.write(row, 6, 'MVOL', boldStyle)
15551 manas 698 
    worksheet.write(row, 7, 'MTV', boldStyle)
18021 manas 699 
    worksheet.write(row, 8, 'MOWNORD', boldStyle)
700 
    worksheet.write(row, 9, 'MOWNVOL', boldStyle)
701 
    worksheet.write(row, 10, 'MOWNVAL', boldStyle)
702 
    worksheet.write(row, 11, 'MACCSU', boldStyle)
18201 manas 703 
    worksheet.write(row, 12, 'MACCSCART', boldStyle)
704 
    worksheet.write(row, 13, 'MACCSB', boldStyle)
705 
    worksheet.write(row, 14, 'MACCSORD', boldStyle)
706 
    worksheet.write(row, 15, 'MACCSOVOL', boldStyle)
707 
    worksheet.write(row, 16, 'MACCSOVAL', boldStyle)
18012 manas 708 
 
709 
    monthMapDb = {}
14772 kshitij.so 710 
    for r in result:
711 
        row += 1
712 
        column = 0
713 
        for data in r :
714 
            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 715 
            monthMapDb[str(data)]=row
14772 kshitij.so 716 
            column += 1
717 
 
17217 manas 718 
    cursor.execute(MTRU_QUERY,(cutOffDate))
719 
    result = cursor.fetchall()
720 
    for r in result:
721 
        sumDa=r[0]
722 
 
14772 kshitij.so 723 
    row = 0
17217 manas 724 
    cursor.execute(mnruSql,(cutOffDate,todaysDate()))
14772 kshitij.so 725 
    result = cursor.fetchall()
726 
    for r in result:
727 
        row += 1
728 
        column = 1
729 
        for data in r :
17217 manas 730 
            sumDa=sumDa+data
731 
            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 732 
            column += 1
733 
 
734 
    row = 0
17217 manas 735 
    cursor.execute(mnruSql,(cutOffDate,todaysDate()))
14772 kshitij.so 736 
    result = cursor.fetchall()
737 
    for r in result:
738 
        row += 1
739 
        column = 2
740 
        for data in r :
741 
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
742 
            column += 1
743 
 
744 
    row = 0
17217 manas 745 
    cursor.execute(mauSql,(cutOffDate,todaysDate()))
14772 kshitij.so 746 
    result = cursor.fetchall()
747 
 
748 
    for r in result:
749 
        row += 1
750 
        column = 3
751 
        for data in r :
752 
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
753 
            column += 1
754 
 
755 
    row = 0
17217 manas 756 
    cursor.execute(mabSql,(cutOffDate,todaysDate()))
14772 kshitij.so 757 
    result = cursor.fetchall()
758 
 
759 
    for r in result:
760 
        row += 1
761 
        column = 4
762 
        for data in r :
763 
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
764 
            column += 1
765 
 
766 
    row = 0
17217 manas 767 
    cursor.execute(mtoSql,(cutOffDate,todaysDate()))
14772 kshitij.so 768 
    result = cursor.fetchall()
769 
 
770 
    for r in result:
771 
        row += 1
772 
        column = 5
773 
        for data in r :
774 
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
775 
            column += 1
15551 manas 776 
 
777 
    row=0
17396 manas 778 
    cursor.execute(mvolSql,(cutOffDate,cutOffDate))
779 
    result = cursor.fetchall()
780 
 
781 
    for r in result:
15551 manas 782 
        row += 1
783 
        column = 6
17396 manas 784 
        for data in r :
785 
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
786 
            column += 1
18021 manas 787 
 
17396 manas 788 
    row = 0
18021 manas 789 
    cursor.execute(mownoSql,(cutOffDate))
790 
    result = cursor.fetchall()
791 
    for r in result:
792 
        column = 8
793 
        row=monthMapDb.get(str(r[0]))
794 
        if row:
795 
            worksheet.write(row, column, r[1])
796 
 
797 
    row = 0
17396 manas 798 
    cursor.execute(mownvolSql,(cutOffDate))
799 
    result = cursor.fetchall()
800 
 
801 
    for r in result:
15551 manas 802 
        row += 1
18021 manas 803 
        column = 9
17396 manas 804 
        for data in r :
805 
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
806 
            column += 1
18012 manas 807 
 
808 
    row=1
18021 manas 809 
    column=11
18012 manas 810 
    breakDate = to_java_date(datetime.now())
811 
    currentDay = cutOff*1000
812 
    while True:
813 
        currentDay = currentDay + (monthMap.get(month_get(currentDay))*86400000)
814 
        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')
815 
        currentDay = currentDay+ 86400000
816 
        worksheet.write(row,column,len(result))
817 
        row=row+1
818 
        if currentDay>breakDate:
819 
            break
820 
 
18201 manas 821 
    row=1
822 
    column=12
823 
    breakDate = to_java_date(datetime.now())
824 
    currentDay = cutOff*1000
825 
    while True:
826 
        currentDay = currentDay + (monthMap.get(month_get(currentDay))*86400000)
827 
        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')
828 
        currentDay = currentDay+ 86400000
829 
        worksheet.write(row,column,len(result))
830 
        row=row+1
831 
        if currentDay>breakDate:
832 
            break
18012 manas 833 
 
834 
    row = 0
835 
    cursor.execute(maccsbSql,(cutOffDate))
836 
    result = cursor.fetchall()
837 
    for r in result:
18201 manas 838 
        column = 13
18012 manas 839 
        row=monthMapDb.get(str(r[0]))
840 
        if row:
841 
            worksheet.write(row, column, r[1])
18021 manas 842 
 
843 
    row = 0
844 
    cursor.execute(maccstoSql,(cutOffDate))
845 
    result = cursor.fetchall()
846 
 
847 
    for r in result:
18201 manas 848 
        column = 14
18021 manas 849 
        row=monthMapDb.get(str(r[0]))
850 
        if row:
851 
            worksheet.write(row, column, r[1])
18012 manas 852 
 
853 
    row = 0
854 
    cursor.execute(maccsoSql,(cutOffDate))
855 
    result = cursor.fetchall()
856 
 
857 
    for r in result:
18201 manas 858 
        column = 15
18012 manas 859 
        row=monthMapDb.get(str(r[0]))
860 
        if row:
861 
            worksheet.write(row, column, r[1])
862 
            column += 1
863 
            worksheet.write(row, column, r[2])
864 
 
14772 kshitij.so 865 
    workbook.save(TMP_FILE)
17031 manas 866 
 
14772 kshitij.so 867 
def generateWeeklyReport():
868 
    weekSql = WEEK_QUERY
869 
    wnruSql = WNRU_QUERY
870 
    wauSql = WAU_QUERY
871 
    wabSql = WAB_QUERY
872 
    wtoSql = WTO_QUERY
17265 manas 873 
    wownSql=WOWN_QUERY
874 
    wvolSql=WVOL_QUERY
18012 manas 875 
    waccsbSql = WACCSB_QUERY
876 
    waccsoSql = WACCSO_QUERY
18021 manas 877 
    wownoSql=WOWNO_QUERY
878 
    waccstoSql=WACCSTO_QUERY
879 
 
14772 kshitij.so 880 
    conn = getDbConnection()
881 
 
882 
    cursor = conn.cursor()
883 
 
17217 manas 884 
    cursor.execute(weekSql,(last_day(cutOffDate, 'sunday')))
14772 kshitij.so 885 
    result = cursor.fetchall()
886 
    rb = open_workbook(TMP_FILE)
887 
    wb = copy(rb)
888 
    worksheet = workbook.add_sheet("Weekly")
889 
    boldStyle = xlwt.XFStyle()
890 
    f = xlwt.Font()
891 
    f.bold = True
892 
    boldStyle.font = f
893 
    column = 0
894 
    row = 0
895 
    worksheet.write(row, 0, 'Week', boldStyle)
896 
    worksheet.write(row, 1, 'WTRU', boldStyle)
897 
    worksheet.write(row, 2, 'WNRU', boldStyle)
898 
    worksheet.write(row, 3, 'WAU', boldStyle)
899 
    worksheet.write(row, 4, 'WAB', boldStyle)
900 
    worksheet.write(row, 5, 'WTO', boldStyle)
16933 manas 901 
    worksheet.write(row, 6, 'WVOL', boldStyle)
15228 manas 902 
    worksheet.write(row, 7, 'WTV', boldStyle)
18021 manas 903 
    worksheet.write(row, 8, 'WOWNORD', boldStyle)
904 
    worksheet.write(row, 9, 'WOWNVOL', boldStyle)
905 
    worksheet.write(row, 10, 'WOWNVAL', boldStyle)
906 
    worksheet.write(row, 11, 'WACCSU', boldStyle)
18201 manas 907 
    worksheet.write(row, 12, 'WACCSCART', boldStyle)
908 
    worksheet.write(row, 13, 'WACCSB', boldStyle)
909 
    worksheet.write(row, 14, 'WACCSORD', boldStyle)
910 
    worksheet.write(row, 15, 'WACCSVOL', boldStyle)
911 
    worksheet.write(row, 16, 'WACCSVAL', boldStyle)
18021 manas 912 
 
18012 manas 913 
    weekMap={}
18021 manas 914 
 
14772 kshitij.so 915 
    for r in result:
916 
        row += 1
917 
        column = 0
18012 manas 918 
        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)
919 
        weekMap[str(r[1])]=row
920 
        column += 1
14772 kshitij.so 921 
 
17265 manas 922 
    cursor.execute(MTRU_QUERY,(cutOffDate))
923 
    result = cursor.fetchall()
924 
    for r in result:
925 
        sumDa=r[0]
926 
 
14772 kshitij.so 927 
    row = 0
17265 manas 928 
    cursor.execute(wnruSql,(cutOffDate))
14772 kshitij.so 929 
    result = cursor.fetchall()
930 
    for r in result:
931 
        row += 1
932 
        column = 1
933 
        for data in r :
17265 manas 934 
            sumDa=sumDa+data
935 
            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 936 
            column += 1
937 
 
938 
    row = 0
17265 manas 939 
    cursor.execute(wnruSql,(cutOffDate))
14772 kshitij.so 940 
    result = cursor.fetchall()
941 
    for r in result:
942 
        row += 1
943 
        column = 2
944 
        for data in r :
945 
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
946 
            column += 1
947 
 
948 
    row = 0
17265 manas 949 
    cursor.execute(wauSql,(cutOffDate))
14772 kshitij.so 950 
    result = cursor.fetchall()
951 
 
952 
    for r in result:
953 
        row += 1
954 
        column = 3
955 
        for data in r :
956 
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
957 
            column += 1
958 
 
959 
    row = 0
17265 manas 960 
    cursor.execute(wabSql,(cutOffDate))
14772 kshitij.so 961 
    result = cursor.fetchall()
962 
 
963 
    for r in result:
964 
        row += 1
965 
        column = 4
966 
        for data in r :
967 
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
968 
            column += 1
969 
 
970 
    row = 0
17265 manas 971 
    cursor.execute(wtoSql,(cutOffDate))
14772 kshitij.so 972 
    result = cursor.fetchall()
973 
 
974 
    for r in result:
975 
        row += 1
976 
        column = 5
977 
        for data in r :
978 
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
979 
            column += 1
17265 manas 980 
 
981 
    row = 0
17269 manas 982 
    cursor.execute(wvolSql,(cutOffDate,cutOffDate))
17265 manas 983 
    result = cursor.fetchall()
984 
 
985 
    for r in result:
15228 manas 986 
        row += 1
987 
        column = 6
17265 manas 988 
        for data in r :
989 
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
990 
            column += 1
18021 manas 991 
 
992 
    row = 0
993 
    cursor.execute(wownoSql,(cutOffDate))
994 
    result = cursor.fetchall()
995 
    for r in result:
996 
        column = 8
997 
        row=weekMap.get(str(r[0]))
998 
        if row:
999 
            worksheet.write(row, column, r[1])
17265 manas 1000 
 
1001 
    row = 0
1002 
    cursor.execute(wownSql,(cutOffDate))
1003 
    result = cursor.fetchall()
16964 manas 1004 
 
17265 manas 1005 
    for r in result:
16964 manas 1006 
        row += 1
18021 manas 1007 
        column = 9
17265 manas 1008 
        for data in r :
1009 
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
1010 
            column += 1
18012 manas 1011 
 
1012 
    row=1
18021 manas 1013 
    column=11
18012 manas 1014 
    breakDate = to_java_date(datetime.now())
1015 
    currentDay = to_java_date(last_day(cutOffDate, 'sunday'))
1016 
    while True:
1017 
        currentDay = currentDay + (7*86400000)
1018 
        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')
1019 
        worksheet.write(row,column,len(result))
1020 
        row=row+1
1021 
        if currentDay>breakDate:
1022 
            break
18201 manas 1023 
 
1024 
 
1025 
    row=1
1026 
    column=12
1027 
    breakDate = to_java_date(datetime.now())
1028 
    currentDay = to_java_date(last_day(cutOffDate, 'sunday'))
1029 
    while True:
1030 
        currentDay = currentDay + (7*86400000)
1031 
        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')
1032 
        worksheet.write(row,column,len(result))
1033 
        row=row+1
1034 
        if currentDay>breakDate:
1035 
            break
1036 
 
18012 manas 1037 
    row = 0
1038 
    cursor.execute(waccsbSql,(cutOffDate))
1039 
    result = cursor.fetchall()
1040 
    for r in result:
18201 manas 1041 
        column = 13
18012 manas 1042 
        row=weekMap.get(str(r[0]))
1043 
        if row:
1044 
            worksheet.write(row, column, r[1])
18021 manas 1045 
 
18012 manas 1046 
    row = 0
18021 manas 1047 
    cursor.execute(waccstoSql,(cutOffDate))
1048 
    result = cursor.fetchall()
1049 
 
1050 
    for r in result:
18201 manas 1051 
        column = 14
18021 manas 1052 
        row=weekMap.get(str(r[0]))
1053 
        if row:
1054 
            worksheet.write(row, column, r[1])
1055 
 
1056 
    row = 0
18012 manas 1057 
    cursor.execute(waccsoSql,(cutOffDate))
1058 
    result = cursor.fetchall()
1059 
 
1060 
    for r in result:
18201 manas 1061 
        column = 15
18012 manas 1062 
        row=weekMap.get(str(r[0]))
1063 
        if row:
1064 
            worksheet.write(row, column, r[1])
1065 
            column += 1
1066 
            worksheet.write(row, column, r[2])
17265 manas 1067 
 
16964 manas 1068 
 
14772 kshitij.so 1069 
def sendmail(email, message, fileName, title):
1070 
    if email == "":
1071 
        return
1072 
    mailServer = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)
1073 
    mailServer.ehlo()
1074 
    mailServer.starttls()
1075 
    mailServer.ehlo()
1076 
 
1077 
    # Create the container (outer) email message.
1078 
    msg = MIMEMultipart()
1079 
    msg['Subject'] = title
1080 
    msg.preamble = title
1081 
    html_msg = MIMEText(message, 'html')
1082 
    msg.attach(html_msg)
1083 
 
1084 
    fileMsg = MIMEBase('application', 'vnd.ms-excel')
1085 
    fileMsg.set_payload(file(TMP_FILE).read())
1086 
    encoders.encode_base64(fileMsg)
1087 
    fileMsg.add_header('Content-Disposition', 'attachment;filename=' + fileName)
1088 
    msg.attach(fileMsg)
14805 kshitij.so 1089 
 
18155 manas 1090 
    MAILTO = ['manas.kapoor@saholic.com','rajneesh.arora@saholic.com', 'amit.gupta@saholic.com','chaitnaya.vats@shop2020.in']
1091 
    #MAILTO = ['manas.kapoor@saholic.com']
14772 kshitij.so 1092 
    mailServer.login(SENDER, PASSWORD)
17443 manish.sha 1093 
    mailServer.sendmail(SENDER, MAILTO, msg.as_string())
14772 kshitij.so 1094 
 
15228 manas 1095 
class __Order:
1096 
 
1097 
    def __init__(self, count, value):
1098 
 
1099 
        self.count = count
1100 
        self.value = value
1101 
 
1102 
def get_mongo_connection(host='localhost', port=27017):
1103 
    global con
1104 
    if con is None:
1105 
        print "Establishing connection %s host and port %d" %(host,port)
1106 
        try:
1107 
            con = pymongo.MongoClient(host, port)
1108 
        except Exception, e:
1109 
            print e
1110 
            return None
1111 
    return con
1112 
 
1113 
def populateOrderMap():
1114 
    global dateWiseOrderMap
17171 amit.gupta 1115 
    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 1116 
    for orders in allOrders:
1117 
        if orders.get('orderId') not in order_ids:
1118 
            continue
1119 
        cdate = ((to_py_date(orders.get('createdOnInt') * 1000)))
1120 
        millisec = to_java_date(datetime(cdate.year, cdate.month, cdate.day))
1121 
        if dateWiseOrderMap.has_key(millisec):
1122 
            orderObj = dateWiseOrderMap.get(millisec)
1123 
            q, c = getSubOrderQuantity(orders.get('subOrders'))
1124 
            orderObj.count += q
1125 
            orderObj.value += c
1126 
        else:
1127 
            orderObj = __Order(None, None)
1128 
            q, c = getSubOrderQuantity(orders.get('subOrders'))
1129 
            orderObj.count = q
1130 
            orderObj.value = c
1131 
            dateWiseOrderMap[millisec] = orderObj
1132 
 
15551 manas 1133 
 
1134 
 
15228 manas 1135 
def getSubOrderQuantity(subOrders):
1136 
    q = 0
1137 
    c = 0
1138 
    if subOrders is None:
1139 
        return q, c
1140 
    for subOrder in subOrders:
1141 
        q = q + int(subOrder.get('quantity'))
1142 
        try:
1143 
            c = c + float(subOrder.get('amountPaid').encode('utf-8'))
1144 
        except AttributeError:
1145 
            c = c + float(subOrder.get('amountPaid'))
1146 
    return q, c
1147 
 
1148 
def populateWeekWiseMap():
1149 
    global weekCutOff
1150 
    while(True):
1151 
        quantity, amount = 0 , 0
1152 
        for i in xrange(weekCutOff , (weekCutOff * 7)+1):
1153 
            orderObj = dateWiseOrderMap.get(i*1000)
1154 
            quantity += orderObj.count
1155 
            amount += orderObj.value
1156 
            i = i + oneDay
1157 
        weekWiseOrderMap[weekCutOff * 1000] = __Order(quantity, amount)
1158 
        weekCutOff = weekCutOff * 1000 * 7 + oneDay
1159 
        if weekCutOff >= to_py_date(datetime.now()):
1160 
            break
1161 
 
1162 
def populateWeekWiseMap1():
1163 
    global weekCutOff
1164 
    while(True):
1165 
        if weekCutOff *1000 >= to_java_date(datetime.now()):
1166 
            break
1167 
        init = weekCutOff
1168 
        breakPoint = weekCutOff + (6 * oneDay)
1169 
        quantity, amount = 0 , 0
1170 
        while(True):
1171 
            orderObj = dateWiseOrderMap.get(weekCutOff * 1000)
1172 
            if orderObj is not None:
1173 
                quantity += orderObj.count
1174 
                amount += orderObj.value
1175 
            weekCutOff = weekCutOff + oneDay
1176 
            if weekCutOff > breakPoint:
1177 
                weekWiseOrderMap[init * 1000] = __Order(quantity, amount)
1178 
                break
1179 
 
1180 
def populateMonthWiseMap():
1181 
    global monthCutOff
1182 
    while(True):
1183 
        quantity, amount = 0 , 0
1184 
        for i in xrange(monthCutOff , (monthCutOff * 30)+1):
1185 
            orderObj = dateWiseOrderMap.get(i*1000)
1186 
            quantity += orderObj.count
1187 
            amount += orderObj.value
1188 
            i = i + oneDay
1189 
        weekWiseOrderMap[monthCutOff * 1000] = __Order(quantity, amount)
1190 
        monthCutOff = monthCutOff * 1000 * 30 + oneDay
1191 
        if monthCutOff >= to_py_date(datetime.now()):
1192 
            break
1193 
 
1194 
def populateMonthWiseMap1():
15551 manas 1195 
    global monthCutOff
15228 manas 1196 
    while(True):
15551 manas 1197 
 
1198 
        print monthCutOff *1000
1199 
        a=str(to_py_date(monthCutOff*1000))
1200 
        print 'Now ' + str(to_java_date(datetime.now()))
15228 manas 1201 
        print "**********************"
15551 manas 1202 
        if monthCutOff *1000 >= to_java_date(datetime.now()):
15228 manas 1203 
            print "Breaking outer while"
1204 
            break
15551 manas 1205 
        init = monthCutOff
1206 
        breakPoint = monthCutOff + (monthMap.get(a[5:7]) * oneDay)
1207 
        print breakPoint
15228 manas 1208 
        quantity, amount = 0 , 0
1209 
        while(True):
15551 manas 1210 
            print to_py_date(monthCutOff*1000)
1211 
            print "weekCutOff ",monthCutOff
15228 manas 1212 
            print "breakPoint ",breakPoint
15551 manas 1213 
            orderObj = dateWiseOrderMap.get(monthCutOff * 1000)
15228 manas 1214 
            if orderObj is None:
15551 manas 1215 
                print "None for ", to_py_date(monthCutOff * 1000)
15228 manas 1216 
            if orderObj is not None:
1217 
                quantity += orderObj.count
1218 
                amount += orderObj.value
15551 manas 1219 
            monthCutOff = monthCutOff + oneDay
1220 
            counter=0
1221 
            print counter+1
1222 
            if monthCutOff > breakPoint:
1223 
                monthWiseOrderMap[init * 1000] = __Order(quantity, amount)
15228 manas 1224 
                print "Breaking inner while"
1225 
                break
16964 manas 1226 
 
1227 
def populateSaholicOrderMap():
1228 
    global dateWiseSaholicOrderMap
1229 
    allOrders = get_mongo_connection().Dtr.merchantOrder.find({'createdOnInt':{"$gte":cutOff},'storeId':{"$in":[4]}}).sort([('createdOnInt',pymongo.ASCENDING)])
1230 
    for orders in allOrders:
1231 
        if orders.get('orderId') not in order_ids:
1232 
            continue
1233 
        cdate = ((to_py_date(orders.get('createdOnInt') * 1000)))
1234 
        millisec = to_java_date(datetime(cdate.year, cdate.month, cdate.day))
1235 
        if dateWiseSaholicOrderMap.has_key(millisec):
1236 
            orderObj = dateWiseSaholicOrderMap.get(millisec)
1237 
            q, c = getSaholicSubOrderQuantity(orders.get('subOrders'))
1238 
            orderObj.count += q
1239 
            orderObj.value += c
1240 
        else:
1241 
            orderObj = __Order(None, None)
1242 
            q, c = getSaholicSubOrderQuantity(orders.get('subOrders'))
1243 
            orderObj.count = q
1244 
            orderObj.value = c
1245 
            dateWiseSaholicOrderMap[millisec] = orderObj
15228 manas 1246 
 
16964 manas 1247 
 
1248 
 
1249 
def getSaholicSubOrderQuantity(subOrders):
1250 
    q = 0
1251 
    c = 0
1252 
    if subOrders is None:
1253 
        return q, c
1254 
    for subOrder in subOrders:
1255 
        q = q + int(subOrder.get('quantity'))
1256 
        try:
1257 
            c = c + float(subOrder.get('amountPaid').encode('utf-8'))
1258 
        except AttributeError:
1259 
            c = c + float(subOrder.get('amountPaid'))
1260 
    return q, c
1261 
 
1262 
def populateSaholicWeekWiseMap1():
1263 
    global newWeekCutOff
1264 
    while(True):
1265 
        if newWeekCutOff *1000 >= to_java_date(datetime.now()):
1266 
            break
1267 
        init = newWeekCutOff
1268 
        breakPoint = newWeekCutOff + (6 * oneDay)
1269 
        quantity, amount = 0 , 0
1270 
        while(True):
1271 
            orderObj = dateWiseSaholicOrderMap.get(newWeekCutOff * 1000)
1272 
            if orderObj is not None:
1273 
                quantity += orderObj.count
1274 
                amount += orderObj.value
1275 
            else:
1276 
                quantity +=0
1277 
                amount +=0
1278 
            newWeekCutOff = newWeekCutOff + oneDay
1279 
            if newWeekCutOff > breakPoint:
1280 
                weekWiseSaholicOrderMap[init * 1000] = __Order(quantity, amount)
1281 
                break
17134 amit.gupta 1282 
def populateYesterdayActiveUsers(hours=4):
1283 
    con = Mongo.get_mongo_connection(DTR_DATA_HOST)
1284 
    db = con.User
1285 
    curtime = datetime.now()
1286 
    fourhrsprev = curtime - timedelta(hours=hours)
1287 
    pipeline = [{"$match":{"created":{"$lt":toTimeStamp(curtime)*1000, "$gt":toTimeStamp(fourhrsprev)*1000}}},
1288 
                {"$group":{"_id":{"user_id":"$user_id","created":{"$subtract":["$created",{"$mod":["$created",86400000]}]}}}}]
1289 
    alluserdates=db.browsinghistories.aggregate(pipeline)['result']
1290 
    insertq = "insert ignore into daily_visitors values (%s, %s)"
1291 
    conn = getDbConnection()
1292 
    try:
1293 
        for a in alluserdates:
1294 
            cursor = conn.cursor()
1295 
            # Execute the SQL command
1296 
            # Fetch source id.
1297 
            cursor.execute(insertq,(a['_id']['user_id'], utils.fromTimeStamp(int(a['_id']['created']/1000))))
1298 
            conn.commit()
1299 
    finally:
1300 
        conn.close()
16964 manas 1301 
 
1302 
def populateSaholicMonthWiseMap1():
1303 
    global newMonthCutOff
1304 
    while(True):
1305 
 
1306 
        print newMonthCutOff *1000
1307 
        a=str(to_py_date(newMonthCutOff*1000))
1308 
        print 'Now ' + str(to_java_date(datetime.now()))
1309 
        print "**********************"
1310 
        if newMonthCutOff *1000 >= to_java_date(datetime.now()):
1311 
            print "Breaking outer while"
1312 
            break
1313 
        init = newMonthCutOff
1314 
        breakPoint = newMonthCutOff + (monthMap.get(a[5:7]) * oneDay)
1315 
        print breakPoint
1316 
        quantity, amount = 0 , 0
1317 
        while(True):
1318 
            print to_py_date(newMonthCutOff*1000)
1319 
            print "weekCutOff ",newMonthCutOff
1320 
            print "breakPoint ",breakPoint
1321 
            orderObj = dateWiseSaholicOrderMap.get(newMonthCutOff * 1000)
1322 
            if orderObj is None:
1323 
                print "None for ", to_py_date(newMonthCutOff * 1000)
1324 
                quantity += 0
1325 
                amount += 0
1326 
            if orderObj is not None:
1327 
                quantity += orderObj.count
1328 
                amount += orderObj.value
1329 
            newMonthCutOff = newMonthCutOff + oneDay
1330 
            counter=0
1331 
            print counter+1
1332 
            if newMonthCutOff > breakPoint:
1333 
                monthWiseSaholicOrderMap[init * 1000] = __Order(quantity, amount)
1334 
                print "Breaking inner while"
1335 
                break
1336 
 
1337 
 
15228 manas 1338 
def populateValidOrders():
1339 
    global order_ids
17217 manas 1340 
    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 1341 
    order_ids = list(zip(*allOrders)[0])
17217 manas 1342 
 
1343 
def last_day(d, day_name):
1344 
    days_of_week = ['sunday','monday','tuesday','wednesday',
1345 
                        'thursday','friday','saturday']
1346 
    target_day = days_of_week.index(day_name.lower())
1347 
    delta_day = target_day - (d.isoweekday()%7)
1348 
    return d + timedelta(days=delta_day)
18012 manas 1349 
 
1350 
def get_mongo_connection_new(host='45.33.50.227', port=27017):
1351 
    global con
1352 
    if con is None:
1353 
        print "Establishing connection %s host and port %d" %(host,port)
1354 
        try:
1355 
            con = pymongo.MongoClient(host, port)
1356 
        except Exception, e:
1357 
            print e
1358 
            return None
1359 
    return con
1360 
 
1361 
 
14772 kshitij.so 1362 
def main():
18155 manas 1363 
    populateYesterdayActiveUsers(24)
17396 manas 1364 
    #populateValidOrders()
1365 
    #populateOrderMap()
17269 manas 1366 
    #populateWeekWiseMap1()
17396 manas 1367 
    #populateMonthWiseMap1()
1368 
    #populateSaholicOrderMap()
17269 manas 1369 
    #populateSaholicWeekWiseMap1()
17396 manas 1370 
    #populateSaholicMonthWiseMap1()
14772 kshitij.so 1371 
    generateDailyReport()
1372 
    generateWeeklyReport()
1373 
    generateMonthlyReport()
18155 manas 1374 
    sendmail(["manas.kapoor@shop2020.in","rajneesh.arora@saholic.com", "amit.gupta@shop2020.in","chaitnaya.vats@shop2020.in"], "", TMP_FILE, SUBJECT)
1375 
    #sendmail(["manas.kapoor@shop2020.in"], "", TMP_FILE, SUBJECT)
15228 manas 1376 
 
17269 manas 1377 
 
16964 manas 1378 
def to_x_date(java_timestamp):
1379 
    try:
1380 
        date = datetime.fromtimestamp(java_timestamp / 1e3)
1381 
    except:
1382 
        return None
1383 
    return date.strftime('%Y-%m-%d')
15551 manas 1384 
 
18012 manas 1385 
def month_get(java_timestamp):
1386 
    try:
1387 
        date = datetime.fromtimestamp(java_timestamp / 1e3)
1388 
    except:
1389 
        return None
1390 
    return date.strftime('%m')
1391 
 
14772 kshitij.so 1392 
if __name__ == '__main__':
17158 amit.gupta 1393 
    if len(sys.argv)==1:
17134 amit.gupta 1394 
        main()
1395 
    else:
17135 amit.gupta 1396 
        populateYesterdayActiveUsers(int(sys.argv[1]))