query.js

import("system.SQLTYPES");
import("system.result");
import("system.vars");
import("system.calendars");
import("system.db");
import("Sql_lib");
import("Keyword_lib");
import("KeywordRegistry_basic");

var sqlQuery, sqlHelper, queryCondition, affectedIds;
queryCondition = "";
if (vars.exists("$local.idvalue")) {
    affectedIds = vars.get("$local.idvalue");
    queryCondition = "where SALESORDERID in ('" + affectedIds.map(function (v){return db.quote(v);}).join("', '") + "')";
    //TODO: refactor this for incremental indexer (injections?)
}
sqlHelper = new SqlMaskingUtils();
sqlQuery = "select SALESORDERID as uid, " 
    + sqlHelper.concat([sqlHelper.cast("SALESORDERCODE", SQLTYPES.CHAR, 10), KeywordUtils.getResolvedTitleSqlPart($KeywordRegistry.salesorderState(), "SALESORDER.STATUS")], " | ")
    + " as title, " 
    + sqlHelper.concat(["ORGANISATION.NAME"], " | ") 
    + " as description" 
    + ", SALESORDERCODE" 
    + ", CUSTOMERCODE"
    + ", SALESORDER.CONTACT_ID"
    + ', CONTACT.ORGANISATION_ID as CONTACT_ORG_ID'
    + ', ORGANISATION.NAME  as "CONTACT_ORG_ID.displayValue"'
    + " from SALESORDER "
    + " join CONTACT on SALESORDER.CONTACT_ID = CONTACTID "
    + " join ORGANISATION on ORGANISATIONID = CONTACT.ORGANISATION_ID "
    + queryCondition + " order by SALESORDERCODE ";
result.string(sqlQuery);