Rent calculation based on multiple values selected by customers for events

Rent calculation based on multiple values selected by customers for events

Requirement

Estimate rent for a property based on the date and time it is booked, as well as attendance numbers.

Use Case

A party hall of a hotel is being rented out. Here is the pricing structure of the hall:
Day
Base price
(in USD)
Standard guests count
(in numbers)
Cost per extra guest
(in USD)
Standard duration
(in hours)
Cost per extra hour per guest
(in USD)
Monday - Thursday
5200
50
80
5
7
Friday and Sunday
6500
50
80
5
7
Saturday
7500
50
80
5
7

 
Now, let's say this hotel's management app contains a form to book the party hall. When a user who wants to book the party hall inputs his expected guest count and the event timings, the total price of the event will be calculated based on the above specs.

Steps to follow

1. Create a form with the following details.
Form
Form Link Name
Field Type
Field Name
Field Link Name
Book Party Hall
Book_Party_Hall
Name
Name
Name
Email
Email
Email
Number
Guest Count
Guest_Count
Date-Time
Event Start Time
Event_Start_Time
Date-Time
Event End Time
Event_End_Time
Currency
Total Cost
Total_Cost
 
The Total Cost is estimated based on the Guest Count, Event Start Time, and Event End Time fields. To ensure precise calculation, mark these three fields mandatory.
 
2. Create workflow with the following details:
 
3. The Total Cost field will be auto populated with the calculated value. Thus, the field will be disabled on load of the form. Save the following Deluge script in the Deluge editor:
  1. disable Total_Cost;
 
4. Create another workflow with the following details:

5. Save the following Deluge script in the Deluge editor:
  1. // Perform the estimation calculation only on the following conditions
  2. if(Event_Start_Time != null && Event_End_Time != null && Event_Start_Time < Event_End_Time && Guest_Count != null)
  3. {
  4.  
  5.  // Define base package details
  6.  monday_to_thursday_cost = 5200;
  7.  friday_sunday_cost = 6500;
  8.  saturday_cost = 7500;
  9.  standard_guests = 50;
  10. standard_hours = 5;
  11.  
  12.  // Define additional costs
  13.  cost_per_extra_guest = 80;
  14.  cost_per_extra_hour_per_guest = 7;
  15.  
  16.  //Find on which day the event will be conducted
  17.  day = input.Event_Start_Time.getDayOfWeek();
  18.  
  19.  //Set cost based on the day the event will be conducted
  20.  if(day == 7)
  21.  {
  22.   cost = saturday_cost;
  23.  }
  24.  else if(day == 6 || day == 1)
  25.  {
  26.   cost = friday_sunday_cost;
  27.  }
  28.  else
  29.  {
  30.   cost = monday_to_thursday_cost;
  31.  }
  32.  
  33.  // Charge additional price for extra guests
  34.  if(Guest_Count > 50)
  35.  {
  36.   extra_guests = input.Guest_Count - standard_guests;
  37.   cost = cost + cost_per_extra_guest * extra_guests;
  38.  }
  39.  
  40.  //Calculate number of event hours
  41.  event_hours = ((input.Event_End_Time - input.Event_Start_Time)) / (1000 * 3600);
  42.  
  43.  //Charge additional cost for extra hours
  44.  if(event_hours > 5)
  45.  {
  46.   extra_hours = event_hours - standard_hours;
  47.   cost = cost + cost_per_extra_hour_per_guest * extra_hours * input.Guest_Count;
  48.  }
  49.  input.Total_Cost = cost;
  50. }

Now, let's recalculate the total cost on the input of Guest Count field and Start Time field. This is to ensure the correct Total Cost is estimated even if the user re-enters guest count and start time.
 


 
7. Save the same Deluge script from step 5 in the Deluge editor.

See how it works


Points to note

  • This tutorial demonstrates the cost calculation of one party hall that is rented out with a complex pricing structure based on the date, time, and attendance numbers of the event. Thus, for simplicity, the base prices are hardcoded in the script. In order to apply the script to multiple party halls in the hotel that are rented out with similar pricing structure but different rates, store the base prices and additional prices on an admin form and fetch the rates according to the selected party hall as and when required.
     
  • In this tutorial, the hours difference between the start and end time is calculated using a formula instead of using the hoursBetween built-in function. This is because the intended behaviour of this function is to neither round off the hours value based on the minutes difference nor return an accurate decimal equivalent.

    For example:
     
    start_time = '18-Dec-2019 07:00:00';
    end_time = '18-Dec-2019 08:59:59';
    total_hours = hoursBetween(start_time, end_time);

    Here, the value of total_hours is 1 even though the time difference is 1 hour and 59 minutes. So, if hoursBetween function is used, the customer will be charged only for 1 hour even if the event lasted for 1 hour and 59 minutes.

  • In services other than Zoho Creator, you can use timeBetween built-in function to find the accurate time difference between two date-time values. We will support this in Zoho Creator soon. You can use the following formula to achieve the result returned by the timeBetween function.

    start_date = '1-Jun-2021 00:00:07';
    end_date = '24-Jun-2021 12:12:14';
    difference_in_ms = (end_date-start_date);
    hours = difference_in_ms / (1000 * 60 * 60);
    mins = (difference_in_ms / (1000 * 60)) % 60;
    secs = (difference_in_ms/ 1000)% 60;
    info hours.floor()+":"+ mins.floor() + ":" + secs.floor();