Subversion Repositories SmartDukaan

Rev

Rev 17158 | Rev 17209 | 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 = []
17171 amit.gupta 47 
cutOff = utils.toTimeStamp(date.today()-timedelta(days=90))
15228 manas 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)
17171 amit.gupta 338 
    dateMap={}
17128 manas 339 
    for r in result:
14772 kshitij.so 340 
        row += 1
341 
        column = 0
17128 manas 342 
        for data in r :
343 
            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 344 
            dateMap[str(data)]=row
17128 manas 345 
            column += 1
14772 kshitij.so 346 
 
347 
    row = 0
348 
    cursor.execute(dnruSql)
349 
    result = cursor.fetchall()
350 
    for r in result:
351 
        row += 1
352 
        column = 1
353 
        for data in r :
354 
            sumdata=sumdata+data
355 
            worksheet.write(row, column, int(sumdata) if type(sumdata) is float else sumdata, date_format if type(sumdata) is date else default_format)
356 
            column += 1
357 
 
358 
    row = 0
359 
    cursor.execute(dnruSql)
360 
    result = cursor.fetchall()
361 
    for r in result:
362 
        row += 1
363 
        column = 2
364 
        for data in r :
365 
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
366 
            column += 1
367 
 
368 
    row = 0
369 
    cursor.execute(dauSql)
370 
    result = cursor.fetchall()
371 
 
372 
    for r in result:
373 
        row += 1
374 
        column = 3
375 
        for data in r :
376 
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
377 
            column += 1
378 
 
379 
    row = 0
380 
    cursor.execute(dabSql)
381 
    result = cursor.fetchall()
382 
 
383 
    for r in result:
384 
        row += 1
385 
        column = 4
386 
        for data in r :
387 
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
388 
            column += 1
389 
 
390 
    row = 0
391 
    cursor.execute(dtoSql)
392 
    result = cursor.fetchall()
393 
    for r in result:
394 
        row += 1
395 
        column = 5
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
15228 manas 399 
 
400 
    row=0
14772 kshitij.so 401 
 
15228 manas 402 
    z=0
403 
    for x in sorted(dateWiseOrderMap):
404 
        column = 6
17171 amit.gupta 405 
        d = datetime.fromtimestamp(x/1000.0)
406 
        row = dateMap[datetime.strftime(d,'%Y-%m-%d')]
407 
        dq=DQ_QUERY%str(d)[:10]
15551 manas 408 
        cursor.execute(dq)
409 
        result = cursor.fetchall()
410 
        for r in result:
411 
            quantity= r[0]
412 
            if quantity is None:
413 
                quantity=0
414 
        worksheet.write(row,column,(dateWiseOrderMap.get(x).count)+quantity)
15228 manas 415 
        column += 1
14772 kshitij.so 416 
 
15228 manas 417 
    row =0
418 
    for x in sorted(dateWiseOrderMap):
419 
        column = 7
17171 amit.gupta 420 
        d = datetime.fromtimestamp(x/1000.0)
421 
        row = dateMap[datetime.strftime(d, '%Y-%m-%d')]
422 
        dq=DV_QUERY%str(d)[:10]
15551 manas 423 
        cursor.execute(dq)
424 
        result = cursor.fetchall()
425 
        for r in result:
426 
            quantity= r[0]
427 
            if quantity is None:
428 
                quantity=0
429 
        worksheet.write(row,column,(dateWiseOrderMap.get(x).value)+float(quantity))
15228 manas 430 
 
16964 manas 431 
    #For Saholic
432 
    cursor = conn.cursor()
433 
    cursor.execute(datesql)
434 
    result = cursor.fetchall()
435 
    for x in sorted(dateWiseSaholicOrderMap):
17171 amit.gupta 436 
        z= to_x_date(x)
437 
        row = dateMap.get(z)
438 
        if row:
439 
            column = 8
440 
            worksheet.write(row,column,dateWiseSaholicOrderMap.get(x).count)
16964 manas 441 
 
442 
    for x in sorted(dateWiseSaholicOrderMap):
17171 amit.gupta 443 
        z= to_x_date(x)
444 
        row=dateMap.get(z)
445 
        if row:
446 
                column = 9
447 
                worksheet.write(row,column,dateWiseSaholicOrderMap.get(x).value)
16964 manas 448 
 
449 
 
14772 kshitij.so 450 
def generateMonthlyReport():
451 
    monthSql = MONTH_QUERY
452 
    mnruSql = MNRU_QUERY
453 
    mauSql = MAU_QUERY
454 
    mabSql = MAB_QUERY
455 
    mtoSql = MTO_QUERY
456 
 
457 
    conn = getDbConnection()
458 
 
459 
    cursor = conn.cursor()
460 
 
461 
    cursor.execute(monthSql)
462 
    result = cursor.fetchall()
463 
    rb = open_workbook(TMP_FILE)
464 
    wb = copy(rb)
465 
    worksheet = workbook.add_sheet("Monthly")
466 
    boldStyle = xlwt.XFStyle()
467 
    f = xlwt.Font()
468 
    f.bold = True
469 
    boldStyle.font = f
470 
    column = 0
471 
    row = 0
472 
    sumdata=17
473 
    worksheet.write(row, 0, 'Month', boldStyle)
474 
    worksheet.write(row, 1, 'MTRU', boldStyle)
475 
    worksheet.write(row, 2, 'MNRU', boldStyle)
476 
    worksheet.write(row, 3, 'MAU', boldStyle)
477 
    worksheet.write(row, 4, 'MAB', boldStyle)
478 
    worksheet.write(row, 5, 'MTO', boldStyle)
16933 manas 479 
    worksheet.write(row, 6, 'MVOL', boldStyle)
15551 manas 480 
    worksheet.write(row, 7, 'MTV', boldStyle)
16964 manas 481 
    worksheet.write(row, 8, 'MOWNVOL', boldStyle)
482 
    worksheet.write(row, 9, 'MOWNVAL', boldStyle)
15551 manas 483 
 
14772 kshitij.so 484 
    for r in result:
485 
        row += 1
486 
        column = 0
487 
        for data in r :
488 
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
489 
            column += 1
490 
 
491 
    row = 0
492 
    cursor.execute(mnruSql)
493 
    result = cursor.fetchall()
494 
    for r in result:
495 
        row += 1
496 
        column = 1
497 
        for data in r :
498 
            sumdata=sumdata+data
499 
            worksheet.write(row, column, int(sumdata) if type(sumdata) is float else sumdata, date_format if type(sumdata) is date else default_format)
500 
            column += 1
501 
 
502 
    row = 0
503 
    cursor.execute(mnruSql)
504 
    result = cursor.fetchall()
505 
    for r in result:
506 
        row += 1
507 
        column = 2
508 
        for data in r :
509 
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
510 
            column += 1
511 
 
512 
    row = 0
513 
    cursor.execute(mauSql)
514 
    result = cursor.fetchall()
515 
 
516 
    for r in result:
517 
        row += 1
518 
        column = 3
519 
        for data in r :
520 
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
521 
            column += 1
522 
 
523 
    row = 0
524 
    cursor.execute(mabSql)
525 
    result = cursor.fetchall()
526 
 
527 
    for r in result:
528 
        row += 1
529 
        column = 4
530 
        for data in r :
531 
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
532 
            column += 1
533 
 
534 
    row = 0
535 
    cursor.execute(mtoSql)
536 
    result = cursor.fetchall()
537 
 
538 
    for r in result:
539 
        row += 1
540 
        column = 5
541 
        for data in r :
542 
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
543 
            column += 1
15551 manas 544 
 
545 
    row=0
14772 kshitij.so 546 
 
15551 manas 547 
    z=0
548 
    for x in sorted(monthWiseOrderMap):
549 
        d = str(datetime.fromtimestamp(x/1000.0))
550 
        nextday = (x/1000 + (monthMap.get(d[5:7]) * oneDay))*1000
551 
        dnext = str(datetime.fromtimestamp(nextday/1000.0))
552 
        row += 1
553 
        column = 6
554 
        dq=WQ_QUERY % (d[:10],dnext[:10])
555 
        cursor.execute(dq)
556 
        result = cursor.fetchall()
557 
        for r in result:
558 
            quantity= r[0]
559 
            if quantity is None:
560 
                quantity=0
561 
        worksheet.write(row,column,(monthWiseOrderMap.get(x).count)+quantity)
562 
        column += 1
563 
 
564 
    row =0
565 
    for x in sorted(monthWiseOrderMap):
566 
        row += 1
567 
        column = 7
568 
        d = str(datetime.fromtimestamp(x/1000.0))
569 
        nextday = (x/1000 + (monthMap.get(d[5:7]) * oneDay))*1000
570 
        dnext = str(datetime.fromtimestamp(nextday/1000.0))
571 
        dq=WV_QUERY % (d[:10],dnext[:10])
572 
        cursor.execute(dq)
573 
        result = cursor.fetchall()
574 
        for r in result:
575 
            quantity= r[0]
576 
            if quantity is None:
577 
                quantity=0
578 
        worksheet.write(row,column,(monthWiseOrderMap.get(x).value)+float(quantity))
16964 manas 579 
        column += 1
580 
 
581 
    row =0
582 
    for x in sorted(monthWiseSaholicOrderMap):
583 
        row += 1
584 
        column = 8
585 
        worksheet.write(row,column,monthWiseSaholicOrderMap.get(x).count)
586 
        column += 1
587 
    row =0
588 
    for x in sorted(monthWiseSaholicOrderMap):
589 
        row += 1
590 
        column = 9
591 
        worksheet.write(row,column,monthWiseSaholicOrderMap.get(x).value)
592 
        column += 1
593 
 
14772 kshitij.so 594 
    workbook.save(TMP_FILE)
17031 manas 595 
 
14772 kshitij.so 596 
def generateWeeklyReport():
597 
    weekSql = WEEK_QUERY
598 
    wnruSql = WNRU_QUERY
599 
    wauSql = WAU_QUERY
600 
    wabSql = WAB_QUERY
601 
    wtoSql = WTO_QUERY
602 
 
603 
    conn = getDbConnection()
604 
 
605 
    cursor = conn.cursor()
606 
 
607 
    cursor.execute(weekSql)
608 
    result = cursor.fetchall()
609 
    rb = open_workbook(TMP_FILE)
610 
    wb = copy(rb)
611 
    worksheet = workbook.add_sheet("Weekly")
612 
    boldStyle = xlwt.XFStyle()
613 
    f = xlwt.Font()
614 
    f.bold = True
615 
    boldStyle.font = f
616 
    column = 0
617 
    row = 0
618 
    sumdata=17
619 
    worksheet.write(row, 0, 'Week', boldStyle)
620 
    worksheet.write(row, 1, 'WTRU', boldStyle)
621 
    worksheet.write(row, 2, 'WNRU', boldStyle)
622 
    worksheet.write(row, 3, 'WAU', boldStyle)
623 
    worksheet.write(row, 4, 'WAB', boldStyle)
624 
    worksheet.write(row, 5, 'WTO', boldStyle)
16933 manas 625 
    worksheet.write(row, 6, 'WVOL', boldStyle)
15228 manas 626 
    worksheet.write(row, 7, 'WTV', boldStyle)
16964 manas 627 
    worksheet.write(row, 8, 'WOWNVOL', boldStyle)
628 
    worksheet.write(row, 9, 'WOWNVAL', boldStyle)
14772 kshitij.so 629 
 
630 
    for r in result:
631 
        row += 1
632 
        column = 0
633 
        for data in r :
634 
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
635 
            column += 1
636 
 
637 
    row = 0
638 
    cursor.execute(wnruSql)
639 
    result = cursor.fetchall()
640 
    for r in result:
641 
        row += 1
642 
        column = 1
643 
        for data in r :
644 
            sumdata=sumdata+data
645 
            worksheet.write(row, column, int(sumdata) if type(sumdata) is float else sumdata, date_format if type(sumdata) is date else default_format)
646 
            column += 1
647 
 
648 
    row = 0
649 
    cursor.execute(wnruSql)
650 
    result = cursor.fetchall()
651 
    for r in result:
652 
        row += 1
653 
        column = 2
654 
        for data in r :
655 
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
656 
            column += 1
657 
 
658 
    row = 0
659 
    cursor.execute(wauSql)
660 
    result = cursor.fetchall()
661 
 
662 
    for r in result:
663 
        row += 1
664 
        column = 3
665 
        for data in r :
666 
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
667 
            column += 1
668 
 
669 
    row = 0
670 
    cursor.execute(wabSql)
671 
    result = cursor.fetchall()
672 
 
673 
    for r in result:
674 
        row += 1
675 
        column = 4
676 
        for data in r :
677 
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
678 
            column += 1
679 
 
680 
    row = 0
681 
    cursor.execute(wtoSql)
682 
    result = cursor.fetchall()
683 
 
684 
    for r in result:
685 
        row += 1
686 
        column = 5
687 
        for data in r :
688 
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
689 
            column += 1
15228 manas 690 
 
691 
    row = 0
692 
    for x in sorted(weekWiseOrderMap):
693 
        row += 1
694 
        column = 6
15551 manas 695 
        d = str(datetime.fromtimestamp(x/1000.0))
696 
        nextday=oneDay*7*1000+x
697 
        dnext = str(datetime.fromtimestamp(nextday/1000.0))
698 
        dq=WQ_QUERY % (d[:10],dnext[:10])
699 
        cursor.execute(dq)
700 
        result = cursor.fetchall()
701 
        for r in result:
702 
            quantity= r[0]
703 
            if quantity is None:
704 
                quantity=0
705 
        worksheet.write(row,column,(weekWiseOrderMap.get(x).count)+quantity)
15228 manas 706 
        column+=1
707 
    row = 0
708 
    for x in sorted(weekWiseOrderMap):
709 
        row += 1
710 
        column = 7
15551 manas 711 
        d = str(datetime.fromtimestamp(x/1000.0))
712 
        nextday=oneDay*7*1000+x
713 
        dnext = str(datetime.fromtimestamp(nextday/1000.0))
714 
        dq=WV_QUERY % (d[:10],dnext[:10])
715 
        cursor.execute(dq)
716 
        result = cursor.fetchall()
717 
        for r in result:
718 
            quantity= r[0]
719 
            if quantity is None:
720 
                quantity=0
721 
        worksheet.write(row,column,(weekWiseOrderMap.get(x).value)+float(quantity))
15228 manas 722 
        column+=1
16964 manas 723 
 
724 
    row=0
725 
    for x in sorted(weekWiseSaholicOrderMap):
726 
        row += 1
727 
        column = 8
728 
        worksheet.write(row,column,weekWiseSaholicOrderMap.get(x).count)
729 
        column+=1
730 
 
731 
    row=0
732 
    for x in sorted(weekWiseSaholicOrderMap):
733 
        row += 1
734 
        column = 9
735 
        worksheet.write(row,column,weekWiseSaholicOrderMap.get(x).value)
736 
        column+=1
737 
 
14772 kshitij.so 738 
def sendmail(email, message, fileName, title):
739 
    if email == "":
740 
        return
741 
    mailServer = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)
742 
    mailServer.ehlo()
743 
    mailServer.starttls()
744 
    mailServer.ehlo()
745 
 
746 
    # Create the container (outer) email message.
747 
    msg = MIMEMultipart()
748 
    msg['Subject'] = title
749 
    msg.preamble = title
750 
    html_msg = MIMEText(message, 'html')
751 
    msg.attach(html_msg)
752 
 
753 
    fileMsg = MIMEBase('application', 'vnd.ms-excel')
754 
    fileMsg.set_payload(file(TMP_FILE).read())
755 
    encoders.encode_base64(fileMsg)
756 
    fileMsg.add_header('Content-Disposition', 'attachment;filename=' + fileName)
757 
    msg.attach(fileMsg)
14805 kshitij.so 758 
 
17171 amit.gupta 759 
    MAILTO = ['manas.kapoor@saholic.com','rajneesh.arora@saholic.com', 'amit.gupta@saholic.com']
760 
    #MAILTO = ['amit.gupta@saholic.com']
14772 kshitij.so 761 
    mailServer.login(SENDER, PASSWORD)
762 
    mailServer.sendmail(PASSWORD, MAILTO, msg.as_string())
763 
 
15228 manas 764 
class __Order:
765 
 
766 
    def __init__(self, count, value):
767 
 
768 
        self.count = count
769 
        self.value = value
770 
 
771 
def get_mongo_connection(host='localhost', port=27017):
772 
    global con
773 
    if con is None:
774 
        print "Establishing connection %s host and port %d" %(host,port)
775 
        try:
776 
            con = pymongo.MongoClient(host, port)
777 
        except Exception, e:
778 
            print e
779 
            return None
780 
    return con
781 
 
782 
def populateOrderMap():
783 
    global dateWiseOrderMap
17171 amit.gupta 784 
    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 785 
    for orders in allOrders:
786 
        if orders.get('orderId') not in order_ids:
787 
            continue
788 
        cdate = ((to_py_date(orders.get('createdOnInt') * 1000)))
789 
        millisec = to_java_date(datetime(cdate.year, cdate.month, cdate.day))
790 
        if dateWiseOrderMap.has_key(millisec):
791 
            orderObj = dateWiseOrderMap.get(millisec)
792 
            q, c = getSubOrderQuantity(orders.get('subOrders'))
793 
            orderObj.count += q
794 
            orderObj.value += c
795 
        else:
796 
            orderObj = __Order(None, None)
797 
            q, c = getSubOrderQuantity(orders.get('subOrders'))
798 
            orderObj.count = q
799 
            orderObj.value = c
800 
            dateWiseOrderMap[millisec] = orderObj
801 
 
15551 manas 802 
 
803 
 
15228 manas 804 
def getSubOrderQuantity(subOrders):
805 
    q = 0
806 
    c = 0
807 
    if subOrders is None:
808 
        return q, c
809 
    for subOrder in subOrders:
810 
        q = q + int(subOrder.get('quantity'))
811 
        try:
812 
            c = c + float(subOrder.get('amountPaid').encode('utf-8'))
813 
        except AttributeError:
814 
            c = c + float(subOrder.get('amountPaid'))
815 
    return q, c
816 
 
817 
def populateWeekWiseMap():
818 
    global weekCutOff
819 
    while(True):
820 
        quantity, amount = 0 , 0
821 
        for i in xrange(weekCutOff , (weekCutOff * 7)+1):
822 
            orderObj = dateWiseOrderMap.get(i*1000)
823 
            quantity += orderObj.count
824 
            amount += orderObj.value
825 
            i = i + oneDay
826 
        weekWiseOrderMap[weekCutOff * 1000] = __Order(quantity, amount)
827 
        weekCutOff = weekCutOff * 1000 * 7 + oneDay
828 
        if weekCutOff >= to_py_date(datetime.now()):
829 
            break
830 
 
831 
def populateWeekWiseMap1():
832 
    global weekCutOff
833 
    while(True):
834 
        if weekCutOff *1000 >= to_java_date(datetime.now()):
835 
            break
836 
        init = weekCutOff
837 
        breakPoint = weekCutOff + (6 * oneDay)
838 
        quantity, amount = 0 , 0
839 
        while(True):
840 
            orderObj = dateWiseOrderMap.get(weekCutOff * 1000)
841 
            if orderObj is not None:
842 
                quantity += orderObj.count
843 
                amount += orderObj.value
844 
            weekCutOff = weekCutOff + oneDay
845 
            if weekCutOff > breakPoint:
846 
                weekWiseOrderMap[init * 1000] = __Order(quantity, amount)
847 
                break
848 
 
849 
def populateMonthWiseMap():
850 
    global monthCutOff
851 
    while(True):
852 
        quantity, amount = 0 , 0
853 
        for i in xrange(monthCutOff , (monthCutOff * 30)+1):
854 
            orderObj = dateWiseOrderMap.get(i*1000)
855 
            quantity += orderObj.count
856 
            amount += orderObj.value
857 
            i = i + oneDay
858 
        weekWiseOrderMap[monthCutOff * 1000] = __Order(quantity, amount)
859 
        monthCutOff = monthCutOff * 1000 * 30 + oneDay
860 
        if monthCutOff >= to_py_date(datetime.now()):
861 
            break
862 
 
863 
def populateMonthWiseMap1():
15551 manas 864 
    global monthCutOff
15228 manas 865 
    while(True):
15551 manas 866 
 
867 
        print monthCutOff *1000
868 
        a=str(to_py_date(monthCutOff*1000))
869 
        print 'Now ' + str(to_java_date(datetime.now()))
15228 manas 870 
        print "**********************"
15551 manas 871 
        if monthCutOff *1000 >= to_java_date(datetime.now()):
15228 manas 872 
            print "Breaking outer while"
873 
            break
15551 manas 874 
        init = monthCutOff
875 
        breakPoint = monthCutOff + (monthMap.get(a[5:7]) * oneDay)
876 
        print breakPoint
15228 manas 877 
        quantity, amount = 0 , 0
878 
        while(True):
15551 manas 879 
            print to_py_date(monthCutOff*1000)
880 
            print "weekCutOff ",monthCutOff
15228 manas 881 
            print "breakPoint ",breakPoint
15551 manas 882 
            orderObj = dateWiseOrderMap.get(monthCutOff * 1000)
15228 manas 883 
            if orderObj is None:
15551 manas 884 
                print "None for ", to_py_date(monthCutOff * 1000)
15228 manas 885 
            if orderObj is not None:
886 
                quantity += orderObj.count
887 
                amount += orderObj.value
15551 manas 888 
            monthCutOff = monthCutOff + oneDay
889 
            counter=0
890 
            print counter+1
891 
            if monthCutOff > breakPoint:
892 
                monthWiseOrderMap[init * 1000] = __Order(quantity, amount)
15228 manas 893 
                print "Breaking inner while"
894 
                break
16964 manas 895 
 
896 
def populateSaholicOrderMap():
897 
    global dateWiseSaholicOrderMap
898 
    allOrders = get_mongo_connection().Dtr.merchantOrder.find({'createdOnInt':{"$gte":cutOff},'storeId':{"$in":[4]}}).sort([('createdOnInt',pymongo.ASCENDING)])
899 
    for orders in allOrders:
900 
        if orders.get('orderId') not in order_ids:
901 
            continue
902 
        cdate = ((to_py_date(orders.get('createdOnInt') * 1000)))
903 
        millisec = to_java_date(datetime(cdate.year, cdate.month, cdate.day))
904 
        if dateWiseSaholicOrderMap.has_key(millisec):
905 
            orderObj = dateWiseSaholicOrderMap.get(millisec)
906 
            q, c = getSaholicSubOrderQuantity(orders.get('subOrders'))
907 
            orderObj.count += q
908 
            orderObj.value += c
909 
        else:
910 
            orderObj = __Order(None, None)
911 
            q, c = getSaholicSubOrderQuantity(orders.get('subOrders'))
912 
            orderObj.count = q
913 
            orderObj.value = c
914 
            dateWiseSaholicOrderMap[millisec] = orderObj
15228 manas 915 
 
16964 manas 916 
 
917 
 
918 
def getSaholicSubOrderQuantity(subOrders):
919 
    q = 0
920 
    c = 0
921 
    if subOrders is None:
922 
        return q, c
923 
    for subOrder in subOrders:
924 
        q = q + int(subOrder.get('quantity'))
925 
        try:
926 
            c = c + float(subOrder.get('amountPaid').encode('utf-8'))
927 
        except AttributeError:
928 
            c = c + float(subOrder.get('amountPaid'))
929 
    return q, c
930 
 
931 
def populateSaholicWeekWiseMap1():
932 
    global newWeekCutOff
933 
    while(True):
934 
        if newWeekCutOff *1000 >= to_java_date(datetime.now()):
935 
            break
936 
        init = newWeekCutOff
937 
        breakPoint = newWeekCutOff + (6 * oneDay)
938 
        quantity, amount = 0 , 0
939 
        while(True):
940 
            orderObj = dateWiseSaholicOrderMap.get(newWeekCutOff * 1000)
941 
            if orderObj is not None:
942 
                quantity += orderObj.count
943 
                amount += orderObj.value
944 
            else:
945 
                quantity +=0
946 
                amount +=0
947 
            newWeekCutOff = newWeekCutOff + oneDay
948 
            if newWeekCutOff > breakPoint:
949 
                weekWiseSaholicOrderMap[init * 1000] = __Order(quantity, amount)
950 
                break
17134 amit.gupta 951 
def populateYesterdayActiveUsers(hours=4):
952 
    con = Mongo.get_mongo_connection(DTR_DATA_HOST)
953 
    db = con.User
954 
    curtime = datetime.now()
955 
    fourhrsprev = curtime - timedelta(hours=hours)
956 
    pipeline = [{"$match":{"created":{"$lt":toTimeStamp(curtime)*1000, "$gt":toTimeStamp(fourhrsprev)*1000}}},
957 
                {"$group":{"_id":{"user_id":"$user_id","created":{"$subtract":["$created",{"$mod":["$created",86400000]}]}}}}]
958 
    alluserdates=db.browsinghistories.aggregate(pipeline)['result']
959 
    insertq = "insert ignore into daily_visitors values (%s, %s)"
960 
    conn = getDbConnection()
961 
    try:
962 
        for a in alluserdates:
963 
            cursor = conn.cursor()
964 
            # Execute the SQL command
965 
            # Fetch source id.
966 
            cursor.execute(insertq,(a['_id']['user_id'], utils.fromTimeStamp(int(a['_id']['created']/1000))))
967 
            conn.commit()
968 
    finally:
969 
        conn.close()
16964 manas 970 
 
971 
def populateSaholicMonthWiseMap1():
972 
    global newMonthCutOff
973 
    while(True):
974 
 
975 
        print newMonthCutOff *1000
976 
        a=str(to_py_date(newMonthCutOff*1000))
977 
        print 'Now ' + str(to_java_date(datetime.now()))
978 
        print "**********************"
979 
        if newMonthCutOff *1000 >= to_java_date(datetime.now()):
980 
            print "Breaking outer while"
981 
            break
982 
        init = newMonthCutOff
983 
        breakPoint = newMonthCutOff + (monthMap.get(a[5:7]) * oneDay)
984 
        print breakPoint
985 
        quantity, amount = 0 , 0
986 
        while(True):
987 
            print to_py_date(newMonthCutOff*1000)
988 
            print "weekCutOff ",newMonthCutOff
989 
            print "breakPoint ",breakPoint
990 
            orderObj = dateWiseSaholicOrderMap.get(newMonthCutOff * 1000)
991 
            if orderObj is None:
992 
                print "None for ", to_py_date(newMonthCutOff * 1000)
993 
                quantity += 0
994 
                amount += 0
995 
            if orderObj is not None:
996 
                quantity += orderObj.count
997 
                amount += orderObj.value
998 
            newMonthCutOff = newMonthCutOff + oneDay
999 
            counter=0
1000 
            print counter+1
1001 
            if newMonthCutOff > breakPoint:
1002 
                monthWiseSaholicOrderMap[init * 1000] = __Order(quantity, amount)
1003 
                print "Breaking inner while"
1004 
                break
1005 
 
1006 
 
15228 manas 1007 
def populateValidOrders():
1008 
    global order_ids
15244 manas 1009 
    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 1010 
    order_ids = list(zip(*allOrders)[0])
15244 manas 1011 
 
14772 kshitij.so 1012 
def main():
17134 amit.gupta 1013 
    populateYesterdayActiveUsers(24)
15228 manas 1014 
    populateValidOrders()
1015 
    populateOrderMap()
1016 
    populateWeekWiseMap1()
15551 manas 1017 
    populateMonthWiseMap1()
16964 manas 1018 
    populateSaholicOrderMap()
1019 
    populateSaholicWeekWiseMap1()
1020 
    populateSaholicMonthWiseMap1()
14772 kshitij.so 1021 
    generateDailyReport()
1022 
    generateWeeklyReport()
1023 
    generateMonthlyReport()
17171 amit.gupta 1024 
    sendmail(["manas.kapoor@shop2020.in","rajneesh.arora@saholic.com", "amit.gupta@shop2020.in"], "", TMP_FILE, SUBJECT)
17125 manas 1025 
    #sendmail(["manas.kapoor@shop2020.in"], "", TMP_FILE, SUBJECT)
15228 manas 1026 
 
16964 manas 1027 
def to_x_date(java_timestamp):
1028 
    try:
1029 
        date = datetime.fromtimestamp(java_timestamp / 1e3)
1030 
    except:
1031 
        return None
1032 
    return date.strftime('%Y-%m-%d')
15551 manas 1033 
 
14772 kshitij.so 1034 
if __name__ == '__main__':
17158 amit.gupta 1035 
    if len(sys.argv)==1:
17134 amit.gupta 1036 
        main()
1037 
    else:
17135 amit.gupta 1038 
        populateYesterdayActiveUsers(int(sys.argv[1]))