Dispersal Postgres Function

/*
drop table rogan.dispersed_copies;
drop table rogan.dispersals;
*/

CREATE TABLE rogan.dispersals (
        id                              SERIAL                                          PRIMARY KEY,
        circ_from                INT                   NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
        circ_to                  INT                   NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
        acl                      INT                NOT NULL REFERENCES asset.copy_location (id) DEFERRABLE INITIALLY DEFERRED,
        dispersal_date  TIMESTAMP WITH TIME ZONE       NOT NULL,
        num_of_copies   INT                                                     NOT NULL,
        transferred             BOOLEAN                                         NOT NULL DEFAULT FALSE,
        creator                 INT                                                     NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED
);

-- the transferred is a placeholder for when I add automation later to interact with buckets and have Evergreen change the
-- locations for staff

CREATE TABLE rogan.dispersed_copies (
        id                              SERIAL                                          PRIMARY KEY,
        copy_id                 INT                                                     NOT NULL REFERENCES asset.COPY (id) DEFERRABLE INITIALLY DEFERRED,
        dispersal               INT4                                            NOT NULL REFERENCES     rogan.dispersals (id) DEFERRABLE INITIALLY DEFERRED
);


CREATE TABLE rogan.org_unit_setting_type (
    NAME            TEXT    PRIMARY KEY,
    label           TEXT    UNIQUE NOT NULL,
    grp             TEXT    REFERENCES config.settings_group (NAME),
    description     TEXT,
    datatype        TEXT    NOT NULL DEFAULT 'string',
    fm_class        TEXT,
    view_perm       INT,
    update_perm     INT,
    CONSTRAINT coust_valid_datatype CHECK ( datatype IN
    ( 'bool', 'integer', 'float', 'currency', 'interval',
      'date', 'string', 'object', 'array', 'link' ) ),
    CONSTRAINT coust_no_empty_link CHECK
    ( ( datatype =  'link' AND fm_class IS NOT NULL ) OR
      ( datatype <> 'link' AND fm_class IS NULL ) )
);


CREATE TABLE rogan.org_unit_setting (
        id              BIGSERIAL       PRIMARY KEY,
        org_unit        INT             NOT NULL REFERENCES actor.org_unit ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
        NAME            TEXT                    NOT NULL REFERENCES rogan.org_unit_setting_type DEFERRABLE INITIALLY DEFERRED,
        VALUE           TEXT            NOT NULL,
        CONSTRAINT ou_once_per_key UNIQUE (org_unit,NAME)
        );

/*  seed values

insert into rogan.org_unit_setting_type (name,label,description,datatype,grp)
values ('disperal_max','Dispersals before weeding.','Maximum number of items an item will disperse before being eligible for weeding.','integer','lib');

insert into rogan.org_unit_setting_type (name,label,description,datatype,grp)
values ('weeding_age','Age of items before they weed.','Age an item must be older than and not have circs during to be elibible for weeding.','interval','lib');

insert into rogan.org_unit_setting_type (name,label,description,datatype,grp)
values ('dispersal_age','Age before item is dispersed.','Item must be older than this and have at least this much time without circs to be dispersed.','interval','lib');

insert into rogan.org_unit_setting (org_unit,name,value)
values (119,'dispersal_max',3);

insert into rogan.org_unit_setting (org_unit,name,value)
values (119,'weeding_age','3 years');

insert into rogan.org_unit_setting (org_unit,name,value)
values (119,'dispersal_age','6 months');

insert into rogan.org_unit_setting (org_unit,name,value)
values (1,'dispersal_age','1 year');

insert into rogan.org_unit_setting (org_unit,name,value)
values (1,'weeding_age','5 years');

insert into rogan.org_unit_setting (org_unit,name,value)
values (1,'dispersal_max',4);

*/


/* testing time!

RH Adult Large Print 1008
YK Adult Large Print 1046
My account 1599187

*/


-- -------------------------------------------------------------------------------------------------
-- -------------------------------------------------------------------------------------------------
-- -------------------------------------------------------------------------------------------------

-- -------------------------------------------------------------------------------------------------
-- eventually I'd like to handle exceptions through an interface, gui everything and
-- do something to help automate some steps through buckets or bucket like behavior
-- -------------------------------------------------------------------------------------------------

/*

2015-02-04 Correcting it for new paradign of county owned volumes and shelving locations

instead of acl to acl we need to move items at a certain shelving location from circ lib to circ lib
tables are changed but code hasn't been touched yet below
*/

DROP FUNCTION rogan.disperse_copies(int4,int4,INT,int4);


CREATE OR REPLACE FUNCTION rogan.disperse_copies(
        from_acl                        INT4,
        to_acl                          INT4,
        copies_to_disperse      INT,
        dispersing_staff        INT4
)
RETURNS INTEGER AS $$

DECLARE
                disperse_id             INT4;
                org_dispersal_age       INTERVAL;
                creator_org             INT4;
BEGIN

SELECT INTO creator_org au.home_ou FROM actor.usr au WHERE au.id = dispersing_staff;

-- right now the seed values have a consortium default but I should include in here something to handle a null
SELECT INTO org_dispersal_age rous.VALUE
FROM rogan.org_unit_setting rous
JOIN actor.org_unit_ancestors_distance(creator_org) d ON (d.id = rous.org_unit)
WHERE NAME = 'dispersal_age'
ORDER BY d.distance
LIMIT 1
;

INSERT INTO rogan.dispersals (acl_from,acl_to,dispersal_date,num_of_copies,creator)
VALUES (from_acl,to_acl,NOW(),copies_to_disperse,dispersing_staff) RETURNING id INTO disperse_id;

CREATE TEMP TABLE dest_bibs ON COMMIT DROP AS
        SELECT distinct(bre.id) bre_id FROM
        asset.COPY ac
        JOIN asset.call_number acn ON acn.id = ac.call_number
        JOIN biblio.record_entry bre ON bre.id = acn.record
        WHERE ac.deleted = FALSE AND ac.LOCATION = to_acl
        ;

CREATE TEMP TABLE disp_copies ON COMMIT DROP AS
        SELECT ac.id AS ids, MAX(acirc.xact_start) AS circs
    FROM asset.COPY ac
    JOIN asset.copy_location acl ON ac.LOCATION = acl.id
    LEFT JOIN ACTION.circulation acirc ON acirc.target_copy = ac.id
    JOIN asset.call_number acn ON acn.id = ac.call_number
    JOIN reporter.materialized_simple_record msr ON msr.id = acn.record
    WHERE acl.id = from_acl
    AND acirc.xact_start IS NULL
    AND date(ac.create_date) <= NOW() - org_dispersal_age
    AND ac.deleted = FALSE
    AND ac.status = 0
    AND msr.id NOT IN (SELECT bre_id FROM dest_bibs)
    GROUP BY 1
        UNION ALL
    SELECT ac.id, MAX(acirc.xact_start) AS circs
    FROM asset.COPY ac
    JOIN asset.copy_location acl ON ac.LOCATION = acl.id
    LEFT JOIN ACTION.circulation acirc ON acirc.target_copy = ac.id
    JOIN asset.call_number acn ON acn.id = ac.call_number
    JOIN reporter.materialized_simple_record msr ON msr.id = acn.record
    WHERE acl.id = from_acl
    AND date(ac.create_date) <= NOW() - org_dispersal_age
    AND ac.deleted = FALSE
    AND ac.status = 0
    AND msr.id NOT IN (SELECT bre_id FROM dest_bibs)
    GROUP BY 1
    HAVING MAX(acirc.xact_start) < NOW() - org_dispersal_age
    ORDER BY circs DESC LIMIT copies_to_disperse;

INSERT INTO rogan.dispersed_copies (copy_id,dispersal) (
        SELECT ids, disperse_id FROM disp_copies);


RETURN disperse_id;

END
$$ LANGUAGE plpgsql;


-- -------------------------------------------------------------------------------------------------
-- -------------------------------------------------------------------------------------------------
-- -------------------------------------------------------------------------------------------------

-- testing


SELECT * FROM rogan.disperse_copies(1008,1046,50,1599187);

SELECT * FROM rogan.dispersals;

SELECT * FROM rogan.dispersed_copies;

SELECT ac.barcode, rsr.author, rsr.title FROM asset.COPY ac
 JOIN asset.call_number acn ON acn.id = ac.call_number
 JOIN reporter.super_simple_record rsr ON rsr.id = acn.record
 WHERE ac.id IN (SELECT copy_id FROM rogan.dispersed_copies WHERE dispersal = 32)
 ;