IF EXISTS (SELECT * FROM SYS.PROCEDURES WHERE NAME ='P_WEB_CONV_LISTA_CLI_DETALHADO')
BEGIN 
	DROP PROCEDURE P_WEB_CONV_LISTA_CLI_DETALHADO
END
GO 
/*
=======================================================================================
'Nível Classificação.: INFORMAÇÃO RESTRITA
'Versão..............: 20241008
'Job.................:  
'Nome................: [P_WEB_CONV_LISTA_CLI_DETALHADO]
'Objetivo............: 
'Tabelas.............: 
'Dt Criação..........: --
'Dt Ult Alteracão....: 08/10/2024
'Criado por..........: --
'Versionado por......: Gerson Reis
'Alterado por........: Gerson Reis e Iuri Londero
'=======================================================================================
Alterado dia de vencimento para INT
*/

CREATE PROCEDURE [DBO].[P_WEB_CONV_LISTA_CLI_DETALHADO] (@CD_CONV INT ,
														 @DT_INI DATETIME,
														 @DT_FIM DATETIME,
														 @ST_COMP_COLABORADORES INT ,
														 @CD_FILIAL INT,
														 @RULE VARCHAR(30),
														 @DATAADD INT,
														 @CD_CLI VARCHAR (MAX))
AS 
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
/*
DECLARE 
@CD_CLI  	INT =153358,
@CD_CONV 	INT = 1368,
@DT_INI  	DATETIME = '07/15/2018',
@DT_FIM  	DATETIME ='07/31/2018',
@ST_COMP_COLABORADORES INT = -1,
@CD_FILIAL 	INT =0,
@SELECT     VARCHAR (MAX) = NULL,
@DT_INI2 	DATETIME,
@RULE 		VARCHAR(30)='CONVENIADO'
*/
DECLARE @SELECT VARCHAR (MAX) = NULL,
		@DT_INI2 DATETIME
SET @DT_INI2 = DATEADD(DAY,@DATAADD ,@DT_INI)
----------------------------------------------------------------------------------------------------
CREATE TABLE #DETALHADA
		(
					 CD_CTR                   INT
				   , CD_CLI                   INT
				   , RZ_CLI                   VARCHAR(250)
				   , DT_VD                    DATETIME
				   , CD_FILIAL                INT
				   , RZ_FILIAL                VARCHAR(150)
				   , CD_CX                    INT
				   , NR_ECF                   INT
				   , VLR_CONV                 MONEY
				   , ST_FECHVD_CONV           TINYINT
				   , HR_VD                    DATETIME
				   , MATRICULA                VARCHAR(50)
				   , FLAG_CONVENIADO_DEMISSAO INT
				   , PARC                     INT
				   , CD_PROD                  INT
				   , QT_IT                    MONEY
				   , VLR_IT                   MONEY
				   , TOT_IT                   MONEY
				   , VLR_DESC                 MONEY
				   , DS_USUAL                 VARCHAR(250)
				   , DS_ARV_MERC_LINHA        VARCHAR(150)
				   , DS_ARV_MERC_CATEG        VARCHAR(150)
				   , DS_ARV_MERC_DEPTO        VARCHAR(150)
				   , NM_FABRIC                VARCHAR(150)
				   , VLR_CONV_IT              MONEY
				   , CD_MEDICO_REC            INT
				   , CD_IT                    INT
				   , CD_VD                    INT
				   , CD_PLANO_VENDA           INT
				   , DS_PLANO_VENDA           VARCHAR(30)
				   , DIA_FAT                  INT
				   , DIA_VENCTO               INT
				   , ST_IT                    SMALLINT
				   , LIBERA_VD_CONV           SMALLINT
		);
----------------------------------------------------------------------------------------------------
   CREATE TABLE #COMPCOLABORADORESSTATUS (CD_EMP         INT, ST_FECHVD_CONV INT);
	IF @ST_COMP_COLABORADORES >= 0
    BEGIN
        INSERT INTO #COMPCOLABORADORESSTATUS (CD_EMP , ST_FECHVD_CONV)
        SELECT 1, @ST_COMP_COLABORADORES;
    END;
    ELSE
    BEGIN
        INSERT INTO #COMPCOLABORADORESSTATUS (CD_EMP, ST_FECHVD_CONV)
        SELECT 1 , ID
        FROM
            UFD_PRM_IN_STRING('0,1,5');
    END;
/************************************************************************************************************************************/
    SET @SELECT ='
         SELECT
			   RVC.CD_CTR
			 , RC_CLI.CD_CLI
			 , RZ_CLI
			 , RVC.DT_VD
			 , RVC.CD_FILIAL
			 , A.RZ_FILIAL
			 , RVC.CD_CX
			 , RVC.NR_ECF
			 , RVC.VLR_CONV - ISNULL(G.VLR_CRED_CONV, 0) VLR_CONV
			 , RVC.ST_FECHVD_CONV
			 , CASE WHEN F.HR_VD IS NULL THEN EST_NF_SAI.DT_HR_CAD ELSE F.HR_VD END AS HR_VD 
			 , ISNULL(D.MATRICULA, 0) AS MATRICULA
			 , ISNULL(D.FLAG_CONVENIADO_DEMISSAO, 1) AS FLAG_CONVENIADO_DEMISSAO
			 , RVC.PARC
			 , PROD.CD_PROD
			 , CASE WHEN IT.QT_IT IS NULL THEN EST_NF_SAI_IT.QT_IT ELSE IT.QT_IT END AS QT_IT 
			 , CASE WHEN IT.VLR_IT IS NULL THEN EST_NF_SAI_IT.VLR_IT ELSE IT.VLR_IT END AS VLR_IT 
			 , CASE WHEN IT.TOT_IT IS NULL THEN ROUND(EST_NF_SAI_IT.VLR_TOT_IT - ((((EST_NF_SAI_IT.PERC_DESC_IT * EST_NF_SAI_IT.VLR_IT) / 100) * EST_NF_SAI_IT.QT_IT)), 2) ELSE IT.TOT_IT END AS TOT_IT 
			 , CASE WHEN IT.VLR_IT IS NULL THEN ROUND((((EST_NF_SAI_IT.PERC_DESC_IT * EST_NF_SAI_IT.VLR_IT) / 100) * EST_NF_SAI_IT.QT_IT), 2) ELSE ROUND((((IT.TX_DESC * IT.VLR_IT) / 100) * IT.QT_IT), 4) END AS VLR_DESC
			 , PROD.DS_USUAL
			 , LINHA.DS_ARV_MERC_LINHA
			 , CATEG.DS_ARV_MERC_CATEG
			 , DEPTO.DS_ARV_MERC_DEPTO
			 , FABRIC.NM_FABRIC 
			 , CAST ( (CASE WHEN IT.ST_IT IS NULL THEN ROUND(((EST_NF_SAI_IT.VLR_TOT_IT - ((((EST_NF_SAI_IT.PERC_DESC_IT * EST_NF_SAI_IT.VLR_IT) / 100) * EST_NF_SAI_IT.QT_IT))) / EST_NF_SAI.VLR_NF * RVC.VLR_CONV) +  COALESCE(EST_NF_SAI_IT.VLR_PROD_FRETE,0) ,4,1)
							WHEN IT.ST_IT = 2 OR F.VLR_CONV = 0 THEN 0
							ELSE ROUND( CAST(IT.TOT_IT AS DECIMAL(10,2)) / CAST(F.VLR_LIQ_VD AS DECIMAL(10,2)) * CAST(F.VLR_CONV AS DECIMAL(10,2)) ,4,1) 
					    END) AS DECIMAL (10,2) )  AS VLR_CONV_IT 
			 , ISNULL(IR.CD_MEDICO_REC, 0) CD_MEDICO_REC
			 , CASE WHEN IT.CD_IT IS NULL THEN EST_NF_SAI_IT.CD_IT ELSE IT.CD_IT END AS CD_IT 
			 , CASE WHEN F.CD_VD IS NULL THEN EST_NF_SAI.NF_NF ELSE F.CD_VD END AS CD_VD 
			 , ISNULL( PVENDA.CD_PLANO_VENDA, 0) CD_PLANO_VENDA
			 , ISNULL( PVENDA.DS_PLANO_VENDA, ''NAO DEFINIDO'') DS_PLANO_VENDA
			 , CONV.DIA_FAT
			 , CONV.DIA_VENCTO
			 , CASE WHEN IT.ST_IT IS NULL THEN 1 ELSE IT.ST_IT END AS ST_IT 
			 , ISNULL(IT.LIBERA_VD_CONV,1) LIBERA_VD_CONV
		FROM
			 RC_CLI
			 INNER JOIN RC_VD_CONV AS RVC ON RVC.CD_EMP = RC_CLI.CD_EMP
											 AND RVC.CD_CLI = RC_CLI.CD_CLI
											 AND RVC.VLR_CONV > 0
											 AND RC_CLI.CD_EMP = 1
			 INNER JOIN #COMPCOLABORADORESSTATUS COMP ON COMP.CD_EMP = RVC.CD_EMP
														 AND COMP.ST_FECHVD_CONV = RVC.ST_FECHVD_CONV
			 INNER JOIN PRC_FILIAL AS A ON A.CD_FILIAL = RVC.CD_FILIAL
										   AND RVC.CD_EMP = A.CD_EMP
			 LEFT JOIN RC_CLI_CONV_RC_CLI AS D ON D.CD_CLI = RVC.CD_CLI
												  AND RVC.CD_EMP = D.CD_EMP
												  AND RVC.CD_CONV = D.CD_CONV
			 LEFT JOIN PDV_VD_RC_VD_CONV AS E ON E.CD_EMP = RVC.CD_EMP
												  AND E.CD_CTR = RVC.CD_CTR
			 LEFT JOIN PDV_VD AS F ON F.CD_EMP = E.CD_EMP
									   AND F.CD_FILIAL = E.CD_FILIAL
									   AND F.CD_VD = E.CD_VD
									   AND F.ST_VD = 0
			 LEFT JOIN PDV_VD_IT IT ON F.CD_VD = IT.CD_VD
										AND F.CD_EMP = IT.CD_EMP
										AND F.CD_FILIAL = IT.CD_FILIAL
			 LEFT OUTER JOIN PDV_VD_IT_REC AS IR ON IT.CD_EMP = IR.CD_EMP
												 AND IT.CD_FILIAL = IR.CD_FILIAL
												 AND IT.CD_VD = IR.CD_VD
												 AND IT.CD_IT = IR.CD_IT
			 LEFT OUTER JOIN EST_NF_SAI_RC_VD_CONV ON RVC.CD_EMP = EST_NF_SAI_RC_VD_CONV.CD_EMP
												   AND RVC.CD_FILIAL = EST_NF_SAI_RC_VD_CONV.CD_FILIAL
												   AND RVC.CD_CTR = EST_NF_SAI_RC_VD_CONV.CD_CTR
			 LEFT OUTER JOIN EST_NF_SAI ON EST_NF_SAI_RC_VD_CONV.CD_EMP = EST_NF_SAI.CD_EMP
										AND EST_NF_SAI_RC_VD_CONV.CD_FILIAL = EST_NF_SAI.CD_FILIAL
										AND EST_NF_SAI_RC_VD_CONV.CD_NF = EST_NF_SAI.CD_NF
			 LEFT OUTER JOIN EST_NF_SAI_IT ON EST_NF_SAI.CD_EMP = EST_NF_SAI_IT.CD_EMP
										   AND EST_NF_SAI.CD_FILIAL = EST_NF_SAI_IT.CD_FILIAL
										   AND EST_NF_SAI.CD_NF = EST_NF_SAI_IT.CD_NF
			 INNER JOIN EST_PROD PROD ON (IT.CD_PROD = PROD.CD_PROD OR EST_NF_SAI_IT.CD_PROD = PROD.CD_PROD)
			 LEFT JOIN (SELECT CD_EMP , CD_CTR , SUM(ISNULL(VLR_CRED, 0)) VLR_CRED_CONV
					FROM RC_VD_CONV_CRED GROUP BY CD_EMP , CD_CTR ) G ON RVC.CD_EMP = G.CD_EMP
						AND RVC.CD_CTR = G.CD_CTR
			 INNER JOIN EST_PROD_EST_ARV_MERCADOLOGICA ARVMERC ON PROD.CD_EMP = ARVMERC.CD_EMP
																  AND PROD.CD_PROD = ARVMERC.CD_PROD
			 INNER JOIN EST_ARV_MERC_LINHA LINHA ON ARVMERC.CD_EMP = LINHA.CD_EMP
													AND ARVMERC.CD_ARV_MERC_LINHA = LINHA.CD_ARV_MERC_LINHA
			 INNER JOIN EST_ARV_MERC_CATEGORIA CATEG ON ARVMERC.CD_EMP = CATEG.CD_EMP
														AND ARVMERC.CD_ARV_MERC_CATEG = CATEG.CD_ARV_MERC_CATEG
			 INNER JOIN EST_ARV_MERC_DEPTO DEPTO ON ARVMERC.CD_EMP = DEPTO.CD_EMP
													AND CATEG.CD_ARV_MERC_DEPTO = DEPTO.CD_ARV_MERC_DEPTO
			 INNER JOIN EST_PROD_FABRIC FABRIC ON PROD.CD_FABRIC = FABRIC.CD_FABRIC
			 LEFT JOIN GLB_PLANO_VENDA AS PVENDA ON D.CD_PLANO_VENDA = PVENDA.CD_PLANO_VENDA
			 INNER JOIN RC_CLI_CONV AS CONV ON CONV.CD_EMP = RC_CLI.CD_EMP
											   AND CONV.CD_CONV = RVC.CD_CONV
          WHERE
                RVC.CD_EMP = 1
			 AND (F.ST_VD=0 OR EST_NF_SAI.STS_NF = 0)
			 AND RVC.DT_VD BETWEEN ''' + CONVERT (VARCHAR ,@DT_INI2,112) + '''  AND  '''+ CONVERT (VARCHAR,@DT_FIM ,112)+''''
		IF @CD_CONV IS NOT NULL
			BEGIN
				SET @SELECT = @SELECT+' AND RVC.CD_CONV ='+CONVERT(VARCHAR, @CD_CONV);
			END;
		IF @RULE = 'CONVENIADO'
			BEGIN
				SET @SELECT = @SELECT+' AND RC_CLI.CD_CLI IN (SELECT * FROM UFD_PRM_IN_STRING('''+@CD_CLI+'''))';
			END;
		IF @CD_FILIAL > 0
			BEGIN
				SET @SELECT = @SELECT+' AND RVC.CD_FILIAL = '+CONVERT(VARCHAR, @CD_FILIAL);
			END;
/************************************************************************************************************************************/
		--PRINT @SELECT
		INSERT INTO #DETALHADA  
		EXEC (@SELECT)
/************************************************************************************************************************************/		 
		DECLARE @FILIAL     INT;
		DECLARE @CD_VD	    INT;
		DECLARE @VLR_DIF    MONEY;
		DECLARE @TIRAPOE    MONEY;
		DECLARE @VLR_CONV   MONEY;  		
			SET @TIRAPOE = 0.00;
		DECLARE C1 CURSOR
		FOR 
		    	SELECT 
				       V.CD_FILIAL, 
				       V.CD_VD, 
					   V.VLR_CONV,
				       V.VLR_CONV - ROUND(VLR_CONV_IT,2) AS VLR_DIF
				  FROM PDV_VD V
				  	   JOIN (SELECT CD_FILIAL, 
									CD_VD, 
									SUM(VLR_CONV_IT) VLR_CONV_IT 
							   FROM #DETALHADA 
							  GROUP BY CD_FILIAL, 
									   CD_VD) B ON V.CD_FILIAL = B.CD_FILIAL 
											   AND V.CD_VD	   = B.CD_VD
				  WHERE V.CD_EMP=1
				  	AND (V.VLR_CONV - VLR_CONV_IT) <> 0
		OPEN C1;
		FETCH NEXT FROM C1 INTO  @FILIAL, @CD_VD , @VLR_CONV , @VLR_DIF;
		WHILE @@FETCH_STATUS = 0
		BEGIN
		PRINT 'AJUSTANDO DIFERENÇA DO VLR_CONV_IT DA VENDA = ' + CONVERT(VARCHAR,@CD_VD) + ' DIF. '+ CONVERT(VARCHAR,@VLR_DIF) ;
			IF @VLR_CONV <> (SELECT SUM(TOT_IT) FROM #DETALHADA WHERE CD_VD = @CD_VD AND CD_FILIAL = @FILIAL) 
			BEGIN
			print ' Venda com pgto parcial em convenio! ' + CONVERT(VARCHAR,@CD_VD)  ;
				IF @VLR_DIF <> 0 
				BEGIN 	  		   
					IF @VLR_DIF > 0 
						SET @TIRAPOE = 0.01
					IF @VLR_DIF < 0 
						SET @TIRAPOE = -0.01;
					WHILE @VLR_DIF > 0
						BEGIN 
							UPDATE TOP(1) #DETALHADA
							   SET VLR_CONV_IT = VLR_CONV_IT + @TIRAPOE
							 WHERE CD_VD	   = @CD_VD
							   AND CD_FILIAL   = @FILIAL
							   AND VLR_CONV_IT > 0  
							   AND TOT_IT >  VLR_CONV_IT
							SET @VLR_DIF = @VLR_DIF - @TIRAPOE		 
							PRINT ' VLR_DIF ' + CONVERT(VARCHAR,@VLR_DIF) ;
						END 
				END	
			END		    		
			ELSE
			BEGIN
				print ' Venda com pgto total em convenio.'  + CONVERT(VARCHAR,@CD_VD)  
				UPDATE #DETALHADA	
				   SET VLR_CONV_IT = TOT_IT
				 WHERE CD_VD	   = @CD_VD
				   AND CD_FILIAL   = @FILIAL;
			END
		FETCH NEXT FROM C1 INTO  @FILIAL, @CD_VD , @VLR_CONV , @VLR_DIF;
		  END   
		CLOSE C1;
		DEALLOCATE C1;	 
/************************************************************************************************************************************/
	SELECT
		   MATRICULA
		 , CD_CLI
		 , RZ_CLI
		 , CD_FILIAL AS CODIGO_FILIAL
		 , CONVERT(VARCHAR(10), ISNULL(DT_VD, ''), 120)+' '+CONVERT(VARCHAR(10), ISNULL(HR_VD, ''), 108) AS DT_VD
		 , REPLICATE('0', 3-LEN(CONVERT(VARCHAR, CD_FILIAL)))+CONVERT(VARCHAR, CD_FILIAL)+' - '+RZ_FILIAL AS CD_FILIAL
		 , NR_ECF
		 , PARC
		 , ST_FECHVD_CONV
		 , QT_IT
		 , VLR_CONV
		 , VLR_IT
		 , TOT_IT
		 , VLR_CONV_IT
		 , CASE WHEN (TOT_IT - VLR_CONV_IT) < 0 THEN 0 ELSE  (TOT_IT - VLR_CONV_IT) END AS VLR_OUTROS_PGTO
		 , VLR_DESC
		 , DS_USUAL
		 , DS_ARV_MERC_LINHA
		 , DS_ARV_MERC_CATEG
		 , DS_ARV_MERC_DEPTO
		 , NM_FABRIC
		 , FLAG_CONVENIADO_DEMISSAO
		 , CD_MEDICO_REC
		 , CD_IT
		 , CD_VD
		 , CD_PLANO_VENDA
		 , DS_PLANO_VENDA
		 , DIA_FAT
		 , DIA_VENCTO
		 , CD_CX
		 , HR_VD
		 , RZ_FILIAL
		 , ST_IT
	FROM
		 #DETALHADA;

GO


