Subversion Repositories SmartDukaan

Rev

Rev 17209 | Rev 17265 | Go to most recent revision | Details | Compare with Previous | Last modification | View Log | RSS feed

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