Subversion Repositories SmartDukaan

Rev

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