Subversion Repositories SmartDukaan

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;

"""

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

    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)

        generateAccessoriesTabsReport()

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)

    message4="Will Order % " +str(round((ACCS_TAB_WILL_PLACE_ORDER/float(ACCS_TAB_TOTAL))*100,2)) 

    return "<br><br><u>" + heading + "</u><br>" +message+"<br>"+message1+"<br>"+message2+"<br>"+message3+"<br>"+message4+"<br>"

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

    msg.attach(MIMEText(getAccsTabHtml(), 'html'))

    msg.attach(MIMEText(getAccsTabOrders(),'html'))

    msg.attach(MIMEText(getAccsTabProductPricingInput(),'html'))