Bugzilla – Attachment 29224 Details for
Bug 10402
Add multiple contacts for vendors
Home
|
New
|
Browse
|
Search
|
[?]
|
Reports
|
Help
|
New Account
|
Log In
[x]
|
Forgot Password
Login:
[x]
[patch]
Bug 10402: Move contacts to separate table
Bug-10402-Move-contacts-to-separate-table.patch (text/plain), 10.56 KB, created by
Jared Camins-Esakov
on 2014-06-25 15:30:59 UTC
(
hide
)
Description:
Bug 10402: Move contacts to separate table
Filename:
MIME Type:
Creator:
Jared Camins-Esakov
Created:
2014-06-25 15:30:59 UTC
Size:
10.56 KB
patch
obsolete
>From 5758bc1c88298a4287f567e23805068545489d37 Mon Sep 17 00:00:00 2001 >From: Jared Camins-Esakov <jcamins@cpbibliography.com> >Date: Wed, 12 Jun 2013 09:25:20 -0400 >Subject: [PATCH] Bug 10402: Move contacts to separate table > >This patch normalizes the data structures used for bookseller >contacts. > >To test: >1) Repeat tests described on previous patch. > >Signed-off-by: Owen Leonard <oleonard@myacpl.org> >Signed-off-by: Jared Camins-Esakov <jcamins@cpbibliography.com> >--- > C4/Bookseller.pm | 18 ++++++-- > C4/Bookseller/Contact.pm | 77 +++++++++++++++++++--------------- > installer/data/mysql/kohastructure.sql | 28 +++++++++---- > installer/data/mysql/updatedatabase.pl | 30 +++++++++++++ > 4 files changed, 108 insertions(+), 45 deletions(-) > >diff --git a/C4/Bookseller.pm b/C4/Bookseller.pm >index 768a8c3..6ceb606 100644 >--- a/C4/Bookseller.pm >+++ b/C4/Bookseller.pm >@@ -260,12 +260,22 @@ sub ModBookseller { > $data->{'id'} > ); > $contacts ||= $data->{'contacts'}; >+ my $contactquery = "DELETE FROM aqcontacts WHERE booksellerid = ?"; >+ my @contactparams = ($data->{'id'}); > if ($contacts) { >- $contacts->[0] = C4::Bookseller::Contact->new( $contacts->[0] ) >- unless ref $contacts->[0] eq 'C4::Bookseller::Contact'; >- $contacts->[0]->bookseller($data->{'id'}); >- $contacts->[0]->save(); >+ foreach my $contact (@$contacts) { >+ $contact = C4::Bookseller::Contact->new( $contact ) >+ unless ref $contacts->[0] eq 'C4::Bookseller::Contact'; >+ $contact->bookseller($data->{'id'}); >+ $contact->save(); >+ push @contactparams, $contact->id if $contact->id; >+ } >+ if ($#contactparams > 0) { >+ $contactquery .= ' AND id NOT IN (' . ('?, ' x ($#contactparams - 1)) . '?);'; >+ } > } >+ $sth = $dbh->prepare($contactquery); >+ $sth->execute(@contactparams); > return; > } > >diff --git a/C4/Bookseller/Contact.pm b/C4/Bookseller/Contact.pm >index e08d66d..09ec8da 100644 >--- a/C4/Bookseller/Contact.pm >+++ b/C4/Bookseller/Contact.pm >@@ -63,10 +63,10 @@ Contact's e-mail address. > > Notes about contact. > >-=item primary >+=item rank > >-Flag to indicate whether a contact is "primary" or not. Initially unused since >-each bookseller can have only one contact. >+Ranking of the contact so that the contact can be given the correct >+priority in display. > > =item bookseller > >@@ -81,7 +81,7 @@ use C4::Context; > > use base qw(Class::Accessor); > >-__PACKAGE__->mk_accessors(qw(id name position phone altphone fax email notes primary bookseller)); >+__PACKAGE__->mk_accessors(qw(id name position phone altphone fax email notes rank bookseller)); > > =head1 METHODS > >@@ -90,7 +90,8 @@ __PACKAGE__->mk_accessors(qw(id name position phone altphone fax email notes pri > my @contacts = @{C4::Bookseller::Contact->get_from_bookseller($booksellerid)}; > > Returns a reference to an array of C4::Bookseller::Contact objects for the >-specified bookseller. >+specified bookseller. This will always return at least one item, though that one >+item may be an empty contact. > > =cut > >@@ -100,16 +101,16 @@ sub get_from_bookseller { > return unless $bookseller; > > my @contacts; >- my $query = "SELECT contact AS name, contpos AS position, contphone AS phone, contaltphone AS altphone, contfax AS fax, contemail AS email, contnotes AS notes, id AS bookseller FROM aqbooksellers WHERE id = ?"; >- # When we have our own table, we can use: my $query = "SELECT * FROM aqcontacts WHERE bookseller = ?"; >+ my $query = "SELECT * FROM aqcontacts WHERE booksellerid = ?"; > my $dbh = C4::Context->dbh; > my $sth = $dbh->prepare($query); > $sth->execute($bookseller); > while (my $rec = $sth->fetchrow_hashref()) { >- $rec->{'primary'} = 1; > push @contacts, $class->new($rec); > } > >+ push @contacts, $class->new() unless @contacts; >+ > return \@contacts; > } > >@@ -123,21 +124,21 @@ because there is no separate table from which contacts can be fetched. > > =cut > >-#sub fetch { >-# my ($class, $id) = @_; >-# >-# my $rec = { }; >-# if ($id) { >-# my $query = "SELECT * FROM aqcontacts WHERE id = ?"; >-# my $dbh = C4::Context->dbh; >-# my $sth = $dbh->prepare($query); >-# $sth->execute($id); >-# $rec = $sth->fetchrow_hashref(); >-# } >-# my $self = $class->SUPER::new($rec); >-# bless $self, $class; >-# return $self; >-#} >+sub fetch { >+ my ($class, $id) = @_; >+ >+ my $rec = { }; >+ if ($id) { >+ my $query = "SELECT * FROM aqcontacts WHERE id = ?"; >+ my $dbh = C4::Context->dbh; >+ my $sth = $dbh->prepare($query); >+ $sth->execute($id); >+ $rec = $sth->fetchrow_hashref(); >+ } >+ my $self = $class->new($rec); >+ bless $self, $class; >+ return $self; >+} > > =head2 save > >@@ -151,21 +152,29 @@ sub save { > my ($self) = @_; > > my $query; >-# if ($self->id) { >-# $query = 'UPDATE aqcontacts SET name = ?, position = ?, phone = ?, altphone = ?, fax = ?, email = ?, notes = ?, primary = ? WHERE id = ?;'; >-# } else { >-# $query = 'INSERT INTO aqcontacts (name, position, phone, altphone, fax, email, notes, primary) VALUES (?, ?, ?, ?, ? ,? ,?, ?);'; >-# } >- if ($self->bookseller) { >- $query = 'UPDATE aqbooksellers SET contact = ?, contpos = ?, contphone = ?, contaltphone = ?, contfax = ?, contemail = ?, contnotes = ? WHERE id = ?;'; >+ my @params = ($self->name, $self->position, $self->phone, $self->altphone, $self->fax, $self->email, $self->notes, $self->rank, $self->bookseller); >+ if ($self->id) { >+ $query = 'UPDATE aqcontacts SET name = ?, position = ?, phone = ?, altphone = ?, fax = ?, email = ?, notes = ?, rank = ?, booksellerid = ? WHERE id = ?;'; >+ push @params, $self->id; > } else { >- return; >+ $query = 'INSERT INTO aqcontacts (name, position, phone, altphone, fax, email, notes, rank, booksellerid) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?);'; > } > my $dbh = C4::Context->dbh; > my $sth = $dbh->prepare($query); >- $sth->execute($self->name, $self->position, $self->phone, $self->altphone, $self->fax, $self->email, $self->notes, $self->bookseller); >- #$self->id = $dbh->last_insert_id(undef, undef, 'aqcontacts', undef); >- return $self->bookseller; >+ $sth->execute(@params); >+ $self->id($dbh->{'mysql_insertid'}) unless $self->id; >+ return $self->id; >+} >+ >+sub delete { >+ my ($self) = @_; >+ >+ return unless $self->id; >+ >+ my $dbh = C4::Context->dbh; >+ my $sth = $dbh->prepare("DELETE FROM aqcontacts WHERE id = ?;"); >+ $sth->execute($self->id); >+ return; > } > > =head1 AUTHOR >diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql >index b46dc6a..19b351e 100644 >--- a/installer/data/mysql/kohastructure.sql >+++ b/installer/data/mysql/kohastructure.sql >@@ -2864,15 +2864,8 @@ CREATE TABLE `aqbooksellers` ( -- information about the vendors listed in acquis > `notes` mediumtext, -- order notes > `bookselleremail` mediumtext, -- vendor email > `booksellerurl` mediumtext, -- unused in Koha >- `contact` varchar(100) default NULL, -- name of contact at vendor > `postal` mediumtext, -- vendor postal address (all lines) > `url` varchar(255) default NULL, -- vendor web address >- `contpos` varchar(100) default NULL, -- contact person's position >- `contphone` varchar(100) default NULL, -- contact's phone number >- `contfax` varchar(100) default NULL, -- contact's fax number >- `contaltphone` varchar(100) default NULL, -- contact's alternate phone number >- `contemail` varchar(100) default NULL, -- contact's email address >- `contnotes` mediumtext, -- notes related to the contact > `active` tinyint(4) default NULL, -- is this vendor active (1 for yes, 0 for no) > `listprice` varchar(10) default NULL, -- currency code for list prices > `invoiceprice` varchar(10) default NULL, -- currency code for invoice prices >@@ -2968,6 +2961,27 @@ CREATE TABLE `aqbudgets_planning` ( > ) ENGINE=InnoDB DEFAULT CHARSET=utf8; > > -- >+-- Table structure for table 'aqcontacts' >+-- >+ >+DROP TABLE IF EXISTS aqcontacts; >+CREATE TABLE aqcontacts ( >+ id int(11) NOT NULL auto_increment, -- primary key and unique number assigned by Koha >+ name varchar(100) default NULL, -- name of contact at vendor >+ position varchar(100) default NULL, -- contact person's position >+ phone varchar(100) default NULL, -- contact's phone number >+ altphone varchar(100) default NULL, -- contact's alternate phone number >+ fax varchar(100) default NULL, -- contact's fax number >+ email varchar(100) default NULL, -- contact's email address >+ notes mediumtext, -- notes related to the contact >+ rank SMALLINT default 0, -- display rank for the contact >+ booksellerid int(11) not NULL, >+ PRIMARY KEY (id), >+ CONSTRAINT booksellerid_fk2 FOREIGN KEY (booksellerid) >+ REFERENCES aqbooksellers (id) ON DELETE CASCADE ON UPDATE CASCADE >+) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; >+ >+-- > -- Table structure for table 'aqcontract' > -- > >diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl >index c68ba51..ac5ad65 100755 >--- a/installer/data/mysql/updatedatabase.pl >+++ b/installer/data/mysql/updatedatabase.pl >@@ -8560,6 +8560,36 @@ if ( CheckVersion($DBversion) ) { > SetVersion($DBversion); > } > >+$DBversion = "3.13.00.XXX"; >+if ( CheckVersion($DBversion) ) { >+ $dbh->do("CREATE TABLE aqcontacts ( >+ id int(11) NOT NULL auto_increment, >+ name varchar(100) default NULL, >+ position varchar(100) default NULL, >+ phone varchar(100) default NULL, >+ altphone varchar(100) default NULL, >+ fax varchar(100) default NULL, >+ email varchar(100) default NULL, >+ notes mediumtext, >+ rank SMALLINT default 0, >+ booksellerid int(11) not NULL, >+ PRIMARY KEY (id), >+ CONSTRAINT booksellerid_fk2 FOREIGN KEY (booksellerid) >+ REFERENCES aqbooksellers (id) ON DELETE CASCADE ON UPDATE CASCADE >+ ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;"); >+ $dbh->do("INSERT INTO aqcontacts (name, position, phone, altphone, fax, >+ email, notes, booksellerid) >+ SELECT contact, contpos, contphone, contaltphone, contfax, contemail, >+ contnotes, id FROM aqbooksellers;"); >+ $dbh->do("ALTER TABLE aqbooksellers DROP COLUMN contact, >+ DROP COLUMN contpos, DROP COLUMN contphone, >+ DROP COLUMN contaltphone, DROP COLUMN contfax, >+ DROP COLUMN contemail, DROP COLUMN contnotes;"); >+ print "Upgrade to $DBversion done (Bug 10402: Move bookseller contacts to separate table)\n"; >+ SetVersion($DBversion); >+} >+ >+ > =head1 FUNCTIONS > > =head2 TableExists($table) >-- >1.9.1
You cannot view the attachment while viewing its details because your browser does not support IFRAMEs.
View the attachment on a separate page
.
View Attachment As Diff
View Attachment As Raw
Actions:
View
|
Diff
|
Splinter Review
Attachments on
bug 10402
:
18973
|
18974
|
18975
|
19233
|
19234
|
19235
|
19603
|
19604
|
19808
|
19809
|
19810
|
20019
|
20020
|
20021
|
20045
|
20046
|
20047
|
20048
|
20559
|
20560
|
20561
|
20562
|
21019
|
21020
|
21021
|
21022
|
21023
|
21056
|
21547
|
21549
|
21804
|
23370
|
23371
|
23372
|
23373
|
23374
|
23375
|
23376
|
23377
|
24561
|
24562
|
24563
|
24564
|
24565
|
24566
|
24567
|
24568
|
29223
|
29224
|
29225
|
29226
|
29227
|
29511
|
29600
|
29601
|
29602
|
29603
|
29604
|
29761
|
30209
|
30210
|
30211
|
30212
|
30213
|
30460
|
30461
|
30462
|
30463
|
30464
|
31091
|
31092
|
31093
|
31094
|
31095
|
31096