BD Presupuesto Script

DB_Presupuesto.sql

USE [Presupuesto]
GO
/****** Object:  Table [dbo].[Categorias]    Script Date: 22/11/2024 06:24:54 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Categorias](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[Nombre] [nvarchar](50) NOT NULL,
	[TipoOperacionId] [int] NOT NULL,
	[UsuarioId] [int] NOT NULL,
 CONSTRAINT [PK_Categorias] 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
/****** Object:  Table [dbo].[Cuentas]    Script Date: 22/11/2024 06:24:54 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Cuentas](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[Nombre] [nvarchar](50) NOT NULL,
	[TipoCuentaId] [int] NOT NULL,
	[Balance] [decimal](18, 2) NOT NULL,
	[Descripcion] [nvarchar](1000) NULL,
 CONSTRAINT [PK_Cuentas] 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
/****** Object:  Table [dbo].[TipoOperaciones]    Script Date: 22/11/2024 06:24:54 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TipoOperaciones](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[Descripcion] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_TipoOperaciones] 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
/****** Object:  Table [dbo].[TiposCuentas]    Script Date: 22/11/2024 06:24:54 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TiposCuentas](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[Nombre] [nvarchar](50) NOT NULL,
	[UsuarioId] [int] NOT NULL,
	[Orden] [int] NOT NULL,
 CONSTRAINT [PK_TiposCuentas] 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
/****** Object:  Table [dbo].[Transacciones]    Script Date: 22/11/2024 06:24:54 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Transacciones](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[UsuarioId] [int] NOT NULL,
	[FechaTransaccion] [datetime] NOT NULL,
	[Monto] [decimal](18, 2) NOT NULL,
	[Nota] [nvarchar](1000) NULL,
	[CuentaId] [int] NOT NULL,
	[CategoriaId] [int] NOT NULL,
 CONSTRAINT [PK_Transacciones] 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
/****** Object:  Table [dbo].[Usuarios]    Script Date: 22/11/2024 06:24:54 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Usuarios](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[Email] [nvarchar](256) NOT NULL,
	[EmailNormalizado] [nvarchar](256) NOT NULL,
	[PasswordHash] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_Usuarios] 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] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[Categorias]  WITH CHECK ADD  CONSTRAINT [FK_Categorias_TipoOperaciones] FOREIGN KEY([TipoOperacionId])
REFERENCES [dbo].[TipoOperaciones] ([id])
GO
ALTER TABLE [dbo].[Categorias] CHECK CONSTRAINT [FK_Categorias_TipoOperaciones]
GO
ALTER TABLE [dbo].[Categorias]  WITH CHECK ADD  CONSTRAINT [FK_Categorias_Usuarios] FOREIGN KEY([UsuarioId])
REFERENCES [dbo].[Usuarios] ([id])
GO
ALTER TABLE [dbo].[Categorias] CHECK CONSTRAINT [FK_Categorias_Usuarios]
GO
ALTER TABLE [dbo].[Cuentas]  WITH CHECK ADD  CONSTRAINT [FK_Cuentas_TiposCuentas] FOREIGN KEY([TipoCuentaId])
REFERENCES [dbo].[TiposCuentas] ([id])
GO
ALTER TABLE [dbo].[Cuentas] CHECK CONSTRAINT [FK_Cuentas_TiposCuentas]
GO
ALTER TABLE [dbo].[TiposCuentas]  WITH CHECK ADD  CONSTRAINT [FK_TiposCuentas_Usuarios] FOREIGN KEY([UsuarioId])
REFERENCES [dbo].[Usuarios] ([id])
GO
ALTER TABLE [dbo].[TiposCuentas] CHECK CONSTRAINT [FK_TiposCuentas_Usuarios]
GO
ALTER TABLE [dbo].[Transacciones]  WITH CHECK ADD  CONSTRAINT [FK_Transacciones_Categorias] FOREIGN KEY([CategoriaId])
REFERENCES [dbo].[Categorias] ([id])
GO
ALTER TABLE [dbo].[Transacciones] CHECK CONSTRAINT [FK_Transacciones_Categorias]
GO
ALTER TABLE [dbo].[Transacciones]  WITH CHECK ADD  CONSTRAINT [FK_Transacciones_Cuentas] FOREIGN KEY([CuentaId])
REFERENCES [dbo].[Cuentas] ([id])
GO
ALTER TABLE [dbo].[Transacciones] CHECK CONSTRAINT [FK_Transacciones_Cuentas]
GO
ALTER TABLE [dbo].[Transacciones]  WITH CHECK ADD  CONSTRAINT [FK_Transacciones_Usuarios] FOREIGN KEY([UsuarioId])
REFERENCES [dbo].[Usuarios] ([id])
GO
ALTER TABLE [dbo].[Transacciones] CHECK CONSTRAINT [FK_Transacciones_Usuarios]
GO
/****** Object:  StoredProcedure [dbo].[Buscar_TransaccionPorID]    Script Date: 22/11/2024 06:24:54 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[Buscar_TransaccionPorID] 
	@id int,
	@usuarioId int
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    SELECT * FROM Transacciones t
	INNER JOIN Categorias c	ON c.id = t.CategoriaId
	WHERE t.id = @id AND t.UsuarioId = @usuarioId
END
GO
/****** Object:  StoredProcedure [dbo].[ReporteMensual]    Script Date: 22/11/2024 06:24:54 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>

exec ReporteMensual 1,2024
-- =============================================
*/
CREATE PROCEDURE [dbo].[ReporteMensual] 
	@usuarioId int,
	@anio int
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	select MONTH(t.FechaTransaccion) Mes,SUM(t.Monto) Monto, cat.TipoOperacionId
	from Transacciones t
	inner join Categorias cat
	on cat.id = t.CategoriaId
	where 
	t.UsuarioId = @usuarioId and
	YEAR(t.FechaTransaccion)= @anio
	GROUP BY MONTH(t.FechaTransaccion),cat.TipoOperacionId
	
END
GO
/****** Object:  StoredProcedure [dbo].[ReporteSemanal]    Script Date: 22/11/2024 06:24:54 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>

exec ReporteSemanal 1,'2022-01-01','2024-10-31'
-- =============================================
*/
CREATE PROCEDURE [dbo].[ReporteSemanal] 
	@usuarioId int,
	@fechaInicio date,
	@fechaFin date
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	select DATEDIFF(d,@fechaInicio,FechaTransaccion) / 7 + 1 Semana,SUM(t.Monto) Monto, cat.TipoOperacionId
	from Transacciones t
	inner join Categorias cat
	on cat.id = t.CategoriaId
	where 
	t.UsuarioId = @usuarioId and
	FechaTransaccion between @fechaInicio and @fechaFin
	Group by DATEDIFF(d,@fechaInicio,FechaTransaccion) / 7, cat.TipoOperacionId
END
GO
/****** Object:  StoredProcedure [dbo].[selTransaccionPorCuentaId]    Script Date: 22/11/2024 06:24:54 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[selTransaccionPorCuentaId]
	-- Add the parameters for the stored procedure here
	@CuentaId int,
	@UsuarioId int,
	@FechaInicio Datetime,
	@FechaFin Datetime
AS
BEGIN
	
	SET NOCOUNT ON;

	SELECT t.id, t.Monto, t.FechaTransaccion, c.Nombre as Categoria,
	cu.Nombre as Cuenta, c.TipoOperacionId
	FROM Transacciones t
	INNER JOIN Categorias c	ON c.id = t.CategoriaId
	INNER JOIN Cuentas cu ON cu.id = t.CuentaId

	WHERE t.CuentaId = @CuentaId and t.UsuarioId = @UsuarioId
	AND t.FechaTransaccion BETWEEN @FechaInicio AND @FechaFin
    
END
GO
/****** Object:  StoredProcedure [dbo].[selTransaccionPorUsuarioId]    Script Date: 22/11/2024 06:24:54 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[selTransaccionPorUsuarioId]
	-- Add the parameters for the stored procedure here
	@UsuarioId int,
	@FechaInicio Datetime,
	@FechaFin Datetime
AS
BEGIN
	
	SET NOCOUNT ON;

	SELECT t.id, t.Monto, t.FechaTransaccion, c.Nombre as Categoria,
	cu.Nombre as Cuenta, c.TipoOperacionId,t.Nota
	FROM Transacciones t
	INNER JOIN Categorias c	ON c.id = t.CategoriaId
	INNER JOIN Cuentas cu ON cu.id = t.CuentaId

	WHERE t.UsuarioId = @UsuarioId
	AND t.FechaTransaccion BETWEEN @FechaInicio AND @FechaFin
	ORDER BY t.FechaTransaccion DESC
    
END
GO
/****** Object:  StoredProcedure [dbo].[TiposCuentas_Insertar]    Script Date: 22/11/2024 06:24:54 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Martin Castro
-- Create date: 16/07/2024
-- Description:	Inserta un tipo de Cuenta
-- =============================================
CREATE PROCEDURE [dbo].[TiposCuentas_Insertar]
	
	@Nombre nvarchar(50),
	@UsuarioId int
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @Orden int;

	SELECT @Orden = COALESCE(MAX(Orden),0) + 1
	FROM TiposCuentas
	WHERE UsuarioId = @UsuarioId

	INSERT INTO TiposCuentas(Nombre,UsuarioId,Orden)
	VALUES (@Nombre,@UsuarioId,@Orden)

	SELECT SCOPE_IDENTITY();
END
GO
/****** Object:  StoredProcedure [dbo].[Transacciones_Actualizar]    Script Date: 22/11/2024 06:24:54 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[Transacciones_Actualizar] 
	@Id int,
	@FechaTransaccion DateTime,
	@Monto decimal(18,2),
	@MontoAnterior decimal(18,2),
	@CuentaId int,
	@CuentaAnteriorId int,
	@CategoriaId int,
	@Nota nvarchar(1000)= NULL

AS
BEGIN

	SET NOCOUNT ON;
	--Revertir tran anterior
	UPDATE Cuentas SET Balance -= @MontoAnterior WHERE Id = @CuentaAnteriorId

	--Realizar nueva tran
	UPDATE Cuentas SET Balance += @Monto WHERE Id = @CuentaId

	UPDATE Transacciones SET Monto = ABS(@Monto), FechaTransaccion = @FechaTransaccion, CategoriaId = @CategoriaId, CuentaId = @CuentaId,
	Nota = @Nota WHERE id = @Id


	
END
GO
/****** Object:  StoredProcedure [dbo].[Transacciones_Borrar]    Script Date: 22/11/2024 06:24:54 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[Transacciones_Borrar]
	@Id int
AS
BEGIN
	
	SET NOCOUNT ON;

	DECLARE @Monto decimal(18,2);
	DECLARE @CuentaId int;
	DECLARE @TipoOperacion int;
	DECLARE @FactorMultiplicativo int = 1;

	SELECT  @Monto = Monto, @CuentaId = CuentaId, @TipoOperacion = cat.TipoOperacionId
	FROM Transacciones
	INNER JOIN Categorias cat ON cat.id = Transacciones.CategoriaId
	WHERE Transacciones.id = @Id

	IF (@TipoOperacion = 2)
		SET @FactorMultiplicativo = -1;

	SET @Monto = @Monto * @FactorMultiplicativo;

	UPDATE Cuentas SET Balance -= @Monto WHERE id = @CuentaId

	DELETE Transacciones WHERE Id = @Id;

END
GO
/****** Object:  StoredProcedure [dbo].[Transacciones_Insertar]    Script Date: 22/11/2024 06:24:54 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[Transacciones_Insertar] 
	@UsuarioId int,
	@FechaTransaccion date,
	@Monto decimal(18,2),
	@CategoriaId int,
	@CuentaId int,
	@Nota nvarchar(1000)=NULL
AS
BEGIN

	SET NOCOUNT ON;

	INSERT INTO Transacciones(UsuarioId, FechaTransaccion, Monto, CategoriaId,CuentaId,Nota)
	VALUES (@UsuarioId, @FechaTransaccion, ABS(@Monto), @CategoriaId, @CuentaId, @Nota)

	UPDATE Cuentas SET Balance += @Monto WHERE id = @CuentaId;

	SELECT SCOPE_IDENTITY();


END
GO
/****** Object:  StoredProcedure [dbo].[Usuario_Buscar_PorEmail]    Script Date: 22/11/2024 06:24:54 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
--exec Usuario_Buscar_PorEmail 'PRUEBA@GMAIL.COM'
-- =============================================
CREATE PROCEDURE [dbo].[Usuario_Buscar_PorEmail] 
	
	@EmailNormalizado nvarchar(100)
	
	
AS
BEGIN

	SET NOCOUNT ON;

	SELECT Email, EmailNormalizado, PasswordHash
	FROM Usuarios
	WHERE EmailNormalizado = @EmailNormalizado

END
GO
/****** Object:  StoredProcedure [dbo].[Usuario_Insertar]    Script Date: 22/11/2024 06:24:54 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- exec Usuario_Insertar 'xxxx@gmail.com', 'XXXX@gmail.com', 123456
-- =============================================
CREATE PROCEDURE [dbo].[Usuario_Insertar] 
	@Email nvarchar(100),
	@EmailNormalizado nvarchar(100),
	@PasswordHash nvarchar(100)
	
AS
BEGIN

	SET NOCOUNT ON;

	INSERT INTO Usuarios(Email, EmailNormalizado, PasswordHash)
	VALUES (@Email,UPPER(@EmailNormalizado),@PasswordHash)

	

	


END
GO