Create report displaying total and subtotal

Asked

Viewed 757 times

1

I need to create a PHP report in which information will be searched in Mysql.

In Mysql I have a list of billets issued, in which most were paid. In this table(Historico) has the following fields:

  • Code_boleto
  • Code_client
  • Payment date
  • Valor_pago
  • Financial group

I would like the report to display all the data, but separating the companies by group and calculating the subtotal collected from each group(There are several groups).

Example:

GRUPO-1

 1. Cliente1   |   26/02/2018   |   100,00   |   Grupo1
 2. Cliente7   |   22/02/2018   |    50,00   |   Grupo1  
 3. Cliente8   |   10/02/2018   |    50,00   |   Grupo1 

Subtotal = 200,00

GRUPO-2

 1. Cliente3   |   01/02/2018   |   120,00   |   Grupo2
 2. Cliente5   |   01/02/2018   |    40,00   |   Grupo2  
 3. Cliente9   |   11/02/2018   |    50,00   |   Grupo2 

Subtotal = 210,00

Total Final = 410,00

I’m not finding the ideal logic to geara automatically this way. Could you help me?

php mysql

  • What have you done? you want SQL to bring everything ready or it can be formatted in PHP?

  • Gulherme, I have done all the graphical part of the report, but the main one (PHP and Mysql) not yet. I’m using Codeigniter, but I won’t stick to the detail of the framework.... I want to do it in a way that will burden the server less, that is, in the most correct way possible.

  • I don’t use any PHP framework, but I would do (without Codeigniter) an SQL search taking all the data without WHERE, sorting by group and client, would divide the screen into several tables (1 per group), total and subtotal I would let the JS calculate

  • Yes, but my question is how to separate by group. I don’t want to print everything together understood? And it would be too heavy to do a while for each group...

2 answers

1

You can use DISTINCT from SQL, it takes all the values of a tab, and takes 1 single example of each.

Table (User):
User | Empresa

A     | Google
B     | Facebook
C     | Google
D     | Facebook

SELECT DISTINCT empresa FROM User

Return:

    Google
    Facebook

Note that he returned all types of company only once.

With two other queries, you can take users and the total sum.

SELECT * FROM TABLE_NAME WHERE Grupo = 'NOME_GRUPO'

SELECT SUM(COLUMN_PRICE) FROM TABLE_NAME WHERE Grupo = 'NOME_GRUPO';

This is a method that asks for more queries, but returns small results, and who processes everything is the database.

You could use method to sort, pass value by value summing, and when you arrive in a different group, it puts the maximum and starts summing again. But give user the server processing that is running the PHP server.

0

You can do something like this (mysqli, without Codeigniter):

Just to understand, first the value of the first group is saved and whenever it changes the table is closed, shows the total and opens a new one, then calculates the general total by js

<?php
$mysqli = new mysqli('localhost', 'root', 'costamilam', 'teste');

$sql = "
    SELECT 
        *
    FROM 
        a
    ORDER BY
        grupo_financeiro,
        codigo_cliente
";

$stmt = $mysqli->prepare($sql);

$stmt->execute();

$resultado = $stmt->get_result();

$array = $resultado->fetch_all();

?>
<table>
    <thead>
        <th>
            <td>Cliente</td>
            <td>Data</td>
            <td>Valor</td>
        </th>
    </thead>
    <tbody>

<?php
$total = 0;
$grupo = $array[0][4];

foreach($array as $a) {
    if($grupo == $a[4]) {
        $total += $a[3];

        echo "
            <tr>
                <td>$a[1]</td>
                <td>$a[2]</td>
                <td>$a[3]</td>
            </tr>
        ";
    } else {
        $grupo = $a[4];

        echo "
                </tbody>
                <tfoot>
                    <tr>
                        <td>Total R$<span class='total'>$total</td>
                    </tr>
                </tfoot>
            </table>

            <table>
                <thead>
                    <th>
                        <td>Cliente</td>
                        <td>Data</td>
                        <td>Valor</td>
                    </th>
                </thead>
                <tbody>
                    <tr>
                        <td>$a[1]</td>
                        <td>$a[2]</td>
                        <td>$a[3]</td>
                    </tr>
        ";

        $total = $a[3];
    }
}
?>
    </tbody>
    <tfoot>
        <tr>
            <td>Total R$<span class='total'><?php echo $total; ?></td>
        </tr>
    </tfoot>
</table>

<p id="total"></p>

<script>
    var texto = document.getElementById('total')

    var totais = document.getElementsByClassName('total')

    var total = 0

    for(var i = 0; i < totais.length; i++) {
        total += parseInt(totais[i].firstChild.nodeValue)
    }

    texto.innerText = 'TOTAL GERAL: R$' + total
</script>

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.