Subversion Repositories SmartDukaan

Rev

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