X-Git-Url: http://git.rot13.org/?a=blobdiff_plain;f=C4%2FBookseller.pm;h=702b37c5349510ad91f330ff8d7db3df0c679477;hb=532b41934c7a573727a4700ed0975a2426d955bb;hp=21ad34848d4cc7b2140aa43d1f72f6db84fe5571;hpb=c0441ebbfb547507cda86c8c05575d88761045e1;p=koha.git diff --git a/C4/Bookseller.pm b/C4/Bookseller.pm index 21ad34848d..702b37c534 100644 --- a/C4/Bookseller.pm +++ b/C4/Bookseller.pm @@ -1,6 +1,7 @@ package C4::Bookseller; # Copyright 2000-2002 Katipo Communications +# Copyright 2010 PTFS Europe # # This file is part of Koha. # @@ -18,23 +19,20 @@ package C4::Bookseller; # 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA. use strict; -#use warnings; FIXME - Bug 2505 - -use vars qw($VERSION @ISA @EXPORT); - -BEGIN { - # set the version for version checking - $VERSION = 3.01; - require Exporter; - @ISA = qw(Exporter); - @EXPORT = qw( - &GetBookSeller &GetBooksellersWithLateOrders &GetBookSellerFromId - &ModBookseller - &DelBookseller - &AddBookseller - ); -} +use warnings; + +use base qw( Exporter ); +use C4::Bookseller::Contact; + +# set the version for version checking +our $VERSION = 3.07.00.049; +our @EXPORT_OK = qw( + GetBookSeller GetBooksellersWithLateOrders GetBookSellerFromId + ModBookseller + DelBookseller + AddBookseller +); =head1 NAME @@ -54,93 +52,110 @@ a bookseller. =head2 GetBookSeller -@results = &GetBookSeller($searchstring); +@results = GetBookSeller($searchstring); -Looks up a book seller. C<$searchstring> may be either a book seller -ID, or a string to look for in the book seller's name. +Looks up a book seller. C<$searchstring> is a string to look for in the +book seller's name. -C<@results> is an array of references-to-hash, whose keys are the fields of of the +C<@results> is an array of hash_refs whose keys are the fields of of the aqbooksellers table in the Koha database. =cut -# FIXME: This function is badly named. It should be something like -# SearchBookSellersByName. It is NOT a singular return value. +sub GetBookSeller { + my $searchstring = shift; + $searchstring = q{%} . $searchstring . q{%}; + my $query = " + SELECT aqbooksellers.*, count(*) AS basketcount + FROM aqbooksellers + LEFT JOIN aqbasket ON aqbasket.booksellerid = aqbooksellers.id + WHERE name LIKE ? GROUP BY aqbooksellers.id ORDER BY name + "; + + my $dbh = C4::Context->dbh; + my $sth = $dbh->prepare($query); + $sth->execute($searchstring); + my $resultset_ref = $sth->fetchall_arrayref( {} ); + return @{$resultset_ref}; +} -sub GetBookSeller($) { - my ($searchstring) = @_; +sub GetBookSellerFromId { + my $id = shift or return; my $dbh = C4::Context->dbh; - my $query = "SELECT * FROM aqbooksellers WHERE name LIKE ?"; - my $sth =$dbh->prepare($query); - $sth->execute( "%$searchstring%" ); - my @results; - # count how many baskets this bookseller has. - # if it has none, the bookseller can be deleted - my $sth2 = $dbh->prepare("SELECT count(*) FROM aqbasket WHERE booksellerid=?"); - while ( my $data = $sth->fetchrow_hashref ) { - $sth2->execute($data->{id}); - $data->{basketcount} = $sth2->fetchrow(); - push( @results, $data ); + my $vendor = + $dbh->selectrow_hashref( 'SELECT * FROM aqbooksellers WHERE id = ?', + {}, $id ); + if ($vendor) { + ( $vendor->{basketcount} ) = $dbh->selectrow_array( + 'SELECT count(*) FROM aqbasket where booksellerid = ?', + {}, $id ); + ( $vendor->{subscriptioncount} ) = $dbh->selectrow_array( + 'SELECT count(*) FROM subscription WHERE aqbooksellerid = ?', + {}, $id ); + $vendor->{'contacts'} = C4::Bookseller::Contact->get_from_bookseller($id); } - $sth->finish; - return @results ; + return $vendor; } - -sub GetBookSellerFromId($) { - my $id = shift or return; - my $dbh = C4::Context->dbh(); - my $query = "SELECT * FROM aqbooksellers WHERE id = ?"; - my $sth =$dbh->prepare($query); - $sth->execute( $id ); - if (my $data = $sth->fetchrow_hashref()){ - my $sth2 = $dbh->prepare("SELECT count(*) FROM aqbasket WHERE booksellerid=?"); - $sth2->execute($id); - $data->{basketcount}=$sth2->fetchrow(); - return $data; - } - return; -} #-----------------------------------------------------------------# =head2 GetBooksellersWithLateOrders -%results = &GetBooksellersWithLateOrders; +%results = GetBooksellersWithLateOrders( $delay, $estimateddeliverydatefrom, $estimateddeliverydateto ); Searches for suppliers with late orders. =cut sub GetBooksellersWithLateOrders { - my ($delay,$branch) = @_; # FIXME: Branch argument unused. - my $dbh = C4::Context->dbh; + my ( $delay, $estimateddeliverydatefrom, $estimateddeliverydateto ) = @_; + my $dbh = C4::Context->dbh; -# FIXME NOT quite sure that this operation is valid for DBMs different from Mysql, HOPING so -# should be tested with other DBMs + # FIXME NOT quite sure that this operation is valid for DBMs different from Mysql, HOPING so + # should be tested with other DBMs - my $strsth; + my $query; + my @query_params = (); my $dbdriver = C4::Context->config("db_scheme") || "mysql"; - if ( $dbdriver eq "mysql" ) { - $strsth = " - SELECT DISTINCT aqbasket.booksellerid, aqbooksellers.name - FROM aqorders LEFT JOIN aqbasket ON aqorders.basketno=aqbasket.basketno - LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id - WHERE (closedate < DATE_SUB(CURDATE( ),INTERVAL $delay DAY) - AND (datereceived = '' OR datereceived IS NULL)) - "; + $query = " + SELECT DISTINCT aqbasket.booksellerid, aqbooksellers.name + FROM aqorders LEFT JOIN aqbasket ON aqorders.basketno=aqbasket.basketno + LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id + WHERE + ( datereceived = '' + OR datereceived IS NULL + OR aqorders.quantityreceived < aqorders.quantity + ) + AND aqorders.rrp <> 0 + AND aqorders.ecost <> 0 + AND aqorders.quantity - COALESCE(aqorders.quantityreceived,0) <> 0 + AND aqbasket.closedate IS NOT NULL + "; + if ( defined $delay && $delay >= 0 ) { + $query .= " AND (closedate <= DATE_SUB(CAST(now() AS date),INTERVAL ? + COALESCE(aqbooksellers.deliverytime,0) DAY)) "; + push @query_params, $delay; + } elsif ( $delay && $delay < 0 ){ + warn 'WARNING: GetBooksellerWithLateOrders is called with a negative value'; + return; + } + if ( defined $estimateddeliverydatefrom ) { + $query .= ' + AND ADDDATE(aqbasket.closedate, INTERVAL COALESCE(aqbooksellers.deliverytime,0) DAY) >= ?'; + push @query_params, $estimateddeliverydatefrom; + if ( defined $estimateddeliverydateto ) { + $query .= ' AND ADDDATE(aqbasket.closedate, INTERVAL COALESCE(aqbooksellers.deliverytime, 0) DAY) <= ?'; + push @query_params, $estimateddeliverydateto; + } else { + $query .= ' AND ADDDATE(aqbasket.closedate, INTERVAL COALESCE(aqbooksellers.deliverytime, 0) DAY) <= CAST(now() AS date)'; + } } - else { - $strsth = " - SELECT DISTINCT aqbasket.booksellerid, aqbooksellers.name - FROM aqorders LEFT JOIN aqbasket ON aqorders.basketno=aqbasket.basketno - LEFT JOIN aqbooksellers ON aqbasket.aqbooksellerid = aqbooksellers.id - WHERE (closedate < (CURDATE( )-(INTERVAL $delay DAY))) - AND (datereceived = '' OR datereceived IS NULL)) - "; + if ( defined $estimateddeliverydateto ) { + $query .= ' AND ADDDATE(aqbasket.closedate, INTERVAL COALESCE(aqbooksellers.deliverytime,0) DAY) <= ?'; + push @query_params, $estimateddeliverydateto; } - my $sth = $dbh->prepare($strsth); - $sth->execute; + my $sth = $dbh->prepare($query); + $sth->execute( @query_params ); my %supplierlist; while ( my ( $id, $name ) = $sth->fetchrow ) { $supplierlist{$id} = $name; @@ -164,53 +179,51 @@ Returns the ID of the newly-created bookseller. =cut sub AddBookseller { - my ($data) = @_; - my $dbh = C4::Context->dbh; - my $query = " + my ($data, $contacts) = @_; + my $dbh = C4::Context->dbh; + my $query = q| INSERT INTO aqbooksellers ( - name, address1, address2, address3, address4, - postal, phone, fax, url, contact, - contpos, contphone, contfax, contaltphone, contemail, - contnotes, active, listprice, invoiceprice, gstreg, - listincgst,invoiceincgst, gstrate, discount, - notes + name, address1, address2, address3, address4, + postal, phone, accountnumber,fax, url, + active, listprice, invoiceprice, gstreg, + listincgst,invoiceincgst, gstrate, discount, notes, + deliverytime ) - VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) - "; + VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) | + ; my $sth = $dbh->prepare($query); $sth->execute( $data->{'name'}, $data->{'address1'}, $data->{'address2'}, $data->{'address3'}, $data->{'address4'}, $data->{'postal'}, - $data->{'phone'}, $data->{'fax'}, - $data->{'url'}, $data->{'contact'}, - $data->{'contpos'}, $data->{'contphone'}, - $data->{'contfax'}, $data->{'contaltphone'}, - $data->{'contemail'}, $data->{'contnotes'}, + $data->{'phone'}, $data->{'accountnumber'}, + $data->{'fax'}, $data->{'url'}, $data->{'active'}, $data->{'listprice'}, $data->{'invoiceprice'}, $data->{'gstreg'}, $data->{'listincgst'}, $data->{'invoiceincgst'}, - $data->{'gstrate'}, - $data->{'discount'}, $data->{'notes'} + $data->{'gstrate'}, $data->{'discount'}, + $data->{notes}, $data->{deliverytime}, ); # return the id of this new supplier - # FIXME: no protection against simultaneous addition: max(id) might be wrong! - $query = " - SELECT max(id) - FROM aqbooksellers - "; - $sth = $dbh->prepare($query); - $sth->execute; - return scalar($sth->fetchrow); + my $id = $dbh->{'mysql_insertid'}; + if ($id && $contacts) { + foreach my $contact (@$contacts) { + $contact = C4::Bookseller::Contact->new( $contact ) + unless ref $contacts eq 'C4::Bookseller::Contact'; + $contact->bookseller($id); + $contact->save(); + } + } + return $id; } #-----------------------------------------------------------------# =head2 ModBookseller -&ModBookseller($bookseller); +ModBookseller($bookseller); Updates the information for a given bookseller. C<$bookseller> is a reference-to-hash whose keys are the fields of the aqbooksellers table @@ -224,52 +237,64 @@ C<&ModBookseller> with the result. =cut sub ModBookseller { - my ($data) = @_; + my ($data, $contacts) = @_; my $dbh = C4::Context->dbh; - my $query = " - UPDATE aqbooksellers + return unless $data->{'id'}; + my $query = 'UPDATE aqbooksellers SET name=?,address1=?,address2=?,address3=?,address4=?, - postal=?,phone=?,fax=?,url=?,contact=?,contpos=?, - contphone=?,contfax=?,contaltphone=?,contemail=?, - contnotes=?,active=?,listprice=?, invoiceprice=?, + postal=?,phone=?,accountnumber=?,fax=?,url=?, + active=?,listprice=?, invoiceprice=?, gstreg=?,listincgst=?,invoiceincgst=?, - discount=?, notes=?, gstrate=? - WHERE id=? - "; - my $sth = $dbh->prepare($query); - $sth->execute( + discount=?,notes=?,gstrate=?,deliverytime=? + WHERE id=?'; + my $sth = $dbh->prepare($query); + my $cnt = $sth->execute( $data->{'name'}, $data->{'address1'}, $data->{'address2'}, $data->{'address3'}, $data->{'address4'}, $data->{'postal'}, - $data->{'phone'}, $data->{'fax'}, - $data->{'url'}, $data->{'contact'}, - $data->{'contpos'}, $data->{'contphone'}, - $data->{'contfax'}, $data->{'contaltphone'}, - $data->{'contemail'}, $data->{'contnotes'}, + $data->{'phone'}, $data->{'accountnumber'}, + $data->{'fax'}, $data->{'url'}, $data->{'active'}, $data->{'listprice'}, $data->{'invoiceprice'}, $data->{'gstreg'}, $data->{'listincgst'}, $data->{'invoiceincgst'}, - $data->{'discount'}, - $data->{'notes'}, $data->{'gstrate'}, + $data->{'discount'}, $data->{'notes'}, + $data->{'gstrate'}, $data->{deliverytime}, $data->{'id'} ); - $sth->finish; + $contacts ||= $data->{'contacts'}; + my $contactquery = "DELETE FROM aqcontacts WHERE booksellerid = ?"; + my @contactparams = ($data->{'id'}); + if ($contacts) { + foreach my $contact (@$contacts) { + $contact = C4::Bookseller::Contact->new( $contact ) + unless ref $contacts 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 $cnt; } =head2 DelBookseller -&DelBookseller($booksellerid); +DelBookseller($booksellerid); -delete the supplier identified by $booksellerid -This sub can be called only if the supplier has no order. +delete the supplier record identified by $booksellerid +This sub assumes it is called only if the supplier has no order. =cut sub DelBookseller { - my ($id) = @_; - my $dbh=C4::Context->dbh; - my $sth=$dbh->prepare("DELETE FROM aqbooksellers WHERE id=?"); - $sth->execute($id); + my $id = shift; + my $dbh = C4::Context->dbh; + my $sth = $dbh->prepare('DELETE FROM aqbooksellers WHERE id=?'); + return $sth->execute($id); } 1;