USE [TryNFeOnline]
GO

ALTER TABLE [dbo].[emitente] ADD 
[usuarioEfrete] [varchar] (50) COLLATE Latin1_General_CI_AS NULL,
[senhaEfrete] [varchar] (50) COLLATE Latin1_General_CI_AS NULL
GO
ALTER TABLE [dbo].[veiculo] ADD 
[idnEfrete] [bit] NULL,
[anoFabricacao] [int] NULL,
[anoModelo] [int] NULL,
[chassi] [varchar] (100) COLLATE Latin1_General_CI_AS NULL,
[idCidade] [int] NULL,
[cor] [varchar] (100) COLLATE Latin1_General_CI_AS NULL,
[idFabricante] [int] NULL,
[modelo] [varchar] (50) COLLATE Latin1_General_CI_AS NULL,
[numeroEixos] [int] NULL
GO
CREATE TABLE [dbo].[mdfeCIOTViagem]
(
	[id] [int] IDENTITY (1,1) NOT NULL,
	[idCIOT] [int] NOT NULL,
	[documentoViagem] [varchar] (100) COLLATE Latin1_General_CI_AS NULL,
	[idCidadeOrigem] [int] NULL,
	[idCidadeDestino] [int] NULL,
	[cepOrigem] [varchar] (10) COLLATE Latin1_General_CI_AS NULL,
	[cepDestino] [varchar] (10) COLLATE Latin1_General_CI_AS NULL,
	[distanciaPercorrida] [int] NULL,
	[valorOperacao] [decimal] (18,4) NULL,
	[valorViagem] [decimal] (18,4) NULL,
	[valorAdiantamento] [decimal] (18,4) NULL,
	[valorQuitacao] [decimal] (18,4) NULL,
	[valorCombustivel] [decimal] (18,4) NULL,
	[valorPedagio] [decimal] (18,4) NULL,
	[valorSeguro] [decimal] (18,4) NULL,
	[valorOutrosCreditos] [decimal] (18,4) NULL,
	[justificativaOutrosCreditos] [varchar] (200) COLLATE Latin1_General_CI_AS NULL,
	[valorOutrosDebitos] [decimal] (18,4) NULL,
	[justificativaOutrosDebitos] [varchar] (200) COLLATE Latin1_General_CI_AS NULL,
	CONSTRAINT [PK_mdfeCIOTViagem] PRIMARY KEY CLUSTERED
	(
		[id] ASC
	) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY  = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[mdfeCIOTVeiculo]
(
	[id] [int] IDENTITY (1,1) NOT NULL,
	[idCIOT] [int] NOT NULL,
	[idVeiculo] [int] NULL,
	CONSTRAINT [PK_mdfeCIOTVeiculo] PRIMARY KEY CLUSTERED
	(
		[id] ASC
	) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY  = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[mdfeCIOTPagamento]
(
	[id] [int] IDENTITY (1,1) NOT NULL,
	[idCIOT] [int] NOT NULL,
	[idBanco] [int] NULL,
	[agencia] [varchar] (20) COLLATE Latin1_General_CI_AS NULL,
	[contaCorrente] [varchar] (20) COLLATE Latin1_General_CI_AS NULL,
	[dataDeposito] [date] NULL,
	[valor] [decimal] (18,4) NULL,
	[tipoPagamento] [varchar] (30) COLLATE Latin1_General_CI_AS NULL,
	[categoria] [varchar] (30) COLLATE Latin1_General_CI_AS NULL,
	CONSTRAINT [PK_mdfeCIOTPagamento] PRIMARY KEY CLUSTERED
	(
		[id] ASC
	) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY  = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ParceiroVeiculo]
(
	[id] [int] IDENTITY (1,1) NOT NULL,
	[idParceiro] [int] NOT NULL,
	[idVeiculo] [int] NOT NULL,
	CONSTRAINT [PK_ParceiroVeiculo] PRIMARY KEY CLUSTERED
	(
		[id] ASC
	) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY  = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[mdfeCIOTNotaFiscal]
(
	[id] [int] IDENTITY (1,1) NOT NULL,
	[idCIOTViagem] [int] NOT NULL,
	[numero] [varchar] (50) COLLATE Latin1_General_CI_AS NULL,
	[serie] [varchar] (50) COLLATE Latin1_General_CI_AS NULL,
	[dataEmissao] [date] NULL,
	[valor] [decimal] (18,4) NULL,
	[ncmMercadoria] [varchar] (10) COLLATE Latin1_General_CI_AS NULL,
	[descricaoMercadoria] [varchar] (100) COLLATE Latin1_General_CI_AS NULL,
	[pesoCarga] [decimal] (18,4) NULL,
	[tipoToleranciaPerdaMercadoria] [int] NULL,
	[valorToleranciaPerdaMercadoria] [decimal] (18,4) NULL,
	[tipoDiferencaFrete] [int] NULL,
	[valorToleranciaDiferencaFrete] [decimal] (18,4) NULL,
	CONSTRAINT [PK_mdfeCIOTNotaFiscal] PRIMARY KEY CLUSTERED
	(
		[id] ASC
	) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY  = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[mdfeCIOT]
(
	[id] [int] IDENTITY (1,1) NOT NULL,
	[idFilial] [int] NOT NULL,
	[codigoIdentificacaoOperacao] [varchar] (50) COLLATE Latin1_General_CI_AS NULL,
	[tipoOperacao] [varchar] (20) COLLATE Latin1_General_CI_AS NULL,
	[meioPagamento] [varchar] (20) COLLATE Latin1_General_CI_AS NULL,
	[idDestinatario] [int] NULL,
	[idTomador] [int] NULL,
	[idContratado] [int] NULL,
	[idMotorista] [int] NULL,
	[dataFimContrato] [date] NULL,
	[documentoViagem] [varchar] (100) COLLATE Latin1_General_CI_AS NULL,
	[dataInicioViagem] [date] NULL,
	[dataFimViagem] [date] NULL,
	[codigoTipoCarga] [varchar] (20) COLLATE Latin1_General_CI_AS NULL,
	[ncmMercadoria] [varchar] (10) COLLATE Latin1_General_CI_AS NULL,
	[embalagem] [varchar] (20) COLLATE Latin1_General_CI_AS NULL,
	[pesoCarga] [decimal] (18,4) NULL,
	[observacoesCredenciado] [varchar] (max) COLLATE Latin1_General_CI_AS NULL,
	[qtdSaques] [int] NULL,
	[qtdTransferencias] [int] NULL,
	[valorIRRF] [decimal] (18,4) NULL,
	[valorISSQN] [decimal] (18,4) NULL,
	[valorINSS] [decimal] (18,4) NULL,
	[valorSestSenat] [decimal] (18,4) NULL,
	[valorOutrosImpostos] [decimal] (18,4) NULL,
	[descritivoOutrosImpostos] [varchar] (max) COLLATE Latin1_General_CI_AS NULL,
	[dataGeracao] [datetime] NULL,
	[estadoCiot] [varchar] (20) COLLATE Latin1_General_CI_AS NULL,
	[arquivoPDF] [varbinary] (max) NULL,
	[protocoloCancelamento] [varchar] (50) COLLATE Latin1_General_CI_AS NULL,
	[protocoloEncerramento] [varchar] (50) COLLATE Latin1_General_CI_AS NULL,
	[dataCancelamento] [datetime] NULL,
	CONSTRAINT [PK_mdfeCIOT] PRIMARY KEY CLUSTERED
	(
		[id] ASC
	) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY  = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[mdfeCIOTNotaFiscal] ADD CONSTRAINT [FK_mdfeCIOTNotaFiscal_mdfeCIOTViagem] FOREIGN KEY
	(
		[idCIOTViagem]
	)
	REFERENCES [dbo].[mdfeCIOTViagem]
	(
		[id]
	)
GO

ALTER TABLE [dbo].[mdfeCIOTPagamento] ADD CONSTRAINT [FK_mdfeCIOTPagamento_Banco] FOREIGN KEY
	(
		[idBanco]
	)
	REFERENCES [dbo].[Banco]
	(
		[id]
	)
GO

ALTER TABLE [dbo].[mdfeCIOTPagamento] ADD CONSTRAINT [FK_mdfeCIOTPagamento_mdfeCIOT] FOREIGN KEY
	(
		[idCIOT]
	)
	REFERENCES [dbo].[mdfeCIOT]
	(
		[id]
	)
GO

ALTER TABLE [dbo].[mdfeCIOTVeiculo] ADD CONSTRAINT [FK_mdfeCIOTVeiculo_mdfeCIOT] FOREIGN KEY
	(
		[idCIOT]
	)
	REFERENCES [dbo].[mdfeCIOT]
	(
		[id]
	)
GO

ALTER TABLE [dbo].[mdfeCIOTVeiculo] ADD CONSTRAINT [FK_mdfeCIOTVeiculo_veiculo] FOREIGN KEY
	(
		[idVeiculo]
	)
	REFERENCES [dbo].[veiculo]
	(
		[id]
	)
GO

ALTER TABLE [dbo].[mdfeCIOTViagem] ADD CONSTRAINT [FK_mdfeCIOTViagem_cidadeDestino] FOREIGN KEY
	(
		[idCidadeDestino]
	)
	REFERENCES [dbo].[cidade]
	(
		[id]
	)
GO

ALTER TABLE [dbo].[mdfeCIOTViagem] ADD CONSTRAINT [FK_mdfeCIOTViagem_cidadeOrigem] FOREIGN KEY
	(
		[idCidadeOrigem]
	)
	REFERENCES [dbo].[cidade]
	(
		[id]
	)
GO

ALTER TABLE [dbo].[mdfeCIOTViagem] ADD CONSTRAINT [FK_mdfeCIOTViagem_mdfeCIOT] FOREIGN KEY
	(
		[idCIOT]
	)
	REFERENCES [dbo].[mdfeCIOT]
	(
		[id]
	)
GO

ALTER TABLE [dbo].[mdfeCIOT] ADD CONSTRAINT [FK_mdfeCIOT_contratado] FOREIGN KEY
	(
		[idContratado]
	)
	REFERENCES [dbo].[parceiro]
	(
		[id]
	)
GO

ALTER TABLE [dbo].[mdfeCIOT] ADD CONSTRAINT [FK_mdfeCIOT_destinatario] FOREIGN KEY
	(
		[idDestinatario]
	)
	REFERENCES [dbo].[parceiro]
	(
		[id]
	)
GO

ALTER TABLE [dbo].[mdfeCIOT] ADD CONSTRAINT [FK_mdfeCIOT_emitente] FOREIGN KEY
	(
		[idFilial]
	)
	REFERENCES [dbo].[emitente]
	(
		[id]
	)
GO

ALTER TABLE [dbo].[mdfeCIOT] ADD CONSTRAINT [FK_mdfeCIOT_motorista] FOREIGN KEY
	(
		[idMotorista]
	)
	REFERENCES [dbo].[parceiro]
	(
		[id]
	)
GO

ALTER TABLE [dbo].[mdfeCIOT] ADD CONSTRAINT [FK_mdfeCIOT_tomador] FOREIGN KEY
	(
		[idTomador]
	)
	REFERENCES [dbo].[parceiro]
	(
		[id]
	)
GO

ALTER TABLE [dbo].[ParceiroVeiculo] ADD CONSTRAINT [FK_ParceiroVeiculo_parceiro] FOREIGN KEY
	(
		[idParceiro]
	)
	REFERENCES [dbo].[parceiro]
	(
		[id]
	)
GO

ALTER TABLE [dbo].[ParceiroVeiculo] ADD CONSTRAINT [FK_ParceiroVeiculo_veiculo] FOREIGN KEY
	(
		[idVeiculo]
	)
	REFERENCES [dbo].[veiculo]
	(
		[id]
	)
GO

ALTER TABLE [dbo].[veiculo] ADD CONSTRAINT [FK_veiculo_FabricanteVeiculo] FOREIGN KEY
	(
		[idFabricante]
	)
	REFERENCES [dbo].[FabricanteVeiculo]
	(
		[id]
	)
GO
ALTER TABLE [dbo].[veiculo] ADD CONSTRAINT [FK_veiculo_cidade] FOREIGN KEY
	(
		[idCidade]
	)
	REFERENCES [dbo].[cidade]
	(
		[id]
	)
GO
UPDATE configuracoes SET versaoMinimaAppComandas = 10
GO
INSERT INTO VERSAO(VERSAO, VERSAOATUALIZACAO, DATAATUALIZACAO) VALUES(387, 645, GETDATE());