Low Level Design Of Customers(DEPRICATED)

What is the objective of this article?

This article aims to explore a method for creating a "Customers" webpage within the Eshopbox workspace. This webpage incorporates several beneficial features for users, such as the ability to add new customers to the database, include new addresses for these customers, and designate their status as ACTIVE, SUPPRESSED, or ARCHIVED. This status information is crucial for subsequent order processing. Additionally, users can view the order and return history of customers, as well as search for specific customers using their name, phone number, or email. To achieve above objective we need to address following points

Creation of Customers Table in database
Creation of Addresses Table in database
Condition to populate initial data into these two tables
Getting Order and Return History associated with these customers

How can the outlined objective be accomplished?

Create Customers table in database with following columns
1. account_id
2. name
3. phone
4. email
5. address_id
6. status
7. whatsapp_status
Create Addresses table in database with following columns
1. addressLine1
2. addressLine2
3. city
4. pincode
5. state
6. country
7. landmark
To populate customers table, we will create a cron which will fetch last 24 hours data based on condition given in this document(Suppressed Customers) through which we are populating “suppressed_customers” table or we can refer to the suppressed_customers table fetch data which is created at within last 24 hours and based on rejected count percentage we will populate Customers table
To populate addresses table, we will use same Cron job which we have used to populated Customers table and using account id, phone and email we will fetch addresses from WMS database and populate the customers table
To get order and return history we will use account_id, phone and email to query BIG Query table to get order and return history using following query
SELECT current_status, current_status_remark, shipment_id, return_id, return_reason, return_sub_reason, return_item_condition, cancelled_by, cancellation_reason, cancellation_sub_reason FROM `analytics-340903.bq_export_sync.bq_sale_order_report_v2` WHERE order_journey_update_time IS NOT NULL AND order_processing_on BETWEEN DATETIME("2023-09-28") AND DATETIME_ADD("2023-09-28", INTERVAL 60 DAY) AND esb_shipping = '1' AND workspace = 'farmely' AND shipping_contact_number = '9557932275' AND shipping_email_id = 'azeemparvez@outlook.com';
Create a GET API to get customers details.
Create a GET API to orders details
Create a POST API to update customers detail
Create a POST API to update customers address
Create a POST API to change customers status

Customers Cron Sequence Diagram:

CRON TO UPDATE CUSTOMERS AND ADDRESSES TABLE

Get account_id, email, phone, rejectedCount and total count using following query
SELECT account_id, email, phone, rejectedShipmentCount, totalShipmentCount FROM suppressed_customers WHERE created_at >= : startDate AND created_at <=: endDate
After getting suppressed_customers details we will filter out account_id, email and phone number based on rejectedShipmentCount percentage e.g. (rejectedShipmentCount*100/totalShipmentCount)
1. If rejectedShipmentCount >= 30 then get a list of account_id,email, and phone number list
2. using the list fetch customers details using query given below
  SELECT orders.shipping_customerName, orders.shipping_contactPhone, orders.shipping_email, channels.account_id, orders.shipping_addressLine1, orders.shipping_addressLine2, orders.shipping_city, orders.shipping_state, orders.shipping_postalCode, orders.shipping_countryName, orders.countryCode, FROM orders LEFT JOIN channels ON channels.id = orders.channel_id LEFT JOIN shipments ON shipments.order_id = orders.id WHERE channels.account_id IN (552, 801, 725) AND shipping_contactPhone IN ( '7204634592', '9068070522', '7259034618', '9712919840' ) AND shipping_email IN ( 'haifabanday@yahoo.com', 'rashmitejaswinir@gmail.com', 'deekshabarora@gmail.com', 'g.bhatnagar22@gmail.com' )
3. After getting result we will match with the list we obtained after applying rejetectedShipmentCount percentage condition
4. OPEN POINT HOW TO INSERT ADDRESS AND CUSTOMERS DETAILS

Sequence Diagram of GET APIs:

GET APIs to be created for customers page:

GET API: _ah/api/esb/v1/shipping/customers Response body of this API will be
{ "total": "71674", "perPage": "50", "data": [ { "customerName": "John Doe", "account": "farmely", "status": "1", "customerCreatedOn": "1700489886993", "email": "testing@email.com", "phone": "9876543210", "customerAddresses":[ { "addressLine1":"Phase 3 Gurugram", "addressLine2":"Udhyog Vihar", "shippingCity": "Bengaluru", "shippingState": "Karnataka", "shippingPostalCode": "560037" }] } ] }
GET API: _ah/api/esb/v1/shipping/customers/orders/search?workspace=farmely&email=testing@email.com&phone=9876543210
1. Using query parameter values we will get workspace, email and phone number
2. using workspace, email and phone number we will fetch order details using following query from Big Query:
3. SELECT order_id, current_status, current_status_remark, shipment_id, return_id, return_reason, return_sub_reason, return_item_condition, cancelled_by, cancellation_reason, cancellation_sub_reason FROM `analytics-340903.bq_export_sync.bq_sale_order_report_v2` WHERE order_journey_update_time IS NOT NULL AND order_processing_on BETWEEN DATETIME("2023-09-28") AND DATETIME_ADD("2023-09-28", INTERVAL 60 DAY) AND esb_shipping = '1' AND workspace = 'farmely' AND shipping_contact_number = '9876543210' AND shipping_email_id = 'testing@email.com';
4. Based on above result we will prepare response body as given below:
  { "totalOrderCount": "20", "returns": { "totalReturnCount": 20, "customerReturnCount": 10, "courierReturnCount": 5, "unknownReturnCount": 5 }, "cancellations": { "totalCancellation": 10, "customerCancellation": 5, "sellerCancellation": 5 }, "returnedItemConditions": { "totalReturnedItems": 8, "restockedCount": 2, "quarantinedCount": 3, "damagedCount": 2, "lostCount": 1 } }

Orders and Shipping Engine