Subversion Repositories SmartDukaan

Rev

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