Adjust dates of Gantt workflows

							
							//Copyright © 2020 by Business Automated
//Update dates of tasks in Gantt chart in Airtable. To be used together with the Airtable Gantt and Scripting block.

// Select the To Do table and records
let table = base.getTable("Table 1");  // <== change here the Table name
let toDoRecords = await table.selectRecordsAsync();

let nameOfStartDate  = "Start Date"; // <== change here the to the column name with start date
let nameOfEndDate = "End Date";       // <== change here the to the column name with end date
let nameOfDuration = "Duration" ;      // <== change here the to the column name with duration 
let nameOfSuccessor = "Successor Task" ; // <== change here the to the column name with successor task



// Prompt the user to pick a record 
// If this script is run from a button field, this will use the button's record instead.
let record = await input.recordAsync('Select the start record', table);

//check for no empty record
if (record) {
    // Customize this section to handle the selected record
    // You can use record.getCellValue("Field name") to access
    // cell values from the record
    output.text(`You selected this record: ${record.name}`);
} else {
    output.text('No record was selected');
    return;
}

// pick up data from the starting record 
let startDate = record.getCellValue(nameOfStartDate);  
let endDate = new Date(startDate); 
endDate.setDate(endDate.getDate() - 1);

// exit on start date

if (startDate == null){
    output.text("No start Date - exiting the script");
    return;
}

//creat Hash table for faster updates vs updates in the database
let ganttHashTable = {};
/**
 * @param {DetailedToDoTable_Record} record
 */
function createGanttHashTable(record){
    ganttHashTable[record.id] = {
        "Date Start": record.getCellValue(nameOfStartDate),   
        "Date End": record.getCellValue(nameOfEndDate),       
        "Duration": record.getCellValue(nameOfDuration),     
        "Successors": record.getCellValue(nameOfSuccessor) 
    }
    if(ganttHashTable[record.id]["Successors"]){
        for (let successor of ganttHashTable[record.id]["Successors"]){
            let newRecord = toDoRecords.getRecord(successor.id);
            createGanttHashTable(newRecord);
            //console.log(record);
        }
    }    
}

createGanttHashTable(record);
//console.log(Object.keys(ganttHashTable).length);


//declare main update fuction which will be use recurisviely 
/**
 * @param {string | number | Date} endDate
 * @param {Record} record
 * @param {string} level
 */


async function updateDates (endDate, record, level){
    
    //refresh 
    let recordNew = toDoRecords.getRecord(record.id);
    
    level = level + "=";
    //convert end date to start
    
    let newStartDate = new Date();
    let newEndDate = new Date(endDate);
    let daysToAdd = ganttHashTable[recordNew.id]["Duration"];
    //console.log("Dats to add ===" + daysToAdd);

    for (let day = 0 ; day < daysToAdd; day++){
        newEndDate.setDate(newEndDate.getDate() + 1);

        if (newEndDate.getDay() == 0){
            newEndDate.setDate(newEndDate.getDate() + 1);
        } else if (newEndDate.getDay() == 6){
            newEndDate.setDate(newEndDate.getDate() + 2);
        }
        if (day==0){
            newStartDate = new Date(newEndDate);
        };
        
        
    }
 
    //check current date
    let currentStartDate = new Date(ganttHashTable[recordNew.id]["Date Start"]);
    let currentEndDate = new Date(ganttHashTable[recordNew.id]["Date End"]);



    // see if already existing date is not further away
    if (currentStartDate.getTime() > newStartDate.getTime()){
        
        newEndDate = new Date(currentEndDate);
    } else {
        
        ganttHashTable[recordNew.id] = {
            "Date Start": newStartDate,
            "Date End": newEndDate,
            "Duration": ganttHashTable[recordNew.id]["Duration"],
            "Successors": ganttHashTable[recordNew.id]["Successors"] 
        }
        //update records Async in Airtable

        

    };
    let successorRecords = ganttHashTable[recordNew.id]["Successors"];
    //if successor Record exist iterate over the successors
    if (successorRecords){
        for (let successorRecord of successorRecords){
            //console.log(record);
            //adding fuctions and properties to the object            
            let newRecord = toDoRecords.getRecord(successorRecord.id);
            //console.log(record);
            //output.text(level+" Successor task: " + record.getCellValue("Name"));
            //pass current event end date and successor record
            await updateDates(newEndDate, newRecord, level);
        }
    }
        
};

//adjust start for the starting record to be similar in format(-1) to "end date" of predecessor

let level = ""; 
await updateDates(endDate,record, level);

let updateArray = []
for (let [key,value] of Object.entries(ganttHashTable)){
   updateArray.push({
       "id": key, 
        "fields": {
            [nameOfStartDate]: value["Date Start"],
            [nameOfEndDate] : value["Date End"] 
            }
   })

}

// batch update of exisitng records
let updateRecordsLenght = updateArray.length
while (updateArray.length > 0) {
    await table.updateRecordsAsync(updateArray.slice(0, 50));
    updateArray = updateArray.slice(50);
    output.text(`Updated time entries for ${updateRecordsLenght-updateArray.length}/${updateRecordsLenght} task`);
}