<?php
 

 
<?php
/**
 * Tabular Query Report
 *
 * @version    1.0
 * @package    samples
 * @subpackage tutor
 * @author     Pablo Dall'Oglio
 * @copyright  Copyright (c) 2006 Adianti Solutions Ltd. (http://www.adianti.com.br)
 * @license    http://www.adianti.com.br/framework-license
 */
class RelatorioApolice extends TPage
{
    private $form; // form
    
    /**
     * Class constructor
     * Creates the page and the registration form
     */
    function __construct()
    {
        parent::__construct();
        
        // creates the form
        $this->form = new BootstrapFormBuilder('form_RelatorioApolice_report');
        $this->form->setFormTitle( 'RELATÓRIO COMERCIAL-APÓLICE' );
        
        // create the form fields
        
        $data1      = new TDate('data1');
        $data2      = new TDate('data2');
        
        $row = $this->form->addFields( [new TLabel('Data Início *: ')],     [$data1],[new TLabel('Data Fim: *')],     [$data2] );
        $data1->setSize('100%');
        $data2->setSize('100%');
        
        
        $data1->addValidation('Data1 ', new TRequiredValidator);
        $data2->addValidation('Data2 ', new TRequiredValidator);
        
        $output_type  = new TRadioGroup('output_type');
        $this->form->addFields( [new TLabel('Formato:')],   [$output_type] );
        
        // define field properties
        $output_type->setUseButton();
        $options = ['html' =>'HTML', 'pdf' =>'PDF', 'rtf' =>'RTF', 'xls' =>'XLS'];
        $output_type->addItems($options);
        $output_type->setValue('pdf');
        $output_type->setLayout('horizontal');
        
        $this->form->addAction( 'Generate', new TAction(array($this, 'onGenerate')), 'fa:download blue');
        
        // wrap the page content using vertical box
        $vbox = new TVBox;
        $vbox->style = 'width: 100%';
        // $vbox->add(new TXMLBreadCrumb('menu.xml', __CLASS__));
        $vbox->add($this->form);
        
        parent::add($vbox);
    }
    /**
     * method onGenerate()
     * Executed whenever the user clicks at the generate button
     */
    function onGenerate()
    {
        try
        {
            // get the form data into an active record Customer
            $data = $this->form->getData();
            $this->form->setData($data);
            
            $format = $data->output_type;
            
            // open a transaction with database 'sgs'
            $source = TTransaction::open('sgs');
            
            // define the query
            $query = '   SELECT apolice.valor_premio as "valor_premio",
          apolice.data_inicio as "data_inicio",
          apolice.data_fim as "data_fim",
          apolice.tempo as "tempo",
          plano_seguro.designacao as "designacao",
          tomador.nome as "nome",
          apolice_segurado.referencia_apolice as "referencia_apolice"
     FROM apolice,
          plano_seguro,
          tomador,
          apolice_segurado
    WHERE apolice.codigo_plano = plano_seguro.codigo AND 
          apolice.codigo_tomador = tomador.codigo AND 
          apolice_segurado.codigo_apolice = apolice.codigo AND
          apolice.data_inicio BETWEEN :data1 AND :data2
';
            
            $filters = [];
            $filters['data1'] = $data->data1;
            $filters['data2'] = $data->data2;
            
            $data = TDatabase::getData($source, $query, null, $filters );
            
            if ($data)
            {
                $widths = [200,200,200,200,200,200,200];
                
                switch ($format)
                {
                    case 'html':
                        $table = new TTableWriterHTML($widths);
                        break;
                    case 'pdf':
                        $table = new TTableWriterPDF($widths);
                        break;
                    case 'rtf':
                        $table = new TTableWriterRTF($widths);
                        break;
                    case 'xls':
                        $table = new TTableWriterXLS($widths);
                        break;
                }
                
                if (!empty($table))
                {
                    // create the document styles
                    $table->addStyle('header', 'Helvetica', '16', 'B', '#ffffff', '#4B5D8E');
                    $table->addStyle('title',  'Helvetica', '10', 'B', '#ffffff', '#617FC3');
                    $table->addStyle('datap',  'Helvetica', '10', '',  '#000000', '#E3E3E3', 'LR');
                    $table->addStyle('datai',  'Helvetica', '10', '',  '#000000', '#ffffff', 'LR');
                    $table->addStyle('footer', 'Helvetica', '10', '',  '#2B2B2B', '#B4CAFF');
                    
                    $table->setHeaderCallback( function($table) {
                        $table->addRow();
                        $table->addCell('RELATÓRIO COMERCIAL', 'center', 'header', 7);
                        
                        $table->addRow();
                        $table->addCell('Tomador', 'left', 'title');
                        $table->addCell('Apólice', 'center', 'title');
                        $table->addCell('Plano', 'center', 'title');
                        $table->addCell('Duração', 'center', 'title');
                        $table->addCell('Início', 'center', 'title');
                        $table->addCell('Fim', 'center', 'title');
                        $table->addCell('Total', 'center', 'title');
                        
                       
                        
                    });
                    
                    $table->setFooterCallback( function($table) {
                        $table->addRow();
                        $table->addCell(date('Y-m-d h:i:s'), 'center', 'footer', 7);
                    });
                    
                    // controls the background filling
                    $colour= FALSE;
                    
                    // data rows
                    foreach ($data as $row)
                    {
                        $style = $colour ? 'datap' : 'datai';
                        
                        $table->addRow();
                        $table->addCell($row['nome'], 'left', $style);
                        $table->addCell($row['referencia_apolice'], 'center', $style);
                        $table->addCell($row['designacao'], 'left', $style);
                        $table->addCell($row['tempo'], 'center', $style);
                        $table->addCell($row['data_inicio'], 'center', $style);
                        $table->addCell($row['data_fim'], 'center', $style);
                        $table->addCell('Kz '.number_format($row['valor_premio'],2,',','.'), 'left', $style);
                  
                        
                        $colour = !$colour;
                    }
                    
                    $output = "app/output/tabular.{$format}";
                    
                    // stores the file
                    if (!file_exists($output) OR is_writable($output))
                    {
                        $table->save($output);
                        parent::openFile($output);
                    }
                    else
                    {
                        throw new Exception(_t('Permission denied') . ': ' . $output);
                    }
                    
                    // shows the success message
                    new TMessage('info', "Relatório gerado com sucesso.<br> <a href='$output'>Click aqui para download</a>");
                }
            }
            else
            {
                new TMessage('error', 'Nenhum dados encontrado');
            }
    
            // close the transaction
            TTransaction::close();
        }
        catch (Exception $e) // in case of exception
        {
            new TMessage('error', $e->getMessage());
            TTransaction::rollback();
        }
    }
}
?>