Subversion Repositories SmartDukaan

Rev

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

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