TripOn

This table stores the day-wise tour plan details for each selected location. It includes hotel stay information, season-based room rate, meal plan (fetched from enquiry), and vehicle details. When a location is selected, hotels from that location are listed, and based on the check-in and check-out dates, the corresponding season rate is automatically fetched and assigned as the daily room rent. Vehicle details such as vehicle type, count, distance, and rent are also stored for that specific day and location. The enterprise_id is taken from the logged-in session, and enquiry_id links the tour plan with the original enquiry. This table acts as the main transactional table connecting enquiry, hotel, season rate, room category, vehicle, and location.

Table

tour_plan_details

• id INT (PK) System generated Unique ID
• tour_plan_id INT (FK) tour_plan_master.id Tour plan reference
• enquiry_id INT (FK) enquiry_master.id Enquiry reference
• enterprise_id INT (FK) enterprise_master.enterprise_id From session
• location_id INT (FK) locations_geography.geog_id Selected location
• checkin_date DATE User input Hotel check-in date
• checkout_date DATE User input Hotel check-out date
• nights INT enquiry_master.no_of_nights Auto fetched
• hotel_category_id INT (FK) hotel_category_master.id Hotel category
• hotel_id INT (FK) hotel_master.id Selected hotel
• room_category_id INT (FK) room_category_master.id Room category
• meal_plan VARCHAR(50) enquiry_master.meal_plan Auto fetched
• no_of_adults INT enquiry_master.no_of_adults Auto fetched
• children_with_bed_qty INT enquiry_master.child_with_bed Auto fetched
• children_without_bed_qty INT enquiry_master.child_without_bed Auto fetched
• extra_bed_qty INT enquiry_master.extra_bed Auto fetched
• total_pax INT enquiry_master.total_pax Auto fetched
• double_room_count INT enquiry_master.no_of_double_room Auto fetched
• single_room_count INT enquiry_master.no_of_single_room Auto fetched
• season_id INT (FK) hotel_room_season_master.id Auto fetched season
• daily_room_rent DECIMAL(10,2) hotel_room_season_master.rate Auto fetched
• children_with_bed_rate DECIMAL(10,2) User input Manual rate
• children_without_bed_rate DECIMAL(10,2) User input Manual rate
• extra_bed_rate DECIMAL(10,2) User input Manual rate
• hotel_total_amount DECIMAL(12,2) System calculated Total hotel cost
• vehicle_id INT FK → vehicle_master.id From enquiry_vehicle table Selected vehicle model
• vehicle_model_name VARCHAR(100) From enquiry_vehicle Display purpose only
• vehicle_season_rate_id INT FK → vehicle_season_rate.id Auto fetch Matching seasonal rate record
• vehicle_count INT User input (from enquiry screen) Number of vehicles
• daily_rent DECIMAL(10,2) From vehicle_season_rate.rate_per_day Daily vehicle rate
• max_km_per_day DECIMAL(10,2) From vehicle_season_rate.max_km_per_day (if exists) OR static config Maximum km allowed per day
• distance DECIMAL(10,2) Auto calculated from geography OR user input Total distance
• extra_km DECIMAL(10,2) Calculated distance − max_km_per_day
• extra_km_rate DECIMAL(10,2) From vehicle_season_rate.extra_km_rate Rate per extra km
• vehicle_total_amount DECIMAL(10,2) Calculated Final vehicle cost
• is_active BOOLEAN System Active status
• created_at DATETIME System Created date
• updated_at DATETIME System Updated date

Validations

Mandatory Fields:

• tour_plan_id required
• enquiry_id required
• enterprise_id required (session based)
• location_id required
• checkin_date required
• checkout_date required
• hotel_category_id required
• hotel_id required
• room_category_id required

Auto-Fetch

These fields must be auto-filled and NOT manually editable:

• nights
• no_of_adults
• children_with_bed_qty
• children_without_bed_qty
• extra_bed_qty
• total_pax
• double_room_count
• single_room_count
• meal_plan

System must fetch using enquiry_id.

Season Rate

System must first check in:

hotel_room_season_master

Matching conditions:

• hotel_id must match
• room_category_id must match
• enterprise_id must match

checkin_date must be between start_date and end_date

is_active = TRUE

If season record found:

daily_room_rent = season_rate_master.rate

If NO season record found

System must fetch default rate from:

hotel_room_category_master (or hotel_room_category_map — based on your project table)

Matching conditions:

• hotel_id must match
• room_category_id must match
• enterprise_id must match

daily_room_rent = default_rate

Location

• location_id must exist in locations_geography
• hotel must belong to selected location
• hotel_master.location_id must match location_id

Date

• checkin_date must be less than checkout_date
• nights must match date difference

Hotel

• hotel_id must exist in hotel_master
• hotel must belong to enterprise_id

Room Category

• room_category_id must exist in room_category_master
• room category must belong to selected hotel

Manual Rate

These fields must be numeric and ≥ 0:

• children_with_bed_rate
• children_without_bed_rate
• extra_bed_rate

Cannot be negative.

Vehicle Validation

If vehicle required:

• vehicle_id must exist in vehicle_master
• vehicle_count must be numeric
• vehicle_count must be ≥ 1

Auto-fetch fields from vehicle_master:

• daily_rent
• max_km_per_day
• extra_km_rate

Calculated fields:

• distance
• extra_km
• vehicle_total_amount

Enterprise

• enterprise_id must come from session
• enterprise_id must match enquiry_master.enterprise_id
• enterprise_id must match hotel_master.enterprise_id
• enterprise_id must match vehicle_master.enterprise_id

Duplicate Validation

Prevent duplicate entry:

Same combination not allowed:

• tour_plan_id
• location_id
• checkin_date
• hotel_id