Skip to main content

Calculate Driver Advance Payment ver2

Overview

This is a cron job executing every 5 minutes that computes and generates advance payment records for drivers based on their delivered orders within a specified Malaysia date range, leveraging JobCostSetup to determine payment rates per route. Scoped to company ID 14 only.
Task Code Calculate Driver Advance Payment
Schedule Every 5 minutes
Company Scope ID = 14 (hardcoded)
Output Tables driver_advance_payment, driver_advance_payment_job
Source File src/_cron/cron.service.ts ~line 6198

Changelog

Date By Change
2026-03-02 sf Scoped to company 14; switched order filter from job.jobStatus/supplierDoDatetime to order.order_status/unloaded_datetime; added MYT→UTC date range derivation; added latest-job-per-order subquery; fixed effectiveFrom comparison to use durationTo; added placeholder driver_advance_payment_job rows for no-cost/no-route-match cases; jobUnloadedDatetime now uses order.unloadedDatetime
2026-02-25 sf Changed job filtering from jobStatus = COMPLETED to jobStatus = Delivered

Previous versions are preserved in Page Revisions (top-right menu → Revisions).

Trigger Mechanism

A MicroserviceTask record must exist with code = Calculate Driver Advance Payment and status = Pending. The cron selects the oldest pending task during each execution cycle.

Task Input

The task value field requires JSON with a target date (Malaysia date):

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

Date format: yyyy-MM-dd. The value represents a Malaysia (MYT, UTC+8) calendar date.

Date Range Derivation (MYT → UTC)

targetDate is a Malaysia calendar date. Since order.unloaded_datetime is stored in UTC, the code converts the full Malaysia day to a UTC range before querying:

Malaysia Time UTC Stored
2025-01-31 00:00:00 MYT 2025-01-30 16:00:00 UTC → durationFrom
2025-01-31 23:59:59 MYT 2025-01-31 15:59:59 UTC → durationTo

Both durationFrom and durationTo are used in the BETWEEN filter and for effectiveFrom comparison.

Processing Flow

  1. Select oldest Pending task
  2. Parse targetDate from task value; derive durationFrom / durationTo (MYT day → UTC)
  3. Retrieve all existing order IDs from driver_advance_payment_job where order.company_id = 14 (deduplication scoped to company 14)
  4. Query Orders (via latest Job per order) matching:
    • order.company_id = 14
    • order.order_status = 'Delivered'
    • order.unloaded_datetime BETWEEN durationFrom AND durationTo
    • Latest job per order (MAX job.id subquery)
    • Joins: Driver (via job.driver_id), Company (via order.company_id)
    • Exclude orders already in driver_advance_payment_job
  5. Group resulting jobs by driver ID (skip jobs with no driver)
  6. Per driver:
    • Identify unique routes (origin → destination country)
    • Fetch matching JobCostSetup records: effective_from <= durationTo, frequency BY_JOB or NULL, recipient DRIVER, is_deleted = false, ordered by effective_from DESC (latest rate wins)
    • Calculate total amount by summing job × matched costs
    • Create driver_advance_payment record with PENDING status, paymentDate = targetDate
    • Per job, create driver_advance_payment_job:
      • Cost matched → real cost fields, jobUnloadedDatetime = order.unloadedDatetime
      • No cost setup → placeholder with null/0 cost fields, jobUnloadedDatetime = order.unloadedDatetime
      • Cost exists but route not matched → same placeholder, jobUnloadedDatetime = order.unloadedDatetime
    • On error: log and continue to next driver
  7. Update task to Success or Fail

Job Type Classification

determineJobType(fromCountryId, toCountryId) assigns:

  • LOCAL — pickup and delivery within same country
  • EXPORT — pickup from Malaysia (id = 1)
  • IMPORT — all other combinations

Output: driver_advance_payment

Single record per driver per execution:

Field Value
driverId from job
paymentDate targetDate (Malaysia date string)
totalAmount sum of matched costs (0 if none)
totalJobs count of jobs for this driver
status PENDING
isAutoGenerated true
createdBy System_Cron

Output: driver_advance_payment_job

One or more records per job (always at least one, even with no cost):

Field Value
driverAdvancePaymentId parent record ID
driverId from job
jobId job.id
orderId job.orderId
jobCostSetupId jcs.id or null (no match)
jobCostName jcs.costName or null
jobCostAmount amount or 0
jobPayTo DRIVER or null
jobUnloadedDatetime order.unloadedDatetime
jobType LOCAL / EXPORT / IMPORT
isDeducted false
createdBy System_Cron

Critical Considerations

  • Company-Scoped Deduplication — Existing order ID exclusion is filtered to company_id = 14; orders from other companies are unaffected
  • Always Tracked — Every delivered order always produces at least one driver_advance_payment_job row (placeholder if no cost), preventing duplicate processing on subsequent cron runs
  • Zero-Amount Payments — Records generate even without matching cost setups (totalAmount = 0), with placeholder job rows
  • Malaysia Date Handling — targetDate is MYT; queries use UTC-converted range to correctly capture all records within that calendar day
  • effectiveFrom Comparison — Uses durationTo (end of MYT day in UTC), not the raw targetDate string, to avoid missing costs set up within the same Malaysia day
  • Latest Job per Order — Only the highest job.id per order is processed (one advance payment per order)
  • Driver-Level Error Isolation — Individual driver failures don't halt processing; errors are logged and processing continues
  • No Transaction Protection — Each save is independent; mid-loop failures may create partial records
Back to top