Project

General

Profile

Feature #219 » sql.sql

Junaid M, 04/22/2024 11:55 AM

 
-- @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
-- GDSLCC->TBL_GDS_CUSTOM_FORMAT TBL_GDS_CUSTOM_FORMAT TBL_GDS_CUSTOM_FORMAT TBL_GDS_CUSTOM_FORMAT
-- @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
------------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION fn_get_all_gds_custom_format() RETURNS SETOF gdslcc.tbl_gds_custom_format AS $$
DECLARE

BEGIN
RETURN QUERY SELECT * FROM gdslcc.tbl_gds_custom_format WHERE int_sys_action_id != -1;
END;
$$ LANGUAGE plpgsql;

------------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION fn_get_all_gds_custom_format_with_action_user()
RETURNS TABLE (pk_gds_custom_format_id BIGINT,

sin_category SMALLINT,
sin_sub_category SMALLINT,
vhr_sys_attribute_name VARCHAR,
fk_corporate_custom_data_id BIGINT,
fk_service_custom_data_id BIGINT,

vhr_amadeus_format VARCHAR,
vhr_galileo_format VARCHAR,
vhr_sabre_format VARCHAR,
vhr_worldspan_format VARCHAR,
vhr_farelogix_format VARCHAR,
vhr_abacus_format VARCHAR,
vhr_travelport_format VARCHAR,

int_sys_action_id INT,
fk_created_user_id BIGINT,
vhr_created_user VARCHAR,
dtm_created TIMESTAMP WITH TIME ZONE,
fk_last_modified_user_id BIGINT,
vhr_last_modified_user VARCHAR,
dtm_last_modified TIMESTAMP WITH TIME ZONE,
fk_deleted_user_id BIGINT,
vhr_deleted_user VARCHAR,
dtm_deleted TIMESTAMP WITH TIME ZONE) AS $$
DECLARE

BEGIN
RETURN QUERY SELECT mt.pk_gds_custom_format_id,

mt.sin_category,
mt.sin_sub_category,
mt.vhr_sys_attribute_name,
mt.fk_corporate_custom_data_id,
mt.fk_service_custom_data_id,

mt.vhr_amadeus_format,
mt.vhr_galileo_format,
mt.vhr_sabre_format,
mt.vhr_worldspan_format,
mt.vhr_farelogix_format,
mt.vhr_abacus_format,
mt.vhr_travelport_format,

mt.int_sys_action_id,
mt.fk_created_user_id,
u1.vhr_login_name AS vhr_created_user,
mt.dtm_created,
mt.fk_last_modified_user_id,
u2.vhr_login_name AS vhr_last_modified_user,
mt.dtm_last_modified,
mt.fk_deleted_user_id,
u3.vhr_login_name AS vhr_deleted_user,
mt.dtm_deleted
FROM gdslcc.tbl_gds_custom_format mt
LEFT JOIN authentication.tbl_user u1
ON mt.fk_created_user_id = u1.pk_user_id
LEFT JOIN authentication.tbl_user u2
ON mt.fk_last_modified_user_id = u2.pk_user_id
LEFT JOIN authentication.tbl_user u3
ON mt.fk_deleted_user_id = u3.pk_user_id
WHERE mt.int_sys_action_id != -1
ORDER BY vhr_sys_attribute_name;
END;
$$ LANGUAGE plpgsql;

------------------------------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE sp_insert_or_update_gds_custom_format(jsnGdsCustomFormat JSONB, jsnSqlRes OUT JSON) AS $$
DECLARE
objSpOutRes TYP_SP_OUT_RES;
objFnCheckRes TYP_FN_CHECK_RES;
dtmCurrent TIMESTAMP WITH TIME ZONE;
BEGIN
SELECT NOW() INTO dtmCurrent;

-- // Duplication Checking in DB FUCTION(Insert and Update)
SELECT * FROM fn_check_data_uniqueness('gdslcc.tbl_gds_custom_format'::VARCHAR, ''::VARCHAR, ::VARCHAR, 'pk_gds_custom_format_id'::VARCHAR, CAST(jsnGdsCustomFormat->>'binGdsCustomFormatId' AS BIGINT)::BIGINT) INTO objFnCheckRes;
IF objFnCheckRes.sin_sql_check_status = -1::SMALLINT THEN
objSpOutRes.sinSqlCustomStatus := -1::SMALLINT;
objSpOutRes.strSqlCustomStatusCode := 'ERROR';
objSpOutRes.strSqlCustomMessage := objFnCheckRes.vhr_sql_check_message;

objSpOutRes.strSqlCustomErrorType := 'DATABASE_VALIDATION_ERROR';

SELECT * FROM fn_convert_type_out_object_to_out_json(objSpOutRes) INTO jsnSqlRes;
RAISE NOTICE 'jsnSqlRes: %', jsnSqlRes;
RETURN;
END IF;

IF CAST(jsnGdsCustomFormat->>'binGdsCustomFormatId' AS BIGINT)::BIGINT = 0::BIGINT THEN
-- // Count Exced or other checking License related in DB FUCTION(Insert)

--jsnGdsCustomFormat.intSysActionId = 0;
--jsnGdsCustomFormat.binCreatedUserId = jsnGdsCustomFormat.linLoginUserId;
--jsnGdsCustomFormat.dtmCreated = jsnGdsCustomFormat.dtmCurrentDateTime; --SELECT NOW();

INSERT INTO gdslcc.tbl_gds_custom_format (sin_category,
sin_sub_category,
vhr_sys_attribute_name,
fk_corporate_custom_data_id,
fk_service_custom_data_id,

vhr_amadeus_format,
vhr_galileo_format,
vhr_sabre_format,
vhr_worldspan_format,
vhr_farelogix_format,
vhr_abacus_format,
vhr_travelport_format,

int_sys_action_id,
fk_created_user_id,
dtm_created)
VALUES(CAST(jsnGdsCustomFormat->>'sinCategory' AS SMALLINT),
CAST(jsnGdsCustomFormat->>'sinSubCategory' AS SMALLINT),
jsnGdsCustomFormat->>'strSysAttributeName',
CAST(jsnGdsCustomFormat->>'binCorporateCustomDataId' AS BIGINT),
CAST(jsnGdsCustomFormat->>'binServiceCustomDataId' AS BIGINT),

jsnGdsCustomFormat->>'strAmadeusFormat',
jsnGdsCustomFormat->>'strGalileoFormat',
jsnGdsCustomFormat->>'strSabreFormat',
jsnGdsCustomFormat->>'strWorldspanFormat',
jsnGdsCustomFormat->>'strFarelogixFormat',
jsnGdsCustomFormat->>'strAbacusFormat',
jsnGdsCustomFormat->>'strTravelportFormat',

0,
CAST(jsnGdsCustomFormat->>'binCreatedUserId' AS BIGINT),
dtmCurrent) RETURNING pk_gds_custom_format_id INTO objSpOutRes.binSqlPk;
ELSE
-- // UPDATE Check already update another usersin DB FUCTION(Update and Delete)
SELECT * FROM fn_check_already_update_or_delete_another_user('gdslcc.tbl_gds_custom_format'::VARCHAR, 'pk_gds_custom_format_id'::VARCHAR, CAST(jsnGdsCustomFormat->>'binGdsCustomFormatId' AS BIGINT)::BIGINT, CAST(jsnGdsCustomFormat->>'intSysActionId' AS INT)::INT) INTO objFnCheckRes;
IF objFnCheckRes.sin_sql_check_status = -1::SMALLINT THEN
objSpOutRes.sinSqlCustomStatus := -1::SMALLINT;
objSpOutRes.strSqlCustomStatusCode := 'ERROR';
objSpOutRes.strSqlCustomMessage := objFnCheckRes.vhr_sql_check_message;

objSpOutRes.strSqlCustomErrorType := 'DATABASE_VALIDATION_ERROR';

SELECT * FROM fn_convert_type_out_object_to_out_json(objSpOutRes) INTO jsnSqlRes;
RAISE NOTICE 'jsnSqlRes: %', jsnSqlRes;
RETURN;
END IF;

--jsnGdsCustomFormat.intSysActionId = jsnGdsCustomFormat.intSysActionId + 1;
--jsnGdsCustomFormat.binLastModifiedUserId = jsnGdsCustomFormat.linLoginUserId;
--jsnGdsCustomFormat.dtmLastModified = jsnGdsCustomFormat.dtmCurrentDateTime; --SELECT NOW();

UPDATE gdslcc.tbl_gds_custom_format SET sin_category = CAST(jsnGdsCustomFormat->>'sinCategory' AS SMALLINT),
sin_sub_category = CAST(jsnGdsCustomFormat->>'sinSubCategory' AS SMALLINT),
vhr_sys_attribute_name = jsnGdsCustomFormat->>'strSysAttributeName',
fk_corporate_custom_data_id = CAST(jsnGdsCustomFormat->>'binCorporateCustomDataId' AS BIGINT),
fk_service_custom_data_id = CAST(jsnGdsCustomFormat->>'binServiceCustomDataId' AS BIGINT),

vhr_amadeus_format = jsnGdsCustomFormat->>'strAmadeusFormat',
vhr_galileo_format = jsnGdsCustomFormat->>'strGalileoFormat',
vhr_sabre_format = jsnGdsCustomFormat->>'strSabreFormat',
vhr_worldspan_format = jsnGdsCustomFormat->>'strWorldspanFormat',
vhr_farelogix_format = jsnGdsCustomFormat->>'strFarelogixFormat',
vhr_abacus_format = jsnGdsCustomFormat->>'strAbacusFormat',
vhr_travelport_format = jsnGdsCustomFormat->>'strTravelportFormat',

int_sys_action_id = int_sys_action_id + 1,
fk_last_modified_user_id = CAST(jsnGdsCustomFormat->>'binLastModifiedUserId' AS BIGINT),
dtm_last_modified = dtmCurrent
WHERE pk_gds_custom_format_id = CAST(jsnGdsCustomFormat->>'binGdsCustomFormatId' AS BIGINT);
END IF;

--COMMIT;
objSpOutRes.sinSqlCustomStatus := 1::SMALLINT;
objSpOutRes.strSqlCustomStatusCode := 'SUCCESS';
objSpOutRes.strSqlCustomMessage := 'GdsCustomFormat Saved';
objSpOutRes.dtmCurrent = dtmCurrent;
SELECT * FROM fn_convert_type_out_object_to_out_json(objSpOutRes) INTO jsnSqlRes;
RAISE NOTICE 'jsnSqlRes: %', jsnSqlRes;
RETURN;
EXCEPTION WHEN OTHERS THEN
ROLLBACK;
objSpOutRes.sinSqlCustomStatus := -1::SMALLINT;
objSpOutRes.strSqlCustomStatusCode := 'ERROR';
objSpOutRes.strSqlCustomMessage := 'GdsCustomFormat Save Failed';

objSpOutRes.strSqlCustomErrorType := 'DATABASE_SYSTEM_ERROR';

GET STACKED DIAGNOSTICS
objSpOutRes.strSqlSysMessage := MESSAGE_TEXT,
objSpOutRes.strSqlSysExceptionDetail := PG_EXCEPTION_DETAIL,
objSpOutRes.strSqlSysExceptionHint := PG_EXCEPTION_CONTEXT;
SELECT * FROM fn_convert_type_out_object_to_out_json(objSpOutRes) INTO jsnSqlRes;
RAISE EXCEPTION 'jsnSqlRes: %', jsnSqlRes;
RETURN;
END;
$$ LANGUAGE plpgsql;

------------------------------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE sp_delete_gds_custom_format(binGdsCustomFormatId BIGINT, intSysActionId INT, binDeletedUserId BIGINT, jsnSqlRes OUT JSON) AS $$
DECLARE
objSpOutRes TYP_SP_OUT_RES;
objFnCheckRes TYP_FN_CHECK_RES;
dtmCurrent TIMESTAMP WITH TIME ZONE;
BEGIN
SELECT NOW() INTO dtmCurrent;

-- // FOREIGN KEY REFERENCES Checking in DB FUCTION(Delete)
SELECT * FROM fn_check_foreignkey_referance('tbl_gds_custom_format'::VARCHAR, 'pk_gds_custom_format_id'::VARCHAR, binGdsCustomFormatId::BIGINT, '{}'::VARCHAR ARRAY) INTO objFnCheckRes;
IF objFnCheckRes.sin_sql_check_status = -1::SMALLINT THEN
objSpOutRes.sinSqlCustomStatus := -1::SMALLINT;
objSpOutRes.strSqlCustomStatusCode := 'ERROR';
objSpOutRes.strSqlCustomMessage := objFnCheckRes.vhr_sql_check_message;

objSpOutRes.strSqlCustomErrorType := 'DATABASE_VALIDATION_ERROR';

SELECT * FROM fn_convert_type_out_object_to_out_json(objSpOutRes) INTO jsnSqlRes;
RAISE NOTICE 'jsnSqlRes: %', jsnSqlRes;
RETURN;
END IF;

-- // UPDATE Check already update another users in DB FUCTION(Update and Delete)
SELECT * FROM fn_check_already_update_or_delete_another_user('gdslcc.tbl_gds_custom_format'::VARCHAR, 'pk_gds_custom_format_id'::VARCHAR, CAST(binGdsCustomFormatId AS BIGINT)::BIGINT, intSysActionId::INT) INTO objFnCheckRes;
IF objFnCheckRes.sin_sql_check_status = -1::SMALLINT THEN
objSpOutRes.sinSqlCustomStatus := -1::SMALLINT;
objSpOutRes.strSqlCustomStatusCode := 'ERROR';
objSpOutRes.strSqlCustomMessage := objFnCheckRes.vhr_sql_check_message;

objSpOutRes.strSqlCustomErrorType := 'DATABASE_VALIDATION_ERROR';

SELECT * FROM fn_convert_type_out_object_to_out_json(objSpOutRes) INTO jsnSqlRes;
RAISE NOTICE 'jsnSqlRes: %', jsnSqlRes;
RETURN;
END IF;

--dtmDeleted = SELECT NOW();
UPDATE gdslcc.tbl_gds_custom_format SET int_sys_action_id = -1::INT,
fk_deleted_user_id = binDeletedUserId,
dtm_deleted = dtmCurrent
WHERE pk_gds_custom_format_id = binGdsCustomFormatId;

--COMMIT;
objSpOutRes.sinSqlCustomStatus := 1::SMALLINT;
objSpOutRes.strSqlCustomStatusCode := 'SUCCESS';
objSpOutRes.strSqlCustomMessage := 'GdsCustomFormat Deleted';
objSpOutRes.dtmCurrent = dtmCurrent;

SELECT * FROM fn_convert_type_out_object_to_out_json(objSpOutRes) INTO jsnSqlRes;
RAISE NOTICE 'jsnSqlRes: %', jsnSqlRes;
RETURN;
EXCEPTION WHEN OTHERS THEN
ROLLBACK;
objSpOutRes.sinSqlCustomStatus := -1::SMALLINT;
objSpOutRes.strSqlCustomStatusCode := 'ERROR';
objSpOutRes.strSqlCustomMessage := 'GdsCustomFormat Delete Failed';

objSpOutRes.strSqlCustomErrorType := 'DATABASE_SYSTEM_ERROR';

GET STACKED DIAGNOSTICS
objSpOutRes.strSqlSysMessage := MESSAGE_TEXT,
objSpOutRes.strSqlSysExceptionDetail := PG_EXCEPTION_DETAIL,
objSpOutRes.strSqlSysExceptionHint := PG_EXCEPTION_CONTEXT;

SELECT * FROM fn_convert_type_out_object_to_out_json(objSpOutRes) INTO jsnSqlRes;
RAISE EXCEPTION 'jsnSqlRes: %', jsnSqlRes;
RETURN;
END;
$$ LANGUAGE plpgsql;

(7-7/7)