Routing External Party to Last Agent they spoke to for continuity

Scenario

External party calls into an IVR/Group and speaks to an agent, they then hang up and forget to mention something to the agent, so rather than speaking to someone else, the caller calls back and it routes to the last person they spoke to.

This dial plan is structured to make a dynamic call routing decision based on recent call data from a MySQL database (cdr table). Add this to the start of your inbound dial plan to execute the script.

Here's a breakdown of each part of the dial plan:

1. Connecting to the MySQL Database

{
  "name": "CustomApp",
  "params": {
    "cwApp": "MYSQL(Connect connid localhost root wil01dix cdr)"
  }
}

Action: Connects to a MySQL database on localhost with the username root and password wil01dix. It connects to the cdr database.
Purpose: This initializes the database connection, which will be referenced later in the query steps using ${connid}.

2. Running a SQL Query to Find Recent Call Information

{
  "name": "CustomApp",
  "params": {
    "cwApp": "MYSQL(Query resultid ${connid} SELECT c_to FROM cdr WHERE c_from = '${CALLERID(num)}' AND answer >= NOW() - INTERVAL 10 MINUTE AND disposition = 'ANSWERED' ORDER BY start DESC LIMIT 1)"
  }
}

Action: Executes a SQL query to retrieve the c_to (destination number) from the cdr (Call Detail Record) table. The query filters by:
- c_from equals ${CALLERID(num)}, i.e., the caller's number.
- The call was answered in the last 10 minutes (answer >= NOW() - INTERVAL 10 MINUTE).
- The call disposition is ANSWERED.
- Orders the results by start DESC, so it fetches the most recent call.
- Limits the result to just one record.
Purpose: This retrieves the most recent destination number (c_to) for a call made by the caller in the last 10 minutes where the call was answered.

3. Fetching the Result from the Query

{
  "name": "CustomApp",
  "params": {
    "cwApp": "MYSQL(Fetch fetchid ${resultid} c_to)"
  }
}

Action: Fetches the c_to value from the result of the previously executed SQL query (referenced by ${resultid}).
Purpose: Retrieves the destination phone number (c_to) from the query result and stores it in a variable.

4. Logging the Result

{
  "name": "CustomApp",
  "params": {
    "cwApp": "NoOp(Result: ${c_to})"
  }
}

Action: Outputs the retrieved c_to value (the destination number) as a log message using NoOp.
Purpose: This is useful for debugging, as it logs the result of the query (c_to) to the console or logs.

5. Clearing the Query Result

{
  "name": "CustomApp",
  "params": {
    "cwApp": "MYSQL(Clear ${resultid})"
  }
}

Action: Clears the query result (${resultid}).
Purpose: Frees up resources after using the result of the database query.

6. Disconnecting from the MySQL Database

{
  "name": "CustomApp",
  "params": {
    "cwApp": "MYSQL(Disconnect ${connid})"
  }
}

Action: Disconnects from the MySQL database (${connid}).
Purpose: Closes the connection to the database after the query execution is complete.

7. Setting the Extension for Dialling

{
  "name": "CustomApp",
  "params": {
    "cwApp": "Set(Ext=${c_to})"
  }
}

Action: Sets a variable Ext to the value of c_to (the phone number retrieved from the query).
Purpose: This assigns the destination number (c_to) to the Ext variable, so it can be used in the dialling application.

8. Dialling the Phone

{
  "name": "DialPhone",
  "params": {
    "number": "${Ext}",
    "timeout": "",
    "options": "",
    "msgForOperator": ""
  }
}

Action: Dials the phone number stored in the Ext variable (which is the destination number retrieved from the query).
Purpose: This routes the call to the phone number stored in the Ext variable.

9. Queuing the Call (Fallback)

{
  "name": "Queue",
  "params": {
    "queueId": "1",
    "msgForOperator": "",
    "timeout": "",
    "mohClass": ""
  }
}

Action: If the previous DialPhone application doesn't work (e.g., the call doesn't go through), the call is routed to a queue (with queueId: 1).
Purpose: This ensures that if dialling the number fails, the call will be placed into a queue (likely for a follow-up or to wait for an agent).

Summary of the Workflow:

Connect to the MySQL database.
Run a SQL query to find the most recent answered call made by the caller in the last 10 minutes.
Fetch the destination number (c_to) from the query result.
Log the result for debugging.
Clear the query result and disconnect from the database.
Set the destination phone number to a variable (Ext).
Dial the destination number (Ext).
If dialing fails, queue the call in a queue with queueId: 1.

This dial plan effectively routes calls based on recent call data stored in the cdr database, making it dynamic based on the caller's history.