Route calls using CFD

[roney_ringoffice]

Hi,

I need to create CFD where a client is prompted to a enter a route name( customer will say the route name) and then prompted for the time of the day AM or PM. System will then lookup that route and AM/PM in a spreadsheet/database and depending on result from spreadsheet/database route the call to a call queue within 3cx.

For example :

Incoming Call Prompt: What route are you calling about?( Capture caller’s voice (speech to text))
Prompt: Press 1 for AM or 2 for PM?
Lookup in spreadsheet/database for Route X and XM then transfer caller to that call queue

How can I do that? Any help would be highly appriciated.Thanks in advance

 

[edossantos_sipcaller]

Hello,

You will need to use the Voice Input prompt for speech recognition to start. The following article will be helpful for this:
https://www.3cx.com/docs/cfd-google-cloud-speech/

Checking from a spreadsheet will be quite challenging. If you want to query a database, check this:
https://www.3cx.com/docs/cfd-accessing-database/

 

[VoIPTools]

@roney_ringoffice,

If your server is hosted in the cloud (not even possible with hosted-by-3CX servers) uploading a text file every time there is a route change may become tiresome. I would recommend a database.

Keep in mind, however, that you are not permitted to install a DBMS directly on the 3CX server, nor are you permitted to use the 3CX internal database. As a result, you will need to host the database on a separate server. That means, for cloud-based 3CX servers, you will either need to expose your database to the internet, or create a web-service front end for your database.

If you don't want to tackle this yourself, you can contact either @ConceptsWeb or @VoIPTools. We can help.

 

[roney_ringoffice]

I am trying to connect the DB in my CFD but I am encountering this error.any idea?




ystem.AggregateException: One or more errors occurred. (Unable to connect to any of the specified MySQL hosts.)
---> MySqlConnector.MySqlException (0x80004005): Unable to connect to any of the specified MySQL hosts.
at MySqlConnector.Core.ServerSession.OpenTcpSocketAsync(ConnectionSettings cs, ILoadBalancer loadBalancer, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/ServerSession.cs:line 1148
at MySqlConnector.Core.ServerSession.ConnectAsync(ConnectionSettings cs, MySqlConnection connection, Int32 startTickCount, ILoadBalancer loadBalancer, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/ServerSession.cs:line 438
at MySqlConnector.Core.ConnectionPool.ConnectSessionAsync(MySqlConnection connection, String logMessage, Int32 startTickCount, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/ConnectionPool.cs:line 403
at MySqlConnector.Core.ConnectionPool.ConnectSessionAsync(MySqlConnection connection, String logMessage, Int32 startTickCount, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/ConnectionPool.cs:line 408
at MySqlConnector.Core.ConnectionPool.GetSessionAsync(MySqlConnection connection, Int32 startTickCount, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/ConnectionPool.cs:line 98
at MySqlConnector.Core.ConnectionPool.GetSessionAsync(MySqlConnection connection, Int32 startTickCount, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/ConnectionPool.cs:line 128
at MySqlConnector.MySqlConnection.CreateSessionAsync(ConnectionPool pool, Int32 startTickCount, Activity activity, Nullable`1 ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlConnection.cs:line 929
at MySqlConnector.MySqlConnection.OpenAsync(Nullable`1 ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlConnection.cs:line 423
at CallFlow.CFD.DatabaseAccessComponent.PrepareCommandAsync(DbConnection conn)
at CallFlow.CFD.DatabaseAccessComponent.ExecuteScalarAsync(DbConnection conn)
at CallFlow.CFD.DatabaseAccessComponent.ExecuteStatement(DbConnection conn)
at CallFlow.CFD.DatabaseAccessComponent.ExecuteStart()
at CallFlow.CFD.DatabaseAccessComponent.Start(TimerManager timerManager, Dictionary`2 variableMap, TempWavFileManager tempWavFileManager, PromptQueue promptQueue)
at CallFlow.CFD.SequenceContainerComponent.Start(TimerManager timerManager, Dictionary`2 variableMap, TempWavFileManager tempWavFileManager, PromptQueue promptQueue)
at CallFlow.CFD.SequenceContainerComponent.OnPromptPlayed(TimerManager timerManager, Dictionary`2 variableMap, TempWavFileManager tempWavFileManager, PromptQue

 

[VoIPTools]

I will assume that your MySQL server is not installed on the 3CX server.

1. Have you enabled remote access to MySQL in the MySQL configuration?
2. If there is a firewall protecting MySQL, have you opened the necessary ports?
3. If you are referencing the MySQL server via FQDN, can your 3CX server resolve the FQDN to the correct IP address?

 

[edossantos_sipcaller]

Can you show how you're configuring the Database Access component? Hide sensitive data, but leave the keys so we can see the properties you're setting to specify the connection.

 

[roney_ringoffice]

Hi,
Thanks for all your suggestion.it was permission issue for remote host to access the database.Now I can access database and after running my query based on customer input , I am getting a scalar result from the DB query in CFD and I want to pass that value to Transfercall component. But I can see the query is running but there is no value returning from the DB query . If I run this query in my Mysql (SELECT destinationq FROM routes where routesnumber=' CB 25 ') it gives me the output 810.Please see attached screenshot if you need.



'DatabaseAccess1' - Start executing component with server='64.176.220.227' - port='3306' - database='busroute' - statementType='Scalar'
2024/09/13 12:36:39.309|16104|0058|Trc|CallPair._762_17.Main.6048.[C:2305.2]-From script: Findroutes - CallID 00000191EC3E16FA_2305 - CallFlow.CFD.MySqlDatabaseAccessComponent 'DatabaseAccess1' - Command to execute: SELECT destinationq FROM routes where routesnumber=' CB 25 '
2024/09/13 12:36:39.318|16104|0058|Trc|CallPair._762_17.Main.6048.[C:2305.2]-From script: Findroutes - CallID 00000191EC3E16FA_2305 - CallFlow.CFD.ParallelExecutionComponent 'ParallelExecution1' - Start waiting for parallel execution result
2024/09/13 12:36:39.540|16104|0058|Trc|CallPair._762_17.Main.6048.[C:2305.2]-From script: Findroutes - CallID 00000191EC3E16FA_2305 - CallFlow.CFD.MySqlDatabaseAccessComponent 'DatabaseAccess1' - End executing component with ScalarResult:
2024/09/13 12:36:39.540|16104|0058|Trc|CallPair._762_17.Main.6048.[C:2305.2]-From script: Findroutes - CallID 00000191EC3E16FA_2305 - CallFlow.CFD.SequenceContainerComponent 'parallelExecutionComponentBranch1' - Start executing component 'TransfertoQ'
2024/09/13 12:36:39.542|16104|0058|Trc|CallPair._762_17.Main.6048.[C:2305.2]-From script: Findroutes - CallID 00000191EC3E16FA_2305 - CallFlow.CFD.TransferComponent 'TransfertoQ' - Transfering call to destination=''.
2024/09/13 12:36:39.543|16104|0058|Trc|CallPair._762_17.Main.6048.[C:2305.2]-From script: Findroutes - CallID 00000191EC3E16FA_2305 - CallFlow.CFD.TransferComponent 'TransfertoQ' - Adding timer to monitor transfer call progress.
2024/09/13 12:36:39.598|16104|0005|Inf|ScriptRunner.ScriptRunner-CallStorageSize=1(1)
2024/09/13 12:36:44.543|16104|0058|Trc|CallPair._762_17.Main.6048.[C:2305.2]-From script: Findroutes - CallID 00000191EC3E16FA_2305 - Callflow - OnTimeout for component 'Askfordepartment_Conditional'
2024/09/13 12:36:44.543|16104|0058|Trc|CallPair._762_17.Main.6048.[C:2305.2]-From script: Findroutes - CallID 00000191EC3E16FA_2305 - CallFlow.CFD.ConditionalComponent 'Askfordepartment_Conditional' - OnTimeout for branch 'Askfordepartment_Conditional_ValidInput'
2024/09/13 12:36:44.543|16104|0058|Trc|CallPair._762_17.Main.6048.[C:2305.2]-From script: Findroutes - CallID 00000191EC3E16FA_2305 - CallFlow.CFD.SequenceContainerComponent 'Askfordepartment_Conditional_ValidInput' - OnTimeout for component 'ParallelExecution1'
2024/09/13 12:36:44.543|16104|0058|Trc|CallPair._762_17.Main.6048.[C:2305.2]-From script: Findroutes - CallID 00000191EC3E16FA_2305 - CallFlow.CFD.ParallelExecutionComponent 'ParallelExecution1' - OnTimeout for branch 'parallelExecutionComponentBranch1'
2024/09/13 12:36:44.543|16104|0058|Trc|CallPair._762_17.Main.6048.[C:2305.2]-From script: Findroutes - CallID 00000191EC3E16FA_2305 - CallFlow.CFD.SequenceContainerComponent 'parallelExecutionComponentBranch1' - OnTimeout for component 'TransfertoQ'
2024/09/13 12:36:44.544|16104|0058|Trc|CallPair._762_17.Main.6048.[C:2305.2]-From script: Findroutes - CallID 00000191EC3E16FA_2305 - CallFlow.CFD.TransferComponent 'TransfertoQ' - Monitoring transfer call progress - transfer failed.

 

[roney_ringoffice]

I am using this statement in the Call flow - CONCATENATE("SELECT destinationq FROM routes where routesnumber=' ",Askfordepartment.RecognizedText," ' " )

 

[edossantos_sipcaller]

I am using this statement in the Call flow - CONCATENATE("SELECT destinationq FROM routes where routesnumber=' ",Askfordepartment.RecognizedText," ' " )

OK, the problem is before executing the SQL statement. The connection can't be established. How are you configuring the connection to the database?

 

[roney_ringoffice]

Hi ,

Please check the attached image.I do no see any dataabse connection error in 3cx call flow log.Do you see any error?Here is database connection

 

[edossantos_sipcaller]

The issue you're having is exactly while connecting to the database:
MySqlConnector.MySqlException (0x80004005): Unable to connect to any of the specified MySQL hosts.

I can't tell from the screenshot what could be wrong. Ensure that the HostName / FQDN you're using for the database properly resolves to an IP address. Ensure that you don't have firewalls blocking the connection.

 

[roney_ringoffice]

Hi,

I found out the issue.The issue was with the expression.I fixed it. now my cfd can read the database tables.but Can you suggest how can I train the google text to speech well so that it can understand what I am saying? .My database First column looks like this below so sometimes the speech to text get the word correctly and send the values (from column A) correctly to my DB and I get the correct result in CFD prompt.So whats the best way to organize the column A so that Always easily it matches what customer or user is saying.

BS H5	AM	285
CA H6	PM	280
CAB1	PM	285
CB 25	PM	810
CB 27	PM	810
C B 2 5	AM	810
D B 2 5	AM	810
O A H S 1	AM	810
M T T 3 P	AM	810
RA34	AM	810
RA 34	AM	810

 

[roney_ringoffice]

SELECT destinationq FROM routes where routesnumber='r a 34'

So like here you can see my user says ra 34 or r a 34 but the google transcribes it to r a 34.which is wrong.so how do I eliminate this kind of issue with speech to text?

 

[edossantos_sipcaller]

In the Voice Input component you can configure Hints. These hints will tell Google what to expect from the input, so it can do a better recognition. To do this, you use speech adaptation. The tokens you can use are defined by Google here. You can specify tokens that match what you expect from the user. For example, I can see that you expect separated letters and numbers, so you could use the hint:
$OOV_CLASS_ALPHANUMERIC_SEQUENCE

But this is just a hint, you can't be sure about what Google will recognize, so your database needs to contemplate variants of those values. The best you can do is regularly checking the 3CXCallFlow.log file in verbose mode, and see what Google is returning, and then you add those variants to your database.