Get CDR records and insert in a custom DB table

FreePBX Development
1

I want to capture duration, billsec, disposition & calldate and save them in my own custom database table for every outbound call.
The problem is in my update_outbound.php despite having it sleep(5), before querying the cdr table in asteriskcdrdb based on the $(UNIQUEID} value from the dial plan, the querying comes up empty.
This is part of my php script;

$servername = “asterisk-IP-address”;
$username = “name”;
$password = “pass”;
$dbname = “asteriskcdrdb”;

try {
$con = new mysqli($servername, $username, $password, $dbname);
sleep(5);
$sql1 = “SELECT duration, billsec, calldate, disposition FROM cdr WHERE uniqueid=’$unqID’;”;
$result = $con->query($sql1);
$data = $result->fetch_assoc();
if($data){
file_put_contents("/home/mandela/debug.txt",“yes DATA”);
}else{
file_put_contents("/home/mandela/debug.txt",“NO DATA”);
}
$duration = $data[‘duration’];
$billsec = $data[‘billsec’];
$calldate = strtotime($data[‘calldate’]);
$dispo = $data[‘disposition’];
} catch (Exception $e) {
echo "Connection failed: " . $e->getMessage();
}

I have this in my extensions_custom_conf:

[from-internal-custom]
include => outbound

[outbound]
;exten => 701,1,NoOp(Starting outbound call!)
exten => _254XXXXXXXXX,1,NoOp(Starting outbound call!)
same => n,NoOp(Customer number is ${EXTEN})
same => n,NoOp(Calling Extension number is ${CALLERID(num)})
same => n,DumpChan
same => n,NoOp(UNIQUE ID IS ${UNIQUEID})
same => n,AGI(fmscurl.php,${EXTEN},${CALLERID(num)})
same => n,Set(GLOBAL(lastID)=${lastID})
same => n,Set(GLOBAL(agentNo)=${CALLERID(num)})
same => n,Set(GLOBAL(custNo)=${EXTEN})
same => n,NoOp(THIS IS THE LAST ID ${lastID})
same => n,Dial(SIP/${EXTEN},10,U(record-on-answer))
same => n,GotoIf($["${DIALSTATUS}"=“NOANSWER”]?noanswer)
same => n,GotoIf($["${DIALSTATUS}"=“BUSY”]?busy)
same => n(noanswer),NoOp(NO ANSWER REACHED)
same => n,Set(CHANNEL(hangup_handler_push)=run-on-noAnswer-hangup,s,1)
same => n,Hangup
same => n(busy),NoOp(BUSY REACHED)
same => n,Set(CHANNEL(hangup_handler_push)=run-on-Busy-hangup,s,1)
same => n,MacroExit

[record-on-answer]
exten => s,1,Noop(Entering call record context)
same => n,Monitor(wav,Call-Record,m)
same => n,Set(CHANNEL(hangup_handler_push)=run-on-outbound-hangup,s,1)
same => n,MacroExit

[run-on-outbound-hangup]
exten => s,1,Noop(We are here, after outbound hangup)
same => n,AGI(update_outbound.php,${lastID},${agentNo},${custNo},${UNIQUEID})
same => n,Return

[run-on-noAnswer-hangup]
exten => s,1,AGI(update_outbound.php,${lastID},${agentNo},${custNo},${UNIQUEID})
same => n,Return

[run-on-Busy-hangup]
exten => s,1,AGI(update_outbound.php,${lastID},${agentNo},${custNo},${UNIQUEID})
same => n,Return

2

Your basic problem is that the sleep is synchronous with the dialplan, and the CDR is not written until the dialplan ends.

However, you also need to be aware that uniqueid is for the channel, and is not a candidate key for the CDR; more than one CDR can have the same uniqueid in some scenarios.

You could force out the CDR with ResetCDR.

Also note that hangup handlers are not intended to be long running.

3

I would suggest just designing your own custom ‘backend’
http://www.asteriskdocs.org/en/3rd_Edition/asterisk-book-html-chunk/database_storing-cdr.html

4

Thanks for your feedback, I have searched and seen a previous answer you gave on setting up a custom back-end and so far this is what i have done.
In odbc.ini i have added the following connection to my cdr database;

[MySQL-cdrdb]
Description=MySQL connection to ‘cdr’ database
driver=MySQL
server=localhost
database=cdr
Port=3306
Socket=/var/lib/mysql/mysql.sock
option=3
Charset=utf8

Then in my res_odbc_custom.conf I have this context so far though i don’t wuite understand whether so far I am doing the right thing; how do i insert into a table in this db? Kindly clarify?

[cdrdb]
endbeforehexten=yes
unanswered = yes
enabled=>yes
dsn=>MySQL-cdrdb
pre-connect=>yes
max_connections=>5
username=>root
password=>
database=>cdr

5

just ‘map’ the cdr ones to ypur ones on a one to one basis, use triggers and stored procedures to further massage the records

6

@dicko kindly if you don’t mind clarify on how to “map” or point me to some where I can read further on this, thanks.

7

http://www.asteriskdocs.org/en/3rd_Edition/asterisk-book-html-chunk/asterisk-SysAdmin-SECT-1.html#default_cdr_fields

Google is occasionally useful also :wink:

8

@dicko on it thanks.

9

This topic was automatically closed 31 days after the last reply. New replies are no longer allowed.