BRD: Weight discrepancy management

FIGMA: https://www.figma.com/proto/LURem3BZz7dFNbUTvN6hDj/WIP-%7C-Nov-2023?page-id=704%3A12680&type=design&node-id=805-46727&viewport=2014%2C9098%2C0.28&t=2WyzjI2IHpxVRSKE-1&scaling=min-zoom&mode=design

EPIC: https://auperator.atlassian.net/browse/SHE-3799

Account Fees

https://docs.google.com/spreadsheets/d/1b14TlCfahCt3t-2_b_lXMJwlsIUgNB_lW0JdMtpDIos/edit?usp=sharing

Activity details to be displayed

Weight discrepancy activity

Pending

1. Exports

2. CRON :

   1. charging seller at end after specific time(to be discussed with mituj sir)

   2. changing new to lost after specific time

   3. update if excess charges is zero

3. Notifications

4. Weight Protection Plan - Frontend Changes for Shield

5. If status is won hide excess weight and excess charge

Contents:

Open

Open

Data Model

Excel: Weight_Discrepancy_Data_Model.xlsx

Sample Logs

Possible logs

1. Import (Report Discrepancy ➤ New)

CSV Format: https://storage.googleapis.com/eshopbox-portal-dev.appspot.com/import_job/weight_discrepancy/WeightDiscrepancyImportTemplate.csv

1. Read the excel data and create list of DTO and list of tracking_id's

2. Fetch list of all the shipping_id's and weight's for the tracking_id's

   1. SELECT shipments.id AS shipmentId, shipments.trackingID AS trackingId, shipments.weight AS shipmentWeight, shipments.dimension_length AS shipmentLength, shipments.dimension_width AS shipmentWidth, shipments.dimension_height AS shipmentHeight, warehouses.pincode AS warehousePincode, orders.shipping_postalCode AS ordersShippingPostalCode, IF(orders.isCOD='1', true, false) AS isCOD, order_items.brandAccountId AS brandAccountId, SUM(order_items.invoiceTotal) AS shipmentInvoiceTotal, COUNT(order_items.id) AS itemsInOrder, CASE WHEN courier_partners.standard='1' THEN 'Standard' WHEN courier_partners.express='1' THEN 'Express' WHEN courier_partners.priority='1' THEN 'Priority' END AS courierType FROM shipments LEFT JOIN orders ON orders.id = shipments.order_id LEFT JOIN order_items ON order_items.order_id = orders.id LEFT JOIN warehouses ON warehouses.id = shipments.warehouse_id LEFT JOIN courier_partners ON courier_partners.id = shipments.courier_partner_id WHERE trackingID IN (:trackingIdSet) GROUP BY shipments.id

   2. Fetch existing data using it and do not update it

   3. SELECT shipments.id AS shipmentId, shipments.trackingID AS trackingId FROM weight_discrepancy INNER JOIN shipments ON shipments.id = weight_discrepancy.shipment_id WHERE weight_discrepancy.shipment_id IN (:shipmentIdSet)

3. Prepare two lists:

   1. If CourierWeight and ShipmentWeight are in same weight slab, then list with new status

   2. If in different slabs then list with isDiscrepancy=0 status

4. Using spring-hibernate mapping insert data in both tables

5. Publish create event for all the new discrepancies in the Pub/Sub

   1. { "actor": "snehal.soni@eshopbox.com", "response_data": { [ { "trackingID" : 195041816874355, "shipmentID" : 289232983, "itemsInOrder" : 3, "excessWeight" : 0.9, "chargeableWeight" : 4.2, "appliedWeight" : 3.3 }, { ... }, {} ] }, "eventSubType": "new", "resource": "weight_discrepancy", "accountSlug": "tab", "eventType": "POST", "version": "v1" }

6. After all discrepancy have been created, update the import job on platform with below event :

   URL: https://{{accountSlug}}.eshopbox.com/_ah/api/esb/v1/import-job/<jobId>

   Headers:

Request body:

1. Get API (with filters)

GET URL: https://{{accountSlug}}.myeshopbox.com/api/v1/dicrepancy

1. Fetch accountId from the http session

2. Fetch weight dicrepancies and count

   1. SELECT SQL_CALC_FOUND_ROWS weight_discrepancy.id AS id, orders.id AS order_id, orders.customerOrderNumber AS customerOrderNumber, orders.vendorOrderNumber AS vendorOrderNumber, weight_discrepancy.shipment_id AS shipment_id, shipments.externalShipmentID AS externalShipmentID, shipments.trackingID AS trackingID, weight_discrepancy.length AS length, weight_discrepancy.width AS width, weight_discrepancy.height AS height, weight_discrepancy.weight AS weight, shipments.weight AS shipmentWeight, weight_discrepancy.user_id AS user_id, users.email AS email, weight_discrepancy.additionalDetails AS additionalDetails, weight_discrepancy.excessCharge AS excessCharge, weight_discrepancy.isDiscrepancy AS isDiscrepancy, COUNT(order_items.id) AS itemsInOrder, UNIX_TIMESTAMP(weight_discrepancy.externalUpdatedAt) AS externalUpdatedAt, UNIX_TIMESTAMP(weight_discrepancy.created_at) AS created_at, UNIX_TIMESTAMP(weight_discrepancy.updated_at) AS updated_at, SUBSTRING_INDEX(GROUP_CONCAT(weight_discrepancy_status_logs.`status` ORDER BY weight_discrepancy_status_logs.id DESC), ',', 1) AS latestStatus, SUBSTRING_INDEX(GROUP_CONCAT(weight_discrepancy_status_logs.remarks ORDER BY weight_discrepancy_status_logs.id DESC), ',', 1) AS latestRemarks, SUBSTRING_INDEX(GROUP_CONCAT(UNIX_TIMESTAMP(weight_discrepancy_status_logs.externalUpdatedAt) ORDER BY weight_discrepancy_status_logs.id DESC), ',', 1) AS latestStatusExternalUpdatedAt, SUBSTRING_INDEX(GROUP_CONCAT(UNIX_TIMESTAMP(weight_discrepancy_status_logs.created_at) ORDER BY weight_discrepancy_status_logs.id DESC), ',', 1) AS latestStatusCreatedAt, SUBSTRING_INDEX(GROUP_CONCAT(UNIX_TIMESTAMP(weight_discrepancy_status_logs.updated_at) ORDER BY weight_discrepancy_status_logs.id DESC), ',', 1) AS latestStatusUpdatedAt FROM weight_discrepancy LEFT JOIN weight_discrepancy_status_logs ON weight_discrepancy.id = weight_discrepancy_status_logs.weight_discrepancy_id LEFT JOIN shipments ON shipments.id = weight_discrepancy.shipment_id LEFT JOIN orders ON orders.id = shipments.order_id LEFT JOIN order_items ON order_items.shipment_id = weight_discrepancy.shipment_id LEFT JOIN users ON users.id = weight_discrepancy.user_id WHERE weight_discrepancy.isDiscrepancy='1' AND ( orders.id IN (:searchParam ) OR shipments.id IN (:searchParam ) OR shipments.trackingID IN (:searchParam ) ) AND order_items.brandAccountId = :accountId AND weight_discrepancy.created_at >= :fromDate AND weight_discrepancy.created_at <= :toDate GROUP BY weight_discrepancy.id HAVING latestStatus IN (:status) LIMIT :limit;
   2. SELECT FOUND_ROWS()

3. Fetch status logs and add it to response

   1. SELECT weight_discrepancy_status_logs.id AS id, weight_discrepancy_status_logs.weight_discrepancy_id AS weight_discrepancy_id, weight_discrepancy_status_logs.`status` AS `status`, weight_discrepancy_status_logs.remarks AS remarks, weight_discrepancy_status_logs.actor AS actor, weight_discrepancy_status_logs.user_id AS user_id, users.email AS email, weight_discrepancy_status_logs.additionalDetails AS additionalDetails, UNIX_TIMESTAMP(weight_discrepancy_status_logs.externalUpdatedAt) AS externalUpdatedAt, UNIX_TIMESTAMP(weight_discrepancy_status_logs.created_at) AS created_at, UNIX_TIMESTAMP(weight_discrepancy_status_logs.updated_at) AS updated_at FROM weight_discrepancy_status_logs LEFT JOIN users ON users.id = weight_discrepancy_status_logs.user_id WHERE weight_discrepancy_status_logs.weight_discrepancy_id IN (:weightDiscrepancyIdList)

4. Fetch status count for each status

   1. SELECT wdcStatus.latestStatus AS status, COUNT( wdcStatus.latestStatus ) AS statusCount FROM ( SELECT SUBSTRING_INDEX(GROUP_CONCAT(weight_discrepancy_status_logs.`status` ORDER BY weight_discrepancy_status_logs.id DESC), ',', 1) AS latestStatus FROM weight_discrepancy_status_logs LEFT JOIN weight_discrepancy ON weight_discrepancy.id = weight_discrepancy_status_logs.weight_discrepancy_id LEFT JOIN shipments ON shipments.id = weight_discrepancy.shipment_id LEFT JOIN orders ON orders.id = shipments.order_id LEFT JOIN order_items ON order_items.shipment_id = weight_discrepancy.shipment_id WHERE weight_discrepancy.isDiscrepancy='1' AND ( orders.id IN ( '11205905' ) OR shipments.id IN ( '11205905' ) OR shipments.trackingID IN ( '11205905' ) ) AND order_items.brandAccountId = 1541 AND weight_discrepancy.created_at >= '2024-02-21 17:13:01' AND weight_discrepancy.created_at <= '2024-03-13 12:26:52' GROUP BY weight_discrepancy.id HAVING latestStatus IN ('new','lost','disputed','not_disputed','won','escalated') ) AS wdcStatus GROUP BY wdcStatus.latestStatus;

   2. If accountId is '5' then remove where condition from the above query as we need to fetch all the discrepancies possible

5. Convert it to required response based on latest status for each id

6. Sample Response

   1. { "page": 1, "perPage": 50, "total": "13", "totalPages": "13", "newCount": 6, "disputedCount": 2, "lostCount": 3, "notDisputedCount": 4, "wonCount": 5, "escalatedCount": 1, "data": [ { "id": "6", "order_id": "44213869", "customerOrderNumber": "5643620712515", "vendorOrderNumber": "#1009", "shipment_id": "11205815", "externalShipmentID": "5643620712515-6421-9758", "trackingID": "593816220555", "length": "30", "width": "20", "height": "20", "weight": "490", "shipmentWeight": "250", "user_id": "3354", "email": "snehal.soni@eshopbox.com", "additionalDetails":"", "excessCharge": 75.0, "isDiscrepancy": "1", "itemsInOrder": 2, "externalUpdatedAt": "", "created_at": "1709116180", "updated_at": "1709116180", "latestStatus": "not_disputed", "latestStatusExternalUpdatedAt": "", "latestStatusCreatedAt": "1709735588", "latestStatusUpdatedAt": "1709735588", "statusLogs": { "new": { "id": "2", "weight_discrepancy_id": "6", "status": "new", "remarks": "", "reason": "", "actor": "system", "user_id": "3354", "email": "snehal.soni@eshopbox.com", "additionalDetails": "{\"imageUrl1\":\"https://i.postimg.cc/pLRNcNgv/LCMSF652-PR10946-W1-0.jpg\",\"imageUrl2\":\"https://cdn.filestackcontent.com/hsq64kfTwKdRjWNgP4Mt\",\"imageUrl3\":\"https://i.postimg.cc/Kc3yRDn6/LCMSF652-PR10872-B2-0.jpg\"}", "externalUpdatedAt": "", "created_at": "1709116181", "updated_at": "1709116181" }, "not_disputed": { "id": "5", "weight_discrepancy_id": "6", "status": "not_disputed", "created_at": "1709735588", "updated_at": "1709735588" } } }, {...}, {...} ] }

   2.  

7. gr

2. Update Action PUT API

Implementation:

Action Types:

1. Accept Discrepancies ➤ Not Disputed (Seller)

2. Reject Discrepancy ➤ Disputed (Seller)

3. Accept Dispute ➤ Won (Admin)

4. Reject Dispute ➤ Lost (Admin)

5. Escalate ➤ Disputed (Seller)

3. Rate Calculator POST API

Rate Calculator - Internal

1. CRON POST API