Skip to content
Snippets Groups Projects
Select Git revision
  • master default protected
  • KeywordMigration
  • upgrade/2021.2.0 protected
  • upgrade/2021.0.0 protected
4 results
Blame Permalink
process.js 48.14 KiB 
import("Keyword_lib");
import("Communication_lib");
import("system.fileIO");
import("system.SQLTYPES");
import("system.text");
import("system.db");
import("system.vars");
import("system.eMath");
import("system.util");
import("system.datetime");
import("system.logging");
import("Attribute_lib");
import("KeywordRegistry_basic");
import("Sql_lib");
import("Importer_lib");

// TODO: use SqlBuilder

/*
+---------------------------------------------------------------+
� toolkit methods for the import handler                �
� DO NOT TOUCH - use lib_importerCustomMappingFunctions �
+---------------------------------------------------------------+
 */

/*
 * Values of the mapping line:
 * Keyword req -- the column index with the new keyword value
 * Category req -- the keyword category name
 * (Container opt -- the keyword container (= the same as the keyword category name), for backwards compatibility only)
 *
 * @name iKeyword
 * @param {Object} pObject req the mapping line
 * @return {Boolean} true
 **/
function iKeyword (pObject) 
{ 
    if (!this.doIfCheck(pObject)) 
        return true;

    var keyword = this.InputRecord[pObject.Keyword];
    if (keyword == undefined) 
        keyword = this.resolveSymbol(pObject, pObject.Keyword);
    
    var keywordCategory = pObject.Category != undefined ? pObject.Category : pObject.Container;
    var category = this.InputRecord[keywordCategory];
    if (category == undefined)
        category = this.resolveSymbol(pObject, keywordCategory);

    if (!keyword || !category)
        return true;
    
    var dbAlias = this.Config.AliasTo;
    var categoryId = new SqlBuilder(dbAlias)
        .select(this.getColumnCase("ab_keyword_categoryid"))
        .from(this.getTableCase("ab_keyword_category"))
        .where([this.getTableCase("ab_keyword_category"), this.getColumnCase("name")], category)
        .cell();
    if (!categoryId)
    {
        categoryId = util.getNewUUID();
        var categoryColumns = [
            this.getColumnCase("ab_keyword_categoryid"),
            this.getColumnCase("name"),
            this.getColumnCase("sortingby"),
            this.getColumnCase("sortingdirection")
        ];
        var categoryValues = [categoryId, category, "0", "ASC"];
        this.insertData(this.getTableCase("ab_keyword_category"), categoryColumns, null, categoryValues, dbAlias);
    }
    
    var id = new SqlBuilder(dbAlias)
        .select(this.getColumnCase("keyid"))
        .from(this.getTableCase("ab_keyword_entry"))
        .where([this.getTableCase("ab_keyword_entry"), this.getColumnCase("ab_keyword_category_id")], categoryId)
        .and([this.getTableCase("ab_keyword_entry"), this.getColumnCase("title")], keyword)
        .cell();
    
    if (!id)
    {
        id = util.getNewUUID();
        var columns = [
            this.getColumnCase("ab_keyword_entryid"), 
            this.getColumnCase("keyid"), 
            this.getColumnCase("ab_keyword_category_id"),
            this.getColumnCase("container"),
            this.getColumnCase("title"), 
            this.getColumnCase("sorting"), 
            this.getColumnCase("isactive"), 
            this.getColumnCase("isessential")
        ]; 
        var sort = new SqlBuilder(dbAlias)
            .select("max(sorting)")
            .from(this.getTableCase("ab_keyword_entry"))
            .where([this.getTableCase("ab_keyword_entry"), this.getColumnCase("ab_keyword_category_id")], categoryId)
            .cell();
        sort = Number(sort) + 1; //if sort is empty, it will be set to 1 because Number("") is 0
        values = [
            id, 
            util.getNewUUID(), 
            categoryId, 
            category, 
            keyword, 
            sort.toString(), 
            "1", 
            "0"
        ];
        this.insertData(this.getTableCase("ab_keyword_entry"), columns, null, values, this.Config.AliasTo);
    }
    if (pObject.Target) // no target specified? just try to create a new keyword entry if necessary 
        this.setOutput(pObject, id);
    return true;
}

/*
 * Values of the mapping line:
 * attribute (required): The new attribute name.
 * attributeType (required): The type of the attribute e.g 'GROUP'.
 * objectType (optional): The type of the object (AB_ATTRIBUTEUSAGE).
 * objectId (optional): The OBJECT_ROWID for the object instance (AB_ATTRIBUTERELATION).
 * value (optional): The value for the object instance (AB_ATTRIBUTERELATION).
 *
 * @name iAttribute
 * @param {Object} pObject req the mapping line
 * @return {Boolean} true
 * */

function iAttribute(pObject) 
{
    if (!this.doIfCheck(pObject)) return true;

    var attribute = this.InputRecord[pObject.attribute];
    var attributeType = this.InputRecord[pObject.attributeType];
    var objectType = this.InputRecord[pObject.objectType];
    var objectId = this.InputRecord[pObject.objectId];
    var value = this.InputRecord[pObject.value];
    
    var alias = this.Config.AliasTo;
    var funcBuffer = this.FuncBuffer.iAttribute;
    var ab_attribute = this.getTableCase("ab_attribute");
    var ab_attributeId = this.getColumnCase("ab_attribute.ab_attributeid");
    var attribute_parent_id = this.getColumnCase("ab_attribute.attribute_parent_id");
    var attribute_name = this.getColumnCase("ab_attribute.attribute_name");
    var attribute_type = this.getColumnCase("ab_attribute.attribute_type");
    var attribute_active = this.getColumnCase("ab_attribute.attribute_active");
    var ab_attributeUsage = this.getTableCase("ab_attributeusage");
    var ab_attributeUsageId = this.getColumnCase("ab_attributeusage.ab_attributeusageid");
    var attrU_ab_attribute_id = this.getColumnCase("ab_attributeusage.ab_attribute_id");
    var attrU_object_type = this.getColumnCase("ab_attributeusage.object_type");
    var ab_attributeRelation = this.getTableCase("ab_attributerelation");
    var attrRel_ab_attribute_id = this.getColumnCase("ab_attributerelation.ab_attribute_id"); 
    var ab_attributeRelationId = this.getColumnCase("ab_attributerelation.ab_attributerelationid");
    var object_rowId = this.getColumnCase("ab_attributerelation.object_rowid");
    var attrRel_object_type = this.getColumnCase("ab_attributerelation.object_type")
    
    if(attribute == undefined) attribute = this.resolveSymbol(pObject, pObject.attribute);
    if(attributeType == undefined) attributeType = this.resolveSymbol(pObject, pObject.attributeType);
    if(objectType == undefined) objectType = this.resolveSymbol(pObject, pObject.objectType);
    if(objectId == undefined) objectId = this.resolveSymbol(pObject, pObject.objectId);
    if(value == undefined) value = this.resolveSymbol(pObject, pObject.value);
    
    if (!attribute || !attributeType) return true;
    attributeType = attributeType.toUpperCase();

    var valueColumn = "";
    var attributes = attribute.split(".");   
    var columns = [ab_attributeId, attribute_parent_id, attribute_name, attribute_type, attribute_active];
    var type = AttributeTypes.GROUP();

    if (AttributeTypes[attributeType] == undefined) 
    {
        this.writeLog(this.LogLevels.Error, "iAttribute: " + attributeType + " isn't a valid attribute type.");
        return false;
    }

    switch (attributeType) 
    {
        case AttributeTypes.COMBO():
            valueColumn = AttributeTypes[attributeType].databaseField;
            type = AttributeTypes.COMBO();
            break;
        default:
            valueColumn = AttributeTypes[attributeType].databaseField;
    }

    if (funcBuffer == undefined) funcBuffer = {childs: {}};
    var pathToFollow = funcBuffer;

    for (var i = 0; i < attributes.length; i++) 
    {    
        if (pathToFollow["childs"][attributes[i]] != undefined) var id = pathToFollow["childs"][attributes[i]]["id"];
        else 
        {
            pathToFollow["childs"][attributes[i]] = {id: id, childs: {}};
            
            if (i == 0) 
            {
                var parent = null;
                // select ab_attributeid from AB_ATTRIBUTE where ATTRIBUTE_NAME = 'Subordinate campaign of' and attribute_parent_id is null
                id = newSelect(ab_attributeId, alias).from(ab_attribute).where(attribute_name, attributes[i])
                        .and(attribute_parent_id + " is null").cell();
            } 
            else 
            {
                parent = pathToFollow["id"];
                id = newSelect(ab_attributeId, alias).from(ab_attribute).where(attribute_name, attributes[i])
                        .and(attribute_parent_id, parent).cell();
            }
            
            if (id == "" || id == null) 
            {
                id = util.getNewUUID();
                if (attributes.length == i+1) type = attributeType;
                
                //TODO: add insertNoWait to instantly add AB_ATTRIBUTE records; 
                //this ensures that nothing is in the funcBuffer that does not exist in the database
                //TODO: check: are COMOB-values added automatically?
                var values = [id, parent, attributes[i], type, "1"];
                
                if(parent == null)
                {
                    values = [id, attributes[i], type, "1"];
                    columns = [ab_attributeId, attribute_name, attribute_type, attribute_active];
                }
                
                this.insertData(ab_attribute, columns, null, values, alias);
            }   
            pathToFollow["childs"][attributes[i]]["id"] = id;   
        }
        pathToFollow = pathToFollow["childs"][attributes[i]];
    }
    
    if (objectType) 
    {
        var attributeId = id;
        id = newSelect(ab_attributeUsageId, alias).from(ab_attributeUsage).where(attrU_ab_attribute_id, attributeId)
                .and(attrU_object_type, objectType).cell();
        
        if (id == "" || id == null) 
        {
            columns = [ab_attributeUsageId, attrU_ab_attribute_id, attrU_object_type];
            values = [util.getNewUUID(), attributeId, objectType];
            this.insertData(ab_attributeUsage, columns, null, values, alias);
        } 
        
        if (value && objectId) 
        {
            var exisingAttrRelation = newSelect([ab_attributeRelationId, valueColumn], alias)
                .from(ab_attributeRelation)
                .where(attrRel_ab_attribute_id, attributeId)
                .and(object_rowId, objectId)
                .and(attrRel_object_type, objectType)
                .arrayRow();
             
            if (exisingAttrRelation.length == 0)
            {
                columns = [ab_attributeRelationId, attrRel_ab_attribute_id, attrRel_object_type, object_rowId, valueColumn];
                values = [util.getNewUUID(), attributeId, objectType, objectId, value];
                this.insertData(ab_attributeRelation, columns, null, values, alias);
            }
            else
            {
                var existingValue;
                [id, existingValue] = exisingAttrRelation;
                //new value has to differ from the old (existing) value to prevent unneccesary updates
                 if (this.Config.ImportCommand.indexOf("update") != -1 && value != null && value.toString() != existingValue)
                {
                    cond = ab_attributeRelationId + " = '" + id + "'";
                    this.updateData(ab_attributeRelation, [valueColumn], null, [value], cond, alias);
                }
            }
        }
    }    
    return true;
}

/*
 * Values of the mapping line:
 * - attribute (required): The column index with the new attribute value.
 * - attributeType (required): The type of the attribute.
 * - keywordCategory (required): The category name of the keyword.
 * - keyword (optional): A new keyword name or an existing KeyId (AB_KEYWORD_ATTRIBUTERELATION).
 * - value (optional): The value of the relation (AB_KEYWORD_ATTRIBUTERELATION).
 *
 * @name iKeywordAttribute
 * @param {Object} pObject (required) the mapping line.
 * @return {Boolean} true
 * */

function iKeywordAttribute (pObject) 
{
    if (!this.doIfCheck(pObject)) 
        return true;
    
    var keywordAttribute = this.InputRecord[pObject.attribute];
    if (keywordAttribute == undefined) 
        keywordAttribute = this.resolveSymbol(pObject, pObject.attribute);

    var keywordAttributeType = this.InputRecord[pObject.attributeType];
    if (keywordAttributeType == undefined) 
        keywordAttributeType = this.resolveSymbol(pObject, pObject.attributeType);

    var category = pObject.keywordCategory != undefined ? pObject.keywordCategory : pObject.keywordContainer;
    var keywordCategory = this.InputRecord[category];
    if (keywordCategory == undefined) 
        keywordCategory = this.resolveSymbol(pObject, category);
    
    var keyword = this.InputRecord[pObject.keyword];
    if (keyword == undefined) 
        keyword = this.resolveSymbol(pObject, pObject.keyword);
    
    var keywordAttrRelValue = this.InputRecord[pObject.keywordAttrRelValue];
    if (keywordAttrRelValue == undefined) 
        keywordAttrRelValue = this.resolveSymbol(pObject, pObject.keywordAttrRelValue);
    
    if (!keywordAttribute || !keywordCategory || !keywordAttributeType) 
        return true;
    keywordAttributeType = keywordAttributeType.toUpperCase();
    var dbAlias = this.Config.AliasTo;
    var valueColumn = "";
    
    switch (keywordAttributeType) 
    {
        case $KeywordRegistry.keywordAttributeType$char():
            valueColumn = this.getColumnCase("char_value");
            break;
        case $KeywordRegistry.keywordAttributeType$number():
            valueColumn = this.getColumnCase("number_value");           
            break;
        case $KeywordRegistry.keywordAttributeType$bool():
            valueColumn = this.getColumnCase("bool_value");         
            break;
        case $KeywordRegistry.keywordAttributeType$longChar():
            valueColumn = this.getColumnCase("long_char_value");
            break;
        default:
            return true;
    }
    
    var categoryId = new SqlBuilder(dbAlias)
        .select(this.getColumnCase("ab_keyword_categoryid"))
        .from(this.getTableCase("ab_keyword_category"))
        .where([this.getTableCase("ab_keyword_category"), this.getColumnCase("name")], keywordCategory)
        .cell();
    if (!categoryId)
    {
        categoryId = util.getNewUUID();
        var categoryColumns = [
            this.getColumnCase("ab_keyword_categoryid"),
            this.getColumnCase("name"),
            this.getColumnCase("sortingby"),
            this.getColumnCase("sortingdirection")
        ];
        var categoryValues = [categoryId, keywordCategory, "0", "ASC"];
        this.insertData(this.getTableCase("ab_keyword_category"), categoryColumns, null, categoryValues, dbAlias);
    }
    
    var attributeId = new SqlBuilder(dbAlias)
        .select(this.getColumnCase("ab_keyword_attributeid"))
        .from(this.getTableCase("ab_keyword_attribute"))
        .where([this.getTableCase("ab_keyword_attribute"), this.getColumnCase("name")], keywordAttribute)
        .and([this.getTableCase("ab_keyword_attribute"), this.getColumnCase("ab_keyword_category_id")], categoryId)
        .cell();
    
    // Creates the entry in AB_KEYWORD_ATTRIBUTE, case if it not exists.
    if (!attributeId) 
    {
        attributeId = util.getNewUUID();
        var attributeColumns = [
            this.getColumnCase("ab_keyword_attributeid"), 
            this.getColumnCase("ab_keyword_category_id"),
            this.getColumnCase("name"), 
            this.getColumnCase("container"), 
            this.getColumnCase("kind")
        ];
        var attributeValues = [
            attributeId, 
            categoryId,
            keywordAttribute, 
            keywordCategory, 
            keywordAttributeType
        ];
        this.insertData(this.getTableCase("ab_keyword_attribute"), attributeColumns, null, attributeValues, dbAlias);
    }
    
    // Creates the entry in AB_KEYWORD_ENTRY and AB_KEYWORD_ATTRIBUTERELATION, case if it not exists.
    if (keyword && keywordAttrRelValue) 
    {   
        var keywordId = new SqlBuilder(dbAlias)
            .select(this.getColumnCase("ab_keyword_entryid"))
            .from(this.getTableCase("ab_keyword_entry"))
            .where([this.getTableCase("ab_keyword_entry"), this.getColumnCase("keyid")], keyword)
            .cell();
        
        if (keywordId == "" || keywordId == null) 
        {
            keywordId = new SqlBuilder(dbAlias)
                .select(this.getColumnCase("keyid"))
                .from(this.getTableCase("ab_keyword_entry"))
                .where([this.getTableCase("ab_keyword_entry"), this.getColumnCase("ab_keyword_category_id")], categoryId)
                .and([this.getTableCase("ab_keyword_entry"), this.getColumnCase("title")], keyword)
                .cell();
            
            if (keywordId == "" || keywordId == null) {
                
                var keywordColumns = [
                    this.getColumnCase("ab_keyword_entryid"), 
                    this.getColumnCase("keyid"), 
                    this.getColumnCase("ab_keyword_category_id"),
                    this.getColumnCase("container"),
                    this.getColumnCase("title"), 
                    this.getColumnCase("sorting"), 
                    this.getColumnCase("isactive"),
                    this.getColumnCase("isessential")
                ]; 
                
                var sort = new SqlBuilder(dbAlias)
                    .select("max(sorting)")
                    .from(this.getTableCase("ab_keyword_entry"))
                    .where([this.getTableCase("ab_keyword_entry"), this.getColumnCase("ab_keyword_category_id")], categoryId)
                    .cell();
                sort = Number(sort) + 1; //if sort is empty, it will be set to 1 because Number("") is 0
                
                keywordId = util.getNewUUID();
                var keywordValues = [
                    keywordId, 
                    util.getNewUUID(), 
                    categoryId, 
                    keywordCategory, 
                    keyword, 
                    sort.toString(), 
                    "1", 
                    "0"
                ];
                
                this.insertData(this.getTableCase("ab_keyword_entry"), keywordColumns, null, keywordValues, this.Config.AliasTo);
            }
        }
        
        // Creates or updates the keyword attributerelation.
        id = new SqlBuilder(dbAlias)
            .select(this.getColumnCase("ab_keyword_attributerelationid"))
            .from(this.getTableCase("ab_keyword_attributerelation"))
            .where([this.getTableCase("ab_keyword_attributerelation"), this.getColumnCase("ab_keyword_entry_id")], keywordId)
            .and([this.getTableCase("ab_keyword_attributerelation"), this.getColumnCase("ab_keyword_attribute_id")], attributeId)
            .cell();
        
        if (id == "" || id == null) 
        {
            var attributeRelationColumns = [
                this.getColumnCase("ab_keyword_attributerelationid"), 
                this.getColumnCase("ab_keyword_entry_id"), 
                this.getColumnCase("ab_keyword_attribute_id"), 
                valueColumn
            ];
                    
            id = util.getNewUUID();
            this.insertData(this.getTableCase("ab_keyword_attributerelation"), attributeRelationColumns, null,
                [id, keywordId, attributeId, keywordAttrRelValue], this.Config.AliasTo);
        } 
        else 
        {
            if (this.Config.ImportCommand.includes("update")) 
            {
                cond = this.getColumnCase("ab_keyword_attributerelationid") + " = '" + id + "'";
                this.updateData(this.getTableCase("ab_keyword_attributerelation"), [valueColumn], null, [keywordAttrRelValue], cond, this.Config.AliasTo);
            }            
        }       
    } 
    return true;  
}

/*
 * Values of the mapping line:
 * Address req -- the address for the communication entry
 * Medium req -- the medium id
 * ContactID req -- the id of the entry in the contact table
 * Standard opt -- the standard value (boolean)
 *
 * @name iComm
 * @param {Object} pObject req the mapping line
 * @return {Boolean} true
 * */

function iComm (pObject) 
{
    if (!this.doIfCheck(pObject)) 
        return true;
    var address = this.InputRecord[pObject.Address];
    var medium = this.InputRecord[pObject.Medium];
    var contact = this.InputRecord[pObject.ContactID];
    var standard = "0";
    
    if (address == undefined) 
        address = this.resolveSymbol(pObject, pObject.Address);
    if (medium == undefined) 
        medium = this.resolveSymbol(pObject, pObject.Medium);
    if (contact == undefined) 
        contact = this.resolveSymbol(pObject, pObject.ContactID);
    if (pObject.Standard) 
        standard = "1";
    
    if (!address || !medium || !contact) 
        return true;
    
    const COMMUNICATION = this.getTableCase("communication");
    const COMMUNICATIONID = this.getColumnCase("communicationid");
    const OBJECT_ROWID = this.getColumnCase("object_rowid");
    const OBJECT_TYPE = this.getColumnCase("object_type");
    const ISSTANDARD = this.getColumnCase("isstandard");
    const MEDIUM_ID = this.getColumnCase("medium_id");
    const ADDR = this.getColumnCase("addr");
    
    var keywordAttr = new KeywordAttribute($KeywordRegistry.communicationMedium(), "category");
    mediumCategory = new SqlBuilder(this.Config.AliasTo)
        .select("AB_KEYWORD_ATTRIBUTERELATION." + keywordAttr.dbField)
        .from("AB_KEYWORD_ATTRIBUTERELATION")
        .join("AB_KEYWORD_ENTRY", "AB_KEYWORD_ATTRIBUTERELATION.AB_KEYWORD_ENTRY_ID = AB_KEYWORD_ENTRY.AB_KEYWORD_ENTRYID")
        .where("AB_KEYWORD_ATTRIBUTERELATION.AB_KEYWORD_ATTRIBUTE_ID", keywordAttr.id)
        .and("AB_KEYWORD_ENTRY.KEYID", medium)
        .cell();
    
    var sql = new SqlBuilder(this.Config.AliasTo)
        .select([COMMUNICATIONID, ADDR, MEDIUM_ID, ISSTANDARD])
        .from(COMMUNICATION)
        .where(COMMUNICATION + "." + OBJECT_ROWID, contact)
        .and(COMMUNICATION + "." + OBJECT_TYPE, "Contact")
        .and(COMMUNICATION + "." + MEDIUM_ID, CommUtil.getMediumIdsByCategory(mediumCategory), SqlBuilder.IN());
        
    var existingData = sql.table(null, null, this.getConfiguredTimeout());
    var hasStandardAddr = existingData.some(function (commData)
    {
        return commData[3] == "1"; //check if there is already a standard address for the medium category
    });
    if (hasStandardAddr)
    {
        standard = "0";
    }
    
    var existingComm = existingData.find(function (commData)
    {
        return commData[2] == medium; //check if communication with the same medium exists
    });
        
    if (!existingComm)
    {
        var columns = [COMMUNICATIONID, ADDR, MEDIUM_ID, OBJECT_ROWID, ISSTANDARD];
        this.insertData(COMMUNICATION, columns, null, [util.getNewUUID(), address, medium, contact, standard], this.Config.AliasTo);       
    }
    else
    {
        var existingId = existingComm[0];
        var existingAddress = existingComm[1];
        if (address != existingAddress)
        {
            var cond = COMMUNICATIONID + " = '" + existingId + "'";
            this.updateData(COMMUNICATION, [ADDR], null, [address], cond, this.Config.AliasTo);
        }
    }
    return true;
}

/*
 * Values of the mapping line:
 * contact (required): The id of the entry in the contact table.
 * restrictionMedium (required): The medium id.
 * restrictionReason (optional): The reason.
 * startDate (optional): The date when the restrictions begins.
 * involvedEmployee (optional): The contact which is responsible for the restriction.
 *
 * @name iCommRestriction
 * @param {Object} pObject (required): Associative Array with required information.
 * @return {Boolean} true
 * */

function iCommRestriction(pObject) 
{
    if (!this.doIfCheck(pObject)) return true;

    var restrictionContact = this.InputRecord[pObject.restrictionContact];
    var restrictionReason = this.InputRecord[pObject.restrictionReason];
    var restrictionMedium = this.InputRecord[pObject.restrictionMedium];
    var restrictionStartDate = this.InputRecord[pObject.restrictionStartDate];
    var restrictionInvolvedEmployee = this.InputRecord[pObject.restrictionInvolvedEmployee];
    
    if(restrictionContact == undefined) restrictionContact = this.resolveSymbol(pObject, pObject.restrictionContact);
    if(restrictionReason == undefined) restrictionReason = this.resolveSymbol(pObject, pObject.restrictionReason);
    if(restrictionReason == undefined || restrictionReason == null) restrictionReason = "NULL";
    if(restrictionMedium == undefined) restrictionMedium = this.resolveSymbol(pObject, pObject.restrictionMedium);
    if(restrictionStartDate == undefined) restrictionStartDate = this.resolveSymbol(pObject.restrictionStartDate);
    if(restrictionStartDate == undefined || restrictionStartDate == null) restrictionStartDate = "NULL";
    if(restrictionInvolvedEmployee == undefined) restrictionInvolvedEmployee = this.resolveSymbol(pObject.restrictionInvolvedEmployee);
    if(restrictionInvolvedEmployee == undefined || restrictionInvolvedEmployee == null) restrictionInvolvedEmployee = "NULL";
    
    if (!restrictionMedium || !restrictionContact) return true;

    var alias = this.Config.AliasTo;
    var commRestriction = this.getTableCase("commrestriction");
    var commRestrictionId = this.getColumnCase("commrestriction.commrestrictionid");
    var contactId = this.getColumnCase("commrestriction.contact_id");
    var medium = this.getColumnCase("commrestriction.medium");
    var reason = this.getColumnCase("commrestriction.reason");
    var startDate = this.getColumnCase("commrestriction.startdate");
    var involvedEmployee = this.getColumnCase("commrestriction.employee_involved");

    var id = newSelect(commRestrictionId, alias).from(commRestriction).where(contactId, restrictionContact).and(medium, restrictionMedium).cell();
    var columns = [commRestrictionId, medium, reason, contactId, startDate, involvedEmployee];
    var values = [id, restrictionMedium, restrictionReason, restrictionContact, restrictionStartDate, restrictionInvolvedEmployee];
    
    if (id == "" || id == null) 
    {
        values[0] = util.getNewUUID();
        this.insertData(commRestriction, columns, null, values, alias);       
    }
    else
    {
        this.updateData(commRestriction, columns, null, values, alias);
    }
    
    return true;
}

/*
 * Values of the mapping line:
 * ActivityID req -- the column specifier for the activity table
 * OID req -- the id for a default object for object_rowid
 * OType req -- the context name
 *
 * @name iActivityLink
 * @param {Object} pObject req the mapping line
 * @return {Boolean} true
 * */

function iActivityLink(pObject) 
{
    if (!this.doIfCheck(pObject))
        return true;
 
    var activityId = this.InputRecord[pObject.ActivityID];
    
    if(activityId == undefined) 
        activityId = this.resolveSymbol(pObject, pObject.ActivityID);
    
    var objectType = this.InputRecord[pObject.OType];
    
    if(objectType == undefined) 
        objectType = this.resolveSymbol(pObject, pObject.OType);
    
    var objectId = this.InputRecord[pObject.OID];
    
    if(objectId == undefined) 
        objectId = this.resolveSymbol(pObject, pObject.OID);
    
    if (!activityId || !objectId || !objectType) 
        return true; 
    
    var columnCaseActivityLinkId = this.getColumnCase("activitylinkid");
    var tableCaseActivityLink = this.getTableCase("activitylink");
    var columnCaseActivityId = this.getColumnCase("activity_id");
    var columnCaseObjectType = this.getColumnCase("object_type");
    var columnCaseObjectRowId = this.getColumnCase("object_rowid");
    var configAliasTo = this.Config.AliasTo;
       
    var sql = "select " + columnCaseActivityLinkId
    +" from " + tableCaseActivityLink
    + " where " + columnCaseActivityId
    + " = ? and " + columnCaseObjectType
    + " = ? and " + columnCaseObjectRowId
    + " = ?";
    
    var id = db.cell([sql, [[activityId, SQLTYPES.VARCHAR], [objectType, SQLTYPES.VARCHAR], [objectId, SQLTYPES.CHAR]]], configAliasTo);
    
    if (id == "" || id == null) {
        var columns = [columnCaseActivityLinkId, columnCaseActivityId, columnCaseObjectType, columnCaseObjectRowId];
        this.insertData(tableCaseActivityLink, columns, null, [util.getNewUUID(), activityId, objectType, objectId], configAliasTo);
    }
    
    return true;
}

/*
 * imports an document from a given path
 *
 * @param {Object} pObject req the mapping line
 *
 * @return {Boolean} true, if import of the data was successful, otherwise false
 */
function iDocumentByPath(pObject){
    var resultDocument = true;
    if (! this.doIfCheck(pObject)) return true;

    try {
        if(pObject.Rowid != "" && pObject.Filename != "") {
            if(pObject.Value && pObject.Rowid) {          
                var wert = this.resolveSymbol(pObject, pObject.Value);
                var row = this.resolveSymbol(pObject, pObject.Rowid);
                var dateNew = this.resolveSymbol(pObject, pObject.DateNew);           
                var filename = this.InputRecord[pObject.Filename];
                var data = fileIO.getData(wert, util.DATA_BINARY);
                var length = fileIO.getLength(wert);
                logging.log(filename + " " + dateNew)
            }       
            var sql = "select count(" + this.getColumnCase("row_id") + ") from " + this.getTableCase("asys_binaries") 
            + " where " + this.getColumnCase("row_id") + " = ? and " + this.getColumnCase("filename") + " = ?";
            var count = db.cell([sql, [[row, SQLTYPES.CHAR], [filename, SQLTYPES.VARCHAR]]], this.Config. AliasSys);
            if(count == 0) {            
                var cols = [this.getColumnCase("Id"), this.getColumnCase("Tablename"), this.getColumnCase("Datasize"),
                this.getColumnCase("date_new"), this.getColumnCase("date_edit"), this.getColumnCase("user_new"),
                this.getColumnCase("bindata"), this.getColumnCase("containername"), this.getColumnCase("filename"),
                this.getColumnCase("row_id"), this.getColumnCase("mimetype")];
                var vals = [util.getNewUUID(), "$!GENERIC!$", length, dateNew, dateNew, vars.getString("$sys.user"), data, 
                "DOCUMENT", filename, row, util.getMimeType(filename)];
                db.insertData(this.getTableCase("asys_binaries"), cols, null, vals, this.Config. AliasSys);
            }
        }
    } catch(ex) {
        logging.log("Datei nicht gefunden!");
        resultDocument = false;
    }
    return resultDocument;
}

/*
 * imports an document
 * draft: Container: "string", Row: "TBL.COLID", Source: index, Filename: index, Tablename: "string",
 * Description: "string", Keywords: "string"
 *
 * @param {Object} pObject req the mapping line
 *
 * @return {Boolean} true, if import of the data was successful, otherwise false
 */
function iDocument(pObject)
{
    var resultDocument = true;

    //is any DoIf-condition set?
    if (! this.doIfCheck(pObject))
        return true;

    // iDocument is insert-only
    this.setDefaultAction(pObject);
    if(pObject.Action != "I") return resultDocument;

    try
    {
        var desc = "";
        if(pObject.Description != undefined) desc = this.InputRecord[pObject.Description];
        var keyw = "";
        if(pObject.Keywords != undefined) keyw = this.InputRecord[pObject.Keywords];

        if(pObject.Rowid != "" && pObject.Filename != "")
            db.insertBinary(
                pObject.Tablename, 
                pObject.Container, 
                this.getOutput(pObject, "Rowid"), 
                null, 
                this.InputRecord[pObject.Source], 
                this.InputRecord[pObject.Filename], 
                desc, 
                keyw, 
                this.Config.AliasTo);
    }
    catch(ex)
    {
        logging.log(ex);
        resultDocument = false;
    }

    return resultDocument;
}

/*
 * move import data to target
 *
 * @param {Object} pObject req the mapping line
 *
 * @example: [iMove, { Source: 3, Target: "RELATION.ADDRESS" } ]
 *
 * @return {Boolean} false, if the import of the row is not possible. otherwise true
 */
function iMove(pObject)
{
    var resultMove = true;

    //is any DoIf-condition set?
    if (! this.doIfCheck(pObject))
        return true;

    if(pObject.Blobfile != undefined && pObject.Blobfile == true)  // blobfile move
    {
        var pn = pObject.Pathname;
        var fn = this.InputRecord[pObject.Source];

        // s will be NULL is something went wrong (no file, access error, etc)
        var s = this.getFileContent(pn.toString() + fn.toString(), util.DATA_TEXT);

        // if blob file could be read, assign to output buffer,
        // otherweise signal "no import for this row" by returning false as the function value
        if(s != null && s != undefined)
            this.setOutput(pObject, s);
        else
            resultMove = false;
    }
    else  // no blob file handling, just plan old move
    {
        var expr = "";
        if(pObject.Source != undefined) expr = this.InputRecord[pObject.Source];
        if(pObject.Value != undefined) expr = this.resolveSymbol(pObject, pObject.Value, pObject.Eval);
        if(pObject.Map != undefined && pObject.Index) expr = pObject.Map[this.resolveSymbol(pObject, pObject.Index, pObject.Eval)];

        //if expr is undefined, then do no replace
        if(expr != undefined)
        {
            // check for trimming option
            if(pObject.Trim != undefined && typeof(pObject.Trim) == "string")
            {
                switch(pObject.Trim.toLowerCase())
                {
                    case "left":
                        expr = expr.replace(/^\s+/, "");
                        break;
                    case "right":
                        expr = expr.replace(/\s+$/, "");
                        break;
                    case "both":
                        expr = expr.replace(/^\s+|\s+$/g, "");
                        break;
                }
            }

            // chek for replacing option
            if(pObject.Replace != undefined && typeof(pObject.Replace) == "string" && pObject.ReplaceTo != undefined)
                expr = expr.replace(pObject.Replace, pObject.ReplaceTo);

            // check for format conversion
            if(pObject.HTML2Text)
                expr = text.html2text(expr);
            else if (pObject.RTF2Text)
                expr = text.rtf2text(expr);
        }
        else
            expr = "";

        this.setOutput(pObject, expr);
    }

    return resultMove;
}


/*
 * Return word number "Index" from source column.
 *    Values of the mapping line:
 *    String Source the source column index
 *    String Regex the regular expression for the split
 *    Number Index the word number starting with 0
 *    String Substring "right" or "left"
 *    String Separator concatenation string, default is blank
 *
 * @param {Object} pObject req the mapping line
 *
 * @return {Boolean} true
 */
function iWord(pObject)
{
    var resultWord = true;

    //is any DoIf-condition set?
    if (! this.doIfCheck(pObject))
        return true;

    var mode = pObject.Substring;
    var sep = pObject.Separator;
    if(sep == undefined) sep = " ";  // default concat with blank

    // split the input string with the regex and get the word number,
    // negative values will count from the end of the string (e.g. -1 for the last word in a string)
    if(pObject.Source != undefined) s = this.InputRecord[pObject.Source];
    if(pObject.Value != undefined) s = this.resolveSymbol(pObject, pObject.Value);

    s = s.split( pObject.Regex );
    var len = s.length;
    var num = Number(pObject.Index);
    if(num < 0) num = len - eMath.absInt(num);

    // just to be sure we are in a valid range
    if((num >= 0) && (num < len))
    {
        if(mode != undefined)
        {
            var part = "";
            // concatenate up the word
            mode = mode.toString().toLowerCase();
            if(mode == "left")
            {
                num++;
                part = s.slice(0,num).join(sep);
            }
            else if(mode == "right")
            {
                part = s.slice(pObject.Index).join(sep);
            }
            this.setOutput(pObject, part);
        }
        else
        {
            // use the single word
            this.setOutput(pObject, s[num]);
        }
    }

    if(resultWord == undefined) resultWord = "";
    return resultWord;
}


/*
 * return a new ID for a key field
 *			 	Value of the mapping line:
 *				String pColumn req the key column
 *
 * @param {Object} pObject req the mapping line
 *
 * @return {Boolean} true
 */
function iNewID(pObject)
{
    //is any DoIf-condition set?
    if (! this.doIfCheck(pObject))
        return true;

    if (pObject.Action == undefined)
    {
        pObject.Action = "I";
    }

    this.setOutput(pObject, util.getNewUUID());
    return true;
}


/*
 * join the list of columns into the specified target column
 * Values of the mapping line:
 *			Array pList req array containing result set indexes with joinable columns
 *			String pDelimiter req the delimiter string
 *			String pColumn req target column name
 *
 * @param {Object} pObject req the mapping line
 *
 * @example1: [iJoin, {Source: [3, 5], Delimiter: "\n", Target: "RELATION.ADDRESS"}]
 * @example2: [iJoin, {Value: ["{3}", "{5}"], Delimiter: "\n", Target: "RELATION.ADDRESS"}]
 *
 * @return {Boolean} true
 */
function iJoin(pObject)
{
    var s = "";
    var len;

    //is any DoIf-condition set?
    if (! this.doIfCheck(pObject))
        return true;

    if(pObject.Source != undefined)
        len = pObject.Source.length;
    if(pObject.Value != undefined)
        len = pObject.Value.length;

    for(var i=0; i < len; i++)
    {
        if (pObject.Source != undefined)
            if(this.InputRecord[pObject.Source[i]] != "")
            {
                if(i > 0 ) s += pObject.Delimiter;
                s += this.InputRecord[pObject.Source[i]];
            }

        if(pObject.Value != undefined)
            if(this.resolveSymbol(pObject, pObject.Value[i]) != "")
            {
                if(i > 0 ) s += pObject.Delimiter;
                s += this.resolveSymbol(pObject, pObject.Value[i]);
            }
    }

    this.setOutput(pObject, s);


    return true;
}

/*
 * executes an sql statement with the data from input result set column in pIndex
 * Values of the mapping line:
 * Number pIndex req the index into the input result set
 * String Command req the sql command (use {0}..{n} to specify source indexes)
 * String Alias req the alias name
 * String Target req the target column
 *
 * @param {Object} pObject req the mapping line
 *
 * @return {Boolean} true
 */
function iSql(pObject)
{
    //is any DoIf-condition set?
    if (! this.doIfCheck(pObject))
        return true;

    var sql = this.resolveSymbol(pObject, pObject.Command);

    if (pObject.Target != undefined)
        this.setOutput(pObject, db.cell(sql, pObject.Alias));
    else
        db.cell(sql, pObject.Alias);


    return true;
}


/*
 * inserts or updates an relation entry
 *
 * @param {Object} pObject req the mapping line
 *
 * @example: [iInsertUpdate, { Table: "RELATION", Alias: "AO_DATEN",
 *                      			Columns: ( {Name: "RELATIONID", Source: 4, Required: true },
 *                            			     {Name: "AOTYPE", Value: "2" },
 *                                 			 {Name: "PERS_ID", Column: "PERS.PERSID" })  } ]
 *
 * @return {Boolean} true, if insert and update are successful, otherwise false
 */
function iInsertUpdate(pObject)
{
    var resultUpdate = true;
    //is any DoIf-condition set?
    if (! this.doIfCheck(pObject))
        return true;

    this.setDefaultAction(pObject);

    try
    {
        var spalten = [];
        var typen = [];
        var werte = [];
        var coldef;
        var data_ok = true; // be optimistic ...
        var alias = this.Config.AliasTo;
        var tableName = this.getTableCase(pObject.Table);
        var condition = this.resolveSymbol(pObject, pObject.Condition);

        if(pObject.Action == undefined)  // set reasonable defaults for Action, if not specified
        {
            if(this.Config.ImportCommand == "insert")
                pObject.Action = "I";
            else if(this.Config.ImportCommand == "update")
                pObject.Action = "U";
            else if(this.Config.ImportCommand == "insert+update")
                pObject.Action = "I+U";
        }

        if(this.Config.ImportCommand == "insert+update")
        {
            if(pObject.Action == "I")
                action = "insert";
            else if (pObject.Action == "U")
                action = "update";
            else if(pObject.Action == "I+U")
            {
                //try to find an existing entry
                var entryid = db.cell("select count(*) from " + tableName + " where " + condition, alias);

                if(Number(entryid) > 0)
                {
                    //exist, do update
                    action = "update";
                }
                else
                {
                    //no entry, do insert
                    action = "insert";
                }
            }
        }
        else if (this.Config.ImportCommand == "update")
        {
            action = "update";
        }
        else
        {
            action = "insert";
        }

        // loop thru the column definitions
        for(var i=0; i < pObject.Columns.length; i++)
        {
            var value = undefined;
            coldef = pObject.Columns[i];

            //be sure, that no keycolumn is pushed in the array, when action like insert
            if(coldef.Key != true || (coldef.Key == true && action == "insert")) spalten.push(this.getColumnCase(coldef.Name));
            if(coldef.Key != true || (coldef.Key == true && action == "insert")) typen.push( this.DataType[tableName][this.getColumnCase(coldef.Name)] );

            if(value == undefined && coldef.Source != undefined) value = this.InputRecord[coldef.Source];
            if(value == undefined && coldef.Value != undefined) value = this.resolveSymbol(coldef, coldef.Value, coldef.Eval);
            if(value == undefined && coldef.Key == true && action == "insert") value = util.getNewUUID();

            //value undefined should not be pushed
            //only add value if column was pushed
            if(value != undefined && (coldef.Key != true || (coldef.Key == true && action == "insert"))) werte.push(value);

            // do not update data if any required field is empty
            if(coldef.Required == true && (value == undefined || value == "")) data_ok = false;
        }

        if(data_ok == true)
        {
            switch(action)
            {
                case "insert":
                    this.insertData(tableName, spalten, typen, werte, alias);
                    break;
                case "update":
                    this.updateData(tableName, spalten, typen, werte, condition, alias);
                    break;
            }
        }
    }
    catch(ex)
    {
        logging.log(ex);
        resultUpdate = false;
    }

    return resultUpdate;
}

/*
 * import a timestamp string in a specified format into a date field
 *    Values of the mapping line:
 *    String Source req the column index for the current record
 *    String Target req target column name
 *    String Format opt The timestamp format, default is YYYY-MM-DD HH:MI:SS
 *    String Timezone opt The timezone string, default is UTC
 *
 * @param {Object} pObject req the mapping line
 *
 * @return {Boolean} true, if the import of the timestamp was successfull, otherwise false
 */
function iTimestamp(pObject)
{
    var resultTimestamp = true;

    //is any DoIf-condition set?
    if (! this.doIfCheck(pObject))
        return true;

    var fmt = pObject.Format;
    var tz = pObject.Timezone;
    if(fmt == undefined || fmt == "") fmt = "yyyy-MM-dd HH:mm:ss";
    if(tz == undefined || tz == "") tz = "UTC";

    var value = "";
    if(pObject.Source != undefined) value = this.InputRecord[pObject.Source];
    if(pObject.Value != undefined) value = this.resolveSymbol(pObject, pObject.Value);
    try
    {
        this.setOutput(pObject, datetime.toLong(value, fmt, tz));
    }
    catch(ex)
    {
        logging.log(ex);
        resultTimestamp = false;
    }

    return resultTimestamp;
}


/*
 * decode an input entry by searching thru a translation list
 *    Values of the mapping line:
 *    String Value -- the input data
 *    String Target -- the target column
 *    String List -- the decode list, format: data;replacement;data;replacement.....
 *
 * @param {Object} pObject req the mapping line
 *
 * @return {Boolean} true, if the the decoding was successfull, otherwise false
 */
function iDecode(pObject)
{
    var resultDecode = true;

    //is any DoIf-condition set?
    if (! this.doIfCheck(pObject))
        return true;

    var wert = "";
    if(pObject.Source != undefined) wert = this.InputRecord[pObject.Source];
    if(pObject.Value != undefined) wert = this.resolveSymbol(pObject, pObject.Value);

    var list = pObject.List;
    var map = new Object();
    if(list != undefined)
    {
        // convert decode list into map
        list = list.split(";");

        //is the list complete?
        if(list.length % 2 == 0)
        {
            for(var i=0; i < list.length; i=i+2)
            {
                map[list[i]] = list[i+1];
            }

            // use map entry to decode
            if(wert != "") wert = map[wert];

            //if not found, set default to empty
            if(wert == undefined) wert = "";
        }
        else
        {
            //list is not correct, so wert = "" and log error message
            wert = "";
            this.writeLog(this.LogLevels.Error, "[iDecode] List is not correct!");
        }

        // write to output buffer
        this.setOutput(pObject, wert);
    }
    else
    {
        resultDecode = false;
    }

    return resultDecode;
}


/*
 * save an input in a globalvar
 *    Values of the mapping line:
 *    String Value -- the input data
 *    String Name -- the name for the globalvar
 *
 * @param {Object} pObject req the mapping line
 *
 * @example [(iGlobalVar {Value: "{3}", Name: "importLogin"} )     -->  $global.importLogin]
 *
 * @return {Boolean}
 */
function iGlobalVar(pObject)
{
    //is any DoIf-condition set?
    if (! this.doIfCheck(pObject))
        return true;

    var value = "";
    var name = "";
    if(pObject.Source != undefined) value = this.InputRecord[pObject.Source];
    if(pObject.Value != undefined) value = this.resolveSymbol(pObject, pObject.Value);
    if(pObject.Name != undefined) name = pObject.Name;
    vars.getString("$global." + name, value);
    
    return false;
}



/*
 * do character set translation.
 * basically works like iMove, but allows to specify a conversion map
 * that will be used to process the input data.
 * conversion map is a map (directionary, associative array, whatever you call it).
 * declare a varaible like theMap = new Array(); theMap("a") = "X"; theMap("b") = "z"; etc. ...
 * and specify this a sthe value for the Parameter "Map"
 *
 * Important! Usage of "Method" parameter value "replaceall" requires ADITO online 3.0.3 or above!
 *
 * Values of the mapping line:
 * String Value -- the input data
 * String Target -- the target column
 * String Map -- the decode map
 * String Method -- which Method to use: "js", "replaceall" (default to "js")]
 *
 * @param {Object} pObject req the mapping line
 *
 * @return {Boolean}
 */
function iCharMap(pObject)
{
    var resultMap = true;

    //is any DoIf-condition set?
    if (! this.doIfCheck(pObject))
        return true;

    var wert = "";
    if(pObject.Source != undefined) wert = this.InputRecord[pObject.Source];
    if(pObject.Value != undefined) wert = this.resolveSymbol(pObject, pObject.Value);

    var map = pObject.Map;

    if(map != undefined)
    {
        if(pObject.Method == undefined) pObject.Method = "js";  // default to JavaScript
        this.writeLog(this.LogLevels.Debug, "[iCharMap] Using mapping method '" + pObject.Method + "' for mapping in iCharMap");

        switch(pObject.Method)
        {
            case "js" :
                for (var i in map)
                {
                    wert = wert.replace(new RegExp(i, "gi"), map[i]);
                }
                break;

            case "replaceall" :
                wert = text.replaceAll(wert, map);
                break;
        }

        // write to output buffer
        this.setOutput(pObject, wert);

    }
    else
    {
        this.writeLog(this.LogLevels.Warning, "[iCharMap] Map for iCharMap missing or undefined/empty");
        resultMap = false;
    }

    return resultMap;
}