Subversion Repositories SmartDukaan

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

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

              }

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;

"""

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, '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)

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

    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(22,0, 'User Id', style)

    worksheet.write(22,1, 'Call Disposition', style)

    worksheet.write(22,2, 'Product Inputs', style)

    worksheet.write(22,3, 'Pricing Inputs', style)    

    datesql = ACCS_ORDER_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

    conn.close()

    newWorkbook.save(RET_FILE)

        generateAccessoriesOrderReport()

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

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

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

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