cityssm/contract-expiration-tracker

import sqlite from "better-sqlite3";
import { contractsDB as databasePath } from "../../data/databasePaths.js";
import * as dateTimeFunctions from "@cityssm/expressjs-server-js/dateTimeFns.js";
export const getContracts = (filters, requestSession, options = {}) => {
    let sql = "select contractId," +
        " contractTitle, contractCategory, contractParty," +
        (options.includeContractDescription
            ? " contractDescription,"
            : "") +
        (requestSession.user.canUpdate && options.includePrivateContractDescription
            ? " privateContractDescription,"
            : "") +
        " startDate, userFn_dateIntegerToString(startDate) as startDateString," +
        " endDate, userFn_dateIntegerToString(endDate) as endDateString," +
        " extensionDate, userFn_dateIntegerToString(extensionDate) as extensionDateString," +
        " hasBeenReplaced," +
        " managingUserName" +
        (options.includeTimeMillis
            ? ", recordCreate_timeMillis, recordUpdate_timeMillis"
            : "") +
        " from Contracts" +
        " where recordDelete_timeMillis is null";
    const parameters = [];
    if (!requestSession.user.canUpdate) {
        sql += " and contractCategory in (select contractCategory from ContractCategoryUsers where userName = ?)";
        parameters.push(requestSession.user.userName);
    }
    if (filters.contractCategory && filters.contractCategory !== "") {
        sql += " and contractCategory = ?";
        parameters.push(filters.contractCategory);
    }
    if (filters.hasBeenReplaced && filters.hasBeenReplaced !== "") {
        sql += " and hasBeenReplaced = ?";
        parameters.push(filters.hasBeenReplaced);
    }
    if (filters.searchString && filters.searchString !== "") {
        const searchStringPieces = filters.searchString.trim().toLowerCase().split(" ");
        for (const searchStringPiece of searchStringPieces) {
            sql += " and (" +
                "instr(lower(contractTitle), ?)" +
                " or instr(lower(contractDescription), ?)" +
                " or instr(lower(contractParty), ?)" +
                ")";
            parameters.push(searchStringPiece, searchStringPiece, searchStringPiece);
        }
    }
    if (filters.managingUserName && filters.managingUserName !== "") {
        sql += " and managingUserName = ?";
        parameters.push(filters.managingUserName);
    }
    if (!filters.includeExpired) {
        sql += " and endDate is not null and endDate >= ?";
        parameters.push(dateTimeFunctions.dateToInteger(new Date()));
    }
    sql += " order by endDate desc, startDate desc";
    const database = sqlite(databasePath, {
        readonly: true
    });
    database.function("userFn_dateIntegerToString", dateTimeFunctions.dateIntegerToString);
    const rows = database.prepare(sql).all(parameters);
    database.close();
    return rows;
};
export default getContracts;