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         create_time     TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
 124 );
 125
 126 CREATE TABLE acq.fund_allocation (
 127     id          SERIAL  PRIMARY KEY,
 128     funding_source        INT     NOT NULL REFERENCES acq.funding_source (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 129     fund        INT     NOT NULL REFERENCES acq.fund (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 130     amount      NUMERIC,
 131     percent     NUMERIC CHECK (percent IS NULL OR percent BETWEEN 0.0 AND 100.0),
 132     allocator   INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 133     note        TEXT,
 134         create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
 135     CONSTRAINT allocation_amount_or_percent CHECK ((percent IS NULL AND amount IS NOT NULL) OR (percent IS NOT NULL AND amount IS NULL))
 136 );
 137
 138
 139 CREATE TABLE acq.picklist (
 140         id              SERIAL                          PRIMARY KEY,
 141         owner           INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 142         creator         INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 143         editor          INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 144         org_unit        INT                             NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 145         name            TEXT                            NOT NULL,
 146         create_time     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 147         edit_time       TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 148         CONSTRAINT name_once_per_owner UNIQUE (name,owner)
 149 );
 150
 151 CREATE TABLE acq.purchase_order (
 152         id              SERIAL                          PRIMARY KEY,
 153         owner           INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 154         creator         INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 155         editor          INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 156         ordering_agency INT                             NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 157         create_time     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 158         edit_time       TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 159         provider        INT                             NOT NULL REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED,
 160         state           TEXT                            NOT NULL DEFAULT 'new'
 161 );
 162 CREATE INDEX po_owner_idx ON acq.purchase_order (owner);
 163 CREATE INDEX po_provider_idx ON acq.purchase_order (provider);
 164 CREATE INDEX po_state_idx ON acq.purchase_order (state);
 165
 166 CREATE TABLE acq.po_note (
 167         id              SERIAL                          PRIMARY KEY,
 168         purchase_order  INT                             NOT NULL REFERENCES acq.purchase_order (id) DEFERRABLE INITIALLY DEFERRED,
 169         creator         INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 170         editor          INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 171         create_time     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 172         edit_time       TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 173         value           TEXT                            NOT NULL
 174 );
 175 CREATE INDEX po_note_po_idx ON acq.po_note (purchase_order);
 176
 177 CREATE TABLE acq.lineitem (
 178         id                  BIGSERIAL                   PRIMARY KEY,
 179         creator             INT                         NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 180         editor              INT                         NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 181         selector            INT                         NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 182         provider            INT                         REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED,
 183         purchase_order      INT                         REFERENCES acq.purchase_order (id) DEFERRABLE INITIALLY DEFERRED,
 184         picklist            INT                         REFERENCES acq.picklist (id) DEFERRABLE INITIALLY DEFERRED,
 185         expected_recv_time  TIMESTAMP WITH TIME ZONE,
 186         create_time         TIMESTAMP WITH TIME ZONE    NOT NULL DEFAULT NOW(),
 187         edit_time           TIMESTAMP WITH TIME ZONE    NOT NULL DEFAULT NOW(),
 188         marc                TEXT                        NOT NULL,
 189         eg_bib_id           INT                         REFERENCES biblio.record_entry (id) DEFERRABLE INITIALLY DEFERRED,
 190         source_label        TEXT,
 191         item_count          INT                         NOT NULL DEFAULT 0,
 192         state               TEXT                        NOT NULL DEFAULT 'new',
 193     CONSTRAINT picklist_or_po CHECK (picklist IS NOT NULL OR purchase_order IS NOT NULL)
 194 );
 195 CREATE INDEX li_po_idx ON acq.lineitem (purchase_order);
 196 CREATE INDEX li_pl_idx ON acq.lineitem (picklist);
 197
 198 CREATE TABLE acq.lineitem_note (
 199         id              SERIAL                          PRIMARY KEY,
 200         lineitem        INT                             NOT NULL REFERENCES acq.lineitem (id) DEFERRABLE INITIALLY DEFERRED,
 201         creator         INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 202         editor          INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 203         create_time     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 204         edit_time       TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 205         value           TEXT                            NOT NULL
 206 );
 207 CREATE INDEX li_note_li_idx ON acq.lineitem_note (lineitem);
 208
 209 CREATE TABLE acq.lineitem_detail (
 210     id          BIGSERIAL       PRIMARY KEY,
 211     lineitem    INT         NOT NULL REFERENCES acq.lineitem (id) DEFERRABLE INITIALLY DEFERRED,
 212     fund        INT         REFERENCES acq.fund (id) DEFERRABLE INITIALLY DEFERRED,
 213     fund_debit  INT         REFERENCES acq.fund_debit (id) DEFERRABLE INITIALLY DEFERRED,
 214     eg_copy_id  BIGINT      REFERENCES asset.copy (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
 215     barcode     TEXT,
 216     cn_label    TEXT,
 217     note        TEXT,
 218     circ_modifier   TEXT    REFERENCES config.circ_modifier (code) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
 219     owning_lib  INT         REFERENCES actor.org_unit (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
 220     location    INT         REFERENCES asset.copy_location (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
 221     recv_time   TIMESTAMP WITH TIME ZONE
 222 );
 223
 224 CREATE INDEX li_detail_li_idx ON acq.lineitem_detail (lineitem);
 225
 226 CREATE TABLE acq.lineitem_attr_definition (
 227         id              BIGSERIAL       PRIMARY KEY,
 228         code            TEXT            NOT NULL,
 229         description     TEXT            NOT NULL,
 230         remove          TEXT            NOT NULL DEFAULT '',
 231         ident           BOOL            NOT NULL DEFAULT FALSE
 232 );
 233
 234 CREATE TABLE acq.lineitem_marc_attr_definition (
 235         id              BIGINT  PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq'),
 236         xpath           TEXT            NOT NULL
 237 ) INHERITS (acq.lineitem_attr_definition);
 238
 239 CREATE TABLE acq.lineitem_provider_attr_definition (
 240         id              BIGINT  PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq'),
 241         xpath           TEXT            NOT NULL,
 242         provider        INT     NOT NULL REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED
 243 ) INHERITS (acq.lineitem_attr_definition);
 244
 245 CREATE TABLE acq.lineitem_generated_attr_definition (
 246         id              BIGINT  PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq'),
 247         xpath           TEXT            NOT NULL
 248 ) INHERITS (acq.lineitem_attr_definition);
 249
 250 CREATE TABLE acq.lineitem_usr_attr_definition (
 251         id              BIGINT  PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq'),
 252         usr             INT     NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED
 253 ) INHERITS (acq.lineitem_attr_definition);
 254
 255 CREATE TABLE acq.lineitem_local_attr_definition (
 256         id              BIGINT  PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq')
 257 ) INHERITS (acq.lineitem_attr_definition);
 258
 259 CREATE TABLE acq.lineitem_attr (
 260         id              BIGSERIAL       PRIMARY KEY,
 261         definition      BIGINT          NOT NULL,
 262         lineitem        BIGINT          NOT NULL REFERENCES acq.lineitem (id) DEFERRABLE INITIALLY DEFERRED,
 263         attr_type       TEXT            NOT NULL,
 264         attr_name       TEXT            NOT NULL,
 265         attr_value      TEXT            NOT NULL
 266 );
 267
 268 CREATE INDEX li_attr_li_idx ON acq.lineitem_attr (lineitem);
 269 CREATE INDEX li_attr_value_idx ON acq.lineitem_attr (attr_value);
 270 CREATE INDEX li_attr_definition_idx ON acq.lineitem_attr (definition);
 271
 272
 273 -- Seed data
 274
 275
 276 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('title','Title of work','//*[@tag="245"]/*[contains("abcmnopr",@code)]');
 277 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)]');
 278 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('language','Lanuage of work','//*[@tag="240"]/*[@code="l"][1]');
 279 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('pagination','Pagination','//*[@tag="300"]/*[@code="a"][1]');
 280 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath, remove ) VALUES ('isbn','ISBN','//*[@tag="020"]/*[@code="a"]', $r$(?:-|\s.+$)$r$);
 281 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath, remove ) VALUES ('issn','ISSN','//*[@tag="022"]/*[@code="a"]', $r$(?:-|\s.+$)$r$);
 282 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('price','Price','//*[@tag="020" or @tag="022"]/*[@code="c"][1]');
 283 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('identifier','Identifier','//*[@tag="001"]');
 284 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('publisher','Publisher','//*[@tag="260"]/*[@code="b"][1]');
 285 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('pubdate','Publication Date','//*[@tag="260"]/*[@code="c"][1]');
 286 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('edition','Edition','//*[@tag="250"]/*[@code="a"][1]');
 287
 288 INSERT INTO acq.lineitem_local_attr_definition ( code, description ) VALUES ('estimated_price', 'Estimated Price');
 289
 290
 291 CREATE TABLE acq.distribution_formula (
 292         id              SERIAL PRIMARY KEY,
 293         owner   INT NOT NULL
 294                         REFERENCES actor.org_unit(id) DEFERRABLE INITIALLY DEFERRED,
 295         name    TEXT NOT NULL,
 296         skip_count      INT NOT NULL DEFAULT 0,
 297         CONSTRAINT acqdf_name_once_per_owner UNIQUE (name, owner)
 298 );
 299
 300 CREATE TABLE acq.distribution_formula_entry (
 301         id                      SERIAL PRIMARY KEY,
 302         formula         INTEGER NOT NULL REFERENCES acq.distribution_formula(id)
 303                                 ON DELETE CASCADE
 304                                 DEFERRABLE INITIALLY DEFERRED,
 305         position        INTEGER NOT NULL,
 306         item_count      INTEGER NOT NULL,
 307         owning_lib      INTEGER REFERENCES actor.org_unit(id)
 308                                 DEFERRABLE INITIALLY DEFERRED,
 309         location        INTEGER REFERENCES asset.copy_location(id),
 310         CONSTRAINT acqdfe_lib_once_per_formula UNIQUE( formula, position ),
 311         CONSTRAINT acqdfe_must_be_somewhere
 312                                 CHECK( owning_lib IS NOT NULL OR location IS NOT NULL )
 313 );
 314
 315 CREATE TABLE acq.fund_tag (
 316         id              SERIAL PRIMARY KEY,
 317         owner   INT NOT NULL
 318                         REFERENCES actor.org_unit(id) DEFERRABLE INITIALLY DEFERRED,
 319         name    TEXT NOT NULL,
 320         CONSTRAINT acqft_tag_once_per_owner UNIQUE (name, owner)
 321 );
 322
 323 CREATE TABLE acq.fund_tag_map (
 324         id                      SERIAL PRIMARY KEY,
 325         fund            INTEGER NOT NULL REFERENCES acq.fund(id)
 326                                 DEFERRABLE INITIALLY DEFERRED,
 327         tag         INTEGER REFERENCES acq.fund_tag(id)
 328                                 ON DELETE CASCADE
 329                                 DEFERRABLE INITIALLY DEFERRED,
 330         CONSTRAINT acqftm_fund_once_per_tag UNIQUE( fund, tag )
 331 );
 332
 333 -- Functions
 334
 335 CREATE TYPE acq.flat_lineitem_holding_subfield AS (lineitem int, holding int, subfield text, data text);
 336 CREATE OR REPLACE FUNCTION acq.extract_holding_attr_table (lineitem int, tag text) RETURNS SETOF acq.flat_lineitem_holding_subfield AS $$
 337 DECLARE
 338     counter INT;
 339     lida    acq.flat_lineitem_holding_subfield%ROWTYPE;
 340 BEGIN
 341
 342     SELECT  COUNT(*) INTO counter
 343       FROM  xpath_table(
 344                 'id',
 345                 'marc',
 346                 'acq.lineitem',
 347                 '//*[@tag="' || tag || '"]',
 348                 'id=' || lineitem
 349             ) as t(i int,c text);
 350
 351     FOR i IN 1 .. counter LOOP
 352         FOR lida IN
 353             SELECT  *
 354               FROM  (   SELECT  id,i,t,v
 355                           FROM  xpath_table(
 356                                     'id',
 357                                     'marc',
 358                                     'acq.lineitem',
 359                                     '//*[@tag="' || tag || '"][position()=' || i || ']/*/@code|' ||
 360                                         '//*[@tag="' || tag || '"][position()=' || i || ']/*[@code]',
 361                                     'id=' || lineitem
 362                                 ) as t(id int,t text,v text)
 363                     )x
 364         LOOP
 365             RETURN NEXT lida;
 366         END LOOP;
 367     END LOOP;
 368
 369     RETURN;
 370 END;
 371 $$ LANGUAGE PLPGSQL;
 372
 373 CREATE TYPE acq.flat_lineitem_detail AS (lineitem int, holding int, attr text, data text);
 374 CREATE OR REPLACE FUNCTION acq.extract_provider_holding_data ( lineitem_i int ) RETURNS SETOF acq.flat_lineitem_detail AS $$
 375 DECLARE
 376     prov_i  INT;
 377     tag_t   TEXT;
 378     lida    acq.flat_lineitem_detail%ROWTYPE;
 379 BEGIN
 380     SELECT provider INTO prov_i FROM acq.lineitem WHERE id = lineitem_i;
 381     IF NOT FOUND THEN RETURN; END IF;
 382
 383     SELECT holding_tag INTO tag_t FROM acq.provider WHERE id = prov_i;
 384     IF NOT FOUND OR tag_t IS NULL THEN RETURN; END IF;
 385
 386     FOR lida IN
 387         SELECT  lineitem_i,
 388                 h.holding,
 389                 a.name,
 390                 h.data
 391           FROM  acq.extract_holding_attr_table( lineitem_i, tag_t ) h
 392                 JOIN acq.provider_holding_subfield_map a USING (subfield)
 393           WHERE a.provider = prov_i
 394     LOOP
 395         RETURN NEXT lida;
 396     END LOOP;
 397
 398     RETURN;
 399 END;
 400 $$ LANGUAGE PLPGSQL;
 401
 402 -- select * from acq.extract_provider_holding_data(699);
 403
 404 CREATE OR REPLACE FUNCTION public.extract_acq_marc_field ( BIGINT, TEXT, TEXT) RETURNS TEXT AS $$
 405         SELECT public.extract_marc_field('acq.lineitem', $1, $2, $3);
 406 $$ LANGUAGE SQL;
 407
 408 /*
 409 CREATE OR REPLACE FUNCTION public.extract_bib_marc_field ( BIGINT, TEXT ) RETURNS TEXT AS $$
 410         SELECT public.extract_marc_field('biblio.record_entry', $1, $2);
 411 $$ LANGUAGE SQL;
 412
 413 CREATE OR REPLACE FUNCTION public.extract_authority_marc_field ( BIGINT, TEXT ) RETURNS TEXT AS $$
 414         SELECT public.extract_marc_field('authority.record_entry', $1, $2);
 415 $$ LANGUAGE SQL;
 416 */
 417 -- For example:
 418 -- INSERT INTO acq.lineitem_provider_attr_definition ( provider, code, description, xpath ) VALUES (1,'price','Price','//*[@tag="020" or @tag="022"]/*[@code="a"][1]');
 419
 420 /*
 421 Suggested vendor fields:
 422         vendor_price
 423         vendor_currency
 424         vendor_avail
 425         vendor_po
 426         vendor_identifier
 427 */
 428
 429 CREATE OR REPLACE FUNCTION public.ingest_acq_marc ( ) RETURNS TRIGGER AS $$
 430 DECLARE
 431         value           TEXT;
 432         atype           TEXT;
 433         prov            INT;
 434         adef            RECORD;
 435         xpath_string    TEXT;
 436 BEGIN
 437         FOR adef IN SELECT *,tableoid FROM acq.lineitem_attr_definition LOOP
 438
 439                 SELECT relname::TEXT INTO atype FROM pg_class WHERE oid = adef.tableoid;
 440
 441                 IF (atype NOT IN ('lineitem_usr_attr_definition','lineitem_local_attr_definition')) THEN
 442                         IF (atype = 'lineitem_provider_attr_definition') THEN
 443                                 SELECT provider INTO prov FROM acq.lineitem_provider_attr_definition WHERE id = adef.id;
 444                                 CONTINUE WHEN NEW.provider IS NULL OR prov <> NEW.provider;
 445                         END IF;
 446
 447                         IF (atype = 'lineitem_provider_attr_definition') THEN
 448                                 SELECT xpath INTO xpath_string FROM acq.lineitem_provider_attr_definition WHERE id = adef.id;
 449                         ELSIF (atype = 'lineitem_marc_attr_definition') THEN
 450                                 SELECT xpath INTO xpath_string FROM acq.lineitem_marc_attr_definition WHERE id = adef.id;
 451                         ELSIF (atype = 'lineitem_generated_attr_definition') THEN
 452                                 SELECT xpath INTO xpath_string FROM acq.lineitem_generated_attr_definition WHERE id = adef.id;
 453                         END IF;
 454
 455                         SELECT extract_acq_marc_field(id, xpath_string, adef.remove) INTO value FROM acq.lineitem WHERE id = NEW.id;
 456
 457                         IF (value IS NOT NULL AND value <> '') THEN
 458                                 INSERT INTO acq.lineitem_attr (lineitem, definition, attr_type, attr_name, attr_value)
 459                                         VALUES (NEW.id, adef.id, atype, adef.code, value);
 460                         END IF;
 461
 462                 END IF;
 463
 464         END LOOP;
 465
 466         RETURN NULL;
 467 END;
 468 $$ LANGUAGE PLPGSQL;
 469
 470 CREATE OR REPLACE FUNCTION public.cleanup_acq_marc ( ) RETURNS TRIGGER AS $$
 471 BEGIN
 472         IF TG_OP = 'UPDATE' THEN
 473                 DELETE FROM acq.lineitem_attr
 474                         WHERE lineitem = OLD.id AND attr_type IN ('lineitem_provider_attr_definition', 'lineitem_marc_attr_definition','lineitem_generated_attr_definition');
 475                 RETURN NEW;
 476         ELSE
 477                 DELETE FROM acq.lineitem_attr WHERE lineitem = OLD.id;
 478                 RETURN OLD;
 479         END IF;
 480 END;
 481 $$ LANGUAGE PLPGSQL;
 482
 483 CREATE TRIGGER cleanup_lineitem_trigger
 484         BEFORE UPDATE OR DELETE ON acq.lineitem
 485         FOR EACH ROW EXECUTE PROCEDURE public.cleanup_acq_marc();
 486
 487 CREATE TRIGGER ingest_lineitem_trigger
 488         AFTER INSERT OR UPDATE ON acq.lineitem
 489         FOR EACH ROW EXECUTE PROCEDURE public.ingest_acq_marc();
 490
 491 CREATE OR REPLACE FUNCTION acq.exchange_ratio ( from_ex TEXT, to_ex TEXT ) RETURNS NUMERIC AS $$
 492 DECLARE
 493     rat NUMERIC;
 494 BEGIN
 495     IF from_ex = to_ex THEN
 496         RETURN 1.0;
 497     END IF;
 498
 499     SELECT ratio INTO rat FROM acq.exchange_rate WHERE from_currency = from_ex AND to_currency = to_ex;
 500
 501     IF FOUND THEN
 502         RETURN rat;
 503     ELSE
 504         SELECT ratio INTO rat FROM acq.exchange_rate WHERE from_currency = to_ex AND to_currency = from_ex;
 505         IF FOUND THEN
 506             RETURN 1.0/rat;
 507         END IF;
 508     END IF;
 509
 510     RETURN NULL;
 511
 512 END;
 513 $$ LANGUAGE PLPGSQL;
 514
 515 CREATE OR REPLACE FUNCTION acq.exchange_ratio ( TEXT, TEXT, NUMERIC ) RETURNS NUMERIC AS $$
 516     SELECT $3 * acq.exchange_ratio($1, $2);
 517 $$ LANGUAGE SQL;
 518
 519 CREATE OR REPLACE VIEW acq.funding_source_credit_total AS
 520     SELECT  funding_source,
 521             SUM(amount) AS amount
 522       FROM  acq.funding_source_credit
 523       GROUP BY 1;
 524
 525 CREATE OR REPLACE VIEW acq.funding_source_allocation_total AS
 526     SELECT  funding_source,
 527             SUM(amount)::NUMERIC(100,2) AS amount
 528       FROM (
 529             SELECT  funding_source,
 530                     SUM(a.amount)::NUMERIC(100,2) AS amount
 531               FROM  acq.fund_allocation a
 532               WHERE a.percent IS NULL
 533               GROUP BY 1
 534                             UNION ALL
 535             SELECT  funding_source,
 536                     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
 537               FROM  acq.fund_allocation a
 538               WHERE a.amount IS NULL
 539               GROUP BY 1
 540         ) x
 541       GROUP BY 1;
 542
 543 CREATE OR REPLACE VIEW acq.funding_source_balance AS
 544     SELECT  COALESCE(c.funding_source, a.funding_source) AS funding_source,
 545             SUM(COALESCE(c.amount,0.0) - COALESCE(a.amount,0.0))::NUMERIC(100,2) AS amount
 546       FROM  acq.funding_source_credit_total c
 547             FULL JOIN acq.funding_source_allocation_total a USING (funding_source)
 548       GROUP BY 1;
 549
 550 CREATE OR REPLACE VIEW acq.fund_allocation_total AS
 551     SELECT  fund,
 552             SUM(amount)::NUMERIC(100,2) AS amount
 553       FROM (
 554             SELECT  fund,
 555                     SUM(a.amount * acq.exchange_ratio(s.currency_type, f.currency_type))::NUMERIC(100,2) AS amount
 556               FROM  acq.fund_allocation a
 557                     JOIN acq.fund f ON (a.fund = f.id)
 558                     JOIN acq.funding_source s ON (a.funding_source = s.id)
 559               WHERE a.percent IS NULL
 560               GROUP BY 1
 561                             UNION ALL
 562             SELECT  fund,
 563                     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
 564               FROM  acq.fund_allocation a
 565                     JOIN acq.fund f ON (a.fund = f.id)
 566                     JOIN acq.funding_source s ON (a.funding_source = s.id)
 567               WHERE a.amount IS NULL
 568               GROUP BY 1
 569         ) x
 570       GROUP BY 1;
 571
 572 CREATE OR REPLACE VIEW acq.fund_debit_total AS
 573     SELECT  id AS fund,
 574             encumbrance,
 575             SUM(amount) AS amount
 576       FROM  acq.fund_debit
 577       GROUP BY 1,2;
 578
 579 CREATE OR REPLACE VIEW acq.fund_encumbrance_total AS
 580     SELECT  fund,
 581             SUM(amount) AS amount
 582       FROM  acq.fund_debit_total
 583       WHERE encumbrance IS TRUE
 584       GROUP BY 1;
 585
 586 CREATE OR REPLACE VIEW acq.fund_spent_total AS
 587     SELECT  fund,
 588             SUM(amount) AS amount
 589       FROM  acq.fund_debit_total
 590       WHERE encumbrance IS FALSE
 591       GROUP BY 1;
 592
 593 CREATE OR REPLACE VIEW acq.fund_combined_balance AS
 594     SELECT  c.fund,
 595             c.amount - COALESCE(d.amount,0.0) AS amount
 596       FROM  acq.fund_allocation_total c
 597             LEFT JOIN acq.fund_debit_total d USING (fund);
 598
 599 CREATE OR REPLACE VIEW acq.fund_spent_balance AS
 600     SELECT  c.fund,
 601             c.amount - COALESCE(d.amount,0.0) AS amount
 602       FROM  acq.fund_allocation_total c
 603             LEFT JOIN acq.fund_spent_total d USING (fund);
 604
 605 COMMIT;
 606
 607
 608
 609