Skip to main content

api-price-and-allotment

 ## **RATE CALCULATION

Here is the calculation logic for our booking engine:

Rate calculation payload needed are:

Property_id: id f(int) from property_details table selected period: format YYYY-MM-DD Room: id (int) from Rooms table

1. Get all Standard rates from table Rate_Plans form specific room:​

"SELECT id FROM Rate_Plans where Room_id= :room_id");

2. Get all rateplan details:​

SELECT  id,maxnights,Rate_M,Min_Nights_M,Room_id,show_option  FROM Rate_Plans where id= :rateplan_id";

  • Maxnights:optional and in case the rateplan cannot be booked more than specific days.
  • Rate_M: standard rate
  • Min_Nights_M: standard min night
  • Room_id: id of the room
  • Show_option: show/hide . If hide, we have to hide this rate.Note: if the rateplan is (hide) but the hotdeal is enabled, system should only show the hotdeal linked to this rateplan.

3. Select daily datas:​

"SELECT date,rates,stopsell,minnights,cta,ctd FROM yearly_monthly_deals where rateplan_id= :rateplan_id and unix_timestamp(date)>= :today and unix_timestamp(date)<= :end order by unix_timestamp(date)" 

=> If no result for specific day, standard results from point (2) will apply. 

=> Stopsell=1 => stopsell ON

=> Stopsell=0 or stopsell=2 => stopsell OFF

=> if one of the date is stopsell=1 => dates are not available for booking for the period

4. Check CTD on checkout date:​

If data not added in point 3, we need to check if any CTD on checkout date:

"SELECT  ctd FROM yearly_monthly_deals where rateplan_id= :rateplan_id and date= :finish"

5. Hotdeal calculation:​

"SELECT specials.* FROM specials

 WHERE 

  specials.property_id IN ($in)

  AND

 specials.rate_package LIKE ?

 AND

 (specials.type_special='Percent Discount' or specials.type_special='Amount Discount')

 AND status='0'"

  •  specials.rate_package are rateplans ids (can be multiple rateplan separated with coma), example: 51693,51694,51695

  •  if type_special='Percent Discount’ => percent calculation

  •  if type_special=''Amount Discount’ => amount calculation

  • mon_available,tue_available, etc… => days of the week included (if=1 => included)

  • refundable => if=1 means it is refundable

  • Hotel_general_policy => if=1 means the hotdeal must follow hotel general policy.

  • Custom_policy => if=1 means must follow custom policy from custom_policy_details

  • sale_date => All Dates/Specific Dates

  • iif “Specific Dates” means only for sale between sale_from_date and sale_to_date

  • promotion_code_unlock. If =1 => need to hide this hotdeal

  • discount: discount amount

  • min_night => need min night to apply this special

  •  valid_for =>   if=Specific Dates, hotdeal only valid from valid_for_from_date and valid_for_to_date 

  • Booked => within/atleast (last minute or early bird) 

  • Days_before => if booked=with or booked=atleast => days_before is the number of days for last minute or early bird.

  • Stackable => if=0 means it is stackable. Script must calculate the best rate for each day and all hotdeals. if=1 means it is not stackable and the script must show each hotdeal separately… 

"SELECT * FROM specials_excluding_date WHERE special_id= :id_paystay"

=> here we select period which excluded from calculation

INVENTORY CALCULATION​

1. Get total room default inventory​

"SELECT Property_Id,Rooms_Available_To FROM Rooms where id= :roomplan_id"

2. Select Values in Booking_dates table (number of bookings)​

"SELECT distinct Booked_date, count(Booked_date) as total FROM Booking_dates where Room_id= :roomplan_id and Booked_date>= :today2 $sql_end_pdo2 and status!='cancel' group by Booked_date"

3. Select inventory from yearly_monthly_deals​

"SELECT date,id,availability FROM yearly_monthly_deals where room_id= ? and date>= ? $sql_end_pdo  and availability!='' GROUP BY date order by unix_timestamp(date)"

  • If point 3 has some results:

inventory=availaiblity_value (result from point 3) - total_booking_dates (result from point 2).

Example:

Availability from yearly_monthly deals = 5

Booking_dates = 3

=> Inventory = 5 - 3 = 2 inventory.

To INSERT values in yearly_monthly_deal= Booking_dates values (result from point 2) + inventory value

Example:

Inventory should be 0

Booking_dates = 3

=> Availability from yearly_monthly deals = 3

  • If point 2 got no values:

inventory=default_inventory (result from point 1) - total_booking_dates (result from point 2)

Example:

Default Inventory = 5

Booking_dates = 2

=> Inventory = 5 - 2 = 3**