Skip to main content

Calculate Driver Advance Payment (Ver2)

Overview

A cron job that runs every 5 minutes to calculate and create actual advance payment records per driver, grouped by their completed jobs on a specific date. Uses JobCostSetup to determine payment amounts per route.
Task Code Calculate Driver Advance Payment
Schedule Every 5 minutes
Output Tables driver_advance_payment, driver_advance_payment_job
Source File src/_cron/cron.service.ts line 6209

Trigger

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

Task Input

The task value field (JSON):

{
  "targetDate": "2025-01-31"
}

Date format: yyyy-MM-dd

Processing Flow

  1. Pick oldest Pending task and mark Running
  2. Fetch all existing orderId values from driver_advance_payment_job (dedup list)
  3. Query Jobs where jobStatus = COMPLETED, DATE(order.supplierDoDatetime) = targetDate, and order.id NOT IN existing order IDs
  4. Group jobs by driverId
  5. For each driver:
    • Collect unique routes (fromCountryId to toCountryId)
    • Query JobCostSetup per route where effectiveFrom <= targetDate, costFrequency = BY_JOB or NULL, payTo = DRIVER, isDeleted = false, ordered by effectiveFrom DESC (latest rate wins)
    • Sum amount across jobs x matching costs to get totalAmount
    • INSERT driver_advance_payment record with status PENDING
    • INSERT driver_advance_payment_job per job x matching cost
    • On error: log and continue to next driver
  6. Finally update task status to Success or Fail

Job Type Determination

Determined by determineJobType(fromCountryId, toCountryId):

Condition Job Type
Pickup country = Delivery country LOCAL
Pickup country = Malaysia (id = 1) EXPORT
Otherwise IMPORT

Output: driver_advance_payment

One record per driver per run.

Field Value
driverId driver ID
paymentDate targetDate
totalAmount sum of all applicable job costs
totalJobs count of jobs processed
status PENDING
isAutoGenerated true
createdBy System_Cron

Output: driver_advance_payment_job

One record per job x matching cost setup.

Field Value
driverAdvancePaymentId parent driver_advance_payment.id
driverId driver ID
jobId job ID
orderId order ID
jobCostSetupId matched JobCostSetup.id
jobCostName cost name
jobCostAmount cost amount
jobPayTo DRIVER
jobUnloadedDatetime order.supplierDoDatetime
jobType LOCAL / EXPORT / IMPORT
isDeducted false
createdBy System_Cron

Important Notes

  • Dedup is global - all existing order IDs in driver_advance_payment_job are excluded regardless of date. An order processed in any previous run will never be reprocessed, even if the task is requeued for the same date.
  • Zero-amount record still created - if no matching JobCostSetup is found for a driver, a record is still inserted with totalAmount = 0.
  • Per-driver error isolation - if one driver fails, the loop continues to the next driver; the error is logged but not rethrown.
  • No transaction wrapping - each save() is independent. A mid-loop failure can leave partial records for a driver.
  • Uses supplierDoDatetime - filtering uses supplier_do_datetime, unlike ver1 which uses unloadedDatetime.
Back to top