Subversion Repositories SmartDukaan

Rev

Rev 18673 | Rev 19551 | Go to most recent revision | Details | Compare with Previous | Last modification | View Log | RSS feed

Rev Author Line No. Line
15394 manas 1 
'''
2 
Created on 27-May-2015
3 
@author: kshitij
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
20 
import pymongo
21 
from datetime import datetime
22 
from elixir import *
23 
from dtr.storage import DataService
24 
from dtr.storage.DataService import Orders, Users, CallHistory
25 
from sqlalchemy.sql.expression import func
17440 manish.sha 26 
from dtr.utils.MailSender import Email
18382 manas 27 
import optparse
15394 manas 28 
 
29 
DB_HOST = "localhost"
30 
DB_USER = "root"
31 
DB_PASSWORD = "shop2020"
32 
DB_NAME = "dtr"
18382 manas 33 
TMP_FILE = "/tmp/CRM_OutBound_Report.xls"
34 
RET_FILE = "/tmp/CRM_Retention_Report.xls"
15394 manas 35 
 
36 
con = None
37 
 
15407 manas 38 
SENDER = "adwords@shop2020.in"
39 
PASSWORD = "adwords_shop2020"
40 
SUBJECT = "CRM Outbound Report for " + str(date.today() - timedelta(days=1))
15394 manas 41 
SMTP_SERVER = "smtp.gmail.com"
42 
SMTP_PORT = 587
43 
 
18422 manas 44 
RET_SUBJECT = "CRM Retention Report for " + str(date.today() - timedelta(days=1))
18382 manas 45 
 
15394 manas 46 
date_format = xlwt.XFStyle()
47 
date_format.num_format_str = 'yyyy/mm/dd'
48 
 
49 
datetime_format = xlwt.XFStyle()
50 
datetime_format.num_format_str = 'yyyy/mm/dd HH:MM AM/PM'
51 
 
52 
default_format = xlwt.XFStyle()
53 
freshList=['1','2','3','4','5','6','7','8','9','10','11','12','13']
15409 manas 54 
followUpList=['1','2','3','4','5','6','7','8','9','10']
18382 manas 55 
onBoardingList=['1','2','3','4','5']
56 
accsList=['1','2','3','4','5','6','7','8','9','10','11','12']
18673 manas 57 
accsOrdersList=['1','2','3','4','5','6','7','8','9','10','11','12','13','14']
15394 manas 58 
dispositionMap = {  'call_later':0,
59 
                    'ringing_no_answer':1,
60 
                    'not_reachable':2,
61 
                    'switch_off':3,
62 
                    'invalid_no':4,
63 
                    'wrong_no':5,
64 
                    'hang_up':6,
65 
                    'retailer_not_interested':7,
66 
                    'alreadyuser':8,
67 
                    'verified_link_sent':9,
68 
                    'accessory_retailer':10,
69 
                    'service_center_retailer':11,
70 
                    'not_retailer':12,
71 
                    'recharge_retailer':13,
72 
                    'onboarded':14
73 
                }
74 
followUpDispositionMap = {  'call_later':0,
75 
                    'ringing_no_answer':1,
76 
                    'not_reachable':2,
77 
                    'switch_off':3,
78 
                    'retailer_not_interested':4,
79 
                    'alreadyuser':5,
80 
                    'verified_link_sent':6,
15409 manas 81 
                    'accessory_retailer':7,
82 
                    'service_center_retailer':8,
83 
                    'not_retailer':9,
84 
                    'recharge_retailer':10
15394 manas 85 
                }
86 
onBoardingDispositionMap = {  'call_later':0,
87 
                    'ringing_no_answer':1,
88 
                    'not_reachable':2,
89 
                    'switch_off':3,
90 
                    'onboarded':4,
91 
                }
92 
 
18382 manas 93 
accsDispositionMap = {  'call_later':1,
94 
                'ringing_no_answer':2,
95 
                'not_reachable':3,
96 
                'switch_off':4,
97 
                'technical_issue':5,
98 
                'pricing_issue':6,
99 
                'shipping_issue':7,
100 
                'internet_issue':8,
101 
                'checking_price':9,
102 
                'order_process':10,
103 
                'placed_order':11,
104 
                'place_order':12
105 
              }
18673 manas 106 
 
107 
accsDispositionOrdersMap = {  'call_later':1,
108 
                'ringing_no_answer':2,
109 
                'not_reachable':3,
110 
                'switch_off':4,
111 
                'technical_issue':5,
112 
                'pricing_issue':6,
113 
                'shipping_issue':7,
114 
                'already_purchased':8,
115 
                'product_availability':9,
116 
                'return_replacement':10,
117 
                'product_quality_issue':11,
118 
                'delayed_delivery':12,
119 
                'other_complaint':13,
120 
                'not_dealing_accessories':14
121 
              }
15402 manas 122 
# FRESH_QUERY="""
123 
# select call_disposition,count(1) from
124 
# (select * from (select * from callhistory where date(created)=date(now())
125 
# and call_type ='fresh' order by created desc)
126 
# as a group by retailer_id)
127 
# a1 group by date(created), call_disposition;
128 
# """
15394 manas 129 
FRESH_QUERY="""
130 
select call_disposition,count(1) from
15407 manas 131 
(select * from (select * from callhistory where date(created)=date(now() - INTERVAL 1 DAY)
15394 manas 132 
and call_type ='fresh' order by created desc)
133 
as a group by retailer_id)
134 
a1 group by date(created), call_disposition;
135 
"""
136 
AGENT_FRESH_QUERY="""
137 
select call_disposition,count(1) from
15407 manas 138 
(select * from (select * from callhistory where date(created)=date(now() - INTERVAL 1 DAY)
15394 manas 139 
and call_type ='fresh' and agent_id=%s order by created desc)
140 
as a group by retailer_id)
141 
a1 group by date(created), call_disposition,agent_id;
142 
"""
143 
AGENT_NAME_QUERY="""
144 
select name from agents where id=%s
145 
"""
146 
FRESH_QUERY_LOGIN_TIME="""
15407 manas 147 
select agent_id,TIMEDIFF(logoutTime,loginTime) from agentlogintimings where role='fresh' and date(created)=date(now() - INTERVAL 1 DAY);
15394 manas 148 
"""
149 
FRESH_QUERY_DURATION="""
15407 manas 150 
select sum(duration_sec) from callhistory where call_type ='fresh' and date(created)=date(now() - INTERVAL 1 DAY);
15394 manas 151 
"""
15402 manas 152 
FRESH_QUERY_AHT="""
15407 manas 153 
select timestampdiff(second, callhistory.last_fetch_time, created) from callhistory where last_fetch_time is not null and call_type='fresh' and date(created)=date(now() - INTERVAL 1 DAY );
15402 manas 154 
"""
155 
FRESH_QUERY_AIT="""
15407 manas 156 
select timestampdiff(second, last_action_time, created) from fetchdatahistory where call_type='fresh' and date(created)=date(now() - INTERVAL 1 DAY);
15402 manas 157 
"""
15455 manas 158 
#select count(*) from retailerlinks where date(activated) is not null and date(activated)=date(now() - interval 1 day);
15402 manas 159 
FRESH_QUERY_LINKS_CONVERTED="""
17019 manas 160 
select count(*) from users u join retailerlinks rl on (rl.user_id=u.id) where date(rl.activated)=date(now()- interval 1 day) and date(rl.created)=date(now()- interval 1 day);
15402 manas 161 
"""
162 
FRESH_QUERY_LINKS_NOT_CONVERTED="""
15407 manas 163 
select count(*) from retailerlinks where date(activated) is null and date(created)=date(now() - interval 1 day);
15402 manas 164 
"""
15418 manas 165 
FRESH_AGENTS_CALLED_COUNT="""
166 
select distinct(agent_id) from callhistory where call_type='fresh' and date(created) = date(now()-interval 1 day);
167 
"""
168 
 
15415 manas 169 
TOTAL_ACTIVATIONS="""
17019 manas 170 
select count(1) from users where (lower(referrer)  not in('emp01','crm01','crm02','fos01','crm03','crm04') or lower(utm_campaign) not in('emp01','crm01','crm02','fos01','crm03','crm04')) and date(activation_time) = date(now()-interval 1 day)
15415 manas 171 
"""
15402 manas 172 
 
15394 manas 173 
AGENT_FRESH_QUERY_LOGIN_TIME="""
15407 manas 174 
select TIMEDIFF(logoutTime,loginTime) from agentlogintimings where role='fresh' and date(created)=date(now() - INTERVAL 1 DAY) and agent_id=%s;
15394 manas 175 
"""
176 
AGENT_FRESH_QUERY_DURATION="""
15407 manas 177 
select sum(duration_sec) from callhistory where call_type ='fresh' and date(created)=date(now() - INTERVAL 1 DAY) and agent_id=%s;
15394 manas 178 
"""
15402 manas 179 
AGENT_FRESH_QUERY_AHT="""
15407 manas 180 
select timestampdiff(second, callhistory.last_fetch_time, created) from callhistory where last_fetch_time is not null and call_type='fresh' and date(created)=date(now() - INTERVAL 1 DAY) and agent_id=%s;
15402 manas 181 
"""
182 
AGENT_FRESH_QUERY_AIT="""
15407 manas 183 
select timestampdiff(second, last_action_time, created) from fetchdatahistory where call_type='fresh' and date(created)=date(now() - INTERVAL 1 DAY) and agent_id=%s;
15402 manas 184 
"""
185 
AGENT_FRESH_QUERY_LINKS_CONVERTED="""
17019 manas 186 
select count(*) from users u join retailerlinks rl on (rl.user_id=u.id) where date(rl.activated)=date(now()- interval 1 day) and date(rl.created)=date(now()- interval 1 day) and agent_id=%s;
15402 manas 187 
"""
188 
AGENT_FRESH_QUERY_LINKS_NOT_CONVERTED="""
15407 manas 189 
select count(*) from retailerlinks where date(activated) is null and date(created)=date(now() - interval 1 day) and agent_id=%s;
15402 manas 190 
"""
15394 manas 191 
 
15402 manas 192 
 
15394 manas 193 
FOLLOW_UP_QUERY="""
194 
select call_disposition,count(1) from
15407 manas 195 
(select * from (select * from callhistory where date(created)=date(now() - INTERVAL 1 DAY)
15394 manas 196 
and call_type ='followup' order by created desc)
197 
as a group by retailer_id)
198 
a1 group by date(created), call_disposition;
199 
"""
200 
AGENT_FOLLOW_UP_QUERY="""
201 
select call_disposition,count(1) from
15407 manas 202 
(select * from (select * from callhistory where date(created)=date(now() - INTERVAL 1 DAY)
15394 manas 203 
and call_type ='followup' and agent_id=%s order by created desc)
204 
as a group by retailer_id)
205 
a1 group by date(created), call_disposition,agent_id;
206 
"""
207 
FOLLOW_UP_QUERY_LOGIN_TIME="""
15407 manas 208 
select agent_id,TIMEDIFF(logoutTime,loginTime) from agentlogintimings where role='followup' and date(created)=date(now() - INTERVAL 1 DAY);
15394 manas 209 
"""
210 
FOLLOW_UP_QUERY_DURATION="""
15407 manas 211 
select sum(duration_sec) from callhistory where call_type ='followup' and date(created)=date(now() - INTERVAL 1 DAY);
15394 manas 212 
"""
15402 manas 213 
FOLLOW_UP_QUERY_AHT="""
15407 manas 214 
select timestampdiff(second, callhistory.last_fetch_time, created) from callhistory where last_fetch_time is not null and call_type='followup' and date(created)=date(now() - INTERVAL 1 DAY );
15402 manas 215 
"""
216 
FOLLOW_UP_QUERY_AIT="""
15407 manas 217 
select timestampdiff(second, last_action_time, created) from fetchdatahistory where call_type='followup' and date(created)=date(now() - INTERVAL 1 DAY);
15402 manas 218 
"""
15418 manas 219 
FOLLOW_UP_AGENTS_CALLED_COUNT="""
220 
select count(distinct(agent_id)) from callhistory where call_type='followup' and date(created) = date(now()-interval 1 day);
221 
"""
15394 manas 222 
AGENT_FOLLOW_UP_QUERY_LOGIN_TIME="""
15407 manas 223 
select TIMEDIFF(logoutTime,loginTime) from agentlogintimings where role='followup' and date(created)=date(now() - INTERVAL 1 DAY) and agent_id=%s;
15394 manas 224 
"""
225 
AGENT_FOLLOW_UP_QUERY_DURATION="""
15407 manas 226 
select sum(duration_sec) from callhistory where call_type ='followup' and date(created)=date(now() - INTERVAL 1 DAY) and agent_id=%s;
15394 manas 227 
"""
15402 manas 228 
AGENT_FOLLOW_UP_QUERY_AHT="""
15407 manas 229 
select timestampdiff(second, callhistory.last_fetch_time, created) from callhistory where last_fetch_time is not null and call_type='followup' and date(created)=date(now() - INTERVAL 1 DAY) and agent_id=%s;
15402 manas 230 
"""
231 
AGENT_FOLLOW_UP_QUERY_AIT="""
15407 manas 232 
select timestampdiff(second, last_action_time, created) from fetchdatahistory where call_type='followup' and date(created)=date(now() - INTERVAL 1 DAY)  and agent_id=%s;
15402 manas 233 
"""
15394 manas 234 
 
15402 manas 235 
 
15394 manas 236 
ONBOARDING_QUERY="""
237 
select call_disposition,count(1) from
15407 manas 238 
(select * from (select * from callhistory where date(created)=date(now() - INTERVAL 1 DAY)
15394 manas 239 
and call_type ='onboarding' order by created desc)
240 
as a group by retailer_id)
241 
a1 group by date(created), call_disposition;
242 
"""
15402 manas 243 
ONBOARDING_QUERY_LOGIN_TIME="""
15407 manas 244 
select agent_id,TIMEDIFF(logoutTime,loginTime) from agentlogintimings where role='onboarding' and date(created)=date(now() - INTERVAL 1 DAY);
15402 manas 245 
"""
246 
ONBOARDING_QUERY_DURATION="""
15407 manas 247 
select sum(duration_sec) from callhistory where call_type ='onboarding' and date(created)=date(now() - INTERVAL 1 DAY);
15402 manas 248 
"""
249 
ONBOARDING_QUERY_AHT="""
15407 manas 250 
select timestampdiff(second, callhistory.last_fetch_time, created) from callhistory where last_fetch_time is not null and call_type='onboarding' and date(created)=date(now() - INTERVAL 1 DAY );
15402 manas 251 
"""
252 
ONBOARDING_QUERY_AIT="""
15407 manas 253 
select timestampdiff(second, last_action_time, created) from fetchdatahistory where call_type='onboarding' and date(created)=date(now() - INTERVAL 1 DAY);
15402 manas 254 
"""
15418 manas 255 
ONBOARDING_AGENTS_CALLED_COUNT="""
256 
select count(distinct(agent_id)) from callhistory where call_type='onboarding' and date(created) = date(now()-interval 1 day);
257 
"""
15402 manas 258 
 
15394 manas 259 
AGENT_ONBOARDING_QUERY="""
260 
select call_disposition,count(1) from
15407 manas 261 
(select * from (select * from callhistory where date(created)=date(now() - INTERVAL 1 DAY)
15394 manas 262 
and call_type ='onboarding' and agent_id=%s order by created desc)
263 
as a group by retailer_id)
264 
a1 group by date(created), call_disposition,agent_id;
265 
"""
266 
AGENT_ONBOARDING_QUERY_LOGIN_TIME="""
15407 manas 267 
select TIMEDIFF(logoutTime,loginTime) from agentlogintimings where role='onboarding' and date(created)=date(now() - INTERVAL 1 DAY) and agent_id=%s;
15394 manas 268 
"""
269 
AGENT_ONBOARDING_QUERY_DURATION="""
15407 manas 270 
select sum(duration_sec) from callhistory where call_type ='onboarding' and date(created)=date(now() - INTERVAL 1 DAY) and agent_id=%s;
15394 manas 271 
"""
15402 manas 272 
AGENT_ONBOARDING_QUERY_AHT="""
15407 manas 273 
select timestampdiff(second, callhistory.last_fetch_time, created) from callhistory where last_fetch_time is not null and call_type='onboarding' and date(created)=date(now() - INTERVAL 1 DAY ) and agent_id=%s;
15402 manas 274 
"""
275 
AGENT_ONBOARDING_QUERY_AIT="""
15407 manas 276 
select timestampdiff(second, last_action_time, created) from fetchdatahistory where call_type='onboarding' and date(created)=date(now() - INTERVAL 1 DAY)  and agent_id=%s;
15402 manas 277 
"""
278 
AGENT_ONBOARDING_QUERY_AIT="""
15407 manas 279 
select timestampdiff(second, last_action_time, created) from fetchdatahistory where call_type='onboarding' and date(created)=date(now() - INTERVAL 1 DAY) and agent_id=%s;
15402 manas 280 
"""
15394 manas 281 
 
15402 manas 282 
center_alignment=xlwt.easyxf("align: horiz center")
15394 manas 283 
 
284 
 
18382 manas 285 
ACCS_CART_QUERY="""
286 
select call_disposition,count(1) from
18421 manas 287 
(select * from (select * from callhistorycrm where date(created)=date(now() - interval 1 day)
18382 manas 288 
and project_id=1 order by created desc)
289 
as a group by user_id)
290 
a1 group by date(created), call_disposition;
291 
"""
292 
ACCS_CART_AGENTS_CALLED_COUNT="""
18421 manas 293 
select distinct(agent_id) from callhistorycrm where project_id=1 and date(created) = date(now() - interval 1 day)
18382 manas 294 
"""
295 
ACCS_CART_QUERY_LOGIN_TIME="""
18421 manas 296 
select agent_id,TIMEDIFF(logoutTime,loginTime) from agentlogintimings where role='accs_cart' and date(created)=date(now() - interval 1 day);
18382 manas 297 
"""
298 
ACCS_CART_DISPOSITION_DESCRIPTION="""
299 
select user_id,call_disposition,disposition_description,disposition_comments
300 
from callhistorycrm where call_disposition not in ('ringing_no_answer','not_reachable','switch_off','call_later')
18421 manas 301 
and date(created)=date(now() - interval 1 day)"""
15402 manas 302 
 
18382 manas 303 
ACCS_CART_PRODUCT_PRICING="""
304 
select user_id,call_disposition,product_input,pricing_input from productpricinginputs where
18421 manas 305 
date(created)=date(now() - interval 1 day) and product_input not like '';
18382 manas 306 
"""
307 
 
18495 manas 308 
ACCS_TAB_QUERY="""
309 
select call_disposition,count(1) from
310 
(select * from (select * from callhistorycrm where date(created)=date(now() - interval 1 day)
311 
and project_id=2 order by created desc)
312 
as a group by user_id)
313 
a1 group by date(created), call_disposition;
314 
"""
315 
 
316 
ACCS_TAB_AGENTS_CALLED_COUNT="""
317 
select distinct(agent_id) from callhistorycrm where project_id=2 and date(created) = date(now() - interval 1 day)
318 
"""
319 
 
320 
ACCS_TAB_QUERY_LOGIN_TIME="""
321 
select agent_id,TIMEDIFF(logoutTime,loginTime) from agentlogintimings where role='accs_active' and date(created)=date(now() - interval 1 day);
322 
"""
323 
 
324 
ACCS_TAB_DISPOSITION_DESCRIPTION="""
325 
select user_id,call_disposition,disposition_description,disposition_comments
326 
from callhistorycrm where call_disposition not in ('ringing_no_answer','not_reachable','switch_off','call_later')
327 
and date(created)=date(now() - interval 1 day) and project_id=2"""
328 
 
329 
ACCS_TAB_PRODUCT_PRICING="""
330 
select user_id,call_disposition,product_input,pricing_input from productpricinginputs where
331 
date(created)=date(now() - interval 1 day) and product_input not like '' and project_id=2;
332 
"""
333 
 
18673 manas 334 
ACCS_ORDER_QUERY="""
335 
select call_disposition,count(1) from
336 
(select * from (select * from callhistorycrm where date(created)=date(now()- interval 1 day)
337 
and project_id=3 order by created desc)
338 
as a group by user_id)
339 
a1 group by date(created), call_disposition;
340 
"""
341 
 
342 
ACCS_ORDER_AGENTS_CALLED_COUNT="""
343 
select distinct(agent_id) from callhistorycrm where project_id=3 and date(created) = date(now() - interval 1 day)
344 
"""
345 
 
346 
ACCS_ORDER_QUERY_LOGIN_TIME="""
347 
select agent_id,TIMEDIFF(logoutTime,loginTime) from agentlogintimings where role='accs_order' and date(created)=date(now() - interval 1 day);
348 
"""
349 
 
350 
ACCS_ORDER_DISPOSITION_DESCRIPTION="""
351 
select user_id,call_disposition,disposition_description,disposition_comments
352 
from callhistorycrm where call_disposition not in ('ringing_no_answer','not_reachable','switch_off','call_later')
353 
and date(created)=date(now() - interval 1 day) and project_id=3"""
354 
 
355 
ACCS_ORDER_PRODUCT_PRICING="""
356 
select user_id,call_disposition,product_input,pricing_input from productpricinginputs where
357 
date(created)=date(now() - interval 1 day) and product_input not like '' and project_id=3;
358 
"""
359 
 
15394 manas 360 
def getDbConnection():
361 
    return MySQLdb.connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME)
362 
 
18382 manas 363 
def generateAccessoriesCartReport():
364 
    datesql=ACCS_CART_QUERY
365 
    conn = getDbConnection()
366 
 
367 
    cursor = conn.cursor()
368 
    cursor.execute(datesql)
369 
    result = cursor.fetchall()
370 
    global newWorkbook
371 
    newWorkbook = xlwt.Workbook()
372 
    worksheet = newWorkbook.add_sheet("Accessories Cart")
373 
    boldStyle = xlwt.XFStyle()
374 
    newStyle= xlwt.XFStyle()
375 
    style = xlwt.XFStyle()
376 
    pattern = xlwt.Pattern()
377 
    pattern.pattern = xlwt.Pattern.SOLID_PATTERN
378 
    pattern.pattern_fore_colour = xlwt.Style.colour_map['yellow']
379 
    style.pattern = pattern
380 
    f = xlwt.Font()
381 
    f.bold = True
382 
    boldStyle.font = f
383 
 
384 
    column = 0
385 
    row = 0
386 
    currentList=[]
387 
 
388 
    worksheet.write(0,0,'Call Disposition Type',style)
389 
    worksheet.write(0,1,'Count',style)
390 
    worksheet.write(1, column, 'Call Later', newStyle)
391 
    worksheet.write(2,column, 'Ringing No Answer', newStyle)
392 
    worksheet.write(3,column, 'Not Reachable', newStyle)
393 
    worksheet.write(4,column, 'Switched Off', newStyle)
394 
    worksheet.write(5,column, 'Technical Issue', newStyle)
395 
    worksheet.write(6,column, 'Pricing Issue', newStyle)
396 
    worksheet.write(7,column, 'Shipping Issue', newStyle)
397 
    worksheet.write(8,column, 'Internet Issue', newStyle)
398 
    worksheet.write(9,column, 'Checking Price', newStyle)
399 
    worksheet.write(10,column, 'Order Process', newStyle)
400 
    worksheet.write(11,column, 'Placed Order', newStyle)
401 
    worksheet.write(12,column, 'Will Place Order', boldStyle)
402 
    worksheet.write(13,column, 'Contactable Data (%)', newStyle)
403 
    worksheet.write(14,column, 'Non Contactable Data (%)', newStyle)
404 
    worksheet.write(15,column, 'Agents Logged In', style)
405 
    worksheet.write(16,column, 'Average Login Time (In Hrs)', style)
406 
    worksheet.write(17,column, 'Total Dialed Out', style)
407 
    worksheet.write(18,column, 'Average Dialed Out per agent', style)
15394 manas 408 
 
18382 manas 409 
    contactableData=0
410 
    nonContactableData=0
18407 manas 411 
    global ACCS_CART_WILL_PLACE_ORDER
18531 manas 412 
    ACCS_CART_WILL_PLACE_ORDER=0
18382 manas 413 
    for r in result:
414 
        row = accsDispositionMap.get(r[0])
415 
        if accsDispositionMap.get(r[0]) == 2 or accsDispositionMap.get(r[0]) == 3 or accsDispositionMap.get(r[0]) == 4:
416 
            nonContactableData+=int(r[1])
417 
        else:
18407 manas 418 
            if accsDispositionMap.get(r[0])==12:
419 
                ACCS_CART_WILL_PLACE_ORDER = r[1]
18382 manas 420 
            contactableData+=r[1]
421 
        currentList.append(str(row))
422 
        column = 1
423 
        worksheet.write(row, column, r[1],center_alignment)
424 
 
425 
    remainingList = list(set(accsList) - set(currentList))
426 
    for i,val in enumerate(remainingList):
427 
        row = int(val)
428 
        column = 1
429 
        worksheet.write(row, column, 0,center_alignment)
430 
    totalDispositions=contactableData+nonContactableData
18407 manas 431 
    global ACCS_CART_TOTAL
432 
    global ACCS_CART_SUCCESSFUL
433 
    ACCS_CART_TOTAL = totalDispositions
434 
    ACCS_CART_SUCCESSFUL=contactableData
18382 manas 435 
    if totalDispositions > 0:
436 
        worksheet.write(13,1,round((contactableData/float(totalDispositions))*100,2),center_alignment)
437 
        worksheet.write(14,1,round((nonContactableData/float(totalDispositions))*100,2),center_alignment)
438 
 
439 
    conn.close()
440 
 
441 
    datesql=ACCS_CART_AGENTS_CALLED_COUNT
442 
    conn = getDbConnection()
443 
    cursor = conn.cursor()
444 
    cursor.execute(datesql)
445 
    result = cursor.fetchall()
446 
    agentLoginList=[]
447 
    for r in result:
448 
        agentLoginList.append(str(r[0]))
449 
    conn.close()
18407 manas 450 
    global ACCS_CART_LOGIN
451 
    ACCS_CART_LOGIN = len(list(set(agentLoginList)))
18382 manas 452 
    if len(list(set(agentLoginList))) > 0:
453 
        datesql=ACCS_CART_QUERY_LOGIN_TIME
454 
        conn = getDbConnection()
455 
        cursor = conn.cursor()
456 
        cursor.execute(datesql)
457 
        result = cursor.fetchall()
458 
        loginTime=0
459 
        for r in result:
460 
            loginTime+=r[1].seconds
461 
        averageLoginTime=loginTime/len(list(set(agentLoginList)))
462 
        hours=averageLoginTime/3600
463 
        minutesLeft=(averageLoginTime%3600)/60
464 
        worksheet.write(15,1,len(list(set(agentLoginList))),center_alignment)
465 
        worksheet.write(16,1,str(hours) + ':'+ str(minutesLeft),center_alignment)
466 
        worksheet.write(17,1,totalDispositions,center_alignment)
467 
        worksheet.write(18,1,totalDispositions/float(len(list(set(agentLoginList)))),center_alignment)
468 
        conn.close()
469 
 
470 
 
471 
    worksheet.write(0,5, 'User Id', style)
472 
    worksheet.write(0,6, 'Call Disposition', style)
473 
    worksheet.write(0,7, 'Disposition Description', style)
474 
    worksheet.write(0,8, 'Disposition Comments', style)
475 
    datesql = ACCS_CART_DISPOSITION_DESCRIPTION
476 
    conn = getDbConnection()
477 
    cursor = conn.cursor()
478 
    cursor.execute(datesql)
479 
    result = cursor.fetchall()
480 
    row=0
481 
    for r in result:
482 
        row=row+1
483 
        column =5
484 
        for data in r:
485 
            worksheet.write(row,column,data)
486 
            column+=1
487 
    conn.close()
488 
 
489 
    worksheet.write(20,0, 'User Id', style)
490 
    worksheet.write(20,1, 'Call Disposition', style)
491 
    worksheet.write(20,2, 'Product Inputs', style)
492 
    worksheet.write(20,3, 'Pricing Inputs', style)
493 
    datesql = ACCS_CART_PRODUCT_PRICING
494 
    conn = getDbConnection()
495 
    cursor = conn.cursor()
496 
    cursor.execute(datesql)
497 
    result = cursor.fetchall()
498 
    row=20
499 
    for r in result:
500 
        row=row+1
501 
        column =0
502 
        for data in r:
503 
            worksheet.write(row,column,data)
504 
            column+=1
505 
 
506 
    newWorkbook.save(RET_FILE)
507 
 
18495 manas 508 
def generateAccessoriesTabsReport():
509 
    datesql=ACCS_TAB_QUERY
510 
    conn = getDbConnection()
511 
 
512 
    cursor = conn.cursor()
513 
    cursor.execute(datesql)
514 
    result = cursor.fetchall()
515 
    rb = open_workbook(RET_FILE,formatting_info=True)
516 
    newWorkbook = copy(rb)
517 
 
518 
    worksheet = newWorkbook.add_sheet("Accessories Tab")
519 
    boldStyle = xlwt.XFStyle()
520 
    newStyle= xlwt.XFStyle()
521 
    style = xlwt.XFStyle()
522 
    pattern = xlwt.Pattern()
523 
    pattern.pattern = xlwt.Pattern.SOLID_PATTERN
524 
    pattern.pattern_fore_colour = xlwt.Style.colour_map['yellow']
525 
    style.pattern = pattern
526 
    f = xlwt.Font()
527 
    f.bold = True
528 
    boldStyle.font = f
529 
 
530 
    column = 0
531 
    row = 0
532 
    currentList=[]
533 
 
534 
    worksheet.write(0,0,'Call Disposition Type',style)
535 
    worksheet.write(0,1,'Count',style)
536 
    worksheet.write(1, column, 'Call Later', newStyle)
537 
    worksheet.write(2,column, 'Ringing No Answer', newStyle)
538 
    worksheet.write(3,column, 'Not Reachable', newStyle)
539 
    worksheet.write(4,column, 'Switched Off', newStyle)
540 
    worksheet.write(5,column, 'Technical Issue', newStyle)
541 
    worksheet.write(6,column, 'Pricing Issue', newStyle)
542 
    worksheet.write(7,column, 'Shipping Issue', newStyle)
543 
    worksheet.write(8,column, 'Internet Issue', newStyle)
544 
    worksheet.write(9,column, 'Checking Price', newStyle)
545 
    worksheet.write(10,column, 'Order Process', newStyle)
546 
    worksheet.write(11,column, 'Placed Order', newStyle)
547 
    worksheet.write(12,column, 'Will Place Order', boldStyle)
548 
    worksheet.write(13,column, 'Contactable Data (%)', newStyle)
549 
    worksheet.write(14,column, 'Non Contactable Data (%)', newStyle)
550 
    worksheet.write(15,column, 'Agents Logged In', style)
551 
    worksheet.write(16,column, 'Average Login Time (In Hrs)', style)
552 
    worksheet.write(17,column, 'Total Dialed Out', style)
553 
    worksheet.write(18,column, 'Average Dialed Out per agent', style)
554 
 
555 
    contactableData=0
556 
    nonContactableData=0
557 
    global ACCS_TAB_WILL_PLACE_ORDER
18532 manas 558 
    ACCS_TAB_WILL_PLACE_ORDER=0
18495 manas 559 
    for r in result:
560 
        row = accsDispositionMap.get(r[0])
561 
        if accsDispositionMap.get(r[0]) == 2 or accsDispositionMap.get(r[0]) == 3 or accsDispositionMap.get(r[0]) == 4:
562 
            nonContactableData+=int(r[1])
563 
        else:
564 
            if accsDispositionMap.get(r[0])==12:
565 
                ACCS_TAB_WILL_PLACE_ORDER = r[1]
566 
            contactableData+=r[1]
567 
        currentList.append(str(row))
568 
        column = 1
569 
        worksheet.write(row, column, r[1],center_alignment)
570 
 
571 
    remainingList = list(set(accsList) - set(currentList))
572 
    for i,val in enumerate(remainingList):
573 
        row = int(val)
574 
        column = 1
575 
        worksheet.write(row, column, 0,center_alignment)
576 
    totalDispositions=contactableData+nonContactableData
577 
    global ACCS_TAB_TOTAL
578 
    global ACCS_TAB_SUCCESSFUL
579 
    ACCS_TAB_TOTAL = totalDispositions
580 
    ACCS_TAB_SUCCESSFUL=contactableData
581 
    if totalDispositions > 0:
582 
        worksheet.write(13,1,round((contactableData/float(totalDispositions))*100,2),center_alignment)
583 
        worksheet.write(14,1,round((nonContactableData/float(totalDispositions))*100,2),center_alignment)
584 
 
585 
    conn.close()
586 
 
587 
    datesql=ACCS_TAB_AGENTS_CALLED_COUNT
588 
    conn = getDbConnection()
589 
    cursor = conn.cursor()
590 
    cursor.execute(datesql)
591 
    result = cursor.fetchall()
592 
    agentLoginList=[]
593 
    for r in result:
594 
        agentLoginList.append(str(r[0]))
595 
    conn.close()
596 
    global ACCS_TAB_LOGIN
597 
    ACCS_TAB_LOGIN = len(list(set(agentLoginList)))
598 
    if len(list(set(agentLoginList))) > 0:
599 
        datesql=ACCS_TAB_QUERY_LOGIN_TIME
600 
        conn = getDbConnection()
601 
        cursor = conn.cursor()
602 
        cursor.execute(datesql)
603 
        result = cursor.fetchall()
604 
        loginTime=0
605 
        for r in result:
606 
            loginTime+=r[1].seconds
607 
        averageLoginTime=loginTime/len(list(set(agentLoginList)))
608 
        hours=averageLoginTime/3600
609 
        minutesLeft=(averageLoginTime%3600)/60
610 
        worksheet.write(15,1,len(list(set(agentLoginList))),center_alignment)
611 
        worksheet.write(16,1,str(hours) + ':'+ str(minutesLeft),center_alignment)
612 
        worksheet.write(17,1,totalDispositions,center_alignment)
613 
        worksheet.write(18,1,totalDispositions/float(len(list(set(agentLoginList)))),center_alignment)
614 
        conn.close()
615 
 
616 
 
617 
    worksheet.write(0,5, 'User Id', style)
618 
    worksheet.write(0,6, 'Call Disposition', style)
619 
    worksheet.write(0,7, 'Disposition Description', style)
620 
    worksheet.write(0,8, 'Disposition Comments', style)
621 
    datesql = ACCS_TAB_DISPOSITION_DESCRIPTION
622 
    conn = getDbConnection()
623 
    cursor = conn.cursor()
624 
    cursor.execute(datesql)
625 
    result = cursor.fetchall()
626 
    row=0
627 
    for r in result:
628 
        row=row+1
629 
        column =5
630 
        for data in r:
631 
            worksheet.write(row,column,data)
632 
            column+=1
633 
    conn.close()
634 
 
635 
    worksheet.write(20,0, 'User Id', style)
636 
    worksheet.write(20,1, 'Call Disposition', style)
637 
    worksheet.write(20,2, 'Product Inputs', style)
638 
    worksheet.write(20,3, 'Pricing Inputs', style)
639 
    datesql = ACCS_TAB_PRODUCT_PRICING
640 
    conn = getDbConnection()
641 
    cursor = conn.cursor()
642 
    cursor.execute(datesql)
643 
    result = cursor.fetchall()
644 
    row=20
645 
    for r in result:
646 
        row=row+1
647 
        column =0
648 
        for data in r:
649 
            worksheet.write(row,column,data)
650 
            column+=1
651 
 
652 
    newWorkbook.save(RET_FILE)
18673 manas 653 
 
654 
def generateAccessoriesOrderReport():
655 
    datesql=ACCS_ORDER_QUERY
656 
    conn = getDbConnection()
18495 manas 657 
 
18673 manas 658 
    cursor = conn.cursor()
659 
    cursor.execute(datesql)
660 
    result = cursor.fetchall()
661 
    rb = open_workbook(RET_FILE,formatting_info=True)
662 
    newWorkbook = copy(rb)
663 
 
664 
    worksheet = newWorkbook.add_sheet("Accessories Order")
665 
    boldStyle = xlwt.XFStyle()
666 
    newStyle= xlwt.XFStyle()
667 
    style = xlwt.XFStyle()
668 
    pattern = xlwt.Pattern()
669 
    pattern.pattern = xlwt.Pattern.SOLID_PATTERN
670 
    pattern.pattern_fore_colour = xlwt.Style.colour_map['yellow']
671 
    style.pattern = pattern
672 
    f = xlwt.Font()
673 
    f.bold = True
674 
    boldStyle.font = f
675 
 
676 
    column = 0
677 
    row = 0
678 
    currentList=[]
679 
 
680 
    worksheet.write(0,0,'Call Disposition Type',style)
681 
    worksheet.write(0,1,'Count',style)
682 
    worksheet.write(1, column, 'Call Later', newStyle)
683 
    worksheet.write(2,column, 'Ringing No Answer', newStyle)
684 
    worksheet.write(3,column, 'Not Reachable', newStyle)
685 
    worksheet.write(4,column, 'Switched Off', newStyle)
686 
    worksheet.write(5,column, 'Technical Issue', newStyle)
687 
    worksheet.write(6,column, 'Pricing Issue', newStyle)
688 
    worksheet.write(7,column, 'Shipping Issue', newStyle)
689 
    worksheet.write(8,column, 'Already purchased required stock', newStyle)
690 
    worksheet.write(9,column, 'Product Not available', newStyle)
691 
    worksheet.write(10,column, 'Return or replacement pending', newStyle)
692 
    worksheet.write(11,column, 'Product Quality issue', newStyle)
693 
    worksheet.write(12,column, 'Delayed delivery', newStyle)
694 
    worksheet.write(13,column, 'Other complaint with Profitmandi', newStyle)
695 
    worksheet.write(14,column, 'Not Dealing in Accessories/Not Interested', newStyle)
696 
    worksheet.write(15,column, 'Contactable Data (%)', newStyle)
697 
    worksheet.write(16,column, 'Non Contactable Data (%)', newStyle)
698 
    worksheet.write(17,column, 'Agents Logged In', style)
699 
    worksheet.write(18,column, 'Average Login Time (In Hrs)', style)
700 
    worksheet.write(19,column, 'Total Dialed Out', style)
701 
    worksheet.write(20,column, 'Average Dialed Out per agent', style)
702 
 
703 
    contactableData=0
704 
    nonContactableData=0
705 
    for r in result:
706 
        row = accsDispositionOrdersMap.get(r[0])
707 
        if accsDispositionOrdersMap.get(r[0]) == 2 or accsDispositionOrdersMap.get(r[0]) == 3 or accsDispositionOrdersMap.get(r[0]) == 4:
708 
            nonContactableData+=int(r[1])
709 
        else:
710 
            contactableData+=r[1]
711 
        currentList.append(str(row))
712 
        column = 1
713 
        worksheet.write(row, column, r[1],center_alignment)
714 
 
715 
    remainingList = list(set(accsOrdersList) - set(currentList))
716 
    for i,val in enumerate(remainingList):
717 
        row = int(val)
718 
        column = 1
719 
        worksheet.write(row, column, 0,center_alignment)
720 
    totalDispositions=contactableData+nonContactableData
721 
    global ACCS_ORDER_TOTAL
722 
    global ACCS_ORDER_SUCCESSFUL
723 
    ACCS_ORDER_TOTAL = totalDispositions
724 
    ACCS_ORDER_SUCCESSFUL=contactableData
725 
    if totalDispositions > 0:
726 
        worksheet.write(15,1,round((contactableData/float(totalDispositions))*100,2),center_alignment)
727 
        worksheet.write(16,1,round((nonContactableData/float(totalDispositions))*100,2),center_alignment)
728 
 
729 
    conn.close()
730 
 
731 
    datesql=ACCS_ORDER_AGENTS_CALLED_COUNT
732 
    conn = getDbConnection()
733 
    cursor = conn.cursor()
734 
    cursor.execute(datesql)
735 
    result = cursor.fetchall()
736 
    agentLoginList=[]
737 
    for r in result:
738 
        agentLoginList.append(str(r[0]))
739 
    conn.close()
740 
    global ACCS_ORDER_LOGIN
741 
    ACCS_ORDER_LOGIN = len(list(set(agentLoginList)))
742 
    if len(list(set(agentLoginList))) > 0:
743 
        datesql=ACCS_ORDER_QUERY_LOGIN_TIME
744 
        conn = getDbConnection()
745 
        cursor = conn.cursor()
746 
        cursor.execute(datesql)
747 
        result = cursor.fetchall()
748 
        loginTime=0
749 
        for r in result:
750 
            loginTime+=r[1].seconds
751 
        averageLoginTime=loginTime/len(list(set(agentLoginList)))
752 
        hours=averageLoginTime/3600
753 
        minutesLeft=(averageLoginTime%3600)/60
754 
        worksheet.write(17,1,len(list(set(agentLoginList))),center_alignment)
755 
        worksheet.write(18,1,str(hours) + ':'+ str(minutesLeft),center_alignment)
756 
        worksheet.write(19,1,totalDispositions,center_alignment)
757 
        worksheet.write(20,1,totalDispositions/float(len(list(set(agentLoginList)))),center_alignment)
758 
        conn.close()
759 
 
760 
 
761 
    worksheet.write(0,5, 'User Id', style)
762 
    worksheet.write(0,6, 'Call Disposition', style)
763 
    worksheet.write(0,7, 'Disposition Description', style)
764 
    worksheet.write(0,8, 'Disposition Comments', style)
765 
    datesql = ACCS_ORDER_DISPOSITION_DESCRIPTION
766 
    conn = getDbConnection()
767 
    cursor = conn.cursor()
768 
    cursor.execute(datesql)
769 
    result = cursor.fetchall()
770 
    row=0
771 
    for r in result:
772 
        row=row+1
773 
        column =5
774 
        for data in r:
775 
            worksheet.write(row,column,data)
776 
            column+=1
777 
    conn.close()
778 
 
779 
    worksheet.write(22,0, 'User Id', style)
780 
    worksheet.write(22,1, 'Call Disposition', style)
781 
    worksheet.write(22,2, 'Product Inputs', style)
782 
    worksheet.write(22,3, 'Pricing Inputs', style)
783 
    datesql = ACCS_ORDER_PRODUCT_PRICING
784 
    conn = getDbConnection()
785 
    cursor = conn.cursor()
786 
    cursor.execute(datesql)
787 
    result = cursor.fetchall()
788 
    row=20
789 
    for r in result:
790 
        row=row+1
791 
        column =0
792 
        for data in r:
793 
            worksheet.write(row,column,data)
794 
            column+=1
795 
    conn.close()
796 
    newWorkbook.save(RET_FILE)
797 
 
15394 manas 798 
def generateFreshCallingReport():
799 
    datesql=FRESH_QUERY
800 
    conn = getDbConnection()
801 
 
802 
    cursor = conn.cursor()
803 
    cursor.execute(datesql)
804 
    result = cursor.fetchall()
805 
    global workbook
15506 manas 806 
    totalVerifiedLinkSent=0
15394 manas 807 
    workbook = xlwt.Workbook()
808 
    worksheet = workbook.add_sheet("Fresh")
809 
    boldStyle = xlwt.XFStyle()
810 
    newStyle= xlwt.XFStyle()
811 
    style = xlwt.XFStyle()
812 
    pattern = xlwt.Pattern()
813 
    pattern.pattern = xlwt.Pattern.SOLID_PATTERN
814 
    pattern.pattern_fore_colour = xlwt.Style.colour_map['yellow']
815 
    style.pattern = pattern
816 
    f = xlwt.Font()
817 
    f.bold = True
818 
    boldStyle.font = f
819 
 
820 
    column = 0
821 
    row = 0
822 
    currentList=[]
823 
 
824 
    worksheet.write(0,0,'Call Disposition Type',style)
825 
    worksheet.write(0,1,'Count',style)
826 
 
827 
    worksheet.write(1, column, 'Call Later', newStyle)
828 
    worksheet.write(2,column, 'Ringing No Answer', newStyle)
829 
    worksheet.write(3,column, 'Not Reachable', newStyle)
830 
    worksheet.write(4,column, 'Switched Off', newStyle)
831 
    worksheet.write(5,column, 'Invalid Number', newStyle)
832 
    worksheet.write(6,column, 'Wrong Number', newStyle)
833 
    worksheet.write(7,column, 'Hang Up', newStyle)
834 
    worksheet.write(8,column, 'Retailer Not Interested', newStyle)
835 
    worksheet.write(9,column, 'Already Profitmandi user', boldStyle)
836 
    worksheet.write(10,column, 'Verified Link Sent', boldStyle)
837 
    worksheet.write(11,column, 'Accessory Retailer', newStyle)
838 
    worksheet.write(12,column, 'Service Center Retailer', newStyle)
839 
    worksheet.write(13,column, 'Not a Retailer', newStyle)
840 
    worksheet.write(14,column, 'Recharge Retailer', newStyle)
15402 manas 841 
    worksheet.write(15,column, 'Contactable Data (%)', newStyle)
842 
    worksheet.write(16,column, 'Non Contactable Data (%)', newStyle)
15394 manas 843 
    worksheet.write(17,column, 'Agents Logged In', style)
844 
    worksheet.write(18,column, 'Average Login Time (In Hrs)', style)
845 
    worksheet.write(19,column, 'Total Dialed Out', style)
846 
    worksheet.write(20,column, 'Average Dialed Out per agent', style)
847 
    worksheet.write(21,column, 'Average Call Duration (In Hrs)', style)
15402 manas 848 
    worksheet.write(22,column, 'Average Handling Time (In Hrs)', style)
849 
    worksheet.write(23,column, 'Average Idle Time (In Hrs)', style)
850 
    worksheet.write(24,column, 'Links Converted', style)
851 
    worksheet.write(25,column, 'Link sent yet to be converted', style)
17021 manas 852 
    worksheet.write(26,column, 'Total activations(Links Converted + Followup)', style)
15394 manas 853 
    contactableData=0
854 
    nonContactableData=0
855 
 
856 
    for r in result:
15437 manas 857 
        if dispositionMap.get(r[0]) == 9:
858 
            totalVerifiedLinkSent=int(r[1])
15394 manas 859 
        row = dispositionMap.get(r[0])+1
860 
        if dispositionMap.get(r[0]) == 1 or dispositionMap.get(r[0]) == 2 or dispositionMap.get(r[0]) == 3 or dispositionMap.get(r[0]) == 4:
861 
            nonContactableData+=int(r[1])
862 
        else:
863 
            contactableData+=r[1]
864 
        currentList.append(str(row))
865 
        column = 1
15402 manas 866 
        worksheet.write(row, column, r[1],center_alignment)
15394 manas 867 
 
868 
    remainingList = list(set(freshList) - set(currentList))
869 
    for i,val in enumerate(remainingList):
870 
        row = int(val)
871 
        column = 1
15402 manas 872 
        worksheet.write(row, column, 0,center_alignment)
15394 manas 873 
    totalDispositions=contactableData+nonContactableData
874 
 
15502 amit.gupta 875 
    if totalDispositions > 0:
876 
        worksheet.write(15,1,round((contactableData/float(totalDispositions))*100,2),center_alignment)
877 
        worksheet.write(16,1,round((nonContactableData/float(totalDispositions))*100,2),center_alignment)
15394 manas 878 
 
15418 manas 879 
    conn.close()
15402 manas 880 
 
15418 manas 881 
    datesql=FRESH_AGENTS_CALLED_COUNT
882 
    conn = getDbConnection()
883 
    cursor = conn.cursor()
884 
    cursor.execute(datesql)
885 
    result = cursor.fetchall()
886 
    agentLoginList=[]
887 
    for r in result:
888 
        agentLoginList.append(str(r[0]))
889 
 
15394 manas 890 
    conn.close()
15505 manas 891 
 
18059 manas 892 
    if len(list(set(agentLoginList))) > 0:
15505 manas 893 
        datesql=FRESH_QUERY_LOGIN_TIME
894 
        conn = getDbConnection()
895 
        cursor = conn.cursor()
896 
        cursor.execute(datesql)
897 
        result = cursor.fetchall()
898 
        loginTime=0
899 
        for r in result:
900 
            loginTime+=r[1].seconds
901 
        averageLoginTime=loginTime/len(list(set(agentLoginList)))
902 
        hours=averageLoginTime/3600
903 
        minutesLeft=(averageLoginTime%3600)/60
904 
        worksheet.write(17,1,len(list(set(agentLoginList))),center_alignment)
905 
        worksheet.write(18,1,str(hours) + ':'+ str(minutesLeft),center_alignment)
906 
        worksheet.write(19,1,totalDispositions,center_alignment)
907 
        worksheet.write(20,1,totalDispositions/float(len(list(set(agentLoginList)))),center_alignment)
908 
        conn.close()
909 
        datesql=FRESH_QUERY_DURATION
910 
        conn = getDbConnection()
15418 manas 911 
 
15505 manas 912 
        cursor = conn.cursor()
913 
        cursor.execute(datesql)
914 
        result = cursor.fetchall()
915 
        for r in result:
916 
            totalCallDuration= r[0]
917 
        averageCallDuration=totalCallDuration/len(list(set(agentLoginList)))
918 
        hours=averageCallDuration/3600
919 
        minutesLeft=(averageCallDuration%3600)/60
920 
        worksheet.write(21,1,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
921 
 
922 
        conn.close()
923 
        datesql=FRESH_QUERY_AHT
924 
        conn = getDbConnection()
925 
 
926 
        cursor = conn.cursor()
927 
        cursor.execute(datesql)
928 
        result = cursor.fetchall()
929 
        loginTime=0
930 
        for r in result:
15481 amit.gupta 931 
            loginTime+=r[0]
15505 manas 932 
        averageLoginTime=loginTime/len(list(set(agentLoginList)))
933 
        hours=averageLoginTime/3600
934 
        minutesLeft=(averageLoginTime%3600)/60
935 
        worksheet.write(22,1,len(list(set(agentLoginList))),center_alignment)
936 
 
937 
        conn.close()
938 
        datesql=FRESH_QUERY_AIT
939 
        conn = getDbConnection()
940 
 
941 
        cursor = conn.cursor()
942 
        cursor.execute(datesql)
943 
        result = cursor.fetchall()
944 
        loginTime=0
945 
        for r in result:
946 
            if r[0] is not None:
947 
                loginTime+=r[0]
948 
        averageLoginTime=loginTime/len(list(set(agentLoginList)))
949 
        hours=averageLoginTime/3600
950 
        minutesLeft=(averageLoginTime%3600)/60
951 
        worksheet.write(23,1,len(list(set(agentLoginList))),center_alignment)
952 
        conn.close()
953 
 
15402 manas 954 
    datesql=FRESH_QUERY_LINKS_CONVERTED
955 
    conn = getDbConnection()
15441 manas 956 
    global Converted
957 
    global AgentsLoggedIn
958 
    AgentsLoggedIn=len(list(set(agentLoginList)))
15402 manas 959 
    cursor = conn.cursor()
960 
    cursor.execute(datesql)
961 
    result = cursor.fetchall()
962 
    loginTime=0
963 
    for r in result:
15441 manas 964 
        Converted=r[0]
15402 manas 965 
        worksheet.write(24,1,r[0],center_alignment)
15441 manas 966 
    worksheet.write(25,1,totalVerifiedLinkSent-Converted,center_alignment)
15402 manas 967 
    conn.close()
968 
 
15415 manas 969 
    datesql=TOTAL_ACTIVATIONS
970 
    conn = getDbConnection()
15441 manas 971 
    global TotalActivations
15415 manas 972 
    cursor = conn.cursor()
973 
    cursor.execute(datesql)
974 
    result = cursor.fetchall()
975 
    loginTime=0
976 
    for r in result:
15441 manas 977 
        TotalActivations=r[0]
15415 manas 978 
        worksheet.write(26,1,r[0],center_alignment)
979 
 
15394 manas 980 
    workbook.save(TMP_FILE)
981 
    #sendmail(["manas.kapoor@shop2020.in"], "", TMP_FILE, SUBJECT)
982 
 
983 
 
984 
def generateAgentWiseFreshCallingReport():
985 
 
986 
    agentId=3
987 
    rb = open_workbook(TMP_FILE,formatting_info=True)
988 
    workbook = copy(rb)
989 
    numberOfSheets=rb.nsheets
990 
    sheet=rb.sheet_by_index(numberOfSheets-1)
991 
    worksheet = workbook.get_sheet(numberOfSheets-1)
15556 amit.gupta 992 
    totalVerifiedLinkSent=0
15394 manas 993 
    boldStyle = xlwt.XFStyle()
994 
    newStyle= xlwt.XFStyle()
995 
    style = xlwt.XFStyle()
996 
    pattern = xlwt.Pattern()
997 
    pattern.pattern = xlwt.Pattern.SOLID_PATTERN
998 
    pattern.pattern_fore_colour = xlwt.Style.colour_map['yellow']
999 
    style.pattern = pattern
1000 
    f = xlwt.Font()
1001 
    f.bold = True
1002 
    boldStyle.font = f
1003 
 
1004 
    column = agentId
19160 manas 1005 
    row = 0
15394 manas 1006 
    worksheet.write(row,column-1,'Call Disposition Type',style)
1007 
    worksheet.write(row+1, column-1, 'Call Later', newStyle)
1008 
    worksheet.write(row+2,column-1, 'Ringing No Answer', newStyle)
1009 
    worksheet.write(row+3,column-1, 'Not Reachable', newStyle)
1010 
    worksheet.write(row+4,column-1, 'Switched Off', newStyle)
1011 
    worksheet.write(row+5,column-1, 'Invalid Number', newStyle)
1012 
    worksheet.write(row+6,column-1, 'Wrong Number', newStyle)
1013 
    worksheet.write(row+7,column-1, 'Hang Up', newStyle)
1014 
    worksheet.write(row+8,column-1, 'Retailer Not Interested', newStyle)
1015 
    worksheet.write(row+9,column-1, 'Already Profitmandi user', boldStyle)
1016 
    worksheet.write(row+10,column-1, 'Verified Link Sent', boldStyle)
1017 
    worksheet.write(row+11,column-1, 'Accessory Retailer', newStyle)
1018 
    worksheet.write(row+12,column-1, 'Service Center Retailer', newStyle)
1019 
    worksheet.write(row+13,column-1, 'Not a Retailer', newStyle)
1020 
    worksheet.write(row+14,column-1, 'Recharge Retailer', newStyle)
15402 manas 1021 
    worksheet.write(row+15,column-1, 'Contactable Data (%)', newStyle)
1022 
    worksheet.write(row+16,column-1, 'Non Contactable Data (%)', newStyle)
15394 manas 1023 
    worksheet.write(row+17,column-1, 'Total Dialed Out', style)
1024 
    worksheet.write(row+18,column-1, 'Login Time (In Hrs)', style)
1025 
    worksheet.write(row+19,column-1, 'Call Duration (In Hrs)', style)
15402 manas 1026 
    worksheet.write(row+20,column-1, 'Handling Time (In Hrs)', style)
1027 
    worksheet.write(row+21,column-1, 'Idle Time (In Hrs)', style)
1028 
    worksheet.write(row+22,column-1, 'Links Converted', style)
1029 
    worksheet.write(row+23,column-1, 'Link sent yet to be converted', style)
15394 manas 1030 
    columnId=agentId
1031 
    while True:
17752 manas 1032 
            if agentId >50 :
15394 manas 1033 
                break
1034 
            else:
1035 
                datesql=AGENT_FRESH_QUERY %(agentId)
1036 
                conn = getDbConnection()
1037 
 
1038 
                cursor = conn.cursor()
1039 
                cursor.execute(datesql)
1040 
                result = cursor.fetchall()
1041 
 
1042 
                currentList=[]
1043 
                contactableData=0
1044 
                nonContactableData=0
1045 
                if cursor.rowcount ==0:
1046 
 
1047 
                    agentId+=1
1048 
                    continue
1049 
                else:
1050 
                    for r in result:
15438 manas 1051 
                        if dispositionMap.get(r[0]) == 9:
1052 
                            totalVerifiedLinkSent=int(r[1])
19160 manas 1053 
                        row = dispositionMap.get(r[0])+1
15394 manas 1054 
                        if dispositionMap.get(r[0]) == 1 or dispositionMap.get(r[0]) == 2 or dispositionMap.get(r[0]) == 3 or dispositionMap.get(r[0]) == 4:
1055 
                            nonContactableData+=int(r[1])
1056 
                        else:
1057 
                            contactableData+=r[1]
1058 
                        currentList.append(str(dispositionMap.get(r[0])))
1059 
                        column = agentId
1060 
                        remainingList = list(set(freshList) - set(currentList))
1061 
 
15402 manas 1062 
                        worksheet.write(row, columnId, r[1],center_alignment)
15394 manas 1063 
                        for i,val in enumerate(remainingList):
19160 manas 1064 
                            row = int(val)+1
15394 manas 1065 
                            column = agentId
15402 manas 1066 
                            worksheet.write(row, columnId, 0,center_alignment)
15394 manas 1067 
                    totalDialedOut = contactableData+nonContactableData
15505 manas 1068 
                    if totalDialedOut > 0:
19160 manas 1069 
                        worksheet.write(15,columnId,round((contactableData/float(totalDialedOut))*100,2),center_alignment)
1070 
                        worksheet.write(16,columnId,round((nonContactableData/float(totalDialedOut))*100,2),center_alignment)
1071 
                        worksheet.write(17,columnId,totalDialedOut,center_alignment)
15394 manas 1072 
                    conn.close()
1073 
 
1074 
                    name_query=AGENT_NAME_QUERY %(agentId)
1075 
                    conn = getDbConnection()
1076 
                    column=agentId
1077 
                    cursor = conn.cursor()
1078 
                    cursor.execute(name_query)
1079 
                    result = cursor.fetchall()
1080 
                    for r in result:
19160 manas 1081 
                        worksheet.write(0,columnId,r,style)
15394 manas 1082 
                    conn.close()
19160 manas 1083 
 
15394 manas 1084 
                    name_query=AGENT_FRESH_QUERY_LOGIN_TIME %(agentId)
1085 
                    conn = getDbConnection()
1086 
                    column=agentId
1087 
                    cursor = conn.cursor()
1088 
                    cursor.execute(name_query)
1089 
                    result = cursor.fetchall()
1090 
                    loginTime=0
1091 
                    for r in result:
1092 
                        loginTime+=r[0].seconds
1093 
 
1094 
                    hours=loginTime/3600
1095 
                    minutesLeft=(loginTime%3600)/60
19160 manas 1096 
                    worksheet.write(18,columnId,str(hours)+':'+str(minutesLeft),center_alignment)
15394 manas 1097 
                    conn.close()
1098 
 
1099 
                    name_query=AGENT_FRESH_QUERY_DURATION %(agentId)
1100 
                    conn = getDbConnection()
1101 
                    column=agentId
1102 
                    cursor = conn.cursor()
1103 
                    cursor.execute(name_query)
1104 
                    result = cursor.fetchall()
1105 
                    loginTime=0
1106 
                    for r in result:
1107 
                        loginTime+=r[0]
1108 
                    hours=loginTime/3600
1109 
                    minutesLeft=(loginTime%3600)/60
19160 manas 1110 
                    worksheet.write(19,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
15394 manas 1111 
                    conn.close()
1112 
 
15402 manas 1113 
                    name_query=AGENT_FRESH_QUERY_AHT %(agentId)
1114 
                    conn = getDbConnection()
1115 
                    column=agentId
1116 
                    cursor = conn.cursor()
1117 
                    cursor.execute(name_query)
1118 
                    result = cursor.fetchall()
1119 
                    loginTime=0
1120 
                    for r in result:
1121 
                        loginTime+=r[0]
1122 
                    hours=loginTime/3600
1123 
                    minutesLeft=(loginTime%3600)/60
19160 manas 1124 
                    worksheet.write(20,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
15402 manas 1125 
                    conn.close()
1126 
 
1127 
                    name_query=AGENT_FRESH_QUERY_AIT %(agentId)
1128 
                    conn = getDbConnection()
1129 
                    column=agentId
1130 
                    cursor = conn.cursor()
1131 
                    cursor.execute(name_query)
1132 
                    result = cursor.fetchall()
1133 
                    loginTime=0
1134 
                    for r in result:
15482 amit.gupta 1135 
                        if r[0] is not None:
1136 
                            loginTime+=r[0]
15402 manas 1137 
                    hours=loginTime/3600
1138 
                    minutesLeft=(loginTime%3600)/60
19160 manas 1139 
                    worksheet.write(21,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
15402 manas 1140 
                    conn.close()
1141 
 
1142 
                    datesql=AGENT_FRESH_QUERY_LINKS_CONVERTED%(agentId)
1143 
                    conn = getDbConnection()
1144 
 
1145 
                    cursor = conn.cursor()
1146 
                    cursor.execute(datesql)
1147 
                    result = cursor.fetchall()
1148 
                    loginTime=0
1149 
                    for r in result:
15438 manas 1150 
                        converted=r[0]
19160 manas 1151 
                        worksheet.write(22,columnId,r[0],center_alignment)
15513 manas 1152 
 
1153 
                    if totalVerifiedLinkSent==0:
19160 manas 1154 
                        worksheet.write(23,columnId,0,center_alignment)
15514 manas 1155 
                    elif totalVerifiedLinkSent<converted:
19160 manas 1156 
                        worksheet.write(23,columnId,converted-totalVerifiedLinkSent,center_alignment)
15513 manas 1157 
                    else:
19160 manas 1158 
                        worksheet.write(23,columnId,totalVerifiedLinkSent-converted,center_alignment)
15402 manas 1159 
                    conn.close()
15394 manas 1160 
                agentId+=1
1161 
                columnId+=1
1162 
    workbook.save(TMP_FILE)
1163 
 
1164 
def generateFollowUpCallingReport():
1165 
    datesql=FOLLOW_UP_QUERY
1166 
    conn = getDbConnection()
1167 
 
1168 
    cursor = conn.cursor()
1169 
    cursor.execute(datesql)
1170 
    result = cursor.fetchall()
1171 
    rb = open_workbook(TMP_FILE,formatting_info=True)
1172 
    workbook = copy(rb)
1173 
 
1174 
    worksheet = workbook.add_sheet("FollowUp")
1175 
    boldStyle = xlwt.XFStyle()
1176 
    newStyle= xlwt.XFStyle()
1177 
    style = xlwt.XFStyle()
1178 
    pattern = xlwt.Pattern()
1179 
    pattern.pattern = xlwt.Pattern.SOLID_PATTERN
1180 
    pattern.pattern_fore_colour = xlwt.Style.colour_map['yellow']
1181 
    style.pattern = pattern
1182 
    f = xlwt.Font()
1183 
    f.bold = True
1184 
    boldStyle.font = f
1185 
 
1186 
    column = 0
1187 
    row = 0
1188 
    currentList=[]
1189 
 
1190 
    worksheet.write(0,0,'Call Disposition Type',style)
1191 
    worksheet.write(0,1,'Count',style)
1192 
 
1193 
    worksheet.write(1, column, 'Call Later', newStyle)
1194 
    worksheet.write(2,column, 'Ringing No Answer', newStyle)
1195 
    worksheet.write(3,column, 'Not Reachable', newStyle)
1196 
    worksheet.write(4,column, 'Switched Off', newStyle)
1197 
    worksheet.write(5,column, 'Retailer Not Interested', newStyle)
1198 
    worksheet.write(6,column, 'Already Profitmandi user', boldStyle)
1199 
    worksheet.write(7,column, 'Verified Link Sent', boldStyle)
15409 manas 1200 
    worksheet.write(8,column, 'Accessory Retailer', newStyle)
1201 
    worksheet.write(9,column, 'Service Center Retailer', newStyle)
1202 
    worksheet.write(10,column, 'Not Retailer', boldStyle)
1203 
    worksheet.write(11,column, 'Recharge Retailer', boldStyle)
1204 
    worksheet.write(12,column, 'Contactable Data (%)', newStyle)
1205 
    worksheet.write(13,column, 'Non Contactable Data (%)', newStyle)
1206 
    worksheet.write(14,column, 'Agents Logged In', style)
1207 
    worksheet.write(15,column, 'Average Login Time (In Hrs)', style)
1208 
    worksheet.write(16,column, 'Total Dialed Out', style)
1209 
    worksheet.write(17,column, 'Average Dialed Out per agent', style)
1210 
    worksheet.write(18,column, 'Average Call Duration (In Hrs)', style)
1211 
    worksheet.write(19,column, 'Average Handling Time (In Hrs)', style)
1212 
    worksheet.write(20,column, 'Average Idle Time (In Hrs)', style)
15394 manas 1213 
 
1214 
    contactableData=0
1215 
    nonContactableData=0
1216 
    totalDispositions=0
15402 manas 1217 
 
15394 manas 1218 
    for r in result:
1219 
        row = followUpDispositionMap.get(r[0])+1
15399 manas 1220 
        if followUpDispositionMap.get(r[0]) == 1 or followUpDispositionMap.get(r[0]) == 2 or followUpDispositionMap.get(r[0]) == 3:
15394 manas 1221 
            nonContactableData+=int(r[1])
1222 
        else:
1223 
            contactableData+=r[1]
1224 
        currentList.append(str(row))
1225 
        column = 1
15402 manas 1226 
        worksheet.write(row, column, r[1],center_alignment)
15394 manas 1227 
 
1228 
    remainingList = list(set(followUpList) - set(currentList))
15402 manas 1229 
 
15394 manas 1230 
    for i,val in enumerate(remainingList):
1231 
        row = int(val)
15402 manas 1232 
 
15394 manas 1233 
        column = 1
15402 manas 1234 
        worksheet.write(row, column, 0,center_alignment)
15505 manas 1235 
 
15394 manas 1236 
    totalDispositions=contactableData+nonContactableData
15505 manas 1237 
    if totalDispositions >0:
1238 
        worksheet.write(12,1,round((contactableData/float(totalDispositions))*100,2),center_alignment)
1239 
        worksheet.write(13,1,round((nonContactableData/float(totalDispositions))*100,2),center_alignment)
1240 
 
15394 manas 1241 
 
1242 
    conn.close()
1243 
    datesql=FOLLOW_UP_QUERY_LOGIN_TIME
1244 
    conn = getDbConnection()
1245 
 
1246 
    cursor = conn.cursor()
1247 
    cursor.execute(datesql)
1248 
    result = cursor.fetchall()
1249 
    agentLoginList=[]
1250 
    averageLoginTime=0
1251 
    loginTime=0
1252 
    for r in result:
1253 
        loginTime+=r[1].seconds
1254 
        agentLoginList.append(str(r[0]))
18059 manas 1255 
    if len(list(set(agentLoginList))) > 0:
15505 manas 1256 
        averageLoginTime=loginTime/len(list(set(agentLoginList)))
1257 
        hours=averageLoginTime/3600
1258 
        minutesLeft=(averageLoginTime%3600)/60
1259 
        worksheet.write(14,1,len(list(set(agentLoginList))),center_alignment)
1260 
        worksheet.write(15,1,str(hours) + ':'+ str(minutesLeft),center_alignment)
1261 
        worksheet.write(16,1,totalDispositions,center_alignment)
1262 
        worksheet.write(17,1,(contactableData+nonContactableData)/len(list(set(agentLoginList))),center_alignment)
1263 
        conn.close()
1264 
        datesql=FOLLOW_UP_QUERY_DURATION
1265 
        conn = getDbConnection()
1266 
 
1267 
        cursor = conn.cursor()
1268 
        cursor.execute(datesql)
1269 
        result = cursor.fetchall()
1270 
        for r in result:
1271 
            totalCallDuration= r[0]
1272 
        averageCallDuration=totalCallDuration/len(list(set(agentLoginList)))
1273 
        hours=averageCallDuration/3600
1274 
        minutesLeft=(averageCallDuration%3600)/60
1275 
        worksheet.write(18,1,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
1276 
        conn.close()
1277 
 
1278 
        datesql=FOLLOW_UP_QUERY_AHT
1279 
        conn = getDbConnection()
1280 
        cursor = conn.cursor()
1281 
        cursor.execute(datesql)
1282 
        result = cursor.fetchall()
1283 
        loginTime=0
1284 
        for r in result:
1285 
            loginTime+=r[0]
1286 
        averageLoginTime=loginTime/len(list(set(agentLoginList)))
1287 
        hours=averageLoginTime/3600
1288 
        minutesLeft=(averageLoginTime%3600)/60
1289 
        worksheet.write(19,1,len(list(set(agentLoginList))),center_alignment)
1290 
        conn.close()
1291 
 
1292 
        datesql=FOLLOW_UP_QUERY_AIT
1293 
        conn = getDbConnection()
1294 
        cursor = conn.cursor()
1295 
        cursor.execute(datesql)
1296 
        result = cursor.fetchall()
1297 
        loginTime=0
1298 
        for r in result:
1299 
            loginTime+=r[0]
1300 
        averageLoginTime=loginTime/len(list(set(agentLoginList)))
1301 
        hours=averageLoginTime/3600
1302 
        minutesLeft=(averageLoginTime%3600)/60
1303 
        worksheet.write(20,1,len(list(set(agentLoginList))),center_alignment)
15402 manas 1304 
    workbook.save(TMP_FILE)
15394 manas 1305 
 
1306 
def generateAgentWiseFollowupCallingReport():
1307 
 
1308 
    agentId=3
1309 
    rb = open_workbook(TMP_FILE,formatting_info=True)
1310 
    workbook = copy(rb)
1311 
    numberOfSheets=rb.nsheets
1312 
    sheet=rb.sheet_by_index(numberOfSheets-1)
1313 
    worksheet = workbook.get_sheet(numberOfSheets-1)
1314 
    boldStyle = xlwt.XFStyle()
1315 
    newStyle= xlwt.XFStyle()
1316 
    style = xlwt.XFStyle()
1317 
    pattern = xlwt.Pattern()
1318 
    pattern.pattern = xlwt.Pattern.SOLID_PATTERN
1319 
    pattern.pattern_fore_colour = xlwt.Style.colour_map['yellow']
1320 
    style.pattern = pattern
1321 
    f = xlwt.Font()
1322 
    f.bold = True
1323 
    boldStyle.font = f
1324 
 
1325 
    column = agentId
19160 manas 1326 
    row = 0
15394 manas 1327 
    worksheet.write(row,column-1,'Call Disposition Type',style)
1328 
    worksheet.write(row+1, column-1, 'Call Later', newStyle)
1329 
    worksheet.write(row+2,column-1, 'Ringing No Answer', newStyle)
1330 
    worksheet.write(row+3,column-1, 'Not Reachable', newStyle)
1331 
    worksheet.write(row+4,column-1, 'Switched Off', newStyle)
1332 
    worksheet.write(row+5,column-1, 'Retailer Not Interested', newStyle)
1333 
    worksheet.write(row+6,column-1, 'Already Profitmandi user', boldStyle)
1334 
    worksheet.write(row+7,column-1, 'Verified Link Sent', boldStyle)
15409 manas 1335 
    worksheet.write(row+8,column-1, 'Accessory Retailer', newStyle)
1336 
    worksheet.write(row+9,column-1, 'Service Center Retailer', newStyle)
1337 
    worksheet.write(row+10,column-1, 'Not retailer', style)
1338 
    worksheet.write(row+11,column-1, 'Recharge Retailer', style)
1339 
    worksheet.write(row+12,column-1, 'Contactable Data (%)', newStyle)
1340 
    worksheet.write(row+13,column-1, 'Non Contactable Data (%)', newStyle)
1341 
    worksheet.write(row+14,column-1, 'Total Dialed Out', style)
1342 
    worksheet.write(row+15,column-1, 'Login Time (In Hrs)', style)
1343 
    worksheet.write(row+16,column-1, 'Call Duration (In Hrs)', style)
1344 
    worksheet.write(row+17,column-1, 'Handling Time (In Hrs)', style)
1345 
    worksheet.write(row+18,column-1, 'Idle Time (In Hrs)', style)
15394 manas 1346 
    columnId=agentId
1347 
    while True:
17752 manas 1348 
            if agentId >50 :
15394 manas 1349 
                break
1350 
            else:
1351 
                datesql=AGENT_FOLLOW_UP_QUERY %(agentId)
1352 
                conn = getDbConnection()
1353 
 
1354 
                cursor = conn.cursor()
1355 
                cursor.execute(datesql)
1356 
                result = cursor.fetchall()
1357 
 
1358 
                currentList=[]
1359 
                contactableData=0
1360 
                nonContactableData=0
1361 
                if cursor.rowcount ==0:
1362 
                    agentId+=1
1363 
                    continue
1364 
                else:
15402 manas 1365 
 
15394 manas 1366 
                    for r in result:
19160 manas 1367 
                        row = followUpDispositionMap.get(r[0])+1
15402 manas 1368 
                        if followUpDispositionMap.get(r[0]) == 1 or followUpDispositionMap.get(r[0]) == 2 or followUpDispositionMap.get(r[0]) == 3 :
15394 manas 1369 
                            nonContactableData+=int(r[1])
1370 
                        else:
1371 
                            contactableData+=r[1]
1372 
                        currentList.append(str(followUpDispositionMap.get(r[0])+1))
1373 
                        column = agentId
15402 manas 1374 
                        worksheet.write(row, columnId, r[1],center_alignment)
15394 manas 1375 
                    remainingList = list(set(followUpList) - set(currentList))
1376 
 
1377 
                    for i,val in enumerate(remainingList):
19160 manas 1378 
                        row = int(val)+0
15394 manas 1379 
                        column = agentId
15402 manas 1380 
                        worksheet.write(row, columnId, 0,center_alignment)
15394 manas 1381 
                    totalDialedOut = contactableData+nonContactableData
15505 manas 1382 
                    if totalDialedOut>0:
19160 manas 1383 
                        worksheet.write(12,columnId,round((contactableData/float(totalDialedOut))*100,2),center_alignment)
1384 
                        worksheet.write(13,columnId,round((nonContactableData/float(totalDialedOut))*100,2),center_alignment)
1385 
                        worksheet.write(14,columnId,totalDialedOut,center_alignment)
15394 manas 1386 
                    conn.close()
1387 
 
1388 
                    name_query=AGENT_NAME_QUERY %(agentId)
1389 
                    conn = getDbConnection()
1390 
                    column=agentId
1391 
                    cursor = conn.cursor()
1392 
                    cursor.execute(name_query)
1393 
                    result = cursor.fetchall()
1394 
                    for r in result:
19160 manas 1395 
                        worksheet.write(0,columnId,r,style)
15394 manas 1396 
 
1397 
                    conn.close()
1398 
 
1399 
 
1400 
                    name_query=AGENT_FOLLOW_UP_QUERY_LOGIN_TIME %(agentId)
1401 
                    conn = getDbConnection()
1402 
                    column=agentId
1403 
                    cursor = conn.cursor()
1404 
                    cursor.execute(name_query)
1405 
                    result = cursor.fetchall()
1406 
                    loginTime=0
1407 
                    for r in result:
1408 
                        loginTime+=r[0].seconds
1409 
 
1410 
                    hours=loginTime/3600
1411 
                    minutesLeft=(loginTime%3600)/60
19160 manas 1412 
                    worksheet.write(15,columnId,str(hours)+':'+str(minutesLeft),center_alignment)
15394 manas 1413 
                    conn.close()
1414 
 
1415 
                    name_query=AGENT_FOLLOW_UP_QUERY_DURATION %(agentId)
1416 
                    conn = getDbConnection()
1417 
                    column=agentId
1418 
                    cursor = conn.cursor()
1419 
                    cursor.execute(name_query)
1420 
                    result = cursor.fetchall()
1421 
                    loginTime=0
1422 
                    for r in result:
1423 
                        loginTime+=r[0]
1424 
                    hours=loginTime/3600
1425 
                    minutesLeft=(loginTime%3600)/60
19160 manas 1426 
                    worksheet.write(16,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
15394 manas 1427 
                    conn.close()
1428 
 
15402 manas 1429 
                    name_query=AGENT_FOLLOW_UP_QUERY_AHT %(agentId)
1430 
                    conn = getDbConnection()
1431 
                    column=agentId
1432 
                    cursor = conn.cursor()
1433 
                    cursor.execute(name_query)
1434 
                    result = cursor.fetchall()
1435 
                    loginTime=0
1436 
                    for r in result:
1437 
                        loginTime+=r[0]
1438 
                    hours=loginTime/3600
1439 
                    minutesLeft=(loginTime%3600)/60
19160 manas 1440 
                    worksheet.write(17,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
15402 manas 1441 
                    conn.close()
1442 
 
1443 
                    name_query=AGENT_FOLLOW_UP_QUERY_AIT %(agentId)
1444 
                    conn = getDbConnection()
1445 
                    column=agentId
1446 
                    cursor = conn.cursor()
1447 
                    cursor.execute(name_query)
1448 
                    result = cursor.fetchall()
1449 
                    loginTime=0
1450 
                    for r in result:
1451 
                        loginTime+=r[0]
1452 
                    hours=loginTime/3600
1453 
                    minutesLeft=(loginTime%3600)/60
19160 manas 1454 
                    worksheet.write(18,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
15402 manas 1455 
                    conn.close()
1456 
 
15394 manas 1457 
                agentId+=1
1458 
                columnId+=1
1459 
    workbook.save(TMP_FILE)
1460 
 
1461 
def generateOnBoardingCallingReport():
1462 
    datesql=ONBOARDING_QUERY
1463 
    conn = getDbConnection()
1464 
 
1465 
    cursor = conn.cursor()
1466 
    cursor.execute(datesql)
1467 
    result = cursor.fetchall()
1468 
    rb = open_workbook(TMP_FILE,formatting_info=True)
1469 
    workbook = copy(rb)
1470 
 
1471 
    worksheet = workbook.add_sheet("Onboarding")
1472 
    boldStyle = xlwt.XFStyle()
1473 
    newStyle= xlwt.XFStyle()
1474 
    style = xlwt.XFStyle()
1475 
    pattern = xlwt.Pattern()
1476 
    pattern.pattern = xlwt.Pattern.SOLID_PATTERN
1477 
    pattern.pattern_fore_colour = xlwt.Style.colour_map['yellow']
1478 
    style.pattern = pattern
1479 
    f = xlwt.Font()
1480 
    f.bold = True
1481 
    boldStyle.font = f
1482 
 
1483 
    column = 0
1484 
    row = 0
1485 
    currentList=[]
15517 manas 1486 
    worksheet.write(0,0,'Call Disposition Type',style)
1487 
    worksheet.write(0,1,'Count',style)
1488 
    worksheet.write(1, column, 'Call Later', newStyle)
1489 
    worksheet.write(2,column, 'Ringing No Answer', newStyle)
1490 
    worksheet.write(3,column, 'Not Reachable', newStyle)
1491 
    worksheet.write(4,column, 'Switched Off', newStyle)
1492 
    worksheet.write(5,column, 'Successful', newStyle)
1493 
    worksheet.write(6,column, 'Contactable Data', newStyle)
1494 
    worksheet.write(7,column, 'Non Contactable Data', newStyle)
1495 
    worksheet.write(8,column, 'Agents Logged In', style)
1496 
    worksheet.write(9,column, 'Average Login Time (In Hrs)', style)
1497 
    worksheet.write(10,column, 'Total Dialed Out', style)
1498 
    worksheet.write(11,column, 'Average Dialed Out per agent', style)
1499 
    worksheet.write(12,column, 'Average Call Duration (In Hrs)', style)
1500 
    worksheet.write(13,column, 'Average Handling Time (In Hrs)', style)
1501 
    worksheet.write(14,column, 'Average Idle Time (In Hrs)', style)
15513 manas 1502 
    if len(result)==0:
1503 
        print 'No Data'
1504 
        pass
1505 
    else:
1506 
 
15517 manas 1507 
 
15513 manas 1508 
        contactableData=0
1509 
        nonContactableData=0
1510 
        totalDispositions=0
1511 
        for r in result:
1512 
            row = onBoardingDispositionMap.get(r[0])+1
1513 
            if onBoardingDispositionMap.get(r[0]) == 1 or onBoardingDispositionMap.get(r[0]) == 2 or onBoardingDispositionMap.get(r[0]) == 3:
1514 
                nonContactableData+=int(r[1])
1515 
            else:
1516 
                contactableData+=r[1]
1517 
            currentList.append(str(row))
1518 
            column = 1
1519 
            worksheet.write(row, column, r[1],center_alignment)
1520 
 
1521 
        remainingList = list(set(onBoardingList) - set(currentList))
1522 
 
1523 
        for i,val in enumerate(remainingList):
1524 
            row = int(val)
1525 
            column = 1
1526 
            worksheet.write(row, column, 0,center_alignment)
1527 
        totalDispositions=contactableData+nonContactableData
1528 
        worksheet.write(6,1,round((contactableData/float(totalDispositions))*100,2),center_alignment)
1529 
        worksheet.write(7,1,round((nonContactableData/float(totalDispositions))*100,2),center_alignment)
1530 
 
1531 
 
1532 
        conn.close()
1533 
        datesql=ONBOARDING_QUERY_LOGIN_TIME
1534 
        conn = getDbConnection()
1535 
 
1536 
        cursor = conn.cursor()
1537 
        cursor.execute(datesql)
1538 
        result = cursor.fetchall()
1539 
        agentLoginList=[]
1540 
        averageLoginTime=0
1541 
        loginTime=0
1542 
        for r in result:
1543 
            loginTime+=r[1].seconds
1544 
            agentLoginList.append(str(r[0]))
1545 
        averageLoginTime=loginTime/len(list(set(agentLoginList)))
1546 
        hours=averageLoginTime/3600
1547 
        minutesLeft=(averageLoginTime%3600)/60
1548 
        worksheet.write(8,1,len(list(set(agentLoginList))),center_alignment)
1549 
        worksheet.write(9,1,str(hours) + ':'+ str(minutesLeft),center_alignment)
1550 
        worksheet.write(10,1,totalDispositions,center_alignment)
1551 
        worksheet.write(11,1,(contactableData+nonContactableData)/len(list(set(agentLoginList))),center_alignment)
1552 
        conn.close()
1553 
        datesql=ONBOARDING_QUERY_DURATION
1554 
        conn = getDbConnection()
1555 
 
1556 
        cursor = conn.cursor()
1557 
        cursor.execute(datesql)
1558 
        result = cursor.fetchall()
1559 
        for r in result:
1560 
            totalCallDuration= r[0]
1561 
        averageCallDuration=totalCallDuration/len(list(set(agentLoginList)))
1562 
        hours=averageCallDuration/3600
1563 
        minutesLeft=(averageCallDuration%3600)/60
1564 
        worksheet.write(12,1,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
1565 
 
1566 
        conn.close()
1567 
        datesql=ONBOARDING_QUERY_AHT
1568 
        conn = getDbConnection()
1569 
 
1570 
        cursor = conn.cursor()
1571 
        cursor.execute(datesql)
1572 
        result = cursor.fetchall()
1573 
        for r in result:
1574 
            totalCallDuration= r[0]
1575 
        averageCallDuration=totalCallDuration/len(list(set(agentLoginList)))
1576 
        hours=averageCallDuration/3600
1577 
        minutesLeft=(averageCallDuration%3600)/60
1578 
        worksheet.write(13,1,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
1579 
        conn.close()
1580 
        datesql=ONBOARDING_QUERY_AIT
1581 
        conn = getDbConnection()
1582 
 
1583 
        cursor = conn.cursor()
1584 
        cursor.execute(datesql)
1585 
        result = cursor.fetchall()
1586 
        for r in result:
1587 
            totalCallDuration= r[0]
1588 
        averageCallDuration=totalCallDuration/len(list(set(agentLoginList)))
1589 
        hours=averageCallDuration/3600
1590 
        minutesLeft=(averageCallDuration%3600)/60
1591 
        worksheet.write(14,1,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
15516 manas 1592 
    workbook.save(TMP_FILE)
15394 manas 1593 
 
1594 
    #sendmail(["manas.kapoor@shop2020.in"], "", TMP_FILE, SUBJECT)
1595 
 
1596 
 
1597 
def generateAgentWiseOnboardingCallingReport():
1598 
 
1599 
    agentId=3
1600 
    rb = open_workbook(TMP_FILE,formatting_info=True)
1601 
    workbook = copy(rb)
1602 
    numberOfSheets=rb.nsheets
15402 manas 1603 
 
15394 manas 1604 
    sheet=rb.sheet_by_index(numberOfSheets-1)
1605 
    worksheet = workbook.get_sheet(numberOfSheets-1)
1606 
    boldStyle = xlwt.XFStyle()
1607 
    newStyle= xlwt.XFStyle()
1608 
    style = xlwt.XFStyle()
1609 
    pattern = xlwt.Pattern()
1610 
    pattern.pattern = xlwt.Pattern.SOLID_PATTERN
1611 
    pattern.pattern_fore_colour = xlwt.Style.colour_map['yellow']
1612 
    style.pattern = pattern
1613 
    f = xlwt.Font()
1614 
    f.bold = True
1615 
    boldStyle.font = f
1616 
 
1617 
    column = agentId
19160 manas 1618 
    row = 0
15394 manas 1619 
 
1620 
    worksheet.write(row,column-1,'Call Disposition Type',style)
1621 
    worksheet.write(row+1, column-1, 'Call Later', newStyle)
1622 
    worksheet.write(row+2,column-1, 'Ringing No Answer', newStyle)
1623 
    worksheet.write(row+3,column-1, 'Not Reachable', newStyle)
1624 
    worksheet.write(row+4,column-1, 'Switched Off', newStyle)
1625 
    worksheet.write(row+5,column-1, 'Successful', newStyle)
1626 
    worksheet.write(row+6,column-1, 'Contactable Data', newStyle)
1627 
    worksheet.write(row+7,column-1, 'Non Contactable Data', newStyle)
1628 
    worksheet.write(row+8,column-1, 'Total Dialed Out', style)
1629 
    worksheet.write(row+9,column-1, 'Login Time (In Hrs)', style)
1630 
    worksheet.write(row+10,column-1, 'Call Duration (In Hrs)', style)
15402 manas 1631 
    worksheet.write(row+11,column-1, 'Handling Time (In Hrs)', style)
1632 
    worksheet.write(row+12,column-1, 'Idle Time (In Hrs)', style)
15394 manas 1633 
    columnId=agentId
1634 
    while True:
17752 manas 1635 
            if agentId >50 :
15394 manas 1636 
                break
1637 
            else:
1638 
                datesql=AGENT_ONBOARDING_QUERY %(agentId)
1639 
                conn = getDbConnection()
1640 
 
1641 
                cursor = conn.cursor()
1642 
                cursor.execute(datesql)
1643 
                result = cursor.fetchall()
1644 
 
1645 
                currentList=[]
1646 
                contactableData=0
1647 
                nonContactableData=0
1648 
                if cursor.rowcount ==0:
1649 
                    agentId+=1
1650 
                    continue
1651 
                else:
1652 
                    for r in result:
19160 manas 1653 
                        row = onBoardingDispositionMap.get(r[0])+1
15394 manas 1654 
                        if onBoardingDispositionMap.get(r[0]) == 1 or onBoardingDispositionMap.get(r[0]) == 2 or onBoardingDispositionMap.get(r[0]) == 3 or followUpDispositionMap.get(r[0]) == 4:
1655 
                            nonContactableData+=int(r[1])
1656 
                        else:
1657 
                            contactableData+=r[1]
1658 
                        currentList.append(str(onBoardingDispositionMap.get(r[0])+1))
1659 
                        column = agentId
15402 manas 1660 
                        worksheet.write(row, columnId, r[1],center_alignment)
15394 manas 1661 
                    remainingList = list(set(onBoardingList) - set(currentList))
1662 
 
1663 
                    for i,val in enumerate(remainingList):
19160 manas 1664 
                        row = int(val)+0
15394 manas 1665 
                        column = agentId
15402 manas 1666 
                        worksheet.write(row, columnId, 0,center_alignment)
15394 manas 1667 
                    totalDialedOut = contactableData+nonContactableData
19160 manas 1668 
                    worksheet.write(6,columnId,round((contactableData/float(totalDialedOut))*100,2),center_alignment)
1669 
                    worksheet.write(7,columnId,round((nonContactableData/float(totalDialedOut))*100,2),center_alignment)
1670 
                    worksheet.write(8,columnId,totalDialedOut,center_alignment)
15394 manas 1671 
                    conn.close()
1672 
 
1673 
                    name_query=AGENT_NAME_QUERY %(agentId)
1674 
                    conn = getDbConnection()
1675 
                    column=agentId
1676 
                    cursor = conn.cursor()
1677 
                    cursor.execute(name_query)
1678 
                    result = cursor.fetchall()
1679 
                    for r in result:
19160 manas 1680 
                        worksheet.write(0,columnId,r,style)
15394 manas 1681 
                    conn.close()
1682 
 
1683 
 
15402 manas 1684 
                    name_query=AGENT_ONBOARDING_QUERY_LOGIN_TIME %(agentId)
15394 manas 1685 
                    conn = getDbConnection()
1686 
                    column=agentId
1687 
                    cursor = conn.cursor()
1688 
                    cursor.execute(name_query)
1689 
                    result = cursor.fetchall()
1690 
                    loginTime=0
1691 
                    for r in result:
1692 
                        loginTime+=r[0].seconds
1693 
 
1694 
                    hours=loginTime/3600
1695 
                    minutesLeft=(loginTime%3600)/60
19160 manas 1696 
                    worksheet.write(9,columnId,str(hours)+':'+str(minutesLeft),center_alignment)
15394 manas 1697 
                    conn.close()
1698 
 
15402 manas 1699 
                    name_query=AGENT_ONBOARDING_QUERY_DURATION %(agentId)
15394 manas 1700 
                    conn = getDbConnection()
1701 
                    column=agentId
1702 
                    cursor = conn.cursor()
1703 
                    cursor.execute(name_query)
1704 
                    result = cursor.fetchall()
1705 
                    loginTime=0
1706 
                    for r in result:
1707 
                        loginTime+=r[0]
1708 
                    hours=loginTime/3600
1709 
                    minutesLeft=(loginTime%3600)/60
19160 manas 1710 
                    worksheet.write(10,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
15394 manas 1711 
                    conn.close()
1712 
 
15402 manas 1713 
                    name_query=AGENT_ONBOARDING_QUERY_AHT %(agentId)
1714 
                    conn = getDbConnection()
1715 
                    column=agentId
1716 
                    cursor = conn.cursor()
1717 
                    cursor.execute(name_query)
1718 
                    result = cursor.fetchall()
1719 
                    loginTime=0
1720 
                    for r in result:
1721 
                        loginTime+=r[0]
1722 
                    hours=loginTime/3600
1723 
                    minutesLeft=(loginTime%3600)/60
19160 manas 1724 
                    worksheet.write(11,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
15402 manas 1725 
                    conn.close()
1726 
 
1727 
                    name_query=AGENT_ONBOARDING_QUERY_AIT %(agentId)
1728 
                    conn = getDbConnection()
1729 
                    column=agentId
1730 
                    cursor = conn.cursor()
1731 
                    cursor.execute(name_query)
1732 
                    result = cursor.fetchall()
1733 
                    loginTime=0
1734 
                    for r in result:
1735 
                        loginTime+=r[0]
1736 
                    hours=loginTime/3600
1737 
                    minutesLeft=(loginTime%3600)/60
19160 manas 1738 
                    worksheet.write(12,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
15402 manas 1739 
                    conn.close()
1740 
 
15394 manas 1741 
                agentId+=1
1742 
                columnId+=1
1743 
    workbook.save(TMP_FILE)
1744 
 
1745 
def main():
18382 manas 1746 
    parser = optparse.OptionParser()
1747 
    parser.add_option("-T", "--reporttype", dest="reporttype",
1748 
                      default="crmoutbound",
1749 
                      type="str", help="To avoid not needed order backups",
1750 
                      metavar="REPORTTYPE")
1751 
    (options, args) = parser.parse_args()
1752 
    if options.reporttype == 'crmoutbound':
1753 
        generateFreshCallingReport()
1754 
        generateAgentWiseFreshCallingReport()
1755 
        generateFollowUpCallingReport()
1756 
        generateAgentWiseFollowupCallingReport()
1757 
        generateOnBoardingCallingReport()
1758 
        generateAgentWiseOnboardingCallingReport()
18398 manas 1759 
        #sendmail(["manas.kapoor@shop2020.in"], "", TMP_FILE, SUBJECT)
18382 manas 1760 
        sendmail(["amit.sirohi@shop2020.in","rajneesh.arora@saholic.com","ritesh.chauhan@shop2020.in", "shailesh.kumar@shop2020.in","utkarsh@coreoutsourcingservices.com","gupta.varun@coreoutsourcingservices.com","manas.kapoor@shop2020.in"], "", TMP_FILE, SUBJECT)
1761 
    if options.reporttype == 'retention':
1762 
        generateAccessoriesCartReport()
18495 manas 1763 
        generateAccessoriesTabsReport()
18673 manas 1764 
        generateAccessoriesOrderReport()
18383 manas 1765 
        #sendmailretention(["manas.kapoor@shop2020.in"], "", RET_FILE, RET_SUBJECT)
1766 
        sendmailretention(["amit.sirohi@shop2020.in","rajneesh.arora@saholic.com", "shailesh.kumar@shop2020.in","chaitnaya.vats@shop2020.in","manas.kapoor@shop2020.in"], "", RET_FILE, RET_SUBJECT)
15402 manas 1767 
 
15394 manas 1768 
def sendmail(email, message, fileName, title):
1769 
    if email == "":
1770 
        return
1771 
    mailServer = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)
1772 
    mailServer.ehlo()
1773 
    mailServer.starttls()
1774 
    mailServer.ehlo()
1775 
 
1776 
    # Create the container (outer) email message.
1777 
    msg = MIMEMultipart()
1778 
    msg['Subject'] = title
1779 
    msg.preamble = title
17021 manas 1780 
    message="Total Activations(Fresh + Followup) :" + str(TotalActivations)
1781 
    message1="Links Converted(Fresh) :" + str(Converted)
15441 manas 1782 
    message2="Total Agents Logged In :" + str(AgentsLoggedIn)
15442 manas 1783 
    html_msg = MIMEText(message+"<br>"+message1+"<br>"+message2, 'html')
15394 manas 1784 
    msg.attach(html_msg)
1785 
 
1786 
    fileMsg = MIMEBase('application', 'vnd.ms-excel')
1787 
    fileMsg.set_payload(file(TMP_FILE).read())
1788 
    encoders.encode_base64(fileMsg)
1789 
    fileMsg.add_header('Content-Disposition', 'attachment;filename=' + fileName)
1790 
    msg.attach(fileMsg)
15402 manas 1791 
 
18382 manas 1792 
    MAILTO = ['amit.sirohi@shop2020.in','rajneesh.arora@saholic.com', 'shailesh.kumar@shop2020.in','gupta.varun@coreoutsourcingservices.com','utkarsh@coreoutsourcingservices.com','manas.kapoor@shop2020.in']
17021 manas 1793 
    #MAILTO = ['manas.kapoor@saholic.com']
15394 manas 1794 
    mailServer.login(SENDER, PASSWORD)
17440 manish.sha 1795 
    try:
1796 
        mailServer.sendmail(SENDER, MAILTO, msg.as_string())
1797 
    except:
1798 
        m = Email('localhost')
1799 
        mFrom = "dtr@shop2020.in"
1800 
        m.setFrom(mFrom)
1801 
        for receipient in MAILTO:
1802 
            m.addRecipient(receipient)
1803 
        m.setSubject(title)
1804 
        m.setHtmlBody(message+"<br>"+message1+"<br>"+message2)
1805 
        m.send()
15394 manas 1806 
 
18407 manas 1807 
def getAccsCartHtml():
1808 
    heading = "Accessories Cart Users(Project 1)"
1809 
    message="No. of Agents " + str(ACCS_CART_LOGIN)
1810 
    message1="Total Calls attempted " + str(ACCS_CART_TOTAL)
1811 
    message2="Total Successful calls " + str(ACCS_CART_SUCCESSFUL)
1812 
    message3="Will Order "+ str(ACCS_CART_WILL_PLACE_ORDER)
18532 manas 1813 
    if ACCS_CART_TOTAL!=0:
1814 
        message4="Will Order % " +str(round((ACCS_CART_WILL_PLACE_ORDER/float(ACCS_CART_TOTAL))*100,2))
1815 
    else:
1816 
        message4="Will Order % 0"
18407 manas 1817 
    return "<u>" + heading + "</u><br>" +message+"<br>"+message1+"<br>"+message2+"<br>"+message3+"<br>"+message4+"<br>"
18382 manas 1818 
 
18495 manas 1819 
def getAccsTabHtml():
1820 
    heading = "Accessories Active Users(Project 2)"
1821 
    message="No. of Agents " + str(ACCS_TAB_LOGIN)
1822 
    message1="Total Calls attempted " + str(ACCS_TAB_TOTAL)
1823 
    message2="Total Successful calls " + str(ACCS_TAB_SUCCESSFUL)
1824 
    message3="Will Order "+ str(ACCS_TAB_WILL_PLACE_ORDER)
18532 manas 1825 
    if ACCS_TAB_TOTAL!=0:
1826 
        message4="Will Order % " +str(round((ACCS_TAB_WILL_PLACE_ORDER/float(ACCS_TAB_TOTAL))*100,2))
1827 
    else:
1828 
        message4="Will Order % 0"
18495 manas 1829 
    return "<br><br><u>" + heading + "</u><br>" +message+"<br>"+message1+"<br>"+message2+"<br>"+message3+"<br>"+message4+"<br>"
1830 
 
18407 manas 1831 
def getAccsCartOrders():
18421 manas 1832 
    datesql = "select  date(now() - interval 1 day), count(distinct user_id),count(distinct order_id), sum(quantity),sum(amount_paid) from allorder where store_id='spice' and category='Accs' and date(created_on)=date(now() - interval 1 day) and user_id in (select user_id from usercrmcallingdata where project_id=1 and date(created)>=date(now()- interval 7 day));"
18407 manas 1833 
    conn = getDbConnection()
1834 
    cursor = conn.cursor()
1835 
    cursor.execute(datesql)
1836 
    result = cursor.fetchall()
18421 manas 1837 
    inputs = "No of users who placed orders on Date " + str(result[0][0]) + "<br> Number of Users " + str(result[0][1])+ " <br> Total Orders " + str(result[0][2]) + "<br>Total Quantity " + str(result[0][3]) + "<br>Total Value " + str(result[0][4]) + "<br>"
18407 manas 1838 
    conn.close()
1839 
    return inputs
1840 
 
18495 manas 1841 
def getAccsTabOrders():
1842 
    datesql = "select  date(now() - interval 1 day), count(distinct user_id),count(distinct order_id), sum(quantity),sum(amount_paid) from allorder where store_id='spice' and category='Accs' and date(created_on)=date(now() - interval 1 day) and user_id in (select user_id from usercrmcallingdata where project_id=2 and date(created)>=date(now()- interval 7 day));"
1843 
    conn = getDbConnection()
1844 
    cursor = conn.cursor()
1845 
    cursor.execute(datesql)
1846 
    result = cursor.fetchall()
1847 
    inputs = "No of users who placed orders on Date " + str(result[0][0]) + "<br> Number of Users " + str(result[0][1])+ " <br> Total Orders " + str(result[0][2]) + "<br>Total Quantity " + str(result[0][3]) + "<br>Total Value " + str(result[0][4]) + "<br>"
1848 
    conn.close()
1849 
    return inputs
1850 
 
18407 manas 1851 
def getAccsCartProductPricingInput():
18421 manas 1852 
    datesql = "select product_input,pricing_input from productpricinginputs where date(created)=date(now() - interval 1 day) and project_id=1 and product_input not like '';"
18407 manas 1853 
    conn = getDbConnection()
1854 
    cursor = conn.cursor()
1855 
    cursor.execute(datesql)
1856 
    result = cursor.fetchall()
1857 
    inputs="Product Pricing Inputs"
1858 
    for r in result:
1859 
        inputs = inputs + "<br>" + r[0] + "-----" + str(r[1])
1860 
    conn.close()
1861 
    return inputs
18495 manas 1862 
 
1863 
def getAccsTabProductPricingInput():
1864 
    datesql = "select product_input,pricing_input from productpricinginputs where date(created)=date(now() - interval 1 day) and project_id=2 and product_input not like '';"
1865 
    conn = getDbConnection()
1866 
    cursor = conn.cursor()
1867 
    cursor.execute(datesql)
1868 
    result = cursor.fetchall()
1869 
    inputs="Product Pricing Inputs"
1870 
    for r in result:
1871 
        inputs = inputs + "<br>" + r[0] + "-----" + str(r[1])
1872 
    conn.close()
1873 
    return inputs
1874 
 
18673 manas 1875 
def getAccsOrdersHtml():
1876 
    heading = "Accessories Orders(Project 3)"
1877 
    message="No. of Agents " + str(ACCS_ORDER_LOGIN)
1878 
    message1="Total Calls attempted " + str(ACCS_ORDER_TOTAL)
1879 
    message2="Total Successful calls " + str(ACCS_ORDER_SUCCESSFUL)
1880 
    return "<br><br><u>" + heading + "</u><br>" +message+"<br>"+message1+"<br>"+message2+"<br>"
1881 
 
1882 
def getAccsOrdersProject():
1883 
    datesql = "select  date(now() - interval 1 day), count(distinct user_id),count(distinct order_id), sum(quantity),sum(amount_paid) from allorder where store_id='spice' and category='Accs' and date(created_on)=date(now() - interval 1 day) and user_id in (select user_id from usercrmcallingdata where project_id=3 and date(created)>=date(now()- interval 7 day));"
1884 
    conn = getDbConnection()
1885 
    cursor = conn.cursor()
1886 
    cursor.execute(datesql)
1887 
    result = cursor.fetchall()
1888 
    inputs = "No of users who placed orders on Date " + str(result[0][0]) + "<br> Number of Users " + str(result[0][1])+ " <br> Total Orders " + str(result[0][2]) + "<br>Total Quantity " + str(result[0][3]) + "<br>Total Value " + str(result[0][4]) + "<br>"
1889 
    conn.close()
1890 
    return inputs
1891 
 
1892 
def getAccsOrdersProductPricingInput():
1893 
    datesql = "select product_input,pricing_input from productpricinginputs where date(created)=date(now() - interval 1 day) and project_id=3 and product_input not like '';"
1894 
    conn = getDbConnection()
1895 
    cursor = conn.cursor()
1896 
    cursor.execute(datesql)
1897 
    result = cursor.fetchall()
1898 
    inputs="Product Pricing Inputs"
1899 
    for r in result:
1900 
        inputs = inputs + "<br>" + r[0] + "-----" + str(r[1])
1901 
    conn.close()
1902 
    return inputs
18407 manas 1903 
 
18382 manas 1904 
def sendmailretention(email, message, fileName, title):
1905 
    if email == "":
1906 
        return
1907 
    mailServer = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)
1908 
    mailServer.ehlo()
1909 
    mailServer.starttls()
1910 
    mailServer.ehlo()
1911 
 
1912 
    # Create the container (outer) email message.
1913 
    msg = MIMEMultipart()
1914 
    msg['Subject'] = title
1915 
    msg.preamble = title
1916 
 
18407 manas 1917 
    msg.attach(MIMEText(getAccsCartHtml(), 'html'))
1918 
    msg.attach(MIMEText(getAccsCartOrders(),'html'))
1919 
    msg.attach(MIMEText(getAccsCartProductPricingInput(),'html'))
1920 
 
18495 manas 1921 
    msg.attach(MIMEText(getAccsTabHtml(), 'html'))
1922 
    msg.attach(MIMEText(getAccsTabOrders(),'html'))
1923 
    msg.attach(MIMEText(getAccsTabProductPricingInput(),'html'))
1924 
 
18673 manas 1925 
    msg.attach(MIMEText(getAccsOrdersHtml(), 'html'))
1926 
    msg.attach(MIMEText(getAccsOrdersProject(),'html'))
1927 
    msg.attach(MIMEText(getAccsOrdersProductPricingInput(),'html'))
18495 manas 1928 
 
18382 manas 1929 
    fileMsg = MIMEBase('application', 'vnd.ms-excel')
1930 
    fileMsg.set_payload(file(RET_FILE).read())
1931 
    encoders.encode_base64(fileMsg)
1932 
    fileMsg.add_header('Content-Disposition', 'attachment;filename=' + fileName)
1933 
    msg.attach(fileMsg)
1934 
 
1935 
    MAILTO = ['amit.sirohi@shop2020.in','rajneesh.arora@saholic.com', 'shailesh.kumar@shop2020.in','chaitnaya.vats@shop2020.in','manas.kapoor@shop2020.in']
1936 
    #MAILTO = ['manas.kapoor@saholic.com']
1937 
    mailServer.login(SENDER, PASSWORD)
1938 
    try:
1939 
        mailServer.sendmail(SENDER, MAILTO, msg.as_string())
1940 
    except:
1941 
        m = Email('localhost')
1942 
        mFrom = "dtr@shop2020.in"
1943 
        m.setFrom(mFrom)
1944 
        for receipient in MAILTO:
1945 
            m.addRecipient(receipient)
1946 
        m.setSubject(title)
1947 
        m.send()
1948 
 
15394 manas 1949 
if __name__ == '__main__':
18059 manas 1950 
    main()