From ce3ddc38d689782df2d3c9dae098e4490eb24765 Mon Sep 17 00:00:00 2001 From: Dan Scott Date: Thu, 15 Oct 2015 16:15:44 -0400 Subject: [PATCH] Repopulate bookbags based on ACQ funds Now that we're using acquisitions, commit ourselves to an approach that populates our existing bookbags using the fund codes. Signed-off-by: Dan Scott --- .../daily-scripts/repopulate_recently_ordered.sql | 34 ++++++++++++++++++++++ 1 file changed, 34 insertions(+) create mode 100644 tools/daily-scripts/repopulate_recently_ordered.sql diff --git a/tools/daily-scripts/repopulate_recently_ordered.sql b/tools/daily-scripts/repopulate_recently_ordered.sql new file mode 100644 index 0000000000..e000138b7d --- /dev/null +++ b/tools/daily-scripts/repopulate_recently_ordered.sql @@ -0,0 +1,34 @@ +-- The astute will notice that this depends on a conifer.osul_funds table +-- that looks like the following: +-- +-- Table "conifer.osul_funds" +-- Column | Type | Modifiers +-- ---------+---------+----------- +-- fund | text | +-- name | text | +-- bookbag | integer | +-- +CREATE OR REPLACE FUNCTION conifer.repopulate_bookbags(bookbag INT, fund TEXT, bb_limit INT) RETURNS void AS $$ + DELETE FROM container.biblio_record_entry_bucket_item + WHERE bucket = $1; + INSERT INTO container.biblio_record_entry_bucket_item (bucket, target_biblio_record_entry) + WITH x AS ( + SELECT $1 AS bucket, acn.record + FROM acq.fund af + INNER JOIN acq.lineitem_detail alid ON af.id = alid.fund + INNER JOIN acq.lineitem ali ON ali.id = alid.lineitem + INNER JOIN asset.copy acp ON acp.id = alid.eg_copy_id + INNER JOIN asset.call_number acn ON acp.call_number = acn.id + WHERE alid.cancel_reason IS NULL + AND ali.state IN ('on-order', 'received') + AND acp.circ_modifier ~ 'BOOK' + AND acp.deleted IS FALSE + AND acn.deleted IS FALSE + AND acp.opac_visible IS TRUE + AND code ~ $2 + AND ali.create_time > '2015-05-01'::date + ORDER BY ali.create_time DESC + LIMIT $3 + ) SELECT DISTINCT bucket, record FROM x; +$$ LANGUAGE SQL; +SELECT conifer.repopulate_bookbags(bookbag, fund, 40) FROM conifer.osul_funds; -- 2.11.0