From 579aa677a8110ad61207f34de7a27be480970199 Mon Sep 17 00:00:00 2001 From: Julian Maurice Date: Tue, 17 Apr 2012 14:08:46 +0200 Subject: [PATCH] Bug 7957: Routing lists enhancements - Manage several routing list per subscription - Export a routing list as CSV Test plan: Run the updatedatabase.pl script Existing routing lists should have been imported in a routing list named 'import' From the serials-home.pl page: 1 Make a search, in the results list you have a column 'Actions' that should contain a "New/Edit routing list" link" (if not, consider setting RoutingSerials syspref). If the subscription doesn't have a routing list, you should have a 'New routing list' link. Otherwise you have an 'Edit routing lists (X)' link, where X is the number of routing lists. 2 Choose a subscription which have no routing lists and click on 'New routing list' 3 You are on the routing list creation page. Enter a title for the routing list and click on 'Save'. 4 The routing list has been created and now you are on the routing list modification page. The behaviour of this page is quite the same as before. A sensible change is that reordering borrowers doesn't reload the page each time you make a change. 5 Click on 'Save' to finish routing list modifications. 6 Now you are on the routing lists management page. You can edit a routing list by clicking on its title, preview it, export it, and delete it by clicking on the corresponding link. 7 Click on 'Preview'. 8 You will be asked to choose a serial. Pick the one you want to print the routing list for and click on 'Continue'. 9 If RoutingListAddReserves is OFF, the routing list is displayed and you just have to click on 'Print'. If the syspref is ON, you will have to click on 'Confirm and print', an alert will show telling you that holds will be placed on the serial you chose. Click 'OK' and you will be able to print the slip. Now check if the holds have been placed. (It works only if an item is attached to this serial). 10 Go back to the routing lists management page (routinglists.pl) and click on 'Export'. Download the CSV and check that it contains correct data. From serials-collection.pl page: 11 In the serials list, click on 'Print list'. 12 You will be asked to choose a routing list. Pick one and click on 'Continue'. 13 ... (same behaviour as above) Try to add and delete routing lists to show if links are correctly updated in serials-home.pl, serials-collection.pl and subscription-detail.pl Signed-off-by: Paola Rossi Signed-off-by: Aleisha --- C4/Serials.pm | 235 ++++--------- C4/Serials/RoutingLists.pm | 309 ++++++++++++++++++ acqui/newordersubscription.pl | 3 +- .../data/mysql/atomicupdate/bug_7957.perl | 67 ++++ installer/data/mysql/kohastructure.sql | 45 ++- .../prog/en/includes/serials-menu.inc | 4 +- .../prog/en/modules/members/routing-lists.tt | 71 ++-- .../modules/serials/routing-preview-slip.tt | 122 +++++-- .../en/modules/serials/routing-preview.tt | 68 ---- .../prog/en/modules/serials/routing.tt | 135 -------- .../prog/en/modules/serials/routinglist.tt | 204 ++++++++++++ .../prog/en/modules/serials/routinglists.tt | 71 ++++ .../en/modules/serials/serials-collection.tt | 12 +- .../prog/en/modules/serials/serials-search.tt | 16 +- serials/reorder_members.pl | 37 --- serials/routing-preview-slip.pl | 151 +++++++++ serials/routing-preview.pl | 144 -------- serials/routing.pl | 133 -------- serials/routinglist.pl | 120 +++++++ serials/routinglists.pl | 81 +++++ serials/serials-collection.pl | 14 +- serials/serials-edit.pl | 1 - serials/serials-search.pl | 3 +- serials/subscription-detail.pl | 3 +- t/db_dependent/Serials.t | 49 ++- t/db_dependent/Serials/RoutingLists.t | 240 ++++++++++++++ 26 files changed, 1537 insertions(+), 801 deletions(-) create mode 100644 C4/Serials/RoutingLists.pm create mode 100644 installer/data/mysql/atomicupdate/bug_7957.perl delete mode 100644 koha-tmpl/intranet-tmpl/prog/en/modules/serials/routing-preview.tt delete mode 100644 koha-tmpl/intranet-tmpl/prog/en/modules/serials/routing.tt create mode 100644 koha-tmpl/intranet-tmpl/prog/en/modules/serials/routinglist.tt create mode 100644 koha-tmpl/intranet-tmpl/prog/en/modules/serials/routinglists.tt delete mode 100755 serials/reorder_members.pl create mode 100755 serials/routing-preview-slip.pl delete mode 100755 serials/routing-preview.pl delete mode 100755 serials/routing.pl create mode 100755 serials/routinglist.pl create mode 100755 serials/routinglists.pl create mode 100644 t/db_dependent/Serials/RoutingLists.t diff --git a/C4/Serials.pm b/C4/Serials.pm index 2b9e1a62a5..8083ab26da 100644 --- a/C4/Serials.pm +++ b/C4/Serials.pm @@ -78,11 +78,10 @@ BEGIN { &GetSerialInformation &AddItem2Serial &PrepareSerialsData &GetNextExpected &ModNextExpected &GetPreviousSerialid + GetSerial GetSerialItemnumber &GetSuppliersWithLateIssues - &getroutinglist &delroutingmember &addroutingmember - &reorder_members - &check_routing &updateClaim + &updateClaim &CountIssues HasItems &subscriptionCurrentlyOnOrder @@ -162,6 +161,60 @@ sub GetSubscriptionHistoryFromSubscriptionId { return $results; } +=head2 GetSerial + + my $serial = &GetSerial($serialid); + +This sub returns serial informations (ie. in serial table) for given $serialid +It returns a hashref where each key is a sql column. + +=cut + +sub GetSerial { + my ($serialid) = @_; + + return unless $serialid; + + my $dbh = C4::Context->dbh; + my $query = qq{ + SELECT * + FROM serial + WHERE serialid = ? + }; + my $sth = $dbh->prepare($query); + $sth->execute($serialid); + return $sth->fetchrow_hashref; +} + +=head2 GetSerialItemnumber + + my $itemnumber = GetSerialItemnumber($serialid); + +Returns the itemnumber associated to $serialid or undef if there is no item. + +=cut + +sub GetSerialItemnumber { + my ($serialid) = @_; + + return unless $serialid; + my $itemnumber; + + my $dbh = C4::Context->dbh; + my $query = qq{ + SELECT itemnumber + FROM serialitems + WHERE serialid = ? + }; + my $sth = $dbh->prepare($query); + my $rv = $sth->execute($serialid); + if ($rv) { + my $result = $sth->fetchrow_hashref; + $itemnumber = $result->{itemnumber}; + } + return $itemnumber; +} + =head2 GetSerialInformation $data = GetSerialInformation($serialid); @@ -657,7 +710,7 @@ sub GetSerials { my @serials; my $statuses = join( ',', ( ARRIVED, MISSING_STATUSES, NOT_ISSUED ) ); my $query = "SELECT serialid,serialseq, status, publisheddate, - publisheddatetext, planneddate,notes, routingnotes + publisheddatetext, planneddate, notes FROM serial WHERE subscriptionid = ? AND status NOT IN ( $statuses ) ORDER BY IF(publisheddate IS NULL,planneddate,publisheddate) DESC"; @@ -678,7 +731,7 @@ sub GetSerials { # OK, now add the last 5 issues arrives/missing $query = "SELECT serialid,serialseq, status, planneddate, publisheddate, - publisheddatetext, notes, routingnotes + publisheddatetext, notes FROM serial WHERE subscriptionid = ? AND status IN ( $statuses ) @@ -726,7 +779,7 @@ sub GetSerials2 { my $dbh = C4::Context->dbh; my $query = q| SELECT serialid,serialseq, status, planneddate, publisheddate, - publisheddatetext, notes, routingnotes + publisheddatetext, notes FROM serial WHERE subscriptionid=? | @@ -1084,13 +1137,13 @@ sub ModSerialStatus { #It is a usual serial # 1st, get previous status : my $dbh = C4::Context->dbh; - my $query = "SELECT serial.subscriptionid,serial.status,subscription.periodicity,serial.routingnotes + my $query = "SELECT serial.subscriptionid,serial.status,subscription.periodicity FROM serial, subscription WHERE serial.subscriptionid=subscription.subscriptionid AND serialid=?"; my $sth = $dbh->prepare($query); $sth->execute($serialid); - my ( $subscriptionid, $oldstatus, $periodicity, $routingnotes ) = $sth->fetchrow; + my ( $subscriptionid, $oldstatus, $periodicity ) = $sth->fetchrow; my $frequency = GetSubscriptionFrequency($periodicity); # change status & update subscriptionhistory @@ -1101,12 +1154,12 @@ sub ModSerialStatus { my $query = ' UPDATE serial SET serialseq = ?, publisheddate = ?, publisheddatetext = ?, - planneddate = ?, status = ?, notes = ?, routingnotes = ? + planneddate = ?, status = ?, notes = ? WHERE serialid = ? '; $sth = $dbh->prepare($query); $sth->execute( $serialseq, $publisheddate, $publisheddatetext, - $planneddate, $status, $notes, $routingnotes, $serialid ); + $planneddate, $status, $notes, $serialid ); $query = "SELECT * FROM subscription WHERE subscriptionid = ?"; $sth = $dbh->prepare($query); $sth->execute($subscriptionid); @@ -1162,7 +1215,7 @@ sub ModSerialStatus { $sth = $dbh->prepare($query); $sth->execute( $newlastvalue1, $newlastvalue2, $newlastvalue3, $newinnerloop1, $newinnerloop2, $newinnerloop3, $subscriptionid ); my $newnote = C4::Context->preference('PreserveSerialNotes') ? $notes : ""; - NewIssue( $newserialseq, $subscriptionid, $subscription->{'biblionumber'}, 1, $nextpubdate, $nextpubdate, undef, $newnote, $routingnotes ); + NewIssue( $newserialseq, $subscriptionid, $subscription->{'biblionumber'}, 1, $nextpubdate, $nextpubdate, undef, $newnote ); # check if an alert must be sent... (= a letter is defined & status became "arrived" if ( $subscription->{letter} && $status == ARRIVED && $oldstatus != ARRIVED ) { require C4::Letters; @@ -1541,7 +1594,7 @@ sub ReNewSubscription { =head2 NewIssue -NewIssue($serialseq,$subscriptionid,$biblionumber,$status, $planneddate, $publisheddate, $notes, $routingnotes) +NewIssue($serialseq,$subscriptionid,$biblionumber,$status, $planneddate, $publisheddate, $notes) Create a new issue stored on the database. Note : we have to update the recievedlist and missinglist on subscriptionhistory for this subscription. @@ -1551,7 +1604,7 @@ returns the serial id sub NewIssue { my ( $serialseq, $subscriptionid, $biblionumber, $status, $planneddate, - $publisheddate, $publisheddatetext, $notes, $routingnotes ) = @_; + $publisheddate, $publisheddatetext, $notes ) = @_; ### FIXME biblionumber CAN be provided by subscriptionid. So Do we STILL NEED IT ? return unless ($subscriptionid); @@ -1573,7 +1626,6 @@ sub NewIssue { publisheddate => $publisheddate, publisheddatetext => $publisheddatetext, notes => $notes, - routingnotes => $routingnotes } )->store(); @@ -1886,161 +1938,6 @@ sub updateClaim { {}, CLAIMED, @$serialids ); } -=head2 check_routing - -$result = &check_routing($subscriptionid) - -this function checks to see if a serial has a routing list and returns the count of routingid -used to show either an 'add' or 'edit' link - -=cut - -sub check_routing { - my ($subscriptionid) = @_; - - return unless ($subscriptionid); - - my $dbh = C4::Context->dbh; - my $sth = $dbh->prepare( - "SELECT count(routingid) routingids FROM subscription LEFT JOIN subscriptionroutinglist - ON subscription.subscriptionid = subscriptionroutinglist.subscriptionid - WHERE subscription.subscriptionid = ? GROUP BY routingid ORDER BY ranking ASC - " - ); - $sth->execute($subscriptionid); - my $line = $sth->fetchrow_hashref; - my $result = $line->{'routingids'}; - return $result; -} - -=head2 addroutingmember - -addroutingmember($borrowernumber,$subscriptionid) - -this function takes a borrowernumber and subscriptionid and adds the member to the -routing list for that serial subscription and gives them a rank on the list -of either 1 or highest current rank + 1 - -=cut - -sub addroutingmember { - my ( $borrowernumber, $subscriptionid ) = @_; - - return unless ($borrowernumber and $subscriptionid); - - my $rank; - my $dbh = C4::Context->dbh; - my $sth = $dbh->prepare( "SELECT max(ranking) rank FROM subscriptionroutinglist WHERE subscriptionid = ?" ); - $sth->execute($subscriptionid); - while ( my $line = $sth->fetchrow_hashref ) { - if ( $line->{'rank'} > 0 ) { - $rank = $line->{'rank'} + 1; - } else { - $rank = 1; - } - } - $sth = $dbh->prepare( "INSERT INTO subscriptionroutinglist (subscriptionid,borrowernumber,ranking) VALUES (?,?,?)" ); - $sth->execute( $subscriptionid, $borrowernumber, $rank ); -} - -=head2 reorder_members - -reorder_members($subscriptionid,$routingid,$rank) - -this function is used to reorder the routing list - -it takes the routingid of the member one wants to re-rank and the rank it is to move to -- it gets all members on list puts their routingid's into an array -- removes the one in the array that is $routingid -- then reinjects $routingid at point indicated by $rank -- then update the database with the routingids in the new order - -=cut - -sub reorder_members { - my ( $subscriptionid, $routingid, $rank ) = @_; - my $dbh = C4::Context->dbh; - my $sth = $dbh->prepare( "SELECT * FROM subscriptionroutinglist WHERE subscriptionid = ? ORDER BY ranking ASC" ); - $sth->execute($subscriptionid); - my @result; - while ( my $line = $sth->fetchrow_hashref ) { - push( @result, $line->{'routingid'} ); - } - - # To find the matching index - my $i; - my $key = -1; # to allow for 0 being a valid response - for ( $i = 0 ; $i < @result ; $i++ ) { - if ( $routingid == $result[$i] ) { - $key = $i; # save the index - last; - } - } - - # if index exists in array then move it to new position - if ( $key > -1 && $rank > 0 ) { - my $new_rank = $rank - 1; # $new_rank is what you want the new index to be in the array - my $moving_item = splice( @result, $key, 1 ); - splice( @result, $new_rank, 0, $moving_item ); - } - for ( my $j = 0 ; $j < @result ; $j++ ) { - my $sth = $dbh->prepare( "UPDATE subscriptionroutinglist SET ranking = '" . ( $j + 1 ) . "' WHERE routingid = '" . $result[$j] . "'" ); - $sth->execute; - } - return; -} - -=head2 delroutingmember - -delroutingmember($routingid,$subscriptionid) - -this function either deletes one member from routing list if $routingid exists otherwise -deletes all members from the routing list - -=cut - -sub delroutingmember { - - # if $routingid exists then deletes that row otherwise deletes all with $subscriptionid - my ( $routingid, $subscriptionid ) = @_; - my $dbh = C4::Context->dbh; - if ($routingid) { - my $sth = $dbh->prepare("DELETE FROM subscriptionroutinglist WHERE routingid = ?"); - $sth->execute($routingid); - reorder_members( $subscriptionid, $routingid ); - } else { - my $sth = $dbh->prepare("DELETE FROM subscriptionroutinglist WHERE subscriptionid = ?"); - $sth->execute($subscriptionid); - } - return; -} - -=head2 getroutinglist - -@routinglist = getroutinglist($subscriptionid) - -this gets the info from the subscriptionroutinglist for $subscriptionid - -return : -the routinglist as an array. Each element of the array contains a hash_ref containing -routingid - a unique id, borrowernumber, ranking, and biblionumber of subscription - -=cut - -sub getroutinglist { - my ($subscriptionid) = @_; - my $dbh = C4::Context->dbh; - my $sth = $dbh->prepare( - 'SELECT routingid, borrowernumber, ranking, biblionumber - FROM subscription - JOIN subscriptionroutinglist ON subscription.subscriptionid = subscriptionroutinglist.subscriptionid - WHERE subscription.subscriptionid = ? ORDER BY ranking ASC' - ); - $sth->execute($subscriptionid); - my $routinglist = $sth->fetchall_arrayref({}); - return @{$routinglist}; -} - =head2 countissuesfrom $result = countissuesfrom($subscriptionid,$startdate) diff --git a/C4/Serials/RoutingLists.pm b/C4/Serials/RoutingLists.pm new file mode 100644 index 0000000000..c90998eed8 --- /dev/null +++ b/C4/Serials/RoutingLists.pm @@ -0,0 +1,309 @@ +package C4::Serials::RoutingLists; + +# Copyright 2012 Biblibre +# +# This file is part of Koha. +# +# Koha is free software; you can redistribute it and/or modify it +# under the terms of the GNU General Public License as published by +# the Free Software Foundation; either version 3 of the License, or +# (at your option) any later version. +# +# Koha is distributed in the hope that it will be useful, but +# WITHOUT ANY WARRANTY; without even the implied warranty of +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +# GNU General Public License for more details. +# +# You should have received a copy of the GNU General Public License +# along with Koha; if not, see . + +use Modern::Perl; + +use C4::Context; + +use Koha::Biblios; +use Koha::Patrons; +use Koha::I18N; + +use vars qw($VERSION @ISA @EXPORT_OK); + +BEGIN { + $VERSION = 3.01; + require Exporter; + @ISA = qw(Exporter); + @EXPORT_OK = qw( + &AddRoutingList + &ModRoutingList + &DelRoutingList + &GetRoutingList + &GetRoutingLists + &GetRoutingListsCount + &GetRoutingListAsCSV + ); +} + +=head1 NAME + +C4::Serials::RoutingLists + +=head1 SYNOPSYS + +use C4::Serials::RoutingLists; + +=head1 DESCRIPTION + +This module provides subroutines to deal with subscriptions routing lists + +=head1 FUNCTIONS + +=head2 AddRoutingList + +$routinglistid = &AddRoutingList($subscriptionid, $title); + +this function create a new routing list for a subscription. + +=cut + +sub AddRoutingList { + my ($subscriptionid, $title) = @_; + + return unless $subscriptionid and $title; + + my $dbh = C4::Context->dbh(); + my $query = qq{ + INSERT INTO subscriptionroutinglist (subscriptionid, title) + VALUES (?, ?) + }; + my $sth = $dbh->prepare($query); + my $rv = $sth->execute($subscriptionid, $title); + + return unless $rv; + return $dbh->last_insert_id(undef, undef, 'subscriptionroutinglist', undef); +} + +=head2 ModRoutingList + +&ModRoutingList($routinglistid, $subscriptionid, $title, $notes, @borrowernumbers); + +this function modifies a routing list. + +=cut + +sub ModRoutingList { + my ($routinglistid, $subscriptionid, $title, $notes, @borrowernumbers) = @_; + + return unless $routinglistid; + + my $dbh = C4::Context->dbh; + my $query = 'UPDATE subscriptionroutinglist'; + my @setstrings = (); + my @setargs = (); + if($subscriptionid) { + push @setstrings, 'subscriptionid = ?'; + push @setargs, $subscriptionid; + } + if($title) { + push @setstrings, 'title = ?'; + push @setargs, $title; + } + if($notes) { + push @setstrings, 'notes = ?'; + push @setargs, $notes; + } + + if(@setstrings) { + $query .= ' SET ' . join(',', @setstrings); + $query .= ' WHERE routinglistid = ?'; + my $sth = $dbh->prepare($query); + $sth->execute(@setargs, $routinglistid); + } + + $query = qq{ + DELETE FROM subscriptionrouting + WHERE routinglistid = ? + }; + my $sth = $dbh->prepare($query); + $sth->execute($routinglistid); + + if(@borrowernumbers > 0){ + $query = qq{ + INSERT INTO subscriptionrouting (routinglistid, borrowernumber, ranking) + VALUES (?, ?, ?) + }; + $sth = $dbh->prepare($query); + my $i = 1; + foreach (@borrowernumbers) { + $sth->execute($routinglistid, $_, $i); + $i++; + } + } +} + +=head2 DelRoutingList + +&DelRoutingList($routinglistid); + +this function delete a routing list. + +=cut + +sub DelRoutingList { + my ($routinglistid) = @_; + + return unless $routinglistid; + + my $dbh = C4::Context->dbh; + my $query = qq{ + DELETE FROM subscriptionroutinglist + WHERE routinglistid = ? + }; + my $sth = $dbh->prepare($query); + $sth->execute($routinglistid); +} + + +=head2 GetRoutingList + +$routinglist = &GetRoutingList($routinglistid); + +this function get infos from subscriptionroutinglist table. +The 'borrowers' keys contains the list of borrowernumbers attached +to this routing list. + +=cut + +sub GetRoutingList { + my ($routinglistid) = @_; + + return unless $routinglistid; + + my $dbh = C4::Context->dbh; + my $query = qq{ + SELECT * + FROM subscriptionroutinglist + WHERE routinglistid = ? + }; + my $sth = $dbh->prepare($query); + $sth->execute($routinglistid); + my $result = $sth->fetchrow_hashref; + + $query = qq{ + SELECT borrowernumber + FROM subscriptionrouting + WHERE routinglistid = ? + ORDER BY ranking ASC + }; + $sth = $dbh->prepare($query); + $sth->execute($routinglistid); + while (my $row = $sth->fetchrow_hashref) { + push @{$result->{borrowers}}, $row->{borrowernumber}; + } + + return $result; +} + +=head2 GetRoutingLists + +@routinglists = &GetRoutingLists($subscriptionid); + +this function get all routing lists for a subscription. + +=cut + +sub GetRoutingLists { + my ($subscriptionid) = @_; + + return () unless $subscriptionid; + + my $dbh = C4::Context->dbh; + my $query = qq{ + SELECT routinglistid + FROM subscriptionroutinglist + WHERE subscriptionid = ? + }; + my $sth = $dbh->prepare($query); + $sth->execute($subscriptionid); + my @results; + while (my $row = $sth->fetchrow_hashref) { + my $routinglistid = $row->{routinglistid}; + push @results, GetRoutingList($routinglistid); + } + + return @results; +} + +=head2 GetRoutingListsCount + +$count = &GetRoutingListsCount($subscriptionid); + +this function return the number of routing lists for a subscription. + +=cut + +sub GetRoutingListsCount { + my ($subscriptionid) = @_; + + return unless $subscriptionid; + + my $dbh = C4::Context->dbh; + my $query = qq{ + SELECT COUNT(*) AS count + FROM subscriptionroutinglist + WHERE subscriptionid = ? + }; + my $sth = $dbh->prepare($query); + $sth->execute($subscriptionid); + my $result = $sth->fetchrow_hashref; + + return $result->{count}; +} + +=head2 GetRoutingListAsCSV + +$csv_output = &GetRoutingListAsCSV($routinglistid); + +this function return the routing list as a CSV file. + +=cut + +sub GetRoutingListAsCSV { + my ($routinglistid) = @_; + + return unless $routinglistid; + + require C4::Serials; + require C4::Templates; + require Text::CSV::Encoded; + + my $csv = Text::CSV::Encoded->new( {encoding => "utf8" } ); + my $output; + + my $routinglist = GetRoutingList($routinglistid); + my $subscription = C4::Serials::GetSubscription($routinglist->{'subscriptionid'}); + my $biblio = Koha::Biblios->find($subscription->{'biblionumber'}); + + $csv->combine( + __('Subscription title'), + __('Routing list'), + __('Surname'), + __('First name'), + __('Notes'), + ); + $output .= $csv->string() . "\n"; + + foreach (@{$routinglist->{borrowers}}) { + my $patron = Koha::Patrons->find($_); + $csv->combine( + $biblio->title, + $routinglist->{'title'}, + $patron->surname, + $patron->firstname, + $routinglist->{'notes'}, + ); + $output .= $csv->string() . "\n"; + } + + return $output; +} + +1; diff --git a/acqui/newordersubscription.pl b/acqui/newordersubscription.pl index 51151af7f2..adc3dfd18b 100755 --- a/acqui/newordersubscription.pl +++ b/acqui/newordersubscription.pl @@ -24,6 +24,7 @@ use C4::Auth; use C4::Context; use C4::Output; use C4::Serials; +use C4::Serials::RoutingLists qw( GetRoutingListsCount ); use Koha::Acquisition::Booksellers; @@ -71,7 +72,7 @@ foreach my $sub (@subscriptions) { # to toggle between create or edit routing list options if ($routing) { - $sub->{routingedit} = check_routing( $sub->{subscriptionid} ); + $sub->{routingedit} = GetRoutingListsCount( $sub->{subscriptionid} ); } } diff --git a/installer/data/mysql/atomicupdate/bug_7957.perl b/installer/data/mysql/atomicupdate/bug_7957.perl new file mode 100644 index 0000000000..2f8ca30016 --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_7957.perl @@ -0,0 +1,67 @@ +use Modern::Perl; +use C4::Context; + +my $dbh = C4::Context->dbh; +$dbh->do("RENAME TABLE subscriptionroutinglist TO tmp_subscriptionroutinglist"); +$dbh->do(" + CREATE TABLE subscriptionroutinglist ( + routinglistid int(11) NOT NULL AUTO_INCREMENT, + subscriptionid int(11) NOT NULL, + title varchar(256) NOT NULL, + notes text default NULL, + PRIMARY KEY (routinglistid), + CONSTRAINT subscriptionroutinglist_ibfk_1 FOREIGN KEY (subscriptionid) + REFERENCES subscription (subscriptionid) ON DELETE CASCADE + ) ENGINE=InnoDB DEFAULT CHARSET=utf8 +"); +$dbh->do(" + CREATE TABLE subscriptionrouting ( + routinglistid int(11) NOT NULL, + borrowernumber int(11) NOT NULL, + ranking int(11) DEFAULT NULL, + PRIMARY KEY (routinglistid, borrowernumber), + CONSTRAINT subscriptionrouting_ibfk_1 FOREIGN KEY (routinglistid) + REFERENCES subscriptionroutinglist (routinglistid) ON DELETE CASCADE, + CONSTRAINT subscriptionrouting_ibfk_2 FOREIGN KEY (borrowernumber) + REFERENCES borrowers (borrowernumber) ON DELETE CASCADE + ) ENGINE=InnoDB DEFAULT CHARSET=utf8 +"); + +# Migrate data from old subscriptionroutinglist table +my $query = qq{ + SELECT serial.subscriptionid, serial.routingnotes + FROM tmp_subscriptionroutinglist + LEFT JOIN serial ON (tmp_subscriptionroutinglist.subscriptionid = serial.subscriptionid) + GROUP BY serial.subscriptionid +}; +my $sth = $dbh->prepare($query); +$sth->execute(); +my $results = $sth->fetchall_arrayref( {} ); +$query = qq{ + INSERT INTO subscriptionroutinglist (subscriptionid, title, notes) + VALUES (?, ?, ?) +}; +$sth = $dbh->prepare($query); +$query = qq{ + SELECT borrowernumber, ranking + FROM tmp_subscriptionroutinglist + WHERE subscriptionid = ? +}; +my $select_sth = $dbh->prepare($query); +$query = qq{ + INSERT INTO subscriptionrouting (routinglistid, borrowernumber, ranking) + VALUES(?, ?, ?) +}; +my $insert_sth = $dbh->prepare($query); +foreach ( @$results ) { + $sth->execute($_->{subscriptionid}, 'import', $_->{routingnotes}); + my $routinglistid = $dbh->last_insert_id(undef, undef, 'subscriptionroutinglist', undef); + $select_sth->execute($_->{subscriptionid}); + my $routings = $select_sth->fetchall_arrayref( {} ); + foreach (@$routings) { + $insert_sth->execute($routinglistid, $_->{borrowernumber}, $_->{ranking}); + } +} + +$dbh->do("DROP TABLE tmp_subscriptionroutinglist"); +$dbh->do("ALTER TABLE serial DROP COLUMN routingnotes"); diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 56d5d27d5a..ac75dd8799 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -1473,7 +1473,6 @@ CREATE TABLE `serial` ( -- issues related to subscriptions publisheddatetext varchar(100) default NULL, -- date published (descriptive) `claimdate` date default NULL, -- date claimed claims_count int(11) default 0, -- number of claims made related to this issue - `routingnotes` MEDIUMTEXT, -- notes from the routing list PRIMARY KEY (`serialid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; @@ -2056,22 +2055,38 @@ CREATE TABLE `subscriptionhistory` ( ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --- Table structure for table `subscriptionroutinglist` +-- Table structure for table subscriptionroutinglist -- -DROP TABLE IF EXISTS `subscriptionroutinglist`; -CREATE TABLE `subscriptionroutinglist` ( -- information related to the routing lists attached to subscriptions - `routingid` int(11) NOT NULL auto_increment, -- unique identifier assigned by Koha - `borrowernumber` int(11) NOT NULL, -- foreign key from the borrowers table, defines with patron is on the routing list - `ranking` int(11) default NULL, -- where the patron stands in line to receive the serial - `subscriptionid` int(11) NOT NULL, -- foreign key from the subscription table, defines which subscription this routing list is for - PRIMARY KEY (`routingid`), - UNIQUE (`subscriptionid`, `borrowernumber`), - CONSTRAINT `subscriptionroutinglist_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) - ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT `subscriptionroutinglist_ibfk_2` FOREIGN KEY (`subscriptionid`) REFERENCES `subscription` (`subscriptionid`) - ON DELETE CASCADE ON UPDATE CASCADE -) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS subscriptionroutinglist; +CREATE TABLE subscriptionroutinglist ( + routinglistid int(11) NOT NULL AUTO_INCREMENT, -- unique identifier assigned by Koha + subscriptionid int(11) NOT NULL, -- foreign key from the subscription table, + -- defines which subscription this routing list is for + title varchar(256) NOT NULL, -- title of this routing list + notes text default NULL, -- notes for this routing list + PRIMARY KEY (routinglistid), + CONSTRAINT subscriptionroutinglist_ibfk_1 FOREIGN KEY (subscriptionid) + REFERENCES subscription (subscriptionid) ON DELETE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; + +-- +-- Table structure for subscriptionrouting +-- + +DROP TABLE IF EXISTS subscriptionrouting; +CREATE TABLE subscriptionrouting ( + routinglistid int(11) NOT NULL, -- foreign key from the subscriptionroutinglist + -- table, defines which routing list is affected + borrowernumber int(11) NOT NULL, -- foreign key from the borrowers table, + -- defines which patron is on the routing list + ranking int(11) DEFAULT NULL, -- where the patron stands in line to receive the serial + PRIMARY KEY (routinglistid, borrowernumber), + CONSTRAINT subscriptionrouting_ibfk_1 FOREIGN KEY (routinglistid) + REFERENCES subscriptionroutinglists (routinglistid) ON DELETE CASCADE, + CONSTRAINT subscriptionrouting_ibfk_2 FOREIGN KEY (borrowernumber) + REFERENCES borrowers (borrowernumber) ON DELETE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -- Table structure for table `systempreferences` diff --git a/koha-tmpl/intranet-tmpl/prog/en/includes/serials-menu.inc b/koha-tmpl/intranet-tmpl/prog/en/includes/serials-menu.inc index 4a7fffd043..4797fff9aa 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/includes/serials-menu.inc +++ b/koha-tmpl/intranet-tmpl/prog/en/includes/serials-menu.inc @@ -6,9 +6,9 @@ [% UNLESS closed %] [% IF ( routing && CAN_user_serials_routing ) %] [% IF ( hasRouting ) %] -
  • Edit routing list
  • +
  • Edit routing list
  • [% ELSE %] -
  • Create routing list
  • +
  • Create routing list
  • [% END %] [% END %] [% END %] diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/members/routing-lists.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/members/routing-lists.tt index bc256ff6e0..468ed25dbb 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/members/routing-lists.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/members/routing-lists.tt @@ -30,54 +30,49 @@ [% SET routinglists = patron.get_routing_lists %]

    -[% UNLESS ( routinglists ) %] +[% IF routinglists.size == 0 %] 0 subscription routing lists -[% ELSIF ( routinglists.count == 1 ) %] -[% routinglists.count | html %] subscription routing list +[% ELSIF ( routinglists.size == 1 ) %] +[% routinglists.size | html %] subscription routing list [% ELSE %] -[% routinglists.count | html %] subscription routing lists +[% routinglists.size | html %] subscription routing lists [% END %]

    - -[% IF ( routinglists ) %] + [% IF ( routinglists.size > 0 ) %] - - - - - - - - + + + + + + + + [% FOREACH routinglist IN routinglists %] - - - - - - [% END %] - + + + + + + [% END %] +
    Subscription titlePositionRouting list
    Subscription titleRouting listActions
    - - [% routinglist.subscription.biblio.title | html %] - - - [% routinglist.ranking | html %] - - - Edit routing list - - - -
    + + + [% routinglist.bibliotitle | html %] + + + [% routinglist.title | html %] + + Edit routing list + + +
    -[% ELSE %] + [% ELSE %]

    Patron does not belong to any subscription routing lists.

    - - -[% END %] - + [% END %]
    diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/serials/routing-preview-slip.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/serials/routing-preview-slip.tt index 9caefcca57..acaced35c8 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/serials/routing-preview-slip.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/serials/routing-preview-slip.tt @@ -1,3 +1,4 @@ +[% USE Branches %] [% USE Koha %] [% USE raw %] [% SET footerjs = 1 %] @@ -12,36 +13,106 @@
    - - - - - - - - - - - - [% FOREACH memberloo IN memberloop %] - - - - +[% IF (missing_parameter_routinglistid or missing_parameter_serialid) %] +

    Routing list preview for [% title | html %]

    + + + [% IF (missing_parameter_routinglistid) %] + + + + [% ELSE %] + + + + [% END %] + + +[% ELSE %] + [% IF (error_no_item) %] +
    +

    Holds cannot be placed on this serial. There is no item attached to it.

    +
    [% END %] -

    [% libraryname | html %]

    Title: [% title | html %]
    [% issue | html %]
    NameDate due
    [% memberloo.name | html %] 
    + + [% IF branchcode %] + + + + [% END %] + + + + + + + + + + + [% FOREACH member IN memberloop %] + + + + + [% END %] +

    [% Branches.GetName(branchcode) | html %]

    + Title: [% title | html %]
    + [% serial.serialseq | html %] ([% serial.planneddate | html %]) +
    + Routing list: [% routinglisttitle | html %] +
    NameDate due
    [% member.surname | html %], [% member.firstname | html %] 
    -
    -

    [% Koha.Preference('RoutingListNote') | $raw %]

    -

    [% routingnotes | html %]

    -
    +
    +

    [% Koha.Preference('RoutingListNote') | $raw %]

    +

    [% routingnotes | html %]

    +
    - + [% IF (need_confirm) %] + [%# RoutingListAddReserves is ON %] + +
    + + + + + +
    + [% ELSE %] +
    + Print + Close +
    + [% END %] +[% END %] + +
    [% MACRO jsinclude BLOCK %] - [% END %] - [% INCLUDE 'intranet-bottom.inc' popup_window=1 %] diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/serials/routing-preview.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/serials/routing-preview.tt deleted file mode 100644 index 23af9667b0..0000000000 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/serials/routing-preview.tt +++ /dev/null @@ -1,68 +0,0 @@ -[% SET footerjs = 1 %] -[% INCLUDE 'doc-head-open.inc' %] -Koha › Serials › Preview routing list -[% INCLUDE 'doc-head-close.inc' %] - - - -[% INCLUDE 'header.inc' %] -[% INCLUDE 'serials-search.inc' %] - - - -
    -
    -
    -
    - -

    Preview routing list for [% title | html %]

    - -
    - - -
    -
      -
    1. Issue:[% issue | html %]
    2. -
    3. List member: - -[% FOREACH memberloo IN memberloop %] - -[% END %] -
      Name
      [% memberloo.surname | html %], [% memberloo.firstname | html %]
    4. -
    5. Notes:[% routingnotes | html %]
    6. -
    -
    - -
    - - -
    -
    - -
    -
    - -
    - -
    -
    - -[% MACRO jsinclude BLOCK %] - -[% END %] - -[% INCLUDE 'intranet-bottom.inc' %] diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/serials/routing.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/serials/routing.tt deleted file mode 100644 index 0652f90343..0000000000 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/serials/routing.tt +++ /dev/null @@ -1,135 +0,0 @@ -[% USE KohaDates %] -[% SET footerjs = 1 %] -[% INCLUDE 'doc-head-open.inc' %] -Koha › Serials › [% title | html %] › [% IF ( op ) %]Create routing list[% ELSE %]Edit routing list[% END %] -[% INCLUDE 'doc-head-close.inc' %] - - -[% INCLUDE 'header.inc' %] -[% INCLUDE 'serials-search.inc' %] - - - -
    -
    -
    -
    -[% INCLUDE 'blocking_errors.inc' %] - -[% IF ( op ) %] -

    Create routing list for [% title | html %]

    -[% ELSE %] -

    Edit routing list for [% title | html %]

    -[% END %] - -
    - - -
    -
      -
    1. - [% issue | html %]
    2. - -
    3. - Recipients: - [% IF memberloop %] - - - - - - [% USE m_loop = iterator(memberloop) %] - [% FOREACH member IN m_loop %] - - - - - [% END %] -
      NameRankDelete
      [% member.name | html %] - - Delete
      - [% END %] - -

      - Add recipients - [% IF memberloop %] - Delete all - [% END %] -

      -
    4. - -
    5. -
    - -
    -
    -
    - -
    -
    - -
    - -
    -
    -[% MACRO jsinclude BLOCK %] - -[% END %] - -[% INCLUDE 'intranet-bottom.inc' %] diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/serials/routinglist.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/serials/routinglist.tt new file mode 100644 index 0000000000..b04dd61040 --- /dev/null +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/serials/routinglist.tt @@ -0,0 +1,204 @@ +[% INCLUDE 'doc-head-open.inc' %] +Koha › Serials › Routing list +[% INCLUDE 'doc-head-close.inc' %] + + + + +[% INCLUDE 'header.inc' %] +[% INCLUDE 'serials-search.inc' %] + + + +
    + +
    +
    +
    + [% INCLUDE 'serials-toolbar.inc' %] + [% IF ( new ) %] +

    Create routing list

    + +
    + + + + + +
    + [% ELSE %] +

    Routing list '[% title %]'

    + Add a borrower + [% IF ( borrowers_loop ) %] + + [% ELSE %] +
    + [% END %] + + + + + + + + + [% FOREACH borrower IN borrowers_loop %] + + + + + + [% END %] + + + [% UNLESS ( borrowers_loop ) %] +

    There are no borrowers in this routing list.

    + [% END %] +
    + + + +
    + + +
    +
    +
    +
    +
    + + +
    + [% END %] +
    +
    +
    + +
    +
    +[% INCLUDE 'intranet-bottom.inc' %] diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/serials/routinglists.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/serials/routinglists.tt new file mode 100644 index 0000000000..46bcc8f10d --- /dev/null +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/serials/routinglists.tt @@ -0,0 +1,71 @@ +[% INCLUDE 'doc-head-open.inc' %] +Koha › Serials › Routing lists +[% INCLUDE 'doc-head-close.inc' %] + + + + +[% INCLUDE 'header.inc' %] +[% INCLUDE 'serials-search.inc' %] + + + +
    + +
    +
    +
    + [% INCLUDE 'serials-toolbar.inc' %] +

    Routing lists for [% title %]

    + + New routing list + + [% IF ( routinglists_loop ) %] + + + + + + + + + + + [% FOREACH routinglist IN routinglists_loop %] + + + + + + + [% END %] + +
    TitleNo. of borrowersNotes 
    [% routinglist.title %][% routinglist.borrowers.size || 0 %][% routinglist.notes %] + Preview | + Export | + Delete
    + [% ELSE %] +

    There are no routing lists for this subscription.

    + [% END %] + +
    +
    +
    + +
    +
    +[% INCLUDE 'intranet-bottom.inc' %] diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/serials/serials-collection.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/serials/serials-collection.tt index 7d4310d406..4a66903eb1 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/serials/serials-collection.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/serials/serials-collection.tt @@ -65,10 +65,10 @@ [% IF ( subscription.abouttoexpire ) || ( subscription.subscriptionexpired ) %][% ELSE %][% END %] [% UNLESS subscription.closed %] [% IF ( routing && CAN_user_serials_routing ) %] - [% IF ( subscription.hasRouting ) %] - Edit routing list + [% IF ( subscription.routinglistscount ) %] + Edit routing list [% ELSE %] - Create routing list + New routing list [% END %] [% END %] [% IF ( subscription.abouttoexpire ) %] Renew @@ -242,7 +242,7 @@ [% IF ( routing ) %] - Print list + Print list [% END %] @@ -280,8 +280,8 @@