CREATE TABLE TBFAMILIA_FATURAMENTO (
    ID INTEGER,
    MES INTEGER,
    ANO INTEGER,
    FAMILIA_ID INTEGER,
    FATURAMENTO NUMERIC(15,2));

CREATE SEQUENCE TBFAMILIA_FATURAMENTO;

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

COMMENT ON TABLE TBFAMILIA_FATURAMENTO IS 'faturamento mensal por familia';

CREATE OR ALTER procedure spu_faturamento_familia (
    ano integer,
    mes integer)
as
declare variable familia_id integer;
declare variable faturamento numeric(15,2);
begin

   delete from tbfamilia_faturamento f where f.ano = :ANO and f.mes = :MES;

   for
      SELECT
        
            s.CODIGO,
            s.QUANTIDADE as FATURADO
        
        from(
            Select
            Z.familia_codigo as CODIGO,
            f.descricao_amigavel as descricao,
            Sum(Z.Quantidade) Quantidade
                From
                (
            
                SELECT p.familia_codigo, SUM(A.valor_total) Quantidade
                From TBNFS D, TbNfs_Item A, TbOperacao C, TbProduto P
                Where D.CONTROLE  = A.Nfs_controle
                  and A.Operacao_Codigo = C.Codigo
                  and a.Produto_Codigo = P.Codigo
                  and ((D.Natureza = 1 and D.Situacao = 2) or (D.Natureza = 2 and D.Situacao = 1))
                  and C.Controle_Faturamento = 1
                  and A.Numero_NotaFiscal > 0
                  and A.Data_Emissao between fn_start_of_month('01.'||:MES||'.'||:ANO) and fn_end_of_month('01.'||:MES||'.'||:ANO)
                Group By 1
            
                ) Z, tbfamilia f where f.codigo = Z.familia_codigo Group By 1,2
        ) s order by DESCRICAO

   into
        :FAMILIA_ID,
        :FATURAMENTO
   do
   begin

      UPDATE OR INSERT INTO TBFAMILIA_FATURAMENTO (MES, ANO, FAMILIA_ID, FATURAMENTO)
                                     VALUES (:MES, :ANO, :FAMILIA_ID, :FATURAMENTO)
                                   MATCHING (MES, ANO, FAMILIA_ID);
   end

end;

COMMENT ON PROCEDURE SPU_FATURAMENTO_FAMILIA IS 'calcula o faturamento mensal das familias com faturamento';

