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

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