Erro na query para gerar Relatorio

Ico Menezes 02/09/2019 (há 4 anos)

<?php
//<fileHeader>
//</fileHeader>
class RelatorioTabular extends TPage
{
private $form; // form
private $loaded;
private static $database = 'clinica';
private static $activeRecord = 'Agendamento';
private static $primaryKey = 'id';
private static $formName = 'formReport_Agendamento';
//<classProperties>
//</classProperties>
/**
* Class constructor
* Creates the page, the form and the listing
*/
public function __construct()
{
parent::__construct();
// creates the form
$this->form = new BootstrapFormBuilder(self::$formName);
// define the form title
$this->form->setFormTitle("RelatorioTabular");
$criteria_unidade_id = new TCriteria();
$criteria_profissional = new TCriteria();
$filterVar = TSession::getValue("userunitids");
$criteria_unidade_id->add(new TFilter('id', 'in', $filterVar));
$filterVar = Grupo::PROFISSIONAL;
$criteria_profissional->add(new TFilter('id', 'in', "(SELECT pessoa_id FROM pessoa_grupo WHERE grupo_id in (SELECT id FROM grupo WHERE id = '{$filterVar}') )"));
$unidade_id = new TDBCombo('unidade_id', 'clinica', 'SystemUnit', 'id', '{name}','name asc' , $criteria_unidade_id );
$profissional = new TDBCombo('profissional', 'clinica', 'Pessoa', 'id', '{nome}','nome asc' , $criteria_profissional );
$data_ini = new TDate('data_ini');
$data_fim = new TDate('data_fim');
$data_ini->setDatabaseMask('yyyy-mm-dd');
$data_fim->setDatabaseMask('yyyy-mm-dd');
$data_ini->setMask('dd/mm/yyyy');
$data_fim->setMask('dd/mm/yyyy');
$data_ini->setSize(160);
$data_fim->setSize(160);
$unidade_id->setSize('97%');
$profissional->setSize('97%');
$row1 = $this->form->addFields([new TLabel("Unidade:", null, '14px', null, '100%')],[$unidade_id],[new TLabel("Profissional:", null, '14px', null)],[$profissional]);
$row2 = $this->form->addFields([new TLabel("Data inicial:", null, '14px', null)],[$data_ini],[new TLabel("Data final:", null, '14px', null)],[$data_fim]);
// keep the form filled during navigation with session data
$this->form->setData( TSession::getValue(__CLASS__.'_filter_data') );
$btn_ongeneratehtml = $this->form->addAction("Gerar HTML", new TAction([$this, 'onGenerateHtml']), 'fa:code #ffffff');
$btn_ongeneratehtml->addStyleClass('btn-primary');
$btn_ongeneratepdf = $this->form->addAction("Gerar PDF", new TAction([$this, 'onGeneratePdf']), 'fa:file-pdf-o #d44734');
$btn_ongeneratexls = $this->form->addAction("Gerar XLS", new TAction([$this, 'onGenerateXls']), 'fa:file-excel-o #00a65a');
$btn_ongeneratertf = $this->form->addAction("Gerar RTF", new TAction([$this, 'onGenerateRtf']), 'fa:file-text-o #324bcc');
// vertical box container
$container = new TVBox;
$container->style = 'width: 100%';
$container->add(TBreadCrumb::create(["Gráficos","Gráficos de ajustes"]));
$container->add($this->form);
parent::add($container);
}
//<generated-FormAction-onGenerateHtml>
public function onGenerateHtml($param = null)
{
$this->onGenerate('html');
}//</end>
//</generated-FormAction-onGenerateHtml>
//<generated-FormAction-onGeneratePdf>
public function onGeneratePdf($param = null)
{
$this->onGenerate('pdf');
}//</end>
//</generated-FormAction-onGeneratePdf>
//<generated-FormAction-onGenerateXls>
public function onGenerateXls($param = null)
{
$this->onGenerate('xls');
}//</end>
//</generated-FormAction-onGenerateXls>
//<generated-FormAction-onGenerateRtf>
public function onGenerateRtf($param = null)
{
$this->onGenerate('rtf');
}//</end>
//</generated-FormAction-onGenerateRtf>
/**
* Register the filter in the session
*/
public function getFilters()
{
// get the search form data
$data = $this->form->getData();
$filters = [];
TSession::setValue('filter_data', NULL);
if ($data->unidade_id)
{
$filters['unidade_id'] = $data->unidade_id;// create the filter
};
if (($data->data_ini !== '') AND ($data->data_fim !== ''))
{
$filters['data_ini'] = $data->data_ini;
$filters['data_fim'] = $data->data_fim;
}
// fill the form with data again
$this->form->setData($data);
// keep the search data in the session
TSession::setValue('filter_data', $filters);
return $filters;
}
public function onGenerate($format)
{
try
{
$filters = $this->getFilters();
// open a transaction with database 'clinica'
TTransaction::open('clinica');
$dt_ini = $filters['data_ini'];
$dt_fim = $filters['data_fim'];
$sql = "create temporary table a (index (data))
SELECT count(id) qtd, date(dt_clinica) as data FROM movto_clinico
where dt_clinica between '$dt_ini' and '$dt_fim' group by dt_clinica;
create temporary table b (index (data))
SELECT count(id) cad, date(dt_ativacao) as data FROM pessoa
where dt_ativacao between '$dt_ini' and '$dt_fim' group by dt_ativacao;
create temporary table c (index (data))
SELECT count(id) age, date(start_time) as data FROM agendamento
where date(start_time) between '$dt_ini' and '$dt_fim' group by date(start_time);
create temporary table d (index (data))
SELECT sum(valorparcial) sum, date(dt_pagamento) as data FROM movto_financeiro
where dt_pagamento is not NULL and dt_pagamento between '$dt_ini' and '$dt_fim' group by dt_pagamento;
select a.*, b.cad, c.age, d.sum
from a left outer join b using (data) left outer join c using (data) left outer join d using (data)";
$conn = TTransaction::get();
$objects = $conn->query($sql);
// load the objects according to criteria
//$objects = $repository->load($criteria, FALSE);
if ($objects)
{
$widths = array(200,200,200,200,200,200,200,200);
switch ($format)
{
case 'html':
$tr = new TTableWriterHTML($widths);
break;
case 'xls':
$tr = new TTableWriterXLS($widths);
break;
case 'pdf':
$tr = new TTableWriterPDF($widths, 'L');
break;
case 'rtf':
if (!class_exists('PHPRtfLite_Autoloader'))
{
PHPRtfLite::registerAutoloader();
}
$tr = new TTableWriterRTF($widths, 'L');
break;
}
if (!empty($tr))
{
// create the document styles
$tr->addStyle('title', 'Helvetica', '10', 'B', '#000000', '#dbdbdb');
$tr->addStyle('datap', 'Arial', '10', '', '#333333', '#f0f0f0');
$tr->addStyle('datai', 'Arial', '10', '', '#333333', '#ffffff');
$tr->addStyle('header', 'Helvetica', '16', 'B', '#5a5a5a', '#6B6B6B');
$tr->addStyle('footer', 'Helvetica', '10', 'B', '#5a5a5a', '#A3A3A3');
$tr->addStyle('break', 'Helvetica', '10', 'B', '#ffffff', '#9a9a9a');
$tr->addStyle('total', 'Helvetica', '10', 'I', '#000000', '#c7c7c7');
$tr->addStyle('breakTotal', 'Helvetica', '10', 'I', '#000000', '#c6c8d0');
// add titles row
$tr->addRow();
$tr->addCell("Data", 'left', 'title');
$tr->addCell("Quantidade de retorno", 'left', 'title');
$tr->addCell("Pacientes novos", 'left', 'title');
$tr->addCell("Pessoas atendidas", 'left', 'title');
$tr->addCell("Valor recebido", 'left', 'title');
$grandTotal = [];
$breakTotal = [];
$breakValue = null;
$firstRow = true;
// controls the background filling
$colour = false;
while ($linha = $objects->fetch(PDO::FETCH_OBJ))
{
$style = $colour ? 'datap' : 'datai';
$firstRow = false;
$tr->addRow();
$tr->addCell($object->data, 'left', $style);
$tr->addCell($object->qtd, 'left', $style);
$tr->addCell($object->cad, 'left', $style);
$tr->addCell($object->age, 'left', $style);
$tr->addCell($object->sum, 'left', $style);
$colour = !$colour;
};
var_dump('ate aqui');
$file = 'report_'.uniqid().".{$format}";
// stores the file
if (!file_exists("app/output/{$file}") || is_writable("app/output/{$file}"))
{
$tr->save("app/output/{$file}");
}
else
{
throw new Exception(_t('Permission denied') . ': ' . "app/output/{$file}");
}
parent::openFile("app/output/{$file}");
// shows the success message
new TMessage('info', _t('Report generated. Please, enable popups'));
}
}
else
{
new TMessage('error', _t('No records found'));
}
// close the transaction
TTransaction::close();
}
catch (Exception $e) // in case of exception
{
// shows the exception error message
new TMessage('error', $e->getMessage());
// undo all pending operations
TTransaction::rollback();
}
}
public function onShow($param = null)
{
//<onShow>
//</onShow>
}
//</hideLine> <addUserFunctionsCode/>
//<userCustomFunctions>
//</userCustomFunctions>
}
?>

Pessoal já tentei de tudo e não consigo gerar o relatorio ... dá erro no for each

Comentários (3)