C4/Bookseller.pm

   1 package C4::Bookseller;
   2
   3 # Copyright 2000-2002 Katipo Communications
   4 # Copyright 2010 PTFS Europe
   5 #
   6 # This file is part of Koha.
   7 #
   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
  11 # version.
  12 #
  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.
  16 #
  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.
  20
  21 use strict;
  22 use warnings;
  23
  24 use base qw( Exporter );
  25
  26 # set the version for version checking
  27 our $VERSION   = 3.07.00.049;
  28 our @EXPORT_OK = qw(
  29   GetBookSeller GetBooksellersWithLateOrders GetBookSellerFromId
  30   ModBookseller
  31   DelBookseller
  32   AddBookseller
  33 );
  34
  35 =head1 NAME
  36
  37 C4::Bookseller - Koha functions for dealing with booksellers.
  38
  39 =head1 SYNOPSIS
  40
  41 use C4::Bookseller;
  42
  43 =head1 DESCRIPTION
  44
  45 The functions in this module deal with booksellers. They allow to
  46 add a new bookseller, to modify it or to get some informations around
  47 a bookseller.
  48
  49 =head1 FUNCTIONS
  50
  51 =head2 GetBookSeller
  52
  53 @results = GetBookSeller($searchstring);
  54
  55 Looks up a book seller. C<$searchstring> may be either a book seller
  56 ID, or a string to look for in the book seller's name.
  57
  58 C<@results> is an array of hash_refs whose keys are the fields of of the
  59 aqbooksellers table in the Koha database.
  60
  61 =cut
  62
  63 sub GetBookSeller {
  64     my $searchstring = shift;
  65     $searchstring = q{%} . $searchstring . q{%};
  66     my $query = "
  67         SELECT aqbooksellers.*, count(*) AS basketcount
  68         FROM aqbooksellers
  69         LEFT JOIN aqbasket ON aqbasket.booksellerid = aqbooksellers.id
  70         WHERE name LIKE ? GROUP BY aqbooksellers.id ORDER BY name
  71     ";
  72
  73     my $dbh           = C4::Context->dbh;
  74     my $sth           = $dbh->prepare($query);
  75     $sth->execute($searchstring);
  76     my $resultset_ref = $sth->fetchall_arrayref( {} );
  77     return @{$resultset_ref};
  78 }
  79
  80 sub GetBookSellerFromId {
  81     my $id = shift or return;
  82     my $dbh = C4::Context->dbh;
  83     my $vendor =
  84       $dbh->selectrow_hashref( 'SELECT * FROM aqbooksellers WHERE id = ?',
  85         {}, $id );
  86     if ($vendor) {
  87         ( $vendor->{basketcount} ) = $dbh->selectrow_array(
  88             'SELECT count(*) FROM aqbasket where booksellerid = ?',
  89             {}, $id );
  90         ( $vendor->{subscriptioncount} ) = $dbh->selectrow_array(
  91             'SELECT count(*) FROM subscription WHERE aqbooksellerid = ?',
  92             {}, $id );
  93     }
  94     return $vendor;
  95 }
  96
  97 #-----------------------------------------------------------------#
  98
  99 =head2 GetBooksellersWithLateOrders
 100
 101 %results = GetBooksellersWithLateOrders( $delay, $estimateddeliverydatefrom, $estimateddeliverydateto );
 102
 103 Searches for suppliers with late orders.
 104
 105 =cut
 106
 107 sub GetBooksellersWithLateOrders {
 108     my ( $delay, $estimateddeliverydatefrom, $estimateddeliverydateto ) = @_;
 109     my $dbh = C4::Context->dbh;
 110
 111     # FIXME NOT quite sure that this operation is valid for DBMs different from Mysql, HOPING so
 112     # should be tested with other DBMs
 113
 114     my $query;
 115     my @query_params = ();
 116     my $dbdriver = C4::Context->config("db_scheme") || "mysql";
 117     $query = "
 118         SELECT DISTINCT aqbasket.booksellerid, aqbooksellers.name
 119         FROM aqorders LEFT JOIN aqbasket ON aqorders.basketno=aqbasket.basketno
 120         LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
 121         WHERE
 122             ( datereceived = ''
 123             OR datereceived IS NULL
 124             OR aqorders.quantityreceived < aqorders.quantity
 125             )
 126             AND aqorders.rrp <> 0
 127             AND aqorders.ecost <> 0
 128             AND aqorders.quantity - COALESCE(aqorders.quantityreceived,0) <> 0
 129             AND aqbasket.closedate IS NOT NULL
 130     ";
 131     if ( defined $delay && $delay >= 0 ) {
 132         $query .= " AND (closedate <= DATE_SUB(CAST(now() AS date),INTERVAL ? + COALESCE(aqbooksellers.deliverytime,0) DAY)) ";
 133         push @query_params, $delay;
 134     } elsif ( $delay && $delay < 0 ){
 135         warn 'WARNING: GetBooksellerWithLateOrders is called with a negative value';
 136         return;
 137     }
 138     if ( defined $estimateddeliverydatefrom ) {
 139         $query .= '
 140             AND ADDDATE(aqbasket.closedate, INTERVAL COALESCE(aqbooksellers.deliverytime,0) DAY) >= ?';
 141             push @query_params, $estimateddeliverydatefrom;
 142             if ( defined $estimateddeliverydateto ) {
 143                 $query .= ' AND ADDDATE(aqbasket.closedate, INTERVAL COALESCE(aqbooksellers.deliverytime, 0) DAY) <= ?';
 144                 push @query_params, $estimateddeliverydateto;
 145             } else {
 146                     $query .= ' AND ADDDATE(aqbasket.closedate, INTERVAL COALESCE(aqbooksellers.deliverytime, 0) DAY) <= CAST(now() AS date)';
 147             }
 148     }
 149     if ( defined $estimateddeliverydateto ) {
 150         $query .= ' AND ADDDATE(aqbasket.closedate, INTERVAL COALESCE(aqbooksellers.deliverytime,0) DAY) <= ?';
 151         push @query_params, $estimateddeliverydateto;
 152     }
 153
 154     my $sth = $dbh->prepare($query);
 155     $sth->execute( @query_params );
 156     my %supplierlist;
 157     while ( my ( $id, $name ) = $sth->fetchrow ) {
 158         $supplierlist{$id} = $name;
 159     }
 160
 161     return %supplierlist;
 162 }
 163
 164 #--------------------------------------------------------------------#
 165
 166 =head2 AddBookseller
 167
 168 $id = &AddBookseller($bookseller);
 169
 170 Creates a new bookseller. C<$bookseller> is a reference-to-hash whose
 171 keys are the fields of the aqbooksellers table in the Koha database.
 172 All fields must be present.
 173
 174 Returns the ID of the newly-created bookseller.
 175
 176 =cut
 177
 178 sub AddBookseller {
 179     my ($data) = @_;
 180     my $dbh    = C4::Context->dbh;
 181     my $query  = q|
 182         INSERT INTO aqbooksellers
 183             (
 184                 name,      address1,      address2,     address3,   address4,
 185                 postal,    phone,         accountnumber,fax,        url,
 186                 contact,   contpos,       contphone,    contfax,    contaltphone,
 187                 contemail, contnotes,     active,       listprice,  invoiceprice,
 188                 gstreg,    listincgst,    invoiceincgst,gstrate,    discount,
 189                 notes,     deliverytime
 190             )
 191         VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) |
 192       ;
 193     my $sth = $dbh->prepare($query);
 194     $sth->execute(
 195         $data->{name}         ,$data->{address1},
 196         $data->{address2}     ,$data->{address3},
 197         $data->{address4}     ,$data->{postal},
 198         $data->{phone}        ,$data->{accountnumber},
 199         $data->{fax},
 200         $data->{url}          ,$data->{contact},
 201         $data->{contpos}      ,$data->{contphone},
 202         $data->{contfax}      ,$data->{contaltphone},
 203         $data->{contemail}    ,$data->{contnotes},
 204         $data->{active}       ,$data->{listprice},
 205         $data->{invoiceprice} ,$data->{gstreg},
 206         $data->{listincgst}   ,$data->{invoiceincgst},
 207         $data->{gstrate}      ,$data->{discount},
 208         $data->{notes}        ,$data->{deliverytime},
 209     );
 210
 211     # return the id of this new supplier
 212     return $dbh->{'mysql_insertid'};
 213 }
 214
 215 #-----------------------------------------------------------------#
 216
 217 =head2 ModBookseller
 218
 219 ModBookseller($bookseller);
 220
 221 Updates the information for a given bookseller. C<$bookseller> is a
 222 reference-to-hash whose keys are the fields of the aqbooksellers table
 223 in the Koha database. It must contain entries for all of the fields.
 224 The entry to modify is determined by C<$bookseller-E<gt>{id}>.
 225
 226 The easiest way to get all of the necessary fields is to look up a
 227 book seller with C<&GetBookseller>, modify what's necessary, then call
 228 C<&ModBookseller> with the result.
 229
 230 =cut
 231
 232 sub ModBookseller {
 233     my ($data) = @_;
 234     my $dbh    = C4::Context->dbh;
 235     return unless $data->{'id'};
 236     my $query  = 'UPDATE aqbooksellers
 237         SET name=?,address1=?,address2=?,address3=?,address4=?,
 238             postal=?,phone=?,accountnumber=?,fax=?,url=?,contact=?,contpos=?,
 239             contphone=?,contfax=?,contaltphone=?,contemail=?,
 240             contnotes=?,active=?,listprice=?, invoiceprice=?,
 241             gstreg=?,listincgst=?,invoiceincgst=?,
 242             discount=?,notes=?,gstrate=?,deliverytime=?
 243         WHERE id=?';
 244     my $sth = $dbh->prepare($query);
 245     return $sth->execute(
 246         $data->{'name'},         $data->{'address1'},
 247         $data->{'address2'},     $data->{'address3'},
 248         $data->{'address4'},     $data->{'postal'},
 249         $data->{'phone'},        $data->{'accountnumber'},
 250         $data->{'fax'},
 251         $data->{'url'},          $data->{'contact'},
 252         $data->{'contpos'},      $data->{'contphone'},
 253         $data->{'contfax'},      $data->{'contaltphone'},
 254         $data->{'contemail'},    $data->{'contnotes'},
 255         $data->{'active'},       $data->{'listprice'},
 256         $data->{'invoiceprice'}, $data->{'gstreg'},
 257         $data->{'listincgst'},   $data->{'invoiceincgst'},
 258         $data->{'discount'},     $data->{'notes'},
 259         $data->{'gstrate'},
 260         $data->{deliverytime},
 261         $data->{'id'}
 262     );
 263 }
 264
 265 =head2 DelBookseller
 266
 267 DelBookseller($booksellerid);
 268
 269 delete the supplier record identified by $booksellerid
 270 This sub assumes it is called only if the supplier has no order.
 271
 272 =cut
 273
 274 sub DelBookseller {
 275     my $id  = shift;
 276     my $dbh = C4::Context->dbh;
 277     my $sth = $dbh->prepare('DELETE FROM aqbooksellers WHERE id=?');
 278     return $sth->execute($id);
 279 }
 280
 281 1;
 282
 283 __END__
 284
 285 =head1 AUTHOR
 286
 287 Koha Development Team <http://koha-community.org/>
 288
 289 =cut