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 ()
{
var sqlHelper = new SqlMaskingUtils();
var select = sqlHelper.concatenate(["FORECAST.FORECASTID", "PLANNING_MONTHID"]) + ", '', '', '', ";
if(this.groupedField)
{
select = this.groupMapping[this.groupedField]["forGrouping"][this.idxPlanning] + " as uid,"
+ " (" + this.getGroupFieldDisplayValue(this.idxPlanning) + ") as groupField, ";
}
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>
*/
TurnoverDynamicChart.prototype.getForecastFromSql = function (pIsWeighted)
{
var sqlHelper = new SqlMaskingUtils();
var select = "FORECAST.FORECASTID, '', '', '', ";
if(this.groupedField)
{
select = this.groupMapping[this.groupedField]["forGrouping"][this.idxForecast] + " as uid, "
+ "(" + this.getGroupFieldDisplayValue(this.idxForecast) + ") as groupField,";
}
var volumeColumns = " 0 as sum_planning, FORECAST.VOLUME"
+ " as sum_forecast, 0 as sum_turnover, 0 as sum_forecast_weighted";
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";
}
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 ()
{
var sqlHelper = new SqlMaskingUtils();
var select = "SALESORDERITEM.SALESORDERITEMID, '', '', '', ";
if(this.groupedField)
{
select = this.groupMapping[this.groupedField]["forGrouping"][this.idxTurnover] + " as uid, "
+ "(" + this.getGroupFieldDisplayValue(this.idxTurnover) + ") as groupField, ";
}
select += " 0 as sum_planning, 0 as sum_forecast, "
+ "((SALESORDERITEM.PRICE * SALESORDERITEM.QUANTITY * (100 - " + sqlHelper.isNull("SALESORDERITEM.DISCOUNT", 0)
+ ") / 100) + (SALESORDERITEM.PRICE * SALESORDERITEM.QUANTITY * (100 - " + sqlHelper.isNull("SALESORDERITEM.DISCOUNT", 0)
+ ") / 100 * " + 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 ()
{
var planningSql= this.getPlanningFromSql();
var forecastSql = this.getForecastFromSql();
var forecastWeightedSql = this.getForecastFromSql(true);
var turnoverSql = this.getTurnoverFromSql();
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"
}
};
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());
}
}