Dear Dave,
something seems to be wrong with my code (I guess syntax!?), becaue I get a blank page! Could you please review where is my mistake?
//firmendetail.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;
use \koolreport\processes\Map;
use \koolreport\cleandata\FillNull;
use \koolreport\processes\Custom;
use \koolreport\cube\processes\Cube;
class firmendetail extends koolreport\KoolReport
{
use \koolreport\clients\FontAwesome;
use \koolreport\clients\Bootstrap;
use \koolreport\inputs\Bindable;
use \koolreport\inputs\POSTBinding;
//use \koolreport\inputs\GETBinding;
use \koolreport\export\Exportable;
function defaultParamValues()
{
return array(
"Auftrg" => array("YBC"),
"NameAuftr" => array(),
);
}
function bindParamsToInputs()
{
return array(
"Auftrg",
"NameAuftr",
);
}
function settings()
{
return array(
"dataSources"=>array(
"adr"=>array(
'connectionString' => 'sqlsrv:Server=tcp:1.123.45.67;Database=ABC_ADR',
'username' => 'sa',
'password' => '******',
),
)
);
}
function setup()
{
// KUNDENNUMMER: MultiSelect-Optionswerte aus Reporting.TOTAL_v holen
$this->src('adr')
->query("
SELECT DISTINCT Auftrg
FROM Reporting.TOTAL_v
ORDER BY Auftrg ASC
")
->pipe($this->dataStore("ADR.Auftrg"));
// FIRMENDETAIL-PIVOT: Umsatz/Menge aggregieren
if(count($this->params["Auftrg"])>0){$whereAuftrg="Auftrg IN (:Auftrg) ";}else{$whereAuftrg="Auftrg IS NOT NULL ";}
$this->src('adr')
->query("
SELECT Auftrg, NameWarenem, NameAuftr, StrasAuftr, PlzAuftr, OrtAuftr, Produktname, FakdatZBCP, Ag2, AG1Beschreibung, AG2Beschreibung, AuftrM, Wert
FROM Reporting.TOTAL_v
WHERE
$whereAuftrg
")->params(array(
":Auftrg"=>$this->params["Auftrg"],
))
->pipe(new Custom(function($data){
$data["Wert"] = trim(str_replace(",",".",$data["Wert"]));
return $data;
}))
->pipe(new ColumnMeta(array(
"Wert"=>array(
"align"=>"right",
"type"=>"number",
"prefix"=>"",
"suffix"=>"",
"decimals"=>2,
"thousandSeparator"=>".",
"decimalPoint"=>",",
)
)))
$node = $this->src('adr')
->pipe(new Map(array(
'{value}' => function($row, $metaData) {
$date = explode('.', $row['FakdatZBCP']);
$row['Year'] = $date[0];
$row['Month'] = $date[1];
return array($row);
},
'{meta}' => function($metaData) {
$metaData['columns']['Year'] = array(
'type' => 'number',
);
$metaData['columns']['Month'] = array(
'type' => 'number',
);
return $metaData;
}
)))
$node->pipe(new Pivot(array(
"dimensions"=>array(
"column" => "Year",
"row" => "Month",
),
"aggregates" => array(
"sum" => "AuftrM, Wert",
)
)))
->pipe($this->dataStore('datastore1'));
}
}
//firmendetail.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;
?>
<script type="text/javascript">
function doExport()
{
//alert(1);
var _form = document.getElementById("parameterForm");
_form.action = "export.php";
_form.submit();
_form.action = "index.php";
}
</script>
<script type="text/javascript">
$(document).ready(function(){
$('[data-toggle="tooltip"]').tooltip();
});
</script>
<div id="col2">
<div id="col2_content" class=""clearfix>
<form id="parameterForm" method="post">
<div class="form-group" style="width:250px;margin-right:10px;">
<h5>Kundennummer</h5>
<?php
Select2::create(array(
"name"=>"Auftrg",
"dataStore"=>$this->dataStore("ADR.Auftrg"),
"dataBind"=>"Auftrg",
"multiple"=>true,
"attributes"=>array(
"class"=>"form-control",
)
));
?>
</div>
<div class="form-group text-center">
<button class="btn btn-primary" name="btnSubmit"><i class="glyphicon glyphicon-refresh"></i> Finden</button>
</div>
</form>
</div>
</div>
<div id="col1">
<div id="adr1" class="tab-pane fade in active">
<div id="col1_content" class="clearfix">
<div style="float:left"><h3> <img src="icon_grid.png"/> ADR: FIRMENDETAIL</h3></div>
<div style="float:right"><button style="min-width:200px;" onclick="doExport()" class="btn btn-primary">PDF-Export<br />FIRMENDETAIL</button></div>
<?php
if((isset($_POST["btnSubmit"])) AND ($this->dataStore("datastore1")->countData()>0))
{
PivotTable::create(array(
"dataStore"=>$this->dataStore('datastore1'),
"headerMap" => array(
"AuftrM - sum" => "∑ Menge",
"Wert - sum" => "∑ Umsatz pro Monat",
),
"rowCollapseLevels" => array(0),
"columnCollapseLevels" => array(2),
"totalName" => '<div align="right"><strong>GESAMT</strong></div>'
));
}
?>
</div>
</div>
</div>
</body>
</html>