USE [SitecLockers]
GO
/****** Object:  Table [dbo].[__EFMigrationsHistory]    Script Date: 2022-09-14 14:32:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
INSERT [dbo].[__EFMigrationsHistory] ([MigrationId], [ProductVersion]) VALUES (N'20210520091608_Init', N'3.1.0')
GO
INSERT [dbo].[__EFMigrationsHistory] ([MigrationId], [ProductVersion]) VALUES (N'20210521195726_Image', N'3.1.0')
GO
INSERT [dbo].[__EFMigrationsHistory] ([MigrationId], [ProductVersion]) VALUES (N'20210522095734_Archives', N'3.1.0')
GO
INSERT [dbo].[__EFMigrationsHistory] ([MigrationId], [ProductVersion]) VALUES (N'20210607183231_AvailableLockers', N'3.1.0')
GO
INSERT [dbo].[ConfigurationSettings] ([KeyName], [Category], [KeyValue]) VALUES (N'MessageId', N'API', N'1616')
GO
INSERT [dbo].[ConfigurationSettings] ([KeyName], [Category], [KeyValue]) VALUES (N'MessageId', N'HB', N'4')
GO
INSERT [dbo].[ConfigurationSettings] ([KeyName], [Category], [KeyValue]) VALUES (N'Protocol', N'HB', N'HTTP')
GO
INSERT [dbo].[ConfigurationSettings] ([KeyName], [Category], [KeyValue]) VALUES (N'Protocol', N'System', N'HTTP')
GO
INSERT [dbo].[ConfigurationSettings] ([KeyName], [Category], [KeyValue]) VALUES (N'SecurityKey', N'API', N'8Y58dHn3F3Z8yCFW')
GO
INSERT [dbo].[ConfigurationSettings] ([KeyName], [Category], [KeyValue]) VALUES (N'SecurityKey', N'HB', N'8Y58dHn3F3Z8yCFW')
GO
INSERT [dbo].[ConfigurationSettings] ([KeyName], [Category], [KeyValue]) VALUES (N'UserId', N'API', N'AzolverUser2')
GO
INSERT [dbo].[ConfigurationSettings] ([KeyName], [Category], [KeyValue]) VALUES (N'UserId', N'HB', N'AzolverUser0')
GOUSE [SitecLockers]
  GO
  /****** Object:  StoredProcedure [dbo].[Ats_GetLsosRequiredInformation]    Script Date: 2022-11-23 10:36:46 ******/
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  CREATE PROCEDURE [dbo].[Ats_GetLsosRequiredInformation]
  AS
  BEGIN 
  	SET NOCOUNT ON; 
  	
  SELECT *  FROM [dbo].[ConfigurationSettings] 
  WHERE (KeyName = 'Protocol' and Category = 'HB') OR 
  (KeyName = 'MessageId' and Category = 'HB') OR
  (KeyName = 'UserId' and Category = 'HB') OR 
  (KeyName = 'SecurityKey' and Category = 'HB') 
  
  END
  GO
  /****** Object:  StoredProcedure [dbo].[Ats_UpdateMessageId]    Script Date: 2022-11-23 10:36:46 ******/
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  CREATE PROCEDURE [dbo].[Ats_UpdateMessageId]
  AS
  BEGIN 
  	SET NOCOUNT ON; 
  	
  UPDATE [dbo].[ConfigurationSettings] SET KeyValue = KeyValue + 1 WHERE (KeyName = 'MessageId' and Category = 'HB') 
  
  END
  GO
  /****** Object:  StoredProcedure [dbo].[DelReservation]    Script Date: 2022-11-23 10:36:46 ******/
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  CREATE PROCEDURE [dbo].[DelReservation]
  	
  @ReservationId int,
  @ReservationToken nvarchar(120),
  @SystemId nvarchar(6),
  @PieceId char(13)
  AS
  BEGIN
  	SET NOCOUNT ON;	
  DELETE  FROM [SitecLockers].[dbo].[Reservations]
  WHERE ReservationId = @ReservationId and ReservationToken = @ReservationToken and SystemId = @SystemId and PieceId = @PieceId
  END
  GO
  /****** Object:  StoredProcedure [dbo].[GetAllLockerSizes]    Script Date: 2022-11-23 10:36:46 ******/
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  CREATE PROCEDURE [dbo].[GetAllLockerSizes] AS BEGIN SELECT Lockers.Name, Sizes.DisplayName, Sizes.Width, Sizes.Height, Sizes.Depth, LockerBanks.Id FROM Sizes INNER JOIN LockerBanks ON Sizes.LockerBanksId = LockerBanks.Id INNER JOIN Lockers ON LockerBanks.Id = Lockers.LockerBanksId CROSS JOIN LockersLockerSizes WHERE (LockersLockerSizes.LockersId = Lockers.Id AND LockersLockerSizes.SizesId = Sizes.Id AND LockersLockerSizes.LockerBanksId = LockerBanks.Id) ORDER BY Lockers.Name END
  GO
  /****** Object:  StoredProcedure [dbo].[GetAllReservations]    Script Date: 2022-11-23 10:36:46 ******/
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  CREATE PROCEDURE [dbo].[GetAllReservations]
  	
  AS
  BEGIN
  	SET NOCOUNT ON;
  	SELECT *  FROM [SitecLockers].[dbo].[Reservations]
  END
  GO
  /****** Object:  StoredProcedure [dbo].[GetLockerBankById]    Script Date: 2022-11-23 10:36:46 ******/
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  CREATE PROCEDURE [dbo].[GetLockerBankById]
  	@BankId int
  AS
  BEGIN	
  	SET NOCOUNT ON;
  	SELECT *  FROM [SitecLockers].[dbo].[LockerBanks] Where Id = @BankId
  END
  GO
  /****** Object:  StoredProcedure [dbo].[InsReservationArchive]    Script Date: 2022-11-23 10:36:46 ******/
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  CREATE PROCEDURE [dbo].[InsReservationArchive]
  @CreatedBy nvarchar(450),
  @Created datetime,
  @UpdatedBy nvarchar(450),
  @Updated datetime,
  @ReservationId int,
  @ReservationToken nvarchar(120),
  @Name nvarchar(160),
  @Description nvarchar(255),
  @SystemId nvarchar(6),
  @StartDate datetimeoffset(7),
  @EndDate datetimeoffset(7),
  @LockerGroup nvarchar(30),
  @Status nvarchar(30),
  @Acl nvarchar(30),
  @OpeningTan nvarchar(6),
  @OpeningTan2 nvarchar(6),
  @MinAge int,
  @LockerName nvarchar(30),
  @LockerBanksId int,
  @PieceId char(13),
  @Action nvarchar(30)
  AS
  BEGIN
  	SET NOCOUNT ON;
  INSERT INTO [dbo].[ReservationsArchives] ([CreatedBy],[Created],[UpdatedBy],[Updated],[ReservationId]
  ,[ReservationToken],[Name],[Description],[SystemId],[StartDate],[EndDate],[LockerGroup],[Status],[Acl]
  ,[OpeningTan],[OpeningTan2],[MinAge],[LockerName],[LockerBanksId],[PieceId],[Action])
  VALUES
  (
  @CreatedBy,@Created,@UpdatedBy,@Updated,@ReservationId,@ReservationToken,@Name,@Description,@SystemId,@StartDate
  ,@EndDate,@LockerGroup,@Status,@Acl,@OpeningTan,@OpeningTan2,@MinAge,@LockerName,@LockerBanksId,@PieceId,@Action
  )
  END
  GO
  /****** Object:  StoredProcedure [dbo].[InsReservationArchiveStatusAction]    Script Date: 2022-11-23 10:36:46 ******/
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  CREATE PROCEDURE [dbo].[InsReservationArchiveStatusAction] 
  
  @CreatedBy nvarchar(450),
  @Created datetime,
  @UpdatedBy nvarchar(450),
  @Updated datetime,
  @ReservationId int,
  @ReservationToken nvarchar(120),
  @Name nvarchar(160),
  @Description nvarchar(255),
  @SystemId nvarchar(6),
  @StartDate datetimeoffset(7),
  @EndDate datetimeoffset(7),
  @LockerGroup nvarchar(30),
  @Status nvarchar(30),
  @Acl nvarchar(30),
  @OpeningTan nvarchar(6),
  @OpeningTan2 nvarchar(6),
  @MinAge int,
  @LockerName nvarchar(30),
  @LockerBanksId int,
  @PieceId char(13),
  @Action nvarchar(30)
  AS
  BEGIN
  	SET NOCOUNT ON;
  
  SELECT TOP (1) 
  @CreatedBy = CreatedBy,@Created  = Created,@UpdatedBy = UpdatedBy,@Updated = Updated,@Name = Name,@Description = Description,
  @StartDate = StartDate,@EndDate = EndDate,@LockerGroup = LockerGroup,@Acl = Acl,@OpeningTan = OpeningTan,@OpeningTan2 = OpeningTan2,
  @MinAge = MinAge,@LockerName = LockerName,@LockerBanksId = LockerBanksId, @PieceId = PieceId
  FROM [SitecLockers].[dbo].[Reservations]
  WHERE ReservationId = @ReservationId AND ReservationToken = @ReservationToken AND SystemId = @SystemId
  
  INSERT INTO [dbo].[ReservationsArchives] ([CreatedBy],[Created],[UpdatedBy],[Updated],[ReservationId]
  ,[ReservationToken],[Name],[Description],[SystemId],[StartDate],[EndDate],[LockerGroup],[Status],[Acl]
  ,[OpeningTan],[OpeningTan2],[MinAge],[LockerName],[LockerBanksId],[PieceId],[Action])
  VALUES
  (
  @CreatedBy,@Created,@UpdatedBy,@Updated,@ReservationId,@ReservationToken,@Name,@Description,@SystemId,@StartDate
  ,@EndDate,@LockerGroup,@Status,@Acl,@OpeningTan,@OpeningTan2,@MinAge,@LockerName,@LockerBanksId,@PieceId,@Action
  )
  END
  GO
  /****** Object:  StoredProcedure [dbo].[LockerAvailable]    Script Date: 2022-11-23 10:36:46 ******/
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  CREATE PROCEDURE [dbo].[LockerAvailable]
  	
  @LockerBanksId int,
  @Name nvarchar(30)
  AS
  BEGIN
  	SET NOCOUNT ON;	
  
  	
  UPDATE [AvailableLockers] SET Available = 1 WHERE  LockerId = (SELECT Id  FROM [Lockers] WHERE LockerBanksId = @LockerBanksId AND Name = @Name)
  END
  GO
  /****** Object:  StoredProcedure [dbo].[LockerUnAvailable]    Script Date: 2022-11-23 10:36:46 ******/
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  CREATE PROCEDURE [dbo].[LockerUnAvailable]
  	
  @LockerBanksId int,
  @Name nvarchar(30)
  AS
  BEGIN
  	SET NOCOUNT ON;	
  
  	
  UPDATE [AvailableLockers] SET Available = 0 WHERE  LockerId = (SELECT Id  FROM [Lockers] WHERE LockerBanksId = @LockerBanksId AND Name = @Name)
  END
  GO