ZZCX_yii/backend/modules/hotel/models/OperaHotelRoom.php

<?php

namespace backend\modules\hotel\models;

use Yii;
use yii\data\ActiveDataProvider;
use yii\db\Expression;

/**
 * This is the model class for table "opera_hotel_room".
 *
 * @property integer $ID
 * @property integer $CANCEL_FLAG
 * @property integer $CREATE_USER_ID
 * @property string $CREATE_TIME
 * @property integer $UPDATE_USER_ID
 * @property string $UPDATE_TIME
 * @property integer $HOTEL_ID
 * @property integer $PARENT_ROOM_TYPE
 * @property integer $ROOM_TYPE
 * @property string $ROOM_NAME
 * @property integer $OCCUPANCY_LIMIT
 * @property integer $BREAKFAST_INCLUDE
 * @property integer $IS_ONSALE
 * @property integer $LASTEST_BOOK_TIME
 * @property integer $LASTEST_CANCEL_DAY
 */
class OperaHotelRoom extends \yii\db\ActiveRecord
{
    const IS_ONSALE = 1;
    const IS_OFFSALE = 0;

    /**
     * @inheritdoc
     */
    public static function tableName()
    {
        return 'opera_hotel_room';
    }

    /**
     * @inheritdoc
     */
    public function rules()
    {
        return [
            [['CANCEL_FLAG', 'CREATE_USER_ID', 'UPDATE_USER_ID', 'HOTEL_ID', 'PARENT_ROOM_TYPE', 'ROOM_TYPE', 'OCCUPANCY_LIMIT', 'BREAKFAST_INCLUDE', 'IS_ONSALE'], 'integer'],
            [['UPDATE_TIME'], 'safe'],
            [['CREATE_TIME'], 'string', 'max' => 20],
            [['ROOM_NAME', 'LATEST_BOOKING_TIME'], 'string', 'max' => 100],
        ];
    }

    /**
     * @inheritdoc
     */
    public function attributeLabels()
    {
        return [
            'ID' => 'ID',
            'CANCEL_FLAG' => 'Cancel  Flag',
            'CREATE_USER_ID' => '记录创建用户ID',
            'CREATE_TIME' => '记录创建时间',
            'UPDATE_USER_ID' => '记录最后更新用户ID',
            'UPDATE_TIME' => '记录最后更新时间',
            'HOTEL_ID' => '酒店ID，对应opera_hotel.hotel_id',
            'PARENT_ROOM_TYPE' => '基础房型，对应base_resource.res_id',
            'ROOM_TYPE' => '子房型ID',
            'ROOM_NAME' => '子房型名称',
            'OCCUPANCY_LIMIT' => '入住人数限制 0：无限制',
            'BREAKFAST_INCLUDE' => '是否含早,对应dict_type.id',
            'IS_ONSALE' => '该子房型是否上线 1为上线 0为下线',
            'LATEST_BOOKING_TIME' => '最晚预订时间、最晚取消时间',
            'BED_TYPE' => '子房型床型',
        ];
    }

    /**
     * User: wangxj
     *
     * 根据ID，返回ROOM_TYPE，以便下单使用
     *
     * @param $id
     * @return int
     */
    public static function getSpiderRoomType($id)
    {
        $room = OperaHotelRoom::findOne($id);
        if ($room != null) {
            return $room->ROOM_TYPE;
        } else {
            return 0;
        }
    }


    /**
     * Notes:
     * User: Steven
     * Date: 2018/1/18
     * Time: 14:56
     * @param $child_bed_type
     * @param $parent_bed_type
     * @return string
     */
    public static function getBedDesc($child_bed_type, $parent_bed_type)
    {
        $bed_name = '';
        if ($child_bed_type != 0) {
            $bed_name .= $child_bed_type == 1 ? '大床' : ($child_bed_type == 2 ? '双床' : '多床');
        } else {
            $bed_arr = explode(',', $parent_bed_type);
            foreach ($bed_arr as $item) {
                $bed_desc = $item == 1 ? '大' : ($item == 2 ? '双' : '多');
                $bed_name .= $bed_name == '' ? $bed_desc : ' / ' . $bed_desc;
            }
            $bed_name .= '床';
        }
        return $bed_name;
    }

    /**
     * User: wangxj
     *
     * 获取房型设置的最晚取消时间
     *
     * @param $run_date string
     * @return string timestamp
     */
    public function getLatestCancel($run_date)
    {
        $book_str = $this->LASTEST_CANCEL_DAY;
        if ($book_str == -1) { //不可取消  直接返回1970年的时间戳
            $time = strtotime('1970-01-01');
            return $time;
        }
        //0,23:30
        $arr = explode(',', $book_str);
        $endTime = strtotime($run_date) + 86400;
        $hour_arr = explode(':', $arr[1]);
        $lastTime = $endTime - $arr[0] * 86400 - (24 - $hour_arr[0]) * 3600;
        return $lastTime;
    }

    public function getHotelRelation()
    {
        return $this->hasOne(HotelRelation::className(), ['HotelId' => 'HOTEL_ID']);
    }

    public function getRunHotel()
    {
        return $this->hasMany(RunHotel::className(), ['HOTEL_ID' => 'HOTEL_ID', 'BASE_ROOM_TYPE' => 'PARENT_ROOM_TYPE']);
    }

    public function getRoomRelation()
    {
        return $this->hasOne(RoomRelation::className(), ['RoomId' => 'ID']);
    }

    public function getOperaHotelBaseRoom()
    {
        return $this->hasOne(OperaHotelBaseRoom::className(), ['MAIN_ID' => 'PARENT_ROOM_TYPE']);
    }

    public function getOperaHotel()
    {
        return $this->hasOne(OperaHotel::className(), ['HOTEL_ID' => 'HOTEL_ID']);
    }

    public function getOperaRoomDistrib()
    {
        return $this->hasOne(OperaRoomDistrib::className(), ['ROOM_ID' => 'ID']);
    }

    public function getHotelRoomInfo($channel_room_id)
    {
        $sql = "select b.HOTEL_ID,c.HOTEL_NAME,b.ROOM_NAME,b.INNER_IDENTIFY,a.ChannelRoomId,c.PRINCIPAL,d.USER_NAME from channel_room_relation a 
                LEFT JOIN opera_hotel_room b on a.RoomId=b.ID and b.CANCEL_FLAG=0
                LEFT JOIN opera_hotel c on b.HOTEL_ID=c.HOTEL_ID and c.CANCEL_FLAG=0
                LEFT JOIN base_user d on c.PRINCIPAL=d.ID  
                where a.ChannelRoomId={$channel_room_id} and a.ChannelId=" . Yii::$app->params['ctrip']['relation_supplier_id'];
        $connection = Yii::$app->db;
        $res = $connection->createCommand($sql)->queryAll();
        return $res;
    }

    /**
     * 根据酒店id与基础房型id查询该基础房型下的所有子房型
     */
    public function getAllRoom($hotel_id, $base_room)
    {
//        $sql = 'SELECT ID,ROOM_NAME FROM opera_hotel_room WHERE ID not in (SELECT room_id FROM price_compared_room) AND CANCEL_FLAG = 0 AND HOTEL_ID = '.$hotel_id .' AND PARENT_ROOM_TYPE = '.$base_room;
//        $res = Yii::$app -> db -> createCommand($sql) -> queryAll();
        $res = OperaHotelRoom::find()
            ->select(['ID', 'CONCAT(ROOM_NAME, \'|\', INNER_IDENTIFY) as CON_NAME'])
            ->where(['HOTEL_ID' => $hotel_id, 'PARENT_ROOM_TYPE' => $base_room, 'CANCEL_FLAG' => 0])
            ->asArray()
//            -> createCommand() -> getSql();
            ->all();
        return $res;
    }

    /**
     * @Author wanglg
     * @Desc 根据房型id获取阿里推送房型数据,暂时不用
     */
    public function getRoomInfo($room_id)
    {
        $res = OperaHotelRoom::find()->select(['a.ROOM_TYPE', 'a.ROOM_NAME', 'a.PARENT_ROOM_TYPE', 'a.HOTEL_ID'])
            ->joinWith('operaHotelBaseRoom as b')
            ->from('opera_hotel_room as a')
            ->where(['a.ID' => $room_id, 'a.CANCEL_FLAG' => 0])
            ->asArray()->one();

        return $res;
    }

    /**
     * Notes:获取可下单的酒店
     * User: Steven
     * Date: 2018/1/18
     * Time: 13:32
     */
    public function getHotelProduct($param)
    {
        $query = OperaHotelRoom::find()->select(['a.*', 'c.MAIN_ID'])
            ->joinWith('operaHotel b')
            ->joinWith('operaHotelBaseRoom c')
            ->where(['a.cancel_flag' => 0, 'b.CANCEL_FLAG' => 0, 'a.IS_ONSALE' => 1, 'b.HOTEL_STATUS' => 1])
            ->groupBy(['a.HOTEL_ID', 'a.PARENT_ROOM_TYPE', 'a.ROOM_TYPE'])
            ->from('opera_hotel_room a');
        $dataProvider = new ActiveDataProvider([
            'query' => $query,
            'pagination' => [
                'pageSize' => 20,
            ],
        ]);
        if (!empty(trim($param['HOTEL_NAME']))) {
            $query->andFilterWhere(['like', 'b.HOTEL_NAME', trim($param['HOTEL_NAME'])]);
        }
        if (!empty(trim($param['INNER_IDENTIFY']))) {
            $query->andFilterWhere(['like', 'a.INNER_IDENTIFY', trim($param['INNER_IDENTIFY'])]);
        }
        if (!empty(trim($param['ROOM_NAME']))) {
            $query->andFilterWhere(['like', 'a.ROOM_NAME', trim($param['ROOM_NAME'])]);
        }
        return $dataProvider;
    }

    /**
     * Des:獲取房型數組
     * Name: getSaleRoomList
     * @param $baseRoomId
     * @return array
     * @author 倪宗锋
     */
    public function getSaleRoomList($baseRoomId, $channel_id)
    {
        $select = [
            'a.id',
            'a.room_name',
            'b.channel_mapping_id'
        ];
        $where = [
            'and',
            ['=', 'a.PARENT_ROOM_TYPE', $baseRoomId],
            ['=', 'a.CANCEL_FLAG', 0]
        ];
        $getList = self::find()->select($select)
            ->from(self::tableName() . ' a')
            ->leftJoin(OperaRoomDistrib::tableName() . ' b', 'a.ID = b.ROOM_ID and b.DISTRIB_ID=' . $channel_id)
            ->where($where)
            ->groupBy('a.ID')
            ->asArray()
            ->all();
        if (!is_array($getList)) {
            return [];
        }
        return array_values($getList);
    }

    /**
     * Des:獲取房型數組 对象
     * Name: getSaleRoomListForShow
     * @param $baseRoomId
     * @return ActiveDataProvider
     * @author 倪宗锋
     */
    public function getSaleRoomListForShow($baseRoomId, $channel_id)
    {
        $where = [
            'and',
            ['=', 'a.PARENT_ROOM_TYPE', $baseRoomId],
            ['=', 'a.CANCEL_FLAG', 0]
        ];
        $query = self::find()
            ->from(self::tableName() . ' a')
            ->joinWith(['operaRoomDistrib' => function ($query) use ($channel_id) {
                $query->onCondition(['opera_room_distrib.DISTRIB_ID' => $channel_id]);
            }])
            ->where($where);
        $dataProvider = new ActiveDataProvider([
            'query' => $query,
            'sort' => false,
            'pagination' => [
                'pagesize' => 100,
            ],
        ]);
        return $dataProvider;
    }

    /**
     * Des:获取房型关联信息
     * Name: getRoomInfoForSetSaleRoom
     * @param $params
     * @return array
     * @author 倪宗锋
     */
    public function getRoomInfoForSetSaleRoom($params)
    {
        $where = [
            'and',
            ['=', 'a.ID', $params['cs_sale_room_id']],
            ['=', 'a.CANCEL_FLAG', 0]
        ];
        $select = [
            'cs_sale_room_id' => 'a.ID',
            'room_name' => 'a.ROOM_NAME',
            'base_room_id' => 'a.PARENT_ROOM_TYPE',
            'sale_room_id' => 'b.CHANNEL_MAPPING_ID',
            'distrib_id' => 'b.ID',
            'base_room_name' => 'c.BASE_ROOM_NAME',
            'd.sub_base_room_id'
        ];
        $info = self::find()->select($select)
            ->from(self::tableName() . ' a')
            ->leftJoin(OperaRoomDistrib::tableName() . ' b', " a.ID = b.ROOM_ID and b.DISTRIB_ID = {$params['channel_id']} and b.CANCEL_FLAG = 0")
            ->innerJoin(OperaHotelBaseRoom::tableName() . ' c', 'a.PARENT_ROOM_TYPE = c.MAIN_ID and c.CANCEL_FLAG = 0 ')
            ->leftJoin(ChannelBaseRoomMapping::tableName().' d' ,' c.MAIN_ID = d.base_room_id and d.cancel_flag = 0')
            ->where($where)
            ->asArray()
            ->one();
        return $info;
    }

    /**
     * Des:获取新增售卖房型 需要使用的参数
     * Name: getDataForAddSaleRoom
     * @param $params
     * @return array
     * @author 倪宗锋
     */
    public function getDataForAddSaleRoom($params)
    {
        $select = [
            'b.master_hotel_id',
            'b.sub_hotel_id',
            'b.master_base_room_id',
            'b.sub_base_room_id',
            'a.BREAKFAST_INCLUDE',
            'a.OCCUPANCY_LIMIT',
            'BED_TYPE' => new  Expression('if(a.BED_TYPE=0,c.BED_TYPE,a.BED_TYPE)')
        ];
        $where = [
            'and',
            ['=', 'a.ID', $params['cs_sale_room_id']],
            ['=', 'a.CANCEL_FLAG', 0],
            ['=', 'b.CANCEL_FLAG', 0]
        ];
        $getInfo = self::find()->select($select)
            ->from(self::tableName() . ' a')
            ->innerJoin(ChannelBaseRoomMapping::tableName() . ' b', 'a.PARENT_ROOM_TYPE = b.base_room_id')
            ->innerJoin(OperaHotelBaseRoom::tableName() . ' c', 'a.PARENT_ROOM_TYPE = c.MAIN_ID')
            ->where($where)
            ->asArray()
            ->one();
        return $getInfo;
    }

    /***
     * Des:获取基础房型下面已经关联的售卖房型的数量
     * Name: getChannelByBaseRoom
     * @param $param
     * @param $channel_arr
     * @return int
     * @author 倪宗锋
     */
    public function getChannelByBaseRoom($param, $channel_arr)
    {
        $select = [
            'cnt' => new Expression("COUNT(CHANNEL_MAPPING_ID)")
        ];
        $where = [
            'and',
            ['=', 'a.PARENT_ROOM_TYPE', $param['base_room_id']],
            ['!=', 'b.CHANNEL_MAPPING_ID', 0],
            ['=', 'a.CANCEL_FLAG', 0],
            ['=', 'a.CANCEL_FLAG', 0],
            ['in', 'b.DISTRIB_ID', $channel_arr],
        ];
        $getCnt = self::find()->select($select)
            ->from(self::tableName() . ' a')
            ->innerJoin(OperaRoomDistrib::tableName() . ' b', 'a.ID = b.ROOM_ID')
            ->where($where)
            ->asArray()
            ->one();
        if (empty($getCnt['cnt'])) {
            return 0;
        }
        return $getCnt['cnt'];
    }
}