Attachment 19333 Details for Bug 5349 – Bug 5349: Create a table for order line transfers

[patch] Bug 5349: Create a table for order line transfers

Bug-5349-Create-a-table-for-order-line-transfers.patch (text/plain), 12.70 KB, created by Julian Maurice on 2013-07-02 10:50:22 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Julian Maurice

Created: 2013-07-02 10:50:22 UTC

Size: 12.70 KB

patch

obsolete

>From cfb90c5e4a1fa5dd97aca72ed419f2adb3175483 Mon Sep 17 00:00:00 2001
>From: Julian Maurice <julian.maurice@biblibre.com>
>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.
>---
> C4/Acquisition.pm | 51 +++++++++-----------
> acqui/basket.pl | 21 +++++---
> installer/data/mysql/kohastructure.sql | 16 +++++-
> installer/data/mysql/updatedatabase.pl | 14 ++++--
> .../intranet-tmpl/prog/en/modules/acqui/basket.tt | 30 +++++++++++-
> 5 files changed, 93 insertions(+), 39 deletions(-)
>
>diff --git a/C4/Acquisition.pm b/C4/Acquisition.pm
>index d0517d1..2bd84fb 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')
> ";
>@@ -1352,11 +1355,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 tranferred_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')
>@@ -1704,42 +1714,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);
> 
>@@ -1751,6 +1741,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 7eff6e4..3d97e5c 100755
>--- a/acqui/basket.pl
>+++ b/acqui/basket.pl
>@@ -308,11 +308,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'},
>@@ -332,7 +327,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 ),
>@@ -428,6 +423,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 28a12a2..f43c70b 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)
>@@ -2960,6 +2959,21 @@ CREATE TABLE `aqorders_items` ( -- information on items entered in the acquisiti
> 
> 
> --
>+-- 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 ffb3dbb..1027a16 100755
>--- a/installer/data/mysql/updatedatabase.pl
>+++ b/installer/data/mysql/updatedatabase.pl
>@@ -7012,11 +7012,19 @@ CREATE TABLE IF NOT EXISTS borrower_files (
> 
> $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 a503511..cd5c783 100644
>--- a/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/basket.tt
>+++ b/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/basket.tt
>@@ -343,7 +343,20 @@
> [% ELSE %]
> [<a href="/cgi-bin/koha/acqui/modordernotes.pl?ordernumber=[% books_loo.ordernumber %]">Add note</a>]
> [% END %]
>- [% books_loo.internalnotes %]
>+ [% IF (books_loo.transferred_from) %]
>+ [% USE date %]
>+ [% USE KohaDates %]
>+ [% basket = books_loo.transferred_from.basket %]
>+ [% bookseller = books_loo.transferred_from.bookseller %]
>+ [% timestamp = books_loo.transferred_from.timestamp %]
>+ Transferred from
>+ <a href="/cgi-bin/koha/acqui/basket.pl?basketno=[% basket.basketno %]">basket: [% basket.basketname %]</a>
>+ (<a href="/cgi-bin/koha/acqui/supplier.pl?booksellerid=[% bookseller.id %]">[% bookseller.name %]</a>)
>+ on 
>+ [% date.format(timestamp, '%F') | $KohaDates %]
>+ 
>+ 
>+ [% END %]
> </td>
> <td class="number gste [% IF books_loo.rrpgste.search('^0') %]error[% END %]">[% books_loo.rrpgste %]</td>
> <td class="number gste [% IF books_loo.ecostgste.search('^0') %]error[% END %]">[% books_loo.ecostgste %]</td>
>@@ -435,7 +448,20 @@
> [% IF ( order.publicationyear ) %], [% order.publicationyear %][% END %]
> [% IF ( books_loo.editionstatement ) %], [% books_loo.editionstatement %][% END %]
> 
>- [% order.internalnotes %]
>+ [% IF order.transferred_to %]
>+ [% USE date %]
>+ [% USE KohaDates %]
>+ [% basket = order.transferred_to.basket %]
>+ [% bookseller = order.transferred_to.bookseller %]
>+ [% timestamp = order.transferred_to.timestamp %]
>+ Transferred to
>+ <a href="/cgi-bin/koha/acqui/basket.pl?basketno=[% basket.basketno %]">basket: [% basket.basketname %]</a>
>+ (<a href="/cgi-bin/koha/acqui/supplier.pl?booksellerid=[% bookseller.id %]">[% bookseller.name %]</a>)
>+ on 
>+ [% date.format(timestamp, '%F') | $KohaDates %]
>+ 
>+ 
>+ [% END %]
> </td>
> <td class="number gste">[% order.rrpgste %]</td>
> <td class="number gste">[% order.ecostgste %]</td>
>-- 
>1.7.10.4

Actions: View | Diff | Splinter Review

Attachments on bug 5349: 8961 | 8962 | 9909 | 10032 | 10190 | 10375 | 10376 | 10441 | 10462 | 10463 | 10636 | 11117 | 11118 | 11385 | 11386 | 12236 | 12237 | 12238 | 12420 | 12421 | 12422 | 12462 | 12463 | 12464 | 14441 | 14442 | 14443 | 16499 | 16500 | 16501 | 18931 | 18933 | 18934 | 18935 | 19332 | 19333 | 19336 | 19917 | 19918 | 19919 | 19920 | 19921 | 19922 | 20476 | 20477 | 20478 | 20479 | 20480 | 20481 | 20691 | 20692 | 20693 | 20694 | 20695 | 20696