mysql+thinkphp +amcharts 完成图表统计功能
时间:2022-03-10 18:06
思路:从mysql数据库查询数据,经过thinkphp 后端控制器做逻辑处理,返回给前端,前端调用阿妈charts 插件
1.数据查询:
public function order($time=‘‘,$radio=1){ if($time== ‘‘){ $time = Date("Y"); } $bt = $time."-01-01 00:00:00"; $et = (string)((int)$time+1)."-01-01 00:00:00"; $sql=‘‘; if($radio==1){ $sql="SELECT right(tt.month,2) as month, IFNULL( we.web, 0 ) as web, IFNULL( wx.weixin, 0 ) as weixin, IFNULL( ad.andriod, 0 ) as andriod , IFNULL( ip.iphone, 0 ) as iphone FROM ( ( SELECT DATE_FORMAT( `AddTime` , ‘%Y-%m‘ ) AS MONTH FROM `order` WHERE State = 2 AND AddTime >= ‘$bt‘ AND AddTime < ‘$et‘ GROUP BY MONTH ) AS tt LEFT JOIN ( SELECT DATE_FORMAT( `AddTime` , ‘%Y-%m‘ ) AS MONTH , SUM( `PayMoney` ) AS web FROM `order` WHERE `OrderSource` =1 AND State = 2 AND AddTime >= ‘$bt‘ AND AddTime < ‘$et‘ GROUP BY MONTH ) AS we ON tt.month = we.month LEFT JOIN ( SELECT DATE_FORMAT( `AddTime` , ‘%Y-%m‘ ) AS MONTH , SUM( `PayMoney` ) AS weixin FROM `order` WHERE `OrderSource` =2 AND State = 2 AND AddTime >= ‘$bt‘ AND AddTime < ‘$et‘ GROUP BY MONTH ) AS wx ON tt.month = wx.month LEFT JOIN ( SELECT DATE_FORMAT( `AddTime` , ‘%Y-%m‘ ) AS MONTH , SUM( `PayMoney` ) AS andriod FROM `order` WHERE `OrderSource` =3 AND State = 2 AND AddTime >= ‘$bt‘ AND AddTime < ‘$et‘ GROUP BY MONTH ) AS ad ON tt.month = ad.month LEFT JOIN ( SELECT DATE_FORMAT( `AddTime` , ‘%Y-%m‘ ) AS MONTH , SUM( `PayMoney` ) AS iphone FROM `order` WHERE `OrderSource` =4 AND State = 2 AND AddTime >= ‘$bt‘ AND AddTime < ‘$et‘ GROUP BY MONTH ) AS ip ON tt.month = ip.month )"; }else{ $sql="SELECT right(tt.month,2) as month, IFNULL( we.web, 0 ) as web, IFNULL( wx.weixin, 0 ) as weixin, IFNULL( ad.andriod, 0 ) as andriod, IFNULL( ip.iphone, 0 ) as iphone FROM ( ( SELECT DATE_FORMAT( `AddTime` , ‘%Y-%m‘ ) AS MONTH FROM `order` WHERE State = 2 AND AddTime >= ‘$bt‘ AND AddTime < ‘$et‘ GROUP BY MONTH ) AS tt LEFT JOIN ( SELECT DATE_FORMAT( `AddTime` , ‘%Y-%m‘ ) AS MONTH , count( `id` ) AS web FROM `order` WHERE `OrderSource` =1 AND State = 2 AND AddTime >= ‘$bt‘ AND AddTime < ‘$et‘ GROUP BY MONTH ) AS we ON tt.month = we.month LEFT JOIN ( SELECT DATE_FORMAT( `AddTime` , ‘%Y-%m‘ ) AS MONTH , count( `id` ) AS weixin FROM `order` WHERE `OrderSource` =2 AND State = 2 AND AddTime >= ‘$bt‘ AND AddTime < ‘$et‘ GROUP BY MONTH ) AS wx ON tt.month = wx.month LEFT JOIN ( SELECT DATE_FORMAT( `AddTime` , ‘%Y-%m‘ ) AS MONTH , count( `id` ) AS andriod FROM `order` WHERE `OrderSource` =3 AND State = 2 AND AddTime >= ‘$bt‘ AND AddTime < ‘$et‘ GROUP BY MONTH ) AS ad ON tt.month = ad.month LEFT JOIN ( SELECT DATE_FORMAT( `AddTime` , ‘%Y-%m‘ ) AS MONTH , count( `id` ) AS iphone FROM `order` WHERE `OrderSource` =4 AND State = 2 AND AddTime >= ‘$bt‘ AND AddTime < ‘$et‘ GROUP BY MONTH ) AS ip ON tt.month = ip.month )"; } $m = new Model();//实例化对象模型 //echo $sql; $o = $m->query($sql); //dump(json_encode($o)); $this-> o = json_encode($o); $this-> ol = $o; $this-> time = $time; $this-> radio = $radio; $this-> timearr = $temp; $this ->display(‘order‘); } public function recharge($time=‘‘,$radio=1,$year=NULL,$month=NULL){ $this->meta_title = ‘充值信息统计‘; $sql= ‘‘; if($radio==1){//按月统计 if($time== ‘‘){ $time = Date("Y"); } $bt = $time."-01-01 00:00:00"; $et = (string)((int)$time+1)."-01-01 00:00:00"; $sql="SELECT right(tt.month,2) as month, IFNULL( we.usum, 0 ) as usum FROM ( ( SELECT DATE_FORMAT( `AddTime` , ‘%Y-%m‘ ) AS MONTH FROM `recharge` WHERE PayType in(0,1) AND AddTime >= ‘$bt‘ AND AddTime < ‘$et‘ GROUP BY MONTH ) AS tt LEFT JOIN ( SELECT DATE_FORMAT( `AddTime` , ‘%Y-%m‘ ) AS MONTH , sum( `Money` ) AS usum FROM `recharge` WHERE PayType in(0,1) AND AddTime >= ‘$bt‘ AND AddTime < ‘$et‘ GROUP BY MONTH ) AS we ON tt.month = we.month )"; $this-> showname = ‘月‘; }else{//按天统计 if(!$year){ $year = Date("Y"); } $time = Date("Y"); if(!$month){ $month = Date("m"); } $bt = $year.‘-‘.$month."-01 00:00:00"; $et = date(‘Y-m-d H:i:s‘,strtotime(‘+1 month‘,strtotime($bt))); $sql="SELECT right(tt.month,2) as month, IFNULL( we.usum, 0 ) as usum FROM ( ( SELECT DATE_FORMAT( `AddTime` , ‘%Y-%m-%d‘ ) AS MONTH FROM `recharge` WHERE PayType in(0,1) AND AddTime >= ‘$bt‘ AND AddTime < ‘$et‘ GROUP BY MONTH ) AS tt LEFT JOIN ( SELECT DATE_FORMAT( `AddTime` , ‘%Y-%m-%d‘ ) AS MONTH , sum( `Money` ) AS usum FROM `recharge` WHERE PayType in(0,1) AND AddTime >= ‘$bt‘ AND AddTime < ‘$et‘ GROUP BY MONTH ) AS we ON tt.month = we.month )"; $this-> showname = ‘天‘; } $m = new Model();//实例化对象模型 //echo $sql; $o = $m->query($sql); //dump($o); $this-> o = json_encode($o); $this-> ol = $o; $this-> time = $time; $this-> radio = $radio; $this-> year = $year; $this-> month = $month; $this ->display(‘recharge‘); }
用到分组查询,sql 函数 有
A. right(tt.month,2) as month
B. IFNULL( we.usum, 0 ) as usum
C. count( `id` ) AS unum
D. sum( `Money` ) AS usum
E. SELECT DATE_FORMAT( `AddTime` , ‘%Y-%m‘ ) AS
MONTH FROM `table`
WHERE XXXXX
GROUP BY MONTH(分组group by 直接用select 后面的month即可)
‘%Y-%m‘ 是时间格式,DATE_FORMAT 用于时间格式为“2012-01-01 00:01:22”的这种形式。
F. SELECT FROM_UNIXTIME( `AddTime` , ‘%Y-%m-%d‘) AS
MONTH , count( `id` ) AS unum
FROM `table`
WHERE XXXXX
GROUP BY MONTH
FROM_UNIXTIME 用于时间戳格式。
分组的思想是先查询出有哪些组,然后在查询出符合的数据,然后用left join 来组合。
2. 前端数据整合
A. 数据格式
给前端的数据格式为JSON:
var chartData = [
{
"month": "01",
"web": 2.5,
"weixin": 2.5,
"andriod": 2.1,
"iphone": 1.3
},
{
"month": "02",
"web": 2.6,
"weixin": 2.7,
"andriod": 2.2,
"iphone": 1.3
},
{
"month": "03",
"web": 2.5,
"weixin": 2.5,
"andriod": 2.1,
"iphone": 1.3
},
{
"month": "04",
"web": 2.6,
"weixin": 2.7,
"andriod": 2.2,
"iphone": 1.3
},
{
"month": "05",
"web": 2.8,
"weixin": 2.9,
"andriod": 2.4,
"iphone": 1.3
},
{
"month": "06",
"web": 2.8,
"weixin": 2.9,
"andriod": 2.4,
"iphone": 1.3
},
{
"month": "07",
"web": 2.5,
"weixin": 2.5,
"andriod": 2.1,
"iphone": 1.3
},
{
"month": "08",
"web": 2.6,
"weixin": 2.7,
"andriod": 2.2,
"iphone": 1.3
},
{
"month": "09",
"web": 2.8,
"weixin": 2.9,
"andriod": 2.4,
"iphone": 1.3
},
{
"month": "10",
"web": 2.8,
"weixin": 2.9,
"andriod": 2.4,
"iphone": 1.3
},
{
"month": "11",
"web": 2.8,
"weixin": 2.9,
"andriod": 2.4,
"iphone": 1.3
},
{
"month": "12",
"web": 2.8,
"weixin": 2.9,
"andriod": 2.4,
"iphone": 1.3
}
];
thinkphp 查询出来的数据时数组,用json_encode 转换成JSON 数组
$o = $m->query($sql);
$this-> o = json_encode($o);
$this-> ol = $o;
B. html 代码(要画图的层)
<div id="chartdiv" src="http://image.mamicode.com/info/201407/20180921195126797661.jpg" />
mysql+thinkphp +amcharts 完成图表统计功能,布布扣,bubuko.com