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 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
import optparse

DB_HOST = "localhost"
DB_USER = "root"
DB_PASSWORD = "shop2020"
DB_NAME = "dtr"
TMP_FILE = "/tmp/CRM_OutBound_Report.xls"
RET_FILE = "/tmp/CRM_Retention_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    

RET_SUBJECT = "CRM Retention Report for " + str(date.today() - timedelta(days=1))

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']
accsList=['1','2','3','4','5','6','7','8','9','10','11','12'] 
accsOrdersList=['1','2','3','4','5','6','7','8','9','10','11','12','13','14']  
inactiveUsersList=['1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23']
dispositionMap = {  'call_later':0,
                    'ringing_no_answer':1,
                    'not_reachable':2,
                    'switch_off':3,
                    'invalid_no':4,
                    '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,
                }

accsDispositionMap = {  'call_later':1,
                'ringing_no_answer':2,
                'not_reachable':3,
                'switch_off':4,
                'technical_issue':5,
                'pricing_issue':6,
                'shipping_issue':7,
                'internet_issue':8,
                'checking_price':9,
                'order_process':10,
                'placed_order':11,
                'place_order':12
              }


inactiveUsersDispositionMap = {  'call_later':1,
                'ringing_no_answer':2,
                'not_reachable':3,
                'switch_off':4,
                'app_uninstalled':5,
                'place_order':6,
                'pricing_issue':7,
                'technical_issue':8,
                'shipping_issue':9,
                'product_availability':10,
                'return_replacement':11,
                'product_quality_issue':12,
                'delayed_delivery':13,
                'order_diff_acc':14,
                'not_retailer':15,
                'business_closed':16,
                'invalid_no':17,
                'wrong_no':18,
                'hang_up':19,
                'service_center_retailer':20,
                'recharge_retailer':21,
                'other_complaint':22,
                'other_reason':23,
                'verified_link_sent':24
                
              }

accsDispositionOrdersMap = {  'call_later':1,
                'ringing_no_answer':2,
                'not_reachable':3,
                'switch_off':4,
                'technical_issue':5,
                'pricing_issue':6,
                'shipping_issue':7,
                'already_purchased':8,
                'product_availability':9,
                'return_replacement':10,
                'product_quality_issue':11,
                'delayed_delivery':12,
                'other_complaint':13,
                'not_dealing_accessories':14,
                'scheme_not_clear':15
              }
# 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")


ACCS_CART_QUERY="""
select call_disposition,count(1) from 
(select * from (select * from callhistorycrm where date(created)=date(now() - interval 1 day) 
and project_id=1 order by created desc) 
as a group by user_id) 
a1 group by date(created), call_disposition;
"""
ACCS_CART_AGENTS_CALLED_COUNT="""
select distinct(agent_id) from callhistorycrm where project_id=1 and date(created) = date(now() - interval 1 day)
"""
ACCS_CART_QUERY_LOGIN_TIME="""
select agent_id,TIMEDIFF(logoutTime,loginTime) from agentlogintimings where role='accs_cart' and date(created)=date(now() - interval 1 day);
"""
ACCS_CART_DISPOSITION_DESCRIPTION="""
select user_id,call_disposition,disposition_description,disposition_comments 
from callhistorycrm where call_disposition not in ('ringing_no_answer','not_reachable','switch_off','call_later') 
and date(created)=date(now() - interval 1 day) and project_id=1"""

ACCS_CART_PRODUCT_PRICING="""
select user_id,call_disposition,product_input,pricing_input from productpricinginputs where 
date(created)=date(now() - interval 1 day) and product_input not like '' and project_id=1;
"""

ACCS_TAB_QUERY="""
select call_disposition,count(1) from 
(select * from (select * from callhistorycrm where date(created)=date(now() - interval 1 day) 
and project_id=2 order by created desc) 
as a group by user_id) 
a1 group by date(created), call_disposition;
"""

ACCS_TAB_AGENTS_CALLED_COUNT="""
select distinct(agent_id) from callhistorycrm where project_id=2 and date(created) = date(now() - interval 1 day)
"""

ACCS_TAB_QUERY_LOGIN_TIME="""
select agent_id,TIMEDIFF(logoutTime,loginTime) from agentlogintimings where role='accs_active' and date(created)=date(now() - interval 1 day);
"""

ACCS_TAB_DISPOSITION_DESCRIPTION="""
select user_id,call_disposition,disposition_description,disposition_comments 
from callhistorycrm where call_disposition not in ('ringing_no_answer','not_reachable','switch_off','call_later') 
and date(created)=date(now() - interval 1 day) and project_id=2"""

ACCS_TAB_PRODUCT_PRICING="""
select user_id,call_disposition,product_input,pricing_input from productpricinginputs where 
date(created)=date(now() - interval 1 day) and product_input not like '' and project_id=2;
"""

ACCS_ORDER_QUERY="""
select call_disposition,count(1) from 
(select * from (select * from callhistorycrm where date(created)=date(now()- interval 1 day) 
and project_id=3 order by created desc) 
as a group by user_id) 
a1 group by date(created), call_disposition;
"""

ACCS_ORDER_AGENTS_CALLED_COUNT="""
select distinct(agent_id) from callhistorycrm where project_id=3 and date(created) = date(now() - interval 1 day)
"""

ACCS_ORDER_QUERY_LOGIN_TIME="""
select agent_id,TIMEDIFF(logoutTime,loginTime) from agentlogintimings where role='accs_order' and date(created)=date(now() - interval 1 day);
"""

ACCS_ORDER_DISPOSITION_DESCRIPTION="""
select user_id,call_disposition,disposition_description,disposition_comments 
from callhistorycrm where call_disposition not in ('ringing_no_answer','not_reachable','switch_off','call_later') 
and date(created)=date(now() - interval 1 day) and project_id=3"""

ACCS_ORDER_PRODUCT_PRICING="""
select user_id,call_disposition,product_input,pricing_input from productpricinginputs where 
date(created)=date(now() - interval 1 day) and product_input not like '' and project_id=3;
"""

ACCS_INACTIVE_USERS_QUERY="""
select call_disposition,count(1) from 
(select * from (select * from callhistorycrm where date(created)=date(now()- interval 1 day) 
and project_id=5 order by created desc) 
as a group by user_id) 
a1 group by date(created), call_disposition;
"""

ACCS_INACTIVE_USERS_AGENTS_CALLED_COUNT="""
select distinct(agent_id) from callhistorycrm where project_id=5 and date(created) = date(now() - interval 1 day)
"""

ACCS_INACTIVE_USERS_QUERY_LOGIN_TIME="""
select agent_id,TIMEDIFF(logoutTime,loginTime) from agentlogintimings where role='inactive_users' and date(created)=date(now() - interval 1 day);
"""

ACCS_INACTIVE_USERS_DISPOSITION_DESCRIPTION="""
select user_id,call_disposition,disposition_description,disposition_comments 
from callhistorycrm where call_disposition not in ('ringing_no_answer','not_reachable','switch_off','call_later') 
and date(created)=date(now() - interval 1 day) and project_id=5"""

ACCS_INACTIVE_USERS_PRODUCT_PRICING="""
select user_id,call_disposition,product_input,pricing_input from productpricinginputs where 
date(created)=date(now() - interval 1 day) and product_input not like '' and project_id=5;
"""

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

def generateAccessoriesCartReport():
    datesql=ACCS_CART_QUERY 
    conn = getDbConnection()
    
    cursor = conn.cursor()
    cursor.execute(datesql)
    result = cursor.fetchall()
    global newWorkbook
    newWorkbook = xlwt.Workbook()
    worksheet = newWorkbook.add_sheet("Accessories Cart")
    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, 'Technical Issue', newStyle)
    worksheet.write(6,column, 'Pricing Issue', newStyle)
    worksheet.write(7,column, 'Shipping Issue', newStyle)
    worksheet.write(8,column, 'Internet Issue', newStyle)
    worksheet.write(9,column, 'Checking Price', newStyle)   
    worksheet.write(10,column, 'Order Process', newStyle)
    worksheet.write(11,column, 'Placed Order', newStyle)
    worksheet.write(12,column, 'Will Place Order', boldStyle)
    worksheet.write(13,column, 'Contactable Data (%)', newStyle)
    worksheet.write(14,column, 'Non Contactable Data (%)', newStyle)
    worksheet.write(15,column, 'Agents Logged In', style)
    worksheet.write(16,column, 'Average Login Time (In Hrs)', style)
    worksheet.write(17,column, 'Total Dialed Out', style)
    worksheet.write(18,column, 'Average Dialed Out per agent', style)

    contactableData=0
    nonContactableData=0
    global ACCS_CART_WILL_PLACE_ORDER
    ACCS_CART_WILL_PLACE_ORDER=0
    for r in result:
        row = accsDispositionMap.get(r[0])
        if accsDispositionMap.get(r[0]) == 2 or accsDispositionMap.get(r[0]) == 3 or accsDispositionMap.get(r[0]) == 4:
            nonContactableData+=int(r[1])
        else:
            if accsDispositionMap.get(r[0])==12:
                ACCS_CART_WILL_PLACE_ORDER = r[1]
            contactableData+=r[1] 
        currentList.append(str(row))
        column = 1
        worksheet.write(row, column, r[1],center_alignment)
        
    remainingList = list(set(accsList) - set(currentList))
    for i,val in enumerate(remainingList):
        row = int(val)
        column = 1
        worksheet.write(row, column, 0,center_alignment)
    totalDispositions=contactableData+nonContactableData
    global ACCS_CART_TOTAL
    global ACCS_CART_SUCCESSFUL
    ACCS_CART_TOTAL = totalDispositions
    ACCS_CART_SUCCESSFUL=contactableData
    if totalDispositions > 0:
        worksheet.write(13,1,round((contactableData/float(totalDispositions))*100,2),center_alignment)
        worksheet.write(14,1,round((nonContactableData/float(totalDispositions))*100,2),center_alignment)
    
    conn.close()
    
    datesql=ACCS_CART_AGENTS_CALLED_COUNT 
    conn = getDbConnection()
    cursor = conn.cursor()
    cursor.execute(datesql)
    result = cursor.fetchall()
    agentLoginList=[]
    for r in result:
        agentLoginList.append(str(r[0]))
    conn.close()
    global ACCS_CART_LOGIN
    ACCS_CART_LOGIN = len(list(set(agentLoginList)))
    if len(list(set(agentLoginList))) > 0:    
        datesql=ACCS_CART_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(15,1,len(list(set(agentLoginList))),center_alignment)
        worksheet.write(16,1,str(hours) + ':'+ str(minutesLeft),center_alignment)   
        worksheet.write(17,1,totalDispositions,center_alignment)
        worksheet.write(18,1,totalDispositions/float(len(list(set(agentLoginList)))),center_alignment)
        conn.close()
    
    
    worksheet.write(0,5, 'User Id', style)
    worksheet.write(0,6, 'Call Disposition', style)
    worksheet.write(0,7, 'Disposition Description', style)
    worksheet.write(0,8, 'Disposition Comments', style)    
    datesql = ACCS_CART_DISPOSITION_DESCRIPTION
    conn = getDbConnection()
    cursor = conn.cursor()
    cursor.execute(datesql)
    result = cursor.fetchall()
    row=0
    for r in result:
        row=row+1
        column =5
        for data in r:
            worksheet.write(row,column,data)
            column+=1
    conn.close()
    
    worksheet.write(20,0, 'User Id', style)
    worksheet.write(20,1, 'Call Disposition', style)
    worksheet.write(20,2, 'Product Inputs', style)
    worksheet.write(20,3, 'Pricing Inputs', style)    
    datesql = ACCS_CART_PRODUCT_PRICING
    conn = getDbConnection()
    cursor = conn.cursor()
    cursor.execute(datesql)
    result = cursor.fetchall()
    row=20
    for r in result:
        row=row+1
        column =0
        for data in r:
            worksheet.write(row,column,data)
            column+=1
        
    newWorkbook.save(RET_FILE)
    
def generateInactiveUsersReport():
    datesql=ACCS_INACTIVE_USERS_QUERY 
    conn = getDbConnection()
    
    cursor = conn.cursor()
    cursor.execute(datesql)
    result = cursor.fetchall()
    global newWorkbook
    newWorkbook = xlwt.Workbook()
    worksheet = newWorkbook.add_sheet("Inactive Users")
    boldStyle = xlwt.XFStyle()
    newStyle= xlwt.XFStyle()
    style = xlwt.XFStyle()
    pattern = xlwt.Pattern()
    pattern.pattern = xlwt.Pattern.SOLID_PATTERN
    pattern.pattern_fore_colour = xlwt.Style.colour_map['yellow']
    style.pattern = pattern
    f = xlwt.Font()
    f.bold = True
    boldStyle.font = f
    
    column = 0
    row = 0
    currentList=[]
 
    worksheet.write(0,0,'Call Disposition Type',style)
    worksheet.write(0,1,'Count',style)
    worksheet.write(1, column, 'Call Later', newStyle)
    worksheet.write(2,column, 'Ringing No Answer', newStyle)
    worksheet.write(3,column, 'Not Reachable', newStyle)
    worksheet.write(4,column, 'Switched Off', newStyle)
    worksheet.write(5,column, 'App Uninstalled', newStyle)
    worksheet.write(6,column, 'Will Place Order', boldStyle)
    worksheet.write(7,column, 'Pricing Issue(High)', newStyle)
    worksheet.write(8,column, 'Technical Issue', newStyle)
    worksheet.write(9,column, 'Shipping Issue', newStyle)   
    worksheet.write(10,column, 'Product Not Available', newStyle)
    worksheet.write(11,column, 'Return Replacement Pending', newStyle)
    worksheet.write(12,column, 'Product Quality Issue', newStyle)
    worksheet.write(13, column, 'Delayed Delivery', newStyle)
    worksheet.write(14,column, 'Placing Order from Different Account', newStyle)
    worksheet.write(15,column, 'Not a Retailer', newStyle)
    worksheet.write(16,column, 'Business Closed', newStyle)
    worksheet.write(17,column, 'Invalid No', newStyle)
    worksheet.write(18,column, 'Wrong No', newStyle)
    worksheet.write(19,column, 'Hang up', newStyle)
    worksheet.write(20,column, 'Service Center', newStyle)
    worksheet.write(21,column, 'Recharge Retailer', newStyle)   
    worksheet.write(22,column, 'Other Complaint', newStyle)
    worksheet.write(23,column, 'Other Reasons', newStyle)
    worksheet.write(24,column, 'App Link Sent', newStyle)
    worksheet.write(25,column, 'Contactable Data (%)', newStyle)
    worksheet.write(26,column, 'Non Contactable Data (%)', newStyle)
    worksheet.write(27,column, 'Agents Logged In', style)
    worksheet.write(28,column, 'Average Login Time (In Hrs)', style)
    worksheet.write(29,column, 'Total Dialed Out', style)
    worksheet.write(30,column, 'Average Dialed Out per agent', style)

    contactableData=0
    nonContactableData=0
    global INACTIVE_USERS_WILL_PLACE_ORDER
    INACTIVE_USERS_WILL_PLACE_ORDER=0
    for r in result:
        row = inactiveUsersDispositionMap.get(r[0])
        if inactiveUsersDispositionMap.get(r[0]) == 2 or inactiveUsersDispositionMap.get(r[0]) == 3 or inactiveUsersDispositionMap.get(r[0]) == 4 or inactiveUsersDispositionMap.get(r[0]) == 17 or inactiveUsersDispositionMap.get(r[0]) == 18 or inactiveUsersDispositionMap.get(r[0]) == 19:
            nonContactableData+=int(r[1])
        else:
            if inactiveUsersDispositionMap.get(r[0])== 6:
                INACTIVE_USERS_WILL_PLACE_ORDER = r[1]
            contactableData+=r[1] 
        currentList.append(str(row))
        column = 1
        worksheet.write(row, column, r[1],center_alignment)
        
    remainingList = list(set(inactiveUsersList) - set(currentList))
    for i,val in enumerate(remainingList):
        row = int(val)
        column = 1
        worksheet.write(row, column, 0,center_alignment)
    totalDispositions=contactableData+nonContactableData
    global INACTIVE_USERS_TOTAL
    global INACTIVE_USERS_SUCCESSFUL
    INACTIVE_USERS_TOTAL = totalDispositions
    INACTIVE_USERS_SUCCESSFUL=contactableData
    if totalDispositions > 0:
        worksheet.write(25,1,round((contactableData/float(totalDispositions))*100,2),center_alignment)
        worksheet.write(26,1,round((nonContactableData/float(totalDispositions))*100,2),center_alignment)
    
    conn.close()
    
    datesql=ACCS_INACTIVE_USERS_AGENTS_CALLED_COUNT 
    conn = getDbConnection()
    cursor = conn.cursor()
    cursor.execute(datesql)
    result = cursor.fetchall()
    agentLoginList=[]
    for r in result:
        agentLoginList.append(str(r[0]))
    conn.close()
    global INACTIVE_USERS_LOGIN
    INACTIVE_USERS_LOGIN = len(list(set(agentLoginList)))
    if len(list(set(agentLoginList))) > 0:    
        datesql=ACCS_INACTIVE_USERS_QUERY_LOGIN_TIME
        conn = getDbConnection()
        cursor = conn.cursor()
        cursor.execute(datesql)
        result = cursor.fetchall()
        loginTime=0
        for r in result:
            loginTime+=r[1].seconds
        averageLoginTime=loginTime/len(list(set(agentLoginList)))
        hours=averageLoginTime/3600
        minutesLeft=(averageLoginTime%3600)/60
        worksheet.write(27,1,len(list(set(agentLoginList))),center_alignment)
        worksheet.write(28,1,str(hours) + ':'+ str(minutesLeft),center_alignment)   
        worksheet.write(29,1,totalDispositions,center_alignment)
        worksheet.write(30,1,totalDispositions/float(len(list(set(agentLoginList)))),center_alignment)
        conn.close()
    
    
    worksheet.write(0,5, 'User Id', style)
    worksheet.write(0,6, 'Call Disposition', style)
    worksheet.write(0,7, 'Disposition Description', style)
    worksheet.write(0,8, 'Disposition Comments', style)    
    datesql = ACCS_INACTIVE_USERS_DISPOSITION_DESCRIPTION
    conn = getDbConnection()
    cursor = conn.cursor()
    cursor.execute(datesql)
    result = cursor.fetchall()
    row=0
    for r in result:
        row=row+1
        column =5
        for data in r:
            worksheet.write(row,column,data)
            column+=1
    conn.close()
    
    worksheet.write(32,0, 'User Id', style)
    worksheet.write(32,1, 'Call Disposition', style)
    worksheet.write(32,2, 'Product Inputs', style)
    worksheet.write(32,3, 'Pricing Inputs', style)    
    datesql = ACCS_INACTIVE_USERS_PRODUCT_PRICING
    conn = getDbConnection()
    cursor = conn.cursor()
    cursor.execute(datesql)
    result = cursor.fetchall()
    row=20
    for r in result:
        row=row+1
        column =0
        for data in r:
            worksheet.write(row,column,data)
            column+=1
        
    newWorkbook.save(RET_FILE)
    
def generateAccessoriesTabsReport():
    datesql=ACCS_TAB_QUERY 
    conn = getDbConnection()
    
    cursor = conn.cursor()
    cursor.execute(datesql)
    result = cursor.fetchall()
    rb = open_workbook(RET_FILE,formatting_info=True)
    newWorkbook = copy(rb)
    
    worksheet = newWorkbook.add_sheet("Accessories Tab")
    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, 'Technical Issue', newStyle)
    worksheet.write(6,column, 'Pricing Issue', newStyle)
    worksheet.write(7,column, 'Shipping Issue', newStyle)
    worksheet.write(8,column, 'Internet Issue', newStyle)
    worksheet.write(9,column, 'Checking Price', newStyle)   
    worksheet.write(10,column, 'Order Process', newStyle)
    worksheet.write(11,column, 'Placed Order', newStyle)
    worksheet.write(12,column, 'Will Place Order', boldStyle)
    worksheet.write(13,column, 'Contactable Data (%)', newStyle)
    worksheet.write(14,column, 'Non Contactable Data (%)', newStyle)
    worksheet.write(15,column, 'Agents Logged In', style)
    worksheet.write(16,column, 'Average Login Time (In Hrs)', style)
    worksheet.write(17,column, 'Total Dialed Out', style)
    worksheet.write(18,column, 'Average Dialed Out per agent', style)

    contactableData=0
    nonContactableData=0
    global ACCS_TAB_WILL_PLACE_ORDER
    ACCS_TAB_WILL_PLACE_ORDER=0
    for r in result:
        row = accsDispositionMap.get(r[0])
        if accsDispositionMap.get(r[0]) == 2 or accsDispositionMap.get(r[0]) == 3 or accsDispositionMap.get(r[0]) == 4:
            nonContactableData+=int(r[1])
        else:
            if accsDispositionMap.get(r[0])==12:
                ACCS_TAB_WILL_PLACE_ORDER = r[1]
            contactableData+=r[1] 
        currentList.append(str(row))
        column = 1
        worksheet.write(row, column, r[1],center_alignment)
        
    remainingList = list(set(accsList) - set(currentList))
    for i,val in enumerate(remainingList):
        row = int(val)
        column = 1
        worksheet.write(row, column, 0,center_alignment)
    totalDispositions=contactableData+nonContactableData
    global ACCS_TAB_TOTAL
    global ACCS_TAB_SUCCESSFUL
    ACCS_TAB_TOTAL = totalDispositions
    ACCS_TAB_SUCCESSFUL=contactableData
    if totalDispositions > 0:
        worksheet.write(13,1,round((contactableData/float(totalDispositions))*100,2),center_alignment)
        worksheet.write(14,1,round((nonContactableData/float(totalDispositions))*100,2),center_alignment)
    
    conn.close()
    
    datesql=ACCS_TAB_AGENTS_CALLED_COUNT 
    conn = getDbConnection()
    cursor = conn.cursor()
    cursor.execute(datesql)
    result = cursor.fetchall()
    agentLoginList=[]
    for r in result:
        agentLoginList.append(str(r[0]))
    conn.close()
    global ACCS_TAB_LOGIN
    ACCS_TAB_LOGIN = len(list(set(agentLoginList)))
    if len(list(set(agentLoginList))) > 0:    
        datesql=ACCS_TAB_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(15,1,len(list(set(agentLoginList))),center_alignment)
        worksheet.write(16,1,str(hours) + ':'+ str(minutesLeft),center_alignment)   
        worksheet.write(17,1,totalDispositions,center_alignment)
        worksheet.write(18,1,totalDispositions/float(len(list(set(agentLoginList)))),center_alignment)
        conn.close()
    
    
    worksheet.write(0,5, 'User Id', style)
    worksheet.write(0,6, 'Call Disposition', style)
    worksheet.write(0,7, 'Disposition Description', style)
    worksheet.write(0,8, 'Disposition Comments', style)    
    datesql = ACCS_TAB_DISPOSITION_DESCRIPTION
    conn = getDbConnection()
    cursor = conn.cursor()
    cursor.execute(datesql)
    result = cursor.fetchall()
    row=0
    for r in result:
        row=row+1
        column =5
        for data in r:
            worksheet.write(row,column,data)
            column+=1
    conn.close()
    
    worksheet.write(20,0, 'User Id', style)
    worksheet.write(20,1, 'Call Disposition', style)
    worksheet.write(20,2, 'Product Inputs', style)
    worksheet.write(20,3, 'Pricing Inputs', style)    
    datesql = ACCS_TAB_PRODUCT_PRICING
    conn = getDbConnection()
    cursor = conn.cursor()
    cursor.execute(datesql)
    result = cursor.fetchall()
    row=20
    for r in result:
        row=row+1
        column =0
        for data in r:
            worksheet.write(row,column,data)
            column+=1
        
    newWorkbook.save(RET_FILE)

def generateAccessoriesOrderReport():
    datesql=ACCS_ORDER_QUERY 
    conn = getDbConnection()
    
    cursor = conn.cursor()
    cursor.execute(datesql)
    result = cursor.fetchall()
    rb = open_workbook(RET_FILE,formatting_info=True)
    newWorkbook = copy(rb)
    
    worksheet = newWorkbook.add_sheet("Accessories Order")
    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, 'Technical Issue', newStyle)
    worksheet.write(6,column, 'Pricing Issue', newStyle)
    worksheet.write(7,column, 'Shipping Issue', newStyle)
    worksheet.write(8,column, 'Already purchased required stock', newStyle)
    worksheet.write(9,column, 'Product Not available', newStyle)   
    worksheet.write(10,column, 'Return or replacement pending', newStyle)
    worksheet.write(11,column, 'Product Quality issue', newStyle)
    worksheet.write(12,column, 'Delayed delivery', newStyle)
    worksheet.write(13,column, 'Other complaint with Profitmandi', newStyle)
    worksheet.write(14,column, 'Not Dealing in Accessories/Not Interested', newStyle)
    worksheet.write(15,column, 'Scheme Not Clear to the user', newStyle)
    worksheet.write(16,column, 'Contactable Data (%)', newStyle)
    worksheet.write(17,column, 'Non Contactable Data (%)', newStyle)
    worksheet.write(18,column, 'Agents Logged In', style)
    worksheet.write(19,column, 'Average Login Time (In Hrs)', style)
    worksheet.write(20,column, 'Total Dialed Out', style)
    worksheet.write(21,column, 'Average Dialed Out per agent', style)
 
    contactableData=0
    nonContactableData=0
    for r in result:
        row = accsDispositionOrdersMap.get(r[0])
        if accsDispositionOrdersMap.get(r[0]) == 2 or accsDispositionOrdersMap.get(r[0]) == 3 or accsDispositionOrdersMap.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(accsOrdersList) - set(currentList))
    for i,val in enumerate(remainingList):
        row = int(val)
        column = 1
        worksheet.write(row, column, 0,center_alignment)
    totalDispositions=contactableData+nonContactableData
    global ACCS_ORDER_TOTAL
    global ACCS_ORDER_SUCCESSFUL
    ACCS_ORDER_TOTAL = totalDispositions
    ACCS_ORDER_SUCCESSFUL=contactableData
    if totalDispositions > 0:
        worksheet.write(16,1,round((contactableData/float(totalDispositions))*100,2),center_alignment)
        worksheet.write(17,1,round((nonContactableData/float(totalDispositions))*100,2),center_alignment)
    
    conn.close()
    
    datesql=ACCS_ORDER_AGENTS_CALLED_COUNT 
    conn = getDbConnection()
    cursor = conn.cursor()
    cursor.execute(datesql)
    result = cursor.fetchall()
    agentLoginList=[]
    for r in result:
        agentLoginList.append(str(r[0]))
    conn.close()
    global ACCS_ORDER_LOGIN
    ACCS_ORDER_LOGIN = len(list(set(agentLoginList)))
    if len(list(set(agentLoginList))) > 0:    
        datesql=ACCS_ORDER_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(18,1,len(list(set(agentLoginList))),center_alignment)
        worksheet.write(19,1,str(hours) + ':'+ str(minutesLeft),center_alignment)   
        worksheet.write(20,1,totalDispositions,center_alignment)
        worksheet.write(21,1,totalDispositions/float(len(list(set(agentLoginList)))),center_alignment)
        conn.close()
    
    
    worksheet.write(0,5, 'User Id', style)
    worksheet.write(0,6, 'Call Disposition', style)
    worksheet.write(0,7, 'Disposition Description', style)
    worksheet.write(0,8, 'Disposition Comments', style)
    datesql = ACCS_ORDER_DISPOSITION_DESCRIPTION
    conn = getDbConnection()
    cursor = conn.cursor()
    cursor.execute(datesql)
    result = cursor.fetchall()
    row=0
    for r in result:
        row=row+1
        column =5
        for data in r:
            worksheet.write(row,column,data)
            column+=1
    conn.close()
    
    worksheet.write(23,0, 'User Id', style)
    worksheet.write(23,1, 'Call Disposition', style)
    worksheet.write(23,2, 'Product Inputs', style)
    worksheet.write(23,3, 'Pricing Inputs', style)    
    datesql = ACCS_ORDER_PRODUCT_PRICING
    conn = getDbConnection()
    cursor = conn.cursor()
    cursor.execute(datesql)
    result = cursor.fetchall()
    row=23
    for r in result:
        row=row+1
        column =0
        for data in r:
            worksheet.write(row,column,data)
            column+=1
    conn.close()
    newWorkbook.save(RET_FILE)

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(["rajender.singh@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 = 0
    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])+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(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)+1
                            column = agentId
                            worksheet.write(row, columnId, 0,center_alignment)
                    totalDialedOut = contactableData+nonContactableData
                    if totalDialedOut > 0:
                        worksheet.write(15,columnId,round((contactableData/float(totalDialedOut))*100,2),center_alignment)
                        worksheet.write(16,columnId,round((nonContactableData/float(totalDialedOut))*100,2),center_alignment)
                        worksheet.write(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(0,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(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(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(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(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:
                        converted=r[0]
                        worksheet.write(22,columnId,r[0],center_alignment)
                    
                    if totalVerifiedLinkSent==0:
                        worksheet.write(23,columnId,0,center_alignment)
                    elif totalVerifiedLinkSent<converted:
                        worksheet.write(23,columnId,converted-totalVerifiedLinkSent,center_alignment)    
                    else:
                        worksheet.write(23,columnId,totalVerifiedLinkSent-converted,center_alignment)    
                    conn.close()
                agentId+=1
                columnId+=1
    workbook.save(TMP_FILE)    

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:
            if r[0] is not None:
                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 = 0
    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])+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(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)+0
                        column = agentId
                        worksheet.write(row, columnId, 0,center_alignment)
                    totalDialedOut = contactableData+nonContactableData
                    if totalDialedOut>0:
                        worksheet.write(12,columnId,round((contactableData/float(totalDialedOut))*100,2),center_alignment)
                        worksheet.write(13,columnId,round((nonContactableData/float(totalDialedOut))*100,2),center_alignment)
                        worksheet.write(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(0,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(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(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(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:
                        if r[0] is not None:
                            loginTime+=r[0]
                    hours=loginTime/3600
                    minutesLeft=(loginTime%3600)/60
                    worksheet.write(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(["rajender.singh@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 = 0
    
    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])+1
                        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)+0
                        column = agentId
                        worksheet.write(row, columnId, 0,center_alignment)
                    totalDialedOut = contactableData+nonContactableData
                    worksheet.write(6,columnId,round((contactableData/float(totalDialedOut))*100,2),center_alignment)
                    worksheet.write(7,columnId,round((nonContactableData/float(totalDialedOut))*100,2),center_alignment)
                    worksheet.write(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(0,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(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(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(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(12,columnId,str(int(hours))+':'+str(int(minutesLeft)),center_alignment)    
                    conn.close()
                    
                agentId+=1
                columnId+=1
    workbook.save(TMP_FILE)          
        
def main():
    parser = optparse.OptionParser()
    parser.add_option("-T", "--reporttype", dest="reporttype",
                      default="crmoutbound",
                      type="str", help="To avoid not needed order backups",
                      metavar="REPORTTYPE")
    (options, args) = parser.parse_args()
    if options.reporttype == 'crmoutbound':
        generateFreshCallingReport()
        generateAgentWiseFreshCallingReport()
        generateFollowUpCallingReport()
        generateAgentWiseFollowupCallingReport()
        generateOnBoardingCallingReport()
        generateAgentWiseOnboardingCallingReport()
#         sendmail(["rajender.singh@shop2020.in"], "", TMP_FILE, SUBJECT)
        sendmail(["rajneesh.arora@saholic.com","khushal.bhatia@saholic.com","ritesh.chauhan@shop2020.in", "shailesh.kumar@shop2020.in", "amit.gupta@shop2020.in"], "", TMP_FILE, SUBJECT)
    if options.reporttype == 'retention':
#         generateAccessoriesCartReport()
#         generateAccessoriesTabsReport()
#         generateAccessoriesOrderReport()
        generateInactiveUsersReport()
#         sendmailretention(["rajender.singh@shop2020.in"], "", RET_FILE, RET_SUBJECT)
        sendmailretention(["rajneesh.arora@saholic.com", "khushal.bhatia@saholic.com", "ritesh.chauhan@shop2020.in", "shailesh.kumar@shop2020.in","amit.gupta@shop2020.in"], "", RET_FILE, RET_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', 'khushal.bhatia@saholic.com', 'shailesh.kumar@shop2020.in','gupta.varun@coreoutsourcingservices.com','utkarsh@coreoutsourcingservices.com','rajender.singh@shop2020.in']
#     MAILTO = ['rajender.singh@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()

def getAccsCartHtml():
    heading = "Accessories Cart Users(Project 1)"
    message="No. of Agents " + str(ACCS_CART_LOGIN)
    message1="Total Calls attempted " + str(ACCS_CART_TOTAL)
    message2="Total Successful calls " + str(ACCS_CART_SUCCESSFUL)
    message3="Will Order "+ str(ACCS_CART_WILL_PLACE_ORDER)
    if ACCS_CART_TOTAL!=0:
        message4="Will Order % " +str(round((ACCS_CART_WILL_PLACE_ORDER/float(ACCS_CART_TOTAL))*100,2))
    else: 
        message4="Will Order % 0"      
    return "<u>" + heading + "</u><br>" +message+"<br>"+message1+"<br>"+message2+"<br>"+message3+"<br>"+message4+"<br>"

def getInactiveUsersHtml():
    heading = "Inactive Users(Project 5)"
    message="No. of Agents " + str(INACTIVE_USERS_LOGIN)
    message1="Total Calls attempted " + str(INACTIVE_USERS_TOTAL)
    message2="Total Successful calls " + str(INACTIVE_USERS_SUCCESSFUL)
    message3="Will Order "+ str(INACTIVE_USERS_WILL_PLACE_ORDER)
    if INACTIVE_USERS_TOTAL!=0:
        message4="Will Order % " +str(round((INACTIVE_USERS_WILL_PLACE_ORDER/float(INACTIVE_USERS_TOTAL))*100,2))
    else: 
        message4="Will Order % 0"      
    return "<u>" + heading + "</u><br>" +message+"<br>"+message1+"<br>"+message2+"<br>"+message3+"<br>"+message4+"<br>"


def getAccsTabHtml():
    heading = "Accessories Active Users(Project 2)"
    message="No. of Agents " + str(ACCS_TAB_LOGIN)
    message1="Total Calls attempted " + str(ACCS_TAB_TOTAL)
    message2="Total Successful calls " + str(ACCS_TAB_SUCCESSFUL)
    message3="Will Order "+ str(ACCS_TAB_WILL_PLACE_ORDER)
    if ACCS_TAB_TOTAL!=0:
        message4="Will Order % " +str(round((ACCS_TAB_WILL_PLACE_ORDER/float(ACCS_TAB_TOTAL))*100,2))
    else:
        message4="Will Order % 0"     
    return "<br><br><u>" + heading + "</u><br>" +message+"<br>"+message1+"<br>"+message2+"<br>"+message3+"<br>"+message4+"<br>"

def getAccsCartOrders():
    datesql = "select  date(now() - interval 1 day), count(distinct user_id),count(distinct order_id), sum(quantity),sum(amount_paid) from allorder where store_id='spice' and category='Accs' and date(created_on)=date(now() - interval 1 day) and user_id in (select user_id from usercrmcallingdata where project_id=1 and date(created)>=date(now()- interval 7 day));"
    conn = getDbConnection()
    cursor = conn.cursor()
    cursor.execute(datesql)
    result = cursor.fetchall()
    inputs = "No of users who placed orders on Date " + str(result[0][0]) + "<br> Number of Users " + str(result[0][1])+ " <br> Total Orders " + str(result[0][2]) + "<br>Total Quantity " + str(result[0][3]) + "<br>Total Value " + str(result[0][4]) + "<br>"  
    conn.close()        
    return inputs

def getInactiveUsersOrders():
    datesql = "select  date(now() - interval 1 day), count(distinct user_id),count(distinct order_id), sum(quantity),sum(amount_paid) from allorder where store_id='spice' and category='Accs' and date(created_on)=date(now() - interval 1 day) and user_id in (select user_id from usercrmcallingdata where project_id=5 and date(created)>=date(now()- interval 7 day));"
    conn = getDbConnection()
    cursor = conn.cursor()
    cursor.execute(datesql)
    result = cursor.fetchall()
    inputs = "No of users who placed orders on Date " + str(result[0][0]) + "<br> Number of Users " + str(result[0][1])+ " <br> Total Orders " + str(result[0][2]) + "<br>Total Quantity " + str(result[0][3]) + "<br>Total Value " + str(result[0][4]) + "<br>"  
    conn.close()        
    return inputs

def getAccsTabOrders():
    datesql = "select  date(now() - interval 1 day), count(distinct user_id),count(distinct order_id), sum(quantity),sum(amount_paid) from allorder where store_id='spice' and category='Accs' and date(created_on)=date(now() - interval 1 day) and user_id in (select user_id from usercrmcallingdata where project_id=2 and date(created)>=date(now()- interval 7 day));"
    conn = getDbConnection()
    cursor = conn.cursor()
    cursor.execute(datesql)
    result = cursor.fetchall()
    inputs = "No of users who placed orders on Date " + str(result[0][0]) + "<br> Number of Users " + str(result[0][1])+ " <br> Total Orders " + str(result[0][2]) + "<br>Total Quantity " + str(result[0][3]) + "<br>Total Value " + str(result[0][4]) + "<br>"  
    conn.close()        
    return inputs

def getAccsCartProductPricingInput():
    datesql = "select product_input,pricing_input from productpricinginputs where date(created)=date(now() - interval 1 day) and project_id=1 and product_input not like '';"
    conn = getDbConnection()
    cursor = conn.cursor()
    cursor.execute(datesql)
    result = cursor.fetchall()
    inputs="Product Pricing Inputs"
    for r in result:
        inputs = inputs + "<br>" + r[0] + "-----" + str(r[1])
    conn.close()        
    return inputs

def getInactiveUsersProductPricingInput():
    datesql = "select product_input,pricing_input from productpricinginputs where date(created)=date(now() - interval 1 day) and project_id=5 and product_input not like '';"
    conn = getDbConnection()
    cursor = conn.cursor()
    cursor.execute(datesql)
    result = cursor.fetchall()
    inputs="Product Pricing Inputs"
    for r in result:
        inputs = inputs + "<br>" + r[0] + "-----" + str(r[1])
    conn.close()        
    return inputs

def getAccsTabProductPricingInput():
    datesql = "select product_input,pricing_input from productpricinginputs where date(created)=date(now() - interval 1 day) and project_id=2 and product_input not like '';"
    conn = getDbConnection()
    cursor = conn.cursor()
    cursor.execute(datesql)
    result = cursor.fetchall()
    inputs="Product Pricing Inputs"
    for r in result:
        inputs = inputs + "<br>" + r[0] + "-----" + str(r[1])
    conn.close()        
    return inputs

def getAccsOrdersHtml():
    heading = "Accessories Orders(Project 3)"
    message="No. of Agents " + str(ACCS_ORDER_LOGIN)
    message1="Total Calls attempted " + str(ACCS_ORDER_TOTAL)
    message2="Total Successful calls " + str(ACCS_ORDER_SUCCESSFUL)
    return "<br><br><u>" + heading + "</u><br>" +message+"<br>"+message1+"<br>"+message2+"<br>"

def getAccsOrdersProject():
    datesql = "select  date(now() - interval 1 day), count(distinct user_id),count(distinct order_id), sum(quantity),sum(amount_paid) from allorder where store_id='spice' and category='Accs' and date(created_on)=date(now() - interval 1 day) and user_id in (select user_id from usercrmcallingdata where project_id=3 and date(created)>=date(now()- interval 7 day));"
    conn = getDbConnection()
    cursor = conn.cursor()
    cursor.execute(datesql)
    result = cursor.fetchall()
    inputs = "No of users who placed orders on Date " + str(result[0][0]) + "<br> Number of Users " + str(result[0][1])+ " <br> Total Orders " + str(result[0][2]) + "<br>Total Quantity " + str(result[0][3]) + "<br>Total Value " + str(result[0][4]) + "<br>"  
    conn.close()        
    return inputs

def getAccsOrdersProductPricingInput():
    datesql = "select product_input,pricing_input from productpricinginputs where date(created)=date(now() - interval 1 day) and project_id=3 and product_input not like '';"
    conn = getDbConnection()
    cursor = conn.cursor()
    cursor.execute(datesql)
    result = cursor.fetchall()
    inputs="Product Pricing Inputs"
    for r in result:
        inputs = inputs + "<br>" + r[0] + "-----" + str(r[1])
    conn.close()        
    return inputs
        
def sendmailretention(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
    
#     msg.attach(MIMEText(getAccsCartHtml(), 'html'))
#     msg.attach(MIMEText(getAccsCartOrders(),'html'))
#     msg.attach(MIMEText(getAccsCartProductPricingInput(),'html'))
#     
#     msg.attach(MIMEText(getAccsTabHtml(), 'html'))
#     msg.attach(MIMEText(getAccsTabOrders(),'html'))
#     msg.attach(MIMEText(getAccsTabProductPricingInput(),'html'))
#     
#     msg.attach(MIMEText(getAccsOrdersHtml(), 'html'))
#     msg.attach(MIMEText(getAccsOrdersProject(),'html'))
#     msg.attach(MIMEText(getAccsOrdersProductPricingInput(),'html'))
    
    msg.attach(MIMEText(getInactiveUsersHtml(), 'html'))
    msg.attach(MIMEText(getInactiveUsersOrders(),'html'))
    msg.attach(MIMEText(getInactiveUsersProductPricingInput(),'html'))
    
    fileMsg = MIMEBase('application', 'vnd.ms-excel')
    fileMsg.set_payload(file(RET_FILE).read())
    encoders.encode_base64(fileMsg)
    fileMsg.add_header('Content-Disposition', 'attachment;filename=' + fileName)
    msg.attach(fileMsg)
    
    MAILTO = ['amit.sirohi@shop2020.in','rajneesh.arora@saholic.com', 'khushal.bhatia@saholic.com', 'shailesh.kumar@shop2020.in','chaitnaya.vats@shop2020.in','rajender.singh@shop2020.in']
#     MAILTO = ['rajender.singh@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.send()

if __name__ == '__main__':
    main()