ALTER TABLE TBCCUSTO_COTA_DETALHE
    ADD ANO COMPUTED BY (extract(year from data)),
    ADD MES COMPUTED BY (extract(month from data));

CREATE INDEX ITBCCUSTO_COTA_DETALHE_09
ON TBCCUSTO_COTA_DETALHE COMPUTED BY (extract(month from data));

CREATE INDEX ITBCCUSTO_COTA_DETALHE_08
ON TBCCUSTO_COTA_DETALHE COMPUTED BY (extract(year from data));

ALTER TABLE TBDRE_GERENCIAL_CONTAS
    ADD NATUREZA_SERVICO VARCHAR(10);

CREATE TABLE TBDRE_GERENCIAL_SERVICOS (
    ID INTEGER NOT NULL,
    CONTA VARCHAR(20),
    SQL BLOB SUB_TYPE 0 SEGMENT SIZE 80);

ALTER TABLE TBDRE_GERENCIAL_SERVICOS
ADD CONSTRAINT PK_TBDRE_GERENCIAL_SERVICOS
PRIMARY KEY (ID);

CREATE SEQUENCE GTBDRE_GERENCIAL_SERVICOS;

create trigger ttbdre_gerencial_servicos_b01i for tbdre_gerencial_servicos
active before insert position 0
as
begin
  if (new.id is null) then
    new.id = gen_id(gtbdre_gerencial_servicos,1);
end;

COMMENT ON TABLE TBDRE_GERENCIAL_SERVICOS IS 'tabela com valores vindos de ouytras tabelas e nao vinculados a contas, um ex. e o faturamento dos servicos';

ALTER TABLE TBCCUSTO_COTA_OUTROS
    ADD CONTACONTABIL_DRE VARCHAR(20);

CREATE OR ALTER function fn_dre_servicos (
    conta varchar(20),
    mes integer,
    ano integer)
returns numeric(15,4)
as
declare variable valor numeric(15,4);
declare variable sql varchar(3000);
begin

  VALOR = 0;

  for
    select
        c.sql
    from tbdre_gerencial_servicos c
    where c.conta = :CONTA
  into
    :SQL
  do begin
    :SQL   = replace(:SQL,':MES',:MES);
    :SQL   = replace(:SQL,':ANO',:ANO);

    :VALOR = :VALOR + FN_EXECUTE_CALC('('||:SQL||')');
  end

  return coalesce(VALOR,0);
end;

CREATE OR ALTER function fn_dre_lancamentos (
    conta varchar(20),
    ano integer,
    mes integer,
    tipo integer,
    natureza_servico varchar(10))
returns numeric(15,4)
as
declare variable valor numeric(15,4);
declare variable v1 numeric(15,4);
declare variable v2 numeric(15,4);
declare variable v3 numeric(15,4);
begin
   V1 = 0;
   V2 = 0;
   V3 = 0;

   if(TIPO = 1 or TIPO = 3 )then
   begin
       V1 = coalesce((
                select sum(iif(m.natureza = 'D', m.valor * -1, m.valor))
                from tbmovcontabil m
                where m.ano = :ANO
                and m.mes   = :MES
                and m.conta = :CONTA
                and m.tipolanc <> 'E'
            ),0) +
            coalesce((
                select sum(iif(m.tipo = 'D', m.valor * -1, m.valor))
                from tbdre_gerencial_ajuste m
                where m.ano = :ANO
                and m.mes   = :MES
                and m.conta = :CONTA
            ),0);
   end

   if(TIPO = 2 or TIPO = 3)then
   begin
       V2 = coalesce((
                select sum(b.valor * iif(:NATUREZA_SERVICO = 'D', -1, 1))
                from tbccusto_cota a, tbccusto_cota_outros b
                where a.id = b.ccusto_cota_id and b.statusexclusao = '0' and a.ano = :ANO
                and a.mes = :MES
                and a.id = b.CCUSTO_COTA_ID
                and b.contacontabil_dre = :CONTA
                and b.STATUSEXCLUSAO = 0
            ),0) + FN_DRE_SERVICOS(:CONTA,  :MES, :ANO );
   end

   VALOR = V1 + V2 + V3;

   return VALOR;
end;

CREATE INDEX ITBCCUSTO_COTA_OUTROS_03
ON TBCCUSTO_COTA_OUTROS (CONTACONTABIL_DRE);

ALTER TABLE TBDRE_GERENCIAL_CONTAS
    ADD SQL BLOB SUB_TYPE 0 SEGMENT SIZE 80;

CREATE OR ALTER function fn_dre_servicos (
    conta varchar(20),
    mes integer,
    ano integer)
returns numeric(15,4)
as
declare variable valor numeric(15,4);
declare variable sql varchar(3000);
begin

  VALOR = 0;

  for
    select
        c.sql
    from tbdre_gerencial_contas c
    where c.conta = :CONTA
  into
    :SQL
  do begin
    :SQL   = replace(:SQL,':MES',:MES);
    :SQL   = replace(:SQL,':ANO',:ANO);

    :VALOR = :VALOR + FN_EXECUTE_CALC('('||:SQL||')');
  end

  return coalesce(VALOR,0);
end;

DROP TRIGGER TTBDRE_GERENCIAL_SERVICOS_B01I;

DROP TABLE TBDRE_GERENCIAL_SERVICOS;

CREATE OR ALTER function fn_dre_servicos (
    conta varchar(20),
    mes integer,
    ano integer)
returns numeric(15,4)
as
declare variable valor numeric(15,4);
declare variable sql varchar(3000);
begin

  VALOR = 0;

  for
    select
        c.sql
    from tbdre_gerencial_contas c
    where c.conta = :CONTA
  into
    :SQL
  do begin
    :SQL   = replace(:SQL,':MES'    ,:MES   );
    :SQL   = replace(:SQL,':ANO'    ,:ANO   );
    :SQL   = replace(:SQL,':CONTA'  ,:CONTA );

    :VALOR = :VALOR + FN_EXECUTE_CALC('('||:SQL||')');
  end

  return coalesce(VALOR,0);
end;


ALTER TABLE TBDRE_GERENCIAL_CONTAS
    ADD USARREGRA_COMERCIAL INTEGER DEFAULT 1,
    ADD USARREGRA_SERVICOS INTEGER DEFAULT 1;

CREATE OR ALTER function fn_dre_lancamentos (
    conta varchar(20),
    ano integer,
    mes integer,
    tipo integer,
    natureza_servico varchar(10),
    usar_regra_comercial integer = 0,
    usar_regra_servicos integer = 0)
returns numeric(15,4)
as
declare variable valor numeric(15,4);
declare variable v1 numeric(15,4);
declare variable v2 numeric(15,4);
declare variable v3 numeric(15,4);
begin
   V1 = 0;
   V2 = 0;
   V3 = 0;

   if(USAR_REGRA_COMERCIAL = 0 and (TIPO = 1 or TIPO = 3))then
   begin
       V1 = coalesce((
                select sum(iif(m.natureza = 'D', m.valor * -1, m.valor))
                from tbmovcontabil m
                where m.ano = :ANO
                and m.mes   = :MES
                and m.conta = :CONTA
                and m.tipolanc <> 'E'
            ),0) +
            coalesce((
                select sum(iif(m.tipo = 'D', m.valor * -1, m.valor))
                from tbdre_gerencial_ajuste m
                where m.ano = :ANO
                and m.mes   = :MES
                and m.conta = :CONTA
            ),0);
   end

   if(USAR_REGRA_SERVICOS = 0 and (TIPO = 2 or TIPO = 3))then
   begin
       V2 = coalesce((
                select sum(b.valor * iif(:NATUREZA_SERVICO = 'D', -1, 1))
                from tbccusto_cota a, tbccusto_cota_outros b
                where a.id = b.ccusto_cota_id
                and b.statusexclusao = '0'
                and a.ano = :ANO
                and a.mes = :MES
                and b.contacontabil_dre = :CONTA
            ),0);
   end

   if(USAR_REGRA_COMERCIAL = 1 and (TIPO = 2 or TIPO = 3))then
   begin
      V1 = FN_DRE_SERVICOS(:CONTA,  :MES, :ANO );
   end

   if(USAR_REGRA_SERVICOS = 1 and (TIPO = 2 or TIPO = 3))then
   begin
      V2 = FN_DRE_SERVICOS(:CONTA,  :MES, :ANO );
   end

   VALOR = V1 + V2 + V3;

   return VALOR;
end;

CREATE TABLE TBDRE_GERENCIAL_TOTALIZADOR (
    ID INTEGER NOT NULL,
    DESCRICAO VARCHAR(50),
    NIVEL INTEGER,
    VINCULO_ID INTEGER,
    FORMULA1 BLOB SUB_TYPE 0 SEGMENT SIZE 80,
    OPERACAO VARCHAR(1),
    FORMULA2 BLOB SUB_TYPE 0 SEGMENT SIZE 80);

ALTER TABLE TBDRE_GERENCIAL_TOTALIZADOR
ADD CONSTRAINT PK_TBDRE_GERENCIAL_TOTALIZADOR
PRIMARY KEY (ID);

COMMENT ON COLUMN TBDRE_GERENCIAL_TOTALIZADOR.NIVEL IS
'1-grupo, 2-subgrupo';

COMMENT ON COLUMN TBDRE_GERENCIAL_TOTALIZADOR.VINCULO_ID IS
'id do grupo ou subgrupo';

COMMENT ON COLUMN TBDRE_GERENCIAL_TOTALIZADOR.FORMULA1 IS
'formula que contem os grupos ou subgrupos';

COMMENT ON COLUMN TBDRE_GERENCIAL_TOTALIZADOR.OPERACAO IS
'se vai somar ou subtrair (+, -, * ou /)';

COMMENT ON COLUMN TBDRE_GERENCIAL_TOTALIZADOR.FORMULA2 IS
'sql que retorna um numero, geralmente usado para consultar impostos';

CREATE SEQUENCE GTBDRE_GERENCIAL_TOT;

create trigger ttbdre_gerencial_tot_b01i for tbdre_gerencial_totalizador
active before insert position 0
as
begin
  if (new.id is null) then
    new.id = gen_id(gtbdre_gerencial_tot,1);
end;

COMMENT ON TABLE TBDRE_GERENCIAL_TOTALIZADOR IS 'tabela de totalizadores da dre';

ALTER TABLE TBDRE_GERENCIAL_TOTALIZADOR
    ADD ORDEM INTEGER;

ALTER TABLE TBDRE_GERENCIAL_TOTALIZADOR
    ADD STATUS INTEGER;

ALTER TABLE TBDRE_GERENCIAL
    ADD COR VARCHAR(20);

ALTER TABLE TBDRE_GERENCIAL_CONTAS
    ADD COR VARCHAR(20);

ALTER TABLE TBDRE_GERENCIAL_GRUPOS
    ADD COR VARCHAR(20);

ALTER TABLE TBDRE_GERENCIAL_SUBGRUPOS
    ADD COR VARCHAR(20);

ALTER TABLE TBDRE_GERENCIAL_TOTALIZADOR
    ADD COR VARCHAR(20);

