Support A2Billing :

provided by Star2Billing S.L.

Support A2Billing :
It is currently Fri Mar 29, 2024 5:20 am
VoIP Billing solution


All times are UTC




Post new topic Reply to topic  [ 3 posts ] 
Author Message
 Post subject: export rates script
PostPosted: Thu Nov 28, 2013 2:38 pm 
Offline

Joined: Tue Jul 20, 2010 3:42 pm
Posts: 30
Location: Schaumburg , IL
My intention was to create a php script that I can use to export rates from a2b database weekly or so to be available for download.
This is my creation without temporary table.

//pull ratecards from call plan
$ratecard = '';
$res = mysql_query("select idtariffplan from cc_tariffgroup_plan where idtariffgroup = '$plan'");
while ($ro = mysql_fetch_assoc($res)) {
$ratecard[] = (string)$ro["idtariffplan"];
}
$ratecard = implode(',', $ratecard);

//open file
$fp = fopen('/tmp/rates.csv', 'w');

//pull rates
$result = mysql_query("select DISTINCT(cc_ratecard.dialprefix), cc_prefix.destination, cc_ratecard.rateinitial from cc_ratecard left join cc_prefix on cc_ratecard.dialprefix=cc_prefix.prefix where cc_ratecard.idtariffplan in ($ratecard) GROUP BY cc_ratecard.dialprefix order by cc_ratecard.dialprefix");
while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
fputcsv($fp, $row);
}
fclose($fp);

This would make LCD, not an LCR

Any suggestions on improving the query?


Top
 Profile  
 
 Post subject: Re: export rates script
PostPosted: Mon Dec 02, 2013 9:30 pm 
Offline

Joined: Mon Mar 02, 2009 8:56 pm
Posts: 271
Hi Marcin,

Here's mine -

Code:
<?php

# Call plan ID to extract
$callplanid = "1";

# Database settings for your a2billing datebase
# These are automatically read from /etc/a2billing.conf
$config = parse_ini_file('/etc/a2billing.conf');
$db = "$config[dbname]";
$dbuser = "$config[user]";
$dbpass = "$config[password]";

$conn = mysql_connect("localhost", $dbuser, $dbpass) or die(mysql_error());
mysql_select_db($db, $conn) or die(mysql_error($conn));

$query = sprintf("SELECT SQL_CALC_FOUND_ROWS destination, dialprefix, MIN(rateinitial) as rate$, initblock as increment, connectcharge FROM cc_callplan_lcr WHERE tariffgroup_id= $callplanid GROUP BY dialprefix ORDER BY dialprefix ASC;");
$result = mysql_query($query, $conn) or die(mysql_error($conn));

/*
* send response headers to the browser
* following headers instruct the browser to treat the data as a csv file called export.csv
*/

header('Content-Type: text/csv');
header('Content-Disposition: attachment;filename=export.csv');

/*
* output header row (if atleast one row exists)
*/

$row = mysql_fetch_assoc($result);
if ($row) {
    echocsv(array_keys($row));
}

/*
* output data rows (if atleast one row exists)
*/

while ($row) {
    echocsv($row);
    $row = mysql_fetch_assoc($result);
}

/*
* echo the input array as csv data maintaining consistency with most CSV implementations
* - uses double-quotes as enclosure when necessary
* - uses double double-quotes to escape double-quotes
* - uses CRLF as a line separator
*/

function echocsv($fields)
{
    $separator = '';
    foreach ($fields as $field) {
        if (preg_match('/\\r|\\n|,|"/', $field)) {
            $field = '"' . str_replace('"', '""', $field) . '"';
        }
        echo $separator . $field;
        $separator = ',';
    }
    echo "\r\n";
}
?>


Matt


Top
 Profile  
 
 Post subject: Re: export rates script
PostPosted: Mon Dec 02, 2013 9:35 pm 
Offline

Joined: Mon Mar 02, 2009 8:56 pm
Posts: 271
I should say ... I didn't write the CSV parts but can't remember where I got it, it was a while ago. Thanks to whoever did!


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 3 posts ] 
VoIP Billing solution


All times are UTC


Who is online

Users browsing this forum: No registered users and 8 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  
cron
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group