-- Detalhe Projeção - SQL DEMONSTRAR TODOS TITULOS FINANCEIROS

 
WITH tmp_titulo_financeiro AS (
   
    -- TITULOS FINANCEIROS

    SELECT   distinct

        se.id_empresa,
        se.codigo AS codigo_empresa,
        se.nome AS nome_empresa,
        p.codigo AS codigo_credor_devedor,
        p.nome AS credor_devedor,
        tf.pagar_receber,
        -- < id_movimento_financeiro
        (CASE 
            WHEN tf.situacao = 11 THEN (
            SELECT mc.id_movimento_financeiro FROM movimento_cheque AS mc -- Situacao 11 = Reapresentado
            WHERE  tf.id_titulo_financeiro =  mc.id_titulo_financeiro 
            AND mc.id_conta_movimento IS NOT NULL ORDER BY mc.id_movimento_cheque DESC LIMIT 1)
                                                
            WHEN tf.situacao = 4 THEN ( -- Situacao 4 = Depositado
            SELECT dp.id_movimento_financeiro FROM movimento_cheque AS mc 
            LEFT OUTER JOIN deposito_cheque AS dp ON (mc.id_deposito_cheque = dp.id_deposito_cheque)
            WHERE  tf.id_titulo_financeiro =  mc.id_titulo_financeiro 
            AND dp.id_conta_financeira IS NOT NULL ORDER BY dp.id_deposito_cheque DESC LIMIT 1)
            
            -- WHEN natureza_titulo IN (106,117) THEN td.id_movimento_liquidacao
        
        ELSE mf.id_movimento_financeiro END)  AS id_movimento_financeiro,
        -- <! id_movimento_financeiro
        tf.id_titulo_financeiro,
        tf.id_titulo_filho,
        tf.id_titulo_pai,
        tf.numero_titulo,
        mf.observacao,
        tf.numero_parcela,
        tf.data_emissao,
        tf.data_vencimento,
        tf.data_liquidacao,
        mf.data_conciliacao,
        tf.valor_principal,
        tf.valor_titulo,
        tf.valor_taxa_administracao,
        tf.tarifa_transacao,
        (tf.acrescimo_financeiro + tf.juros_cobranca + tf.multa_cobranca + tf.valor_taxa_administracao + tf.tarifa_transacao) AS total_acrescimo,
        (tf.desconto_financeiro + tf.desconto_cobranca + tf.retencao_tributaria) AS total_desconto,
        /*-- < valor_recebido
        (CASE
            WHEN tf.situacao = 10 THEN 0
            ELSE
            (CASE 
                WHEN tf.situacao = 3 AND tf.id_titulo_filho IS NULL THEN tf.valor_principal 
                - COALESCE((SELECT SUM(tf2.valor_principal) FROM titulo_financeiro AS tf2 WHERE tf.id_titulo_financeiro = tf2.id_titulo_pai ),0)
            
                WHEN tf.situacao = 3 AND tf.id_titulo_filho IS NOT NULL THEN tf.valor_principal 
                - COALESCE((SELECT SUM(tf2.valor_principal) FROM titulo_financeiro AS tf2 WHERE tf.id_titulo_filho = tf2.id_titulo_pai ),0)
            
                ELSE tf.valor_principal  END
            ) 
        END)*/
        (tf.valor_liquidado
            +(tf.desconto_financeiro + tf.desconto_cobranca + tf.retencao_tributaria)
            - (tf.acrescimo_financeiro + tf.juros_cobranca + tf.multa_cobranca))AS valor_recebido,
        -- <! valor_recebido
        tf.valor_liquidado,
        ovl.titulo AS situacao_titulo,
        tf.natureza_titulo AS natureza_titulo_codigo,
        ovl1.titulo AS natureza_titulo,
        (CASE 
            WHEN tf.situacao IN(4,11)  THEN 1 -- Verifica se 4	DEPOSITADO, 11	REAPRESENTADO OU Adiantamento de Cliente, Pagamento Antecipado com Regime Competencia
            ELSE tmf.entrada_saida END ) AS entrada_saida,
        COALESCE(ef.denominacao, '') AS forma_recebimento,
        -- < denominacao_conta
        (CASE 
            WHEN tf.situacao = 11 THEN (
            SELECT cf2.denominacao FROM movimento_cheque AS mc -- Situação 11 = Reapresentado
            LEFT OUTER JOIN conta_financeira AS cf2 ON (cf2.id_conta_financeira = mc.id_conta_movimento)
            WHERE  tf.id_titulo_financeiro =  mc.id_titulo_financeiro 
            AND mc.id_conta_movimento IS NOT NULL ORDER BY mc.id_movimento_cheque DESC LIMIT 1)
                                                        
            WHEN tf.situacao = 4 THEN ( -- Situcao 4 = Depositado
            SELECT cf2.denominacao FROM movimento_cheque AS mc 
            LEFT OUTER JOIN deposito_cheque AS dp ON (mc.id_deposito_cheque = dp.id_deposito_cheque)
            LEFT OUTER JOIN conta_financeira AS cf2 ON (dp.id_conta_financeira = cf2.id_conta_financeira)
            WHERE  tf.id_titulo_financeiro =  mc.id_titulo_financeiro 
            AND dp.id_conta_financeira IS NOT NULL ORDER BY dp.id_deposito_cheque DESC LIMIT 1)
            /*
            WHEN natureza_titulo IN (106,117) THEN (SELECT cf2.denominacao FROM conta_financeira AS cf2 
            WHERE  tf.id_conta_li =  cf2.id_conta_financeira)
            */
            ELSE cf.denominacao END)  AS denominacao_conta,
        -- <! denominacao_conta
        tdr.denominacao AS denominacao,
        tdr.classificacao_despesa_receita,
        cdr.id_categoria_despesa_receita,
        cdr.denominacao AS denominacao_categoria,
        (SELECT COUNT(*) FROM titulo_financeiro WHERE id_titulo_pai = tf.id_titulo_financeiro) AS total_titulos_filho,
        -- < id_conta_financeira
        (CASE 
            WHEN tf.situacao = 11 THEN (SELECT cf2.id_conta_financeira FROM movimento_cheque AS mc 
            LEFT OUTER JOIN conta_financeira AS cf2 ON (cf2.id_conta_financeira = mc.id_conta_movimento)
            WHERE  tf.id_titulo_financeiro =  mc.id_titulo_financeiro 
            AND mc.id_conta_movimento IS NOT NULL ORDER BY mc.id_movimento_cheque DESC LIMIT 1)
                                                    
            WHEN tf.situacao = 4 THEN (
            SELECT cf2.id_conta_financeira FROM movimento_cheque AS mc 
            LEFT OUTER JOIN deposito_cheque AS dp ON (mc.id_deposito_cheque = dp.id_deposito_cheque)
            LEFT OUTER JOIN conta_financeira AS cf2 ON (dp.id_conta_financeira = cf2.id_conta_financeira)
            WHERE  tf.id_titulo_financeiro =  mc.id_titulo_financeiro 
            AND dp.id_conta_financeira IS NOT NULL ORDER BY dp.id_deposito_cheque DESC LIMIT 1)
        
            -- WHEN natureza_titulo IN (106,117) THEN (tf.id_conta_liquidacao)
        
            ELSE cf.id_conta_financeira END)  AS id_conta_financeira
        -- <! id_conta_financeira
    FROM	titulo_financeiro AS tf 
        LEFT JOIN pessoa AS p ON (tf.id_credor_devedor = p.id_pessoa)
        LEFT JOIN tipo_cobranca AS tc ON (tc.id_tipo_cobranca = tf.id_tipo_cobranca)
        LEFT JOIN sis_empresa AS se ON (se.id_empresa = tf.id_empresa)
        LEFT OUTER JOIN conta_financeira AS cf ON (cf.id_conta_financeira = tf.id_conta_liquidacao)
        LEFT OUTER JOIN movimento_financeiro AS mf ON (mf.id_movimento_financeiro = tf.id_movimento_liquidacao)
        LEFT OUTER JOIN tipo_movimento_financeiro AS tmf ON (tmf.id_tipo_movimento_financeiro = mf.id_tipo_movimento_financeiro)
        LEFT OUTER JOIN especie_financeira AS ef ON (ef.id_especie_financeira = mf.id_especie_financeira)
        LEFT JOIN sis_opcao_lista_valor AS ovl ON (ovl.id = tf.situacao AND ovl.id_lista_valor = 'CEDBEA3BCD98445F98C29C5DAC4B809D')
        LEFT JOIN sis_opcao_lista_valor AS ovl1 ON (ovl1.id = tf.natureza_titulo AND ovl1.id_lista_valor = 'C043EFC0EDD5414995003170978F8878')
        
        LEFT JOIN parcela_despesa_receita AS pdr ON (pdr.id_titulo_financeiro = tf.id_titulo_financeiro)
        LEFT JOIN despesa_receita AS dr ON (dr.id_despesa_receita = pdr.id_despesa_receita)
        LEFT JOIN tipo_despesa_receita AS tdr ON (dr.id_tipo_despesa_receita = tdr.id_tipo_despesa_receita)
        LEFT JOIN categoria_despesa_receita AS cdr ON (cdr.id_categoria_despesa_receita = tdr.id_categoria_despesa_receita)

        
        WHERE (tf.id_empresa = :IdEmpresaNaoObrigatorio OR :IdEmpresaNaoObrigatorio = -1)

        AND tf.tipo_titulo IN(1) AND tf.natureza_titulo NOT IN (115)-- 1 Original, 5 Troca de Titulo, 115 Mutuo a Pagar
        
        AND tf.data_vencimento BETWEEN :DataInicial AND :DataFinal
        

        

    -- <! condicao verificar somente um dia do mes
    ORDER BY  nome_empresa,
        data_emissao,
        natureza_titulo	
    /*
    TIPO_MOVIMENTO_FINANCEIRO
    -entrada_saida
    1 - Entrada
    2 - Saída'

    TITULO_FINANCEIRO
    -pagar_receber   1 - Pagar   2 - Receber

    -situacao
    1 - Aberto 
    2 - Liquidado 
    3 - Liquidado Parcialmente 
    4 - Depositado (Restrito apenas a cheques) 
    5 - Devolvido (Restrito apenas a cheques) 
    6 - Aguardando Aprovação Pagamento 
    7 - Agrupado 
    8 - Desmembrado 
    9 - Rascunho 
    10 - Liquidado Por Troca De Título 
    11 - Reapresentado 
    12 - Reservado 
    13 - Cancelado
    
    -tipo_titulo
    1	ORIGINAL
    2	RESIDUAL - Título restante de uma liquidação parcial
    3	MEMBRAMENTO - Título gerado quando se agrupa outros títulos
    4	DESMEMBRAMENTO - Título gerado quando se negocia/parcela um título
    5	TROCA DE TÍTULO - Título originário de uma troca de título

    IN (100,106,117,214, 215,201,202,203,204,205, 213)) 
    -natureza_titulo
    100 - Sangria
    101 - Compra a Prazo
    106 - Adiantamento de Clientes
    107 - Diferença de Caixa Positiva
    108 - Captação de Recursos
    109 - Despesas
    110 - Adiantamento de Funcionários
    111 - Saldo Credor de Funcionário
    112 - Fatura de Pagamento SAF
    114 - Ticket
    115 - Mútuo a Pagar
    116 - Conhecimento Frete
    117 - Pagamento Antecipado
    201 - Venda a Prazo
    202 - Cartão de Crédito
    203 - Cartão de Débito
    204 - Cheque a Vista
    205 - Cheque Pré-Datado
    206 - Adiantamento a Fornecedores
    207 - Diferença de Caixa Negativa
    208 - Aplicação de Recursos
    209 - Receitas
    210 - Adiantamento a Funcionários
    211 - Saldo Devedor de Funcionário
    212 - Fatura de Recebimento SAF
    213 - Vale Frete
    214 - Lote de Ticket
    215 - Mútuo a Receber
    220 - Vale Motorista
    221 - Fatura Global
    222 - Baixa de Pagamento Antecipado

    */
)


SELECT
  '(+)CONTAS A RECEBER POR DATA DE LIQUIDAÇÃO POR TIPO' AS tipo,
  1110 AS ordenacao,
  '1. RECEITA OPERACIONAL' AS sessao_relatorio,
  'VENDAS' AS subsessao_relatorio,
   codigo_empresa,
  nome_empresa,
  codigo_credor_devedor,
  credor_devedor,
  numero_titulo,
  data_emissao,
  data_vencimento,
  data_liquidacao,
  valor_principal,
  0 AS valor_taxa_administracao,
  0 AS tarifa_transacao,
  0 AS total_desconto,
  0 AS total_acrescimo,
  valor_recebido,
  natureza_titulo,
  entrada_saida,
  denominacao_conta,
  observacao,
  data_conciliacao
  
  FROM tmp_titulo_financeiro where natureza_titulo_codigo IN (100,106,117,214, 215,201,202,203,204,205, 213, 221)
AND (pagar_receber = 2 OR (natureza_titulo_codigo IN (106,117) AND pagar_receber = 1)) --pagar_receber, 1 - Pagar, 2 - Receber, 1 


UNION ALL 

SELECT DISTINCT
  '(+)CONTAS A RECEBER POR DATA DE LIQUIDAÇÃO POR TIPO' AS tipo,
  1210 AS ordenacao,
  denominacao_categoria AS sessao_relatorio,
  denominacao AS subsessao_relatorio,
   codigo_empresa,
  nome_empresa,
  codigo_credor_devedor,
  credor_devedor,
  numero_titulo,
  data_emissao,
  data_vencimento,
  data_liquidacao,
  valor_principal,
  0 AS valor_taxa_administracao,
  0 AS tarifa_transacao,
  0 AS total_desconto,
  0 AS total_acrescimo,
  valor_recebido,
  natureza_titulo,
  entrada_saida,
  denominacao_conta,
  observacao,
  data_conciliacao
  
  FROM tmp_titulo_financeiro where natureza_titulo_codigo IN (108,209)  -- 108 - Captação de Recursos, 209 - Receitas
  AND pagar_receber = 2 -- 1 
 --pagar_receber, 1 - Pagar, 2 - Receber


UNION ALL 

SELECT
  '(+)CONTAS A RECEBER POR DATA DE LIQUIDAÇÃO POR TIPO' AS tipo,
  1110 AS ordenacao,
  '1. RECEITA OPERACIONAL' AS sessao_relatorio,
  'DESCONTOS NAS COMPRAS' AS subsessao_relatorio,
  codigo_empresa,
  nome_empresa,
  codigo_credor_devedor,
  credor_devedor,
  numero_titulo,
  data_emissao,
  data_vencimento,
  data_liquidacao,
  valor_principal,
  0 AS valor_taxa_administracao,
  0 AS tarifa_transacao,
  total_desconto,
  0 AS total_acrescimo,
  0 AS valor_recebido,
  natureza_titulo,
  entrada_saida,
  denominacao_conta,
  observacao,
  data_conciliacao
  
  FROM tmp_titulo_financeiro where (entrada_saida = 2 AND natureza_titulo_codigo IN (101, 109, 115, 116))    -- 109 - Despesas, 115 - Mútuo a Pagar, 116 - Conhecimento Frete
AND total_desconto > 0

UNION ALL 

SELECT
  '(+)CONTAS A RECEBER POR DATA DE LIQUIDAÇÃO POR TIPO' AS tipo,
  1310 AS ordenacao,
  '4. RECEITAS FINANCEIRAS' AS sessao_relatorio,
  'ACRESCIMO/JUROS/MULTA' AS subsessao_relatorio,
  codigo_empresa,
  nome_empresa,
  codigo_credor_devedor,
  credor_devedor,
  numero_titulo,
  data_emissao,
  data_vencimento,
  data_liquidacao,
  valor_principal,
  0 AS valor_taxa_administracao,
  0 AS tarifa_transacao,
  0 AS total_desconto,
  total_acrescimo,
  0 AS valor_recebido,
  natureza_titulo,
  entrada_saida,
  denominacao_conta,
  observacao,
  data_conciliacao
  
  FROM tmp_titulo_financeiro where (entrada_saida = 1 AND natureza_titulo_codigo IN (100,106,117,214, 215,201,202,203,204,205, 213,221))    -- 109 - Despesas, 115 - Mútuo a Pagar, 116 - Conhecimento Frete
AND total_acrescimo > 0

UNION ALL 

SELECT
  '(-)CONTAS A PAGAR POR DATA DE LIQUIDAÇÃO POR TIPO' AS tipo,
  3110 AS ordenacao,
  '2. CUSTO DIRETO' AS sessao_relatorio,
  COALESCE(ci.denominacao, 'TITULOS A PAGAR MANUAL') AS subsessao_relatorio,
   
  codigo_empresa,
  nome_empresa,
  codigo_credor_devedor,
  credor_devedor,
  numero_titulo,
  data_emissao,
  tf.data_vencimento,
  data_liquidacao,
   COALESCE(CASE WHEN nfe.total_nota = 0 THEN 0 ELSE ((infe.custo_bruto - (CASE WHEN nfe.frete = 0 THEN  infe.rateio_frete ELSE 0 END)) * tf.valor_recebido / nfe.total_nota) END, tf.valor_recebido) AS valor_principal,
  0 AS valor_taxa_administracao,
  0 AS tarifa_transacao,
  0 AS total_desconto,
  0 AS total_acrescimo,
  COALESCE(CASE WHEN nfe.total_nota = 0 THEN 0 ELSE ((infe.custo_bruto - (CASE WHEN nfe.frete = 0 THEN  infe.rateio_frete ELSE 0 END)) * tf.valor_recebido / nfe.total_nota) END, tf.valor_recebido) AS valor_recebido,
  natureza_titulo,
  entrada_saida,
  denominacao_conta,
  tf.observacao,
  data_conciliacao
  
  FROM tmp_titulo_financeiro AS tf
LEFT JOIN parcela_nfe AS pnfe ON (tf.id_titulo_financeiro = pnfe.id_titulo_financeiro)
LEFT JOIN nota_fiscal_entrada AS nfe ON (pnfe.id_nota_fiscal_entrada = nfe.id_nota_fiscal_entrada)
LEFT JOIN item_nfe AS infe ON (infe.id_nota_fiscal_entrada = nfe.id_nota_fiscal_entrada)
LEFT JOIN item AS it ON (it.id_item = infe.id_item)
LEFT JOIN categoria_item AS ci ON (ci.id_categoria_item = it.id_categoria_item)
LEFT JOIN parcela_despesa_receita AS pdr ON (pdr.id_titulo_financeiro = tf.id_titulo_financeiro)
WHERE  natureza_titulo_codigo IN (101)  -- 101 - Compra a Prazo
AND pagar_receber = 1 -- pagar_receber, 1 - Pagar, 2 - Receber, entrada e conta_financeira ou regime competencia


UNION ALL 

SELECT
  '(-)CONTAS A PAGAR POR DATA DE LIQUIDAÇÃO POR TIPO' AS tipo,
  3120 AS ordenacao,
  '3. CUSTO VARIAVEL' AS sessao_relatorio,
  'TAXA ADMINISTRATIVA' AS subsessao_relatorio,
    codigo_empresa,
  nome_empresa,
  codigo_credor_devedor,
  credor_devedor,
  numero_titulo,
  data_emissao,
  data_vencimento,
  data_liquidacao,
  0 AS valor_principal,
  valor_taxa_administracao,
  0 AS tarifa_transacao,
  0 AS total_desconto,
  0 AS total_acrescimo,
  0 AS valor_recebido,
  natureza_titulo,
  entrada_saida,
  denominacao_conta,
  observacao,
  data_conciliacao
  
  FROM tmp_titulo_financeiro where (entrada_saida = 1 AND natureza_titulo_codigo IN (100,106,117,214, 215,201,202,203,204,205, 213,221,106,117))    -- 109 - Despesas, 115 - Mútuo a Pagar, 116 - Conhecimento Frete
AND valor_taxa_administracao > 0 


UNION ALL 

SELECT
  '(-)CONTAS A PAGAR POR DATA DE LIQUIDAÇÃO POR TIPO' AS tipo,
  3120 AS ordenacao,
  '3. CUSTO VARIAVEL' AS sessao_relatorio,
  'TARIFA TRANSAÇÃO' AS subsessao_relatorio,
  codigo_empresa,
  nome_empresa,
  codigo_credor_devedor,
  credor_devedor,
  numero_titulo,
  data_emissao,
  data_vencimento,
  data_liquidacao,
  0 AS valor_principal,
  0 AS valor_taxa_administracao,
  tarifa_transacao,
  0 AS total_desconto,
  0 AS total_acrescimo,
  0 AS valor_recebido,
  natureza_titulo,
  entrada_saida,
  denominacao_conta,
  observacao,
  data_conciliacao
  
  FROM tmp_titulo_financeiro where (entrada_saida = 1 AND natureza_titulo_codigo IN (100,106,117,214, 215,201,202,203,204,205, 213,221,106,117))    -- 109 - Despesas, 115 - Mútuo a Pagar, 116 - Conhecimento Frete
AND tarifa_transacao > 0 


UNION ALL 

SELECT
  '(-)CONTAS A PAGAR POR DATA DE LIQUIDAÇÃO POR TIPO' AS tipo,
  3110 AS ordenacao,
  '2. CUSTO DIRETO' AS sessao_relatorio,
  'DESCONTOS CONCEDIDOS' AS subsessao_relatorio,
   codigo_empresa,
  nome_empresa,
  codigo_credor_devedor,
  credor_devedor,
  numero_titulo,
  data_emissao,
  data_vencimento,
  data_liquidacao,
  valor_principal,
  0 AS valor_taxa_administracao,
  0 AS tarifa_transacao,
  total_desconto,
  0 AS total_acrescimo,
  0 AS valor_recebido,
  natureza_titulo,
  entrada_saida,
  denominacao_conta,
  observacao,
  data_conciliacao
  
  FROM tmp_titulo_financeiro where (entrada_saida = 1 AND natureza_titulo_codigo IN (100,106,117,214, 215,201,202,203,204,205, 213,221,106,117))    
AND total_desconto > 0 


UNION ALL 

SELECT
  '(-)CONTAS A PAGAR POR DATA DE LIQUIDAÇÃO POR TIPO' AS tipo,
  3110 AS ordenacao,
  '2. CUSTO DIRETO' AS sessao_relatorio,
  '*Atraso de Fornecedores' AS subsessao_relatorio,
  codigo_empresa,
  nome_empresa,
  codigo_credor_devedor,
  credor_devedor,
  numero_titulo,
  data_emissao,
  data_vencimento,
  data_liquidacao,
  valor_principal,
  0 AS valor_taxa_administracao,
  0 AS tarifa_transacao,
  0 AS total_desconto,
  total_acrescimo,
  0 AS valor_recebido,
  natureza_titulo,
  entrada_saida,
  denominacao_conta,
  observacao,
  data_conciliacao
  
FROM tmp_titulo_financeiro where (entrada_saida = 2 AND natureza_titulo_codigo IN (101, 109, 115, 116,106,117))    -- 109 - Despesas, 115 - Mútuo a Pagar, 116 - Conhecimento Frete
AND total_acrescimo > 0 


UNION ALL 

SELECT
  '(-)CONTAS A PAGAR POR DATA DE LIQUIDAÇÃO POR TIPO' AS tipo,
  3120 AS ordenacao,
  COALESCE(denominacao_categoria,'** OUTRAS SAIDAS')  AS sessao_relatorio,
  COALESCE(denominacao,'TITULOS A PAGAR MANUAL') AS subsessao_relatorio,
  codigo_empresa,
  nome_empresa,
  codigo_credor_devedor,
  credor_devedor,
  numero_titulo,
  data_emissao,
  data_vencimento,
  data_liquidacao,
  valor_principal,
  valor_taxa_administracao,
  tarifa_transacao,
  total_desconto,
  total_acrescimo,
  valor_recebido,
  natureza_titulo,
  entrada_saida,
  denominacao_conta,
  observacao,
  data_conciliacao
  
  FROM tmp_titulo_financeiro where  natureza_titulo_codigo IN (109, 115, 116, 106,117)    -- 109 - Despesas, 115 - Mútuo a Pagar, 116 - Conhecimento Frete
    AND pagar_receber = 1 --pagar_receber, 1 - Pagar, 2 - Receber



ORDER BY  tipo DESC, sessao_relatorio, data_vencimento, natureza_titulo, numero_titulo ASC
