cdoc2.sql

USE [master]
GO
/****** Object:  Database [cdoc]    Script Date: 20/02/2020 01:11:21 p. m. ******/
CREATE DATABASE [cdoc]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'cdoc', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.OBLIVION\MSSQL\DATA\cdoc.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'cdoc_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.OBLIVION\MSSQL\DATA\cdoc_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
GO
ALTER DATABASE [cdoc] SET COMPATIBILITY_LEVEL = 140
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [cdoc].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [cdoc] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [cdoc] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [cdoc] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [cdoc] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [cdoc] SET ARITHABORT OFF 
GO
ALTER DATABASE [cdoc] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [cdoc] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [cdoc] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [cdoc] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [cdoc] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [cdoc] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [cdoc] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [cdoc] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [cdoc] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [cdoc] SET  DISABLE_BROKER 
GO
ALTER DATABASE [cdoc] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [cdoc] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [cdoc] SET TRUSTWORTHY OFF 
GO
ALTER DATABASE [cdoc] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO
ALTER DATABASE [cdoc] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [cdoc] SET READ_COMMITTED_SNAPSHOT OFF 
GO
ALTER DATABASE [cdoc] SET HONOR_BROKER_PRIORITY OFF 
GO
ALTER DATABASE [cdoc] SET RECOVERY SIMPLE 
GO
ALTER DATABASE [cdoc] SET  MULTI_USER 
GO
ALTER DATABASE [cdoc] SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE [cdoc] SET DB_CHAINING OFF 
GO
ALTER DATABASE [cdoc] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) 
GO
ALTER DATABASE [cdoc] SET TARGET_RECOVERY_TIME = 60 SECONDS 
GO
ALTER DATABASE [cdoc] SET DELAYED_DURABILITY = DISABLED 
GO
ALTER DATABASE [cdoc] SET QUERY_STORE = OFF
GO
USE [cdoc]
GO
/****** Object:  Table [dbo].[arearps]    Script Date: 20/02/2020 01:11:22 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[arearps](
	[idarearp] [int] IDENTITY(1,1) NOT NULL,
	[arearp] [varchar](20) NOT NULL,
 CONSTRAINT [PK_arearps] PRIMARY KEY CLUSTERED 
(
	[idarearp] 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
/****** Object:  Table [dbo].[cancelaciones]    Script Date: 20/02/2020 01:11:22 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[cancelaciones](
	[idcan] [int] IDENTITY(1,1) NOT NULL,
	[iddoc] [int] NOT NULL,
	[comentarios] [varchar](200) NULL,
	[fecha] [datetime] NOT NULL,
 CONSTRAINT [PK_cancelaciones] PRIMARY KEY CLUSTERED 
(
	[idcan] 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
/****** Object:  Table [dbo].[carpetas]    Script Date: 20/02/2020 01:11:22 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[carpetas](
	[idcarpeta] [int] IDENTITY(1,1) NOT NULL,
	[carpeta] [varchar](20) NOT NULL,
 CONSTRAINT [PK_carpetas] PRIMARY KEY CLUSTERED 
(
	[idcarpeta] 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
/****** Object:  Table [dbo].[documentos]    Script Date: 20/02/2020 01:11:22 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[documentos](
	[iddoc] [int] IDENTITY(1,1) NOT NULL,
	[codigo] [varchar](50) NOT NULL,
	[doc] [varchar](100) NOT NULL,
	[idpersonal] [int] NOT NULL,
	[comentarios] [varchar](200) NOT NULL,
	[autorizo] [varchar](100) NOT NULL,
	[idrama] [int] NOT NULL,
	[idcarpeta] [int] NOT NULL,
	[activo] [int] NOT NULL,
 CONSTRAINT [PK_documentos] PRIMARY KEY CLUSTERED 
(
	[iddoc] 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
/****** Object:  Table [dbo].[historial]    Script Date: 20/02/2020 01:11:22 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[historial](
	[idhist] [int] IDENTITY(1,1) NOT NULL,
	[iddoc] [int] NOT NULL,
	[fecha] [datetime] NOT NULL,
	[comentarios] [varchar](200) NOT NULL,
 CONSTRAINT [PK_historial] PRIMARY KEY CLUSTERED 
(
	[idhist] 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
/****** Object:  Table [dbo].[mos]    Script Date: 20/02/2020 01:11:22 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[mos](
	[idmo] [int] IDENTITY(1,1) NOT NULL,
	[mo] [varchar](15) NOT NULL,
 CONSTRAINT [PK_mos] PRIMARY KEY CLUSTERED 
(
	[idmo] 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
/****** Object:  Table [dbo].[personales]    Script Date: 20/02/2020 01:11:22 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[personales](
	[idpersonal] [int] IDENTITY(1,1) NOT NULL,
	[nombre] [varchar](100) NOT NULL,
	[idpuesto] [int] NULL,
	[idmo] [int] NULL,
	[idarearp] [int] NULL,
 CONSTRAINT [PK_personales] PRIMARY KEY CLUSTERED 
(
	[idpersonal] 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
/****** Object:  Table [dbo].[puestos]    Script Date: 20/02/2020 01:11:22 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[puestos](
	[idpuesto] [int] IDENTITY(1,1) NOT NULL,
	[puesto] [varchar](30) NOT NULL,
 CONSTRAINT [PK_puestos] PRIMARY KEY CLUSTERED 
(
	[idpuesto] 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
/****** Object:  Table [dbo].[ramas]    Script Date: 20/02/2020 01:11:22 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ramas](
	[idrama] [int] IDENTITY(1,1) NOT NULL,
	[rama] [varchar](50) NOT NULL,
 CONSTRAINT [PK_ramas] PRIMARY KEY CLUSTERED 
(
	[idrama] 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].[cancelaciones]  WITH CHECK ADD  CONSTRAINT [FK_cancelaciones_documentos] FOREIGN KEY([iddoc])
REFERENCES [dbo].[documentos] ([iddoc])
GO
ALTER TABLE [dbo].[cancelaciones] CHECK CONSTRAINT [FK_cancelaciones_documentos]
GO
ALTER TABLE [dbo].[documentos]  WITH CHECK ADD  CONSTRAINT [FK_documentos_carpetas] FOREIGN KEY([idcarpeta])
REFERENCES [dbo].[carpetas] ([idcarpeta])
GO
ALTER TABLE [dbo].[documentos] CHECK CONSTRAINT [FK_documentos_carpetas]
GO
ALTER TABLE [dbo].[documentos]  WITH CHECK ADD  CONSTRAINT [FK_documentos_personales] FOREIGN KEY([idpersonal])
REFERENCES [dbo].[personales] ([idpersonal])
GO
ALTER TABLE [dbo].[documentos] CHECK CONSTRAINT [FK_documentos_personales]
GO
ALTER TABLE [dbo].[documentos]  WITH CHECK ADD  CONSTRAINT [FK_documentos_ramas] FOREIGN KEY([idrama])
REFERENCES [dbo].[ramas] ([idrama])
GO
ALTER TABLE [dbo].[documentos] CHECK CONSTRAINT [FK_documentos_ramas]
GO
ALTER TABLE [dbo].[historial]  WITH CHECK ADD  CONSTRAINT [FK_historial_documentos] FOREIGN KEY([iddoc])
REFERENCES [dbo].[documentos] ([iddoc])
GO
ALTER TABLE [dbo].[historial] CHECK CONSTRAINT [FK_historial_documentos]
GO
ALTER TABLE [dbo].[personales]  WITH CHECK ADD  CONSTRAINT [FK_personales_arearps] FOREIGN KEY([idarearp])
REFERENCES [dbo].[arearps] ([idarearp])
GO
ALTER TABLE [dbo].[personales] CHECK CONSTRAINT [FK_personales_arearps]
GO
ALTER TABLE [dbo].[personales]  WITH CHECK ADD  CONSTRAINT [FK_personales_mos] FOREIGN KEY([idmo])
REFERENCES [dbo].[mos] ([idmo])
GO
ALTER TABLE [dbo].[personales] CHECK CONSTRAINT [FK_personales_mos]
GO
ALTER TABLE [dbo].[personales]  WITH CHECK ADD  CONSTRAINT [FK_personales_puestos] FOREIGN KEY([idpuesto])
REFERENCES [dbo].[puestos] ([idpuesto])
GO
ALTER TABLE [dbo].[personales] CHECK CONSTRAINT [FK_personales_puestos]
GO
/****** Object:  StoredProcedure [dbo].[actDoc]    Script Date: 20/02/2020 01:11:22 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		emmanuel chavez
-- Create date: 
-- Description:	
-- =============================================
CREATE PROCEDURE [dbo].[actDoc] 
	-- Add the parameters for the stored procedure here
	@p0 int,
	@p1 varchar(50),
	@p2 varchar(100),
	@p3 varchar(100),
	@p4 varchar(200),
	@p5 varchar(100),
	@p6 varchar(50),
	@p7 varchar(20)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	declare @per int = (select personales.idpersonal from personales where personales.nombre = @p3)
	declare @ram int = (select ramas.idrama from ramas where ramas.rama = @p6)
	declare @car int = (select carpetas.idcarpeta from carpetas where carpetas.carpeta = @p7)

	UPDATE documentos SET
	codigo = @p1, doc = @p2, idpersonal = @per, comentarios = @p4, autorizo = @p5, idrama = @ram, idcarpeta = @car, activo = 1
	where documentos.iddoc = @p0
	

	insert into historial (iddoc, fecha, comentarios) values (@p0, CURRENT_TIMESTAMP, @p4)
END
GO
/****** Object:  StoredProcedure [dbo].[buscarCodigo]    Script Date: 20/02/2020 01:11:22 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		emmanuel chavez
-- Create date: 
-- Description:	
-- =============================================
CREATE PROCEDURE [dbo].[buscarCodigo]
	-- Add the parameters for the stored procedure here
	@p1 varchar(100)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	select documentos.iddoc from documentos where documentos.codigo = @p1
END
GO
/****** Object:  StoredProcedure [dbo].[buscarNombre]    Script Date: 20/02/2020 01:11:22 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		emmanuel chavez
-- Create date: 
-- Description:	
-- =============================================
CREATE PROCEDURE [dbo].[buscarNombre] 
	-- Add the parameters for the stored procedure here
	@p1 varchar(100)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	select documentos.iddoc from documentos where documentos.doc = @p1
END
GO
/****** Object:  StoredProcedure [dbo].[habDoc]    Script Date: 20/02/2020 01:11:22 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		emmanuel chavez
-- Create date: 
-- Description:	
-- =============================================
CREATE PROCEDURE [dbo].[habDoc]
	-- Add the parameters for the stored procedure here
	@p1 int,
	@p2 varchar(200)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	update documentos set activo = 1 where documentos.iddoc = @p1
	insert into historial(iddoc, fecha, comentarios) values (@p1, CURRENT_TIMESTAMP, @p2)
END
GO
/****** Object:  StoredProcedure [dbo].[regArearp]    Script Date: 20/02/2020 01:11:22 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		emmanuel chavez
-- Create date: 
-- Description:	
-- =============================================
CREATE PROCEDURE [dbo].[regArearp] 
	-- Add the parameters for the stored procedure here
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	insert into arearps (arearp) values ('Quebalix 1')
	insert into arearps (arearp) values ('Quebalix 2')
	insert into arearps (arearp) values ('Quebalix 3')
	insert into arearps (arearp) values ('Quebalix 4')
	insert into arearps (arearp) values ('Comunicaciones')
	insert into arearps (arearp) values ('C. Herramientas')
	insert into arearps (arearp) values ('U. Ligeras')
	insert into arearps (arearp) values ('Oficinas')
END
GO
/****** Object:  StoredProcedure [dbo].[regCan]    Script Date: 20/02/2020 01:11:22 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		emmanuel chavez
-- Create date: 
-- Description:	
-- =============================================
CREATE PROCEDURE [dbo].[regCan]
	-- Add the parameters for the stored procedure here
	@p1 int,
	@p2 varchar(200)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	update documentos set activo = 0 where documentos.iddoc = @p1
	insert into cancelaciones (iddoc, fecha, comentarios) values (@p1, CURRENT_TIMESTAMP, @p2)
	insert into historial(iddoc, fecha, comentarios) values (@p1, CURRENT_TIMESTAMP, @p2)
END
GO
/****** Object:  StoredProcedure [dbo].[regCarpetas]    Script Date: 20/02/2020 01:11:22 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		emmanuel chavez
-- Create date: 
-- Description:	
-- =============================================
CREATE PROCEDURE [dbo].[regCarpetas] 
	-- Add the parameters for the stored procedure here
	  
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here

INSERT INTO carpetas (carpeta)VALUES('Formatos')
INSERT INTO carpetas (carpeta)VALUES('Procedimientos')
INSERT INTO carpetas (carpeta)VALUES('Instructivos')
INSERT INTO carpetas (carpeta)VALUES('Ayuda visual')

END
GO
/****** Object:  StoredProcedure [dbo].[regDoc]    Script Date: 20/02/2020 01:11:22 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		emmanuel chavez
-- Create date: 
-- Description:	
-- =============================================
CREATE PROCEDURE [dbo].[regDoc] 
	-- Add the parameters for the stored procedure here
	@p1 varchar(50),
	@p2 varchar(100),
	@p3 varchar(100),
	@p4 varchar(200),
	@p5 varchar(100),
	@p6 varchar(50),
	@p7 varchar(20)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	declare @per int = (select personales.idpersonal from personales where personales.nombre = @p3)
	declare @ram int = (select ramas.idrama from ramas where ramas.rama = @p6)
	declare @car int = (select carpetas.idcarpeta from carpetas where carpetas.carpeta = @p7)

	insert into documentos (codigo, doc, idpersonal, comentarios, autorizo, idrama, idcarpeta, activo) 
	values (@p1, @p2, @per, @p4, @p5, @ram, @car, 1)

	declare @idoc int = (select top (1) documentos.iddoc from documentos where documentos.codigo = @p1)
	insert into historial (iddoc, fecha, comentarios) values (@idoc, CURRENT_TIMESTAMP, @p4)
END
GO
/****** Object:  StoredProcedure [dbo].[regHis]    Script Date: 20/02/2020 01:11:22 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		emmanuel chavez
-- Create date: 
-- Description:	
-- =============================================
CREATE PROCEDURE [dbo].[regHis] 
	-- Add the parameters for the stored procedure here
	@p1 int,
	@p2 varchar(200)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	insert into historial (iddoc, fecha, comentarios) values (@p1, CURRENT_TIMESTAMP, @p2)
END
GO
/****** Object:  StoredProcedure [dbo].[regMos]    Script Date: 20/02/2020 01:11:22 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		emmanuel chavez
-- Create date: 
-- Description:	
-- =============================================
CREATE PROCEDURE [dbo].[regMos] 
	-- Add the parameters for the stored procedure here
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	INSERT INTO mos (mo)VALUES('Mantenimiento')
	INSERT INTO mos (mo)VALUES('Operacion')
END
GO
/****** Object:  StoredProcedure [dbo].[regPers]    Script Date: 20/02/2020 01:11:22 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		emmanuel chavez
-- Create date: 
-- Description:	
-- =============================================
CREATE PROCEDURE [dbo].[regPers] 
	-- Add the parameters for the stored procedure here
	@p1 varchar(100),
	@p2 varchar(30),
	@p3 varchar(15),
	@p4 varchar(20)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	declare @pues int = (select puestos.idpuesto from puestos where puestos.puesto = @p2)
	declare @mu int = (select mos.idmo from mos where mos.mo = @p3)
	declare @arp int = (select arearps.idarearp from arearps where arearps.arearp = @p4)

	insert into personales (nombre, idpuesto, idmo, idarearp) values (@p1, @pues, @mu, @arp)
END
GO
/****** Object:  StoredProcedure [dbo].[regPuestos]    Script Date: 20/02/2020 01:11:22 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		emmanuel chavez
-- Create date: 
-- Description:	
-- =============================================
CREATE PROCEDURE [dbo].[regPuestos] 
	-- Add the parameters for the stored procedure here
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	INSERT INTO puestos (puesto)VALUES('Gerente')
	INSERT INTO puestos (puesto)VALUES('Superintendente')
	INSERT INTO puestos (puesto)VALUES('Jefe')
	INSERT INTO puestos (puesto)VALUES('Supervisor')
	INSERT INTO puestos (puesto)VALUES('Operador')

END
GO
/****** Object:  StoredProcedure [dbo].[regRamas]    Script Date: 20/02/2020 01:11:22 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		emmanuel chavez
-- Create date: 
-- Description:	
-- =============================================
CREATE PROCEDURE [dbo].[regRamas]
	-- Add the parameters for the stored procedure here
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	INSERT INTO ramas (rama) VALUES ('Quebalix 1 - MTTO')
	INSERT INTO ramas (rama) VALUES ('Quebalix 2 - MTTO')
	INSERT INTO ramas (rama) VALUES ('Quebalix 3 - MTTO')
	INSERT INTO ramas (rama) VALUES ('Quebalix 4 - MTTO')
	INSERT INTO ramas (rama) VALUES ('Quebalix-Generales  - MTTO')
	INSERT INTO ramas (rama) VALUES ('Quebalix 1 - OP')
	INSERT INTO ramas (rama) VALUES ('Quebalix 2 - OP')
	INSERT INTO ramas (rama) VALUES ('Quebalix 3 - OP')
	INSERT INTO ramas (rama) VALUES ('Quebalix 4 - OP')
	INSERT INTO ramas (rama) VALUES ('Quebalix-Generales - OP')
	INSERT INTO ramas (rama) VALUES ('Comunicaciones')
	INSERT INTO ramas (rama) VALUES ('C. Herramientas')
	INSERT INTO ramas (rama) VALUES ('U. Ligeras')
	INSERT INTO ramas (rama) VALUES ('Oficinas')

END
GO
/****** Object:  StoredProcedure [dbo].[validarDatos]    Script Date: 20/02/2020 01:11:22 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		emmanuel chavez
-- Create date: 
-- Description:	
-- =============================================
CREATE PROCEDURE [dbo].[validarDatos] 
	-- Add the parameters for the stored procedure here
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	select count (ramas.idrama) from ramas
END
GO
/****** Object:  StoredProcedure [dbo].[verCancelados]    Script Date: 20/02/2020 01:11:22 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		emmanuel chavez
-- Create date: 
-- Description:	
-- =============================================
CREATE PROCEDURE [dbo].[verCancelados] 
	-- Add the parameters for the stored procedure here
	 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT cancelaciones.fecha, documentos.codigo, documentos.doc, cancelaciones.comentarios
	FROM cancelaciones INNER JOIN
    documentos ON cancelaciones.iddoc = documentos.iddoc
END
GO
/****** Object:  StoredProcedure [dbo].[verDoc]    Script Date: 20/02/2020 01:11:22 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		emanuel chavez
-- Create date: 
-- Description:	
-- =============================================
CREATE PROCEDURE [dbo].[verDoc] 
	-- Add the parameters for the stored procedure here
	@p1 int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT documentos.iddoc, documentos.codigo, documentos.doc, personales.nombre, documentos.comentarios, documentos.autorizo, ramas.rama, carpetas.carpeta
	FROM documentos INNER JOIN
		 personales ON documentos.idpersonal = personales.idpersonal INNER JOIN
		 ramas ON documentos.idrama = ramas.idrama INNER JOIN
		 carpetas ON documentos.idcarpeta = carpetas.idcarpeta
	where documentos.iddoc = @p1

END
GO
/****** Object:  StoredProcedure [dbo].[verDocs]    Script Date: 20/02/2020 01:11:22 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		emmanuel chavez
-- Create date: 
-- Description:	
-- =============================================
CREATE PROCEDURE [dbo].[verDocs] 
	-- Add the parameters for the stored procedure here
	@p1 varchar(50),
	@p2 varchar(20)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	declare @r int = (select ramas.idrama from ramas where ramas.rama = @p1)
	declare @c int = (select carpetas.idcarpeta from carpetas where carpetas.carpeta = @p2)

	select documentos.iddoc, documentos.codigo, documentos.doc from documentos where documentos.idrama = @r and documentos.idcarpeta = @c and documentos.activo = 1
END
GO
/****** Object:  StoredProcedure [dbo].[verHist]    Script Date: 20/02/2020 01:11:22 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		emmanuel chavez
-- Create date: 
-- Description:	
-- =============================================
CREATE PROCEDURE [dbo].[verHist] 
	-- Add the parameters for the stored procedure here
	@p1 int 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	select historial.fecha, historial.comentarios from historial where historial.iddoc = @p1
END
GO
/****** Object:  StoredProcedure [dbo].[verRevs]    Script Date: 20/02/2020 01:11:22 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		emmanuel chavez
-- Create date: 
-- Description:	
-- =============================================
CREATE PROCEDURE [dbo].[verRevs] 
	-- Add the parameters for the stored procedure here
	@p1 int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	select top (2) historial.fecha from historial where historial.iddoc = @p1 order by fecha desc
END
GO
USE [master]
GO
ALTER DATABASE [cdoc] SET  READ_WRITE 
GO