cityssm/lottery-licence-manager

import * as dateTimeFns from "@cityssm/expressjs-server-js/dateTimeFns.js";
export const reports = {
    "locations-all": {
        sql: "select * from Locations"
    },
    "locations-formatted": {
        sql: "select locationName," +
            " locationAddress1, locationAddress2, locationCity, locationProvince, locationPostalCode" +
            " from Locations" +
            " where locationID in (select locationID from LotteryLicences)" +
            " and recordDelete_timeMillis is null" +
            " order by locationName"
    },
    "locations-unused": {
        sql: "select lo.locationID, lo.locationName," +
            " lo.locationAddress1, lo.locationAddress2, lo.locationCity, lo.locationProvince," +
            " l.licences_endDateMax, d.distributor_endDateMax, m.manufacturer_endDateMax" +
            " from Locations lo" +
            (" left join (" +
                "select locationID, max(endDate) as licences_endDateMax" +
                " from LotteryLicences" +
                " where recordDelete_timeMillis is null" +
                " group by locationID" +
                ") l on lo.locationID = l.locationID") +
            (" left join (" +
                "select t.distributorLocationID," +
                " max(l.endDate) as distributor_endDateMax" +
                " from LotteryLicenceTicketTypes t" +
                " left join LotteryLicences l on t.licenceID = l.licenceID" +
                " where t.recordDelete_timeMillis is null" +
                " group by t.distributorLocationID" +
                ") d on lo.locationID = d.distributorLocationID") +
            (" left join (" +
                "select t.manufacturerLocationID, max(l.endDate) as manufacturer_endDateMax" +
                " from LotteryLicenceTicketTypes t" +
                " left join LotteryLicences l on t.licenceID = l.licenceID" +
                " where t.recordDelete_timeMillis is null" +
                " group by t.manufacturerLocationID" +
                ") m on lo.locationID = m.manufacturerLocationID") +
            " where lo.recordDelete_timeMillis is null" +
            " group by lo.locationID, lo.locationName," +
            " lo.locationAddress1, lo.locationAddress2, lo.locationCity, lo.locationProvince," +
            " l.licences_endDateMax, d.distributor_endDateMax, m.manufacturer_endDateMax" +
            (" having max(" +
                "ifnull(l.licences_endDateMax, 0)," +
                " ifnull(d.distributor_endDateMax, 0)," +
                " ifnull(m.manufacturer_endDateMax, 0)) <= ?"),
        params: () => {
            const threeYearsAgo = new Date();
            threeYearsAgo.setFullYear(threeYearsAgo.getFullYear() - 3);
            return [dateTimeFns.dateToInteger(threeYearsAgo)];
        }
    },
    "locations-distributors": {
        sql: "select locationID, locationName," +
            " locationAddress1, locationAddress2, locationCity, locationProvince, locationPostalCode" +
            " from Locations" +
            " where locationIsDistributor = 1" +
            " and recordDelete_timeMillis is null"
    },
    "locations-manufacturers": {
        sql: "select locationID, locationName," +
            " locationAddress1, locationAddress2, locationCity, locationProvince, locationPostalCode" +
            " from Locations" +
            " where locationIsManufacturer = 1" +
            " and recordDelete_timeMillis is null"
    }
};
export default reports;