vendasAnual.php
<?php
require_once BASE_EXTRAS . "/koolreport/autoload.php";
use \koolreport\processes\Filter;
use \koolreport\processes\TimeBucket;
use \koolreport\processes\ColumnMeta;
use \koolreport\processes\CalculatedColumn;
use \koolreport\processes\AccumulativeColumn;
use \koolreport\processes\AggregatedColumn;
use \koolreport\processes\RemoveColumn;
use \koolreport\processes\Map;
use \koolreport\pivot\processes\Pivot;
use \koolreport\processes\Group;
use \koolreport\processes\ValueMap;
use \koolreport\processes\Sort;
use \koolreport\processes\Limit;
use \koolreport\inputs\GETBinding;
/*
Filtro Data movimentacaodatadata_inicio
dataInicial
datafinal
*/
$array = $this->input->get();
$datIni = $array["movimentacao__data__data_inicio"][0];
$datFim = $array["movimentacao__data__data_inicio"][1];
/*
Partição
dataInicial
datafinal
*/
$util = $this->call("util", "util", "getInstance");
$sqlParticao = $util->getSqlParticao($datIni, $datFim, false);
class vendasAnual
{
public function __construct($params = array())
{
parent::__construct();
}
}
set_time_limit(0);
class vendasCore extends \koolreport\KoolReport
{
public function settings()
{
return array(
"dataSources" => array(
"vendasAnual" => array(
"connectionString" => "mysql:host=localhost;dbname=Stage",
"username" => "root",
"password" => "a7v400mx",
"charset" => "utf8"
)
),
"assets" => array(
"path" => BASE_ARQUIVOS,
"url" => PATH_ARQUIVOS_DIR
)
);
}
public function setup()
{
$this->src('vendasAnual')
->query("SELECT
p.LevelDescription1,
p.LevelDescription2,
p.LevelDescription3,
p.LevelDescription4,
l.BriefDescription,
m.LocalCode,
COALESCE(SUM(TotalSaleValue), 0) as 'totalVenda',
COALESCE(SUM(ValueCMV), 0) as 'totalCusto',
month(m.dateMoviment) as mes,
SUM(TotalSaleAmount) as 'qtde',
-- round(if((((sum(TotalSaleValue) - sum(ValueCMV))/sum(TotalSaleValue))*100) is null, 0, (((sum(TotalSaleValue) - sum(ValueCMV))/sum(TotalSaleValue))*100)),2) AS 'totalMargem',
m.dateMoviment ,
m.ProductCode ,
COALESCE(estoque.totalEstoque, 0) as 'totalEstoque',
0 as 'totalMargem',
COALESCE(estoque.totalVendaEstoque, 0) as 'totalVendaEstoque',
COALESCE(estoque.totalCustoEstoque, 0) as 'totalCustoEstoque'
FROM Stage.MetricData partition(" . $this->params["part"] . ") m
LEFT JOIN Stage.ProductDimension p
ON p.ProductCode = m.ProductCode
LEFT JOIN Stage.LocalDimension l
ON l.LocalCode = m.LocalCode
LEFT JOIN (SELECT
AmountStockTotal as 'totalEstoque',
ValueStockTotal as 'totalVendaEstoque',
CostStockTotal as 'totalCustoEstoque',
m.ProductCode as 'ProductCodeEstoque'
FROM Stage.MetricData partition(" . $this->params["part"] . ") m
LEFT JOIN Stage.ProductDimension p
ON p.ProductCode = m.ProductCode
LEFT JOIN Stage.LocalDimension l
ON l.LocalCode = m.LocalCode
LEFT JOIN (
SELECT
max(m.dateMoviment) as m,
m.ProductCode as id
FROM Stage.MetricData partition(" . $this->params["part"] . ") m
LEFT JOIN Stage.ProductDimension p
ON p.ProductCode = m.ProductCode
LEFT JOIN Stage.LocalDimension l
ON l.LocalCode = m.LocalCode
WHERE (DATE(m.dateMoviment) BETWEEN :dataIni AND :datafim)
GROUP BY l.BriefDescription
) ee ON ee.id = m.ProductCode
WHERE (DATE(m.dateMoviment) BETWEEN :dataIni AND :datafim)
GROUP BY l.BriefDescription) estoque ON estoque.ProductCodeEstoque = m.ProductCode
WHERE (DATE(m.dateMoviment) BETWEEN :dataIni AND :datafim)
GROUP BY DATE_FORMAT(m.dateMoviment,'%m'),l.BriefDescription,p.LevelDescription1,p.LevelDescription2,p.LevelDescription3,p.LevelDescription4 ")
->params(array(
":dataIni" => $this->params["extraParams"]["movimentacao__data__data_inicio"][0],
":datafim" => $this->params["extraParams"]["movimentacao__data__data_inicio"][1]
))
->pipe(new ColumnMeta(array(
"totalMargem"=>array(
'type' => 'number',
"prefix" => "",
"decimals" => 2,
"dec_point" => ",",
"thousand_sep" => "."
),
"totalVenda" => array(
'type' => 'number',
"prefix" => "",
"decimals" => 2,
"dec_point" => ",",
"thousand_sep" => "."
),
"totalCusto" => array(
'type' => 'number',
"prefix" => "",
"decimals" => 2,
"dec_point" => ",",
"thousand_sep" => "."
),
"qtde" => array(
'type' => 'number',
"prefix" => "",
"decimals" => 2,
"dec_point" => ",",
"thousand_sep" => "."
),
"totalEstoque" => array(
'type' => 'number',
"prefix" => "",
"decimals" => 2,
"dec_point" => ",",
"thousand_sep" => "."
),
"totalVendaEstoque" => array(
'type' => 'number',
"prefix" => "",
"decimals" => 2,
"dec_point" => ",",
"thousand_sep" => "."
),
"totalCustoEstoque" => array(
'type' => 'number',
"prefix" => "",
"decimals" => 2,
"dec_point" => ",",
"thousand_sep" => "."
)
)))
->pipe(new TimeBucket(array(
"dateMoviment" => "month"
)))
->pipe(new Pivot(array(
"dimensions" => array(
"column" => "mes",
// "row" => "BriefDescription,LevelDescription1,LevelDescription2,LevelDescription3,LevelDescription4"
"row" => "BriefDescription"
),
"aggregates" => array(
"sum" => "totalVenda,totalCusto,qtde",
"max" => "totalEstoque,totalVendaEstoque,totalCustoEstoque"
)
)))
->pipe(new CalculatedColumn(array(
// "margem_percent"=>"(({totalVenda - sum}-{totalCusto - sum})/{totalVenda - sum})*100"
"margem_percent"=>function($data){
alert($data);
if(!empty($data["totalVenda - sum"])){
$data["totalMargem"] = (($data["totalVenda - sum"] - $data["totalCusto - sum"])/$data["totalVenda - sum"])*100;
}
return $data["totalMargem"];
}
)))
->pipe($this->dataStore('vendasAnual'));
}
}
// renomeando arquivo que o gestor gera para o padrao do kool troca o .html pelo .view
$_POST = $_REQUEST;
$retorno = rename($this->_VISUAL, str_replace(".html.php", ".view.php", $this->_VISUAL));
$arquivo_view = str_replace(".html", "", pathinfo($this->_VISUAL, PATHINFO_FILENAME));
$vendasCore = new vendasCore([
"extraParams" => [
"data64" => base64_encode(json_encode([
"action" => "relatorios/relatorio_gestor",
"method" => "generic",
"params" => [
"report" => "Venda_anual",
"movimentacao__data__data_inicio" => [$datIni, $datFim]
]
])),
"movimentacao__data__data_inicio" => [$datIni, $datFim]
],
"part" => (string)$sqlParticao
]);
// exibe o relatórios passando qual view ira utlizar
$vendasCore->run()->render($arquivo_view);
exit;
vendasAnual.view.php
<?php
use \koolreport\pivot\widgets\PivotMatrix;
use \koolreport\core\Utility;
use \koolreport\processes\Filter;
use \koolreport\processes\ColumnMeta;
use \koolreport\pivot\processes\Pivot;
use \koolreport\instant\Widget;
use \koolreport\datasources\CSVDataSource;
?>
