| Line 265... |
Line 265... |
| 265 |
center_alignment=xlwt.easyxf("align: horiz center")
|
265 |
center_alignment=xlwt.easyxf("align: horiz center")
|
| 266 |
|
266 |
|
| 267 |
|
267 |
|
| 268 |
ACCS_CART_QUERY="""
|
268 |
ACCS_CART_QUERY="""
|
| 269 |
select call_disposition,count(1) from
|
269 |
select call_disposition,count(1) from
|
| 270 |
(select * from (select * from callhistorycrm where date(created)=date(now())
|
270 |
(select * from (select * from callhistorycrm where date(created)=date(now() - interval 1 day)
|
| 271 |
and project_id=1 order by created desc)
|
271 |
and project_id=1 order by created desc)
|
| 272 |
as a group by user_id)
|
272 |
as a group by user_id)
|
| 273 |
a1 group by date(created), call_disposition;
|
273 |
a1 group by date(created), call_disposition;
|
| 274 |
"""
|
274 |
"""
|
| 275 |
ACCS_CART_AGENTS_CALLED_COUNT="""
|
275 |
ACCS_CART_AGENTS_CALLED_COUNT="""
|
| 276 |
select distinct(agent_id) from callhistorycrm where project_id=1 and date(created) = date(now())
|
276 |
select distinct(agent_id) from callhistorycrm where project_id=1 and date(created) = date(now() - interval 1 day)
|
| 277 |
"""
|
277 |
"""
|
| 278 |
ACCS_CART_QUERY_LOGIN_TIME="""
|
278 |
ACCS_CART_QUERY_LOGIN_TIME="""
|
| 279 |
select agent_id,TIMEDIFF(logoutTime,loginTime) from agentlogintimings where role='accs_cart' and date(created)=date(now());
|
279 |
select agent_id,TIMEDIFF(logoutTime,loginTime) from agentlogintimings where role='accs_cart' and date(created)=date(now() - interval 1 day);
|
| 280 |
"""
|
280 |
"""
|
| 281 |
ACCS_CART_DISPOSITION_DESCRIPTION="""
|
281 |
ACCS_CART_DISPOSITION_DESCRIPTION="""
|
| 282 |
select user_id,call_disposition,disposition_description,disposition_comments
|
282 |
select user_id,call_disposition,disposition_description,disposition_comments
|
| 283 |
from callhistorycrm where call_disposition not in ('ringing_no_answer','not_reachable','switch_off','call_later')
|
283 |
from callhistorycrm where call_disposition not in ('ringing_no_answer','not_reachable','switch_off','call_later')
|
| 284 |
and date(created)=date(now())"""
|
284 |
and date(created)=date(now() - interval 1 day)"""
|
| 285 |
|
285 |
|
| 286 |
ACCS_CART_PRODUCT_PRICING="""
|
286 |
ACCS_CART_PRODUCT_PRICING="""
|
| 287 |
select user_id,call_disposition,product_input,pricing_input from productpricinginputs where
|
287 |
select user_id,call_disposition,product_input,pricing_input from productpricinginputs where
|
| 288 |
date(created)=date(now()) and product_input not like '';
|
288 |
date(created)=date(now() - interval 1 day) and product_input not like '';
|
| 289 |
"""
|
289 |
"""
|
| 290 |
|
290 |
|
| 291 |
def getDbConnection():
|
291 |
def getDbConnection():
|
| 292 |
return MySQLdb.connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME)
|
292 |
return MySQLdb.connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME)
|
| 293 |
|
293 |
|
| Line 1466... |
Line 1466... |
| 1466 |
message3="Will Order "+ str(ACCS_CART_WILL_PLACE_ORDER)
|
1466 |
message3="Will Order "+ str(ACCS_CART_WILL_PLACE_ORDER)
|
| 1467 |
message4="Will Order % " +str(round((ACCS_CART_WILL_PLACE_ORDER/float(ACCS_CART_TOTAL))*100,2))
|
1467 |
message4="Will Order % " +str(round((ACCS_CART_WILL_PLACE_ORDER/float(ACCS_CART_TOTAL))*100,2))
|
| 1468 |
return "<u>" + heading + "</u><br>" +message+"<br>"+message1+"<br>"+message2+"<br>"+message3+"<br>"+message4+"<br>"
|
1468 |
return "<u>" + heading + "</u><br>" +message+"<br>"+message1+"<br>"+message2+"<br>"+message3+"<br>"+message4+"<br>"
|
| 1469 |
|
1469 |
|
| 1470 |
def getAccsCartOrders():
|
1470 |
def getAccsCartOrders():
|
| 1471 |
datesql = "select count(distinct order_id),sum(quantity),sum(amount_paid) from allorder where category='Accs' and date(created_on)>=date(now() - interval 7 day) and user_id in (select user_id from usercrmcallingdata where project_id=1);"
|
1471 |
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=1 and date(created)>=date(now()- interval 7 day));"
|
| 1472 |
conn = getDbConnection()
|
1472 |
conn = getDbConnection()
|
| 1473 |
cursor = conn.cursor()
|
1473 |
cursor = conn.cursor()
|
| 1474 |
cursor.execute(datesql)
|
1474 |
cursor.execute(datesql)
|
| 1475 |
result = cursor.fetchall()
|
1475 |
result = cursor.fetchall()
|
| 1476 |
inputs = "No of users who placed orders within 7 days of successful calling<br>" + "Total Orders " + str(result[0][0]) + "<br>Total Quantity " + str(result[0][1]) + "<br>Total Value " + str(result[0][2]) + "<br>"
|
1476 |
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>"
|
| 1477 |
conn.close()
|
1477 |
conn.close()
|
| 1478 |
return inputs
|
1478 |
return inputs
|
| 1479 |
|
1479 |
|
| 1480 |
def getAccsCartProductPricingInput():
|
1480 |
def getAccsCartProductPricingInput():
|
| 1481 |
datesql = "select product_input,pricing_input from productpricinginputs where date(created)=date(now()) and project_id=1 and product_input not like '';"
|
1481 |
datesql = "select product_input,pricing_input from productpricinginputs where date(created)=date(now() - interval 1 day) and project_id=1 and product_input not like '';"
|
| 1482 |
conn = getDbConnection()
|
1482 |
conn = getDbConnection()
|
| 1483 |
cursor = conn.cursor()
|
1483 |
cursor = conn.cursor()
|
| 1484 |
cursor.execute(datesql)
|
1484 |
cursor.execute(datesql)
|
| 1485 |
result = cursor.fetchall()
|
1485 |
result = cursor.fetchall()
|
| 1486 |
inputs="Product Pricing Inputs"
|
1486 |
inputs="Product Pricing Inputs"
|