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