money Functions

↑ money schema

This page documents all 12 function(s) in the money schema.

Function Index

Function Return Type Language Volatility Security

age_billings_and_payments()

integer

plpgsql

VOLATILE

age_billings_and_payments_for_xact(xact_id bigint)

void

sql

VOLATILE

maintain_billing_ts()

trigger

plpgsql

VOLATILE

mat_summary_create()

trigger

plpgsql

VOLATILE

mat_summary_delete()

trigger

plpgsql

VOLATILE

mat_summary_update()

trigger

plpgsql

VOLATILE

materialized_summary_billing_add()

trigger

plpgsql

VOLATILE

materialized_summary_billing_del()

trigger

plpgsql

VOLATILE

materialized_summary_billing_update()

trigger

plpgsql

VOLATILE

materialized_summary_payment_add()

trigger

plpgsql

VOLATILE

materialized_summary_payment_del()

trigger

plpgsql

VOLATILE

materialized_summary_payment_update()

trigger

plpgsql

VOLATILE

age_billings_and_payments

Signature: money.age_billings_and_payments()

Returns: integer

Language

plpgsql

Volatility

VOLATILE

Strict

No

Security Definer

No

 
-- Age billings and payments linked to transactions which were
-- completed at least 'older_than' time ago.
DECLARE
    xact_id BIGINT;
    counter INTEGER DEFAULT 0;
    keep_age INTERVAL;
BEGIN

    SELECT value::INTERVAL INTO keep_age FROM config.global_flag
        WHERE name = 'history.money.retention_age' AND enabled;

    -- Confirm interval-based aging is enabled.
    IF keep_age IS NULL THEN RETURN counter; END IF;

    -- Start with non-circulation transactions
    FOR xact_id IN SELECT DISTINCT(xact.id) FROM money.billable_xact xact
        -- confirm there is something to age
        JOIN money.billing mb ON mb.xact = xact.id
        -- Avoid aging money linked to non-aged circulations.
        LEFT JOIN action.circulation circ ON circ.id = xact.id
        WHERE circ.id IS NULL AND AGE(NOW(), xact.xact_finish) > keep_age LOOP

        PERFORM money.age_billings_and_payments_for_xact(xact_id);
        counter := counter + 1;
    END LOOP;

    -- Then handle aged circulation money.
    FOR xact_id IN SELECT DISTINCT(xact.id) FROM action.aged_circulation xact
        -- confirm there is something to age
        JOIN money.billing mb ON mb.xact = xact.id
        WHERE AGE(NOW(), xact.xact_finish) > keep_age LOOP

        PERFORM money.age_billings_and_payments_for_xact(xact_id);
        counter := counter + 1;
    END LOOP;

    RETURN counter;
END;

age_billings_and_payments_for_xact

Signature: money.age_billings_and_payments_for_xact(xact_id bigint)

Returns: void

Language

sql

Volatility

VOLATILE

Strict

No

Security Definer

No

 
    INSERT INTO money.aged_billing
        SELECT * FROM money.billing WHERE xact = $1;

    INSERT INTO money.aged_payment
        SELECT * FROM money.payment_view_for_aging WHERE xact = xact_id;

    DELETE FROM money.payment WHERE xact = $1;
    DELETE FROM money.billing WHERE xact = $1;

maintain_billing_ts

Signature: money.maintain_billing_ts()

Returns: trigger

Language

plpgsql

Volatility

VOLATILE

Strict

No

Security Definer

No

 
BEGIN
	NEW.billing_ts := COALESCE(NEW.period_end, NEW.create_date);
	RETURN NEW;
END;

mat_summary_create

Signature: money.mat_summary_create()

Returns: trigger

Language

plpgsql

Volatility

VOLATILE

Strict

No

Security Definer

No

 
BEGIN
	INSERT INTO money.materialized_billable_xact_summary (id, usr, xact_start, xact_finish, total_paid, total_owed, balance_owed, xact_type)
		VALUES ( NEW.id, NEW.usr, NEW.xact_start, NEW.xact_finish, 0.0, 0.0, 0.0, TG_ARGV[0]);
	RETURN NEW;
END;

mat_summary_delete

Signature: money.mat_summary_delete()

Returns: trigger

Language

plpgsql

Volatility

VOLATILE

Strict

No

Security Definer

No

 
BEGIN
	DELETE FROM money.materialized_billable_xact_summary WHERE id = OLD.id;
	RETURN OLD;
END;

mat_summary_update

Signature: money.mat_summary_update()

Returns: trigger

Language

plpgsql

Volatility

VOLATILE

Strict

No

Security Definer

No

 
BEGIN
	UPDATE	money.materialized_billable_xact_summary
	  SET	usr = NEW.usr,
		xact_start = NEW.xact_start,
		xact_finish = NEW.xact_finish
	  WHERE	id = NEW.id;
	RETURN NEW;
END;

materialized_summary_billing_add

Signature: money.materialized_summary_billing_add()

Returns: trigger

Language

plpgsql

Volatility

VOLATILE

Strict

No

Security Definer

No

 
BEGIN
	IF NOT NEW.voided THEN
		UPDATE	money.materialized_billable_xact_summary
		  SET	total_owed = COALESCE(total_owed, 0.0::numeric) + NEW.amount,
			last_billing_ts = NEW.billing_ts,
			last_billing_note = NEW.note,
			last_billing_type = NEW.billing_type,
			balance_owed = balance_owed + NEW.amount
		  WHERE	id = NEW.xact;
	END IF;

	RETURN NEW;
END;

materialized_summary_billing_del

Signature: money.materialized_summary_billing_del()

Returns: trigger

Language

plpgsql

Volatility

VOLATILE

Strict

No

Security Definer

No

 
DECLARE
	prev_billing	money.billing%ROWTYPE;
	old_billing	money.billing%ROWTYPE;
BEGIN
	SELECT * INTO prev_billing FROM money.billing WHERE xact = OLD.xact AND NOT voided ORDER BY billing_ts DESC LIMIT 1 OFFSET 1;
	SELECT * INTO old_billing FROM money.billing WHERE xact = OLD.xact AND NOT voided ORDER BY billing_ts DESC LIMIT 1;

	IF OLD.id = old_billing.id THEN
		UPDATE	money.materialized_billable_xact_summary
		  SET	last_billing_ts = prev_billing.billing_ts,
			last_billing_note = prev_billing.note,
			last_billing_type = prev_billing.billing_type
		  WHERE	id = OLD.xact;
	END IF;

	IF NOT OLD.voided THEN
		UPDATE	money.materialized_billable_xact_summary
		  SET	total_owed = total_owed - OLD.amount,
			balance_owed = balance_owed - OLD.amount
		  WHERE	id = OLD.xact;
	END IF;

	RETURN OLD;
END;

materialized_summary_billing_update

Signature: money.materialized_summary_billing_update()

Returns: trigger

Language

plpgsql

Volatility

VOLATILE

Strict

No

Security Definer

No

 
DECLARE
	old_billing	money.billing%ROWTYPE;
	old_voided	money.billing%ROWTYPE;
BEGIN

	SELECT * INTO old_billing FROM money.billing WHERE xact = NEW.xact AND NOT voided ORDER BY billing_ts DESC LIMIT 1;
	SELECT * INTO old_voided FROM money.billing WHERE xact = NEW.xact ORDER BY billing_ts DESC LIMIT 1;

	IF NEW.voided AND NOT OLD.voided THEN
		IF OLD.id = old_voided.id THEN
			UPDATE	money.materialized_billable_xact_summary
			  SET	last_billing_ts = old_billing.billing_ts,
				last_billing_note = old_billing.note,
				last_billing_type = old_billing.billing_type
			  WHERE	id = OLD.xact;
		END IF;

		UPDATE	money.materialized_billable_xact_summary
		  SET	total_owed = total_owed - NEW.amount,
			balance_owed = balance_owed - NEW.amount
		  WHERE	id = NEW.xact;

	ELSIF NOT NEW.voided AND OLD.voided THEN

		IF OLD.id = old_billing.id THEN
			UPDATE	money.materialized_billable_xact_summary
			  SET	last_billing_ts = old_billing.billing_ts,
				last_billing_note = old_billing.note,
				last_billing_type = old_billing.billing_type
			  WHERE	id = OLD.xact;
		END IF;

		UPDATE	money.materialized_billable_xact_summary
		  SET	total_owed = total_owed + NEW.amount,
			balance_owed = balance_owed + NEW.amount
		  WHERE	id = NEW.xact;

	ELSE
		UPDATE	money.materialized_billable_xact_summary
		  SET	total_owed = total_owed - (OLD.amount - NEW.amount),
			balance_owed = balance_owed - (OLD.amount - NEW.amount)
		  WHERE	id = NEW.xact;
	END IF;

	RETURN NEW;
END;

materialized_summary_payment_add

Signature: money.materialized_summary_payment_add()

Returns: trigger

Language

plpgsql

Volatility

VOLATILE

Strict

No

Security Definer

No

 
BEGIN
	IF NOT NEW.voided THEN
		UPDATE	money.materialized_billable_xact_summary
		  SET	total_paid = COALESCE(total_paid, 0.0::numeric) + NEW.amount,
			last_payment_ts = NEW.payment_ts,
			last_payment_note = NEW.note,
			last_payment_type = TG_ARGV[0],
			balance_owed = balance_owed - NEW.amount
		  WHERE	id = NEW.xact;
	END IF;

	RETURN NEW;
END;

materialized_summary_payment_del

Signature: money.materialized_summary_payment_del()

Returns: trigger

Language

plpgsql

Volatility

VOLATILE

Strict

No

Security Definer

No

 
DECLARE
	prev_payment	money.payment_view%ROWTYPE;
	old_payment	money.payment_view%ROWTYPE;
BEGIN
	SELECT * INTO prev_payment FROM money.payment_view WHERE xact = OLD.xact AND NOT voided ORDER BY payment_ts DESC LIMIT 1 OFFSET 1;
	SELECT * INTO old_payment FROM money.payment_view WHERE xact = OLD.xact AND NOT voided ORDER BY payment_ts DESC LIMIT 1;

	IF OLD.id = old_payment.id THEN
		UPDATE	money.materialized_billable_xact_summary
		  SET	last_payment_ts = prev_payment.payment_ts,
			last_payment_note = prev_payment.note,
			last_payment_type = prev_payment.payment_type
		  WHERE	id = OLD.xact;
	END IF;

	IF NOT OLD.voided THEN
		UPDATE	money.materialized_billable_xact_summary
		  SET	total_paid = total_paid - OLD.amount,
			balance_owed = balance_owed + OLD.amount
		  WHERE	id = OLD.xact;
	END IF;

	RETURN OLD;
END;

materialized_summary_payment_update

Signature: money.materialized_summary_payment_update()

Returns: trigger

Language

plpgsql

Volatility

VOLATILE

Strict

No

Security Definer

No

 
DECLARE
	old_payment	money.payment_view%ROWTYPE;
	old_voided	money.payment_view%ROWTYPE;
BEGIN

	SELECT * INTO old_payment FROM money.payment_view WHERE xact = NEW.xact AND NOT voided ORDER BY payment_ts DESC LIMIT 1;
	SELECT * INTO old_voided FROM money.payment_view WHERE xact = NEW.xact ORDER BY payment_ts DESC LIMIT 1;

	IF NEW.voided AND NOT OLD.voided THEN
		IF OLD.id = old_voided.id THEN
			UPDATE	money.materialized_billable_xact_summary
			  SET	last_payment_ts = old_payment.payment_ts,
				last_payment_note = old_payment.note,
				last_payment_type = old_payment.payment_type
			  WHERE	id = OLD.xact;
		END IF;

		UPDATE	money.materialized_billable_xact_summary
		  SET	total_paid = total_paid - NEW.amount,
			balance_owed = balance_owed + NEW.amount
		  WHERE	id = NEW.xact;

	ELSIF NOT NEW.voided AND OLD.voided THEN

		IF OLD.id = old_payment.id THEN
			UPDATE	money.materialized_billable_xact_summary
			  SET	last_payment_ts = old_payment.payment_ts,
				last_payment_note = old_payment.note,
				last_payment_type = old_payment.payment_type
			  WHERE	id = OLD.xact;
		END IF;

		UPDATE	money.materialized_billable_xact_summary
		  SET	total_paid = total_paid + NEW.amount,
			balance_owed = balance_owed - NEW.amount
		  WHERE	id = NEW.xact;

	ELSE
		UPDATE	money.materialized_billable_xact_summary
		  SET	total_paid = total_paid - (OLD.amount - NEW.amount),
			balance_owed = balance_owed + (OLD.amount - NEW.amount)
		  WHERE	id = NEW.xact;
	END IF;

	RETURN NEW;
END;
Previous
Next