process.js

import("Attribute_lib");
import("AttributeFilter_lib");
import("Util_lib");
import("system.util");
import("ClassificationFilter_lib");
import("system.vars");
import("system.datetime");
import("JditoFilter_lib");
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("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;
    var sqlMask = new SqlMaskingUtils();

    // load data
    var forecastSelect = newSelect([
            "'" + forecastCategory + "'", 
            sqlMask.yearFromDate("DATE_START") + " yearNum", 
            sqlMask.monthFromDate("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, sqlMask.yearFromDate("#") + " <= ?", SQLTYPES.INTEGER)
        .and("FORECAST.DATE_START", minYear, sqlMask.yearFromDate("#") + " >= ?", SQLTYPES.INTEGER)
        .and("FORECAST.KIND", $KeywordRegistry.forecastKind$forecast());
        
    if (pSalesprojectId)
    {
        forecastSelect.and("FORECAST.OBJECT_TYPE", "Salesproject")
                      .and("FORECAST.OBJECT_ROWID", pSalesprojectId)
    }   
    
    forecastSelect.groupBy([sqlMask.yearFromDate("DATE_START"), sqlMask.monthFromDate("DATE_START"), "GROUPCODE", "KIND"])
                  .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;
}




/**
 * 
 * An TurnoverDynamicChart can be used for the dynamic chart turnover<br>
 * <i><u>You have to instanciate it with "new".</u></i>
 * <p>
 * There are also functions where it is not necessary to instantiate it.
 * 
 * @param {Object} pFilter                  <p>
 *                                           The current filter object. <br>
 * @class
 */
function TurnoverDynamicChart(pFilter) {
    this.groupMapping = TurnoverDynamicChart.getGroupMapping();
    
    this.filterMappingPlanning = TurnoverDynamicChart.getFilterMapping("Planning");
    this.filterMappingForecast = TurnoverDynamicChart.getFilterMapping("Forecast");
    this.filterMappingTurnover = TurnoverDynamicChart.getFilterMapping("Turnover");
    
    this.filter = pFilter;
    
    this.idxPlanning = 0;
    this.idxForecast = 1;
    this.idxTurnover = 2;
    this.groupedField = null;
    
    var thisYear = parseInt(datetime.toDate(vars.get("$sys.date"), "yyyy", "UTC"));
    
    this.firstYear = thisYear - 2;
    this.lastYear = thisYear + 1;
    
    
}

/**
 * Returns the planning query.
 *                                    
 */
TurnoverDynamicChart.prototype.getPlanningFromSql = function (pIsRowCount)
{
    var sqlHelper = new SqlMaskingUtils();

    var select;
    if(pIsRowCount)
    {
        select = "FORECAST.FORECASTID";
    }
    else
    {
        if(this.groupedField)
        {
            select = this.groupMapping[this.groupedField]["forGrouping"][this.idxPlanning] + " as uid,"
                            + " (" + this.getGroupFieldDisplayValue(this.idxPlanning) + ") as groupField, ";
        }
        else
        {
            select = sqlHelper.concatenate(["FORECAST.FORECASTID", "PLANNING_MONTHID"]) + ", '', '', '', ";
        }
        
        select += "FORECAST.VOLUME*PLANNING_MONTH.MONTH_FACTOR/100 as sum_planning, " +
            "0 as sum_forecast, 0 as sum_turnover, 0 as sum_forecast_weighted ";
    }
    
    return newSelect(select)
                .from("FORECAST")
                .join("PLANNING_MONTH", null, null, "cross")
                .leftJoin("CONTACT", "FORECAST.CONTACT_ID = CONTACT.CONTACTID and PERSON_ID is null")
                .leftJoin("ORGANISATION", "CONTACT.ORGANISATION_ID = ORGANISATION.ORGANISATIONID")
                .leftJoin("ADDRESS", "CONTACT.ADDRESS_ID = ADDRESS.ADDRESSID")
                .leftJoin("CLASSIFICATIONSTORAGE", "CLASSIFICATIONSTORAGE.OBJECT_ROWID = CONTACT.CONTACTID")
                .where("FORECAST.KIND", $KeywordRegistry.forecastKind$planning())
                .and("FORECAST.FORECAST_YEAR", this.firstYear, SqlBuilder.GREATER_OR_EQUAL())
                .and("FORECAST.FORECAST_YEAR", this.lastYear, SqlBuilder.LESS_OR_EQUAL());
                
}


/**
 * Returns the forecast query.
 *                                    
 * @param {Boolean} pIsWeighted     <p>
 *                                  If the forecast should be weighted the value of the parameter is true.<br>
 * 
 * @param {Boolean} pIsRowCount     <p>
 *                                  Whether or not we want the rowCount instead of retuning the data.<br>
 */
TurnoverDynamicChart.prototype.getForecastFromSql = function (pIsWeighted, pIsRowCount)
{
    var sqlHelper = new SqlMaskingUtils();
    var select;

    if(pIsRowCount)
    {
        select = "FORECAST.FORECASTID";
    }
    else
    {
        select = "FORECAST.FORECASTID, '', '', '', ";

        if(this.groupedField)
        {
            select = this.groupMapping[this.groupedField]["forGrouping"][this.idxForecast] + " as uid, " 
                            + "(" + this.getGroupFieldDisplayValue(this.idxForecast) + ") as groupField,";
        }
        var volumeColumns;

        if(pIsWeighted)
        {   
            if(!this.groupedField)
            {
                select = sqlHelper.concatenate(["FORECAST.FORECASTID", sqlHelper.cast("SALESPROJECT.PROBABILITY", SQLTYPES.VARCHAR, 3)]) +", '', '', '', " ;
            }

            volumeColumns = " 0 as sum_planning, 0 as sum_forecast, 0 as sum_turnover, " 
                                + "(FORECAST.VOLUME/100*SALESPROJECT.PROBABILITY)" 
                                + " as sum_forecast_weighted";
        }
        else
        {
            volumeColumns = " 0 as sum_planning, FORECAST.VOLUME" 
                                + " as sum_forecast, 0 as sum_turnover, 0 as sum_forecast_weighted";
        }
        select += volumeColumns;
    }
    
    return newSelect(select)
                .from("FORECAST")
                .join("SALESPROJECT", "OBJECT_TYPE = 'Salesproject' and OBJECT_ROWID = SALESPROJECTID")
                .leftJoin("CONTACT", "SALESPROJECT.CONTACT_ID = CONTACT.CONTACTID and PERSON_ID is null")
                .leftJoin("ORGANISATION", "CONTACT.ORGANISATION_ID = ORGANISATION.ORGANISATIONID")
                .leftJoin("ADDRESS", "CONTACT.ADDRESS_ID = ADDRESS.ADDRESSID")
                .leftJoin("CLASSIFICATIONSTORAGE", "CLASSIFICATIONSTORAGE.OBJECT_ROWID = CONTACT.CONTACTID")
                .where("FORECAST.KIND", $KeywordRegistry.forecastKind$forecast())
                .and("year(FORECAST.DATE_START) >= " + this.firstYear)
                .and("year(FORECAST.DATE_START) <= " + this.lastYear);
}


/**
 * Returns the turnover query.
 *                                    
 */
TurnoverDynamicChart.prototype.getTurnoverFromSql = function (pIsRowCount)
{
    var sqlHelper = new SqlMaskingUtils();
    var select;
    
    if(pIsRowCount)
    {
        select = "SALESORDERITEM.SALESORDERITEMID";
    }
    else
    {
        select = "SALESORDERITEM.SALESORDERITEMID, '', '', '', ";
        if(this.groupedField)
        {
            select = this.groupMapping[this.groupedField]["forGrouping"][this.idxTurnover] + " as uid, " 
                            + "(" + this.getGroupFieldDisplayValue(this.idxTurnover) + ") as groupField, ";
        }
        
        var discount = sqlHelper.isNull("SALESORDERITEM.DISCOUNT", 0);
        var discountedPrice = "(SALESORDERITEM.PRICE * SALESORDERITEM.QUANTITY  * (100 - " + discount + ") / 100";
        select += " 0 as sum_planning, 0  as sum_forecast, "
                + ""+ discountedPrice + " + " + discountedPrice + " * " + sqlHelper.isNull("SALESORDERITEM.VAT", 0) + " / 100)) as sum_turnover"
                + ", 0 as sum_forecast_weighted";
    }
    
    return newSelect(select)
            .from("SALESORDERITEM")
            .join("SALESORDER", "SALESORDER.SALESORDERID = SALESORDERITEM.SALESORDER_ID")
            .leftJoin("CONTACT", "SALESORDER.CONTACT_ID = CONTACT.CONTACTID and PERSON_ID is null")
            .leftJoin("ORGANISATION", "CONTACT.ORGANISATION_ID = ORGANISATION.ORGANISATIONID")
            .leftJoin("ADDRESS", "CONTACT.ADDRESS_ID = ADDRESS.ADDRESSID")
            .leftJoin("CLASSIFICATIONSTORAGE", "CLASSIFICATIONSTORAGE.OBJECT_ROWID = CONTACT.CONTACTID")
            .where("year(SALESORDER.SALESORDERDATE) >= " + this.firstYear)
            .and("year(SALESORDER.SALESORDERDATE) <= " + this.lastYear);
}


/**
 * Returns the display part for the from sqls.
 * 
 * @param {int} pIndexGroupMapping       <p>
 *                                          The index for the grouping object of the diffrent kinds<br>                        
 */
TurnoverDynamicChart.prototype.getGroupFieldDisplayValue = function (pIndexGroupMapping)
{
    var compareField = this.groupMapping[this.groupedField]["forGrouping"][pIndexGroupMapping];
    var subSqlDisplay;

    switch(this.groupMapping[this.groupedField]["fieldName"])
    {
        case "YEAR": 
            subSqlDisplay = compareField;
        break;
        case "MONTH": 
            subSqlDisplay = "case when " + compareField + " = 1 then '" + translate.text("January") + "' "
                            + "when " + compareField + " = 2 then '" + translate.text("February") + "' "
                            + "when " + compareField + " = 3 then '" + translate.text("March") + "' "
                            + "when " + compareField + " = 4 then '" + translate.text("April") + "' "
                            + "when " + compareField + " = 5 then '" + translate.text("May") + "' "
                            + "when " + compareField + " = 6 then '" + translate.text("June") + "' "
                            + "when " + compareField + " = 7 then '" + translate.text("July") + "' "
                            + "when " + compareField + " = 8 then '" + translate.text("August") + "' "
                            + "when " + compareField + " = 9 then '" + translate.text("September") + "' "
                            + "when " + compareField + " = 10 then '" + translate.text("October") + "' "
                            + "when " + compareField + " = 11 then '" + translate.text("November") + "' "
                            + "when " + compareField + " = 12 then '" + translate.text("December") + "' end";
        break;
        case "ORGANISATION": 
            subSqlDisplay = "ORGANISATION.NAME";
        break;
        case "GROUPCODE": 
            subSqlDisplay = KeywordUtils.getResolvedTitleSqlPart($KeywordRegistry.productGroupcode(), compareField);
        break;
    }

    return subSqlDisplay;
}


/**
 * Returns the condition for the from sqls.
 * 
 * @param {String} pKind       <p>
 *                                <br>  Kind of the diffrent categories                      
 */
TurnoverDynamicChart.prototype.getFilterCond = function (pKind)
{
    var filterCond;    

    switch(pKind)
    {
        case "Planning": 
            filterCond = JditoFilterUtils.getSqlCondition(this.filter, "FORECAST", null, this.filterMappingPlanning );
        break;
        case "Forecast": 
            filterCond = JditoFilterUtils.getSqlCondition(this.filter, "FORECAST", null, this.filterMappingForecast);
        break;
        case "Turnover": 
            filterCond = JditoFilterUtils.getSqlCondition(this.filter, "SALESORDER", null, this.filterMappingTurnover );
            
        break;
    }
    
    return filterCond;
}


/**
 * Returns the finished sql for the from part. <br>
 * The sqls of Planning, Forecast and Turnover are put together with a union and the filters are added.
 *                      
 */
TurnoverDynamicChart.prototype.getFromSql = function (pIsRowCount)
{
    var planningSql= this.getPlanningFromSql(pIsRowCount);
    var forecastSql = this.getForecastFromSql(false, pIsRowCount);
    var forecastWeightedSql = this.getForecastFromSql(true, pIsRowCount);
    var turnoverSql = this.getTurnoverFromSql(pIsRowCount);
    
    if(this.filter != null && this.filter.childs.length > 0)
    {   
        planningSql.and(this.getFilterCond("Planning"));
        forecastSql.and(this.getFilterCond("Forecast"));
        forecastWeightedSql.and(this.getFilterCond("Forecast"));
        turnoverSql.and(this.getFilterCond("Turnover"));
    }
    
    var fromSql = forecastSql.unionAll(turnoverSql).unionAll(planningSql).unionAll(forecastWeightedSql);
    return fromSql;
}



/**
 * Returns the group mapping Object. <br>
 * Structure of the Object: {GroupedFieldName: <br>
 *                                {"forGrouping": [columnPlanning, columnForecast, columnTurnover], <br>
 *                                 "fieldName": generalNameOfGroupedFields <br>
 *                                } <br>
 *                          } <br>
 */
TurnoverDynamicChart.getGroupMapping = function ()
{   
    return {
        "TURNOVER_YEAR": {"forGrouping": ["FORECAST.FORECAST_YEAR", "year(FORECAST.DATE_START)", "year(SALESORDER.SALESORDERDATE)"],
                        "fieldName": "YEAR"},   
        "ORGANISATION_ID" : {"forGrouping": ["FORECAST.CONTACT_ID", "SALESPROJECT.CONTACT_ID", "SALESORDER.CONTACT_ID"],
                    "fieldName": "ORGANISATION"}, 
        "#EXTENSION.Month_filter.Month_filter#TEXT" : {"forGrouping": ["PLANNING_MONTH.MONTH_NUMBER", "MONTH(FORECAST.DATE_START)", "MONTH(SALESORDER.SALESORDERDATE)"],
                    "fieldName": "MONTH"}, 
        "#EXTENSION.Groupcode_filter.Groupcode_filter#TEXT" : {"forGrouping": ["FORECAST.GROUPCODE", "FORECAST.GROUPCODE", "SALESORDERITEM.GROUPCODEID"],
                        "fieldName": "GROUPCODE"}
    };
}

/**
 * Returns the filter mapping Object. <br>
 * Structure of the Object: {Kind: <br>
 *                                {FilteredFieldName: columnName or function} <br>
 *                          } <br>
 * @param {String} pKind       <p>
 *                                <br>  Kind of the diffrent categories 
 */
TurnoverDynamicChart.getFilterMapping = function (pKind)
{   
    var filterMapping = {

        "Planning": {
                        "TURNOVER_YEAR" : function (pValue, pOperator) {
                                        return TurnoverDynamicChart.dateCond(pValue, pOperator, "FORECAST.FORECAST_YEAR");
                        },
                        "#EXTENSION.Month_filter.Month_filter#TEXT" : function (pValue, pOperator) {
                                        return TurnoverDynamicChart.dateCond(pValue, pOperator, "PLANNING_MONTH.MONTH_NUMBER");
                        },
                        "ORGANISATION_ID" : "FORECAST.CONTACT_ID",
                        "#EXTENSION.Groupcode_filter.Groupcode_filter#TEXT" : "FORECAST.GROUPCODE",
                        "#EXTENSION.Salesproject_filter.Salesproject_filter#TEXT" : function (pValue, pOperator) {
                                        
                                        return TurnoverDynamicChart.planningSalesprojectCond(pValue, pOperator);
                        }
                        
                    }, 
        "Forecast" : {
                        "TURNOVER_YEAR" : function (pValue, pOperator) {
                                        return TurnoverDynamicChart.dateCond(pValue, pOperator, "YEAR(FORECAST.DATE_START)");
                        },
                        "#EXTENSION.Month_filter.Month_filter#TEXT" : function (pValue, pOperator) {
                                        return TurnoverDynamicChart.dateCond(pValue, pOperator, "MONTH(FORECAST.DATE_START)");
                        },
                        "ORGANISATION_ID" :"SALESPROJECT.CONTACT_ID",
                        "#EXTENSION.Groupcode_filter.Groupcode_filter#TEXT" : "FORECAST.GROUPCODE",
                        "#EXTENSION.Salesproject_filter.Salesproject_filter#TEXT" : "SALESPROJECT.SALESPROJECTID"
                    }, 
        "Turnover" : {
                        "TURNOVER_YEAR" : function (pValue, pOperator) {
                                      return   TurnoverDynamicChart.dateCond(pValue, pOperator, "YEAR(SALESORDER.SALESORDERDATE)");
                        },
                        "#EXTENSION.Month_filter.Month_filter#TEXT" : function (pValue, pOperator) {
                                      return   TurnoverDynamicChart.dateCond(pValue, pOperator, "MONTH(SALESORDER.SALESORDERDATE)");
                        },
                        "ORGANISATION_ID" :  "SALESORDER.CONTACT_ID",
                        "#EXTENSION.Groupcode_filter.Groupcode_filter#TEXT" : "SALESORDERITEM.GROUPCODEID",
                        "#EXTENSION.Salesproject_filter.Salesproject_filter#TEXT" : "SALESORDER.OBJECT_ROWID"
                    }
    };
    
    for(var key in filterMapping)
    {
        filterMapping[key]["#EXTENSION.DistrictName_filter.DistrictName_filter#TEXT"]
        = function (pValue, pOperator) {
            var districtFilter = newSelect("DISTRICT_FILTER").from("DISTRICT")
                .where("DISTRICT.DISTRICT_NAME", pValue).cell();
            if(!districtFilter)
            {
                return "0=1";
            }
            var filterJson = JSON.stringify(JSON.parse(districtFilter).filter);
            return db.toFilterCondition(filterJson, "Organisation_entity");
        };
        filterMapping[key]["#EXTENSION.DistrictDistrictnumber_filter.DistrictDistrictnumber_filter#NUMBER"]
        = function (pValue, pOperator) {
            var districtFilter = newSelect("DISTRICT_FILTER").from("DISTRICT")
                .where("DISTRICT.DISTRICT_NUMBER", pValue).cell();
            if(!districtFilter)
            {
                return "0=1";
            }
            var filterJson = JSON.stringify(JSON.parse(districtFilter).filter);
            return db.toFilterCondition(filterJson, "Organisation_entity");
        };
    }
    
    filterMapping = TurnoverDynamicChart.addOrganisationFiltersToFilterMapping(filterMapping);
    filterMapping = TurnoverDynamicChart.addClassificationGroupFilterToFilterMapping(filterMapping);
    filterMapping = TurnoverDynamicChart.addAttributeFilterToFilterMapping(filterMapping);
    
    return filterMapping[pKind];
}


/**
 * Adds the organisation filter extensions to the filter object and returns the complete object. <br>
 * 
 * @param {Object} pMapping       <p>
 *                                <br>  The filter mapping object
 */
TurnoverDynamicChart.addOrganisationFiltersToFilterMapping = function (pMapping)
{
    var orgFilters = [["#EXTENSION.OrgCountry_filter.OrgCountry_filter#TEXT", "ADDRESS.COUNTRY"],
                      ["#EXTENSION.OrgAddress_filter.OrgAddress_filter#TEXT", "ADDRESS.ADDRESS"],
                      ["#EXTENSION.OrgDistrict_filter.OrgDistrict_filter#TEXT", "ADDRESS.DISTRICT"],
                      ["#EXTENSION.OrgState_filter.OrgState_filter#TEXT", "ADDRESS.STATE"],
                      ["#EXTENSION.OrgZip_filter.OrgZip_filter#TEXT", "ADDRESS.ZIP"],
                      [ "#EXTENSION.OrgCity_filter.OrgCity_filter#TEXT", "ADDRESS.CITY"],
                      [ "#EXTENSION.OrgRegion_filter.OrgRegion_filter#TEXT", "ADDRESS.REGION"],
                      [ "#EXTENSION.OrgType_filter.OrgType_filter#TEXT", "ORGANISATION.KIND"],
                      [ "#EXTENSION.OrgClassification_filter.OrgClassification_filter#TEXT", "CLASSIFICATIONSTORAGE.CLASSIFICATIONVALUE"],
                      [ "#EXTENSION.OrgLanguage_filter.OrgLanguage_filter#TEXT", "CONTACT.ISOLANGUAGE"]];
                  
    orgFilters.forEach(function([filterFieldName, dbFilterColumn])
    {
        pMapping.Planning[filterFieldName] = dbFilterColumn;
        pMapping.Forecast[filterFieldName] = dbFilterColumn;
        pMapping.Turnover[filterFieldName] = dbFilterColumn;
    });       
    
    return pMapping;

}


/**
 * Adds the classification group filter extension to the filter object and returns the complete object. <br>
 * 
 * @param {Object} pMapping       <p>
 *                                <br>  The filter mapping object
 */
TurnoverDynamicChart.addClassificationGroupFilterToFilterMapping = function (pMapping)
{   
    
    var fields = JSON.parse(ClassificationGroupFilterUtils.makeFilterFields("Organisation")); // gets all possible classification group for filtering
    var classificationTable = "CLASSIFICATIONSTORAGE";
    
    fields.forEach(function(classificationField)
    {
        let decodedFilterName = ClassificationGroupFilterNameCoder.decode(classificationField["name"]); //decode the base 64 string to its original form to receive the sql expression of the classification
        let sqlCondExpression = decodedFilterName["representingSqlExpression"];
        let filterFieldName = "#EXTENSION.ClassificationGroup_filter." + classificationField.name + "#TEXT";

        pMapping.Planning[filterFieldName] = function (pValue, pOperator) {
                return TurnoverDynamicChart.classificationCond(pValue, pOperator, "FORECAST.CONTACT_ID", sqlCondExpression, classificationTable);
            };
        pMapping.Forecast[filterFieldName] = function (pValue, pOperator) {
                return TurnoverDynamicChart.classificationCond(pValue, pOperator, "SALESPROJECT.CONTACT_ID", sqlCondExpression, classificationTable);
            };
        pMapping.Turnover[filterFieldName] = function (pValue, pOperator) {
                return TurnoverDynamicChart.classificationCond(pValue, pOperator, "SALESORDER.CONTACT_ID", sqlCondExpression, classificationTable);
            };
    });
    return pMapping;

}

/**
 * Adds the attribute filter extensions to the filter object and returns the complete object. <br>
 * 
 * @param {Object} pMapping       <p>
 *                                <br>  The filter mapping object
 */
TurnoverDynamicChart.addAttributeFilterToFilterMapping = function (pMapping)
{
    var fields = JSON.parse(AttributeFilterExtensionMaker.makeFilterFields("Organisation")); // gets all possible attributes for filtering
        
    fields.forEach(function(attributeField)
    {
        let attributeId = JSON.parse(util.decodeBase64String(attributeField.name))["id"]; //decode the base 64 string to its original form to receive the id of the attribute
        let type = AttributeUtil.getAttributeType(attributeId);
        let valueField = AttributeTypeUtil.getDatabaseField(type);

        pMapping.Planning["#EXTENSION.OrgAttribute_filter." + attributeField.name + "#TEXT"] = function (pValue, pOperator) {
                return TurnoverDynamicChart.attributeCond(pValue, pOperator, "FORECAST.CONTACT_ID", attributeId, valueField);
            };
        pMapping.Forecast["#EXTENSION.OrgAttribute_filter." + attributeField.name + "#TEXT"] = function (pValue, pOperator) {
                return TurnoverDynamicChart.attributeCond(pValue, pOperator, "SALESPROJECT.CONTACT_ID", attributeId, valueField);
            };
        pMapping.Turnover["#EXTENSION.OrgAttribute_filter." + attributeField.name + "#TEXT"] = function (pValue, pOperator) {
                return TurnoverDynamicChart.attributeCond(pValue, pOperator, "SALESORDER.CONTACT_ID", attributeId, valueField);
            };
    });
    
    return pMapping;

}


/**
 * Returns the cond for Date Columns. 
 * 
 * @param {String} pValue       <p>
 *                              <br>  The value of the chosen filter
 * @param {String} pOperator    <p>
 *                              <br>  The operator of the chosen filter
 * @param {String} pColumn      <p>
 *                              <br>  The column with which the value is compared
 */
TurnoverDynamicChart.dateCond = function (pValue, pOperator, pColumn) {
        
    var cond = TurnoverDynamicChart.getCondition(pValue, pOperator, pColumn);

    if(Array.isArray(cond))
    {
        cond = cond[0];
        cond = cond.replace(new RegExp("#", "g"), pColumn);
        cond = cond.replace("?", pValue);
    }
    
    return cond;

}

/**
 * Returns the cond for classification. 
 * 
 * @param {String} pValue                 <p>
 *                                        <br>  The value of the chosen filter
 * @param {String} pOperator              <p>
 *                                        <br>  The operator of the chosen filter
 * @param {String} pContactIdColumn       <p>
 *                                        <br>  The contact id column with which the subselect is compared
 * @param {String} pCompareField          <p>
 *                                        <br>  The column name with which the value is compared
 * @param {String} pClassificationTable   <p>
 *                                        <br>  The classification table used in the subselect
 */
TurnoverDynamicChart.classificationCond = function (pValue, pOperator, pContactIdColumn, pCompareField, pClassificationTable) {
        
    var cond = TurnoverDynamicChart.getCondition(pValue, pOperator, pCompareField);
    if(Array.isArray(cond))
    {
        cond = cond[0];
        cond = cond.replace(new RegExp("#", "g"), " UPPER(" + pCompareField + ")");
        cond = cond.replace("?", " UPPER('" + pValue + "')");
    }
  
    var resSql = newSelect("CONTACTID")
    .from("CONTACT")
    .join(pClassificationTable, pClassificationTable + ".OBJECT_ROWID = CONTACT.CONTACTID")
    .where(cond);
    
    var finishedCond = newWhere(pContactIdColumn, resSql, SqlBuilder.IN());

    return finishedCond;

}


/**
 * Returns the cond for Attributes. 
 * 
 * @param {String} pValue              <p>
 *                                     <br>  The value of the chosen filter
 * @param {String} pOperator           <p>
 *                                     <br>  The operator of the chosen filter
 * @param {String} pContactIdColumn    <p>
 *                                     <br>  The contact id column with which the subselect is compared
 * @param {String} pAttributeId        <p>
 *                                     <br>  The attribute id of the chosen filter
 * @param {String} pValueField         <p>
 *                                     <br>  The column name with which the value is compared
 */
TurnoverDynamicChart.attributeCond = function (pValue, pOperator, pContactIdColumn, pAttributeId, pValueField) {
    
    let sqlCond = pContactIdColumn;
    let subsqlAttributes = newSelect("OBJECT_ROWID")
            .from("AB_ATTRIBUTERELATION")
            .where("AB_ATTRIBUTERELATION.AB_ATTRIBUTE_ID", pAttributeId)
            .and("AB_ATTRIBUTERELATION.OBJECT_TYPE", "Organisation");

    if (pOperator == "ISNULL")
    {
        sqlCond += " not in (" + subsqlAttributes.toString() + ")";
    }
    else if (pOperator == "ISNOTNULL")
    {
        sqlCond += " in (" + subsqlAttributes.toString() + ")";
    }
    else if ( pOperator == "NOT_EQUAL")
    {
        sqlCond += " not in (" + subsqlAttributes.and("AB_ATTRIBUTERELATION." + pValueField, pValue).toString() + ")";
    }
    else if (pOperator == "EQUAL")
    {
        sqlCond += " in (" + subsqlAttributes.and("AB_ATTRIBUTERELATION." + pValueField, pValue).toString() + ")";
    }

    return sqlCond;

}


/**
 * Returns the cond for the planning salesproject filter 
 * 
 * @param {String} pValue      <p>
 *                             <br>  The value of the chosen filter
 * @param {String} pOperator   <p>
 *                             <br>  The operator of the chosen filter
 */
TurnoverDynamicChart.planningSalesprojectCond = function (pValue, pOperator) {
   
    var contactId = newSelect("CONTACT_ID").from("SALESPROJECT").where("SALESPROJECT.SALESPROJECTID", pValue).cell();
    var compareField = "FORECAST.CONTACT_ID";
    var cond = TurnoverDynamicChart.getCondition(pValue, pOperator, contactId);
    if(Array.isArray(cond))
    {
        cond = cond[0];
        cond = cond.replace(new RegExp("#", "g"), " UPPER(" + compareField + ")");
        cond = cond.replace("?", " UPPER('" + contactId + "')");
    }
    
    return cond;

}


/**
 * returns [condition, value with wildcards] depending on the operator.
 * 
 * @param {String} pValue      <p>
 *                             <br>  The value to be compared
 * @param {String} pOperator   <p>
 *                             <br>  The compare operator
 * @param {String} pField      <p>
 *                             <br>  The name of the field to be compared
 */
TurnoverDynamicChart.getCondition = function  (pValue, pOperator, pField)
{
    switch (pOperator)
    {
        case "CONTAINS":
            return [SqlBuilder.LIKE(), "%" + pValue + "%"];
        case "CONTAINSNOT":
            return [SqlBuilder.NOT_LIKE(), "%" + pValue + "%"];
        case "STARTSWITH":
            return [SqlBuilder.LIKE(), pValue + "%"];
        case "ENDSWITH":
            return [SqlBuilder.LIKE(), "%" + pValue];
        case "EQUAL":
            return [SqlBuilder.EQUAL(), pValue];
        case "NOT_EQUAL":
            return [SqlBuilder.NOT_EQUAL(), pValue];
        case "LESS":
            return [SqlBuilder.LESS(), pValue];
        case "LESS_OR_EQUAL":
            return [SqlBuilder.LESS_OR_EQUAL(), pValue];
        case "GREATER":
            return [SqlBuilder.GREATER(), pValue];
        case "GREATER_OR_EQUAL":
            return [SqlBuilder.GREATER_OR_EQUAL(), pValue];
        case "ISNULL":
            return pField + " is null";
        case "ISNOTNULL":
            return pField + " is not null";
        case "TIMEFRAME_EQUAL":
        case "TIMEFRAME_COMING":
        case "TIMEFRAME_PAST":
            var [start, end] = datetime.resolveRelativeDateExpression(pValue);
            return newWhere(pField, start, SqlBuilder.GREATER_OR_EQUAL())
                .and(pField, end, SqlBuilder.LESS_OR_EQUAL());
    }
}