Compute Engine ( Spring Boot Based Export Service )
- Suraj Kumar Jha (Unlicensed)
Project Name : spring-boot-data-jpa
API : https://exportsprod.myeshopbox.com/exports/api/esb/v2/saleOrderExportJob
This project contains the business logic and execution flow of Sale Order exports, complete process and flow mentioned in this doc.
Phases of execution
1. Request handling and parsing the required parameters.
2. Fetching Order Item Ids for the selected set of filters.
3. Fetching the data from database using the order item ids.
4. Product API execution.
5. Tax Calculations.
6. Write the data in csv file and upload it on cloud.
7. Request the platform service to update the exports status.
8. Sending email to the user who created the exports request on workspace.
Request handling and parsing the required parameters
In this phase of code the request body is parsed and the required parameters get instantiated like exportJobs entity, accountSlug, offset, totalCount.
exportJobs entity contains the parameter like filters, date range filters, export type, is schedule report, & columns.
date range filters get parsed and added in the filters map.
Fetching Order Item Ids for the selected set of filters
Based on the “filters” parameter we got in exportJobs entity. It contains “report_type” parameter.
exportJobs entity contains is schedule report.
Report Type values :
orders_matching_filter
all_order
If report_type is order_matching_filter OR it is a schedule report, then following flow will be executed.
If report_type is all_order.
Fetching the data from database using the order item ids.
In this phase the data fetch SQL query is executed using the order item ids as filters in IN clause.
The Result Set is parsed in the instance of OrderPaymentExportDto, which will be further used to
in columns.
SQL Query :
SELECT orders.vendorOrderNumber AS orderId, orders.customerOrderNumber AS customerOrderNumber, shipments.courierName AS forwardShippingProvider, return_shipments.courierName AS reverseShippingProvider,
CONCAT( orders.shipping_addressLine1, ' ', orders.shipping_addressLine2 ) AS shippingAddress, orders.shipping_email AS shippingEmailId, order_items.`expectedShipDate`,
orders.shipping_customerName AS shippingCustomerName, orders.shipping_contactPhone AS shippingContactPhone, orders.shipping_postalCode AS shippingPostalCode,
orders.shipping_city AS shippingCity, orders.shipping_state AS shippingState, orders.shipping_countryName AS shippingCountry, CONCAT( orders.billing_addressLine1, ' ', orders.billing_addressLine2 ) AS billingAddress,
orders.billing_contactPhone AS billingContactPhone, orders.billing_postalCode AS billingPostalCode, orders.billing_email AS billingEmailId, orders.billing_customerName AS billingCustomerName, orders.billing_city AS billingCity,
orders.billing_state AS billingState, orders.billing_countryName AS billingCountry, order_items.invoiceTotal AS invoiceTotal, order_items.shippingCharges AS shippingCharges, order_items.sku, accounts.account_slug AS accountName,
accounts.id AS accountId, order_items.orderItemID, orders.isCOD, shipments.region, shipments.isMetro as isMetro, shipments.isSpecialplace as isSpecial, order_items.`warehouseInboundStatus`, order_items.`returnFlag`, order_items.`return_shipment_id`,
order_items.`returnReason`, order_items.`customerOrderItemID`, order_items.`reversePickupReason`, return_shipments.`customerReturnNumber`, shipments.`trackingID`, order_items.`isVirtualKit`, order_items.qcRejectionReason, return_shipments.`trackingID` AS returnTrackingID,
order_items.cancellationTime, orders.`orderDate` AS orderPlacedOn, orders.`external_created_at`, orders.`created_at` AS orderCreatedAt, order_items.`created_at` AS createdOnEshopbox, shipments.`invoiceDate`, o.`customerOrderNumber` AS replaceOrderNumber,
shipments.warehouse_id AS warehouseId, orders.channel_id AS channelId, order_items.`lineItemOrderStatus`, order_items.`status` AS orderItemStatus, order_items.`shipment_id` AS shipmentId, shipments.external_wms_channel_id as externalWmsChannelId, shipments.`externalShipmentID`,
shipments.`invoiceNumber`, shipments.invoice_url as invoiceUrl, order_items.`original_order_item_id`, order_items.`expectedDeliveryDate`, order_items.`expectedPickupDate`, order_items.`returnSubReason`, return_shipments.`isExchange` AS isExchange, return_shipments.`repaymentType` AS repaymentType,
return_shipments.`beneficiaryName` AS beneficiaryName, return_shipments.`beneficiaryIfscCode` AS beneficiaryIfscCode, return_shipments.`beneficiaryAccountNo` AS beneficiaryAccountNo, return_shipments.`beneficiaryAccountType` AS beneficiaryAccountType, return_shipments.`exchangeSize` AS exchangeSize,
order_items.`resolutionType` AS returnResolution, order_items.`productAdditionalInfo` AS productAdditionalInfo, order_items.`giftWrapCharges` AS giftWrapCharges, order_items.`cashOnDeliveryCharges` AS cashOnDeliveryCharges, order_items.`cancellationReason` AS cancellationReason,
order_items.`remark` AS additionalReason, shipment_unexpected.unexpectedItemQty AS sh_unexpectedItemsQuantity, shipment_unexpected.unexpectedItemSKUs AS sh_unexpectedItemSKUs, shipment_unexpected.qcRejectionReason AS sh_qcRejectionReason, return_shipment_unexpected.unexpectedItemQty AS rsh_unexpectedItemsQuantity,
return_shipment_unexpected.unexpectedItemSKUs AS rsh_unexpectedItemSKUs, return_shipment_unexpected.qcRejectionReason AS rsh_qcRejectionReason, order_items.warehouseReversePickupReason, order_items.warehouse_id AS orderItemsWarehouseId, return_shipments.type AS returnType,
IF ( channels.integrationType IN ('4', '7') AND channels.shippingConnectionID = '0', 1, IF ( channels.integrationType IN ('11') AND orders.thirdPartyShipping = '0', 1, 0 ) ) AS esbShip
FROM order_items
LEFT JOIN order_items oi ON oi.id = order_items.`original_order_item_id`
LEFT JOIN orders o ON o.id = oi.order_id
LEFT JOIN orders ON order_items.order_id = orders.id
LEFT JOIN channels ON channels.id = orders.channel_id
LEFT JOIN shipments ON shipments.id = order_items.shipment_id
LEFT JOIN return_shipments ON return_shipments.id = order_items.return_shipment_id
LEFT JOIN unexpected_order_items shipment_unexpected ON shipments.id = shipment_unexpected.shipment_id
LEFT JOIN unexpected_order_items return_shipment_unexpected ON return_shipments.id = return_shipment_unexpected.return_shipment_id
LEFT JOIN accounts ON accounts.id = order_items.`brandAccountId`
WHERE order_items.id >= :minId AND order_items.id <= :maxId AND order_items.brandAccountId = :accountId
WHERE order_items.brandAccountId = :accountId AND order_items.id IN ( :orderItemIds )
GROUP BY order_items.id
The data is fetched in the batch records of 5000 order item ids at a time, In Order to improve performance.
Fetching the Return Shipment Status Log
Based on return shipment ids iterated from dto stored in a list, Return Shipment Status log data is fetched using those return shipment ids. The Result Set is then parsed in the ReturnShipmentStatusLogDTO.
Fetching the Shipment Status Log
Based on shipment ids iterated from dto stored in a list, Shipment Status log data is fetched using those Shipment Ids. The Result Set is then parsed in the ShipmentStatusLogDTO.
Product API execution.
In this phase all the records are iterated and we store respective SKUs in a list.
The list of SKUs will be used to execute the product API in the batch of 800 SKUs at a time.
Product Request URL : https://products-dot-esb-product-engine-prod.appspot.com/_ah/api/esb/v1/products?fields=groupCode,components,status,accountSlug,additionalNames,esin,descrip tion,mrp,hsnCode,combo,imageUrl,channelCode,verticalName,brand,type,sku,weight,dimensionHeight,dimensionLength,dimensionWidth,weightUnit,dimensionUnit&ids=
fetched product data is stored in the DTO to be used further.
Write the data in csv file and upload it on cloud.
In this phase of code the OrderPaymentExportDto, ReturnShipmentStatusLogDTO & ShipmentStatusLogDTO along with other DTOs is processed to be write in CSV file.
DTO or dto is termed as Data transfer object, It used to store and get data in the multiple places of application.
ByteArrayOutputStream baos = new ByteArrayOutputStream()
BufferedWriter bw = new BufferedWriter(new OutputStreamWriter(baos));
Header & Sub-Headers of the CSV files which are Columns Category and Columns get write in bw object.
OrderPaymentExportDto is iterated and based on different columns and category logic the data get write in bw object.
Based on the category of columns it have 11 categories :
Order
Columns | Implementation Logic |
Channel Order Id | Value of orders.customerOrderNumber |
Customer Order Id | Value of orders.vendorOrderNumber |
Order Item Id | Value of order_items.id |
Shipment id | Value of shipments.externalShipmentID |
Invoice number | Value of shipments.invoiceNumber |
Sales channel | If orders.channel_id == null, it print empty value, |
Portal | If orders.channel_id == null, it print empty value, |
Fulfilment center | Based on shipments.warehouse_id or order_items.warehouse_id value, |
Customer code | Based on the shipments.external_wms_channel_id value, |
Payment mode | Based on the value of orders.isCOD, "Prepaid", "Cash on delivery (COD)", or blank. |
Sold by | Based on the orders.channel_id value, |
Shipment Zone | Based on the values of shipments.region, shipments.isMetro, shipments.isSpecialplace Shipment |
Replacement | Value of orders.customerOrderNumber |
Replacement order item ID | Value of orders.customerOrderNumber + "-" + |
Sold as virtual kit | If value of order_items.isVirtualKit is "1" - YES |
Virtual kit ESIN | Based ProductInfoDto Object we can get Component Map's List, If the 0th index of the |
2. Customer
Columns | Implementation Logic |
Shipping address | Value of ( orders.shipping_addressLine1 + “ “ + orders.shipping_addressLine2 ) |
Shipping Email id | Value of orders.shipping_email |
Shipping Customer Name | Value of orders.shipping_customerName |
Shipping Contact Number | Value of orders.shipping_contactPhone |
Shipping pin code | Value of orders.shipping_postalCode |
Shipping city | Value of orders.shipping_city |
Shipping state | Value of orders.shipping_state |
Shipping country | Value of orders.shipping_countryName |
Billing address | Value of ( orders.billing_addressLine1 + “ “ + orders.billing_addressLine2 ) |
Billing Email id | Value of orders.billing_email |
Billing Customer Name | Value of orders.billing_customerName |
Billing Contact Number | Value of orders.billing_contactPhone |
Billing pin code | Value of orders.billing_postalCode |
Billing city | Value of orders.billing_city |
Billing state | Value of orders.billing_state |
Billing country | Value of orders.billing_countryName |
3. Product
At each iteration ProductInfoDto instance get used to store the Product related data and to be processed further while populating the Product Category Columns.
ProductAPI is used initially to fetch the product related data from
ProductInfoDto productInfo = productInfo.get( accounts.id + order_items.sku );
Columns | Implementation Logic |
ESIN | ProductAPI.ESIN |
SKU | ProductAPI.SKU |
EAN | ProductAPI.EAN |
Product group code | ProductAPI.GROUP_CODE |
Title | ProductAPI.Description |
Brand | ProductAPI.Brand |
Category | ProductAPI.VerticalName |
Product weight | Based on the value of ProductAPI.WeightUnit. |
HSN code | Value of ProductAPI.HSNCode |
MRP | Value of ProductAPI.MRP |
4. Pricing
giftWrapCharges - order_items.giftWrapCharges else “0”
codCharges - order_items.cashOnDeliveryCharges else “0”
otherCharges = giftWrapCharges + codCharges
sellingPrice = order_items.invoiceTotal - order_items.shippingCharges - otherCharges
discount = ProductAPI.MRP - sellingPrice
discountPercentage = ( discount / ProductAPI.MRP ) * 100
TaxDetailsRequestDto instance is used to store the below mentioned data.
billingStateFrom - warehouseDetails Map fetched using warehouseId to get pincode,
which will be used to get data from pincodeStateMapping Map.
billingStateTo - pincodeStateMapping details get fetched using orders.shipping_postalCode,
else orders.shipping_state.
ItemId - ProductAPI.ESIN.
mrp - ProductAPI.MRP
srp - sellingPrice
Tax Details get calculated based on the data present in TaxDetailsRequestDto and it get stored
TaxDetails DTO.
On the basis of that Pricing columns get assigned,
Columns | Implementation Logic |
Invoice total | Value of order_items.invoiceTotal |
Discount | Value of discount calculated. |
Discount percentage | Value of discountPercentage calculated. |
Shipping charges | Value of order_items.shippingCharges |
Other charges | Value of otherCharges |
Selling price | Value of sellingPrice |
Tax amount | Value of TaxDetails.TaxAmount() |
IGST rate | Value of TaxDetails.getIGST_Rate() |
IGST amount | Value of TaxDetails.getIGST_Amount() |
CGST rate | Value of TaxDetails.getCGST_Rate() |
CGST amount | Value of TaxDetails.getCGST_Amount() |
SGST rate | Value of TaxDetails.getSGST_Rate() |
SGST amount | Value of TaxDetails.getSGST_Amount() |
UTGST rate | Value of TaxDetails.getUTGST_Rate() |
UTGST amount | Value of TaxDetails.getUTGST_Amount() |
5. Shipping provider
Columns | Implementation Logic |
Forward shipping provider | Value of shipments.courierName |
Forward tracking ID | Value of shipments.trackingID |
Return shipping provider | If order_items.returnFlag is "1" |
Return tracking ID | If order_items.returnFlag is "1" |
6. Cancellation
Columns | Implementation logic |
Cancelled by | Value of shipmentStatusLogDTO.getCancelledBy() |
Cancellation reason | Value of order_items.cancellationReason |
Cancellation sub reason | Value of order_items.cancellationReason |
Cancel resolution |
|
Cancel resolution details |
|
7. Timeline
esbShip - If ( channels.integrationType IN ('4', '7') AND channels.shippingConnectionID = '0' ) then 1,
else If ( channels.integrationType IN ('11') AND orders.thirdPartyShipping = '0' ) then 1,
else - 0
Columns | Implementation Logic |
Order Placed on channel | Value of orders.created_at |
Order Created in Eshopbox | Value of order_items.created_at |
Order Invoiced on | Value of shipments.invoiceDate |
Expected order ready to ship on | Value of order_items.expectedShipDate |
Order Ready to ship (RTS) on | Value of shipment_status_logs.created_at |
Time taken for order to Ready to ship (RTS) | Value of time difference duration between shipment_status_logs.created_at and order_items.created_at |
Is order ready to ship on time | Value of time difference duration between shipment_status_logs.created_at and order_items.expectedShipDate |
Reason for order ready to ship (RTS) delay |
|
Is order ready to ship (RTS) SLA breached | If shipment_status_logs.created_at ( If Null then CurrentTimeStamp ) > order_items.expectedShipDate { |
Order Handover done on | If esbShip is 0 then, Value of shipment_status_logs.created_at, |
Is order delivery SLA breached | If shipment_status_logs.created_at ( If Null then CurrentTimeStamp ) > order_items.expectedDeliveryDate { |
Expected order delivery date | Value of order_items.expectedDeliveryDate |
Order delivered on | Value of shipment_status_logs.created_at |
Is order delivered on time | If ( shipment_status_logs.created_at > order_items.expectedDeliveryDate ) { Prints “No” } else { Prints “Yes” } |
Time taken for order delivery | Value of time difference between shipment_status_logs.created_at and order_items.created_at, In days and hours format. |
Number of delivery attempts done | Number of records in shipment_status_logs which have statuses as “out_for_delivery”, for a ceratain shipment id. |
1st out for delivery attempt | Value of shipment_status_logs.created_at |
Latest out for delivery attempt | Value of shipment_status_logs.created_at |
Number of attempt taken to pickup | Number of statuses as “out_for_pickup” is shipment_status_logs for a shipment id. |
1st out for pickup attempt | Value of shipment_status_logs.created_at |
Latest out for pickup attempt | Value of shipment_status_logs.created_at |
Order Cancelled on | Value of shipment_status_logs.created_at when shipment_status_logs.status is “cancelled” for a shipment_id. |
Return created on | Value of shipment_status_logs.created_at when shipment_status_logs.status is ( “rto” or “rto_created“ ) for a shipment_id. |
Expected return pickup date | If esbShip is 1, |
Return picked on | If esbShip is 1, |
Is return pickup done on time | If ( order_items.expectedPickupDate return_shipment_status_logs.created_at is get compared. |
Is return pickup SLA breached | If esbShip is 1, |
Return delivered on | Value of return_shipment_status_logs.created_at, when return_shipment_status_logs.return_status is “delivered”. |
Return received on | Value of return_shipment_status_logs.created_at, when return_shipment_status_logs.return_status is “received”. |
Return completed on | If ( order_items.warehouseInboundStatus is “COMPLETE” ) { |
8. Status
Columns | Implementation Logic |
Current Status | If ( order_items.shipment_id is not null ) { Prints “Unfulfillable” } |
Current status remark |
|
9. Return
CUSTOMER_RETURN("Customer initated return", "CIR"),
COURIER_RETURN("Courier initated return", "FDR"),
UNKNOWN_RETURN("Unknown return", "UNKNOWN")
Columns | Implementation logic |
Return id | If ( order_items.return_shipment_id is not null and 0 ) { Value of return_shipments.customerReturnNumber } |
Return type | If ( order_items.return_shipment_id is not null and 0 ) { if ( return_shipments.type is not null ) { Based on return_shipments.type it get printed. } else { Prints “Customer initated return”. } } |
Return reason | If ( order_items.return_shipment_id is not null and 0 ) { Value of order_items.returnReason } |
Return sub reason | Value of order_items.returnSubReason |
Return resolution | If ( order_items.resolutionType is null ) { |
Return resolution details |
|
Action taken on return item | if ( order_items.reversePickupReason is “good_inventory” ) { Prints “Restocked” } |
Return item condition | if ( order_items.reversePickupReason is “good_inventory” ) { Prints “Sellable” } |
Return rejection reason | Value of order_items.qcRejectionReason |
Incorrect return items in the shipment | Value of return_shipment_unexpected.unexpectedItemSKUs |
10. Failed delivery
Columns | Implementation Logic |
Order Failed to deliver on | If esbShip is 1, |
Reason for failed delivery | If esbShip is 1, |
Courier partner remark for failed delivery | If esbShip is 1, |
Number of failed delivery attempts done | If esbShip is 1, |
Failed delivery action status | If esbShip is 1 AND order_items.returnFlag equals 1 { |
Failed delivery action | If esbShip is 1, |
Failed delivery action taken by | If esbShip is 1, |
11. Failed pickup
Columns | Implementation Logic |
Return failed to pick on | If esbShip is 1, |
Reason for failed pickup | If esbShip is 1, |
Courier partner remark for failed pickup | If esbShip is 1, |
Number of failed pickup attempts done | If esbShip is 1, |
Failed pickup action status | Currently not defined. |
Failed pickup action | Currently not defined. |
Failed pickup action taken by | Currently not defined. |
Request the platform service to update the exports status.
In the case of error or success of export job this part of code is executed in order to update the export job status.
It call the platform Service to update the Status of the exports in the database.
Request URL : https://serviceName.appspot.com/_ah/api/esb/v1/export-job/id
Sending email to the user who created the exports request on workspace.
In this phase of code an event is published on pub/sub containing the exports event object, it trigger the email notifications to the creator of this export job process.
topic name : nf_prod_export_job_completed_v1