Automatically create A2Billing rate card from voip.ms rate file

One of the most important and time consuming parts of managing A2Billing is keeping the rate cards up to date.

Here is a php script designed to download a voip.ms rate file and create a rate card in A2Billing from it. It’s designed to work with the latest version of A2Billing – version 1.9.3

If you copy and paste the script in to a file on your A2Billing server called /tmp/voip.php you can then run it by doing –

cd /tmp
php voip.php

Have a read through the comments, especially at the top of the script, as there a few settings you will need to change – especially the database connection details. Always take a backup of your A2Billing database before running an import, and make sure you check the rates after import to ensure they are as expected.

Also make sure that your base currency is set to ‘usd’ as all voip.ms rates are in USD.

<?php

# This has been designed to work with A2Billing v 1.9.3 but could be used as a starting point for other versions.
# After you have imported the rate card you must set the correct trunk on the card. This is not done by the import process.
# You then just need to add the rate card to a call plan
# by SysAdminMan.net v1.1 - 21/5/11

# This is designed to work with voip.ms rates
# !!! VOIP.MS RATES ARE IN USD. PLEASE ENSURE YOUR BASE CURRENCY IS SET TO USD OR THESE RATES WILL BE INCORRECT
# Please check your rates are as expected after importing. Use this script at your own risk.


# ********* YOU SHOULD NEED TO CHANGE/CHECK THE LINES BELOW HERE *********

# Sell rates will be this % more than the buy rates
$markup = 20;

# Database settings for your a2billing datebase
# If you're not sure what these are you should be able to find them in /etc/a2billing.conf
$db = "A2BILLING DATABASE";
$dbuser = "A2BILLING DATABASE USER";
$dbpass = "A2BILLING DATABASE PASSWORD";

# Decide whether to import 'value' or 'premium' rates. This should be set to the same as in your voip.ms user account
$plan = "value";

# ********* YOU SHOULD NEED TO CHANGE/CHECK THE LINES ABOVE HERE *********


# This is a text label for the rate card name
$supplier = "voip.ms";

$datenow = date("Y-m-d");

switch ($plan) {
    case "value":
        $whichplan = "3";
        break;
    case "premium":
        $whichplan = "4";
        break;
    default:
       die("ERROR: You should set plan to either value or premium\n\n");
}

echo "\n\n";
echo "Removing old download files\n\n";
echo exec('rm -f rates.xml');
echo exec('rm -f rates.csv');

echo "Downloading new rate file\n";
echo exec('wget http://www.voip.ms/rates/xmlapi.php -O rates.xml');
echo "\n\n";

echo "Converting XML file in to CSV file\n\n";
$filexml='rates.xml';
if (file_exists($filexml)) {
    $xml = simplexml_load_file($filexml);
$f = fopen('rates.csv', 'w');
foreach ($xml->item as $item) {
    fputcsv($f, get_object_vars($item),',','"');
}
fclose($f);
}

# Set some static variables for ratecard creation
$tariffname = "$supplier $plan +$markup% ".date("Y-m-d G:i:s");
$description = "Ratecard imported with PHP script from SysAdminMan";

# Set some static variables for ratecard import
$id = "";
$idtariffplan = "";
$dialprefix = "";
$destination = "";
$buyrate = "";
$buyrateinitblock = "";
$buyrateincrement = "";
$rateinitial = "";
$initblock = "";
$billingblock = "";
$connectcharge = 0;
$disconnectcharge = 0;
$stepchargea = 0;
$chargea = 0;
$timechargea = 0;
$billingblocka = 0;
$stepchargeb = 0;
$chargeb = 0;
$timechargeb = 0;
$billingblockb = 0;
$stepchargec = 0;
$chargec = 0;
$timechargec = 0;
$billingblockc = 0;
$startdate = date("Y-m-d h:m:s");
$stopdate = "2020-01-01 00:00:00";
$starttime = 0;
$endtime = 10079;
$id_trunk = -1;
$musiconhold = "";
$id_outbound_cidgroup = -1;

# Setup database connection
$con = mysql_connect("localhost",$dbuser,$dbpass);
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db($db, $con);

# Create the rate card
echo "Creating new rate card - ".$tariffname."\n\n";
mysql_query("INSERT INTO cc_tariffplan (tariffname,description,startingdate,expirationdate) VALUES ('$tariffname','$description','$startdate','$stopdate')")
or die(mysql_error());

# Extract the id of the tariff plan we just created
echo "Retreiving ID of newly created rate card\n\n";
$query = mysql_query("SELECT id FROM cc_tariffplan where tariffname = '$tariffname'")
or die(mysql_error());
$row=mysql_fetch_assoc($query);
$idtariffplan = $row['id'];

# Read CSV file
echo "Reading downloaded rate file\n\n";
$file_handle = fopen("rates.csv", "r");

echo "Creating dial prefixes and rates. Please wait ...\n\n";
while (!feof($file_handle) ) {
$line_of_text = fgetcsv($file_handle, 1024, ',');

# Set the required variables from the import file
$dialprefix = $line_of_text[2];
$destination = $line_of_text[1];
$buyrate = $line_of_text[$whichplan];
$initblock = $line_of_text[5];
$billingblock = $line_of_text[5];
$buyrateinitblock = $line_of_text[5];
$buyrateincrement = $line_of_text[5];
$rateinitial = ($buyrate/100*$markup)+$buyrate;

# Check to make sure the rate if greater than zero.
if ($buyrate>0) {

# Create prefix and description in prefix table
mysql_query("INSERT IGNORE INTO cc_prefix (prefix,destination) VALUES ('$dialprefix','$destination')")
or die(mysql_error());

# Create rate in rate card
mysql_query("INSERT INTO cc_ratecard (id,idtariffplan,dialprefix,destination,buyrate,buyrateinitblock,buyrateincrement,rateinitial,initblock,billingblock,connectcharge,disconnectcharge,stepchargea,chargea,timechargea,billingblocka,stepchargeb,chargeb,timechargeb,billingblockb,stepchargec,chargec,timechargec,billingblockc,startdate,stopdate,starttime,endtime,id_trunk,musiconhold,id_outbound_cidgroup)
VALUES ('$id','$idtariffplan','$dialprefix','$dialprefix','$buyrate','$buyrateinitblock','$buyrateincrement','$rateinitial','$initblock','$billingblock','$connectcharge','$disconnectcharge','$stepchargea','$chargea','$timechargea','$billingblocka','$stepchargeb','$chargeb','$timechargeb','$billingblockb','$stepchargec','$chargec','$timechargec','$billingblockc','$startdate','$stopdate','$starttime','$endtime','$id_trunk','$musiconhold','$id_outbound_cidgroup')")
or die(mysql_error());

}
}

fclose($file_handle);
mysql_close($con);

?>

1 thought on “Automatically create A2Billing rate card from voip.ms rate file

Comments are closed.