Task value calculations

spi_cpi.js

const { ProjectSchedulerRepository } = require('../../repository/projectscheduler');
const { ProjectTaskRepository } = require('../../repository/project-task');
const { CalendarRepository } = require('../../repository/calendars');
const { ConstructManageApi } = require('../../service-apis/construct-manage-api');

const error = require("../action-helper/common-error");
const throwError = error.throwError;
const objectID = require('mongodb').ObjectID;
const moment = require('moment');

class GetProjectSPI {
    constructor(project_id) {
        this.project_id = project_id;
    }
        // to get dates list between two dates
    async getDateArray(start, end){
        let arr = new Array();
        let dt = new Date(start);
        while (dt <= end) {
            arr.push(new Date(dt));
            dt.setDate(dt.getDate() + 1);
        }
        return arr;
    }

    // to get difference between two dates
    async dateDiff(startDate, endDate){
        const diffTime = startDate - endDate;
        const diffDays = Math.ceil(diffTime / (1000 * 60 * 60 * 24));
        return diffDays;
    }

    async execute() {
        try{
            let projectSchedulerRepo = new ProjectSchedulerRepository();
            let projectTaskRepo = new ProjectTaskRepository();
            let calendarRepo = new CalendarRepository();

            // check if valid project_id
            let project_details = await ConstructManageApi.getProjectDetails(this.project_id);

            if(project_details.id != this.project_id){
                throwError(400, "Invalid Project ! Please choose a valid project from the project dropdown menu");
            }

            // get schedule details for the project
            let projectScheduleDetails = await projectSchedulerRepo.getPublishedProjectScheduler(this.project_id);

            // get all project tasks
            let projectTasks = projectScheduleDetails.tasks;

            // get project calendar
            let projectCalendar = await calendarRepo.getAllProjectCalendars(this.project_id);

            let totalBCWP = 0; //earned value (EV or BCWP)
            let totalBCWS = 0; // BCWS
            let total_estimated_cost = 0; // Total Estimated Cost
            let total_actual_cost = 0; // Total Actual Cost

            let expectedprogressPercentage;
            // get non working days
            for(let taskDetails of projectTasks){
                if(taskDetails.task_id !== this.project_id){

                    // get the calendar
                    let taskCalendar = projectCalendar.find(ele => objectID(ele._id).equals(objectID(taskDetails.calendar_id)));
                    //let taskCalendar = projectCalendar.filter(ele => ele._id == taskDetails.calendar_id);

                    if(taskCalendar){
                        let working_days = taskCalendar.working_days;
                        let calendarHolidays = taskCalendar.holidays;

                        // get the non working days
                        let non_working_days = [];
                        for (let key in working_days) {
                            if(working_days[key] === 0){
                                non_working_days.push(key);
                            }
                        }

                        // get the holiday list
                        let holiday_list = [];
                        for(let holiday of calendarHolidays){
                            holiday_list.push(moment(holiday.date).format('DD-MM-YYYY'));
                        }

                        // get date list between planned start date and planned end date
                        let plannedStartDate = taskDetails.planned_start_date;
                        let taskDuration = taskDetails.duration;
                        let startDate = moment(plannedStartDate);
                        let endDate = moment(startDate).add(taskDuration, 'day');

                        //get the date list
                        let dateList = await this.getDateArray(startDate, endDate);

                        let dayCount = 0;
                        for(let dates of dateList){
                            let working_day = moment(dates).format('dddd').toLowerCase();
                            if (!(holiday_list.includes(dates)) && !(non_working_days.includes(working_day))){
                                dayCount++;
                            }
                        }

                        let working_days_count = dayCount;

                        // calculate total_estimated_cost
                        taskDetails.estimated_cost_material = taskDetails.estimated_cost_material != null ? taskDetails.estimated_cost_material : 0;
                        taskDetails.estimated_cost_resource = taskDetails.estimated_cost_resource != null ? taskDetails.estimated_cost_resource : 0;
                        taskDetails.estimated_lumpsum_cost = taskDetails.estimated_lumpsum_cost != null ? taskDetails.estimated_lumpsum_cost : 0;
                        let task_total_estimated_cost = taskDetails.estimated_cost_material + taskDetails.estimated_cost_resource + taskDetails.estimated_lumpsum_cost;

                        // calculate total_actual_cost
                        taskDetails.actual_cost_material = taskDetails.actual_cost_material != null ? taskDetails.actual_cost_material : 0;
                        taskDetails.actual_cost_resource = taskDetails.actual_cost_resource != null ? taskDetails.actual_cost_resource : 0;
                        taskDetails.actual_lumpsum_cost = taskDetails.actual_lumpsum_cost != null ? taskDetails.actual_lumpsum_cost : 0;
                        let task_total_actual_cost = taskDetails.actual_cost_material + taskDetails.actual_cost_resource + taskDetails.actual_lumpsum_cost;

                        // Planned cost per day
                        let planned_cost_per_day = task_total_estimated_cost / working_days_count;

                         // Days Elapsed = Today's Date - Planned Start Date (If result is positive else 0)
                        let todays_date = moment();
                        // let daysElapsed = working_days_count > taskDuration ? taskDuration : working_days_count;
                        let daysDifference = await this.dateDiff(todays_date, startDate);
                        let daysElapsed = daysDifference > taskDuration ? taskDuration : daysDifference;

                        // Expected Progress of a task = Days Elapsed / Total duration
                        daysElapsed = daysElapsed > 0 ? daysElapsed : 0;
                        let expectedProgress = daysElapsed / taskDuration;
                        expectedprogressPercentage = expectedProgress;

                        // BCWS (Budgeted Cost of Work Scheduled)  = Expected Progress * Cost Assigned to the task
                        let taskBCWS = expectedprogressPercentage * task_total_estimated_cost;

                        // EV or BCWP (Earned Value) = Updated Progress Percentage * Costs assigned to the Task
                        let taskProgress = taskDetails.progress;
                        let updatedProgressPercentage = (taskProgress / 100);
                        let taskBCWP = (updatedProgressPercentage * task_total_estimated_cost);
                        total_estimated_cost = total_estimated_cost + task_total_estimated_cost;
                        total_actual_cost = total_actual_cost + task_total_actual_cost;

                       // Total
                        totalBCWS = totalBCWS + taskBCWS;
                        totalBCWP = totalBCWP + taskBCWP;
                    } // end of if task calendar
                }
            } // end of loop

            // CPI = EarnedValue(BCWP) / Total Actual Cost
            let cpi_value = totalBCWP / total_actual_cost;
            let CPI = cpi_value > 0 ? cpi_value : 0;

            // if Total Estimated Cost in a project is 0, then show "N/a" for Earned Value, SPI and Scheduled Value fields of the task board
            if ( total_estimated_cost === 0 ) {
                let result = {
                        "earned_value": "N/A",
                        "BCWS": "N/A",
                        "SPI": "N/A",
                        "CPI": "N/A",
                        "total_project_value": total_estimated_cost,
                        "allocated_value": total_estimated_cost
                    }
                    return result
                }

            // In a case where Total SV or BCWS for a project is 0, then show the SPI as 100%
            if( totalBCWS === 0 ){
                let result = {
                    "earned_value": Number(totalBCWP.toFixed(2)),
                    "BCWS": Number(totalBCWS.toFixed(2)),
                    "SPI": 1,
                    "CPI": Number(CPI.toFixed(2)),
                    "total_project_value": total_estimated_cost,
                    "allocated_value": total_estimated_cost
                }
                return result
            }
            else {
                  // SPI = total BCWP / totalBCWS
                    let SPI = totalBCWP / totalBCWS;
                    let result = {
                        "earned_value": Number(totalBCWP.toFixed(2)),
                        "BCWS": Number(totalBCWS.toFixed(2)),
                        "SPI": Number(SPI.toFixed(2)),
                        "CPI": Number(CPI.toFixed(2)),
                        "total_project_value": total_estimated_cost,
                        "allocated_value": total_estimated_cost
                    }
                    return result;
            }
        }
        catch(error){
           throw error;
        }
    }
}

exports.GetProjectSPI = GetProjectSPI;