需求：
统计最近7天/30天数据画折线图
最终成果图：
1534410155(1).png
1534410238(1).png
先上html:
<?php
use yii\widgets\LinkPager;
?>
<script type="text/javascript" src="/common/lib/layer.ext.js"></script>
<script src="/common/lib/admin/js/bootstrap.min.js"></script>
<script src="/common/lib/admin/js/chart.min.js"></script>
<script>
function datacount(){
window.location.href = '/guest/third/datacount';
}
function search(){
window.location.href="//www.greatytc.com/guest/third/drawthirdcaiwusum?dayselect="+$("#dayselect").val();
}
</script>
<div id="page-inner" style="padding: 30px;">
<div class="row">
<div class="col-md-12">
<h1 class="page-header">
财务统计图表
</h1>
</div>
<div class="panel-body">
<div class="dataTables_wrapper form-inline" style="margin-bottom: 15px;">
<select class="btn btn-default btn-sm" id="dayselect">
<option value = "7" <?= $day == 7?'selected' : ''?>>近7天</option>
<option value = "30" <?= $day == 30?'selected' : ''?>>近30天</option>
</select>
<button class="btn btn-default btn-sm" onclick="search()" title="点击搜索"><i class="fa fa-search"></i> <b>Search</b></button>
<button id="drawdatacount" onclick="datacount()" class="btn btn-sm btn-info">表格显示</button>
</div>
</div>
<div class="container">
<div class="row">
<div class="col-xs-1 "><canvas id="myChart"></canvas></div>
<div class="col-xs-10 "><canvas id="myChart1"></canvas></div>
</div>
</div>
</div>
</div>
<script>
var ctx = document.getElementById("myChart1");
var myChart = new Chart(ctx, {
type: 'line', // line 表示是 曲线图，当然也可以设置其他的图表类型 如柱形图 : bar 或者其他
data: {
labels : <?=$a?>, //按时间段 可以按星期，按月，按年
datasets : <?=$c?>
}
});
</script>
方法一：
//交易量折线图
public function actionDrawthirdcaiwusum()
{
if (Yii::$app->request->get("dayselect")) {
$day = Yii::$app->request->get("dayselect");
} else {
$day = 7;
}
$rows=Yii::$app->db->createCommand("select thirdid,FROM_UNIXTIME(yocreatetime,'%Y-%m-%d') as datetime
,SUM(yototal) as money
from ibd_third_oyihuo
LEFT JOIN ibd_shop ON (ibd_third_oyihuo.yosellerid=ibd_shop.suid or ibd_third_oyihuo.yobuyerid=ibd_shop.suid)
where (ystatus = 1 or ystatus = 4) and yotype<20
and yocreatetime>=unix_timestamp(DATE_FORMAT(subdate(now(),$day),'%Y-%m-%d'))
and yocreatetime<unix_timestamp(DATE_FORMAT(now(),'%Y-%m-%d'))
group by thirdid,FROM_UNIXTIME(yocreatetime,'%Y-%m-%d')
ORDER BY yocreatetime desc")->query();
$th=IbdThirdInfo::find()->all();
$id=[];
foreach($th as $val){
$id[]=$val->id;
}
for ($i = $day; 0 < $i; $i--) {
$result[] = date('Y-m-d', strtotime('-' . $i . ' day'));
foreach($id as $vvv){
${'money'.$vvv}[] =0;
}
}
foreach($rows as $k => $v){
foreach($result as $value =>$item){
// print_r($v);
$temp = reset($v);
$index = array_search($v['datetime'],$result);
${'money'.$temp}[$index]=sprintf("%.2f",$v['money']/100);
}
}
function my_json_decode($str) {
$str = preg_replace('/"(\w+)"(\s*:\s*)/is', '$1$2', $str); //去掉key的双引号
return $str;
}
$color = array("#EE0000","#00FFFF","#712704","#43A102","#049FF1","#FFA500","#4C4C4C","#00CCFF","#72CFD7","#FF8C05");
$res= array();
foreach($id as $vv){
$fileinfo['label'] = IbdThirdInfo::findOne(['id'=>$vv])->name;
$fileinfo['fill'] = true;
$fileinfo['borderColor'] = $color[$vv%10];
$fileinfo['pointBackgroundColor'] = "#fff";
$fileinfo['data'] = ${'money'.$vv};
array_push($res, $fileinfo);
}
$a = json_encode($result);
$c = my_json_decode(json_encode($res));
// print_r($c);die;
return $this->render('drawthirdcaiwusum',['a'=>$a,'c'=>$c,'day'=>$day]);
}
优化后的方法二
//交易量折线图
public function actionDrawthirdcaiwusum()
{
if (Yii::$app->request->get("dayselect")) {
$day = Yii::$app->request->get("dayselect");
} else {
$day = 7;
}
$rows=Yii::$app->db->createCommand("select thirdid,FROM_UNIXTIME(yocreatetime,'%Y-%m-%d') as datetime
,SUM(yototal) as money
from ibd_third_oyihuo
LEFT JOIN ibd_shop ON (ibd_third_oyihuo.yosellerid=suid)
where (ystatus = 1 or ystatus = 4) and yotype<20
and yocreatetime>=unix_timestamp(DATE_FORMAT(subdate(now(),$day),'%Y-%m-%d'))
and yocreatetime<unix_timestamp(DATE_FORMAT(now(),'%Y-%m-%d'))
group by thirdid,FROM_UNIXTIME(yocreatetime,'%Y-%m-%d')
ORDER BY yocreatetime desc")->query();
$th=IbdThirdInfo::find()->all();
$id=[];
foreach($th as $val){
$id[]=$val->id;
}
$rows = $rows->readAll();
for ($i = $day; 0 < $i; $i--) {//time
$result[] = date('Y-m-d', strtotime('-' . $i . ' day'));
}
foreach($id as $v1=>$thirdid){//第三方id
foreach($result as $value =>$item){ //日期
$isexit=0; //0表示不存在 1表示存在
foreach( $rows as $f =>$v){ //数据
if($item==$v['datetime']&&$thirdid==$v['thirdid']){
${'money'.$v['thirdid']}[]=sprintf("%.2f",$v['money']/100);
$isexit=1;
break;
}
}
if(!$isexit){
${'money'.$thirdid}[]=0;
}
}
}
function my_json_decode($str) {
$str = preg_replace('/"(\w+)"(\s*:\s*)/is', '$1$2', $str); //去掉key的双引号
return $str;
}
$color = array("#EE0000","#00FFFF","#712704","#43A102","#049FF1","#FFA500","#4C4C4C","#00CCFF","#72CFD7","#FF8C05");
$res= array();
foreach($id as $vv){
$fileinfo['label'] = IbdThirdInfo::findOne(['id'=>$vv])->name;
$fileinfo['fill'] = true;
$fileinfo['borderColor'] = $color[$vv%10];
$fileinfo['pointBackgroundColor'] = "#fff";
$fileinfo['data'] = ${'money'.$vv};
array_push($res, $fileinfo);
}
$a = json_encode($result);
$c = my_json_decode(json_encode($res));
// print_r($c);die;
return $this->render('drawthirdcaiwusum',['a'=>$a,'c'=>$c,'day'=>$day]);
}
再次优化
$th=IbdThirdInfo::find()->all();
$id=[];
foreach($th as $val){
$id[]=$val->id;
}
$rows = $rows->readAll();
for ($i = $day; 0 < $i; $i--) {//time
$result[] = date('Y-m-d', strtotime('-' . $i . ' day'));
$data[] = 0;
}
// foreach($id as $v1=>$thirdid){//第三方id
// foreach($result as $value =>$item){ //日期
// $isexit=0; //0表示不存在 1表示存在
// foreach( $rows as $f =>$v){ //数据
// if($item==$v['datetime']&&$thirdid==$v['thirdid']){
// ${'money'.$v['thirdid']}[]=sprintf("%.2f",$v['money']/100);
// $isexit=1;
// break;
// }
// }
// if(!$isexit){
// ${'money'.$thirdid}[]=0;
// }
// }
// }
foreach($th as $v1=>$thirdid){//第三方id
${'money'.$thirdid->id} = $data;
}
foreach( $rows as $f =>$v){ //数据
$index = array_search($v['datetime'],$result);
${'money'.$v['thirdid']}[$index]=sprintf("%.2f",$v['money']/100);
}
时间复杂度优化
//交易量折线图
public function actionDrawthirdcaiwusum()
{
if (Yii::$app->request->get("dayselect")) {
$day = Yii::$app->request->get("dayselect");
} else {
$day = 7;
}
$rows=Yii::$app->db->createCommand("select thirdid,FROM_UNIXTIME(yocreatetime,'%Y-%m-%d') as datetime
,SUM(yototal) as money
from ibd_third_oyihuo
LEFT JOIN ibd_shop ON (ibd_third_oyihuo.yosellerid=suid)
where (ystatus = 1 or ystatus = 4) and yotype<20
and yocreatetime>=unix_timestamp(DATE_FORMAT(subdate(now(),$day),'%Y-%m-%d'))
and yocreatetime<unix_timestamp(DATE_FORMAT(now(),'%Y-%m-%d'))
group by thirdid,FROM_UNIXTIME(yocreatetime,'%Y-%m-%d')
ORDER BY yocreatetime desc")->query();
$th=IbdThirdInfo::find()->all();
$id=[];
foreach($th as $val){
$id[]=$val->id;
}
$rows = $rows->readAll();
for ($i = $day; 0 < $i; $i--) {//time
$result[] = date('Y-m-d', strtotime('-' . $i . ' day'));
$data[] = 0;
}
// foreach($id as $v1=>$thirdid){//第三方id
// foreach($result as $value =>$item){ //日期
// $isexit=0; //0表示不存在 1表示存在
// foreach( $rows as $f =>$v){ //数据
// if($item==$v['datetime']&&$thirdid==$v['thirdid']){
// ${'money'.$v['thirdid']}[]=sprintf("%.2f",$v['money']/100);
// $isexit=1;
// break;
// }
// }
// if(!$isexit){
// ${'money'.$thirdid}[]=0;
// }
// }
// }
foreach($th as $v1=>$thirdid){//第三方id
${'money'.$thirdid->id} = $data;
}
foreach( $rows as $f =>$v){ //数据
$index = array_search($v['datetime'],$result);
${'money'.$v['thirdid']}[$index]=sprintf("%.2f",$v['money']/100);
}
function my_json_decode($str) {
$str = preg_replace('/"(\w+)"(\s*:\s*)/is', '$1$2', $str); //去掉key的双引号
return $str;
}
$color = array("#EE0000","#00FFFF","#712704","#43A102","#049FF1","#FFA500","#4C4C4C","#00CCFF","#72CFD7","#FF8C05");
$res= array();
foreach($id as $vv){
$fileinfo['label'] = IbdThirdInfo::findOne(['id'=>$vv])->name;
$fileinfo['fill'] = true;
$fileinfo['borderColor'] = $color[$vv%10];
$fileinfo['pointBackgroundColor'] = "#fff";
$fileinfo['data'] = ${'money'.$vv};
array_push($res, $fileinfo);
}
$a = json_encode($result);
$c = my_json_decode(json_encode($res));
// print_r($c);die;
return $this->render('drawthirdcaiwusum',['a'=>$a,'c'=>$c,'day'=>$day]);
}
EX1
EX1成果图：
1534411205(1).png
1534411214(1).png
EX1 html
<?php
use yii\widgets\LinkPager;
?>
<script type="text/javascript" src="/common/lib/layer.ext.js"></script>
<script src="/common/lib/admin/js/bootstrap.min.js"></script>
<script src="/common/lib/admin/js/chart.min.js"></script>
<script>
function datacount(){
window.location.href = '/guest/account/account-yihuo';
}
function search(){
window.location.href="/guest/account/caiwusum?dayselect="+$("#dayselect").val();
}
</script>
<div id="page-inner" style="padding: 30px;">
<div class="row">
<div class="col-md-12">
<h1 class="page-header">
财务统计图表
</h1>
</div>
<div class="panel-body">
<div class="dataTables_wrapper form-inline" style="margin-bottom: 15px;">
<select class="btn btn-default btn-sm" id="dayselect">
<option value = "7" <?= $day == 7?'selected' : ''?>>近7天</option>
<option value = "30" <?= $day == 30?'selected' : ''?>>近30天</option>
</select>
<button class="btn btn-default btn-sm" onclick="search()" title="点击搜索"><i class="fa fa-search"></i> <b>Search</b></button>
<button id="drawdatacount" onclick="datacount()" class="btn btn-sm btn-info">易货账务表格显示</button>
</div>
</div>
<div class="container">
<div class="row">
<div class="col-xs-2 "><canvas id="myChart"></canvas></div>
<div class="col-xs-8 "><canvas id="myChart1"></canvas></div>
</div>
</div>
</div>
</div>
<script>
var ctx = document.getElementById("myChart1");
var myChart = new Chart(ctx, {
type: 'line', // line 表示是 曲线图，当然也可以设置其他的图表类型 如柱形图 : bar 或者其他
data: {
labels : <?=$a?>, //按时间段 可以按星期，按月，按年
datasets : [
{
label: "交易量(元)", //当前数据的说明
fill: true, //是否要显示数据部分阴影面积块 false:不显示
borderColor: "#36A2EB",//数据曲线颜色
pointBackgroundColor: "#fff", //数据点的颜色
data: <?=$b?>, //填充的数据
},
{
label: "总后台佣金(元)", //当前数据的说明
fill: true, //是否要显示数据部分阴影面积块 false:不显示
borderColor: "#EE0000",//数据曲线颜色
pointBackgroundColor: "#fff", //数据点的颜色
data: <?=$c?>, //填充的数据
}
]
}
});
</script>
EX1Collecter
//财务折线图
public function actionCaiwusum()
{
if (Yii::$app->request->get("dayselect")) {
$day = Yii::$app->request->get("dayselect");
} else {
$day = 7;
}
$rows=Yii::$app->db->createCommand("select FROM_UNIXTIME(yocreatetime,'%Y-%m-%d') as datetime
,SUM(yototal) as money,SUM(yadminmoney) as commission
from ibd_oyihuo
where (ystatus = 1 or ystatus = 4) and yotype<20
and yocreatetime>=unix_timestamp(DATE_FORMAT(subdate(now(),$day),'%Y-%m-%d'))
and yocreatetime<unix_timestamp(DATE_FORMAT(now(),'%Y-%m-%d'))
group by FROM_UNIXTIME(yocreatetime,'%Y-%m-%d')
ORDER BY yocreatetime desc")->query();
for ($i = $day; 0 < $i; $i--) {
$result[] = date('Y-m-d', strtotime('-' . $i . ' day'));
$money[] = 0;
$commission[] = 0;
}
foreach($rows as $k => $v){
foreach($result as $value =>$item){
$index = array_search($v['datetime'],$result);
$money[$index] = sprintf("%.2f",$v['money']/100);
$commission[$index] = sprintf("%.2f",$v['commission']/100);
}
}
$a = json_encode($result);
$b = json_encode($money);
$c = json_encode($commission);
return $this->render('drawcaiwusum',['a'=>$a,'b'=>$b,'c'=>$c,'day'=>$day]);
}
主要要点：
1.php+mysql统计最近30天空值补0
1534411492(1).png
以EX1为例，2018/8/9号后数据为空，使用sql group by需要补0，网上有一种思路是leftjoin一张时间表数据默认为0。
查找最近的几天可以直接构建一个数组
for ($i = $day; 0 < $i; $i--) {//设定日期循环
$result[] = date('Y-m-d', strtotime('-' . $i . ' day'));//初始化时间，每次减1天
$money[] = 0;//初始化金额为0
$commission[] = 0;
}
然后，for循环取出从数据库拿到的数据，查找时间找出数组的位置，赋值给之前初始化为0的数组
foreach($rows as $k => $v){
foreach($result as $value =>$item){
$index = array_search($v['datetime'],$result);
$money[$index] = sprintf("%.2f",$v['money']/100);
$commission[$index] = sprintf("%.2f",$v['commission']/100);
}
}
这里写的有问题，首先$result是完整时间包含$rows从数据库找出的时间，应该调换过来，但会报错'DataReader cannot rewind. It is a forward-only reader.'要使用$rows = $rows->readAll();等填坑
$rows = $rows->readAll();
foreach($result as $value =>$item){
foreach($rows as $k => $v){
$index = array_search($v['datetime'],$result);
$money[$index] = sprintf("%.2f",$v['money']/100);
$commission[$index] = sprintf("%.2f",$v['commission']/100);
}
}
这样写也是没问题的。
后面传json给view就行了。
2.按第三方数量画折线
首先看chart.js的方法
datasets : [
{
label: "交易量(元)", //当前数据的说明
fill: true, //是否要显示数据部分阴影面积块 false:不显示
borderColor: "#36A2EB",//数据曲线颜色
pointBackgroundColor: "#fff", //数据点的颜色
data: <?=$b?>, //填充的数据
},
{
label: "总后台佣金(元)", //当前数据的说明
fill: true, //是否要显示数据部分阴影面积块 false:不显示
borderColor: "#EE0000",//数据曲线颜色
pointBackgroundColor: "#fff", //数据点的颜色
data: <?=$c?>, //填充的数据
}
]
思路：循环构建数组，转为json后，去掉key的双引号
其中变量名的递增可用用：${'money'.$vv}
function my_json_decode($str) {
$str = preg_replace('/"(\w+)"(\s*:\s*)/is', '$1$2', $str); //去掉key的双引号
return $str;
}
$color = array("#EE0000","#00FFFF","#712704","#43A102","#049FF1","#FFA500","#4C4C4C","#00CCFF","#72CFD7","#FF8C05");
$res= array();
foreach($id as $vv){
$fileinfo['label'] = IbdThirdInfo::findOne(['id'=>$vv])->name;
$fileinfo['fill'] = true;
$fileinfo['borderColor'] = $color[$vv%10];
$fileinfo['pointBackgroundColor'] = "#fff";
$fileinfo['data'] = ${'money'.$vv};
array_push($res, $fileinfo);
}
$a = json_encode($result);
$c = my_json_decode(json_encode($res));
html里直接输出
datasets : <?=$c?>
