美文网首页
php+mysql统计最近30天空值补0，chart.js画折线

php+mysql统计最近30天空值补0，chart.js画折线

作者: AGEGG | 来源:发表于2018-08-16 18:12 被阅读0次

    需求：

    统计最近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="/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>
                &nbsp;&nbsp;
                <button class="btn btn-default btn-sm" onclick="search()" title="点击搜索"><i class="fa fa-search"></i>&nbsp;&nbsp;<b>Search</b></button>
                &nbsp;&nbsp;
                <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>
                &nbsp;&nbsp;
                <button class="btn btn-default btn-sm" onclick="search()" title="点击搜索"><i class="fa fa-search"></i>&nbsp;&nbsp;<b>Search</b></button>
                &nbsp;&nbsp;
                <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?>

    相关文章

      网友评论

          本文标题：php+mysql统计最近30天空值补0，chart.js画折线

          本文链接：https://www.haomeiwen.com/subject/wjyobftx.html

          延伸阅读

          深度阅读

          • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

          栏目导航

          热点阅读

          关于我们|服务条款|联系我们|php+mysql统计最近30天空值补0，chart.js画折线|投稿指南|网站地图|RSS订阅|排版工具|手机版

          提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

          Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

          备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

          本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

          所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！