helpers/recordsDB/searchRelatedRecords.js
import * as sqlPool from "@cityssm/mssql-multi-pool";
import * as configFns from "../configFns.js";
import debug from "debug";
const debugSQL = debug("corporate-records-manager:recordsDB:searchRelatedRecords");
export const searchRelatedRecords = async (recordID, recordTypeKey, searchString) => {
try {
const pool = await sqlPool.connect(configFns.getProperty("mssqlConfig"));
let request = pool.request()
.input("recordID", recordID);
let sql = "select top 100" +
" recordID, recordTypeKey, recordNumber," +
" recordTitle, recordDescription," +
" party, location," +
" recordCreate_userName, recordCreate_datetime," +
" recordUpdate_userName, recordUpdate_datetime" +
" from CR.Records" +
" where recordDelete_datetime is null" +
" and recordID <> @recordID" +
" and recordID not in (select recordID_A from CR.RelatedRecords where recordID_B = @recordID)" +
" and recordID not in (select recordID_B from CR.RelatedRecords where recordID_A = @recordID)";
if (recordTypeKey !== "") {
request = request.input("recordTypeKey", recordTypeKey);
sql += " and recordTypeKey = @recordTypeKey";
}
if (searchString !== "") {
const searchStringSplit = searchString.trim().split(" ");
for (const [index, element] of searchStringSplit.entries()) {
const inputKey = "searchString" + index.toString();
request = request.input(inputKey, element);
sql += " and (" +
"recordNumber like '%' + @" + inputKey + " + '%'" +
" or recordTitle like '%' + @" + inputKey + " + '%'" +
" or recordDescription like '%' + @" + inputKey + " + '%'" +
" or party like '%' + @" + inputKey + " + '%'" +
" or location like '%' + @" + inputKey + " + '%'" +
")";
}
}
const result = await request.query(sql);
if (!result.recordset || result.recordset.length === 0) {
return [];
}
const records = result.recordset;
return records;
}
catch (error) {
debugSQL(error);
}
};
export default searchRelatedRecords;