63 lines
1.9 KiB
TypeScript
63 lines
1.9 KiB
TypeScript
import _ from 'lodash'
|
|
import * as z from 'zod'
|
|
import type { FastifyPluginCallbackZod } from 'fastify-type-provider-zod'
|
|
import { sql } from 'kysely'
|
|
|
|
const resultRoutes: FastifyPluginCallbackZod = (fastify, _, done) => {
|
|
const { db } = fastify
|
|
|
|
fastify.route({
|
|
url: '/',
|
|
method: 'GET',
|
|
async handler() {
|
|
const financialYears = await db.selectFrom('financialYear').selectAll().orderBy('year', 'asc').execute()
|
|
|
|
return db
|
|
.selectFrom('transaction as t')
|
|
.innerJoin('entry as e', 't.entryId', 'e.id')
|
|
.innerJoin('financialYear as fy', 'fy.id', 'e.financialYearId')
|
|
.innerJoin('account as a', 't.accountNumber', 'a.number')
|
|
.select(['t.accountNumber', 'a.description'])
|
|
.select(
|
|
financialYears.map((fy) =>
|
|
sql`SUM(CASE WHEN fy.year = ${fy.year} THEN t.amount ELSE 0 END)`.as(fy.year.toString()),
|
|
),
|
|
)
|
|
.groupBy(['t.accountNumber', 'a.description'])
|
|
.where('t.accountNumber', '>=', 3000)
|
|
.orderBy('t.accountNumber')
|
|
.execute()
|
|
},
|
|
})
|
|
|
|
fastify.route({
|
|
url: '/:year',
|
|
method: 'GET',
|
|
schema: {
|
|
params: z.object({
|
|
year: z.coerce.number(),
|
|
}),
|
|
},
|
|
async handler(request) {
|
|
return (
|
|
db
|
|
.selectFrom('transaction as t')
|
|
.innerJoin('entry as e', 't.entryId', 'e.id')
|
|
.innerJoin('financialYear as fy', 'fy.id', 'e.financialYearId')
|
|
.innerJoin('account as a', 't.accountNumber', 'a.number')
|
|
.select(['t.accountNumber', 'a.description', (eb) => eb.fn.sum('t.amount').as('amount')])
|
|
// .sum('t.amount AS amount')
|
|
.groupBy(['t.accountNumber', 'a.description'])
|
|
.where('t.accountNumber', '>=', 3000)
|
|
.where('year', '=', request.params.year)
|
|
.orderBy('t.accountNumber')
|
|
.execute()
|
|
)
|
|
},
|
|
})
|
|
|
|
done()
|
|
}
|
|
|
|
export default resultRoutes
|