IF OBJECT_ID('P_ATUALIZA_SALDO_CONVENIADO') IS NOT NULL
	BEGIN
		DROP PROCEDURE P_ATUALIZA_SALDO_CONVENIADO
	END
 
GO 
/*
=======================================================================================
'Job.................:  
'Função..............: 
'Objetivo............: Atualiza saldo conveniado 
'Tabelas.............: 
'Dt Modificação......: 26/02/2021
'Modificação.........: Tag #A001 Remover alteração no STS_CRED da RC_CLI
'=======================================================================================
Tag #A001 - Alteração do codigo  
Tag #I001 - Inclusão de novo codigo 
*/
-- exec P_ATUALIZA_SALDO_CONVENIADO
CREATE PROCEDURE [dbo].[P_ATUALIZA_SALDO_CONVENIADO] (@CD_CLI_ESP INT = 0) 
 AS
  
	SET NOCOUNT ON;

	CREATE TABLE #RS_SLD (CD_EMP INT, CD_CLI INT,CD_CONV INT, VLR_SLD MONEY,VLR_CRED MONEY
							  PRIMARY KEY (CD_EMP, CD_CLI, CD_CONV))

	DECLARE @CD_EMP INT=1
	DECLARE @CD_CLI INT
	DECLARE @CD_CONV INT
	DECLARE @VLR_SLD MONEY
	DECLARE @CTR_PARCELAMENTO INT
	DECLARE @VLR_CRED MONEY
	DECLARE @NAO_CTR_MULTIPOS_CONVENIOS INT
	DECLARE @VLR_DEB_FUT MONEY
	DECLARE @VLR_DEB MONEY
	DECLARE @FLAG_PERMITE_PRORROGAR_FECHAMENTO_CONVENIO INT 

	UPDATE
		RC_CLI_FIDELI_CARTAO_SOLIC
	SET 
		RC_CLI_FIDELI_CARTAO_SOLIC.STS_SOLIC=1
	FROM
		RC_CLI_FIDELI_CARTAO_SOLIC 
		INNER JOIN RC_CLI_FIDELI_CARTAO_CONF_SOLIC ON
		RC_CLI_FIDELI_CARTAO_SOLIC.CD_SOLIC = RC_CLI_FIDELI_CARTAO_CONF_SOLIC.CD_SOLIC
		INNER JOIN RC_CLI_FIDELI_CARTAO_CONF ON
		RC_CLI_FIDELI_CARTAO_CONF_SOLIC.CD_CONF = RC_CLI_FIDELI_CARTAO_CONF.CD_CONF 
	WHERE
		RC_CLI_FIDELI_CARTAO_SOLIC.STS_SOLIC=0;
	
	IF @CD_CLI_ESP > 0
    BEGIN

		print 'Insere o cliente especificado na pendencia ' + CONVERT(VARCHAR, GETDATE(), 108); 
 		IF NOT EXISTS( SELECT * FROM RC_CLI_CONV_RC_CLI_SALDO_PENDENTE WHERE CD_EMP = @CD_EMP
																			AND CD_CLI = @CD_CLI_ESP )
		BEGIN
			INSERT INTO RC_CLI_CONV_RC_CLI_SALDO_PENDENTE( CD_EMP, CD_CLI , CD_CONV , DT_CAD)
               SELECT
                      CD_EMP
                    , CD_CLI
                    , CD_CONV
                    , GETDATE() DT_CAD
               FROM
                    RC_CLI_CONV_RC_CLI
               WHERE cd_emp = @CD_EMP
                     AND cd_cli = @CD_CLI_ESP;
		END
    END

	print 'P_ATUALIZA_SALDO_CONVENIADO - SELECT 1 ' + CONVERT(VARCHAR, GETDATE(), 108);

	if exists (SELECT TOP 1 cd_emp 
				FROM RC_VD_CONV 
				WHERE CD_CONV IS NULL)
		begin
			print 'UPDATE 2 ' + CONVERT(VARCHAR, GETDATE(), 108);
			UPDATE RC_VD_CONV 
				SET CD_CONV =0 WHERE CD_CONV IS NULL;
		end

		SET @CTR_PARCELAMENTO = ISNULL((SELECT 
											VALOR
										FROM	
											PRC_EMP_CONFIG
										WHERE
											PRC_EMP_CONFIG.CD_CHAVE = 'FLAG_CONTROLE_SALDO_CONVENIADO_PARCELAMENTO'),0);

		SET @NAO_CTR_MULTIPOS_CONVENIOS = ISNULL((SELECT 
											VALOR
										FROM	
											PRC_EMP_CONFIG
										WHERE
											PRC_EMP_CONFIG.CD_CHAVE = 'FLAG_NAO_CONTROLE_MULTIPLOS_CONVENIOS'),0);
	
	IF @CTR_PARCELAMENTO = 0
	BEGIN
		
		--Buscando o saldo de vendas do clientes conveniados
		print 'INSERT 3 ' + CONVERT(VARCHAR, GETDATE(), 108);
		INSERT INTO #RS_SLD
			SELECT
					CD_EMP
				, CD_CLI
				, ISNULL(CD_CONV, 0) AS CD_CONV
				, SUM(VLR_CONV) AS VLR_CONV
				, SUM(VLR_CRED) AS VLR_CRED
			FROM
				(
					SELECT
							RC_VD_CONV.CD_EMP
							, RC_VD_CONV.CD_CLI
							, RC_VD_CONV.CD_CONV
							, SUM(RC_VD_CONV.VLR_CONV) AS VLR_CONV
							, SUM(ISNULL(VLR_CRED, 0)) AS VLR_CRED
					FROM
							RC_VD_CONV
							INNER JOIN RC_CLI_CONV_RC_CLI_SALDO_PENDENTE ON RC_VD_CONV.CD_EMP = RC_CLI_CONV_RC_CLI_SALDO_PENDENTE.CD_EMP
																			AND RC_VD_CONV.CD_CLI = RC_CLI_CONV_RC_CLI_SALDO_PENDENTE.CD_CLI
																			AND RC_VD_CONV.CD_CONV = RC_CLI_CONV_RC_CLI_SALDO_PENDENTE.CD_CONV
							LEFT JOIN PDV_VD_DEV_NCC_LANC_RC_VD_CONV_CRED NCC_CRED ON NCC_CRED.CD_CTR = RC_VD_CONV.CD_CTR
							LEFT JOIN RC_VD_CONV_CRED ON RC_VD_CONV_CRED.CD_CTR_CRED = NCC_CRED.CD_CTR_CRED
														AND RC_VD_CONV_CRED.CD_CTR = NCC_CRED.CD_CTR
					WHERE RC_VD_CONV.CD_EMP = 1
							AND RC_VD_CONV.ST_FECHVD_CONV = 0
							AND RC_VD_CONV.CD_CLI = CASE
														WHEN @CD_CLI_ESP = 0 THEN RC_VD_CONV.CD_CLI
														ELSE @CD_CLI_ESP
													END
							AND NOT EXISTS (SELECT CD_EMP FROM
														RC_CLI_CONV_DEPENDENTE
												WHERE RC_VD_CONV.CD_EMP = RC_CLI_CONV_DEPENDENTE.CD_EMP
														AND RC_VD_CONV.CD_CLI = RC_CLI_CONV_DEPENDENTE.CD_CLI_DEPEND)
					GROUP BY
								RC_VD_CONV.CD_EMP
							, RC_VD_CONV.CD_CLI
							, RC_VD_CONV.CD_CONV
					UNION ALL
					SELECT
							RC_VD_CONV.CD_EMP
							, RC_CLI_CONV_DEPENDENTE.CD_CLI_TITULAR
							, RC_VD_CONV.CD_CONV
							, SUM(RC_VD_CONV.VLR_CONV)
							, SUM(ISNULL(VLR_CRED, 0)) AS VLR_CRED
					FROM
							RC_VD_CONV
							INNER JOIN RC_CLI_CONV_RC_CLI_SALDO_PENDENTE ON RC_VD_CONV.CD_EMP = RC_CLI_CONV_RC_CLI_SALDO_PENDENTE.CD_EMP
																			AND RC_VD_CONV.CD_CLI = RC_CLI_CONV_RC_CLI_SALDO_PENDENTE.CD_CLI
																			AND RC_VD_CONV.CD_CONV = RC_CLI_CONV_RC_CLI_SALDO_PENDENTE.CD_CONV
							INNER JOIN RC_CLI_CONV_DEPENDENTE ON RC_VD_CONV.CD_EMP = RC_CLI_CONV_DEPENDENTE.CD_EMP
																AND RC_VD_CONV.CD_CLI = RC_CLI_CONV_DEPENDENTE.CD_CLI_DEPEND
							LEFT JOIN PDV_VD_DEV_NCC_LANC_RC_VD_CONV_CRED NCC_CRED ON NCC_CRED.CD_CTR = RC_VD_CONV.CD_CTR
							LEFT JOIN RC_VD_CONV_CRED ON RC_VD_CONV_CRED.CD_CTR_CRED = NCC_CRED.CD_CTR_CRED
														AND RC_VD_CONV_CRED.CD_CTR = NCC_CRED.CD_CTR
					WHERE RC_VD_CONV.CD_EMP = 1
							AND RC_VD_CONV.ST_FECHVD_CONV = 0
							AND RC_VD_CONV.CD_CLI = CASE
														WHEN @CD_CLI_ESP = 0 THEN RC_VD_CONV.CD_CLI
														ELSE @CD_CLI_ESP
													END
					GROUP BY
								RC_VD_CONV.CD_EMP
							, RC_CLI_CONV_DEPENDENTE.CD_CLI_TITULAR
							, RC_VD_CONV.CD_CONV
					UNION ALL
					SELECT
							RC_CLI_CONV_RC_CLI_SALDO_PENDENTE.CD_EMP
							, RC_CLI_CONV_RC_CLI_SALDO_PENDENTE.CD_CLI
							, RC_CLI_CONV_RC_CLI.CD_CONV
							, 0 AS VLR_CONV
							, 0 AS VLR_CRED
					FROM
							RC_CLI_CONV_RC_CLI_SALDO_PENDENTE
							INNER JOIN RC_CLI_CONV_RC_CLI ON RC_CLI_CONV_RC_CLI_SALDO_PENDENTE.CD_EMP = RC_CLI_CONV_RC_CLI.CD_EMP
															AND RC_CLI_CONV_RC_CLI_SALDO_PENDENTE.CD_CLI = RC_CLI_CONV_RC_CLI.CD_CLI
															AND RC_CLI_CONV_RC_CLI_SALDO_PENDENTE.CD_CONV = RC_CLI_CONV_RC_CLI.CD_CONV
					WHERE RC_CLI_CONV_RC_CLI.CD_CLI = CASE
															WHEN @CD_CLI_ESP = 0 THEN RC_CLI_CONV_RC_CLI.CD_CLI
															ELSE @CD_CLI_ESP
														END
							AND NOT EXISTS (SELECT CD_EMP FROM
														RC_CLI_CONV_DEPENDENTE
												WHERE RC_CLI_CONV_RC_CLI_SALDO_PENDENTE.CD_EMP = RC_CLI_CONV_DEPENDENTE.CD_EMP
														AND RC_CLI_CONV_RC_CLI_SALDO_PENDENTE.CD_CLI = RC_CLI_CONV_DEPENDENTE.CD_CLI_DEPEND
														AND RC_CLI_CONV_RC_CLI_SALDO_PENDENTE.CD_CONV = RC_CLI_CONV_DEPENDENTE.CD_CONV
											)
					) AS VENDAS
			GROUP BY
					CD_EMP
					, CD_CLI
					, CD_CONV;


		-----------------------------------------------------------------------------------
		--Cliente com solicitacao que nao possui vendas no periodo
		print 'INSERT 4 ' + CONVERT(VARCHAR, GETDATE(), 108);
		INSERT INTO #RS_SLD
		SELECT
			P.CD_EMP, 
			P.CD_CLI,
			P.CD_CONV,
			0,
			0
			FROM
			RC_CLI_CONV_RC_CLI_SALDO_PENDENTE P LEFT OUTER JOIN  #RS_SLD S ON
			P.CD_EMP = S.CD_EMP AND
			P.CD_CLI = S.CD_CLI AND
			P.CD_CONV = S.CD_CONV 
			WHERE
			S.CD_CONV IS NULL
			AND P.cd_emp=@cd_emp
			AND P.CD_CLI = CASE WHEN @CD_CLI_ESP =0 
															THEN P.CD_CLI 
															ELSE @CD_CLI_ESP 
														END

		PRINT @NAO_CTR_MULTIPOS_CONVENIOS
		--Agrupando o convenio em caso de nao usar multiplos convenios
		IF @NAO_CTR_MULTIPOS_CONVENIOS=1
		begin
			print 'SELECT 5 ' + CONVERT(VARCHAR, GETDATE(), 108);
			SELECT
				c.CD_EMP,
				c.CD_CLI,
				v.CD_CONV,
				SUM(c.VLR_SLD) AS VLR_SLD,
				ISNULL(SUM(c.VLR_CRED),0) AS VLR_CRED
				INTO #RS_SLD_TMP
			FROM
				#RS_SLD c INNER JOIN RC_CLI_CONV_RC_CLI v ON
				c.CD_EMP = v.CD_EMP AND
				c.CD_CLI = v.CD_CLI AND
				C.CD_CONV=V.CD_CONV
			GROUP BY
				c.CD_EMP,
				c.CD_CLI,
				v.CD_CONV
			    
			--Limpando tabela saldo
			TRUNCATE TABLE	#RS_SLD
				
			--Devolvendo as vendas agrupadas
			print 'INSERT 6 ' + CONVERT(VARCHAR, GETDATE(), 108);
			INSERT INTO #RS_SLD
			SELECT
				CD_EMP,
				CD_CLI,
				CD_CONV,
				VLR_SLD,
				VLR_CRED
			FROM
				#RS_SLD_TMP

			DROP TABLE #RS_SLD_TMP
		end
			 
		DECLARE ConvenioSaldo CURSOR FAST_FORWARD FOR 
		SELECT DISTINCT 
			CD_EMP, 
			CD_CLI, 
			CD_CONV, 
			VLR_SLD,
			VLR_CRED
		FROM #RS_SLD		
									
		OPEN ConvenioSaldo			
		FETCH NEXT FROM ConvenioSaldo INTO @CD_EMP, @CD_CLI, @CD_CONV, @VLR_SLD,@VLR_CRED
		WHILE @@FETCH_STATUS=0
			
			Begin
					
				PRINT 'Atualizando o cliente ' +convert(varchar,@CD_CLI) + ' Conv.: ' + convert(varchar,@CD_CONV) + ' - '   + CONVERT(VARCHAR, GETDATE(), 108);

				SET @FLAG_PERMITE_PRORROGAR_FECHAMENTO_CONVENIO = ISNULL((SELECT VALOR  FROM PRC_EMP_CONFIG WHERE CD_CHAVE = 'FLAG_PERMITE_PRORROGAR_FECHAMENTO_CONVENIO'),0);
				SET @VLR_DEB_FUT = 0 	
					IF @FLAG_PERMITE_PRORROGAR_FECHAMENTO_CONVENIO = 1 					
						BEGIN 
							/* débito 'futuro' */
							SET @VLR_DEB_FUT = ISNULL((SELECT SUM(A.VLR_CONV) AS VLR_DEB_FUT
														 FROM RC_VD_CONV A
															  INNER JOIN RC_CLI_CONV B ON A.CD_EMP  = B.CD_EMP
																					  AND A.CD_CONV = B.CD_CONV
														WHERE A.CD_EMP = @CD_EMP
														  AND A.CD_CLI = @CD_CLI
														  AND A.CD_CONV = @CD_CONV
														  AND A.DT_FECHA > B.DT_PROX_FECHAMENTO
														  AND A.ST_FECHVD_CONV = 0), 0);
	
						END							  
	
	
				SET @VLR_DEB = @VLR_SLD - @VLR_CRED - @VLR_DEB_FUT;
	
				UPDATE C 
				   SET VLR_DEB_FUTURO 	= @VLR_DEB_FUT, 
					   VLR_DEB 		= (@VLR_SLD -@VLR_CRED) - @VLR_DEB_FUT,
					   DT_ULT_ALT = GETDATE(), 
					   CD_USU_ALT = 1
				  FROM RC_CLI_CONV_RC_CLI C
				 WHERE C.CD_EMP  = @CD_EMP
				   AND C.CD_CLI  = @CD_CLI
				   AND C.CD_CONV = @CD_CONV
				   AND (VLR_DEB <> @VLR_DEB OR VLR_DEB_FUTURO <> @VLR_DEB_FUT); 
						
							
			
				------------------------------------------------------------------------------------------------------

				DELETE FROM RC_CLI_CONV_RC_CLI_SALDO_PENDENTE
				WHERE
				CD_EMP = @CD_EMP
				AND CD_CLI = @CD_CLI
				AND CD_CONV = @CD_CONV;
				------------------------------------------------------------------------------------------------------
 
		
				--Proximo registro
				FETCH NEXT FROM ConvenioSaldo INTO @CD_EMP, @CD_CLI, @CD_CONV, @VLR_SLD,@VLR_CRED
			End
			 
		CLOSE ConvenioSaldo; 
		DEALLOCATE ConvenioSaldo; 
	end

/**************************************************************************************************************************/
--BLOQUEANDO CONVENIADO PENDENTES 
/**************************************************************************************************************************/

    CREATE TABLE #RsDemiteProv(CD_CONV_DEMITE INT, CD_CONV INT, CD_EMP INT, CD_CLI INT)
    INSERT INTO #RsDemiteProv
    SELECT
		  WEB_CONV_DEMITE.CD_CONV_DEMITE,
		  WEB_CONV_DEMITE.CD_CONV,
		  WEB_CONV_DEMITE_RC_CLI.CD_EMP,
		  WEB_CONV_DEMITE_RC_CLI.CD_CLI 
    FROM
		  WEB_CONV_DEMITE INNER JOIN WEB_CONV_DEMITE_RC_CLI ON
		  WEB_CONV_DEMITE.CD_CONV_DEMITE = WEB_CONV_DEMITE_RC_CLI.CD_CONV_DEMITE
    WHERE
		  WEB_CONV_DEMITE.FLAG_PROCESSADO=0
		  AND WEB_CONV_DEMITE_RC_CLI.CD_CLI = CASE WHEN @CD_CLI_ESP =0 
															 THEN WEB_CONV_DEMITE_RC_CLI.CD_CLI 
															 ELSE @CD_CLI_ESP 
														 END;



    print 'UPDATE 9 ' + CONVERT(VARCHAR, GETDATE(), 108);
    DECLARE ConvenioBLQ CURSOR FAST_FORWARD FOR 
    SELECT DISTINCT 
		CD_EMP, CD_CLI, CD_CONV
    FROM 
		#RsDemiteProv
									
    OPEN ConvenioBLQ
			
    FETCH NEXT FROM ConvenioBLQ INTO @CD_EMP, @CD_CLI, @CD_CONV
    WHILE @@FETCH_STATUS=0
	   Begin
		  UPDATE
			 RC_CLI_CONV_RC_CLI
		  SET 
			 RC_CLI_CONV_RC_CLI.STS_CRED=1,
			 DT_ULT_ALT =GETDATE(),
			 RC_CLI_CONV_RC_CLI.FLAG_CONVENIADO_DEMISSAO=1
		  WHERE
			 RC_CLI_CONV_RC_CLI.CD_EMP=@CD_EMP
			 AND RC_CLI_CONV_RC_CLI.CD_CONV=@CD_CONV
			 AND RC_CLI_CONV_RC_CLI.CD_CLI=@CD_CLI
			 AND RC_CLI_CONV_RC_CLI.STS_CRED = 0

/* Tag #A001 
		  UPDATE
			 RC_CLI
		  SET 
			 RC_CLI.STS_CRED=1,
			 RC_CLI.DT_ULT_ALT = CONVERT(CHAR(10),GETDATE(),112),
			 RC_CLI.FLAG_CONVENIADO_DEMISSAO=1
		  FROM
			 RC_CLI
		  WHERE
			 RC_CLI.CD_EMP=@CD_EMP
			 AND RC_CLI.CD_CLI=@CD_CLI
			 AND RC_CLI.STS_CRED=0		
*/
		  --Proximo registro
		  FETCH NEXT FROM ConvenioBLQ INTO @CD_EMP, @CD_CLI, @CD_CONV
	   End
		 
    CLOSE ConvenioBLQ; 
    DEALLOCATE ConvenioBLQ;

	--Marcando como processado
    UPDATE
	    WEB_CONV_DEMITE
    SET 
	    FLAG_PROCESSADO=1
    FROM
	    WEB_CONV_DEMITE INNER JOIN #RsDemiteProv D ON
	    WEB_CONV_DEMITE.CD_CONV_DEMITE = D.CD_CONV_DEMITE

    DROP TABLE 	#RsDemiteProv

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

	print 'P_ATUALIZA_SALDO_CONVENIADO FINALIZADA!';

	print 'INICIO P_ATUALIZA_SALDO_CONVENIADO_PARC !';
	EXEC P_ATUALIZA_SALDO_CONVENIADO_PARC

