Subversion Repositories SmartDukaan

'''
Created on 27-May-2015

@author: kshitij
'''
from datetime import date, datetime, timedelta
from dtr.storage.Mysql import getOrdersAfterDate, \
    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 xlrd import open_workbook
from xlutils.copy import copy
from xlwt.Workbook import Workbook
import MySQLdb
import smtplib
import time
import xlwt
import pymongo
from shop2020.utils.Utils import to_py_date, to_java_date
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

DB_HOST = "localhost"
DB_USER = "root"
DB_PASSWORD = "shop2020"
DB_NAME = "dtr"
TMP_FILE = "/tmp/CRM_OutBound_Report.xls"  

con = None

SENDER = "cnc.center@shop2020.in"
PASSWORD = "5h0p2o2o"
SUBJECT = "CRM Outbound Report for" + date.today().isoformat()
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']
onBoardingList=['1','2','3','4','5'] 
dispositionMap = {  'call_later':0,
                    'ringing_no_answer':1,
                    'not_reachable':2,
                    'switch_off':3,
                    'invalid_no':4,
                    'wrong_no':5,
                    'hang_up':6,
                    'retailer_not_interested':7,
                    'alreadyuser':8,
                    'verified_link_sent':9,
                    'accessory_retailer':10,
                    'service_center_retailer':11,
                    'not_retailer':12, 
                    'recharge_retailer':13,
                    'onboarded':14
                }
followUpDispositionMap = {  'call_later':0,
                    'ringing_no_answer':1,
                    '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;
"""
AGENT_FRESH_QUERY="""
select call_disposition,count(1) from 
(select * from (select * from callhistory where date(created)=date(now()) 
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());
"""
FRESH_QUERY_DURATION="""
select sum(duration_sec) from callhistory where call_type ='fresh' and date(created)=date(now());
"""
AGENT_FRESH_QUERY_LOGIN_TIME="""
select TIMEDIFF(logoutTime,loginTime) from agentlogintimings where role='fresh' and date(created)=date(now()) 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;
"""

FOLLOW_UP_QUERY="""
select call_disposition,count(1) from 
(select * from (select * from callhistory where date(created)=date(now()) 
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()) 
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());
"""
FOLLOW_UP_QUERY_DURATION="""
select sum(duration_sec) from callhistory where call_type ='followup' and date(created)=date(now());
"""
AGENT_FOLLOW_UP_QUERY_LOGIN_TIME="""
select TIMEDIFF(logoutTime,loginTime) from agentlogintimings where role='followup' and date(created)=date(now()) 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;
"""

ONBOARDING_QUERY="""
select call_disposition,count(1) from 
(select * from (select * from callhistory where date(created)=date(now()) 
and call_type ='onboarding' order by created desc) 
as a group by retailer_id) 
a1 group by date(created), call_disposition;
"""
AGENT_ONBOARDING_QUERY="""
select call_disposition,count(1) from 
(select * from (select * from callhistory where date(created)=date(now()) 
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;
"""
AGENT_ONBOARDING_QUERY_DURATION="""
select sum(duration_sec) from callhistory where call_type ='onboarding' and date(created)=date(now()) and agent_id=%s;
"""



def getDbConnection():
    return MySQLdb.connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME)


def generateFreshCallingReport():
    datesql=FRESH_QUERY 
    conn = getDbConnection()
    
    cursor = conn.cursor()
    cursor.execute(datesql)
    result = cursor.fetchall()
    global workbook
    workbook = xlwt.Workbook()
    worksheet = workbook.add_sheet("Fresh")
    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, 'Invalid Number', newStyle)
    worksheet.write(6,column, 'Wrong Number', newStyle)
    worksheet.write(7,column, 'Hang Up', newStyle)
    worksheet.write(8,column, 'Retailer Not Interested', newStyle)
    worksheet.write(9,column, 'Already Profitmandi user', boldStyle)   
    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(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)
    contactableData=0
    nonContactableData=0
    
    for r in result:
        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(row))
        column = 1
        worksheet.write(row, column, r[1])
        
    remainingList = list(set(freshList) - set(currentList))
    for i,val in enumerate(remainingList):
        row = int(val)
        column = 1
        worksheet.write(row, column, 0)
    totalDispositions=contactableData+nonContactableData
    worksheet.write(15,1,(contactableData/float(totalDispositions))*100)
    worksheet.write(16,1,(nonContactableData/float(totalDispositions))*100)
    
    
    conn.close()
    datesql=FRESH_QUERY_LOGIN_TIME 
    conn = getDbConnection()
    
    cursor = conn.cursor()
    cursor.execute(datesql)
    result = cursor.fetchall()
    agentLoginList=[]
    loginTime=0
    for r in result:
        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(18,1,str(hours) + ':'+ str(minutesLeft))   
    worksheet.write(19,1,totalDispositions)
    print 'Avg' + str(totalDispositions/len(list(set(agentLoginList))))
    print 'list' + list(set(agentLoginList))
    worksheet.write(20,1,totalDispositions/len(list(set(agentLoginList))))
    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)))
    
    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()
    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 = agentId
    row = 25
    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)
    worksheet.write(row+5,column-1, 'Invalid Number', newStyle)
    worksheet.write(row+6,column-1, 'Wrong Number', newStyle)
    worksheet.write(row+7,column-1, 'Hang Up', newStyle)
    worksheet.write(row+8,column-1, 'Retailer Not Interested', newStyle)
    worksheet.write(row+9,column-1, 'Already Profitmandi user', boldStyle)   
    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+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)
    
    columnId=agentId
    while True:
            if agentId >5 :
                break
            else: 
                datesql=AGENT_FRESH_QUERY %(agentId)
                conn = getDbConnection()
                
                cursor = conn.cursor()
                cursor.execute(datesql)
                result = cursor.fetchall()
                
                currentList=[]
                contactableData=0
                nonContactableData=0
                if cursor.rowcount ==0:
                    
                    agentId+=1
                    continue    
                else:
                    for r in result:
                        row = dispositionMap.get(r[0])+26
                        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])
                        for i,val in enumerate(remainingList):
                            row = int(val)+26
                            column = agentId
                            worksheet.write(row, columnId, 0)
                    totalDialedOut = contactableData+nonContactableData
                    worksheet.write(25+15,columnId,(contactableData/float(totalDialedOut))*100)
                    worksheet.write(25+16,columnId,(nonContactableData/float(totalDialedOut))*100)
                    worksheet.write(25+17,columnId,totalDialedOut)
                    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=AGENT_FRESH_QUERY_LOGIN_TIME %(agentId)
                    conn = getDbConnection()
                    column=agentId
                    cursor = conn.cursor()
                    cursor.execute(name_query)
                    result = cursor.fetchall()
                    loginTime=0
                    for r in result:
                        loginTime+=r[0].seconds
                        
                    hours=loginTime/3600
                    minutesLeft=(loginTime%3600)/60
                    worksheet.write(25+18,columnId,str(hours)+':'+str(minutesLeft))    
                    conn.close()
                    
                    name_query=AGENT_FRESH_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+19,columnId,str(int(hours))+':'+str(int(minutesLeft)))    
                    conn.close()
                    
                agentId+=1
                columnId+=1
    workbook.save(TMP_FILE)    
    #sendmail(["manas.kapoor@shop2020.in"], "", TMP_FILE, SUBJECT)        

def generateFollowUpCallingReport():
    datesql=FOLLOW_UP_QUERY 
    conn = getDbConnection()
    
    cursor = conn.cursor()
    cursor.execute(datesql)
    result = cursor.fetchall()
    rb = open_workbook(TMP_FILE,formatting_info=True)
    workbook = copy(rb)
    
    worksheet = workbook.add_sheet("FollowUp")
    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, '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(10,column, 'Non Contactable Data', newStyle)
    worksheet.write(11,column, 'Agents Logged In', style)
    worksheet.write(12,column, 'Average Login Time (In Hrs)', style)
    worksheet.write(13,column, 'Total Dialed Out', style)
    worksheet.write(14,column, 'Average Dialed Out per agent', style)
    worksheet.write(15,column, 'Average Call Duration (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 or followUpDispositionMap.get(r[0]) == 4:
            nonContactableData+=int(r[1])
        else:
            contactableData+=r[1] 
        currentList.append(str(row))
        column = 1
        worksheet.write(row, column, r[1])
    
    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)
    totalDispositions=contactableData+nonContactableData
    worksheet.write(9,1,(contactableData/float(totalDispositions))*100)
    worksheet.write(10,1,(nonContactableData/float(totalDispositions))*100)
    
    
    conn.close()
    datesql=FOLLOW_UP_QUERY_LOGIN_TIME 
    conn = getDbConnection()
    
    cursor = conn.cursor()
    cursor.execute(datesql)
    result = cursor.fetchall()
    agentLoginList=[]
    averageLoginTime=0
    loginTime=0
    for r in result:
        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(12,1,str(hours) + ':'+ str(minutesLeft))   
    worksheet.write(13,1,totalDispositions)
    worksheet.write(14,1,(contactableData+nonContactableData)/len(list(set(agentLoginList))))
    conn.close()
    datesql=FOLLOW_UP_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(15,1,str(int(hours))+':'+str(int(minutesLeft)))
    workbook.save(TMP_FILE)
    #sendmail(["manas.kapoor@shop2020.in"], "", TMP_FILE, SUBJECT)        


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()
    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 = agentId
    row = 25
    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)
    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+9,column-1, 'Contactable Data', newStyle)
    worksheet.write(row+10,column-1, 'Non Contactable Data', newStyle)
    worksheet.write(row+11,column-1, 'Total Dialed Out', style)
    worksheet.write(row+12,column-1, 'Login Time (In Hrs)', style)
    worksheet.write(row+13,column-1, 'Call Duration (In Hrs)', style)
    
    columnId=agentId
    while True:
            if agentId >5 :
                break
            else: 
                datesql=AGENT_FOLLOW_UP_QUERY %(agentId)
                conn = getDbConnection()
                
                cursor = conn.cursor()
                cursor.execute(datesql)
                result = cursor.fetchall()
                
                currentList=[]
                contactableData=0
                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:
                            nonContactableData+=int(r[1])
                        else:
                            contactableData+=r[1] 
                        currentList.append(str(followUpDispositionMap.get(r[0])+1))
                        column = agentId
                        worksheet.write(row, columnId, r[1])
                    remainingList = list(set(followUpList) - set(currentList))
                    
                    for i,val in enumerate(remainingList):
                        row = int(val)+25
                        column = agentId
                        worksheet.write(row, columnId, 0)
                    totalDialedOut = contactableData+nonContactableData
                    worksheet.write(25+9,columnId,(contactableData/float(totalDialedOut))*100)
                    worksheet.write(25+10,columnId,(nonContactableData/float(totalDialedOut))*100)
                    worksheet.write(25+11,columnId,totalDialedOut)
                    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=AGENT_FOLLOW_UP_QUERY_LOGIN_TIME %(agentId)
                    conn = getDbConnection()
                    column=agentId
                    cursor = conn.cursor()
                    cursor.execute(name_query)
                    result = cursor.fetchall()
                    loginTime=0
                    for r in result:
                        loginTime+=r[0].seconds
                        
                    hours=loginTime/3600
                    minutesLeft=(loginTime%3600)/60
                    worksheet.write(25+12,columnId,str(hours)+':'+str(minutesLeft))    
                    conn.close()
                    
                    name_query=AGENT_FOLLOW_UP_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+13,columnId,str(int(hours))+':'+str(int(minutesLeft)))    
                    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()
    
    cursor = conn.cursor()
    cursor.execute(datesql)
    result = cursor.fetchall()
    rb = open_workbook(TMP_FILE,formatting_info=True)
    workbook = copy(rb)
    
    worksheet = workbook.add_sheet("Onboarding")
    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, 'Successful', newStyle)
    worksheet.write(6,column, 'Contactable Data', newStyle)
    worksheet.write(7,column, 'Non Contactable Data', newStyle)
    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)
    
    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])
    
    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)
    totalDispositions=contactableData+nonContactableData
    worksheet.write(6,1,(contactableData/float(totalDispositions))*100)
    worksheet.write(7,1,(nonContactableData/float(totalDispositions))*100)
    
    
    conn.close()
    datesql=ONBOARDING_QUERY_LOGIN_TIME 
    conn = getDbConnection()
    
    cursor = conn.cursor()
    cursor.execute(datesql)
    result = cursor.fetchall()
    agentLoginList=[]
    averageLoginTime=0
    loginTime=0
    for r in result:
        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(9,1,str(hours) + ':'+ str(minutesLeft))   
    worksheet.write(10,1,totalDispositions)
    worksheet.write(11,1,(contactableData+nonContactableData)/len(list(set(agentLoginList))))
    conn.close()
    datesql=ONBOARDING_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(12,1,str(int(hours))+':'+str(int(minutesLeft)))
    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()
    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 = agentId
    row = 25
    
    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)
    worksheet.write(row+5,column-1, 'Successful', newStyle)
    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)
    
    columnId=agentId
    while True:
            if agentId >5 :
                break
            else: 
                datesql=AGENT_ONBOARDING_QUERY %(agentId)
                conn = getDbConnection()
                
                cursor = conn.cursor()
                cursor.execute(datesql)
                result = cursor.fetchall()
                
                currentList=[]
                contactableData=0
                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])
                    remainingList = list(set(onBoardingList) - set(currentList))
                    
                    for i,val in enumerate(remainingList):
                        row = int(val)+25
                        column = agentId
                        worksheet.write(row, columnId, 0)
                    totalDialedOut = contactableData+nonContactableData
                    worksheet.write(25+6,columnId,(contactableData/float(totalDialedOut))*100)
                    worksheet.write(25+7,columnId,(nonContactableData/float(totalDialedOut))*100)
                    worksheet.write(25+8,columnId,totalDialedOut)
                    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)
                    conn = getDbConnection()
                    column=agentId
                    cursor = conn.cursor()
                    cursor.execute(name_query)
                    result = cursor.fetchall()
                    loginTime=0
                    for r in result:
                        loginTime+=r[0].seconds
                        
                    hours=loginTime/3600
                    minutesLeft=(loginTime%3600)/60
                    worksheet.write(25+9,columnId,str(hours)+':'+str(minutesLeft))    
                    conn.close()
                    
                    name_query=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)))    
                    conn.close()
                    
                agentId+=1
                columnId+=1
    workbook.save(TMP_FILE)          


        
def main():
    generateFreshCallingReport()
    generateAgentWiseFreshCallingReport()
    generateFollowUpCallingReport()
    generateAgentWiseFollowupCallingReport()
    generateOnBoardingCallingReport()
    generateAgentWiseOnboardingCallingReport()

def sendmail(email, message, fileName, title):
    if email == "":
        return
    mailServer = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)
    mailServer.ehlo()
    mailServer.starttls()
    mailServer.ehlo()
    
    # Create the container (outer) email message.
    msg = MIMEMultipart()
    msg['Subject'] = title
    msg.preamble = title
    html_msg = MIMEText(message, 'html')
    msg.attach(html_msg)
    
    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']
    mailServer.login(SENDER, PASSWORD)
    mailServer.sendmail(PASSWORD, MAILTO, msg.as_string())

if __name__ == '__main__':
    main()