From 905306efb191d684e1470b1279be32230e2258eb Mon Sep 17 00:00:00 2001 From: Julian Maurice Date: Tue, 2 Jul 2013 10:46:37 +0000 Subject: [PATCH] Bug 5349: Create a table for order line transfers This allow to keep transfers informations without having untranslatable strings in database. Signed-off-by: sonia Signed-off-by: Chris Cormack Signed-off-by: Galen Charlton --- C4/Acquisition.pm | 51 +++++++++---------- acqui/basket.pl | 21 +++++--- installer/data/mysql/kohastructure.sql | 16 +++++- installer/data/mysql/updatedatabase.pl | 14 +++-- .../prog/en/modules/acqui/basket.tt | 26 +++++++++- 5 files changed, 89 insertions(+), 39 deletions(-) diff --git a/C4/Acquisition.pm b/C4/Acquisition.pm index 5662e0bdb2..ed9c215127 100644 --- a/C4/Acquisition.pm +++ b/C4/Acquisition.pm @@ -939,11 +939,14 @@ sub GetOrders { SELECT biblio.*,biblioitems.*, aqorders.*, aqbudgets.*, - biblio.title + biblio.title, + aqorders_transfers.ordernumber_from AS transferred_from, + aqorders_transfers.timestamp AS transferred_from_timestamp FROM aqorders LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber LEFT JOIN biblioitems ON biblioitems.biblionumber =biblio.biblionumber + LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_to = aqorders.ordernumber WHERE basketno=? AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00') "; @@ -1265,11 +1268,18 @@ sub GetCancelledOrders { my $dbh = C4::Context->dbh; my $query = " - SELECT biblio.*, biblioitems.*, aqorders.*, aqbudgets.* + SELECT + biblio.*, + biblioitems.*, + aqorders.*, + aqbudgets.*, + aqorders_transfers.ordernumber_to AS transferred_to, + aqorders_transfers.timestamp AS transferred_to_timestamp FROM aqorders LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber + LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_from = aqorders.ordernumber WHERE basketno = ? AND (datecancellationprinted IS NOT NULL AND datecancellationprinted <> '0000-00-00') @@ -1617,42 +1627,22 @@ sub TransferOrder { my $order = GetOrder( $ordernumber ); return if $order->{datereceived}; + my $basket = GetBasket($basketno); + return unless $basket; - my $today = C4::Dates->new()->output("iso"); - my $query = qq{ - SELECT aqbooksellers.name - FROM aqorders - LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno - LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id - WHERE aqorders.ordernumber = ? - }; my $dbh = C4::Context->dbh; - my $sth = $dbh->prepare($query); - $sth->execute($ordernumber); - my ($booksellerfromname) = $sth->fetchrow_array; - - $query = qq{ - SELECT aqbooksellers.name - FROM aqbasket - LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id - WHERE aqbasket.basketno = ? - }; - $sth = $dbh->prepare($query); - $sth->execute($basketno); - my ($booksellertoname) = $sth->fetchrow_array; + my ($query, $sth, $rv); $query = qq{ UPDATE aqorders - SET datecancellationprinted = CAST(NOW() AS date), - internalnotes = ? + SET datecancellationprinted = CAST(NOW() AS date) WHERE ordernumber = ? }; $sth = $dbh->prepare($query); - $sth->execute("Cancelled and transfered to $booksellertoname on $today", $ordernumber); + $rv = $sth->execute($ordernumber); delete $order->{'ordernumber'}; $order->{'basketno'} = $basketno; - $order->{'internalnotes'} = "Transfered from $booksellerfromname on $today"; my $newordernumber; (undef, $newordernumber) = NewOrder($order); @@ -1664,6 +1654,13 @@ sub TransferOrder { $sth = $dbh->prepare($query); $sth->execute($newordernumber, $ordernumber); + $query = q{ + INSERT INTO aqorders_transfers (ordernumber_from, ordernumber_to) + VALUES (?, ?) + }; + $sth = $dbh->prepare($query); + $sth->execute($ordernumber, $newordernumber); + return $newordernumber; } diff --git a/acqui/basket.pl b/acqui/basket.pl index 06d4d1a18a..681cd60dd3 100755 --- a/acqui/basket.pl +++ b/acqui/basket.pl @@ -304,11 +304,6 @@ if ( $op eq 'delete_confirm' ) { last; } - my @cancelledorders = GetCancelledOrders($basketno); - foreach (@cancelledorders) { - $_->{'line_total'} = sprintf("%.2f", $_->{'ecost'} * $_->{'quantity'}); - } - $template->param( basketno => $basketno, basketname => $basket->{'basketname'}, @@ -328,7 +323,7 @@ if ( $op eq 'delete_confirm' ) { name => $bookseller->{'name'}, books_loop => \@books_loop, book_foot_loop => \@book_foot_loop, - cancelledorders_loop => \@cancelledorders, + cancelledorders_loop => \@cancelledorders_loop, total_quantity => $total_quantity, total_gste => sprintf( "%.2f", $total_gste ), total_gsti => sprintf( "%.2f", $total_gsti ), @@ -424,6 +419,20 @@ sub get_order_infos { $line{surnamesuggestedby} = $$suggestion{surnamesuggestedby}; $line{firstnamesuggestedby} = $$suggestion{firstnamesuggestedby}; + foreach my $key (qw(transferred_from transferred_to)) { + if ($line{$key}) { + my $order = GetOrder($line{$key}); + my $basket = GetBasket($order->{basketno}); + my $bookseller = GetBookSellerFromId($basket->{booksellerid}); + $line{$key} = { + order => $order, + basket => $basket, + bookseller => $bookseller, + timestamp => $line{$key . '_timestamp'}, + }; + } + } + return \%line; } diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index c885b556b6..b22134bbd2 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -2911,7 +2911,6 @@ CREATE TABLE `aqorders` ( -- information related to the basket line items `cancelledby` varchar(10) default NULL, -- not used? always NULL `datecancellationprinted` date default NULL, -- the date the line item was deleted `notes` mediumtext, -- notes related to this order line - internalnotes mediumtext DEFAULT NULL, -- used by Koha to store some informations, not editable by librarians `supplierreference` mediumtext, -- not used? always NULL `purchaseordernumber` mediumtext, -- not used? always NULL `basketno` int(11) default NULL, -- links this order line to a specific basket (aqbasket.basketno) @@ -2958,6 +2957,21 @@ CREATE TABLE `aqorders_items` ( -- information on items entered in the acquisiti ) ENGINE=InnoDB DEFAULT CHARSET=utf8; +-- +-- Table structure for table aqorders_transfers +-- + +DROP TABLE IF EXISTS aqorders_transfers; +CREATE TABLE aqorders_transfers ( + ordernumber_from int(11) NULL, + ordernumber_to int(11) NULL, + timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, + UNIQUE KEY ordernumber_from (ordernumber_from), + UNIQUE KEY ordernumber_to (ordernumber_to), + CONSTRAINT aqorders_transfers_ordernumber_from FOREIGN KEY (ordernumber_from) REFERENCES aqorders (ordernumber) ON DELETE SET NULL ON UPDATE CASCADE, + CONSTRAINT aqorders_transfers_ordernumber_to FOREIGN KEY (ordernumber_to) REFERENCES aqorders (ordernumber) ON DELETE SET NULL ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + -- -- Table structure for table aqinvoices -- diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index 3d1699fe8a..9aa8998447 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -7106,11 +7106,19 @@ if ( CheckVersion($DBversion) ) { $DBversion = "XXX"; if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { + $dbh->do(qq{DROP TABLE IF EXISTS aqorders_transfers;}); $dbh->do(qq{ - ALTER TABLE aqorders - ADD COLUMN internalnotes MEDIUMTEXT DEFAULT NULL AFTER notes + CREATE TABLE aqorders_transfers ( + ordernumber_from int(11) NULL, + ordernumber_to int(11) NULL, + timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, + UNIQUE KEY ordernumber_from (ordernumber_from), + UNIQUE KEY ordernumber_to (ordernumber_to), + CONSTRAINT aqorders_transfers_ordernumber_from FOREIGN KEY (ordernumber_from) REFERENCES aqorders (ordernumber) ON DELETE SET NULL ON UPDATE CASCADE, + CONSTRAINT aqorders_transfers_ordernumber_to FOREIGN KEY (ordernumber_to) REFERENCES aqorders (ordernumber) ON DELETE SET NULL ON UPDATE CASCADE + ) ENGINE=InnoDB DEFAULT CHARSET=utf8; }); - print "Upgrade to $DBversion done (Add internalnotes field in aqorders table)\n"; + print "Upgrade to $DBversion done (Add aqorders_transfers table)\n"; SetVersion($DBversion); } diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/basket.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/basket.tt index 1c741203ac..08a7ff43c9 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/basket.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/basket.tt @@ -354,7 +354,18 @@ [% ELSE %]

[Add note]

[% END %] -

[% books_loo.internalnotes %]

+ [% IF (books_loo.transferred_from) %] + [% basket = books_loo.transferred_from.basket %] + [% bookseller = books_loo.transferred_from.bookseller %] + [% timestamp = books_loo.transferred_from.timestamp %] +

Transferred from + basket: [% basket.basketname %] + ([% bookseller.name %]) + on + [% timestamp | $KohaDates %] + +

+ [% END %] [% books_loo.rrpgste %] [% books_loo.ecostgste %] @@ -446,7 +457,18 @@ [% IF ( order.publicationyear ) %], [% order.publicationyear %][% END %] [% IF ( books_loo.editionstatement ) %], [% books_loo.editionstatement %][% END %]

-

[% order.internalnotes %]

+ [% IF order.transferred_to %] + [% basket = order.transferred_to.basket %] + [% bookseller = order.transferred_to.bookseller %] + [% timestamp = order.transferred_to.timestamp %] +

Transferred to + basket: [% basket.basketname %] + ([% bookseller.name %]) + on + [% timestamp | $KohaDates %] + +

+ [% END %] [% order.rrpgste %] [% order.ecostgste %] -- 2.39.5