query.js

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 SALESPROJECTID in ('" + affectedIds.map(function (v){return db.quote(v);}).join("', '") + "')";
    //TODO: refactor this for incremental indexer (injections?)
}
sqlHelper = new SqlMaskingUtils();
sqlQuery = "select SALESPROJECTID as uid" 
    + ", PROJECTTITLE as title, " 
    + sqlHelper.concat([
        "'" + translate.text("Status") + ":'",  KeywordUtils.getResolvedTitleSqlPart($KeywordRegistry.salesprojectState(), "STATE"),
        "'| " + translate.text("Phase") + ":'",  KeywordUtils.getResolvedTitleSqlPart($KeywordRegistry.salesprojectPhase(), "PHASE")
      ]) 
    + " as description" 
    + ", PROJECTCODE"
    + ", PROJECTTITLE"
    + ", STATE" 
    + ', ' + KeywordUtils.getResolvedTitleSqlPart($KeywordRegistry.salesprojectState(), "STATE") + ' as "STATE.displayValue"'
    + ", PHASE"
    + ', ' + KeywordUtils.getResolvedTitleSqlPart($KeywordRegistry.salesprojectState(), "PHASE") + ' as "PHASE.displayValue"'
    + " from SALESPROJECT "
    + queryCondition + " order by PROJECTCODE ";
result.string(sqlQuery);