1){
$orderbydesc="";
$descTXT="↓";
}else{
$orderbydesc=" desc";
$descTXT="↑";
}
If ($orderby=="") {
$orderby="OA_DepartmentID,OA_User_ID";
$descTXT="↓";
}
//查询开始
$SqlOrdDateType=empty($SqlOrdDateType)!=false ? "" : $SqlOrdDateType;
$OrdClassType=empty($OrdClassType)!=false ? "1" : $OrdClassType;
/*
$sql="select SourceName,UserSource,count(ServiceOrdID) as OrdSumInt,sum(isnull(OrdSumIntDel,0)) as OrdSumIntDel,sum(isnull(OrdSumPrice,0)) as OrdSumPrice,sum(isnull(OrdSumPriceDel,0)) as OrdSumPriceDel from (
select ServiceOrdID,ServiceOrdState,ServiceOrdIntroducer as UserSource
,(CASE when ServiceOrdIntroducer<>'' then (select top 1 IntroducerName from IntroducerData where IntroducerID=ServiceOrdIntroducer) when ToUserUUID<>0 then '营销小程序' else '966120小程序' end)as SourceName
,OrdSumIntDel=(select count(ServiceOrdID) from ServiceOrder as b where ServiceOrdState=4 and a.ServiceOrdID=b.ServiceOrdID)
,ServiceOrdTraTxnPrice as OrdSumPrice
,OrdSumPriceDel=(select sum(ServiceOrdTraTxnPrice) from ServiceOrder as b where ServiceOrdState=4 and a.ServiceOrdID=b.ServiceOrdID)
from ServiceOrder as a where ((ServiceOrdCancelReason<>10 or ServiceOrdCancelReason is null) and (ServiceOrdCancelReasonTXT<>'自动取消' or ServiceOrdCancelReasonTXT is null)) and (ServiceOrdOperationRemarks<>'[客户未正式提交需求，可不联系]' or ServiceOrdOperationRemarks is null) and ServiceOrd_CC_Time ".$SqlDateType." and ServiceOrdSource=5
) as aa GROUP BY SourceName,UserSource";
*/
$sql="select SourceName,UserSource,count(ServiceOrdID) as OrdSumInt
,sum(isnull(OrdSumIntOK,0)) as OrdSumIntOK,sum(isnull(OrdSumPriceOK,0)) as OrdSumPriceOK
,sum(isnull(OrdSumIntDel,0)) as OrdSumIntDel,sum(isnull(OrdSumPrice,0)) as OrdSumPrice
,sum(isnull(OrdSumPriceDel,0)) as OrdSumPriceDel from (
select ServiceOrdID,ServiceOrdState,ServiceOrdIntroducer as UserSource
,(CASE when ServiceOrdIntroducer<>'' then (select top 1 IntroducerName from IntroducerData where IntroducerID=ServiceOrdIntroducer) when ToUserUUID<>0 then '营销小程序' else '966120小程序' end)as SourceName
,OrdSumIntOK=(select count(ServiceOrdID) from ServiceOrder as b where ServiceOrdState=3 and a.ServiceOrdID=b.ServiceOrdID)
,OrdSumPriceOK=(select sum(ServiceOrdTraTxnPrice) from ServiceOrder as b where ServiceOrdState=3 and a.ServiceOrdID=b.ServiceOrdID)
,OrdSumIntDel=(select count(ServiceOrdID) from ServiceOrder as b where ServiceOrdState=4 and a.ServiceOrdID=b.ServiceOrdID)
,ServiceOrdTraTxnPrice as OrdSumPrice
,OrdSumPriceDel=(select sum(ServiceOrdTraTxnPrice) from ServiceOrder as b where ServiceOrdState=4 and a.ServiceOrdID=b.ServiceOrdID)
from ServiceOrder as a
where ((ServiceOrdCancelReason<>10 or ServiceOrdCancelReason is null) and (ServiceOrdCancelReasonTXT<>'自动取消' or ServiceOrdCancelReasonTXT is null)) and (ServiceOrdOperationRemarks<>'[客户未正式提交需求，可不联系]' or ServiceOrdOperationRemarks is null) and ServiceOrdUserID not in (4,53,116368,93481) and ServiceOrd_CC_Time ".$SqlDateType." and ServiceOrdSource=5
) as aa GROUP BY SourceName,UserSource";
//echo $sql;//exit;
$data = sqlsrv_query($conn,$sql);
if($data == true){
$FormArray=array();
//表头
$FormArray[0]=array("来源","任务单总数","取消数量","未调度数量","已调度数量","有效占比","任务单总金额","取消金额","未调度金额","已调度金额","有效占比");
$FormArrayX=count($FormArray[0]);
if (sqlsrv_rows_affected($data)==0) {
$SystemMessageType=2;
$SystemMessageTXT="数据库中相关无数据！";
$y=0;
} else {
$i=0;
while($rs = sqlsrv_fetch_array( $data, SQLSRV_FETCH_ASSOC) ) {
//读取数据
$SourceName = $rs['SourceName'];
$UserSource = $rs['UserSource'];
$OrdSumInt = $rs['OrdSumInt'];
$OrdSumIntOK = $rs['OrdSumIntOK'];
$OrdSumIntDel = $rs['OrdSumIntDel'];
$OrdSumPrice = $rs['OrdSumPrice'];
$OrdSumPriceOK= $rs['OrdSumPriceOK'];
$OrdSumPriceDel= $rs['OrdSumPriceDel'];
if ($UserSource!='') {
$SourcID="S".$UserSource;
}elseif ($SourceName=='营销小程序') {
$SourcID="U";
}else{
$SourcID="9";
}
//读取数据--End
//写入数组
$i++;
$FormArray[$i][0]=$SourceName;
$FormArray[$i][1]="$OrdSumInt";
$FormArray[$i][2]="$OrdSumIntDel";
$FormArray[$i][3]="".($OrdSumInt-$OrdSumIntDel-$OrdSumIntOK)."";
$FormArray[$i][4]="".($OrdSumIntOK)."";
if (($OrdSumInt-$OrdSumIntDel)>0) {
$FormArray[$i][5]=floor(((($OrdSumInt-$OrdSumIntDel)/$OrdSumInt)*100))."%";
}else{
$FormArray[$i][5]="0%";
}
$FormArray[$i][6]="".floor($OrdSumPrice)."";
$FormArray[$i][7]="".floor($OrdSumPriceDel)."";
$FormArray[$i][8]="".($OrdSumPrice-$OrdSumPriceDel-$OrdSumPriceOK)."";
$FormArray[$i][9]="".floor($OrdSumPriceOK)."";
if (($OrdSumPrice-$OrdSumPriceDel)>0) {
$FormArray[$i][10]=floor(((($OrdSumPrice-$OrdSumPriceDel)/$OrdSumPrice)*100))."%";
}else{
$FormArray[$i][10]="0%";
}
//写入数组--End
}
}
}
//echo print_r($FormArray);
//exit;
?>