Subversion Repositories SmartDukaan

Rev

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