35 lines
1.0 KiB
TypeScript
35 lines
1.0 KiB
TypeScript
import _ from 'lodash'
|
|
import type { FastifyPluginCallbackZod } from 'fastify-type-provider-zod'
|
|
import { sql } from 'kysely'
|
|
|
|
const balanceRoutes: FastifyPluginCallbackZod = (fastify, _, done) => {
|
|
const { db } = fastify
|
|
|
|
fastify.route({
|
|
url: '/',
|
|
method: 'GET',
|
|
async handler() {
|
|
const financialYears = await db.selectFrom('financialYear').select('year').orderBy('year').execute()
|
|
|
|
return db
|
|
.selectFrom('accountBalance as ab')
|
|
.innerJoin('financialYear as fy', 'fy.id', 'ab.financialYearId')
|
|
.innerJoin('account as a', 'a.number', 'ab.accountNumber')
|
|
.select(['ab.accountNumber', 'a.description'])
|
|
.select(
|
|
financialYears.map((fy) =>
|
|
sql`SUM(CASE WHEN fy.year = ${fy.year} THEN ab."out" ELSE 0 END)`.as(fy.year.toString()),
|
|
),
|
|
)
|
|
.groupBy(['ab.accountNumber', 'a.description'])
|
|
.orderBy('ab.accountNumber')
|
|
.where('ab.accountNumber', '<', 3000)
|
|
.execute()
|
|
},
|
|
})
|
|
|
|
done()
|
|
}
|
|
|
|
export default balanceRoutes
|