<?php
|
$UnitNameID=empty($_REQUEST['UnitNameID'])!=false ? "" : addslashes($_REQUEST['UnitNameID']);
|
|
//分页信息
|
$page=empty($_REQUEST['page'])!=false ? 1 : $_REQUEST['page'];
|
$QuantityInt = 20; //每页显示数量
|
$acc1=$page; //当前页数
|
$acc2=0; //总页数
|
$acc3=0; //总记录数
|
|
//类型列表
|
$ArrayOrdType=array();
|
$sql = "select vID,vtext from dictionary where vtitle='ServiceOrderType' order by vOrder" ;
|
$data = sqlsrv_query($conn,$sql);
|
if($data == true){
|
$i=0;
|
while($rs = sqlsrv_fetch_array( $data, SQLSRV_FETCH_ASSOC) ) {
|
$ArrayOrdType[$rs['vID']]=$rs['vtext'];
|
$i++;
|
}
|
}
|
|
//查看各分公司报表权限
|
if (isDepartment("070109")==1) {
|
$OrdClassListSql="";
|
//$UnitNameID="1";
|
If ($UnitNameID!="" and $UnitNameID!="0") {
|
$sql="select vOrder2 from dictionary where vtitle='UnitName' and vID=".$UnitNameID;
|
$data = sqlsrv_query($conn,$sql);
|
if($data == true){
|
if (sqlsrv_rows_affected($data)!=0) {
|
while($rs = sqlsrv_fetch_array($data) ) {
|
$UnitTypeID = $rs[0];
|
$sql="select vID,vtext,vOrder2 from dictionary where vID in (".$UnitTypeID.") and vtitle='OrderClass' order by vOrder";
|
$data1 = sqlsrv_query($conn,$sql);
|
if($data1 == true){
|
while($rs1 = sqlsrv_fetch_array($data1) ) {
|
$OrderClassID = $rs1['vOrder2'];
|
$OrdClassListSql .= ",".$OrderClassID;
|
}
|
}
|
$OrdClassListSql=substr($OrdClassListSql,1);
|
$OrdClassListSql=str_replace(",","','",$OrdClassListSql);
|
}
|
}
|
}
|
}else{
|
$sql="select vID,vtext,vOrder2 from dictionary where vType in (1,2) and vtitle='OrderClass' order by vOrder";
|
$data1 = sqlsrv_query($conn,$sql);
|
if($data1 == true){
|
while($rs1 = sqlsrv_fetch_array($data1) ) {
|
$OrderClassID = $rs1['vOrder2'];
|
$OrdClassListSql .= ",".$OrderClassID;
|
}
|
}
|
$OrdClassListSql=substr($OrdClassListSql,1);
|
$OrdClassListSql=str_replace(",","','",$OrdClassListSql);
|
}
|
}
|
|
//查询条件
|
$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="↓";
|
}
|
|
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;
|
?>
|
