From cc299405d999d4e05cca71e2d319f72c9340fb2e Mon Sep 17 00:00:00 2001 From: miker Date: Wed, 23 Apr 2008 12:23:10 +0000 Subject: [PATCH] adding helper views for overdue, running and pending reports git-svn-id: svn://svn.open-ils.org/ILS/trunk@9440 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/src/sql/Pg/reporter-schema.sql | 24 ++++++++++++++++++++++++ 1 file changed, 24 insertions(+) diff --git a/Open-ILS/src/sql/Pg/reporter-schema.sql b/Open-ILS/src/sql/Pg/reporter-schema.sql index caf7a2025e..30b3bd0ef4 100644 --- a/Open-ILS/src/sql/Pg/reporter-schema.sql +++ b/Open-ILS/src/sql/Pg/reporter-schema.sql @@ -213,5 +213,29 @@ SELECT * AND (stop_fines NOT IN ('LOST','CLAIMSRETURNED') OR stop_fines IS NULL) AND due_date < now(); +CREATE OR REPLACE VIEW reporter.overdue_reports AS + SELECT s.id, c.barcode AS runner_barcode, r.name, s.run_time, s.run_time - now() AS scheduled_wait_time + FROM reporter.schedule s + JOIN reporter.report r ON r.id = s.report + JOIN actor.usr u ON s.runner = u.id + JOIN actor.card c ON c.id = u.card + WHERE s.start_time IS NULL AND s.run_time < now(); + +CREATE OR REPLACE VIEW reporter.pending_reports AS + SELECT s.id, c.barcode AS runner_barcode, r.name, s.run_time, s.run_time - now() AS scheduled_wait_time + FROM reporter.schedule s + JOIN reporter.report r ON r.id = s.report + JOIN actor.usr u ON s.runner = u.id + JOIN actor.card c ON c.id = u.card + WHERE s.start_time IS NULL; + +CREATE OR REPLACE VIEW reporter.currently_running AS + SELECT s.id, c.barcode AS runner_barcode, r.name, s.run_time, s.run_time - now() AS scheduled_wait_time + FROM reporter.schedule s + JOIN reporter.report r ON r.id = s.report + JOIN actor.usr u ON s.runner = u.id + JOIN actor.card c ON c.id = u.card + WHERE s.start_time IS NOT NULL AND s.complete_time IS NULL; + COMMIT; -- 2.43.2