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

     agentLoginList=[]
     for r in result:
         agentLoginList.append(str(r[0]))
     conn.close()
-    datesql=FRESH_QUERY_LOGIN_TIME
-    conn = getDbConnection()
-    cursor = conn.cursor()
-    cursor.execute(datesql)
-    result = cursor.fetchall()
-    loginTime=0
-    for r in result:
-        loginTime+=r[1].seconds
-    averageLoginTime=loginTime/len(list(set(agentLoginList)))
-    hours=averageLoginTime/3600
-    minutesLeft=(averageLoginTime%3600)/60
-    worksheet.write(17,1,len(list(set(agentLoginList))),center_alignment)
-    worksheet.write(18,1,str(hours) + ':'+ str(minutesLeft),center_alignment)
-    worksheet.write(19,1,totalDispositions,center_alignment)
-    worksheet.write(20,1,totalDispositions/float(len(list(set(agentLoginList)))),center_alignment)
-    conn.close()
-    datesql=FRESH_QUERY_DURATION
-    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(21,1,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
-    conn.close()
-    datesql=FRESH_QUERY_AHT
-    conn = getDbConnection()
+    if len(list(set(agentLoginList))) > 0:
+        datesql=FRESH_QUERY_LOGIN_TIME
+        conn = getDbConnection()
-    cursor = conn.cursor()
+        cursor = conn.cursor()
-    cursor.execute(datesql)
+        cursor.execute(datesql)
-    result = cursor.fetchall()
+        result = cursor.fetchall()
-    loginTime=0
+        loginTime=0
-    for r in result:
+        for r in result:
-        loginTime+=r[0]
+            loginTime+=r[1].seconds
-    averageLoginTime=loginTime/len(list(set(agentLoginList)))
+        averageLoginTime=loginTime/len(list(set(agentLoginList)))
-    hours=averageLoginTime/3600
+        hours=averageLoginTime/3600
-    minutesLeft=(averageLoginTime%3600)/60
+        minutesLeft=(averageLoginTime%3600)/60
-    worksheet.write(22,1,len(list(set(agentLoginList))),center_alignment)
+        worksheet.write(17,1,len(list(set(agentLoginList))),center_alignment)
+        worksheet.write(18,1,str(hours) + ':'+ str(minutesLeft),center_alignment)
+        worksheet.write(19,1,totalDispositions,center_alignment)
+        worksheet.write(20,1,totalDispositions/float(len(list(set(agentLoginList)))),center_alignment)
+        conn.close()
+        datesql=FRESH_QUERY_DURATION
+        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(21,1,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
-    conn.close()
+        conn.close()
-    datesql=FRESH_QUERY_AIT
+        datesql=FRESH_QUERY_AHT
-    conn = getDbConnection()
+        conn = getDbConnection()
-    cursor = conn.cursor()
+        cursor = conn.cursor()
-    cursor.execute(datesql)
+        cursor.execute(datesql)
-    result = cursor.fetchall()
+        result = cursor.fetchall()
-    loginTime=0
+        loginTime=0
-    for r in result:
+        for r in result:
-        if r[0] is not None:
             loginTime+=r[0]
-    averageLoginTime=loginTime/len(list(set(agentLoginList)))
+        averageLoginTime=loginTime/len(list(set(agentLoginList)))
-    hours=averageLoginTime/3600
+        hours=averageLoginTime/3600
-    minutesLeft=(averageLoginTime%3600)/60
+        minutesLeft=(averageLoginTime%3600)/60
-    worksheet.write(23,1,len(list(set(agentLoginList))),center_alignment)
+        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:
+            if r[0] is not None:
+                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()
+        conn.close()
     datesql=FRESH_QUERY_LINKS_CONVERTED
     conn = getDbConnection()
     global Converted
     global AgentsLoggedIn
     AgentsLoggedIn=len(list(set(agentLoginList)))
                         for i,val in enumerate(remainingList):
                             row = int(val)+26
                             column = agentId
                             worksheet.write(row, columnId, 0,center_alignment)
                     totalDialedOut = contactableData+nonContactableData
+                    if totalDialedOut > 0:
-                    worksheet.write(25+15,columnId,round((contactableData/float(totalDialedOut))*100,2),center_alignment)
+                        worksheet.write(25+15,columnId,round((contactableData/float(totalDialedOut))*100,2),center_alignment)
-                    worksheet.write(25+16,columnId,round((nonContactableData/float(totalDialedOut))*100,2),center_alignment)
+                        worksheet.write(25+16,columnId,round((nonContactableData/float(totalDialedOut))*100,2),center_alignment)
-                    worksheet.write(25+17,columnId,totalDialedOut,center_alignment)
+                        worksheet.write(25+17,columnId,totalDialedOut,center_alignment)
                     conn.close()
                     name_query=AGENT_NAME_QUERY %(agentId)
                     conn = getDbConnection()
                     column=agentId
     for i,val in enumerate(remainingList):
         row = int(val)
         column = 1
         worksheet.write(row, column, 0,center_alignment)
     totalDispositions=contactableData+nonContactableData
+    if totalDispositions >0:
-    worksheet.write(12,1,round((contactableData/float(totalDispositions))*100,2),center_alignment)
+        worksheet.write(12,1,round((contactableData/float(totalDispositions))*100,2),center_alignment)
-    worksheet.write(13,1,round((nonContactableData/float(totalDispositions))*100,2),center_alignment)
+        worksheet.write(13,1,round((nonContactableData/float(totalDispositions))*100,2),center_alignment)
     conn.close()
     datesql=FOLLOW_UP_QUERY_LOGIN_TIME
     conn = getDbConnection()
     averageLoginTime=0
     loginTime=0
     for r in result:
         loginTime+=r[1].seconds
         agentLoginList.append(str(r[0]))
+    if len(list(set(agentLoginList))) > 0:
-    averageLoginTime=loginTime/len(list(set(agentLoginList)))
+        averageLoginTime=loginTime/len(list(set(agentLoginList)))
-    hours=averageLoginTime/3600
+        hours=averageLoginTime/3600
-    minutesLeft=(averageLoginTime%3600)/60
+        minutesLeft=(averageLoginTime%3600)/60
-    worksheet.write(14,1,len(list(set(agentLoginList))),center_alignment)
+        worksheet.write(14,1,len(list(set(agentLoginList))),center_alignment)
-    worksheet.write(15,1,str(hours) + ':'+ str(minutesLeft),center_alignment)
+        worksheet.write(15,1,str(hours) + ':'+ str(minutesLeft),center_alignment)
-    worksheet.write(16,1,totalDispositions,center_alignment)
+        worksheet.write(16,1,totalDispositions,center_alignment)
-    worksheet.write(17,1,(contactableData+nonContactableData)/len(list(set(agentLoginList))),center_alignment)
+        worksheet.write(17,1,(contactableData+nonContactableData)/len(list(set(agentLoginList))),center_alignment)
-    conn.close()
+        conn.close()
-    datesql=FOLLOW_UP_QUERY_DURATION
+        datesql=FOLLOW_UP_QUERY_DURATION
-    conn = getDbConnection()
+        conn = getDbConnection()
-    cursor = conn.cursor()
+        cursor = conn.cursor()
-    cursor.execute(datesql)
+        cursor.execute(datesql)
-    result = cursor.fetchall()
+        result = cursor.fetchall()
-    for r in result:
+        for r in result:
-        totalCallDuration= r[0]
+            totalCallDuration= r[0]
-    averageCallDuration=totalCallDuration/len(list(set(agentLoginList)))
+        averageCallDuration=totalCallDuration/len(list(set(agentLoginList)))
-    hours=averageCallDuration/3600
+        hours=averageCallDuration/3600
-    minutesLeft=(averageCallDuration%3600)/60
+        minutesLeft=(averageCallDuration%3600)/60
-    worksheet.write(18,1,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
+        worksheet.write(18,1,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
-    conn.close()
+        conn.close()
-    datesql=FOLLOW_UP_QUERY_AHT
+        datesql=FOLLOW_UP_QUERY_AHT
-    conn = getDbConnection()
+        conn = getDbConnection()
-    cursor = conn.cursor()
+        cursor = conn.cursor()
-    cursor.execute(datesql)
+        cursor.execute(datesql)
-    result = cursor.fetchall()
+        result = cursor.fetchall()
-    loginTime=0
+        loginTime=0
-    for r in result:
+        for r in result:
-        loginTime+=r[0]
+            loginTime+=r[0]
-    averageLoginTime=loginTime/len(list(set(agentLoginList)))
+        averageLoginTime=loginTime/len(list(set(agentLoginList)))
-    hours=averageLoginTime/3600
+        hours=averageLoginTime/3600
-    minutesLeft=(averageLoginTime%3600)/60
+        minutesLeft=(averageLoginTime%3600)/60
-    worksheet.write(19,1,len(list(set(agentLoginList))),center_alignment)
+        worksheet.write(19,1,len(list(set(agentLoginList))),center_alignment)
-    conn.close()
+        conn.close()
-    datesql=FOLLOW_UP_QUERY_AIT
+        datesql=FOLLOW_UP_QUERY_AIT
-    conn = getDbConnection()
+        conn = getDbConnection()
-    cursor = conn.cursor()
+        cursor = conn.cursor()
-    cursor.execute(datesql)
+        cursor.execute(datesql)
-    result = cursor.fetchall()
+        result = cursor.fetchall()
-    loginTime=0
+        loginTime=0
-    for r in result:
+        for r in result:
-        loginTime+=r[0]
+            loginTime+=r[0]
-    averageLoginTime=loginTime/len(list(set(agentLoginList)))
+        averageLoginTime=loginTime/len(list(set(agentLoginList)))
-    hours=averageLoginTime/3600
+        hours=averageLoginTime/3600
-    minutesLeft=(averageLoginTime%3600)/60
+        minutesLeft=(averageLoginTime%3600)/60
-    worksheet.write(20,1,len(list(set(agentLoginList))),center_alignment)
+        worksheet.write(20,1,len(list(set(agentLoginList))),center_alignment)
     workbook.save(TMP_FILE)
 def generateAgentWiseFollowupCallingReport():
     agentId=3
                     for i,val in enumerate(remainingList):
                         row = int(val)+25
                         column = agentId
                         worksheet.write(row, columnId, 0,center_alignment)
                     totalDialedOut = contactableData+nonContactableData
+                    if totalDialedOut>0:
-                    worksheet.write(25+12,columnId,round((contactableData/float(totalDialedOut))*100,2),center_alignment)
+                        worksheet.write(25+12,columnId,round((contactableData/float(totalDialedOut))*100,2),center_alignment)
-                    worksheet.write(25+13,columnId,round((nonContactableData/float(totalDialedOut))*100,2),center_alignment)
+                        worksheet.write(25+13,columnId,round((nonContactableData/float(totalDialedOut))*100,2),center_alignment)
-                    worksheet.write(25+14,columnId,totalDialedOut,center_alignment)
+                        worksheet.write(25+14,columnId,totalDialedOut,center_alignment)
                     conn.close()
                     name_query=AGENT_NAME_QUERY %(agentId)
                     conn = getDbConnection()
                     column=agentId
     generateAgentWiseFreshCallingReport()
     generateFollowUpCallingReport()
     generateAgentWiseFollowupCallingReport()
     generateOnBoardingCallingReport()
     generateAgentWiseOnboardingCallingReport()
-    sendmail(["manas.kapoor@shop2020.in","amit.sirohi@shop2020.in","rajneesh.arora@saholic.com","ritesh.chauhan@shop2020.in", "shailesh.kumar@shop2020.in"], "", TMP_FILE, SUBJECT)
+    #sendmail(["manas.kapoor@shop2020.in","amit.sirohi@shop2020.in","rajneesh.arora@saholic.com","ritesh.chauhan@shop2020.in", "shailesh.kumar@shop2020.in"], "", TMP_FILE, SUBJECT)
-    #sendmail(["manas.kapoor@shop2020.in"], "", TMP_FILE, SUBJECT)
+    sendmail(["manas.kapoor@shop2020.in"], "", TMP_FILE, SUBJECT)
 def sendmail(email, message, fileName, title):
     if email == "":
         return
     mailServer = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)
     fileMsg.set_payload(file(TMP_FILE).read())
     encoders.encode_base64(fileMsg)
     fileMsg.add_header('Content-Disposition', 'attachment;filename=' + fileName)
     msg.attach(fileMsg)
-    MAILTO = ['manas.kapoor@saholic.com','amit.sirohi@shop2020.in','rajneesh.arora@saholic.com', 'ritesh.chauhan@shop2020.in', 'shailesh.kumar@shop2020.in']
+    #MAILTO = ['manas.kapoor@saholic.com','amit.sirohi@shop2020.in','rajneesh.arora@saholic.com', 'ritesh.chauhan@shop2020.in', 'shailesh.kumar@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 15502 → 15505