PHP / MySQL Billing System on Asterisk

Question

LRNPHP 0 Junior Poster in Training

12 Years Ago

Hi There,

I'm busy developing a PHP / MySQL billing system for our company but can't seem to get the following working for me.
I need to have 1 page that will display the extensions billing period / month for local, national and international calls and it needs to be displayed on 1 line per extension.

Really hope someone can help in resolving this.

Kind Regards

mysql php

2 Contributors
32 Replies
502 Views
5 Days Discussion Span
Latest Post 12 Years Ago Latest Post by pritaeas

pritaeas 2,194 ¯\_(ツ)_/¯

12 Years Ago

Can you show how your data looks, and an example of the required output?

pritaeas 2,194 ¯\_(ツ)_/¯

12 Years Ago

We need to know your table structure (preferably with some sample data) too.

pritaeas 2,194 ¯\_(ツ)_/¯

12 Years Ago

Can you show how you are displaying the results (your code) ?

pritaeas 2,194 ¯\_(ツ)_/¯

12 Years Ago

You should put each variable in it's own <td>, then it will align automatically.

pritaeas 2,194 ¯\_(ツ)_/¯

12 Years Ago

for ($a = 0; $a < $sqlSumRowExt1; $a++) {
    $LocalExt1 = mysql_result($sqlSumResultsExt1, $a, 'Ext');
    $LocalTarrif1 = mysql_result($sqlSumResultsExt1, $a, 'Tarrif');
    $Summ = mysql_result($sqlSumResultsExt1, $a, 'Summery');
    $lcr_location1 = mysql_result($sqlSumResultsExt1, $a, 'lcr_data.lcr_location');

    echo '<tr>';
    echo "<td>$LocalExt1</td>";
    echo "<td>$lcr_location1</td>";
    echo "<td>" . round($LocalTarrif1, 2) . "</td>";
    echo '</tr>';
}

Edited 12 Years Ago by pritaeas

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

LRNPHP 0 Junior Poster in Training · Answer 1 · 2012-08-08T09:06:07+00:00

Hi Pritaeas,

Attached is the way I would like it to be.

Regards

LRNPHP 0 Junior Poster in Training · Answer 2 · 2012-08-09T09:26:00+00:00

Hi Pritaeas,

I attached a screenshot of the lcr_data table. THis table includes all my prefixes. I would like to know if it's possible to extract data directly from MySQL in the following manner.

I don't know if it's possible to do this in MySQL and then just output the variables?

This is my MySQL code sofar:

select SUBSTRING(cdr.calldate,1,10) as Calldate, cdr.src, cdr.dst, SUBSTRING(cdr.dst,1,3), lcr_data.lcr_prefix, SUM(cdr.billsec) as Billsec, lcr_data.lcr_peakrate AS Rate, COUNT(src) as ExtCount, SUM(cdr.billsec*lcr_data.lcr_peakrate/60) as Tarrif, lcr_data.lcr_location from cdr, lcr_data where SUBSTRING(cdr.dst,1,3) = lcr_data.lcr_prefix GROUP by src limit 5

Regards

LRNPHP 0 Junior Poster in Training · Answer 3 · 2012-08-09T10:07:37+00:00

LRNPHP 0 Junior Poster in Training

12 Years Ago

Hi Pritaeas,

The table structures.

Thanks

LRNPHP 0 Junior Poster in Training · Answer 4 · 2012-08-09T15:01:43+00:00

Hi Pritaeas,

This is what I get, all I would like to do now is to allign it properly.

Extension | Cell | National | Local | International

Thanks

LRNPHP 0 Junior Poster in Training · Answer 5 · 2012-08-09T16:54:27+00:00

Hi Pritaeas,

Here is the code that I used to get the data per extension.

Thanks

<?php
session_start();
include "include/config.php";
?>
<table width="100%" border="0">

<?php
#Extension Numbers
$sqlSumExt1="select calldate, src, SUBSTRING(cdr.src,7,10) as Ext, cdr.dst, SUBSTRING(cdr.dst,1,3), COUNT(lcr_data.lcr_prefix) as Summery,lcr_data.lcr_prefix, SUM(cdr.billsec) as Billsec, lcr_data.lcr_peakrate AS Rate, SUM(cdr.billsec*lcr_data.lcr_peakrate/60) as Tarrif, lcr_data.lcr_location, cdr.lastdata from cdr, lcr_data where calldate > '2011-08-01' and calldate < '2011-09-31' and src like '01154483%' and lastdata like 'SIP/ECNTEB/%' and SUBSTRING(cdr.dst,1,3) = lcr_data.lcr_prefix GROUP BY src, lcr_location";
$sqlSumResultsExt1=mysql_query($sqlSumExt1)or die(mysql_error());
$sqlSumRowExt1=mysql_num_rows($sqlSumResultsExt1)or die(mysql_error());

for($a=0; $a< $sqlSumRowExt1; $a++){
    $LocalExtCount1=mysql_result($sqlSumResultsExt1, $a, 'Ext');
    $LocalTarrif1=mysql_result($sqlSumResultsExt1, $a, 'Tarrif');
    $Summ=mysql_result($sqlSumResultsExt1, $a, 'Summery');
    $lcr_location1=mysql_result($sqlSumResultsExt1, $a, 'lcr_data.lcr_location');
    $location[]=mysql_result($sqlSumResultsExt1, $a, 'lcr_data.lcr_location');

######################################################################################################################################################################################################################################################################################################################################################################################################


?>  
<?php
    #echo $LocalExtCount1;
?>
<tr>
    <td><? echo " Extension : " .$LocalExtCount1. " ............. ". $lcr_location1 ." Cost R ".round($LocalTarrif1,2)." : ". $Summ?></td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>



  </tr>
<?php
    }
?>

</table>

LRNPHP 0 Junior Poster in Training · Answer 6 · 2012-08-09T18:57:15+00:00

Hi Pritaeas,

I have the following code.

for($a=0; $a< $sqlSumRowExt1; $a++){
    $LocalExt1=mysql_result($sqlSumResultsExt1, $a, 'Ext');
    $LocalTarrif1=mysql_result($sqlSumResultsExt1, $a, 'Tarrif');
    $Summ=mysql_result($sqlSumResultsExt1, $a, 'Summery');
    $lcr_location1=mysql_result($sqlSumResultsExt1, $a, 'lcr_data.lcr_location');




?>  
<?php
    #echo $LocalExt1;
?>

My extensions is in the $LocalExt1 and the cost involed is LocalTarrif1. If I put the variable $LocalExt1 and LocalTarrif1 etc in between each <td> </td> I will get the same data over and over.

I would like to have all data per Extension on 1 line.

Extension | Cell | National | Local | International

Regards

LRNPHP 0 Junior Poster in Training · Answer 7 · 2012-08-10T06:13:43+00:00

Hi Pritaeas,

It's displaying exactly the same as in example 1.

Extension | Cell | National | Local | International
8303..........80.....45............50.......3
8304..........70.....35............10.......1
8310..........50.....25............20.......5
8311..........40.....15............30.......7

and not
Extension
8303........80....Cell
8303........35....National
8303........50....Local
8303........73....International
8304........70....Cell
8304........35....National
8304........10....Local
8304........17....International
8310........50....Cell
8310........25....National
8310........20....Local
8310........25....International
8311........40....Cell
8311........15....National
8311........30....Local
8311........17....International

At this stage I'm getting it in the 2nd example but it takes up allot of space. I would like to get them group by extension as in example 1.

Hope this makes more sense.
Thanks for your assistance so far.

Regards

pritaeas 2,194 ¯\_(ツ)_/¯ Moderator Featured Poster · Answer 8 · 2012-08-10T08:14:40+00:00

If you want to do it in PHP, then you can start a new row every four records.

LRNPHP 0 Junior Poster in Training · Answer 9 · 2012-08-10T08:38:40+00:00

Not all the extension have 4 records, it's dependend on if the extension dialed cell phones, local, national and international calls. If the extension only did cell calls he will have only 1 row etc.

Don't know if what I want is even possible.

Regards

pritaeas 2,194 ¯\_(ツ)_/¯ Moderator Featured Poster · Answer 10 · 2012-08-10T08:55:20+00:00

If they are ordered by extension, and by call type (cell, national, local, international) then you can easily insert an empty cell. I'd execute the query and then build an array first, and the table after that. Something like this:

$values = array ();
for ($a = 0; $a < $sqlSumRowExt1; $a++) {
    $LocalExt1 = mysql_result($sqlSumResultsExt1, $a, 'Ext');
    $LocalTarrif1 = mysql_result($sqlSumResultsExt1, $a, 'Tarrif');
    $Summ = mysql_result($sqlSumResultsExt1, $a, 'Summery');
    $lcr_location1 = mysql_result($sqlSumResultsExt1, $a, 'lcr_data.lcr_location');

    $values[$LocalExt1][$lcr_location1] = round($LocalTarrif1, 2);
}

$types = array ('Cell', 'National', 'Local', 'International');
foreach ($values as $extension => $value) {
    echo '<tr>';
    echo "<td>$extension</td>";
    foreach ($types as $type) {
        echo '<td>';
        if (isset($values[$extension][$type])) {
            echo $values[$extension][$type];
        }
        else {
            echo '&nbsp;';
        }
        echo '</td>';
    }
    echo '</tr>';
}

LRNPHP 0 Junior Poster in Training · Answer 11 · 2012-08-10T09:50:45+00:00

Hi Pritaeas,

That works 100%, how will I get the count for local, national, cell and international calls. Would like to have the count for all local, national, cell and international calls before I display the cost

Thanks for your assistance.

pritaeas 2,194 ¯\_(ツ)_/¯ Moderator Featured Poster · Answer 12 · 2012-08-10T10:20:39+00:00

Between line 14 and 15 add:

$sum = array_sum($values[$extension]);
echo "<td>$sum</td>";

LRNPHP 0 Junior Poster in Training · Answer 13 · 2012-08-10T11:15:56+00:00

$sum = array_sum($values[$extension]);
echo "<td>$sum</td>";

Hi Pritaeas,

This gives me the sum for the cost, I would like to have the total count (Number) for local calls, national calls, etc.

extension | local count | local cost | national count | national cost
8303..........10...............50.............5.....................30

The costing is working 100%, just want to get the ammount of local calls before local cost etc.

Once again thanks for your assistance.

Regards

pritaeas 2,194 ¯\_(ツ)_/¯ Moderator Featured Poster · Answer 14 · 2012-08-10T11:23:49+00:00

pritaeas 2,194 ¯\_(ツ)_/¯

12 Years Ago

What column in your query is it in? Just use that one.

LRNPHP 0 Junior Poster in Training · Answer 15 · 2012-08-10T11:30:51+00:00

Hi Pritaeas,

The values are located in

$Summ = mysql_result($sqlSumResultsExt1, $a, 'Summery');

How would I implement it from this variable to display the number of local calls, national calls etc?

Thanks

pritaeas 2,194 ¯\_(ツ)_/¯ Moderator Featured Poster · Answer 16 · 2012-08-10T12:14:18+00:00

pritaeas 2,194 ¯\_(ツ)_/¯

12 Years Ago

Just like the others.

LRNPHP 0 Junior Poster in Training · Answer 17 · 2012-08-10T12:16:36+00:00

LRNPHP 0 Junior Poster in Training

12 Years Ago

Ok

LRNPHP 0 Junior Poster in Training · Answer 18 · 2012-08-10T18:08:03+00:00

Hi Pritaeas,

I'm stuck again and don't know what to do anymore. Hope you will assist me again. I can't seem to get the number off calls for Local, National etc in line with the data that's being displayed.

Thanks

pritaeas 2,194 ¯\_(ツ)_/¯ Moderator Featured Poster · Answer 19 · 2012-08-10T18:51:45+00:00

pritaeas 2,194 ¯\_(ツ)_/¯

12 Years Ago

Show your new code (the relevant part).

LRNPHP 0 Junior Poster in Training · Answer 20 · 2012-08-10T18:55:40+00:00

Hi Pritaeas,

I'm so lost with this at ths stage. Sorry for being an idiot..

$values = array ();
for ($a = 0; $a < $sqlSumRowExt1; $a++) {
    $LocalExt1 = mysql_result($sqlSumResultsExt1, $a, 'Ext');
    $LocalTarrif1 = mysql_result($sqlSumResultsExt1, $a, 'Tarrif');
    $Summ = mysql_result($sqlSumResultsExt1, $a, 'Summary');
    $lcr_location1 = mysql_result($sqlSumResultsExt1, $a, 'lcr_data.lcr_location');
    $values[$LocalExt1][$lcr_location1] = round($LocalTarrif1, 2);
    $TotalSum[]=mysql_result($sqlSumResultsExt1, $a, 'Summary');

}   

foreach($TotalSum as $Total){
    echo $Total;    
}

$types = array ('Local', 'National', 'Cell', 'IntNational', '086National');
foreach ($values as $extension => $value) {
    echo '<tr>';
    echo "<td>$extension</td>";

    foreach ($types as $type) {
        echo '<td>';
        if (isset($values[$extension][$type])) {
            echo $values[$extension][$type];
        }
        else {
            echo '&nbsp;';
        }
        echo '</td>';
    }

    echo '</tr>';
}

Regards

pritaeas 2,194 ¯\_(ツ)_/¯ Moderator Featured Poster · Answer 21 · 2012-08-10T21:30:02+00:00

I can't seem to get the number off calls for Local, National

Which variables contain those values?

LRNPHP 0 Junior Poster in Training · Answer 22 · 2012-08-11T10:31:01+00:00

Hi Pritaeas,

This Variable contains the code $TotalSum[]=mysql_result($sqlSumResultsExt1, $a, 'Summary');

Thanks

pritaeas 2,194 ¯\_(ツ)_/¯ Moderator Featured Poster · Answer 23 · 2012-08-11T11:03:41+00:00

pritaeas 2,194 ¯\_(ツ)_/¯

12 Years Ago

Add:

echo "<td>$Total</td>";

on line 20.

LRNPHP 0 Junior Poster in Training · Answer 24 · 2012-08-11T14:45:25+00:00

Hi Pritaeas,

Nope it only displays 1 for every row.

Regards