Subversion Repositories SmartDukaan

Rev

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