<?php
namespace App\Controller;

use App\Controller\AppController;
use Cake\Log\Log;
use Cake\ORM\TableRegistry;
use MedidasDispersao\MedidasDispersao;

require_once ROOT . DS . "vendor" . DS . "Zurubabel" . DS . "MedidasDispersao.php";

/**
 * ApacheIiScores Controller
 *
 * @property \App\Model\Table\ApacheIiScoresTable $ApacheIiScores
 */
class RelatoriosController extends AppController
{

    public function initialize()
    {
        parent::initialize();
        $this->loadComponent('RequestHandler');

        $this->loadComponent('RequestHandler');

        $this->loadComponent('Auth', [
            'authorize' => 'Controller',
        ]);

        $this->loadComponent('Permission');

        // View or Controller
        $this->set('title', 'Relatórios');

        $session = $this->request->session();

        $pacient_id = $session->read('selected-pacient');
        $this->set('pacient_id', $pacient_id);

        $unit_id = $session->read('selected-unit');
        $this->set('unit_id', $unit_id);

        $hospitalization_id = $session->read('selected-hospitalization');
        $this->set('hospitalization_id', $hospitalization_id);

        $date_before = $session->read('date_before');
        $date_after = $session->read('date_after');
        $this->set('date_before', $date_before);
        $this->set('date_after', $date_after);
    }

    public function isAuthorized($user)
    {
        $action = $this->request->params['action'];

        return true;
        return $this->Permission->hasPermission($user['role_name'], 'Relatorios', $action);
        //return parent::isAuthorized($user);
    }

    /**
     * Index method
     *
     * @return \Cake\Network\Response|null
     */
    public function index()
    {
        $hospitalizations_id = $this->request->session()->read('selected-hospitalization');

        // $this->paginate = [

        //     'contain' => ['Hospitalizations'],
        // ];
        $this->paginate = [
            'contain' => ['NasScores', 'ApacheIiScores', 'PreDeliricScores', 'SnapIiScores', 'SapsIiiScores', 'SofaEscores', 'CribScores'],
            'conditions' => ['Hospitalizations.id =' => $hospitalizations_id],

        ];
        $hospitalizations_table = TableRegistry::get('Hospitalizations');
        $scores = $this->paginate($hospitalizations_table);

        //$apacheIiScores = $this->paginate($this->ApacheIiScores);

        $this->set(compact('scores'));
        $this->set('_serialize', ['scores']);
    }

    public function dashboard()
    {
        $professionalTable = TableRegistry::get('Professionals');
        $session = $this->request->session();
        $report_access = [];

        $emergency = [1, 2, 3];
        $surgery_center = [4];
        $obstetric_center = [6];

        $professionals = $professionalTable->find()
            ->where(['Professionals.unit_id' => $session->read('selected-unit'),
                    'Professionals.user_id' => $session->read('Auth.User.id') ]) 
            ->contain(['Modules', 'Users', 'Sectors'])
            ->first();

            foreach ($professionals['modules'] as $module) {
                if ($module['_joinData']['privilege_level'] == 2) {
                    array_push($report_access, $module['name']);                
                }
            }

             foreach ($professionals['sectors'] as $sector) {
                if (in_array($sector['name'], $emergency)) {
                    array_push($report_access, "Emergência");                
                }

                if (in_array($sector['name'], $surgery_center)) {
                    array_push($report_access, "Centro Cirúrgico");                
                }

                if (in_array($sector['name'], $obstetric_center)) {
                    array_push($report_access, "Centro Obstétrico");                
                }
            }

            //$this->set('professionals', $professionals);
            $this->set(compact('professionals', 'report_access'));
            $this->set('_serialize', ['professionals', 'report_access']);
    }

    public function setDate($date_before = null, $date_after = null, $relatorio_type = null)
    {
        $this->autoRender = false;

        if ($this->request->is('Ajax'))
        {
            $session = $this->request->session();
            $relatorio_type = $this->request->query['relatorio_type'];
            $session->write('relatorio_type', $relatorio_type);

            if (isset($this->request->query['date_before']) && $this->request->query['date_before'] !== "")
            {
                $date_before = $this->request->query['date_before'];
                $date_before = new \DateTime($date_before);

                if ($relatorio_type === "Parcial")
                {
                    $date_before->modify('first day of this month')->setTime(0, 0, 0);
                }

                $session->write('date_before', $date_before);
            }

            if (isset($this->request->query['date_after']) && $this->request->query['date_after'] !== "")
            {
                $date_after = $this->request->query['date_after'];
                $date_after = new \DateTime($date_after);

                if ($relatorio_type === "Parcial")
                {
                    //ultimo dia do mes
                    $date_after->modify('last day of this month')->setTime(23, 59, 59);
                }

                $session->write('date_after', $date_after);
            }

            $this->response->body
                (
                json_encode(
                    [
                        "status" => 200,
                        "status_message" => "sucesso",
                        "date_before" => $date_before,
                        "date_after" => $date_after,
                    ])
            );
            return $this->response;
        }
    }

    public function view($id = null)
    {
        $patient_table = TableRegistry::get('Patients');
        $patient = $patient_table->get($id);
        $this->viewBuilder()->options([
            'pdfConfig' => [
                'orientation' => 'portrait',
                'filename' => 'Patient ' . $id . '.pdf',
            ],
        ]);
        $this->set('patient', $patient);
    }

    /**
     * Add method
     *
     * @return \Cake\Network\Response|void Redirects on successful add, renders view otherwise.
     */
    public function add()
    {
        $apacheIiScore = $this->ApacheIiScores->newEntity();

        if ($this->request->is('post'))
        {
            $apacheIiScore = $this->ApacheIiScores->patchEntity($apacheIiScore, $this->request->data);

            if ($this->ApacheIiScores->save($apacheIiScore))
            {
                $this->Flash->success(__('O registro foi salvo..'));
                return $this->redirect(['action' => 'index']);
            }
            else
            {
                Log::write('debug', $apacheIiScore);
                Log::write('debug', $apacheIiScore->errors());
                $this->Flash->error(__('O registro não foi salvo. Por favor, tente novamente.'));
            }
        }

        $hospitalizations = $this->ApacheIiScores->Hospitalizations->find('list', ['limit' => 200]);
        $this->set(compact('apacheIiScore', 'hospitalizations'));
        $this->set('_serialize', ['apacheIiScore']);
    }

    /**
     * Edit method
     *
     * @param string|null $id Apache Ii Score id.
     * @return \Cake\Network\Response|void Redirects on successful edit, renders view otherwise.
     * @throws \Cake\Network\Exception\NotFoundException When record not found.
     */
    public function edit($id = null)
    {
        $apacheIiScore = $this->ApacheIiScores->get($id, [
            'contain' => [],
        ]);

        if ($this->request->is(['patch', 'post', 'put']))
        {
            $apacheIiScore = $this->ApacheIiScores->patchEntity($apacheIiScore, $this->request->data);

            if ($this->ApacheIiScores->save($apacheIiScore))
            {
                $this->Flash->success(__('O registro foi salvo..'));
                return $this->redirect(['action' => 'index']);
            }
            else
            {
                $this->Flash->error(__('O registro não foi salvo. Por favor, tente novamente.'));
            }
        }

        $hospitalizations = $this->ApacheIiScores->Hospitalizations->find('list', ['limit' => 200]);
        $this->set(compact('apacheIiScore', 'hospitalizations'));
        $this->set('_serialize', ['apacheIiScore']);
    }

    /**
     * Delete method
     *
     * @param string|null $id Apache Ii Score id.
     * @return \Cake\Network\Response|null Redirects to index.
     * @throws \Cake\Datasource\Exception\RecordNotFoundException When record not found.
     */
    public function delete($id = null)
    {
        $this->request->allowMethod(['post', 'delete']);
        $apacheIiScore = $this->ApacheIiScores->get($id);

        if ($this->ApacheIiScores->delete($apacheIiScore))
        {
            $this->Flash->success(__('O registro foi deletado'));
        }
        else
        {
            $this->Flash->error(__('O registro não foi deletado. Por favor, tente novamente.'));
        }

        return $this->redirect(['action' => 'index']);
    }

    public function nutritionalAssessments()
    {
        $calc_obj = new MedidasDispersao();

        $session = $this->request->session();
        $date_before = $session->read('date_before');
        $date_after = $session->read('date_after');
        $data = [];
        $arr = [
            'patients_id' => [],
            'setores' => [],

            'cirurgica' => [
                'total_registros' => 0,
                'patients_id' => [],
                'nutrition_evolution' => 0,
                'nutrition_evolution_leve' => 0,
                'nutrition_evolution_moderada' => 0,
                'nutrition_evolution_grave' => 0,

                'media_indice_nutricional_arr' => [],
                'media_indice_nutricional' => 0,

                'variacao_indice_nutricional' => 0,

                'geb_calometria_indireta' => [],
                'geb_calometria_indireta_percent' => 0,
                'geb_calometria_indireta_circulatoria' => [],
                'geb_calometria_indireta_circulatoria_percent' => 0,

                'media_jejum' => [],

                'pacientes_atigiram_vet_75' => [
                    'arr' => [],
                    'total' => 0,
                    'percentual' => 0,
                ],

                'pacientes_atigiram_vet_100' => [
                    'arr' => [],
                    'total' => 0,
                    'percentual' => 0,
                ],

                'pacientes_nao_atigiram_vet_100' => [
                    'arr' => [],
                    'total' => 0,
                    'percentual' => 0,
                ],

                'pacientes_receberam_jejum' => [
                    'arr' => [],
                    'total' => 0,
                    'percentual' => 0,
                ],

                'pacientes_nao_receberam_jejum' => [
                    'arr' => [],
                    'total' => 0,
                    'percentual' => 0,
                ],

                'volume_energetico_programado' => [
                    'arr' => [],
                    'total' => 0,
                    'media' => 0,
                    'mediana' => 0,
                    'desvio_padrao' => 0,
                ],

                'volume_energetico_atingido' => [
                    'arr' => [],
                    'total' => 0,
                    'media' => 0,
                    'mediana' => 0,
                    'desvio_padrao' => 0,
                ],
                'motivo_nao_atingir_vet_pleno' => [
                    '0' => ['tot' => 0, 'percen' => 0],
                    '1' => ['tot' => 0, 'percen' => 0],
                    '2' => ['tot' => 0, 'percen' => 0],
                    '3' => ['tot' => 0, 'percen' => 0],
                    '4' => ['tot' => 0, 'percen' => 0],
                ],
                'motivo_de_jejum' => [
                    '0' => ['tot' => 0, 'percen' => 0],
                    '1' => ['tot' => 0, 'percen' => 0],
                    '2' => ['tot' => 0, 'percen' => 0],
                    '3' => ['tot' => 0, 'percen' => 0],
                    '4' => ['tot' => 0, 'percen' => 0],
                    '5' => ['tot' => 0, 'percen' => 0],
                    '6' => ['tot' => 0, 'percen' => 0],
                    '7' => ['tot' => 0, 'percen' => 0],
                    '8' => ['tot' => 0, 'percen' => 0],
                    '9' => ['tot' => 0, 'percen' => 0],
                    '10' => ['tot' => 0, 'percen' => 0],
                    '11' => ['tot' => 0, 'percen' => 0],
                    '12' => ['tot' => 0, 'percen' => 0],
                ],
                'arr_tempo_ausencia_nutricao' => [],
                'media_tempo_ausencia_nutricao' => 0,
            ],

            'clinica' => [
                'total_registros' => 0,
                'patients_id' => [],
                'nutrition_evolution' => 0,
                'nutrition_evolution_leve' => 0,
                'nutrition_evolution_moderada' => 0,
                'nutrition_evolution_grave' => 0,

                'media_indice_nutricional_arr' => [],
                'media_indice_nutricional' => 0,

                'variacao_indice_nutricional' => 0,

                'geb_calometria_indireta' => [],
                'geb_calometria_indireta_percent' => 0,
                'geb_calometria_indireta_circulatoria' => [],
                'geb_calometria_indireta_circulatoria_percent' => 0,

                'media_jejum' => [],

                'pacientes_atigiram_vet_75' => [
                    'arr' => [],
                    'total' => 0,
                    'percentual' => 0,
                ],

                'pacientes_atigiram_vet_100' => [
                    'arr' => [],
                    'total' => 0,
                    'percentual' => 0,
                ],

                'pacientes_nao_atigiram_vet_100' => [
                    'arr' => [],
                    'total' => 0,
                    'percentual' => 0,
                ],

                'pacientes_receberam_jejum' => [
                    'arr' => [],
                    'total' => 0,
                    'percentual' => 0,
                ],

                'pacientes_nao_receberam_jejum' => [
                    'arr' => [],
                    'total' => 0,
                    'percentual' => 0,
                ],

                'volume_energetico_programado' => [
                    'arr' => [],
                    'total' => 0,
                    'media' => 0,
                    'mediana' => 0,
                    'desvio_padrao' => 0,
                ],

                'volume_energetico_atingido' => [
                    'arr' => [],
                    'total' => 0,
                    'media' => 0,
                    'mediana' => 0,
                    'desvio_padrao' => 0,
                ],
                'motivo_nao_atingir_vet_pleno' => [
                    '0' => ['tot' => 0, 'percen' => 0],
                    '1' => ['tot' => 0, 'percen' => 0],
                    '2' => ['tot' => 0, 'percen' => 0],
                    '3' => ['tot' => 0, 'percen' => 0],
                    '4' => ['tot' => 0, 'percen' => 0],
                ],

                'motivo_de_jejum' => [
                    '0' => ['tot' => 0, 'percen' => 0],
                    '1' => ['tot' => 0, 'percen' => 0],
                    '2' => ['tot' => 0, 'percen' => 0],
                    '3' => ['tot' => 0, 'percen' => 0],
                    '4' => ['tot' => 0, 'percen' => 0],
                    '5' => ['tot' => 0, 'percen' => 0],
                    '6' => ['tot' => 0, 'percen' => 0],
                    '7' => ['tot' => 0, 'percen' => 0],
                    '8' => ['tot' => 0, 'percen' => 0],
                    '9' => ['tot' => 0, 'percen' => 0],
                    '10' => ['tot' => 0, 'percen' => 0],
                    '11' => ['tot' => 0, 'percen' => 0],
                    '12' => ['tot' => 0, 'percen' => 0],
                ],
                'arr_tempo_ausencia_nutricao' => [],
                'media_tempo_ausencia_nutricao' => 0,
            ],
            'geral' => [
                'total_registros' => 0,
                'patients_id' => [],
                'nutrition_evolution' => 0,
                'nutrition_evolution_leve' => 0,
                'nutrition_evolution_moderada' => 0,
                'nutrition_evolution_grave' => 0,

                'media_indice_nutricional_arr' => [],
                'media_indice_nutricional' => 0,

                'variacao_indice_nutricional' => 0,

                'geb_calometria_indireta' => [],
                'geb_calometria_indireta_percent' => 0,
                'geb_calometria_indireta_circulatoria' => [],
                'geb_calometria_indireta_circulatoria_percent' => 0,

                'media_jejum' => [],

                'pacientes_atigiram_vet_75' => [
                    'arr' => [],
                    'total' => 0,
                    'percentual' => 0,
                ],

                'pacientes_atigiram_vet_100' => [
                    'arr' => [],
                    'total' => 0,
                    'percentual' => 0,
                ],

                'pacientes_nao_atigiram_vet_100' => [
                    'arr' => [],
                    'total' => 0,
                    'percentual' => 0,
                ],

                'motivo_de_jejum' => [
                    '0' => ['tot' => 0, 'percen' => 0],
                    '1' => ['tot' => 0, 'percen' => 0],
                    '2' => ['tot' => 0, 'percen' => 0],
                    '3' => ['tot' => 0, 'percen' => 0],
                    '4' => ['tot' => 0, 'percen' => 0],
                    '5' => ['tot' => 0, 'percen' => 0],
                    '6' => ['tot' => 0, 'percen' => 0],
                    '7' => ['tot' => 0, 'percen' => 0],
                    '8' => ['tot' => 0, 'percen' => 0],
                    '9' => ['tot' => 0, 'percen' => 0],
                    '10' => ['tot' => 0, 'percen' => 0],
                    '11' => ['tot' => 0, 'percen' => 0],
                    '12' => ['tot' => 0, 'percen' => 0],
                ],

                'pacientes_receberam_jejum' => [
                    'arr' => [],
                    'total' => 0,
                    'percentual' => 0,
                ],

                'pacientes_nao_receberam_jejum' => [
                    'arr' => [],
                    'total' => 0,
                    'percentual' => 0,
                ],

                'volume_energetico_programado' => [
                    'arr' => [],
                    'total' => 0,
                    'media' => 0,
                    'mediana' => 0,
                    'desvio_padrao' => 0,
                ],

                'volume_energetico_atingido' => [
                    'arr' => [],
                    'total' => 0,
                    'media' => 0,
                    'mediana' => 0,
                    'desvio_padrao' => 0,
                ],
                'motivo_nao_atingir_vet_pleno' => [
                    '0' => ['tot' => 0, 'percen' => 0],
                    '1' => ['tot' => 0, 'percen' => 0],
                    '2' => ['tot' => 0, 'percen' => 0],
                    '3' => ['tot' => 0, 'percen' => 0],
                    '4' => ['tot' => 0, 'percen' => 0],
                ],
                'arr_tempo_ausencia_nutricao' => [],
                'media_tempo_ausencia_nutricao' => 0,
            ],
        ];
        $nutrition = [];
        $hospital_ids = [];
        $session = $this->request->session();
        $unit_id = $session->read('selected-unit');
        $cont = 0;

        $evolution = [
            'cirurgica' => [],
            'geral' => [],
            'clinica' => [],
        ];

        $evo = [
            'cirurgica' => [],
            'geral' => [],
            'clinica' => [],
        ];

        $hospitalizations_table = TableRegistry::get('Hospitalizations');
        $nutrition_table = TableRegistry::get('NutritionalAssessments');
        $sector_table = TableRegistry::get('Sectors');

        $hospital_result = $hospitalizations_table
            ->find()
            ->where(['Hospitalizations.unit_id' => $unit_id])
            ->toArray()
        ;

        $sector_result = $sector_table
            ->find()
            ->where(['Sectors.unit_id' => $unit_id])
            ->toArray()
        ;

        foreach ($sector_result as $key => $value)
        {
            $sector_id = $value['id'];

            $dummy_arr = [
                'name' => $value['name'],
                'sector_name' => $value['name'],
                'sector_id' => $value['id'],
                'cirurgica' => [
                    'patients_id' => [],
                    'nutrition_evolution' => 0,
                    'nutrition_evolution_leve' => 0,
                    'nutrition_evolution_moderada' => 0,
                    'nutrition_evolution_grave' => 0,

                    'media_indice_nutricional_arr' => [],
                    'media_indice_nutricional' => 0,

                    'variacao_indice_nutricional' => 0,

                    'geb_calometria_indireta' => [],
                    'geb_calometria_indireta_percent' => 0,
                    'geb_calometria_indireta_circulatoria' => [],
                    'geb_calometria_indireta_circulatoria_percent' => 0,

                    'media_jejum' => [],

                    'pacientes_atigiram_vet_75' => [
                        'arr' => [],
                        'total' => 0,
                        'percentual' => 0,
                    ],

                    'pacientes_atigiram_vet_100' => [
                        'arr' => [],
                        'total' => 0,
                        'percentual' => 0,
                    ],

                    'pacientes_nao_atigiram_vet_100' => [
                        'arr' => [],
                        'total' => 0,
                        'percentual' => 0,
                    ],

                    'pacientes_receberam_jejum' => [
                        'arr' => [],
                        'total' => 0,
                        'percentual' => 0,
                    ],

                    'pacientes_nao_receberam_jejum' => [
                        'arr' => [],
                        'total' => 0,
                        'percentual' => 0,
                    ],

                    'volume_energetico_programado' => [
                        'arr' => [],
                        'total' => 0,
                        'media' => 0,
                        'mediana' => 0,
                        'desvio_padrao' => 0,
                    ],

                    'volume_energetico_atingido' => [
                        'arr' => [],
                        'total' => 0,
                        'media' => 0,
                        'mediana' => 0,
                        'desvio_padrao' => 0,
                    ],

                ],

                'clinica' =>
                [
                    'patients_id' => [],
                    'nutrition_evolution' => 0,
                    'nutrition_evolution_leve' => 0,
                    'nutrition_evolution_moderada' => 0,
                    'nutrition_evolution_grave' => 0,

                    'media_indice_nutricional_arr' => [],
                    'media_indice_nutricional' => 0,

                    'variacao_indice_nutricional' => 0,

                    'geb_calometria_indireta' => [],
                    'geb_calometria_indireta_percent' => 0,
                    'geb_calometria_indireta_circulatoria' => [],
                    'geb_calometria_indireta_circulatoria_percent' => 0,

                    'media_jejum' => [],

                    'pacientes_atigiram_vet_75' => [
                        'arr' => [],
                        'total' => 0,
                        'percentual' => 0,
                    ],

                    'pacientes_atigiram_vet_100' => [
                        'arr' => [],
                        'total' => 0,
                        'percentual' => 0,
                    ],

                    'pacientes_nao_atigiram_vet_100' => [
                        'arr' => [],
                        'total' => 0,
                        'percentual' => 0,
                    ],

                    'pacientes_receberam_jejum' => [
                        'arr' => [],
                        'total' => 0,
                        'percentual' => 0,
                    ],

                    'pacientes_nao_receberam_jejum' => [
                        'arr' => [],
                        'total' => 0,
                        'percentual' => 0,
                    ],

                    'volume_energetico_programado' => [
                        'arr' => [],
                        'total' => 0,
                        'media' => 0,
                        'mediana' => 0,
                        'desvio_padrao' => 0,
                    ],

                    'volume_energetico_atingido' => [
                        'arr' => [],
                        'total' => 0,
                        'media' => 0,
                        'mediana' => 0,
                        'desvio_padrao' => 0,
                    ],

                ],
                'geral' => [
                    'patients_id' => [],
                    'nutrition_evolution' => 0,
                    'nutrition_evolution_leve' => 0,
                    'nutrition_evolution_moderada' => 0,
                    'nutrition_evolution_grave' => 0,

                    'media_indice_nutricional_arr' => [],
                    'media_indice_nutricional' => 0,

                    'variacao_indice_nutricional' => 0,

                    'geb_calometria_indireta' => [],
                    'geb_calometria_indireta_percent' => 0,
                    'geb_calometria_indireta_circulatoria' => [],
                    'geb_calometria_indireta_circulatoria_percent' => 0,

                    'media_jejum' => [],

                    'pacientes_atigiram_vet_75' => [
                        'arr' => [],
                        'total' => 0,
                        'percentual' => 0,
                    ],

                    'pacientes_atigiram_vet_100' => [
                        'arr' => [],
                        'total' => 0,
                        'percentual' => 0,
                    ],

                    'pacientes_nao_atigiram_vet_100' => [
                        'arr' => [],
                        'total' => 0,
                        'percentual' => 0,
                    ],

                    'motivo_de_jejum' => [
                        '0' => ['tot' => 0, 'percen' => 0],
                        '1' => ['tot' => 0, 'percen' => 0],
                        '2' => ['tot' => 0, 'percen' => 0],
                        '3' => ['tot' => 0, 'percen' => 0],
                        '4' => ['tot' => 0, 'percen' => 0],
                        '5' => ['tot' => 0, 'percen' => 0],
                        '6' => ['tot' => 0, 'percen' => 0],
                        '7' => ['tot' => 0, 'percen' => 0],
                        '8' => ['tot' => 0, 'percen' => 0],
                        '9' => ['tot' => 0, 'percen' => 0],
                        '10' => ['tot' => 0, 'percen' => 0],
                        '11' => ['tot' => 0, 'percen' => 0],
                        '12' => ['tot' => 0, 'percen' => 0],
                    ],

                    'pacientes_receberam_jejum' => [
                        'arr' => [],
                        'total' => 0,
                        'percentual' => 0,
                    ],

                    'pacientes_nao_receberam_jejum' => [
                        'arr' => [],
                        'total' => 0,
                        'percentual' => 0,
                    ],

                    'volume_energetico_programado' => [
                        'arr' => [],
                        'total' => 0,
                        'media' => 0,
                        'mediana' => 0,
                        'desvio_padrao' => 0,
                    ],

                    'volume_energetico_atingido' => [
                        'arr' => [],
                        'total' => 0,
                        'media' => 0,
                        'mediana' => 0,
                        'desvio_padrao' => 0,
                    ],
                ],
            ];
            $arr['setores'][$sector_id] = $dummy_arr;
            //array_push($arr['setores'], $);
        }

        //pega o id das internações separando por tipo
        foreach ($hospital_result as $key => $value)
        {
            if (strpos($value['type'], 'Clínica') !== false)
            {
                $hospital_ids['clinica'][] = $value['id'];
                array_push($arr['clinica']['patients_id'], $value['id']);
            }

            if (strpos($value['type'], 'Cirúr') !== false)
            {
                $hospital_ids['cirurgica'][] = $value['id'];
                array_push($arr['cirurgica']['patients_id'], $value['id']);
            }

            $hospital_ids['geral'][] = $value['id'];
            array_push($arr['geral']['patients_id'], $value['id']);

            //calcula total de pacientes
            if (!in_array($value['patient_id'], $arr['patients_id']))
            {
                array_push($arr['patients_id'], $value['patient_id']);
            }
        }

        $hospital_all = $hospitalizations_table
            ->find()
            ->contain(['NutritionalAssessments'])
            ->toArray();

        // $hospital_clinica = [];
        // $hospital_cirurgica = [];
        // $hospital_geral = [];

        // foreach ($hospital_all as $hospital)
        // {
        //     if (in_array($hospital['id'], $hospital_ids['clinica']))
        //     {
        //         array_push($hospital_clinica, $hospital);
        //     }
        //     else if (in_array($hospital['id'], $hospital_ids['cirurgica']))
        //     {
        //         array_push($hospital_cirurgica, $hospital);
        //     }
        //     else if (in_array($hospital['id'], $hospital_ids['geral']))
        //     {
        //         array_push($hospital_geral, $hospital);
        //     }
        // }

        $hospital_clinica = $hospitalizations_table
            ->find()
            ->contain(['NutritionalAssessments'])
            ->where(['Hospitalizations.id IN' => $hospital_ids['clinica']])
            ->toArray();
        $hospital_cirurgica = $hospitalizations_table
            ->find()
            ->contain(['NutritionalAssessments'])
            ->where(['Hospitalizations.id IN' => $hospital_ids['cirurgica']])
            ->toArray();
        $hospital_geral = $hospitalizations_table
            ->find()
            ->contain(['NutritionalAssessments'])
            ->where(['Hospitalizations.id IN' => $hospital_ids['geral']])
            ->toArray();

        $data['clinica'] =   $nutrition_table->getRelatorioData($hospital_clinica, $date_before, $date_after, $sector_result);
        $data['cirurgica'] = $nutrition_table->getRelatorioData($hospital_cirurgica, $date_before, $date_after, $sector_result);
        $data['geral'] =     $nutrition_table->getRelatorioData($hospital_geral, $date_before, $date_after, $sector_result);
        //$data['all'] =     $nutrition_table->getRelatorioDataBySector($hospital_all, $date_before, $date_after, $sector_result);
        // $data['all'] =     $nutrition_table->getRelatorioData($hospital_all, $date_before, $date_after, $sector_result);

        // $data['setores'] = ['geral'=>[], 'clinica'=>[], 'cirurgica'=>[]];
        // $data['setores']['clinica'] = $data['clinica']['setores'];
        // $data['setores']['geral'] = $data['geral']['setores'];
        // $data['setores']['cirurgica'] = $data['cirurgica']['setores'];

        //cada tipo de internação(clinica, cirurgica)
        foreach ($hospital_ids as $type => $typeArr)
        {
            $cont = 0;
            $nutrition[$type] = $nutrition_table
                ->find()
                ->contain(['Hospitalizations'])
                ->where([
                    'NutritionalAssessments.hospitalization_id IN' => $hospital_ids[$type],
                    'NutritionalAssessments.created >' => $date_before->format('Y-m-d'),
                    'NutritionalAssessments.created <' => $date_after->format('Y-m-d'),
                ])
                ->order(['NutritionalAssessments.created' => 'DESC'])
                ->toArray();

            foreach ($nutrition[$type] as $key => $value)
            {
                $cont++;
                $sector_id = null;

                if (isset($value['sector_id']))
                {
                    $sector_id = $value['sector_id'];
                }

                if ($sector_id)
                {
                    if (isset($arr['setores'][$sector_id][$type] ))
                    {
                        $arr['setores'][$sector_id][$type]['media_indice_nutricional'] += $value['nutric_score'];
                        array_push($arr['setores'][$sector_id][$type]['media_indice_nutricional_arr'], $value['nutric_score']);
                    }

                }

                if (isset($value['motivo_energia']))
                {
                    $motivo = $value['motivo_energia'];
                    if (isset($arr[$type]['motivo_nao_atingir_vet_pleno'][$motivo]['tot']))
                    {
                        $arr[$type]['motivo_nao_atingir_vet_pleno'][$motivo]['tot']++;
                    }
                    //$arr[$type]['motivo_nao_atingir_vet_pleno'][$motivo]['tot'] ++;
                }

                if (isset($value['motivo_de_jejum']))
                {
                    $motivo = $value['motivo_de_jejum'];
                    $arr[$type]['motivo_de_jejum'][$motivo]['tot']++;
                }

                $arr[$type]['media_indice_nutricional'] += $value['nutric_score'];
                array_push($arr[$type]['media_indice_nutricional_arr'], $value['nutric_score']);

                //calcula total de pacientes dependendo do tipo(clinico, geral, cirurgia)
                // if (!in_array($value['hospitalization']['patient_id'], $arr[$type]['patients_id']))
                // {
                //     array_push($arr[$type]['patients_id'], $value['hospitalization']['patient_id']);
                // }

                //calcula os pacientes que estão  em jejum
                if (isset($value['periodo_jejum_data']))
                {
                    //verifica se o id do paciente já está no array
                    if (!in_array($value['hospitalization']['patient_id'], $arr[$type]['pacientes_receberam_jejum']['arr']))
                    {
                        array_push($arr[$type]['pacientes_receberam_jejum']['arr'], $value['hospitalization']['patient_id']);
                        $arr[$type]['pacientes_receberam_jejum']['total']++;
                    }
                }
                else
                {
                    //verifica se o id do paciente já está no array
                    if (!in_array($value['hospitalization']['patient_id'], $arr[$type]['pacientes_nao_receberam_jejum']['arr']))
                    {
                        array_push($arr[$type]['pacientes_nao_receberam_jejum']['arr'], $value['hospitalization']['patient_id']);
                        $arr[$type]['pacientes_nao_receberam_jejum']['total']++;
                    }
                }

                //calcula vet
                if (isset($value['volume_energetico_programado']) &&
                    $value['volume_energetico_programado'] > 0 &&
                    isset($value['volume_energetico_atingido']))
                {
                    $vet_programado = $value['volume_energetico_programado'];
                    $vet_atingido = $value['volume_energetico_atingido'];

                    array_push($arr[$type]['volume_energetico_programado']['arr'], $vet_programado);
                    array_push($arr[$type]['volume_energetico_atingido']['arr'], $vet_atingido);

                    //verifica se o paciente atingiur 75% do VET
                    if ($vet_atingido >= ($vet_programado * 0.75))
                    {
                        //verifica se o id do paciente já está no array
                        if (!in_array($value['hospitalization']['patient_id'], $arr[$type]['pacientes_atigiram_vet_75']['arr']))
                        {
                            array_push($arr[$type]['pacientes_atigiram_vet_75']['arr'], $value['hospitalization']['patient_id']);
                            $arr[$type]['pacientes_atigiram_vet_75']['total']++;
                        }
                    }

                    //calcula vet 100
                    if ($vet_atingido == $vet_programado)
                    {
                        //verifica se o id do paciente já está no array
                        if (!in_array($value['hospitalization']['patient_id'], $arr[$type]['pacientes_atigiram_vet_100']['arr']))
                        {
                            array_push($arr[$type]['pacientes_atigiram_vet_100']['arr'], $value['hospitalization']['patient_id']);
                            $arr[$type]['pacientes_atigiram_vet_100']['total']++;
                        }
                    }
                    else
                    {
                        //verifica se o id do paciente já está no array
                        if (!in_array($value['hospitalization']['patient_id'], $arr[$type]['pacientes_nao_atigiram_vet_100']['arr']))
                        {
                            array_push($arr[$type]['pacientes_nao_atigiram_vet_100']['arr'], $value['hospitalization']['patient_id']);
                            $arr[$type]['pacientes_nao_atigiram_vet_100']['total']++;
                            if ($sector_id)
                            {
                                $arr['setores'][$sector_id][$type]['pacientes_nao_atigiram_vet_100']['total']++;
                            }
                        }
                    }
                }

                if ($value['nutric_score'] == 1)
                {
                    $evolution[$type] = $value['hospitalization_id'];
                }

                //cálcula a quantidade de pacientes que utilizaram o GEB
                if ($value['geb_type'] == 2)
                {
                    //calcula o valor absoluto do geb
                    if (in_array($value['hospitalization_id'], $arr[$type]['geb_calometria_indireta']) == false)
                    {
                        array_push($arr[$type]['geb_calometria_indireta'], $value['hospitalization_id']);
                    }

                    //$arr[$type]['geb_calometria_indireta']++;

                    //calcula o percentual do geb
                    $arr[$type]['geb_calometria_indireta_percent'] = $calc_obj->calculate_percentage(count($arr[$type]['geb_calometria_indireta']), count($arr['patients_id']));
                }
                elseif ($value['geb_type'] == 3)
                {

                    //calcula o valor absoluto do geb

                    //$arr[$type]['geb_calometria_indireta_circulatoria']++;
                    if (in_array($value['hospitalization_id'], $arr[$type]['geb_calometria_indireta_circulatoria']) == false)
                    {
                        array_push($arr[$type]['geb_calometria_indireta_circulatoria'], $value['hospitalization_id']);
                    }

                    //calcula o percentual do geb
                    $arr[$type]['geb_calometria_indireta_circulatoria_percent'] = $calc_obj->calculate_percentage(count($arr[$type]['geb_calometria_indireta_circulatoria']), count($arr['patients_id']));
                }

                //checa se o paciente evoluiu no quadro de nutric_score partindo do número 1
                /*
                 * como a função in_array não aceita arrays multidimensionais foi criado um
                 * array para receber o índice do array multidimensional
                 */
                $dummyArrEvolution[] = $evolution[$type];
                $dummyArrEvo[] = $evo[$type];

                if ((in_array($value['hospitalization_id'], $dummyArrEvo) == false) &&
                    (in_array($value['hospitalization_id'], $dummyArrEvolution)))
                {
                    if ($value['nutric_score'] > 1)
                    {
                        $arr[$type]['nutrition_evolution']++;
                        $evo[$type][] = $value['hospitalization_id'];
                    }
                }
            }

            //calcula a média do índice (total / quantidade)
            $arr[$type]['media_indice_nutricional'] = $calc_obj->calc_average($arr[$type]['media_indice_nutricional_arr']);
            $arr[$type]['variacao_indice_nutricional'] = $calc_obj->calc_variance($arr[$type]['media_indice_nutricional_arr']);
            if ($sector_id)
            {
                $arr[$sector_id][$type]['media_indice_nutricional'] = $calc_obj->calc_average($arr[$type]['media_indice_nutricional_arr']);
                $arr[$sector_id][$type]['variacao_indice_nutricional'] = $calc_obj->calc_variance($arr[$type]['media_indice_nutricional_arr']);
            }

            //calcula percentual do vet
            if (count($arr[$type]['patients_id']) > 0)
            {
                $tot_patient = count($arr[$type]['patients_id']);

                if ($arr[$type]['pacientes_atigiram_vet_100']['total'] > 0)
                {
                    $tot_patient_vet = $arr[$type]['pacientes_atigiram_vet_100']['total'];

                    if ($tot_patient_vet > 0 && $tot_patient > 0)
                    {
                        $arr[$type]['pacientes_atigiram_vet_100']['percentual'] = round(($tot_patient_vet * 100) / $tot_patient, 2);
                    }
                }

                if (isset($arr[$type]['pacientes_nao_atigiram_vet_100']['total']))
                {
                    $tot_patient_nao_vet = $arr[$type]['pacientes_nao_atigiram_vet_100']['total'];
                    $arr[$type]['pacientes_nao_atigiram_vet_100']['percentual'] = round(($tot_patient_nao_vet * 100) / $tot_patient, 2);
                }

                if (isset($arr[$type]['pacientes_atigiram_vet_75']['total']))
                {
                    $tot_patient_nao_vet = $arr[$type]['pacientes_atigiram_vet_75']['total'];
                    $arr[$type]['pacientes_atigiram_vet_75']['percentual'] = round(($tot_patient_nao_vet * 100) / $tot_patient, 2);
                }

                //calcula porcentagem de paciente com jejum
                if ($arr[$type]['pacientes_nao_receberam_jejum']['total'] > 0)
                {
                    $arr[$type]['pacientes_nao_receberam_jejum']['percentual'] = ($arr[$type]['pacientes_nao_receberam_jejum']['total'] * 100) / $tot_patient;
                    $arr[$type]['pacientes_nao_receberam_jejum']['percentual'] = round($arr[$type]['pacientes_nao_receberam_jejum']['percentual'], 2);
                }
            }
        }

        foreach ($arr['cirurgica']['motivo_de_jejum'] as $key => $value)
        {
            if ($arr['cirurgica']['total_registros'] > 0 && $value['tot'] > 0)
            {
                $arr['cirurgica']['motivo_de_jejum'][$key]['percen'] = ($value['tot'] * 100) / $arr['cirurgica']['total_registros'];
            }
        }

        $this->viewBuilder()->options([
            'pdfConfig' => [
                'orientation' => 'portrait',
                'filename' => 'Relatório de Avaliação Nutricional.pdf',
            ],
        ]);

        $this->set('nutrition', $arr);
        $this->set('sector_result', $sector_result);
        $this->set('data', $data);
        $this->set('hospital_result', $hospital_result);
        $this->set('hospital_ids', $hospital_ids);
    }

    public function bundleCateterVascular()
    {
        $hospital_ids = [];
        $session = $this->request->session();
        $unit_id = $session->read('selected-unit');
        $cont = 0;
        $data = null;

        //ultimo dia do mes
        $date_before = $session->read('date_before');
        $date_after = $session->read('date_after');
        //$date_after->modify('last day of this month')->setTime(23, 59, 59);

        $bundle_table = TableRegistry::get('Bundles');
        $hospitalization_table = TableRegistry::get('Hospitalizations');
        $hospitalizations = $hospitalization_table
            ->find('all', [
                'contain' => ["Bundles.ImmediateComplications"],
            ])
            ->where(['Hospitalizations.unit_id' => $unit_id, 'Hospitalizations.status <>' => 'obito'])
            ->orwhere(['Hospitalizations.unit_id' => $unit_id, 'Hospitalizations.status' => 'obito', 'Hospitalizations.data_alta_obito < ' => $date_after->format("Y-m-d")])
            ->toArray();

        $data = $bundle_table->getRelatorioData($hospitalizations, $date_before, $date_after);

        $this->viewBuilder()->options([
            'pdfConfig' => [
                'orientation' => 'portrait',
                'filename' => 'Relatório de Cateter Vascular.pdf',
            ],
        ]);

        $this->set('data', $data);
        $this->set('hospitalizations', $hospitalizations);
    }

    public function bundleCateterVesical()
    {

        $hospital_ids = [];
        $session = $this->request->session();
        $unit_id = $session->read('selected-unit');
        $cont = 0;
        $data = [];

        //ultimo dia do mes
        $date_before = $session->read('date_before');
        $date_after = $session->read('date_after');
        //$date_after->modify('last day of this month')->setTime(23, 59, 59);

        $bundle_table = TableRegistry::get('Bundles');
        $bundle_table = TableRegistry::get('ItuPreventionBundles');
        $hospitalization_table = TableRegistry::get('Hospitalizations');
        $hospitalizations = $hospitalization_table
            ->find('all')
            ->where(['Hospitalizations.unit_id' => $unit_id, 'Hospitalizations.status <>' => 'obito'])
            ->orwhere(['Hospitalizations.unit_id' => $unit_id, 'Hospitalizations.status' => 'obito', 'Hospitalizations.data_alta_obito < ' => $date_after->format("Y-m-d")])
            ->contain(["ItuPreventionBundles.ImmediateComplications", "Bundles.ImmediateComplications", "SupportiveMeasures"])
            ->toArray();

        $data = $bundle_table->getRelatorioData($hospitalizations, $date_before, $date_after);

        $this->viewBuilder()->options([
            'pdfConfig' => [
                'orientation' => 'portrait',
                'filename' => 'Relatório de Cateter Vesical.pdf',
            ],
        ]);

        $this->set('data', $data);
        $this->set('hospitalizations', $hospitalizations);
    }

    public function bundleCateterVesicalDiario()
    {
        $hospital_ids = [];
        $session = $this->request->session();
        $unit_id = $session->read('selected-unit');
        $cont = 0;
        $data = null;

        //ultimo dia do mes
        $date_before = $session->read('date_before');
        $date_after = $session->read('date_after');
        $date_after->modify('last day of this month')->setTime(23, 59, 59);

        //$bundle_table = TableRegistry::get('DailyItuPreventionBundles');
        $bundle_table = TableRegistry::get('DailyItuPreventionBundles');
        $hospitalization_table = TableRegistry::get('Hospitalizations');
        $hospitalizations = $hospitalization_table
            ->find('all')
            ->where(['Hospitalizations.unit_id' => $unit_id, 'Hospitalizations.status <>' => 'obito'])
            ->orwhere(['Hospitalizations.unit_id' => $unit_id, 'Hospitalizations.status' => 'obito', 'Hospitalizations.data_alta_obito < ' => $date_after->format("Y-m-d")])
            ->contain(["ItuPreventionBundles.DailyItuPreventionBundles", "Bundles.DailyBundles", "SupportiveMeasures"])
            ->toArray();

        $data = $bundle_table->getRelatorioData($hospitalizations, $date_before, $date_after);

        $this->viewBuilder()->options([
            'pdfConfig' => [
                'orientation' => 'portrait',
                'filename' => 'Relatório de Cateter Vesical Diario.pdf',
            ],
        ]);

        $this->set('data', $data);
        $this->set('hospitalizations', $hospitalizations);
    }

    public function bundleCateterVascularDiario()
    {
        $hospital_ids = [];
        $session = $this->request->session();
        $unit_id = $session->read('selected-unit');
        $cont = 0;
        $data = null;

        //primeiro dia do mes
        $date_before = $session->read('date_before');
        $date_before->modify('first day of this month')->setTime(0, 0, 0);

        //ultimo dia do mes
        $date_after = $session->read('date_after');
        $date_after->modify('last day of this month')->setTime(23, 59, 59);

        $bundle_table = TableRegistry::get('DailyBundles');
        $hospitalization_table = TableRegistry::get('Hospitalizations');
        $hospitalizations = $hospitalization_table
            ->find('all')
            ->where(['Hospitalizations.unit_id' => $unit_id, 'Hospitalizations.status <>' => 'obito'])
            ->orwhere([
                'Hospitalizations.unit_id' => $unit_id,
                'Hospitalizations.status' => 'obito',
                'Hospitalizations.data_alta_obito < ' => $date_after->format("Y-m-d"),
            ])
            ->contain(["Bundles.DailyBundles", "SupportiveMeasures"])
            ->toArray();

        $data = $bundle_table->getRelatorioData($hospitalizations, $date_before, $date_after);

        $this->viewBuilder()->options([
            'pdfConfig' => [
                'orientation' => 'portrait',
                'filename' => 'Relatório de Cateter Vesical Diario.pdf',
            ],
        ]);

        $this->set('data', $data);
    }

    public function supportiveMeasures()
    {
        $arr = [];
        $data = null;
        $session = $this->request->session();
        $date_before = $session->read('date_before');
        $date_after = $session->read('date_after');
        $unit_id = $session->read('selected-unit');
        $count_pacient = 0;
        //echo "unit_id = " . $unit_id;

        $supportive_measures_table = TableRegistry::get('supportiveMeasures');
        //primeiro dia do mes
        $date_before = $session->read('date_before');
        $date_before->modify('first day of this month')->setTime(0, 0, 0);

        //ultimo dia do mes
        $date_after = $session->read('date_after');
        $date_after->modify('last day of this month')->setTime(23, 59, 59);

        $hospitalization_table = TableRegistry::get('Hospitalizations');
        $hospitalizations = $hospitalization_table
            ->find('all')
            ->where(['Hospitalizations.unit_id' => $unit_id, 'Hospitalizations.status <>' => 'obito'])
            ->orwhere([
                'Hospitalizations.unit_id' => $unit_id,
                'Hospitalizations.status' => 'obito',
                'Hospitalizations.data_alta_obito < ' => $date_after->format("Y-m-d"),
            ])
            ->contain(["Patients", "SupportiveMeasures"])
            ->toArray();

        $data = $supportive_measures_table->getRelatorioData($hospitalizations, $date_before, $date_after, $unit_id);

        $this->set('data', $data);

        $this->viewBuilder()->options([
            'pdfConfig' => [
                'orientation' => 'portrait',
                'filename' => 'Relatório de medidas de suporte.pdf',
            ],
        ]);
    }

    public function listarFuncionario()
    {
    }

    public function indicadoresUnidade()
    {
        $data =
            [
            'dates' => [],
            'unit' => null,
        ];

        $hospitalization_table = TableRegistry::get('Hospitalizations');
        $indicator_table = TableRegistry::get('IndicatorReports');
        $unit_table = TableRegistry::get('Units');
        $session = $this->request->session();
        $unit_id = $session->read('selected-unit');
        $tot_de_leitos = 0;

        //$tot_de_leitos = $unit_table->getTotalDeLeitos($unit_id);

        $date_before = $session->read('date_before');
        $date_after = $session->read('date_after');
        $interval = new \DateInterval('P1M');
        $daterange = new \DatePeriod($date_before, $interval, $date_after);
        $data['daterange'] = $daterange;
        $month_name = [
            '01' => 'Jan',
            '02' => 'Fev',
            '03' => 'Mar',
            '04' => 'Abr',
            '05' => 'Mai',
            '06' => 'Jun',
            '07' => 'Jul',
            '08' => 'Ago',
            '09' => 'Set',
            '10' => 'Out',
            '11' => 'Nov',
            '12' => 'Dez',
        ];

        $data['unit'] = $unit_table->find()
            ->where(['Units.id' => $unit_id])
            ->contain(['IndicatorsUnits', 'Sectors'])
            ->first();

        foreach ($data['unit']['sectors'] as $sector)
        {
            //pega somente os leitos
            if ($sector['type_id'] !== 4)
            {
                $tot_de_leitos += $sector['limit_of_rooms'];
            }
        }

        $hospitalizations = $hospitalization_table->find('all', [
            'contain' => [],
            'conditions' => [
                'Hospitalizations.unit_id' => $unit_id,
                'Hospitalizations.date_of_hospitalization >\'' . $date_before->format("Y-m-d") . '\' and Hospitalizations.date_of_hospitalization < \'' . $date_after->format("Y/m/d") . '\'',
            ],
        ])
            ->toArray();


        foreach ($daterange as $date) //gera os dados do relatorio de acordo com o mes
        {
            //primeiro dia do mes
            $startOfMonth = new \DateTime($date->format('Y-m-d'));
            $startOfMonth->modify('first day of this month')->setTime(0, 0, 0);

            //ultimo dia do mes
            $endOfMonth = new \DateTime($date->format('Y-m-d'));
            $endOfMonth->modify('last day of this month')->setTime(23, 59, 59);

            $hospitalizations_period = $hospitalization_table->find('all', [
                'contain' => ["Patients", "HistoricPatientMovements", 'SupportiveMeasures', 'Surgeries'],
            ])
                ->where(['Hospitalizations.unit_id' => $unit_id, 'Hospitalizations.status' => 'internado'])
                ->orwhere(['Hospitalizations.unit_id' => $unit_id, 'Hospitalizations.status <>' => 'internado', 'Hospitalizations.data_alta_obito <=' => $endOfMonth->format("Y-m-d")])
                ->toArray();

            $data['dates'][$date->format("m")] = $hospitalization_table->getRelatorioData($hospitalizations_period, $startOfMonth, $endOfMonth, $tot_de_leitos);
            $data['dates'][$date->format("m")]['full_date'] = $date;

            $data['dates'][$date->format("m")]['indicators'] = $indicator_table->find('all')
                 ->where([
                        //'indicator_name' => 'total_de_internacoes',
                        'month' => $date->format('m'),
                        'year' => $date->format('Y'),
                        'unit_id' => $unit_id,
                    ]);
        }

        $this->viewBuilder()->options([
            'pdfConfig' => [
                'orientation' => 'landscape',
                'filename' => 'Indicadores da unidade.pdf',
            ],
        ]);
        $this->set('month_name', $month_name);
        $this->set('data', $data);
    }

    public function indicadoresTerapiaIntensiva()
    {
        $data =
            [
            'dates' => [],
            'unit' => null,
        ];

        $hospitalization_table = TableRegistry::get('Hospitalizations');
        $indicator_table = TableRegistry::get('IndicatorReports');
        $unit_table = TableRegistry::get('Units');
        $session = $this->request->session();
        $unit_id = $session->read('selected-unit');
        $tot_de_leitos = 0;

        //$tot_de_leitos = $unit_table->getTotalDeLeitos($unit_id);

        $date_before = $session->read('date_before');
        $date_after = $session->read('date_after');
        $interval = new \DateInterval('P1M');
        $daterange = new \DatePeriod($date_before, $interval, $date_after);
        $data['daterange'] = $daterange;
        $month_name = [
            '01' => 'Jan',
            '02' => 'Fev',
            '03' => 'Mar',
            '04' => 'Abr',
            '05' => 'Mai',
            '06' => 'Jun',
            '07' => 'Jul',
            '08' => 'Ago',
            '09' => 'Set',
            '10' => 'Out',
            '11' => 'Nov',
            '12' => 'Dez',
        ];

        $data['unit'] = $unit_table->find()
            ->where(['Units.id' => $unit_id])
            ->contain(['IndicatorsUnits', 'Sectors'])
            ->first();

        foreach ($data['unit']['sectors'] as $sector)
        {
            //pega somente os leitos
            if ($sector['type_id'] !== 4)
            {
                $tot_de_leitos += $sector['limit_of_rooms'];
            }
        }

        $hospitalizations = $hospitalization_table->find('all', [
            'contain' => [],
            'conditions' => [
                'Hospitalizations.unit_id' => $unit_id,
                'Hospitalizations.date_of_hospitalization >\'' . $date_before->format("Y-m-d") . '\' and Hospitalizations.date_of_hospitalization < \'' . $date_after->format("Y/m/d") . '\'',
            ],
        ])
            ->toArray();


        foreach ($daterange as $date) //gera os dados do relatorio de acordo com o mes
        {
            //primeiro dia do mes
            $startOfMonth = new \DateTime($date->format('Y-m-d') . ' -3 months');
            $startOfMonth->modify('first day of this month')->setTime(0, 0, 0);

            //ultimo dia do mes
            $endOfMonth = new \DateTime($date->format('Y-m-d') . ' -3 months');
            $endOfMonth->modify('last day of this month')->setTime(23, 59, 59);

            $hospitalizations_period = $hospitalization_table->find('all', [
                'contain' => ["Patients", "HistoricPatientMovements", 'SupportiveMeasures', 'Surgeries'],
            ])
                ->where(['Hospitalizations.unit_id' => $unit_id, 'Hospitalizations.status' => 'internado'])
                ->orwhere(['Hospitalizations.unit_id' => $unit_id, 'Hospitalizations.status <>' => 'internado', 'Hospitalizations.data_alta_obito <=' => $endOfMonth->format("Y-m-d")])
                ->toArray();

            $data['dates'][$date->format("m")] = $hospitalization_table->getRelatorioData($hospitalizations_period, $startOfMonth, $endOfMonth, $tot_de_leitos);
            $data['dates'][$date->format("m")]['full_date'] = $date;

            $data['dates'][$date->format("m")]['indicators'] = $indicator_table->find('all')
                 ->where([
                        //'indicator_name' => 'total_de_internacoes',
                        'month' => $date->format('m'),
                        'year' => $date->format('Y'),
                        'unit_id' => $unit_id,
                    ]);
        }

        $this->viewBuilder()->options([
            'pdfConfig' => [
                'orientation' => 'landscape',
                'filename' => 'Indicadores da unidade.pdf',
            ],
        ]);
        $this->set('month_name', $month_name);
        $this->set('data', $data);
    }

    public function indicadoresCentroCirurgico()
    {
        $data = [
            'dates' => [],
            'unit' => null,
        ];

        $hospitalization_table = TableRegistry::get('Hospitalizations');
        $surgeries_table = TableRegistry::get('Surgeries');
        $specialties_table = TableRegistry::get('Specialties');
        $anesthesia_table = TableRegistry::get('TypeOfAnesthesias');
        $unit_table = TableRegistry::get('Units');
        $session = $this->request->session();
        $unit_id = $session->read('selected-unit');
        $tot_de_leitos = 0;

        //$tot_de_leitos = $unit_table->getTotalDeLeitos($unit_id);

        $date_before = $session->read('date_before');
        $date_after = $session->read('date_after');
        $interval = new \DateInterval('P1M');
        $daterange = new \DatePeriod($date_before, $interval, $date_after);
        $data['daterange'] = $daterange;
        $month_name = [
            '01' => 'Jan',
            '02' => 'Fev',
            '03' => 'Mar',
            '04' => 'Abr',
            '05' => 'Mai',
            '06' => 'Jun',
            '07' => 'Jul',
            '08' => 'Ago',
            '09' => 'Set',
            '10' => 'Out',
            '11' => 'Nov',
            '12' => 'Dez',
        ];

        $data['unit'] = $unit_table->find()
            ->where(['Units.id' => $unit_id])
            ->contain(['IndicatorsUnits', 'Sectors'])
            ->first();

        $specialties = $specialties_table
            ->find('all')
            ->toArray();

        $anesthesias = $anesthesia_table
            ->find('all')
            ->toArray();

        foreach ($data['unit']['sectors'] as $sector)
        {
            //pega somente os leitos
            if ($sector['type_id'] >= 12 && $sector['type_id'] <= 17)
            {
                $tot_de_leitos += $sector['limit_of_rooms'];
            }
        }

        $hospitalizations = $hospitalization_table->find('all', [
            'contain' => [],
            'conditions' => [
                'Hospitalizations.unit_id' => $unit_id,
                'Hospitalizations.date_of_hospitalization >\'' . $date_before->format("Y-m-d") . '\' and Hospitalizations.date_of_hospitalization < \'' . $date_after->format("Y/m/d") . '\'',
            ],
        ])
            ->toArray();

        foreach ($daterange as $date) //gera os dados do relatorio de acordo com o mes
        {
            //primeiro dia do mes
            $startOfMonth = new \DateTime($date->format('Y-m-d'));
            $startOfMonth->modify('first day of this month')->setTime(0, 0, 0);

            //ultimo dia do mes
            $endOfMonth = new \DateTime($date->format('Y-m-d'));
            $endOfMonth->modify('last day of this month')->setTime(23, 59, 59);

            $hospitalizations_period = $hospitalization_table->find('all', [])
                //'contain' => ["Patients", "HistoricPatientMovements", 'SupportiveMeasures', 'Surgeries'],])
                ->contain(["Patients", "Infections", "Surgeries" => ["TypeOfAnesthesias", 'Specialties']])
                ->where(['Hospitalizations.unit_id' => $unit_id, 'Hospitalizations.status' => 'internado'])
                ->orwhere([
                    'Hospitalizations.unit_id' => $unit_id, 'Hospitalizations.status <>' => 'internado',
                    'Hospitalizations.data_alta_obito <=' => $endOfMonth->format("Y-m-d")])
                ->toArray();

            //$data['dates'][$date->format("m")] = $hospitalization_table->getRelatorioData($hospitalizations_period, $startOfMonth, $endOfMonth, $tot_de_leitos);
            $data['dates'][$date->format("m")] = $surgeries_table->getRelatorioData($hospitalizations_period, $startOfMonth, $endOfMonth, $anesthesias, $specialties);
        }

        $this->viewBuilder()->options([
            'pdfConfig' => [
                'orientation' => 'landscape',
                'filename' => 'Indicadores da terapia intensiva.pdf',
            ],
        ]);
        $this->set('month_name', $month_name);
        $this->set('data', $data);
        //$this->set('hospitalizations', $hospitalizations);
    }

    public function indicadoresUnidadeInternacao()
    {
        $data =
            [
            'dates' => [],
            'unit' => null,
        ];

        $hospitalization_table = TableRegistry::get('Hospitalizations');
        $indicator_table = TableRegistry::get('IndicatorReports');
        $unit_table = TableRegistry::get('Units');
        $session = $this->request->session();
        $unit_id = $session->read('selected-unit');
        $tot_de_leitos = 0;

        //$tot_de_leitos = $unit_table->getTotalDeLeitos($unit_id);

        $date_before = $session->read('date_before');
        $date_after = $session->read('date_after');
        $interval = new \DateInterval('P1M');
        $daterange = new \DatePeriod($date_before, $interval, $date_after);
        $data['daterange'] = $daterange;
        $month_name = [
            '01' => 'Jan',
            '02' => 'Fev',
            '03' => 'Mar',
            '04' => 'Abr',
            '05' => 'Mai',
            '06' => 'Jun',
            '07' => 'Jul',
            '08' => 'Ago',
            '09' => 'Set',
            '10' => 'Out',
            '11' => 'Nov',
            '12' => 'Dez',
        ];

        $data['unit'] = $unit_table->find()
            ->where(['Units.id' => $unit_id])
            ->contain(['IndicatorsUnits', 'Sectors'])
            ->first();

        foreach ($data['unit']['sectors'] as $sector)
        {
            //pega somente os leitos
            if ($sector['type_id'] !== 4)
            {
                $tot_de_leitos += $sector['limit_of_rooms'];
            }
        }

        $hospitalizations = $hospitalization_table->find('all', [
            'contain' => [],
            'conditions' => [
                'Hospitalizations.unit_id' => $unit_id,
                'Hospitalizations.date_of_hospitalization >\'' . $date_before->format("Y-m-d") . '\' and Hospitalizations.date_of_hospitalization < \'' . $date_after->format("Y/m/d") . '\'',
            ],
        ])
            ->toArray();


        foreach ($daterange as $date) //gera os dados do relatorio de acordo com o mes
        {
            //primeiro dia do mes
            $startOfMonth = new \DateTime($date->format('Y-m-d'));
            $startOfMonth->modify('first day of this month')->setTime(0, 0, 0);

            //ultimo dia do mes
            $endOfMonth = new \DateTime($date->format('Y-m-d'));
            $endOfMonth->modify('last day of this month')->setTime(23, 59, 59);

            $hospitalizations_period = $hospitalization_table->find('all', [
                'contain' => ["Patients", "HistoricPatientMovements", 'SupportiveMeasures', 'Surgeries'],
            ])
                ->where(['Hospitalizations.unit_id' => $unit_id, 'Hospitalizations.status' => 'internado'])
                ->orwhere(['Hospitalizations.unit_id' => $unit_id, 'Hospitalizations.status <>' => 'internado', 'Hospitalizations.data_alta_obito <=' => $endOfMonth->format("Y-m-d")])
                ->toArray();

            $data['dates'][$date->format("m")] = $hospitalization_table->getRelatorioData($hospitalizations_period, $date_before, $date_after, $tot_de_leitos);
            $data['dates'][$date->format("m")]['full_date'] = $date;

            $data['dates'][$date->format("m")]['indicators'] = $indicator_table->find('all')
                 ->where([
                        //'indicator_name' => 'total_de_internacoes',
                        'month' => $date->format('m'),
                        'year' => $date->format('Y'),
                        'unit_id' => $unit_id,
                    ]);
        }

        $this->viewBuilder()->options([
            'pdfConfig' => [
                'orientation' => 'landscape',
                'filename' => 'Indicadores da unidade de internação.pdf',
            ],
        ]);
        $this->set('month_name', $month_name);
        $this->set('data', $data);
    }

    public function indicadoresEmergencia()
    {
        $data = [
            'dates' => [],
            'unit' => null,
        ];

        $hospitalization_table = TableRegistry::get('Hospitalizations');
        $unit_table = TableRegistry::get('Units');
        $session = $this->request->session();
        $unit_id = $session->read('selected-unit');
        $tot_de_leitos = 0;

        //$tot_de_leitos = $unit_table->getTotalDeLeitos($unit_id);

        $date_before = $session->read('date_before');
        $date_after = $session->read('date_after');
        $interval = new \DateInterval('P1M');
        $daterange = new \DatePeriod($date_before, $interval, $date_after);
        $data['daterange'] = $daterange;
        $month_name = [
            '01' => 'Jan',
            '02' => 'Fev',
            '03' => 'Mar',
            '04' => 'Abr',
            '05' => 'Mai',
            '06' => 'Jun',
            '07' => 'Jul',
            '08' => 'Ago',
            '09' => 'Set',
            '10' => 'Out',
            '11' => 'Nov',
            '12' => 'Dez',
        ];

        $data['unit'] = $unit_table->find()
            ->where(['Units.id' => $unit_id])
            ->contain(['IndicatorsUnits', 'Sectors'])
            ->first();

        foreach ($data['unit']['sectors'] as $sector)
        {
            //pega somente os leitos
            if ($sector['type_id'] >= 12 && $sector['type_id'] <= 17)
            {
                $tot_de_leitos += $sector['limit_of_rooms'];
            }
        }

        $hospitalizations = $hospitalization_table->find('all', [
            'contain' => [],
            'conditions' => [
                'Hospitalizations.unit_id' => $unit_id,
                'Hospitalizations.date_of_hospitalization >\'' . $date_before->format("Y-m-d") . '\' and Hospitalizations.date_of_hospitalization < \'' . $date_after->format("Y/m/d") . '\'',
            ],
        ])
            ->toArray();

        //$data['data'] = $hospitalization_table->getRelatorioData($hospitalizations);

        foreach ($daterange as $date) //gera os dados do relatorio de acordo com o mes
        {
            //primeiro dia do mes
            $startOfMonth = new \DateTime($date->format('Y-m-d'));
            $startOfMonth->modify('first day of this month')->setTime(0, 0, 0);

            //ultimo dia do mes
            $endOfMonth = new \DateTime($date->format('Y-m-d'));
            $endOfMonth->modify('last day of this month')->setTime(23, 59, 59);

            $hospitalizations_period = $hospitalization_table->find('all', [
                'contain' => ["Patients", "HistoricPatientMovements", 'SupportiveMeasures', 'Surgeries'],
                // 'conditions' => [
                //     'Hospitalizations.unit_id' => $unit_id,
                //     'Hospitalizations.status' => 'obito',

                //     'Hospitalizations.data_alta_obito < \'' . $endOfMonth->format("Y-m-d") . '\'',
                // ],
            ])
                ->where(['Hospitalizations.unit_id' => $unit_id, 'Hospitalizations.status <>' => 'obito'])
                ->orwhere(['Hospitalizations.unit_id' => $unit_id, 'Hospitalizations.status' => 'obito', 'Hospitalizations.data_alta_obito < ' => $endOfMonth->format("Y-m-d")])

                ->toArray();

            $data['dates'][$date->format("m")] = $hospitalization_table->getRelatorioData($hospitalizations_period, $startOfMonth, $endOfMonth, $tot_de_leitos);
        }

        $this->viewBuilder()->options([
            'pdfConfig' => [
                'orientation' => 'landscape',
                'filename' => 'Indicadores da unidade.pdf',
            ],
        ]);
        $this->set('month_name', $month_name);
        $this->set('data', $data);
        //$this->set('hospitalizations', $hospitalizations);
    }

    public function indicadoresSetor()
    {
        $this->viewBuilder()->options([
            'pdfConfig' => [
                'orientation' => 'portrait',
                'filename' => 'Indicadores do setor de emergência.pdf',
            ],
        ]);
    }

    public function ccih()
    {
        $data2 = [];
        $session = $this->request->session();
        $date_before = $session->read('date_before');
        $date_after = $session->read('date_after');
        $unit_id = $session->read('selected-unit');
        $cid_names = ['Meningite'];
        $hospitalization_ids = []; //array  com o id de todas as internações
        $infections = [];
        //$cultures = [];
        $data = [
            'hospital' => [
                'total_de_registros' => 0,

                'gravidade_percentual' => 0,
                'gravidade_total' => 0,
                'gravidade_sepse' => 0,
                'gravidade_infeccao' => 0,
                'gravidade_choque' => 0,

                'classificacao_comunitaria' => 0,
                'classificacao_nosocomial' => 0,
                'classificacao_nosocomial_de' => 0,
                'sem_identificacao' => 0,
                'classificacao_percentual' => 0,

                'comprovacao_microbiologica_total' => 0,
                'comprovacao_microbiologica_percentual' => 0,

                'gram_positivo_total' => 0,
                'gram_positivo_percentual' => 0,

                'tab_gravidade_tipo' => //tipo de infecção
                [
                    'infeccao' =>
                    [
                        'meningite' => 0, 'encefalite' => 0, 'meningoencefalite' => 0, 'sinusite' => 0, 'mastoidite' => 0, 'otite' => 0, 'celulite' => 0, 'amigdalite' => 0, 'laringite' => 0, 'traqueite' => 0, 'pneumonia' => 0, 'endocardite' => 0, 'infeccao_corrente' => 0, 'mediastinite' => 0, 'gastroenterite' => 0, 'hepatite' => 0, 'pancreatite' => 0, 'peritonite' => 0, 'infeccao_urinaria' => 0, 'infeccao_pelvica' => 0, 'piodermite' => 0, 'foliculite' => 0, 'fasciite' => 0, 'ulcera_infectada' => 0, 'abcesso' => 0, 'ostemielite' => 0, 'artrite_septica' => 0, 'tuberculose_pulmonar' => 0, 'herpes_zoster' => 0, 'outros' => 0,
                    ],
                    'sepse' =>
                    [
                        'meningite' => 0, 'encefalite' => 0, 'meningoencefalite' => 0, 'sinusite' => 0, 'mastoidite' => 0, 'otite' => 0, 'celulite' => 0, 'amigdalite' => 0, 'laringite' => 0, 'traqueite' => 0, 'pneumonia' => 0, 'endocardite' => 0, 'infeccao_corrente' => 0, 'mediastinite' => 0, 'gastroenterite' => 0, 'hepatite' => 0, 'pancreatite' => 0, 'peritonite' => 0, 'infeccao_urinaria' => 0, 'infeccao_pelvica' => 0, 'piodermite' => 0, 'foliculite' => 0, 'fasciite' => 0, 'ulcera_infectada' => 0, 'abcesso' => 0, 'ostemielite' => 0, 'artrite_septica' => 0, 'tuberculose_pulmonar' => 0, 'herpes_zoster' => 0, 'outros' => 0,
                    ],
                    'choque' =>
                    [
                        'meningite' => 0, 'encefalite' => 0, 'meningoencefalite' => 0, 'sinusite' => 0, 'mastoidite' => 0, 'otite' => 0, 'celulite' => 0, 'amigdalite' => 0, 'laringite' => 0, 'traqueite' => 0, 'pneumonia' => 0, 'endocardite' => 0, 'infeccao_corrente' => 0, 'mediastinite' => 0, 'gastroenterite' => 0, 'hepatite' => 0, 'pancreatite' => 0, 'peritonite' => 0, 'infeccao_urinaria' => 0, 'infeccao_pelvica' => 0, 'piodermite' => 0, 'foliculite' => 0, 'fasciite' => 0, 'ulcera_infectada' => 0, 'abcesso' => 0, 'ostemielite' => 0, 'artrite_septica' => 0, 'tuberculose_pulmonar' => 0, 'herpes_zoster' => 0, 'outros' => 0,
                    ],
                ],

                'comprovacao_microbiologica_sitio' =>
                [
                    'meningite' => 0, 'encefalite' => 0, 'meningoencefalite' => 0, 'sinusite' => 0, 'mastoidite' => 0, 'otite' => 0, 'celulite' => 0, 'amigdalite' => 0, 'laringite' => 0, 'traqueite' => 0, 'pneumonia' => 0, 'endocardite' => 0, 'infeccao_corrente' => 0, 'mediastinite' => 0, 'gastroenterite' => 0, 'hepatite' => 0, 'pancreatite' => 0, 'peritonite' => 0, 'infeccao_urinaria' => 0, 'infeccao_pelvica' => 0, 'piodermite' => 0, 'foliculite' => 0, 'fasciite' => 0, 'ulcera_infectada' => 0, 'abcesso' => 0, 'ostemielite' => 0, 'artrite_septica' => 0, 'tuberculose_pulmonar' => 0, 'herpes_zoster' => 0, 'outros' => 0,
                ],

                'comprovacao_microbiologica_gravidade' =>
                [
                    'infeccao' => 0,
                    'sepse' => 0,
                    'choque' => 0,
                ],

                //TABELA ENTRE COLUNAS OS TIPOS DE INFECÇÕES E LINHA GRAM POSITIVO
                'tipo_infeccao_gram_positivo' =>
                [
                    'meningite' => 0, 'encefalite' => 0, 'meningoencefalite' => 0, 'sinusite' => 0, 'mastoidite' => 0, 'otite' => 0, 'celulite' => 0, 'amigdalite' => 0, 'laringite' => 0, 'traqueite' => 0, 'pneumonia' => 0, 'endocardite' => 0, 'infeccao_corrente' => 0, 'mediastinite' => 0, 'gastroenterite' => 0, 'hepatite' => 0, 'pancreatite' => 0, 'peritsonite' => 0, 'infeccao_urinaria' => 0, 'infeccao_pelvica' => 0, 'piodermite' => 0, 'foliculite' => 0, 'fasciite' => 0, 'ulcera_infectada' => 0, 'abcesso' => 0, 'ostemielite' => 0, 'artrite_septica' => 0, 'tuberculose_pulmonar' => 0, 'herpes_zoster' => 0, 'outros' => 0,
                ],

                //Tipo de infecção e característica de uso de ATB
                'tipo_infeccao_caracteristica_atb' =>
                [
                    'unico' => [
                        'meningite' => 0, 'encefalite' => 0, 'meningoencefalite' => 0, 'sinusite' => 0, 'mastoidite' => 0, 'otite' => 0, 'celulite' => 0, 'amigdalite' => 0, 'laringite' => 0, 'traqueite' => 0, 'pneumonia' => 0, 'endocardite' => 0, 'infeccao_corrente' => 0, 'mediastinite' => 0, 'gastroenterite' => 0, 'hepatite' => 0, 'pancreatite' => 0, 'peritsonite' => 0, 'infeccao_urinaria' => 0, 'infeccao_pelvica' => 0, 'piodermite' => 0, 'foliculite' => 0, 'fasciite' => 0, 'ulcera_infectada' => 0, 'abcesso' => 0, 'ostemielite' => 0, 'artrite_septica' => 0, 'tuberculose_pulmonar' => 0, 'herpes_zoster' => 0, 'outros' => 0,
                    ],
                    'dois_tratamentos' => [
                        'meningite' => 0, 'encefalite' => 0, 'meningoencefalite' => 0, 'sinusite' => 0, 'mastoidite' => 0, 'otite' => 0, 'celulite' => 0, 'amigdalite' => 0, 'laringite' => 0, 'traqueite' => 0, 'pneumonia' => 0, 'endocardite' => 0, 'infeccao_corrente' => 0, 'mediastinite' => 0, 'gastroenterite' => 0, 'hepatite' => 0, 'pancreatite' => 0, 'peritsonite' => 0, 'infeccao_urinaria' => 0, 'infeccao_pelvica' => 0, 'piodermite' => 0, 'foliculite' => 0, 'fasciite' => 0, 'ulcera_infectada' => 0, 'abcesso' => 0, 'ostemielite' => 0, 'artrite_septica' => 0, 'tuberculose_pulmonar' => 0, 'herpes_zoster' => 0, 'outros' => 0,
                    ],
                    'tratamento_<_7' => [
                        'meningite' => 0, 'encefalite' => 0, 'meningoencefalite' => 0, 'sinusite' => 0, 'mastoidite' => 0, 'otite' => 0, 'celulite' => 0, 'amigdalite' => 0, 'laringite' => 0, 'traqueite' => 0, 'pneumonia' => 0, 'endocardite' => 0, 'infeccao_corrente' => 0, 'mediastinite' => 0, 'gastroenterite' => 0, 'hepatite' => 0, 'pancreatite' => 0, 'peritsonite' => 0, 'infeccao_urinaria' => 0, 'infeccao_pelvica' => 0, 'piodermite' => 0, 'foliculite' => 0, 'fasciite' => 0, 'ulcera_infectada' => 0, 'abcesso' => 0, 'ostemielite' => 0, 'artrite_septica' => 0, 'tuberculose_pulmonar' => 0, 'herpes_zoster' => 0, 'outros' => 0,
                    ],
                    'tratamento_<_30' => [
                        'meningite' => 0, 'encefalite' => 0, 'meningoencefalite' => 0, 'sinusite' => 0, 'mastoidite' => 0, 'otite' => 0, 'celulite' => 0, 'amigdalite' => 0, 'laringite' => 0, 'traqueite' => 0, 'pneumonia' => 0, 'endocardite' => 0, 'infeccao_corrente' => 0, 'mediastinite' => 0, 'gastroenterite' => 0, 'hepatite' => 0, 'pancreatite' => 0, 'peritsonite' => 0, 'infeccao_urinaria' => 0, 'infeccao_pelvica' => 0, 'piodermite' => 0, 'foliculite' => 0, 'fasciite' => 0, 'ulcera_infectada' => 0, 'abcesso' => 0, 'ostemielite' => 0, 'artrite_septica' => 0, 'tuberculose_pulmonar' => 0, 'herpes_zoster' => 0, 'outros' => 0,
                    ],
                ],
            ],
        ];
        $risk_data = [
            'arr_pacientes_penumonia' => [],
            'tot_pacientes_penumonia' => 0,
        ];
        $supprotive_measures_data = [
            'ventilacao_mecanica' => [
                'arr_paciente_ventilacao' => [],
                'tot_paciente_ventilacao' => 0,

                'arr_paciente_invasiva' => [],
                'tot_paciente_invasiva' => 0,

                'arr_paciente_nao_invasiva' => [],
                'tot_paciente_nao_invasiva' => 0,

                'paciente_ventilacao_dia' => 0, //PACIENTES SOB VENTILAÇÃO MECÂNICA DIA
            ],

            'densidade_tqt' => [
                'arr_paciente_ids' => [],
                'total_pacientes' => 0,
                'arr_tempo_permanencia' => [],
                'tempo_medio_permanencia' => 0,
                'densidade_por_dia' => 0,
                'percen_p_vent_meca_invasiva' => 0,
                'percen_p_vent_meca_nao_invasiva' => 0,
            ],
        ];

        $diagnostic_table = TableRegistry::get('Diagnostics');
        $diagnostic = $diagnostic_table->find()
            ->where(['Diagnostics.diagnostic_date >= ' => $date_before->format('Y-m-d'), 'Diagnostics.diagnostic_date <=' => $date_after->format('Y-m-d')])
            ->orWhere(['Diagnostics.description LIKE' => '%(A%%)%'])
            ->orWhere(['Diagnostics.description LIKE' => '%Meningite%'])
            ->orWhere(['Diagnostics.description LIKE' => '%Encefalite%'])
            ->orWhere(['Diagnostics.description LIKE' => '%Meningoencefalite%'])
            ->orWhere(['Diagnostics.description LIKE' => '%Mastoidite%'])
            ->orWhere(['Diagnostics.description LIKE' => '%Otite%'])
            ->orWhere(['Diagnostics.description LIKE' => '%Sinusite%'])
            ->orWhere(['Diagnostics.description LIKE' => '%Celulite%'])
            ->orWhere(['Diagnostics.description LIKE' => '%Amigdalite%'])
            ->orWhere(['Diagnostics.description LIKE' => '%Laringite%'])
            ->orWhere(['Diagnostics.description LIKE' => '%TraqueÍte%'])
            ->orWhere(['Diagnostics.description LIKE' => '%Pneumonia%'])
            ->orWhere(['Diagnostics.description LIKE' => '%Endocardite%'])
            ->orWhere(['Diagnostics.description LIKE' => '%Infecção de Corrente sanguínea%'])
            ->orWhere(['Diagnostics.description LIKE' => '%Mediastinite%'])
            ->orWhere(['Diagnostics.description LIKE' => '%Gastroenterite%'])
            ->orWhere(['Diagnostics.description LIKE' => '%Hepatite%'])
            ->orWhere(['Diagnostics.description LIKE' => '%Pancreatite%'])
            ->orWhere(['Diagnostics.description LIKE' => '%Peritonite%'])
            ->orWhere(['Diagnostics.description LIKE' => '%Infecção urinária%'])
            ->orWhere(['Diagnostics.description LIKE' => '%Infecção pélvica%'])
            ->orWhere(['Diagnostics.description LIKE' => '%Piodermite%'])
            ->orWhere(['Diagnostics.description LIKE' => '%Foliculite%'])
            ->orWhere(['Diagnostics.description LIKE' => '%Fasciíte%'])
            ->orWhere(['Diagnostics.description LIKE' => '%Úlcera Infectada%'])
            ->orWhere(['Diagnostics.description LIKE' => '%Abcesso'])
            ->orWhere(['Diagnostics.description LIKE' => '%Ostemielite%'])
            ->orWhere(['Diagnostics.description LIKE' => '%Artrite séptica%'])
            ->orWhere(['Diagnostics.description LIKE' => '%Tuberculose Pulmonar%'])
            ->orWhere(['Diagnostics.description LIKE' => '%Herpes Zoster%'])
            ->orWhere(['Diagnostics.description LIKE' => '%Outros%'])
            ->toArray()
        ;
        $hospitalizations_table = TableRegistry::get('Hospitalizations');
        $infections_table = TableRegistry::get('Infections');
        //$cultures_table = TableRegistry::get('Cultures');

        $hospitalizations = $hospitalizations_table
            ->find('all')
            ->contain(['Cultures', 'Infections.Cultures.Medications', 'Diagnostics', 'SepseProtocols', 'SupportiveMeasures'])
            ->where(['Hospitalizations.unit_id' => $unit_id, 'Hospitalizations.status <>' => 'obito'])
            ->orwhere(['Hospitalizations.unit_id' => $unit_id, 'Hospitalizations.status' => 'obito', 'Hospitalizations.data_alta_obito < ' => $date_after->format("Y-m-d")]);

        foreach ($hospitalizations as $key => $hospitalization)
        {
            array_push($hospitalization_ids, $hospitalization->id);

            //total de tratamentos por internação
            $count_infeccao_atb = [
                'meningite' => 0, 'encefalite' => 0, 'meningoencefalite' => 0, 'sinusite' => 0, 'mastoidite' => 0, 'otite' => 0, 'celulite' => 0, 'amigdalite' => 0, 'laringite' => 0, 'traqueite' => 0, 'pneumonia' => 0, 'endocardite' => 0, 'infeccao_corrente' => 0, 'mediastinite' => 0, 'gastroenterite' => 0, 'hepatite' => 0, 'pancreatite' => 0, 'peritsonite' => 0, 'infeccao_urinaria' => 0, 'infeccao_pelvica' => 0, 'piodermite' => 0, 'foliculite' => 0, 'fasciite' => 0, 'ulcera_infectada' => 0, 'abcesso' => 0, 'ostemielite' => 0, 'artrite_septica' => 0, 'tuberculose_pulmonar' => 0, 'herpes_zoster' => 0, 'outros' => 0,
            ];

            // foreach ($hospitalization['identification_of_risks'] as $measure)
            // {
            // }
            foreach ($hospitalization['supportive_measures'] as $measure)
            {
                if (!isset($measure['data_inicio']))
                {
                    continue;
                }

                $measure['data_inicio'] = new \Datetime($measure['data_inicio']->format('Y-m-d'));

                if ($measure['data_inicio'] >= $date_before &&
                    $measure['data_inicio'] <= $date_after)
                {
                    if (strpos($measure['type'], 'Ventilação') !== false)
                    {
                        $supprotive_measures_data['ventilacao_mecanica']['paciente_ventilacao_dia'] += $measure['dias_de_uso'];

                        if (!in_array($hospitalization->id, $supprotive_measures_data['ventilacao_mecanica']['arr_paciente_ventilacao']))
                        {
                            array_push($supprotive_measures_data['ventilacao_mecanica']['arr_paciente_ventilacao'], $hospitalization->id);
                        }
                    }
                }
            }

            foreach ($hospitalization['infections'] as $infection)
            {
                foreach ($infection['cultures'] as $culture)
                {
                    foreach ($culture['medications'] as $medication)
                    {
                        $infection_type = null;
                        $days = 0;
                        if (isset($medication['start_date']) &&
                            isset($medication['end_date']))
                        {
                            $start_date = new \DateTime($medication['start_date']->format('Y-m-d'));
                            $end_date = new \DateTime($medication['end_date']->format('Y-m-d'));
                            $days = $start_date->diff($end_date)->days;
                        }

                        //Tipo de infecção e característica de uso de ATB
                        switch ($infection['type'])
                        {
                            case 0:
                                $infection_type = 'meningite';
                                break;
                            case 1:
                                $infection_type = 'encefalite';
                                break;
                            case 2:
                                $infection_type = 'meningoencefalite';
                                break;
                            case 3:
                                $infection_type = 'sinusite';
                                break;
                            case 4:
                                $infection_type = 'mastoidite';
                                break;
                            case 5:
                                $infection_type = 'otite';
                                break;
                            case 6:
                                $infection_type = 'celulite';
                                break;
                            case 7:
                                $infection_type = 'amigdalite';
                                break;
                            case 8:
                                $infection_type = 'laringite';
                                break;
                            case 9:
                                $infection_type = 'traqueite';
                                break;
                            case 10:
                                $infection_type = 'pneumonia';
                                break;
                            case 11:
                                $infection_type = 'endocardite';
                                break;
                            case 12:
                                $infection_type = 'infeccao_corrente';
                                break;
                            case 13:
                                $infection_type = 'mediastinite';
                                break;
                            case 14:
                                $infection_type = 'gastroenterite';
                                break;
                            case 15:
                                $infection_type = 'hepatite';
                                break;
                            case 16:
                                $infection_type = 'pancreatite';
                                break;
                            case 17:
                                $infection_type = 'peritonite';
                                break;
                            case 18:
                                $infection_type = 'infeccao_urinaria';
                                break;
                            case 19:
                                $infection_type = 'infeccao_pelvica';
                                break;
                            case 20:
                                $infection_type = 'piodermite';
                                break;
                            case 21:
                                $infection_type = 'foliculite';
                                break;
                            case 22:
                                $infection_type = 'fasciite';
                                break;
                            case 23:
                                $infection_type = 'ulcera_infectada';
                                break;
                            case 24:
                                $infection_type = 'abcesso';
                                break;
                            case 25:
                                $infection_type = 'ostemielite';
                                break;
                            case 26:
                                $infection_type = 'artrite_septica';
                                break;
                            case 27:
                                $infection_type = 'tuberculose_pulmonar';
                                break;
                            case 28:
                                $infection_type = 'herpes_zoster';
                                break;
                            case 29:
                                $infection_type = 'outros';
                                break;
                        }

                        if ($infection_type && isset($data['tipo_infeccao_caracteristica_atb']) )
                        {
                            $data['tipo_infeccao_caracteristica_atb']['unico'][$infection_type]++;
                            $count_infeccao_atb[$infection_type]++;

                            if ($count_infeccao_atb[$infection_type] > 1)
                            {
                                $data['tipo_infeccao_caracteristica_atb']['dois_tratamentos'][$infection_type]++;
                            }

                            if ($days <= 7)
                            {
                                $data['tipo_infeccao_caracteristica_atb']['tratamento_<_7'][$infection_type]++;
                            }
                            else if ($days <= 30)
                            {
                                $data['tipo_infeccao_caracteristica_atb']['tratamento_<_30'][$infection_type]++;
                            }
                        }
                    }
                }
            }
        }

        if (!empty($hospitalization_ids))
        {
            $infections = $infections_table->find('all', [
                'contain' => ["Cultures.Medications", "Hospitalizations.Surgeries"],
                'conditions' => ['Infections.hospitalization_id in ' => $hospitalization_ids,
                ],
            ]);
        }

        $data2 = $infections_table->getRelatorioData($hospitalizations, $diagnostic, $date_before, $date_after);

        foreach ($infections as $key => $infection)
        {
            $gravidade_infeccao = null;
            $data['hospital']['total_de_registros']++;

            if ($infection['gravity'] == 0) //GRAVIDADE DAS INFECÇÕES: INFECÇÃO, SEPSE E CHOQUE SÉPTICO NO HOSPITAL
            {
                $data['hospital']['gravidade_infeccao']++;
                $gravidade_infeccao = "infeccao";
            }
            elseif ($infection['gravity'] == 1)
            {
                $data['hospital']['gravidade_sepse']++;
                $gravidade_infeccao = "sepse";
            }
            elseif ($infection['gravity'] == 2)
            {
                $data['hospital']['gravidade_choque']++;
                $gravidade_infeccao = "choque";
            }

            if ($infection['classifcation'] == 0) //TIPO DE INFECÇÕES: COMUNITÁRIA, NOSOCOMIAL, NOSOCOMIAL OUTRA INSTITUIÇÃO OU SEM IDENTIFICAÇÃO NO HOSPITAL
            {
                $data['hospital']['classificacao_comunitaria']++;
            }
            elseif ($infection['classifcation'] == 1)
            {
                $data['hospital']['classificacao_nosocomial']++;
            }
            elseif ($infection['classifcation'] == 2)
            {
                $data['hospital']['classificacao_nosocomial_de']++;
            }
            elseif ($infection['classifcation'] == 3)
            {
                $data['hospital']['sem_identificacao']++;
            }

            foreach ($infection['cultures'] as $culture)
            {
                //TABELA ENTRE COLUNAS OS TIPOS DE INFECÇÕES E LINHA GRAM POSITIVO
                if ($culture['germe'] == 'Gram-positive cocci unspecified' || $culture['germe'] == 'Gram-positive rod unspecified')
                {
                    switch ($infection['type']) //TABELA COMPROVAÇÃO MICROBIOLÓGICA POR SÍTIO/TIPO
                    {
                        case 0:
                            $data['hospital']['tipo_infeccao_gram_positivo']['meningite']++;
                            break;
                        case 1:
                            $data['hospital']['tipo_infeccao_gram_positivo']['encefalite']++;
                            break;
                        case 2:
                            $data['hospital']['tipo_infeccao_gram_positivo']['meningoencefalite']++;
                            break;
                        case 3:
                            $data['hospital']['tipo_infeccao_gram_positivo']['sinusite']++;
                            break;
                        case 4:
                            $data['hospital']['tipo_infeccao_gram_positivo']['mastoidite']++;
                            break;
                        case 5:
                            $data['hospital']['tipo_infeccao_gram_positivo']['otite']++;
                            break;
                        case 6:
                            $data['hospital']['tipo_infeccao_gram_positivo']['celulite']++;
                            break;
                        case 7:
                            $data['hospital']['tipo_infeccao_gram_positivo']['amigdalite']++;
                            break;
                        case 8:
                            $data['hospital']['tipo_infeccao_gram_positivo']['laringite']++;
                            break;
                        case 9:
                            $data['hospital']['tipo_infeccao_gram_positivo']['traqueite']++;
                            break;
                        case 10:
                            $data['hospital']['tipo_infeccao_gram_positivo']['pneumonia']++;
                            break;
                        case 11:
                            $data['hospital']['tipo_infeccao_gram_positivo']['endocardite']++;
                            break;
                        case 12:
                            $data['hospital']['tipo_infeccao_gram_positivo']['infeccao_corrente']++;
                            break;
                        case 13:
                            $data['hospital']['tipo_infeccao_gram_positivo']['mediastinite']++;
                            break;
                        case 14:
                            $data['hospital']['tipo_infeccao_gram_positivo']['gastroenterite']++;
                            break;
                        case 15:
                            $data['hospital']['tipo_infeccao_gram_positivo']['hepatite']++;
                            break;
                        case 16:
                            $data['hospital']['tipo_infeccao_gram_positivo']['pancreatite']++;
                            break;
                        case 17:
                            $data['hospital']['tipo_infeccao_gram_positivo']['peritonite']++;
                            break;
                        case 18:
                            $data['hospital']['tipo_infeccao_gram_positivo']['infeccao_urinaria']++;
                            break;
                        case 19:
                            $data['hospital']['tipo_infeccao_gram_positivo']['infeccao_pelvica']++;
                            break;
                        case 20:
                            $data['hospital']['tipo_infeccao_gram_positivo']['piodermite']++;
                            break;
                        case 21:
                            $data['hospital']['tipo_infeccao_gram_positivo']['foliculite']++;
                            break;
                        case 22:
                            $data['hospital']['tipo_infeccao_gram_positivo']['fasciite']++;
                            break;
                        case 23:
                            $data['hospital']['tipo_infeccao_gram_positivo']['ulcera_infectada']++;
                            break;
                        case 24:
                            $data['hospital']['tipo_infeccao_gram_positivo']['abcesso']++;
                            break;
                        case 25:
                            $data['hospital']['tipo_infeccao_gram_positivo']['ostemielite']++;
                            break;
                        case 26:
                            $data['hospital']['tipo_infeccao_gram_positivo']['artrite_septica']++;
                            break;
                        case 27:
                            $data['hospital']['tipo_infeccao_gram_positivo']['tuberculose_pulmonar']++;
                            break;
                        case 28:
                            $data['hospital']['tipo_infeccao_gram_positivo']['herpes_zoster']++;
                            break;
                        case 29:
                            $data['hospital']['tipo_infeccao_gram_positivo']['outros']++;
                            break;
                    }
                }
            }

            if ($infection['identificacao_agente']) //COMPROVAÇÃO MICROBIOLÓGICA: VALOR ABSOLUTO E PERCENTUAL NO TOTAL DE INFECÇOES/SEPSES
            {
                $data['hospital']['comprovacao_microbiologica_total']++;

                foreach ($infection['cultures'] as $culture)
                {
                    if ($culture['germe'] == 'Gram-positive cocci unspecified' || $culture['germe'] == 'Gram-positive rod unspecified')
                    {
                        $data['hospital']['gram_positivo_total']++; //GERME GRAM POSITIVO: VALOR ABSOLUTO E PERCENTUAL DE INCECÇÕES/SEPSES
                    }
                }

                switch ($gravidade_infeccao) //TABELA COMPROVAÇÃO MICROBIOLÓGICA POR GRAVIDADE DE INFECÇÒES
                {
                    case 'infeccao':
                        $data['hospital']['comprovacao_microbiologica_gravidade']['infeccao']++;
                        break;

                    case 'sepse':
                        $data['hospital']['comprovacao_microbiologica_gravidade']['sepse']++;
                        break;

                    case 'choque':
                        $data['hospital']['comprovacao_microbiologica_gravidade']['choque']++;
                        break;
                }

                switch ($infection['type']) //TABELA COMPROVAÇÃO MICROBIOLÓGICA POR SÍTIO/TIPO
                {
                    case 0:
                        $data['hospital']['tab_gravidade_tipo'][$gravidade_infeccao]['meningite']++;
                        break;
                    case 1:
                        $data['hospital']['tab_gravidade_tipo'][$gravidade_infeccao]['encefalite']++;
                        break;
                    case 2:
                        $data['hospital']['tab_gravidade_tipo'][$gravidade_infeccao]['meningoencefalite']++;
                        break;
                    case 3:
                        $data['hospital']['tab_gravidade_tipo'][$gravidade_infeccao]['sinusite']++;
                        break;
                    case 4:
                        $data['hospital']['tab_gravidade_tipo'][$gravidade_infeccao]['mastoidite']++;
                        break;
                    case 5:
                        $data['hospital']['tab_gravidade_tipo'][$gravidade_infeccao]['otite']++;
                        break;
                    case 6:
                        $data['hospital']['tab_gravidade_tipo'][$gravidade_infeccao]['celulite']++;
                        break;
                    case 7:
                        $data['hospital']['tab_gravidade_tipo'][$gravidade_infeccao]['amigdalite']++;
                        break;
                    case 8:
                        $data['hospital']['tab_gravidade_tipo'][$gravidade_infeccao]['laringite']++;
                        break;
                    case 9:
                        $data['hospital']['tab_gravidade_tipo'][$gravidade_infeccao]['traqueite']++;
                        break;
                    case 10:
                        $data['hospital']['tab_gravidade_tipo'][$gravidade_infeccao]['pneumonia']++;
                        break;
                    case 11:
                        $data['hospital']['tab_gravidade_tipo'][$gravidade_infeccao]['endocardite']++;
                        break;
                    case 12:
                        $data['hospital']['tab_gravidade_tipo'][$gravidade_infeccao]['infeccao_corrente']++;
                        break;
                    case 13:
                        $data['hospital']['tab_gravidade_tipo'][$gravidade_infeccao]['mediastinite']++;
                        break;
                    case 14:
                        $data['hospital']['tab_gravidade_tipo'][$gravidade_infeccao]['gastroenterite']++;
                        break;
                    case 15:
                        $data['hospital']['tab_gravidade_tipo'][$gravidade_infeccao]['hepatite']++;
                        break;
                    case 16:
                        $data['hospital']['tab_gravidade_tipo'][$gravidade_infeccao]['pancreatite']++;
                        break;
                    case 17:
                        $data['hospital']['tab_gravidade_tipo'][$gravidade_infeccao]['peritonite']++;
                        break;
                    case 18:
                        $data['hospital']['tab_gravidade_tipo'][$gravidade_infeccao]['infeccao_urinaria']++;
                        break;
                    case 19:
                        $data['hospital']['tab_gravidade_tipo'][$gravidade_infeccao]['infeccao_pelvica']++;
                        break;
                    case 20:
                        $data['hospital']['tab_gravidade_tipo'][$gravidade_infeccao]['piodermite']++;
                        break;
                    case 21:
                        $data['hospital']['tab_gravidade_tipo'][$gravidade_infeccao]['foliculite']++;
                        break;
                    case 22:
                        $data['hospital']['tab_gravidade_tipo'][$gravidade_infeccao]['fasciite']++;
                        break;
                    case 23:
                        $data['hospital']['tab_gravidade_tipo'][$gravidade_infeccao]['ulcera_infectada']++;
                        break;
                    case 24:
                        $data['hospital']['tab_gravidade_tipo'][$gravidade_infeccao]['abcesso']++;
                        break;
                    case 25:
                        $data['hospital']['tab_gravidade_tipo'][$gravidade_infeccao]['ostemielite']++;
                        break;
                    case 26:
                        $data['hospital']['tab_gravidade_tipo'][$gravidade_infeccao]['artrite_septica']++;
                        break;
                    case 27:
                        $data['hospital']['tab_gravidade_tipo'][$gravidade_infeccao]['tuberculose_pulmonar']++;
                        break;
                    case 28:
                        $data['hospital']['tab_gravidade_tipo'][$gravidade_infeccao]['herpes_zoster']++;
                        break;
                    case 29:
                        $data['hospital']['tab_gravidade_tipo'][$gravidade_infeccao]['outros']++;
                        break;
                }
            }

            switch ($infection['type']) //TABELA GRAVIDADE DE TIPO DE INFECÇÃO
            {
                case 0:
                    $data['hospital']['tab_gravidade_tipo'][$gravidade_infeccao]['meningite']++;
                    break;
                case 1:
                    $data['hospital']['tab_gravidade_tipo'][$gravidade_infeccao]['encefalite']++;
                    break;
                case 2:
                    $data['hospital']['tab_gravidade_tipo'][$gravidade_infeccao]['meningoencefalite']++;
                    break;
                case 3:
                    $data['hospital']['tab_gravidade_tipo'][$gravidade_infeccao]['sinusite']++;
                    break;
                case 4:
                    $data['hospital']['tab_gravidade_tipo'][$gravidade_infeccao]['mastoidite']++;
                    break;
                case 5:
                    $data['hospital']['tab_gravidade_tipo'][$gravidade_infeccao]['otite']++;
                    break;
                case 6:
                    $data['hospital']['tab_gravidade_tipo'][$gravidade_infeccao]['celulite']++;
                    break;
                case 7:
                    $data['hospital']['tab_gravidade_tipo'][$gravidade_infeccao]['amigdalite']++;
                    break;
                case 8:
                    $data['hospital']['tab_gravidade_tipo'][$gravidade_infeccao]['laringite']++;
                    break;
                case 9:
                    $data['hospital']['tab_gravidade_tipo'][$gravidade_infeccao]['traqueite']++;
                    break;
                case 10:
                    $data['hospital']['tab_gravidade_tipo'][$gravidade_infeccao]['pneumonia']++;
                    break;
                case 11:
                    $data['hospital']['tab_gravidade_tipo'][$gravidade_infeccao]['endocardite']++;
                    break;
                case 12:
                    $data['hospital']['tab_gravidade_tipo'][$gravidade_infeccao]['infeccao_corrente']++;
                    break;
                case 13:
                    $data['hospital']['tab_gravidade_tipo'][$gravidade_infeccao]['mediastinite']++;
                    break;
                case 14:
                    $data['hospital']['tab_gravidade_tipo'][$gravidade_infeccao]['gastroenterite']++;
                    break;
                case 15:
                    $data['hospital']['tab_gravidade_tipo'][$gravidade_infeccao]['hepatite']++;
                    break;
                case 16:
                    $data['hospital']['tab_gravidade_tipo'][$gravidade_infeccao]['pancreatite']++;
                    break;
                case 17:
                    $data['hospital']['tab_gravidade_tipo'][$gravidade_infeccao]['peritonite']++;
                    break;
                case 18:
                    $data['hospital']['tab_gravidade_tipo'][$gravidade_infeccao]['infeccao_urinaria']++;
                    break;
                case 19:
                    $data['hospital']['tab_gravidade_tipo'][$gravidade_infeccao]['infeccao_pelvica']++;
                    break;
                case 20:
                    $data['hospital']['tab_gravidade_tipo'][$gravidade_infeccao]['piodermite']++;
                    break;
                case 21:
                    $data['hospital']['tab_gravidade_tipo'][$gravidade_infeccao]['foliculite']++;
                    break;
                case 22:
                    $data['hospital']['tab_gravidade_tipo'][$gravidade_infeccao]['fasciite']++;
                    break;
                case 23:
                    $data['hospital']['tab_gravidade_tipo'][$gravidade_infeccao]['ulcera_infectada']++;
                    break;
                case 24:
                    $data['hospital']['tab_gravidade_tipo'][$gravidade_infeccao]['abcesso']++;
                    break;
                case 25:
                    $data['hospital']['tab_gravidade_tipo'][$gravidade_infeccao]['ostemielite']++;
                    break;
                case 26:
                    $data['hospital']['tab_gravidade_tipo'][$gravidade_infeccao]['artrite_septica']++;
                    break;
                case 27:
                    $data['hospital']['tab_gravidade_tipo'][$gravidade_infeccao]['tuberculose_pulmonar']++;
                    break;
                case 28:
                    $data['hospital']['tab_gravidade_tipo'][$gravidade_infeccao]['herpes_zoster']++;
                    break;
                case 29:
                    $data['hospital']['tab_gravidade_tipo'][$gravidade_infeccao]['outros']++;
                    break;
            }
        }

        $this->viewBuilder()->options(
            [
                'pdfConfig' =>
                [
                    'orientation' => 'portrait',
                    'filename' => 'Relatório CCIH.pdf',
                ],
            ]);

        $this->set('data', $data);
        $this->set('infections', $infections);
        $this->set('diagnostic', $diagnostic);
        $this->set('data2', $data2);
        $this->set('hospitalizations', $hospitalizations);
    }

    public function sihdot()
    {
        $session = $this->request->session();
        $unit_id = $session->read('selected-unit');

        $date_before = $session->read('date_before');
        $date_before->modify('first day of this month')->setTime(0, 0, 0);

        $date_after = $session->read('date_after');
        $date_after->modify('last day of this month')->setTime(23, 59, 59);

        $title = "Relatório de CIHDOTT";
        $calc_obj = new MedidasDispersao();
        $hospitalization_ids = [];
        $protocolos = null;
        $data2 = [];
        $data = [
            'tot_paciente' => [],
            'arr_paciente_suspeita' => [],
            'tot_paciente_suspeita' => 0,

            'arr_paciente_prot_aberto' => [],
            'tot_paciente_prot_aberto' => 0,
            'percen_paciente_prot_aberto' => 0,

            'arr_paciente_prot_fechado' => [],
            'tot_paciente_prot_fechado' => 0,
            'percen_paciente_prot_fechado' => 0,

            'arr_paciente_captou_orgao' => [],
            'tot_paciente_captou_orgao' => 0,
            'percen_paciente_captou_orgao' => 0,

            'percent_paciente_suspeita' => 0,
            'temp_1_e_2_exam_clinico' => [
                'temp_min_1_e_2_exam_clinico' => 0,
                'temp_medio_1_e_2_exam_clinico' => 0,
                'temp_max_1_e_2_exam_clinico' => 0,
                'total_hours' => 0,
                'total_registers' => 0,
            ],

            'idade_pelo_tempo_captacao' => [
                '0_9' => [
                    'arr' => [],
                    'media' => 0,
                ],
                '10_20' => [
                    'arr' => [],
                    'media' => 0,
                ],
                '21_30' => [
                    'arr' => [],
                    'media' => 0,
                ],
                '31_40' => [
                    'arr' => [],
                    'media' => 0,
                ],
                '41_50' => [
                    'arr' => [],
                    'media' => 0,
                ],
                '51_60' => [
                    'arr' => [],
                    'media' => 0,
                ],
                '61_70' => [
                    'arr' => [],
                    'media' => 0,
                ],
                '71_80' => [
                    'arr' => [],
                    'media' => 0,
                ],
                '81_90' => [
                    'arr' => [],
                    'media' => 0,
                ],
                '91_0' => [
                    'arr' => [],
                    'media' => 0,
                ],
            ],

            //HOUVE CONFIRMAÇÃO NO 1º MÉTODO UTILIZADO
            'confirmacao_primeiro_metodo' => [
                'arr' => [],
                'media' => 0,
                'total' => 0,
            ],

            //protocolos ME que foram fechados
            'protocolos_fechados' => [
                'arr' => [],
                'media' => 0,
                'total' => 0,
            ],
        ];

        $sihdot_table = TableRegistry::get('SuspectedMeProtocols');
        $hospitalizations_table = TableRegistry::get('Hospitalizations');

        $hospitalizations = $hospitalizations_table->find('all')
            ->contain(['CurbScores', 'SofaEscores', 'SapsIiiScores', 'Patients', 'Diagnostics', 'Discharges', 'SuspectedMeProtocols' => ['Organs', 'SupplementaryExaminations']])
            ->where(['Hospitalizations.unit_id' => $unit_id]);

        $data2 = $sihdot_table->getRelatorioData($hospitalizations);

        foreach ($hospitalizations as $key => $value)
        {
            array_push($hospitalization_ids, $value->id);

            if (!isset($value->discharges[0]['data_saida_unidade']))
            {
                if (!in_array($value->id, $data['tot_paciente']))
                {
                    array_push($data['tot_paciente'], $value->id);
                }
            }
        }

        if (!empty($hospitalization_ids))
        {
            $protocolos = $sihdot_table->find('all',
                [
                    'contain' => ["Hospitalizations.Patients"],

                    'conditions' => [
                        'SuspectedMeProtocols.hospitalization_id IN' => $hospitalization_ids,
                        'SuspectedMeProtocols.created >' => $date_before,
                        'SuspectedMeProtocols.created <' => $date_after,
                    ],
                ]);
            $firstIteration = 0;

            foreach ($protocolos as $key => $protocol)
            {
                //calcula os relatorios que foram fehchados
                $tot_exames_complementares = 0;

                if ($protocol['supplementary_examinations'])
                {
                    foreach ($protocol['supplementary_examinations'] as $exam)
                    {
                        $tot_exames_complementares++;
                    }
                }

                if ($protocol['data_captcao_orgaos'] || $protocol['data_nao_houve_captacao'])
                {
                    if (!in_array($protocol['hospitalization_id'], $data['protocolos_fechados']['arr']))
                    {
                        array_push($data['protocolos_fechados']['arr'], $protocol['hospitalization_id']);
                        $data['protocolos_fechados']['total']++;
                    }

                    if ($tot_exames_complementares <= 1)
                    {
                        if (!in_array($protocol['hospitalization_id'], $data['confirmacao_primeiro_metodo']['arr']))
                        {
                            array_push($data['confirmacao_primeiro_metodo']['arr'], $protocol['hospitalization_id']);
                            $data['confirmacao_primeiro_metodo']['total']++;
                        }
                    }
                }

                //calcula médio tempo de captacao de
                if (isset($protocol['data_captcao_orgaos']) &&
                    isset($protocol['hora_captcao_orgaos']) &&
                    isset($protocol['created']) &&
                    isset($protocol['hospitalization']['patient']['birthday']))
                {
                    $age = 0;
                    $total = 0;

                    $birthday = \DateTime::createFromFormat('d/m/Y', $protocol['hospitalization']['patient']['birthday']);
                    $now = new \DateTime();
                    //$age = $now->diff($birthday)->y;
                    $data_abertura_protocolo = \DateTime::createFromFormat('Y-m-d', $protocol['created']->format('Y-m-d'));

                    //$hora = \DateTime::createFromFormat("Y-m-d H:i:s", $protocol['hora_captcao_orgaos']->format('Y-m-d H:i:s') );
                    //$hora = new \DateTime( $protocol['hora_captcao_orgaos']->format('Y-m-d H:i:s') );

                    $data_captcao_orgaos = \DateTime::createFromFormat('Y-m-d', $protocol['data_captcao_orgaos']->format('Y-m-d'));
                    $data_captcao_orgaos->setTime($protocol['hora_captcao_orgaos']->format('H'), $protocol['hora_captcao_orgaos']->format('i'), 0);

                    $interval = $data_abertura_protocolo->diff($data_captcao_orgaos);

                    //$interval = new \DateTime();
                    if ($interval->format('%a') > 0)
                    {
                        $total += (float) $interval->format('%a') * 24;
                    }

                    if ($interval->format('%h') > 0)
                    {
                        $total += (float) $interval->format('%h');
                    }

                    $data['teste'][$key] = $total;

                    if ($age >= 0 && $age <= 9)
                    {
                        array_push($data['idade_pelo_tempo_captacao']['0_9']['arr'], $total);
                    }
                    else if ($age <= 20)
                    {
                        array_push($data['idade_pelo_tempo_captacao']['10_20']['arr'], $total);
                    }
                    else if ($age <= 30)
                    {
                        array_push($data['idade_pelo_tempo_captacao']['21_30']['arr'], $total);
                    }
                    else if ($age <= 40)
                    {
                        array_push($data['idade_pelo_tempo_captacao']['31_40']['arr'], $total);
                    }
                    else if ($age <= 50)
                    {
                        array_push($data['idade_pelo_tempo_captacao']['41_50']['arr'], $total);
                    }
                    else if ($age <= 60)
                    {
                        array_push($data['idade_pelo_tempo_captacao']['51_60']['arr'], $total);
                    }
                    else if ($age <= 70)
                    {
                        array_push($data['idade_pelo_tempo_captacao']['61_70']['arr'], $total);
                    }
                    else if ($age <= 80)
                    {
                        array_push($data['idade_pelo_tempo_captacao']['71_80']['arr'], $total);
                    }
                    else if ($age <= 90)
                    {
                        array_push($data['idade_pelo_tempo_captacao']['81_90']['arr'], $total);
                    }
                    else if ($age >= 90)
                    {
                        array_push($data['idade_pelo_tempo_captacao']['91_0']['arr'], $total);
                    }
                }

                if
                (
                    isset($protocol['1_exame_data']) &&
                    isset($protocol['1_exame_hora']) &&
                    isset($protocol['2_exame_data']) &&
                    isset($protocol['2_exame_hora'])
                )
                {
                    $firstIteration++;
                    //$date_exame_1 = new \DateTime( $protocol['1_exame_data'] );
                    $hour1 = 0;
                    $hour2 = 0;
                    $total = 0;
                    $diff = null;
                    $date_exame_1 = \DateTime::createFromFormat('d/m/Y', $protocol['1_exame_data']);
                    $date_exame_2 = \DateTime::createFromFormat('d/m/Y', $protocol['2_exame_data']);

                    //$diff = $date_exame_1->diff($date_exame_2)->format("%h");
                    $diff = $date_exame_1->diff($date_exame_2);

                    if ($diff->format('%a') > 0)
                    {
                        $hour1 = $diff->format('%a') * 24;
                    }

                    if ($diff->format('%h') > 0)
                    {
                        $hour2 = $diff->format('%h');
                    }

                    $total = $hour1 + $hour2;

                    if ($total > $data['temp_1_e_2_exam_clinico']['temp_max_1_e_2_exam_clinico']) //calcula o tempo mínimo
                    {
                        $data['temp_1_e_2_exam_clinico']['temp_max_1_e_2_exam_clinico'] = $total;

                        // $data['temp_1_e_2_exam_clinico']['data_max_1'] = $date_exame_1;
                        // $data['temp_1_e_2_exam_clinico']['data_max_2'] = $date_exame_2;
                    }

                    if ($firstIteration == 1 || $total < $data['temp_1_e_2_exam_clinico']['temp_max_1_e_2_exam_clinico'])
                    {
                        $data['temp_1_e_2_exam_clinico']['temp_min_1_e_2_exam_clinico'] = $total;

                        // $data['temp_1_e_2_exam_clinico']['data_min_1'] = $date_exame_1;
                        // $data['temp_1_e_2_exam_clinico']['data_min_2'] = $date_exame_2;
                    }

                    // $data['temp_1_e_2_exam_clinico']['total_hours']['1_exame_data'] = $hour1;
                    // $data['temp_1_e_2_exam_clinico']['total_hours']['2_exame_data'] = $hour2;
                    $data['temp_1_e_2_exam_clinico']['total_hours'] += $total;
                    $data['temp_1_e_2_exam_clinico']['total_registers']++;

                    //array_push($data['temp_1_e_2_exam_clinico']['total_hours'], $diff);
                }

                //pega todos os pacientes
                if (!in_array($protocol['hospitalization']['patient_id'], $data['arr_paciente_suspeita']))
                {
                    array_push($data['arr_paciente_suspeita'], $protocol['hospitalization']['patient_id']);
                    $data['tot_paciente_suspeita']++;
                }

                //pega todos os pacientes com protoclo aberto
                if (!$protocol['protocolo_foi_fechado'])
                {
                    if (!in_array($protocol['hospitalization']['patient_id'], $data['arr_paciente_prot_aberto']))
                    {
                        array_push($data['arr_paciente_prot_aberto'], $protocol['hospitalization']['patient_id']);
                        $data['tot_paciente_prot_aberto']++;
                    }
                }
                else
                {
                    if (!in_array($protocol['hospitalization']['patient_id'], $data['arr_paciente_prot_fechado']))
                    {
                        array_push($data['arr_paciente_prot_fechado'], $protocol['hospitalization']['patient_id']);
                        $data['tot_paciente_prot_fechado']++;
                    }
                }
            }
        }

        if ($data['tot_paciente_prot_aberto'] > 0 && $data['tot_paciente_suspeita'] > 0)
        {
            $data['percen_paciente_prot_aberto'] = ($data['tot_paciente_prot_aberto'] * 100) / $data['tot_paciente_suspeita'];
        }

        if ($data['tot_paciente_prot_fechado'] > 0 && $data['tot_paciente_suspeita'] > 0)
        {
            $data['percen_paciente_prot_fechado'] = ($data['tot_paciente_prot_aberto'] * 100) / $data['tot_paciente_suspeita'];
        }

        foreach ($data['idade_pelo_tempo_captacao'] as $key => $value)
        {
            if (!empty($value['arr']))
            {
                $data['idade_pelo_tempo_captacao'][$key]['media'] = $calc_obj->calc_average($data['idade_pelo_tempo_captacao'][$key]['arr']);

                if ($data['idade_pelo_tempo_captacao'][$key]['media'] > 96)
                {
                    $data['idade_pelo_tempo_captacao'][$key]['media'] = round($data['idade_pelo_tempo_captacao'][$key]['media'] / 24, 0);

                    if ($data['idade_pelo_tempo_captacao'][$key]['media'] % 24)
                    {
                        $data['idade_pelo_tempo_captacao'][$key]['media'] = $data['idade_pelo_tempo_captacao'][$key]['media'] . 'D';

                        $data['idade_pelo_tempo_captacao'][$key]['media'] .= ' ' . ($data['idade_pelo_tempo_captacao'][$key]['media'] % 24) . 'H';
                    }
                    else
                    {
                        $data['idade_pelo_tempo_captacao'][$key]['media'] .= $data['idade_pelo_tempo_captacao'][$key]['media'] . 'D';
                    }
                }
                else
                {
                    $data['idade_pelo_tempo_captacao'][$key]['media'] = $data['idade_pelo_tempo_captacao'][$key]['media'] . 'H';
                }
            }
        }

        if ($data['protocolos_fechados']['total'] > 0 &&
            $data['confirmacao_primeiro_metodo']['total'] > 0)
        {
            $data['confirmacao_primeiro_metodo']['media'] = $data['confirmacao_primeiro_metodo']['total'];
        }

        $this->viewBuilder()->options([
            'pdfConfig' => [
                'orientation' => 'portrait',
                'filename' => 'Relatório de CIHDOTT.pdf',
            ],
        ]);

        $protocolos->toArray();
        $this->set('protocolos', $protocolos);
        $this->set('hospitalizations', $hospitalizations);
        $this->set('data', $data);
        $this->set('data2', $data2);
        $this->set('title', $title);
    }

    public function bundlePav()
    {
        $data = [
            'bundle' => [],
            'measures' => [],
        ];

        $pdf_orientation = 'portrait';

        $session = $this->request->session();

        $bundle_table = TableRegistry::get('PavPreventionBundles');
        $measures_table = TableRegistry::get('SupportiveMeasures');
        $hospitalization_table = TableRegistry::get('Hospitalizations');

        $unit_id = $session->read('selected-unit');
        $relatorio_type = $session->read('relatorio_type');

        $date_before = $session->read('date_before');
        $date_after = $session->read('date_after');


        //daterange
        $interval = new \DateInterval('P1M');
        $daterange = new \DatePeriod($date_before, $interval, $date_after);

        $date_before->modify('first day of this month')->setTime(0, 0, 0);
        $date_after->modify('last day of this month')->setTime(23, 59, 59);

        $month_name = [
            '01' => 'Jan',
            '02' => 'Fev',
            '03' => 'Mar',
            '04' => 'Abr',
            '05' => 'Mai',
            '06' => 'Jun',
            '07' => 'Jul',
            '08' => 'Ago',
            '09' => 'Set',
            '10' => 'Out',
            '11' => 'Nov',
            '12' => 'Dez',
        ];



        $hospitalizations = $hospitalization_table
            ->find()
            ->contain(['PavPreventionBundles.ImmediateComplications', 'SupportiveMeasures'])
            ->where(['Hospitalizations.unit_id' => $unit_id, 'Hospitalizations.status' => 'internado'])
            ->orwhere(['Hospitalizations.unit_id' => $unit_id, 'Hospitalizations.status <>' => 'internado', 'Hospitalizations.data_alta_obito <=' => $date_after->format("Y-m-d")])
            ->toArray();



        if($relatorio_type == 'parcial'){

            $pdf_orientation = 'landscape';

            foreach ($daterange as $date) //gera os dados do relatorio de acordo com o mes
            {
                

                $monthCardiacSurgeries = [];
                $hospitalizations_period=[];

                //primeiro dia do mes
                $startOfMonth = new \DateTime($date->format('Y-m-d'));
                $startOfMonth->modify('first day of this month')->setTime(0, 0, 0);

                //ultimo dia do mes
                $endOfMonth = new \DateTime($date->format('Y-m-d'));
                $endOfMonth->modify('last day of this month')->setTime(23, 59, 59);
                
               
                $firstDay = $startOfMonth->format("Y-m-d");
                $lastDay  = $endOfMonth->format("Y-m-d");

                $hospitalizations_period = $hospitalization_table->find('all', [
                    'contain' => [
                        
                        "PavPreventionBundles" =>  function ($q) use($firstDay,$lastDay) {
                           return $q->find('all')
                           ->contain(['ImmediateComplications'])
                           ->where([
                                'PavPreventionBundles.created >= ' => $firstDay,
                                'PavPreventionBundles.data_da_retirada <= ' => $lastDay
                           ]);
                        },

                        "SupportiveMeasures" =>  function ($q) use($firstDay,$lastDay) {
                           return $q->find('all')->where([
                                'SupportiveMeasures.created >= ' => $firstDay,
                                'SupportiveMeasures.created <= ' => $lastDay
                           ]);
                        }

                    ]
                ])
                ->where(['Hospitalizations.unit_id' => $unit_id, 'Hospitalizations.status' => 'internado'])
                ->orwhere(['Hospitalizations.unit_id' => $unit_id, 'Hospitalizations.status <>' => 'internado', 'Hospitalizations.data_alta_obito <=' => $endOfMonth->format("Y-m-d")])
                ->toArray();


                $data['bundles']['dates'][$date->format("m")] = $bundle_table->getRelatorioData($hospitalizations_period,$firstDay,$lastDay);
                $data['measures']['dates'][$date->format("m")] = $measures_table->getRelatorioPav($hospitalizations_period);
            }

        }else{

            
            $data['bundle'] = $bundle_table->getRelatorioData($hospitalizations, $date_before, $date_after);
            $data['measures'] = $measures_table->getRelatorioPav($hospitalizations);

        }    

        

        $this->viewBuilder()->options([
            'pdfConfig' => [
                'orientation' => $pdf_orientation,
                'filename' => 'Relatório de Bundle de Prevenção de PAV.pdf',
            ],
        ]);

        $this->set('hospitalizations', $hospitalizations);
        $this->set('month_name', $month_name);
        $this->set('data', $data);

        if ($relatorio_type == 'parcial'){
            $this->render('detailed_bunlde_pav');
        }
    }

    public function bundlePavParcial()
    {
        $session = $this->request->session();
        $date_before = $session->read('date_before');
        $date_after = $session->read('date_after');
        $interval = new \DateInterval('P1M');
        $daterange = new \DatePeriod($date_before, $interval, $date_after);
        $month_name = [
            '01' => 'Jan',
            '02' => 'Fev',
            '03' => 'Mar',
            '04' => 'Abr',
            '05' => 'Mai',
            '06' => 'Jun',
            '07' => 'Jul',
            '08' => 'Ago',
            '09' => 'Set',
            '10' => 'Out',
            '11' => 'Nov',
            '12' => 'Dez',
        ];
        $data =
            [
            'dates' => [],
        ];

        foreach ($daterange as $date)
        {
            $data['dates'][$date->format("m")] = [];
        }

        $this->viewBuilder()->options([
            'pdfConfig' => [
                'orientation' => 'landscape',
                'filename' => 'Relatório Bundle de PAV parcial.pdf',
            ],
        ]);

        $this->set('data', $data);
        $this->set('month_name', $month_name);
        $this->set('hospitalizations', $hospitalizations);
    }

    public function bundlePavDiario()
    {
        $hospital_ids = [];
        $session = $this->request->session();
        $unit_id = $session->read('selected-unit');
        $cont = 0;
        $data = null;

        //ultimo dia do mes
        $date_before = $session->read('date_before');
        $date_before->modify('first day of this month')->setTime(00, 00, 00);
        $date_after = $session->read('date_after');
        $date_after->modify('last day of this month')->setTime(23, 59, 59);

        $bundle_table = TableRegistry::get('PavDiaryChecklists');
        $hospitalization_table = TableRegistry::get('Hospitalizations');
        $hospitalizations = $hospitalization_table
            ->find('all')
            ->where(['Hospitalizations.unit_id' => $unit_id, 'Hospitalizations.status <>' => 'obito'])
            ->orwhere(['Hospitalizations.unit_id' => $unit_id, 'Hospitalizations.status' => 'obito', 'Hospitalizations.data_alta_obito < ' => $date_after->format("Y-m-d")])
            ->contain(["PavPreventionBundles.PavDiaryChecklists", "SupportiveMeasures"])
            ->toArray();

        $data = $bundle_table->getRelatorioData($hospitalizations, $date_before, $date_after);

        $this->viewBuilder()->options([
            'pdfConfig' => [
                'orientation' => 'portrait',
                'filename' => 'Relatório Bundle de PAV diario.pdf',
            ],
        ]);

        $this->set('data', $data);
        $this->set('hospitalizations', $hospitalizations);
    }

    public function visualizationEmergency()
    {

        $session = $this->request->session();
        $date_before = $session->read('date_before');
        $date_after = $session->read('date_after');

        $screening_table = TableRegistry::get('Screenings');
        $screening = $screening_table->find('all', [
            'contain' => ["MedicalCares", "Admissions", 'Patients' => ['Diagnostics'], 'Professionals' => ["Users"]],

            //'conditions' => ['Screenings.unit_id' => 1, 'Screenings.created >\'' . $date_before- . '\' and Screenings.created < \'' . $date_after . '\''],
        ]);

        $this->viewBuilder()->options([
            'pdfConfig' => [
                'orientation' => 'landscape',
                'filename' => 'Visualizacao da emergencia.pdf',
            ],
        ]);
        $screening->toArray();
        $this->set('data', $screening);
    }

    public function visualizationHospitalization()
    {
        $session = $this->request->session();
        $date_before = $session->read('date_before');
        $date_after = $session->read('date_after');

        $hospitalization_table = TableRegistry::get('Hospitalizations');
        $hospitalization = $hospitalization_table->find('all', [
            'contain' => ["IdentificationOfRisks", 'Patients', 'HistoricPatientMovements' => ['Rooms' => ['Sectors']], 'Diagnostics'],

            'conditions' => [
                'Hospitalizations.unit_id' => 1,
                'Hospitalizations.date_of_hospitalization >\'' . $date_before . '\' and Hospitalizations.date_of_hospitalization < \'' . $date_after . '\'',

            ],
        ]);

        $this->viewBuilder()->options([
            'pdfConfig' => [
                'orientation' => 'landscape',
                'filename' => 'Visualizacao dos pacientes na internacao.pdf',
            ],
        ]);
        $hospitalization->toArray();
        $this->set('data', $hospitalization);
    }

    public function resumoInternacao()
    {

        $hospitalization_table = TableRegistry::get('Hospitalizations');
        $hospitalization = $hospitalization_table->find('all', [
            'contain' => ["IdentificationOfRisks", 'Patients', 'HistoricPatientMovements' => ['Rooms' => ['Sectors']], 'Diagnostics' => ['Comorbidities']],

            'conditions' => ['Hospitalizations.id' => 2],
        ]

        )->first();

        $this->viewBuilder()->options([
            'pdfConfig' => [
                'orientation' => 'portrait',
                'filename' => 'Resumo da internação.pdf',
            ],
        ]);
        $hospitalization->toArray();
        $this->set('data', $hospitalization);
    }

    public function surgeries()
    {
        $session = $this->request->session();
        $unit_id = $session->read('selected-unit');

        $dataInicio = $session->read('date_before');
        $dataFim = $session->read('date_after');

        $surgeries_table = TableRegistry::get('Surgeries');
        $specialties_table = TableRegistry::get('Specialties');
        $anesthesia_table = TableRegistry::get('TypeOfAnesthesias');
        $hospitalizations_table = TableRegistry::get('Hospitalizations');

        $hospitalizations = $hospitalizations_table
            ->find('all')
            ->where(['Hospitalizations.unit_id' => $unit_id, 'Hospitalizations.status' => 'internado'])
            ->orwhere(['Hospitalizations.unit_id' => $unit_id, 'Hospitalizations.status <>' => 'internado', 'Hospitalizations.data_alta_obito <=' => $dataFim->format("Y-m-d")])
            ->contain(["Infections", "Surgeries" => ["TypeOfAnesthesias", 'Specialties']])
            ->toArray();

        $specialties = $specialties_table
            ->find('all')
            ->toArray();

        $anesthesias = $anesthesia_table
            ->find('all')
            ->toArray();

        $data = $surgeries_table->getRelatorioData($hospitalizations, $dataInicio, $dataFim, $anesthesias, $specialties);

        $hospitalization_ids = [];

        foreach ($hospitalizations as $key => $value)
        {
            array_push($hospitalization_ids, $value->id);
        }



        $surgeries = $surgeries_table->find('all', [
            'contain' => ['Rooms'],
            'conditions' => [
                'Surgeries.hospitalization_id in ' => $hospitalization_ids,
                'Surgeries.data_da_cirurgia >' => $dataInicio,
                'Surgeries.data_da_cirurgia <' => $dataFim,
            ],
        ])
            ->toArray();

        $count = 0;
        $a = 0;
        $somatorioDias = 0;
        $countCirurgiaCadastroRealizado = 0;
        $somatorioDiasCicurgiaCadastroRealizado = 0;
        $arr = [];
        $taxaCancelamento = 0;
        $taxaAdiamento = 0;
        $countTaxaAtraso = 0;
        $countTaxaTerminoAtraso = 0;
        $tempoAtraso = 0;
        $tempoTerminoAtraso = 0;
        $tempoAtrasoCirurgia = 0;
        $countPortetempoAtrasoCirurgia = 0;
        $countConveniotempoAtrasoCirurgia = 0;
        $countTipoCirurgiatempoAtrasoCirurgia = 0;

        foreach ($surgeries as $key => $surgerie)
        {
            // echo '<pre>';
            // var_dump($surgerie['hospitalization']);

            $data1 = $surgerie['data_da_solicitacao'];
            $data2 = $surgerie['data_da_liberacao'];
            $data_cirurgia = $surgerie['data_da_cirurgia'];

            //echo "dd";
            if ($surgerie['data_da_cirurgia'] != "" && $surgerie['inicio_da_cirurgia_data'] != "")
            {
                $dataHoraAgendamentoCirurgia = null;
                $dataHoraInicioCirurgia = null;
                $dataHoraTerminoCirurgia = null;

                if (isset($surgerie['hora_da_cirurgia']) && isset($surgerie['data_da_cirurgia']))
                {
                    $dataHoraAgendamentoCirurgia = new \DateTime($surgerie['data_da_cirurgia']->i18nFormat('yyyy-MM-dd') . ' ' . $surgerie['hora_da_cirurgia']->i18nFormat('HH:mm:ss'));
                }
                else
                {
                    continue;
                }

                if (isset($surgerie['termino_da_cirurgia_hora']) && isset($surgerie['inicio_da_cirurgia_data']))
                {
                    $dataHoraInicioCirurgia = new \DateTime($surgerie['inicio_da_cirurgia_data']->i18nFormat('yyyy-MM-dd') . ' ' . $surgerie['inicio_da_cirurgia_hora']->i18nFormat('HH:mm:ss'));
                }
                else
                {
                    continue;
                }

                if (isset($surgerie['termino_da_cirurgia_hora']) && isset($surgerie['inicio_da_cirurgia_data']))
                {
                    $dataHoraTerminoCirurgia = new \DateTime($surgerie['inicio_da_cirurgia_data']->i18nFormat('yyyy-MM-dd') . ' ' . $surgerie['termino_da_cirurgia_hora']->i18nFormat('HH:mm:ss'));
                }
                else
                {
                    continue;
                }

                $intervaloDataHoraCirugia = $dataHoraAgendamentoCirurgia->diff($dataHoraInicioCirurgia);
                $tempoAtrasoCirurgia = 0;
                if ($intervaloDataHoraCirugia->h > 0)
                {
                    $tempoAtrasoCirurgia = $intervaloDataHoraCirugia->h;
                    $tempoAtraso += $intervaloDataHoraCirugia->h;
                    $countTaxaAtraso++;
                }

                //echo $surgerie['data_da_cirurgia']->i18nFormat('yyyy-MM-dd');
                //tempo que deve levar a cirurgia
                $tempo_de_cirurgia_estimado = explode(":", $surgerie['tempo_de_cirurgia_estimado']->i18nFormat('HH:mm:ss'));

                $somar = ' +' . $tempo_de_cirurgia_estimado[0] . ' hours, +' . $tempo_de_cirurgia_estimado[1] . ' minute';

                //echo 'somar = ' . $somar;
                //hora de termino da cirurgia
                $dataHoraAgendamentoCirurgiaTermino = $dataHoraAgendamentoCirurgia->modify($somar);

                $dataHoraCirurgiaTermino = $dataHoraTerminoCirurgia->modify($somar);

                $intervaloDataHoraTerminoCirugia = $dataHoraCirurgiaTermino->diff($dataHoraAgendamentoCirurgiaTermino);

                $tempoAtrasoTerminoCirurgia = 0;

                if ($intervaloDataHoraTerminoCirugia->h > 0)
                {
                    $tempoAtrasoTerminoCirurgia = $intervaloDataHoraTerminoCirugia->h;
                    $tempoTerminoAtraso += $intervaloDataHoraTerminoCirugia->h;
                    $countTaxaTerminoAtraso++;
                }

                //print_r($dataHoraAgendamentoCirurgiaTermino);
            }

            if ($surgerie['cancelar_cirurgia'])
            {
                $taxaCancelamento++;
            }

            if ($surgerie['adiar_cirurgia'])
            {
                $taxaAdiamento++;
            }

            //TEMPO MÉDIO DE CADASTRO DE CIRURGIA E LIBERAÇÃO DA CIRURGIA
            if ($data1 != "" && $data2 != "")
            {
                $intervalo = $data1->diff($data2);
                $somatorioDias += $intervalo->d;
                $count++;

                //TEMPO MÉDIO DE CADASTRO DE CIRURGIA E LIBERAÇÃO DA CIRURGIA POR PORTE DE CIRURGIA

                //$arr['porte'][$surgerie['porte_da_cirurgia']]['cancelamento'] = 0;
                if (!isset($arr['porte'][$surgerie['porte_da_cirurgia']]['totalDiasPorte']))
                {
                    $arr['porte'][$surgerie['porte_da_cirurgia']]['totalDiasPorte'] = $intervalo->d;
                    $arr['porte'][$surgerie['porte_da_cirurgia']]['countCirurgiaPorte'] = 1;
                    if ($surgerie['cancelar_cirurgia'])
                    {
                        $arr['porte'][$surgerie['porte_da_cirurgia']]['cancelamento'] = 1;
                    }
                    else
                    {
                        $arr['porte'][$surgerie['porte_da_cirurgia']]['cancelamento'] = 0;
                    }

                    if ($surgerie['adiar_cirurgia'])
                    {
                        $arr['porte'][$surgerie['porte_da_cirurgia']]['adiado'] = 1;
                    }
                    else
                    {
                        $arr['porte'][$surgerie['porte_da_cirurgia']]['adiado'] = 0;
                    }

                    if ($tempoAtrasoCirurgia > 0)
                    {
                        $arr['porte'][$surgerie['porte_da_cirurgia']]['tempoTotalAtrasoCirurgia'] = $tempoAtrasoCirurgia;
                        $countPortetempoAtrasoCirurgia++;
                    }
                }
                else
                {
                    $arr['porte'][$surgerie['porte_da_cirurgia']]['totalDiasPorte'] += $intervalo->d;
                    $arr['porte'][$surgerie['porte_da_cirurgia']]['countCirurgiaPorte'] += 1;

                    if ($surgerie['cancelar_cirurgia'])
                    {
                        $arr['porte'][$surgerie['porte_da_cirurgia']]['cancelamento'] += 1;
                    }

                    if ($surgerie['adiar_cirurgia'])
                    {
                        $arr['porte'][$surgerie['porte_da_cirurgia']]['adiado'] += 1;
                    }

                    if ($tempoAtrasoCirurgia > 0)
                    {
                        if (isset($arr['porte'][$surgerie['porte_da_cirurgia']]['tempoTotalAtrasoCirurgia']))
                        {
                            $arr['porte'][$surgerie['porte_da_cirurgia']]['tempoTotalAtrasoCirurgia'] += $tempoAtrasoCirurgia;
                            $countPortetempoAtrasoCirurgia++;
                        }
                        else
                        {
                            $arr['porte'][$surgerie['porte_da_cirurgia']]['tempoTotalAtrasoCirurgia'] = $tempoAtrasoCirurgia;
                            $countPortetempoAtrasoCirurgia++;
                        }
                    }
                }

                //TEMPO MÉDIO DE CADASTRO DE CIRURGIA E LIBERAÇÃO DA CIRURGIA POR TIPO DE CIRURGIA

                //$arr['tipoCirurgia'][$surgerie['nome']]['cancelamento'] = 0;
                if (!isset($arr['tipoCirurgia'][$surgerie['nome']]['countTipoCirurgia']))
                {
                    $arr['tipoCirurgia'][$surgerie['nome']]['nome'] = $surgerie['nome'];
                    $arr['tipoCirurgia'][$surgerie['nome']]['countTipoCirurgia'] = 1;
                    $arr['tipoCirurgia'][$surgerie['nome']]['totalDiasTipo'] = $intervalo->d;

                    //taxa cancelamento
                    if ($surgerie['cancelar_cirurgia'])
                    {
                        $arr['tipoCirurgia'][$surgerie['nome']]['cancelamento'] = 1;
                    }
                    else
                    {
                        $arr['tipoCirurgia'][$surgerie['nome']]['cancelamento'] = 0;
                    }

                    //taxa adiamento
                    if ($surgerie['adiar_cirurgia'])
                    {
                        $arr['tipoCirurgia'][$surgerie['nome']]['adiado'] = 1;
                    }
                    else
                    {
                        $arr['tipoCirurgia'][$surgerie['nome']]['adiado'] = 0;
                    }

                    if ($tempoAtrasoCirurgia > 0)
                    {
                        $arr['tipoCirurgia'][$surgerie['nome']]['tempoTotalAtrasoCirurgia'] = $tempoAtrasoCirurgia;
                        $countTipoCirurgiatempoAtrasoCirurgia++;
                    }
                }
                else
                {
                    $arr['tipoCirurgia'][$surgerie['nome']]['countTipoCirurgia'] += 1;
                    $arr['tipoCirurgia'][$surgerie['nome']]['totalDiasTipo'] += $intervalo->d;

                    if ($surgerie['cancelar_cirurgia'])
                    {
                        $arr['tipoCirurgia'][$surgerie['nome']]['cancelamento'] += 1;
                    }

                    if ($surgerie['adiar_cirurgia'])
                    {
                        $arr['tipoCirurgia'][$surgerie['nome']]['adiado'] += 1;
                    }

                    if ($tempoAtrasoCirurgia > 0)
                    {
                        if (!isset($arr['tipoCirurgia'][$surgerie['nome']]['tempoTotalAtrasoCirurgia']))
                        {
                            $arr['tipoCirurgia'][$surgerie['nome']]['tempoTotalAtrasoCirurgia'] += $tempoAtrasoCirurgia;
                            $countTipoCirurgiatempoAtrasoCirurgia++;
                        }
                        else
                        {
                            $arr['tipoCirurgia'][$surgerie['nome']]['tempoTotalAtrasoCirurgia'] = $tempoAtrasoCirurgia;
                            $countTipoCirurgiatempoAtrasoCirurgia++;
                        }
                    }
                }

                if (!isset($arr['convenio'][$surgerie['hospitalization']['complementary_health']]['countConvenio']))
                {
                    $arr['convenio'][$surgerie['hospitalization']['complementary_health']]['nome'] = $surgerie['hospitalization']['complementary_health'];

                    $arr['convenio'][$surgerie['hospitalization']['complementary_health']]['countConvenio'] = 1;
                    $arr['convenio'][$surgerie['hospitalization']['complementary_health']]['totalDiasConvenio'] = $intervalo->d;

                    if ($surgerie['cancelar_cirurgia'])
                    {
                        $arr['convenio'][$surgerie['hospitalization']['complementary_health']]['cancelamento'] = 1;
                    }
                    else
                    {
                        $arr['convenio'][$surgerie['hospitalization']['complementary_health']]['cancelamento'] = 0;
                    }

                    if ($surgerie['adiar_cirurgia'])
                    {
                        $arr['convenio'][$surgerie['hospitalization']['complementary_health']]['adiado'] = 1;
                    }
                    else
                    {
                        $arr['convenio'][$surgerie['hospitalization']['complementary_health']]['adiado'] = 0;
                    }

                    if ($tempoAtrasoCirurgia > 0)
                    {
                        $arr['convenio'][$surgerie['hospitalization']['complementary_health']]['tempoTotalAtrasoCirurgia'] = $tempoAtrasoCirurgia;
                        $countConveniotempoAtrasoCirurgia++;
                    }
                }
                else
                {
                    $arr['convenio'][$surgerie['hospitalization']['complementary_health']]['countConvenio'] += 1;
                    $arr['convenio'][$surgerie['hospitalization']['complementary_health']]['totalDiasConvenio'] += $intervalo->d;

                    if ($surgerie['cancelar_cirurgia'])
                    {
                        $arr['convenio'][$surgerie['hospitalization']['complementary_health']]['cancelamento'] += 1;
                    }

                    if ($surgerie['adiar_cirurgia'])
                    {
                        $arr['convenio'][$surgerie['hospitalization']['complementary_health']]['adiado'] += 1;
                    }

                    if ($tempoAtrasoCirurgia > 0)
                    {
                        if (isset($arr['convenio'][$surgerie['hospitalization']['complementary_health']]['tempoTotalAtrasoCirurgia']))
                        {
                            $arr['convenio'][$surgerie['hospitalization']['complementary_health']]['tempoTotalAtrasoCirurgia'] += $tempoAtrasoCirurgia;
                            $countConveniotempoAtrasoCirurgia++;
                        }
                        else
                        {
                            $arr['convenio'][$surgerie['hospitalization']['complementary_health']]['tempoTotalAtrasoCirurgia'] = $tempoAtrasoCirurgia;
                            $countConveniotempoAtrasoCirurgia++;
                        }
                    }
                }

                //TEMPO MÉDIO DE ATRASO DE CIRURGIA POR SALA CIRURGICA
                if ($tempoAtrasoCirurgia > 0 && $surgerie['room']['id'] != null)
                {
                    if (!isset($arr['tempoAtrasoSalaCirurgica'][$surgerie['room']['id']]['countSala']))
                    {
                        $arr['tempoAtrasoSalaCirurgica'][$surgerie['room']['id']]['nome'] = $surgerie['room']['name'];
                        $arr['tempoAtrasoSalaCirurgica'][$surgerie['room']['id']]['countSala'] = 1;
                        $arr['tempoAtrasoSalaCirurgica'][$surgerie['room']['id']]['tempoTotalAtrasoCirurgia'] = $tempoAtrasoCirurgia;
                    }
                    else
                    {
                        $arr['tempoAtrasoSalaCirurgica'][$surgerie['room']['id']]['countSala'] = +1;
                        $arr['tempoAtrasoSalaCirurgica'][$surgerie['room']['id']]['tempoTotalAtrasoCirurgia'] += $tempoAtrasoCirurgia;
                    }
                }

                if ($tempoAtrasoCirurgia > 0)
                {
                    //TEMPO MÉDIO DE ATRASO DE CIRURGIA POR DIA DA SEMANA
                    $diaSemana = $surgeries_table->diaSemana($surgerie['data_da_cirurgia']->i18nFormat('yyyy-MM-dd'));

                    if (!isset($arr['diaDeSemana'][$diaSemana]['diaSemana']))
                    {
                        $arr['diaDeSemana'][$diaSemana]['diaSemana'] = $diaSemana;
                        $arr['diaDeSemana'][$diaSemana]['countDiaSemana'] = 1;
                        $arr['diaDeSemana'][$diaSemana]['tempoTotalAtrasoCirurgia'] = $tempoAtrasoCirurgia;
                    }
                    else
                    {
                        $arr['diaDeSemana'][$diaSemana]['countDiaSemana'] += 1;
                        $arr['diaDeSemana'][$diaSemana]['tempoTotalAtrasoCirurgia'] += $tempoAtrasoCirurgia;
                    }

                    //TEMPO MÉDIO DE ATRASO DE CIRURGIA POR TIPO ROTINA, URGENTE E EMERGENTE
                    if (!isset($arr['caracteristica_da_cirurgia'][$surgerie['caracteristica_da_cirurgia']]['tipo']))
                    {
                        $arr['caracteristica_da_cirurgia'][$surgerie['caracteristica_da_cirurgia']]['tipo'] = $surgerie['caracteristica_da_cirurgia'];
                        $arr['caracteristica_da_cirurgia'][$surgerie['caracteristica_da_cirurgia']]['countTipo'] = 1;
                        $arr['caracteristica_da_cirurgia'][$surgerie['caracteristica_da_cirurgia']]['tempoTotalAtrasoCirurgia'] = $tempoAtrasoCirurgia;
                    }
                    else
                    {
                        $arr['caracteristica_da_cirurgia'][$surgerie['caracteristica_da_cirurgia']]['countTipo'] += 1;
                        $arr['caracteristica_da_cirurgia'][$surgerie['caracteristica_da_cirurgia']]['tempoTotalAtrasoCirurgia'] += $tempoAtrasoCirurgia;
                    }
                }
            }

            if ($data1 != "" && $data_cirurgia != "")
            {
                $intervalo = $data1->diff($data_cirurgia);
                $somatorioDiasCicurgiaCadastroRealizado += $intervalo->d;
                $countCirurgiaCadastroRealizado++;
                if (!isset($arr['cirurgia_cadastro_realizado']['totalDias']))
                {
                    $arr['cirurgia_cadastro_realizado']['totalDias'] = $intervalo->d;
                    $arr['cirurgia_cadastro_realizado']['countCirurgiaCadastroRealizado'] = 1;
                }
                else
                {
                    $arr['cirurgia_cadastro_realizado']['totalDias'] += $intervalo->d;
                    $arr['cirurgia_cadastro_realizado']['countCirurgiaCadastroRealizado'] += 1;
                }

                //echo $surgerie['nome']."<br>";
                if (!isset($arr['cirurgia_cadastro_realizado']['tipoCirurgia'][$surgerie['nome']]['countTipoCirurgia']))
                {
                    $arr['cirurgia_cadastro_realizado']['tipoCirurgia'][$surgerie['nome']]['nome'] = $surgerie['nome'];
                    $arr['cirurgia_cadastro_realizado']['tipoCirurgia'][$surgerie['nome']]['countTipoCirurgia'] = 1;
                    $arr['cirurgia_cadastro_realizado']['tipoCirurgia'][$surgerie['nome']]['totalDiasTipo'] = $intervalo->d;
                }
                else
                {
                    $arr['cirurgia_cadastro_realizado']['tipoCirurgia'][$surgerie['nome']]['countTipoCirurgia'] += 1;
                    $arr['cirurgia_cadastro_realizado']['tipoCirurgia'][$surgerie['nome']]['totalDiasTipo'] += $intervalo->d;
                }

                if (!isset($arr['cirurgia_cadastro_realizado']['porte'][$surgerie['porte_da_cirurgia']]['totalDiasPorte']))
                {
                    $arr['cirurgia_cadastro_realizado']['porte'][$surgerie['porte_da_cirurgia']]['totalDiasPorte'] = $intervalo->d;
                    $arr['cirurgia_cadastro_realizado']['porte'][$surgerie['porte_da_cirurgia']]['countCirurgiaPorte'] = 1;
                }
                else
                {
                    $arr['cirurgia_cadastro_realizado']['porte'][$surgerie['porte_da_cirurgia']]['totalDiasPorte'] += $intervalo->d;
                    $arr['cirurgia_cadastro_realizado']['porte'][$surgerie['porte_da_cirurgia']]['countCirurgiaPorte'] += 1;
                }

                if (!isset($arr['cirurgia_cadastro_realizado']['convenio'][$surgerie['hospitalization']['complementary_health']]['countConvenio']))
                {
                    //echo $a++;
                    $arr['cirurgia_cadastro_realizado']['convenio'][$surgerie['hospitalization']['complementary_health']]['nome'] = $surgerie['hospitalization']['complementary_health'];

                    $arr['cirurgia_cadastro_realizado']['convenio'][$surgerie['hospitalization']['complementary_health']]['countConvenio'] = 1;
                    $arr['cirurgia_cadastro_realizado']['convenio'][$surgerie['hospitalization']['complementary_health']]['totalDiasConvenio'] = $intervalo->d;
                }
                else
                {
                    $arr['cirurgia_cadastro_realizado']['convenio'][$surgerie['hospitalization']['complementary_health']]['countConvenio'] += 1;
                    $arr['cirurgia_cadastro_realizado']['convenio'][$surgerie['hospitalization']['complementary_health']]['totalDiasConvenio'] += $intervalo->d;
                }
            }
        }

        foreach ($arr['porte'] as $key => $porte)
        {
            $somatorioDiasP = $porte['totalDiasPorte'];
            $countDiasP = $porte['countCirurgiaPorte'];
            $mediaP = $somatorioDiasP / $countDiasP;
            $arr['porte'][$key]['mediaDiasPorte'] = number_format($mediaP, 2, '.', '');

            if (isset($porte['tempoTotalAtrasoCirurgia']))
            {
                $somatorioAtrasoCirurgia = $porte['tempoTotalAtrasoCirurgia'];
                $arr['porte'][$key]['tempoTotalAtrasoCirurgia'] = $porte['tempoTotalAtrasoCirurgia'] . 'h';
                $arr['porte'][$key]['countPorteTempoAtrasoCirurgia'] = $countPortetempoAtrasoCirurgia;
                $arr['porte'][$key]['tempoMedioAtrasoCirurgia'] = ($somatorioAtrasoCirurgia / $countPortetempoAtrasoCirurgia) . 'h';
            }
            else
            {
                $arr['porte'][$key]['tempoTotalAtrasoCirurgia'] = 0;
                $arr['porte'][$key]['countPorteTempoAtrasoCirurgia'] = 0;
                $arr['porte'][$key]['tempoMedioAtrasoCirurgia'] = 0;
            }
            //$arr['porte'][$key]['countAtrasoCirurgiaMedia'] = $countPortetempoAtrasoCirurgia;
        }

        foreach ($arr['tipoCirurgia'] as $key => $tipo)
        {
            $totalDiasTipo = $tipo['totalDiasTipo'];
            $countDiasT = $tipo['countTipoCirurgia'];
            $mediaT = $totalDiasTipo / $countDiasT;
            $arr['tipoCirurgia'][$key]['mediaTotalDiasTipo'] = number_format($mediaT, 2, '.', '');

            if (isset($tipo['tempoTotalAtrasoCirurgia']))
            {
                $somatorioAtrasoCirurgia = $tipo['tempoTotalAtrasoCirurgia'];
                $arr['tipoCirurgia'][$key]['tempoTotalAtrasoCirurgia'] = $tipo['tempoTotalAtrasoCirurgia'] . 'h';
                $arr['tipoCirurgia'][$key]['countPorteTempoAtrasoCirurgia'] = $countTipoCirurgiatempoAtrasoCirurgia;
                $arr['tipoCirurgia'][$key]['tempoMedioAtrasoCirurgia'] = ($somatorioAtrasoCirurgia / $countTipoCirurgiatempoAtrasoCirurgia) . 'h';
            }
            else
            {
                $arr['tipoCirurgia'][$key]['tempoTotalAtrasoCirurgia'] = 0;
                $arr['tipoCirurgia'][$key]['countPorteTempoAtrasoCirurgia'] = $countTipoCirurgiatempoAtrasoCirurgia;
                $arr['tipoCirurgia'][$key]['tempoMedioAtrasoCirurgia'] = ($somatorioAtrasoCirurgia / $countTipoCirurgiatempoAtrasoCirurgia) . 'h';
            }
        }

        foreach ($arr['convenio'] as $key => $convenio)
        {
            $totalDiasConvenio = $convenio['totalDiasConvenio'];
            $countDiasConvenio = $convenio['countConvenio'];
            $mediaP = $totalDiasConvenio / $countDiasConvenio;
            $arr['convenio'][$key]['mediaTotalDiasConvenio'] = number_format($mediaP, 2, '.', '');

            $somatorioAtrasoCirurgia = $convenio['tempoTotalAtrasoCirurgia'];
            $arr['convenio'][$key]['tempoTotalAtrasoCirurgia'] = $convenio['tempoTotalAtrasoCirurgia'] . 'h';
            $arr['convenio'][$key]['countPortetempoAtrasoCirurgia'] = $countPortetempoAtrasoCirurgia;
            $arr['convenio'][$key]['tempoMedioAtrasoCirurgia'] = ($somatorioAtrasoCirurgia / $countPortetempoAtrasoCirurgia) . 'h';
        }

        foreach ($arr['cirurgia_cadastro_realizado']['tipoCirurgia'] as $key => $tipo)
        {
            $totalDiasTipo = $tipo['totalDiasTipo'];
            $countDiasT = $tipo['countTipoCirurgia'];
            $mediaT = $totalDiasTipo / $countDiasT;
            $arr['cirurgia_cadastro_realizado']['tipoCirurgia'][$key]['media'] = number_format($mediaT, 2, '.', '');
        }

        foreach ($arr['cirurgia_cadastro_realizado']['porte'] as $key => $porte)
        {
            $somatorioDiasP = $porte['totalDiasPorte'];
            $countDiasP = $porte['countCirurgiaPorte'];
            $mediaP = $somatorioDiasP / $countDiasP;
            $arr['cirurgia_cadastro_realizado']['porte'][$key]['media'] = number_format($mediaP, 2, '.', '');
        }

        foreach ($arr['cirurgia_cadastro_realizado']['convenio'] as $key => $convenio)
        {
            $somatorioDiasConvenio = $convenio['totalDiasConvenio'];
            $countDiasConvenio = $convenio['countConvenio'];
            $mediaP = $somatorioDiasConvenio / $countDiasConvenio;
            $arr['cirurgia_cadastro_realizado']['convenio'][$key]['media'] = number_format($mediaP, 2, '.', '');
        }

        if (isset($arr['tempoAtrasoSalaCirurgica']))
        {
            foreach ($arr['tempoAtrasoSalaCirurgica'] as $key => $salaCirurgica)
            {
                $somatorioAtrasoCirurgia = $salaCirurgica['tempoTotalAtrasoCirurgia'];
                $countSala = $salaCirurgica['countSala'];
                $mediaP = $somatorioAtrasoCirurgia / $countSala;
                $arr['tempoAtrasoSalaCirurgica'][$key]['media'] = number_format($mediaP, 2, '.', '');
            }
        }

        foreach ($arr['diaDeSemana'] as $key => $diaSemana)
        {
            $somatorioAtrasoDiaSemana = $diaSemana['tempoTotalAtrasoCirurgia'];
            $countDiaSemana = $diaSemana['countDiaSemana'];
            $mediaP = $somatorioAtrasoDiaSemana / $countDiaSemana;
            $arr['diaDeSemana'][$key]['media'] = number_format($mediaP, 2, '.', '');
        }

        foreach ($arr['caracteristica_da_cirurgia'] as $key => $caracteristica_da_cirurgia)
        {
            $somatorioAtrasoCaracteristicaCirurgia = $caracteristica_da_cirurgia['tempoTotalAtrasoCirurgia'];
            $countTipo = $caracteristica_da_cirurgia['countTipo'];
            $mediaP = $somatorioAtrasoCaracteristicaCirurgia / $countTipo;
            $arr['caracteristica_da_cirurgia'][$key]['media'] = number_format($mediaP, 2, '.', '');
        }

        //$arr['taxaLiberaçãoSala']['tempoTerminoAtraso'] = $tempoTerminoAtraso;

        //$arr['taxaLiberaçãoSala']['countTaxaTerminoAtraso'] = $countTaxaTerminoAtraso;
        //$tempoTerminoAtraso += $intervaloDataHoraTerminoCirugia->h;

        $arr['totalDias'] = $somatorioDias;
        $arr['totalCirurgia'] = $count;
        $media = $somatorioDias / $count;
        $media_cirurgia_cadastro_realizado = $somatorioDiasCicurgiaCadastroRealizado / $countCirurgiaCadastroRealizado;
        $arr['totalTempoMedia'] = number_format($media, 2, '.', '');
        $arr['cirurgia_cadastro_realizado']['totalTempoMedia'] = number_format($media_cirurgia_cadastro_realizado, 2, '.', '');
        $arr['taxaCancelamentoCirurgia'] = $taxaCancelamento;
        $arr['taxaAdiamentoCirurgia'] = $taxaAdiamento;
        $arr['tempoTotalAtrasoCirurgia'] = $tempoAtraso . 'h';
        $arr['countTaxaAtrasoCirurgia'] = $countTaxaAtraso;

        $tempoMedioAtrasoCirurgia = $tempoAtraso / $countTaxaAtraso;
        $arr['tempoMedioAtrasoCirurgia'] = $tempoMedioAtrasoCirurgia . 'h';
        // echo "<pre>";
        // print_r($arr);

        $this->viewBuilder()->options([
            'pdfConfig' => [
                'orientation' => 'portrait',
                'filename' => 'Relatório de centro Cirúrgico.pdf',
            ],
        ]);

        $this->set('surgeries', $arr);
        $this->set('data', $data);
        $this->set('hospitalizations', $hospitalizations);
    }

    public function neonatal()
    {
        $data = null;
        $session = $this->request->session();

        $date_before = $session->read('date_before');
        $date_after = $session->read('date_after');
        $unit_id = $session->read('selected-unit');
        $neonatal_table = TableRegistry::get('NeonatalChecklists');
        $hospitalization_table = TableRegistry::get('Hospitalizations');
        $hospitalizations = $hospitalization_table->find('all', [
            'contain' => ["NeonatalChecklists" => ['Anomalias', 'Inspections']],
        ])
            ->where(['Hospitalizations.unit_id' => $unit_id, 'Hospitalizations.status <>' => 'obito'])
            ->orwhere(['Hospitalizations.unit_id' => $unit_id, 'Hospitalizations.status' => 'obito', 'Hospitalizations.data_alta_obito < ' => $date_after->format("Y-m-d")])
            ->toArray();

        $data = $neonatal_table->relatorioNeonatal($hospitalizations);

            // foreach ($hospitalizations as $hospitalization) {

            //     //$data = $hospitalization["neonatal_checklists"];

            //     foreach ($hospitalization["neonatal_checklists"] as $key => $neonatal) {

            //         //$netonatal['paridade']

            //         if ($neonatal['paridade']) {

            //             if (isset($data[$neonatal['id']]["paridade"])) {

            //                 $data[$neonatal['id']]["paridade"]++;

            //             } else {

            //                 $data[$neonatal['id']]["paridade"] = 1;

            //             }

            //         }

            //         if ($neonatal['paridade']) {

            //             if (isset($data[$neonatal['id']]["paridade"])) {

            //                 $data[$neonatal['id']]["paridade"]++;

            //             } else {

            //                 $data[$neonatal['id']]["paridade"] = 1;

            //             }

            //         }

            //     }
                    // }

        $this->viewBuilder()->options([
            'pdfConfig' => [
                'orientation' => 'portrait',
                'filename' => 'Relatório neonatal.pdf',
            ],
        ]);
        //$hospitalization->toArray();
        $this->set('hospitalizations', $hospitalizations);
        $this->set('data', $data);
    }

    public function escores()
    {
        $data = [
            'dates' => [],
            'consolidado' => [],
        ];

        $apache_ii_scores_table = TableRegistry::get('ApacheIiScores');
        $hospitalization_table = TableRegistry::get('Hospitalizations');
        $pdf_orientation = 'portrait';

        $session = $this->request->session();
        $unit_id = $session->read('selected-unit');
        $relatorio_type = $session->read('relatorio_type');

        $date_before = $session->read('date_before');
        $date_after = $session->read('date_after');

        $interval = new \DateInterval('P1M');
        //echo "<pre>";
        //print_r($interval);
        $daterange = new \DatePeriod($date_before, $interval, $date_after);
        $data['daterange'] = $daterange;
        $month_name = [
            '01' => 'Jan',
            '02' => 'Fev',
            '03' => 'Mar',
            '04' => 'Abr',
            '05' => 'Mai',
            '06' => 'Jun',
            '07' => 'Jul',
            '08' => 'Ago',
            '09' => 'Set',
            '10' => 'Out',
            '11' => 'Nov',
            '12' => 'Dez',
        ];

        $hospitalizations = $hospitalization_table
            ->find('all', [
                'contain' => [],
            ])
            ->where(['Hospitalizations.unit_id' => $unit_id, 'Hospitalizations.status' => 'internado'])
            ->orwhere(['Hospitalizations.unit_id' => $unit_id, 'Hospitalizations.status <>' => 'internado', 'Hospitalizations.data_alta_obito <=' => $date_after->format("Y-m-d")])
            ->toArray();

        if ($relatorio_type == 'parcial')
        {
            //$this->render('detailed_clinic_tev_protocols');
            $pdf_orientation = 'landscape';

            foreach ($daterange as $date) //gera os dados do relatorio de acordo com o mes
            {
                //primeiro dia do mes
                $startOfMonth = new \DateTime($date->format('Y-m-d'));
                $startOfMonth->modify('first day of this month')->setTime(0, 0, 0);

                //ultimo dia do mes
                $endOfMonth = new \DateTime($date->format('Y-m-d'));
                $endOfMonth->modify('last day of this month')->setTime(23, 59, 59);

                $hospitalizations_period = $hospitalization_table->find('all', [
                    'contain' => [],
                    ])
                    ->where(['Hospitalizations.unit_id' => $unit_id, 'Hospitalizations.status' => 'internado'])
                    ->orwhere(['Hospitalizations.unit_id' => $unit_id, 'Hospitalizations.status <>' => 'internado', 'Hospitalizations.data_alta_obito <=' => $endOfMonth->format("Y-m-d")])
                    ->toArray();

                $data['dates'][$date->format("m")] = $apache_ii_scores_table->getRelatorioData($hospitalizations_period, $startOfMonth, $endOfMonth);
            }
        }
        else
        {
            $data['consolidado'] = $apache_ii_scores_table->getRelatorioData($hospitalizations, $date_before, $date_after);
        }

        $this->viewBuilder()->options([
            'pdfConfig' => [
                'orientation' => $pdf_orientation,
                'filename' => 'Relatório Escores.pdf',
            ],
        ]);

        $count_mes = count($data['dates']);

        $count_mes = ($count_mes * 2) + 1;

        $this->set('month_name', $month_name);
        $this->set('hospitalizations', $hospitalizations);
        $this->set('data', $data);
        $this->set('count_mes', $count_mes);


        if ($relatorio_type == 'parcial')
        {
            $this->render('detailed_escores');

        }
    }

    //Gerenciamento de leito
    public function bedManagement()
    {
        $this->set('title', 'Mapa de Leitos');
        $data = [];

        $session = $this->request->session();
        $date_before = $session->read('date_before');
        $date_after = $session->read('date_after');
        $unit_id = $session->read('selected-unit');
        $sector_table = TableRegistry::get('Sectors');
        $hospitalizations_table = TableRegistry::get('Hospitalizations');
        $historic_table = TableRegistry::get('HistoricPatientMovements');
        //$today = date();

        $hospital_ids = [];
        $sectors_arr = [];
        $session = $this->request->session();
        $unit_id = $session->read('selected-unit');
        $cont = 0;

        $hospital_result = $hospitalizations_table
            ->find()
            ->where(['Hospitalizations.unit_id =' => $unit_id])
            ->toArray()
        ;

        foreach ($hospital_result as $key => $value) //pega o id das internações separando por tipo
        {
            array_push($hospital_ids, $value->id);
        }

        $patient_movement = $historic_table
            ->find()
            ->contain(['Rooms.Sectors'])
            ->where([])
            ->toArray()
        ;

        $sectors = $sector_table
            ->find('all', [
                'contain' => [
                    'Rooms.HistoricPatientMovements' => function (\Cake\ORM\Query $q)
                {
                        return $q
                            ->autoFields(true)
                        ;
                    },
                ],

                'conditions' => [
                    'Sectors.unit_id' => $unit_id,
                ],
            ])
            ->toArray()
        ;

        foreach ($sectors as $key => $value)
        {
            $sectors_arr[$value['id']] = $value;
            $dummy_arr = [
                "occupation" => 0,
                "available" => $value->limit_of_rooms,
                "name" => $value->name,
                "capacity" => $value->limit_of_rooms,
            ];

            for ($i = 1; $i < 9; $i++)
            {
                $data[$i]['sectors'][$value['id']] = $dummy_arr;
            }

// $data[2]['sectors'][$value['id']] = $dummy_arr;

// $data[3]['sectors'][$value['id']] = $dummy_arr;

// $data[4]['sectors'][$value['id']] = $dummy_arr;

// $data[5]['sectors'][$value['id']] = $dummy_arr;

// $data[6]['sectors'][$value['id']] = $dummy_arr;

// $data[7]['sectors'][$value['id']] = $dummy_arr;

// $data[8]['sectors'][$value['id']] = $dummy_arr;
            //array_push($data['sectors'], $dummy_arr);
        }

        foreach ($patient_movement as $key => $value)
        {
            $data_termino = null;

            if ($value['data_inicio'])
            {
                if ($value['data_termino'])
                {
                    $data_termino = date($value['data_termino']);
                }
                elseif ($value['data_previsao_alta'])
                {
                    $data_termino = date($value['data_previsao_alta']);
                }

                for ($i = 1; $i < 9; $i++)
                {
                    if (strtotime($data_termino) >= strtotime("Ymd + " . $i . " days"))
                    {
                        if (isset($data[$i]['sectors'][$value['room']['sector_id']]['occupation']))
                        {
                            $data[$i]['sectors'][$value['room']['sector_id']]['occupation']++;
                        }

                        if (isset($data[$i]['sectors'][$value['room']['sector_id']]['available']))
                        {
                            $data[$i]['sectors'][$value['room']['sector_id']]['available']--;
                        }
                    }
                }
            }
        }

        $this->viewBuilder()->options([
            'pdfConfig' => [
                'orientation' => 'portrait',
                'filename' => 'Relatório de gerenciamento de leito.pdf',
            ],
        ]);

        $this->set('data', $data);
        $this->set('sectors_arr', $sectors_arr);
        $this->set('patient_movement', $patient_movement);
    }

    //relatorio famarcia
    public function prescription()
    {
        $data = null;
        $pdf_orientation = 'portrait';
        $session = $this->request->session();

        $unit_id = $session->read('selected-unit');
        $relatorio_type = $session->read('relatorio_type');
        $date_before = $session->read('date_before');
        $date_after = $session->read('date_after');        
        
        $prescription_table = TableRegistry::get('Prescriptions');
        $hospitalization_table = TableRegistry::get('Hospitalizations');
        $sector_table = TableRegistry::get('Sectors');

        //daterange
        $interval = new \DateInterval('P1M');
        $daterange = new \DatePeriod($date_before, $interval, $date_after);

        $date_before->modify('first day of this month')->setTime(0, 0, 0);
        $date_after->modify('last day of this month')->setTime(23, 59, 59);

        $month_name = [
            '01' => 'Jan',
            '02' => 'Fev',
            '03' => 'Mar',
            '04' => 'Abr',
            '05' => 'Mai',
            '06' => 'Jun',
            '07' => 'Jul',
            '08' => 'Ago',
            '09' => 'Set',
            '10' => 'Out',
            '11' => 'Nov',
            '12' => 'Dez',
        ];

         $sectors = $sector_table
            ->find('all', [
                'conditions' => [
                    'Sectors.unit_id' => $unit_id,
                ],
            ])
            ->toArray();

        if ($relatorio_type == 'parcial')
        {
            $pdf_orientation = 'landscape';

            foreach ($daterange as $date) //gera os dados do relatorio de acordo com o mes
            {
                //primeiro dia do mes
                $startOfMonth = new \DateTime($date->format('Y-m-d'));
                $startOfMonth->modify('first day of this month')->setTime(0, 0, 0);

                //ultimo dia do mes
                $endOfMonth = new \DateTime($date->format('Y-m-d'));
                $endOfMonth->modify('last day of this month')->setTime(23, 59, 59);

                $hospitalizations_period = $hospitalization_table->find('all')
                    ->contain(["Prescriptions.Medications.Signs"])
                    ->where(['Hospitalizations.unit_id' => $unit_id, 'Hospitalizations.status' => 'internado'])
                    ->orwhere(['Hospitalizations.unit_id' => $unit_id, 'Hospitalizations.status <>' => 'internado', 'Hospitalizations.data_alta_obito <=' => $endOfMonth->format("Y-m-d")])
                    ->toArray();

                $data['dates'][$date->format("m")] = $prescription_table->getRelatorioData($hospitalizations_period, $sectors, $startOfMonth, $endOfMonth);
            }
        }
        else
        {
            $hospitalizations = $hospitalization_table
                ->find('all')
                ->contain(["Prescriptions.Medications.Signs"])
                ->where(['Hospitalizations.unit_id' => $unit_id, 'Hospitalizations.status' => 'internado'])
                ->orwhere(['Hospitalizations.unit_id' => $unit_id, 'Hospitalizations.status <>' => 'internado', 'Hospitalizations.data_alta_obito <=' => $date_after->format("Y-m-d")])
                ->toArray();

            //$data['consolidado'] = $prescription_table->getRelatorioData($hospitalizations, $sectors, $date_before, $date_after);
                $data = $prescription_table->getRelatorioData($hospitalizations, $sectors, $date_before, $date_after);
        }
        
        $this->viewBuilder()->options([
            'pdfConfig' => [
                'orientation' => $pdf_orientation,
                'filename' => 'Relatório de farmacia.pdf',
            ],
        ]);
        
        $this->set('data', $data);
        $this->set('month_name', $month_name);

        if ($relatorio_type == 'parcial')
        {
            $this->render('detailed_prescription');
        }
    }

    
    public function cardiacSurgeryChecklists()
    {
        $data = null;
        $cardiac_surgery_checklist = null;
        $session = $this->request->session();
        $pdf_orientation = 'portrait';
        
        $date_before = $session->read('date_before');
        $date_after = $session->read('date_after');

        $relatorio_type = $session->read('relatorio_type');
        $unit_id = $session->read('selected-unit');

        
        //daterange
        $interval = new \DateInterval('P1M');
        $daterange = new \DatePeriod($date_before, $interval, $date_after);


         $month_name = [
            '01' => 'Jan',
            '02' => 'Fev',
            '03' => 'Mar',
            '04' => 'Abr',
            '05' => 'Mai',
            '06' => 'Jun',
            '07' => 'Jul',
            '08' => 'Ago',
            '09' => 'Set',
            '10' => 'Out',
            '11' => 'Nov',
            '12' => 'Dez',
        ];

        
        $cardiac_surgery_checklists_table = TableRegistry::get('CardiacSurgeryChecklists');

        $hospitalizations_table = TableRegistry::get('Hospitalizations');

        $hospitalizations = $hospitalizations_table->find('all')
            ->where(['Hospitalizations.unit_id' => $unit_id]);

        $hospitalization_ids = [];

        foreach ($hospitalizations as $key => $value)
        {
            array_push($hospitalization_ids, $value->id);
        }

        $cardiac_surgery_checklists = $cardiac_surgery_checklists_table->find('all', [
            'conditions' => ['hospitalization_id in ' => $hospitalization_ids],
        ]);


        if ($relatorio_type == 'parcial')
        {
         
            $pdf_orientation = 'landscape';

            foreach ($daterange as $date) //gera os dados do relatorio de acordo com o mes
            {
                

                $monthCardiacSurgeries = [];
                $hospitalizations_period=[];

                //primeiro dia do mes
                $startOfMonth = new \DateTime($date->format('Y-m-d'));
                $startOfMonth->modify('first day of this month')->setTime(0, 0, 0);

                //ultimo dia do mes
                $endOfMonth = new \DateTime($date->format('Y-m-d'));
                $endOfMonth->modify('last day of this month')->setTime(23, 59, 59);
                
               
                $firstDay = $startOfMonth->format("Y-m-d");
                $lastDay  = $endOfMonth->format("Y-m-d");

                $hospitalizations_period = $hospitalizations_table->find('all', [
                    'contain' => [
                        "CardiacSurgeryChecklists" =>  function ($q) use($firstDay,$lastDay) {
                           return $q->find('all')->where([
                                'CardiacSurgeryChecklists.created >= ' =>$firstDay,
                                'CardiacSurgeryChecklists.created <= ' => $lastDay
                           ]);
                        }
                    ]
                ])
                ->where(['Hospitalizations.unit_id' => $unit_id, 'Hospitalizations.status' => 'internado'])
                ->orwhere(['Hospitalizations.unit_id' => $unit_id, 'Hospitalizations.status <>' => 'internado', 'Hospitalizations.data_alta_obito <=' => $endOfMonth->format("Y-m-d")])
                ->toArray();


                if(!empty($hospitalizations_period)){

                    foreach ($hospitalizations_period as $key => $hospitalization_period) {

                        if(!empty($hospitalization_period['cardiac_surgery_checklists'])){

                            foreach ($hospitalization_period['cardiac_surgery_checklists'] as $key => $cardiac_surgery_checklist) {
                               array_push($monthCardiacSurgeries, $cardiac_surgery_checklist);
                            }

                        }
                        
                    }    

                }

                $data['dates'][$date->format("m")] = $cardiac_surgery_checklists_table->relatorioCirurgiaCardiaca($monthCardiacSurgeries);
            }
        }
        else
        {
           $cardiac_surgery_checklist = $cardiac_surgery_checklists_table->relatorioCirurgiaCardiaca($cardiac_surgery_checklists);
        }


        $this->viewBuilder()->options([
            'pdfConfig' => [
                'orientation' => $pdf_orientation,
                'filename' => 'Relatório de Checklist Cirurgia Cardíaca.pdf',
            ],
        ]);

        $this->set('month_name', $month_name);
        $this->set('data', $data);
        $this->set('cardiac_surgery_checklist', $cardiac_surgery_checklist);

        if ($relatorio_type == 'parcial'){
            $this->render('detailed_cardiac_surgery_checklist');
        }
        
       
    }

    public function medicalOpinionRequest()
    {
        $data = null;
        $session = $this->request->session();
        $date_before = $session->read('date_before');
        $date_after = $session->read('date_after');

        $unit_id = $session->read('selected-unit');
        $medical_opinion_table = TableRegistry::get('MedicalOpinionRequests');

        $hospitalizations_table = TableRegistry::get('Hospitalizations');
        $specialty_table = TableRegistry::get('Specialties');
        $setores_table = TableRegistry::get('Sectors');

        $setores = $setores_table->find('all')
            ->where(['Sectors.unit_id' => $unit_id])
            ->toArray();

        $specialties = $specialty_table->find('all')
        //->where(['Specialties.unit_id' => $unit_id])
            ->toArray();

        $hospitalizations = $hospitalizations_table->find('all')
            ->where(['Hospitalizations.unit_id' => $unit_id])
            ->contain(['MedicalOpinionRequests.Specialties', 'MedicalOpinionRequests.Sectors', 'MedicalOpinionRequests.MedicalOpinionAnswers'])
            ->toArray();

        $hospitalization_ids = [];

        foreach ($hospitalizations as $key => $value)
        {
            array_push($hospitalization_ids, $value->id);
        }

        $data = $medical_opinion_table->relatorioMedicalOpinion($hospitalizations, $date_before, $date_after, $setores, $specialties);

        $this->viewBuilder()->options([
            'pdfConfig' => [
                'orientation' => 'portrait',
                'filename' => 'Relatório de parecer médico.pdf',
            ],
        ]);

        //echo '<pre>';
        //print_r($medical_opinions);
        $this->set('data', $data);
        $this->set('hospitalizations', $hospitalizations);
    }

    public function checklistObstetrico()
    {
        $data = null;
        $session = $this->request->session();

        $date_before = $session->read('date_before');
        $date_after = $session->read('date_after');
        $unit_id = $session->read('selected-unit');
        $obstetric_table = TableRegistry::get('ObstetricSurgeryChecklists');
        $hospitalization_table = TableRegistry::get('Hospitalizations');
        $hospitalizations = $hospitalization_table->find('all', [
            'contain' => ["ObstetricSurgeryChecklists"],
        ])
            ->where(['Hospitalizations.unit_id' => $unit_id, 'Hospitalizations.status <>' => 'obito'])
            ->orwhere(['Hospitalizations.unit_id' => $unit_id, 'Hospitalizations.status' => 'obito',
                'Hospitalizations.data_alta_obito < ' => $date_after->format("Y-m-d")])
            ->toArray();

        $data = $obstetric_table->getRelatorioData($hospitalizations);

        $this->viewBuilder()->options([
            'pdfConfig' => [
                'orientation' => 'portrait',
                'filename' => 'Relatório obstétrico.pdf',
            ],
        ]);
        //$hospitalization->toArray();
        $this->set('hospitalizations', $hospitalizations);
        $this->set('data', $data);
    }

    public function checklistSeguranca()
    {
        $data = null;
        $session = $this->request->session();

        $date_before = $session->read('date_before');
        $date_after = $session->read('date_after');
        $unit_id = $session->read('selected-unit');
        $relatorio_type = $session->read('relatorio_type');
        $pdf_orientation = 'portrait';

        $checklist_table = TableRegistry::get('DailySafetyChecklists');
        $hospitalization_table = TableRegistry::get('Hospitalizations');

        //daterange
        $interval = new \DateInterval('P1M');
        $daterange = new \DatePeriod($date_before, $interval, $date_after);

        $month_name = [
            '01' => 'Jan',
            '02' => 'Fev',
            '03' => 'Mar',
            '04' => 'Abr',
            '05' => 'Mai',
            '06' => 'Jun',
            '07' => 'Jul',
            '08' => 'Ago',
            '09' => 'Set',
            '10' => 'Out',
            '11' => 'Nov',
            '12' => 'Dez',
        ];
    
        if ($relatorio_type == 'parcial')
        {
            $pdf_orientation = 'landscape';

            foreach ($daterange as $date) //gera os dados do relatorio de acordo com o mes
            {
                //primeiro dia do mes
                $startOfMonth = new \DateTime($date->format('Y-m-d'));
                $startOfMonth->modify('first day of this month')->setTime(0, 0, 0);

                //ultimo dia do mes
                $endOfMonth = new \DateTime($date->format('Y-m-d'));
                $endOfMonth->modify('last day of this month')->setTime(23, 59, 59);

                $hospitalizations_period = $hospitalization_table->find('all')
                    ->contain(["DailySafetyChecklists"])
                    ->where(['Hospitalizations.unit_id' => $unit_id, 'Hospitalizations.status' => 'internado'])
                    ->orwhere(['Hospitalizations.unit_id' => $unit_id, 'Hospitalizations.status <>' => 'internado', 'Hospitalizations.data_alta_obito <=' => $endOfMonth->format("Y-m-d")])
                    ->toArray();

                $data['dates'][$date->format("m")] = $checklist_table->getRelatorioData($hospitalizations_period, $startOfMonth, $endOfMonth);
            }
        }
        else
        {
            $hospitalizations = $hospitalization_table
                ->find('all', [
                    'contain' => ["DailySafetyChecklists"],
                ])

                ->where(['Hospitalizations.unit_id' => $unit_id, 'Hospitalizations.status' => 'internado'])

                ->orwhere(['Hospitalizations.unit_id' => $unit_id, 'Hospitalizations.status <>' => 'internado',
                    'Hospitalizations.data_alta_obito <=' => $date_after->format("Y-m-d")])
                ->toArray();

            $data = $checklist_table->getRelatorioData($hospitalizations, $date_before, $date_after);
        }

        $this->viewBuilder()->options([
            'pdfConfig' => [
                'orientation' => $pdf_orientation,
                'filename' => 'Relatório de segurança.pdf',
            ],
        ]);

        $this->set('month_name', $month_name);
        $this->set('data', $data);

        if ($relatorio_type == 'parcial')
        {
            $this->render('detailed_checklist_seguranca');
        }
    }

    public function riscos()
    {
        $data = null;
        $session = $this->request->session();

        $date_before = $session->read('date_before');
        $date_after = $session->read('date_after');
        $unit_id = $session->read('selected-unit');

        $risco_table = TableRegistry::get('IdentificationOfRisks');
        $braden_table = TableRegistry::get('BradenScales');
        $nas_table = TableRegistry::get('NasScores');
        $sector_table = TableRegistry::get('Sectors');
        $fugulin_table = TableRegistry::get('FugulinBradenScales');
        $hospitalization_table = TableRegistry::get('Hospitalizations');

        $sectors = $sector_table
            ->find()
            ->where(['Sectors.unit_id' => $unit_id])
            ->toArray();

        $hospitalizations = $hospitalization_table
            ->find()
            ->contain(["IdentificationOfRisks", "IdentificationOfRisks.Sectors", 'IdentificationOfRisks',
                "FugulinBradenScales", 'BradenScales', 'SkinIntegrities', 'NasScores'])

            ->where(['Hospitalizations.unit_id' => $unit_id, 'Hospitalizations.status <>' => 'obito'])

            ->orwhere(['Hospitalizations.unit_id' => $unit_id, 'Hospitalizations.status' => 'obito',
                'Hospitalizations.data_alta_obito < ' => $date_after->format("Y-m-d")])
            ->toArray();

        $data['risco'] = $risco_table->getRelatorioData($hospitalizations, $date_before, $date_after, $sectors);
        $data['braden'] = $braden_table->getRelatorioData($hospitalizations, $date_before, $date_after, $sectors);
        $data['fugulin'] = $fugulin_table->getRelatorioData($hospitalizations, $date_before, $date_after, $sectors);
        $data['nas'] = $nas_table->getRelatorioData($hospitalizations, $date_before, $date_after, $sectors);

        $this->viewBuilder()->options([
            'pdfConfig' => [
                'orientation' => 'portrait',
                'filename' => 'Relatório de Enfermagem.pdf',
            ],
        ]);

        $this->set('hospitalizations', $hospitalizations);
        $this->set('data', $data);
    }

    public function fisioterapia()
    {
        $data =
            [
            'barthel' => [],
            'mrc' => [],
        ];

        $session = $this->request->session();
        $date_before = $session->read('date_before');
        $date_after = $session->read('date_after');
        $unit_id = $session->read('selected-unit');

        $barthel_table = TableRegistry::get('BarthelIndexes');
        $sectors_table = TableRegistry::get('Sectors');
        $escala_table = TableRegistry::get('StrengthAssessmentScales');
        $hospitalization_table = TableRegistry::get('Hospitalizations');

        $hospitalizations = $hospitalization_table
            ->find('all', [
                'contain' => ["BarthelIndexes", "StrengthAssessmentScales", "Diagnostics"],
            ])
            ->where(['Hospitalizations.unit_id' => $unit_id, 'Hospitalizations.status <>' => 'obito'])
            ->orwhere(['Hospitalizations.unit_id' => $unit_id, 'Hospitalizations.status' => 'obito', 'Hospitalizations.data_alta_obito < ' => $date_after->format("Y-m-d")])
            ->toArray()
        ;

        $sectors = $sectors_table
            ->find('all')
            ->where(['Sectors.unit_id' => $unit_id])
            ->toArray();

        $data['barthel'] = $barthel_table->getRelatorioData($hospitalizations, $date_before, $date_after);
        $data['escala'] = $escala_table->getRelatorioData($hospitalizations, $sectors, $date_before, $date_after);

        $this->viewBuilder()->options([
            'pdfConfig' => [
                'orientation' => 'portrait',
                'filename' => 'Relatório de fisioterapia.pdf',
            ],
        ]);
        //$hospitalization->toArray();
        $this->set('hospitalizations', $hospitalizations);
        $this->set('data', $data);
    }

    public function checklistCirurgico()
    {
        $data = [
            'dates' => [],
            'consolidado' => [],
        ];

        $checklist_table = TableRegistry::get('SurgicalChecklists');
        $hospitalization_table = TableRegistry::get('Hospitalizations');
        $pdf_orientation = 'portrait';
        $session = $this->request->session();
        $unit_id = $session->read('selected-unit');
        $relatorio_type = $session->read('relatorio_type');

        $date_before = $session->read('date_before');
        $date_after = $session->read('date_after');

        $interval = new \DateInterval('P1M');
        //echo "<pre>";
        //print_r($interval);
        $daterange = new \DatePeriod($date_before, $interval, $date_after);
        $data['daterange'] = $daterange;
        $month_name = [
            '01' => 'Jan',
            '02' => 'Fev',
            '03' => 'Mar',
            '04' => 'Abr',
            '05' => 'Mai',
            '06' => 'Jun',
            '07' => 'Jul',
            '08' => 'Ago',
            '09' => 'Set',
            '10' => 'Out',
            '11' => 'Nov',
            '12' => 'Dez',
        ];

        $hospitalizations = $hospitalization_table
            ->find('all', [
                'contain' => ["SurgicalChecklists", "Surgeries" => ['Specialties']],
            ])
            ->where(['Hospitalizations.unit_id' => $unit_id, 'Hospitalizations.status' => 'internado'])
            ->orwhere(['Hospitalizations.unit_id' => $unit_id, 'Hospitalizations.status <>' => 'internado', 'Hospitalizations.data_alta_obito <=' => $date_after->format("Y-m-d")])
            ->toArray();

        if ($relatorio_type == 'parcial')
        {
            //$this->render('detailed_clinic_tev_protocols');
            $pdf_orientation = 'landscape';

            foreach ($daterange as $date) //gera os dados do relatorio de acordo com o mes
            {
                //primeiro dia do mes
                $startOfMonth = new \DateTime($date->format('Y-m-d'));
                $startOfMonth->modify('first day of this month')->setTime(0, 0, 0);

                //ultimo dia do mes
                $endOfMonth = new \DateTime($date->format('Y-m-d'));
                $endOfMonth->modify('last day of this month')->setTime(23, 59, 59);

                $hospitalizations_period = $hospitalization_table->find('all', [
                    'contain' => ["SurgicalChecklists", "Surgeries" => ['Specialties']],
                ])
                ->where(['Hospitalizations.unit_id' => $unit_id, 'Hospitalizations.status' => 'internado'])
                ->orwhere(['Hospitalizations.unit_id' => $unit_id, 'Hospitalizations.status <>' => 'internado', 'Hospitalizations.data_alta_obito <=' => $endOfMonth->format("Y-m-d")])
                ->toArray();

                $data['dates'][$date->format("m")] = $checklist_table->getRelatorioData($hospitalizations_period, $startOfMonth, $endOfMonth);
            }
            $this->set('count_mes', count($data['dates']));
        }
        else
        {
            $data = $checklist_table->getRelatorioData($hospitalizations, $date_before, $date_after);
        }

        $this->viewBuilder()->options([
            'pdfConfig' => [
                'orientation' => $pdf_orientation,
                'filename' => 'Relatório de checklist cirurgico.pdf',
            ],
        ]);
        //$hospitalization->toArray();
        
        $this->set('month_name', $month_name);
        $this->set('hospitalizations', $hospitalizations);
        $this->set('data', $data);        


        if ($relatorio_type == 'parcial')
        {
            $this->render('detailed_checklist_cirurgico');

        }
    }

    public function graficoPlanoVentilatorio()
    {
        $this->viewBuilder()->layout('relatorio');

        $session = $this->request->session();
        $hospitalization_table = TableRegistry::get('Hospitalizations');

        $hospitalization_id = $session->read('selected-hospitalization');

        $hospitalization = $hospitalization_table->find()
            ->where(['Hospitalizations.id' => $hospitalization_id])
            ->contain(['VentilatoryCarePlans', 'Patients'])
            ->first();

        $this->set(compact('hospitalization'));
        $this->set('_serialize', ['hospitalization']);
    }

    public function relatorioEmergencia()
    {
         $data = null;

        $hospitalization_table = TableRegistry::get('Hospitalizations');
        $screening_table = TableRegistry::get('Screenings');
        $unit_table = TableRegistry::get('Units');
        $healh_table = TableRegistry::get('HealthInsurancePlans');
        $professional_table = TableRegistry::get('Professionals');

        $session = $this->request->session();
        $unit_id = $session->read('selected-unit');

        $date_before = $session->read('date_before');
        //$date_before->modify('first day of this month')->setTime(0, 0, 0);

        $date_after = $session->read('date_after');
        //$date_after->modify('last day of this month')->setTime(23, 59, 59);

        $health_insurance_plans = $healh_table->find()
            ->where(['HealthInsurancePlans.unit_id' => $unit_id]);

        $professionals = $professional_table
            ->find()
            ->contain(['Users'])
            ->where(['Professionals.unit_id' => $unit_id])
            ->toArray();

        $hospitalizations = $hospitalization_table->find('all')
            ->contain(['Origins', 'HistoricPatientMovements.Rooms.Sectors', 'Patients', 'Professionals'])
            ->where(['Hospitalizations.unit_id' => $unit_id, 'Hospitalizations.status <>' => 'obito'])
            ->orwhere(['Hospitalizations.unit_id' => $unit_id, 'Hospitalizations.status' => 'obito', 'Hospitalizations.data_alta_obito <= ' => $date_after->format("Y-m-d")])
            ->toArray();

        $screenings = $screening_table->find('all', [
            'contain' => ['Hospitalizations', 'MedicalCares', 'Patients'],
            'conditions' => [
                'Screenings.unit_id' => $unit_id,
                'Screenings.created >\'' . $date_before->format("Y-m-d") . '\' and Screenings.created < \'' . $date_after->format("Y/m/d") . '\'',
            ],
        ])
            ->toArray();

        $data = $screening_table->getRelatorioData($screenings, $date_before, $date_after, $health_insurance_plans, $hospitalizations, $professionals);

        $this->viewBuilder()->options([
            'pdfConfig' => [
                'orientation' => 'portrait',
                'filename' => 'Relatorio de emergência.pdf',
            ],
        ]);

        $this->set('data', $data);
        $this->set('screenings', $screenings);
        $this->set('hospitalizations', $hospitalizations);
    }

    public function clinicTevProtocols()
    {
        $data = [
            'dates' => [],
            'consolidado' => [],
        ];

        $hospitalization_table = TableRegistry::get('Hospitalizations');
        $clinic_tev_protocols_table = TableRegistry::get('ClinicTevProtocols');
        $pdf_orientation = 'portrait';
        $session = $this->request->session();
        $unit_id = $session->read('selected-unit');
        $relatorio_type = $session->read('relatorio_type');
        $tot_de_leitos = 0;

        $date_before = $session->read('date_before');
        $date_after = $session->read('date_after');

        $interval = new \DateInterval('P1M');
        $daterange = new \DatePeriod($date_before, $interval, $date_after);
        $data['daterange'] = $daterange;
        $month_name = [
            '01' => 'Jan',
            '02' => 'Fev',
            '03' => 'Mar',
            '04' => 'Abr',
            '05' => 'Mai',
            '06' => 'Jun',
            '07' => 'Jul',
            '08' => 'Ago',
            '09' => 'Set',
            '10' => 'Out',
            '11' => 'Nov',
            '12' => 'Dez',
        ];

        $hospitalizations = $hospitalization_table->find('all', [
                'contain' => ["ClinicTevProtocols" => ['RiskFactors'], 'Sectors'],
            ])
            ->where(['Hospitalizations.unit_id' => $unit_id, 'Hospitalizations.status' => 'internado'])
            ->orwhere(['Hospitalizations.unit_id' => $unit_id, 'Hospitalizations.status <>' => 'internado', 'Hospitalizations.data_alta_obito <= ' => $date_after->format("Y-m-d")])
            ->toArray();

        if ($relatorio_type == 'parcial')
        {
            //$this->render('detailed_clinic_tev_protocols');
            $pdf_orientation = 'landscape';

            foreach ($daterange as $date) //gera os dados do relatorio de acordo com o mes
            {
                //primeiro dia do mes
                $startOfMonth = new \DateTime($date->format('Y-m-d'));
                $startOfMonth->modify('first day of this month')->setTime(0, 0, 0);

                //ultimo dia do mes
                $endOfMonth = new \DateTime($date->format('Y-m-d'));
                $endOfMonth->modify('last day of this month')->setTime(23, 59, 59);

                $hospitalizations_period = $hospitalization_table->find('all', [
                    'contain' => ["ClinicTevProtocols" => ['RiskFactors'], 'Sectors'],
                ])
                    ->where(['Hospitalizations.unit_id' => $unit_id, 'Hospitalizations.status' => 'internado'])
                    ->orwhere(['Hospitalizations.unit_id' => $unit_id, 'Hospitalizations.status <>' => 'internado', 'Hospitalizations.data_alta_obito <=' => $endOfMonth->format("Y-m-d")])
                    ->toArray();

                $data['dates'][$date->format("m")] = $clinic_tev_protocols_table->getRelatorioData($hospitalizations_period, $startOfMonth, $endOfMonth);
            }
        }
        else
        {
            $data['consolidado'] = $clinic_tev_protocols_table->getRelatorioData($hospitalizations, $date_before, $date_after);
        }


        $this->viewBuilder()->options([
            'pdfConfig' => [
                'orientation' => $pdf_orientation,
                'filename' => 'TEV clinico.pdf',
            ],
        ]);
        $this->set('month_name', $month_name);
        $this->set('data', $data);
        $this->set(compact('hospitalizations_protocols_tev'));

        if ($relatorio_type == 'parcial')
        {
            $this->render('detailed_clinic_tev_protocols');
        }
    }

    public function surgicalTevProtocols()
    {
        $data = [
            'dates' => [],
            'consolidado' => [],
        ];

        //$data2 = [];
        $hospitalization_table = TableRegistry::get('Hospitalizations');
        $surgical_tev_protocols_table = TableRegistry::get('SurgicalTevProtocols');

        $session = $this->request->session();
        $unit_id = $session->read('selected-unit');
        $relatorio_type = $session->read('relatorio_type');
        $tot_de_leitos = 0;
        $pdf_orientation = 'portrait';

        $date_before = $session->read('date_before');
        $date_after = $session->read('date_after');

        $interval = new \DateInterval('P1M');
        $daterange = new \DatePeriod($date_before, $interval, $date_after);
        $data['daterange'] = $daterange;
        $month_name = [
            '01' => 'Jan',
            '02' => 'Fev',
            '03' => 'Mar',
            '04' => 'Abr',
            '05' => 'Mai',
            '06' => 'Jun',
            '07' => 'Jul',
            '08' => 'Ago',
            '09' => 'Set',
            '10' => 'Out',
            '11' => 'Nov',
            '12' => 'Dez',
        ];

        $hospitalizations = $hospitalization_table->find('all', [
            'contain' => ['SurgicalTevProtocols' => ['Contraindications', 'TevOptions'], 'Sectors'],
        ])
            ->where(['Hospitalizations.unit_id' => $unit_id, 'Hospitalizations.status' => 'internado'])
            ->orwhere(['Hospitalizations.unit_id' => $unit_id, 'Hospitalizations.status <>' => 'internado', 'Hospitalizations.data_alta_obito <=' => $date_after->format("Y-m-d")])
            ->toArray();

        if ($relatorio_type == 'parcial')
        {
            //$this->render('detailed_clinic_tev_protocols');
            $pdf_orientation = 'landscape';

            foreach ($daterange as $date) //gera os dados do relatorio de acordo com o mes
            {
                //primeiro dia do mes
                $startOfMonth = new \DateTime($date->format('Y-m-d'));
                $startOfMonth->modify('first day of this month')->setTime(0, 0, 0);

                //ultimo dia do mes
                $endOfMonth = new \DateTime($date->format('Y-m-d'));
                $endOfMonth->modify('last day of this month')->setTime(23, 59, 59);

                $hospitalizations_period = $hospitalization_table->find('all', [
                    'contain' => ["SurgicalTevProtocols" => ['Contraindications', 'TevOptions'], 'Sectors'],
                ])
                    ->where(['Hospitalizations.unit_id' => $unit_id, 'Hospitalizations.status' => 'internado'])
                    ->orwhere(['Hospitalizations.unit_id' => $unit_id, 'Hospitalizations.status <>' => 'internado', 'Hospitalizations.data_alta_obito <=' => $endOfMonth->format("Y-m-d")])
                    ->toArray();

                $data['dates'][$date->format("m")] = $surgical_tev_protocols_table->getRelatorioData($hospitalizations_period, $startOfMonth, $endOfMonth);
            }
        }
        else{
            $data['consolidado'] = $surgical_tev_protocols_table->getRelatorioData($hospitalizations, $date_before, $date_after);
        }


        $this->viewBuilder()->options([
            'pdfConfig' => [
                'orientation' => $pdf_orientation,
                'filename' => 'TEV cirurgico.pdf',
            ],
        ]);
        $this->set('month_name', $month_name);
        $this->set('data', $data);

        $this->set(compact('hospitalizations_protocols_tev'));

        if ($relatorio_type == 'parcial')
        {
            $this->render('detailed_surgical_tev_protocols');
        }
    }

    public function chestPainProtocols()
    {
        $data = null;

        $hospitalization_table = TableRegistry::get('Hospitalizations');
        $chest_pain_protocols_table = TableRegistry::get('ChestPainProtocols');

        $session = $this->request->session();
        $unit_id = $session->read('selected-unit');
        $hospitalization_id = $session->read('selected-hospitalization');

        $startOfMonth = $session->read('date_before');
        $startOfMonth->modify('first day of this month')->setTime(0, 0, 0);

        $endOfMonth = $session->read('date_after');
        $endOfMonth->modify('last day of this month')->setTime(23, 59, 59);

        $hospitalizations_period = $hospitalization_table->find('all', [
            'contain' => ["Patients", "HistoricPatientMovements", 'SupportiveMeasures', 'Surgeries', 'ChestPainProtocols', 'Discharges'],

        ])
            ->where(['Hospitalizations.unit_id' => $unit_id, 'Hospitalizations.status <>' => 'obito'])
            ->orwhere(['Hospitalizations.unit_id' => $unit_id, 'Hospitalizations.status' => 'obito', 'Hospitalizations.data_alta_obito < ' => $endOfMonth->format("Y-m-d")])
            ->toArray();

        $data = $chest_pain_protocols_table->getRelatorioData($hospitalizations_period, $startOfMonth, $endOfMonth, $unit_id);

        //$hospitalization = $hospitalization_table->get($hospitalization_id);

        // foreach ($chest_pain_protocols as $key => $chest_pain_protocol) {
        //     echo "<pre>";
        //     print_r($chest_pain_protocols);
        // }

        //$data = $chest_pain_protocols_table->getRelatorioData($chest_pain_protocols, $hospitalization);

        $this->viewBuilder()->options([
            'pdfConfig' => [
                'orientation' => 'portrait',
                'filename' => 'Relatório de Dor Torácica.pdf',
            ],
        ]);
        //$hospitalization->toArray();
        $this->set('data', $data);
    }

    public function sepseProtocols()
    {
        $data = [
            'dates' => [],
            'consolidado' => [],
        ];

        //$data2 = [];
        $hospitalization_table = TableRegistry::get('Hospitalizations');
        $surgical_tev_protocols_table = TableRegistry::get('SurgicalTevProtocols');

        $session = $this->request->session();
        $unit_id = $session->read('selected-unit');
        $relatorio_type = $session->read('relatorio_type');
        $tot_de_leitos = 0;
        $pdf_orientation = 'portrait';

        $date_before = $session->read('date_before');
        $date_after = $session->read('date_after');

        $interval = new \DateInterval('P1M');
        $daterange = new \DatePeriod($date_before, $interval, $date_after);
        $data['daterange'] = $daterange;
        $month_name = [
            '01' => 'Jan',
            '02' => 'Fev',
            '03' => 'Mar',
            '04' => 'Abr',
            '05' => 'Mai',
            '06' => 'Jun',
            '07' => 'Jul',
            '08' => 'Ago',
            '09' => 'Set',
            '10' => 'Out',
            '11' => 'Nov',
            '12' => 'Dez',
        ];

        $sepse_table = TableRegistry::get('SepseProtocols');
        $hospitalization_table = TableRegistry::get('Hospitalizations');

        $hospitalizations = $hospitalization_table
            ->find('all', [
                'contain' => ["SepseProtocols" => ['Sectors'], 'SofaEscores', 'Discharges', 'Sectors'],
            ])
            ->where(['Hospitalizations.unit_id' => $unit_id, 'Hospitalizations.status' => 'internado'])
            ->orwhere(['Hospitalizations.unit_id' => $unit_id, 'Hospitalizations.status <>' => 'internado', 'Hospitalizations.data_alta_obito <= ' => $date_after->format("Y-m-d")])
            ->toArray();

        if ($relatorio_type == 'parcial')
        {
            //$this->render('detailed_clinic_tev_protocols');
            $pdf_orientation = 'landscape';

            foreach ($daterange as $date) //gera os dados do relatorio de acordo com o mes
            {
                //primeiro dia do mes
                $startOfMonth = new \DateTime($date->format('Y-m-d'));
                $startOfMonth->modify('first day of this month')->setTime(0, 0, 0);

                //ultimo dia do mes
                $endOfMonth = new \DateTime($date->format('Y-m-d'));
                $endOfMonth->modify('last day of this month')->setTime(23, 59, 59);

                $hospitalizations_period = $hospitalization_table->find('all', [
                    'contain' => ["SepseProtocols" => ['Sectors'], 'SofaEscores', 'Discharges', 'Sectors'],
                ])
                    ->where(['Hospitalizations.unit_id' => $unit_id, 'Hospitalizations.status' => 'internado'])
                    ->orwhere(['Hospitalizations.unit_id' => $unit_id, 'Hospitalizations.status <>' => 'internado', 'Hospitalizations.data_alta_obito <=' => $endOfMonth->format("Y-m-d")])
                    ->toArray();

                $data['dates'][$date->format("m")] = $sepse_table->getRelatorioData($hospitalizations_period, $startOfMonth, $endOfMonth);
            }
        }
        else{
            $data['consolidado'] = $sepse_table->getRelatorioData($hospitalizations, $date_before, $date_after);
        }

        $this->viewBuilder()->options([
            'pdfConfig' => [
                'orientation' => $pdf_orientation,
                'filename' => 'Relatório de Sepse.pdf',
            ],
        ]);

        $count_d = count($data['dates']);

        $count_d = ($count_d * 2) + 1;

        $this->set('count_d', $count_d);
        $this->set('data', $data);
        $this->set('hospitalizations', $hospitalizations);

        $this->set('month_name', $month_name);

        if ($relatorio_type == 'parcial')
        {
            $this->render('detailed_sepse_protocols');
        }
    }

    public function relatorioGeral()
    {
        $hospital_ids = [];
        $session = $this->request->session();
        $unit_id = $session->read('selected-unit');
        $data = null;
        $hospitalization_table = TableRegistry::get('Hospitalizations');
        $sector_table = TableRegistry::get('Sectors');
        $cid10_table = TableRegistry::get('Cid10');

        //ultimo dia do mes
        $date_before = $session->read('date_before');
        $date_after = $session->read('date_after');
        $date_after->modify('last day of this month')->setTime(23, 59, 59);

        $sectors = $sector_table
            ->find()
            ->where(['Sectors.unit_id' => $unit_id])
            ->toArray();

        $diagnostics       = $cid10_table->find('all', [
            'order' => ['Cid10.cid_cod' => 'ASC']
            ])
            ->toArray();

        $hospitalizations = $hospitalization_table
            ->find('all',
                [ 'contain' => ["Discharges", "HistoricPatientMovements.Rooms.Sectors",
                "Diagnostics" => ['SystemicDiagnosis', 'Comorbidities'],
                'Patients' => ['HealthInsurancePlans'], "Origins"],
            ])
            ->where(['Hospitalizations.unit_id' => $unit_id, 'Hospitalizations.status' => 'internado'])

            ->orwhere(['Hospitalizations.unit_id' => $unit_id, 'Hospitalizations.status <>' => 'internado',
                'Hospitalizations.data_alta_obito <=' => $date_after->format("Y-m-d")])

            ->toArray();

        $data = $hospitalization_table->getRelatorioGeralData($hospitalizations, $date_before, $date_after, $sectors, $diagnostics);

        // $this->viewBuilder()->options([
        //     'pdfConfig' => [
        //         'orientation' => 'portrait',
        //         'filename' => 'Relatório Geral.pdf',
        //     ],
        // ]);

        $this->set('data', $data);
        $this->set('hospitalizations', $hospitalizations);
    }
}
