2. PHPRunner Tips and Tricks
  3. Guide 16 - Totals group on List pages
This topic is locked

Guide 16 - Totals group on List pages

 		5/23/2021 6:35:58 PM
PHPRunner Tips and Tricks
fhumanes author

They consulted me how to make totalizations by groups, on the LIST pages and facilitated links on how to do it in versions of Phprunner, 6, 7 and 8. We were not able to find references of the same topic in versions 10 of Phprunner.

I was spinning, without wanting to modify the template of the page (as it was done in previous versions) and in the end I have found a very easy solution, based on obtaining the totalizations and the registry where you have to put it in SQL.

Objective

Produce column totalizations when you change group on a List page

Technical solution

Demo: https://fhumanes.com/ruptura

img alt

If you want to see the coding and dispose of a complete example, access my site to download it.
fhumanes author 5/23/2021

It has some limitations, for example, that you can not let the user order the columns, as it requires them to be ordered in a special way.

The "secret" is that the data when executing the SQL has already calculated the total and the line where the total must be printed.

In this example, the data is obtained:

The record data.

SELECT
linea_factura.idlinea_factura,
linea_factura.factura_idfactura,
linea_factura.producto_idproducto,
linea_factura.Nombre,
linea_factura.Precio,
linea_factura.Cantidad,
linea_factura.Valor
FROM linea_factura

To get the total, this Select is executed.

SELECT factura_idfactura factura_idfactura_t1, sum(Valor) Total FROM linea_factura group by factura_idfactura

To know which line you have to "print" the total.

SELECT factura_idfactura factura_idfactura_t2, max(idlinea_factura) idlinea_factura_t2 FROM linea_factura group by factura_idfactura

Thus, uniting everything, this is the resulting query.

SELECT
linea_factura.idlinea_factura,
linea_factura.factura_idfactura,
linea_factura.producto_idproducto,
linea_factura.Nombre,
linea_factura.Precio,
linea_factura.Cantidad,
linea_factura.Valor,
T1.Total,
T2.idlinea_factura_t2
FROM linea_factura
join
(SELECT factura_idfactura factura_idfactura_t1, sum(Valor) Total FROM linea_factura group by factura_idfactura) T1
on ( T1.factura_idfactura_t1 = linea_factura.factura_idfactura)
left join
(SELECT factura_idfactura factura_idfactura_t2, max(idlinea_factura) idlinea_factura_t2 FROM linea_factura group by factura_idfactura) T2
on ( T2.factura_idfactura_t2 = linea_factura.factura_idfactura and T2.idlinea_factura_t2 = linea_factura.idlinea_factura)
Order by linea_factura.factura_idfactura, linea_factura.idlinea_factura

img alt

In the idfacture column we put the group check so that the value of the column is not repeated.

In the value column we put the value field and that of the total. For one on top of another, we include this value in the CUSTOM CSS field of each of them.

:host {
display: block;
}

We have the last one and it is to indicate the total field when and how it should be printed. We use a CUSTOM visualization and include this code.

if ( $data['idlinea_factura_t2'] == NULL){
$value = NULL;
} else {
$a = new \NumberFormatter("es-ES", \NumberFormatter::DECIMAL);
$a->setAttribute(\NumberFormatter::MIN_FRACTION_DIGITS, 2);
$a->setAttribute(\NumberFormatter::MAX_FRACTION_DIGITS, 2); // by default some locales got max 2 fraction digits
$value ='**Total: **'.$a->format($value);
}

What it does is:

  • It is evaluated if it is the line where the total must be printed
  • If so, the system is prepared to print the numerical values and it is printed by preparing the literal total:.

And that's how easy it has been left.

For any questions or problems, please get in touch via email fernandohumanes@gmail.com.