cityssm/parking-ticket-system

import { dateIntegerToString, dateToInteger } from '@cityssm/utils-datetime';
import sqlite from 'better-sqlite3';
import { parkingDB as databasePath } from '../../data/databasePaths.js';
export default function getParkingTicketsAvailableForMTOLookup(currentBatchId, issueDaysAgo) {
    const database = sqlite(databasePath, {
        readonly: true
    });
    database.function('userFn_dateIntegerToString', dateIntegerToString);
    let issueDateNumber = 1e8;
    if (issueDaysAgo !== -1) {
        const issueDate = new Date();
        issueDate.setDate(issueDate.getDate() - issueDaysAgo);
        issueDateNumber = dateToInteger(issueDate);
    }
    const tickets = database
        .prepare(`select t.ticketId, t.ticketNumber,
        t.issueDate, userFn_dateIntegerToString(t.issueDate) as issueDateString,
        t.licencePlateNumber,
        max(
          case when b.mto_includeLabels is not null and b.mto_includeLabels = 0
          then userFn_dateIntegerToString(b.batchDate)
          else null end) as batchStringDate_withoutLabels,
        max(case when b.mto_includeLabels is not null and b.mto_includeLabels = 1
          then userFn_dateIntegerToString(b.batchDate)
          else null end) as batchStringDate_withLabels
        from ParkingTickets t
        left join (
          select e.batchId, b.batchDate, b.mto_includeLabels, e.ticketId,
          e.licencePlateCountry, e.licencePlateProvince, e.licencePlateNumber
          from LicencePlateLookupBatchEntries e
          left join LicencePlateLookupBatches b on e.batchId = b.batchId
        ) b on t.ticketId = b.ticketId
          and t.licencePlateCountry = b.licencePlateCountry
          and t.licencePlateProvince = b.licencePlateProvince
          and t.licencePlateNumber = b.licencePlateNumber
        
        where t.recordDelete_timeMillis is null
        and t.licencePlateCountry = 'CA'
        and t.licencePlateProvince = 'ON'
        and t.licencePlateNumber != ''
        and t.resolvedDate is null
        and t.ticketId not in (select ticketId from LicencePlateLookupBatchEntries where batchId = ?)
        and t.issueDate < ?
        group by t.ticketId, t.ticketNumber, t.issueDate, t.issueTime, t.licencePlateNumber
        order by t.licencePlateNumber, t.issueDate`)
        .all(currentBatchId, issueDateNumber);
    database.close();
    return tickets;
}