/
Customer And Address LLD

Customer And Address LLD

Owned by Rohit Kumar
Last updated: Nov 29, 2023
5 min read

What is purpose of this article?

In this article we will discuss implementation logic to populate ‘addresses’ and ‘customers’ table respectively

Steps to Achieve Customer and Address Cron Job

  1. Cloud Scheduler will hit cron job API (_ah/api/esb/v1/customers-cron) with given request body

    { "isHistoric":true, "startDate":"2023-11-23 15:57:50", "endDate":"2023-11-25 15:57:50", "interval": 7 }

  2. From the cloud scheduler request body we will check whether it is historic sync or regular sync

    1. If it is historic sync then we will get start date, end date and interval values from request body

    2. If it is regular sync then we will set static 24hours time interval of the current date

  3. Using time interval we will fetch customers details of Eshopbox shipping from big query using query given below

    SELECT workspace, shipping_customer_name, shipping_email_id, shipping_contact_number 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-01") AND DATETIME_ADD("2023-09-01", INTERVAL 60 DAY) AND esb_shipping = '1' GROUP BY workspace, shipping_customer_name, shipping_email_id, shipping_contact_number

  4. After fetching customers details from big query we fetch customer details from WMS Database using unique composite key workspace,email and phone number using query given below

    SELECT accounts.account_slug, customers.email, customers.phone FROM customers LEFT JOIN accounts ON customers.account_id = accounts.id WHERE accounts.account_slug IN (farmely) AND customers.phone IN ( '7204634592', '9068070522', '7259034618', '9712919840' ) AND customers.email IN ( 'haifabanday@yahoo.com', 'rashmitejaswinir@gmail.com', 'deekshabarora@gmail.com', 'g.bhatnagar22@gmail.com' );

  5. Using result from big query and wms database we prepare a look up table using workspace##email##phone as unique key and find out new data to be inserted

  6. Using these new data we will fetch orders details from Big Query using query given below

    SELECT channel_order_id, current_status, shipping_customer_name, shipping_email_id, shipping_contact_number, workspace, shipping_address, shipping_city, shipping_pin_code, shipping_state, current_status, current_status_remark, shipment_id, return_id, return_reason, reason_for_failed_delivery, 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 workspace IN ('farmely') AND shipping_email_id IN ('shubhmkhajuriya786@gmail.com') AND shipping_contact_number IN ('8966055092');

  7. We will create another lookup table using the data from above query where key will be worskpace##email##phone corresponding to this key there will be another map which contains details about orders, return, rejectcted percentage etc. we will perform following steps to prepare lookup table

    1. To populate orders count we do followings

      1. create key named “order_id”

      2. create a HashSet which will contains order_id

      3. If record is already present given unique key(workspace##email##phone) then go to inner map and get order_id and add order_id of big query record into set

      4. size of the set will be the count of unique orders of a customer which identified using workspace##email and phone

    2. In similar way we calculated order count we calculate return count

      1. create key named “return_id”

      2. create a HashSet which will contains return_id

      3. If record is already present for given unique key(workspace##email##phone) then go to inner map and get return_id and add order_id of big query record into set

      4. size of the set will be the count of unique returns of a customer which identified using workspace##email and phone

    3. In similar way we calculate shipment count

      1. create key named “shipment_id”

      2. create a HashSet which will contains shipment_id

      3. If record is already present for given unique key(workspace##email##phone) then go to inner map and get shipment_id and add shipment_id of the big query record into set

      4. size of the set will be the count of unique returns of a customer which identified using workspace##email and phone

    4. In similar way we calculate rejected shipment count

      1. create key named “rejected_shipment_id”

      2. create a HashSet which will contains shipment_id

      3. To get rejected shipment_count we will get those shipment_id which satisfy following condtions

        1. Where return_reason = RTO and

        2. failed delivery reason will be “Rejected by customer“

        3. current_status not delivered

      4. If record is already present for given unique key(workspace##email##phone) then go to inner map and get shipment_id and add shipment_id of big query record into set

      5. size of the set will be the count of unique returns of a customer which identified using workspace##email##phone

    5. Similarly we will get count of Cancellation and Returned Item conditions count as well

  8. After preparing above lookup table we will fetch address details of new customers from BigQuery using query given below

    SELECT workspace, shipping_email_id, shipping_contact_number, shipping_address, shipping_city, shipping_pin_code, shipping_state, shipping_country FROM `analytics-340903.bq_export_sync.bq_sale_order_report_v2` WHERE order_journey_update_time IS NOT NULL AND workspace IN ('farmely') AND shipping_email_id IN ('shubhmkhajuriya786@gmail.com') AND shipping_contact_number IN ('8966055092'); GROUP BY workspace, shipping_email_id, shipping_contact_number, shipping_address, shipping_city, shipping_pin_code, shipping_state, shipping_country

  9. After getting address details use order details lookup table to add address list into inner map

  10. After we done with orders details lookup table and new customers lookup table we will populate all the additional details into customers Model class

  11. After completing List of Model class for new customers we save these details into database

  12. Then after we will call another Task Queue Which will fetch data from Customers table into batches and fetch orders details from Big Query and update existing customers details

Steps to achieve post API to add customers

  1. We get hit from frontend on POST API: _ah/api/esb/v1/addCustomers with given request body

    { "customers": { "workspace": "farmely", "name": "Rohit", "email": "rohitkumar@eshopbox.com", "phone": "9876543210", "status": "ACTIVE" }, "address": [ { "addressLine1": "Udhyog Vihar", "addressLine2": "Phase 3", "pincode": "122021", "city": "Gurgaon", "state": "Haryana", "Country": "India" } ] }

  2. After getting request body, we check if customer already exist or not

  3. If customer already exist we return existing customer details to frontend

  4. Else we fetch order details of this customer from Big Query and prepare customer class model

  5. Also we will fetch address details from big query and find unique address by concatenating in special order and converting it into lower case

  6. and save customers details into table and get unique primary key of customers after saving successfully

  7. we use this unique key to as foreign key to bulk insert address into addresses table in database

  8. after saving we will prepare response and return to frontend

 

Steps to achieve PUT API to Update Customers

  1. We get hit from frontend on PUI: _ah/api/esb/v1/updateCustomerDetails/{customer_id}with given request body

    { "workspace": "farmely", "name": "Rohit", "email": "rohitkumar@eshopbox.com", "phone": "9876543210", "status": "ACTIVE" }

  2. After getting request body, run update query using customer_id and return updated result to frontend

Steps to create GET API to get customer and address details

  1. Frontend will hit get API: _ah/api/esb/v1/shipping/customers to fetch customers details

  2. We will fetch customers details using page and per-page value from database

  3. After getting data we will prepare response and return to frontend

  4. The response body will be in following structure

    { "total": "71674", "perPage": "50", "data": [ { "id": 9823, "customerName": "John Doe", "account": "farmely", "status": "ACTIVE", "customerCreatedOn": "1700489886993", "customerUpdatedOn": "1700489886993" "email": "testing@email.com", "phone": "9876543210", "whatsAppStatus":Active, "orderDetails":{ "orderCount":10, "returnCount":2, "customerCancellation":2, "sellerCancellation": 3 } "customerAddresses":[ { "addressLine1":"Phase 3 Gurugram", "addressLine2":"Udhyog Vihar", "shippingCity": "Bengaluru", "shippingState": "Karnataka", "shippingPostalCode": "560037" }] } ] }

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Add label

Related content