$param = Request::instance()->param();
$source=$param['source'];
$industry=$param['industry'];
$searchMap=[];
if(!empty($source)) {
$searchMap['source']=$source;
}
if(!empty($industry)) {
$searchMap['industry']=$industry;
}
$sql=<<<EOF
select realname,total,A,B,C,D,other,
if(CONCAT(ROUND(A/total * 100, 2),'','%') is null,'0.00%',CONCAT(ROUND(A/total * 100, 2),'','%')) as A_Percentage,
if(CONCAT(ROUND(B/total * 100, 2),'','%') is null,'0.00%',CONCAT(ROUND(B/total * 100, 2),'','%')) as B_Percentage,
if(CONCAT(ROUND(C/total * 100, 2),'','%') is null,'0.00%',CONCAT(ROUND(C/total * 100, 2),'','%')) as C_Percentage,
if(CONCAT(ROUND(D/total * 100, 2),'','%') is null,'0.00%',CONCAT(ROUND(D/total * 100, 2),'','%')) as D_Percentage,
if(CONCAT(ROUND(other/total * 100, 2),'','%') is null,'0.00%',CONCAT(ROUND(other/total * 100, 2),'','%')) as other_Percentage,
own_customer_total,
CONCAT(ROUND(own_customer_total/customer_total * 100, 2),'','%') as customer_total_percentage
from (
select a.id,a.realname,count(b.leads_id) as total,count(*) as customer_total,
SUM(CASE WHEN b.level = 'A（重点客户）' THEN 1 else 0 END) AS A,
SUM(CASE WHEN b.level = 'B（普通客户）' THEN 1 else 0 END) AS B,
SUM(CASE WHEN b.level = 'C（非优先客户）' THEN 1 else 0 END) as C,
SUM(CASE WHEN b.level = 'D（无效客户）'THEN 1 else 0 END) AS D,
SUM(CASE WHEN b.level = '其他' or b.level='未接通' or b.level='' THEN 1 else 0 END) AS other,
SUM(CASE WHEN b.is_transform = 1 THEN 1 else 0 END) AS own_customer_total
from 5kcrm_admin_user a left join 5kcrm_crm_leads b on a.id=b.create_user_id where b.source=:source and b.industry=:industry GROUP BY a.id order by a.id asc
) test
EOF;
$result=Db::query($sql, $searchMap);
print_r($result);exit;查询条件为空的时候就报错！
最佳答案
