Subversion Repositories SmartDukaan

Rev

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