<?

class Sinistro2_Model_LaudosFinaisFuncionarios extends Agro_Db_Table_Abstract {
	protected $_schema = 'sinistro';
	protected $_name = 'laudos_finais_funcionarios';
	protected $_primary = 'id_laudo_final';
	public $_order = 'id_funcionario';
	
	public function save(array $dados): Zend_Db_Table_Row {
        $row = $this->createRow();

        $row->id_laudo_final = $dados['id_laudo_final'];
        $row->id_funcionario = $dados['id_funcionario'];
		
        $row->save();
		
        return $row;
	}

	public function find($idLaudoFinal) {
		$select = $this->select();
		$select->setIntegrityCheck(false);
		$select->from(array('f'=>$this->_schema . '.' . $this->_name), '*');
		$select->where('f.id_laudo_final = ?', $idLaudoFinal);
		
		if ($this->fetchRow($select)) {
			return $this->fetchRow($select)->toArray();
		} else {
			return array();		
		}
	}

	public function delete($idLaudoFinal) {
		$sql = "delete from {$this->_schema}.{$this->_name} where id_laudo_final = $idLaudoFinal";

		$this->_db->query($sql);
	}

	public function deleteAuxiliares($idLaudoFinal) {
		$constAuxiliar = Sistema_Model_Usuarios::TIPO_USUARIO_HELPER;
		$constAuxiliarAdmin = Sistema_Model_Usuarios::TIPO_USUARIO_MANAGER;

		$this->_db->query("
			DELETE FROM {$this->_schema}.{$this->_name}
			WHERE 
				id_laudo_final = {$idLaudoFinal}
				AND id_funcionario IN (
					SELECT
						id_funcionario
					FROM 
						{$this->_schema}.{$this->_name} lfv
					INNER JOIN sistema.funcionarios f
						ON f.id = lfv.id_funcionario
					INNER JOIN sistema.usuarios u
						ON u.id = f.id_usuario
					WHERE
						lfv.id_laudo_final = {$idLaudoFinal}
						AND u.tp_usuario IN ('{$constAuxiliar}', '{$constAuxiliarAdmin}')
				)
		");
	}

	public function getVistoriadoresByLaudoFinal($idLaudoFinal) {
		$select = $this->select();
		$select->setIntegrityCheck(false);
		$select->from(array('itf' => $this->_schema .'.'. $this->_name, '*'));
		$select->where("itf.id_laudo_final = ?", $idLaudoFinal);
		$rs = $this->fetchAll($select);
		$result = array();
		
		foreach ($rs as $item) {
			$result[$item['id_funcionario']] = array(
				'id_funcionario' => $item['id_funcionario'],
				'id_laudo_final'     => $item['id_laudo_final']
			);
		}
		
		return $result;
	}
    
    public function getArrayVistoriadoresByIdLaudoFinal($idLaudoFinal, $auxiliares=false) {
        $select = $this->select()->setIntegrityCheck(false);
		$select->from(array('itf' => $this->_schema .'.'. $this->_name, 'id_funcionario'));
		$select->join(array('f' => 'sistema.funcionarios'), 'itf.id_funcionario = f.id', false);
		$select->join(array('u' => 'sistema.usuarios'), 'f.id_usuario = u.id', false);
        $select->where("itf.id_laudo_final = ?", $idLaudoFinal);

		if ($auxiliares) {
			$select->where("u.tp_usuario = 'H'");
		} else {
			$select->where("u.tp_usuario <> 'H'");
		}

        $rs = $this->fetchAll($select);
        $result = array();
        
        foreach ($rs as $item) {
            $result[] = $item['id_funcionario'];
        }
        
        return $result;
    }

	public function getAuxiliaresByIdLaudo(int $idLaudoFinal): ?Zend_Db_Table_Rowset {
		$constAuxiliar = Sistema_Model_Usuarios::TIPO_USUARIO_HELPER;

		return $this->fetchAll(
			$this
				->select()
				->setIntegrityCheck(false)
				->from(array('itf' => $this->_schema .'.'. $this->_name, '*'))
				->join(array('f' => 'sistema.funcionarios'), 'itf.id_funcionario = f.id', false)
				->join(array('u' => 'sistema.usuarios'), 'f.id_usuario = u.id', false)
				->where('id_laudo_final = ?', $idLaudoFinal)
				->where("u.tp_usuario IN ('{$constAuxiliar}')")
		);
	}
}