Excel Expenditure Reports

Relevant source files

The following files were used as context for generating this wiki page:

• src/services/expenditureExcelService.ts
• src/controllers/reportController.ts
• src/services/reportService.ts
• src/types/report.ts
• comprehensive-swagger-api.json

The Excel Expenditure Reports module is a specialized component of the Outmanage logistics system designed to generate downloadable spreadsheets tracking the movement of goods out of warehouses. This feature enables stakeholders to audit completed shipments, verify quantities against delivery orders, and maintain physical or digital records of "Laporan Barang Keluar" (Outgoing Goods Report) across various timeframes and specific warehouse locations.

The system focuses on "SELESAI" (Completed) shipments where items have reached the "COMPLETED" status and have been formally weighed. This ensures that the financial and operational data reflected in the Excel exports represent actual dispatched stock rather than planned or pending orders.

Sources: src/services/expenditureExcelService.ts:101-105, src/services/reportService.ts:480-485

System Architecture and Data Flow

The reporting engine follows a standard Controller-Service architecture. The reportController handles incoming HTTP requests, validates query parameters, and invokes the expenditureExcelService to perform data aggregation and file generation using the xlsx library.

Report Generation Flow

The following diagram illustrates the sequence of operations from the user request to the file download:

The system utilizes moment-timezone configured to Asia/Jakarta to ensure that date filtering aligns with the local operating environment of the warehouses.

Sources: src/controllers/reportController.ts:148-188, src/services/expenditureExcelService.ts:12-25, src/services/expenditureExcelService.ts:60-100

Data Selection and Transformation Logic

Filtering Criteria

The service supports multiple temporal and physical filters to refine the report output:

Filter	Values	Description
period	daily, monthly, yearly	Determines the temporal aggregation level.
startDate / endDate	ISO Date String	Custom range for daily reports.
year / month	Integer	Required for monthly and yearly period selections.
warehouseId	UUID	Restricts data to a specific warehouse facility.

Sources: src/services/expenditureExcelService.ts:43-50, src/types/report.ts:18-32

Transformation Rules

Before writing to the Excel sheet, raw database entities are transformed into an ExpenditureItem structure. Key business logic applied during this phase includes:

1. Net Weight Calculation: Total weight is derived by reducing all weighings associated with a specific chosen product within a shipment.
2. Expedition Type Mapping:
   • ANTAR: Shipments associated with a formal armada (fleet vehicle).
   • JEMPUT: Shipments without an armada (customer pickup).
3. Completion Filter: Items must have status: 'COMPLETED' and a non-null weighedAt timestamp to be included.

Sources: src/services/expenditureExcelService.ts:150-185, src/services/reportService.ts:550-565

Spreadsheet Structure

The generated workbook follows a standardized layout for professional reporting.

Worksheet Layout

• Worksheet Name: "Laporan Barang Keluar"
• Header: A merged title row identifying the warehouse scope (e.g., "LAPORAN HARIAN BARANG KELUAR SEMUA GUDANG").
• Data Columns:
  1. NO: Incremental row counter.
  2. TANGGAL: Verified date in DD-MM-YYYY format.
  3. NO. SPMB: The code of the associated loading instruction (Surat Perintah Muat Barang).
  4. NAMA CUSTOMER: Name of the client receiving the goods.
  5. NAMA BARANG: Product name.
  6. QTY: Requested quantity.
  7. NETTO (KG): Actual weighed net weight.
  8. NOMOR PLAT: Vehicle license plate number.
  9. EKSPEDISI: Shipping method (ANTAR/JEMPUT).

Formatting and Styling

The service applies programmatic formatting to ensure the document is immediately usable:

• Column Widths: Defined specifically for each field (e.g., Customer Name is wider than Date).
• Cell Merging: Applied to the title across all nine columns (A1:I1).
• Data Types: Numeric values (Qty, Netto) are stored as numbers to facilitate Excel calculations.

Sources: src/services/expenditureExcelService.ts:195-245

File Management and Storage

The system manages report files within a dedicated directory structure.

Path Resolution

• Development: Uses path.join(process.cwd(), 'src', 'public', 'reports').
• Production: Defaults to /var/www/sajpoutbound.com/public/reports.
• Naming Convention: Files are named using the pattern laporan_barang_keluar_{WarehouseName}_{YYYY-MM-DD_HH-mm-ss}.xlsx.

Sources: src/services/expenditureExcelService.ts:17-25, src/services/expenditureExcelService.ts:250-255

API Endpoints

The Excel report is accessible via the reporting controller.

Download Expenditure Excel

• Endpoint: GET /api/reports/expenditure/download
• Description: Generates the file on-the-fly and streams it to the client with appropriate headers.
• Headers:
  • Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
  • Content-Disposition: attachment; filename="..."

Sources: src/controllers/reportController.ts:148-188, comprehensive-swagger-api.json:1150-1180

Conclusion

The Excel Expenditure Reports feature provides a bridge between the digital logistics management system and traditional business documentation requirements. By automating the extraction of weighed shipment data into structured XLSX files, it ensures accuracy in reporting and reduces manual administrative overhead for warehouse managers. The system's strict adherence to completed and verified status prevents discrepancies between physical inventory movement and generated reports.