From 385fcf968e39e667ad154f2786c8e47b2154121e Mon Sep 17 00:00:00 2001 From: miker Date: Mon, 31 Jul 2006 04:26:47 +0000 Subject: [PATCH] importing circs via direct SQL is done ... fixing renewal counts will come later git-svn-id: svn://svn.open-ils.org/ILS/trunk@5177 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Evergreen/src/extras/import/legacy_circ.sql | 128 +++++++++++++++++--- 1 file changed, 112 insertions(+), 16 deletions(-) diff --git a/Evergreen/src/extras/import/legacy_circ.sql b/Evergreen/src/extras/import/legacy_circ.sql index d38e6874f8..b6e08575d5 100644 --- a/Evergreen/src/extras/import/legacy_circ.sql +++ b/Evergreen/src/extras/import/legacy_circ.sql @@ -1,33 +1,130 @@ BEGIN; +CREATE TABLE legacy_lib_max_fine (lib text, max_fine numeric(6,2)); +COPY legacy_lib_max_fine (lib, max_fine) FROM STDIN; +CHRLS 10.00 +FRRLS 10.00 +HCLS 10.00 +OCRL 10.00 +OHOOP 10.00 +OKRL 10.00 +PPL 10.00 +PMRLS 10.00 +STRL 10.00 +DTRL 100.00 +SJRL 100.00 +ARL 100.00 +ECGRL 100.00 +\. + +CREATE TABLE legacy_type_circ_map (lib text, max_fine numeric(6,2), renewals int); +COPY legacy_type_circ_map (item_type, recuring_fine, renewals) FROM STDIN; +ART 0.10 0 +ATLAS 0.50 2 +AUDIOBOOK 0.50 2 +AV 0.50 2 +BESTSELLER 0.50 2 +BOOK 0.10 2 +CD 0.10 2 +COMPUTER 0.10 2 +DATALOAD 0.10 2 +DEPOSIT 0.10 2 +DVD 0.50 0 +DVD-LONG 0.10 2 +E-AUDIO 0.50 1 +E-BOOK 0.50 1 +EQUIP-LONG 0.50 0 +EQUIPMENT 0.50 1 +FACBESTSLR 0.10 2 +FACNEWBK 0.10 2 +FILMSTRIP 0.10 2 +ILL-ITEM 0.50 0 +INTERNET 0.10 1 +KIT 0.10 2 +LASERDISC 0.50 0 +LIBRARYUSE 0.10 2 +MAG-CIRC 0.10 2 +MAG-NOCIRC 0.10 2 +MAP 0.50 1 +MICROFORM 0.10 2 +MUSIC 0.10 2 +NEW-AV 0.50 1 +NEW-BOOK 0.10 2 +NEWSPAPER 0.10 2 +NILS-ITEM 0.10 2 +OUTREACH 0.10 2 +PAMPHLET 0.10 2 +PAPERBACK 0.10 2 +REALIA 0.10 2 +RECORD 0.10 2 +REFERENCE 0.00 0 +RESERVE 0.00 0 +ROOM 0.00 0 +ROOMSATELL 0.00 0 +SOFTWARE 0.10 2 +SOFTWRLONG 0.10 2 +STATE-BOOK 0.10 1 +STATE-MFRM 0.10 2 +TALKINGBK 0.00 0 +TOY 0.10 2 +UNKNOWN 0.10 2 +VIDEO 0.10 0 +VIDEO-LONG 0.10 2 +VIDEO-SPEC 0.00 2 +WEBSOURCE 0.00 2 +\. + -- First, we extract the real circs for users that we know about -CREATE TABLE legacy_real_circ (usr int, item int, start_date date, due_date date, item_type text, circ_lib int, claim_return_date date) AS +-- CREATE TABLE legacy_real_circ AS +INSERT INTO action.circulation + ( + usr, + xact_start, + target_copy, + circ_lib, + circ_staff, + renewal_remaining, + due_date, + stop_fines_time, + duration, + recuring_fine, + max_fine, + desk_renewal, + duration_rule, + recuring_fine_rule, + max_fine_rule, + stop_fines + ) SELECT DISTINCT ON (lc.charge_key1, lc.charge_key2, lc.charge_key3) au.id AS usr, - cp.id AS item, - CASE WHEN lc.renewal_date = 'NEVER' - THEN lc.charge_date::DATE - ELSE lc.renewal_date::DATE - END AS start_date, - CASE WHEN lc.due_date = 'NEVER' - THEN (now() + '20 years')::DATE - ELSE lc.due_date::DATE - END AS due_date, - li.item_type, + CASE WHEN lc.renewal_date = 'NEVER' THEN lc.charge_date::DATE ELSE lc.renewal_date::DATE END AS xact_start, + cp.id AS target_copy, ou.id AS circ_lib, - CASE WHEN lc.claim_return_date = '0' - THEN NULL - ELSE lc.claim_return_date::DATE - END AS claim_return_date + 1 AS circ_staff, + tm.renewals AS renewal_remaining, + CASE WHEN lc.due_date = 'NEVER' THEN (now() + '20 years')::DATE ELSE lc.due_date::DATE END AS due_date, + CASE WHEN lc.claim_return_date = '0' THEN NULL ELSE lc.claim_return_date::DATE END AS stop_fines_time, + ((CASE WHEN lc.due_date = 'NEVER' THEN (now() + '20 years')::DATE ELSE lc.due_date::DATE END + - + CASE WHEN lc.renewal_date = 'NEVER' THEN lc.charge_date::DATE ELSE lc.renewal_date::DATE END)||' days')::interval AS duration, + tm.recuring_fine AS recuring_fine, + COALESCE( mf.max_fine, 5.00 ) AS max_fine, + CASE WHEN lc.renewal_date = 'NEVER' THEN FALSE ELSE TRUE END AS desk_renewal, + 'IMPORT'::TEXT AS duration_rule, + 'IMPORT'::TEXT AS recuring_fine_rule, + 'IMPORT'::TEXT AS max_fine_rule, + CASE WHEN lc.claim_return_date = '0' THEN NULL ELSE 'CLAIMSRETURNED' END AS stop_fines FROM legacy_charge lc JOIN joined_legacy li ON ( lc.charge_key1 = li.cat_key AND lc.charge_key2 = li.call_key AND lc.charge_key3 = li.item_key ) JOIN asset.copy cp ON (cp.barcode = li.item_id) + JOIN legacy_type_circ_map tm ON (cp.circ_modifier = tm.item_type) JOIN actor.org_unit ou ON (lc.library = ou.shortname) JOIN actor.usr au ON (lc.user_key = au.id) LEFT JOIN legacy_baduser_map bu ON (bu.id = lc.user_key) + LEFT JOIN legacy_lib_max_fine mf ON (ou.shortname LIKE mf.lib||'%') WHERE bu.id IS NULL ORDER BY lc.charge_key1, @@ -92,4 +189,3 @@ UPDATE asset.copy WHERE asset.copy.id = legacy_lib_change_circ.item; COMMIT; - -- 2.43.2