/
Shopify Badges

Shopify Badges

Owned by Prateek Kaushik (Unlicensed)
Last updated: Nov 30, 2021
4 min read

Objective: If the customer checks the availability of a specific order before ordering, can do so by providing the drop Pincode (Shipping Address Pincode). On the basis of Pincode and VariantId of the Product following response to be returned:

  • Customer Delivery Date

  • Whether the order is serviceable or not

  • Whether the order is COD or not

POST API Path:

https://esb-integration-engine-prod.el.r.appspot.com/_ah/api/esb/v1/expectedDelivery

Prerequisite: We will get drop Pincode, Store Slug, and variantId (Shopify Inventory_item_id) in the request as shown below:

{ "dropPincode":"122001", "storeSlug":"eshopbox21", "variantId":"43410113429698" }

On the basis of dropPincode we will fetch data from country_state_city_mapping using SQL Query as mentioned below:

  • SQL QUERY 1:

SELECT * from country_state_city_mapping where pincode = {{pincode}}

Response of the SQL Query:

{ "id": "4594", "country": "India", "state": "HARYANA", "city": "gurgaon", "pincode":"122001" }

Note: Cache will be done on the basis of Pincode

Cache Key: “countryStateMapping_{{pincode}}“

Now we need to prepare a list of object containing complete data of state_zone_mapping table with the following SQL Query:

  • SQL QUERY 2:

SELECT * from state_zone_mapping

The above query will provide state, zone, zonePriority, createdAt, and UpdatedAt. We need to prepare a list of objects as given shown below keeping the state as the main key:

[ { "HARYANA": { "zone": "East", "zonePriority": "East,South,West,North", "createdAt": "2021-08-20 13:51:18", "updatedAt": "2021-02-03 15:23:41" }, "Andhra Pradesh": { "zone": "South", "zonePriority": "South,West,North,East", "createdAt": "2021-08-20 13:51:18", "updatedAt": "2021-02-03 15:23:41" } } ]

Now search for the storeSlug in the ie_appinstall_connection using drop_pincode

  • SQL Query 3:

SELECT * FROM ie_appinstall_connection WHERE inputFields LIKE '%{"store_name":"eshopbox21"}%'

Cache Key: “appConnection_{{storeSlug}}“

Using this SQL Query, we will get the connectionId using which we can search for the externalChannelID from the 'Channels' table

  • SQL Query 4:

SELECT id from channels where connectionID = {{connectionId}}

Cache Key: “channelData_{{connectionID}}“

Now use that channel_id to search for the Fulfillment Channels created for that connection:

SELECT inventories.inventory,warehouses.pincode,external_wms_channels.warehouse_id, warehouses.externalWarehouseID, external_wms_channels.externalWmsChannelName FROM external_wms_channels LEFT JOIN warehouses ON warehouses.id = external_wms_channels.warehouse_id LEFT JOIN listings ON listings.channel_id = external_wms_channels.channel_id LEFT JOIN inventories ON inventories.listing_id = listings.id WHERE external_wms_channels.channel_id ='{{channel_id}}' AND listings.itemID = '{{itemID}}' GROUP BY warehouses.id ORDER BY FIELD(warehouses.zone,'zonePriority[0]','zonePriority[1]','zonePriority[2]','zonePriority[3]')

Using this query, which will run in real-time, will provide us with the available inventory in the fulfilment channels subscribed for that Connection along with Pincode of the fulfilment channel(warehouse), for e.g if the channel_id is "1200" then the result will be as follows:

Once we have the above details, we need to check the following Scenarios:

Case #1:

If the Inventory is 0 on all the warehouses, then we don't have to call ClickPost API and simply return the following Response:

{ "isCOD": false, "isServiceable": false, "customerDeliveryDate": "", "message": "Pincode not serviceable" }

Case #2:

If Inventory is found at the 1st warehouse, then we need to pick the Pincode and call ClickPost API as given below:

Note: Even if the inventory is not found at the 1st warehouse, then look for the next warehouse, if inventory found then stop the iteration and begin the following process:

POST API:

https://www.clickpost.in/api/v2/serviceability_api/?username=test&key=a090-30ce18a2b96c

Sample Request Body: We need to place fetched Pincode in pickup_pincode and check whether the drop_pincode is serviceable or not

[{ "drop_pincode": "110020", "pickup_pincode": "561203" }]

Sample Response Body:

{ "result": [ { "service_type": "FORWARD", "account_id": 737, "shipping_charge": {}, "account_code": "Xpressbees Surface", "comitted_sla": null, "cp_id": 6, "serviceable": { "EXCHANGE": false, "PICKUP": true, "COD": true, "PREPAID": true } }, { "service_type": "FORWARD", "account_id": 723, "shipping_charge": {}, "account_code": "Delhivery Express", "comitted_sla": null, "cp_id": 4, "serviceable": { "EXCHANGE": false, "PICKUP": true, "COD": true, "PREPAID": true } } ], "meta": { "status": 200, "message": "SUCCESS", "success": true } }

If we found result[].serviceable.COD: true then we can return the isCOD flag true. Along with this, to return isServiceable flag as true, check both “COD” and “Prepaid”, if any one of them is true, isServiceable is also true

Note: If the COD flag is found true in the first Map Object then we do not have to iterate over the remaining map objects.

To return customerDeliveryDate, we need to call 2nd ClickPost API as given below:

POST API:

https://ds.clickpost.in/api/v2/predicted_sla_api/?username=test&key=42d42a34-ae09-4693-%20b20c-ae2624

Sample Request Body:

[ { "pickup_pincode": "600040", "drop_pincode": "421504", } ]

Sample Response Body:

{ "meta": { "status": 200, "message": "SUCCESS", "success": true }, "result": [ { "predicted_sla_min": 2, "predicted_sla_max": 5, "all_map": {}, "min_sla_cp_id": null, "predicted_exact_sla": null, "pickup_date": null } ] }

Now we need to add result[].predicted_sla_min to the current date and that will become our customerDeliverDate

result[].predicted_sla_min represents the number of days, so as per the above response it's 2 days, so adding 48 hours to the current date will be our customerDeliveryDate

So now our API response will be:

{ "isCOD": true, "isServiceable": true, "customerDeliveryDate": "02-12-2021", "message": "Pincode serviceable" }

 

Related content