process.js

import("ExportTemplate_lib");
import("system.workflow");
import("system.project");
import("Util_lib");
import("system.logging");
import("DuplicateScanner_lib");
import("system.datetime");
import("system.SQLTYPES");
import("Keyword_lib");
import("ActivityTask_lib");
import("KeywordRegistry_basic");
import("system.translate");
import("system.question");
import("system.vars");
import("system.text");
import("system.util");
import("Sql_lib");
import("system.db");

/**
 * functions for the lead import
 * Do not create an instance of this!
 *
 * @class
 * @static
 */
function LeadImportUtils(){}

/**
 * Returns the columns of the LEADTEMP table.
 * Do not use db.getColumns("LEADTEMP"); as the order of the columns is not guaranteed!
 * 
 * @return {String[]} all columns
 */
LeadImportUtils.leadTempColumns = function() 
{
    return [
        "COLUMN01",
        "COLUMN02",
        "COLUMN03",
        "COLUMN04",
        "COLUMN05",
        "COLUMN06",
        "COLUMN07",
        "COLUMN08",
        "COLUMN09",
        "COLUMN10",
        "COLUMN11",
        "COLUMN12",
        "COLUMN13",
        "COLUMN14",
        "COLUMN15",
        "COLUMN16",
        "COLUMN17",
        "COLUMN18",
        "COLUMN19",
        "COLUMN20",
        "COLUMN21",
        "COLUMN22",
        "COLUMN23",
        "COLUMN24",
        "COLUMN25",
        "COLUMN26",
        "COLUMN27",
        "COLUMN28",
        "COLUMN29",
        "COLUMN30",
        "IMPORT_DATE",
        "LEADTEMPID",
        "NAME",
        "POSITION",
        "ROW_ID"
    ];
}

/**
 * Load the data
 *
 * @param {String} pBinId the binary id for loading the binary
 * @param {String} pFieldSep the fieldSeparator 
 * @param {String} pFieldLimit the fieldLimit
 * @param {String} pRecordSep the recordSeparator
 * @param {String} pLeadImportId the leadimportid
 * @param {String} pUpdate checks if the Import fields should be deleted or not
 * @param {String} pNewFile checks if the Import fields should be deleted/updated or not
 *
 * @return {integer} rows the number of rows which have been inserted
 */
LeadImportUtils.loadImportFile = function(pBinId, pFieldSep, pFieldLimit, pRecordSep, pLeadImportId, pUpdate, pNewFile)
{
    var rows = 0;
    var toInsert = [];
    var insertTable = "IMPORTFIELD";
    var insertCols = ["IMPORTFIELDID", "FIELDNAME", "LEADIMPORT_ID", "DATE_NEW", "USER_NEW", "FIELDNUMBER"];
    var insertVals = [];
    var sentenceSeparator = ExportTemplateUtils.getSentenceSeparator(pRecordSep);
    var fieldSeparator = ExportTemplateUtils.getFieldSeparator(pFieldSep);
    var fieldDeLimiter = ExportTemplateUtils.getFieldDeLimiter(pFieldLimit);


    if (pUpdate == undefined) pUpdate = false;
    
    if (pBinId)
    {
        try
        {            
            var data = db.getBinaryContent(pBinId, SqlUtils.getBinariesAlias());
            data = util.decodeBase64String(data, "UTF-8");
            var table = text.parseCSV(data.replace(/(^\s+)|(\s+$)/g,""), sentenceSeparator, fieldSeparator, fieldDeLimiter);
            if (pNewFile)
            {
                if (pUpdate) {
                    db.deleteData("IMPORTFIELD", newWhere("IMPORTFIELD.LEADIMPORT_ID", pLeadImportId).build());
                }
                //insert the importfields
                for (let i = 0; i < table[0].length; i++)
                {
                    insertVals =  [util.getNewUUID(), table[0][i], pLeadImportId, vars.getString("$sys.date"), vars.getString("$sys.user"), i.toString()];
                    toInsert.push([insertTable, insertCols, null, insertVals]);
                }
                db.inserts(toInsert);
            }
            
            db.deleteData("LEADTEMP", newWhere("LEADTEMP.ROW_ID", pLeadImportId).build());//delete existing temp data
        }
        catch(ex)
        {
            logging.log(ex);
            question.showMessage(translate.text("Error when reading the file!"));
        }
    }
    return rows;
}

/**
 * processes an import record
 *
 * @param {Object} pDataFields Objekt von DBFelder
 * @param {Object} pDataTypes Objekt von DBTypes
 * @param {Object} pFieldDef Zuordnung der Importfelder
 * @param {Object} pFieldValues ImportWerte
 * @param {String} pImportDefID
 * @param {Object} pAttrObject
 * @param {String} pSource the importSource
 * @param {String} pUser the importuser
 * @param {String} pDate the importdate
 *
 * @return {Array} returns an array with the leadid in the first place, 
 *                 the personobject for duplicate checking in the second place 
 *                 and the organisationobject for duplicate checking in the third place
 * 
 */
LeadImportUtils.importData = function(pDataFields, pDataTypes, pFieldDef, pFieldValues, pImportDefID, pAttrObject, pSource, pUser, pDate)
{
    // set the mappings
    LeadImportUtils.mapping = LeadImportUtils.getMapping(pImportDefID);
    
    var LeadValues = LeadImportUtils.setValues(pDataFields["LEAD"], pFieldDef, pFieldValues); // set the Lead values
    
    var orgid = "";
    var persid = ""; 
    var persContactId = ""; 
    var orgContactId = ""; 
    var persObjID = ""; 
    var orgObjID = "";  
    var orgsForDubCheck = "";
    var persForDubCheck = "";
    var orgRet;
    var persRet;
    
    //------create organisation
    if (LeadValues["NAME"].trim() != "")//only if Organame is filled
    {
        orgObjID = "Organisation";//for attribute
        orgRet = LeadImportUtils.insertOrg(pDataFields, pDataTypes,  pFieldDef, pFieldValues, pUser, pDate);

        orgid = orgRet[0];
        orgContactId = orgRet[1];
        orgsForDubCheck = orgRet[2];

        if (orgRet[3])//not a new organisation
        {
            LeadImportUtils.insertAttr(pDataFields, pDataTypes, pFieldDef, LeadValues, orgContactId, orgObjID, pUser, pDate);
            LeadImportUtils.insertComm(pDataFields, pDataTypes, LeadValues, orgContactId, pUser, pDate, orgObjID);
        }
    }
    //------create person
    if (LeadValues["LASTNAME"].trim() != "")//only if lastname is filled
    {
        persObjID = "Person";//for attribute
        persRet = LeadImportUtils.insertPers(pDataFields, pDataTypes,  pFieldDef, pFieldValues, orgid, LeadValues, pUser, pDate);
        
        persid = persRet[0];
        persContactId = persRet[1];
        persForDubCheck = persRet[2];

        LeadImportUtils.insertAttr(pDataFields, pDataTypes, pFieldDef, LeadValues, persContactId, persObjID , pUser, pDate);
        LeadImportUtils.insertComm(pDataFields, pDataTypes, LeadValues, persContactId, pUser, pDate, persObjID);
    }
    //------create activitys for organisation and person
    var activityData = 
    {
        categoryKeywordId : $KeywordRegistry.activityCategory$mail(),
        directionKeywordId : $KeywordRegistry.activityDirection$outgoing(),
        subject : translate.text("Leadimport"),
        content : pSource
    };
    //  History for organisation and person
    if (orgContactId != "" && orgRet[3])
        ActivityUtils.insertNewActivity(activityData, new Array(new Array(orgObjID, orgContactId)),undefined, undefined, pDate);//org
    if (persContactId != "")
        ActivityUtils.insertNewActivity(activityData, new Array(new Array(persObjID, persContactId)), undefined, undefined, pDate);//pers
    
    LeadValues["LEADIMPORT_ID"] = pImportDefID;
    LeadValues["ORGANISATION_ID"] = orgid;
    LeadValues["PERSON_ID"] = persid;
    
    var leadId = LeadImportUtils.insertTable(pDataFields, pDataTypes ,LeadValues, "LEAD", pUser, pDate);//inserts the LEAD dataset

    LeadImportUtils.insertLeadAttr(pAttrObject, orgid, persid, LeadValues["DATE_NEW"], pUser, pDate, (orgRet && orgRet.length >= 4 && orgRet[3]));
    
    return [leadId, persForDubCheck, orgsForDubCheck];
}


/**
 * get all Mapping to a Leadimport
 *
 * @param {String} pleadimportId the ID of a Leadimport
 * 
 * @return {Object} an associative array in format arr[fieldname][inputvalue] --> outputvalue
 */
LeadImportUtils.getMapping = function(pleadimportId) 
{
    let tempData = {};
    newSelect(["LEADIMPORTMAPPINGASSISTANT.FIELDNAME", "LEADIMPORTMAPPINGASSISTANT.INPUTVALUE", "LEADIMPORTMAPPINGASSISTANT.OUTPUTVALUE"])
    .from("LEADIMPORTMAPPINGASSISTANT")
    .where("LEADIMPORTMAPPINGASSISTANT.LEADIMPORT_ID", pleadimportId)
    .table().forEach(function (d) {
        if (tempData[d[0]] == undefined	)
            tempData[d[0]] =  {}
        tempData[d[0]][d[1]] = d[2];
    });
    return tempData;
    
}

/**
 * adds two arrays
 *
 * @param {[]} pArray the first array
 * @param {[]} pAddArray the second array
 *
 * @return {[]} new Array
 */
LeadImportUtils.addArray = function(pArray, pAddArray)
{
    var NewArray = new Array()
    for(var i = 0; i < pArray.length; i++)
        NewArray.push(pArray[i]);
    NewArray.push(pAddArray);
    return NewArray;
}

/**
 * returns object with columns of the specified tables
 *
 * @param {[]} pDataTables Array of TableNames
 *
 * @return {Object} Object with columns
 */
LeadImportUtils.getDataFields = function(pDataTables)
{
    var DataFields = new Object();
    for (var i = 0; i < pDataTables.length; i++)
    {
        var FieldNames = db.getColumns(pDataTables[i], vars.getString("$sys.dbalias"));
        //capitalize all column names
        for(var f = 0; f < FieldNames.length; f++)
        {
            FieldNames[f] = FieldNames[f].toUpperCase();
        }
        DataFields[pDataTables[i]] = FieldNames;
    }
    return DataFields;
}

/**
 * returns object with column types of the specified tables
 *
 * @param {[]} pDataTables Array of TableNames
 * @param {[]} pDataFields Array of TableColumns
 *
 * @return {Object} object of the column types
 */
LeadImportUtils.getDataTypes = function(pDataFields, pDataTables)
{
    var DataTypes = new Object();
    for (var i = 0; i < pDataTables.length; i++)
    {
        DataTypes[pDataTables[i]] = db.getColumnTypes(pDataTables[i] , pDataFields[pDataTables[i]]);
    }
    return DataTypes;
}


/**
 * sets values for a table
 *
 * @param {String []} pFields fields
 * @param {String []} pFieldDef assignment of import fields
 * @param {String []} pFieldValues import values
 *
 * @return {Object} Object with the assigned values
 */
LeadImportUtils.setValues  = function(pFields, pFieldDef, pFieldValues)
{
    var dataValues = {};
    //  preset values
    for (let i = 0; i <  pFields.length; i++)
    {
        dataValues[pFields[i]] = "";
    }
    // set values
    for (let i = 0; i < pFieldDef.length; i++)
    {
        try
        {
            if (pFieldValues[ pFieldDef[i][0]] != undefined) 
            {
                if (LeadImportUtils.mapping != undefined && 
                    LeadImportUtils.mapping[pFieldDef[i][1]] != undefined && 
                    LeadImportUtils.mapping[pFieldDef[i][1]][pFieldValues[pFieldDef[i][0]]] != undefined) 
                {
                    // mapping is available and is set
                    pFieldValues[pFieldDef[i][0]] = LeadImportUtils.mapping[pFieldDef[i][1]][pFieldValues[pFieldDef[i][0]]];
                }
                    
                dataValues[pFieldDef[i][1]] = pFieldValues[pFieldDef[i][0]];
            }
        }
        catch(ex)
        {
            logging.log(ex, logging.WARNING);
        }
    }
    return dataValues;
}

/**
 * returns import fields
 *
 * @param {String} pID ImportID
 *
 * @return {[]} ImportFieldDef
 */
LeadImportUtils.getImportFieldDef = function(pID)
{
    var ImportFieldDef = newSelect(["distinct FIELDNUMBER", SqlMaskingUtils.prototype.trim("MAPPINGFIELD"), "''"])
                            .from("IMPORTFIELD")
                            .where("IMPORTFIELD.LEADIMPORT_ID", pID)
                            .and("MAPPINGFIELD is not null")
                            .orderBy("IMPORTFIELD.FIELDNUMBER asc")
                            .table();

    var FieldDef = new Array();
    for (let i = 0; i < ImportFieldDef.length; i++)
    {
        FieldDef[i] = new Array (ImportFieldDef[i][0], ImportFieldDef[i][1])
        // TODO: this could be done using the Keyword Attributes instead of doing cracy substinging etc...
        if (ImportFieldDef[i][1].substr(0, 9) == "ATTRIBUTE")//ATTRIBUTES are defined like ATTRIBUTE_Loyalty
        {
            var attrName = ImportFieldDef[i][1].substr(10);// so cut the first 10 characters off to get the ATTRIBUTENAME "Loyalty"
            var attrdata = newSelect("AB_ATTRIBUTEID, ATTRIBUTE_TYPE")
                                .from("AB_ATTRIBUTE")
                                .where("AB_ATTRIBUTE.ATTRIBUTE_NAME", attrName)
                                .arrayRow();
                
            var attrobj = newSelect("OBJECT_TYPE, MAX_COUNT")
                                .from("AB_ATTRIBUTEUSAGE")
                                .where("AB_ATTRIBUTEUSAGE.AB_ATTRIBUTE_ID", attrdata[0])
                                .table();
                
            var attrobject = new Object();
            for (let j = 0; j < attrobj.length; j++)	
            {
                attrobject[attrobj[j][0]] = attrobj[j][1];
            }
            FieldDef[i][2] = new Array(attrName, attrdata, attrobject);
        }
    }
    return FieldDef;
}

/**
 * Attribute anlegen.
 *
 * @param {Object} pDataFields Object of columns
 * @param {Object} pDataTypes Object of types
 * @param {[]} pFieldDef assignment of import fields
 * @param {[]} pLeadValues the lead values
 * @param {String} pContactId CONTACTID
 * @param {String} pObjectID ID of the object
 * @param {String} pUser the importuser
 * @param {String} pDate the importdate
 * 
 * @return {void}
 */
LeadImportUtils.insertAttr = function(pDataFields, pDataTypes, pFieldDef, pLeadValues, pContactId, pObjectID, pUser, pDate)
{
    var Fields = pDataFields["AB_ATTRIBUTERELATION"];

    // Datenwerte setzen
    for (var i = 0; i < pFieldDef.length; i++)
    {
        if (pFieldDef[i][1].substr(0, 9) == "ATTRIBUTE" && pFieldDef[i][2] != undefined)//if Attribute
        {
            var attrdata = pFieldDef[i][2];
            
            let countAttributes = newSelect("count(*)")
                        .from("AB_ATTRIBUTERELATION")
                        .where("AB_ATTRIBUTERELATION.OBJECT_ROWID", pContactId)
                        .and("AB_ATTRIBUTERELATION.AB_ATTRIBUTE_ID", attrdata[1][0])
                        .cell();

            if (attrdata[2][pObjectID] != undefined && countAttributes == 0)//e. g. if attrdata[2]["Organisation"] != undefined
            {
                var DataValues = new Object();
                var value = pLeadValues[pFieldDef[i][1]];
                var type = "";
                
                //  Preset data values
                for (var z = 0; z < Fields.length; z++)    DataValues[Fields[z]] = "";

                DataValues["DATE_NEW"] = pLeadValues["DATE_NEW"];
                DataValues["OBJECT_TYPE"] = pObjectID;
                DataValues["AB_ATTRIBUTE_ID"] = attrdata[1][0];
                DataValues["OBJECT_ROWID"] = pContactId;
                switch(attrdata[1][1].trim())//type
                {
                    case "COMBO":
                        value = newSelect("AB_ATTRIBUTEID")
                                    .from("AB_ATTRIBUTE")
                                    .where("AB_ATTRIBUTE.ATTRIBUTE_PARENT_ID", attrdata[1][0])
                                    .and("AB_ATTRIBUTE.ATTRIBUTE_NAME", value)
                                    .cell();

                        type = "ID_VALUE";
                        break;
                //other cases can be added here
                }
                if (value != "" && type != "")
                {
                    DataValues[type] = value;
                    LeadImportUtils.insertTable(pDataFields, pDataTypes, DataValues, "AB_ATTRIBUTERELATION", pUser, pDate);
                }
            }
        }
    }
}

/**
 * creates a communication
 *
 * @param {Object} pDataFields Object of columns
 * @param {Object} pDataTypes Object of types
 * @param {Object} pLeadValues the lead values
 * @param {String} contactId CONTACTID
 * @param {String} pUser the importuser
 * @param {String} pDate the importdate
 * @param {String} pObjectID ID of the object
 * 
 * @return {void}
 */
LeadImportUtils.insertComm = function(pDataFields, pDataTypes, pLeadValues, contactId, pUser, pDate, pObjectID)
{
    var Fields = pDataFields["COMMUNICATION"];
    var commMedium = KeywordUtils.getEntryNamesAndIdsByContainer("CommunicationMedium");

    var DataValues = new Object();
    //  Preset data values
    for (let i = 0; i <  Fields.length; i++)    DataValues[Fields[i]] = "";
    
    DataValues["CONTACT_ID"] = contactId;

    for (let i = 0; i < commMedium.length; i++)
    {
        var medium = commMedium[i][0].trim(); //e. g. COMMMOBIL, COMMMAIL
        let value = pLeadValues[pObjectID.charAt(0) + "_" + medium];

        if (value != undefined && value != "")
        {
            DataValues[ "MEDIUM_ID" ] = medium; //e. g. COMMMOBIL
            DataValues[ "ADDR" ] = value; //e. g. +49 123 45678900
            DataValues[ "ISSTANDARD" ] = 1; //insert only standard communication data
            DataValues[ "COMMUNICATIONID" ] = "";
            LeadImportUtils.insertTable(pDataFields, pDataTypes, DataValues, "COMMUNICATION", pUser, pDate);
        }
    }
}

/**
 * creates an organisation
 *
 * @param {Object} pDataFields Object of columns
 * @param {Object} pDataTypes Object of types
 * @param {Object} pFieldDef assignment of import fields
 * @param {Object} pFieldValues Object of values
 * @param {String} pUser the importuser
 * @param {String} pDate the importdate
 *
 * @return {String[]} [organisationId, contactId, duplicateMapping, true/false]
 */
LeadImportUtils.insertOrg = function(pDataFields, pDataTypes,  pFieldDef, pFieldValues, pUser, pDate)
{
    //Organisation
    var OrgValues = LeadImportUtils.setValues(pDataFields["ORGANISATION"], pFieldDef, pFieldValues);
    var entityFieldValues = [];
    var ids = LeadImportUtils.checkOrgDup(OrgValues, "ORGANISATION");//checks if there is already an organisation like this
    
    if (ids.length == 0)//there is no organisation like this
    {
        var orgid = LeadImportUtils.insertTable(pDataFields, pDataTypes, OrgValues, "ORGANISATION", pUser, pDate);
    
        //contact
        var ContactValues = LeadImportUtils.setValues(pDataFields["CONTACT"], pFieldDef, pFieldValues);
        ContactValues["STATUS"] = "CONTACTSTATACTIVE";
        ContactValues["ORGANISATION_ID"] = orgid;
        ContactValues["ADDRESS_ID"] = util.getNewUUID();
        var contactId =  LeadImportUtils.insertTable(pDataFields, pDataTypes, ContactValues, "CONTACT", pUser, pDate);
    
        //address
        var AddrValues = LeadImportUtils.setValues(pDataFields["ADDRESS"], pFieldDef, pFieldValues);

        AddrValues["ADDRESSID"] = ContactValues["ADDRESS_ID"];
        AddrValues["ADDR_TYPE"] = "OFFICEADDR";
        AddrValues["CONTACT_ID"] = contactId;
        LeadImportUtils.insertTable(pDataFields, pDataTypes, AddrValues, "ADDRESS", pUser, pDate);
    
        entityFieldValues = LeadImportUtils.getEntityFieldsOrg(OrgValues, ContactValues, AddrValues);
        
        //Activate the lead workflow example in the custom properties
        if(JSON.parse(project.getPreferenceValue("custom.workflow.leadExample", "false")))
        {
            var processVariables = {
                "OrganisationId": orgid,
                "targetId": contactId,
                "recipientContactId": contactId,
                "targetContext": "Organisation",
                "salesprojectName": OrgValues["NAME"],
                "documentTemplateId": "12da39a8-6fc9-4220-b09d-179cd611a814",
                "location": "https://www.adito.de/software/produkt-tour.html",
                "attributeName": ""
            }
            //start the example lead process instance for a new organisation lead
            workflow.startProcessByKey("leadprocess", processVariables)
        }
        
        return new Array(orgid, contactId ,entityFieldValues, true);
    }
    //return the ids of the existing organisation so that people who work for the same organisation are assigned to them
    //               OrgId  ContactID 
    return new Array(ids[0], ids[1] ,entityFieldValues, false);
}

/**
 * creates a person
 *
 * @param {Object} pDataFields Object of columns
 * @param {Object} pDataTypes Object of types
 * @param {Object} pFieldDef assignment of import fields
 * @param {Object} pFieldValues Object of values
 * @param {String} pOrgId the OrganisationId
 * @param {Object} pLeadValues the LeadValues
 * @param {String} pUser the importuser
 * @param {String} pDate the importdate
 *
 * @return {String []} (personId, contactId, duplicateMapping)
 */
LeadImportUtils.insertPers = function(pDataFields, pDataTypes,  pFieldDef, pFieldValues, pOrgId, pLeadValues, pUser, pDate)
{
    var isoLanguage = LeadImportUtils.checkISOLanguage(pLeadValues["ISOLANGUAGE"]);
    //Person
    var PersValues = LeadImportUtils.setValues(pDataFields["PERSON"], pFieldDef, pFieldValues);
    PersValues["TITLE"] = ContactInfoUtils.getTitle(pLeadValues, isoLanguage);
    PersValues["SALUTATION"] = ContactInfoUtils.getSalutation(pLeadValues, isoLanguage);
    PersValues["GENDER"] = ContactInfoUtils.getGender(pLeadValues);
    var persid = LeadImportUtils.insertTable(pDataFields, pDataTypes, PersValues, "PERSON", pUser, pDate);
    
    //contact
    var ContactValues = LeadImportUtils.setValues(pDataFields["CONTACT"], pFieldDef, pFieldValues);

    ContactValues["STATUS"] = "CONTACTSTATACTIVE";//only insert active persons
    ContactValues["PERSON_ID"] = persid;
    ContactValues["ADDRESS_ID"] = util.getNewUUID();
    ContactValues["DEPARTMENT"] = ContactInfoUtils.getContactDepartment(pLeadValues);
    ContactValues["CONTACTROLE"] = ContactInfoUtils.getContactRole(pLeadValues);
    ContactValues["CONTACTPOSITION"] = ContactInfoUtils.getContactPosition(pLeadValues);
    ContactValues["CONTACTPOSITION"] = ContactInfoUtils.getContactPosition(pLeadValues);
    
    if (pOrgId == "")//private
        ContactValues["ORGANISATION_ID"] = 0;
    else
        ContactValues["ORGANISATION_ID"] = pOrgId;
    
    var contactId =  LeadImportUtils.insertTable(pDataFields, pDataTypes, ContactValues, "CONTACT", pUser, pDate);
    
    //Address
    var AddrValues = LeadImportUtils.setValues(pDataFields["ADDRESS"], pFieldDef, pFieldValues);
    AddrValues["ADDRESSID"] = ContactValues["ADDRESS_ID"];
    AddrValues["ADDR_TYPE"] = "HOMEADDR";
    AddrValues["CONTACT_ID"] = contactId;
    LeadImportUtils.insertTable(pDataFields, pDataTypes, AddrValues, "ADDRESS", pUser, pDate);

    var entityFieldValues = LeadImportUtils.getEntityFieldsPers(PersValues, ContactValues, AddrValues);
    
    return new Array(persid, contactId, entityFieldValues);
}

/**
 * Inserts a dataset
 *
 * @param {Object} pDataFields Object of columns
 * @param {Object} pDataTypes Object of types
 * @param {Object} pValues Object of values
 * @param {String} pTable Table
 * @param {String} pUser the importuser
 * @param {String} pDate the importdate
 *
 * @return {String} Tableid
 */
LeadImportUtils.insertTable = function(pDataFields, pDataTypes, pValues, pTable, pUser, pDate)
{
    var Fields = pDataFields[pTable];
    var Types = pDataTypes[pTable];

    var TableValues = new Array();
    var TableID = pTable + "ID";

    if (pValues[TableID] == undefined || pValues[TableID] == "") 	
        pValues[TableID] = util.getNewUUID();
    
    pValues["USER_NEW"] = pUser;
    pValues["DATE_NEW"] = pDate;
    for (var i = 0; i < Fields.length; i++)
    {
        if (pValues[Fields[i]] != "" && Fields[i] != "DATE_NEW")
        {
            try
            {
                switch(String(Types[i]))
                {
                    // formatting of certain data types, e.g. Date
                    case String(SQLTYPES.DATE):
                    case String(SQLTYPES.TIMESTAMP):
                        pValues[Fields[i]] = datetime.toLong(pValues[Fields[i]], "dd.MM.yyyy");
                        break;
                    case String(SQLTYPES.DECIMAL):
                    case String(SQLTYPES.DOUBLE):
                    case String(SQLTYPES.FLOAT):
                        pValues[Fields[i]] = text.parseDouble(pValues[Fields[i]], "#.#");
                        break;
                }
            }catch(err)
            {
                logging.log(err, logging.WARNING);
            }
        }
        TableValues[i] = pValues[Fields[i]];
    }
    
    db.insertData(pTable, Fields, Types, TableValues);     
    
    return pValues[TableID];
}

/**
 * preparing the attributedata for insert
 *
 * @param {Object} pAttrObject the object for the attribute
 * @param {String} orgid the ORGANISATIONID
 * @param {String} persid the PERSONID
 * @param {String} pUser the importuser
 * @param {String} pDate the importdate
 * @param {Boolean} pInsertOrgAttr true/false checks if an orgAttr should be created or not
 *
 * @return {void}
 */
LeadImportUtils.insertLeadAttr  = function(pAttrObject, orgid, persid, pUser, pDate, pInsertOrgAttr)
{
    for (var i = 0; i < pAttrObject["Values"].length; i++)
    {
        let attrValues = pAttrObject["Values"][i];

        // Attribute for the Organisation
        if (orgid != "" && pInsertOrgAttr)
        {
            attrValues[0] = "Organisation";
            attrValues[7] = newSelect("CONTACTID")
                                .from("CONTACT")
                                .where("CONTACT.ORGANISATION_ID", orgid)
                                .and("CONTACT.PERSON_ID is null")
                                .cell();
                                
            LeadImportUtils.sqlInsertAttr(pAttrObject, attrValues, pUser, pDate);
        }
        // Attribute für the Person
        if (persid != "")
        {
            attrValues[0] = "Person";
            if (orgid == "")	orgid = "0";  // Private
            attrValues[7] = newSelect("CONTACT.CONTACTID")
                                .from("CONTACT")
                                .where("CONTACT.ORGANISATION_ID", orgid)
                                .and("CONTACT.PERSON_ID", persid)
                                .cell();

            LeadImportUtils.sqlInsertAttr(pAttrObject, attrValues, pUser, pDate);
        }
    }
}

/**
 * inserts an ATTRIBUTERELATION
 *
 * @param {Object} pAttrObject the object for the attribute
 * @param {[]} pAttrValues values for the attribute
 * @param {String} pUser the importuser
 * @param {String} pDate the importdate
 * 
 * @return {void}
 */
LeadImportUtils.sqlInsertAttr = function(pAttrObject, pAttrValues, pUser, pDate)
{
    pAttrValues[8] = util.getNewUUID();
    pAttrValues[9] = pUser;
    pAttrValues[10] = pDate;
    
    let countAttributes = newSelect("count(*)")
                        .from("AB_ATTRIBUTERELATION")
                        .where("AB_ATTRIBUTERELATION.OBJECT_ROWID", pAttrValues[7])
                        .and("AB_ATTRIBUTERELATION.AB_ATTRIBUTE_ID", pAttrValues[1])
                        .cell();
    if (countAttributes == 0) {
        db.insertData("AB_ATTRIBUTERELATION", pAttrObject["Fields"] , pAttrObject["Types"], pAttrValues);
    }
   
}

/**
 * returns the object for the leadattribute
 *
 * @param {String} pImportDefID the ID of the connected dataset
 *
 * @return {Object} object for the leadattribute
 */
LeadImportUtils.getLeadAttr = function(pImportDefID)
{
    let fields = ["OBJECT_TYPE", "AB_ATTRIBUTE_ID", "CHAR_VALUE","DATE_VALUE","NUMBER_VALUE", "INT_VALUE", "ID_VALUE", "OBJECT_ROWID", "AB_ATTRIBUTERELATIONID", "DATE_NEW", "USER_NEW"];
    return {
        Fields: fields,
        Types: db.getColumnTypes("AB_ATTRIBUTERELATION", fields),
        Values: newSelect("AB_ATTRIBUTERELATION.OBJECT_TYPE, AB_ATTRIBUTERELATION.AB_ATTRIBUTE_ID, CHAR_VALUE, DATE_VALUE, NUMBER_VALUE, INT_VALUE, ID_VALUE, -1, -1, '', ''")
                    .from("AB_ATTRIBUTERELATION")
                    .join("AB_ATTRIBUTEUSAGE", "AB_ATTRIBUTEUSAGE.AB_ATTRIBUTE_ID = AB_ATTRIBUTERELATION.AB_ATTRIBUTE_ID")
                    .where("AB_ATTRIBUTERELATION.OBJECT_ROWID", pImportDefID)
                    .and("AB_ATTRIBUTERELATION.OBJECT_TYPE = 'Leadimport'")
                    .table()
    };
}

/**
 * Checks if the COUNTRY is a valid one.
 * The value is checked against ISO2, ISO3 and NAME_LATIN
 * 
 * @param {String} pCountry the COUNTRY
 *
 * @return {String} validated COUNTRY as ISO2, Default is "DE".
 */
LeadImportUtils.checkCountry = function(pCountry)
{
    var iso2 = newSelect("MAX(ISO2)") // normally there should be only one so MAX is only to be safe here...
                    .from("AB_COUNTRYINFO")
                    .where("AB_COUNTRYINFO.ISO2", pCountry)
                    .or("AB_COUNTRYINFO.ISO3", pCountry)
                    .or("AB_COUNTRYINFO.NAME_LATIN", pCountry)
                    .cell();

    return iso2 ? iso2 : "DE";
}

/**
 * Checks if the LANGUAGE is a valid one.
 * The value is checked against ISO2, ISO3 and NAME_LATIN
 *  
 * @param {String} pLanguage the LANGUAGE
 *
 * @return {String} validated LANGUAGE as ISO3. Default is "deu".
 */
LeadImportUtils.checkISOLanguage = function(pLanguage)
{
    var iso3 = newSelect("MAX(ISO3)") // normally there should be only one so MAX is only to be safe here...
                    .from("AB_LANGUAGE")
                    .where("AB_LANGUAGE.ISO2", pLanguage)
                    .or("AB_LANGUAGE.ISO3", pLanguage)
                    .or("AB_LANGUAGE.NAME_LATIN", pLanguage)
                    .cell();
    
    return iso3 ? iso3 : "deu";
}


/**
 * mapping for the duplicate search for ORGANISATION
 * maps the DB-Field to the ENTITY-Field
 * 
 * @param {Object} pOrgDataFields the the ORGANISATION values; 
 * @param {Object} pContactDataFields the CONTACT values; 
 * @param {Object} pAddressDataFields the ADDRESS values; 
 * 
 * @return {Object} the mapping
 */
LeadImportUtils.getEntityFieldsOrg = function(pOrgDataFields, pContactDataFields, pAddressDataFields)
{
    var EntityOrgFieldObj = {};
    EntityOrgFieldObj["CONTACTID"] = pContactDataFields["CONTACTID"];
    EntityOrgFieldObj["CUSTOMERCODE"] = pOrgDataFields["CUSTOMERCODE"];
    EntityOrgFieldObj["NAME"] = pOrgDataFields["NAME"];
    EntityOrgFieldObj["INFO"] = pOrgDataFields["INFO"];
   
    return EntityOrgFieldObj;  
}

/**
 * mapping for the duplicate search for PERSON
 * maps the DB-Field to the ENTITY-Field
 * 
 * @param {Object} pPersDataFields the the PERSON values; 
 * @param {Object} pContactDataFields the CONTACT values; 
 * @param {Object} pAddressDataFields the ADDRESS values; 
 * 
 * @return {Object} the mapping
 */
LeadImportUtils.getEntityFieldsPers = function(pPersDataFields, pContactDataFields, pAddressDataFields)
{
    var EntityPersFieldObj = {};
    EntityPersFieldObj["CONTACTID"] = pContactDataFields["CONTACTID"];
    EntityPersFieldObj["FIRSTNAME"] = pPersDataFields["FIRSTNAME"];
    EntityPersFieldObj["LASTNAME"] = pPersDataFields["LASTNAME"];
    EntityPersFieldObj["SALUTATION"] = pPersDataFields["SALUTATION"];
    EntityPersFieldObj["TITLE"] = pPersDataFields["TITLE"];
    EntityPersFieldObj["DATEOFBIRTH"] = pPersDataFields["DATEOFBIRTH"];
    EntityPersFieldObj["GENDER"] = pPersDataFields["GENDER"];
   
    return EntityPersFieldObj;
}

/**
 * checks if there are dups for the LEAD and updates the status of it
 * 
 * @param {Object} pAllContactData the contactData; 
 * @return {void}
 */
LeadImportUtils.scanLeadDups = function(pAllContactData)
{
    var dupUpdateLeadTable = "LEAD";

    var leadID = "";
    var toUpdate = [];
    var updDupVals = [];
    var persObj = {};
    var orgObj = {};

    for(let pLeadID in pAllContactData)
    {
        var dupStatus = "NODUP";
        dupOrg = false;
        dupPers = false;
    
        leadID = pLeadID;
        persObj = pAllContactData[pLeadID][0];//personData
        orgObj = pAllContactData[pLeadID][1];//organisationData
        
        if (orgObj != undefined && Object.keys(orgObj).length > 0)//checks if there is an ORGANISATIONDUPLICATE
        {
            let scanResultsOrg = DuplicateScannerUtils.ScanForDuplicates("OrganisationDuplicates", "Organisation_entity", orgObj, null);
            if (scanResultsOrg != null && scanResultsOrg.length > 0)
            {
                let insertCountOrg = DuplicateScannerUtils.CacheNewScanResults(orgObj["CONTACTID"], scanResultsOrg, "Organisation_entity");
                dupOrg = true;
            }
        }
        if (persObj != undefined && Object.keys(persObj).length > 0)//checks if there is an PERSONDUPLICATE
        {
            let scanResultsPers = DuplicateScannerUtils.ScanForDuplicates("PersonDuplicates", "Person_entity", persObj, null);
            if (scanResultsPers != null && scanResultsPers.length > 0)
            {
                let insertCountPers = DuplicateScannerUtils.CacheNewScanResults(persObj["CONTACTID"], scanResultsPers, "Person_entity");
                dupPers = true;
            }
        }

        if (dupOrg && dupPers)
            dupStatus = "ORGPERSDUP";//organisation and person duplicate
        else if (dupOrg)
            dupStatus = "ORGDUP";//organisation duplicate
        else if (dupPers)
            dupStatus = "PERSDUP";//person duplicate
       
        updDupVals = [dupStatus];
    
        toUpdate.push([dupUpdateLeadTable, ["DUPSTATUS"], null, updDupVals, newWhere([dupUpdateLeadTable, "LEADID"], leadID).build()]);
    }
    db.updates(toUpdate);//update Leads with the new status
}

/**
 * Checks if there is already an ORGANISATION 
 * if there is one then return the ids of it
 * else return no ids
 *
 * @param {Object} pLeadValues values of the Lead
 * @return {[]} [ORGANISATIONID, CONTACTID]
 */
LeadImportUtils.checkOrgDup = function(pLeadValues)
{
    //  search whether the organisation already exists        
    var query = newSelect("ORGANISATIONID, CONTACTID")
                .from("ORGANISATION")
                .join("CONTACT", "ORGANISATIONID = CONTACT.ORGANISATION_ID")
                .join("ADDRESS", "ADDRESSID = ADDRESS_ID");
        
    var fields = ["ORGANISATION.NAME", "ADDRESS.COUNTRY", "ADDRESS.ADDRESS", "ADDRESS.CITY", "ADDRESS.ZIP", "ADDRESS.COUNTRY"];
    query.where("PERSON_ID is null");

    for (var i = 0; i < fields.length; i++)
    {
        var fieldVal = pLeadValues[fields[i].split(".")[1]];
        if (fieldVal !== undefined) {
            query.and(fields[i], fieldVal);
            query.and(fields[i] + " is not null");
        } 
        else
        {
            query.and(fields[i] + " is null");
        }
    }
    return query.arrayRow();
}

/**
 * loads the technical value defined for the given field separator
 *
 * @param {String} pKeyId
 * @return {String} the value
 * @exception if no technicalValue defined for the keyword
 */
LeadImportUtils.getFieldSeparator = function(pKeyId)
{
    var fieldSep = StringUtils.unescapeSlashes(KeywordUtils.getAttributeRelation(pKeyId, $KeywordRegistry.fieldSeparator(), "technicalValue", ""));
    
    if (fieldSep === "")
    {
       throw new Error("errror no keyword attribute \"technicalValue\" defined for keyword " + pKeyId);
    }
    
    return fieldSep;
}

/**
 * loads the technical value defined for the given field limit
 *
 * @param {String} pKeyId
 * @return {String} the value
 * @exception if no technicalValue defined for the keyword
 */
LeadImportUtils.getFieldLimit = function(pKeyId)
{
    var fieldLimit = StringUtils.unescapeSlashes(KeywordUtils.getAttributeRelation(pKeyId, $KeywordRegistry.fieldLimit(), "technicalValue", ""));
    
    if (fieldLimit === "")
    {
       throw new Error("errror no keyword attribute \"technicalValue\" defined for keyword " + pKeyId);
    }
    
    return fieldLimit;
}

/**
 * loads the technical value defined for the given record separator
 *
 * @param {String} pKeyId
 * @return {String} the value
 * @exception if no technicalValue defined for the keyword
 */
LeadImportUtils.getRecordSeparator = function(pKeyId)
{
    var recordSeparator = StringUtils.unescapeSlashes(KeywordUtils.getAttributeRelation(pKeyId, $KeywordRegistry.recordSeparator(), "technicalValue", ""));
    
    if (recordSeparator === "")
    {
       throw new Error("errror no keyword attribute \"technicalValue\" defined for keyword " + pKeyId);
    }
    
    return recordSeparator;
}

/**
 * a static Utility class for contact infos
 * 
 * @class
 */
function ContactInfoUtils() {}

/**
 * checks if there is a department for the contact
 * 
 * @param {Object} pLeadValues the leadValues; 
 * 
 * @return {String} the department
 */
ContactInfoUtils.getContactDepartment = function(pLeadValues)
{
    if (pLeadValues["DEPARTMENT"] != undefined && pLeadValues["DEPARTMENT"] != "")
    {
        var department = KeywordUtils.getEntryNamesByContainer("ContactDepartment");
    
        for (let i = 0; i < department.length; i++)
        {
            if (pLeadValues["DEPARTMENT"].trim() == department[i].trim())
                return department[i];
        }
    }
    return "";
};

/**
 * checks if there is a position for the contact
 * 
 * @param {Object} pLeadValues the leadValues; 
 * 
 * @return {String} the position
 */
ContactInfoUtils.getContactPosition = function(pLeadValues)
{   
    if (pLeadValues["CONTACTPOSITION"] != undefined && pLeadValues["CONTACTPOSITION"] != "")
    {
        var position = KeywordUtils.getEntryNamesByContainer("ContactPosition");

        for (let i = 0; i < position.length; i++)
        {
            if (pLeadValues["CONTACTPOSITION"].trim() == position[i].trim())
                return position[i];
        }
    }
    return "";
};

/**
 * checks if there is a contactRole for the contact
 * 
 * @param {Object} pLeadValues the leadValues; 
 * 
 * @return {String} the contactRole
 */
ContactInfoUtils.getContactRole = function(pLeadValues)
{
    if (pLeadValues["CONTACTROLE"] != undefined && pLeadValues["CONTACTROLE"] != "")
    {
        var role = KeywordUtils.getEntryNamesByContainer("ContactContactrole");
  
        for (let i = 0; i < role.length; i++)
        {
            if (pLeadValues["CONTACTROLE"].trim() == role[i].trim())
                return role[i];
        }
    }
    return "";
};

/**
 * checks if there is a gender for the person
 * 
 * @param {Object} pLeadValues the leadValues; 
 * 
 * @return {String} the gender
 */
ContactInfoUtils.getGender = function(pLeadValues)
{
    if (pLeadValues["GENDER"] != undefined && pLeadValues["GENDER"] != "")
    {
        var gender = KeywordUtils.getEntryNamesByContainer("PersonGender");

        for (let i = 0; i < gender.length; i++)
        {
            if (pLeadValues["GENDER"].trim() == gender[i].trim())
                return gender[i];
        }
    }
    return "";
};

/**
 * checks if there is a salutation for the person
 * 
 * @param {Object} pLeadValues the leadValues; 
 * @param {String} pIsoLanguage the IsoLanguage; 
 * 
 * @return {String} the salutation
 */
ContactInfoUtils.getSalutation = function(pLeadValues, pIsoLanguage)
{
    // TODO: use getRows
    if (pLeadValues["SALUTATION"] != undefined && pLeadValues["SALUTATION"] != "")
    {
        var salutation = newSelect("distinct SALUTATION")
                            .from("SALUTATION")
                            .where("SALUTATION.ISOLANGUAGE", pIsoLanguage)
                            .and("SALUTATION.SALUTATION is not null")
                            .arrayColumn();
    
        for (let i = 0; i < salutation.length; i++)
        {
            if (pLeadValues["SALUTATION"].trim() == salutation[i].trim())
                return salutation[i];
        }
    }
    return "";
};

/**
 * checks if there is a title for the person
 * 
 * @param {Object} pLeadValues the leadValues; 
 * @param {String} pIsoLanguage the IsoLanguage; 
 * 
 * @return {String} the title
 */
ContactInfoUtils.getTitle = function(pLeadValues, pIsoLanguage)
{
    // TODO: use getRows
    if (pLeadValues["TITLE"] != undefined && pLeadValues["TITLE"] != "")
    {
        var title = newSelect("distinct TITLE")
                            .from("SALUTATION")
                            .where("SALUTATION.ISOLANGUAGE", pIsoLanguage)
                            .and("SALUTATION.TITLE is not null")
                            .arrayColumn();
            
        for (let i = 0; i < title.length; i++)
        {
            if (pLeadValues["TITLE"].trim() == title[i].trim())
                return title[i];
        }
    }
    return "";
};