<?php
|
//分页信息
|
$page=empty($_REQUEST['page'])!=false ? 1 : $_REQUEST['page'];
|
$QuantityInt = 20; //每页显示数量
|
$acc1=$page; //当前页数
|
$acc2=0; //总页数
|
$acc3=0; //总记录数
|
|
//查询条件
|
$searchSql="";
|
$searchTXT=empty($searchTXT)!=false ? "" : $searchTXT;
|
if ($searchTXT!=""){
|
$searchSql=" and (OA_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,OA_User_ID";
|
$descTXT="↓";
|
}
|
|
if ($OrdDateType=="0" and $OrdDateStart=="" and $OrdDateEnd=="") {$OrdDateType=4;}
|
|
//$SqlOrdDateType="between DATEADD(yy,DATEDIFF(yy,0,getdate()),0) and dateadd(ms,-3,DATEADD(yy,DATEDIFF(yy,0,getdate())+1,0))";
|
|
if (empty($SqlOrdDateType)==false) {
|
$SqlOrdDateType=str_replace("between","",$SqlOrdDateType);
|
$DateTypeArray = explode(" and ", $SqlOrdDateType);
|
$StDate=$DateTypeArray[0];
|
$BeDate=$DateTypeArray[1];
|
} else {
|
$StDate="DATEADD(yy,DATEDIFF(yy,0,getdate()),0)";
|
$BeDate="dateadd(ms,-3,DATEADD(yy,DATEDIFF(yy,0,getdate())+1,0))";
|
}
|
|
//查询开始
|
$sql ="select stDate=CONVERT(nvarchar(20),dateadd(m,num,$StDate), 111)";
|
$sql.=",OrderInt=(select count(DispatchOrdID) from DispatchOrd where DispatchOrdState in (8,9) and DispatchOrd_NS_Time between dateadd(m,num,$StDate) and dateadd(s,-1,dateadd(m,num+1,$StDate)))";
|
$sql.=",OrderRespiratorInt=(select count(DispatchOrdID) from DispatchOrd,ServiceOrd_Condition where DispatchOrdState in (8,9) and DispatchOrd_NS_Time between dateadd(m,num,$StDate) and dateadd(s,-1,dateadd(m,num+1,$StDate)) and ServiceOrdIDDt=ServiceOrdCoID and ConditionID=4)";
|
$sql.=",OrderMoney=isnull((select sum(ServiceOrdTraPaidPrice) from ServiceOrder,DispatchOrd where DispatchOrdState in (8,9) and DispatchOrd_NS_Time between dateadd(m,num,$StDate) and dateadd(s,-1,dateadd(m,num+1,$StDate)) and ServiceOrdIDDt=ServiceOrdID),0)";
|
$sql.=",OrderRespiratorMoney=isnull((select sum(ServiceOrdTraPaidPrice) from ServiceOrder,DispatchOrd,ServiceOrd_Condition where DispatchOrdState in (8,9) and DispatchOrd_NS_Time between dateadd(m,num,$StDate) and dateadd(s,-1,dateadd(m,num+1,$StDate)) and ServiceOrdIDDt=ServiceOrdCoID and ConditionID=4 and ServiceOrdIDDt=ServiceOrdID),0)";
|
$sql.=" FROM (SELECT ROW_NUMBER() OVER (ORDER BY ID)-1 AS num FROM sysobjects) KK where dateadd(m,num,$StDate)<=$BeDate ";
|
//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) ) {
|
//读取数据
|
$stDate = $rs['stDate'];
|
$OrderInt = $rs['OrderInt'];
|
$OrderRespiratorInt= $rs['OrderRespiratorInt'];
|
$OrderMoney = $rs['OrderMoney'];
|
$OrderRespiratorMoney= $rs['OrderRespiratorMoney'];
|
|
//读取数据--End
|
|
//写入数组
|
$FormArray[$i][0]=date("Y年m月",strtotime($stDate));
|
$FormArray[$i][1]=$OrderInt;
|
$FormArray[$i][2]=$OrderRespiratorInt;
|
if ($FormArray[$i][2]>0) {$FormArray[$i][3]=round($FormArray[$i][2]/$FormArray[$i][1]*100,2)."%";} else {$FormArray[$i][3]="0%";}
|
$FormArray[$i][4]=round($OrderMoney);
|
$FormArray[$i][5]=round($OrderRespiratorMoney);
|
if ($FormArray[$i][5]>0) {$FormArray[$i][6]=round($FormArray[$i][5]/$FormArray[$i][4]*100,2)."%";} else {$FormArray[$i][6]="0%";}
|
$i++;
|
//写入数组--End
|
}
|
|
|
}
|
}
|
|
if ($is_CSV==2) {
|
echo json_encode($FormArray);
|
}
|
//echo print_r($FormArray);
|
//exit;
|
?>
|
