 IF EXISTS
(
    SELECT *
    FROM sys.views
    WHERE object_id = OBJECT_ID(N'[dbo].[V_PDV_VD_CRM_BONUS]')
)
    DROP VIEW [dbo].[V_PDV_VD_CRM_BONUS];
GO

CREATE VIEW [dbo].[V_PDV_VD_CRM_BONUS] AS
WITH 
TEF_AGG AS (
    SELECT 
        TEF.CD_EMP,
        TEF.CD_FILIAL,
        TEF.CD_VD,
        TEF.COD_MODALIDADE,
        TEF.CD_ADM_CARTAO,
        TEF.TP_ADM,
        TEF.NM_OPERADORA_PDV,
		TEF.QTDE_PARCELAS,
        SUM(CASE WHEN TEF.TP_ADM <> 6 AND LEFT(TEF.COD_MODALIDADE, 1) = '0' THEN TEF.VL_CARTAO ELSE 0 END) AS VLR_CARTAO_POS,
        SUM(CASE WHEN TEF.TP_ADM <> 6 AND LEFT(TEF.COD_MODALIDADE, 2) IN ('10', '30') THEN TEF.VL_CARTAO ELSE 0 END) AS VLR_CARTAO_DEBITO,
        SUM(CASE WHEN TEF.TP_ADM <> 6 AND LEFT(TEF.COD_MODALIDADE, 2) = '20' THEN TEF.VL_CARTAO ELSE 0 END) AS VLR_CARTAO_CREDITO,
        SUM(CASE WHEN TEF.TP_ADM <> 6 AND LEFT(TEF.COD_MODALIDADE, 2) = '70' THEN TEF.VL_CARTAO ELSE 0 END) AS VLR_PIX
    FROM PDV_VD_TEF TEF
    GROUP BY TEF.CD_EMP, TEF.CD_FILIAL, TEF.CD_VD, TEF.COD_MODALIDADE, TEF.CD_ADM_CARTAO, TEF.TP_ADM, TEF.NM_OPERADORA_PDV, TEF.QTDE_PARCELAS
),
CREDIARIO_AGG AS (
    SELECT 
        CRED.CD_EMP,
        CRED.CD_FILIAL,
        CRED.CD_VD,
        SUM(CRED.VLR_PARC_CRED) AS VLR_CREDIARIO
    FROM RC_CREDIARIO_PARC CRED
    GROUP BY CRED.CD_EMP, CRED.CD_FILIAL, CRED.CD_VD
),
IT_AGG AS (
    SELECT 
        IT.CD_EMP,
        IT.CD_FILIAL,
        IT.CD_VD,
        SUM(CASE WHEN IT.ST_IT = 1 THEN IT.QT_IT ELSE 0 END) AS QT_IT
    FROM PDV_VD_IT IT
    GROUP BY IT.CD_EMP, IT.CD_FILIAL, IT.CD_VD
),
VENDEDOR_AGG AS (
    SELECT DISTINCT
        USU.CD_USU,
        USU.NM_USU,
        USU.CD_FILIAL
    FROM GLB_USU USU
),
VENDAS_PROCESSADAS_AGG AS (
    SELECT DISTINCT 
    CD_VD   
    FROM VENDAS_PROCESSADAS_CRM_BONUS            
)
SELECT
    VD.CD_EMP,
    VD.CD_FILIAL,
    VD.CD_VD,
    VD.DT_VD,
	VD.HR_VD,
    VD.ST_VD,
    VD.CD_CLI,
    VD.CGC_CPF,
    VD.NR_TEL_CUPOM,
    VD.NM_COMPRADOR,
    TEF.COD_MODALIDADE,
	TEF.QTDE_PARCELAS,
	RAC.NM_USUAL AS NOME_POS,
    TEF.NM_OPERADORA_PDV AS NOME_OPERADORA_CARTAO,
    VEND.CD_USU,
    VEND.NM_USU,
	IT.QT_IT AS QUANTIDADE_ITENS,
    ISNULL(VD.VLR_DINH, 0) AS VLR_DINH,
    ISNULL(VD.VLR_CHQS, 0) AS VLR_CHQS,
    ISNULL(TEF.VLR_PIX, 0) AS VLR_PIX,
	ISNULL(TEF.VLR_CARTAO_POS, 0) AS VLR_CARTAO_POS,
    ISNULL(TEF.VLR_CARTAO_DEBITO, 0) AS VLR_CARTAO_DEBITO,
    ISNULL(TEF.VLR_CARTAO_CREDITO, 0) AS VLR_CARTAO_CREDITO,
    ISNULL(CRED.VLR_CREDIARIO, 0) AS VLR_CREDIARIO,
    ISNULL(VD.VLR_TICKETS, 0) AS VLR_TICKETS,
    ISNULL(VD.VLR_CONV, 0) AS VLR_CONV,
    ISNULL(VD.VLR_DUP, 0) AS VLR_DUP,
    ISNULL(VD.VLR_NCC, 0) AS VLR_NCC,
    ISNULL(VD.VLR_CONV_PBM, 0) AS VLR_CONV_PBM,
    ISNULL(VD.VLR_LIQ_VD, 0) AS VLR_TOT_LIQ_VENDA,
    ISNULL(VD.VLR_VD, 0) AS VLR_TOT_BRUTO_VENDA
FROM PDV_VD VD
LEFT JOIN TEF_AGG TEF
    ON TEF.CD_EMP = VD.CD_EMP 
    AND TEF.CD_FILIAL = VD.CD_FILIAL 
    AND TEF.CD_VD = VD.CD_VD
LEFT JOIN RC_ADM_CARTAO RAC
    ON RAC.CD_EMP = TEF.CD_EMP
    AND RAC.CD_ADM_CARTAO = TEF.CD_ADM_CARTAO
    AND RAC.TP_ADM = TEF.TP_ADM	
LEFT JOIN CREDIARIO_AGG CRED
    ON CRED.CD_EMP = VD.CD_EMP 
    AND CRED.CD_FILIAL = VD.CD_FILIAL 
    AND CRED.CD_VD = VD.CD_VD
LEFT JOIN IT_AGG IT
    ON IT.CD_EMP = VD.CD_EMP 
    AND IT.CD_FILIAL = VD.CD_FILIAL 
    AND IT.CD_VD = VD.CD_VD
LEFT JOIN VENDEDOR_AGG VEND
    ON VEND.CD_USU = VD.CD_USU
   WHERE VD.DT_VD = CAST(DATEADD(DAY, -1, GETDATE()) AS DATE)
UNION ALL
SELECT
    VD.CD_EMP,
    VD.CD_FILIAL,
    VD.CD_VD,
    VD.DT_VD,
	VD.HR_VD,
    VD.ST_VD,
    VD.CD_CLI,
    VD.CGC_CPF,
    VD.NR_TEL_CUPOM,
    VD.NM_COMPRADOR,
	TEF.QTDE_PARCELAS,
    TEF.COD_MODALIDADE,
	RAC.NM_USUAL AS NOME_POS,
    TEF.NM_OPERADORA_PDV AS NOME_OPERADORA_CARTAO,
    VEND.CD_USU,
    VEND.NM_USU,
	IT.QT_IT AS QUANTIDADE_ITENS,
    ISNULL(VD.VLR_DINH, 0) AS VLR_DINH,
    ISNULL(VD.VLR_CHQS, 0) AS VLR_CHQS,
    ISNULL(TEF.VLR_PIX, 0) AS VLR_PIX,
	ISNULL(TEF.VLR_CARTAO_POS, 0) AS VLR_CARTAO_POS,
    ISNULL(TEF.VLR_CARTAO_DEBITO, 0) AS VLR_CARTAO_DEBITO,
    ISNULL(TEF.VLR_CARTAO_CREDITO, 0) AS VLR_CARTAO_CREDITO,
    ISNULL(CRED.VLR_CREDIARIO, 0) AS VLR_CREDIARIO,
    ISNULL(VD.VLR_TICKETS, 0) AS VLR_TICKETS,
    ISNULL(VD.VLR_CONV, 0) AS VLR_CONV,
    ISNULL(VD.VLR_DUP, 0) AS VLR_DUP,
    ISNULL(VD.VLR_NCC, 0) AS VLR_NCC,
    ISNULL(VD.VLR_CONV_PBM, 0) AS VLR_CONV_PBM,
    ISNULL(VD.VLR_LIQ_VD, 0) AS VLR_TOT_LIQ_VENDA,
    ISNULL(VD.VLR_VD, 0) AS VLR_TOT_BRUTO_VENDA
FROM PDV_VD VD
INNER JOIN VENDAS_PROCESSADAS_CRM_BONUS VPCB
    ON  VPCB.CD_EMP = VD.CD_EMP 
	AND VPCB.CD_FILIAL = VD.CD_FILIAL 
	AND VPCB.DT_PROCESSAMENTO >= CAST(DATEADD(DAY, -7, GETDATE()) AS DATE)   
    AND VPCB.CD_VD = VD.CD_VD
    AND VPCB.ST_VD <> VD.ST_VD
LEFT JOIN TEF_AGG TEF
    ON TEF.CD_EMP = VD.CD_EMP 
    AND TEF.CD_FILIAL = VD.CD_FILIAL 
    AND TEF.CD_VD = VD.CD_VD
LEFT JOIN RC_ADM_CARTAO RAC
    ON RAC.CD_EMP = TEF.CD_EMP
    AND RAC.CD_ADM_CARTAO = TEF.CD_ADM_CARTAO
    AND RAC.TP_ADM = TEF.TP_ADM		
LEFT JOIN CREDIARIO_AGG CRED
    ON CRED.CD_EMP = VD.CD_EMP 
    AND CRED.CD_FILIAL = VD.CD_FILIAL 
    AND CRED.CD_VD = VD.CD_VD
LEFT JOIN IT_AGG IT
    ON IT.CD_EMP = VD.CD_EMP 
    AND IT.CD_FILIAL = VD.CD_FILIAL 
    AND IT.CD_VD = VD.CD_VD
LEFT JOIN VENDEDOR_AGG VEND
    ON VEND.CD_USU = VD.CD_USU