<?php
|
//分页信息
|
$page=empty($_REQUEST['page'])!=false ? 1 : $_REQUEST['page'];
|
$QuantityInt = 20; //每页显示数量
|
$acc1=$page; //当前页数
|
$acc2=0; //总页数
|
$acc3=0; //总记录数
|
|
//查询条件
|
$searchSql="";
|
/*
|
$searchTXT=empty($searchTXT)!=false ? "" : $searchTXT;
|
if ($searchTXT!=""){
|
$searchSql=" and (OA_Name like '%$searchTXT%') ";
|
}else{
|
$searchSql="";
|
}
|
*/
|
|
$orderby=empty($orderby)!=false ? "" : $orderby;
|
If (strrpos($orderby," desc")>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,11638,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]="<a href=\"/ServiceOrder_List.gds?h_menu1_1=1&OrdState=0&OrdDateType=$OrdDateType&OrdDateStart=$OrdDateStart&OrdDateEnd=$OrdDateEnd&OrdClassList=-1&searchTXT=WXCX|0$SourcID\" target=\"_blank\">$OrdSumInt</a>";
|
$FormArray[$i][2]="<a href=\"/ServiceOrder_List.gds?h_menu1_1=1&OrdState=0&OrdDateType=$OrdDateType&OrdDateStart=$OrdDateStart&OrdDateEnd=$OrdDateEnd&OrdClassList=-1&searchTXT=WXCX|4$SourcID\" target=\"_blank\">$OrdSumIntDel</a>";
|
$FormArray[$i][3]="<a href=\"/ServiceOrder_List.gds?h_menu1_1=1&OrdState=0&OrdDateType=$OrdDateType&OrdDateStart=$OrdDateStart&OrdDateEnd=$OrdDateEnd&OrdClassList=-1&searchTXT=WXCX|1$SourcID\" target=\"_blank\">".($OrdSumInt-$OrdSumIntDel-$OrdSumIntOK)."</a>";
|
$FormArray[$i][4]="<a href=\"/ServiceOrder_List.gds?h_menu1_1=1&OrdState=0&OrdDateType=$OrdDateType&OrdDateStart=$OrdDateStart&OrdDateEnd=$OrdDateEnd&OrdClassList=-1&searchTXT=WXCX|3$SourcID\" target=\"_blank\">".($OrdSumIntOK)."</a>";
|
if (($OrdSumInt-$OrdSumIntDel)>0) {
|
$FormArray[$i][5]=floor(((($OrdSumInt-$OrdSumIntDel)/$OrdSumInt)*100))."%";
|
}else{
|
$FormArray[$i][5]="0%";
|
}
|
$FormArray[$i][6]="<a href=\"/ServiceOrder_List.gds?h_menu1_1=1&OrdState=0&OrdDateType=$OrdDateType&OrdDateStart=$OrdDateStart&OrdDateEnd=$OrdDateEnd&OrdClassList=-1&searchTXT=WXCX|0$SourcID\" target=\"_blank\">".floor($OrdSumPrice)."</a>";
|
$FormArray[$i][7]="<a href=\"/ServiceOrder_List.gds?h_menu1_1=1&OrdState=0&OrdDateType=$OrdDateType&OrdDateStart=$OrdDateStart&OrdDateEnd=$OrdDateEnd&OrdClassList=-1&searchTXT=WXCX|4$SourcID\" target=\"_blank\">".floor($OrdSumPriceDel)."</a>";
|
$FormArray[$i][8]="<a href=\"/ServiceOrder_List.gds?h_menu1_1=1&OrdState=0&OrdDateType=$OrdDateType&OrdDateStart=$OrdDateStart&OrdDateEnd=$OrdDateEnd&OrdClassList=-1&searchTXT=WXCX|1$SourcID\" target=\"_blank\">".($OrdSumPrice-$OrdSumPriceDel-$OrdSumPriceOK)."</a>";
|
$FormArray[$i][9]="<a href=\"/ServiceOrder_List.gds?h_menu1_1=1&OrdState=0&OrdDateType=$OrdDateType&OrdDateStart=$OrdDateStart&OrdDateEnd=$OrdDateEnd&OrdClassList=-1&searchTXT=WXCX|3$SourcID\" target=\"_blank\">".floor($OrdSumPriceOK)."</a>";
|
if (($OrdSumPrice-$OrdSumPriceDel)>0) {
|
$FormArray[$i][10]=floor(((($OrdSumPrice-$OrdSumPriceDel)/$OrdSumPrice)*100))."%";
|
}else{
|
$FormArray[$i][10]="0%";
|
}
|
|
//写入数组--End
|
}
|
|
}
|
}
|
|
//echo print_r($FormArray);
|
//exit;
|
?>
|
