3 # Copyright 2000-2002 Katipo Communications
4 # Parts Copyright 2010 Biblibre
6 # This file is part of Koha.
8 # Koha is free software; you can redistribute it and/or modify it under the
9 # terms of the GNU General Public License as published by the Free Software
10 # Foundation; either version 2 of the License, or (at your option) any later
13 # Koha is distributed in the hope that it will be useful, but WITHOUT ANY
14 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
15 # A PARTICULAR PURPOSE. See the GNU General Public License for more details.
17 # You should have received a copy of the GNU General Public License along
18 # with Koha; if not, write to the Free Software Foundation, Inc.,
19 # 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
23 use C4::Auth qw(haspermission);
25 use C4::Dates qw(format_date format_date_in_iso);
26 use Date::Calc qw(:all);
27 use POSIX qw(strftime setlocale LC_TIME);
29 use C4::Log; # logaction
31 use C4::Serials::Frequency;
32 use C4::Serials::Numberpattern;
34 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
37 $VERSION = 3.07.00.049; # set version for version checking
41 &NewSubscription &ModSubscription &DelSubscription &GetSubscriptions
42 &GetSubscription &CountSubscriptionFromBiblionumber &GetSubscriptionsFromBiblionumber
44 &GetFullSubscriptionsFromBiblionumber &GetFullSubscription &ModSubscriptionHistory
45 &HasSubscriptionStrictlyExpired &HasSubscriptionExpired &GetExpirationDate &abouttoexpire
46 &GetSubscriptionHistoryFromSubscriptionId
48 &GetNextSeq &GetSeq &NewIssue &ItemizeSerials &GetSerials
49 &GetLatestSerials &ModSerialStatus &GetNextDate &GetSerials2
50 &ReNewSubscription &GetLateIssues &GetLateOrMissingIssues
51 &GetSerialInformation &AddItem2Serial
52 &PrepareSerialsData &GetNextExpected &ModNextExpected
54 &UpdateClaimdateIssues
55 &GetSuppliersWithLateIssues &getsupplierbyserialid
56 &GetDistributedTo &SetDistributedTo
57 &getroutinglist &delroutingmember &addroutingmember
59 &check_routing &updateClaim &removeMissingIssue
62 &GetSubscriptionsFromBorrower
63 &subscriptionCurrentlyOnOrder
70 C4::Serials - Serials Module Functions
78 Functions for handling subscriptions, claims routing etc.
83 =head2 GetSuppliersWithLateIssues
85 $supplierlist = GetSuppliersWithLateIssues()
87 this function get all suppliers with late issues.
90 an array_ref of suppliers each entry is a hash_ref containing id and name
91 the array is in name order
95 sub GetSuppliersWithLateIssues {
96 my $dbh = C4::Context->dbh;
98 SELECT DISTINCT id, name
100 LEFT JOIN serial ON serial.subscriptionid=subscription.subscriptionid
101 LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id
104 (planneddate < now() AND serial.status=1)
105 OR serial.STATUS IN (3, 4, 41, 42, 43, 44)
107 AND subscription.closed = 0
109 return $dbh->selectall_arrayref($query, { Slice => {} });
114 @issuelist = GetLateIssues($supplierid)
116 this function selects late issues from the database
119 the issuelist as an array. Each element of this array contains a hashi_ref containing
120 name,title,planneddate,serialseq,serial.subscriptionid from tables : subscription, serial & biblio
125 my ($supplierid) = @_;
127 return unless ($supplierid);
129 my $dbh = C4::Context->dbh;
133 SELECT name,title,planneddate,serialseq,serial.subscriptionid
135 LEFT JOIN serial ON subscription.subscriptionid = serial.subscriptionid
136 LEFT JOIN biblio ON biblio.biblionumber = subscription.biblionumber
137 LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id
138 WHERE ((planneddate < now() AND serial.STATUS =1) OR serial.STATUS = 3)
139 AND subscription.aqbooksellerid=?
140 AND subscription.closed = 0
143 $sth = $dbh->prepare($query);
144 $sth->execute($supplierid);
147 SELECT name,title,planneddate,serialseq,serial.subscriptionid
149 LEFT JOIN serial ON subscription.subscriptionid = serial.subscriptionid
150 LEFT JOIN biblio ON biblio.biblionumber = subscription.biblionumber
151 LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id
152 WHERE ((planneddate < now() AND serial.STATUS =1) OR serial.STATUS = 3)
153 AND subscription.closed = 0
156 $sth = $dbh->prepare($query);
161 while ( my $line = $sth->fetchrow_hashref ) {
162 $line->{title} = "" if $last_title and $line->{title} eq $last_title;
163 $last_title = $line->{title} if ( $line->{title} );
164 $line->{planneddate} = format_date( $line->{planneddate} );
165 push @issuelist, $line;
170 =head2 GetSubscriptionHistoryFromSubscriptionId
172 $history = GetSubscriptionHistoryFromSubscriptionId($subscriptionid);
174 This function returns the subscription history as a hashref
178 sub GetSubscriptionHistoryFromSubscriptionId {
179 my ($subscriptionid) = @_;
181 return unless $subscriptionid;
183 my $dbh = C4::Context->dbh;
186 FROM subscriptionhistory
187 WHERE subscriptionid = ?
189 my $sth = $dbh->prepare($query);
190 $sth->execute($subscriptionid);
191 my $results = $sth->fetchrow_hashref;
197 =head2 GetSerialStatusFromSerialId
199 $sth = GetSerialStatusFromSerialId();
200 this function returns a statement handle
201 After this function, don't forget to execute it by using $sth->execute($serialid)
203 $sth = $dbh->prepare($query).
207 sub GetSerialStatusFromSerialId {
208 my $dbh = C4::Context->dbh;
214 return $dbh->prepare($query);
217 =head2 GetSerialInformation
220 $data = GetSerialInformation($serialid);
221 returns a hash_ref containing :
222 items : items marcrecord (can be an array)
224 subscription table field
225 + information about subscription expiration
229 sub GetSerialInformation {
231 my $dbh = C4::Context->dbh;
233 SELECT serial.*, serial.notes as sernotes, serial.status as serstatus,subscription.*,subscription.subscriptionid as subsid
234 FROM serial LEFT JOIN subscription ON subscription.subscriptionid=serial.subscriptionid
237 my $rq = $dbh->prepare($query);
238 $rq->execute($serialid);
239 my $data = $rq->fetchrow_hashref;
241 # create item information if we have serialsadditems for this subscription
242 if ( $data->{'serialsadditems'} ) {
243 my $queryitem = $dbh->prepare("SELECT itemnumber from serialitems where serialid=?");
244 $queryitem->execute($serialid);
245 my $itemnumbers = $queryitem->fetchall_arrayref( [0] );
247 if ( scalar(@$itemnumbers) > 0 ) {
248 foreach my $itemnum (@$itemnumbers) {
250 #It is ASSUMED that GetMarcItem ALWAYS WORK...
251 #Maybe GetMarcItem should return values on failure
252 $debug and warn "itemnumber :$itemnum->[0], bibnum :" . $data->{'biblionumber'};
253 my $itemprocessed = C4::Items::PrepareItemrecordDisplay( $data->{'biblionumber'}, $itemnum->[0], $data );
254 $itemprocessed->{'itemnumber'} = $itemnum->[0];
255 $itemprocessed->{'itemid'} = $itemnum->[0];
256 $itemprocessed->{'serialid'} = $serialid;
257 $itemprocessed->{'biblionumber'} = $data->{'biblionumber'};
258 push @{ $data->{'items'} }, $itemprocessed;
261 my $itemprocessed = C4::Items::PrepareItemrecordDisplay( $data->{'biblionumber'}, '', $data );
262 $itemprocessed->{'itemid'} = "N$serialid";
263 $itemprocessed->{'serialid'} = $serialid;
264 $itemprocessed->{'biblionumber'} = $data->{'biblionumber'};
265 $itemprocessed->{'countitems'} = 0;
266 push @{ $data->{'items'} }, $itemprocessed;
269 $data->{ "status" . $data->{'serstatus'} } = 1;
270 $data->{'subscriptionexpired'} = HasSubscriptionExpired( $data->{'subscriptionid'} ) && $data->{'status'} == 1;
271 $data->{'abouttoexpire'} = abouttoexpire( $data->{'subscriptionid'} );
272 $data->{cannotedit} = not can_edit_subscription( $data );
276 =head2 AddItem2Serial
278 $rows = AddItem2Serial($serialid,$itemnumber);
279 Adds an itemnumber to Serial record
280 returns the number of rows affected
285 my ( $serialid, $itemnumber ) = @_;
287 return unless ($serialid and $itemnumber);
289 my $dbh = C4::Context->dbh;
290 my $rq = $dbh->prepare("INSERT INTO `serialitems` SET serialid=? , itemnumber=?");
291 $rq->execute( $serialid, $itemnumber );
295 =head2 UpdateClaimdateIssues
297 UpdateClaimdateIssues($serialids,[$date]);
299 Update Claimdate for issues in @$serialids list with date $date
304 sub UpdateClaimdateIssues {
305 my ( $serialids, $date ) = @_;
307 return unless ($serialids);
309 my $dbh = C4::Context->dbh;
310 $date = strftime( "%Y-%m-%d", localtime ) unless ($date);
312 UPDATE serial SET claimdate = ?, status = 7
313 WHERE serialid in (" . join( ",", map { '?' } @$serialids ) . ")";
314 my $rq = $dbh->prepare($query);
315 $rq->execute($date, @$serialids);
319 =head2 GetSubscription
321 $subs = GetSubscription($subscriptionid)
322 this function returns the subscription which has $subscriptionid as id.
324 a hashref. This hash containts
325 subscription, subscriptionhistory, aqbooksellers.name, biblio.title
329 sub GetSubscription {
330 my ($subscriptionid) = @_;
331 my $dbh = C4::Context->dbh;
333 SELECT subscription.*,
334 subscriptionhistory.*,
335 aqbooksellers.name AS aqbooksellername,
336 biblio.title AS bibliotitle,
337 subscription.biblionumber as bibnum
339 LEFT JOIN subscriptionhistory ON subscription.subscriptionid=subscriptionhistory.subscriptionid
340 LEFT JOIN aqbooksellers ON subscription.aqbooksellerid=aqbooksellers.id
341 LEFT JOIN biblio ON biblio.biblionumber=subscription.biblionumber
342 WHERE subscription.subscriptionid = ?
345 $debug and warn "query : $query\nsubsid :$subscriptionid";
346 my $sth = $dbh->prepare($query);
347 $sth->execute($subscriptionid);
348 my $subscription = $sth->fetchrow_hashref;
349 $subscription->{cannotedit} = not can_edit_subscription( $subscription );
350 return $subscription;
353 =head2 GetFullSubscription
355 $array_ref = GetFullSubscription($subscriptionid)
356 this function reads the serial table.
360 sub GetFullSubscription {
361 my ($subscriptionid) = @_;
363 return unless ($subscriptionid);
365 my $dbh = C4::Context->dbh;
367 SELECT serial.serialid,
370 serial.publisheddate,
372 serial.notes as notes,
373 year(IF(serial.publisheddate="00-00-0000",serial.planneddate,serial.publisheddate)) as year,
374 aqbooksellers.name as aqbooksellername,
375 biblio.title as bibliotitle,
376 subscription.branchcode AS branchcode,
377 subscription.subscriptionid AS subscriptionid
379 LEFT JOIN subscription ON
380 (serial.subscriptionid=subscription.subscriptionid )
381 LEFT JOIN aqbooksellers on subscription.aqbooksellerid=aqbooksellers.id
382 LEFT JOIN biblio on biblio.biblionumber=subscription.biblionumber
383 WHERE serial.subscriptionid = ?
385 IF(serial.publisheddate="00-00-0000",serial.planneddate,serial.publisheddate) DESC,
386 serial.subscriptionid
388 $debug and warn "GetFullSubscription query: $query";
389 my $sth = $dbh->prepare($query);
390 $sth->execute($subscriptionid);
391 my $subscriptions = $sth->fetchall_arrayref( {} );
392 for my $subscription ( @$subscriptions ) {
393 $subscription->{cannotedit} = not can_edit_subscription( $subscription );
395 return $subscriptions;
398 =head2 PrepareSerialsData
400 $array_ref = PrepareSerialsData($serialinfomation)
401 where serialinformation is a hashref array
405 sub PrepareSerialsData {
408 return unless ($lines);
414 my $aqbooksellername;
418 my $previousnote = "";
420 foreach my $subs (@{$lines}) {
421 for my $datefield ( qw(publisheddate planneddate) ) {
422 # handle both undef and undef returned as 0000-00-00
423 if (!defined $subs->{$datefield} or $subs->{$datefield}=~m/^00/) {
424 $subs->{$datefield} = 'XXX';
427 $subs->{ "status" . $subs->{'status'} } = 1;
428 if ( grep { $_ == $subs->{status} } ( 1, 3, 4, 41, 42, 43, 44, 7 ) ) {
429 $subs->{"checked"} = 1;
432 if ( $subs->{'year'} && $subs->{'year'} ne "" ) {
433 $year = $subs->{'year'};
437 if ( $tmpresults{$year} ) {
438 push @{ $tmpresults{$year}->{'serials'} }, $subs;
440 $tmpresults{$year} = {
442 'aqbooksellername' => $subs->{'aqbooksellername'},
443 'bibliotitle' => $subs->{'bibliotitle'},
444 'serials' => [$subs],
449 foreach my $key ( sort { $b cmp $a } keys %tmpresults ) {
450 push @res, $tmpresults{$key};
455 =head2 GetSubscriptionsFromBiblionumber
457 $array_ref = GetSubscriptionsFromBiblionumber($biblionumber)
458 this function get the subscription list. it reads the subscription table.
460 reference to an array of subscriptions which have the biblionumber given on input arg.
461 each element of this array is a hashref containing
462 startdate, histstartdate,opacnote,missinglist,recievedlist,periodicity,status & enddate
466 sub GetSubscriptionsFromBiblionumber {
467 my ($biblionumber) = @_;
469 return unless ($biblionumber);
471 my $dbh = C4::Context->dbh;
473 SELECT subscription.*,
475 subscriptionhistory.*,
476 aqbooksellers.name AS aqbooksellername,
477 biblio.title AS bibliotitle
479 LEFT JOIN subscriptionhistory ON subscription.subscriptionid=subscriptionhistory.subscriptionid
480 LEFT JOIN aqbooksellers ON subscription.aqbooksellerid=aqbooksellers.id
481 LEFT JOIN biblio ON biblio.biblionumber=subscription.biblionumber
482 LEFT JOIN branches ON branches.branchcode=subscription.branchcode
483 WHERE subscription.biblionumber = ?
485 my $sth = $dbh->prepare($query);
486 $sth->execute($biblionumber);
488 while ( my $subs = $sth->fetchrow_hashref ) {
489 $subs->{startdate} = format_date( $subs->{startdate} );
490 $subs->{histstartdate} = format_date( $subs->{histstartdate} );
491 $subs->{histenddate} = format_date( $subs->{histenddate} );
492 $subs->{opacnote} =~ s/\n/\<br\/\>/g;
493 $subs->{missinglist} =~ s/\n/\<br\/\>/g;
494 $subs->{recievedlist} =~ s/\n/\<br\/\>/g;
495 $subs->{ "periodicity" . $subs->{periodicity} } = 1;
496 $subs->{ "numberpattern" . $subs->{numberpattern} } = 1;
497 $subs->{ "status" . $subs->{'status'} } = 1;
499 if ( $subs->{enddate} eq '0000-00-00' ) {
500 $subs->{enddate} = '';
502 $subs->{enddate} = format_date( $subs->{enddate} );
504 $subs->{'abouttoexpire'} = abouttoexpire( $subs->{'subscriptionid'} );
505 $subs->{'subscriptionexpired'} = HasSubscriptionExpired( $subs->{'subscriptionid'} );
506 $subs->{cannotedit} = not can_edit_subscription( $subs );
512 =head2 GetFullSubscriptionsFromBiblionumber
514 $array_ref = GetFullSubscriptionsFromBiblionumber($biblionumber)
515 this function reads the serial table.
519 sub GetFullSubscriptionsFromBiblionumber {
520 my ($biblionumber) = @_;
521 my $dbh = C4::Context->dbh;
523 SELECT serial.serialid,
526 serial.publisheddate,
528 serial.notes as notes,
529 year(IF(serial.publisheddate="00-00-0000",serial.planneddate,serial.publisheddate)) as year,
530 biblio.title as bibliotitle,
531 subscription.branchcode AS branchcode,
532 subscription.subscriptionid AS subscriptionid
534 LEFT JOIN subscription ON
535 (serial.subscriptionid=subscription.subscriptionid)
536 LEFT JOIN aqbooksellers on subscription.aqbooksellerid=aqbooksellers.id
537 LEFT JOIN biblio on biblio.biblionumber=subscription.biblionumber
538 WHERE subscription.biblionumber = ?
540 IF(serial.publisheddate="00-00-0000",serial.planneddate,serial.publisheddate) DESC,
541 serial.subscriptionid
543 my $sth = $dbh->prepare($query);
544 $sth->execute($biblionumber);
545 my $subscriptions = $sth->fetchall_arrayref( {} );
546 for my $subscription ( @$subscriptions ) {
547 $subscription->{cannotedit} = not can_edit_subscription( $subscription );
549 return $subscriptions;
552 =head2 GetSubscriptions
554 @results = GetSubscriptions($title,$ISSN,$ean,$biblionumber);
555 this function gets all subscriptions which have title like $title,ISSN like $ISSN,EAN like $ean and biblionumber like $biblionumber.
557 a table of hashref. Each hash containt the subscription.
561 sub GetSubscriptions {
562 my ( $string, $issn, $ean, $biblionumber ) = @_;
564 #return unless $title or $ISSN or $biblionumber;
565 my $dbh = C4::Context->dbh;
568 SELECT subscriptionhistory.*, subscription.*, biblio.title,biblioitems.issn,biblio.biblionumber
570 LEFT JOIN subscriptionhistory USING(subscriptionid)
571 LEFT JOIN biblio ON biblio.biblionumber = subscription.biblionumber
572 LEFT JOIN biblioitems ON biblio.biblionumber = biblioitems.biblionumber
577 $sqlwhere = " WHERE biblio.biblionumber=?";
578 push @bind_params, $biblionumber;
582 my @strings_to_search;
583 @strings_to_search = map { "%$_%" } split( / /, $string );
584 foreach my $index (qw(biblio.title subscription.callnumber subscription.location subscription.notes subscription.internalnotes)) {
585 push @bind_params, @strings_to_search;
586 my $tmpstring = "AND $index LIKE ? " x scalar(@strings_to_search);
587 $debug && warn "$tmpstring";
588 $tmpstring =~ s/^AND //;
589 push @sqlstrings, $tmpstring;
591 $sqlwhere .= ( $sqlwhere ? " AND " : " WHERE " ) . "((" . join( ") OR (", @sqlstrings ) . "))";
595 my @strings_to_search;
596 @strings_to_search = map { "%$_%" } split( / /, $issn );
597 foreach my $index ( qw(biblioitems.issn subscription.callnumber)) {
598 push @bind_params, @strings_to_search;
599 my $tmpstring = "OR $index LIKE ? " x scalar(@strings_to_search);
600 $debug && warn "$tmpstring";
601 $tmpstring =~ s/^OR //;
602 push @sqlstrings, $tmpstring;
604 $sqlwhere .= ( $sqlwhere ? " AND " : " WHERE " ) . "((" . join( ") OR (", @sqlstrings ) . "))";
608 my @strings_to_search;
609 @strings_to_search = map { "$_" } split( / /, $ean );
610 foreach my $index ( qw(biblioitems.ean) ) {
611 push @bind_params, @strings_to_search;
612 my $tmpstring = "OR $index = ? " x scalar(@strings_to_search);
613 $debug && warn "$tmpstring";
614 $tmpstring =~ s/^OR //;
615 push @sqlstrings, $tmpstring;
617 $sqlwhere .= ( $sqlwhere ? " AND " : " WHERE " ) . "((" . join( ") OR (", @sqlstrings ) . "))";
620 $sql .= "$sqlwhere ORDER BY title";
621 $debug and warn "GetSubscriptions query: $sql params : ", join( " ", @bind_params );
622 $sth = $dbh->prepare($sql);
623 $sth->execute(@bind_params);
624 my $subscriptions = $sth->fetchall_arrayref( {} );
625 for my $subscription ( @$subscriptions ) {
626 $subscription->{cannotedit} = not can_edit_subscription( $subscription );
628 return @$subscriptions;
631 =head2 SearchSubscriptions
633 @results = SearchSubscriptions($args);
635 This function returns a list of hashrefs, one for each subscription
636 that meets the conditions specified by the $args hashref.
638 The valid search fields are:
652 The expiration_date search field is special; it specifies the maximum
653 subscription expiration date.
657 sub SearchSubscriptions {
662 subscription.notes AS publicnotes,
664 subscriptionhistory.*,
665 biblio.notes AS biblionotes,
670 LEFT JOIN subscriptionhistory USING(subscriptionid)
671 LEFT JOIN biblio ON biblio.biblionumber = subscription.biblionumber
672 LEFT JOIN biblioitems ON biblioitems.biblionumber = subscription.biblionumber
673 LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id
677 if( $args->{biblionumber} ) {
678 push @where_strs, "biblio.biblionumber = ?";
679 push @where_args, $args->{biblionumber};
681 if( $args->{title} ){
682 my @words = split / /, $args->{title};
684 foreach my $word (@words) {
685 push @strs, "biblio.title LIKE ?";
686 push @args, "%$word%";
689 push @where_strs, '(' . join (' AND ', @strs) . ')';
690 push @where_args, @args;
694 push @where_strs, "biblioitems.issn LIKE ?";
695 push @where_args, "%$args->{issn}%";
698 push @where_strs, "biblioitems.ean LIKE ?";
699 push @where_args, "%$args->{ean}%";
701 if ( $args->{callnumber} ) {
702 push @where_strs, "subscription.callnumber LIKE ?";
703 push @where_args, "%$args->{callnumber}%";
705 if( $args->{publisher} ){
706 push @where_strs, "biblioitems.publishercode LIKE ?";
707 push @where_args, "%$args->{publisher}%";
709 if( $args->{bookseller} ){
710 push @where_strs, "aqbooksellers.name LIKE ?";
711 push @where_args, "%$args->{bookseller}%";
713 if( $args->{branch} ){
714 push @where_strs, "subscription.branchcode = ?";
715 push @where_args, "$args->{branch}";
717 if ( $args->{location} ) {
718 push @where_strs, "subscription.location = ?";
719 push @where_args, "$args->{location}";
721 if ( $args->{expiration_date} ) {
722 push @where_strs, "subscription.enddate <= ?";
723 push @where_args, "$args->{expiration_date}";
725 if( defined $args->{closed} ){
726 push @where_strs, "subscription.closed = ?";
727 push @where_args, "$args->{closed}";
730 $query .= " WHERE " . join(" AND ", @where_strs);
733 my $dbh = C4::Context->dbh;
734 my $sth = $dbh->prepare($query);
735 $sth->execute(@where_args);
736 my $results = $sth->fetchall_arrayref( {} );
739 for my $subscription ( @$results ) {
740 $subscription->{cannotedit} = not can_edit_subscription( $subscription );
741 $subscription->{cannotdisplay} =
742 ( C4::Context->preference("IndependentBranches") &&
743 C4::Context->userenv &&
744 $subscription->{branchcode} ne C4::Context->userenv->{'branch'} ) ? 1 : 0;
753 ($totalissues,@serials) = GetSerials($subscriptionid);
754 this function gets every serial not arrived for a given subscription
755 as well as the number of issues registered in the database (all types)
756 this number is used to see if a subscription can be deleted (=it must have only 1 issue)
758 FIXME: We should return \@serials.
763 my ( $subscriptionid, $count ) = @_;
765 return unless $subscriptionid;
767 my $dbh = C4::Context->dbh;
769 # status = 2 is "arrived"
771 $count = 5 unless ($count);
773 my $query = "SELECT serialid,serialseq, status, publisheddate, planneddate,notes, routingnotes
775 WHERE subscriptionid = ? AND status NOT IN (2,4,5)
776 ORDER BY IF(publisheddate<>'0000-00-00',publisheddate,planneddate) DESC";
777 my $sth = $dbh->prepare($query);
778 $sth->execute($subscriptionid);
780 while ( my $line = $sth->fetchrow_hashref ) {
781 $line->{ "status" . $line->{status} } = 1; # fills a "statusX" value, used for template status select list
782 for my $datefield ( qw( planneddate publisheddate) ) {
783 if ($line->{$datefield} && $line->{$datefield}!~m/^00/) {
784 $line->{$datefield} = format_date( $line->{$datefield});
786 $line->{$datefield} = q{};
789 push @serials, $line;
792 # OK, now add the last 5 issues arrives/missing
793 $query = "SELECT serialid,serialseq, status, planneddate, publisheddate,notes, routingnotes
795 WHERE subscriptionid = ?
796 AND (status in (2,4,5))
797 ORDER BY IF(publisheddate<>'0000-00-00',publisheddate,planneddate) DESC
799 $sth = $dbh->prepare($query);
800 $sth->execute($subscriptionid);
801 while ( ( my $line = $sth->fetchrow_hashref ) && $counter < $count ) {
803 $line->{ "status" . $line->{status} } = 1; # fills a "statusX" value, used for template status select list
804 for my $datefield ( qw( planneddate publisheddate) ) {
805 if ($line->{$datefield} && $line->{$datefield}!~m/^00/) {
806 $line->{$datefield} = format_date( $line->{$datefield});
808 $line->{$datefield} = q{};
812 push @serials, $line;
815 $query = "SELECT count(*) FROM serial WHERE subscriptionid=?";
816 $sth = $dbh->prepare($query);
817 $sth->execute($subscriptionid);
818 my ($totalissues) = $sth->fetchrow;
819 return ( $totalissues, @serials );
824 @serials = GetSerials2($subscriptionid,$status);
825 this function returns every serial waited for a given subscription
826 as well as the number of issues registered in the database (all types)
827 this number is used to see if a subscription can be deleted (=it must have only 1 issue)
832 my ( $subscription, $status ) = @_;
834 return unless ($subscription and $status);
836 my $dbh = C4::Context->dbh;
838 SELECT serialid,serialseq, status, planneddate, publisheddate,notes, routingnotes
840 WHERE subscriptionid=$subscription AND status IN ($status)
841 ORDER BY publisheddate,serialid DESC
843 $debug and warn "GetSerials2 query: $query";
844 my $sth = $dbh->prepare($query);
848 while ( my $line = $sth->fetchrow_hashref ) {
849 $line->{ "status" . $line->{status} } = 1; # fills a "statusX" value, used for template status select list
850 # Format dates for display
851 for my $datefield ( qw( planneddate publisheddate ) ) {
852 if ($line->{$datefield} =~m/^00/) {
853 $line->{$datefield} = q{};
856 $line->{$datefield} = format_date( $line->{$datefield} );
859 push @serials, $line;
864 =head2 GetLatestSerials
866 \@serials = GetLatestSerials($subscriptionid,$limit)
867 get the $limit's latest serials arrived or missing for a given subscription
869 a ref to an array which contains all of the latest serials stored into a hash.
873 sub GetLatestSerials {
874 my ( $subscriptionid, $limit ) = @_;
876 return unless ($subscriptionid and $limit);
878 my $dbh = C4::Context->dbh;
880 # status = 2 is "arrived"
881 my $strsth = "SELECT serialid,serialseq, status, planneddate, publisheddate, notes
883 WHERE subscriptionid = ?
884 AND status IN (2, 4, 41, 42, 43, 44)
885 ORDER BY publisheddate DESC LIMIT 0,$limit
887 my $sth = $dbh->prepare($strsth);
888 $sth->execute($subscriptionid);
890 while ( my $line = $sth->fetchrow_hashref ) {
891 $line->{ "status" . $line->{status} } = 1; # fills a "statusX" value, used for template status select list
892 $line->{"planneddate"} = format_date( $line->{"planneddate"} );
893 $line->{"publisheddate"} = format_date( $line->{"publisheddate"} );
894 push @serials, $line;
900 =head2 GetDistributedTo
902 $distributedto=GetDistributedTo($subscriptionid)
903 This function returns the field distributedto for the subscription matching subscriptionid
907 sub GetDistributedTo {
908 my $dbh = C4::Context->dbh;
910 my ($subscriptionid) = @_;
912 return unless ($subscriptionid);
914 my $query = "SELECT distributedto FROM subscription WHERE subscriptionid=?";
915 my $sth = $dbh->prepare($query);
916 $sth->execute($subscriptionid);
917 return ($distributedto) = $sth->fetchrow;
923 $nextseq, $newlastvalue1, $newlastvalue2, $newlastvalue3,
924 $newinnerloop1, $newinnerloop2, $newinnerloop3
925 ) = GetNextSeq( $subscription, $pattern, $planneddate );
927 $subscription is a hashref containing all the attributes of the table
929 $pattern is a hashref containing all the attributes of the table
930 'subscription_numberpatterns'.
931 $planneddate is a C4::Dates object.
932 This function get the next issue for the subscription given on input arg
937 my ($subscription, $pattern, $planneddate) = @_;
939 return unless ($subscription and $pattern);
941 my ( $newlastvalue1, $newlastvalue2, $newlastvalue3,
942 $newinnerloop1, $newinnerloop2, $newinnerloop3 );
945 if ($subscription->{'skip_serialseq'}) {
946 my @irreg = split /;/, $subscription->{'irregularity'};
948 my $irregularities = {};
949 $irregularities->{$_} = 1 foreach(@irreg);
950 my $issueno = GetFictiveIssueNumber($subscription, $planneddate) + 1;
951 while($irregularities->{$issueno}) {
958 my $numberingmethod = $pattern->{numberingmethod};
960 if ($numberingmethod) {
961 $calculated = $numberingmethod;
962 my $locale = $subscription->{locale};
963 $newlastvalue1 = $subscription->{lastvalue1} || 0;
964 $newlastvalue2 = $subscription->{lastvalue2} || 0;
965 $newlastvalue3 = $subscription->{lastvalue3} || 0;
966 $newinnerloop1 = $subscription->{innerloop1} || 0;
967 $newinnerloop2 = $subscription->{innerloop2} || 0;
968 $newinnerloop3 = $subscription->{innerloop3} || 0;
971 $calc{$_} = 1 if ($numberingmethod =~ /\{$_\}/);
974 for(my $i = 0; $i < $count; $i++) {
976 # check if we have to increase the new value.
978 if ($newinnerloop1 >= $pattern->{every1}) {
980 $newlastvalue1 += $pattern->{add1};
982 # reset counter if needed.
983 $newlastvalue1 = $pattern->{setto1} if ($newlastvalue1 > $pattern->{whenmorethan1});
986 # check if we have to increase the new value.
988 if ($newinnerloop2 >= $pattern->{every2}) {
990 $newlastvalue2 += $pattern->{add2};
992 # reset counter if needed.
993 $newlastvalue2 = $pattern->{setto2} if ($newlastvalue2 > $pattern->{whenmorethan2});
996 # check if we have to increase the new value.
998 if ($newinnerloop3 >= $pattern->{every3}) {
1000 $newlastvalue3 += $pattern->{add3};
1002 # reset counter if needed.
1003 $newlastvalue3 = $pattern->{setto3} if ($newlastvalue3 > $pattern->{whenmorethan3});
1007 my $newlastvalue1string = _numeration( $newlastvalue1, $pattern->{numbering1}, $locale );
1008 $calculated =~ s/\{X\}/$newlastvalue1string/g;
1011 my $newlastvalue2string = _numeration( $newlastvalue2, $pattern->{numbering2}, $locale );
1012 $calculated =~ s/\{Y\}/$newlastvalue2string/g;
1015 my $newlastvalue3string = _numeration( $newlastvalue3, $pattern->{numbering3}, $locale );
1016 $calculated =~ s/\{Z\}/$newlastvalue3string/g;
1020 return ($calculated,
1021 $newlastvalue1, $newlastvalue2, $newlastvalue3,
1022 $newinnerloop1, $newinnerloop2, $newinnerloop3);
1027 $calculated = GetSeq($subscription, $pattern)
1028 $subscription is a hashref containing all the attributes of the table 'subscription'
1029 $pattern is a hashref containing all the attributes of the table 'subscription_numberpatterns'
1030 this function transforms {X},{Y},{Z} to 150,0,0 for example.
1032 the sequence in string format
1037 my ($subscription, $pattern) = @_;
1039 return unless ($subscription and $pattern);
1041 my $locale = $subscription->{locale};
1043 my $calculated = $pattern->{numberingmethod};
1045 my $newlastvalue1 = $subscription->{'lastvalue1'} || 0;
1046 $newlastvalue1 = _numeration($newlastvalue1, $pattern->{numbering1}, $locale) if ($pattern->{numbering1}); # reset counter if needed.
1047 $calculated =~ s/\{X\}/$newlastvalue1/g;
1049 my $newlastvalue2 = $subscription->{'lastvalue2'} || 0;
1050 $newlastvalue2 = _numeration($newlastvalue2, $pattern->{numbering2}, $locale) if ($pattern->{numbering2}); # reset counter if needed.
1051 $calculated =~ s/\{Y\}/$newlastvalue2/g;
1053 my $newlastvalue3 = $subscription->{'lastvalue3'} || 0;
1054 $newlastvalue3 = _numeration($newlastvalue3, $pattern->{numbering3}, $locale) if ($pattern->{numbering3}); # reset counter if needed.
1055 $calculated =~ s/\{Z\}/$newlastvalue3/g;
1059 =head2 GetExpirationDate
1061 $enddate = GetExpirationDate($subscriptionid, [$startdate])
1063 this function return the next expiration date for a subscription given on input args.
1066 the enddate or undef
1070 sub GetExpirationDate {
1071 my ( $subscriptionid, $startdate ) = @_;
1073 return unless ($subscriptionid);
1075 my $dbh = C4::Context->dbh;
1076 my $subscription = GetSubscription($subscriptionid);
1079 # we don't do the same test if the subscription is based on X numbers or on X weeks/months
1080 $enddate = $startdate || $subscription->{startdate};
1081 my @date = split( /-/, $enddate );
1082 return if ( scalar(@date) != 3 || not check_date(@date) );
1083 my $frequency = C4::Serials::Frequency::GetSubscriptionFrequency($subscription->{periodicity});
1084 if ( $frequency and $frequency->{unit} ) {
1087 if ( my $length = $subscription->{numberlength} ) {
1089 #calculate the date of the last issue.
1090 for ( my $i = 1 ; $i <= $length ; $i++ ) {
1091 $enddate = GetNextDate( $subscription, $enddate );
1093 } elsif ( $subscription->{monthlength} ) {
1094 if ( $$subscription{startdate} ) {
1095 my @enddate = Add_Delta_YM( $date[0], $date[1], $date[2], 0, $subscription->{monthlength} );
1096 $enddate = sprintf( "%04d-%02d-%02d", $enddate[0], $enddate[1], $enddate[2] );
1098 } elsif ( $subscription->{weeklength} ) {
1099 if ( $$subscription{startdate} ) {
1100 my @date = split( /-/, $subscription->{startdate} );
1101 my @enddate = Add_Delta_Days( $date[0], $date[1], $date[2], $subscription->{weeklength} * 7 );
1102 $enddate = sprintf( "%04d-%02d-%02d", $enddate[0], $enddate[1], $enddate[2] );
1105 $enddate = $subscription->{enddate};
1109 return $subscription->{enddate};
1113 =head2 CountSubscriptionFromBiblionumber
1115 $subscriptionsnumber = CountSubscriptionFromBiblionumber($biblionumber)
1116 this returns a count of the subscriptions for a given biblionumber
1118 the number of subscriptions
1122 sub CountSubscriptionFromBiblionumber {
1123 my ($biblionumber) = @_;
1125 return unless ($biblionumber);
1127 my $dbh = C4::Context->dbh;
1128 my $query = "SELECT count(*) FROM subscription WHERE biblionumber=?";
1129 my $sth = $dbh->prepare($query);
1130 $sth->execute($biblionumber);
1131 my $subscriptionsnumber = $sth->fetchrow;
1132 return $subscriptionsnumber;
1135 =head2 ModSubscriptionHistory
1137 ModSubscriptionHistory($subscriptionid,$histstartdate,$enddate,$recievedlist,$missinglist,$opacnote,$librariannote);
1139 this function modifies the history of a subscription. Put your new values on input arg.
1140 returns the number of rows affected
1144 sub ModSubscriptionHistory {
1145 my ( $subscriptionid, $histstartdate, $enddate, $receivedlist, $missinglist, $opacnote, $librariannote ) = @_;
1147 return unless ($subscriptionid);
1149 my $dbh = C4::Context->dbh;
1150 my $query = "UPDATE subscriptionhistory
1151 SET histstartdate=?,histenddate=?,recievedlist=?,missinglist=?,opacnote=?,librariannote=?
1152 WHERE subscriptionid=?
1154 my $sth = $dbh->prepare($query);
1155 $receivedlist =~ s/^; // if $receivedlist;
1156 $missinglist =~ s/^; // if $missinglist;
1157 $opacnote =~ s/^; // if $opacnote;
1158 $sth->execute( $histstartdate, $enddate, $receivedlist, $missinglist, $opacnote, $librariannote, $subscriptionid );
1162 # Update missinglist field, used by ModSerialStatus
1163 sub _update_missinglist {
1164 my $subscriptionid = shift;
1166 my $dbh = C4::Context->dbh;
1167 my @missingserials = GetSerials2($subscriptionid, "4,5");
1169 foreach (@missingserials) {
1170 if($_->{'status'} == 4) {
1171 $missinglist .= $_->{'serialseq'} . "; ";
1172 } elsif($_->{'status'} == 5) {
1173 $missinglist .= "not issued " . $_->{'serialseq'} . "; ";
1176 $missinglist =~ s/; $//;
1178 UPDATE subscriptionhistory
1180 WHERE subscriptionid = ?
1182 my $sth = $dbh->prepare($query);
1183 $sth->execute($missinglist, $subscriptionid);
1186 # Update recievedlist field, used by ModSerialStatus
1187 sub _update_receivedlist {
1188 my $subscriptionid = shift;
1190 my $dbh = C4::Context->dbh;
1191 my @receivedserials = GetSerials2($subscriptionid, "2");
1193 foreach (@receivedserials) {
1194 $receivedlist .= $_->{'serialseq'} . "; ";
1196 $receivedlist =~ s/; $//;
1198 UPDATE subscriptionhistory
1199 SET recievedlist = ?
1200 WHERE subscriptionid = ?
1202 my $sth = $dbh->prepare($query);
1203 $sth->execute($receivedlist, $subscriptionid);
1206 =head2 ModSerialStatus
1208 ModSerialStatus($serialid,$serialseq, $planneddate,$publisheddate,$status,$notes)
1210 This function modify the serial status. Serial status is a number.(eg 2 is "arrived")
1211 Note : if we change from "waited" to something else,then we will have to create a new "waited" entry
1215 sub ModSerialStatus {
1216 my ( $serialid, $serialseq, $planneddate, $publisheddate, $status, $notes ) = @_;
1218 return unless ($serialid);
1220 #It is a usual serial
1221 # 1st, get previous status :
1222 my $dbh = C4::Context->dbh;
1223 my $query = "SELECT serial.subscriptionid,serial.status,subscription.periodicity
1224 FROM serial, subscription
1225 WHERE serial.subscriptionid=subscription.subscriptionid
1227 my $sth = $dbh->prepare($query);
1228 $sth->execute($serialid);
1229 my ( $subscriptionid, $oldstatus, $periodicity ) = $sth->fetchrow;
1230 my $frequency = GetSubscriptionFrequency($periodicity);
1232 # change status & update subscriptionhistory
1234 if ( $status == 6 ) {
1235 DelIssue( { 'serialid' => $serialid, 'subscriptionid' => $subscriptionid, 'serialseq' => $serialseq } );
1238 unless ($frequency->{'unit'}) {
1239 if ( not $planneddate or $planneddate eq '0000-00-00' ) { $planneddate = C4::Dates->new()->output('iso') };
1240 if ( not $publisheddate or $publisheddate eq '0000-00-00' ) { $publisheddate = C4::Dates->new()->output('iso') };
1242 my $query = 'UPDATE serial SET serialseq=?,publisheddate=?,planneddate=?,status=?,notes=? WHERE serialid = ?';
1243 $sth = $dbh->prepare($query);
1244 $sth->execute( $serialseq, $publisheddate, $planneddate, $status, $notes, $serialid );
1245 $query = "SELECT * FROM subscription WHERE subscriptionid = ?";
1246 $sth = $dbh->prepare($query);
1247 $sth->execute($subscriptionid);
1248 my $val = $sth->fetchrow_hashref;
1249 unless ( $val->{manualhistory} ) {
1250 if ( $status == 2 || ($oldstatus == 2 && $status != 2) ) {
1251 _update_receivedlist($subscriptionid);
1253 if( grep { $_ == $status } ( 4, 41, 42, 43, 44, 5 )
1255 grep { $_ == $oldstatus } ( 4, 41, 42, 43, 44 )
1256 && ! grep { $_ == $status } ( 4, 41, 42, 43, 44 ) )
1257 || ($oldstatus == 5 && $status != 5)) {
1258 _update_missinglist($subscriptionid);
1263 # create new waited entry if needed (ie : was a "waited" and has changed)
1264 if ( $oldstatus == 1 && $status != 1 ) {
1265 my $subscription = GetSubscription($subscriptionid);
1266 my $pattern = C4::Serials::Numberpattern::GetSubscriptionNumberpattern($subscription->{numberpattern});
1270 $newserialseq, $newlastvalue1, $newlastvalue2, $newlastvalue3,
1271 $newinnerloop1, $newinnerloop2, $newinnerloop3
1273 = GetNextSeq( $subscription, $pattern, $publisheddate );
1275 # next date (calculated from actual date & frequency parameters)
1276 my $nextpublisheddate = GetNextDate($subscription, $publisheddate, 1);
1277 my $nextpubdate = $nextpublisheddate;
1278 NewIssue( $newserialseq, $subscriptionid, $subscription->{'biblionumber'}, 1, $nextpubdate, $nextpubdate );
1279 $query = "UPDATE subscription SET lastvalue1=?, lastvalue2=?, lastvalue3=?, innerloop1=?, innerloop2=?, innerloop3=?
1280 WHERE subscriptionid = ?";
1281 $sth = $dbh->prepare($query);
1282 $sth->execute( $newlastvalue1, $newlastvalue2, $newlastvalue3, $newinnerloop1, $newinnerloop2, $newinnerloop3, $subscriptionid );
1284 # check if an alert must be sent... (= a letter is defined & status became "arrived"
1285 if ( $subscription->{letter} && $status == 2 && $oldstatus != 2 ) {
1286 require C4::Letters;
1287 C4::Letters::SendAlerts( 'issue', $subscription->{subscriptionid}, $subscription->{letter} );
1294 =head2 GetNextExpected
1296 $nextexpected = GetNextExpected($subscriptionid)
1298 Get the planneddate for the current expected issue of the subscription.
1304 planneddate => ISO date
1309 sub GetNextExpected {
1310 my ($subscriptionid) = @_;
1312 my $dbh = C4::Context->dbh;
1316 WHERE subscriptionid = ?
1320 my $sth = $dbh->prepare($query);
1322 # Each subscription has only one 'expected' issue, with serial.status==1.
1323 $sth->execute( $subscriptionid, 1 );
1324 my $nextissue = $sth->fetchrow_hashref;
1325 if ( !$nextissue ) {
1329 WHERE subscriptionid = ?
1330 ORDER BY publisheddate DESC
1333 $sth = $dbh->prepare($query);
1334 $sth->execute($subscriptionid);
1335 $nextissue = $sth->fetchrow_hashref;
1337 foreach(qw/planneddate publisheddate/) {
1338 if ( !defined $nextissue->{$_} ) {
1339 # or should this default to 1st Jan ???
1340 $nextissue->{$_} = strftime( '%Y-%m-%d', localtime );
1342 $nextissue->{$_} = ($nextissue->{$_} ne '0000-00-00')
1350 =head2 ModNextExpected
1352 ModNextExpected($subscriptionid,$date)
1354 Update the planneddate for the current expected issue of the subscription.
1355 This will modify all future prediction results.
1357 C<$date> is an ISO date.
1363 sub ModNextExpected {
1364 my ( $subscriptionid, $date ) = @_;
1365 my $dbh = C4::Context->dbh;
1367 #FIXME: Would expect to only set planneddate, but we set both on new issue creation, so updating it here
1368 my $sth = $dbh->prepare('UPDATE serial SET planneddate=?,publisheddate=? WHERE subscriptionid=? AND status=?');
1370 # Each subscription has only one 'expected' issue, with serial.status==1.
1371 $sth->execute( $date, $date, $subscriptionid, 1 );
1376 =head2 GetSubscriptionIrregularities
1380 =item @irreg = &GetSubscriptionIrregularities($subscriptionid);
1381 get the list of irregularities for a subscription
1387 sub GetSubscriptionIrregularities {
1388 my $subscriptionid = shift;
1390 return unless $subscriptionid;
1392 my $dbh = C4::Context->dbh;
1396 WHERE subscriptionid = ?
1398 my $sth = $dbh->prepare($query);
1399 $sth->execute($subscriptionid);
1401 my ($result) = $sth->fetchrow_array;
1402 my @irreg = split /;/, $result;
1407 =head2 ModSubscription
1409 this function modifies a subscription. Put all new values on input args.
1410 returns the number of rows affected
1414 sub ModSubscription {
1416 $auser, $branchcode, $aqbooksellerid, $cost, $aqbudgetid, $startdate,
1417 $periodicity, $firstacquidate, $irregularity, $numberpattern, $locale,
1418 $numberlength, $weeklength, $monthlength, $lastvalue1, $innerloop1,
1419 $lastvalue2, $innerloop2, $lastvalue3, $innerloop3, $status,
1420 $biblionumber, $callnumber, $notes, $letter, $manualhistory,
1421 $internalnotes, $serialsadditems, $staffdisplaycount, $opacdisplaycount,
1422 $graceperiod, $location, $enddate, $subscriptionid, $skip_serialseq
1425 my $dbh = C4::Context->dbh;
1426 my $query = "UPDATE subscription
1427 SET librarian=?, branchcode=?, aqbooksellerid=?, cost=?, aqbudgetid=?,
1428 startdate=?, periodicity=?, firstacquidate=?, irregularity=?,
1429 numberpattern=?, locale=?, numberlength=?, weeklength=?, monthlength=?,
1430 lastvalue1=?, innerloop1=?, lastvalue2=?, innerloop2=?,
1431 lastvalue3=?, innerloop3=?, status=?, biblionumber=?,
1432 callnumber=?, notes=?, letter=?, manualhistory=?,
1433 internalnotes=?, serialsadditems=?, staffdisplaycount=?,
1434 opacdisplaycount=?, graceperiod=?, location = ?, enddate=?,
1436 WHERE subscriptionid = ?";
1438 my $sth = $dbh->prepare($query);
1440 $auser, $branchcode, $aqbooksellerid, $cost,
1441 $aqbudgetid, $startdate, $periodicity, $firstacquidate,
1442 $irregularity, $numberpattern, $locale, $numberlength,
1443 $weeklength, $monthlength, $lastvalue1, $innerloop1,
1444 $lastvalue2, $innerloop2, $lastvalue3, $innerloop3,
1445 $status, $biblionumber, $callnumber, $notes,
1446 $letter, ($manualhistory ? $manualhistory : 0),
1447 $internalnotes, $serialsadditems, $staffdisplaycount, $opacdisplaycount,
1448 $graceperiod, $location, $enddate, $skip_serialseq,
1451 my $rows = $sth->rows;
1453 logaction( "SERIAL", "MODIFY", $subscriptionid, "" ) if C4::Context->preference("SubscriptionLog");
1457 =head2 NewSubscription
1459 $subscriptionid = &NewSubscription($auser,branchcode,$aqbooksellerid,$cost,$aqbudgetid,$biblionumber,
1460 $startdate,$periodicity,$numberlength,$weeklength,$monthlength,
1461 $lastvalue1,$innerloop1,$lastvalue2,$innerloop2,$lastvalue3,$innerloop3,
1462 $status, $notes, $letter, $firstacquidate, $irregularity, $numberpattern,
1463 $locale, $callnumber, $manualhistory, $internalnotes, $serialsadditems,
1464 $staffdisplaycount, $opacdisplaycount, $graceperiod, $location, $enddate, $skip_serialseq);
1466 Create a new subscription with value given on input args.
1469 the id of this new subscription
1473 sub NewSubscription {
1475 $auser, $branchcode, $aqbooksellerid, $cost, $aqbudgetid, $biblionumber,
1476 $startdate, $periodicity, $numberlength, $weeklength, $monthlength,
1477 $lastvalue1, $innerloop1, $lastvalue2, $innerloop2, $lastvalue3,
1478 $innerloop3, $status, $notes, $letter, $firstacquidate, $irregularity,
1479 $numberpattern, $locale, $callnumber, $manualhistory, $internalnotes,
1480 $serialsadditems, $staffdisplaycount, $opacdisplaycount, $graceperiod,
1481 $location, $enddate, $skip_serialseq
1483 my $dbh = C4::Context->dbh;
1485 #save subscription (insert into database)
1487 INSERT INTO subscription
1488 (librarian, branchcode, aqbooksellerid, cost, aqbudgetid,
1489 biblionumber, startdate, periodicity, numberlength, weeklength,
1490 monthlength, lastvalue1, innerloop1, lastvalue2, innerloop2,
1491 lastvalue3, innerloop3, status, notes, letter, firstacquidate,
1492 irregularity, numberpattern, locale, callnumber,
1493 manualhistory, internalnotes, serialsadditems, staffdisplaycount,
1494 opacdisplaycount, graceperiod, location, enddate, skip_serialseq)
1495 VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
1497 my $sth = $dbh->prepare($query);
1499 $auser, $branchcode, $aqbooksellerid, $cost, $aqbudgetid, $biblionumber,
1500 $startdate, $periodicity, $numberlength, $weeklength,
1501 $monthlength, $lastvalue1, $innerloop1, $lastvalue2, $innerloop2,
1502 $lastvalue3, $innerloop3, $status, $notes, $letter,
1503 $firstacquidate, $irregularity, $numberpattern, $locale, $callnumber,
1504 $manualhistory, $internalnotes, $serialsadditems, $staffdisplaycount,
1505 $opacdisplaycount, $graceperiod, $location, $enddate, $skip_serialseq
1508 my $subscriptionid = $dbh->{'mysql_insertid'};
1510 $enddate = GetExpirationDate( $subscriptionid, $startdate );
1514 WHERE subscriptionid=?
1516 $sth = $dbh->prepare($query);
1517 $sth->execute( $enddate, $subscriptionid );
1520 # then create the 1st expected number
1522 INSERT INTO subscriptionhistory
1523 (biblionumber, subscriptionid, histstartdate, opacnote, librariannote)
1526 $sth = $dbh->prepare($query);
1527 $sth->execute( $biblionumber, $subscriptionid, $startdate, $notes, $internalnotes );
1529 # reread subscription to get a hash (for calculation of the 1st issue number)
1530 my $subscription = GetSubscription($subscriptionid);
1531 my $pattern = C4::Serials::Numberpattern::GetSubscriptionNumberpattern($subscription->{numberpattern});
1533 # calculate issue number
1534 my $serialseq = GetSeq($subscription, $pattern);
1537 (serialseq,subscriptionid,biblionumber,status, planneddate, publisheddate)
1538 VALUES (?,?,?,?,?,?)
1540 $sth = $dbh->prepare($query);
1541 $sth->execute( "$serialseq", $subscriptionid, $biblionumber, 1, $firstacquidate, $firstacquidate );
1543 logaction( "SERIAL", "ADD", $subscriptionid, "" ) if C4::Context->preference("SubscriptionLog");
1545 #set serial flag on biblio if not already set.
1546 my $bib = GetBiblio($biblionumber);
1547 if ( $bib and !$bib->{'serial'} ) {
1548 my $record = GetMarcBiblio($biblionumber);
1549 my ( $tag, $subf ) = GetMarcFromKohaField( 'biblio.serial', $bib->{'frameworkcode'} );
1551 eval { $record->field($tag)->update( $subf => 1 ); };
1553 ModBiblio( $record, $biblionumber, $bib->{'frameworkcode'} );
1555 return $subscriptionid;
1558 =head2 ReNewSubscription
1560 ReNewSubscription($subscriptionid,$user,$startdate,$numberlength,$weeklength,$monthlength,$note)
1562 this function renew a subscription with values given on input args.
1566 sub ReNewSubscription {
1567 my ( $subscriptionid, $user, $startdate, $numberlength, $weeklength, $monthlength, $note ) = @_;
1568 my $dbh = C4::Context->dbh;
1569 my $subscription = GetSubscription($subscriptionid);
1573 LEFT JOIN biblioitems ON biblio.biblionumber=biblioitems.biblionumber
1574 WHERE biblio.biblionumber=?
1576 my $sth = $dbh->prepare($query);
1577 $sth->execute( $subscription->{biblionumber} );
1578 my $biblio = $sth->fetchrow_hashref;
1580 if ( C4::Context->preference("RenewSerialAddsSuggestion") ) {
1581 require C4::Suggestions;
1582 C4::Suggestions::NewSuggestion(
1583 { 'suggestedby' => $user,
1584 'title' => $subscription->{bibliotitle},
1585 'author' => $biblio->{author},
1586 'publishercode' => $biblio->{publishercode},
1587 'note' => $biblio->{note},
1588 'biblionumber' => $subscription->{biblionumber}
1593 # renew subscription
1596 SET startdate=?,numberlength=?,weeklength=?,monthlength=?,reneweddate=NOW()
1597 WHERE subscriptionid=?
1599 $sth = $dbh->prepare($query);
1600 $sth->execute( $startdate, $numberlength, $weeklength, $monthlength, $subscriptionid );
1601 my $enddate = GetExpirationDate($subscriptionid);
1602 $debug && warn "enddate :$enddate";
1606 WHERE subscriptionid=?
1608 $sth = $dbh->prepare($query);
1609 $sth->execute( $enddate, $subscriptionid );
1611 UPDATE subscriptionhistory
1613 WHERE subscriptionid=?
1615 $sth = $dbh->prepare($query);
1616 $sth->execute( $enddate, $subscriptionid );
1618 logaction( "SERIAL", "RENEW", $subscriptionid, "" ) if C4::Context->preference("SubscriptionLog");
1624 NewIssue($serialseq,$subscriptionid,$biblionumber,$status, $planneddate, $publisheddate, $notes)
1626 Create a new issue stored on the database.
1627 Note : we have to update the recievedlist and missinglist on subscriptionhistory for this subscription.
1628 returns the serial id
1633 my ( $serialseq, $subscriptionid, $biblionumber, $status, $planneddate, $publisheddate, $notes ) = @_;
1634 ### FIXME biblionumber CAN be provided by subscriptionid. So Do we STILL NEED IT ?
1636 return unless ($subscriptionid);
1638 my $dbh = C4::Context->dbh;
1641 (serialseq,subscriptionid,biblionumber,status,publisheddate,planneddate,notes)
1642 VALUES (?,?,?,?,?,?,?)
1644 my $sth = $dbh->prepare($query);
1645 $sth->execute( $serialseq, $subscriptionid, $biblionumber, $status, $publisheddate, $planneddate, $notes );
1646 my $serialid = $dbh->{'mysql_insertid'};
1648 SELECT missinglist,recievedlist
1649 FROM subscriptionhistory
1650 WHERE subscriptionid=?
1652 $sth = $dbh->prepare($query);
1653 $sth->execute($subscriptionid);
1654 my ( $missinglist, $recievedlist ) = $sth->fetchrow;
1656 if ( $status == 2 ) {
1657 ### TODO Add a feature that improves recognition and description.
1658 ### As such count (serialseq) i.e. : N18,2(N19),N20
1659 ### Would use substr and index But be careful to previous presence of ()
1660 $recievedlist .= "; $serialseq" unless (index($recievedlist,$serialseq)>0);
1662 if ( $status == 4 ) {
1663 $missinglist .= "; $serialseq" unless (index($missinglist,$serialseq)>0);
1666 UPDATE subscriptionhistory
1667 SET recievedlist=?, missinglist=?
1668 WHERE subscriptionid=?
1670 $sth = $dbh->prepare($query);
1671 $recievedlist =~ s/^; //;
1672 $missinglist =~ s/^; //;
1673 $sth->execute( $recievedlist, $missinglist, $subscriptionid );
1677 =head2 ItemizeSerials
1679 ItemizeSerials($serialid, $info);
1680 $info is a hashref containing barcode branch, itemcallnumber, status, location
1681 $serialid the serialid
1683 1 if the itemize is a succes.
1684 0 and @error otherwise. @error containts the list of errors found.
1688 sub ItemizeSerials {
1689 my ( $serialid, $info ) = @_;
1691 return unless ($serialid);
1693 my $now = POSIX::strftime( "%Y-%m-%d", localtime );
1695 my $dbh = C4::Context->dbh;
1701 my $sth = $dbh->prepare($query);
1702 $sth->execute($serialid);
1703 my $data = $sth->fetchrow_hashref;
1704 if ( C4::Context->preference("RoutingSerials") ) {
1706 # check for existing biblioitem relating to serial issue
1707 my ( $count, @results ) = GetBiblioItemByBiblioNumber( $data->{'biblionumber'} );
1709 for ( my $i = 0 ; $i < $count ; $i++ ) {
1710 if ( $results[$i]->{'volumeddesc'} eq $data->{'serialseq'} . ' (' . $data->{'planneddate'} . ')' ) {
1711 $bibitemno = $results[$i]->{'biblioitemnumber'};
1715 if ( $bibitemno == 0 ) {
1716 my $sth = $dbh->prepare( "SELECT * FROM biblioitems WHERE biblionumber = ? ORDER BY biblioitemnumber DESC" );
1717 $sth->execute( $data->{'biblionumber'} );
1718 my $biblioitem = $sth->fetchrow_hashref;
1719 $biblioitem->{'volumedate'} = $data->{planneddate};
1720 $biblioitem->{'volumeddesc'} = $data->{serialseq} . ' (' . format_date( $data->{'planneddate'} ) . ')';
1721 $biblioitem->{'dewey'} = $info->{itemcallnumber};
1725 my $fwk = GetFrameworkCode( $data->{'biblionumber'} );
1726 if ( $info->{barcode} ) {
1728 if ( is_barcode_in_use( $info->{barcode} ) ) {
1729 push @errors, 'barcode_not_unique';
1731 my $marcrecord = MARC::Record->new();
1732 my ( $tag, $subfield ) = GetMarcFromKohaField( "items.barcode", $fwk );
1733 my $newField = MARC::Field->new( "$tag", '', '', "$subfield" => $info->{barcode} );
1734 $marcrecord->insert_fields_ordered($newField);
1735 if ( $info->{branch} ) {
1736 my ( $tag, $subfield ) = GetMarcFromKohaField( "items.homebranch", $fwk );
1738 #warn "items.homebranch : $tag , $subfield";
1739 if ( $marcrecord->field($tag) ) {
1740 $marcrecord->field($tag)->add_subfields( "$subfield" => $info->{branch} );
1742 my $newField = MARC::Field->new( "$tag", '', '', "$subfield" => $info->{branch} );
1743 $marcrecord->insert_fields_ordered($newField);
1745 ( $tag, $subfield ) = GetMarcFromKohaField( "items.holdingbranch", $fwk );
1747 #warn "items.holdingbranch : $tag , $subfield";
1748 if ( $marcrecord->field($tag) ) {
1749 $marcrecord->field($tag)->add_subfields( "$subfield" => $info->{branch} );
1751 my $newField = MARC::Field->new( "$tag", '', '', "$subfield" => $info->{branch} );
1752 $marcrecord->insert_fields_ordered($newField);
1755 if ( $info->{itemcallnumber} ) {
1756 my ( $tag, $subfield ) = GetMarcFromKohaField( "items.itemcallnumber", $fwk );
1758 if ( $marcrecord->field($tag) ) {
1759 $marcrecord->field($tag)->add_subfields( "$subfield" => $info->{itemcallnumber} );
1761 my $newField = MARC::Field->new( "$tag", '', '', "$subfield" => $info->{itemcallnumber} );
1762 $marcrecord->insert_fields_ordered($newField);
1765 if ( $info->{notes} ) {
1766 my ( $tag, $subfield ) = GetMarcFromKohaField( "items.itemnotes", $fwk );
1768 if ( $marcrecord->field($tag) ) {
1769 $marcrecord->field($tag)->add_subfields( "$subfield" => $info->{notes} );
1771 my $newField = MARC::Field->new( "$tag", '', '', "$subfield" => $info->{notes} );
1772 $marcrecord->insert_fields_ordered($newField);
1775 if ( $info->{location} ) {
1776 my ( $tag, $subfield ) = GetMarcFromKohaField( "items.location", $fwk );
1778 if ( $marcrecord->field($tag) ) {
1779 $marcrecord->field($tag)->add_subfields( "$subfield" => $info->{location} );
1781 my $newField = MARC::Field->new( "$tag", '', '', "$subfield" => $info->{location} );
1782 $marcrecord->insert_fields_ordered($newField);
1785 if ( $info->{status} ) {
1786 my ( $tag, $subfield ) = GetMarcFromKohaField( "items.notforloan", $fwk );
1788 if ( $marcrecord->field($tag) ) {
1789 $marcrecord->field($tag)->add_subfields( "$subfield" => $info->{status} );
1791 my $newField = MARC::Field->new( "$tag", '', '', "$subfield" => $info->{status} );
1792 $marcrecord->insert_fields_ordered($newField);
1795 if ( C4::Context->preference("RoutingSerials") ) {
1796 my ( $tag, $subfield ) = GetMarcFromKohaField( "items.dateaccessioned", $fwk );
1797 if ( $marcrecord->field($tag) ) {
1798 $marcrecord->field($tag)->add_subfields( "$subfield" => $now );
1800 my $newField = MARC::Field->new( "$tag", '', '', "$subfield" => $now );
1801 $marcrecord->insert_fields_ordered($newField);
1805 C4::Items::AddItemFromMarc( $marcrecord, $data->{'biblionumber'} );
1808 return ( 0, @errors );
1812 =head2 HasSubscriptionStrictlyExpired
1814 1 or 0 = HasSubscriptionStrictlyExpired($subscriptionid)
1816 the subscription has stricly expired when today > the end subscription date
1819 1 if true, 0 if false, -1 if the expiration date is not set.
1823 sub HasSubscriptionStrictlyExpired {
1825 # Getting end of subscription date
1826 my ($subscriptionid) = @_;
1828 return unless ($subscriptionid);
1830 my $dbh = C4::Context->dbh;
1831 my $subscription = GetSubscription($subscriptionid);
1832 my $expirationdate = $subscription->{enddate} || GetExpirationDate($subscriptionid);
1834 # If the expiration date is set
1835 if ( $expirationdate != 0 ) {
1836 my ( $endyear, $endmonth, $endday ) = split( '-', $expirationdate );
1838 # Getting today's date
1839 my ( $nowyear, $nowmonth, $nowday ) = Today();
1841 # if today's date > expiration date, then the subscription has stricly expired
1842 if ( Delta_Days( $nowyear, $nowmonth, $nowday, $endyear, $endmonth, $endday ) < 0 ) {
1849 # There are some cases where the expiration date is not set
1850 # As we can't determine if the subscription has expired on a date-basis,
1856 =head2 HasSubscriptionExpired
1858 $has_expired = HasSubscriptionExpired($subscriptionid)
1860 the subscription has expired when the next issue to arrive is out of subscription limit.
1863 0 if the subscription has not expired
1864 1 if the subscription has expired
1865 2 if has subscription does not have a valid expiration date set
1869 sub HasSubscriptionExpired {
1870 my ($subscriptionid) = @_;
1872 return unless ($subscriptionid);
1874 my $dbh = C4::Context->dbh;
1875 my $subscription = GetSubscription($subscriptionid);
1876 my $frequency = C4::Serials::Frequency::GetSubscriptionFrequency($subscription->{periodicity});
1877 if ( $frequency and $frequency->{unit} ) {
1878 my $expirationdate = $subscription->{enddate} || GetExpirationDate($subscriptionid);
1879 if (!defined $expirationdate) {
1880 $expirationdate = q{};
1883 SELECT max(planneddate)
1885 WHERE subscriptionid=?
1887 my $sth = $dbh->prepare($query);
1888 $sth->execute($subscriptionid);
1889 my ($res) = $sth->fetchrow;
1890 if (!$res || $res=~m/^0000/) {
1893 my @res = split( /-/, $res );
1894 my @endofsubscriptiondate = split( /-/, $expirationdate );
1895 return 2 if ( scalar(@res) != 3 || scalar(@endofsubscriptiondate) != 3 || not check_date(@res) || not check_date(@endofsubscriptiondate) );
1897 if ( ( @endofsubscriptiondate && Delta_Days( $res[0], $res[1], $res[2], $endofsubscriptiondate[0], $endofsubscriptiondate[1], $endofsubscriptiondate[2] ) <= 0 )
1902 if ( $subscription->{'numberlength'} ) {
1903 my $countreceived = countissuesfrom( $subscriptionid, $subscription->{'startdate'} );
1904 return 1 if ( $countreceived > $subscription->{'numberlength'} );
1910 return 0; # Notice that you'll never get here.
1913 =head2 SetDistributedto
1915 SetDistributedto($distributedto,$subscriptionid);
1916 This function update the value of distributedto for a subscription given on input arg.
1920 sub SetDistributedto {
1921 my ( $distributedto, $subscriptionid ) = @_;
1922 my $dbh = C4::Context->dbh;
1926 WHERE subscriptionid=?
1928 my $sth = $dbh->prepare($query);
1929 $sth->execute( $distributedto, $subscriptionid );
1933 =head2 DelSubscription
1935 DelSubscription($subscriptionid)
1936 this function deletes subscription which has $subscriptionid as id.
1940 sub DelSubscription {
1941 my ($subscriptionid) = @_;
1942 my $dbh = C4::Context->dbh;
1943 $subscriptionid = $dbh->quote($subscriptionid);
1944 $dbh->do("DELETE FROM subscription WHERE subscriptionid=$subscriptionid");
1945 $dbh->do("DELETE FROM subscriptionhistory WHERE subscriptionid=$subscriptionid");
1946 $dbh->do("DELETE FROM serial WHERE subscriptionid=$subscriptionid");
1948 logaction( "SERIAL", "DELETE", $subscriptionid, "" ) if C4::Context->preference("SubscriptionLog");
1953 DelIssue($serialseq,$subscriptionid)
1954 this function deletes an issue which has $serialseq and $subscriptionid given on input arg.
1956 returns the number of rows affected
1961 my ($dataissue) = @_;
1962 my $dbh = C4::Context->dbh;
1963 ### TODO Add itemdeletion. Would need to get itemnumbers. Should be in a pref ?
1968 AND subscriptionid= ?
1970 my $mainsth = $dbh->prepare($query);
1971 $mainsth->execute( $dataissue->{'serialid'}, $dataissue->{'subscriptionid'} );
1973 #Delete element from subscription history
1974 $query = "SELECT * FROM subscription WHERE subscriptionid = ?";
1975 my $sth = $dbh->prepare($query);
1976 $sth->execute( $dataissue->{'subscriptionid'} );
1977 my $val = $sth->fetchrow_hashref;
1978 unless ( $val->{manualhistory} ) {
1980 SELECT * FROM subscriptionhistory
1981 WHERE subscriptionid= ?
1983 my $sth = $dbh->prepare($query);
1984 $sth->execute( $dataissue->{'subscriptionid'} );
1985 my $data = $sth->fetchrow_hashref;
1986 my $serialseq = $dataissue->{'serialseq'};
1987 $data->{'missinglist'} =~ s/\b$serialseq\b//;
1988 $data->{'recievedlist'} =~ s/\b$serialseq\b//;
1989 my $strsth = "UPDATE subscriptionhistory SET " . join( ",", map { join( "=", $_, $dbh->quote( $data->{$_} ) ) } keys %$data ) . " WHERE subscriptionid=?";
1990 $sth = $dbh->prepare($strsth);
1991 $sth->execute( $dataissue->{'subscriptionid'} );
1994 return $mainsth->rows;
1997 =head2 GetLateOrMissingIssues
1999 @issuelist = GetLateMissingIssues($supplierid,$serialid)
2001 this function selects missing issues on database - where serial.status = 4 or serial.status=3 or planneddate<now
2004 the issuelist as an array of hash refs. Each element of this array contains
2005 name,title,planneddate,serialseq,serial.subscriptionid from tables : subscription, serial & biblio
2009 sub GetLateOrMissingIssues {
2010 my ( $supplierid, $serialid, $order ) = @_;
2012 return unless ( $supplierid or $serialid );
2014 my $dbh = C4::Context->dbh;
2018 $byserial = "and serialid = " . $serialid;
2021 $order .= ", title";
2026 $sth = $dbh->prepare(
2028 serialid, aqbooksellerid, name,
2029 biblio.title, planneddate, serialseq,
2030 serial.status, serial.subscriptionid, claimdate,
2031 subscription.branchcode
2033 LEFT JOIN subscription ON serial.subscriptionid=subscription.subscriptionid
2034 LEFT JOIN biblio ON subscription.biblionumber=biblio.biblionumber
2035 LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id
2036 WHERE subscription.subscriptionid = serial.subscriptionid
2037 AND (serial.STATUS IN (4, 41, 42, 43, 44) OR ((planneddate < now() AND serial.STATUS =1) OR serial.STATUS = 3 OR serial.STATUS = 7))
2038 AND subscription.aqbooksellerid=$supplierid
2043 $sth = $dbh->prepare(
2045 serialid, aqbooksellerid, name,
2046 biblio.title, planneddate, serialseq,
2047 serial.status, serial.subscriptionid, claimdate,
2048 subscription.branchcode
2050 LEFT JOIN subscription ON serial.subscriptionid=subscription.subscriptionid
2051 LEFT JOIN biblio ON subscription.biblionumber=biblio.biblionumber
2052 LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id
2053 WHERE subscription.subscriptionid = serial.subscriptionid
2054 AND (serial.STATUS IN (4, 41, 42, 43, 44) OR ((planneddate < now() AND serial.STATUS =1) OR serial.STATUS = 3 OR serial.STATUS = 7))
2061 while ( my $line = $sth->fetchrow_hashref ) {
2063 if ($line->{planneddate} && $line->{planneddate} !~/^0+\-/) {
2064 $line->{planneddate} = format_date( $line->{planneddate} );
2066 if ($line->{claimdate} && $line->{claimdate} !~/^0+\-/) {
2067 $line->{claimdate} = format_date( $line->{claimdate} );
2069 $line->{"status".$line->{status}} = 1;
2070 push @issuelist, $line;
2075 =head2 removeMissingIssue
2077 removeMissingIssue($subscriptionid)
2079 this function removes an issue from being part of the missing string in
2080 subscriptionlist.missinglist column
2082 called when a missing issue is found from the serials-recieve.pl file
2086 sub removeMissingIssue {
2087 my ( $sequence, $subscriptionid ) = @_;
2089 return unless ($sequence and $subscriptionid);
2091 my $dbh = C4::Context->dbh;
2092 my $sth = $dbh->prepare("SELECT * FROM subscriptionhistory WHERE subscriptionid = ?");
2093 $sth->execute($subscriptionid);
2094 my $data = $sth->fetchrow_hashref;
2095 my $missinglist = $data->{'missinglist'};
2096 my $missinglistbefore = $missinglist;
2098 # warn $missinglist." before";
2099 $missinglist =~ s/($sequence)//;
2101 # warn $missinglist." after";
2102 if ( $missinglist ne $missinglistbefore ) {
2103 $missinglist =~ s/\|\s\|/\|/g;
2104 $missinglist =~ s/^\| //g;
2105 $missinglist =~ s/\|$//g;
2106 my $sth2 = $dbh->prepare(
2107 "UPDATE subscriptionhistory
2109 WHERE subscriptionid = ?"
2111 $sth2->execute( $missinglist, $subscriptionid );
2118 &updateClaim($serialid)
2120 this function updates the time when a claim is issued for late/missing items
2122 called from claims.pl file
2127 my ($serialid) = @_;
2128 my $dbh = C4::Context->dbh;
2129 my $sth = $dbh->prepare(
2130 "UPDATE serial SET claimdate = now()
2134 $sth->execute($serialid);
2138 =head2 getsupplierbyserialid
2140 $result = getsupplierbyserialid($serialid)
2142 this function is used to find the supplier id given a serial id
2145 hashref containing serialid, subscriptionid, and aqbooksellerid
2149 sub getsupplierbyserialid {
2150 my ($serialid) = @_;
2151 my $dbh = C4::Context->dbh;
2152 my $sth = $dbh->prepare(
2153 "SELECT serialid, serial.subscriptionid, aqbooksellerid
2155 LEFT JOIN subscription ON serial.subscriptionid = subscription.subscriptionid
2159 $sth->execute($serialid);
2160 my $line = $sth->fetchrow_hashref;
2161 my $result = $line->{'aqbooksellerid'};
2165 =head2 check_routing
2167 $result = &check_routing($subscriptionid)
2169 this function checks to see if a serial has a routing list and returns the count of routingid
2170 used to show either an 'add' or 'edit' link
2175 my ($subscriptionid) = @_;
2177 return unless ($subscriptionid);
2179 my $dbh = C4::Context->dbh;
2180 my $sth = $dbh->prepare(
2181 "SELECT count(routingid) routingids FROM subscription LEFT JOIN subscriptionroutinglist
2182 ON subscription.subscriptionid = subscriptionroutinglist.subscriptionid
2183 WHERE subscription.subscriptionid = ? ORDER BY ranking ASC
2186 $sth->execute($subscriptionid);
2187 my $line = $sth->fetchrow_hashref;
2188 my $result = $line->{'routingids'};
2192 =head2 addroutingmember
2194 addroutingmember($borrowernumber,$subscriptionid)
2196 this function takes a borrowernumber and subscriptionid and adds the member to the
2197 routing list for that serial subscription and gives them a rank on the list
2198 of either 1 or highest current rank + 1
2202 sub addroutingmember {
2203 my ( $borrowernumber, $subscriptionid ) = @_;
2205 return unless ($borrowernumber and $subscriptionid);
2208 my $dbh = C4::Context->dbh;
2209 my $sth = $dbh->prepare( "SELECT max(ranking) rank FROM subscriptionroutinglist WHERE subscriptionid = ?" );
2210 $sth->execute($subscriptionid);
2211 while ( my $line = $sth->fetchrow_hashref ) {
2212 if ( $line->{'rank'} > 0 ) {
2213 $rank = $line->{'rank'} + 1;
2218 $sth = $dbh->prepare( "INSERT INTO subscriptionroutinglist (subscriptionid,borrowernumber,ranking) VALUES (?,?,?)" );
2219 $sth->execute( $subscriptionid, $borrowernumber, $rank );
2222 =head2 reorder_members
2224 reorder_members($subscriptionid,$routingid,$rank)
2226 this function is used to reorder the routing list
2228 it takes the routingid of the member one wants to re-rank and the rank it is to move to
2229 - it gets all members on list puts their routingid's into an array
2230 - removes the one in the array that is $routingid
2231 - then reinjects $routingid at point indicated by $rank
2232 - then update the database with the routingids in the new order
2236 sub reorder_members {
2237 my ( $subscriptionid, $routingid, $rank ) = @_;
2238 my $dbh = C4::Context->dbh;
2239 my $sth = $dbh->prepare( "SELECT * FROM subscriptionroutinglist WHERE subscriptionid = ? ORDER BY ranking ASC" );
2240 $sth->execute($subscriptionid);
2242 while ( my $line = $sth->fetchrow_hashref ) {
2243 push( @result, $line->{'routingid'} );
2246 # To find the matching index
2248 my $key = -1; # to allow for 0 being a valid response
2249 for ( $i = 0 ; $i < @result ; $i++ ) {
2250 if ( $routingid == $result[$i] ) {
2251 $key = $i; # save the index
2256 # if index exists in array then move it to new position
2257 if ( $key > -1 && $rank > 0 ) {
2258 my $new_rank = $rank - 1; # $new_rank is what you want the new index to be in the array
2259 my $moving_item = splice( @result, $key, 1 );
2260 splice( @result, $new_rank, 0, $moving_item );
2262 for ( my $j = 0 ; $j < @result ; $j++ ) {
2263 my $sth = $dbh->prepare( "UPDATE subscriptionroutinglist SET ranking = '" . ( $j + 1 ) . "' WHERE routingid = '" . $result[$j] . "'" );
2269 =head2 delroutingmember
2271 delroutingmember($routingid,$subscriptionid)
2273 this function either deletes one member from routing list if $routingid exists otherwise
2274 deletes all members from the routing list
2278 sub delroutingmember {
2280 # if $routingid exists then deletes that row otherwise deletes all with $subscriptionid
2281 my ( $routingid, $subscriptionid ) = @_;
2282 my $dbh = C4::Context->dbh;
2284 my $sth = $dbh->prepare("DELETE FROM subscriptionroutinglist WHERE routingid = ?");
2285 $sth->execute($routingid);
2286 reorder_members( $subscriptionid, $routingid );
2288 my $sth = $dbh->prepare("DELETE FROM subscriptionroutinglist WHERE subscriptionid = ?");
2289 $sth->execute($subscriptionid);
2294 =head2 getroutinglist
2296 @routinglist = getroutinglist($subscriptionid)
2298 this gets the info from the subscriptionroutinglist for $subscriptionid
2301 the routinglist as an array. Each element of the array contains a hash_ref containing
2302 routingid - a unique id, borrowernumber, ranking, and biblionumber of subscription
2306 sub getroutinglist {
2307 my ($subscriptionid) = @_;
2308 my $dbh = C4::Context->dbh;
2309 my $sth = $dbh->prepare(
2310 'SELECT routingid, borrowernumber, ranking, biblionumber
2312 JOIN subscriptionroutinglist ON subscription.subscriptionid = subscriptionroutinglist.subscriptionid
2313 WHERE subscription.subscriptionid = ? ORDER BY ranking ASC'
2315 $sth->execute($subscriptionid);
2316 my $routinglist = $sth->fetchall_arrayref({});
2317 return @{$routinglist};
2320 =head2 countissuesfrom
2322 $result = countissuesfrom($subscriptionid,$startdate)
2324 Returns a count of serial rows matching the given subsctiptionid
2325 with published date greater than startdate
2329 sub countissuesfrom {
2330 my ( $subscriptionid, $startdate ) = @_;
2331 my $dbh = C4::Context->dbh;
2335 WHERE subscriptionid=?
2336 AND serial.publisheddate>?
2338 my $sth = $dbh->prepare($query);
2339 $sth->execute( $subscriptionid, $startdate );
2340 my ($countreceived) = $sth->fetchrow;
2341 return $countreceived;
2346 $result = CountIssues($subscriptionid)
2348 Returns a count of serial rows matching the given subsctiptionid
2353 my ($subscriptionid) = @_;
2354 my $dbh = C4::Context->dbh;
2358 WHERE subscriptionid=?
2360 my $sth = $dbh->prepare($query);
2361 $sth->execute($subscriptionid);
2362 my ($countreceived) = $sth->fetchrow;
2363 return $countreceived;
2368 $result = HasItems($subscriptionid)
2370 returns a count of items from serial matching the subscriptionid
2375 my ($subscriptionid) = @_;
2376 my $dbh = C4::Context->dbh;
2378 SELECT COUNT(serialitems.itemnumber)
2380 LEFT JOIN serialitems USING(serialid)
2381 WHERE subscriptionid=? AND serialitems.serialid IS NOT NULL
2383 my $sth=$dbh->prepare($query);
2384 $sth->execute($subscriptionid);
2385 my ($countitems)=$sth->fetchrow_array();
2389 =head2 abouttoexpire
2391 $result = abouttoexpire($subscriptionid)
2393 this function alerts you to the penultimate issue for a serial subscription
2395 returns 1 - if this is the penultimate issue
2401 my ($subscriptionid) = @_;
2402 my $dbh = C4::Context->dbh;
2403 my $subscription = GetSubscription($subscriptionid);
2404 my $per = $subscription->{'periodicity'};
2405 my $frequency = C4::Serials::Frequency::GetSubscriptionFrequency($per);
2406 if ($frequency and $frequency->{unit}){
2407 my $expirationdate = GetExpirationDate($subscriptionid);
2408 my ($res) = $dbh->selectrow_array('select max(planneddate) from serial where subscriptionid = ?', undef, $subscriptionid);
2409 my $nextdate = GetNextDate($subscription, $res);
2410 if(Date::Calc::Delta_Days(
2411 split( /-/, $nextdate ),
2412 split( /-/, $expirationdate )
2416 } elsif ($subscription->{numberlength}>0) {
2417 return (countissuesfrom($subscriptionid,$subscription->{'startdate'}) >=$subscription->{numberlength}-1);
2422 sub in_array { # used in next sub down
2423 my ( $val, @elements ) = @_;
2424 foreach my $elem (@elements) {
2425 if ( $val == $elem ) {
2432 =head2 GetSubscriptionsFromBorrower
2434 ($count,@routinglist) = GetSubscriptionsFromBorrower($borrowernumber)
2436 this gets the info from subscriptionroutinglist for each $subscriptionid
2439 a count of the serial subscription routing lists to which a patron belongs,
2440 with the titles of those serial subscriptions as an array. Each element of the array
2441 contains a hash_ref with subscriptionID and title of subscription.
2445 sub GetSubscriptionsFromBorrower {
2446 my ($borrowernumber) = @_;
2447 my $dbh = C4::Context->dbh;
2448 my $sth = $dbh->prepare(
2449 "SELECT subscription.subscriptionid, biblio.title
2451 JOIN biblio ON biblio.biblionumber = subscription.biblionumber
2452 JOIN subscriptionroutinglist USING (subscriptionid)
2453 WHERE subscriptionroutinglist.borrowernumber = ? ORDER BY title ASC
2456 $sth->execute($borrowernumber);
2459 while ( my $line = $sth->fetchrow_hashref ) {
2461 push( @routinglist, $line );
2463 return ( $count, @routinglist );
2467 =head2 GetFictiveIssueNumber
2469 $issueno = GetFictiveIssueNumber($subscription, $publishedate);
2471 Get the position of the issue published at $publisheddate, considering the
2472 first issue (at firstacquidate) is at position 1, the next is at position 2, etc...
2473 This issuenumber doesn't take into account irregularities, so, for instance, if the 3rd
2474 issue is declared as 'irregular' (will be skipped at receipt), the next issue number
2475 will be 4, not 3. It's why it is called 'fictive'. It is NOT a serial seq, and is not
2476 depending on how many rows are in serial table.
2477 The issue number calculation is based on subscription frequency, first acquisition
2478 date, and $publisheddate.
2482 sub GetFictiveIssueNumber {
2483 my ($subscription, $publisheddate) = @_;
2485 my $frequency = GetSubscriptionFrequency($subscription->{'periodicity'});
2486 my $unit = $frequency->{unit} ? lc $frequency->{'unit'} : undef;
2490 my ($year, $month, $day) = split /-/, $publisheddate;
2491 my ($fa_year, $fa_month, $fa_day) = split /-/, $subscription->{'firstacquidate'};
2495 if($unit eq 'day') {
2496 $delta = Delta_Days($fa_year, $fa_month, $fa_day, $year, $month, $day);
2497 } elsif($unit eq 'week') {
2498 ($wkno, $year) = Week_of_Year($year, $month, $day);
2499 my ($fa_wkno, $fa_yr) = Week_of_Year($fa_year, $fa_month, $fa_day);
2500 $delta = ($fa_yr == $year) ? ($wkno - $fa_wkno) : ( ($year-$fa_yr-1)*52 + (52-$fa_wkno+$wkno) );
2501 } elsif($unit eq 'month') {
2502 $delta = ($fa_year == $year)
2503 ? ($month - $fa_month)
2504 : ( ($year-$fa_year-1)*12 + (12-$fa_month+$month) );
2505 } elsif($unit eq 'year') {
2506 $delta = $year - $fa_year;
2508 if($frequency->{'unitsperissue'} == 1) {
2509 $issueno = $delta * $frequency->{'issuesperunit'} + $subscription->{'countissuesperunit'};
2511 # Assuming issuesperunit == 1
2512 $issueno = int( ($delta + $frequency->{'unitsperissue'}) / $frequency->{'unitsperissue'} );
2520 $resultdate = GetNextDate($publisheddate,$subscription)
2522 this function it takes the publisheddate and will return the next issue's date
2523 and will skip dates if there exists an irregularity.
2524 $publisheddate has to be an ISO date
2525 $subscription is a hashref containing at least 'periodicity', 'firstacquidate', 'irregularity', and 'countissuesperunit'
2526 $updatecount is a boolean value which, when set to true, update the 'countissuesperunit' in database
2527 - eg if periodicity is monthly and $publisheddate is 2007-02-10 but if March and April is to be
2528 skipped then the returned date will be 2007-05-10
2531 $resultdate - then next date in the sequence (ISO date)
2533 Return $publisheddate if subscription is irregular
2538 my ( $subscription, $publisheddate, $updatecount ) = @_;
2540 my $freqdata = GetSubscriptionFrequency($subscription->{'periodicity'});
2542 if ($freqdata->{'unit'}) {
2543 my ( $year, $month, $day ) = split /-/, $publisheddate;
2545 # Process an irregularity Hash
2546 # Suppose that irregularities are stored in a string with this structure
2547 # irreg1;irreg2;irreg3
2548 # where irregX is the number of issue which will not be received
2549 # (the first issue takes the number 1, the 2nd the number 2 and so on)
2550 my @irreg = split /;/, $subscription->{'irregularity'} ;
2552 foreach my $irregularity (@irreg) {
2553 $irregularities{$irregularity} = 1;
2556 # Get the 'fictive' next issue number
2557 # It is used to check if next issue is an irregular issue.
2558 my $issueno = GetFictiveIssueNumber($subscription, $publisheddate) + 1;
2560 # Then get the next date
2561 my $unit = lc $freqdata->{'unit'};
2562 if ($unit eq 'day') {
2563 while ($irregularities{$issueno}) {
2564 if ($subscription->{'countissuesperunit'} + 1 > $freqdata->{'issuesperunit'}){
2565 ($year,$month,$day) = Add_Delta_Days($year,$month, $day , $freqdata->{'unitsperissue'} );
2566 $subscription->{'countissuesperunit'} = 1;
2568 $subscription->{'countissuesperunit'}++;
2572 if ($subscription->{'countissuesperunit'} + 1 > $freqdata->{'issuesperunit'}){
2573 ($year,$month,$day) = Add_Delta_Days($year,$month, $day , $freqdata->{"unitsperissue"} );
2574 $subscription->{'countissuesperunit'} = 1;
2576 $subscription->{'countissuesperunit'}++;
2579 elsif ($unit eq 'week') {
2580 my ($wkno, $yr) = Week_of_Year($year, $month, $day);
2581 while ($irregularities{$issueno}) {
2582 if ($subscription->{'countissuesperunit'} + 1 > $freqdata->{'issuesperunit'}){
2583 $subscription->{'countissuesperunit'} = 1;
2584 $wkno += $freqdata->{"unitsperissue"};
2589 my $dow = Day_of_Week($year, $month, $day);
2590 ($year,$month,$day) = Monday_of_Week($wkno, $yr);
2591 if($freqdata->{'issuesperunit'} == 1) {
2592 ($year, $month, $day) = Add_Delta_Days($year, $month, $day, $dow - 1);
2595 $subscription->{'countissuesperunit'}++;
2599 if ($subscription->{'countissuesperunit'} + 1 > $freqdata->{'issuesperunit'}){
2600 $subscription->{'countissuesperunit'} = 1;
2601 $wkno += $freqdata->{"unitsperissue"};
2603 $wkno = $wkno % 52 ;
2606 my $dow = Day_of_Week($year, $month, $day);
2607 ($year,$month,$day) = Monday_of_Week($wkno, $yr);
2608 if($freqdata->{'issuesperunit'} == 1) {
2609 ($year, $month, $day) = Add_Delta_Days($year, $month, $day, $dow - 1);
2612 $subscription->{'countissuesperunit'}++;
2615 elsif ($unit eq 'month') {
2616 while ($irregularities{$issueno}) {
2617 if ($subscription->{'countissuesperunit'} + 1 > $freqdata->{'issuesperunit'}){
2618 $subscription->{'countissuesperunit'} = 1;
2619 ($year,$month,$day) = Add_Delta_YM($year,$month,$day, 0,$freqdata->{"unitsperissue"});
2620 unless($freqdata->{'issuesperunit'} == 1) {
2621 $day = 1; # Jumping to the first day of month, because we don't know what day is expected
2624 $subscription->{'countissuesperunit'}++;
2628 if ($subscription->{'countissuesperunit'} + 1 > $freqdata->{'issuesperunit'}){
2629 $subscription->{'countissuesperunit'} = 1;
2630 ($year,$month,$day) = Add_Delta_YM($year,$month,$day, 0,$freqdata->{"unitsperissue"});
2631 unless($freqdata->{'issuesperunit'} == 1) {
2632 $day = 1; # Jumping to the first day of month, because we don't know what day is expected
2635 $subscription->{'countissuesperunit'}++;
2638 elsif ($unit eq 'year') {
2639 while ($irregularities{$issueno}) {
2640 if ($subscription->{'countissuesperunit'} + 1 > $freqdata->{'issuesperunit'}){
2641 $subscription->{'countissuesperunit'} = 1;
2642 ($year,$month,$day) = Add_Delta_YM($year,$month,$day, $freqdata->{"unitsperissue"},0);
2643 unless($freqdata->{'issuesperunit'} == 1) {
2644 # Jumping to the first day of year, because we don't know what day is expected
2649 $subscription->{'countissuesperunit'}++;
2653 if ($subscription->{'countissuesperunit'} + 1 > $freqdata->{'issuesperunit'}){
2654 $subscription->{'countissuesperunit'} = 1;
2655 ($year,$month,$day) = Add_Delta_YM($year,$month,$day, $freqdata->{"unitsperissue"},0);
2656 unless($freqdata->{'issuesperunit'} == 1) {
2657 # Jumping to the first day of year, because we don't know what day is expected
2662 $subscription->{'countissuesperunit'}++;
2666 my $dbh = C4::Context->dbh;
2669 SET countissuesperunit = ?
2670 WHERE subscriptionid = ?
2672 my $sth = $dbh->prepare($query);
2673 $sth->execute($subscription->{'countissuesperunit'}, $subscription->{'subscriptionid'});
2675 return sprintf("%04d-%02d-%02d", $year, $month, $day);
2678 return $publisheddate;
2684 $string = &_numeration($value,$num_type,$locale);
2686 _numeration returns the string corresponding to $value in the num_type
2696 my ($value, $num_type, $locale) = @_;
2698 my $initlocale = setlocale(LC_TIME);
2699 if($locale and $locale ne $initlocale) {
2700 $locale = setlocale(LC_TIME, $locale);
2702 $locale ||= $initlocale;
2706 when (/^dayname$/) {
2707 $value = $value % 7;
2708 $string = POSIX::strftime("%A",0,0,0,0,0,0,$value);
2710 when (/^monthname$/) {
2711 $value = $value % 12;
2712 $string = POSIX::strftime("%B",0,0,0,1,$value,0,0,0,0);
2715 my $seasonlocale = ($locale)
2716 ? (substr $locale,0,2)
2720 [qw(Spring Summer Fall Winter)],
2722 [qw(Printemps Été Automne Hiver)],
2724 $value = $value % 4;
2725 $string = ($seasons{$seasonlocale})
2726 ? $seasons{$seasonlocale}->[$value]
2727 : $seasons{'en'}->[$value];
2733 if($locale ne $initlocale) {
2734 setlocale(LC_TIME, $initlocale);
2739 =head2 is_barcode_in_use
2741 Returns number of occurence of the barcode in the items table
2742 Can be used as a boolean test of whether the barcode has
2743 been deployed as yet
2747 sub is_barcode_in_use {
2748 my $barcode = shift;
2749 my $dbh = C4::Context->dbh;
2750 my $occurences = $dbh->selectall_arrayref(
2751 'SELECT itemnumber from items where barcode = ?',
2756 return @{$occurences};
2759 =head2 CloseSubscription
2760 Close a subscription given a subscriptionid
2762 sub CloseSubscription {
2763 my ( $subscriptionid ) = @_;
2764 return unless $subscriptionid;
2765 my $dbh = C4::Context->dbh;
2766 my $sth = $dbh->prepare( qq{
2769 WHERE subscriptionid = ?
2771 $sth->execute( $subscriptionid );
2773 # Set status = missing when status = stopped
2774 $sth = $dbh->prepare( qq{
2777 WHERE subscriptionid = ?
2780 $sth->execute( $subscriptionid );
2783 =head2 ReopenSubscription
2784 Reopen a subscription given a subscriptionid
2786 sub ReopenSubscription {
2787 my ( $subscriptionid ) = @_;
2788 return unless $subscriptionid;
2789 my $dbh = C4::Context->dbh;
2790 my $sth = $dbh->prepare( qq{
2793 WHERE subscriptionid = ?
2795 $sth->execute( $subscriptionid );
2797 # Set status = expected when status = stopped
2798 $sth = $dbh->prepare( qq{
2801 WHERE subscriptionid = ?
2804 $sth->execute( $subscriptionid );
2807 =head2 subscriptionCurrentlyOnOrder
2809 $bool = subscriptionCurrentlyOnOrder( $subscriptionid );
2811 Return 1 if subscription is currently on order else 0.
2815 sub subscriptionCurrentlyOnOrder {
2816 my ( $subscriptionid ) = @_;
2817 my $dbh = C4::Context->dbh;
2819 SELECT COUNT(*) FROM aqorders
2820 WHERE subscriptionid = ?
2821 AND datereceived IS NULL
2822 AND datecancellationprinted IS NULL
2824 my $sth = $dbh->prepare( $query );
2825 $sth->execute($subscriptionid);
2826 return $sth->fetchrow_array;
2829 =head2 can_edit_subscription
2831 $can = can_edit_subscription( $subscriptionid[, $userid] );
2833 Return 1 if the subscription is editable by the current logged user (or a given $userid), else 0.
2837 sub can_edit_subscription {
2838 my ( $subscription, $userid ) = @_;
2839 return 0 unless C4::Context->userenv;
2840 my $flags = C4::Context->userenv->{flags};
2841 $userid ||= C4::Context->userenv->{'id'};
2842 my $independent_branches = C4::Context->preference('IndependentBranches');
2843 return 1 unless $independent_branches;
2844 if( C4::Context->IsSuperLibrarian()
2845 or C4::Auth::haspermission( $userid, {serials => 'superserials'}),
2846 or C4::Auth::haspermission( $userid, {serials => 'edit_subscription'}),
2847 or not defined $subscription->{branchcode}
2848 or $subscription->{branchcode} eq ''
2849 or $subscription->{branchcode} eq C4::Context->userenv->{'branch'}
2861 Koha Development Team <http://koha-community.org/>