Requirement

Use Case

Steps to follow

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. }

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();

Related links

• GetDayOfWeek built-in functions
• Disable fields
• Conditional statements