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
from dtr.utils.MailSender import Email

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

con = None

SENDER = "adwords@shop2020.in"
PASSWORD = "adwords_shop2020"
SUBJECT = "CRM Outbound Report for " + str(date.today() - timedelta(days=1))
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','8','9','10']
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,
                    'accessory_retailer':7,
                    'service_center_retailer':8,
                    'not_retailer':9, 
                    'recharge_retailer':10
                }
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;
# """
FRESH_QUERY="""
select call_disposition,count(1) from 
(select * from (select * from callhistory where date(created)=date(now() - INTERVAL 1 DAY) 
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() - INTERVAL 1 DAY) 
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() - INTERVAL 1 DAY);
"""
FRESH_QUERY_DURATION="""
select sum(duration_sec) from callhistory where call_type ='fresh' and date(created)=date(now() - INTERVAL 1 DAY);
"""
FRESH_QUERY_AHT="""
select timestampdiff(second, callhistory.last_fetch_time, created) from callhistory where last_fetch_time is not null and call_type='fresh' and date(created)=date(now() - INTERVAL 1 DAY );
"""
FRESH_QUERY_AIT="""
select timestampdiff(second, last_action_time, created) from fetchdatahistory where call_type='fresh' and date(created)=date(now() - INTERVAL 1 DAY);
"""
#select count(*) from retailerlinks where date(activated) is not null and date(activated)=date(now() - interval 1 day);
FRESH_QUERY_LINKS_CONVERTED="""
select count(*) from users u join retailerlinks rl on (rl.user_id=u.id) where date(rl.activated)=date(now()- interval 1 day) and date(rl.created)=date(now()- interval 1 day);
"""
FRESH_QUERY_LINKS_NOT_CONVERTED="""
select count(*) from retailerlinks where date(activated) is null and date(created)=date(now() - interval 1 day);
"""
FRESH_AGENTS_CALLED_COUNT="""
select distinct(agent_id) from callhistory where call_type='fresh' and date(created) = date(now()-interval 1 day);
"""

TOTAL_ACTIVATIONS="""
select count(1) from users where (lower(referrer)  not in('emp01','crm01','crm02','fos01','crm03','crm04') or lower(utm_campaign) not in('emp01','crm01','crm02','fos01','crm03','crm04')) and date(activation_time) = date(now()-interval 1 day)
"""

AGENT_FRESH_QUERY_LOGIN_TIME="""
select TIMEDIFF(logoutTime,loginTime) from agentlogintimings where role='fresh' and date(created)=date(now() - INTERVAL 1 DAY) and agent_id=%s;
"""
AGENT_FRESH_QUERY_DURATION="""
select sum(duration_sec) from callhistory where call_type ='fresh' and date(created)=date(now() - INTERVAL 1 DAY) and agent_id=%s;
"""
AGENT_FRESH_QUERY_AHT="""
select timestampdiff(second, callhistory.last_fetch_time, created) from callhistory where last_fetch_time is not null and call_type='fresh' and date(created)=date(now() - INTERVAL 1 DAY) and agent_id=%s;
"""
AGENT_FRESH_QUERY_AIT="""
select timestampdiff(second, last_action_time, created) from fetchdatahistory where call_type='fresh' and date(created)=date(now() - INTERVAL 1 DAY) and agent_id=%s;
"""
AGENT_FRESH_QUERY_LINKS_CONVERTED="""
select count(*) from users u join retailerlinks rl on (rl.user_id=u.id) where date(rl.activated)=date(now()- interval 1 day) and date(rl.created)=date(now()- interval 1 day) and agent_id=%s;
"""
AGENT_FRESH_QUERY_LINKS_NOT_CONVERTED="""
select count(*) from retailerlinks where date(activated) is null and date(created)=date(now() - interval 1 day) and agent_id=%s;
"""


FOLLOW_UP_QUERY="""
select call_disposition,count(1) from 
(select * from (select * from callhistory where date(created)=date(now() - INTERVAL 1 DAY) 
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() - INTERVAL 1 DAY)
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() - INTERVAL 1 DAY);
"""
FOLLOW_UP_QUERY_DURATION="""
select sum(duration_sec) from callhistory where call_type ='followup' and date(created)=date(now() - INTERVAL 1 DAY);
"""
FOLLOW_UP_QUERY_AHT="""
select timestampdiff(second, callhistory.last_fetch_time, created) from callhistory where last_fetch_time is not null and call_type='followup' and date(created)=date(now() - INTERVAL 1 DAY );
"""
FOLLOW_UP_QUERY_AIT="""
select timestampdiff(second, last_action_time, created) from fetchdatahistory where call_type='followup' and date(created)=date(now() - INTERVAL 1 DAY);
"""
FOLLOW_UP_AGENTS_CALLED_COUNT="""
select count(distinct(agent_id)) from callhistory where call_type='followup' and date(created) = date(now()-interval 1 day);
"""
AGENT_FOLLOW_UP_QUERY_LOGIN_TIME="""
select TIMEDIFF(logoutTime,loginTime) from agentlogintimings where role='followup' and date(created)=date(now() - INTERVAL 1 DAY) and agent_id=%s;
"""
AGENT_FOLLOW_UP_QUERY_DURATION="""
select sum(duration_sec) from callhistory where call_type ='followup' and date(created)=date(now() - INTERVAL 1 DAY) and agent_id=%s;
"""
AGENT_FOLLOW_UP_QUERY_AHT="""
select timestampdiff(second, callhistory.last_fetch_time, created) from callhistory where last_fetch_time is not null and call_type='followup' and date(created)=date(now() - INTERVAL 1 DAY) and agent_id=%s;
"""
AGENT_FOLLOW_UP_QUERY_AIT="""
select timestampdiff(second, last_action_time, created) from fetchdatahistory where call_type='followup' and date(created)=date(now() - INTERVAL 1 DAY)  and agent_id=%s;
"""


ONBOARDING_QUERY="""
select call_disposition,count(1) from 
(select * from (select * from callhistory where date(created)=date(now() - INTERVAL 1 DAY) 
and call_type ='onboarding' order by created desc) 
as a group by retailer_id) 
a1 group by date(created), call_disposition;
"""
ONBOARDING_QUERY_LOGIN_TIME="""
select agent_id,TIMEDIFF(logoutTime,loginTime) from agentlogintimings where role='onboarding' and date(created)=date(now() - INTERVAL 1 DAY);
"""
ONBOARDING_QUERY_DURATION="""
select sum(duration_sec) from callhistory where call_type ='onboarding' and date(created)=date(now() - INTERVAL 1 DAY);
"""
ONBOARDING_QUERY_AHT="""
select timestampdiff(second, callhistory.last_fetch_time, created) from callhistory where last_fetch_time is not null and call_type='onboarding' and date(created)=date(now() - INTERVAL 1 DAY );
"""
ONBOARDING_QUERY_AIT="""
select timestampdiff(second, last_action_time, created) from fetchdatahistory where call_type='onboarding' and date(created)=date(now() - INTERVAL 1 DAY);
"""
ONBOARDING_AGENTS_CALLED_COUNT="""
select count(distinct(agent_id)) from callhistory where call_type='onboarding' and date(created) = date(now()-interval 1 day);
"""

AGENT_ONBOARDING_QUERY="""
select call_disposition,count(1) from 
(select * from (select * from callhistory where date(created)=date(now() - INTERVAL 1 DAY)
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;
"""
AGENT_ONBOARDING_QUERY_LOGIN_TIME="""
select TIMEDIFF(logoutTime,loginTime) from agentlogintimings where role='onboarding' and date(created)=date(now() - INTERVAL 1 DAY) and agent_id=%s;
"""
AGENT_ONBOARDING_QUERY_DURATION="""
select sum(duration_sec) from callhistory where call_type ='onboarding' and date(created)=date(now() - INTERVAL 1 DAY) and agent_id=%s;
"""
AGENT_ONBOARDING_QUERY_AHT="""
select timestampdiff(second, callhistory.last_fetch_time, created) from callhistory where last_fetch_time is not null and call_type='onboarding' and date(created)=date(now() - INTERVAL 1 DAY ) and agent_id=%s;
"""
AGENT_ONBOARDING_QUERY_AIT="""
select timestampdiff(second, last_action_time, created) from fetchdatahistory where call_type='onboarding' and date(created)=date(now() - INTERVAL 1 DAY)  and agent_id=%s;
"""
AGENT_ONBOARDING_QUERY_AIT="""
select timestampdiff(second, last_action_time, created) from fetchdatahistory where call_type='onboarding' and date(created)=date(now() - INTERVAL 1 DAY) and agent_id=%s;
"""

center_alignment=xlwt.easyxf("align: horiz center")



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
    totalVerifiedLinkSent=0
    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)
    worksheet.write(22,column, 'Average Handling Time (In Hrs)', style)
    worksheet.write(23,column, 'Average Idle Time (In Hrs)', style)
    worksheet.write(24,column, 'Links Converted', style)
    worksheet.write(25,column, 'Link sent yet to be converted', style)
    worksheet.write(26,column, 'Total activations(Links Converted + Followup)', style)
    contactableData=0
    nonContactableData=0
    
    for r in result:
        if dispositionMap.get(r[0]) == 9:
            totalVerifiedLinkSent=int(r[1])
        row = dispositionMap.get(r[0])+1
        if dispositionMap.get(r[0]) == 1 or dispositionMap.get(r[0]) == 2 or dispositionMap.get(r[0]) == 3 or dispositionMap.get(r[0]) == 4:
            nonContactableData+=int(r[1])
        else:
            contactableData+=r[1] 
        currentList.append(str(row))
        column = 1
        worksheet.write(row, column, r[1],center_alignment)
        
    remainingList = list(set(freshList) - set(currentList))
    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(15,1,round((contactableData/float(totalDispositions))*100,2),center_alignment)
        worksheet.write(16,1,round((nonContactableData/float(totalDispositions))*100,2),center_alignment)
    
    conn.close()
    
    datesql=FRESH_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()
    
    if len(list(set(agentLoginList))) > 0:    
        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()
        
        cursor = conn.cursor()
        cursor.execute(datesql)
        result = cursor.fetchall()
        loginTime=0
        for r in result:
            loginTime+=r[0]
        averageLoginTime=loginTime/len(list(set(agentLoginList)))
        hours=averageLoginTime/3600
        minutesLeft=(averageLoginTime%3600)/60
        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()
        
    datesql=FRESH_QUERY_LINKS_CONVERTED
    conn = getDbConnection()
    global Converted
    global AgentsLoggedIn
    AgentsLoggedIn=len(list(set(agentLoginList)))
    cursor = conn.cursor()
    cursor.execute(datesql)
    result = cursor.fetchall()
    loginTime=0
    for r in result:
        Converted=r[0]
        worksheet.write(24,1,r[0],center_alignment)
    worksheet.write(25,1,totalVerifiedLinkSent-Converted,center_alignment)
    conn.close()
    
    datesql=TOTAL_ACTIVATIONS
    conn = getDbConnection()
    global TotalActivations
    cursor = conn.cursor()
    cursor.execute(datesql)
    result = cursor.fetchall()
    loginTime=0
    for r in result:
        TotalActivations=r[0]
        worksheet.write(26,1,r[0],center_alignment)    
    
    workbook.save(TMP_FILE)
    #sendmail(["manas.kapoor@shop2020.in"], "", TMP_FILE, SUBJECT)        


def generateAgentWiseFreshCallingReport():
    
    agentId=3
    rb = open_workbook(TMP_FILE,formatting_info=True)
    workbook = copy(rb)
    numberOfSheets=rb.nsheets
    sheet=rb.sheet_by_index(numberOfSheets-1)
    worksheet = workbook.get_sheet(numberOfSheets-1)
    totalVerifiedLinkSent=0
    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)
    worksheet.write(row+20,column-1, 'Handling Time (In Hrs)', style)
    worksheet.write(row+21,column-1, 'Idle Time (In Hrs)', style)
    worksheet.write(row+22,column-1, 'Links Converted', style)
    worksheet.write(row+23,column-1, 'Link sent yet to be converted', style)        
    columnId=agentId
    while True:
            if agentId >50 :
                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:
                        if dispositionMap.get(r[0]) == 9:
                            totalVerifiedLinkSent=int(r[1])
                        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],center_alignment)
                        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+16,columnId,round((nonContactableData/float(totalDialedOut))*100,2),center_alignment)
                        worksheet.write(25+17,columnId,totalDialedOut,center_alignment)
                    conn.close()
                
                    name_query=AGENT_NAME_QUERY %(agentId)
                    conn = getDbConnection()
                    column=agentId
                    cursor = conn.cursor()
                    cursor.execute(name_query)
                    result = cursor.fetchall()
                    for r in result:
                        worksheet.write(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),center_alignment)    
                    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)),center_alignment)    
                    conn.close()
                    
                    name_query=AGENT_FRESH_QUERY_AHT %(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+20,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)    
                    conn.close()
                    
                    name_query=AGENT_FRESH_QUERY_AIT %(agentId)
                    conn = getDbConnection()
                    column=agentId
                    cursor = conn.cursor()
                    cursor.execute(name_query)
                    result = cursor.fetchall()
                    loginTime=0
                    for r in result:
                        if r[0] is not None:
                            loginTime+=r[0]
                    hours=loginTime/3600
                    minutesLeft=(loginTime%3600)/60
                    worksheet.write(25+21,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)    
                    conn.close()

                    datesql=AGENT_FRESH_QUERY_LINKS_CONVERTED%(agentId)
                    conn = getDbConnection()
                    
                    cursor = conn.cursor()
                    cursor.execute(datesql)
                    result = cursor.fetchall()
                    loginTime=0
                    for r in result:
                        #worksheet.write(25+22,columnId,r[0],center_alignment)
                        converted=r[0]
                        worksheet.write(25+22,columnId,r[0],center_alignment)
                    
                    if totalVerifiedLinkSent==0:
                        worksheet.write(25+23,columnId,0,center_alignment)
                    elif totalVerifiedLinkSent<converted:
                        worksheet.write(25+23,columnId,converted-totalVerifiedLinkSent,center_alignment)    
                    else:
                        worksheet.write(25+23,columnId,totalVerifiedLinkSent-converted,center_alignment)    
                    conn.close()
#                     datesql=AGENT_FRESH_QUERY_LINKS_NOT_CONVERTED%(agentId)
#                     conn = getDbConnection()
#                     
#                     cursor = conn.cursor()
#                     cursor.execute(datesql)
#                     result = cursor.fetchall()
#                     loginTime=0
#                     for r in result:
#                         worksheet.write(25+23,columnId,r[0],center_alignment)    
                    
                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, 'Accessory Retailer', newStyle)
    worksheet.write(9,column, 'Service Center Retailer', newStyle)
    worksheet.write(10,column, 'Not Retailer', boldStyle)   
    worksheet.write(11,column, 'Recharge Retailer', boldStyle)
    worksheet.write(12,column, 'Contactable Data (%)', newStyle)
    worksheet.write(13,column, 'Non Contactable Data (%)', newStyle)
    worksheet.write(14,column, 'Agents Logged In', style)
    worksheet.write(15,column, 'Average Login Time (In Hrs)', style)
    worksheet.write(16,column, 'Total Dialed Out', style)
    worksheet.write(17,column, 'Average Dialed Out per agent', style)
    worksheet.write(18,column, 'Average Call Duration (In Hrs)', style)
    worksheet.write(19,column, 'Average Handling Time (In Hrs)', style)
    worksheet.write(20,column, 'Average Idle Time (In Hrs)', style)
    
    contactableData=0
    nonContactableData=0
    totalDispositions=0
    
    for r in result:
        row = followUpDispositionMap.get(r[0])+1
        if followUpDispositionMap.get(r[0]) == 1 or followUpDispositionMap.get(r[0]) == 2 or followUpDispositionMap.get(r[0]) == 3:
            nonContactableData+=int(r[1])
        else:
            contactableData+=r[1] 
        currentList.append(str(row))
        column = 1
        worksheet.write(row, column, r[1],center_alignment)
    
    remainingList = list(set(followUpList) - set(currentList))
    
    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(13,1,round((nonContactableData/float(totalDispositions))*100,2),center_alignment)
        
    
    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]))
    if len(list(set(agentLoginList))) > 0:
        averageLoginTime=loginTime/len(list(set(agentLoginList)))
        hours=averageLoginTime/3600
        minutesLeft=(averageLoginTime%3600)/60
        worksheet.write(14,1,len(list(set(agentLoginList))),center_alignment)
        worksheet.write(15,1,str(hours) + ':'+ str(minutesLeft),center_alignment)   
        worksheet.write(16,1,totalDispositions,center_alignment)
        worksheet.write(17,1,(contactableData+nonContactableData)/len(list(set(agentLoginList))),center_alignment)
        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(18,1,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
        conn.close()
        
        datesql=FOLLOW_UP_QUERY_AHT
        conn = getDbConnection()
        cursor = conn.cursor()
        cursor.execute(datesql)
        result = cursor.fetchall()
        loginTime=0
        for r in result:
            loginTime+=r[0]
        averageLoginTime=loginTime/len(list(set(agentLoginList)))
        hours=averageLoginTime/3600
        minutesLeft=(averageLoginTime%3600)/60
        worksheet.write(19,1,len(list(set(agentLoginList))),center_alignment)
        conn.close()
        
        datesql=FOLLOW_UP_QUERY_AIT
        conn = getDbConnection()
        cursor = conn.cursor()
        cursor.execute(datesql)
        result = cursor.fetchall()
        loginTime=0
        for r in result:
            loginTime+=r[0]
        averageLoginTime=loginTime/len(list(set(agentLoginList)))
        hours=averageLoginTime/3600
        minutesLeft=(averageLoginTime%3600)/60
        worksheet.write(20,1,len(list(set(agentLoginList))),center_alignment)
    workbook.save(TMP_FILE)  

def generateAgentWiseFollowupCallingReport():
    
    agentId=3
    rb = open_workbook(TMP_FILE,formatting_info=True)
    workbook = copy(rb)
    numberOfSheets=rb.nsheets
    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, 'Accessory Retailer', newStyle)
    worksheet.write(row+9,column-1, 'Service Center Retailer', newStyle)
    worksheet.write(row+10,column-1, 'Not retailer', style)
    worksheet.write(row+11,column-1, 'Recharge Retailer', style)
    worksheet.write(row+12,column-1, 'Contactable Data (%)', newStyle)
    worksheet.write(row+13,column-1, 'Non Contactable Data (%)', newStyle)
    worksheet.write(row+14,column-1, 'Total Dialed Out', style)
    worksheet.write(row+15,column-1, 'Login Time (In Hrs)', style)
    worksheet.write(row+16,column-1, 'Call Duration (In Hrs)', style)
    worksheet.write(row+17,column-1, 'Handling Time (In Hrs)', style)
    worksheet.write(row+18,column-1, 'Idle Time (In Hrs)', style)
    columnId=agentId
    while True:
            if agentId >50 :
                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:
                    
                    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 :
                            nonContactableData+=int(r[1])
                        else:
                            contactableData+=r[1] 
                        currentList.append(str(followUpDispositionMap.get(r[0])+1))
                        column = agentId
                        worksheet.write(row, columnId, r[1],center_alignment)
                    remainingList = list(set(followUpList) - set(currentList))
                    
                    for i,val in enumerate(remainingList):
                        row = int(val)+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+13,columnId,round((nonContactableData/float(totalDialedOut))*100,2),center_alignment)
                        worksheet.write(25+14,columnId,totalDialedOut,center_alignment)
                    conn.close()
                
                    name_query=AGENT_NAME_QUERY %(agentId)
                    conn = getDbConnection()
                    column=agentId
                    cursor = conn.cursor()
                    cursor.execute(name_query)
                    result = cursor.fetchall()
                    for r in result:
                        worksheet.write(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+15,columnId,str(hours)+':'+str(minutesLeft),center_alignment)    
                    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+16,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)    
                    conn.close()
                    
                    name_query=AGENT_FOLLOW_UP_QUERY_AHT %(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+17,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)    
                    conn.close()
                    
                    name_query=AGENT_FOLLOW_UP_QUERY_AIT %(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+18,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)    
                    conn.close()
                    
                agentId+=1
                columnId+=1
    workbook.save(TMP_FILE)    
    
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)
    worksheet.write(13,column, 'Average Handling Time (In Hrs)', style)
    worksheet.write(14,column, 'Average Idle Time (In Hrs)', style)
    if len(result)==0:
        print 'No Data'
        pass
    else:
        
        
        contactableData=0
        nonContactableData=0
        totalDispositions=0
        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],center_alignment)
        
        remainingList = list(set(onBoardingList) - set(currentList))
        
        for i,val in enumerate(remainingList):
            row = int(val)
            column = 1
            worksheet.write(row, column, 0,center_alignment)
        totalDispositions=contactableData+nonContactableData
        worksheet.write(6,1,round((contactableData/float(totalDispositions))*100,2),center_alignment)
        worksheet.write(7,1,round((nonContactableData/float(totalDispositions))*100,2),center_alignment)
        
        
        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))),center_alignment)
        worksheet.write(9,1,str(hours) + ':'+ str(minutesLeft),center_alignment)   
        worksheet.write(10,1,totalDispositions,center_alignment)
        worksheet.write(11,1,(contactableData+nonContactableData)/len(list(set(agentLoginList))),center_alignment)
        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)),center_alignment)
        
        conn.close()
        datesql=ONBOARDING_QUERY_AHT
        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(13,1,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
        conn.close()
        datesql=ONBOARDING_QUERY_AIT
        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(14,1,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)
    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
    
    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)
    worksheet.write(row+11,column-1, 'Handling Time (In Hrs)', style)
    worksheet.write(row+12,column-1, 'Idle Time (In Hrs)', style)
    columnId=agentId
    while True:
            if agentId >50 :
                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:
                    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],center_alignment)
                    remainingList = list(set(onBoardingList) - set(currentList))
                    
                    for i,val in enumerate(remainingList):
                        row = int(val)+25
                        column = agentId
                        worksheet.write(row, columnId, 0,center_alignment)
                    totalDialedOut = contactableData+nonContactableData
                    worksheet.write(25+6,columnId,round((contactableData/float(totalDialedOut))*100,2),center_alignment)
                    worksheet.write(25+7,columnId,round((nonContactableData/float(totalDialedOut))*100,2),center_alignment)
                    worksheet.write(25+8,columnId,totalDialedOut,center_alignment)
                    conn.close()
                
                    name_query=AGENT_NAME_QUERY %(agentId)
                    conn = getDbConnection()
                    column=agentId
                    cursor = conn.cursor()
                    cursor.execute(name_query)
                    result = cursor.fetchall()
                    for r in result:
                        worksheet.write(25,columnId,r,style)
                    conn.close()
                
                
                    name_query=AGENT_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),center_alignment)    
                    conn.close()
                    
                    name_query=AGENT_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)),center_alignment)    
                    conn.close()
                    
                    name_query=AGENT_ONBOARDING_QUERY_AHT %(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+11,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)    
                    conn.close()
                    
                    name_query=AGENT_ONBOARDING_QUERY_AIT %(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+12,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)    
                    conn.close()
                    
                agentId+=1
                columnId+=1
    workbook.save(TMP_FILE)          


        
def main():
    generateFreshCallingReport()
    generateAgentWiseFreshCallingReport()
    generateFollowUpCallingReport()
    generateAgentWiseFollowupCallingReport()
    generateOnBoardingCallingReport()
    generateAgentWiseOnboardingCallingReport()
    sendmail(["amit.sirohi@shop2020.in","rajneesh.arora@saholic.com","ritesh.chauhan@shop2020.in", "shailesh.kumar@shop2020.in","utkarsh@coreoutsourcingservices.com","gupta.varun@coreoutsourcingservices.com","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)
    mailServer.ehlo()
    mailServer.starttls()
    mailServer.ehlo()
    
    # Create the container (outer) email message.
    msg = MIMEMultipart()
    msg['Subject'] = title
    msg.preamble = title
    message="Total Activations(Fresh + Followup) :" + str(TotalActivations)
    message1="Links Converted(Fresh) :" + str(Converted)
    message2="Total Agents Logged In :" + str(AgentsLoggedIn)
    html_msg = MIMEText(message+"<br>"+message1+"<br>"+message2, '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)
    
    MAILTO = ['amit.sirohi@shop2020.in','rajneesh.arora@saholic.com', 'ritesh.chauhan@shop2020.in', 'shailesh.kumar@shop2020.in','gupta.varun@coreoutsourcingservices.com','utkarsh@coreoutsourcingservices.com','manas.kapoor@shop2020.in']
    #MAILTO = ['manas.kapoor@saholic.com']
    mailServer.login(SENDER, PASSWORD)
    try:
        mailServer.sendmail(SENDER, MAILTO, msg.as_string())
    except:
        m = Email('localhost')
        mFrom = "dtr@shop2020.in"
        m.setFrom(mFrom)
        for receipient in MAILTO:
            m.addRecipient(receipient)
        m.setSubject(title)
        m.setHtmlBody(message+"<br>"+message1+"<br>"+message2)
        m.send()

if __name__ == '__main__':
    main()