Calculate Driver Advance Payment ver2

Version: Draft — Changes pending production deployment.

---

Overview

AThis is a cron job that runsexecuting every 5 minutes tothat calculatecomputes and create actualgenerates advance payment records perfor driver,drivers groupedbased byon their completeddelivered jobsorders onwithin a specificspecified date.Malaysia Usesdate range, leveraging JobCostSetup to determine payment amountsrates 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 62096198

---

Changelog

Date	AuthorBy	DescriptionChange
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 filterfiltering 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 must exist in the database.. The cron picksselects the oldest pending task during each run.execution cycle.

---

Task Input

The task value field requires JSON with a target date (JSON)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. PickSelect oldest Pending task and mark Running
2. FetchParse all existing orderId valuestargetDate from driver_advance_payment_jobtask value; derive durationFrom / durationTo (dedupMYT list)day → UTC)
3. QueryRetrieve Jobs where jobStatus = Delivered, DATE(order.supplierDoDatetime) = targetDate, and order.id NOT INall 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 driverIddriver ID (skip jobs with no driver)
6. For eachPer driver:
   • CollectIdentify unique routes (fromCountryIdorigin to→ toCountryId)destination country)
   • QueryFetch matching JobCostSetup perrecords: route where effectiveFromeffective_from <= targetDate,durationTo, costFrequencyfrequency BY_JOB or NULL, recipient DRIVER, is_deleted = BY_JOB or NULL, payTo = DRIVER, isDeleted = false,false, ordered by effectiveFromeffective_from DESC (latest rate wins)
   • SumCalculate total amount acrossby jobssumming xjob matching× matched costs to get totalAmount
   • INSERTCreate driver_advance_payment record with statusPENDING PENDINGstatus, paymentDate = targetDate
   • INSERTPer job, create driver_advance_payment_job: per
     job
     • Cost xmatched matching→ 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. Finally updateUpdate task status to Success or Fail

---

Job Type DeterminationClassification

Determined by determineJobType(fromCountryId, toCountryId): assigns:

LOCAL

—pickupdeliverywithin pickup

IMPORT

—allothercombinations

Condition	Joband Type
Pickupsame country = EXPORT Delivery— country	LOCAL
Pickup country =from Malaysia (id = 1)	EXPORT
Otherwise	IMPORT

---

Output: driver_advance_payment

OneSingle record per driver per run.execution:

Field	Value
driverId	driverfrom IDjob
paymentDate	targetDate (Malaysia date string)
totalAmount	sum of all applicable jobmatched costs (0 if none)
totalJobs	count of jobs processedfor this driver
status	PENDING
isAutoGenerated	true
createdBy	System_Cron

---

Output: driver_advance_payment_job

One recordor more records per job x(always matchingat costleast setup.one, even with no cost):

Field	Value
driverAdvancePaymentId	parent driver_advance_payment.idrecord ID
driverId	driverfrom IDjob
jobId	job IDjob.id
orderId	order IDjob.orderId
jobCostSetupId	matchedjcs.id JobCostSetup.idor null (no match)
jobCostName	costjcs.costName nameor null
jobCostAmount	costamount amountor 0
jobPayTo	DRIVER or null
jobUnloadedDatetime	order.supplierDoDatetimeunloadedDatetime
jobType	LOCAL / EXPORT / IMPORT
isDeducted	false
createdBy	System_Cron

---

ImportantCritical NotesConsiderations

• DedupCompany-Scoped Deduplication — Existing order ID exclusion is globalfiltered 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 existingrecords orderwithin IDsthat calendar day
• effectiveFrom Comparison — Uses durationTo (end of MYT day in driver_advance_payment_jobUTC), are excluded regardless of date. An order processed in any previous run will never be reprocessed, even ifnot the taskraw istargetDate requeuedstring, forto avoid missing costs set up within the same date.Malaysia day
• Zero-amountLatest recordJob stillper createdOrder -— ifOnly nothe matchinghighest JobCostSetupjob.id per order is foundprocessed for(one aadvance driver,payment aper record is still inserted with totalAmount = 0.order)
• Per-driverDriver-Level errorError isolationIsolation -— if oneIndividual driver fails,failures thedon't loophalt continuesprocessing; toerrors the next driver; the error isare logged butand notprocessing rethrown.continues
• No transactionTransaction wrappingProtection -— eachEach save()save is independent. Aindependent; mid-loop failurefailures canmay leavecreate partial records for a driver.
• Uses supplierDoDatetime - filtering uses supplier_do_datetime, unlike ver1 which uses unloadedDatetime.