0

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

2
Contributors
32
Replies
39
Views
5 Years
Discussion Span
Last Post by pritaeas
Featured Replies
  • 1

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

  • 1

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

  • 1

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

  • 1

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

  • 1

    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>'; } Read More

0

Hi Pritaeas,

Attached is the way I would like it to be.

Regards

Attachments Capture.GIF 5 KB
0

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.

Ext.....| Local Total | Local Cost | National Total | National Cost | Mobile Total
8300...30.................R 300.00....10.....................R 50.00............0
8301...10.................R 100.00....2.....................R 3.00............0

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

Attachments lcr_data.GIF 67.66 KB lcr_data2.GIF 32.01 KB
0

Hi Pritaeas,

The table structures.

Thanks

Attachments cdr_structure.GIF 57.16 KB lcr_structure.GIF 28.97 KB
0

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

Attachments lcr_extensions.GIF 26.02 KB
0

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>
0

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

Attachments lcr_tarrif.GIF 5.1 KB
1
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 by pritaeas

0

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

0

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

1

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>';
}

Edited by pritaeas

0

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.

0

$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

0

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

Edited by LRNPHP

0

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

Attachments callsNew.GIF 14 KB
0

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

1

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

Which variables contain those values?

0

Hi Pritaeas,

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

Thanks

0

Hi Pritaeas,

Nope it only displays 1 for every row.

Regards

Attachments callsCount.GIF 10.34 KB
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.