Subversion Repositories SmartDukaan

Rev

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