<?php

namespace app\modules\car\controllers;

use app\common\controllers\BController;
use app\modules\car\models\FCarInfo;
use app\modules\car\models\FOneCarOneTable;
use app\modules\car\models\FUnDispatchOrdersCrossBorderStatistics;
use app\modules\car\models\FViolationDetails;
use app\modules\car\models\FUnAnnualInspectionleasedVehiclesDetails;
use app\modules\car\models\FSeverelyOverMaintainedLeasedVehiclesDetails;
use app\modules\car\models\FUnDispatchOrdersDetails;
use app\modules\car\models\FUnDispatchOrdersCrossBorder;

use Yii;

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

    public function actionDispatch()
    {
        $type = Yii::$app->request->get('type', 1);
        //无派单越界统计
        if ($type == 1) {
            $query = FUnDispatchOrdersCrossBorderStatistics::find();


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

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

            //账期
            if(!empty($params['statistical_month'])){
                $statistical_month = str_replace('-','',$params['statistical_month']);
            }else{
                $res = FUnDispatchOrdersCrossBorderStatistics::find()->orderBy(['statistical_month' => SORT_DESC])->asArray()->one();
                $statistical_month = $res['statistical_month'];
            }

            $one_query = FOneCarOneTable::find()->alias('one')->leftJoin('{{%f_car_info}} as c', 'one.car_id = c.car_id');
            $_where['year_info'] = substr($statistical_month,0,4);
            $_where['month_info'] = intval(substr($statistical_month,-2));
            $res = FOneCarOneTable::find()->where($_where)->asArray()->one();
            if(empty($res)){
                $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']);
            }

            $where['statistical_month'] = $statistical_month;

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

            if(empty($where['city'])){
                $needgetall = true;
                $citydata = $query->where($where)->andWhere('"dpt_sec" is null')->orderBy($this->citySort)->asArray()->all();

                $res = $one_query->select(['city','sum_car'=>'count("one"."id")'])->where($_where)->groupBy('city')->asArray()->all();
                $_citydata = [];
                foreach ($res as $v) {
                    $hash = md5($v['city'].'_city');
                    $_citydata[$hash] = $v['sum_car'];
                }

                $total_hash = md5('全省');

                if($params['getall']==1) {
                    $needgetall = false;

                    $res = $one_query->select(['dpt_sec','sum_car'=>'count("one"."id")'])->where($_where)->groupBy('dpt_sec')->asArray()->all();
                    $secdata = [];
                    foreach ($res as $v) {
                        $hash = md5($v['dpt_sec'].'_dpt_sec');
                        $secdata[$hash] = $v['sum_car'];
                    }
                }


                foreach ($citydata as $v) {
                    if(!in_array($v['city'],$this->cityOptions)) break;
                    $hash = md5($v['city'].'_city');
                    $v = array_merge($v, ['sum_car'=>$_citydata[$hash],'hash' => $hash, 'parent_hash' => $total_hash]);


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

                    if($params['getall']==1) {
                        $res = $query->where($where)->andWhere(['city' => $v['city']])->andWhere('"dpt_sec" is not null')->asArray()->all();
                        foreach ($res as $_k => $_v) {
                            $hash = md5($_v['dpt_sec'].'_dpt_sec');
                            $_v['hash'] = '';
                            $_v['parent_hash'] = md5($v['city'].'_city');
                            $_v['city'] = $_v['dpt_sec'];
                            $_v['sum_car'] = $secdata[$hash];
                            $rows[] = $_v;
                        }
                    }else{
                        $_v = [];
                        $_v['hash'] = '';
                        $_v['parent_hash'] = md5($v['city'].'_city');
                        $rows[] = $_v;
                    }
                }


                $total = $query->where($where)->andWhere(['city' => '全省'])->asArray()->one();
                $res = $one_query->select(['sum_car'=>'count("one"."id")'])->filterWhere($_where)->groupBy('')->asArray()->one();
                $total['sum_car'] = $res['sum_car'];
                $total['hash'] = $total_hash;
                $total['parent_hash'] = md5('全国');

            }else{
                $citydata = $query->where($where)->andWhere('"dpt_sec" is not null')->asArray()->all();

                $res = $one_query->select(['dpt_sec','sum_car'=>'count("one"."id")'])->where($_where)->andWhere(['city'=>$where['city']])->groupBy('dpt_sec')->asArray()->all();
                $_citydata = [];
                foreach ($res as $v) {
                    $hash = md5($v['dpt_sec'].'_dpt_sec');
                    $_citydata[$hash] = $v['sum_car'];
                }

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

                foreach ($citydata as $v) {
                    $hash = md5($v['dpt_sec'].'_dpt_sec');
                    $v = array_merge($v, ['sum_car'=>$_citydata[$hash],'hash' => $hash, 'parent_hash' => $parent_hash]);
                    $v['city'] = $v['dpt_sec'];
                    $rows[] = $v;

                    $chart1data[] = ['value' => $v['un_dispatch_cross_border_num_sum'], 'name' => $v['dpt_sec']];

                }

                $total = $query->where($where)->andWhere('"dpt_sec" is null')->asArray()->one();
                $res = $one_query->select(['sum_car'=>'count("one"."id")'])->where($_where)->andWhere(['city'=>$where['city']])->groupBy('')->asArray()->one();
                $total['sum_car'] = $res['sum_car'];
                $total['hash'] = $parent_hash;
                $total['parent_hash'] = md5('全省');
            }


            array_unshift($rows, $total);

            $year = substr($statistical_month,0,4);

            for ($i=1;$i<12;$i++){
                $month = $year*100+$i;
                if($month>$statistical_month) break;
                $xAxis[] = $month;
                $where['statistical_month'] = $month;
                if(empty($where['city'])) $where['city'] = '全省';
                $res = $query->where($where)->andWhere('"dpt_sec" is null')->orderBy(['statistical_month' => SORT_ASC])->asArray()->one();
                if($res){
                    $num[$i] = $res['un_dispatch_cross_border_num_sum'];
                }else{
                    $num[$i] = 0;
                }
                $series[] =  $i>1 ? $num[$i]-$num[$i-1] : $num[$i];
            }


            $result = ["total" => null, "totalNotFiltered" => null, "rows" => $rows, 'needgetall'=> $needgetall, 'date'=>['year_info'=>$year,'month_info'=>substr($statistical_month,-2)]];

            $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' => 'departures_car_num_sum', 'title' => '出车次数累计', 'align' => 'right', 'formatter'=>'numericFormatter'),
                array('field' => 'un_dispatch_num_sum', 'title' => '无派单次数累计', 'align' => 'right', 'formatter'=>'numericFormatter'),
                array('field' => 'cross_border__num_sum', 'title' => '越界次数累计', 'align' => 'right', 'formatter'=>'numericFormatter'),
                array('field' => 'un_dispatch_cross_border_num_sum', 'title' => '无派单越界次数累计', 'align' => 'right', 'formatter'=>'numericFormatter'),
            );

            $this->tableConfig = array('table' => 'costs_driving', 'url' => $this->createRealUrl(['car/safety/dispatch', 'type' => $type]), 'setFieldUrl' => $this->createRealUrl(['car/safety/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 = FUnDispatchOrdersDetails::find();
            if (Yii::$app->request->isAjax) {

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

                $where = $this->getCityWhere($params);
                //账期
                if(!empty($params['statistical_month'])){
                    $where['statistical_month'] = str_replace('-','',$params['statistical_month']);
                }

                //车牌
                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);
                }

                $countQuery = clone $query;
                //分页
                if (isset($_GET['limit'])) {
                    $query->limit(intval($_GET['limit']));
                }
                if (isset($_GET['offset'])) {
                    $query->offset(intval($_GET['offset']));
                }
                $field = ['*'];

                //排序
                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' => 'own_dpt', 'title' => '车辆所属单位', 'align' => 'center', 'sortable' => true),
                array('field' => 'violation_time_start', 'title' => '违规开始时间', 'align' => 'center', 'sortable' => true),
                array('field' => 'violation_time_end', 'title' => '违规结束时间', 'align' => 'center', 'sortable' => true),
                array('field' => 'violation_time_duration', 'title' => '违规持续时间（分）', 'align' => 'right', 'formatter'=>'numericFormatter', 'sortable' => true,),
            );
            $this->tableConfig = array('table' => FUnDispatchOrdersDetails::shortTableName(), 'url' => $this->createRealUrl(['car/safety/dispatch', 'type' => $type]), 'setFieldUrl' => $this->createRealUrl(['car/safety/setfield']), 'idField' => FUnDispatchOrdersDetails::modelPrimaryKey(), 'checkbox' => 0, 'dropmenu' => 1, 'pagination' => true, 'pagesize' => 20, 'refresh' => true, 'exportFileName' => '无派单越界详单', 'height' => 500);


        }

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


    public function actionViolation()
    {
        $type = Yii::$app->request->get('type', 1);
        //违章统计
        if ($type == 1) {


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

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


            $_query = FViolationDetails::find();

            //账期
            if(!empty($params['statistical_month'])){
                $statistical_month = str_replace('-','',$params['statistical_month']);
                $date = date('Y-m-d',strtotime("+1 months",strtotime($params['statistical_month'].'-01')));
            }else{
                $res = FViolationDetails::find()->orderBy(['cast("violation_time" as timestamp)' => SORT_DESC])->asArray()->one();
                $date = $res['violation_time'];
                $res = FViolationDetails::find()->orderBy(['statistical_month' => SORT_DESC])->asArray()->one();
                $statistical_month = $res['statistical_month'];
            }

            $one_query = FOneCarOneTable::find()->alias('one')->leftJoin('{{%f_car_info}} as c', 'one.car_id = c.car_id');
            $one_where['year_info'] = substr($statistical_month,0,4);
            $one_where['month_info'] = intval(substr($statistical_month,-2));
            $res = FOneCarOneTable::find()->where($one_where)->asArray()->one();
            if(empty($res)){
                $res = FOneCarOneTable::find()->orderBy('year_info desc,month_info desc' )->asArray()->one();
                $one_where['year_info'] = intval($res['year_info']);
                $one_where['month_info'] = intval($res['month_info']);
            }

            //$where['statistical_month'] = $statistical_month;


            $start_date = date('Y-01-01',strtotime($date));

            $_where = ['and',['>=','cast("violation_time" as timestamp)',$start_date],['<=','cast("violation_time" as timestamp)',$date]];

            $sum_field = ["city"=>"city","sum_violation_car"=>"count(DISTINCT card_num)","sum_violation"=>"count(car_id)",
                "sum_processing_yes"=>"sum(CASE WHEN offline_actual_processing_status like '已处理%' THEN 1 ELSE 0 END)",
                "sum_processing_no"=>"sum(CASE WHEN offline_actual_processing_status like '未处理%' THEN 1 ELSE 0 END)",
                //"sum_processing_no_180"=>"sum(CASE WHEN offline_actual_processing_status like '未处理%' AND unprocessed_duration_of_violation>=180 THEN 1 ELSE 0 END)"
            ];
            $sum_data = ['sum_violation_car'=>0,'sum_violation'=>0,'sum_processing_yes'=>0,'sum_processing_no'=>0,'sum_processing_no_180'=>0];

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

                $total = $sum_data;
                $total['sum_car'] = 0;
                $total_hash = md5('全省');
                $res = $_query->select($sum_field)->where($_where)->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]);
                }
                $res = $_query->select(["city"=>"city",
                    "sum_processing_no_180"=>"sum(CASE WHEN offline_actual_processing_status like '未处理%' AND unprocessed_duration_of_violation>=180 THEN 1 ELSE 0 END)"
                ])->where(['statistical_month'=>$statistical_month])->groupBy('city')->asArray()->all();
                foreach ($res as $v) {
                    $hash = md5($v['city'].'_city');
                    $_citydata[$hash] = array_merge($_citydata[$hash],$v);
                }
                if($params['getall']==1) {
                    $needgetall = false;
                    $sum_field['city'] = "dpt_sec";
                    $res = $_query->select($sum_field)->where($_where)->groupBy('dpt_sec')->asArray()->all();
                    $secdata = [];
                    foreach ($res as $v) {
                        $hash = md5($v['city'].'_dpt_sec');
                        $secdata[$hash] = $v;
                    }
                    $res = $_query->select(["city"=>"dpt_sec",
                        "sum_processing_no_180"=>"sum(CASE WHEN offline_actual_processing_status like '未处理%' AND unprocessed_duration_of_violation>=180 THEN 1 ELSE 0 END)"
                    ])->where(['statistical_month'=>$statistical_month])->groupBy('dpt_sec')->asArray()->all();
                    foreach ($res as $v) {
                        $hash = md5($v['city'].'_dpt_sec');
                        $secdata[$hash] = array_merge($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];
                    }
                    $rows[] = $v;
                    $chart1data[] = ['value' => $v['sum_processing_no_180'], 'name' => $v['city']];
                    if($params['getall']==1) {
                        $res = $one_query->select(['city'=>'dpt_sec','sum_car'=>'count("one"."id")'])->where($one_where)->andWhere(['city' => $v['city']])->orderBy(['sum_car' => SORT_DESC])->groupBy('dpt_sec')->asArray()->all();
                        foreach ($res as $_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');
                            $rows[] = $_v;
                        }
                    }else{
                        $_v = [];
                        $_v['hash'] = '';
                        $_v['parent_hash'] = md5($v['city'].'_city');
                        $_v = array_merge($_v, $sum_data);
                        $rows[] = $_v;
                    }
                }
                $res = $one_query->select(['sum_car'=>'count("one"."id")'])->where($one_where)->groupBy('')->orderBy('')->asArray()->one();
                $total['sum_car'] = $res['sum_car'];
                $total['city'] = '全省';
                $total['hash'] = $total_hash;
                $total['parent_hash'] = md5('全国');
                array_unshift($rows, $total);

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

                $_where[] = ['=','city',$where['city']];

                $parent_hash = md5($where['city'].'_city');
                $total['hash'] = $parent_hash;
                $total['parent_hash'] = md5('全省');
                $total['sum_car'] = $one_query->where($one_where)->andWhere(['city'=>$where['city']])->groupBy('')->orderBy('')->count();
                $res =  $_query->select($sum_field)->where($_where)->groupBy('city')->asArray()->one();

                $res1 = $_query->select(["city"=>"city",
                    "sum_processing_no_180"=>"sum(CASE WHEN offline_actual_processing_status like '未处理%' AND unprocessed_duration_of_violation>=180 THEN 1 ELSE 0 END)"
                ])->where(['statistical_month'=>$statistical_month,'city'=>$where['city']])->groupBy('city')->asArray()->one();

                $rows[] = array_merge($total,($res?:$sum_data),$res1,['city'=>$where['city']]);

                $sum_field['city'] = "dpt_sec";
                $res = $_query->select($sum_field)->where($_where)->groupBy('dpt_sec')->asArray()->all();
                $secdata = [];
                foreach ($res as $v){
                    $hash = md5($v['city'].'_dpt_sec');
                    $secdata[$hash] = $v;
                }
                $res = $_query->select(["city"=>"dpt_sec",
                    "sum_processing_no_180"=>"sum(CASE WHEN offline_actual_processing_status like '未处理%' AND unprocessed_duration_of_violation>=180 THEN 1 ELSE 0 END)"
                ])->where(['statistical_month'=>$statistical_month,'city'=>$where['city']])->groupBy('dpt_sec')->asArray()->all();
                foreach ($res as $v) {
                    $hash = md5($v['city'].'_dpt_sec');
                    $secdata[$hash] = array_merge($secdata[$hash],$v);
                }

                foreach ($citydata as $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'] = $parent_hash;
                    $rows[] = $v;
                    $chart1data[] = ['value' => $v['sum_processing_no_180'], 'name' => $v['city']];
                }
            }


            $result = ["total" => null, "totalNotFiltered" => null, "rows" => $rows, 'needgetall'=> $needgetall, 'date'=>['year_info'=>substr($statistical_month,0,4),'month_info'=>substr($statistical_month,-2)]];

            $year = substr($date,0,4);

            for ($i=1;$i<12;$i++){
                $month = $year*100+$i;
                if($month>$statistical_month) break;
                $xAxis[] = $month;
                $where['statistical_month'] = $month;
                $res = FViolationDetails::find()->select('statistical_month,sum(CASE WHEN offline_actual_processing_status like \'未处理%\' AND unprocessed_duration_of_violation>=180 THEN 1 ELSE 0 END) as sum_processing_no_180')->where($where)->groupBy('statistical_month')->orderBy(['statistical_month' => SORT_ASC])->asArray()->one();
                if($res){
                    $series[] = $res['sum_processing_no_180'];
                }else{
                    $series[] = 0;
                }
            }


            $result['chartdata'] = [
                'chart1' => $this->getChart1Option([
                    'title' => [
                        'text' => '违章超180天未处理次数',
                    ],
                    'series' => [
                        [
                            'name' => '违章长期未处理次数',
                            'data' => $chart1data
                        ]
                    ]
                ]),
                'chart2' => $this->getChart2Option([
                    'title' => [
                        'text' => '违章超180天未处理',
                    ],
                    'xAxis' => [
                        [
                            'data' => $xAxis,
                        ]
                    ],
                    'series' => [
                        [
                            'name' => '违章长期未处理',
                            'data' => $series,
                        ]
                    ]
                ])
            ];

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

            $this->tableTitle = array(
                array('field' => 'city', 'title' => '地市', 'align' => 'left'),
                array('field' => 'sum_car', 'title' => '车辆总数量', 'align' => 'right', 'formatter'=>'numericFormatter'),
                array('field' => 'sum_violation_car', 'title' => '违章车辆数量', 'align' => 'right', 'formatter'=>'numericFormatter'),
                array('field' => 'sum_violation', 'title' => '违章次数', 'align' => 'right', 'formatter'=>'numericFormatter'),
                array('field' => 'sum_processing_yes', 'title' => '已处理违章', 'align' => 'right', 'formatter'=>'numericFormatter'),
                array('field' => 'sum_processing_no', 'title' => '未处理违章', 'align' => 'right', 'formatter'=>'numericFormatter'),
                array('field' => 'sum_processing_no_180', 'title' => '违章超180天未处理', 'align' => 'right', 'formatter'=>'numericFormatter'),
            );
            $this->tableConfig = array('table' => 'FViolationDetails', 'url' => $this->createRealUrl(['car/safety/violation', 'type' => $type]), 'setFieldUrl' => $this->createRealUrl(['car/safety/setfield']), 'idField' => 'hash', 'checkbox' => 0, 'dropmenu' => 1, 'pagesize' => 20, 'refresh' => true, 'tree' => 1, 'parentIdField' => 'parent_hash', 'treeShowField' => 'city', 'treeColumn' => 0, 'expand' => false, 'exportFileName' => '违章统计', 'height' => 500);
        }
        //单车违章次数TOP50
        if ($type == 2) {

            if (Yii::$app->request->isAjax) {
                $query = FViolationDetails::find();
                $data = [];
                $params = Yii::$app->request->get();

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


                //账期
                if(!empty($params['statistical_month'])){
                    $where['statistical_month'] = str_replace('-','',$params['statistical_month']);
                }else{
                    $res = FViolationDetails::find()->orderBy(['statistical_month' => SORT_DESC])->asArray()->one();
                    $where['statistical_month'] = $res['statistical_month'];
                }
                $date = date('Y-m-d',strtotime("+1 months",strtotime($where['statistical_month'].'01')));
                $start_date = date('Y-01-01',strtotime($date));
                $query->where($where)->andWhere(['and',['>=','cast("violation_time" as timestamp)',$start_date],['<=','cast("violation_time" as timestamp)',$date]]);


                $query->limit(100);
                $resultList = $query->select('card_num,max(city) as city,max(dpt_sec) as dpt_sec,max(grid) as grid,max(statistical_month) as statistical_month,count(car_id) as cell_num')->groupBy('card_num')->orderBy(['cell_num' => SORT_DESC])->asArray()->all();
                $i = 1;
                $c = 0;
                foreach ($resultList as $v){
                    if($i==50) $c = $v['cell_num'];
                    if($i>50 && $v['cell_num']<$c) break;
                    $data[] = array_merge($v,['idx'=>$i]);
                    $i++;
                }

                $result = ["total" => null, "totalNotFiltered" => null, "rows" => $data, 'date'=>['year_info'=>substr($_where['statistical_month'],0,4),'month_info'=>substr($_where['statistical_month'],-2)]];
                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' => 'cell_num', 'title' => '违章次数累计', 'align' => 'center', 'formatter'=>'numericFormatter'),
            );
            $this->tableConfig = array('table' => FViolationDetails::shortTableName(), 'url' => $this->createRealUrl(['car/safety/violation', 'type' => $type]), 'setFieldUrl' => $this->createRealUrl(['car/safety/setfield']), 'idField' => FViolationDetails::modelPrimaryKey(), 'checkbox' => 0, 'dropmenu' => 1, 'pagination' => false, 'pagesize' => 20, 'refresh' => true, 'exportFileName' => '单车违章次数TOP50', 'height' => 500);


        }

        //违章详单
        if ($type == 3) {

            if (Yii::$app->request->isAjax) {
                $query = FViolationDetails::find();
                $params = Yii::$app->request->get();

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

                //账期
                if(!empty($params['statistical_month'])){
                    $where['statistical_month'] = str_replace('-','',$params['statistical_month']);
                }

                //车牌
                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);
                }


                $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->orderBy([$_GET['sort'] => ($_GET['sortOrder'] == 'asc' ? SORT_ASC : SORT_DESC)])->asArray()->all();
                } else {
                    $resultList = $query->orderBy(['car_id' => SORT_DESC])->asArray()->all();
                }

                $result = ["total" => $countQuery->count(), "totalNotFiltered" => $countQuery->count(), "rows" => $resultList];
                echo_json($result);
            }
            $this->tableTitle = array(
                array('field' => 'car_id', 'title' => '序号', 'align' => 'center', 'sortable' => true),
                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' => 'violation_time', 'title' => '违章时间', 'align' => 'center', 'sortable' => true),
                array('field' => 'violation_location', 'title' => '违章地点', 'align' => 'center', 'sortable' => true),
                array('field' => 'violation_details', 'title' => '违章详情', 'align' => 'center', 'sortable' => true, 'width' => '200px'),
                array('field' => 'deduction_points', 'title' => '扣分', 'align' => 'center', 'sortable' => true),
                array('field' => 'fine', 'title' => '罚款', 'align' => 'center', 'sortable' => true),
                array('field' => 'processing_time', 'title' => '处理时间', 'align' => 'center', 'sortable' => true),
                array('field' => 'unprocessed_duration_of_violation', 'title' => '违章未处理时长（天）', 'align' => 'center', 'sortable' => true),
                array('field' => 'offline_actual_processing_status', 'title' => '线下实际处理状态（最终状态）', 'align' => 'center', 'sortable' => true),
            );
            $this->tableConfig = array('table' => FViolationDetails::shortTableName(), 'url' => $this->createRealUrl(['car/safety/violation', 'type' => $type]), 'setFieldUrl' => $this->createRealUrl(['car/safety/setfield']), 'idField' => 'car_id', 'checkbox' => 0, 'dropmenu' => 1, 'pagination' => true, 'refresh' => true, 'exportFileName' => '违章详单', 'height' => 500);


        }


        $koujing = '这里是口径描述';
        return $this->render('violation', array('type' => $type, 'koujing' => $koujing));
    }
    //年检情况
    public function actionAnnually()
    {
        $type = Yii::$app->request->get('type', 1);
        if ($type == 1) {


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

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

                $where['self_rent'] = '租赁车辆';


                $_query = FUnAnnualInspectionleasedVehiclesDetails::find();

                //账期
                if(!empty($params['statistical_month'])){
                    $statistical_month = str_replace('-','',$params['statistical_month']);
                }else{
                    $res = FUnAnnualInspectionleasedVehiclesDetails::find()->orderBy(['statistical_month' => SORT_DESC])->asArray()->one();
                    $statistical_month = $res['statistical_month'];
                }
            $_where['statistical_month'] = $statistical_month;

            $one_query = FOneCarOneTable::find()->alias('one')->leftJoin('{{%f_car_info}} as c', 'one.car_id = c.car_id');
            $one_where['year_info'] = substr($statistical_month,0,4);
            $one_where['month_info'] = intval(substr($statistical_month,-2));
            $res = FOneCarOneTable::find()->where($one_where)->asArray()->one();
            if(empty($res)){
                $res = FOneCarOneTable::find()->orderBy('year_info desc,month_info desc' )->asArray()->one();
                $one_where['year_info'] = intval($res['year_info']);
                $one_where['month_info'] = intval($res['month_info']);
            }
            $one_where['self_rent'] = '租赁车辆';

                $sum_field = "count(DISTINCT card_num) as sum_annual_car,count(car_id) as sum_annual,
                        sum(CASE WHEN should_inspection_be_conducted_this_month like '是%' THEN 1 ELSE 0 END) as sum_should_inspection,
                        sum(CASE WHEN annual_inspection_this_month like '否%' THEN 1 ELSE 0 END) as sum_annual_inspection,
                        sum(CASE WHEN passed_inspection like '是%' AND statistical_month>cast(inspect_annually_date as int4) THEN 1 ELSE 0 END) as sum_passed_inspection";
                $sum_data = ['sum_annual_car'=>0,'sum_annual'=>0,'sum_should_inspection'=>0,'sum_annual_inspection'=>0,'sum_passed_inspection'=>0];

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

                        $total = $sum_data;
                        $total['sum_car'] = 0;
                        $total_hash = md5('全省');
                        $res = $_query->select("city,$sum_field")->where($_where)->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;
                        $res = $_query->select("dpt_sec as city,$sum_field")->where($_where)->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];
                            }
                            $rows[] = $v;
                            $chart1data[] = ['value' => $v['sum_annual_inspection'], 'name' => $v['city']];
                            if($params['getall']==1) {
                                $res = $one_query->select(['city'=>'dpt_sec','sum_car'=>'count("one"."id")'])->where($one_where)->andWhere(['city' => $v['city']])->orderBy(['sum_car' => SORT_DESC])->groupBy('dpt_sec')->asArray()->all();
                                foreach ($res as $_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');
                                    $rows[] = $_v;
                                }
                            }else{
                                $_v = [];
                                $_v['hash'] = '';
                                $_v['parent_hash'] = md5($v['city'].'_city');
                                $_v = array_merge($_v, $sum_data);
                                $rows[] = $_v;
                            }
                        }

                        $res = $one_query->select(['sum_car'=>'count("one"."id")'])->where($one_where)->groupBy('')->orderBy('')->asArray()->one();
                        $total['sum_car'] = $res['sum_car'];
                        $total['city'] = '全省';
                        $total['hash'] = $total_hash;
                        $total['parent_hash'] = md5('全国');
                        array_unshift($rows, $total);

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

                    $_where['city'] = $where['city'];

                    $parent_hash = md5($where['city'].'_city');
                    $total['hash'] = $parent_hash;
                    $total['parent_hash'] = md5('全省');
                    $total['sum_car'] = $one_query->where($one_where)->andWhere(['city'=>$where['city']])->groupBy('')->orderBy('')->count();
                    $res =  $_query->select("city,$sum_field")->where($_where)->groupBy('city')->asArray()->one();

                    $rows[] = array_merge($total,$res?:$sum_data,['city'=>$_where['city']]);

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

                    foreach ($citydata as $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'] = $parent_hash;
                        $rows[] = $v;
                        $chart1data[] = ['value' => $v['sum_annual_inspection'], 'name' => $v['city']];
                    }
                }


                $result = ["total" => null, "totalNotFiltered" => null, "rows" => $rows, 'needgetall'=> $needgetall, 'date'=>['year_info'=>substr($statistical_month,0,4),'month_info'=>substr($statistical_month,-2)]];

                $year = substr($statistical_month,0,4);

                for ($i=1;$i<12;$i++){
                    $month = $year*100+$i;
                    if($month>$_where['statistical_month']) break;
                    $xAxis[] = $month;
                    $where['statistical_month'] = $month;
                    unset($where['self_rent']);
                    $res = FUnAnnualInspectionleasedVehiclesDetails::find()->select('statistical_month,sum(CASE WHEN annual_inspection_this_month like \'否%\' THEN 1 ELSE 0 END) as sum_annual_inspection')->where($where)->groupBy('statistical_month')->orderBy(['statistical_month' => SORT_ASC])->asArray()->one();
                    if($res){
                        $series[] = $res['sum_annual_inspection'];
                    }else{
                        $series[] = 0;
                    }
                }


                $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->result = $result;
            $this->tableTitle = array(
                array('field' => 'city', 'title' => '地市', 'align' => 'left'),
                array('field' => 'sum_car', 'title' => '租赁车辆数量', 'align' => 'right', 'formatter'=>'numericFormatter'),
                array('field' => 'sum_should_inspection', 'title' => '应年检租赁车辆数量', 'align' => 'right', 'formatter'=>'numericFormatter'),
                array('field' => 'sum_annual_inspection', 'title' => '未年检租赁车辆数量', 'align' => 'right', 'formatter'=>'numericFormatter'),
                array('field' => 'sum_passed_inspection', 'title' => '过年检租赁车辆数量', 'align' => 'right', 'formatter'=>'numericFormatter'),
            );
            $this->tableConfig = array('table' => 'FUnAnnualInspectionleasedVehiclesDetails', 'url' => $this->createRealUrl(['car/safety/annually', 'type' => $type]), 'setFieldUrl' => $this->createRealUrl(['car/safety/setfield']), 'idField' => 'hash', 'checkbox' => 0, 'dropmenu' => 1, 'pagesize' => 20, 'refresh' => true, 'tree' => 1, 'parentIdField' => 'parent_hash', 'treeShowField' => 'city', 'treeColumn' => 0, 'expand' => false, 'exportFileName' => '未年检租赁车辆统计', 'height' => 500);
        }
        if ($type == 2) {
            if (Yii::$app->request->isAjax) {
                $query = FUnAnnualInspectionleasedVehiclesDetails::find();
                $params = Yii::$app->request->get();

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

                //账期
                if(!empty($params['statistical_month'])){
                    $where['statistical_month'] = str_replace('-','',$params['statistical_month']);
                }

                //车牌
                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);
                }


                $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->orderBy([$_GET['sort'] => ($_GET['sortOrder'] == 'asc' ? SORT_ASC : SORT_DESC)])->asArray()->all();
                } else {
                    $resultList = $query->orderBy(['car_id' => SORT_DESC])->asArray()->all();
                }

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

            $this->tableTitle = array(
                array('field' => 'car_id', 'title' => '序号', 'align' => 'center', 'sortable' => true),
                array('field' => 'statistical_month', 'title' => '账期', 'align' => 'center', 'sortable' => true),
                array('field' => 'card_num', 'title' => '车牌号', 'align' => 'center', 'sortable' => true),
                array('field' => 'enable_date', 'title' => '登记日期', 'align' => 'center', 'sortable' => true),
                array('field' => 'car_brand', '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' => 'passed_inspection', 'title' => '是否过检', 'align' => 'center', 'sortable' => true),
                array('field' => 'should_inspection_be_conducted_this_month', 'title' => '是否本月应检', 'align' => 'center', 'sortable' => true),
                array('field' => 'annual_inspection_this_month', 'title' => '本月是否年检', 'align' => 'center', 'sortable' => true),
                array('field' => 'inspect_annually_date', 'title' => '年检时间', 'align' => 'center', 'sortable' => true),
            );

            $this->tableConfig = array('table' => 'FUnAnnualInspectionleasedVehiclesDetails', 'url' => $this->createRealUrl(['car/safety/annually', 'type' => $type]), 'setFieldUrl' => $this->createRealUrl(['car/safety/setfield']), 'idField' => 'car_id', 'checkbox' => 0, 'dropmenu' => 1, 'pagination' => true, 'pagesize' => 20, 'refresh' => true, 'exportFileName' => '未年检租赁车辆详单', 'height' => 500);
        }
        $koujing = '存在交通违章，6个月以上未处理的车辆为异常。';
        return $this->render('annually', array('type' => $type, 'koujing' => $koujing));
    }
    //保养情况
    public function actionMaintain()
    {
        $type = Yii::$app->request->get('type', 1);
        if ($type == 1) {


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

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

                $where['self_rent'] = '租赁车辆';


                $_query = FSeverelyOverMaintainedLeasedVehiclesDetails::find();


            //账期
            if(!empty($params['statistical_month'])){
                $statistical_month = str_replace('-','',$params['statistical_month']);
            }else{
                $res = FSeverelyOverMaintainedLeasedVehiclesDetails::find()->orderBy(['statistical_month' => SORT_DESC])->asArray()->one();
                $statistical_month = $res['statistical_month'];
            }
            $_where['statistical_month'] = $statistical_month;

            $one_query = FOneCarOneTable::find()->alias('one')->leftJoin('{{%f_car_info}} as c', 'one.car_id = c.car_id');
            $one_where['year_info'] = substr($statistical_month,0,4);
            $one_where['month_info'] = intval(substr($statistical_month,-2));
            $res = FOneCarOneTable::find()->where($one_where)->asArray()->one();
            if(empty($res)){
                $res = FOneCarOneTable::find()->orderBy('year_info desc,month_info desc' )->asArray()->one();
                $one_where['year_info'] = intval($res['year_info']);
                $one_where['month_info'] = intval($res['month_info']);
            }
            $one_where['self_rent'] = '租赁车辆';

                $sum_field = "sum(CASE WHEN should_maintenance_be_carried_out like '是%' THEN 1 ELSE 0 END) as sum_should_maintenance, 
                        sum(CASE WHEN exceeding_the_recommended_maintenance_time > 0 OR exceeding_the_recommended_maintenance_mileage > 0 THEN 1 ELSE 0 END) as sum_exceeding,
                        sum(CASE WHEN exceeding_the_recommended_maintenance_time > 90 OR exceeding_the_recommended_maintenance_mileage > 3000 THEN 1 ELSE 0 END) as sum_severely_exceeding";
                $sum_data = ['sum_should_maintenance'=>0,'sum_exceeding'=>0,'sum_severely_exceeding'=>0];

                $chart1data = [];
                $rows = [];
                $needgetall = false;
                if(empty($where['city'])){
                    $needgetall = true;

                    $citydata = $one_query->select(['city','sum_car'=>'count("one"."id")'])->where($one_where)->groupBy('city')->orderBy($this->citySort)->asArray()->all();

                    $total = $sum_data;
                    $total['sum_car'] = 0;
                    $total_hash = md5('全省');
                    $res = $_query->select("city,$sum_field")->where($_where)->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;
                        $res = $_query->select("dpt_sec as city,$sum_field")->where($_where)->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];
                        }
                        $rows[] = $v;
                        $chart1data[] = ['value' => $v['sum_severely_exceeding'], 'name' => $v['city']];
                        if($params['getall']==1) {
                            $res = $one_query->select(['city'=>'dpt_sec','sum_car'=>'count("one"."id")'])->where($one_where)->andWhere(['city' => $v['city']])->orderBy(['sum_car' => SORT_DESC])->groupBy('dpt_sec')->asArray()->all();
                            foreach ($res as $_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');
                                $rows[] = $_v;
                            }
                        }else{
                            $_v = [];
                            $_v['hash'] = '';
                            $_v['parent_hash'] = md5($v['city'].'_city');
                            $_v = array_merge($_v, $sum_data);
                            $rows[] = $_v;
                        }
                    }
                    $res = $one_query->select(['sum_car'=>'count("one"."id")'])->where($one_where)->groupBy('')->orderBy('')->asArray()->one();
                    $total['sum_car'] = $res['sum_car'];
                    $total['city'] = '全省';
                    $total['hash'] = $total_hash;
                    $total['parent_hash'] = md5('全国');
                    array_unshift($rows,$total);
                }else{
                    $citydata = $one_query->select(['city'=>'dpt_sec','sum_car'=>'count("one"."id")'])->where($one_where)->andWhere(['city' => $where['city']])->orderBy(['sum_car' => SORT_DESC])->groupBy('dpt_sec')->asArray()->all();


                    $_where['city'] = $where['city'];

                    $parent_hash = md5($where['city'].'_city');
                    $total['hash'] = $parent_hash;
                    $total['parent_hash'] = md5('全省');
                    $total['sum_car'] = $one_query->where($one_where)->andWhere(['city'=>$where['city']])->groupBy('')->orderBy('')->count();
                    $res =  $_query->select("city,$sum_field")->where($_where)->groupBy('city')->asArray()->one();

                    $rows[] = array_merge($total,$res?:$sum_data,['city'=>$_where['city']]);

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

                    foreach ($citydata as $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'] = $parent_hash;
                        $rows[] = $v;
                        $chart1data[] = ['value' => $v['sum_severely_exceeding'], 'name' => $v['city']];
                    }
                }


            $result = ["total" => null, "totalNotFiltered" => null, "rows" => $rows, 'needgetall'=> $needgetall, 'date'=>['year_info'=>substr($_where['statistical_month'],0,4),'month_info'=>substr($_where['statistical_month'],-2)]];


            $year = substr($_where['statistical_month'],0,4);

                for ($i=1;$i<12;$i++){
                    $month = $year*100+$i;
                    if($month>$_where['statistical_month']) break;
                    $xAxis[] = $month;
                    $where['statistical_month'] = $month;
                    unset($where['self_rent']);
                    $res = FSeverelyOverMaintainedLeasedVehiclesDetails::find()->select('statistical_month,sum(CASE WHEN exceeding_the_recommended_maintenance_time > 90 OR exceeding_the_recommended_maintenance_mileage > 3000 THEN 1 ELSE 0 END) as sum_severely_exceeding')->where($where)->groupBy('statistical_month')->orderBy(['statistical_month' => SORT_ASC])->asArray()->one();
                    if($res){
                        $series[] = $res['sum_severely_exceeding'];
                    }else{
                        $series[] = 0;
                    }
                }


                $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->result = $result;
            $this->tableTitle = array(
                array('field' => 'city', 'title' => '地市', 'align' => 'left'),
                array('field' => 'sum_car', 'title' => '租赁车辆数量', 'align' => 'right', 'formatter'=>'numericFormatter'),
                array('field' => 'sum_should_maintenance', 'title' => '应保养租赁车辆数量', 'align' => 'right', 'formatter'=>'numericFormatter'),
                array('field' => 'sum_exceeding', 'title' => '超保养租赁车辆数量', 'align' => 'right', 'formatter'=>'numericFormatter'),
                array('field' => 'sum_severely_exceeding', 'title' => '严重超保养车辆数量', 'align' => 'right', 'formatter'=>'numericFormatter'),
            );
            $this->tableConfig = array('table' => 'FSeverelyOverMaintainedLeasedVehiclesDetails', 'url' => $this->createRealUrl(['car/safety/maintain', 'type' => $type]), 'setFieldUrl' => $this->createRealUrl(['car/safety/setfield']), 'idField' => 'hash', 'checkbox' => 0, 'dropmenu' => 1,  'pagesize' => 20, 'refresh' => true, 'tree' => 1, 'parentIdField' => 'parent_hash', 'treeShowField' => 'city', 'treeColumn' => 0, 'expand' => false, 'exportFileName' => '严重超保养租赁车辆统计', 'height' => 500);
        }
        if ($type == 2) {
            if (Yii::$app->request->isAjax) {
                $query = FSeverelyOverMaintainedLeasedVehiclesDetails::find();
                $params = Yii::$app->request->get();

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

                //账期
                if(!empty($params['statistical_month'])){
                    $where['statistical_month'] = str_replace('-','',$params['statistical_month']);
                }

                //车牌
                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',['>','exceeding_the_recommended_maintenance_time',90],['>','exceeding_the_recommended_maintenance_mileage',3000]]);

                $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->orderBy([$_GET['sort'] => ($_GET['sortOrder'] == 'asc' ? SORT_ASC : SORT_DESC)])->asArray()->all();
                } else {
                    $resultList = $query->orderBy(['statistical_month' => SORT_DESC])->asArray()->all();
                }

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

            $this->tableTitle = array(
                array('field' => 'car_id', 'title' => '序号', 'align' => 'center', 'sortable' => true),
                array('field' => 'statistical_month', 'title' => '账期', 'align' => 'center', 'sortable' => true),
                array('field' => 'card_num', 'title' => '车牌号', 'align' => 'center', 'sortable' => true),
                array('field' => 'car_brand', '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' => 'enable_date', 'title' => '登记日期', 'align' => 'center', 'sortable' => true),
                array('field' => 'arrival_time', 'title' => '进厂时间', 'align' => 'center', 'sortable' => true),
                array('field' => 'kilometers_entering_the_factory', 'title' => '进厂公里数', 'align' => 'right', 'sortable' => true, 'formatter'=>'numeric1Formatter'),
                array('field' => 'Mileage_driven_as_of_data_extraction_date', 'title' => '截止数据提取日行驶里程', 'align' => 'right', 'sortable' => true, 'formatter'=>'numeric1Formatter'),
                array('field' => 'should_maintenance_be_carried_out', 'title' => '是否应该保养', 'align' => 'center', 'sortable' => true),
                array('field' => 'exceeding_the_recommended_maintenance_time', 'title' => '超出建议保养时间（天）', 'align' => 'right', 'sortable' => true, 'formatter'=>'numericFormatter'),
                array('field' => 'exceeding_the_recommended_maintenance_mileage', 'title' => '超出建议保养公里数', 'align' => 'right', 'sortable' => true, 'formatter'=>'numeric1Formatter'),
            );

            $this->tableConfig = array('table' => 'FSeverelyOverMaintainedLeasedVehiclesDetails', 'url' => $this->createRealUrl(['car/safety/maintain', 'type' => $type]), 'setFieldUrl' => $this->createRealUrl(['car/safety/setfield']), 'idField' => 'car_id', 'checkbox' => 0, 'dropmenu' => 1, 'pagination' => true, 'pagesize' => 20, 'refresh' => true, 'exportFileName' => '低效车辆明细', 'height' => 500);
        }


        $koujing = '严重应保养未保车辆数量=超出建议保养时间90天租赁车辆数量 or 超出建议保养3000公里租赁车辆数量';
        return $this->render('maintain', array('type' => $type, 'koujing' => $koujing));
    }
}