package C4::Bookseller;
# Copyright 2000-2002 Katipo Communications
+# Copyright 2010 PTFS Europe
#
# This file is part of Koha.
#
# 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., 59 Temple Place,
-# Suite 330, Boston, MA 02111-1307 USA
-
-# $Id$
+# 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.
use strict;
+use warnings;
-use vars qw($VERSION @ISA @EXPORT);
+use base qw( Exporter );
# set the version for version checking
-$VERSION = do { my @v = '$Revision$' =~ /\d+/g; shift(@v) . "." . join( "_", map { sprintf "%03d", $_ } @v ); };
-
-@ISA = qw(Exporter);
-@EXPORT = qw(
- &GetBookSeller &GetBooksellersWithLateOrders
- &ModBookseller
- &AddBookseller
+our $VERSION = 3.07.00.049;
+our @EXPORT_OK = qw(
+ GetBookSeller GetBooksellersWithLateOrders GetBookSellerFromId
+ ModBookseller
+ DelBookseller
+ AddBookseller
);
-
=head1 NAME
C4::Bookseller - Koha functions for dealing with booksellers.
=head1 FUNCTIONS
-=cut
-
-#-------------------------------------------------------------------#
-
=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.
-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
sub GetBookSeller {
- my ($searchstring) = @_;
- my $dbh = C4::Context->dbh;
+ my $searchstring = shift;
+ $searchstring = q{%} . $searchstring . q{%};
my $query = "
- SELECT *
- FROM aqbooksellers
- WHERE name LIKE ? OR id = ?
+ 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 $sth =$dbh->prepare($query);
- $sth->execute("$searchstring%", $searchstring );
- my @results;
- while ( my $data = $sth->fetchrow_hashref ) {
- push( @results, $data );
- }
- $sth->finish;
- return @results ;
+
+ my $dbh = C4::Context->dbh;
+ my $sth = $dbh->prepare($query);
+ $sth->execute($searchstring);
+ my $resultset_ref = $sth->fetchall_arrayref( {} );
+ return @{$resultset_ref};
}
+sub GetBookSellerFromId {
+ my $id = shift or return;
+ my $dbh = C4::Context->dbh;
+ 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 );
+ }
+ return $vendor;
+}
#-----------------------------------------------------------------#
=head2 GetBooksellersWithLateOrders
-%results = &GetBooksellersWithLateOrders;
+%results = GetBooksellersWithLateOrders($delay);
Searches for suppliers with late orders.
=cut
sub GetBooksellersWithLateOrders {
- my ($delay,$branch) = @_;
- my $dbh = C4::Context->dbh;
+ my ( $delay, $branch, $estimateddeliverydatefrom, $estimateddeliverydateto ) = @_; # FIXME: Branch argument unused.
+ 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_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))
- ";
+ $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
+ ( datereceived = ''
+ OR datereceived IS NULL
+ OR aqorders.quantityreceived < aqorders.quantity
+ )
+ AND aqorders.rrp <> 0
+ AND aqorders.ecost <> 0
+ AND aqorders.quantity - IFNULL(aqorders.quantityreceived,0) <> 0
+ AND aqbasket.closedate IS NOT NULL
+ ";
+ if ( defined $delay ) {
+ $strsth .= " AND (closedate <= DATE_SUB(CAST(now() AS date),INTERVAL ? DAY)) ";
+ push @query_params, $delay;
+ }
+ if ( defined $estimateddeliverydatefrom ) {
+ $strsth .= '
+ AND aqbooksellers.deliverytime IS NOT NULL
+ AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) >= ?';
+ push @query_params, $estimateddeliverydatefrom;
}
- 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 $estimateddeliverydatefrom and defined $estimateddeliverydateto ) {
+ $strsth .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= ?';
+ push @query_params, $estimateddeliverydateto;
+ } elsif ( defined $estimateddeliverydatefrom ) {
+ $strsth .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= CAST(now() AS date)';
}
my $sth = $dbh->prepare($strsth);
- $sth->execute;
+ $sth->execute( @query_params );
my %supplierlist;
while ( my ( $id, $name ) = $sth->fetchrow ) {
$supplierlist{$id} = $name;
sub AddBookseller {
my ($data) = @_;
- my $dbh = C4::Context->dbh;
- my $query = "
+ my $dbh = C4::Context->dbh;
+ my $query = q|
INSERT INTO aqbooksellers
(
name, address1, address2, address3, address4,
- postal, phone, fax, url, contact,
+ postal, phone, accountnumber, fax, url,
+ contact,
contpos, contphone, contfax, contaltphone, contemail,
contnotes, active, listprice, invoiceprice, gstreg,
- listincgst,invoiceincgst, specialty, discount, invoicedisc,
- nocalc, notes
+ listincgst,invoiceincgst, gstrate, discount,
+ notes
)
- 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->{'phone'}, $data->{'accountnumber'},
+ $data->{'fax'},
$data->{'url'}, $data->{'contact'},
$data->{'contpos'}, $data->{'contphone'},
$data->{'contfax'}, $data->{'contaltphone'},
$data->{'active'}, $data->{'listprice'},
$data->{'invoiceprice'}, $data->{'gstreg'},
$data->{'listincgst'}, $data->{'invoiceincgst'},
- $data->{'specialty'}, $data->{'discount'},
- $data->{'invoicedisc'}, $data->{'nocalc'},
- $data->{'notes'}
+ $data->{'gstrate'},
+ $data->{'discount'}, $data->{'notes'}
);
# return the id of this new supplier
- $query = "
- SELECT max(id)
- FROM aqbooksellers
- ";
- $sth = $dbh->prepare($query);
- $sth->execute;
- return scalar($sth->fetchrow);
+ return $dbh->{'mysql_insertid'};
}
#-----------------------------------------------------------------#
-=head2 ModSupplier
+=head2 ModBookseller
-&ModSupplier($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<&booksellers>, modify what's necessary, then call
-C<&ModSupplier> with the result.
+book seller with C<&GetBookseller>, modify what's necessary, then call
+C<&ModBookseller> with the result.
=cut
sub ModBookseller {
my ($data) = @_;
my $dbh = C4::Context->dbh;
- my $query = "
- UPDATE aqbooksellers
+ my $query = 'UPDATE aqbooksellers
SET name=?,address1=?,address2=?,address3=?,address4=?,
- postal=?,phone=?,fax=?,url=?,contact=?,contpos=?,
+ postal=?,phone=?,accountnumber=?,fax=?,url=?,contact=?,contpos=?,
contphone=?,contfax=?,contaltphone=?,contemail=?,
contnotes=?,active=?,listprice=?, invoiceprice=?,
- gstreg=?, listincgst=?,invoiceincgst=?,
- specialty=?,discount=?,invoicedisc=?,nocalc=?, notes=?
- WHERE id=?
- ";
- my $sth = $dbh->prepare($query);
+ gstreg=?,listincgst=?,invoiceincgst=?,
+ discount=?,notes=?,gstrate=?,deliverytime=?
+ WHERE id=?';
+ my $sth = $dbh->prepare($query);
$sth->execute(
$data->{'name'}, $data->{'address1'},
$data->{'address2'}, $data->{'address3'},
$data->{'address4'}, $data->{'postal'},
- $data->{'phone'}, $data->{'fax'},
+ $data->{'phone'}, $data->{'accountnumber'},
+ $data->{'fax'},
$data->{'url'}, $data->{'contact'},
$data->{'contpos'}, $data->{'contphone'},
$data->{'contfax'}, $data->{'contaltphone'},
$data->{'active'}, $data->{'listprice'},
$data->{'invoiceprice'}, $data->{'gstreg'},
$data->{'listincgst'}, $data->{'invoiceincgst'},
- $data->{'specialty'}, $data->{'discount'},
- $data->{'invoicedisc'}, $data->{'nocalc'},
- $data->{'notes'}, $data->{'id'}
+ $data->{'discount'}, $data->{'notes'},
+ $data->{'gstrate'},
+ $data->{deliverytime},
+ $data->{'id'}
);
- $sth->finish;
+ return;
}
-END { } # module clean-up code here (global destructor)
+=head2 DelBookseller
+
+DelBookseller($booksellerid);
+
+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 = shift;
+ my $dbh = C4::Context->dbh;
+ my $sth = $dbh->prepare('DELETE FROM aqbooksellers WHERE id=?');
+ $sth->execute($id);
+ return;
+}
1;
=head1 AUTHOR
-Koha Developement team <info@koha.org>
+Koha Development Team <http://koha-community.org/>
=cut
projects / koha.git / blobdiff