-- FUNCTION: diku_mod_finance_storage.calculate_planned_encumbrance_amount(jsonb, jsonb, boolean)

-- DROP FUNCTION diku_mod_finance_storage.calculate_planned_encumbrance_amount(jsonb, jsonb, boolean);

CREATE OR REPLACE FUNCTION diku_mod_finance_storage.calculate_planned_encumbrance_amount(
	_transaction jsonb,
	_rollover_record jsonb,
	_rounding boolean)
    RETURNS numeric
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
DECLARE
		amount DECIMAL DEFAULT 0;
		encumbrance_rollover jsonb DEFAULT null;
		po_line_cost DECIMAL DEFAULT 0;
		total_amount DECIMAL DEFAULT 0;
		distribution_value DECIMAL DEFAULT 0;
	BEGIN

	    SELECT sum((jsonb->'encumbrance'->>'initialAmountEncumbered')::decimal) INTO po_line_cost
	        FROM diku_mod_finance_storage.transaction
            WHERE _rollover_record->>'fromFiscalYearId'=jsonb->>'fiscalYearId' AND jsonb->'encumbrance'->>'sourcePoLineId'=_transaction->'encumbrance'->>'sourcePoLineId'
            GROUP BY jsonb->'encumbrance'->>'sourcePoLineId';

    distribution_value := 0;

		IF
			po_line_cost > 0
		THEN
			distribution_value := (_transaction->'encumbrance'->>'initialAmountEncumbered')::decimal/po_line_cost;
	 	END IF;

		IF
		  _transaction->'encumbrance'->>'orderType'='Ongoing' AND (_transaction->'encumbrance'->>'subscription')::boolean
		THEN
			SELECT INTO encumbrance_rollover (er::jsonb) FROM jsonb_array_elements(_rollover_record->'encumbrancesRollover') er WHERE er->>'orderType'='Ongoing-Subscription';
		ELSIF
			_transaction->'encumbrance'->>'orderType'='Ongoing'
		THEN
			SELECT INTO encumbrance_rollover (er::jsonb) FROM jsonb_array_elements(_rollover_record->'encumbrancesRollover') er WHERE er->>'orderType'='Ongoing';
		ELSIF
			_transaction->'encumbrance'->>'orderType'='One-Time'
		THEN
			SELECT INTO encumbrance_rollover (er::jsonb) FROM jsonb_array_elements(_rollover_record->'encumbrancesRollover') er WHERE er->>'orderType'='One-time';
		END IF;

		IF
			encumbrance_rollover->>'basedOn'='Expended'
		THEN
		    SELECT sum((jsonb->'encumbrance'->>'amountExpended')::decimal) INTO total_amount
            	        FROM diku_mod_finance_storage.transaction
                        WHERE _rollover_record->>'fromFiscalYearId'=jsonb->>'fiscalYearId' AND jsonb->'encumbrance'->>'sourcePoLineId'=_transaction->'encumbrance'->>'sourcePoLineId'
                        GROUP BY jsonb->'encumbrance'->>'sourcePoLineId';

		ELSE
			SELECT sum((jsonb->>'amount')::decimal) INTO total_amount
                        	        FROM diku_mod_finance_storage.transaction
                                    WHERE _rollover_record->>'fromFiscalYearId'=jsonb->>'fiscalYearId' AND jsonb->'encumbrance'->>'sourcePoLineId'=_transaction->'encumbrance'->>'sourcePoLineId'
                                    GROUP BY jsonb->'encumbrance'->>'sourcePoLineId';
		END IF;
		total_amount:= total_amount + total_amount * (encumbrance_rollover->>'increaseBy')::decimal/100;
		amount := total_amount * distribution_value;
	    IF
	        _rounding IS NOT NULL AND _rounding
	    THEN
		    RETURN ROUND(amount,(_rollover_record->>'currencyFactor')::integer);
	    ELSE
            RETURN amount;
        END IF;
	END;
$BODY$;

ALTER FUNCTION diku_mod_finance_storage.calculate_planned_encumbrance_amount(jsonb, jsonb, boolean)
    OWNER TO folio_admin;