


--TABLA DOC_ALQUILER

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

if not exists(select * FROM INFORMATION_SCHEMA.TABLES  where Table_Name='doc_documento.alquiler')
EXEC _ini_creartabla 'doc_documento.alquiler', 0
go
if not exists(select * FROM INFORMATION_SCHEMA.TABLES  where Table_Name='alq_tipoTarifa')
EXEC _ini_creartabla 'alq_tipoTarifa', 1
go
if not exists(select * FROM INFORMATION_SCHEMA.TABLES  where Table_Name='alq_tarifa')
EXEC _ini_creartabla 'alq_tarifa', 1
go


--DOC_DOCUMENTO_ALQUILER
if not exists(select * FROM INFORMATION_SCHEMA.COLUMNS  where Table_Name='doc_documento.alquiler' and column_name = 'fechaFin_ff' )
begin
alter table [doc_documento.alquiler] add [fechaFin_ff] smalldatetime NULL DEFAULT (((1)/(1))/(1900))
end

if not exists(select * FROM INFORMATION_SCHEMA.COLUMNS  where Table_Name='doc_documento.alquiler' and column_name = 'mesVencido' )
begin
alter table [doc_documento.alquiler] add [mesVencido] bit NOT NULL DEFAULT ((1))
end

if not exists(select * FROM INFORMATION_SCHEMA.COLUMNS  where Table_Name='doc_documento.alquiler' and column_name = 'observaciones' )
begin
alter table [doc_documento.alquiler] add [observaciones]  varchar(3000) NULL
end



--TABLA ALQ_TARIFA

if not exists(select * FROM INFORMATION_SCHEMA.COLUMNS  where Table_Name='alq_tarifa' and column_name = 'id_itemDocumento' )
begin
alter table [alq_tarifa] add [id_itemDocumento]  int NOT NULL DEFAULT ((0))
end

if not exists(select * FROM INFORMATION_SCHEMA.COLUMNS  where Table_Name='alq_tarifa' and column_name = 'id_detalleAlquiler' )
begin
alter table [alq_tarifa] add [id_detalleAlquiler]  int NOT NULL DEFAULT ((0))
end

if not exists(select * FROM INFORMATION_SCHEMA.COLUMNS  where Table_Name='alq_tarifa' and column_name = 'id_tipoTarifa' )
begin
alter table [alq_tarifa] add [id_tipoTarifa]  int NOT NULL DEFAULT ((0))
end

if not exists(select * FROM INFORMATION_SCHEMA.COLUMNS  where Table_Name='alq_tarifa' and column_name = 'horasIncluidas' )
begin
alter table [alq_tarifa] add [horasIncluidas] [float] NOT NULL DEFAULT ((0.0))
end

IF NOT  EXISTS (SELECT * FROM sys.foreign_keys WHERE parent_object_id = OBJECT_ID(N'[dbo].[alq_tarifa]') AND object_id = OBJECT_ID(N'[dbo].[FK_alq_tarifa_id_itemDocumento]')  )
ALTER TABLE [alq_tarifa] ADD CONSTRAINT
	FK_alq_tarifa_id_itemDocumento FOREIGN KEY
	([id_itemDocumento]) REFERENCES [doc_itemDocumento]
	(id) 
ALTER TABLE [dbo].[alq_tarifa] CHECK CONSTRAINT [FK_alq_tarifa_id_itemDocumento]
GO


IF NOT  EXISTS (SELECT * FROM sys.foreign_keys WHERE parent_object_id = OBJECT_ID(N'[dbo].[alq_tarifa]') AND object_id = OBJECT_ID(N'[dbo].[FK_alq_tarifa_id_detalleAlquiler]')  )
ALTER TABLE [alq_tarifa] ADD CONSTRAINT
	FK_alq_tarifa_id_detalleAlquiler FOREIGN KEY
	([id_detalleAlquiler]) REFERENCES [equ_detalleAlquiler]
	(id) 
ALTER TABLE [dbo].[alq_tarifa] CHECK CONSTRAINT [FK_alq_tarifa_id_detalleAlquiler]
GO

IF NOT  EXISTS (SELECT * FROM sys.foreign_keys WHERE parent_object_id = OBJECT_ID(N'[dbo].[alq_tarifa]') AND object_id = OBJECT_ID(N'[dbo].[FK_alq_tarifa_id_tipoTarifa ]')  )
ALTER TABLE [alq_tarifa] ADD CONSTRAINT
	FK_alq_tarifa_id_tipoTarifa FOREIGN KEY
	([id_tipoTarifa]) REFERENCES [alq_tipoTarifa]
	(id)
ALTER TABLE [dbo].[alq_tarifa] CHECK CONSTRAINT [FK_alq_tarifa_id_tipoTarifa]
GO


--EQU_DETALLEALQUILER ACTUALIZACION

if not exists(select * FROM INFORMATION_SCHEMA.COLUMNS  where Table_Name='equ_detalleAlquiler' and column_name = 'cantidadSolicitada' )
begin
alter table [equ_detalleAlquiler] add [cantidadSolicitada]  int NOT NULL DEFAULT ((0))
end
if not exists(select * FROM INFORMATION_SCHEMA.COLUMNS  where Table_Name='equ_detalleAlquiler' and column_name = 'cantidadAlquilada' )
begin
alter table [equ_detalleAlquiler] add [cantidadAlquilada]  int NOT NULL DEFAULT ((0))
end
if not exists(select * FROM INFORMATION_SCHEMA.COLUMNS  where Table_Name='equ_detalleAlquiler' and column_name = 'id_catalogo' )
begin
alter table [equ_detalleAlquiler] add [id_catalogo]  int NOT NULL DEFAULT ((0))
end


IF not  EXISTS (SELECT * FROM sys.foreign_keys WHERE parent_object_id = OBJECT_ID(N'[dbo].[equ_detalleAlquiler]') AND object_id = OBJECT_ID(N'[dbo].[FK_equ_detalleAlquiler_id_catalogo]')  )
 ALTER TABLE [equ_detalleAlquiler] ADD CONSTRAINT
	FK_equ_detalleAlquiler_id_catalogo FOREIGN KEY
	([id_catalogo]) REFERENCES [cat_catalogo]
	(id) ON UPDATE  NO ACTION ON DELETE  NO ACTION 
GO


--ELIMINA RELACION DE ID_ALQUILER PARA VINCULARLA CON ID_DOCUMENTO.ALQUILER 
IF   EXISTS (SELECT * FROM sys.foreign_keys WHERE parent_object_id = OBJECT_ID(N'[dbo].[equ_detalleAlquiler]') AND object_id = OBJECT_ID(N'[dbo].[FK_equ_detalleAlquiler_id_alquiler]')  )
ALTER TABLE [dbo].[equ_detalleAlquiler] drop constraint FK_equ_detalleAlquiler_id_alquiler
GO

if not exists(select * FROM INFORMATION_SCHEMA.COLUMNS  where Table_Name='equ_detalleAlquiler' and column_name = 'id_documento.alquiler' )
begin
EXEC sp_rename 'equ_detalleAlquiler.id_alquiler', 'id_documento.alquiler', 'COLUMN';
end




IF not  EXISTS (SELECT * FROM sys.foreign_keys WHERE parent_object_id = OBJECT_ID(N'[dbo].[equ_detalleAlquiler]') AND object_id = OBJECT_ID(N'[dbo].[FK_equ_detalleAlquiler_id_documento_alquiler]')  )
ALTER TABLE [dbo].[equ_detalleAlquiler]  WITH CHECK ADD  CONSTRAINT [FK_equ_detalleAlquiler_id_documento_alquiler] FOREIGN KEY([id_documento.alquiler])
REFERENCES [dbo].[doc_documento.alquiler] ([id])
GO


--ELIMINA   ID_ALQUILER EN EQU_TRAZABILIDAD
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_equ_trazabilidad_id_alquiler]') AND parent_object_id = OBJECT_ID(N'[dbo].[equ_trazabilidad]'))
ALTER TABLE [dbo].[equ_trazabilidad] DROP CONSTRAINT [FK_equ_trazabilidad_id_alquiler]
GO

if not exists(select * FROM INFORMATION_SCHEMA.COLUMNS  where Table_Name='equ_trazabilidad' and column_name = 'id_documento.alquiler' )
begin
EXEC sp_rename 'equ_trazabilidad.id_alquiler', 'id_documento.alquiler', 'COLUMN';
end 

IF NOT  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_equ_trazabilidad_id_documento_alquiler]') AND parent_object_id = OBJECT_ID(N'[dbo].[equ_trazabilidad]'))
ALTER TABLE [dbo].[equ_trazabilidad]  WITH CHECK ADD  CONSTRAINT [FK_equ_trazabilidad_id_documento_alquiler] FOREIGN KEY([id_documento.alquiler])
REFERENCES [dbo].[doc_documento.alquiler] ([id])
GO


IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_equ_pagoAlquiler_id_alquiler]') AND parent_object_id = OBJECT_ID(N'[dbo].[equ_pagoAlquiler]'))
ALTER TABLE [dbo].[equ_pagoAlquiler] DROP CONSTRAINT [FK_equ_pagoAlquiler_id_alquiler]
GO

if not exists(select * FROM INFORMATION_SCHEMA.COLUMNS  where Table_Name='equ_pagoAlquiler' and column_name = 'id_documento.alquiler' )
begin
EXEC sp_rename 'equ_pagoAlquiler.id_alquiler', 'id_documento.alquiler', 'COLUMN';
end

IF NOT  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_equ_pagoAlquiler_id_documento_alquiler]') AND parent_object_id = OBJECT_ID(N'[dbo].[equ_pagoAlquiler]'))
ALTER TABLE [dbo].[equ_pagoAlquiler]  WITH CHECK ADD  CONSTRAINT [FK_equ_pagoAlquiler_id_documento_alquiler] FOREIGN KEY([id_documento.alquiler])
REFERENCES [dbo].[doc_documento.alquiler] ([id])
GO






----TABLA ALQ_TIPOTARIFA 
if not exists(select * FROM INFORMATION_SCHEMA.COLUMNS  where Table_Name='alq_tipoTarifa' and column_name = 'valor' )
begin
alter table alq_tipoTarifa add  valor decimal(10,1) default 0.0
end




----INSERTAR DATOS ALQ_TIPOTARIFA

declare @uid varchar(40)
set @uid =(select uid from seg_usuario where id=1)

IF NOT EXISTS(SELECT * FROM alq_tipotarifa )
BEGIN
insert into alq_tipotarifa (uid,eid,tipoTarifa,tipoTarifa_codigo)values(@uid,'01','elementoAlquilado','1')
insert into alq_tipotarifa (uid,eid,tipoTarifa,tipoTarifa_codigo)values(@uid,'01','operador','2')
insert into alq_tipotarifa (uid,eid,tipoTarifa,tipoTarifa_codigo)values(@uid,'01','servicioAdicional','3')
insert into alq_tipotarifa (uid,eid,tipoTarifa,tipoTarifa_codigo)values(@uid,'01','horaAdicional','4')
insert into alq_tipotarifa (uid,eid,tipoTarifa,tipoTarifa_codigo)values(@uid,'01','serviciosPeriodo','5')
insert into alq_tipotarifa (uid,eid,tipoTarifa,tipoTarifa_codigo)values(@uid,'01','elementoFaltante','6')
insert into alq_tipotarifa (uid,eid,tipoTarifa,tipoTarifa_codigo)values(@uid,'01','horaStandby','7')
insert into alq_tipotarifa (uid,eid,tipoTarifa,tipoTarifa_codigo)values(@uid,'01','horasUso','8')

END











   
