text: import * as configFunctions from "../functions.config.js";
  const sql_bankRecordsFlatByBankingYear = (() => {
      const bankRecordTypes = configFunctions.getProperty("bankRecordTypes");
      const sql = "select o.organizationName," +
          " r1.accountNumber, m.bankingYear, m.bankingMonth" +
          bankRecordTypes.reduce((soFar, bankRecordType) => {
              const bankRecordTypeKey = bankRecordType.bankRecordType;
              return soFar +
                  ", max(case" +
                  " when r2.bankRecordType = '" + bankRecordTypeKey + "' and r2.recordIsNA = 1 then 'Not Applicable'" +
                  " when r2.bankRecordType = '" + bankRecordTypeKey + "' and r2.recordDate is not null and r2.recordDate != 0 then 'Received'" +
                  " else '' end) as " + bankRecordTypeKey + "_status" +
                  ", max(case when r2.bankRecordType = '" + bankRecordTypeKey + "' then r2.recordNote end) as " + bankRecordTypeKey + "_recordNote";
          }, "") +
          " from (select ? as bankingYear, bankingMonth from BankingMonths) as m" +
          " left join (select distinct organizationID, accountNumber, bankingYear from OrganizationBankRecords) r1 on m.bankingYear = r1.bankingYear" +
          " left join OrganizationBankRecords r2" +
          " on r1.organizationID = r2.organizationID and r1.accountNumber = r2.accountNumber" +
          " and m.bankingYear = r2.bankingYear and m.bankingMonth = r2.bankingMonth" +
          " left join Organizations o on r1.organizationID = o.organizationID" +
          " where r2.recordDelete_timeMillis is null" +
          " and o.recordDelete_timeMillis is null" +
          " group by o.organizationName, r1.accountNumber, m.bankingYear, m.bankingMonth";
      return sql;
  })();
  const sql_bankRecordsFlatByOrganizationAndBankingYear = (() => {
      const bankRecordTypes = configFunctions.getProperty("bankRecordTypes");
      const sql = "select o.organizationName," +
          " r1.accountNumber, m.bankingYear, m.bankingMonth" +
          bankRecordTypes.reduce((soFar, bankRecordType) => {
              const bankRecordTypeKey = bankRecordType.bankRecordType;
              return soFar +
                  ", max(case" +
                  " when r2.bankRecordType = '" + bankRecordTypeKey + "' and r2.recordIsNA = 1 then 'Not Applicable'" +
                  " when r2.bankRecordType = '" + bankRecordTypeKey + "' and r2.recordDate is not null and r2.recordDate != 0 then 'Received'" +
                  " else '' end) as " + bankRecordTypeKey + "_status" +
                  ", max(case when bankRecordType = '" + bankRecordTypeKey + "' then recordDate end) as " + bankRecordTypeKey + "_recordDate" +
                  ", max(case when r2.bankRecordType = '" + bankRecordTypeKey + "' then r2.recordNote end) as " + bankRecordTypeKey + "_recordNote";
          }, "") +
          " from (select ? as bankingYear, bankingMonth from BankingMonths) as m" +
          " left join (select distinct organizationID, accountNumber, bankingYear from OrganizationBankRecords) r1 on m.bankingYear = r1.bankingYear" +
          " left join OrganizationBankRecords r2" +
          " on r1.organizationID = r2.organizationID and r1.accountNumber = r2.accountNumber" +
          " and m.bankingYear = r2.bankingYear and m.bankingMonth = r2.bankingMonth" +
          " left join Organizations o on r1.organizationID = o.organizationID" +
          " where r2.recordDelete_timeMillis is null" +
          " and r1.organizationID = ?" +
          " group by o.organizationName, r1.accountNumber, m.bankingYear, m.bankingMonth";
      return sql;
  })();
  export const reports = {
      "bankRecords-all": {
          sql: "select * from OrganizationBankRecords"
      },
      "bankRecordsFlat-byBankingYear": {
          sql: sql_bankRecordsFlatByBankingYear,
          params: (request) => [request.query.bankingYear]
      },
      "bankRecordsFlat-byOrganizationAndBankingYear": {
          sql: sql_bankRecordsFlatByOrganizationAndBankingYear,
          params: (request) => [request.query.bankingYear, request.query.organizationID]
      }
  };
  export default reports;
  