Subversion Repositories SmartDukaan

Rev

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