$$ LANGUAGE plpgsql;
--- Function for testing whether circ should be aged.
--- If circ CANNOT be aged based on exceptions and user settings, function returns NULL.
--- If circ CAN be aged, function returns xact_finish timestamp of last circ in chain.
+-- function for testing whether circ should be aged
CREATE OR REPLACE FUNCTION sitka.age_circ_test(circ_id BIGINT, cutoff DATE, skip_basic_tests BOOL DEFAULT FALSE) RETURNS TIMESTAMPTZ AS $$
DECLARE
potential_aged_circ action.circulation%ROWTYPE;
-- so we provide an option to skip those basic tests.
IF skip_basic_tests IS FALSE THEN
IF potential_aged_circ.xact_finish IS NULL THEN
- --RAISE NOTICE 'Cannot age circ %, circ is not closed', circ_id;
- RETURN NULL;
+ RETURN FALSE;
END IF;
SELECT balance_owed INTO balance FROM money.materialized_billable_xact_summary WHERE id = circ_id;
IF balance != 0 THEN
- --RAISE NOTICE 'Cannot age circ %, non-zero balance', circ_id;
- RETURN NULL;
+ RETURN FALSE;
END IF;
END IF;
-- user's history.circ.retention_start setting, if any, overrides default cutoff date
SELECT value INTO usr_keep_start FROM actor.usr_setting WHERE usr = potential_aged_circ.usr AND name = 'history.circ.retention_start'; IF usr_keep_start IS NOT NULL THEN
circ_cutoff := oils_json_to_text(usr_keep_start)::DATE;
- --RAISE NOTICE 'circ cutoff adjusted to % for circ %', circ_cutoff, circ_id;
END IF;
-- trim cutoff to user's history.circ.retention_age, if set
SELECT (now() - usr_keep_age::INTERVAL)::DATE INTO usr_keep_date;
IF usr_keep_date IS NOT NULL AND usr_keep_date < circ_cutoff THEN
circ_cutoff := usr_keep_date;
- --RAISE NOTICE 'circ cutoff adjusted to % for circ %', circ_cutoff, circ_id;
END IF;
END IF;
-- don't age the chain if the last circ is later than the cutoff
IF circ_chain_finish IS NULL OR circ_chain_finish > circ_cutoff THEN
- --RAISE NOTICE 'Cannot age circ %, circ chain is still open or was open on cutoff date', circ_id;
- RETURN NULL;
+ RETURN FALSE;
END IF;
-- don't age the circ if it matches a defined exception
has_exception := FALSE;
SELECT * INTO has_exception FROM sitka.has_age_circ_exception(potential_aged_circ.id);
IF has_exception IS TRUE THEN
- --RAISE NOTICE 'Cannot age circ %, has exceptions', circ_id;
- RETURN NULL;
+ RETURN FALSE;
END IF;
- -- this circ passes all our tests; return the timestamp when the circ chain was closed
- RETURN circ_chain_finish;
+ -- this circ passes all our tests
+ RETURN TRUE;
EXCEPTION WHEN OTHERS THEN
RAISE WARNING 'Cannot age circ %, error during age circ test', circ_id;
- RETURN NULL;
+ RETURN FALSE;
END;
$$ LANGUAGE plpgsql;
\echo 'table for aged circs'
CREATE TABLE sitka.aged_circs (
id BIGINT NOT NULL,
- circ_chain_tail_xact_finish TIMESTAMPTZ NOT NULL,
delete_date DATE NOT NULL
);
DECLARE
today DATE;
potential_aged_circ BIGINT;
- circ_chain_finish TIMESTAMPTZ;
+ can_age_circ BOOL;
circs_aged INT;
BEGIN
today := now()::DATE;
-- TODO: more strictness here?
CREATE TEMPORARY TABLE circs_to_age AS
- SELECT c.id, NULL::TIMESTAMP WITH TIME ZONE AS circ_chain_tail_xact_finish
- FROM action.circulation c
+ SELECT c.id FROM action.circulation c
JOIN money.materialized_billable_xact_summary m ON c.id = m.id
WHERE c.xact_finish IS NOT NULL AND c.xact_finish < cutoff
AND m.balance_owed = 0
SELECT id FROM circs_to_age
LOOP
- circ_chain_finish := NULL;
+ can_age_circ := NULL;
-- test whether we can age each circ
- SELECT * INTO circ_chain_finish FROM sitka.age_circ_test(potential_aged_circ, cutoff, TRUE);
- IF circ_chain_finish IS NOT NULL THEN
- UPDATE circs_to_age SET circ_chain_tail_xact_finish = circ_chain_finish WHERE id = potential_aged_circ;
- ELSE
+ SELECT * INTO can_age_circ FROM sitka.age_circ_test(potential_aged_circ, cutoff, TRUE);
+ IF can_age_circ IS FALSE OR can_age_circ IS NULL THEN
DELETE FROM circs_to_age WHERE id = potential_aged_circ;
END IF;
SET usr_post_code = NULL, usr_birth_year = NULL
WHERE usr_post_code IS NOT NULL OR usr_birth_year IS NOT NULL;
- INSERT INTO sitka.aged_circs (id, circ_chain_tail_xact_finish, delete_date)
- SELECT id, circ_chain_tail_xact_finish, today FROM circs_to_age;
+ INSERT INTO sitka.aged_circs (id, delete_date)
+ SELECT id, today FROM circs_to_age;
SELECT count(*) INTO circs_aged FROM circs_to_age;
DROP TABLE circs_to_age;