Subversion Repositories SmartDukaan

Rev

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