Order revamps feature

SALE ORDER EXPORTS
Always use order_items.brandAccountId filter in the primary query
Always pass date range of orderReceivedOn (Created in eshopbox).
- SELECT MIN(id) from order_items where created_at >= orderReceivedOn.value[0] and order_items.brandAccountId = :account_id
- SELECT MAX(id) from order_items where id > :minId and created_at <= orderReceivedOn.value[1] and order_items.brandAccountId = :account_id
- The minId and maxId should be calculated before at the start and should be used while querying all the other filters.

Use offset and limit in query, to reduce the number of data fetched in each iteration of sheet generation

Always try to reterive a set of order_items.id those matching the filter.
- This will reduce the execution time since order_items table is the first join of the data-grab query.

FILTERS

1. Brand: Picked from productDetails.brand.

   • algolia_key: orderItemDetails.brand

   • How to set a filter in exports?
     -- Use product API to fetch esin of all the products of selected brand.
     -- Then query the database
     - AND (order_items.sku in (:brandESINs))

2. Cancelled by: "Seller", if shipment_status_logs.status = "cancelled" AND remark = "fulfilment_cancel", else "Customer"

   • algolia_key: cancellationDetails.type

   • How to set a filter in exports?

     • Using join between order_items and shipment_status_logs find only the cancelled status logs

       • SELECT order_items.id, shipment_status_logs.remarks

       • FROM order_items LEFT JOIN shipment_status_logs shsl ON shsl.shipment_id = order_items.shipment_id

       • WHERE order_items.id >= :mindId AND order_items.id <= :maxId AND order_items.brandAccountId = :account_id AND

       • shsl.status = 'cancelled'

     • Once all the order_items are found, filter the order_items based on the type of value to be filtered ("Seller", "Customer", or both)

3. Order cancelled on: TODO

4. Customer order ID:

   • algolia_key: vendorOrderNumber

5. Channel order ID:

   • algolia_key: customerOrderNumber

6. Order item ID:

   • algolia_key: orderItemID

7. Shipment ID:

   • algolia_key: shipmentDetails.shipmentNumber

   • table.column: shipment.externalShipmentID

8. Shipment Zone:

   • algolia_key: shipmentDetails.region

   • How to set a filter in exports?

     • Shipment zone is set using 3 keys:

       • shipments.region ('Local', 'National', 'Zonal')

       • shipments.isSepcialplace ('0', '1')

       • shipments.isMetro ('0', '1')

     • Shipment zone is determined by:

       • Local: shipments.region = "Local"

       • Zonal: shipments.region = "Zonal"

       • National: shipment.region = "National" AND shipments.isSepcialplace = '0' AND shipments.isMetro = '0'

       • Metro: shipment.region = "National" AND shipments.isSepcialplace = '0' AND shipments.isMetro = '1'

       • Remote: shipment.region = "National" AND shipments.isSepcialplace = '1' AND shipments.isMetro = '0'

   • How to implement:

     • Using order_items min and max Ids, brandAccountId, LEFT JOIN order_items and shipments

     • SELECT order_items.id, shipments.region, shipemnts.isSepcialplace, shipments.isMetro

     • FROM order_items LEFT JOIN shipments on shipments.id = order_items.shipment_id

     • WHERE order_items.id >= :mindId AND order_items.id <= :maxId AND order_items.brandAccountId = :account_id

     • Once all records are received, filter out the order_items.id based on the filter requested

9. Invoice number

   • algolia_key: shipmentDetails.invoiceNumber

   • table.column: shipments.invoiceNumber

   • How to set a filter in exports?

     • In algolia, if in shipmentEvents, the invoiceNumber is empty or NULL, externalShipmentID is set instead.

     • Straight forward find the order_items where shipments.invoiceNumber = shipmentDetails.invoiceNumber[0]

     • If no result is found? Use below query

     • Find order_items by filtering the invoiceNumber value in externalShipmentID

10. Order invoiced on:

    • algolia_key: shipmentDetails.invoiceDate

    • table.column: shipments.invoiceDate

    • Open point: Should we not use 'packed' shsl.created_at as invoiceDate?

11. Order delivered on:

    • algolia_key: orderDeliveredOn

    • Delivered status to be queried for eshopbox shipping services only

12. Is delivered on time

    • algolia_key: shipmentTrackingDetails.delayedDeliveryTag

    • Implementation TBD

13. Is delivery SLA breached

    • algolia_key: shipmentTrackingDetails.slaResolution

14. EAN, ESIN:

    • algolia_key: orderItemDetails.ean, orderItemDetails.esin

15. Order handover done on

    • algolia_key: orderHandoverDoneTimestamp

    • Implementation:

      • Handover done is to be queried for dispatched 'status'

      • Only for non eshopbox shipping service accounts

16. Current status

    • algolia_key: currentOrderStatus

    • Contains returnStatus along with orderStatus

    • Implementation

      • Filter out orderStatus and returnStatus from the values[] based on Category

      • Then fetch all the filtered orderStatus subStatuses and returnStatus subStatuses as a separate lists

        • filter orderStatus subStatuses

          • use function getShipmentsBasedOnStatus to get the shipment_ids for orderStatusList

        • filter returnStatus subStatuses

          • separate out rto like statuses from the subStatuses and query from shipment_status_logs

          • separate not-rto like statuses from the subStatuses and query from return_shipment_status_logs

17.