process.js

import("system.project");
import("ActivityTask_lib");
import("KeywordRegistry_basic");
import("system.translate");
import("system.datetime");
import("JditoFilter_lib");
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: [FILTER_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 = newSelect("FILTER_CONDITION, COUNT_CHARACTERS_TO_USE, MAX_RESULTS_THRESHOLD")
                    .from("DUPLICATESCANNERPREFILTERCONFIG")
                    .join("DUPLICATESCANNER", "DUPLICATESCANNER.ID = DUPLICATESCANNERPREFILTERCONFIG.DUPLICATESCANNER_ID")
                    .where("DUPLICATESCANNER.FILTER_NAME", pFilterName)
                    .and("DUPLICATESCANNER.ENTITY_TO_SCAN_NAME", pTargetEntity);

    return query.table();
}

/*
 * 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)
{


    var query = newSelect("count(ID), CLUSTERID")
                    .from("DUPLICATECLUSTERS")
                    .where("DUPLICATECLUSTERS.CLUSTERID", newSelect("CLUSTERID")
                                                            .from("DUPLICATECLUSTERS")
                                                            .where("DUPLICATECLUSTERS.DUPLICATEID", pDuplicateId).build(),
                                                        SqlBuilder.IN())
                    .and("DUPLICATECLUSTERS.DUPLICATEID", pDuplicateId, SqlBuilder.NOT_EQUAL())
                    .groupBy("CLUSTERID");

    var countAndClusterId = query.arrayRow();
    let countDuplicatesInClusterWithoutParameterId = countAndClusterId[0];
    let clusterId = countAndClusterId[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", newWhere("DUPLICATECLUSTERS.CLUSTERID", clusterId).build()]);
        deleteStatements.push(["UNRELATEDDUPLICATES", newWhere("UNRELATEDDUPLICATES.CLUSTERID", clusterId).build()]);

        db.deletes(deleteStatements);
    }
    else
    {
        newWhereIfSet("DUPLICATECLUSTERS.DUPLICATEID", pDuplicateId)
            .deleteData(true, "DUPLICATECLUSTERS");

        //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 newWhereIfSet("DUPLICATECLUSTERS.TARGET_ENTITY", pTargetEntity)
                .deleteData(true, "DUPLICATECLUSTERS");
}

/*
 * 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 = newSelect("dc1.CLUSTERID, ud.CLUSTERID")
                    .from("UNRELATEDDUPLICATES", "ud")
                    .join("DUPLICATECLUSTERS", "dc1.DUPLICATEID = ud.SOURCEDUPLICATEID", "dc1")
                    .join("DUPLICATECLUSTERS", "dc2.DUPLICATEID = ud.UNRELATEDDUPLICATEID", "dc2")
                    .where(["DUPLICATECLUSTERS", "TARGET_ENTITY", "dc1"], pTargetEntity);

    let newIdOldIdRay = query.table();
    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 updateStatement = ["UNRELATEDDUPLICATES", updateColumns, null, [newClusterId], newWhere("UNRELATEDDUPLICATES.CLUSTERID", oldClusterId).build()];

        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.
     */
    newWhere("UNRELATEDDUPLICATES.CLUSTERID", newSelect("dc1.CLUSTERID").from("DUPLICATECLUSTERS", "dc1"), SqlBuilder.NOT_IN())
        .deleteData(true, "UNRELATEDDUPLICATES");
}

/*
 * 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)
{
    return newSelect("CLUSTERID")
                .from("DUPLICATECLUSTERS")
                .where("DUPLICATECLUSTERS.DUPLICATEID", pDuplicateId)
                .cell();
}

/*
 * 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)
{
    return newWhere("UNRELATEDDUPLICATES.SOURCEDUPLICATEID", pSourceDuplicateId)
                .and("UNRELATEDDUPLICATES.UNRELATEDDUPLICATEID", pUnrelatedDuplicateId)
                .deleteData(true, "UNRELATEDDUPLICATES");
}

/*
 * 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)
{
    DuplicateScannerUtils.DeleteUnrelatedDuplicateRelation(pDuplicateId, pDuplicateId);
}

/*
 * Loads all other duplicates from the cluster in which the parameterized duplicate is located
 *
 *  @param {String} pDuplicateId
 *  @returns {String[]} Array of duplicate ids
 */
DuplicateScannerUtils.GetCachedDuplicatesForDuplicateId = function(pDuplicateId)
{
    return newSelect("DUPLICATEID")
                .from("DUPLICATECLUSTERS")
                .where("DUPLICATECLUSTERS.CLUSTERID", newSelect("CLUSTERID")
                                                            .from("DUPLICATECLUSTERS")
                                                            .where("DUPLICATECLUSTERS.DUPLICATEID", pDuplicateId),
                                                    SqlBuilder.IN())
                .and("DUPLICATECLUSTERS.DUPLICATEID", pDuplicateId, SqlBuilder.NOT_EQUAL())
                .and("DUPLICATECLUSTERS.DUPLICATEID", newSelect("UNRELATEDDUPLICATEID")
                                                            .from("UNRELATEDDUPLICATES")
                                                            .where("UNRELATEDDUPLICATES.SOURCEDUPLICATEID", pDuplicateId),
                                                    SqlBuilder.NOT_IN())
                .and("DUPLICATECLUSTERS.DUPLICATEID", newSelect("SOURCEDUPLICATEID")
                                                            .from("UNRELATEDDUPLICATES")
                                                            .where("UNRELATEDDUPLICATES.UNRELATEDDUPLICATEID", pDuplicateId),
                                                    SqlBuilder.NOT_IN())
                .arrayColumn();
}

/*
 * 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)
{
    return newSelect("DUPLICATEID")
                .from("DUPLICATECLUSTERS")
                .where("DUPLICATECLUSTERS.DUPLICATEID", newSelect("UNRELATEDDUPLICATEID").from("UNRELATEDDUPLICATES"),
                            SqlBuilder.NOT_IN())
                .and("DUPLICATECLUSTERS.CLUSTERID", pClusterId)
                .arrayColumn();
}

/*
 * Recreates the cached duplicate clusters based on the configured pattern. <br />
 * The old clusters have to be deleted manually beforehand or by 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 search will be executed beforehand and the results 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} pRecordsBlockSize The values which are checked get loaded in blocks.
 * @param {String} pFormatValuesConsumeWebserviceCallback Null if no external service is used otherwise a function with one parameter.
 * @return {Int} Count of duplicate clusters created
 *
 * @example
 * var filterName = "PersonDuplicates";
 * var targetEntity = "Person_entity";
 * var recordBlockSize = DuplicateScannerUtils.GetBlockSize();
 *
 * 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];
 *              //format values
 *          }
 *      }
 *      //call webservice
 *      //reformat results to same structure as before
 *      return pPossibleDuplicatesRay;
 * };
 *
 * DuplicateScannerUtils.DeleteDuplicateClustersByTargetEntity(targetEntity);
 *
 * DuplicateScannerUtils.RebuildDuplicatesCache(filterName, targetEntity, pRecordsBlockSize, formatToJsonAndCallWsCallback);
 *
 * DuplicateScannerUtils.RefreshUnrelatedDuplicateRelations(targetEntity);
 */
DuplicateScannerUtils.RebuildDuplicatesCache = function(pFilterName, pTargetEntity,
    pRecordsBlockSize, pFormatValuesConsumeWebserviceCallback)
{
    let useExternalWebservice = _DuplicateScannerUtils._isUseExternalWebservice(pFilterName, pTargetEntity);
    let resultFields = DuplicateScannerUtils.LoadResultFields(filterName, targetEntity);

    let indexPattern = _DuplicateScannerUtils._loadIndexPattern(pFilterName, pTargetEntity);
    let entityFieldConfigs = _DuplicateScannerUtils._loadEntityFieldConfigsFromPattern(indexPattern);
    let entityIdField = _DuplicateScannerUtils._loadEntityIdField(pFilterName, pTargetEntity);
    
    let alreadyIdentifiedIds = [];
    
    let entityFields = _DuplicateScannerUtils._loadEntityFieldsFromFieldConfigs(entityFieldConfigs);

    entityFields.push(entityIdField);
    
    let targetRecords = DuplicateScannerUtils.LoadEntityRecords(pTargetEntity, entityFields, 0, pRecordsBlockSize);

    let currentRecordIndex = pRecordsBlockSize;
    while(targetRecords.length > 0)
    {
        
        foundDuplicateIds = DuplicateScannerUtils.ScanRecords(pTargetEntity, targetRecords,
            entityFieldConfigs, resultFields, useExternalWebservice,
            pFormatValuesConsumeWebserviceCallback, alreadyIdentifiedIds, indexPattern, entityIdField);
        
        if (foundDuplicateIds)
            alreadyIdentifiedIds = alreadyIdentifiedIds.concat(foundDuplicateIds);

        if(targetRecords.length < pRecordsBlockSize)
        {
            break;
        }
        targetRecords = DuplicateScannerUtils.LoadEntityRecords(pTargetEntity, entityFields,
            currentRecordIndex, pRecordsBlockSize);

        currentRecordIndex += pRecordsBlockSize;
    }
}

DuplicateScannerUtils.ScanRecords = function(pTargetEntity, pTargetRecordsData,
    pEntityFieldConfigs, pResultFields, pUseExternalWebservice, pFormatValuesConsumeWebserviceCallback, pAlreadyIdentifiedIds, pIndexPattern, pEntityIdField)
{
    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 null;

    //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
    var duplicatesToInsertQueries = [];
    for (b = 0; b < pTargetRecordsData.length; b++)
    {
        let entityFieldValuesRay = DuplicateScannerUtils.BuildEntityFieldConfigValueRays(pEntityFieldConfigs, pTargetRecordsData[b]);

        //The first field in this Array must always be the configured id field. This is ensured using onValidation-logic
        let idValue = pTargetRecordsData[b][pEntityIdField];

        //If the current Id has already been identified, continue
        if(pAlreadyIdentifiedIds.indexOf(pTargetRecordsData[b][pEntityIdField]) > -1)
        {
            continue;
        }

        let foundDuplicates = _DuplicateScannerUtils._scanForDuplicates(pTargetEntity,
            entityFieldValuesRay, pResultFields, idValue, pFormatValuesConsumeWebserviceCallback, pUseExternalWebservice, pIndexPattern)

        if(foundDuplicates == null || foundDuplicates.length == 0)
        {
            continue;
        }
        //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];
            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][pEntityIdField]);

        let insertQueriesRay = _DuplicateScannerUtils._createInsertDuplicatesClusterQuery(foundDuplicateIds, pTargetEntity)
        duplicatesToInsertQueries = duplicatesToInsertQueries.concat(insertQueriesRay);
        foundDuplicateIds = [];
    }
    db.inserts(duplicatesToInsertQueries, db.getCurrentAlias(), 10 * datetime.ONE_MINUTE);
    return foundDuplicateIds;
}

/* 
 * Searches for a cluster which contains the duplicates specified by the parameterized array. <br />
 * The contents of the cluster have to be identical, if no fitting cluster could be found an empty string is returned.
 * 
 * @param {String} pNewRecordId The id of the record which was used to scan for duplicates
 * @param {String[]} pDuplicateIds Duplicate ids used to search for a cluster containing them
 * @param {String} pTargetEntity Entity which has been configured
 * @returns {String} A clusterid if a matching cluster has been found, otherwise ""
 */
DuplicateScannerUtils.CacheNewScanResults = function(pNewRecordId, pDuplicateIds, pTargetEntity)
{
    let duplicateIds = [];
    //Run thru every duplicate result and read out the id. 
    //Do it now to have a simple array on all usages lateron.
    for (let i = 0; i < pDuplicateIds.length; i++)
    {
        let duplicateContactId = pDuplicateIds[i][indexsearch.FIELD_ID];
        duplicateIds.push(duplicateContactId);
    }
    
    let clusterId = DuplicateScannerUtils.GetClusterWithIdenticalDuplicates(duplicateIds);
    
    //If no cluster has beend found, create a new one with all found duplicateIds, 
    //otherwise add the id to the existing cluster
    let idRayToInsert = [];
    if(clusterId == undefined || clusterId == null || clusterId == "")
    {
        idRayToInsert = duplicateIds;
        idRayToInsert.push(pNewRecordId);
    }
    else
        idRayToInsert.push(pNewRecordId);
    
    insertQueriesRay = _DuplicateScannerUtils._createInsertDuplicatesClusterQuery(idRayToInsert, pTargetEntity, clusterId)

    return db.inserts(insertQueriesRay);
}

/* 
 * Searches for a cluster which contains the duplicates specified by the parameterized array. <br />
 * The contents of the cluster have to be identical, if no fitting cluster could be found an empty string is returned.
 * 
 * @param {String[]} pDuplicateIds Duplicate ids which should be in the same cluster
 * @returns {String} Id of the cluster which contains all given duplicate ids or ""
 */
DuplicateScannerUtils.GetClusterWithIdenticalDuplicates = function(pDuplicateIds)
{
    let RESULT_NO_CLUSTER_FOUND = "";

    if(pDuplicateIds.length < 1)
        return RESULT_NO_CLUSTER_FOUND;
    
    let clusterIdSelect = newSelect("distinct CLUSTERID")
                                .from("DUPLICATECLUSTERS")
                                .where();
    
    for (let i = 0; i < pDuplicateIds.length; i++) 
    {
        clusterIdSelect.and("DUPLICATECLUSTERS.CLUSTERID", newSelect("CLUSTERID").from("DUPLICATECLUSTERS").where("DUPLICATECLUSTERS.DUPLICATEID", pDuplicateIds[i]),
                                        SqlBuilder.IN());
    }

    let foundClusterId = clusterIdSelect.cell();

    if(foundClusterId == null || foundClusterId == "")
        return RESULT_NO_CLUSTER_FOUND;
    
    let duplicatesInCluster = newSelect("DUPLICATEID")
                                            .from("DUPLICATECLUSTERS")
                                            .where("DUPLICATECLUSTERS.CLUSTERID", foundClusterId)
                                            .arrayColumn();

    /* 
     * A cluster has been searched which contains all duplicate ids as specified via parameter.
     * There's the possibility that this cluster contains even more duplicates than specified via the parameter.
     * In this case, the cluster and the parameterized duplicateids are not identical 
     * which means a new cluster has to be created.
     */
    if(pDuplicateIds.length != duplicatesInCluster.length)
        return RESULT_NO_CLUSTER_FOUND;
    else
        return foundClusterId;
}

DuplicateScannerUtils.LoadEntityRecords = function(pTargetEntity, pEntityFields, pStartRow, pCountRecordsToLoad)
{
    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)
{
    return newSelect("dsrfc.ENTITY_FIELD_NAME")
                .from("DUPLICATESCANNERRESULTFIELDCONFIG dsrfc")
                .join("DUPLICATESCANNER", "DUPLICATESCANNER.ID = dsrfc.DUPLICATESCANNER_ID")
                .where("DUPLICATESCANNER.FILTER_NAME", pFilterName)
                .and("DUPLICATESCANNER.ENTITY_TO_SCAN_NAME", pTargetEntity)
                .arrayColumn();
}

/*
 * Scans for duplicates based on the configured pattern and the selected id field<br />
 * All values to the used placeholders have to be present in "pValuesToCheck"<br />
 * First all placeholders in the pattern will be replaced with their respective values.
 * Then, the pattern is extended wo exclude the record on which the search is based on.
 *<br />
 * If the usage of an external webservice has been activated, the result 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
 * field names can be converted using DuplicateScannerUtils.TranslateEntityToIndexFields
 * <br />
 * <br />
 * Attention!<br />
 * 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.<br />
 * 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 {{"key", "value"}} pValuesToCheck An object with key value pairs which hold the name of the entity field as key and it's value as value. See the example "valuesToCheck"
 * @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";
 * let targetEntity = "Person_entity";
 * let valuesToCheck = {};
 * var entityModel = project.getEntityStructure(targetEntity);
 * 
 * //Read the values of all available entity fields and write the fieldname/value combination
 * //as key/value pairs into an object. This is used to trigger the scan for duplicates
 * let fieldValue = "";
 * let entityFields = [];
 * for (fieldname in entityModel.fields) 
 * { 
 *     field = entityModel.fields[fieldname]; 
 *     if(field.fieldType == project.ENTITYFIELDTYPE_FIELD)
 *     {
 *         fieldValue = vars.get("$field." + field.name);
 *         
 *         if(fieldValue != null && fieldValue != "")
 *         {
 *             valuesToCheck[field.name] = fieldValue;
 *         }
 *     }
 * }
 *
 * 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];
 *             //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, valuesToCheck,
 * formatToJsonAndCallWsCallback);
 */
DuplicateScannerUtils.ScanForDuplicates = function(pFilterName, pTargetEntity, pValuesToCheck,
    pFormatValuesConsumeWebserviceCallback)
{
    let useExternalWebservice = _DuplicateScannerUtils._isUseExternalWebservice(pFilterName, pTargetEntity);
    let resultFields = DuplicateScannerUtils.LoadResultFields(pFilterName, pTargetEntity);
    
    let indexPattern = _DuplicateScannerUtils._loadIndexPattern(pFilterName, pTargetEntity);
    let entityFieldConfigs = _DuplicateScannerUtils._loadEntityFieldConfigsFromPattern(indexPattern);  
    let entityIdField = _DuplicateScannerUtils._loadEntityIdField(pFilterName, pTargetEntity);
    
    let entityFieldConfigValuesRay = DuplicateScannerUtils.BuildEntityFieldConfigValueRays(entityFieldConfigs, pValuesToCheck);
   
   //The first field in this Array must always be the configured id field.
    let idValue = pValuesToCheck[entityIdField];

    return _DuplicateScannerUtils._scanForDuplicates(pTargetEntity,
        entityFieldConfigValuesRay, resultFields, idValue,
        pFormatValuesConsumeWebserviceCallback, useExternalWebservice, indexPattern)
}

/*
 * 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);
 *
 */
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;
}

/*
 *
 * Merges the source person into the target person. 
 * This 
 * - replaces the source's with the target's contactid in a predefined set of tables.
 * - resets the standard communications of the source contact and keeps the ones of the target.
 * - updates participants of campaigns and removes obsolet ones(which would be duplicates)
 * - deletes the source person and contact
 * - deletes the duplicate record, if one exists
 * - deletes all unrelated-duplicate-relations containing the source contact id
 *
 *  @param {String} pSourceContactId The contact to be integrated into another
 *  @param {String} pTargetContactId The contact in which the source gets integrated
 *  @returns {String}
 */
DuplicateScannerUtils.MergePerson = function(pSourceContactId, pTargetContactId)
{
    var updateStatementsCurrentAlias = [];
    var updateStatementsSystemAlias = [];
    var deleteStatements = [];

    var sourcePersonId = newSelect("PERSON_ID")
                            .from("CONTACT")
                            .where("CONTACT.CONTACTID", pSourceContactId)
                            .cell();
    var tableInfosCurrentAlias = _DuplicateScannerUtils._getMergeUpdateTableInfosCurrentAlias();
    var tableInfosSystemAlias = _DuplicateScannerUtils._getMergeUpdateTableInfosSystemAlias();

    updateStatementsCurrentAlias = updateStatementsCurrentAlias.concat(_DuplicateScannerUtils._buildUpdateResetStandardCommunications(pSourceContactId));
    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, SqlUtils.getSystemAlias()));

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

    var affectedRowsCurrentAlias = db.updates(updateStatementsCurrentAlias);
    var affectedRowsSystemAlias = db.updates(updateStatementsSystemAlias, SqlUtils.getSystemAlias());
    var deletedRows = db.deletes(deleteStatements)

    DuplicateScannerUtils.DeleteCachedDuplicate(pSourceContactId);

    return (affectedRowsCurrentAlias > 0 && deletedRows >= 2);
}

DuplicateScannerUtils.CreateMergeSuccessActivity = function(pSourceContactId, pTargetContactId, pCurrentContactId, pContext)
{
    var activityDataForInsert = {
        subject: translate.withArguments("A %0 record has been merged", [pContext]),
        content: translate.withArguments("%0 with ID \"%1\" has been integrated into the %0 with the ID \"%2\"", [pContext, pSourceContactId, pTargetContactId]),
        //categoryKeywordId: $KeywordRegistry.ac
        directionKeywordId: $KeywordRegistry.activityDirection$internal(),
        responsibleContactId: pCurrentContactId
    };
    var activityLinks = [[pContext, pTargetContactId]];

    return ActivityUtils.insertNewActivity(activityDataForInsert, activityLinks, null, db.getCurrentAlias());
}

DuplicateScannerUtils.MergeOrganisation = function(pSourceContactId, pTargetContactId)
{
    let updateStatementsCurrentAlias = [];
    let updateStatementsSystemAlias = [];
    let deleteStatements = [];

    var sourceOrganisationId = newSelect("ORGANISATION_ID")
                                    .from("CONTACT")
                                    .where("CONTACT.CONTACTID", pSourceContactId)
                                    .cell();

    var tableInfosCurrentAlias = _DuplicateScannerUtils._getMergeUpdateTableInfosCurrentAlias();
    var tableInfosSystemAlias = _DuplicateScannerUtils._getMergeUpdateTableInfosSystemAlias();

	updateStatementsCurrentAlias = updateStatementsCurrentAlias.concat(_DuplicateScannerUtils._buildUpdateResetStandardCommunications(pSourceContactId));
    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));

    let affectedRowsCurrentAlias = db.updates(updateStatementsCurrentAlias);
    let affectedRowsSystemAlias = db.updates(updateStatementsSystemAlias, SqlUtils.getSystemAlias());
    let deletedRows = db.deletes(deleteStatements)

    DuplicateScannerUtils.DeleteCachedDuplicate(pSourceContactId);

    return (affectedRowsCurrentAlias > 0 && deletedRows >= 2);
}

/*
 * Creates an array of arrays containing the entity field config 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 {{"key", "value"}} pTargetRecordData One record containing the values for the configured fields. It's in the format of {"key(=EntityFieldName", "Value"}
 * @return {[[]]} An array of arrays containing the entity field config an its value. ["{entityfield: FIRSTNAME}", "PETER"]]
 * @example
 * pDuplicateFieldsConfig
 * ["CONTACTID", true, false]
 * ["FIRSTNAME", false, true]
 *
 * pTargetRecordData
 * ["d786045c-8b21-4f22-b6d9-72be9f61c04d", "PETER"]
 *
 * => ["{entityfield: FIRSTNAME}", "PETER"]]
 */
DuplicateScannerUtils.BuildEntityFieldConfigValueRays = function(pDuplicateFieldsConfig, pTargetRecordData)
{
    let INDEX_CONFIG_ENTITY_FIELD = 0;
    let entityFieldConfigValuesRay = [];
    /*
    * 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++)
    {
        let fieldConfig = JSON.parse("{" + pDuplicateFieldsConfig[a] + "}");
        let entityField = fieldConfig.entityfield;
        let entityFieldValue = pTargetRecordData[entityField];
        if(entityFieldValue == null)
            entityFieldValue = "";
        else
            entityFieldValue = entityFieldValue.toLowerCase().replace(/[():\.\/!]/gi, "");
        
        let exclude = fieldConfig.exclude;
        if ( exclude )
        {   
            for(let i = 0; i < exclude.length; i++)
            {    
                entityFieldValue = " " + entityFieldValue + " ";
                entityFieldValue = entityFieldValue.replace(new RegExp( " " + exclude[i] + " ", "gi"), "");
            }    
        }
        
        let valuelength = fieldConfig.length; 
        if ( valuelength )
            entityFieldValue = entityFieldValue.substr(0, parseInt(valuelength)) + "*";
        
        let emptyall = fieldConfig.emptyall; 
        if ( fieldConfig.emptyall && entityFieldValue == "")
            entityFieldValue = "*";
       
        entityFieldValue = entityFieldValue.trim();
        entityFieldConfigValuesRay.push([pDuplicateFieldsConfig[a], entityFieldValue]);
    }
    return entityFieldConfigValuesRay.length > 0 ? entityFieldConfigValuesRay : [["", ""]];
}

DuplicateScannerUtils.GetBlockSize = function()
{
    return project.getPreferenceValue("custom.duplicates.dataBlockSize", "5000");
}

DuplicateScannerUtils.GetEntityFieldsFromConfig = function(pFilterName, pTargetEntity)
{
    let indexPattern = _DuplicateScannerUtils._loadIndexPattern(pFilterName, pTargetEntity);
    if(indexPattern == null || indexPattern == "")
        return [];
    let fieldConfigs = _DuplicateScannerUtils._loadEntityFieldConfigsFromPattern(indexPattern);
    if(fieldConfigs == null || fieldConfigs.length < 1)
        return [];
    let entityFields = _DuplicateScannerUtils._loadEntityFieldsFromFieldConfigs(fieldConfigs);
    let entityIdField = _DuplicateScannerUtils._loadEntityIdField(pFilterName, pTargetEntity);
    entityFields.push(entityIdField);
    return entityFields;
}

DuplicateScannerUtils.GetUnrelatedRelationsForDuplicate = function(pDuplicateId)
{
    let unrelatedIds = [];
    let duplicateIds = newSelect("SOURCEDUPLICATEID, UNRELATEDDUPLICATEID")
                .from("UNRELATEDDUPLICATES")
                .where("UNRELATEDDUPLICATES.SOURCEDUPLICATEID", pDuplicateId)
                .or("UNRELATEDDUPLICATES.UNRELATEDDUPLICATEID", pDuplicateId)
                .table();
    let sourceDuplicateId = "";
    let unrelatedDuplicateId = "";
    for (let i = 0; i < duplicateIds.length; i++) 
    {
        sourceDuplicateId = duplicateIds[i][0];
        unrelatedDuplicateId = duplicateIds[i][1];
        
        if(sourceDuplicateId != null && sourceDuplicateId != "" && sourceDuplicateId != pDuplicateId)
        {
            unrelatedIds.push(sourceDuplicateId);
        }
        else if(unrelatedDuplicateId != null && unrelatedDuplicateId != "" && unrelatedDuplicateId != pDuplicateId)
        {
            unrelatedIds.push(unrelatedDuplicateId);
        }
    }
    return unrelatedIds;
}

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;

_DuplicateScannerUtils._loadEntityFieldsFromFieldConfigs = function(pEntityFieldConfigs)
{
    let fieldNames = [];
    for (i = 0; i < pEntityFieldConfigs.length; i++) 
    {
        let fieldConfig = JSON.parse("{" + pEntityFieldConfigs[i] + "}");
        let entityField = fieldConfig.entityfield;
        fieldNames.push(entityField);
    }
    return fieldNames;
}

_DuplicateScannerUtils._buildUpdateResetStandardCommunications = function(pSourceContactId)
{
    return [["COMMUNICATION", ["ISSTANDARD"], null, ["0"], newWhere("COMMUNICATION.CONTACT_ID", pSourceContactId).build()]];
}

/*
 * Gets the Pattern for the scanner
 * A pattern usually contains placeholders in the style of "{entityFieldName]"
 * 
 * @param {String} pScannerName Name of the filter to use
 * @param {String} pTargetEntity The target entity which has been assigned to the filters configuration
 * @returns {String} Scan pattern as string
 */
_DuplicateScannerUtils._loadIndexPattern = function(pScannerName, pTargetEntity)
{
    let scanPattern = newSelect("SCAN_PATTERN")
                            .from("DUPLICATESCANNER")
                            .where("DUPLICATESCANNER.FILTER_NAME", pScannerName)
                            .and("DUPLICATESCANNER.ENTITY_TO_SCAN_NAME", pTargetEntity)
                            .cell();
    scanPattern = scanPattern.trim();
    return scanPattern;
}

_DuplicateScannerUtils._loadEntityFieldConfigsFromPattern = function(indexPattern)
{
    return indexPattern.match(/[^{}]+(?=\})/g);
}

_DuplicateScannerUtils._replacePlaceholderForValuesInPattern = function(pIndexPattern, pEntityFieldValueRays)
{
    let INDEX_ENTITY_FIELD_NAME = 0;
    let INDEX_ENTITY_FIELD_VALUE = 1;

    let placeholder = "";
    let fieldValue = "";
    for (let i = 0; i < pEntityFieldValueRays.length; i++) 
    {
        placeholder = "{" + pEntityFieldValueRays[i][INDEX_ENTITY_FIELD_NAME] + "}";
        fieldValue = pEntityFieldValueRays[i][INDEX_ENTITY_FIELD_VALUE];        
        pIndexPattern = pIndexPattern.replace(placeholder, fieldValue);
    }
    return pIndexPattern;
}

_DuplicateScannerUtils._loadEntityIdField = function(pFilterName, pTargetEntity)
{
    return newSelect("ID_FIELD_NAME")
                .from("DUPLICATESCANNER")
                .where("DUPLICATESCANNER.FILTER_NAME", pFilterName)
                .and("DUPLICATESCANNER.ENTITY_TO_SCAN_NAME", pTargetEntity)
                .cell();
}

/*
 * @see DuplicateScannerUtils.ScanForDuplicates for the documentation
 */
_DuplicateScannerUtils._scanForDuplicates = function(pTargetEntity, pEntityFieldConfigValuesRay,
pResultFields, pRecordIdValueToIgnore, pFormatValuesConsumeWebserviceCallback, pUseExternalWebservice, pIndexPattern)
{
    //No filterfields/indexpattern => No indexsearch
    if(pEntityFieldConfigValuesRay.length < 1 || pIndexPattern == null || pIndexPattern == "")
        return null;
    
    let possibleDuplicates = [];
    let ignoreSourceRecordPattern = _DuplicateScannerUtils._getIgnoreSourceRecordPattern(pRecordIdValueToIgnore);

    let indexPatternWithValues = _DuplicateScannerUtils._replacePlaceholderForValuesInPattern(pIndexPattern, pEntityFieldConfigValuesRay);
    indexPatternWithValues = ignoreSourceRecordPattern + indexPatternWithValues + ")";

    possibleDuplicates = _DuplicateScannerUtils._callIndexSearch(pTargetEntity, indexPatternWithValues, pResultFields, 100);
    
    if(possibleDuplicates == null)
        return null;

    possibleDuplicates = possibleDuplicates[indexsearch.HITS];
    
    if(pUseExternalWebservice && pFormatValuesConsumeWebserviceCallback != null)
        possibleDuplicates = pFormatValuesConsumeWebserviceCallback.apply(this, [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 isUseWebservice = newSelect("EXTERNAL_SERVICE_USAGE_ALLOWED")
                                .from("DUPLICATESCANNER")
                                .where("DUPLICATESCANNER.FILTER_NAME", pFilterName)
                                .and("DUPLICATESCANNER.ENTITY_TO_SCAN_NAME", pTargetEntity)
                                .cell();
    return (isUseWebservice == 0) ? false : true;
}

/*
 * Runs the indexsearch based on the given parameters.
 * If the "pEntityFieldValueRays" is empty, no search will be executed.
 * 
 * @param {String} pTargetEntity Entity which has been configured
 * @param {String} pIndexPatternWithValues The pattern used to search. Has to contain the values already.
 * @param {String} pResultFields The result field config. Use "DuplicateScannerUtils.LoadResultFields"
 * @param {String} pResultSetRows todo
 * @returns {[["key", "value"]] || null} Array of Key-Value-Pairs based on the configured pResultFields, if no pattern exists null
 */
_DuplicateScannerUtils._callIndexSearch = function(pTargetEntity, pIndexPatternWithValues, pResultFields, pResultSetRows)
{
    //The indexPattern can't be null because it is required to run the search.
    if(pIndexPatternWithValues == null || pIndexPatternWithValues == "")
        return null;
    let indexQuery = indexsearch.createIndexQuery()
                                .setPattern(pIndexPatternWithValues)
                                .setEntities([pTargetEntity])
                                // .setDefaultOperator(indexsearch.OPERATOR_AND)
                                //.addSearchFields("Person_entity.FIRSTNAME", "Person_entity.LASTNAME", "Person_entity.CONTACTID")
                                //.setRows(pResultSetRows);
    indexQuery = _DuplicateScannerUtils._setResultFields(indexQuery, pResultFields);
    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)
        pIndexQuery = pIndexQuery.addResultIndexFields(resultIndexFields);

    if(resultFields.length > 0)
    {
        pIndexQuery = pIndexQuery.addResultFields(resultFields);
    }
    return pIndexQuery;
}

/*
 * 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)
{
    return _DuplicateScannerUtils._createInsertDuplicatesClusterQuery(pDuplicatesRay, pTargetEntity, util.getNewUUID());
}

/*
 * 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
 * @param {String} pClusterId Clusters id to which the duplicates are in relation
 * @returns {String[]} Array of insert queries
 */
_DuplicateScannerUtils._createInsertDuplicatesClusterQuery = function (pDuplicatesRay, pTargetEntity, pClusterId)
{
    let duplicatesToInsertQueries = [];
    let cols = ["ID", "CLUSTERID", "DUPLICATEID", "TARGET_ENTITY"];

    if(pClusterId == undefined || pClusterId == null || pClusterId == "")
        pClusterId = util.getNewUUID();
            
    for (i = 0; i < pDuplicatesRay.length; i++)
    {
        let newId = util.getNewUUID();
        let valuesToInsert = [newId, pClusterId, 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 = newSelect(pAssignableIdColumn)
                                                        .from(pTableName)
                                                        .where([pTableName, pContactIdColumn], pTargetContactId);

    let updateCondition = newWhere([pTableName, pAssignableIdColumn], selectAssignableIdsOfTargetContactQuery, SqlBuilder.NOT_IN())
                                .and([pTableName, pContactIdColumn], pSourceContactId)

    return [[pTableName, [pContactIdColumn], null, [pTargetContactId], updateCondition.build()]];
}


_DuplicateScannerUtils._buildDeleteRemoveObsoleteParticipantsRecordsQuery = function (pTableName, pContactIdColumn, pAssignableIdColumn, pSourceContactId, pTargetContactId, updateStatements)
{
    var selectAssignableIdsOfTargetContactQuery = newSelect(pAssignableIdColumn)
                                                        .from(pTableName)
                                                        .where([pTableName, pContactIdColumn], pTargetContactId);

    let deleteCondition = newWhere([pTableName, pAssignableIdColumn], selectAssignableIdsOfTargetContactQuery, SqlBuilder.IN())
                                .and([pTableName, pAssignableIdColumn], pSourceContactId)

    let recordsToDelete = [];
    recordsToDelete.push([pTableName, deleteCondition.build()]);
    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", newWhere("PERSON.PERSONID", pSourcePersonId).build()]);
    recordsToDelete.push(["CONTACT", newWhere("CONTACT.CONTACTID", pSourceContactId).build()]);
    return recordsToDelete;
}

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

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

/*
 * Creates a pattern which excludes the field and it's value
 *
 * @param {String} pRecordIdValueToIgnore The fields value
 * @returns {String} Pattern which excludes the gived field in combination with the value
 */
_DuplicateScannerUtils._getIgnoreSourceRecordPattern = function(pRecordIdValueToIgnore)
{
    return "( +( -" + indexsearch.FIELD_ID + ":(" + pRecordIdValueToIgnore + ") ) ";
}

_DuplicateScannerUtils._buildUpdateContactIdStatements = function(pTableInfos, pSourceContactId, pTargetContactId, pAlias)
{
    let statements = [];

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

_DuplicateScannerUtils._buildStatement = function(pTableinfos, pSourceContactId, pTargetContactId, pAlias)
{
    let tableName = pTableinfos[INDEX_TABLE_NAME];
    let columnName = pTableinfos[INDEX_COLUMN_NAME];
    let additionalCondition = pTableinfos[INDEX_CONDITION];

    let condition = newWhere([tableName, columnName], pSourceContactId, undefined, undefined, pAlias);

    if(additionalCondition != "")
        condition.and(additionalCondition);

    return [tableName, [columnName], null, [pTargetContactId], condition.build()];
}

/*
 * 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()
{
    var tableInfos = new Array();
    tableInfos.push(["ASYS_CALENDARLINK", "DBID", ""]);
    tableInfos.push(["ASYS_BINARIES", "ROW_ID", "TABLENAME = 'CONTACT'"]);
    return tableInfos;
}

/*
 * 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);
}