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