process.js

import("Sql_lib");
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
 * @param {String} [pSalesprojectId  = undefined] if set, select only from this salesproject
 * 
 * @return {String[][]} 
 */
TurnoverUtil.getTurnoverData = function (pMaxYear, pYearCount, pSalesprojectId)
{
    var turnoverCategory = translate.text("Turnover");
    var minYear = pMaxYear - pYearCount + 1;
    var sqlMask = new SqlMaskingUtils();
        
    // load data
    
    return newSelect([
            "'" + turnoverCategory + "'", 
            sqlMask.yearFromDate("SALESORDERDATE"), 
            sqlMask.monthFromDate("SALESORDERDATE"),
            "SALESORDERITEM.DISCOUNT",
            "SALESORDERITEM.VAT",
            "SALESORDERITEM.PRICE",
            "sum(SALESORDERITEM.QUANTITY)",
            "SALESORDERITEM.GROUPCODEID",
            KeywordUtils.getResolvedTitleSqlPart($KeywordRegistry.productGroupcode(), "SALESORDERITEM.GROUPCODEID")
        ])
        .from("SALESORDER")
        .join("SALESORDERITEM", "SALESORDERITEM.SALESORDER_ID = SALESORDER.SALESORDERID")
        .where("SALESORDER.ORDERTYPE", "ORDTYPEINVO")
        .and("SALESORDER.ORDERSTATUS", "1")
        .and("SALESORDER.CANCELLATION", "1", SqlBuilder.NOT_EQUAL())
        .and("SALESORDERITEM.OPTIONAL", "1", SqlBuilder.NOT_EQUAL())
        .and("SALESORDER.SALESORDERDATE", pMaxYear, sqlMask.yearFromDate("#") + " <= ?", SQLTYPES.INTEGER)
        .and("SALESORDER.SALESORDERDATE", minYear, sqlMask.yearFromDate("#") + " >= ?", SQLTYPES.INTEGER)
        .andIfSet("SALESORDER.SALESPROJECT_ID", pSalesprojectId)
        .groupBy([
            sqlMask.yearFromDate("SALESORDERDATE"), 
            sqlMask.monthFromDate("SALESORDERDATE"),
            "SALESORDERITEM.GROUPCODEID",
            "SALESORDERITEM.DISCOUNT",
            "SALESORDERITEM.VAT",
            "SALESORDERITEM.PRICE"
        ])
        .orderBy([
            sqlMask.yearFromDate("SALESORDERDATE"), 
            sqlMask.monthFromDate("SALESORDERDATE")
        ])
        .table();
}

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

    var minYear = pMaxYear - pYearCount + 1;

    // load data
    var forecastSelect = newSelect("'" + 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")
                            .where("FORECAST.DATE_START", pMaxYear, "year(#) <= ?", SQLTYPES.INTEGER)
                            .and("FORECAST.DATE_START", minYear, "year(#) >= ?", SQLTYPES.INTEGER)
    if (pSalesprojectId)
    {
        forecastSelect.and("FORECAST.OBJECT_TYPE", 'Salesproject')
                      .and("FORECAST.OBJECT_ROWID", pSalesprojectId)
    }   
    
    forecastSelect.groupBy("year(DATE_START), month(DATE_START), GROUPCODE")
                  .orderBy("yearNum, monthNum");
                  
    return forecastSelect.table();
}

/**
 * get turnover data
 * 
 * @param {Number} pMaxYear the maximum year
 * @param {Number} pYearCount count of years to select
 * @param {Boolean} pShowForecast
 * @param {Boolean} pShowTurnover
 * @param {String} [pSalesprojectId  = undefined] if set, select only from this salesproject
 * 
 * @return {String[][]} 
 */
TurnoverUtil.getTurnoverAndForecastData = function (pMaxYear, pYearCount, pShowForecast, pShowTurnover, pSalesprojectId)
{   
    var data = [];
    if (pShowTurnover)
        data = data.concat(TurnoverUtil.getTurnoverData(pMaxYear, pYearCount, pSalesprojectId));
    
    if (pShowForecast)
        data = data.concat(TurnoverUtil.getForecastData(pMaxYear, pYearCount, pSalesprojectId));
    return data;
}