LP#1497335 Aged/All circulation API access
authorBill Erickson <berickxx@gmail.com>
Wed, 3 Aug 2016 17:34:29 +0000 (13:34 -0400)
committerMike Rylander <mrylander@gmail.com>
Wed, 24 Aug 2016 22:28:51 +0000 (18:28 -0400)
Various SQL, IDL, and API changes for accessing aged circulations,
primarily via all_circulation objects, for imporoved staff client
integration.

*. Support open-ils.pcrud access to the action.all_circulation DB view /
   'combcirc' class.

*. Add missing parent_circ, checkin_scan_time, checkin_workstation to
   combcirc class.

*. Add 'usr' field to combcirc.  The action.all_circulation VIEW will
   return NULL as the 'usr' column value when returning data for an
   aged_circulation.

*. Add virtual 'active_circ' and 'aged_circ' fields to combcirc for
   fleshing the related action.circulation or action.aged_circulation
   object.

* Adds SQL functions for action.all_circ_chain and
  action.summarize_all_circ_chain, which pull data from
  aged_circulation.

*. API calls
open-ils.circ.[prev_]renewal_chain.retrieve_by_circ[.summary] now return
data for active and aged circulations by using action.all_circ_chain and
action.summarize_all_circ_chain.  When using these APIs, a null value
in the 'usr' column is the indication that a given circulation or circ
chain summary represents an aged circulation.

* API open-ils.circ.copy_details.retrieve will now optionally return
  aged circ data within the copy circ history.

Signed-off-by: Bill Erickson <berickxx@gmail.com>
Open-ILS/examples/fm_IDL.xml
Open-ILS/src/perlmods/lib/OpenILS/Application/AppUtils.pm
Open-ILS/src/perlmods/lib/OpenILS/Application/Circ.pm
Open-ILS/src/sql/Pg/090.schema.action.sql
Open-ILS/src/sql/Pg/upgrade/XXXX.schema.aged-circ-chains.sql [new file with mode: 0644]

index 485e4c6..7126f16 100644 (file)
@@ -4209,7 +4209,7 @@ SELECT  usr,
                        </actions>
                </permacrud>
        </class>
-       <class id="combcirc" controller="open-ils.cstore" oils_obj:fieldmapper="action::all_circulation" oils_persist:tablename="action.all_circulation" reporter:core="true" reporter:label="Combined Aged and Active Circulations" oils_persist:readonly="true">
+       <class id="combcirc" controller="open-ils.cstore open-ils.pcrud" oils_obj:fieldmapper="action::all_circulation" oils_persist:tablename="action.all_circulation" reporter:core="true" reporter:label="Combined Aged and Active Circulations" oils_persist:readonly="true">
                <fields oils_persist:primary="id" oils_persist:sequence="money.billable_xact_id_seq">
                        <field reporter:label="Check In Library" name="checkin_lib" reporter:datatype="org_unit"/>
                        <field reporter:label="Check In Staff" name="checkin_staff" reporter:datatype="link"/>
@@ -4237,6 +4237,10 @@ SELECT  usr,
                        <field reporter:label="Transaction Finish Date/Time" name="xact_finish" reporter:datatype="timestamp" />
                        <field reporter:label="Checkout Date/Time" name="xact_start" reporter:datatype="timestamp" />
                        <field reporter:label="Record Creation Date/Time" name="create_time" reporter:datatype="timestamp" />
+                       <field reporter:label="Parent Circulation" name="parent_circ" reporter:datatype="link"/>
+                       <field reporter:label="Checkin Scan Time" name="checkin_scan_time" reporter:datatype="timestamp"/>
+                       <field reporter:label="Checkin Workstation" name="checkin_workstation" reporter:datatype="link"/>
+                       <field reporter:label="Patron" name="usr" reporter:datatype="link"/>
                        <field reporter:label="Transaction Billings" name="billings" oils_persist:virtual="true" reporter:datatype="link"/>
                        <field reporter:label="Transaction Payments" name="payments" oils_persist:virtual="true" reporter:datatype="link"/>
                        <field reporter:label="Base Transaction" name="billable_transaction" oils_persist:virtual="true" reporter:datatype="link"/>
@@ -4254,6 +4258,8 @@ SELECT  usr,
                        <field reporter:label="Bib Record" name="copy_bib_record" reporter:datatype="link"/>
                        <field reporter:label="Archived Patron Stat-Cat Entries" name="aaactsc_entries" oils_persist:virtual="true" reporter:datatype="link"/>
                        <field reporter:label="Archived Copy Stat-Cat Entries" name="aaasc_entries" oils_persist:virtual="true" reporter:datatype="link"/>
+                       <field reporter:label="Linked Active Circulation" name="active_circ" oils_persist:virtual="true" reporter:datatype="link"/>
+                       <field reporter:label="Linked Aged Circulation" name="aged_circ" oils_persist:virtual="true" reporter:datatype="link"/>
                </fields>
                <links>
                        <link field="billable_transaction" reltype="might_have" key="id" map="" class="mbt"/>
@@ -4275,12 +4281,22 @@ SELECT  usr,
                        <link field="copy_owning_lib" reltype="has_a" key="id" map="" class="aou"/>
                        <link field="copy_circ_lib" reltype="has_a" key="id" map="" class="aou"/>
                        <link field="workstation" reltype="has_a" key="id" map="" class="aws"/>
+                       <link field="checkin_workstation" reltype="has_a" key="id" map="" class="aws"/>
                        <link field="copy_bib_record" reltype="has_a" key="id" map="" class="bre"/>
                        <link field="aaactsc_entries" reltype="has_many" key="xact" map="" class="aaactsc"/>
                        <link field="aaasc_entries" reltype="has_many" key="xact" map="" class="aaasc"/>
                        <link field="usr_home_ou" reltype="has_a" key="id" map="" class="aou"/>
                        <link field="usr_profile" reltype="has_a" key="id" map="" class="pgt"/>
+                       <link field="active_circ" reltype="might_have" key="id" map="" class="circ"/>
+                       <link field="aged_circ" reltype="might_have" key="id" map="" class="acirc"/>
+                       <link field="parent_circ" reltype="might_have" key="id" map="" class="acirc"/>
+                       <link field="usr" reltype="has_a" key="id" map="" class="au"/>
                </links>
+               <permacrud xmlns="http://open-ils.org/spec/opensrf/IDL/permacrud/v1">
+                       <actions>
+                               <retrieve permission="VIEW_CIRCULATIONS" context_field="circ_lib" />
+                       </actions>
+               </permacrud>
        </class>
        <class id="acirc" controller="open-ils.cstore" oils_obj:fieldmapper="action::aged_circulation" oils_persist:tablename="action.aged_circulation" reporter:core="true" reporter:label="Aged (patronless) Circulation">
                <fields oils_persist:primary="id" oils_persist:sequence="money.billable_xact_id_seq">
index 0fad7d9..83b9789 100644 (file)
@@ -1811,7 +1811,7 @@ sub create_uuid_string {
 
 sub create_circ_chain_summary {
     my($class, $e, $circ_id) = @_;
-    my $sum = $e->json_query({from => ['action.summarize_circ_chain', $circ_id]})->[0];
+    my $sum = $e->json_query({from => ['action.summarize_all_circ_chain', $circ_id]})->[0];
     return undef unless $sum;
     my $obj = Fieldmapper::action::circ_chain_summary->new;
     $obj->$_($sum->{$_}) for keys %$sum;
index ba1133d..530da64 100644 (file)
@@ -55,14 +55,20 @@ __PACKAGE__->register_method(
         Retrieve a circ object by id
         @param authtoken Login session key
         @pararm circid The id of the circ object
+        @param all_circ Returns an action.all_circulation object instead
+            of an action.circulation object to pick up aged circs.
     /
 );
+
 sub retrieve_circ {
-    my( $s, $c, $a, $i ) = @_;
+    my( $s, $c, $a, $i, $all_circ ) = @_;
     my $e = new_editor(authtoken => $a);
     return $e->event unless $e->checkauth;
-    my $circ = $e->retrieve_action_circulation($i) or return $e->event;
-    if( $e->requestor->id ne $circ->usr ) {
+    my $method = $all_circ ?
+        'retrieve_action_all_circulation' :
+        'retrieve_action_circulation';
+    my $circ = $e->$method($i) or return $e->event;
+    if( $e->requestor->id ne ($circ->usr || '') ) {
         return $e->event unless $e->allowed('VIEW_CIRCULATIONS');
     }
     return $circ;
@@ -783,7 +789,7 @@ sub view_circs {
         $count = 4 unless defined $count;
     }
 
-    return $e->search_action_circulation([
+    return $e->search_action_all_circulation([
         {target_copy => $copyid}, 
         {limit => $count, order_by => { circ => "xact_start DESC" }} 
     ]);
@@ -1074,35 +1080,33 @@ sub copy_details {
     my $transit = $e->search_action_transit_copy(
         { target_copy => $copy_id, dest_recv_time => undef } )->[0];
 
-    # find the latest circ, open or closed
-    my $circ = $e->search_action_circulation(
-        [
-            { target_copy => $copy_id },
-            { 
-                flesh => 1,
-                flesh_fields => {
-                    circ => [
-                        'workstation',
-                        'checkin_workstation', 
-                        'duration_rule', 
-                        'max_fine_rule', 
-                        'recurring_fine_rule'
-                    ]
-                },
-                order_by => { circ => 'xact_start desc' }, 
-                limit => 1 
-            }
-        ]
-    )->[0];
-
+    # find the most recent circulation for the requested copy,
+    # be it active, completed, or aged.
+    my $circ = $e->search_action_all_circulation([
+        { target_copy => $copy_id },
+        {
+            flesh => 1,
+            flesh_fields => {
+                combcirc => [
+                    'workstation',
+                    'checkin_workstation',
+                    'duration_rule',
+                    'max_fine_rule',
+                    'recurring_fine_rule'
+                ],
+            },
+            order_by => { combcirc => 'xact_start desc' },
+            limit => 1
+        }
+    ])->[0];
 
     return {
-        copy        => $copy,
-        hold        => $hold,
+        copy    => $copy,
+        hold    => $hold,
         transit => $transit,
-        circ        => $circ,
+        circ    => $circ,
         volume  => $vol,
-        mvr     => $mvr,
+        mvr     => $mvr
     };
 }
 
@@ -1796,10 +1800,10 @@ sub retrieve_circ_chain {
 
     } else {
 
-        my $chain = $e->json_query({from => ['action.circ_chain', $circ_id]});
+        my $chain = $e->json_query({from => ['action.all_circ_chain', $circ_id]});
 
         for my $circ_info (@$chain) {
-            my $circ = Fieldmapper::action::circulation->new;
+            my $circ = Fieldmapper::action::all_circulation->new;
             $circ->$_($circ_info->{$_}) for keys %$circ_info;
             $conn->respond($circ);
         }
@@ -1844,51 +1848,51 @@ sub retrieve_prev_circ_chain {
     return $e->event unless $e->checkauth;
     return $e->event unless $e->allowed('VIEW_CIRCULATIONS');
 
-    if($self->api_name =~ /summary/) {
-        my $first_circ = $e->json_query({from => ['action.circ_chain', $circ_id]})->[0];
-        my $target_copy = $$first_circ{'target_copy'};
-        my $usr = $$first_circ{'usr'};
-        my $last_circ_from_prev_chain = $e->json_query({
-            'select' => { 'circ' => ['id','usr'] },
-            'from' => 'circ', 
-            'where' => {
-                target_copy => $target_copy,
-                xact_start => { '<' => $$first_circ{'xact_start'} }
+    my $first_circ = 
+        $e->json_query({from => ['action.all_circ_chain', $circ_id]})->[0];
+
+    my $prev_circ = $e->search_action_all_circulation([
+        {   target_copy => $first_circ->{target_copy},
+            xact_start => {'<' => $first_circ->{xact_start}}
+        }, {   
+            flesh => 1,
+            flesh_fields => {
+                combcirc => [
+                    'active_circ',
+                    'aged_circ'
+                ]
             },
-            'order_by' => [{ 'class'=>'circ', 'field'=>'xact_start', 'direction'=>'desc' }],
-            'limit' => 1
-        })->[0];
-        return undef unless $last_circ_from_prev_chain;
-        return undef unless $$last_circ_from_prev_chain{'id'};
-        my $sum = $e->json_query({from => ['action.summarize_circ_chain', $$last_circ_from_prev_chain{'id'}]})->[0];
-        return undef unless $sum;
-        my $obj = Fieldmapper::action::circ_chain_summary->new;
-        $obj->$_($sum->{$_}) for keys %$sum;
-        return { 'summary' => $obj, 'usr' => $$last_circ_from_prev_chain{'usr'} };
+            order_by => { combcirc => 'xact_start desc' },
+            limit => 1 
+        }
+    ])->[0];
 
-    } else {
+    return undef unless $prev_circ;
 
-        my $first_circ = $e->json_query({from => ['action.circ_chain', $circ_id]})->[0];
-        my $target_copy = $$first_circ{'target_copy'};
-        my $last_circ_from_prev_chain = $e->json_query({
-            'select' => { 'circ' => ['id'] },
-            'from' => 'circ', 
-            'where' => {
-                target_copy => $target_copy,
-                xact_start => { '<' => $$first_circ{'xact_start'} }
-            },
-            'order_by' => [{ 'class'=>'circ', 'field'=>'xact_start', 'direction'=>'desc' }],
-            'limit' => 1
+    my $chain_usr = $prev_circ->usr; # note: may be undef
+
+    if ($self->api_name =~ /summary/) {
+        my $sum = $e->json_query({
+            from => [
+                'action.summarize_all_circ_chain', 
+                $prev_circ->id
+            ]
         })->[0];
-        return undef unless $last_circ_from_prev_chain;
-        return undef unless $$last_circ_from_prev_chain{'id'};
-        my $chain = $e->json_query({from => ['action.circ_chain', $$last_circ_from_prev_chain{'id'}]});
 
-        for my $circ_info (@$chain) {
-            my $circ = Fieldmapper::action::circulation->new;
-            $circ->$_($circ_info->{$_}) for keys %$circ_info;
-            $conn->respond($circ);
-        }
+        my $summary = Fieldmapper::action::circ_chain_summary->new;
+        $summary->$_($sum->{$_}) for keys %$sum;
+
+        return {summary => $summary, usr => $chain_usr};
+    }
+
+
+    my $chain = $e->json_query(
+        {from => ['action.all_circ_chain', $prev_circ->id]});
+
+    for my $circ_info (@$chain) {
+        my $circ = Fieldmapper::action::all_circulation->new;
+        $circ->$_($circ_info->{$_}) for keys %$circ_info;
+        $conn->respond($circ);
     }
 
     return undef;
index 5ce80d3..731ab3c 100644 (file)
@@ -239,7 +239,8 @@ CREATE OR REPLACE VIEW action.all_circulation AS
         circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, grace_period, due_date,
         stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
         max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
-        max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ
+        max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ,
+        NULL AS usr
       FROM  action.aged_circulation
             UNION ALL
     SELECT  DISTINCT circ.id,COALESCE(a.post_code,b.post_code) AS usr_post_code, p.home_ou AS usr_home_ou, p.profile AS usr_profile, EXTRACT(YEAR FROM p.dob)::INT AS usr_birth_year,
@@ -248,7 +249,7 @@ CREATE OR REPLACE VIEW action.all_circulation AS
         circ.checkin_lib, circ.renewal_remaining, circ.grace_period, circ.due_date, circ.stop_fines_time, circ.checkin_time, circ.create_time, circ.duration,
         circ.fine_interval, circ.recurring_fine, circ.max_fine, circ.phone_renewal, circ.desk_renewal, circ.opac_renewal, circ.duration_rule,
         circ.recurring_fine_rule, circ.max_fine_rule, circ.stop_fines, circ.workstation, circ.checkin_workstation, circ.checkin_scan_time,
-        circ.parent_circ
+        circ.parent_circ, circ.usr
       FROM  action.circulation circ
         JOIN asset.copy cp ON (circ.target_copy = cp.id)
         JOIN asset.call_number cn ON (cp.call_number = cn.id)
@@ -883,6 +884,95 @@ BEGIN
 END;
 $$ LANGUAGE 'plpgsql';
 
+-- same as action.circ_chain, but returns action.all_circulation 
+-- rows which may include aged circulations.
+CREATE OR REPLACE FUNCTION action.all_circ_chain (ctx_circ_id INTEGER) 
+    RETURNS SETOF action.all_circulation AS $$
+DECLARE
+    tmp_circ action.all_circulation%ROWTYPE;
+    circ_0 action.all_circulation%ROWTYPE;
+BEGIN
+
+    SELECT INTO tmp_circ * FROM action.all_circulation WHERE id = ctx_circ_id;
+
+    IF tmp_circ IS NULL THEN
+        RETURN NEXT tmp_circ;
+    END IF;
+    circ_0 := tmp_circ;
+
+    -- find the front of the chain
+    WHILE TRUE LOOP
+        SELECT INTO tmp_circ * FROM action.all_circulation 
+            WHERE id = tmp_circ.parent_circ;
+        IF tmp_circ IS NULL THEN
+            EXIT;
+        END IF;
+        circ_0 := tmp_circ;
+    END LOOP;
+
+    -- now send the circs to the caller, oldest to newest
+    tmp_circ := circ_0;
+    WHILE TRUE LOOP
+        IF tmp_circ IS NULL THEN
+            EXIT;
+        END IF;
+        RETURN NEXT tmp_circ;
+        SELECT INTO tmp_circ * FROM action.all_circulation 
+            WHERE parent_circ = tmp_circ.id;
+    END LOOP;
+
+END;
+$$ LANGUAGE 'plpgsql';
+
+-- same as action.summarize_circ_chain, but returns data collected
+-- from action.all_circulation, which may include aged circulations.
+CREATE OR REPLACE FUNCTION action.summarize_all_circ_chain 
+    (ctx_circ_id INTEGER) RETURNS action.circ_chain_summary AS $$
+
+DECLARE
+
+    -- first circ in the chain
+    circ_0 action.all_circulation%ROWTYPE;
+
+    -- last circ in the chain
+    circ_n action.all_circulation%ROWTYPE;
+
+    -- circ chain under construction
+    chain action.circ_chain_summary;
+    tmp_circ action.all_circulation%ROWTYPE;
+
+BEGIN
+    
+    chain.num_circs := 0;
+    FOR tmp_circ IN SELECT * FROM action.all_circ_chain(ctx_circ_id) LOOP
+
+        IF chain.num_circs = 0 THEN
+            circ_0 := tmp_circ;
+        END IF;
+
+        chain.num_circs := chain.num_circs + 1;
+        circ_n := tmp_circ;
+    END LOOP;
+
+    chain.start_time := circ_0.xact_start;
+    chain.last_stop_fines := circ_n.stop_fines;
+    chain.last_stop_fines_time := circ_n.stop_fines_time;
+    chain.last_checkin_time := circ_n.checkin_time;
+    chain.last_checkin_scan_time := circ_n.checkin_scan_time;
+    SELECT INTO chain.checkout_workstation name FROM actor.workstation WHERE id = circ_0.workstation;
+    SELECT INTO chain.last_checkin_workstation name FROM actor.workstation WHERE id = circ_n.checkin_workstation;
+
+    IF chain.num_circs > 1 THEN
+        chain.last_renewal_time := circ_n.xact_start;
+        SELECT INTO chain.last_renewal_workstation name FROM actor.workstation WHERE id = circ_n.workstation;
+    END IF;
+
+    RETURN chain;
+
+END;
+$$ LANGUAGE 'plpgsql';
+
+
 CREATE OR REPLACE FUNCTION action.usr_visible_holds (usr_id INT) RETURNS SETOF action.hold_request AS $func$
 DECLARE
     h               action.hold_request%ROWTYPE;
diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.aged-circ-chains.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.aged-circ-chains.sql
new file mode 100644 (file)
index 0000000..b618980
--- /dev/null
@@ -0,0 +1,141 @@
+
+BEGIN;
+
+-- SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version);
+
+DROP VIEW IF EXISTS action.all_circulation;
+CREATE VIEW action.all_circulation AS
+     SELECT aged_circulation.id, aged_circulation.usr_post_code,
+        aged_circulation.usr_home_ou, aged_circulation.usr_profile,
+        aged_circulation.usr_birth_year, aged_circulation.copy_call_number,
+        aged_circulation.copy_location, aged_circulation.copy_owning_lib,
+        aged_circulation.copy_circ_lib, aged_circulation.copy_bib_record,
+        aged_circulation.xact_start, aged_circulation.xact_finish,
+        aged_circulation.target_copy, aged_circulation.circ_lib,
+        aged_circulation.circ_staff, aged_circulation.checkin_staff,
+        aged_circulation.checkin_lib, aged_circulation.renewal_remaining,
+        aged_circulation.grace_period, aged_circulation.due_date,
+        aged_circulation.stop_fines_time, aged_circulation.checkin_time,
+        aged_circulation.create_time, aged_circulation.duration,
+        aged_circulation.fine_interval, aged_circulation.recurring_fine,
+        aged_circulation.max_fine, aged_circulation.phone_renewal,
+        aged_circulation.desk_renewal, aged_circulation.opac_renewal,
+        aged_circulation.duration_rule,
+        aged_circulation.recurring_fine_rule,
+        aged_circulation.max_fine_rule, aged_circulation.stop_fines,
+        aged_circulation.workstation, aged_circulation.checkin_workstation,
+        aged_circulation.checkin_scan_time, aged_circulation.parent_circ,
+        NULL AS usr
+       FROM action.aged_circulation
+UNION ALL
+     SELECT DISTINCT circ.id,
+        COALESCE(a.post_code, b.post_code) AS usr_post_code,
+        p.home_ou AS usr_home_ou, p.profile AS usr_profile,
+        date_part('year'::text, p.dob)::integer AS usr_birth_year,
+        cp.call_number AS copy_call_number, circ.copy_location,
+        cn.owning_lib AS copy_owning_lib, cp.circ_lib AS copy_circ_lib,
+        cn.record AS copy_bib_record, circ.xact_start, circ.xact_finish,
+        circ.target_copy, circ.circ_lib, circ.circ_staff,
+        circ.checkin_staff, circ.checkin_lib, circ.renewal_remaining,
+        circ.grace_period, circ.due_date, circ.stop_fines_time,
+        circ.checkin_time, circ.create_time, circ.duration,
+        circ.fine_interval, circ.recurring_fine, circ.max_fine,
+        circ.phone_renewal, circ.desk_renewal, circ.opac_renewal,
+        circ.duration_rule, circ.recurring_fine_rule, circ.max_fine_rule,
+        circ.stop_fines, circ.workstation, circ.checkin_workstation,
+        circ.checkin_scan_time, circ.parent_circ, circ.usr
+       FROM action.circulation circ
+  JOIN asset.copy cp ON circ.target_copy = cp.id
+JOIN asset.call_number cn ON cp.call_number = cn.id
+JOIN actor.usr p ON circ.usr = p.id
+LEFT JOIN actor.usr_address a ON p.mailing_address = a.id
+LEFT JOIN actor.usr_address b ON p.billing_address = b.id;
+
+
+CREATE OR REPLACE FUNCTION action.all_circ_chain (ctx_circ_id INTEGER) 
+    RETURNS SETOF action.all_circulation AS $$
+DECLARE
+    tmp_circ action.all_circulation%ROWTYPE;
+    circ_0 action.all_circulation%ROWTYPE;
+BEGIN
+
+    SELECT INTO tmp_circ * FROM action.all_circulation WHERE id = ctx_circ_id;
+
+    IF tmp_circ IS NULL THEN
+        RETURN NEXT tmp_circ;
+    END IF;
+    circ_0 := tmp_circ;
+
+    -- find the front of the chain
+    WHILE TRUE LOOP
+        SELECT INTO tmp_circ * FROM action.all_circulation 
+            WHERE id = tmp_circ.parent_circ;
+        IF tmp_circ IS NULL THEN
+            EXIT;
+        END IF;
+        circ_0 := tmp_circ;
+    END LOOP;
+
+    -- now send the circs to the caller, oldest to newest
+    tmp_circ := circ_0;
+    WHILE TRUE LOOP
+        IF tmp_circ IS NULL THEN
+            EXIT;
+        END IF;
+        RETURN NEXT tmp_circ;
+        SELECT INTO tmp_circ * FROM action.all_circulation 
+            WHERE parent_circ = tmp_circ.id;
+    END LOOP;
+
+END;
+$$ LANGUAGE 'plpgsql';
+
+CREATE OR REPLACE FUNCTION action.summarize_all_circ_chain 
+    (ctx_circ_id INTEGER) RETURNS action.circ_chain_summary AS $$
+
+DECLARE
+
+    -- first circ in the chain
+    circ_0 action.all_circulation%ROWTYPE;
+
+    -- last circ in the chain
+    circ_n action.all_circulation%ROWTYPE;
+
+    -- circ chain under construction
+    chain action.circ_chain_summary;
+    tmp_circ action.all_circulation%ROWTYPE;
+
+BEGIN
+    
+    chain.num_circs := 0;
+    FOR tmp_circ IN SELECT * FROM action.all_circ_chain(ctx_circ_id) LOOP
+
+        IF chain.num_circs = 0 THEN
+            circ_0 := tmp_circ;
+        END IF;
+
+        chain.num_circs := chain.num_circs + 1;
+        circ_n := tmp_circ;
+    END LOOP;
+
+    chain.start_time := circ_0.xact_start;
+    chain.last_stop_fines := circ_n.stop_fines;
+    chain.last_stop_fines_time := circ_n.stop_fines_time;
+    chain.last_checkin_time := circ_n.checkin_time;
+    chain.last_checkin_scan_time := circ_n.checkin_scan_time;
+    SELECT INTO chain.checkout_workstation name FROM actor.workstation WHERE id = circ_0.workstation;
+    SELECT INTO chain.last_checkin_workstation name FROM actor.workstation WHERE id = circ_n.checkin_workstation;
+
+    IF chain.num_circs > 1 THEN
+        chain.last_renewal_time := circ_n.xact_start;
+        SELECT INTO chain.last_renewal_workstation name FROM actor.workstation WHERE id = circ_n.workstation;
+    END IF;
+
+    RETURN chain;
+
+END;
+$$ LANGUAGE 'plpgsql';
+
+
+COMMIT;
+