KoolReport's Forum

Official Support Area, Q&As, Discussions, Suggestions and Bug reports.
Forum's Guidelines

Add column with orders of previous year #1109

Open Roberto opened this topic on on Oct 1, 2019 - 4 comments

Roberto commented on Oct 1, 2019

Hi, I've generated a report with Koolreport and Laravel; in the table, I have the amount of orders grouped by month of the selected year.

This is my code:

<?php
$query = DB::table('fattura')
->select('anagrafica.nome_breve', DB::raw('SUM(qta*prezzo) as somma'), DB::raw('MONTH(data_documento) as mese_documento'))
->leftJoin('riga_fattura', 'riga_fattura.id_fattura', 'fattura.id')
->leftJoin('anagrafica', 'anagrafica.id', 'fattura.id_cliente')
->where([['data_documento', 'LIKE', $anno.'-'.$mese.'-%']])
->groupBy('mese_documento')
->orderBy('mese_documento', 'ASC');

$vendita = array(
  "dataSource"=>$query->get()
);


Table::create(array(
  "dataSource"=>$vendita['dataSource'],
  "showFooter"=>true,
  "cssClass"=>array(
    "table"=>"table-bordered table-striped table-hover"
  ),
  "columns"=>array(
    "mese_documento"=>array(
      "label"=>"Mese"
    ),
    "somma"=>array(
      "label"=>"Somma",
      "type"=>"number",
      "footer"=>"sum",
      "footerText"=>"<b>Totale:</b> @value",
      'formatValue'=>function($value){
        return "€ ".number_format($value, 2, ",", ".");
        }
      )
    ),
  ));
  ?>

Now, I want to add a column with the amount of the orders of the previous year, grouped by month. How I can do this? Thanks! Roberto

KoolReport commented on Oct 1, 2019

Then you need another query to get the same kinds of data for previous year. Next you join two data datasources base on the "mese_documento" so that you have array with first column is "mese_documento" second column is "soma" and third column is "lastyear_soma". Now everything will be easy with Table.

Roberto commented on Oct 1, 2019

Thanks. But where I can do the Join? In the query or in the Table object?

$query_1 = DB::table('fattura')
->select('anagrafica.nome_breve', DB::raw('SUM(qta*prezzo) as somma'), DB::raw('MONTH(data_documento) as mese_documento'))
->leftJoin('riga_fattura', 'riga_fattura.id_fattura', 'fattura.id')
->leftJoin('anagrafica', 'anagrafica.id', 'fattura.id_cliente')
->where([['data_documento', 'LIKE', $anno.'-'.$mese.'-%']])
->groupBy('mese_documento')
->orderBy('mese_documento', 'ASC');

$query_2 = DB::table('fattura')
->select('anagrafica.nome_breve', DB::raw('SUM(qta*prezzo) as somma'), DB::raw('MONTH(data_documento) as mese_documento'))
->leftJoin('riga_fattura', 'riga_fattura.id_fattura', 'fattura.id')
->leftJoin('anagrafica', 'anagrafica.id', 'fattura.id_cliente')
->where([['data_documento', 'LIKE', ($anno-1).'-'.$mese.'-%']])
->groupBy('mese_documento')
->orderBy('mese_documento', 'ASC');

$vendita = array(
  "dataSource"=>$query_1->get()
);

......

Roberto commented on Oct 1, 2019

This is my setup() function:

$first = $this->src("mysql")
        ->query("SELECT SUM(qta*prezzo) as somma, MONTH(data_documento) as mese_documento FROM fattura LEFT JOIN riga_fattura ON riga_fattura.id_fattura = fattura.id WHERE data_documento LIKE '2019-%' GROUP BY mese_documento");
        $second = $this->src("mysql")
        ->query("SELECT SUM(qta*prezzo) as somma, MONTH(data_documento) as mese_documento FROM fattura LEFT JOIN riga_fattura ON riga_fattura.id_fattura = fattura.id WHERE data_documento LIKE '2018-%' GROUP BY mese_documento");

        $first->pipe($this->dataStore("first"));
        $second->pipe($this->dataStore("second"));

        //Save orginal data
        $join = new Join($first,$second,array("mese_documento"=>"mese_documento"));

        $join->pipe($this->dataStore("fatture_total"));

The result table has only two columns, somma and mese_documento. Can you help me? Thanks!

KoolReport commented on Oct 2, 2019

In your second query, please change SUM(qta*prezzo) as somma to SUM(qta*prezzo) as somma_previous to avoid the overwriting the column of two sources.

Build Your Excellent Data Report

Let KoolReport help you to make great reports. It's free & open-source released under MIT license.

Download KoolReport View demo
help needed

None