alla-tms/docs/sprint-7-phase-1-reports-review.md

Sprint 7 Phase 1 Reports Review

Summary

Sprint 7 Phase 1 was implemented on top of the existing reports module without changing the project structure or ORM setup.

This phase adds four report views:

1. Employee Training Transcript
2. Training Matrix
3. Department Summary
4. Annual Training Summary

The implementation now uses database-side aggregation for summary-style reports and keeps employee access scoped to their own transcript through the existing Auth.js organization session.

No database migration was required.

Files Changed

• src/app/dashboard/reports/page.tsx
• src/app/api/reports/export/route.ts
• src/config/nav-config.ts
• src/features/reports/api/types.ts
• src/features/reports/components/report-table-card.tsx
• src/features/reports/components/reports-page-content.tsx
• src/features/reports/server/report-data.ts
• src/assets/fonts/THSarabunNew.ttf
• package.json
• package-lock.json

Database Queries Added

The reports layer now uses SQL and Drizzle-based aggregation instead of relying on in-memory report assembly for the Sprint 7 report set.

Added query patterns:

1. Employee transcript query filtered by:

   • organization
   • year
   • company
   • department
   • current employee scope for non-HRD users

2. Department summary aggregation:

   • employee count per department
   • training record count per department
   • approved / pending / rejected counts
   • approved hour totals

3. Annual training summary aggregation:

   • grouped by month
   • submitted / approved / pending / rejected counts
   • submitted hours
   • approved hours

4. Training matrix compliance SQL:

   • employee scope CTE
   • required assignment CTE
   • latest approved record CTE
   • final compliance summary per employee

Reports Added

1. Employee Training Transcript

• Available to all authenticated employees with organization access
• Employees see only their own records
• HRD/admin can see organization-wide results

2. Training Matrix

• HRD/admin only
• Shows required courses, completed courses, compliance percentage, and missing courses

3. Department Summary

• HRD/admin only
• Shows employee totals, record totals, status totals, and approved hours by department

4. Annual Training Summary

• HRD/admin only
• Shows monthly reporting and approval totals for the selected year

Export Features

Added per-report export actions for:

• Excel (.xlsx)
• PDF (.pdf)

Implementation details:

• Excel export uses xlsx
• PDF export uses pdf-lib
• Thai PDF output uses embedded font file:
  • src/assets/fonts/THSarabunNew.ttf

Supported export route:

• /api/reports/export

Supported query params:

• report
• format
• year
• company
• departmentId

Permission Rules

Employee

• Can open /dashboard/reports
• Can view only Employee Training Transcript
• Can export only Employee Training Transcript

HRD/Admin

• Can open /dashboard/reports
• Can view all four reports organization-wide
• Can export all four reports

Server-side Enforcement

• Export route validates report type and format
• Export route blocks non-HRD users from exporting admin-only reports
• Dataset queries are scoped by current organization and session user

Responsive Notes

• Filter area uses responsive grid layout
• Export actions stack on small screens and align horizontally on larger screens
• Report tables remain horizontally scrollable on mobile/tablet
• Summary cards collapse cleanly from 4 columns down to smaller breakpoints

Manual Test Checklist

1. Sign in as employee and open /dashboard/reports
2. Confirm only transcript report is visible
3. Confirm transcript data contains only the current employee records
4. Export employee transcript to Excel
5. Export employee transcript to PDF
6. Sign in as HRD/admin and open /dashboard/reports
7. Filter by year and verify transcript, matrix, department summary, and annual summary update
8. Filter by company and verify results update
9. Filter by department and verify results update
10. Export each report to Excel
11. Export each report to PDF
12. Confirm sidebar shows Reports for employee and HRD roles
13. Confirm direct export access to admin-only reports returns forbidden for employee role

Known Limitations

1. PDF export is formatted as a lightweight text/table document, not a branded print layout.
2. The Training Matrix report depends on current matrix assignments and latest approved records before the selected year end.
3. Lint still reports pre-existing warnings in unrelated files outside this Sprint 7 scope.

Sprint 7.2 Recommendation

Recommended next improvements:

1. Add chart-based visualization for annual and department summaries
2. Add background export jobs if report volume grows significantly
3. Add report presets and saved filters
4. Add richer PDF layout with pagination headers, footer, and branding
5. Consider moving the reports page to query-backed client filters only if UX requires instant filter changes

Migration Commands

No migration commands are required for Sprint 7 Phase 1.