Insert SP from Json Object

 USE [AGSBIM_APPS_DEV]

GO

/****** Object:  StoredProcedure [AGS_BIM_GLL].[USP_SAVE_LOANOUT_REQUEST]    Script Date: 3/28/2023 3:43:51 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [AGS_BIM_GLL].[USP_SAVE_LOANOUT_REQUEST](

@requestedEmployeeId NVARCHAR(10),

@team NVARCHAR(50),

@requestObj VARCHAR(MAX)

)

AS

BEGIN

BEGIN TRANSACTION  

BEGIN TRY

DECLARE @requestType NVARCHAR(10) = 'LOAN_OUT';

DECLARE @count NVARCHAR(10);  

SET @count = (SELECT COUNT(*) FROM [AGS_BIM_GLL].[request]) + 1

DECLARE @requestId NVARCHAR(10);  

SET @requestId = CONCAT('REQ', REPLICATE('0',7-LEN(@count)) + @count);

-- status id 1: pending for approval, status id 2: approved

DECLARE @defaultStatusId INT;

SET @defaultStatusId = CASE 

WHEN ((SELECT COUNT(*) FROM [AGS_BIM_GLL].[team_configuration] WHERE team = @team AND request_type =  @requestType AND auto_approve = 1)) > 0 THEN 2

ELSE 1

END

INSERT INTO [AGS_BIM_GLL].[request] (request_id, request_type, status_id, created_by, created_at, updated_at ) VALUES

(@requestId, @requestType, @defaultStatusId, @requestedEmployeeId, GETUTCDATE(), GETUTCDATE());

INSERT INTO [AGS_BIM_GLL].[available_resource_pool] (request_id, team, employee_id, justification, start_date, end_date, status_id , created_at, updated_at)

SELECT @requestId, team, employee_id, justification, start_date, end_date, @defaultStatusId, GETUTCDATE(), GETUTCDATE() FROM OPENJSON(@requestObj) WITH

(

team NVARCHAR(50) '$.team', 

employee_id NVARCHAR(10) '$.employeeId', 

            justification NVARCHAR(MAX) '$.justification',

            start_date DATE '$.startDate', 

            end_date DATE '$.endDate'

) 

DECLARE @listOfEmployeeIds table (employeeId NVARCHAR(10));

INSERT INTO @listOfEmployeeIds

SELECT employee_id FROM OPENJSON(@requestObj) WITH

(

employee_id NVARCHAR(10) '$.employeeId'

)

-- Skills

DELETE FROM [AGS_BIM_GLL].[employee_skills_mapping] WHERE employee_id IN (SELECT employeeId FROM @listOfEmployeeIds)

INSERT INTO [AGS_BIM_GLL].[employee_skills_mapping] (employee_id, skill_name, created_at, updated_at)

SELECT employee_id, skill_name, GETUTCDATE(), GETUTCDATE() FROM OPENJSON(@requestObj) WITH

(

employee_id NVARCHAR(10) '$.employeeId', 

        skills NVARCHAR(MAX) '$.skills' as JSON

)  

CROSS APPLY OPENJSON (skills) WITH

(

id INT '$.id',  

skill_name NVARCHAR(255) '$.name'

)

-- Languages

DELETE FROM [AGS_BIM_GLL].[employee_languages_mapping] WHERE employee_id IN (SELECT employeeId FROM @listOfEmployeeIds)

INSERT INTO [AGS_BIM_GLL].[employee_languages_mapping] (employee_id, language_name, created_at, updated_at)

SELECT employee_id, language_name, GETUTCDATE(), GETUTCDATE() FROM OPENJSON(@requestObj) WITH

(

employee_id NVARCHAR(10) '$.employeeId', 

        languages NVARCHAR(MAX) '$.languages' as JSON

)  

CROSS APPLY OPENJSON (languages) WITH

(

id INT '$.id',  

language_name NVARCHAR(255) '$.name'

)

-- Certifications

DELETE FROM [AGS_BIM_GLL].[employee_certifications_mapping] WHERE employee_id IN (SELECT employeeId FROM @listOfEmployeeIds)

INSERT INTO [AGS_BIM_GLL].[employee_certifications_mapping] (employee_id, certification_name, created_at, updated_at)

SELECT employee_id, certification_name, GETUTCDATE(), GETUTCDATE() FROM OPENJSON(@requestObj) WITH

(

employee_id NVARCHAR(10) '$.employeeId', 

        certifications NVARCHAR(MAX) '$.certifications' as JSON

)  

CROSS APPLY OPENJSON (certifications) WITH

(

id INT '$.id',  

certification_name NVARCHAR(255) '$.name'

)

-- Passport Details

DELETE FROM [AGS_BIM_GLL].[employee_passport_details] WHERE employee_id IN (SELECT employeeId FROM @listOfEmployeeIds)

INSERT INTO [AGS_BIM_GLL].[employee_passport_details] (employee_id, issued_by, valid_from, valid_to, created_at, updated_at)

SELECT employee_id, issued_by, valid_from, valid_to, GETUTCDATE(), GETUTCDATE() FROM OPENJSON(@requestObj) WITH

(

employee_id NVARCHAR(10) '$.employeeId', 

        passportDetails NVARCHAR(MAX) '$.passportDetails' as JSON

)  

CROSS APPLY OPENJSON (passportDetails) WITH

(

id INT '$.id',  

issued_by INT '$.issued_by',

valid_from DATE '$.valid_from',

valid_to DATE '$.valid_to'

)

-- Visa Details

DELETE FROM [AGS_BIM_GLL].[employee_visa_details] WHERE employee_id IN (SELECT employeeId FROM @listOfEmployeeIds)

INSERT INTO [AGS_BIM_GLL].[employee_visa_details] (employee_id, country_id, visa_type_id, valid_from, valid_to, created_at, updated_at)

SELECT employee_id, country_id, visa_type_id, valid_from, valid_to, GETUTCDATE(), GETUTCDATE() FROM OPENJSON(@requestObj) WITH

(

employee_id NVARCHAR(10) '$.employeeId', 

        visaDetails NVARCHAR(MAX) '$.visaDetails' as JSON

)  

CROSS APPLY OPENJSON (visaDetails) WITH

(

id INT '$.id',  

country_id INT '$.country_id',

visa_type_id INT '$.visa_type_id',

valid_from DATE '$.valid_from',

valid_to DATE '$.valid_to'

)

COMMIT TRANSACTION

END TRY

BEGIN CATCH

  THROW(SELECT ERROR_MESSAGE());

END CATCH

END