<?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>
}
?>