Hackathon - Team Thripppey

Use Case 2 - Flow + Email Booking Confirmation

Explore

Use Case 1 - Simple Email Quoting

Objective: This use case involves processing FCL email quote requests by utilizing the Hackathon DB, which is also a Google Sheet, to derive rates for specific container types and routes. After calculating the appropriate rates, the system generates a quote and records the information in the "Result-EmailPath-QuoteGenerated" tab of the sheet. Finally, an email is sent back to the requester, containing the generated quote and a unique quote ID.

Resources :

https://docs.google.com/spreadsheets/d/1LrOovM0pk8ZKgeNi4LHAn3TtkeS1LmPOzJgpxRtUwGs/edit?gid=375825848#gid=375825848⁠

⁠

Guide Documentation :

https://docs.google.com/document/d/1JcZJZvciAM7t3BRs9tGJ65Ju2a4HTuDq9MtfQC_y_Ak/edit#heading=h.y3l79jns88ke⁠

Solution : Ideal Workflow :

⁠

a. Ease of Configuration:

The super admin dashboard played a crucial role by providing an easily configurable interface that allowed adjustments without frequently updating lookup sheets or the database. This reduced the complexity of maintaining and modifying configurations.

b. Scalability of Implementation:

Challenge:

Ensuring that the system can scale to handle increasing email traffic and larger datasets while maintaining performance.

The implementation is scalable, but the deletion of data from the sheet poses challenges. Mistakes in data alteration can occur, and due to no restricted access.

2. Solution Workflow

⁠

⁠

a. Handling Emails:

The system is designed to process specific types of FCL quoting emails, such as:

"Please quote me for two 20ft high cube's from Hong Kong to Long Beach."

"Please quote me for a 40ft standard from Chennai to Long Beach." With the use cases in hand we used our pre-existing email service for email and quote classification and model extraction. task email classification : email_classification_task_v2_peg quote classification : email_quote_classifier_task_v2 model extraction : email_extraction_task_v5.4_sl

b. Rate Information Retrieval:

The system uses the Hackathon DB via Google Sheets and Google API to derive rate information from the relevant sheet table. This was something Get Access Token

curl --location 'https://www.googleapis.com/oauth2/v4/token' \

--header 'Cookie: "access_token": "ya29.a0AcM612xi2A-eCUepH619423gx-ISZB_OpCfMaPuUMxeryaVcULZgQeJtetSGHKPJB1_SGtb2dbOhds6rd9yN0Xb6JX48gylz9Aib3QKSijOsaBADt2ppNWI94Oe2WlPGzUnvdXLx4_SbCBxpKtkxqnNUe6pSr_nXuyZQaCgYKAWsSARASFQHGX2MiZyWqKtfJ76hhib5YO3ZPwQ0171",' \

--header 'Content-Type: application/json' \

--data '{

"client_id" : "644492635423-ni6ah015c9cbmcm5addg2rtj8g9lr316.apps.googleusercontent.com",

"client_secret" : "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",

"refresh_token" : "1//05jMTrxWwgwmHCgYIARAAGAUSNwF-L9IrbuTUyjy5g7GG9AlQT2PXN4-fwLxXD0LLtLvKQGi6stcXG52c1V3QQq59yyyUpcaRG00",

"grant_type" : "refresh_token"

}

Get Sheet Data

curl --location 'https://sheets.googleapis.com/v4/spreadsheets/1LrOovM0pk8ZKgeNi4LHAn3TtkeS1LmPOzJgpxRtUwGs/values/EmailPath-Rate Sheet!A2:H500?alt=json' \

--header 'Authorization: Bearer ya29.a0AcM612wB_XTM-9W0tuL8cD9Y2ugI-o6g4JogghGurGAGM7HhqezRS5CE9rvQmKHxfYM4UcQc5gg62SCOqKMw2X2bOGgE0d2o5B_eywpaQxZd6Ny_HLVfQL1EMGXrdbD_7k4tv9RxZwA6RcAhztWOvvAwJL65k_EnqIAIZYbT5waCgYKAV0SARASFQHGX2Mib45MEnohcXtO8vb0vYGmxA0177' \

--data ''

Rate Calculation

function test() {

let unlocode_data = {

hongkong: "HKHKG",

shangai: "CNSHA",

longbeach: "USLGB",

};

const db_data = _.get(payload, "generated.get_sheet_data.data.values");

const ext_data = _.get(payload, "generated.mapping.result.extracted_data.0");

const container_type = _.get(ext_data, "container_details.0.container_size");

const container_count = _.get(

ext_data,

"container_details.0.container_count"

);

let origin_loc = _.get(ext_data, "origin.location")

.replace(/\s/g, "")

.toLowerCase();

let dest_loc = _.get(ext_data, "destination.location")

.replace(/\s/g, "")

.toLowerCase();

const origin_code = unlocode_data[origin_loc];

const dest_code = unlocode_data[dest_loc];

let total_charge = 0;

let freight_charge = 0;

let charge = 0;

db_data.map((item) => {

if (

item[0] === container_type &&

item[2] === origin_code &&

item[3] === dest_code

) {

if (item[5] === "Freight") {

freight_charge = Number(item[6].replace("$", "").replace(/,/g, ""));

} else {

charge = Number(item[6].replace("$", "").replace(/,/g, ""));

}

total_charge = freight_charge + charge;

}

});

return {

type: container_type,

origin: _.get(ext_data, "origin.location"),

destination: _.get(ext_data, "destination.location"),

pol: origin_code,

pod: dest_code,

freight: freight_charge,

fuel: charge,

total_container: container_count,

total: total_charge * container_count,

description: `Quotation for ${container_count} ${container_type} from ${origin_code}(${_.get(

ext_data,

"origin.location"

)}) to ${dest_code}(${_.get(

ext_data,

"destination.location"

)}). Your total charge is ${total_charge * container_count}`,

};

}

Quote Generation:

Once the rate information is retrieved, the system generates a quote and writes the data to the tab titled "Result-EmailPath-QuoteGenerated."

Post to Sheet

curl --location 'https://sheets.googleapis.com/v4/spreadsheets/1LrOovM0pk8ZKgeNi4LHAn3TtkeS1LmPOzJgpxRtUwGs/values/Result-EmailPath-QuoteGenerated!A:G:append?valueInputOption=USER_ENTERED' \

--header 'Authorization: Bearer ya29.a0AcM612wOCfL4ChE9T03DJUGgxIvzC072zXzfTis3MTT0B0euLz8KhZ1j2Txg0v8vCK4PYDkIDvRTRSCOhqd28xn0dCZbKUznhsveM_I5lEdt0BdvaUAYRcTfJSRL4YJT3PqdnF-XPj0sz4AuiAoVn5HNps9Y7TeaVz7uNIeOaAaCgYKARoSARASFQHGX2Mi8SPw_6EjZBwCiDTXoETlHA0177' \

--header 'Content-Type: application/json' \

--data '{

"values": [

["foo", "bar"]

]

}

. Email Response:

After generating the quote, the system automatically sends an email back to the requester, including the quote details and a unique quote ID.

3. Completeness of the Solution and Innovation

a. Completeness:

The solution covers the entire process from email parsing to quote generation and response. By integrating with the Hackathon DB and automating the response process, the system ensures that all necessary steps are completed without manual intervention.

b. Innovation:

The use of the super admin dashboard for easy configuration allows rapid adjustments without requiring updates to the underlying database or lookup sheets. This innovation reduces downtime and enhances system flexibility. However, care must be taken to manage data deletion, as mistakes can occur in case of sheet data storage. State Flow Diagram

FCL Quote

8.1 kB

⁠

Want to print your doc?
This is not the way.

Try clicking the ⋯ next to your doc name or using a keyboard shortcut (

CtrlP

) instead.