Annotation: transactions.get_average_party_transaction

CREATE OR REPLACE FUNCTION transactions.get_average_party_transaction(party_id bigint)
RETURNS money_strict2

Information: transactions.get_average_party_transaction

Schema	transactions
Function Name	get_average_party_transaction
Arguments	party_id bigint
Owner	postgres
Result Type	money_strict2
Description

Implementation: transactions.get_average_party_transaction

CREATE OR REPLACE FUNCTION transactions.get_average_party_transaction(party_id bigint)
 RETURNS money_strict2
 LANGUAGE plpgsql
AS $function$
    DECLARE _account_id bigint= 0;
    DECLARE _debit money_strict2 = 0;
    DECLARE _credit money_strict2 = 0;
BEGIN

    IF(COALESCE($1, 0) <= 0) THEN
        RAISE EXCEPTION 'Invalid party.';
    END IF;

    _account_id := core.get_account_id_by_party_id($1);

    IF(COALESCE(_account_id, 0) <= 0) THEN
        RAISE EXCEPTION 'Invalid party.';
    END IF;

    
    SELECT SUM(amount_in_local_currency)
    INTO _debit
    FROM transactions.verified_transaction_view
    WHERE transactions.verified_transaction_view.account_id=_account_id
    AND tran_type='Dr';

    SELECT SUM(amount_in_local_currency)
    INTO _credit
    FROM transactions.verified_transaction_view
    WHERE transactions.verified_transaction_view.account_id=_account_id
    AND tran_type='Cr';

    RETURN FLOOR( (COALESCE(_credit, '0') + COALESCE(_debit, '0')) /2 );
END
$function$