drop function fn_tu_b_examerequisicao() cascade;

create function fn_tu_b_examerequisicao() returns opaque as 
$$
declare
  req bigint;
begin
if (new.exr_situacao is null or trim(new.exr_situacao) = '') and (new.exr_dataassina is not null or old.exr_dataassina is not null) then
  --raise exception 'O exame já foi alterado por outro usuário, verifique a situação atual do exame, %, %', new.req_codigo, new.exa_codigo;
end if;

select req_codigo into req from examerequisicaoresultado where fil_codigo = new.fil_codigo and req_codigo = new.req_codigo and exa_codigo = new.exa_codigo and mco_codigo = new.mco_codigo;
if (NOT FOUND) then
  insert into examerequisicaoresultado(fil_codigo, req_codigo, exa_codigo, mco_codigo) values (new.fil_codigo, new.req_codigo, new.exa_codigo, new.mco_codigo);
end if;


if ((upper(new.exr_situacao) = upper('L') or upper(new.exr_situacao) = upper('C') or upper(new.exr_situacao) = upper('I') or upper(new.exr_situacao) = upper('E') or upper(new.exr_situacao) = upper('P')) and 
  new.exr_resultado is not null and length(new.exr_resultado) > 0) then
  update examerequisicaoresultado set ert_resultado = new.exr_resultado where fil_codigo = new.fil_codigo and req_codigo = new.req_codigo and exa_codigo = new.exa_codigo and mco_codigo = new.mco_codigo;
end if;

if ((new.exr_situacao is null or trim(new.exr_situacao) = '' or upper(new.exr_situacao) = upper('F') or upper(new.exr_situacao) = upper('T') or upper(new.exr_situacao) = upper('R'))) then
  update examerequisicaoresultado set ert_resultado = new.exr_resultado where fil_codigo = new.fil_codigo and req_codigo = new.req_codigo and exa_codigo = new.exa_codigo and mco_codigo = new.mco_codigo;
end if;


/* troca o valor do campo de resultado na tabela exame requisicao */
new.exr_resultado := null;

return new;

END
$$
language 'plpgsql';



CREATE TRIGGER tu_b_examerequisicao 
  BEFORE UPDATE on ExameRequisicao
for each row execute procedure fn_tu_b_examerequisicao();




create or replace function todosexamesprontos(integer, integer) returns boolean AS $$
declare 
  total integer;
  prontos integer;
  codigofilial alias for $1;
  codigorequisicao alias for $2;
begin
    select count(exa_codigo) into total from examerequisicao where fil_codigo = codigofilial and req_codigo = codigorequisicao and exr_excluido = false;
    select count(exa_codigo) into prontos from examerequisicao where fil_codigo = codigofilial and req_codigo = codigorequisicao and exr_excluido = false 
   		                                                         and upper(exr_situacao) in (upper('C'), upper('I'), upper('E'), upper('A'));
    raise notice 'e ae? % % %', total = prontos, total, prontos;	

	return total = prontos;
end;
$$ language plpgsql;


create or replace function pegavalorcomissaomedicoconvenio(integer, integer, varchar, varchar, varchar) returns numeric(15,4) AS $$
declare 
  comissao numeric(15, 4);
  codigofilial alias for $1;
  codigoconvenio alias for $2;
  codigomedico alias for $3;
  conselhomedico alias for $4;
  ufmedico alias for $5;
begin
  select coalesce(mco_comissao, 0) into comissao 
	    from medicoconvenio
	   where fil_codigo = codigofilial
	     and con_codigo = codigoconvenio
		 and med_crm = codigomedico
		 and med_conselho = conselhomedico
		 and med_uf = ufmedico;
	
	if (not found) then
	  comissao = 0;
	end if;

	return comissao;
end;
$$ language plpgsql;



insert into atualizacoes(atu_nome) values ('v22020001_090222sql');