Fetch data from sql database to make call


#1

There is a database present on a server different from asterisk . The database contains information of people their name and contact number. I want to build a system that extract info from that server and make call to them. I believe curl can be used to interact and make request to another server but how should I do it with asterisk…the work is urgent…Please help!


#3

If I understand the request correctly*…

When your script retrieves a contact number from the database, the script can create a ‘call file’ to originate the call. You can pass variables from the database to Asterisk in that file using ‘setvar.’

Read https://wiki.asterisk.org/wiki/display/AST/Asterisk+Call+Files for details.

*) Better questions yield better answers.


#4

I did not read correctly your request, if is call numbers from a database, I do this using PHP for DB connection and AMI Originate action


#5

Basically what I require is that to fetch contacts from database on a different server and then make Interactive voice response call to those contacts. IVR part is ok but how should I fetch them and how to execute the call after fetching.


#6

I suspect you don’t have a lot of experience in this area, so my
suggestions are tailored to help you learn. I’ll apologize in advance
if I’ve mis-characterized the situation.

You can ask Asterisk to originate a call using either a call file or
by using AMI. Using a call file is easier while AMI is more flexible,
robust, and efficient.

Whichever approach you decide to use in production, using a call file
is a great way to learn.

Start by writing a script that would create a ‘call file’ to originate
a call to the number passed on the command line. For example:

./dial-number.sh +1-800-555-1212

When you have this working, write a script that reads the contacts
from the database and then feed the numbers to the dialing
script. Reading the contacts is database specific and you haven’t
specified what database you use. If you use MySQL, your script would
look something like:

MYSQL='mysql --database=... --host=... --password=... --user=...'
while   read -a REPLY
	do
	NUMBER=${REPLY[0]}
	./dial-number.sh ${NUMBER}
	done < <(${MYSQL} --execute="select number from clients;")

Note that I used the ‘array’ form of ‘read’ so that you could enhance
it to do something like:

while   read -a REPLY
	do
	BALANCE=${REPLY[0]}
	NUMBER=${REPLY[1]}
	./dial-number.sh ${NUMBER} ${BALANCE}
	done < <(${MYSQL} --execute="select balance, number from clients;")

If you wanted to tell each client how much money they owed you.

Without knowing the specifics of your task, it’s difficult to go much
further – and you’re approaching the threshold of when I think you should
consider contracting someone to assist you or to implement the task
for you.


#7

Thank You @sedwards … Yes I am using MySQL but I wanted to ask won’t I be required to make a curl request or Ajax request to get the data from another server. For instance the data on my server consist of 3 entries Name, contact number and then dob and I want to extract all three. Another thing I want to keep checking the database after every say 10 seconds for new entries and then place a call to them.
Waiting for your reply


#8

How you access your data is your (or your DBA’s) choice.

If your data is exposed via HTTP (port 80, 443), then use ‘curl.’ If your data is exposed via MySQL (port 3306), then use ‘mysql.’ Most of my projects use MySQL. (Note the ‘host’ parameter in the example script.)

Saying you want to call new entries implies updating the row with the outcome of the call so you know whether the call was answered so you can exclude it on your next pass.


#9

If I have such scenario I would use a php or ruby script on my server, and connect to AMI, and run a loop to get all numbers and make calls using AMI commands.
If number success will update records in same script.
I may use cron so process keep running. I adde two more fields in table about is_called and UnderCall, so I can know if it is being called and was it called?. If you want you can go more further by adding exact status of call number or tries etc. I suggest to make sure proper time out, space between calls should be setup. Following is just sample you can modify and use it, actually it is calling another Asterisk and service provider to test number. I also put a huge space between calls. But you can remove as you are calling and sending proper voice. In my case I just want to give ping call.

<?php 

$mysqli = new mysqli($servername, $username, $password, $dbname);
$strSQL = "Select number from contactnumbers where is_called = 1";
$Result = $mysqli->query($strSQL);

while($row = $Result->fetch_assoc()){	
		$success = TestNumber($row['number']);
if ($success == true){
$stmt = $mysqli->prepare("UPDATE contactnumber set is_called=1 where number = ?");
  $stmt->bind_param("s",$number);
  $stmt->execute();

}
}


function TestNumber($number){
  global $mysqli;
  $CallerID = "__YOURID__"; // id which want to show
  $mgrUSERNAME='__YOURUSER__';
  $mgrSECRET='__YOURSECRET__';
  $server_ip='__ASTERISKSERVER_IP__';
  $stmt = $mysqli->prepare("UPDATE contactnumber set UnderCall=1, is_called=0 where number = ?");
  $stmt->bind_param("s",$number);
  $stmt->execute();

  $socket = fsockopen($server_ip,"5060", $errno, $errstr, 10);
         if (!$socket) {
            echo "$errstr ($errno)\n";
         } else {
            fputs($socket, "Action: Login\r\n");
            fputs($socket, "Username: $mgrUSERNAME\r\n");
            fputs($socket, "Secret: $mgrSECRET\r\n\r\n");
            fputs($socket, "Action: Originate\r\n");
            stream_set_timeout($socket, 10);
            fputs($socket, "Exten: \r\n");
            fputs($socket, "Context: default\r\n");
            fputs($socket, "Channel: IAX2/__USERNAME__:__PASSWORD__\@__PROVIDERDOMAIN__/$number\r\n"); // if want to call your service provider
            fputs($socket, "Priority: 1\r\n");
            fputs($socket, "Timeout: 20000\r\n");
            fputs($socket, "Callerid: $CallerID\r\n\r\n");
            fputs($socket, "Action: Logoff\r\n\r\n");
            usleep(15000000); // waiting time between each call in microseconds

         }


  // Logging details in test log
return true
}