X-Git-Url: http://git.rot13.org/?a=blobdiff_plain;f=C4%2FSerials.pm;h=35d62c9605ad4edcff291ce1fc181c796ed12834;hb=1ea1504c30c5c34dd763027caee55dcf359e94cf;hp=7c4bf82328262569a8f874adc98cc0409e56a16c;hpb=dc3dd3ede6f179efb976a3c3a761c836cd87ebc3;p=koha.git diff --git a/C4/Serials.pm b/C4/Serials.pm index 7c4bf82328..35d62c9605 100644 --- a/C4/Serials.pm +++ b/C4/Serials.pm @@ -5,58 +5,83 @@ package C4::Serials; # # 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 2 of the License, or (at your option) any later -# version. +# 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. +# 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, write to the Free Software Foundation, Inc., -# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA. +# You should have received a copy of the GNU General Public License +# along with Koha; if not, see . use Modern::Perl; use C4::Auth qw(haspermission); use C4::Context; -use C4::Dates qw(format_date format_date_in_iso); +use DateTime; use Date::Calc qw(:all); -use POSIX qw(strftime setlocale LC_TIME); +use POSIX qw(strftime); use C4::Biblio; use C4::Log; # logaction use C4::Debug; use C4::Serials::Frequency; use C4::Serials::Numberpattern; - -use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS); +use Koha::AdditionalField; +use Koha::DateUtils; +use Koha::Serial; +use Koha::Subscriptions; +use Koha::Subscription::Histories; + +use vars qw(@ISA @EXPORT @EXPORT_OK %EXPORT_TAGS); + +# Define statuses +use constant { + EXPECTED => 1, + ARRIVED => 2, + LATE => 3, + MISSING => 4, + MISSING_NEVER_RECIEVED => 41, + MISSING_SOLD_OUT => 42, + MISSING_DAMAGED => 43, + MISSING_LOST => 44, + NOT_ISSUED => 5, + DELETED => 6, + CLAIMED => 7, + STOPPED => 8, +}; + +use constant MISSING_STATUSES => ( + MISSING, MISSING_NEVER_RECIEVED, + MISSING_SOLD_OUT, MISSING_DAMAGED, + MISSING_LOST +); BEGIN { - $VERSION = 3.07.00.049; # set version for version checking require Exporter; @ISA = qw(Exporter); @EXPORT = qw( - &NewSubscription &ModSubscription &DelSubscription &GetSubscriptions + &NewSubscription &ModSubscription &DelSubscription &GetSubscription &CountSubscriptionFromBiblionumber &GetSubscriptionsFromBiblionumber &SearchSubscriptions &GetFullSubscriptionsFromBiblionumber &GetFullSubscription &ModSubscriptionHistory &HasSubscriptionStrictlyExpired &HasSubscriptionExpired &GetExpirationDate &abouttoexpire &GetSubscriptionHistoryFromSubscriptionId - &GetNextSeq &GetSeq &NewIssue &ItemizeSerials &GetSerials + &GetNextSeq &GetSeq &NewIssue &GetSerials &GetLatestSerials &ModSerialStatus &GetNextDate &GetSerials2 - &ReNewSubscription &GetLateIssues &GetLateOrMissingIssues + &ReNewSubscription &GetLateOrMissingIssues &GetSerialInformation &AddItem2Serial &PrepareSerialsData &GetNextExpected &ModNextExpected - &UpdateClaimdateIssues &GetSuppliersWithLateIssues &getsupplierbyserialid &GetDistributedTo &SetDistributedTo &getroutinglist &delroutingmember &addroutingmember &reorder_members - &check_routing &updateClaim &removeMissingIssue + &check_routing &updateClaim &CountIssues HasItems &GetSubscriptionsFromBorrower @@ -94,79 +119,22 @@ the array is in name order sub GetSuppliersWithLateIssues { my $dbh = C4::Context->dbh; + my $statuses = join(',', ( LATE, MISSING_STATUSES, CLAIMED ) ); my $query = qq| - SELECT DISTINCT id, name + SELECT DISTINCT id, name FROM subscription LEFT JOIN serial ON serial.subscriptionid=subscription.subscriptionid LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id WHERE id > 0 AND ( (planneddate < now() AND serial.status=1) - OR serial.STATUS = 3 OR serial.STATUS = 4 + OR serial.STATUS IN ( $statuses ) ) AND subscription.closed = 0 ORDER BY name|; return $dbh->selectall_arrayref($query, { Slice => {} }); } -=head2 GetLateIssues - -@issuelist = GetLateIssues($supplierid) - -this function selects late issues from the database - -return : -the issuelist as an array. Each element of this array contains a hashi_ref containing -name,title,planneddate,serialseq,serial.subscriptionid from tables : subscription, serial & biblio - -=cut - -sub GetLateIssues { - my ($supplierid) = @_; - - return unless ($supplierid); - - my $dbh = C4::Context->dbh; - my $sth; - if ($supplierid) { - my $query = qq| - SELECT name,title,planneddate,serialseq,serial.subscriptionid - FROM subscription - LEFT JOIN serial ON subscription.subscriptionid = serial.subscriptionid - LEFT JOIN biblio ON biblio.biblionumber = subscription.biblionumber - LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id - WHERE ((planneddate < now() AND serial.STATUS =1) OR serial.STATUS = 3) - AND subscription.aqbooksellerid=? - AND subscription.closed = 0 - ORDER BY title - |; - $sth = $dbh->prepare($query); - $sth->execute($supplierid); - } else { - my $query = qq| - SELECT name,title,planneddate,serialseq,serial.subscriptionid - FROM subscription - LEFT JOIN serial ON subscription.subscriptionid = serial.subscriptionid - LEFT JOIN biblio ON biblio.biblionumber = subscription.biblionumber - LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id - WHERE ((planneddate < now() AND serial.STATUS =1) OR serial.STATUS = 3) - AND subscription.closed = 0 - ORDER BY title - |; - $sth = $dbh->prepare($query); - $sth->execute; - } - my @issuelist; - my $last_title; - while ( my $line = $sth->fetchrow_hashref ) { - $line->{title} = "" if $last_title and $line->{title} eq $last_title; - $last_title = $line->{title} if ( $line->{title} ); - $line->{planneddate} = format_date( $line->{planneddate} ); - push @issuelist, $line; - } - return @issuelist; -} - =head2 GetSubscriptionHistoryFromSubscriptionId $history = GetSubscriptionHistoryFromSubscriptionId($subscriptionid); @@ -292,30 +260,6 @@ sub AddItem2Serial { return $rq->rows; } -=head2 UpdateClaimdateIssues - -UpdateClaimdateIssues($serialids,[$date]); - -Update Claimdate for issues in @$serialids list with date $date -(Take Today if none) - -=cut - -sub UpdateClaimdateIssues { - my ( $serialids, $date ) = @_; - - return unless ($serialids); - - my $dbh = C4::Context->dbh; - $date = strftime( "%Y-%m-%d", localtime ) unless ($date); - my $query = " - UPDATE serial SET claimdate = ?, status = 7 - WHERE serialid in (" . join( ",", map { '?' } @$serialids ) . ")"; - my $rq = $dbh->prepare($query); - $rq->execute($date, @$serialids); - return $rq->rows; -} - =head2 GetSubscription $subs = GetSubscription($subscriptionid) @@ -346,7 +290,16 @@ sub GetSubscription { my $sth = $dbh->prepare($query); $sth->execute($subscriptionid); my $subscription = $sth->fetchrow_hashref; + $subscription->{cannotedit} = not can_edit_subscription( $subscription ); + + # Add additional fields to the subscription into a new key "additional_fields" + my $additional_field_values = Koha::AdditionalField->fetch_all_values({ + tablename => 'subscription', + record_id => $subscriptionid, + }); + $subscription->{additional_fields} = $additional_field_values->{$subscriptionid}; + return $subscription; } @@ -368,6 +321,7 @@ sub GetFullSubscription { serial.serialseq, serial.planneddate, serial.publisheddate, + serial.publisheddatetext, serial.status, serial.notes as notes, year(IF(serial.publisheddate="00-00-0000",serial.planneddate,serial.publisheddate)) as year, @@ -419,13 +373,15 @@ sub PrepareSerialsData { foreach my $subs (@{$lines}) { for my $datefield ( qw(publisheddate planneddate) ) { - # handle both undef and undef returned as 0000-00-00 - if (!defined $subs->{$datefield} or $subs->{$datefield}=~m/^00/) { - $subs->{$datefield} = 'XXX'; + # handle 0000-00-00 dates + if (defined $subs->{$datefield} and $subs->{$datefield} =~ m/^00/) { + $subs->{$datefield} = undef; } } $subs->{ "status" . $subs->{'status'} } = 1; - $subs->{"checked"} = $subs->{'status'} =~ /1|3|4|7/; + if ( grep { $_ == $subs->{status} } ( EXPECTED, LATE, MISSING_STATUSES, CLAIMED ) ) { + $subs->{"checked"} = 1; + } if ( $subs->{'year'} && $subs->{'year'} ne "" ) { $year = $subs->{'year'}; @@ -484,9 +440,9 @@ sub GetSubscriptionsFromBiblionumber { $sth->execute($biblionumber); my @res; while ( my $subs = $sth->fetchrow_hashref ) { - $subs->{startdate} = format_date( $subs->{startdate} ); - $subs->{histstartdate} = format_date( $subs->{histstartdate} ); - $subs->{histenddate} = format_date( $subs->{histenddate} ); + $subs->{startdate} = output_pref( { dt => dt_from_string( $subs->{startdate} ), dateonly => 1 } ); + $subs->{histstartdate} = output_pref( { dt => dt_from_string( $subs->{histstartdate} ), dateonly => 1 } ); + $subs->{histenddate} = output_pref( { dt => dt_from_string( $subs->{histenddate} ), dateonly => 1 } ); $subs->{opacnote} =~ s/\n/\/g; $subs->{missinglist} =~ s/\n/\/g; $subs->{recievedlist} =~ s/\n/\/g; @@ -497,7 +453,7 @@ sub GetSubscriptionsFromBiblionumber { if ( $subs->{enddate} eq '0000-00-00' ) { $subs->{enddate} = ''; } else { - $subs->{enddate} = format_date( $subs->{enddate} ); + $subs->{enddate} = output_pref( { dt => dt_from_string( $subs->{enddate}), dateonly => 1 } ); } $subs->{'abouttoexpire'} = abouttoexpire( $subs->{'subscriptionid'} ); $subs->{'subscriptionexpired'} = HasSubscriptionExpired( $subs->{'subscriptionid'} ); @@ -522,6 +478,7 @@ sub GetFullSubscriptionsFromBiblionumber { serial.serialseq, serial.planneddate, serial.publisheddate, + serial.publisheddatetext, serial.status, serial.notes as notes, year(IF(serial.publisheddate="00-00-0000",serial.planneddate,serial.publisheddate)) as year, @@ -547,121 +504,70 @@ sub GetFullSubscriptionsFromBiblionumber { return $subscriptions; } -=head2 GetSubscriptions - -@results = GetSubscriptions($title,$ISSN,$ean,$biblionumber); -this function gets all subscriptions which have title like $title,ISSN like $ISSN,EAN like $ean and biblionumber like $biblionumber. -return: -a table of hashref. Each hash containt the subscription. - -=cut - -sub GetSubscriptions { - my ( $string, $issn, $ean, $biblionumber ) = @_; - - #return unless $title or $ISSN or $biblionumber; - my $dbh = C4::Context->dbh; - my $sth; - my $sql = qq( - SELECT subscriptionhistory.*, subscription.*, biblio.title,biblioitems.issn,biblio.biblionumber - FROM subscription - LEFT JOIN subscriptionhistory USING(subscriptionid) - LEFT JOIN biblio ON biblio.biblionumber = subscription.biblionumber - LEFT JOIN biblioitems ON biblio.biblionumber = biblioitems.biblionumber - ); - my @bind_params; - my $sqlwhere = q{}; - if ($biblionumber) { - $sqlwhere = " WHERE biblio.biblionumber=?"; - push @bind_params, $biblionumber; - } - if ($string) { - my @sqlstrings; - my @strings_to_search; - @strings_to_search = map { "%$_%" } split( / /, $string ); - foreach my $index (qw(biblio.title subscription.callnumber subscription.location subscription.notes subscription.internalnotes)) { - push @bind_params, @strings_to_search; - my $tmpstring = "AND $index LIKE ? " x scalar(@strings_to_search); - $debug && warn "$tmpstring"; - $tmpstring =~ s/^AND //; - push @sqlstrings, $tmpstring; - } - $sqlwhere .= ( $sqlwhere ? " AND " : " WHERE " ) . "((" . join( ") OR (", @sqlstrings ) . "))"; - } - if ($issn) { - my @sqlstrings; - my @strings_to_search; - @strings_to_search = map { "%$_%" } split( / /, $issn ); - foreach my $index ( qw(biblioitems.issn subscription.callnumber)) { - push @bind_params, @strings_to_search; - my $tmpstring = "OR $index LIKE ? " x scalar(@strings_to_search); - $debug && warn "$tmpstring"; - $tmpstring =~ s/^OR //; - push @sqlstrings, $tmpstring; - } - $sqlwhere .= ( $sqlwhere ? " AND " : " WHERE " ) . "((" . join( ") OR (", @sqlstrings ) . "))"; - } - if ($ean) { - my @sqlstrings; - my @strings_to_search; - @strings_to_search = map { "$_" } split( / /, $ean ); - foreach my $index ( qw(biblioitems.ean) ) { - push @bind_params, @strings_to_search; - my $tmpstring = "OR $index = ? " x scalar(@strings_to_search); - $debug && warn "$tmpstring"; - $tmpstring =~ s/^OR //; - push @sqlstrings, $tmpstring; - } - $sqlwhere .= ( $sqlwhere ? " AND " : " WHERE " ) . "((" . join( ") OR (", @sqlstrings ) . "))"; - } +=head2 SearchSubscriptions - $sql .= "$sqlwhere ORDER BY title"; - $debug and warn "GetSubscriptions query: $sql params : ", join( " ", @bind_params ); - $sth = $dbh->prepare($sql); - $sth->execute(@bind_params); - my $subscriptions = $sth->fetchall_arrayref( {} ); - for my $subscription ( @$subscriptions ) { - $subscription->{cannotedit} = not can_edit_subscription( $subscription ); - } - return @$subscriptions; -} + @results = SearchSubscriptions($args); -=head2 SearchSubscriptions +This function returns a list of hashrefs, one for each subscription +that meets the conditions specified by the $args hashref. -@results = SearchSubscriptions($args); -$args is a hashref. Its keys can be contained: title, issn, ean, publisher, bookseller and branchcode +The valid search fields are: -this function gets all subscriptions which have title like $title, ISSN like $issn, EAN like $ean, publisher like $publisher, bookseller like $bookseller AND branchcode eq $branch. + biblionumber + title + issn + ean + callnumber + location + publisher + bookseller + branch + expiration_date + closed -return: -a table of hashref. Each hash containt the subscription. +The expiration_date search field is special; it specifies the maximum +subscription expiration date. =cut sub SearchSubscriptions { my ( $args ) = @_; - my $query = qq{ + my $additional_fields = $args->{additional_fields} // []; + my $matching_record_ids_for_additional_fields = []; + if ( @$additional_fields ) { + $matching_record_ids_for_additional_fields = Koha::AdditionalField->get_matching_record_ids({ + fields => $additional_fields, + tablename => 'subscription', + exact_match => 0, + }); + return () unless @$matching_record_ids_for_additional_fields; + } + + my $query = q| SELECT subscription.notes AS publicnotes, - subscription.*, subscriptionhistory.*, + subscription.*, biblio.notes AS biblionotes, biblio.title, biblio.author, + biblio.biblionumber, biblioitems.issn FROM subscription LEFT JOIN subscriptionhistory USING(subscriptionid) LEFT JOIN biblio ON biblio.biblionumber = subscription.biblionumber LEFT JOIN biblioitems ON biblioitems.biblionumber = subscription.biblionumber LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id - }; + |; + $query .= q| WHERE 1|; my @where_strs; my @where_args; if( $args->{biblionumber} ) { push @where_strs, "biblio.biblionumber = ?"; push @where_args, $args->{biblionumber}; } + if( $args->{title} ){ my @words = split / /, $args->{title}; my (@strs, @args); @@ -682,6 +588,10 @@ sub SearchSubscriptions { push @where_strs, "biblioitems.ean LIKE ?"; push @where_args, "%$args->{ean}%"; } + if ( $args->{callnumber} ) { + push @where_strs, "subscription.callnumber LIKE ?"; + push @where_args, "%$args->{callnumber}%"; + } if( $args->{publisher} ){ push @where_strs, "biblioitems.publishercode LIKE ?"; push @where_args, "%$args->{publisher}%"; @@ -694,25 +604,44 @@ sub SearchSubscriptions { push @where_strs, "subscription.branchcode = ?"; push @where_args, "$args->{branch}"; } + if ( $args->{location} ) { + push @where_strs, "subscription.location = ?"; + push @where_args, "$args->{location}"; + } + if ( $args->{expiration_date} ) { + push @where_strs, "subscription.enddate <= ?"; + push @where_args, "$args->{expiration_date}"; + } if( defined $args->{closed} ){ push @where_strs, "subscription.closed = ?"; push @where_args, "$args->{closed}"; } + if(@where_strs){ - $query .= " WHERE " . join(" AND ", @where_strs); + $query .= ' AND ' . join(' AND ', @where_strs); + } + if ( @$additional_fields ) { + $query .= ' AND subscriptionid IN (' + . join( ', ', @$matching_record_ids_for_additional_fields ) + . ')'; } + $query .= " ORDER BY " . $args->{orderby} if $args->{orderby}; + my $dbh = C4::Context->dbh; my $sth = $dbh->prepare($query); $sth->execute(@where_args); - my $results = $sth->fetchall_arrayref( {} ); - $sth->finish; + my $results = $sth->fetchall_arrayref( {} ); for my $subscription ( @$results ) { $subscription->{cannotedit} = not can_edit_subscription( $subscription ); - $subscription->{cannotdisplay} = - ( C4::Context->preference("IndependentBranches") - and $subscription->{branchcode} ne C4::Context->userenv->{'branch'} ) ? 1 : 0; + $subscription->{cannotdisplay} = not can_show_subscription( $subscription ); + + my $additional_field_values = Koha::AdditionalField->fetch_all_values({ + record_id => $subscription->{subscriptionid}, + tablename => 'subscription' + }); + $subscription->{additional_fields} = $additional_field_values->{$subscription->{subscriptionid}}; } return @$results; @@ -741,9 +670,11 @@ sub GetSerials { my $counter = 0; $count = 5 unless ($count); my @serials; - my $query = "SELECT serialid,serialseq, status, publisheddate, planneddate,notes, routingnotes + my $statuses = join( ',', ( ARRIVED, MISSING_STATUSES, NOT_ISSUED ) ); + my $query = "SELECT serialid,serialseq, status, publisheddate, + publisheddatetext, planneddate,notes, routingnotes FROM serial - WHERE subscriptionid = ? AND status NOT IN (2,4,5) + WHERE subscriptionid = ? AND status NOT IN ( $statuses ) ORDER BY IF(publisheddate<>'0000-00-00',publisheddate,planneddate) DESC"; my $sth = $dbh->prepare($query); $sth->execute($subscriptionid); @@ -752,7 +683,7 @@ sub GetSerials { $line->{ "status" . $line->{status} } = 1; # fills a "statusX" value, used for template status select list for my $datefield ( qw( planneddate publisheddate) ) { if ($line->{$datefield} && $line->{$datefield}!~m/^00/) { - $line->{$datefield} = format_date( $line->{$datefield}); + $line->{$datefield} = output_pref( { dt => dt_from_string( $line->{$datefield} ), dateonly => 1 } ); } else { $line->{$datefield} = q{}; } @@ -761,10 +692,11 @@ sub GetSerials { } # OK, now add the last 5 issues arrives/missing - $query = "SELECT serialid,serialseq, status, planneddate, publisheddate,notes, routingnotes + $query = "SELECT serialid,serialseq, status, planneddate, publisheddate, + publisheddatetext, notes, routingnotes FROM serial WHERE subscriptionid = ? - AND (status in (2,4,5)) + AND status IN ( $statuses ) ORDER BY IF(publisheddate<>'0000-00-00',publisheddate,planneddate) DESC "; $sth = $dbh->prepare($query); @@ -774,7 +706,7 @@ sub GetSerials { $line->{ "status" . $line->{status} } = 1; # fills a "statusX" value, used for template status select list for my $datefield ( qw( planneddate publisheddate) ) { if ($line->{$datefield} && $line->{$datefield}!~m/^00/) { - $line->{$datefield} = format_date( $line->{$datefield}); + $line->{$datefield} = output_pref( { dt => dt_from_string( $line->{$datefield} ), dateonly => 1 } ); } else { $line->{$datefield} = q{}; } @@ -792,23 +724,28 @@ sub GetSerials { =head2 GetSerials2 -@serials = GetSerials2($subscriptionid,$status); +@serials = GetSerials2($subscriptionid,$statuses); this function returns every serial waited for a given subscription as well as the number of issues registered in the database (all types) this number is used to see if a subscription can be deleted (=it must have only 1 issue) +$statuses is an arrayref of statuses and is mandatory. + =cut sub GetSerials2 { - my ( $subscription, $status ) = @_; + my ( $subscription, $statuses ) = @_; - return unless ($subscription and $status); + return unless ($subscription and @$statuses); + + my $statuses_string = join ',', @$statuses; my $dbh = C4::Context->dbh; my $query = qq| - SELECT serialid,serialseq, status, planneddate, publisheddate,notes, routingnotes + SELECT serialid,serialseq, status, planneddate, publisheddate, + publisheddatetext, notes, routingnotes FROM serial - WHERE subscriptionid=$subscription AND status IN ($status) + WHERE subscriptionid=$subscription AND status IN ($statuses_string) ORDER BY publisheddate,serialid DESC |; $debug and warn "GetSerials2 query: $query"; @@ -820,11 +757,11 @@ sub GetSerials2 { $line->{ "status" . $line->{status} } = 1; # fills a "statusX" value, used for template status select list # Format dates for display for my $datefield ( qw( planneddate publisheddate ) ) { - if ($line->{$datefield} =~m/^00/) { + if (!defined($line->{$datefield}) || $line->{$datefield} =~m/^00/) { $line->{$datefield} = q{}; } else { - $line->{$datefield} = format_date( $line->{$datefield} ); + $line->{$datefield} = output_pref( { dt => dt_from_string( $line->{$datefield} ), dateonly => 1 } ); } } push @serials, $line; @@ -848,11 +785,11 @@ sub GetLatestSerials { my $dbh = C4::Context->dbh; - # status = 2 is "arrived" + my $statuses = join( ',', ( ARRIVED, MISSING_STATUSES ) ); my $strsth = "SELECT serialid,serialseq, status, planneddate, publisheddate, notes FROM serial WHERE subscriptionid = ? - AND (status =2 or status=4) + AND status IN ($statuses) ORDER BY publisheddate DESC LIMIT 0,$limit "; my $sth = $dbh->prepare($strsth); @@ -860,8 +797,8 @@ sub GetLatestSerials { my @serials; while ( my $line = $sth->fetchrow_hashref ) { $line->{ "status" . $line->{status} } = 1; # fills a "statusX" value, used for template status select list - $line->{"planneddate"} = format_date( $line->{"planneddate"} ); - $line->{"publisheddate"} = format_date( $line->{"publisheddate"} ); + $line->{planneddate} = output_pref( { dt => dt_from_string( $line->{planneddate} ), dateonly => 1 } ); + $line->{publisheddate} = output_pref( { dt => dt_from_string( $line->{publisheddate} ), dateonly => 1 } ); push @serials, $line; } @@ -899,7 +836,7 @@ $subscription is a hashref containing all the attributes of the table 'subscription'. $pattern is a hashref containing all the attributes of the table 'subscription_numberpatterns'. -$planneddate is a C4::Dates object. +$planneddate is a date string in iso format. This function get the next issue for the subscription given on input arg =cut @@ -1050,7 +987,9 @@ sub GetExpirationDate { # we don't do the same test if the subscription is based on X numbers or on X weeks/months $enddate = $startdate || $subscription->{startdate}; my @date = split( /-/, $enddate ); + return if ( scalar(@date) != 3 || not check_date(@date) ); + my $frequency = C4::Serials::Frequency::GetSubscriptionFrequency($subscription->{periodicity}); if ( $frequency and $frequency->{unit} ) { @@ -1130,53 +1069,10 @@ sub ModSubscriptionHistory { return $sth->rows; } -# Update missinglist field, used by ModSerialStatus -sub _update_missinglist { - my $subscriptionid = shift; - - my $dbh = C4::Context->dbh; - my @missingserials = GetSerials2($subscriptionid, "4,5"); - my $missinglist; - foreach (@missingserials) { - if($_->{'status'} == 4) { - $missinglist .= $_->{'serialseq'} . "; "; - } elsif($_->{'status'} == 5) { - $missinglist .= "not issued " . $_->{'serialseq'} . "; "; - } - } - $missinglist =~ s/; $//; - my $query = qq{ - UPDATE subscriptionhistory - SET missinglist = ? - WHERE subscriptionid = ? - }; - my $sth = $dbh->prepare($query); - $sth->execute($missinglist, $subscriptionid); -} - -# Update recievedlist field, used by ModSerialStatus -sub _update_receivedlist { - my $subscriptionid = shift; - - my $dbh = C4::Context->dbh; - my @receivedserials = GetSerials2($subscriptionid, "2"); - my $receivedlist; - foreach (@receivedserials) { - $receivedlist .= $_->{'serialseq'} . "; "; - } - $receivedlist =~ s/; $//; - my $query = qq{ - UPDATE subscriptionhistory - SET recievedlist = ? - WHERE subscriptionid = ? - }; - my $sth = $dbh->prepare($query); - $sth->execute($receivedlist, $subscriptionid); -} - =head2 ModSerialStatus -ModSerialStatus($serialid,$serialseq, $planneddate,$publisheddate,$status,$notes) + ModSerialStatus($serialid, $serialseq, $planneddate, $publisheddate, + $publisheddatetext, $status, $notes); This function modify the serial status. Serial status is a number.(eg 2 is "arrived") Note : if we change from "waited" to something else,then we will have to create a new "waited" entry @@ -1184,7 +1080,8 @@ Note : if we change from "waited" to something else,then we will have to create =cut sub ModSerialStatus { - my ( $serialid, $serialseq, $planneddate, $publisheddate, $status, $notes ) = @_; + my ($serialid, $serialseq, $planneddate, $publisheddate, $publisheddatetext, + $status, $notes) = @_; return unless ($serialid); @@ -1202,35 +1099,55 @@ sub ModSerialStatus { # change status & update subscriptionhistory my $val; - if ( $status == 6 ) { + if ( $status == DELETED ) { DelIssue( { 'serialid' => $serialid, 'subscriptionid' => $subscriptionid, 'serialseq' => $serialseq } ); } else { - unless ($frequency->{'unit'}) { - if ( not $planneddate or $planneddate eq '0000-00-00' ) { $planneddate = C4::Dates->new()->output('iso') }; - if ( not $publisheddate or $publisheddate eq '0000-00-00' ) { $publisheddate = C4::Dates->new()->output('iso') }; - } - my $query = 'UPDATE serial SET serialseq=?,publisheddate=?,planneddate=?,status=?,notes=? WHERE serialid = ?'; + my $query = ' + UPDATE serial + SET serialseq = ?, publisheddate = ?, publisheddatetext = ?, + planneddate = ?, status = ?, notes = ? + WHERE serialid = ? + '; $sth = $dbh->prepare($query); - $sth->execute( $serialseq, $publisheddate, $planneddate, $status, $notes, $serialid ); + $sth->execute( $serialseq, $publisheddate, $publisheddatetext, + $planneddate, $status, $notes, $serialid ); $query = "SELECT * FROM subscription WHERE subscriptionid = ?"; $sth = $dbh->prepare($query); $sth->execute($subscriptionid); my $val = $sth->fetchrow_hashref; unless ( $val->{manualhistory} ) { - if ( $status == 2 || ($oldstatus == 2 && $status != 2) ) { - _update_receivedlist($subscriptionid); + $query = "SELECT missinglist,recievedlist FROM subscriptionhistory WHERE subscriptionid=?"; + $sth = $dbh->prepare($query); + $sth->execute($subscriptionid); + my ( $missinglist, $recievedlist ) = $sth->fetchrow; + + if ( $status == ARRIVED || ($oldstatus == ARRIVED && $status != ARRIVED) ) { + $recievedlist .= "; $serialseq" + if ($recievedlist !~ /(^|;)\s*$serialseq(?=;|$)/); } - if($status == 4 || $status == 5 - || ($oldstatus == 4 && $status != 4) - || ($oldstatus == 5 && $status != 5)) { - _update_missinglist($subscriptionid); + + # in case serial has been previously marked as missing + if (grep /$status/, (EXPECTED, ARRIVED, LATE, CLAIMED)) { + $missinglist=~ s/(^|;)\s*$serialseq(?=;|$)//g; } + + $missinglist .= "; $serialseq" + if ( ( grep { $_ == $status } ( MISSING_STATUSES ) ) && ( $missinglist !~/(^|;)\s*$serialseq(?=;|$)/ ) ); + $missinglist .= "; not issued $serialseq" + if ( $status == NOT_ISSUED && $missinglist !~ /(^|;)\s*$serialseq(?=;|$)/ ); + + $query = "UPDATE subscriptionhistory SET recievedlist=?, missinglist=? WHERE subscriptionid=?"; + $sth = $dbh->prepare($query); + $recievedlist =~ s/^; //; + $missinglist =~ s/^; //; + $sth->execute( $recievedlist, $missinglist, $subscriptionid ); } } - # create new waited entry if needed (ie : was a "waited" and has changed) - if ( $oldstatus == 1 && $status != 1 ) { + # create new expected entry if needed (ie : was "expected" and has changed) + my $otherIssueExpected = scalar findSerialsByStatus(EXPECTED, $subscriptionid); + if ( !$otherIssueExpected && $oldstatus == EXPECTED && $status != EXPECTED ) { my $subscription = GetSubscription($subscriptionid); my $pattern = C4::Serials::Numberpattern::GetSubscriptionNumberpattern($subscription->{numberpattern}); @@ -1244,16 +1161,17 @@ sub ModSerialStatus { # next date (calculated from actual date & frequency parameters) my $nextpublisheddate = GetNextDate($subscription, $publisheddate, 1); my $nextpubdate = $nextpublisheddate; - NewIssue( $newserialseq, $subscriptionid, $subscription->{'biblionumber'}, 1, $nextpubdate, $nextpubdate ); $query = "UPDATE subscription SET lastvalue1=?, lastvalue2=?, lastvalue3=?, innerloop1=?, innerloop2=?, innerloop3=? WHERE subscriptionid = ?"; $sth = $dbh->prepare($query); $sth->execute( $newlastvalue1, $newlastvalue2, $newlastvalue3, $newinnerloop1, $newinnerloop2, $newinnerloop3, $subscriptionid ); + NewIssue( $newserialseq, $subscriptionid, $subscription->{'biblionumber'}, 1, $nextpubdate, $nextpubdate ); + # check if an alert must be sent... (= a letter is defined & status became "arrived" - if ( $subscription->{letter} && $status == 2 && $oldstatus != 2 ) { + if ( $subscription->{letter} && $status == ARRIVED && $oldstatus != ARRIVED ) { require C4::Letters; - C4::Letters::SendAlerts( 'issue', $subscription->{subscriptionid}, $subscription->{letter} ); + C4::Letters::SendAlerts( 'issue', $serialid, $subscription->{letter} ); } } @@ -1288,8 +1206,8 @@ sub GetNextExpected { }; my $sth = $dbh->prepare($query); - # Each subscription has only one 'expected' issue, with serial.status==1. - $sth->execute( $subscriptionid, 1 ); + # Each subscription has only one 'expected' issue. + $sth->execute( $subscriptionid, EXPECTED ); my $nextissue = $sth->fetchrow_hashref; if ( !$nextissue ) { $query = qq{ @@ -1336,8 +1254,8 @@ sub ModNextExpected { #FIXME: Would expect to only set planneddate, but we set both on new issue creation, so updating it here my $sth = $dbh->prepare('UPDATE serial SET planneddate=?,publisheddate=? WHERE subscriptionid=? AND status=?'); - # Each subscription has only one 'expected' issue, with serial.status==1. - $sth->execute( $date, $date, $subscriptionid, 1 ); + # Each subscription has only one 'expected' issue. + $sth->execute( $date, $date, $subscriptionid, EXPECTED ); return 0; } @@ -1429,7 +1347,7 @@ $subscriptionid = &NewSubscription($auser,branchcode,$aqbooksellerid,$cost,$aqbu $startdate,$periodicity,$numberlength,$weeklength,$monthlength, $lastvalue1,$innerloop1,$lastvalue2,$innerloop2,$lastvalue3,$innerloop3, $status, $notes, $letter, $firstacquidate, $irregularity, $numberpattern, - $callnumber, $hemisphere, $manualhistory, $internalnotes, $serialsadditems, + $locale, $callnumber, $manualhistory, $internalnotes, $serialsadditems, $staffdisplaycount, $opacdisplaycount, $graceperiod, $location, $enddate, $skip_serialseq); Create a new subscription with value given on input args. @@ -1489,25 +1407,32 @@ sub NewSubscription { # then create the 1st expected number $query = qq( INSERT INTO subscriptionhistory - (biblionumber, subscriptionid, histstartdate, opacnote, librariannote) - VALUES (?,?,?,?,?) + (biblionumber, subscriptionid, histstartdate) + VALUES (?,?,?) ); $sth = $dbh->prepare($query); - $sth->execute( $biblionumber, $subscriptionid, $startdate, $notes, $internalnotes ); + $sth->execute( $biblionumber, $subscriptionid, $startdate); # reread subscription to get a hash (for calculation of the 1st issue number) my $subscription = GetSubscription($subscriptionid); my $pattern = C4::Serials::Numberpattern::GetSubscriptionNumberpattern($subscription->{numberpattern}); # calculate issue number - my $serialseq = GetSeq($subscription, $pattern); - $query = qq| - INSERT INTO serial - (serialseq,subscriptionid,biblionumber,status, planneddate, publisheddate) - VALUES (?,?,?,?,?,?) - |; - $sth = $dbh->prepare($query); - $sth->execute( "$serialseq", $subscriptionid, $biblionumber, 1, $firstacquidate, $firstacquidate ); + my $serialseq = GetSeq($subscription, $pattern) || q{}; + + Koha::Serial->new( + { + serialseq => $serialseq, + serialseq_x => $subscription->{'lastvalue1'}, + serialseq_y => $subscription->{'lastvalue2'}, + serialseq_z => $subscription->{'lastvalue3'}, + subscriptionid => $subscriptionid, + biblionumber => $biblionumber, + status => EXPECTED, + planneddate => $firstacquidate, + publisheddate => $firstacquidate, + } + )->store(); logaction( "SERIAL", "ADD", $subscriptionid, "" ) if C4::Context->preference("SubscriptionLog"); @@ -1599,183 +1524,56 @@ returns the serial id =cut sub NewIssue { - my ( $serialseq, $subscriptionid, $biblionumber, $status, $planneddate, $publisheddate, $notes ) = @_; + my ( $serialseq, $subscriptionid, $biblionumber, $status, $planneddate, + $publisheddate, $publisheddatetext, $notes ) = @_; ### FIXME biblionumber CAN be provided by subscriptionid. So Do we STILL NEED IT ? return unless ($subscriptionid); - my $dbh = C4::Context->dbh; - my $query = qq| - INSERT INTO serial - (serialseq,subscriptionid,biblionumber,status,publisheddate,planneddate,notes) - VALUES (?,?,?,?,?,?,?) - |; - my $sth = $dbh->prepare($query); - $sth->execute( $serialseq, $subscriptionid, $biblionumber, $status, $publisheddate, $planneddate, $notes ); - my $serialid = $dbh->{'mysql_insertid'}; - $query = qq| - SELECT missinglist,recievedlist - FROM subscriptionhistory - WHERE subscriptionid=? - |; - $sth = $dbh->prepare($query); - $sth->execute($subscriptionid); - my ( $missinglist, $recievedlist ) = $sth->fetchrow; + my $schema = Koha::Database->new()->schema(); + + my $subscription = Koha::Subscriptions->find( $subscriptionid ); + + my $serial = Koha::Serial->new( + { + serialseq => $serialseq, + serialseq_x => $subscription->lastvalue1(), + serialseq_y => $subscription->lastvalue2(), + serialseq_z => $subscription->lastvalue3(), + subscriptionid => $subscriptionid, + biblionumber => $biblionumber, + status => $status, + planneddate => $planneddate, + publisheddate => $publisheddate, + publisheddatetext => $publisheddatetext, + notes => $notes, + } + )->store(); + + my $serialid = $serial->id(); - if ( $status == 2 ) { - ### TODO Add a feature that improves recognition and description. - ### As such count (serialseq) i.e. : N18,2(N19),N20 - ### Would use substr and index But be careful to previous presence of () - $recievedlist .= "; $serialseq" unless (index($recievedlist,$serialseq)>0); + my $subscription_history = Koha::Subscription::Histories->find($subscriptionid); + my $missinglist = $subscription_history->missinglist(); + my $recievedlist = $subscription_history->recievedlist(); + + if ( $status == ARRIVED ) { + ### TODO Add a feature that improves recognition and description. + ### As such count (serialseq) i.e. : N18,2(N19),N20 + ### Would use substr and index But be careful to previous presence of () + $recievedlist .= "; $serialseq" unless ( index( $recievedlist, $serialseq ) > 0 ); } - if ( $status == 4 ) { - $missinglist .= "; $serialseq" unless (index($missinglist,$serialseq)>0); + if ( grep { /^$status$/ } (MISSING_STATUSES) ) { + $missinglist .= "; $serialseq" unless ( index( $missinglist, $serialseq ) > 0 ); } - $query = qq| - UPDATE subscriptionhistory - SET recievedlist=?, missinglist=? - WHERE subscriptionid=? - |; - $sth = $dbh->prepare($query); + $recievedlist =~ s/^; //; $missinglist =~ s/^; //; - $sth->execute( $recievedlist, $missinglist, $subscriptionid ); - return $serialid; -} - -=head2 ItemizeSerials - -ItemizeSerials($serialid, $info); -$info is a hashref containing barcode branch, itemcallnumber, status, location -$serialid the serialid -return : -1 if the itemize is a succes. -0 and @error otherwise. @error containts the list of errors found. - -=cut - -sub ItemizeSerials { - my ( $serialid, $info ) = @_; - return unless ($serialid); - - my $now = POSIX::strftime( "%Y-%m-%d", localtime ); + $subscription_history->recievedlist($recievedlist); + $subscription_history->missinglist($missinglist); + $subscription_history->store(); - my $dbh = C4::Context->dbh; - my $query = qq| - SELECT * - FROM serial - WHERE serialid=? - |; - my $sth = $dbh->prepare($query); - $sth->execute($serialid); - my $data = $sth->fetchrow_hashref; - if ( C4::Context->preference("RoutingSerials") ) { - - # check for existing biblioitem relating to serial issue - my ( $count, @results ) = GetBiblioItemByBiblioNumber( $data->{'biblionumber'} ); - my $bibitemno = 0; - for ( my $i = 0 ; $i < $count ; $i++ ) { - if ( $results[$i]->{'volumeddesc'} eq $data->{'serialseq'} . ' (' . $data->{'planneddate'} . ')' ) { - $bibitemno = $results[$i]->{'biblioitemnumber'}; - last; - } - } - if ( $bibitemno == 0 ) { - my $sth = $dbh->prepare( "SELECT * FROM biblioitems WHERE biblionumber = ? ORDER BY biblioitemnumber DESC" ); - $sth->execute( $data->{'biblionumber'} ); - my $biblioitem = $sth->fetchrow_hashref; - $biblioitem->{'volumedate'} = $data->{planneddate}; - $biblioitem->{'volumeddesc'} = $data->{serialseq} . ' (' . format_date( $data->{'planneddate'} ) . ')'; - $biblioitem->{'dewey'} = $info->{itemcallnumber}; - } - } - - my $fwk = GetFrameworkCode( $data->{'biblionumber'} ); - if ( $info->{barcode} ) { - my @errors; - if ( is_barcode_in_use( $info->{barcode} ) ) { - push @errors, 'barcode_not_unique'; - } else { - my $marcrecord = MARC::Record->new(); - my ( $tag, $subfield ) = GetMarcFromKohaField( "items.barcode", $fwk ); - my $newField = MARC::Field->new( "$tag", '', '', "$subfield" => $info->{barcode} ); - $marcrecord->insert_fields_ordered($newField); - if ( $info->{branch} ) { - my ( $tag, $subfield ) = GetMarcFromKohaField( "items.homebranch", $fwk ); - - #warn "items.homebranch : $tag , $subfield"; - if ( $marcrecord->field($tag) ) { - $marcrecord->field($tag)->add_subfields( "$subfield" => $info->{branch} ); - } else { - my $newField = MARC::Field->new( "$tag", '', '', "$subfield" => $info->{branch} ); - $marcrecord->insert_fields_ordered($newField); - } - ( $tag, $subfield ) = GetMarcFromKohaField( "items.holdingbranch", $fwk ); - - #warn "items.holdingbranch : $tag , $subfield"; - if ( $marcrecord->field($tag) ) { - $marcrecord->field($tag)->add_subfields( "$subfield" => $info->{branch} ); - } else { - my $newField = MARC::Field->new( "$tag", '', '', "$subfield" => $info->{branch} ); - $marcrecord->insert_fields_ordered($newField); - } - } - if ( $info->{itemcallnumber} ) { - my ( $tag, $subfield ) = GetMarcFromKohaField( "items.itemcallnumber", $fwk ); - - if ( $marcrecord->field($tag) ) { - $marcrecord->field($tag)->add_subfields( "$subfield" => $info->{itemcallnumber} ); - } else { - my $newField = MARC::Field->new( "$tag", '', '', "$subfield" => $info->{itemcallnumber} ); - $marcrecord->insert_fields_ordered($newField); - } - } - if ( $info->{notes} ) { - my ( $tag, $subfield ) = GetMarcFromKohaField( "items.itemnotes", $fwk ); - - if ( $marcrecord->field($tag) ) { - $marcrecord->field($tag)->add_subfields( "$subfield" => $info->{notes} ); - } else { - my $newField = MARC::Field->new( "$tag", '', '', "$subfield" => $info->{notes} ); - $marcrecord->insert_fields_ordered($newField); - } - } - if ( $info->{location} ) { - my ( $tag, $subfield ) = GetMarcFromKohaField( "items.location", $fwk ); - - if ( $marcrecord->field($tag) ) { - $marcrecord->field($tag)->add_subfields( "$subfield" => $info->{location} ); - } else { - my $newField = MARC::Field->new( "$tag", '', '', "$subfield" => $info->{location} ); - $marcrecord->insert_fields_ordered($newField); - } - } - if ( $info->{status} ) { - my ( $tag, $subfield ) = GetMarcFromKohaField( "items.notforloan", $fwk ); - - if ( $marcrecord->field($tag) ) { - $marcrecord->field($tag)->add_subfields( "$subfield" => $info->{status} ); - } else { - my $newField = MARC::Field->new( "$tag", '', '', "$subfield" => $info->{status} ); - $marcrecord->insert_fields_ordered($newField); - } - } - if ( C4::Context->preference("RoutingSerials") ) { - my ( $tag, $subfield ) = GetMarcFromKohaField( "items.dateaccessioned", $fwk ); - if ( $marcrecord->field($tag) ) { - $marcrecord->field($tag)->add_subfields( "$subfield" => $now ); - } else { - my $newField = MARC::Field->new( "$tag", '', '', "$subfield" => $now ); - $marcrecord->insert_fields_ordered($newField); - } - } - require C4::Items; - C4::Items::AddItemFromMarc( $marcrecord, $data->{'biblionumber'} ); - return 1; - } - return ( 0, @errors ); - } + return $serialid; } =head2 HasSubscriptionStrictlyExpired @@ -1909,10 +1707,14 @@ this function deletes subscription which has $subscriptionid as id. sub DelSubscription { my ($subscriptionid) = @_; my $dbh = C4::Context->dbh; - $subscriptionid = $dbh->quote($subscriptionid); - $dbh->do("DELETE FROM subscription WHERE subscriptionid=$subscriptionid"); - $dbh->do("DELETE FROM subscriptionhistory WHERE subscriptionid=$subscriptionid"); - $dbh->do("DELETE FROM serial WHERE subscriptionid=$subscriptionid"); + $dbh->do("DELETE FROM subscription WHERE subscriptionid=?", undef, $subscriptionid); + $dbh->do("DELETE FROM subscriptionhistory WHERE subscriptionid=?", undef, $subscriptionid); + $dbh->do("DELETE FROM serial WHERE subscriptionid=?", undef, $subscriptionid); + + my $afs = Koha::AdditionalField->all({tablename => 'subscription'}); + foreach my $af (@$afs) { + $af->delete_values({record_id => $subscriptionid}); + } logaction( "SERIAL", "DELETE", $subscriptionid, "" ) if C4::Context->preference("SubscriptionLog"); } @@ -1967,7 +1769,7 @@ sub DelIssue { @issuelist = GetLateMissingIssues($supplierid,$serialid) -this function selects missing issues on database - where serial.status = 4 or serial.status=3 or planneddatedbh; + my $sth; my $byserial = ''; if ($serialid) { @@ -1991,95 +1794,65 @@ sub GetLateOrMissingIssues { } else { $order = "title"; } + my $missing_statuses_string = join ',', (MISSING_STATUSES); if ($supplierid) { $sth = $dbh->prepare( "SELECT serialid, aqbooksellerid, name, - biblio.title, planneddate, serialseq, - serial.status, serial.subscriptionid, claimdate, + biblio.title, biblioitems.issn, planneddate, serialseq, + serial.status, serial.subscriptionid, claimdate, claims_count, subscription.branchcode - FROM serial - LEFT JOIN subscription ON serial.subscriptionid=subscription.subscriptionid + FROM serial + LEFT JOIN subscription ON serial.subscriptionid=subscription.subscriptionid LEFT JOIN biblio ON subscription.biblionumber=biblio.biblionumber + LEFT JOIN biblioitems ON subscription.biblionumber=biblioitems.biblionumber LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id - WHERE subscription.subscriptionid = serial.subscriptionid - AND (serial.STATUS = 4 OR ((planneddate < now() AND serial.STATUS =1) OR serial.STATUS = 3 OR serial.STATUS = 7)) + WHERE subscription.subscriptionid = serial.subscriptionid + AND (serial.STATUS IN ($missing_statuses_string) OR ((planneddate < now() AND serial.STATUS = ?) OR serial.STATUS = ? OR serial.STATUS = ?)) AND subscription.aqbooksellerid=$supplierid $byserial ORDER BY $order" ); } else { $sth = $dbh->prepare( - "SELECT + "SELECT serialid, aqbooksellerid, name, biblio.title, planneddate, serialseq, - serial.status, serial.subscriptionid, claimdate, + serial.status, serial.subscriptionid, claimdate, claims_count, subscription.branchcode - FROM serial - LEFT JOIN subscription ON serial.subscriptionid=subscription.subscriptionid + FROM serial + LEFT JOIN subscription ON serial.subscriptionid=subscription.subscriptionid LEFT JOIN biblio ON subscription.biblionumber=biblio.biblionumber LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id - WHERE subscription.subscriptionid = serial.subscriptionid - AND (serial.STATUS = 4 OR ((planneddate < now() AND serial.STATUS =1) OR serial.STATUS = 3 OR serial.STATUS = 7)) + WHERE subscription.subscriptionid = serial.subscriptionid + AND (serial.STATUS IN ($missing_statuses_string) OR ((planneddate < now() AND serial.STATUS = ?) OR serial.STATUS = ? OR serial.STATUS = ?)) $byserial ORDER BY $order" ); } - $sth->execute; + $sth->execute( EXPECTED, LATE, CLAIMED ); my @issuelist; while ( my $line = $sth->fetchrow_hashref ) { if ($line->{planneddate} && $line->{planneddate} !~/^0+\-/) { - $line->{planneddate} = format_date( $line->{planneddate} ); + $line->{planneddateISO} = $line->{planneddate}; + $line->{planneddate} = output_pref( { dt => dt_from_string( $line->{"planneddate"} ), dateonly => 1 } ); } if ($line->{claimdate} && $line->{claimdate} !~/^0+\-/) { - $line->{claimdate} = format_date( $line->{claimdate} ); + $line->{claimdateISO} = $line->{claimdate}; + $line->{claimdate} = output_pref( { dt => dt_from_string( $line->{"claimdate"} ), dateonly => 1 } ); } $line->{"status".$line->{status}} = 1; - push @issuelist, $line; - } - return @issuelist; -} - -=head2 removeMissingIssue - -removeMissingIssue($subscriptionid) - -this function removes an issue from being part of the missing string in -subscriptionlist.missinglist column - -called when a missing issue is found from the serials-recieve.pl file -=cut - -sub removeMissingIssue { - my ( $sequence, $subscriptionid ) = @_; - - return unless ($sequence and $subscriptionid); + my $additional_field_values = Koha::AdditionalField->fetch_all_values({ + record_id => $line->{subscriptionid}, + tablename => 'subscription' + }); + %$line = ( %$line, additional_fields => $additional_field_values->{$line->{subscriptionid}} ); - my $dbh = C4::Context->dbh; - my $sth = $dbh->prepare("SELECT * FROM subscriptionhistory WHERE subscriptionid = ?"); - $sth->execute($subscriptionid); - my $data = $sth->fetchrow_hashref; - my $missinglist = $data->{'missinglist'}; - my $missinglistbefore = $missinglist; - - # warn $missinglist." before"; - $missinglist =~ s/($sequence)//; - - # warn $missinglist." after"; - if ( $missinglist ne $missinglistbefore ) { - $missinglist =~ s/\|\s\|/\|/g; - $missinglist =~ s/^\| //g; - $missinglist =~ s/\|$//g; - my $sth2 = $dbh->prepare( - "UPDATE subscriptionhistory - SET missinglist = ? - WHERE subscriptionid = ?" - ); - $sth2->execute( $missinglist, $subscriptionid ); + push @issuelist, $line; } - return; + return @issuelist; } =head2 updateClaim @@ -2093,15 +1866,19 @@ called from claims.pl file =cut sub updateClaim { - my ($serialid) = @_; - my $dbh = C4::Context->dbh; - my $sth = $dbh->prepare( - "UPDATE serial SET claimdate = now() - WHERE serialid = ? - " - ); - $sth->execute($serialid); - return; + my ($serialids) = @_; + return unless $serialids; + unless ( ref $serialids ) { + $serialids = [ $serialids ]; + } + my $dbh = C4::Context->dbh; + return $dbh->do(q| + UPDATE serial + SET claimdate = NOW(), + claims_count = claims_count + 1, + status = ? + WHERE serialid in (| . join( q|,|, (q|?|) x @$serialids ) . q|)|, + {}, CLAIMED, @$serialids ); } =head2 getsupplierbyserialid @@ -2373,18 +2150,26 @@ sub abouttoexpire { my $per = $subscription->{'periodicity'}; my $frequency = C4::Serials::Frequency::GetSubscriptionFrequency($per); if ($frequency and $frequency->{unit}){ + my $expirationdate = GetExpirationDate($subscriptionid); + my ($res) = $dbh->selectrow_array('select max(planneddate) from serial where subscriptionid = ?', undef, $subscriptionid); my $nextdate = GetNextDate($subscription, $res); - if(Date::Calc::Delta_Days( - split( /-/, $nextdate ), - split( /-/, $expirationdate ) - ) <= 0) { - return 1; + + # only compare dates if both dates exist. + if ($nextdate and $expirationdate) { + if(Date::Calc::Delta_Days( + split( /-/, $nextdate ), + split( /-/, $expirationdate ) + ) <= 0) { + return 1; + } } + } elsif ($subscription->{numberlength}>0) { return (countissuesfrom($subscriptionid,$subscription->{'startdate'}) >=$subscription->{numberlength}-1); } + return 0; } @@ -2484,6 +2269,89 @@ sub GetFictiveIssueNumber { return $issueno; } +sub _get_next_date_day { + my ($subscription, $freqdata, $year, $month, $day) = @_; + + if ($subscription->{countissuesperunit} + 1 > $freqdata->{issuesperunit}){ + ($year,$month,$day) = Add_Delta_Days($year,$month, $day , $freqdata->{unitsperissue} ); + $subscription->{countissuesperunit} = 1; + } else { + $subscription->{countissuesperunit}++; + } + + return ($year, $month, $day); +} + +sub _get_next_date_week { + my ($subscription, $freqdata, $year, $month, $day) = @_; + + my ($wkno, $yr) = Week_of_Year($year, $month, $day); + my $fa_dow = Day_of_Week(split /-/, $subscription->{firstacquidate}); + + if ($subscription->{countissuesperunit} + 1 > $freqdata->{issuesperunit}){ + $subscription->{countissuesperunit} = 1; + $wkno += $freqdata->{unitsperissue}; + if($wkno > 52){ + $wkno = $wkno % 52; + $yr++; + } + ($year,$month,$day) = Monday_of_Week($wkno, $yr); + ($year,$month,$day) = Add_Delta_Days($year, $month, $day, $fa_dow - 1); + } else { + # Try to guess the next day of week + my $delta_days = int((7 - ($fa_dow - 1)) / $freqdata->{issuesperunit}); + ($year,$month,$day) = Add_Delta_Days($year, $month, $day, $delta_days); + $subscription->{countissuesperunit}++; + } + + return ($year, $month, $day); +} + +sub _get_next_date_month { + my ($subscription, $freqdata, $year, $month, $day) = @_; + + my $fa_day; + (undef, undef, $fa_day) = split /-/, $subscription->{firstacquidate}; + + if ($subscription->{countissuesperunit} + 1 > $freqdata->{issuesperunit}){ + $subscription->{countissuesperunit} = 1; + ($year,$month,$day) = Add_Delta_YM($year,$month,$day, 0, + $freqdata->{unitsperissue}); + my $days_in_month = Days_in_Month($year, $month); + $day = $fa_day <= $days_in_month ? $fa_day : $days_in_month; + } else { + # Try to guess the next day in month + my $days_in_month = Days_in_Month($year, $month); + my $delta_days = int(($days_in_month - ($fa_day - 1)) / $freqdata->{issuesperunit}); + ($year,$month,$day) = Add_Delta_Days($year, $month, $day, $delta_days); + $subscription->{countissuesperunit}++; + } + + return ($year, $month, $day); +} + +sub _get_next_date_year { + my ($subscription, $freqdata, $year, $month, $day) = @_; + + my ($fa_year, $fa_month, $fa_day) = split /-/, $subscription->{firstacquidate}; + + if ($subscription->{countissuesperunit} + 1 > $freqdata->{issuesperunit}){ + $subscription->{countissuesperunit} = 1; + ($year) = Add_Delta_YM($year,$month,$day, $freqdata->{"unitsperissue"},0); + $month = $fa_month; + my $days_in_month = Days_in_Month($year, $month); + $day = $fa_day <= $days_in_month ? $fa_day : $days_in_month; + } else { + # Try to guess the next day in year + my $days_in_year = Days_in_Year($year,12); #Sum the days of all the months of this year + my $delta_days = int(($days_in_year - ($fa_day - 1)) / $freqdata->{issuesperunit}); + ($year,$month,$day) = Add_Delta_Days($year, $month, $day, $delta_days); + $subscription->{countissuesperunit}++; + } + + return ($year, $month, $day); +} + =head2 GetNextDate $resultdate = GetNextDate($publisheddate,$subscription) @@ -2499,13 +2367,15 @@ skipped then the returned date will be 2007-05-10 return : $resultdate - then next date in the sequence (ISO date) -Return $publisheddate if subscription is irregular +Return undef if subscription is irregular =cut sub GetNextDate { my ( $subscription, $publisheddate, $updatecount ) = @_; + return unless $subscription and $publisheddate; + my $freqdata = GetSubscriptionFrequency($subscription->{'periodicity'}); if ($freqdata->{'unit'}) { @@ -2516,10 +2386,12 @@ sub GetNextDate { # irreg1;irreg2;irreg3 # where irregX is the number of issue which will not be received # (the first issue takes the number 1, the 2nd the number 2 and so on) - my @irreg = split /;/, $subscription->{'irregularity'} ; my %irregularities; - foreach my $irregularity (@irreg) { - $irregularities{$irregularity} = 1; + if ( $subscription->{irregularity} ) { + my @irreg = split /;/, $subscription->{'irregularity'} ; + foreach my $irregularity (@irreg) { + $irregularities{$irregularity} = 1; + } } # Get the 'fictive' next issue number @@ -2530,107 +2402,41 @@ sub GetNextDate { my $unit = lc $freqdata->{'unit'}; if ($unit eq 'day') { while ($irregularities{$issueno}) { - if ($subscription->{'countissuesperunit'} + 1 > $freqdata->{'issuesperunit'}){ - ($year,$month,$day) = Add_Delta_Days($year,$month, $day , $freqdata->{'unitsperissue'} ); - $subscription->{'countissuesperunit'} = 1; - } else { - $subscription->{'countissuesperunit'}++; - } + ($year, $month, $day) = _get_next_date_day($subscription, + $freqdata, $year, $month, $day); $issueno++; } - if ($subscription->{'countissuesperunit'} + 1 > $freqdata->{'issuesperunit'}){ - ($year,$month,$day) = Add_Delta_Days($year,$month, $day , $freqdata->{"unitsperissue"} ); - $subscription->{'countissuesperunit'} = 1; - } else { - $subscription->{'countissuesperunit'}++; - } + ($year, $month, $day) = _get_next_date_day($subscription, $freqdata, + $year, $month, $day); } elsif ($unit eq 'week') { - my ($wkno, $yr) = Week_of_Year($year, $month, $day); while ($irregularities{$issueno}) { - if ($subscription->{'countissuesperunit'} + 1 > $freqdata->{'issuesperunit'}){ - $subscription->{'countissuesperunit'} = 1; - $wkno += $freqdata->{"unitsperissue"}; - if($wkno > 52){ - $wkno = $wkno % 52; - $yr++; - } - my $dow = Day_of_Week($year, $month, $day); - ($year,$month,$day) = Monday_of_Week($wkno, $yr); - if($freqdata->{'issuesperunit'} == 1) { - ($year, $month, $day) = Add_Delta_Days($year, $month, $day, $dow - 1); - } - } else { - $subscription->{'countissuesperunit'}++; - } + ($year, $month, $day) = _get_next_date_week($subscription, + $freqdata, $year, $month, $day); $issueno++; } - if ($subscription->{'countissuesperunit'} + 1 > $freqdata->{'issuesperunit'}){ - $subscription->{'countissuesperunit'} = 1; - $wkno += $freqdata->{"unitsperissue"}; - if($wkno > 52){ - $wkno = $wkno % 52 ; - $yr++; - } - my $dow = Day_of_Week($year, $month, $day); - ($year,$month,$day) = Monday_of_Week($wkno, $yr); - if($freqdata->{'issuesperunit'} == 1) { - ($year, $month, $day) = Add_Delta_Days($year, $month, $day, $dow - 1); - } - } else { - $subscription->{'countissuesperunit'}++; - } + ($year, $month, $day) = _get_next_date_week($subscription, + $freqdata, $year, $month, $day); } elsif ($unit eq 'month') { while ($irregularities{$issueno}) { - if ($subscription->{'countissuesperunit'} + 1 > $freqdata->{'issuesperunit'}){ - $subscription->{'countissuesperunit'} = 1; - ($year,$month,$day) = Add_Delta_YM($year,$month,$day, 0,$freqdata->{"unitsperissue"}); - unless($freqdata->{'issuesperunit'} == 1) { - $day = 1; # Jumping to the first day of month, because we don't know what day is expected - } - } else { - $subscription->{'countissuesperunit'}++; - } + ($year, $month, $day) = _get_next_date_month($subscription, + $freqdata, $year, $month, $day); $issueno++; } - if ($subscription->{'countissuesperunit'} + 1 > $freqdata->{'issuesperunit'}){ - $subscription->{'countissuesperunit'} = 1; - ($year,$month,$day) = Add_Delta_YM($year,$month,$day, 0,$freqdata->{"unitsperissue"}); - unless($freqdata->{'issuesperunit'} == 1) { - $day = 1; # Jumping to the first day of month, because we don't know what day is expected - } - } else { - $subscription->{'countissuesperunit'}++; - } + ($year, $month, $day) = _get_next_date_month($subscription, + $freqdata, $year, $month, $day); } elsif ($unit eq 'year') { while ($irregularities{$issueno}) { - if ($subscription->{'countissuesperunit'} + 1 > $freqdata->{'issuesperunit'}){ - $subscription->{'countissuesperunit'} = 1; - ($year,$month,$day) = Add_Delta_YM($year,$month,$day, $freqdata->{"unitsperissue"},0); - unless($freqdata->{'issuesperunit'} == 1) { - # Jumping to the first day of year, because we don't know what day is expected - $month = 1; - $day = 1; - } - } else { - $subscription->{'countissuesperunit'}++; - } + ($year, $month, $day) = _get_next_date_year($subscription, + $freqdata, $year, $month, $day); $issueno++; } - if ($subscription->{'countissuesperunit'} + 1 > $freqdata->{'issuesperunit'}){ - $subscription->{'countissuesperunit'} = 1; - ($year,$month,$day) = Add_Delta_YM($year,$month,$day, $freqdata->{"unitsperissue"},0); - unless($freqdata->{'issuesperunit'} == 1) { - # Jumping to the first day of year, because we don't know what day is expected - $month = 1; - $day = 1; - } - } else { - $subscription->{'countissuesperunit'}++; - } + ($year, $month, $day) = _get_next_date_year($subscription, + $freqdata, $year, $month, $day); } + if ($updatecount){ my $dbh = C4::Context->dbh; my $query = qq{ @@ -2641,11 +2447,9 @@ sub GetNextDate { my $sth = $dbh->prepare($query); $sth->execute($subscription->{'countissuesperunit'}, $subscription->{'subscriptionid'}); } + return sprintf("%04d-%02d-%02d", $year, $month, $day); } - else { - return $publisheddate; - } } =head2 _numeration @@ -2655,8 +2459,11 @@ sub GetNextDate { _numeration returns the string corresponding to $value in the num_type num_type can take : -dayname + -dayabrv -monthname + -monthabrv -season + -seasonabrv =cut #' @@ -2664,50 +2471,49 @@ num_type can take : sub _numeration { my ($value, $num_type, $locale) = @_; $value ||= 0; - my $initlocale = setlocale(LC_TIME); - if($locale and $locale ne $initlocale) { - $locale = setlocale(LC_TIME, $locale); - } - $locale ||= $initlocale; - my $string; $num_type //= ''; - given ($num_type) { - when (/^dayname$/) { - $value = $value % 7; - $string = POSIX::strftime("%A",0,0,0,0,0,0,$value); - } - when (/^monthname$/) { - $value = $value % 12; - $string = POSIX::strftime("%B",0,0,0,1,$value,0,0,0,0); - } - when (/^season$/) { - my $seasonlocale = ($locale) - ? (substr $locale,0,2) - : "en"; - my %seasons=( - "en" => - [qw(Spring Summer Fall Winter)], - "fr"=> - [qw(Printemps Été Automne Hiver)], - ); - $value = $value % 4; - $string = ($seasons{$seasonlocale}) - ? $seasons{$seasonlocale}->[$value] - : $seasons{'en'}->[$value]; - } - default { - $string = $value; - } - } - if($locale ne $initlocale) { - setlocale(LC_TIME, $initlocale); + $locale ||= 'en'; + my $string; + if ( $num_type =~ /^dayname$/ or $num_type =~ /^dayabrv$/ ) { + # 1970-11-01 was a Sunday + $value = $value % 7; + my $dt = DateTime->new( + year => 1970, + month => 11, + day => $value + 1, + locale => $locale, + ); + $string = $num_type =~ /^dayname$/ + ? $dt->strftime("%A") + : $dt->strftime("%a"); + } elsif ( $num_type =~ /^monthname$/ or $num_type =~ /^monthabrv$/ ) { + $value = $value % 12; + my $dt = DateTime->new( + year => 1970, + month => $value + 1, + locale => $locale, + ); + $string = $num_type =~ /^monthname$/ + ? $dt->strftime("%B") + : $dt->strftime("%b"); + } elsif ( $num_type =~ /^season$/ ) { + my @seasons= qw( Spring Summer Fall Winter ); + $value = $value % 4; + $string = $seasons[$value]; + } elsif ( $num_type =~ /^seasonabrv$/ ) { + my @seasonsabrv= qw( Spr Sum Fal Win ); + $value = $value % 4; + $string = $seasonsabrv[$value]; + } else { + $string = $value; } + return $string; } =head2 is_barcode_in_use -Returns number of occurence of the barcode in the items table +Returns number of occurrences of the barcode in the items table Can be used as a boolean test of whether the barcode has been deployed as yet @@ -2716,13 +2522,13 @@ been deployed as yet sub is_barcode_in_use { my $barcode = shift; my $dbh = C4::Context->dbh; - my $occurences = $dbh->selectall_arrayref( + my $occurrences = $dbh->selectall_arrayref( 'SELECT itemnumber from items where barcode = ?', {}, $barcode ); - return @{$occurences}; + return @{$occurrences}; } =head2 CloseSubscription @@ -2732,7 +2538,7 @@ sub CloseSubscription { my ( $subscriptionid ) = @_; return unless $subscriptionid; my $dbh = C4::Context->dbh; - my $sth = $dbh->prepare( qq{ + my $sth = $dbh->prepare( q{ UPDATE subscription SET closed = 1 WHERE subscriptionid = ? @@ -2740,13 +2546,13 @@ sub CloseSubscription { $sth->execute( $subscriptionid ); # Set status = missing when status = stopped - $sth = $dbh->prepare( qq{ + $sth = $dbh->prepare( q{ UPDATE serial - SET status = 8 + SET status = ? WHERE subscriptionid = ? - AND status = 1 + AND status = ? } ); - $sth->execute( $subscriptionid ); + $sth->execute( STOPPED, $subscriptionid, EXPECTED ); } =head2 ReopenSubscription @@ -2756,7 +2562,7 @@ sub ReopenSubscription { my ( $subscriptionid ) = @_; return unless $subscriptionid; my $dbh = C4::Context->dbh; - my $sth = $dbh->prepare( qq{ + my $sth = $dbh->prepare( q{ UPDATE subscription SET closed = 0 WHERE subscriptionid = ? @@ -2764,13 +2570,13 @@ sub ReopenSubscription { $sth->execute( $subscriptionid ); # Set status = expected when status = stopped - $sth = $dbh->prepare( qq{ + $sth = $dbh->prepare( q{ UPDATE serial - SET status = 1 + SET status = ? WHERE subscriptionid = ? - AND status = 8 + AND status = ? } ); - $sth->execute( $subscriptionid ); + $sth->execute( EXPECTED, $subscriptionid, STOPPED ); } =head2 subscriptionCurrentlyOnOrder @@ -2795,30 +2601,95 @@ sub subscriptionCurrentlyOnOrder { return $sth->fetchrow_array; } +=head2 can_claim_subscription + + $can = can_claim_subscription( $subscriptionid[, $userid] ); + +Return 1 if the subscription can be claimed by the current logged user (or a given $userid), else 0. + +=cut + +sub can_claim_subscription { + my ( $subscription, $userid ) = @_; + return _can_do_on_subscription( $subscription, $userid, 'claim_serials' ); +} + =head2 can_edit_subscription $can = can_edit_subscription( $subscriptionid[, $userid] ); -Return 1 if the subscription is editable by the current logged user (or a given $userid), else 0. +Return 1 if the subscription can be edited by the current logged user (or a given $userid), else 0. =cut sub can_edit_subscription { my ( $subscription, $userid ) = @_; + return _can_do_on_subscription( $subscription, $userid, 'edit_subscription' ); +} + +=head2 can_show_subscription + + $can = can_show_subscription( $subscriptionid[, $userid] ); + +Return 1 if the subscription can be shown by the current logged user (or a given $userid), else 0. + +=cut + +sub can_show_subscription { + my ( $subscription, $userid ) = @_; + return _can_do_on_subscription( $subscription, $userid, '*' ); +} + +sub _can_do_on_subscription { + my ( $subscription, $userid, $permission ) = @_; + return 0 unless C4::Context->userenv; my $flags = C4::Context->userenv->{flags}; $userid ||= C4::Context->userenv->{'id'}; - my $independent_branches = C4::Context->preference('IndependentBranches'); - return 1 unless $independent_branches; - if( $flags % 2 == 1 # superlibrarian - or C4::Auth::haspermission( $userid, {serials => 'superserials'}), - or C4::Auth::haspermission( $userid, {serials => 'edit_subscription'}), - or not defined $subscription->{branchcode} - or $subscription->{branchcode} eq '' - or $subscription->{branchcode} eq C4::Context->userenv->{'branch'} - ) { - return 1; - } - return 0; + + if ( C4::Context->preference('IndependentBranches') ) { + return 1 + if C4::Context->IsSuperLibrarian() + or + C4::Auth::haspermission( $userid, { serials => 'superserials' } ) + or ( + C4::Auth::haspermission( $userid, + { serials => $permission } ) + and ( not defined $subscription->{branchcode} + or $subscription->{branchcode} eq '' + or $subscription->{branchcode} eq + C4::Context->userenv->{'branch'} ) + ); + } + else { + return 1 + if C4::Context->IsSuperLibrarian() + or + C4::Auth::haspermission( $userid, { serials => 'superserials' } ) + or C4::Auth::haspermission( + $userid, { serials => $permission } + ), + ; + } + return 0; +} + +=head2 findSerialsByStatus + + @serials = findSerialsByStatus($status, $subscriptionid); + + Returns an array of serials matching a given status and subscription id. + +=cut + +sub findSerialsByStatus { + my ( $status, $subscriptionid ) = @_; + my $dbh = C4::Context->dbh; + my $query = q| SELECT * from serial + WHERE status = ? + AND subscriptionid = ? + |; + my $serials = $dbh->selectall_arrayref( $query, { Slice => {} }, $status, $subscriptionid ); + return @$serials; } 1;