Subversion Repositories SmartDukaan

Rev

Rev 15244 | Rev 16931 | Go to most recent revision | Show entire file | Ignore whitespace | Details | Blame | Last modification | View Log | RSS feed

Rev 15244 Rev 15551
Line 45... Line 45...
45 
weekCutOff = 1425839400
 45 
weekCutOff = 1425839400
46 
#weekCutOff = 1425234600
 46 
#weekCutOff = 1425234600
47 
 
 47 
 
48 
DataService.initialize()
 48 
DataService.initialize()
49 
 
 49 
 
- 
 
 50 
monthMap = {  '01':30,
- 
 
 51 
            '02':28,
- 
 
 52 
            '03':30,
- 
 
 53 
            '04':29,
- 
 
 54 
            '05':30,
- 
 
 55 
            '06':29,
- 
 
 56 
            '07':30,
- 
 
 57 
            '08':30,
- 
 
 58 
            '09':29,
- 
 
 59 
            '10':30,
- 
 
 60 
            '11':29,
- 
 
 61 
            '12':30
- 
 
 62 
        }
50 
 
 63 
 
51 
# KEY NAMES
 64 
# KEY NAMES
52 
SENDER = "cnc.center@shop2020.in"
 65 
SENDER = "cnc.center@shop2020.in"
53 
PASSWORD = "5h0p2o2o"
 66 
PASSWORD = "5h0p2o2o"
54 
SUBJECT = "User Activity Report for" + date.today().isoformat()
 67 
SUBJECT = "User Activity Report for " + date.today().isoformat()
55 
SMTP_SERVER = "smtp.gmail.com"
 68 
SMTP_SERVER = "smtp.gmail.com"
56 
SMTP_PORT = 587
69 
SMTP_PORT = 587
57 
 
 70 
 
58 
DATE_QUERY="""
 71 
DATE_QUERY="""
59 
SELECT date(d.visited) from daily_visitors d
72 
SELECT date(d.visited) from daily_visitors d
Line 93... Line 106...
93 
FROM daily_visitors d join users u WHERE u.id=d.user_id
106 
FROM daily_visitors d join users u WHERE u.id=d.user_id
94 
AND (LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
 107 
AND (LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
95 
AND date(d.visited) > '2015-03-08' group by visited ;
 108 
AND date(d.visited) > '2015-03-08' group by visited ;
96 
"""
 109 
"""
97 
 
 110 
 
- 
 
 111 
# DAB_QUERY="""
- 
 
 112 
# SELECT COUNT(DISTINCT o.user_id )
- 
 
 113 
# FROM  order_view o
- 
 
 114 
# JOIN users u ON u.id = o.user_id WHERE
- 
 
 115 
# (LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
- 
 
 116 
# AND (o.STATUS =  'ORDER_CREATED' OR o.STATUS ='DETAIL_CREATED')
- 
 
 117 
# AND date(o.created) > '2015-03-08'
- 
 
 118 
# group by date(o.created)
- 
 
 119 
# order by date(o.created)
- 
 
 120 
# """
98 
DAB_QUERY="""
 121 
DAB_QUERY="""
99 
SELECT COUNT(DISTINCT o.user_id )
122 
select count(DISTINCT s.user_id) from
100 
FROM  order_view o
 123 
(SELECT created, id,user_id FROM  order_view where STATUS =  'ORDER_CREATED' OR STATUS ='DETAIL_CREATED'
- 
 
 124 
union  SELECT created, id,user_id FROM   flipkartorders where date(created)>'2015-03-22') s
101 
JOIN users u ON u.id = o.user_id WHERE
125 
JOIN users u ON u.id = s.user_id WHERE
102 
(LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
 126 
(LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated = 1
103 
AND (o.STATUS =  'ORDER_CREATED' OR o.STATUS ='DETAIL_CREATED')
 - 
 
104 
AND date(o.created) > '2015-03-08'
127 
AND date(s.created) > '2015-03-08'
105 
group by date(o.created)
 128 
GROUP by date(s.created)
106 
order by date(o.created)
 129 
ORDER by date(s.created);
107 
"""
 130 
"""
108 
 
 131 
 
109 
DTO_QUERY="""
 132 
DTO_QUERY="""
- 
 
 133 
select count(*) from
- 
 
 134 
(SELECT created, id,user_id FROM  order_view where STATUS =  'ORDER_CREATED' OR STATUS ='DETAIL_CREATED'
- 
 
 135 
union  SELECT created, id,ifnull(user_id,139) FROM   flipkartorders where date(created)>'2015-03-22') s
- 
 
 136 
JOIN users u ON u.id = s.user_id WHERE
- 
 
 137 
(LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated = 1
- 
 
 138 
AND date(s.created) > '2015-03-08'
- 
 
 139 
GROUP by date(s.created)
- 
 
 140 
ORDER by date(s.created);
- 
 
 141 
"""
- 
 
 142 
# DTO_QUERY="""
110 
SELECT COUNT( *)
143 
# SELECT date(o.created),COUNT(*)
111 
FROM  order_view o
 144 
# FROM  order_view o
112 
JOIN users u ON u.id = o.user_id WHERE
145 
# JOIN users u ON u.id = o.user_id WHERE
113 
(LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
 146 
# (LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
114 
AND (o.STATUS =  'ORDER_CREATED' OR o.STATUS ='DETAIL_CREATED')
 147 
# AND (o.STATUS =  'ORDER_CREATED' OR o.STATUS ='DETAIL_CREATED')
115 
AND date(o.created) > '2015-03-08'
148 
# AND date(o.created) > '2015-03-08'
116 
GROUP BY DATE(o.created)
 149 
# GROUP BY DATE(o.created)
117 
order by date(o.created)
150 
# order by date(o.created)
118 
"""
 151 
#"""
119 
 
 152 
 
120 
MNRU_QUERY="""
 153 
MNRU_QUERY="""
121 
SELECT count(*) FROM users u WHERE
154 
SELECT count(*) FROM users u WHERE
122 
(LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
 155 
(LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
123 
AND date(u.created) > '2015-03-08'
156 
AND date(u.created) > '2015-03-08'
Line 132... Line 165...
132 
AND date(d.visited) > '2015-03-08'
165 
AND date(d.visited) > '2015-03-08'
133 
group by month(d.visited)
 166 
group by month(d.visited)
134 
order by month(d.visited);
167 
order by month(d.visited);
135 
"""
 168 
"""
136 
 
 169 
 
- 
 
 170 
# MAB_QUERY="""
- 
 
 171 
# SELECT COUNT(DISTINCT o.user_id )
- 
 
 172 
# FROM  order_view o
- 
 
 173 
# JOIN users u ON u.id = o.user_id where
- 
 
 174 
# (LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
- 
 
 175 
# AND (o.STATUS =  'ORDER_CREATED' OR o.STATUS ='DETAIL_CREATED')
- 
 
 176 
# AND date(o.created) > '2015-03-08'
- 
 
 177 
# group by month(o.created)
- 
 
 178 
# order by month(o.created);
- 
 
 179 
# """
- 
 
 180 
 
- 
 
 181 
# MTO_QUERY="""
- 
 
 182 
# SELECT COUNT( *)
- 
 
 183 
# FROM  order_view o
- 
 
 184 
# JOIN users u ON u.id = o.user_id where
- 
 
 185 
# (LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
- 
 
 186 
# AND (o.STATUS =  'ORDER_CREATED' OR o.STATUS ='DETAIL_CREATED')
- 
 
 187 
# AND date(o.created) > '2015-03-08'
- 
 
 188 
# GROUP BY MONTH(o.created)
- 
 
 189 
# order by month(o.created);
- 
 
 190 
# """
- 
 
 191 
 
137 
MAB_QUERY="""
 192 
MAB_QUERY="""
138 
SELECT COUNT(DISTINCT o.user_id )
193 
select count(DISTINCT s.user_id) from
139 
FROM  order_view o
 194 
(SELECT created, id,user_id FROM  order_view where STATUS =  'ORDER_CREATED' OR STATUS ='DETAIL_CREATED'
140 
JOIN users u ON u.id = o.user_id where
 195 
union  SELECT created, id,user_id FROM   flipkartorders where date(created)>'2015-03-22') s
141 
(LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
 196 
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
142 
AND (o.STATUS =  'ORDER_CREATED' OR o.STATUS ='DETAIL_CREATED')
 - 
 
143 
AND date(o.created) > '2015-03-08'
197 
AND date(s.created) > '2015-03-08'
144 
group by month(o.created)
 198 
GROUP by month(s.created)
145 
order by month(o.created);
199 
order by month(s.created);
146 
"""
 200 
"""
147 
 
 - 
 
148 
MTO_QUERY="""
 201 
MTO_QUERY="""
149 
SELECT COUNT( *)
202 
select count(*) from
150 
FROM  order_view o
 203 
(SELECT created, id,user_id FROM  order_view where STATUS =  'ORDER_CREATED' OR STATUS ='DETAIL_CREATED'
151 
JOIN users u ON u.id = o.user_id where
 204 
union  SELECT created, id,ifnull(user_id,139) FROM   flipkartorders where date(created)>'2015-03-22') s
152 
(LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
 205 
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
153 
AND (o.STATUS =  'ORDER_CREATED' OR o.STATUS ='DETAIL_CREATED')
 - 
 
154 
AND date(o.created) > '2015-03-08'
206 
AND date(s.created) > '2015-03-08'
155 
GROUP BY MONTH(o.created)
 207 
GROUP by month(s.created)
156 
order by month(o.created);
208 
order by month(s.created);
157 
"""
 209 
"""
158 
 
 210 
 
159 
WNRU_QUERY="""
 211 
WNRU_QUERY="""
160 
SELECT COUNT(*)
 212 
SELECT COUNT(*)
161 
FROM users u WHERE
213 
FROM users u WHERE
Line 173... Line 225...
173 
GROUP BY WEEK(d.visited)
 225 
GROUP BY WEEK(d.visited)
174 
ORDER BY WEEK(d.visited)
 226 
ORDER BY WEEK(d.visited)
175 
"""
 227 
"""
176 
 
 228 
 
177 
 
 229 
 
- 
 
 230 
# WAB_QUERY="""
- 
 
 231 
# SELECT COUNT( DISTINCT o.user_id )
- 
 
 232 
# FROM  order_view o
- 
 
 233 
# JOIN users u ON u.id = o.user_id WHERE
- 
 
 234 
# (LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
- 
 
 235 
# AND (o.STATUS =  'ORDER_CREATED' OR o.STATUS ='DETAIL_CREATED')
- 
 
 236 
# AND date(o.created) > '2015-03-08'
- 
 
 237 
# GROUP BY WEEK(date(o.created))
- 
 
 238 
# ORDER BY WEEK(date(o.created))
- 
 
 239 
# """
- 
 
 240 
 
- 
 
 241 
# WTO_QUERY="""
- 
 
 242 
# SELECT COUNT(*)
- 
 
 243 
# FROM  order_view o
- 
 
 244 
# JOIN users u ON u.id = o.user_id WHERE
- 
 
 245 
# (LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
- 
 
 246 
# AND (o.STATUS =  'ORDER_CREATED' OR o.STATUS ='DETAIL_CREATED')
- 
 
 247 
# AND date(o.created) > '2015-03-08'
- 
 
 248 
# GROUP BY WEEK(date(o.created))
- 
 
 249 
# ORDER BY WEEK(date(o.created))
- 
 
 250 
# """
- 
 
 251 
 
178 
WAB_QUERY="""
 252 
WAB_QUERY="""
179 
SELECT COUNT( DISTINCT o.user_id )
253 
select count(distinct user_id) from
180 
FROM  order_view o
 254 
(SELECT created, id,user_id FROM  order_view where STATUS =  'ORDER_CREATED' OR STATUS ='DETAIL_CREATED'
181 
JOIN users u ON u.id = o.user_id WHERE
 255 
union  SELECT created, id,user_id FROM   flipkartorders where date(created)>'2015-03-22') s
182 
(LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
 256 
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
183 
AND (o.STATUS =  'ORDER_CREATED' OR o.STATUS ='DETAIL_CREATED')
 - 
 
184 
AND date(o.created) > '2015-03-08'
257 
AND date(s.created) > '2015-03-08'
185 
GROUP BY WEEK(date(o.created))
 258 
GROUP by week(s.created)
186 
ORDER BY WEEK(date(o.created))
 259 
order by week(s.created);
187 
"""
 260 
"""
188 
 
 261 
 
189 
WTO_QUERY="""
 262 
WTO_QUERY="""
190 
SELECT COUNT(*)
 263 
select count(*) from
191 
FROM  order_view o
 264 
(SELECT created, id,user_id FROM  order_view where STATUS =  'ORDER_CREATED' OR STATUS ='DETAIL_CREATED'
192 
JOIN users u ON u.id = o.user_id WHERE
265 
union  SELECT created, id,ifnull(user_id,139) FROM   flipkartorders where date(created)>'2015-03-22') s
193 
(LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
 266 
JOIN users u ON u.id = s.user_id WHERE   (LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
194 
AND (o.STATUS =  'ORDER_CREATED' OR o.STATUS ='DETAIL_CREATED')
 - 
 
195 
AND date(o.created) > '2015-03-08'
267 
AND date(s.created) > '2015-03-08'
196 
GROUP BY WEEK(date(o.created))
 268 
GROUP by week(s.created)
197 
ORDER BY WEEK(date(o.created))
 269 
order by week(s.created);
- 
 
 270 
"""
- 
 
 271 
 
- 
 
 272 
DQ_QUERY="""
- 
 
 273 
select sum(quantity) from flipkartorders where date(created)='%s';
- 
 
 274 
"""
- 
 
 275 
DV_QUERY="""
- 
 
 276 
select sum(quantity*price) from flipkartorders where date(created)='%s';
- 
 
 277 
"""
- 
 
 278 
WQ_QUERY="""
- 
 
 279 
select sum(quantity) from flipkartorders where date(created) between '%s' and '%s';
- 
 
 280 
"""
- 
 
 281 
WV_QUERY="""
- 
 
 282 
select sum(quantity*price) from flipkartorders where date(created) between '%s' and '%s';
198 
"""
 283 
"""
199 
 
 - 
 
200 
 
 284 
 
201 
date_format = xlwt.XFStyle()
 285 
date_format = xlwt.XFStyle()
202 
date_format.num_format_str = 'yyyy/mm/dd'
 286 
date_format.num_format_str = 'yyyy/mm/dd'
203 
 
 287 
 
204 
datetime_format = xlwt.XFStyle()
 288 
datetime_format = xlwt.XFStyle()
Line 297... Line 381...
297 
            column += 1
 381 
            column += 1
298 
 
 382 
 
299 
    row = 0
383 
    row = 0
300 
    cursor.execute(dtoSql)
 384 
    cursor.execute(dtoSql)
301 
    result = cursor.fetchall()
 385 
    result = cursor.fetchall()
302
- 
 
303 
    for r in result:
 386 
    for r in result:
304 
        row += 1
 387 
        row += 1
305 
        column = 5
 388 
        column = 5
306 
        for data in r :
 389 
        for data in r :
307 
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
 390 
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
Line 311... Line 394...
311 
 
 394 
 
312 
    z=0
395 
    z=0
313 
    for x in sorted(dateWiseOrderMap):
 396 
    for x in sorted(dateWiseOrderMap):
314 
        row += 1
 397 
        row += 1
315 
        column = 6
 398 
        column = 6
- 
 
 399 
        d = str(datetime.fromtimestamp(x/1000.0))
- 
 
 400 
        dq=DQ_QUERY%d[:10]
- 
 
 401 
        cursor.execute(dq)
- 
 
 402 
        result = cursor.fetchall()
- 
 
 403 
        for r in result:
- 
 
 404 
            quantity= r[0]
- 
 
 405 
            if quantity is None:
- 
 
 406 
                quantity=0
316 
        worksheet.write(row,column,dateWiseOrderMap.get(x).count)
 407 
        worksheet.write(row,column,(dateWiseOrderMap.get(x).count)+quantity)
317 
        column += 1
 408 
        column += 1
318 
 
 409 
 
319 
    row =0
 410 
    row =0
320 
    for x in sorted(dateWiseOrderMap):
 411 
    for x in sorted(dateWiseOrderMap):
321 
        row += 1
 412 
        row += 1
322 
        column = 7
 413 
        column = 7
- 
 
 414 
        d = str(datetime.fromtimestamp(x/1000.0))
- 
 
 415 
        dq=DV_QUERY%d[:10]
- 
 
 416 
        cursor.execute(dq)
- 
 
 417 
        result = cursor.fetchall()
- 
 
 418 
        for r in result:
- 
 
 419 
            quantity= r[0]
- 
 
 420 
            if quantity is None:
- 
 
 421 
                quantity=0
323 
        worksheet.write(row,column,dateWiseOrderMap.get(x).value)
 422 
        worksheet.write(row,column,(dateWiseOrderMap.get(x).value)+float(quantity))
324 
        column += 1
423 
        column += 1
325
424
326 
def generateMonthlyReport():
 425 
def generateMonthlyReport():
327 
    monthSql = MONTH_QUERY
 426 
    monthSql = MONTH_QUERY
328 
    mnruSql = MNRU_QUERY
 427 
    mnruSql = MNRU_QUERY
Line 350... Line 449...
350 
    worksheet.write(row, 1, 'MTRU', boldStyle)
 449 
    worksheet.write(row, 1, 'MTRU', boldStyle)
351 
    worksheet.write(row, 2, 'MNRU', boldStyle)
 450 
    worksheet.write(row, 2, 'MNRU', boldStyle)
352 
    worksheet.write(row, 3, 'MAU', boldStyle)
 451 
    worksheet.write(row, 3, 'MAU', boldStyle)
353 
    worksheet.write(row, 4, 'MAB', boldStyle)
 452 
    worksheet.write(row, 4, 'MAB', boldStyle)
354 
    worksheet.write(row, 5, 'MTO', boldStyle)
 453 
    worksheet.write(row, 5, 'MTO', boldStyle)
- 
 
 454 
    worksheet.write(row, 6, 'MTS', boldStyle)
- 
 
 455 
    worksheet.write(row, 7, 'MTV', boldStyle)
355
456
356
- 
 
357 
    for r in result:
 457 
    for r in result:
358 
        row += 1
 458 
        row += 1
359 
        column = 0
 459 
        column = 0
360 
        for data in r :
 460 
        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)
 461 
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
Line 414... Line 514...
414 
        row += 1
 514 
        row += 1
415 
        column = 5
 515 
        column = 5
416 
        for data in r :
 516 
        for data in r :
417 
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
 517 
            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
418 
            column += 1
 518 
            column += 1
- 
 
 519
- 
 
 520 
    row=0
419 
 
 521 
 
- 
 
 522 
    z=0
- 
 
 523 
    for x in sorted(monthWiseOrderMap):
- 
 
 524 
        d = str(datetime.fromtimestamp(x/1000.0))
- 
 
 525 
        nextday = (x/1000 + (monthMap.get(d[5:7]) * oneDay))*1000
- 
 
 526 
        dnext = str(datetime.fromtimestamp(nextday/1000.0))
- 
 
 527 
        row += 1
- 
 
 528 
        column = 6
- 
 
 529 
        dq=WQ_QUERY % (d[:10],dnext[:10])
- 
 
 530 
        cursor.execute(dq)
- 
 
 531 
        result = cursor.fetchall()
- 
 
 532 
        for r in result:
- 
 
 533 
            quantity= r[0]
- 
 
 534 
            if quantity is None:
- 
 
 535 
                quantity=0
- 
 
 536 
        worksheet.write(row,column,(monthWiseOrderMap.get(x).count)+quantity)
- 
 
 537 
        column += 1
- 
 
 538
- 
 
 539 
    row =0
- 
 
 540 
    for x in sorted(monthWiseOrderMap):
- 
 
 541 
        row += 1
- 
 
 542 
        column = 7
- 
 
 543 
        d = str(datetime.fromtimestamp(x/1000.0))
- 
 
 544 
        nextday = (x/1000 + (monthMap.get(d[5:7]) * oneDay))*1000
- 
 
 545 
        dnext = str(datetime.fromtimestamp(nextday/1000.0))
- 
 
 546 
        dq=WV_QUERY % (d[:10],dnext[:10])
- 
 
 547 
        cursor.execute(dq)
- 
 
 548 
        result = cursor.fetchall()
- 
 
 549 
        for r in result:
- 
 
 550 
            quantity= r[0]
- 
 
 551 
            if quantity is None:
- 
 
 552 
                quantity=0
- 
 
 553 
        worksheet.write(row,column,(monthWiseOrderMap.get(x).value)+float(quantity))
- 
 
 554 
        column += 1
420 
    workbook.save(TMP_FILE)
 555 
    workbook.save(TMP_FILE)
421 
    sendmail(["manas.kapoor@shop2020.in","rajneesh.arora@saholic.com"], "", TMP_FILE, SUBJECT)
 556 
    sendmail(["manas.kapoor@shop2020.in","rajneesh.arora@saholic.com"], "", TMP_FILE, SUBJECT)
422 
    #sendmail(["manas.kapoor@shop2020.in"], "", TMP_FILE, SUBJECT)
 557 
    #sendmail(["manas.kapoor@shop2020.in"], "", TMP_FILE, SUBJECT)
423 
 
 558 
 
424 
def generateWeeklyReport():
 559 
def generateWeeklyReport():
Line 518... Line 653...
518
653
519 
    row = 0
654 
    row = 0
520 
    for x in sorted(weekWiseOrderMap):
 655 
    for x in sorted(weekWiseOrderMap):
521 
        row += 1
 656 
        row += 1
522 
        column = 6
 657 
        column = 6
- 
 
 658 
        d = str(datetime.fromtimestamp(x/1000.0))
- 
 
 659 
        nextday=oneDay*7*1000+x
- 
 
 660 
        dnext = str(datetime.fromtimestamp(nextday/1000.0))
- 
 
 661 
        dq=WQ_QUERY % (d[:10],dnext[:10])
- 
 
 662 
        cursor.execute(dq)
- 
 
 663 
        result = cursor.fetchall()
- 
 
 664 
        for r in result:
- 
 
 665 
            quantity= r[0]
- 
 
 666 
            if quantity is None:
- 
 
 667 
                quantity=0
523 
        worksheet.write(row,column,weekWiseOrderMap.get(x).count)
 668 
        worksheet.write(row,column,(weekWiseOrderMap.get(x).count)+quantity)
524 
        column+=1
 669 
        column+=1
525 
    row = 0
 670 
    row = 0
526 
    for x in sorted(weekWiseOrderMap):
 671 
    for x in sorted(weekWiseOrderMap):
527 
        row += 1
 672 
        row += 1
528 
        column = 7
 673 
        column = 7
- 
 
 674 
        d = str(datetime.fromtimestamp(x/1000.0))
- 
 
 675 
        nextday=oneDay*7*1000+x
- 
 
 676 
        dnext = str(datetime.fromtimestamp(nextday/1000.0))
- 
 
 677 
        dq=WV_QUERY % (d[:10],dnext[:10])
- 
 
 678 
        cursor.execute(dq)
- 
 
 679 
        result = cursor.fetchall()
- 
 
 680 
        for r in result:
- 
 
 681 
            quantity= r[0]
- 
 
 682 
            if quantity is None:
- 
 
 683 
                quantity=0
529 
        worksheet.write(row,column,weekWiseOrderMap.get(x).value)
 684 
        worksheet.write(row,column,(weekWiseOrderMap.get(x).value)+float(quantity))
530 
        column+=1
685 
        column+=1
531 
 
 686 
 
532 
 
 687 
 
533 
def sendmail(email, message, fileName, title):
 688 
def sendmail(email, message, fileName, title):
534 
    if email == "":
 689 
    if email == "":
Line 574... Line 729...
574 
            return None
 729 
            return None
575 
    return con
 730 
    return con
576 
 
 731 
 
577 
def populateOrderMap():
 732 
def populateOrderMap():
578 
    global dateWiseOrderMap
 733 
    global dateWiseOrderMap
579 
    allOrders = get_mongo_connection().Dtr.merchantOrder.find({'createdOnInt':{"$gte":cutOff}}).sort([('createdOnInt',pymongo.ASCENDING)])
 734 
    allOrders = get_mongo_connection().Dtr.merchantOrder.find({'createdOnInt':{"$gte":cutOff},'storeId':{"$in":[1,3,4]}}).sort([('createdOnInt',pymongo.ASCENDING)])
580 
    for orders in allOrders:
 735 
    for orders in allOrders:
581 
        if orders.get('orderId') not in order_ids:
 736 
        if orders.get('orderId') not in order_ids:
582 
            continue
 737 
            continue
583 
        cdate = ((to_py_date(orders.get('createdOnInt') * 1000)))
 738 
        cdate = ((to_py_date(orders.get('createdOnInt') * 1000)))
584 
        millisec = to_java_date(datetime(cdate.year, cdate.month, cdate.day))
 739 
        millisec = to_java_date(datetime(cdate.year, cdate.month, cdate.day))
Line 592... Line 747...
592 
            q, c = getSubOrderQuantity(orders.get('subOrders'))
 747 
            q, c = getSubOrderQuantity(orders.get('subOrders'))
593 
            orderObj.count = q
 748 
            orderObj.count = q
594 
            orderObj.value = c
749 
            orderObj.value = c
595 
            dateWiseOrderMap[millisec] = orderObj
 750 
            dateWiseOrderMap[millisec] = orderObj
596
751
- 
 
 752
- 
 
 753
597 
def getSubOrderQuantity(subOrders):
 754 
def getSubOrderQuantity(subOrders):
598 
    q = 0
 755 
    q = 0
599 
    c = 0
 756 
    c = 0
600 
    if subOrders is None:
 757 
    if subOrders is None:
601 
        return q, c
 758 
        return q, c
Line 622... Line 779...
622 
            break
 779 
            break
623
780
624 
def populateWeekWiseMap1():
 781 
def populateWeekWiseMap1():
625 
    global weekCutOff
 782 
    global weekCutOff
626 
    while(True):
 783 
    while(True):
627 
        #print weekCutOff *1000
 - 
 
628 
        #print to_java_date(datetime.now())
 - 
 
629 
        #print "**********************"
 - 
 
630 
        if weekCutOff *1000 >= to_java_date(datetime.now()):
 784 
        if weekCutOff *1000 >= to_java_date(datetime.now()):
631 
            #print "Breaking outer while"
 - 
 
632 
            break
 785 
            break
633 
        init = weekCutOff
 786 
        init = weekCutOff
634 
        breakPoint = weekCutOff + (6 * oneDay)
 787 
        breakPoint = weekCutOff + (6 * oneDay)
635 
        quantity, amount = 0 , 0
 788 
        quantity, amount = 0 , 0
636 
        while(True):
 789 
        while(True):
637 
            #print to_py_date(weekCutOff*1000)
 - 
 
638 
            #print "weekCutOff ",weekCutOff
 - 
 
639 
            #print "breakPoint ",breakPoint
 - 
 
640 
            orderObj = dateWiseOrderMap.get(weekCutOff * 1000)
 790 
            orderObj = dateWiseOrderMap.get(weekCutOff * 1000)
641 
            if orderObj is not None:
 791 
            if orderObj is not None:
642 
                quantity += orderObj.count
 792 
                quantity += orderObj.count
643 
                amount += orderObj.value
 793 
                amount += orderObj.value
644 
            weekCutOff = weekCutOff + oneDay
 794 
            weekCutOff = weekCutOff + oneDay
645 
            if weekCutOff > breakPoint:
 795 
            if weekCutOff > breakPoint:
646 
                weekWiseOrderMap[init * 1000] = __Order(quantity, amount)
 796 
                weekWiseOrderMap[init * 1000] = __Order(quantity, amount)
647 
                #print "Breaking inner while"
 - 
 
648 
                break
797 
                break
649 
 
 798 
 
650 
def populateMonthWiseMap():
 799 
def populateMonthWiseMap():
651 
    global monthCutOff
 800 
    global monthCutOff
652 
    while(True):
 801 
    while(True):
Line 660... Line 809...
660 
        monthCutOff = monthCutOff * 1000 * 30 + oneDay
 809 
        monthCutOff = monthCutOff * 1000 * 30 + oneDay
661 
        if monthCutOff >= to_py_date(datetime.now()):
 810 
        if monthCutOff >= to_py_date(datetime.now()):
662 
            break
 811 
            break
663 
 
 812 
 
664 
def populateMonthWiseMap1():
 813 
def populateMonthWiseMap1():
665 
    global weekCutOff
 814 
    global monthCutOff
666 
    while(True):
 815 
    while(True):
- 
 
 816
667 
        print weekCutOff *1000
 817 
        print monthCutOff *1000
- 
 
 818 
        a=str(to_py_date(monthCutOff*1000))
668 
        print to_java_date(datetime.now())
 819 
        print 'Now ' + str(to_java_date(datetime.now()))
669 
        print "**********************"
 820 
        print "**********************"
670 
        if weekCutOff *1000 >= to_java_date(datetime.now()):
 821 
        if monthCutOff *1000 >= to_java_date(datetime.now()):
671 
            print "Breaking outer while"
 822 
            print "Breaking outer while"
672 
            break
 823 
            break
673 
        init = weekCutOff
 824 
        init = monthCutOff
674 
        breakPoint = weekCutOff + (6 * oneDay)
 825 
        breakPoint = monthCutOff + (monthMap.get(a[5:7]) * oneDay)
- 
 
 826 
        print breakPoint
675 
        quantity, amount = 0 , 0
 827 
        quantity, amount = 0 , 0
676 
        while(True):
 828 
        while(True):
677 
            print to_py_date(weekCutOff*1000)
 829 
            print to_py_date(monthCutOff*1000)
678 
            print "weekCutOff ",weekCutOff
 830 
            print "weekCutOff ",monthCutOff
679 
            print "breakPoint ",breakPoint
 831 
            print "breakPoint ",breakPoint
680 
            orderObj = dateWiseOrderMap.get(weekCutOff * 1000)
 832 
            orderObj = dateWiseOrderMap.get(monthCutOff * 1000)
681 
            if orderObj is None:
 833 
            if orderObj is None:
682 
                print "None for ", to_py_date(weekCutOff * 1000)
 834 
                print "None for ", to_py_date(monthCutOff * 1000)
683 
            if orderObj is not None:
 835 
            if orderObj is not None:
684 
                quantity += orderObj.count
 836 
                quantity += orderObj.count
685 
                amount += orderObj.value
 837 
                amount += orderObj.value
686 
            weekCutOff = weekCutOff + oneDay
 838 
            monthCutOff = monthCutOff + oneDay
- 
 
 839 
            counter=0
- 
 
 840 
            print counter+1
687 
            if weekCutOff > breakPoint:
 841 
            if monthCutOff > breakPoint:
688 
                weekWiseOrderMap[init * 1000] = __Order(quantity, amount)
 842 
                monthWiseOrderMap[init * 1000] = __Order(quantity, amount)
689 
                print "Breaking inner while"
 843 
                print "Breaking inner while"
690 
                break
844 
                break
691
845
692 
def populateValidOrders():
 846 
def populateValidOrders():
693 
    global order_ids
 847 
    global order_ids
694 
    #allOrders = session.query(Orders.id).join((Users,Orders.user_id==Users.id)).filter(~(func.lower(Users.referrer)).like('emp%')).all()
 - 
 
695 
    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()
 848 
    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()
696 
    order_ids = list(zip(*allOrders)[0])
 849 
    order_ids = list(zip(*allOrders)[0])
697
850
698 
def main():
 851 
def main():
699 
    #date = raw_input('Enter a date name: ')
 - 
 
700 
    populateValidOrders()
 852 
    populateValidOrders()
701 
    populateOrderMap()
 853 
    populateOrderMap()
702 
    populateWeekWiseMap1()
 854 
    populateWeekWiseMap1()
- 
 
 855 
    populateMonthWiseMap1()
703 
    generateDailyReport()
 856 
    generateDailyReport()
704 
    generateWeeklyReport()
 857 
    generateWeeklyReport()
705 
    generateMonthlyReport()
 858 
    generateMonthlyReport()
706
859
- 
 
 860
707 
if __name__ == '__main__':
 861 
if __name__ == '__main__':
708 
    main()
 862 
    main()
709 
 
 - 
 
710 
 
 - 
 
711 

            863