Subversion Repositories SmartDukaan

Rev

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