Subversion Repositories SmartDukaan

import optparse

RET_FILE = "/tmp/CRM_Retention_Report.xls"  

RET_SUBJECT = "CRM Retention Report for " + str(date.today())

accsList=['1','2','3','4','5','6','7','8','9','10','11','12'] 

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

              }

ACCS_CART_QUERY="""

select call_disposition,count(1) from 

(select * from (select * from callhistorycrm where date(created)=date(now()) 

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

"""

ACCS_CART_QUERY_LOGIN_TIME="""

select agent_id,TIMEDIFF(logoutTime,loginTime) from agentlogintimings where role='accs_cart' and date(created)=date(now());

"""

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

ACCS_CART_PRODUCT_PRICING="""

select user_id,call_disposition,product_input,pricing_input from productpricinginputs where 

date(created)=date(now()) and product_input not like '';

"""

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

    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:

            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

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

    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)

    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':

    if options.reporttype == 'retention':

        sendmailretention(["manas.kapoor@shop2020.in"], "", RET_FILE, RET_SUBJECT)

        #sendmailretention(["amit.sirohi@shop2020.in","rajneesh.arora@saholic.com", "shailesh.kumar@shop2020.in","chaitnaya.vats@shop2020.in","manas.kapoor@shop2020.in"], "", RET_FILE, RET_SUBJECT)

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

    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', 'shailesh.kumar@shop2020.in','chaitnaya.vats@shop2020.in','manas.kapoor@shop2020.in']

    #MAILTO = ['manas.kapoor@saholic.com']

    mailServer.login(SENDER, PASSWORD)

    try:

        mailServer.sendmail(SENDER, MAILTO, msg.as_string())

    except:

        m = Email('localhost')

        mFrom = "dtr@shop2020.in"

        m.setFrom(mFrom)

        for receipient in MAILTO:

            m.addRecipient(receipient)

        m.setSubject(title)

        m.send()