From 4779d45b56987da5442c1c5c0da4253fa841231f Mon Sep 17 00:00:00 2001
From: Chris Sharp <csharp@georgialibraries.org>
Date: Wed, 5 Sep 2018 07:39:20 -0400
Subject: [PATCH] add convenience script to get report template locations from
 output URL

---
 pines-finder/report_owner_from_url.pl | 94 +++++++++++++++++++++++++++++++++++
 1 file changed, 94 insertions(+)
 create mode 100755 pines-finder/report_owner_from_url.pl

diff --git a/pines-finder/report_owner_from_url.pl b/pines-finder/report_owner_from_url.pl
new file mode 100755
index 0000000..3cee7d4
--- /dev/null
+++ b/pines-finder/report_owner_from_url.pl
@@ -0,0 +1,94 @@
+#!/usr/bin/perl
+
+use warnings;
+use strict;
+use URI::URL;
+use DBI;
+
+my $settings = {
+    host => "db03",
+    db => "evergreen",
+    user => "evergreen"
+};
+
+my $input_url = $ARGV[0];
+
+my $url = new URI::URL $input_url;
+
+my $query = qq/ select r.name as report_name,
+                rowner.first_given_name as rowner_first_name, 
+                rowner.second_given_name as rowner_middle_name, 
+                rowner.family_name as rowner_last_name, 
+                rowner_cd.barcode as rowner_barcode,
+                rfolder.name as rfolder_name,
+                rfolder_owner.first_given_name as rfo_first_name,
+                rfolder_owner.second_given_name as rfo_middle_name,
+                rfolder_owner.family_name as rfo_last_name,
+                rfo_cd.barcode as rfo_barcode,
+                t.name as template_name,
+                towner.first_given_name as towner_first_name,
+                towner.second_given_name as towner_middle_name,
+                towner.family_name as towner_last_name,
+                towner_cd.barcode as towner_barcode,
+                tfolder.name as tfolder_name,
+                tfolder_owner.first_given_name as tfo_first_name,
+                tfolder_owner.second_given_name as tfo_middle_name,
+                tfolder_owner.family_name as tfo_last_name,
+                tfo_cd.barcode as tfo_barcode,
+                rfparent.name as rfparent_name,
+                tfparent.name as tfparent_name
+                from reporter.report r
+                inner join actor.usr rowner on (r.owner = rowner.id)
+                inner join actor.card rowner_cd on (rowner.card = rowner_cd.id)
+                inner join reporter.report_folder rfolder on (r.folder = rfolder.id)
+                inner join actor.usr rfolder_owner on (rfolder.owner = rfolder_owner.id)
+                inner join actor.card rfo_cd on (rfolder_owner.card = rfo_cd.id)
+                inner join reporter.template t on (r.template = t.id)
+                inner join actor.usr towner on (t.owner = towner.id)
+                inner join actor.card towner_cd on (towner.card = towner_cd.id)
+                inner join reporter.template_folder tfolder on (t.folder = tfolder.id)
+                inner join actor.usr tfolder_owner on (tfolder.owner = tfolder_owner.id)
+                inner join actor.card tfo_cd on (tfolder_owner.card = tfo_cd.id)
+                left outer join reporter.template_folder tfparent on (tfolder.parent = tfparent.id)
+                left outer join reporter.report_folder rfparent on (rfolder.parent = rfparent.id)
+                where r.id = ?/;
+
+my @path_array = $url->path_components;
+
+# example URL: https://gapines.org/reporter//58631/126950/876937/report-data.html
+
+my $report;
+# it's possible that the URL has an extra empty
+# directory in the path, so check for it
+if ($path_array[2]) {
+    $report = $path_array[3];
+} else {
+    $report = $path_array[4];
+}
+
+my $dbh = DBI->connect('DBI:Pg:dbname=' . $settings->{'db'} . ';host=' . $settings->{'host'}, $settings->{'user'}, 
+            undef,
+            {
+                RaiseError => 1,
+                ShowErrorStatement => 0,
+                AutoCommit => 0
+            }
+) or die DBI->errstr;
+
+my $sth = $dbh->prepare($query);
+$sth->execute($report);
+my $results = $sth->fetchrow_hashref;
+
+print "Report Name: $results->{'report_name'}\n";
+print "Report Owner: $results->{'rowner_first_name'}" . (defined $results->{'rowner_middle_name'} ? " $results->{'rowner_middle_name'}" : "") . " $results->{'rowner_last_name'} ($results->{'rowner_barcode'})\n";
+print "Report Folder: $results->{'rfolder_name'}\n";
+print "Report Folder Parent Folder: " . (defined $results->{'rfparent_name'} ? "$results->{'rfparent_name'}" : "(no parent folder)") . "\n";
+print "Report Folder Owner: $results->{'rfo_first_name'} $results->{'rfo_middle_name'} $results->{'rfo_last_name'} ($results->{'rfo_barcode'})\n";
+print "Template Name: $results->{'template_name'}\n";
+print "Template Owner: $results->{'towner_first_name'}" . (defined $results->{'towner_middle_name'} ? " $results->{'towner_middle_name'}" : "") . " $results->{'towner_last_name'} ($results->{'towner_barcode'})\n";
+print "Template Folder: $results->{'tfolder_name'}\n";
+print "Template Folder Parent Folder: " . (defined $results->{'tfparent_name'} ? "$results->{'tfparent_name'}" : "(no parent folder)") . "\n";
+print "Template Folder Owner: $results->{'tfo_first_name'}" . (defined $results->{'tfo_middle_name'} ? " $results->{'tfo_middle_name'}" : "") . " $results->{'tfo_last_name'} ($results->{'tfo_barcode'})\n";
+
+$sth->finish();
+$dbh->disconnect();
-- 
2.11.0