query.js

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

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