<?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; //总记录数
|
|
//区域列表
|
$ArrayOrdAreaType=array();
|
$sql = "select vID,vtext from dictionary where vtitle='ServiceOrdAreaType' order by vOrder" ;
|
$data = sqlsrv_query($conn,$sql);
|
if($data == true){
|
$i=0;
|
while($rs = sqlsrv_fetch_array( $data, SQLSRV_FETCH_ASSOC) ) {
|
$ArrayOrdAreaType[$rs['vID']]=$rs['vtext'];
|
$i++;
|
}
|
}
|
|
//类型列表
|
$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 (EntourageOA_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,EntourageOAid";
|
$descTXT="↓";
|
}
|
|
if ($OrdDateType=="0" and $OrdDateStart=="" and $OrdDateEnd=="") {$OrdDateType=4;}
|
|
//查询开始
|
$SqlOrdDateType=empty($SqlOrdDateType)!=false ? "" : $SqlOrdDateType;
|
$OAOccupationSQL=empty($OAOccupation)!=false ? "" : " and EntourageID in (".$OAOccupation.") ";
|
$sql="select EntourageOAid,EntourageOA_Name,ServiceOrdAreaType,ServiceOrdType,SumOrd=count(DispatchOrdID),SumWorkingHours=sum(datediff(MINUTE,DispatchOrdActualDate,DispatchOrdReturnDate)),SumMileage=sum(DispatchOrdMileage)
|
,SumHotel=(select count(h.id) from DispatchOrd_Hotel as h,DispatchOrd as a,ServiceOrder as b,DispatchOrd_Entourage as e where b.ServiceOrdID=a.ServiceOrdIDDt and b.ServiceOrdType=ServiceOrder.ServiceOrdType and a.DispatchOrdID=h.DispatchOrdIDDt and a.DispatchOrdID=e.DispatchOrdIDDt and e.EntourageOAid=DispatchOrd_Entourage.EntourageOAid and DispatchOrd_NS_Time ".$SqlOrdDateType.")
|
,SumOrdDistance=sum(case when convert(money,Replace(Replace(ServiceOrdTraDistance,'公里',''),'km',''))>0 then convert(money,Replace(Replace(ServiceOrdTraDistance,'公里',''),'km','')) else 0 end)
|
,SumConversionDistance=SUM(ConversionDistance)
|
,SumOT=sum(EntourageOT_is)
|
,SumBetimes=sum(case when EntourageBetimesHH>0 then 1 else 0 end)
|
from ServiceOrder,DispatchOrd,DispatchOrd_Entourage where ServiceOrdID=ServiceOrdIDDt and DispatchOrdReturnDate is not null and DispatchOrdClass in ('".$OrdClassListSql."') and DispatchOrdIDDt=DispatchOrdID and EntourageState<>4 and DispatchOrd_NS_Time ".$SqlOrdDateType.$searchSql." GROUP BY EntourageOAid,EntourageOA_Name,ServiceOrdAreaType,ServiceOrdType order by EntourageOA_Name,ServiceOrdType";
|
//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=1;
|
while($rs = sqlsrv_fetch_array( $data, SQLSRV_FETCH_ASSOC) ) {
|
//读取数据
|
$EntourageOAid = $rs['EntourageOAid'];
|
|
$EntourageOA_Name = $rs['EntourageOA_Name'];
|
$ServiceOrdAreaType = $rs['ServiceOrdAreaType'];
|
$ServiceOrdType = $rs['ServiceOrdType'];
|
$SumOrd = $rs['SumOrd'];
|
$SumWorkingHours = $rs['SumWorkingHours'];
|
$SumMileage = $rs['SumMileage'];
|
$SumHotel = $rs['SumHotel'];
|
$SumOT = $rs['SumOT'];
|
$SumBetimes = $rs['SumBetimes'];
|
$SumOrdDistance = $rs['SumOrdDistance'];
|
$SumConversionDistance=$rs['SumConversionDistance'];
|
|
|
//读取数据--End
|
|
|
|
//写入数组
|
$FormArray[$i][0]=$EntourageOA_Name;
|
$FormArray[$i][1]=$ArrayOrdAreaType[$ServiceOrdAreaType];
|
$FormArray[$i][2]=$ArrayOrdType[$ServiceOrdType];
|
$FormArray[$i][3]=$SumOrd;
|
$FormArray[$i][4]=round($SumWorkingHours/60,1);
|
$FormArray[$i][5]=round($SumOrdDistance,0);
|
$FormArray[$i][6]=round($SumMileage,0);
|
$FormArray[$i][7]=round($SumConversionDistance,0);
|
$FormArray[$i][8]=$SumHotel;
|
$FormArray[$i][9]=$SumBetimes;
|
$FormArray[$i][10]=$SumOT;
|
$i++;
|
|
}
|
|
|
}
|
}
|
|
if ($is_CSV==2) {
|
echo json_encode($FormArray);
|
}
|
//echo print_r($FormArray);
|
//exit;
|
?>
|
