use base qw( Exporter );
+use C4::Bookseller::Contact;
+
# set the version for version checking
-our $VERSION = 4.01;
+our $VERSION = 3.07.00.049;
our @EXPORT_OK = qw(
GetBookSeller GetBooksellersWithLateOrders GetBookSellerFromId
ModBookseller
@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 hash_refs whose keys are the fields of of the
aqbooksellers table in the Koha database.
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 $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);
( $vendor->{subscriptioncount} ) = $dbh->selectrow_array(
'SELECT count(*) FROM subscription WHERE aqbooksellerid = ?',
{}, $id );
+ $vendor->{'contacts'} = C4::Bookseller::Contact->get_from_bookseller($id);
}
return $vendor;
}
=head2 GetBooksellersWithLateOrders
-%results = GetBooksellersWithLateOrders($delay);
+%results = GetBooksellersWithLateOrders( $delay, $estimateddeliverydatefrom, $estimateddeliverydateto );
Searches for suppliers with late orders.
=cut
sub GetBooksellersWithLateOrders {
- my ( $delay, $branch, $estimateddeliverydatefrom, $estimateddeliverydateto ) = @_; # FIXME: Branch argument unused.
+ 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
- my $strsth;
+ my $query;
my @query_params = ();
my $dbdriver = C4::Context->config("db_scheme") || "mysql";
- $strsth = "
+ $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
)
AND aqorders.rrp <> 0
AND aqorders.ecost <> 0
- AND aqorders.quantity - IFNULL(aqorders.quantityreceived,0) <> 0
+ AND aqorders.quantity - COALESCE(aqorders.quantityreceived,0) <> 0
AND aqbasket.closedate IS NOT NULL
";
- if ( defined $delay ) {
- $strsth .= " AND (closedate <= DATE_SUB(CAST(now() AS date),INTERVAL ? DAY)) ";
+ 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 ) {
- $strsth .= '
- AND aqbooksellers.deliverytime IS NOT NULL
- AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) >= ?';
- push @query_params, $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)';
+ }
}
- if ( defined $estimateddeliverydatefrom and defined $estimateddeliverydateto ) {
- $strsth .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= ?';
+ if ( defined $estimateddeliverydateto ) {
+ $query .= ' AND ADDDATE(aqbasket.closedate, INTERVAL COALESCE(aqbooksellers.deliverytime,0) 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);
+ my $sth = $dbh->prepare($query);
$sth->execute( @query_params );
my %supplierlist;
while ( my ( $id, $name ) = $sth->fetchrow ) {
=cut
sub AddBookseller {
- my ($data) = @_;
+ my ($data, $contacts) = @_;
my $dbh = C4::Context->dbh;
- my $query = q|
+ my $query = q|
INSERT INTO aqbooksellers
(
- name, address1, address2, address3, address4,
- postal, phone, accountnumber, 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->{'address2'}, $data->{'address3'},
$data->{'address4'}, $data->{'postal'},
$data->{'phone'}, $data->{'accountnumber'},
- $data->{'fax'},
- $data->{'url'}, $data->{'contact'},
- $data->{'contpos'}, $data->{'contphone'},
- $data->{'contfax'}, $data->{'contaltphone'},
- $data->{'contemail'}, $data->{'contnotes'},
+ $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
- return $dbh->{'mysql_insertid'};
+ 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;
}
#-----------------------------------------------------------------#
=cut
sub ModBookseller {
- my ($data) = @_;
+ my ($data, $contacts) = @_;
my $dbh = C4::Context->dbh;
+ return unless $data->{'id'};
my $query = 'UPDATE aqbooksellers
SET name=?,address1=?,address2=?,address3=?,address4=?,
- postal=?,phone=?,accountnumber=?,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=?,deliverytime=?
WHERE id=?';
my $sth = $dbh->prepare($query);
- $sth->execute(
+ my $cnt = $sth->execute(
$data->{'name'}, $data->{'address1'},
$data->{'address2'}, $data->{'address3'},
$data->{'address4'}, $data->{'postal'},
$data->{'phone'}, $data->{'accountnumber'},
- $data->{'fax'},
- $data->{'url'}, $data->{'contact'},
- $data->{'contpos'}, $data->{'contphone'},
- $data->{'contfax'}, $data->{'contaltphone'},
- $data->{'contemail'}, $data->{'contnotes'},
+ $data->{'fax'}, $data->{'url'},
$data->{'active'}, $data->{'listprice'},
$data->{'invoiceprice'}, $data->{'gstreg'},
$data->{'listincgst'}, $data->{'invoiceincgst'},
$data->{'discount'}, $data->{'notes'},
- $data->{'gstrate'},
- $data->{deliverytime},
+ $data->{'gstrate'}, $data->{deliverytime},
$data->{'id'}
);
- return;
+ $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
my $id = shift;
my $dbh = C4::Context->dbh;
my $sth = $dbh->prepare('DELETE FROM aqbooksellers WHERE id=?');
- $sth->execute($id);
- return;
+ return $sth->execute($id);
}
1;
projects / koha.git / blobdiff