hebei-chengben-car/commands/ValueController.php

<?php
namespace app\commands;
use app\models\Linkmenu;
use app\modules\car\models\FCarInfo;
use app\modules\car\models\FCarsDetailData;
use app\modules\car\models\FOneCarOneTable;
use yii\console\Controller;
use Yii;
class ValueController extends Controller
{
    public $carTypes;
    public $cityOptions;
    public $carUseTypes;
    public $carSourceTypes;
    public $carCity;
    public $carYears;
    public $province = '河北';//默认省份
    public $provinceOptions;
    public function init()
    {
        $this->cityOptions = ['石家庄','机动局','保定','唐山','廊坊','张家口','承德','沧州','秦皇岛','衡水','邢台','邯郸','雄安','省本部'];
        //北十省份
        $provinceResult = Yii::$app->db->createCommand("select distinct(province) from wz_f_ten_northern_provinces_stat")->queryAll();
        foreach($provinceResult as $uprovince)
        {
            $this->provinceOptions[] = $uprovince['province'];
        }

        $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 actionIndex()
    {
        $this->_doreport();
        $this->_dowave();
    }

    //车辆专题报告
    private  function _doreport()
    {
        $url = $this->createRealUrl('car/default/report');

        https_request($url);
    }

    //波动分析缓存
    private function _dowave()
    {

        $url = $this->createRealUrl('car/wave/index');
        for($i=1;$i<=9;$i++)
        {
            for($j=1;$j<=2;$j++)
            {
                $params[] = "type=$i";
                $params[] = "start_month=1";
                $params[] = "end_month=".intval(date('m'));
                $params[] = "datatype=".$j;
                $params[] = "year=".date('Y');
                $cacheurl = $url.'&'.join('&',$params);
                https_request($cacheurl);
            }
        }





    }
    //生成含入口脚本的URL地址
    public function createRealUrl($params)
    {
        $url = WEB_URL.'index.html' ;
        return  $url.'?r='.$params.'&docache=1';
    }

    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];
        }
        $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,ci.self_rent,ci.car_type,ci.using_tag';
        }

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

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

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

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

        if($all==0)
        {
            $count_sql = 'SELECT `city` FROM '.$table. ' GROUP BY ' . $groupby;

            $connection = Yii::$app->db;
            $count = count($connection->createCommand($count_sql)->queryAll());

            $sql = 'SELECT '. $field .' FROM '.$table. ' GROUP BY ' . $groupby;
        }
        else
        {
            $count_sql = 'SELECT `city` FROM '.$table;

            $connection = Yii::$app->db;
            $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();
        $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();
        }

    }

}