Advanced Cfd - Creation of an automatic appointment management service

[_aMiCiCi_]

Good morning, last night, checking the forum, I was looking for how to create a cfd that, based on a database, performs the search, control and eventual insertion of an appointment with keyboard choices.

As I had thought it, the customer calls, we record the entry session.ani, upon selecting the IVR who intends to book an appointment with us, the system queries the dates and times actually available closest to him and via google text to speech proposes them in the quick selection, first the date and confirmed the date the available times.

The customer can choose between the three proposals advanced by the system or with the 4th choice if you want to change the date.

If he confirms among the first three choices through 1,2,3 from the keyboard, he goes on.

If he presses key 4, he is instead offered 5 dates among those available (perhaps those that have more times available).

Once the date between the first three choices or the fourth choice is confirmed, the mechanism for choosing the times is replicated, always checking the database from time to time.

Collected the information and registered in the sql database, we send an email to us and an email to the end customer confirming the appointment.

What do you think of logical reasoning?
How do you think I should set up the database itself?

Are we all able to help each other, solving and creating this CFD with a high complexity of management and variables?

What do you think of the reasoning made in the logical creation of the cfd?

If you ever have alternative solutions or additions, he will be happy to read and compare them.


Thanking you in advance for your help, I await your valuable suggestions.

Joseph

 

[Evolute IT]

I recommend you go over some of the example projects, this will help you understand CFD in a real-world situation and from there it should be easier for you to build what you need.

 

[edossantos]

The logic described looks good to me. What I don't understand is if you already created this or you need any help to get this done. What you describe is totally doable, quite a challenging project indeed, but totally doable.

 

[VoIPTools]

I will offer some things to consider.

• What if you can't find the phone number when you attempt to find the contact in your database? You would need to ask for additional information such as an account number or other information you can use to identify the contact.
  
  
• Remember, because the interface is a phone, entering characters is, practically speaking, not an option. Limit needed input to only digits.
  
  
• 3CX does not want you installing a database (DBMS) directly on your 3CX server. So, you have two options (1) make a direct connection to a remote database, or (2) create a web service and use that as the interface to the database. Using a web service is the preferred approach
  
  
• If your 3CX server is hosted, exposing your database to the internet is a HORRIBLE idea. In this scenario, the only option is to create a web service interface to the database, and restrict access to this web service to only permit the IP of the 3CX server
  
  
• 3CX does not permit CFD applications on their hosting platform. If you need to implement a CFD application, you will need to host your PBX with a different vendor (AWS, Azure, etc.)
  
  
• What you are proposing is absolutely feasible from a technology standpoint, but it is not a trivial project. If you want to embark on a project of this complexity, you can expect answers from the forums for very specific questions, but this is not likely to be a community effort to help you build this application. You will have to learn the Call Flow Designer and perform 100% of the work, or engage a consultant.

 

[Evolute IT]

If you want to embark on a project of this complexity, you can expect answers from the forums for very specific questions, but this is not likely to be a community effort to help you build this application. You will have to learn the Call Flow Designer and perform 100% of the work, or engage a consultant.

Couldn't said it better.

 

[_aMiCiCi_]

I recommend you go over some of the example projects, this will help you understand CFD in a real-world situation and from there it should be easier for you to build what you need.

To be created totally from scratch, I will soon prepare a more detailed draft that I will share with you.

 

[_aMiCiCi_]

Your observations are correct, but let's go in order point by point.

Point 1

• What if you can't find the phone number when you attempt to find the contact in your database? You would need to ask for additional information such as an account number or other information you can use to identify the contact.

In this specific case it is recognized by the incoming number only as a welcome message (I have already done this) by adapting a message of Welcome back Name and Surname or if the check of the db does not contain information it gives it the classic standard standard welcome message. In both cases, they then refer to the underlying IVR which is the same for both, it is just an "effective" business card.

I could add to every single booking an id that is managed on the database and sent to the customer in the confirmation email. So that at the entrance during a second call if the reservation has been managed, then passed it calls him only by name, if the reservation has not been managed by the number with which he booked it recognizes it automatically, alternatively I could put a check by typing id booking.

Point 2

• Remember, because the interface is a phone, entering characters is, practically speaking, not an option. Limit needed input to only digits.

Exactly, as I said in the first message the customer chooses the date and only after the time of those available by pressing the keyboard from a multiple choice that is given "of those available".

3 choices for dates
5 for timetables (if available)

And this is where I wrapped myself in the possible functions to create and how to create them.

If you know the product well, I ask for help in how to create this piece of variables / code.

Point 3,4,5

• 3CX does not want you installing a database (DBMS) directly on your 3CX server. So, you have two options (1) make a direct connection to a remote database, or (2) create a web service and use that as the interface to the database. Using a web service is the preferred approach
• If your 3CX server is hosted, exposing your database to the internet is a HORRIBLE idea. In this scenario, the only option is to create a web service interface to the database, and restrict access to this web service to only permit the IP of the 3CX server
  
  
• 3CX does not permit CFD applications on their hosting platform. If you need to implement a CFD application, you will need to host your PBX with a different vendor (AWS, Azure, etc.)

I had thought as you suggested on a database on an external server, unrelated to the installation made by 3cx to date on a digital ocean server.

For the database, hosted on another service, another ip (to be created) I insert a firewall filter so that it can only be interrogated by some ip addresses, with username, password.

You suggest in the web approach, certainly more comfortable, but easier to hack, right?

What do you propose for "dedicated web service" and with access filters?

Please be clearer.

Point 6

I hope to succeed in the enterprise and in your help, I conclude, as a last point, I agree with you, it is not a trivial project, if it were simple, others would have already done it and / or we would not be here to talk about it.

Definitely of my interest to create it, although there will be time and it will not be something simple.

In conclusion, I am in 3cx and I am going to register as a partner in Italy in the next few weeks for about 15 days and I already have a good patronage, I certainly have a lot to learn, I do not deny it, but with calm we build castles and complex applications.

In the next few days I will post a visual draft to be created and I would be grateful that you and many others can help me.

If you have other questions or initial concerns, I'm happy to receive them so I manage them in the "draft" that I will create.

Joseph

 

[VoIPTools]

Joseph,

There is nothing more insecure than opening up your entire database to the internet via a direct connection. Restricting access to the database IP port on your firewall is a step in the right direction, but the goal should be to minimize the surface area that an outside party has access.

A web service further restricts access to your DBMS by only exposing precisely what you want people to access. How you implement the web service is up to you. If it were me, as a Microsoft developer, I would create a "minimal API" using .Net / C# . I would also implement JWT authorization (token based security).

 

[_aMiCiCi_]

Great tip, I won't be as experienced as you are.

But as soon as I can, work commitments permitting, I get to work.

Trying to understand how to best "organize" your suggestion.

 

[_aMiCiCi_]

Hi guys, can you tell me where am I wrong?

The welcome message starts, selects option 1, stays on hold and always drops the line.

I deliberately put the same number with and without international prefix to understand what value it took.

Because after the positive test I would have added the other reading variables of the same row.

Indeed in this regard, how would you read the variable name and surname of the same line?

I relied on this guide:

and

https://www.3cx.com/docs/cfd-accessing-database/

 

[edossantos]

In order to investigate the issue, please enable verbose logs from the 3CX Console > Dashboard > Activity Log > Settings. Then make a test call where you reproduce the issue, and check the 3CXCallFlow.log file that you can download from the 3CX Console > Dashboard > Activity Log > Logs button > Instance folder.

 

[_aMiCiCi_]

The entire generated file is attached as a log.

 

[edossantos]

The entire generated file is attached as a log.

You missed the attachment.

 

[_aMiCiCi_]

You missed the attachment.

Yes this file is very big, i added this link download zip complete any test

 

[edossantos]

Don't upload the Support Information Package, that file contains sensitive information!

 

[_aMiCiCi_]

Correct link of log file is inserted https://drive.google.com/file/d/16V9RnzgOJnfYx0mmTq5WlcciU2qNZqA5/view?usp=sharing

I deleted all the above with the support files and the target link.

Sorry

 

[edossantos]

That's not the log I mentioned.... Please check my previous response. You need to check the 3CXCallFlow.log file.

 

[_aMiCiCi_]

Here you go, sorry I have been using 3cx for about 2 weeks.

Here is the requested file, also I add the screen where we can see the incoming call with one of the correct numbers in the database.

 

[edossantos]

From the logs, it looks like you're having an issue connecting to the database:

2022/04/27 21:06:55.902|5684|0032|Err|CallPair._80001_145.Demo_BookCall.344.[C:172.2]-From script: Test_database - CallID 000001806C6AE5AE_172 - Callflow - Error executing last component: System.Data.SqlClient.SqlException (0x80131904): Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement. This could be because the pre-login handshake failed or the server was unable to respond back in time. The duration spent while attempting to connect to this server was - [Pre-Login] initialization=10; handshake=15096;
---> System.ComponentModel.Win32Exception (258): Unknown error 258

A timeout is usually a firewall issue, but you need to investigate this.

 

[_aMiCiCi_]

I solved the database access problem to firewall.

I also changed the database, I switched to postgresql.

I recreated the table, I put the phone, name and surname values back in text mode, but nothing, it gives me another error, see below.