-- View: public.view_dashboard_impostos

-- DROP VIEW public.view_dashboard_impostos;

CREATE OR REPLACE VIEW public.view_dashboard_impostos AS



 SELECT ano_mes,
    tipo,
    icms,
    ipi,
    iss,
    pis,
    cofins,
    icms_subst,
    importacao,
	difal
   FROM ( SELECT 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 (valor_total_icms_destino + valor_total_icms_origem) * '-1'::integer::numeric
                    ELSE (valor_total_icms_destino + 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
             LEFT JOIN nfs_nota_fiscal ON to_char(nfs_nota_fiscal.data_emissao::timestamp with time zone, 'YYYYmm'::text)::integer = tabaux.ano_mes::integer
             LEFT JOIN nfs_op_fiscal ON nfs_op_fiscal.cfop = nfs_nota_fiscal.cfop
          WHERE nfs_nota_fiscal.cancelada::text = 'N'::text
          GROUP BY tabaux.ano_mes
        UNION ALL
         SELECT 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
                    ELSE (0) * '-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
             LEFT JOIN ent_nota_fiscal ON to_char(ent_nota_fiscal.data_entrega::timestamp with time zone, 'YYYYmm'::text)::integer = tabaux.ano_mes::integer
             LEFT JOIN nfs_op_fiscal ON nfs_op_fiscal.cfop = ent_nota_fiscal.cfop
          GROUP BY tabaux.ano_mes) tab_final
  ORDER BY ano_mes, tipo;

ALTER TABLE public.view_dashboard_impostos    OWNER TO postgres;