IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[P_FECHA_VENDA_CONVENIO]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[P_FECHA_VENDA_CONVENIO]
GO

--Procedure para fechar as venda do conv?nio com a data de fechamento ou agendamento do conv?nio
-- EXEC P_FECHA_VENDA_CONVENIO
CREATE PROCEDURE [dbo].[P_FECHA_VENDA_CONVENIO] AS
	SET NOCOUNT ON;


	DECLARE @DT_MES_ANT					AS DATETIME
	DECLARE @DT_MES_ATU					AS DATETIME
	DECLARE @FLAG_FECHA_CONV_AUTOMATICO AS INT
	DECLARE @FLAG_IG_FECHA_CONV_PBM_AUTOMATICO AS INT
	DECLARE @ULTDIAMES INT
	DECLARE @FLAG_FECHA_CONV_GRUPO_ECONOMICO AS VARCHAR
	DECLARE @DT_MES_SEG                        AS DATETIME


	CREATE TABLE #RS_DT_FECH (
			CD_EMP INT, 
			CD_CONV INT, 
			DT_FECH_MES_ANT VARCHAR(15), 
			DT_FECH			VARCHAR(15)
			PRIMARY KEY (CD_EMP, CD_CONV))


	SET @DT_MES_ANT = DATEADD(MONTH,-1,GETDATE())
	SET @DT_MES_ATU = GETDATE()
	SET @DT_MES_SEG = DATEADD(MONTH,+ 1,GETDATE())
	SET @FLAG_FECHA_CONV_AUTOMATICO = ISNULL((SELECT VALOR 
									   FROM PRC_EMP_CONFIG 
									   WHERE CD_EMP = 1
											AND CD_CHAVE = 'FLAG_FECHA_CONV_AUTOMATICO'),0)
	--Pega flag de fechamento de convenio pbm
	SET @FLAG_IG_FECHA_CONV_PBM_AUTOMATICO = ISNULL((SELECT VALOR 
									   FROM PRC_EMP_CONFIG 
									   WHERE CD_EMP = 1
											AND CD_CHAVE = 'IGNORAR_FECHAMENTO_AUTOMATICO_CONVENIO_PBM'),0)
	SET @ULTDIAMES = (select convert(char(10),dbo.F_FINAL_MES(@DT_MES_ANT),112))
	-- Pegar c?digo dos grupos econ?mico que est?o definidos na flag
	SET @FLAG_FECHA_CONV_GRUPO_ECONOMICO = ISNULL((SELECT VALOR 
									   FROM PRC_EMP_CONFIG 
									   WHERE CD_EMP = 1
											AND CD_CHAVE = 'FLAG_FECHA_CONV_GRUPO_ECONOMICO'),0)


	CREATE TABLE #tblGrpEcon(Cd_Emp int, Cd_Filial int)

	insert into #tblGrpEcon
	select 
		f.Cd_Emp,
		f.Cd_Filial
	from 
		PRC_GRP_ECON_PRC_FILIAL F 
	where
		f.cd_emp=1
		and F.CD_GRP_ECON NOT IN (SELECT ID FROM UFD_PRM_IN_STRING(@FLAG_FECHA_CONV_GRUPO_ECONOMICO));

---------------------------------------------------------------------------------------------------------
--Capturando as Datas de fechamento do conv?nio
---------------------------------------------------------------------------------------------------------
	PRINT 'BUSCANDO CONVENIO PARA FECHAMENTO'
	INSERT INTO #RS_DT_FECH
	SELECT
		DISTINCT 
		RC_CLI_CONV.CD_EMP,
		RC_CLI_CONV.CD_CONV,
		CAST(YEAR(@DT_MES_ANT) AS VARCHAR) + 
			RIGHT('0' + CAST(MONTH(@DT_MES_ANT) AS VARCHAR),2) + 
			RIGHT('0' +CAST(RC_CLI_CONV.DIA_FAT AS VARCHAR),2) AS DT_FECH_MES_ANT,

		(CASE WHEN RC_CLI_CONV.FLAG_FECHA_PERIODO=0 THEN
			(CASE WHEN RC_CLI_CONV.DIA_FAT >= DAY(GETDATE()) THEN
				CAST(YEAR(@DT_MES_ANT) AS VARCHAR) + 
					RIGHT('0' + CAST(MONTH(@DT_MES_ANT) AS VARCHAR),2) + 
					RIGHT('0' + CAST(CASE WHEN RC_CLI_CONV.DIA_FAT = 31 THEN @ULTDIAMES ELSE RC_CLI_CONV.DIA_FAT END AS VARCHAR),2)		
			 ELSE
				CAST(YEAR(@DT_MES_ATU) AS VARCHAR) + 
					RIGHT('0' + CAST(MONTH(@DT_MES_ATU) AS VARCHAR),2) + 
					RIGHT('0' +CAST(CASE WHEN RC_CLI_CONV.DIA_FAT = 31 THEN @ULTDIAMES ELSE RC_CLI_CONV.DIA_FAT END AS VARCHAR),2)END)			
		 ELSE
			(CASE WHEN RC_CLI_CONV.DIA_FAT >= DAY(GETDATE()) THEN
				CAST(YEAR(@DT_MES_ANT) AS VARCHAR) + 
					RIGHT('0' + CAST(MONTH(@DT_MES_ANT) AS VARCHAR),2) + 
					RIGHT('0' + CAST(CASE WHEN RC_CLI_CONV.DIA_FAT = 31 THEN @ULTDIAMES ELSE RC_CLI_CONV.DIA_FAT END AS VARCHAR),2)
			WHEN RC_CLI_CONV.DIA_FAT2 >= DAY(GETDATE()) THEN
				CAST(YEAR(@DT_MES_ATU) AS VARCHAR) + 
					RIGHT('0' + CAST(MONTH(@DT_MES_ATU) AS VARCHAR),2) + 
					RIGHT('0' +CAST(CASE WHEN RC_CLI_CONV.DIA_FAT2 = 31 THEN @ULTDIAMES ELSE RC_CLI_CONV.DIA_FAT2 END AS VARCHAR),2)	
			WHEN RC_CLI_CONV.DIA_FAT3 >= DAY(GETDATE()) THEN
				CAST(YEAR(@DT_MES_ATU) AS VARCHAR) + 
					RIGHT('0' + CAST(MONTH(@DT_MES_ATU) AS VARCHAR),2) + 
					RIGHT('0' +CAST(CASE WHEN RC_CLI_CONV.DIA_FAT3 = 31 THEN @ULTDIAMES ELSE RC_CLI_CONV.DIA_FAT3 END AS VARCHAR),2)	
			else
				CAST(YEAR(@DT_MES_ATU) AS VARCHAR) + 
					RIGHT('0' + CAST(MONTH(@DT_MES_SEG) AS VARCHAR),2)  + 
					RIGHT('0' + CAST(CASE WHEN RC_CLI_CONV.DIA_FAT = 31 THEN @ULTDIAMES ELSE RC_CLI_CONV.DIA_FAT END AS VARCHAR),2)
			END)
		 END) AS DT_FECH
	FROM         	
		RC_CLI_CONV 
	WHERE     
 		 RC_CLI_CONV.DIA_FAT > 0
 		 AND  RC_CLI_CONV.TP_CONV = (CASE WHEN @FLAG_IG_FECHA_CONV_PBM_AUTOMATICO = 1 THEN 0 ELSE RC_CLI_CONV.TP_CONV END)

/*
	UPDATE A
	  SET
		  A.DT_FECH = CONVERT(VARCHAR, DT_FECH_AGD, 112)
	FROM #RS_DT_FECH A
		 INNER JOIN RC_CLI_CONV_AGD_FECH ON A.cd_emp = RC_CLI_CONV_AGD_FECH.cd_emp
											AND A.cd_conv = RC_CLI_CONV_AGD_FECH.cd_conv
											AND RC_CLI_CONV_AGD_FECH.sts_agd = 0;
*/
---------------------------------------------------------------------------------------------------------
--Alterando as datas de fechamento para as data agendadas
---------------------------------------------------------------------------------------------------------
	PRINT 'ALTERANDO DATA DE FECHAMENTO'
	UPDATE fech
	SET
		fech.DT_FECH = (CASE WHEN CONVERT(CHAR(10), agd_fech.DT_FECH_AGD, 112) >= CONVERT(CHAR(10), @DT_MES_ATU, 112) THEN CONVERT(CHAR(10), fech.DT_FECH_MES_ANT, 112)
								ELSE CONVERT(CHAR(10), agd_fech.DT_FECH_AGD, 112)
								END)
	FROM 
		#RS_DT_FECH fech
		INNER JOIN RC_CLI_CONV_AGD_FECH agd_fech ON 
			agd_fech.CD_EMP = fech.CD_EMP
			AND agd_fech.CD_CONV = fech.CD_CONV
			AND (LEFT(CONVERT(CHAR(10), agd_fech.DT_FECH_CONV, 112), 6) = LEFT(CONVERT(CHAR(10), @DT_MES_ATU, 112), 6)
			OR LEFT(CONVERT(CHAR(10), agd_fech.DT_FECH_CONV, 112), 6) = LEFT(CONVERT(CHAR(10), fech.DT_FECH, 112), 6))
			AND agd_fech.sts_agd = 0; 

	--Remove convenios que tiveram fechamento anterior por agendamento, no mes referencia
	DELETE FROM fech
	FROM 
		#RS_DT_FECH fech
		INNER JOIN RC_CLI_CONV_AGD_FECH agd_fech ON 
		agd_fech.CD_EMP = fech.CD_EMP
		AND agd_fech.CD_CONV = fech.CD_CONV
		AND (LEFT(CONVERT(CHAR(10), agd_fech.DT_FECH_CONV, 112), 6) = LEFT(CONVERT(CHAR(10), @DT_MES_ATU, 112), 6)
					OR LEFT(CONVERT(CHAR(10), agd_fech.DT_FECH_CONV, 112), 6) = LEFT(CONVERT(CHAR(10), fech.DT_FECH, 112), 6))
		AND agd_fech.sts_agd = 1; 

---------------------------------------------------------------------------------------------------------
--Realizando os fechamento dos convenios em aberto
---------------------------------------------------------------------------------------------------------
	PRINT 'FECHANDO VENDAS DO CONVENIO'

	--Tbl temporaria para colocar em pendencia..
	select
		distinct
		 c.CD_EMP
		,c.CD_CLI
		,c.CD_CONV
		,getdate() DT_CAD
		into #TmpPendencia
	FROM         
		RC_VD_CONV c 
		INNER JOIN #RS_DT_FECH fech ON 
			c.CD_EMP = fech.CD_EMP AND 
			c.CD_CONV = fech.CD_CONV
		JOIN #tblGrpEcon e ON 
			c.CD_EMP = e.CD_EMP AND 
			c.CD_FILIAL = e.CD_FILIAL	
	WHERE    
		 c.ST_FECHVD_CONV = 0  
		 AND  CONVERT(VARCHAR,c.DT_FECHA,112) <= fech.DT_FECH
		 AND c.CD_EMP = 1	 

---------------------------------------------------------------------------------------------------------
	--Remove vendas de clientes/Convenio sem vinculo
	delete from p
	from 
		#TmpPendencia	 p
		left join RC_CLI_CONV_RC_CLI  c on 
		p.cd_emp	= c.cd_emp and
		p.cd_cli	= c.cd_cli and 
		p.cd_conv	= c.cd_conv
	where	
		c.cd_emp is  null;

---------------------------------------------------------------------------------------------------------

	UPDATE c
	SET
		  ST_FECHVD_CONV = (CASE
								WHEN @FLAG_FECHA_CONV_AUTOMATICO = 1 THEN 1
								ELSE 5
							END)
		, DT_ATUAL = GETDATE()
	FROM 
		RC_VD_CONV c
		join RC_CLI_CONV_RC_CLI  Cli on /* Apenas vendas que possuem clientes cadastrados */
		c.cd_emp	= Cli.cd_emp and
		c.cd_cli	= Cli.cd_cli and 
		c.cd_conv	= Cli.cd_conv

		INNER JOIN #RS_DT_FECH fech ON c.CD_EMP = fech.CD_EMP
										AND c.CD_CONV = fech.CD_CONV
		JOIN #tblGrpEcon e ON 
			c.CD_EMP = e.CD_EMP AND 
			c.CD_FILIAL = e.CD_FILIAL
	WHERE
		  c.ST_FECHVD_CONV = 0
		  AND CONVERT(VARCHAR, c.DT_FECHA, 112) <= fech.DT_FECH
		  AND c.CD_EMP = 1;
	 
---------------------------------------------------------------------------------------------------------
--Insere a pendencia de revisao de saldo
---------------------------------------------------------------------------------------------------------

	insert into RC_CLI_CONV_RC_CLI_SALDO_PENDENTE(CD_EMP,CD_CLI,CD_CONV,DT_CAD)
	select
		distinct
		 c.CD_EMP
		,c.CD_CLI
		,c.CD_CONV
		,getdate() DT_CAD
	FROM         
		#TmpPendencia c
		left join RC_CLI_CONV_RC_CLI_SALDO_PENDENTE p on 
			c.CD_EMP  = p.CD_EMP AND 
			c.CD_CLI  = p.CD_CLI AND 
			c.CD_CONV = p.CD_CONV 
	where p.cd_emp is null;
	 
---------------------------------------------------------------------------------------------------------
--Confirmando o Status de agendamento efetivado
---------------------------------------------------------------------------------------------------------
	PRINT 'ALTERANDO O STATUS DO AGENDAMENTO DE VENDAS';
	UPDATE RC_CLI_CONV_AGD_FECH
	SET STS_AGD = 1
	WHERE
		STS_AGD = 0
		AND CONVERT(CHAR(10), DT_FECH_AGD, 101) < CONVERT(CHAR(10), GETDATE(), 101);

	DROP TABLE #RS_DT_FECH;

GO


