UPDATE PDV_CONSLD_VD_MOVIMENTO
SET
    PDV_CONSLD_VD_MOVIMENTO.CONSD_PENDENTE = 0
FROM PDV_CONSLD_VD_MOVIMENTO
WHERE
    PDV_CONSLD_VD_MOVIMENTO.CONSD_PENDENTE = 1;
GO

IF EXISTS(SELECT * FROM sys.objects
				WHERE object_id = OBJECT_ID(N'[dbo].[P_PDV_CONSLD_VD_MOVIMENTO_GERACAO]') AND type IN(N'P', N'PC'))
    BEGIN 
		DROP PROCEDURE [dbo].[P_PDV_CONSLD_VD_MOVIMENTO_GERACAO];
    END;
GO

/*
=======================================================================================
'Job.................: Linx - Manutencao 15 min. (executada pela P_PDV_CONSLD_VD_MOVIMENTO_GERACAO_EXECUTE_ALL)
'Função..............: 
'Objetivo............: Atualizar relatório de leitura Z
'Tabelas.............: 
'Dt Modificação......: 08/03/2021
'Modificação.........: Tag #A001 Round para gerar  o vlr_diferenca
					   Tag #A002 Round no VLR_ISSQN
'=======================================================================================
Tag #A001 - Alteração do codigo  
Tag #I001 - Inclusão de novo codigo 
*/
--EXEC P_PDV_CONSLD_VD_MOVIMENTO_GERACAO 1,1,'2018-06-21','2018-06-21'
CREATE PROCEDURE [dbo].[P_PDV_CONSLD_VD_MOVIMENTO_GERACAO](@CD_EMP    INT
															, @CD_FILIAL INT
															, @DT_INI    DATETIME
															, @DT_FIM    DATETIME )
WITH ENCRYPTION,
     RECOMPILE
AS

     SET NOCOUNT ON;
     SET ANSI_WARNINGS OFF; 

	CREATE TABLE #RSCUPOMQTDE(
									CD_EMP                       INT
									, CD_FILIAL                    INT
									, DT_VD                        DATETIME
									, QT_CUPOM_LOJA                INT
									, QT_CUPOM_MATRIZ              INT
									, QTDE_CUPOM_NAO_FISCAL_LOJA   INT
									, QTDE_CUPOM_NAO_FISCAL_MATRIZ INT
									);
	
	
	INSERT INTO #RSCUPOMQTDE(
									CD_EMP
									, CD_FILIAL
									, DT_VD
									, QT_CUPOM_LOJA
									, QT_CUPOM_MATRIZ
									, QTDE_CUPOM_NAO_FISCAL_LOJA
									, QTDE_CUPOM_NAO_FISCAL_MATRIZ
									)
        SELECT
                CD_EMP
                , CD_FILIAL
                , DT_VD
                , SUM(FILIAL) AS FILIAL
                , SUM(MATRIZ) AS MATRIZ
                , SUM(QTDE_CUPOM_NAO_FISCAL_LOJA) AS QTDE_CUPOM_NAO_FISCAL_LOJA
                , SUM(QTDE_CUPOM_NAO_FISCAL_MATRIZ) AS QTDE_CUPOM_NAO_FISCAL_MATRIZ
        FROM
			(SELECT
						CD_EMP
						, CD_FILIAL
						, DT_VD
						, COUNT(NR_ECF) AS FILIAL
						, 0 AS MATRIZ
						, 0 AS QTDE_CUPOM_NAO_FISCAL_LOJA
						, 0 AS QTDE_CUPOM_NAO_FISCAL_MATRIZ
				FROM
						PDV_VD_CUPONS
				WHERE CD_EMP = @CD_EMP
						AND CD_FILIAL = @CD_FILIAL
						AND DT_VD BETWEEN @DT_INI AND @DT_FIM
						AND TP_CUPOM = 0
				GROUP BY
							CD_EMP
						, CD_FILIAL
						, DT_VD
				UNION ALL
				SELECT
						CD_EMP
						, CD_FILIAL
						, DT_VD
						, 0 AS FILIAL
						, COUNT(NR_ECF) AS MATRIZ
						, 0 AS QTDE_CUPOM_NAO_FISCAL_LOJA
						, 0 AS QTDE_CUPOM_NAO_FISCAL_MATRIZ
				FROM
						PDV_VD
				WHERE CD_EMP = @CD_EMP
						AND CD_FILIAL = @CD_FILIAL
						AND DT_VD BETWEEN @DT_INI AND @DT_FIM
				GROUP BY
							CD_EMP
						, CD_FILIAL
						, DT_VD
				UNION ALL
				SELECT
						CD_EMP
						, CD_FILIAL
						, DT_VD
						, 0 AS FILIAL
						, 0 AS MATRIZ
						, COUNT(NR_ECF) AS QTDE_CUPOM_NAO_FISCAL_LOJA
						, 0 AS QTDE_CUPOM_NAO_FISCAL_MATRIZ
				FROM
						PDV_VD_CUPONS
				WHERE CD_EMP = @CD_EMP
						AND CD_FILIAL = @CD_FILIAL
						AND DT_VD BETWEEN @DT_INI AND @DT_FIM
						AND TP_CUPOM = 1
				GROUP BY
							CD_EMP
						, CD_FILIAL
						, DT_VD
				UNION ALL
				SELECT
						CD_EMP
						, CD_FILIAL
						, DT_CUPOM
						, 0 AS FILIAL
						, 0 AS MATRIZ
						, 0 AS QTDE_CUPOM_NAO_FISCAL_LOJA
						, COUNT(NR_COO) AS QTDE_CUPOM_NAO_FISCAL_MATRIZ
				FROM
						PDV_CUPOM_NAO_FISCAL
				WHERE CD_EMP = @CD_EMP
						AND CD_FILIAL = @CD_FILIAL
						AND DT_CUPOM BETWEEN @DT_INI AND @DT_FIM
				GROUP BY
							CD_EMP
						, CD_FILIAL
						, DT_CUPOM
			) RS
		GROUP BY
                CD_EMP
            , CD_FILIAL
            , DT_VD;

	 ------------------------------------------------------------------------------------------------------
     DELETE FROM PDV_CONSLD_VD_MOVIMENTO
     WHERE
           CD_EMP = @CD_EMP
           AND CD_FILIAL = @CD_FILIAL
           AND DT_MOV BETWEEN @DT_INI AND @DT_FIM;

	------------------------------------------------------------------------------------------------------
--  Tag #A002
     INSERT INTO PDV_CONSLD_VD_MOVIMENTO (
												CD_EMP
												, CD_FILIAL
												, DT_MOV
												, VLR_LEITUR_Z
												, VLR_PDV_VD
												, VLR_PDV_VD_IT
												, VLR_PDV_VD_RCBTO
												, VLR_PDV_VD_ISSQN
												, DT_ULT_CONSD
												, CONSD_PENDENTE
												, DT_CAD
												, VLR_DIFERENCA
												, FLAG_EXPORT_CTB
												, FLAG_EXPOT_FISCAL
												, FLAG_CAIXA_FECHADO
												, FLAG_CAIXA_BLOQUEADO
												, FLAG_CAIXA_CONSOLIDADO
												, FLAG_CARTAO_FECHADO
												, FLAG_MOV_OCORRENCIA
												, QT_CUPOM_MATRIZ
												, QT_CUPOM_LOJA
												, QT_NAO_CUPOM_MATRIZ
												, QT_NAO_CUPOM_LOJA
											)
    SELECT
            RSLEITURA.CD_EMP
            , RSLEITURA.CD_FILIAL
            , RSLEITURA.DT_VD
            , SUM(VLR_LEITURA) AS VLR_LEITURA
            , SUM(VLR_VENDA) AS VLR_VENDA
            , ROUND(SUM(VLR_VD_IT), 2) AS VLR_VD_IT
            , SUM(VLR_RECBTO) AS VLR_RECBTO
            , ROUND(SUM(VLR_ISSQN), 2) AS VLR_ISSQN
            , MAX(DT_ULT_CONSD) AS DT_ULT_CONSD
            , 0 AS CONSD_PENDENTE
            , GETDATE()
            , 0 AS VLR_DIFERENCA
            , 0 AS FLAG_EXPORT_CTB
            , 0 AS FLAG_EXPOT_FISCAL
            , 0 AS FLAG_CAIXA_FECHADO
            , 0 AS FLAG_CAIXA_BLOQUEADO
            , 0 AS FLAG_CAIXA_CONSOLIDADO
            , 0 AS FLAG_CARTAO_FECHADO
            , 0 AS FLAG_MOV_OCORRENCIA
            , QT.QT_CUPOM_MATRIZ
            , QT.QT_CUPOM_LOJA
            , QT.QTDE_CUPOM_NAO_FISCAL_MATRIZ
            , QT.QTDE_CUPOM_NAO_FISCAL_LOJA
    FROM
		(SELECT
				PDV_VD.CD_EMP
				, PDV_VD.CD_FILIAL
				, PDV_VD.DT_VD
				, SUM(PDV_VD.VLR_LIQ_VD) AS VLR_VENDA
				, 0 AS VLR_LEITURA
				, MAX(DT_CONSD) AS DT_ULT_CONSD
				, 0 AS VLR_ISSQN
				, 0 AS VLR_VD_IT
				, ROUND(SUM(PDV_VD.VLR_DINH + PDV_VD.VLR_CHQS + ISNULL(PDV_VD.VLR_CARTAO, 0) + PDV_VD.VLR_TICKETS + PDV_VD.VLR_DUP + PDV_VD.VLR_CONV + ISNULL(PDV_VD.VLR_CONV_PBM, 0) + ISNULL(PDV_VD.VLR_NCC, 0) - ISNULL(PDV_VD.VLR_TROCO_CHQ, 0)), 2) AS VLR_RECBTO
		FROM
				PDV_VD
		WHERE PDV_VD.CD_EMP = @CD_EMP
				AND PDV_VD.CD_FILIAL = @CD_FILIAL
				AND PDV_VD.DT_VD BETWEEN @DT_INI AND @DT_FIM
				AND PDV_VD.ST_VD = 0
		GROUP BY
					PDV_VD.CD_FILIAL
				, PDV_VD.CD_EMP
				, PDV_VD.DT_VD
		UNION ALL
		SELECT
				PDV_VD.CD_EMP
				, PDV_VD.CD_FILIAL
				, PDV_VD.DT_VD
				, 0 AS VLR_VENDA
				, 0 AS VLR_LEITURA
				, NULL AS DT_ULT_CONSD
				, SUM(CASE
						WHEN ISNULL(PDV_VD_IT.TIPO_IMPOSTO, 0) = 1 THEN(PDV_VD_IT.TOT_IT -  PDV_VD_IT.VLR_DESC_TOT_RAT )
						ELSE 0
					END) AS VLR_ISSQN
				, ISNULL(SUM(PDV_VD_IT.TOT_IT - PDV_VD_IT.VLR_DESC_TOT_RAT), 0) AS VLR_VD_IT
				, 0 AS VLR_RECBTO
		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 = PDV_VD_IT.DT_VD
		WHERE PDV_VD_IT.CD_EMP = @CD_EMP
				AND PDV_VD_IT.CD_FILIAL = @CD_FILIAL
				AND PDV_VD_IT.DT_VD BETWEEN @DT_INI AND @DT_FIM
				AND PDV_VD.ST_VD = 0
				AND PDV_VD_IT.ST_IT = 1
		GROUP BY
					PDV_VD.CD_FILIAL
				, PDV_VD.CD_EMP
				, PDV_VD.DT_VD
		UNION ALL
		SELECT
				CD_EMP
				, CD_FILIAL
				, PDV_CONSLD_VD.DT_MOV
				, 0 AS VLR_VENDA
				, SUM(VLR_VD_LIQ) AS VLR_LEITURA
				, NULL AS DT_ULT_CONSD
				, 0 AS VLR_ISSQN
				, 0 AS VLR_VD_IT
				, 0 AS VLR_RECBTO
		FROM
				PDV_CONSLD_VD
		WHERE CD_EMP = @CD_EMP
				AND CD_FILIAL = @CD_FILIAL
				AND DT_MOV BETWEEN @DT_INI AND @DT_FIM
		GROUP BY
					CD_FILIAL
				, CD_EMP
				, PDV_CONSLD_VD.DT_MOV ) RSLEITURA
		INNER JOIN PRC_FILIAL ON RSLEITURA.CD_EMP = PRC_FILIAL.CD_EMP
                         AND RSLEITURA.CD_FILIAL = PRC_FILIAL.CD_FILIAL
		INNER JOIN #RSCUPOMQTDE QT ON RSLEITURA.CD_EMP = QT.CD_EMP
									  AND RSLEITURA.CD_FILIAL = QT.CD_FILIAL
									  AND RSLEITURA.DT_VD = QT.DT_VD
    GROUP BY
                RSLEITURA.CD_EMP
            , RSLEITURA.CD_FILIAL
            , FLAG_CONSOLIDADOR_ATIVO
            , RSLEITURA.DT_VD
            , QT.QT_CUPOM_MATRIZ
            , QT.QT_CUPOM_LOJA
            , QT.QTDE_CUPOM_NAO_FISCAL_MATRIZ
            , QT.QTDE_CUPOM_NAO_FISCAL_LOJA; 

	------------------------------------------------------------------------------------------------------
	--Analisando a diferenca
	--Tag #A001	
	UPDATE PDV_CONSLD_VD_MOVIMENTO
	SET
		VLR_DIFERENCA = CASE
							WHEN round( (VLR_LEITUR_Z - (VLR_PDV_VD - VLR_PDV_VD_ISSQN)),2) <> 0 THEN round((VLR_LEITUR_Z - (VLR_PDV_VD - VLR_PDV_VD_ISSQN)),2)
							ELSE CASE
									WHEN round((VLR_PDV_VD - VLR_PDV_VD_IT),2) <> 0 THEN round((VLR_PDV_VD - (VLR_PDV_VD_IT)),2)
									ELSE round((VLR_PDV_VD - (VLR_PDV_VD_RCBTO)),2)
								END
						END
	WHERE
		CD_EMP = @CD_EMP
		AND CD_FILIAL = @CD_FILIAL
		AND DT_MOV BETWEEN @DT_INI AND @DT_FIM;


	------------------------------------------------------------------------------------------------------
	--Reducao z nao pode existir data atual..
	-- Esta dando erro de duplicidade
	-- pois não existe ISNULL(PDV_MAPA_RESUMO.CD_CTR,0)

     IF @DT_FIM >= CONVERT(DATE, GETDATE())
         BEGIN
             SET @DT_FIM = DATEADD(day, -1, CONVERT(DATE, GETDATE()));
         END;

	------------------------------------------------------------------------------------------------------
	--Conferencia de movimento leitura Z versus cupons
	DELETE FROM PDV_MAPA_RESUMO_MOVIMENTO_CONFERENCIA
	WHERE
		CD_EMP = @CD_EMP
		AND CD_FILIAL = @CD_FILIAL
		AND DT_ECF BETWEEN @DT_INI AND @DT_FIM;


     INSERT INTO PDV_MAPA_RESUMO_MOVIMENTO_CONFERENCIA(CD_EMP
														, CD_FILIAL
														, CD_CTR
														, DT_ECF
														, VLR_LEITURA_Z
														, VLR_VENDA_CUPONS
														, VLR_ICMS_Z
														, VLR_ICMS_CUPONS
														, VLR_ICMS_SUBS_Z
														, VLR_ICMS_SUBS_CUPONS
														, VLR_ISSQN
														, VLR_N_TRIB
														, CD_CX)
    SELECT
            V_PDV_VD_TRIBUTOS.CD_EMP
            , V_PDV_VD_TRIBUTOS.CD_FILIAL
            , ISNULL(PDV_MAPA_RESUMO.CD_CTR, 0)
            , V_PDV_VD_TRIBUTOS.DT_VD
            , ISNULL(PDV_MAPA_RESUMO.VLR_CONTAB, 0)
            , V_PDV_VD_TRIBUTOS.VLR_VENDA_LIQUIDA
            , ISNULL(PDV_MAPA_RESUMO.VLR_ICMS, 0)
            , CONVERT(MONEY, ROUND(V_PDV_VD_TRIBUTOS.VLR_IMPOSTO, 2, 1))
            , ISNULL(PDV_MAPA_RESUMO.VLR_BASE_ICMS_SUBS, 0)
            , ISNULL(V_PDV_VD_TRIBUTOS.VLR_SUBST_TRIB, 0)
            , V_PDV_VD_TRIBUTOS.BASE_ISSQN
            , 0
            , V_PDV_VD_TRIBUTOS.CD_CX
    FROM
            V_PDV_VD_TRIBUTOS
            LEFT OUTER JOIN PDV_MAPA_RESUMO ON V_PDV_VD_TRIBUTOS.CD_EMP = PDV_MAPA_RESUMO.CD_EMP
                                            AND V_PDV_VD_TRIBUTOS.CD_FILIAL = PDV_MAPA_RESUMO.CD_FILIAL
                                            AND V_PDV_VD_TRIBUTOS.DT_VD = PDV_MAPA_RESUMO.DT_ECF
                                            AND V_PDV_VD_TRIBUTOS.CD_CX = PDV_MAPA_RESUMO.CD_CX
    WHERE V_PDV_VD_TRIBUTOS.CD_EMP = @CD_EMP
            AND V_PDV_VD_TRIBUTOS.CD_FILIAL = @CD_FILIAL
            AND V_PDV_VD_TRIBUTOS.DT_VD BETWEEN @DT_INI AND @DT_FIM;
 

	EXEC P_PDV_CONSLD_VD_MOVIMENTO_GERACAO_FLAGS @CD_EMP, @CD_FILIAL, @DT_INI , @DT_FIM; 
