-- View: public.view_dashboard_impostos

DROP VIEW public.view_dashboard_impostos;

CREATE OR REPLACE VIEW public.view_dashboard_impostos
 AS
 SELECT filial,
    ano_mes,
    tipo,
    icms,
    ipi,
    iss,
    pis,
    cofins,
    icms_subst,
    importacao,
    difal
   FROM ( SELECT bas_filial.codigo AS filial,
            tabaux.ano_mes::integer AS ano_mes,
            '1 - Saida'::text AS tipo,
            COALESCE(sum(
                CASE
                    WHEN nfs_op_fiscal.entrada_saida::text = 'S'::text THEN nfs_nota_fiscal.vlr_icms * '-1'::integer::numeric
                    ELSE nfs_nota_fiscal.vlr_icms
                END), 0.000000) AS icms,
            COALESCE(sum(
                CASE
                    WHEN nfs_op_fiscal.entrada_saida::text = 'S'::text THEN nfs_nota_fiscal.vlr_ipi * '-1'::integer::numeric
                    ELSE nfs_nota_fiscal.vlr_ipi
                END), 0.000000) AS ipi,
            COALESCE(sum(
                CASE
                    WHEN nfs_op_fiscal.entrada_saida::text = 'S'::text THEN nfs_nota_fiscal.vlr_iss * '-1'::integer::numeric
                    ELSE nfs_nota_fiscal.vlr_iss
                END), 0.000000) AS iss,
            COALESCE(sum(
                CASE
                    WHEN nfs_op_fiscal.entrada_saida::text = 'S'::text THEN nfs_nota_fiscal.vlr_pis * '-1'::integer::numeric
                    ELSE nfs_nota_fiscal.vlr_pis
                END), 0.000000) AS pis,
            COALESCE(sum(
                CASE
                    WHEN nfs_op_fiscal.entrada_saida::text = 'S'::text THEN nfs_nota_fiscal.valor_cofins * '-1'::integer::numeric
                    ELSE nfs_nota_fiscal.valor_cofins
                END), 0.000000) AS cofins,
            COALESCE(sum(
                CASE
                    WHEN nfs_op_fiscal.entrada_saida::text = 'S'::text THEN nfs_nota_fiscal.vlr_icms_substituicao * '-1'::integer::numeric
                    ELSE nfs_nota_fiscal.vlr_icms_substituicao
                END), 0.000000) AS icms_subst,
            COALESCE(sum(
                CASE
                    WHEN nfs_op_fiscal.entrada_saida::text = 'S'::text THEN nfs_nota_fiscal.total_imp_import * '-1'::integer::numeric
                    ELSE nfs_nota_fiscal.total_imp_import
                END), 0.000000) AS importacao,
            COALESCE(sum(
                CASE
                    WHEN nfs_op_fiscal.entrada_saida::text = 'S'::text THEN (nfs_nota_fiscal.valor_total_icms_destino + nfs_nota_fiscal.valor_total_icms_origem) * '-1'::integer::numeric
                    ELSE nfs_nota_fiscal.valor_total_icms_destino + nfs_nota_fiscal.valor_total_icms_origem
                END), 0.000000) AS difal
           FROM ( SELECT to_char(CURRENT_DATE - '1 mon'::interval month * generate_series(0, 11)::double precision, 'YYYYmm'::text) AS ano_mes) tabaux
             CROSS JOIN bas_filial
             LEFT JOIN nfs_nota_fiscal ON to_char(nfs_nota_fiscal.data_emissao::timestamp with time zone, 'YYYYmm'::text)::integer = tabaux.ano_mes::integer AND nfs_nota_fiscal.filial_nota = bas_filial.codigo AND nfs_nota_fiscal.cancelada::text = 'N'::text
             LEFT JOIN nfs_op_fiscal ON nfs_op_fiscal.cfop = nfs_nota_fiscal.cfop
          GROUP BY bas_filial.codigo, tabaux.ano_mes
        UNION ALL
         SELECT bas_filial.codigo AS filial,
            tabaux.ano_mes::integer AS ano_mes,
            '2 - Entrada'::text AS tipo,
            COALESCE(sum(
                CASE
                    WHEN nfs_op_fiscal.entrada_saida::text = 'E'::text THEN ent_nota_fiscal.icm_informado
                    ELSE ent_nota_fiscal.icm_informado * '-1'::integer::numeric
                END), 0.000000) AS icms,
            COALESCE(sum(
                CASE
                    WHEN nfs_op_fiscal.entrada_saida::text = 'E'::text THEN ent_nota_fiscal.total_ipi
                    ELSE ent_nota_fiscal.total_ipi * '-1'::integer::numeric
                END), 0.000000) AS ipi,
            COALESCE(sum(
                CASE
                    WHEN nfs_op_fiscal.entrada_saida::text = 'E'::text THEN ent_nota_fiscal.vlr_iss
                    ELSE ent_nota_fiscal.vlr_iss * '-1'::integer::numeric
                END), 0.000000) AS iss,
            COALESCE(sum(
                CASE
                    WHEN nfs_op_fiscal.entrada_saida::text = 'E'::text THEN ent_nota_fiscal.vlr_pis
                    ELSE ent_nota_fiscal.vlr_pis * '-1'::integer::numeric
                END), 0.000000) AS pis,
            COALESCE(sum(
                CASE
                    WHEN nfs_op_fiscal.entrada_saida::text = 'E'::text THEN ent_nota_fiscal.valor_cofins
                    ELSE ent_nota_fiscal.valor_cofins * '-1'::integer::numeric
                END), 0.000000) AS cofins,
            COALESCE(sum(
                CASE
                    WHEN nfs_op_fiscal.entrada_saida::text = 'E'::text THEN ent_nota_fiscal.vlr_icm_subst
                    ELSE ent_nota_fiscal.vlr_icm_subst * '-1'::integer::numeric
                END), 0.000000) AS icms_subst,
            COALESCE(sum(
                CASE
                    WHEN nfs_op_fiscal.entrada_saida::text = 'E'::text THEN ent_nota_fiscal.total_imp_import
                    ELSE ent_nota_fiscal.total_imp_import * '-1'::integer::numeric
                END), 0.000000) AS importacao,
            COALESCE(sum(
                CASE
                    WHEN nfs_op_fiscal.entrada_saida::text = 'E'::text THEN 0::numeric
                    ELSE 0::numeric * '-1'::integer::numeric
                END), 0.000000) AS difal
           FROM ( SELECT to_char(CURRENT_DATE - '1 mon'::interval month * generate_series(0, 11)::double precision, 'YYYYmm'::text) AS ano_mes) tabaux
             CROSS JOIN bas_filial
             LEFT JOIN ent_nota_fiscal ON to_char(ent_nota_fiscal.data_entrega::timestamp with time zone, 'YYYYmm'::text)::integer = tabaux.ano_mes::integer AND ent_nota_fiscal.tipo_duplicata = 1 AND ent_nota_fiscal.modelo::text <> '0'::text AND ent_nota_fiscal.filial_nota = bas_filial.codigo
             LEFT JOIN nfs_op_fiscal ON nfs_op_fiscal.cfop = ent_nota_fiscal.cfop
          GROUP BY bas_filial.codigo, tabaux.ano_mes
        UNION ALL
         SELECT bas_filial.codigo AS filial,
            tabaux.ano_mes::integer AS ano_mes,
            '0 - Saldo Mês Anterior'::text AS tipo,
            sum(COALESCE(dashboard_lancamento_tributos.icms, 0.000000)) AS icms,
            sum(COALESCE(dashboard_lancamento_tributos.ipi, 0.000000)) AS ipi,
            sum(COALESCE(dashboard_lancamento_tributos.iss, 0.000000)) AS iss,
            sum(COALESCE(dashboard_lancamento_tributos.pis, 0.000000)) AS pis,
            sum(COALESCE(dashboard_lancamento_tributos.cofins, 0.000000)) AS cofins,
            sum(COALESCE(dashboard_lancamento_tributos.icmsst, 0.000000)) AS icms_subst,
            sum(COALESCE(dashboard_lancamento_tributos.importacao, 0.000000)) AS importacao,
            sum(COALESCE(dashboard_lancamento_tributos.difal, 0.000000)) AS difal
           FROM ( SELECT to_char(CURRENT_DATE - '1 mon'::interval month * generate_series(0, 11)::double precision, 'YYYYmm'::text) AS ano_mes) tabaux
             CROSS JOIN bas_filial
             LEFT JOIN dashboard_lancamento_tributos ON (lpad(dashboard_lancamento_tributos.ano::text, 4, '0'::text) || lpad(dashboard_lancamento_tributos.mes::text, 2, '0'::text)) = tabaux.ano_mes AND dashboard_lancamento_tributos.filial = bas_filial.codigo
          GROUP BY bas_filial.codigo, tabaux.ano_mes) tab_final
  ORDER BY filial, ano_mes, tipo;

ALTER TABLE public.view_dashboard_impostos
    OWNER TO postgres;