aonestar.public

Parameters

Name	Type	Mode

Definition

BEGIN
  delete from room_usage;    
  delete from room_activity;
  
--  /* Insert total room for each room types */
--  insert into roomlog (logtype, logevent, roomtypeno, startdate, enddate, qty, logtime)
--  select 'TTL', 'Initialize', roomtypeno, :curdate, :curdate+400, totalroom, :logtime
--  from roomtype;

  /* Bookings */
  INSERT INTO room_activity (status, activity_type, activity_time, start_date, end_date, qty, roomtype_id, channel_id, booking_item) 
  SELECT 'RSV', 'Initialize', current_timestamp, bi.arrival, bi.departure-1, (qty -block_qty -inhouse_qty), bi.roomtype_id, b.channel_id, bi.item_id 
  FROM booking_items bi INNER JOIN booking b on b.id = bi.booking_id 
  WHERE b.status in('R','C','I') AND (qty - block_qty - inhouse_qty) > 0;

  /* Block */
  INSERT INTO room_activity (status, activity_type, activity_time, start_date, end_date, qty, roomtype_id, channel_id, booking_item, room_id) 
  SELECT 'BLK', 'Initialize', current_timestamp, bi.arrival, bi.departure-1, 1, r.roomtype_id, b.channel_id, bi.item_id, rb.room_id           
  FROM room_block rb left join room r on r.id = rb.room_id
      LEFT JOIN booking_items bi ON bi.item_id = rb.booking_item 
      LEFT JOIN booking b on b.id = bi.booking_id;

  /* Occupied rooms */
  INSERT INTO room_activity (status, activity_type, activity_time, start_date, end_date, qty, roomtype_id, channel_id, booking_item, room_id, register_id)
  WITH INH AS (
    SELECT room_id, max(id) register_id, min(arrival) arrival, max(departure) departure
    FROM registration 
    WHERE status = 'I'
    GROUP BY room_id
  )
  SELECT 'INH', 'Initialize', current_timestamp, inh.arrival, inh.departure-1, 1, r.roomtype_id, reg.channel_id, reg.booking_item, inh.room_id, inh.register_id
  FROM INH LEFT join registration reg ON reg.id = inh.register_id
      left join room r on r.id = inh.room_id;


  /* Out Of Order */
  INSERT INTO room_activity (status, activity_type, activity_time, start_date, end_date, qty, roomtype_id, room_id) 
  SELECT 'OOO', 'Initialize', current_timestamp, o.start_date, o.end_date-1, 1, r.roomtype_id, o.room_id
  FROM out_of_order o left join room r on r.id = o.room_id;

  --post_event 'ROOM_AVAIL_RESET';
END

sp_init_room_available

Parameters

Definition