query.js

import("system.translate");
import("system.result");
import("system.vars");
import("system.calendars");
import("system.db");
import("Sql_lib");
import("Communication_lib");

var sqlQuery, sqlHelper, queryCondition, affectedIds;
if (vars.exists("$local.idvalue")) {
    affectedIds = vars.get("$local.idvalue");
    queryCondition = "where CONTACT.CONTACTID in ('" + affectedIds.map(function (v){return db.quote(v);}).join("', '") + "')";
    //TODO: refactor this for incremental indexer (injections?)
}
sqlHelper = new SqlMaskingUtils();
sqlQuery = "select ORGANISATION.ORGANISATIONID as uid"
    + ", " + sqlHelper.concat(["ORGANISATION.NAME", "'|'", "ORGANISATION.CUSTOMERCODE"]) 
    + " as title "
    + ", " + sqlHelper.concat([
         sqlHelper.concat(["defaultAddress.ADDRESS", "defaultAddress.BUILDINGNO", "'-'"
            ,"defaultAddress.COUNTRY", "defaultAddress.ZIP", "defaultAddress.CITY"])
        ,sqlHelper.concat(["'" + translate.text("Phone") + ":'", "(" + CommUtil.getStandardSubSqlPhone() + ")"])
        ,sqlHelper.concat(["'" + translate.text("Email") + ":'", "(" + CommUtil.getStandardSubSqlMail() + ")"])
        ], " | ") + " as description "
    //additional indexed fields
    + ", ORGANISATION.ORGANISATIONID"
    + ", ORGANISATION.NAME"
    + ", CONTACT.CONTACTID"
    + ", (" + CommUtil.getStandardSubSqlPhone() + ') as "STANDARD_PHONE_COMMUNICATION.value"'
    + ", (" + CommUtil.getStandardSubSqlMail() + ') as "STANDARD_EMAIL_COMMUNICATION.value"'
    + " from ORGANISATION "
    + " join CONTACT on CONTACT.ORGANISATION_ID = ORGANISATION.ORGANISATIONID and CONTACT.PERSON_ID is null "
    + " left join ADDRESS defaultAddress on defaultAddress.ADDRESSID = CONTACT.ADDRESS_ID "
    + " left join COMMUNICATION on COMMUNICATION.CONTACT_ID = CONTACT.CONTACTID "
    + (queryCondition || "") 
    + " order by CONTACT.CONTACTID ";
result.string(sqlQuery);