Create tables acq.fiscal_calendar and acq.fiscal_year.

Create tables acq.fiscal_calendar and acq.fiscal_year.

Create function acq.find_bad_fy to perform sanity checks
on acq.fiscal_year.

To add to an existing database:

CREATE TABLE acq.fiscal_calendar (
	id              SERIAL         PRIMARY KEY,
	name            TEXT           NOT NULL
);

CREATE TABLE acq.fiscal_year (
	id              SERIAL         PRIMARY KEY,
	calendar        INT            NOT NULL
	                               REFERENCES acq.fiscal_calendar
	                               ON DELETE CASCADE
	                               DEFERRABLE INITIALLY DEFERRED,
	year            INT            NOT NULL,
	year_begin      TIMESTAMPTZ    NOT NULL,
	year_end        TIMESTAMPTZ    NOT NULL,
	CONSTRAINT acq_fy_logical_key  UNIQUE ( calendar, year ),
    CONSTRAINT acq_fy_physical_key UNIQUE ( calendar, year_begin )
);

CREATE OR REPLACE FUNCTION acq.find_bad_fy()
/*
	Examine the acq.fiscal_year table, comparing successive years.
	Report any inconsistencies, i.e. years that overlap or have
	gaps between them.
*/
RETURNS SETOF RECORD AS $$
DECLARE
	first_row  BOOLEAN;
	curr_year  RECORD;
	prev_year  RECORD;
	return_rec RECORD;
BEGIN
	first_row := true;
	FOR curr_year in
		SELECT
			id,
			calendar,
			year,
			year_begin,
			year_end
		FROM
			acq.fiscal_year
		ORDER BY
			calendar,
			year_begin
	LOOP
		--
		IF first_row THEN
			first_row := FALSE;
		ELSIF curr_year.calendar    = prev_year.calendar THEN
			IF curr_year.year_begin > prev_year.year_end THEN
				-- This ugly kludge works around the fact that older
				-- versions of PostgreSQL don't support RETURN QUERY SELECT
				FOR return_rec IN SELECT
					prev_year.id,
					prev_year.year,
					'Gap between fiscal years'::TEXT
				LOOP
					RETURN NEXT return_rec;
				END LOOP;
			ELSIF curr_year.year_begin < prev_year.year_end THEN
				FOR return_rec IN SELECT
					prev_year.id,
					prev_year.year,
					'Overlapping fiscal years'::TEXT
				LOOP
					RETURN NEXT return_rec;
				END LOOP;
			ELSIF curr_year.year < prev_year.year THEN
				FOR return_rec IN SELECT
					prev_year.id,
					prev_year.year,
					'Fiscal years out of order'::TEXT
				LOOP
					RETURN NEXT return_rec;
				END LOOP;
			END IF;
		END IF;
		--
		prev_year := curr_year;
	END LOOP;
	--
	RETURN;
END;
$$ LANGUAGE plpgsql;

git-svn-id: svn://svn.open-ils.org/ILS/trunk@13931 dcc99617-32d9-48b4-a31d-7c20da2025e4