'') "; } $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_Dict= '".$vtext."' and a.ServiceOrdVisit_ID=b.ServiceOrdVisit_ID and b.ServiceOrdVisit_time ".$SqlOrdDateType.") other".$vId; } } //查询开始 $sql="select OA_Name,ServiceOrdVisit_ID,COUNT(ServiceOrdID) VisitSum, ( select COUNT(*) from (select b.ServiceOrdID VisitSum from ServiceOrder b,DispatchOrd_Entourage,DispatchOrd where a.ServiceOrdVisit_ID=b.ServiceOrdVisit_ID 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%') and b.ServiceOrdVisit_time ".$SqlOrdDateType." GROUP BY b.ServiceOrdID) as bb) dissatisfied".$ListSql." from ServiceOrder as a left join OA_User on ServiceOrdVisit_ID=OA_User_ID where ServiceOrdVisit<>'' and ServiceOrdVisit_time ".$SqlOrdDateType." GROUP BY OA_Name,ServiceOrdVisit_ID 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); $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) ) { //读取数据 $OA_Name = $rs['OA_Name']; $ServiceOrdVisit_ID = $rs['ServiceOrdVisit_ID']; $VisitSum = $rs['VisitSum']; $dissatisfied = $rs['dissatisfied']; //读取数据--End //写入数组 $FormArray[$i][0]=$OA_Name; $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]; } $FormArray[$i][2]="".$FormArray[$i][2].""; $i++; } } } if ($is_CSV==2) { echo json_encode($FormArray); } //echo print_r($FormArray); //exit; ?>