import { BadRequestException, Inject, Injectable, NotFoundException, forwardRef } from '@nestjs/common'; import { InjectRepository } from '@nestjs/typeorm'; import { Repository } from 'typeorm'; import { ClsService } from 'nestjs-cls'; import { BookingHotelItemEntity } from '../../entities/booking-hotel-item.entity'; import { BookingTransportItemEntity } from '../../entities/booking-transport-item.entity'; import { BookingActivityItemEntity } from '../../entities/booking-activity-item.entity'; import { BookingSpecialItemEntity } from '../../entities/booking-special-item.entity'; import { CustomerInvoiceEntity } from '../../entities/customer-invoice.entity'; import { TaxSlabEntity } from '../../entities/tax-slab.entity'; import { CLS_TENANT_ID } from '../../common/cls/cls.constants'; import { bifurcateInclusive, type TaxSlabSnapshotInput, } from '../../common/services/tax-bifurcation.helper'; import { TaxLedgerGroupBy, TaxLedgerQueryDto } from './dto/tax-ledger-query.dto'; import { ReceivablesParty } from './dto/aging-query.dto'; import { CustomerInvoiceRepository } from './repositories/customer-invoice.repository'; import { BookingService } from '../booking/booking.service'; import { allocateReceiptNumber } from '../payment-settlement/receipt-sequence.helper'; import { pickPrimaryPeriod, sumTotalRooms, } from '../../common/hotel/hotel-primary-period'; /** * One canonical row inside the aggregated response. All money values * already reflect cancellations & refunds (negative rows pre-merged * into the totals when include_cancelled is true). */ export interface TaxLedgerRow { group_key: string; group_label: string; // Optional secondary label — used by booking rows ("guest name"), // supplier rows (tax slab name), component rows (slab name). group_sublabel?: string; count: number; taxable_base: number; tax_collected: number; // Supplier-side tax. Best-effort: read from cost_tax_snapshot where // present; fall back to 0 if the line has no supplier slab captured. supplier_tax: number; // net = tax_collected - supplier_tax. Positive = payable to FTA, // negative = recoverable. net_tax: number; // Effective rate — derived from totals so a mixed component bucket // shows the weighted average, not just the first slab's rate. effective_rate: number; // Whether the row aggregates only refunded amounts. Used by the FE // to render a "Refund" pill — the underlying numbers are already // negative, so no further math required. refund_only?: boolean; } export interface TaxLedgerResponse { rows: TaxLedgerRow[]; totals: { count: number; taxable_base: number; tax_collected: number; supplier_tax: number; net_tax: number; }; } interface NormalisedLine { // Booking / item context — used by every group_by. booking_id: string; booking_number: string; guest_name: string; start_date: string; destination_id: string | null; // Service axis — drives the "by service" tab. service: 'hotel' | 'transport' | 'activity' | 'special' | 'service_charge'; // Supplier axis — id when assigned to a registered supplier, free- // text label otherwise, or null when no supplier on the line yet. supplier_id: string | null; supplier_label: string; // Pre-computed money already adjusted for cancellations / refunds. taxable_base: number; tax_collected: number; supplier_tax: number; components: Array<{ name: string; amount: number }>; // When true the line is a refund "ghost" row contributing negative // money — used to emit the optional refund_only pill in the response. is_refund: boolean; } @Injectable() export class AccountingService { constructor( private readonly cls: ClsService, @InjectRepository(BookingHotelItemEntity) private readonly hotelRepo: Repository, @InjectRepository(BookingTransportItemEntity) private readonly transportRepo: Repository, @InjectRepository(BookingActivityItemEntity) private readonly activityRepo: Repository, @InjectRepository(BookingSpecialItemEntity) private readonly specialRepo: Repository, private readonly invoices: CustomerInvoiceRepository, @Inject(forwardRef(() => BookingService)) private readonly bookingService: BookingService, ) {} private getTenantId(): string { const t = this.cls.get(CLS_TENANT_ID); if (!t) throw new Error('No tenant in context'); return t; } async getTaxLedger(query: TaxLedgerQueryDto): Promise { const tenantId = this.getTenantId(); const includeCancelled = query.include_cancelled !== 'false'; const lines = await this.collectLines(tenantId, query, includeCancelled); const groupBy = query.group_by || TaxLedgerGroupBy.SERVICE; return this.aggregate(lines, groupBy); } /** * Pull lines from all four item tables, join start_date from the * booking row, and convert each line into a NormalisedLine carrying * its taxable base + tax collected + supplier tax + per-component * split. Cancellation handling: a partial cancellation produces TWO * lines — the gross original (still positive) and a refund line * with the cancelled-pax share flipped negative. */ private async collectLines( tenantId: string, query: TaxLedgerQueryDto, includeCancelled: boolean, ): Promise { const out: NormalisedLine[] = []; // ── Hotels ── const hotels = await this.hotelRepo .createQueryBuilder('hi') .innerJoinAndSelect('hi.booking', 'b') .leftJoinAndSelect('b.query', 'q') .leftJoinAndSelect('hi.supplier', 'sup') .leftJoinAndSelect('hi.hotel', 'h') .where('hi.tenant_id = :tenantId', { tenantId }) .andWhere('hi.is_deleted = false') .andWhere('b.is_deleted = false') // Only count confirmed/converted bookings — exclude cancelled // bookings from the gross ledger (their partial cancellations // still emit sign-flipped refund rows via cancelled_count per // activity item below, so the net liability stays accurate). // Queries / unconverted quotes never appear here because the // booking_*_items tables only carry rows for actual bookings. .andWhere('b.status != :cancelled', { cancelled: 'cancelled' }) .andWhere('q.start_date BETWEEN :from AND :to', { from: query.from, to: query.to }) .andWhere(query.destination_id ? 'q.destination_id = :destId' : '1=1', { destId: query.destination_id, }) .getMany(); for (const hi of hotels) { const subtotal = Number(hi.subtotal) || 0; const split = this.bifurcate(subtotal, hi.tax_snapshot); const cost = Number(hi.actual_supplier_cost) || 0; const supplierTax = this.bifurcate(cost, null).tax; // hotels don't carry cost_tax_snapshot yet out.push({ booking_id: hi.booking_id, booking_number: hi.booking?.booking_number || '', guest_name: hi.booking?.query?.guest_name || '', start_date: (hi.booking?.query?.start_date as unknown as string) || '', destination_id: hi.booking?.query?.destination_id ?? null, service: 'hotel', supplier_id: hi.supplier_id ?? null, supplier_label: hi.supplier?.name || hi.supplier_name_text || '— Unassigned —', taxable_base: split.base, tax_collected: split.tax, supplier_tax: supplierTax, components: split.components, is_refund: false, }); } // ── Transport ── const transports = await this.transportRepo .createQueryBuilder('ti') .innerJoinAndSelect('ti.booking', 'b') .leftJoinAndSelect('b.query', 'q') .leftJoinAndSelect('ti.supplier', 'sup') .where('ti.tenant_id = :tenantId', { tenantId }) .andWhere('ti.is_deleted = false') .andWhere('b.is_deleted = false') // Only count confirmed/converted bookings — exclude cancelled // bookings from the gross ledger (their partial cancellations // still emit sign-flipped refund rows via cancelled_count per // activity item below, so the net liability stays accurate). // Queries / unconverted quotes never appear here because the // booking_*_items tables only carry rows for actual bookings. .andWhere('b.status != :cancelled', { cancelled: 'cancelled' }) .andWhere('q.start_date BETWEEN :from AND :to', { from: query.from, to: query.to }) .andWhere(query.destination_id ? 'q.destination_id = :destId' : '1=1', { destId: query.destination_id, }) .getMany(); for (const ti of transports) { const subtotal = Number(ti.subtotal) || 0; const split = this.bifurcate(subtotal, ti.tax_snapshot); const cost = Number(ti.actual_supplier_cost) || 0; const supplierTax = this.bifurcate(cost, ti.cost_tax_snapshot).tax; out.push({ booking_id: ti.booking_id, booking_number: ti.booking?.booking_number || '', guest_name: ti.booking?.query?.guest_name || '', start_date: (ti.booking?.query?.start_date as unknown as string) || '', destination_id: ti.booking?.query?.destination_id ?? null, service: 'transport', supplier_id: ti.supplier_id ?? null, supplier_label: ti.supplier?.name || ti.supplier_name_text || '— Unassigned —', taxable_base: split.base, tax_collected: split.tax, supplier_tax: supplierTax, components: split.components, is_refund: false, }); } // ── Activities ── const activities = await this.activityRepo .createQueryBuilder('ai') .innerJoinAndSelect('ai.booking', 'b') .leftJoinAndSelect('b.query', 'q') .leftJoinAndSelect('ai.supplier', 'sup') .where('ai.tenant_id = :tenantId', { tenantId }) .andWhere('ai.is_deleted = false') .andWhere('b.is_deleted = false') // Only count confirmed/converted bookings — exclude cancelled // bookings from the gross ledger (their partial cancellations // still emit sign-flipped refund rows via cancelled_count per // activity item below, so the net liability stays accurate). // Queries / unconverted quotes never appear here because the // booking_*_items tables only carry rows for actual bookings. .andWhere('b.status != :cancelled', { cancelled: 'cancelled' }) .andWhere('q.start_date BETWEEN :from AND :to', { from: query.from, to: query.to }) .andWhere(query.destination_id ? 'q.destination_id = :destId' : '1=1', { destId: query.destination_id, }) .getMany(); for (const ai of activities) { const subtotal = Number(ai.subtotal) || 0; const qty = Number(ai.quantity) || 0; const cancelled = Number(ai.cancelled_count) || 0; // Active share = (qty - cancelled) / qty; cancelled share emits // as a refund line (negative). Both reference the same booking // so the by-booking grouping nets out correctly. const activeRatio = qty > 0 ? Math.max(0, qty - cancelled) / qty : 1; const activeAmt = subtotal * activeRatio; const split = this.bifurcate(activeAmt, ai.tax_snapshot); const cost = Number(ai.actual_supplier_cost) || 0; const supplierTax = this.bifurcate(cost * activeRatio, ai.cost_tax_snapshot).tax; out.push({ booking_id: ai.booking_id, booking_number: ai.booking?.booking_number || '', guest_name: ai.booking?.query?.guest_name || '', start_date: (ai.booking?.query?.start_date as unknown as string) || '', destination_id: ai.booking?.query?.destination_id ?? null, service: 'activity', supplier_id: ai.supplier_id ?? null, supplier_label: ai.supplier?.name || ai.supplier_name_text || '— Unassigned —', taxable_base: split.base, tax_collected: split.tax, supplier_tax: supplierTax, components: split.components, is_refund: false, }); // Refund / cancellation ghost line — only when includeCancelled. if (includeCancelled && cancelled > 0 && qty > 0) { const refundRatio = cancelled / qty; const refundSplit = this.bifurcate(-subtotal * refundRatio, ai.tax_snapshot); const refundSupplierTax = this.bifurcate( -cost * refundRatio, ai.cost_tax_snapshot, ).tax; out.push({ booking_id: ai.booking_id, booking_number: ai.booking?.booking_number || '', guest_name: ai.booking?.query?.guest_name || '', start_date: (ai.booking?.query?.start_date as unknown as string) || '', destination_id: ai.booking?.query?.destination_id ?? null, service: 'activity', supplier_id: ai.supplier_id ?? null, supplier_label: ai.supplier?.name || ai.supplier_name_text || '— Unassigned —', taxable_base: refundSplit.base, tax_collected: refundSplit.tax, supplier_tax: refundSupplierTax, components: refundSplit.components, is_refund: true, }); } } // ── Specials ── const specials = await this.specialRepo .createQueryBuilder('si') .innerJoinAndSelect('si.booking', 'b') .leftJoinAndSelect('b.query', 'q') .where('si.tenant_id = :tenantId', { tenantId }) .andWhere('si.is_deleted = false') .andWhere('b.is_deleted = false') // Only count confirmed/converted bookings — exclude cancelled // bookings from the gross ledger (their partial cancellations // still emit sign-flipped refund rows via cancelled_count per // activity item below, so the net liability stays accurate). // Queries / unconverted quotes never appear here because the // booking_*_items tables only carry rows for actual bookings. .andWhere('b.status != :cancelled', { cancelled: 'cancelled' }) .andWhere('q.start_date BETWEEN :from AND :to', { from: query.from, to: query.to }) .andWhere(query.destination_id ? 'q.destination_id = :destId' : '1=1', { destId: query.destination_id, }) .getMany(); for (const si of specials) { const subtotal = Number(si.total_price) || 0; const split = this.bifurcate(subtotal, si.tax_snapshot); out.push({ booking_id: si.booking_id, booking_number: si.booking?.booking_number || '', guest_name: si.booking?.query?.guest_name || '', start_date: (si.booking?.query?.start_date as unknown as string) || '', destination_id: si.booking?.query?.destination_id ?? null, service: 'special', supplier_id: null, supplier_label: '— Internal —', taxable_base: split.base, tax_collected: split.tax, supplier_tax: 0, components: split.components, is_refund: false, }); } // ── Service charge (markup) — tax is on the markup only ── // Booking-level: base = markup, tax = markup_tax_amount, both // converted from selling to line (destination) currency. Raw SQL so // the tenant filter is explicit (tenancy.md caution). const serviceRows: Array<{ id: string; booking_number: string; guest_name: string | null; start_date: string; destination_id: string | null; selling_total: string; markup_tax_amount: string; exchange_rate: string; grand_total: string; tax_name: string | null; }> = await this.specialRepo.manager.query( `SELECT b.id, b.booking_number, q.guest_name, q.start_date, q.destination_id, b.selling_total, b.markup_tax_amount, b.exchange_rate, b.grand_total, tt.name AS tax_name FROM bookings b JOIN queries q ON q.id = b.query_id AND q.tenant_id = b.tenant_id LEFT JOIN tax_slabs ts ON ts.id = b.tax_slab_id AND ts.tenant_id = b.tenant_id LEFT JOIN tax_types tt ON tt.id = ts.tax_type_id WHERE b.tenant_id = $1 AND b.is_deleted = false AND b.status <> 'cancelled' AND b.markup_tax_amount > 0 AND q.start_date BETWEEN $2 AND $3 AND ($4::uuid IS NULL OR q.destination_id = $4::uuid)`, [tenantId, query.from, query.to, query.destination_id || null], ); for (const r of serviceRows) { const er = Number(r.exchange_rate) || 1; const tax = +(Number(r.markup_tax_amount) / er).toFixed(2); const base = +( (Number(r.selling_total) - Number(r.markup_tax_amount)) / er - Number(r.grand_total) ).toFixed(2); out.push({ booking_id: r.id, booking_number: r.booking_number || '', guest_name: r.guest_name || '', start_date: r.start_date || '', destination_id: r.destination_id, service: 'service_charge', supplier_id: null, supplier_label: '— Internal —', taxable_base: base, tax_collected: tax, supplier_tax: 0, components: [{ name: r.tax_name || 'Tax', amount: tax }], is_refund: false, }); } // Tax-type filter applied after bifurcation so we can inspect the // snapshot's slab_id. Keep lines whose snapshot matches the // requested tax_type_id; lines with no snapshot fall out. if (query.tax_type_id) { // Future: thread tax_type_id through the bifurcate helper so we // can scope by tax type rather than slab id. For now we don't // apply this filter inline — left as a no-op so the query param // shape is forward-compatible with the FE. } return out; } /** * Bifurcate an inclusive amount through the line's snapshot. Wraps * the existing helper so the service stays declarative. */ private bifurcate( inclusiveAmount: number, snapshot: Record | null | undefined, ): { base: number; tax: number; components: Array<{ name: string; amount: number }> } { if (!snapshot) return { base: inclusiveAmount, tax: 0, components: [] }; const slab = snapshot as unknown as TaxSlabSnapshotInput; const res = bifurcateInclusive(inclusiveAmount, slab, 1); return { base: res.base, tax: res.tax_amount, components: res.components.map((c) => ({ name: c.name, amount: c.amount })), }; } private aggregate(lines: NormalisedLine[], groupBy: TaxLedgerGroupBy): TaxLedgerResponse { const buckets = new Map(); const addToBucket = ( key: string, label: string, sublabel: string | undefined, line: NormalisedLine, taxOverride?: number, baseOverride?: number, ) => { const existing = buckets.get(key) || ({ group_key: key, group_label: label, group_sublabel: sublabel, count: 0, taxable_base: 0, tax_collected: 0, supplier_tax: 0, net_tax: 0, effective_rate: 0, _refunded: false, _normal: false, } as TaxLedgerRow & { _refunded: boolean; _normal: boolean }); existing.count += 1; existing.taxable_base += baseOverride ?? line.taxable_base; existing.tax_collected += taxOverride ?? line.tax_collected; existing.supplier_tax += line.supplier_tax; if (line.is_refund) existing._refunded = true; else existing._normal = true; buckets.set(key, existing); }; for (const line of lines) { switch (groupBy) { case TaxLedgerGroupBy.SERVICE: { addToBucket(line.service, this.serviceLabel(line.service), undefined, line); break; } case TaxLedgerGroupBy.BOOKING: { addToBucket(line.booking_id, line.booking_number, line.guest_name, line); break; } case TaxLedgerGroupBy.SUPPLIER: { const key = line.supplier_id || `text:${line.supplier_label}`; addToBucket(key, line.supplier_label, this.serviceLabel(line.service), line); break; } case TaxLedgerGroupBy.COMPONENT: { // One bucket per component name (VAT / CGST / SGST). Tax is // the per-component slice; base is the line's base distributed // proportionally so the rate column adds up. for (const c of line.components) { const compShare = line.tax_collected !== 0 ? c.amount / line.tax_collected : 0; addToBucket( c.name.toLowerCase(), c.name, undefined, line, c.amount, line.taxable_base * compShare, ); } break; } } } const rows: TaxLedgerRow[] = Array.from(buckets.values()).map((b) => { const net = b.tax_collected - b.supplier_tax; const rate = b.taxable_base !== 0 ? (b.tax_collected / b.taxable_base) * 100 : 0; return { group_key: b.group_key, group_label: b.group_label, group_sublabel: b.group_sublabel, count: b.count, taxable_base: round(b.taxable_base), tax_collected: round(b.tax_collected), supplier_tax: round(b.supplier_tax), net_tax: round(net), effective_rate: round(rate), refund_only: b._refunded && !b._normal, }; }); const totals = rows.reduce( (acc, r) => ({ count: acc.count + r.count, taxable_base: acc.taxable_base + r.taxable_base, tax_collected: acc.tax_collected + r.tax_collected, supplier_tax: acc.supplier_tax + r.supplier_tax, net_tax: acc.net_tax + r.net_tax, }), { count: 0, taxable_base: 0, tax_collected: 0, supplier_tax: 0, net_tax: 0 }, ); totals.taxable_base = round(totals.taxable_base); totals.tax_collected = round(totals.tax_collected); totals.supplier_tax = round(totals.supplier_tax); totals.net_tax = round(totals.net_tax); // Sort: largest net tax first so the most material rows lead. rows.sort((a, b) => Math.abs(b.net_tax) - Math.abs(a.net_tax)); return { rows, totals }; } private serviceLabel(s: NormalisedLine['service']): string { if (s === 'service_charge') return 'Service charge'; return s.charAt(0).toUpperCase() + s.slice(1) + (s === 'activity' ? '' : 's'); } // ────────────────────────────────────────────────────────────── // Tier-1 Accounting reports — all read-only aggregations off the // payment_settlements + bookings tables. No new storage; each one // is a different slice of the same fact data finance asks for at // month-end. // ────────────────────────────────────────────────────────────── /** * AR Aging — outstanding with days-past-due buckets, grouped by the * requested party lens (§12d Outstanding Report): * * CUSTOMER keyed by (guest_name, guest_mobile) — the traveller * AGENT keyed by trip_source — the channel that sold the trip * * Aging is measured from settlement.due_date against `as_of`. * * Outstanding = sum(amount - amount_settled) on rows where * party_type=CUSTOMER, type=COLLECTION, status != SETTLED. (The * settlement is always raised against the customer; the agent lens * re-groups the same rows by who introduced the booking.) * * `guest_name` / `guest_mobile` stay populated in both modes so * existing consumers keep working — in AGENT mode `guest_name` * carries the source name and `guest_mobile` is blank. `party_name` * is the mode-agnostic label for new callers. * * Buckets: * current — due in the future (due_date >= today) * d_0_30 — 0-30 days past due * d_31_60 — 31-60 days past due * d_61_90 — 61-90 days past due * d_90p — 90+ days past due */ async getReceivablesAging(input: { as_of?: string; party?: ReceivablesParty; }): Promise<{ party: ReceivablesParty; rows: Array<{ party_name: string; guest_name: string; guest_mobile: string; currency_code: string; total_outstanding: number; current: number; d_0_30: number; d_31_60: number; d_61_90: number; d_90p: number; }>; totals: { total_outstanding: number; current: number; d_0_30: number; d_31_60: number; d_61_90: number; d_90p: number; }; }> { const tenantId = this.cls.get(CLS_TENANT_ID); const asOf = input.as_of ?? new Date().toISOString().slice(0, 10); const party = input.party ?? ReceivablesParty.CUSTOMER; const isAgent = party === ReceivablesParty.AGENT; // Two grouping shapes over one fact set. Agent mode collapses the // guest columns so a source with many travellers reads as one row. const nameExpr = isAgent ? `COALESCE(ts.name, 'Direct / No agent')` : `q.guest_name`; const contactExpr = isAgent ? `''` : `q.guest_mobile`; const groupBy = isAgent ? `ts.name, c.code` : `q.guest_name, q.guest_mobile, c.code`; const sourceJoin = isAgent ? `LEFT JOIN trip_sources ts ON ts.id = q.source_id AND ts.is_deleted = false` : ''; const rows = (await this.hotelRepo.manager.query( `SELECT ${nameExpr} AS guest_name, ${contactExpr} AS guest_mobile, COALESCE(c.code, '') AS currency_code, SUM(ps.amount - ps.amount_settled) AS total_outstanding, SUM(CASE WHEN ps.due_date >= $2::date THEN ps.amount - ps.amount_settled ELSE 0 END) AS current, SUM(CASE WHEN ps.due_date < $2::date AND ($2::date - ps.due_date) BETWEEN 0 AND 30 THEN ps.amount - ps.amount_settled ELSE 0 END) AS d_0_30, SUM(CASE WHEN ($2::date - ps.due_date) BETWEEN 31 AND 60 THEN ps.amount - ps.amount_settled ELSE 0 END) AS d_31_60, SUM(CASE WHEN ($2::date - ps.due_date) BETWEEN 61 AND 90 THEN ps.amount - ps.amount_settled ELSE 0 END) AS d_61_90, SUM(CASE WHEN ($2::date - ps.due_date) > 90 THEN ps.amount - ps.amount_settled ELSE 0 END) AS d_90p FROM payment_settlements ps INNER JOIN bookings b ON b.id = ps.booking_id AND b.is_deleted = false INNER JOIN queries q ON q.id = b.query_id AND q.is_deleted = false ${sourceJoin} LEFT JOIN currencies c ON c.id = ps.currency_id WHERE ps.tenant_id = $1 AND ps.is_deleted = false AND ps.party_type = 'CUSTOMER' AND ps.type = 'COLLECTION' AND ps.status <> 'SETTLED' AND ps.amount > ps.amount_settled GROUP BY ${groupBy} ORDER BY total_outstanding DESC NULLS LAST`, [tenantId, asOf], )) as Array>; const out = rows.map((r) => ({ party_name: r.guest_name ?? '', guest_name: r.guest_name ?? '', guest_mobile: r.guest_mobile ?? '', currency_code: r.currency_code ?? '', total_outstanding: Number(r.total_outstanding) || 0, current: Number(r.current) || 0, d_0_30: Number(r.d_0_30) || 0, d_31_60: Number(r.d_31_60) || 0, d_61_90: Number(r.d_61_90) || 0, d_90p: Number(r.d_90p) || 0, })); const totals = out.reduce( (acc, r) => ({ total_outstanding: acc.total_outstanding + r.total_outstanding, current: acc.current + r.current, d_0_30: acc.d_0_30 + r.d_0_30, d_31_60: acc.d_31_60 + r.d_31_60, d_61_90: acc.d_61_90 + r.d_61_90, d_90p: acc.d_90p + r.d_90p, }), { total_outstanding: 0, current: 0, d_0_30: 0, d_31_60: 0, d_61_90: 0, d_90p: 0 }, ); return { party, rows: out, totals }; } /** * AP Aging — mirror of AR, grouped by supplier. Supplier-side rows * are party_type=SUPPLIER + type=REFUND ("we owe them"). Same * bucket boundaries so the two reports read symmetrically. */ async getPayablesAging(input: { as_of?: string; }): Promise<{ rows: Array<{ supplier_id: string; supplier_name: string; currency_code: string; total_outstanding: number; current: number; d_0_30: number; d_31_60: number; d_61_90: number; d_90p: number; }>; totals: { total_outstanding: number; current: number; d_0_30: number; d_31_60: number; d_61_90: number; d_90p: number; }; }> { const tenantId = this.cls.get(CLS_TENANT_ID); const asOf = input.as_of ?? new Date().toISOString().slice(0, 10); const rows = (await this.hotelRepo.manager.query( `SELECT ps.supplier_id AS supplier_id, COALESCE(sup.name, 'Unassigned') AS supplier_name, COALESCE(c.code, '') AS currency_code, SUM(ps.amount - ps.amount_settled) AS total_outstanding, SUM(CASE WHEN ps.due_date >= $2::date THEN ps.amount - ps.amount_settled ELSE 0 END) AS current, SUM(CASE WHEN ps.due_date < $2::date AND ($2::date - ps.due_date) BETWEEN 0 AND 30 THEN ps.amount - ps.amount_settled ELSE 0 END) AS d_0_30, SUM(CASE WHEN ($2::date - ps.due_date) BETWEEN 31 AND 60 THEN ps.amount - ps.amount_settled ELSE 0 END) AS d_31_60, SUM(CASE WHEN ($2::date - ps.due_date) BETWEEN 61 AND 90 THEN ps.amount - ps.amount_settled ELSE 0 END) AS d_61_90, SUM(CASE WHEN ($2::date - ps.due_date) > 90 THEN ps.amount - ps.amount_settled ELSE 0 END) AS d_90p FROM payment_settlements ps -- Exclude payables on soft-deleted bookings: a partially-paid -- supplier settlement survives the booking soft-delete cascade -- (money-safe), so without this join its outstanding would still -- show in AP aging. Mirrors the AR side (getReceivablesAging). INNER JOIN bookings b ON b.id = ps.booking_id AND b.is_deleted = false LEFT JOIN suppliers sup ON sup.id = ps.supplier_id LEFT JOIN currencies c ON c.id = ps.currency_id WHERE ps.tenant_id = $1 AND ps.is_deleted = false AND ps.party_type = 'SUPPLIER' AND ps.type = 'REFUND' AND ps.status <> 'SETTLED' AND ps.amount > ps.amount_settled GROUP BY ps.supplier_id, sup.name, c.code ORDER BY total_outstanding DESC NULLS LAST`, [tenantId, asOf], )) as Array>; const out = rows.map((r) => ({ supplier_id: r.supplier_id ?? '', supplier_name: r.supplier_name ?? 'Unassigned', currency_code: r.currency_code ?? '', total_outstanding: Number(r.total_outstanding) || 0, current: Number(r.current) || 0, d_0_30: Number(r.d_0_30) || 0, d_31_60: Number(r.d_31_60) || 0, d_61_90: Number(r.d_61_90) || 0, d_90p: Number(r.d_90p) || 0, })); const totals = out.reduce( (acc, r) => ({ total_outstanding: acc.total_outstanding + r.total_outstanding, current: acc.current + r.current, d_0_30: acc.d_0_30 + r.d_0_30, d_31_60: acc.d_31_60 + r.d_31_60, d_61_90: acc.d_61_90 + r.d_61_90, d_90p: acc.d_90p + r.d_90p, }), { total_outstanding: 0, current: 0, d_0_30: 0, d_31_60: 0, d_61_90: 0, d_90p: 0 }, ); return { rows: out, totals }; } /** * P&L — period revenue, cost, margin. Revenue = customer collections * actually received in the window. Cost = supplier payouts actually * paid out in the window. Both measured by `settled_date` so a * settlement that crossed periods only counts where the money moved. * * Optional group_by gives a one-level breakdown (destination, source, * or service category). Default rolls up to a single row. */ async getProfitAndLoss(input: { from: string; to: string; group_by?: 'destination' | 'source' | 'service' | null; }): Promise<{ rows: Array<{ group_key: string; group_label: string; revenue: number; cost: number; margin: number; margin_pct: number; }>; totals: { revenue: number; cost: number; margin: number; margin_pct: number }; }> { const tenantId = this.cls.get(CLS_TENANT_ID); const groupBy = input.group_by ?? null; // Pick SELECT shape per group_by. Source = trip_source name from // queries.source_id; service = ps.source_item_type (HOTEL/TRANSPORT/ // ACTIVITY/SPECIAL); destination = q.destination_id → d.name. const grouping = (() => { switch (groupBy) { case 'destination': return { keyExpr: 'd.id', labelExpr: "COALESCE(d.name, 'Unknown')", joinExtra: 'LEFT JOIN destinations d ON d.id = q.destination_id', groupExtra: 'd.id, d.name', }; case 'source': return { keyExpr: 'ts.id', labelExpr: "COALESCE(ts.name, 'Unknown')", joinExtra: 'LEFT JOIN trip_sources ts ON ts.id = q.source_id', groupExtra: 'ts.id, ts.name', }; case 'service': return { keyExpr: "COALESCE(ps.source_item_type, 'UNCLASSIFIED')", labelExpr: "COALESCE(ps.source_item_type, 'Unclassified')", joinExtra: '', groupExtra: 'ps.source_item_type', }; default: return { keyExpr: "'__all__'", labelExpr: "'All'", joinExtra: '', groupExtra: '', }; } })(); const groupByClause = grouping.groupExtra ? `GROUP BY ${grouping.groupExtra}` : ''; // Audit fix (2026-06-29): the prior version aggregated // ps.amount_settled with a settled_date filter, which only counts // FULLY-settled rows and misses every PARTIAL payment that landed // inside the period. Correct semantic is "money that actually moved // in the window" — that's settlement_payments.paid_amount summed // by sp.paid_date. Void payments are excluded. const sql = ` WITH revenue AS ( SELECT ${grouping.keyExpr} AS group_key, ${grouping.labelExpr} AS group_label, SUM(sp.paid_amount) AS amt FROM settlement_payments sp INNER JOIN payment_settlements ps ON ps.id = sp.settlement_id INNER JOIN bookings b ON b.id = ps.booking_id AND b.is_deleted = false INNER JOIN queries q ON q.id = b.query_id AND q.is_deleted = false ${grouping.joinExtra} WHERE sp.tenant_id = $1 AND sp.is_deleted = false AND sp.is_void = false AND ps.party_type = 'CUSTOMER' AND ps.type = 'COLLECTION' AND sp.paid_date BETWEEN $2::date AND $3::date ${groupByClause} ), cost AS ( SELECT ${grouping.keyExpr} AS group_key, ${grouping.labelExpr} AS group_label, SUM(sp.paid_amount) AS amt FROM settlement_payments sp INNER JOIN payment_settlements ps ON ps.id = sp.settlement_id INNER JOIN bookings b ON b.id = ps.booking_id AND b.is_deleted = false INNER JOIN queries q ON q.id = b.query_id AND q.is_deleted = false ${grouping.joinExtra} WHERE sp.tenant_id = $1 AND sp.is_deleted = false AND sp.is_void = false AND ps.party_type = 'SUPPLIER' AND ps.type = 'REFUND' AND sp.paid_date BETWEEN $2::date AND $3::date ${groupByClause} ) SELECT COALESCE(r.group_key, c.group_key) AS group_key, COALESCE(r.group_label, c.group_label) AS group_label, COALESCE(r.amt, 0) AS revenue, COALESCE(c.amt, 0) AS cost FROM revenue r FULL OUTER JOIN cost c ON r.group_key = c.group_key ORDER BY revenue DESC NULLS LAST `; const rawRows = (await this.hotelRepo.manager.query(sql, [tenantId, input.from, input.to])) as Array< Record >; const out = rawRows.map((r) => { const revenue = Number(r.revenue) || 0; const cost = Number(r.cost) || 0; const margin = +(revenue - cost).toFixed(2); const margin_pct = revenue > 0 ? +((margin / revenue) * 100).toFixed(2) : 0; return { group_key: r.group_key ?? '__all__', group_label: r.group_label ?? 'All', revenue: +revenue.toFixed(2), cost: +cost.toFixed(2), margin, margin_pct, }; }); const totals = out.reduce( (acc, r) => ({ revenue: acc.revenue + r.revenue, cost: acc.cost + r.cost }), { revenue: 0, cost: 0 }, ); const margin = +(totals.revenue - totals.cost).toFixed(2); return { rows: out, totals: { revenue: +totals.revenue.toFixed(2), cost: +totals.cost.toFixed(2), margin, margin_pct: totals.revenue > 0 ? +((margin / totals.revenue) * 100).toFixed(2) : 0, }, }; } // ────────────────────────────────────────────────────────────── // Customer Tax Invoice — Tier 1 #105. // // Generates a sequential INV-YYYY-NNNN voucher per booking, with // frozen snapshots of guest + destination + per-line items + // aggregated tax bifurcation. Re-issue allowed: each call creates a // new invoice; FE displays the latest non-void one as the current. // ────────────────────────────────────────────────────────────── /** * Generate (or re-generate) the customer tax invoice for a booking. * Snapshots are stored so the printable invoice stays frozen even * if the booking is edited downstream. */ async generateCustomerInvoice(input: { booking_id: string }) { const booking = await this.bookingService.findById(input.booking_id); if (!booking) throw new NotFoundException('Booking not found'); // The flattenQuery path on findById hydrates guest_*, destination, // currency_id, start_date, end_date directly on the booking object. // eslint-disable-next-line @typescript-eslint/no-explicit-any -- booking is enriched at runtime with flattened query fields const b = booking as any; const currencyId: string | null = b.currency_id ?? b.destination?.currency?.id ?? null; if (!currencyId) { throw new BadRequestException( 'Booking has no currency assigned — cannot generate invoice', ); } // Build a flat snapshot of every billable line. Hotel items expose // total_price, transport / activity expose price * qty, special is // a flat total. Tax_amount lives on each entity (filled at booking // save time by the slab resolver). const lines: NonNullable = []; const taxComponents = new Map< string, { tax_type_id: string; tax_type_name: string; rate: number; amount: number } >(); const addComponents = (snap: Record | null | undefined, taxAmount: number) => { if (!snap || taxAmount === 0) return; // tax_snapshot shape comes from TaxSlabSnapshotInput. const components = (snap as { components?: Array<{ name: string; rate: number }> }).components; const slabId = (snap as { slab_id?: string | null }).slab_id ?? null; const totalRate = (snap as { total_rate?: number }).total_rate ?? 0; if (!components || components.length === 0 || totalRate <= 0) { // Single-component fallback so a flat-rate slab still surfaces. const key = slabId ?? `flat-${totalRate}`; const slabName = (snap as { slab_name?: string }).slab_name ?? 'Tax'; const existing = taxComponents.get(key); if (existing) { existing.amount += taxAmount; } else { taxComponents.set(key, { tax_type_id: slabId ?? '', tax_type_name: slabName, rate: totalRate, amount: taxAmount, }); } return; } for (const comp of components) { const share = totalRate > 0 ? comp.rate / totalRate : 0; const amt = +(taxAmount * share).toFixed(2); const key = `${slabId ?? '_'}::${comp.name}`; const existing = taxComponents.get(key); if (existing) { existing.amount += amt; } else { taxComponents.set(key, { tax_type_id: slabId ?? '', tax_type_name: comp.name, rate: comp.rate, amount: amt, }); } } }; for (const h of b.hotel_items ?? []) { // Hotel line moved to periods + extras — surface the primary // period's room/meal as the line label and the total room count // across all periods as qty. Legacy `h.total_price` / `h.no_of_rooms` // never actually existed on the entity (pre-existing bug — // subtotal + Σ periods.num_rooms are the correct sources). const primary = pickPrimaryPeriod(h.periods); const totalRooms = sumTotalRooms(h.periods); const lineTotal = Number(h.subtotal ?? 0); const label = [primary.room_type, primary.meal_plan].filter(Boolean).join(' · '); lines.push({ kind: 'HOTEL', description: `${h.hotel?.name ?? 'Hotel'}${label ? ' — ' + label : ''}`.trim(), qty: totalRooms, unit_price: lineTotal && totalRooms ? +(lineTotal / totalRooms).toFixed(2) : lineTotal, line_total: lineTotal, }); addComponents(h.tax_snapshot, Number(h.tax_amount ?? 0)); } for (const t of b.transport_items ?? []) { const lineTotal = Number(t.total_price ?? 0); lines.push({ kind: 'TRANSPORT', description: `${t.transport_service?.name ?? 'Transport'}`, qty: Number(t.quantity ?? 1), unit_price: lineTotal && t.quantity ? +(lineTotal / Number(t.quantity)).toFixed(2) : lineTotal, line_total: lineTotal, }); addComponents(t.tax_snapshot, Number(t.tax_amount ?? 0)); } // Activities are stored per (activity, ticket, date, age_group) — merge // into a single invoice line per (activity, ticket, date) so "2A + 2C" // renders as one row instead of duplicating Adult + Child. Same policy // as the booking detail page's itinerary aggregation. const activityMap = new Map< string, { first: any; totalQty: number; lineTotal: number; taxAmount: number; taxSnapshot: any; pax: string[]; } >(); for (const a of b.activity_items ?? []) { const key = `${a.activity_id}|${a.ticket_id || ''}|${a.date || ''}`; if (!activityMap.has(key)) { activityMap.set(key, { first: a, totalQty: 0, lineTotal: 0, taxAmount: 0, taxSnapshot: a.tax_snapshot, pax: [], }); } const group = activityMap.get(key)!; const adultQty = Number(a.adult_count ?? 0); const childQty = Number(a.child_count ?? 0); const itemQty = adultQty + childQty; group.totalQty += itemQty; group.lineTotal += Number(a.subtotal ?? 0); group.taxAmount += Number(a.tax_amount ?? 0); if (adultQty > 0) group.pax.push(`${adultQty} Adult`); if (childQty > 0) group.pax.push(`${childQty} Child`); } for (const [, g] of activityMap.entries()) { const paxSuffix = g.pax.length > 0 ? ` (${g.pax.join(' + ')})` : ''; lines.push({ kind: 'ACTIVITY', description: `${g.first.activity?.name ?? 'Activity'}${g.first.ticket?.name ? ` · ${g.first.ticket.name}` : ''}${paxSuffix}`, qty: g.totalQty || 1, unit_price: g.lineTotal && g.totalQty ? +(g.lineTotal / g.totalQty).toFixed(2) : g.lineTotal, line_total: g.lineTotal, }); addComponents(g.taxSnapshot, g.taxAmount); } for (const s of b.special_items ?? []) { const lineTotal = Number(s.total_price ?? 0); lines.push({ kind: 'SPECIAL', description: s.service_name ?? 'Special service', qty: 1, unit_price: lineTotal, line_total: lineTotal, }); addComponents(s.tax_snapshot, Number(s.tax_amount ?? 0)); } // Service charge (markup) + tax on it. Lines above are cost lines in // the destination currency; selling_total / markup_tax_amount are in // the selling currency, so convert back via exchange_rate. Grand // total then equals what the customer is billed (selling_total). const costTotal = Number(b.grand_total ?? 0); const er = Number(b.exchange_rate) || 1; const sellingTotal = Number(b.selling_total ?? 0); const markupTax = sellingTotal > 0 ? +(Number(b.markup_tax_amount ?? 0) / er).toFixed(2) : 0; const serviceCharge = sellingTotal > 0 ? +(sellingTotal / er - markupTax - costTotal).toFixed(2) : 0; if (Math.abs(serviceCharge) > 0.005) { lines.push({ kind: 'SERVICE', description: serviceCharge > 0 ? 'Service charge' : 'Discount', qty: 1, unit_price: serviceCharge, line_total: serviceCharge, }); } if (markupTax > 0.005) { const slab = b.tax_slab_id ? await this.invoices.manager.findOne(TaxSlabEntity, { where: { id: b.tax_slab_id, tenant_id: this.cls.get(CLS_TENANT_ID) }, relations: { tax_type: true }, }) : null; const name = `${slab?.tax_type?.name ?? 'Tax'} on service charge`; taxComponents.set(`markup::${slab?.id ?? 'flat'}`, { tax_type_id: slab?.tax_type_id ?? '', tax_type_name: name, rate: Number(slab?.total_rate ?? 0), amount: markupTax, }); } const grandTotal = +(costTotal + serviceCharge + markupTax).toFixed(2); const taxTotal = +Array.from(taxComponents.values()) .reduce((sum, c) => sum + c.amount, 0) .toFixed(2); const subtotal = +(grandTotal - taxTotal).toFixed(2); // UAT fallback (2026-06-29): Quick Bookings carry a flat // grand_total with no itemised children. Synthesise one // "Travel Package" line so the printed invoice never reads // "No billable lines" while the customer is being charged a // real amount. if (lines.length === 0 && grandTotal > 0.005) { lines.push({ kind: 'SPECIAL', description: 'Travel Package', qty: 1, unit_price: grandTotal, line_total: grandTotal, }); } // INV-YYYY-NNNN — same sequence object as receipts / vouchers. const today = new Date().toISOString().slice(0, 10); const year = Number(today.slice(0, 4)); const { receipt_no: invoice_no, receipt_year: invoice_year } = await allocateReceiptNumber( this.invoices.manager, this.cls.get(CLS_TENANT_ID)!, year, 'INV', ); const saved = await this.invoices.save({ invoice_no, invoice_year, booking_id: input.booking_id, invoice_date: today, currency_id: currencyId, guest_name_snapshot: b.guest_name ?? null, guest_mobile_snapshot: b.guest_mobile ?? null, guest_email_snapshot: b.guest_email ?? null, destination_name_snapshot: b.destination?.name ?? null, travel_start_snapshot: b.start_date ?? null, travel_end_snapshot: b.end_date ?? null, subtotal: subtotal < 0 ? 0 : subtotal, tax_total: taxTotal, grand_total: grandTotal, tax_bifurcation: Array.from(taxComponents.values()).map((c) => ({ ...c, amount: +c.amount.toFixed(2), })), line_items: lines, notes: null, }); return saved; } async findInvoiceById(id: string) { const inv = await this.invoices.findById(id); if (!inv) throw new NotFoundException('Invoice not found'); return inv; } async findInvoicesForBooking(bookingId: string) { return this.invoices.findForBooking(bookingId); } } function round(n: number): number { return Math.round(n * 100) / 100; }