egdb3_11_7.rating

Parameters

Name	Type	Mode
badge_id	integer	IN
record	bigint	TABLE
value	numeric	TABLE

Definition

DECLARE
    badge   rating.badge_with_orgs%ROWTYPE;
    iage    INT     := 1;
    iint    INT     := NULL;
    iscale  NUMERIC := NULL;
BEGIN

SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;

IF badge.horizon_age IS NULL THEN
        RAISE EXCEPTION 'Badge "%" with id % requires a horizon age but has none.',
            badge.name,
            badge.id;
    END IF;

PERFORM rating.precalc_bibs_by_copy(badge_id);

DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
        SELECT id FROM precalc_filter_bib_list
            INTERSECT
        SELECT id FROM precalc_bibs_by_copy_list
    );

ANALYZE precalc_copy_filter_bib_list;

iint := EXTRACT(EPOCH FROM badge.importance_interval);
    IF badge.importance_age IS NOT NULL THEN
        iage := (EXTRACT(EPOCH FROM badge.importance_age) / iint)::INT;
    END IF;

-- if iscale is smaller than 1, scaling slope will be shallow ... BEWARE!
    iscale := COALESCE(badge.importance_scale, 1.0);

RETURN QUERY
     SELECT bib,
            SUM( circs * GREATEST( iscale * (iage - cage), 1.0 ))
      FROM (
         SELECT cn.record AS bib,
                (1 + EXTRACT(EPOCH FROM AGE(c.xact_start)) / iint)::INT AS cage,
                COUNT(c.id)::INT AS circs
          FROM  action.circulation c
                JOIN precalc_copy_filter_bib_list cf ON (c.target_copy = cf.copy)
                JOIN asset.copy cp ON (cp.id = c.target_copy)
                JOIN asset.call_number cn ON (cn.id = cp.call_number)
          WHERE c.xact_start >= NOW() - badge.horizon_age
                AND cn.owning_lib = ANY (badge.orgs)
                AND c.phone_renewal IS FALSE  -- we don't count renewals
                AND c.desk_renewal IS FALSE
                AND c.opac_renewal IS FALSE
          GROUP BY 1, 2
      ) x
      GROUP BY 1;
END;

circs_over_time(badge_id integer)

Parameters

Definition