IF OBJECT_ID('P_GERA_PDV_MAPA_RESUMO_GERACAO') IS NOT NULL
    BEGIN
	   DROP PROCEDURE P_GERA_PDV_MAPA_RESUMO_GERACAO;
    END;
GO 
/*
=======================================================================================
'Job.................:  
'Função..............: 
'Objetivo............: Gravação Mapa Resumo 
'Tabelas.............: 
'Dt Modificação......: 12/02/2021
'Modificação.........: Tag #A001 Ajuste de arredondamento 
'=======================================================================================
Tag #A001 - Alteração do codigo  
Tag #I001 - Inclusão de novo codigo 
*/


--EXEC P_GERA_PDV_MAPA_RESUMO_GERACAO 1,1,'2018-05-22',1
CREATE PROCEDURE P_GERA_PDV_MAPA_RESUMO_GERACAO (@CD_EMP INT =1, @FILIAIS VARCHAR(8000), @DATAINI DATE, @REFAZER TINYINT )
AS 
set nocount on;

/*****************************************************************************************************************************************/

--Testes

/*
deClare @cd_emp int =1, 
		@Filiais varchar(8000) = '1,10', 
		@DataIni date  ='20180103',  
		@Refazer TinyInt = 0

	/* 
		drop table #TblFilial
		drop table #TblMov 
		drop table #TblMapa
	*/
 
*/
 
/*****************************************************************************************************************************************/
Declare @sCmd Varchar(5000)

    Print 'Inicio da rotina P_GERA_PDV_MAPA_RESUMO_GERACAO - Time : ' + convert(varchar,getdate(),108);
	Print 'Gerando Mapa Resumo com base no movimento de vendas: ' + convert(varchar, @DataIni) 
	
	create table #TblFilial (CD_FILIAL INT) 
	set @sCmd ='insert into #TblFilial 
				SELECT 
					CD_filial
				FROM 
					PRC_FILIAL 
				WHERE
					CD_EMP= ' + convert(varchar, @CD_EMP) + '
					AND STS_FILIAL =0
					AND CD_FILIAL IN ( ' + @Filiais + ')'

	exec (@sCmd);

/*****************************************************************************************************************************************/
	Print 'Carregando movimentos da analise.. - Time : ' + convert(varchar,getdate(),108);
	Create Table #TblMov (		CD_EMP			 INT,
							CD_FILIAL			Int , 
							DT_VD			Datetime, 
							CD_CX			Int, 
							FLAG_NFCE		Tinyint, 
							DTULTMOV		Date,
							CONT_REINICIO	Int,
							CONT_ORD_OP_INI	Int, 
							VLR_GT_INI		Money,
							CONT_RED_Z		Int,
							CD_CTR			Int)

	INSERT INTO #TblMov 	
	SELECT DISTINCT
		PDV_VD.CD_EMP
		,PDV_VD.CD_FILIAL
		, PDV_VD.DT_VD
		, PDV_VD.CD_CX
		, ISNULL(PDV_CX.FLAG_NFCE, 0) AS FLAG_NFCE
		, Null
		, 0 CONT_REINICIO	
		, 0 CONT_ORD_OP_INI	 
		, 0 VLR_GT_INI		
		, 0 CONT_RED_Z		
		, 0 CD_CTR
	FROM
		PDV_VD
		INNER JOIN PDV_CX ON PDV_VD.CD_EMP = PDV_CX.CD_EMP
								AND PDV_VD.CD_FILIAL = PDV_CX.CD_FILIAL
								AND PDV_VD.CD_CX = PDV_CX.CD_CX
		join #TblFilial f on 
			PDV_CX.CD_FILIAL = f.CD_FILIAL
	WHERE  
			PDV_VD.CD_EMP = @CD_EMP
		AND PDV_VD.DT_VD =@DataIni
		AND PDV_CX.EXPORT = 0
	ORDER BY
		PDV_VD.CD_FILIAL
		, PDV_VD.DT_VD;

/*****************************************************************************************************************************************/
-- Valida Datas.. Remove movimentos bloqueados. Contabil e Fiscal
	Print 'Valida Datas.. - Time : ' + convert(varchar,getdate(),108);
	delete from d
	--Select * 
	from 
		#TblMov  d
		join CTB_EXP_CTR E on 
		d.CD_FILIAL = e.CD_FILIAL AND
		d.DT_VD		= e.DT_EXP
	WHERE
		 e.TP_EXP =0 
		 AND ISNULL(e.LIBERADO,'S') = 'N'; 

	delete from d
	--Select * 
	from 
		#TblMov  d
		join CTB_CONTROLE_EXPORTACAO E on 
		d.CD_FILIAL = e.CD_FILIAL AND
		d.DT_VD		= e.DT_EXPORT
	WHERE
		 ISNULL(e.LIBERADO,'S') = 'N';

		
	if @Refazer = 0 --Remove movimentos ja gerados
	begin 
		Print 'Removendo movimentos ja gerados... - Time : ' + convert(varchar,getdate(),108);

		delete from d
		--Select * 
		from 
			#TblMov  d
			join PDV_MAPA_RESUMO E on 
			d.CD_FILIAL			= e.CD_FILIAL AND
			convert (date,d.DT_VD)	= convert (date,e.DT_ECF) and 
			d.CD_CX				= e.CD_CX   
		where 
			e.CD_EMP =@CD_EMP
	end
	else
	begin 
		Print 'Exclui movimentos anteriores ... - Time : ' + convert(varchar,getdate(),108);

		delete from C
		--Select * 
		from 
			#TblMov  d
			join PDV_MAPA_RESUMO E on 
			d.CD_FILIAL			= e.CD_FILIAL AND
			convert (date,d.DT_VD)	= convert (date,e.DT_ECF) and 
			d.CD_CX				= e.CD_CX   
			join PDV_MAPA_RESUMO_CPL C  on 
			e.CD_FILIAL		= C.CD_FILIAL AND
			e.CD_EMP		= C.CD_EMP and 
			e.CD_CTR		= C.CD_CTR  
		where 
			e.CD_EMP =@CD_EMP


		delete from e
		--Select * 
		from 
			#TblMov  d
			join PDV_MAPA_RESUMO E on 
			d.CD_FILIAL			= e.CD_FILIAL AND
			convert (date ,d.DT_VD)	= convert (date ,e.DT_ECF) and 
			d.CD_CX				= e.CD_CX   
		where 
			e.CD_EMP =@CD_EMP

	end  

	if not exists (select * from #TblMov)
	begin 
		Print 'Nenhum movimento pendente .. - Time : ' + convert(varchar,getdate(),108);
		Return
	end
/*****************************************************************************************************************************************/
-- Busca Ultimo Movimento Mapa Resumo
	Print 'Busca ultimo movimento mapa resumo .. - Time : ' + convert(varchar,getdate(),108);
	UPDATE M
	SET 
		DtUltMov = A.DT_ULT_MOV
	FROM      #TblMov m
		JOIN
			(
				SELECT
						m.CD_FILIAL
						, m.DT_VD
						, m.CD_CX
						, MAX(DT_ECF) DT_ULT_MOV
				FROM
					PDV_MAPA_RESUMO v
					JOIN #TblMov m ON v.CD_FILIAL = m.CD_FILIAL
										AND v.DT_ECF < m.DT_VD
										AND v.CD_CX = m.CD_CX
				WHERE  V.CD_EMP = @CD_EMP
				GROUP BY
							m.CD_FILIAL
						, m.DT_VD
						, m.CD_CX
			) A ON A.CD_FILIAL = m.CD_FILIAL
					AND A.DT_VD = m.DT_VD
					AND A.CD_CX = m.CD_CX; 

/*****************************************************************************************************************************************/

	--select * From #TblMov
	--Carrega os contadores inicio
	Print 'Carregando os contadores inicio.. - Time : ' + convert(varchar,getdate(),108);

	UPDATE M 
	SET
		CONT_REINICIO	= v.CONT_REINICIO, 		
		CONT_ORD_OP_INI	= v.CONT_ORD_OP_FIN + 1 , 
		VLR_GT_INI		= v.VLR_GT_FIN,
		CONT_RED_Z		= v.CONT_RED_Z + 1		
	FROM 
		PDV_MAPA_RESUMO v 
		join #TblMov m on 
		v.CD_FILIAL = m.CD_FILIAL AND
		v.DT_ECF	= m.DtUltMov and 
		v.CD_CX		= m.Cd_cx    ;  

	--Gera o Cd_Ctr
	update m
	set
		CD_CTR= T.CD_CTR
	from
	#TblMov m		 
	JOIN (select 
				m.CD_FILIAL,
				m.DT_VD,
				m.CD_CX,
				isnull(v.Cd_ctr,0)+  Row_Number() over (Partition by m.cd_filial order by m.cd_filial, m.dt_vd, m.cd_cx) CD_CTR
			From 
				#TblMov m		 
				left join (SELECT 
								CD_FILIAL,  
								MAX(CD_CTR) CD_CTR 
							FROM PDV_MAPA_RESUMO 
							GROUP BY CD_FILIAL  ) v on 
				v.CD_FILIAL = m.CD_FILIAL) T ON 
		T.CD_FILIAL = m.CD_FILIAL AND
		T.DT_VD		= m.DT_VD	  AND 
		T.CD_CX		= m.CD_CX    ;  

	--select * From #TblMov
	CREATE UNIQUE CLUSTERED INDEX IX_#TblMov ON 
			#TblMov (DT_VD,CD_FILIAL,CD_CX);		

		
/*****************************************************************************************************************************************/
	
	Print 'Gerando o dados dos mapas resumo ' + convert(varchar,getdate(),108)

	Create Table #TblMapa(	CD_EMP INT
							,CD_FILIAL			Int
							,Cd_Ctr				Int
							,CD_CX				Int
							,DT_ECF				Datetime
							,CONT_RED_Z			Int
							,CONT_ORD_OP_INI	Int
							,CONT_ORD_OP_FIN	Int
							,CONT_REINICIO		Int
							,VLR_GT_INI			Money
							,VLR_GT_FIN			Money
							,TIPO_IMPOSTO		Tinyint
							,TX_ICMS_IT			Money
							,VLR_VEND_BRUTA		Money
							,VLR_DESC			Money
							,VLR_CANC			Money
							,VLR_CONTAB			Money
							,VLR_IMPOSTO		Money
							,VLR_ISENTAS		Money
							,VLR_N_TRIB			Money
							,CD_USU				Int
							,VLR_BASE_ICMS_SUBS	Money
							,BASE_ISSQN			Money
							,VLR_TOT_BASE		Money
							,VLR_DESC_ISSQN		Money
							,VLR_CANCEL_ISSQN	Money
							,COO_REDUCAO_Z		Int
							,FLAG_CONFERIDO		Int
							,MAPA_BASE_DADOS	Int
							,VLR_ISENTAS_ISSQN	Money
							,VLR_N_INCIDENCIA_ISSQN	Money
							,FLAG_NFCE				Tinyint)

	DECLARE @cdCx int, @cdFilial int , @DtVd date;
							
	declare c_Chave CURSOR FAST_FORWARD FOR
	SELECT 	
		 CD_CX
		,CD_FILIAL
		,DT_VD
	FROM #TblMov
	
	OPEN c_Chave
	FETCH NEXT FROM c_Chave INTO @cdCx , @cdFilial , @DtVd ;
	WHILE @@FETCH_STATUS = 0
		BEGIN
			Print 'Gravando dados #TblMapa ' + convert(varchar,getdate(),108)
			insert into #TblMapa
				(
	 						/*1*/	 CD_EMP ,CD_FILIAL			,CD_CTR	,CD_CX			,DT_ECF				,CONT_RED_Z			,CONT_ORD_OP_INI	
	 						/*2*/	,CONT_ORD_OP_FIN	,CONT_REINICIO			,VLR_GT_INI			,VLR_GT_FIN			,TIPO_IMPOSTO		,TX_ICMS_IT
	 						/*3*/	,VLR_VEND_BRUTA		,VLR_DESC				,VLR_CANC			,VLR_CONTAB			,VLR_IMPOSTO		,VLR_ISENTAS
	 						/*4*/	,VLR_N_TRIB			,CD_USU					,VLR_BASE_ICMS_SUBS	,BASE_ISSQN			,VLR_TOT_BASE
	 						/*5*/	,VLR_DESC_ISSQN		,VLR_CANCEL_ISSQN		,COO_REDUCAO_Z		,FLAG_CONFERIDO		,MAPA_BASE_DADOS			
	 						/*6*/	,VLR_ISENTAS_ISSQN	,VLR_N_INCIDENCIA_ISSQN	,FLAG_NFCE)
				SELECT		
					 --Line 1
					 V.CD_EMP
					 ,v.CD_FILIAL
					 , m.CD_CTR
					 , v.CD_CX
					 , v.DT_MOV				as DT_ECF				
					 , m.CONT_RED_Z			as CONT_RED_Z
					 , m.CONT_ORD_OP_INI	as CONT_ORD_OP_INI

					 --Line 2  
					 , v.COO_FINAL						as CONT_ORD_OP_FIN
					 , m.CONT_REINICIO					as CONT_REINICIO
					 , m.VLR_GT_INI						as VLR_GT_INI
					 , m.VLR_GT_INI + v.VLR_VENDA_BRUTA as VLR_GT_FIN
					 , v.TIPO_IMPOSTO					as TIPO_IMPOSTO
					 , v.TX_ICMS_IT						as TX_ICMS_IT

					 --Line 3  
					, v.VLR_VENDA_BRUTA					as VLR_VEND_BRUTA
					, ISNULL(v.VLR_DESCONTOS, 0)		as VLR_DESC
					, ISNULL(v.VLR_CANCELAMENTOS, 0)	as VLR_CANC
					, v.VLR_CONTAB						as VLR_CONTAB
					,  0							     as VLR_IMPOSTO
					, ISNULL(v.VLR_ISENTAS, 0)			as VLR_ISENTAS

					--Line 4  
					, ISNULL(v.VLR_N_INCIDENCIA, 0)		as VLR_N_TRIB 
					, 1									as CD_USU
					, ISNULL(v.VLR_SUBST_TRIB, 0)		as VLR_BASE_ICMS_SUBS
					, ISNULL(v.BASE_ISSQN, 0)			as BASE_ISSQN
					, ISNULL(v.VLR_TOT_BASE, 0)			as VLR_TOT_BASE

					--Line 5 
					, ISNULL(v.VLR_DESCONTOS_ISSQN, 0)	as VLR_DESC_ISSQN
					, v.VLR_CANCELAMENTOS_ISSQN			as VLR_CANCEL_ISSQN
					, v.COO_FINAL + 1					as COO_REDUCAO_Z		
					, 0 as FLAG_CONFERIDO	
					, 1 as MAPA_BASE_DADOS		 
		 
					--Line 6
					 , ISNULL(v.VLR_ISENTAS_ISSQN, 0)		as VLR_ISENTAS_ISSQN
					 , ISNULL(v.VLR_N_INCIDENCIA_ISSQN, 0)	as VLR_N_INCIDENCIA_ISSQN
					 , m.FLAG_NFCE as FLAG_NFCE
		
				FROM
					V_PDV_VD_MAPA_RESUMO_CX v
					join #TblMov m on 
					v.CD_FILIAL = m.CD_FILIAL AND
					v.DT_MOV	= m.DT_VD and 
					v.CD_CX		= m.CD_CX   
				Where 
					v.cd_emp =@CD_EMP 
					AND v.dt_mov =@DtVd
					and v.CD_CX	=@cdCx
					and v.cd_filial =@cdFilial ;


			FETCH NEXT FROM c_Chave INTO @cdCx , @cdFilial , @DtVd ;
		END
	CLOSE c_Chave;
	DEALLOCATE c_Chave; 


-- Tag #A001
;WITH TAXAS AS 
(
SELECT DISTINCT
    V.CD_EMP ,
    V.CD_FILIAL,
    V.CD_CX,
    V.DT_VD,
--  ROUND (V.VLR_IMPOSTO,2) AS VLR_IMPOSTO
    V.VLR_IMPOSTO   AS VLR_IMPOSTO
FROM #TBLMAPA MAP
     INNER JOIN V_PDV_VD_TRIBUTOS_TAXAS V ON MAP.CD_EMP = V.CD_EMP
                                             AND MAP.CD_FILIAL = V.CD_FILIAL
                                             AND MAP.CD_CX = V.CD_CX
                                             AND MAP.DT_ECF = V.DT_VD
									and map.tipo_imposto =v.tipo_imposto
WHERE   V.CD_EMP = @CD_EMP
	   AND V.TIPO_IMPOSTO = 0
),IMPOSTO AS (SELECT 
	   	             V.CD_EMP ,
                     V.CD_FILIAL,
                     V.CD_CX,
                     V.DT_VD,
                     --SUM (round (V.VLR_IMPOSTO,2)) AS VLR_IMPOSTO 
					  CAST(SUM (V.VLR_IMPOSTO) AS DECIMAL(14,2)) AS VLR_IMPOSTO 
		        FROM TAXAS V
		       GROUP BY 
		               V.CD_EMP ,
		               V.CD_FILIAL,
		               V.CD_CX,
		               V.DT_VD 
		     )  --SELECT * FROM IMPOSTO

UPDATE M
  SET
      VLR_IMPOSTO =  V.VLR_IMPOSTO
FROM #TBLMAPA M
     INNER JOIN IMPOSTO V ON M.CD_FILIAL = V.CD_FILIAL
                           AND M.CD_CX = V.CD_CX
                           AND M.DT_ECF = V.DT_VD


 

/*****************************************************************************************************************************************/
	Print 'Gravando dados dos mapas resumo ' + convert(varchar,getdate(),108)
	 
	INSERT INTO PDV_MAPA_RESUMO (
		 CD_EMP	,CD_FILIAL	,CD_CTR	,CD_CX			,DT_ECF				,CONT_RED_Z			,CONT_ORD_OP_INI	
		,CONT_ORD_OP_FIN	,CONT_REINICIO			,VLR_GT_INI			,VLR_GT_FIN
		,VLR_VEND_BRUTA		,VLR_DESC				,VLR_CANC			,VLR_CONTAB			,VLR_ICMS			,VLR_ISENTAS
		,VLR_N_TRIB			,DT_CAD					,CD_USU				,VLR_BASE_ICMS_SUBS	,VLR_ISSQN
		,VLR_DESC_ISSQN		,VLR_CANCEL_ISSQN		,COO_REDUCAO_Z		,FLAG_CONFERIDO		,MAPA_BASE_DADOS			
		,VLR_ISENTAS_ISSQN	,VLR_N_INCIDENCIA_ISSQN	,FLAG_NFCE, RECEIPT_URI)

	SELECT DISTINCT
		 @CD_EMP				,CD_FILIAL				,m.CD_CTR			,m.CD_CX				,m.DT_ECF		, m.CONT_RED_Z , m.CONT_ORD_OP_INI	
		,m.CONT_ORD_OP_FIN		,CONT_REINICIO			,m.VLR_GT_INI		,m.VLR_GT_FIN
		,m.VLR_VEND_BRUTA		,VLR_DESC				,m.VLR_CANC			,m.VLR_CONTAB			, 
		(ISNULL(m.VLR_IMPOSTO, 0)) as VLR_ICMS, m.VLR_ISENTAS
		,m.VLR_N_TRIB			,GetDate()				,m.CD_USU			,m.VLR_BASE_ICMS_SUBS	, 
		(ISNULL(m.BASE_ISSQN, 0)) as VLR_ISSQN	
		,m.VLR_DESC_ISSQN		,VLR_CANCEL_ISSQN		,m.COO_REDUCAO_Z	,m.FLAG_CONFERIDO		, m.MAPA_BASE_DADOS			
		,m.VLR_ISENTAS_ISSQN	,VLR_N_INCIDENCIA_ISSQN	,m.FLAG_NFCE, 'P_GERA_PDV_MAPA_RESUMO_GERACAO'
	FROM 
		#TblMapa m
 
/*****************************************************************************************************************************************/
--DesbloqueioAutomaticoFechaCaixa = val(BuscaParametroSistemas(Me.Name, "FLAG_DESBLOQUEIO_FECHA_CAIXA_EXC_MP_RESUMO", Chave_Empresa))
	
	IF Exists(select * from prc_emp_config
			where cd_emp=@CD_EMP
				and CD_CHAVE= 'FLAG_DESBLOQUEIO_FECHA_CAIXA_EXC_MP_RESUMO'
				and Valor ='1')
	begin 
		Print 'Desbloqueio automatico fecha caixa - Time ' + convert(varchar,getdate(),108)

		UPDATE V
		SET
			STS_MOV=1
		FROM 
			PDV_MAPA_RESUMO_AUDIT_EXCLUSAO v
			join #TblMov m on 
			v.CD_FILIAL = m.CD_FILIAL AND
			v.DT_MOV	= m.DT_VD; 
				
		UPDATE V
		SET
			BLQ_ALT_FECHA=1
		FROM 
			PDV_VD_FECHA_MOV v
			join #TblMov m on 
			v.CD_FILIAL = m.CD_FILIAL AND
			v.DT_MOV	= m.DT_VD 
		WHERE
			BLQ_ALT_FECHA=0 ;
	end

	
/*****************************************************************************************************************************************/
--ExecutarProcessamentoMapaResumoConsolidacao

	INSERT INTO PDV_MAPA_RESUMO_CPL (CD_EMP,CD_FILIAL,CD_CTR,TX_ICMS
									,BASE_CALC,TIPO_IMPOSTO,CD_TOTALIZADOR_PARCIAL)
	
	SELECT  
		@CD_EMP
		,m.CD_FILIAL
		,m.CD_CTR
		,m.TX_ICMS_IT
		,m.VLR_TOT_BASE
		,m.TIPO_IMPOSTO
		,Row_Number() over(Partition By CD_ctr, Tipo_imposto order by CD_CTR ,Tipo_imposto ) -1
	FROM 
		#Tblmapa m

	--select * from #tblmapa where cd_ctr =4970
/*****************************************************************************************************************************************/
--ExecutarProcessamentoMapaResumoConsolidacao

    Print 'Executar processamento mapa resumo consolidacao - Time ' + convert(varchar,getdate(),108)
	DECLARE @CodFilial int,
			@Data Date  
							
	declare cFilial CURSOR FAST_FORWARD FOR
	select distinct 
		cd_filial,
		Dt_Vd
	from 
		#TblMov 

	OPEN cFilial
	FETCH NEXT FROM cFilial INTO @CodFilial , @Data
	WHILE @@FETCH_STATUS = 0
		BEGIN
			PRINT 'Procesando filial ' + CONVERT(VARCHAR, @CodFilial ) + ' dia ' + convert(Varchar, @Data ) + ' - Time ' + convert(varchar,getdate(),108)

			EXECUTE P_PDV_CONSLD_VD_MOVIMENTO_GERACAO @Cd_emp,@CodFilial,@Data,@Data;
			FETCH NEXT FROM cFilial INTO @CodFilial ,@Data
		END

	CLOSE cFilial;
	DEALLOCATE cFilial; 
		
/*****************************************************************************************************************************************/		