<?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="↓";
}

//查询开始

$sql="select CouponDate,sum(CASE WHEN CouponType=2 then UserInt else 0 end) Wx1Int,sum(CASE WHEN CouponType=3 then UserInt else 0 end) Wx2Int,sum(CASE WHEN CouponType=4 then UserInt else 0 end) Wx3Int,sum(CASE WHEN CouponType=5 then UserInt else 0 end) Wx4Int,sum(CASE WHEN CouponType=1 then UserInt else 0 end) PcInt from (
select CONVERT(VARCHAR(10),CouponDate,120) CouponDate,
(CASE WHEN CHARINDEX('PC',CouponName)>0 then 1
 WHEN CHARINDEX('公交广告促销活动',CouponName)>0 then 2
 WHEN CHARINDEX('公交车促销活动',CouponName)>0 then 3
 WHEN CHARINDEX('道闸促销活动',CouponName)>0 then 4
 WHEN CHARINDEX('城市服务优惠',CouponName)>0 then 5
 else 0 end) CouponType,
COUNT(Coupon.ToUserID) UserInt from Coupon
left join UserPhone on UserPhone.ToUserID=Coupon.ToUserID
 where CouponMoney=500 and (strPhone is not null or CouponDate<'2023-10-13') and CouponDate ".$SqlDateType." GROUP BY CONVERT(VARCHAR(10),CouponDate,120),(CASE WHEN CHARINDEX('PC',CouponName)>0 then 1
 WHEN CHARINDEX('公交广告促销活动',CouponName)>0 then 2
 WHEN CHARINDEX('公交车促销活动',CouponName)>0 then 3
 WHEN CHARINDEX('道闸促销活动',CouponName)>0 then 4
 WHEN CHARINDEX('城市服务优惠',CouponName)>0 then 5
 else 0 end)) as a1
 GROUP BY CouponDate order by CouponDate desc";

/*
$sql="select CouponDate,sum(CASE WHEN CouponType=2 then UserInt else 0 end) Wx1Int,sum(CASE WHEN CouponType=3 then UserInt else 0 end) Wx2Int,sum(CASE WHEN CouponType=4 then UserInt else 0 end) Wx3Int,sum(CASE WHEN CouponType=5 then UserInt else 0 end) Wx4Int,sum(CASE WHEN CouponType=1 then UserInt else 0 end) PcInt from (
select CONVERT(VARCHAR(10),CouponDate,120) CouponDate,
(CASE WHEN CHARINDEX('PC',CouponName)>0 then 1
 WHEN CHARINDEX('公交广告促销活动',CouponName)>0 then 2
 WHEN CHARINDEX('公交车促销活动',CouponName)>0 then 3
 WHEN CHARINDEX('道闸促销活动',CouponName)>0 then 4
 WHEN CHARINDEX('城市服务优惠',CouponName)>0 then 5
 else 0 end) CouponType,
COUNT(Coupon.ToUserID) UserInt from Coupon where CouponMoney=500 and CouponDate ".$SqlDateType." GROUP BY CONVERT(VARCHAR(10),CouponDate,120),(CASE WHEN CHARINDEX('PC',CouponName)>0 then 1
 WHEN CHARINDEX('公交广告促销活动',CouponName)>0 then 2
 WHEN CHARINDEX('公交车促销活动',CouponName)>0 then 3
 WHEN CHARINDEX('道闸促销活动',CouponName)>0 then 4
 WHEN CHARINDEX('城市服务优惠',CouponName)>0 then 5
 else 0 end)) as a1
 GROUP BY CouponDate order by CouponDate desc";
*/
//echo $sql;exit;
$data = sqlsrv_query($conn,$sql);
if($data == true){
	$FormArray=array();
	//表头
	$FormArray[0]=array("日期","PC数量","小程序扫码数量","4月活动","7月活动","珠江医院道闸","城市服务");
	$FormArrayX=count($FormArray[0]);
	if (sqlsrv_rows_affected($data)==0) {
		$SystemMessageType=2;
		$SystemMessageTXT="数据库中相关无数据！";
		$y=0;
	} else {
		$i=0;
		while($rs = sqlsrv_fetch_array( $data, SQLSRV_FETCH_ASSOC) ) {
			//读取数据
			$CouponDate		= $rs['CouponDate'];
			$WxInt		= $rs['Wx1Int']+$rs['Wx2Int']+$rs['Wx3Int']+$rs['Wx4Int'];
			$Wx1Int		= $rs['Wx1Int'];
			$Wx2Int		= $rs['Wx2Int'];
			$Wx3Int		= $rs['Wx3Int'];
			$Wx3Int		= $rs['Wx3Int'];
			$Wx4Int		= $rs['Wx4Int'];
			$PcInt		= $rs['PcInt'];
			//读取数据--End
			
			//写入数组
			$i++;
			$FormArray[$i][0]=$CouponDate;
			$FormArray[$i][1]=$PcInt;
			$FormArray[$i][2]=$WxInt;
			$FormArray[$i][3]=$Wx1Int;
			$FormArray[$i][4]=$Wx2Int;
			$FormArray[$i][5]=$Wx3Int;
			$FormArray[$i][6]=$Wx4Int;
			
			//写入数组--End
		}

	}
}

//echo print_r($FormArray);
//exit;
?>