Subversion Repositories SmartDukaan

Rev

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