<?php
namespace app\modules\car\controllers;
use app\common\controllers\BController;
use app\modules\car\models\FDetaIllegalUsingCar;
use app\modules\car\models\FOneCarOneTable;
use Yii;
class LegalController extends BController
{
    public $layout = 'main';
    public function actionCars()
    {
        $type = Yii::$app->request->get('type',1);
        if($type==1)
        {
            if (Yii::$app->request->isAjax) {
                $params = Yii::$app->request->get();
                if(empty($params['start_date'])) $params['start_date'] = get_date(TIMESTAMP,'Y-1');
                if(empty($params['end_date'])) $params['end_date'] = get_date(TIMESTAMP,'Y-m');
                //日期计算
                $startdateInfo = explode('-',$params['start_date']);
                $year =   $startdateInfo[0];
                $startmonth = intval($startdateInfo[1]);
                $enddateInfo = explode('-',$params['end_date']);
                $endmonth = intval($enddateInfo[1]);


                $sql_carsdetail = $this->getCarsDetailData($params,'car_id,illegal_times');
                $sql_carinfo = $this->getCarinfo(array_merge($params,['is_special_car'=>0]),'car_id, city, dpt_sec, grid, self_rent, car_type, using_tag');
                $table = '(' .$sql_carsdetail. ') cd  JOIN (' .$sql_carinfo. ')  ci ON ( cd.car_id = ci.car_id ) ';
                $field = 'ci.city,
            sum(cd.illegal_times) AS times,
            '.$year.'  AS year,
            (SELECT (case when max(month_info)>= '.$endmonth.'  then '.$endmonth.'  else max(month_info) end) from wz_f_one_car_one_table WHERE year_info = '.$year.' ) AS month';
                $data = $this->getPingjiaData($table,$field,$params);

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

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

                        //获取二级单位数据
                        $params1 = $params;
                        $params1['city_name_1']= $u['city'];
                        $params1['city']= $u['city'];
                        $sql_carsdetail = $this->getCarsDetailData($params1,'car_id,illegal_times');
                        $sql_carinfo = $this->getCarinfo(array_merge($params1,['is_special_car'=>0]),'car_id, city, dpt_sec, grid, self_rent, car_type, using_tag');
                        $table = '(' .$sql_carsdetail. ') cd  JOIN (' .$sql_carinfo. ')  ci ON ( cd.car_id = ci.car_id ) ';
                        $tempData = $this->getPingjiaData($table,str_replace('ci.city,','',$field),$params1);

                        if($tempData!='')
                        {
                            foreach($tempData['rows'] as $k1=>$u1) {
                                $tempData['rows'][$k1]['hash'] = '';
                                $tempData['rows'][$k1]['parent_hash'] = md5($u['city']);
                                $tempData['rows'][$k1]['city'] = '';
                            }
                            $data['rows'] = array_merge($data['rows'],$tempData['rows']);
                        }

                    }
                    //$data['rows'] = array_merge($dataAll['rows'],$data['rows']);
                }
                else
                {
                    foreach($data['rows'] as $k=>$u) {
                        $data['rows'][$k]['hash'] = md5($u['city']);
                        $data['rows'][$k]['parent_hash'] = '';
                    }
                }
                if(!empty($dataAll['rows']))$data['rows'] = array_merge($dataAll['rows'],$data['rows']);
                echo_json($data);
            }

            $this->tableTitle = array(
                array('field'=>'city','title'=>'地市','align'=>'center','class'=>'col-md-2'),
                array('field'=>'dpt_sec','title'=>'二级单位','align'=>'center','class'=>'col-md-2'),
                array('field'=>'grid','title'=>'网格','align'=>'center','class'=>'col-md-2'),
                array('field'=>'times','title'=>'次数','align'=>'center','class'=>'col-md-2','sortable'=>true),
                array('field'=>'year','title'=>'年份','align'=>'center','class'=>'col-md-2','sortable'=>true),
                array('field'=>'month','title'=>'截止月份','align'=>'center','class'=>'col-md-2','sortable'=>true),

            );
            $this->tableConfig = array('table'=>'FStatsIllegalUsingCar','url'=>$this->createRealUrl(['car/legal/cars','type'=>$type]),'setFieldUrl'=>$this->createRealUrl(['car/legal/setfield']),'idField'=>'hash','checkbox'=>0,'dropmenu'=>1,'pagination'=>true,'pagesize'=>20,'refresh'=>true,'tree'=>1,'parentIdField'=>'parent_hash','treeShowField'=>'city','treeColumn'=>0,'expand'=>false,'exportFileName'=>'违规用车统计','height'=>500);

        }

        if($type==2)
        {
            $query = FDetaIllegalUsingCar::find()->alias('weigui')->leftJoin('{{%f_car_info}}', 'weigui.car_id = {{%f_car_info}}.car_id');
            if (Yii::$app->request->isAjax) {
                $data = [];
                $params = Yii::$app->request->get();
                if(empty($params['start_date'])) $params['start_date'] = get_date(TIMESTAMP,'Y-1');
                if(empty($params['end_date'])) $params['end_date'] = get_date(TIMESTAMP,'Y-12');
                $where = $this->getWhere($query, $params);
                if(!empty($where)){
                    $sql = join(" and ",$where);
                    $query->where($sql);
                }

                $countQuery = clone $query;
                if(!empty($where)){
                    $sql = join(" and ",$where);
                    $countQuery->where($sql);
                }
                //分页
                if(isset($_GET['limit'])){ $query->limit(intval($_GET['limit']));}
                if(isset($_GET['offset'])){ $query->offset(intval($_GET['offset']));}
                //排序
                if(isset($_GET['sort'])&&isset($_GET['sortOrder']))
                {
                    $resultList = $query->select('*')->orderBy([$_GET['sort']=>($_GET['sortOrder']=='asc'?SORT_ASC:SORT_DESC)])->asArray()->all();
                }
                else
                {
                    $resultList = $query->select('*')->orderBy(['weigui.id'=>SORT_DESC])->asArray()->all();
                }

                foreach($resultList as $result)
                {
                    $data[] = array('card_num'=>$result['card_num'],'city'=>$result['city'],'dpt_sec'=>$result['dpt_sec'],'grid'=>$result['grid'],'car_type'=>$result['car_type'],'using_tag'=>$result['using_tag'],'alarm_area_des'=>$result['alarm_area_des'],'alarm_rule_des'=>$result['alarm_rule_des'],'cross_border_t'=>$result['cross_border_t'],'dest'=>$result['dest'],'reason'=>$result['reason'],'year'=>$result['year'],'month'=>$result['month']);
                }
                $result = ["total"=>$countQuery->count(),"totalNotFiltered"=>$countQuery->count(),"rows"=>$data];
                echo_json($result);
            }

            $this->tableTitle = array(
                array('field'=>'card_num','title'=>'车牌号','align'=>'center'),
                array('field'=>'city','title'=>'地市','align'=>'center'),
                array('field'=>'dpt_sec','title'=>'二级单位','align'=>'center'),
                array('field'=>'grid','title'=>'网格','align'=>'center'),
                array('field'=>'car_type','title'=>'车辆类型','align'=>'center'),
                array('field'=>'using_tag','title'=>'车辆使用性质','align'=>'center'),
                array('field'=>'alarm_area_des','title'=>'报警区域描述','align'=>'center'),
                array('field'=>'alarm_rule_des','title'=>'报警规则描述','align'=>'center'),
                array('field'=>'cross_border_t','title'=>'越界时间','align'=>'center','sortable'=>true),
                array('field'=>'dest','title'=>'目的地','align'=>'center'),
                array('field'=>'reason','title'=>'出车事由','align'=>'center'),
                array('field'=>'year','title'=>'年份','align'=>'center'),
                array('field'=>'month','title'=>'月份','align'=>'center'),
            );

            $this->tableConfig = array('table'=>FDetaIllegalUsingCar::shortTableName(),'url'=>$this->createRealUrl(['car/legal/cars','type'=>$type]),'setFieldUrl'=>$this->createRealUrl(['car/legal/setfield']),'idField'=>FDetaIllegalUsingCar::modelPrimaryKey(),'checkbox'=>0,'dropmenu'=>1,'pagination'=>true,'pagesize'=>20,'refresh'=>true,'exportFileName'=>'违规用车明细','height'=>500);
        }

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


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

                $sql_oncaronetable = $this->getOneCarOneTable($params,'car_id, repair_costs, insurance_costs, year_check_costs');
                $sql_carinfo = $this->getCarinfo(array_merge($params,['is_special_car'=>0, 'self_rent'=>'租赁车辆']),'car_id, card_num, city, dpt_sec, grid, self_rent, car_type, using_tag');
                $table = '(' .$sql_oncaronetable. ') ocot  JOIN (' .$sql_carinfo. ')  ci ON ( ocot.car_id = ci.car_id ) ';
                $field = 'ci.city,
            cast(sum(ocot.repair_costs) as decimal(10,2)) AS repair_costs,
            cast(sum(ocot.insurance_costs) as decimal(10,2)) AS insurance_costs,
            cast(sum(ocot.year_check_costs) as decimal(10,2)) AS year_check_costs,
            '.$year.' AS year,
            (SELECT (case when max(month_info)>= '.$endmonth.' then '.$endmonth.' else max(month_info) end) from wz_f_one_car_one_table WHERE year_info = '.$year.') AS month';
                $data = $this->getPingjiaData($table,$field,$params);
                //图标数据
                unset($params['city'],$params['dpt_sec'],$params['grid']);
                $sql_carinfo = $this->getCarinfo(['is_special_car'=>0, 'self_rent'=>'租赁车辆'],'car_id',false);
                $sql = 'SELECT  
                        cast(sum(ocot.repair_costs) as decimal(10,2)) AS repair_costs ,
                        cast(sum(ocot.insurance_costs) as decimal(10,2)) AS insurance_costs ,
                        cast(sum(ocot.year_check_costs) as decimal(10,2)) AS year_check_costs
                    FROM (' .$sql_oncaronetable. ') ocot  JOIN (' .$sql_carinfo. ')  ci ON ( ocot.car_id = ci.car_id ) ';

                $data['count'] = Yii::$app->db->createCommand($sql)->queryOne();

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

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

                        //获取二级单位数据
                        $params1 = $params;
                        $params1['city_name_1']= $u['city'];
                        $params1['city']= $u['city'];
                        $sql_oncaronetable = $this->getOneCarOneTable($params1,'car_id, repair_costs, insurance_costs, year_check_costs');
                        $sql_carinfo = $this->getCarinfo(array_merge($params1,['is_special_car'=>0, 'self_rent'=>'租赁车辆']),'car_id, card_num, city, dpt_sec, grid, self_rent, car_type, using_tag');
                        $table = '(' .$sql_oncaronetable. ') ocot  JOIN (' .$sql_carinfo. ')  ci ON ( ocot.car_id = ci.car_id ) ';
                        $tempData = $this->getPingjiaData($table,str_replace('ci.city,','',$field),$params1);

                        if($tempData['rows']!='')
                        {
                            foreach($tempData['rows'] as $k1=>$u1) {
                                $tempData['rows'][$k1]['hash'] = '';
                                $tempData['rows'][$k1]['parent_hash'] = md5($u['city']);
                                $tempData['rows'][$k1]['city'] = '';
                            }
                            $data['rows'] = array_merge($data['rows'],$tempData['rows']);
                        }

                    }
                    //$data['rows'] = array_merge($dataAll['rows'],$data['rows']);
                }
                else
                {
                    foreach($data['rows'] as $k=>$u) {
                        $data['rows'][$k]['hash'] = md5($u['city']);
                        $data['rows'][$k]['parent_hash'] = '';
                    }
                }
                if(!empty($dataAll['rows']))$data['rows'] = array_merge($dataAll['rows'],$data['rows']);
                echo_json($data);
            }

            $this->tableTitle = array(
                array('field'=>'city','title'=>'地市','align'=>'center'),
                array('field'=>'dpt_sec','title'=>'二级单位','align'=>'center'),
                array('field'=>'grid','title'=>'网格','align'=>'center'),
                array('field'=>'car_type','title'=>'车辆类型','align'=>'center'),
                array('field'=>'using_tag','title'=>'车辆使用性质','align'=>'center'),
                array('field'=>'repair_costs','title'=>'维修费','align'=>'center','sortable'=>true,),
                array('field'=>'year','title'=>'年份','align'=>'center','sortable'=>true),
                array('field'=>'month','title'=>'截止月份','align'=>'center','sortable'=>true),
            );

            $this->tableConfig = array('table'=>'FRentCarCost','url'=>$this->createRealUrl(['car/legal/rent','type'=>$type]),'setFieldUrl'=>$this->createRealUrl(['car/legal/setfield']),'idField'=>'hash','checkbox'=>0,'dropmenu'=>1,'pagination'=>true,'pagesize'=>20,'refresh'=>true,'tree'=>1,'parentIdField'=>'parent_hash','treeShowField'=>'city','treeColumn'=>0,'expand'=>false,'exportFileName'=>'租赁车辆维修费统计','height'=>500);
        }
        if($type==2)
        {
            $query = FOneCarOneTable::find()->alias('onecar')->leftJoin('{{%f_car_info}}', 'onecar.car_id = {{%f_car_info}}.car_id');
            if (Yii::$app->request->isAjax) {
                $data = [];
                $params = Yii::$app->request->get();
                if(empty($params['start_date'])) $params['start_date'] = get_date(TIMESTAMP,'Y-1');
                if(empty($params['end_date'])) $params['end_date'] = get_date(TIMESTAMP,'Y-12');
                $where = $this->getWhere($query, $params);
                if(!empty($where)){
                    $sql = join(" and ",$where);
                    $query->where($sql);
                }
                $countQuery = clone $query;
                if(!empty($where)){
                    $sql = join(" and ",$where);
                    $countQuery->where($sql);
                }
                //分页
                if(isset($_GET['limit'])){ $query->limit(intval($_GET['limit']));}
                if(isset($_GET['offset'])){ $query->offset(intval($_GET['offset']));}
                //排序
                if(isset($_GET['sort'])&&isset($_GET['sortOrder']))
                {
                    $resultList = $query->select('*')->orderBy([$_GET['sort']=>($_GET['sortOrder']=='asc'?SORT_ASC:SORT_DESC)])->asArray()->all();
                }
                else
                {
                    $resultList = $query->select('*')->orderBy(['onecar.id'=>SORT_DESC])->asArray()->all();
                }

                foreach($resultList as $result)
                {

                    $data[] = array('card_num'=>$result['card_num'],'city'=>$result['city'],'dpt_sec'=>$result['dpt_sec'],'grid'=>$result['grid'],'car_type'=>$result['car_type'],'using_tag'=>$result['using_tag'],'oil_costs'=>round($result['oil_costs'],2),'insurance_costs'=>round($result['insurance_costs'],2),'repair_costs'=>round($result['repair_costs'],2),'road_bridge_costs'=>round($result['road_bridge_costs'],2),'year'=>$result['year_info'],'month'=>$result['month_info']);
                }
                $result = ["total"=>$countQuery->count(),"totalNotFiltered"=>$countQuery->count(),"rows"=>$data];
                echo_json($result);
            }
            $this->tableTitle = array(
                array('field'=>'card_num','title'=>'车牌号','align'=>'center'),
                array('field'=>'city','title'=>'地市','align'=>'center'),
                array('field'=>'dpt_sec','title'=>'二级单位','align'=>'center'),
                array('field'=>'grid','title'=>'网格','align'=>'center'),
                array('field'=>'car_type','title'=>'车辆类型','align'=>'center'),
                array('field'=>'using_tag','title'=>'车辆使用性质','align'=>'center'),
                array('field'=>'repair_costs','title'=>'维修费用','align'=>'center','sortable'=>true),
                array('field'=>'year','title'=>'年份','align'=>'center'),
                array('field'=>'month','title'=>'月份','align'=>'center'),
            );
            $this->tableConfig = array('table'=>FOneCarOneTable::shortTableName(),'url'=>$this->createRealUrl(['car/legal/rent','type'=>$type]),'setFieldUrl'=>$this->createRealUrl(['car/legal/setfield']),'idField'=>FOneCarOneTable::modelPrimaryKey(),'checkbox'=>0,'dropmenu'=>1,'pagination'=>true,'pagesize'=>20,'refresh'=>true,'exportFileName'=>'租赁车辆费用明细','height'=>500);

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


                $sql_oncaronetable = $this->getOneCarOneTable($params,'car_id, repair_costs, insurance_costs, year_check_costs');
                $sql_carinfo = $this->getCarinfo(array_merge($params,['is_special_car'=>0, 'self_rent'=>'租赁车辆']),'car_id, card_num, city, dpt_sec, grid, self_rent, car_type, using_tag');
                $table = '(' .$sql_oncaronetable. ') ocot  JOIN (' .$sql_carinfo. ')  ci ON ( ocot.car_id = ci.car_id ) ';
                $field = 'ci.city,
               cast(sum(ocot.repair_costs) as decimal(10,2)) AS repair_costs,
            cast(sum(ocot.insurance_costs) as decimal(10,2)) AS insurance_costs,
            cast(sum(ocot.year_check_costs) as decimal(10,2)) AS year_check_costs,
            
            '.$year.' AS year,
            (SELECT (case when max(month_info)>= '.$endmonth.' then '.$endmonth.' else max(month_info) end) from wz_f_one_car_one_table WHERE year_info = '.$year.') AS month';
                $data = $this->getPingjiaData($table,$field,$params);
              /*  //图标数据
                unset($params['city'],$params['dpt_sec'],$params['grid']);
                $sql_carinfo = $this->getCarinfo(['is_special_car'=>0, 'self_rent'=>'租赁车辆'],'car_id',false);
                $sql = 'SELECT  
                        round(sum(ocot.repair_costs),2) AS repair_costs ,
                        round(sum(ocot.insurance_costs),2) AS insurance_costs ,
                        round(sum(ocot.year_check_costs),2) AS year_check_costs
                    FROM (' .$sql_oncaronetable. ') ocot  JOIN (' .$sql_carinfo. ')  ci ON ( ocot.car_id = ci.car_id ) ';

                $data['count'] = Yii::$app->db->createCommand($sql)->queryOne();*/

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

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

                        //获取二级单位数据
                        $params1 = $params;
                        $params1['city_name_1']= $u['city'];
                        $params1['city']= $u['city'];
                        $sql_oncaronetable = $this->getOneCarOneTable($params1,'car_id, repair_costs, insurance_costs, year_check_costs');
                        $sql_carinfo = $this->getCarinfo(array_merge($params1,['is_special_car'=>0, 'self_rent'=>'租赁车辆']),'car_id, card_num, city, dpt_sec, grid, self_rent, car_type, using_tag');
                        $table = '(' .$sql_oncaronetable. ') ocot  JOIN (' .$sql_carinfo. ')  ci ON ( ocot.car_id = ci.car_id ) ';
                        $tempData = $this->getPingjiaData($table,str_replace('ci.city,','',$field),$params1);
                        if($tempData['rows']!='')
                        {
                            foreach($tempData['rows'] as $k1=>$u1) {
                                $tempData['rows'][$k1]['hash'] = '';
                                $tempData['rows'][$k1]['parent_hash'] = md5($u['city']);
                                $tempData['rows'][$k1]['city'] = '';
                            }
                            $data['rows'] = array_merge($data['rows'],$tempData['rows']);
                        }

                    }
                    //$data['rows'] = array_merge($dataAll['rows'],$data['rows']);
                }
                else
                {
                    foreach($data['rows'] as $k=>$u) {
                        $data['rows'][$k]['hash'] = md5($u['city']);
                        $data['rows'][$k]['parent_hash'] = '';
                    }
                }
                if(!empty($dataAll['rows']))$data['rows'] = array_merge($dataAll['rows'],$data['rows']);
                echo_json($data);
            }

            $this->tableTitle = array(
                array('field'=>'city','title'=>'地市','align'=>'center'),
                array('field'=>'dpt_sec','title'=>'二级单位','align'=>'center'),
                array('field'=>'grid','title'=>'网格','align'=>'center'),
                array('field'=>'car_type','title'=>'车辆类型','align'=>'center'),
                array('field'=>'using_tag','title'=>'车辆使用性质','align'=>'center'),
                array('field'=>'insurance_costs','title'=>'保险费','align'=>'center','sortable'=>true,),
                array('field'=>'year','title'=>'年份','align'=>'center','sortable'=>true),
                array('field'=>'month','title'=>'截止月份','align'=>'center','sortable'=>true),
            );

            $this->tableConfig = array('table'=>'FRentCarCost','url'=>$this->createRealUrl(['car/legal/rent','type'=>$type]),'setFieldUrl'=>$this->createRealUrl(['car/legal/setfield']),'idField'=>'hash','checkbox'=>0,'dropmenu'=>1,'pagination'=>true,'pagesize'=>20,'refresh'=>true,'tree'=>1,'parentIdField'=>'parent_hash','treeShowField'=>'city','treeColumn'=>0,'expand'=>false,'exportFileName'=>'租赁车辆保险费统计','height'=>500);
        }

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

                $sql_oncaronetable = $this->getOneCarOneTable($params,'car_id, repair_costs, insurance_costs, year_check_costs');
                $sql_carinfo = $this->getCarinfo(array_merge($params,['is_special_car'=>0, 'self_rent'=>'租赁车辆']),'car_id, card_num, city, dpt_sec, grid, self_rent, car_type, using_tag');
                $table = '(' .$sql_oncaronetable. ') ocot  JOIN (' .$sql_carinfo. ')  ci ON ( ocot.car_id = ci.car_id ) ';
                $field = 'ci.city,
            cast(sum(ocot.repair_costs) as decimal(10,2)) AS repair_costs,
            cast(sum(ocot.insurance_costs) as decimal(10,2)) AS insurance_costs,
            cast(sum(ocot.year_check_costs) as decimal(10,2)) AS year_check_costs,
            '.$year.' AS year,
            (SELECT (case when max(month_info)>= '.$endmonth.' then '.$endmonth.' else max(month_info) end) from wz_f_one_car_one_table WHERE year_info = '.$year.') AS month';
                $data = $this->getPingjiaData($table,$field,$params);
                //图标数据
                unset($params['city'],$params['dpt_sec'],$params['grid']);
                $sql_carinfo = $this->getCarinfo(['is_special_car'=>0, 'self_rent'=>'租赁车辆'],'car_id',false);
                $sql = 'SELECT  
                        cast(sum(ocot.repair_costs) as decimal(10,2)) AS repair_costs,
                        cast(sum(ocot.insurance_costs) as decimal(10,2)) AS insurance_costs,
                        cast(sum(ocot.year_check_costs) as decimal(10,2)) AS year_check_costs
                    FROM (' .$sql_oncaronetable. ') ocot  JOIN (' .$sql_carinfo. ')  ci ON ( ocot.car_id = ci.car_id ) ';

                $data['count'] = Yii::$app->db->createCommand($sql)->queryOne();

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

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

                        //获取二级单位数据
                        $params1 = $params;
                        $params1['city_name_1']= $u['city'];
                        $params1['city']= $u['city'];
                        $sql_oncaronetable = $this->getOneCarOneTable($params1,'car_id, repair_costs, insurance_costs, year_check_costs');
                        $sql_carinfo = $this->getCarinfo(array_merge($params1,['is_special_car'=>0, 'self_rent'=>'租赁车辆']),'car_id, card_num, city, dpt_sec, grid, self_rent, car_type, using_tag');
                        $table = '(' .$sql_oncaronetable. ') ocot  JOIN (' .$sql_carinfo. ')  ci ON ( ocot.car_id = ci.car_id ) ';
                        $tempData = $this->getPingjiaData($table,str_replace('ci.city,','',$field),$params1);
                        if($tempData['rows']!='')
                        {
                            foreach($tempData['rows'] as $k1=>$u1) {
                                $tempData['rows'][$k1]['hash'] = '';
                                $tempData['rows'][$k1]['parent_hash'] = md5($u['city']);
                                $tempData['rows'][$k1]['city'] = '';
                            }
                            $data['rows'] = array_merge($data['rows'],$tempData['rows']);
                        }

                    }
                    //$data['rows'] = array_merge($dataAll['rows'],$data['rows']);
                }
                else
                {
                    foreach($data['rows'] as $k=>$u) {
                        $data['rows'][$k]['hash'] = md5($u['city']);
                        $data['rows'][$k]['parent_hash'] = '';
                    }
                }
                if(!empty($dataAll['rows']))$data['rows'] = array_merge($dataAll['rows'],$data['rows']);
                echo_json($data);
            }

            $this->tableTitle = array(
                array('field'=>'city','title'=>'地市','align'=>'center'),
                array('field'=>'dpt_sec','title'=>'二级单位','align'=>'center'),
                array('field'=>'grid','title'=>'网格','align'=>'center'),
                array('field'=>'car_type','title'=>'车辆类型','align'=>'center'),
                array('field'=>'using_tag','title'=>'车辆使用性质','align'=>'center'),
                array('field'=>'year_check_costs','title'=>'年检费','align'=>'center','sortable'=>true,),
                array('field'=>'year','title'=>'年份','align'=>'center','sortable'=>true),
                array('field'=>'month','title'=>'截止月份','align'=>'center','sortable'=>true),
            );

            $this->tableConfig = array('table'=>'FRentCarCost','url'=>$this->createRealUrl(['car/legal/rent','type'=>$type]),'setFieldUrl'=>$this->createRealUrl(['car/legal/setfield']),'idField'=>'hash','checkbox'=>0,'dropmenu'=>1,'pagination'=>true,'pagesize'=>20,'refresh'=>true,'tree'=>1,'parentIdField'=>'parent_hash','treeShowField'=>'city','treeColumn'=>0,'expand'=>false,'exportFileName'=>'租赁车辆年检费统计','height'=>500);
        }

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

}