Subversion Repositories SmartDukaan

Rev

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