Hello David,
here are the binding parameters:
function bindParamsToInputs()
{
return array(
"Sell",
"KdName",
"Werbegruppe",
"NKdName",
"NOrt",
"ADBezirk",
"NProdGruppe",
"NArtBez",
"Geschaeftsjahr",
"Quartal",
"Monat",
);
}
Here is the SQL statement incl. params binding:
// PIVOTTABLE: Umsatz aggregieren
if(count($this->params["Werbegruppe"])>0){$whereWerbegruppe="WerbegruppeVerbund IN (:Werbegruppe) ";}else{$whereWerbegruppe="WerbegruppeVerbund IS NOT NULL ";}
if(count($this->params["NOrt"])>0){$whereNOrt="NOrt IN (:NOrt) ";}else{$whereNOrt="NOrt IS NOT NULL ";}
if(count($this->params["NKdName"])>0){$whereNKdName="NKdName IN (:NKdName) ";}else{$whereNKdName="NKdName IS NOT NULL ";}
if(count($this->params["ADBezirk"])>0){$whereADBezirk="ADBezirk IN (:ADBezirk) ";}else{$whereADBezirk="ADBezirk IS NOT NULL ";}
if(count($this->params["NProdGruppe"])>0){$whereNProdGruppe="NProdGruppe IN (:NProdGruppe) ";}else{$whereNProdGruppe="NProdGruppe IS NOT NULL ";}
if(count($this->params["Geschaeftsjahr"])>0){$whereGeschaeftsjahr="Geschaeftsjahr IN (:Geschaeftsjahr) ";}else{$whereGeschaeftsjahr="Geschaeftsjahr IS NOT NULL ";}
if(count($this->params["NArtBez"])>0){$whereNArtBez="NArtBez IN (:NArtBez) ";}else{$whereNArtBez="NArtBez IS NOT NULL ";}
if(count($this->params["Quartal"])>0){$whereQuartal="Quartal IN (:Quartal) ";}else{$whereQuartal="Quartal IS NOT NULL ";}
if(count($this->params["Monat"])>0){$whereMonat="Monat IN (:Monat) ";}else{$whereMonat="Monat IS NOT NULL ";}
$this->src('autorep')
->query("
SELECT Sell, Geschaeftsjahr, Monat, Quartal, WerbegruppeVerbund, NKdName, NKdNr, NOrt, NProdGruppe, NArtBez, BestandHaendler_aktuell, BestandZentrale_aktuell, ADBezirk, Verkaufsmenge, Umsatz
FROM Reporting.autorep_v_noNulls
WHERE
Sell = (:Sell) AND
KdName IN (:KdName) AND
$whereWerbegruppe AND
$whereNKdName AND
$whereNOrt AND
$whereADBezirk AND
$whereNProdGruppe AND
$whereNArtBez AND
$whereGeschaeftsjahr AND
$whereQuartal AND
$whereMonat
")->params(array(
":Sell"=>$this->params["Sell"],
":KdName"=>$this->params["KdName"],
":Werbegruppe"=>$this->params["Werbegruppe"],
":NKdName"=>$this->params["NKdName"],
":NOrt"=>$this->params["NOrt"],
":ADBezirk"=>$this->params["ADBezirk"],
":NProdGruppe"=>$this->params["NProdGruppe"],
":NArtBez"=>$this->params["NArtBez"],
":Geschaeftsjahr"=>$this->params["Geschaeftsjahr"],
":Quartal"=>$this->params["Quartal"],
":Monat"=>$this->params["Monat"]
))
->pipe(new ColumnMeta(array(
"Umsatz"=>array(
"align"=>"right",
"type"=>"number",
"prefix"=>"",
"suffix"=>"",
"decimals"=>2,
"thousandSeparator"=>".",
"decimalPoint"=>",",
),
"Verkaufsmenge" => array(
"align"=>"right",
)
)))
->saveTo($node);
$node->pipe(new Pivot(array(
"dimensions" => array(
"column" => "Geschaeftsjahr, Quartal, Monat",
"row" => "WerbegruppeVerbund, NKdName, NOrt, NKdNr, NProdGruppe"
),
"aggregates" => array(
"sum" => "Verkaufsmenge, Umsatz",
)
)))
->pipe($this->dataStore('Reporting.autorep_v_noNulls'));
Thx a lot and kind regards,
bysystem