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

     getOrdersByTag
 from email import encoders
 from email.mime.base import MIMEBase
 from email.mime.multipart import MIMEMultipart
 from email.mime.text import MIMEText
-from pymongo.mongo_client import MongoClient
+#from pymongo.mongo_client import MongoClient
 from xlrd import open_workbook
 from xlutils.copy import copy
 from xlwt.Workbook import Workbook
 import MySQLdb
 import smtplib
 import time
 import xlwt
-import pymongo
+# import pymongo
 from datetime import datetime
 from elixir import *
 from dtr.storage import DataService
 from dtr.storage.DataService import Orders, Users, CallHistory
 from sqlalchemy.sql.expression import func
 freshList=['1','2','3','4','5','6','7','8','9','10','11','12','13']
 followUpList=['1','2','3','4','5','6','7','8','9','10']
 onBoardingList=['1','2','3','4','5']
 accsList=['1','2','3','4','5','6','7','8','9','10','11','12']
 accsOrdersList=['1','2','3','4','5','6','7','8','9','10','11','12','13','14']
+inactiveUsersList=['1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23']
 dispositionMap = {  'call_later':0,
                     'ringing_no_answer':1,
                     'not_reachable':2,
                     'switch_off':3,
                     'invalid_no':4,
                 'order_process':10,
                 'placed_order':11,
                 'place_order':12
+              }
+inactiveUsersDispositionMap = {  'call_later':1,
+                'ringing_no_answer':2,
+                'not_reachable':3,
+                'switch_off':4,
+                'app_uninstalled':5,
+                'place_order':6,
+                'pricing_issue':7,
+                'technical_issue':8,
+                'shipping_issue':9,
+                'product_availability':10,
+                'return_replacement':11,
+                'product_quality_issue':12,
+                'delayed_delivery':13,
+                'order_diff_acc':14,
+                'not_retailer':15,
+                'business_closed':16,
+                'invalid_no':17,
+                'wrong_no':18,
+                'hang_up':19,
+                'service_center_retailer':20,
+                'recharge_retailer':21,
+                'other_complaint':22,
+                'other_reason':23,
+                'verified_link_sent':24
+              }
 accsDispositionOrdersMap = {  'call_later':1,
                 'ringing_no_answer':2,
                 'not_reachable':3,
                 'switch_off':4,
                 'technical_issue':5,
 ACCS_ORDER_PRODUCT_PRICING="""
 select user_id,call_disposition,product_input,pricing_input from productpricinginputs where
 date(created)=date(now() - interval 1 day) and product_input not like '' and project_id=3;
 """
+ACCS_INACTIVE_USERS_QUERY="""
+select call_disposition,count(1) from
+(select * from (select * from callhistorycrm where date(created)=date(now()- interval 1 day)
+and project_id=5 order by created desc)
+as a group by user_id)
+a1 group by date(created), call_disposition;
+"""
+ACCS_INACTIVE_USERS_AGENTS_CALLED_COUNT="""
+select distinct(agent_id) from callhistorycrm where project_id=5 and date(created) = date(now() - interval 1 day)
+"""
+ACCS_INACTIVE_USERS_QUERY_LOGIN_TIME="""
+select agent_id,TIMEDIFF(logoutTime,loginTime) from agentlogintimings where role='inactive_users' and date(created)=date(now() - interval 1 day);
+"""
+ACCS_INACTIVE_USERS_DISPOSITION_DESCRIPTION="""
+select user_id,call_disposition,disposition_description,disposition_comments
+from callhistorycrm where call_disposition not in ('ringing_no_answer','not_reachable','switch_off','call_later')
+and date(created)=date(now() - interval 1 day) and project_id=5"""
+ACCS_INACTIVE_USERS_PRODUCT_PRICING="""
+select user_id,call_disposition,product_input,pricing_input from productpricinginputs where
+date(created)=date(now() - interval 1 day) and product_input not like '' and project_id=5;
+"""
 def getDbConnection():
     return MySQLdb.connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME)
 def generateAccessoriesCartReport():
     datesql=ACCS_CART_QUERY
             worksheet.write(row,column,data)
             column+=1
     newWorkbook.save(RET_FILE)
+def generateInactiveUsersReport():
+    datesql=ACCS_INACTIVE_USERS_QUERY
+    conn = getDbConnection()
+    cursor = conn.cursor()
+    cursor.execute(datesql)
+    result = cursor.fetchall()
+    global newWorkbook
+    newWorkbook = xlwt.Workbook()
+    worksheet = newWorkbook.add_sheet("Inactive Users")
+    boldStyle = xlwt.XFStyle()
+    newStyle= xlwt.XFStyle()
+    style = xlwt.XFStyle()
+    pattern = xlwt.Pattern()
+    pattern.pattern = xlwt.Pattern.SOLID_PATTERN
+    pattern.pattern_fore_colour = xlwt.Style.colour_map['yellow']
+    style.pattern = pattern
+    f = xlwt.Font()
+    f.bold = True
+    boldStyle.font = f
+    column = 0
+    row = 0
+    currentList=[]
+    worksheet.write(0,0,'Call Disposition Type',style)
+    worksheet.write(0,1,'Count',style)
+    worksheet.write(1, column, 'Call Later', newStyle)
+    worksheet.write(2,column, 'Ringing No Answer', newStyle)
+    worksheet.write(3,column, 'Not Reachable', newStyle)
+    worksheet.write(4,column, 'Switched Off', newStyle)
+    worksheet.write(5,column, 'App Uninstalled', newStyle)
+    worksheet.write(6,column, 'Will Place Order', boldStyle)
+    worksheet.write(7,column, 'Pricing Issue(High)', newStyle)
+    worksheet.write(8,column, 'Technical Issue', newStyle)
+    worksheet.write(9,column, 'Shipping Issue', newStyle)
+    worksheet.write(10,column, 'Product Not Available', newStyle)
+    worksheet.write(11,column, 'Return Replacement Pending', newStyle)
+    worksheet.write(12,column, 'Product Quality Issue', newStyle)
+    worksheet.write(13, column, 'Delayed Delivery', newStyle)
+    worksheet.write(14,column, 'Placing Order from Different Account', newStyle)
+    worksheet.write(15,column, 'Not a Retailer', newStyle)
+    worksheet.write(16,column, 'Business Closed', newStyle)
+    worksheet.write(17,column, 'Invalid No', newStyle)
+    worksheet.write(18,column, 'Wrong No', newStyle)
+    worksheet.write(19,column, 'Hang up', newStyle)
+    worksheet.write(20,column, 'Service Center', newStyle)
+    worksheet.write(21,column, 'Recharge Retailer', newStyle)
+    worksheet.write(22,column, 'Other Complaint', newStyle)
+    worksheet.write(23,column, 'Other Reasons', newStyle)
+    worksheet.write(24,column, 'App Link Sent', newStyle)
+    worksheet.write(25,column, 'Contactable Data (%)', newStyle)
+    worksheet.write(26,column, 'Non Contactable Data (%)', newStyle)
+    worksheet.write(27,column, 'Agents Logged In', style)
+    worksheet.write(28,column, 'Average Login Time (In Hrs)', style)
+    worksheet.write(29,column, 'Total Dialed Out', style)
+    worksheet.write(30,column, 'Average Dialed Out per agent', style)
+    contactableData=0
+    nonContactableData=0
+    global INACTIVE_USERS_WILL_PLACE_ORDER
+    INACTIVE_USERS_WILL_PLACE_ORDER=0
+    for r in result:
+        row = inactiveUsersDispositionMap.get(r[0])
+        if inactiveUsersDispositionMap.get(r[0]) == 2 or inactiveUsersDispositionMap.get(r[0]) == 3 or inactiveUsersDispositionMap.get(r[0]) == 4 or inactiveUsersDispositionMap.get(r[0]) == 17 or inactiveUsersDispositionMap.get(r[0]) == 18 or inactiveUsersDispositionMap.get(r[0]) == 19:
+            nonContactableData+=int(r[1])
+        else:
+            if inactiveUsersDispositionMap.get(r[0])== 6:
+                INACTIVE_USERS_WILL_PLACE_ORDER = r[1]
+            contactableData+=r[1]
+        currentList.append(str(row))
+        column = 1
+        worksheet.write(row, column, r[1],center_alignment)
+    remainingList = list(set(inactiveUsersList) - set(currentList))
+    for i,val in enumerate(remainingList):
+        row = int(val)
+        column = 1
+        worksheet.write(row, column, 0,center_alignment)
+    totalDispositions=contactableData+nonContactableData
+    global INACTIVE_USERS_TOTAL
+    global INACTIVE_USERS_SUCCESSFUL
+    INACTIVE_USERS_TOTAL = totalDispositions
+    INACTIVE_USERS_SUCCESSFUL=contactableData
+    if totalDispositions > 0:
+        worksheet.write(25,1,round((contactableData/float(totalDispositions))*100,2),center_alignment)
+        worksheet.write(26,1,round((nonContactableData/float(totalDispositions))*100,2),center_alignment)
+    conn.close()
+    datesql=ACCS_INACTIVE_USERS_AGENTS_CALLED_COUNT
+    conn = getDbConnection()
+    cursor = conn.cursor()
+    cursor.execute(datesql)
+    result = cursor.fetchall()
+    agentLoginList=[]
+    for r in result:
+        agentLoginList.append(str(r[0]))
+    conn.close()
+    global INACTIVE_USERS_LOGIN
+    INACTIVE_USERS_LOGIN = len(list(set(agentLoginList)))
+    if len(list(set(agentLoginList))) > 0:
+        datesql=ACCS_INACTIVE_USERS_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(27,1,len(list(set(agentLoginList))),center_alignment)
+        worksheet.write(28,1,str(hours) + ':'+ str(minutesLeft),center_alignment)
+        worksheet.write(29,1,totalDispositions,center_alignment)
+        worksheet.write(30,1,totalDispositions/float(len(list(set(agentLoginList)))),center_alignment)
+        conn.close()
+    worksheet.write(0,5, 'User Id', style)
+    worksheet.write(0,6, 'Call Disposition', style)
+    worksheet.write(0,7, 'Disposition Description', style)
+    worksheet.write(0,8, 'Disposition Comments', style)
+    datesql = ACCS_INACTIVE_USERS_DISPOSITION_DESCRIPTION
+    conn = getDbConnection()
+    cursor = conn.cursor()
+    cursor.execute(datesql)
+    result = cursor.fetchall()
+    row=0
+    for r in result:
+        row=row+1
+        column =5
+        for data in r:
+            worksheet.write(row,column,data)
+            column+=1
+    conn.close()
+    worksheet.write(32,0, 'User Id', style)
+    worksheet.write(32,1, 'Call Disposition', style)
+    worksheet.write(32,2, 'Product Inputs', style)
+    worksheet.write(32,3, 'Pricing Inputs', style)
+    datesql = ACCS_INACTIVE_USERS_PRODUCT_PRICING
+    conn = getDbConnection()
+    cursor = conn.cursor()
+    cursor.execute(datesql)
+    result = cursor.fetchall()
+    row=20
+    for r in result:
+        row=row+1
+        column =0
+        for data in r:
+            worksheet.write(row,column,data)
+            column+=1
+    newWorkbook.save(RET_FILE)
 def generateAccessoriesTabsReport():
     datesql=ACCS_TAB_QUERY
     conn = getDbConnection()
     cursor = conn.cursor()
         generateAgentWiseFreshCallingReport()
         generateFollowUpCallingReport()
         generateAgentWiseFollowupCallingReport()
         generateOnBoardingCallingReport()
         generateAgentWiseOnboardingCallingReport()
-        #sendmail(["rajender.singh@shop2020.in"], "", TMP_FILE, SUBJECT)
+#         sendmail(["rajender.singh@shop2020.in"], "", TMP_FILE, SUBJECT)
         sendmail(["amit.sirohi@shop2020.in","rajneesh.arora@saholic.com","ritesh.chauhan@shop2020.in", "shailesh.kumar@shop2020.in","utkarsh@coreoutsourcingservices.com","gupta.varun@coreoutsourcingservices.com","rajender.singh@shop2020.in"], "", TMP_FILE, SUBJECT)
     if options.reporttype == 'retention':
-        generateAccessoriesCartReport()
+#         generateAccessoriesCartReport()
-        generateAccessoriesTabsReport()
+#         generateAccessoriesTabsReport()
-        generateAccessoriesOrderReport()
+#         generateAccessoriesOrderReport()
+        generateInactiveUsersReport()
-        #sendmailretention(["rajender.singh@shop2020.in"], "", RET_FILE, RET_SUBJECT)
+#         sendmailretention(["rajender.singh@shop2020.in"], "", RET_FILE, RET_SUBJECT)
         sendmailretention(["amit.sirohi@shop2020.in","rajneesh.arora@saholic.com", "shailesh.kumar@shop2020.in","chaitnaya.vats@shop2020.in","rajender.singh@shop2020.in"], "", RET_FILE, RET_SUBJECT)
 def sendmail(email, message, fileName, title):
     if email == "":
         return
     encoders.encode_base64(fileMsg)
     fileMsg.add_header('Content-Disposition', 'attachment;filename=' + fileName)
     msg.attach(fileMsg)
     MAILTO = ['amit.sirohi@shop2020.in','rajneesh.arora@saholic.com', 'shailesh.kumar@shop2020.in','gupta.varun@coreoutsourcingservices.com','utkarsh@coreoutsourcingservices.com','rajender.singh@shop2020.in']
-    #MAILTO = ['rajender.singh@saholic.com']
+#     MAILTO = ['rajender.singh@saholic.com']
     mailServer.login(SENDER, PASSWORD)
     try:
         mailServer.sendmail(SENDER, MAILTO, msg.as_string())
     except:
         m = Email('localhost')
         message4="Will Order % " +str(round((ACCS_CART_WILL_PLACE_ORDER/float(ACCS_CART_TOTAL))*100,2))
     else:
         message4="Will Order % 0"
     return "<u>" + heading + "</u><br>" +message+"<br>"+message1+"<br>"+message2+"<br>"+message3+"<br>"+message4+"<br>"
+def getInactiveUsersHtml():
+    heading = "Inactive Users(Project 5)"
+    message="No. of Agents " + str(INACTIVE_USERS_LOGIN)
+    message1="Total Calls attempted " + str(INACTIVE_USERS_TOTAL)
+    message2="Total Successful calls " + str(INACTIVE_USERS_SUCCESSFUL)
+    message3="Will Order "+ str(INACTIVE_USERS_WILL_PLACE_ORDER)
+    if INACTIVE_USERS_TOTAL!=0:
+        message4="Will Order % " +str(round((INACTIVE_USERS_WILL_PLACE_ORDER/float(INACTIVE_USERS_TOTAL))*100,2))
+    else:
+        message4="Will Order % 0"
+    return "<u>" + heading + "</u><br>" +message+"<br>"+message1+"<br>"+message2+"<br>"+message3+"<br>"+message4+"<br>"
 def getAccsTabHtml():
     heading = "Accessories Active Users(Project 2)"
     message="No. of Agents " + str(ACCS_TAB_LOGIN)
     message1="Total Calls attempted " + str(ACCS_TAB_TOTAL)
     message2="Total Successful calls " + str(ACCS_TAB_SUCCESSFUL)
     result = cursor.fetchall()
     inputs = "No of users who placed orders on Date " + str(result[0][0]) + "<br> Number of Users " + str(result[0][1])+ " <br> Total Orders " + str(result[0][2]) + "<br>Total Quantity " + str(result[0][3]) + "<br>Total Value " + str(result[0][4]) + "<br>"
     conn.close()
     return inputs
+def getInactiveUsersOrders():
+    datesql = "select  date(now() - interval 1 day), count(distinct user_id),count(distinct order_id), sum(quantity),sum(amount_paid) from allorder where store_id='spice' and category='Accs' and date(created_on)=date(now() - interval 1 day) and user_id in (select user_id from usercrmcallingdata where project_id=5 and date(created)>=date(now()- interval 7 day));"
+    conn = getDbConnection()
+    cursor = conn.cursor()
+    cursor.execute(datesql)
+    result = cursor.fetchall()
+    inputs = "No of users who placed orders on Date " + str(result[0][0]) + "<br> Number of Users " + str(result[0][1])+ " <br> Total Orders " + str(result[0][2]) + "<br>Total Quantity " + str(result[0][3]) + "<br>Total Value " + str(result[0][4]) + "<br>"
+    conn.close()
+    return inputs
 def getAccsTabOrders():
     datesql = "select  date(now() - interval 1 day), count(distinct user_id),count(distinct order_id), sum(quantity),sum(amount_paid) from allorder where store_id='spice' and category='Accs' and date(created_on)=date(now() - interval 1 day) and user_id in (select user_id from usercrmcallingdata where project_id=2 and date(created)>=date(now()- interval 7 day));"
     conn = getDbConnection()
     cursor = conn.cursor()
     cursor.execute(datesql)
     for r in result:
         inputs = inputs + "<br>" + r[0] + "-----" + str(r[1])
     conn.close()
     return inputs
+def getInactiveUsersProductPricingInput():
+    datesql = "select product_input,pricing_input from productpricinginputs where date(created)=date(now() - interval 1 day) and project_id=5 and product_input not like '';"
+    conn = getDbConnection()
+    cursor = conn.cursor()
+    cursor.execute(datesql)
+    result = cursor.fetchall()
+    inputs="Product Pricing Inputs"
+    for r in result:
+        inputs = inputs + "<br>" + r[0] + "-----" + str(r[1])
+    conn.close()
+    return inputs
 def getAccsTabProductPricingInput():
     datesql = "select product_input,pricing_input from productpricinginputs where date(created)=date(now() - interval 1 day) and project_id=2 and product_input not like '';"
     conn = getDbConnection()
     cursor = conn.cursor()
     cursor.execute(datesql)
     # Create the container (outer) email message.
     msg = MIMEMultipart()
     msg['Subject'] = title
     msg.preamble = title
-    msg.attach(MIMEText(getAccsCartHtml(), 'html'))
+#     msg.attach(MIMEText(getAccsCartHtml(), 'html'))
-    msg.attach(MIMEText(getAccsCartOrders(),'html'))
+#     msg.attach(MIMEText(getAccsCartOrders(),'html'))
-    msg.attach(MIMEText(getAccsCartProductPricingInput(),'html'))
+#     msg.attach(MIMEText(getAccsCartProductPricingInput(),'html'))
+#
-    msg.attach(MIMEText(getAccsTabHtml(), 'html'))
+#     msg.attach(MIMEText(getAccsTabHtml(), 'html'))
-    msg.attach(MIMEText(getAccsTabOrders(),'html'))
+#     msg.attach(MIMEText(getAccsTabOrders(),'html'))
-    msg.attach(MIMEText(getAccsTabProductPricingInput(),'html'))
+#     msg.attach(MIMEText(getAccsTabProductPricingInput(),'html'))
+#
-    msg.attach(MIMEText(getAccsOrdersHtml(), 'html'))
+#     msg.attach(MIMEText(getAccsOrdersHtml(), 'html'))
-    msg.attach(MIMEText(getAccsOrdersProject(),'html'))
+#     msg.attach(MIMEText(getAccsOrdersProject(),'html'))
-    msg.attach(MIMEText(getAccsOrdersProductPricingInput(),'html'))
+#     msg.attach(MIMEText(getAccsOrdersProductPricingInput(),'html'))
+    msg.attach(MIMEText(getInactiveUsersHtml(), 'html'))
+    msg.attach(MIMEText(getInactiveUsersOrders(),'html'))
+    msg.attach(MIMEText(getInactiveUsersProductPricingInput(),'html'))
     fileMsg = MIMEBase('application', 'vnd.ms-excel')
     fileMsg.set_payload(file(RET_FILE).read())
     encoders.encode_base64(fileMsg)
     fileMsg.add_header('Content-Disposition', 'attachment;filename=' + fileName)
     msg.attach(fileMsg)
     MAILTO = ['amit.sirohi@shop2020.in','rajneesh.arora@saholic.com', 'shailesh.kumar@shop2020.in','chaitnaya.vats@shop2020.in','rajender.singh@shop2020.in']
-    #MAILTO = ['rajender.singh@saholic.com']
+#     MAILTO = ['rajender.singh@saholic.com']
     mailServer.login(SENDER, PASSWORD)
     try:
         mailServer.sendmail(SENDER, MAILTO, msg.as_string())
     except:
         m = Email('localhost')

Subversion Repositories SmartDukaan

(root)//trunk/PyDTR/src/dtr/reports/crmoutbound.py – Rev 20046 → 20160