<?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;
|
?>
|
