Dear David,
here is my controller php:
<?php
require_once "../koolreport/autoload.php";
use \koolreport\processes\Filter;
use \koolreport\processes\ColumnMeta;
use \koolreport\pivot\processes\Pivot;
use \koolreport\processes\Group;
use \koolreport\processes\Sort;
use \koolreport\processes\Limit;
class abs extends koolreport\KoolReport
{
use \koolreport\clients\FontAwesome;
use \koolreport\clients\Bootstrap;
use \koolreport\inputs\Bindable;
use \koolreport\inputs\POSTBinding;
use \koolreport\export\Exportable;
function defaultParamValues()
{
return array(
"ProductNumber" => array(),
"LocalMaterialDescription" => array(),
"ProductHierarchie" => array(),
);
}
function bindParamsToInputs()
{
return array(
"ProductNumber",
"LocalMaterialDescription",
"ProductHierarchie",
);
}
function settings()
{
return array(
"dataSources"=>array(
"abs"=>array(
'connectionString' => 'sqlsrv:Server=tcp:12.234.45.67;Database=ABS',
'username' => 'xyz',
'password' => '*********',
),
)
);
}
function setup()
{
// PRODUKT: MultiSelect-Optionswerte aus Reporting.Report_ABS holen
$this->src('abs')
->query("
SELECT DISTINCT LocalMaterialDescription
FROM Reporting.Report_ABS
ORDER BY LocalMaterialDescription ASC
")
->pipe($this->dataStore("ABS.LocalMaterialDescription"));
// PRODUKTHIERARCHIE: MultiSelect-Optionswerte aus Reporting.Report_ABS holen
$this->src('abs')
->query("
SELECT DISTINCT ProductHierarchie
FROM Reporting.Report_ABS
ORDER BY ProductHierarchie ASC
")
->pipe($this->dataStore("ABS.ProductHierarchie"));
// ARTIKELNUMMER: MultiSelect-Optionswerte aus Reporting.Report_ABS holen
$this->src('abs')
->query("
SELECT DISTINCT ProductNumber
FROM Reporting.Report_ABS
ORDER BY ProductNumber ASC
")
->pipe($this->dataStore("ABS.ProductNumber"));
// PIVOTTABLE: Umsatz aggregieren
if(count($this->params["ProductNumber"])>0){$whereProductNumber="ProductNumber IN (:ProductNumber) ";}else{$whereProductNumber="ProductNumber IS NOT NULL ";}
if(count($this->params["LocalMaterialDescription"])>0){$whereLocalMaterialDescription="LocalMaterialDescription IN (:LocalMaterialDescription) ";}else{$whereLocalMaterialDescription="LocalMaterialDescription IS NOT NULL ";}
if(count($this->params["ProductHierarchie"])>0){$whereProductHierarchie="ProductHierarchie IN (:ProductHierarchie) ";}else{$whereProductHierarchie="ProductHierarchie IS NOT NULL ";}
$this->src('abs')
->query("
SELECT ProductNumber, LocalMaterialDescription, ProductHierarchie, BudgetAktuellerMonat, ErgebnisAktuellerMonat, BereitsFakturiertAktuellerMonat, BereitsFakturiertAktuelleWoche
FROM Reporting.Report_ABS
WHERE
$whereProductNumber AND
$whereLocalMaterialDescription AND
$whereProductHierarchie
")->params(array(
":ProductNumber"=>$this->params["ProductNumber"],
":LocalMaterialDescription"=>$this->params["LocalMaterialDescription"],
":ProductHierarchie"=>$this->params["ProductHierarchie"]
))
->pipe(new CalculatedColumn(array(
"customerGroup"=>function($row){
if ($row['BudgetAktuellerMonat'] === 'BudgetAktuellerMonat' || $row['ErgebnisAktuellerMonat'] === 'ErgebnisAktuellerMonat')
return 'customerGroup1';
else if ($row['BereitsFakturiertAktuellerMonat'] === 'BereitsFakturiertAktuellerMonat' || $row['BereitsFakturiertAktuelleWoche'] === 'BereitsFakturiertAktuelleWoche')
return 'customerGroup2';
},
)))
->pipe(new Pivot(array(
"dimensions" => array(
"column" => "customerGroup1, BudgetAktuellerMonat",
"row" => "ProductHierarchie, LocalMaterialDescription"
),
"aggregates" => array(
"sum" => "BudgetAktuellerMonat, ErgebnisAktuellerMonat, BereitsFakturiertAktuellerMonat, BereitsFakturiertAktuelleWoche, ",
)
)))
->pipe($this->dataStore('Reporting.Report_ABS'));
//echo $this->params["ProductNumber"];
}
}
And here is my view php:
<?php
use \koolreport\pivot\widgets\PivotTable;
use \koolreport\widgets\koolphp\Table;
use \koolreport\inputs\MultiSelect;
use \koolreport\inputs\Select2;
use \koolreport\inputs\CheckBoxList;
use \koolreport\inputs\RadioList;
use \koolreport\widgets\google\BarChart;
use \koolreport\widgets\google\PieChart;
use \koolreport\widgets\google;
?>
<div id="col2">
<div id="col2_content" class=""clearfix>
<form method="post">
<div class="form-group" style="width:250px;margin-right:10px;">
<h5>Produkt</h5>
<?php
Select2::create(array(
"name"=>"LocalMaterialDescription",
"dataStore"=>$this->dataStore("ABS.LocalMaterialDescription"),
"dataBind"=>"LocalMaterialDescription",
"multiple"=>true,
"attributes"=>array(
"class"=>"form-control",
)
));
?>
</div>
<div class="form-group" style="width:250px;margin-right:10px;">
<h5>Produkthierarchie</h5>
<?php
Select2::create(array(
"name"=>"ProductHierarchie",
"dataStore"=>$this->dataStore("ABS.ProductHierarchie"),
"dataBind"=>"ProductHierarchie",
"multiple"=>true,
"attributes"=>array(
"class"=>"form-control",
)
));
?>
</div>
<div class="form-group" style="width:250px;margin-right:10px;">
<h5>Artikelnummer</h5>
<?php
Select2::create(array(
"name"=>"ProductNumber",
"dataStore"=>$this->dataStore("ABS.ProductNumber"),
"dataBind"=>"ProductNumber",
"multiple"=>true,
"attributes"=>array(
"class"=>"form-control",
)
));
?>
</div>
<div class="form-group text-center">
<button class="btn btn-success" name="btnSubmit"><i class="glyphicon glyphicon-refresh"></i> Finden</button>
</div>
</form>
</div>
</div>
<div id="col1">
<div id="col1_content" class="clearfix">
<h3><img src="icon_grid.png"/> ABS (Außendienst Business Steering)</h3>
<?php
if((isset($_POST["btnSubmit"])) AND ($this->dataStore("Reporting.Report_ABS")->countData()>0))
{
PivotTable::create(array(
"dataStore"=>$this->dataStore('Reporting.Report_ABS'),
"measures" => array(
"BudgetAktuellerMonat - sum",
"ErgebnisAktuellerMonat - sum",
"BereitsFakturiertAktuellerMonat - sum",
"BereitsFakturiertAktuelleWoche - sum",
),
"headerMap" => array(
"BudgetAktuellerMonat - sum" => "∑ Budget akt. Monat",
"ErgebnisAktuellerMonat - sum" => "∑ Aktueller Monat",
"BereitsFakturiertAktuellerMonat - sum" => "∑ Fakturiert akt. Monat",
"BereitsFakturiertAktuelleWoche - sum" => "∑ Fakturiert akt. Woche",
),
"rowCollapseLevels" => array(3), //0 = zu, 1 = auf
"columnCollapseLevels" => array(1,2),
"totalName" => '<div style="display:none" align="right"><strong>TOTAL</strong></div>',
"hideTotalRow" => true,
));
}
?>
</div>
</div>
</body>
</html>