From 70da519fb815265cc806283c660cc3ae0d1e1580 Mon Sep 17 00:00:00 2001 From: Doug Kyle Date: Tue, 10 Jun 2014 16:34:49 -0400 Subject: [PATCH] Count in transit duplicates, metrics, testing In transit dup counts, Optional and better metrics, rough cgi test script Signed-off-by: Doug Kyle --- Open-ILS/src/sql/Pg/002.functions.config.sql | 4 ++ Open-ILS/src/sql/Pg/smart_float.sql | 68 ++++++++++++++++++---- .../Pg/version-upgrade/2.5.2-2.5.3-upgrade-db.sql | 4 ++ .../test-scripts/smart_float_test.cgi | 36 ++++++++++++ 4 files changed, 101 insertions(+), 11 deletions(-) create mode 100755 Open-ILS/src/support-scripts/test-scripts/smart_float_test.cgi diff --git a/Open-ILS/src/sql/Pg/002.functions.config.sql b/Open-ILS/src/sql/Pg/002.functions.config.sql index 3dab7d9dea..2c5ab18e76 100644 --- a/Open-ILS/src/sql/Pg/002.functions.config.sql +++ b/Open-ILS/src/sql/Pg/002.functions.config.sql @@ -353,6 +353,10 @@ if ($schema eq 'biblio') { $new_901->add_subfields("d" => $_TD->{new}{share_depth}); } + if ($_TD->{new}{source}) { + $new_901->add_subfields("s" => $_TD->{new}{source}); + } + $marc->append_fields($new_901); } elsif ($schema eq 'authority') { my $new_901 = MARC::Field->new("901", " ", " ", diff --git a/Open-ILS/src/sql/Pg/smart_float.sql b/Open-ILS/src/sql/Pg/smart_float.sql index e64dc881db..eedb492814 100644 --- a/Open-ILS/src/sql/Pg/smart_float.sql +++ b/Open-ILS/src/sql/Pg/smart_float.sql @@ -13,6 +13,7 @@ shelf_is_group BOOL NOT NULL DEFAULT FALSE, items_allowed INT, homing_threshold INT NOT NULL DEFAULT 0, homing_lifespan TEXT DEFAULT '1 day', +log_metrics BOOL NOT NULL DEFAULT FALSE, CONSTRAINT org_unit_fkey FOREIGN KEY (org_unit) REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED, CONSTRAINT org_plus_loc UNIQUE(org_unit,shelf_location) ); @@ -26,20 +27,29 @@ BEGIN RETURN QUERY with sflibs as ( select org_unit from config.smart_float where active is true group by 1 ), +dupcops as (select circ_lib,id from asset.copy where call_number in (select id from asset.call_number where record in (select record from asset.call_number join asset.copy on call_number.id=copy.call_number and copy.barcode=bcode)) +), +holdtransits as ( +select target_copy from action.hold_transit_copy where dest_recv_time is null +), duplibs as ( -select circ_lib,count(circ_lib) from asset.copy where barcode != bcode and status in (0,7) and call_number in (select id from asset.call_number where record in (select record from asset.call_number join asset.copy on call_number.id=copy.call_number and copy.barcode=bcode and copy.deleted is false)) group by 1 +select circ_lib,count(circ_lib) from asset.copy where barcode != bcode and status in (0,6,7) and deleted is false and id in (select id from dupcops) and id not in (select target_copy from holdtransits) group by 1 ) -select sflibs.org_unit,coalesce(duplibs.count,0) as count from sflibs left join duplibs on sflibs.org_unit=duplibs.circ_lib; +select sflibs.org_unit,coalesce(duplibs.count,0) as count from sflibs left join duplibs on sflibs.org_unit=duplibs.circ_lib order by 1; END; $function$; -- !!! smart_float.opens_and_dups !!! -CREATE OR REPLACE FUNCTION smart_float.opens_and_dups(bcode text, cloc integer) - RETURNS TABLE(clib integer, taken bigint, allowed integer, open bigint, dups bigint, dups_threshold integer) +CREATE OR REPLACE FUNCTION smart_float.opens_and_dups(bcode text) + RETURNS TABLE(clib integer, taken bigint, allowed integer, open bigint, dups_threshold integer, dups bigint) LANGUAGE plpgsql AS $function$ +DECLARE + cloc INT; + BEGIN - RETURN QUERY select copy.circ_lib,count(copy.id),sf.items_allowed,(sf.items_allowed-count(copy.id)) as spaces,(select numdups from smart_float.dups(bcode) where smart_float_dups.clib=copy.circ_lib) as dups, sf.dups_threshold from asset.copy join config.smart_float sf on (copy.circ_lib=sf.org_unit and copy.location=sf.shelf_location and sf.active is true) where copy.location=cloc and copy.status in (0,7) group by circ_lib,sf.items_allowed,sf.dups_threshold; + SELECT INTO cloc location from asset.copy where barcode=bcode; + RETURN QUERY select openings.*,dups.numdups from smart_float.dups(bcode), smart_float.openings(cloc) where dups.clib=openings.clib; END; $function$; @@ -113,7 +123,7 @@ DECLARE send_lib INT; homing_threshold_met BOOL; circs_needed INT; - days_to_go INT; + days_to_go INT; open_space INT; duplicates INT; opcode TEXT; @@ -124,8 +134,10 @@ BEGIN SELECT INTO shelf_loc_atts * FROM config.smart_float WHERE org_unit = chkin_org and shelf_location = copy_rec.location and active is true; IF NOT FOUND THEN -- no smart_float config for this org and shelf, so dumb float it here - PERFORM smart_float.save_metric(copy_rec.id,copy_rec.circ_lib,origin_owner,chkin_org,NULL,NULL,chkin_org,'not_active',NULL,NULL,NULL); - RETURN chkin_org; + IF shelf_loc_atts.log_metrics IS TRUE THEN + PERFORM smart_float.save_metric(copy_rec.id,copy_rec.circ_lib,origin_owner,chkin_org,NULL,NULL,chkin_org,'not_active',NULL,NULL,NULL); + END IF; + RETURN chkin_org; END IF; SELECT INTO origin_loc_atts * FROM config.smart_float WHERE org_unit = origin_owner and shelf_location = copy_rec.location; @@ -133,10 +145,19 @@ BEGIN IF homing_threshold_met IS FALSE THEN -- send home, homing threshold not met - PERFORM smart_float.save_metric(copy_rec.id,copy_rec.circ_lib,origin_owner,chkin_org,NULL,NULL,NULL,'homed',origin_loc_atts.*,circs_needed,days_to_go); + IF shelf_loc_atts.log_metrics IS TRUE THEN + PERFORM smart_float.save_metric(copy_rec.id,copy_rec.circ_lib,origin_owner,chkin_org,NULL,NULL,NULL,'homed',origin_loc_atts.*,circs_needed,days_to_go); + END IF; RETURN origin_owner; END IF; + IF shelf_loc_atts.log_metrics IS TRUE THEN + -- store checkin lib data + SELECT INTO send_lib,open_space,duplicates o.clib,o.open,d.numdups from smart_float.openings(copy_rec.location) as o join smart_float.dups(copy_rec.barcode) as d on d.clib=o.clib where o.clib=chkin_org; + PERFORM smart_float.save_metric(copy_rec.id,copy_rec.circ_lib,origin_owner,chkin_org,open_space,duplicates,send_lib,'checkin',shelf_loc_atts.*,NULL,NULL); + END IF; + + -- can item float to checkin lib? aka the shelving location is not full and has less than dups_threshold duplicate titles? opcode := 'floated'; SELECT INTO send_lib,open_space,duplicates o.clib,o.open,d.numdups from smart_float.openings(copy_rec.location) as o join smart_float.dups(copy_rec.barcode) as d on d.clib=o.clib where o.clib=chkin_org and o.open>0 and (d.numdupsadd_subfields("d" => $_TD->{new}{share_depth}); } + if ($_TD->{new}{source}) { + $new_901->add_subfields("s" => $_TD->{new}{source}); + } + $marc->append_fields($new_901); } elsif ($schema eq 'authority') { my $new_901 = MARC::Field->new("901", " ", " ", diff --git a/Open-ILS/src/support-scripts/test-scripts/smart_float_test.cgi b/Open-ILS/src/support-scripts/test-scripts/smart_float_test.cgi new file mode 100755 index 0000000000..024ce5cc43 --- /dev/null +++ b/Open-ILS/src/support-scripts/test-scripts/smart_float_test.cgi @@ -0,0 +1,36 @@ +#!/usr/bin/perl -w + +use DBI; +use CGI qw(:standard); +print header; + +our %config; +do '/openils/conf/live-db-setup.pl'; + +my $bcode = param('bcode'); + +unless ($bcode) { + print "
Enter Item Barcode:

"; + exit; +} + +my $dbh = DBI->connect($config{dsn},$config{usr},$config{pw}); +my $cid = $dbh->prepare("select id,location from asset.copy where barcode=?"); +$cid->execute($bcode); +my $cr = $cid->fetch; +my $cp = $cr->[0]; +my $cl = $cr->[1]; + +my $odresult = `/usr/local/pgsql/bin/psql -U postgres -c "select * from smart_float.opens_and_dups('$bcode')" -H evergreen`; +print "Item $bcode Location $cl
$odresult"; + +foreach $cklib (11..17) { #alter loop for your orgs +print "Checkin at $cklib
"; +$dresult = `/usr/local/pgsql/bin/psql -U postgres -c "select * from smart_float.destination($cklib,$cp)" -H evergreen`; +print $dresult; +print "Metrics
"; +$mresult = `/usr/local/pgsql/bin/psql -U postgres -c "select * from smart_float.metrics where copy_id = $cp order by id desc limit 2" -H evergreen`; +print $mresult; +} + +exit; -- 2.11.0