Display report separated by date groups

0

I want to display a report separating results into date groups as shown below

|   REF.    |   NOME    |   DEBITO  |
=====================================
|   X741852 |   MARIA   |   2.500   |
|   B890656 |   EDUARDA |   500     |
|
|   Data: 2015-02-05
-------------------------------------
|   CK54604 |   JOAO    |   42.050  |
|
|   Data: 2015-02-18
-------------------------------------
|   FE55852 |   CHARLES |   28.500  |
|   X741852 |   VIVIANE |   74.2500 |
|
|   Data: 2014-06-09
-------------------------------------

The data comes from a query in DB

$sql = $this->db->query($query) or die (sprintf("Falha: %s", $this->db->error()));

if ($sql->num_rows) {
    $html = "<table border=\"1\">
        <tr>
            <th>REF.</th>
            <th>FAVORECIDO</th>
            <th>DEBITO</th>
        </tr>";

    while ($row = $sql->fetch_object()) {

        $html .= "<tr>
            <td>{$row->refer}</td>
            <td>{$row->favorecido}</td>
            <td>{$row->favorecido}</td>
        </tr>";

        /*
        // aqui não sei como separar, por uma linha com a DATA conforme exemplo acima
        if ($row->data) {
             $html .= "<tr>
                <td rowspan="3">Data: {$row->data}</td>
            </tr>";
        }
        */
    }

    $html .= "</table>";

    echo $html;
}

My test array

$data[] = array(
    'data' => '2015-02-05',
    'refer' => 'X741852',
    'favorecido' => 'MARIA',
    'debito' => '2.500'
);
$data[] = array(
    'data' => '2015-02-05',
    'refer' => 'B890656',
    'favorecido' => 'EDUARDA',
    'debito' => '500'
);
$data[] = array(
    'data' => '2015-02-18',
    'refer' => 'CK546045',
    'favorecido' => 'JOAO',
    'debito' => '42.050'
);
$data[] = array(
    'data' => '2014-06-09',
    'refer' => 'FE55852',
    'favorecido' => 'CHARLES',
    'debito' => '28.500'
);
$data[] = array(
    'data' => '2014-06-09',
    'refer' => 'X741852',
    'favorecido' => 'VIVIANE',
    'debito' => '74.2500'
);
    
asked by anonymous 13.01.2016 / 14:38

2 answers

2

This is very simple, I've actually generated a new array based on the initial array data

<?php

$data[] = array(
    'data' => '2015-02-05',
    'refer' => 'X741852',
    'favorecido' => 'MARIA',
    'debito' => '2.500'
);
$data[] = array(
    'data' => '2015-02-05',
    'refer' => 'B890656',
    'favorecido' => 'EDUARDA',
    'debito' => '500'
);
$data[] = array(
    'data' => '2015-02-18',
    'refer' => 'CK546045',
    'favorecido' => 'JOAO',
    'debito' => '42.050'
);
$data[] = array(
    'data' => '2014-06-09',
    'refer' => 'FE55852',
    'favorecido' => 'CHARLES',
    'debito' => '28.500'
);
$data[] = array(
    'data' => '2014-06-09',
    'refer' => 'X741852',
    'favorecido' => 'VIVIANE',
    'debito' => '74.2500'
);

$novo_array = array();

// Looop para construir Novo array separado pela data
foreach ( $data as $value ) {
    $novo_array[ $value[ "data" ] ][ ] = $value;
}

?>

<html>
    <head><title>Apenas Teste</title></head>
    <body>
        <table border="1">
            <tr>
                <th>REF.</th>
                <th>NOME</th>
                <th>DEBITO</th>
            </tr>
            <?php foreach( $novo_array as $data => $linhas ): ?>

                <?php foreach( $linhas as $linha ): ?>

                    <tr>
                        <td><?php echo $linha["refer"];?></td>
                        <td><?php echo $linha["favorecido"];?></td>
                        <td><?php echo $linha["debito"];?></td>
                    </tr>

                <?php endforeach; ?>

                <tr>
                    <td colspan="3">DATA: <?php echo $data;?></td>
                </tr>

            <?php endforeach; ?>
        </table>
    </body>
</html>

I tested it here and it worked perfectly, if you have any errors please let me know.

    
13.01.2016 / 15:05
1

First you need to make sure the data is already sorted by date.

According to your test array, we have the following array:

$data = array(
    0 => array(
        'data'       => '2015-02-05',
        'refer'      => 'X741852',
        'favorecido' => 'MARIA',
        'debito'     => '2.500'
    ),
    1 => array(
        'data'       => '2015-02-05',
        'refer'      => 'B890656',
        'favorecido' => 'EDUARDA',
        'debito'     => '500'
    ),
    2 => array(
        'data'       => '2015-02-18',
        'refer'      => 'CK546045',
        'favorecido' => 'JOAO',
        'debito'     => '42.050'
    ),
    3 => array(
        'data'       => '2014-06-09',
        'refer'      => 'FE55852',
        'favorecido' => 'CHARLES',
        'debito'     => '28.500'
    ),
    4 => array(
        'data'       => '2014-06-09',
        'refer'      => 'X741852',
        'favorecido' => 'VIVIANE',
        'debito'     => '74.2500'
    ),
);

The code to display it in an HTML table is this below (you can change the display layout if you want, but the PHP code remains the same):

<table>
    <thead>
        <th>REF.</th>
        <th>NOME</th>
        <th>DEBITO</th>
    </thead>
    <tbody>

    <?php 
    foreach ($data as $index => $dados) {
        echo '<tr>';

        foreach ($dados as $key => $value) {
            echo '<td>'.$value.'</td>';
        }

        // Analisa quando a próxima data muda e imprime
        $dataAtual   = $dados['data'];
        $dataProximo = $data[$index+1];

        if ($dataAtual != $dataProximo) {
            echo '<td>'.$dataAtual.'</td>';
        }

        echo '</tr>';
    } 
    ?>

    </tbody>
</table>

PS: This format does what you requested, it prints the date after of the data, but I find that strange, in my opinion it is better to display the date before , would look like this:

<?php 
$dataAnterior = null;

foreach ($data as $dados) {
    echo '<tr>';

    // Analisa quando a data muda e imprime
    $dataAtual = $dados['data'];
    if ($dataAnterior != $dataAtual) {
        echo '<td>'.$dataAtual.'</td>';
        $dataAnterior = $dataAtual;
    }

    foreach ($dados as $key => $value) {
        echo '<td>'.$value.'</td>';
    }

    echo '</tr>';
} 
?>
    
13.01.2016 / 15:13