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()