process.js

import("KeywordRegistry_basic");
import("Keyword_lib");
import("system.datetime");
import("system.translate");
import("system.neon");
import("system.vars");
import("system.result");
import("system.db");
import("Sql_lib");
import("Util_lib");
import("Context_lib");

/**
 * a static Utility class for organisations
 * 
 * Do not create an instance of this!
 * @class
 */
function OrganisationUtils() {}//TODO: there exsits a OrgUtils and OrganisationUtils, this is inconvenient

/*
 * retrieves the name of an organisation with a select statement
 * 
 * @param {String} pOrganisationId the ID of the organisation that shall be searched in the database; 
 * 
 * @return {String} the name of the organisation
 */
OrganisationUtils.getNameByOrganisationId = function(pOrganisationId)
{
    var orgname = newSelect("ORGANISATION.NAME")
        .from("ORGANISATION")
        .whereIfSet("ORGANISATION.ORGANISATIONID", pOrganisationId)
        .cell(true);
    
    return orgname;
};

/*
 * retrieves the name of an organisation with a select statement
 * 
 * @param {String} pContactId the ID of the corresponding org-contact of the organisation that shall be searched in the database; 
 * 
 * @return {String} the name of the organisation
 */
OrganisationUtils.getNameByContactId = function(pContactId)
{
    var orgname = newSelect("ORGANISATION.NAME")
        .from("ORGANISATION")
        .join("CONTACT", "CONTACT.ORGANISATION_ID = ORGANISATION.ORGANISATIONID and CONTACT.PERSON_ID is null")
        .whereIfSet("CONTACT.CONTACTID", pContactId)
        .cell(true);
    
    return orgname;
};

/**
 * a static Utility class for contacts
 * 
 * Do not create an instance of this!
 * @class
 */
function ContactUtils() {}

/**
 * creates an subSql for resolving a person/organisation-contact into one string of text (for example the name of a person)
 * useful for example in an displayValue-expression to resolave a references (which is chosen by Lookups)
 * does not validate if pRelationIdField exists or is a valid or harmful value
 * 
 * @param {String} pContactIdField fieldname for the CONTACTID-condition as TABLEALIAS.COLUMNALIAS; e.g. TASK.EDITOR_CONTACT_ID
 * @param {Boolean} [pIncludeOrganisation=true] false if the organisation shall not be included in the result string
 * @return {String} a subsql (without bracets) that can be played within an SQL
 */
ContactUtils.getResolvingDisplaySubSql = function(pContactIdField, pIncludeOrganisation)
{
    var contact = Contact.createWithColumnPreset();
    var rendererOptions;
    if (pIncludeOrganisation === false)
        rendererOptions = ContactTitleRenderer.OPTIONS.NoOption;
    else
        rendererOptions = ContactTitleRenderer.OPTIONS.IncludeOrganisation;
    var renderer = new ContactTitleRenderer(contact, rendererOptions);
    var selectExpression = renderer.asSql();

    //TODO: verify if there is a better solution for the usage of this as a displayValueExpression --> automatic use of #TITLE | waiting vor implementation
    return "select " + selectExpression + "from CONTACT "
         + " left join PERSON on (PERSON.PERSONID = CONTACT.PERSON_ID) "
         + " left join ORGANISATION on (ORGANISATION.ORGANISATIONID = CONTACT.ORGANISATION_ID) "
         + " where CONTACT.CONTACTID = " + pContactIdField;
}

/*
 * validates if a ORGANISATION_ID in a person-contact is correct [=>does not already exist] or not [=>does already exist]
 * this is done by checking the database for entires that do already exist with this combination of ORGANISATIONID and PERSONID
 * gives different messages for private persons and contacts that do already exist
 * 
 * @param {String} pPersonId the ID of the person that shall be searched in the database
 * @param {String} pOrganisationId the ID of the organisation that shall be searched in the database; 
 *                                 if this is an empty string it will be treated as private-dummy-organisation
 * @param {String} [pOwnContactId] the CONTACTID of your current record; this is only needed when in EDIT-mode since you don't want to get a message 
 *                                  for your own CONTACT;
 *                                  (if you do a lookup if a organisation-person-combination does already exist you'l get your own contact which you want to exclude)
 * 
 * @return {String} translated text that describes whats the problem or null if there was no problem and everything is fine
 * 
 */
ContactUtils.validateIfAlreadyExists = function(pPersonId, pOrganisationId, pOwnContactId)
{
    if (!pPersonId)
        return null;
    if (pOrganisationId == "")
        pOrganisationId = "0";

    var alreadyExistantContactId = newSelect("CONTACT.CONTACTID")
        .from("CONTACT")
        .where("CONTACT.PERSON_ID", pPersonId)
        .and("CONTACT.ORGANISATION_ID", pOrganisationId)
        //exclude the own since we do not want a "is not valid"-message for our own entry (on EDIT-mode)
        .andIfSet("CONTACT.CONTACTID", pOwnContactId, SqlBuilder.NOT_EQUAL())
        .cell();
        
    if (alreadyExistantContactId)
        if (pOrganisationId.trim() == "0")
            return translate.text("This private person doeas already exist and can not be created once more.");
        else
            return translate.text("This combination of person and organisation does already exist and can not be created once more.");
    
    return null;
};

/**
 * Get the type of contact. <br>
 * In recordstate NEW or EDIT it loads the person- / orgid from the db.<br>
 * But in the other states it uses the values pPersonId, pOrganisationId directly (for performance).<br>
 * <br>
 * It only checks if pPersonId / pOrganisationId are not empty. <br>
 * Based on which parameter is empty / not empty it return s the type of the contact. <br>
 *  <br>
 * !!It does not check if the person / org ids really exist!! <br>
 * !!And it does not check if really any contact with this person / org ids exist!! <br>
 *  <br>
 *  <br>
 * It is meant to be used by entitys, where you can load person and org with the DataRecord. <br>
 * This saves an extra select from CONTACT. <br>
 *  <br>
 *  <br>
 * @param {String} pContactId
 * @param {String} pPersonId selected from the CONTACT table
 * @param {String} pOrganisationId selected from the CONTACT table
 *  <br>
 * @return {Integer} <br>0 if both ids are empty <br>
 *                      1 if organisation <br>
 *                      2 if privat person <br>
 *                      3 if person of an organisation <br>
 */
ContactUtils.getContactType = function(pContactId, pPersonId, pOrganisationId)
{
    if (vars.get("$sys.recordstate") == neon.OPERATINGSTATE_NEW || vars.get("$sys.recordstate") == neon.OPERATINGSTATE_EDIT)
    {
        return ContactUtils.getContactTypeByContactId(pContactId);
    }
    else
    {
        return ContactUtils.getContactTypeByPersOrg(pPersonId, pOrganisationId);
    }
}

/**
 * Check if the contact is of the contactType "1": organisation <br>
 * This function uses "ContactUtils.getContactType" to check the type.<br>
 * So for more information about when to use whcih parameters for better perfrmance see it's documentation.<br>
 * <br>
 * <br>
 * @param {String} pContactId
 * @param {String} pPersonId selected from the CONTACT table
 * @param {String} pOrganisationId selected from the CONTACT table
 *  <br>
 * @return {Boolean} <br>true if the contact is a organisationType<br>
 *                      else: false <br>
 */
ContactUtils.isOrganisation = function(pContactId, pPersonId, pOrganisationId)
{
    var contactType = ContactUtils.getContactType(pContactId, pPersonId, pOrganisationId);
    
    return contactType == 1;
}

/**
 * Check if the contact is of the contactType "2": private person <br>
 * This function uses "ContactUtils.getContactType" to check the type.<br>
 * So for more information about when to use whcih parameters for better perfrmance see it's documentation.<br>
 * <br>
 * <br>
 * @param {String} pContactId
 * @param {String} pPersonId selected from the CONTACT table
 * @param {String} pOrganisationId selected from the CONTACT table
 *  <br>
 * @return {Boolean} <br>true if the contact is a private person<br>
 *                      else: false <br>
 */
ContactUtils.isPrivatePerson = function(pContactId, pPersonId, pOrganisationId)
{
    var contactType = ContactUtils.getContactType(pContactId, pPersonId, pOrganisationId);
    
    return contactType == 2;
}

/**
 * Check if the contact is of the contactType "3": person of an organisation <br>
 * This function uses "ContactUtils.getContactType" to check the type.<br>
 * So for more information about when to use whcih parameters for better perfrmance see it's documentation.<br>
 * <br>
 * <br>
 * @param {String} pContactId
 * @param {String} pPersonId selected from the CONTACT table
 * @param {String} pOrganisationId selected from the CONTACT table
 *  <br>
 * @return {Boolean} <br>true if the contact is a person of an organisation<br>
 *                      else: false <br>
 */
ContactUtils.isPersonOfOrganisation = function(pContactId, pPersonId, pOrganisationId)
{
    var contactType = ContactUtils.getContactType(pContactId, pPersonId, pOrganisationId);
    
    return contactType == 3;
}

/**
 * get the type of contact for a relationId <br>
 * If you already have persId and orgId from the CONTACT table, use getContactTypeByPersOrg() <br>
 * @param {String} pContactId
 * <br>
 * @return {Integer} 0 if relationId not found <br>
 *                  1 if organisation <br>
 *                  2 if privat person <br>
 *                  3 if person of an organisation <br>
 */
ContactUtils.getContactTypeByContactId = function(pContactId)
{
    var relationData = ContactUtils.getPersOrgIds(pContactId);
    if (relationData[0]) 
    {
        return this.getContactTypeByPersOrg(relationData[1], relationData[2]);
    }
    else
    {
        return 0;
    }
}

/**
 * Get the type of contact. <br>
 * It only checks if the parameters are not empty. <br>
 * Based on which parameter is empty / not empty it return s the type of the contact. <br>
 *  <br>
 * !!It does not check if the person / org ids really exist!! <br>
 * !!And it does not check if really any contact with this person / org ids exist!! <br>
 *  <br>
 * This function is more performant than getContactTypeByContactId, <br>
 * because it doesn't load something from the db. <br>
 *  <br>
 * It is meant to be used by entitys, where you can load person and org with the DataRecord. <br>
 * This saves an extra select from CONTACT. <br>
 *  <br>
 * @param {String} pPersonId selected from the CONTACT table
 * @param {String} pOrganisationId selected from the CONTACT table
 *  <br>
 * @return {Integer} <br>0 if both ids are empty <br>
 *                      1 if organisation <br>
 *                      2 if privat person <br>
 *                      3 if person of an organisation <br>
 */
ContactUtils.getContactTypeByPersOrg = function(pPersonId, pOrganisationId)
{
    if (!pPersonId)
    {
        if (!pOrganisationId) {
            return 0; // both are empty
        }
        return 1;  // Organisation da PERSON_ID leer
    }
    else
    {
        if (pOrganisationId.replace(/\s/g,"") == "0" )
        {
            return 2; // Privatperson da PERSON_ID nicht leer und ORGANISATION_ID.trim() = '0'
        }
        else
        {
            return 3; // Person einer Organisation da PERSON_ID nicht leer und ORGANISATION_ID nicht '0'
        }
    }
}

/**
 * return the corresponding context of the contact
 * 
 * It only checks if the parameters are not empty. <br>
 * Based on which parameter is empty / not empty it return s the type of the contact. <br>
 *  <br>
 * !!It does not check if the person / org ids really exist!! <br>
 * !!And it does not check if really any contact with this person / org ids exist!! <br>
 *  <br>
 * This function is more performant than getContextByContactId, <br>
 * because it doesn't load something from the db. <br>
 *  <br>
 * It is meant to be used by entitys, where you can load person and org with the DataRecord. <br>
 * This saves an extra select from CONTACT. <br>
 *  <br>
 * 
 * @param {String} pPersonId selected from the CONTACT table
 * @param {String} pOrganisationId selected from the CONTACT table
 * 
 * @return {String} contextname or "" if both ids are empty
 */
ContactUtils.getContextByPersOrg = function(pPersonId, pOrganisationId)
{
    switch (ContactUtils.getContactTypeByPersOrg(pPersonId, pOrganisationId))
    {
        case 1: // Org
            return ContextUtils.getContextName("Organisation");
        case 2: // private Person
        case 3: // Person
            return ContextUtils.getContextName("Person");
        default:
            return "";
    }
}

/**
 * return the corresponding context of the contact <br>
 * If you already have persId and orgId from the CONTACT table, use getContextByPersOrg() <br>
 * 
 * @param {String} pContactId
 * @return {String} contextname or "" if contact not found
 */
ContactUtils.getContextByContactId = function(pContactId)
{
    var relationData = ContactUtils.getPersOrgIds(pContactId);
    return ContactUtils.getContextByPersOrg(relationData[1], relationData[2])
}

/**
 * get the person- and org-id from a contact as array
 * 
 * @param {String} pContactId
 * @return {String[]} result as [contactid, persid, orgid] if one of them is null in the db, "" will be returned as the id. if Contactid is empty -> [] is returned
 */
ContactUtils.getPersOrgIds = function(pContactId)
{
    var persOrgIds = newSelect("CONTACTID, PERSON_ID, ORGANISATION_ID")
        .from("CONTACT")
        .whereIfSet("CONTACT.CONTACTID", pContactId)
        .arrayRow(true);
    
    return persOrgIds;
}


/**
 * get the person- and org-id from a contact as array
 * 
 * @param {String} pContactId
 * @return {String[]} result as [contactid, persid, orgid] if one of them is null in the db, "" will be returned as the id. if Contactid is empty -> [] is returned
 */
ContactUtils.getPersOrgIds = function(pContactId)
{
    return newSelect("CONTACTID, PERSON_ID, ORGANISATION_ID")
        .from("CONTACT")
        .whereIfSet("CONTACT.CONTACTID", pContactId)
        .arrayRow(true);
}

/**
 * automatically determines the organisations contactid by specifying a person-contactid (or organisation-contactid)
 * this is done with a sql statement, so this will only affect to the db commited data
 * 
 * @param {String} pAnyContactId a person-contactid or organisation-contactid
 * @return {String} the contactId of the organisation or ""
 */
ContactUtils.getOrganisationContactId = function(pAnyContactId)
{
    return newSelect("CONTACT.CONTACTID")
        .from("CONTACT")
        .join("CONTACT", "anyContact.ORGANISATION_ID = CONTACT.ORGANISATION_ID and CONTACT.PERSON_ID is null", "anyContact")
        .whereIfSet(["CONTACT", "CONTACTID", "anyContact"], pAnyContactId)
        .cell(true);
}

/**
 * get the contactId from the OrganisationId
 * 
 * @param {String} pOrganisationId
 * @return {String} the contactId or ""
 */
ContactUtils.getOrgContactId = function(pOrganisationId)
{
    if (pOrganisationId) {
        return newSelect("CONTACTID")
            .from("CONTACT")
            .where("CONTACT.ORGANISATION_ID", pOrganisationId)
            .and("CONTACT.PERSON_ID is null")
            .cell();
    }
    return "";
}

/**
 * get the name of the person or organisation
 * 
 * @param {String} pContactId the contact id where pers-name or orgname shall be loaded
 * @param {Boolean} [pIncludeOrganisation=true] false if the organisation shall not be included in the result string
 * 
 * @return {String} the name or ""
 */
ContactUtils.getFullTitleByContactId = function(pContactId, pIncludeOrganisation)
{
    var data = newSelect("ORGANISATION.NAME, PERSON.SALUTATION, PERSON.TITLE, PERSON.FIRSTNAME, PERSON.MIDDLENAME, PERSON.LASTNAME")
        .from("CONTACT")
        .join("ORGANISATION", "ORGANISATION.ORGANISATIONID = CONTACT.ORGANISATION_ID")
        .leftJoin("PERSON", "PERSON.PERSONID = CONTACT.PERSON_ID")
        .whereIfSet("CONTACT.CONTACTID", pContactId)
        .arrayRow(true);

    if (data.length == 0)
        return "";
    var contact = new Contact();
    [contact.organisationName, contact.salutation, contact.title, contact.firstname, contact.middlename, contact.lastname] = data;
    var rendererOptions;
    if (pIncludeOrganisation === false)
        rendererOptions = ContactTitleRenderer.OPTIONS.NoOption;
    else
        rendererOptions = ContactTitleRenderer.OPTIONS.IncludeOrganisation;
    var renderer = new ContactTitleRenderer(contact, rendererOptions);
    return renderer.asString();
}

/**
 * get the name of the person
 * do not use this for a mass of data (e.g. in a loop) since this will be slow due to select-time
 * 
 * @param {String} pPersonId the id of the person where the data shall be loaded
 * @param {Boolean} [pIncludeOrganisation=true] false if the organisation shall not be included in the result string
 * 
 * @return {String} the name or ""
 */
ContactUtils.getTitleByPersonId = function(pPersonId, pIncludeOrganisation)
{
    var data = newSelect("PERSON.SALUTATION, PERSON.TITLE, PERSON.FIRSTNAME, PERSON.MIDDLENAME, PERSON.LASTNAME")
        .from("PERSON")
        .whereIfSet("PERSON.PERSONID", pPersonId)
        .arrayRow(true);
        
    if (data.length == 0)
        return "";
    var contact = new Contact();
    [contact.salutation, contact.title, contact.firstname, contact.middlename, contact.lastname] = data;
    var rendererOptions;
    if (pIncludeOrganisation === false)
        rendererOptions = ContactTitleRenderer.OPTIONS.NoOption;
    else
        rendererOptions = ContactTitleRenderer.OPTIONS.IncludeOrganisation;
    var renderer = new ContactTitleRenderer(contact, rendererOptions);
    return renderer.asString();
}

/**
 * get the name of the person
 * do not use this for a mass of data (e.g. in a loop) since this will be slow due to select-time
 * 
 * @param {String} pContactId the id of the contact entry with the person where the data shall be loaded
 * 
 * @return {String} the name or ""
 */
ContactUtils.getTitleByContactId = function(pContactId)
{
    var personId = newSelect("CONTACT.PERSON_ID")
        .from("CONTACT")
        .whereIfSet("CONTACT.CONTACTID", pContactId)
        .cell(true);
    
    return ContactUtils.getTitleByPersonId(personId);
}

/**
 * returns the from string for the contact joined with org, person, address 
 *
 * @return {String}
 */
ContactUtils.getFullContactString = function()
{
    return " CONTACT "
         + " join ORGANISATION on ORGANISATION.ORGANISATIONID = CONTACT.ORGANISATION_ID  "
         + " left join PERSON on PERSON.PERSONID = CONTACT.PERSON_ID "
         + " left join ADDRESS on ADDRESS.ADDRESSID = CONTACT.ADDRESS_ID";
}

/**
 * Returns a SqlBuilder for selecting a contact with organisation, person and address. 
 * 
 * @param {String|Array|SqlBuilder} [pSelectFields] The select-part to set for the query, if it is omitted, no select will be set.
 * @return {SqlBuilder}
 */
ContactUtils.getFullContactSqlBuilder = function (pSelectFields)
{
    var sql = new SqlBuilder()
        .from("CONTACT")
        .join("ORGANISATION", "ORGANISATION.ORGANISATIONID = CONTACT.ORGANISATION_ID")
        .leftJoin("PERSON", "PERSON.PERSONID = CONTACT.PERSON_ID")
        .leftJoin("ADDRESS", "ADDRESS.ADDRESSID = CONTACT.ADDRESS_ID");
        
    if (pSelectFields)
        sql.select(pSelectFields);
    
    return sql;
}

/**
 * get the addressid of the default address
 *
 * @return {String} the ID or "" if no default address exists
 */
ContactUtils.getDefaultAddressId = function(pContactId)
{
    return newSelect("ADDRESS_ID")
        .from("CONTACT")
        .whereIfSet("CONTACT.CONTACTID", pContactId)
        .cell(true);
}

/**
 * makes a SqlBuilder that checks if there's (not) a commrestriction for a contact
 * 
 * @param {String} [pMedium=undefined] medium to check if undefined, don't check it
 * @param {boolean} [pNoRestriction=false] if true, the condition gets every contact that has no
 *                                     a commrestriction, otherwise every contact that has a commrestriction
 * @param {String|Number} [pStartDate=current date] date to check against the start date of the commrestriction
 * 
 * @return {SqlBuilder} the condition
 */
ContactUtils.getCommRestrictionCondition = function (pMedium, pNoRestriction, pStartDate)
{
    if (!pStartDate && pStartDate !== 0)
        pStartDate = datetime.date();
    var existsQuery = new SqlBuilder()
        .select("COMMRESTRICTIONID")
        .from("COMMRESTRICTION")
        .whereIfSet("COMMRESTRICTION.MEDIUM", pMedium)
        .and("COMMRESTRICTION.STARTDATE", pStartDate, SqlBuilder.LESS_OR_EQUAL())
        .and(newWhere()
            // check contactid
            .or("COMMRESTRICTION.CONTACT_ID = CONTACT.CONTACTID")
            // or the contact id of the organisation
            .or("COMMRESTRICTION.CONTACT_ID", newSelect("orgContact.CONTACTID")
                                                    .from("CONTACT anyContact")
                                                    .join("CONTACT orgContact", newWhere("anyContact.ORGANISATION_ID = orgContact.ORGANISATION_ID")
                                                                                    .and("orgContact.PERSON_ID is null")
                                                    )
                                                    .where("anyContact.CONTACTID = CONTACT.CONTACTID")
            )
        )
    var cond = pNoRestriction ? SqlBuilder.NOT_EXISTS() : SqlBuilder.EXISTS();
    return newWhere(null, existsQuery, cond);
}

/**
 * checks if a contact has a commrestriction
 * 
 * @param {String} pContactId contact id
 * @param {String} pMedium medium to check
 * @param {String|Number} [pStartDate=current date] the start date of the commrestriction
 * 
 * @return {Boolean} true, if the contact has a commrestriction, otherwise false
 */
ContactUtils.hasCommRestriction = function(pContactId, pMedium, pStartDate)
{
    return newSelect("CONTACTID")
        .from("CONTACT")
        .where("CONTACT.CONTACTID", pContactId)
        .and(ContactUtils.getCommRestrictionCondition(pMedium, false, pStartDate))
        .cell() != "";
}

/**
 * returns a sql subselect which concatenates all commrestriction displayvalues (translated).
 * The select needs the CONTACT.CONTACTID column.
 * 
 * @return {String} the resulting subselect
 */
ContactUtils.getActiveCommRestrictionsSubselect = function()
{
    var mediumList = KeywordUtils.getResolvedTitleSqlPart($KeywordRegistry.communicationMediumCampaign(), "COMMRESTRICTION.MEDIUM");
    var mask = new SqlMaskingUtils()

    var orgContactSubselect = newSelect("orgContact.CONTACTID")
                                    .from("CONTACT anyContact")
                                    .join("CONTACT orgContact", newWhere("anyContact.ORGANISATION_ID = orgContact.ORGANISATION_ID")
                                                                    .and("orgContact.PERSON_ID IS NULL"))
                                    .where("anyContact.CONTACTID = CONTACT.CONTACTID")

    var group = mask.getGroupConcat("DISTINCT "+mediumList, "', '");
    var subselect = "''";
    if(group)
    {
        var res = newSelect(group).from("COMMRESTRICTION")
                    .where("COMMRESTRICTION.CONTACT_ID = CONTACT.CONTACTID")

        var subres = newSelect(group).from("COMMRESTRICTION")
                    .where("COMMRESTRICTION.CONTACT_ID in ( "+orgContactSubselect.toString()+")")
                    
        subselect = mask.concatWithSeparator(["("+res.toString()+")", "("+subres.toString()+")"], ", ", false);
    }
    return subselect;
}

/**
 * returns a sql subselect which counts all Commrestrictions from a Person and his Organisation.
 * The select needs the CONTACT.CONTACTID column.
 * 
 * @return {String} the resulting subselect
 */
ContactUtils.getCommRestrictionCount = function(pMedium, pStartDate)
{
    if (!pStartDate && pStartDate !== 0)
        pStartDate = datetime.date();
    var firstLevel = new SqlBuilder()
        .select("count(*)")
        .from("COMMRESTRICTION")
        .whereIfSet("COMMRESTRICTION.MEDIUM", pMedium)
        .and("COMMRESTRICTION.STARTDATE", pStartDate, SqlBuilder.LESS_OR_EQUAL())
        .and("COMMRESTRICTION.CONTACT_ID = CONTACT.CONTACTID");
        
    var secondLevel = new SqlBuilder()
        .select("count(*)")
        .from("COMMRESTRICTION")
        .whereIfSet("COMMRESTRICTION.MEDIUM", pMedium)
        .and("COMMRESTRICTION.STARTDATE", pStartDate, SqlBuilder.LESS_OR_EQUAL())
        .and("COMMRESTRICTION.CONTACT_ID", newSelect("c1.CONTACTID").from("CONTACT", "c1")
                                          .join("CONTACT", "c2.ORGANISATION_ID = c1.ORGANISATION_ID and c1.PERSON_ID is null", "c2")
                                          .where("c2.CONTACTID = CONTACT.CONTACTID")
        , SqlBuilder.IN());
            
    return firstLevel.toString() +") + ("+secondLevel.toString(); 
}

/**
 * returns sql for filtering links, mail addresses or telephone numbers
 * 
 * @param {String} pOperator filter operator
 * @param {String} pRawvalue value that is filtered for
 * @param {String} pFilterOperatorName name of the filter operator
 * @param {String} pComparison comparison operator
 * @param {String} pType type of communication
 * 
 * @return {String} the resulting select
 */
ContactUtils.getCommFilter = function(pOperator, pRawvalue, pFilterOperatorName, pComparison, pType)
{
    let resultSqlCond;
    
    //SqlBuilder not implemented as the statement needs to be finished in the switch case
    let commpart = "(select COMMUNICATION.CONTACT_ID from COMMUNICATION "
        + " join AB_KEYWORD_ENTRY on AB_KEYWORD_ENTRY.KEYID = COMMUNICATION.MEDIUM_ID "
        + " join AB_KEYWORD_ATTRIBUTERELATION on AB_KEYWORD_ENTRY.AB_KEYWORD_ENTRYID = AB_KEYWORD_ATTRIBUTERELATION.AB_KEYWORD_ENTRY_ID"
        + " where AB_KEYWORD_ATTRIBUTERELATION.CHAR_VALUE = '" + pType + "'";
 
    resultSqlCond = "CONTACTID in " + commpart + " and COMMUNICATION.ADDR " + pFilterOperatorName;

    switch (pComparison)
    {
        case "EQUAL":
        case "NOT_EQUAL":
            resultSqlCond = resultSqlCond + " '" + pRawvalue + "')";
            break;
        case "CONTAINS":
        case "CONTAINSNOT":
            resultSqlCond = resultSqlCond + " '%" + pRawvalue + "%')";
            break;
        case "STARTSWITH":
            resultSqlCond = resultSqlCond + " '" + pRawvalue + "%')";
            break;
        case "ENDSWITH":
            resultSqlCond = resultSqlCond + " '%" + pRawvalue + "')";
            break;
        case "ISNULL":
            resultSqlCond = "CONTACTID not in " + commpart + ")";
            break;
        case "ISNOTNULL":
            resultSqlCond = resultSqlCond + ")";
            break;
    }    
    return resultSqlCond;
}

/**
 * object for handling of a single contact
 * provides static- and instance-functions
 * 
 * @class
 *
 */
function Contact()
{
    //storage for information
    this.salutation = "";
    this.title = "";
    this.firstname= "";
    this.middlename = "";
    this.lastname = "";
    this.suffix = "";

    this.customercode = "";
    this.organisationName = "";
    
    this._contactType = Contact.TYPES.Auto;
}

/**
 * sets the contactType of a contact
 * reserved for future implementation
 * 
 * @param {String} pContactType contactType that shall be set; value of Contact.TYPES.
 *
 * @return void
 */
Contact.prototype.setContactType = function (pContactType)
{
    if (! ObjectUtils.existsValue(Contact.TYPES, pContactType))
        throw new TypeError("the given contact type is not a valid value and not a contact type");
    
    this._contactType = pContactType;
};

/**
 * returns the contactType of a contact
 * reserved for future implementation
 * 
 * @return {String} the contactType is a value of Contact.TYPES.
 */
Contact.prototype.getContactType = function ()
{
    return this._contactType;
};

/**
 * constants for types of Contacts
 * use only within functions
 * reserved for future implementation
 * 
 * @static
 */
Contact.TYPES = {
    Organisation: "organisation",
    Contact: "contact",
    Private: "private",
    Auto: null
};

/**
 * creates a new Contact-object with a preset of DB-columns
 * 
 * @static
 */
Contact.createWithColumnPreset = function()
{
    var contact = new Contact();
    contact.salutation = "PERSON.SALUTATION";
    contact.title = "PERSON.TITLE";
    contact.firstname= "PERSON.FIRSTNAME";
    contact.middlename = "PERSON.MIDDLENAME";
    contact.lastname = "PERSON.LASTNAME";
    contact.suffix = "PERSON.TITLESUFFIX";
    contact.customercode = "ORGANISATION.CUSTOMERCODE";
    contact.organisationName = "ORGANISATION.NAME";
    return contact;
};

/**
 * creates a new Contact-object with a preset of DB-columns
 * 
 * @static
 */
Contact.createWithColumnPresetForPrivatePerson = function()
{
    var contact = new Contact();
    contact.salutation = "PERSON.SALUTATION";
    contact.title = "PERSON.TITLE";
    contact.firstname= "PERSON.FIRSTNAME";
    contact.middlename = "PERSON.MIDDLENAME";
    contact.lastname = "PERSON.LASTNAME";
    contact.suffix = "PERSON.TITLESUFFIX";
    return contact;
};

/**
 * object for rendering a Contact-object into text representation or an sql that represents the text
 * provides static- and instance-functions
 * 
 * @param {Contact} pContact The Contact-object to render
 * @param {Number} pOptions additional options for rendering; use values of ContactTitleRenderer.OPTIONS and pass them by bitwise OR concatination; e.g.:
 *                  OPTION_1 | OPTION_2 | OPTION_5
 * 
 * @class
 *
 * //TODO: far too complex to understand for the very easy job it does. Refactor for better understanding what this (and related) function do, and how.
 *
 */
function ContactTitleRenderer(pContact, pOptions)
{
    this.contact = pContact;
    if (pOptions !== undefined)//null means null which is "no option"; so check exactly for undefined to check if default option has to be set
        this._options = pOptions;
    else 
        this._options = ContactTitleRenderer.OPTIONS.IncludeOrganisation;
        
    //function that renders the contact into a sql expression (e.g. for a subselect)
    this._asSqlFn = function(){
        var maskingUtil = new SqlMaskingUtils();
        var res = maskingUtil.concatWithSeparator([this.contact.salutation, this.contact.title, this.contact.firstname, this.contact.middlename, this.contact.lastname].filter(function (e){
            return e != "";
        }), " ", false);
        //binary AND check for possibility to check serveral options
        if (this._options & ContactTitleRenderer.OPTIONS.IncludeOrganisation && this.contact.organisationName)
            res = maskingUtil.concatWithSeparator([res, this.contact.organisationName], " | ");
        return res;
    };
    
    //function that renders the contact into a text  (e.g. for a displayValue)
    this._asStringFn = function (){
        var res = StringUtils.concat(" ", [this.contact.salutation, this.contact.title, this.contact.firstname, this.contact.middlename, this.contact.lastname]);
        //binary AND check for possibility to check serveral options
        if (this._options & ContactTitleRenderer.OPTIONS.IncludeOrganisation && this.contact.organisationName)
            res = StringUtils.concat(" | ", [res, this.contact.organisationName]);
        return res;
    }
}

/**
 * constants for options for the ContactTitleRenderer
 * use only within functions
 * 
 * @static
 */
ContactTitleRenderer.OPTIONS = {
    NoOption: 0,
    IncludeOrganisation: 1
};

/**
 * function that renders the contact into a sql expression (e.g. for a subselect)
 * what this function exactly does depends on specified values and options specified in the Renderer-object
 * 
 * @return {String} sql-expression that can be placed inside a select statement
 */
ContactTitleRenderer.prototype.asSql = function ()
{
    return this._asSqlFn.apply(this, arguments);
};

/**
 * function that renders the contact into text
 * what this function exactly does depends on specified values and options specified in the Renderer-object
 * 
 * @return {String} rendered values as text
 */
ContactTitleRenderer.prototype.asString = function ()
{
    return this._asStringFn.apply(this, arguments);
};