set session_replication_role=replica;

alter table filiais_postocoleta
  add fil_agruparmultiguiasms boolean default false,
  add fil_agruparmultiguiaemail boolean default false;

alter table setores
  add set_tipoalimentos varchar(1) default 'O';

alter table fatura
  add fat_dataarquivo timestamp;

insert into atualizacoes(atu_nome) values ('v19030001_020419.sql');


create sequence medicocontato_sequence;

select criasequencia('medicocontato');

create table medicocontato (
       fil_codigo           integer not null,
       med_conselho         varchar(7) not null,
       med_crm              varchar(10) not null,
       med_uf               varchar(2) not null,
       mct_codigo           integer not null,
       mct_nome             varchar(100),
       mct_email            varchar(200),
       mct_enviaremail      boolean default false
);

create unique index xpkmedicocontato on medicocontato
(
       fil_codigo,
       med_conselho,
       med_crm,
       med_uf,
       mct_codigo
);

create index xif1medicocontato on medicocontato
(
       fil_codigo,
       med_conselho,
       med_crm,
       med_uf
);


alter table medicocontato
       add primary key (fil_codigo, med_conselho, med_crm, med_uf, 
              mct_codigo);




drop function fn_td_medico() cascade;

create function fn_td_medico() returns opaque as '
declare
	numrows integer;										 
begin
    select count(*) into numrows
      from medicocontato
      where
        medicocontato.fil_codigo = old.fil_codigo and
        medicocontato.med_conselho = old.med_conselho and
        medicocontato.med_crm = old.med_crm and
        medicocontato.med_uf = old.med_uf;
    if (numrows > 0) then
      raise exception ''o registro nao pode ser excluido, por estar sendo utilizado na tabela medicocontato.'';
    end if;

    select count(*) into numrows
      from orcamento
      where
        orcamento.fil_codigo = old.fil_codigo and
        orcamento.med_conselho = old.med_conselho and
        orcamento.med_crm = old.med_crm and
        orcamento.med_uf = old.med_uf;
    if (numrows > 0) then
      raise exception ''o registro nao pode ser excluido, por estar sendo utilizado na tabela orcamento.'';
    end if;

    select count(*) into numrows
      from medicodestino
      where
        medicodestino.fil_codigo = old.fil_codigo and
        medicodestino.med_conselho = old.med_conselho and
        medicodestino.med_crm = old.med_crm and
        medicodestino.med_uf = old.med_uf;
    if (numrows > 0) then
      raise exception ''o registro nao pode ser excluido, por estar sendo utilizado na tabela medicodestino.'';
    end if;

    select count(*) into numrows
      from requisicaoweb
      where
        requisicaoweb.fil_codigo = old.fil_codigo and
        requisicaoweb.med_conselho = old.med_conselho and
        requisicaoweb.med_crm = old.med_crm and
        requisicaoweb.med_uf = old.med_uf;
    if (numrows > 0) then
      raise exception ''o registro nao pode ser excluido, por estar sendo utilizado na tabela requisicaoweb.'';
    end if;

    delete from contrassenharequisicao
      where
        contrassenharequisicao.fil_codigo = old.fil_codigo and
        contrassenharequisicao.med_conselho = old.med_conselho and
        contrassenharequisicao.med_crm = old.med_crm and
        contrassenharequisicao.med_uf = old.med_uf;

    delete from medicoexameweb
      where
        medicoexameweb.fil_codigo = old.fil_codigo and
        medicoexameweb.med_conselho = old.med_conselho and
        medicoexameweb.med_crm = old.med_crm and
        medicoexameweb.med_uf = old.med_uf;

    delete from medicoconvenioproibido
      where
        medicoconvenioproibido.fil_codigo = old.fil_codigo and
        medicoconvenioproibido.med_conselho = old.med_conselho and
        medicoconvenioproibido.med_crm = old.med_crm and
        medicoconvenioproibido.med_uf = old.med_uf;

    delete from medicoconveniotiss
      where
        medicoconveniotiss.fil_codigo = old.fil_codigo and
        medicoconveniotiss.med_conselho = old.med_conselho and
        medicoconveniotiss.med_crm = old.med_crm and
        medicoconveniotiss.med_uf = old.med_uf;

    select count(*) into numrows
      from paciente
      where
        paciente.fil_codigo = old.fil_codigo and
        paciente.med_conselho = old.med_conselho and
        paciente.med_crm = old.med_crm and
        paciente.med_uf = old.med_uf;
    if (numrows > 0) then
      raise exception ''o registro nao pode ser excluido, por estar sendo utilizado na tabela paciente.'';
    end if;

    delete from medicoespecialidades
      where
        medicoespecialidades.fil_codigo = old.fil_codigo and
        medicoespecialidades.med_conselho = old.med_conselho and
        medicoespecialidades.med_crm = old.med_crm and
        medicoespecialidades.med_uf = old.med_uf;

    select count(*) into numrows
      from medicoconvenio
      where
        medicoconvenio.fil_codigo = old.fil_codigo and
        medicoconvenio.med_conselho = old.med_conselho and
        medicoconvenio.med_crm = old.med_crm and
        medicoconvenio.med_uf = old.med_uf;
    if (numrows > 0) then
      raise exception ''o registro nao pode ser excluido, por estar sendo utilizado na tabela medicoconvenio.'';
    end if;

    select count(*) into numrows
      from requisicao
      where
        requisicao.fil_codigo = old.fil_codigo and
        requisicao.med_conselho = old.med_conselho and
        requisicao.med_crm = old.med_crm and
        requisicao.med_uf = old.med_uf;
    if (numrows > 0) then
      raise exception ''o registro nao pode ser excluido, por estar sendo utilizado na tabela requisicao.'';
    end if;


return old;
end;'
language 'plpgsql';
					
create trigger td_medico
after delete on medico
for each row execute procedure fn_td_medico();


drop function fn_ti_medico() cascade;

create function fn_ti_medico() returns opaque as '
declare
	numrows integer;
	tmp varchar;									 
begin
    select count(*) into numrows
      from celulaestoque
      where
        new.fil_codigo = celulaestoque.fil_codigo and
        new.cel_codigo = celulaestoque.cel_codigo;
    tmp:=''celulaestoque|'';
    
      tmp:=tmp || ''fil_codigo='' || new.fil_codigo || ''|'';
    
      tmp:=tmp || ''cel_codigo='' || new.cel_codigo || ''|'';
    
    tmp:=substring(tmp,1,length(tmp)-1);

    if (
      new.fil_codigo is not null and
      new.cel_codigo is not null and
      numrows = 0
    ) then
      raise exception ''o registro nao pode ser inserido, pois um valor informado nao existe na tabela celulaestoque. chave para consulta:[%]'',tmp using errcode=upper(''ifkne'');
    end if;

    select count(*) into numrows
      from bairro
      where
        new.fil_codigo = bairro.fil_codigo and
        new.bai_codigo = bairro.bai_codigo;
    tmp:=''bairro|'';
    
      tmp:=tmp || ''fil_codigo='' || new.fil_codigo || ''|'';
    
      tmp:=tmp || ''bai_codigo='' || new.bai_codigo || ''|'';
    
    tmp:=substring(tmp,1,length(tmp)-1);

    if (
      new.fil_codigo is not null and
      new.bai_codigo is not null and
      numrows = 0
    ) then
      raise exception ''o registro nao pode ser inserido, pois um valor informado nao existe na tabela bairro. chave para consulta:[%]'',tmp using errcode=upper(''ifkne'');
    end if;

    select count(*) into numrows
      from filiais_postocoleta
      where
        new.fil_codigo = filiais_postocoleta.fil_codigo;
    tmp:=''filiais_postocoleta|'';
    
      tmp:=tmp || ''fil_codigo='' || new.fil_codigo || ''|'';
    
    tmp:=substring(tmp,1,length(tmp)-1);

    if (
      
      numrows = 0
    ) then
      raise exception ''o registro nao pode ser inserido, pois um valor informado nao existe na tabela filiais_postocoleta. chave para consulta:[%]'',tmp using errcode=upper(''ifkne'');
    end if;

    select count(*) into numrows
      from cidades
      where
        new.fil_codigo = cidades.fil_codigo and
        new.cid_codigo = cidades.cid_codigo;
    tmp:=''cidades|'';
    
      tmp:=tmp || ''fil_codigo='' || new.fil_codigo || ''|'';
    
      tmp:=tmp || ''cid_codigo='' || new.cid_codigo || ''|'';
    
    tmp:=substring(tmp,1,length(tmp)-1);

    if (
      new.fil_codigo is not null and
      new.cid_codigo is not null and
      numrows = 0
    ) then
      raise exception ''o registro nao pode ser inserido, pois um valor informado nao existe na tabela cidades. chave para consulta:[%]'',tmp using errcode=upper(''ifkne'');
    end if;


return new;
end;'
language 'plpgsql';
					
create trigger ti_medico
after insert on medico
for each row execute procedure fn_ti_medico();


drop function fn_tu_medico() cascade;

create function fn_tu_medico() returns opaque as '
declare
	numrows integer;
        tmp varchar;										 
begin
  if
    (old.fil_codigo <> new.fil_codigo or 
     old.med_conselho <> new.med_conselho or 
     old.med_crm <> new.med_crm or 
     old.med_uf <> new.med_uf) then
    select count(*)
      from medicocontato
      where
        medicocontato.fil_codigo = old.fil_codigo and
        medicocontato.med_conselho = old.med_conselho and
        medicocontato.med_crm = old.med_crm and
        medicocontato.med_uf = old.med_uf into numrows;
    if (numrows > 0) then
     raise exception ''o registro nao pode ser alterado, pois um valor esta sendo utilizado na tabela medicocontato.'';
    end if;
  end if;
  if
    (old.fil_codigo <> new.fil_codigo or 
     old.med_conselho <> new.med_conselho or 
     old.med_crm <> new.med_crm or 
     old.med_uf <> new.med_uf) then
    select count(*)
      from orcamento
      where
        orcamento.fil_codigo = old.fil_codigo and
        orcamento.med_conselho = old.med_conselho and
        orcamento.med_crm = old.med_crm and
        orcamento.med_uf = old.med_uf into numrows;
    if (numrows > 0) then
     raise exception ''o registro nao pode ser alterado, pois um valor esta sendo utilizado na tabela orcamento.'';
    end if;
  end if;
  if
    (old.fil_codigo <> new.fil_codigo or 
     old.med_conselho <> new.med_conselho or 
     old.med_crm <> new.med_crm or 
     old.med_uf <> new.med_uf) then
    select count(*)
      from medicodestino
      where
        medicodestino.fil_codigo = old.fil_codigo and
        medicodestino.med_conselho = old.med_conselho and
        medicodestino.med_crm = old.med_crm and
        medicodestino.med_uf = old.med_uf into numrows;
    if (numrows > 0) then
     raise exception ''o registro nao pode ser alterado, pois um valor esta sendo utilizado na tabela medicodestino.'';
    end if;
  end if;
  if
    (old.fil_codigo <> new.fil_codigo or 
     old.med_conselho <> new.med_conselho or 
     old.med_crm <> new.med_crm or 
     old.med_uf <> new.med_uf) then
    select count(*)
      from requisicaoweb
      where
        requisicaoweb.fil_codigo = old.fil_codigo and
        requisicaoweb.med_conselho = old.med_conselho and
        requisicaoweb.med_crm = old.med_crm and
        requisicaoweb.med_uf = old.med_uf into numrows;
    if (numrows > 0) then
     raise exception ''o registro nao pode ser alterado, pois um valor esta sendo utilizado na tabela requisicaoweb.'';
    end if;
  end if;
  if
    (old.fil_codigo <> new.fil_codigo or 
     old.med_conselho <> new.med_conselho or 
     old.med_crm <> new.med_crm or 
     old.med_uf <> new.med_uf) then
    select count(*)
      from contrassenharequisicao
      where
        contrassenharequisicao.fil_codigo = old.fil_codigo and
        contrassenharequisicao.med_conselho = old.med_conselho and
        contrassenharequisicao.med_crm = old.med_crm and
        contrassenharequisicao.med_uf = old.med_uf into numrows;
    if (numrows > 0) then
     raise exception ''o registro nao pode ser alterado, pois um valor esta sendo utilizado na tabela contrassenharequisicao.'';
    end if;
  end if;
  if
    (old.fil_codigo <> new.fil_codigo or 
     old.med_conselho <> new.med_conselho or 
     old.med_crm <> new.med_crm or 
     old.med_uf <> new.med_uf) then
    select count(*)
      from medicoexameweb
      where
        medicoexameweb.fil_codigo = old.fil_codigo and
        medicoexameweb.med_conselho = old.med_conselho and
        medicoexameweb.med_crm = old.med_crm and
        medicoexameweb.med_uf = old.med_uf into numrows;
    if (numrows > 0) then
     raise exception ''o registro nao pode ser alterado, pois um valor esta sendo utilizado na tabela medicoexameweb.'';
    end if;
  end if;
  if
    (old.fil_codigo <> new.fil_codigo or 
     old.med_conselho <> new.med_conselho or 
     old.med_crm <> new.med_crm or 
     old.med_uf <> new.med_uf) then
    select count(*)
      from medicoconvenioproibido
      where
        medicoconvenioproibido.fil_codigo = old.fil_codigo and
        medicoconvenioproibido.med_conselho = old.med_conselho and
        medicoconvenioproibido.med_crm = old.med_crm and
        medicoconvenioproibido.med_uf = old.med_uf into numrows;
    if (numrows > 0) then
     raise exception ''o registro nao pode ser alterado, pois um valor esta sendo utilizado na tabela medicoconvenioproibido.'';
    end if;
  end if;
  if
    (old.fil_codigo <> new.fil_codigo or 
     old.med_conselho <> new.med_conselho or 
     old.med_crm <> new.med_crm or 
     old.med_uf <> new.med_uf) then
    select count(*)
      from medicoconveniotiss
      where
        medicoconveniotiss.fil_codigo = old.fil_codigo and
        medicoconveniotiss.med_conselho = old.med_conselho and
        medicoconveniotiss.med_crm = old.med_crm and
        medicoconveniotiss.med_uf = old.med_uf into numrows;
    if (numrows > 0) then
     raise exception ''o registro nao pode ser alterado, pois um valor esta sendo utilizado na tabela medicoconveniotiss.'';
    end if;
  end if;
  if
    (old.fil_codigo <> new.fil_codigo or 
     old.med_conselho <> new.med_conselho or 
     old.med_crm <> new.med_crm or 
     old.med_uf <> new.med_uf) then
    select count(*)
      from paciente
      where
        paciente.fil_codigo = old.fil_codigo and
        paciente.med_conselho = old.med_conselho and
        paciente.med_crm = old.med_crm and
        paciente.med_uf = old.med_uf into numrows;
    if (numrows > 0) then
     raise exception ''o registro nao pode ser alterado, pois um valor esta sendo utilizado na tabela paciente.'';
    end if;
  end if;
  if
    (old.fil_codigo <> new.fil_codigo or 
     old.med_conselho <> new.med_conselho or 
     old.med_crm <> new.med_crm or 
     old.med_uf <> new.med_uf) then
    select count(*)
      from medicoespecialidades
      where
        medicoespecialidades.fil_codigo = old.fil_codigo and
        medicoespecialidades.med_conselho = old.med_conselho and
        medicoespecialidades.med_crm = old.med_crm and
        medicoespecialidades.med_uf = old.med_uf into numrows;
    if (numrows > 0) then
     raise exception ''o registro nao pode ser alterado, pois um valor esta sendo utilizado na tabela medicoespecialidades.'';
    end if;
  end if;
  if
    (old.fil_codigo <> new.fil_codigo or 
     old.med_conselho <> new.med_conselho or 
     old.med_crm <> new.med_crm or 
     old.med_uf <> new.med_uf) then
    select count(*)
      from medicoconvenio
      where
        medicoconvenio.fil_codigo = old.fil_codigo and
        medicoconvenio.med_conselho = old.med_conselho and
        medicoconvenio.med_crm = old.med_crm and
        medicoconvenio.med_uf = old.med_uf into numrows;
    if (numrows > 0) then
     raise exception ''o registro nao pode ser alterado, pois um valor esta sendo utilizado na tabela medicoconvenio.'';
    end if;
  end if;
  if
    (old.fil_codigo <> new.fil_codigo or 
     old.med_conselho <> new.med_conselho or 
     old.med_crm <> new.med_crm or 
     old.med_uf <> new.med_uf) then
    select count(*)
      from requisicao
      where
        requisicao.fil_codigo = old.fil_codigo and
        requisicao.med_conselho = old.med_conselho and
        requisicao.med_crm = old.med_crm and
        requisicao.med_uf = old.med_uf into numrows;
    if (numrows > 0) then
     raise exception ''o registro nao pode ser alterado, pois um valor esta sendo utilizado na tabela requisicao.'';
    end if;
  end if;
  select count(*) into numrows
    from celulaestoque
    where
      new.fil_codigo = celulaestoque.fil_codigo and
      new.cel_codigo = celulaestoque.cel_codigo;
    tmp:=''celulaestoque|'';
    
      tmp:=tmp || ''fil_codigo='' || new.fil_codigo || ''|'';
    
      tmp:=tmp || ''cel_codigo='' || new.cel_codigo || ''|'';
    
    tmp:=substring(tmp,1,length(tmp)-1);
  if (
    new.fil_codigo is not null and
    new.cel_codigo is not null and
    numrows = 0
  ) then
    raise exception ''o registro nao pode ser alterado, pois um valor nao existe na tabela celulaestoque. chave para consulta:[%]'',tmp using errcode=upper(''ufkne'');
  end if;

  select count(*) into numrows
    from bairro
    where
      new.fil_codigo = bairro.fil_codigo and
      new.bai_codigo = bairro.bai_codigo;
    tmp:=''bairro|'';
    
      tmp:=tmp || ''fil_codigo='' || new.fil_codigo || ''|'';
    
      tmp:=tmp || ''bai_codigo='' || new.bai_codigo || ''|'';
    
    tmp:=substring(tmp,1,length(tmp)-1);
  if (
    new.fil_codigo is not null and
    new.bai_codigo is not null and
    numrows = 0
  ) then
    raise exception ''o registro nao pode ser alterado, pois um valor nao existe na tabela bairro. chave para consulta:[%]'',tmp using errcode=upper(''ufkne'');
  end if;

  select count(*) into numrows
    from filiais_postocoleta
    where
      new.fil_codigo = filiais_postocoleta.fil_codigo;
    tmp:=''filiais_postocoleta|'';
    
      tmp:=tmp || ''fil_codigo='' || new.fil_codigo || ''|'';
    
    tmp:=substring(tmp,1,length(tmp)-1);
  if (
    
    numrows = 0
  ) then
    raise exception ''o registro nao pode ser alterado, pois um valor nao existe na tabela filiais_postocoleta. chave para consulta:[%]'',tmp using errcode=upper(''ufkne'');
  end if;

  select count(*) into numrows
    from cidades
    where
      new.fil_codigo = cidades.fil_codigo and
      new.cid_codigo = cidades.cid_codigo;
    tmp:=''cidades|'';
    
      tmp:=tmp || ''fil_codigo='' || new.fil_codigo || ''|'';
    
      tmp:=tmp || ''cid_codigo='' || new.cid_codigo || ''|'';
    
    tmp:=substring(tmp,1,length(tmp)-1);
  if (
    new.fil_codigo is not null and
    new.cid_codigo is not null and
    numrows = 0
  ) then
    raise exception ''o registro nao pode ser alterado, pois um valor nao existe na tabela cidades. chave para consulta:[%]'',tmp using errcode=upper(''ufkne'');
  end if;


return old;
end;'
language 'plpgsql';
					
create trigger tu_medico
after update on medico
for each row execute procedure fn_tu_medico();


--drop function fn_ti_medicocontato() cascade;

create function fn_ti_medicocontato() returns opaque as '
declare
	numrows integer;
	tmp varchar;									 
begin
    select count(*) into numrows
      from medico
      where
        new.fil_codigo = medico.fil_codigo and
        new.med_conselho = medico.med_conselho and
        new.med_crm = medico.med_crm and
        new.med_uf = medico.med_uf;
    tmp:=''medico|'';
    
      tmp:=tmp || ''fil_codigo='' || new.fil_codigo || ''|'';
    
      tmp:=tmp || ''med_conselho='' || new.med_conselho || ''|'';
    
      tmp:=tmp || ''med_crm='' || new.med_crm || ''|'';
    
      tmp:=tmp || ''med_uf='' || new.med_uf || ''|'';
    
    tmp:=substring(tmp,1,length(tmp)-1);

    if (
      
      numrows = 0
    ) then
      raise exception ''o registro nao pode ser inserido, pois um valor informado nao existe na tabela medico. chave para consulta:[%]'',tmp using errcode=upper(''ifkne'');
    end if;


return new;
end;'
language 'plpgsql';
					
create trigger ti_medicocontato
after insert on medicocontato
for each row execute procedure fn_ti_medicocontato();


--drop function fn_tu_medicocontato() cascade;

create function fn_tu_medicocontato() returns opaque as '
declare
	numrows integer;
        tmp varchar;										 
begin
  select count(*) into numrows
    from medico
    where
      new.fil_codigo = medico.fil_codigo and
      new.med_conselho = medico.med_conselho and
      new.med_crm = medico.med_crm and
      new.med_uf = medico.med_uf;
    tmp:=''medico|'';
    
      tmp:=tmp || ''fil_codigo='' || new.fil_codigo || ''|'';
    
      tmp:=tmp || ''med_conselho='' || new.med_conselho || ''|'';
    
      tmp:=tmp || ''med_crm='' || new.med_crm || ''|'';
    
      tmp:=tmp || ''med_uf='' || new.med_uf || ''|'';
    
    tmp:=substring(tmp,1,length(tmp)-1);
  if (
    
    numrows = 0
  ) then
    raise exception ''o registro nao pode ser alterado, pois um valor nao existe na tabela medico. chave para consulta:[%]'',tmp using errcode=upper(''ufkne'');
  end if;


return old;
end;'
language 'plpgsql';
					
create trigger tu_medicocontato
after update on medicocontato
for each row execute procedure fn_tu_medicocontato();

insert into atualizacoes(atu_nome) values ('v19030002_250419.sql');



alter table notafiscalmaterial
    add   nfm_ordem            smallint not null default -1;

create index xieordemnotafiscal on notafiscalmaterial
(
       fil_codigo,
       nfi_codigo,
       nfm_ordem
);


create or replace function atualizanotamaterial() returns integer
as $$
declare
  contador integer;
  r record;
  anterior integer;
begin
  anterior = -1;
  for r in select fil_codigo, nfi_codigo, nfm_ordem, mat_codigo from notafiscalmaterial order by 1,2,4
  loop
    if anterior <> r.nfi_codigo then
      contador = 1;
      anterior = r.nfi_codigo;
    end if;
    execute 'update notafiscalmaterial set nfm_ordem = ' || contador || ' where fil_codigo =  ' || r.fil_codigo || ' and nfi_codigo = ' || r.nfi_codigo || ' and mat_codigo = ' || r.mat_codigo;
    contador = contador + 1;
  end loop;
  return 1;
end;

$$ language plpgsql;

select atualizanotamaterial();

insert into atualizacoes(atu_nome) values ('v19030003_070519.sql');