USE [TryNFeOnline]
GO

ALTER TABLE [dbo].[ctePrestacaoDesacordo] ADD 
[acao] [int] NULL,
[cStatCancelamento] [varchar] (5) COLLATE Latin1_General_CI_AS NULL,
[dataCancelamento] [datetime] NULL,
[nProtCancelamento] [varchar] (20) COLLATE Latin1_General_CI_AS NULL,
[xMotivoCancelamento] [varchar] (500) COLLATE Latin1_General_CI_AS NULL
GO
ALTER TABLE [dbo].[emitente] ADD 
[versaoCTe] [int] NULL
GO
ALTER TABLE [dbo].[NFProduto] ALTER COLUMN [vuncom] [decimal] (28,10) NULL
GO
ALTER TABLE [dbo].[NFProduto] ALTER COLUMN [vuntrib] [decimal] (28,10) NULL
GO
ALTER TABLE [dbo].[notafiscal] ADD 
[versaoCTeCTeOS] [int] NULL
GO
ALTER PROCEDURE [dbo].[TRYNFE_OBTERNOTAS]
  (
	-- Add the parameters for the stored procedure here
	@SERVICO_IN int
   )	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	declare @Resultado TABLE
	(
		NOTA_ID int,
		SERVICO int,
		NUM_RECIBO varchar(30),
		TP_AMB int,
		CODFILIAL int,
		CUF int,
		TP_EMIS int,
		idnEmiteNF bit,
		idnEmiteCte bit,
		idnEmiteMdfe bit,
		idnEmiteNFSe bit,
		idnEmiteNFCe bit,
		idnEmiteMDe bit,
		idnEmiteCTeOS bit,
		PADRAO_NFSe varchar(50),
		idnNovaVersao bit,
		versaoNFe int,
		versaoCTe int
	)
	
	if ((@SERVICO_IN = 0) or (@SERVICO_IN = 1)) 
	begin
				/*NOTA_ID int,
		SERVICO int,
		NUM_RECIBO varchar(30),
		TP_AMB int,
		CODFILIAL int,
		CUF int,
		TP_EMIS int*/
		INSERT INTO @Resultado
		select nf.id as NOTA_ID, 1 as SERVICO, null as NUM_RECIBO, null as TP_AMB,
		ne.idEmitente as CODFILIAL, null as CUF, nf.tpEmis as TP_EMIS, nf.idnEmiteNF, nf.idnEmiteCte, nf.idnEmiteMDFe, nf.idnEmiteNFSe, nf.idnEmiteNFCe, 0, nf.idnEmiteCTeOS, nf.NFSePadrao as PADRAO_NFSe, nf.idnNovaVersaoCTe, nf.versaoNFeNFCe, nf.versaoCTeCTeOS
		from notafiscal nf
		inner join nfEmitente ne on ne.idNotaFiscal = nf.id   
		where (nf.nfeAcao = 1)--Enviar Nota
		and   ((nf.idnEmiteNF = 1) or (nf.idnEmiteCte = 1) or (nf.idnEmiteCTeOS = 1) or (nf.idnEmiteMDFe = 1) or (nf.idnEmiteNFSe = 1) or (nf.idnEmiteNFCe = 1))

		INSERT INTO @Resultado
		select nf.id as NOTA_ID, 21 as SERVICO, null as NUM_RECIBO, null as TP_AMB,
		ne.idEmitente as CODFILIAL, null as CUF, nf.tpEmis as TP_EMIS, nf.idnEmiteNF, nf.idnEmiteCte, nf.idnEmiteMDFe, nf.idnEmiteNFSe, nf.idnEmiteNFCe, 0, nf.idnEmiteCTeOS, null as PADRAO_NFSe, nf.idnNovaVersaoCTe, nf.versaoNFeNFCe, nf.versaoCTeCTeOS
		from notafiscal nf
		inner join nfEmitente ne on ne.idNotaFiscal = nf.id   
		where (nf.nfeAcao = 21) --EPEC
		and   ((nf.idnEmiteNF = 1) or (nf.idnEmiteCte = 1) or (nf.idnEmiteMDFe = 1) or (nf.idnEmiteNFCe = 1))
				
		INSERT INTO @Resultado
		select nf.id as NOTA_ID, 2 as SERVICO, null as NUM_RECIBO, null as TP_AMB,
		ne.idEmitente as CODFILIAL, null as CUF, nf.tpEmis as TP_EMIS, nf.idnEmiteNF, nf.idnEmiteCte, nf.idnEmiteMDFe, nf.idnEmiteNFSe, nf.idnEmiteNFCe, 0, nf.idnEmiteCTeOS, nf.NFSePadrao as PADRAO_NFSe, nf.idnNovaVersaoCTe, nf.versaoNFeNFCe, nf.versaoCTeCTeOS
		from notafiscal nf
		inner join nfEmitente ne on ne.idNotaFiscal = nf.id 
		where (nf.nfeAcao = 2) --Cancelar Nota
		and   ((nf.nfeCstat = 100) or (nf.nfeCstat = 150))
		and   ((nf.idnEmiteNF = 1) or (nf.idnEmiteCte = 1) or (nf.idnEmiteCTeOS = 1) or (nf.idnEmiteMDFe = 1) or (nf.idnEmiteNFSe = 1) or (nf.idnEmiteNFCe = 1))
		
		INSERT INTO @Resultado
		select nf.id as NOTA_ID, 6 as SERVICO, null as NUM_RECIBO, null as TP_AMB,
		ne.idEmitente as CODFILIAL, null as CUF, nf.tpEmis as TP_EMIS, nf.idnEmiteNF, nf.idnEmiteCte, nf.idnEmiteMDFe, nf.idnEmiteNFSe, nf.idnEmiteNFCe, 0, nf.idnEmiteCTeOS, nf.NFSePadrao as PADRAO_NFSe, nf.idnNovaVersaoCTe, nf.versaoNFeNFCe, nf.versaoCTeCTeOS
		from notafiscal nf
		inner join nfEmitente ne on ne.idNotaFiscal = nf.id 		
		where (nf.nfeAcao = 6) --Verifica situa??o da nfe
		and   ((nf.idnEmiteNF = 1) or (nf.idnEmiteCte = 1) or (nf.idnEmiteCTeOS = 1) or (nf.idnEmiteMDFe = 1) or (nf.idnEmiteNFSe = 1) or (nf.idnEmiteNFCe = 1))
		
		INSERT INTO @Resultado
		select ni.id as NOTA_ID, 3 as SERVICO, null as NUM_RECIBO, null as TP_AMB,
		ni.idemitente as CODFILIAL, null as CUF, null as TP_EMIS, ni.idnEmiteNF, ni.idnEmiteCte, 0, 0, ni.idnEmiteNFCe, 0, ni.idnEmiteCTeOS, null as PADRAO_NFSe, 1, 4, 4
		from inutilizacao ni
		where (ni.cstat is null) -- Se algum status j? foi definido, indica que o servi?o j? obteve uma resposta...
						
		INSERT INTO @Resultado
		select nf.id as NOTA_ID, 8 as SERVICO, null as NUM_RECIBO, null as TP_AMB,
		ne.idEmitente as CODFILIAL, null as CUF, nf.tpEmis as TP_EMIS, nf.idnEmiteNF, nf.idnEmiteCte, nf.idnEmiteMDFe, nf.idnEmiteNFSe, nf.idnEmiteNFCe, 0, nf.idnEmiteCTeOS, null as PADRAO_NFSe, 0, nf.versaoNFeNFCe, nf.versaoCTeCTeOS
		from notafiscal nf
		inner join nfEmitente ne on ne.idNotaFiscal = nf.id   
		where (nf.nfeAcao = 100)    --Notas Recebidas
		and   (nf.idnEmiteNF = 0)
		
		INSERT INTO @Resultado
		select cc.id as NOTA_ID, 9 as SERVICO, null as NUM_RECIBO, nf.nfeTpAmb as TP_AMB, ne.idEmitente as CODFILIAL,
		null as CUF, null as TP_EMIS, nf.idnEmiteNF, nf.idnEmiteCte, nf.idnEmiteMDFe, nf.idnEmiteNFSe, nf.idnEmiteNFCe, 0, nf.idnEmiteCTeOS, null as PADRAO_NFSe, nf.idnNovaVersaoCTe, nf.versaoNFeNFCe, nf.versaoCTeCTeOS
		from nfCCe cc --Carta Correcao
		inner join notafiscal nf on nf.id = cc.idNotaFiscal
		inner join nfEmitente ne on ne.idNotaFiscal = nf.id
		where (cc.cStat is null)
		
		INSERT INTO @Resultado
		select ev.id as NOTA_ID, 9 as SERVICO, null as NUM_RECIBO, nf.nfeTpAmb as TP_AMB, ne.idEmitente as CODFILIAL,
		null as CUF, null as TP_EMIS, nf.idnEmiteNF, nf.idnEmiteCte, nf.idnEmiteMDFe, nf.idnEmiteNFSe, nf.idnEmiteNFCe, 0, nf.idnEmiteCTeOS, null as PADRAO_NFSe, nf.idnNovaVersaoCTe, nf.versaoNFeNFCe, nf.versaoCTeCTeOS
		from NFEvento ev --Carta correcao
		inner join notafiscal nf on nf.id = ev.idNotaFiscal
		inner join nfEmitente ne on ne.idNotaFiscal = nf.id
		where (ev.Acao = 1)

		INSERT INTO @Resultado
		select pd.id as NOTA_ID, 10 as SERVICO, null, pd.tpAmb, pd.idFilial, null, null, 0, 1, 0, 0, 0, 0, 0, null, 0, 4, coalesce(em.versaoCTe, 4)
		from ctePrestacaoDesacordo pd --Prestacao de Servico em Desacordo CTe
		inner join emitente em on em.id = pd.idFilial
		where (pd.cStat is null)
		
		INSERT INTO @Resultado
		select condutor.id as NOTA_ID, 11 as SERVICO, null as NUM_RECIBO, nf.nfeTpAmb as TP_AMB, ne.idEmitente as CODFILIAL,
		null as CUF, null as TP_EMIS, nf.idnEmiteNF, nf.idnEmiteCte, nf.idnEmiteMDFe, nf.idnEmiteNFSe, nf.idnEmiteNFCe, 0, nf.idnEmiteCTeOS, null as PADRAO_NFSe, nf.idnNovaVersaoCTe, nf.versaoNFeNFCe, nf.versaoCTeCTeOS
		from mdfeInclusaoCondutor condutor
		inner join notafiscal nf on nf.id = condutor.idNotaFiscal
		inner join nfEmitente ne on ne.idNotaFiscal = nf.id
		where (condutor.cStat is null) --Evento de Inclusao de Condutor MDFe
		
		INSERT INTO @Resultado
		select nf.id as NOTA_ID, 12 as SERVICO, null as NUM_RECIBO, nf.nfeTpAmb as TP_AMB, ne.idEmitente as CODFILIAL, 
		null as CUF, null as TP_EMIS, nf.idnEmiteNF, nf.idnEmiteCte, nf.idnEmiteMDFe, nf.idnEmiteNFSe, nf.idnEmiteNFCe, 0, nf.idnEmiteCTeOS, null as PADRAO_NFSe, nf.idnNovaVersaoCTe, nf.versaoNFeNFCe, nf.versaoCTeCTeOS
		from notafiscal nf
		inner join nfEmitente ne on ne.idNotaFiscal = nf.id 		
		where (nf.nfeAcao = 12) --Evento Encerramento MDFe
		and (nf.idnEmiteMDFe = 1)

		INSERT INTO @Resultado
		select pagto.id as NOTA_ID, 13 as SERVICO, null as NUM_RECIBO, nf.nfeTpAmb as TP_AMB, ne.idEmitente as CODFILIAL,
		null as CUF, null as TP_EMIS, nf.idnEmiteNF, nf.idnEmiteCte, nf.idnEmiteMDFe, nf.idnEmiteNFSe, nf.idnEmiteNFCe, 0, nf.idnEmiteCTeOS, null as PADRAO_NFSe, nf.idnNovaVersaoCTe, nf.versaoNFeNFCe, nf.versaoCTeCTeOS
		from mdfeMrPagamentoFrete pagto
		inner join mdfeModalRodoviario m on m.id = pagto.idModalRodoviario
		inner join notafiscal nf on nf.id = m.idNotaFiscal
		inner join nfEmitente ne on ne.idNotaFiscal = nf.id
		where (pagto.cStat is null) --Evento de Pagamento da Operacao de Transporte do MDFe
		and (pagto.idnEvento = 1)
		
		INSERT INTO @Resultado
		select mde.id as NOTA_ID, 15 as SERVICO, null as NUM_RECIBO, mde.tpAmb as TP_AMB, mde.idEmitente as CODFILIAL,
		null as CUF, null as TP_EMIS, 0, 0, 0, 0, 0, 1, 0, null as PADRAO_NFSe, 0, 4, 4
		from MDeConsultaNFe mde
		where (mde.acao = 1) --Consultar Notas Manifestacao
		and ((mde.dataHoraConsultar <= GETDATE()) or (mde.dataHoraConsultar is null))
		
		INSERT INTO @Resultado
		select ev.id as NOTA_ID, 16 as SERVICO, null as NUM_RECIBO, mde.tpAmb as TP_AMB, cons.idEmitente as CODFILIAL,
		null as CUF, null as TP_EMIS, 0, 0, 0, 0, 0, 1, 0, null as PADRAO_NFSe, 0, 4, 4
		from MDeEvento ev
		inner join MDeNotas mde on mde.id = ev.idMDeNota
		inner join MDeConsultaNFe cons on cons.id = mde.idMDeConsulta
		where (ev.Acao = 1) --Eventos Manifestacao Destinatario
		
		INSERT INTO @Resultado
		select mde.id as NOTA_ID, 17 as SERVICO, null as NUM_RECIBO, mde.tpAmb as TP_AMB, mde.idEmitente as CODFILIAL,
		null as CUF, null as TP_EMIS, 0, 0, 0, 0, 0, 1, 0, null as PADRAO_NFSe, 0, 4, 4
		from MDeDownloadNFe mde
		where (mde.Acao = 1) --Download XML
		
		INSERT INTO @Resultado
		select nf.id as NOTA_ID, 18 as SERVICO, null as NUM_RECIBO, null as TP_AMB,
		ne.idEmitente as CODFILIAL, null as CUF, nf.tpEmis as TP_EMIS, nf.idnEmiteNF, nf.idnEmiteCte, nf.idnEmiteMDFe, nf.idnEmiteNFSe, nf.idnEmiteNFCe, 0, nf.idnEmiteCTeOS, nf.NFSePadrao as PADRAO_NFSe, nf.idnNovaVersaoCTe, nf.versaoNFeNFCe, nf.versaoCTeCTeOS
		from notafiscal nf
		inner join nfEmitente ne on ne.idNotaFiscal = nf.id   
		where (nf.nfeAcao = 18) --Consultar Situacao Lote NFSe
		and   (nf.idnEmiteNFSe = 1)
		
		INSERT INTO @Resultado
		select nf.id as NOTA_ID, 19 as SERVICO, null as NUM_RECIBO, null as TP_AMB,
		ne.idEmitente as CODFILIAL, null as CUF, nf.tpEmis as TP_EMIS, nf.idnEmiteNF, nf.idnEmiteCte, nf.idnEmiteMDFe, nf.idnEmiteNFSe, nf.idnEmiteNFCe, 0, nf.idnEmiteCTeOS, nf.NFSePadrao as PADRAO_NFSe, nf.idnNovaVersaoCTe, nf.versaoNFeNFCe, nf.versaoCTeCTeOS
		from notafiscal nf
		inner join nfEmitente ne on ne.idNotaFiscal = nf.id   
		where (nf.nfeAcao = 19) --Consultar NFSe por RPS
		and   (nf.idnEmiteNFSe = 1)
		
		INSERT INTO @Resultado
		select nf.id as NOTA_ID, 20 as SERVICO, null as NUM_RECIBO, null as TP_AMB,
		ne.idEmitente as CODFILIAL, null as CUF, nf.tpEmis as TP_EMIS, nf.idnEmiteNF, nf.idnEmiteCte, nf.idnEmiteMDFe, nf.idnEmiteNFSe, nf.idnEmiteNFCe, 0, nf.idnEmiteCTeOS, nf.NFSePadrao as PADRAO_NFSe, nf.idnNovaVersaoCTe, nf.versaoNFeNFCe, nf.versaoCTeCTeOS
		from notafiscal nf
		inner join nfEmitente ne on ne.idNotaFiscal = nf.id   
		where (nf.nfeAcao = 20) --Consultar URL NFSe
		and   (nf.idnEmiteNFSe = 1)
		
		INSERT INTO @Resultado
		select en.id as NOTA_ID, 22 as SERVICO, null as NUM_RECIBO, en.tpAmb as TP_AMB,
		en.idEmitente as CODFILIAL, null as CUF, null as TP_EMIS, 0, 0, 1, 0, 0, 0, 0, null as PADRAO_NFSe, 0, 4, 4
		from mdfeEncerrar en
		where (en.acao = 1) --Encerrar MDFe nao lancada sistema
		
		INSERT INTO @Resultado
		select nf.id as NOTA_ID, 23 as SERVICO, null as NUM_RECIBO, null as TP_AMB,
		ne.idEmitente as CODFILIAL, null as CUF, nf.tpEmis as TP_EMIS, nf.idnEmiteNF, nf.idnEmiteCte, nf.idnEmiteMDFe, nf.idnEmiteNFSe, nf.idnEmiteNFCe, 0, nf.idnEmiteCTeOS, null as PADRAO_NFSe, nf.idnNovaVersaoCTe, nf.versaoNFeNFCe, nf.versaoCTeCTeOS
		from notafiscal nf
		inner join nfEmitente ne on ne.idNotaFiscal = nf.id
		where (nf.nfeAcao = 23) --Duplicar nota
		and   ((nf.idnEmiteNF = 1) or (nf.idnEmiteCte = 1) or (nf.idnEmiteMDFe = 1) or (nf.idnEmiteNFCe = 1))
		
		INSERT INTO @Resultado
		select pd.id as NOTA_ID, 24 as SERVICO, null, pd.tpAmb, pd.idFilial, null, null, 0, 1, 0, 0, 0, 0, 0, null, 0, 4, coalesce(em.versaoCTe, 4)
		from ctePrestacaoDesacordo pd --Cancelar evento Prestacao Servico em Desacordo CTe
		inner join emitente em on em.id = pd.idFilial
		where (pd.acao = 2)
		
		SELECT * from @Resultado		
		
	end
	
	if ((@SERVICO_IN = 0) or (@SERVICO_IN = 2))
	begin	    
			/*NOTA_ID int,
		SERVICO int,
		NUM_RECIBO varchar(30),
		TP_AMB int,
		CODFILIAL int,
		CUF int,
		TP_EMIS int*/
		INSERT INTO @Resultado
	    select nf.id, 5 as SERVICO, nf.nfeNrec as NUM_RECIBO, nf.nfeTpAmb as TP_AMB, ne.idEmitente as CODFILIAL,
	    es.codnacional as CUF, nf.tpEmis as TP_EMIS,  nf.idnEmiteNF, nf.idnEmiteCte, nf.idnEmiteMDFe, nf.idnEmiteNFSe, nf.idnEmiteNFCe, 0, nf.idnEmiteCTeOS, null as PADRAO_NFSe, nf.idnNovaVersaoCTe, nf.versaoNFeNFCe, nf.versaoCTeCTeOS
	    from notafiscal nf
	    inner join nfEmitente ne on ne.idNotaFiscal = nf.id
	    inner join cidade ci on ci.id = ne.idCidade
		inner join estado es on es.UF = ci.uf and es.idPais = ci.idPais	    	    
	    where (nf.nfeAcao = 5) --Acao 5 ? pra verificar o status da nota..
	    and   (nf.nfeNrec is not null)
	    and   (nf.nfeCstat in (103,104,105))
	    and   (DATEDIFF(SECOND, coalesce(nf.dataAtualizacaoStatusNota, convert(date, GETDATE())), GETDATE()) > 15)
	    
	    select * from @Resultado
	end	

	if ((@SERVICO_IN = 0) or (@SERVICO_IN = 4))
	begin		
	  INSERT INTO @Resultado
	  select nf.id as NOTA_ID, 4 as SERVICO, null as NUM_RECIBO, null as TP_AMB,
	  ne.idEmitente as CODFILIAL, null as CUF, null as TP_EMIS,  nf.idnEmiteNF, nf.idnEmiteCte, nf.idnEmiteMDFe, nf.idnEmiteNFSe, nf.idnEmiteNFCe, 0, nf.idnEmiteCTeOS, null as PADRAO_NFSe, nf.idnNovaVersaoCTe, nf.versaoNFeNFCe, nf.versaoCTeCTeOS
	  from notafiscal nf
	  inner join nfEmitente ne on ne.idNotaFiscal = nf.id
      where (nf.nfeAcao = 4)
    end	
END
GO
UPDATE emitente SET versaoCTe = 4
GO
INSERT INTO VERSAO(VERSAO, VERSAOATUALIZACAO, DATAATUALIZACAO, REVISAO) VALUES(498, 756, GETDATE(),1);