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

<?php

namespace app\modules\car\controllers;

use app\common\controllers\BController;
use app\modules\car\models\FCarInfo;
use app\modules\car\models\FCarsDetailData;
use app\modules\car\models\FOneCarCost;
use app\modules\car\models\FOneCarOneTable;
use app\modules\car\models\FViolationDetails;
use Yii;

class FeeController extends BController
{
    public $layout = 'main';

    public function actionTotal()
    {
        $type = Yii::$app->request->get('type', 1);
        $koujing = '这里是口径描述';
        //车辆费用统计
        if ($type == 1) {

                $query = FOneCarOneTable::find()->alias('one')->leftJoin('{{%f_car_info}} as c', 'one.car_id = c.car_id');

                $params = Yii::$app->request->get();

                $where = $this->getCityWhere($params);
                //$where['is_special_car'] = 0;

                $_query =  clone $query;

            //账期
            if(!empty($params['statistical_month'])){
                $statistical_month = explode('-',$params['statistical_month']);
                $_where['year_info'] = intval($statistical_month[0]);
                $_where['month_info'] = intval($statistical_month[1]);
            }else{
                $res = FOneCarOneTable::find()->orderBy('year_info desc,month_info desc' )->asArray()->one();
                $_where['year_info'] = intval($res['year_info']);
                $_where['month_info'] = intval($res['month_info']);
            }
            $_query->where(['and',['=','year_info',intval($_where['year_info'])],['<=','month_info',intval($_where['month_info'])]]);
                //$_where['is_special_car'] = 0;
                $month = $_query->select('COUNT(DISTINCT month_info) as month_num,MAX(month_info) as month_info')->asArray()->one();
                $month_num = max(intval($month['month_num']),1);
                $month_info = max($month['month_info'],1);

                //if($_where['month_info']>$month_info) $_where['month_info'] = $month_info;

                $query->where($where);

                $sum_field = ['city'=>'city',
                    'sum_costs'=>'cast(sum("sum_costs") as decimal(10,2))',
                    'sum_driving'=>'cast(sum("oil_costs"+"repair_costs"+"road_bridge_costs"+"insurance_costs") as decimal(10,2))',
                    'sum_oil'=>'cast(sum("oil_costs") as decimal(10,2))',
                    'sum_repair'=>'cast(sum("repair_costs") as decimal(10,2))',
                    'sum_road_bridge'=>'cast(sum("road_bridge_costs") as decimal(10,2))',
                    'sum_rent'=>'cast(sum("rent_costs") as decimal(10,2))',
                    'sum_insurance'=>'cast(sum("insurance_costs") as decimal(10,2))',
                    'sum_year_check'=>'cast(sum("year_check_costs") as decimal(10,2))',
                    'sum_other'=>'cast(sum("other_costs") as decimal(10,2))'];
                $sum_data = ['sum_costs'=>0,'sum_driving'=>0,'sum_oil'=>0,'sum_repair'=>0,'sum_road_bridge'=>0,'sum_rent'=>0,'sum_insurance'=>0,'sum_year_check'=>0,'sum_other'=>0];



                $city_arr = [];
                $chart1data = [];
                $rows = [];
                $needgetall = false;

                if(empty($where['city'])){
                        $needgetall = true;
                        $citydata = $query->select(['city','sum_car'=>'count("one"."id")','rent_car'=>'sum(case when "self_rent"=\'租赁车辆\' then 1 else 0 end)'])->andWhere($_where)->groupBy('city')->orderBy($this->citySort)->asArray()->all();

                        $total = $sum_data;

                        $total_hash = md5('全省');
                        $res = $_query->select($sum_field)->groupBy('city')->asArray()->all();
                        $_citydata = [];
                        foreach ($res as $v) {
                            $hash = md5($v['city'].'_city');
                            $_citydata[$hash] = array_merge($v, ['hash' => $hash, 'parent_hash' => $total_hash]);
                        }
                        if($params['getall']==1) {
                            $needgetall = false;
                            $sum_field['city'] = 'dpt_sec';
                            $res = $_query->select($sum_field)->groupBy('dpt_sec')->asArray()->all();
                            $secdata = [];
                            foreach ($res as $v) {
                                $hash = md5($v['city'].'_dpt_sec');
                                $secdata[$hash] = $v;
                            }
                        }



                        foreach ($citydata as $v) {
                            if(!in_array($v['city'],$this->cityOptions)) break;

                            $hash = md5($v['city'].'_city');
                            $v['sum_car'] = intval($v['sum_car']);
                            $v['rent_car'] = intval($v['rent_car']);
                            if (isset($_citydata[$hash])) {
                                $v = array_merge($v, $_citydata[$hash]);
                            } else {
                                $v = array_merge($v, ['hash' => $hash, 'parent_hash' => $total_hash], $sum_data);
                            }
                            foreach ($total as $key => $_v) {
                                $total[$key] += $v[$key];
                            }
                            $v['sum_costs_bak'] = $v['sum_costs'];
                            foreach ($sum_data as $key => $_v){
                                $avg_key = str_replace('sum','avg',$key);
                                if($key == 'sum_rent'){
                                    $v[$avg_key] = $v['rent_car'] ? strval(round($v[$key]/$v['rent_car']/$month_num,2)) : strval('0.00');
                                }else{
                                    $v[$avg_key] = $v['sum_car'] ? strval(round($v[$key]/$v['sum_car']/$month_num,2)) : strval('0.00');
                                }
                                unset($v[$key]);
                            }
                            $v['sum_costs'] = strval(round($v['sum_costs_bak'],2));
                            unset($v['sum_costs_bak']);
                            $rows[] = $v;

                            $city_arr[$v['city']] = $v['avg_costs'];

                            $chart1data[] = ['value' => round($v['sum_costs']/10000), 'name' => $v['city']];
                            if($params['getall']==1) {
                                $res = $query->select(['city'=>'dpt_sec','sum_car'=>'count("one"."id")','rent_car'=>'sum(case when "self_rent"=\'租赁车辆\' then 1 else 0 end)'])->where(['city' => $v['city']])->andWhere($_where)->orderBy(['sum_car' => SORT_DESC])->groupBy('dpt_sec')->asArray()->all();
                                foreach ($res as $_k => $_v) {
                                    $hash = md5($_v['city'].'_dpt_sec');
                                    $_v['sum_car'] = intval($_v['sum_car']);
                                    $_v['rent_car'] = intval($_v['rent_car']);
                                    if (isset($secdata[$hash])) {
                                        $_v = array_merge($_v, $secdata[$hash]);
                                    } else {
                                        $_v = array_merge($_v, $sum_data);
                                    }
                                    $_v['hash'] = '';
                                    $_v['parent_hash'] = md5($v['city'].'_city');
                                    $_v['sum_costs_bak'] = $_v['sum_costs'];
                                    foreach ($sum_data as $key => $val) {
                                        $avg_key = str_replace('sum', 'avg', $key);
                                        if($key == 'sum_rent'){
                                            $_v[$avg_key] = $_v['rent_car'] ? strval(round($_v[$key] / $_v['rent_car']/$month_num, 2)) : strval('0.00');
                                        }else{
                                            $_v[$avg_key] = $_v['sum_car'] ? strval(round($_v[$key] / $_v['sum_car']/$month_num, 2)) : strval('0.00');
                                        }
                                        unset($_v[$key]);
                                    }
                                    $_v['sum_costs'] = strval(round($_v['sum_costs_bak'],2));
                                    unset($_v['sum_costs_bak']);
                                    $rows[] = $_v;
                                }
                            }else{
                                $_v = [];
                                $_v['hash'] = '';
                                $_v['parent_hash'] = md5($v['city'].'_city');
                                $_v = array_merge($_v, $sum_data);
                                $rows[] = $_v;
                            }
                        }
                        $total['city'] = '全省';
                        $total['hash'] = $total_hash;
                        $total['parent_hash'] = md5('全国');
                        $res = $query->select(['sum_car'=>'count("one"."id")','rent_car'=>'sum(case when "self_rent"=\'租赁车辆\' then 1 else 0 end)'])->filterWhere($_where)->groupBy('')->orderBy(['sum_car' => SORT_DESC])->asArray()->one();
                        $total['sum_car'] = $res['sum_car'];
                        $total['rent_car'] = $res['rent_car'];
                        foreach ($sum_data as $key => $val){
                            $avg_key = str_replace('sum','avg',$key);
                            if($key == 'sum_rent'){
                                $total[$avg_key] = $total['rent_car'] ? strval(round($total[$key]/$total['rent_car']/$month_num,2)) : strval('0.00');
                            }else{
                                $total[$avg_key] = $total['sum_car'] ? strval(round($total[$key]/$total['sum_car']/$month_num,2)) : strval('0.00');
                            }
                        }
                        array_unshift($rows, $total);


                    arsort($city_arr);
                    foreach ($city_arr as $k=>$v){
                            $xAxis[] = $k;
                            $series[] = $v;
                    }
                }else{
                    $citydata = $query->select(['city'=>'dpt_sec','sum_car'=>'count("one"."id")','rent_car'=>'sum(case when "self_rent"=\'租赁车辆\' then 1 else 0 end)'])->andWhere($_where)->orderBy(['sum_car' => SORT_DESC])->groupBy('dpt_sec')->asArray()->all();


                    $parent_hash = md5($where['city'].'_city');


                    $sum_field['city'] = 'dpt_sec';
                    $res = $_query->select($sum_field)->groupBy('dpt_sec')->asArray()->all();
                    $_citydata = [];
                    foreach ($res as $v) {
                        $hash = md5($v['city'].'_dpt_sec');
                        $_citydata[$hash] = array_merge($v, ['hash' => $hash, 'parent_hash' => $parent_hash]);
                    }



                    foreach ($citydata as $v) {

                        $hash = md5($v['city'].'_dpt_sec');
                        $v['sum_car'] = intval($v['sum_car']);
                        $v['rent_car'] = intval($v['rent_car']);
                        if (isset($_citydata[$hash])) {
                            $v = array_merge($v, $_citydata[$hash]);
                        } else {
                            $v = array_merge($v, ['hash' => $hash, 'parent_hash' => $parent_hash], $sum_data);
                        }

                        $v['sum_costs_bak'] = $v['sum_costs'];
                        foreach ($sum_data as $key => $_v){
                            $avg_key = str_replace('sum','avg',$key);
                            if($key == 'sum_rent'){
                                $v[$avg_key] = $v['rent_car'] ? strval(round($v[$key]/$v['rent_car']/$month_num,2)) : strval('0.00');
                            }else{
                                $v[$avg_key] = $v['sum_car'] ? strval(round($v[$key]/$v['sum_car']/$month_num,2)) : strval('0.00');
                            }
                            unset($v[$key]);
                        }
                        $v['sum_costs'] = strval(round($v['sum_costs_bak'],2));
                        unset($v['sum_costs_bak']);
                        $rows[] = $v;

                        $city_arr[$v['city']] = $v['avg_costs'];

                        $chart1data[] = ['value' => round($v['sum_costs']/10000), 'name' => $v['city']];

                    }

                    $res = $query->select(['city','sum_car'=>'count("one"."id")','rent_car'=>'sum(case when "self_rent"=\'租赁车辆\' then 1 else 0 end)'])->where($_where)->andWhere(['city'=>$where['city']])->groupBy('city')->orderBy(['sum_car' => SORT_DESC])->asArray()->one();
                    $sum_field['city'] = 'city';
                    $total = $_query->select($sum_field)->andWhere(['city'=>$where['city']])->groupBy('city')->asArray()->one();
                    $total['sum_car'] = $res['sum_car'];
                    $total['rent_car'] = $res['rent_car'];
                    foreach ($sum_data as $key => $val){
                        $avg_key = str_replace('sum','avg',$key);
                        if($key == 'sum_rent'){
                            $total[$avg_key] = $total['rent_car'] ? strval(round($total[$key]/$total['rent_car']/$month_num,2)) : strval('0.0');
                        }else{
                            $total[$avg_key] = $total['sum_car'] ? strval(round($total[$key]/$total['sum_car']/$month_num,2)) : strval('0.0');
                        }
                        if($key!='sum_costs') unset($total[$key]);
                    }
                    $total['hash'] = $parent_hash;
                    $total['parent_hash'] = md5('全省');
                    array_unshift($rows, $total);


                    arsort($city_arr);
                    foreach ($city_arr as $k=>$v){
                        $xAxis[] = $k;
                        $series[] = $v;
                    }
                }


                $result = ["total" => null, "totalNotFiltered" => null, "rows" => $rows, 'needgetall'=> $needgetall,'date'=>$_where];


                $result['chartdata'] = [
                    'chart1' => $this->getChart1Option([
                        'title' => [
                            'text' => '各地市车辆总费用占比',
                        ],
                        'series' => [
                            [
                                'name' => '车辆总费用(万元)',
                                'data' => $chart1data
                            ]
                        ]
                    ]),
                    'chart2' => $this->getChart2Option([
                        'title' => [
                            'text' => '单车月费用（元）',
                        ],
                        'xAxis' => [
                            [
                                'data' => $xAxis,
                            ]
                        ],
                        'series' => [
                            [
                                'name' => '单车月平均费用（元）',
                                'data' => $series,
                            ]
                        ]
                    ])
                ];
               //Yii::$app->cache->set($cachekey,$result,3600);
               if (Yii::$app->request->isAjax) echo_json($result);

            $this->tableTitle = array(
                    array('field' => 'city', 'title' => '地市', 'align' => 'left'),
                    array('field' => 'sum_car', 'title' => '车辆总数量', 'align' => 'right', 'formatter'=>'numericFormatter'),
                    array('field' => 'sum_costs', 'title' => '车辆总费用', 'align' => 'right', 'formatter'=>'numeric1Formatter'),
                    array('field' => 'avg_costs', 'title' => '单车费用平均值', 'align' => 'right', 'formatter'=>'numeric1Formatter'),
                    array('field' => 'avg_driving', 'title' => '单车运行费用平均值', 'align' => 'right', 'formatter'=>'numeric1Formatter'),
                    array('field' => 'avg_oil', 'title' => '单车油料费平均值', 'align' => 'right', 'formatter'=>'numeric1Formatter'),
                    array('field' => 'avg_repair', 'title' => '单车维修费用平均值', 'align' => 'right', 'formatter'=>'numeric1Formatter'),
                    array('field' => 'avg_road_bridge', 'title' => '单车路桥费平均值', 'align' => 'right', 'formatter'=>'numeric1Formatter'),
                    array('field' => 'avg_rent', 'title' => '单车租赁费平均值', 'align' => 'right', 'formatter'=>'numeric1Formatter'),
                    //array('field' => 'avg_insurance', 'title' => '单车保险费平均值', 'align' => 'right', 'formatter'=>'numeric1Formatter'),
                    //array('field' => 'avg_year_check', 'title' => '单车年检费平均值', 'align' => 'right', 'formatter'=>'numeric1Formatter'),
                    array('field' => 'avg_other', 'title' => '单车其他费用平均值', 'align' => 'right', 'formatter'=>'numeric1Formatter'),
            );
            $this->tableConfig = array('table' => 'total_costs', 'url' => $this->createRealUrl(['car/fee/total', 'type' => $type]), 'setFieldUrl' => $this->createRealUrl(['car/fee/setfield']), 'idField' => 'hash', 'checkbox' => 0, 'dropmenu' => 1, 'refresh' => true, 'fixed' => true, 'fixedNum' => 3, 'fixedRightNumber' => 0, 'tree' => 1, 'parentIdField' => 'parent_hash', 'treeShowField' => 'city', 'treeColumn' => 0, 'expand' => false, 'exportFileName' => '车辆费用统计', 'height' => 500);
            $this->result = $result;
            $koujing = '车辆费用=燃油费+维修费+路桥费+租赁费+保险费+年检费+其他费用';

        }
        //运行费用统计
        if ($type == 2) {

            $query = FOneCarOneTable::find()->alias('one')->leftJoin('{{%f_car_info}} as c', 'one.car_id = c.car_id');

            $params = Yii::$app->request->get();

            $where = $this->getCityWhere($params);
            //$where['is_special_car'] = 0;

            $_query =  clone $query;

            //账期
            if(!empty($params['statistical_month'])){
                $statistical_month = explode('-',$params['statistical_month']);
                $_where['year_info'] = intval($statistical_month[0]);
                $_where['month_info'] = intval($statistical_month[1]);
            }else{
                $res = FOneCarOneTable::find()->orderBy('year_info desc,month_info desc' )->asArray()->one();
                $_where['year_info'] = intval($res['year_info']);
                $_where['month_info'] = intval($res['month_info']);
            }
            $_query->where(['and',['=','year_info',intval($_where['year_info'])],['<=','month_info',intval($_where['month_info'])]]);
            //$_where['is_special_car'] = 0;
            //$month = $_query->select('COUNT(DISTINCT month_info) as month_num,MAX(month_info) as month_info')->asArray()->one();
            //$month_num = max(intval($month['month_num']),2);
            //$month_info = max($month['month_info'],2);

            //if($_where['month_info']>$month_info) $_where['month_info'] = $month_info;

            $query->where($where);

            $sum_field = ['city'=>'city',
                'sum_costs'=>'cast(sum("sum_costs") as decimal(10,2))',
                'sum_driving'=>'cast(sum("oil_costs"+"repair_costs"+"road_bridge_costs"+"insurance_costs") as decimal(10,2))',
                'sum_oil'=>'cast(sum("oil_costs") as decimal(10,2))',
                'sum_repair'=>'cast(sum("repair_costs") as decimal(10,2))',
                'sum_road_bridge'=>'cast(sum("road_bridge_costs") as decimal(10,2))',
                'sum_insurance'=>'cast(sum("insurance_costs") as decimal(10,2))',
                ];
            $sum_data = ['sum_costs'=>0,'sum_driving'=>0,'sum_oil'=>0,'sum_repair'=>0,'sum_road_bridge'=>0,'sum_insurance'=>0];


            $city_arr = [];
            $chart1data = [];
            $rows = [];
            $needgetall = false;

            if(empty($where['city'])){
                $needgetall = true;
                $citydata = $query->select(['city','sum_car'=>'count("one"."id")'])->andWhere($_where)->groupBy('city')->orderBy($this->citySort)->asArray()->all();

                $total = $sum_data;

                $total_hash = md5('全省');
                $res = $_query->select($sum_field)->groupBy('city')->asArray()->all();
                $_citydata = [];
                foreach ($res as $v) {
                    $hash = md5($v['city'].'_city');
                    $_citydata[$hash] = array_merge($v, ['hash' => $hash, 'parent_hash' => $total_hash]);
                }
                if($params['getall']==1) {
                    $needgetall = false;
                    $sum_field['city'] = 'dpt_sec';
                    $res = $_query->select($sum_field)->groupBy('dpt_sec')->asArray()->all();
                    $secdata = [];
                    foreach ($res as $v) {
                        $hash = md5($v['city'].'_dpt_sec');
                        $secdata[$hash] = $v;
                    }
                }


                foreach ($citydata as $v) {
                    if(!in_array($v['city'],$this->cityOptions)) break;

                    $hash = md5($v['city'].'_city');
                    if (isset($_citydata[$hash])) {
                        $v = array_merge($v, $_citydata[$hash]);
                    } else {
                        $v = array_merge($v, ['hash' => $hash, 'parent_hash' => $total_hash], $sum_data);
                    }
                    foreach ($total as $key => $_v) {
                        $total[$key] += $v[$key];
                    }
                    $v['per_driving'] = sprintf("%.1f",$v['sum_driving']/$v['sum_costs']*100);

                    $city_arr[$v['city']] = $v['per_driving'];

                    $v['per_driving'] .= '%';

                    $rows[] = $v;

                    $chart1data[] = ['value' => round($v['sum_driving']/10000), 'name' => $v['city']];
                    if($params['getall']==1) {
                        $res = $query->select(['city'=>'dpt_sec','sum_car'=>'count("one"."id")'])->where(['city' => $v['city']])->andWhere($_where)->orderBy(['sum_car' => SORT_DESC])->groupBy('dpt_sec')->asArray()->all();
                        foreach ($res as $_k => $_v) {
                            $hash = md5($_v['city'].'_dpt_sec');
                            if (isset($secdata[$hash])) {
                                $_v = array_merge($_v, $secdata[$hash]);
                            } else {
                                $_v = array_merge($_v, $sum_data);
                            }
                            $_v['hash'] = '';
                            $_v['parent_hash'] = md5($v['city'].'_city');

                            $_v['per_driving'] = sprintf("%.1f",$_v['sum_driving']/$_v['sum_costs']*100).'%';
                            $rows[] = $_v;
                        }
                    }else{
                        $_v = [];
                        $_v['hash'] = '';
                        $_v['parent_hash'] = md5($v['city'].'_city');
                        $_v = array_merge($_v, $sum_data);
                        $rows[] = $_v;
                    }
                }
                $total['city'] = '全省';
                $total['hash'] = $total_hash;
                $res = $query->select(['sum_car'=>'count("one"."id")'])->filterWhere($_where)->groupBy('')->orderBy(['sum_car' => SORT_DESC])->asArray()->one();
                $total['sum_car'] = $res['sum_car'];
                $total['parent_hash'] = md5('全国');
                $total['per_driving'] = sprintf("%.1f",$total['sum_driving']/$total['sum_costs']*100).'%';
                array_unshift($rows, $total);


                arsort($city_arr);
                foreach ($city_arr as $k=>$v){
                    $xAxis[] = $k;
                    $series[] = $v;
                }
            }else{
                $citydata = $query->select(['city'=>'dpt_sec','sum_car'=>'count("one"."id")'])->andWhere($_where)->orderBy(['sum_car' => SORT_DESC])->groupBy('dpt_sec')->asArray()->all();


                $parent_hash = md5($where['city'].'_city');


                $sum_field['city'] = 'dpt_sec';
                $res = $_query->select($sum_field)->groupBy('dpt_sec')->asArray()->all();
                $_citydata = [];
                foreach ($res as $v) {
                    $hash = md5($v['city'].'_dpt_sec');
                    $_citydata[$hash] = array_merge($v, ['hash' => $hash, 'parent_hash' => $parent_hash]);
                }



                foreach ($citydata as $v) {

                    $hash = md5($v['city'].'_dpt_sec');

                    if (isset($_citydata[$hash])) {
                        $v = array_merge($v, $_citydata[$hash]);
                    } else {
                        $v = array_merge($v, ['hash' => $hash, 'parent_hash' => $parent_hash], $sum_data);
                    }

                    $v['per_driving'] = sprintf("%.1f",$v['sum_driving']/$v['sum_costs']*100);

                    $city_arr[$v['city']] = $v['per_driving'];

                    $v['per_driving'] .= '%';
                    $rows[] = $v;

                    $chart1data[] = ['value' => round($v['sum_driving']/10000), 'name' => $v['city']];
                }

                $sum_field['city'] = 'city';
                $total = $_query->select($sum_field)->andWhere(['city'=>$where['city']])->groupBy('city')->asArray()->one();
                $res = $query->select(['sum_car'=>'count("one"."id")'])->filterWhere($_where)->andWhere(['city'=>$where['city']])->groupBy('city')->orderBy(['sum_car' => SORT_DESC])->asArray()->one();
                $total['sum_car'] = $res['sum_car'];
                $total['hash'] = $parent_hash;
                $total['parent_hash'] = md5('全省');
                $total['per_driving'] = sprintf("%.1f",$total['sum_driving']/$total['sum_costs']*100).'%';

                array_unshift($rows, $total);

                arsort($city_arr);
                foreach ($city_arr as $k=>$v){
                    $xAxis[] = $k;
                    $series[] = $v;
                }
            }


            $result = ["total" => null, "totalNotFiltered" => null, "rows" => $rows, 'needgetall'=> $needgetall,'date'=>$_where];


            $result['chartdata'] = [
                'chart1' => $this->getChart1Option([
                    'title' => [
                        'text' => '运行费地市占比',
                    ],
                    'series' => [
                        [
                            'name' => '运行费(万元)',
                            'data' => $chart1data
                        ]
                    ]
                ]),
                'chart2' => $this->getChart2Option([
                    'title' => [
                        'text' => '运行费占车辆费用比例',
                    ],
                    'xAxis' => [
                        [
                            'data' => $xAxis,
                        ]
                    ],
                    'series' => [
                        [
                            'name' => '运行费占比(%)',
                            'data' => $series,
                        ]
                    ]
                ])
            ];

            if (Yii::$app->request->isAjax) echo_json($result);

            $this->tableTitle = array(
                array('field' => 'city', 'title' => '地市', 'align' => 'left'),
                array('field' => 'sum_car', 'title' => '车辆总数量', 'align' => 'right', 'formatter'=>'numericFormatter'),
                array('field' => 'sum_costs', 'title' => '车辆总费用', 'align' => 'right', 'formatter'=>'numeric1Formatter'),
                array('field' => 'sum_oil', 'title' => '油料费', 'align' => 'right', 'formatter'=>'numeric1Formatter'),
                array('field' => 'sum_insurance', 'title' => '保险费', 'align' => 'right', 'formatter'=>'numeric1Formatter'),
                array('field' => 'sum_repair', 'title' => '维修费用', 'align' => 'right', 'formatter'=>'numeric1Formatter'),
                array('field' => 'sum_road_bridge', 'title' => '路桥费', 'align' => 'right', 'formatter'=>'numeric1Formatter'),
                array('field' => 'sum_driving', 'title' => '运行费合计', 'align' => 'right', 'formatter'=>'numeric1Formatter'),
                array('field' => 'per_driving', 'title' => '运行费占总费用比例', 'align' => 'right'),
            );

            $this->tableConfig = array('table' => 'costs_driving', 'url' => $this->createRealUrl(['car/fee/total', 'type' => $type]), 'setFieldUrl' => $this->createRealUrl(['car/fee/setfield']), 'idField' => 'hash', 'checkbox' => 0, 'dropmenu' => 1, 'refresh' => true, 'fixed' => true, 'fixedNum' => 3, 'fixedRightNumber' => 0, 'tree' => 1, 'parentIdField' => 'parent_hash', 'treeShowField' => 'city', 'treeColumn' => 0, 'expand' => false, 'exportFileName' => '运行费用统计', 'height' => 500);
            $this->result = $result;
            $koujing = '运行费=燃油费+保险费+维修费+路桥费';

        }

        //单车费用详单
        if ($type == 3) {
            //$query = FCarsDetailData::find();
            $query = FOneCarOneTable::find()->alias('one')->leftJoin('{{%f_car_info}} as c', 'one.car_id = c.car_id');
            if (Yii::$app->request->isAjax) {

                $params = Yii::$app->request->get();

                $where = $this->getCityWhere($params);
                //账期
                if(!empty($params['statistical_month'])){
                    $statistical_month = explode('-',$params['statistical_month']);
                    $_where['year_info'] = intval($statistical_month[0]);
                    $_where['month_info'] = intval($statistical_month[1]);
                }else{
                    $res = FOneCarOneTable::find()->orderBy('year_info desc,month_info desc' )->asArray()->one();
                    $_where['year_info'] = intval($res['year_info']);
                    $_where['month_info'] = intval($res['month_info']);
                }


                //车牌
                if(!empty($params['card_num'])){
                    $where['card_num'] = $params['card_num'];
                }
                if ($params['card_num']!=$params['card_num_text']){
                    unset($where['card_num']);
                    $query->where($where)->andWhere(['like','card_num',strtoupper($params['card_num_text'])]);
                }else{
                    $query->where($where);
                }

                $query->andWhere(['and',['=','year_info',intval($_where['year_info'])],['<=','month_info',intval($_where['month_info'])]]);
                $query->select(['one.car_id',
                    'year_info'=>'max("year_info")',
                    'month_info'=>'max("month_info")',
                    'oil_costs'=>'sum("oil_costs")',
                    'repair_costs'=>'sum("repair_costs")',
                    'insurance_costs'=>'sum("insurance_costs")',
                    'rent_costs'=>'sum("rent_costs")',
                    'year_check_costs'=>'sum("year_check_costs")',
                    'road_bridge_costs'=>'sum("road_bridge_costs")',
                    'other_costs'=>'sum("other_costs")',
                    'attend_days'=>'sum("attend_days")',
                    'work_days'=>'sum("work_days")',
                    'sum_mileage'=>'sum("sum_mileage")',
                ])->groupBy('one.car_id');
                $sql = $query->createCommand()->getRawSql();

                $query = FCarInfo::find()->alias('car')->leftJoin("({$sql}) as os", 'car.car_id = os.car_id');

                if ($params['card_num']!=$params['card_num_text']){
                    unset($where['card_num']);
                    $query->where($where)->andWhere(['like','card_num',$params['card_num_text']]);
                }else{
                    $query->where($where);
                }

                $query->andWhere(['>','os.car_id',0]);

                $countQuery = clone $query;
                //分页
                if (isset($_GET['limit'])) {
                    $query->limit(intval($_GET['limit']));
                }
                if (isset($_GET['offset'])) {
                    $query->offset(intval($_GET['offset']));
                }
                $field = ['os.*','car.*','car_age'=>'age(CURRENT_DATE, car.enabled_date::date)'];

                //排序
                if (isset($_GET['sort']) && isset($_GET['sortOrder'])) {
                    $resultList = $query->select($field)->orderBy([$_GET['sort'] => ($_GET['sortOrder'] == 'asc' ? SORT_ASC : SORT_DESC)])->asArray()->all();
                } else {
                    $resultList = $query->select($field)->orderBy(['sum_mileage' => SORT_DESC])->asArray()->all();
                }

                foreach ($resultList as &$v){
                    $v['car_age'] = str_replace(['years','mons','days','year','mon','day',' '],['年','月','天','年','月','天',''],$v['car_age']);
                }

                $result = ["total" => $countQuery->count(), "totalNotFiltered" => $countQuery->count(), "rows" => $resultList];
                echo_json($result);
            }

            $this->tableTitle = array(
                //array('field' => 'statistical_month', 'title' => '账期', 'align' => 'center', 'sortable' => true),
                array('field' => 'card_num', 'title' => '车牌号', 'align' => 'center', 'sortable' => true),
                //array('field' => 'own_dpt', 'title' => '车辆所属单位', 'align' => 'center', 'sortable' => true),
                array('field' => 'city', 'title' => '单位', 'align' => 'center', 'sortable' => true),
                array('field' => 'dpt_sec', 'title' => '二级单位', 'align' => 'center', 'sortable' => true),
                array('field' => 'grid', 'title' => '三级单位', 'align' => 'center', 'sortable' => true),
                array('field' => 'self_rent', 'title' => '车辆来源', 'align' => 'center', 'sortable' => true),
                array('field' => 'car_type', 'title' => '车辆类型', 'align' => 'center', 'sortable' => true),
                array('field' => 'using_tag', 'title' => '车辆使用性质', 'align' => 'center', 'sortable' => true),
                array('field' => 'oil_costs', 'title' => '燃油', 'align' => 'right', 'formatter'=>'numeric1Formatter', 'sortable' => true,),
                array('field' => 'repair_costs', 'title' => '维修', 'align' => 'right', 'formatter'=>'numeric1Formatter', 'sortable' => true,),
                array('field' => 'insurance_costs', 'title' => '保险', 'align' => 'right', 'formatter'=>'numeric1Formatter', 'sortable' => true,),
                array('field' => 'rent_costs', 'title' => '租赁', 'align' => 'right', 'formatter'=>'numeric1Formatter', 'sortable' => true,),
                array('field' => 'year_check_costs', 'title' => '年检费', 'align' => 'right', 'formatter'=>'numeric1Formatter', 'sortable' => true,),
                array('field' => 'road_bridge_costs', 'title' => '路桥', 'align' => 'right', 'formatter'=>'numeric1Formatter', 'sortable' => true,),
                array('field' => 'other_costs', 'title' => '其他', 'align' => 'right', 'formatter'=>'numeric1Formatter', 'sortable' => true,),
                //array('field' => 'driving_time', 'title' => '行驶时长', 'align' => 'right', 'formatter'=>'numeric1Formatter', 'sortable' => true,),
                array('field' => 'attend_days', 'title' => '行驶天数', 'align' => 'right', 'formatter'=>'numericFormatter', 'sortable' => true,),
                array('field' => 'sum_mileage', 'title' => '里程', 'align' => 'right', 'formatter'=>'numeric1Formatter', 'sortable' => true,),
                array('field' => 'car_brand', 'title' => '车辆品牌', 'align' => 'center', 'sortable' => true),
                array('field' => 'emissions', 'title' => '排量', 'align' => 'center', 'sortable' => true),
                array('field' => 'emissions_type', 'title' => '排量类型', 'align' => 'center', 'sortable' => true),
                array('field' => 'oil_type', 'title' => '油料类型', 'align' => 'center', 'sortable' => true),
                array('field' => 'enabled_date', 'title' => '登记日期', 'align' => 'center', 'sortable' => true),
                array('field' => 'car_age', 'title' => '车龄', 'align' => 'center', 'sortable' => true),
                //array('field' => 'env_lev', 'title' => '环保等级', 'align' => 'center', 'sortable' => true),
                //array('field' => 'engine_id', 'title' => '发动机号', 'align' => 'center', 'sortable' => true),
                //array('field' => 'fra_id', 'title' => '车架号', 'align' => 'center', 'sortable' => true),
                //array('field' => 'init_mileage', 'title' => '初始里程', 'align' => 'center', 'sortable' => true),
                //array('field' => 'driving_id', 'title' => '行驶证号', 'align' => 'center', 'sortable' => true),
                //array('field' => 'car_state', 'title' => '车辆状态', 'align' => 'center', 'sortable' => true),
                //array('field' => 'asset_id', 'title' => '资产编号', 'align' => 'center', 'sortable' => true),
            );

            $this->tableConfig = array('table' => "fone_car_one_table", 'url' => $this->createRealUrl(['car/fee/total', 'type' => $type]), 'setFieldUrl' => $this->createRealUrl(['car/fee/setfield']), 'idField' => "id", 'checkbox' => 0, 'dropmenu' => 1, 'pagination' => true, 'pagesize' => 20, 'refresh' => true, 'exportFileName' => '单车费用详单', 'height' => 500, 'tableClass' => 'table-nowrap');

        }


        return $this->render('total', array('type' => $type, 'koujing' => $koujing));
    }

    public function actionMiles100()
    {
        $type = Yii::$app->request->get('type', 1);
        $koujing = '这里是口径描述';
        //百公里费用统计
        if ($type == 1) {
            $query = FOneCarOneTable::find()->alias('one')->leftJoin('{{%f_car_info}} as c', 'one.car_id = c.car_id');

            $params = Yii::$app->request->get();

            $where = $this->getCityWhere($params);
            //$where['is_special_car'] = 0;

            $_query =  clone $query;

            //账期
            if(!empty($params['statistical_month'])){
                $statistical_month = explode('-',$params['statistical_month']);
                $_where['year_info'] = intval($statistical_month[0]);
                $_where['month_info'] = intval($statistical_month[1]);
            }else{
                $res = FOneCarOneTable::find()->orderBy('year_info desc,month_info desc' )->asArray()->one();
                $_where['year_info'] = intval($res['year_info']);
                $_where['month_info'] = intval($res['month_info']);
            }
            $_query->where(['and',['=','year_info',intval($_where['year_info'])],['<=','month_info',intval($_where['month_info'])]]);
            //$_where['is_special_car'] = 0;
            //$month = $_query->select('COUNT(DISTINCT month_info) as month_num,MAX(month_info) as month_info')->asArray()->one();
            //$month_num = max(intval($month['month_num']),2);
            //$month_info = max($month['month_info'],2);

            //if($_where['month_info']>$month_info) $_where['month_info'] = $month_info;

            $query->where($where);

            $sum_field = ['city'=>'city',
                'sum_costs'=>'cast(sum("sum_costs") as decimal(10,2))',
                'sum_driving'=>'cast(sum("oil_costs"+"repair_costs"+"road_bridge_costs"+"insurance_costs") as decimal(10,2))',
                'sum_oil'=>'cast(sum("oil_costs") as decimal(10,2))',
                'sum_repair'=>'cast(sum("repair_costs") as decimal(10,2))',
                'sum_road_bridge'=>'cast(sum("road_bridge_costs") as decimal(10,2))',
                'sum_rent'=>'cast(sum("rent_costs") as decimal(10,2))',
                'sum_insurance'=>'cast(sum("insurance_costs") as decimal(10,2))',
                'sum_year_check'=>'cast(sum("year_check_costs") as decimal(10,2))',
                'sum_other'=>'cast(sum("other_costs") as decimal(10,2))',
                'sum_mileage'=>'cast(sum("sum_mileage") as decimal(10,2))',
            ];
            $sum_data = ['sum_costs'=>0,'sum_driving'=>0,'sum_oil'=>0,'sum_repair'=>0,'sum_road_bridge'=>0,'sum_rent'=>0,'sum_insurance'=>0,'sum_year_check'=>0,'sum_other'=>0,'sum_mileage'=>0];



            $city_arr = [];
            $chart1data = [];
            $rows = [];
            $needgetall = false;

            if(empty($where['city'])){
                $needgetall = true;
                $citydata = $query->select(['city','sum_car'=>'count("one"."id")'])->andWhere($_where)->groupBy('city')->orderBy($this->citySort)->asArray()->all();

                $total = $sum_data;

                $total_hash = md5('全省');
                $res = $_query->select($sum_field)->groupBy('city')->asArray()->all();
                $_citydata = [];
                foreach ($res as $v) {
                    $hash = md5($v['city'].'_city');
                    $_citydata[$hash] = array_merge($v, ['hash' => $hash, 'parent_hash' => $total_hash]);
                }
                if($params['getall']==1) {
                    $needgetall = false;
                    $sum_field['city'] = 'dpt_sec';
                    $res = $_query->select($sum_field)->groupBy('dpt_sec')->asArray()->all();
                    $secdata = [];
                    foreach ($res as $v) {
                        $hash = md5($v['city'].'_dpt_sec');
                        $secdata[$hash] = $v;
                    }
                }


                foreach ($citydata as $v) {
                    if(!in_array($v['city'],$this->cityOptions)) break;

                    $hash = md5($v['city'].'_city');
                    if (isset($_citydata[$hash])) {
                        $v = array_merge($v, $_citydata[$hash]);
                    } else {
                        $v = array_merge($v, ['hash' => $hash, 'parent_hash' => $total_hash], $sum_data);
                    }

                    if($v['city']=='省公司本部'){
                        $v['oil_cost_hkm'] = sprintf("%.1f",$total['sum_oil']/$total['sum_mileage']*100);
                        $v['driving_cost_hkm'] = sprintf("%.1f",$total['sum_driving']/$total['sum_mileage']*100);
                        $v['cost_hkm'] = sprintf("%.1f",$total['sum_costs']/$total['sum_mileage']*100);
                    }else{
                        $v['oil_cost_hkm'] = sprintf("%.1f",$v['sum_oil']/$v['sum_mileage']*100);
                        $v['driving_cost_hkm'] = sprintf("%.1f",$v['sum_driving']/$v['sum_mileage']*100);
                        $v['cost_hkm'] = sprintf("%.1f",$v['sum_costs']/$v['sum_mileage']*100);
                    }

                    foreach ($total as $key => $_v) {
                        $total[$key] += $v[$key];
                    }
                    $city_arr[$v['city']] = $v['cost_hkm'];

                    $v['per_driving'] .= '%';

                    $rows[] = $v;

                    //$chart1data[] = ['value' => $v['sum_driving'], 'name' => $v['city']];
                    if($params['getall']==1) {
                        $res = $query->select(['city'=>'dpt_sec','sum_car'=>'count("one"."id")'])->where(['city' => $v['city']])->andWhere($_where)->orderBy(['sum_car' => SORT_DESC])->groupBy('dpt_sec')->asArray()->all();
                        foreach ($res as $_k => $_v) {
                            $hash = md5($_v['city'].'_dpt_sec');
                            if (isset($secdata[$hash])) {
                                $_v = array_merge($_v, $secdata[$hash]);
                            } else {
                                $_v = array_merge($_v, $sum_data);
                            }
                            $_v['hash'] = '';
                            $_v['parent_hash'] = md5($v['city'].'_city');

                            if($_v['city']=='省公司本部'){
                                $_v['oil_cost_hkm'] = $v['oil_cost_hkm'];
                                $_v['driving_cost_hkm'] = $v['driving_cost_hkm'];
                                $_v['cost_hkm'] = $v['cost_hkm'];
                            }else{
                                $_v['oil_cost_hkm'] = sprintf("%.1f",$_v['sum_oil']/$_v['sum_mileage']*100);
                                $_v['driving_cost_hkm'] = sprintf("%.1f",$_v['sum_driving']/$_v['sum_mileage']*100);
                                $_v['cost_hkm'] = sprintf("%.1f",$_v['sum_costs']/$_v['sum_mileage']*100);
                            }

                            $rows[] = $_v;
                        }
                    }else{
                        $_v = [];
                        $_v['hash'] = '';
                        $_v['parent_hash'] = md5($v['city'].'_city');
                        $_v = array_merge($_v, $sum_data);
                        $rows[] = $_v;
                    }
                }
                $total['city'] = '全省';
                $total['hash'] = $total_hash;
                $res = $query->select(['sum_car'=>'count("one"."id")'])->filterWhere($_where)->groupBy('')->orderBy(['sum_car' => SORT_DESC])->asArray()->one();
                $total['sum_car'] = $res['sum_car'];
                $total['parent_hash'] = md5('全国');
                $total['oil_cost_hkm'] = sprintf("%.1f",$total['sum_oil']/$total['sum_mileage']*100);
                $total['driving_cost_hkm'] = sprintf("%.1f",$total['sum_driving']/$total['sum_mileage']*100);
                $total['cost_hkm'] = sprintf("%.1f",$total['sum_costs']/$total['sum_mileage']*100);

            }else{
                $citydata = $query->select(['city'=>'dpt_sec','sum_car'=>'count("one"."id")'])->andWhere($_where)->orderBy(['sum_car' => SORT_DESC])->groupBy('dpt_sec')->asArray()->all();


                $parent_hash = md5($where['city'].'_city');


                $sum_field['city'] = 'dpt_sec';
                $res = $_query->select($sum_field)->groupBy('dpt_sec')->asArray()->all();
                $_citydata = [];
                foreach ($res as $v) {
                    $hash = md5($v['city'].'_dpt_sec');
                    $_citydata[$hash] = array_merge($v, ['hash' => $hash, 'parent_hash' => $parent_hash]);
                }



                foreach ($citydata as $v) {

                    $hash = md5($v['city'].'_dpt_sec');

                    if (isset($_citydata[$hash])) {
                        $v = array_merge($v, $_citydata[$hash]);
                    } else {
                        $v = array_merge($v, ['hash' => $hash, 'parent_hash' => $parent_hash], $sum_data);
                    }

                    $v['oil_cost_hkm'] = sprintf("%.1f",$v['sum_oil']/$v['sum_mileage']*100);
                    $v['driving_cost_hkm'] = sprintf("%.1f",$v['sum_driving']/$v['sum_mileage']*100);
                    $v['cost_hkm'] = sprintf("%.1f",$v['sum_costs']/$v['sum_mileage']*100);

                    $city_arr[$v['city']] = $v['cost_hkm'];


                    $rows[] = $v;


                }

                $sum_field['city'] = 'city';
                $total = $_query->select($sum_field)->andWhere(['city'=>$where['city']])->groupBy('city')->asArray()->one();
                $res = $query->select(['sum_car'=>'count("one"."id")'])->filterWhere($_where)->andWhere(['city'=>$where['city']])->groupBy('city')->orderBy(['sum_car' => SORT_DESC])->asArray()->one();
                $total['sum_car'] = $res['sum_car'];
                $total['hash'] = $parent_hash;
                $total['parent_hash'] = md5('全省');
                $total['oil_cost_hkm'] = sprintf("%.1f",$total['sum_oil']/$total['sum_mileage']*100);
                $total['driving_cost_hkm'] = sprintf("%.1f",$total['sum_driving']/$total['sum_mileage']*100);
                $total['cost_hkm'] = sprintf("%.1f",$total['sum_costs']/$total['sum_mileage']*100);


            }

            foreach ($sum_data as $k=>$v){
                $total[$k] = sprintf("%.1f",$total[$k]);
            }

            array_unshift($rows, $total);

            arsort($city_arr);
            foreach ($city_arr as $k=>$v){
                $xAxis[] = $k;
                $series[] = $v;
            }

            $result = ["total" => null, "totalNotFiltered" => null, "rows" => $rows, 'needgetall'=> $needgetall,'date'=>$_where];

            $chart1data = [
                ['value' => round($total['sum_oil']/10000), 'name' => '燃油'],
                ['value' => round($total['sum_repair']/10000), 'name' => '维修'],
                ['value' => round($total['sum_insurance']/10000), 'name' => '保险'],
                ['value' => round($total['sum_rent']/10000), 'name' => '租赁'],
                ['value' => round($total['sum_year_check']/10000), 'name' => '年检费'],
                ['value' => round($total['sum_road_bridge']/10000), 'name' => '路桥'],
                ['value' => round($total['sum_other']/10000), 'name' => '其他'],
            ];

            $result['chartdata'] = [
                'chart1' => $this->getChart1Option([
                    'title' => [
                        'text' => '车辆费用分类（万元）',
                    ],
                    'series' => [
                        [
                            'name' => '车辆分类费用（万元）',
                            'data' => $chart1data
                        ]
                    ]
                ]),
                'chart2' => $this->getChart2Option([
                    'title' => [
                        'text' => '百公里费用排名',
                    ],
                    'xAxis' => [
                        [
                            'data' => $xAxis,
                        ]
                    ],
                    'series' => [
                        [
                            'name' => '百公里费用（元）',
                            'data' => $series,
                        ]
                    ]
                ])
            ];

            if (Yii::$app->request->isAjax) echo_json($result);

            $this->tableTitle = array(
                array('field' => 'city', 'title' => '地市', 'align' => 'left'),
                array('field' => 'sum_car', 'title' => '车辆总数量', 'align' => 'right', 'formatter'=>'numericFormatter'),
                array('field' => 'sum_costs', 'title' => '车辆总费用', 'align' => 'right', 'formatter'=>'numeric1Formatter'),
                array('field' => 'sum_driving', 'title' => '运行费', 'align' => 'right', 'formatter'=>'numeric1Formatter'),
                array('field' => 'sum_mileage', 'title' => '行驶里程', 'align' => 'right', 'formatter'=>'numeric1Formatter'),
                array('field' => 'oil_cost_hkm', 'title' => '百公里燃油费', 'align' => 'right', 'formatter'=>'numeric1Formatter'),
                array('field' => 'driving_cost_hkm', 'title' => '百公里运行费', 'align' => 'right', 'formatter'=>'numeric1Formatter'),
                array('field' => 'cost_hkm', 'title' => '百公里费用', 'align' => 'right', 'formatter'=>'numeric1Formatter'),
            );

            $this->tableConfig = array('table' => 'costs_driving', 'url' => $this->createRealUrl(['car/fee/miles100', 'type' => $type]), 'setFieldUrl' => $this->createRealUrl(['car/fee/setfield']), 'idField' => 'hash', 'checkbox' => 0, 'dropmenu' => 1, 'refresh' => true, 'fixed' => true, 'fixedNum' => 3, 'fixedRightNumber' => 0, 'tree' => 1, 'parentIdField' => 'parent_hash', 'treeShowField' => 'city', 'treeColumn' => 0, 'expand' => false, 'exportFileName' => '百公里费用统计', 'height' => 500);
            $this->result = $result;

        }
        //百公里运行费统计
        if ($type == 2) {
            $query = FOneCarOneTable::find()->alias('one')->leftJoin('{{%f_car_info}} as c', 'one.car_id = c.car_id');

            $params = Yii::$app->request->get();

            $where = $this->getCityWhere($params);
            //$where['is_special_car'] = 0;

            $_query =  clone $query;

            //账期
            if(!empty($params['statistical_month'])){
                $statistical_month = explode('-',$params['statistical_month']);
                $_where['year_info'] = intval($statistical_month[0]);
                $_where['month_info'] = intval($statistical_month[1]);
            }else{
                $res = FOneCarOneTable::find()->orderBy('year_info desc,month_info desc' )->asArray()->one();
                $_where['year_info'] = intval($res['year_info']);
                $_where['month_info'] = intval($res['month_info']);
            }
            $_query->where(['and',['=','year_info',intval($_where['year_info'])],['<=','month_info',intval($_where['month_info'])]]);
            //$_where['is_special_car'] = 0;
            //$month = $_query->select('COUNT(DISTINCT month_info) as month_num,MAX(month_info) as month_info')->asArray()->one();
            //$month_num = max(intval($month['month_num']),2);
            //$month_info = max($month['month_info'],2);

            //if($_where['month_info']>$month_info) $_where['month_info'] = $month_info;

            $query->where($where);

            $sum_field = ['city'=>'city',
                'sum_costs'=>'cast(sum("sum_costs") as decimal(10,2))',
                'sum_driving'=>'cast(sum("oil_costs"+"repair_costs"+"road_bridge_costs"+"insurance_costs") as decimal(10,2))',
                'sum_oil'=>'cast(sum("oil_costs") as decimal(10,2))',
                'sum_repair'=>'cast(sum("repair_costs") as decimal(10,2))',
                'sum_road_bridge'=>'cast(sum("road_bridge_costs") as decimal(10,2))',
                'sum_rent'=>'cast(sum("rent_costs") as decimal(10,2))',
                'sum_insurance'=>'cast(sum("insurance_costs") as decimal(10,2))',
                'sum_year_check'=>'cast(sum("year_check_costs") as decimal(10,2))',
                'sum_other'=>'cast(sum("other_costs") as decimal(10,2))',
                'sum_mileage'=>'cast(sum("sum_mileage") as decimal(10,2))',
            ];
            $sum_data = ['sum_costs'=>0,'sum_driving'=>0,'sum_oil'=>0,'sum_repair'=>0,'sum_road_bridge'=>0,'sum_rent'=>0,'sum_insurance'=>0,'sum_year_check'=>0,'sum_other'=>0,'sum_mileage'=>0];



            $city_arr = [];
            $chart1data = [];
            $rows = [];
            $needgetall = false;

            if(empty($where['city'])){
                $needgetall = true;
                $citydata = $query->select(['city','sum_car'=>'count("one"."id")'])->andWhere($_where)->groupBy('city')->orderBy($this->citySort)->asArray()->all();

                $total = $sum_data;

                $total_hash = md5('全省');
                $res = $_query->select($sum_field)->groupBy('city')->asArray()->all();
                $_citydata = [];
                foreach ($res as $v) {
                    $hash = md5($v['city'].'_city');
                    $_citydata[$hash] = array_merge($v, ['hash' => $hash, 'parent_hash' => $total_hash]);
                }
                if($params['getall']==1) {
                    $needgetall = false;
                    $sum_field['city'] = 'dpt_sec';
                    $res = $_query->select($sum_field)->groupBy('dpt_sec')->asArray()->all();
                    $secdata = [];
                    foreach ($res as $v) {
                        $hash = md5($v['city'].'_dpt_sec');
                        $secdata[$hash] = $v;
                    }
                }


                foreach ($citydata as $v) {
                    if(!in_array($v['city'],$this->cityOptions)) break;

                    $hash = md5($v['city'].'_city');
                    if (isset($_citydata[$hash])) {
                        $v = array_merge($v, $_citydata[$hash]);
                    } else {
                        $v = array_merge($v, ['hash' => $hash, 'parent_hash' => $total_hash], $sum_data);
                    }

                    if($v['city']=='省公司本部'){
                        $v['oil_cost_hkm'] = sprintf("%.1f",$total['sum_oil']/$total['sum_mileage']*100);
                        $v['driving_cost_hkm'] = sprintf("%.1f",$total['sum_driving']/$total['sum_mileage']*100);
                        $v['cost_hkm'] = sprintf("%.1f",$total['sum_costs']/$total['sum_mileage']*100);
                    }else{
                        $v['oil_cost_hkm'] = sprintf("%.1f",$v['sum_oil']/$v['sum_mileage']*100);
                        $v['driving_cost_hkm'] = sprintf("%.1f",$v['sum_driving']/$v['sum_mileage']*100);
                        $v['cost_hkm'] = sprintf("%.1f",$v['sum_costs']/$v['sum_mileage']*100);
                    }

                    foreach ($total as $key => $_v) {
                        $total[$key] += $v[$key];
                    }


                    $city_arr[$v['city']] = $v['driving_cost_hkm'];

                    $rows[] = $v;

                    //$chart1data[] = ['value' => $v['sum_driving'], 'name' => $v['city']];
                    if($params['getall']==1) {
                        $res = $query->select(['city'=>'dpt_sec','sum_car'=>'count("one"."id")'])->where(['city' => $v['city']])->andWhere($_where)->orderBy(['sum_car' => SORT_DESC])->groupBy('dpt_sec')->asArray()->all();
                        foreach ($res as $_k => $_v) {
                            $hash = md5($_v['city'].'_dpt_sec');
                            if (isset($secdata[$hash])) {
                                $_v = array_merge($_v, $secdata[$hash]);
                            } else {
                                $_v = array_merge($_v, $sum_data);
                            }
                            $_v['hash'] = '';
                            $_v['parent_hash'] = md5($v['city'].'_city');

                            if($_v['city']=='省公司本部'){
                                $_v['oil_cost_hkm'] = $v['oil_cost_hkm'];
                                $_v['driving_cost_hkm'] = $v['driving_cost_hkm'];
                                $_v['cost_hkm'] = $v['cost_hkm'];
                            }else{
                                $_v['oil_cost_hkm'] = sprintf("%.1f",$_v['sum_oil']/$_v['sum_mileage']*100);
                                $_v['driving_cost_hkm'] = sprintf("%.1f",$_v['sum_driving']/$_v['sum_mileage']*100);
                                $_v['cost_hkm'] = sprintf("%.1f",$_v['sum_costs']/$_v['sum_mileage']*100);
                            }
                            $rows[] = $_v;
                        }
                    }else{
                        $_v = [];
                        $_v['hash'] = '';
                        $_v['parent_hash'] = md5($v['city'].'_city');
                        $_v = array_merge($_v, $sum_data);
                        $rows[] = $_v;
                    }
                }
                $total['city'] = '全省';
                $total['hash'] = $total_hash;
                $res = $query->select(['sum_car'=>'count("one"."id")'])->filterWhere($_where)->groupBy('')->orderBy(['sum_car' => SORT_DESC])->asArray()->one();
                $total['sum_car'] = $res['sum_car'];
                $total['parent_hash'] = md5('全国');
                $total['oil_cost_hkm'] = sprintf("%.1f",$total['sum_oil']/$total['sum_mileage']*100);
                $total['driving_cost_hkm'] = sprintf("%.1f",$total['sum_driving']/$total['sum_mileage']*100);
                $total['cost_hkm'] = sprintf("%.1f",$total['sum_costs']/$total['sum_mileage']*100);

            }else{
                $citydata = $query->select(['city'=>'dpt_sec','sum_car'=>'count("one"."id")'])->andWhere($_where)->orderBy(['sum_car' => SORT_DESC])->groupBy('dpt_sec')->asArray()->all();


                $parent_hash = md5($where['city'].'_city');


                $sum_field['city'] = 'dpt_sec';
                $res = $_query->select($sum_field)->groupBy('dpt_sec')->asArray()->all();
                $_citydata = [];
                foreach ($res as $v) {
                    $hash = md5($v['city'].'_dpt_sec');
                    $_citydata[$hash] = array_merge($v, ['hash' => $hash, 'parent_hash' => $parent_hash]);
                }



                foreach ($citydata as $v) {

                    $hash = md5($v['city'].'_dpt_sec');

                    if (isset($_citydata[$hash])) {
                        $v = array_merge($v, $_citydata[$hash]);
                    } else {
                        $v = array_merge($v, ['hash' => $hash, 'parent_hash' => $parent_hash], $sum_data);
                    }

                    $v['oil_cost_hkm'] = sprintf("%.1f",$v['sum_oil']/$v['sum_mileage']*100);
                    $v['driving_cost_hkm'] = sprintf("%.1f",$v['sum_driving']/$v['sum_mileage']*100);
                    $v['cost_hkm'] = sprintf("%.1f",$v['sum_costs']/$v['sum_mileage']*100);

                    $city_arr[$v['city']] = $v['driving_cost_hkm'];


                    $rows[] = $v;

                    //$chart1data[] = ['value' => $v['sum_costs'], 'name' => $v['city']];
                }

                $sum_field['city'] = 'city';
                $total = $_query->select($sum_field)->andWhere(['city'=>$where['city']])->groupBy('city')->asArray()->one();
                $res = $query->select(['sum_car'=>'count("one"."id")'])->filterWhere($_where)->andWhere(['city'=>$where['city']])->groupBy('city')->orderBy(['sum_car' => SORT_DESC])->asArray()->one();
                $total['sum_car'] = $res['sum_car'];
                $total['hash'] = $parent_hash;
                $total['parent_hash'] = md5('全省');
                $total['oil_cost_hkm'] = sprintf("%.1f",$total['sum_oil']/$total['sum_mileage']*100);
                $total['driving_cost_hkm'] = sprintf("%.1f",$total['sum_driving']/$total['sum_mileage']*100);
                $total['cost_hkm'] = sprintf("%.1f",$total['sum_costs']/$total['sum_mileage']*100);


            }

            foreach ($sum_data as $k=>$v){
                $total[$k] = sprintf("%.1f",$total[$k]);
            }

            array_unshift($rows, $total);

            arsort($city_arr);
            foreach ($city_arr as $k=>$v){
                $xAxis[] = $k;
                $series[] = $v;
            }

            $result = ["total" => null, "totalNotFiltered" => null, "rows" => $rows, 'needgetall'=> $needgetall,'date'=>$_where];

            $chart1data = [
                ['value' => round($total['sum_oil']/10000), 'name' => '燃油'],
                ['value' => round($total['sum_repair']/10000), 'name' => '维修'],
                ['value' => round($total['sum_insurance']/10000), 'name' => '保险'],
                ['value' => round($total['sum_road_bridge']/10000), 'name' => '路桥'],
            ];

            $result['chartdata'] = [
                'chart1' => $this->getChart1Option([
                    'title' => [
                        'text' => '运行费分类（万元）',
                    ],
                    'series' => [
                        [
                            'name' => '运行费分类（万元）',
                            'data' => $chart1data
                        ]
                    ]
                ]),
                'chart2' => $this->getChart2Option([
                    'title' => [
                        'text' => '百公里运行费排名',
                    ],
                    'xAxis' => [
                        [
                            'data' => $xAxis,
                        ]
                    ],
                    'series' => [
                        [
                            'name' => '百公里运行费（元）',
                            'data' => $series,
                        ]
                    ]
                ])
            ];

            if (Yii::$app->request->isAjax) echo_json($result);

            $this->tableTitle = array(
                array('field' => 'city', 'title' => '地市', 'align' => 'left'),
                array('field' => 'sum_car', 'title' => '车辆总数量', 'align' => 'right', 'formatter'=>'numericFormatter'),
                array('field' => 'sum_costs', 'title' => '车辆总费用', 'align' => 'right', 'formatter'=>'numeric1Formatter'),
                array('field' => 'sum_driving', 'title' => '运行费', 'align' => 'right', 'formatter'=>'numeric1Formatter'),
                array('field' => 'sum_mileage', 'title' => '行驶里程', 'align' => 'right', 'formatter'=>'numeric1Formatter'),
                array('field' => 'oil_cost_hkm', 'title' => '百公里燃油费', 'align' => 'right', 'formatter'=>'numeric1Formatter'),
                array('field' => 'driving_cost_hkm', 'title' => '百公里运行费', 'align' => 'right', 'formatter'=>'numeric1Formatter'),
                array('field' => 'cost_hkm', 'title' => '百公里费用', 'align' => 'right', 'formatter'=>'numeric1Formatter'),
            );

            $this->tableConfig = array('table' => 'costs_driving', 'url' => $this->createRealUrl(['car/fee/miles100', 'type' => $type]), 'setFieldUrl' => $this->createRealUrl(['car/fee/setfield']), 'idField' => 'hash', 'checkbox' => 0, 'dropmenu' => 1, 'refresh' => true, 'fixed' => true, 'fixedNum' => 3, 'fixedRightNumber' => 0, 'tree' => 1, 'parentIdField' => 'parent_hash', 'treeShowField' => 'city', 'treeColumn' => 0, 'expand' => false, 'exportFileName' => '百公里运行费用统计', 'height' => 500);
            $this->result = $result;
        }
        //百公里燃油费统计
        if ($type == 3) {
            $query = FOneCarOneTable::find()->alias('one')->leftJoin('{{%f_car_info}} as c', 'one.car_id = c.car_id');

            $params = Yii::$app->request->get();

            $where = $this->getCityWhere($params);
            //$where['is_special_car'] = 0;

            $_query =  clone $query;

            //账期
            if(!empty($params['statistical_month'])){
                $statistical_month = explode('-',$params['statistical_month']);
                $_where['year_info'] = intval($statistical_month[0]);
                $_where['month_info'] = intval($statistical_month[1]);
            }else{
                $res = FOneCarOneTable::find()->orderBy('year_info desc,month_info desc' )->asArray()->one();
                $_where['year_info'] = intval($res['year_info']);
                $_where['month_info'] = intval($res['month_info']);
            }
            $_query->where(['and',['=','year_info',intval($_where['year_info'])],['<=','month_info',intval($_where['month_info'])]]);
            //$_where['is_special_car'] = 0;
            //$month = $_query->select('COUNT(DISTINCT month_info) as month_num,MAX(month_info) as month_info')->asArray()->one();
            //$month_num = max(intval($month['month_num']),1);
            //$month_info = max($month['month_info'],1);

            //if($_where['month_info']>$month_info) $_where['month_info'] = $month_info;

            $query->where($where);

            $sum_field = ['city'=>'city',
                'sum_oil'=>'cast(sum("oil_costs") as decimal(10,2))',
                'sum_mileage'=>'cast(sum("sum_mileage") as decimal(10,2))',
            ];
            $sum_data = ['sum_costs'=>0,'sum_driving'=>0,'sum_oil'=>0,'sum_repair'=>0,'sum_road_bridge'=>0,'sum_rent'=>0,'sum_insurance'=>0,'sum_year_check'=>0,'sum_other'=>0,'sum_mileage'=>0];

            $car_type = [
                 '货车',
                 '轿车',
                 '皮卡车',
                 '商务车',
                 '微型面包车',
                 '中型面包车（8-20座的面包车）',
                '微型双排',
                '越野车',
            ];

            /*$res = FCarInfo::find()->select('car_type')->where(['is_special_car'=>0])->groupBy('car_type')->asArray()->all();

            foreach ($res as $v){
                if(!empty($v['car_type']) && !in_array($v['car_type'],$car_type)) $car_type[] = $v['car_type'];
            }*/

            foreach ($car_type as $k=>$v){
                $sum_field['sum_oil_'.$k] = 'cast(sum(case when "car_type"=\''.$v.'\' then "oil_costs" else 0 end) as decimal(10,2))';
                $sum_field['sum_mileage_'.$k] = 'cast(sum(case when "car_type"=\''.$v.'\' then "sum_mileage" else 0 end) as decimal(10,2))';
                $sum_data['sum_oil_'.$k] = 0;
                $sum_data['sum_mileage_'.$k] = 0;
            }

            $city_arr = [];
            $chart1data = [];
            $rows = [];
            $needgetall = false;

            if(empty($where['city'])){
                $needgetall = true;
                $citydata = $query->select(['city','sum_car'=>'count("one"."id")'])->andWhere($_where)->groupBy('city')->orderBy($this->citySort)->asArray()->all();

                $total = $sum_data;

                $total_hash = md5('全省');
                $res = $_query->select($sum_field)->groupBy('city')->asArray()->all();
                $_citydata = [];
                foreach ($res as $v) {
                    $hash = md5($v['city'].'_city');
                    $_citydata[$hash] = array_merge($v, ['hash' => $hash, 'parent_hash' => $total_hash]);
                }
                if($params['getall']==1) {
                    $needgetall = false;
                    $sum_field['city'] = 'dpt_sec';
                    $res = $_query->select($sum_field)->groupBy('dpt_sec')->asArray()->all();
                    $secdata = [];
                    foreach ($res as $v) {
                        $hash = md5($v['city'].'_dpt_sec');
                        $secdata[$hash] = $v;
                    }
                }


                foreach ($citydata as $v) {
                    if(!in_array($v['city'],$this->cityOptions)) break;

                    $hash = md5($v['city'].'_city');
                    if (isset($_citydata[$hash])) {
                        $v = array_merge($v, $_citydata[$hash]);
                    } else {
                        $v = array_merge($v, ['hash' => $hash, 'parent_hash' => $total_hash], $sum_data);
                    }

                    if($v['city']=='省公司本部'){
                        $v['oil_cost_hkm'] = sprintf("%.1f",$total['sum_oil']/$total['sum_mileage']*100);
                        foreach ($car_type as $k=>$_v){
                            $v['hkm_oil_'.$k] = sprintf("%.1f",$v['sum_mileage_'.$k]>0 ? $total['sum_oil_'.$k]/$total['sum_mileage_'.$k]*100 : 0);
                            //unset($v['sum_oil_'.$k],$v['sum_mileage_'.$k]);
                        }
                    }else{
                        $v['oil_cost_hkm'] = sprintf("%.1f",$v['sum_oil']/$v['sum_mileage']*100);
                        foreach ($car_type as $k=>$_v){
                            $v['hkm_oil_'.$k] = sprintf("%.1f",$v['sum_mileage_'.$k]>0 ? $v['sum_oil_'.$k]/$v['sum_mileage_'.$k]*100 : 0);
                            //unset($v['sum_oil_'.$k],$v['sum_mileage_'.$k]);
                        }
                    }

                    foreach ($total as $key => $_v) {
                        $total[$key] += $v[$key];
                    }



                    $city_arr[$v['city']] = $v['oil_cost_hkm'];

                    $rows[] = $v;

                    //$chart1data[] = ['value' => $v['sum_driving'], 'name' => $v['city']];
                    if($params['getall']==1) {
                        $res = $query->select(['city'=>'dpt_sec','sum_car'=>'count("one"."id")'])->where(['city' => $v['city']])->andWhere($_where)->orderBy(['sum_car' => SORT_DESC])->groupBy('dpt_sec')->asArray()->all();
                        foreach ($res as $_k => $_v) {
                            $hash = md5($_v['city'].'_dpt_sec');
                            if (isset($secdata[$hash])) {
                                $_v = array_merge($_v, $secdata[$hash]);
                            } else {
                                $_v = array_merge($_v, $sum_data);
                            }
                            $_v['hash'] = '';
                            $_v['parent_hash'] = md5($v['city'].'_city');

                            if($_v['city']=='省公司本部'){
                                $_v['oil_cost_hkm'] = $v['oil_cost_hkm'];
                                foreach ($car_type as $k=>$val){
                                    $_v['hkm_oil_'.$k] = $v['hkm_oil_'.$k];
                                    unset($_v['sum_oil_'.$k],$_v['sum_mileage_'.$k]);
                                }
                            }else{
                                $_v['oil_cost_hkm'] = sprintf("%.1f",$_v['sum_oil']/$_v['sum_mileage']*100);
                                foreach ($car_type as $k=>$val){
                                    $_v['hkm_oil_'.$k] = sprintf("%.1f",$_v['sum_mileage_'.$k]>0 ? $_v['sum_oil_'.$k]/$_v['sum_mileage_'.$k]*100 : 0);
                                    unset($_v['sum_oil_'.$k],$_v['sum_mileage_'.$k]);
                                }
                            }


                            $rows[] = $_v;
                        }
                    }else{
                        $_v = [];
                        $_v['hash'] = '';
                        $_v['parent_hash'] = md5($v['city'].'_city');
                        $_v = array_merge($_v, $sum_data);
                        $rows[] = $_v;
                    }
                }
                $total['city'] = '全省';
                $total['hash'] = $total_hash;
                $res = $query->select(['sum_car'=>'count("one"."id")'])->filterWhere($_where)->groupBy('')->orderBy(['sum_car' => SORT_DESC])->asArray()->one();
                $total['sum_car'] = $res['sum_car'];
                $total['parent_hash'] = md5('全国');
                $total['oil_cost_hkm'] = sprintf("%.1f",$total['sum_oil']/$total['sum_mileage']*100);


            }else{
                $citydata = $query->select(['city'=>'dpt_sec','sum_car'=>'count("one"."id")'])->andWhere($_where)->orderBy(['sum_car' => SORT_DESC])->groupBy('dpt_sec')->asArray()->all();


                $parent_hash = md5($where['city'].'_city');


                $sum_field['city'] = 'dpt_sec';
                $res = $_query->select($sum_field)->groupBy('dpt_sec')->asArray()->all();
                $_citydata = [];
                foreach ($res as $v) {
                    $hash = md5($v['city'].'_dpt_sec');
                    $_citydata[$hash] = array_merge($v, ['hash' => $hash, 'parent_hash' => $parent_hash]);
                }



                foreach ($citydata as $v) {

                    $hash = md5($v['city'].'_dpt_sec');

                    if (isset($_citydata[$hash])) {
                        $v = array_merge($v, $_citydata[$hash]);
                    } else {
                        $v = array_merge($v, ['hash' => $hash, 'parent_hash' => $parent_hash], $sum_data);
                    }

                    $v['oil_cost_hkm'] = sprintf("%.1f",$v['sum_oil']/$v['sum_mileage']*100);
                    foreach ($car_type as $k=>$_v){
                        $v['hkm_oil_'.$k] = sprintf("%.1f",$v['sum_mileage_'.$k]>0 ? $v['sum_oil_'.$k]/$v['sum_mileage_'.$k]*100 : 0);
                        unset($v['sum_oil_'.$k],$v['sum_mileage_'.$k]);
                    }

                    $city_arr[$v['city']] = $v['oil_cost_hkm'];


                    $rows[] = $v;

                    //$chart1data[] = ['value' => $v['sum_costs'], 'name' => $v['city']];
                }

                $sum_field['city'] = 'city';
                $total = $_query->select($sum_field)->andWhere(['city'=>$where['city']])->groupBy('city')->asArray()->one();
                $res = $query->select(['sum_car'=>'count("one"."id")'])->filterWhere($_where)->andWhere(['city'=>$where['city']])->groupBy('city')->orderBy(['sum_car' => SORT_DESC])->asArray()->one();
                $total['sum_car'] = $res['sum_car'];
                $total['hash'] = $parent_hash;
                $total['parent_hash'] = md5('全省');
                $total['oil_cost_hkm'] = sprintf("%.1f",$total['sum_oil']/$total['sum_mileage']*100);

            }

            $this->tableTitle = array(
                array('field' => 'city', 'title' => '地市', 'align' => 'left'),
                array('field' => 'sum_car', 'title' => '车辆总数量', 'align' => 'right', 'formatter'=>'numericFormatter'),
                array('field' => 'oil_cost_hkm', 'title' => '百公里燃油费', 'align' => 'right', 'formatter'=>'numeric1Formatter'),
            );

            foreach ($car_type as $k=>$_v){
                $total['hkm_oil_'.$k] = sprintf("%.1f",$total['sum_mileage_'.$k]>0 ? $total['sum_oil_'.$k]/$total['sum_mileage_'.$k]*100 : 0);
                unset($total['sum_oil_'.$k],$total['sum_mileage_'.$k],$sum_data['sum_oil_'.$k],$sum_data['sum_mileage_'.$k]);
                $chart1data[] = ['value' => $total['hkm_oil_'.$k], 'name' => $_v];
                $this->tableTitle[] = array('field' => 'hkm_oil_'.$k, 'title' => $_v, 'align' => 'right', 'formatter'=>'numeric1Formatter');
            }

            foreach ($sum_data as $k=>$v){
                $total[$k] = sprintf("%.1f",$total[$k]);
            }

            array_unshift($rows, $total);

            arsort($city_arr);
            foreach ($city_arr as $k=>$v){
                $xAxis[] = $k;
                $series[] = $v;
            }

            $result = ["total" => null, "totalNotFiltered" => null, "rows" => $rows, 'needgetall'=> $needgetall,'date'=>$_where];



            $result['chartdata'] = [
                'chart1' => $this->getChart1Option([
                    'title' => [
                        'text' => '分车型百公里燃油费（元）',
                    ],
                    'series' => [
                        [
                            'name' => '分车型百公里燃油费（元）',
                            'data' => $chart1data
                        ]
                    ]
                ]),
                'chart2' => $this->getChart2Option([
                    'title' => [
                        'text' => '百公里燃油费排名',
                    ],
                    'xAxis' => [
                        [
                            'data' => $xAxis,
                        ]
                    ],
                    'series' => [
                        [
                            'name' => '百公里燃油费（元）',
                            'data' => $series,
                        ]
                    ]
                ])
            ];

            if (Yii::$app->request->isAjax) echo_json($result);

            $this->tableConfig = array('table' => 'costs_driving', 'url' => $this->createRealUrl(['car/fee/miles100', 'type' => $type]), 'setFieldUrl' => $this->createRealUrl(['car/fee/setfield']), 'idField' => 'hash', 'checkbox' => 0, 'dropmenu' => 1, 'refresh' => true, 'fixed' => true, 'fixedNum' => 3, 'fixedRightNumber' => 0, 'tree' => 1, 'parentIdField' => 'parent_hash', 'treeShowField' => 'city', 'treeColumn' => 0, 'expand' => false, 'exportFileName' => '百公里燃油费统计', 'height' => 500);
            $this->result = $result;

        }
        //百公里费用明细
        if ($type == 4) {
            $query = FOneCarOneTable::find()->alias('one')->leftJoin('{{%f_car_info}} as c', 'one.car_id = c.car_id');
            if (Yii::$app->request->isAjax) {

                $params = Yii::$app->request->get();

                $where = $this->getCityWhere($params);

                //账期
                if(!empty($params['statistical_month'])){
                    $statistical_month = explode('-',$params['statistical_month']);
                    $_where['year_info'] = intval($statistical_month[0]);
                    $_where['month_info'] = intval($statistical_month[1]);
                }else{
                    $res = FOneCarOneTable::find()->orderBy('year_info desc,month_info desc' )->asArray()->one();
                    $_where['year_info'] = intval($res['year_info']);
                    $_where['month_info'] = intval($res['month_info']);
                }


                //车牌
                if(!empty($params['card_num'])){
                    $where['card_num'] = $params['card_num'];
                }

                if ($params['card_num']!=$params['card_num_text']){
                    unset($where['card_num']);
                    $query->where($where)->andWhere(['like','card_num',$params['card_num_text']]);
                }else{
                    $query->where($where);
                }

                $query->andWhere(['and',['=','year_info',intval($_where['year_info'])],['<=','month_info',intval($_where['month_info'])]]);
                $query->select(['one.car_id',
                    'year_info'=>'max("year_info")',
                    'month_info'=>'max("month_info")',
                    'oil_costs'=>'sum("oil_costs")',
                    'repair_costs'=>'sum("repair_costs")',
                    'insurance_costs'=>'sum("insurance_costs")',
                    //'rent_costs'=>'sum("rent_costs")',
                    //'year_check_costs'=>'sum("year_check_costs")',
                    'road_bridge_costs'=>'sum("road_bridge_costs")',
                    //'other_costs'=>'sum("other_costs")',
                    //'attend_days'=>'sum("attend_days")',
                    //'work_days'=>'sum("work_days")',
                    'sum_mileage'=>'sum("sum_mileage")',
                    'sum_costs'=>'sum("sum_costs")',
                ])->groupBy('one.car_id');
                $sql = $query->createCommand()->getRawSql();

                $query = FCarInfo::find()->alias('car')->leftJoin("({$sql}) as os", 'car.car_id = os.car_id');

                if ($params['card_num']!=$params['card_num_text']){
                    unset($where['card_num']);
                    $query->where($where)->andWhere(['like','card_num',$params['card_num_text']]);
                }else{
                    $query->where($where);
                }

                $countQuery = clone $query;
                //分页
                if (isset($_GET['limit'])) {
                    $query->limit(intval($_GET['limit']));
                }
                if (isset($_GET['offset'])) {
                    $query->offset(intval($_GET['offset']));
                }
                $field = ['os.*','car.*',
                    'oil_per_km'=>'cast((case when "sum_mileage">0 then ("oil_costs")/"sum_mileage"*100 else 0 end) as decimal(10,2))',
                    'driving_costs_per_km'=>'cast((case when "sum_mileage">0 then ("oil_costs"+"repair_costs"+"road_bridge_costs"+"insurance_costs")/"sum_mileage"*100 else 0 end) as decimal(10,2))',
                    'sum_costs_per_km'=>'cast((case when "sum_mileage">0 then ("sum_costs")/"sum_mileage"*100 else 0 end) as decimal(10,2))',
                    'statistical_month'=>'concat("year_info",LPAD("month_info"::text,2,\'0\'))'
                ];

                //$field = ['os.*','car.*','car_age'=>'age(CURRENT_DATE, car.enabled_date::date)'];
                //排序
                if (isset($_GET['sort']) && isset($_GET['sortOrder'])) {
                    $resultList = $query->select($field)->orderBy([$_GET['sort'] => ($_GET['sortOrder'] == 'asc' ? SORT_ASC : SORT_DESC)])->asArray()->all();
                } else {
                    $resultList = $query->select($field)->orderBy(['sum_costs_per_km' => SORT_DESC])->asArray()->all();
                }


                $result = ["total" => $countQuery->count(), "totalNotFiltered" => $countQuery->count(), "rows" => $resultList];
                echo_json($result);
            }
            $this->tableTitle = array(
                //array('field' => 'statistical_month', 'title' => '截止账期', 'align' => 'center', 'sortable' => true),
                array('field' => 'card_num', 'title' => '车牌号', 'align' => 'center', 'sortable' => true),
                array('field' => 'city', 'title' => '单位', 'align' => 'center', 'sortable' => true),
                array('field' => 'dpt_sec', 'title' => '二级单位', 'align' => 'center', 'sortable' => true),
                array('field' => 'grid', 'title' => '三级单位', 'align' => 'center', 'sortable' => true),
                array('field' => 'self_rent', 'title' => '车辆来源', 'align' => 'center', 'sortable' => true),
                array('field' => 'car_type', 'title' => '车辆类型', 'align' => 'center', 'sortable' => true),
                array('field' => 'using_tag', 'title' => '车辆使用性质', 'align' => 'center', 'sortable' => true),
                //array('field' => 'oil_costs', 'title' => '燃油', 'align' => 'right', 'formatter'=>'numeric1Formatter', 'sortable' => true,),
                //array('field' => 'repair_costs', 'title' => '维修', 'align' => 'right', 'formatter'=>'numeric1Formatter', 'sortable' => true,),
                //array('field' => 'insurance_costs', 'title' => '保险', 'align' => 'right', 'formatter'=>'numeric1Formatter', 'sortable' => true,),
                //array('field' => 'rent_costs', 'title' => '租赁', 'align' => 'right', 'formatter'=>'numeric1Formatter', 'sortable' => true,),
                //array('field' => 'year_check_costs', 'title' => '年检费', 'align' => 'right', 'formatter'=>'numeric1Formatter', 'sortable' => true,),
                //array('field' => 'road_bridge_costs', 'title' => '路桥', 'align' => 'right', 'formatter'=>'numeric1Formatter', 'sortable' => true,),
                //array('field' => 'other_costs', 'title' => '其他', 'align' => 'right', 'formatter'=>'numeric1Formatter', 'sortable' => true,),
                array('field' => 'sum_mileage', 'title' => '行驶里程', 'align' => 'right', 'formatter'=>'numeric1Formatter', 'sortable' => true,),
                array('field' => 'oil_per_km', 'title' => '百公里燃油费', 'align' => 'right', 'formatter'=>'numeric1Formatter', 'sortable' => true,),
                array('field' => 'driving_costs_per_km', 'title' => '百公里运行费', 'align' => 'right', 'formatter'=>'numeric1Formatter', 'sortable' => true,),
                array('field' => 'sum_costs_per_km', 'title' => '百公里费用', 'align' => 'right', 'formatter'=>'numeric1Formatter', 'sortable' => true,),


            );
            $this->tableConfig = array('table' => "fone_car_one_table", 'url' => $this->createRealUrl(['car/fee/miles100', 'type' => $type]), 'setFieldUrl' => $this->createRealUrl(['car/fee/setfield']), 'idField' => "id", 'checkbox' => 0, 'dropmenu' => 1, 'pagination' => true, 'pagesize' => 20, 'refresh' => true, 'exportFileName' => '百公里费用详单', 'height' => 500);
        }

        return $this->render('miles100', array('type' => $type, 'koujing' => $koujing));
    }

    public function actionRental()
    {
        $type = Yii::$app->request->get('type', 1);
        $koujing = '这里是口径描述';
        //燃油费、租赁费、路桥费统计
        if ($type == 1) {
            $query = FOneCarOneTable::find()->alias('one')->leftJoin('{{%f_car_info}} as c', 'one.car_id = c.car_id');

            $params = Yii::$app->request->get();

            $where = $this->getCityWhere($params);
            //$where['is_special_car'] = 0;
            $where['self_rent'] = '租赁车辆';

            $_query =  clone $query;

            //账期
            if(!empty($params['statistical_month'])){
                $statistical_month = explode('-',$params['statistical_month']);
                $_where['year_info'] = intval($statistical_month[0]);
                $_where['month_info'] = intval($statistical_month[1]);
            }else{
                $res = FOneCarOneTable::find()->orderBy('year_info desc,month_info desc' )->asArray()->one();
                $_where['year_info'] = intval($res['year_info']);
                $_where['month_info'] = intval($res['month_info']);
            }
            $_query->where(['and',['=','year_info',intval($_where['year_info'])],['<=','month_info',intval($_where['month_info'])],['=','self_rent','租赁车辆']]);

            //$_where['is_special_car'] = 0;
            $_where['self_rent'] = '租赁车辆';

            $month = $_query->select('COUNT(DISTINCT month_info) as month_num,MAX(month_info) as month_info')->asArray()->one();
            $month_num = max(intval($month['month_num']),1);
            $month_info = max($month['month_info'],1);

            //if($_where['month_info']>$month_info) $_where['month_info'] = $month_info;

            $query->where($where);

            $sum_field = ['city'=>'city',
                'sum_costs'=>'cast(sum("sum_costs") as decimal(10,2))',
                //'sum_driving'=>'cast(sum("oil_costs"+"repair_costs"+"road_bridge_costs"+"insurance_costs") as decimal(10,2))',
                'sum_oil'=>'cast(sum("oil_costs") as decimal(10,2))',
                //'sum_repair'=>'cast(sum("repair_costs") as decimal(10,2))',
                'sum_road_bridge'=>'cast(sum("road_bridge_costs") as decimal(10,2))',
                'sum_rent'=>'cast(sum("rent_costs") as decimal(10,2))',
                //'sum_insurance'=>'cast(sum("insurance_costs") as decimal(10,2))',
                //'sum_year_check'=>'cast(sum("year_check_costs") as decimal(10,2))',
                'sum_other'=>'cast(sum("other_costs") as decimal(10,2))',
                'sum_mileage'=>'cast(sum("sum_mileage") as decimal(10,2))'
            ];
            $sum_data = ['sum_costs'=>0,'sum_oil'=>0,'sum_road_bridge'=>0,'sum_rent'=>0,'sum_other'=>0,'sum_mileage'=>0];



            $city_arr = [];
            $chart1data = [];
            $rows = [];
            $needgetall = false;

            if(empty($where['city'])){
                $needgetall = true;
                $citydata = $query->select(['city','sum_car'=>'count("one"."id")'])->andWhere($_where)->groupBy('city')->orderBy($this->citySort)->asArray()->all();

                $total = $sum_data;

                $total_hash = md5('全省');
                $res = $_query->select($sum_field)->groupBy('city')->asArray()->all();
                $_citydata = [];
                foreach ($res as $v) {
                    $hash = md5($v['city'].'_city');
                    $_citydata[$hash] = array_merge($v, ['hash' => $hash, 'parent_hash' => $total_hash]);
                }
                if($params['getall']==1) {
                    $needgetall = false;
                    $sum_field['city'] = 'dpt_sec';
                    $res = $_query->select($sum_field)->groupBy('dpt_sec')->asArray()->all();
                    $secdata = [];
                    foreach ($res as $v) {
                        $hash = md5($v['city'].'_dpt_sec');
                        $secdata[$hash] = $v;
                    }
                }


                foreach ($citydata as $v) {
                    if(!in_array($v['city'],$this->cityOptions)) break;

                    $hash = md5($v['city'].'_city');
                    $v['sum_car'] = intval($v['sum_car']);
                    if (isset($_citydata[$hash])) {
                        $v = array_merge($v, $_citydata[$hash]);
                    } else {
                        $v = array_merge($v, ['hash' => $hash, 'parent_hash' => $total_hash], $sum_data);
                    }

                    $v['sum_costs_bak'] = $v['sum_costs'];
                    foreach ($sum_data as $key => $_v){
                        $avg_key = str_replace('sum','avg',$key);
                        $v[$avg_key] = $v['sum_car'] ? strval(round($v[$key]/$v['sum_car']/$month_num,2)) : strval('0.0');
                    }
                    $v['sum_costs'] = strval(round($v['sum_costs_bak'],2));

                    if($v['city']=='省公司本部'){
                        $v['oil_cost_hkm'] = sprintf("%.1f",$total['sum_oil']/$total['sum_mileage']*100);
                    }else{
                        $v['oil_cost_hkm'] = sprintf("%.1f",$v['sum_oil']/$v['sum_mileage']*100);
                    }
                    foreach ($total as $key => $_v) {
                        $total[$key] += $v[$key];
                    }
                    unset($v['sum_costs_bak']);
                    $rows[] = $v;

                    $city_arr[$v['city']] = $v['oil_cost_hkm'];

                    //$chart1data[] = ['value' => $v['sum_costs'], 'name' => $v['city']];
                    if($params['getall']==1) {
                        $res = $query->select(['city'=>'dpt_sec','sum_car'=>'count("one"."id")'])->where(['city' => $v['city']])->andWhere($_where)->orderBy(['sum_car' => SORT_DESC])->groupBy('dpt_sec')->asArray()->all();
                        foreach ($res as $_k => $_v) {
                            $hash = md5($_v['city'].'_dpt_sec');
                            $_v['sum_car'] = intval($_v['sum_car']);
                            if (isset($secdata[$hash])) {
                                $_v = array_merge($_v, $secdata[$hash]);
                            } else {
                                $_v = array_merge($_v, $sum_data);
                            }
                            $_v['hash'] = '';
                            $_v['parent_hash'] = md5($v['city'].'_city');
                            $_v['sum_costs_bak'] = $_v['sum_costs'];
                            foreach ($sum_data as $key => $val) {
                                $avg_key = str_replace('sum', 'avg', $key);
                                $_v[$avg_key] = $_v['sum_car'] ? strval(round($_v[$key] / $_v['sum_car']/$month_num, 2)) : strval('0.0');
                            }
                            $_v['sum_costs'] = strval(round($_v['sum_costs_bak'],2));
                            if($_v['city']=='省公司本部'){
                                $_v['oil_cost_hkm'] = $v['oil_cost_hkm'];
                            }else{
                                $_v['oil_cost_hkm'] = sprintf("%.1f",$_v['sum_oil']/$_v['sum_mileage']*100);
                            }

                            unset($_v['sum_costs_bak']);
                            $rows[] = $_v;
                        }
                    }else{
                        $_v = [];
                        $_v['hash'] = '';
                        $_v['parent_hash'] = md5($v['city'].'_city');
                        $_v = array_merge($_v, $sum_data);
                        $rows[] = $_v;
                    }
                }
                $total['city'] = '全省';
                $total['hash'] = $total_hash;
                $total['parent_hash'] = md5('全国');
                $res = $query->select(['sum_car'=>'count("one"."id")'])->filterWhere($_where)->groupBy('')->orderBy(['sum_car' => SORT_DESC])->asArray()->one();
                $total['sum_car'] = $res['sum_car'];
                foreach ($sum_data as $key => $val){
                    $avg_key = str_replace('sum','avg',$key);
                    $total[$avg_key] = $total['sum_car'] ? strval(round($total[$key]/$total['sum_car']/$month_num,2)) : strval('0.0');
                }
                $total['oil_cost_hkm'] = sprintf("%.1f",$total['sum_oil']/$total['sum_mileage']*100);
                array_unshift($rows, $total);


                arsort($city_arr);
                foreach ($city_arr as $k=>$v){
                    $xAxis[] = $k;
                    $series[] = $v;
                }
            }else{
                $citydata = $query->select(['city'=>'dpt_sec','sum_car'=>'count("one"."id")'])->andWhere($_where)->orderBy(['sum_car' => SORT_DESC])->groupBy('dpt_sec')->asArray()->all();


                $parent_hash = md5($where['city'].'_city');


                $sum_field['city'] = 'dpt_sec';
                $res = $_query->select($sum_field)->groupBy('dpt_sec')->asArray()->all();
                $_citydata = [];
                foreach ($res as $v) {
                    $hash = md5($v['city'].'_dpt_sec');
                    $_citydata[$hash] = array_merge($v, ['hash' => $hash, 'parent_hash' => $parent_hash]);
                }



                foreach ($citydata as $v) {

                    $hash = md5($v['city'].'_dpt_sec');
                    $v['sum_car'] = intval($v['sum_car']);

                    if (isset($_citydata[$hash])) {
                        $v = array_merge($v, $_citydata[$hash]);
                    } else {
                        $v = array_merge($v, ['hash' => $hash, 'parent_hash' => $parent_hash], $sum_data);
                    }

                    $v['sum_costs_bak'] = $v['sum_costs'];
                    foreach ($sum_data as $key => $_v){
                        $avg_key = str_replace('sum','avg',$key);
                        $v[$avg_key] = $v['sum_car'] ? strval(round($v[$key]/$v['sum_car']/$month_num,2)) : strval('0.0');
                    }
                    $v['sum_costs'] = strval(round($v['sum_costs_bak'],2));

                    $v['oil_cost_hkm'] = sprintf("%.1f",$v['sum_oil']/$v['sum_mileage']*100);
                    unset($v['sum_costs_bak']);
                    $rows[] = $v;

                    $city_arr[$v['city']] = $v['oil_cost_hkm'];

                    //$chart1data[] = ['value' => $v['sum_costs'], 'name' => $v['city']];

                }

                $res = $query->select(['city','sum_car'=>'count("one"."id")'])->where($_where)->andWhere(['city'=>$where['city']])->groupBy('city')->orderBy(['sum_car' => SORT_DESC])->asArray()->one();
                $sum_field['city'] = 'city';
                $total = $_query->select($sum_field)->andWhere(['city'=>$where['city']])->groupBy('city')->asArray()->one();
                $total['sum_car'] = $res['sum_car'];
                foreach ($sum_data as $key => $val){
                    $avg_key = str_replace('sum','avg',$key);
                    $total[$avg_key] = $total['sum_car'] ? strval(round($total[$key]/$total['sum_car']/$month_num,2)) : strval('0.0');
                }
                $total['oil_cost_hkm'] = sprintf("%.1f",$total['sum_oil']/$total['sum_mileage']*100);
                $total['hash'] = $parent_hash;
                $total['parent_hash'] = md5('全省');
                array_unshift($rows, $total);


                arsort($city_arr);
                foreach ($city_arr as $k=>$v){
                    $xAxis[] = $k;
                    $series[] = $v;
                }
            }


            $result = ["total" => null, "totalNotFiltered" => null, "rows" => $rows, 'needgetall'=> $needgetall,'date'=>$_where];


            $chart1data = [
                ['value' => round($total['sum_oil']/10000), 'name' => '燃油费'],
                ['value' => round($total['sum_rent']/10000), 'name' => '租赁费'],
                ['value' => round($total['sum_road_bridge']/10000), 'name' => '路桥费'],
                ['value' => round($total['sum_other']/10000), 'name' => '其他费']
            ];
            $result['chartdata'] = [
                'chart1' => $this->getChart1Option([
                    'title' => [
                        'text' => '租赁车辆费用占比（万元）',
                    ],
                    'series' => [
                        [
                            'name' => '费用占比（万元）',
                            'data' => $chart1data
                        ]
                    ]
                ]),
                'chart2' => $this->getChart2Option([
                    'title' => [
                        'text' => '租赁车辆百公里燃油费排名',
                    ],
                    'xAxis' => [
                        [
                            'data' => $xAxis,
                        ]
                    ],
                    'series' => [
                        [
                            'name' => '百公里燃油费（元）',
                            'data' => $series,
                        ]
                    ]
                ])
            ];

            if (Yii::$app->request->isAjax) echo_json($result);

            $this->tableTitle = array(
                array('field' => 'city', 'title' => '地市', 'align' => 'left'),
                array('field' => 'sum_car', 'title' => '租赁车辆数量', 'align' => 'right', 'formatter'=>'numericFormatter'),
                //array('field' => 'sum_costs', 'title' => '车辆总费用', 'align' => 'right', 'formatter'=>'numeric1Formatter'),
                array('field' => 'sum_oil', 'title' => '燃油费', 'align' => 'right', 'formatter'=>'numeric1Formatter'),
                array('field' => 'sum_rent', 'title' => '租赁费', 'align' => 'right', 'formatter'=>'numeric1Formatter'),
                array('field' => 'sum_road_bridge', 'title' => '路桥费', 'align' => 'right', 'formatter'=>'numeric1Formatter'),
                array('field' => 'sum_other', 'title' => '其他费', 'align' => 'right', 'formatter'=>'numeric1Formatter'),
                array('field' => 'sum_mileage', 'title' => '总里程', 'align' => 'right', 'formatter'=>'numeric1Formatter'),
                array('field' => 'avg_oil', 'title' => '单车月燃油费', 'align' => 'right', 'formatter'=>'numeric1Formatter'),
                array('field' => 'oil_cost_hkm', 'title' => '百公里燃油费', 'align' => 'right', 'formatter'=>'numeric1Formatter'),
                array('field' => 'avg_rent', 'title' => '单车月租赁费', 'align' => 'right', 'formatter'=>'numeric1Formatter'),
                array('field' => 'avg_road_bridge', 'title' => '单车月路桥费', 'align' => 'right', 'formatter'=>'numeric1Formatter'),
                array('field' => 'avg_other', 'title' => '单车月其他费', 'align' => 'right', 'formatter'=>'numeric1Formatter'),
            );
            $this->tableConfig = array('table' => 'rental_costs', 'url' => $this->createRealUrl(['car/fee/rental', 'type' => $type]), 'setFieldUrl' => $this->createRealUrl(['car/fee/setfield']), 'idField' => 'hash', 'checkbox' => 0, 'dropmenu' => 1, 'refresh' => true, 'fixed' => true, 'fixedNum' => 3, 'fixedRightNumber' => 0, 'tree' => 1, 'parentIdField' => 'parent_hash', 'treeShowField' => 'city', 'treeColumn' => 0, 'expand' => false, 'exportFileName' => '燃油费、租赁费、路桥费统计', 'height' => 500);
            $this->result = $result;

        }
        //租赁车辆维修费统计
        if ($type == 2) {
            $query = FOneCarOneTable::find()->alias('one')->leftJoin('{{%f_car_info}} as c', 'one.car_id = c.car_id');

            $params = Yii::$app->request->get();

            $where = $this->getCityWhere($params);
            //$where['is_special_car'] = 0;
            $where['self_rent'] = '租赁车辆';

            $_query =  clone $query;

            //账期
            if(!empty($params['statistical_month'])){
                $statistical_month = explode('-',$params['statistical_month']);
                $_where['year_info'] = intval($statistical_month[0]);
                $_where['month_info'] = intval($statistical_month[1]);
            }else{
                $res = FOneCarOneTable::find()->orderBy('year_info desc,month_info desc' )->asArray()->one();
                $_where['year_info'] = intval($res['year_info']);
                $_where['month_info'] = intval($res['month_info']);
            }
            $_query->where(['and',['=','year_info',intval($_where['year_info'])],['<=','month_info',intval($_where['month_info'])],['=','self_rent','租赁车辆']]);
            //$_where['is_special_car'] = 0;
            $_where['self_rent'] = '租赁车辆';

            $month = $_query->select('COUNT(DISTINCT month_info) as month_num,MAX(month_info) as month_info')->asArray()->one();
            $month_num = max(intval($month['month_num']),1);
            $month_info = max($month['month_info'],1);

            //if($_where['month_info']>$month_info) $_where['month_info'] = $month_info;

            $query->where($where);

            $sum_field = ['city'=>'city',
                //'sum_costs'=>'cast(sum("sum_costs") as decimal(10,2))',
                //'sum_driving'=>'cast(sum("oil_costs"+"repair_costs"+"road_bridge_costs"+"insurance_costs") as decimal(10,2))',
                //'sum_oil'=>'cast(sum("oil_costs") as decimal(10,2))',
                'sum_repair'=>'cast(sum("repair_costs") as decimal(10,2))',
                //'sum_road_bridge'=>'cast(sum("road_bridge_costs") as decimal(10,2))',
                //'sum_rent'=>'cast(sum("rent_costs") as decimal(10,2))',
                //'sum_insurance'=>'cast(sum("insurance_costs") as decimal(10,2))',
                //'sum_year_check'=>'cast(sum("year_check_costs") as decimal(10,2))',
                //'sum_other'=>'cast(sum("other_costs") as decimal(10,2))',
                //'sum_mileage'=>'cast(sum("sum_mileage") as decimal(10,2))'
            ];
            $sum_data = [
                //'sum_costs'=>0,
                'sum_repair'=>0,
                //'sum_road_bridge'=>0,
                //'sum_rent'=>0,
                //'sum_other'=>0,
                //'sum_mileage'=>0
            ];



            $city_arr = [];
            $chart1data = [];
            $rows = [];
            $needgetall = false;

            if(empty($where['city'])){
                $needgetall = true;
                $citydata = $query->select(['city','sum_car'=>'count("one"."id")'])->andWhere($_where)->groupBy('city')->orderBy($this->citySort)->asArray()->all();

                $total = $sum_data;

                $total_hash = md5('全省');
                $res = $_query->select($sum_field)->groupBy('city')->asArray()->all();
                $_citydata = [];
                foreach ($res as $v) {
                    $hash = md5($v['city'].'_city');
                    $_citydata[$hash] = array_merge($v, ['hash' => $hash, 'parent_hash' => $total_hash]);
                }
                if($params['getall']==1) {
                    $needgetall = false;
                    $sum_field['city'] = 'dpt_sec';
                    $res = $_query->select($sum_field)->groupBy('dpt_sec')->asArray()->all();
                    $secdata = [];
                    foreach ($res as $v) {
                        $hash = md5($v['city'].'_dpt_sec');
                        $secdata[$hash] = $v;
                    }
                }


                foreach ($citydata as $v) {
                    if(!in_array($v['city'],$this->cityOptions)) break;

                    $hash = md5($v['city'].'_city');
                    $v['sum_car'] = intval($v['sum_car']);
                    $v['year_info'] = $_where['year_info'];
                    $v['month_info'] = $_where['month_info'];
                    if (isset($_citydata[$hash])) {
                        $v = array_merge($v, $_citydata[$hash]);
                    } else {
                        $v = array_merge($v, ['hash' => $hash, 'parent_hash' => $total_hash], $sum_data);
                    }
                    foreach ($total as $key => $_v) {
                        $total[$key] += $v[$key];
                    }
                    $v['sum_costs_bak'] = $v['sum_costs'];
                    foreach ($sum_data as $key => $_v){
                        $avg_key = str_replace('sum','avg',$key);
                        $v[$avg_key] = $v['sum_car'] ? strval(round($v[$key]/$v['sum_car'],2)) : strval('0.0');
                    }
                    $v['sum_costs'] = strval(round($v['sum_costs_bak'],2));

                    unset($v['sum_costs_bak']);
                    $rows[] = $v;

                    $city_arr[$v['city']] = $v['avg_repair'];

                    $chart1data[] = ['value' => $v['sum_repair'], 'name' => $v['city']];
                    if($params['getall']==1) {
                        $res = $query->select(['city'=>'dpt_sec','sum_car'=>'count("one"."id")'])->where(['city' => $v['city']])->andWhere($_where)->orderBy(['sum_car' => SORT_DESC])->groupBy('dpt_sec')->asArray()->all();
                        foreach ($res as $_k => $_v) {
                            $hash = md5($_v['city'].'_dpt_sec');
                            $_v['sum_car'] = intval($_v['sum_car']);
                            $_v['year_info'] = $_where['year_info'];
                            $_v['month_info'] = $_where['month_info'];
                            if (isset($secdata[$hash])) {
                                $_v = array_merge($_v, $secdata[$hash]);
                            } else {
                                $_v = array_merge($_v, $sum_data);
                            }
                            $_v['hash'] = '';
                            $_v['parent_hash'] = md5($v['city'].'_city');
                            $_v['sum_costs_bak'] = $_v['sum_costs'];
                            foreach ($sum_data as $key => $val) {
                                $avg_key = str_replace('sum', 'avg', $key);
                                $_v[$avg_key] = $_v['sum_car'] ? strval(round($_v[$key] / $_v['sum_car'], 2)) : strval('0.0');
                            }
                            $_v['sum_costs'] = strval(round($_v['sum_costs_bak'],2));

                            unset($_v['sum_costs_bak']);
                            $rows[] = $_v;
                        }
                    }else{
                        $_v = [];
                        $_v['hash'] = '';
                        $_v['parent_hash'] = md5($v['city'].'_city');
                        $_v = array_merge($_v, $sum_data);
                        $rows[] = $_v;
                    }
                }
                $total['city'] = '全省';
                $total['hash'] = $total_hash;
                $total['parent_hash'] = md5('全国');
                $res = $query->select(['sum_car'=>'count("one"."id")'])->filterWhere($_where)->groupBy('')->orderBy(['sum_car' => SORT_DESC])->asArray()->one();
                $total['sum_car'] = $res['sum_car'];
                $total['year_info'] = $_where['year_info'];
                $total['month_info'] = $_where['month_info'];
                foreach ($sum_data as $key => $val){
                    $avg_key = str_replace('sum','avg',$key);
                    $total[$avg_key] = $total['sum_car'] ? strval(round($total[$key]/$total['sum_car'],2)) : strval('0.0');
                }

                array_unshift($rows, $total);


                arsort($city_arr);
                foreach ($city_arr as $k=>$v){
                    $xAxis[] = $k;
                    $series[] = $v;
                }
            }else{
                $citydata = $query->select(['city'=>'dpt_sec','sum_car'=>'count("one"."id")'])->andWhere($_where)->orderBy(['sum_car' => SORT_DESC])->groupBy('dpt_sec')->asArray()->all();


                $parent_hash = md5($where['city'].'_city');


                $sum_field['city'] = 'dpt_sec';
                $res = $_query->select($sum_field)->groupBy('dpt_sec')->asArray()->all();
                $_citydata = [];
                foreach ($res as $v) {
                    $hash = md5($v['city'].'_dpt_sec');
                    $_citydata[$hash] = array_merge($v, ['hash' => $hash, 'parent_hash' => $parent_hash]);
                }



                foreach ($citydata as $v) {

                    $hash = md5($v['city'].'_dpt_sec');
                    $v['sum_car'] = intval($v['sum_car']);
                    $v['year_info'] = $_where['year_info'];
                    $v['month_info'] = $_where['month_info'];
                    if (isset($_citydata[$hash])) {
                        $v = array_merge($v, $_citydata[$hash]);
                    } else {
                        $v = array_merge($v, ['hash' => $hash, 'parent_hash' => $parent_hash], $sum_data);
                    }

                    $v['sum_costs_bak'] = $v['sum_costs'];
                    foreach ($sum_data as $key => $_v){
                        $avg_key = str_replace('sum','avg',$key);
                        $v[$avg_key] = $v['sum_car'] ? strval(round($v[$key]/$v['sum_car'],2)) : strval('0.0');
                    }
                    $v['sum_costs'] = strval(round($v['sum_costs_bak'],2));


                    unset($v['sum_costs_bak']);
                    $rows[] = $v;

                    $city_arr[$v['city']] = $v['avg_repair'];

                    $chart1data[] = ['value' => $v['sum_repair'], 'name' => $v['city']];

                }

                $res = $query->select(['city','sum_car'=>'count("one"."id")'])->where($_where)->andWhere(['city'=>$where['city']])->groupBy('city')->orderBy(['sum_car' => SORT_DESC])->asArray()->one();
                $sum_field['city'] = 'city';
                $total = $_query->select($sum_field)->andWhere(['city'=>$where['city']])->groupBy('city')->asArray()->one();
                $total['sum_car'] = $res['sum_car'];
                $total['year_info'] = $_where['year_info'];
                $total['month_info'] = $_where['month_info'];
                foreach ($sum_data as $key => $val){
                    $avg_key = str_replace('sum','avg',$key);
                    $total[$avg_key] = $total['sum_car'] ? strval(round($total[$key]/$total['sum_car'],2)) : strval('0.0');
                }

                $total['hash'] = $parent_hash;
                $total['parent_hash'] = md5('全省');
                array_unshift($rows, $total);


                arsort($city_arr);
                foreach ($city_arr as $k=>$v){
                    $xAxis[] = $k;
                    $series[] = $v;
                }
            }


            $result = ["total" => null, "totalNotFiltered" => null, "rows" => $rows, 'needgetall'=> $needgetall,'date'=>$_where];



            $result['chartdata'] = [
                'chart1' => $this->getChart1Option([
                    'title' => [
                        'text' => '租赁车辆维修费用占比（元）',
                    ],
                    'series' => [
                        [
                            'name' => '费用占比（元）',
                            'data' => $chart1data
                        ]
                    ]
                ]),
                'chart2' => $this->getChart2Option([
                    'title' => [
                        'text' => '租赁车辆单车维修费排名',
                    ],
                    'xAxis' => [
                        [
                            'data' => $xAxis,
                        ]
                    ],
                    'series' => [
                        [
                            'name' => '单车维修费（元）',
                            'data' => $series,
                        ]
                    ]
                ])
            ];

            if (Yii::$app->request->isAjax) echo_json($result);

            $this->tableTitle = array(
                array('field' => 'city', 'title' => '地市', 'align' => 'left'),
                array('field' => 'sum_car', 'title' => '租赁车辆数量', 'align' => 'right', 'formatter'=>'numericFormatter'),
                //array('field' => 'sum_costs', 'title' => '车辆总费用', 'align' => 'right', 'formatter'=>'numeric1Formatter'),
                array('field' => 'sum_repair', 'title' => '租赁车辆维修费', 'align' => 'right', 'formatter'=>'numeric1Formatter'),
                array('field' => 'avg_repair', 'title' => '单车维修费', 'align' => 'right', 'formatter'=>'numeric1Formatter'),
                array('field' => 'year_info', 'title' => '年份', 'align' => 'right'),
                array('field' => 'month_info', 'title' => '截止月份', 'align' => 'right'),
            );
            $this->tableConfig = array('table' => 'rental_costs', 'url' => $this->createRealUrl(['car/fee/rental', 'type' => $type]), 'setFieldUrl' => $this->createRealUrl(['car/fee/setfield']), 'idField' => 'hash', 'checkbox' => 0, 'dropmenu' => 1, 'refresh' => true, 'fixed' => true, 'fixedNum' => 3, 'fixedRightNumber' => 0, 'tree' => 1, 'parentIdField' => 'parent_hash', 'treeShowField' => 'city', 'treeColumn' => 0, 'expand' => false, 'exportFileName' => '租赁车辆维修费统计', 'height' => 500);
            $this->result = $result;
        }
        //保险、年检费统计
        if ($type == 3) {
            $query = FOneCarOneTable::find()->alias('one')->leftJoin('{{%f_car_info}} as c', 'one.car_id = c.car_id');

            $params = Yii::$app->request->get();

            $where = $this->getCityWhere($params);
            //$where['is_special_car'] = 0;
            $where['self_rent'] = '租赁车辆';

            $_query =  clone $query;

            //账期
            if(!empty($params['statistical_month'])){
                $statistical_month = explode('-',$params['statistical_month']);
                $_where['year_info'] = intval($statistical_month[0]);
                $_where['month_info'] = intval($statistical_month[1]);
            }else{
                $res = FOneCarOneTable::find()->orderBy('year_info desc,month_info desc' )->asArray()->one();
                $_where['year_info'] = intval($res['year_info']);
                $_where['month_info'] = intval($res['month_info']);
            }
            $_query->where(['and',['=','year_info',intval($_where['year_info'])],['<=','month_info',intval($_where['month_info'])],['=','self_rent','租赁车辆']]);
            //$_where['is_special_car'] = 0;
            $_where['self_rent'] = '租赁车辆';

            $month = $_query->select('COUNT(DISTINCT month_info) as month_num,MAX(month_info) as month_info')->asArray()->one();
            $month_num = max(intval($month['month_num']),1);
            $month_info = max($month['month_info'],1);

            //if($_where['month_info']>$month_info) $_where['month_info'] = $month_info;

            $query->where($where);

            $sum_field = ['city'=>'city',
                //'sum_costs'=>'cast(sum("sum_costs") as decimal(10,2))',
                //'sum_driving'=>'cast(sum("oil_costs"+"repair_costs"+"road_bridge_costs"+"insurance_costs") as decimal(10,2))',
                //'sum_oil'=>'cast(sum("oil_costs") as decimal(10,2))',
                //'sum_repair'=>'cast(sum("repair_costs") as decimal(10,2))',
                //'sum_road_bridge'=>'cast(sum("road_bridge_costs") as decimal(10,2))',
                //'sum_rent'=>'cast(sum("rent_costs") as decimal(10,2))',
                'sum_insurance'=>'cast(sum("insurance_costs") as decimal(10,2))',
                'sum_year_check'=>'cast(sum("year_check_costs") as decimal(10,2))',
                //'sum_other'=>'cast(sum("other_costs") as decimal(10,2))',
                //'sum_mileage'=>'cast(sum("sum_mileage") as decimal(10,2))'
            ];
            $sum_data = [
                'sum_insurance'=> 0,
                'sum_year_check'=> 0
            ];



            $city_arr = [];
            $chart1data = [];
            $rows = [];
            $needgetall = false;

            if(empty($where['city'])){
                $needgetall = true;
                $citydata = $query->select(['city','sum_car'=>'count("one"."id")'])->andWhere($_where)->groupBy('city')->orderBy($this->citySort)->asArray()->all();

                $total = $sum_data;

                $total_hash = md5('全省');
                $res = $_query->select($sum_field)->groupBy('city')->asArray()->all();
                $_citydata = [];
                foreach ($res as $v) {
                    $hash = md5($v['city'].'_city');
                    $_citydata[$hash] = array_merge($v, ['hash' => $hash, 'parent_hash' => $total_hash]);
                }
                if($params['getall']==1) {
                    $needgetall = false;
                    $sum_field['city'] = 'dpt_sec';
                    $res = $_query->select($sum_field)->groupBy('dpt_sec')->asArray()->all();
                    $secdata = [];
                    foreach ($res as $v) {
                        $hash = md5($v['city'].'_dpt_sec');
                        $secdata[$hash] = $v;
                    }
                }


                foreach ($citydata as $v) {
                    if(!in_array($v['city'],$this->cityOptions)) break;

                    $hash = md5($v['city'].'_city');
                    $v['sum_car'] = intval($v['sum_car']);
                    $v['year_info'] = $_where['year_info'];
                    $v['month_info'] = $_where['month_info'];
                    if (isset($_citydata[$hash])) {
                        $v = array_merge($v, $_citydata[$hash]);
                    } else {
                        $v = array_merge($v, ['hash' => $hash, 'parent_hash' => $total_hash], $sum_data);
                    }
                    foreach ($total as $key => $_v) {
                        $total[$key] += $v[$key];
                    }

                    $rows[] = $v;

                    $city_arr[$v['city']] = $v['sum_insurance']+$v['sum_year_check'];

                    $chart1data[] = ['value' => $v['sum_insurance']+$v['sum_year_check'], 'name' => $v['city']];
                    if($params['getall']==1) {
                        $res = $query->select(['city'=>'dpt_sec','sum_car'=>'count("one"."id")'])->where(['city' => $v['city']])->andWhere($_where)->orderBy(['sum_car' => SORT_DESC])->groupBy('dpt_sec')->asArray()->all();
                        foreach ($res as $_k => $_v) {
                            $hash = md5($_v['city'].'_dpt_sec');
                            $_v['sum_car'] = intval($_v['sum_car']);
                            $_v['year_info'] = $_where['year_info'];
                            $_v['month_info'] = $_where['month_info'];
                            if (isset($secdata[$hash])) {
                                $_v = array_merge($_v, $secdata[$hash]);
                            } else {
                                $_v = array_merge($_v, $sum_data);
                            }
                            $_v['hash'] = '';
                            $_v['parent_hash'] = md5($v['city'].'_city');
                            $rows[] = $_v;
                        }
                    }else{
                        $_v = [];
                        $_v['hash'] = '';
                        $_v['parent_hash'] = md5($v['city'].'_city');
                        $_v = array_merge($_v, $sum_data);
                        $rows[] = $_v;
                    }
                }
                $total['city'] = '全省';
                $total['hash'] = $total_hash;
                $total['parent_hash'] = md5('全国');
                $res = $query->select(['sum_car'=>'count("one"."id")'])->filterWhere($_where)->groupBy('')->orderBy(['sum_car' => SORT_DESC])->asArray()->one();
                $total['sum_car'] = $res['sum_car'];
                $total['year_info'] = $_where['year_info'];
                $total['month_info'] = $_where['month_info'];
                foreach ($sum_data as $key => $val){
                    $avg_key = str_replace('sum','avg',$key);
                    $total[$avg_key] = $total['sum_car'] ? strval(round($total[$key]/$total['sum_car'],2)) : strval('0.0');
                }

                array_unshift($rows, $total);


                arsort($city_arr);
                foreach ($city_arr as $k=>$v){
                    $xAxis[] = $k;
                    $series[] = $v;
                }
            }else{
                $citydata = $query->select(['city'=>'dpt_sec','sum_car'=>'count("one"."id")'])->andWhere($_where)->orderBy(['sum_car' => SORT_DESC])->groupBy('dpt_sec')->asArray()->all();


                $parent_hash = md5($where['city'].'_city');


                $sum_field['city'] = 'dpt_sec';
                $res = $_query->select($sum_field)->groupBy('dpt_sec')->asArray()->all();
                $_citydata = [];
                foreach ($res as $v) {
                    $hash = md5($v['city'].'_dpt_sec');
                    $_citydata[$hash] = array_merge($v, ['hash' => $hash, 'parent_hash' => $parent_hash]);
                }



                foreach ($citydata as $v) {

                    $hash = md5($v['city'].'_dpt_sec');
                    $v['sum_car'] = intval($v['sum_car']);
                    $v['year_info'] = $_where['year_info'];
                    $v['month_info'] = $_where['month_info'];
                    if (isset($_citydata[$hash])) {
                        $v = array_merge($v, $_citydata[$hash]);
                    } else {
                        $v = array_merge($v, ['hash' => $hash, 'parent_hash' => $parent_hash], $sum_data);
                    }
                    $rows[] = $v;

                    $city_arr[$v['city']] = $v['sum_insurance']+$v['sum_year_check'];

                    $chart1data[] = ['value' => $v['sum_insurance']+$v['sum_year_check'], 'name' => $v['city']];

                }

                $res = $query->select(['city','sum_car'=>'count("one"."id")'])->where($_where)->andWhere(['city'=>$where['city']])->groupBy('city')->orderBy(['sum_car' => SORT_DESC])->asArray()->one();
                $sum_field['city'] = 'city';
                $total = $_query->select($sum_field)->andWhere(['city'=>$where['city']])->groupBy('city')->asArray()->one();
                $total['sum_car'] = $res['sum_car'];
                $total['year_info'] = $_where['year_info'];
                $total['month_info'] = $_where['month_info'];
                foreach ($sum_data as $key => $val){
                    $avg_key = str_replace('sum','avg',$key);
                    $total[$avg_key] = $total['sum_car'] ? strval(round($total[$key]/$total['sum_car'],2)) : strval('0.0');
                }

                $total['hash'] = $parent_hash;
                $total['parent_hash'] = md5('全省');
                array_unshift($rows, $total);


                arsort($city_arr);
                foreach ($city_arr as $k=>$v){
                    $xAxis[] = $k;
                    $series[] = $v;
                }
            }


            $result = ["total" => null, "totalNotFiltered" => null, "rows" => $rows, 'needgetall'=> $needgetall,'date'=>$_where];



            $result['chartdata'] = [
                'chart1' => $this->getChart1Option([
                    'title' => [
                        'text' => '租赁车辆保险、年检费',
                    ],
                    'series' => [
                        [
                            'name' => '费用占比（元）',
                            'data' => $chart1data
                        ]
                    ]
                ]),
                'chart2' => $this->getChart2Option([
                    'title' => [
                        'text' => '租赁车辆保险、年检费排名',
                    ],
                    'xAxis' => [
                        [
                            'data' => $xAxis,
                        ]
                    ],
                    'series' => [
                        [
                            'name' => '租赁车辆保险、年检费（元）',
                            'data' => $series,
                        ]
                    ]
                ])
            ];

            if (Yii::$app->request->isAjax) echo_json($result);

            $this->tableTitle = array(
                array('field' => 'city', 'title' => '地市', 'align' => 'left'),
                array('field' => 'sum_car', 'title' => '租赁车辆数量', 'align' => 'right', 'formatter'=>'numericFormatter'),
                //array('field' => 'sum_costs', 'title' => '车辆总费用', 'align' => 'right', 'formatter'=>'numeric1Formatter'),
                array('field' => 'sum_insurance', 'title' => '租赁车保险费', 'align' => 'right', 'formatter'=>'numeric1Formatter'),
                array('field' => 'sum_year_check', 'title' => '租赁车年检费', 'align' => 'right', 'formatter'=>'numeric1Formatter'),
                array('field' => 'year_info', 'title' => '年份', 'align' => 'right'),
                array('field' => 'month_info', 'title' => '截止月份', 'align' => 'right'),
            );
            $this->tableConfig = array('table' => 'rental_costs', 'url' => $this->createRealUrl(['car/fee/rental', 'type' => $type]), 'setFieldUrl' => $this->createRealUrl(['car/fee/setfield']), 'idField' => 'hash', 'checkbox' => 0, 'dropmenu' => 1, 'refresh' => true, 'fixed' => true, 'fixedNum' => 3, 'fixedRightNumber' => 0, 'tree' => 1, 'parentIdField' => 'parent_hash', 'treeShowField' => 'city', 'treeColumn' => 0, 'expand' => false, 'exportFileName' => '保险、年检费统计', 'height' => 500);
            $this->result = $result;

        }
        //维修费详单
        if ($type == 4) {
            $query = FOneCarOneTable::find()->alias('one')->leftJoin('{{%f_car_info}} as c', 'one.car_id = c.car_id');
            if (Yii::$app->request->isAjax) {

                $params = Yii::$app->request->get();

                $where = $this->getCityWhere($params);
                //账期
                if(!empty($params['statistical_month'])){
                    $statistical_month = explode('-',$params['statistical_month']);
                    $where['year_info'] = intval($statistical_month[0]);
                    $where['month_info'] = intval($statistical_month[1]);
                }
                $where['self_rent'] = '租赁车辆';
                //车牌
                if(!empty($params['card_num'])){
                    $where['card_num'] = $params['card_num'];
                }
                if ($params['card_num']!=$params['card_num_text']){
                    unset($where['card_num']);
                    $query->where($where)->andWhere(['like','card_num',strtoupper($params['card_num_text'])]);
                }else{
                    $query->where($where);
                }
                $query->andWhere(['>','repair_costs',0]);

                $countQuery = clone $query;
                //分页
                if (isset($_GET['limit'])) {
                    $query->limit(intval($_GET['limit']));
                }
                if (isset($_GET['offset'])) {
                    $query->offset(intval($_GET['offset']));
                }
                //$field = ['one.*','c.*','statistical_month'=>'concat("year_info",LPAD("month_info"::text,2,\'0\'))'];
                $field = ['c_id'=>'one.car_id','one.id','one.repair_costs','card_num','city','dpt_sec','grid','car_type','self_rent','using_tag','statistical_month'=>'concat("year_info",LPAD("month_info"::text,2,\'0\'))'];

                //排序
                if (isset($_GET['sort']) && isset($_GET['sortOrder'])) {
                    $resultList = $query->select($field)->orderBy([$_GET['sort'] => ($_GET['sortOrder'] == 'asc' ? SORT_ASC : SORT_DESC)])->asArray()->all();
                } else {
                    $resultList = $query->select($field)->orderBy(['statistical_month' => SORT_DESC])->asArray()->all();
                }


                $result = ["total" => $countQuery->count(), "totalNotFiltered" => $countQuery->count(), "rows" => $resultList];
                echo_json($result);
            }
            $this->tableTitle = array(
                array('field' => 'statistical_month', 'title' => '账期', 'align' => 'center', 'sortable' => true),
                array('field' => 'card_num', 'title' => '车牌号', 'align' => 'center', 'sortable' => true),
                array('field' => 'city', 'title' => '单位', 'align' => 'center', 'sortable' => true),
                array('field' => 'dpt_sec', 'title' => '二级单位', 'align' => 'center', 'sortable' => true),
                array('field' => 'grid', 'title' => '三级单位', 'align' => 'center', 'sortable' => true),
                //array('field' => 'self_rent', 'title' => '车辆来源', 'align' => 'center', 'sortable' => true),
                array('field' => 'car_type', 'title' => '车辆类型', 'align' => 'center', 'sortable' => true),
                array('field' => 'using_tag', 'title' => '车辆使用性质', 'align' => 'center', 'sortable' => true),
                array('field' => 'repair_costs', 'title' => '维修费用', 'align' => 'right', 'formatter'=>'numeric1Formatter', 'sortable' => true,),
                //array('field' => 'insurance_costs', 'title' => '保险', 'align' => 'right', 'formatter'=>'numeric1Formatter', 'sortable' => true,),
                //array('field' => 'rent_costs', 'title' => '租赁', 'align' => 'right', 'formatter'=>'numeric1Formatter', 'sortable' => true,),
                //array('field' => 'year_check_costs', 'title' => '年检费', 'align' => 'right', 'formatter'=>'numeric1Formatter', 'sortable' => true,),
                //array('field' => 'road_bridge_costs', 'title' => '路桥', 'align' => 'right', 'formatter'=>'numeric1Formatter', 'sortable' => true,),
                //array('field' => 'other_costs', 'title' => '其他', 'align' => 'right', 'formatter'=>'numeric1Formatter', 'sortable' => true,),
                //array('field' => 'sum_mileage', 'title' => '行驶里程', 'align' => 'right', 'formatter'=>'numeric1Formatter', 'sortable' => true,),
                //array('field' => 'oil_per_km', 'title' => '百公里燃油费', 'align' => 'right', 'formatter'=>'numeric1Formatter', 'sortable' => true,),
                //array('field' => 'driving_costs_per_km', 'title' => '百公里运行费', 'align' => 'right', 'formatter'=>'numeric1Formatter', 'sortable' => true,),
                //array('field' => 'sum_costs_per_km', 'title' => '百公里费用', 'align' => 'right', 'formatter'=>'numeric1Formatter', 'sortable' => true,),
            );
            $this->tableConfig = array('table' => "fone_car_one_table", 'url' => $this->createRealUrl(['car/fee/rental', 'type' => $type]), 'setFieldUrl' => $this->createRealUrl(['car/fee/setfield']), 'idField' => "id", 'checkbox' => 0, 'dropmenu' => 1, 'pagination' => true, 'pagesize' => 20, 'refresh' => true, 'exportFileName' => '维修费详单', 'height' => 500);
        }
        //保险、年检费详单
        if ($type == 5) {
            $query = FOneCarOneTable::find()->alias('one')->leftJoin('{{%f_car_info}} as c', 'one.car_id = c.car_id');
            if (Yii::$app->request->isAjax) {

                $params = Yii::$app->request->get();

                $where = $this->getCityWhere($params);
                //账期
                if(!empty($params['statistical_month'])){
                    $statistical_month = explode('-',$params['statistical_month']);
                    $where['year_info'] = intval($statistical_month[0]);
                    $where['month_info'] = intval($statistical_month[1]);
                }
                $where['self_rent'] = '租赁车辆';
                //车牌
                if(!empty($params['card_num'])){
                    $where['card_num'] = $params['card_num'];
                }
                if ($params['card_num']!=$params['card_num_text']){
                    unset($where['card_num']);
                    $query->where($where)->andWhere(['like','card_num',strtoupper($params['card_num_text'])]);
                }else{
                    $query->where($where);
                }
                $query->andWhere(['or',['>','insurance_costs',0],['>','year_check_costs',0]]);

                $countQuery = clone $query;
                //分页
                if (isset($_GET['limit'])) {
                    $query->limit(intval($_GET['limit']));
                }
                if (isset($_GET['offset'])) {
                    $query->offset(intval($_GET['offset']));
                }
                $field = ['c_id'=>'one.car_id','one.id','one.insurance_costs','one.year_check_costs','card_num','city','dpt_sec','grid','car_type','self_rent','using_tag','statistical_month'=>'concat("year_info",LPAD("month_info"::text,2,\'0\'))'];

                //排序
                if (isset($_GET['sort']) && isset($_GET['sortOrder'])) {
                    $resultList = $query->select($field)->orderBy([$_GET['sort'] => ($_GET['sortOrder'] == 'asc' ? SORT_ASC : SORT_DESC)])->asArray()->all();
                } else {
                    $resultList = $query->select($field)->orderBy(['statistical_month' => SORT_DESC])->asArray()->all();
                }


                $result = ["total" => $countQuery->count(), "totalNotFiltered" => $countQuery->count(), "rows" => $resultList];
                echo_json($result);
            }
            $this->tableTitle = array(
                array('field' => 'statistical_month', 'title' => '账期', 'align' => 'center', 'sortable' => true),
                array('field' => 'card_num', 'title' => '车牌号', 'align' => 'center', 'sortable' => true),
                array('field' => 'city', 'title' => '单位', 'align' => 'center', 'sortable' => true),
                array('field' => 'dpt_sec', 'title' => '二级单位', 'align' => 'center', 'sortable' => true),
                array('field' => 'grid', 'title' => '三级单位', 'align' => 'center', 'sortable' => true),
                //array('field' => 'self_rent', 'title' => '车辆来源', 'align' => 'center', 'sortable' => true),
                array('field' => 'car_type', 'title' => '车辆类型', 'align' => 'center', 'sortable' => true),
                array('field' => 'using_tag', 'title' => '车辆使用性质', 'align' => 'center', 'sortable' => true),
                //array('field' => 'repair_costs', 'title' => '维修费用', 'align' => 'right', 'formatter'=>'numeric1Formatter', 'sortable' => true,),
                array('field' => 'insurance_costs', 'title' => '保险费', 'align' => 'right', 'formatter'=>'numeric1Formatter', 'sortable' => true,),
                //array('field' => 'rent_costs', 'title' => '租赁', 'align' => 'right', 'formatter'=>'numeric1Formatter', 'sortable' => true,),
                array('field' => 'year_check_costs', 'title' => '年检费', 'align' => 'right', 'formatter'=>'numeric1Formatter', 'sortable' => true,),
                //array('field' => 'road_bridge_costs', 'title' => '路桥', 'align' => 'right', 'formatter'=>'numeric1Formatter', 'sortable' => true,),
                //array('field' => 'other_costs', 'title' => '其他', 'align' => 'right', 'formatter'=>'numeric1Formatter', 'sortable' => true,),
                //array('field' => 'sum_mileage', 'title' => '行驶里程', 'align' => 'right', 'formatter'=>'numeric1Formatter', 'sortable' => true,),
                //array('field' => 'oil_per_km', 'title' => '百公里燃油费', 'align' => 'right', 'formatter'=>'numeric1Formatter', 'sortable' => true,),
                //array('field' => 'driving_costs_per_km', 'title' => '百公里运行费', 'align' => 'right', 'formatter'=>'numeric1Formatter', 'sortable' => true,),
                //array('field' => 'sum_costs_per_km', 'title' => '百公里费用', 'align' => 'right', 'formatter'=>'numeric1Formatter', 'sortable' => true,),
            );
            $this->tableConfig = array('table' => "fone_car_one_table", 'url' => $this->createRealUrl(['car/fee/rental', 'type' => $type]), 'setFieldUrl' => $this->createRealUrl(['car/fee/setfield']), 'idField' => "id", 'checkbox' => 0, 'dropmenu' => 1, 'pagination' => true, 'pagesize' => 20, 'refresh' => true, 'exportFileName' => '保险、年检费详单', 'height' => 500);
        }

        return $this->render('rental', array('type' => $type, 'koujing' => $koujing));
    }

    public function actionOwning()
    {
        $type = Yii::$app->request->get('type', 1);
        $koujing = '这里是口径描述';
        //自有车辆费用统计
        if ($type == 1) {
            $query = FOneCarOneTable::find()->alias('one')->leftJoin('{{%f_car_info}} as c', 'one.car_id = c.car_id');

            $params = Yii::$app->request->get();

            $where = $this->getCityWhere($params);
            //$where['is_special_car'] = 0;
            $where['self_rent'] = '自有车辆';

            $_query =  clone $query;

            //账期
            if(!empty($params['statistical_month'])){
                $statistical_month = explode('-',$params['statistical_month']);
                $_where['year_info'] = intval($statistical_month[0]);
                $_where['month_info'] = intval($statistical_month[1]);
            }else{
                $res = FOneCarOneTable::find()->orderBy('year_info desc,month_info desc' )->asArray()->one();
                $_where['year_info'] = intval($res['year_info']);
                $_where['month_info'] = intval($res['month_info']);
            }
            $_query->where(['and',['=','year_info',intval($_where['year_info'])],['<=','month_info',intval($_where['month_info'])],['=','self_rent','自有车辆']]);

            //$_where['is_special_car'] = 0;
            $_where['self_rent'] = '自有车辆';

            $month = $_query->select('COUNT(DISTINCT month_info) as month_num,MAX(month_info) as month_info')->asArray()->one();
            $month_num = max(intval($month['month_num']),1);
            $month_info = max($month['month_info'],1);

            //if($_where['month_info']>$month_info) $_where['month_info'] = $month_info;

            $query->where($where);

            $sum_field = ['city'=>'city',
                'sum_costs'=>'cast(sum("sum_costs") as decimal(10,2))',
                'sum_driving'=>'cast(sum("oil_costs"+"repair_costs"+"road_bridge_costs"+"insurance_costs") as decimal(10,2))',
                'sum_oil'=>'cast(sum("oil_costs") as decimal(10,2))',
                'sum_repair'=>'cast(sum("repair_costs") as decimal(10,2))',
                'sum_road_bridge'=>'cast(sum("road_bridge_costs") as decimal(10,2))',
                'sum_rent'=>'cast(sum("rent_costs") as decimal(10,2))',
                'sum_insurance'=>'cast(sum("insurance_costs") as decimal(10,2))',
                'sum_year_check'=>'cast(sum("year_check_costs") as decimal(10,2))',
                'sum_other'=>'cast(sum("other_costs") as decimal(10,2))',
                'sum_mileage'=>'cast(sum("sum_mileage") as decimal(10,2))'
            ];
            $sum_data = ['sum_costs'=>0,'sum_oil'=>0,'sum_repair'=>0,'sum_insurance'=>0,'sum_road_bridge'=>0,'sum_rent'=>0,'sum_other'=>0,'sum_year_check'=>0,'sum_mileage'=>0];



            $city_arr = [];
            $chart1data = [];
            $rows = [];
            $needgetall = false;

            if(empty($where['city'])){
                $needgetall = true;
                $citydata = $query->select(['city','sum_car'=>'count("one"."id")'])->andWhere($_where)->groupBy('city')->orderBy($this->citySort)->asArray()->all();

                $total = $sum_data;

                $total_hash = md5('全省');
                $res = $_query->select($sum_field)->groupBy('city')->asArray()->all();
                $_citydata = [];
                foreach ($res as $v) {
                    $hash = md5($v['city'].'_city');
                    $_citydata[$hash] = array_merge($v, ['hash' => $hash, 'parent_hash' => $total_hash]);
                }
                if($params['getall']==1) {
                    $needgetall = false;
                    $sum_field['city'] = 'dpt_sec';
                    $res = $_query->select($sum_field)->groupBy('dpt_sec')->asArray()->all();
                    $secdata = [];
                    foreach ($res as $v) {
                        $hash = md5($v['city'].'_dpt_sec');
                        $secdata[$hash] = $v;
                    }
                }


                foreach ($citydata as $v) {
                    if(!in_array($v['city'],$this->cityOptions)) break;

                    $hash = md5($v['city'].'_city');
                    $v['sum_car'] = intval($v['sum_car']);
                    if (isset($_citydata[$hash])) {
                        $v = array_merge($v, $_citydata[$hash]);
                    } else {
                        $v = array_merge($v, ['hash' => $hash, 'parent_hash' => $total_hash], $sum_data);
                    }

                    $v['sum_costs_bak'] = $v['sum_costs'];
                    foreach ($sum_data as $key => $_v){
                        $avg_key = str_replace('sum','avg',$key);
                        $v[$avg_key] = $v['sum_car'] ? strval(round($v[$key]/$v['sum_car'],2)) : strval('0.0');
                    }
                    $v['sum_costs'] = strval(round($v['sum_costs_bak'],2));
                    if($v['city']=='省公司本部'){
                        $v['oil_cost_hkm'] = sprintf("%.1f",$total['sum_oil']/$total['sum_mileage']*100);
                    }else{
                        $v['oil_cost_hkm'] = sprintf("%.1f",$v['sum_oil']/$v['sum_mileage']*100);
                    }
                    foreach ($total as $key => $_v) {
                        $total[$key] += $v[$key];
                    }
                    unset($v['sum_costs_bak']);
                    $rows[] = $v;

                    $city_arr[$v['city']] = $v['oil_cost_hkm'];

                    //$chart1data[] = ['value' => $v['sum_costs'], 'name' => $v['city']];
                    if($params['getall']==1) {
                        $res = $query->select(['city'=>'dpt_sec','sum_car'=>'count("one"."id")'])->where(['city' => $v['city']])->andWhere($_where)->orderBy(['sum_car' => SORT_DESC])->groupBy('dpt_sec')->asArray()->all();
                        foreach ($res as $_k => $_v) {
                            $hash = md5($_v['city'].'_dpt_sec');
                            $_v['sum_car'] = intval($_v['sum_car']);
                            if (isset($secdata[$hash])) {
                                $_v = array_merge($_v, $secdata[$hash]);
                            } else {
                                $_v = array_merge($_v, $sum_data);
                            }
                            $_v['hash'] = '';
                            $_v['parent_hash'] = md5($v['city'].'_city');
                            $_v['sum_costs_bak'] = $_v['sum_costs'];
                            foreach ($sum_data as $key => $val) {
                                $avg_key = str_replace('sum', 'avg', $key);
                                $_v[$avg_key] = $_v['sum_car'] ? strval(round($_v[$key] / $_v['sum_car'], 2)) : strval('0.0');
                            }
                            $_v['sum_costs'] = strval(round($_v['sum_costs_bak'],2));
                            if($v['city']=='省公司本部'){
                                $_v['oil_cost_hkm'] = $v['oil_cost_hkm'];
                            }else{
                                $_v['oil_cost_hkm'] = sprintf("%.1f",$_v['sum_oil']/$_v['sum_mileage']*100);
                            }

                            unset($_v['sum_costs_bak']);
                            $rows[] = $_v;
                        }
                    }else{
                        $_v = [];
                        $_v['hash'] = '';
                        $_v['parent_hash'] = md5($v['city'].'_city');
                        $_v = array_merge($_v, $sum_data);
                        $rows[] = $_v;
                    }
                }
                $total['city'] = '全省';
                $total['hash'] = $total_hash;
                $total['parent_hash'] = md5('全国');
                $res = $query->select(['sum_car'=>'count("one"."id")'])->filterWhere($_where)->groupBy('')->orderBy(['sum_car' => SORT_DESC])->asArray()->one();
                $total['sum_car'] = $res['sum_car'];
                foreach ($sum_data as $key => $val){
                    $avg_key = str_replace('sum','avg',$key);
                    $total[$avg_key] = $total['sum_car'] ? strval(round($total[$key]/$total['sum_car'],2)) : strval('0.0');
                }
                $total['oil_cost_hkm'] = sprintf("%.1f",$total['sum_oil']/$total['sum_mileage']*100);
                array_unshift($rows, $total);


                arsort($city_arr);
                foreach ($city_arr as $k=>$v){
                    $xAxis[] = $k;
                    $series[] = $v;
                }
            }else{
                $citydata = $query->select(['city'=>'dpt_sec','sum_car'=>'count("one"."id")'])->andWhere($_where)->orderBy(['sum_car' => SORT_DESC])->groupBy('dpt_sec')->asArray()->all();


                $parent_hash = md5($where['city'].'_city');


                $sum_field['city'] = 'dpt_sec';
                $res = $_query->select($sum_field)->groupBy('dpt_sec')->asArray()->all();
                $_citydata = [];
                foreach ($res as $v) {
                    $hash = md5($v['city'].'_dpt_sec');
                    $_citydata[$hash] = array_merge($v, ['hash' => $hash, 'parent_hash' => $parent_hash]);
                }



                foreach ($citydata as $v) {

                    $hash = md5($v['city'].'_dpt_sec');
                    $v['sum_car'] = intval($v['sum_car']);

                    if (isset($_citydata[$hash])) {
                        $v = array_merge($v, $_citydata[$hash]);
                    } else {
                        $v = array_merge($v, ['hash' => $hash, 'parent_hash' => $parent_hash], $sum_data);
                    }

                    $v['sum_costs_bak'] = $v['sum_costs'];
                    foreach ($sum_data as $key => $_v){
                        $avg_key = str_replace('sum','avg',$key);
                        $v[$avg_key] = $v['sum_car'] ? strval(round($v[$key]/$v['sum_car'],2)) : strval('0.0');
                    }
                    $v['sum_costs'] = strval(round($v['sum_costs_bak'],2));

                    $v['oil_cost_hkm'] = sprintf("%.1f",$v['sum_oil']/$v['sum_mileage']*100);
                    unset($v['sum_costs_bak']);
                    $rows[] = $v;

                    $city_arr[$v['city']] = $v['oil_cost_hkm'];

                    //$chart1data[] = ['value' => $v['sum_costs'], 'name' => $v['city']];

                }

                $res = $query->select(['city','sum_car'=>'count("one"."id")'])->where($_where)->andWhere(['city'=>$where['city']])->groupBy('city')->orderBy(['sum_car' => SORT_DESC])->asArray()->one();
                $sum_field['city'] = 'city';
                $total = $_query->select($sum_field)->andWhere(['city'=>$where['city']])->groupBy('city')->asArray()->one();
                $total['sum_car'] = $res['sum_car'];
                foreach ($sum_data as $key => $val){
                    $avg_key = str_replace('sum','avg',$key);
                    $total[$avg_key] = $total['sum_car'] ? strval(round($total[$key]/$total['sum_car'],2)) : strval('0.0');
                }
                $total['oil_cost_hkm'] = sprintf("%.1f",$total['sum_oil']/$total['sum_mileage']*100);
                $total['hash'] = $parent_hash;
                $total['parent_hash'] = md5('全省');
                array_unshift($rows, $total);


                arsort($city_arr);
                foreach ($city_arr as $k=>$v){
                    $xAxis[] = $k;
                    $series[] = $v;
                }
            }


            $result = ["total" => null, "totalNotFiltered" => null, "rows" => $rows, 'needgetall'=> $needgetall,'date'=>$_where];

            $chart1data = [
                ['value' => round($total['sum_oil']/10000), 'name' => '燃油费'],
                ['value' => round($total['sum_repair']/10000), 'name' => '维修费'],
                ['value' => round($total['sum_year_check']/10000), 'name' => '年检费'],
                ['value' => round($total['sum_road_bridge']/10000), 'name' => '路桥费'],
                ['value' => round($total['sum_other']/10000), 'name' => '其他费']
            ];

            $result['chartdata'] = [
                'chart1' => $this->getChart1Option([
                    'title' => [
                        'text' => '自有车辆费用占比（万元）',
                    ],
                    'series' => [
                        [
                            'name' => '费用占比（万元）',
                            'data' => $chart1data
                        ]
                    ]
                ]),
                'chart2' => $this->getChart2Option([
                    'title' => [
                        'text' => '自有车辆百公里燃油费排名',
                    ],
                    'xAxis' => [
                        [
                            'data' => $xAxis,
                        ]
                    ],
                    'series' => [
                        [
                            'name' => '百公里燃油费（元）',
                            'data' => $series,
                        ]
                    ]
                ])
            ];

            if (Yii::$app->request->isAjax) echo_json($result);

            $this->tableTitle = array(
                array('field' => 'city', 'title' => '地市', 'align' => 'left'),
                array('field' => 'sum_car', 'title' => '自有车辆数量', 'align' => 'right', 'formatter'=>'numericFormatter'),
                //array('field' => 'sum_costs', 'title' => '车辆总费用', 'align' => 'right', 'formatter'=>'numeric1Formatter'),
                array('field' => 'sum_mileage', 'title' => '里程', 'align' => 'right', 'formatter'=>'numeric1Formatter'),
                array('field' => 'sum_oil', 'title' => '燃油费', 'align' => 'right', 'formatter'=>'numeric1Formatter'),
                array('field' => 'sum_repair', 'title' => '维修费', 'align' => 'right', 'formatter'=>'numeric1Formatter'),
                array('field' => 'sum_insurance', 'title' => '保险费', 'align' => 'right', 'formatter'=>'numeric1Formatter'),
                array('field' => 'sum_rent', 'title' => '租赁费', 'align' => 'right', 'formatter'=>'numeric1Formatter'),
                array('field' => 'sum_year_check', 'title' => '年检费', 'align' => 'right', 'formatter'=>'numeric1Formatter'),
                array('field' => 'sum_road_bridge', 'title' => '路桥费', 'align' => 'right', 'formatter'=>'numeric1Formatter'),
                array('field' => 'sum_other', 'title' => '其他费', 'align' => 'right', 'formatter'=>'numeric1Formatter'),
                //array('field' => 'avg_oil', 'title' => '单车月燃油费', 'align' => 'right', 'formatter'=>'numeric1Formatter'),
                array('field' => 'oil_cost_hkm', 'title' => '百公里燃油费', 'align' => 'right', 'formatter'=>'numeric1Formatter'),
                array('field' => 'avg_insurance', 'title' => '平均单车保险费', 'align' => 'right', 'formatter'=>'numeric1Formatter'),
                array('field' => 'avg_repair', 'title' => '平均单车修理费', 'align' => 'right', 'formatter'=>'numeric1Formatter'),
                //array('field' => 'avg_other', 'title' => '单车月其他费', 'align' => 'right', 'formatter'=>'numeric1Formatter'),
            );
            $this->tableConfig = array('table' => 'rental_costs', 'url' => $this->createRealUrl(['car/fee/owning', 'type' => $type]), 'setFieldUrl' => $this->createRealUrl(['car/fee/setfield']), 'idField' => 'hash', 'checkbox' => 0, 'dropmenu' => 1, 'refresh' => true, 'fixed' => true, 'fixedNum' => 3, 'fixedRightNumber' => 0, 'tree' => 1, 'parentIdField' => 'parent_hash', 'treeShowField' => 'city', 'treeColumn' => 0, 'expand' => false, 'exportFileName' => '自有车辆费用统计', 'height' => 500);
            $this->result = $result;

        }

        //发生租赁费详单
        if ($type == 2) {

            $query = FOneCarOneTable::find()->alias('one')->leftJoin('{{%f_car_info}} as c', 'one.car_id = c.car_id');
            if (Yii::$app->request->isAjax) {

                $params = Yii::$app->request->get();

                $where = $this->getCityWhere($params);
                //账期
                if(!empty($params['statistical_month'])){
                    $statistical_month = explode('-',$params['statistical_month']);
                    $where['year_info'] = intval($statistical_month[0]);
                    $where['month_info'] = intval($statistical_month[1]);
                }
                $where['self_rent'] = '自有车辆';
                //车牌
                if(!empty($params['card_num'])){
                    $where['card_num'] = $params['card_num'];
                }
                if ($params['card_num']!=$params['card_num_text']){
                    unset($where['card_num']);
                    $query->where($where)->andWhere(['like','card_num',strtoupper($params['card_num_text'])]);
                }else{
                    $query->where($where);
                }
                $query->andWhere(['>','rent_costs',0]);

                $countQuery = clone $query;
                //分页
                if (isset($_GET['limit'])) {
                    $query->limit(intval($_GET['limit']));
                }
                if (isset($_GET['offset'])) {
                    $query->offset(intval($_GET['offset']));
                }
                //$field = ['one.*','c.*','statistical_month'=>'concat("year_info",LPAD("month_info"::text,2,\'0\'))'];
                $field = ['c_id'=>'one.car_id','one.id','one.oil_costs','one.repair_costs','one.insurance_costs','one.rent_costs','one.year_check_costs','one.road_bridge_costs',
                    'one.other_costs','one.attend_days','one.sum_mileage',
                    'card_num','city','dpt_sec','grid','car_type','self_rent','using_tag','car_brand','emissions','emissions_type','oil_type','c.enabled_date',
                    'car_age'=>'age(CURRENT_DATE, c.enabled_date::date)',
                    'statistical_month'=>'concat("year_info",LPAD("month_info"::text,2,\'0\'))'];

                //排序
                if (isset($_GET['sort']) && isset($_GET['sortOrder'])) {
                    $resultList = $query->select($field)->orderBy([$_GET['sort'] => ($_GET['sortOrder'] == 'asc' ? SORT_ASC : SORT_DESC)])->asArray()->all();
                } else {
                    $resultList = $query->select($field)->orderBy(['statistical_month' => SORT_DESC])->asArray()->all();
                }

                foreach ($resultList as &$v){
                    $v['car_age'] = str_replace(['years','mons','days','year','mon','day',' '],['年','月','天','年','月','天',''],$v['car_age']);
                }

                $result = ["total" => $countQuery->count(), "totalNotFiltered" => $countQuery->count(), "rows" => $resultList];
                echo_json($result);
            }

            $this->tableTitle = array(
                array('field' => 'statistical_month', 'title' => '账期', 'align' => 'center', 'sortable' => true),
                array('field' => 'card_num', 'title' => '车牌号', 'align' => 'center', 'sortable' => true),
                //array('field' => 'own_dpt', 'title' => '车辆所属单位', 'align' => 'center', 'sortable' => true),
                array('field' => 'city', 'title' => '单位', 'align' => 'center', 'sortable' => true),
                array('field' => 'dpt_sec', 'title' => '二级单位', 'align' => 'center', 'sortable' => true),
                array('field' => 'grid', 'title' => '三级单位', 'align' => 'center', 'sortable' => true),
                array('field' => 'self_rent', 'title' => '车辆来源', 'align' => 'center', 'sortable' => true),
                array('field' => 'car_type', 'title' => '车辆类型', 'align' => 'center', 'sortable' => true),
                array('field' => 'using_tag', 'title' => '车辆使用性质', 'align' => 'center', 'sortable' => true),
                array('field' => 'oil_costs', 'title' => '燃油', 'align' => 'right', 'formatter'=>'numeric1Formatter', 'sortable' => true,),
                array('field' => 'repair_costs', 'title' => '维修', 'align' => 'right', 'formatter'=>'numeric1Formatter', 'sortable' => true,),
                array('field' => 'insurance_costs', 'title' => '保险', 'align' => 'right', 'formatter'=>'numeric1Formatter', 'sortable' => true,),
                array('field' => 'rent_costs', 'title' => '租赁', 'align' => 'right', 'formatter'=>'numeric1Formatter', 'sortable' => true,),
                array('field' => 'year_check_costs', 'title' => '年检费', 'align' => 'right', 'formatter'=>'numeric1Formatter', 'sortable' => true,),
                array('field' => 'road_bridge_costs', 'title' => '路桥', 'align' => 'right', 'formatter'=>'numeric1Formatter', 'sortable' => true,),
                array('field' => 'other_costs', 'title' => '其他', 'align' => 'right', 'formatter'=>'numeric1Formatter', 'sortable' => true,),
                //array('field' => 'driving_time', 'title' => '行驶时长', 'align' => 'right', 'formatter'=>'numeric1Formatter', 'sortable' => true,),
                array('field' => 'attend_days', 'title' => '行驶天数', 'align' => 'right', 'formatter'=>'numericFormatter', 'sortable' => true,),
                array('field' => 'sum_mileage', 'title' => '里程', 'align' => 'right', 'formatter'=>'numeric1Formatter', 'sortable' => true,),
                array('field' => 'car_brand', 'title' => '车辆品牌', 'align' => 'center', 'sortable' => true),
                array('field' => 'emissions', 'title' => '排量', 'align' => 'center', 'sortable' => true),
                array('field' => 'emissions_type', 'title' => '排量类型', 'align' => 'center', 'sortable' => true),
                array('field' => 'oil_type', 'title' => '油料类型', 'align' => 'center', 'sortable' => true),
                array('field' => 'enabled_date', 'title' => '登记日期', 'align' => 'center', 'sortable' => true),
                array('field' => 'car_age', 'title' => '车龄', 'align' => 'center', 'sortable' => true),
                //array('field' => 'env_lev', 'title' => '环保等级', 'align' => 'center', 'sortable' => true),
                //array('field' => 'engine_id', 'title' => '发动机号', 'align' => 'center', 'sortable' => true),
                //array('field' => 'fra_id', 'title' => '车架号', 'align' => 'center', 'sortable' => true),
                //array('field' => 'init_mileage', 'title' => '初始里程', 'align' => 'center', 'sortable' => true),
                //array('field' => 'driving_id', 'title' => '行驶证号', 'align' => 'center', 'sortable' => true),
                //array('field' => 'car_state', 'title' => '车辆状态', 'align' => 'center', 'sortable' => true),
                //array('field' => 'asset_id', 'title' => '资产编号', 'align' => 'center', 'sortable' => true),
            );

            $this->tableConfig = array('table' => "fone_car_one_table", 'url' => $this->createRealUrl(['car/fee/owning', 'type' => $type]), 'setFieldUrl' => $this->createRealUrl(['car/fee/setfield']), 'idField' => "id", 'checkbox' => 0, 'dropmenu' => 1, 'pagination' => true, 'pagesize' => 20, 'refresh' => true, 'exportFileName' => '单车费用详单', 'height' => 500, 'tableClass' => 'table-nowrap');

        }

        return $this->render('owning', array('type' => $type, 'koujing' => $koujing));
    }

    public function actionCost()
    {
        $type = Yii::$app->request->get('type', 1);
        //费用统计
        if ($type == 1) {
            if (Yii::$app->request->isAjax) {
                $params = Yii::$app->request->get();
                if (empty($params['start_date'])) $params['start_date'] = get_date(TIMESTAMP, 'Y-1');
                if (empty($params['end_date'])) $params['end_date'] = get_date(TIMESTAMP, 'Y-m');
                //日期计算
                $startdateInfo = explode('-', $params['start_date']);
                $year = $startdateInfo[0];
                $startmonth = intval($startdateInfo[1]);
                $enddateInfo = explode('-', $params['end_date']);
                $endmonth = intval($enddateInfo[1]);

                $sql_oncaronetable = $this->getOneCarOneTable($params, 'car_id, oil_costs, insurance_costs, repair_costs, road_bridge_costs');

                $sql_carinfo = $this->getCarinfo(array_merge($params, ['is_special_car' => 0]), 'car_id, card_num, city, dpt_sec, grid, self_rent, car_type, using_tag');

                $table = '(' . $sql_oncaronetable . ') ocot  JOIN (' . $sql_carinfo . ')  ci ON ( ocot.car_id = ci.car_id ) ';
                $field = 'ci.city,
            cast(sum(ocot.oil_costs) as decimal(10,2)) AS oil_costs,
            cast(sum(ocot.insurance_costs) as decimal(10,2)) AS insurance_costs,
            cast(sum(ocot.repair_costs) as decimal(10,2)) AS repair_costs, 
            cast(sum(ocot.road_bridge_costs) as decimal(10,2)) AS road_bridge_costs,
            (cast(sum(ocot.oil_costs) as decimal(10,2))+cast(sum(ocot.insurance_costs) as decimal(10,2))+cast(sum(ocot.repair_costs) as decimal(10,2))+cast(sum(ocot.road_bridge_costs) as decimal(10,2))) AS sum_cost,
            ' . $year . ' AS year,
            (SELECT (case when max(month_info)>= ' . $endmonth . ' then ' . $endmonth . ' else max(month_info) end) from wz_f_one_car_one_table WHERE year_info = ' . $year . ') AS month';

                $data = $this->getPingjiaData($table, $field, $params);
                //没有查询地市条件的时候
                $dataAll['rows'] = [];
                if (empty($params['city_1'])) {
                    //获取全省数据
                    $dataAll = $this->getPingjiaData($table, str_replace('ci.city,', '', $field), $params, 1);
                    $dataAll['rows'][0]['city'] = '全省';
                    $dataAll['rows'][0]['hash'] = md5('全省');
                    $dataAll['rows'][0]['parent_hash'] = md5('全国');

                    foreach ($data['rows'] as $k => $u) {
                        $data['rows'][$k]['hash'] = md5($u['city']);
                        $data['rows'][$k]['parent_hash'] = md5('全省');

                        //获取二级单位数据
                        $params1 = $params;
                        $params1['city_name_1'] = $u['city'];
                        $params1['city'] = $u['city'];
                        $sql_oncaronetable = $this->getOneCarOneTable($params1, 'car_id, oil_costs, insurance_costs, repair_costs, road_bridge_costs');

                        $sql_carinfo = $this->getCarinfo(array_merge($params1, ['is_special_car' => 0]), 'car_id, card_num, city, dpt_sec, grid, self_rent, car_type, using_tag');

                        $table = '(' . $sql_oncaronetable . ') ocot  JOIN (' . $sql_carinfo . ')  ci ON ( ocot.car_id = ci.car_id ) ';
                        $tempData = $this->getPingjiaData($table, str_replace('ci.city,', '', $field), $params1);
                        if ($tempData != '') {
                            foreach ($tempData['rows'] as $k1 => $u1) {
                                $tempData['rows'][$k1]['hash'] = '';
                                $tempData['rows'][$k1]['parent_hash'] = md5($u['city']);
                                $tempData['rows'][$k1]['city'] = '';
                            }
                            $data['rows'] = array_merge($data['rows'], $tempData['rows']);
                        }

                    }
                    // $data['rows'] = array_merge($dataAll['rows'],$data['rows']);
                } else {
                    foreach ($data['rows'] as $k => $u) {
                        $data['rows'][$k]['hash'] = md5($u['city']);
                        $data['rows'][$k]['parent_hash'] = '';
                    }
                }
                //图标数据
                /* unset($params['city'],$params['dpt_sec'],$params['grid'],$params['card_num']);
                $sql_carinfo = $this->getCarinfo(array_merge($params,['is_special_car'=>0]),'car_id',false);
                $sql = 'SELECT
                        round(sum(ocot.oil_costs)/10000,2) AS oil_costs,
                        round(sum(ocot.insurance_costs)/10000,2) AS insurance_costs ,
                        round(sum(ocot.repair_costs)/10000,2) AS repair_costs,
                        round(sum(ocot.road_bridge_costs)/10000,2) AS road_bridge_costs
                    FROM (' .$sql_oncaronetable. ') ocot  JOIN (' .$sql_carinfo. ')  ci ON ( ocot.car_id = ci.car_id ) ';

                $data['count'] = Yii::$app->db->createCommand($sql)->queryOne();*/
                if (!empty($dataAll['rows'])) $data['rows'] = array_merge($dataAll['rows'], $data['rows']);
                echo_json($data);
            }

            $this->tableTitle = array(
                array('field' => 'city', 'title' => '地市', 'align' => 'center'),
                array('field' => 'dpt_sec', 'title' => '二级单位', 'align' => 'center'),
                array('field' => 'grid', 'title' => '网格', 'align' => 'center'),
                array('field' => 'self_rent', 'title' => '车辆来源', 'align' => 'center'),
                array('field' => 'car_type', 'title' => '车辆类型', 'align' => 'center'),
                array('field' => 'using_tag', 'title' => '车辆使用性质', 'align' => 'center', 'class' => 'bigwidth'),
                array('field' => 'oil_costs', 'title' => '油料费', 'align' => 'center', 'sortable' => true,),
                array('field' => 'insurance_costs', 'title' => '保险费', 'align' => 'center', 'sortable' => true,),
                array('field' => 'repair_costs', 'title' => '维修费用', 'align' => 'center', 'sortable' => true,),
                array('field' => 'road_bridge_costs', 'title' => '路桥费', 'align' => 'center', 'sortable' => true,),
                array('field' => 'sum_cost', 'title' => '合计', 'align' => 'center', 'sortable' => true,),
                array('field' => 'year', 'title' => '年份', 'align' => 'center', 'sortable' => true),
                array('field' => 'month', 'title' => '截止月份', 'align' => 'center', 'sortable' => true),
            );
            $this->tableConfig = array('table' => 'FOperatingCostMonitor', 'url' => $this->createRealUrl(['car/fee/cost', 'type' => $type]), 'setFieldUrl' => $this->createRealUrl(['car/pingjia/setfield']), 'idField' => 'hash', 'checkbox' => 0, 'dropmenu' => 1, 'pagination' => true, 'pagesize' => 20, 'refresh' => true, 'tree' => 1, 'parentIdField' => 'parent_hash', 'treeShowField' => 'city', 'treeColumn' => 0, 'expand' => false, 'exportFileName' => '运行成本统计', 'height' => 500);
        }
        //费用明细
        if ($type == 2) {
            $query = FOneCarOneTable::find()->alias('onecar')->leftJoin('{{%f_car_info}}', 'onecar.car_id = {{%f_car_info}}.car_id');
            if (Yii::$app->request->isAjax) {
                $data = [];
                $params = Yii::$app->request->get();
                if (empty($params['start_date'])) $params['start_date'] = get_date(TIMESTAMP, 'Y-1');
                if (empty($params['end_date'])) $params['end_date'] = get_date(TIMESTAMP, 'Y-12');
                $where = $this->getWhere($query, $params);
                if (!empty($where)) {
                    $sql = join(" and ", $where);
                    $query->where($sql);
                }
                $countQuery = clone $query;

                //分页
                if (isset($_GET['limit'])) {
                    $query->limit(intval($_GET['limit']));
                }
                if (isset($_GET['offset'])) {
                    $query->offset(intval($_GET['offset']));
                }
                //排序
                if (isset($_GET['sort']) && isset($_GET['sortOrder'])) {
                    $resultList = $query->select('*,(oil_costs+repair_costs+insurance_costs+road_bridge_costs) as sum_costs')->orderBy([$_GET['sort'] => ($_GET['sortOrder'] == 'asc' ? SORT_ASC : SORT_DESC)])->asArray()->all();
                } else {
                    $resultList = $query->select('*,(oil_costs+repair_costs+insurance_costs+road_bridge_costs) as sum_costs')->orderBy(['onecar.id' => SORT_DESC])->asArray()->all();
                }

                foreach ($resultList as $result) {
                    $sum_costs = round(($result['oil_costs'] + $result['repair_costs'] + $result['insurance_costs'] + $result['road_bridge_costs']), 2);

                    $data[] = array('card_num' => $result['card_num'], 'city' => $result['city'], 'dpt_sec' => $result['dpt_sec'], 'grid' => $result['grid'], 'car_type' => $result['car_type'], 'using_tag' => $result['using_tag'], 'oil_costs' => round($result['oil_costs'], 2), 'insurance_costs' => round($result['insurance_costs'], 2), 'repair_costs' => round($result['repair_costs'], 2), 'road_bridge_costs' => round($result['road_bridge_costs'], 2), 'sum_costs' => $sum_costs, 'year' => $result['year_info'], 'month' => $result['month_info']);
                }
                $result = ["total" => $countQuery->count(), "totalNotFiltered" => $countQuery->count(), "rows" => $data];
                echo_json($result);
            }
            $this->tableTitle = array(
                array('field' => 'card_num', 'title' => '车牌号', 'align' => 'center'),
                array('field' => 'city', 'title' => '地市', 'align' => 'center'),
                array('field' => 'dpt_sec', 'title' => '二级单位', 'align' => 'center'),
                array('field' => 'grid', 'title' => '网格', 'align' => 'center'),
                array('field' => 'car_type', 'title' => '车辆类型', 'align' => 'center'),
                array('field' => 'using_tag', 'title' => '车辆使用性质', 'align' => 'center', 'class' => 'bigwidth'),
                array('field' => 'oil_costs', 'title' => '油料费', 'align' => 'center', 'sortable' => true),
                array('field' => 'insurance_costs', 'title' => '保险费', 'align' => 'center', 'sortable' => true),
                array('field' => 'repair_costs', 'title' => '维修费用', 'align' => 'center', 'sortable' => true),
                array('field' => 'road_bridge_costs', 'title' => '路桥费', 'align' => 'center', 'sortable' => true),
                array('field' => 'sum_costs', 'title' => '合计', 'align' => 'center', 'sortable' => true),
                array('field' => 'year', 'title' => '年份', 'align' => 'center'),
                array('field' => 'month', 'title' => '月份', 'align' => 'center'),
            );
            $this->tableConfig = array('table' => "fone_car_one_table", 'url' => $this->createRealUrl(['car/fee/cost', 'type' => $type]), 'setFieldUrl' => $this->createRealUrl(['car/fee/setfield']), 'idField' => "id", 'checkbox' => 0, 'dropmenu' => 1, 'pagination' => true, 'pagesize' => 20, 'refresh' => true, 'exportFileName' => '运行成本明细', 'height' => 500);
        }


        $koujing = '这里是口径描述';
        return $this->render('cost', array('type' => $type, 'koujing' => $koujing));
    }


}