Project

General

Profile

Feature #285 » sql.sql

Junaid M, 04/08/2024 12:58 PM

 
-- @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
-- PAIDCHEQUE->TBL_PAID_CHEQUE_RETURN TBL_PAID_CHEQUE_RETURN TBL_PAID_CHEQUE_RETURN TBL_PAID_CHEQUE_RETURN
-- @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
------------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION fn_get_all_paid_cheque_return() RETURNS SETOF paidcheque.tbl_paid_cheque_return AS $$
DECLARE

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

------------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION fn_get_all_paid_cheque_return_with_action_user()
RETURNS TABLE (pk_paid_cheque_return_id BIGINT,

fk_documents_id BIGINT,
fk_paid_cheque_id BIGINT,
fk_paid_cheque_details_id BIGINT,

fk_bank_charge_id BIGINT,
fk_bank_cr_customer_id BIGINT,
fk_bank_expense_id BIGINT,

vhr_bank_currency VARCHAR,
dbl_bank_cur_roe DOUBLE PRECISION,
dbl_bank_cur_bank_charge DOUBLE PRECISION,

vhr_cust_currency VARCHAR,
dbl_cust_cur_roe DOUBLE PRECISION,
dbl_cust_cur_cust_charge DOUBLE PRECISION,

vhr_base_currency VARCHAR,
dbl_base_cur_bank_charge DOUBLE PRECISION,
dbl_base_cur_cust_charge DOUBLE PRECISION,

fk_service_tax_id BIGINT,
dbl_sys_perce_of_tax DOUBLE PRECISION,
dbl_perce_of_tax DOUBLE PRECISION,
dbl_ledg_cur_tax_base_amount DOUBLE PRECISION,
dbl_ledg_cur_tax_amount DOUBLE PRECISION,

dbl_base_cur_tax_base_amount DOUBLE PRECISION,
dbl_base_cur_tax_amount DOUBLE PRECISION) AS $$
DECLARE

BEGIN
RETURN QUERY SELECT mt.pk_paid_cheque_return_id,

mt.fk_documents_id,
mt.fk_paid_cheque_id,
mt.fk_paid_cheque_details_id,

mt.fk_bank_charge_id,
mt.fk_bank_cr_customer_id,
mt.fk_bank_expense_id,

mt.vhr_bank_currency,
mt.dbl_bank_cur_roe,
mt.dbl_bank_cur_bank_charge,

mt.vhr_cust_currency,
mt.dbl_cust_cur_roe,
mt.dbl_cust_cur_cust_charge,

mt.vhr_base_currency,
mt.dbl_base_cur_bank_charge,
mt.dbl_base_cur_cust_charge,

mt.fk_service_tax_id,
mt.dbl_sys_perce_of_tax,
mt.dbl_perce_of_tax,
mt.dbl_ledg_cur_tax_base_amount,
mt.dbl_ledg_cur_tax_amount,

mt.dbl_base_cur_tax_base_amount,
mt.dbl_base_cur_tax_amount
FROM paidcheque.tbl_paid_cheque_return 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 ;
END;
$$ LANGUAGE plpgsql;

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

CREATE OR REPLACE PROCEDURE sp_insert_or_update_paid_cheque_return(jsnPaidChequeReturn 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('paidcheque.tbl_paid_cheque_return'::VARCHAR, ''::VARCHAR, ::VARCHAR, 'pk_paid_cheque_return_id'::VARCHAR, CAST(jsnPaidChequeReturn->>'binPaidChequeReturnId' 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(jsnPaidChequeReturn->>'binPaidChequeReturnId' AS BIGINT)::BIGINT = 0::BIGINT THEN
-- // Count Exced or other checking License related in DB FUCTION(Insert)

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

INSERT INTO paidcheque.tbl_paid_cheque_return (fk_documents_id,
fk_paid_cheque_id,
fk_paid_cheque_details_id,

fk_bank_charge_id,
fk_bank_cr_customer_id,
fk_bank_expense_id,

vhr_bank_currency,
dbl_bank_cur_roe,
dbl_bank_cur_bank_charge,

vhr_cust_currency,
dbl_cust_cur_roe,
dbl_cust_cur_cust_charge,

vhr_base_currency,
dbl_base_cur_bank_charge,
dbl_base_cur_cust_charge,

fk_service_tax_id,
dbl_sys_perce_of_tax,
dbl_perce_of_tax,
dbl_ledg_cur_tax_base_amount,
dbl_ledg_cur_tax_amount,

dbl_base_cur_tax_base_amount,
dbl_base_cur_tax_amount)
VALUES(CAST(jsnPaidChequeReturn->>'binDocumentsId' AS BIGINT),
CAST(jsnPaidChequeReturn->>'binPaidChequeId' AS BIGINT),
CAST(jsnPaidChequeReturn->>'binPaidChequeDetailsId' AS BIGINT),

CAST(jsnPaidChequeReturn->>'binBankChargeId' AS BIGINT),
CAST(jsnPaidChequeReturn->>'binBankCrCustomerId' AS BIGINT),
CAST(jsnPaidChequeReturn->>'binBankExpenseId' AS BIGINT),

jsnPaidChequeReturn->>'strBankCurrency',
CAST(jsnPaidChequeReturn->>'dblBankCurRoe' AS DOUBLE PRECISION),
CAST(jsnPaidChequeReturn->>'dblBankCurBankCharge' AS DOUBLE PRECISION),

jsnPaidChequeReturn->>'strCustCurrency',
CAST(jsnPaidChequeReturn->>'dblCustCurRoe' AS DOUBLE PRECISION),
CAST(jsnPaidChequeReturn->>'dblCustCurCustCharge' AS DOUBLE PRECISION),

jsnPaidChequeReturn->>'strBaseCurrency',
CAST(jsnPaidChequeReturn->>'dblBaseCurBankCharge' AS DOUBLE PRECISION),
CAST(jsnPaidChequeReturn->>'dblBaseCurCustCharge' AS DOUBLE PRECISION),

CAST(jsnPaidChequeReturn->>'binServiceTaxId' AS BIGINT),
CAST(jsnPaidChequeReturn->>'dblSysPerceOfTax' AS DOUBLE PRECISION),
CAST(jsnPaidChequeReturn->>'dblPerceOfTax' AS DOUBLE PRECISION),
CAST(jsnPaidChequeReturn->>'dblLedgCurTaxBaseAmount' AS DOUBLE PRECISION),
CAST(jsnPaidChequeReturn->>'dblLedgCurTaxAmount' AS DOUBLE PRECISION),

CAST(jsnPaidChequeReturn->>'dblBaseCurTaxBaseAmount' AS DOUBLE PRECISION),
CAST(jsnPaidChequeReturn->>'dblBaseCurTaxAmount' AS DOUBLE PRECISION)) RETURNING pk_paid_cheque_return_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('paidcheque.tbl_paid_cheque_return'::VARCHAR, 'pk_paid_cheque_return_id'::VARCHAR, CAST(jsnPaidChequeReturn->>'binPaidChequeReturnId' AS BIGINT)::BIGINT, CAST(jsnPaidChequeReturn->>'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;

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

UPDATE paidcheque.tbl_paid_cheque_return SET fk_documents_id = CAST(jsnPaidChequeReturn->>'binDocumentsId' AS BIGINT),
fk_paid_cheque_id = CAST(jsnPaidChequeReturn->>'binPaidChequeId' AS BIGINT),
fk_paid_cheque_details_id = CAST(jsnPaidChequeReturn->>'binPaidChequeDetailsId' AS BIGINT),

fk_bank_charge_id = CAST(jsnPaidChequeReturn->>'binBankChargeId' AS BIGINT),
fk_bank_cr_customer_id = CAST(jsnPaidChequeReturn->>'binBankCrCustomerId' AS BIGINT),
fk_bank_expense_id = CAST(jsnPaidChequeReturn->>'binBankExpenseId' AS BIGINT),

vhr_bank_currency = jsnPaidChequeReturn->>'strBankCurrency',
dbl_bank_cur_roe = CAST(jsnPaidChequeReturn->>'dblBankCurRoe' AS DOUBLE PRECISION),
dbl_bank_cur_bank_charge = CAST(jsnPaidChequeReturn->>'dblBankCurBankCharge' AS DOUBLE PRECISION),

vhr_cust_currency = jsnPaidChequeReturn->>'strCustCurrency',
dbl_cust_cur_roe = CAST(jsnPaidChequeReturn->>'dblCustCurRoe' AS DOUBLE PRECISION),
dbl_cust_cur_cust_charge = CAST(jsnPaidChequeReturn->>'dblCustCurCustCharge' AS DOUBLE PRECISION),

vhr_base_currency = jsnPaidChequeReturn->>'strBaseCurrency',
dbl_base_cur_bank_charge = CAST(jsnPaidChequeReturn->>'dblBaseCurBankCharge' AS DOUBLE PRECISION),
dbl_base_cur_cust_charge = CAST(jsnPaidChequeReturn->>'dblBaseCurCustCharge' AS DOUBLE PRECISION),

fk_service_tax_id = CAST(jsnPaidChequeReturn->>'binServiceTaxId' AS BIGINT),
dbl_sys_perce_of_tax = CAST(jsnPaidChequeReturn->>'dblSysPerceOfTax' AS DOUBLE PRECISION),
dbl_perce_of_tax = CAST(jsnPaidChequeReturn->>'dblPerceOfTax' AS DOUBLE PRECISION),
dbl_ledg_cur_tax_base_amount = CAST(jsnPaidChequeReturn->>'dblLedgCurTaxBaseAmount' AS DOUBLE PRECISION),
dbl_ledg_cur_tax_amount = CAST(jsnPaidChequeReturn->>'dblLedgCurTaxAmount' AS DOUBLE PRECISION),

dbl_base_cur_tax_base_amount = CAST(jsnPaidChequeReturn->>'dblBaseCurTaxBaseAmount' AS DOUBLE PRECISION),
dbl_base_cur_tax_amount = CAST(jsnPaidChequeReturn->>'dblBaseCurTaxAmount' AS DOUBLE PRECISION)
WHERE pk_paid_cheque_return_id = CAST(jsnPaidChequeReturn->>'binPaidChequeReturnId' AS BIGINT);
END IF;

--COMMIT;
objSpOutRes.sinSqlCustomStatus := 1::SMALLINT;
objSpOutRes.strSqlCustomStatusCode := 'SUCCESS';
objSpOutRes.strSqlCustomMessage := 'PaidChequeReturn 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 := 'PaidChequeReturn 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_paid_cheque_return(binPaidChequeReturnId 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_paid_cheque_return'::VARCHAR, 'pk_paid_cheque_return_id'::VARCHAR, binPaidChequeReturnId::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('paidcheque.tbl_paid_cheque_return'::VARCHAR, 'pk_paid_cheque_return_id'::VARCHAR, CAST(binPaidChequeReturnId 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 paidcheque.tbl_paid_cheque_return SET int_sys_action_id = -1::INT,
fk_deleted_user_id = binDeletedUserId,
dtm_deleted = dtmCurrent
WHERE pk_paid_cheque_return_id = binPaidChequeReturnId;

--COMMIT;
objSpOutRes.sinSqlCustomStatus := 1::SMALLINT;
objSpOutRes.strSqlCustomStatusCode := 'SUCCESS';
objSpOutRes.strSqlCustomMessage := 'PaidChequeReturn 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 := 'PaidChequeReturn 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/10)