Skip to main content

Calculate Driver Advance Payment ver1

Overview

A cron job that runs every 5 minutes to process queued tasks and generate flat report records from delivered orders within a specified date range. Used for reporting and finance export (e.g. payment file generation).
Task Code Calculate Driver Advance Payment ver1
Schedule Every 5 minutes
Output Table report_driver_advance_payment
Source File src/_cron/cron.service.ts line 5758

Trigger

A MicroserviceTask record with code = Calculate Driver Advance Payment ver1 and status = Pending must exist in the database. The cron picks the oldest pending task each run.

Task Input

The task value field (JSON):

{
  "durationFrom": "01/01/2025 00:00:00",
  "durationTo":   "31/01/2025 23:59:59"
}

Date format: dd/LL/yyyy HH:mm:ss in Asia/Kuala_Lumpur (MYT). Converted to UTC before querying.

Processing Flow

  1. Pick oldest Pending task and mark Running (pessimistic lock, skip locked)
  2. Parse durationFrom and durationTo from MYT, convert to UTC
  3. Query Orders where companyId = 14 (hardcoded), orderStatus = Delivered, unloadedDatetime BETWEEN durationFrom AND durationTo, with joins for latest Job per order (MAX job.id), Driver, Company, DriverBankInfo
  4. For each order, INSERT into report_driver_advance_payment
  5. Finally update task status to Success or Fail

Output Fields

Field Source
orderId order.id
jobId latest job.id
planningDatetime order.pickupDatetime
deliveryDatetime order.unloadedDatetime
isLocal order.isLocal
productName order.productName
transporterName company.companyName
clientName order.clientName
deliveryAddressName order.deliveryAddressName
deliveryState order.deliveryAddressStateName
purchaseOrderNo order.purchaseOrderNo
saleOrderNo order.saleOrderNo
tonnage order.netWeight
orderStatus job.jobStatus
driverFullName job.fullName
plateNo job.plateNo
driverAccountCode bankInfo.accountCode
bankBeneficiaryName bankInfo.bankBeneficiaryName
bankBeneficiaryNric bankInfo.bankBeneficiaryNric
bankBeneficiaryAccountNo bankInfo.bankBeneficiaryAccountNo
createdBy System

Important Notes

  • No duplicate check - if the same task is queued twice for the same date range, duplicate records will be inserted.
  • Company hardcoded - only processes orders for companyId = 14.
  • Date field - uses unloadedDatetime (changed from supplierDoDatetime on 2025-09-19 because not all orders have a supplier DO datetime).
  • deliveryDatetme typo - the entity column name is missing an i and matches the actual DB column name as-is.
  • No bank info guard - if a driver has no DriverBankInfo, all bank-related fields will be null.
Back to top