<?php
namespace App\Controller;

use App\Controller\AppController;
use Cake\Core\Exception\Exception;
use Cake\Datasource\ConnectionManager;
use Cake\Log\Log;
use Cake\ORM\TableRegistry;

/**
 * Sectors Controller
 *
 * @property \App\Model\Table\SectorsTable $Sectors
 */
class SectorsController extends AppController
{

    public function initialize()
    {
        parent::initialize();
        $this->loadComponent('Permission');
        $this->loadComponent('Upload');
        $this->set('title', 'Setor');

        $unit_role_name = $this->request->session()->read('selected-unit-role-name');
        $this->set('unit_role_name', $unit_role_name);

    }

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

        $session   = $this->request->session();
        $unit_role = $session->read('selected-unit-role-name');

        if (isset($unit_role)) {
            return $this->Permission->hasPermission($unit_role, 'sectors', $action);
        } else {
            return $this->redirect(['controller' => 'users', 'action' => 'login']);
            //return  $this->Permission->hasPermission($user['role_name'], 'sectors', $action);
        }
        //return parent::isAuthorized($user);
    }

    /**
     * Index method
     *
     * @return \Cake\Network\Response|null
     */
    public function index()
    {
        // $sectors = $this->paginate($this->Sectors);

        // $this->set(compact('sectors', 'unit_id'));
        // $this->set('_serialize', ['sectors']);

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

        $unitsTable = TableRegistry::get('Units');

        $query = $this->Sectors->find()
            ->contain(['Units', 'Types'])
            ->order(['created'=>'desc']);
        ;

        $query->innerJoinWith('Units')
            ->distinct(['Sectors.id'])
            ->where(['Units.id =' => $unit_id])
        ;

        // $users = $this->paginate($this->Users);
        $sectors = $this->paginate($query);

        $this->set("unit_id", $unit_id);
        $this->set(compact('sectors'));
        $this->set('_serialize', ['sectors']);
    }

    /**
     * View method
     *
     * @param string|null $id Sector id.
     * @return \Cake\Network\Response|null
     * @throws \Cake\Datasource\Exception\RecordNotFoundException When record not found.
     */
    public function view($sector_id = null, $unit_id = 0)
    {
        $sector = $this->Sectors->get($sector_id, [
            //'contain' => ['Goals', 'ProfessionalSectors', 'Rooms']
            'contain' => ['Types', 'Units'],
        ]);

        $this->set('unit_idunit_id', $unit_id);
        $this->set('sector', $sector);
        $this->set('_serialize', ['sector']);
    }

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

        $typesTable = TableRegistry::get('Types');
        $types      = $typesTable->find();
        $types      = $types->toArray();

        if ($this->request->is('post')) {
            $sector = $this->Sectors->patchEntity($sector, $this->request->data);
            // Log::write('debug', '$sector');
            // Log::write('debug', $sector->errors());

            if ($this->Sectors->save($sector))
            // if ( $this->Upload->sendMultiple( $this->request->data, $sector ) )
            {
                Log::write('debug', '$sector->id');
                Log::write('debug', $sector->id);

                for ($i = 1; $i <= $sector->limit_of_rooms; $i++) {
                    $conn = ConnectionManager::get('default');
                    $stmt = $conn->execute(
                        'INSERT INTO rooms (sector_id, name) values (?, ?)', [$sector->id, 'Leito ' . $i]
                    );
                    // Log::write('debug', '$stmt');
                    // Log::write('debug', $stmt);
                }

                $this->Flash->success(__('O setor foi salvado com sucesso.'));
                return $this->redirect(['action' => 'index']);
            } else {
                $this->Flash->error(__('O setor não pode ser salvo. Por favor, tente novamente.'));
            }
        }
        //$types = $this->Sectors->Types->find('list', ['limit' => 200]);

        //$types = $types->toArray();
        $this->set("types", $types);
        $this->set(compact('sector', 'unit_id'));
        $this->set('_serialize', ['sector']);
    }

    /**
     * Edit method
     *
     * @param string|null $id Sector 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)
    {
        $sector = $this->Sectors->get($id, [
            //'contain' => ['Goals']
            //'contain' => ['Types']
        ]);

        $typesTable = TableRegistry::get('Types');
        $types      = $typesTable->find();
        $types      = $types->toArray();

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

            $rooms = $this->Sectors->Rooms->find('list', [
                'conditions' => ['Rooms.sector_id =' => $id],
                'limit'      => 999])->toArray();
            // Log::write('debug', '$rooms');
            // Log::write('debug', $rooms);
            $sum_of_rooms = 0;
            foreach ($rooms as $key => $value) {
                $sum_of_rooms++;
            }
            // $query = $articles->find('all', [
            //     'conditions' => ['Articles.title LIKE' => '%Ovens%']
            // ]);
            // $number = $query->count();

            // Log::write('debug', '$sector');
            // Log::write('debug', $sector->errors());

            //Log::write('debug', '$limit_of_rooms and sum_of_rooms');
            //Log::write('debug', 'limit_of_rooms = '.$sector->limit_of_rooms. ' sum_of_rooms = '.$sum_of_rooms);

            if ($sector->limit_of_rooms >= $sum_of_rooms) {

                //if ( $this->Upload->sendMultiple( $this->request->data, $sector ) )
                if ($this->Sectors->save($sector)) {
                    $this->Flash->success(__('O setor foi salvo.'));

                    return $this->redirect(['action' => 'index']);
                } else {
                    $this->Flash->error(__('O setor não pode ser salvo. Por favor, tente novamente.'));
                }
            } else {
                $this->Flash->error(__('O limite fornecido é menor do que o total de leitos atual.'));
            }

        }

        //$goals = $this->Sectors->Goals->find('list', ['limit' => 200]);
        //$types = $this->Sectors->Types->find('list', ['limit' => 200]);

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

    /**
     * Delete method
     *
     * @param string|null $id Sector 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']);

        try {
            $sector = $this->Sectors->get($id);
            if ($this->Sectors->delete($sector)) {
                $this->Flash->success(__('O setor foi deletado com sucesso.'));
            } else {
                $this->Flash->error(__('O setor não pode ser deletado. Por favor, tente novamente.'));
            }
        } catch (Exception $e) {
            $this->Flash->error(__('O setor está em uso e não pode ser deletado'));
        }
        return $this->redirect(['action' => 'index']);
    }

    public function desactivate($id = null)
    {
        $this->request->allowMethod(['post', 'desactivate']);

        $sector = $this->Sectors->get($id);

        $sector['is_active'] = false;

        if ($this->Sectors->save($sector)) {
            $this->Flash->success(__('O setor foi suspenso com sucesso.'));
        } else {
            $this->Flash->error(__('O setor não pode ser suspenso. Por favor, tente novamente.'));
        }

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

    public function activate($id = null)
    {
        $this->request->allowMethod(['post', 'desactivate']);

        $sector            = $this->Sectors->get($id);
        $sector->is_active = true;

        if ($this->Sectors->save($sector)) {
            $this->Flash->success(__('O setor foi ativado com sucesso.'));
        } else {
            $this->Flash->error(__('O setor não pode ser ativado. Por favor, tente novamente.'));
        }

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

    }

    public function listSectorPatients($sector_id = 0, $date = null)
    {
        $session = $this->request->session();
        $unit_id = $session->read('selected-unit');

        $unitsTable = TableRegistry::get('Units');
        $roomsTable = TableRegistry::get('Rooms');

        $query = $roomsTable->find()
            ->where(['Rooms.sector_id'=>$sector_id])
            ->contain([
                'Sectors.Types'
            ]);

        // $query = $this->Sectors->find()
        //     ->where(['Sectors.id'=>$sector_id])
        //     ->contain([
        //         'Units', 
        //         'Types',
        //         'Rooms'
        //     ]);

 
            $date = date_create($date);     
            $date =date_format($date, 'Y-m-d');


            
            if ($date) {
                $query->leftJoinWith('HistoricPatientMovements.Hospitalizations.Patients', function ($q) use ($date)  {
                return $q
                   // ->contain(['Hospitalizations.Patients'])
                    //->innerJoinWith('Rooms.Sectors')
                    ->select([                     
                        'Patients.id',
                        'Patients.nome',
                        'Patients.birthday',
                        'HistoricPatientMovements.hora_inicio',
                        'HistoricPatientMovements.hora_inicio',
                        'HistoricPatientMovements.hora_inicio',
                        'Hospitalizations.id',
                        'Hospitalizations.numero_do_prontuario',
                        'Hospitalizations.date_of_hospitalization'
                    ])
                    ->autoFields(true)
                    ->group(['Hospitalizations.date_of_hospitalization'])
                    // ->where(['HistoricPatientMovements.data_inicio <>' => null,])
                    ->where(['Hospitalizations.date_of_hospitalization >' => $date ])
                    ->order(['HistoricPatientMovements.data_inicio' => 'DESC'])
                    ->limit(1);
            });
            } 
            else {
                $query->leftJoinWith('HistoricPatientMovements.Hospitalizations.Patients', function ($q) use ($date)  {
                return $q
                   // ->contain(['Hospitalizations.Patients'])
                    //->innerJoinWith('Rooms.Sectors')
                    ->select([                     
                        'Patients.id',
                        'Patients.nome',
                        'Patients.birthday',
                        'HistoricPatientMovements.hora_inicio',
                        'HistoricPatientMovements.hora_inicio',
                        'HistoricPatientMovements.hora_inicio',
                        'Hospitalizations.id',
                        'Hospitalizations.numero_do_prontuario',
                        'Hospitalizations.date_of_hospitalization'
                    ])
                    ->autoFields(true)
                    ->group(['Hospitalizations.date_of_hospitalization'])
                    // ->where(['HistoricPatientMovements.data_inicio <>' => null,])
                    // ->where(['Hospitalizations.date_of_hospitalization >' => $date ])
                    ->order(['HistoricPatientMovements.data_inicio' => 'DESC'])
                    ->limit(1);
            });
            }
            
        //}

        // $query->innerJoinWith('Units')
        //     ->distinct(['Sectors.id'])
        //     ->where(['Units.id =' => $unit_id])
        // ;

        // $users = $this->paginate($this->Users);
        $sectors = $this->paginate($query);

        $this->set("date", $date);
        $this->set("unit_id", $unit_id);
        $this->set(compact('sectors'));
        $this->set('_serialize', ['sectors']);
    }



    // public function listSectorPatientsEmergency($sector_id = 0, $date = null){
    //     $session = $this->request->session();
    //     $unit_id = $session->read('selected-unit');

    //     $unitsTable = TableRegistry::get('Units');
    //     $roomsTable = TableRegistry::get('Rooms');

    //     $query = $roomsTable->find()
    //         ->where(['Rooms.sector_id'=>$sector_id])
    //         ->contain([
    //             'Sectors.Types'
    //         ])
    //         ->group(['Rooms.id']);
 
    //         $date = date_create($date);     
    //         $date =date_format($date, 'Y-m-d');
            
    //         if ($date) {
    //             $query->leftJoinWith('HistoricPatientMovements.Hospitalizations.Patients', function ($q)use ($date)  {
    //                     return $q
    //                     ->select([                     
    //                         'Patients.id',
    //                         'Patients.nome',
    //                         'Patients.birthday',
    //                         'HistoricPatientMovements.hora_inicio',
    //                         'HistoricPatientMovements.hora_inicio',
    //                         'HistoricPatientMovements.hora_inicio',
    //                         'Hospitalizations.id',
    //                         'Hospitalizations.numero_do_prontuario',
    //                         'Hospitalizations.date_of_hospitalization'
    //                     ])
    //                     ->autoFields(true)
    //                     ->group(['Hospitalizations.date_of_hospitalization' ])
    //                     ->where(['Hospitalizations.date_of_hospitalization < ' => $date])
    //                     ->order(['HistoricPatientMovements.data_inicio' => 'DESC'])
    //                     ->limit(1);
    //             });
    //         } 
    //         else {
    //             $query->leftJoinWith('HistoricPatientMovements.Hospitalizations.Patients', function ($q)use ($date)  {
    //                 return $q
    //                 ->select([                     
    //                     'Patients.id',
    //                     'Patients.nome',
    //                     'Patients.birthday',
    //                     'HistoricPatientMovements.hora_inicio',
    //                     'HistoricPatientMovements.hora_inicio',
    //                     'HistoricPatientMovements.hora_inicio',
    //                     'Hospitalizations.id',
    //                     'Hospitalizations.numero_do_prontuario',
    //                     'Hospitalizations.date_of_hospitalization'
    //                 ])
    //                 ->autoFields(true)
    //                 ->group(['Hospitalizations.date_of_hospitalization'])
    //                 ->order(['HistoricPatientMovements.data_inicio' => 'DESC'])
    //                 ->limit(1);
    //             });
    //         }

    //     $sectors = $this->paginate($query);

    //     $this->set("date", $date);
    //     $this->set("unit_id", $unit_id);
    //     $this->set(compact('sectors'));
    //     $this->set('_serialize', ['sectors']);
    // } 

    public function listSectorPatientsEmergency($sector_id = 0, $date = null){
        $session = $this->request->session();
        $unit_id = $session->read('selected-unit');

        $unitsTable = TableRegistry::get('Units');
        $roomsTable = TableRegistry::get('Rooms');
        $hospitalizationTable = TableRegistry::get('Hospitalizations');

        $date = date_create($date);     
        $date =date_format($date, 'Y-m-d');

        $hospitalizations = $hospitalizationTable->find('all')
            ->contain(["IdentificationOfRisks", 'Patients', 'HistoricPatientMovements' => ['Rooms' => ['Sectors']], 'Diagnostics'])
            ->join([
                'hi'=>[ 
                    'table' => 'historic_patient_movements',
                    'type' => 'INNER',
                    'conditions' => 'hi.hospitalization_id = Hospitalizations.id',
                ],
                'ro'=>[ 
                    'table' => 'rooms',
                    'type' => 'INNER',
                    'conditions' => 'ro.id = hi.room_id',
                ]
            ])
            ->where(['Hospitalizations.unit_id' => $unit_id, 'ro.sector_id' => $sector_id]);


        $hospitalizations = $this->paginate($hospitalizations);
        $this->set('hospitalizations', $hospitalizations);
        $this->set(compact('hospitalizations'));
        $this->set('_serialize', ['hospitalizations']);
        $this->set("date", $date);
        $this->set("unit_id", $unit_id);
    } 

    public function listSectorPatientsHospitalizationUnits($sector_id = 0, $date = null){
        $session = $this->request->session();
        $unit_id = $session->read('selected-unit');

        $unitsTable = TableRegistry::get('Units');
        $roomsTable = TableRegistry::get('Rooms');
        $hospitalizationTable = TableRegistry::get('Hospitalizations');

        $date = date_create($date);     
        $date =date_format($date, 'Y-m-d');

        $hospitalizations = $hospitalizationTable->find('all')
            ->contain(["IdentificationOfRisks", 'Patients', 'HistoricPatientMovements' => ['Rooms' => ['Sectors']], 'Diagnostics'])
            ->join([
                'hi'=>[ 
                    'table' => 'historic_patient_movements',
                    'type' => 'INNER',
                    'conditions' => 'hi.hospitalization_id = Hospitalizations.id',
                ],
                'ro'=>[ 
                    'table' => 'rooms',
                    'type' => 'INNER',
                    'conditions' => 'ro.id = hi.room_id',
                ]
            ])
            ->where(['Hospitalizations.unit_id' => $unit_id, 'ro.sector_id' => $sector_id]);


        $hospitalizations = $this->paginate($hospitalizations);
        $this->set('hospitalizations', $hospitalizations);
        $this->set(compact('hospitalizations'));
        $this->set('_serialize', ['hospitalizations']);
        $this->set("date", $date);
        $this->set("unit_id", $unit_id);
    } 

    public function listSectorPatientsIntensiveCareUnits($sector_id = 0, $date = null){
        $session = $this->request->session();
        $unit_id = $session->read('selected-unit');

        $unitsTable = TableRegistry::get('Units');
        $roomsTable = TableRegistry::get('Rooms');
        $hospitalizationTable = TableRegistry::get('Hospitalizations');

        $date = date_create($date);     
        $date =date_format($date, 'Y-m-d');

        $hospitalizations = $hospitalizationTable->find('all')
            ->contain(["IdentificationOfRisks", 'Patients', 'HistoricPatientMovements' => ['Rooms' => ['Sectors']], 'Diagnostics'])
            ->join([
                'hi'=>[ 
                    'table' => 'historic_patient_movements',
                    'type' => 'INNER',
                    'conditions' => 'hi.hospitalization_id = Hospitalizations.id',
                ],
                'ro'=>[ 
                    'table' => 'rooms',
                    'type' => 'INNER',
                    'conditions' => 'ro.id = hi.room_id',
                ]
            ])
            ->where(['Hospitalizations.unit_id' => $unit_id, 'ro.sector_id' => $sector_id]);


        $hospitalizations = $this->paginate($hospitalizations);
        $this->set('hospitalizations', $hospitalizations);
        $this->set(compact('hospitalizations'));
        $this->set('_serialize', ['hospitalizations']);
        $this->set("date", $date);
        $this->set("unit_id", $unit_id);
    } 

    public function listSectorPatientsSurgeries($sector_id = 0, $date = null){
        $session = $this->request->session();
        $unit_id = $session->read('selected-unit');

        $unitsTable = TableRegistry::get('Units');
        $roomsTable = TableRegistry::get('Rooms');
        $hospitalizationTable = TableRegistry::get('Hospitalizations');

        $date = date_create($date);     
        $date =date_format($date, 'Y-m-d');

        $hospitalizations = $hospitalizationTable->find('all')
            ->contain(['Surgeries'=>['TypeOfAnesthesias', 'SurgicalMaterials', 'Professionals.Users'],
             "IdentificationOfRisks", 'Patients', 'HistoricPatientMovements' => ['Rooms' => ['Sectors']], 'Diagnostics'])
            ->join([
                'hi'=>[ 
                    'table' => 'historic_patient_movements',
                    'type' => 'INNER',
                    'conditions' => 'hi.hospitalization_id = Hospitalizations.id',
                ],
                'ro'=>[ 
                    'table' => 'rooms',
                    'type' => 'INNER',
                    'conditions' => 'ro.id = hi.room_id',
                ]
            ])
            ->where(['Hospitalizations.unit_id' => $unit_id, 'ro.sector_id' => $sector_id])
            ->group(['Hospitalizations.id']);


        $hospitalizations = $this->paginate($hospitalizations);
        $this->set('hospitalizations', $hospitalizations);
        $this->set(compact('hospitalizations'));
        $this->set('_serialize', ['hospitalizations']);
        $this->set("date", $date);
        $this->set("unit_id", $unit_id);
    } 
}
