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

<?php

namespace app\modules\car\controllers;

use app\common\controllers\BController;
use app\modules\car\models\FCarInfo;
use app\modules\car\models\FCarScrap;
use app\modules\car\models\FInputPolicy;
use app\modules\car\models\FMileageAttend;
use app\modules\car\models\FOneCarOneTable;
use app\modules\car\models\FSpcCar;
use Yii;

class EffController 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_mileage'=>'cast(sum("sum_mileage") as decimal(10,2))',
                'sum_attend_days'=>'cast(sum("attend_days") as decimal(10,0))',
                'sum_work_days'=>'cast(sum("work_days") as decimal(10,0))',
                ];
            $sum_data = ['sum_mileage'=>0,'sum_attend_days'=>0,'sum_work_days'=>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);
                    }
                    if($v['city']=='省公司本部'){
                        $v['avg_mileage'] = strval(round($v['sum_mileage']/$v['sum_work_days'],2));
                        $v['per_attend'] = strval(round($total['sum_attend_days']/$total['sum_work_days']*100,2));
                    }else{
                        $v['avg_mileage'] = strval(round($v['sum_mileage']/$v['sum_work_days'],2));
                        $v['per_attend'] = strval(round($v['sum_attend_days']/$v['sum_work_days']*100,2));
                    }

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

                    $rows[] = $v;

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

                    $chart1data[] = ['value' => round($v['sum_mileage']/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');
                            $_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');
                            if($_v['city']=='省公司本部'){
                                $_v['avg_mileage'] = $v['avg_mileage'];
                                $_v['per_attend'] = $v['per_attend'];
                            }else{
                                $_v['avg_mileage'] = strval(round($_v['sum_mileage']/$_v['sum_work_days'],2));
                                $_v['per_attend'] = strval(round($_v['sum_attend_days']/$_v['sum_work_days']*100,2));
                            }

                            $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['avg_mileage'] = strval(round($total['sum_mileage']/$total['sum_work_days'],2));
                $total['per_attend'] = strval(round($total['sum_attend_days']/$total['sum_work_days']*100,2));
                array_unshift($rows, $total);


            }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['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['avg_mileage'] = strval(round($v['sum_mileage']/$v['sum_work_days'],2));
                    $v['per_attend'] = strval(round($v['sum_attend_days']/$v['sum_work_days']*100,2));
                    $rows[] = $v;

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

                    $chart1data[] = ['value' => round($v['sum_mileage']/10000), '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['avg_mileage'] = strval(round($total['sum_mileage']/$total['sum_work_days'],2));
                $total['per_attend'] = strval(round($total['sum_attend_days']/$total['sum_work_days']*100,2));
                $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_mileage', 'title' => '行驶里程（公里）', 'align' => 'right', 'formatter'=>'numeric1Formatter'),
                array('field' => 'sum_attend_days', 'title' => '行驶天数', 'align' => 'right', 'formatter'=>'numericFormatter'),
                array('field' => 'sum_work_days', 'title' => '工作日天数', 'align' => 'right', 'formatter'=>'numericFormatter'),
                array('field' => 'avg_mileage', 'title' => '平均单车日里程', 'align' => 'right', 'formatter'=>'numeric1Formatter'),
                array('field' => 'per_attend', 'title' => '出勤率（%）', 'align' => 'right', 'formatter'=>'numeric1Formatter'),
            );
            $this->tableConfig = array('table' => 'total_eff', 'url' => $this->createRealUrl(['car/eff/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;


        }

        //效能详单
        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]);
                }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);
                }

                $countQuery = clone $query;
                //分页
                if (isset($_GET['limit'])) {
                    $query->limit(intval($_GET['limit']));
                }
                if (isset($_GET['offset'])) {
                    $query->offset(intval($_GET['offset']));
                }
                $field = ['os.*','car.*',
                    'avg_mileage'=>'cast((case when "work_days">0 then ("sum_mileage")/"work_days" else 0 end) as decimal(10,2))',
                    'per_attendance'=>'cast((case when "work_days">0 then ("attend_days"/"work_days"::float8)*100 else 0 end) as decimal(10,2))',
                    'statistical_month'=>'concat("year_info",LPAD("month_info"::text,2,\'0\'))'];
                    //'\''.($_where['year_info']*100+$_where['month_info']).'\' as "statistical_month"'];

                //排序
                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' => 'sum_mileage', 'title' => '行驶里程（公里）', 'align' => 'right', 'formatter'=>'numeric1Formatter', 'sortable' => true,),
                array('field' => 'avg_mileage', 'title' => '日均行驶里程（公里）', 'align' => 'right', 'formatter'=>'numeric1Formatter', 'sortable' => true,),
                array('field' => 'per_attendance', 'title' => '出勤率（%）', 'align' => 'right', 'formatter'=>'numeric1Formatter', 'sortable' => true,),
                array('field' => 'attend_days', 'title' => '行驶天数', 'align' => 'right', 'formatter'=>'numericFormatter', 'sortable' => true),
                array('field' => 'work_days', 'title' => '工作日天数', 'align' => 'right', 'formatter'=>'numericFormatter', 'sortable' => true),


            );

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


        }


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




    public function actionLow()
    {
        $type = Yii::$app->request->get('type', 1);

        $res = FSpcCar::find()->where(['is_spec'=>1])->asArray()->all();
        $spc_cars = [];
        foreach ($res as $v){
            $spc_cars[] = $v['card_num'];
        }
        $res = FCarScrap::find()->asArray()->all();
        //$spc_cars = [];
        foreach ($res as $v){
            $spc_cars[] = $v['card_num'];
        }



        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['is_inefficient'] = 1;

            $_query =  clone $query;

            $policy = FInputPolicy::find()->where(['is_active'=>1])->asArray()->one();

            if(empty($policy)){
                $policy = ['thd_km_per_day'=>20,'thd_att'=>50];
            }
            $cale = 'sum(case when coalesce((attend_days/nullif(work_days, 0)::float8*100),0)<'.$policy['thd_att'].' and coalesce((sum_mileage/nullif(work_days, 0)::float8),0)<'.$policy['thd_km_per_day'].($spc_cars?' and "card_num" not in (\''.implode('\',\'',$spc_cars).'\')':'').' and "is_special_car"<>0 then 1 else 0 end)';
            //$cale = 'sum(case when is_inefficient=1 then 1 else 0 end)';

            //->andWhere('(CASE WHEN ("car_new"=1 OR "car_move"=1) THEN (to_date(to_char(allocation_in_date,\'9999-99\'),\'yyyy-MM\')+INTERVAL\'3 month\')<=to_date(to_char("year_info"*100+"month_info",\'9999-99\'),\'yyyy-MM\') ELSE 1=1 END)');


            //账期
            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->leftJoin('(SELECT car_id FROM {{%f_one_car_one_table}} WHERE ("year_info" = '.intval($_where['year_info']).') AND ("month_info" <= '.intval($_where['month_info']).') GROUP BY "car_id"
             //   HAVING coalesce((sum(attend_days)/nullif(sum(work_days), 0)::float8*100),0)<'.$policy['thd_att'].' and coalesce((sum(sum_mileage)/nullif(sum(work_days), 0)::float8),0)<'.$policy['thd_km_per_day'].') as "oc"','one.car_id = oc.car_id');
            //$_query->where(['and',['=','year_info',intval($_where['year_info'])],['<=','month_info',intval($_where['month_info'])],['=','is_special_car',0],['not in','card_num',$spc_cars]]);
            //$_query->andWhere('(CASE WHEN ("car_new"=1 OR "car_move"=1) THEN (to_date(to_char(allocation_in_date,\'9999-99\'),\'yyyy-MM\')+INTERVAL\'3 month\')<=to_date(to_char("year_info"*100+"month_info",\'9999-99\'),\'yyyy-MM\') ELSE 1=1 END)');

            //$_where['is_special_car'] = 0;


            $res = FCarInfo::find()->where(['car_move'=>1])->andWhere('(to_date(to_char(allocation_in_date,\'9999-99\'),\'yyyy\'))=to_date(to_char('.(intval($_where['year_info'])*100).',\'9999-99\'),\'yyyy\')')->asArray()->all();
            $car_ids = [];
            foreach ($res as $v){
                $_res = FCarInfo::find()->where(['and',['=','card_num',$v['card_num']],['<','car_id',$v['car_id']],['<>','city',$v['city']]])->asArray()->all();
                foreach ($_res as $_v){
                    $car_ids[] = $_v['car_id'];
                }
            }

            $_query->andWhere('"year_info" = '.intval($_where['year_info']).' AND "month_info" =:mi')
                ->andWhere('(CASE WHEN ("car_new"=1 OR "car_move"=1) THEN (to_date(to_char(allocation_in_date,\'9999-99\'),\'yyyy-MM\')+INTERVAL\'3 month\')<=to_date(to_char("year_info"*100+"month_info",\'9999-99\'),\'yyyy-MM\') ELSE 1=1 END)');
            $_query->select(['c_card_num'=>'card_num', 'c_city'=>'city',
                'car_id'=>'max("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('card_num,city');

            $base_sql = $_query->createCommand()->getRawSql();

            $sql = str_replace('=:mi','<= '.intval($_where['month_info']),$base_sql);
            $_sql = str_replace('=:mi','= '.intval($_where['month_info']),$base_sql);

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

            $query->where($where);

            $where['is_special_car'] = 0;

            $_query->where($where);
            $__query->where($where);



            $cale_where = '(coalesce((attend_days/nullif(work_days, 0)::float8*100),0)<'.$policy['thd_att'].' and coalesce((sum_mileage/nullif(work_days, 0)::float8),0)<'.$policy['thd_km_per_day'].')';
            $_query->andWhere($cale_where)->andWhere(['>','os.car_id',0]);
            $__query->andWhere($cale_where)->andWhere(['>','os.car_id',0]);
            if(!empty($spc_cars)){
                $_query->andWhere(['not in','car.card_num',$spc_cars]);
                $__query->andWhere(['not in','car.card_num',$spc_cars]);
            }

            if(!empty($car_ids)){
                $_query->andWhere(['not in','car.car_id',$car_ids]);
                $__query->andWhere(['not in','car.car_id',$car_ids]);
            }

            $sum_field = ['city'=>'city',
                //'sum_mileage'=>'cast(sum("sum_mileage") as decimal(10,2))',
                //'sum_attend_days'=>'cast(sum("attend_days") as decimal(10,0))',
                //'sum_work_days'=>'cast(sum("work_days") as decimal(10,0))',
                'sum_inefficient_car'=>'count(DISTINCT os.car_id)'
            ];
            $sum_data = ['sum_mileage'=>0,'sum_attend_days'=>0,'sum_work_days'=>0,'sum_inefficient_car'=>0,'month_inefficient_car'=>0,'sum_car'=>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], ['month_inefficient_car'=>0]);
                }
                $res = $__query->select(['city','month_inefficient_car'=>'count(DISTINCT os.car_id)'])->groupBy('city')->asArray()->all();
                foreach ($res as $v) {
                    $hash = md5($v['city'].'_city');
                    $_citydata[$hash] = array_merge(['hash' => $hash, 'parent_hash' => $total_hash], ($_citydata[$hash]?$_citydata[$hash]:['sum_inefficient_car'=>0]),$v);
                }
                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] = array_merge($v, ['month_inefficient_car'=>0]);
                    }
                    $res = $__query->select(['city'=>'dpt_sec','month_inefficient_car'=>'count(DISTINCT os.car_id)'])->groupBy('dpt_sec')->asArray()->all();
                    foreach ($res as $v) {
                        $hash = md5($v['city'].'_dpt_sec');
                        $secdata[$hash] = array_merge(($secdata[$hash]?$secdata[$hash]:['sum_inefficient_car'=>0]),$v);
                    }
                }


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

                    $hash = md5($v['city'].'_city');
                    $v['sum_car'] = $sum_data['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);
                    }

                    /*if($v['city']=='省公司本部'){
                        $v['month_inefficient_car'] = ceil($v['sum_car']*round($total['month_inefficient_car']/$total['sum_car']*100,2)/100);
                        $v['sum_inefficient_car'] = ceil($v['sum_car']*round($total['sum_inefficient_car']/$total['sum_car']*100,2)/100);
                    }*/
                    $v['per_month'] = strval(round($v['month_inefficient_car']/$v['sum_car']*100,2));
                    $v['per_inefficient'] = strval(round($v['sum_inefficient_car']/$v['sum_car']*100,2));


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

                    $v['year_info'] = $_where['year_info'];
                    $v['month_info'] = $_where['month_info'];
                    $rows[] = $v;



                    $chart1data[] = ['value' => $v['sum_inefficient_car'], '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'] = $sum_data['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');
                            /*if($_v['city']=='省公司本部'){
                                $_v['month_inefficient_car'] = $v['month_inefficient_car'];
                                $_v['sum_inefficient_car'] = $v['sum_inefficient_car'];
                            }*/
                            $_v['per_month'] = strval(round($_v['month_inefficient_car']/$_v['sum_car']*100,2));
                            $_v['per_inefficient'] = strval(round($_v['sum_inefficient_car']/$_v['sum_car']*100,2));
                            $_v['year_info'] = $_where['year_info'];
                            $_v['month_info'] = $_where['month_info'];
                            $rows[] = $_v;
                        }
                    }else{
                        $_v = [];
                        $_v['hash'] = '';
                        $_v['parent_hash'] = md5($v['city'].'_city');
                        $sum_data['sum_car'] = 0;
                        $_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'] = intval($res['sum_car']);
                $res = $__query->select(['month_inefficient_car'=>'count(DISTINCT os.car_id)'])->groupBy('')->asArray()->one();
                $total['month_inefficient_car'] = $res['month_inefficient_car'];
                $total['per_month'] = strval(round($total['month_inefficient_car']/$total['sum_car']*100,2));
                $total['per_inefficient'] = strval(round($total['sum_inefficient_car']/$total['sum_car']*100,2));
                $total['year_info'] = $_where['year_info'];
                $total['month_info'] = $_where['month_info'];
                array_unshift($rows, $total);


            }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], ['month_inefficient_car'=>0]);
                }
                $res = $__query->select(['city'=>'dpt_sec','month_inefficient_car'=>'count(DISTINCT os.car_id)'])->groupBy('dpt_sec')->asArray()->all();
                foreach ($res as $v) {
                    $hash = md5($v['city'].'_dpt_sec');
                    $_citydata[$hash] = array_merge(['hash' => $hash, 'parent_hash' => $parent_hash], ($_citydata[$hash]?$_citydata[$hash]:['sum_inefficient_car'=>0]),$v);
                }


                foreach ($citydata as $v) {

                    $hash = md5($v['city'].'_dpt_sec');
                    $v['sum_car'] = $sum_data['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['per_month'] = strval(round($v['month_inefficient_car']/$v['sum_car']*100,2));
                    $v['per_inefficient'] = strval(round($v['sum_inefficient_car']/$v['sum_car']*100,2));
                    $v['year_info'] = $_where['year_info'];
                    $v['month_info'] = $_where['month_info'];
                    $rows[] = $v;



                    $chart1data[] = ['value' => $v['sum_inefficient_car'], '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'] = intval($res['sum_car']);
                $res = $__query->select(['month_inefficient_car'=>'count(DISTINCT os.car_id)'])->andWhere(['city'=>$where['city']])->groupBy('city')->asArray()->one();
                $total['month_inefficient_car'] = intval($res['month_inefficient_car']);
                $total['per_month'] = strval(round($total['month_inefficient_car']/$total['sum_car']*100,2));
                $total['per_inefficient'] = strval(round($total['sum_inefficient_car']/$total['sum_car']*100,2));
                $total['year_info'] = $_where['year_info'];
                $total['month_info'] = $_where['month_info'];
                $total['hash'] = $parent_hash;
                $total['parent_hash'] = md5('全省');
                array_unshift($rows, $total);

            }



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

            for ($i=1;$i<=$_where['month_info'];$i++){
                $xAxis[] = $_where['year_info'].'/'.str_pad($i,2,'0',STR_PAD_LEFT);
                $city_where = [];
                if($where['city']) $city_where['city'] = $where['city'];

                $sql = str_replace('=:mi','<= '.$i,$base_sql);
                $_sql = str_replace('=:mi','= '.$i,$base_sql);

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

                $city_where['is_special_car'] = 0;

                $_query->where($city_where);
                $__query->where($city_where);

                $cale_where = '(coalesce((attend_days/nullif(work_days, 0)::float8*100),0)<'.$policy['thd_att'].' and coalesce((sum_mileage/nullif(work_days, 0)::float8),0)<'.$policy['thd_km_per_day'].')';
                $_query->andWhere($cale_where)->andWhere(['>','os.car_id',0]);
                $__query->andWhere($cale_where)->andWhere(['>','os.car_id',0]);
                if(!empty($spc_cars)){
                    $_query->andWhere(['not in','car.card_num',$spc_cars]);
                    $__query->andWhere(['not in','car.card_num',$spc_cars]);
                }
                if(!empty($car_ids)){
                    $_query->andWhere(['not in','car.car_id',$car_ids]);
                    $__query->andWhere(['not in','car.car_id',$car_ids]);
                }

                $res = $_query->select(['sum_inefficient_car'=>'count(DISTINCT os.car_id)'])->asArray()->one();
                $data_month[] = $res['sum_inefficient_car'];

                $res = $__query->select(['month_inefficient_car'=>'count(DISTINCT os.car_id)'])->asArray()->one();;
                $data_sum[] = $res['month_inefficient_car'];
            }

            $result['chartdata'] = [
                'chart1' => $this->getChart1Option([
                    'title' => [
                        'text' => '低效车地市占比',
                    ],
                    'series' => [
                        [
                            'name' => '低效车数量',
                            'data' => $chart1data
                        ]
                    ]
                ]),
                'chart2' => $this->getChart2Option([
                    'title' => [
                        'text' => '',
                    ],
                    'legend' => [
                        'data' => ['累计算法低效', '单月算法低效']
                    ],
                    'xAxis' => [
                        [
                            'type' => 'category',
                            'data' => $xAxis,
                            'axisPointer' => [
                                'type' => 'shadow'
                            ]
                        ]
                    ],
                    'yAxis' => [
                        [
                            'type' => 'value',
                            'name' => '累计算法低效',
                            'min' => 0,
                            'max' => 200,
                            'interval' => 50,
                            'axisLabel' => [
                                'formatter' => '{value} 辆'
                            ]
                        ],
                        [
                            'type' => 'value',
                            'name' => '单月算法低效',
                            'min' => 0,
                            'max' => 200,
                            'interval' => 50,
                            'axisLabel' => [
                                'formatter' => '{value} 辆'
                            ]
                        ]
                    ],
                    'series' => [
                        [
                            'name' => '累计算法低效',
                            'type' => 'bar',
                            'tooltip' => json_decode("{
        valueFormatter: function (value) {
          return value + ' 辆';
        }",true),
                            'data' => $data_sum
                        ],
                        [
                            'name' => '单月算法低效',
                            'type' => 'line',
                            'yAxisIndex' => 1,
                            'tooltip' => json_decode("{
        valueFormatter: function (value) {
          return value + ' 辆';
        }",true),
                            'data' => $data_month
                        ]
                    ]
                ])
            ];

            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' => 'month_inefficient_car', 'title' => '单月算法低效车辆', 'align' => 'right'),
                array('field' => 'per_month', 'title' => '单月低效车占比', 'align' => 'right', 'formatter'=>'numeric1Formatter'),
                array('field' => 'sum_inefficient_car', 'title' => '累计算法低效车辆', 'align' => 'right'),
                array('field' => 'per_inefficient', 'title' => '累计低效车占比', 'align' => 'right', 'formatter'=>'numeric1Formatter'),
                array('field' => 'year_info', 'title' => '年份', 'align' => 'right'),
                array('field' => 'month_info', 'title' => '截止月份', 'align' => 'right'),
            );
            $this->tableConfig = array('table' => 'low_eff', 'url' => $this->createRealUrl(['car/eff/low', '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]);
                }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']);
                }

                $res = FCarInfo::find()->where(['car_move'=>1])->andWhere('(to_date(to_char(allocation_in_date,\'9999-99\'),\'yyyy\'))=to_date(to_char('.(intval($_where['year_info'])*100).',\'9999-99\'),\'yyyy\')')->asArray()->all();
                $car_ids = [];
                foreach ($res as $v){
                    $_res = FCarInfo::find()->where(['and',['=','card_num',$v['card_num']],['<','car_id',$v['car_id']],['<>','city',$v['city']]])->asArray()->all();
                    foreach ($_res as $_v){
                        $car_ids[] = $_v['car_id'];
                    }
                }

                //车牌
                if(!empty($params['card_num'])){
                    $where['c.card_num'] = $params['card_num'];
                }
                if ($params['card_num']!=$params['card_num_text']){
                    unset($where['c.card_num']);
                    $query->where($where)->andWhere(['like','c.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'])]])
                    ->andWhere('(CASE WHEN ("car_new"=1 OR "car_move"=1) THEN (to_date(to_char(allocation_in_date,\'9999-99\'),\'yyyy-MM\')+INTERVAL\'3 month\')<=to_date(to_char("year_info"*100+"month_info",\'9999-99\'),\'yyyy-MM\') ELSE 1=1 END)');
                $query->select(['c_card_num'=>'card_num', 'c_city'=>'city',
                    'car_id'=>'max("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('card_num,city');
                $sql = $query->createCommand()->getRawSql();

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

                $where['is_special_car'] = 0;

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



                $policy = FInputPolicy::find()->where(['is_active'=>1])->asArray()->one();

                if(empty($policy)){
                    $policy = ['thd_km_per_day'=>20,'thd_att'=>50];
                }
                $cale_where = '(coalesce((attend_days/nullif(work_days, 0)::float8*100),0)<'.$policy['thd_att'].' and coalesce((sum_mileage/nullif(work_days, 0)::float8),0)<'.$policy['thd_km_per_day'].')';

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

                if(!empty($spc_cars)) $query->andWhere(['not in','car.card_num',$spc_cars]);

                if(!empty($car_ids)){
                    $query->andWhere(['not in','car.car_id',$car_ids]);
                }

                $countQuery = clone $query;
                //分页
                if (isset($_GET['limit'])) {
                    $query->limit(intval($_GET['limit']));
                }
                if (isset($_GET['offset'])) {
                    $query->offset(intval($_GET['offset']));
                }
                $field = ['os.*','car.*',
                    'avg_mileage'=>'cast((case when "work_days">0 then ("sum_mileage")/"work_days" else 0 end) as decimal(10,2))',
                    'per_attendance'=>'cast((case when "work_days">0 then ("attend_days"/"work_days"::float8)*100 else 0 end) as decimal(10,2))',
                    '\''.($_where['year_info']*100+$_where['month_info']).'\' as "statistical_month"'];

                //排序
                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' => 'sum_mileage', 'title' => '行驶里程（公里）', 'align' => 'right', 'formatter'=>'numeric1Formatter', 'sortable' => true,),
                array('field' => 'avg_mileage', 'title' => '日均行驶里程（公里）', 'align' => 'right', 'formatter'=>'numeric1Formatter', 'sortable' => true,),
                array('field' => 'per_attendance', 'title' => '出勤率（%）', 'align' => 'right', 'formatter'=>'numeric1Formatter', 'sortable' => true,),
                array('field' => 'attend_days', 'title' => '行驶天数', 'align' => 'right', 'formatter'=>'numericFormatter', 'sortable' => true),
                array('field' => 'work_days', 'title' => '工作日天数', 'align' => 'right', 'formatter'=>'numericFormatter', 'sortable' => true),


            );

            $this->tableConfig = array('table' => "fone_car_one_table", 'url' => $this->createRealUrl(['car/eff/low', 'type' => $type]), 'setFieldUrl' => $this->createRealUrl(['car/eff/setfield']), 'idField' => 'id', 'checkbox' => 0, 'dropmenu' => 1, 'pagination' => true, 'pagesize' => 20, 'refresh' => true, 'exportFileName' => '低效详单', 'height' => 500);


        }

        $koujing = '低效车辆指，以年累计里程、年出勤天数计算，日均行驶里程<20公里且出勤率<50%的车辆。出现低效车辆，发管理工单，预警到县分领导。市分低效车辆占比超过5%，发管理工单，预警到市分办公室主任，超过10%，预警升级，预警到市分主管领导。';

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