IF OBJECT_ID('UFD_SESI_INTEGRADOR') IS NOT NULL
BEGIN
	DROP FUNCTION UFD_SESI_INTEGRADOR
END
GO

/*
=======================================================================================
'Nvel Classificao.: INFORMAO RESTRITA
'Verso............: 20240904
'Job..............:  
'Nome.............: [UFD_SESI_INTEGRADOR]
'Objetivo.........: view usada pelo programa integrador da Linx para geração do arquivo de integração com o Benner, para Dinheiro
'Tabelas..........: 
'Dt Criao.........: 2024-10-18
'Dt Ult Alteraco..:
'Alterado por.....: Marcos Barros
'Criado por.......: Marcos Barros 
'=======================================================================================
SELECT * FROM UFD_SESI_INTEGRADOR('2024-10-08', 544)
 */
 

CREATE FUNCTION [dbo].[UFD_SESI_INTEGRADOR] (@DATE DATE,@CD_FILIAL INT)
RETURNS TABLE
AS
RETURN 
(
    -- CTE PARA CALCULAR CODIGOCONTA
    WITH CODIGOCONTA AS (
        SELECT 
            PDV_VD_IT.CD_EMP, 
            PDV_VD_IT.CD_FILIAL, 
            PDV_VD_IT.CD_VD, 
            CASE WHEN PDV_VD_IT.TP_PROD = 10 THEN 42 ELSE 29 END AS CODIGOCONTA
        FROM 
            PDV_VD_IT
        WHERE 
            PDV_VD_IT.DT_VD = @DATE
			AND (@CD_FILIAL = 0 OR PDV_VD_IT.CD_FILIAL = @CD_FILIAL)
            AND PDV_VD_IT.ST_IT = 1
        GROUP BY 
            PDV_VD_IT.CD_EMP, 
            PDV_VD_IT.CD_FILIAL, 
            PDV_VD_IT.CD_VD, 
            PDV_VD_IT.TP_PROD
    ),

    -- CTE PARA CALCULAR OS VALORES DE PDV_VD COM BASE NA CTE ANTERIOR
    AGGREGATEDPDV AS (
        SELECT 
            PDV_VD.CD_FILIAL,
            PDV_VD.DT_VD,
            SUM(PDV_VD.VL_CHQS_A_VISTA) AS VL_CHQS_A_VISTA,
            SUM(PDV_VD.VLR_DINH - PDV_VD.VLR_TROCO_CHQ) AS VLR_DINH,
            CC.CODIGOCONTA
        FROM 
            PDV_VD
        INNER JOIN CODIGOCONTA CC ON 
            PDV_VD.CD_EMP = CC.CD_EMP AND 
            PDV_VD.CD_FILIAL = CC.CD_FILIAL AND 
            PDV_VD.CD_VD = CC.CD_VD
        WHERE 
            PDV_VD.DT_VD = @DATE
			AND (@CD_FILIAL = 0 OR PDV_VD.CD_FILIAL = @CD_FILIAL)
            AND PDV_VD.ST_VD = 0
        GROUP BY 
            PDV_VD.CD_FILIAL, 
            PDV_VD.DT_VD, 
            CC.CODIGOCONTA
    )

    -- Consulta principal
    SELECT 
        AVISTA.CD_FILIAL AS CD_FILIAL,
        AVISTA.DT_MOV AS DT_MOV,
        SUM(AVISTA.VLR_CHQ_VISTA) AS VLR_CHQ_VISTA,
        SUM(AVISTA.VLR_DINH) AS VLR_DINH,
		SUM(AVISTA.VLR_NCC_QUITACAO_DINHEIRO) AS VLR_NCC_QUITACAO_DINHEIRO,
    --    SUM(AVISTA.VLR_FECHAMENTO) AS VLR_FECHAMENTO,
        AVISTA.CODIGOCONTA AS CODIGOCONTA,
		AVISTA.TIPOMOVIMENTO AS TIPOMOVIMENTO
		
    FROM (
        SELECT 
            PDV_VD_FECHA_MOV.CD_FILIAL, 
            PDV_VD_FECHA_MOV.DT_MOV AS DT_MOV, 
            COALESCE(PDV.VL_CHQS_A_VISTA, 0) AS VLR_CHQ_VISTA, 
            CASE 
                WHEN ISNULL(PDV_VD_FECHA_MOV.NAO_ABATE_DEV_NO_DINHEIRO_FECHA_CAIXA, 0) = 0 THEN COALESCE(PDV.VLR_DINH, 0)  
                ELSE COALESCE(PDV.VLR_DINH, 0) - (PDV_VD_FECHA_MOV.VLR_DESP + PDV_VD_FECHA_MOV.VLR_NCC_AMORTIZADA + PDV_VD_FECHA_MOV.VLR_NCC_CANCELADA_DINH) 
            END AS VLR_DINH,
			CASE 
				WHEN ISNULL(NAO_ABATE_DEV_NO_DINHEIRO_FECHA_CAIXA, 0) = 0 THEN PDV_VD_FECHA_MOV.VLR_DINH 
				ELSE (PDV_VD_FECHA_MOV.VLR_SLD_INI + PDV_VD_FECHA_MOV.VLR_DINH) - (PDV_VD_FECHA_MOV.VLR_DESP + PDV_VD_FECHA_MOV.VLR_FUNDO_TROCO + PDV_VD_FECHA_MOV.VLR_NCC_AMORTIZADA + PDV_VD_FECHA_MOV.VLR_NCC_CANCELADA_DINH) 
			END AS VLR_FECHAMENTO, 
			
			COALESCE(PDV_VD_FECHA_MOV.VLR_NCC_QUITACAO_DINHEIRO,0) AS VLR_NCC_QUITACAO_DINHEIRO,

            COALESCE(PDV.CODIGOCONTA, 29) AS CODIGOCONTA,
			CASE  WHEN  PDV.CODIGOCONTA = 42 THEN  'SERVIÇOS' ELSE 'PRODUTOS' END AS TIPOMOVIMENTO
        FROM 
            PDV_VD_FECHA_MOV AS PDV_VD_FECHA_MOV
        LEFT JOIN 
            AGGREGATEDPDV PDV ON 
            PDV_VD_FECHA_MOV.CD_FILIAL = PDV.CD_FILIAL AND 
            PDV_VD_FECHA_MOV.DT_MOV = PDV.DT_VD
        WHERE 
            PDV_VD_FECHA_MOV.DT_MOV = @DATE
		 AND (@CD_FILIAL = 0 OR PDV_VD_FECHA_MOV.CD_FILIAL = @CD_FILIAL)

        UNION ALL

        SELECT 
            PDV_CUPOM_NAO_FISCAL.CD_FILIAL, 
            PDV_CUPOM_NAO_FISCAL.DT_CUPOM AS DT_MOV, 
            SUM(PDV_CUPOM_NAO_FISCAL.VLR_CHQS_A_VISTA) AS VLR_CHQ_VISTA, 
            SUM(PDV_CUPOM_NAO_FISCAL.VLR_DINH) AS VLR_DINH,
			0 AS VLR_NCC_QUITACAO_DINHEIRO,
            0 AS VLR_DINH2,
            29 AS CODIGOCONTA,
			'PRODUTO' AS TIPOMOVIMENTO
        FROM     
            PDV_VD_FECHA_MOV
        INNER JOIN PDV_CUPOM_NAO_FISCAL ON 
            PDV_CUPOM_NAO_FISCAL.CD_FILIAL = PDV_VD_FECHA_MOV.CD_FILIAL
            AND PDV_CUPOM_NAO_FISCAL.DT_CUPOM = PDV_VD_FECHA_MOV.DT_MOV
        WHERE  
            PDV_CUPOM_NAO_FISCAL.TP_CUPOM_NAO_FISCAL = 0
			AND (@CD_FILIAL = 0 OR PDV_CUPOM_NAO_FISCAL.CD_FILIAL = @CD_FILIAL)
            AND PDV_CUPOM_NAO_FISCAL.DT_CUPOM = @DATE
        GROUP BY PDV_CUPOM_NAO_FISCAL.CD_FILIAL, PDV_CUPOM_NAO_FISCAL.DT_CUPOM
    ) AS AVISTA
    GROUP BY AVISTA.CD_FILIAL, AVISTA.DT_MOV, AVISTA.CODIGOCONTA, AVISTA.TIPOMOVIMENTO
);
GO


