WebSVN – SmartDukaan – Diff – //trunk/PyDTR/src/dtr/reports/crmoutbound.py

 con = None
 SENDER = "cnc.center@shop2020.in"
 PASSWORD = "5h0p2o2o"
-SUBJECT = "CRM Outbound Report for" + date.today().isoformat()
+SUBJECT = "CRM Outbound Report for " + str(date.today() - timedelta(days=3))
 SMTP_SERVER = "smtp.gmail.com"
 SMTP_PORT = 587
 date_format = xlwt.XFStyle()
 date_format.num_format_str = 'yyyy/mm/dd'
 datetime_format = xlwt.XFStyle()
 datetime_format.num_format_str = 'yyyy/mm/dd HH:MM AM/PM'
 default_format = xlwt.XFStyle()
 freshList=['1','2','3','4','5','6','7','8','9','10','11','12','13']
-followUpList=['1','2','3','4','5','6','7']
+followUpList=['1','2','3','4','5','6']
 onBoardingList=['1','2','3','4','5']
 dispositionMap = {  'call_later':0,
                     'ringing_no_answer':1,
                     'not_reachable':2,
                     'switch_off':3,
                     'not_reachable':2,
                     'switch_off':3,
                     'retailer_not_interested':4,
                     'alreadyuser':5,
                     'verified_link_sent':6,
-                    'not_retailer':7,
+                }
 onBoardingDispositionMap = {  'call_later':0,
                     'ringing_no_answer':1,
                     'not_reachable':2,
                     'switch_off':3,
                     'onboarded':4,
+                }
+# FRESH_QUERY="""
+# select call_disposition,count(1) from
+# (select * from (select * from callhistory where date(created)=date(now())
+# and call_type ='fresh' order by created desc)
+# as a group by retailer_id)
+# a1 group by date(created), call_disposition;
+# """
 FRESH_QUERY="""
 select call_disposition,count(1) from
-(select * from (select * from callhistory where date(created)=date(now())
+(select * from (select * from callhistory where date(created)=date(now() - INTERVAL 3 DAY)
 and call_type ='fresh' order by created desc)
 as a group by retailer_id)
 a1 group by date(created), call_disposition;
 """
 AGENT_FRESH_QUERY="""
 select call_disposition,count(1) from
-(select * from (select * from callhistory where date(created)=date(now())
+(select * from (select * from callhistory where date(created)=date(now() - INTERVAL 3 DAY)
 and call_type ='fresh' and agent_id=%s order by created desc)
 as a group by retailer_id)
 a1 group by date(created), call_disposition,agent_id;
 """
 AGENT_NAME_QUERY="""
 select name from agents where id=%s
 """
 FRESH_QUERY_LOGIN_TIME="""
-select agent_id,TIMEDIFF(logoutTime,loginTime) from agentlogintimings where role='fresh' and date(created)=date(now());
+select agent_id,TIMEDIFF(logoutTime,loginTime) from agentlogintimings where role='fresh' and date(created)=date(now() - INTERVAL 3 DAY);
 """
 FRESH_QUERY_DURATION="""
-select sum(duration_sec) from callhistory where call_type ='fresh' and date(created)=date(now());
+select sum(duration_sec) from callhistory where call_type ='fresh' and date(created)=date(now() - INTERVAL 3 DAY);
 """
+FRESH_QUERY_AHT="""
+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 3 DAY );
+"""
+FRESH_QUERY_AIT="""
+select timestampdiff(second, last_action_time, created) from fetchdatahistory where call_type='fresh' and date(created)=date(now() - INTERVAL 3 DAY);
+"""
+FRESH_QUERY_LINKS_CONVERTED="""
+select count(*) from retailerlinks where date(activated) = date(created) and date(created)=date(now() - interval 3 day);
+"""
+FRESH_QUERY_LINKS_NOT_CONVERTED="""
+select count(*) from retailerlinks where date(activated) is null and date(created)=date(now() - interval 3 day);
+"""
 AGENT_FRESH_QUERY_LOGIN_TIME="""
-select TIMEDIFF(logoutTime,loginTime) from agentlogintimings where role='fresh' and date(created)=date(now()) and agent_id=%s;
+select TIMEDIFF(logoutTime,loginTime) from agentlogintimings where role='fresh' and date(created)=date(now() - INTERVAL 3 DAY) and agent_id=%s;
 """
 AGENT_FRESH_QUERY_DURATION="""
-select sum(duration_sec) from callhistory where call_type ='fresh' and date(created)=date(now()) and agent_id=%s;
+select sum(duration_sec) from callhistory where call_type ='fresh' and date(created)=date(now() - INTERVAL 3 DAY) and agent_id=%s;
+"""
+AGENT_FRESH_QUERY_AHT="""
+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 3 DAY) and agent_id=%s;
 """
+AGENT_FRESH_QUERY_AIT="""
+select timestampdiff(second, last_action_time, created) from fetchdatahistory where call_type='fresh' and date(created)=date(now() - INTERVAL 3 DAY) and agent_id=%s;
+"""
+AGENT_FRESH_QUERY_LINKS_CONVERTED="""
+select count(*) from retailerlinks where date(activated) = date(created) and date(created)=date(now() - interval 3 day) and agent_id=%s;
+"""
+AGENT_FRESH_QUERY_LINKS_NOT_CONVERTED="""
+select count(*) from retailerlinks where date(activated) is null and date(created)=date(now() - interval 3 day) and agent_id=%s;
+"""
 FOLLOW_UP_QUERY="""
 select call_disposition,count(1) from
-(select * from (select * from callhistory where date(created)=date(now())
+(select * from (select * from callhistory where date(created)=date(now() - INTERVAL 3 DAY)
 and call_type ='followup' order by created desc)
 as a group by retailer_id)
 a1 group by date(created), call_disposition;
 """
 AGENT_FOLLOW_UP_QUERY="""
 select call_disposition,count(1) from
-(select * from (select * from callhistory where date(created)=date(now())
+(select * from (select * from callhistory where date(created)=date(now() - INTERVAL 3 DAY)
 and call_type ='followup' and agent_id=%s order by created desc)
 as a group by retailer_id)
 a1 group by date(created), call_disposition,agent_id;
 """
 FOLLOW_UP_QUERY_LOGIN_TIME="""
-select agent_id,TIMEDIFF(logoutTime,loginTime) from agentlogintimings where role='followup' and date(created)=date(now());
+select agent_id,TIMEDIFF(logoutTime,loginTime) from agentlogintimings where role='followup' and date(created)=date(now() - INTERVAL 3 DAY);
 """
 FOLLOW_UP_QUERY_DURATION="""
-select sum(duration_sec) from callhistory where call_type ='followup' and date(created)=date(now());
+select sum(duration_sec) from callhistory where call_type ='followup' and date(created)=date(now() - INTERVAL 3 DAY);
+"""
+FOLLOW_UP_QUERY_AHT="""
+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 3 DAY );
+"""
+FOLLOW_UP_QUERY_AIT="""
+select timestampdiff(second, last_action_time, created) from fetchdatahistory where call_type='followup' and date(created)=date(now() - INTERVAL 3 DAY);
 """
 AGENT_FOLLOW_UP_QUERY_LOGIN_TIME="""
-select TIMEDIFF(logoutTime,loginTime) from agentlogintimings where role='followup' and date(created)=date(now()) and agent_id=%s;
+select TIMEDIFF(logoutTime,loginTime) from agentlogintimings where role='followup' and date(created)=date(now() - INTERVAL 3 DAY) and agent_id=%s;
 """
 AGENT_FOLLOW_UP_QUERY_DURATION="""
-select sum(duration_sec) from callhistory where call_type ='followup' and date(created)=date(now()) and agent_id=%s;
+select sum(duration_sec) from callhistory where call_type ='followup' and date(created)=date(now() - INTERVAL 3 DAY) and agent_id=%s;
+"""
+AGENT_FOLLOW_UP_QUERY_AHT="""
+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 3 DAY) and agent_id=%s;
 """
+AGENT_FOLLOW_UP_QUERY_AIT="""
+select timestampdiff(second, last_action_time, created) from fetchdatahistory where call_type='followup' and date(created)=date(now() - INTERVAL 3 DAY)  and agent_id=%s;
+"""
 ONBOARDING_QUERY="""
 select call_disposition,count(1) from
-(select * from (select * from callhistory where date(created)=date(now())
+(select * from (select * from callhistory where date(created)=date(now() - INTERVAL 3 DAY)
 and call_type ='onboarding' order by created desc)
 as a group by retailer_id)
 a1 group by date(created), call_disposition;
 """
+ONBOARDING_QUERY_LOGIN_TIME="""
+select agent_id,TIMEDIFF(logoutTime,loginTime) from agentlogintimings where role='onboarding' and date(created)=date(now() - INTERVAL 3 DAY);
+"""
+ONBOARDING_QUERY_DURATION="""
+select sum(duration_sec) from callhistory where call_type ='onboarding' and date(created)=date(now() - INTERVAL 3 DAY);
+"""
+ONBOARDING_QUERY_AHT="""
+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 3 DAY );
+"""
+ONBOARDING_QUERY_AIT="""
+select timestampdiff(second, last_action_time, created) from fetchdatahistory where call_type='onboarding' and date(created)=date(now() - INTERVAL 3 DAY);
+"""
 AGENT_ONBOARDING_QUERY="""
 select call_disposition,count(1) from
-(select * from (select * from callhistory where date(created)=date(now())
+(select * from (select * from callhistory where date(created)=date(now() - INTERVAL 3 DAY)
 and call_type ='onboarding' and agent_id=%s order by created desc)
 as a group by retailer_id)
 a1 group by date(created), call_disposition,agent_id;
 """
-ONBOARDING_QUERY_LOGIN_TIME="""
-select agent_id,TIMEDIFF(logoutTime,loginTime) from agentlogintimings where role='onboarding' and date(created)=date(now());
-"""
-ONBOARDING_QUERY_DURATION="""
-select sum(duration_sec) from callhistory where call_type ='onboarding' and date(created)=date(now());
-"""
 AGENT_ONBOARDING_QUERY_LOGIN_TIME="""
-select TIMEDIFF(logoutTime,loginTime) from agentlogintimings where role='onboarding' and date(created)=date(now()) and agent_id=%s;
+select TIMEDIFF(logoutTime,loginTime) from agentlogintimings where role='onboarding' and date(created)=date(now() - INTERVAL 3 DAY) and agent_id=%s;
 """
 AGENT_ONBOARDING_QUERY_DURATION="""
-select sum(duration_sec) from callhistory where call_type ='onboarding' and date(created)=date(now()) and agent_id=%s;
+select sum(duration_sec) from callhistory where call_type ='onboarding' and date(created)=date(now() - INTERVAL 3 DAY) and agent_id=%s;
+"""
+AGENT_ONBOARDING_QUERY_AHT="""
+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 3 DAY ) and agent_id=%s;
+"""
+AGENT_ONBOARDING_QUERY_AIT="""
+select timestampdiff(second, last_action_time, created) from fetchdatahistory where call_type='onboarding' and date(created)=date(now() - INTERVAL 3 DAY)  and agent_id=%s;
+"""
+AGENT_ONBOARDING_QUERY_AIT="""
+select timestampdiff(second, last_action_time, created) from fetchdatahistory where call_type='onboarding' and date(created)=date(now() - INTERVAL 3 DAY) and agent_id=%s;
 """
+center_alignment=xlwt.easyxf("align: horiz center")
 def getDbConnection():
     return MySQLdb.connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME)
     worksheet.write(10,column, 'Verified Link Sent', boldStyle)
     worksheet.write(11,column, 'Accessory Retailer', newStyle)
     worksheet.write(12,column, 'Service Center Retailer', newStyle)
     worksheet.write(13,column, 'Not a Retailer', newStyle)
     worksheet.write(14,column, 'Recharge Retailer', newStyle)
-    worksheet.write(15,column, 'Contactable Data', newStyle)
+    worksheet.write(15,column, 'Contactable Data (%)', newStyle)
-    worksheet.write(16,column, 'Non Contactable Data', newStyle)
+    worksheet.write(16,column, 'Non Contactable Data (%)', newStyle)
     worksheet.write(17,column, 'Agents Logged In', style)
     worksheet.write(18,column, 'Average Login Time (In Hrs)', style)
     worksheet.write(19,column, 'Total Dialed Out', style)
     worksheet.write(20,column, 'Average Dialed Out per agent', style)
     worksheet.write(21,column, 'Average Call Duration (In Hrs)', style)
+    worksheet.write(22,column, 'Average Handling Time (In Hrs)', style)
+    worksheet.write(23,column, 'Average Idle Time (In Hrs)', style)
+    worksheet.write(24,column, 'Links Converted', style)
+    worksheet.write(25,column, 'Link sent yet to be converted', style)
     contactableData=0
     nonContactableData=0
     for r in result:
         row = dispositionMap.get(r[0])+1
             nonContactableData+=int(r[1])
         else:
             contactableData+=r[1]
         currentList.append(str(row))
         column = 1
-        worksheet.write(row, column, r[1])
+        worksheet.write(row, column, r[1],center_alignment)
     remainingList = list(set(freshList) - set(currentList))
     for i,val in enumerate(remainingList):
         row = int(val)
         column = 1
-        worksheet.write(row, column, 0)
+        worksheet.write(row, column, 0,center_alignment)
     totalDispositions=contactableData+nonContactableData
-    worksheet.write(15,1,(contactableData/float(totalDispositions))*100)
+    worksheet.write(15,1,round((contactableData/float(totalDispositions))*100,2),center_alignment)
-    worksheet.write(16,1,(nonContactableData/float(totalDispositions))*100)
+    worksheet.write(16,1,round((nonContactableData/float(totalDispositions))*100,2),center_alignment)
     conn.close()
     datesql=FRESH_QUERY_LOGIN_TIME
     conn = getDbConnection()
         loginTime+=r[1].seconds
         agentLoginList.append(str(r[0]))
     averageLoginTime=loginTime/len(list(set(agentLoginList)))
     hours=averageLoginTime/3600
     minutesLeft=(averageLoginTime%3600)/60
-    worksheet.write(17,1,len(list(set(agentLoginList))))
+    worksheet.write(17,1,len(list(set(agentLoginList))),center_alignment)
-    worksheet.write(18,1,str(hours) + ':'+ str(minutesLeft))
+    worksheet.write(18,1,str(hours) + ':'+ str(minutesLeft),center_alignment)
-    worksheet.write(19,1,totalDispositions)
+    worksheet.write(19,1,totalDispositions,center_alignment)
-    print 'list' + str(agentLoginList)
-    print 'Avg' + str(totalDispositions/float(len(list(set(agentLoginList)))))
-    print 'list' + str(list(set(agentLoginList)))
-    worksheet.write(20,1,totalDispositions/float(len(list(set(agentLoginList)))))
+    worksheet.write(20,1,totalDispositions/float(len(list(set(agentLoginList)))),center_alignment)
     conn.close()
     datesql=FRESH_QUERY_DURATION
     conn = getDbConnection()
     cursor = conn.cursor()
     for r in result:
         totalCallDuration= r[0]
     averageCallDuration=totalCallDuration/len(list(set(agentLoginList)))
     hours=averageCallDuration/3600
     minutesLeft=(averageCallDuration%3600)/60
-    worksheet.write(21,1,str(int(hours))+':'+str(int(minutesLeft)))
+    worksheet.write(21,1,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
+    conn.close()
+    datesql=FRESH_QUERY_AHT
+    conn = getDbConnection()
+    cursor = conn.cursor()
+    cursor.execute(datesql)
+    result = cursor.fetchall()
+    loginTime=0
+    for r in result:
+        loginTime+=r[0]
+    averageLoginTime=loginTime/len(list(set(agentLoginList)))
+    hours=averageLoginTime/3600
+    minutesLeft=(averageLoginTime%3600)/60
+    worksheet.write(22,1,len(list(set(agentLoginList))),center_alignment)
+    conn.close()
+    datesql=FRESH_QUERY_AIT
+    conn = getDbConnection()
+    cursor = conn.cursor()
+    cursor.execute(datesql)
+    result = cursor.fetchall()
+    loginTime=0
+    for r in result:
+        loginTime+=r[0]
+    averageLoginTime=loginTime/len(list(set(agentLoginList)))
+    hours=averageLoginTime/3600
+    minutesLeft=(averageLoginTime%3600)/60
+    worksheet.write(23,1,len(list(set(agentLoginList))),center_alignment)
+    conn.close()
+    datesql=FRESH_QUERY_LINKS_CONVERTED
+    conn = getDbConnection()
+    cursor = conn.cursor()
+    cursor.execute(datesql)
+    result = cursor.fetchall()
+    loginTime=0
+    for r in result:
+        worksheet.write(24,1,r[0],center_alignment)
+    conn.close()
+    datesql=FRESH_QUERY_LINKS_NOT_CONVERTED
+    conn = getDbConnection()
+    cursor = conn.cursor()
+    cursor.execute(datesql)
+    result = cursor.fetchall()
+    loginTime=0
+    for r in result:
+        worksheet.write(25,1,r[0],center_alignment)
     workbook.save(TMP_FILE)
     #sendmail(["manas.kapoor@shop2020.in"], "", TMP_FILE, SUBJECT)
 def generateAgentWiseFreshCallingReport():
     agentId=3
-    #global workbook
     rb = open_workbook(TMP_FILE,formatting_info=True)
     workbook = copy(rb)
     numberOfSheets=rb.nsheets
-    print 'Number of sheets ' + str(numberOfSheets)
     sheet=rb.sheet_by_index(numberOfSheets-1)
     worksheet = workbook.get_sheet(numberOfSheets-1)
     boldStyle = xlwt.XFStyle()
     newStyle= xlwt.XFStyle()
     worksheet.write(row+10,column-1, 'Verified Link Sent', boldStyle)
     worksheet.write(row+11,column-1, 'Accessory Retailer', newStyle)
     worksheet.write(row+12,column-1, 'Service Center Retailer', newStyle)
     worksheet.write(row+13,column-1, 'Not a Retailer', newStyle)
     worksheet.write(row+14,column-1, 'Recharge Retailer', newStyle)
-    worksheet.write(row+15,column-1, 'Contactable Data', newStyle)
+    worksheet.write(row+15,column-1, 'Contactable Data (%)', newStyle)
-    worksheet.write(row+16,column-1, 'Non Contactable Data', newStyle)
+    worksheet.write(row+16,column-1, 'Non Contactable Data (%)', newStyle)
     worksheet.write(row+17,column-1, 'Total Dialed Out', style)
     worksheet.write(row+18,column-1, 'Login Time (In Hrs)', style)
     worksheet.write(row+19,column-1, 'Call Duration (In Hrs)', style)
+    worksheet.write(row+20,column-1, 'Handling Time (In Hrs)', style)
+    worksheet.write(row+21,column-1, 'Idle Time (In Hrs)', style)
+    worksheet.write(row+22,column-1, 'Links Converted', style)
+    worksheet.write(row+23,column-1, 'Link sent yet to be converted', style)
     columnId=agentId
     while True:
             if agentId >5 :
                 break
             else:
                             contactableData+=r[1]
                         currentList.append(str(dispositionMap.get(r[0])))
                         column = agentId
                         remainingList = list(set(freshList) - set(currentList))
-                        worksheet.write(row, columnId, r[1])
+                        worksheet.write(row, columnId, r[1],center_alignment)
                         for i,val in enumerate(remainingList):
                             row = int(val)+26
                             column = agentId
-                            worksheet.write(row, columnId, 0)
+                            worksheet.write(row, columnId, 0,center_alignment)
                     totalDialedOut = contactableData+nonContactableData
-                    worksheet.write(25+15,columnId,(contactableData/float(totalDialedOut))*100)
+                    worksheet.write(25+15,columnId,round((contactableData/float(totalDialedOut))*100,2),center_alignment)
-                    worksheet.write(25+16,columnId,(nonContactableData/float(totalDialedOut))*100)
+                    worksheet.write(25+16,columnId,round((nonContactableData/float(totalDialedOut))*100,2),center_alignment)
-                    worksheet.write(25+17,columnId,totalDialedOut)
+                    worksheet.write(25+17,columnId,totalDialedOut,center_alignment)
                     conn.close()
                     name_query=AGENT_NAME_QUERY %(agentId)
                     conn = getDbConnection()
                     column=agentId
                     for r in result:
                         loginTime+=r[0].seconds
                     hours=loginTime/3600
                     minutesLeft=(loginTime%3600)/60
-                    worksheet.write(25+18,columnId,str(hours)+':'+str(minutesLeft))
+                    worksheet.write(25+18,columnId,str(hours)+':'+str(minutesLeft),center_alignment)
                     conn.close()
                     name_query=AGENT_FRESH_QUERY_DURATION %(agentId)
                     conn = getDbConnection()
                     column=agentId
                     loginTime=0
                     for r in result:
                         loginTime+=r[0]
                     hours=loginTime/3600
                     minutesLeft=(loginTime%3600)/60
-                    worksheet.write(25+19,columnId,str(int(hours))+':'+str(int(minutesLeft)))
+                    worksheet.write(25+19,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
+                    conn.close()
+                    name_query=AGENT_FRESH_QUERY_AHT %(agentId)
+                    conn = getDbConnection()
+                    column=agentId
+                    cursor = conn.cursor()
+                    cursor.execute(name_query)
+                    result = cursor.fetchall()
+                    loginTime=0
+                    for r in result:
+                        loginTime+=r[0]
+                    hours=loginTime/3600
+                    minutesLeft=(loginTime%3600)/60
+                    worksheet.write(25+20,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
+                    conn.close()
+                    name_query=AGENT_FRESH_QUERY_AIT %(agentId)
+                    conn = getDbConnection()
+                    column=agentId
+                    cursor = conn.cursor()
+                    cursor.execute(name_query)
+                    result = cursor.fetchall()
+                    loginTime=0
+                    for r in result:
+                        loginTime+=r[0]
+                    hours=loginTime/3600
+                    minutesLeft=(loginTime%3600)/60
+                    worksheet.write(25+21,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
+                    conn.close()
+                    datesql=AGENT_FRESH_QUERY_LINKS_CONVERTED%(agentId)
+                    conn = getDbConnection()
+                    cursor = conn.cursor()
+                    cursor.execute(datesql)
+                    result = cursor.fetchall()
+                    loginTime=0
+                    for r in result:
+                        worksheet.write(25+22,columnId,r[0],center_alignment)
                     conn.close()
+                    datesql=AGENT_FRESH_QUERY_LINKS_NOT_CONVERTED%(agentId)
+                    conn = getDbConnection()
+                    cursor = conn.cursor()
+                    cursor.execute(datesql)
+                    result = cursor.fetchall()
+                    loginTime=0
+                    for r in result:
+                        worksheet.write(25+23,columnId,r[0],center_alignment)
                 agentId+=1
                 columnId+=1
     workbook.save(TMP_FILE)
     #sendmail(["manas.kapoor@shop2020.in"], "", TMP_FILE, SUBJECT)
     worksheet.write(3,column, 'Not Reachable', newStyle)
     worksheet.write(4,column, 'Switched Off', newStyle)
     worksheet.write(5,column, 'Retailer Not Interested', newStyle)
     worksheet.write(6,column, 'Already Profitmandi user', boldStyle)
     worksheet.write(7,column, 'Verified Link Sent', boldStyle)
-    worksheet.write(8,column, 'Not a retailer', boldStyle)
-    worksheet.write(9,column, 'Contactable Data', newStyle)
+    worksheet.write(8,column, 'Contactable Data (%)', newStyle)
-    worksheet.write(10,column, 'Non Contactable Data', newStyle)
+    worksheet.write(9,column, 'Non Contactable Data (%)', newStyle)
-    worksheet.write(11,column, 'Agents Logged In', style)
+    worksheet.write(10,column, 'Agents Logged In', style)
-    worksheet.write(12,column, 'Average Login Time (In Hrs)', style)
+    worksheet.write(11,column, 'Average Login Time (In Hrs)', style)
-    worksheet.write(13,column, 'Total Dialed Out', style)
+    worksheet.write(12,column, 'Total Dialed Out', style)
-    worksheet.write(14,column, 'Average Dialed Out per agent', style)
+    worksheet.write(13,column, 'Average Dialed Out per agent', style)
-    worksheet.write(15,column, 'Average Call Duration (In Hrs)', style)
+    worksheet.write(14,column, 'Average Call Duration (In Hrs)', style)
+    worksheet.write(15,column, 'Average Handling Time (In Hrs)', style)
+    worksheet.write(16,column, 'Average Idle Time (In Hrs)', style)
     contactableData=0
     nonContactableData=0
     totalDispositions=0
-    print result
     for r in result:
         row = followUpDispositionMap.get(r[0])+1
         if followUpDispositionMap.get(r[0]) == 1 or followUpDispositionMap.get(r[0]) == 2 or followUpDispositionMap.get(r[0]) == 3:
             nonContactableData+=int(r[1])
         else:
             contactableData+=r[1]
         currentList.append(str(row))
         column = 1
-        worksheet.write(row, column, r[1])
+        worksheet.write(row, column, r[1],center_alignment)
     remainingList = list(set(followUpList) - set(currentList))
-    print remainingList
     for i,val in enumerate(remainingList):
         row = int(val)
-        print row
         column = 1
-        worksheet.write(row, column, 0)
+        worksheet.write(row, column, 0,center_alignment)
     totalDispositions=contactableData+nonContactableData
-    worksheet.write(9,1,(contactableData/float(totalDispositions))*100)
+    worksheet.write(8,1,round((contactableData/float(totalDispositions))*100,2),center_alignment)
-    worksheet.write(10,1,(nonContactableData/float(totalDispositions))*100)
+    worksheet.write(9,1,round((nonContactableData/float(totalDispositions))*100,2),center_alignment)
     conn.close()
     datesql=FOLLOW_UP_QUERY_LOGIN_TIME
     conn = getDbConnection()
         loginTime+=r[1].seconds
         agentLoginList.append(str(r[0]))
     averageLoginTime=loginTime/len(list(set(agentLoginList)))
     hours=averageLoginTime/3600
     minutesLeft=(averageLoginTime%3600)/60
-    worksheet.write(11,1,len(list(set(agentLoginList))))
+    worksheet.write(10,1,len(list(set(agentLoginList))),center_alignment)
-    worksheet.write(12,1,str(hours) + ':'+ str(minutesLeft))
+    worksheet.write(11,1,str(hours) + ':'+ str(minutesLeft),center_alignment)
-    worksheet.write(13,1,totalDispositions)
+    worksheet.write(12,1,totalDispositions,center_alignment)
-    worksheet.write(14,1,(contactableData+nonContactableData)/len(list(set(agentLoginList))))
+    worksheet.write(13,1,(contactableData+nonContactableData)/len(list(set(agentLoginList))),center_alignment)
     conn.close()
     datesql=FOLLOW_UP_QUERY_DURATION
     conn = getDbConnection()
     cursor = conn.cursor()
     for r in result:
         totalCallDuration= r[0]
     averageCallDuration=totalCallDuration/len(list(set(agentLoginList)))
     hours=averageCallDuration/3600
     minutesLeft=(averageCallDuration%3600)/60
-    worksheet.write(15,1,str(int(hours))+':'+str(int(minutesLeft)))
+    worksheet.write(14,1,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
+    conn.close()
+    datesql=FOLLOW_UP_QUERY_AHT
+    conn = getDbConnection()
+    cursor = conn.cursor()
-    workbook.save(TMP_FILE)
+    cursor.execute(datesql)
+    result = cursor.fetchall()
+    loginTime=0
+    for r in result:
+        loginTime+=r[0]
+    averageLoginTime=loginTime/len(list(set(agentLoginList)))
+    hours=averageLoginTime/3600
+    minutesLeft=(averageLoginTime%3600)/60
-    #sendmail(["manas.kapoor@shop2020.in"], "", TMP_FILE, SUBJECT)
+    worksheet.write(15,1,len(list(set(agentLoginList))),center_alignment)
+    conn.close()
+    datesql=FOLLOW_UP_QUERY_AIT
+    conn = getDbConnection()
+    cursor = conn.cursor()
+    cursor.execute(datesql)
+    result = cursor.fetchall()
+    loginTime=0
+    for r in result:
+        loginTime+=r[0]
+    averageLoginTime=loginTime/len(list(set(agentLoginList)))
+    hours=averageLoginTime/3600
+    minutesLeft=(averageLoginTime%3600)/60
+    worksheet.write(16,1,len(list(set(agentLoginList))),center_alignment)
+    workbook.save(TMP_FILE)
 def generateAgentWiseFollowupCallingReport():
     agentId=3
     rb = open_workbook(TMP_FILE,formatting_info=True)
     workbook = copy(rb)
     numberOfSheets=rb.nsheets
-    print 'Number of sheets ' + str(numberOfSheets)
     sheet=rb.sheet_by_index(numberOfSheets-1)
     worksheet = workbook.get_sheet(numberOfSheets-1)
     boldStyle = xlwt.XFStyle()
     newStyle= xlwt.XFStyle()
     style = xlwt.XFStyle()
     worksheet.write(row+3,column-1, 'Not Reachable', newStyle)
     worksheet.write(row+4,column-1, 'Switched Off', newStyle)
     worksheet.write(row+5,column-1, 'Retailer Not Interested', newStyle)
     worksheet.write(row+6,column-1, 'Already Profitmandi user', boldStyle)
     worksheet.write(row+7,column-1, 'Verified Link Sent', boldStyle)
-    worksheet.write(row+8,column-1, 'Not a Retailer', newStyle)
+    worksheet.write(row+8,column-1, 'Contactable Data (%)', newStyle)
-    worksheet.write(row+9,column-1, 'Contactable Data', newStyle)
+    worksheet.write(row+9,column-1, 'Non Contactable Data (%)', newStyle)
-    worksheet.write(row+10,column-1, 'Non Contactable Data', newStyle)
+    worksheet.write(row+10,column-1, 'Total Dialed Out', style)
-    worksheet.write(row+11,column-1, 'Total Dialed Out', style)
+    worksheet.write(row+11,column-1, 'Login Time (In Hrs)', style)
+    worksheet.write(row+12,column-1, 'Call Duration (In Hrs)', style)
-    worksheet.write(row+12,column-1, 'Login Time (In Hrs)', style)
+    worksheet.write(row+13,column-1, 'Handling Time (In Hrs)', style)
-    worksheet.write(row+13,column-1, 'Call Duration (In Hrs)', style)
+    worksheet.write(row+14,column-1, 'Idle Time (In Hrs)', style)
     columnId=agentId
     while True:
             if agentId >5 :
                 break
             else:
                 nonContactableData=0
                 if cursor.rowcount ==0:
                     agentId+=1
                     continue
                 else:
-                    print result
                     for r in result:
                         row = followUpDispositionMap.get(r[0])+26
-                        if followUpDispositionMap.get(r[0]) == 1 or followUpDispositionMap.get(r[0]) == 2 or followUpDispositionMap.get(r[0]) == 3 or followUpDispositionMap.get(r[0]) == 4:
+                        if followUpDispositionMap.get(r[0]) == 1 or followUpDispositionMap.get(r[0]) == 2 or followUpDispositionMap.get(r[0]) == 3 :
                             nonContactableData+=int(r[1])
                         else:
                             contactableData+=r[1]
                         currentList.append(str(followUpDispositionMap.get(r[0])+1))
                         column = agentId
-                        worksheet.write(row, columnId, r[1])
+                        worksheet.write(row, columnId, r[1],center_alignment)
                     remainingList = list(set(followUpList) - set(currentList))
                     for i,val in enumerate(remainingList):
                         row = int(val)+25
                         column = agentId
-                        worksheet.write(row, columnId, 0)
+                        worksheet.write(row, columnId, 0,center_alignment)
                     totalDialedOut = contactableData+nonContactableData
-                    worksheet.write(25+9,columnId,(contactableData/float(totalDialedOut))*100)
+                    worksheet.write(25+8,columnId,round((contactableData/float(totalDialedOut))*100,2),center_alignment)
-                    worksheet.write(25+10,columnId,(nonContactableData/float(totalDialedOut))*100)
+                    worksheet.write(25+9,columnId,round((nonContactableData/float(totalDialedOut))*100,2),center_alignment)
-                    worksheet.write(25+11,columnId,totalDialedOut)
+                    worksheet.write(25+10,columnId,totalDialedOut,center_alignment)
                     conn.close()
                     name_query=AGENT_NAME_QUERY %(agentId)
                     conn = getDbConnection()
                     column=agentId
                     for r in result:
                         loginTime+=r[0].seconds
                     hours=loginTime/3600
                     minutesLeft=(loginTime%3600)/60
-                    worksheet.write(25+12,columnId,str(hours)+':'+str(minutesLeft))
+                    worksheet.write(25+11,columnId,str(hours)+':'+str(minutesLeft),center_alignment)
                     conn.close()
                     name_query=AGENT_FOLLOW_UP_QUERY_DURATION %(agentId)
                     conn = getDbConnection()
                     column=agentId
                     loginTime=0
                     for r in result:
                         loginTime+=r[0]
                     hours=loginTime/3600
                     minutesLeft=(loginTime%3600)/60
+                    worksheet.write(25+12,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
+                    conn.close()
+                    name_query=AGENT_FOLLOW_UP_QUERY_AHT %(agentId)
+                    conn = getDbConnection()
+                    column=agentId
+                    cursor = conn.cursor()
+                    cursor.execute(name_query)
+                    result = cursor.fetchall()
+                    loginTime=0
+                    for r in result:
+                        loginTime+=r[0]
+                    hours=loginTime/3600
+                    minutesLeft=(loginTime%3600)/60
-                    worksheet.write(25+13,columnId,str(int(hours))+':'+str(int(minutesLeft)))
+                    worksheet.write(25+13,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
+                    conn.close()
+                    name_query=AGENT_FOLLOW_UP_QUERY_AIT %(agentId)
+                    conn = getDbConnection()
+                    column=agentId
+                    cursor = conn.cursor()
+                    cursor.execute(name_query)
+                    result = cursor.fetchall()
+                    loginTime=0
+                    for r in result:
+                        loginTime+=r[0]
+                    hours=loginTime/3600
+                    minutesLeft=(loginTime%3600)/60
+                    worksheet.write(25+14,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
                     conn.close()
                 agentId+=1
                 columnId+=1
     workbook.save(TMP_FILE)
-    #sendmail(["manas.kapoor@shop2020.in"], "", TMP_FILE, SUBJECT)
 def generateOnBoardingCallingReport():
     datesql=ONBOARDING_QUERY
     conn = getDbConnection()
     worksheet.write(8,column, 'Agents Logged In', style)
     worksheet.write(9,column, 'Average Login Time (In Hrs)', style)
     worksheet.write(10,column, 'Total Dialed Out', style)
     worksheet.write(11,column, 'Average Dialed Out per agent', style)
     worksheet.write(12,column, 'Average Call Duration (In Hrs)', style)
+    worksheet.write(13,column, 'Average Handling Time (In Hrs)', style)
+    worksheet.write(14,column, 'Average Idle Time (In Hrs)', style)
     contactableData=0
     nonContactableData=0
     totalDispositions=0
-    print result
     for r in result:
         row = onBoardingDispositionMap.get(r[0])+1
         if onBoardingDispositionMap.get(r[0]) == 1 or onBoardingDispositionMap.get(r[0]) == 2 or onBoardingDispositionMap.get(r[0]) == 3:
             nonContactableData+=int(r[1])
         else:
             contactableData+=r[1]
         currentList.append(str(row))
         column = 1
-        worksheet.write(row, column, r[1])
+        worksheet.write(row, column, r[1],center_alignment)
     remainingList = list(set(onBoardingList) - set(currentList))
-    print remainingList
     for i,val in enumerate(remainingList):
         row = int(val)
-        print row
         column = 1
-        worksheet.write(row, column, 0)
+        worksheet.write(row, column, 0,center_alignment)
     totalDispositions=contactableData+nonContactableData
-    worksheet.write(6,1,(contactableData/float(totalDispositions))*100)
+    worksheet.write(6,1,round((contactableData/float(totalDispositions))*100,2),center_alignment)
-    worksheet.write(7,1,(nonContactableData/float(totalDispositions))*100)
+    worksheet.write(7,1,round((nonContactableData/float(totalDispositions))*100,2),center_alignment)
     conn.close()
     datesql=ONBOARDING_QUERY_LOGIN_TIME
     conn = getDbConnection()
         loginTime+=r[1].seconds
         agentLoginList.append(str(r[0]))
     averageLoginTime=loginTime/len(list(set(agentLoginList)))
     hours=averageLoginTime/3600
     minutesLeft=(averageLoginTime%3600)/60
-    worksheet.write(8,1,len(list(set(agentLoginList))))
+    worksheet.write(8,1,len(list(set(agentLoginList))),center_alignment)
-    worksheet.write(9,1,str(hours) + ':'+ str(minutesLeft))
+    worksheet.write(9,1,str(hours) + ':'+ str(minutesLeft),center_alignment)
-    worksheet.write(10,1,totalDispositions)
+    worksheet.write(10,1,totalDispositions,center_alignment)
-    worksheet.write(11,1,(contactableData+nonContactableData)/len(list(set(agentLoginList))))
+    worksheet.write(11,1,(contactableData+nonContactableData)/len(list(set(agentLoginList))),center_alignment)
     conn.close()
     datesql=ONBOARDING_QUERY_DURATION
     conn = getDbConnection()
     cursor = conn.cursor()
     for r in result:
         totalCallDuration= r[0]
     averageCallDuration=totalCallDuration/len(list(set(agentLoginList)))
     hours=averageCallDuration/3600
     minutesLeft=(averageCallDuration%3600)/60
-    worksheet.write(12,1,str(int(hours))+':'+str(int(minutesLeft)))
+    worksheet.write(12,1,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
+    conn.close()
+    datesql=ONBOARDING_QUERY_AHT
+    conn = getDbConnection()
+    cursor = conn.cursor()
+    cursor.execute(datesql)
+    result = cursor.fetchall()
+    for r in result:
+        totalCallDuration= r[0]
+    averageCallDuration=totalCallDuration/len(list(set(agentLoginList)))
+    hours=averageCallDuration/3600
+    minutesLeft=(averageCallDuration%3600)/60
+    worksheet.write(13,1,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
+    conn.close()
+    datesql=ONBOARDING_QUERY_AIT
+    conn = getDbConnection()
+    cursor = conn.cursor()
+    cursor.execute(datesql)
+    result = cursor.fetchall()
+    for r in result:
+        totalCallDuration= r[0]
+    averageCallDuration=totalCallDuration/len(list(set(agentLoginList)))
+    hours=averageCallDuration/3600
+    minutesLeft=(averageCallDuration%3600)/60
+    worksheet.write(14,1,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
     workbook.save(TMP_FILE)
     #sendmail(["manas.kapoor@shop2020.in"], "", TMP_FILE, SUBJECT)
 def generateAgentWiseOnboardingCallingReport():
     agentId=3
     rb = open_workbook(TMP_FILE,formatting_info=True)
     workbook = copy(rb)
     numberOfSheets=rb.nsheets
-    print 'Number of sheets ' + str(numberOfSheets)
     sheet=rb.sheet_by_index(numberOfSheets-1)
     worksheet = workbook.get_sheet(numberOfSheets-1)
     boldStyle = xlwt.XFStyle()
     newStyle= xlwt.XFStyle()
     style = xlwt.XFStyle()
     worksheet.write(row+6,column-1, 'Contactable Data', newStyle)
     worksheet.write(row+7,column-1, 'Non Contactable Data', newStyle)
     worksheet.write(row+8,column-1, 'Total Dialed Out', style)
     worksheet.write(row+9,column-1, 'Login Time (In Hrs)', style)
     worksheet.write(row+10,column-1, 'Call Duration (In Hrs)', style)
+    worksheet.write(row+11,column-1, 'Handling Time (In Hrs)', style)
+    worksheet.write(row+12,column-1, 'Idle Time (In Hrs)', style)
     columnId=agentId
     while True:
-            if agentId >5 :
+            if agentId >6 :
                 break
             else:
                 datesql=AGENT_ONBOARDING_QUERY %(agentId)
                 conn = getDbConnection()
                 nonContactableData=0
                 if cursor.rowcount ==0:
                     agentId+=1
                     continue
                 else:
-                    print result
                     for r in result:
                         row = onBoardingDispositionMap.get(r[0])+26
                         if onBoardingDispositionMap.get(r[0]) == 1 or onBoardingDispositionMap.get(r[0]) == 2 or onBoardingDispositionMap.get(r[0]) == 3 or followUpDispositionMap.get(r[0]) == 4:
                             nonContactableData+=int(r[1])
                         else:
                             contactableData+=r[1]
                         currentList.append(str(onBoardingDispositionMap.get(r[0])+1))
                         column = agentId
-                        worksheet.write(row, columnId, r[1])
+                        worksheet.write(row, columnId, r[1],center_alignment)
                     remainingList = list(set(onBoardingList) - set(currentList))
                     for i,val in enumerate(remainingList):
                         row = int(val)+25
                         column = agentId
-                        worksheet.write(row, columnId, 0)
+                        worksheet.write(row, columnId, 0,center_alignment)
                     totalDialedOut = contactableData+nonContactableData
-                    worksheet.write(25+6,columnId,(contactableData/float(totalDialedOut))*100)
+                    worksheet.write(25+6,columnId,round((contactableData/float(totalDialedOut))*100,2),center_alignment)
-                    worksheet.write(25+7,columnId,(nonContactableData/float(totalDialedOut))*100)
+                    worksheet.write(25+7,columnId,round((nonContactableData/float(totalDialedOut))*100,2),center_alignment)
-                    worksheet.write(25+8,columnId,totalDialedOut)
+                    worksheet.write(25+8,columnId,totalDialedOut,center_alignment)
                     conn.close()
                     name_query=AGENT_NAME_QUERY %(agentId)
                     conn = getDbConnection()
                     column=agentId
                     cursor = conn.cursor()
                     cursor.execute(name_query)
                     result = cursor.fetchall()
                     for r in result:
                         worksheet.write(25,columnId,r,style)
                     conn.close()
-                    name_query=ONBOARDING_QUERY_LOGIN_TIME %(agentId)
+                    name_query=AGENT_ONBOARDING_QUERY_LOGIN_TIME %(agentId)
                     conn = getDbConnection()
                     column=agentId
                     cursor = conn.cursor()
                     cursor.execute(name_query)
                     result = cursor.fetchall()
                     for r in result:
                         loginTime+=r[0].seconds
                     hours=loginTime/3600
                     minutesLeft=(loginTime%3600)/60
-                    worksheet.write(25+9,columnId,str(hours)+':'+str(minutesLeft))
+                    worksheet.write(25+9,columnId,str(hours)+':'+str(minutesLeft),center_alignment)
+                    conn.close()
+                    name_query=AGENT_ONBOARDING_QUERY_DURATION %(agentId)
+                    conn = getDbConnection()
+                    column=agentId
+                    cursor = conn.cursor()
+                    cursor.execute(name_query)
+                    result = cursor.fetchall()
+                    loginTime=0
+                    for r in result:
+                        loginTime+=r[0]
+                    hours=loginTime/3600
+                    minutesLeft=(loginTime%3600)/60
+                    worksheet.write(25+10,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
                     conn.close()
-                    name_query=ONBOARDING_QUERY_DURATION %(agentId)
+                    name_query=AGENT_ONBOARDING_QUERY_AHT %(agentId)
                     conn = getDbConnection()
                     column=agentId
                     cursor = conn.cursor()
                     cursor.execute(name_query)
                     result = cursor.fetchall()
                     loginTime=0
                     for r in result:
                         loginTime+=r[0]
                     hours=loginTime/3600
                     minutesLeft=(loginTime%3600)/60
-                    worksheet.write(25+10,columnId,str(int(hours))+':'+str(int(minutesLeft)))
+                    worksheet.write(25+11,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
+                    conn.close()
+                    name_query=AGENT_ONBOARDING_QUERY_AIT %(agentId)
+                    conn = getDbConnection()
+                    column=agentId
+                    cursor = conn.cursor()
+                    cursor.execute(name_query)
+                    result = cursor.fetchall()
+                    loginTime=0
+                    for r in result:
+                        loginTime+=r[0]
+                    hours=loginTime/3600
+                    minutesLeft=(loginTime%3600)/60
+                    worksheet.write(25+12,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
                     conn.close()
                 agentId+=1
                 columnId+=1
     workbook.save(TMP_FILE)
     generateAgentWiseFreshCallingReport()
     generateFollowUpCallingReport()
     generateAgentWiseFollowupCallingReport()
     generateOnBoardingCallingReport()
     generateAgentWiseOnboardingCallingReport()
+    sendmail(["manas.kapoor@shop2020.in,amit.sirohi@shop2020.in"], "", TMP_FILE, SUBJECT)
 def sendmail(email, message, fileName, title):
     if email == "":
         return
     mailServer = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)
     mailServer.ehlo()
     fileMsg = MIMEBase('application', 'vnd.ms-excel')
     fileMsg.set_payload(file(TMP_FILE).read())
     encoders.encode_base64(fileMsg)
     fileMsg.add_header('Content-Disposition', 'attachment;filename=' + fileName)
     msg.attach(fileMsg)
-    print 'Sending report'
-    #MAILTO = ['manas.kapoor@saholic.com','rajneesh.arora@saholic.com']
+    MAILTO = ['manas.kapoor@saholic.com','amit.sirohi@shop2020.in']
-    MAILTO = ['manas.kapoor@saholic.com']
+    #MAILTO = ['manas.kapoor@saholic.com']
     mailServer.login(SENDER, PASSWORD)
     mailServer.sendmail(PASSWORD, MAILTO, msg.as_string())
 if __name__ == '__main__':
     main()

Subversion Repositories SmartDukaan

(root)//trunk/PyDTR/src/dtr/reports/crmoutbound.py – Rev 15399 → 15402