We’ve had a bit of a tidy up and I need to delete about 400 DID numbers from FreePBX as we are dropping the numbers with our supplier as they are no longer used.
I am comfortable using SQL so would there be any repercussions from dropping these records directly from the asterisk.incoming table and then simply issuing fwconsole reload afterwards?
400 numbers is a rather lot to do by hand! I thought of using the Bulk handler although I don’t think that would help me!
Based on @jfinstrom tip (thank you very much for that), here is a basic PHP script to delete all the DIDs from the inbound routes. If you need to filter them, then you will need to edit the MySQL query. If you have different database settings, change them as well. I tried this script on one of my backup machines and it deleted hundreds of dids in a second. Don’t forget to reload the web page to test if it works.
<?php
include '/etc/freepbx.conf';
//Prepare database connection
$servername = 'localhost';
$username = 'root';
$password = '';
$dbname = 'asterisk';
//Connect to the database
$conn = new mysqli($servername, $username, $password, $dbname);
//Check the connection
if ($conn ->connector_error) {
die("Connection failed: " . $conn->connector_error);
}
//Create Array function
function ResultToArray($result) {
$rows = array();
while ($row = $result->fetch_assoc()) {
$rows[] = $row;
}
return $rows;
}
//Query the DIDs
//$query = "select cidnum,extension from incoming where extension like '0%'";
$query = "select cidnum,extension from incoming";
$result = $conn->query($query);
$rows = ResultToArray($result);
$result->free();
//Prepare the DIDs deletion
$f = FreePBX::Create();
foreach($rows as $item){
$f->Core->delDID($item['extension'], $item['cidnum']);
echo "The DID " . $item['extension'] . " with CID " . $item['cidnum'] . " has been deleted\r\n";
}
echo "\r\nDone!\r\n";
@danielf there are built in methods to handle queries to the MySQL database. Do not code in DB connections.
// Load FreePBX bootstrap environment
require_once('/etc/freepbx.conf');
// Initialize a database connection
global $db;
// This pulls every number in contact maanger that is part of the group specified by $contact_manager_group
$sql = "SELECT cen.number, cge.displayname, cen.type, cen.E164, 0 AS 'sortorder' FROM contactmanager_group_entries AS cge LEFT JOIN contactmanager_entry_numbers AS cen ON cen.entryid = cge.id WHERE cge.groupid = (SELECT cg.id FROM contactmanager_groups AS cg WHERE cg.name = '$contact_manager_group') ORDER BY cge.displayname, cen.number;";
// Execute the SQL statement
$res = $db->prepare($sql);
$res->execute();
// Check that something is returned
if (DB::IsError($res)) {
// Potentially clean this up so that it outputs pretty if not valid
error_log( "There was an error attempting to query contactmanager<br>($sql)<br>\n" . $res->getMessage() . "\n<br>\n");
} else {
$contacts = $res->fetchAll(PDO::FETCH_ASSOC);
foreach ($contacts as $i => $contact){
etc...........
You did not notice that you can change the SQL query of the DID selection in my script:
$query = "select cidnum,extension from incoming where extension like '0%'";
You do not have to delete all your DIDs if you do not need to. Although, sometimes it is quicker to delete all the DIDs from the database and then add the necessary DIDs through the bulk handler module.