[OPEN-ILS-DEV] Functions for updating the call number label or owning_lib associated with a copy

[Ben Ostrowsky]

On Wed, Apr 13, 2011 at 7:46 PM, Thomas Berezansky <tsbere at mvlc.org> wrote:

> Hopefully without the HTML wrapping? ;)
>

Oh, for heaven's sake... yes. The Google netbook is pretty awesome, but
leads to some wacky workarounds.


-- Two functions for updating copy information (call number label and
owning_lib)
-- that actually live in asset.call_number.

CREATE OR REPLACE FUNCTION asset.change_call_number( copy_id BIGINT,
new_label TEXT ) RETURNS VOID AS $$

DECLARE
  old_volume   BIGINT;
  new_volume   BIGINT;
  bib          BIGINT;
  owner        INTEGER;
  old_label    TEXT;
  remainder    BIGINT;

BEGIN

  -- Bail out if asked to change the label to ##URI##
  IF new_label = '##URI##' THEN
    RETURN;
  END IF;

  -- Gather information
  SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
  SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
  SELECT owning_lib INTO owner FROM asset.call_number WHERE id = old_volume;

  -- Bail out if the label already is ##URI##
  SELECT label INTO old_label FROM asset.call_number WHERE id = old_volume;
  IF old_label = '##URI##' THEN
    RETURN;
  END IF;

  -- Bail out if the call number label is already correct
  IF new_volume = old_volume THEN
    RETURN;
  END IF;

  -- Check whether we already have a destination volume available
  SELECT id INTO new_volume FROM asset.call_number
    WHERE
      record = bib AND
      owning_lib = owner AND
      label = new_label AND
      NOT deleted;

  -- Create destination volume if needed
  IF NOT FOUND THEN
    INSERT INTO asset.call_number (creator, editor, record, owning_lib,
label)
      VALUES (1, 1, bib, owner, new_label);
    SELECT id INTO new_volume FROM asset.call_number
      WHERE
        record = bib AND
        owning_lib = owner AND
        label = new_label AND
        NOT deleted;
  END IF;

  -- Move copy to destination
  UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;

  -- Delete source volume if it is now empty
  SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume
AND NOT deleted;
  IF NOT FOUND THEN
    DELETE FROM asset.call_number WHERE id = old_volume;
  END IF;

END;

$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION asset.change_owning_lib( copy_id BIGINT,
new_owner_input TEXT ) RETURNS VOID AS $$

DECLARE
  old_volume   BIGINT;
  new_volume   BIGINT;
  bib          BIGINT;
  cnlabel        TEXT;
  old_owner   INTEGER;
  new_owner   INTEGER;
  remainder    BIGINT;

BEGIN

  -- Convert new_owner_input from shortname to ou.id if needed
  SELECT id INTO new_owner FROM actor.org_unit WHERE shortname =
new_owner_input;
  IF NOT FOUND THEN
    SELECT id INTO new_owner FROM actor.org_unit WHERE id =
new_owner_input::INTEGER;
  END IF;

  -- and if it's not even a valid org unit ID, bail out.
  IF NOT FOUND THEN
    RETURN;
  END IF;

  -- Gather information
  SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
  SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
  SELECT owning_lib, label INTO old_owner, cnlabel FROM asset.call_number
acn WHERE id = old_volume;

  -- Bail out if the label is ##URI##; that should be done by changing 856$9
  IF cnlabel = '##URI##' THEN
    RETURN;
  END IF;

  -- Bail out if the owning library is already correct
  IF new_owner = old_owner THEN
    RETURN;
  END IF;

  -- Check whether we already have a destination volume available
  SELECT id INTO new_volume FROM asset.call_number
    WHERE
      record = bib AND
      owning_lib = new_owner AND
      label = cnlabel AND
      NOT deleted;

  -- Create destination volume if needed
  IF NOT FOUND THEN
    INSERT INTO asset.call_number (creator, editor, record, owning_lib,
label)
      VALUES (1, 1, bib, new_owner, cnlabel);
    SELECT id INTO new_volume FROM asset.call_number
      WHERE
        record = bib AND
        owning_lib = new_owner AND
        label = cnlabel AND
        NOT deleted;
  END IF;

  -- Move copy to destination
  UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;

  -- Delete source volume if it is now empty
  SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume
AND NOT deleted;
  IF NOT FOUND THEN
    DELETE FROM asset.call_number WHERE id = old_volume;
  END IF;

END;

$$ LANGUAGE plpgsql;



> Also, may want to make some docs on how they are intended to be called, and
> reasons why, as I assume these are for manual DB calls and may never have a
> GUI method of calling them.
>

They are indeed intended for manual DB calls, or for abstracting this
process within other internal code. Comes in handy if you want to add (or
remove) "MYS " at the beginning of the call numbers of all copies in a
location called 'Mysteries', for example.

Ben
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://libmail.georgialibraries.org/pipermail/open-ils-dev/attachments/20110414/70f566c3/attachment-0001.htm