process.js

import("system.process");
import("system.util");
import("system.vars");
import("system.net");
import("system.logging");
import("system.db");
import("system.entities");
import("Sql_lib");
import("system.indexsearch");

/**
 * Methods for duplicate scanning.
 * Do not create an instance of this!
 * 
 * @class
 */
function DuplicateScannerUtils() {}

/* 
 * Loads all prefilters for a scanner in the form of arrays in an array.
 * Single filter record: [CONDITION, COUNT_CHARACTERS_TO_USE, MAX_RESULTS_THRESHOLD]
 * 
 *  @param {String} pFilterName Name of the filter
 *  @param {String} pTargetEntity Entity which has been configured
 *  @returns {String[[]]} Array of arrays containing the configured values 
 */
DuplicateScannerUtils.loadFilters = function(pFilterName, pTargetEntity)
{
    let query = "select \"CONDITION\", COUNT_CHARACTERS_TO_USE, MAX_RESULTS_THRESHOLD from DUPLICATESCANNERPREFILTERCONFIG"
                + " join DUPLICATESCANNER on DUPLICATESCANNER.ID = DUPLICATESCANNERPREFILTERCONFIG.DUPLICATESCANNER_ID"
                + " where FILTER_NAME = '" + pFilterName + "'"
            + " and ENTITY_TO_SCAN_NAME = '" + pTargetEntity + "'";
    return db.table(query);
}

/*
 * Deletes the cached duplicate for the given id. 
 * If there would only remain one item in the cluster after deletion, the whole cluster including the duplicate gets deleted.
 * In this case, all records marked as unrelated duplicate will be deleted aswell.
 * 
 * @param {String} pDuplicateId Id of the duplicate to delete
 */
DuplicateScannerUtils.DeleteCachedDuplicate = function(pDuplicateId)
{
    let query = "select count(ID), CLUSTERID from DUPLICATECLUSTERS"
    + " where CLUSTERID = (select CLUSTERID from DUPLICATECLUSTERS where DUPLICATEID = '"+ pDuplicateId +"')"
    + " and DUPLICATEID != '"+ pDuplicateId +"'"
    + " group by CLUSTERID";

    let coundAndClusterId = db.array(db.ROW, query);
    let countDuplicatesInClusterWithoutParameterId = coundAndClusterId[0];
    let clusterId = coundAndClusterId[1];
    
    //If only one duplicate would be remaining, 
    //the whole cluster has to be deleted because there are no more duplicates.
    //Otherwise delete just the single duplicate. The parameterized duplicate has been excluded via sql
    //therefore check for smaller/equals 1
    if(countDuplicatesInClusterWithoutParameterId <= 1)
    {
        let deleteStatements = [];
        deleteStatements.push(["DUPLICATECLUSTERS", "DUPLICATECLUSTERS.CLUSTERID = '"+ clusterId +"'"]);
        deleteStatements.push(["UNRELATEDDUPLICATES", "UNRELATEDDUPLICATES.CLUSTERID = '"+ clusterId +"'"]);
        
        db.deletes(deleteStatements);
    }
    else
    {
        db.deleteData("DUPLICATECLUSTERS", "DUPLICATECLUSTERS.DUPLICATEID = '"+ pDuplicateId +"'");
        //Delete all records where this duplicateId is mentioned
        DuplicateScannerUtils.DeleteAllUnrelatedDuplicateRelations(pDuplicateId);
    }
}

/*
 * Deletes all Clusters for the given target Entity.
 * No records markes as unrelated duplicate are being deleted.
 * 
 * @param {String} pTargetEntity Entity which has been configured
 * @return Count of deleted rows
 */
DuplicateScannerUtils.DeleteDuplicateClustersByTargetEntity = function(pTargetEntity)
{
    return db.deleteData("DUPLICATECLUSTERS", "DUPLICATECLUSTERS.TARGET_ENTITY = '"+ pTargetEntity +"'")
}

/*
 * Updates the duplicate relations markes as unrelated for the given target entity. 
 * All ClusterIds get updated with the new values if the same combination of duplicateIds
 * still exists in the DUPLICATECLUSTERS table.
 * 
 * Afterwards, all records which contain a nonexistend clusterId are being deleted
 * 
 * @param {String} pTargetEntity Name of Entity whose duplicates should be updated
 */
DuplicateScannerUtils.RefreshUnrelatedDuplicateRelations = function(pTargetEntity)
{
    /* 
     * Update all records with the current valid clusterId where the same duplicateId combination exists
     */
    let INDEX_NEW_CLUSTERID = 0;
    let INDEX_OLD_CLUSTERID = 1;
    
    let query = "select dc1.CLUSTERID, ud.CLUSTERID from UNRELATEDDUPLICATES ud"
                + " join DUPLICATECLUSTERS dc1 on dc1.DUPLICATEID = ud.SOURCEDUPLICATEID"
                + " join DUPLICATECLUSTERS dc2 on dc2.DUPLICATEID = ud.UNRELATEDDUPLICATEID"
                + " where dc1.TARGET_ENTITY = '" + pTargetEntity + "'";
            
    let newIdOldIdRay = db.table(query);
    let updateStatements = [];

    //Build update statements to set new clusterIds
    for (let i = 0; i < newIdOldIdRay.length; i++) 
    {
        let newClusterId = newIdOldIdRay[i][INDEX_NEW_CLUSTERID];
        let oldClusterId = newIdOldIdRay[i][INDEX_OLD_CLUSTERID];
        let updateColumns = ["CLUSTERID"];
        let condition = "UNRELATEDDUPLICATES.CLUSTERID = '" + oldClusterId + "'";
        
        let updateStatement = ["UNRELATEDDUPLICATES", updateColumns, null, [newClusterId], condition];
        
        updateStatements.push(updateStatement);
    }
    db.updates(updateStatements);
    
    /* 
     * All unrelated duplicate ids that still exist in a cluster, have been updated with the new cluster id.
     * All records with a nonexistend clusterid can now be deleted because they haven't been detected as a duplicate any more.
     */
    let deleteCondition = "CLUSTERID not in (select dc1.CLUSTERID from DUPLICATECLUSTERS dc1)";
    db.deleteData("UNRELATEDDUPLICATES", deleteCondition);
}

/* 
 * Creates a relation between two duplicates which means they are unrelated.
 * They will not appear in each others duplicate tab any more.
 * To remove this relation use DuplicateScannerUtils.DeleteUnrelatedDuplicateRelation
 * 
 * @param {String} pSourceContactId Id of first duplicate
 * @param {String} pUnrelatedContactId Id of second duplicate
 * @param {String} pClusterId Id of the cluster in which the duplicates are aggregated
 * @returns {String} Number of Records inserted
 */
DuplicateScannerUtils.CreateUnrelatedDuplicateRelation = function(pSourceContactId, pUnrelatedContactId, pClusterId)
{
    let newUid = util.getNewUUID();
    let columns = ["ID", "SOURCEDUPLICATEID", "UNRELATEDDUPLICATEID", "CLUSTERID"];
    let values = [newUid, pSourceContactId, pUnrelatedContactId, pClusterId];

    return db.insertData("UNRELATEDDUPLICATES", columns, null, values);
}

/* 
 * Gets the cluster id in which the given duplicate id exists
 * 
 *  @param {String} pDuplicateId whose cluster id should be searched
 *  @returns {String} Cluster id
 */
DuplicateScannerUtils.GetClusterId = function(pDuplicateId)
{
    let duplicateIdsOfClusterWithoutUnrelated = SqlCondition.begin()
    .and("DUPLICATECLUSTERS.DUPLICATEID = '" + pDuplicateId + "'")
    .buildSql("select CLUSTERID from DUPLICATECLUSTERS");
     
    return db.cell(duplicateIdsOfClusterWithoutUnrelated);
}

/* 
 * Deletes the "unrelated" relation between two duplicates
 * 
 *  @param {String} pSourceDuplicateId Id of the source duplicate
 *  @param {String} pUnrelatedDuplicateId Id of the source duplicate
 *  @returns {String} Number of records deleted
 */
DuplicateScannerUtils.DeleteUnrelatedDuplicateRelation = function(pSourceDuplicateId, pUnrelatedDuplicateId)
{
    var cond = new SqlCondition();
    cond.andPrepare("UNRELATEDDUPLICATES.SOURCEDUPLICATEID", pSourceDuplicateId)
    cond.andPrepare("UNRELATEDDUPLICATES.UNRELATEDDUPLICATEID", pUnrelatedDuplicateId)
    let condition = db.translateCondition(cond.build());
    
    return db.deleteData("UNRELATEDDUPLICATES", condition);
}

/*
 * Deletes all relations to a duplicate id wether the id is the source or the unrelated duplicate in the relation
 *
 *  @param {String} pDuplicateId Duplicate id whose "unrelated" relations are to delete
 *  @returns {String} Number of records deleted
 */
DuplicateScannerUtils.DeleteAllUnrelatedDuplicateRelations = function(pDuplicateId)
{
    var cond = new SqlCondition();
    cond.orPrepare("UNRELATEDDUPLICATES.SOURCEDUPLICATEID", pDuplicateId)
    cond.orPrepare("UNRELATEDDUPLICATES.UNRELATEDDUPLICATEID", pDuplicateId)
    let condition = db.translateCondition(cond.build());
    
    return db.deleteData("UNRELATEDDUPLICATES", condition);
}

/*
 * Loads all other duplicates from the cluster in which the parameterized duplicate is located
 *
 *  @param {String} pDuplicateId
 *  @returns {String[]} Array of duplicate ids
 */
DuplicateScannerUtils.GetCachedDuplicatesForContactId = function(pDuplicateId)
{
    let querySelectIgnoredDuplicates = '';
    let querySelectDuplicateContactIds = "select DUPLICATEID from DUPLICATECLUSTERS"
                                        + " where CLUSTERID = (select CLUSTERID from DUPLICATECLUSTERS"
                                        + " where DUPLICATEID = '"+ pDuplicateId +"')"
                                        + " and DUPLICATEID != '"+ pDuplicateId +"'"
                                        + " and DUPLICATEID not in (select UNRELATEDDUPLICATEID from UNRELATEDDUPLICATES where SOURCEDUPLICATEID = '"+ pDuplicateId +"')"
                                        + " and DUPLICATEID not in (select SOURCEDUPLICATEID from UNRELATEDDUPLICATES where UNRELATEDDUPLICATEID = '"+ pDuplicateId +"')";

    return db.array(db.COLUMN, querySelectDuplicateContactIds);
}

/*
 * Returns all duplicate ids which haven't been marked as unrelated for the given cluster id.
 * 
 * @param {String} pClusterId The clusters id
 * @return {String[]} Array of duplicate ids excluding those marked as unrelated
 */
DuplicateScannerUtils.GetCachedDuplicatesForClusterId = function(pClusterId)
{
    let clusterIdRay = [];
    clusterIdRay.push(pClusterId);
    
    let duplicateIdsOfClusterWithoutUnrelated = SqlCondition.begin()
    .and("DUPLICATEID not in (select UNRELATEDDUPLICATES.UNRELATEDDUPLICATEID from UNRELATEDDUPLICATES)")
    .and("DUPLICATECLUSTERS.CLUSTERID = '" + clusterIdRay + "'")
    .buildSql("select DUPLICATEID from DUPLICATECLUSTERS");
    
    return db.array(db.COLUMN, duplicateIdsOfClusterWithoutUnrelated);
}

/*
 * Recreates the cached duplicate clusters based on the configured filters. <br />
 * The old clusters have to be deleted manually beforehand using "DeleteDuplicateClustersByTargetEntity".<br />
 * If there have already been ignored relations between duplicate records, it's advised to call "RefreshUnrelatedDuplicateRelations" after the recreation of the duplicates cache.<br />
 * Please check the documentation of the params on how to get the infos required.<br />
 * <br />
 * If the usage of an external webservice has been activated, the results will be narrowed down by the prefilter and<br />
 * will then be given to the pFormatValuesConsumeWebserviceCallback via parameter.<br />
 * To access the values it is advised to run thru the parameter like an array and access its value by key which is the index field name. The entity<br />
 * field names can be converted using DuplicateScannerUtils.TranslateEntityToIndexFields. For further infos see the example section below.<br />
 * <br />
 * Attention!<br />
 * If it is configured to use the external webservice callback the values have to be in the same format as they are in the parameter of the callback.<br />
 * <br />
 * @param {String} pFilterName Name of the filter to use
 * @param {String} pTargetEntity The target entity which has been assigned to the filters configuration
 * @param {String} pQueryTargetRecords Query which holds the values that are being used as configured in the filter.
 * @param {String} pDuplicateFieldsConfig The index field config. Use "DuplicateScannerUtils.LoadIndexFieldsConfiguration"
 * @param {String} pResultFields The result field config. Use "DuplicateScannerUtils.LoadResultFields"
 * @param {String} pFormatValuesConsumeWebserviceCallback Null if no external service is used otherwise a function with one parameter.
 * @param {String[]} pDuplicateFieldsConfig The configuration of the fields and their usage. @see DuplicateScannerUtils.LoadDuplicateIndexFieldsConfiguration
 * Important: The first element has to be the id field!
 * @return {Int} Count of duplicate clusters created
 * 
 * @example
 * var filterName = "PersonDuplicates";
 * var targetEntity = "Person_entity";
 *
 * let duplicateFieldsConfig = DuplicateScannerUtils.LoadIndexFieldsConfiguration(filterName, targetEntity);
 * let resultFields = DuplicateScannerUtils.LoadResultFields(filterName, targetEntity);
 * let querySelectFields = DuplicateScannerUtils.BuildSqlSelectFieldsFromFieldConfig(duplicateFieldsConfig);
 * let queryPersonFieldData = "select " + querySelectFields + " from CONTACT"
 *                             + " join PERSON on PERSONID = PERSON_ID"
 *                             + " left join ADDRESS on ADDRESS.CONTACT_ID = CONTACT.CONTACTID";
 *
 * let formatToJsonAndCallWsCallback = function(pPossibleDuplicatesRay)
 * {
 *      let indexResultFields = DuplicateScannerUtils.TranslateEntityToIndexFields(targetEntity, resultFields)
 *
 *      //Run thru every duplicate result an read out the resultfields
 *      for (let i = 0; i < pPossibleDuplicatesRay.length; i++)
 *      {
 *          for (let b = 0; b < resultFields.length; b++)
 *          {
 *              let entityFieldName = resultFields[b];
 *              let indexFieldName = indexResultFields[entityFieldName];
 *              //logging.log("Entity Field -> "+ pPossibleDuplicatesRay[i][indexFieldName]);
 *              //format values
 *          }
 *      }
 *      //call webservice
 *      //reformat results to same structure as before
 *      return pPossibleDuplicatesRay;
 * };
 *
 * DuplicateScannerUtils.DeleteDuplicateClustersByTargetEntity(targetEntity);
 * DuplicateScannerUtils.RebuildDuplicatesCache(filterName, targetEntity, queryPersonFieldData,
 * duplicateFieldsConfig, resultFields, formatToJsonAndCallWsCallback);
 *
 * DuplicateScannerUtils.RefreshUnrelatedDuplicateRelations(targetEntity);
 */
DuplicateScannerUtils.RebuildDuplicatesCache = function(pFilterName, pTargetEntity,
pRecordsBlockSize, pDuplicateFieldsConfig, pResultFields, pFormatValuesConsumeWebserviceCallback)
{   
    let useExternalWebservice = _DuplicateScannerUtils._isUseExternalWebservice(pFilterName, pTargetEntity);
    let alreadyIdentifiedIds = [];
    
    let entityFieldsToLoad = [];
    for (field in duplicateFieldsConfig) 
    {
        entityFieldsToLoad.push(duplicateFieldsConfig[field][0]);
    }

    logging.log("entityFieldsToLoad -> " + entityFieldsToLoad);

    
    let targetRecords = DuplicateScannerUtils.LoadEntityRecords(pTargetEntity, entityFieldsToLoad, 0, pRecordsBlockSize);
    logging.log("Initialer Block geladen targetRecords-> " + JSON.stringify(targetRecords));

    let currentRecordIndex = pRecordsBlockSize;
    while(targetRecords.length > 0)
    {
        foundDuplicateIds = DuplicateScannerUtils.ScanRecords(pFilterName, pTargetEntity, targetRecords,
                                pDuplicateFieldsConfig, pResultFields, useExternalWebservice, 
                                pFormatValuesConsumeWebserviceCallback, alreadyIdentifiedIds);
        logging.log("gefundene ids zum ignorieren foundDuplicateIds -> " + foundDuplicateIds);
        alreadyIdentifiedIds = alreadyIdentifiedIds.concat(foundDuplicateIds);
        logging.log("Gesamte ignorierListe -> " + alreadyIdentifiedIds);
        if(targetRecords.length < pRecordsBlockSize)
        {
            logging.log("weniger records geladen als möglich => Ende der verfügbaren Records -> Abbrechen");
            break;
        }

        logging.log("Nächster Block wird geladen mit startRow -> " + currentRecordIndex);
        
        targetRecords = DuplicateScannerUtils.LoadEntityRecords(pTargetEntity, entityFieldsToLoad,
        currentRecordIndex, pRecordsBlockSize);
        
        currentRecordIndex += pRecordsBlockSize;
        logging.log("Nächster Block geladen mit targetRecords -> " + JSON.stringify(targetRecords));
    }
}

DuplicateScannerUtils.ScanRecords = function(pFilterName, pTargetEntity, pTargetRecordsData,
pDuplicateFieldsConfig, pResultFields, pUseExternalWebservice, pFormatValuesConsumeWebserviceCallback, pAlreadyIdentifiedIds)
{
    logging.log("in ScanRecords -> ");
    let foundDuplicateIds = [];
    
    //If the contact id loading query has no results, stop. 
    //No ids should be deleted if an error has been made in this query.
    if(pTargetRecordsData.length <= 0)
        return;

     //First it gets checked if the current id has already been identified. If that's the case it'll continue with the next.
     //Otherwise an object gets build in the form of ["FilterFieldName" = "FilterFieldValueFromQuery"] with which a scan for possible duplicates get's started
    logging.log("configured pResultFields -> " + pResultFields);
    var duplicatesToInsertQueries = [];
    for (b = 0; b < pTargetRecordsData.length; b++) 
    {
        logging.log("Nächster Datensatz in RebuildDuplicatesCache -> " + b);
//        logging.log("Neuer Record -> " + pTargetRecordsData[b]);

        logging.log("pTargetRecordsData[b] -> " + JSON.stringify(pTargetRecordsData[b]));
        let entityFieldValuesRay = DuplicateScannerUtils.BuildEntityFieldNameValueRays(pDuplicateFieldsConfig, pTargetRecordsData[b]);
        logging.log("Werte des Datensatzes entityFieldValuesRay -> " + JSON.stringify(entityFieldValuesRay));
        
        //The first field in this Array must always be the configured id field. This is ensured using onValidation-logic 
        let idField = entityFieldValuesRay[0][0];
        let idValue = entityFieldValuesRay[0][1];
//        logging.log("idField -> " + idField);
//        logging.log("idValue -> " + idValue);
            logging.log("pTargetRecordsData[b][idField] -> " + pTargetRecordsData[b][idField]);
        //If the current Id has already been identified, continue
        if(pAlreadyIdentifiedIds.indexOf(pTargetRecordsData[b][idField]) > -1)
            {
                logging.log("Id schon behandelt, continue; -> ");
                continue;
            }
        
        let foundDuplicates = _DuplicateScannerUtils._scanForDuplicates(pFilterName, pTargetEntity,
                                entityFieldValuesRay, pResultFields, idField, idValue, pFormatValuesConsumeWebserviceCallback, pUseExternalWebservice)
        
        if(foundDuplicates == null || foundDuplicates.length == 0)
        {
            logging.log("Keine Datensätze gefunden  continue;-> ");
            continue;
        }
        logging.log("foundDuplicates.length nach _scanForDuplicates -> " + foundDuplicates.length);
 
        //Insert all found duplicate ids into an cache array because those ids don't have to be checked again lateron.
        
        for (let i = 0; i < foundDuplicates.length; i++)
        {
            let localId = foundDuplicates[i][indexsearch.FIELD_ID];
            logging.log("foundDuplicates[i] -> " + foundDuplicates[i]);
            foundDuplicateIds.push(localId);
        }
        
        pAlreadyIdentifiedIds = pAlreadyIdentifiedIds.concat(foundDuplicateIds);
        
        //The duplicates list contains only the found duplicates to the original id, therefore it get's added manually
        foundDuplicateIds.push(pTargetRecordsData[b][idField]);
        logging.log("foundDuplicates -> " + JSON.stringify(foundDuplicates));
        logging.log("foundDuplicateIds -> " + JSON.stringify(foundDuplicateIds));
        
        let insertQueriesRay = _DuplicateScannerUtils._createInsertDuplicatesClusterQuery(foundDuplicateIds, pTargetEntity)
        duplicatesToInsertQueries = duplicatesToInsertQueries.concat(insertQueriesRay);
    }
    logging.log("duplicatesToInsertQueries -> " + JSON.stringify(duplicatesToInsertQueries));
    db.inserts(duplicatesToInsertQueries);
    return foundDuplicateIds;
}

DuplicateScannerUtils.LoadEntityRecords = function(pTargetEntity, pEntityFields, pStartRow, pCountRecordsToLoad)
{
    logging.log("pTargetEntity -> " + pTargetEntity);
    logging.log("pEntityFields -> " + pEntityFields);
    logging.log("pCountRecordsToLoad -> " + pCountRecordsToLoad);
    logging.log("pStartRow -> " + pStartRow);
    let getRowsConfig = entities.createConfigForLoadingRows()
                                .entity(pTargetEntity)
                                .fields(pEntityFields)
                                .count(pCountRecordsToLoad)
                                .startrow(pStartRow);
    return entities.getRows(getRowsConfig)          
}
/* 
 * Loads the configured resultfields as array
 * 
 *  @param {String} pFilterName Name of the filter
 *  @param {String} pTargetEntity Entity which has been configured
 *  @returns {String[]} Resultfields as array 
 */
DuplicateScannerUtils.LoadResultFields = function(pFilterName, pTargetEntity)
{
    let duplicateResultFields = SqlCondition.begin()
        .andPrepare("DUPLICATESCANNER.FILTER_NAME", pFilterName)
        .andPrepare("DUPLICATESCANNER.ENTITY_TO_SCAN_NAME", pTargetEntity)
        .buildSql("select dsrfc.ENTITY_FIELD_NAME from DUPLICATESCANNERRESULTFIELDCONFIG dsrfc join DUPLICATESCANNER on DUPLICATESCANNER.ID = dsrfc.DUPLICATESCANNER_ID"
        , "1=2");
    
    return db.array(db.COLUMN, duplicateResultFields);
}

/* 
 * Loads the fields and their configuration. 
 * One field record is in the following format: 
 * [ENTITY_FELD, IS_ID, USE_FOR_SEARCH]
 * Example:
 * ["FIRSTNAME", false, true]
 * 
 *  @param {String} pFilterName Name of the filter
 *  @param {String} pTargetEntity Entity which has been configured
 *  @returns {String[[]]} An Array of Arrays in the format described above 
 */
DuplicateScannerUtils.LoadIndexFieldsConfiguration = function(pFilterName, pTargetEntity)
{
    let duplicateIndexFieldConfigurations = SqlCondition.begin()
        .andPrepare("DUPLICATESCANNER.FILTER_NAME", pFilterName)
        .andPrepare("DUPLICATESCANNER.ENTITY_TO_SCAN_NAME", pTargetEntity)
        .buildSql("select dsic.ENTITY_FIELD_NAME, dsic.IS_ID_FIELD, dsic.USE_FOR_INDEX_DUPLICATE_SEARCH from DUPLICATESCANNERINDEXCONFIG dsic join DUPLICATESCANNER on DUPLICATESCANNER.ID = dsic.DUPLICATESCANNER_ID"
        , "1=2", "order by dsic.IS_ID_FIELD desc");
    
    return db.table(duplicateIndexFieldConfigurations);
}

/* 
 * Scans for duplicates based on the configured prefilters and the pFilterFieldValueRays.
 * First the prefilters get applied one after another until the count of the returned data is in the allowed threshold.
 * Then, the duplicate search using the index starts. All fields which have been configured will be used here.
 *
 * If the usage of an external webservice has been activated, the result will then be given to the pFormatValuesConsumeWebserviceCallback via parameter.
 * To access the values it is advised to run thru the parameter like an array and access its value by key which is the index field name. The entity
 * field names can be converted using DuplicateScannerUtils.TranslateEntityToIndexFields
 *
 * Attention!
 * If it's a single ScanForDuplicates call it doesn't matter what the callback returns because after the callback, no more modifications follow before
 * returning the data.
 * If it's inside the RebuildCache the values have to be in the same format as the parameter
 *
 * @param {String} pFilterName Name of the filter
 * @param {String} pTargetEntity Respective target entity
 * @param {String[[]]} pFilterFieldValueRays Array of Arrays containing the name of a used field and its value.
 * Use "DuplicateScannerUtils.BuildEntityFieldNameValueRays". The fieldnames and values have to be in the same order
 * @param {String[]} pResultFields The result field config. Use "DuplicateScannerUtils.LoadResultFields"
 * @param {String} pRecordIdFieldToIgnore Name of the id field e.g. the contact id in case of a Person duplicate
 * @param {String} pRecordIdValueToIgnore Value to the id field
 * @param {function} pFormatValuesConsumeWebserviceCallback Null if no external service is used otherwise a function with one parameter.
 * If the function is called is based on the configuration of the current scanner
 * @returns {[["key", "value"]]} Array of Key-Value-Pairs based on the configured resultfields, if an external webservices was used
 * the structure is defined by the parameterized function "pFormatValuesConsumeWebserviceCallback"
 *
 * @example
 * var filterName = "PersonDuplicates";
 * var targetEntity = "Person_entity";
 * let duplicateFieldsConfig = DuplicateScannerUtils.LoadIndexFieldsConfiguration(filterName, targetEntity);
 * let resultFields = DuplicateScannerUtils.LoadResultFields(filterName, targetEntity);
 * let querySelectFields = DuplicateScannerUtils.BuildSqlSelectFieldsFromFieldConfig(duplicateFieldsConfig);
 *
 * let queryPersonFieldData = "select " + querySelectFields + " from CONTACT"
 *                             + " join PERSON on PERSONID = PERSON_ID"
 *                             + " left join ADDRESS on ADDRESS.CONTACT_ID = CONTACT.CONTACTID"
 *                             + " where Condition for the record to be checked";
 * let targetRecordsData = db.table(queryPersonFieldData);
 *
 * let entityFieldValuesRay = DuplicateScannerUtils.BuildEntityFieldNameValueRays(duplicateFieldsConfig, targetRecordsData[0]);
 * //The first field in this Array must always be the configured id field.
 * let idField = entityFieldValuesRay[0][0];
 * let idValue = entityFieldValuesRay[0][1];
 *
 * let formatToJsonAndCallWsCallback = function(pPossibleDuplicatesRay)
 * {
 *     let indexResultFields = DuplicateScannerUtils.TranslateEntityToIndexFields(targetEntity, resultFields)
 *
 *     //Run thru every duplicate result an read out the resultfields
 *     for (let i = 0; i < pPossibleDuplicatesRay.length; i++)
 *     {
 *         for (let b = 0; b < resultFields.length; b++)
 *         {
 *             let entityFieldName = resultFields[b];
 *             let indexFieldName = indexResultFields[entityFieldName];
 *             //logging.log("Entity Field -> "+ pPossibleDuplicatesRay[i][indexFieldName]);
 *             //format values
 *         }
 *     }
 *     //call webservice
 *     //reformat results to same structure as before
 *     return pPossibleDuplicatesRay;
 * };
 *
 * //The result values can be accessed as seen above in "formatToJsonAndCallWsCallback"
 * DuplicateScannerUtils.ScanForDuplicates(filterName, targetEntity, entityFieldValuesRay, resultFields,
 * idField, idValue, formatToJsonAndCallWsCallback);
 */
DuplicateScannerUtils.ScanForDuplicates = function(pFilterName, pTargetEntity, pFilterFieldValueRays,
pResultFields, pRecordIdFieldToIgnore, pRecordIdValueToIgnore, pFormatValuesConsumeWebserviceCallback)
{
    let useExternalWebservice = _DuplicateScannerUtils._isUseExternalWebservice(pFilterName, pTargetEntity);

    return _DuplicateScannerUtils._scanForDuplicates(pFilterName, pTargetEntity,
                        pFilterFieldValueRays, pResultFields, pRecordIdFieldToIgnore, pRecordIdValueToIgnore,
                         pFormatValuesConsumeWebserviceCallback, useExternalWebservice)
}

/* 
 * Concatenates the fields with a semicolon as separator. This can be used in a sql select.
 * 
 *  @param {String[]} pIndexFieldsConfig Array of Names
 *  @returns {String} String in the style of "Value1, Value2, Value3"
 */
DuplicateScannerUtils.BuildSqlSelectFieldsFromFieldConfig = function(pIndexFieldsConfig)
{
    let querySelectFields = "";
    for (let i = 0; i < pIndexFieldsConfig.length; i++) 
    {
        querySelectFields += pIndexFieldsConfig[i][0];

        if(i < pIndexFieldsConfig.length - 1)
            querySelectFields += ", ";
    }
    return querySelectFields;
}


/* 
 * Executes a indexsearch.lookupIndexField for eacht entity field in the parameterized array
 * and returns it as Map.
 *
 *  @param {String} pEntityName ...Name of the entity
 *  @param {String[]} pEntityFields Array of the entities Fields to translate to index fields
 *  @returns Map-like object where (key = entity field) and (value = index field)
 *
 *  @example
 *  let entityResultFields = ["LASTNAME"];
 *  let entityIndexFields = DuplicateScannerUtils.TranslateEntityToIndexFields("Person_entity", entityResultFields);
 *  
 *  logging.log(entityIndexFields["LASTNAME"]);//=> "LASTNAME_value"
 */
DuplicateScannerUtils.TranslateEntityToIndexFields = function(pEntityName, pEntityFields)
{
    let entityIndexFields = {};
    for (let i = 0; i < pEntityFields.length; i++) 
    {
        let entityFieldName = pEntityFields[i];

        //todo remove if api has been fixed
        if(entityFieldName.startsWith(pEntityName))
            entityFieldName = entityFieldName.replace(pEntityName + ".", "");

         let indexField = indexsearch.lookupIndexField(pEntityName, entityFieldName);
         entityIndexFields[pEntityFields[i]] = indexField;
    }
    return entityIndexFields;
}

/*
 *
 *
 *  @param {String}
 *  @param {String[]}
 *  @returns {String}
 */
DuplicateScannerUtils.MergePerson = function(pSourceContactId, pTargetContactId)
{
    let updateStatementsCurrentAlias = [];
    let updateStatementsSystemAlias = [];
    let deleteStatements = [];
    
    var sourcePersonId = db.cell("select PERSON_ID from CONTACT where CONTACTID = '" + pSourceContactId + "'");
    var tableInfosCurrentAlias = _DuplicateScannerUtils._getMergeUpdateTableInfosCurrentAlias();
    var tableInfosSystemAlias = _DuplicateScannerUtils._getMergeUpdateTableInfosSystemAlias();
    
    updateStatementsCurrentAlias = updateStatementsCurrentAlias.concat(_DuplicateScannerUtils._buildUpdateContactIdStatements(tableInfosCurrentAlias, pSourceContactId, pTargetContactId));
    updateStatementsCurrentAlias = updateStatementsCurrentAlias.concat(_DuplicateScannerUtils._buildUpdateAttachParticipantsToNewContactQuery("CAMPAIGNPARTICIPANT", "CONTACT_ID", "CAMPAIGN_ID", pSourceContactId, pTargetContactId));
    
    updateStatementsSystemAlias = updateStatementsSystemAlias.concat(_DuplicateScannerUtils._buildUpdateContactIdStatements(tableInfosSystemAlias, pSourceContactId, pTargetContactId));
    
    deleteStatements = deleteStatements.concat(_DuplicateScannerUtils._buildDeleteRemoveObsoleteParticipantsRecordsQuery("CAMPAIGNPARTICIPANT", "CONTACT_ID", "CAMPAIGN_ID", pSourceContactId, pTargetContactId));
    deleteStatements = deleteStatements.concat(_DuplicateScannerUtils._buildDeletePersonAndContactQuery(sourcePersonId, pSourceContactId));
    deleteStatements = deleteStatements.concat(_DuplicateScannerUtils._buildDeleteCachedUnrelatedDuplicateQuery(pSourceContactId));

    logging.log("updateStatementsCurrentAlias -> " + JSON.stringify(updateStatementsCurrentAlias));
    logging.log("deleteStatements -> " + JSON.stringify(deleteStatements));
    
    let affectedRowsCurrentAlias = db.updates(updateStatementsCurrentAlias);
    let affectedRowsSystemAlias = db.updates(updateStatementsSystemAlias, "_____SYSTEMALIAS");
    let deletedRows = db.deletes(deleteStatements)
    
    logging.log("affectedRowsCurrentAlias -> " + affectedRowsCurrentAlias);
    logging.log("deletedRows -> " + deletedRows);
    
    DuplicateScannerUtils.DeleteCachedDuplicate(pSourceContactId);
    
    return (affectedRowsCurrentAlias > 0 && deletedRows >= 2);
}

DuplicateScannerUtils.MergeOrganisation = function(pSourceContactId, pTargetContactId)
{
    let updateStatements = [];
    let deleteStatements = [];
    
    let querySourceOrganisationId = SqlCondition.begin()
    .and("CONTACTID = '" + pSourceContactId + "'")
    .buildSql("select ORGANISATION_ID from CONTACT");
    var sourceOrganisationId = db.cell(querySourceOrganisationId);

    var tableInfosCurrentAlias = _DuplicateScannerUtils._getMergeUpdateTableInfosCurrentAlias();
    var tableInfosSystemAlias = _DuplicateScannerUtils._getMergeUpdateTableInfosSystemAlias();
    
    updateStatementsCurrentAlias = updateStatementsCurrentAlias.concat(_DuplicateScannerUtils._buildUpdateContactIdStatements(tableInfosCurrentAlias, pSourceContactId, pTargetContactId));
    updateStatementsCurrentAlias = updateStatementsCurrentAlias.concat(_DuplicateScannerUtils._buildUpdateAttachParticipantsToNewContactQuery("CAMPAIGNPARTICIPANT", "CONTACT_ID", "CAMPAIGN_ID", pSourceContactId, pTargetContactId));
    
    updateStatementsSystemAlias = updateStatementsSystemAlias.concat(_DuplicateScannerUtils._buildUpdateContactIdStatements(tableInfosSystemAlias, pSourceContactId, pTargetContactId));
    
    deleteStatements = deleteStatements.concat(_DuplicateScannerUtils._buildDeleteRemoveObsoleteParticipantsRecordsQuery("CAMPAIGNPARTICIPANT", "CONTACT_ID", "CAMPAIGN_ID", pSourceContactId, pTargetContactId));
    deleteStatements = deleteStatements.concat(_DuplicateScannerUtils._buildDeleteOrganisationAndContactQuery(sourceOrganisationId, pSourceContactId));
    deleteStatements = deleteStatements.concat(_DuplicateScannerUtils._buildDeleteCachedUnrelatedDuplicateQuery(pSourceContactId));

    logging.log("updateStatementsCurrentAlias -> " + JSON.stringify(updateStatementsCurrentAlias));
    logging.log("deleteStatements -> " + JSON.stringify(deleteStatements));
    
    let affectedRowsCurrentAlias = db.updates(updateStatementsCurrentAlias);
    let affectedRowsSystemAlias = db.updates(updateStatementsSystemAlias, "_____SYSTEMALIAS");
    let deletedRows = db.deletes(deleteStatements)
    
    logging.log("affectedRowsCurrentAlias -> " + affectedRowsCurrentAlias);
    logging.log("deletedRows -> " + deletedRows);
    
    DuplicateScannerUtils.DeleteCachedDuplicate(pSourceContactId);
    
    return (affectedRowsCurrentAlias > 0 && deletedRows >= 2);
}

/*
 * Creates an array of arrays containing the entity field name paired with it's value.
 *
 * @param {[]} pDuplicateFieldsConfig An Array with the configured fields in the form of [ENTITY_FIELD, IS_ID, USE_FOR_SEARCH]. @see LoadDuplicateIndexFieldsConfiguration()
 * @param {[]} pTargetRecordData One record containing the values for the configured fields. Has to be in the same order as the fields in the first parameter
 * @return {[[]]} An array of arrays containing the entity field name an its value. [["CONTACTID", "d786045c-8b21-4f22-b6d9-72be9f61c04d"]]
 * @example
 * pDuplicateFieldsConfig
 * ["CONTACTID", true, false]
 * ["FIRSTNAME", false, true]
 *
 * pTargetRecordData
 * ["d786045c-8b21-4f22-b6d9-72be9f61c04d", "PETER"]
 *
 * => [["CONTACTID", "d786045c-8b21-4f22-b6d9-72be9f61c04d"], ["FIRSTNAME", "PETER"]]
 */
DuplicateScannerUtils.BuildEntityFieldNameValueRays = function(pDuplicateFieldsConfig, pTargetRecordData)
{
    let INDEX_CONFIG_ENTITY_FIELD = 0;
    let INDEX_CONFIG_USE_FOR_SEARCH = 2;
    let entityFieldValuesRay = [];
    /*
    * Based on the parameterized filter field names and the values loaded via the query,
    * an array which contains records in the style of ["FilterFieldName", "FilterFieldValueFromQuery"] gets created.
    * This is mandatory to run the scan for this record.
    */
    for (a = 0; a < pDuplicateFieldsConfig.length; a++)
    {
//        logging.log("pDuplicateFieldsConfig[a][1] -> " + pDuplicateFieldsConfig[a][INDEX_CONFIG_ENTITY_FIELD]);
//        logging.log(" pTargetRecordData[a] -> " + pTargetRecordData[a]);
        let entityField = pDuplicateFieldsConfig[a][INDEX_CONFIG_ENTITY_FIELD];
        entityFieldValuesRay.push([entityField, pTargetRecordData[entityField], pDuplicateFieldsConfig[a][INDEX_CONFIG_USE_FOR_SEARCH]])
    }
    return entityFieldValuesRay;
}

function _DuplicateScannerUtils() {}

var INDEX_FILTER_CONDITION = 0;
var INDEX_COUNT_CHARS_TO_USE = 1;
var INDEX_MAX_RESULTS_THRESHOLD = 2;

var INDEX_TABLE_NAME = 0;
var INDEX_COLUMN_NAME = 1;
var INDEX_CONDITION = 2;

/*
 * @see DuplicateScannerUtils.ScanForDuplicates for the documentation
 */
_DuplicateScannerUtils._scanForDuplicates = function(pFilterName, pTargetEntity, pFilterFieldValueRays,
pResultFields, pRecordIdFieldToIgnore, pRecordIdValueToIgnore, pFormatValuesConsumeWebserviceCallback, pUseExternalWebservice)
{
    let possibleDuplicates = [];
    let ignoredRecordFilter = _DuplicateScannerUtils._getIgnoreRecordFilter(pRecordIdFieldToIgnore, pRecordIdValueToIgnore, pTargetEntity);
    let configuredFilters = _DuplicateScannerUtils._loadFilters(pFilterName, pTargetEntity);

    //To ensure the record which the current search is based on isnt found as result, the other configured filters get appended to
    //the filter of said records to ignore
    configuredFilters = [ignoredRecordFilter].concat(configuredFilters);

    logging.log("Found filters -> " + configuredFilters);

    let preFilter = _DuplicateScannerUtils._applyPreFilter(pTargetEntity, configuredFilters, pFilterFieldValueRays);

    logging.log("preFilter welcher Elemente im erlaubten bereich ausgibt -> " + preFilter);

    if(preFilter == null)
        return null;

    possibleDuplicates = _DuplicateScannerUtils._callIndexSearch(pTargetEntity, preFilter, pFilterFieldValueRays, pResultFields, 100);
    
    if(possibleDuplicates == null)
        return null;
    
    possibleDuplicates = possibleDuplicates[indexsearch.HITS];

    if(pUseExternalWebservice && possibleDuplicates.length > 0 && pFormatValuesConsumeWebserviceCallback != null)
        possibleDuplicates = pFormatValuesConsumeWebserviceCallback.apply(this, [possibleDuplicates]);

//    logging.log("pTargetEntity -> " + pTargetEntity);
//    logging.log("preFilter -> " + preFilter);
//    logging.log("pFilterFieldValueRays -> " + pFilterFieldValueRays);
//    logging.log("pRecordIdFieldToIgnore -> " + pRecordIdFieldToIgnore);
//    logging.log("possibleDuplicates -> " + possibleDuplicates);

    return possibleDuplicates;
}

/*
 * Returns a bool which say wether or not an external service should be used
 *
 * @param {String} pFilterName Name of the filter
 * @param {String} pTargetEntity Entity which has been configured
 *  @returns {Bool} True = use, False = no use
 */
_DuplicateScannerUtils._isUseExternalWebservice = function(pFilterName, pTargetEntity)
{
    let scannerUseExternalWebserviceQuery = SqlCondition.begin()
        .andPrepare("DUPLICATESCANNER.FILTER_NAME", pFilterName)
        .andPrepare("DUPLICATESCANNER.ENTITY_TO_SCAN_NAME", pTargetEntity)
        .buildSql("select EXTERNAL_SERVICE_USAGE_ALLOWED from DUPLICATESCANNER"
        , "1=2");
    
    let isUseWebservice = db.cell(scannerUseExternalWebserviceQuery);
    return (isUseWebservice == 0) ? false : true;
}

/*
 * Applies... the configured prefilters. The pre filter is used to narrow the records to be searched by the duplicate scan service
 * The filters get applied sequentially one after another.
 * If the number of results equal or lower as the configured threshold and greater than zero, the current filter combination gets returned.
 * If no more filters are available and the number of results still exceed the threshold, null gets returned.
 * If the number of results reach zero  while aplying filters, null gets returned
 * The reason is, that if a huge count of records were to be used, the time to search for duplicates would be substantially longer.
 * If the prefilters to not have the desired effect it is advised to modify the configured filter conditions
 *
 *  @param {String} pTargetEntity Entity which has been configured
 *  @param {String[[]]} pFilterCountCharactersToUseRay Array of Arrays containing the configuration of the filters.
 * The structure is as follows: [INDEX_FILTER_CONDITION, INDEX_COUNT_CHARS_TO_USE, INDEX_MAX_RESULTS_THRESHOLD]
 *  @param {String[[]]} pFilterFieldValueRays Array of Arrays containing the name of a used field and its value.
 * Use "DuplicateScannerUtils.BuildEntityFieldNameValueRays". The fieldnames and values have to be in the same order
 *  @returns {String} Null if the records count wasnt inside the threshold, the used combined filter to achieve a successfull prefiltering
 */
_DuplicateScannerUtils._applyPreFilter = function(pTargetEntity, pFilterCountCharactersToUseRay, pFilterFieldValueRays)
{
    var combinedFilter = {};

    for (i = 0; i < pFilterCountCharactersToUseRay.length; i++) 
    {
        let filter = pFilterCountCharactersToUseRay[i][INDEX_FILTER_CONDITION];
        let countCharsOfValueToUse = pFilterCountCharactersToUseRay[i][INDEX_COUNT_CHARS_TO_USE];
        let maxResultsThreshold = pFilterCountCharactersToUseRay[i][INDEX_MAX_RESULTS_THRESHOLD];
        
        if(filter == null || filter == "")
            continue;

        filter = JSON.parse(filter);
        
        let filterValuesObject = {};
        for (a = 0; a < pFilterFieldValueRays.length; a++) 
        {
            filterValuesObject[pFilterFieldValueRays[a][0]] = pFilterFieldValueRays[a][1];
        }

        logging.log("filterValuesObject zum füllen des jsons -> " + JSON.stringify(filterValuesObject));
        /*
         * Insert the values into the current filter. Has to be here so that only the new filter
         * and therefore the combinedFilter incrementally gets filled and not always everything multiple times.
         */
        logging.log("1 ###### filter zum befüllen mit werten-> " + JSON.stringify(filter));
        filter.filter.childs = _DuplicateScannerUtils._insertValuesInFilterTemplate(filter.filter.childs, filterValuesObject, countCharsOfValueToUse);
        //logging.log("2 ###### gefüllter filter prefilter index-> " + JSON.stringify(filter));

        if(i == 0)
            combinedFilter = filter.filter;
        else
        {
            //logging.log("3 ###### aktueller combinedFilter -> " + JSON.stringify(combinedFilter));
            //logging.log("4 ###### gefüllter filter -> " + JSON.stringify(filter));
            //Extend the current combined filter with the next filter condition to further refine the results
            //It seems to always contain one child element at the root
            //combinedFilter.childs.push(filter.filter.childs);
            let newCombinedFilterChilds = combinedFilter.childs.concat(filter.filter.childs);
            combinedFilter.childs = newCombinedFilterChilds;
        }
        
        //logging.log("5 ###### combinedFilter + gefüllter filter vor indexsuche-> " + JSON.stringify(combinedFilter));
        
        filter.filter = combinedFilter;
        logging.log("6 ###### completeFilter -> " + JSON.stringify(filter));
        
        //Workaround to load the smallest possible resultset because only the TOTALHITS are relevant at this time
        //Only load "indexsearch.FIELD_ID" and a resultSet size of 1
        let searchResult = _DuplicateScannerUtils._callIndexSearch(pTargetEntity, JSON.stringify(filter), [],
        [], 1);//todo use again after this has been fixed!! insert the local id after fix
        logging.log("searchResults hits length -> " + searchResult[indexsearch.HITS].length);

//        if(searchResult[indexsearch.TOTALHITS] < 80)//todo entfernen?!
//        {
//            for (let i = 0; i < searchResult[indexsearch.HITS].length; i++) 
//            {
//                logging.log("Treffer Nr -> " + i);
//                //searchResults hits 0 -> {#ADITO_SEARCH_ID=1868bd3a-05af-4b7f-a633-e3aec50ac45c, _index_group_=Person, #ADITO_SEARCH_TYPE=Person, firstname_value=Peter, _local_id_=1868bd3a-05af-4b7f-a633-e3aec50ac45c}
//                let localId = searchResult[indexsearch.HITS][i]["_local_id_"];
//                let firstname = searchResult[indexsearch.HITS][i]["firstname_value"];
//                let indexGroup = searchResult[indexsearch.HITS][i]["_index_group_"];
//                logging.log("localId -> " + localId);
//                logging.log("firstname -> " + firstname);
//                logging.log("indexGroup -> " + indexGroup);
//            }
//        }
        
        let totalHits = searchResult[indexsearch.TOTALHITS]

        logging.log("totalHits -> " + totalHits);
        
        if(totalHits > maxResultsThreshold)
        {
            logging.log("zu viele rows gefundenn nächsten Filter anwenden -> totalHits:" + totalHits + " maxResultsThreshold:" + maxResultsThreshold);
            //Found more rows than allowed by the threshold, run next filter to narrow the results
            continue;
        }
        else if(totalHits <= 0)
        {
            return null;
        }
        else
        {
            //we're in the treshold, return the valid filter. The filter gets used lateron.
            return JSON.stringify(filter);
        }
    }
    logging.log("zu viele rows und keine filter mehr -> ");
    return null;
}

/*
 * Runs the indexsearch based on the given parameters.
 * If the "pEntityFieldValueRays" is empty, only the prefilters get applied as pattern.
 * if not, the prefilters will be applies as pattern and the contents of "pEntityFieldValueRays" get applies as filter.
 *
 * @param {String} pTargetEntity Entity which has been configured
 * @param {String} pPreFilterJson The prefilters
 * @param {String[[]]} pEntityFieldValueRays Array of Arrays containing the name of a used field and its value.
 * Use "DuplicateScannerUtils.BuildEntityFieldNameValueRays". The fieldnames and values have to be in the same order. NotNull!->Empty Array 
 * @param {String} pResultFields The result field config. Use "DuplicateScannerUtils.LoadResultFields"
 * @param {String} pResultSetRows todo
 * @returns {[["key", "value"]]} Array of Key-Value-Pairs based on the configured pResultFields
 */
_DuplicateScannerUtils._callIndexSearch = function(pTargetEntity, pPreFilterJson, pEntityFieldValueRays, pResultFields, pResultSetRows)
{
    let parsedFilterAsPatternTerm = indexsearch.buildQueryFromSearchCondition(pPreFilterJson);
    //logging.log("pTargetEntity -> " + pTargetEntity);
    logging.log("pResultFields -> " + pResultFields);
    //logging.log("pResultSetRows -> " + pResultSetRows);
    let indexQuery = indexsearch.createIndexQuery()
                                .setPattern(parsedFilterAsPatternTerm)
                                .setEntities([pTargetEntity])
                                //.addSearchFields("Person_entity.FIRSTNAME", "Person_entity.LASTNAME", "Person_entity.CONTACTID")
                                .setRows(pResultSetRows);

    indexQuery = _DuplicateScannerUtils._setResultFields(indexQuery, pResultFields);
    
    //indexQuery = indexQuery.addResultFields(["Person_entity.FIRSTNAME", "Person_entity.LASTNAME"]);
    //indexQuery = indexQuery.addResultFields(["FIRSTNAME", "LASTNAME"]);
    
    let filterPatternConfig = _DuplicateScannerUtils._buildFilterPatternConfig(pEntityFieldValueRays, pTargetEntity);
    if(filterPatternConfig != null)
    {
        let filterPatternString = indexsearch.buildPatternString(filterPatternConfig);
        indexQuery = indexQuery.addFilter(filterPatternString);
        logging.log("real filter PatternString -> " + filterPatternString);
    }
    logging.log("parsedFilterAsPatternTerm -> " + parsedFilterAsPatternTerm);
    
    if(filterPatternConfig == null && pEntityFieldValueRays.length > 0)
    {
        logging.log("FilterPattern ist null aber es gibt pEntityFieldValueRays -> Die Felder sollten genutzt werden, beinhalten aber keine Werte");
        return null;
    }
    else
    {
        logging.log("Starte Indexsuche -> ");
        return indexsearch.searchIndex(indexQuery);
    }
}

/*
 * Sets each field of the given array as resultfield on the given query.
 * Supports indexsearch internal fields aswell
 * (indexsearch.FIELD_ID, indexsearch.FIELD_TITLE, indexsearch.FIELD_TYPE, indexsearch.FIELD_DESCRIPTION)
 *
 * @param {IndexQuery} pIndexQuery An indexquery created with "indexsearch.createIndexQuery()"
 * @param {String[]} pResultFields The result field config. Use "DuplicateScannerUtils.LoadResultFields"
 * @returns {IndexQuery} IndexQuery with the resultfields set
 */
_DuplicateScannerUtils._setResultFields = function(pIndexQuery, pResultFields)
{
    let resultIndexFields = [];
    let resultFields = [];
    for (let i = 0; i < pResultFields.length; i++) 
    {
        if(pResultFields[i] == indexsearch.FIELD_ID
        || pResultFields[i] == indexsearch.FIELD_TITLE
        || pResultFields[i] == indexsearch.FIELD_TYPE
        || pResultFields[i] == indexsearch.FIELD_DESCRIPTION)
        {
            resultIndexFields.push(pResultFields[i]);
        }
        else
            resultFields.push(pResultFields[i]);
    }
    
//    if(resultIndexFields.length == 0 && resultFields.length == 0)
//        pIndexQuery = pIndexQuery.addResultIndexFields([indexsearch.FIELD_ID]);
//    else 
//    {
        if(resultIndexFields.length > 0)
            pIndexQuery = pIndexQuery.addResultIndexFields(resultIndexFields);
        
        if(resultFields.length > 0)
        {
            pIndexQuery = pIndexQuery.addResultFields(resultFields);
        }
//    }
    
    logging.log("resultFields -> " + resultFields);
    return pIndexQuery;
}

/*
 * Translates the fields and the respective value which are configured to use for duplicates to a pattern config to use with
 * an index search query.
 *
 * @param {String[[]]} pEntityFieldValueRays Array of Arrays containing the name of a used field and its value.
 * @param {String} pTargetEntity Entity which has been configured
 * @returns {PatternConfig} PatternConfig created with "indexsearch.createPatternConfig()"
 */
_DuplicateScannerUtils._buildFilterPatternConfig = function(pEntityFieldValueRays, pTargetEntity)
{
    //The index to get the fields value for USE_FOR_SEARCH
    //Structure of this array is [ENTITY_FIELD, FIELD_VALUE, USE_FOR_SEARCH]
    let INDEX_CONFIG_USE_FOR_SEARCH = 2;
    let filterPatternConfig = null;
    
    logging.log("pEntityFieldValueRays.length -> " + pEntityFieldValueRays.length);
    if(pEntityFieldValueRays.length > 0)
    {
        filterPatternConfig = indexsearch.createPatternConfig();
        for (let i = 0; i < pEntityFieldValueRays.length; i++) 
        {
            let entityFieldValue = pEntityFieldValueRays[i][1];
            let entityFieldName = pEntityFieldValueRays[i][0];
            
            if(pEntityFieldValueRays[i][INDEX_CONFIG_USE_FOR_SEARCH] == 0 || entityFieldValue == "")
                continue;
            
            logging.log("entityFieldValue -> " + entityFieldValue);
            logging.log("entityFieldName -> " + entityFieldName);
                      
            let indexField = indexsearch.lookupIndexField(pTargetEntity, entityFieldName);
            
            logging.log("indexField -> " + indexField);
            var filterTerm = indexsearch.createTerm(entityFieldValue)
                                        .setIndexField(indexField)
                                        .setFuzzySearchFactor(0);
                                        
            filterPatternConfig.and(filterTerm);
        }
    }
    return (filterPatternConfig == null || filterPatternConfig.isEmpty()) ? null : filterPatternConfig;
}

/*
 * Creates the queries to insert new duplicates into a new cluster based on the pDuplicatesRay
 *
 * @param {String[]} pDuplicatesRay Array of duplicate ids
 * @param {String} pTargetEntity Entity which has been configured
 * @returns {String[]} Array of insert queries
 */
_DuplicateScannerUtils._createInsertDuplicatesClusterQuery = function (pDuplicatesRay, pTargetEntity)
{
    let duplicatesToInsertQueries = [];
    let cols = ["ID", "CLUSTERID", "DUPLICATEID", "TARGET_ENTITY"];
    let newClusterUid = util.getNewUUID();
    
    for (i = 0; i < pDuplicatesRay.length; i++) 
    {
        let newId = util.getNewUUID();
        let valuesToInsert = [newId, newClusterUid, pDuplicatesRay[i], pTargetEntity];

        duplicatesToInsertQueries.push(["DUPLICATECLUSTERS", cols, null, valuesToInsert]);
    }
    return duplicatesToInsertQueries;
}

/*
 * Deletes all clusters
 *
 * @returns {String} Count of records deleted
 */
_DuplicateScannerUtils._deleteDuplicateClusters = function ()
{
    return db.deleteData("DUPLICATECLUSTERS");
}

/*
 * All records with contactId = sourceContactId get updated, which are not assigned to the same "group" as the targetContactId.
 * This is because otherwise there would now be in total two "participants" in the same "group" as opposed to one before.
 * Also if they already are in the same "group" those records shouldn't be updated because it would lead to the same outcome.
 * 
 * Mandatory: All records ignored for the time being have to be deleted aswell! See #_DuplicateScannerUtils._buildRemoveObsoleteParticipantsRecordsDeleteQuery
 * @returns {String[]} Query to update records
 */
_DuplicateScannerUtils._buildUpdateAttachParticipantsToNewContactQuery = function (pTableName, pContactIdColumn, pAssignableIdColumn, pSourceContactId, pTargetContactId, updateStatements)
{
    var selectAssignableIdsOfTargetContactQuery = "select " + pAssignableIdColumn 
                                                    + " from " + pTableName 
                                                    + " where " + pContactIdColumn + " = '" + pTargetContactId + "'";
    let updateCondition = pAssignableIdColumn
                            + " not in"
                            + " (" + selectAssignableIdsOfTargetContactQuery + ")"
                            + " and " + pContactIdColumn + " = '" + pSourceContactId + "'";
    
    return [[pTableName, [pContactIdColumn], null, [pTargetContactId], updateCondition]];
}


_DuplicateScannerUtils._buildDeleteRemoveObsoleteParticipantsRecordsQuery = function (pTableName, pContactIdColumn, pAssignableIdColumn, pSourceContactId, pTargetContactId, updateStatements)
{
    var selectAssignableIdsOfTargetContactQuery = "select " + pAssignableIdColumn 
                                                    + " from " + pTableName 
                                                    + " where " + pContactIdColumn + " = '" + pTargetContactId + "'";

    let deleteCondition = pAssignableIdColumn + " in"
                            + " (" + selectAssignableIdsOfTargetContactQuery + ")"
                            + " and " + pAssignableIdColumn + " = '" + pSourceContactId + "'";
    let recordsToDelete = [];
    recordsToDelete.push([pTableName, deleteCondition]);
    return recordsToDelete;
}


/*
 * Creates Queries to delete from the PERSON and CONTACT table.
 *
 * @returns {String[]} Queries to delete
 */
_DuplicateScannerUtils._buildDeletePersonAndContactQuery = function(pSourcePersonId, pSourceContactId)
{
    let recordsToDelete = []
    recordsToDelete.push(["PERSON", "PERSONID = '" + pSourcePersonId + "'"]);
    recordsToDelete.push(["CONTACT", "CONTACTID = '" + pSourceContactId + "'"]);
    return recordsToDelete;
}

_DuplicateScannerUtils._buildDeleteOrganisationAndContactQuery = function(pSourceOrganisationId, pSourceContactId)
{
    let recordsToDelete = []
    recordsToDelete.push(["ORGANISATION", "ORGANISATIONID = '" + pSourceOrganisationId + "'"]);
    recordsToDelete.push(["CONTACT", "CONTACTID = '" + pSourceContactId + "'"]);
    return recordsToDelete;
}

_DuplicateScannerUtils._buildDeleteCachedUnrelatedDuplicateQuery = function(pSourceContactId)
{
    let recordsToDelete = []
    recordsToDelete.push(["UNRELATEDDUPLICATES", "SOURCEDUPLICATEID = '" + pSourceContactId + "'"]);
    recordsToDelete.push(["UNRELATEDDUPLICATES", "UNRELATEDDUPLICATEID = '" + pSourceContactId + "'"]);
    return recordsToDelete;
}

/*
 * Creates a filter JSON which excludes the field and it's value using the operator "NOT_EQUAL"
 *
 * @param {String} pRecordIdFieldToIgnore Field to be ignored
 * @param {String} pRecordIdValueToIgnore The fields value
 * @param {String} pTargetEntity Entity which has been configured
 * @returns {String[]} Array where 0 = filter, 1 = null(INDEX_COUNT_CHARS_TO_USE), 2 = null(INDEX_MAX_RESULTS_THRESHOLD)
 */
_DuplicateScannerUtils._getIgnoreRecordFilter = function(pRecordIdFieldToIgnore, pRecordIdValueToIgnore, pTargetEntity)
{
    let ignoreFilterJson = JSON.stringify({"entity":pTargetEntity,"filter":{"type":"group","operator":"AND","childs":[{"type":"row","name":pRecordIdFieldToIgnore,"operator":"NOT_EQUAL","value":pRecordIdValueToIgnore,"key":"","contenttype":"TEXT"}]}});
    
    return [ignoreFilterJson, null, null];
}

_DuplicateScannerUtils._buildUpdateContactIdStatements = function(pTableInfos, pSourceContactId, pTargetContactId)
{
    let statements = [];
    
    for (let i = 0; i < pTableInfos.length; i++) 
    {
        let tableInfo = pTableInfos[i];
        let updateStatement = _DuplicateScannerUtils._buildStatement(tableInfo, pSourceContactId, pTargetContactId);
        statements.push(updateStatement);
    }
    return statements;
}

_DuplicateScannerUtils._buildStatement = function(pTableinfos, pSourceContactId, pTargetContactId)
{
    let tableName = pTableinfos[INDEX_TABLE_NAME];
    let columnName = pTableinfos[INDEX_COLUMN_NAME];
    let additionalCondition = pTableinfos[INDEX_CONDITION];
    
    let condition = columnName + " = '" + pSourceContactId + "'";
    
    if(additionalCondition != "")
        condition += " and ( " + additionalCondition + ") ";
    
    return [tableName, [columnName], null, [pTargetContactId], condition];
}

/*
 * Contains all Tables and their fields which may contain the contact id to be replaced for the data alias
 *
 * @returns {String[[]]} Array in the format [TableName, ContactIdColumnName, AdditionalCondition]
 */
_DuplicateScannerUtils._getMergeUpdateTableInfosCurrentAlias = function()
{
    var tableInfos = new Array();
    tableInfos.push(["AB_APPOINTMENTLINK", "OBJECT_ROWID", ""]);
    tableInfos.push(["AB_CTILOG", "CONTACT_ID", ""]);
    tableInfos.push(["AB_OBJECTRELATION", "AB_OBJECTRELATIONID", ""]);
    tableInfos.push(["AB_OBJECTRELATION", "OBJECT1_ROWID", ""]);
    tableInfos.push(["AB_OBJECTRELATION", "OBJECT2_ROWID", ""]);
    tableInfos.push(["AB_LOGHISTORY", "TABLENAMEID", ""]);
    tableInfos.push(["ADDRESS", "CONTACT_ID", ""]);
    tableInfos.push(["BULKMAILRECIPIENT", "CONTACT_ID", ""]);
    tableInfos.push(["BULKMAIL", "TESTING_CONTACT_ID", ""]);
    tableInfos.push(["CAMPAIGN", "EMPLOYEE_CONTACT_ID", ""]);
    tableInfos.push(["CAMPAIGNSTEP", "EMPLOYEE_CONTACT_ID", ""]);
    tableInfos.push(["COMMRESTRICTION", "CONTACT_ID", ""]);
    tableInfos.push(["COMMRESTRICTION", "EMPLOYEE_INVOLVED", ""]);
    tableInfos.push(["COMMUNICATION", "CONTACT_ID", ""]);
    tableInfos.push(["COMPETITION", "CONTACT_ID", ""]);
    tableInfos.push(["CONTRACT", "CONTACT_ID", ""]);
    tableInfos.push(["LETTERRECIPIENT", "CONTACT_ID", ""]);
    tableInfos.push(["OBJECTMEMBER", "CONTACT_ID", ""]);
    tableInfos.push(["OFFER", "CONTACT_ID", ""]);
    tableInfos.push(["PRODUCT", "CONTACT_ID", ""]);
    tableInfos.push(["PRODUCTPRICE", "CONTACT_ID", ""]);
    tableInfos.push(["SALESORDER", "CONTACT_ID", ""]);
    tableInfos.push(["SALESPROJECT", "CONTACT_ID", ""]);
    tableInfos.push(["TASK", "REQUESTOR_CONTACT_ID", ""]);
    tableInfos.push(["TASK", "EDITOR_CONTACT_ID", ""]);
    tableInfos.push(["TASKLINK", "OBJECT_ROWID", ""]);
    tableInfos.push(["ACTIVITY", "RESPONSIBLE", ""]);
    tableInfos.push(["DSGVO", "CONTACT_ID", ""]);
    tableInfos.push(["DSGVOINFO", "CONTACT_ID", ""]);
    tableInfos.push(["TIMETRACKING", "CONTACT_ID", ""]);
    tableInfos.push(["ACTIVITYLINK", "OBJECT_ROWID", ""]);
    tableInfos.push(["AB_ATTRIBUTERELATION", "OBJECT_ROWID", ""]);

    return tableInfos;
}

/*
 * Contains all Tables and their fields which may contain the contact id to be replaced for the system alias
 *
 * @returns {String[[]]} Array in the format [TableName, ContactIdColumnName, AdditionalCondition]
 */
_DuplicateScannerUtils._getMergeUpdateTableInfosSystemAlias = function(pSourceContactId, pTargetContactId)
{
    var tableInfos = new Array();
    tableInfos.push(["ASYS_CALENDARLINK", "DBID", ""]);
    tableInfos.push(["ASYS_BINARIES", "ROW_ID", "TABLENAME = 'CONTACT'"]);
    return tableInfos;
}

/*
 * Loads all filters for the requested scanner in the format of:
 * [CONDITION, COUNT_CHARACTERS_TO_USE, MAX_RESULTS_THRESHOLD]
 *
 * @param {String} pFilterName Name of the filter
 * @param {String} pTargetEntity Entity which has been configured
 * @returns {String[[]]} Array of arrays in the format [CONDITION, COUNT_CHARACTERS_TO_USE, MAX_RESULTS_THRESHOLD]
 */
_DuplicateScannerUtils._loadFilters = function(pFilterName, pTargetEntity)
{
    let query = "select \"CONDITION\", COUNT_CHARACTERS_TO_USE, MAX_RESULTS_THRESHOLD from DUPLICATESCANNERPREFILTERCONFIG"
                + " join DUPLICATESCANNER on DUPLICATESCANNER.ID = DUPLICATESCANNERPREFILTERCONFIG.DUPLICATESCANNER_ID"
                + " where FILTER_NAME = '" + pFilterName + "'"
            + " and ENTITY_TO_SCAN_NAME = '" + pTargetEntity + "'";
    return db.table(query);
}

/*
 * Starts at the pJsonRootNode and recursively traverses thru the jsons "row" and "group" nodes to fill the fields values based on
 * the values in pEntitiyFieldAndValueMap.
 *
 * @param {JSON} pJsonRootNode A filter Json
 * @param {Map{"key", "value"}}} pEntitiyFieldAndValueMap {"key", "value"}
 * @returns {JSON} The JSON with filled values based on pEntitiyFieldAndValueMap and pCountCharsOfValueToUse
 */
_DuplicateScannerUtils._insertValuesInFilterTemplate = function(pJsonRootNode, pEntitiyFieldAndValueMap, pCountCharsOfValueToUse)
{
    for(var filterChildNode in pJsonRootNode)
    {
        var currentNode = pJsonRootNode[filterChildNode];
        if(currentNode.type == "row")
        {
            let fieldName = currentNode.name;
            let fieldValue = pEntitiyFieldAndValueMap[fieldName];
            pCountCharsOfValueToUse = parseInt(pCountCharsOfValueToUse, 10);
            
            if(fieldValue == null)
            {
                logging.show("Duplicate Scan: Requested value for field " + fieldName + " not present in the provided valueslist");
                continue;
            }

            if(_DuplicateScannerUtils._isNotNullAndANumber(pCountCharsOfValueToUse)
                && _DuplicateScannerUtils._isValueLongerThanCharsToUse(fieldValue.length, pCountCharsOfValueToUse))
            {
                fieldValue = fieldValue.substring(0, pCountCharsOfValueToUse);
            }
            
            pJsonRootNode[filterChildNode].value = fieldValue;
        }
        else
        {
            //Type of curren node is "group", now run thru it's nodes
            let populatedChildNodes = _DuplicateScannerUtils._insertValuesInFilterTemplate(currentNode.childs, pEntitiyFieldAndValueMap, pCountCharsOfValueToUse);
            pJsonRootNode[filterChildNode].childs = populatedChildNodes;
        }
    }
    return pJsonRootNode;
}

/*
 * Returns wether or not a value should be substring'd
 *
 * @return true if pCountCharsOfValueToUse is a number, greater than 0 and smaller than the values length
 */
_DuplicateScannerUtils._isValueLongerThanCharsToUse = function(pValueLength, pCountCharsOfValueToUse)
{
    return !isNaN(pCountCharsOfValueToUse) 
            && pCountCharsOfValueToUse > 0 
            && pValueLength > pCountCharsOfValueToUse;
}

/*
 * Returns wether or not the parameter isnt null and a number or not
 *
 * @param {String} pCountCharsOfValueToUse Hopefully a number
 * @returns {String} True if parameter isnt null and a number, False if it's null or no number
 */
_DuplicateScannerUtils._isNotNullAndANumber = function(pCountCharsOfValueToUse) 
{
    return pCountCharsOfValueToUse != null && !isNaN(pCountCharsOfValueToUse);
}