<?php
namespace App\Controller;

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

/**
 * Surgeries Controller
 *
 * @property \App\Model\Table\SurgeriesTable $Surgeries
 */
class SurgeriesController extends AppController
{

    public function initialize()
    {
        parent::initialize();

        $this->loadComponent('Permission');
        $this->loadComponent('RequestHandler');

        // View or Controller
        $this->set('title', 'Cirurgia');

        $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);
    }

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

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

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

        $this->paginate = [
            'contain' => ['Hospitalizations'],
            'conditions' => ['Surgeries.hospitalization_id' => $hospitalization_id],
            'order' => ['created'=>'desc']
        ];
        $surgeries = $this->paginate($this->Surgeries);

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

    /**
     * View method
     *
     * @param string|null $id Surgery id.
     * @return \Cake\Network\Response|null
     * @throws \Cake\Datasource\Exception\RecordNotFoundException When record not found.
     */
    public function view($id = null)
    {
        $surgery = $this->Surgeries->get($id, [
            'contain' => ['Hospitalizations', 'Professionals', 'SurgicalMaterials', 'TypeOfAnesthesias'],
        ]);

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

    public function getProfessionals()
    {
        $this->autoRender = false;
        $paging           = null;

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

        $this->paginate = [
            //'contain' => ['Users'],
            'limit' => 6,
        ];

        $professionals = $this->paginate($this->Surgeries->Professionals
                ->find('all', ['limit' => 200, 'contain' => ['Users', 'Specialties']])

                ->where(['Professionals.unit_id' => $unit_id])
        );

        Log::write('debug', '$this->request["paging"]');
        Log::write('debug', $this->request['paging']);

        // Log::write('debug', 'paginator');
        // Log::write('debug', $this->paginate);

        if ($this->request->is('ajax')) {

            $paging = $this->request["paging"]["Professionals"];
            //$this->set('professionals', $this->paginate());
            // Specify which view vars JsonView should serialize.
            // $this->set('_serialize', ['professionals']);
            echo json_encode(compact('professionals', 'paging'));

        }
    }

    /**
     * Add method
     *
     * @return \Cake\Network\Response|void Redirects on successful add, renders view otherwise.
     */
    public function add()
    {
        $session = $this->request->session();
        $unit_id = $session->read('selected-unit');

        $sector_table = TableRegistry::get('Sectors');
        //$rooms        = $sector_table->find('list', ['fields' => ['id', 'name'], 'contain' => ['Rooms']])
            //->where(['Sectors.unit_id' => $unit_id, 'Sectors.type_id' => 1])
        //;

        $sectors        = $sector_table->find('all',[
            'contain' => ['Rooms']])
            ->where(['Sectors.unit_id' => $unit_id, 'Sectors.type_id' => 1])
        ;

        $rooms = [];
        foreach ($sectors as $key => $value) {
            foreach ($value['rooms'] as $key2 => $value2) {
                $rooms[$value2->id] = $value->name . " - " . $value2->name;
            }
        }

        $this->paginate = [
            //'contain' => ['Users'],
            'limit' => 2,
        ];

        $professionals = $this->paginate($this->Surgeries->Professionals
                ->find('all', ['limit' => 200, 'contain' => ['Users', 'Specialties']])
                ->where(['Professionals.unit_id' => $unit_id])
        );

        $surgery = $this->Surgeries->newEntity();

        if ($this->request->is('post')) {
            $surgery = $this->Surgeries->patchEntity($surgery, $this->request->data);
            //echo '<pre>';
            //print_r($this->request->data);
            //Log::write('debug', $surgery);
            //return;

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

        $surgicalComplications = $this->Surgeries->SurgicalComplications->find('list', ['limit' => 200, 'fields' => ['id', 'nome']]);
        $hospitalizations      = $this->Surgeries->Hospitalizations->find('list', ['limit' => 200]);
        $specialties           = $this->Surgeries->Specialties->find('list', ['limit' => 200]);
        $auxiliarySpecialties  = $this->Surgeries->Specialties->find('list', ['limit' => 200]);
        $surgicalMaterials     = $this->Surgeries->SurgicalMaterials->find('list', ['limit' => 200, 'fields' => ['id', 'nome']]);
        $typeOfAnesthesias     = $this->Surgeries->TypeOfAnesthesias->find('list', ['limit' => 200, 'fields' => ['id', 'nome']]);
        $changeTypeOfAnesthesias     = $this->Surgeries->ChangeTypeOfAnesthesias->find('list', ['limit' => 200, 'fields' => ['id', 'nome']]);

        // $rooms             = $this->Surgeries->Rooms->Sectors->find('list', ['limit' => 200, 'fields' => ['id', 'name']]);

        $this->set(compact('surgery', 'hospitalizations', 'auxiliarySpecialties', 'professionals', 'surgicalMaterials', 'typeOfAnesthesias', 'surgicalComplications', 'specialties', 'rooms', 'changeTypeOfAnesthesias'));
        $this->set('_serialize', ['surgery']);
    }

    /**
     * Edit method
     *
     * @param string|null $id Surgery 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)
    {
        $session              = $this->request->session();
        $unit_id              = $session->read('selected-unit');
        $prof_surgeries_table = TableRegistry::get('ProfessionalsSurgeries');

        $sector_table = TableRegistry::get('Sectors');
        $sectors        = $sector_table->find('all',[
            'contain' => ['Rooms']])
            ->where(['Sectors.unit_id' => $unit_id, 'Sectors.type_id' => 1])
        ;

        $rooms = [];
        foreach ($sectors as $key => $value) {
            foreach ($value['rooms'] as $key2 => $value2) {
                $rooms[$value2->id] = $value->name . " - " . $value2->name;
            }
        }

        $this->paginate = [
            //'contain' => ['Users'],
            'limit' => 100,
        ];

        $professionals = $this->paginate($this->Surgeries->Professionals
                ->find('all', ['limit' => 200, 'contain' => ['Users', 'Specialties']])

                ->where(['Professionals.unit_id' => $unit_id])
        );

        $surgery = $this->Surgeries->get($id, [
            'contain' => ['Professionals', 'SurgicalMaterials', 'TypeOfAnesthesias', 'Specialties', 'AuxiliarySpecialties', 'ChangeSpecialties', 'ChangeTypeOfAnesthesias'],
        ]);
        if ($this->request->is(['patch', 'post', 'put'])) {
            $surgery = $this->Surgeries->patchEntity($surgery, $this->request->data);

            if ($this->Surgeries->save($surgery)) {

                //$prof_ids = '';
                $prof_ids = [];
                foreach ($surgery['professionals'] as $key => $value) {
                    array_push($prof_ids, $value->id);
                    //$prof_ids .= $value->id . ', ';
                }
                //$prof_ids .= '0';

                // $conn = ConnectionManager::get('default');
                // $stmt = $conn->execute(
                //     'DELETE FROM professionals_surgeries  WHERE surgery_id = ? AND professional_id NOT IN (?)',
                //     [$id, $prof_ids]
                // );

                $prof_surgeries_table->deleteAll(
                    [
                        'ProfessionalsSurgeries.Surgery_id'             => 16,
                        'ProfessionalsSurgeries.professional_id NOT IN' => $prof_ids,
                    ],
                    false# <- single delete statement please
                );

                Log::write('debug', '$prof_ids');
                Log::write('debug', $prof_ids);

                $this->Flash->success(__('O registro foi salvo.'));
                Log::write('debug', '$surgery');
                Log::write('debug', $surgery);

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

            } else {
                $this->Flash->error(__('O registro não pode ser salvo. Por favor, tente novamente.'));
            }
        }
        $hospitalizations     = $this->Surgeries->Hospitalizations->find('list', ['limit' => 200]);
        $auxiliarySpecialties = $this->Surgeries->Specialties->find('list', ['limit' => 200]);
        $specialties          = $this->Surgeries->Specialties->find('list', ['limit' => 200]);
        $surgicalMaterials    = $this->Surgeries->SurgicalMaterials->find('list', ['limit' => 200, 'fields' => ['id', 'nome']]);
        $typeOfAnesthesias    = $this->Surgeries->TypeOfAnesthesias->find('list', ['limit' => 200, 'fields' => ['id', 'nome']]);
        $changeTypeOfAnesthesias     = $this->Surgeries->ChangeTypeOfAnesthesias->find('list', ['limit' => 200, 'fields' => ['id', 'nome']]);
        $this->set(compact('rooms', 'surgery', 'auxiliarySpecialties', 'hospitalizations', 'professionals', 'surgicalMaterials', 'typeOfAnesthesias', 'specialties', 'changeTypeOfAnesthesias'));
        $this->set('_serialize', ['surgery']);
    }

    /**
     * Delete method
     *
     * @param string|null $id Surgery 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']);
        $surgery = $this->Surgeries->get($id);
        if ($this->Surgeries->delete($surgery)) {
            $this->Flash->success(__('O registro foi deletado'));
        } else {
            $this->Flash->error(__('O registro não pode ser deletado. Por favor, tente novamente.'));
        }
        return $this->redirect(['action' => 'index']);
    }

    public function unlinkUserFromSurgery($id = null)
    {
        $session = $this->request->session();
        $user_id = $session->read('selected-unit');

        $conn = ConnectionManager::get('default');
        $stmt = $conn->execute(
            'DELETE FROM units_users  WHERE user_id = ? AND unit_id = ?',
            [$user_id, $unit_id]
        );

        $code = $stmt->errorCode();
        $info = $stmt->errorInfo();

        if ($code === '00000') {
            $this->Flash->success(__('O usuário foi desvinculado com sucesso.'));
        } else {
            $this->Flash->error(__('O usuário não pode ser desvinculado a unidade.'));
        }
        return $this->redirect(['action' => 'attachUsers', $unit_id]);

    }

    public function linkUserToSurgery($professional_id = null)
    {
        $session = $this->request->session();
        $user_id = $session->read('selected-unit');

        $conn = ConnectionManager::get('default');
        $stmt = $conn->execute(
            'INSERT INTO professionals_surgeries (professional_id, unit_id, privilege_id) values (?, ?, ?)',
            [$user_id, $unit_id, 1]
        );

        $code = $stmt->errorCode();
        $info = $stmt->errorInfo();

        if ($code === '00000') {
            $this->Flash->success(__('O usuário foi vinculado com sucesso.'));
        } else {
            $this->Flash->error(__('O usuário não pode ser vinculado a unidade.'));
        }
        return $this->redirect(['action' => 'attachUsers', $unit_id]);
    }

    public function search()
    {
        $this->autoRender = false;

        if ($this->request->is('get')) {

            $session = $this->request->session();
            $unit_id = $session->read('selected-unit');
            $name    = '%' . $this->request->query('term') . '%';

            $this->paginate = [
                //'contain' => ['Users'],
                'limit' => 6,
            ];

            //             $results = $this->Surgeries->find('all', array(
            //                                            'conditions' => array('Surgeries.title LIKE ' => '%' . $name . '%')

            //                                            ));

            // , 'contain' => ['Users', 'Specialties']

            // $results = $this->paginate($this->Surgeries->Professionals
            //         ->find('all', ['limit' => 200])
            //         ->where(['Professionals.unit_id' => $unit_id])
            //         ->join('users', ['users.id' => 'Professionals.user_id', 'users.name LIKE' => '%' . $name . '%'])
            // );

            $conn = ConnectionManager::get('default');

            $query = "SELECT  Professionals.id as professional_id, Users.id as user_id, Users.name as user_name, Specialties.name speciality_name FROM specialties as Specialties";
            $query .= " INNER JOIN professionals_specialties as ProfessionalsSpecialties ON (Specialties.id =  ProfessionalsSpecialties.specialty_id)";
            $query .= " INNER JOIN professionals as Professionals ON (Professionals.id =  ProfessionalsSpecialties.professional_id)";
            $query .= " INNER JOIN users as Users ON (Users.id =  Professionals.user_id)";
            $query .= " INNER JOIN units_users as UnitsUsers ON (UnitsUsers.user_id =  Users.id)";
            $query .= " INNER JOIN units as Units ON (Units.id =  Professionals.unit_id)";
            $query .= " WHERE Professionals.unit_id = ? AND Users.name like ? GROUP BY Users.id";

            $stmt = $conn->execute(
                $query,
                [$unit_id, $name]
            );

            $results = $stmt->fetchAll('assoc');

            // $results = $this->paginate($this->Surgeries->Professionals->find()
            //         ->contain(['Users'])
            //     //->join('users', ['users.id' => 'Professionals.user_id', 'Professionals.unit_id' => $unit_id, 'users.name LIKE' => '%' . $name . '%'])

            // );

            // $results->join([
            //     'a' => [
            //         'table'      => 'Users',
            //         'type'       => 'INNER',
            //         'conditions' => 'Users.id = Professionals.user_id',
            //     ],
            // ]);

            $resultArr = array();
            foreach ($results as $result) {
                $resultArr[] = array('label' => $result['user_name'], 'value' => $result['user_name']);
            }

            //echo "<pre>";

            //print_r($stmt);
            //print_r($results);
            echo json_encode($results);
        }
    }

    public function surgeries()
    {
        $surgeries_table                        = TableRegistry::get('Surgeries');
        $surgeries                              = $surgeries_table->find('all');
        $count                                  = 0;
        $somatorioDias                          = 0;
        $countCirurgiaCadastroRealizado         = 0;
        $somatorioDiasCicurgiaCadastroRealizado = 0;
        $arr                                    = [];
        foreach ($surgeries as $key => $surgerie) {

            $data1         = $surgerie['data_da_solicitacao'];
            $data2         = $surgerie['data_da_liberacao'];
            $data_cirurgia = $surgerie['data_da_cirurgia'];
            if ($data1 != "" && $data2 != "") {
                $intervalo = $data1->diff($data2);
                $somatorioDias += $intervalo->d;
                $count++;
                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;
                } else {
                    $arr['porte'][$surgerie['porte_da_cirurgia']]['totalDiasPorte'] += $intervalo->d;
                    $arr['porte'][$surgerie['porte_da_cirurgia']]['countCirurgiaPorte'] += 1;
                }
                //echo $surgerie['nome']."<br>";
                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;
                } else {
                    $arr['tipoCirurgia'][$surgerie['nome']]['countTipoCirurgia'] += 1;
                    $arr['tipoCirurgia'][$surgerie['nome']]['totalDiasTipo'] += $intervalo->d;
                }
            }

            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']]['totalCirurgiaPorte'] = 1;
                } else {
                    $arr['cirurgia_cadastro_realizado']['porte'][$surgerie['porte_da_cirurgia']]['totalDiasPorte'] += $intervalo->d;
                    $arr['cirurgia_cadastro_realizado']['porte'][$surgerie['porte_da_cirurgia']]['totalCirurgiaPorte'] += 1;
                }
            }
        }

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

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

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

            // $totalDiasPorte = $cirurgia_cadastro_realizado['totalDiasPorte'];
            // $countDiasT = $cirurgia_cadastro_realizado['countTipoCirurgia'];
            // $mediaT = $totalDiasTipo / $countDiasT;
            // $arr['tipoCirurgia'][$key]['media'] = number_format($mediaT, 2, '.', '');

            // $tipoCirurgia = $cirurgia_cadastro_realizado['tipoCirurgia'];
            // $porte = $cirurgia_cadastro_realizado['porte'];

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

        }

        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) {
            // $totalDiasTipo = $tipo['totalDiasTipo'];
            // $countDiasT = $tipo['countTipoCirurgia'];
            // $mediaT = $totalDiasTipo / $countDiasT;
            // $arr['cirurgia_cadastro_realizado']['tipoCirurgia'][$key]['media'] = number_format($mediaT, 2, '.', '');
            $somatorioDiasP              = $porte['totalDiasPorte'];
            $countDiasP                  = $porte['countCirurgiaPorte'];
            $mediaP                      = $somatorioDiasP / $countDiasP;
            $arr['porte'][$key]['media'] = number_format($mediaP, 2, '.', '');
        }

        $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, '.', '');
        echo "<pre>";
        print_r($arr);
    }
}
