Source: Sql_lib/process.js

import("system.translate");
import("system.vars");
import("system.db");
import("system.datetime");
import("system.tools");
import("system.SQLTYPES");
import("system.text");
import("Util_lib");

/**
 * object for easier handling of conditions;
 * With this object you do not have to check if the string is empty or not;
 * you don't need to append a "1=1" condition or similar;
 * this objects gains most benefit if you have a lot of conditions that are added (or not) depending on tons of conditions
 * @example //TODO: add missing example
 */
function SqlCondition(alias){
   //setting null is only needed to provide autocomplete for the ADITO-designer
    this.preparedValues = null;
    this._init();//the properties are initalized in an extra function because init is nearly the same as resetting (clearing) the SqlConditions
    this.alias = alias;
}
/**
 * append with SQL-and; no paranthesize of existing conditions is done
 * @param {String} cond the condition string which shall be appended
 * @return {Object} current SqlCondition-object
 * @memberof SqlCondition
 */
SqlCondition.prototype.and = function(cond){
    if (!cond)
        return this;
    if (this._sqlStorage)
        this._sqlStorage += " and ";
    this._sqlStorage += cond;
    return this;
}

/**
 * same as the "and"-function but with preparedStatement functionality
 * @param {String} field the database field as "tablename.columnname"; e.g. "ORG.NAME"
 * @param {String} value the value that shall be set into the prepared statement
 * @param {String} cond opt the strucutre of the SQL condition as preparedString, you can use a number sign "#" as placeholder for you fieldname; 
 *                 e.g. "# > ?"; escaping the number sign is possible with a backslash "\"
 *                 Default is "# = ?" 
 * @param {Numeric} [fieldType] SQL-column-type; if the fieldType is not given it's loaded automatically;
 *                              please note that this can be a performace issue if it happens a lot of times (e.g. in aloop)
 * @return {Object} current SqlCondition-object
 */
SqlCondition.prototype.andPrepare = function(field, value, cond, fieldType){
    cond = this._prepare(field, value, cond, fieldType);
    return this.and(cond);
}

/**
 * append with SQL-or; Also paranthesize the existing conditions
 * @param {String} cond the condition string which shall be appended
 * @return {Object} current SqlCondition-object
 */
SqlCondition.prototype.or = function(cond){
    if (!cond)
        return this;
    if (this._sqlStorage)
        this._sqlStorage = "(" + this._sqlStorage + ") or (" + cond + ")";
    else
        this._sqlStorage = cond;
    return this;
}

/**
 * same as the "or"-function but with preparedStatement functionality
 * @param {String} field the database field as "tablename.columnname"; e.g. "ORG.NAME"
 * @param {String} value the value that shall be set into the prepared statement
 * @param {String} cond the strucutre of the SQL condition as preparedString, you can use a number sign "#" as placeholder for you fieldname; 
 *                 e.g. "# > ?"; escaping the number sign is possible with a backslash "\"
 *                 Default is "# = ?" 
 * @param {Numeric} [fieldType] SQL-column-type; if the fieldType is not given it's loaded automatically;
 *                              please note that this can be a performace issue if it happens a lot of times (e.g. in aloop)
 * @return {Object} current SqlCondition-object
 */
SqlCondition.prototype.orPrepare = function(field, value, cond, fieldType){
    cond = this._prepare(field, value, cond, fieldType);
    return this.or(cond);
}

/**
 * same as the "andPrepare"-function but with validation of adito-variables functionality
 * @param {String} field the database field as "tablename.columnname"; e.g. "ORG.NAME"
 * @param {String} variable the adito-variable that shall be set into the prepared statement
 * @param {String} cond opt the strucutre of the SQL condition as preparedString, you can use a number sign "#" as placeholder for you fieldname; 
 *                 e.g. "# > ?"; escaping the number sign is possible with a backslash "\"
 *                 Default is "# = ?" 
 * @param {Numeric} [fieldType] SQL-column-type; if the fieldType is not given it's loaded automatically;
 *                              please note that this can be a performace issue if it happens a lot of times (e.g. in aloop)
 * @return {Object} current SqlCondition-object
 */
SqlCondition.prototype.andPrepareVars = function(field, variable, cond, fieldType){
    variable = this._checkVars(variable)
    if (variable) {
        return this.andPrepare(field, variable, cond, fieldType);
    }
    return this;
}

/**
 * same as the "orPrepare"-function but with validation of adito-variables functionality
 * @param {String} field the database field as "tablename.columnname"; e.g. "ORG.NAME"
 * @param {String} variable the adito-variable that shall be set into the prepared statement
 * @param {String} cond opt the strucutre of the SQL condition as preparedString, you can use a number sign "#" as placeholder for you fieldname; 
 *                 e.g. "# > ?"; escaping the number sign is possible with a backslash "\"
 *                 Default is "# = ?" 
 * @param {Numeric} [fieldType] SQL-column-type; if the fieldType is not given it's loaded automatically;
 *                              please note that this can be a performace issue if it happens a lot of times (e.g. in aloop)
 * @return {Object} current SqlCondition-object
 */
SqlCondition.prototype.orPrepareVars = function(field, variable, cond, fieldType){
    variable = this._checkVars(variable)
    if (variable) {
        return this.orPrepare(field, variable, cond, fieldType);
    }
    return this;
}

/**
 * ready to use string; does not contain a where keyword at the beginning
 * @param {String} alternativeCond opt condition that is returned when nothing has been appended. Default is "1 = 1".
 * @return {String} concatenated SQL-condition; empty string if nothing has been appended or - if passed - the alternativeCond
 */
SqlCondition.prototype.toString = function(alternativeCond){
    if (alternativeCond == undefined) { alternativeCond = "1 = 1" }
    
    if (!this._sqlStorage && alternativeCond)
        return alternativeCond
    else
        return this._sqlStorage;
}

/**
 * ready to use string; does contain a where keyword at the beginning
 * @param {String} alternativeCond opt condition that is returned when nothing has been appended. Default is "1 = 1".
 * @return {String} concatenated SQL-condition; empty string if nothing has been appended or - if passed - the alternativeCond
 */
SqlCondition.prototype.toWhereString = function(alternativeCond){
    var cond = this.toString(alternativeCond);
    if (cond)
        return " where " + cond;
    else 
        return cond;
}

/**
 * ready to use prepared condition; does not contain a where keyword at the beginning
 * @param {String} alternativeCond opt Condition that is returned when nothing has been appended. Default is "1 = 1".
 * @return {Array[][][]} Prepared condition with [condition, [[field1, type1], [field2, type2]]]
 */
SqlCondition.prototype.build = function(alternativeCond){
    return [cond.toString(alternativeCond), cond.preparedValues];
}

/**
 * ready to use prepared select
 * @param {String} beforeCondition Part of the sql before the condition without where (e.g. "select FIRSTNAME from PERS")
 * @param {String} alternativeCond opt Condition that is returned when nothing has been appended. Default is "1 = 1".
 * @param {String} afterCondition opt Part of the sql after the condition (e.g. "order by FIRSTNAME"). Default is "".
 * @return {Array[][][]} Prepared condition with [condition, [[field1, type1], [field2, type2]]]
 */
SqlCondition.prototype.buildSelect = function(beforeCondition, alternativeCond, afterCondition){
    if (afterCondition == undefined) { afterCondition = "" };
    return [beforeCondition  + " " + cond.toWhereString(alternativeCond) + " " + afterCondition, cond.preparedValues];
}

/**
 * Check if (adito-)variable exists and vars.getString is not empty
 * @param {String} variable the variable name (e.g. "$field.RELATION_ID")
 * @return {String | Boolean} The value of the field as string OR false if it doesn't exist.
 */
SqlCondition.prototype._checkVars = function(variable) {
    if (vars.exists(variable)) {
        var value = vars.getString(variable);
        
        if (value) {
            return value;
        }
    }
    return false;
}

/**
 * hidden function for composing preparedStatements
 * @param {String} field the database field as "tablename.columnname"; e.g. "ORG.NAME"
 * @param {String} value the value that shall be set into the prepared statement
 * @param {String} cond the strucutre of the SQL condition as preparedString, you can use a number sign "#" as placeholder for you fieldname; 
 *                 e.g. "# > ?"; escaping the number sign is possible with a backslash "\"
 *                 Default is "# = ?" 
 * @param {Numeric} [fieldType] SQL-column-type; if the fieldType is not given it's loaded automatically;
 *                              please note that this can be a performace issue if it happens a lot of times (e.g. in aloop)
 * @return {String} the replaced SQL-condition string (replace # by the fieldname)
 */
SqlCondition.prototype._prepare = function(field, value, cond, fieldType){
    if (cond == undefined) { cond = "# = ?" }
    var type;
    //this function looks more complex (and slower) than it actually is
    /* the following regex looks like this after javascript-escaping of the backslash: (?<!\\)((?:\\\\)*)#
    the regexp searches for the unescaped character and these characters are replaced by the field name

    examples:
    ---------------------
    | # --match         |
    | \# --no-match     |
    | \\# --match       |
    | \\\# --no-match   |
    | \\\\# --match     |
    ---------------------
    */
   //use replaceAll because it's faster and supports negative lookbehinds
    cond = text.replaceAll(cond, {
        //manually readd the replaced backslashes by using a group reference, because they a part of the match and therefore replaced by "replaceAll"
        //since the field COULD contain already a group reference (I think this is extremely uncommon; 
        //probably that never happens but better stay save): escape that references within the fieldname
        "(?<!\\\\)((?:\\\\\\\\)*)#": "$1" + text.replaceAll(field, {"$1": "\\$1"}),
        //now that we've replaced the correct field placeholder let's replace the escaped number sign "\#" to a normal number sign "#"
        "\\\\#": "#"
    });
    type = fieldType || SqlUtils.getSingleColumnType(field, undefined, this.alias);
    this.preparedValues.push([value.toString(), type]);
    return cond;
}


/**
 * function that resets the current SqlCondition as if no conditions would have been added
 * this is usefull if you want to reuse the same object over and over
 * @return {null} 
 */
SqlCondition.prototype.clear = function(){
    this._sqlStorage = "";
    this.preparedValues = [];
    return null;
}

/**
 * hidden function for initializing all properties for the sql conditions
 * @return {null} 
 */
SqlCondition.prototype._init = function(){
    //init only wraps the clear function to avoid confusion in the constructor (and provide better extensibility)
    return this.clear();
}
/**
 provides functions for masking sql functions
*
* @param {String} [alias=currentAlias] database alias, you can specify null if you have no alias available and  you can manually set the dbType property
*/
function SqlMaskingUtils(alias){
    this.alias = null;
    Object.defineProperty(this, "alias", {
        set: function(v){
            this._alias = v;
            if (v != null)
                this._dbType = db.getDatabaseType(this._alias);
        },
        get: function (){
            return this._alias;
        }
    });
    this.dbType = null;
    Object.defineProperty(this, "dbType", {
        set: function(v){
            this._alias = null;
            this._dbType = v;
        },
        get: function (){
            return this._dbType;
        }
    });
    
    if (alias === undefined)
        this.alias = vars.getString("$sys.dbalias");
    else
        this.alias = alias;
}

/**
*  returns the trim function depending on the database behin the given alias
*  note that this function does not verifiy where the types of your expression are trimable or not
*
* @param {String} field expression that shall be trimmed
*
* @return {String}
*/
SqlMaskingUtils.prototype.trim = function(field) {
    var dbType, resultStr;
    dbType = this.dbType;
    switch(dbType) {
        case db.DBTYPE_SQLSERVER2000:
            resultStr = "ltrim(rtrim(" + field + "))";
            break;
        default:
            resultStr = "trim(" + field + ")"
            break;
    }
    return resultStr;
}


/**
    * masks the function cast of standard sql
    * please note that this function does not do any validation if it's possible to cast the expression's datatype you pass to the function in every supported DBMS
    *
    * @param {String} field name of the database field that shall be castet
    * @param {String} [targetDatatype] a SQLTYPES-value of the following: SQLTYPES.CHAR, SQLTYPES.VARCHAR, SQLTYPES.INTEGER, 
    *                                   SQLTYPES.DECIMAL, SQLTYPES.DATE
    * @param {int|int[]} targetLength specifies the length of the target data type;
    *                                   <br/>- char/varchar: length
    *                                   <br/>- decimal: [length, decimals]
    *
    * @return {String} sql part to be included in sql-statements
    */
SqlMaskingUtils.prototype.cast = function (field, targetDatatype, targetLength){
    //TODO: use callbacks for different handling?
    /* Some informations if you want to add supported databaseTypes or dataTypes:
         * You should consider using the _mapDefaults function-expression (details in the functions doc)
         * However you shouldn't use the function in a "default"-Block of a switch-case because of the following behaviour:
         * If a datatype is not supported you just have to NOT specify "sqlDataType" (leave it "undefined") -> an error is then raised
         * Therefore you should explicitly define which Data-type is supported and which is not
        */
    var dbType, functionName, sqlPart, sqlDataType, _mapDefaults;
    dbType = this.dbType;
    functionName = "cast";//overwrite this in the "switch (dbType)" if needed with your DBMS

    /**
         * handles default-scenarios for mapping input-targetDatatype to a string for a sql-statement
         * e.g. SQLTYPES.INTEGER --> int
         * @param {Number} dataType input as a value of "SQLTYPES." that will be mapped to a string
         * @return {String} the mapped dataType for using in a sql-statement
         */
    _mapDefaults = function (dataType){
        var res;
        switch(dataType) {
            case SQLTYPES.CHAR:
                res = "char";
                break;
            case SQLTYPES.VARCHAR:
                res = "char";
                break;
            case SQLTYPES.INTEGER:
                res = "int";
                break;
            case SQLTYPES.DECIMAL:
                res = "decimal";
                break;
            case SQLTYPES.DATE:
                res = "date";
                break;
        }
        return res;
    }
        
    switch (dbType) {
        case db.DBTYPE_DERBY10:
            switch(targetDatatype) {
                case SQLTYPES.VARCHAR:
                    // Because of a Derby bug, you can't cast INTEGER into VARCHAR
                    // Therefor first cast to char then to varchar
                    // https://issues.apache.org/jira/browse/DERBY-2072
                    field = "rtrim(" + this.cast(field, SQLTYPES.CHAR, targetLength) + ")";
                    sqlDataType = "varchar";
                    break;
                case SQLTYPES.CHAR:
                    //TODO: throw error if(targetLength > 254)? https://db.apache.org/derby/docs/10.14/ref/rrefsqlj13733.html
                    sqlDataType = "char";
                    break;
                case SQLTYPES.DECIMAL:
                case SQLTYPES.INTEGER:
                case SQLTYPES.DATE:
                    sqlDataType = _mapDefaults(dataType);
                    break;
            }
            break;
        case db.DBTYPE_MARIADB10:
        case db.DBTYPE_MYSQL4:
            switch(targetDatatype) {
                case SQLTYPES.VARCHAR:
                case SQLTYPES.CHAR:
                case SQLTYPES.INTEGER:
                case SQLTYPES.DECIMAL:
                case SQLTYPES.DATE:
                    sqlDataType = _mapDefaults(targetDatatype);
                    break;
            }
            break;
        case db.DBTYPE_ORACLE10_CLUSTER:
        case db.DBTYPE_ORACLE10_THIN:
        case db.DBTYPE_ORACLE10_OCI:
            switch(targetDatatype)
            {
                case SQLTYPES.VARCHAR:
                    datatype = "varchar2";
                    break;
                case SQLTYPES.INTEGER:
                    datatype = "number";
                    targetLength = "10"
                    break;
                case SQLTYPES.CHAR:
                case SQLTYPES.DECIMAL:
                case SQLTYPES.DATE:
                    sqlDataType = _mapDefaults(targetDatatype);
                    break;
            }
            break;
        case db.DBTYPE_POSTGRESQL8:
            switch(targetDatatype)
            {
                case SQLTYPES.DATE:
                case SQLTYPES.DECIMAL:
                case SQLTYPES.INTEGER:
                case SQLTYPES.CHAR:
                case SQLTYPES.VARCHAR:
                    sqlDataType = _mapDefaults(targetDatatype);
                    break;
            }
            break;
        case db.DBTYPE_SQLSERVER2000:
        case SQLTYPES.DATE:
        case SQLTYPES.DECIMAL:
        case SQLTYPES.INTEGER:
        case SQLTYPES.CHAR:
        case SQLTYPES.VARCHAR:
            sqlDataType = _mapDefaults(targetDatatype);
            break;
        case db.DBTYPE_FIREBIRD250:
            //TODO: firebird support?
            break;
    }

    if (sqlDataType == undefined) {
        throw new Error("sqlDataType");//TODO: add usefull message
    }

    if(targetLength == undefined)
        targetLength = "";
    else if(targetLength != "")
    {
        if(typeof(targetLength == "object") && (targetLength instanceof Array))
            targetLength = "(" + targetLength.join(", ") + ")";
        else
            targetLength = "(" + targetLength + ")";
    }

    sqlPart = functionName + "(" + field + " as " + sqlDataType + targetLength + ")";
    return sqlPart;
}

/**
 * masks the cast function for lob datatypes(clob, blob) into varchar or similar
 *
 * @param {String} field expression that shall be casted
 * @param {Integer|Interger[]} targetLength dessired length of the datatype
 *                                         decimal: [length, decimals]
 *
 * @return {String} part of sql-expression that can be used
 */
SqlMaskingUtils.prototype.castLob = function(field, targetLength) {
    var res;
    switch(this.dbType) {
        case db.DBTYPE_ORACLE10_CLUSTER:
        case db.DBTYPE_ORACLE10_THIN:
        case db.DBTYPE_ORACLE10_OCI:
            res = "DBMS_LOB.SUBSTR(" + field + ", " + targetLength + ", 1)";
            break;
        default:
            res = this.cast(field, "varchar", targetLength);
            break;
    }
    return res;
}

/**
    * masks the sql function substring
    *
    * @param {String } field the expression that shall be substringed
    * @param {Number} start posistion where the substring starts
    * @param {Number} length amount of characters of the expression will be returned by the sql function
    *
    * @return {String} part of sql-expression that can be used for substringing
    */
SqlMaskingUtils.prototype.substring = function(field, start, length){
    var sqlFnName;

    switch(this.dbType)
    {
        case db.DBTYPE_ORACLE10_CLUSTER:
        case db.DBTYPE_ORACLE10_THIN:
        case db.DBTYPE_ORACLE10_OCI:
            sqlFnName = "substr";
            break;
        case db.DBTYPE_DERBY10:
            sqlFnName = "substr";
            break;
        case db.DBTYPE_POSTGRESQL8:
            sqlFnName = "substr";
            break;
        case db.DBTYPE_SQLSERVER2000:
            sqlFnName = "substring";
            break;
        case db.DBTYPE_MYSQL4:
        case db.DBTYPE_MARIADB10:
            sqlFnName = "substring";
            break;
        default:
            throw new Error();//ToDo: add message
    }

    return sqlFnName + "(" + field + ", " + start + ", " + length + ")";
}


/**
* masks the function concat
* if a sql field is empty no separator will be added
* note that this function will often create a lot of sql-code
*
* @param {Array} fields req fields (or expressions) that should be concatenated
* @param {String} [separatorCharacter=space-character] character for separating the fields; warning: the character will not be quoted
* @param {String} [autoTrimFields=true] autoTrimFields if true the expressions are always trimmed, false no change will be applied
*
* @return {String} part of SQL-querey
*/
SqlMaskingUtils.prototype.concat = function(fields, separatorCharacter, autoTrimFields) {
    var i, concatSql, retSql, isNotEmptyStrSql, isNotNullSql, separatorSql, _isNotEmpty, _trimIfAutoTrimEnabled;
    if (fields.length == 0)
        return "''";
    else if (fields.length == 1)
        return fields[0];
    concatSql = " || ";
    isNotEmptyStrSql =  " != '' ";
    isNotNullSql = " is not null ";
    separatorSql = separatorCharacter == undefined ? " " : separatorCharacter;
    retSql = "";
    if (autoTrimFields == undefined)
        autoTrimFields = true;

    switch(this.dbType)
    {
        case db.DBTYPE_MYSQL4:
        case db.DBTYPE_MARIADB10:
            retSql = " concat_ws( '" + separatorSql + "'";
            for (i = 0; i < fields.length; i++) {
                retSql += ", " + fields[i];
            }
            return retSql + ") ";
            break;
        case db.DBTYPE_ORACLE10_CLUSTER:
        case db.DBTYPE_ORACLE10_THIN:
        case db.DBTYPE_ORACLE10_OCI:
            isNotEmptyStrSql = null; //empty strings are changed to DB-null-values internally in oracle; by specifing JS-null we disable this check
            break;
        case db.DBTYPE_SQLSERVER2000:
            //MS SQL Server supports "concat_ws" (and ignoring null values) from version SQL Server 2017 and newer:
            //https://docs.microsoft.com/de-de/sql/t-sql/functions/concat-ws-transact-sql?view=sql-server-2017
            concatSql = " + ";
            break;
        case db.DBTYPE_DERBY10:
            break;
        default:
            throw new Error();//TODO: add Message
    }
    separatorSql = concatSql + "'" + separatorSql + "'";
    _trimIfAutoTrimEnabled = function(f){
        if (autoTrimFields)
            return this.trim(f);
        else
            return f;
    }
    _trimIfAutoTrimEnabled = _trimIfAutoTrimEnabled.bind(this);
    _isNotEmpty = function(f){
        return _trimIfAutoTrimEnabled(f) + isNotEmptyStrSql + " and " + f + isNotNullSql;
    }
    _isNotEmpty = _isNotEmpty.bind(this);
    
    for (i = 0; i < fields.length; i++) {
        if (retSql != "")  
            retSql += concatSql;
        retSql += "case when " + _isNotEmpty(fields[i]) + " then ";
        if ( i < fields.length - 1 ) //Prüfen, ob ein nachfolgender Wert kommt, sonst braucht man keinen Separator
            retSql += " case when " + _isNotEmpty(fields[i + 1]) + " then " + _trimIfAutoTrimEnabled(fields[i]) + separatorSql + " else " + _trimIfAutoTrimEnabled(fields[i]) + " end "; 
        else
            retSql += _trimIfAutoTrimEnabled(fields[i]);
        retSql += " else '' end ";
    }
    return retSql;
}

/**
     * returns the function for replacing a null value
     *
     * @param {String} field expression that shall be checked for a null value
     * @param {String} [replaceWith=empty string] expression that shall be used if the field contains null
     *
     * @return {string}
     */
SqlMaskingUtils.prototype.isNull = function(field, replaceWith) {
    var retSql;
    
    if (replaceWith == undefined)
        replaceWith = "''";
    switch(this.dbType) {
        case db.DBTYPE_SQLSERVER2000:
            retSql = "isnull(" + field + ", " + replaceWith + ")";
            break;
        case db.DBTYPE_ORACLE10_CLUSTER:
        case db.DBTYPE_ORACLE10_OCI:
        case db.DBTYPE_ORACLE10_THIN :
            retSql = "nvl(" + field + ", " + replaceWith + ")";
            break;
        case db.DBTYPE_POSTGRESQL8:
        case db.DBTYPE_DERBY10:
        case db.DBTYPE_MYSQL4:
        case db.DBTYPE_MARIADB10:
        default:
            retSql = "coalesce(" + field + ", " + replaceWith + ")";
            break;
    }
    return retSql;
}

function SqlUtils(){
}

SqlUtils.getSingleColumnType = function(fieldOrTableName, columnName, alias) {
    var tableName, fieldVarType;
    if (columnName == undefined){
        fieldVarType = typeof(fieldOrTableName);
        if (fieldVarType == "string")
            fieldOrTableName = text.split(fieldOrTableName, "\\.");
        else if (fieldVarType != "object"){
            throw new TypeError();//TODO: add message
        }

        if (fieldOrTableName.hasOwnProperty("length") && fieldOrTableName.length == 2)
        {
            tableName = fieldOrTableName[0];
            columnName = fieldOrTableName[1];
        }
        else
            throw new TypeError();//TODO: add message
    }
    else
        tableName = fieldOrTableName;

    if (typeof(columnName) != "string")
        throw new TypeError();//TODO: add message
    if (typeof(tableName) != "string")
        throw new TypeError();//TODO: add message

    if (alias == undefined)
        alias = db.getCurrentAlias();

    return db.getColumnTypes(tableName, [columnName], alias)[0];
}

/**
 *Class containing utilities for SQL
 *@deprecated use SqlMaskingUtils
 *@class
 */
function LegacySqlUtils()
{
    var that = this;
    /**
    * builds a condition out of multiple conditions
    *
    * @param {Array} pArray req Array containing the conditions
    * @param {String} pOperator req Operator that concatenates the conditions (AND/OR)
    *
    * @return {String} concatenated Condition
    */
    this.concatConditions = function(pArray, pOperator)
    {
        var resultCondition = "";

        for(var i = 0; i < pArray.length; i++)
        {
            if(pArray[i] != null && pArray[i] != '')
            {
                if(resultCondition.length > 0)
                    resultCondition += (" " + pOperator + " ");

                resultCondition += pArray[i];
            }
        }

        return resultCondition;
    }
    /**
    * Checks if a new entry already exists
    *
    * @param {String} pTable req Databasetable(z.B. "comm")
    * @param {Array} pColumns req colums, like sqlInsert
    * @param {Array} pTypes req die datatypes, like sqlInsert
    * @param {Array} pValues req values, like sqlInsert
    * @param {Array} pExcludeFields opt columns, that should not be checked
    * @param {String} pAlias opt Database alias
    *
    * @return {Integer}
    */
    this.isDuplicat = function(pTable, pColumns, pTypes, pValues, pExcludeFields, pAlias)
    {
        var col = new Array();
        var typ = new Array();
        var val = new Array();
        var excludefields = ["DATE_NEW" ,"DATE_EDIT" ,"USER_NEW" ,"USER_EDIT", "KEYVALUE",  "KEYSORT", pTable.toUpperCase() + "ID"];

        if(pExcludeFields != undefined) 
            excludefields = excludefields.concat(pExcludeFields);
        if(pAlias == undefined)  
            pAlias = vars.getString("$sys.dbalias");

        for(var i = 0; i < pColumns.length; i++)
        {
            if(!hasElement(excludefields, pColumns[i], true) && pValues[i] != "" && pTypes[i] != SQLTYPES.LONGVARCHAR && pTypes[i] != SQLTYPES.CLOB)
            {
                col.push(pColumns[i]);
                typ.push(pTypes[i]);
                val.push(pValues[i]);
            }
        }
        var count = db.getRowCount(pTable, col, typ, val, pAlias);
        return count;
    }

    /**
    * returns the day of a date
    *
    * @param {Datetime} pDate req the date
    * @param {String} pAlias req database alias
    *
    * @return {String}
    */
    this.dayFromDate = function(pDate, pAlias)
    {
        var usedAlias = pAlias;

        if(pAlias == undefined) 
            usedAlias = vars.getString("$sys.dbalias");

        var dbAlias = db.getDatabaseType(usedAlias);
        var string = "";

        switch(Number(dbAlias))
        {
            case db.DBTYPE_ORACLE10_CLUSTER:
            case db.DBTYPE_ORACLE10_THIN:
            case db.DBTYPE_ORACLE10_OCI:
                string = "to_char(" + pDate + ",'dd')";
                break;
            case db.DBTYPE_DERBY10:
            case db.DBTYPE_SQLSERVER2000:
            case db.DBTYPE_MYSQL4:
            case db.DBTYPE_MARIADB10:
                string = "DAY(" + pDate + ")";
                break;
            case db.DBTYPE_POSTGRESQL8:
                string = "EXTRACT (DAY from " + pDate + ")";
                break;
        }
        return string;
    }
    /**
    * returns the month of a date
    *
    * @param {Datetime} pDate req the date
    * @param {String} pAlias req database alias
    *
    * @return {String}
    */
    this.monthFromDate = function(pDate, pAlias)
    {
        var usedAlias = pAlias;
        if(pAlias == undefined) 
            usedAlias = vars.getString("$sys.dbalias");

        var dbAlias = db.getDatabaseType(usedAlias);
        var string = "";

        switch(Number(dbAlias))
        {
            case db.DBTYPE_ORACLE10_CLUSTER:
            case db.DBTYPE_ORACLE10_THIN:
            case db.DBTYPE_ORACLE10_OCI:
                string = "to_char(" + pDate + ",'MM')";
                break;
            case db.DBTYPE_DERBY10:
            case db.DBTYPE_SQLSERVER2000:
            case db.DBTYPE_MYSQL4:
            case db.DBTYPE_MARIADB10:
                string = "MONTH(" + pDate + ")";
                break;
            case db.DBTYPE_POSTGRESQL8:
                string = "EXTRACT (MONTH FROM " + pDate + ")";
                break;
        }
        return string;
    }
    /**
    * returns the year of a date
    *
    * @param {Datetime} pDate req the date
    * @param {String} pAlias req database alias
    *
    * @return {String}
    */
    this.yearFromDate = function(pDate, pAlias)
    {
        var usedAlias = pAlias;
        if(pAlias == undefined) 
            usedAlias = vars.getString("$sys.dbalias");

        var dbAlias = db.getDatabaseType(usedAlias);
        var string = "";

        switch(Number(dbAlias))
        {
            case db.DBTYPE_ORACLE10_CLUSTER:
            case db.DBTYPE_ORACLE10_THIN:
            case db.DBTYPE_ORACLE10_OCI:
                string = "to_char(" + pDate + ",'yyyy')";
                break;
            case db.DBTYPE_DERBY10:
            case db.DBTYPE_SQLSERVER2000:
            case db.DBTYPE_MYSQL4:
            case db.DBTYPE_MARIADB10:
                string = "YEAR(" + pDate + ")";
                break;
            case db.DBTYPE_POSTGRESQL8:
                string = "EXTRACT (YEAR FROM " + pDate + ")";
                break;
        }
        return string;
    }
    /**
    * returns the function for current date depending on database
    *
    * @return {String} expression
    */
    this.currentDate = function()
    {
        var dbtype = db.getDatabaseType(vars.getString("$sys.dbalias"));
        var expression = "";

        switch (Number(dbtype))
        {
            case db.DBTYPE_ORACLE10_CLUSTER:
            case db.DBTYPE_ORACLE10_THIN:
            case db.DBTYPE_ORACLE10_OCI:
            case db.DBTYPE_DERBY10:
                expression = "CURRENT_DATE";
                break;
            case db.DBTYPE_SQLSERVER2000:
                expression = "GETDATE()";
                break;
            case db.DBTYPE_MYSQL4:
            case db.DBTYPE_MARIADB10:
                expression = "NOW()";
                break;
        }
        return expression;
    }
    /**
    * returns the current search string incl placeholders
    *
    * @param {String} pfield req the search field
    * @param {String} pfind req the search string
    * @param {String} pIgnoreCase opt (true/false)
    * @param {String} pPlaceHolder opt (Platzhalter config)
    *
    * @return {String}
    */
    this.getPlacerholderCondition = function( pfield, pfind, pIgnoreCase, pPlaceHolder )
    {
        var user = tools.getCurrentUser();
        var IgCa;
        var PlHo;

        //wenn optoinal IgnoreCase und PlaceHolder vorhanden, dann diese verwenden
        if(pIgnoreCase != undefined)
            IgCa = pIgnoreCase;
        else
            IgCa = user[tools.PARAMS][tools.SELECTION_IGNORECASE];

        if(pPlaceHolder != undefined)
            PlHo = pPlaceHolder;
        else
            PlHo = user[tools.PARAMS][tools.SELECTION_PLACEHOLDER];

        if ( pfind )
        {
            pfind = pfind.replace( new RegExp("\\'", "g"), "''");
            pfind = pfind.replace( new RegExp("\\*", "g"), "%");
            var ic = (IgCa == "true" ? "UPPER" : "");
            var cond = "";
            switch( PlHo )
            {
                case "1":
                    cond = ic + "(" + pfield + ") like " + ic + "('" + pfind + "%')";
                    break;
                case "2":
                    cond = ic + "(" + pfield + ") like " + ic + "('%" + pfind + "')";
                    break;
                case "3":
                    cond = ic + "(" + pfield + ") like " + ic + "('%" + pfind + "%')";
                    break;
                case "4":
                    cond = ic + "(" + pfield + ") like " + ic + "('" + pfind + "')";
                    break;
                default:
                    cond = ic + "(" + pfield + ") = " + ic + "('" + pfind + "')";
                    break;
            }
        }
        return cond;
    }
    /**
    * returns SQLSystax for a date.
    *
    * @param {String} pColumn req Column name
    * @param {String} pAlias opt Database alias
    * @param {Boolean} pWithTime opt if true, then add time hh:mm:ss
    * @param {String} pFormat opt type of the format, e.g. yyyy-MM-dd; possible placeholers are: -dd -MM -yyyy
    *
    *@return {String} sqlstr, fully functional select for sql for different types of databases
    */
    this.getSqlFormattedDate = function(pColumn, pAlias, pWithTime, pFormat)
    {
        if(pAlias == undefined)  
            pAlias = vars.getString("$sys.dbalias");

        var pDatabaseType = db.getDatabaseType(pAlias);

        if (pFormat == undefined)
            pFormat = translate.text("yyyy-MM-dd");

        var str = "";

        switch(Number(pDatabaseType))
        {
            case db.DBTYPE_SQLSERVER2000:
                day = "right('0' + cast(day(" + pColumn + ") as varchar(2)) , 2)";
                month = "right('0' + cast(month(" + pColumn + ") as varchar(2)) , 2)";
                year = "cast(year(" + pColumn + ") as char(4))";
                time = pWithTime == true? that.concat(["' '","cast(cast(" + pColumn + " as time) as char(8))"], "", pAlias) : "";
                break;
            case db.DBTYPE_POSTGRESQL8:
                day = "extract(day from " + pColumn + ")";
                month = "extract(month from " + pColumn + ")";
                year = "extract(year from " + pColumn + ")";
                time = pWithTime == true? that.concat(["' '","extract(time from" + pColumn + ")"], "", pAlias) : "";
                break;
            case db.DBTYPE_DERBY10:
            case db.DBTYPE_MYSQL4:
            case db.DBTYPE_MARIADB10:
                // concat will try to have a leading blank space if the number is => 10. This is why we had to use substr.
                day = that.substring(that.concat(["case when day(" + pColumn + ") <= 9 then '00' else '0' end "
                    , " trim(cast(day(" + pColumn + ") as char(2)))"], "", pAlias), 2, 2, pAlias);
                month = that.substring(that.concat(["case when month(" + pColumn + ") <= 9 then '00' else '0' end "
                    , "trim(cast(month(" + pColumn + ") as char(2)))"], "", pAlias), 2, 2, pAlias);
                year = "trim(cast(year(" + pColumn + ") as char(4)))";
                time = pWithTime == true? that.concat(["cast(' ' as char(1))", "trim(cast(time(" + pColumn + ") as char(8)))"], "", pAlias) : "";
                break;
            case db.DBTYPE_ORACLE10_CLUSTER:
            case db.DBTYPE_ORACLE10_OCI:
            case db.DBTYPE_ORACLE10_THIN:

                day = "to_char(" + pColumn + ", 'dd') ";
                month = "to_char(" + pColumn + ", 'MM') ";
                year = "to_char(" + pColumn + ", 'yyyy') ";
                time = pWithTime == true ? " to_char(" + pColumn + ", ' hh24:mi:ss')" : "";
                break;
            default:
                str = "cast(" + pColumn + " as varchar (10))";
                return str;
                break;
        }

        var re = /(dd)|(MM)|(yyyy)/g        // Regex to check the date
        var matchResult;
        var endOfLastMatch = 0;
        var res = [];

        while ((matchResult = re.exec(pFormat)) !== null) 
        {
            if( endOfLastMatch != matchResult.index)
            {
                res.push("'" + db.quote(pFormat.substring(endOfLastMatch, matchResult.index), pAlias) + "'");     // making sure we get the correct amount of quotations
            }
            switch(matchResult[0])
            {
                case "dd":
                    res.push(day);
                    break;
                case "MM":
                    res.push(month);
                    break;
                case "yyyy":
                    res.push(year);
                    break;
            }
            endOfLastMatch = re.lastIndex;
        }
        // making sure we get the correct amount of quotations
        // allows us to add custom strings behind the format which will be shown in the output
        // e.g. "yyyy-MM-dd 00:00", "date: MM/dd/yyyy"
        res.push("'" + db.quote(pFormat.slice(endOfLastMatch), pAlias) + "'");

        if(time != "")
            res.push(time);

        str = concat(res, "", pAlias);

        return str;
    }
    /**
    * returns a SQL operator depending on an integer value, i.e. $local.operator
    *
    * @param {int} pVal
    *
    * @return {string}
    */
    this.getSQLOperator = function(pVal)
    {
        var retval = "";
        switch(Number(pVal))
        {
            case 1:
                retval = "=";
                break; //equals
            case 2:
                retval = "<>";
                break; //not equal
            case 3:
                retval = ">";
                break; //greater
            case 4:
                retval = "<";
                break; //lesser
            case 5:
                retval = "<=";
                break; //lesser or equal
            case 6:
                retval = ">=";
                break; //greater or equal
            case 7:
                retval = "like";
                break; //contains
            case 8:
                retval = "not like";
                break; //contains not
            case 9:
                retval = "";
                break;
            case 10:
                retval = "";
                break;
            case 11:
                retval = "is not null";
                break;
            case 12:
                retval = "is null";
                break;
        }
        return retval;
    }
    /**
    *  returns the function which determines the length of binary data, depending on db type
    *
    * @param {String} pField name of the checked field
    *
    * @return {String}
    */
    this.binDataLength = function(pField)
    {
        var dbtype = db.getDatabaseType(vars.getString("$sys.dbalias"));
        var length;

        switch(Number(dbtype))
        {
            case db.DBTYPE_MARIADB10:
            case db.DBTYPE_MYSQL4:
            case db.DBTYPE_ORACLE10_CLUSTER:
            case db.DBTYPE_ORACLE10_THIN:
            case db.DBTYPE_ORACLE10_OCI:
            case db.DBTYPE_POSTGRESQL8:
            case db.DBTYPE_DERBY10:
                length = "LENGTH("+pField+")";
                break;
            case db.DBTYPE_SQLSERVER2000:
                length = "DATALENGTH("+pField+")";
                break;
        }
        return length;
    }

    /**
    * returns the concat symbol depending on database type
    *
    * @param {String} pAlias opt database alias
    *
    * @return {String} Concat Symbol
    */
    this.getConcatSymbol = function(pAlias)
    {
        if(pAlias == undefined || pAlias == "")  
            pAlias =  vars.getString("$sys.dbalias");

        var dbtype = db.getDatabaseType(pAlias);
        var concatSymbol = " ";

        switch(Number(dbtype))
        {
            case db.DBTYPE_SQLSERVER2000:
                concatSymbol = " + ";
                break;
            case db.DBTYPE_MARIADB10:
            case db.DBTYPE_MYSQL4:
            case db.DBTYPE_ORACLE10_CLUSTER:
            case db.DBTYPE_ORACLE10_THIN:
            case db.DBTYPE_ORACLE10_OCI:
            case db.DBTYPE_POSTGRESQL8:
            case db.DBTYPE_DERBY10:
            default:
                concatSymbol = " || ";
                break;
        }

        return concatSymbol;
    }
    /**
     * Builds a SQL IN condition, while accounting for the 1000 elements maximum
     * Single conditions are concatenated with OR, which can be devastating for performance!
     *
     * @param {String} pFieldname req name of the field with table alias
     *                                z.B ORGREL.RELATIONID
     * @param {String[]|String[][]} pData req Data as ID Array
     * @param {String} pQuoteSymbol opt symbol for quoting values,
     *                                  Strings i.e.: ' default is no symbol
     *
     * @return {String} SQL condition: where VALS in (1,2,3)
     */
    this.getSqlInStatement = function(pFieldname, pData, pQuoteSymbol)
    {
        if (pData.length == 0)
            return " 1 = 2 ";

        var res = "";
        var qs = pQuoteSymbol || "";

        var MAX_COUNT = 1000;
        //pData.length -1 um für den Fall, dass MAX_COUNT == pData.length ist trotzdem nur einen Aufruf
        //zu machen
        var count = ((pData.length -1) / MAX_COUNT) >> 0;//aus kommazahl eine ganzzahl machen
        //<= verwenden, da bei einer Länge von "126" der Vorgang einmal ausgeführt werden soll
        for (var i = 0; i <= count; i++)
        {
            if (i > 0)
                res += "or ";

            res += pFieldname + " in (" + qs + pData.slice(i * MAX_COUNT, i * MAX_COUNT + MAX_COUNT)
            .join(qs + ", " + qs) + qs + ") ";
        }

        //wenn mehrere Zeilen mit "or" verknüpft wurden nochmal klammern
        if (count > 0)
            res = "(" + res + ")";

        return res;
    }
    /**
    * Setzt eine Condition zusammen und liefert sie zurück
    * builds a conditions and returns it
    *
    * @param {Object} pValue req Filtervalue
    * @param {String} pCondition req variable in which the condition should be written
    * @param {String} pWhere req additional condition
    * @param {Integer} pSQLType opt SQLTYPES type of pValue
    * @param {Array} pPreparedValues opt Value for the condition, if it's a prepared statement
    *
    * @return {String}
    */
    this.makeCondition = function( pValue, pCondition, pWhere, pSQLType, pPreparedValues)
    {
        if ( pValue != "" )
        {
            if ( pCondition != "" ) 
                pCondition += " and ";

            pCondition += pWhere;

            if(pPreparedValues != undefined)
            {
                pPreparedValues.push([pValue, pSQLType]);
            }
        }
        return pCondition;
    }
    /**
     * returns a type of column in the database
     *
     * @param {String} pTableName req name of a table (e.g. "EVENT") OR if pColumnName is not passed table.column (e.g. "EVENT.STATUS")
     * @param {String} pColumnName opt name of column (e.g. "STATUS") if in pTableName only tablename is passed
     * @param {String} pAlias opt Alias to the database where the type should be loaded; default is current alias
     *
     * @return {String} type of column such as SQLTYPES.xyz
     */
    this.getSingleColumnType = function(pTableName, pColumnName, pAlias)
    {
        if (pColumnName == undefined)
        {
            pColumnName = pTableName.substring(pTableName.indexOf(".") + 1);
            pTableName = pTableName.substring(0, pTableName.indexOf("."));
        }
        if (pAlias == undefined)
            pAlias = db.getCurrentAlias();

        return db.getColumnTypes(pTableName, [pColumnName], pAlias)[0];
    }
    /**
    * calls a given function for N blocks of sql-data as long as records are available or the paging-process is manually canceled
    *
    * @param {Object|String} pSql req sql statement that shall be executed
    *                                 String: SQL-query in a simple text form
    *                                 Object: prepared-sql-query: [sqlStr, [[value1, type1], [valueN, typeN]]]
    * @param {Number} pBlockSize req Amount of records that shall be read per block. (you need to specify an ORDER BY in your SQL-query)
    *                                "0" <=> all records
    * @param {Object (function)} pCallback req a callback-function that is called for every block and has the following params:
    *                                            myCallback(myDataBlockAs2Darray, myLoopCountThatStartsWith1)
    *                                          If "false" is returned sqlPageData will abort the paging process and return false
    * @param {String} pDbAlias opt Database-Aliasname, where the SQL-Statement shall be executed; default is the current dbalias
    * @param {Number} pTimeout opt Timeout in milliseconds; When it's reached the SQL-Statement will abort; default is in PREFERENCES configured
    * @param {Number} pStartOffset opt Position where to begin with  the data-reading-process; default is 0
    *
    *
    * @return {bool} returns whether the function read all available data or not:
    *                        false if the callback-function returned false, otherwise true
    *
    * @example
    * var varValues = [];//you've got access to variables declared with 'var'
    * let letValues = [];//you've got access to variables declared with 'let'
    * var count = 0;//you cannot overwrite a variable of 'sqlPageData' by accident
    *
    * var sql = "select ORGNAME from ORG";
    * var blockSize = 5 * 1000;
    *
    * var allRows = +db.cell("select count(*) from ORG");
    *
    * sqlPageData(sql, blockSize, function (pData, pRunNo){
    *     var j = pData.length;//pData is the current block with data
    *     logging.log(pRunNo.toString() + "#" + j);//pRunNo is the amount how often the func. has been already called
    *     //you can calculate the progress easily by: progress = (blockSize* (pRunNo-1) + pData.length) / (allRows - startOffset)
    *     //example in per cent:
    *     var startOffset = 0;//we did not pass any startOffset to sqlPageData - this is equivalent to zero
    *     var progress = (blockSize* (pRunNo-1) + pData.length) / (allRows - startOffset);
    *     logging.log("progess: " + eMath.roundDec(progress * 100, 2, eMath.ROUND_CEILING) + "%");
    *
    *     for (var i = 0; i < j; i++)
    *     {
    *         varValues.push(pData[i][0]);
    *         letValues.push(pData[i][0]);
    *     }
    *
    *     count += pRunNo * 100;
    *     logging.log("count:" + count);//you cannot overwrite a variable of 'sqlPageData' by accident
    * });
    *
    * logging.show(letValues);//contains orgnames
    * logging.show(varValues);//contains orgnames
    */
    this.sqlPageData = function(pSql, pBlockSize, pCallback, pDbAlias, pTimeout, pStartOffset)
    {
        if (pDbAlias == undefined)
            pDbAlias = db.getCurrentAlias();

        if (pStartOffset == undefined)
            pStartOffset = 0;

        let count = 0;
        while (pStartOffset > -1)
        {
            let data;
            if (pTimeout == undefined)
                data = db.tablePage(pSql, pDbAlias, pStartOffset, pBlockSize);
            else
                data = db.tablePage(pSql, pDbAlias, pStartOffset, pBlockSize, pTimeout);

            pStartOffset += pBlockSize;

            //this happens when all-records % pBlockSize == 0
            //we do not want to call the callback-fn
            if (data.length == 0)
                return true;
            else if (data.length < pBlockSize || pBlockSize == 0)//blocksize 0 is everything
                pStartOffset = -1;//call callback the last time

            if (pCallback.call(this, data, ++count) === false)
                return false;//callback can return false to manually stop the paging-process

        }
        return true;
    }
}