USE [AGSBIM_APPS_DEV]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [AGS_BIM_GLL].[USP_SAVE_USER_DELEGATION](
@employeeId NVARCHAR(10),
@action NVARCHAR(10),
@requestObj NVARCHAR(MAX)
)
AS
BEGIN
DECLARE @id INT, @targetEmployeeId NVARCHAR(10), @startDate NVARCHAR(50), @endDate NVARCHAR(50), @reason NVARCHAR(MAX);
SELECT @id = id, @targetEmployeeId = targetEmployeeId, @startDate = startDate, @endDate = endDate, @reason = reason
FROM OPENJSON(@requestObj) WITH
(
id INT '$.id',
targetEmployeeId NVARCHAR(10) '$.targetEmployeeId',
startDate NVARCHAR(50) '$.startDate',
endDate NVARCHAR(50) '$.endDate',
reason NVARCHAR(MAX) '$.reason'
)
DECLARE @count INT;
IF (@action = 'NEW')
BEGIN
SELECT @count = COUNT(*) FROM [AGS_BIM_GLL].[user_delegation] WHERE source_employee_id = @employeeId AND active = 1
AND ((@startDate BETWEEN start_date AND end_date) OR (@endDate BETWEEN start_date AND end_date))
IF(@count > 0)
BEGIN
RAISERROR('Delegation was done for this time period already', 16, 1)
RETURN
END
INSERT INTO [AGS_BIM_GLL].[user_delegation](source_employee_id, target_employee_id, start_date, end_date, active, created_at, updated_at)
SELECT @employeeId, @targetEmployeeId, @startDate, @endDate, 1, GETUTCDATE(), GETUTCDATE()
END
ELSE IF (@action = 'UPDATE')
BEGIN
SELECT @count = COUNT(*) FROM [AGS_BIM_GLL].[user_delegation] WHERE source_employee_id = @employeeId AND active = 1
AND ((@startDate BETWEEN start_date AND end_date) OR (@endDate BETWEEN start_date AND end_date))
AND id != @id
IF(@count > 0)
BEGIN
RAISERROR('Delegation was done for this time period already', 16, 1)
RETURN
END
UPDATE [AGS_BIM_GLL].[user_delegation] SET target_employee_id = @targetEmployeeId, start_date = @startDate, end_date = @endDate, updated_at = GETUTCDATE()
WHERE id = @id
END
ELSE IF (@action = 'CANCEL')
BEGIN
UPDATE [AGS_BIM_GLL].[user_delegation] SET reason = @reason, active = 0, updated_at = GETUTCDATE()
WHERE id = @id
END
END
