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' + str(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()