3 # Copyright (C) 2012 Georgia Public Library Service
4 # Chris Sharp <csharp@georgialibraries.org>
6 # This program is free software: you can redistribute it and/or modify
7 # it under the terms of the GNU General Public License as published by
8 # the Free Software Foundation, either version 3 of the License, or
9 # (at your option) any later version.
11 # This program is distributed in the hope that it will be useful,
12 # but WITHOUT ANY WARRANTY; without even the implied warranty of
13 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
14 # GNU General Public License for more details.
16 # You should have received a copy of the GNU General Public License
17 # along with this program. If not, see <http://www.gnu.org/licenses/>.
22 This is a utility for use with the Evergreen ILS that eases the transfer
23 the ownership of one user's report folders, templates, and report definitions
28 ./change_reports_owner.pl
35 my $select_query = qq/SELECT u.id, u.first_given_name, u.second_given_name, u.family_name, u.email
37 JOIN actor.card cd on (cd.usr = u.id and cd.active)
38 WHERE cd.barcode = ?/;
42 my $db_name = "mydbname";
43 my $db_host = "mydbhost";
44 my $db_user = "mydbuser";
45 my $db_pass = "mydbpass";
47 print "Please provide the library card barcode for the user who currently owns the reports: ";
48 chomp($old_barcode = <STDIN>);
49 print "Please provide the library card barcode for the user to whom we are transferring ownership: ";
50 chomp($new_barcode = <STDIN>);
53 my $dbh = DBI->connect("DBI:Pg:dbname=$db_name;host=$db_host", "$db_user", "$db_pass", {'RaiseError' => 1, 'AutoCommit' => 0});
56 # execute SELECT query
57 my $sth = $dbh->prepare("$select_query");
58 $sth->execute($old_barcode);
59 my ($user_id1, $first_name1, $middle_name1, $last_name1, $email1);
61 # iterate through resultset
63 my $ref = $sth->bind_columns(\($user_id1, $first_name1, $middle_name1, $last_name1, $email1));
65 print "The old user has ID $user_id1, barcode $old_barcode and is named $first_name1 $middle_name1 $last_name1 with an email address of $email1.\n";
68 # execute SELECT query
69 #$sth = $dbh->prepare("$select_query2");
70 $sth->execute($new_barcode);
71 my ($user_id2, $first_name2, $middle_name2, $last_name2, $email2);
73 # iterate through resultset
75 $ref = $sth->bind_columns(\($user_id2, $first_name2, $middle_name2, $last_name2, $email2));
77 print "The new user has ID $user_id2, barcode $new_barcode and is named $first_name2 $middle_name2 $last_name2 with an email address of $email2.\n";
80 print "The email address for user $last_name2 is $email2 - is this the correct email address to use (y/n)? ";
81 chomp($response = <STDIN>);
83 if ($response eq "n") {
84 print "Please enter the email address to be used: ";
85 chomp(my $email2 = <STDIN>);
86 print "We will use $email2 as the email address.\n";
87 } elsif ($response ne "y") {
88 print "Response invalid. Aborting.\n";
92 print "We will be transferring all report templates and report definitions from $first_name1 $middle_name1 $last_name1 ($old_barcode) to $first_name2 $middle_name2 $last_name2 ($new_barcode). Is that correct (y/n)? ";
93 chomp($response = <STDIN>);
95 if ($response eq "n") {
100 } elsif ($response eq "y") {
101 print "Beginning transfer of reports from $first_name1 $middle_name1 $last_name1 ($old_barcode) to $first_name2 $middle_name2 $last_name2 ($new_barcode)...\n";
103 print "The response is not valid. Aborting.\n";
109 my @tables = qw/reporter.template_folder reporter.report_folder reporter.output_folder reporter.template reporter.report/;
110 foreach my $table (@tables) {
111 my $update_owner = qq/UPDATE $table SET owner = ? WHERE owner = ?/;
112 $sth = $dbh->prepare("$update_owner");
113 $sth->execute($user_id2, $user_id1);
114 print "Setting new owner for $table.\n";
117 print "Updating reporter.schedule with new runner and replacing email addresses.\n";
119 if (defined($email1) && defined($email2)) {
120 my $update_schedule = qq/UPDATE reporter.schedule SET runner = ?, email = regexp_replace(email, ?, ?) WHERE runner = ? AND complete_time IS NULL/;
121 $sth = $dbh->prepare("$update_schedule");
122 $sth->execute($user_id2, $email1, $email2, $user_id1);
123 } elsif (defined($email2)) {
124 my $update_schedule = qq/UPDATE reporter.schedule SET runner = ?, email = email || ' ' || ? WHERE runner = ? AND complete_time IS NULL/;
125 $sth = $dbh->prepare("$update_schedule");
126 $sth->execute($user_id2, $email2, $user_id1);
128 my $update_schedule = qq/UPDATE reporter.schedule SET runner = ? WHERE runner = ? AND complete_time IS NULL/;
129 $sth = $dbh->prepare("$update_schedule");
130 $sth->execute($user_id2, $user_id1);