Task #145
Updated by Dana Basheer 9 days ago
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 → (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 vehicle_master.daily_rent Daily vehicle rate Auto fetched * max_km_per_day DECIMAL(10,2) From vehicle_season_rate.max_km_per_day (if exists) OR static config vehicle_master.max_km_per_day Maximum km allowed per day Auto fetched * distance DECIMAL(10,2) Auto System calculated from geography OR user input Total distance Distance between locations * extra_km DECIMAL(10,2) Calculated System calculated distance − max_km_per_day Extra KM * extra_km_rate DECIMAL(10,2) From vehicle_season_rate.extra_km_rate vehicle_master.extra_km_rate Rate per extra km Auto fetched * vehicle_total_amount DECIMAL(10,2) DECIMAL(12,2) Calculated System calculated Final vehicle cost Vehicle total * 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