<?php
|
$is_CSV=empty($_REQUEST['is_CSV'])!=false ? $is_CSV : $_REQUEST['is_CSV']; //输出模式
|
//按json方式输出
|
if ($is_CSV==2) {
|
require '/inc/odbc.php';
|
require '/inc/function.php';
|
$UnitNameID=empty($_REQUEST['UnitNameID'])!=false ? "" : addslashes($_REQUEST['UnitNameID']);
|
$CoopUnitID=empty($_REQUEST['CoopUnitID'])!=false ? "" : addslashes($_REQUEST['CoopUnitID']);
|
|
$searchTXT=empty($_REQUEST['searchTXT'])!=false ? "" : addslashes($_REQUEST['searchTXT']);
|
$orderby=empty($_REQUEST['orderby'])!=false ? "" : addslashes($_REQUEST['orderby']);
|
|
$OrdDateType=empty($_REQUEST['OrdDateType'])!=false ? "0" : addslashes($_REQUEST['OrdDateType']);
|
$OrdDateStart=empty($_REQUEST['OrdDateStart'])!=false ? "" : addslashes($_REQUEST['OrdDateStart']);
|
$OrdDateEnd=empty($_REQUEST['OrdDateEnd'])!=false ? "" : addslashes($_REQUEST['OrdDateEnd']);
|
$OrdType=empty($_REQUEST['OrdType'])!=false ? "1" : addslashes($_REQUEST['OrdType']);
|
$OrdClassType=empty($_REQUEST['OrdClassType'])!=false ? "0" : addslashes($_REQUEST['OrdClassType']);
|
$OrderLevel=empty($_REQUEST['OrderLevel'])!=false ? "0" : addslashes($_REQUEST['OrderLevel']);
|
|
$draw=empty($_REQUEST['draw'])!=false ? "1" : addslashes($_REQUEST['draw']);
|
//默认显示字段
|
$Report_Name = "调度单明细汇总";
|
if ($OrdDateType=="0" and $OrdDateStart=="" and $OrdDateEnd=="") {
|
$OrdDateType=4;
|
$OrdDateTop=" top 20 ";
|
}
|
else {
|
$OrdDateTop="";
|
}
|
$OrdDateTypeName="";
|
if ($OrdDateType=="0") {
|
if ($OrdDateStart=="" and $OrdDateEnd=="") {
|
$OrdDateStart=date("Y-m-d");
|
$OrdDateEnd=date("Y-m-d");
|
}
|
elseif ($OrdDateStart!="" and $OrdDateEnd=="") {
|
$OrdDateEnd=$OrdDateStart;
|
}
|
elseif ($OrdDateStart=="" and $OrdDateEnd!="") {
|
$OrdDateStart=$OrdDateEnd;
|
}
|
If ($OrdDateStart>$OrdDateEnd) {
|
$OrdDateStart1=$OrdDateStart;
|
$OrdDateStart=$OrdDateEnd;
|
$OrdDateEnd=$OrdDateStart1;
|
}
|
If ($OrdDateStart==$OrdDateEnd) {
|
$OrdDateTypeName=$OrdDateStart;
|
$OrdDateTypeName1=$OrdDateTypeName;
|
}
|
Else {
|
$OrdDateTypeName=$OrdDateStart." 至 ".$OrdDateEnd;
|
$OrdDateTypeName1="时间段 ".$OrdDateTypeName;
|
}
|
$SqlOrdDateType=" between '".$OrdDateStart."' and '".$OrdDateEnd." 23:59:59'";
|
}
|
Else {
|
$sql = "select top 1 vID,vtext,vOrder2 from dictionary where vType>=1 and vtitle='OrdDateType' and vID=".$OrdDateType ;
|
$data = sqlsrv_query($conn,$sql);
|
if($data == true){
|
if (sqlsrv_rows_affected($data)!=0) {
|
while($rs = sqlsrv_fetch_array( $data, SQLSRV_FETCH_ASSOC) ) {
|
$OrdDateTypeName = $rs['vtext'];
|
$OrdDateTypeName1 = $OrdDateTypeName;
|
$SqlOrdDateType = $rs['vOrder2'];
|
}
|
} else {
|
$OrdDateType="4";
|
$OrdDateTypeName="本月";
|
$OrdDateTypeName1=$OrdDateTypeName;
|
$SqlOrdDateType="between DATEADD(mm,DATEDIFF(mm,0,getdate()),0) and dateadd(ms,-3,DATEADD(mm,DATEDIFF(m,0,getdate())+1,0))";
|
|
}
|
}
|
|
}
|
}
|
//分页信息
|
$page=empty($_REQUEST['page'])!=false ? 1 : $_REQUEST['page']; //当前页数
|
$page=empty($draw)!=false ? $page : $draw; //当前页数
|
$page_size = 20; //每页显示数量
|
$total_page=0; //总页数
|
$total_records=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++;
|
}
|
}
|
|
//电话查询
|
function findThePhoneNumbers($oldStr = ""){
|
// 检测字符串是否为空
|
$oldStr=trim("q{$oldStr}q");
|
$numbers = array();
|
if(empty($oldStr)){
|
return $numbers;
|
}
|
$strArr = explode("-", $oldStr);
|
$newStr = $strArr[0];
|
for ($i=1; $i < count($strArr); $i++) {
|
if (preg_match("/\d{2}$/", $newStr) && preg_match("/^\d{11}/", $strArr[$i])){
|
$newStr .= $strArr[$i];
|
} elseif (preg_match("/\d{3,4}$/", $newStr) && preg_match("/^\d{7,8}/", $strArr[$i])) {
|
$newStr .= $strArr[$i];
|
} else {
|
$newStr .= "-".$strArr[$i];
|
}
|
}
|
// 手机号的获取
|
$reg='/\D(?:86)?(\d{11})\D/is';//匹配数字的正则表达式
|
preg_match_all($reg,$newStr,$result);
|
$nums = array();
|
$common = '/^1[3-9]\d{9}$/';
|
foreach ($result[1] as $key => $value) {
|
if(preg_match($common,$value)){
|
$nums[] = $value;
|
}
|
}
|
// 固话的获取
|
$reg='/\D(?:86)?(\d{11})\D/is';//匹配数字的正则表达式
|
preg_match_all($reg,$newStr,$result);
|
$common = '/^020\d{8}$/';
|
foreach ($result[1] as $key => $value) {
|
if(preg_match($common,$value)){
|
$nums[] = $value;
|
}
|
}
|
// 返回最终数组
|
return $nums;
|
}
|
|
//查询条件
|
$searchSql="";
|
$searchTXT=empty($searchTXT)!=false ? "" : $searchTXT;
|
if ($searchTXT!=""){
|
If (substr($searchTXT,0,4)=="out|") {
|
$searchSql=" and (ServiceOrdPtOutHosp in (select vid from dictionary where vType=1 and vtitle='HospName' and vtext='".substr($searchTXT,4)."')) ";
|
} elseif (substr($searchTXT,0,3)=="in|") {
|
$searchSql=" and (ServiceOrdPtInHosp in (select vid from dictionary where vType=1 and vtitle='HospName' and vtext='".substr($searchTXT,3)."')) ";
|
} elseif (substr($searchTXT,0,5)=="hosp|") {
|
$searchSql=" and (ServiceOrdPtOutHosp in (select vid from dictionary where vType=1 and vtitle='HospName' and vtext='".substr($searchTXT,5)."') or ServiceOrdPtInHosp in (select vid from dictionary where vType=1 and vtitle='HospName' and vtext='".substr($searchTXT,5)."'))";
|
} elseif (substr($searchTXT,0,6)=="staff|") {
|
$searchSql=" and (DispatchOrdID in (select DispatchOrdIDDt from DispatchOrd_Entourage where EntourageState=3 and EntourageOAid=".substr($searchTXT,6).")) ";
|
} elseif (substr($searchTXT,0,7)=="staff1|") {
|
$searchSql=" and (DispatchOrdID in (select DispatchOrdIDDt from DispatchOrd_Entourage where EntourageState=3 and EntourageOAid=".substr($searchTXT,7).")) and DispatchOrdState in (8,9) ";
|
} elseif (substr($searchTXT,0,8)=="OrdType|") {
|
$searchSql=" and (ServiceOrdIDDt in (select ServiceOrdID from ServiceOrder where ServiceOrdState=3 and ServiceOrdType=".substr($searchTXT,8).")) and DispatchOrdState not in (10) ";
|
} else {
|
$searchSql=" and (ServiceOrdCoName like '%".$searchTXT."%' or ServiceOrdCoPhone like '%".$searchTXT."%' or ServiceOrdPtName like '%".$searchTXT."%' or ServiceOrdTraStreet like '%".$searchTXT."%' or ServiceOrdTraEnd like '%".$searchTXT."%' or ServiceOrdPtOutHosp in (select vid from dictionary where vType=1 and vtitle='HospName' and vtext like '%".$searchTXT."%')) ";
|
}
|
}else{
|
$searchSql="";
|
}
|
|
if ($OrdClassType=="-1"){
|
$searchSql=$searchSql." and ServiceOrdType in (select vID from dictionary where vType=1 and vtitle='ServiceOrderType') ";
|
}elseif ($OrdClassType=="-2"){
|
$searchSql=$searchSql." and ServiceOrdType in (select vID from dictionary where vType=2 and vtitle='ServiceOrderType') ";
|
}elseif (!empty($OrdClassType)){
|
$searchSql=$searchSql." and ServiceOrdType in (".$OrdClassType.") ";
|
}
|
|
if (!empty($OrderLevel)){
|
$searchSql=$searchSql." and OrderLevel=".($OrderLevel-1)." ";
|
}
|
|
$orderby=empty($orderby)!=false ? "" : $orderby;
|
If (strrpos($orderby," desc")>1){
|
$orderbydesc="";
|
$descTXT="↓";
|
}else{
|
$orderbydesc=" desc";
|
$descTXT="↑";
|
}
|
|
If ($orderby=="") {
|
$orderby="OutHosp|InHosp desc,OutHospPrice|InHospPrice desc";
|
$descTXT="↓";
|
}
|
//echo $OrdClassListSql;
|
|
//查询开始
|
$SqlOrdDateType=empty($SqlOrdDateType)!=false ? "" : $SqlOrdDateType;
|
$OrdType=empty($OrdType)!=false ? "1" : $OrdType;
|
//按调度单统计
|
//SQL查询条件
|
$sqlWhere=" from DispatchOrd,ServiceOrder LEFT JOIN HospData as hospA on hospA.HospID=ServiceOrdPtOutHospID LEFT JOIN HospData as HospB on HospB.HospID=ServiceOrdPtInHospID where ServiceOrdIDDt=ServiceOrdID and DispatchOrdState not in (10) and DispatchOrd_NS_Time ".$SqlOrdDateType.$searchSql." and ServiceOrdType in (select vID from dictionary where vType=2 and vtitle='ServiceOrderType')";
|
//组成计算总页数SQL
|
$total_sql = "select DispatchOrdID $sqlWhere";
|
//组成显示结果SQL
|
$Result_sql = "select ServiceOrdID,DispatchOrdID,ServiceOrdType
|
,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)
|
,DispatchOrdState,DispatchOrd_NS_Date=CONVERT(nvarchar(20),DispatchOrd_NS_Time, 120),ServiceOrdCoName,ServiceOrdCoPhone
|
,OrdTraPrice=(case when isnull(ServiceOrdTraTxnPrice,0)=0 or DispatchOrdPerfomance=0 then ServiceOrdTraTxnPrice else round(ServiceOrdTraTxnPrice/((select SUM(DispatchOrdPerfomance) from DispatchOrd where ServiceOrdIDDt=ServiceOrdID and DispatchOrdState not in (10))/DispatchOrdPerfomance),2,1) END)+StretcherMoney
|
,ServiceOrdPtOutHospID,ServiceOrdPtInHospID,ServiceOrdPtServicesID,ServiceOrdPtInServicesID
|
,DispatchOrdCarID,DispatchOrd_NS_Time,ServiceOrdPtServices,ServiceOrdTraStreet,ServiceOrdTraVia,ServiceOrdTraEnd,DispatchOrd_OAName,HospName=hospA.HospName,HospNameIn=hospB.HospName,ServiceOrdTaskRemarks
|
,row_number() over (order by DispatchOrd_NS_Time desc) n $sqlWhere";
|
//附加结果SQL
|
$Additional_sql = ",DispatchOrdStateTXT=(select vtext from dictionary where vtitle='DispatchOrdState' and vID=DispatchOrdState),EntourageName=DispatchOrd_OAName";
|
//$sql=",isRespirator=(select count(id) from ServiceOrd_Condition where ConditionID=4 and ServiceOrdCoID=ServiceOrdID)"
|
//echo $total_sql;exit;
|
if ($is_CSV!=1){
|
//计算总页数
|
$data = sqlsrv_query( $conn, $total_sql, array(), array( "Scrollable" => 'keyset' ));
|
$total_records=sqlsrv_num_rows($data); //总行数
|
$total_page=ceil($total_records/$page_size); //总页数
|
//避免page小于0或大于总页数
|
$page=$page<=0?1:$page;
|
$page=$page>$total_page?$total_page:$page;
|
//计算开始和结束行数
|
$pageStart = ($page-1)*$page_size;
|
$pageEnd = $pageStart+$page_size;
|
$sqlPage = " where sqlData.n > $pageStart and sqlData.n <= $pageEnd " ;
|
}else{
|
$sqlPage="" ;
|
}
|
$sql = "select * $Additional_sql from ($Result_sql) sqlData ".$sqlPage." order by sqlData.n" ;
|
//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) ) {
|
//读取数据
|
|
//读取数据--End
|
|
//写入数组
|
$FormArray[$i][0]=$i+(($page-1)*$page_size);
|
$FormArray[$i][1]="<a href='DispatchOrder.gds?DispatchOrdID=".$rs['DispatchOrdID']."&OrdDateType=4&OrdClassList=&h_menu1_1=1' target='_blank'>".$rs['DispatchOrdNo']."</a>";
|
$FormArray[$i][2]=$ArrayOrdType[$rs['ServiceOrdType']];
|
$FormArray[$i][3]=$rs['DispatchOrdStateTXT'];
|
$FormArray[$i][4]=date("Y/m/d",strtotime($rs['DispatchOrd_NS_Date']));
|
$FormArray[$i][5]=CarDataA($rs['DispatchOrdCarID'],'','CarLicense');
|
$FormArray[$i][6]=$rs['EntourageName'];
|
$TraStreet=$rs["ServiceOrdTraStreet"];
|
$HospName=$rs['ServiceOrdTraVia'];
|
if ($TraStreet != $HospName) {
|
$FormArray[$i][7]=$TraStreet;
|
}else{
|
$FormArray[$i][7]='';
|
}
|
$FormArray[$i][8]=$HospName;
|
$HospNameIn=$rs['ServiceOrdTraEnd'];
|
$FormArray[$i][9]=$HospNameIn;
|
$OrdCoName="--";
|
if (strpos($rs['ServiceOrdTaskRemarks'],"社区人员：")>0) {
|
$OrdCoName=mb_strcut($rs['ServiceOrdTaskRemarks'],strpos($rs['ServiceOrdTaskRemarks'],"社区人员：")+16,28,"utf-8");
|
preg_match_all("/[\x{4e00}-\x{9fff}]+/u","$OrdCoName",$arr);
|
$OrdCoName=join('',$arr[0]);
|
}elseif (strpos($rs['ServiceOrdTaskRemarks'],"对接人：")>0) {
|
$OrdCoName=mb_strcut($rs['ServiceOrdTaskRemarks'],strpos($rs['ServiceOrdTaskRemarks'],"对接人：")+14,12,"utf-8");
|
preg_match_all("/[\x{4e00}-\x{9fff}]+/u","$OrdCoName",$arr);
|
$OrdCoName=join('',$arr[0]);
|
}elseif (strpos($rs['ServiceOrdTaskRemarks'],"联系人：")>0) {
|
$OrdCoName=mb_strcut($rs['ServiceOrdTaskRemarks'],strpos($rs['ServiceOrdTaskRemarks'],"联系人：")+14,12,"utf-8");
|
preg_match_all("/[\x{4e00}-\x{9fff}]+/u","$OrdCoName",$arr);
|
$OrdCoName=join('',$arr[0]);
|
}elseif (strpos($rs['ServiceOrdTaskRemarks'],"联系人员：")>0) {
|
$OrdCoName=mb_strcut($rs['ServiceOrdTaskRemarks'],strpos($rs['ServiceOrdTaskRemarks'],"联系人员：")+16,12,"utf-8");
|
preg_match_all("/[\x{4e00}-\x{9fff}]+/u","$OrdCoName",$arr);
|
$OrdCoName=join('',$arr[0]);
|
}elseif (strpos($rs['ServiceOrdTaskRemarks'],"社区")>0) {
|
$OrdCoName=mb_strcut($rs['ServiceOrdTaskRemarks'],strpos($rs['ServiceOrdTaskRemarks'],"社区")+8,28,"utf-8");
|
preg_match_all("/[\x{4e00}-\x{9fff}]+/u","$OrdCoName",$arr);
|
$OrdCoName=join('',$arr[0]);
|
}
|
if ($OrdCoName!="--") {$OrdCoName=str_ireplace("电话","",$OrdCoName);}
|
$FormArray[$i][10]=$OrdCoName;
|
$OrdCoPhoneArray=findThePhoneNumbers($rs['ServiceOrdTaskRemarks']);
|
$OrdCoPhone="--";
|
if (count($OrdCoPhoneArray)>0) {$OrdCoPhone=$OrdCoPhoneArray[count($OrdCoPhoneArray)-1];}
|
$FormArray[$i][11]=$OrdCoPhone;
|
$PeopleNumber="--";
|
if (strpos($rs['ServiceOrdTaskRemarks'],"【高危密接】")>0) {
|
$PeopleNumber=mb_strcut($rs['ServiceOrdTaskRemarks'],strpos($rs['ServiceOrdTaskRemarks'],"【高危密接】"),25,"utf-8");
|
$PeopleNumber=preg_replace('/\D/s', '', $PeopleNumber);
|
}elseif (strpos($rs['ServiceOrdTaskRemarks'],"人数")>0) {
|
$PeopleNumber=mb_strcut($rs['ServiceOrdTaskRemarks'],strpos($rs['ServiceOrdTaskRemarks'],"人数"),10,"utf-8");
|
$PeopleNumber=preg_replace('/\D/s', '', $PeopleNumber);
|
}elseif (strpos($rs['ServiceOrdTaskRemarks'],"转运人员")>0) {
|
$PeopleNumber=mb_strcut($rs['ServiceOrdTaskRemarks'],strpos($rs['ServiceOrdTaskRemarks'],"转运人员"),20,"utf-8");
|
$PeopleNumber=preg_replace('/\D/s', '', $PeopleNumber);
|
}elseif (strpos($rs['ServiceOrdTaskRemarks'],"密接人员")>0) {
|
$PeopleNumber=mb_strcut($rs['ServiceOrdTaskRemarks'],strpos($rs['ServiceOrdTaskRemarks'],"密接人员"),20,"utf-8");
|
$PeopleNumber=preg_replace('/\D/s', '', $PeopleNumber);
|
}elseif (strpos($rs['ServiceOrdTaskRemarks'],"几人")>0) {
|
$PeopleNumber=mb_strcut($rs['ServiceOrdTaskRemarks'],strpos($rs['ServiceOrdTaskRemarks'],"几人"),10,"utf-8");
|
$PeopleNumber=preg_replace('/\D/s', '', $PeopleNumber);
|
}
|
$FormArray[$i][12]=$PeopleNumber;
|
$FormArray[$i][13]=round($rs['OrdTraPrice'],2);
|
$FormArray[$i][14]=$rs['ServiceOrdTaskRemarks'];
|
$i++;
|
//写入数组--End
|
}
|
|
}
|
}
|
|
//组成Json数据
|
if ($is_CSV==2) {
|
$JsonArray=array();
|
for($y=1;$y<count($FormArray);$y++) {
|
$JsonArray[($y-1)]=$FormArray[$y];
|
}
|
|
echo json_encode(array(
|
"draw" => intval($page),
|
"recordsTotal" => intval($total_records),
|
"recordsFiltered" => intval($total_records),
|
"data" => $JsonArray
|
),JSON_UNESCAPED_UNICODE);
|
//echo "{\"data\":".json_encode($JsonArray)."}";
|
}
|
//echo print_r($FormArray);
|
//exit;
|
?>
|
