IF EXISTS ( SELECT *
			  FROM sys.objects
		   	 WHERE object_id = OBJECT_ID(N'[dbo].[P_EST_NF_CONTROLE_NUMERACAO]')
		       AND type IN(N'P', N'PC')
			)
	DROP PROCEDURE [dbo].[P_EST_NF_CONTROLE_NUMERACAO];
GO
  
--P_EST_NF_CONTROLE_NUMERACAO 1, '1',6,'2015-09-30','2015-09-30'

CREATE PROCEDURE P_EST_NF_CONTROLE_NUMERACAO(@CD_EMP      INT
										   , @CD_FILIAL   VARCHAR(2500)
										   , @CD_NF_SERIE INT           = 0
										   , @DT_INI      DATETIME
										   , @DT_FIM      DATETIME
										   , @STS_NF      INT           = 99)
AS
SET NOCOUNT ON;

	 CREATE TABLE #NotaFiscalAnalise (
			  CD_FILIAL              INT
			, CD_NF                  INT
			, NR_NF                  INT
			, DT_EMI_NF              DATETIME
			, SERIE                  VARCHAR(20)
			, NM_FANT                VARCHAR(200)
			, TIPO_DOCUMENTO         VARCHAR(30)
			, INUTILIZADO            VARCHAR(30)
			, NR_PROTOCOLO_INU       VARCHAR(50)
			, NM_USU                 VARCHAR(150)
			, NR_PROTOCOLO_VALIDACAO VARCHAR(50)
			, NR_PROTOCOLO_CANCEL    VARCHAR(50)
			, BX_ESTOQUE             INT
			, ATU_ESTOQUE            INT
			, TIPO_ESTOQUE           INT
			, STS_NF				 Tinyint );


	 CREATE TABLE #NotaFiscalFalta (
			  CD_FILIAL              INT
			, CD_NF                  INT
			, NR_NF                  INT
			, DT_EMI_NF              DATETIME
			, SERIE                  VARCHAR(20)
			, NM_FANT                VARCHAR(200)
			, TIPO_DOCUMENTO         VARCHAR(30)
			, INUTILIZADO            VARCHAR(30)
			, NR_PROTOCOLO_INU       VARCHAR(50)
			, NM_USU                 VARCHAR(150)
			, NR_PROTOCOLO_VALIDACAO VARCHAR(50)
			, NR_PROTOCOLO_CANCEL    VARCHAR(50)
			, BX_ESTOQUE             INT
			, ATU_ESTOQUE            INT
			, TIPO_ESTOQUE           INT
			, STS_NF				 Tinyint );


	 CREATE TABLE #Filiais( 
			  CD_EMP    INT
			, CD_FILIAL INT 	 );


-- ::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
-- ::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

	PRINT 'Inicio da rotina ' +convert(varchar(10), getdate(), 108) ;

	INSERT INTO #Filiais
		SELECT @CD_EMP
			  ,ID
		  FROM UFD_PRM_IN_STRING(@CD_FILIAL)
		OPTION(RECOMPILE);



	PRINT 'Carga de filial ' +convert(varchar(10), getdate(), 108) ;

	 INSERT INTO #NOTAFISCALANALISE
			SELECT N.CD_FILIAL
				 , N.CD_NF
				 , N.NR_NF
				 , N.DT_EMI_NF
				 , N.SERIE
				 , N.NM_FANT
				 , N.TIPO_DOCUMENTO
				 , N.INUTILIZADO
				 , N.NR_PROTOCOLO_INU
				 , N.NM_USU
				 , NR_PROTOCOLO_VALIDACAO
				 , NR_PROTOCOLO_CANCEL
				 , N.BX_EST
				 , N.ATU_EST
				 , N.TP_ESTQ
				 , N.STS_NF
			FROM V_EST_NF_CONTROLE_NUMERACAO N
				 INNER JOIN #FILIAIS F ON N.CD_EMP = F.CD_EMP
									  AND N.CD_FILIAL = F.CD_FILIAL
			WHERE N.CD_EMP = 1
			  AND N.DT_EMI_NF BETWEEN @DT_INI AND @DT_FIM
			  AND N.CD_NF_SERIE = CASE WHEN @CD_NF_SERIE = 0 THEN N.CD_NF_SERIE
									   ELSE @CD_NF_SERIE
								   END 
			ORDER BY N.CD_FILIAL
				   , N.SERIE
				   , N.NR_NF;

-- ::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
-- ::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
		
	 PRINT 'carregou as notas ' +convert(varchar(10), getdate(), 108) ;
	 DECLARE @CD_FILIAL_CURSOR		INT;
	 DECLARE @NR_NF					INT;
	 DECLARE @SERIE					VARCHAR(10);
	 
	 DECLARE @CD_FILIAL_CURSOR_ANT	INT;
	 DECLARE @SERIE_ANT				VARCHAR(10);
	 DECLARE @NR_NF_ANT				INT;
	 DECLARE @NR_INI				INT; 
	 DECLARE @NR_FIM				INT; 

	 DECLARE ANALISEFALHA CURSOR FAST_FORWARD READ_ONLY 
	 FOR 
		SELECT CD_FILIAL
			  , SERIE
		 FROM #NOTAFISCALANALISE
		GROUP BY CD_FILIAL,
				 SERIE;

	 

	 OPEN ANALISEFALHA;
	 FETCH NEXT FROM ANALISEFALHA INTO @CD_FILIAL_CURSOR , @SERIE;

		WHILE @@FETCH_STATUS = 0
		BEGIN
		
			SELECT @NR_INI = MIN(NR_NF)
			  FROM #NOTAFISCALANALISE 
			 WHERE CD_FILIAL = @CD_FILIAL_CURSOR 
			   AND SERIE     = @SERIE
			
				SET @NR_NF_ANT = @NR_INI
							 
			SELECT @NR_FIM = MAX(NR_NF)
			  FROM #NOTAFISCALANALISE
			 WHERE CD_FILIAL = @CD_FILIAL_CURSOR
			   AND SERIE  = @SERIE

			PRINT 'Nf_Ini.: ' + convert(varchar, @NR_INI ) + 
			      ' Nf_Fim.: ' + convert(varchar, @NR_FIM )  
					
		 
			WHILE @NR_NF_ANT < @NR_FIM
				BEGIN 
					SET @NR_NF_ANT = @NR_NF_ANT + 1 
	
						IF NOT EXISTS ( SELECT *
										  FROM #NOTAFISCALANALISE A
												JOIN (SELECT A.CD_FILIAL,	
															 B.CD_FILIAL CD_FILIAL_COMPARTILHADA		   
													    FROM EF_NF_CTR_NR_COMPARTILHADO A 
															 JOIN EF_NF_CTR_NR_COMPARTILHADO B ON A.CD_CTR=B.CD_CTR 
																		  					  AND A.CD_EMP=B.CD_EMP 
																		  				 	  AND A.CD_FILIAL <>B.CD_FILIAL )C ON A.CD_FILIAL  = C.CD_FILIAL 
										  WHERE C.CD_FILIAL_COMPARTILHADA = @CD_FILIAL_CURSOR
												 )  AND NOT EXISTS (SELECT * 
																	  FROM #NOTAFISCALANALISE A 
																	 WHERE CD_FILIAL = @CD_FILIAL_CURSOR 
																	   AND NR_NF     = @NR_NF_ANT 
																	   AND SERIE     = @SERIE)  
	
							BEGIN 
	
							PRINT 'Carrega a nota com falha Lj ' + convert(varchar, @CD_FILIAL_CURSOR  ) + 
							      ' Nf_ant.:' + convert(varchar, @NR_NF_ANT ) + ' - ' + convert(varchar(10), getdate(), 108) ;	
	
	
								   INSERT INTO #NOTAFISCALFALTA  (	CD_FILIAL, CD_NF, NR_NF, DT_EMI_NF, SERIE, NM_FANT, 
																	TIPO_DOCUMENTO, INUTILIZADO, NR_PROTOCOLO_INU, NM_USU, 
																	NR_PROTOCOLO_VALIDACAO 	, NR_PROTOCOLO_CANCEL, BX_ESTOQUE, ATU_ESTOQUE,TIPO_ESTOQUE,STS_NF)           									
									SELECT 
										  @CD_FILIAL_CURSOR CD_FILIAL
										, 0					CD_NF
										, @NR_NF_ANT		NR_NF
										, NULL				DT_EMI_NF
										, @SERIE			SERIE
										, 'FALHA NA NUMERACAO' NM_FANT
										, 'FALHA NA NUMERACAO' TIPO_DOCUMENTO
										, ''				INUTILIZADO
										, ''				NR_PROTOCOLO_INU
										, ''				NM_USU
										, ''				NR_PROTOCOLO_VALIDACAO
										, ''				NR_PROTOCOLO_CANCEL 
										,0					BX_ESTOQUE 
										,0					ATU_ESTOQUE
										,0					TIPO_ESTOQUE
										, @STS_NF ; 
							END
				END		
			
	 FETCH NEXT FROM ANALISEFALHA INTO @CD_FILIAL_CURSOR, @SERIE;
		 END
		  
	 CLOSE ANALISEFALHA;  
	 DEALLOCATE ANALISEFALHA;	

-- ::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
-- ::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

	 
		INSERT INTO #NOTAFISCALANALISE
			SELECT * 
			  FROM #NOTAFISCALFALTA


-- ::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
-- ::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

	IF @STS_NF  <> 2 
		BEGIN 
			SELECT *
			  FROM #NOTAFISCALANALISE
			 WHERE STS_NF = CASE WHEN @STS_NF = 99 THEN STS_NF
								 WHEN @STS_NF = 1  THEN @STS_NF
								 ELSE @STS_NF 
							END  
		
			ORDER BY CD_FILIAL
					, SERIE
					, NR_NF;
		END 
		ELSE IF  @STS_NF  = 2
			BEGIN 
				SELECT *
				  FROM #NOTAFISCALANALISE
				 WHERE NR_PROTOCOLO_VALIDACAO <> ''
				   AND INUTILIZADO = 'CANCELADO'
				   AND NR_PROTOCOLO_CANCEL = ''
				   AND STS_NF = 1 
				ORDER BY CD_FILIAL
						, SERIE
						, NR_NF;
			END 
		

	 DROP TABLE #NOTAFISCALANALISE;
	 DROP TABLE #NOTAFISCALFALTA;