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

<?php

namespace backend\modules\hotel\models;

use Yii;

/**
 * This is the model class for table "run_hotel".
 * 基础房型库存数量
 *
 * @property integer $ID
 * @property integer $CREATE_USER_ID
 * @property string $CREATE_TIME
 * @property integer $UPDATE_USER_ID
 * @property string $UPDATE_TIME
 * @property integer $HOTEL_ID
 * @property integer $BASE_ROOM_TYPE
 * @property string $RUN_DATE
 * @property integer $STOCK_TYPE
 * @property integer $REMAINING_COUNT
 * @property integer $SALED_COUNT
 * @property integer $IS_ONSALE
 */
class RunHotel extends \yii\db\ActiveRecord
{
    /**
     * @inheritdoc
     */
    public static function tableName()
    {
        return 'run_hotel';
    }

    /**
     * @inheritdoc
     */
    public function rules()
    {
        return [
            [['CREATE_USER_ID', 'UPDATE_USER_ID', 'HOTEL_ID', 'BASE_ROOM_TYPE', 'STOCK_TYPE', 'REMAINING_COUNT', 'SALED_COUNT', 'IS_ONSALE'], 'integer'],
            [['CREATE_TIME', 'RUN_DATE'], 'required'],
            [['UPDATE_TIME'], 'safe'],
            [['CREATE_TIME', 'RUN_DATE'], 'string', 'max' => 20],
        ];
    }

    /**
     * @inheritdoc
     */
    public function attributeLabels()
    {
        return [
            'ID' => 'ID',
            'CREATE_USER_ID' => '记录创建用户ID',
            'CREATE_TIME' => '记录创建时间',
            'UPDATE_USER_ID' => '记录最后更新用户ID',
            'UPDATE_TIME' => '记录最后更新时间',
            'HOTEL_ID' => '酒店ID',
            'BASE_ROOM_TYPE' => '基础房型',
            'RUN_DATE' => '售卖日期',
            'STOCK_TYPE' => '库存类型',
            'REMAINING_COUNT' => '库存剩余数量',
            'SALED_COUNT' => '已售数量',
            'IS_ONSALE' => ' 上下线标志 1：上线 0：下线',
        ];
    }

    /**
     * User:Steven
     * Desc:获取基础房型的库存 用于对部分房型的库存进行监控预警
     * @param $base_room_id 基础房型的ID
     */
    public function getRunHotelCount($base_room_id)
    {
        $begin_date = date('Y-m-d', time());
        $end_date = date('Y-m-d', strtotime('+1 month'));
        $sql = "select a.hotel_id,a.base_room_type,a.run_date,a.is_onsale,a.stock_type,c.hotel_name,b.base_room_name,c.principal,d.USER_NAME,
                sum(case a.stock_type when 228 then a.remaining_count else 0 end)  as 'buyout',
                sum(case a.stock_type when 229 then a.remaining_count else 0 end) as 'inquiry',
                sum(case a.stock_type when 230 then a.remaining_count else 0 end) as 'retain'
                from run_hotel a 
                LEFT JOIN opera_hotel_base_room b on a.base_room_type=b.main_id 
                LEFT JOIN opera_hotel c on a.hotel_id=c.hotel_id 
                LEFT JOIN base_user d on c.PRINCIPAL=d.ID  
                where a.base_room_type={$base_room_id} and a.run_date BETWEEN '{$begin_date}'  and '{$end_date}' GROUP BY base_room_type,run_date";
        $connection = Yii::$app->db;
        $res = $connection->createCommand($sql)->queryAll();
        return $res;
    }

    /**
     * User:Steven
     * Desc:获取部分基础房型（拆分房型和合并房型）已售数量的统计 （徐安）
     * @return array
     */
    public function getRoomSaledCount(){
        $date=date('Y-m-d',time());
        $sql="SELECT
                    a.HOTEL_ID,
                    a.RUN_DATE,
                sum(a.num) as saledcount
                FROM
                    (
                        SELECT
                            HOTEL_ID,
                            BASE_ROOM_TYPE,
                            RUN_DATE,
                            sum(SALED_COUNT) as num
                        FROM
                            run_hotel
                        WHERE
                            hotel_id = 23
                        AND RUN_DATE >= '{$date}'
                        AND BASE_ROOM_TYPE IN (9797, 150039, 150060)
                        GROUP BY
                            RUN_DATE,
                            base_room_type
                    ) AS a GROUP BY a.RUN_DATE";
        $connection = Yii::$app->db;
        $res = $connection->createCommand($sql)->queryAll();
        return $res;
    }

}