From 2d804f9da625b0ee401e18b297a1f19da4e968e4 Mon Sep 17 00:00:00 2001 From: Chris Sharp Date: Wed, 28 Feb 2018 17:12:55 -0500 Subject: [PATCH] adding script to get report owner/folder info from an output URL --- reports/report_owner_from_url.pl | 132 +++++++++++++++++++++++++++++++++++++++ 1 file changed, 132 insertions(+) create mode 100755 reports/report_owner_from_url.pl diff --git a/reports/report_owner_from_url.pl b/reports/report_owner_from_url.pl new file mode 100755 index 0000000..6341e82 --- /dev/null +++ b/reports/report_owner_from_url.pl @@ -0,0 +1,132 @@ +#!/usr/bin/perl +# Copyright (C) 2018 Georgia Public Library Service +# Chris Sharp +# +# This program is free software: you can redistribute it and/or modify +# it under the terms of the GNU General Public License as published by +# the Free Software Foundation, either version 3 of the License, or +# (at your option) any later version. +# +# This program is distributed in the hope that it will be useful, +# but WITHOUT ANY WARRANTY; without even the implied warranty of +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +# GNU General Public License for more details. +# +# You should have received a copy of the GNU General Public License +# along with this program. If not, see . +# +=pod + +This is a utility for retrieving ownership and folder information +from a reports output URL from an Evergreen server. + +Usage: + + ./report_owner_from_url.pl + +Example: + + ./report_owner_from_url.pl https://gapines.org/reporter//59823/117959/684498/report-data.html + +Required Perl modules: + + - DBI + - URI::URL + - DBD::Pg + +These can be installed on a Debian-based system with the following packages: + + - libdbi-perl + - liburi-perl + - libdbd-pg-perl + +=cut + + +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; + +# by default, clark adds a "/" to the listed in +# opensrf.xml. This means that all of our report URLs have +# a double "/", and URI::URL assumes that's an empty directory +# on the path, so without that, this would be $path_array[3] +my $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