Subversion Repositories SmartDukaan

Rev

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