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;
        }
      }
    }
  }

});