USE [SST]
GO
/****** Object:  StoredProcedure [dbo].[Ats_get_SENDERSbyName]    Script Date: 2022-11-18 10:16:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO






Create PROCEDURE [dbo].[Ats_get_SENDERSbyName]
	@SNDNAME char(30)
As
Begin
	Select  TOP 1
		[SNDCODE],
		[SNDNAME],
		[SNDADDR],
		[SNDNAM2],
		[SNDCITY],
		[SNDSTAT],
		[SNDZIP],
		[SNDCNTRY],
		[SNDCNTCOD],
		[SNDPHONE],
		[SNDFORM1],
		[SNDFORM2],
		[SNDTEXT1],
		[SNDNUM1],
		[SNDFILE],
		[UPDUSER],
		[UPDDATE],
		[UPDTIME],
		[UPDSITE],
		[SNDEMAIL],
		[SNDADDR2],
		[SNDDATE1],
		[UPDTYPE],
		[UPDMACHINE]
	From SENDERS
	Where
		SNDNAME = @SNDNAME
End







GO
/****** Object:  StoredProcedure [dbo].[Ats_GetAllNotifications]    Script Date: 2022-11-18 10:16:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
Create PROCEDURE [dbo].[Ats_GetAllNotifications]
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 *  FROM Notifications
END
GO
/****** Object:  StoredProcedure [dbo].[Ats_GetLinkMobilityConfigurationSettings]    Script Date: 2022-11-18 10:16:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
Create PROCEDURE [dbo].[Ats_GetLinkMobilityConfigurationSettings]
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 *  FROM [dbo].[ConfigurationSettings] WHERE Category = 'LinkMobility'

END
GO
/****** Object:  StoredProcedure [dbo].[Ats_GetLsosRequiredInformation]    Script Date: 2022-11-18 10:16:59 ******/
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 = 'API') OR 
(KeyName = 'MessageId' and Category = 'API') OR
(KeyName = 'UserId' and Category = 'API') OR 
(KeyName = 'SecurityKey' and Category = 'API') 

END
GO
/****** Object:  StoredProcedure [dbo].[Ats_GetNotificationPackage]    Script Date: 2022-11-18 10:16:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Ats_GetNotificationPackage]
@PieceId [nvarchar] (13)
AS
BEGIN
	SET NOCOUNT ON;
	SELECT TRACKNO,RECVRID,RECVRNAME,RECVREMAIL,PKGSTATUS,UNUM1,SITEID,UPDSITE,PCCATEGORY,POUCHTYPE,PARPOUCH,PIECES,UNUM4,
	ALTPOCID,UCLERK5,UCLERK6,NTFDONE,PIECEID,RDATE,RTIME,DLVRDATE,DLVRTIME,LOCATION,RCVREFER,PONUM,CLERKID,CLERKNAME,UTEXT1,UTEXT2,
	UTEXT3,UTEXT4,UTEXT5,UTEXT6,UNUM5,FILTERID,ORIGSITE,FILTERID1,FILTERID2,UNUM6,QRECVBTCH,RECVRMSTOP,RECVRMROUT,DROPID,
	RECVRDEPT,PKGAPRNM,SIGNBYNM,RECVRBLDNG,RECVRFLOOR,SNDNAME,SNDEMAIL,SNDPHONE,CARR,USIG6,SNDCODE,UPDDATE,POUCHID,POCID,RECVRPHONE	
	FROM MAILS  where  PIECEID = @PieceId 
END
GO
/****** Object:  StoredProcedure [dbo].[Ats_GetNotificationPackages]    Script Date: 2022-11-18 10:16:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Ats_GetNotificationPackages]
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 TRACKNO,RECVRID,RECVRNAME,RECVREMAIL,PKGSTATUS,UNUM1,SITEID,UPDSITE,PCCATEGORY,POUCHTYPE,PARPOUCH,PIECES,UNUM4,
	ALTPOCID,UCLERK5,UCLERK6,NTFDONE,PIECEID,RDATE,RTIME,DLVRDATE,DLVRTIME,LOCATION,RCVREFER,PONUM,CLERKID,CLERKNAME,UTEXT1,UTEXT2,
	UTEXT3,UTEXT4,UTEXT5,UTEXT6,UNUM5,FILTERID,ORIGSITE,FILTERID1,FILTERID2,UNUM6,QRECVBTCH,RECVRMSTOP,RECVRMROUT,DROPID,
	RECVRDEPT,PKGAPRNM,SIGNBYNM,RECVRBLDNG,RECVRFLOOR,SNDNAME,SNDEMAIL,SNDPHONE,CARR,USIG6,SNDCODE,UPDDATE,POUCHID,POCID,RECVRPHONE
	
	FROM MAILS  where  NTFDONE = 'N' AND LABELFLAG = 'Y' AND EMPEMLNTF = 'Y' and RECVRID != '' AND UNUM1 != '0'
END
GO
/****** Object:  StoredProcedure [dbo].[Ats_GetSmsProviderConfigurationSettings]    Script Date: 2022-11-18 10:16:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
Create PROCEDURE [dbo].[Ats_GetSmsProviderConfigurationSettings]
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 *  FROM [dbo].[ConfigurationSettings] WHERE (KeyName = 'SmsProvider' and Category = 'SmsProvider') 

END
GO
/****** Object:  StoredProcedure [dbo].[Ats_LockersEnabled]    Script Date: 2022-11-18 10:16:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Ats_LockersEnabled]
AS
BEGIN	
	SET NOCOUNT ON;
   SELECT *  FROM [dbo].[ConfigurationSettings]  WHERE (KeyName = 'AtsReceiveToLocker' and Category = 'SystemConfig')

END
GO
/****** Object:  StoredProcedure [dbo].[Ats_NotificationMailExist]    Script Date: 2022-11-18 10:16:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
Create PROCEDURE [dbo].[Ats_NotificationMailExist]
@active [nvarchar] (5),
@Name [nvarchar] (160),
@PackageType [char] (10),
@Condition [nvarchar] (1)
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 *  FROM Notifications  where  Active = @active and Name = @Name and Condition = @Condition and PackageType = @PackageType
END
GO
/****** Object:  StoredProcedure [dbo].[Ats_NotificationSent]    Script Date: 2022-11-18 10:16:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[Ats_NotificationSent]
@Trackno [char] (40)
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 [dbo].[MAILS]   SET NTFDONE = 'Y' where TRACKNO =  @Trackno
END
GO
/****** Object:  StoredProcedure [dbo].[Ats_NotificationUpdatePouch]    Script Date: 2022-11-18 10:16:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[Ats_NotificationUpdatePouch]
@Trackno [char] (40),
@Ntfdone [char] (1),
@Unum4 [int],
@Unum1 [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
	UPDATE [dbo].[MAILS]   SET NTFDONE = @Ntfdone,UNUM4 = @Unum4,UNUM1 = @Unum1  where TRACKNO =  @Trackno
END
GO
/****** Object:  StoredProcedure [dbo].[Ats_SmtpConfigurationSettings]    Script Date: 2022-11-18 10:16:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[Ats_SmtpConfigurationSettings]
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 *  FROM [dbo].[ConfigurationSettings] WHERE (KeyName = 'EmailServerPassword' and Category = 'SMTP') OR  (KeyName = 'UserId' and Category = 'SMTP') OR (KeyName = 'From' and Category = 'SMTP') OR (KeyName = 'Host' and Category = 'SMTP') OR (KeyName = 'Port' and Category = 'SMTP') or (KeyName = 'EnableSSL' and Category = 'SMTP')

END
GO
/****** Object:  StoredProcedure [dbo].[Ats_UpdateAtsPackage]    Script Date: 2022-11-18 10:16:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Ats_UpdateAtsPackage]
	
@PieceId [char] (13),
@Status [char] (30),
@StartDate datetimeoffset(7),
@EndDate datetimeoffset(7),
@LockerName [char] (160)
AS
BEGIN
	SET NOCOUNT ON;
IF ltrim(rtrim(@Status)) = 'RENTED'    
BEGIN
UPDATE [SitecLockers].[dbo].[Reservations] SET  StartDate = convert(datetimeoffset,@StartDate);
UPDATE [SitecLockers].[dbo].[Reservations] SET EndDate = convert(datetimeoffset,@EndDate)  ;
UPDATE [SitecLockers].[dbo].[Reservations] SET Status = @Status FROM [SitecLockers].[dbo].[Reservations] WHERE PieceId = @PieceId;
UPDATE MAILS SET PKGSTATUS = @Status, UTEXT5 = @LockerName, EMPEMLNTF = 'Y',LABELFLAG = 'Y', NTFDONE = 'N', UNUM1 = '3' where PIECEID = @PieceId
END

ELSE IF ltrim(rtrim(@Status)) = 'FINISHED'    
BEGIN
set @Status = 'DELIVERED'
UPDATE MAILS SET PKGSTATUS = 'DELIVERED', DLVRDATE = FORMAT (getdate(), 'yyyy-MM-dd 00:00:00'), DLVRTIME = FORMAT (getdate(), 'HHmm') , 
PKGAPRNM = (SELECT ltrim(rtrim( RECVRNAME)) FROM MAILS WHERE PIECEID = @PieceId),
SIGNBYNM = (SELECT ltrim(rtrim( RECVRNAME)) FROM MAILS WHERE PIECEID = @PieceId),
DLVRNM = 'Locker', UTEXT5 = @LockerName, EMPEMLNTF = 'Y',LABELFLAG = 'Y', NTFDONE = 'N', UNUM1 = '3' where PIECEID = @PieceId
END
ELSE
BEGIN
	UPDATE MAILS SET PKGSTATUS = @Status, UTEXT5 = @LockerName, EMPEMLNTF = 'Y',LABELFLAG = 'Y', NTFDONE = 'N', UNUM1 = '3' where PIECEID = @PieceId

	END

	BEGIN
	
    declare @uniquelog nvarchar(13)
    execute get_SequenceNumber @uniquelog output

	
INSERT INTO [dbo].[T1VEW] ([UNIQUELOG],[PIECEID],[LOG_DATE],[LOG_TIME],[LOG_CLERK],[LOG_CNAME],[LOG_LOC]
           ,[LOG_STATUS],[LOG_SITE],[LOG_UTEXT1],[LOG_UTEXT2],[LOG_UDATE],[LOG_UTIME],[LOG_FILE]
           ,[UPDUSER],[UPDDATE],[UPDTIME],[UPDSITE],[FILTERID],[CONTID],[LOG_EXCEPT]
           ,[LOG_EXCST],[UPDTYPE],[UPDMACHINE])
     VALUES
           ( (SELECT @uniquelog)
           ,@PieceId
           ,getdate()
           ,FORMAT (getdate(), 'HHmm')
           ,(SELECT CLERKID FROM MAILS WHERE PIECEID = @PieceId)
           ,(SELECT CLERKNAME FROM MAILS WHERE PIECEID = @PieceId)
           ,(SELECT LOCATION FROM MAILS WHERE PIECEID = @PieceId)
           ,@Status
           ,(SELECT SITEID FROM MAILS WHERE PIECEID = @PieceId)
           ,(SELECT UTEXT1 FROM MAILS WHERE PIECEID = @PieceId)
           ,(SELECT RCVREFER FROM MAILS WHERE PIECEID = @PieceId)
           ,getdate()
           ,FORMAT (getdate(), 'HHmm')
           ,''
           ,(SELECT CLERKNAME FROM MAILS WHERE PIECEID = @PieceId)
           ,getdate()
           , FORMAT (getdate(), 'yyyyMMddHHmmss')
           ,(SELECT UPDSITE FROM MAILS WHERE PIECEID = @PieceId)
           ,(SELECT FILTERID FROM MAILS WHERE PIECEID = @PieceId)
           ,''
           ,(SELECT PKGEXCEPT FROM MAILS WHERE PIECEID = @PieceId)
           ,' '
           ,'CREATED'
           ,'ATS')
		   END
END
GO
/****** Object:  StoredProcedure [dbo].[Ats_UpdateMessageId]    Script Date: 2022-11-18 10:16:59 ******/
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 = 'API') 

END
GO