Subversion Repositories SmartDukaan

Rev

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