cityssm/lottery-licence-manager

import sqlite from "better-sqlite3";
import { licencesDB as databasePath } from "../../data/databasePaths.js";

import * as dateTimeFns from "@cityssm/expressjs-server-js/dateTimeFns.js";
import { canUpdateObject } from "../licencesDB.js";

import type * as llm from "../../types/recordTypes";
import type * as expressSession from "express-session";


export const getOrganizations = (requestBody: {
  organizationName?: string;
  representativeName?: string;
  isEligibleForLicences?: string;
  organizationIsActive?: string;
}, requestSession: expressSession.Session, includeOptions: {
  limit: number;
  offset?: number;
}): llm.Organization[] => {

  const database = sqlite(databasePath, {
    readonly: true
  });

  const sqlParameters: Array<string | number> = [dateTimeFns.dateToInteger(new Date())];

  let sql = "select o.organizationID, o.organizationName, o.isEligibleForLicences, o.organizationNote," +
    " r.representativeName," +
    " sum(case when l.endDate >= ? then 1 else 0 end) as licences_activeCount," +
    " max(l.endDate) as licences_endDateMax," +
    " o.recordCreate_userName, o.recordCreate_timeMillis, o.recordUpdate_userName, o.recordUpdate_timeMillis" +
    " from Organizations o" +
    " left join OrganizationRepresentatives r on o.organizationID = r.organizationID and r.isDefault = 1" +
    " left join LotteryLicences l on o.organizationID = l.organizationID and l.recordDelete_timeMillis is null" +
    " where o.recordDelete_timeMillis is null";

  if (requestBody.organizationName && requestBody.organizationName !== "") {

    const organizationNamePieces = requestBody.organizationName.toLowerCase().split(" ");

    for (const organizationPiece of organizationNamePieces) {

      sql += " and instr(lower(o.organizationName), ?)";
      sqlParameters.push(organizationPiece);
    }
  }

  if (requestBody.representativeName && requestBody.representativeName !== "") {

    const representativeNamePieces = requestBody.representativeName.toLowerCase().split(" ");

    for (const representativePiece of representativeNamePieces) {

      sql += " and o.organizationID in (" +
        "select organizationID from OrganizationRepresentatives where instr(lower(representativeName), ?)" +
        ")";

      sqlParameters.push(representativePiece);
    }
  }

  if (requestBody.isEligibleForLicences && requestBody.isEligibleForLicences !== "") {

    sql += " and o.isEligibleForLicences = ?";
    sqlParameters.push(requestBody.isEligibleForLicences);

  }

  if (requestBody.organizationIsActive && requestBody.organizationIsActive !== "") {

    const currentDate = dateTimeFns.dateToInteger(new Date());

    sql += " and o.organizationID in (" +
      "select lx.organizationID from LotteryLicences lx" +
      " where lx.recordDelete_timeMillis is null" +
      " and lx.issueDate is not null and lx.endDate >= ?)";

    sqlParameters.push(currentDate);
  }

  sql += " group by o.organizationID, o.organizationName, o.isEligibleForLicences, o.organizationNote," +
    " r.representativeName," +
    " o.recordCreate_userName, o.recordCreate_timeMillis, o.recordUpdate_userName, o.recordUpdate_timeMillis" +
    " order by o.organizationName, o.organizationID";

  if (includeOptions.limit !== -1) {

    sql += " limit " + includeOptions.limit.toString() +
      " offset " + (includeOptions.offset || 0).toString();

  }

  const rows: llm.Organization[] = database.prepare(sql).all(sqlParameters);

  database.close();

  for (const element of rows) {

    element.recordType = "organization";

    element.licences_endDateMaxString = dateTimeFns.dateIntegerToString(element.licences_endDateMax || 0);

    element.canUpdate = canUpdateObject(element, requestSession);

    delete element.recordCreate_userName;
    delete element.recordCreate_timeMillis;
    delete element.recordUpdate_userName;
    delete element.recordUpdate_timeMillis;
  }

  return rows;

};