contentProcess.js

import("system.datetime");
import("system.db");
import("system.result");
import("system.translate");
   
var sumOfMonthTurnover = db.table("select year(SALESORDERDATE) yearNum, 'turnover', month(SALESORDERDATE) monthNum, sum(NET + VAT) from SALESORDER group by year(SALESORDERDATE), month(SALESORDERDATE) order by yearNum, monthNum");
var sumOfMonthForecast = db.table("select year(DATE_START) yearNum, 'forecast', month(DATE_START) monthNum, sum(VOLUME) from SALESPROJECT_FORECAST group by year(DATE_START), month(DATE_START) order by yearNum, monthNum");


var chartData = [];

var turnoverSkippedCount = 0;
var forecastSkippedCount = 0;

for (let i = 0; i < 4; i++)
{
    var year = i + 2016;
    
    _addMonthRows(year);
}

result.object(chartData);

function _addRow(pRow) 
{
    //logging.log(pRow.toSource())
    // month = 0 --> sum of the whole year
    var parent = "";
    var dateDisplay = pRow[0];
    
    if (pRow[2] != 0) 
    {
        parent = pRow[0] + "0" + pRow[1].trim();

        var rowDate = new Date(pRow[0], pRow[2]-1);
        dateDisplay = datetime.toDate(rowDate.getTime(), "MMM yyyy", "UTC")
    }
    
    chartData.push([pRow[0] + pRow[2] + pRow[1].trim(), parent, 
                    ((pRow[1].trim() == "turnover") ? translate.text("Turnover") : translate.text("Forecast")), parseFloat(pRow[3]), dateDisplay]);
}

function _addMonthRows(pYear) 
{
    var turnoverSkippedCount = 0;
    
    var forecastSkippedCount = 0;

    var turnoverYearSum = 0;
    var forecastYearSum = 0;

    var filteredTurnover = sumOfMonthTurnover.filter(function(row)
    {
        return row[0] == pYear
    });
    
    var filteredForecast = sumOfMonthForecast.filter(function(row)
    {
        return row[0] == pYear
    });

    for (let i = 1; i <= 12; i++) 
    {
        var turnoverSum = filteredTurnover[i - turnoverSkippedCount - 1];
        var forecastSum = filteredForecast[i - forecastSkippedCount - 1];
        
        if (turnoverSum != undefined && turnoverSum[0] == pYear && turnoverSum[2] == i.toString()) 
        {
            _addRow(turnoverSum);
            turnoverYearSum += turnoverSum[3];
        }
        else
        {
            _addRow([year.toString(), 'turnover', i, 0.0]);
            
            turnoverSkippedCount++;
        }
        
        if (forecastSum != undefined && forecastSum[0] == pYear && forecastSum[2] == i) 
        {
            _addRow(forecastSum);
            forecastYearSum += forecastSum[3];
        }
        else
        {
            _addRow([year.toString(), 'forecast', i, 0.0]);
            
            forecastSkippedCount++;
        }
    }
    
    _addRow([year.toString(), 'turnover', 0, turnoverYearSum]);
    _addRow([year.toString(), 'forecast', 0, forecastYearSum]);
}