Date check, if there in table

[apacer]

Hi, im trying to do a script for date check. For example there is two dates in table 01-10-2023 and 05-10-2023. If user chooses two dates in that period of time like 01-10-2023 to lets say 07-10-2023 the message will popup that the dates are taken and you can pick the day after latest date. So if the dates in table are 01-10-2023 - 05-10-2023, user selects 01-10-2023 - 07-10-2023 the message will give that user can take 06-10-2023 

I did some scripting but the script gives me the day before like in the example above it gives me 04-10-2023.

And if user picks the 06-10-2023 it will give the message that is taken...

[Kalyani Jangam1]

Hi @apacer 

can you share your script and let us know in which step it stuck

[apacer]

Yes, ofcourse:

Script Include:

var ReservationHelper = Class.create();
ReservationHelper.prototype = Object.extendsObject(global.AbstractAjaxProcessor, {
checkReservations: function() {
var modelNumber = this.getParameter('sysparm_model');
var start = this.getParameter('sysparm_start');
var end = this.getParameter('sysparm_end');

var startGDT = new GlideDateTime(start);
var endGDT = new GlideDateTime(end);

var conflictingReservationGR = new GlideRecord('x_1064535_projecto_rent_a_projector');
conflictingReservationGR.addQuery('model_of_the_projector', modelNumber);
conflictingReservationGR.addQuery('start_of_rental', '<=', endGDT);
conflictingReservationGR.addQuery('end_of_rental', '>=', startGDT);
conflictingReservationGR.query();

if (conflictingReservationGR.next()) {
var latestEndDate = new GlideDateTime(conflictingReservationGR.end_of_rental.getDisplayValue());
latestEndDate.addDaysUTC(2);
return latestEndDate.getDisplayValue();
}

return 'no_conflict';
}
});

Client Script:

function onChange(control, oldValue, newValue, isLoading) {
if (isLoading || newValue === '') {
return;
}

var startDate = g_form.getValue('start_of_rental');
var endDate = g_form.getValue('end_of_rental');
var model = g_form.getValue('model_of_the_projector');

//bierząca data, startowa i końcowa
var currentDate = new Date();
var start = new Date(startDate);
var end = new Date(endDate);

if (start < currentDate || end < currentDate) { 
g_form.addErrorMessage('Selected date cannot be in the past.');
g_form.clearValue('start_of_rental');
g_form.clearValue('end_of_rental');
return;
}

if (startDate !== '' && endDate !== '' && model !== '') { 
var ga = new GlideAjax('ReservationHelper'); 
ga.addParam('sysparm_name', 'checkReservations');
ga.addParam('sysparm_start', startDate);
ga.addParam('sysparm_end', endDate);
ga.addParam('sysparm_model', model);

ga.getXMLAnswer(function(answer) {
if (answer === 'no_conflict') {
} else if (answer !== 'no_conflict' && answer !== 'conflict') {
g_form.addInfoMessage('The projector is available after ' + answer);
g_form.clearValue('start_of_rental');
g_form.clearValue('end_of_rental');
} else {
g_form.addErrorMessage('Conflict');
g_form.clearValue('start_of_rental');
g_form.clearValue('end_of_rental');
}
});
}
}

[OlaN]

Hi,

I can help, and will give a suggestion below, but I'm having one issue with the given scenario here.

What if there exists two records in the database like this:

No1: Start date 02-10-2023  end date 04-10-2023

No2: Start date 05-10-2023 end date 08-10-2023

So given above scenario, if the user selects date as 03-10-2023, the system should suggest 05-10-2023 as next available date according to record No1, but record 2 has already occupied this date. How should it work in this case?

Some simple scripting suggestion:

var startDate = new GlideDateTime('2023-10-05');
var endDate = new GlideDateTime('2023-10-10');

var mytestDate = new GlideDateTime('2023-10-07');

if (mytestDate.after(startDate) && mytestDate.before(endDate)){
    gs.info('Date already taken');
    var nextDay = new GlideDateTime(endDate)
    nextDay.addDaysUTC(1);
    gs.info('Try date from: ' + nextDay.getDate());
}
else{
    gs.info('Date is free to take');
}

[apacer]

var ReservationHelper = Class.create();
ReservationHelper.prototype = Object.extendsObject(global.AbstractAjaxProcessor, {
    checkReservations: function() {
        var modelNumber = this.getParameter('sysparm_model');
        var start = this.getParameter('sysparm_start');
        var end = this.getParameter('sysparm_end');

        var startGDT = new GlideDateTime(start); 
        var endGDT = new GlideDateTime(end);

        var conflictingReservationGR = new GlideRecord('x_1064535_projecto_rent_a_projector');
        conflictingReservationGR.addQuery('model_of_the_projector', modelNumber);
        conflictingReservationGR.addQuery('start_of_rental', '<=', endGDT);
        conflictingReservationGR.addQuery('end_of_rental', '>=', startGDT);
        conflictingReservationGR.query();

        if (conflictingReservationGR.next()) {  
            var latestEndDate = new GlideDateTime(conflictingReservationGR.end_of_rental.getDisplayValue());
            latestEndDate.addDaysUTC(1); //dodaje jeden dzień do "ostatniej" daty
            return latestEndDate.getDisplayValue(); //zwraca datę z dodanym dniem 
        }

        return 'no_conflict';
    }
});

This is my script include, it looks for the item in the table and searches for the dates. If there is dates like 01-10-2023 to 05-10-2023 and 06-10-2023 to 10-10-2023 it should give back 11-10-2023. My issue here is that i have already  01-10-2023 to 05-10-2023 and i cant pick the 06-10-2023, it sees conflict idk why