hebei-chengben-car/common/controllers/BController.php

<?php
namespace app\common\controllers;
use app\models\Linkmenu;
use app\modules\car\models\FCarInfo;
use app\modules\car\models\FCarsDetailData;
use app\modules\car\models\FOneCarOneTable;
use Yii;
use \app\modules\admin\models\Resource;
use \app\modules\admin\models\Adminlog;
use app\modules\admin\models\Lang;
/**
 * 后台基类
 */
class BController extends CController
{

    public $resourceBlock;
    public $currentResource;//当前资源
    public $tableConfig;//列表页配置
    public $tableTitle;//列表页表头
    public $topLeftTableMenu;
    public $topRightTableMenu;
    public $operTableMenu;
    public $admin_id;
    public $carTypes;
    public $colors;
    public $cityOptions;
    public $citySort;
    public $carUseTypes;
    public $carSourceTypes;
    public $carCity;
    public $carYears;
    public $role_id;
    public $province = '河北';//默认省份
    public $result = [];
    public function init()
    {
        parent::init();
        $this->resourceBlock = array('main'=>'主体');
        $this->admin_id = $this->getIdentityInfo('admin_id');
        $this->carTypes = ['皮卡车','微型面包车','越野车','轿车','其他'];
        $this->colors = ['#485ec4','#2ca67a','#cd9941','#cf5a5a','#ff0000','#ff9900','#8f9a79','#b3b3a4','#c89063','#50be34','#efded4'];
       /* $cityResult = FCarInfo::find()->select('distinct(city)')->orderBy(['city'=>SORT_ASC])->all();
        foreach($cityResult as $ucity)
        {
            $this->cityOptions[] = $ucity['city'];
        }*/
        $this->cityOptions = ['石家庄','唐山','秦皇岛','邯郸','邢台','保定','张家口','承德','廊坊','沧州','衡水','雄安','机动局','省公司本部'];
        //排序
        $this->citySort = ' CASE "city"';
        foreach ($this->cityOptions as $k=>$v){
            $this->citySort .= " WHEN '".$v."' THEN ".$k;
        }
        $this->citySort .= " ELSE ".($k+1)." END";

        $carUseTypesResult = FCarInfo::find()->select('distinct(using_tag)')->orderBy(['using_tag'=>SORT_ASC])->all();
        foreach($carUseTypesResult as $unit)
        {
            $this->carUseTypes[] = $unit['using_tag'];
        }
        $carSourceTypesResult = FCarInfo::find()->select('distinct(self_rent)')->orderBy(['self_rent'=>SORT_ASC])->all();
        foreach($carSourceTypesResult as $unit)
        {
            $this->carSourceTypes[] = $unit['self_rent'];
        }

    }

    public function behaviors()
    {

        parent::behaviors();
        $module =  $this->moduleName;
        $controller = $this->controllerName;
        $actionName = $this->actionName;
        //获取当前管理页面的菜单
        $this->currentResource = Resource::find()->where('module = :module and controller=:controller and action=:action', [':module' => $module,':controller' => $controller,':action' => $actionName])->one();
        $userInfo = $this->getIdentityInfo();
        if($this->currentResource&&!empty($userInfo['role_id']))
        {
            if($userInfo['role_id']==1)
            {
                $this->topLeftTableMenu = Resource::find()->where("parent_hash=:parent_hash and menu_type in(2,3,4,10) and disabled=0",[':parent_hash'=>$this->currentResource->hash])->orderBy(['list_order' => SORT_ASC])->all();
                $this->topRightTableMenu = Resource::find()->where("parent_hash=:parent_hash and menu_type in(5,6,7,8,9) and disabled=0",[':parent_hash'=>$this->currentResource->hash])->orderBy(['list_order' => SORT_ASC])->all();
                $this->operTableMenu = Resource::find()->where("parent_hash=:parent_hash and menu_type in(11,12,13,14,15,16,17) and disabled=0",[':parent_hash'=>$this->currentResource->hash])->orderBy(['list_order' => SORT_ASC])->all();
            }
            else
            {
                $this->topLeftTableMenu = Resource::find()->where("parent_hash=:parent_hash and menu_type in(2,3,4,10) and disabled=0 and hash in(select hash from {{%role_resource}} where role_id=".$userInfo['role_id'].")",[':parent_hash'=>$this->currentResource->hash])->orderBy(['list_order' => SORT_ASC])->all();
                $this->topRightTableMenu = Resource::find()->where("parent_hash=:parent_hash and menu_type in(5,6,7,8,9) and disabled=0 and hash in(select hash from {{%role_resource}} where role_id=".$userInfo['role_id'].")",[':parent_hash'=>$this->currentResource->hash])->orderBy(['list_order' => SORT_ASC])->all();
                $this->operTableMenu = Resource::find()->where("parent_hash=:parent_hash and menu_type in(11,12,13,14,15,16,17) and disabled=0 and hash in(select hash from {{%role_resource}} where role_id=".$userInfo['role_id'].")",[':parent_hash'=>$this->currentResource->hash])->orderBy(['list_order' => SORT_ASC])->all();
            }
        }
        //写后台操作日志
        if(!empty($this->admin_id))
        {
            $log = new Adminlog();
            $log->module = $this->moduleName;
            $log->controller = $this->controllerName;
            $log->action = $this->actionName;
            $log->query_string = $_SERVER["QUERY_STRING"];
            $log->admin_id = $this->admin_id;
            $log->ip = MYIP;
            $log->create_time = TIMESTAMP;
            $log->save();
        }
        //如果不是生成缓存，需要权限校验
        if($_GET['docache'])
        {
            return  [];
        }
        else
        {
            return [
                [
                    'class' => 'app\common\filters\BackEndAuthFilter',
                    'except' => ['login','logout','lockscreen','syndata']
                ],
                [
                    'class' => 'app\common\filters\BackEndResourceFilter',
                    'except' => ['login','logout','lockscreen','testfunction','fieldsetting','syndata']
                ],
            ];
        }



    }

    public function getMyResource()
    {

        $userInfo = $this->getIdentityInfo();
        //获取左侧菜单
        if($userInfo['role_id'])
        {
            if($userInfo['role_id']==1)
            {
                $resultList = Resource::find()->where('disabled = :disabled', [':disabled' => 0])->orderBy(['list_order' => SORT_ASC])->all();
            }
            else
            {
                $resultList = Resource::findBySql('SELECT * FROM {{%resource}} where hash in(select hash from {{%role_resource}} where role_id='.$userInfo['role_id'].' order by role_resource_id asc) order by list_order asc' )->all();

            }

        }

        $resourceList = [];
        if(is_array($resultList))foreach($resultList as $result){

            $resourceList[$result['hash']] = single_object_to_array($result);

        }

        foreach($resourceList as $k=>$resource)
        {
            $sonList = [];
            $menuSonList = [];
            foreach($resourceList as $temp)
            {
                if($temp['parent_hash']==$resource['hash'])
                {
                    $sonList[] = $temp;
                    if($temp['menu_type']==1)
                    {
                        $menuSonList[] = $temp;
                    }
                }
            }
            $resourceList[$k]['sonList'] = $sonList;
            $resourceList[$k]['menuSonList'] = $menuSonList;
        }
        return $resourceList;

    }

    //更新语言表
    public function updateLang($key,$value,$file)
    {
        $lang = Yii::$app->language;
        //更新语言表数据
        $model = Lang::find()->where("lang='".$lang."' and lang_key='".$key."' and lang_file='".$file."'")->one();
        if(!$model)$model = new Lang;
        $model->lang  = $lang;
        $model->lang_key = $key;
        $model->lang_value = $value;
        $model->lang_file = $file;
        $model->save();
    }

    //更新语言包文件
    public function updateMessageFile($file)
    {
        $resourceDir = BASE_PATH.'messages'.DIRECTORY_SEPARATOR.Yii::$app->language.DIRECTORY_SEPARATOR;
        $resourcefile = $resourceDir.$file.'.php';
        if(!dir_writeable($resourceDir)) exit('Please chmod '.$resourceDir.' to 0777 !');
        $resourceList = Lang::find()->where("lang='".Yii::$app->language."' and lang_file='".$file."'")->all();
        $array = array();
        if(is_array($resourceList))foreach($resourceList as $resource) {

            $array[$resource['lang_key']] = trim($resource['lang_value']);
        }
        $resourceStr ="<?php ".PHP_EOL.'return '. array2string($array).';'.PHP_EOL."?>";
        Yii::$app->params['lockEx'] ? file_put_contents($resourcefile, $resourceStr, LOCK_EX) : file_put_contents($resourcefile, $resourceStr);
    }

    //单元格修改
    public function actionSetfield()
    {

        $table = strtolower(safe_replace($_GET['table']));
        $fileldName = safe_replace($_GET['field']);
        $fieldValue = safe_replace($_GET['value']);
        $pk = safe_replace($_GET['pk']);
        $pkField = \app\common\models\EActiveRecord::getPrikey($table);
        if(empty($pkField)) $pkField = 'id';
        if(!empty($pk)&&!empty($fileldName))
        {
            $result  =  Yii::$app->db->createCommand("UPDATE {{%$table}} SET $fileldName='$fieldValue' WHERE $pkField='$pk'")->execute();
            if($result)
            {
                $msgdata = ['error' => 0,'msg' => '操作成功'];
            }
            else
            {

                $msgdata = ['error' => 1,'msg' => '操作失败'];
            }
        }
        else
        {

            $msgdata = ['error' => 1,'msg' => '操作失败'];
        }
        echo_json($msgdata);
    }

    public function getWhere($query, $params=[], $isnull = [])
    {
        $where = [];
        $tables = [];
        $tablefrom = $query->getTablesUsedInFrom();
        foreach ($tablefrom as $v){
            $tables[] = str_replace(array('{{%','}}'),"",$v);
        }
        foreach ((array)$query->join as $v){
            $tables[] = str_replace(array('{{%','}}'),"",$v[1]);
        }

        $yeartables = [
            'f_car_condition'=>'year',
            'f_cars_detail_data'=>'year',
            'f_deta_illegal_using_car'=>'year',
            'f_fluc_cost_hkm'=>'year',
            'f_fluc_fuel_hkm'=>'year',
            'f_fluc_illegal_using_car'=>'year',
            'f_fluc_inefficient_car'=>'year',
            'f_fuel_hkm'=>'year',
            'f_mileage_attend'=>'year',
            'f_one_car_cost'=>'year',
            'f_one_car_one_table'=>'year_info',
            'f_operating_cost_monitor'=>'year',
            'f_rent_car_cost'=>'year',
            'f_stats_cost_avg'=>'year',
            'f_stats_cost_hkm'=>'year',
            'f_stats_fuel_hkm'=>'year',
            'f_stats_illegal_using_car'=>'year',
            'f_stats_inefficient_car'=>'year',
            'f_t_car_status'=>'year',
            'f_t_car_status_fuel'=>'year',
            'f_t_one_car_avg'=>'year',
            'f_violating_order'=>'year',
            'f_year_car_cost'=>'year',
        ];

        $citytables = [
            'f_car_info'=>'city',
            'f_cars_detail_data'=>'city',
            'f_cost_abn_order'=>'city',
            'f_fluc_cost_hkm'=>'city',
            'f_fluc_fuel_hkm'=>'city',
            'f_fluc_illegal_using_car'=>'city',
            'f_fluc_inefficient_car'=>'city',
            'f_stats_cost_avg'=>'city',
            'f_stats_cost_hkm'=>'city',
            'f_stats_fuel_hkm'=>'city',
            'f_stats_illegal_using_car'=>'city',
            'f_stats_inefficient_car'=>'city',
            'f_t_car_status'=>'city',
            'f_t_one_car_avg'=>'city',
            'f_year_car_cost'=>'city',
        ];
        //序列化查询语句
        $_where = [];
        foreach ($tables as $v){
            if($this->carCity && in_array($v,array_keys($citytables))){
                $_where[$citytables[$v]] = $this->carCity;
            }
            if($this->carYears && in_array($v,array_keys($yeartables))){
                $_where[$yeartables[$v]] = $this->carYears;
            }
        }
        foreach ($_where as $k=>$v){
            $where[] =  $k . (is_array($v) ? ( count($v)>1 ? " in ('".implode("','",$v)."')" : " = '".current($v)."'") : " = '".$v."'");
        }



        //日期选择
        $yearField = 'year';
        $monthField = 'month';
        foreach ($tables as $v){
            if($v=='f_one_car_one_table') {
                $yearField = 'year_info';
                $monthField = 'month_info';
            }
        }
        if(!empty($params['start_date'])&&!empty($params['end_date']))
        {
            $startdateInfo = explode('-',$params['start_date']);
            $start_year =   $startdateInfo[0];
            $start_month = intval($startdateInfo[1]);
            $enddateInfo = explode('-',$params['end_date']);
            $end_year =    $enddateInfo[0];
            $end_month = intval($enddateInfo[1]);
            if(!empty($start_year)&&!empty($end_year))$where[] = " ($yearField>=$start_year and $yearField<=$end_year) ";
            if(!empty($start_month)&&!empty($end_month))$where[] = " ($monthField>=$start_month and $monthField<=$end_month) ";
        }
        if(!empty($params['date'])) {
            $dateInfo = explode('-', $params['date']);
            $year = $dateInfo[0];
            $month = intval($dateInfo[1]);
            if(!empty($year))$where[] = " $yearField = $year ";
            if(!empty($month))$where[] = " $monthField = $month ";
        }

        //地市选择
        if(!empty($params['city'])||!empty($params['city_1'])||!empty($params['city_2'])||!empty($params['city_3']))
        {
            if($params['city_3'])
            {
                $cityInfo = Linkmenu::getMenuNameRs($params['city_3']);
            }
            else if($params['city_2'])
            {
                $cityInfo = Linkmenu::getMenuNameRs($params['city_2']);
            }
            else if($params['city_1'])
            {
                $cityInfo = Linkmenu::getMenuNameRs($params['city_1']);
            }
            if(!empty($cityInfo[0]))$where[] = " city = '".$cityInfo[0]."' ";
            if(!empty($cityInfo[1])){
                $where[] = " dpt_sec = '".$cityInfo[1]."' ";
            }elseif(in_array('dpt_sec',$isnull)){
                $where[] = !empty($cityInfo[0]) ? " dpt_sec is not null " : " dpt_sec is null ";
            }
            if(!empty($cityInfo[2])){
                $where[] = " grid = '".$cityInfo[2]."' ";
            }elseif(in_array('grid',$isnull)){
                $where[] = !empty($cityInfo[1]) ? " grid is not null " : " grid is null ";
            }
        }

        if(!empty($params['card_num']))$where[] = " card_num = '".$params['card_num']."' ";

        if(!empty($params['car_type'])){
            $where[] = " car_type = '".$params['car_type']."' ";
        }elseif(in_array('car_type',$isnull)){
            $where[] = " car_type is null ";
        }

        if(!empty($params['using_tag'])){
            $where[] = " using_tag = '".$params['using_tag']."' ";
        }elseif(in_array('using_tag',$isnull)){
            $where[] = " using_tag is null ";
        }
        if(!empty($params['self_rent'])){
            $where[] = " self_rent = '".$params['self_rent']."' ";
        }elseif(in_array('self_rent',$isnull)){
            $where[] = " self_rent is null ";
        }
        if(!empty($params['car_src'])){
            $where[] = " car_src = '".$params['car_src']."' ";
        }elseif(in_array('car_src',$isnull)){
            $where[] = " car_src is null ";
        }
        return $where;
    }


    /*
     * 取得一车一表SQL语句或数据
     */
    public function getOneCarOneTable($params=[],$field = '*',$setYears=true,$ressql = true)
    {
        $params = $params ? $params : Yii::$app->request->get();

        $tableschema = FOneCarOneTable::getTableSchema();
        $columns = array_keys($tableschema->columns);
        $query = FOneCarOneTable::find();
        $where = [];

        //限定年份数据
        if($setYears && $this->carYears && $this->role_id>1 && in_array('year_info',$columns)){
            $where[] =  'year_info' . (is_array($this->carYears) ? ( count($this->carYears)>1 ? " in ('".implode("','",$this->carYears)."')" : " = '".current($this->carYears)."'") : " = '".$this->carYears."'");
        }
        //没有设定年份，取当年
        if(!empty($params['start_date'])&&!empty($params['end_date']))
        {
            $startdateInfo = explode('-',$params['start_date']);
            $start_year =   $startdateInfo[0];
            $start_month = intval($startdateInfo[1]);
            $enddateInfo = explode('-',$params['end_date']);
            $end_year =    $enddateInfo[0];
            $end_month = intval($enddateInfo[1]);
            if(!empty($start_year)&&!empty($end_year))$where[] = " (year_info>=$start_year and year_info<=$end_year) ";
            if(!empty($start_month)&&!empty($end_month))$where[] = " (month_info>=$start_month and month_info<=$end_month) ";
        }

        if(!empty($params['date'])) {
            $dateInfo = explode('-', $params['date']);
            $year = $dateInfo[0];
            $month = intval($dateInfo[1]);
            if(!empty($year))$where[] = " year_info = $year ";
            if(!empty($month))$where[] = " month_info = $month ";
        }

        foreach ($params as $k=>$v){
            if(in_array($k,$columns) && trim($v)!==''&&$k!='city'){
                if ($tableschema->columns[$k]->phpType=='string'){
                    $where[] = $k . " = '".$v."'";
                }else{
                    $where[] = $k . " = ".$v;
                }
            }
        }



        if(!empty($where)){
            $sql = join(" and ",$where);
            $query->where($sql);
        }
        $query->select($field);
        if($ressql){
            return $query->createCommand()->getRawSql();
        }else{
            return $query->asArray()->all();
        }

    }

    /*
    * 取得车辆基础表SQL或数据
    */
    public function getCarInfo($params=[],$field = '*',$setCity=true,$ressql = true)
    {
        $params = $params ? $params : Yii::$app->request->get();
        $tableschema = FCarInfo::getTableSchema();
        $columns = array_keys($tableschema->columns);
        $query = FCarInfo::find();
        $where = [];
        if($setCity && $this->carCity && $this->role_id>1 && in_array('city',$columns)){
            $where[] =  'city' . (is_array($this->carCity) ? ( count($this->carCity)>1 ? " in ('".implode("','",$this->carCity)."')" : " = '".current($this->carCity)."'") : " = '".$this->carCity."'");
        }
        foreach ($params as $k=>$v){
            if(in_array($k,$columns) && trim($v)!==''&&$k!='city'){
                if($k=='card_num'){
                    $where[] = $k . " LIKE '%".$v."%'";
                }elseif ($tableschema->columns[$k]->phpType=='string'){
                    $where[] = $k . " = '".$v."'";
                }else{
                    $where[] = $k . " = ".$v;
                }
            }
        }
        //地市选择
        if(!empty($params['city'])||!empty($params['city_1'])||!empty($params['city_2'])||!empty($params['city_3']))
        {
            if($params['city_3'])
            {
                $cityInfo = Linkmenu::getMenuNameRs($params['city_3']);
            }
            else if($params['city_2'])
            {
                $cityInfo = Linkmenu::getMenuNameRs($params['city_2']);
            }
            else if($params['city_1'])
            {
                $cityInfo = Linkmenu::getMenuNameRs($params['city_1']);
            }
            if(!empty($cityInfo[0]))$where[] = " city = '".$cityInfo[0]."' ";
            if(!empty($cityInfo[1])) $where[] = " dpt_sec = '".$cityInfo[1]."' ";
            if(!empty($cityInfo[2]))$where[] = " grid = '".$cityInfo[2]."' ";
        }
        //附加的地市条件
        if(!empty($params['city_name_1'])||!empty($params['city_name_2'])||!empty($params['city_name_3']))
        {
            if(!empty($params['city_name_1']))$where[] = " city = '".$params['city_name_1']."' ";
            if(!empty($params['city_name_2'])) $where[] = " dpt_sec = '".$params['city_name_2']."' ";
            if(!empty($params['city_name_3']))$where[] = " grid = '".$params['city_name_3']."' ";
        }

        if(!empty($where)){
            $sql = join(" and ",$where);
            $query->where($sql);
        }
        $query->select($field);
        if($ressql){
            return $query->createCommand()->getRawSql();
        }else{
            return $query->asArray()->all();;
        }

    }
    /*
    * 拼接评价报表SQL及取得分页数据
    */
    public function getPingjiaData($table,$field='*',$params=[],$all=0)
    {
        $params = $params ? $params : Yii::$app->request->get();

        if(!empty($params['city_1'])||!empty($params['city_2'])||!empty($params['city_3']))
        {
            if($params['city_3'])
            {
                $cityInfo = Linkmenu::getMenuNameRs($params['city_3']);
            }
            else if($params['city_2'])
            {
                $cityInfo = Linkmenu::getMenuNameRs($params['city_2']);
            }
            else if($params['city_1'])
            {
                $cityInfo = Linkmenu::getMenuNameRs($params['city_1']);
            }
            $params['city'] = $cityInfo[0];
            $params['dpt_sec'] = $cityInfo[1];
            $params['grid'] = $cityInfo[2];
        }
        if(empty($params['city']))unset($params['city']);

      
        $groupby[] = 'ci.city';

        if(!empty($params['city'])){
            $groupby[] = 'ci.dpt_sec';
            $field .= ',ci.dpt_sec';
        }

        if(!empty($params['dpt_sec'])){
            $groupby[] = 'ci.grid';
            $field .= ',ci.grid';
        }

        if(!empty($params['card_num'])){
            $groupby[] = 'ci.card_num';
            $field .= ',ci.card_num';
        }
        // && stripos($table,'card_num')!==false
        if(!empty($params['grid'])){
           // $groupby[] = 'ci.card_num';
            //$field .= ',ci.card_num,ci.self_rent,ci.car_type,ci.using_tag';
            $field .= ',ci.grid';
        }

        if(!empty($params['car_type'])){
            $groupby[] = 'ci.car_type';
            $field .= ',ci.car_type';
        }

        if(!empty($params['using_tag'])){
            $groupby[] = 'ci.using_tag';
            $field .= ',ci.using_tag';
        }

        if(!empty($params['self_rent'])){
            $groupby[] = 'ci.self_rent';
            $field .= ',ci.self_rent';
        }
      
        if($all==0)
        {

            $groupby = join(",",$groupby);

            $connection = Yii::$app->db;
            //$count_sql = 'SELECT ci.city as city FROM '.$table. ' GROUP BY ' . $groupby;
            //$count = count($connection->createCommand($count_sql)->queryAll());

            $sql = 'SELECT '. $field .' FROM '.$table. ' GROUP BY ' . $groupby;
             
            
        }
        else
        {
            
            
            $connection = Yii::$app->db;
            //$count_sql = 'SELECT ci.city as city FROM '.$table. ' GROUP BY ' . $groupby;
            //$count = count($connection->createCommand($count_sql)->queryAll());

            $sql = 'SELECT '. $field .' FROM '.$table;
          
            
        }
        //没有设定年份，取当年
        if(!empty($params['start_date'])&&!empty($params['end_date']))
        {
            $startdateInfo = explode('-',$params['start_date']);
            $start_year =   $startdateInfo[0];
            $start_month = intval($startdateInfo[1]);
            $enddateInfo = explode('-',$params['end_date']);
            $end_year =    $enddateInfo[0];
            $end_month = intval($enddateInfo[1]);
            $year = !empty($end_year) ? $end_year : date('Y');
        }
        else
        {
            $year = !empty($params['year']) ? $params['year'] : date('Y');
            $start_month = intval($params['start_month'])?intval($params['start_month']):1;
            $end_month = intval($params['end_month'])?intval($params['end_month']):12;
        }




        $sql = str_replace(['年份','起始月份','终止月份'],[$year,$start_month,$end_month],$sql);

        //排序
        if(isset($params['sort']))
        {
            $sql .= ' ORDER BY ' .$params['sort'].' '.(isset($params['sortOrder']) && $params['sortOrder']=='asc'?'ASC':'DESC');
        }

        //分页
        if(isset($params['limit'])){
            $sql .= ' LIMIT ';
            if(isset($params['offset'])){
                $sql .= $params['offset']. ',';
            }
            $sql .= $params['limit'];
        }

        $data = $connection->createCommand($sql)->queryAll();
        if(!empty($data[0]['city']))
        {
            $data = $this->arrayByArraySort($data,$this->cityOptions,'city');
        }



        return ["total"=>$count,"totalNotFiltered"=>$count,"rows"=>$data];

    }

    function arrayByArraySort($data,$sort,$column=null){
        $temp = [];
        if (!is_null($column)){
            foreach ($data as $item){
                $temp[$item[$column]][] = $item;
            }
        }else{
            foreach ($data as $key => $item){
                $temp[$key] = [$item];
            }
        }

        $ret = [];
        foreach ($sort as $sortum){
            if (isset($temp[$sortum])){
                $ret = array_merge($ret,$temp[$sortum]);
            }
        }
        $ret = array_values($ret);
        return $ret;

    }

    /*
    * 取得车辆详情表SQL或数据
    */
    public function getCarsDetailData($params=[],$field = '*',$setYears=true,$ressql = true)
    {
        $params = $params ? $params : Yii::$app->request->get();
        if(empty($params['city']))unset($params['city']);
        $tableschema = FCarsDetailData::getTableSchema();
        $columns = array_keys($tableschema->columns);
        $query = FCarsDetailData::find();
        $where = [];
        if($setYears && $this->carYears && $this->role_id>1 && in_array('year',$columns)){
            $where[] =  'year' . (is_array($this->carYears) ? ( count($this->carYears)>1 ? " in ('".implode("','",$this->carYears)."')" : " = '".current($this->carYears)."'") : " = '".$this->carYears."'");
        }
        foreach ($params as $k=>$v){
            if(in_array($k,$columns) && trim($v)!==''){
                if ($tableschema->columns[$k]->phpType=='string'){
                    $where[] = $k . " = '".$v."'";
                }else{
                    $where[] = $k . " = ".$v;
                }
            }
        }

        //没有设定年份，取当年
        if(!empty($params['start_date'])&&!empty($params['end_date']))
        {
            $startdateInfo = explode('-',$params['start_date']);
            $start_year =   $startdateInfo[0];
            $start_month = intval($startdateInfo[1]);
            $enddateInfo = explode('-',$params['end_date']);
            $end_year =    $enddateInfo[0];
            $end_month = intval($enddateInfo[1]);
            if(!empty($start_year)&&!empty($end_year))$where[] = " (year>=$start_year and year<=$end_year) ";
            if(!empty($start_month)&&!empty($end_month))$where[] = " (month>=$start_month and month<=$end_month) ";
        }

        if(!empty($params['date'])) {
            $dateInfo = explode('-', $params['date']);
            $year = $dateInfo[0];
            $month = intval($dateInfo[1]);
            if(!empty($year))$where[] = " year = $year ";
            if(!empty($month))$where[] = " month = $month ";
        }

        if(!empty($where)){
            $sql = join(" and ",$where);
            $query->where($sql);
        }
        $query->select($field);
        if($ressql){
            return $query->createCommand()->getRawSql();
        }else{
            return $query->asArray()->all();
        }

    }

    /*
    *取得地市条件
    */
    public function getCityWhere($params)
    {
        $where = [];
        //地市选择
        if(!empty($params['city'])||!empty($params['city_1'])||!empty($params['city_2'])||!empty($params['city_3']))
        {
            if($params['city_3'])
            {
                $cityInfo = Linkmenu::getMenuNameRs($params['city_3']);
            }
            else if($params['city_2'])
            {
                $cityInfo = Linkmenu::getMenuNameRs($params['city_2']);
            }
            else if($params['city_1'])
            {
                $cityInfo = Linkmenu::getMenuNameRs($params['city_1']);
            }
            //unset($params['city'],$params['city_1'],$params['city_2'],$params['city_3']);
            if(!empty($cityInfo[0])){
                $where['city'] = $cityInfo[0];
            }
            if(!empty($cityInfo[1])){
                $where['dpt_sec'] = $cityInfo[1];
            }
            if(!empty($cityInfo[2])){
                $where['grid'] = $cityInfo[2];
            }
        }

        return $where;
    }
    /*
    *pie图表设置
    */
    public function getChart1Option($option)
    {
        $def = [
            'title' => [
                'text' => '',
                'textStyle' => [
                    'fontSize' => 14,
                ],
                'subtext' => '',
                'left' => 'left'
            ],
            'tooltip' => [
                'trigger' => 'item',
                'formatter' => '{a} <br/>{b} : {c} ({d}%)'
            ],
            'legend' => [
                'left' => 'center',
                'top' => 'bottom',
                'data' => []
            ],
            'toolbox' => [
                'show' => true,
                'feature' => [
                    'mark' => ['show' => true],
                    'dataView' => ['show' => true, 'readOnly' => false],
                    'restore' => ['show' => false],
                    'saveAsImage' => ['show' => true]
                ]
            ],
            'series' => [
                [
                    'name' => '',
                    'type' => 'pie',
                    'radius' => [20, 130],
                    'center' => ['45%', '50%'],
                    'roseType' => 'area',
                    'itemStyle' => [
                        'borderRadius' => 5
                    ],
                    'data' => [

                    ]
                ]
            ]
        ];

        return array_replace_recursive($def,$option);
    }

    /*
    *bar图表设置
    */
    public function getChart2Option($option)
    {
        $def = [
            'title' => [
                'text' => '',
                'textStyle' => [
                    'fontSize' => 14,
                ],
                'subtext' => '',
                'left' => 'left'
            ],
            'tooltip' => [
                'trigger' => 'axis',
                'axisPointer' => [
                    'type' => 'shadow'
                ]
            ],
            'grid' => [
                'left' => '3%',
                'right' => '7%',
                'bottom' => '3%',
                'containLabel' => true,
            ],
            'toolbox' => [
                'show' => true,
                'feature' => [
                    'dataZoom' => ['yAxisIndex' => 'none'],
                    'dataView' => ['show' => true, 'readOnly' => false],
                    'magicType' => ['type' => ['line', 'bar']],
                    'restore' => ['show' => false],
                    'saveAsImage' => ['show' => true]
                ]
            ],
            'xAxis' => [
                [
                    'type' => 'category',
                    'data' => [],
                    'axisTick' => [
                        'alignWithLabel' => true
                    ],
                    'axisLabel' => [
                        'interval' => 0
                    ]
                ]
            ],
            'yAxis' => [
                [
                    'type' => 'value'
                ]
            ],
            'series' => [
                [
                    'name' => '',
                    'type' => 'bar',
                    'barWidth' => '60%',
                    'data' => [],
                    'markLine' => [
                        'data' => [
                            ['type' => 'average', 'name' => '平均值']
                        ]
                    ]
                ]
            ]
        ];

        return array_replace_recursive($def,$option);
    }


}