ZZCX_yii/backend/modules/api/models/BusOrder.php

<?php

namespace backend\modules\api\models;

use function GuzzleHttp\default_ca_bundle;
use yii\db\ActiveRecord;
use Yii;

/**
 * This is the model class for table "bus_order".
 *
 * @property integer $id
 * @property integer $bus_number
 * @property integer $create_user_id
 * @property string $create_time
 * @property integer $update_user_id
 * @property string $update_time
 * @property integer $cancel_flag
 * @property integer $use_bus_org_id
 * @property string $run_date
 * @property integer $run_bus_id
 * @property integer $run_id
 * @property integer $line_id
 * @property string $start_time
 * @property integer $bus_order_id
 * @property integer $bus_type_res_id
 * @property integer $seat_count
 * @property integer $saled_count
 * @property integer $itinerary_id
 * @property string $itinerary_name
 * @property integer $line_type
 * @property integer $day_num
 * @property integer $bus_cost_type
 * @property string $bus_cost
 * @property string $remark
 * @property string $send_msg
 * @property integer $send_type
 * @property string $send_cost
 * @property integer $send_bus_res_id
 * @property integer $send_bus_driver_res_id
 * @property integer $send_status
 * @property integer $run_status
 * @property integer $finance_status
 * @property integer $end_status
 * @property integer $line_status
 * @property integer $safe
 *
 * @property BaseBus $sendBusRes
 * @property BaseDriver $sendBusDriverRes
 */
class BusOrder extends ActiveRecord
{
    /**
     * @inheritdoc
     */
    public static function tableName()
    {
        return 'bus_order';
    }

    /**
     * @inheritdoc
     */
    public function rules()
    {
        return [
            [['bus_number', 'create_user_id', 'update_user_id', 'run_date', 'seat_count', 'saled_count', 'send_type'], 'required'],
            [['bus_number', 'create_user_id', 'update_user_id', 'cancel_flag', 'use_bus_org_id', 'run_bus_id', 'run_id', 'line_id', 'bus_order_id', 'bus_type_res_id', 'seat_count', 'saled_count', 'itinerary_id', 'line_type', 'day_num', 'bus_cost_type', 'send_type', 'send_bus_res_id', 'send_bus_driver_res_id', 'send_status', 'run_status', 'finance_status', 'end_status', 'line_status', 'safe'], 'integer'],
            [['update_time'], 'safe'],
            [['bus_cost', 'send_cost'], 'number'],
            [['create_time'], 'string', 'max' => 20],
            [['run_date'], 'string', 'max' => 10],
            [['start_time'], 'string', 'max' => 128],
            [['itinerary_name'], 'string', 'max' => 50],
            [['remark'], 'string', 'max' => 100],
            [['send_msg'], 'string', 'max' => 1024],
            [['send_bus_res_id'], 'exist', 'skipOnError' => true, 'targetClass' => BaseBus::className(), 'targetAttribute' => ['send_bus_res_id' => 'BUS_ID']],
            [['send_bus_driver_res_id'], 'exist', 'skipOnError' => true, 'targetClass' => BaseDriver::className(), 'targetAttribute' => ['send_bus_driver_res_id' => 'DRIVER_ID']],
        ];
    }

    /**
     * @inheritdoc
     */
    public function attributeLabels()
    {
        return [
            'id' => '主键，自增',
            'bus_number' => '出车单号',
            'create_user_id' => 'Create User ID',
            'create_time' => 'Create Time',
            'update_user_id' => 'Update User ID',
            'update_time' => '记录最后更新时间',
            'cancel_flag' => 'Cancel Flag',
            'use_bus_org_id' => '用车单位',
            'run_date' => '用车日期',
            'run_bus_id' => '车次ID，对应run_bus中的主键ID,用于派车时更新巴士系统车次库存',
            'run_id' => '班次ID，供同步已有班次使用',
            'line_id' => '线路ID，供同步数据使用',
            'start_time' => 'Start Time',
            'bus_order_id' => '车次号，供同步已有车次使用',
            'bus_type_res_id' => '用车类型',
            'seat_count' => '车座数',
            'saled_count' => '乘客人数',
            'itinerary_id' => '行程ID',
            'itinerary_name' => '行程名称',
            'line_type' => '行程类型，市内或省际 1为省际，2为市内',
            'day_num' => '行程包含的行程天数',
            'bus_cost_type' => '车辆计价方式 477为包车计价，478为人数计价',
            'bus_cost' => '车辆计价金额',
            'remark' => '用车备注',
            'send_msg' => '调度原因等调度时候的信息',
            'send_type' => '内部派车还是外部派车 0：内部 1：外部',
            'send_cost' => '调派车辆时的价格',
            'send_bus_res_id' => '调派车辆res_id',
            'send_bus_driver_res_id' => '调派司机res_id',
            'send_status' => '调度状态',
            'run_status' => '出车状态',
            'finance_status' => '报账状态',
            'end_status' => '结算状态',
            'line_status' => '线路牌  0为未亮起 1为亮起',
            'safe' => '安检  0为未安检 1为已安检',
        ];
    }

    /**
     * @return \yii\db\ActiveQuery
     */
    public function getSendBusRes()
    {
        return $this->hasOne(BaseBus::className(), ['BUS_ID' => 'send_bus_res_id']);
    }

    /**
     * @return \yii\db\ActiveQuery
     */
    public function getSendBusDriverRes()
    {
        return $this->hasOne(BaseDriver::className(), ['DRIVER_ID' => 'send_bus_driver_res_id']);
    }

    /**
     * Function Description:获取任务列表
     * Function Name: getDriverTask
     * @param int $driver_id 司机id
     * @param string $run_date 日期
     *
     * @return array|\yii\db\ActiveRecord[]
     *
     * @author 张帅
     */
    public function getDriverTask($driver_id, $run_date)
    {
        #region 1.获取列表详情
        $task_list = self::find()
            ->select([
                'bo.contacts',//联系人姓名
                'bo.tels',//联系人号码
                'bo.run_bus_id',//车次id
                'bo.bus_number',//出车单号
                'bo.run_id',//班次id
                'bo.line_id',//线路id
                'run_date',//用车日期
                'run_status_id' => 'bo.run_status',
                'run_status' => DictType::find()->select('type_name')->where('id = bo.run_status')->limit(1),//班次状态
                'finance_status_id' => 'bo.finance_status',
                'finance_status' => DictType::find()->select('type_name')->where('id = bo.finance_status')->limit(1),//报账状态
                'bo.bus_order_id',//车次号
                'saled_count_bo'=>'bo.saled_count',//bus_order已售座位数
                'saled_count_rb'=>'rb.saled_count',//run_bus已售座位数
                'send_bus_no' => BaseBus::find()->select('bus_no')->where('bus_id = bo.send_bus_res_id'),//车牌号
                'itinerary_id',//行程id
                'start_time' => "str_to_date(start_time,'%H:%i')",
            ])
            ->from(self::tableName() . ' as bo')
            ->leftJoin('run_bus as rb', 'bo.run_bus_id = rb.id')
            ->where([
                'and',
                ['=', 'bo.cancel_flag', 0],
                ['=', 'bo.send_bus_driver_res_id', $driver_id],
                ['=', 'bo.run_date', $run_date],
            ])
            ->orderBy(['start_time' => SORT_ASC])
            ->asArray()->all();
        #endregion

        #region 2.获取上下站点及时间
        //行程id数组
        $itinerary_id = [];
        foreach ($task_list as $key => $vel) {
            $itinerary_id[] = $vel['itinerary_id'];
            if($vel['run_id'] != 0){    //run_id不为0时从run_bus里面取
                $task_list[$key]['saled_count'] = $vel['saled_count_rb'];
            }else{
                $task_list[$key]['saled_count'] = $vel['saled_count_bo'];
            }
        }
        //行程列表
        $itinerary_list = BusItinerary::find()
            ->select('itinerary_id,station_name,day_seq_id,start_time')
            ->where([
                'and',
                ['in', 'itinerary_id', $itinerary_id],
                ['=', 'cancel_flag', 0],
            ])
            ->orderBy(['itinerary_id' => SORT_ASC, 'station_seq_id' => SORT_ASC])
            ->asArray()->all();
        //分车次行程
        $itinerary_bus_list = [];
        foreach ($itinerary_list as $key => $vel) {
            if (!isset($itinerary_bus_list[$vel['itinerary_id']])) {
                $itinerary_bus_list[$vel['itinerary_id']]['start_station'] = $vel['station_name'];
                $itinerary_bus_list[$vel['itinerary_id']]['run_time'] = $vel['start_time'];
            } else {
                $itinerary_bus_list[$vel['itinerary_id']]['end_station'] = $vel['station_name'];
                if( $vel["day_seq_id"] == 1 ) {
                    $itinerary_bus_list[$vel['itinerary_id']]['arrive_time'] = $vel['start_time'];
                } else {
                    $start_date_disp = "";
                    switch( $vel["day_seq_id"] ) {
                        case 2:
                            $start_date_disp = "次日";
                            break;
                        case 3:
                            $start_date_disp = "第三日";
                            break;
                        case 4:
                            $start_date_disp = "第四日";
                            break;
                        default:
                            $start_date_disp = "第{$vel["day_seq_id"]}日";
                            break;
                    }
                    $itinerary_bus_list[$vel['itinerary_id']]['arrive_time'] = $start_date_disp." ".$vel['start_time'];
                }
            }
        }
        #endregion

        #region 3.整理任务列表
        foreach ($task_list as $key => $vel) {
            if( is_array($vel) && isset($itinerary_bus_list[$vel['itinerary_id']]) ) {
                $task_list[$key] = array_merge($vel, $itinerary_bus_list[$vel['itinerary_id']]);
            } else {
                $task_list[$key]['start_station'] = '未设置';
                $task_list[$key]['run_time'] = '未设置';
                $task_list[$key]['end_station'] = '未设置';
                $task_list[$key]['arrive_time'] = '未设置';
            }
        }
        #endregion

        return $task_list;
    }

    /**
     * Function Description:获取车次详情
     * Function Name: getBusInfo
     * @param int $bus_number 出车单号
     *
     * @return array|bool|null|ActiveRecord
     *
     * @author 张帅
     */
    public function getBusInfo($bus_number)
    {
        $result = self::find()
            ->select([
                'bo.run_id',//班次id
                'bo.bus_order_id',//车次id
                'bo.bus_number',//出车单号
                'bus_no' => BaseBus::find()->select('bus_no')->where('bus_id = bo.send_bus_res_id'),//车牌号
                'bo.send_bus_res_id',
                'bo.run_date',
                'bo.send_bus_driver_res_id',
                'bo.itinerary_id',
                'order_seat_count' => 'bo.seat_count',//总座位数
                'order_saled_count' => 'bo.saled_count',//售出的座位数
                'bus_seat_count' => 'rb.seat_count',//总座位数
                'bus_saled_count' => 'rb.saled_count',//售出的座位数
                'run_status_id' => 'bo.run_status',
                'bus_status' => DictType::find()->select('type_name')->where('id = bo.run_status')->limit(1),//班次状态
                'finance_status_id' => 'bo.finance_status',
                'finance_status' => DictType::find()->select('type_name')->where('id = bo.finance_status')->limit(1),//报账状态
            ])
            ->from(self::tableName() . ' as bo')
            ->leftJoin('run_bus as rb', 'bo.run_bus_id = rb.id')
            ->where([
                'and',
                ['=', 'bo.cancel_flag', 0],
                ['=', 'bo.bus_number', $bus_number],
            ])
            ->asArray()->one();

        if (count($result) == 0) {
            return false;
        }

        //找到司机当天所有的可以开启的行程，并且正序排序
        $is_line_now = self::find()
            ->select(['bus_number','start_time'])
            ->where(['and',['=','cancel_flag',0],['=','run_date',$result['run_date']],['=','send_bus_driver_res_id',$result['send_bus_driver_res_id']],['or',['=','run_status',435],['=','run_status',436]]])
            ->orderBy(['start_time'=>SORT_ASC])
            ->asArray()->all();
        if($is_line_now){     //如果当天有结果并且当前的就是司机进入的，就返回true
            if($is_line_now[0]['bus_number'] == $bus_number){
                $result['is_right'] = true;
            }else{
                $result['is_right'] = false;
            }
            $result['right_task_time'] = $is_line_now[0]['start_time'];
        }
        if (empty($result['bus_seat_count']) && empty($result['bus_saled_count'])) {
            $result['seat_count'] = $result['order_seat_count'];
            $result['saled_count'] = $result['order_saled_count'];
        } else {
            $result['seat_count'] = $result['bus_seat_count'];
            $result['saled_count'] = $result['bus_saled_count'];
        }

        return $result;
    }

    /**
     * Function Description:获取出车详情
     * Function Name: getRunBusStationOrderInfo
     * @param $bus_info
     * @param $station_info
     * @param $order_info
     *
     * @return mixed
     *
     * @author 张帅
     */
    public function getRunBusStationOrderInfo($bus_info, $station_info, $order_info)
    {
        #region 1.获取车次信息
        $start = reset($station_info);
        $bus_info['start_station'] = $start['station_name'];
        $bus_info['start_time'] = $start['start_time'];

        $end = end($station_info);
        $bus_info['end_station'] = $end['station_name'];
        $bus_info['end_time'] = $end['start_time'];
        #endregion

        #region 2.获取每站信息
        $i = 0;
        $run_station_info = [];//站点数据
        foreach ($station_info as $key => $vel) {
            $vel['people_up_all_num'] = 0;//应上车总人数
            $vel['people_down_all_num'] = 0;//应下车总人数
            $vel['has_up_num'] = 0;//已上人数
            $vel['not_up_num'] = 0;//未上人数
            $vel['passengers'] = [];//上车乘客信息
            $vel['down_passengers'] = [];//下车乘客信息
            if ($vel['station_id'] == '') {
                $run_station_info[$i] = $vel;
                $i++;
            } else {
                $run_station_info[$vel['station_id']] = $vel;
            }
            unset($run_station_info[$vel['station_id']]['run_id']);
            unset($run_station_info[$vel['station_id']]['station_id']);
        }

        if (count($order_info) > 0) {
            foreach ($order_info as $key => $vel) {
                //$bus_info['saled_count'] += $vel['people_num'];//增加总乘客数
                $run_station_info[$vel['start_station_id']]['people_up_all_num'] += $vel['people_num'];//增加对应站应上车总人数
                $run_station_info[$vel['end_station_id']]['people_down_all_num'] += $vel['people_num'];//增加对应站应下车总人数
                if ($vel['check_status'] == 0) {
                    $run_station_info[$vel['start_station_id']]['not_up_num'] += $vel['people_num'];//增加对应站未检票总人数
                } else {
                    $run_station_info[$vel['start_station_id']]['has_up_num'] += $vel['people_num'];//增加对应站已检票总人数
                }
                $station_one = $vel;
                unset($station_one['start_station_id']);
                unset($station_one['end_station_id']);
                $run_station_info[$vel['start_station_id']]['passengers'][] = $station_one;//增加对应站上车订单
                $run_station_info[$vel['end_station_id']]['down_passengers'][] = $station_one;//增加对应站下车订单
            }
        }
        #endregion

        $result['bus_info'] = $bus_info;
        $result['station_info'] = array_merge($run_station_info);
        return $result;
    }

    /**
     * Function Description:获取已完成的任务数量
     * Function Name: getTaskComplete
     * @param int $driver_id 司机id
     *
     * @return int|string
     *
     * @author 张帅
     */
    public function getTaskComplete($driver_id)
    {
        $result = self::find()
            ->where([
                'and',
                ['=', 'send_bus_driver_res_id', $driver_id],
                ['=', 'cancel_flag', 0],
                ['=', 'run_status', 438]
            ])->count();

        return $result;
    }

    /**
     * Function Description:通过司机获取报账列表
     * Function Name: getFinanceListByDriver
     * @param int $driver_id 司机id
     *
     * @return array|\yii\db\ActiveRecord[]
     *
     * @author 张帅
     */
    public function getFinanceListByDriver($driver_id)
    {
        #region 1.从数据库获取数据
        $finance_list = self::find()
            ->select([
                'o.bus_number',//任务单号
                'o.itinerary_name',//行程名称
                'o.run_date',//行程日期
                'finance_status_id' => 'o.finance_status',//报账状态代号
                'finance_status' => DictType::find()->select('type_name')->where('id = o.finance_status')->limit(1),//报账状态
                'finance_price' => 'e.expense_value',//报账金额
                'e.expense_subject_id',
                'run_status_id' => 'o.run_status',
                'run_status' => DictType::find()->select('type_name')->where('id = o.run_status')->limit(1),
                'finance_log' => BusOrderStatusLog::find()->select('msg')->where('bus_number = o.bus_number and type = 432 and after_status = o.finance_status')->orderBy(['id' => SORT_DESC])->limit(1),//日志
            ])
            ->from(self::tableName() . ' as o')
            ->innerJoin(RunBusExpenses::tableName() . ' as e', 'o.bus_number = e.bus_number')
            ->where([
                'and',
                ['=', 'o.send_bus_driver_res_id', $driver_id],
                ['<=', 'o.run_date', date('Y-m-d', time())],
                ['>=', 'o.run_date', date('Y-m-d', strtotime(" -1 month "))],
                ['=', 'o.cancel_flag', 0],
                ['=', 'e.cancel_flag', 0],
                ['=', 'run_status', 438],
            ])
            ->orderBy(['o.run_date' => SORT_DESC, 'o.start_time' => SORT_DESC])
            ->asArray()->all();
        #endregion

        #region 2.整理数据
        $result = [];
        foreach ($finance_list as $key => $vel) {
            if (!isset($result[$vel['bus_number']])) {
                $result[$vel['bus_number']] = $vel;
                $result[$vel['bus_number']]['finance_price'] = 0;
            }
            if (in_array($vel['expense_subject_id'], [465, 466, 467])) {
                $result[$vel['bus_number']]['finance_price'] += $vel['finance_price'];
            }
        }
        $result = array_merge($result);
        #endregion

        return $result;
    }

    /**
     * Function Description:未报帐消息提醒
     * Function Name: sendFinanceMsg
     *
     * @return array
     *
     * @author 张帅
     */
    public function sendFinanceMsg()
    {
        #region 1.找出需要报账提醒的行程
        $itinerary_list = BusItinerary::find()
            ->select('itinerary_id,max(station_seq_id) as station_seq_id')
            ->where('cancel_flag = 0')
            ->groupBy('itinerary_id');

        $itinerary_list = BusItinerary::find()
            ->select('bi.itinerary_id,bi.station_seq_id,bi.start_time,bi.day_seq_id')
            ->from(BusItinerary::tableName() . ' as bi')
            ->innerJoin(['i1' => $itinerary_list], 'bi.itinerary_id = i1.itinerary_id and bi.station_seq_id = i1.station_seq_id')
            ->where('cancel_flag = 0');

        $finance_list = self::find()
            ->select([
                'o.bus_number',
                'o.finance_status',
                'driver_id' => 'o.send_bus_driver_res_id',
                'o.run_date',
                'i.start_time',
                'o.itinerary_id',
                'o.itinerary_name',
                'i.station_seq_id',
            ])
            ->from(self::tableName() . ' as o')
            ->innerJoin(['i' => $itinerary_list], 'o.itinerary_id = i.itinerary_id')
            ->where([
                'and',
                ['>=', 'i.start_time', date('H') . '00'],
                ['<', 'i.start_time', date('H', strtotime("+1 hours")) . '00'],
                [
                    'or',
                    [
                        'and',
                        ['=', 'day_seq_id', 1],
                        ['=', 'o.run_date', date("Y-m-d", strtotime("-1 day"))],
                    ],
                    [
                        'and',
                        ['=', 'day_seq_id', 2],
                        ['=', 'o.run_date', date("Y-m-d", strtotime("-2 day"))],
                    ],
                ],
                ['=', 'o.finance_status', 444],
                ['!=', 'o.send_bus_driver_res_id', ''],
                ['=', 'o.cancel_flag', 0],
                ['=', 'o.run_status', 438],
            ])
            ->orderBy(['i.start_time' => SORT_ASC])
            ->asArray()->all();
        #endregion

        #region 2.插入信息表数据
        if (count($finance_list) > 0) {
            $msg_list = [];
            foreach ($finance_list as $key => $vel) {
                $msg_list[] = [
                    'CANCEL_FLAG' => 0,
                    'CREATE_USER_ID' => 1,
                    'CREATE_TIME' => date('Y-m-d H:i:s'),
                    'UPDATE_USER_ID' => 1,
                    'UPDATE_TIME' => date('Y-m-d H:i:s'),
                    'BUS_NUMBER' => $vel['bus_number'],
                    'DRIVER_ID' => $vel['driver_id'],//9181,
                    'MSG_TYPE' => 490,
                    'SEND_MESSAGE' => date('m', strtotime($vel['run_date'])) . '月' . date('d', strtotime($vel['run_date'])) . '日,(' . $vel['itinerary_name'] . ')的出车任务还未进行报账，请及时进行报账，点击进入相应任务的报账页面，该任务已报账，再次点击提示“该任务已完成报账”',
                    'IS_READ' => 0,
                ];
            };

            $res = Yii::$app->db->createCommand()->batchInsert(DriverMessage::tableName(),
                ['CANCEL_FLAG', 'CREATE_USER_ID', 'CREATE_TIME', 'UPDATE_USER_ID', 'UPDATE_TIME', 'BUS_NUMBER', 'DRIVER_ID', 'MSG_TYPE', 'SEND_MESSAGE', 'IS_READ'],
                $msg_list)->execute();
            if ($res) {
                return $msg_list;
            } else {
                return [];
            }
        } else {
            return [];
        }
        #endregion
    }

    public function countBusOrderFromStatus($driver_res_id, $run_date, $run_status)
    {
        $total_count = self::find()
            ->from(self::tableName())
            ->where(
                [
                    'and',
                    ['=', 'send_bus_driver_res_id', $driver_res_id],
                    ['=', 'run_date', $run_date],
                    ['=', 'run_status', $run_status]
                ]
            )->count();
        return $total_count;
    }

    /**
     * 根据司机、车辆、日期等信息模糊查询出任务列表信息
     */
    public function getTaskByParam($driver_name,$bus_no,$run_date){
        $filter = ['and',['=','bo.cancel_flag',0]];
        $filter[] = ['and',['=','bb.cancel_flag',0]];
        $filter[] = ['and',['=','bd.cancel_flag',0]];
        if($driver_name != ''){
            //根据司机名字模糊查询出可能的司机资源id
            $driver_id_list = BaseDriver::find()->select(['driver_id as send_bus_driver_res_id'])->where(['and',['=','cancel_flag',0],['like','driver_name',$driver_name]])->asArray()->all();
            if(!$driver_id_list){   //没找到匹配的司机
                $filter[] = ['=','send_bus_driver_res_id','null'];
            }else{
                $filter[] = ['in','send_bus_driver_res_id',$driver_id_list];
            }



        }

        if($bus_no != ''){
            //根据车牌号查询出可能的车辆资源id
            $bus_id_list = BaseBus::find()->select('bus_id as send_bus_res_id')->where(['like','bus_no',$bus_no])->asArray()->all();
            if(!$bus_id_list){  //如果输入了车牌号，并且没有该车牌号信息
                $filter[] = ['=','send_bus_res_id','null'];
            }else{
                $filter[] = ['in','send_bus_res_id',$bus_id_list];
            }


        }
        if($run_date != ''){
            $filter[] = ['like','run_date',$run_date];
        }
        $task_list = self::find()
            ->select(['bo.run_date','bo.bus_number','bo.run_id','bo.bus_order_id','bo.start_time','bo.saled_count','bo.itinerary_name','bb.bus_no','bd.driver_name','bo.run_status'])
            ->from(BusOrder::tableName().' as bo')
            ->innerJoin(BaseBus::tableName().' as bb','bo.send_bus_res_id = bb.bus_id' )
            ->innerJoin(BaseDriver::tableName().' as bd','bo.send_bus_driver_res_id = bd.driver_id')
            ->filterWhere($filter)
            ->orderBy(['run_date'=>SORT_DESC])
            ->asArray()->all();
        // ->createCommand()->getRawSql();

        return $task_list;
    }

    public function getRunIdByBusNumber($bus_number){
        $result = self::find()->select("run_id,bus_order_id")->where(['bus_number' => $bus_number, 'cancel_flag' => 0])->asArray()->one();
        return $result;
    }
}