IF EXISTS (SELECT * FROM SYS.VIEWS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[V_EST_PROD_DOTZ_VENDA]'))
	DROP VIEW [DBO].V_EST_PROD_DOTZ_VENDA
GO
/*
=======================================================================================
'N�vel Classifica��o.: INFORMA��O RESTRITA
'Vers�o..............: 2112090800 
'Job.................:  
'Nome................:  V_EST_PROD_DOTZ_PRODUTO
'Objetivo............:  
'Tabelas.............: 
'Dt Cria��o..........: 
'Dt Ult Alterac�o....: 09/12/2021
'Alterado por........: 
'Criado por..........: Marcos Barros   
'=======================================================================================
*/
CREATE VIEW V_EST_PROD_DOTZ_VENDA
AS
SELECT 
	CONVERT(VARCHAR(33), PDV_VD.DT_VD, 127) AS DT_VD, 
	(CASE
		WHEN PDV_VD.VENDA_DOTZ = 1 THEN PDV_VD.CGC_CPF 
		ELSE '' 
	END)	 						AS CUSTOMER_ID,						
	PDV_VD.NR_ECF					AS TICKET_ID,							
	PDV_VD.CD_FILIAL				AS STORE_ID,							
	PDV_VD.CD_CX					AS POS_ID,								
	ISNULL(PDV_VD.VLR_VD, '')		AS AMOUNT,								
	''								AS CHANNEL,	
	ISNULL((CASE 
		WHEN PDV_VD.VENDA_DOTZ = 1 THEN 1 
		ELSE 0 
	END), '')						AS COUPON_FLAG,						
	CONVERT(VARCHAR(33), PDV_VD.DT_CONSD, 126)					AS DATE,							
	ISNULL(PDV_VD.VLR_DESC, '')		AS DISCOUNT_AMOUNT,					
	ISNULL(PDV_VD.VLR_LIQ_VD, '')	AS GROSS_PROFIT_AMOUNT,				
	ISNULL(SUM(PDV_DOTZ.DOTZ),'')	AS POINTS_EARNED_AMOUNT,			
	ISNULL((CASE
		WHEN PDV_DOTZ.DOTZ < 0 THEN SUM(PDV_DOTZ.DOTZ) 
		ELSE ''
	END), '')						AS POINTS_BURNED_AMOUNT,			
	ISNULL(T.QTD, '')				AS ITEMS_QUANTITY,	
	ISNULL((CASE 
		WHEN CONVERT(VARCHAR,ISNULL(PDV_VD.VENDA_DOTZ, 0)) > 0 THEN 'DOTZ'
		WHEN PDV_VD.VLR_DINH > 0 THEN 'CASH'
		WHEN PDV_VD.VLR_CHQS > 0 OR PDV_VD.VL_CHQS_A_PRZ > 0 OR PDV_VD.VL_CHQS_A_VISTA > 0 THEN 'CHECK'
		WHEN PDV_VD.VLR_CARTAO > 0 THEN 'CARD'
		ELSE ''
	END), '')						AS PAYMENT_METHOD,				
	ISNULL((CASE	
		WHEN VLR_CONV > 0 THEN 'FORWARD'
		ELSE 'SPOT'
	END), '') 						AS PAYMENT_TERMS,					
	''								AS IIN_ISSUER_IDENTIFICATION_NUMBER,
	ISNULL((CASE 
		WHEN VLR_CARTAO > 0 AND QT_PARC_CONV > 1 THEN 0	
	 ELSE 1 
	 END ), '')						AS DEBIT_CREDIT_FLAG,				
	PDV_VD_IT.CD_PROD				AS PRODUCT_ID,						
	PDV_VD_IT.CD_IT					AS ITEM_ID,							
	(CASE 
		WHEN PDV_VD_IT.CD_TBL_DESC <> 0 THEN 1 
		ELSE 0 
	END)							AS DISPLAY_FLAG,					
	(CASE 
		WHEN PROD_TABLOID.CD_TABLOIDE <> 0 THEN 1 
		ELSE 0 
	END)							AS FEATURE_FLAG,					
	(CASE
		WHEN PDV_DOTZ.CD_IT = 1 AND PDV_DOTZ.DOTZ > 0 THEN SUM(PDV_DOTZ.DOTZ) 
		ELSE ''
	END)							AS POINTS_BONUS_AMOUNT,				
	(CASE 
		WHEN PDV_VD_IT.TIPO_DESCONTO <> 0 THEN 1 
		ELSE 0 
	END)							AS PRICE_REDUCTION_FLAG,							
	T.QTD							AS QUANTITY,						
	PDV_VD_IT.CD_BARRA				AS GTIN,	
	''								AS EVENT_ID							
FROM
	PDV_VD
	INNER JOIN PDV_VD_IT ON(
			PDV_VD.CD_EMP		= PDV_VD_IT.CD_EMP
        AND PDV_VD.CD_FILIAL	= PDV_VD_IT.CD_FILIAL
        AND PDV_VD.CD_VD		= PDV_VD_IT.CD_VD
		AND PDV_VD.DT_VD		>= DATEADD(DAY,-360, GETDATE())
	)
	INNER JOIN  (SELECT CD_EMP, CD_FILIAL, CD_VD, MAX(CD_IT) AS QTD FROM PDV_VD_IT  WHERE DT_VD >= DATEADD(DAY,-360, GETDATE()) GROUP BY CD_EMP, CD_FILIAL, CD_VD) AS T ON (
			PDV_VD.CD_EMP		= T.CD_EMP
        AND PDV_VD.CD_FILIAL	= T.CD_FILIAL
		AND PDV_VD.CD_VD		= T.CD_VD
	)
	LEFT JOIN PDV_VD_IT_DOTZ AS PDV_DOTZ ON (
			PDV_DOTZ.CD_EMP = PDV_VD.CD_EMP
		AND PDV_DOTZ.CD_FILIAL = PDV_VD.CD_FILIAL
		AND PDV_DOTZ.CD_CX = PDV_VD.CD_CX
		AND PDV_DOTZ.CD_VD = PDV_VD.CD_VD
	)
	LEFT JOIN EST_PROD_TABLOIDE_CPL AS PROD_TABLOID ON (
			PROD_TABLOID.CD_EMP = PDV_VD_IT.CD_EMP
		AND PROD_TABLOID.CD_PROD = PDV_VD_IT.CD_PROD
	)
WHERE PDV_VD.CD_USU_CANCEL = 0
GROUP BY
		PDV_VD.DT_VD,
		PDV_VD.VENDA_DOTZ,	
		PDV_VD.CGC_CPF,
		PDV_VD.NR_ECF,
		PDV_VD.CD_FILIAL,
		PDV_VD.VLR_VD,	
		PDV_VD.CD_CX,
		PDV_VD.VENDA_DOTZ,
		PDV_VD.VLR_CARTAO,
		PDV_VD.QT_PARC_CONV,
		PDV_VD.DT_CONSD,							
		PDV_VD.VLR_DESC,					
		PDV_VD.VLR_LIQ_VD,
		PDV_VD_IT.CD_TBL_DESC,
		PROD_TABLOID.CD_TABLOIDE,
		T.QTD,				
		PDV_VD_IT.CD_PROD,					
		PDV_VD_IT.CD_IT,	
		PDV_VD_IT.TIPO_DESCONTO,	
		PDV_VD.VENDA_DOTZ,
		PDV_VD.VLR_CHQS,
		PDV_VD.VL_CHQS_A_PRZ,
		PDV_VD.VL_CHQS_A_VISTA,
		PDV_VD.VLR_DINH,
		VLR_CONV,
		PDV_VD_IT.TIPO_DESCONTO,
		PDV_DOTZ.DOTZ,
		PDV_DOTZ.CD_IT,
		T.QTD,					
		PDV_VD_IT.CD_BARRA;
