package C4::Bookseller;
# Copyright 2000-2002 Katipo Communications
+# Copyright 2010 PTFS Europe
#
# This file is part of Koha.
#
-# Koha 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 2 of the License, or (at your option) any later
-# version.
+# Koha 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.
#
-# Koha 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.
+# Koha 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 Koha; if not, write to the Free Software Foundation, Inc.,
-# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
+# You should have received a copy of the GNU General Public License
+# along with Koha; if not, see <http://www.gnu.org/licenses>.
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;
+our @EXPORT_OK = qw(
+ GetBooksellersWithLateOrders
+ ModBookseller
+ DelBookseller
+ AddBookseller
+);
=head1 NAME
=head1 FUNCTIONS
-=head2 GetBookSeller
-
-@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.
-
-C<@results> is an array of references-to-hash, 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) = @_;
- 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 );
- }
- $sth->finish;
- return @results ;
-}
-
-
-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;
=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, 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->{'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
The entry to modify is determined by C<$bookseller-E<gt>{id}>.
The easiest way to get all of the necessary fields is to look up a
-book seller with C<&GetBookseller>, modify what's necessary, then call
+book seller with C<Koha::Acquisition::Bookseller>, modify what's necessary, then call
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;