
--Crear Tabla pro-horariotrabajo

if not exists(select * FROM INFORMATION_SCHEMA.TABLES  where Table_Name='pro_horarioEjecutores')
begin
EXEC _ini_creartabla 'pro_horarioEjecutores', 1
end


--Crear Tabla pro-ejecutores	

if not exists(select * FROM INFORMATION_SCHEMA.TABLES  where Table_Name='pro_ejecutores')
begin
EXEC _ini_creartabla 'pro_ejecutores', 1
end
--Crear Tabla pro-recursofisico

if not exists(select * FROM INFORMATION_SCHEMA.TABLES  where Table_Name='pro_recursoFisico')
begin
EXEC _ini_creartabla 'pro_recursoFisico', 1
end



---creacion columnas

if not exists(select * FROM INFORMATION_SCHEMA.COLUMNS  where Table_Name='pro_ejecutores' and column_name = 'costo' )
begin
alter table [pro_ejecutores] add [costo] float not NULL DEFAULT 0
end

if not exists(select * FROM INFORMATION_SCHEMA.COLUMNS  where Table_Name='pro_ejecutores' and column_name = 'unidadTiempo' )
begin
alter table [pro_ejecutores] add [unidadTiempo] varchar not NULL DEFAULT 0
end

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


if not exists(select * FROM INFORMATION_SCHEMA.COLUMNS  where Table_Name='pro_recursoFisico' and column_name = 'costo' )
begin
alter table [pro_recursoFisico] add [costo] float not NULL DEFAULT 0
end

if not exists(select * FROM INFORMATION_SCHEMA.COLUMNS  where Table_Name='pro_recursoFisico' and column_name = 'unidadTiempo' )
begin
alter table [pro_recursoFisico] add [unidadTiempo] varchar not NULL DEFAULT 0
end

if not exists(select * FROM INFORMATION_SCHEMA.COLUMNS  where Table_Name='pro_recursoFisico' and column_name = 'esPropio' )
begin	
alter table [pro_recursoFisico] add [esPropio] bit not NULL DEFAULT 0
end

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

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



-- insercion _columnas

IF NOT EXISTS(SELECT *FROM [_columnas] WHERE[tabla]='pro_ejecutores'
      AND [columna]='costo' AND[mostrarEnGrilla]=1 AND[ordenGrilla]=5AND[requerido]=1 AND [tipo]='float' AND[defecto]=0 AND [tamano] =100)
BEGIN
INSERT INTO [_columnas]
([tabla] ,[columna] ,[mostrarEnGrilla],[ordenGrilla],[requerido] ,[tipo],[defecto],[tamano])
 VALUES
 ('pro_ejecutores' ,'costo',1 ,5 ,1 ,'float' ,0 ,100)
END

IF NOT EXISTS(SELECT *FROM [_columnas] WHERE[tabla]='pro_ejecutores'
      AND [columna]='unidadTiempo' AND[mostrarEnGrilla]=1 AND[ordenGrilla]=5AND[requerido]=1 AND [tipo]='varchar' AND[defecto]=0 AND [tamano] =100)
BEGIN
INSERT INTO [_columnas]
([tabla] ,[columna] ,[mostrarEnGrilla],[ordenGrilla],[requerido] ,[tipo],[defecto],[tamano])
 VALUES
 ('pro_ejecutores' ,'unidadTiempo',1 ,5 ,1 ,'varchar' ,0 ,100)
END

IF NOT EXISTS(SELECT *FROM [_columnas] WHERE[tabla]='pro_ejecutores'
      AND [columna]='id_horarioEjecutores' AND[mostrarEnGrilla]=1 AND[ordenGrilla]=5AND[requerido]=1 AND [tipo]='int' AND[defecto]=0 AND [tamano] =100)
BEGIN
INSERT INTO [_columnas]
([tabla] ,[columna] ,[mostrarEnGrilla],[ordenGrilla],[requerido] ,[tipo],[defecto],[tamano])
 VALUES
 ('pro_ejecutores' ,'id_horarioEjecutores',1 ,5 ,1 ,'int' ,0 ,100)
END

IF NOT EXISTS(SELECT *FROM [_columnas] WHERE[tabla]='pro_recursoFisico'
      AND [columna]='costo' AND[mostrarEnGrilla]=1 AND[ordenGrilla]=5AND[requerido]=1 AND [tipo]='float' AND[defecto]=0 AND [tamano] =100)
BEGIN
INSERT INTO [_columnas]
([tabla] ,[columna] ,[mostrarEnGrilla],[ordenGrilla],[requerido] ,[tipo],[defecto],[tamano])
 VALUES
 ('pro_recursoFisico' ,'costo',1 ,5 ,1 ,'float' ,0 ,100)
END

IF NOT EXISTS(SELECT *FROM [_columnas] WHERE[tabla]='pro_recursoFisico'
      AND [columna]='unidadTiempo' AND[mostrarEnGrilla]=1 AND[ordenGrilla]=5AND[requerido]=1 AND [tipo]='varchar' AND[defecto]=0 AND [tamano] =100)
BEGIN
INSERT INTO [_columnas]
([tabla] ,[columna] ,[mostrarEnGrilla],[ordenGrilla],[requerido] ,[tipo],[defecto],[tamano])
 VALUES
 ('pro_recursoFisico' ,'unidadTiempo',1 ,5 ,1 ,'varchar' ,0 ,100)
END

IF NOT EXISTS(SELECT *FROM [_columnas] WHERE[tabla]='pro_recursoFisico'
      AND [columna]='esPropio' AND[mostrarEnGrilla]=1 AND[ordenGrilla]=5AND[requerido]=1 AND [tipo]='bit' AND[defecto]=0 AND [tamano] =100)
BEGIN
INSERT INTO [_columnas]
([tabla] ,[columna] ,[mostrarEnGrilla],[ordenGrilla],[requerido] ,[tipo],[defecto],[tamano])
 VALUES
 ('pro_recursoFisico' ,'esPropio',1 ,5 ,1 ,'bit' ,0 ,100)
END
 
IF NOT EXISTS(SELECT *FROM [_columnas] WHERE[tabla]='pro_recursoFisico'
      AND [columna]='id_equipo' AND[mostrarEnGrilla]=1 AND[ordenGrilla]=5AND[requerido]=1 AND [tipo]='int' AND[defecto]=0 AND [tamano] =100)
BEGIN
INSERT INTO [_columnas]
([tabla] ,[columna] ,[mostrarEnGrilla],[ordenGrilla],[requerido] ,[tipo],[defecto],[tamano])
 VALUES
 ('pro_recursoFisico' ,'id_equipo',1 ,5 ,1 ,'int' ,0 ,100)
END

IF NOT EXISTS(SELECT *FROM [_columnas] WHERE[tabla]='pro_recursoFisico'
      AND [columna]='id_catalogo' AND[mostrarEnGrilla]=1 AND[ordenGrilla]=5AND[requerido]=1 AND [tipo]='int' AND[defecto]=0 AND [tamano] =100)
BEGIN
INSERT INTO [_columnas]
([tabla] ,[columna] ,[mostrarEnGrilla],[ordenGrilla],[requerido] ,[tipo],[defecto],[tamano])
 VALUES
 ('pro_recursoFisico' ,'id_catalogo',1 ,5 ,1 ,'int' ,0 ,100)
END

IF NOT EXISTS(SELECT *FROM [_columnas] WHERE[tabla]='pro_ejecutores'
      AND [columna]='id_usuario' AND[mostrarEnGrilla]=1 AND[ordenGrilla]=5AND[requerido]=1 AND [tipo]='int' AND[defecto]=0 AND [tamano] =100)
BEGIN
INSERT INTO [_columnas]
([tabla] ,[columna] ,[mostrarEnGrilla],[ordenGrilla],[requerido] ,[tipo],[defecto],[tamano])
 VALUES
 ('pro_ejecutores' ,'id_usuario',1 ,5 ,1 ,'int' ,0 ,100)
END


 --Relaciones--------------------------------
 
IF NOT  EXISTS (SELECT * FROM sys.foreign_keys WHERE parent_object_id = OBJECT_ID(N'[dbo].[pro_ejecutores]') AND object_id = OBJECT_ID(N'[dbo].[fk_pro_ejecutores_id_horarioEjecutores]')  )
BEGIN
ALTER TABLE [pro_ejecutores] ADD CONSTRAINT
	[fk_pro_ejecutores_id_horarioEjecutores] FOREIGN KEY
	--nombre llave foranea--------tabla relacionada---------llave primaria------------
	([id_horarioEjecutores]) REFERENCES [pro_horarioEjecutores]	(id)
 
ALTER TABLE [dbo].[pro_ejecutores] CHECK CONSTRAINT [fk_pro_ejecutores_id_horarioEjecutores]
END

IF NOT  EXISTS (SELECT * FROM sys.foreign_keys WHERE parent_object_id = OBJECT_ID(N'[dbo].[pro_recursoFisico]') AND object_id = OBJECT_ID(N'[dbo].[fk_pro_recursoFisico_id_equipo]')  )
BEGIN
ALTER TABLE [pro_recursoFisico] ADD CONSTRAINT
	[fk_pro_recursoFisico_id_equipo] FOREIGN KEY
	--nombre llave foranea--------tabla relacionada---------llave primaria------------
	([id_equipo]) REFERENCES [equ_equipo]	(id)
 
ALTER TABLE [dbo].[pro_recursoFisico] CHECK CONSTRAINT [fk_pro_recursoFisico_id_equipo]
END

IF NOT  EXISTS (SELECT * FROM sys.foreign_keys WHERE parent_object_id = OBJECT_ID(N'[dbo].[pro_recursoFisico]') AND object_id = OBJECT_ID(N'[dbo].[fk_pro_recursoFisico_id_catalogo]')  )
BEGIN
ALTER TABLE [pro_recursoFisico] ADD CONSTRAINT
	[fk_pro_recursoFisico_id_catalogo] FOREIGN KEY
	--nombre llave foranea--------tabla relacionada---------llave primaria------------
	([id_catalogo]) REFERENCES [cat_catalogo]	(id)
 
ALTER TABLE [dbo].[pro_recursoFisico] CHECK CONSTRAINT [fk_pro_recursoFisico_id_catalogo]
END


IF NOT  EXISTS (SELECT * FROM sys.foreign_keys WHERE parent_object_id = OBJECT_ID(N'[dbo].[pro_ejecutores]') AND object_id = OBJECT_ID(N'[dbo].[fk_pro_ejecutores_id_usuario]')  )
BEGIN
ALTER TABLE [pro_ejecutores] ADD CONSTRAINT
	[fk_pro_ejecutores_id_usuario] FOREIGN KEY
	--nombre llave foranea--------tabla relacionada---------llave primaria------------
	([id_usuario]) REFERENCES [seg_usuario]	(id)
 
ALTER TABLE [dbo].[pro_ejecutores] CHECK CONSTRAINT [fk_pro_ejecutores_id_usuario]
END