What is the purpose of this article?

The purpose of this article is to make a cron that will release those Onhold orders which are stuck in onhold status due to high risk for some particular hour. The idea is to release those orders which have been assigned high risk score and no manual action has ben taken on those orders till now. This is to ensure that if user does not take manual action on high risk orders for a particular interval of time then those orders will get auto released as they acnnot remain on high risk forever.

Why we are making this cron

The corn will pickup those order items and externalShipmentId which are in "HIGH RISK SCORE" status (riskScore column should be 1 in orders table) and which have been set a timer for auto release (releaseHighRiskOrderAfter column in account_additional_config table) by their order_items.updated_at date. If according to the timer set, the limit for order to remain on High risk has been crossed then the order items and externalShipmentId will get auto released

for e.g., For orderItemA,

1. releaseHighRiskOrderAfter = 2 hour

2. order_items.updated_at = 12-05-2023 08:00:00

3. current time = 12-05-2023 10:01:00

Then orderItemA should be auto relased

Note - Auto-release means :-

1. If an unfulfillable order is assigned a high risk score, then after auto- release its risk score should become low

2. If an order is in onhold status with a high risk score, then after auto-release, its status should change to new/processing with a low risk score

Technical steps to develop the cron

1. The corn will run at a particular interval of time set at google cloud scheduler and will target the below endpoint

   Endpoint - orderUnholdEndpoint Project - Esb-client-order-return service - order-return API path - GET API /_ah/api/esb/v1/highRiskOrder/auto-release

2. Write a query to fetch those account_id and their releaseHighRiskOrderAfter time from account_additional_config table in DB and store them in a List of DTO (List<OrderReleaseTimeIntervalDTO>)

   SELECT account_id, releaseHighRiskOrderAfter FROM account_additional_config WHERE isRiskScoreEnabled = '1' AND releaseHighRiskOrderAfter > 0;

3. Get the maximum releaseHighRiskOrderAfter from 0th index of list of DTO and calculate a start date

   1. start date = current time - maximum releaseHighRiskOrderAfter time

4. Write a query to fetch those order items and external shipment ids which have been assigned high risk scores by the start date set and store the result in a list of dto

   SELECT order_items.id AS orderItemId_pk, shipments.externalShipmentId, order_items.shipment_id, order_items.updated_at FROM orders LEFT JOIN order_items ON orders.id = order_items.order_id LEFT JOIN shipments ON shipments.id = order_items.shipment_id WHERE order_items.brandAccountId IN (6,28,758) AND order.riskScore = 'high' AND order_items.updated_at >= '2023-05-10 14:30:48' AND order_items.status != 'CANCELLED';

5. Prepare a Map<String, Object> where key = brandAccountId and value = List<dto>

6. Calculate the current time and store it in a String

7. Iterate over the List<OrderReleaseTimeIntervalDTO> and get the releaseHighRiskOrderAfter time

   1. {

      • Iterate over the Map<String, Object> and get the object of OrderReleaseTimeIntervalDTO’accountId from this map

      • Iterate over this this list of DTO again and compare the order_items.updated-at time with the current time. If the comparison has a time gap of more than or equal to the releaseHighRiskOrderAfter then

        • check if its shipment_id = 0, then collect the order_items.id in List<Long>orderItemIds else collect externalShipmentIds in List<String> externalShipmentIds

   2. }

8. Pass the externalShipmentIds in existing unhold API function to unhold them via taskqueue

9. For unhold order_items, need a discussion