1 package C4::Bookseller;
3 # Copyright 2000-2002 Katipo Communications
4 # Copyright 2010 PTFS Europe
6 # This file is part of Koha.
8 # Koha is free software; you can redistribute it and/or modify it under the
9 # terms of the GNU General Public License as published by the Free Software
10 # Foundation; either version 2 of the License, or (at your option) any later
13 # Koha is distributed in the hope that it will be useful, but WITHOUT ANY
14 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
15 # A PARTICULAR PURPOSE. See the GNU General Public License for more details.
17 # You should have received a copy of the GNU General Public License along
18 # with Koha; if not, write to the Free Software Foundation, Inc.,
19 # 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
24 use base qw( Exporter );
26 use C4::Bookseller::Contact;
28 # set the version for version checking
29 our $VERSION = 3.07.00.049;
31 GetBookSeller GetBooksellersWithLateOrders GetBookSellerFromId
39 C4::Bookseller - Koha functions for dealing with booksellers.
47 The functions in this module deal with booksellers. They allow to
48 add a new bookseller, to modify it or to get some informations around
55 @results = GetBookSeller($searchstring);
57 Looks up a book seller. C<$searchstring> is a string to look for in the
60 C<@results> is an array of hash_refs whose keys are the fields of of the
61 aqbooksellers table in the Koha database.
66 my $searchstring = shift;
67 $searchstring = q{%} . $searchstring . q{%};
69 SELECT aqbooksellers.*, count(*) AS basketcount
71 LEFT JOIN aqbasket ON aqbasket.booksellerid = aqbooksellers.id
72 WHERE name LIKE ? GROUP BY aqbooksellers.id ORDER BY name
75 my $dbh = C4::Context->dbh;
76 my $sth = $dbh->prepare($query);
77 $sth->execute($searchstring);
78 my $resultset_ref = $sth->fetchall_arrayref( {} );
79 return @{$resultset_ref};
82 sub GetBookSellerFromId {
83 my $id = shift or return;
84 my $dbh = C4::Context->dbh;
86 $dbh->selectrow_hashref( 'SELECT * FROM aqbooksellers WHERE id = ?',
89 ( $vendor->{basketcount} ) = $dbh->selectrow_array(
90 'SELECT count(*) FROM aqbasket where booksellerid = ?',
92 ( $vendor->{subscriptioncount} ) = $dbh->selectrow_array(
93 'SELECT count(*) FROM subscription WHERE aqbooksellerid = ?',
95 $vendor->{'contacts'} = C4::Bookseller::Contact->get_from_bookseller($id);
100 #-----------------------------------------------------------------#
102 =head2 GetBooksellersWithLateOrders
104 %results = GetBooksellersWithLateOrders( $delay, $estimateddeliverydatefrom, $estimateddeliverydateto );
106 Searches for suppliers with late orders.
110 sub GetBooksellersWithLateOrders {
111 my ( $delay, $estimateddeliverydatefrom, $estimateddeliverydateto ) = @_;
112 my $dbh = C4::Context->dbh;
114 # FIXME NOT quite sure that this operation is valid for DBMs different from Mysql, HOPING so
115 # should be tested with other DBMs
118 my @query_params = ();
119 my $dbdriver = C4::Context->config("db_scheme") || "mysql";
121 SELECT DISTINCT aqbasket.booksellerid, aqbooksellers.name
122 FROM aqorders LEFT JOIN aqbasket ON aqorders.basketno=aqbasket.basketno
123 LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
126 OR datereceived IS NULL
127 OR aqorders.quantityreceived < aqorders.quantity
129 AND aqorders.rrp <> 0
130 AND aqorders.ecost <> 0
131 AND aqorders.quantity - COALESCE(aqorders.quantityreceived,0) <> 0
132 AND aqbasket.closedate IS NOT NULL
134 if ( defined $delay && $delay >= 0 ) {
135 $query .= " AND (closedate <= DATE_SUB(CAST(now() AS date),INTERVAL ? + COALESCE(aqbooksellers.deliverytime,0) DAY)) ";
136 push @query_params, $delay;
137 } elsif ( $delay && $delay < 0 ){
138 warn 'WARNING: GetBooksellerWithLateOrders is called with a negative value';
141 if ( defined $estimateddeliverydatefrom ) {
143 AND ADDDATE(aqbasket.closedate, INTERVAL COALESCE(aqbooksellers.deliverytime,0) DAY) >= ?';
144 push @query_params, $estimateddeliverydatefrom;
145 if ( defined $estimateddeliverydateto ) {
146 $query .= ' AND ADDDATE(aqbasket.closedate, INTERVAL COALESCE(aqbooksellers.deliverytime, 0) DAY) <= ?';
147 push @query_params, $estimateddeliverydateto;
149 $query .= ' AND ADDDATE(aqbasket.closedate, INTERVAL COALESCE(aqbooksellers.deliverytime, 0) DAY) <= CAST(now() AS date)';
152 if ( defined $estimateddeliverydateto ) {
153 $query .= ' AND ADDDATE(aqbasket.closedate, INTERVAL COALESCE(aqbooksellers.deliverytime,0) DAY) <= ?';
154 push @query_params, $estimateddeliverydateto;
157 my $sth = $dbh->prepare($query);
158 $sth->execute( @query_params );
160 while ( my ( $id, $name ) = $sth->fetchrow ) {
161 $supplierlist{$id} = $name;
164 return %supplierlist;
167 #--------------------------------------------------------------------#
171 $id = &AddBookseller($bookseller);
173 Creates a new bookseller. C<$bookseller> is a reference-to-hash whose
174 keys are the fields of the aqbooksellers table in the Koha database.
175 All fields must be present.
177 Returns the ID of the newly-created bookseller.
182 my ($data, $contacts) = @_;
183 my $dbh = C4::Context->dbh;
185 INSERT INTO aqbooksellers
187 name, address1, address2, address3, address4,
188 postal, phone, accountnumber,fax, url,
189 active, listprice, invoiceprice, gstreg,
190 listincgst,invoiceincgst, gstrate, discount, notes,
193 VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) |
195 my $sth = $dbh->prepare($query);
197 $data->{'name'}, $data->{'address1'},
198 $data->{'address2'}, $data->{'address3'},
199 $data->{'address4'}, $data->{'postal'},
200 $data->{'phone'}, $data->{'accountnumber'},
201 $data->{'fax'}, $data->{'url'},
202 $data->{'active'}, $data->{'listprice'},
203 $data->{'invoiceprice'}, $data->{'gstreg'},
204 $data->{'listincgst'}, $data->{'invoiceincgst'},
205 $data->{'gstrate'}, $data->{'discount'},
206 $data->{notes}, $data->{deliverytime},
209 # return the id of this new supplier
210 my $id = $dbh->{'mysql_insertid'};
211 if ($id && $contacts) {
212 $contacts->[0] = C4::Bookseller::Contact->new( $contacts->[0] )
213 unless ref $contacts->[0] eq 'C4::Bookseller::Contact';
214 $contacts->[0]->bookseller($id);
215 $contacts->[0]->save();
220 #-----------------------------------------------------------------#
224 ModBookseller($bookseller);
226 Updates the information for a given bookseller. C<$bookseller> is a
227 reference-to-hash whose keys are the fields of the aqbooksellers table
228 in the Koha database. It must contain entries for all of the fields.
229 The entry to modify is determined by C<$bookseller-E<gt>{id}>.
231 The easiest way to get all of the necessary fields is to look up a
232 book seller with C<&GetBookseller>, modify what's necessary, then call
233 C<&ModBookseller> with the result.
238 my ($data, $contacts) = @_;
239 my $dbh = C4::Context->dbh;
240 return unless $data->{'id'};
241 my $query = 'UPDATE aqbooksellers
242 SET name=?,address1=?,address2=?,address3=?,address4=?,
243 postal=?,phone=?,accountnumber=?,fax=?,url=?,
244 active=?,listprice=?, invoiceprice=?,
245 gstreg=?,listincgst=?,invoiceincgst=?,
246 discount=?,notes=?,gstrate=?,deliverytime=?
248 my $sth = $dbh->prepare($query);
249 return $sth->execute(
250 $data->{'name'}, $data->{'address1'},
251 $data->{'address2'}, $data->{'address3'},
252 $data->{'address4'}, $data->{'postal'},
253 $data->{'phone'}, $data->{'accountnumber'},
254 $data->{'fax'}, $data->{'url'},
255 $data->{'active'}, $data->{'listprice'},
256 $data->{'invoiceprice'}, $data->{'gstreg'},
257 $data->{'listincgst'}, $data->{'invoiceincgst'},
258 $data->{'discount'}, $data->{'notes'},
259 $data->{'gstrate'}, $data->{deliverytime},
262 $contacts ||= $data->{'contacts'};
263 my $contactquery = "DELETE FROM aqcontacts WHERE booksellerid = ?";
264 my @contactparams = ($data->{'id'});
266 foreach my $contact (@$contacts) {
267 $contact = C4::Bookseller::Contact->new( $contact )
268 unless ref $contacts->[0] eq 'C4::Bookseller::Contact';
269 $contact->bookseller($data->{'id'});
271 push @contactparams, $contact->id if $contact->id;
273 if ($#contactparams > 0) {
274 $contactquery .= ' AND id NOT IN (' . ('?, ' x ($#contactparams - 1)) . '?);';
277 $sth = $dbh->prepare($contactquery);
278 $sth->execute(@contactparams);
284 DelBookseller($booksellerid);
286 delete the supplier record identified by $booksellerid
287 This sub assumes it is called only if the supplier has no order.
293 my $dbh = C4::Context->dbh;
294 my $sth = $dbh->prepare('DELETE FROM aqbooksellers WHERE id=?');
295 return $sth->execute($id);
304 Koha Development Team <http://koha-community.org/>