Querys Dashboard

-- QUERY RESUMO COMPRAS --
SELECT
    CAST(COUNT(1) AS INTEGER) as qtdCompras
FROM
    venda_cf AS v
    INNER JOIN item_venda_cf AS iv ON (iv.id_venda_cf = v.id_venda_cf)
    INNER JOIN movimento_venda_terminal AS mvt ON (
        mvt.id_movimento_venda_terminal = v.id_movimento_venda_terminal
    )
WHERE
    v.id_cliente = CAST(16692656 AS BIGINT)
    AND v.cancelada = 'N'
    AND iv.cancelado = 'N'
    AND DATE_PART('MONTH', CAST(v.data_cupom AS DATE)) = DATE_PART('MONTH', current_date)
    AND DATE_PART('YEAR', CAST(v.data_cupom as date)) = DATE_PART('YEAR', current_date)
    AND mvt.id_empresa IN (
        SELECT
            id_empresa
        FROM
            sis_empresa se
        WHERE
            se.guid_empresa IN (
                '428FE6893116415A9E8492A4E4389FDC',
                '58F6191039094B118F8FA138D3571059',
                'A17799B432C14F04AAB0278CEE9F3AB1',
                'AE167ECCFDBF4434A7688C9B5EFFE988',
                'B532F21B0B9D41B29EAE1FE0B223A805',
                'F2CA336B72CC45C9B7A504374B44FA77',
                'F75A07CBC0354BCF8E88411CF5F1B0D0'
            )
    );

-- QUERY MOVIMENTAÇÃO NOS 13 MESES
SELECT
    COALESCE(sum(total_bruto), 0)
FROM
    venda_cf AS v
    INNER JOIN item_venda_cf AS iv ON (iv.id_venda_cf = v.id_venda_cf)
    INNER JOIN item AS i ON (i.id_item = iv.id_item)
    INNER JOIN movimento_venda_terminal AS mv ON (
        v.id_movimento_venda_terminal = mv.id_movimento_venda_terminal
    )
WHERE
    v.id_cliente = CAST(16692656 AS BIGINT)
    AND v.cancelada = 'N'
    AND iv.cancelado = 'N'
    AND mv.id_empresa IN (
        SELECT
            id_empresa
        FROM
            sis_empresa se
        WHERE
            se.guid_empresa IN (
                '428FE6893116415A9E8492A4E4389FDC',
                '58F6191039094B118F8FA138D3571059',
                'A17799B432C14F04AAB0278CEE9F3AB1',
                'AE167ECCFDBF4434A7688C9B5EFFE988',
                'B532F21B0B9D41B29EAE1FE0B223A805',
                'F2CA336B72CC45C9B7A504374B44FA77',
                'F75A07CBC0354BCF8E88411CF5F1B0D0'
            )
    )
    AND DATE_PART('MONTH', CAST(v.data_cupom AS DATE)) = DATE_PART('MONTH', CAST('2019-01-30' AS DATE))
    AND DATE_PART('YEAR', CAST(v.data_cupom as date)) = DATE_PART('YEAR', CAST('2019-01-30' AS DATE));

SELECT
    COALESCE(sum(total_bruto), 0)
FROM
    venda_cf AS v
    INNER JOIN item_venda_cf AS iv ON (iv.id_venda_cf = v.id_venda_cf)
    INNER JOIN item AS i ON (i.id_item = iv.id_item)
    INNER JOIN movimento_venda_terminal AS mv ON (
        v.id_movimento_venda_terminal = mv.id_movimento_venda_terminal
    )
WHERE
    v.id_cliente = CAST(16692656 AS BIGINT)
    AND v.cancelada = 'N'
    AND iv.cancelado = 'N'
    AND mv.id_empresa IN (
        SELECT
            id_empresa
        FROM
            sis_empresa se
        WHERE
            se.guid_empresa IN (
                '428FE6893116415A9E8492A4E4389FDC',
                '58F6191039094B118F8FA138D3571059',
                'A17799B432C14F04AAB0278CEE9F3AB1',
                'AE167ECCFDBF4434A7688C9B5EFFE988',
                'B532F21B0B9D41B29EAE1FE0B223A805',
                'F2CA336B72CC45C9B7A504374B44FA77',
                'F75A07CBC0354BCF8E88411CF5F1B0D0'
            )
    )
    AND DATE_PART('MONTH', CAST(v.data_cupom AS DATE)) = DATE_PART('MONTH', CAST('2019-02-28' AS DATE))
    AND DATE_PART('YEAR', CAST(v.data_cupom as date)) = DATE_PART('YEAR', CAST('2019-02-28' AS DATE));

SELECT
    COALESCE(sum(total_bruto), 0)
FROM
    venda_cf AS v
    INNER JOIN item_venda_cf AS iv ON (iv.id_venda_cf = v.id_venda_cf)
    INNER JOIN item AS i ON (i.id_item = iv.id_item)
    INNER JOIN movimento_venda_terminal AS mv ON (
        v.id_movimento_venda_terminal = mv.id_movimento_venda_terminal
    )
WHERE
    v.id_cliente = CAST(16692656 AS BIGINT)
    AND v.cancelada = 'N'
    AND iv.cancelado = 'N'
    AND mv.id_empresa IN (
        SELECT
            id_empresa
        FROM
            sis_empresa se
        WHERE
            se.guid_empresa IN (
                '428FE6893116415A9E8492A4E4389FDC',
                '58F6191039094B118F8FA138D3571059',
                'A17799B432C14F04AAB0278CEE9F3AB1',
                'AE167ECCFDBF4434A7688C9B5EFFE988',
                'B532F21B0B9D41B29EAE1FE0B223A805',
                'F2CA336B72CC45C9B7A504374B44FA77',
                'F75A07CBC0354BCF8E88411CF5F1B0D0'
            )
    )
    AND DATE_PART('MONTH', CAST(v.data_cupom AS DATE)) = DATE_PART('MONTH', CAST('2019-03-28' AS DATE))
    AND DATE_PART('YEAR', CAST(v.data_cupom as date)) = DATE_PART('YEAR', CAST('2019-03-28' AS DATE));

SELECT
    COALESCE(sum(total_bruto), 0)
FROM
    venda_cf AS v
    INNER JOIN item_venda_cf AS iv ON (iv.id_venda_cf = v.id_venda_cf)
    INNER JOIN item AS i ON (i.id_item = iv.id_item)
    INNER JOIN movimento_venda_terminal AS mv ON (
        v.id_movimento_venda_terminal = mv.id_movimento_venda_terminal
    )
WHERE
    v.id_cliente = CAST(16692656 AS BIGINT)
    AND v.cancelada = 'N'
    AND iv.cancelado = 'N'
    AND mv.id_empresa IN (
        SELECT
            id_empresa
        FROM
            sis_empresa se
        WHERE
            se.guid_empresa IN (
                '428FE6893116415A9E8492A4E4389FDC',
                '58F6191039094B118F8FA138D3571059',
                'A17799B432C14F04AAB0278CEE9F3AB1',
                'AE167ECCFDBF4434A7688C9B5EFFE988',
                'B532F21B0B9D41B29EAE1FE0B223A805',
                'F2CA336B72CC45C9B7A504374B44FA77',
                'F75A07CBC0354BCF8E88411CF5F1B0D0'
            )
    )
    AND DATE_PART('MONTH', CAST(v.data_cupom AS DATE)) = DATE_PART('MONTH', CAST('2019-04-28' AS DATE))
    AND DATE_PART('YEAR', CAST(v.data_cupom as date)) = DATE_PART('YEAR', CAST('2019-04-28' AS DATE));

SELECT
    COALESCE(sum(total_bruto), 0)
FROM
    venda_cf AS v
    INNER JOIN item_venda_cf AS iv ON (iv.id_venda_cf = v.id_venda_cf)
    INNER JOIN item AS i ON (i.id_item = iv.id_item)
    INNER JOIN movimento_venda_terminal AS mv ON (
        v.id_movimento_venda_terminal = mv.id_movimento_venda_terminal
    )
WHERE
    v.id_cliente = CAST(16692656 AS BIGINT)
    AND v.cancelada = 'N'
    AND iv.cancelado = 'N'
    AND mv.id_empresa IN (
        SELECT
            id_empresa
        FROM
            sis_empresa se
        WHERE
            se.guid_empresa IN (
                '428FE6893116415A9E8492A4E4389FDC',
                '58F6191039094B118F8FA138D3571059',
                'A17799B432C14F04AAB0278CEE9F3AB1',
                'AE167ECCFDBF4434A7688C9B5EFFE988',
                'B532F21B0B9D41B29EAE1FE0B223A805',
                'F2CA336B72CC45C9B7A504374B44FA77',
                'F75A07CBC0354BCF8E88411CF5F1B0D0'
            )
    )
    AND DATE_PART('MONTH', CAST(v.data_cupom AS DATE)) = DATE_PART('MONTH', CAST('2019-05-28' AS DATE))
    AND DATE_PART('YEAR', CAST(v.data_cupom as date)) = DATE_PART('YEAR', CAST('2019-05-28' AS DATE));

SELECT
    COALESCE(sum(total_bruto), 0)
FROM
    venda_cf AS v
    INNER JOIN item_venda_cf AS iv ON (iv.id_venda_cf = v.id_venda_cf)
    INNER JOIN item AS i ON (i.id_item = iv.id_item)
    INNER JOIN movimento_venda_terminal AS mv ON (
        v.id_movimento_venda_terminal = mv.id_movimento_venda_terminal
    )
WHERE
    v.id_cliente = CAST(16692656 AS BIGINT)
    AND v.cancelada = 'N'
    AND iv.cancelado = 'N'
    AND mv.id_empresa IN (
        SELECT
            id_empresa
        FROM
            sis_empresa se
        WHERE
            se.guid_empresa IN (
                '428FE6893116415A9E8492A4E4389FDC',
                '58F6191039094B118F8FA138D3571059',
                'A17799B432C14F04AAB0278CEE9F3AB1',
                'AE167ECCFDBF4434A7688C9B5EFFE988',
                'B532F21B0B9D41B29EAE1FE0B223A805',
                'F2CA336B72CC45C9B7A504374B44FA77',
                'F75A07CBC0354BCF8E88411CF5F1B0D0'
            )
    )
    AND DATE_PART('MONTH', CAST(v.data_cupom AS DATE)) = DATE_PART('MONTH', CAST('2019-06-28' AS DATE))
    AND DATE_PART('YEAR', CAST(v.data_cupom as date)) = DATE_PART('YEAR', CAST('2019-06-28' AS DATE));

SELECT
    COALESCE(sum(total_bruto), 0)
FROM
    venda_cf AS v
    INNER JOIN item_venda_cf AS iv ON (iv.id_venda_cf = v.id_venda_cf)
    INNER JOIN item AS i ON (i.id_item = iv.id_item)
    INNER JOIN movimento_venda_terminal AS mv ON (
        v.id_movimento_venda_terminal = mv.id_movimento_venda_terminal
    )
WHERE
    v.id_cliente = CAST(16692656 AS BIGINT)
    AND v.cancelada = 'N'
    AND iv.cancelado = 'N'
    AND mv.id_empresa IN (
        SELECT
            id_empresa
        FROM
            sis_empresa se
        WHERE
            se.guid_empresa IN (
                '428FE6893116415A9E8492A4E4389FDC',
                '58F6191039094B118F8FA138D3571059',
                'A17799B432C14F04AAB0278CEE9F3AB1',
                'AE167ECCFDBF4434A7688C9B5EFFE988',
                'B532F21B0B9D41B29EAE1FE0B223A805',
                'F2CA336B72CC45C9B7A504374B44FA77',
                'F75A07CBC0354BCF8E88411CF5F1B0D0'
            )
    )
    AND DATE_PART('MONTH', CAST(v.data_cupom AS DATE)) = DATE_PART('MONTH', CAST('2019-07-28' AS DATE))
    AND DATE_PART('YEAR', CAST(v.data_cupom as date)) = DATE_PART('YEAR', CAST('2019-07-28' AS DATE));

SELECT
    COALESCE(sum(total_bruto), 0)
FROM
    venda_cf AS v
    INNER JOIN item_venda_cf AS iv ON (iv.id_venda_cf = v.id_venda_cf)
    INNER JOIN item AS i ON (i.id_item = iv.id_item)
    INNER JOIN movimento_venda_terminal AS mv ON (
        v.id_movimento_venda_terminal = mv.id_movimento_venda_terminal
    )
WHERE
    v.id_cliente = CAST(16692656 AS BIGINT)
    AND v.cancelada = 'N'
    AND iv.cancelado = 'N'
    AND mv.id_empresa IN (
        SELECT
            id_empresa
        FROM
            sis_empresa se
        WHERE
            se.guid_empresa IN (
                '428FE6893116415A9E8492A4E4389FDC',
                '58F6191039094B118F8FA138D3571059',
                'A17799B432C14F04AAB0278CEE9F3AB1',
                'AE167ECCFDBF4434A7688C9B5EFFE988',
                'B532F21B0B9D41B29EAE1FE0B223A805',
                'F2CA336B72CC45C9B7A504374B44FA77',
                'F75A07CBC0354BCF8E88411CF5F1B0D0'
            )
    )
    AND DATE_PART('MONTH', CAST(v.data_cupom AS DATE)) = DATE_PART('MONTH', CAST('2019-08-28' AS DATE))
    AND DATE_PART('YEAR', CAST(v.data_cupom as date)) = DATE_PART('YEAR', CAST('2019-08-28' AS DATE));

SELECT
    COALESCE(sum(total_bruto), 0)
FROM
    venda_cf AS v
    INNER JOIN item_venda_cf AS iv ON (iv.id_venda_cf = v.id_venda_cf)
    INNER JOIN item AS i ON (i.id_item = iv.id_item)
    INNER JOIN movimento_venda_terminal AS mv ON (
        v.id_movimento_venda_terminal = mv.id_movimento_venda_terminal
    )
WHERE
    v.id_cliente = CAST(16692656 AS BIGINT)
    AND v.cancelada = 'N'
    AND iv.cancelado = 'N'
    AND mv.id_empresa IN (
        SELECT
            id_empresa
        FROM
            sis_empresa se
        WHERE
            se.guid_empresa IN (
                '428FE6893116415A9E8492A4E4389FDC',
                '58F6191039094B118F8FA138D3571059',
                'A17799B432C14F04AAB0278CEE9F3AB1',
                'AE167ECCFDBF4434A7688C9B5EFFE988',
                'B532F21B0B9D41B29EAE1FE0B223A805',
                'F2CA336B72CC45C9B7A504374B44FA77',
                'F75A07CBC0354BCF8E88411CF5F1B0D0'
            )
    )
    AND DATE_PART('MONTH', CAST(v.data_cupom AS DATE)) = DATE_PART('MONTH', CAST('2019-09-28' AS DATE))
    AND DATE_PART('YEAR', CAST(v.data_cupom as date)) = DATE_PART('YEAR', CAST('2019-09-28' AS DATE));

SELECT
    COALESCE(sum(total_bruto), 0)
FROM
    venda_cf AS v
    INNER JOIN item_venda_cf AS iv ON (iv.id_venda_cf = v.id_venda_cf)
    INNER JOIN item AS i ON (i.id_item = iv.id_item)
    INNER JOIN movimento_venda_terminal AS mv ON (
        v.id_movimento_venda_terminal = mv.id_movimento_venda_terminal
    )
WHERE
    v.id_cliente = CAST(16692656 AS BIGINT)
    AND v.cancelada = 'N'
    AND iv.cancelado = 'N'
    AND mv.id_empresa IN (
        SELECT
            id_empresa
        FROM
            sis_empresa se
        WHERE
            se.guid_empresa IN (
                '428FE6893116415A9E8492A4E4389FDC',
                '58F6191039094B118F8FA138D3571059',
                'A17799B432C14F04AAB0278CEE9F3AB1',
                'AE167ECCFDBF4434A7688C9B5EFFE988',
                'B532F21B0B9D41B29EAE1FE0B223A805',
                'F2CA336B72CC45C9B7A504374B44FA77',
                'F75A07CBC0354BCF8E88411CF5F1B0D0'
            )
    )
    AND DATE_PART('MONTH', CAST(v.data_cupom AS DATE)) = DATE_PART('MONTH', CAST('2019-10-28' AS DATE))
    AND DATE_PART('YEAR', CAST(v.data_cupom as date)) = DATE_PART('YEAR', CAST('2019-10-28' AS DATE));

SELECT
    COALESCE(sum(total_bruto), 0)
FROM
    venda_cf AS v
    INNER JOIN item_venda_cf AS iv ON (iv.id_venda_cf = v.id_venda_cf)
    INNER JOIN item AS i ON (i.id_item = iv.id_item)
    INNER JOIN movimento_venda_terminal AS mv ON (
        v.id_movimento_venda_terminal = mv.id_movimento_venda_terminal
    )
WHERE
    v.id_cliente = CAST(16692656 AS BIGINT)
    AND v.cancelada = 'N'
    AND iv.cancelado = 'N'
    AND mv.id_empresa IN (
        SELECT
            id_empresa
        FROM
            sis_empresa se
        WHERE
            se.guid_empresa IN (
                '428FE6893116415A9E8492A4E4389FDC',
                '58F6191039094B118F8FA138D3571059',
                'A17799B432C14F04AAB0278CEE9F3AB1',
                'AE167ECCFDBF4434A7688C9B5EFFE988',
                'B532F21B0B9D41B29EAE1FE0B223A805',
                'F2CA336B72CC45C9B7A504374B44FA77',
                'F75A07CBC0354BCF8E88411CF5F1B0D0'
            )
    )
    AND DATE_PART('MONTH', CAST(v.data_cupom AS DATE)) = DATE_PART('MONTH', CAST('2019-11-28' AS DATE))
    AND DATE_PART('YEAR', CAST(v.data_cupom as date)) = DATE_PART('YEAR', CAST('2019-11-28' AS DATE));

SELECT
    COALESCE(sum(total_bruto), 0)
FROM
    venda_cf AS v
    INNER JOIN item_venda_cf AS iv ON (iv.id_venda_cf = v.id_venda_cf)
    INNER JOIN item AS i ON (i.id_item = iv.id_item)
    INNER JOIN movimento_venda_terminal AS mv ON (
        v.id_movimento_venda_terminal = mv.id_movimento_venda_terminal
    )
WHERE
    v.id_cliente = CAST(16692656 AS BIGINT)
    AND v.cancelada = 'N'
    AND iv.cancelado = 'N'
    AND mv.id_empresa IN (
        SELECT
            id_empresa
        FROM
            sis_empresa se
        WHERE
            se.guid_empresa IN (
                '428FE6893116415A9E8492A4E4389FDC',
                '58F6191039094B118F8FA138D3571059',
                'A17799B432C14F04AAB0278CEE9F3AB1',
                'AE167ECCFDBF4434A7688C9B5EFFE988',
                'B532F21B0B9D41B29EAE1FE0B223A805',
                'F2CA336B72CC45C9B7A504374B44FA77',
                'F75A07CBC0354BCF8E88411CF5F1B0D0'
            )
    )
    AND DATE_PART('MONTH', CAST(v.data_cupom AS DATE)) = DATE_PART('MONTH', CAST('2019-12-28' AS DATE))
    AND DATE_PART('YEAR', CAST(v.data_cupom as date)) = DATE_PART('YEAR', CAST('2019-12-28' AS DATE));

SELECT
    COALESCE(sum(total_bruto), 0)
FROM
    venda_cf AS v
    INNER JOIN item_venda_cf AS iv ON (iv.id_venda_cf = v.id_venda_cf)
    INNER JOIN item AS i ON (i.id_item = iv.id_item)
    INNER JOIN movimento_venda_terminal AS mv ON (
        v.id_movimento_venda_terminal = mv.id_movimento_venda_terminal
    )
WHERE
    v.id_cliente = CAST(16692656 AS BIGINT)
    AND v.cancelada = 'N'
    AND iv.cancelado = 'N'
    AND mv.id_empresa IN (
        SELECT
            id_empresa
        FROM
            sis_empresa se
        WHERE
            se.guid_empresa IN (
                '428FE6893116415A9E8492A4E4389FDC',
                '58F6191039094B118F8FA138D3571059',
                'A17799B432C14F04AAB0278CEE9F3AB1',
                'AE167ECCFDBF4434A7688C9B5EFFE988',
                'B532F21B0B9D41B29EAE1FE0B223A805',
                'F2CA336B72CC45C9B7A504374B44FA77',
                'F75A07CBC0354BCF8E88411CF5F1B0D0'
            )
    )
    AND DATE_PART('MONTH', CAST(v.data_cupom AS DATE)) = DATE_PART('MONTH', CAST('2020-01-28' AS DATE))
    AND DATE_PART('YEAR', CAST(v.data_cupom as date)) = DATE_PART('YEAR', CAST('2020-01-28' AS DATE));

--QUERY ULTIMOS LANÇAMENTOS
with tmp_venda AS (
    SELECT
        *
    FROM
        venda_cf
    WHERE
        id_cliente = CAST(16692656 AS BIGINT)
        AND cancelada = 'N'
    ORDER BY
        data_cupom DESC
    limit
        15
), tmp_item_venda AS (
    SELECT
        *
    FROM
        item_venda_cf
    WHERE
        EXISTS(
            SELECT
                1
            FROM
                tmp_venda
            WHERE
                id_venda_cf = item_venda_cf.id_venda_cf
        )
        AND cancelado = 'N'
)
SELECT
    iv.id_venda_cf,
    v.data_cupom,
    i.denominacao,
    iv.quantidade,
    iv.preco,
    iv.total_bruto,
    COALESCE(
        (
            iv.desconto + iv.desconto_automatico + iv.desconto_rateado + iv.desconto_ajuste_rateado + iv.desconto_automatico_rateado + iv.desconto_fidelidade_rateado + iv.desconto_financeiro
        ),
        0
    ) AS descontos,
    COALESCE(
        (
            iv.acrescimo + iv.acrescimo_automatico + iv.acrescimo_rateado + iv.acrescimo_ajuste_rateado + iv.acrescimo_automatico_rateado + iv.acrescimo_financeiro
        ),
        0
    ) AS acrescimos,
    iv.total_item AS total_liquido,
    e.nome AS nomeEmpresa,
    v.odometro,
    v.quilometragem,
    ROUND(
        COALESCE(
            v.quilometragem / COALESCE(
                (
                    SELECT
                        SUM(quantidade)
                    FROM
                        tmp_item_venda
                    WHERE
                        id_venda_cf = v.id_venda_cf
                        AND id_abastecimento IS NOT NULL
                ),
                1
            ),
            0
        ),
        3
    ) AS media,
    mc.id_motorista_cliente,
    mc.nome AS nomeMotorista,
    vc.id_veiculo_cliente,
    vc.placa,
    vc.frota
FROM
    tmp_venda AS v
    INNER JOIN tmp_item_venda AS iv ON (iv.id_venda_cf = v.id_venda_cf)
    INNER JOIN item AS i ON (i.id_item = iv.id_item)
    INNER JOIN movimento_venda_terminal AS mvt ON (
        mvt.id_movimento_venda_terminal = v.id_movimento_venda_terminal
    )
    INNER JOIN sis_empresa AS e ON (e.id_empresa = mvt.id_empresa)
    LEFT OUTER JOIN motorista_cliente AS mc ON (mc.id_motorista_cliente = v.id_motorista_cliente)
    LEFT OUTER JOIN veiculo_cliente AS vc ON (vc.id_veiculo_cliente = v.id_veiculo_cliente)
WHERE
    mvt.id_empresa IN (
        SELECT
            id_empresa
        FROM
            sis_empresa se
        WHERE
            se.guid_empresa IN (
                '428FE6893116415A9E8492A4E4389FDC',
                '58F6191039094B118F8FA138D3571059',
                'A17799B432C14F04AAB0278CEE9F3AB1',
                'AE167ECCFDBF4434A7688C9B5EFFE988',
                'B532F21B0B9D41B29EAE1FE0B223A805',
                'F2CA336B72CC45C9B7A504374B44FA77',
                'F75A07CBC0354BCF8E88411CF5F1B0D0'
            )
    )
ORDER BY
    v.data_cupom DESC;

--QUERY QTD MOTORISTAS
SELECT
    CAST(COUNT(1) AS INTEGER) as qtdMotoristas
FROM
    motorista_cliente AS mc
WHERE
    mc.id_cliente = CAST(16692656 AS BIGINT)
    AND registro_ativo = 'S';

--QUERY QTD VEICULOS
SELECT
    CAST(COUNT(1) AS INTEGER) as qtdVeiculos
FROM
    veiculo_cliente AS vc
WHERE
    vc.id_cliente = CAST(16692656 AS BIGINT)
    AND registro_ativo = 'S';

--QUERY QTD TITULOS EM ABERTO
SELECT
    CAST(COUNT(1) AS INTEGER) as qtdTitulosAbertos
FROM
    vwfw_titulo_financeiro AS tf
    INNER JOIN sis_empresa AS e ON (e.id_empresa = tf.id_empresa)
WHERE
    tf.id_credor_devedor = CAST(16692656 AS BIGINT)
    AND tf.situacao in (1, 9)
    AND tf.pagar_receber = 2;

--QUERY QTD COMPRAS
SELECT
    CAST(COUNT(1) AS INTEGER) as qtdCompras
FROM
    venda_cf AS v
    INNER JOIN item_venda_cf AS iv ON (iv.id_venda_cf = v.id_venda_cf)
    INNER JOIN movimento_venda_terminal AS mvt ON (
        mvt.id_movimento_venda_terminal = v.id_movimento_venda_terminal
    )
WHERE
    v.id_cliente = CAST(16692656 AS BIGINT)
    AND v.cancelada = 'N'
    AND iv.cancelado = 'N'
    AND DATE_PART('MONTH', CAST(v.data_cupom AS DATE)) = DATE_PART('MONTH', current_date)
    AND DATE_PART('YEAR', CAST(v.data_cupom as date)) = DATE_PART('YEAR', current_date)
    AND mvt.id_empresa IN (
        SELECT
            id_empresa
        FROM
            sis_empresa se
        WHERE
            se.guid_empresa IN (
                '428FE6893116415A9E8492A4E4389FDC',
                '58F6191039094B118F8FA138D3571059',
                'A17799B432C14F04AAB0278CEE9F3AB1',
                'AE167ECCFDBF4434A7688C9B5EFFE988',
                'B532F21B0B9D41B29EAE1FE0B223A805',
                'F2CA336B72CC45C9B7A504374B44FA77',
                'F75A07CBC0354BCF8E88411CF5F1B0D0'
            )
    );

--QUERY SALDO ADIANTAMENTO
SELECT
    COALESCE(SUM(t.valor_titulo), 0)
FROM
    titulo_financeiro as t
WHERE
    t.situacao IN (1, 9)
    AND t.natureza_titulo = 106
    AND t.id_credor_devedor = CAST(16692656 AS BIGINT);

--QUERY LIMITE CREDITO
Select
    (
        CASE
            WHEN(
                c.sobrepoe_parametros_classe = 'S'
                OR c.id_classe_cliente IS NULL
            ) THEN c.limite_credito
            ELSE cc.limite_credito
        END
    ) AS limite_credito
FROM
    cliente AS c
    LEFT OUTER JOIN classe_cliente AS cc ON (cc.id_classe_cliente = c.id_classe_cliente)
WHERE
    c.id_cliente = CAST(16692656 AS BIGINT);

--QUERY RESUMO CREDITO UTILIZADO
SELECT
    COALESCE(
        SUM(
            valor_titulo + juros_cobranca + multa_cobranca - desconto_cobranca - retencao_tributaria - valor_taxa_administracao - tarifa_transacao
        ),
        0
    )
FROM
    titulo_financeiro
WHERE
    situacao IN (1, 5, 9)
    AND NOT natureza_titulo IN (
        SELECT
            CASE
                WHEN tr.id_tipo_recebimento = 2 THEN 204
                WHEN tr.id_tipo_recebimento = 3 THEN 205
                WHEN tr.id_tipo_recebimento = 4 THEN 202
                WHEN tr.id_tipo_recebimento = 5 THEN 203
                WHEN tr.id_tipo_recebimento = 6 THEN 201
                WHEN tr.id_tipo_recebimento = 7 THEN 213
                ELSE 0
            END AS natureza_titulo
        FROM
            tipo_recebimento AS tr
            LEFT OUTER JOIN tipo_recebimento_terminal AS trt ON (trt.id_tipo_recebimento = tr.id_tipo_recebimento)
        WHERE
            COALESCE(trt.exige_credito, tr.exige_credito) = 'N'
    )
    AND pagar_receber = 2
    AND id_credor_devedor = CAST(16692656 AS BIGINT);

--QUERY TITULOS VENCIDOS
SELECT
    SUM(tf.valor_titulo)
FROM
    titulo_financeiro AS tf
    INNER JOIN sis_empresa AS se ON (se.id_empresa = tf.id_empresa)
WHERE
    tf.id_credor_devedor = CAST(16692656 AS BIGINT)
    AND tf.situacao IN (1, 9)
    AND tf.pagar_receber = 2
    AND tf.data_vencimento < CURRENT_DATE;

--QUERY TITULOS A VENCER
SELECT
    SUM(tf.valor_titulo)
FROM
    titulo_financeiro AS tf
    INNER JOIN sis_empresa AS se ON (se.id_empresa = tf.id_empresa)
WHERE
    tf.id_credor_devedor = CAST(16692656 AS BIGINT)
    AND tf.situacao IN (1, 9)
    AND tf.pagar_receber = 2
    AND tf.data_vencimento >= CURRENT_DATE;

--QUERY TITULOS A FATURAR
SELECT
    SUM(tf.valor_titulo)
FROM
    titulo_financeiro AS tf
    INNER JOIN sis_empresa AS se ON (se.id_empresa = tf.id_empresa)
WHERE
    tf.id_credor_devedor = CAST(16692656 AS BIGINT)
    AND tf.situacao IN (1, 9)
    AND tf.pagar_receber = 2
    AND tf.tipo_titulo = 1;