Subversion Repositories SmartDukaan

Rev

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

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