From a560c897709f81dd478114ecb8ea7d97039c8772 Mon Sep 17 00:00:00 2001 From: Kyle M Hall Date: Thu, 29 Aug 2013 12:23:39 -0400 Subject: [PATCH] Bug 10636 - patronimage should have borrowernumber as PK, not cardnumber Test Plan: 1) Apply this patch 2) Run updatedatabase.pl 3) Enable patronimages 4) Verify patron images are still displaying correctly 5) Test deleting a patron image 6) Test adding a patron image from moremember.pl 7) Test adding a patron image from tools/picture-upload.pl --- C4/Members.pm | 26 ++++++------ circ/circulation.pl | 2 +- installer/data/mysql/kohastructure.sql | 6 +- installer/data/mysql/updatedatabase.pl | 46 ++++++++++++++++++++ .../intranet-tmpl/prog/en/includes/circ-menu.inc | 2 +- .../intranet-tmpl/prog/en/includes/circ-menu.tt | 2 +- members/boraccount.pl | 2 +- members/deletemem.pl | 2 +- members/files.pl | 2 +- members/mancredit.pl | 2 +- members/maninvoice.pl | 2 +- members/member-flags.pl | 2 +- members/member-password.pl | 2 +- members/moremember.pl | 2 +- members/notices.pl | 2 +- members/patronimage.pl | 18 ++++---- members/pay.pl | 2 +- members/paycollect.pl | 2 +- members/printfeercpt.pl | 2 +- members/printinvoice.pl | 2 +- members/readingrec.pl | 2 +- members/routing-lists.pl | 2 +- members/statistics.pl | 2 +- opac/opac-memberentry.pl | 2 +- opac/opac-patron-image.pl | 3 +- opac/sco/sco-main.pl | 2 +- opac/sco/sco-patron-image.pl | 4 +- patroncards/create-pdf.pl | 2 +- tools/picture-upload.pl | 2 +- tools/viewlog.pl | 2 +- 30 files changed, 98 insertions(+), 53 deletions(-) diff --git a/C4/Members.pm b/C4/Members.pm index 7ac69df..1598893 100644 --- a/C4/Members.pm +++ b/C4/Members.pm @@ -1944,19 +1944,19 @@ sub GetTitles { =head2 GetPatronImage - my ($imagedata, $dberror) = GetPatronImage($cardnumber); + my ($imagedata, $dberror) = GetPatronImage($borrowernumber); -Returns the mimetype and binary image data of the image for the patron with the supplied cardnumber. +Returns the mimetype and binary image data of the image for the patron with the supplied borrowernumber. =cut sub GetPatronImage { - my ($cardnumber) = @_; - warn "Cardnumber passed to GetPatronImage is $cardnumber" if $debug; + my ($borrowernumber) = @_; + warn "Borrowernumber passed to GetPatronImage is $borrowernumber" if $debug; my $dbh = C4::Context->dbh; - my $query = 'SELECT mimetype, imagefile FROM patronimage WHERE cardnumber = ?'; + my $query = 'SELECT mimetype, imagefile FROM patronimage WHERE borrowernumber = ?'; my $sth = $dbh->prepare($query); - $sth->execute($cardnumber); + $sth->execute($borrowernumber); my $imagedata = $sth->fetchrow_hashref; warn "Database error!" if $sth->errstr; return $imagedata, $sth->errstr; @@ -1975,7 +1975,7 @@ sub PutPatronImage { my ($cardnumber, $mimetype, $imgfile) = @_; warn "Parameters passed in: Cardnumber=$cardnumber, Mimetype=$mimetype, " . ($imgfile ? "Imagefile" : "No Imagefile") if $debug; my $dbh = C4::Context->dbh; - my $query = "INSERT INTO patronimage (cardnumber, mimetype, imagefile) VALUES (?,?,?) ON DUPLICATE KEY UPDATE imagefile = ?;"; + my $query = "INSERT INTO patronimage (borrowernumber, mimetype, imagefile) VALUES ( ( SELECT borrowernumber from borrowers WHERE cardnumber = ? ),?,?) ON DUPLICATE KEY UPDATE imagefile = ?;"; my $sth = $dbh->prepare($query); $sth->execute($cardnumber,$mimetype,$imgfile,$imgfile); warn "Error returned inserting $cardnumber.$mimetype." if $sth->errstr; @@ -1984,19 +1984,19 @@ sub PutPatronImage { =head2 RmPatronImage - my ($dberror) = RmPatronImage($cardnumber); + my ($dberror) = RmPatronImage($borrowernumber); -Removes the image for the patron with the supplied cardnumber. +Removes the image for the patron with the supplied borrowernumber. =cut sub RmPatronImage { - my ($cardnumber) = @_; - warn "Cardnumber passed to GetPatronImage is $cardnumber" if $debug; + my ($borrowernumber) = @_; + warn "Borrowernumber passed to GetPatronImage is $borrowernumber" if $debug; my $dbh = C4::Context->dbh; - my $query = "DELETE FROM patronimage WHERE cardnumber = ?;"; + my $query = "DELETE FROM patronimage WHERE borrowernumber = ?;"; my $sth = $dbh->prepare($query); - $sth->execute($cardnumber); + $sth->execute($borrowernumber); my $dberror = $sth->errstr; warn "Database error!" if $sth->errstr; return $dberror; diff --git a/circ/circulation.pl b/circ/circulation.pl index efd533e..cd997b3 100755 --- a/circ/circulation.pl +++ b/circ/circulation.pl @@ -765,7 +765,7 @@ if ($stickyduedate) { $session->param( 'stickyduedate', $duedatespec ); } -my ($picture, $dberror) = GetPatronImage($borrower->{'cardnumber'}); +my ($picture, $dberror) = GetPatronImage($borrower->{'borrowernumber'}); $template->param( picture => 1 ) if $picture; # get authorised values with type of BOR_NOTES diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 7628983..e7af046 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -1660,11 +1660,11 @@ CREATE TABLE `patroncards` ( DROP TABLE IF EXISTS `patronimage`; CREATE TABLE `patronimage` ( -- information related to patron images - `cardnumber` varchar(16) NOT NULL, -- the cardnumber of the patron this image is attached to (borrowers.cardnumber) + `borrowernumber` int(11) NOT NULL, -- the borrowernumber of the patron this image is attached to (borrowers.borrowernumber) `mimetype` varchar(15) NOT NULL, -- the format of the image (png, jpg, etc) `imagefile` mediumblob NOT NULL, -- the image - PRIMARY KEY (`cardnumber`), - CONSTRAINT `patronimage_fk1` FOREIGN KEY (`cardnumber`) REFERENCES `borrowers` (`cardnumber`) ON DELETE CASCADE ON UPDATE CASCADE + PRIMARY KEY (`borrowernumber`), + CONSTRAINT `patronimage_fk1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- Table structure for table `pending_offline_operations` diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index 74aeb3e..bceda86 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -7067,6 +7067,52 @@ if ( CheckVersion($DBversion) ) { SetVersion($DBversion); } +$DBversion = "3.13.00.XXX"; +if ( CheckVersion($DBversion) ) { + $dbh->do(qq{ + DELETE FROM patronimage WHERE cardnumber NOT IN ( SELECT cardnumber FROM borrowers ) + }); + + $dbh->do(qq{ + ALTER TABLE patronimage ADD borrowernumber INT( 11 ) NULL FIRST + }); + + $dbh->{AutoCommit} = 0; + $dbh->{RaiseError} = 1; + + eval { + $dbh->do(qq{ + UPDATE patronimage LEFT JOIN borrowers USING ( cardnumber ) SET patronimage.borrowernumber = borrowers.borrowernumber + }); + $dbh->commit(); + }; + + if ($@) { + print "Upgrade to $DBversion done (Bug 10636 - patronimage should have borrowernumber as PK, not cardnumber) failed! Transaction aborted because $@\n"; + eval { $dbh->rollback }; + } + else { + $dbh->do(qq{ + ALTER TABLE patronimage DROP FOREIGN KEY patronimage_fk1 + }); + $dbh->do(qq{ + ALTER TABLE patronimage DROP PRIMARY KEY, ADD PRIMARY KEY( borrowernumber ) + }); + $dbh->do(qq{ + ALTER TABLE patronimage DROP cardnumber + }); + $dbh->do(qq{ + ALTER TABLE patronimage ADD FOREIGN KEY ( borrowernumber ) REFERENCES borrowers ( borrowernumber ) ON DELETE CASCADE ON UPDATE CASCADE + }); + + print "Upgrade to $DBversion done (Bug 10636 - patronimage should have borrowernumber as PK, not cardnumber)\n"; + SetVersion($DBversion); + } + + $dbh->{AutoCommit} = 1; + $dbh->{RaiseError} = 0; +} + =head1 FUNCTIONS =head2 TableExists($table) diff --git a/koha-tmpl/intranet-tmpl/prog/en/includes/circ-menu.inc b/koha-tmpl/intranet-tmpl/prog/en/includes/circ-menu.inc index 4632467..62db1c5 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/includes/circ-menu.inc +++ b/koha-tmpl/intranet-tmpl/prog/en/includes/circ-menu.inc @@ -7,7 +7,7 @@