<?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) ";
	} elseif (substr($searchTXT,0,12)=="StaffScore2|") {
		$StaffScore=explode("|",$searchTXT);
		$searchSql=" and (ServiceOrdIDDt in (select ServiceOrdID from ServiceOrder where ServiceOrdVisit_ID=".$StaffScore[2]." and ServiceOrdVisit_time ".$SqlOrdDateType.")) ";
	} elseif (substr($searchTXT,0,12)=="StaffScore1|") {
		$StaffScore=explode("|",$searchTXT);
		$searchSql=" and (ServiceOrdIDDt in (select ServiceOrdID from ServiceOrder where ServiceOrdVisit_Dict= '".$StaffScore[1]."' and ServiceOrdVisit_ID=".$StaffScore[2]." and ServiceOrdVisit_time ".$SqlOrdDateType.")) ";
	} elseif (substr($searchTXT,0,12)=="StaffScore0|") {
		$StaffScore=explode("|",$searchTXT);
		$searchSql=" and (ServiceOrdIDDt in (select ServiceOrdID from ServiceOrder where ServiceOrdVisit_Dict is null and ServiceOrdVisit_ID=".$StaffScore[2]." and ServiceOrdVisit_time ".$SqlOrdDateType.")) and (DispatchOrdID not in  (select DispatchOrdIDDt from DispatchOrd_Entourage where RV_PointE in  (',1',',2')))";
	} elseif (substr($searchTXT,0,12)=="StaffScore3|") {
		$StaffScore=explode("|",$searchTXT);
		$searchSql=" and (ServiceOrdIDDt in (select ServiceOrdID from ServiceOrder where ServiceOrdVisit_Dict is null and ServiceOrdVisit_ID=".$StaffScore[2]." and ServiceOrdVisit_time ".$SqlOrdDateType.")) and (DispatchOrdID in  (select DispatchOrdIDDt from DispatchOrd_Entourage where RV_PointE in  (',1',',2')))";
	} 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) ".$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
	 ,ServiceOrdVisit_ID,ServiceOrdVisit,ServiceOrdReturnChannel,ServiceOrdVisit_time=CONVERT(nvarchar(20),ServiceOrdVisit_time, 120),Guest_Point,Guest_Reward
	 ,row_number() over (order by DispatchOrd_NS_Time desc) n  $sqlWhere";
	//附加结果SQL
	$Additional_sql = "
	,ServiceOrd_VisitName=(select OA_Name from OA_User where OA_User_ID=ServiceOrdVisit_ID)
	";
	//$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) ) {
			//读取数据
			$ServiceOrdID		= $rs['ServiceOrdID'];
			$EntourageOA="";
			$sql="select DispatchOrd_Entourage.id,EntourageOAid,OA_Name,RV_PointE,RV_Point_1,RV_Point_2,RV_Point_3,EntourageID from DispatchOrd_Entourage,OA_User,DispatchOrd where OA_User_ID=EntourageOAid and EntourageState<>4 and DispatchOrdIDDt=DispatchOrdID and DispatchOrdState not in (10,9) and ServiceOrdIDDt=".$ServiceOrdID;
			$dataDt = sqlsrv_query($conn,$sql);
			if($dataDt == true){
				while($rsDt = sqlsrv_fetch_array($dataDt) ) {
					If ($rsDt['RV_PointE']==",8")  {
						$RV_PointE="满意";
					} elseif ($rsDt['RV_PointE']==",1") {
						$RV_PointE="不满意技能水平";
					} elseif ($rsDt['RV_PointE']==",2") {
						$RV_PointE="不满意服务水平";
					} else {
						$RV_PointE="未选择";
					}
					$EntourageOA=$EntourageOA." ".$rsDt['OA_Name']."[".$RV_PointE."] ";
				}
			}
			//$OrdSum		= $rs['OrdSum'];
			//$CancelType	= $rs['CancelType'];
			//读取数据--End
			
			//写入数组
			$FormArray[$i][0]=$i+(($page-1)*$page_size);
			$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>";
			if ($rs['HospName']=="保障") {$HospName=$rs['ServiceOrdTraVia'];}elseif ($rs['HospName']=="家中") {$HospName="家中-".$rs['ServiceOrdTraVia'];}else{$HospName=$rs['HospName'];}
			$FormArray[$i][3]=$HospName;
			if ($rs['HospNameIn']=="保障") {$HospNameIn=$rs['ServiceOrdTraEnd'];}elseif ($rs['HospNameIn']=="家中") {$HospNameIn="家中-".$rs['ServiceOrdTraEnd'];}else{$HospNameIn=$rs['HospNameIn'];}
			$FormArray[$i][4]=$HospNameIn;
			$FormArray[$i][5]=$EntourageOA;
			$FormArray[$i][6]=$rs['ServiceOrdVisit'];
			$FormArray[$i][7]=$rs['ServiceOrdReturnChannel'];
			$FormArray[$i][8]=round($rs['Guest_Reward'],2);
			$FormArray[$i][9]=date("Y/m/d H:i",strtotime($rs['DispatchOrd_NS_Date']));
			$FormArray[$i][10]=date("Y/m/d H:i",strtotime($rs['ServiceOrdVisit_time']));
			if ($rs['Guest_Point']==0) {$GuestVisit="客服回访";}else {$GuestVisit="客户评价";}
			$FormArray[$i][11]=$GuestVisit;
			$FormArray[$i][12]=$rs['ServiceOrd_VisitName'];
			$FormArray[$i][13]=str_replace("公里","",$rs['ServiceOrdTraDistance']);
			$FormArray[$i][14]=round($rs['OrdTraPrice'],2);


			$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;
?>