Task

Make sure that the user cannot create or modify a CarReservation,

• whose end date is equal to or earlier than the start date;
• whose timespan overlaps with the timespan of at least one existing reservation.

tip

Here you can find a generic description of validations.

---

Solution

Enter the following onValidation process in CarReservation_entity:

import { result, vars } from "@aditosoftware/jdito-types";
import { newSelect, SqlBuilder } from "SqlBuilder_lib";

let res;
let carReservationId = vars.get("$field.CARRESERVATIONID");
let carId = vars.get("$field.CAR_ID");
let endDate = vars.get("$field.ENDDATE");
let startDate = vars.get("$field.STARTDATE");

if (carId && endDate && startDate)
{
     if (endDate <= startDate)
    {
        res = "The end date must not be equal to or earlier than the start date.";
    } else
    {
        let overlapsCount = newSelect("count(*)")
            .from("CARRESERVATION")
            .where("CARRESERVATION.CAR_ID", carId)
            .and("CARRESERVATION.CARRESERVATIONID", carReservationId, SqlBuilder.NOT_LIKE())
            .and("CARRESERVATION.STARTDATE", endDate, SqlBuilder.LESS())
            .and("CARRESERVATION.ENDDATE", startDate, SqlBuilder.GREATER())
            .cell();
        
        if (overlapsCount > 0 )
        {
            res = "The selected timespan overlaps with "
             + overlapsCount + " existing reservation(s).";    
        }
    }
    
    if (res) {
        result.string(res);
    }
}

CarReservation_entity.onValidation

If you now enter wrong dates, the client will give you feedback accordingly, with the "Save" button disabled. Example: