IF NOT OBJECT_ID('P_WEB_CONV_RELATORIO_VENDAS_COLABORADORES') IS NULL
	BEGIN
		DROP PROCEDURE P_WEB_CONV_RELATORIO_VENDAS_COLABORADORES
	END

GO


CREATE PROCEDURE P_WEB_CONV_RELATORIO_VENDAS_COLABORADORES (@CD_EMP INT,@CD_CONV INT,@DT_INI  DATETIME,@DT_FIM  DATETIME,@DIA_FAT DATETIME, 
												@DT_DEMIS DATETIME,@COMP_COLABORADORES INT,@CD_CLI INT ,@CD_FILIAL INT)
AS 
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
--DROP TABLE #CONSULTA,#COMPCOLABORADORESSTATUS
/*DECLARE
	  @CD_EMP  INT =1,
       @CD_CONV INT =2209,
       @DT_INI  DATETIME ='20191120',
       @DT_FIM  DATETIME ='20191203',
       @DIA_FAT DATETIME =null,
       @DT_DEMIS DATETIME =null,
       @COMP_COLABORADORES INT =-1 ,
       @CD_CLI INT =1902678,
       @CD_FILIAL INT = 0 
*/
DECLARE @SELECT VARCHAR(MAX) =NULL
-- Tag #A001
				CREATE TABLE #CONSULTA (
								    CD_CLI					INT ,
								    NR_CARTAO				VARCHAR(30),
								    DT_VD					DATETIME,
								    CD_FILIAL				INT,
								    CD_CX					INT,
								    NR_ECF					INT,
								    MATRICULA				VARCHAR(30),
								    RZ_CLI					VARCHAR(130),
								    CD_PLANO_VENDA			INT ,
								    VLR_CONV				MONEY,
								    DIA_FAT					INT,
								    DIA_VENCTO				INT,
								    DS_PLANO_VENDA			VARCHAR (130),
								    QTDE_PONTOS				MONEY,
								    VLR_CONV_SEM_REC		MONEY,
								    VLR_CONV_COM_REC		MONEY,
								    VLR_CRED				MONEY,
									PARC					INT 
								   )
				CREATE TABLE #PONTOS
								(
								CD_EMP      INT,
								CD_FILIAL   INT,
								CD_VD       INT,
								DT_VD       DATETIME,
								CD_IT       INT,
								QTDE_PONTOS MONEY
								);
				CREATE TABLE #PDV_VD
								(
								CD_EMP        INT,
								CD_FILIAL     INT,
								CD_VD         INT,
								DT_VD         DATETIME,
								CD_IT         INT,
								CD_MEDICO_REC INT,
								VLR_CONV_IT   MONEY,
								QT_PARC_CONV INT
								);
   CREATE TABLE #COMPCOLABORADORESSTATUS (CD_EMP INT,ST_FECHVD_CONV INT)
    IF @COMP_COLABORADORES >= 0	 
	   BEGIN 
		INSERT INTO #COMPCOLABORADORESSTATUS (CD_EMP,ST_FECHVD_CONV)
		  SELECT 
			 1,
			 @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

    BEGIN 
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- ALIMENTANDO A TABELA QUE SUBISTIRUIR A V_PDV_VD_IT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    SET @SELECT = '
			   SELECT 
				    I.CD_EMP,
				    I.CD_FILIAL,
				    I.CD_VD,
				    I.DT_VD,
				    I.CD_IT,
				    0 AS CD_MEDICO_REC,
				    (CASE 
						WHEN I.ST_IT = 2 THEN 0
					  ELSE  I.TOT_IT / CAST(V.VLR_LIQ_VD AS decimal(20,9)) * V.VLR_CONV END) AS VLR_CONV_IT,
					  V.QT_PARC_CONV
			 FROM PDV_VD V INNER JOIN PDV_VD_IT I  ON V.CD_EMP = I.CD_EMP
								  AND V.CD_FILIAL =I.CD_FILIAL
								  AND V.CD_VD = I.CD_VD
								  AND V.DT_VD =I.DT_VD	
				 INNER JOIN PDV_VD_RC_VD_CONV ON
				 V.CD_EMP = PDV_VD_RC_VD_CONV.CD_EMP AND
				 V.CD_FILIAL = PDV_VD_RC_VD_CONV.CD_FILIAL AND
				 V.CD_VD = PDV_VD_RC_VD_CONV.CD_VD 
				 INNER JOIN RC_VD_CONV ON
				 PDV_VD_RC_VD_CONV.CD_EMP = RC_VD_CONV.CD_EMP AND
				 PDV_VD_RC_VD_CONV.CD_CTR = RC_VD_CONV.CD_CTR 
			 WHERE 
				V.CD_EMP =1
				AND V.ST_VD = 0
				AND I.ST_IT =1'
				IF @DT_INI IS NOT NULL
				    BEGIN 
				SET @SELECT =@SELECT +  ' AND V.DT_VD BETWEEN ''' + CONVERT (VARCHAR ,@DT_INI,112) + '''  AND  '''+ CONVERT (VARCHAR,@DT_FIM ,112)+''''
				    END
				    ELSE
					   BEGIN
						  SET @SELECT =@SELECT +  'AND V.DT_VD BETWEEN ''' + CONVERT (VARCHAR ,dateadd(day,1,@DIA_FAT),112) +
						   '''  AND  '''+  + CONVERT (VARCHAR,@DT_DEMIS ,112)+''''
					   END 
			 
				SET @SELECT =@SELECT + 'AND RC_VD_CONV.CD_CONV =' +CONVERT (VARCHAR,@CD_CONV)
				SET @SELECT =@SELECT + ' GROUP BY I.CD_FILIAL, I.CD_EMP, I.CD_VD, I.DT_VD, I.CD_IT, I.ST_IT, I.LIBERA_VD_CONV, I.TOT_IT, V.VLR_LIQ_VD, V.VLR_CONV, V.QT_PARC_CONV '

    --print @SELECT
    print 'Insert na #PDV_VD '+convert(varchar(20),getdate(),108);	
			 INSERT INTO #PDV_VD
			 EXEC (@SELECT)
	   END 

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- INSERINDO PONTOS
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 print 'Insert na #PONTOS '+convert(varchar(20),getdate(),108);	
    INSERT INTO #PONTOS
		  SELECT 
			    F.CD_EMP,
			    F.CD_FILIAL,
			    F.CD_VD,
			    F.DT_VD,
			    F.CD_IT,
			    QTDE_PONTOS
		  FROM #PDV_VD F
			  INNER JOIN RC_CLI_FIDELI_PONTOS_CREDITO_PDV_VD_IT_FIDELI_CARTAO AS VINCPONTOS ON VINCPONTOS.CD_EMP = F.CD_EMP
																			   AND VINCPONTOS.CD_FILIAL = F.CD_FILIAL
																			   AND VINCPONTOS.CD_VD = F.CD_VD
																			   AND VINCPONTOS.CD_IT = F.CD_IT
			  INNER JOIN RC_CLI_FIDELI_PONTOS_CREDITO AS PONTOS ON PONTOS.CD_EMP = F.CD_EMP
														AND PONTOS.CD_CTR_PONTOS = VINCPONTOS.CD_CTR_PONTOS;

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--INSERINDO OS DADOS DA RECEITA
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    print 'UPDATE na #PDV_VD '+convert(varchar(20),getdate(),108);
    UPDATE VD 
		  SET CD_MEDICO_REC = IR.CD_MEDICO_REC
    FROM #PDV_VD VD INNER JOIN PDV_VD_IT_REC AS IR ON   
							  VD.CD_EMP        = IR.CD_EMP AND   
							  VD.CD_FILIAL = IR.CD_FILIAL AND   
							  VD.CD_VD         = IR.CD_VD AND   
							  VD.CD_IT         = IR.CD_IT
			
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Tag #A002
  SET @SELECT = '
			    SELECT 
				    A.CD_CLI,
				    0 AS NR_CARTAO,
				    RVC.DT_VD,
				    RVC.CD_FILIAL,
				    RVC.CD_CX,
				    RVC.NR_ECF,
				    '''' AS MATRICULA,
				    A.RZ_CLI,
				    ISNULL(PVENDA.CD_PLANO_VENDA, 0) CD_PLANO_VENDA ,
				    RVC.VLR_CONV,
				    CONV.DIA_FAT,
				    CONV.DIA_VENCTO,
				    ISNULL(PVENDA.DS_PLANO_VENDA, ''NAO DEFINIDO'') DS_PLANO_VENDA,
				    ISNULL(SUM(VINCPONTOS.QTDE_PONTOS), 0) AS QTDE_PONTOS,
				    SUM(CASE  WHEN F.cd_medico_rec = 0  THEN F.vlr_conv_it/(CASE WHEN F.QT_PARC_CONV = 0 THEN 1 ELSE F.QT_PARC_CONV END) ELSE 0  END  ) VLR_CONV_SEM_REC,
                    SUM(CASE  WHEN F.cd_medico_rec > 0  THEN F.vlr_conv_it/(CASE WHEN F.QT_PARC_CONV = 0 THEN 1 ELSE F.QT_PARC_CONV END) ELSE 0  END  ) VLR_CONV_COM_REC,
				    ISNULL(G.VLR_CRED, 0) VLR_CRED
					,RVC.PARC
				FROM   RC_CLI AS A
				    INNER JOIN RC_VD_CONV AS RVC ON RVC.CD_EMP = A.CD_EMP
											 AND RVC.CD_CLI = A.CD_CLI
											 AND RVC.VLR_CONV > 0
											 AND A.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 RC_CLI_CONV AS CONV ON CONV.CD_EMP = A.CD_EMP
												  AND CONV.CD_CONV = RVC.CD_CONV
					LEFT JOIN RC_CLI_CONV_RC_CLI CONV_CLI ON RVC.CD_EMP  = CONV_CLI.CD_EMP
														 AND RVC.CD_CLI  = CONV_CLI.CD_CLI 
														 AND RVC.CD_CONV = CONV_CLI.CD_CONV							
				    LEFT JOIN GLB_PLANO_VENDA AS PVENDA ON CONV_CLI.CD_PLANO_VENDA = PVENDA.CD_PLANO_VENDA
 
				    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
				    LEFT JOIN #PONTOS AS VINCPONTOS ON VINCPONTOS.CD_EMP = F.CD_EMP
											 AND VINCPONTOS.CD_FILIAL = F.CD_FILIAL
											 AND VINCPONTOS.CD_VD = F.CD_VD
											 AND VINCPONTOS.CD_IT = F.CD_IT			  
				    LEFT JOIN RC_VD_CONV_CRED G ON RVC.CD_EMP = G.CD_EMP
											 AND RVC.CD_CTR = G.CD_CTR
				WHERE  A.CD_EMP =' +CONVERT (VARCHAR,@CD_EMP) +'
				    AND RVC.CD_CONV = ' +CONVERT (VARCHAR,@CD_CONV) 
				    IF @DT_INI IS NOT NULL
				    BEGIN 
				SET @SELECT =@SELECT +  ' AND RVC.DT_VD BETWEEN ''' + CONVERT (VARCHAR ,@DT_INI,112) + '''  AND  '''+ CONVERT (VARCHAR,@DT_FIM ,112)+''''
				    END
				    ELSE
					   BEGIN
						  SET @SELECT =@SELECT +  'AND RVC.DT_VD BETWEEN ''' + CONVERT (VARCHAR ,dateadd(day,1,@DIA_FAT),112) +
						   '''  AND  '''+  + CONVERT (VARCHAR,@DT_DEMIS ,112)+''''
					   END 
				    IF @CD_CLI IS NOT NULL
					   BEGIN
						  SET @SELECT =@SELECT +   'AND A.CD_CLI = ' + CONVERT (VARCHAR ,@CD_CLI)
					   END
					   IF @CD_FILIAL > 0
					   BEGIN 
						  SET @SELECT =@SELECT +   'AND RVC.CD_FILIAL = '+CONVERT (VARCHAR ,@CD_FILIAL)
					   END
				    SET @SELECT =@SELECT +  '
					   GROUP BY 
							  A.RZ_CLI,
							  A.CD_CLI,
							  RVC.VLR_CONV,
							  RVC.DT_VD,
							  RVC.CD_FILIAL,
							  RVC.CD_CX,
							  RVC.NR_ECF,
							  G.VLR_CRED,
							  RVC.DT_VD,
							  CONV.DIA_FAT,
							  CONV.DIA_VENCTO,
							  PVENDA.CD_PLANO_VENDA,
							  PVENDA.DS_PLANO_VENDA
							 ,RVC.PARC, F.QT_PARC_CONV' 
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    --print @SELECT
    print 'Insert na #CONSULTA '+convert(varchar(20),getdate(),108);
 				    --select @SELECT
				    INSERT INTO #CONSULTA
				     EXEC (@SELECT)

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    print 'Update na #CONSULTA '+convert(varchar(20),getdate(),108);
	    UPDATE CON
			 SET NR_CARTAO = D.NR_CARTAO
	    FROM #CONSULTA CON 
					  INNER JOIN RC_CLI_FIDELI_CARTAO AS D ON D.CD_EMP =1
													 AND CON.CD_CLI = D.CD_CLI
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    print 'Update 2 na #CONSULTA '+convert(varchar(20),getdate(),108);
	    UPDATE CON
			 SET MATRICULA = C.MATRICULA
	    FROM #CONSULTA CON  INNER JOIN RC_CLI_CONV_RC_CLI AS C ON C.CD_EMP = 1
												    AND CON.CD_CLI = C.CD_CLI
												    AND C.CD_CONV = @CD_CONV
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Tag #A003
	   print 'SELECT na #CONSULTA '+convert(varchar(20),getdate(),108);
	  SELECT      CD_CLI,
                    NR_CARTAO,
                    MATRICULA,
                    RZ_CLI,
                    DIA_FAT,
                    DIA_VENCTO,
                    CD_PLANO_VENDA,
                    DS_PLANO_VENDA, 
                    ISNULL(SUM(QTDE_PONTOS), 0) AS QTDE_PONTOS,
                    ISNULL(SUM(VLR_CONV - VLR_CRED), 0) AS VLR_CONV,
                    SUM(VLR_CONV_SEM_REC - VLR_CRED) AS VLR_CONV_SEM_REC,
                    SUM(VLR_CONV_COM_REC - VLR_CRED) AS VLR_CONV_COM_REC
        FROM
        (
            SELECT    CD_CLI,
                    NR_CARTAO,
                    MATRICULA,
                    RZ_CLI,
                    NR_ECF,
                    VLR_CONV,
                    VLR_CONV_SEM_REC,
                    VLR_CONV_COM_REC,
                    DIA_FAT,
                    DIA_VENCTO,
                    CD_PLANO_VENDA,
                    DS_PLANO_VENDA,
                    ISNULL(SUM(QTDE_PONTOS), 0) AS QTDE_PONTOS,
                    SUM(VLR_CRED) VLR_CRED
            FROM
            (
				SELECT 
				    CD_CLI ,
				    NR_CARTAO ,
				    DT_VD ,
				    CD_FILIAL ,
				    CD_CX ,
				    NR_ECF,
				    MATRICULA ,
				    RZ_CLI ,
				    CD_PLANO_VENDA ,
				    VLR_CONV ,
				    DIA_FAT ,
				    DIA_VENCTO ,
				    DS_PLANO_VENDA ,
				    QTDE_PONTOS ,
				    VLR_CONV_SEM_REC ,
				    VLR_CONV_COM_REC ,
				    VLR_CRED,
					PARC 
				 FROM #CONSULTA
                               
                    ) T
                        WHERE VLR_CONV - VLR_CRED > 0
                        GROUP BY CD_CLI,
                                 NR_CARTAO,
                                 MATRICULA,
                                 RZ_CLI,
                                 NR_ECF,
                                 VLR_CONV,
                                 VLR_CONV_SEM_REC,
                                 DIA_FAT,
                                 DIA_VENCTO,
                                 CD_PLANO_VENDA,
                                 DS_PLANO_VENDA,
                                 VLR_CONV_COM_REC
								,PARC
                    ) AS COMPRAS
                    GROUP BY CD_CLI,
                             NR_CARTAO,
                             MATRICULA,
                             RZ_CLI,
                             DIA_FAT,
                             DIA_VENCTO,
                             CD_PLANO_VENDA,
                             DS_PLANO_VENDA
                    ORDER BY RZ_CLI;
