Open-ILS/src/sql/Pg/200.schema.acq.sql

   1 DROP SCHEMA acq CASCADE;
   2
   3 BEGIN;
   4
   5 CREATE SCHEMA acq;
   6
   7
   8 -- Tables
   9
  10
  11 CREATE TABLE acq.currency_type (
  12         code    TEXT PRIMARY KEY,
  13         label   TEXT
  14 );
  15
  16 -- Use the ISO 4217 abbreviations for currency codes
  17 INSERT INTO acq.currency_type (code, label) VALUES ('USD','US Dollars');
  18 INSERT INTO acq.currency_type (code, label) VALUES ('CAN','Canadian Dollars');
  19 INSERT INTO acq.currency_type (code, label) VALUES ('EUR','Euros');
  20
  21 CREATE TABLE acq.exchange_rate (
  22     id              SERIAL  PRIMARY KEY,
  23     from_currency   TEXT    NOT NULL REFERENCES acq.currency_type (code) DEFERRABLE INITIALLY DEFERRED,
  24     to_currency     TEXT    NOT NULL REFERENCES acq.currency_type (code) DEFERRABLE INITIALLY DEFERRED,
  25     ratio           NUMERIC NOT NULL,
  26     CONSTRAINT exchange_rate_from_to_once UNIQUE (from_currency,to_currency)
  27 );
  28
  29 INSERT INTO acq.exchange_rate (from_currency,to_currency,ratio) VALUES ('USD','CAN',1.2);
  30 INSERT INTO acq.exchange_rate (from_currency,to_currency,ratio) VALUES ('USD','EUR',0.5);
  31
  32 CREATE TABLE acq.provider (
  33     id                  SERIAL  PRIMARY KEY,
  34     name                TEXT    NOT NULL,
  35     owner               INT     NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
  36     currency_type       TEXT    NOT NULL REFERENCES acq.currency_type (code) DEFERRABLE INITIALLY DEFERRED,
  37     code                TEXT    UNIQUE,
  38     holding_tag         TEXT,
  39     CONSTRAINT provider_name_once_per_owner UNIQUE (name,owner)
  40 );
  41
  42 CREATE TABLE acq.provider_holding_subfield_map (
  43     id          SERIAL  PRIMARY KEY,
  44     provider    INT     NOT NULL REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED,
  45     name        TEXT    NOT NULL, -- barcode, price, etc
  46     subfield    TEXT    NOT NULL,
  47     CONSTRAINT name_once_per_provider UNIQUE (provider,name)
  48 );
  49
  50 CREATE TABLE acq.provider_address (
  51         id              SERIAL  PRIMARY KEY,
  52         valid           BOOL    NOT NULL DEFAULT TRUE,
  53         address_type    TEXT,
  54     provider    INT     NOT NULL REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED,
  55         street1         TEXT    NOT NULL,
  56         street2         TEXT,
  57         city            TEXT    NOT NULL,
  58         county          TEXT,
  59         state           TEXT    NOT NULL,
  60         country         TEXT    NOT NULL,
  61         post_code       TEXT    NOT NULL
  62 );
  63
  64 CREATE TABLE acq.provider_contact (
  65         id              SERIAL  PRIMARY KEY,
  66     provider    INT NOT NULL REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED,
  67     name    TEXT NULL NULL,
  68     role    TEXT, -- free-form.. e.g. "our sales guy"
  69     email   TEXT,
  70     phone   TEXT
  71 );
  72
  73 CREATE TABLE acq.provider_contact_address (
  74         id                      SERIAL  PRIMARY KEY,
  75         valid                   BOOL    NOT NULL DEFAULT TRUE,
  76         address_type    TEXT,
  77         contact                 INT         NOT NULL REFERENCES acq.provider_contact (id) DEFERRABLE INITIALLY DEFERRED,
  78         street1                 TEXT    NOT NULL,
  79         street2                 TEXT,
  80         city                    TEXT    NOT NULL,
  81         county                  TEXT,
  82         state                   TEXT    NOT NULL,
  83         country                 TEXT    NOT NULL,
  84         post_code               TEXT    NOT NULL
  85 );
  86
  87
  88 CREATE TABLE acq.funding_source (
  89         id              SERIAL  PRIMARY KEY,
  90         name            TEXT    NOT NULL,
  91         owner           INT     NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
  92         currency_type   TEXT    NOT NULL REFERENCES acq.currency_type (code) DEFERRABLE INITIALLY DEFERRED,
  93         code            TEXT    UNIQUE,
  94         CONSTRAINT funding_source_name_once_per_owner UNIQUE (name,owner)
  95 );
  96
  97 CREATE TABLE acq.funding_source_credit (
  98         id      SERIAL  PRIMARY KEY,
  99         funding_source    INT     NOT NULL REFERENCES acq.funding_source (id) DEFERRABLE INITIALLY DEFERRED,
 100         amount  NUMERIC NOT NULL,
 101         note    TEXT
 102 );
 103
 104 CREATE TABLE acq.fund (
 105     id              SERIAL  PRIMARY KEY,
 106     org             INT     NOT NULL REFERENCES actor.org_unit (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 107     name            TEXT    NOT NULL,
 108     year            INT     NOT NULL DEFAULT EXTRACT( YEAR FROM NOW() ),
 109     currency_type   TEXT    NOT NULL REFERENCES acq.currency_type (code) DEFERRABLE INITIALLY DEFERRED,
 110     code            TEXT    UNIQUE,
 111     CONSTRAINT name_once_per_org_year UNIQUE (org,name,year)
 112 );
 113
 114 CREATE TABLE acq.fund_debit (
 115         id                      SERIAL  PRIMARY KEY,
 116         fund                    INT     NOT NULL REFERENCES acq.fund (id) DEFERRABLE INITIALLY DEFERRED,
 117         origin_amount           NUMERIC NOT NULL,  -- pre-exchange-rate amount
 118         origin_currency_type    TEXT    NOT NULL REFERENCES acq.currency_type (code) DEFERRABLE INITIALLY DEFERRED,
 119         amount                  NUMERIC NOT NULL,
 120         encumbrance             BOOL    NOT NULL DEFAULT TRUE,
 121         debit_type              TEXT    NOT NULL,
 122         xfer_destination        INT     REFERENCES acq.fund (id) DEFERRABLE INITIALLY DEFERRED
 123 );
 124
 125 CREATE TABLE acq.fund_allocation (
 126     id          SERIAL  PRIMARY KEY,
 127     funding_source        INT     NOT NULL REFERENCES acq.funding_source (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 128     fund        INT     NOT NULL REFERENCES acq.fund (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 129     amount      NUMERIC,
 130     percent     NUMERIC CHECK (percent IS NULL OR percent BETWEEN 0.0 AND 100.0),
 131     allocator   INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 132     note        TEXT,
 133     CONSTRAINT allocation_amount_or_percent CHECK ((percent IS NULL AND amount IS NOT NULL) OR (percent IS NOT NULL AND amount IS NULL))
 134 );
 135
 136
 137 CREATE TABLE acq.picklist (
 138         id              SERIAL                          PRIMARY KEY,
 139         owner           INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 140         creator         INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 141         editor          INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 142         org_unit        INT                             NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 143         name            TEXT                            NOT NULL,
 144         create_time     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 145         edit_time       TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 146         CONSTRAINT name_once_per_owner UNIQUE (name,owner)
 147 );
 148
 149 CREATE TABLE acq.purchase_order (
 150         id              SERIAL                          PRIMARY KEY,
 151         owner           INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 152         creator         INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 153         editor          INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 154         ordering_agency INT                             NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 155         create_time     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 156         edit_time       TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 157         provider        INT                             NOT NULL REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED,
 158         state           TEXT                            NOT NULL DEFAULT 'new'
 159 );
 160 CREATE INDEX po_owner_idx ON acq.purchase_order (owner);
 161 CREATE INDEX po_provider_idx ON acq.purchase_order (provider);
 162 CREATE INDEX po_state_idx ON acq.purchase_order (state);
 163
 164 CREATE TABLE acq.po_note (
 165         id              SERIAL                          PRIMARY KEY,
 166         purchase_order  INT                             NOT NULL REFERENCES acq.purchase_order (id) DEFERRABLE INITIALLY DEFERRED,
 167         creator         INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 168         editor          INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 169         create_time     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 170         edit_time       TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 171         value           TEXT                            NOT NULL
 172 );
 173 CREATE INDEX po_note_po_idx ON acq.po_note (purchase_order);
 174
 175 CREATE TABLE acq.lineitem (
 176         id                  BIGSERIAL                   PRIMARY KEY,
 177         creator             INT                         NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 178         editor              INT                         NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 179         selector            INT                         NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 180         provider            INT                         REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED,
 181         purchase_order      INT                         REFERENCES acq.purchase_order (id) DEFERRABLE INITIALLY DEFERRED,
 182         picklist            INT                         REFERENCES acq.picklist (id) DEFERRABLE INITIALLY DEFERRED,
 183         expected_recv_time  TIMESTAMP WITH TIME ZONE,
 184         create_time         TIMESTAMP WITH TIME ZONE    NOT NULL DEFAULT NOW(),
 185         edit_time           TIMESTAMP WITH TIME ZONE    NOT NULL DEFAULT NOW(),
 186         marc                TEXT                        NOT NULL,
 187         eg_bib_id           INT                         REFERENCES biblio.record_entry (id) DEFERRABLE INITIALLY DEFERRED,
 188         source_label        TEXT,
 189         item_count          INT                         NOT NULL DEFAULT 0,
 190         state               TEXT                        NOT NULL DEFAULT 'new',
 191     CONSTRAINT picklist_or_po CHECK (picklist IS NOT NULL OR purchase_order IS NOT NULL)
 192 );
 193 CREATE INDEX li_po_idx ON acq.lineitem (purchase_order);
 194 CREATE INDEX li_pl_idx ON acq.lineitem (picklist);
 195
 196 CREATE TABLE acq.lineitem_note (
 197         id              SERIAL                          PRIMARY KEY,
 198         lineitem        INT                             NOT NULL REFERENCES acq.lineitem (id) DEFERRABLE INITIALLY DEFERRED,
 199         creator         INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 200         editor          INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 201         create_time     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 202         edit_time       TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 203         value           TEXT                            NOT NULL
 204 );
 205 CREATE INDEX li_note_li_idx ON acq.lineitem_note (lineitem);
 206
 207 CREATE TABLE acq.lineitem_detail (
 208     id          BIGSERIAL       PRIMARY KEY,
 209     lineitem    INT         NOT NULL REFERENCES acq.lineitem (id) DEFERRABLE INITIALLY DEFERRED,
 210     fund        INT         REFERENCES acq.fund (id) DEFERRABLE INITIALLY DEFERRED,
 211     fund_debit  INT         REFERENCES acq.fund_debit (id) DEFERRABLE INITIALLY DEFERRED,
 212     eg_copy_id  BIGINT      REFERENCES asset.copy (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
 213     barcode     TEXT,
 214     cn_label    TEXT,
 215     note        TEXT,
 216     circ_modifier   TEXT    REFERENCES config.circ_modifier (code) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
 217     owning_lib  INT         REFERENCES actor.org_unit (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
 218     location    INT         REFERENCES asset.copy_location (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
 219     recv_time   TIMESTAMP WITH TIME ZONE
 220 );
 221
 222 CREATE INDEX li_detail_li_idx ON acq.lineitem_detail (lineitem);
 223
 224 CREATE TABLE acq.lineitem_attr_definition (
 225         id              BIGSERIAL       PRIMARY KEY,
 226         code            TEXT            NOT NULL,
 227         description     TEXT            NOT NULL,
 228         remove          TEXT            NOT NULL DEFAULT '',
 229         ident           BOOL            NOT NULL DEFAULT FALSE
 230 );
 231
 232 CREATE TABLE acq.lineitem_marc_attr_definition (
 233         id              BIGINT  PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq'),
 234         xpath           TEXT            NOT NULL
 235 ) INHERITS (acq.lineitem_attr_definition);
 236
 237 CREATE TABLE acq.lineitem_provider_attr_definition (
 238         id              BIGINT  PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq'),
 239         xpath           TEXT            NOT NULL,
 240         provider        INT     NOT NULL REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED
 241 ) INHERITS (acq.lineitem_attr_definition);
 242
 243 CREATE TABLE acq.lineitem_generated_attr_definition (
 244         id              BIGINT  PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq'),
 245         xpath           TEXT            NOT NULL
 246 ) INHERITS (acq.lineitem_attr_definition);
 247
 248 CREATE TABLE acq.lineitem_usr_attr_definition (
 249         id              BIGINT  PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq'),
 250         usr             INT     NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED
 251 ) INHERITS (acq.lineitem_attr_definition);
 252
 253 CREATE TABLE acq.lineitem_local_attr_definition (
 254         id              BIGINT  PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq')
 255 ) INHERITS (acq.lineitem_attr_definition);
 256
 257 CREATE TABLE acq.lineitem_attr (
 258         id              BIGSERIAL       PRIMARY KEY,
 259         definition      BIGINT          NOT NULL,
 260         lineitem        BIGINT          NOT NULL REFERENCES acq.lineitem (id) DEFERRABLE INITIALLY DEFERRED,
 261         attr_type       TEXT            NOT NULL,
 262         attr_name       TEXT            NOT NULL,
 263         attr_value      TEXT            NOT NULL
 264 );
 265
 266 CREATE INDEX li_attr_li_idx ON acq.lineitem_attr (lineitem);
 267 CREATE INDEX li_attr_value_idx ON acq.lineitem_attr (attr_value);
 268 CREATE INDEX li_attr_definition_idx ON acq.lineitem_attr (definition);
 269
 270
 271 -- Seed data
 272
 273
 274 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('title','Title of work','//*[@tag="245"]/*[contains("abcmnopr",@code)]');
 275 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('author','Author of work','//*[@tag="100" or @tag="110" or @tag="113"]/*[contains("ad",@code)]');
 276 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('language','Lanuage of work','//*[@tag="240"]/*[@code="l"][1]');
 277 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('pagination','Pagination','//*[@tag="300"]/*[@code="a"][1]');
 278 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath, remove ) VALUES ('isbn','ISBN','//*[@tag="020"]/*[@code="a"]', $r$(?:-|\s.+$)$r$);
 279 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath, remove ) VALUES ('issn','ISSN','//*[@tag="022"]/*[@code="a"]', $r$(?:-|\s.+$)$r$);
 280 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('price','Price','//*[@tag="020" or @tag="022"]/*[@code="c"][1]');
 281 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('identifier','Identifier','//*[@tag="001"]');
 282 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('publisher','Publisher','//*[@tag="260"]/*[@code="b"][1]');
 283 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('pubdate','Publication Date','//*[@tag="260"]/*[@code="c"][1]');
 284 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('edition','Edition','//*[@tag="250"]/*[@code="a"][1]');
 285
 286
 287 CREATE TABLE acq.distribution_formula (
 288         id              SERIAL PRIMARY KEY,
 289         owner   INT NOT NULL
 290                         REFERENCES actor.org_unit(id) DEFERRABLE INITIALLY DEFERRED,
 291         name    TEXT NOT NULL,
 292         skip_count      INT NOT NULL DEFAULT 0,
 293         CONSTRAINT acqdf_name_once_per_owner UNIQUE (name, owner)
 294 );
 295
 296 CREATE TABLE acq.distribution_formula_entry (
 297         id                      SERIAL PRIMARY KEY,
 298         formula         INTEGER NOT NULL REFERENCES acq.distribution_formula(id)
 299                                 ON DELETE CASCADE
 300                                 DEFERRABLE INITIALLY DEFERRED,
 301         position        INTEGER NOT NULL,
 302         item_count      INTEGER NOT NULL,
 303         owning_lib      INTEGER REFERENCES actor.org_unit(id)
 304                                 DEFERRABLE INITIALLY DEFERRED,
 305         location        INTEGER REFERENCES asset.copy_location(id),
 306         CONSTRAINT acqdfe_lib_once_per_formula UNIQUE( formula, position ),
 307         CONSTRAINT acqdfe_must_be_somewhere
 308                                 CHECK( owning_lib IS NOT NULL OR location IS NOT NULL )
 309 );
 310
 311
 312 -- Functions
 313
 314 CREATE TYPE acq.flat_lineitem_holding_subfield AS (lineitem int, holding int, subfield text, data text);
 315 CREATE OR REPLACE FUNCTION acq.extract_holding_attr_table (lineitem int, tag text) RETURNS SETOF acq.flat_lineitem_holding_subfield AS $$
 316 DECLARE
 317     counter INT;
 318     lida    acq.flat_lineitem_holding_subfield%ROWTYPE;
 319 BEGIN
 320
 321     SELECT  COUNT(*) INTO counter
 322       FROM  xpath_table(
 323                 'id',
 324                 'marc',
 325                 'acq.lineitem',
 326                 '//*[@tag="' || tag || '"]',
 327                 'id=' || lineitem
 328             ) as t(i int,c text);
 329
 330     FOR i IN 1 .. counter LOOP
 331         FOR lida IN
 332             SELECT  *
 333               FROM  (   SELECT  id,i,t,v
 334                           FROM  xpath_table(
 335                                     'id',
 336                                     'marc',
 337                                     'acq.lineitem',
 338                                     '//*[@tag="' || tag || '"][position()=' || i || ']/*/@code|' ||
 339                                         '//*[@tag="' || tag || '"][position()=' || i || ']/*[@code]',
 340                                     'id=' || lineitem
 341                                 ) as t(id int,t text,v text)
 342                     )x
 343         LOOP
 344             RETURN NEXT lida;
 345         END LOOP;
 346     END LOOP;
 347
 348     RETURN;
 349 END;
 350 $$ LANGUAGE PLPGSQL;
 351
 352 CREATE TYPE acq.flat_lineitem_detail AS (lineitem int, holding int, attr text, data text);
 353 CREATE OR REPLACE FUNCTION acq.extract_provider_holding_data ( lineitem_i int ) RETURNS SETOF acq.flat_lineitem_detail AS $$
 354 DECLARE
 355     prov_i  INT;
 356     tag_t   TEXT;
 357     lida    acq.flat_lineitem_detail%ROWTYPE;
 358 BEGIN
 359     SELECT provider INTO prov_i FROM acq.lineitem WHERE id = lineitem_i;
 360     IF NOT FOUND THEN RETURN; END IF;
 361
 362     SELECT holding_tag INTO tag_t FROM acq.provider WHERE id = prov_i;
 363     IF NOT FOUND OR tag_t IS NULL THEN RETURN; END IF;
 364
 365     FOR lida IN
 366         SELECT  lineitem_i,
 367                 h.holding,
 368                 a.name,
 369                 h.data
 370           FROM  acq.extract_holding_attr_table( lineitem_i, tag_t ) h
 371                 JOIN acq.provider_holding_subfield_map a USING (subfield)
 372           WHERE a.provider = prov_i
 373     LOOP
 374         RETURN NEXT lida;
 375     END LOOP;
 376
 377     RETURN;
 378 END;
 379 $$ LANGUAGE PLPGSQL;
 380
 381 -- select * from acq.extract_provider_holding_data(699);
 382
 383 CREATE OR REPLACE FUNCTION public.extract_acq_marc_field ( BIGINT, TEXT, TEXT) RETURNS TEXT AS $$
 384         SELECT public.extract_marc_field('acq.lineitem', $1, $2, $3);
 385 $$ LANGUAGE SQL;
 386
 387 /*
 388 CREATE OR REPLACE FUNCTION public.extract_bib_marc_field ( BIGINT, TEXT ) RETURNS TEXT AS $$
 389         SELECT public.extract_marc_field('biblio.record_entry', $1, $2);
 390 $$ LANGUAGE SQL;
 391
 392 CREATE OR REPLACE FUNCTION public.extract_authority_marc_field ( BIGINT, TEXT ) RETURNS TEXT AS $$
 393         SELECT public.extract_marc_field('authority.record_entry', $1, $2);
 394 $$ LANGUAGE SQL;
 395 */
 396 -- For example:
 397 -- INSERT INTO acq.lineitem_provider_attr_definition ( provider, code, description, xpath ) VALUES (1,'price','Price','//*[@tag="020" or @tag="022"]/*[@code="a"][1]');
 398
 399 /*
 400 Suggested vendor fields:
 401         vendor_price
 402         vendor_currency
 403         vendor_avail
 404         vendor_po
 405         vendor_identifier
 406 */
 407
 408 CREATE OR REPLACE FUNCTION public.ingest_acq_marc ( ) RETURNS TRIGGER AS $$
 409 DECLARE
 410         value           TEXT;
 411         atype           TEXT;
 412         prov            INT;
 413         adef            RECORD;
 414         xpath_string    TEXT;
 415 BEGIN
 416         FOR adef IN SELECT *,tableoid FROM acq.lineitem_attr_definition LOOP
 417
 418                 SELECT relname::TEXT INTO atype FROM pg_class WHERE oid = adef.tableoid;
 419
 420                 IF (atype NOT IN ('lineitem_usr_attr_definition','lineitem_local_attr_definition')) THEN
 421                         IF (atype = 'lineitem_provider_attr_definition') THEN
 422                                 SELECT provider INTO prov FROM acq.lineitem_provider_attr_definition WHERE id = adef.id;
 423                                 CONTINUE WHEN NEW.provider IS NULL OR prov <> NEW.provider;
 424                         END IF;
 425
 426                         IF (atype = 'lineitem_provider_attr_definition') THEN
 427                                 SELECT xpath INTO xpath_string FROM acq.lineitem_provider_attr_definition WHERE id = adef.id;
 428                         ELSIF (atype = 'lineitem_marc_attr_definition') THEN
 429                                 SELECT xpath INTO xpath_string FROM acq.lineitem_marc_attr_definition WHERE id = adef.id;
 430                         ELSIF (atype = 'lineitem_generated_attr_definition') THEN
 431                                 SELECT xpath INTO xpath_string FROM acq.lineitem_generated_attr_definition WHERE id = adef.id;
 432                         END IF;
 433
 434                         SELECT extract_acq_marc_field(id, xpath_string, adef.remove) INTO value FROM acq.lineitem WHERE id = NEW.id;
 435
 436                         IF (value IS NOT NULL AND value <> '') THEN
 437                                 INSERT INTO acq.lineitem_attr (lineitem, definition, attr_type, attr_name, attr_value)
 438                                         VALUES (NEW.id, adef.id, atype, adef.code, value);
 439                         END IF;
 440
 441                 END IF;
 442
 443         END LOOP;
 444
 445         RETURN NULL;
 446 END;
 447 $$ LANGUAGE PLPGSQL;
 448
 449 CREATE OR REPLACE FUNCTION public.cleanup_acq_marc ( ) RETURNS TRIGGER AS $$
 450 BEGIN
 451         IF TG_OP = 'UPDATE' THEN
 452                 DELETE FROM acq.lineitem_attr
 453                         WHERE lineitem = OLD.id AND attr_type IN ('lineitem_provider_attr_definition', 'lineitem_marc_attr_definition','lineitem_generated_attr_definition');
 454                 RETURN NEW;
 455         ELSE
 456                 DELETE FROM acq.lineitem_attr WHERE lineitem = OLD.id;
 457                 RETURN OLD;
 458         END IF;
 459 END;
 460 $$ LANGUAGE PLPGSQL;
 461
 462 CREATE TRIGGER cleanup_lineitem_trigger
 463         BEFORE UPDATE OR DELETE ON acq.lineitem
 464         FOR EACH ROW EXECUTE PROCEDURE public.cleanup_acq_marc();
 465
 466 CREATE TRIGGER ingest_lineitem_trigger
 467         AFTER INSERT OR UPDATE ON acq.lineitem
 468         FOR EACH ROW EXECUTE PROCEDURE public.ingest_acq_marc();
 469
 470 CREATE OR REPLACE FUNCTION acq.exchange_ratio ( from_ex TEXT, to_ex TEXT ) RETURNS NUMERIC AS $$
 471 DECLARE
 472     rat NUMERIC;
 473 BEGIN
 474     IF from_ex = to_ex THEN
 475         RETURN 1.0;
 476     END IF;
 477
 478     SELECT ratio INTO rat FROM acq.exchange_rate WHERE from_currency = from_ex AND to_currency = to_ex;
 479
 480     IF FOUND THEN
 481         RETURN rat;
 482     ELSE
 483         SELECT ratio INTO rat FROM acq.exchange_rate WHERE from_currency = to_ex AND to_currency = from_ex;
 484         IF FOUND THEN
 485             RETURN 1.0/rat;
 486         END IF;
 487     END IF;
 488
 489     RETURN NULL;
 490
 491 END;
 492 $$ LANGUAGE PLPGSQL;
 493
 494 CREATE OR REPLACE FUNCTION acq.exchange_ratio ( TEXT, TEXT, NUMERIC ) RETURNS NUMERIC AS $$
 495     SELECT $3 * acq.exchange_ratio($1, $2);
 496 $$ LANGUAGE SQL;
 497
 498 CREATE OR REPLACE VIEW acq.funding_source_credit_total AS
 499     SELECT  funding_source,
 500             SUM(amount) AS amount
 501       FROM  acq.funding_source_credit
 502       GROUP BY 1;
 503
 504 CREATE OR REPLACE VIEW acq.funding_source_allocation_total AS
 505     SELECT  funding_source,
 506             SUM(amount)::NUMERIC(100,2) AS amount
 507       FROM (
 508             SELECT  funding_source,
 509                     SUM(a.amount)::NUMERIC(100,2) AS amount
 510               FROM  acq.fund_allocation a
 511               WHERE a.percent IS NULL
 512               GROUP BY 1
 513                             UNION ALL
 514             SELECT  funding_source,
 515                     SUM( (SELECT SUM(amount) FROM acq.funding_source_credit c WHERE c.funding_source = a.funding_source) * (a.percent/100.0) )::NUMERIC(100,2) AS amount
 516               FROM  acq.fund_allocation a
 517               WHERE a.amount IS NULL
 518               GROUP BY 1
 519         ) x
 520       GROUP BY 1;
 521
 522 CREATE OR REPLACE VIEW acq.funding_source_balance AS
 523     SELECT  COALESCE(c.funding_source, a.funding_source) AS funding_source,
 524             SUM(COALESCE(c.amount,0.0) - COALESCE(a.amount,0.0))::NUMERIC(100,2) AS amount
 525       FROM  acq.funding_source_credit_total c
 526             FULL JOIN acq.funding_source_allocation_total a USING (funding_source)
 527       GROUP BY 1;
 528
 529 CREATE OR REPLACE VIEW acq.fund_allocation_total AS
 530     SELECT  fund,
 531             SUM(amount)::NUMERIC(100,2) AS amount
 532       FROM (
 533             SELECT  fund,
 534                     SUM(a.amount * acq.exchange_ratio(s.currency_type, f.currency_type))::NUMERIC(100,2) AS amount
 535               FROM  acq.fund_allocation a
 536                     JOIN acq.fund f ON (a.fund = f.id)
 537                     JOIN acq.funding_source s ON (a.funding_source = s.id)
 538               WHERE a.percent IS NULL
 539               GROUP BY 1
 540                             UNION ALL
 541             SELECT  fund,
 542                     SUM( (SELECT SUM(amount) FROM acq.funding_source_credit c WHERE c.funding_source = a.funding_source) * acq.exchange_ratio(s.currency_type, f.currency_type) * (a.percent/100.0) )::NUMERIC(100,2) AS amount
 543               FROM  acq.fund_allocation a
 544                     JOIN acq.fund f ON (a.fund = f.id)
 545                     JOIN acq.funding_source s ON (a.funding_source = s.id)
 546               WHERE a.amount IS NULL
 547               GROUP BY 1
 548         ) x
 549       GROUP BY 1;
 550
 551 CREATE OR REPLACE VIEW acq.fund_debit_total AS
 552     SELECT  id AS fund,
 553             encumbrance,
 554             SUM(amount) AS amount
 555       FROM  acq.fund_debit
 556       GROUP BY 1,2;
 557
 558 CREATE OR REPLACE VIEW acq.fund_encumbrance_total AS
 559     SELECT  fund,
 560             SUM(amount) AS amount
 561       FROM  acq.fund_debit_total
 562       WHERE encumbrance IS TRUE
 563       GROUP BY 1;
 564
 565 CREATE OR REPLACE VIEW acq.fund_spent_total AS
 566     SELECT  fund,
 567             SUM(amount) AS amount
 568       FROM  acq.fund_debit_total
 569       WHERE encumbrance IS FALSE
 570       GROUP BY 1;
 571
 572 CREATE OR REPLACE VIEW acq.fund_combined_balance AS
 573     SELECT  c.fund,
 574             c.amount - COALESCE(d.amount,0.0) AS amount
 575       FROM  acq.fund_allocation_total c
 576             LEFT JOIN acq.fund_debit_total d USING (fund);
 577
 578 CREATE OR REPLACE VIEW acq.fund_spent_balance AS
 579     SELECT  c.fund,
 580             c.amount - COALESCE(d.amount,0.0) AS amount
 581       FROM  acq.fund_allocation_total c
 582             LEFT JOIN acq.fund_spent_total d USING (fund);
 583
 584 COMMIT;
 585
 586
 587
 588