Ultimate Booking Scheduler

/*
Script: Ultimate Booking Scheduler
Authors: Kamille Parks, Jeremy Oglesby 

This script combines Jeremy Oglesby's "Check for Speaker Schedule Conflicts" script and 
Kamille Parks's "No-Conflict Asset Reservations" script to create the ultimate scheduling
aid for event planners!

*/

// Begin BASE SPECIFIC NAMES Section (adjust these values to fit your base!)

const BaseSpecificNames = {
    // Reservations Table
    reservationsTable: "Reservations", // name of the [RESERVATIONS] table
    assetField: "Room", // name of the link-type field connecting to the [ASSETS] table
    startField: "Start Date",
    endField: "End Date",
    personField: "Customer", // name of the link-type field connection to the [PEOPLE] table

    // Assets Table
    assetsTable: "Rooms", // name of the [ASSETS] table
    assetName: "Room #", // name of the primary field in the [ASSETS] table
    reservationsField: "Reservations",

    // People Table
    peopleTable: "People", // name of the [PEOPLE] table
    peopleName: "Name" // name of the primary field in the [PEOPLE] table
}

// End BASE SPECIFIC NAMES Section (everything below should work without the need for further adjustment.)

// Begin VARIABLE DECLARATIONS Section

const peopleTable = base.getTable(BaseSpecificNames.peopleTable);

const reservationsTable = base.getTable(BaseSpecificNames.reservationsTable);
const reservationsQuery = await reservationsTable.selectRecordsAsync();
const allReservations = reservationsQuery.records;

const assetsTable = base.getTable(BaseSpecificNames.assetsTable);
const assetsQuery = await assetsTable.selectRecordsAsync({sorts: [{field: BaseSpecificNames.assetName}]});
const allAssets = assetsQuery.records;

let conflicts, unavailableAssets, availableAssets;
let overlaps = [];
let altered = [];
let unaltered = [];

// End VARIABLE DECLARATIONS Section

// Begin FUNCTIONS Section

async function findConflictingReservations(startDate, endDate) {
    conflicts = [];
    for (let reservation of allReservations) {
        let compareStart = new Date(reservation.getCellValue(BaseSpecificNames.startField)).toISOString();
        let compareEnd = new Date(reservation.getCellValue(BaseSpecificNames.endField)).toISOString();

        if ((compareStart >= startDate && compareStart <= endDate) || startDate >= compareStart && startDate <= compareEnd || (compareStart <= startDate && compareEnd >= endDate)) {
            conflicts.push(reservation.id);
        };
    }
}

async function setUnavailableAssets() {
    unavailableAssets = [];
    for (var i = 0; i < conflicts.length; i++) {
        let reservation = reservationsQuery.getRecord(conflicts[i]);
        let asset = reservation.getCellValueAsString(BaseSpecificNames.assetField);
        unavailableAssets.push(asset);
    };
}

async function setAvailableAssets() {
    availableAssets = [];
    availableAssets = allAssets.filter(record => {
        let assetName = record.getCellValue(BaseSpecificNames.assetName);
        return assetName !== null && ! unavailableAssets.includes(assetName);
    });
}

// End FUNCTIONS Section


output.markdown(`# Ultimate Booking Scheduler`)

let mode = await input.buttonsAsync('What would you like to do?',[
    {label: 'Book a New Reservation', value: 'book', variant: 'primary'},
    {label: 'Resolve Scheduling Conflicts', value: 'resolve', variant: 'danger'}
]);

output.markdown('---');

if (mode == 'book') {
    output.markdown(`## Schedule a New ${BaseSpecificNames.assetField} Reservation`);

    let person = await input.recordAsync("Reserve a " + BaseSpecificNames.assetField + " For:", peopleTable, {shouldAllowCreatingRecord: true});

    let startInput = await input.textAsync("Start Date (YYYY-MM-DD):");
    let startDate = new Date(startInput).toISOString();

    let endInput = await input.textAsync("End Date (YYYY-MM-DD):");
    let endDate = new Date(endInput).toISOString();

    findConflictingReservations(startDate, endDate);

    setUnavailableAssets();

    setAvailableAssets();

    if (availableAssets.length > 0) {

        let selectedAsset = await input.recordAsync("Requested " + BaseSpecificNames.assetField + ":", availableAssets);
        
        if (selectedAsset) {
            output.markdown(`You are about to reserve **${selectedAsset.name}** for **${person.name}** from **${startInput}** to **${endInput}**.`);

            let confirmed = await input.buttonsAsync('',[{label: 'Confirm Reservation', value: 'true', variant: 'primary'}]);

            if (confirmed) {
                await reservationsTable.createRecordAsync({
                        [BaseSpecificNames.assetField]: [{id: selectedAsset.id}],
                        [BaseSpecificNames.personField]: [{id: person.id}],
                        [BaseSpecificNames.startField]: startDate,
                        [BaseSpecificNames.endField]: endDate
                    })
                output.markdown(`*Your reservation was booked successfully. Please run the script again to book another reservation or to check for scheduling conflicts.*`)
            }
        } else {
            output.markdown(`#### No ${BaseSpecificNames.assetField} was selected. Please run the script again and select a ${BaseSpecificNames.assetField}.`)
        }
    }

    else {
        output.markdown(`#### Unfortunately, there are no available ${BaseSpecificNames.assetsTable} for this date range. Please run the script again and select new dates.`)
    }

} else {
    output.markdown(`## Check for Conflicting ${BaseSpecificNames.assetField} ${BaseSpecificNames.reservationsTable}`);
    
    let reservationsChecked = [];
    
    for (let asset of allAssets) {
        let conflictingRecords = new Set();
        let conflictingReservationsNames = new Set();

        let linkedReservations = allReservations.filter(record => {
            let assetFieldValues = record.getCellValueAsString(BaseSpecificNames.assetField);
            return assetFieldValues !== null && assetFieldValues.includes(asset.name);
        });

        for (let reservation of linkedReservations) {
            let startDate = new Date(reservation.getCellValue(BaseSpecificNames.startField)).toISOString();
            let endDate= new Date(reservation.getCellValue(BaseSpecificNames.endField)).toISOString();

            reservationsChecked.push(reservation.id);

            for (let currentReservation of linkedReservations) {
                if (!reservationsChecked.includes(currentReservation.id)) {
                    let compareStart = new Date(currentReservation.getCellValue(BaseSpecificNames.startField)).toISOString();
                    let compareEnd = new Date(currentReservation.getCellValue(BaseSpecificNames.endField)).toISOString();

                    if ((startDate >= compareStart && startDate <= compareEnd) || compareStart >= startDate && compareStart <= endDate || (startDate <= compareStart && endDate >= compareEnd)) {
                        conflictingRecords.add(reservation);
                        conflictingRecords.add(currentReservation);
                        conflictingReservationsNames.add(reservation.name);
                        conflictingReservationsNames.add(currentReservation.name);
                    }
                }
            }
        }
        
        if (conflictingRecords.size > 0) {
            overlaps.push({Asset: asset, ConflictingRecords: conflictingRecords, ConflictingRecordsNames: conflictingReservationsNames});
        }
    }
    if (overlaps.length > 0) {
        output.markdown(`#### The following (${overlaps.length}) ${BaseSpecificNames.assetsTable} have scheduling conflicts:`);
        output.table(overlaps.map( conflict => ({
            [BaseSpecificNames.assetField]: conflict.Asset.getCellValue(BaseSpecificNames.assetName),
            ['Conflicting ' + BaseSpecificNames.reservationsTable]: [...conflict.ConflictingRecordsNames].sort().join(', '),
            'Count': conflict.ConflictingRecords.size
        })))
        output.markdown(`*You will now be asked to resolve each of the scheduling conflicts shown above, one at a time. Conflicts will be grouped by ${BaseSpecificNames.assetField}. For each conflict you will be given the choice to change the ${BaseSpecificNames.assetField}, change the dates, or skip to the next conflict.*`);
        let beginResolve = await input.buttonsAsync('',[{label: 'Begin', variant: 'primary'}])
        
        for (let overlap of overlaps) {
            output.clear();
            output.markdown(`#### Conflicting ${BaseSpecificNames.assetField} ${BaseSpecificNames.reservationsTable} for ${overlap.Asset.name}`)
            let conflictingRecords = overlap.ConflictingRecords;
            output.table(Array.from(conflictingRecords).map(record => ({
                Name: record.name,
                Start: record.getCellValueAsString(BaseSpecificNames.startField),
                End: record.getCellValueAsString(BaseSpecificNames.endField),
                [BaseSpecificNames.personField]: record.getCellValueAsString(BaseSpecificNames.personField)
            })));

            output.markdown('---');

            let index = 0;

            for (let currentRecord of conflictingRecords) {
                index ++;
                output.markdown(`**${index}. ${currentRecord.name}**`)
                let method = await input.buttonsAsync('What would you like to do to this record?',[
                    {label: 'Change the ' + BaseSpecificNames.assetField, value: 'reassign', variant: 'primary'},
                    {label: 'Change the Dates', value: 'reschedule', variant: 'primary'},
                    {label: 'Skip', value: 'skip', variant: 'default'}
                ])
                if (method == 'reassign') {
                    let startDate = new Date(currentRecord.getCellValue(BaseSpecificNames.startField)).toISOString();
                    let endDate = new Date(currentRecord.getCellValue(BaseSpecificNames.endField)).toISOString();

                    findConflictingReservations(startDate, endDate)

                    setUnavailableAssets();

                    setAvailableAssets();

                    if (availableAssets.length > 0) {
                        let selectedAsset = await input.recordAsync("New " + BaseSpecificNames.assetField + ":", availableAssets);

                        if (selectedAsset) {
                            
                            await reservationsTable.updateRecordAsync(currentRecord.id, {[BaseSpecificNames.assetField]: [{id: selectedAsset.id}]});
                            output.markdown(`**Success!** *New Details: ${selectedAsset.name} from ${startDate} to ${endDate}*`);
                            altered.push(currentRecord);
                        } else {
                            output.markdown(`**WARNING:** *No changes were made to this record because no ${BaseSpecificNames.assetField} was selected.*`);
                            unaltered.push(currentRecord);
                        }
                    } else {
                        output.markdown(`*Unfortunately, there are no available ${BaseSpecificNames.assetsTable} for this date range. Please run the script again and select new dates.*`);
                        unaltered.push(currentRecord);
                    }
                } else if (method == 'reschedule') {
                    let newStart = await input.textAsync("New Start Date (YYYY-MM-DD):");
                    let startDate = new Date(newStart).toISOString();

                    let newEnd = await input.textAsync("New End Date (YYYY-MM-DD):");
                    let endDate = new Date(newEnd).toISOString();

                    await reservationsTable.updateRecordAsync(currentRecord.id, {[BaseSpecificNames.startField]: startDate,[BaseSpecificNames.endField]: endDate});
                    output.markdown(`**Success!** *New Details: ${currentRecord.getCellValueAsString(BaseSpecificNames.assetField)} from ${startDate} to ${endDate}*`);
                    altered.push(currentRecord);
                } else if (method == 'skip') {
                    output.markdown(`*No changes were made for ${currentRecord.name}.*`);
                    unaltered.push(currentRecord);
                }
                output.markdown('---');
            }
            output.markdown(`**End of shceduling conflicts for ${overlap.Asset.name}**`);
            let next = await input.buttonsAsync('',['Continue']);
        }
        output.clear();
        output.markdown(`#### Done! All conflicting ${BaseSpecificNames.assetField} ${BaseSpecificNames.reservationsTable} have been dealt with.`);
        
        output.markdown(`##### Altered ${BaseSpecificNames.assetField} ${BaseSpecificNames.reservationsTable}: ${altered.length}`)
        output.table(altered);

        output.markdown(`##### Unaltered ${BaseSpecificNames.assetField} ${BaseSpecificNames.reservationsTable}: ${unaltered.length}`)
        output.table(unaltered);
        
        output.markdown(`*Please run the script again to book a new reservation or to re-check for scheduling conflicts.*`);
    } else {
        output.markdown(`#### Good News! There are no conflicting ${BaseSpecificNames.assetField} ${BaseSpecificNames.reservationsTable}.`);
        output.markdown(`*Please run the script again to book a new reservation.*`);
    }
}