some paper overdues require unifont pkg

[sitka/sitka-tools.git] / maintenance / age-circs.sql

diff --git a/maintenance/age-circs.sql b/maintenance/age-circs.sql
index 5fb7f53..a5b5353 100644 (file)
--- a/maintenance/age-circs.sql
+++ b/maintenance/age-circs.sql
@@ -30,14 +30,16 @@ CREATE TABLE sitka.age_circ_exception_matrix (
 CREATE OR REPLACE FUNCTION sitka.has_age_circ_exception(circ_id BIGINT) RETURNS BOOL AS $$
 DECLARE
   potential_aged_circ  action.circulation%ROWTYPE;
+  usr                  actor.usr%ROWTYPE;
   matching_exceptions  INT;
 BEGIN
   matching_exceptions := 0;
   -- TODO: should we match exceptions against the entire circ chain?
   SELECT * INTO potential_aged_circ FROM action.circulation WHERE id = circ_id;
+  SELECT * INTO usr FROM actor.usr WHERE id = potential_aged_circ.usr;
   SELECT count(*) INTO matching_exceptions
     FROM sitka.age_circ_exception_matrix m
-      LEFT JOIN permission.grp_ancestors_distance( potential_aged_circ.usr_profile ) upgad ON m.grp = upgad.id
+      LEFT JOIN permission.grp_ancestors_distance( usr.profile ) upgad ON m.grp = upgad.id
       LEFT JOIN actor.org_unit_ancestors_distance( potential_aged_circ.circ_lib ) ctoua ON m.org_unit = ctoua.id
     WHERE m.active
       AND (m.grp IS NULL      OR upgad.id IS NOT NULL)
@@ -52,7 +54,7 @@ $$ LANGUAGE plpgsql;
 \echo 'table for aged circs'
 CREATE TABLE sitka.aged_circs (
   id BIGINT NOT NULL,
-  circ_chain_tail_xact_finish TIMESTAMPTZ,
+  circ_chain_tail_xact_finish TIMESTAMPTZ NOT NULL,
   delete_date DATE NOT NULL
 );
 
@@ -125,8 +127,8 @@ BEGIN
   END LOOP;
 
   -- log the aging of this circ
-  INSERT INTO sitka.aged_circs (id, delete_date)
-    SELECT id, today FROM circs_to_age;
+  INSERT INTO sitka.aged_circs (id, circ_chain_tail_xact_finish, delete_date)
+    SELECT id, circ_chain_tail.xact_finish, today FROM circs_to_age;
 
   -- update user circ counts
   CREATE TEMPORARY TABLE usr_circ_totals AS
@@ -143,6 +145,11 @@ BEGIN
     WHERE (usr, month, year) NOT IN (SELECT usr, month, year FROM sitka.aged_circs_by_user);
   DROP TABLE usr_circ_totals;
 
+  -- old circs may have a null copy_location, but action.aged_circulation doesn't allow that
+  UPDATE action.circulation a SET copy_location = 1
+    FROM circs_to_age b WHERE a.id = b.id
+    AND a.copy_location IS NULL;
+
   -- this will set off the age_circs trigger on action.circulation
   DELETE FROM action.circulation WHERE id IN (SELECT id FROM circs_to_age);
 
@@ -151,6 +158,7 @@ BEGIN
   WHERE usr_post_code IS NOT NULL OR usr_birth_year IS NOT NULL;
 
   SELECT count(*) INTO circs_aged FROM circs_to_age;
+  DROP TABLE circs_to_age;
   RETURN circs_aged;
 END;
 $$ LANGUAGE plpgsql;