Create report displaying total and subtotal

Question

Create report displaying total and subtotal

Navigation

#1 by (0 votes)

1

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

In MySQL I have a boleto list issued, in which the majority has been removed. In this table (Historico) it has the following fields:

Bold_Code
CustomerID
Date_Payment
Paid_value
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.

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 automatically geara this way. Could you help me?

php mysql

asked by anonymous 23.03.2018 / 21:06

1 answer

CardView And HorizontalScrollView Performance - Helpers

score 0 · Answer 1

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

Just to understand, first is saved the value of the first group and whenever it changes the table is closed, shows the total and opens a new one, then calculates the grand 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>