Subversion Repositories SmartDukaan

Rev

Rev 16973 | Rev 17032 | 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
17031 manas 105 
AND date(u.activation_time) > '2015-03-08'
106 
group by date(u.activation_time)
107 
order by date(u.activation_time)
14772 kshitij.so 108 
"""
109 
 
110 
DAU_QUERY="""
111 
SELECT count(distinct d.user_id)
15244 manas 112 
FROM daily_visitors d join users u WHERE u.id=d.user_id
113 
AND (LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
14772 kshitij.so 114 
AND date(d.visited) > '2015-03-08' group by visited ;
115 
"""
116 
 
15551 manas 117 
# DAB_QUERY="""
118 
# SELECT COUNT(DISTINCT o.user_id )
119 
# FROM  order_view o
120 
# JOIN users u ON u.id = o.user_id WHERE
121 
# (LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
122 
# AND (o.STATUS =  'ORDER_CREATED' OR o.STATUS ='DETAIL_CREATED')
123 
# AND date(o.created) > '2015-03-08'
124 
# group by date(o.created)
125 
# order by date(o.created)
126 
# """
14772 kshitij.so 127 
DAB_QUERY="""
15551 manas 128 
select count(DISTINCT s.user_id) from
129 
(SELECT created, id,user_id FROM  order_view where STATUS =  'ORDER_CREATED' OR STATUS ='DETAIL_CREATED'
130 
union  SELECT created, id,user_id FROM   flipkartorders where date(created)>'2015-03-22') s
131 
JOIN users u ON u.id = s.user_id WHERE
132 
(LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated = 1
133 
AND date(s.created) > '2015-03-08'
134 
GROUP by date(s.created)
135 
ORDER by date(s.created);
14772 kshitij.so 136 
"""
137 
 
138 
DTO_QUERY="""
15551 manas 139 
select count(*) from
140 
(SELECT created, id,user_id FROM  order_view where STATUS =  'ORDER_CREATED' OR STATUS ='DETAIL_CREATED'
141 
union  SELECT created, id,ifnull(user_id,139) FROM   flipkartorders where date(created)>'2015-03-22') s
142 
JOIN users u ON u.id = s.user_id WHERE
143 
(LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated = 1
144 
AND date(s.created) > '2015-03-08'
145 
GROUP by date(s.created)
146 
ORDER by date(s.created);
14772 kshitij.so 147 
"""
15551 manas 148 
# DTO_QUERY="""
149 
# SELECT date(o.created),COUNT(*)
150 
# FROM  order_view o
151 
# JOIN users u ON u.id = o.user_id WHERE
152 
# (LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
153 
# AND (o.STATUS =  'ORDER_CREATED' OR o.STATUS ='DETAIL_CREATED')
154 
# AND date(o.created) > '2015-03-08'
155 
# GROUP BY DATE(o.created)
156 
# order by date(o.created)
157 
#"""
14772 kshitij.so 158 
 
159 
MNRU_QUERY="""
15244 manas 160 
SELECT count(*) FROM users u WHERE
161 
(LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
17031 manas 162 
AND date(u.activation_time) > '2015-03-08'
163 
group by month(u.activation_time)
164 
order by month(u.activation_time)
14772 kshitij.so 165 
"""
166 
 
167 
MAU_QUERY="""
168 
SELECT count(distinct d.user_id)
15244 manas 169 
FROM daily_visitors d join users u where u.id=d.user_id AND
170 
(LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
14772 kshitij.so 171 
AND date(d.visited) > '2015-03-08'
172 
group by month(d.visited)
173 
order by month(d.visited);
174 
"""
175 
 
15551 manas 176 
# MAB_QUERY="""
177 
# SELECT COUNT(DISTINCT o.user_id )
178 
# FROM  order_view o
179 
# JOIN users u ON u.id = o.user_id where
180 
# (LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
181 
# AND (o.STATUS =  'ORDER_CREATED' OR o.STATUS ='DETAIL_CREATED')
182 
# AND date(o.created) > '2015-03-08'
183 
# group by month(o.created)
184 
# order by month(o.created);
185 
# """
186 
 
187 
# MTO_QUERY="""
188 
# SELECT COUNT( *)
189 
# FROM  order_view o
190 
# JOIN users u ON u.id = o.user_id where
191 
# (LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
192 
# AND (o.STATUS =  'ORDER_CREATED' OR o.STATUS ='DETAIL_CREATED')
193 
# AND date(o.created) > '2015-03-08'
194 
# GROUP BY MONTH(o.created)
195 
# order by month(o.created);
196 
# """
197 
 
14772 kshitij.so 198 
MAB_QUERY="""
15551 manas 199 
select count(DISTINCT s.user_id) from
200 
(SELECT created, id,user_id FROM  order_view where STATUS =  'ORDER_CREATED' OR STATUS ='DETAIL_CREATED'
201 
union  SELECT created, id,user_id FROM   flipkartorders where date(created)>'2015-03-22') s
202 
JOIN users u ON u.id = s.user_id WHERE   (LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
203 
AND date(s.created) > '2015-03-08'
204 
GROUP by month(s.created)
205 
order by month(s.created);
14772 kshitij.so 206 
"""
207 
MTO_QUERY="""
15551 manas 208 
select count(*) from
209 
(SELECT created, id,user_id FROM  order_view where STATUS =  'ORDER_CREATED' OR STATUS ='DETAIL_CREATED'
210 
union  SELECT created, id,ifnull(user_id,139) FROM   flipkartorders where date(created)>'2015-03-22') s
211 
JOIN users u ON u.id = s.user_id WHERE   (LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
212 
AND date(s.created) > '2015-03-08'
213 
GROUP by month(s.created)
214 
order by month(s.created);
14772 kshitij.so 215 
"""
216 
 
217 
WNRU_QUERY="""
218 
SELECT COUNT(*)
219 
FROM users u WHERE
15244 manas 220 
(LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
17031 manas 221 
AND date(u.activation_time) > '2015-03-08'
222 
GROUP BY WEEK(date(u.activation_time))
223 
ORDER BY WEEK(date(u.activation_time))
14772 kshitij.so 224 
 
225 
"""
226 
WAU_QUERY="""
227 
SELECT  COUNT(distinct d.user_id) AS total
15244 manas 228 
FROM  daily_visitors d join users u where u.id=d.user_id AND
229 
(LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
14772 kshitij.so 230 
AND date(d.visited) > '2015-03-08'
231 
GROUP BY WEEK(d.visited)
232 
ORDER BY WEEK(d.visited)
233 
"""
234 
 
235 
 
15551 manas 236 
# WAB_QUERY="""
237 
# SELECT COUNT( DISTINCT o.user_id )
238 
# FROM  order_view o
239 
# JOIN users u ON u.id = o.user_id WHERE
240 
# (LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
241 
# AND (o.STATUS =  'ORDER_CREATED' OR o.STATUS ='DETAIL_CREATED')
242 
# AND date(o.created) > '2015-03-08'
243 
# GROUP BY WEEK(date(o.created))
244 
# ORDER BY WEEK(date(o.created))
245 
# """
246 
 
247 
# WTO_QUERY="""
248 
# SELECT COUNT(*)
249 
# FROM  order_view o
250 
# JOIN users u ON u.id = o.user_id WHERE
251 
# (LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
252 
# AND (o.STATUS =  'ORDER_CREATED' OR o.STATUS ='DETAIL_CREATED')
253 
# AND date(o.created) > '2015-03-08'
254 
# GROUP BY WEEK(date(o.created))
255 
# ORDER BY WEEK(date(o.created))
256 
# """
257 
 
14772 kshitij.so 258 
WAB_QUERY="""
15551 manas 259 
select count(distinct user_id) from
260 
(SELECT created, id,user_id FROM  order_view where STATUS =  'ORDER_CREATED' OR STATUS ='DETAIL_CREATED'
261 
union  SELECT created, id,user_id FROM   flipkartorders where date(created)>'2015-03-22') s
262 
JOIN users u ON u.id = s.user_id WHERE   (LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
263 
AND date(s.created) > '2015-03-08'
264 
GROUP by week(s.created)
265 
order by week(s.created);
14772 kshitij.so 266 
"""
267 
 
268 
WTO_QUERY="""
15551 manas 269 
select count(*) from
270 
(SELECT created, id,user_id FROM  order_view where STATUS =  'ORDER_CREATED' OR STATUS ='DETAIL_CREATED'
271 
union  SELECT created, id,ifnull(user_id,139) FROM   flipkartorders where date(created)>'2015-03-22') s
272 
JOIN users u ON u.id = s.user_id WHERE   (LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
273 
AND date(s.created) > '2015-03-08'
274 
GROUP by week(s.created)
275 
order by week(s.created);
14772 kshitij.so 276 
"""
277 
 
15551 manas 278 
DQ_QUERY="""
279 
select sum(quantity) from flipkartorders where date(created)='%s';
280 
"""
281 
DV_QUERY="""
282 
select sum(quantity*price) from flipkartorders where date(created)='%s';
283 
"""
284 
WQ_QUERY="""
285 
select sum(quantity) from flipkartorders where date(created) between '%s' and '%s';
286 
"""
287 
WV_QUERY="""
288 
select sum(quantity*price) from flipkartorders where date(created) between '%s' and '%s';
289 
"""
14772 kshitij.so 290 
 
291 
date_format = xlwt.XFStyle()
292 
date_format.num_format_str = 'yyyy/mm/dd'
293 
 
294 
datetime_format = xlwt.XFStyle()
295 
datetime_format.num_format_str = 'yyyy/mm/dd HH:MM AM/PM'
296 
 
297 
default_format = xlwt.XFStyle()
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)
17031 manas 601 
 
14772 kshitij.so 602 
def generateWeeklyReport():
603 
    weekSql = WEEK_QUERY
604 
    wnruSql = WNRU_QUERY
605 
    wauSql = WAU_QUERY
606 
    wabSql = WAB_QUERY
607 
    wtoSql = WTO_QUERY
608 
 
609 
    conn = getDbConnection()
610 
 
611 
    cursor = conn.cursor()
612 
 
613 
    cursor.execute(weekSql)
614 
    result = cursor.fetchall()
615 
    rb = open_workbook(TMP_FILE)
616 
    wb = copy(rb)
617 
    worksheet = workbook.add_sheet("Weekly")
618 
    boldStyle = xlwt.XFStyle()
619 
    f = xlwt.Font()
620 
    f.bold = True
621 
    boldStyle.font = f
622 
    column = 0
623 
    row = 0
624 
    sumdata=17
625 
    worksheet.write(row, 0, 'Week', boldStyle)
626 
    worksheet.write(row, 1, 'WTRU', boldStyle)
627 
    worksheet.write(row, 2, 'WNRU', boldStyle)
628 
    worksheet.write(row, 3, 'WAU', boldStyle)
629 
    worksheet.write(row, 4, 'WAB', boldStyle)
630 
    worksheet.write(row, 5, 'WTO', boldStyle)
16933 manas 631 
    worksheet.write(row, 6, 'WVOL', boldStyle)
15228 manas 632 
    worksheet.write(row, 7, 'WTV', boldStyle)
16964 manas 633 
    worksheet.write(row, 8, 'WOWNVOL', boldStyle)
634 
    worksheet.write(row, 9, 'WOWNVAL', boldStyle)
14772 kshitij.so 635 
 
636 
    for r in result:
637 
        row += 1
638 
        column = 0
639 
        for data in r :
640 
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
641 
            column += 1
642 
 
643 
    row = 0
644 
    cursor.execute(wnruSql)
645 
    result = cursor.fetchall()
646 
    for r in result:
647 
        row += 1
648 
        column = 1
649 
        for data in r :
650 
            sumdata=sumdata+data
651 
            worksheet.write(row, column, int(sumdata) if type(sumdata) is float else sumdata, date_format if type(sumdata) is date else default_format)
652 
            column += 1
653 
 
654 
    row = 0
655 
    cursor.execute(wnruSql)
656 
    result = cursor.fetchall()
657 
    for r in result:
658 
        row += 1
659 
        column = 2
660 
        for data in r :
661 
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
662 
            column += 1
663 
 
664 
    row = 0
665 
    cursor.execute(wauSql)
666 
    result = cursor.fetchall()
667 
 
668 
    for r in result:
669 
        row += 1
670 
        column = 3
671 
        for data in r :
672 
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
673 
            column += 1
674 
 
675 
    row = 0
676 
    cursor.execute(wabSql)
677 
    result = cursor.fetchall()
678 
 
679 
    for r in result:
680 
        row += 1
681 
        column = 4
682 
        for data in r :
683 
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
684 
            column += 1
685 
 
686 
    row = 0
687 
    cursor.execute(wtoSql)
688 
    result = cursor.fetchall()
689 
 
690 
    for r in result:
691 
        row += 1
692 
        column = 5
693 
        for data in r :
694 
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
695 
            column += 1
15228 manas 696 
 
697 
    row = 0
698 
    for x in sorted(weekWiseOrderMap):
699 
        row += 1
700 
        column = 6
15551 manas 701 
        d = str(datetime.fromtimestamp(x/1000.0))
702 
        nextday=oneDay*7*1000+x
703 
        dnext = str(datetime.fromtimestamp(nextday/1000.0))
704 
        dq=WQ_QUERY % (d[:10],dnext[:10])
705 
        cursor.execute(dq)
706 
        result = cursor.fetchall()
707 
        for r in result:
708 
            quantity= r[0]
709 
            if quantity is None:
710 
                quantity=0
711 
        worksheet.write(row,column,(weekWiseOrderMap.get(x).count)+quantity)
15228 manas 712 
        column+=1
713 
    row = 0
714 
    for x in sorted(weekWiseOrderMap):
715 
        row += 1
716 
        column = 7
15551 manas 717 
        d = str(datetime.fromtimestamp(x/1000.0))
718 
        nextday=oneDay*7*1000+x
719 
        dnext = str(datetime.fromtimestamp(nextday/1000.0))
720 
        dq=WV_QUERY % (d[:10],dnext[:10])
721 
        cursor.execute(dq)
722 
        result = cursor.fetchall()
723 
        for r in result:
724 
            quantity= r[0]
725 
            if quantity is None:
726 
                quantity=0
727 
        worksheet.write(row,column,(weekWiseOrderMap.get(x).value)+float(quantity))
15228 manas 728 
        column+=1
16964 manas 729 
 
730 
    row=0
731 
    for x in sorted(weekWiseSaholicOrderMap):
732 
        row += 1
733 
        column = 8
734 
        worksheet.write(row,column,weekWiseSaholicOrderMap.get(x).count)
735 
        column+=1
736 
 
737 
    row=0
738 
    for x in sorted(weekWiseSaholicOrderMap):
739 
        row += 1
740 
        column = 9
741 
        worksheet.write(row,column,weekWiseSaholicOrderMap.get(x).value)
742 
        column+=1
743 
 
14772 kshitij.so 744 
def sendmail(email, message, fileName, title):
745 
    if email == "":
746 
        return
747 
    mailServer = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)
748 
    mailServer.ehlo()
749 
    mailServer.starttls()
750 
    mailServer.ehlo()
751 
 
752 
    # Create the container (outer) email message.
753 
    msg = MIMEMultipart()
754 
    msg['Subject'] = title
755 
    msg.preamble = title
756 
    html_msg = MIMEText(message, 'html')
757 
    msg.attach(html_msg)
758 
 
759 
    fileMsg = MIMEBase('application', 'vnd.ms-excel')
760 
    fileMsg.set_payload(file(TMP_FILE).read())
761 
    encoders.encode_base64(fileMsg)
762 
    fileMsg.add_header('Content-Disposition', 'attachment;filename=' + fileName)
763 
    msg.attach(fileMsg)
14805 kshitij.so 764 
 
17031 manas 765 
    #MAILTO = ['manas.kapoor@saholic.com','rajneesh.arora@saholic.com']
766 
    MAILTO = ['manas.kapoor@saholic.com']
14772 kshitij.so 767 
    mailServer.login(SENDER, PASSWORD)
768 
    mailServer.sendmail(PASSWORD, MAILTO, msg.as_string())
769 
 
15228 manas 770 
class __Order:
771 
 
772 
    def __init__(self, count, value):
773 
 
774 
        self.count = count
775 
        self.value = value
776 
 
777 
def get_mongo_connection(host='localhost', port=27017):
778 
    global con
779 
    if con is None:
780 
        print "Establishing connection %s host and port %d" %(host,port)
781 
        try:
782 
            con = pymongo.MongoClient(host, port)
783 
        except Exception, e:
784 
            print e
785 
            return None
786 
    return con
787 
 
788 
def populateOrderMap():
789 
    global dateWiseOrderMap
16931 manas 790 
    allOrders = get_mongo_connection().Dtr.merchantOrder.find({'createdOnInt':{"$gte":cutOff},'storeId':{"$in":[1,3,4,5,6]}}).sort([('createdOnInt',pymongo.ASCENDING)])
15228 manas 791 
    for orders in allOrders:
792 
        if orders.get('orderId') not in order_ids:
793 
            continue
794 
        cdate = ((to_py_date(orders.get('createdOnInt') * 1000)))
795 
        millisec = to_java_date(datetime(cdate.year, cdate.month, cdate.day))
796 
        if dateWiseOrderMap.has_key(millisec):
797 
            orderObj = dateWiseOrderMap.get(millisec)
798 
            q, c = getSubOrderQuantity(orders.get('subOrders'))
799 
            orderObj.count += q
800 
            orderObj.value += c
801 
        else:
802 
            orderObj = __Order(None, None)
803 
            q, c = getSubOrderQuantity(orders.get('subOrders'))
804 
            orderObj.count = q
805 
            orderObj.value = c
806 
            dateWiseOrderMap[millisec] = orderObj
807 
 
15551 manas 808 
 
809 
 
15228 manas 810 
def getSubOrderQuantity(subOrders):
811 
    q = 0
812 
    c = 0
813 
    if subOrders is None:
814 
        return q, c
815 
    for subOrder in subOrders:
816 
        q = q + int(subOrder.get('quantity'))
817 
        try:
818 
            c = c + float(subOrder.get('amountPaid').encode('utf-8'))
819 
        except AttributeError:
820 
            c = c + float(subOrder.get('amountPaid'))
821 
    return q, c
822 
 
823 
def populateWeekWiseMap():
824 
    global weekCutOff
825 
    while(True):
826 
        quantity, amount = 0 , 0
827 
        for i in xrange(weekCutOff , (weekCutOff * 7)+1):
828 
            orderObj = dateWiseOrderMap.get(i*1000)
829 
            quantity += orderObj.count
830 
            amount += orderObj.value
831 
            i = i + oneDay
832 
        weekWiseOrderMap[weekCutOff * 1000] = __Order(quantity, amount)
833 
        weekCutOff = weekCutOff * 1000 * 7 + oneDay
834 
        if weekCutOff >= to_py_date(datetime.now()):
835 
            break
836 
 
837 
def populateWeekWiseMap1():
838 
    global weekCutOff
839 
    while(True):
840 
        if weekCutOff *1000 >= to_java_date(datetime.now()):
841 
            break
842 
        init = weekCutOff
843 
        breakPoint = weekCutOff + (6 * oneDay)
844 
        quantity, amount = 0 , 0
845 
        while(True):
846 
            orderObj = dateWiseOrderMap.get(weekCutOff * 1000)
847 
            if orderObj is not None:
848 
                quantity += orderObj.count
849 
                amount += orderObj.value
850 
            weekCutOff = weekCutOff + oneDay
851 
            if weekCutOff > breakPoint:
852 
                weekWiseOrderMap[init * 1000] = __Order(quantity, amount)
853 
                break
854 
 
855 
def populateMonthWiseMap():
856 
    global monthCutOff
857 
    while(True):
858 
        quantity, amount = 0 , 0
859 
        for i in xrange(monthCutOff , (monthCutOff * 30)+1):
860 
            orderObj = dateWiseOrderMap.get(i*1000)
861 
            quantity += orderObj.count
862 
            amount += orderObj.value
863 
            i = i + oneDay
864 
        weekWiseOrderMap[monthCutOff * 1000] = __Order(quantity, amount)
865 
        monthCutOff = monthCutOff * 1000 * 30 + oneDay
866 
        if monthCutOff >= to_py_date(datetime.now()):
867 
            break
868 
 
869 
def populateMonthWiseMap1():
15551 manas 870 
    global monthCutOff
15228 manas 871 
    while(True):
15551 manas 872 
 
873 
        print monthCutOff *1000
874 
        a=str(to_py_date(monthCutOff*1000))
875 
        print 'Now ' + str(to_java_date(datetime.now()))
15228 manas 876 
        print "**********************"
15551 manas 877 
        if monthCutOff *1000 >= to_java_date(datetime.now()):
15228 manas 878 
            print "Breaking outer while"
879 
            break
15551 manas 880 
        init = monthCutOff
881 
        breakPoint = monthCutOff + (monthMap.get(a[5:7]) * oneDay)
882 
        print breakPoint
15228 manas 883 
        quantity, amount = 0 , 0
884 
        while(True):
15551 manas 885 
            print to_py_date(monthCutOff*1000)
886 
            print "weekCutOff ",monthCutOff
15228 manas 887 
            print "breakPoint ",breakPoint
15551 manas 888 
            orderObj = dateWiseOrderMap.get(monthCutOff * 1000)
15228 manas 889 
            if orderObj is None:
15551 manas 890 
                print "None for ", to_py_date(monthCutOff * 1000)
15228 manas 891 
            if orderObj is not None:
892 
                quantity += orderObj.count
893 
                amount += orderObj.value
15551 manas 894 
            monthCutOff = monthCutOff + oneDay
895 
            counter=0
896 
            print counter+1
897 
            if monthCutOff > breakPoint:
898 
                monthWiseOrderMap[init * 1000] = __Order(quantity, amount)
15228 manas 899 
                print "Breaking inner while"
900 
                break
16964 manas 901 
 
902 
def populateSaholicOrderMap():
903 
    global dateWiseSaholicOrderMap
904 
    allOrders = get_mongo_connection().Dtr.merchantOrder.find({'createdOnInt':{"$gte":cutOff},'storeId':{"$in":[4]}}).sort([('createdOnInt',pymongo.ASCENDING)])
905 
    for orders in allOrders:
906 
        if orders.get('orderId') not in order_ids:
907 
            continue
908 
        cdate = ((to_py_date(orders.get('createdOnInt') * 1000)))
909 
        millisec = to_java_date(datetime(cdate.year, cdate.month, cdate.day))
910 
        if dateWiseSaholicOrderMap.has_key(millisec):
911 
            orderObj = dateWiseSaholicOrderMap.get(millisec)
912 
            q, c = getSaholicSubOrderQuantity(orders.get('subOrders'))
913 
            orderObj.count += q
914 
            orderObj.value += c
915 
        else:
916 
            orderObj = __Order(None, None)
917 
            q, c = getSaholicSubOrderQuantity(orders.get('subOrders'))
918 
            orderObj.count = q
919 
            orderObj.value = c
920 
            dateWiseSaholicOrderMap[millisec] = orderObj
15228 manas 921 
 
16964 manas 922 
 
923 
 
924 
def getSaholicSubOrderQuantity(subOrders):
925 
    q = 0
926 
    c = 0
927 
    if subOrders is None:
928 
        return q, c
929 
    for subOrder in subOrders:
930 
        q = q + int(subOrder.get('quantity'))
931 
        try:
932 
            c = c + float(subOrder.get('amountPaid').encode('utf-8'))
933 
        except AttributeError:
934 
            c = c + float(subOrder.get('amountPaid'))
935 
    return q, c
936 
 
937 
def populateSaholicWeekWiseMap1():
938 
    global newWeekCutOff
939 
    while(True):
940 
        if newWeekCutOff *1000 >= to_java_date(datetime.now()):
941 
            break
942 
        init = newWeekCutOff
943 
        breakPoint = newWeekCutOff + (6 * oneDay)
944 
        quantity, amount = 0 , 0
945 
        while(True):
946 
            orderObj = dateWiseSaholicOrderMap.get(newWeekCutOff * 1000)
947 
            if orderObj is not None:
948 
                quantity += orderObj.count
949 
                amount += orderObj.value
950 
            else:
951 
                quantity +=0
952 
                amount +=0
953 
            newWeekCutOff = newWeekCutOff + oneDay
954 
            if newWeekCutOff > breakPoint:
955 
                weekWiseSaholicOrderMap[init * 1000] = __Order(quantity, amount)
956 
                break
957 
 
958 
def populateSaholicMonthWiseMap1():
959 
    global newMonthCutOff
960 
    while(True):
961 
 
962 
        print newMonthCutOff *1000
963 
        a=str(to_py_date(newMonthCutOff*1000))
964 
        print 'Now ' + str(to_java_date(datetime.now()))
965 
        print "**********************"
966 
        if newMonthCutOff *1000 >= to_java_date(datetime.now()):
967 
            print "Breaking outer while"
968 
            break
969 
        init = newMonthCutOff
970 
        breakPoint = newMonthCutOff + (monthMap.get(a[5:7]) * oneDay)
971 
        print breakPoint
972 
        quantity, amount = 0 , 0
973 
        while(True):
974 
            print to_py_date(newMonthCutOff*1000)
975 
            print "weekCutOff ",newMonthCutOff
976 
            print "breakPoint ",breakPoint
977 
            orderObj = dateWiseSaholicOrderMap.get(newMonthCutOff * 1000)
978 
            if orderObj is None:
979 
                print "None for ", to_py_date(newMonthCutOff * 1000)
980 
                quantity += 0
981 
                amount += 0
982 
            if orderObj is not None:
983 
                quantity += orderObj.count
984 
                amount += orderObj.value
985 
            newMonthCutOff = newMonthCutOff + oneDay
986 
            counter=0
987 
            print counter+1
988 
            if newMonthCutOff > breakPoint:
989 
                monthWiseSaholicOrderMap[init * 1000] = __Order(quantity, amount)
990 
                print "Breaking inner while"
991 
                break
992 
 
993 
 
15228 manas 994 
def populateValidOrders():
995 
    global order_ids
15244 manas 996 
    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 997 
    order_ids = list(zip(*allOrders)[0])
15244 manas 998 
 
14772 kshitij.so 999 
def main():
15228 manas 1000 
    populateValidOrders()
1001 
    populateOrderMap()
1002 
    populateWeekWiseMap1()
15551 manas 1003 
    populateMonthWiseMap1()
16964 manas 1004 
    populateSaholicOrderMap()
1005 
    populateSaholicWeekWiseMap1()
1006 
    populateSaholicMonthWiseMap1()
14772 kshitij.so 1007 
    generateDailyReport()
1008 
    generateWeeklyReport()
1009 
    generateMonthlyReport()
17031 manas 1010 
    #sendmail(["manas.kapoor@shop2020.in","rajneesh.arora@saholic.com"], "", TMP_FILE, SUBJECT)
1011 
    sendmail(["manas.kapoor@shop2020.in"], "", TMP_FILE, SUBJECT)
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()