importPackage(Packages.de.elo.ix.jscript);
importPackage(Packages.de.elo.ix.scripting);
//@include lib_Class.js
//@include lib_sol.common.StringUtils.js
//@include lib_sol.common.SordUtils.js
//@include lib_sol.common.UserProfile.js
//@include lib_sol.common.Locale.js
//@include lib_sol.common.ix.DynKwlUtils.js
/**
* @class sol.common.ix.DynKwlDatabaseIterator
*
* Database Iterator used by dynamic keyword lists.
* This class provides an abstract layer that simplifies the usage of database connections.
*
* Dynamic keyword lists simplify the use of gathering data from external services.
* This implementation helps in querying database tables. Each query is returned as a table
* which contains a title and a specific table configuration.
*
* Queries are defined by sql statements that can contain placeholders ?.
* Each placeholder must be defined in the sqlParams array. If no name is given, the given filter value will be used.
* In case the field is empty, a message can be set, which is displayed by the client.
* Thanks to a mode type filters can be applied in different ways.
*
* {mode: 'STARTS_WITH'},
* {name: 'COMPANY_CODE', message: 'please select a company.'},
*
* Following modes are supported. Please mind that the modes ENDS_WITH and CONTAINS might lead to expansive queries.
*
* - <b>STARTS_WITH</b>: String starts with the given value. e.g. COMPA%.
* - <b>ENDS_WITH</b>: String ends with the given value. e.g. %COMPA.
* - <b>CONTAINS</b>: String contains the given value. e.g. %COMPA%.
* - <b>No type name given</b>: String equals the given value. e.g. "COMPA".
*
*
* # Localization
*
* SQL Queries can be localized by passing the locale key as a parameter. Therefore the valueType 'LANGUAGE' can be used.
*
* sqlQuery: "select code, description from sol_invoice_trade_charge where language = ? order by description",
* sqlParams: [{ valueType: 'LANGUAGE' }],
*
*
* # Examples
*
* Example implementation as instance
*
* sol.create('sol.common.ix.DynKwlDatabaseIterator', {
* tableTitle: 'Company',
* sqlQuery: "select CODE, NAME, STREET, ZIPCODE, CITY, COUNTRY_CODE from sol_invoice_company where CODE like ? OR NAME like ?",
* sqlParams: [
* {mode: 'STARTS_WITH'},
* {mode: 'CONTAINS'}
* ],
* tableKeyNames: ["COMPANY_CODE", "COMPANY_NAME", null, null, null, null],
* tableHeaders: ["No.", "Name", "Street", "Zip", "City", "Country"]
* });
*
*
* Example implementation as a custom class.
*
* sol.define('sol.invoice.ix.dynkwl.Company', {
* extend: 'sol.common.ix.DynKwlDatabaseIterator',
* tableTitle: 'Company',
* sqlQuery: "select CODE, NAME, STREET, ZIPCODE, CITY, COUNTRY_CODE from sol_invoice_company where CODE like ? OR NAME like ?",
* sqlParams: [
* {mode: 'STARTS_WITH'},
* {mode: 'CONTAINS'}
* ],
* tableKeyNames: ["COMPANY_CODE", "COMPANY_NAME", null, null, null, null],
* tableHeaders: ["No.", "Name", "Street", "Zip", "City", "Country"]
* });
*
* Example usage in ix dynkwl-script:
*
* function getDataIterator() {
* var iterator;
* try {
* iterator = sol.create('sol.invoice.ix.dynkwl.Company', {
* dbName: 'customers',
* jdbc: 'testconn'
* });
* return new DynamicKeywordDataProvider(iterator);
* } finally {
* log.info(")getDataIterator");
* }
* }
*
* @author NM, ELO Digital Office GmbH
* @version 1.03.000
*
* @eloix
* @requires sol.common.StringUtils
* @requires sol.common.SordUtils
* @requires sol.common.UserProfile
* @requires sol.common.Locale
* @requires sol.common.ix.DynKwlUtils
*/
sol.define("sol.common.ix.DynKwlDatabaseIterator", {
mixins: ["sol.common.ix.DynKwlMixin"],
/**
* @cfg {string} tableTitle
* name of this table. The title is displayed by the client.
*/
tableTitle: undefined,
/**
* @cfg {string} sqlQuery (required)
* sql-query that can contain placeholders ? used by prepared statements.
*
* "select CODE, NAME, STREET, ZIPCODE, CITY, COUNTRY_CODE from sol_invoice_company where CODE like ? OR NAME like ?"
*/
sqlQuery: undefined,
/**
* @cfg {Array} sqlParams (required)
* fields that are used by the prepared statements. (Array of config options).
*
* [{ name: 'IX_MAP_NUMBER', mode: 'STARTS', message: 'field x missing' }]
*
* if no name is provided current focused field is used.
*/
sqlParams: undefined,
/**
* @cfg {Array} tableKeyNames (required)
* mapping between columns and elo group or map fields. if null, value is not transferred.
*
* // GRP field map static field map table field
* ['INVOICE_NO', 'IX_MAP_INVOICE_DUE_DATE', 'IX_MAP_ACCDESC{i}'];
*
* Map fields must contain IX_MAP as a prefix and can optionally use {i} as a counter value.
*/
tableKeyNames: undefined,
* @cfg {Array} tableHeaders (required)
* column header names. if null column is hidden in the client.
*
* ["No.", "Name", "Street", "Zip", "City", "Country"]
*/
tableHeaders: undefined,
/**
* @cfg {string} dbName
* JDBC: Database name for the jdbc connection. Should not be defined if table is part of the current archive database.
*/
dbName: undefined,
/**
* @cfg {string} jdbc
* JDBC: Name of the jdbc connection. Should not be defined if table is part of the current archive database.
*/
jdbc: undefined,
/**
* @property
* Error message that is passed to the client if value is missing.
*/
errorMessage: "",
initialize: function (config) {
this.log = sol.create("sol.Logger", { scope: this.$className || "sol.common.ix.DynKwlDatabaseIterator" });
this.log.enter("initialize", config);
config = config || {};
if ((!this.sqlQuery && !config.sqlQuery)
|| (!this.tableKeyNames && !config.tableKeyNames)
|| (!this.tableHeaders && !config.tableHeaders)) {
this.log.error("Dynamic keyword list: sqlQuery, keyNameTemplate, header must be set.");
}
this.tableTitle = config.tableTitle || this.tableTitle;
this.sqlQuery = config.sqlQuery || this.sqlQuery;
this.sqlParams = config.sqlParams || this.sqlParams;
this.tableKeyNames = config.tableKeyNames || this.tableKeyNames;
this.tableHeaders = config.tableHeaders || this.tableHeaders;
/* jdbc database settings. only pass if table is not part of the archive database */
this.dbName = config.dbName || this.dbName;
this.jdbc = config.jdbc || this.jdbc;
this.errorMessage = "";
this.log.exit("initialize");
},
/**
* Opens a connection for the elo java client and non map field capable clients.
*
* @param {de.elo.ix.client.IXServerEventsContext} ec Events context
* @param {Object} sord working version of the current sord object
* @param {String} fieldName name of the currently focused field
*/
open: function (ec, sord, fieldName) {
var me = this,
dbParams;
me.log.enter("open", { sord: sord, fieldName: fieldName });
me.ec = ec;
me.initOpen(ec, sord, fieldName);
if (!me.database) {
me.database = me.openDbConnection();
}
me._keyNames = me.getTableKeyNames(fieldName);
me.index = 0;
dbParams = this.createDbParameterList(ec, fieldName, null, null, sord);
me.resultSet = me.database.query(this.sqlQuery, dbParams);
me.log.exit("open");
},
/**
* Opens a connection for elo wf forms and map field capable components
*
* @param {de.elo.ix.client.IXServerEventsContext} ec Events context
* @param {Object} map map of all entries passed by the client
* @param {String} focusName name of the currently focused field
*/
openMap: function (ec, map, focusName) {
var me = this,
fieldIndex, dbParams;
me.log.enter("openMap", { focusName: focusName, map: map });
me.ec = ec;
me.initOpenMap(ec, map, focusName);
if (!me.database) {
me.database = me.openDbConnection();
}
fieldIndex = me.getIndexFromName(focusName);
me._keyNames = me.getTableKeyNames(focusName).map(function (keyName) {
return !!keyName ? ((fieldIndex != "") ? keyName.replace("{i}", fieldIndex) : keyName) : null;
});
me.index = 0;
dbParams = this.createDbParameterList(ec, focusName, fieldIndex, map, null);
me.resultSet = this.database.query(this.sqlQuery, dbParams);
me.log.exit("openMap");
},
/**
* Closes the connection for both map and non map capable clients.
*/
close: function () {
this.log.enter("close");
this.database = null;
this.log.exit("close");
},
/**
* Returns the next row of the table.
* @param {de.elo.ix.client.IXServerEventsContext} ec Events context
* @return {String[]} table row
*/
getNextRow: function () {
var me = this,
row;
row = this.resultSet[this.index++];
if (row) {
me.prepareRow(row);
row = row.map(function (value) {
return value ? value : "";
});
return row;
}
},
/**
* Initializes the list for an `open` call
*
* @param {de.elo.ix.client.IXServerEventsContext} ec Events context
* @param {Object} sord working version of the current sord object
* @param {String} fieldName name of the currently focused field
*/
initOpen: function (ec, sord, fieldName) {
},
/**
* Initializes the list for an `openMap` call
*
* @param {de.elo.ix.client.IXServerEventsContext} ec Events context
* @param {Object} map map of all entries passed by the client
* @param {String} focusName name of the currently focused field
*/
initOpenMap: function (ec, map, focusName) {
},
/**
* Initializes the list
* @param {Array} row Row
*/
prepareRow: function (row) {
var me = this;
me.formatRow(row);
},
* Returns the header of this table that can be displayed by the clients.
*
* @return {String[]} table header
*/
getHeader: function () {
return this.tableHeaders;
},
/**
* Returns the keys of this table that can be used in order to map
* map or group fields with columns.
*
* @return {String[]} table keys
*/
getKeyNames: function () {
return this._keyNames;
},
/**
* Returns true if table has more rows.
*
* @return {Boolean} has more rows
*/
hasMoreRows: function () {
return (this.index < (this.resultSet.length));
},
/**
* Returns the error message that should be displayed by the client
* instead of the table data.
*
* @return {String} error message
*/
getMessage: function () {
return this.errorMessage;
},
/**
* Returns a title for this table used by the user interface.
*
* @return {String} title
*/
getTitle: function () {
return this.tableTitle || "Database query";
},
/**
* Internal function that opens a database connection for map and non map
* capable clients. Via default the archive database is used but can be changed
* to a custom database connection if jdbc and dbName has been set.
*
* @returns {de.elo.ix.jscript.DBConnection} database connection
*/
openDbConnection: function () {
if (this.jdbc && this.dbName) {
return new Packages.de.elo.ix.jscript.DBConnection(this.jdbc, this.dbName);
} else if (this.jdbc) {
return new Packages.de.elo.ix.jscript.DBConnection(this.jdbc);
} else {
return new Packages.de.elo.ix.jscript.DBConnection();
}
},
/**
* Internal function that utilizes the creation of database parameter lists used by
* prepared statements.
*
* configuration as defined by sqlParams is used to identify fields and the query type.
*
* @param {Object} ec IX ScriptExecContext
* @param {String} focusField currently focused field
* @param {String} fieldIndex index field
* @param {Object} map map of all entries passed by the client
* @param {Object} sord working version of the current sord object
* @returns {String[]} values for prepared statement
*/
createDbParameterList: function (ec, focusField, fieldIndex, map, sord) {
var me = this,
list = [],
i, param, fieldName, value;
me.log.enter("createDbParameterList", { focusField: focusField, fieldIndex: fieldIndex, map: map, sord: sord });
me.errorMessage = "";
me.sqlParams = me.sqlParams || [];
for (i = 0; i < me.sqlParams.length; i++) {
param = me.sqlParams[i];
fieldName = param.name || focusField;
if (fieldIndex) {
fieldName = String(fieldName).replace("{i}", fieldIndex);
}
if (param.value) {
value = param.value;
} else if (param.valueType) {
switch (param.valueType) {
case "LANGUAGE":
value = ec.ci.language;
break;
default:
value = "";
}
} else if (map) {
// WF currently passes IX_GRP for group fields.
// This must be fixed so SQL-Queries can be used in mixed mode.
value = (map[fieldName] || map["IX_GRP_" + fieldName]) || "";
} else {
value = sol.common.SordUtils.getObjKeyValue(sord, fieldName) || "";
}
if (sol.common.StringUtils.isEmpty(value) && param.message) {
me.errorMessage = param.message;
}
switch (param.mode) {
case "STARTS_WITH":
list.push(value + "%");
break;
case "CONTAINS":
list.push("%" + value + "%");
break;
case "ENDS_WITH":
list.push("%" + value);
break;
default:
list.push(String(value));
}
}
me.log.exit("createDbParameterList");
return list;
},
getIndexFromName: function (name) {
name = String(name);
if (!name) {
return "";
}
var pos = name.search(/\d+$/);
if (pos > 0) {
return name.substring(pos);
}
return "";
},
/**
* @deprecated Use {@link sol.common.ix.DynKwlMixin#formatRow} instead
* Formats decimal numbers
* @param {Array} row Row
* @param {Array} defs definitions
* @param {String} defs[].params.columnNames Column names
* @param {Array} defs[].params.columnIndexes Column indexes
* @param {Number} params.decimalPlaces Decimal places
*/
formatDecimals: function (row, defs) {
var me = this,
columnIndexes = [],
i, j, def, columnNames, columnName, columnIndex, value;
defs = defs || [];
for (i = 0; i < defs.length; i++) {
def = defs[i];
def.groupingSeparator = (def.groupingSeparator == false) ? false : true;
columnNames = def.columnNames || [];
columnIndexes = def.columnIndexes || [];
for (j = 0; j < columnNames.length; j++) {
columnName = columnNames[j];
columnIndex = me.tableKeyNames.indexOf(columnName);
if (columnIndex > -1) {
columnIndexes.push(columnIndex);
}
}
for (j = 0; j < columnIndexes.length; j++) {
columnIndex = columnIndexes[j];
value = row[columnIndex] + "";
if (value == "null") {
value == "";
} else {
if (!me.locale) {
me.locale = sol.create("sol.common.Locale", { ec: me.ec });
me.locale.read();
}
value = me.locale.formatDecimal(value, def);
row[columnIndex] = value;
}
}
}
}
});