Subversion Repositories SmartDukaan

'''
Created on May 25, 2015

@author: amit
'''

from datetime import datetime, timedelta, date
from email import encoders
from email.mime.base import MIMEBase
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
import MySQLdb
import copy
import smtplib
import xlwt

SENDER = "adwords@shop2020.in"
PASSWORD = "adwords_shop2020"
SUBJECT = "CRM Outbound Report for " + str(date.today() - timedelta(days=1))
SMTP_SERVER = "smtp.gmail.com"
SMTP_PORT = 587  

date_format = xlwt.XFStyle()
date_format.num_format_str = 'dd/mm/yyyy'

datetime_format = xlwt.XFStyle()
datetime_format.num_format_str = 'dd/mm/yyyy HH:MM AM/PM'

default_format = xlwt.XFStyle()

boldStyle = xlwt.XFStyle()
f = xlwt.Font()
f.bold = True
boldStyle.font = f

date_format.font = f
ALLIED = 4
NOT_RETAILER =7
RETAILER_NOT_INTERESTED=3
NOT_CONTACTABLE = 6
CALL_LATER=5
ALREADY_USER = 8
TOTAL = 9
TOTAL_AGENTS = 10
TOTAL_LOGIN_TIME = 11
TOTAL_CALL_DURATION = 12
CONVERTED=1
AWAITING_CONVERSION =2
LINK_SENT =2





disposition_map={
                    'call_later':CALL_LATER,
                    'ringing_no_answer':NOT_CONTACTABLE,
                    'not_reachable':NOT_CONTACTABLE,
                    'switch_off':NOT_CONTACTABLE,
                    'invalid_no':NOT_CONTACTABLE,
                    'wrong_no':NOT_CONTACTABLE,
                    'hang_up':NOT_CONTACTABLE,
                    'retailer_not_interested':RETAILER_NOT_INTERESTED,
                    'alreadyuser':ALREADY_USER,
                    'verified_link_sent':LINK_SENT,
                    'converted':CONVERTED,
                    'accessory_retailer':ALLIED,
                    'service_center_retailer':ALLIED,
                    'not_retailer':NOT_RETAILER,
                    'recharge_retailer':ALLIED,
                    None:TOTAL,
                    'agent_count':TOTAL_AGENTS,
                    'login_time' : TOTAL_LOGIN_TIME,
                    'call_duration': TOTAL_CALL_DURATION
                }
STATUS_MAP = {
              CONVERTED:0,
              AWAITING_CONVERSION:0,
              RETAILER_NOT_INTERESTED:0,
              NOT_CONTACTABLE:0,
              ALLIED:0,
              CALL_LATER:0,
              NOT_CONTACTABLE:0,
              NOT_RETAILER:0,
              ALREADY_USER:0,
              TOTAL:0,
              TOTAL_AGENTS:0,
              TOTAL_LOGIN_TIME:0,
              TOTAL_CALL_DURATION:0
              }
PREVIOUS_DATE = datetime.now() -timedelta(days=1)
DATE_MAP = {PREVIOUS_DATE.date():1, (PREVIOUS_DATE - timedelta(days=1)).date():2, (PREVIOUS_DATE - timedelta(days=2)).date():3, (PREVIOUS_DATE - timedelta(days=3)).date():4, 'MTD':5, 'Total Till Date':6}

def getLast4daysQuery(type='fresh'):
    fourDaysBefore = PREVIOUS_DATE - timedelta(days=3)
    fourDaysBefore = fourDaysBefore.date()
    curDate = (PREVIOUS_DATE + timedelta(days=1)).date()
    return '''select date(created), call_disposition, count(1) from (
    (select id, created, call_disposition from (select * from callhistory where created between '%s' and '%s' and call_type ='%s' order by created desc) as a group by retailer_id)
    union 
    (select id, created, 'converted' from (select h.*  from retailers r join callhistory h on h.retailer_id=r.id where r.status in ('onboarded', 'onboarding') and h.call_disposition='verified_link_sent' and h.call_type='%s' and h.created between '%s' and '%s' order by h.created desc) as b1 group by retailer_id) 
    )a group by date(created), call_disposition with rollup'''%(fourDaysBefore,curDate,type,type,fourDaysBefore,curDate )
        #union select date(created) dc, agent_i

def getMDTQuery(type='fresh'):
    return '''select a.dc, a.call_disposition, count(1) from
            (select 'MTD' as dc, call_disposition from (select * from callhistory where month(created)=%s and call_type ='%s' order by created desc) as a group by retailer_id
    union all     select 'Total Till Date' dc, call_disposition from (select * from callhistory where call_type ='%s' order by created desc) as a group by retailer_id
    union all    select 'MTD' dc, 'converted'  call_disposition  from(select h.* from retailers r join callhistory h on h.retailer_id=r.id where r.status in ('onboarded', 'onboarding') and h.call_disposition='verified_link_sent' and h.call_type='%s' and month(h.created) = %s order by h.created desc) as b1 group by retailer_id
    union all    select 'Total Till Date' dc, 'converted'  call_disposition  from(select h.* from retailers r join callhistory h on h.retailer_id=r.id where r.status in ('onboarded', 'onboarding') and h.call_disposition='verified_link_sent' and h.call_type='%s' order by h.created desc) as b1 group by retailer_id)
    a group by a.dc, a.call_disposition with rollup
    '''%(PREVIOUS_DATE.month, type, type, type, PREVIOUS_DATE.month, type)
    
def getConvertedMTDQuery():
    pass


def getTillDateQuery():
    pass
 
    
def setWorksheetTemplate(freshSheet, followupSheet):
    freshSheet.write(1, 0, 'Converted', boldStyle)
    freshSheet.write(2, 0, 'Link sent yet to be converted', boldStyle)
    freshSheet.write(3, 0, 'Retailer not interested', boldStyle)
    freshSheet.write(4, 0, 'Allied category retailer', boldStyle)
    freshSheet.write(5, 0, 'Call Later', boldStyle)
    freshSheet.write(6, 0, 'Not contactable', boldStyle)
    freshSheet.write(7, 0, 'Not a retailer', boldStyle)
    freshSheet.write(8, 0, 'AlreadyUser', boldStyle)
    freshSheet.write(9, 0, 'Total Attempts (Total of above)', boldStyle)
    freshSheet.write(10, 0, 'Total Agents Logged in', boldStyle)
    freshSheet.write(11, 0, 'Total Login time (Hours)', boldStyle)
    freshSheet.write(12, 0, 'Total Call Duration (Hours)', boldStyle)
    
    followupSheet.write(1, 0, 'Conversion After Followup', boldStyle)
    followupSheet.write(2, 0, 'Link sent again', boldStyle)
    followupSheet.write(3, 0, 'Retailer not interested', boldStyle)
    followupSheet.write(4, 0, 'Allied category retailer', boldStyle)
    followupSheet.write(5, 0, 'Call Later', boldStyle)
    followupSheet.write(6, 0, 'Not contactable', boldStyle)
    followupSheet.write(7, 0, 'Not a retailer', boldStyle)
    followupSheet.write(8, 0, 'AlreadyUser', boldStyle)
    followupSheet.write(9, 0, 'Total Attempts (Total of above)', boldStyle)
    followupSheet.write(10, 0, 'Total Agents Logged in', boldStyle)
    followupSheet.write(11, 0, 'Total Login time (Hours)', boldStyle)
    followupSheet.write(12, 0, 'Total Call Duration (Hours)', boldStyle)
    

    
def getDbConnection():
    return MySQLdb.connect('localhost', 'root', 'shop2020', 'dtr')


    conn = getDbConnection()
    
def populateFreshSheet(freshSheet):
    query = getLast4daysQuery()
    print query
    conn = getDbConnection()
    cursor = conn.cursor()
    cursor.execute(query)
    result = cursor.fetchall()
    datewiseMap = {}
    for x in [0,1,2,3,'MTD', 'Total Till Date' ]:
        if type(x) is int:
            datetime1 = PREVIOUS_DATE - timedelta(days=x)
            datewiseMap[datetime1.date()] = copy.deepcopy(STATUS_MAP)
        else:
            datewiseMap[x] = copy.deepcopy(STATUS_MAP)
    print datewiseMap
    for row in result:
        if row[0] is None:
            continue
        print row[0]
        statuswisemap = datewiseMap.get(row[0])
        disposition_tag = disposition_map.get(row[1])
        if statuswisemap.get(disposition_tag) is None:
            statuswisemap[disposition_tag] = 0
        print row[2]
        statuswisemap[disposition_tag] += row[2]
    
    query = getMDTQuery()
    cursor.execute(query)
    result = cursor.fetchall()
    for row in result:
        if row[0] is None:
            continue
        print row[0]
        statuswisemap = datewiseMap.get(row[0])
        disposition_tag = disposition_map.get(row[1])
        if statuswisemap.get(disposition_tag) is None:
            statuswisemap[disposition_tag] = 0
        print row[2]
        statuswisemap[disposition_tag] += row[2]
    
        
        
    q= '''
    select * from (select date(created) d, count(distinct agent_id), sum(TIMESTAMPDIFF(SECOND, loginTime,logoutTime)) from  agentlogintimings   where role = 'fresh' group by date(created) with rollup)d1  order by -d limit 6 
    '''
    conn = getDbConnection()
    cursor = conn.cursor()
    cursor.execute(q)
    result = cursor.fetchall()
    for row in result:
        if row[0] is None:
            statuswisemap = datewiseMap.get('Total Till Date')
        else:
            statuswisemap = datewiseMap.get(row[0])
            
        print "------------", row[0], statuswisemap
        if statuswisemap is None:
            continue
        statuswisemap[disposition_map.get('agent_count')] = row[1]
        statuswisemap[disposition_map.get('login_time')] = round(row[2]/3600,2)

    q= '''
    select 'MTD', count(distinct agent_id),  sum(TIMESTAMPDIFF(SECOND, loginTime,logoutTime)) from agentlogintimings where role = 'fresh' and month(created)=month(DATE_ADD(now(), interval - 1 day))
    '''
    conn = getDbConnection()
    cursor = conn.cursor()
    cursor.execute(q)
    result = cursor.fetchall()
    for row in result:
        if row[0] is None:
            statuswisemap = datewiseMap.get('Total Till Date')
        else:
            statuswisemap = datewiseMap.get(row[0])
            
        print "------------", row[0], statuswisemap
        if statuswisemap is None:
            continue
        statuswisemap[disposition_map.get('agent_count')] = row[1]
        statuswisemap[disposition_map.get('login_time')] = round(row[2]/3600,2)
        
        
    q ='''
         select * from (select date(created)d, sum(duration_sec) from callhistory  where duration_sec >0 and call_type='fresh' group by date(created) with rollup) a order by -d limit 5
         '''
    conn = getDbConnection()
    cursor = conn.cursor()
    cursor.execute(q)
    result = cursor.fetchall()
    for row in result:
        if row[0] is None:
            statuswisemap = datewiseMap.get('Total Till Date')
        else:
            statuswisemap = datewiseMap.get(row[0])
            
        print "------------", row[0], statuswisemap
        if statuswisemap is None:
            continue
        statuswisemap[disposition_map.get('call_duration')] = round(row[1]/3600,2)

    q ='''
         select 'MTD', sum(duration_sec) from callhistory  where duration_sec >0 and call_type='fresh' and month(date_add(now(), interval -1 day)) = month(created) group by month(created) 
         '''
    conn = getDbConnection()
    cursor = conn.cursor()
    cursor.execute(q)
    result = cursor.fetchall()
    for row in result:
        if row[0] is None:
            statuswisemap = datewiseMap.get('Total Till Date')
        else:
            statuswisemap = datewiseMap.get(row[0])
            
        print "------------", row[0], statuswisemap
        if statuswisemap is None:
            continue
        statuswisemap[disposition_map.get('call_duration')] = round(row[1]/3600,2)

    for disdate, statusmap in datewiseMap.iteritems():
        freshSheet.write(0, DATE_MAP.get(disdate), disdate, date_format if type(disdate) is date else boldStyle)
        for index, statuscount in statusmap.iteritems():
            if index in [TOTAL,LINK_SENT]:
                statuscount -= statusmap.get(CONVERTED)
                
            print index, disdate, statuscount    
            freshSheet.write(index, DATE_MAP.get(disdate),statuscount)

def populateFollowupSheet(followupSheet):
    query = getLast4daysQuery('followup')
    print query
    conn = getDbConnection()
    cursor = conn.cursor()
    cursor.execute(query)
    result = cursor.fetchall()
    datewiseMap = {}
    for x in [0,1,2,3,'MTD', 'Total Till Date' ]:
        if type(x) is int:
            datetime1 = PREVIOUS_DATE - timedelta(days=x)
            datewiseMap[datetime1.date()] = copy.deepcopy(STATUS_MAP)
        else:
            datewiseMap[x] = copy.deepcopy(STATUS_MAP)
    print datewiseMap
    for row in result:
        if row[0] is None:
            continue
        print row[0]
        statuswisemap = datewiseMap.get(row[0])
        disposition_tag = disposition_map.get(row[1])
        print "disposition_tag",disposition_tag
        if statuswisemap.get(disposition_tag) is None:
            statuswisemap[disposition_tag] = 0
        print row[2]
        statuswisemap[disposition_tag] += row[2]
    
    query = getMDTQuery('followup')
    cursor.execute(query)
    result = cursor.fetchall()
    for row in result:
        if row[0] is None:
            continue
        print row[0]
        statuswisemap = datewiseMap.get(row[0])
        disposition_tag = disposition_map.get(row[1])
        print "disposition_tag",disposition_tag
        if statuswisemap.get(disposition_tag) is None:
            statuswisemap[disposition_tag] = 0
        print row[2]
        statuswisemap[disposition_tag] += row[2]
        
        
        q= '''
    select * from (select date(created) d, count(distinct agent_id), sum(TIMESTAMPDIFF(SECOND, loginTime,logoutTime)) from  agentlogintimings   where role = 'followup' group by date(created) with rollup)d1  order by -d limit 6 
    '''
    conn = getDbConnection()
    cursor = conn.cursor()
    cursor.execute(q)
    result = cursor.fetchall()
    for row in result:
        if row[0] is None:
            statuswisemap = datewiseMap.get('Total Till Date')
        else:
            statuswisemap = datewiseMap.get(row[0])
            
        print "------------", row[0], statuswisemap
        if statuswisemap is None:
            continue
        statuswisemap[disposition_map.get('agent_count')] = row[1]
        statuswisemap[disposition_map.get('login_time')] = round(row[2]/3600,2)

    q= '''
    select 'MTD', count(distinct agent_id),  sum(TIMESTAMPDIFF(SECOND, loginTime,logoutTime)) from agentlogintimings where role = 'followup' and month(created)=month(DATE_ADD(now(), interval - 1 day))
    '''
    conn = getDbConnection()
    cursor = conn.cursor()
    cursor.execute(q)
    result = cursor.fetchall()
    for row in result:
        if row[0] is None:
            statuswisemap = datewiseMap.get('Total Till Date')
        else:
            statuswisemap = datewiseMap.get(row[0])
            
        print "------------", row[0], statuswisemap
        if statuswisemap is None:
            continue
        statuswisemap[disposition_map.get('agent_count')] = row[1]
        statuswisemap[disposition_map.get('login_time')] = round(row[2]/3600,2)
        
            
    q ='''
         select * from (select date(created)d, sum(duration_sec) from callhistory  where duration_sec >0 and call_type='followup' group by date(created) with rollup) a order by -d limit 5
         '''
    conn = getDbConnection()
    cursor = conn.cursor()
    cursor.execute(q)
    result = cursor.fetchall()
    for row in result:
        if row[0] is None:
            statuswisemap = datewiseMap.get('Total Till Date')
        else:
            statuswisemap = datewiseMap.get(row[0])
            
        print "------------", row[0], statuswisemap
        if statuswisemap is None:
            continue
        statuswisemap[disposition_map.get('call_duration')] = round(row[1]/3600,2)
        
    q ='''
         select 'MTD', sum(duration_sec) from callhistory  where duration_sec >0 and call_type='followup' and month(date_add(now(), interval -1 day)) = month(created) group by month(created) 
         '''
    conn = getDbConnection()
    cursor = conn.cursor()
    cursor.execute(q)
    result = cursor.fetchall()
    for row in result:
        if row[0] is None:
            statuswisemap = datewiseMap.get('Total Till Date')
        else:
            statuswisemap = datewiseMap.get(row[0])
            
        print "------------", row[0], statuswisemap
        if statuswisemap is None:
            continue
        statuswisemap[disposition_map.get('call_duration')] = round(row[1]/3600,2)

    for disdate, statusmap in datewiseMap.iteritems():
        print disdate
        followupSheet.write(0, DATE_MAP.get(disdate), disdate, date_format if type(disdate) is date else boldStyle)
        for index, statuscount in statusmap.iteritems():
            if index in [TOTAL,LINK_SENT]:
                statuscount -= statusmap.get(CONVERTED)
                
                
            followupSheet.write(index, DATE_MAP.get(disdate),statuscount)

def generateCrmAcquisitionReport():
    workbook = xlwt.Workbook()
    freshSheet = workbook.add_sheet("Fresh Call Summary")
    followupSheet = workbook.add_sheet("Followup Call Summary")
    setWorksheetTemplate(freshSheet, followupSheet)
    populateFreshSheet(freshSheet)
    populateFollowupSheet(followupSheet)
    workbook.save("crmacquisition.xls")
    sendmail(["amit.gupta@shop2020.in", "rajneesh.arora@saholic.com", "amit.sirohi@shop2020.in"], "", "CRM Acquision Report", "crmacquisition.xls")
    
def sendmail(email, message, title, *varargs):
    if email == "":
        return
    mailServer = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)
    mailServer.ehlo()
    mailServer.starttls()
    mailServer.ehlo()
    
    # Create the container (outer) email message.
    msg = MIMEMultipart()
    msg['Subject'] = title
    msg.preamble = title
    html_msg = MIMEText(message, 'html')
    msg.attach(html_msg)
    
    #snapdeal more to be added here
    for fileName in varargs:
        snapdeal = MIMEBase('application', 'vnd.ms-excel')
        snapdeal.set_payload(file(fileName).read())
        encoders.encode_base64(snapdeal)
        snapdeal.add_header('Content-Disposition', 'attachment;filename=' + fileName)
        msg.attach(snapdeal)


    email.append('amit.gupta@shop2020.in')
    MAILTO = email 
    mailServer.login(SENDER, PASSWORD)
    mailServer.sendmail(SENDER, MAILTO, msg.as_string())        

def main():
    generateCrmAcquisitionReport()

if __name__ == '__main__':
    main()