process.js

import("system.translate");
import("KeywordRegistry_basic");
import("Keyword_lib");
import("system.SQLTYPES");
import("system.db");
import("system.neon");
import("Context_lib");

/**
 * Methods used for turnover data.
 * Do not create an instance of this!
 * 
 * @class
 */
function TurnoverUtil() {}
   
/**
 * get all turnover data.
 * 
 * @param {Number} pMaxYear the maximum year
 * @param {Number} pYearCount count of years to select
 * 
 * @return {String[][]} 
 */
TurnoverUtil.getTurnoverData = function (pMaxYear, pYearCount)
{
    var turnoverCategory = translate.text('Turnover');

    var minYear = pMaxYear - pYearCount + 1;

    // load data
    var data = db.table(SqlCondition.begin()
                                    .and("SALESORDERITEM.OPTIONAL <> 1")
                                    .andPrepare("SALESORDER.SALESORDERDATE", pMaxYear, "year(#) <= ?", SQLTYPES.INTEGER)
                                    .andPrepare("SALESORDER.SALESORDERDATE", minYear, "year(#) >= ?", SQLTYPES.INTEGER)
                                    .buildSql("select '" + turnoverCategory + "', year(SALESORDERDATE) yearNum, month(SALESORDERDATE) monthNum, SALESORDERITEM.DISCOUNT discount, SALESORDERITEM.VAT vat, SALESORDERITEM.PRICE price, sum(SALESORDERITEM.QUANTITY) quantity, SALESORDERITEM.GROUPCODEID prodGroup, (" + KeywordUtils.getResolvedTitleSqlPart($KeywordRegistry.productGroupcode(), "SALESORDERITEM.GROUPCODEID") + ") prodGroupName \n\
                                                from SALESORDER  \n\
                                                join SALESORDERITEM on SALESORDERITEM.SALESORDER_ID = SALESORDER.SALESORDERID", "1=2", "group by year(SALESORDERDATE), month(SALESORDERDATE), SALESORDERITEM.GROUPCODEID, SALESORDERITEM.DISCOUNT, SALESORDERITEM.VAT, SALESORDERITEM.PRICE \n\
                                                order by yearNum, monthNum "));
    
    return data;
}

/**
 * get forecast data
 * 
 * @param {Number} pMaxYear the maximum year
 * @param {Number} pYearCount count of years to select
 * 
 * @return {String[][]} 
 */
TurnoverUtil.getForecastData = function (pMaxYear, pYearCount)
{
    var forecastCategory = translate.text('Forecast');

    var minYear = pMaxYear - pYearCount + 1;

    // load data
    var data = db.table(SqlCondition.begin()
                                    .andPrepare("FORECAST.DATE_START", pMaxYear, "year(#) <= ?", SQLTYPES.INTEGER)
                                    .andPrepare("FORECAST.DATE_START", minYear, "year(#) >= ?", SQLTYPES.INTEGER)
                                    .buildSql("select '" + forecastCategory + "', year(DATE_START) yearNum, month(DATE_START) monthNum, 0 discount, 0 vat, sum(VOLUME) price, 1 quantity, GROUPCODE prodGroup, (" + KeywordUtils.getResolvedTitleSqlPart($KeywordRegistry.productGroupcode(), "GROUPCODE") + ") prodGroupName from FORECAST", "1=2", " group by year(DATE_START), month(DATE_START), GROUPCODE order by yearNum, monthNum"))
    
    return data;
}

/**
 * get turnover data
 * 
 * @param {Number} pMaxYear the maximum year
 * @param {Number} pYearCount count of years to select
 * 
 * @return {String[][]} 
 */
TurnoverUtil.getTurnoverAndForecastData = function (pMaxYear, pYearCount)
{   
    return TurnoverUtil.getTurnoverData(pMaxYear, pYearCount).concat(TurnoverUtil.getForecastData(pMaxYear, pYearCount));
}