DELETE FROM Adm_Opciones WHERE IdOpc ='VVC' and nModulo ='TRAES'
GO
ALTER TABLE Trn_TraEspContratoDet ADD
EsItemPME BIT DEFAULT 0 NOT NULL
GO
ALTER TABLE Trn_TraEspResDetalle ADD
EsItemPME BIT DEFAULT 0 NOT NULL
GO
--Sys_TiposDoc Agregar los tipos de documentos de transporte especial
INSERT INTO dbo.Sys_TiposDoc VALUES
('LPE','LISTA DE PASAJEROS TRANSPORTE ESPECIAL',0,NULL,0,0)
GO
UPDATE Sys_ObjetosApp SET IndObj = (IndObj + 1) 
WHERE IndObj  > 6 AND IdGrupo = 'TESDOC' AND SubModulo = 'TES' AND IndObj <= 11
GO
UPDATE Sys_ObjetosApp SET IndObj = 1 
WHERE IdGrupo = 'TESPRO' AND SubModulo = 'TES' AND IdObj ='FRMOTROSI'
GO
INSERT INTO dbo.Sys_ObjetosApp (IdObj, IdGrupo,	SubModulo, IndObj, Nombre, Formulario, Permisos, NivelMinimo, NomArchivo)
--Catálogos tablas básicas,
VALUES
('FRMCERESTE','TESPRO','TES',2,'Cambio de Estado de Reservas','FRMDCERES','S',4,NULL),
('FRMLISPRTE','TESDOC','TES',7,'Listado de Pasajeros Reservas (Facturación)','FRMDLISPR','S',0,NULL),
('FRMRELRES','TESPRO','TES',3,'Proceso de Réplica de Lista de Reservas','FRMDRELRES','S',0,NULL),
('FRMPMFTE','TESPRO','TES',20,'Proceso Masivo de Facturación-Colegios','FRMDPMFTE','S',0,NULL)
GO
--Adm_Opciones
INSERT INTO dbo.Adm_Opciones VALUES
('VVE','VALIDACIONES COMPLETAS PARA VEHÍCULOS EN CONTRATO DE TRANSPORTE ESPECIAL','BOOLEAN','1',5,'TRAES'),
('DVE','DÍAS DE VENCIMIENTO DE FACTURAS PARA ESTUDIANTES','INTEGER','5',4,'TRAES')
GO
--Usuarios
INSERT INTO dbo.Sys_Roles VALUES
--Lista Pasajeros Reservas
('FRMLISPRTE','CIA','Cambiar de Compañía'),
('FRMRELRES','CIA','Cambiar de Compañía')
GO
CREATE TABLE [dbo].[Trn_TraEspListaRes](
	[IdLista] [int] NOT NULL,
 CONSTRAINT [PK_Trn_TraEspListaRes] PRIMARY KEY CLUSTERED 
(
	[IdLista] 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].[Trn_TraEspListaPasaRes](
	[IdLista] [int] NOT NULL,
	[Documento] [varchar](16) NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Trn_TraEspListaPasaRes]  WITH CHECK ADD  CONSTRAINT [FK_Trn_TraEspListaPasaRes_Lista] FOREIGN KEY([IdLista])
REFERENCES [dbo].[Trn_TraEspListaRes] ([IdLista])
GO
ALTER TABLE [dbo].[Trn_TraEspListaPasaRes] CHECK CONSTRAINT [FK_Trn_TraEspListaPasaRes_Lista]
GO
ALTER TABLE [dbo].[Trn_TraEspListaPasaRes]  WITH CHECK ADD  CONSTRAINT [FK_Trn_TraEspListaPasaRes_Pasajeros] FOREIGN KEY([Documento])
REFERENCES [dbo].[Trn_TraEspPasajeros] ([Documento])
GO
ALTER TABLE [dbo].[Trn_TraEspListaPasaRes] CHECK CONSTRAINT [FK_Trn_TraEspListaPasaRes_Pasajeros]
GO
ALTER TABLE [dbo].[Trn_TraEspListaPasaRes]  WITH CHECK ADD  CONSTRAINT [CK_Trn_TraEspListaPasaRes_Documento] CHECK  ((len([Documento])>(0)))
GO
ALTER TABLE [dbo].[Trn_TraEspListaPasaRes] CHECK CONSTRAINT [CK_Trn_TraEspListaPasaRes_Documento]
GO
--Tabla reserva lista
CREATE TABLE [dbo].[Trn_TraEspReservaVehLista](
	[IdLista] [int] NOT NULL,
	[TipDoc] [varchar](3) NOT NULL,
	[Reserva] [int] NOT NULL,
	[IdCia] [char](2) NOT NULL,
	[Item] [int] NOT NULL,
	[IdVehiculo] [varchar](10) NOT NULL,
	[Fecha] [datetime] NOT NULL,
	[IdEstado] [varchar](4) NOT NULL,
	[FecUpdate] [smalldatetime] NULL,
	[IdUsuario] [varchar](11) NOT NULL,
	[IdListaCOE] [int] NOT NULL default 0,
 CONSTRAINT [PK_Trn_TraEspReservaVehLista] PRIMARY KEY CLUSTERED 
(
	[IdLista] ASC,
	[TipDoc] ASC,
	[Reserva] ASC,
	[IdCia] ASC,
	[Item] 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].[Trn_TraEspReservaVehLista] ADD  DEFAULT ((0)) FOR [Item]
GO
ALTER TABLE [dbo].[Trn_TraEspReservaVehLista]  WITH CHECK ADD  CONSTRAINT [FK_Trn_TraEspReservaVehLista_adm_Usuarios] FOREIGN KEY([IdUsuario])
REFERENCES [dbo].[adm_Usuarios] ([IdUsuario])
GO
ALTER TABLE [dbo].[Trn_TraEspReservaVehLista] CHECK CONSTRAINT [FK_Trn_TraEspReservaVehLista_adm_Usuarios]
GO
ALTER TABLE [dbo].[Trn_TraEspReservaVehLista]  WITH CHECK ADD  CONSTRAINT [FK_Trn_TraEspReservaVehLista_EstadoDoc] FOREIGN KEY([IdEstado])
REFERENCES [dbo].[EstadoDoc] ([IdEstado])
GO
ALTER TABLE [dbo].[Trn_TraEspReservaVehLista] CHECK CONSTRAINT [FK_Trn_TraEspReservaVehLista_EstadoDoc]
GO
ALTER TABLE [dbo].[Trn_TraEspReservaVehLista]  WITH CHECK ADD  CONSTRAINT [FK_Trn_TraEspReservaVehLista_Lista] FOREIGN KEY([IdLista])
REFERENCES [dbo].[Trn_TraEspListaRes] ([IdLista])
GO
ALTER TABLE [dbo].[Trn_TraEspReservaVehLista] CHECK CONSTRAINT [FK_Trn_TraEspReservaVehLista_Lista]
GO
ALTER TABLE [dbo].[Trn_TraEspReservaVehLista]  WITH CHECK ADD  CONSTRAINT [FK_Trn_TraEspReservaVehLista_Trn_TraEspResDetalle] FOREIGN KEY([TipDoc], [Reserva], [IdCia], [Item])
REFERENCES [dbo].[Trn_TraEspResDetalle] ([TipDoc], [Reserva], [IdCia], [Item])
GO
ALTER TABLE [dbo].[Trn_TraEspReservaVehLista] CHECK CONSTRAINT [FK_Trn_TraEspReservaVehLista_Trn_TraEspResDetalle]
GO
ALTER TABLE [dbo].[Trn_TraEspReservaVehLista]  WITH CHECK ADD  CONSTRAINT [FK_Trn_TraEspReservaVehLista_Vehiculos] FOREIGN KEY([IdVehiculo])
REFERENCES [dbo].[Vehiculos] ([IdVehiculo])
GO
ALTER TABLE [dbo].[Trn_TraEspReservaVehLista] CHECK CONSTRAINT [FK_Trn_TraEspReservaVehLista_Vehiculos]
GO
ALTER TABLE [dbo].[Trn_TraEspReservaVehLista]  WITH CHECK ADD  CONSTRAINT [CK_Trn_TraEspReservaVehLista_IdCia] CHECK  ((len([IdCia])>(0)))
GO
ALTER TABLE [dbo].[Trn_TraEspReservaVehLista] CHECK CONSTRAINT [CK_Trn_TraEspReservaVehLista_IdCia]
GO
ALTER TABLE [dbo].[Trn_TraEspReservaVehLista]  WITH CHECK ADD  CONSTRAINT [CK_Trn_TraEspReservaVehLista_IdEstado] CHECK  ((len([IdEstado])>(0)))
GO
ALTER TABLE [dbo].[Trn_TraEspReservaVehLista] CHECK CONSTRAINT [CK_Trn_TraEspReservaVehLista_IdEstado]
GO
ALTER TABLE [dbo].[Trn_TraEspReservaVehLista]  WITH CHECK ADD  CONSTRAINT [CK_Trn_TraEspReservaVehLista_TipDoc] CHECK  ((len([TipDoc])>(0)))
GO
ALTER TABLE [dbo].[Trn_TraEspReservaVehLista] CHECK CONSTRAINT [CK_Trn_TraEspReservaVehLista_TipDoc]
GO
CREATE TABLE [dbo].[Trn_TraEspCumFacturas](
	[TipDoc] [varchar](3) NOT NULL,
	[Cumplido] [int] NOT NULL,
	[IdCia] [char](2) NOT NULL,
	[TipFac] [varchar](3) NOT NULL,
	[Factura] [int] NOT NULL,
	[IdCiaFac] [char](2) NOT NULL,
	[IdCliente] [varchar](16) NOT NULL,
	[VrIngreso] [money] NOT NULL,
 CONSTRAINT [PK_Trn_TraEspCumFacturas] PRIMARY KEY CLUSTERED 
(
	[TipDoc] ASC,
	[Cumplido] ASC,
	[IdCia] ASC,
	[TipFac] ASC,
	[Factura] ASC,
	[IdCiaFac] 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].[Trn_TraEspCumFacturas] ADD  CONSTRAINT [DF__Trn_TraEs__TipDo__369C4574]  DEFAULT ('GIE') FOR [TipDoc]
GO

ALTER TABLE [dbo].[Trn_TraEspCumFacturas] ADD  CONSTRAINT [DF__Trn_TraEs__Cumpl__379069AD]  DEFAULT ((0)) FOR [Cumplido]
GO

ALTER TABLE [dbo].[Trn_TraEspCumFacturas] ADD  CONSTRAINT [DF__Trn_TraEs__IdCia__38848DE6]  DEFAULT ('01') FOR [IdCia]
GO

ALTER TABLE [dbo].[Trn_TraEspCumFacturas] ADD  CONSTRAINT [DF__Trn_TraEs__TipFa__3978B21F]  DEFAULT ('FCE') FOR [TipFac]
GO

ALTER TABLE [dbo].[Trn_TraEspCumFacturas] ADD  CONSTRAINT [DF__Trn_TraEs__IdCia__3A6CD658]  DEFAULT ('01') FOR [IdCiaFac]
GO

ALTER TABLE [dbo].[Trn_TraEspCumFacturas] ADD  CONSTRAINT [DF_Trn_TraEspCumFacturas_IdCliente]  DEFAULT ((0)) FOR [IdCliente]
GO

ALTER TABLE [dbo].[Trn_TraEspCumFacturas] ADD  CONSTRAINT [DF__Trn_TraEs__VrIng__3B60FA91]  DEFAULT ((0)) FOR [VrIngreso]
GO

ALTER TABLE [dbo].[Trn_TraEspCumFacturas]  WITH CHECK ADD  CONSTRAINT [FK_Trn_TraEspCumFacturas_TercCliente] FOREIGN KEY([IdCliente])
REFERENCES [dbo].[TercCliente] ([IdClie])
GO

ALTER TABLE [dbo].[Trn_TraEspCumFacturas] CHECK CONSTRAINT [FK_Trn_TraEspCumFacturas_TercCliente]
GO

ALTER TABLE [dbo].[Trn_TraEspCumFacturas]  WITH CHECK ADD  CONSTRAINT [FK_Trn_TraEspCumFacturas_Trn_Facturas] FOREIGN KEY([TipFac], [Factura], [IdCiaFac])
REFERENCES [dbo].[Trn_Facturas] ([TipDoc], [Factura], [IdCia])
GO

ALTER TABLE [dbo].[Trn_TraEspCumFacturas] CHECK CONSTRAINT [FK_Trn_TraEspCumFacturas_Trn_Facturas]
GO
--Ojo siempre sacar este fragmento de script
IF OBJECT_ID (N'UpdateVersion', N'U') IS NULL 
BEGIN
	CREATE TABLE UpdateVersion (
		Id int PRIMARY KEY,
		Modulo varchar(30) NOT NULL,
		VersionModulo int );
END
GO
IF OBJECT_ID (N'UpdateRuta', N'U') IS NULL 
BEGIN
	CREATE TABLE UpdateRuta (
		Id int PRIMARY KEY,
		IdModulo int NOT NULL,
		Ruta  varchar(250), 
		constraint FK_UpdateRuta_UpdateVersion_idModulo
		foreign key(idModulo)
		references UpdateVersion(Id)
		);
END
GO
--ACTUALIZAR LA VERSIÓN
IF NOT EXISTS(SELECT TOP 1 * FROM UpdateVersion WHERE Modulo = 'TransporteEspecial')
BEGIN
	INSERT INTO UpdateVersion VALUES(3, 'TransporteEspecial', 50044)
END
ELSE
BEGIN
	UPDATE UpdateVersion SET VersionModulo = 50044
END
GO
--ACTUALIZAR LA VERSIÓN
IF NOT EXISTS(SELECT TOP 1 * FROM UpdateRuta WHERE IdModulo = 'TransporteEspecial')
BEGIN
	INSERT INTO UpdateRuta VALUES(3, 3, 'C:\EspecialServidor')
END
--**********************************************
GO