Generate .CALL from database


#1

I’ve been trying to find an exisiting example/script to build .CALL files based on data from a new table in a database. Does anyone know of such a beast? If not, I’ll have to hammer out some code but wanted ask before going down that road.

This is NOT for any kind of sales/telemarketer thing, just need to dial out numbers and spit out some information for internal use.


#2

instead of using .CALL files, look into the Originate command via the manager interface.

for example, with PHP, i could tie into MySQL, grab the numbers i need to dial, and then initiate the call via the manager by connecting to asterisk via a socket. depending on what type of mysql queries you are running, you could generate the entire script in about 20 or 30 lines, i would think.

the only thing that i’m not sure of is how to keep the script from initating the calls too fast or slow…i suppose you could also build script that would query the manager interface for the active channels, and then see if the channel you are using to initiate the calls is still active, and not start another call until that channel is dead…basically, an automated power dialer, if you will. not sure if you need something that complex, but i’m sitting here and can envision almost all of the code in my head.

HTH


#3

[quote=“whoiswes”]instead of using .CALL files, look into the Originate command via the manager interface.

for example, with PHP, i could tie into MySQL, grab the numbers i need to dial, and then initiate the call via the manager by connecting to asterisk via a socket. depending on what type of mysql queries you are running, you could generate the entire script in about 20 or 30 lines, i would think.

the only thing that i’m not sure of is how to keep the script from initating the calls too fast or slow…i suppose you could also build script that would query the manager interface for the active channels, and then see if the channel you are using to initiate the calls is still active, and not start another call until that channel is dead…basically, an automated power dialer, if you will. not sure if you need something that complex, but i’m sitting here and can envision almost all of the code in my head.

HTH[/quote]

I will definately check into the Originate via manager option. I will only be making a few calls per day (if that) so I don’t know that I need to worry about too many calls. I’m assuming that Asterisk will queue up these calls if there is not a line available but is that not the case?

Thanks for this very valuable information!!


#4

if you were to originate 5 calls at once, they all 5 go out at once…there is no built-in queuing system that i am aware.

like i said, the easiest way, in my mind, if you were going to be initiating multiple calls at once time, would be to set up the script so that when you initiate the first call, the script watches for that call to complete before sending the next. this shouldn’t be too difficult, it would just require you to parse the active channels list…i just found out about the ‘show channels concise’ command today, which is a much more parseable format than the original ‘show channels’.

let me know if you need more help with this, i’d be glad to assist.


#5

[quote=“whoiswes”]if you were to originate 5 calls at once, they all 5 go out at once…there is no built-in queuing system that i am aware.

like i said, the easiest way, in my mind, if you were going to be initiating multiple calls at once time, would be to set up the script so that when you initiate the first call, the script watches for that call to complete before sending the next. this shouldn’t be too difficult, it would just require you to parse the active channels list…i just found out about the ‘show channels concise’ command today, which is a much more parseable format than the original ‘show channels’.

let me know if you need more help with this, i’d be glad to assist.[/quote]

Hey Wes,
Sorry for the delayed response. I’ve been trying to get up to speed on everything. I’d love to get together and see what we could figure out. Can you PM me your e-mail address and I’ll let you know what I have in mind.

Take care,
Gregg


#6

Hey,

Im glad to have found this thread as it is somewhat close to what I have been researching as well…

I have a MySQL database which I will query for information to make outbound calls with Asterisk. Calls will be few in the beginning but I will need to be scalable (add multiple Asterisk boxes to handle outgoing call volume).

Im having a hard time working out the architecture of this solution. The database will store the dates and times for the calls to be placed, but I’m looking for a way to initiate and load-balance the calls. For instance, 5 calls need to be made 1 minute from now, and 12 made 3 minutes from now. A script needs to be constantly monitoring and then place the call using one of the Asterisk boxes which is least busy. Any ideas?

Similarly, this is NOT for telemarketing or sales which may be hard to believe but a new project I am working on. In short - yes the users will WANT to receive these calls.


#7

sorry, had a bad start to the week - we lost a server on monday.

anyways, i’d be happy to help still…perhaps we should try to figure out a framework or flowchart that gives an overview of what both of you want to happen, and go from there.


#8

Here’s an example of what im trying to do:

diagram

Basically, I need a way to keep querying the database and when the current time matches rows in the database, place the calls from one of the Asterisk boxes (preferably the one with the least utilization).

Ideally, I need a way to make sure the boxes aren’t stepping on each other’s toes. If I had one Asterisk machine, I could put the script on there. If I had 1 database server, I could put it there. But with scalability in mind, I would need a script, most likely running on each Asterisk machine that keeps querying for calls and marks the call as placed so another machine doesn’t place the same call? Im having trouble getting my head around this.
Any help would be appreciated!


#9

hmmm…one thing you could do is to have the servers only query once every 5 seconds…have one server query at 1 second, the second at 2, the third at 3, and so on…as long as the clocks are synched, that MIGHT work.

the only other thing that i can easily think of would to have the query process lock the table while searching, and if it finds a record, update a ‘server’ column to a certain value, so that the other servers know that it’s calling that record at that time…it’s probably more foolproof than the first setup, but it should work.

alternately, you could have the script running on the database server, and PUSH the calls to the asterisk boxes. so you’d have a script that monitors the call load on each box, and is also watching for ‘scheduled’ calls - when a call comes up, whichever box has the lowest call load gets sent the call (via an Originate command through the manager interface - don’t use .CALL files, we learned the hard way).

i just thought of this third method, and i’d say that’s the way to go…hell, i even have two of the three scripts you’d need written. one to initiate the call, one to query the call load, and another to query the scheduled calls.

the best part about the third method would be that you can add additional servers as needed, and just add them to the queried server list.

hope this helps out, i’m just thinking out loud at this point.


#10

ok, i got antsy and decided to post some code snippets to get the ball rolling.

this is my socket function for asterisk. i have all of my connection settings stored in whatever php file this function is listed in, and i just include it in every page that needs to access the manager interface.

function send_socket($socket_host,$socket_login, $socket_pass, $socket_port,$socket_errno,$socket_errstr,$socket_timeout, $socket_action, $socket_command) { $socket = fsockopen($socket_host,$socket_port,$socket_errno,$socket_errstr,$socket_timeout); if (!$socket) { return "$socket_errstr - $socket_errno"; } fputs($socket, "Action: Login\r\n"); fputs($socket, "UserName: $socket_login\r\n"); fputs($socket, "Secret: $socket_pass\r\n\r\n"); if($socket_action=='Command' && $socket_action != '') { fputs($socket, "Action: $socket_action\r\n"); fputs($socket, "Command: $socket_command\r\n\r\n"); } elseif ($socket_action != '') { fputs($socket, "Action: $socket_action\r\n\r\n"); } fputs($socket, "Action: Logoff\r\n\r\n"); while (!feof($socket)) { $output .= fread($socket, 8192); } fclose($socket); return $output; }

a very crude method to count the number of active calls would as follows:

Database schema (rough)

Table:  server
fields:  id, hostname, mgr_login, mgr_pass, mgr_port, call_count

Table:  calls
fields:  id, number, call_time, called

a page, being refreshed constantly, that updates the server call count. this is run from each asterisk server.

<?php

// set the server name for each server
$servername = 'someserver'; 

include 'function.php';
include 'mysql_class.php';

// we call the socket function to pull a list of active calls, including the manager header and footer (important later)
$active_calls = send_socket($socket_host,$socket_login, $socket_pass, $socket_port,$socket_errno, $socket_errstr, $socket_timeout, 'Status', '');

// we break the returned string into an array, based on line breaks
$active_calls_array = explode("\n\r", $active_calls);

// count the size of the array, and subtract 5 for the manager header and footer output.
$active_call_count = count($active_calls_array)-5;

$sql = "update servers set call_count='$active_call_count' where hostname = '$servername'";

//use mysql class to send query to central DB
$query_result = $db->$query($sql);

another page that pulls calls to be made and sends them to the asterisk server with the lowest call count…running on your central DB server constantly.

<?php

include 'function.php';
include 'mysql_class.php';

// query any calls that have not yet been made...we use call_time <= so that if a call was missed or a script stopped, the call will be initiated at the next opportunity.  this may require additional fields or tweaking.
$sql = "select id, number from calls where call_time <= NOW() and called='N' limit 1";
$query_result = $db->query($sql);
$call_result = $query_result->fetch();

$sql = "select hostname from servers order by call_count limit 1";
$query_result = $db->query($sql);
$server_result = $query_result->fetch();

//set the socket_host variable to the ip address of the server with the fewest active calls
$socket_host=$server_result['server_ip'];

//we need to set the extension we're going to send this call to
$extension='SIP/100';

$action = "Originate\r\n";
$action .= "Channel: $extension\r\n";
$action .= "Exten: $call_result[number]\r\n";
$action .= "Context: default\r\n";
$action .= "Priority: 1\r\n";
$action .= "Timeout: 5000\r\n";
$action .= "Callerid: Monitor\r\n";

$output = send_socket($socket_host,$socket_login, $socket_pass, $socket_port,$socket_errno, $socket_errstr, $socket_timeout, $action, '');

$sql = "update calls set called='Y' where id='$call_result[call_id]'";
$update_result = $db->query($sql);

this is pretty rough, and doesn’t include the mysql class or alot of the maintenance code, but it should give you an idea of what i was thinking. hope this helps out some, and let me know if you have any questions.


#11

Ah excellent! This is extremely helpful - esp. the code as our solution is PHP.

So you think for scalability and performance, it’s better to have the database servers PUSH the calls to the most available Asterisk boxes rather than each Asterisk box query and PULL from the database server?

Let me make sure I have this down:

1.) Database server(s) run a script (crontab) to temporarily lock and query database for any calls to be placed at the current time, every 1 or 5 minutes.

2.) If no calls, try again at next interval.

3.) If there are calls, query the list of Asterisk servers from their DB and use the stored username/password credentials to poll the Asterisk boxes’ call volume and compare.

4.) Distribute the calls accordingly.

5.) Update certain columns in the database to mark them as called

6.) Unlock the database.

This seems like it would work great - hopefully I’m not missing any small detail. If there are multiple database servers (cluster of the same database) would they each run the same script?

Lastly- I hope Im not threadjacking here… I believe our issues are related and I think fender21 would most likely benefit too :smile:


#12

yes, i think that having the database server push the calls is a much better idea, because that reduces the load on the phone boxes a little bit, and also allows for easier clustering…it also prevents two servers from trying to grab the same call at the same time.

your timeline seems to be pretty spot-on - the only thing missing is the script that runs on each asterisk box, updating the call load…that would be around #1 or so. otherwise, that’s about it.

if you had multiple database servers, you’d either want to cluster them (so they appear as one database) or split each server off into it’s own ‘campaign’ - i would not want to have to deal with keeping two databases sychronized, but if you had two separate DB’s with different numbers to call, you could do that with minimal modifications - you’d still query the * boxes for the lowest call load and send the call origination to that box.

i don’t think you’re threadjacking at all, and i hope that this is helping fender as well…he wants to do the same thing, basically, only his system will be parsing the ‘active calls’ list to see if a certain extension is active…once it’s not, the next call in line will get pushed to the server. so the code needs to be changed, but not much. in any case, this is fun for me (in a sick sort of way).

i really hope this is helping…and please keep in mind that i’m only intermediate in PHP - i’m sure my code is far from perfect, but it works for me.


#13

Thanks a lot I will begin to work on this and post my results for review! You’ve been extremely helpful!