<?php
namespace app\modules\car\controllers;
use app\common\controllers\BController;
use app\models\Linkmenu;
use app\modules\car\models\FCarCondition;
use app\modules\car\models\FCarScrap;
use app\modules\car\models\FOneCarOneTable;
use app\modules\car\models\FCarInfo;
use Yii;
class DefaultController extends BController
{
    public $layout = 'main';
    public function actionIndex()
    {
        if (Yii::$app->request->isAjax) {
            $date = Yii::$app->request->get('date');
            $city = Yii::$app->request->get('city');
            $year = get_date(TIMESTAMP,'Y');
            $lastyear = $year-1;
            //获取城市
            if($city)
            {
                $cityInfo = Linkmenu::getMenuNameRs($city);
                $cityName = $cityInfo[0];
            }
            if(!empty($date))
            {
                $dateInfo = explode('-',$date);
                $year = $dateInfo[0];
                $month = intval($dateInfo[1]);
                $lastyear = $year-1;
                //车辆总数
                if(empty($cityName))
                {
                    $sql = "select count(*) from(SELECT a.*,b.city from (select * from wz_f_one_car_one_table where year_info=$year and month_info=$month)  a left join wz_f_car_info b on a.car_id = b.car_id) c  ";
                }
                else
                {
                    $sql = "select count(*) from(SELECT a.*,b.city from (select * from wz_f_one_car_one_table where year_info=$year and month_info=$month)  a left join wz_f_car_info b on a.car_id = b.car_id) c WHERE  city='".$cityName."'";
                }
                $allCarNum = Yii::$app->db->createCommand($sql)->queryScalar();


                //租赁车辆
                if(empty($cityName))
                {
                    $sql = "select count(*) from(SELECT a.*,b.self_rent,b.city from (select * from wz_f_one_car_one_table where year_info=$year and month_info=$month)  a left join wz_f_car_info b on a.car_id = b.car_id) c WHERE  self_rent='租赁车辆'";
                }
                else
                {
                    $sql = "select count(*) from(SELECT a.*,b.self_rent,b.city from (select * from wz_f_one_car_one_table where year_info=$year and month_info=$month)  a left join wz_f_car_info b on a.car_id = b.car_id) c WHERE self_rent='租赁车辆' and city='".$cityName."'";
                }
                $rentCarNum = Yii::$app->db->createCommand($sql)->queryScalar();

                //低效车辆
                if(empty($cityName))
                {
                    $sql = "select sum(num) from wz_f_fluc_inefficient_car WHERE year=$year and month=$month";
                }
                else
                {
                    $sql = "select sum(num) from wz_f_fluc_inefficient_car WHERE year=$year and month=$month and city='".$cityName."'";
                }
                $effCarNum = Yii::$app->db->createCommand($sql)->queryScalar();

                //违规车辆
                if(empty($cityName))
                {
                    $sql = "select sum(num) from wz_f_fluc_illegal_using_car WHERE year=$year and month=$month";
                }
                else
                {
                    $sql = "select sum(num) from wz_f_fluc_illegal_using_car WHERE year=$year and month=$month and city='".$cityName."'";
                }
                $legalCarNum = Yii::$app->db->createCommand($sql)->queryScalar();

                //燃油费
                if(empty($cityName))
                {
                    $sql = "select sum(oil_costs) from(select a.*,b.city from (select * from wz_f_one_car_cost where year=$year) a left join wz_f_car_info b on a.car_id = b.car_id) c ";

                }
                else
                {
                    $sql = "select sum(oil_costs) from(select a.*,b.city from (select * from wz_f_one_car_cost where year=$year) a left join wz_f_car_info b on a.car_id = b.car_id) c where city='".$cityName."'";
                }
                $oilCost =  Yii::$app->db->createCommand($sql)->queryScalar();
                if(empty($cityName))
                {
                    $sql = "select sum(oil_costs) from(select a.*,b.city from (select * from wz_f_one_car_cost  where year=$year and month=$month) a left join wz_f_car_info b on a.car_id = b.car_id) c";

                }
                else
                {
                    $sql = "select sum(oil_costs) from(select a.*,b.city from (select * from wz_f_one_car_cost  where year=$year and month=$month) a left join wz_f_car_info b on a.car_id = b.car_id) c where city='".$cityName."'";
                }
                $oilMonthCost =  Yii::$app->db->createCommand($sql)->queryScalar();

                //运行费
                if(empty($cityName))
                {
                    $sql = "select sum(oil_costs+repair_costs+insurance_costs+road_bridge_costs) from(select a.*,b.city from (select * from wz_f_one_car_cost where year=$year) a left join wz_f_car_info b on a.car_id = b.car_id) c ";

                }
                else
                {
                    $sql = "select sum(oil_costs+repair_costs+insurance_costs+road_bridge_costs) from(select a.*,b.city from (select * from wz_f_one_car_cost where year=$year) a left join wz_f_car_info b on a.car_id = b.car_id) c where city='".$cityName."'";
                }
                $totalCost =  Yii::$app->db->createCommand($sql)->queryScalar();

                if(empty($cityName))
                {
                    $sql = "select sum(oil_costs+repair_costs+insurance_costs+road_bridge_costs) from(select a.*,b.city from (select * from wz_f_one_car_cost  where year=$year and month=$month) a left join wz_f_car_info b on a.car_id = b.car_id) c ";

                }
                else
                {
                    $sql = "select sum(oil_costs+repair_costs+insurance_costs+road_bridge_costs) from(select a.*,b.city from (select * from wz_f_one_car_cost  where year=$year and month=$month) a left join wz_f_car_info b on a.car_id = b.car_id) c where city='".$cityName."'";
                }
                $totalMonthCost =  Yii::$app->db->createCommand($sql)->queryScalar();

                //车辆基本情况
                $sql = "SELECT ci.city as city,ci.car_type as car_type ,count(concat(ci.city,ci.car_type)) as car_num from (SELECT car_id ,year_info,month_info from wz_f_one_car_one_table WHERE year_info=$year and month_info=$month) ocot JOIN wz_f_car_info ci on (ocot.car_id=ci.car_id) WHERE ci.car_type in  ('皮卡车','微型面包车','越野车','轿车') GROUP BY ci.city,ci.car_type union SELECT ci.city,'其他',count(ci.city) from (SELECT car_id ,year_info,month_info from wz_f_one_car_one_table WHERE year_info=$year and month_info=$month) ocot JOIN wz_f_car_info ci on (ocot.car_id=ci.car_id) where ci.car_type not in ('皮卡车','微型面包车','越野车','轿车') GROUP BY ci.city ORDER BY city";
                $resultList = Yii::$app->db->createCommand($sql)->queryAll();


                foreach($this->carTypes as $k=>$carType){
                    $series1[$k]['name'] = $carType;
                    $tempDatas = [];
                    foreach($this->cityOptions as $city)
                    {
                        foreach($resultList as $result)
                        {
                            if($result['city']==$city&&$result['car_type']==$carType)
                            {
                                $tempDatas[] = intval($result['car_num']);
                                continue;
                            }
                        }

                    }
                    $series1[$k]['data'] = $tempDatas;
                }
                //详情表格1
                if(!empty($resultList))
                {
                    foreach($this->carTypes as $carType){

                        foreach($resultList as $result)
                        {
                            if($result['car_type']==$carType)
                            {
                                $table1ResultTotal[$carType] +=  intval($result['car_num']);
                            }

                        }
                    }
                }
                else
                {
                    foreach($this->carTypes as $carType){

                        $table1ResultTotal[$carType] += 0;
                    }

                }

                if(is_array($resultList))foreach($resultList as $result)
                {
                    $table1Result[$result['city'].'_'.$result['car_type']]  = intval($result['car_num']);
                }

                $table1Html = '';
                $table1Html .='<table class="table dt-responsive nowrap no-footer dtr-inline table-bordered table-hover">';
                $table1Html .= '<thead><tr><th>单位</th>';
                foreach($this->carTypes as $k=>$carType){

                    $table1Html.="<th>".$carType."</th>";

                }
                $table1Html .="<th>总计</th></tr></thead>";
                $table1Html .= "<tr>";
                $table1Html .= "<td>全省</td>";
                $totalNum = 0;
                if(is_array($table1ResultTotal))foreach($table1ResultTotal as $k=>$v)
                {
                    $table1Html .= "<td>".$v."</td>";
                    $totalNum += $v;
                }
                $table1Html.="<td>".$totalNum."</td></tr>";
                foreach($this->cityOptions as $city)
                {
                    $table1Html .= "<tr>";
                    $table1Html .= "<td>".$city."</td>";
                    $totalNum = 0;
                    foreach($this->carTypes as $carType){
                       if(isset($table1Result[$city.'_'.$carType]))
                       {
                           $table1Html.="<td>".$table1Result[$city.'_'.$carType]."</td>";
                       }
                       else
                       {
                           $table1Html.="<td>0</td>";
                       }
                       $totalNum += $table1Result[$city.'_'.$carType];
                    }
                    $table1Html.="<td>".$totalNum."</td></tr>";
                }

                $table1Html .="</table>";
                //北十省份
                $provinceResult = Yii::$app->db->createCommand("select distinct(province) from wz_f_ten_northern_provinces_stat")->queryAll();
                if(is_array($provinceResult))foreach($provinceResult as $uprovince)
                {
                    $provinceOptions[] = $uprovince['province'];
                }

                //车辆费用
                foreach($provinceOptions as $province)
                {
                    //本年累计
                    $sql = "select accumulated_expenditure_this_year from wz_f_ten_northern_provinces_stat  where year=$year and month=$month and province='".$province."'";
                    $fee1[] =  round(Yii::$app->db->createCommand($sql)->queryScalar(),2);
                    $fee1Province[$province] = round(Yii::$app->db->createCommand($sql)->queryScalar(),2);
                    //去年累计
                    $sql = "select accumulated_expenditure_last_year from wz_f_ten_northern_provinces_stat  where year=$lastyear and month=$month and province='".$province."'";
                    $fee2[] =  round(Yii::$app->db->createCommand($sql)->queryScalar(),2);
                    $fee2Province[$province] = round(Yii::$app->db->createCommand($sql)->queryScalar(),2);
                    //本年占收比
                    $sql = "select avg(ratio_this_year) from wz_f_ten_northern_provinces_stat where year=$year and month=$month and province='".$province."'";
                    $fee3[] =  round(Yii::$app->db->createCommand($sql)->queryScalar(),2);
                    $fee3Province[$province] = round(Yii::$app->db->createCommand($sql)->queryScalar(),2);
                    //去年占收比
                    $sql = "select avg(ratio_last_year) from wz_f_ten_northern_provinces_stat where year=$lastyear and month=$month and province='".$province."'";
                    $fee4[] =  round(Yii::$app->db->createCommand($sql)->queryScalar(),2);
                    $fee4Province[$province] =  round(Yii::$app->db->createCommand($sql)->queryScalar(),2);

                }

                //表格2


                $table2Html = '';
                $table2Html .='<table class="table dt-responsive nowrap no-footer dtr-inline table-bordered table-hover">';
                $table2Html .= '<thead><tr><th>省份</th><th>本年累计</th><th>去年累计</th><th>本年占收比</th><th>去年占收比</th></tr></thead>';

                foreach($provinceOptions as $province)
                {
                    $table2Html .= "<tr><td>".$province."</td>";
                    $table2Html .= "<td>".$fee1Province[$province]."</td>";
                    $table2Html .= "<td>".$fee2Province[$province]."</td>";
                    $table2Html .= "<td>".$fee3Province[$province]."</td>";
                    $table2Html .= "<td>".$fee4Province[$province]."</td>";
                    $table2Html .= "</tr>";
                }
                $table2Html .= "</table>";
                //车辆里程波动
                for($i=1;$i<=12;$i++)
                {
                    //本年里程
                    if(empty($cityName))
                    {
                        $sql = "select sum(mileage) from (select a.*,b.city from (select * from wz_f_mileage_attend where  year=$year and month=$i) a left join wz_f_car_info b on a.car_id = b.car_id) c ";
                    }
                    else
                    {
                        $sql = "select sum(mileage) from (select a.*,b.city from (select * from wz_f_mileage_attend where  year=$year and month=$i) a left join wz_f_car_info b on a.car_id = b.car_id) c where city='".$cityName."'";
                    }
                    $miles1[] = round(Yii::$app->db->createCommand($sql)->queryScalar()/10000,2);
                    //去年里程
                    if(empty($cityName))
                    {
                        $sql = "select sum(mileage) from (select a.*,b.city from (select * from wz_f_mileage_attend where year=$lastyear and month=$i) a left join wz_f_car_info b on a.car_id = b.car_id) c ";
                    }
                    else
                    {
                        $sql = "select sum(mileage) from (select a.*,b.city from (select * from wz_f_mileage_attend where year=$lastyear and month=$i) a left join wz_f_car_info b on a.car_id = b.car_id) c where city='".$cityName."'";
                    }
                    $miles2[] = round(Yii::$app->db->createCommand($sql)->queryScalar()/10000,2);
                }

                //表格三
                $sql = "select sum(mileage) as mileage,city from (select a.*,b.city from (select * from wz_f_mileage_attend where  year=$year and month=$month) a left join wz_f_car_info b on a.car_id = b.car_id) c where city !='' group by city";
                $table3ResultYearList = Yii::$app->db->createCommand($sql)->queryAll();
                $sql = "select sum(mileage) as mileage from (select a.*,b.city from (select * from wz_f_mileage_attend where  year=$year and month=$month) a left join wz_f_car_info b on a.car_id = b.car_id) c where city !=''";
                $table3ResultYearListTotal = Yii::$app->db->createCommand($sql)->queryAll();
                $table3ResultYearListTotal[0]['city'] = '全省';
                $table3ResultYearList = array_merge($table3ResultYearListTotal,$table3ResultYearList);
                foreach($table3ResultYearList as $table3ResultYearUnit)
                {
                    $table3ResultYearList[$table3ResultYearUnit['city']] = $table3ResultYearUnit;
                }


                $sql = "select sum(mileage) as mileage,city from (select a.*,b.city from (select * from wz_f_mileage_attend where  year=$lastyear and month=$month) a left join wz_f_car_info b on a.car_id = b.car_id) c where city !='' group by city";
                $table3ResultLastYearList = Yii::$app->db->createCommand($sql)->queryAll();
                $sql = "select sum(mileage) as mileage from (select a.*,b.city from (select * from wz_f_mileage_attend where  year=$lastyear and month=$month) a left join wz_f_car_info b on a.car_id = b.car_id) c where city !=''";
                $table3ResultLastYearListTotal = Yii::$app->db->createCommand($sql)->queryAll();
                $table3ResultLastYearListTotal[0]['city'] = '全省';
                $table3ResultLastYearList = array_merge($table3ResultLastYearListTotal,$table3ResultLastYearList);
                foreach($table3ResultLastYearList as $table3ResultLastYearUnit)
                {
                    $table3ResultLastYearList[$table3ResultLastYearUnit['city']] = $table3ResultLastYearUnit;
                }
                $table3ResultList = [];
                $table3ResultList['全省'] = array(
                  0=>'全省',
                  1=> $table3ResultYearListTotal[0]['mileage'],
                  2=> $table3ResultLastYearListTotal[0]['mileage'],
                  3=>  $table3ResultYearListTotal[0]['mileage']-$table3ResultLastYearListTotal[0]['mileage'],
                  4=>  $table3ResultLastYearListTotal[0]['mileage']?round(((($table3ResultYearListTotal[0]['mileage']-$table3ResultLastYearListTotal[0]['mileage'])/$table3ResultLastYearListTotal[0]['mileage'])*100),2).'%':'--'
                );
                foreach($this->cityOptions as $city)
                {
                    $table3ResultList[$city] = array(
                        0=>$city,
                        1=> $table3ResultYearList[$city]['mileage'],
                        2=> $table3ResultLastYearList[$city]['mileage'],
                        3=>  $table3ResultYearList[$city]['mileage']-$table3ResultLastYearList[$city]['mileage'],
                        4=> $table3ResultLastYearList[$city]['mileage']>0? round(((($table3ResultYearList[$city]['mileage']-$table3ResultLastYearList[$city]['mileage'])/$table3ResultLastYearList[$city]['mileage'])*100),2).'%':'--'
                    );

                }


                $table3Html = '';
                $table3Html .='<table class="table dt-responsive nowrap no-footer dtr-inline table-bordered table-hover">';
                $table3Html .= '<thead><tr><th>地市</th><th>累计</th><th>去年同期</th><th>同比变动</th><th>同比增幅</th></tr></thead>';
                if(is_array($table3ResultList))foreach($table3ResultList as $table3ResultUnit)
                {
                    $table3Html .= '<tr><td>'.$table3ResultUnit[0].'</td><td>'.$table3ResultUnit[1].'</td><td>'.$table3ResultUnit[2].'</td><td>'.$table3ResultUnit[3].'</td><td>'.$table3ResultUnit[4].'</td></tr>';
                }
                $table3Html .="</table>";

                for($i=1;$i<=12;$i++)
                {
                    //本年出勤率
                    if(empty($cityName))
                    {
                        $sql = "select avg(attend_rate) from (select a.*,b.city from (select * from wz_f_mileage_attend where  year=$year and month=$i) a left join wz_f_car_info b on a.car_id = b.car_id) c";
                    }
                    else
                    {
                        $sql = "select avg(attend_rate) from (select a.*,b.city from (select * from wz_f_mileage_attend where  year=$year and month=$i) a left join wz_f_car_info b on a.car_id = b.car_id) c where city='".$cityName."'";
                    }
                    $attend1[] = round(Yii::$app->db->createCommand($sql)->queryScalar(),2);
                    //去年出勤率
                    if(empty($cityName))
                    {
                        $sql = "select avg(attend_rate) from (select a.*,b.city from (select * from wz_f_mileage_attend  where  year=$lastyear and month=$i) a left join wz_f_car_info b on a.car_id = b.car_id) c";
                    }
                    else
                    {
                        $sql = "select avg(attend_rate) from (select a.*,b.city from (select * from wz_f_mileage_attend  where  year=$lastyear and month=$i) a left join wz_f_car_info b on a.car_id = b.car_id) c where city='".$cityName."'";
                    }
                    $attend2[] = round(Yii::$app->db->createCommand($sql)->queryScalar(),2);

                }

                //表格四
                $sql = "select avg(attend_rate) as attend_rate,city from (select a.*,b.city from (select * from wz_f_mileage_attend where  year=$year and month=$month) a left join wz_f_car_info b on a.car_id = b.car_id) c where city !='' group by city";
                $table4ResultYearList = Yii::$app->db->createCommand($sql)->queryAll();
                $sql = "select avg(attend_rate) as attend_rate from (select a.*,b.city from (select * from wz_f_mileage_attend where  year=$year and month=$month) a left join wz_f_car_info b on a.car_id = b.car_id) c where city !=''";
                $table4ResultYearListTotal = Yii::$app->db->createCommand($sql)->queryAll();
                $table4ResultYearListTotal[0]['city'] = '全省';
                $table4ResultYearList = array_merge($table4ResultYearListTotal,$table4ResultYearList);
                foreach($table4ResultYearList as $table4ResultYearUnit)
                {
                    $table4ResultYearList[$table4ResultYearUnit['city']] = $table4ResultYearUnit;
                }


                $sql = "select avg(attend_rate) as attend_rate,city from (select a.*,b.city from (select * from wz_f_mileage_attend where  year=$lastyear and month=$month) a left join wz_f_car_info b on a.car_id = b.car_id) c where city !='' group by city";
                $table4ResultLastYearList = Yii::$app->db->createCommand($sql)->queryAll();
                $sql = "select avg(attend_rate) as attend_rate from (select a.*,b.city from (select * from wz_f_mileage_attend where  year=$lastyear and month=$month) a left join wz_f_car_info b on a.car_id = b.car_id) c where city !=''";
                $table4ResultLastYearListTotal = Yii::$app->db->createCommand($sql)->queryAll();
                $table4ResultLastYearListTotal[0]['city'] = '全省';
                $table4ResultLastYearList = array_merge($table4ResultLastYearListTotal,$table4ResultLastYearList);
                foreach($table4ResultLastYearList as $table4ResultLastYearUnit)
                {
                    $table4ResultLastYearList[$table4ResultLastYearUnit['city']] = $table4ResultLastYearUnit;
                }


                $table4ResultList = [];
                $table4ResultList['全省'] = array(
                    0=>'全省',
                    1=> $table4ResultYearListTotal[0]['attend_rate'],
                    2=> $table4ResultLastYearListTotal[0]['attend_rate'],
                    3=>  $table4ResultYearListTotal[0]['attend_rate']-$table4ResultLastYearListTotal[0]['attend_rate'],
                    4=>  $table4ResultLastYearListTotal[0]['attend_rate']?round(((($table4ResultYearListTotal[0]['attend_rate']-$table4ResultLastYearListTotal[0]['attend_rate'])/$table4ResultLastYearListTotal[0]['attend_rate'])*100),2).'%':'--'
                );
                foreach($this->cityOptions as $city)
                {
                    $table4ResultList[$city] = array(
                        0=>$city,
                        1=> $table4ResultYearList[$city]['attend_rate'],
                        2=> $table4ResultLastYearList[$city]['attend_rate'],
                        3=>  $table4ResultYearList[$city]['attend_rate']-$table4ResultLastYearList[$city]['attend_rate'],
                        4=> $table4ResultLastYearList[$city]['attend_rate']>0? round(((($table4ResultYearList[$city]['attend_rate']-$table4ResultLastYearList[$city]['attend_rate'])/$table4ResultLastYearList[$city]['attend_rate'])*100),2).'%':'--'
                    );

                }


                $table4Html = '';
                $table4Html .='<table class="table dt-responsive nowrap no-footer dtr-inline table-bordered table-hover">';
                $table4Html .= '<thead><tr><th>地市</th><th>累计</th><th>去年同期</th><th>同比变动</th><th>同比增幅</th></tr></thead>';
                if(is_array($table4ResultList))foreach($table4ResultList as $table4ResultUnit)
                {
                    $table4Html .= '<tr><td>'.$table4ResultUnit[0].'</td><td>'.round($table4ResultUnit[1],2).'</td><td>'.round($table4ResultUnit[2],2).'</td><td>'.round($table4ResultUnit[3],2).'</td><td>'.$table4ResultUnit[4].'</td></tr>';
                }
                $table4Html .="</table>";


                echo_json(array(
                    'error'=>'0',
                    'msg'=>'获取成功',
                    'allCarNum'=>$allCarNum>0?$allCarNum:'--',
                    'rentCarNum'=>$rentCarNum>0?$rentCarNum:'--',
                    'effCarNum'=>$effCarNum>0?$effCarNum:'--',
                    'legalCarNum'=>$legalCarNum>0?$legalCarNum:'--',
                    'oilCost'=>$oilCost>0?numconvert2($oilCost):'--',
                    'oilMonthCost'=>$oilMonthCost>0?numconvert2($oilMonthCost):'--',
                    'totalCost'=>$totalCost>0?numconvert2($totalCost):'--',
                    'totalMonthCost'=>$totalMonthCost>0?numconvert2($totalMonthCost):'--',
                    'series1'=>$series1,
                    'fee1'=>$fee1,
                    'fee2'=>$fee2,
                    'fee3'=>$fee3,
                    'fee4'=>$fee4,
                    'miles1'=>$miles1,
                    'miles2'=>$miles2,
                    'attend1'=>$attend1,
                    'attend2'=>$attend2,
                    'currentYear'=>$year,
                    'lastYear'=>$lastyear,
                    'carTypes'=>$this->carTypes,
                    'cityOptions'=>$this->cityOptions,
                    'colors'=>$this->colors,
                    'provinceOptions'=>$provinceOptions,
                    'table1Html'=>$table1Html,
                    'table2Html'=>$table2Html,
                    'table3Html'=>$table3Html,
                    'table4Html'=>$table4Html
                ));

            }
            else
            {
                $msgdata = ['error' => 1,'msg' => '系统错误（缺少查询参数）','data'=>array(),'code'=>'200'];
            }

            echo_json($msgdata);
        }
        return $this->render('index');
    }

    //车辆专题报告
    public function actionReport()
    {
        $cacheFlag = 'report';
        $cacheData = Yii::$app->cache->get($cacheFlag);
        $cacheData = json_decode($cacheData,true);
        if($_GET['docache']==1) $cacheData = null;
        if(empty($cacheData)) {
            //车辆费用
            $year = get_date(TIMESTAMP, 'Y');
            $lastyear = $year - 1;
            $startmonth = 1;
            $endmonth = get_date(TIMESTAMP, 'm')-1;
            $sql = "select sum(accumulated_expenditure_this_year)  from wz_f_ten_northern_provinces_stat
     where year=$year and (month>=$startmonth and month<=$endmonth) and province='" . $this->province . "'";
            $totalcost = Yii::$app->db->createCommand($sql)->queryScalar();
            $sql = "select sum(accumulated_income_this_year)  from wz_f_ten_northern_provinces_stat
     where year=$year and (month>=$startmonth and month<=$endmonth) and province='" . $this->province . "'";
            $totalincome = Yii::$app->db->createCommand($sql)->queryScalar();
            $yearRatio = (round($totalcost / ($totalincome * 10000), 2) * 100) . '%';
            $sql = "select sum(accumulated_expenditure_last_year)  from wz_f_ten_northern_provinces_stat
     where year=$year and (month>=$startmonth and month<=$endmonth) and province='" . $this->province . "'";
            $totalcostLast = Yii::$app->db->createCommand($sql)->queryScalar();
            $sql = "select avg(ratio_this_year) as total,province  from wz_f_ten_northern_provinces_stat
     where year=$year and (month>=$startmonth and month<=$endmonth) group by province";
            $tenCityInfo = Yii::$app->db->createCommand($sql)->queryAll();
            $sort_1 = array_column($tenCityInfo, 'total');
            array_multisort($sort_1, SORT_DESC, $tenCityInfo);
            foreach ($tenCityInfo as $k => $v) {
                $provinceOrder[$v['province']] = $k + 1;
            }
            $sql = "select sum(accumulated_income_this_year) as accumulated_income_this_year, sum(accumulated_income_last_year) as accumulated_income_last_year, sum(accumulated_expenditure_this_year) as accumulated_expenditure_this_year, sum(accumulated_expenditure_last_year) as accumulated_expenditure_last_year, sum(budget_accumulation) as budget_accumulation,city from wz_f_income_and_expenditure_of_hebei_province_stat where year=$year and (month>=$startmonth and month<=$endmonth) group by city";
            $cityBudgets = Yii::$app->db->createCommand($sql)->queryAll();
            if (is_array($cityBudgets)) foreach ($cityBudgets as $k => $v) {
                $cityBudgets[$k]['year_on_year_gap'] = $v['accumulated_expenditure_this_year'] - $v['accumulated_expenditure_last_year'];
                $cityBudgets[$k]['tongbi'] = ($v['accumulated_expenditure_this_year'] - $v['accumulated_expenditure_last_year']) / $v['accumulated_expenditure_last_year'] * 100;
                $cityBudgets[$k]['budget_deviation'] = $v['accumulated_expenditure_this_year'] - $v['budget_accumulation'];
                $cityBudgets[$k]['budget_deviation_percent'] = round(($v['accumulated_expenditure_this_year'] - $v['budget_accumulation']) / $v['budget_accumulation'] * 100, 2);
                $cityBudgets[$k]['zhanshoubi'] = $v['accumulated_income_this_year']>0?$v['accumulated_expenditure_this_year'] / $v['accumulated_income_this_year']:'';
            }

            $sql = "select sum(accumulated_income_this_year) as accumulated_income_this_year, sum(accumulated_income_last_year) as accumulated_income_last_year, sum(accumulated_expenditure_this_year) as accumulated_expenditure_this_year, sum(accumulated_expenditure_last_year) as accumulated_expenditure_last_year, sum(budget_accumulation) as budget_accumulation from wz_f_income_and_expenditure_of_hebei_province_stat where year=$year and (month>=$startmonth and month<=$endmonth)";
            $cityBudgets0 = Yii::$app->db->createCommand($sql)->queryOne();

            $cityBudgets0['city'] = '全省';
            $cityBudgets0['year_on_year_gap'] = $cityBudgets0['accumulated_expenditure_this_year'] - $cityBudgets0['accumulated_expenditure_last_year'];
            $cityBudgets0['tongbi'] = ($cityBudgets0['accumulated_expenditure_this_year'] - $cityBudgets0['accumulated_expenditure_last_year']) / $cityBudgets0['accumulated_expenditure_last_year'] * 100;
            $cityBudgets0['budget_deviation'] = $cityBudgets0['accumulated_expenditure_this_year'] - $cityBudgets0['budget_accumulation'];
            $cityBudgets0['budget_deviation_percent'] = round(($cityBudgets0['accumulated_expenditure_this_year'] - $cityBudgets0['budget_accumulation']) / $cityBudgets0['budget_accumulation'] * 100, 2);
            $cityBudgets0['zhanshoubi'] = $cityBudgets0['accumulated_income_this_year']>0?$cityBudgets0['accumulated_expenditure_this_year'] / $cityBudgets0['accumulated_income_this_year']:'';
            $cityBudgets = array_merge(array($cityBudgets0), $cityBudgets);

            $cityBudgets1 = $cityBudgets;
            unset($cityBudgets1[0]);
            $sort_1 = array_column($cityBudgets1, 'zhanshoubi');
            array_multisort($sort_1, SORT_DESC, $cityBudgets1);
            foreach ($cityBudgets1 as $k => $v) {
                if ($k > 2) continue;
                $budgetsCitys[] = $v['city'];
            }
            $cityBudgets = array_merge(array($cityBudgets0), $cityBudgets1);


            //低效车辆统计
            if (empty($params['start_date'])) $params['start_date'] = get_date(TIMESTAMP, 'Y-m');
            if (empty($params['end_date'])) $params['end_date'] = get_date(TIMESTAMP, 'Y-m');
            $lastYear = get_date(TIMESTAMP, 'Y') - 1;
            if (empty($params1['start_date'])) $params1['start_date'] = get_date(TIMESTAMP, $lastYear . '-m');
            if (empty($params1['end_date'])) $params1['end_date'] = get_date(TIMESTAMP, $lastYear . '-m');
            $sql_oncaronetable = $this->getOneCarOneTable($params, 'car_id,
            attend_days,
            work_days,
            sum_mileage,
            (CASE WHEN attend_days * 100 / work_days < ( SELECT thd_att FROM wz_f_input_policy WHERE is_active = 1 ) 
                    AND sum_mileage / work_days < ( SELECT thd_km_per_day FROM wz_f_input_policy WHERE is_active = 1 ) THEN
                        1 ELSE 0 
                    END 
                    ) AS is_ineff');
            $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_oncaronetable . ') ocot  JOIN (' . $sql_carinfo . ')  ci ON ( ocot.car_id = ci.car_id ) ';
            $field = 'city,
                cast(count(ocot.car_id)/(SELECT (case when max(month_info)>= '.$endmonth.' then '.$endmonth.' else max(month_info) end)-1+1 from wz_f_one_car_one_table WHERE year_info = '.$year.') as decimal(16,0)) AS car_num,
                sum( ocot.is_ineff ) AS ineff_car,
                round( sum( ocot.is_ineff ) * 100 / count( ocot.car_id ), 2 ) AS ineff_per,
                round( sum( ocot.sum_mileage ) / sum( work_days ), 2 ) AS av_daily_mileage,
                round( sum( attend_days ) * 100 / sum( work_days ), 2 ) AS attend_rate,
                '.$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 = $this->getPingjiaData($table, $field, $params, 1);
            $dataAll['rows'][0]['city'] = '全省';
            $data['rows'] = array_merge($dataAll['rows'], $data['rows']);
            $data = $data['rows'];

            $sql_oncaronetable = $this->getOneCarOneTable($params1, 'car_id,
            attend_days,
            work_days,
            sum_mileage,
            (CASE WHEN attend_days * 100 / work_days < ( SELECT thd_att FROM wz_f_input_policy WHERE is_active = 1 ) 
                    AND sum_mileage / work_days < ( SELECT thd_km_per_day FROM wz_f_input_policy WHERE is_active = 1 ) THEN
                        1 ELSE 0 
                    END 
                    ) AS is_ineff');
            $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_oncaronetable . ') ocot  JOIN (' . $sql_carinfo . ')  ci ON ( ocot.car_id = ci.car_id ) ';
            $field = 'city,
                cast(count(ocot.car_id)/(SELECT (case when max(month_info)>= '.$endmonth.' then '.$endmonth.' else max(month_info) end)-1+1 from wz_f_one_car_one_table WHERE year_info = '.$year.') as decimal(16,0)) AS car_num,
                sum( ocot.is_ineff ) AS ineff_car,
                round( sum( ocot.is_ineff ) * 100 / count( ocot.car_id ), 2 ) AS ineff_per,
                round( sum( ocot.sum_mileage ) / sum( work_days ), 2 ) AS av_daily_mileage,
                round( sum( attend_days ) * 100 / sum( work_days ), 2 ) AS attend_rate,
                '.$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';
            $data1 = $this->getPingjiaData($table, $field, $params1);
            $dataAll1 = $this->getPingjiaData($table, $field, $params1, 1);
            $dataAll1['rows'][0]['city'] = '全省';
            $data1['rows'] = array_merge($dataAll1['rows'], $data1['rows']);
            $data1 = $data1['rows'];
            $lowtotal = [];
            $lowdata = [];
            $lowtotal['car_num'] = $data[0]['car_num'];
            $lowtotal['ineff_car'] = $data[0]['ineff_car'];
            $lowtotal['ineff_per'] = $data[0]['ineff_per'] . '%';
            $lowtotal['lastyear_ineff_per'] = $data1[0]['ineff_per'] . '%';
            foreach ($data as $k => $v) {
                $lowdata[] = array(
                    'city' => $v['city'],
                    'car_num' => $v['car_num'],
                    'ineff_car' => $v['ineff_car'],
                    'lastyear_ineff_per' => $data1[$k]['ineff_per'] . '%',
                    'ineff_per' => $v['ineff_per'] . '%',
                    'ineff_per_num' => $v['ineff_per'],
                    'tongbi' => ($v['ineff_per'] > $data1[$k]['ineff_per'] ? '' : '-') . round((abs($v['ineff_per'] - $data1[$k]['ineff_per']) / $data1[$k]['ineff_per']) * 100, 2) . '%'

                );
            }
            $lowdata1 = $lowdata;
            unset($lowdata1[0]);
            $sort_1 = array_column($lowdata1, 'ineff_per_num');
            array_multisort($sort_1, SORT_ASC, $lowdata1);
            foreach ($lowdata1 as $k => $v) {
                $cityOrder[$v['city']] = $k + 1;
            }
            $lowtotal['citys'] = $lowdata1[0]['city'] . '，' . $lowdata1[1]['city'] . '，' . $lowdata1[2]['city'];
            foreach ($lowdata as $k => $v) {
                $lowdata[$k]['order'] = $cityOrder[$v['city']];
            }
            //违规车辆统计
            $sql = "select sum(illegal_times) as times from wz_f_cars_detail_data a left join wz_f_car_info b on a.car_id=b.car_id where a.year=$year";
            $totalegal = Yii::$app->db->createCommand($sql)->queryScalar();
            $legaldata = [];
            $unitrow['city'] = '全省';
            $all = 0;
            for ($i = 1; $i <= $endmonth; $i++) {
                $sql = "select sum(illegal_times) as times from wz_f_cars_detail_data a left join wz_f_car_info b on a.car_id=b.car_id where a.year=$year and a.month=$i";
                $unitrow['datas'][] = Yii::$app->db->createCommand($sql)->queryScalar();
                $all += Yii::$app->db->createCommand($sql)->queryScalar();
            }
            $unitrow['all'] = $all;
            $legaldata[] = $unitrow;
            foreach ($this->cityOptions as $k => $city) {
                $all = 0;
                $legaldata[$k + 1]['city'] = $city;
                for ($i = 1; $i <= $endmonth; $i++) {
                    $sql = "select sum(illegal_times) as times from wz_f_cars_detail_data a left join wz_f_car_info b on a.car_id=b.car_id where a.year=$year and a.month=$i and b.city='" . $city . "'";
                    $legaldata[$k + 1]['datas'][] = Yii::$app->db->createCommand($sql)->queryScalar();
                    $all += Yii::$app->db->createCommand($sql)->queryScalar();
                }
                $legaldata[$k + 1]['all'] = $all;
            }
            $maxcity = '';
            $maxnum = 0;
            foreach (array_slice($legaldata, 1) as $unitData) {
                if ($unitData['all'] > $maxnum) {
                    $maxnum = $unitData['all'];
                    $maxcity = $unitData['city'];
                }
            }
            if ($legaldata[0]['all'] > 0) {
                $percent = (round($maxnum / $legaldata[0]['all'], 2) * 100) . '%';
            }


            $result =  array('lowdata' => $lowdata, 'lowtotal' => $lowtotal, 'year' => $year, 'startmonth' => $startmonth, 'endmonth' => $endmonth, 'maxcity' => $maxcity, 'maxnum' => $maxnum, 'percent' => $percent, 'legaldata' => $legaldata, 'totalcost' => $totalcost, 'yearRatio' => $yearRatio, 'totalcostLast' => $totalcostLast, 'provinceOrder' => $provinceOrder[$this->province], 'cityBudgets' => $cityBudgets, 'budgetsCitys' => $budgetsCitys);
            //生成缓存
            if($_GET['docache']==1)
            {
                Yii::$app->cache->set($cacheFlag, json_encode($result),0);
            }
            return $this->render('report',$result);
        }
        else
        {
            return $this->render('report',$cacheData);
        }


    }

    //一车一表
    public function actionTable()
    {
        //初始条件
        //$post = Yii::$app->request->get();
        //if(!empty($post))
        //{
            $statistical_month = Yii::$app->request->get('statistical_month');
            $card_num = Yii::$app->request->get('card_num');

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

            if(empty($card_num)) {
                $car = FCarInfo::find()->where($where)->orderBy('random()')->asArray()->one();
                $card_num = $car['card_num'];
            }
            //{
                $startdateInfo = explode('-',$statistical_month);
                $start_year = $startdateInfo[0]?$startdateInfo[0]:get_date(TIMESTAMP,'Y');
                $start_month = 1;
                $end_year = $start_year;
                $end_month = $startdateInfo[1]?intval($startdateInfo[1]):12;

                $year = $start_year;

        $sum_field = [
            'sum_costs' => 'cast(sum("sum_costs") as decimal(10,2))',
            'sum_driving' => 'cast(sum("oil_costs"+"repair_costs"+"road_bridge_costs"+"insurance_costs") as decimal(10,2))',
            'sum_oil' => 'cast(sum("oil_costs") as decimal(10,2))',
            'sum_repair' => 'cast(sum("repair_costs") as decimal(10,2))',
            'sum_road_bridge' => 'cast(sum("road_bridge_costs") as decimal(10,2))',
            'sum_rent' => 'cast(sum("rent_costs") as decimal(10,2))',
            'sum_insurance' => 'cast(sum("insurance_costs") as decimal(10,2))',
            'sum_year_check' => 'cast(sum("year_check_costs") as decimal(10,2))',
            'sum_other' => 'cast(sum("other_costs") as decimal(10,2))',
            'sum_mileage' => 'cast(sum("sum_mileage") as decimal(10,2))',
            'violations_times' => 'sum("violations_times")',
            'attend_days' => 'sum("attend_days")',
            'work_days' => 'sum("work_days")'
        ];

        $monthData = [];

                $where['card_num'] = $card_num;
                $cars = FCarInfo::find()->where($where)->orderBy('car_id asc')->asArray()->all();
                $city = '';
                $last_id = 0;
                foreach ($cars as $car) {
                    $car_id = $car['car_id'];
                    unset($sum_field['month_info']);
                    $sum_data[$car_id] = FOneCarOneTable::find()->select($sum_field)->where("car_id='" . $car_id . "' and year_info='" . $year . "' and (month_info<=$end_month)")->asArray()->one();
                    if(!$sum_data[$car_id]) continue;
                    //当前车辆账期内成本排行
                    $rankList = Yii::$app->db->createCommand("select sum(sum_costs) as sumcosts,car_id from {{%f_one_car_one_table}} where year_info='" . $year . "'  and (month_info>=$start_month and month_info<=$end_month) group by car_id order by sumcosts desc")->queryAll();
                    foreach ($rankList as $k => $rankInfo) {
                        if ($car_id == $rankInfo['car_id']) $rank[$car_id] = $k + 1;
                    }


                    $sum_field['month_info'] = 'month_info';

                    $month_data = FOneCarOneTable::find()->select($sum_field)->where("car_id='" . $car['car_id'] . "' and year_info='" . $year . "' and (month_info<=$end_month)")->groupBy('month_info')->orderBy('month_info')->asArray()->all();

                    foreach ($month_data as $k => $v) {
                        /*if ($k > 0) {
                            foreach ($v as $_k => $_v) {
                                if ($_k != 'month_info') {
                                    $month_data[$k][$_k] += $month_data[$k - 1][$_k];
                                }
                            }
                        }*/
                        $monthData[$car_id][$v['month_info']] = $month_data[$k];
                    }


                    if($car['city']==$city && $last_id>0){
                        foreach ($sum_data[$car_id] as $k=>$v){
                            $sum_data[$car_id][$k] = $sum_data[$last_id][$k] + $v;
                        }

                        foreach ($monthData[$last_id] as $k => $v) {
                            foreach ($v as $_k=>$_v){
                                $monthData[$car_id][$k][$_k] = round($monthData[$car_id][$k][$_k],2) + round($_v,2);
                            }
                        }
                    }

                    $city = $car['city'];
                    $last_id = $car_id;
                }

        $last = [];
                ksort($monthData[$car_id]);
        foreach ($monthData[$car_id] as $k => $v) {
            if(!empty($last)){
                foreach ($v as $_k=>$_v){
                    $monthData[$car_id][$k][$_k] += $last[$_k];
                }
            }
            $last = $monthData[$car_id][$k];
            $monthData[$car_id][$k]['attendance'] = $monthData[$car_id][$k]['work_days'] ? $monthData[$car_id][$k]['attend_days'] / $monthData[$car_id][$k]['work_days'] : 0.00;
            if($monthData[$car_id][$k]['sum_mileage']>0){
                $monthData[$car_id][$k]['oil_per_km'] = $monthData[$car_id][$k]['sum_oil'] / $monthData[$car_id][$k]['sum_mileage'] * 100;
                $monthData[$car_id][$k]['repair_per_km'] = $monthData[$car_id][$k]['sum_repair'] / $monthData[$car_id][$k]['sum_mileage'] * 100;
                $monthData[$car_id][$k]['sum_costs_per_km'] = $monthData[$car_id][$k]['sum_costs'] / $monthData[$car_id][$k]['sum_mileage'] * 100;
            }else{
                $monthData[$car_id][$k]['oil_per_km'] = 0.00;
                $monthData[$car_id][$k]['repair_per_km'] = 0.00;
                $monthData[$car_id][$k]['sum_costs_per_km'] = 0.00;
            }
        }


            //}
            //else
            //{
                //Yii::$app->session->setFlash('error','请输入车牌号');
            //}

        //}
        $koujing = '这里是口径描述';
        return $this->render('table',array('searchData'=>$params,'cars'=>$cars,'car'=>$car,'sum_data'=>$sum_data[$car_id],'monthData'=>$monthData[$car_id],'rank'=>$rank[$car_id],'koujing'=>$koujing));
    }

    //车辆基本信息
    public function actionBaseinfo()
    {
        $type = Yii::$app->request->get('type',1);
        //车辆记录
        if($type==1)
        {
            $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(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('date'=>$result['year_info'].($result['month_info']>=10?$result['month_info']:'0'.$result['month_info']),'card_num'=>$result['card_num'],'city'=>$result['city'],'dpt_sec'=>$result['dpt_sec'],'grid'=>$result['grid'],'self_rent'=>$result['self_rent'],'car_type'=>$result['car_type'],'using_tag'=>$result['using_tag']);
                }
                $result = ["total"=>$countQuery->count(),"totalNotFiltered"=>$countQuery->count(),"rows"=>$data];
                echo_json($result);
            }

            $this->tableTitle = array(
                array('field'=>'date','title'=>'时间','align'=>'center'),
                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'=>'self_rent','title'=>'车辆来源','align'=>'center'),
                array('field'=>'car_type','title'=>'车辆类型','align'=>'center'),
                array('field'=>'using_tag','title'=>'车辆使用性质','align'=>'center'),
            );

            $this->tableConfig = array('table'=>FOneCarOneTable::shortTableName(),'url'=>$this->createRealUrl(['car/default/baseinfo','type'=>1]),'setFieldUrl'=>$this->createRealUrl(['car/default/setfield']),'idField'=>FOneCarOneTable::modelPrimaryKey(),'checkbox'=>0,'dropmenu'=>1,'pagination'=>true,'pagesize'=>20,'refresh'=>true,'exportFileName'=>'睿行车辆记录','height'=>500);
        }
        //车况
        if($type==2)
        {
            $query = FCarCondition::find()->alias('chekuang')->leftJoin('{{%f_car_info}}', 'chekuang.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(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(['chekuang.id'=>SORT_DESC])->asArray()->all();
                }

                foreach($resultList as $result)
                {
                    $data[] = array('date'=>$result['year'].($result['month']>=10?$result['month']:'0'.$result['month']),'card_num'=>$result['card_num'],'city'=>$result['city'],'dpt_sec'=>$result['dpt_sec'],'grid'=>$result['grid'],'factory'=>$result['factory'],'enabled_date'=>$result['enabled_date'],'car_age'=>$result['car_age']);
                }
                $result = ["total"=>$countQuery->count(),"totalNotFiltered"=>$countQuery->count(),"rows"=>$data];
                echo_json($result);
            }

            $this->tableTitle = array(
                array('field'=>'date','title'=>'时间','align'=>'center'),
                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'=>'factory','title'=>'厂家','align'=>'center'),
                array('field'=>'enabled_date','title'=>'启用日期','align'=>'center'),
                array('field'=>'car_age','title'=>'车龄（年）','align'=>'center','sortable'=>true,'class'=>'bigwidth'),
            );

            $this->tableConfig = array('table'=>FCarCondition::shortTableName(),'url'=>$this->createRealUrl(['car/default/baseinfo','type'=>2]),'setFieldUrl'=>$this->createRealUrl(['car/default/baseinfo']),'idField'=>FCarCondition::modelPrimaryKey(),'checkbox'=>0,'dropmenu'=>1,'pagination'=>true,'pagesize'=>20,'refresh'=>true,'exportFileName'=>'车况','height'=>500);
        }
        //报废
        if($type==3)
        {
            $query = FCarScrap::find()->alias('chekuang')->leftJoin('{{%f_car_info}}', 'chekuang.card_num = {{%f_car_info}}.card_num');
            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 = str_replace('card_num','chekuang.card_num',join(" and ",$where))." and chekuang.card_num!='' and {{%f_car_info}}.card_num!=''";
                    $query->where($sql);
                }

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

                foreach($resultList as $result)
                {
                    $data[] = array('date'=>$result['year'].($result['month']>=10?$result['month']:'0'.$result['month']),'card_num'=>$result['card_num'],'city'=>$result['city'],'dpt_sec'=>$result['dpt_sec'],'grid'=>$result['grid'],'factory'=>$result['factory'],'enabled_date'=>$result['enabled_date'],'car_age'=>$result['car_age']);
                }
                $result = ["total"=>$countQuery->count(),"totalNotFiltered"=>$countQuery->count(),"rows"=>$data];
                echo_json($result);
            }

            $this->tableTitle = array(
                array('field'=>'date','title'=>'时间','align'=>'center'),
                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'=>'factory','title'=>'厂家','align'=>'center'),
                array('field'=>'enabled_date','title'=>'启用日期','align'=>'center'),
                array('field'=>'car_age','title'=>'车龄（年）','align'=>'center','sortable'=>true,'class'=>'bigwidth'),
            );

            $this->tableConfig = array('table'=>FCarCondition::shortTableName(),'url'=>$this->createRealUrl(['car/default/baseinfo','type'=>3]),'setFieldUrl'=>$this->createRealUrl(['car/default/baseinfo']),'idField'=>FCarCondition::modelPrimaryKey(),'checkbox'=>0,'dropmenu'=>1,'pagination'=>true,'pagesize'=>20,'refresh'=>true,'exportFileName'=>'报废','height'=>500);
        }
        $koujing = '这里是口径描述';
        return $this->render('baseinfo',['type'=>$type,'koujing'=>$koujing]);

    }

}