
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[V_NFE_STATUS]'))
DROP VIEW [DBO].[V_NFE_STATUS]
GO

DECLARE @QUERY NVARCHAR(MAX);
DECLARE @DBNAME NVARCHAR(128);

SET @DBNAME =(select coalesce((select  NOME_BANCO_DADOS from PRC_BANCOS_DADOS where TIPO_BANCO_DADOS = 2), 'nfe') as NomeBancoDeDados)



	
SELECT @QUERY=N'
	CREATE VIEW [dbo].[V_NFE_STATUS]
	AS

	WITH
		TMP_LOTES_PEND_ENVIO AS (
			SELECT ID FROM '+@DBNAME+'..EST_NF_NFE_LOTE WHERE STS_LOTE = 0
		)
		, TMP_LOTES_PEND_RETORNO AS (
			SELECT ID FROM '+@DBNAME+'..EST_NF_NFE_LOTE WHERE STS_LOTE = 1
		)
		, TMP_LOTES_PEND_ARMAZENAMENTO AS (
			SELECT ID FROM '+@DBNAME+'..EST_NF_NFE_LOTE WHERE STS_LOTE = 2
		)
		, TMP_ENTRADA AS (
			SELECT
				''ENTRADA'' AS TIPO	
				--PENDENTES DE GERACAO DO LOTE - A SEREM ENVIADAS PARA O INTERFACE NFE
				, (SELECT COUNT(*) FROM V_EST_NF_NFE_PENDENTE_EMISSAO WHERE PEND_SOLUC = 0 AND DS_TIPO = ''ENTRADA'') AS PEND_ENVIO_INTERFACE
				--PENDENDE DE CRIACAO NO INTERFACE NFE
				, (SELECT COUNT(*) FROM 
					(
						SELECT ''ENTRADA'' AS TIPO, * FROM EST_NFE_LOTE_ENT WHERE CD_NFE_LOTE IN (SELECT CD_NFE_LOTE FROM NFE_LOTE_PENDENTE WHERE TP_LOTE = 0 AND TP_NF = 0)
					) AS LOTES_PEND
					WHERE
						CD_NFE_LOTE NOT IN (SELECT CD_LOTE FROM '+@DBNAME+'..EST_NF_NFE_LOTE)
				) AS PEND_CRIACAO_NFE
				--PENDENTES DE ENVIO PARA SEFAZ
				, (
					SELECT COUNT(*) FROM '+@DBNAME+'..EST_NF_NFE_LOTE_NOTA WHERE ID_LOTE IN (SELECT ID FROM TMP_LOTES_PEND_ENVIO) AND TP_MOV = 0
				) AS PEND_ENVIO_SEFAZ
				--PENDENTES DE RETORNO DA SEFAZ
				, (
					SELECT COUNT(*) FROM '+@DBNAME+'..EST_NF_NFE_LOTE_NOTA WHERE ID_LOTE IN (SELECT ID FROM TMP_LOTES_PEND_RETORNO) AND TP_MOV = 0
				) AS PEND_RETORNO_SEFAZ
				--PENDENTES DE GRAVACAO NO GESTAO
				, (
					SELECT COUNT(*) FROM '+@DBNAME+'..EST_NF_NFE_LOTE_NOTA WHERE ID_LOTE IN (SELECT ID FROM TMP_LOTES_PEND_ARMAZENAMENTO) AND TP_MOV = 0
				) AS PEND_GRAVACAO_GESTAO
		)			
		, TMP_SAIDA AS (
			SELECT
				''SAIDA'' AS TIPO	
				--PENDENTES DE GERACAO DO LOTE - A SEREM ENVIADAS PARA O INTERFACE NFE
				, (SELECT COUNT(*) FROM V_EST_NF_NFE_PENDENTE_EMISSAO WHERE PEND_SOLUC = 0 AND DS_TIPO = ''SAIDA'') AS PEND_ENVIO_INTERFACE
				--PENDENDE DE CRIACAO NO INTERFACE NFE
				, (SELECT COUNT(*) FROM 
					(
						SELECT ''SAIDA'' AS TIPO, * FROM EST_NFE_LOTE_SAI WHERE CD_NFE_LOTE IN (SELECT CD_NFE_LOTE FROM NFE_LOTE_PENDENTE WHERE TP_LOTE = 0 AND TP_NF = 1)
					) AS LOTES_PEND
					WHERE
						CD_NFE_LOTE NOT IN (SELECT CD_LOTE FROM '+@DBNAME+'..EST_NF_NFE_LOTE)
				) AS PEND_CRIACAO_NFE
				--PENDENTES DE ENVIO PARA SEFAZ
				, (
					SELECT COUNT(*) FROM '+@DBNAME+'..EST_NF_NFE_LOTE_NOTA WHERE ID_LOTE IN (SELECT ID FROM TMP_LOTES_PEND_ENVIO) AND TP_MOV = 1
				) AS PEND_ENVIO_SEFAZ
				--PENDENTES DE RETORNO DA SEFAZ
				, (
					SELECT COUNT(*) FROM '+@DBNAME+'..EST_NF_NFE_LOTE_NOTA WHERE ID_LOTE IN (SELECT ID FROM TMP_LOTES_PEND_RETORNO) AND TP_MOV = 1
				) AS PEND_RETORNO_SEFAZ
				--PENDENTES DE GRAVACAO NO GESTAO
				, (
					SELECT COUNT(*) FROM '+@DBNAME+'..EST_NF_NFE_LOTE_NOTA WHERE ID_LOTE IN (SELECT ID FROM TMP_LOTES_PEND_ARMAZENAMENTO) AND TP_MOV = 1
				) AS PEND_GRAVACAO_GESTAO
		)			
		, TMP AS (
			SELECT * FROM TMP_ENTRADA
			UNION
			SELECT * FROM TMP_SAIDA
		)
		SELECT 
			TIPO
			, PEND_ENVIO_INTERFACE AS A_EMITIR
			, PEND_CRIACAO_NFE AS A_PROCESSAR
			, PEND_ENVIO_SEFAZ AS A_ENVIAR
			, PEND_RETORNO_SEFAZ AS A_RETORNAR
			, PEND_GRAVACAO_GESTAO AS A_GRAVAR
			, (PEND_ENVIO_INTERFACE + PEND_CRIACAO_NFE + PEND_ENVIO_SEFAZ + PEND_RETORNO_SEFAZ + PEND_GRAVACAO_GESTAO) AS TOTAL_PEND
			, GETDATE() AS CONSULTADO 
		FROM TMP
	GO';

EXEC sp_executesql @QUERY;

