query.js

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

var sqlHelper = new SqlMaskingUtils();
var querySelect = newSelect("CONTACT.CONTACTID as \"_uid_\""
                        + "," + sqlHelper.concat(["PERSON.SALUTATION", "PERSON.FIRSTNAME", "PERSON.LASTNAME", "'|'", "ORGANISATION.NAME"]) //  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
                        + ", CONTACT.CONTACTID "
                        + ", PERSON.PERSONID "
                        + ", PERSON.SALUTATION "
                        + ", PERSON.TITLE " // as "TITLE.value
                        + ", PERSON.FIRSTNAME "
                        + ", PERSON.LASTNAME "
                        + ", TRIM(PERSON.GENDER) " // as GENDER
                        + ", CONTACT.ORGANISATION_ID "
                        + ", ORGANISATION.NAME "
                        + ", COMMUNICATION.ADDR " // as IndexCommunication
                        + ", ADDRESS.ADDRESS " // as IndexAddress
                        + ", ADDRESS.COUNTRY " // as IndexCountry
                        + ", ADDRESS.ZIP " // as IndexZIP
                        + ", ADDRESS.CITY " // as IndexCity"
                        + ", PERSON.FIRSTNAME " // as IndexPhoneticFirstname
                        + ", PERSON.LASTNAME ")
                    .from("PERSON")
                    .join("CONTACT", "CONTACT.PERSON_ID = PERSON.PERSONID")
                    .join("ORGANISATION", "CONTACT.ORGANISATION_ID = ORGANISATION.ORGANISATIONID")
                    .leftJoin("ADDRESS", "defaultAddress.ADDRESSID = CONTACT.ADDRESS_ID", "defaultAddress")
                    .leftJoin("ADDRESS", "ADDRESS.CONTACT_ID = CONTACT.CONTACTID or ADDRESS.ADDRESSID = CONTACT.ADDRESS_ID")
                    .leftJoin("COMMUNICATION", "COMMUNICATION.CONTACT_ID = CONTACT.CONTACTID")
                    .orderBy("CONTACT.CONTACTID");

if (vars.exists("$local.idvalue")) {
    var affectedIds = vars.get("$local.idvalue");
    sqlQuery.whereIfSet("CONTACT.CONTACTID", affectedIds, SqlBuilder.IN());
}

result.string(sqlQuery);