hebei-chengben-car/modules/car/controllers/ProbeController.php

<?php

namespace app\modules\car\controllers;

use app\common\controllers\BController;
use app\modules\car\models\FCarInfo;
use app\modules\car\models\FCarScrap;
use app\modules\car\models\FInputPolicy;
use app\modules\car\models\FOneCarOneTable;
use app\modules\car\models\FSpcCar;
use app\modules\car\models\FUnDispatchOrdersCrossBorder;
use app\modules\car\models\FViolationDetails;
use Yii;

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

    public function actionCost()
    {
        $type = Yii::$app->request->get('type', 1);
        $koujing = '这里是口径描述';

        $res = FSpcCar::find()->where(['is_spec'=>1])->asArray()->all();
        $spc_cars = [];
        foreach ($res as $v){
            $spc_cars[] = $v['card_num'];
        }
        $res = FCarScrap::find()->asArray()->all();
        //$spc_cars = [];
        foreach ($res as $v){
            $spc_cars[] = $v['card_num'];
        }

        //燃油费异常
        //按百公里燃油费用排名，前50名且百公里油耗>15升（120元）的车辆为异常。
        if ($type == 1) {

            if (Yii::$app->request->isAjax) {

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

                $where = $this->getCityWhere($params);
                $where['is_special_car'] = 0;

                $_query = FOneCarOneTable::find()->alias('one')->leftJoin('{{%f_car_info}} as ci', 'one.car_id = ci.car_id');
                $_query->where($where);
                //账期
                if(!empty($params['statistical_month'])){
                    $statistical_month = explode('-',$params['statistical_month']);
                    $_where['year_info'] = intval($statistical_month[0]);
                    $_query->andWhere(['and',['=','year_info',intval($statistical_month[0])],['<=','month_info',intval($statistical_month[1])]]);
                    $_where['month_info'] = intval($statistical_month[1]);
                }else{
                    $res = FOneCarOneTable::find()->orderBy('year_info desc,month_info desc' )->asArray()->one();
                    $_where['year_info'] = intval($res['year_info']);
                    $_query->andWhere(['and',['=','year_info',intval($res['year_info'])],['<=','month_info',intval($res['month_info'])]]);
                    $_where['month_info'] = intval($res['month_info']);
                }
                $_query->andWhere('(CASE WHEN ("car_new"=1 OR "car_move"=1) THEN (to_date(to_char(allocation_in_date,\'9999-99\'),\'yyyy-MM\')+INTERVAL\'3 month\')<=to_date(to_char("year_info"*100+"month_info",\'9999-99\'),\'yyyy-MM\') ELSE 1=1 END)');
                $_query->select(['c_card_num'=>'card_num', 'c_city'=>'city',
                    'car_id'=>'max("one"."car_id")',
                    'sum_oil'=>'cast(sum("oil_costs") as decimal(10,2))',
                    'sum_mileage'=>'cast(sum("sum_mileage") as decimal(10,2))',
                    'oil_per_km'=>'cast(coalesce((sum("oil_costs")/nullif(sum("sum_mileage"), 0)::float8*100),0) as decimal(10,2))',
                    'oil_avg_km'=>'cast(coalesce((sum("oil_costs")/nullif(sum("sum_mileage"), 0)::float8*100),0)/coalesce(nullif(avg("fuel_cost_per_l"), 0),8.7) as decimal(10,2))'
                ])->groupBy('card_num,city')->having('coalesce((sum("oil_costs")/nullif(sum("sum_mileage"), 0)::float8*100),0)>120');
                $sql = $_query->createCommand()->getRawSql();


                $_query = FCarInfo::find()->alias('car')->leftJoin("({$sql}) as os", 'car.car_id = os.car_id')->select(['os.*','rank'=>'rank() over(ORDER BY "oil_per_km" DESC)']);
                $_query->where(['car.is_special_car'=>0])->andWhere(['>','os.car_id',0]);
                if(!empty($spc_cars)) $_query->andWhere(['not in','car.card_num',$spc_cars]);
                $sql = $_query->createCommand()->getRawSql();


                $query = FCarInfo::find()->alias('c')->leftJoin("({$sql}) as cs", 'c.car_id = cs.car_id');


                //车牌
                if(!empty($params['card_num'])){
                    $where['c.card_num'] = $params['card_num'];
                }
                if ($params['card_num']!=$params['card_num_text']){
                    unset($where['c.card_num']);
                    $query->where($where)->andWhere(['like','c.card_num',strtoupper($params['card_num_text'])]);
                }else{
                    $query->where($where);
                }


                $query->andWhere(['and',['>','oil_per_km',120],['<=','rank',50]]);



                $countQuery = clone $query;

                $query->select(['c.card_num','c.city','c.dpt_sec','c.grid','c.self_rent','c.car_type','c.using_tag','cs.*']);
                //分页
                if (isset($_GET['limit'])) {
                    $query->limit(intval($_GET['limit']));
                }
                if (isset($_GET['offset'])) {
                    $query->offset(intval($_GET['offset']));
                }

                //排序
                if (isset($_GET['sort']) && isset($_GET['sortOrder'])) {
                    $resultList = $query->orderBy([$_GET['sort'] => ($_GET['sortOrder'] == 'asc' ? SORT_ASC : SORT_DESC)])->asArray()->all();
                } else {
                    $resultList = $query->orderBy(['oil_per_km' => SORT_DESC])->asArray()->all();
                }

                $result = ["total" => $countQuery->count(), "totalNotFiltered" => $countQuery->count(), "rows" => $resultList];
                echo_json($result);
            }
            $this->tableTitle = array(
                //array('field' => 'statistical_month', 'title' => '帐期', 'align' => 'center', 'sortable' => true),
                array('field' => 'card_num', 'title' => '车牌号', 'align' => 'center', 'sortable' => true),
                array('field' => 'city', 'title' => '单位', 'align' => 'center', 'sortable' => true),
                array('field' => 'dpt_sec', 'title' => '二级单位', 'align' => 'center', 'sortable' => true),
                array('field' => 'grid', 'title' => '三级单位', 'align' => 'center', 'sortable' => true),
                array('field' => 'self_rent', 'title' => '车辆来源', 'align' => 'center', 'sortable' => true),
                array('field' => 'car_type', 'title' => '车辆类型', 'align' => 'center', 'sortable' => true),
                array('field' => 'using_tag', 'title' => '车辆使用性质', 'align' => 'center', 'sortable' => true),
                array('field' => 'sum_oil', 'title' => '燃油', 'align' => 'right', 'sortable' => true, 'formatter'=>'numeric1Formatter'),
                array('field' => 'sum_mileage', 'title' => '里程', 'align' => 'right', 'sortable' => true, 'formatter'=>'numeric1Formatter'),
                array('field' => 'oil_per_km', 'title' => '百公里油费', 'align' => 'right', 'sortable' => true, 'formatter'=>'numeric1Formatter'),
                array('field' => 'oil_avg_km', 'title' => '百公里油耗', 'align' => 'right', 'sortable' => true, 'formatter'=>'numeric1Formatter'),
                array('field' => 'rank', 'title' => '排名', 'align' => 'right', 'sortable' => true, 'formatter'=>'numericFormatter'),
            );
            $this->tableConfig = array('table' => "fone_car_one_table", 'url' => $this->createRealUrl(['car/probe/cost', 'type' => $type]), 'setFieldUrl' => $this->createRealUrl(['car/probe/setfield']), 'idField' => 'id', 'checkbox' => 0, 'dropmenu' => 1, 'pagination' => true, 'pagesize' => 20, 'refresh' => true, 'exportFileName' => '燃油费异常', 'height' => 500);
            $koujing = '按百公里燃油费用排名，前50名且百公里油耗>15升（120元）的车辆为异常。';
        }
        //自有车辆维修费异常
        if ($type == 2) {
            if (Yii::$app->request->isAjax) {

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

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

                //$where['is_special_car'] = 0;
                $where['self_rent'] = '自有车辆';

                $_query = FOneCarOneTable::find()->alias('one')->leftJoin('{{%f_car_info}} as ci', 'one.car_id = ci.car_id');
                $_query->where($where);
                //账期
                if(!empty($params['statistical_month'])){
                    $statistical_month = explode('-',$params['statistical_month']);
                    $_where['year_info'] = intval($statistical_month[0]);
                    $_query->andWhere(['and',['=','year_info',intval($statistical_month[0])],['<=','month_info',intval($statistical_month[1])]]);
                    $_where['month_info'] = intval($statistical_month[1]);
                }else{
                    $res = FOneCarOneTable::find()->orderBy('year_info desc,month_info desc' )->asArray()->one();
                    $_where['year_info'] = intval($res['year_info']);
                    $_query->andWhere(['and',['=','year_info',intval($res['year_info'])],['<=','month_info',intval($res['month_info'])]]);
                    $_where['month_info'] = intval($res['month_info']);
                }
                $_query->andWhere('(CASE WHEN ("car_new"=1 OR "car_move"=1) THEN (to_date(to_char(allocation_in_date,\'9999-99\'),\'yyyy-MM\')+INTERVAL\'3 month\')<=to_date(to_char("year_info"*100+"month_info",\'9999-99\'),\'yyyy-MM\') ELSE 1=1 END)');

                $_query->select(['c_card_num'=>'card_num', 'c_city'=>'city',
                    'car_id'=>'max("one"."car_id")',
                    'sum_repair'=>'cast(coalesce(sum("repair_costs"),0) as decimal(10,2))',
                ])->groupBy('card_num,city')->having('coalesce(sum("repair_costs"),0)>5000');
                $sql = $_query->createCommand()->getRawSql();

                $_query = FCarInfo::find()->alias('car')->leftJoin("({$sql}) as os", 'car.car_id = os.car_id')->select(['os.*','rank'=>'rank() over(ORDER BY "sum_repair" DESC)']);
                $_query->where(['car.is_special_car'=>0,'self_rent'=>'自有车辆'])->andWhere(['>','os.car_id',0]);
                if(!empty($spc_cars)) $_query->andWhere(['not in','car.card_num',$spc_cars]);
                $sql = $_query->createCommand()->getRawSql();

                $query = FCarInfo::find()->alias('c')->leftJoin("({$sql}) as cs", 'c.car_id = cs.car_id');


                //车牌
                if(!empty($params['card_num'])){
                    $where['c.card_num'] = $params['card_num'];
                }
                if ($params['card_num']!=$params['card_num_text']){
                    unset($where['c.card_num']);
                    $query->where($where)->andWhere(['like','c.card_num',strtoupper($params['card_num_text'])]);
                }else{
                    $query->where($where);
                }


                $query->andWhere(['and',['>','sum_repair',5000],['<=','rank',50]]);



                $countQuery = clone $query;

                $query->select(['c.card_num','c.city','c.dpt_sec','c.grid','c.self_rent','c.car_type','c.using_tag','c.enabled_date'
                    ,'cs.*','car_age'=>'age(CURRENT_DATE, c.enabled_date::date)']);
                //分页
                if (isset($_GET['limit'])) {
                    $query->limit(intval($_GET['limit']));
                }
                if (isset($_GET['offset'])) {
                    $query->offset(intval($_GET['offset']));
                }

                //排序
                if (isset($_GET['sort']) && isset($_GET['sortOrder'])) {
                    $resultList = $query->orderBy([$_GET['sort'] => ($_GET['sortOrder'] == 'asc' ? SORT_ASC : SORT_DESC)])->asArray()->all();
                } else {
                    $resultList = $query->orderBy(['sum_repair' => SORT_DESC])->asArray()->all();
                }

                foreach ($resultList as &$v){
                    $v['car_age'] = str_replace(['years','mons','days','year','mon','day',' '],['年','月','天','年','月','天',''],$v['car_age']);
                }

                $result = ["total" => $countQuery->count(), "totalNotFiltered" => $countQuery->count(), "rows" => $resultList];
                echo_json($result);
            }
            $this->tableTitle = array(
                //array('field' => 'statistical_month', 'title' => '帐期', 'align' => 'center', 'sortable' => true),
                array('field' => 'card_num', 'title' => '车牌号', 'align' => 'center', 'sortable' => true),
                array('field' => 'city', 'title' => '单位', 'align' => 'center', 'sortable' => true),
                array('field' => 'dpt_sec', 'title' => '二级单位', 'align' => 'center', 'sortable' => true),
                array('field' => 'grid', 'title' => '三级单位', 'align' => 'center', 'sortable' => true),
                array('field' => 'self_rent', 'title' => '车辆来源', 'align' => 'center', 'sortable' => true),
                array('field' => 'car_type', 'title' => '车辆类型', 'align' => 'center', 'sortable' => true),
                array('field' => 'using_tag', 'title' => '车辆使用性质', 'align' => 'center', 'sortable' => true),
                array('field' => 'car_age', 'title' => '车龄', 'align' => 'right', 'sortable' => true),
                array('field' => 'enabled_date', 'title' => '登记日期', 'align' => 'center', 'sortable' => true),
                array('field' => 'sum_repair', 'title' => '累计维修费（元）', 'align' => 'right', 'sortable' => true, 'formatter'=>'numeric1Formatter'),
                array('field' => 'rank', 'title' => '排名', 'align' => 'right', 'sortable' => true, 'formatter'=>'numericFormatter'),
            );
            $this->tableConfig = array('table' => "fone_car_one_table", 'url' => $this->createRealUrl(['car/probe/cost', 'type' => $type]), 'setFieldUrl' => $this->createRealUrl(['car/probe/setfield']), 'idField' => FOneCarOneTable::modelPrimaryKey(), 'checkbox' => 0, 'dropmenu' => 1, 'pagination' => true, 'pagesize' => 20, 'refresh' => true, 'exportFileName' => '自有车辆维修费异常', 'height' => 500);
            $koujing = '按单车维修费排名，前50名且年累计维修费>5000元的车辆为异常。';

        }

        //租赁车辆维修费异常
        if ($type == 3) {
            if (Yii::$app->request->isAjax) {

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

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

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

                $_query = FOneCarOneTable::find()->alias('one')->leftJoin('{{%f_car_info}} as ci', 'one.car_id = ci.car_id');
                $_query->where($where);
                //账期
                if(!empty($params['statistical_month'])){
                    $statistical_month = explode('-',$params['statistical_month']);
                    $_where['year_info'] = intval($statistical_month[0]);
                    $_query->andWhere(['and',['=','year_info',intval($statistical_month[0])],['<=','month_info',intval($statistical_month[1])]]);
                    $_where['month_info'] = intval($statistical_month[1]);
                }else{
                    $res = FOneCarOneTable::find()->orderBy('year_info desc,month_info desc' )->asArray()->one();
                    $_where['year_info'] = intval($res['year_info']);
                    $_query->andWhere(['and',['=','year_info',intval($res['year_info'])],['<=','month_info',intval($res['month_info'])]]);
                    $_where['month_info'] = intval($res['month_info']);
                }
                $_query->andWhere('(CASE WHEN ("car_new"=1 OR "car_move"=1) THEN (to_date(to_char(allocation_in_date,\'9999-99\'),\'yyyy-MM\')+INTERVAL\'3 month\')<=to_date(to_char("year_info"*100+"month_info",\'9999-99\'),\'yyyy-MM\') ELSE 1=1 END)');

                $_query->select(['c_card_num'=>'card_num', 'c_city'=>'city',
                    'car_id'=>'max("one"."car_id")',
                    'sum_repair'=>'cast(coalesce(sum("repair_costs"),0) as decimal(10,2))',
                ])->groupBy('card_num,city')->having('coalesce(sum("repair_costs"),0)>2000');
                $sql = $_query->createCommand()->getRawSql();

                $_query = FCarInfo::find()->alias('car')->leftJoin("({$sql}) as os", 'car.car_id = os.car_id')->select(['os.*','rank'=>'rank() over(ORDER BY "sum_repair" DESC)']);
                $_query->where(['car.is_special_car'=>0,'self_rent'=>'租赁车辆'])->andWhere(['>','os.car_id',0]);
                if(!empty($spc_cars)) $_query->andWhere(['not in','car.card_num',$spc_cars]);
                $sql = $_query->createCommand()->getRawSql();

                $query = FCarInfo::find()->alias('c')->leftJoin("({$sql}) as cs", 'c.car_id = cs.car_id');


                //车牌
                if(!empty($params['card_num'])){
                    $where['c.card_num'] = $params['card_num'];
                }
                if ($params['card_num']!=$params['card_num_text']){
                    unset($where['c.card_num']);
                    $query->where($where)->andWhere(['like','c.card_num',strtoupper($params['card_num_text'])]);
                }else{
                    $query->where($where);
                }


                $query->andWhere(['and',['>','sum_repair',2000]]);



                $countQuery = clone $query;

                $query->select(['c.card_num','c.city','c.dpt_sec','c.grid','c.self_rent','c.car_type','c.using_tag','c.enabled_date'
                    ,'cs.*','car_age'=>'age(CURRENT_DATE, c.enabled_date::date)']);
                //分页
                if (isset($_GET['limit'])) {
                    $query->limit(intval($_GET['limit']));
                }
                if (isset($_GET['offset'])) {
                    $query->offset(intval($_GET['offset']));
                }

                //排序
                if (isset($_GET['sort']) && isset($_GET['sortOrder'])) {
                    $resultList = $query->orderBy([$_GET['sort'] => ($_GET['sortOrder'] == 'asc' ? SORT_ASC : SORT_DESC)])->asArray()->all();
                } else {
                    $resultList = $query->orderBy(['sum_repair' => SORT_DESC])->asArray()->all();
                }

                foreach ($resultList as &$v){
                    $v['car_age'] = str_replace(['years','mons','days','year','mon','day',' '],['年','月','天','年','月','天',''],$v['car_age']);
                }

                $result = ["total" => $countQuery->count(), "totalNotFiltered" => $countQuery->count(), "rows" => $resultList];
                echo_json($result);
            }
            $this->tableTitle = array(
                //array('field' => 'statistical_month', 'title' => '帐期', 'align' => 'center', 'sortable' => true),
                array('field' => 'card_num', 'title' => '车牌号', 'align' => 'center', 'sortable' => true),
                array('field' => 'city', 'title' => '单位', 'align' => 'center', 'sortable' => true),
                array('field' => 'dpt_sec', 'title' => '二级单位', 'align' => 'center', 'sortable' => true),
                array('field' => 'grid', 'title' => '三级单位', 'align' => 'center', 'sortable' => true),
                array('field' => 'self_rent', 'title' => '车辆来源', 'align' => 'center', 'sortable' => true),
                array('field' => 'car_type', 'title' => '车辆类型', 'align' => 'center', 'sortable' => true),
                array('field' => 'using_tag', 'title' => '车辆使用性质', 'align' => 'center', 'sortable' => true),
                array('field' => 'car_age', 'title' => '车龄', 'align' => 'right', 'sortable' => true),
                array('field' => 'enabled_date', 'title' => '登记日期', 'align' => 'center', 'sortable' => true),
                array('field' => 'sum_repair', 'title' => '累计维修费（元）', 'align' => 'right', 'sortable' => true, 'formatter'=>'numeric1Formatter'),
                array('field' => 'rank', 'title' => '排名', 'align' => 'right', 'sortable' => true, 'formatter'=>'numericFormatter'),
            );
            $this->tableConfig = array('table' => "fone_car_one_table", 'url' => $this->createRealUrl(['car/probe/cost', 'type' => $type]), 'setFieldUrl' => $this->createRealUrl(['car/probe/setfield']), 'idField' => FOneCarOneTable::modelPrimaryKey(), 'checkbox' => 0, 'dropmenu' => 1, 'pagination' => true, 'pagesize' => 20, 'refresh' => true, 'exportFileName' => '租赁车辆维修费异常', 'height' => 500);
            $koujing = '租赁车辆年累计维修费超过2000元为异常。根据车辆租赁合同，租赁车辆正常的维修、保养由供方负责，人为造成的非正常损失由使用方负责，主要是轮胎非正常损坏。对租赁车辆产生维修费的情况进行监控，防止套取维修费。';

        }


        return $this->render('cost', array('type' => $type, 'koujing' => $koujing));
    }


    public function actionEfficiency()
    {
        $type = Yii::$app->request->get('type', 1);

        if ($type == 1) {
            if (Yii::$app->request->isAjax) {

                $res = FSpcCar::find()->where(['is_spec'=>1])->asArray()->all();
                $spc_cars = [];
                foreach ($res as $v){
                    $spc_cars[] = $v['card_num'];
                }
                $res = FCarScrap::find()->asArray()->all();
                //$spc_cars = [];
                foreach ($res as $v){
                    $spc_cars[] = $v['card_num'];
                }

                $policy = FInputPolicy::find()->where(['is_active'=>1])->asArray()->one();

                if(empty($policy)){
                    $policy = ['thd_km_per_day'=>20,'thd_att'=>50];
                }

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

                $where = $this->getCityWhere($params);
                //$where['is_special_car'] = 0;

                $_query = FOneCarOneTable::find()->alias('one')->leftJoin('{{%f_car_info}} as ci', 'one.car_id = ci.car_id');
                $_query->where($where);
                //账期
                if(!empty($params['statistical_month'])){
                    $statistical_month = explode('-',$params['statistical_month']);
                    $_where['year_info'] = intval($statistical_month[0]);
                    $_query->andWhere(['and',['=','year_info',intval($statistical_month[0])],['<=','month_info',intval($statistical_month[1])]]);
                    $_where['month_info'] = intval($statistical_month[1]);
                }else{
                    $res = FOneCarOneTable::find()->orderBy('year_info desc,month_info desc' )->asArray()->one();
                    $_where['year_info'] = intval($res['year_info']);
                    $_query->andWhere(['and',['=','year_info',intval($res['year_info'])],['<=','month_info',intval($res['month_info'])]]);
                    $_where['month_info'] = intval($res['month_info']);
                }

                $res = FCarInfo::find()->where(['car_move'=>1])->andWhere('(to_date(to_char(allocation_in_date,\'9999-99\'),\'yyyy\'))=to_date(to_char('.(intval($_where['year_info'])*100).',\'9999-99\'),\'yyyy\')')->asArray()->all();
                $car_ids = [];
                foreach ($res as $v){
                    $_res = FCarInfo::find()->where(['and',['=','card_num',$v['card_num']],['<','car_id',$v['car_id']],['<>','city',$v['city']]])->asArray()->all();
                    foreach ($_res as $_v){
                        $car_ids[] = $_v['car_id'];
                    }
                }

                $_query->andWhere('(CASE WHEN ("car_new"=1 OR "car_move"=1) THEN (to_date(to_char(allocation_in_date,\'9999-99\'),\'yyyy-MM\')+INTERVAL\'3 month\')<=to_date(to_char("year_info"*100+"month_info",\'9999-99\'),\'yyyy-MM\') ELSE 1=1 END)');
                $_query->select(['c_card_num'=>'card_num', 'c_city'=>'city',
                    'car_id'=>'max("one"."car_id")',
                    'sum_attend_days'=>'cast(sum("attend_days") as decimal(10,2))',
                    'sum_work_days'=>'cast(sum("work_days") as decimal(10,2))',
                    'sum_mileage'=>'cast(sum("sum_mileage") as decimal(10,2))',
                    'avg_mileage'=>'cast(coalesce((sum("sum_mileage")/nullif(sum("work_days"), 0)::float8),0) as decimal(10,2))',
                    'attendance'=>'cast(coalesce((sum("attend_days")/nullif(sum("work_days"), 0)::float8*100),0) as decimal(10,2))'
                ])->groupBy('card_num,city')
                ->having('coalesce((sum("sum_mileage")/nullif(sum("work_days"), 0)::float8),0)<'.$policy['thd_km_per_day'].' AND coalesce((sum("attend_days")/nullif(sum("work_days"), 0)::float8*100),0)<'.$policy['thd_att']);
                $sql = $_query->createCommand()->getRawSql();


                $_query = FCarInfo::find()->alias('car')->leftJoin("({$sql}) as os", 'car.car_id = os.car_id')->select(['os.*','rank'=>'rank() over(ORDER BY "avg_mileage", "attendance")']);
                $_query->where(['car.is_special_car'=>0])->andWhere(['>','os.car_id',0]);
                if(!empty($spc_cars)){
                    $_query->andWhere(['not in','car.card_num',$spc_cars]);
                }
                if(!empty($car_ids)){
                    $_query->andWhere(['not in','car.car_id',$car_ids]);
                }

                $sql = $_query->createCommand()->getRawSql();


                $query = FCarInfo::find()->alias('c')->leftJoin("({$sql}) as cs", 'c.car_id = cs.car_id');


                //车牌
                if(!empty($params['card_num'])){
                    $where['c.card_num'] = $params['card_num'];
                }
                if ($params['card_num']!=$params['card_num_text']){
                    unset($where['c.card_num']);
                    $query->where($where)->andWhere(['like','c.card_num',strtoupper($params['card_num_text'])]);
                }else{
                    $query->where($where);
                }



                $query->andWhere(['and',['<','avg_mileage',$policy['thd_km_per_day']],['<','attendance',$policy['thd_att']]]);



                $countQuery = clone $query;

                $query->select(['c.card_num','c.city','c.dpt_sec','c.grid','c.self_rent','c.car_type','c.using_tag','cs.*']);
                //分页
                if (isset($_GET['limit'])) {
                    $query->limit(intval($_GET['limit']));
                }
                if (isset($_GET['offset'])) {
                    $query->offset(intval($_GET['offset']));
                }

                //排序
                if (isset($_GET['sort']) && isset($_GET['sortOrder'])) {
                    $resultList = $query->orderBy([$_GET['sort'] => ($_GET['sortOrder'] == 'asc' ? SORT_ASC : SORT_DESC)])->asArray()->all();
                } else {
                    $resultList = $query->orderBy(["avg_mileage" => SORT_ASC, "attendance" => SORT_ASC])->asArray()->all();
                }

                $result = ["total" => $countQuery->count(), "totalNotFiltered" => $countQuery->count(), "rows" => $resultList];
                echo_json($result);
            }
            $this->tableTitle = array(
                //array('field' => 'statistical_month', 'title' => '帐期', 'align' => 'center', 'sortable' => true),
                array('field' => 'card_num', 'title' => '车牌号', 'align' => 'center', 'sortable' => true),
                array('field' => 'city', 'title' => '单位', 'align' => 'center', 'sortable' => true),
                array('field' => 'dpt_sec', 'title' => '二级单位', 'align' => 'center', 'sortable' => true),
                array('field' => 'grid', 'title' => '三级单位', 'align' => 'center', 'sortable' => true),
                array('field' => 'self_rent', 'title' => '车辆来源', 'align' => 'center', 'sortable' => true),
                array('field' => 'car_type', 'title' => '车辆类型', 'align' => 'center', 'sortable' => true),
                array('field' => 'using_tag', 'title' => '车辆使用性质', 'align' => 'center', 'sortable' => true),
                array('field' => 'avg_mileage', 'title' => '日均里程（公里）', 'align' => 'right', 'sortable' => true, 'formatter'=>'numeric1Formatter'),
                array('field' => 'attendance', 'title' => '出勤率（%）', 'align' => 'right', 'sortable' => true, 'formatter'=>'numeric1Formatter'),
                array('field' => 'sum_mileage', 'title' => '总里程（公里）', 'align' => 'right', 'sortable' => true, 'formatter'=>'numeric1Formatter'),
                array('field' => 'sum_attend_days', 'title' => '行驶天数', 'align' => 'right', 'sortable' => true, 'formatter'=>'numericFormatter'),
                array('field' => 'sum_work_days', 'title' => '工作日天数', 'align' => 'right', 'sortable' => true, 'formatter'=>'numericFormatter'),
            );
            $this->tableConfig = array('table' => "fone_car_one_table", 'url' => $this->createRealUrl(['car/probe/efficiency', 'type' => $type]), 'setFieldUrl' => $this->createRealUrl(['car/probe/setfield']), 'idField' => 'statistical_month', 'checkbox' => 0, 'dropmenu' => 1, 'pagination' => true, 'pagesize' => 20, 'refresh' => true, 'exportFileName' => '车辆效能探针', 'height' => 500);


        }

        $koujing = '低效车辆指，以年累计里程、年出勤天数计算，日均行驶里程<20公里且出勤率<50%的车辆。出现低效车辆，发管理工单，预警到县分领导。市分低效车辆占比超过5%，发管理工单，预警到市分办公室主任，超过10%，预警升级，预警到市分主管领导。';
        return $this->render('efficiency', array('type' => $type, 'koujing' => $koujing));
    }

    public function actionDriving()
    {
        $type = Yii::$app->request->get('type', 1);
        $koujing = '';
        if ($type == 1) {
            if (Yii::$app->request->isAjax) {
                $query = FUnDispatchOrdersCrossBorder::find();
                $params = Yii::$app->request->get();

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

                //车牌
                if(!empty($params['card_num'])){
                    $where['card_num'] = $params['card_num'];
                }
                if ($params['card_num']!=$params['card_num_text']){
                    unset($where['card_num']);
                    $query->where($where)->andWhere(['like','card_num',strtoupper($params['card_num_text'])]);
                }else{
                    $query->where($where);
                }

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

                //排序
                if (isset($_GET['sort']) && isset($_GET['sortOrder'])) {
                    $resultList = $query->select($field)->orderBy([$_GET['sort'] => ($_GET['sortOrder'] == 'asc' ? SORT_ASC : SORT_DESC)])->asArray()->all();
                } else {
                    $resultList = $query->select($field)->orderBy(['statistical_month' => SORT_DESC])->asArray()->all();
                }


                $result = ["total" => $countQuery->count(), "totalNotFiltered" => $countQuery->count(), "rows" => $resultList];
                echo_json($result);
            }
            $this->tableTitle = array(
                array('field' => 'statistical_month', 'title' => '帐期', 'align' => 'center', 'sortable' => true),
                array('field' => 'cross_border_t', 'title' => '越界时间', 'align' => 'center', 'sortable' => true),
                array('field' => 'card_num', 'title' => '车牌号', 'align' => 'center', 'sortable' => true),
                array('field' => 'city', 'title' => '单位', 'align' => 'center', 'sortable' => true),
                array('field' => 'dpt_sec', 'title' => '二级单位', 'align' => 'center', 'sortable' => true),
                array('field' => 'grid', 'title' => '三级单位', 'align' => 'center', 'sortable' => true),
                array('field' => 'alarm_area_des', 'title' => '报警区域描述', 'align' => 'center', 'sortable' => true),
                array('field' => 'alarm_rule_des', 'title' => '报警规则描述', 'align' => 'center', 'sortable' => true),
                array('field' => 'is_working', 'title' => '是否执行中越界', 'align' => 'center', 'sortable' => true),
                array('field' => 'car_type', 'title' => '车辆类型', 'align' => 'center', 'sortable' => true),
                array('field' => 'using_tag', 'title' => '车辆使用性质', 'align' => 'center', 'sortable' => true),
            );
            $this->tableConfig = array('table' => FUnDispatchOrdersCrossBorder::shortTableName(), 'url' => $this->createRealUrl(['car/probe/driving', 'type' => $type]), 'setFieldUrl' => $this->createRealUrl(['car/probe/setfield']), 'idField' => FUnDispatchOrdersCrossBorder::modelPrimaryKey(), 'checkbox' => 0, 'dropmenu' => 1, 'pagination' => true, 'pagesize' => 20, 'refresh' => true, 'exportFileName' => '无派单越界', 'height' => 500);
            $koujing = "1、评价规则：

我公司车辆依托“睿行”平台管理，省、市、县3级车辆分别设置电子围栏，在电子围栏外行驶且未在“睿行”平台派单的车辆为违规车辆。

2、工单配置：

策略推送频率为月度，向车辆所属市分公司办公室主任发送管理工单，根据“睿行”平台实施细则，所属市分公司要在本地网内以正式文件通报，累计3次，扣罚司机绩效。

 ‘ 0’表示取到数字，值为0，‘-’表示从数据库没有取到数字，‘空’表示没有去数据库取数字";

        }

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

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

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


                //车牌
                if(!empty($params['card_num'])){
                    $where['card_num'] = $params['card_num'];
                }
                if ($params['card_num']!=$params['card_num_text']){
                    unset($where['card_num']);
                    $query->where($where)->andWhere(['like','card_num',strtoupper($params['card_num_text'])]);
                }else{
                    $query->where($where);
                }

                $query->where($where)->andWhere(['and',['like','offline_actual_processing_status','未处理'],['>','unprocessed_duration_of_violation',180]]);

                $countQuery = clone $query;

                $query->select(['statistical_month','card_num','city','dpt_sec','grid','violation_time'=>'"violation_time"::timestamp','unprocessed_duration_of_violation',
                    'violation_location','violation_details','deduction_points','fine','offline_actual_processing_status','row_num'=>'ROW_NUMBER() OVER()']);
                //分页
                if (isset($_GET['limit'])) {
                    $query->limit(intval($_GET['limit']));
                }
                if (isset($_GET['offset'])) {
                    $query->offset(intval($_GET['offset']));
                }

                //排序
                if (isset($_GET['sort']) && isset($_GET['sortOrder'])) {
                    $resultList = $query->orderBy([$_GET['sort'] => ($_GET['sortOrder'] == 'asc' ? SORT_ASC : SORT_DESC)])->asArray()->all();
                } else {
                    $resultList = $query->orderBy(['statistical_month' => SORT_DESC])->asArray()->all();
                }

                $result = ["total" => $countQuery->count(), "totalNotFiltered" => $countQuery->count(), "rows" => $resultList];
                echo_json($result);
            }
            $this->tableTitle = array(
                array('field' => 'row_num', 'title' => '序号', 'align' => 'center', 'sortable' => true),
                array('field' => 'statistical_month', 'title' => '账期', 'align' => 'center', 'sortable' => true),
                array('field' => 'card_num', 'title' => '车牌号', 'align' => 'center', 'sortable' => true),
                array('field' => 'city', 'title' => '单位', 'align' => 'center', 'sortable' => true),
                array('field' => 'dpt_sec', 'title' => '二级单位', 'align' => 'center', 'sortable' => true),
                array('field' => 'grid', 'title' => '三级单位', 'align' => 'center', 'sortable' => true),
                array('field' => 'violation_time', 'title' => '违章时间', 'align' => 'center', 'sortable' => true),
                array('field' => 'unprocessed_duration_of_violation', 'title' => '违章未处理时长（天）', 'align' => 'center', 'sortable' => true, 'formatter'=>'numericFormatter'),
                array('field' => 'violation_location', 'title' => '违章地点', 'align' => 'center', 'sortable' => true),
                array('field' => 'violation_details', 'title' => '违章详情', 'align' => 'center', 'sortable' => true, 'width' => '200px'),
                array('field' => 'deduction_points', 'title' => '扣分', 'align' => 'center', 'sortable' => true, 'formatter'=>'numericFormatter'),
                array('field' => 'fine', 'title' => '罚款', 'align' => 'right', 'sortable' => true, 'formatter'=>'numeric1Formatter'),
                array('field' => 'offline_actual_processing_status', 'title' => '三方处理状态', 'align' => 'center', 'sortable' => true),
            );
            $this->tableConfig = array('table' => FViolationDetails::shortTableName(), 'url' => $this->createRealUrl(['car/probe/driving', 'type' => $type]), 'setFieldUrl' => $this->createRealUrl(['car/probe/setfield']), 'idField' => 'car_id', 'checkbox' => 0, 'dropmenu' => 1, 'pagination' => true, 'pagesize' => 20,  'refresh' => true, 'exportFileName' => '违章长期未处理', 'height' => 500);
            $koujing = '存在交通违章，6个月以上未处理的车辆为异常。（加强处理后，时间会调整，如5个月。）';
        }


        return $this->render('driving', array('type' => $type, 'koujing' => $koujing));
    }
}