Hello Ladies and Gentleman,
I am currently having some trouble with the “Queries” area of the module SmartRoutes that uses SQL/(MySql) “Queries” on an external (MySql) database.
[size=20]I’m trying to have the route perform this little stunt.[/size]
When the caller calls the DID number, I need SmartRoutes to query my external MySql CRM database for the table “companies” and search the column “company_phone1”.
If it finds the matching 10 digit CID, it needs to now check the column “company_type” in the same row it found that CID for the value in the “company_type” column. If that value is for example a number 5 then proceed to Queue: 4.
If the value is let’s say 12 from the “company_type” column, go to a different place say Queue: 1.
This is what I have to work with in the Queries section of the module that looks like:
The option for the destination that I have to work from is as follow and I have provided a screen shot from SmartRoutes website that can be viewed here:
The Queries designates the initial search that the SmartRoutes needs to perform.
There are two types of Queries - Basic and Advanced.
[size=20]Basic[/size]
Table The name of the table to search.
Search Column The name of the column to search
Look For Look for the “Extension”, “Called Number (DID)”, or “Caller ID”.
Return Column What column should be returned when a match is found? Note that this value will be assigned to the Asterisk var DBRESULT.
The Advanced(has 2 options) 1.SQL 2.AST Vars 1-5
I think that I have all of the destinations figured out, but the Data Queries is the problem where I’m stuck on. I have have Googled the hell out of this and I cannot find any simple enough explanation on inputting (writing) the SQL/MySql Queries.
I have not used this module much, but I do have some experience with MySQL. What you are trying to do is a simple query, why aren’t you using the Wizard to structure your query? It should look something like this:
Table: companies
Search Col: company_phone1
Look For: CallerID
Return Col: company_type
Then set each of your destinations based on the contents of the company_type column. You probably want a match type of “Exact”, you probably don’t want anything in the “Override Primary Ext/Context”, then choose the default destination in case the query returns a value that is not set specifically.
I have it setup exactly as you suggested, but I have nothing in the Data Queries area under that “Main Query”. All the calls fail over to the default Destination.
The freepbx system status gives me the following:
"There are 1 bad destinations"
Ignore this
DEST STATUS: CUSTOM
Inbound Route: Technical support (phone number) Added 51 minutes ago
(retrieve_conf.BADDEST)
Here is what it suggests on the modules site for Data Queries:
These queries are used to pull data related to this call or route that can be used in the routing or for other purposes (like setting CDR fields or caller id name prefix).
Enter your own SQL with multiple returns (up to 5 returns per query). The returns will be assigned to the Asterisk vars specified so that they can be used in the dialplan (or replace/update existing Asterisk dialplan vars).
These queries are only performed if a match is found on the first return from the main query against destination match values below.
SQL
Enter an SQL query to use. You can return up to 5 columns and they will be assigned to the associated Asterisk vars. The first column returned will be the key used to match a destination below. The other columns can be assigned to Asterisk dialplan vars for use in the dialplan, as a CID name prefix, recorded into the CDR, etc.
Note that any Asterisk vars in the query need to be indicated as they are in Asterisk with ‘${asteriskvar}’.
Assign AST Vars 1-5
Up to five results from the query can be assigned to Asterisk dialplan vars. Examples would be: CALLERID(name), CDR(userfield), OUTCID_2, or a custom variable that you put in the FreePBX Caller ID prefix field.
Note that you can leave a return value blank and not assign a var.
Note that the vars entered here do not need to be formatted as Asterisk vars but just include the var name.
What should I use in the SQL fields? Currently, how would the dial plan know that if the matching CID row has a 5 in the same rows under column “company_type” without these values? I would guess that I need to supply that information here right?
I want the SmartRoutes to search and find the the callers CID and if it finds the number 5 in the company_type go on to the queue that I want. I understand that if I want it to go on to other destinations, that I will have to create more “SmartRoutes” and I understand that. If anyone has the knowledge with mysql and Asterisk, I would appreciate it more then i could ever express.
