<?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++;
|
}
|
}
|
|
//FromHQ2_is 支援总部类型列表
|
$FromHQ2Type=array();
|
$sql="select vID,vtext from dictionary where vType=1 and vtitle='FromHQ2_is' order by vOrder";
|
$data = sqlsrv_query($conn,$sql);
|
if($data == true){
|
$i=0;
|
while($rs = sqlsrv_fetch_array( $data,SQLSRV_FETCH_ASSOC) ) {
|
$FromHQ2Type[$rs['vID']]=$rs['vtext'];
|
$i++;
|
}
|
}
|
//$OrdClassListSql="";
|
//查看各分公司报表权限
|
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);
|
}
|
}
|
//敏感信息权限
|
if (isDepartment("020109")==1) {
|
$is_Privacy="YES";
|
}else{
|
$is_Privacy="NO";
|
}
|
|
//合作单位
|
If ($CoopUnitID!="" and $CoopUnitID!="0") {
|
If (substr($CoopUnitID,0,2)=="0,") {$CoopUnitID=substr($CoopUnitID,2);}
|
$CoopUnitSql=" And (ServiceOrdUnitID in (".$CoopUnitID.")";
|
$sql="select HospID,HospIntroducerDate=CONVERT(nvarchar(20),HospIntroducerDate, 111) from HospData,IntroducerData where HospIntroducerID=IntroducerID and IntroducerUnitID in (".$CoopUnitID.") GROUP BY HospID,HospIntroducerDate order by HospIntroducerDate";
|
$data = sqlsrv_query($conn,$sql);
|
if($data == true){
|
if (sqlsrv_rows_affected($data)!=0) {
|
$UnitHospIDSP=array();
|
$UnitHospDateOld="";
|
$UnitHospDateSP=array();
|
$H=-1;
|
while($rs = sqlsrv_fetch_array($data) ) {
|
If ($UnitHospDateOld!=$rs['HospIntroducerDate']) {
|
array_push($UnitHospIDSP,$rs['HospID']);
|
array_push($UnitHospDateSP,$rs['HospIntroducerDate']);
|
$UnitHospDateOld=$rs['HospIntroducerDate'];
|
$H++;
|
}else{
|
$UnitHospIDSP[$H]=$UnitHospIDSP[$H].",".$rs['HospID'];
|
}
|
}
|
for($x=0;$x<count($UnitHospIDSP);$x++) {
|
$CoopUnitSql=$CoopUnitSql." or (ServiceOrdPtOutHospID in (".$UnitHospIDSP[$x].") and ServiceOrd_CC_Time>='".$UnitHospDateSP[$x]."')";
|
}
|
}
|
}
|
$CoopUnitSql=$CoopUnitSql.")";
|
}Else{
|
$CoopUnitSql="";
|
}
|
|
//查询条件
|
$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;
|
If ($OrdType=="2") {
|
//按服务单统计
|
//SQL查询条件
|
$sqlWhere=" from ServiceOrder LEFT JOIN HospData as hospA on hospA.HospID=ServiceOrdPtOutHospID LEFT JOIN HospData as HospB on HospB.HospID=ServiceOrdPtInHospID LEFT JOIN DispatchOrd on ServiceOrdIDDt=ServiceOrdID and DispatchOrdState<>10 where ServiceOrd_CC_Time ".$SqlOrdDateType.$CoopUnitSql.$searchSql." and ServiceOrdClass in ('".$OrdClassListSql."') and ((ServiceOrdCancelReason<>10 or ServiceOrdCancelReason is null) and (ServiceOrdCancelReasonTXT<>'自动取消' or ServiceOrdCancelReasonTXT is null)) and (ServiceOrdOperationRemarks<>'[客户未正式提交需求，可不联系]' or ServiceOrdOperationRemarks is null) ";
|
//组成计算总页数SQL
|
$total_sql = "select ServiceOrdID $sqlWhere";
|
//组成显示结果SQL
|
//$Result_sql = "select ServiceOrdID,OrderLevel,ServiceOrdType,ServiceOrdClass,ServiceOrdDepartureType,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),ServiceOrdNo=ServiceOrdClass+convert(nvarchar(10),year(ServiceOrd_CC_Time))+right('0'+convert(nvarchar(10),month(ServiceOrd_CC_Time)),2)+right('0'+convert(nvarchar(10),day(ServiceOrd_CC_Time)),2)+'-'+right('00'+convert(nvarchar(10),ServiceOrdNo),3),ServiceOrd_CC_Date=CONVERT(nvarchar(20),ServiceOrd_CC_Time, 120) ,ServiceOrdCoName,ServiceOrdCoPhone,ServiceOrdIntroducer,OrdTraPrice=ServiceOrdTraTxnPrice+isnull(StretcherMoney,0),ServiceOrdState,ServiceOrd_Sale_ID,ServiceOrd_CC_ID,DispatchOrd_NS_ID,ServiceOrdSource,ServiceOrdPtOutHospID,ServiceOrdPtInHospID,ServiceOrdPtServicesID,ServiceOrdPtInServicesID,AMV_is,ServiceOrdPtName,ServiceOrdTraDistance,DispatchOrdCarID='--',FromHQ_is,row_number() over (order by ServiceOrd_CC_Time) n $sqlWhere";
|
|
$Result_sql = "select ServiceOrdID,DispatchOrdID,OrderLevel,ServiceOrdAreaType,ServiceOrdType,ServiceOrdClass,ServiceOrdDepartureType
|
,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)
|
,ServiceOrdNo=ServiceOrdClass+convert(nvarchar(10),year(ServiceOrd_CC_Time))+right('0'+convert(nvarchar(10),month(ServiceOrd_CC_Time)),2)+right('0'+convert(nvarchar(10),day(ServiceOrd_CC_Time)),2)+'-'+right('00'+convert(nvarchar(10),ServiceOrdNo),3)
|
,ServiceOrd_CC_Date=CONVERT(nvarchar(20),ServiceOrd_CC_Time, 120)
|
,DispatchOrdState,DispatchOrd_NS_Date=CONVERT(nvarchar(20),DispatchOrd_NS_Time, 120),ServiceOrdCoName,ServiceOrdCoPhone,ServiceOrdIntroducer
|
,OrdTraPrice=ServiceOrdTraTxnPrice+isnull(StretcherMoney,0),ServiceOrdState
|
,ServiceOrd_Sale_ID,ServiceOrd_CC_ID,DispatchOrd_NS_ID,ServiceOrdSource,ServiceOrdPtOutHospID,ServiceOrdPtInHospID,ServiceOrdPtServicesID,ServiceOrdPtInServicesID,AMV_is
|
,ServiceOrdTraDistance,DispatchOrdCarID,DispatchOrd_NS_Time,DispatchOrdActualDate,DispatchOrd_OAName,DispatchOrd_OAEntourage,FromHQ_is,FromHQ2_is,ServiceOrdPtName,ServiceOrdPtServices,ServiceOrdTraVia,ServiceOrdTraEnd
|
,HopsProvince=hospA.HopsProvince,HopsCity=hospA.HopsCity,HopsArea=hospA.HopsArea,HospName=hospA.HospName
|
,HopsProvinceIn=hospB.HopsProvince,HopsCityIn=hospB.HopsCity,HopsAreaIn=hospB.HopsArea,HospNameIn=hospB.HospName,ServiceOrdTaskRemarks
|
,ServiceOrd_work_ID,ServiceOrd_work_IDs,ServiceOrd_work_is,CommissionScenarioID
|
,row_number() over (order by ServiceOrd_CC_Time desc) n $sqlWhere";
|
//附加结果SQL
|
//$Additional_sql = ",ServiceOrderStateTXT=(select vtext from dictionary where vtitle='ServiceOrderState' and vID=ServiceOrdState),ServiceOrd_SaleName=(select OA_Name from OA_User where OA_User_ID=ServiceOrd_Sale_ID),ServiceOrd_CCName=(select OA_Name from OA_User where OA_User_ID=ServiceOrd_CC_ID),DispatchOrd_NSName=(select OA_Name from OA_User where OA_User_ID=DispatchOrd_NS_ID),ServiceOrdSource=(select vtext from dictionary where vtitle='OrdSource' and vID=ServiceOrdSource) ,HospNameA=(select HospName from HospData where HospID=ServiceOrdPtOutHospID),HospNameB=(select HospName from HospData where HospID=ServiceOrdPtInHospID),PaidType=(SELECT STUFF(( select ',' + vtext from PaidMoney,dictionary where vtitle='PaidMoneyType' and vType>=1 and vID=PaidMoneyType and PaidMoney<>0 and ServiceOrdIDDt=ServiceOrdID GROUP BY vtext FOR XML PATH('')), 1, 1, '') AS PaidType),HospDepartmentName=isnull((select top 1 vtext from dictionary where vtitle='HospitalDepartment' and ServiceOrdPtServicesID=vID),''),HospDepartmentInName=isnull((select top 1 vtext from dictionary where vtitle='HospitalDepartment' and ServiceOrdPtInServicesID=vID),''),isRespirator=AMV_is,EntourageName='--',MessageCountInt=FromHQ_is,Stime2='--'";
|
|
$Additional_sql = ",ServiceOrderStateTXT=(select vtext from dictionary where vtitle='ServiceOrderState' and vID=ServiceOrdState),DispatchOrdStateTXT=(select vtext from dictionary where vtitle='DispatchOrdState' and vID=DispatchOrdState)
|
,ServiceOrd_SaleName=(select OA_Name from OA_User where OA_User_ID=ServiceOrd_Sale_ID)
|
,ServiceOrd_CCName=(select OA_Name from OA_User where OA_User_ID=ServiceOrd_CC_ID)
|
,ServiceOrd_workName=(select OA_Name from OA_User where OA_User_ID=ServiceOrd_work_ID)
|
,DispatchOrd_NSName=(select OA_Name from OA_User where OA_User_ID=DispatchOrd_NS_ID)
|
,ServiceOrdSource=(select vtext from dictionary where vtitle='OrdSource' and vID=ServiceOrdSource)
|
,PaidType=(SELECT STUFF(( select ',' + vtext from PaidMoney,dictionary where vtitle='PaidMoneyType' and vType>=1 and vID=PaidMoneyType and PaidMoney<>0 and ServiceOrdIDDt=ServiceOrdID GROUP BY vtext FOR XML PATH('')), 1, 1, '') AS PaidType)
|
,HospDepartmentName=isnull((select top 1 vtext from dictionary where vtitle='HospitalDepartment' and ServiceOrdPtServicesID=vID),'')
|
,HospDepartmentInName=isnull((select top 1 vtext from dictionary where vtitle='HospitalDepartment' and ServiceOrdPtInServicesID=vID),'')
|
,isRespirator=AMV_is,EntourageName=isnull(DispatchOrd_OAName,'--'),EntouragePost=isnull(DispatchOrd_OAEntourage,'--'),MessageCountInt=FromHQ_is,FromHQ2_is,Stime2=DATEDIFF(n,DispatchOrd_NS_Time,DispatchOrdActualDate)
|
,ContractMode_is=isnull((select COUNT(id) from ServiceOrd_TransferMode where ServiceOrdTMID=ServiceOrdID and TransferModeID=7),0)
|
,AddMoney1=isnull((select sum(AddMoney) from PaidMoney_Add where ToDispatchOrdID=DispatchOrdID and AddMoneyType in (1)),0)
|
,AddMoney2=isnull((select sum(AddMoney) from PaidMoney_Add where ToDispatchOrdID=DispatchOrdID and AddMoneyType in (2)),0)
|
,AddMoney3=isnull((select sum(AddMoney) from PaidMoney_Add where ToDispatchOrdID=DispatchOrdID and AddMoneyType in (3)),0)
|
";
|
|
|
}Else{
|
//按调度单统计
|
//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.$CoopUnitSql.$searchSql." and DispatchOrdClass in ('".$OrdClassListSql."')";
|
//组成计算总页数SQL
|
$total_sql = "select DispatchOrdID $sqlWhere";
|
//组成显示结果SQL
|
$Result_sql = "select ServiceOrdID,DispatchOrdID,OrderLevel,ServiceOrdAreaType,ServiceOrdType,ServiceOrdClass,ServiceOrdDepartureType,OrderPrice_Auto
|
,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)
|
,ServiceOrdNo=ServiceOrdClass+convert(nvarchar(10),year(ServiceOrd_CC_Time))+right('0'+convert(nvarchar(10),month(ServiceOrd_CC_Time)),2)+right('0'+convert(nvarchar(10),day(ServiceOrd_CC_Time)),2)+'-'+right('00'+convert(nvarchar(10),ServiceOrdNo),3)
|
,DispatchOrdState,DispatchOrd_NS_Date=CONVERT(nvarchar(20),DispatchOrd_NS_Time, 120),ServiceOrdCoName,ServiceOrdCoPhone,ServiceOrdIntroducer
|
,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
|
,ServiceOrd_Sale_ID,ServiceOrd_CC_ID,DispatchOrd_NS_ID,ServiceOrdSource,ServiceOrdPtOutHospID,ServiceOrdPtInHospID,ServiceOrdPtServicesID,ServiceOrdPtInServicesID,AMV_is
|
,ServiceOrdTraDistance,DispatchOrdCarID,DispatchOrd_NS_Time,DispatchOrdActualDate,DispatchOrd_OAName,DispatchOrd_OAEntourage,FromHQ_is,FromHQ2_is,ServiceOrdPtName,ServiceOrdPtServices,ServiceOrdTraVia,ServiceOrdTraEnd
|
,HopsProvince=hospA.HopsProvince,HopsCity=hospA.HopsCity,HopsArea=hospA.HopsArea,HospName=hospA.HospName
|
,HopsProvinceIn=hospB.HopsProvince,HopsCityIn=hospB.HopsCity,HopsAreaIn=hospB.HopsArea,HospNameIn=hospB.HospName,ServiceOrdTaskRemarks
|
,ServiceOrd_work_ID,ServiceOrd_work_IDs,ServiceOrd_work_is,CommissionScenarioID
|
,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)
|
,ServiceOrd_SaleName=(select OA_Name from OA_User where OA_User_ID=ServiceOrd_Sale_ID)
|
,ServiceOrd_CCName=(select OA_Name from OA_User where OA_User_ID=ServiceOrd_CC_ID)
|
,ServiceOrd_workName=(select OA_Name from OA_User where OA_User_ID=ServiceOrd_work_ID)
|
,DispatchOrd_NSName=(select OA_Name from OA_User where OA_User_ID=DispatchOrd_NS_ID)
|
,ServiceOrdSource=(select vtext from dictionary where vtitle='OrdSource' and vID=ServiceOrdSource)
|
,PaidType=(SELECT STUFF(( select ',' + vtext from PaidMoney,dictionary where vtitle='PaidMoneyType' and vType>=1 and vID=PaidMoneyType and PaidMoney<>0 and ServiceOrdIDDt=ServiceOrdID GROUP BY vtext FOR XML PATH('')), 1, 1, '') AS PaidType)
|
,HospDepartmentName=isnull((select top 1 vtext from dictionary where vtitle='HospitalDepartment' and ServiceOrdPtServicesID=vID),'')
|
,HospDepartmentInName=isnull((select top 1 vtext from dictionary where vtitle='HospitalDepartment' and ServiceOrdPtInServicesID=vID),'')
|
,isRespirator=AMV_is,EntourageName=DispatchOrd_OAName,EntouragePost=DispatchOrd_OAEntourage,MessageCountInt=FromHQ_is,FromHQ2_is,Stime2=DATEDIFF(n,DispatchOrd_NS_Time,DispatchOrdActualDate)
|
,ContractMode_is=isnull((select COUNT(id) from ServiceOrd_TransferMode where ServiceOrdTMID=ServiceOrdID and TransferModeID=7),0)
|
,AddMoney1=isnull((select sum(AddMoney) from PaidMoney_Add where ToDispatchOrdID=DispatchOrdID and AddMoneyType in (1)),0)
|
,AddMoney2=isnull((select sum(AddMoney) from PaidMoney_Add where ToDispatchOrdID=DispatchOrdID and AddMoneyType in (2)),0)
|
,AddMoney3=isnull((select sum(AddMoney) from PaidMoney_Add where ToDispatchOrdID=DispatchOrdID and AddMoneyType in (3)),0)
|
";
|
//$sql=",isRespirator=(select count(id) from ServiceOrd_Condition where ConditionID=4 and ServiceOrdCoID=ServiceOrdID)"
|
}
|
/**
|
* 根据 DispatchOrdID 查找开单人及跟单人列表
|
*
|
* @param int $ServiceOrdID 调度单 ID
|
* @param object $conn 数据库连接对象
|
* @return array 包含开单人及跟单人信息的数组
|
*/
|
function getDispatchOrderStaff($ServiceOrdID, $conn) {
|
$staffList = [];
|
|
// 查询开单人信息
|
$sqlDispatcher = "select PersonName,CommissionRatio from ServiceOrderCommissionDetails where ServiceOrderID='$ServiceOrdID' and PersonType='Opener'";
|
|
$dataDispatcher = sqlsrv_query($conn, $sqlDispatcher);
|
if ($dataDispatcher && sqlsrv_has_rows($dataDispatcher)) {
|
$rsDispatcher = sqlsrv_fetch_array($dataDispatcher);
|
$staffList['Opener'] = $rsDispatcher['PersonName'];
|
$staffList['OpenerCommissionRatio'] = $rsDispatcher['CommissionRatio'];
|
}
|
|
// 查询跟单人列表
|
$sqlEntourage = "select PersonName,CommissionRatio from ServiceOrderCommissionDetails where ServiceOrderID='$ServiceOrdID' and PersonType='Follower'";
|
$dataEntourage = sqlsrv_query($conn, $sqlEntourage);
|
if ($dataEntourage) {
|
$entourageList = [];
|
$i=1;
|
while ($rsEntourage = sqlsrv_fetch_array($dataEntourage)) {
|
$entourageList[$i]["Follower"] = $rsEntourage['PersonName'];
|
$entourageList[$i]["FollowerRatio"] = $rsEntourage['CommissionRatio'];
|
}
|
$staffList['entourage'] = $entourageList;
|
}
|
|
return $staffList;
|
}
|
//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("序号","服务单号","调度单号","级别","分区","预约类型","调度单状态","日期","时间","班次","联系人","联系电话","患者姓名","销售","介绍人","客服","调度","来源","是否企微成交","主要企微客服","其他企微客服","情景编号","企微绩效","总部下发","支援总部","承包组","区域","类型","出发省份","出发市","出发区","转出医院","转出科室","转出床位","转入省份","转入市","转入区","转入医院","转入科室","总成交价","系统报价参考","等待费","担架费","居家ICU费","距离(公里)","支付方式","使用呼吸机","车牌","出车成员","出车岗位","医护出车时间(分钟)","开单人","开单人提成比例","跟单人","跟单人提成比例","跟单人","跟单人提成比例","跟单人","跟单人提成比例","跟单人","跟单人提成比例");
|
if ($is_CSV==1){array_push($FormArray[0],"任务备注");}
|
$FormArrayX=count($FormArray[0]);
|
if (sqlsrv_rows_affected($data)==0) {
|
$SystemMessageType=2;
|
$SystemMessageTXT="数据库中相关无数据！";
|
$y=0;
|
} else {
|
$i=1;
|
while($rs = sqlsrv_fetch_array($data) ) {
|
//读取数据
|
//$DispatchOrdID = $rs['DispatchOrdID'];
|
//$OrdSum = $rs['OrdSum'];
|
//$CancelType = $rs['CancelType'];
|
if ($rs['isRespirator']==0) {
|
$isRespirator="否";
|
}else{
|
$isRespirator="是";
|
}
|
//读取数据--End
|
$ServiceOrdID=$rs['ServiceOrdID'];
|
//写入数组
|
$FormArray[$i][0]=$i+(($page-1)*$page_size);
|
If ($OrdType=="2") {
|
$FormArray[$i][1]="<a href='ServiceOrder.gds?ServiceOrdID=".$ServiceOrdID."&OrdDateType=4&OrdClassList=&h_menu1_1=1' target='_blank'>".$rs['ServiceOrdNo']."</a>";
|
if (!empty($rs['DispatchOrdNo'])) {
|
$FormArray[$i][2]="<a href='DispatchOrder.gds?DispatchOrdID=".$rs['DispatchOrdID']."&OrdDateType=4&OrdClassList=&h_menu1_1=1' target='_blank'>".$rs['DispatchOrdNo']."</a>";
|
}else{
|
$FormArray[$i][2]='--';
|
}
|
|
$FormArray[$i][3]=OrderLevelA($rs['OrderLevel']);
|
$FormArray[$i][4]=str_replace("地面","",str_replace("服务单","",OrderClassA($rs['ServiceOrdClass'])));
|
$FormArray[$i][5]=DepartureTypeA($rs['ServiceOrdDepartureType']);
|
if (!empty($rs['DispatchOrdStateTXT'])){
|
$FormArray[$i][6]=$rs['DispatchOrdStateTXT'];
|
}else{
|
$FormArray[$i][6]=$rs['ServiceOrderStateTXT'];
|
}
|
$FormArray[$i][7]=date("Y/m/d",strtotime($rs['ServiceOrd_CC_Date']));
|
$FormArray[$i][8]=date("H:i",strtotime($rs['ServiceOrd_CC_Date']));
|
$FormArray[$i][9]=date("H:i",strtotime($rs['ServiceOrd_CC_Date']))>"08:30" && date("H:i",strtotime($rs['ServiceOrd_CC_Date']))<"17:30" ? "白班" : "夜班";
|
}else{
|
$FormArray[$i][1]="<a href='ServiceOrder.gds?ServiceOrdID=".$rs['ServiceOrdID']."&OrdDateType=4&OrdClassList=&h_menu1_1=1' target='_blank'>".$rs['ServiceOrdNo']."</a>";
|
$FormArray[$i][2]="<a href='DispatchOrder.gds?DispatchOrdID=".$rs['DispatchOrdID']."&OrdDateType=4&OrdClassList=&h_menu1_1=1' target='_blank'>".$rs['DispatchOrdNo']."</a>";
|
$FormArray[$i][3]=OrderLevelA($rs['OrderLevel']);
|
$FormArray[$i][4]=str_replace("地面","",str_replace("服务单","",OrderClassA($rs['ServiceOrdClass'])));
|
$FormArray[$i][5]=DepartureTypeA($rs['ServiceOrdDepartureType']);
|
$FormArray[$i][6]=$rs['DispatchOrdStateTXT'];
|
$FormArray[$i][7]=date("Y/m/d",strtotime($rs['DispatchOrd_NS_Date']));
|
$FormArray[$i][8]=date("H:i",strtotime($rs['DispatchOrd_NS_Date']));
|
$FormArray[$i][9]=date("H:i",strtotime($rs['DispatchOrd_NS_Date']))>"08:30" && date("H:i",strtotime($rs['DispatchOrd_NS_Date']))<"17:30" ? "白班" : "夜班";
|
}
|
if ($is_Privacy=="NO" and $rs['ServiceOrdClass']=="BF") {
|
$FormArray[$i][10]="***";
|
$FormArray[$i][11]="1**********";
|
}else{
|
$FormArray[$i][10]=$rs['ServiceOrdCoName'];
|
$FormArray[$i][11]=$rs['ServiceOrdCoPhone'];
|
}
|
$FormArray[$i][12]=$rs['ServiceOrdPtName'];
|
if (isDepartment("010601")==1) {$FormArray[$i][13]=$rs['ServiceOrd_SaleName'];}elseif (isDepartment("010601")!=1 and !empty($rs['ServiceOrd_SaleName'])){$FormArray[$i][13]="***";}else{$FormArray[$i][13]="";}
|
if (isDepartment("010602")==1) {$FormArray[$i][14]=UnitIntroducer($rs['ServiceOrdIntroducer'],"UnitName");}elseif (isDepartment("010602")!=1 and !empty($rs['ServiceOrdIntroducer'])){$FormArray[$i][14]="***";}else{$FormArray[$i][14]="";}
|
$FormArray[$i][15]=$rs['ServiceOrd_CCName'];
|
$FormArray[$i][16]=$rs['DispatchOrd_NSName'];
|
$FormArray[$i][17]=$rs['ServiceOrdSource'];
|
if ($rs['ServiceOrd_work_is']==0){
|
$FormArray[$i][18]="否";
|
}else{
|
$FormArray[$i][18]="<label style='color: #ff4aff;'>是</>";
|
}
|
$FormArray[$i][19]=$rs['ServiceOrd_workName'];
|
$ServiceOrd_work_Names="";
|
if (!empty($rs['ServiceOrd_work_IDs'])) {
|
$sql = "select OA_Name from OA_User where OA_User_ID in (".$rs['ServiceOrd_work_IDs'].")";
|
$dataDt = sqlsrv_query($conn,$sql);
|
if($dataDt == true){
|
while($rsDt = sqlsrv_fetch_array( $dataDt, SQLSRV_FETCH_ASSOC) ) {
|
$ServiceOrd_work_Names = $ServiceOrd_work_Names.$rsDt['OA_Name'].",";
|
}
|
}
|
}
|
$FormArray[$i][20]=$ServiceOrd_work_Names;
|
|
if (!empty($rs['CommissionScenarioID']) and isDepartment("070127")==1) {
|
$sql = "select vID,vtext,vMono,vOrder2 from dictionary where vtitle in ('CommissionScenario') and vID=".$rs['CommissionScenarioID'];
|
$dataDt = sqlsrv_query($conn,$sql);
|
if($dataDt == true){
|
while($rsDt = sqlsrv_fetch_array( $dataDt, SQLSRV_FETCH_ASSOC) ) {
|
$FormArray[$i][21]=$rsDt['vtext'];
|
$FormArray[$i][22]=$rsDt['vOrder2'];
|
}
|
}
|
}else{
|
$FormArray[$i][21]="";
|
$FormArray[$i][22]="";
|
}
|
|
if ($rs['MessageCountInt']==0){
|
$FormArray[$i][23]="否";
|
}else{
|
$FormArray[$i][23]="<label style='color: #ff4aff;'>是</>";
|
}
|
if ($rs['FromHQ2_is']==0){
|
$FormArray[$i][24]="否";
|
}else{
|
$FormArray[$i][24]="<label style='color: #ff4aff;'>".$FromHQ2Type[$rs['FromHQ2_is']]."</>";
|
}
|
if ($rs['ContractMode_is']==0){
|
$FormArray[$i][25]="否";
|
}else{
|
$FormArray[$i][25]="<label style='color: #ff4aff;'>是</>";
|
}
|
$FormArray[$i][26]=$ArrayOrdAreaType[$rs['ServiceOrdAreaType']];
|
$FormArray[$i][27]=$ArrayOrdType[$rs['ServiceOrdType']];
|
$FormArray[$i][28]=$rs['HopsProvince'];
|
$FormArray[$i][29]=$rs['HopsCity'];
|
$FormArray[$i][30]=$rs['HopsArea'];
|
if ($rs['HospName']=="保障") {$HospName=$rs['ServiceOrdTraVia'];}elseif ($rs['HospName']=="家中") {$HospName="家中-".$rs['ServiceOrdTraVia'];}else{$HospName=$rs['HospName'];}
|
$FormArray[$i][31]=$HospName;
|
$FormArray[$i][32]=$rs['HospDepartmentName'];
|
$FormArray[$i][33]=$rs['ServiceOrdPtServices'];
|
$FormArray[$i][34]=$rs['HopsProvinceIn'];
|
$FormArray[$i][35]=$rs['HopsCityIn'];
|
$FormArray[$i][36]=$rs['HopsAreaIn'];
|
if ($rs['HospNameIn']=="保障") {$HospNameIn=$rs['ServiceOrdTraEnd'];}elseif ($rs['HospNameIn']=="家中") {$HospNameIn="家中-".$rs['ServiceOrdTraEnd'];}else{$HospNameIn=$rs['HospNameIn'];}
|
$FormArray[$i][37]=$HospNameIn;
|
$FormArray[$i][38]=$rs['HospDepartmentInName'];
|
$FormArray[$i][39]=round($rs['OrdTraPrice'],2);
|
if (!empty($rs['OrderPrice_Auto'])) {$OrderPrice_Auto=round($rs['OrderPrice_Auto'],2);}else{$OrderPrice_Auto='--';}
|
$FormArray[$i][40]=$OrderPrice_Auto;
|
$FormArray[$i][41]=round($rs['AddMoney1'],2);
|
$FormArray[$i][42]=round($rs['AddMoney2'],2);
|
$FormArray[$i][43]=round($rs['AddMoney3'],2);
|
$FormArray[$i][44]=str_replace("公里","",$rs['ServiceOrdTraDistance']);
|
$FormArray[$i][45]=$rs['PaidType'];
|
$FormArray[$i][46]=$isRespirator;
|
$FormArray[$i][47]=CarDataA($rs['DispatchOrdCarID'],'','CarLicense');
|
$FormArray[$i][48]=$rs['EntourageName'];
|
$FormArray[$i][49]=$rs['EntouragePost'];
|
$FormArray[$i][50]=$rs['Stime2'];
|
$rs2=getDispatchOrderStaff($ServiceOrdID, $conn);
|
//如果 $rs2 为空，设置默认值
|
if (empty($rs2)) {
|
$FormArray[$i][51]='--';
|
$FormArray[$i][52]='';
|
}else{
|
$FormArray[$i][51]=isset($rs2['Opener'])?$rs2['Opener']:'--';
|
$FormArray[$i][52]=isset($rs2['OpenerCommissionRatio'])?$rs2['OpenerCommissionRatio']:'';
|
}
|
if (!empty($rs2['entourage'])) {
|
//$rs2['entourage']的长度
|
$count=count($rs2['entourage']);
|
$j=0;
|
|
foreach ($rs2['entourage'] as $key => $value) {
|
$FormArray[$i][53+$j]=$value['Follower'];
|
$FormArray[$i][54+$j]=$value['FollowerRatio'];
|
$j++;
|
}
|
if($count<4){
|
//补足4个
|
while($j<4){
|
$FormArray[$i][53+$j*2]='--';
|
$FormArray[$i][54+$j*2]='';
|
$j++;
|
}
|
}
|
}else{
|
$FormArray[$i][53]='--';
|
$FormArray[$i][54]='';
|
$FormArray[$i][55]='--';
|
$FormArray[$i][56]='';
|
$FormArray[$i][57]='--';
|
$FormArray[$i][58]='';
|
$FormArray[$i][59]='--';
|
$FormArray[$i][60]='';
|
}
|
if ($is_CSV==1){$FormArray[$i][61]=$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;
|
?>
|
