hebei-chengben-car/modules/car/controllers/WaveController.php

<?php
namespace app\modules\car\controllers;
use AlibabaCloud\SDK\Dingtalk\Vindustry_1_0\Models\QueryAllGroupResponseBody\content;
use app\common\controllers\BController;
use app\models\Linkmenu;
use app\modules\car\models\FCarInfo;
use app\modules\car\models\FFlucCostHkm;
use Yii;
use yii\web\Link;

class WaveController extends BController
{
    public $layout = 'maininner';
    public function actionIndex()
    {
        $get = Yii::$app->request->get();
        $type = $get['type'];
        $start_month = 1;
        $end_month = $get['end_month']?$get['end_month']:12;
        //下面三个参数为查询提交
        $datatype = $get['datatype'];
        $year = $get['year'];
        $lastyear = $year-1;
        $city = $get['city'];
        if($city)
        {
            $cityInfo = Linkmenu::getMenuNameRs($city);
            $cityName = $cityInfo[0];
        }
        if(!empty($type))
        {
            //百公里费用波动(万元)
            $func = 'avg';
            if($type==1)
            {
                $tableName = 'wz_f_cost_fluctuation';
                $fieldName = 'all_costs_per_100_km';
                $unit = '元';
            }
            //百公里油耗波动(升)
            if($type==2)
            {
                $tableName = 'wz_f_cost_fluctuation';
                $fieldName = 'fuel_consumption_per_100_km';
                $unit = '升';
            }
            //单车费用波动(元)
            if($type==3)
            {
                $tableName = 'wz_f_cost_fluctuation';
                $fieldName = 'all_costs_sum';
                $unit = '元';
                $func = 'sum';
            }
            //运行成本波动(元)
            if($type==4)
            {
                $tableName = 'wz_f_cost_fluctuation';
                $fieldName = 'operating_cost_sum';
                $unit = '元';
                $func = 'sum';
            }
            //低效车辆波动
            if($type==5)
            {
                $tableName = 'wz_f_efficiency_fluctuation';
                $fieldName = 'inefficiency_count';
                $unit = '辆';
                $func = 'sum';
            }

            //行驶里程波动
            if($type==6)
            {
                $tableName = 'wz_f_efficiency_fluctuation';
                $fieldName = 'mileage_avg';
                $unit = '公里';
                $func = 'avg';
            }

            //出勤率波动
            if($type==7)
            {
                $tableName = 'wz_f_efficiency_fluctuation';
                $fieldName = 'attendance_rate_avg';
                $unit = '%';
                $func = 'avg';
            }


            //违规用车波动
            if($type==8)
            {
                $tableName = 'wz_f_illegal_vehicle_fluctuation';
                $fieldName = 'total';
                $unit = '辆';
                $func = 'sum';
            }

            //租赁车辆非正常费用波动
            if($type==9)
            {
                $tableName = 'wz_f_rental_vehicle_abnormal_charges_fluctuation';
                $fieldName = 'total_sum';
                $unit = '元';
                $func = 'sum';
            }

            //AJAX读取数据
            if (Yii::$app->request->isAjax || $_GET['docache']==1) {

                if(empty($cityName))
                {
                    $cacheFlag = 'wave_'.$type.'_'.$start_month.'_'.$end_month.'_'.$datatype.'_'.$year;
                    $cacheData = json_decode(Yii::$app->cache->get($cacheFlag),true);
                    if($_GET['docache']==1)$cacheData = null;
                }
                $cacheData = null;
                //月度
                if($datatype==1)
                {

                    if(empty($cacheData))
                    {

                        $dpts = Yii::$app->db->createCommand("select distinct(dpt_sec),city  from $tableName where dpt_sec!=''")->queryAll();
                        $sql = "select $func($fieldName) as finalresult,dpt_sec,month from $tableName where year=$year  and dpt_sec!='' group by dpt_sec,month";
                        $dptsRsults = Yii::$app->db->createCommand($sql)->queryAll();
                        if(is_array($dptsRsults))foreach($dptsRsults as $dptsRsult)
                        {
                            $dptsInfos[$dptsRsult['dpt_sec'].'_'.$dptsRsult['month']] = $dptsRsult['finalresult'];
                        }
                        //当年
                        $line1 = [];
                        for($i=1;$i<=$end_month;$i++)
                        {
                            if(empty($cityName))
                            {
                                $sql = "select $func($fieldName) from  $tableName where year=$year and month=$i";
                            }
                            else
                            {
                                $sql = "select $func($fieldName) from $tableName where city = '".$cityName."' and year=$year and month=$i";
                            }
                            $line1[] = round(Yii::$app->db->createCommand($sql)->queryScalar(),2);
                        }
                        //上一年
                        $line2 = [];
                        for($i=1;$i<=$end_month;$i++)
                        {
                            if(empty($cityName))
                            {
                                $sql = "select $func($fieldName) from $tableName where year=$lastyear and month=$i";
                            }
                            else
                            {
                                $sql = "select $func($fieldName) from $tableName where city = '".$cityName."' and year=$lastyear and month=$i";
                            }
                            $line2[] = round(Yii::$app->db->createCommand($sql)->queryScalar(),2);
                        }
                        //表格
                        $table = '';
                        $table .= "<table id='table' class='admin-table table dt-responsive nowrap dataTable no-footer dtr-inline table-bordered table-hover'>";
                        $table .= "<thead><tr>";
                        $table .= "<td style='text-align: center;font-weight:bold;'>地市</td>";
                        $table .= "<td style='text-align: center;font-weight:bold;'>二级单位</td>";
                        for($i=1;$i<=$end_month;$i++)
                        {
                            $table .= "<td style='text-align: center;font-weight:bold;'>".$i."月</td>";
                        }
                        $table .= "</tr></thead></table>";

                        $table .= "<div style='height: 250px;overflow-y:scroll;padding:0;'><table class='admin-table table dt-responsive nowrap dataTable no-footer dtr-inline table-bordered table-hover' style='margin:0 !important;'><tr class='treegrid-".md5('全省')." treegrid-expanded' data-id='".md5('全省')."'>";
                        $table .= "<td style='text-align: left;'><span class='treegrid-expander treegrid-expander-expanded'></span>全省</td>";
                        $table .= "<td style='text-align: center;'></td>";
                        for($i=1;$i<=$end_month;$i++)
                        {
                            $sql1 = "select count(*)  from $tableName where year=$year and month=$i";
                            $recordnum = Yii::$app->db->createCommand($sql1)->queryScalar();
                            if($recordnum==0)
                            {
                                $table .= "<td style='text-align: center;'>--</td>";
                            }
                            else
                            {
                                $sql = "select $func($fieldName) from $tableName where year=$year and month=$i";
                                $table .= "<td style='text-align: center;'>".round(Yii::$app->db->createCommand($sql)->queryScalar(),2)."</td>";
                            }
                        }
                        $table .= "</tr>";
                        foreach($this->cityOptions as $ucity)
                        {
                            if(!empty($cityName)&&$ucity!=$cityName)continue;
                            $table .= "<tr class='treegrid-".md5($ucity)." treegrid-parent-".md5('全省')." treegrid-collapsed' data-id='".md5($ucity)."'>";
                            $table .= "<td style='text-align: left;' ><span class='treegrid-expander treegrid-expander-collapsed'></span>&nbsp;&nbsp;".$ucity."</td>";
                            $table .= "<td></td>";
                            for($i=1;$i<=$end_month;$i++)
                            {
                                $sql1 = "select count(*)  from $tableName where city='".$ucity."' and year=$year and month=$i";
                                $recordnum = Yii::$app->db->createCommand($sql1)->queryScalar();
                                if($recordnum==0)
                                {
                                    $table .= "<td style='text-align: center;'>--</td>";
                                }
                                else
                                {
                                    $sql = "select $func($fieldName) from $tableName where city='".$ucity."' and year=$year and month=$i";
                                    $table .= "<td style='text-align: center;'>".round(Yii::$app->db->createCommand($sql)->queryScalar(),2)."</td>";
                                }
                            }
                            $table .= "</tr>";
                            //二级单位数据
                            if(!empty($dpts))
                            {
                                foreach($dpts as $udpt)
                                {
                                    if($udpt['city']!=$ucity)continue;
                                    $dpt = $udpt['dpt_sec'];
                                    $table .= "<tr class='treegrid-".md5($dpt)." treegrid-parent-".md5($ucity)." treegrid-collapsed' data-id='".md5($dpt)."' style='display:none;'>";
                                    $table .= "<td style='text-align: left;' ></td>";
                                    $table .= "<td>".$dpt."</td>";
                                    for($i=1;$i<=$end_month;$i++)
                                    {
                                        if(empty($dptsInfos[$dpt.'_'.$i]))
                                        {
                                            $table .= "<td style='text-align: center;'>--</td>";
                                        }
                                        else
                                        {
                                            $table .= "<td style='text-align: center;'>".round($dptsInfos[$dpt.'_'.$i],2)."</td>";
                                        }
                                    }
                                    $table .= "</tr>";
                                }
                            }
                        }

                        $table .= "</div></table>";
                        $result = array(
                            'error'=>'0',
                            'msg'=>'获取成功',
                            'year'=>$year,
                            'lastyear'=>$lastyear,
                            'line1'=>$line1,
                            'line2'=>$line2,
                            'table'=>$table,
                            'unit'=>$unit,
                            'dpts'=>$dpts,
                            'dptsInfos'=>$dptsInfos,
                        );
                        //生成缓存
                        if($_GET['docache']==1)
                        {
                            Yii::$app->cache->set($cacheFlag, json_encode($result),0);
                        }
                        echo_json($result);

                    }
                    else
                    {
                        echo_json($cacheData);
                    }

                }

                //累计
                if($datatype==2)
                {
                    if(empty($cacheData))
                    {
                        $dpts = Yii::$app->db->createCommand("select distinct(dpt_sec),city  from $tableName where dpt_sec!=''")->queryAll();
                        $sql = "select $func($fieldName) as finalresult ,dpt_sec from $tableName where year=$year and dpt_sec!='' group by dpt_sec";
                        $dptsRsults = Yii::$app->db->createCommand($sql)->queryAll();
                        if(is_array($dptsRsults))foreach($dptsRsults as $dptsRsult)
                        {
                            $dptsInfos[$dptsRsult['dpt_sec']] = $dptsRsult['finalresult'];
                        }

                        $sql = "select $func($fieldName) as finalresult ,dpt_sec from $tableName where year=$lastyear and dpt_sec!='' group by dpt_sec";
                        $dptsRsults = Yii::$app->db->createCommand($sql)->queryAll();
                        if(is_array($dptsRsults))foreach($dptsRsults as $dptsRsult)
                        {
                            $dptsInfosLast[$dptsRsult['dpt_sec']] = $dptsRsult['finalresult'];
                        }

                        //本年柱状
                        foreach($this->cityOptions as $ucity)
                        {
                            $sql = "select $func($fieldName) from $tableName where year=$year and city='".$ucity."'";
                            $line1[] = round(Yii::$app->db->createCommand($sql)->queryScalar(),2);
                        }
                        //上一年柱状
                        foreach($this->cityOptions as $ucity)
                        {
                            $sql = "select $func($fieldName) from $tableName where year=$lastyear and city='".$ucity."'";
                            $line2[] = round(Yii::$app->db->createCommand($sql)->queryScalar(),2);
                        }
                        //对比
                        foreach($line1 as $k=>$v)
                        {
                            if($line1[$k]>0)
                            {
                                $duibi = $line1[$k]>$line2[$k]?'+'.round(abs($line1[$k] - $line2[$k])/$line1[$k],2)*100:0-round(abs($line1[$k] - $line2[$k])/$line1[$k],2);
                                $line3[] = $duibi;
                            }
                            else
                            {
                                $line3[] = 0;
                            }

                        }
                        //表格
                        $table = '';
                        $table .= "<table id='table' class='admin-table table dt-responsive nowrap dataTable no-footer dtr-inline table-bordered table-hover'>";
                        $table .= "<thead><tr>";
                        $table .= "<td style='text-align: center;font-weight:bold;'>地市</td>";
                        $table .= "<td style='text-align: center;font-weight:bold;'>二级单位</td>";
                        $table .= "<td style='text-align: center;font-weight:bold;'>累计</td>";
                        $table .= "<td style='text-align: center;font-weight:bold;'>去年同期</td>";
                        $table .= "<td style='text-align: center;font-weight:bold;'>同比变动</td>";
                        $table .= "<td style='text-align: center;font-weight:bold;'>同比增幅</td>";
                        $table .= "</tr></thead></table>";
                        $table .= "<div style='height: 250px;overflow-y:scroll;padding:0;'><table class='admin-table table dt-responsive nowrap dataTable no-footer dtr-inline table-bordered table-hover' style='margin:0 !important;'><tr class='treegrid-".md5('全省')." treegrid-expanded' data-id='".md5('全省')."'>";
                        $table .= "<td style='text-align: center;'><span class='treegrid-expander treegrid-expander-expanded'></span>全省</td>";
                        $table .= "<td></td>";
                        $sql = "select $func($fieldName) from $tableName where year=$year";
                        $total1 = round(Yii::$app->db->createCommand($sql)->queryScalar(),2);
                        $table .= "<td style='text-align: center;'>".$total1."</td>";
                        $sql = "select $func($fieldName) from $tableName where year=$lastyear";
                        $total2 = round(Yii::$app->db->createCommand($sql)->queryScalar(),2);
                        $table .= "<td style='text-align: center;'>".round($total2,2)."</td>";
                        $table .= "<td style='text-align: center;'>".round($total1-$total2,2)."</td>";
                        $percent = !empty($total1)?round(round($total1-$total2,2)/$total1*100,2).'%':'';
                        $table .= "<td style='text-align: center;'>".$percent."</td>";
                        $table .= "</tr>";
                        foreach($this->cityOptions as $ucity)
                        {
                            if(!empty($cityName)&&$ucity!=$cityName)continue;
                            $table .= "<tr class='treegrid-".md5($ucity)." treegrid-parent-".md5('全省')." treegrid-collapsed' data-id='".md5($ucity)."'>";
                            $table .= "<td style='text-align: center;'><span class='treegrid-expander treegrid-expander-collapsed'></span>".$ucity."</td>";
                            $table .= "<td></td>";
                            $sql = "select $func($fieldName) from $tableName where year=$year and city='".$ucity."'";
                            $total1 = round(Yii::$app->db->createCommand($sql)->queryScalar(),2);
                            $table .= "<td style='text-align: center;'>".$total1."</td>";
                            $sql = "select $func($fieldName) from $tableName where year=$lastyear and city='".$ucity."'";
                            $total2 = round(Yii::$app->db->createCommand($sql)->queryScalar(),2);
                            $table .= "<td style='text-align: center;'>".round($total2,2)."</td>";
                            $table .= "<td style='text-align: center;'>".round($total1-$total2,2)."</td>";
                            $percent = !empty($total1)?round(round($total1-$total2,2)/$total1*100,2).'%':'';
                            $table .= "<td style='text-align: center;'>".$percent."</td>";
                            $table .= "</tr>";
                            //二级单位数据
                            if(!empty($dpts))
                            {
                                foreach($dpts as $udpt)
                                {
                                    if($udpt['city']!=$ucity)continue;
                                    $dpt = $udpt['dpt_sec'];
                                    $table .= "<tr class='treegrid-".md5($dpt)." treegrid-parent-".md5($ucity)." treegrid-collapsed' data-id='".md5($dpt)."' style='display:none;'>";
                                    $table .= "<td style='text-align: left;' ></td>";
                                    $table .= "<td>".$dpt."</td>";
                                    $total1 = round($dptsInfos[$dpt],2);
                                    $table .= "<td style='text-align: center;'>".$total1."</td>";
                                    $sql = "select $func($fieldName) from $tableName where year=$lastyear and city='".$ucity."'";
                                    $total2 = round($dptsInfosLast[$dpt],2);
                                    $table .= "<td style='text-align: center;'>".round($total2,2)."</td>";
                                    $table .= "<td style='text-align: center;'>".round($total1-$total2,2)."</td>";
                                    $percent = !empty($total1)?round(round($total1-$total2,2)/$total1*100,2).'%':'';
                                    $table .= "<td style='text-align: center;'>".$percent."</td>";
                                    $table .= "</tr>";
                                }
                            }



                        }
                        $table .= "</table></div>";


                        $result = array(
                            'error'=>'0',
                            'msg'=>'获取成功',
                            'year'=>$year,
                            'lastyear'=>$lastyear,
                            'line1'=>$line1,
                            'line2'=>$line2,
                            'line3'=>$line3,
                            'table'=>$table,
                            'cityOptions'=>$this->cityOptions,
                            'unit' => $unit,
                            'dpts'=>$dpts,
                            'dptsInfos'=>$dptsInfos,
                            'dptsInfosLast'=>$dptsInfosLast
                        );

                        //生成缓存
                        if($_GET['docache']==1)
                        {
                            Yii::$app->cache->set($cacheFlag, json_encode($result),0);
                        }
                        echo_json($result);
                    }
                    else
                    {
                        echo_json($cacheData);
                    }


                }


            }
            $koujing = '这里是口径描述';
            $fileNames = [
                '百公里费用波动',
                '百公里油耗波动',
                '单车费用波动',
                '运行成本波动',
                '低效车辆波动',
                '行驶里程波动',
                '出勤率波动',
                '违规用车波动',
                '租赁车辆非正常费用波动',
            ];
            //渲染视图
            return $this->render('wave',array('type'=>$type,'koujing'=>$koujing,'fileName'=>$fileNames[$type-1]));
        }
        else
        {
             $this->layout = 'main';
             return $this->render('index');
        }

    }



}