A2Billing – Monthly report detailing call spend

Invoicing in A2Billing is not the easiest thing in the world to manage and can be overly complicated if all you want is a figure for the monthly call spend for each A2Billing customer.

I got sent the following script by a business associate and thought you might find it useful.

It e-mails a report (that could be scheduled to run monthly) detailing each A2Billing customer’s call spend for the previous month. These figures could then be used in your existing invoicing system.

As always you use at your own risk and I would strongly urge you to check that the figures it produces are as expected. You will need to change the database details to match your system. You can probably find these in /etc/a2billing.conf.

Check out Lansalot Ltd @ www.lansalot.co.uk

<?php
// Script to email you monthly the bill summary from A2Billing
// Script this with Cron to run on the 1st of each month, to email you
// the previous calendar month's bill summary.
//
// This script is useful if you make your own invoices outside of A2B
// Remember you still need to record when the customer has paid by use of
// a refill within A2B - otherwise they will at some point exceed their
// credit limit.
//
//
// ============ LICENCE =============
// You are free to copy and re-use this software, so long as you do not make commercial gain from it
// If you do, please contact us at www.lansalot.co.uk to discuss licensing the script for your own purpose
//
// If you use this script, please:
//  - like us on facebook at http://www.facebook.com/lansalotlimited
//  - follow us on twitter at http://twitter.com/Lansalot_Ltd
//  - Linkin to us at http://www.linkedin.com/company/lansalot-limited?trk=fc_badge
//  THANK YOU :)


// This may get altered later to format in a table - not sure of the requirement yet

// Enter Details for your MySQL connection
$username = "root";
$password = "pass";
$hostname = "localhost";
$dbasename = "mya2billing";
//Note: dbname may not work as the SQL query doesn't pick it up

// Details for your email
$Systemname = "MyServer";
$mailto = "[email protected]";
$subject = "Monthly bill summary for {$Systemname}";
$from = "{$Systemname} Billing <[email protected]{$Systemname}.pbx>";


// ============= Main Script ===================


$dbhandle = mysql_connect($hostname, $username, $password)
or die("Unable to connect to MySQL");

$selected = mysql_select_db($dbasename,$dbhandle)
or die("Could not select database");

$result = mysql_query("SELECT firstname,lastname,card_id, round(sum(sessionbill),2) as Monthbill FROM
`mya2billing`.`cc_call` a inner join `mya2billing`.`cc_card` on card_id = `cc_card`.id where a.stoptime between
DATE_FORMAT(LAST_DAY(DATE_SUB(NOW(), INTERVAL 1 MONTH)),'%Y-%m-01 00:00:00') AND DATE_FORMAT(LAST_DAY(DATE_SUB(NOW(),
INTERVAL 1 MONTH)),'%Y-%m-%d 23:59:59') group by card_id;"
);

//Write HTML headers for the email
$outmessage = "<html><head><title>Call Summary</title></head><body><p>Summary of calls made on {$Systemname} system for
"
.date("F",strtotime("-1 month"))."<br>";

while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
    $outmessage = $outmessage. "<b>Name :{$row['firstname']} {$row['lastname']} </b><br>" .
         "Calls : {$row['Monthbill']} <br>" .
         "<br>";
}
//Write the end of the HTML for the email
$outmessage .= "</p>Copyright 2011<a href="http://www.lansalot.co.uk"> Lansalot Ltd</a></body></html>";

echo $outmessage;

  //send email
$headers  = 'MIME-Version: 1.0' . "\r\n";
$headers .= 'Content-type: text/html; charset=iso-8859-1' . "\r\n";
$headers .= 'From:'.$from . "\r\n";

  mail($mailto, $subject,  $outmessage, $headers);
  echo "<br>Thank you for using our mail form";
  echo "<br>".$headers

?>

6 thoughts on “A2Billing – Monthly report detailing call spend

  1. Jon

    Glauco – not sure what you would want to do? Have the system read the summary out to you? It’s not really that suitable, it’s designed as a management report to quickly give you a list of all customers and their bill for the month.

    voiptalk.gr – what sim are you looking for?

Comments are closed.