NOW() - (SELECT MIN(value) FROM (
SELECT value::INTERVAL FROM actor.org_unit_ancestor_setting(
'circ.staff.max_visible_event_age',
COALESCE(targ_circ.circ_lib, targ_ahr.pickup_lib)
) UNION
SELECT '1000 YEARS'::INTERVAL AS value
) ous)
]]>= '1 day' AND due_date >= 'today') OR (fine_interval < '1 day' AND due_date > 'now'))
AND (stop_fines IS NULL OR stop_fines NOT IN ('LOST','CLAIMSRETURNED','LONGOVERDUE'))
) THEN 1
ELSE 0
END
) AS out,
SUM(
CASE
WHEN (
((fine_interval >= '1 day' AND due_date < 'today') OR (fine_interval < '1 day' AND due_date < 'now'))
AND (stop_fines IS NULL OR stop_fines NOT IN ('LOST','CLAIMSRETURNED','LONGOVERDUE'))
) THEN 1
ELSE 0
END
) AS overdue,
SUM( CASE WHEN (xact_finish IS NULL AND stop_fines = 'LOST') THEN 1 ELSE 0 END) AS lost,
SUM( CASE WHEN stop_fines = 'CLAIMSRETURNED' THEN 1 ELSE 0 END) AS claims_returned,
SUM( CASE WHEN (xact_finish IS NULL AND stop_fines = 'LONGOVERDUE') THEN 1 ELSE 0 END) AS long_overdue
FROM action.circulation
WHERE checkin_time IS NULL
GROUP BY 1
]]>= '1 day' AND due_date >= 'today') OR (fine_interval < '1 day' AND due_date > 'now'))
AND (stop_fines IS NULL OR stop_fines NOT IN ('LOST','CLAIMSRETURNED','LONGOVERDUE'))
) THEN id::TEXT
ELSE '0'
END
,',') AS out,
STRING_AGG(
CASE
WHEN (
((fine_interval >= '1 day' AND due_date < 'today') OR (fine_interval < '1 day' AND due_date < 'now'))
AND (stop_fines IS NULL OR stop_fines NOT IN ('LOST','CLAIMSRETURNED','LONGOVERDUE'))
) THEN id::TEXT
ELSE '0'
END
,',') AS overdue,
STRING_AGG( CASE WHEN (xact_finish IS NULL AND stop_fines = 'LOST') THEN id::TEXT ELSE '0' END,',') AS lost,
STRING_AGG( CASE WHEN stop_fines = 'CLAIMSRETURNED' THEN id::TEXT ELSE '0' END,',') AS claims_returned,
STRING_AGG( CASE WHEN (xact_finish IS NULL AND stop_fines = 'LONGOVERDUE') THEN id::TEXT ELSE '0' END,',') AS long_overdue
FROM action.circulation
WHERE checkin_time IS NULL
GROUP BY 1
]]> ahr.requestor) AS is_staff_hold,
ahcm_1.copy_count AS potential_copies
FROM action.hold_request ahr
JOIN asset.copy acp ON (acp.id = ahr.current_copy)
JOIN asset.call_number acn ON (acp.call_number = acn.id)
JOIN asset.call_number_prefix acnp ON (acn.prefix = acnp.id)
JOIN asset.call_number_suffix acns ON (acn.suffix = acns.id)
JOIN actor.usr au ON (au.id = ahr.usr)
JOIN (
SELECT *, (ROW_NUMBER() OVER (ORDER BY name) + 1000000) AS fallback_position
FROM asset.copy_location
) acpl_ordered ON (acpl_ordered.id = acp.location)
LEFT JOIN actor.usr_standing_penalty ausp
ON (ahr.usr = ausp.usr AND (ausp.stop_date IS NULL OR ausp.stop_date > NOW()))
LEFT JOIN config.standing_penalty csp
ON (
csp.id = ausp.standing_penalty AND
csp.block_list LIKE '%CAPTURE%' AND (
(csp.org_depth IS NULL AND ahr.pickup_lib = ausp.org_unit) OR
(csp.org_depth IS NOT NULL AND ahr.pickup_lib IN (
SELECT id FROM actor.org_unit_descendants(ausp.org_unit, csp.org_depth))
)
)
)
JOIN (
SELECT COUNT(target_copy) AS copy_count, hold
FROM action.hold_copy_map
GROUP BY 2
) ahcm_1 ON (ahcm_1.hold = ahr.id)
LEFT JOIN serial.issuance siss
ON (ahr.hold_type = 'I' AND siss.id = ahr.target)
LEFT JOIN asset.copy_location_order acplo
ON (acp.location = acplo.location AND
acp.circ_lib = acplo.org)
WHERE
ahr.capture_time IS NULL AND
ahr.cancel_time IS NULL AND
csp.id IS NULL AND
(ahr.expire_time is NULL OR ahr.expire_time > NOW()) AND
acp.status IN (0,7)
]]> po.order_date
-- filter out all claim policy actions where claim events
-- have occurred on or after the action's action_interval
AND NOT EXISTS (
SELECT 1
FROM
acq.claim_event evt
JOIN acq.claim claim ON (
claim.id = evt.claim
AND claim.lineitem_detail = lid.id
)
WHERE
evt.event_date >= (po.order_date + cpa.action_interval)
)
)
WHERE
lid.cancel_reason IS NULL
AND li.cancel_reason IS NULL -- belt/suspenders
AND po.cancel_reason IS NULL -- belt/suspenders
AND lid.recv_time IS NULL
AND po.state = 'on-order'
ORDER BY 1, 2, 3, 4, 5
]]> (invoice_count + cancel_count)
]]> d.parent_ou
]]>