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;} //人员列表 $OAUserArray=array(); $sql="select OA_User_ID,OA_Name,OA_DepartmentID from OA_User where OA_execLevel in (1,2) and OA_DepartmentID in (11,5,8,150,157,215,198,217) and OA_User_ID not in (95,175,176) order by OA_DepartmentID,OA_User_ID"; $data = sqlsrv_query($conn,$sql); if($data == true){ while($rs = sqlsrv_fetch_array( $data, SQLSRV_FETCH_ASSOC) ) { $OA_User_ID = $rs['OA_User_ID']; array_push($OAUserArray,array("OA_User_ID"=>$rs['OA_User_ID'],"OA_Name"=>$rs['OA_Name'],"OA_DepartmentID"=>$rs['OA_DepartmentID'],"Mileage1"=>0,"Mileage2"=>0,"Mileage3"=>0,"Mileage4"=>0,"OrdInt1"=>0,"OrdInt2"=>0,"OrdInt3"=>0,"OrdInt4"=>0,"WorkingHours1"=>0,"WorkingHours2"=>0,"WorkingHours3"=>0,"WorkingHours4"=>0,"OTInt1"=>0,"OTInt2"=>0,"OTInt3"=>0,"OTInt4"=>0)); } } //更新特殊技能数据 $sql="update DispatchOrd_Entourage set Condition_is=1 from DispatchOrd_Entourage,DispatchOrd,ServiceOrd_Condition where DispatchOrdID=DispatchOrdIDDt and ServiceOrdCoID=ServiceOrdIDDt and DispatchOrdState in (8,9) and EntourageState<>4 and EntourageLong_is=0 and ConditionID in (4,22,24) and DispatchOrd_NS_Time ".$SqlOrdDateType; $data = sqlsrv_query($conn,$sql); //查询开始 $SqlOrdDateType=empty($SqlOrdDateType)!=false ? "" : $SqlOrdDateType; $OAOccupationSQL=empty($OAOccupation)!=false ? "" : " and EntourageID in (".$OAOccupation.") "; //$sql="select OA_User_ID,OA_Name,Post_MoneyP,Post_MoneyP1,EntourageP1,DispatchOrdID,ServiceOrdID,DispatchOrdNo=DispatchOrdClass+convert(nvarchar(10),year(DispatchOrd_NS_Time))+right('0'+convert(nvarchar(10),month(DispatchOrd_NS_Time)),2)+right('0'+convert(nvarchar(10),day(DispatchOrd_NS_Time)),2)+'-'+right('00'+convert(nvarchar(10),DispatchOrdNo),3),DispatchOrdActualDate=convert(char,DispatchOrdActualDate,120),DispatchOrdReturnDate=convert(char,DispatchOrdReturnDate,120),DispatchOrdTraStreetMileage,DispatchOrdTraEndMileage,DispatchOrdMileage,OrdDistance=convert(money,Replace(Replace(ServiceOrdTraDistance,'公里',''),'km','')),ServiceOrdAreaType,DispatchOrd_NS_Time=convert(char,DispatchOrd_NS_Time,120),TimeLength_Program,TimeLength_ICU,TimeLength_Wait,DispatchOrdPerfomance,EntourageOT_is,EntourageLong_is,EntourageBetimesHH,EntourageState_Time,EntourageMileageFactor,Stretcher_is,StretcherM=isnull((select sum(AddMoney) from (select AddMoney=(AddMoney/COUNT(a.Stretcher_is)) from DispatchOrd_Entourage as a,PaidMoney_Add as b where a.DispatchOrdIDDt=DispatchOrd_Entourage.DispatchOrdIDDt and b.ToDispatchOrdID=a.DispatchOrdIDDt and a.EntourageState<>4 and a.Stretcher_is=1 and b.AddMoneyType=2 GROUP BY AddMoney) as c),0),ServiceOrdPtOutHosp=(select vtext from dictionary where vType=1 and vtitle='HospName' and vID=ServiceOrdPtOutHosp),ServiceOrdTraVia,ServiceOrdPtInHosp=(select vtext from dictionary where vType=1 and vtitle='HospName' and vID=ServiceOrdPtInHosp),ServiceOrdTraEnd,Condition_is=(select COUNT(id) from ServiceOrd_Condition where ConditionID in (4,22,24) and ServiceOrdCoID=ServiceOrdIDDt) from DispatchOrd_Entourage,DispatchOrd,OA_User,ServiceOrder where OA_User_ID=EntourageOAid and ServiceOrdID=ServiceOrdIDDt and DispatchOrdID=DispatchOrdIDDt and (ServiceOrdTraDistance like '%公里%' or ServiceOrdTraDistance like '%km%') and DispatchOrdState in (8,9) and EntourageState<>4".$OAOccupationSQL." and DispatchOrd_NS_Time ".$SqlOrdDateType.$searchSql." and ServiceOrdClass in ('".$OrdClassListSql."') order by OA_User_ID,DispatchOrd_NS_Time"; $sql="select EntourageOAid,EntourageP1,DispatchOrdID,ServiceOrdID,DispatchOrdNo=DispatchOrdClass+convert(nvarchar(10),year(DispatchOrd_NS_Time))+right('0'+convert(nvarchar(10),month(DispatchOrd_NS_Time)),2)+right('0'+convert(nvarchar(10),day(DispatchOrd_NS_Time)),2)+'-'+right('00'+convert(nvarchar(10),DispatchOrdNo),3),DispatchOrdActualDate=convert(char,DispatchOrdActualDate,120),DispatchOrdReturnDate=convert(char,DispatchOrdReturnDate,120),DispatchOrdTraStreetMileage,DispatchOrdTraEndMileage,DispatchOrdMileage,OrdDistance=convert(money,Replace(Replace(ServiceOrdTraDistance,'公里',''),'km','')),ServiceOrdAreaType,DispatchOrd_NS_Time=convert(char,DispatchOrd_NS_Time,120),TimeLength_Program,TimeLength_ICU,TimeLength_Wait,DispatchOrdPerfomance,EntourageOT_is,EntourageLong_is,EntourageBetimesHH,EntourageState_Time,EntourageMileageFactor,Stretcher_is,StretcherM=isnull((select sum(AddMoney) from (select AddMoney=(AddMoney/COUNT(a.Stretcher_is)) from DispatchOrd_Entourage as a,PaidMoney_Add as b where a.DispatchOrdIDDt=DispatchOrd_Entourage.DispatchOrdIDDt and b.ToDispatchOrdID=a.DispatchOrdIDDt and a.EntourageState<>4 and a.Stretcher_is=1 and b.AddMoneyType=2 GROUP BY AddMoney) as c),0),ServiceOrdPtOutHosp,ServiceOrdTraVia,ServiceOrdPtInHosp,ServiceOrdTraEnd,Condition_is,EntourageOutHosp,EntourageInHosp,EntourageOA_Name,EntourageOA_DepartmentName from DispatchOrd_Entourage,DispatchOrd,ServiceOrder where ServiceOrdID=ServiceOrdIDDt and DispatchOrdID=DispatchOrdIDDt and (ServiceOrdTraDistance like '%公里%' or ServiceOrdTraDistance like '%km%') and DispatchOrdState in (8,9) and EntourageState<>4".$OAOccupationSQL." and DispatchOrd_NS_Time ".$SqlOrdDateType.$searchSql." and ServiceOrdClass in ('".$OrdClassListSql."') order by EntourageOAid,DispatchOrd_NS_Time"; //echo $sql;exit; $sql="select EntourageOAid,EntourageOA_Name=case when EntourageOA_Name is not null then EntourageOA_Name else convert(nvarchar(10),EntourageOAid) end,EntourageP1,DispatchOrdID,ServiceOrdID,DispatchOrdNo=DispatchOrdClass+convert(nvarchar(10),year(DispatchOrd_NS_Time))+right('0'+convert(nvarchar(10),month(DispatchOrd_NS_Time)),2)+right('0'+convert(nvarchar(10),day(DispatchOrd_NS_Time)),2)+'-'+right('00'+convert(nvarchar(10),DispatchOrdNo),3) ,DispatchOrdActualDate=convert(char,DispatchOrdActualDate,120),DispatchOrdReturnDate=convert(char,DispatchOrdReturnDate,120),DispatchOrdTraStreetMileage,DispatchOrdTraEndMileage,DispatchOrdMileage ,OrdDistance=case when PATINDEX('%[^0-9|.|-|+]%',Replace(Replace(ServiceOrdTraDistance,'公里',''),'km',''))=0 then convert(money,Replace(Replace(ServiceOrdTraDistance,'公里',''),'km','')) else 0 end ,ServiceOrdAreaType,DispatchOrd_NS_Time=convert(char,DispatchOrd_NS_Time,120),TimeLength_Program,TimeLength_ICU,TimeLength_Wait,EntourageOT_is,EntourageBetimesHH,EntourageState_Time ,EntourageOA_DepartmentName,EntourageOutHosp,EntourageInHosp ,ConversionDistance,EntourageOT_Money,Stretcher_Money,EntourageMileageFactor,Condition_Money ,ServiceOrdPtOutHosp,ServiceOrdTraVia,ServiceOrdPtInHosp,ServiceOrdTraEnd from DispatchOrd_Entourage,DispatchOrd,ServiceOrder where ServiceOrdID=ServiceOrdIDDt and DispatchOrdID=DispatchOrdIDDt and DispatchOrdState in (8,9) and EntourageState<>4".$OAOccupationSQL." and DispatchOrd_NS_Time ".$SqlOrdDateType.$searchSql." and ServiceOrdClass in ('".$OrdClassListSql."') order by EntourageOAid,DispatchOrd_NS_Time"; $data = sqlsrv_query($conn,$sql); if($data == true){ if (sqlsrv_rows_affected($data)==0) { $SystemMessageType=2; $SystemMessageTXT="数据库中相关无数据!"; $y=0; } else { $i=1; while($rs = sqlsrv_fetch_array( $data, SQLSRV_FETCH_ASSOC) ) { //读取数据 $OA_User_ID = $rs['EntourageOAid']; $OA_Name = $rs['EntourageOA_Name']; $DispatchOrdID = $rs['DispatchOrdID']; $ServiceOrdID = $rs['ServiceOrdID']; $DispatchOrdNo = $rs['DispatchOrdNo']; $ServiceOrdAreaType = $rs['ServiceOrdAreaType']; $DispatchOrdActualDate= $rs['DispatchOrdActualDate']; //出车时间 $DispatchOrdReturnDate= $rs['DispatchOrdReturnDate']; //收车时间 $DispatchOrdTraStreetMileage = $rs['DispatchOrdTraStreetMileage']; //出车 $DispatchOrdTraEndMileage = $rs['DispatchOrdTraEndMileage']; //收车 $DispatchOrdMileage = $rs['DispatchOrdMileage']; //实际公里 $OrdDistance = $rs['OrdDistance']; //预估距离 $TimeLength_Program = $rs['TimeLength_Program']; $TimeLength_ICU = $rs['TimeLength_ICU']; $TimeLength_Wait = $rs['TimeLength_Wait']; $DispatchOrd_NS_Time = $rs['DispatchOrd_NS_Time']; //$DispatchOrdPerfomance = $rs['DispatchOrdPerfomance']; $EntourageOT_is = $rs['EntourageOT_is']; //$EntourageLong_is = $rs['EntourageLong_is']; $EntourageBetimesHH = $rs['EntourageBetimesHH']; //$ServiceOrdPtOutHosp = $rs['ServiceOrdPtOutHosp']; $ServiceOrdTraVia = $rs['ServiceOrdTraVia']; //$ServiceOrdPtInHosp = $rs['ServiceOrdPtInHosp']; $ServiceOrdTraEnd = $rs['ServiceOrdTraEnd']; //$Post_MoneyP = $rs['Post_MoneyP']; //岗位工资 P $DepartmentName = $rs['EntourageOA_DepartmentName'];//所属部门 $EntourageOutHosp = $rs['EntourageOutHosp']; //转出医院 $EntourageInHosp = $rs['EntourageInHosp']; //转出医院 $EntourageP1 = $rs['EntourageP1']; //岗位里程价格 P1 $ConversionDistance = $rs['ConversionDistance'];//折算公里数 L $EntourageMileageFactor = $rs['EntourageMileageFactor']; //折算公里数 L $EntourageOT_Money = $rs['EntourageOT_Money']; //加班费 $Stretcher_Money = $rs['Stretcher_Money']; //担架费 $Condition_Money = $rs['Condition_Money']; //特殊技能 //计算个人系数 //$DepartmentName=OAUser($OA_User_ID,"DepartmentName"); //if ($DepartmentName=="车管中心" and $EntourageMileageFactor==1.2 and $ConversionDistance>250) {$EntourageMileageFactor=1.5;} //$ConversionDistance = $ConversionDistance*$EntourageMileageFactor; //写入数组 for ($row = 0; $row < count($OAUserArray); $row++) { if (array_search($OA_User_ID,$OAUserArray[$row],true)=="OA_User_ID" and array_search($OA_Name,$OAUserArray[$row],true)=="OA_Name"){ if ($ServiceOrdAreaType==1){ //市内 $OAUserArray[$row]["Mileage1"]+=$ConversionDistance; $OAUserArray[$row]["OrdInt1"]++; if (!empty($DispatchOrdReturnDate)){$OAUserArray[$row]["WorkingHours1"]+=round((strtotime($DispatchOrdReturnDate)-strtotime($DispatchOrdActualDate))/60/60,1);} if (!empty($EntourageOT_is)) {$OAUserArray[$row]["OTInt1"]++;} }elseif ($ServiceOrdAreaType==2){ //省内 $OAUserArray[$row]["Mileage2"]+=$ConversionDistance; $OAUserArray[$row]["OrdInt2"]++; if (!empty($DispatchOrdReturnDate)){$OAUserArray[$row]["WorkingHours2"]+=round((strtotime($DispatchOrdReturnDate)-strtotime($DispatchOrdActualDate))/60/60,1);} if (!empty($EntourageOT_is)) {$OAUserArray[$row]["OTInt2"]++;} }elseif ($ServiceOrdAreaType==3){ //省外 $OAUserArray[$row]["Mileage3"]+=$ConversionDistance; $OAUserArray[$row]["OrdInt3"]++; if (!empty($DispatchOrdReturnDate)){$OAUserArray[$row]["WorkingHours3"]+=round((strtotime($DispatchOrdReturnDate)-strtotime($DispatchOrdActualDate))/60/60,1);} if (!empty($EntourageOT_is)) {$OAUserArray[$row]["OTInt3"]++;} }else{ //其它 $OAUserArray[$row]["Mileage4"]+=$ConversionDistance; $OAUserArray[$row]["OrdInt4"]++; if (!empty($DispatchOrdReturnDate)){$OAUserArray[$row]["WorkingHours4"]+=round((strtotime($DispatchOrdReturnDate)-strtotime($DispatchOrdActualDate))/60/60,1);} if (!empty($EntourageOT_is)) {$OAUserArray[$row]["OTInt4"]++;} } //echo $OAUserArray[$row]["Mileage1"];exit; } } $i++; } } } $FormArray=array(); //表头 $FormArray[0]=array("员工姓名|rowspan=\"2\"","所属部门|rowspan=\"2\"","里程数|colspan=\"5\"","单量|colspan=\"5\"","工时|colspan=\"5\"","加班|colspan=\"5\""); $FormArrayX1=count($FormArray[0]); $FormArray[1]=array("市内","省内","省外","其它","合计","市内","省内","省外","其它","合计","市内","省内","省外","其它","合计","市内","省内","省外","其它","合计"); $FormArrayX2=count($FormArray[1]); for ($row = 0; $row < count($OAUserArray); $row++) { $OA_User_ID=$OAUserArray[$row]["OA_User_ID"]; $OA_Name=$OAUserArray[$row]["OA_Name"]; $OA_DepartmentID=$OAUserArray[$row]["OA_DepartmentID"]; $Mileage1=$OAUserArray[$row]["Mileage1"]; $Mileage2=$OAUserArray[$row]["Mileage2"]; $Mileage3=$OAUserArray[$row]["Mileage3"]; $Mileage4=$OAUserArray[$row]["Mileage4"]; $OrdInt1=$OAUserArray[$row]["OrdInt1"]; $OrdInt2=$OAUserArray[$row]["OrdInt2"]; $OrdInt3=$OAUserArray[$row]["OrdInt3"]; $OrdInt4=$OAUserArray[$row]["OrdInt4"]; $WorkingHours1=$OAUserArray[$row]["WorkingHours1"]; $WorkingHours2=$OAUserArray[$row]["WorkingHours2"]; $WorkingHours3=$OAUserArray[$row]["WorkingHours3"]; $WorkingHours4=$OAUserArray[$row]["WorkingHours4"]; $OTInt1=$OAUserArray[$row]["OTInt1"]; $OTInt2=$OAUserArray[$row]["OTInt2"]; $OTInt3=$OAUserArray[$row]["OTInt3"]; $OTInt4=$OAUserArray[$row]["OTInt4"]; $FormArray[$row+2][0]=$OA_Name; $FormArray[$row+2][1]=OAUser($OA_User_ID,"DepartmentName"); $FormArray[$row+2][2]=$Mileage1; $FormArray[$row+2][3]=$Mileage2; $FormArray[$row+2][4]=$Mileage3; $FormArray[$row+2][5]=$Mileage4; $FormArray[$row+2][6]=$Mileage1+$Mileage2+$Mileage3+$Mileage4; $FormArray[$row+2][7]=$OrdInt1; $FormArray[$row+2][8]=$OrdInt2; $FormArray[$row+2][9]=$OrdInt3; $FormArray[$row+2][10]=$OrdInt4; $FormArray[$row+2][11]=$OrdInt1+$OrdInt2+$OrdInt3+$OrdInt4; $FormArray[$row+2][12]=$WorkingHours1; $FormArray[$row+2][13]=$WorkingHours2; $FormArray[$row+2][14]=$WorkingHours3; $FormArray[$row+2][15]=$WorkingHours4; $FormArray[$row+2][16]=$WorkingHours1+$WorkingHours2+$WorkingHours3+$WorkingHours4; $FormArray[$row+2][17]=$OTInt1; $FormArray[$row+2][18]=$OTInt2; $FormArray[$row+2][19]=$OTInt3; $FormArray[$row+2][20]=$OTInt4; $FormArray[$row+2][21]=$OTInt1+$OTInt2+$OTInt3+$OTInt4; } if ($is_CSV==2) { echo json_encode($FormArray); } //echo print_r($FormArray); //exit; ?>