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



    f = xlwt.Font()
    f.bold = True
    boldStyle.font = f
    
    column = agentId
    row = 0
    worksheet.write(row,column-1,'Call Disposition Type',style)
    worksheet.write(row+1, column-1, 'Call Later', newStyle)
    worksheet.write(row+2,column-1, 'Ringing No Answer', newStyle)
    worksheet.write(row+3,column-1, 'Not Reachable', newStyle)
    worksheet.write(row+4,column-1, 'Switched Off', newStyle)

                    continue    
                else:
                    for r in result:
                        if dispositionMap.get(r[0]) == 9:
                            totalVerifiedLinkSent=int(r[1])
                        row = dispositionMap.get(r[0])+1
                        if dispositionMap.get(r[0]) == 1 or dispositionMap.get(r[0]) == 2 or dispositionMap.get(r[0]) == 3 or dispositionMap.get(r[0]) == 4:
                            nonContactableData+=int(r[1])
                        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],center_alignment)
                        for i,val in enumerate(remainingList):
                            row = int(val)+1
                            column = agentId
                            worksheet.write(row, columnId, 0,center_alignment)
                    totalDialedOut = contactableData+nonContactableData
                    if totalDialedOut > 0:
                        worksheet.write(15,columnId,round((contactableData/float(totalDialedOut))*100,2),center_alignment)
                        worksheet.write(16,columnId,round((nonContactableData/float(totalDialedOut))*100,2),center_alignment)
                        worksheet.write(17,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(0,columnId,r,style)                    
 
                    conn.close()
 
                                
                    name_query=AGENT_FRESH_QUERY_LOGIN_TIME %(agentId)
                    conn = getDbConnection()
                    column=agentId
                    cursor = conn.cursor()
                    cursor.execute(name_query)

                    for r in result:
                        loginTime+=r[0].seconds
                        
                    hours=loginTime/3600
                    minutesLeft=(loginTime%3600)/60
                    worksheet.write(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(19,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)    
                    conn.close()
                    
                    name_query=AGENT_FRESH_QUERY_AHT %(agentId)
                    conn = getDbConnection()
                    column=agentId

                    loginTime=0
                    for r in result:
                        loginTime+=r[0]
                    hours=loginTime/3600
                    minutesLeft=(loginTime%3600)/60
                    worksheet.write(20,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)    
                    conn.close()
                    
                    name_query=AGENT_FRESH_QUERY_AIT %(agentId)
                    conn = getDbConnection()
                    column=agentId

                    for r in result:
                        if r[0] is not None:
                            loginTime+=r[0]
                    hours=loginTime/3600
                    minutesLeft=(loginTime%3600)/60
                    worksheet.write(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:
 
                        converted=r[0]
                        worksheet.write(22,columnId,r[0],center_alignment)
                    
                    if totalVerifiedLinkSent==0:
                        worksheet.write(23,columnId,0,center_alignment)
                    elif totalVerifiedLinkSent<converted:
                        worksheet.write(23,columnId,converted-totalVerifiedLinkSent,center_alignment)    
                    else:
                        worksheet.write(23,columnId,totalVerifiedLinkSent-converted,center_alignment)    
                    conn.close()
 
 
 
 
 
 
 
 
 
 
                agentId+=1
                columnId+=1
    workbook.save(TMP_FILE)    
 
 
def generateFollowUpCallingReport():
    datesql=FOLLOW_UP_QUERY 
    conn = getDbConnection()
    

    f = xlwt.Font()
    f.bold = True
    boldStyle.font = f
    
    column = agentId
    row = 0
    worksheet.write(row,column-1,'Call Disposition Type',style)
    worksheet.write(row+1, column-1, 'Call Later', newStyle)
    worksheet.write(row+2,column-1, 'Ringing No Answer', newStyle)
    worksheet.write(row+3,column-1, 'Not Reachable', newStyle)
    worksheet.write(row+4,column-1, 'Switched Off', newStyle)

                    agentId+=1
                    continue    
                else:
                    
                    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(followUpDispositionMap.get(r[0])+1))
                        column = agentId
                        worksheet.write(row, columnId, r[1],center_alignment)
                    remainingList = list(set(followUpList) - set(currentList))
                    
                    for i,val in enumerate(remainingList):
                        row = int(val)+0
                        column = agentId
                        worksheet.write(row, columnId, 0,center_alignment)
                    totalDialedOut = contactableData+nonContactableData
                    if totalDialedOut>0:
                        worksheet.write(12,columnId,round((contactableData/float(totalDialedOut))*100,2),center_alignment)
                        worksheet.write(13,columnId,round((nonContactableData/float(totalDialedOut))*100,2),center_alignment)
                        worksheet.write(14,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(0,columnId,r,style)
                    
                    conn.close()
                
                
                    name_query=AGENT_FOLLOW_UP_QUERY_LOGIN_TIME %(agentId)

                    for r in result:
                        loginTime+=r[0].seconds
                        
                    hours=loginTime/3600
                    minutesLeft=(loginTime%3600)/60
                    worksheet.write(15,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(16,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)    
                    conn.close()
                    
                    name_query=AGENT_FOLLOW_UP_QUERY_AHT %(agentId)
                    conn = getDbConnection()
                    column=agentId

                    loginTime=0
                    for r in result:
                        loginTime+=r[0]
                    hours=loginTime/3600
                    minutesLeft=(loginTime%3600)/60
                    worksheet.write(17,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)    
                    conn.close()
                    
                    name_query=AGENT_FOLLOW_UP_QUERY_AIT %(agentId)
                    conn = getDbConnection()
                    column=agentId

                    loginTime=0
                    for r in result:
                        loginTime+=r[0]
                    hours=loginTime/3600
                    minutesLeft=(loginTime%3600)/60
                    worksheet.write(18,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)    
                    conn.close()
                    
                agentId+=1
                columnId+=1
    workbook.save(TMP_FILE)    

    f = xlwt.Font()
    f.bold = True
    boldStyle.font = f
    
    column = agentId
    row = 0
    
    worksheet.write(row,column-1,'Call Disposition Type',style)
    worksheet.write(row+1, column-1, 'Call Later', newStyle)
    worksheet.write(row+2,column-1, 'Ringing No Answer', newStyle)
    worksheet.write(row+3,column-1, 'Not Reachable', newStyle)

                if cursor.rowcount ==0:
                    agentId+=1
                    continue    
                else:
                    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 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],center_alignment)
                    remainingList = list(set(onBoardingList) - set(currentList))
                    
                    for i,val in enumerate(remainingList):
                        row = int(val)+0
                        column = agentId
                        worksheet.write(row, columnId, 0,center_alignment)
                    totalDialedOut = contactableData+nonContactableData
                    worksheet.write(6,columnId,round((contactableData/float(totalDialedOut))*100,2),center_alignment)
                    worksheet.write(7,columnId,round((nonContactableData/float(totalDialedOut))*100,2),center_alignment)
                    worksheet.write(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(0,columnId,r,style)
                    conn.close()
                
                
                    name_query=AGENT_ONBOARDING_QUERY_LOGIN_TIME %(agentId)
                    conn = getDbConnection()

                    for r in result:
                        loginTime+=r[0].seconds
                        
                    hours=loginTime/3600
                    minutesLeft=(loginTime%3600)/60
                    worksheet.write(9,columnId,str(hours)+':'+str(minutesLeft),center_alignment)    
                    conn.close()
                    
                    name_query=AGENT_ONBOARDING_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(10,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)    
                    conn.close()
                    
                    name_query=AGENT_ONBOARDING_QUERY_AHT %(agentId)
                    conn = getDbConnection()
                    column=agentId

                    loginTime=0
                    for r in result:
                        loginTime+=r[0]
                    hours=loginTime/3600
                    minutesLeft=(loginTime%3600)/60
                    worksheet.write(11,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)    
                    conn.close()
                    
                    name_query=AGENT_ONBOARDING_QUERY_AIT %(agentId)
                    conn = getDbConnection()
                    column=agentId

                    loginTime=0
                    for r in result:
                        loginTime+=r[0]
                    hours=loginTime/3600
                    minutesLeft=(loginTime%3600)/60
                    worksheet.write(12,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)    
                    conn.close()
                    
                agentId+=1
                columnId+=1
    workbook.save(TMP_FILE)          
 
 
        
def main():
    parser = optparse.OptionParser()
    parser.add_option("-T", "--reporttype", dest="reporttype",
                      default="crmoutbound",

Rev 18673	Rev 19160
Line 1000...	Line 1000...
1000	`f = xlwt.Font()`	1000	`f = xlwt.Font()`
1001	`f.bold = True`	1001	`f.bold = True`
1002	`boldStyle.font = f`	1002	`boldStyle.font = f`
1003		1003
1004	`column = agentId`	1004	`column = agentId`
1005	`row = 25`	1005	`row = 0`
1006	`worksheet.write(row,column-1,'Call Disposition Type',style)`	1006	`worksheet.write(row,column-1,'Call Disposition Type',style)`
1007	`worksheet.write(row+1, column-1, 'Call Later', newStyle)`	1007	`worksheet.write(row+1, column-1, 'Call Later', newStyle)`
1008	`worksheet.write(row+2,column-1, 'Ringing No Answer', newStyle)`	1008	`worksheet.write(row+2,column-1, 'Ringing No Answer', newStyle)`
1009	`worksheet.write(row+3,column-1, 'Not Reachable', newStyle)`	1009	`worksheet.write(row+3,column-1, 'Not Reachable', newStyle)`
1010	`worksheet.write(row+4,column-1, 'Switched Off', newStyle)`	1010	`worksheet.write(row+4,column-1, 'Switched Off', newStyle)`
Line 1048...	Line 1048...
1048	`continue`	1048	`continue`
1049	`else:`	1049	`else:`
1050	`for r in result:`	1050	`for r in result:`
1051	`if dispositionMap.get(r[0]) == 9:`	1051	`if dispositionMap.get(r[0]) == 9:`
1052	`totalVerifiedLinkSent=int(r[1])`	1052	`totalVerifiedLinkSent=int(r[1])`
1053	`row = dispositionMap.get(r[0])+26`	1053	`row = dispositionMap.get(r[0])+1`
1054	`if dispositionMap.get(r[0]) == 1 or dispositionMap.get(r[0]) == 2 or dispositionMap.get(r[0]) == 3 or dispositionMap.get(r[0]) == 4:`	1054	`if dispositionMap.get(r[0]) == 1 or dispositionMap.get(r[0]) == 2 or dispositionMap.get(r[0]) == 3 or dispositionMap.get(r[0]) == 4:`
1055	`nonContactableData+=int(r[1])`	1055	`nonContactableData+=int(r[1])`
1056	`else:`	1056	`else:`
1057	`contactableData+=r[1]`	1057	`contactableData+=r[1]`
1058	`currentList.append(str(dispositionMap.get(r[0])))`	1058	`currentList.append(str(dispositionMap.get(r[0])))`
1059	`column = agentId`	1059	`column = agentId`
1060	`remainingList = list(set(freshList) - set(currentList))`	1060	`remainingList = list(set(freshList) - set(currentList))`
1061		1061
1062	`worksheet.write(row, columnId, r[1],center_alignment)`	1062	`worksheet.write(row, columnId, r[1],center_alignment)`
1063	`for i,val in enumerate(remainingList):`	1063	`for i,val in enumerate(remainingList):`
1064	`row = int(val)+26`	1064	`row = int(val)+1`
1065	`column = agentId`	1065	`column = agentId`
1066	`worksheet.write(row, columnId, 0,center_alignment)`	1066	`worksheet.write(row, columnId, 0,center_alignment)`
1067	`totalDialedOut = contactableData+nonContactableData`	1067	`totalDialedOut = contactableData+nonContactableData`
1068	`if totalDialedOut > 0:`	1068	`if totalDialedOut > 0:`
1069	`worksheet.write(25+15,columnId,round((contactableData/float(totalDialedOut))*100,2),center_alignment)`	1069	`worksheet.write(15,columnId,round((contactableData/float(totalDialedOut))*100,2),center_alignment)`
1070	`worksheet.write(25+16,columnId,round((nonContactableData/float(totalDialedOut))*100,2),center_alignment)`	1070	`worksheet.write(16,columnId,round((nonContactableData/float(totalDialedOut))*100,2),center_alignment)`
1071	`worksheet.write(25+17,columnId,totalDialedOut,center_alignment)`	1071	`worksheet.write(17,columnId,totalDialedOut,center_alignment)`
1072	`conn.close()`	1072	`conn.close()`
1073		1073
1074	`name_query=AGENT_NAME_QUERY %(agentId)`	1074	`name_query=AGENT_NAME_QUERY %(agentId)`
1075	`conn = getDbConnection()`	1075	`conn = getDbConnection()`
1076	`column=agentId`	1076	`column=agentId`
1077	`cursor = conn.cursor()`	1077	`cursor = conn.cursor()`
1078	`cursor.execute(name_query)`	1078	`cursor.execute(name_query)`
1079	`result = cursor.fetchall()`	1079	`result = cursor.fetchall()`
1080	`for r in result:`	1080	`for r in result:`
1081	`worksheet.write(25,columnId,r,style)`	1081	`worksheet.write(0,columnId,r,style)`
1082		-
1083	`conn.close()`	1082	`conn.close()`
1084		-
1085		1083
1086	`name_query=AGENT_FRESH_QUERY_LOGIN_TIME %(agentId)`	1084	`name_query=AGENT_FRESH_QUERY_LOGIN_TIME %(agentId)`
1087	`conn = getDbConnection()`	1085	`conn = getDbConnection()`
1088	`column=agentId`	1086	`column=agentId`
1089	`cursor = conn.cursor()`	1087	`cursor = conn.cursor()`
1090	`cursor.execute(name_query)`	1088	`cursor.execute(name_query)`
Line 1093...	Line 1091...
1093	`for r in result:`	1091	`for r in result:`
1094	`loginTime+=r[0].seconds`	1092	`loginTime+=r[0].seconds`
1095		1093
1096	`hours=loginTime/3600`	1094	`hours=loginTime/3600`
1097	`minutesLeft=(loginTime%3600)/60`	1095	`minutesLeft=(loginTime%3600)/60`
1098	`worksheet.write(25+18,columnId,str(hours)+':'+str(minutesLeft),center_alignment)`	1096	`worksheet.write(18,columnId,str(hours)+':'+str(minutesLeft),center_alignment)`
1099	`conn.close()`	1097	`conn.close()`
1100		1098
1101	`name_query=AGENT_FRESH_QUERY_DURATION %(agentId)`	1099	`name_query=AGENT_FRESH_QUERY_DURATION %(agentId)`
1102	`conn = getDbConnection()`	1100	`conn = getDbConnection()`
1103	`column=agentId`	1101	`column=agentId`
Line 1107...	Line 1105...
1107	`loginTime=0`	1105	`loginTime=0`
1108	`for r in result:`	1106	`for r in result:`
1109	`loginTime+=r[0]`	1107	`loginTime+=r[0]`
1110	`hours=loginTime/3600`	1108	`hours=loginTime/3600`
1111	`minutesLeft=(loginTime%3600)/60`	1109	`minutesLeft=(loginTime%3600)/60`
1112	`worksheet.write(25+19,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)`	1110	`worksheet.write(19,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)`
1113	`conn.close()`	1111	`conn.close()`
1114		1112
1115	`name_query=AGENT_FRESH_QUERY_AHT %(agentId)`	1113	`name_query=AGENT_FRESH_QUERY_AHT %(agentId)`
1116	`conn = getDbConnection()`	1114	`conn = getDbConnection()`
1117	`column=agentId`	1115	`column=agentId`
Line 1121...	Line 1119...
1121	`loginTime=0`	1119	`loginTime=0`
1122	`for r in result:`	1120	`for r in result:`
1123	`loginTime+=r[0]`	1121	`loginTime+=r[0]`
1124	`hours=loginTime/3600`	1122	`hours=loginTime/3600`
1125	`minutesLeft=(loginTime%3600)/60`	1123	`minutesLeft=(loginTime%3600)/60`
1126	`worksheet.write(25+20,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)`	1124	`worksheet.write(20,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)`
1127	`conn.close()`	1125	`conn.close()`
1128		1126
1129	`name_query=AGENT_FRESH_QUERY_AIT %(agentId)`	1127	`name_query=AGENT_FRESH_QUERY_AIT %(agentId)`
1130	`conn = getDbConnection()`	1128	`conn = getDbConnection()`
1131	`column=agentId`	1129	`column=agentId`
Line 1136...	Line 1134...
1136	`for r in result:`	1134	`for r in result:`
1137	`if r[0] is not None:`	1135	`if r[0] is not None:`
1138	`loginTime+=r[0]`	1136	`loginTime+=r[0]`
1139	`hours=loginTime/3600`	1137	`hours=loginTime/3600`
1140	`minutesLeft=(loginTime%3600)/60`	1138	`minutesLeft=(loginTime%3600)/60`
1141	`worksheet.write(25+21,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)`	1139	`worksheet.write(21,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)`
1142	`conn.close()`	1140	`conn.close()`
1143		1141
1144	`datesql=AGENT_FRESH_QUERY_LINKS_CONVERTED%(agentId)`	1142	`datesql=AGENT_FRESH_QUERY_LINKS_CONVERTED%(agentId)`
1145	`conn = getDbConnection()`	1143	`conn = getDbConnection()`
1146		1144
1147	`cursor = conn.cursor()`	1145	`cursor = conn.cursor()`
1148	`cursor.execute(datesql)`	1146	`cursor.execute(datesql)`
1149	`result = cursor.fetchall()`	1147	`result = cursor.fetchall()`
1150	`loginTime=0`	1148	`loginTime=0`
1151	`for r in result:`	1149	`for r in result:`
1152	`#worksheet.write(25+22,columnId,r[0],center_alignment)`	-
1153	`converted=r[0]`	1150	`converted=r[0]`
1154	`worksheet.write(25+22,columnId,r[0],center_alignment)`	1151	`worksheet.write(22,columnId,r[0],center_alignment)`
1155		1152
1156	`if totalVerifiedLinkSent==0:`	1153	`if totalVerifiedLinkSent==0:`
1157	`worksheet.write(25+23,columnId,0,center_alignment)`	1154	`worksheet.write(23,columnId,0,center_alignment)`
1158	`elif totalVerifiedLinkSent<converted:`	1155	`elif totalVerifiedLinkSent<converted:`
1159	`worksheet.write(25+23,columnId,converted-totalVerifiedLinkSent,center_alignment)`	1156	`worksheet.write(23,columnId,converted-totalVerifiedLinkSent,center_alignment)`
1160	`else:`	1157	`else:`
1161	`worksheet.write(25+23,columnId,totalVerifiedLinkSent-converted,center_alignment)`	1158	`worksheet.write(23,columnId,totalVerifiedLinkSent-converted,center_alignment)`
1162	`conn.close()`	1159	`conn.close()`
1163	`# datesql=AGENT_FRESH_QUERY_LINKS_NOT_CONVERTED%(agentId)`	-
1164	`# conn = getDbConnection()`	-
1165	`#`	-
1166	`# cursor = conn.cursor()`	-
1167	`# cursor.execute(datesql)`	-
1168	`# result = cursor.fetchall()`	-
1169	`# loginTime=0`	-
1170	`# for r in result:`	-
1171	`# worksheet.write(25+23,columnId,r[0],center_alignment)`	-
1172		-
1173	`agentId+=1`	1160	`agentId+=1`
1174	`columnId+=1`	1161	`columnId+=1`
1175	`workbook.save(TMP_FILE)`	1162	`workbook.save(TMP_FILE)`
1176	`#sendmail(["manas.kapoor@shop2020.in"], "", TMP_FILE, SUBJECT)`	-
1177		1163
1178	`def generateFollowUpCallingReport():`	1164	`def generateFollowUpCallingReport():`
1179	`datesql=FOLLOW_UP_QUERY`	1165	`datesql=FOLLOW_UP_QUERY`
1180	`conn = getDbConnection()`	1166	`conn = getDbConnection()`
1181		1167
Line 1335...	Line 1321...
1335	`f = xlwt.Font()`	1321	`f = xlwt.Font()`
1336	`f.bold = True`	1322	`f.bold = True`
1337	`boldStyle.font = f`	1323	`boldStyle.font = f`
1338		1324
1339	`column = agentId`	1325	`column = agentId`
1340	`row = 25`	1326	`row = 0`
1341	`worksheet.write(row,column-1,'Call Disposition Type',style)`	1327	`worksheet.write(row,column-1,'Call Disposition Type',style)`
1342	`worksheet.write(row+1, column-1, 'Call Later', newStyle)`	1328	`worksheet.write(row+1, column-1, 'Call Later', newStyle)`
1343	`worksheet.write(row+2,column-1, 'Ringing No Answer', newStyle)`	1329	`worksheet.write(row+2,column-1, 'Ringing No Answer', newStyle)`
1344	`worksheet.write(row+3,column-1, 'Not Reachable', newStyle)`	1330	`worksheet.write(row+3,column-1, 'Not Reachable', newStyle)`
1345	`worksheet.write(row+4,column-1, 'Switched Off', newStyle)`	1331	`worksheet.write(row+4,column-1, 'Switched Off', newStyle)`
Line 1376...	Line 1362...
1376	`agentId+=1`	1362	`agentId+=1`
1377	`continue`	1363	`continue`
1378	`else:`	1364	`else:`
1379		1365
1380	`for r in result:`	1366	`for r in result:`
1381	`row = followUpDispositionMap.get(r[0])+26`	1367	`row = followUpDispositionMap.get(r[0])+1`
1382	`if followUpDispositionMap.get(r[0]) == 1 or followUpDispositionMap.get(r[0]) == 2 or followUpDispositionMap.get(r[0]) == 3 :`	1368	`if followUpDispositionMap.get(r[0]) == 1 or followUpDispositionMap.get(r[0]) == 2 or followUpDispositionMap.get(r[0]) == 3 :`
1383	`nonContactableData+=int(r[1])`	1369	`nonContactableData+=int(r[1])`
1384	`else:`	1370	`else:`
1385	`contactableData+=r[1]`	1371	`contactableData+=r[1]`
1386	`currentList.append(str(followUpDispositionMap.get(r[0])+1))`	1372	`currentList.append(str(followUpDispositionMap.get(r[0])+1))`
1387	`column = agentId`	1373	`column = agentId`
1388	`worksheet.write(row, columnId, r[1],center_alignment)`	1374	`worksheet.write(row, columnId, r[1],center_alignment)`
1389	`remainingList = list(set(followUpList) - set(currentList))`	1375	`remainingList = list(set(followUpList) - set(currentList))`
1390		1376
1391	`for i,val in enumerate(remainingList):`	1377	`for i,val in enumerate(remainingList):`
1392	`row = int(val)+25`	1378	`row = int(val)+0`
1393	`column = agentId`	1379	`column = agentId`
1394	`worksheet.write(row, columnId, 0,center_alignment)`	1380	`worksheet.write(row, columnId, 0,center_alignment)`
1395	`totalDialedOut = contactableData+nonContactableData`	1381	`totalDialedOut = contactableData+nonContactableData`
1396	`if totalDialedOut>0:`	1382	`if totalDialedOut>0:`
1397	`worksheet.write(25+12,columnId,round((contactableData/float(totalDialedOut))*100,2),center_alignment)`	1383	`worksheet.write(12,columnId,round((contactableData/float(totalDialedOut))*100,2),center_alignment)`
1398	`worksheet.write(25+13,columnId,round((nonContactableData/float(totalDialedOut))*100,2),center_alignment)`	1384	`worksheet.write(13,columnId,round((nonContactableData/float(totalDialedOut))*100,2),center_alignment)`
1399	`worksheet.write(25+14,columnId,totalDialedOut,center_alignment)`	1385	`worksheet.write(14,columnId,totalDialedOut,center_alignment)`
1400	`conn.close()`	1386	`conn.close()`
1401		1387
1402	`name_query=AGENT_NAME_QUERY %(agentId)`	1388	`name_query=AGENT_NAME_QUERY %(agentId)`
1403	`conn = getDbConnection()`	1389	`conn = getDbConnection()`
1404	`column=agentId`	1390	`column=agentId`
1405	`cursor = conn.cursor()`	1391	`cursor = conn.cursor()`
1406	`cursor.execute(name_query)`	1392	`cursor.execute(name_query)`
1407	`result = cursor.fetchall()`	1393	`result = cursor.fetchall()`
1408	`for r in result:`	1394	`for r in result:`
1409	`worksheet.write(25,columnId,r,style)`	1395	`worksheet.write(0,columnId,r,style)`
1410		1396
1411	`conn.close()`	1397	`conn.close()`
1412		1398
1413		1399
1414	`name_query=AGENT_FOLLOW_UP_QUERY_LOGIN_TIME %(agentId)`	1400	`name_query=AGENT_FOLLOW_UP_QUERY_LOGIN_TIME %(agentId)`
Line 1421...	Line 1407...
1421	`for r in result:`	1407	`for r in result:`
1422	`loginTime+=r[0].seconds`	1408	`loginTime+=r[0].seconds`
1423		1409
1424	`hours=loginTime/3600`	1410	`hours=loginTime/3600`
1425	`minutesLeft=(loginTime%3600)/60`	1411	`minutesLeft=(loginTime%3600)/60`
1426	`worksheet.write(25+15,columnId,str(hours)+':'+str(minutesLeft),center_alignment)`	1412	`worksheet.write(15,columnId,str(hours)+':'+str(minutesLeft),center_alignment)`
1427	`conn.close()`	1413	`conn.close()`
1428		1414
1429	`name_query=AGENT_FOLLOW_UP_QUERY_DURATION %(agentId)`	1415	`name_query=AGENT_FOLLOW_UP_QUERY_DURATION %(agentId)`
1430	`conn = getDbConnection()`	1416	`conn = getDbConnection()`
1431	`column=agentId`	1417	`column=agentId`
Line 1435...	Line 1421...
1435	`loginTime=0`	1421	`loginTime=0`
1436	`for r in result:`	1422	`for r in result:`
1437	`loginTime+=r[0]`	1423	`loginTime+=r[0]`
1438	`hours=loginTime/3600`	1424	`hours=loginTime/3600`
1439	`minutesLeft=(loginTime%3600)/60`	1425	`minutesLeft=(loginTime%3600)/60`
1440	`worksheet.write(25+16,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)`	1426	`worksheet.write(16,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)`
1441	`conn.close()`	1427	`conn.close()`
1442		1428
1443	`name_query=AGENT_FOLLOW_UP_QUERY_AHT %(agentId)`	1429	`name_query=AGENT_FOLLOW_UP_QUERY_AHT %(agentId)`
1444	`conn = getDbConnection()`	1430	`conn = getDbConnection()`
1445	`column=agentId`	1431	`column=agentId`
Line 1449...	Line 1435...
1449	`loginTime=0`	1435	`loginTime=0`
1450	`for r in result:`	1436	`for r in result:`
1451	`loginTime+=r[0]`	1437	`loginTime+=r[0]`
1452	`hours=loginTime/3600`	1438	`hours=loginTime/3600`
1453	`minutesLeft=(loginTime%3600)/60`	1439	`minutesLeft=(loginTime%3600)/60`
1454	`worksheet.write(25+17,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)`	1440	`worksheet.write(17,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)`
1455	`conn.close()`	1441	`conn.close()`
1456		1442
1457	`name_query=AGENT_FOLLOW_UP_QUERY_AIT %(agentId)`	1443	`name_query=AGENT_FOLLOW_UP_QUERY_AIT %(agentId)`
1458	`conn = getDbConnection()`	1444	`conn = getDbConnection()`
1459	`column=agentId`	1445	`column=agentId`
Line 1463...	Line 1449...
1463	`loginTime=0`	1449	`loginTime=0`
1464	`for r in result:`	1450	`for r in result:`
1465	`loginTime+=r[0]`	1451	`loginTime+=r[0]`
1466	`hours=loginTime/3600`	1452	`hours=loginTime/3600`
1467	`minutesLeft=(loginTime%3600)/60`	1453	`minutesLeft=(loginTime%3600)/60`
1468	`worksheet.write(25+18,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)`	1454	`worksheet.write(18,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)`
1469	`conn.close()`	1455	`conn.close()`
1470		1456
1471	`agentId+=1`	1457	`agentId+=1`
1472	`columnId+=1`	1458	`columnId+=1`
1473	`workbook.save(TMP_FILE)`	1459	`workbook.save(TMP_FILE)`
Line 1627...	Line 1613...
1627	`f = xlwt.Font()`	1613	`f = xlwt.Font()`
1628	`f.bold = True`	1614	`f.bold = True`
1629	`boldStyle.font = f`	1615	`boldStyle.font = f`
1630		1616
1631	`column = agentId`	1617	`column = agentId`
1632	`row = 25`	1618	`row = 0`
1633		1619
1634	`worksheet.write(row,column-1,'Call Disposition Type',style)`	1620	`worksheet.write(row,column-1,'Call Disposition Type',style)`
1635	`worksheet.write(row+1, column-1, 'Call Later', newStyle)`	1621	`worksheet.write(row+1, column-1, 'Call Later', newStyle)`
1636	`worksheet.write(row+2,column-1, 'Ringing No Answer', newStyle)`	1622	`worksheet.write(row+2,column-1, 'Ringing No Answer', newStyle)`
1637	`worksheet.write(row+3,column-1, 'Not Reachable', newStyle)`	1623	`worksheet.write(row+3,column-1, 'Not Reachable', newStyle)`
Line 1662...	Line 1648...
1662	`if cursor.rowcount ==0:`	1648	`if cursor.rowcount ==0:`
1663	`agentId+=1`	1649	`agentId+=1`
1664	`continue`	1650	`continue`
1665	`else:`	1651	`else:`
1666	`for r in result:`	1652	`for r in result:`
1667	`row = onBoardingDispositionMap.get(r[0])+26`	1653	`row = onBoardingDispositionMap.get(r[0])+1`
1668	`if onBoardingDispositionMap.get(r[0]) == 1 or onBoardingDispositionMap.get(r[0]) == 2 or onBoardingDispositionMap.get(r[0]) == 3 or followUpDispositionMap.get(r[0]) == 4:`	1654	`if onBoardingDispositionMap.get(r[0]) == 1 or onBoardingDispositionMap.get(r[0]) == 2 or onBoardingDispositionMap.get(r[0]) == 3 or followUpDispositionMap.get(r[0]) == 4:`
1669	`nonContactableData+=int(r[1])`	1655	`nonContactableData+=int(r[1])`
1670	`else:`	1656	`else:`
1671	`contactableData+=r[1]`	1657	`contactableData+=r[1]`
1672	`currentList.append(str(onBoardingDispositionMap.get(r[0])+1))`	1658	`currentList.append(str(onBoardingDispositionMap.get(r[0])+1))`
1673	`column = agentId`	1659	`column = agentId`
1674	`worksheet.write(row, columnId, r[1],center_alignment)`	1660	`worksheet.write(row, columnId, r[1],center_alignment)`
1675	`remainingList = list(set(onBoardingList) - set(currentList))`	1661	`remainingList = list(set(onBoardingList) - set(currentList))`
1676		1662
1677	`for i,val in enumerate(remainingList):`	1663	`for i,val in enumerate(remainingList):`
1678	`row = int(val)+25`	1664	`row = int(val)+0`
1679	`column = agentId`	1665	`column = agentId`
1680	`worksheet.write(row, columnId, 0,center_alignment)`	1666	`worksheet.write(row, columnId, 0,center_alignment)`
1681	`totalDialedOut = contactableData+nonContactableData`	1667	`totalDialedOut = contactableData+nonContactableData`
1682	`worksheet.write(25+6,columnId,round((contactableData/float(totalDialedOut))*100,2),center_alignment)`	1668	`worksheet.write(6,columnId,round((contactableData/float(totalDialedOut))*100,2),center_alignment)`
1683	`worksheet.write(25+7,columnId,round((nonContactableData/float(totalDialedOut))*100,2),center_alignment)`	1669	`worksheet.write(7,columnId,round((nonContactableData/float(totalDialedOut))*100,2),center_alignment)`
1684	`worksheet.write(25+8,columnId,totalDialedOut,center_alignment)`	1670	`worksheet.write(8,columnId,totalDialedOut,center_alignment)`
1685	`conn.close()`	1671	`conn.close()`
1686		1672
1687	`name_query=AGENT_NAME_QUERY %(agentId)`	1673	`name_query=AGENT_NAME_QUERY %(agentId)`
1688	`conn = getDbConnection()`	1674	`conn = getDbConnection()`
1689	`column=agentId`	1675	`column=agentId`
1690	`cursor = conn.cursor()`	1676	`cursor = conn.cursor()`
1691	`cursor.execute(name_query)`	1677	`cursor.execute(name_query)`
1692	`result = cursor.fetchall()`	1678	`result = cursor.fetchall()`
1693	`for r in result:`	1679	`for r in result:`
1694	`worksheet.write(25,columnId,r,style)`	1680	`worksheet.write(0,columnId,r,style)`
1695	`conn.close()`	1681	`conn.close()`
1696		1682
1697		1683
1698	`name_query=AGENT_ONBOARDING_QUERY_LOGIN_TIME %(agentId)`	1684	`name_query=AGENT_ONBOARDING_QUERY_LOGIN_TIME %(agentId)`
1699	`conn = getDbConnection()`	1685	`conn = getDbConnection()`
Line 1705...	Line 1691...
1705	`for r in result:`	1691	`for r in result:`
1706	`loginTime+=r[0].seconds`	1692	`loginTime+=r[0].seconds`
1707		1693
1708	`hours=loginTime/3600`	1694	`hours=loginTime/3600`
1709	`minutesLeft=(loginTime%3600)/60`	1695	`minutesLeft=(loginTime%3600)/60`
1710	`worksheet.write(25+9,columnId,str(hours)+':'+str(minutesLeft),center_alignment)`	1696	`worksheet.write(9,columnId,str(hours)+':'+str(minutesLeft),center_alignment)`
1711	`conn.close()`	1697	`conn.close()`
1712		1698
1713	`name_query=AGENT_ONBOARDING_QUERY_DURATION %(agentId)`	1699	`name_query=AGENT_ONBOARDING_QUERY_DURATION %(agentId)`
1714	`conn = getDbConnection()`	1700	`conn = getDbConnection()`
1715	`column=agentId`	1701	`column=agentId`
Line 1719...	Line 1705...
1719	`loginTime=0`	1705	`loginTime=0`
1720	`for r in result:`	1706	`for r in result:`
1721	`loginTime+=r[0]`	1707	`loginTime+=r[0]`
1722	`hours=loginTime/3600`	1708	`hours=loginTime/3600`
1723	`minutesLeft=(loginTime%3600)/60`	1709	`minutesLeft=(loginTime%3600)/60`
1724	`worksheet.write(25+10,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)`	1710	`worksheet.write(10,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)`
1725	`conn.close()`	1711	`conn.close()`
1726		1712
1727	`name_query=AGENT_ONBOARDING_QUERY_AHT %(agentId)`	1713	`name_query=AGENT_ONBOARDING_QUERY_AHT %(agentId)`
1728	`conn = getDbConnection()`	1714	`conn = getDbConnection()`
1729	`column=agentId`	1715	`column=agentId`
Line 1733...	Line 1719...
1733	`loginTime=0`	1719	`loginTime=0`
1734	`for r in result:`	1720	`for r in result:`
1735	`loginTime+=r[0]`	1721	`loginTime+=r[0]`
1736	`hours=loginTime/3600`	1722	`hours=loginTime/3600`
1737	`minutesLeft=(loginTime%3600)/60`	1723	`minutesLeft=(loginTime%3600)/60`
1738	`worksheet.write(25+11,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)`	1724	`worksheet.write(11,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)`
1739	`conn.close()`	1725	`conn.close()`
1740		1726
1741	`name_query=AGENT_ONBOARDING_QUERY_AIT %(agentId)`	1727	`name_query=AGENT_ONBOARDING_QUERY_AIT %(agentId)`
1742	`conn = getDbConnection()`	1728	`conn = getDbConnection()`
1743	`column=agentId`	1729	`column=agentId`
Line 1747...	Line 1733...
1747	`loginTime=0`	1733	`loginTime=0`
1748	`for r in result:`	1734	`for r in result:`
1749	`loginTime+=r[0]`	1735	`loginTime+=r[0]`
1750	`hours=loginTime/3600`	1736	`hours=loginTime/3600`
1751	`minutesLeft=(loginTime%3600)/60`	1737	`minutesLeft=(loginTime%3600)/60`
1752	`worksheet.write(25+12,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)`	1738	`worksheet.write(12,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)`
1753	`conn.close()`	1739	`conn.close()`
1754		1740
1755	`agentId+=1`	1741	`agentId+=1`
1756	`columnId+=1`	1742	`columnId+=1`
1757	`workbook.save(TMP_FILE)`	1743	`workbook.save(TMP_FILE)`
1758		-
1759		-
1760		1744
1761	`def main():`	1745	`def main():`
1762	`parser = optparse.OptionParser()`	1746	`parser = optparse.OptionParser()`
1763	`parser.add_option("-T", "--reporttype", dest="reporttype",`	1747	`parser.add_option("-T", "--reporttype", dest="reporttype",`
1764	`default="crmoutbound",`	1748	`default="crmoutbound",`

Subversion Repositories SmartDukaan

(root)/trunk/PyDTR/src/dtr/reports/crmoutbound.py – Rev 18673 → 19160