Name
global.PmProjectStatusReport
Description
Status Report API
Script
var PmProjectStatusReport = Class.create();
PmProjectStatusReport.prototype = {
initialize: function() {},
projectRecord: function(sysId, projectTable) {
//gs.info("Into projectRecord: " + sysId + " - " + projectTable);
var gr;
if (JSUtil.notNil(projectTable)) {
gr = new GlideRecord(projectTable); // for Teamspaces
} else {
gr = new GlideRecord("pm_project");
}
gr.get(sysId);
return gr;
},
getIcon: function(value) {
if (value == 'green')
return 'icon-success-circle';
else if (value == 'yellow')
return 'icon-not-started-circle';
else
return 'icon-warning-circle';
},
headerInfoPlain: function(sysId, weekDate, reportId) {
//gs.info("Into headerInfo: " + sysId);
var projectStatusRec, startDate, endDate, workStart, workEnd, approvedStartDate, approvedEndDate, cost, workCost;
if (reportId)
projectStatusRec = this.getProjectStatusBySysId(reportId);
else if (weekDate)
projectStatusRec = this.getProjectStatus(sysId, weekDate);
else {
projectStatusRec = this.getProjectStatusByProjectID(sysId);
if (projectStatusRec.hasNext())
projectStatusRec.next();
}
var projectRec = this.projectRecord(sysId);
if (JSUtil.notNil(projectStatusRec.getValue("start_date"))) {
var psd = projectStatusRec.start_date.getGlideObject();
startDate = psd.getLocalDate().getDisplayValue();
}
if (JSUtil.notNil(projectStatusRec.getValue("end_date"))) {
var ped = projectStatusRec.end_date.getGlideObject();
endDate = ped.getLocalDate().getDisplayValue();
}
if (JSUtil.notNil(projectStatusRec.getValue("work_start"))) {
var asd = projectStatusRec.work_start.getGlideObject();
workStart = asd.getLocalDate().getDisplayValue();
}
if (JSUtil.notNil(projectStatusRec.getValue("work_end"))) {
var aed = projectStatusRec.work_end.getGlideObject();
workEnd = aed.getLocalDate().getDisplayValue();
}
if (JSUtil.notNil(projectStatusRec.getValue("approved_start_date"))) {
var apsd = projectStatusRec.approved_start_date.getGlideObject();
approvedStartDate = apsd.getLocalDate().getDisplayValue();
}
if (JSUtil.notNil(projectStatusRec.getValue("approved_end_date"))) {
var aped = projectStatusRec.approved_end_date.getGlideObject();
approvedEndDate = aped.getLocalDate().getDisplayValue();
}
if (this._isProjectCurrencySelected(projectRec)) {
if (projectStatusRec.planned_cost_project_currency)
cost = projectStatusRec.planned_cost_project_currency.getDisplayValue();
if (projectStatusRec.actual_cost_project_currency)
workCost = projectStatusRec.actual_cost_project_currency.getDisplayValue();
} else {
if (projectStatusRec.planned_cost)
cost = projectStatusRec.planned_cost.getCurrencyDisplayValue();
if (projectStatusRec.work_cost)
workCost = projectStatusRec.work_cost.getCurrencyDisplayValue();
}
var statusReportData = {
short_description: projectRec.getValue("short_description"),
number: projectRec.getValue("number"),
project_manager_label: gs.getMessage("Project Manager"),
project_manager_value: projectRec.getDisplayValue("project_manager"),
portfolio_label: gs.getMessage("Portfolio"),
portfolio_value: projectRec.getDisplayValue("primary_portfolio"),
start_date_label: gs.getMessage("Planned Start Date"),
start_date_value: startDate,
work_start_label: gs.getMessage("Actual Start Date"),
work_start_value: workStart,
work_end_exists: JSUtil.notNil(projectRec.getValue("work_end")),
work_end_label: gs.getMessage("Actual End Date"),
work_end_value: workEnd,
end_date_label: gs.getMessage("Planned End Date"),
end_date_value: endDate,
approved_start_date_label: gs.getMessage("Approved Start Date"),
approved_start_date_value: approvedStartDate,
approved_end_date_label: gs.getMessage("Approved End Date"),
approved_end_date_value: approvedEndDate,
percent_complete_label: gs.getMessage("% Complete"),
percent_complete_value: projectStatusRec.getDisplayValue("percent_complete"),
state_label: gs.getMessage("State"),
state_value: projectStatusRec.getDisplayValue("state"),
planned_cost: cost,
work_cost: workCost,
phase: projectStatusRec.getDisplayValue("phase")
};
if (this._isProjectCurrencySelected(projectRec))
statusReportData["project_currency"] = projectRec.project_currency.code + '';
return statusReportData;
},
allProjectStatus: function(sysId) {
var projectStatus = [];
var gr = new GlideRecord("project_status");
gr.addQuery("project", sysId);
gr.orderByDesc("as_on");
gr.query();
//gs.info("statusRecordCount: " + gr.getRowCount() + " -- " + gr.getEncodedQuery());
while (gr.next()) {
projectStatus.push({
sys_id: gr.getValue("sys_id"),
label: gr.getDisplayValue("as_on"),
value: gr.getValue("as_on"),
selected: false
});
}
if (projectStatus.length > 0) {
projectStatus[0].selected = true;
} else {
projectStatus.push({
sys_id: -1,
label: gs.getMessage("None"),
value: "none",
selected: true
});
}
return projectStatus;
},
weeksInfo: function(sysId) {
//gs.info("Into weeksInfo: " + sysId);
var gr = this.projectRecord(sysId);
var startDate = new GlideDateTime(gr.getValue("start_date"));
var endDate = new GlideDateTime(gr.getValue("end_date"));
var startWeek = startDate.getWeekOfYearLocalTime();
var endWeek = endDate.getWeekOfYearLocalTime();
var weeks = [];
for (var i = startWeek; i <= endWeek; i++) {
weeks.push({
number: i,
name: gs.getMessage("Week {0}", i),
selected: (i == startWeek)
});
}
return {
name: gr.getValue("short_description"),
start_date: startDate.getDisplayValue(),
end_date: endDate.getDisplayValue(),
start_week: startWeek,
end_week: endWeek,
weeks: weeks
};
},
statusInfo: function(sysId) {
//gs.info("Into weeksInfo: " + sysId);
var gr = this.projectRecord(sysId);
var allProjectStatus = this.allProjectStatus(sysId);
return {
name: gr.getValue("short_description"),
choices: allProjectStatus,
init_value: allProjectStatus[0].value
};
},
otherStatus: function(statusRecord) {
//gs.info("Into overallStatus: " + statusRecord.getValue("sys_id"));
return this.otherStatusSummary(statusRecord);
},
otherStatusSummary: function(gr) {
var otherStatus = [];
otherStatus.push({
order: 1,
label: gs.getMessage("Overall"),
value: gr.getDisplayValue("overall_health"),
internal_value: this.getColorValue(gr.getValue("overall_health")),
comments: gr.getValue("comments"),
icon: this.getIcon(gr.getValue("overall_health"))
});
otherStatus.push({
order: 2,
label: gs.getMessage("Schedule"),
value: gr.getDisplayValue("schedule"),
internal_value: this.getColorValue(gr.getValue("schedule")),
comments: gr.getValue("schedule_comments"),
icon: this.getIcon(gr.getValue("schedule"))
});
otherStatus.push({
order: 3,
label: gs.getMessage("Cost"),
value: gr.getDisplayValue("cost"),
internal_value: this.getColorValue(gr.getValue("cost")),
comments: gr.getValue("cost_comments"),
icon: this.getIcon(gr.getValue("cost"))
});
otherStatus.push({
order: 4,
label: gs.getMessage("Resources"),
value: gr.getDisplayValue("resources"),
internal_value: this.getColorValue(gr.getValue("resources")),
comments: gr.getValue("resources_comments"),
icon: this.getIcon(gr.getValue("resources"))
});
otherStatus.push({
order: 5,
label: gs.getMessage("Scope"),
value: gr.getDisplayValue("scope"),
internal_value: this.getColorValue(gr.getValue("scope")),
comments: gr.getValue("scope_comments"),
icon: this.getIcon(gr.getValue("scope"))
});
return {
displayFields: otherStatus
};
},
statusByReportName: function(reportName, sysId, weekDate, reportId) {
var allStatus = [];
var gr;
if (reportId)
gr = this.getProjectStatusBySysId(reportId);
else if (weekDate)
gr = this.getProjectStatus(sysId, weekDate);
else {
var gr = this.getProjectStatusByProjectID(sysId);
if (gr.hasNext())
gr.next();
}
return this[reportName](gr);
},
getProjectStatus: function(sysId, weekDate) {
var gr = new GlideRecordSecure("project_status");
gr.addQuery("project", sysId);
if (JSUtil.notNil(weekDate))
gr.addQuery("as_on", "=", weekDate);
else
gr.addQuery("as_on", "<=", (new GlideDateTime()).getValue());
gr.orderByDesc("as_on");
gr.query();
//gs.info("statusRecordCount: " + gr.getRowCount() + " -- " + gr.getEncodedQuery());
if (gr.next()) {
return gr;
}
},
getProjectStatusBySysId: function(sysId) {
var gr = new GlideRecordSecure("project_status");
gr.get(sysId);
return gr;
},
getProjectStatusByProjectID: function(projID) {
var gr = new GlideRecordSecure("project_status");
gr.addQuery('project', projID);
gr.orderByDesc('as_on');
gr.orderByDesc('sys_created_on');
gr.setLimit(1);
gr.query();
return gr;
},
overallStatus: function(statusRecord) {
//gs.info("Into overallStatus: " + statusRecord.getValue("sys_id"));
var overallStatus = {
label: gs.getMessage("Overall Status"),
displayFields: []
};
if (JSUtil.notNil(statusRecord) && statusRecord.isValidRecord()) {
overallStatus.displayFields.push({
order: 1,
label: gs.getMessage("Executive Summary"),
value: statusRecord.getValue("executive_summary")
});
overallStatus.displayFields.push({
order: 2,
label: gs.getMessage("Achievements Last Week"),
value: statusRecord.getValue("achievements_last_week")
});
overallStatus.displayFields.push({
order: 3,
label: gs.getMessage("Key Activities planned for next week"),
value: statusRecord.getValue("key_activities_next_week")
});
/*overallStatus.displayFields.push({
order: 4,
label: gs.getMessage("Status"),
value: statusRecord.getDisplayValue("overall_health"),
internal_value: statusRecord.getValue("overall_health")
});*/
}
return overallStatus;
},
getColorValue: function(colorValue) {
if (JSUtil.nil(colorValue))
return "white";
return colorValue;
},
getWeekValue: function(dateValue) {
var date = new GlideDateTime(dateValue);
var week = date.getWeekOfYearLocalTime();
return week;
},
statusValues: function(sysId, startDate, endDate) {
//gs.info("Into statusValues: " + sysId);
var statusValues = {};
var projectGr = this.projectRecord(sysId);
var gr = new GlideRecord("project_status");
gr.addQuery("project", sysId);
if (JSUtil.notNil(startDate))
gr.addQuery("as_on", ">=", startDate);
if (JSUtil.notNil(endDate))
gr.addQuery("as_on", "<=", endDate);
else
gr.addQuery("as_on", "<=", (new GlideDateTime()).getValue());
gr.query();
//gs.info("statusRecordCount: " + gr.getRowCount() + " -- " + gr.getEncodedQuery());
while (gr.next()) {
var week = this.getWeekValue(gr.getValue("as_on"));
statusValues[week] = {
overall: this.getColorValue(gr.getValue("overall_health")),
schedule: this.getColorValue(gr.getValue("schedule")),
cost: this.getColorValue(gr.getValue("cost")),
resources: this.getColorValue(gr.getValue("resources")),
scope: this.getColorValue(gr.getValue("scope")),
};
}
//gs.info("statusValues: " + (new JSON()).encode(statusValues));
return statusValues;
},
getStatuses: function(projectId, endDate, limit) {
var projectStatus = [],
orderedStatus = [];
var gr = new GlideRecord("project_status");
gr.addQuery("project", projectId);
if (JSUtil.notNil(endDate)) {
var endDateObj = new GlideDate();
endDateObj.setDisplayValue(endDate);
gr.addQuery("as_on", "<=", endDateObj.getValue());
}
gr.setLimit(limit);
gr.orderByDesc("as_on");
gr.orderByDesc("sys_created_on");
gr.query();
//gs.info("statusRecordCount: " + gr.getRowCount() + " -- " + gr.getEncodedQuery());
while (gr.next()) {
projectStatus.push({
overall: this.getColorValue(gr.getValue("overall_health")),
comments: gr.getValue("comments"),
schedule: this.getColorValue(gr.getValue("schedule")),
schedule_comments: gr.getValue("schedule_comments"),
cost: this.getColorValue(gr.getValue("cost")),
cost_comments: gr.getValue("cost_comments"),
resources: this.getColorValue(gr.getValue("resources")),
resources_comments: gr.getValue("resources_comments"),
scope: this.getColorValue(gr.getValue("scope")),
scope_comments: gr.getValue("scope_comments"),
date: gr.getDisplayValue("as_on")
});
}
return projectStatus;
},
getStatusesFromLastReport: function(lastReportId, limit) {
//gs.info("Into getStatusesFromLastReport: " + lastReportId + " -- " + limit);
var lastProjectStatus = this.getProjectStatusBySysId(lastReportId);
var projectId = lastProjectStatus.getValue("project");
var endDate = lastProjectStatus.getValue("as_on");
var projectStatus = [];
var gr = new GlideRecord("project_status");
gr.addQuery("project", projectId);
if (JSUtil.notNil(endDate))
gr.addQuery("as_on", "<=", endDate);
else
gr.addQuery("as_on", "<=", (new GlideDateTime()).getValue());
gr.setLimit(limit || 10);
gr.orderBy("as_on");
gr.query();
//gs.info("statusRecordCount: " + gr.getRowCount() + " -- " + gr.getEncodedQuery());
while (gr.next()) {
projectStatus.push({
overall: this.getColorValue(gr.getValue("overall_health")),
schedule: this.getColorValue(gr.getValue("schedule")),
cost: this.getColorValue(gr.getValue("cost")),
resources: this.getColorValue(gr.getValue("resources")),
scope: this.getColorValue(gr.getValue("scope")),
date: gr.getValue("as_on")
});
}
return projectStatus;
},
statusReport: function(sysId, startDate, endDate) {
//gs.info("Into statusReport: " + sysId);
var statusReport = {};
// high chart plots on the based on the index of the category
statusReport['yAxis'] = {
categories: [gs.getMessage('Scope'), gs.getMessage('Resource'),
gs.getMessage('Cost'), gs.getMessage('Schedule'),
gs.getMessage('Overall')
],
title: null
};
var weeksInfo = this.weeksInfo(sysId);
var startWeek = weeksInfo.start_week;
var weeks = weeksInfo.weeks;
var statusValues = this.statusValues(sysId, startDate, endDate);
var xAxisCategories = [];
for (var i = 0; i < weeks.length; i++) {
xAxisCategories.push(weeks[i].name);
}
statusReport['xAxis'] = {
categories: xAxisCategories
}
// now re-map the start week to zero index
var data = [];
for (var i = 0; i < weeks.length; i++) {
var week = weeks[i].number;
var statusValue = statusValues[week];
var valueArray = ['scope', 'resources', 'cost', 'schedule', 'overall'];
for (var j = 0; j < valueArray.length; j++) {
var color = "white";
if (JSUtil.notNil(statusValue))
color = statusValue[valueArray[j]];
data.push({
x: i,
y: j,
color: color
});
}
}
statusReport['data'] = data;
return statusReport;
},
fiscalPeriodsForIds: function(fiscalPeriodIds) {
//gs.info("Into fiscalPeriodsForIds: " + fiscalPeriodIds.join(','));
var fiscalPeriodsArray = [];
var fiscalPeriods = new GlideRecord("fiscal_period");
fiscalPeriods.addQuery("sys_id", "IN", fiscalPeriodIds.join());
fiscalPeriods.orderBy("start_date_time");
fiscalPeriods.query();
while (fiscalPeriods.next()) {
fiscalPeriodsArray.push({
sys_id: fiscalPeriods.getValue("sys_id"),
name: fiscalPeriods.getValue("name"),
start_date_time: fiscalPeriods.getValue("fiscal_start_date_time"),
end_date_time: fiscalPeriods.getValue("fiscal_end_date_time")
})
}
//gs.info("return fiscalPeriodsForIds: " + (new JSON()).encode(fiscalPeriodsArray));
return fiscalPeriodsArray;
},
fiscalPeriodsWithinDateRange: function(startDate, endDate) {
var breakdownFiscalType = new FinancialsForPPM().getBreakdownUnit();
var fiscalPeriodsArray = [];
if (JSUtil.notNil(startDate) && JSUtil.notNil(endDate)) {
var fiscalPeriods = new GlideRecord("fiscal_period");
fiscalPeriods.addQuery("fiscal_type", breakdownFiscalType || "month");
fiscalPeriods.addQuery("fiscal_start_date_time", ">=", startDate.getValue());
fiscalPeriods.addQuery("fiscal_end_date_time", "<=", endDate.getValue());
fiscalPeriods.orderBy("start_date_time");
fiscalPeriods.query();
while (fiscalPeriods.next()) {
fiscalPeriodsArray.push({
sys_id: fiscalPeriods.getValue("sys_id"),
name: fiscalPeriods.getValue("name"),
start_date_time: fiscalPeriods.getValue("fiscal_start_date_time"),
end_date_time: fiscalPeriods.getValue("fiscal_end_date_time"),
start_date : fiscalPeriods.start_date_time.getGlideObject().getGlideDateTime().getDate(),
end_date : fiscalPeriods.end_date_time.getGlideObject().getGlideDateTime().getDate()
});
}
}
return fiscalPeriodsArray;
},
getFiscalPeriodForDate: function(date) {
//gs.info("Into getFiscalPeriodForDate - date: " + date);
if (JSUtil.notNil(date)) {
// var fiscalYear = PPMFiscalPeriod.getFiscalYearForDate(date);
var fiscalType = new FinancialsForPPM().getBreakdownUnit();
var fiscalPeriods = new GlideRecord("fiscal_period");
// fiscalPeriods.addQuery("fiscal_year", fiscalYear.getValue("sys_id"));
fiscalPeriods.addQuery("fiscal_type", fiscalType); // Take it from fiscal unit
fiscalPeriods.addQuery("fiscal_start_date_time", "<=", date);
fiscalPeriods.addQuery("fiscal_end_date_time", ">=", date);
fiscalPeriods.orderBy("start_date_time");
fiscalPeriods.query();
//gs.info("getFiscalPeriodForDate query: " + fiscalPeriods.getEncodedQuery());
if (fiscalPeriods.next()) {
//gs.info("getFiscalPeriodForDate period: " + fiscalPeriods.getValue("name"));
return fiscalPeriods;
}
}
},
breakdownRecords: function(sysId, fiscalPeriodSysIds) {
//gs.info("Into breakdownRecords: " + sysId);
var budgetValues = {};
var gr = new GlideRecord("cost_plan_breakdown");
gr.addQuery("breakdown_type", "task");
gr.addQuery("task", sysId);
if (JSUtil.notNil(fiscalPeriodSysIds))
gr.addQuery("fiscal_period", "IN", fiscalPeriodSysIds.join(","));
gr.query();
//gs.info("breakdownRecords Count: " + gr.getRowCount() + " -- " + gr.getEncodedQuery());
return gr;
},
getData: function(budgetActualValues, fiscalPeriodId) {
var budgetActual = {
budget: 0,
actual: 0
};
if (JSUtil.notNil(budgetActualValues) && JSUtil.notNil(fiscalPeriodId)) {
for (var i = 0; i < budgetActualValues.length; i++) {
if (fiscalPeriodId == budgetActualValues[i].fiscal_period) {
// entries for capex and opex
budgetActual.budget = parseFloat(budgetActual.budget) + parseFloat(budgetActualValues[i].budget);
budgetActual.actual = parseFloat(budgetActual.actual) + parseFloat(budgetActualValues[i].actual);
}
}
}
return budgetActual;
},
budgetVsActual: function(sysId) { // Cost Budget Vs Actual
//gs.info("Into budgetVsActual: " + sysId);
var arrayUtil = new ArrayUtil();
var budgetActualValues = [];
var fiscalPeriodSysIds = [];
var projectRec = this.projectRecord(sysId);
var gr = this.breakdownRecords(sysId);
while (gr.next()) {
var fiscalPeriodId = gr.getValue("fiscal_period");
//check if customer want to show function currency or project currency
if (this._isProjectCurrencySelected(projectRec)) {
budgetActualValues.push({
budget: gr.getValue("cost_project_currency"),
expense_type: gr.getValue("expense_type"),
actual: gr.getValue("actual"), //need to change
fiscal_period: fiscalPeriodId
});
} else {
budgetActualValues.push({
budget: gr.getValue("cost_default_currency"),
expense_type: gr.getValue("expense_type"),
actual: gr.getValue("actual"),
fiscal_period: fiscalPeriodId
});
}
if (!arrayUtil.contains(fiscalPeriodSysIds, fiscalPeriodId))
fiscalPeriodSysIds.push(fiscalPeriodId);
}
//gs.info("fiscalPeriodSysIds: " + fiscalPeriodSysIds.join(","));
var fiscalPeriods = this.fiscalPeriodsForIds(fiscalPeriodSysIds);
var xAxis = [];
var leastStartDate, highestEndDate;
for (var i = 0; i < fiscalPeriods.length; i++) {
if (JSUtil.nil(leastStartDate)) {
leastStartDate = new GlideDateTime(fiscalPeriods[i].start_date_time);
} else {
var startDate = new GlideDateTime(fiscalPeriods[i].start_date_time);
if (startDate.compareTo(leastStartDate) < 0)
leastStartDate = startDate;
}
if (JSUtil.nil(highestEndDate)) {
highestEndDate = new GlideDateTime(fiscalPeriods[i].end_date_time);
} else {
var endDate = new GlideDateTime(fiscalPeriods[i].end_date_time);
if (endDate.compareTo(highestEndDate) > 0)
highestEndDate = endDate;
}
}
var fiscalPeriods = this.fiscalPeriodsWithinDateRange(leastStartDate, highestEndDate);
var budgetData = [];
var actualData = [];
for (var i = 0; i < fiscalPeriods.length; i++) {
xAxis.push(fiscalPeriods[i].name);
var budgetActualValue = this.getData(budgetActualValues, fiscalPeriods[i].sys_id)
budgetData.push(budgetActualValue.budget);
actualData.push(budgetActualValue.actual);
}
// Prepare the Chart Options
var chartOptions = {
xAxis: {
categories: xAxis
},
series: [{
data: budgetData,
name: 'Planned'
}, {
data: actualData,
name: 'Actual'
}],
yAxis_title: this._isProjectCurrencySelected(projectRec) ? projectRec.project_currency.symbol + '' : PPMCurrencyHelper.defaultCurrencySymbol(),
};
if (this._isProjectCurrencySelected(projectRec))
chartOptions["projectCurrencySelected"] = true;
return chartOptions;
},
actualVsAllocated: function(sysId) { // Resource Actual Vs Allocated
//gs.info("Into actualVsAllocated: " + sysId);
var leastStartDate, highestEndDate;
var gr = this.resourcePlans(sysId);
while (gr.next()) {
if (JSUtil.nil(leastStartDate)) {
leastStartDate = new GlideDateTime(gr.getValue("start_date"));
} else {
var startDate = new GlideDateTime(gr.getValue("start_date"));
if (startDate.compareTo(leastStartDate) < 0)
leastStartDate = startDate;
}
if (JSUtil.nil(highestEndDate)) {
highestEndDate = new GlideDateTime(gr.getValue("end_date"));
} else {
var endDate = new GlideDateTime(gr.getValue("end_date"));
if (endDate.compareTo(highestEndDate) > 0)
highestEndDate = endDate;
}
}
if (JSUtil.notNil(leastStartDate)) {
var fiscalPeriodRecord = this.getFiscalPeriodForDate(leastStartDate);
if (JSUtil.notNil(fiscalPeriodRecord) && fiscalPeriodRecord.isValidRecord())
leastStartDate = new GlideDateTime(fiscalPeriodRecord.getValue("fiscal_start_date_time"));
}
if (JSUtil.notNil(highestEndDate)) {
var fiscalPeriodRecord = this.getFiscalPeriodForDate(highestEndDate);
if (JSUtil.notNil(fiscalPeriodRecord) && fiscalPeriodRecord.isValidRecord())
highestEndDate = new GlideDateTime(fiscalPeriodRecord.getValue("fiscal_end_date_time"));
}
var fiscalPeriods = this.fiscalPeriodsWithinDateRange(leastStartDate, highestEndDate);
var xAxis = [],
actualData = [],
allocatedData = [];
for (var i = 0; i < fiscalPeriods.length; i++) {
xAxis.push(fiscalPeriods[i].name);
var resourceData = this.actualVsAllocatedHours(sysId, fiscalPeriods[i]);
actualData.push(resourceData.actual);
allocatedData.push(resourceData.allocated);
}
// Prepare the Chart Options
var chartOptions = {
xAxis: {
categories: xAxis
},
series: [{
data: allocatedData,
name: 'Allocated'
},
{
data: actualData,
name: 'Actual'
}
],
yAxis_title: gs.getMessage("Hours")
};
return chartOptions;
},
resourcePlans: function(topTaskId) {
//gs.info("resourcePlans: " + topTaskId);
var gr = new GlideRecord("resource_plan");
gr.addQuery("top_task", topTaskId);
gr.orderBy("start_date");
gr.query();
//gs.info("resourcePlans query: " + gr.getEncodedQuery());
return gr;
},
getExecutiveSummary: function(projectStatusId) {
//gs.info("getExecutiveSummary: " + projectStatusId);
var statusRecord = this.getProjectStatusBySysId(projectStatusId);
return this.overallStatus(statusRecord);
},
getOtherSummary: function(projectStatusId) {
//gs.info("getOtherSummary: " + projectStatusId);
var statusRecord = this.getProjectStatusBySysId(projectStatusId);
return this.otherStatusSummary(statusRecord);
},
getVariance: function(startDate, workStart) {
//gs.info("Into getVariance: " + startDate + " - " + workStart);
var variance;
var start = new GlideDateTime(startDate);
var work = new GlideDateTime();
if (JSUtil.notNil(workStart)) {
work = new GlideDateTime(workStart);
}
variance = GlideDateTime.subtract(start, work);
var value = "";
if (work.compareTo(start) > 0 && (work.getDate().getValue() != start.getDate().getValue())) {
value = variance.getDayPart() + " Days";
}
return {
value: value,
show_icon: JSUtil.notNil(value)
};
},
getTasksByQuery: function(reportBaselineSysId, queryString, tableName) {
//gs.info("Into getTasksByQuery: " + projectSysId + " - " + queryString);
var fields_array = [gs.getMessage("Short Description"), gs.getMessage("Planned Start Date"),
gs.getMessage("Actual Start Date"), gs.getMessage("State"), gs.getMessage("Variance")
];
var tasks = [];
var gr = new GlideRecord(tableName);
gr.addEncodedQuery(queryString);
gr.orderBy("start_date");
gr.query();
//gs.info("getTasksByQuery Count: " + gr.getRowCount() + " - " + gr.getEncodedQuery());
while (gr.next()) {
var variance = this.getVariance(gr.getValue("start_date"), gr.getValue("work_start"));
tasks.push({
short_description: gr.getValue("short_description"),
start_date: gr.getDisplayValue("start_date"),
work_start: gr.getDisplayValue("work_start"),
variance: variance.value,
variance_show_icon: variance.show_icon,
state: gr.getDisplayValue("state")
});
}
var response = {
fields_array: fields_array,
tasks: tasks
};
//gs.info("getTasksByQuery: " + (new JSON()).encode(response));
return response;
},
actualVsAllocatedHours: function(projectSysId, fiscalPeriod) {
//gs.info("Into actualVsAllocatedHours: " + projectSysId + " - " + fiscalPeriod.sys_id);
var metrics = {};
metrics.actual = 0;
metrics.allocated = 0;
var refCol = 'resource_plan.top_task';
//gs.info("refCol: " + refCol);
var gr = new GlideAggregate('resource_allocation');
gr.addQuery(refCol, projectSysId);
gr.addQuery('start_date', '>=', fiscalPeriod.start_date);
gr.addQuery('start_date', '<=', fiscalPeriod.end_date);
gr.addQuery('booking_type', 1);
gr.addAggregate('SUM', 'allocated_hours');
gr.setGroup(false);
gr.query();
//gs.info("resource_allocation Query: " + gr.getEncodedQuery());
if (gr.next()) {
var allocated = gr.getAggregate('SUM', 'allocated_hours');
if (!gs.nil(allocated))
metrics.allocated = parseFloat(allocated);
}
var timeCard = new GlideAggregate('time_card');
// Time card as top task column -- Check with Pradeep.
timeCard.addAggregate('SUM', 'total');
timeCard.addQuery('week_starts_on', '>=', fiscalPeriod.start_date);
timeCard.addQuery('week_starts_on', '<=', fiscalPeriod.end_date);
timeCard.addQuery('state', 'Processed');
timeCard.setGroup(false);
var taskSubQuery = timeCard.addJoinQuery('planned_task', 'task', 'sys_id');
taskSubQuery.addCondition('top_task', projectSysId);
timeCard.query();
//gs.info("time_card Query: " + timeCard.getEncodedQuery());
if (timeCard.next()) {
var totalHours = timeCard.getAggregate('SUM', 'total');
if (!gs.nil(totalHours))
metrics.actual = parseFloat(totalHours);
}
//gs.info("metrics: " + (new JSON()).encode(metrics));
return metrics;
},
getReportBaselineId: function(reportId) {
var gr = new GlideRecord('project_status_report_baseline');
if (!gr.isValid())
return;
gr.addQuery('status_report', reportId);
gr.query();
if (gr.next())
return gr.getValue('sys_id');
return;
},
createStatusReportBaseline: function(projectId, statusReportId) {
try {
var extraFieldsMap = {
status_report: statusReportId,
cost_data : JSON.stringify(this.budgetVsActual(projectId)),
resource_data : JSON.stringify(this.actualVsAllocated(projectId))
};
var metaInfo = {
name: 'Project Status Report Basleine',
baselineConfig: '28bdc09973b300101001fcf2c4f6a779', //hardcoded config
primarySysId: projectId,
considerChildren: true,
postAction: null,
extraFieldMaps: extraFieldsMap
};
var baselineAPI = new BaselineAPI();
baselineAPI.startBaselineJob(metaInfo);
} catch (e) {
gs.debug("Project Status Report Baseline: Error occured in occured in creating the job");
gs.debug(JSON.stringify(e));
}
},
_isProjectCurrencySelected: function(projectGR) {
return projectGR.status_report_currency == "project_currency";
},
type: 'PmProjectStatusReport'
};Sys ID
9e75b5e09f331200598a5bb0657fcf19