$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"; } //查询条件 $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 Coupon,ServiceOrder LEFT JOIN DispatchOrd on ServiceOrdID=ServiceOrdIDDt and DispatchOrdState<>'10' where CouponState=1 and ServiceOrdID=CouponRemarks and ServiceOrd_CC_Time ".$SqlOrdDateType; //组成计算总页数SQL $total_sql = "select ServiceOrdID $sqlWhere"; //组成显示结果SQL $Result_sql = "select ServiceOrdID,CONVERT(VARCHAR(20),ServiceOrd_CC_Time,120) ServiceOrd_CC_Time,DispatchOrdClass,DispatchOrdID ,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) ,ServiceOrdTraTxnPrice,CouponMoney,CouponNO,SUBSTRING(CouponName,0,charindex(' ',CouponName)) CouponName,CONVERT(VARCHAR(20),CouponDate,120) CouponDate,ServiceOrdCoPhone ,row_number() over (order by ServiceOrd_CC_Time desc) n $sqlWhere"; //附加结果SQL $Additional_sql = ""; //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) ) { //读取数据 $CouponName = $rs['CouponName']; if ($CouponName=='公交广告促销活动') { $CouponName='4月公交活动'; }elseif ($CouponName=='公交车促销活动') { $CouponName='7月公交活动'; } //$OrdSum = $rs['OrdSum']; //$CancelType = $rs['CancelType']; //读取数据--End //写入数组 $FormArray[$i][0]=$i+(($page-1)*$page_size); $FormArray[$i][1]=$rs['ServiceOrd_CC_Time']; $FormArray[$i][2]="".$rs['ServiceOrdNo'].""; if (!empty($rs['DispatchOrdNo'])) { $FormArray[$i][3]="".$rs['DispatchOrdNo'].""; }else{ $FormArray[$i][3]='--'; } $FormArray[$i][4]=round($rs['ServiceOrdTraTxnPrice'],2); $FormArray[$i][5]=round($rs['CouponMoney'],2); $FormArray[$i][6]=$CouponName; $FormArray[$i][7]=$rs['CouponNO']; $FormArray[$i][8]=$rs['ServiceOrdCoPhone']; $i++; //写入数组--End } } } //组成Json数据 if ($is_CSV==2) { $JsonArray=array(); for($y=1;$y 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; ?>