'') "; } $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="↓"; } if ($OrdDateType=="0" and $OrdDateStart=="" and $OrdDateEnd=="") {$OrdDateType=4;} $SqlOrdDateType=empty($SqlOrdDateType)!=false ? "" : $SqlOrdDateType; $OAOccupationSQL=empty($OAOccupation)!=false ? "" : " and EntourageID in (".$OAOccupation.") "; $StaffScoreSum=0; $StaffScoreSum_Bad=0; $StaffScoreSum_Other=0; //条件查询 $ListSql=""; $OtherFormArray=array(); $OtherArray=array(); $sql="select vId,vtext from dictionary where vType=1 and vtitle='VisitDict' order by vOrder"; //echo $sql;exit; $data = sqlsrv_query($conn,$sql); if($data == true){ while($rs = sqlsrv_fetch_array( $data, SQLSRV_FETCH_ASSOC) ) { $vId = $rs['vId']; $vtext = $rs['vtext']; array_push($OtherFormArray,"other".$vId); array_push($OtherArray,$vtext); $ListSql=$ListSql.",(select COUNT(ServiceOrdID) from ServiceOrder as b where b.ServiceOrdVisit like '%".$vtext."%' and a.ServiceOrdClass=b.ServiceOrdClass and b.ServiceOrdVisit_time ".$SqlOrdDateType.") other".$vId; } } $ReturnChannel="'aa'"; $ReturnChannelFormArray=array(); $ReturnChannelArray=array(); $sql="select vId,vtext from dictionary where vType=1 and vtitle='ReturnChannel' and vId<>7 order by vOrder"; //echo $sql;exit; $data = sqlsrv_query($conn,$sql); if($data == true){ while($rs = sqlsrv_fetch_array( $data, SQLSRV_FETCH_ASSOC) ) { $vId = $rs['vId']; $vtext = $rs['vtext']; array_push($ReturnChannelFormArray,"ReturnChannel".$vId); array_push($ReturnChannelArray,$vtext); $ReturnChannel=$ReturnChannel.",'".$vtext."'"; $ListSql=$ListSql.",(select COUNT(ServiceOrdID) from ServiceOrder as b where b.ServiceOrdReturnChannel='".$vtext."' and a.ServiceOrdClass=b.ServiceOrdClass and b.ServiceOrdVisit_time ".$SqlOrdDateType.") ReturnChannel".$vId; } } //查询开始 $sql="select replace(UnitShort,'微信小程序','广州') UnitShort,ServiceOrdClass,COUNT(ServiceOrdID) VisitSum, ( select COUNT(*) from (select b.ServiceOrdID VisitSum from ServiceOrder b,DispatchOrd_Entourage,DispatchOrd where a.ServiceOrdClass=b.ServiceOrdClass and EntourageState<>4 and DispatchOrdIDDt=DispatchOrdID and b.ServiceOrdID=ServiceOrdIDDt and DispatchOrdState not in (10,9) and (RV_PointE like '%1%' or RV_PointE like '%2%' or Guest_PointE like '%1%' or Guest_PointE like '%2%') and (b.ServiceOrdVisit_time ".$SqlOrdDateType." or b.Guest_Time ".$SqlOrdDateType.") GROUP BY b.ServiceOrdID) as bb) dissatisfied".$ListSql.", (select COUNT(ServiceOrdID) from ServiceOrder as b where b.ServiceOrdReturnChannel not in (".$ReturnChannel.") and a.ServiceOrdClass=b.ServiceOrdClass and b.ServiceOrdVisit_time ".$SqlOrdDateType.") ReturnChannel7 from ServiceOrder as a,dictionary left join IntroducerUnitData on ServiceBranch=vOrder2 where vtitle='OrderClass' and UnitState in (1,3) and ServiceOrdClass=vOrder2 and ServiceOrdVisit<>'' and ServiceOrdVisit_time ".$SqlOrdDateType." GROUP BY UnitShort,ServiceOrdClass order by COUNT(ServiceOrdID) desc"; //echo $sql;exit; $data = sqlsrv_query($conn,$sql); if($data == true){ $FormArray=array(); //表头 $FormArray[0]=array("分公司名","回访任务数量","满意任务数量","不满意任务数量"); $FormArray[0]=array_merge($FormArray[0],$OtherArray,$ReturnChannelArray); array_push($FormArray[0],"其他(手写)"); $FormArrayX=count($FormArray[0]); if (sqlsrv_rows_affected($data)==0) { $SystemMessageType=2; $SystemMessageTXT="数据库中相关无数据!"; $y=0; } else { $i=1; while($rs = sqlsrv_fetch_array( $data, SQLSRV_FETCH_ASSOC) ) { //读取数据 $UnitShort = $rs['UnitShort']; $ServiceOrdClass = $rs['ServiceOrdClass']; $VisitSum = $rs['VisitSum']; $dissatisfied = $rs['dissatisfied']; //读取数据--End //写入数组 $FormArray[$i][0]=$UnitShort; $FormArray[$i][1]=$VisitSum; $FormArray[$i][2]=$VisitSum-$dissatisfied; $FormArray[$i][3]="".$dissatisfied.""; $OtherInt=0; foreach($OtherFormArray as $value){ $OtherInt++; $FormArray[$i][3+$OtherInt]="".$rs[$value].""; $FormArray[$i][2]=$FormArray[$i][2]-$rs[$value]; } $ReturnChannelInt=0; foreach($ReturnChannelFormArray as $value){ $ReturnChannelInt++; $FormArray[$i][3+$OtherInt+$ReturnChannelInt]="".$rs[$value].""; } $FormArray[$i][count($FormArray[0])-1]="".$rs["ReturnChannel7"]."";; $i++; } } } if ($is_CSV==2) { echo json_encode($FormArray); } //echo print_r($FormArray); //exit; ?>