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 = 3 OR serial.STATUS = 4
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 $subs->{"checked"} = $subs->{'status'} =~ /1|3|4|7/;
430 if ( $subs->{'year'} && $subs->{'year'} ne "" ) {
431 $year = $subs->{'year'};
435 if ( $tmpresults{$year} ) {
436 push @{ $tmpresults{$year}->{'serials'} }, $subs;
438 $tmpresults{$year} = {
440 'aqbooksellername' => $subs->{'aqbooksellername'},
441 'bibliotitle' => $subs->{'bibliotitle'},
442 'serials' => [$subs],
447 foreach my $key ( sort { $b cmp $a } keys %tmpresults ) {
448 push @res, $tmpresults{$key};
453 =head2 GetSubscriptionsFromBiblionumber
455 $array_ref = GetSubscriptionsFromBiblionumber($biblionumber)
456 this function get the subscription list. it reads the subscription table.
458 reference to an array of subscriptions which have the biblionumber given on input arg.
459 each element of this array is a hashref containing
460 startdate, histstartdate,opacnote,missinglist,recievedlist,periodicity,status & enddate
464 sub GetSubscriptionsFromBiblionumber {
465 my ($biblionumber) = @_;
467 return unless ($biblionumber);
469 my $dbh = C4::Context->dbh;
471 SELECT subscription.*,
473 subscriptionhistory.*,
474 aqbooksellers.name AS aqbooksellername,
475 biblio.title AS bibliotitle
477 LEFT JOIN subscriptionhistory ON subscription.subscriptionid=subscriptionhistory.subscriptionid
478 LEFT JOIN aqbooksellers ON subscription.aqbooksellerid=aqbooksellers.id
479 LEFT JOIN biblio ON biblio.biblionumber=subscription.biblionumber
480 LEFT JOIN branches ON branches.branchcode=subscription.branchcode
481 WHERE subscription.biblionumber = ?
483 my $sth = $dbh->prepare($query);
484 $sth->execute($biblionumber);
486 while ( my $subs = $sth->fetchrow_hashref ) {
487 $subs->{startdate} = format_date( $subs->{startdate} );
488 $subs->{histstartdate} = format_date( $subs->{histstartdate} );
489 $subs->{histenddate} = format_date( $subs->{histenddate} );
490 $subs->{opacnote} =~ s/\n/\<br\/\>/g;
491 $subs->{missinglist} =~ s/\n/\<br\/\>/g;
492 $subs->{recievedlist} =~ s/\n/\<br\/\>/g;
493 $subs->{ "periodicity" . $subs->{periodicity} } = 1;
494 $subs->{ "numberpattern" . $subs->{numberpattern} } = 1;
495 $subs->{ "status" . $subs->{'status'} } = 1;
497 if ( $subs->{enddate} eq '0000-00-00' ) {
498 $subs->{enddate} = '';
500 $subs->{enddate} = format_date( $subs->{enddate} );
502 $subs->{'abouttoexpire'} = abouttoexpire( $subs->{'subscriptionid'} );
503 $subs->{'subscriptionexpired'} = HasSubscriptionExpired( $subs->{'subscriptionid'} );
504 $subs->{cannotedit} = not can_edit_subscription( $subs );
510 =head2 GetFullSubscriptionsFromBiblionumber
512 $array_ref = GetFullSubscriptionsFromBiblionumber($biblionumber)
513 this function reads the serial table.
517 sub GetFullSubscriptionsFromBiblionumber {
518 my ($biblionumber) = @_;
519 my $dbh = C4::Context->dbh;
521 SELECT serial.serialid,
524 serial.publisheddate,
526 serial.notes as notes,
527 year(IF(serial.publisheddate="00-00-0000",serial.planneddate,serial.publisheddate)) as year,
528 biblio.title as bibliotitle,
529 subscription.branchcode AS branchcode,
530 subscription.subscriptionid AS subscriptionid
532 LEFT JOIN subscription ON
533 (serial.subscriptionid=subscription.subscriptionid)
534 LEFT JOIN aqbooksellers on subscription.aqbooksellerid=aqbooksellers.id
535 LEFT JOIN biblio on biblio.biblionumber=subscription.biblionumber
536 WHERE subscription.biblionumber = ?
538 IF(serial.publisheddate="00-00-0000",serial.planneddate,serial.publisheddate) DESC,
539 serial.subscriptionid
541 my $sth = $dbh->prepare($query);
542 $sth->execute($biblionumber);
543 my $subscriptions = $sth->fetchall_arrayref( {} );
544 for my $subscription ( @$subscriptions ) {
545 $subscription->{cannotedit} = not can_edit_subscription( $subscription );
547 return $subscriptions;
550 =head2 GetSubscriptions
552 @results = GetSubscriptions($title,$ISSN,$ean,$biblionumber);
553 this function gets all subscriptions which have title like $title,ISSN like $ISSN,EAN like $ean and biblionumber like $biblionumber.
555 a table of hashref. Each hash containt the subscription.
559 sub GetSubscriptions {
560 my ( $string, $issn, $ean, $biblionumber ) = @_;
562 #return unless $title or $ISSN or $biblionumber;
563 my $dbh = C4::Context->dbh;
566 SELECT subscriptionhistory.*, subscription.*, biblio.title,biblioitems.issn,biblio.biblionumber
568 LEFT JOIN subscriptionhistory USING(subscriptionid)
569 LEFT JOIN biblio ON biblio.biblionumber = subscription.biblionumber
570 LEFT JOIN biblioitems ON biblio.biblionumber = biblioitems.biblionumber
575 $sqlwhere = " WHERE biblio.biblionumber=?";
576 push @bind_params, $biblionumber;
580 my @strings_to_search;
581 @strings_to_search = map { "%$_%" } split( / /, $string );
582 foreach my $index (qw(biblio.title subscription.callnumber subscription.location subscription.notes subscription.internalnotes)) {
583 push @bind_params, @strings_to_search;
584 my $tmpstring = "AND $index LIKE ? " x scalar(@strings_to_search);
585 $debug && warn "$tmpstring";
586 $tmpstring =~ s/^AND //;
587 push @sqlstrings, $tmpstring;
589 $sqlwhere .= ( $sqlwhere ? " AND " : " WHERE " ) . "((" . join( ") OR (", @sqlstrings ) . "))";
593 my @strings_to_search;
594 @strings_to_search = map { "%$_%" } split( / /, $issn );
595 foreach my $index ( qw(biblioitems.issn subscription.callnumber)) {
596 push @bind_params, @strings_to_search;
597 my $tmpstring = "OR $index LIKE ? " x scalar(@strings_to_search);
598 $debug && warn "$tmpstring";
599 $tmpstring =~ s/^OR //;
600 push @sqlstrings, $tmpstring;
602 $sqlwhere .= ( $sqlwhere ? " AND " : " WHERE " ) . "((" . join( ") OR (", @sqlstrings ) . "))";
606 my @strings_to_search;
607 @strings_to_search = map { "$_" } split( / /, $ean );
608 foreach my $index ( qw(biblioitems.ean) ) {
609 push @bind_params, @strings_to_search;
610 my $tmpstring = "OR $index = ? " x scalar(@strings_to_search);
611 $debug && warn "$tmpstring";
612 $tmpstring =~ s/^OR //;
613 push @sqlstrings, $tmpstring;
615 $sqlwhere .= ( $sqlwhere ? " AND " : " WHERE " ) . "((" . join( ") OR (", @sqlstrings ) . "))";
618 $sql .= "$sqlwhere ORDER BY title";
619 $debug and warn "GetSubscriptions query: $sql params : ", join( " ", @bind_params );
620 $sth = $dbh->prepare($sql);
621 $sth->execute(@bind_params);
622 my $subscriptions = $sth->fetchall_arrayref( {} );
623 for my $subscription ( @$subscriptions ) {
624 $subscription->{cannotedit} = not can_edit_subscription( $subscription );
626 return @$subscriptions;
629 =head2 SearchSubscriptions
631 @results = SearchSubscriptions($args);
632 $args is a hashref. Its keys can be contained: title, issn, ean, publisher, bookseller and branchcode
634 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.
637 a table of hashref. Each hash containt the subscription.
641 sub SearchSubscriptions {
646 subscription.notes AS publicnotes,
648 subscriptionhistory.*,
649 biblio.notes AS biblionotes,
654 LEFT JOIN subscriptionhistory USING(subscriptionid)
655 LEFT JOIN biblio ON biblio.biblionumber = subscription.biblionumber
656 LEFT JOIN biblioitems ON biblioitems.biblionumber = subscription.biblionumber
657 LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id
661 if( $args->{biblionumber} ) {
662 push @where_strs, "biblio.biblionumber = ?";
663 push @where_args, $args->{biblionumber};
665 if( $args->{title} ){
666 my @words = split / /, $args->{title};
668 foreach my $word (@words) {
669 push @strs, "biblio.title LIKE ?";
670 push @args, "%$word%";
673 push @where_strs, '(' . join (' AND ', @strs) . ')';
674 push @where_args, @args;
678 push @where_strs, "biblioitems.issn LIKE ?";
679 push @where_args, "%$args->{issn}%";
682 push @where_strs, "biblioitems.ean LIKE ?";
683 push @where_args, "%$args->{ean}%";
685 if( $args->{publisher} ){
686 push @where_strs, "biblioitems.publishercode LIKE ?";
687 push @where_args, "%$args->{publisher}%";
689 if( $args->{bookseller} ){
690 push @where_strs, "aqbooksellers.name LIKE ?";
691 push @where_args, "%$args->{bookseller}%";
693 if( $args->{branch} ){
694 push @where_strs, "subscription.branchcode = ?";
695 push @where_args, "$args->{branch}";
697 if( defined $args->{closed} ){
698 push @where_strs, "subscription.closed = ?";
699 push @where_args, "$args->{closed}";
702 $query .= " WHERE " . join(" AND ", @where_strs);
705 my $dbh = C4::Context->dbh;
706 my $sth = $dbh->prepare($query);
707 $sth->execute(@where_args);
708 my $results = $sth->fetchall_arrayref( {} );
711 for my $subscription ( @$results ) {
712 $subscription->{cannotedit} = not can_edit_subscription( $subscription );
713 $subscription->{cannotdisplay} =
714 ( C4::Context->preference("IndependentBranches")
715 and $subscription->{branchcode} ne C4::Context->userenv->{'branch'} ) ? 1 : 0;
724 ($totalissues,@serials) = GetSerials($subscriptionid);
725 this function gets every serial not arrived for a given subscription
726 as well as the number of issues registered in the database (all types)
727 this number is used to see if a subscription can be deleted (=it must have only 1 issue)
729 FIXME: We should return \@serials.
734 my ( $subscriptionid, $count ) = @_;
736 return unless $subscriptionid;
738 my $dbh = C4::Context->dbh;
740 # status = 2 is "arrived"
742 $count = 5 unless ($count);
744 my $query = "SELECT serialid,serialseq, status, publisheddate, planneddate,notes, routingnotes
746 WHERE subscriptionid = ? AND status NOT IN (2,4,5)
747 ORDER BY IF(publisheddate<>'0000-00-00',publisheddate,planneddate) DESC";
748 my $sth = $dbh->prepare($query);
749 $sth->execute($subscriptionid);
751 while ( my $line = $sth->fetchrow_hashref ) {
752 $line->{ "status" . $line->{status} } = 1; # fills a "statusX" value, used for template status select list
753 for my $datefield ( qw( planneddate publisheddate) ) {
754 if ($line->{$datefield} && $line->{$datefield}!~m/^00/) {
755 $line->{$datefield} = format_date( $line->{$datefield});
757 $line->{$datefield} = q{};
760 push @serials, $line;
763 # OK, now add the last 5 issues arrives/missing
764 $query = "SELECT serialid,serialseq, status, planneddate, publisheddate,notes, routingnotes
766 WHERE subscriptionid = ?
767 AND (status in (2,4,5))
768 ORDER BY IF(publisheddate<>'0000-00-00',publisheddate,planneddate) DESC
770 $sth = $dbh->prepare($query);
771 $sth->execute($subscriptionid);
772 while ( ( my $line = $sth->fetchrow_hashref ) && $counter < $count ) {
774 $line->{ "status" . $line->{status} } = 1; # fills a "statusX" value, used for template status select list
775 for my $datefield ( qw( planneddate publisheddate) ) {
776 if ($line->{$datefield} && $line->{$datefield}!~m/^00/) {
777 $line->{$datefield} = format_date( $line->{$datefield});
779 $line->{$datefield} = q{};
783 push @serials, $line;
786 $query = "SELECT count(*) FROM serial WHERE subscriptionid=?";
787 $sth = $dbh->prepare($query);
788 $sth->execute($subscriptionid);
789 my ($totalissues) = $sth->fetchrow;
790 return ( $totalissues, @serials );
795 @serials = GetSerials2($subscriptionid,$status);
796 this function returns every serial waited for a given subscription
797 as well as the number of issues registered in the database (all types)
798 this number is used to see if a subscription can be deleted (=it must have only 1 issue)
803 my ( $subscription, $status ) = @_;
805 return unless ($subscription and $status);
807 my $dbh = C4::Context->dbh;
809 SELECT serialid,serialseq, status, planneddate, publisheddate,notes, routingnotes
811 WHERE subscriptionid=$subscription AND status IN ($status)
812 ORDER BY publisheddate,serialid DESC
814 $debug and warn "GetSerials2 query: $query";
815 my $sth = $dbh->prepare($query);
819 while ( my $line = $sth->fetchrow_hashref ) {
820 $line->{ "status" . $line->{status} } = 1; # fills a "statusX" value, used for template status select list
821 # Format dates for display
822 for my $datefield ( qw( planneddate publisheddate ) ) {
823 if ($line->{$datefield} =~m/^00/) {
824 $line->{$datefield} = q{};
827 $line->{$datefield} = format_date( $line->{$datefield} );
830 push @serials, $line;
835 =head2 GetLatestSerials
837 \@serials = GetLatestSerials($subscriptionid,$limit)
838 get the $limit's latest serials arrived or missing for a given subscription
840 a ref to an array which contains all of the latest serials stored into a hash.
844 sub GetLatestSerials {
845 my ( $subscriptionid, $limit ) = @_;
847 return unless ($subscriptionid and $limit);
849 my $dbh = C4::Context->dbh;
851 # status = 2 is "arrived"
852 my $strsth = "SELECT serialid,serialseq, status, planneddate, publisheddate, notes
854 WHERE subscriptionid = ?
855 AND (status =2 or status=4)
856 ORDER BY publisheddate DESC LIMIT 0,$limit
858 my $sth = $dbh->prepare($strsth);
859 $sth->execute($subscriptionid);
861 while ( my $line = $sth->fetchrow_hashref ) {
862 $line->{ "status" . $line->{status} } = 1; # fills a "statusX" value, used for template status select list
863 $line->{"planneddate"} = format_date( $line->{"planneddate"} );
864 $line->{"publisheddate"} = format_date( $line->{"publisheddate"} );
865 push @serials, $line;
871 =head2 GetDistributedTo
873 $distributedto=GetDistributedTo($subscriptionid)
874 This function returns the field distributedto for the subscription matching subscriptionid
878 sub GetDistributedTo {
879 my $dbh = C4::Context->dbh;
881 my ($subscriptionid) = @_;
883 return unless ($subscriptionid);
885 my $query = "SELECT distributedto FROM subscription WHERE subscriptionid=?";
886 my $sth = $dbh->prepare($query);
887 $sth->execute($subscriptionid);
888 return ($distributedto) = $sth->fetchrow;
894 $nextseq, $newlastvalue1, $newlastvalue2, $newlastvalue3,
895 $newinnerloop1, $newinnerloop2, $newinnerloop3
896 ) = GetNextSeq( $subscription, $pattern, $planneddate );
898 $subscription is a hashref containing all the attributes of the table
900 $pattern is a hashref containing all the attributes of the table
901 'subscription_numberpatterns'.
902 $planneddate is a C4::Dates object.
903 This function get the next issue for the subscription given on input arg
908 my ($subscription, $pattern, $planneddate) = @_;
910 return unless ($subscription and $pattern);
912 my ( $newlastvalue1, $newlastvalue2, $newlastvalue3,
913 $newinnerloop1, $newinnerloop2, $newinnerloop3 );
916 if ($subscription->{'skip_serialseq'}) {
917 my @irreg = split /;/, $subscription->{'irregularity'};
919 my $irregularities = {};
920 $irregularities->{$_} = 1 foreach(@irreg);
921 my $issueno = GetFictiveIssueNumber($subscription, $planneddate) + 1;
922 while($irregularities->{$issueno}) {
929 my $numberingmethod = $pattern->{numberingmethod};
931 if ($numberingmethod) {
932 $calculated = $numberingmethod;
933 my $locale = $subscription->{locale};
934 $newlastvalue1 = $subscription->{lastvalue1} || 0;
935 $newlastvalue2 = $subscription->{lastvalue2} || 0;
936 $newlastvalue3 = $subscription->{lastvalue3} || 0;
937 $newinnerloop1 = $subscription->{innerloop1} || 0;
938 $newinnerloop2 = $subscription->{innerloop2} || 0;
939 $newinnerloop3 = $subscription->{innerloop3} || 0;
942 $calc{$_} = 1 if ($numberingmethod =~ /\{$_\}/);
945 for(my $i = 0; $i < $count; $i++) {
947 # check if we have to increase the new value.
949 if ($newinnerloop1 >= $pattern->{every1}) {
951 $newlastvalue1 += $pattern->{add1};
953 # reset counter if needed.
954 $newlastvalue1 = $pattern->{setto1} if ($newlastvalue1 > $pattern->{whenmorethan1});
957 # check if we have to increase the new value.
959 if ($newinnerloop2 >= $pattern->{every2}) {
961 $newlastvalue2 += $pattern->{add2};
963 # reset counter if needed.
964 $newlastvalue2 = $pattern->{setto2} if ($newlastvalue2 > $pattern->{whenmorethan2});
967 # check if we have to increase the new value.
969 if ($newinnerloop3 >= $pattern->{every3}) {
971 $newlastvalue3 += $pattern->{add3};
973 # reset counter if needed.
974 $newlastvalue3 = $pattern->{setto3} if ($newlastvalue3 > $pattern->{whenmorethan3});
978 my $newlastvalue1string = _numeration( $newlastvalue1, $pattern->{numbering1}, $locale );
979 $calculated =~ s/\{X\}/$newlastvalue1string/g;
982 my $newlastvalue2string = _numeration( $newlastvalue2, $pattern->{numbering2}, $locale );
983 $calculated =~ s/\{Y\}/$newlastvalue2string/g;
986 my $newlastvalue3string = _numeration( $newlastvalue3, $pattern->{numbering3}, $locale );
987 $calculated =~ s/\{Z\}/$newlastvalue3string/g;
992 $newlastvalue1, $newlastvalue2, $newlastvalue3,
993 $newinnerloop1, $newinnerloop2, $newinnerloop3);
998 $calculated = GetSeq($subscription, $pattern)
999 $subscription is a hashref containing all the attributes of the table 'subscription'
1000 $pattern is a hashref containing all the attributes of the table 'subscription_numberpatterns'
1001 this function transforms {X},{Y},{Z} to 150,0,0 for example.
1003 the sequence in string format
1008 my ($subscription, $pattern) = @_;
1010 return unless ($subscription and $pattern);
1012 my $locale = $subscription->{locale};
1014 my $calculated = $pattern->{numberingmethod};
1016 my $newlastvalue1 = $subscription->{'lastvalue1'} || 0;
1017 $newlastvalue1 = _numeration($newlastvalue1, $pattern->{numbering1}, $locale) if ($pattern->{numbering1}); # reset counter if needed.
1018 $calculated =~ s/\{X\}/$newlastvalue1/g;
1020 my $newlastvalue2 = $subscription->{'lastvalue2'} || 0;
1021 $newlastvalue2 = _numeration($newlastvalue2, $pattern->{numbering2}, $locale) if ($pattern->{numbering2}); # reset counter if needed.
1022 $calculated =~ s/\{Y\}/$newlastvalue2/g;
1024 my $newlastvalue3 = $subscription->{'lastvalue3'} || 0;
1025 $newlastvalue3 = _numeration($newlastvalue3, $pattern->{numbering3}, $locale) if ($pattern->{numbering3}); # reset counter if needed.
1026 $calculated =~ s/\{Z\}/$newlastvalue3/g;
1030 =head2 GetExpirationDate
1032 $enddate = GetExpirationDate($subscriptionid, [$startdate])
1034 this function return the next expiration date for a subscription given on input args.
1037 the enddate or undef
1041 sub GetExpirationDate {
1042 my ( $subscriptionid, $startdate ) = @_;
1044 return unless ($subscriptionid);
1046 my $dbh = C4::Context->dbh;
1047 my $subscription = GetSubscription($subscriptionid);
1050 # we don't do the same test if the subscription is based on X numbers or on X weeks/months
1051 $enddate = $startdate || $subscription->{startdate};
1052 my @date = split( /-/, $enddate );
1053 return if ( scalar(@date) != 3 || not check_date(@date) );
1054 my $frequency = C4::Serials::Frequency::GetSubscriptionFrequency($subscription->{periodicity});
1055 if ( $frequency and $frequency->{unit} ) {
1058 if ( my $length = $subscription->{numberlength} ) {
1060 #calculate the date of the last issue.
1061 for ( my $i = 1 ; $i <= $length ; $i++ ) {
1062 $enddate = GetNextDate( $subscription, $enddate );
1064 } elsif ( $subscription->{monthlength} ) {
1065 if ( $$subscription{startdate} ) {
1066 my @enddate = Add_Delta_YM( $date[0], $date[1], $date[2], 0, $subscription->{monthlength} );
1067 $enddate = sprintf( "%04d-%02d-%02d", $enddate[0], $enddate[1], $enddate[2] );
1069 } elsif ( $subscription->{weeklength} ) {
1070 if ( $$subscription{startdate} ) {
1071 my @date = split( /-/, $subscription->{startdate} );
1072 my @enddate = Add_Delta_Days( $date[0], $date[1], $date[2], $subscription->{weeklength} * 7 );
1073 $enddate = sprintf( "%04d-%02d-%02d", $enddate[0], $enddate[1], $enddate[2] );
1076 $enddate = $subscription->{enddate};
1080 return $subscription->{enddate};
1084 =head2 CountSubscriptionFromBiblionumber
1086 $subscriptionsnumber = CountSubscriptionFromBiblionumber($biblionumber)
1087 this returns a count of the subscriptions for a given biblionumber
1089 the number of subscriptions
1093 sub CountSubscriptionFromBiblionumber {
1094 my ($biblionumber) = @_;
1096 return unless ($biblionumber);
1098 my $dbh = C4::Context->dbh;
1099 my $query = "SELECT count(*) FROM subscription WHERE biblionumber=?";
1100 my $sth = $dbh->prepare($query);
1101 $sth->execute($biblionumber);
1102 my $subscriptionsnumber = $sth->fetchrow;
1103 return $subscriptionsnumber;
1106 =head2 ModSubscriptionHistory
1108 ModSubscriptionHistory($subscriptionid,$histstartdate,$enddate,$recievedlist,$missinglist,$opacnote,$librariannote);
1110 this function modifies the history of a subscription. Put your new values on input arg.
1111 returns the number of rows affected
1115 sub ModSubscriptionHistory {
1116 my ( $subscriptionid, $histstartdate, $enddate, $receivedlist, $missinglist, $opacnote, $librariannote ) = @_;
1118 return unless ($subscriptionid);
1120 my $dbh = C4::Context->dbh;
1121 my $query = "UPDATE subscriptionhistory
1122 SET histstartdate=?,histenddate=?,recievedlist=?,missinglist=?,opacnote=?,librariannote=?
1123 WHERE subscriptionid=?
1125 my $sth = $dbh->prepare($query);
1126 $receivedlist =~ s/^; // if $receivedlist;
1127 $missinglist =~ s/^; // if $missinglist;
1128 $opacnote =~ s/^; // if $opacnote;
1129 $sth->execute( $histstartdate, $enddate, $receivedlist, $missinglist, $opacnote, $librariannote, $subscriptionid );
1133 # Update missinglist field, used by ModSerialStatus
1134 sub _update_missinglist {
1135 my $subscriptionid = shift;
1137 my $dbh = C4::Context->dbh;
1138 my @missingserials = GetSerials2($subscriptionid, "4,5");
1140 foreach (@missingserials) {
1141 if($_->{'status'} == 4) {
1142 $missinglist .= $_->{'serialseq'} . "; ";
1143 } elsif($_->{'status'} == 5) {
1144 $missinglist .= "not issued " . $_->{'serialseq'} . "; ";
1147 $missinglist =~ s/; $//;
1149 UPDATE subscriptionhistory
1151 WHERE subscriptionid = ?
1153 my $sth = $dbh->prepare($query);
1154 $sth->execute($missinglist, $subscriptionid);
1157 # Update recievedlist field, used by ModSerialStatus
1158 sub _update_receivedlist {
1159 my $subscriptionid = shift;
1161 my $dbh = C4::Context->dbh;
1162 my @receivedserials = GetSerials2($subscriptionid, "2");
1164 foreach (@receivedserials) {
1165 $receivedlist .= $_->{'serialseq'} . "; ";
1167 $receivedlist =~ s/; $//;
1169 UPDATE subscriptionhistory
1170 SET recievedlist = ?
1171 WHERE subscriptionid = ?
1173 my $sth = $dbh->prepare($query);
1174 $sth->execute($receivedlist, $subscriptionid);
1177 =head2 ModSerialStatus
1179 ModSerialStatus($serialid,$serialseq, $planneddate,$publisheddate,$status,$notes)
1181 This function modify the serial status. Serial status is a number.(eg 2 is "arrived")
1182 Note : if we change from "waited" to something else,then we will have to create a new "waited" entry
1186 sub ModSerialStatus {
1187 my ( $serialid, $serialseq, $planneddate, $publisheddate, $status, $notes ) = @_;
1189 return unless ($serialid);
1191 #It is a usual serial
1192 # 1st, get previous status :
1193 my $dbh = C4::Context->dbh;
1194 my $query = "SELECT serial.subscriptionid,serial.status,subscription.periodicity
1195 FROM serial, subscription
1196 WHERE serial.subscriptionid=subscription.subscriptionid
1198 my $sth = $dbh->prepare($query);
1199 $sth->execute($serialid);
1200 my ( $subscriptionid, $oldstatus, $periodicity ) = $sth->fetchrow;
1201 my $frequency = GetSubscriptionFrequency($periodicity);
1203 # change status & update subscriptionhistory
1205 if ( $status == 6 ) {
1206 DelIssue( { 'serialid' => $serialid, 'subscriptionid' => $subscriptionid, 'serialseq' => $serialseq } );
1209 unless ($frequency->{'unit'}) {
1210 if ( not $planneddate or $planneddate eq '0000-00-00' ) { $planneddate = C4::Dates->new()->output('iso') };
1211 if ( not $publisheddate or $publisheddate eq '0000-00-00' ) { $publisheddate = C4::Dates->new()->output('iso') };
1213 my $query = 'UPDATE serial SET serialseq=?,publisheddate=?,planneddate=?,status=?,notes=? WHERE serialid = ?';
1214 $sth = $dbh->prepare($query);
1215 $sth->execute( $serialseq, $publisheddate, $planneddate, $status, $notes, $serialid );
1216 $query = "SELECT * FROM subscription WHERE subscriptionid = ?";
1217 $sth = $dbh->prepare($query);
1218 $sth->execute($subscriptionid);
1219 my $val = $sth->fetchrow_hashref;
1220 unless ( $val->{manualhistory} ) {
1221 if ( $status == 2 || ($oldstatus == 2 && $status != 2) ) {
1222 _update_receivedlist($subscriptionid);
1224 if($status == 4 || $status == 5
1225 || ($oldstatus == 4 && $status != 4)
1226 || ($oldstatus == 5 && $status != 5)) {
1227 _update_missinglist($subscriptionid);
1232 # create new waited entry if needed (ie : was a "waited" and has changed)
1233 if ( $oldstatus == 1 && $status != 1 ) {
1234 my $subscription = GetSubscription($subscriptionid);
1235 my $pattern = C4::Serials::Numberpattern::GetSubscriptionNumberpattern($subscription->{numberpattern});
1239 $newserialseq, $newlastvalue1, $newlastvalue2, $newlastvalue3,
1240 $newinnerloop1, $newinnerloop2, $newinnerloop3
1242 = GetNextSeq( $subscription, $pattern, $publisheddate );
1244 # next date (calculated from actual date & frequency parameters)
1245 my $nextpublisheddate = GetNextDate($subscription, $publisheddate, 1);
1246 my $nextpubdate = $nextpublisheddate;
1247 NewIssue( $newserialseq, $subscriptionid, $subscription->{'biblionumber'}, 1, $nextpubdate, $nextpubdate );
1248 $query = "UPDATE subscription SET lastvalue1=?, lastvalue2=?, lastvalue3=?, innerloop1=?, innerloop2=?, innerloop3=?
1249 WHERE subscriptionid = ?";
1250 $sth = $dbh->prepare($query);
1251 $sth->execute( $newlastvalue1, $newlastvalue2, $newlastvalue3, $newinnerloop1, $newinnerloop2, $newinnerloop3, $subscriptionid );
1253 # check if an alert must be sent... (= a letter is defined & status became "arrived"
1254 if ( $subscription->{letter} && $status == 2 && $oldstatus != 2 ) {
1255 require C4::Letters;
1256 C4::Letters::SendAlerts( 'issue', $subscription->{subscriptionid}, $subscription->{letter} );
1263 =head2 GetNextExpected
1265 $nextexpected = GetNextExpected($subscriptionid)
1267 Get the planneddate for the current expected issue of the subscription.
1273 planneddate => ISO date
1278 sub GetNextExpected {
1279 my ($subscriptionid) = @_;
1281 my $dbh = C4::Context->dbh;
1285 WHERE subscriptionid = ?
1289 my $sth = $dbh->prepare($query);
1291 # Each subscription has only one 'expected' issue, with serial.status==1.
1292 $sth->execute( $subscriptionid, 1 );
1293 my $nextissue = $sth->fetchrow_hashref;
1294 if ( !$nextissue ) {
1298 WHERE subscriptionid = ?
1299 ORDER BY publisheddate DESC
1302 $sth = $dbh->prepare($query);
1303 $sth->execute($subscriptionid);
1304 $nextissue = $sth->fetchrow_hashref;
1306 foreach(qw/planneddate publisheddate/) {
1307 if ( !defined $nextissue->{$_} ) {
1308 # or should this default to 1st Jan ???
1309 $nextissue->{$_} = strftime( '%Y-%m-%d', localtime );
1311 $nextissue->{$_} = ($nextissue->{$_} ne '0000-00-00')
1319 =head2 ModNextExpected
1321 ModNextExpected($subscriptionid,$date)
1323 Update the planneddate for the current expected issue of the subscription.
1324 This will modify all future prediction results.
1326 C<$date> is an ISO date.
1332 sub ModNextExpected {
1333 my ( $subscriptionid, $date ) = @_;
1334 my $dbh = C4::Context->dbh;
1336 #FIXME: Would expect to only set planneddate, but we set both on new issue creation, so updating it here
1337 my $sth = $dbh->prepare('UPDATE serial SET planneddate=?,publisheddate=? WHERE subscriptionid=? AND status=?');
1339 # Each subscription has only one 'expected' issue, with serial.status==1.
1340 $sth->execute( $date, $date, $subscriptionid, 1 );
1345 =head2 GetSubscriptionIrregularities
1349 =item @irreg = &GetSubscriptionIrregularities($subscriptionid);
1350 get the list of irregularities for a subscription
1356 sub GetSubscriptionIrregularities {
1357 my $subscriptionid = shift;
1359 return unless $subscriptionid;
1361 my $dbh = C4::Context->dbh;
1365 WHERE subscriptionid = ?
1367 my $sth = $dbh->prepare($query);
1368 $sth->execute($subscriptionid);
1370 my ($result) = $sth->fetchrow_array;
1371 my @irreg = split /;/, $result;
1376 =head2 ModSubscription
1378 this function modifies a subscription. Put all new values on input args.
1379 returns the number of rows affected
1383 sub ModSubscription {
1385 $auser, $branchcode, $aqbooksellerid, $cost, $aqbudgetid, $startdate,
1386 $periodicity, $firstacquidate, $irregularity, $numberpattern, $locale,
1387 $numberlength, $weeklength, $monthlength, $lastvalue1, $innerloop1,
1388 $lastvalue2, $innerloop2, $lastvalue3, $innerloop3, $status,
1389 $biblionumber, $callnumber, $notes, $letter, $manualhistory,
1390 $internalnotes, $serialsadditems, $staffdisplaycount, $opacdisplaycount,
1391 $graceperiod, $location, $enddate, $subscriptionid, $skip_serialseq
1394 my $dbh = C4::Context->dbh;
1395 my $query = "UPDATE subscription
1396 SET librarian=?, branchcode=?, aqbooksellerid=?, cost=?, aqbudgetid=?,
1397 startdate=?, periodicity=?, firstacquidate=?, irregularity=?,
1398 numberpattern=?, locale=?, numberlength=?, weeklength=?, monthlength=?,
1399 lastvalue1=?, innerloop1=?, lastvalue2=?, innerloop2=?,
1400 lastvalue3=?, innerloop3=?, status=?, biblionumber=?,
1401 callnumber=?, notes=?, letter=?, manualhistory=?,
1402 internalnotes=?, serialsadditems=?, staffdisplaycount=?,
1403 opacdisplaycount=?, graceperiod=?, location = ?, enddate=?,
1405 WHERE subscriptionid = ?";
1407 my $sth = $dbh->prepare($query);
1409 $auser, $branchcode, $aqbooksellerid, $cost,
1410 $aqbudgetid, $startdate, $periodicity, $firstacquidate,
1411 $irregularity, $numberpattern, $locale, $numberlength,
1412 $weeklength, $monthlength, $lastvalue1, $innerloop1,
1413 $lastvalue2, $innerloop2, $lastvalue3, $innerloop3,
1414 $status, $biblionumber, $callnumber, $notes,
1415 $letter, ($manualhistory ? $manualhistory : 0),
1416 $internalnotes, $serialsadditems, $staffdisplaycount, $opacdisplaycount,
1417 $graceperiod, $location, $enddate, $skip_serialseq,
1420 my $rows = $sth->rows;
1422 logaction( "SERIAL", "MODIFY", $subscriptionid, "" ) if C4::Context->preference("SubscriptionLog");
1426 =head2 NewSubscription
1428 $subscriptionid = &NewSubscription($auser,branchcode,$aqbooksellerid,$cost,$aqbudgetid,$biblionumber,
1429 $startdate,$periodicity,$numberlength,$weeklength,$monthlength,
1430 $lastvalue1,$innerloop1,$lastvalue2,$innerloop2,$lastvalue3,$innerloop3,
1431 $status, $notes, $letter, $firstacquidate, $irregularity, $numberpattern,
1432 $callnumber, $hemisphere, $manualhistory, $internalnotes, $serialsadditems,
1433 $staffdisplaycount, $opacdisplaycount, $graceperiod, $location, $enddate, $skip_serialseq);
1435 Create a new subscription with value given on input args.
1438 the id of this new subscription
1442 sub NewSubscription {
1444 $auser, $branchcode, $aqbooksellerid, $cost, $aqbudgetid, $biblionumber,
1445 $startdate, $periodicity, $numberlength, $weeklength, $monthlength,
1446 $lastvalue1, $innerloop1, $lastvalue2, $innerloop2, $lastvalue3,
1447 $innerloop3, $status, $notes, $letter, $firstacquidate, $irregularity,
1448 $numberpattern, $locale, $callnumber, $manualhistory, $internalnotes,
1449 $serialsadditems, $staffdisplaycount, $opacdisplaycount, $graceperiod,
1450 $location, $enddate, $skip_serialseq
1452 my $dbh = C4::Context->dbh;
1454 #save subscription (insert into database)
1456 INSERT INTO subscription
1457 (librarian, branchcode, aqbooksellerid, cost, aqbudgetid,
1458 biblionumber, startdate, periodicity, numberlength, weeklength,
1459 monthlength, lastvalue1, innerloop1, lastvalue2, innerloop2,
1460 lastvalue3, innerloop3, status, notes, letter, firstacquidate,
1461 irregularity, numberpattern, locale, callnumber,
1462 manualhistory, internalnotes, serialsadditems, staffdisplaycount,
1463 opacdisplaycount, graceperiod, location, enddate, skip_serialseq)
1464 VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
1466 my $sth = $dbh->prepare($query);
1468 $auser, $branchcode, $aqbooksellerid, $cost, $aqbudgetid, $biblionumber,
1469 $startdate, $periodicity, $numberlength, $weeklength,
1470 $monthlength, $lastvalue1, $innerloop1, $lastvalue2, $innerloop2,
1471 $lastvalue3, $innerloop3, $status, $notes, $letter,
1472 $firstacquidate, $irregularity, $numberpattern, $locale, $callnumber,
1473 $manualhistory, $internalnotes, $serialsadditems, $staffdisplaycount,
1474 $opacdisplaycount, $graceperiod, $location, $enddate, $skip_serialseq
1477 my $subscriptionid = $dbh->{'mysql_insertid'};
1479 $enddate = GetExpirationDate( $subscriptionid, $startdate );
1483 WHERE subscriptionid=?
1485 $sth = $dbh->prepare($query);
1486 $sth->execute( $enddate, $subscriptionid );
1489 # then create the 1st expected number
1491 INSERT INTO subscriptionhistory
1492 (biblionumber, subscriptionid, histstartdate, opacnote, librariannote)
1495 $sth = $dbh->prepare($query);
1496 $sth->execute( $biblionumber, $subscriptionid, $startdate, $notes, $internalnotes );
1498 # reread subscription to get a hash (for calculation of the 1st issue number)
1499 my $subscription = GetSubscription($subscriptionid);
1500 my $pattern = C4::Serials::Numberpattern::GetSubscriptionNumberpattern($subscription->{numberpattern});
1502 # calculate issue number
1503 my $serialseq = GetSeq($subscription, $pattern);
1506 (serialseq,subscriptionid,biblionumber,status, planneddate, publisheddate)
1507 VALUES (?,?,?,?,?,?)
1509 $sth = $dbh->prepare($query);
1510 $sth->execute( "$serialseq", $subscriptionid, $biblionumber, 1, $firstacquidate, $firstacquidate );
1512 logaction( "SERIAL", "ADD", $subscriptionid, "" ) if C4::Context->preference("SubscriptionLog");
1514 #set serial flag on biblio if not already set.
1515 my $bib = GetBiblio($biblionumber);
1516 if ( $bib and !$bib->{'serial'} ) {
1517 my $record = GetMarcBiblio($biblionumber);
1518 my ( $tag, $subf ) = GetMarcFromKohaField( 'biblio.serial', $bib->{'frameworkcode'} );
1520 eval { $record->field($tag)->update( $subf => 1 ); };
1522 ModBiblio( $record, $biblionumber, $bib->{'frameworkcode'} );
1524 return $subscriptionid;
1527 =head2 ReNewSubscription
1529 ReNewSubscription($subscriptionid,$user,$startdate,$numberlength,$weeklength,$monthlength,$note)
1531 this function renew a subscription with values given on input args.
1535 sub ReNewSubscription {
1536 my ( $subscriptionid, $user, $startdate, $numberlength, $weeklength, $monthlength, $note ) = @_;
1537 my $dbh = C4::Context->dbh;
1538 my $subscription = GetSubscription($subscriptionid);
1542 LEFT JOIN biblioitems ON biblio.biblionumber=biblioitems.biblionumber
1543 WHERE biblio.biblionumber=?
1545 my $sth = $dbh->prepare($query);
1546 $sth->execute( $subscription->{biblionumber} );
1547 my $biblio = $sth->fetchrow_hashref;
1549 if ( C4::Context->preference("RenewSerialAddsSuggestion") ) {
1550 require C4::Suggestions;
1551 C4::Suggestions::NewSuggestion(
1552 { 'suggestedby' => $user,
1553 'title' => $subscription->{bibliotitle},
1554 'author' => $biblio->{author},
1555 'publishercode' => $biblio->{publishercode},
1556 'note' => $biblio->{note},
1557 'biblionumber' => $subscription->{biblionumber}
1562 # renew subscription
1565 SET startdate=?,numberlength=?,weeklength=?,monthlength=?,reneweddate=NOW()
1566 WHERE subscriptionid=?
1568 $sth = $dbh->prepare($query);
1569 $sth->execute( $startdate, $numberlength, $weeklength, $monthlength, $subscriptionid );
1570 my $enddate = GetExpirationDate($subscriptionid);
1571 $debug && warn "enddate :$enddate";
1575 WHERE subscriptionid=?
1577 $sth = $dbh->prepare($query);
1578 $sth->execute( $enddate, $subscriptionid );
1580 UPDATE subscriptionhistory
1582 WHERE subscriptionid=?
1584 $sth = $dbh->prepare($query);
1585 $sth->execute( $enddate, $subscriptionid );
1587 logaction( "SERIAL", "RENEW", $subscriptionid, "" ) if C4::Context->preference("SubscriptionLog");
1593 NewIssue($serialseq,$subscriptionid,$biblionumber,$status, $planneddate, $publisheddate, $notes)
1595 Create a new issue stored on the database.
1596 Note : we have to update the recievedlist and missinglist on subscriptionhistory for this subscription.
1597 returns the serial id
1602 my ( $serialseq, $subscriptionid, $biblionumber, $status, $planneddate, $publisheddate, $notes ) = @_;
1603 ### FIXME biblionumber CAN be provided by subscriptionid. So Do we STILL NEED IT ?
1605 return unless ($subscriptionid);
1607 my $dbh = C4::Context->dbh;
1610 (serialseq,subscriptionid,biblionumber,status,publisheddate,planneddate,notes)
1611 VALUES (?,?,?,?,?,?,?)
1613 my $sth = $dbh->prepare($query);
1614 $sth->execute( $serialseq, $subscriptionid, $biblionumber, $status, $publisheddate, $planneddate, $notes );
1615 my $serialid = $dbh->{'mysql_insertid'};
1617 SELECT missinglist,recievedlist
1618 FROM subscriptionhistory
1619 WHERE subscriptionid=?
1621 $sth = $dbh->prepare($query);
1622 $sth->execute($subscriptionid);
1623 my ( $missinglist, $recievedlist ) = $sth->fetchrow;
1625 if ( $status == 2 ) {
1626 ### TODO Add a feature that improves recognition and description.
1627 ### As such count (serialseq) i.e. : N18,2(N19),N20
1628 ### Would use substr and index But be careful to previous presence of ()
1629 $recievedlist .= "; $serialseq" unless (index($recievedlist,$serialseq)>0);
1631 if ( $status == 4 ) {
1632 $missinglist .= "; $serialseq" unless (index($missinglist,$serialseq)>0);
1635 UPDATE subscriptionhistory
1636 SET recievedlist=?, missinglist=?
1637 WHERE subscriptionid=?
1639 $sth = $dbh->prepare($query);
1640 $recievedlist =~ s/^; //;
1641 $missinglist =~ s/^; //;
1642 $sth->execute( $recievedlist, $missinglist, $subscriptionid );
1646 =head2 ItemizeSerials
1648 ItemizeSerials($serialid, $info);
1649 $info is a hashref containing barcode branch, itemcallnumber, status, location
1650 $serialid the serialid
1652 1 if the itemize is a succes.
1653 0 and @error otherwise. @error containts the list of errors found.
1657 sub ItemizeSerials {
1658 my ( $serialid, $info ) = @_;
1660 return unless ($serialid);
1662 my $now = POSIX::strftime( "%Y-%m-%d", localtime );
1664 my $dbh = C4::Context->dbh;
1670 my $sth = $dbh->prepare($query);
1671 $sth->execute($serialid);
1672 my $data = $sth->fetchrow_hashref;
1673 if ( C4::Context->preference("RoutingSerials") ) {
1675 # check for existing biblioitem relating to serial issue
1676 my ( $count, @results ) = GetBiblioItemByBiblioNumber( $data->{'biblionumber'} );
1678 for ( my $i = 0 ; $i < $count ; $i++ ) {
1679 if ( $results[$i]->{'volumeddesc'} eq $data->{'serialseq'} . ' (' . $data->{'planneddate'} . ')' ) {
1680 $bibitemno = $results[$i]->{'biblioitemnumber'};
1684 if ( $bibitemno == 0 ) {
1685 my $sth = $dbh->prepare( "SELECT * FROM biblioitems WHERE biblionumber = ? ORDER BY biblioitemnumber DESC" );
1686 $sth->execute( $data->{'biblionumber'} );
1687 my $biblioitem = $sth->fetchrow_hashref;
1688 $biblioitem->{'volumedate'} = $data->{planneddate};
1689 $biblioitem->{'volumeddesc'} = $data->{serialseq} . ' (' . format_date( $data->{'planneddate'} ) . ')';
1690 $biblioitem->{'dewey'} = $info->{itemcallnumber};
1694 my $fwk = GetFrameworkCode( $data->{'biblionumber'} );
1695 if ( $info->{barcode} ) {
1697 if ( is_barcode_in_use( $info->{barcode} ) ) {
1698 push @errors, 'barcode_not_unique';
1700 my $marcrecord = MARC::Record->new();
1701 my ( $tag, $subfield ) = GetMarcFromKohaField( "items.barcode", $fwk );
1702 my $newField = MARC::Field->new( "$tag", '', '', "$subfield" => $info->{barcode} );
1703 $marcrecord->insert_fields_ordered($newField);
1704 if ( $info->{branch} ) {
1705 my ( $tag, $subfield ) = GetMarcFromKohaField( "items.homebranch", $fwk );
1707 #warn "items.homebranch : $tag , $subfield";
1708 if ( $marcrecord->field($tag) ) {
1709 $marcrecord->field($tag)->add_subfields( "$subfield" => $info->{branch} );
1711 my $newField = MARC::Field->new( "$tag", '', '', "$subfield" => $info->{branch} );
1712 $marcrecord->insert_fields_ordered($newField);
1714 ( $tag, $subfield ) = GetMarcFromKohaField( "items.holdingbranch", $fwk );
1716 #warn "items.holdingbranch : $tag , $subfield";
1717 if ( $marcrecord->field($tag) ) {
1718 $marcrecord->field($tag)->add_subfields( "$subfield" => $info->{branch} );
1720 my $newField = MARC::Field->new( "$tag", '', '', "$subfield" => $info->{branch} );
1721 $marcrecord->insert_fields_ordered($newField);
1724 if ( $info->{itemcallnumber} ) {
1725 my ( $tag, $subfield ) = GetMarcFromKohaField( "items.itemcallnumber", $fwk );
1727 if ( $marcrecord->field($tag) ) {
1728 $marcrecord->field($tag)->add_subfields( "$subfield" => $info->{itemcallnumber} );
1730 my $newField = MARC::Field->new( "$tag", '', '', "$subfield" => $info->{itemcallnumber} );
1731 $marcrecord->insert_fields_ordered($newField);
1734 if ( $info->{notes} ) {
1735 my ( $tag, $subfield ) = GetMarcFromKohaField( "items.itemnotes", $fwk );
1737 if ( $marcrecord->field($tag) ) {
1738 $marcrecord->field($tag)->add_subfields( "$subfield" => $info->{notes} );
1740 my $newField = MARC::Field->new( "$tag", '', '', "$subfield" => $info->{notes} );
1741 $marcrecord->insert_fields_ordered($newField);
1744 if ( $info->{location} ) {
1745 my ( $tag, $subfield ) = GetMarcFromKohaField( "items.location", $fwk );
1747 if ( $marcrecord->field($tag) ) {
1748 $marcrecord->field($tag)->add_subfields( "$subfield" => $info->{location} );
1750 my $newField = MARC::Field->new( "$tag", '', '', "$subfield" => $info->{location} );
1751 $marcrecord->insert_fields_ordered($newField);
1754 if ( $info->{status} ) {
1755 my ( $tag, $subfield ) = GetMarcFromKohaField( "items.notforloan", $fwk );
1757 if ( $marcrecord->field($tag) ) {
1758 $marcrecord->field($tag)->add_subfields( "$subfield" => $info->{status} );
1760 my $newField = MARC::Field->new( "$tag", '', '', "$subfield" => $info->{status} );
1761 $marcrecord->insert_fields_ordered($newField);
1764 if ( C4::Context->preference("RoutingSerials") ) {
1765 my ( $tag, $subfield ) = GetMarcFromKohaField( "items.dateaccessioned", $fwk );
1766 if ( $marcrecord->field($tag) ) {
1767 $marcrecord->field($tag)->add_subfields( "$subfield" => $now );
1769 my $newField = MARC::Field->new( "$tag", '', '', "$subfield" => $now );
1770 $marcrecord->insert_fields_ordered($newField);
1774 C4::Items::AddItemFromMarc( $marcrecord, $data->{'biblionumber'} );
1777 return ( 0, @errors );
1781 =head2 HasSubscriptionStrictlyExpired
1783 1 or 0 = HasSubscriptionStrictlyExpired($subscriptionid)
1785 the subscription has stricly expired when today > the end subscription date
1788 1 if true, 0 if false, -1 if the expiration date is not set.
1792 sub HasSubscriptionStrictlyExpired {
1794 # Getting end of subscription date
1795 my ($subscriptionid) = @_;
1797 return unless ($subscriptionid);
1799 my $dbh = C4::Context->dbh;
1800 my $subscription = GetSubscription($subscriptionid);
1801 my $expirationdate = $subscription->{enddate} || GetExpirationDate($subscriptionid);
1803 # If the expiration date is set
1804 if ( $expirationdate != 0 ) {
1805 my ( $endyear, $endmonth, $endday ) = split( '-', $expirationdate );
1807 # Getting today's date
1808 my ( $nowyear, $nowmonth, $nowday ) = Today();
1810 # if today's date > expiration date, then the subscription has stricly expired
1811 if ( Delta_Days( $nowyear, $nowmonth, $nowday, $endyear, $endmonth, $endday ) < 0 ) {
1818 # There are some cases where the expiration date is not set
1819 # As we can't determine if the subscription has expired on a date-basis,
1825 =head2 HasSubscriptionExpired
1827 $has_expired = HasSubscriptionExpired($subscriptionid)
1829 the subscription has expired when the next issue to arrive is out of subscription limit.
1832 0 if the subscription has not expired
1833 1 if the subscription has expired
1834 2 if has subscription does not have a valid expiration date set
1838 sub HasSubscriptionExpired {
1839 my ($subscriptionid) = @_;
1841 return unless ($subscriptionid);
1843 my $dbh = C4::Context->dbh;
1844 my $subscription = GetSubscription($subscriptionid);
1845 my $frequency = C4::Serials::Frequency::GetSubscriptionFrequency($subscription->{periodicity});
1846 if ( $frequency and $frequency->{unit} ) {
1847 my $expirationdate = $subscription->{enddate} || GetExpirationDate($subscriptionid);
1848 if (!defined $expirationdate) {
1849 $expirationdate = q{};
1852 SELECT max(planneddate)
1854 WHERE subscriptionid=?
1856 my $sth = $dbh->prepare($query);
1857 $sth->execute($subscriptionid);
1858 my ($res) = $sth->fetchrow;
1859 if (!$res || $res=~m/^0000/) {
1862 my @res = split( /-/, $res );
1863 my @endofsubscriptiondate = split( /-/, $expirationdate );
1864 return 2 if ( scalar(@res) != 3 || scalar(@endofsubscriptiondate) != 3 || not check_date(@res) || not check_date(@endofsubscriptiondate) );
1866 if ( ( @endofsubscriptiondate && Delta_Days( $res[0], $res[1], $res[2], $endofsubscriptiondate[0], $endofsubscriptiondate[1], $endofsubscriptiondate[2] ) <= 0 )
1871 if ( $subscription->{'numberlength'} ) {
1872 my $countreceived = countissuesfrom( $subscriptionid, $subscription->{'startdate'} );
1873 return 1 if ( $countreceived > $subscription->{'numberlength'} );
1879 return 0; # Notice that you'll never get here.
1882 =head2 SetDistributedto
1884 SetDistributedto($distributedto,$subscriptionid);
1885 This function update the value of distributedto for a subscription given on input arg.
1889 sub SetDistributedto {
1890 my ( $distributedto, $subscriptionid ) = @_;
1891 my $dbh = C4::Context->dbh;
1895 WHERE subscriptionid=?
1897 my $sth = $dbh->prepare($query);
1898 $sth->execute( $distributedto, $subscriptionid );
1902 =head2 DelSubscription
1904 DelSubscription($subscriptionid)
1905 this function deletes subscription which has $subscriptionid as id.
1909 sub DelSubscription {
1910 my ($subscriptionid) = @_;
1911 my $dbh = C4::Context->dbh;
1912 $subscriptionid = $dbh->quote($subscriptionid);
1913 $dbh->do("DELETE FROM subscription WHERE subscriptionid=$subscriptionid");
1914 $dbh->do("DELETE FROM subscriptionhistory WHERE subscriptionid=$subscriptionid");
1915 $dbh->do("DELETE FROM serial WHERE subscriptionid=$subscriptionid");
1917 logaction( "SERIAL", "DELETE", $subscriptionid, "" ) if C4::Context->preference("SubscriptionLog");
1922 DelIssue($serialseq,$subscriptionid)
1923 this function deletes an issue which has $serialseq and $subscriptionid given on input arg.
1925 returns the number of rows affected
1930 my ($dataissue) = @_;
1931 my $dbh = C4::Context->dbh;
1932 ### TODO Add itemdeletion. Would need to get itemnumbers. Should be in a pref ?
1937 AND subscriptionid= ?
1939 my $mainsth = $dbh->prepare($query);
1940 $mainsth->execute( $dataissue->{'serialid'}, $dataissue->{'subscriptionid'} );
1942 #Delete element from subscription history
1943 $query = "SELECT * FROM subscription WHERE subscriptionid = ?";
1944 my $sth = $dbh->prepare($query);
1945 $sth->execute( $dataissue->{'subscriptionid'} );
1946 my $val = $sth->fetchrow_hashref;
1947 unless ( $val->{manualhistory} ) {
1949 SELECT * FROM subscriptionhistory
1950 WHERE subscriptionid= ?
1952 my $sth = $dbh->prepare($query);
1953 $sth->execute( $dataissue->{'subscriptionid'} );
1954 my $data = $sth->fetchrow_hashref;
1955 my $serialseq = $dataissue->{'serialseq'};
1956 $data->{'missinglist'} =~ s/\b$serialseq\b//;
1957 $data->{'recievedlist'} =~ s/\b$serialseq\b//;
1958 my $strsth = "UPDATE subscriptionhistory SET " . join( ",", map { join( "=", $_, $dbh->quote( $data->{$_} ) ) } keys %$data ) . " WHERE subscriptionid=?";
1959 $sth = $dbh->prepare($strsth);
1960 $sth->execute( $dataissue->{'subscriptionid'} );
1963 return $mainsth->rows;
1966 =head2 GetLateOrMissingIssues
1968 @issuelist = GetLateMissingIssues($supplierid,$serialid)
1970 this function selects missing issues on database - where serial.status = 4 or serial.status=3 or planneddate<now
1973 the issuelist as an array of hash refs. Each element of this array contains
1974 name,title,planneddate,serialseq,serial.subscriptionid from tables : subscription, serial & biblio
1978 sub GetLateOrMissingIssues {
1979 my ( $supplierid, $serialid, $order ) = @_;
1981 return unless ($supplierid);
1983 my $dbh = C4::Context->dbh;
1987 $byserial = "and serialid = " . $serialid;
1990 $order .= ", title";
1995 $sth = $dbh->prepare(
1997 serialid, aqbooksellerid, name,
1998 biblio.title, planneddate, serialseq,
1999 serial.status, serial.subscriptionid, claimdate,
2000 subscription.branchcode
2002 LEFT JOIN subscription ON serial.subscriptionid=subscription.subscriptionid
2003 LEFT JOIN biblio ON subscription.biblionumber=biblio.biblionumber
2004 LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id
2005 WHERE subscription.subscriptionid = serial.subscriptionid
2006 AND (serial.STATUS = 4 OR ((planneddate < now() AND serial.STATUS =1) OR serial.STATUS = 3 OR serial.STATUS = 7))
2007 AND subscription.aqbooksellerid=$supplierid
2012 $sth = $dbh->prepare(
2014 serialid, aqbooksellerid, name,
2015 biblio.title, planneddate, serialseq,
2016 serial.status, serial.subscriptionid, claimdate,
2017 subscription.branchcode
2019 LEFT JOIN subscription ON serial.subscriptionid=subscription.subscriptionid
2020 LEFT JOIN biblio ON subscription.biblionumber=biblio.biblionumber
2021 LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id
2022 WHERE subscription.subscriptionid = serial.subscriptionid
2023 AND (serial.STATUS = 4 OR ((planneddate < now() AND serial.STATUS =1) OR serial.STATUS = 3 OR serial.STATUS = 7))
2030 while ( my $line = $sth->fetchrow_hashref ) {
2032 if ($line->{planneddate} && $line->{planneddate} !~/^0+\-/) {
2033 $line->{planneddate} = format_date( $line->{planneddate} );
2035 if ($line->{claimdate} && $line->{claimdate} !~/^0+\-/) {
2036 $line->{claimdate} = format_date( $line->{claimdate} );
2038 $line->{"status".$line->{status}} = 1;
2039 push @issuelist, $line;
2044 =head2 removeMissingIssue
2046 removeMissingIssue($subscriptionid)
2048 this function removes an issue from being part of the missing string in
2049 subscriptionlist.missinglist column
2051 called when a missing issue is found from the serials-recieve.pl file
2055 sub removeMissingIssue {
2056 my ( $sequence, $subscriptionid ) = @_;
2058 return unless ($sequence and $subscriptionid);
2060 my $dbh = C4::Context->dbh;
2061 my $sth = $dbh->prepare("SELECT * FROM subscriptionhistory WHERE subscriptionid = ?");
2062 $sth->execute($subscriptionid);
2063 my $data = $sth->fetchrow_hashref;
2064 my $missinglist = $data->{'missinglist'};
2065 my $missinglistbefore = $missinglist;
2067 # warn $missinglist." before";
2068 $missinglist =~ s/($sequence)//;
2070 # warn $missinglist." after";
2071 if ( $missinglist ne $missinglistbefore ) {
2072 $missinglist =~ s/\|\s\|/\|/g;
2073 $missinglist =~ s/^\| //g;
2074 $missinglist =~ s/\|$//g;
2075 my $sth2 = $dbh->prepare(
2076 "UPDATE subscriptionhistory
2078 WHERE subscriptionid = ?"
2080 $sth2->execute( $missinglist, $subscriptionid );
2087 &updateClaim($serialid)
2089 this function updates the time when a claim is issued for late/missing items
2091 called from claims.pl file
2096 my ($serialid) = @_;
2097 my $dbh = C4::Context->dbh;
2098 my $sth = $dbh->prepare(
2099 "UPDATE serial SET claimdate = now()
2103 $sth->execute($serialid);
2107 =head2 getsupplierbyserialid
2109 $result = getsupplierbyserialid($serialid)
2111 this function is used to find the supplier id given a serial id
2114 hashref containing serialid, subscriptionid, and aqbooksellerid
2118 sub getsupplierbyserialid {
2119 my ($serialid) = @_;
2120 my $dbh = C4::Context->dbh;
2121 my $sth = $dbh->prepare(
2122 "SELECT serialid, serial.subscriptionid, aqbooksellerid
2124 LEFT JOIN subscription ON serial.subscriptionid = subscription.subscriptionid
2128 $sth->execute($serialid);
2129 my $line = $sth->fetchrow_hashref;
2130 my $result = $line->{'aqbooksellerid'};
2134 =head2 check_routing
2136 $result = &check_routing($subscriptionid)
2138 this function checks to see if a serial has a routing list and returns the count of routingid
2139 used to show either an 'add' or 'edit' link
2144 my ($subscriptionid) = @_;
2146 return unless ($subscriptionid);
2148 my $dbh = C4::Context->dbh;
2149 my $sth = $dbh->prepare(
2150 "SELECT count(routingid) routingids FROM subscription LEFT JOIN subscriptionroutinglist
2151 ON subscription.subscriptionid = subscriptionroutinglist.subscriptionid
2152 WHERE subscription.subscriptionid = ? ORDER BY ranking ASC
2155 $sth->execute($subscriptionid);
2156 my $line = $sth->fetchrow_hashref;
2157 my $result = $line->{'routingids'};
2161 =head2 addroutingmember
2163 addroutingmember($borrowernumber,$subscriptionid)
2165 this function takes a borrowernumber and subscriptionid and adds the member to the
2166 routing list for that serial subscription and gives them a rank on the list
2167 of either 1 or highest current rank + 1
2171 sub addroutingmember {
2172 my ( $borrowernumber, $subscriptionid ) = @_;
2174 return unless ($borrowernumber and $subscriptionid);
2177 my $dbh = C4::Context->dbh;
2178 my $sth = $dbh->prepare( "SELECT max(ranking) rank FROM subscriptionroutinglist WHERE subscriptionid = ?" );
2179 $sth->execute($subscriptionid);
2180 while ( my $line = $sth->fetchrow_hashref ) {
2181 if ( $line->{'rank'} > 0 ) {
2182 $rank = $line->{'rank'} + 1;
2187 $sth = $dbh->prepare( "INSERT INTO subscriptionroutinglist (subscriptionid,borrowernumber,ranking) VALUES (?,?,?)" );
2188 $sth->execute( $subscriptionid, $borrowernumber, $rank );
2191 =head2 reorder_members
2193 reorder_members($subscriptionid,$routingid,$rank)
2195 this function is used to reorder the routing list
2197 it takes the routingid of the member one wants to re-rank and the rank it is to move to
2198 - it gets all members on list puts their routingid's into an array
2199 - removes the one in the array that is $routingid
2200 - then reinjects $routingid at point indicated by $rank
2201 - then update the database with the routingids in the new order
2205 sub reorder_members {
2206 my ( $subscriptionid, $routingid, $rank ) = @_;
2207 my $dbh = C4::Context->dbh;
2208 my $sth = $dbh->prepare( "SELECT * FROM subscriptionroutinglist WHERE subscriptionid = ? ORDER BY ranking ASC" );
2209 $sth->execute($subscriptionid);
2211 while ( my $line = $sth->fetchrow_hashref ) {
2212 push( @result, $line->{'routingid'} );
2215 # To find the matching index
2217 my $key = -1; # to allow for 0 being a valid response
2218 for ( $i = 0 ; $i < @result ; $i++ ) {
2219 if ( $routingid == $result[$i] ) {
2220 $key = $i; # save the index
2225 # if index exists in array then move it to new position
2226 if ( $key > -1 && $rank > 0 ) {
2227 my $new_rank = $rank - 1; # $new_rank is what you want the new index to be in the array
2228 my $moving_item = splice( @result, $key, 1 );
2229 splice( @result, $new_rank, 0, $moving_item );
2231 for ( my $j = 0 ; $j < @result ; $j++ ) {
2232 my $sth = $dbh->prepare( "UPDATE subscriptionroutinglist SET ranking = '" . ( $j + 1 ) . "' WHERE routingid = '" . $result[$j] . "'" );
2238 =head2 delroutingmember
2240 delroutingmember($routingid,$subscriptionid)
2242 this function either deletes one member from routing list if $routingid exists otherwise
2243 deletes all members from the routing list
2247 sub delroutingmember {
2249 # if $routingid exists then deletes that row otherwise deletes all with $subscriptionid
2250 my ( $routingid, $subscriptionid ) = @_;
2251 my $dbh = C4::Context->dbh;
2253 my $sth = $dbh->prepare("DELETE FROM subscriptionroutinglist WHERE routingid = ?");
2254 $sth->execute($routingid);
2255 reorder_members( $subscriptionid, $routingid );
2257 my $sth = $dbh->prepare("DELETE FROM subscriptionroutinglist WHERE subscriptionid = ?");
2258 $sth->execute($subscriptionid);
2263 =head2 getroutinglist
2265 @routinglist = getroutinglist($subscriptionid)
2267 this gets the info from the subscriptionroutinglist for $subscriptionid
2270 the routinglist as an array. Each element of the array contains a hash_ref containing
2271 routingid - a unique id, borrowernumber, ranking, and biblionumber of subscription
2275 sub getroutinglist {
2276 my ($subscriptionid) = @_;
2277 my $dbh = C4::Context->dbh;
2278 my $sth = $dbh->prepare(
2279 'SELECT routingid, borrowernumber, ranking, biblionumber
2281 JOIN subscriptionroutinglist ON subscription.subscriptionid = subscriptionroutinglist.subscriptionid
2282 WHERE subscription.subscriptionid = ? ORDER BY ranking ASC'
2284 $sth->execute($subscriptionid);
2285 my $routinglist = $sth->fetchall_arrayref({});
2286 return @{$routinglist};
2289 =head2 countissuesfrom
2291 $result = countissuesfrom($subscriptionid,$startdate)
2293 Returns a count of serial rows matching the given subsctiptionid
2294 with published date greater than startdate
2298 sub countissuesfrom {
2299 my ( $subscriptionid, $startdate ) = @_;
2300 my $dbh = C4::Context->dbh;
2304 WHERE subscriptionid=?
2305 AND serial.publisheddate>?
2307 my $sth = $dbh->prepare($query);
2308 $sth->execute( $subscriptionid, $startdate );
2309 my ($countreceived) = $sth->fetchrow;
2310 return $countreceived;
2315 $result = CountIssues($subscriptionid)
2317 Returns a count of serial rows matching the given subsctiptionid
2322 my ($subscriptionid) = @_;
2323 my $dbh = C4::Context->dbh;
2327 WHERE subscriptionid=?
2329 my $sth = $dbh->prepare($query);
2330 $sth->execute($subscriptionid);
2331 my ($countreceived) = $sth->fetchrow;
2332 return $countreceived;
2337 $result = HasItems($subscriptionid)
2339 returns a count of items from serial matching the subscriptionid
2344 my ($subscriptionid) = @_;
2345 my $dbh = C4::Context->dbh;
2347 SELECT COUNT(serialitems.itemnumber)
2349 LEFT JOIN serialitems USING(serialid)
2350 WHERE subscriptionid=? AND serialitems.serialid IS NOT NULL
2352 my $sth=$dbh->prepare($query);
2353 $sth->execute($subscriptionid);
2354 my ($countitems)=$sth->fetchrow_array();
2358 =head2 abouttoexpire
2360 $result = abouttoexpire($subscriptionid)
2362 this function alerts you to the penultimate issue for a serial subscription
2364 returns 1 - if this is the penultimate issue
2370 my ($subscriptionid) = @_;
2371 my $dbh = C4::Context->dbh;
2372 my $subscription = GetSubscription($subscriptionid);
2373 my $per = $subscription->{'periodicity'};
2374 my $frequency = C4::Serials::Frequency::GetSubscriptionFrequency($per);
2375 if ($frequency and $frequency->{unit}){
2376 my $expirationdate = GetExpirationDate($subscriptionid);
2377 my ($res) = $dbh->selectrow_array('select max(planneddate) from serial where subscriptionid = ?', undef, $subscriptionid);
2378 my $nextdate = GetNextDate($subscription, $res);
2379 if(Date::Calc::Delta_Days(
2380 split( /-/, $nextdate ),
2381 split( /-/, $expirationdate )
2385 } elsif ($subscription->{numberlength}>0) {
2386 return (countissuesfrom($subscriptionid,$subscription->{'startdate'}) >=$subscription->{numberlength}-1);
2391 sub in_array { # used in next sub down
2392 my ( $val, @elements ) = @_;
2393 foreach my $elem (@elements) {
2394 if ( $val == $elem ) {
2401 =head2 GetSubscriptionsFromBorrower
2403 ($count,@routinglist) = GetSubscriptionsFromBorrower($borrowernumber)
2405 this gets the info from subscriptionroutinglist for each $subscriptionid
2408 a count of the serial subscription routing lists to which a patron belongs,
2409 with the titles of those serial subscriptions as an array. Each element of the array
2410 contains a hash_ref with subscriptionID and title of subscription.
2414 sub GetSubscriptionsFromBorrower {
2415 my ($borrowernumber) = @_;
2416 my $dbh = C4::Context->dbh;
2417 my $sth = $dbh->prepare(
2418 "SELECT subscription.subscriptionid, biblio.title
2420 JOIN biblio ON biblio.biblionumber = subscription.biblionumber
2421 JOIN subscriptionroutinglist USING (subscriptionid)
2422 WHERE subscriptionroutinglist.borrowernumber = ? ORDER BY title ASC
2425 $sth->execute($borrowernumber);
2428 while ( my $line = $sth->fetchrow_hashref ) {
2430 push( @routinglist, $line );
2432 return ( $count, @routinglist );
2436 =head2 GetFictiveIssueNumber
2438 $issueno = GetFictiveIssueNumber($subscription, $publishedate);
2440 Get the position of the issue published at $publisheddate, considering the
2441 first issue (at firstacquidate) is at position 1, the next is at position 2, etc...
2442 This issuenumber doesn't take into account irregularities, so, for instance, if the 3rd
2443 issue is declared as 'irregular' (will be skipped at receipt), the next issue number
2444 will be 4, not 3. It's why it is called 'fictive'. It is NOT a serial seq, and is not
2445 depending on how many rows are in serial table.
2446 The issue number calculation is based on subscription frequency, first acquisition
2447 date, and $publisheddate.
2451 sub GetFictiveIssueNumber {
2452 my ($subscription, $publisheddate) = @_;
2454 my $frequency = GetSubscriptionFrequency($subscription->{'periodicity'});
2455 my $unit = $frequency->{unit} ? lc $frequency->{'unit'} : undef;
2459 my ($year, $month, $day) = split /-/, $publisheddate;
2460 my ($fa_year, $fa_month, $fa_day) = split /-/, $subscription->{'firstacquidate'};
2464 if($unit eq 'day') {
2465 $delta = Delta_Days($fa_year, $fa_month, $fa_day, $year, $month, $day);
2466 } elsif($unit eq 'week') {
2467 ($wkno, $year) = Week_of_Year($year, $month, $day);
2468 my ($fa_wkno, $fa_yr) = Week_of_Year($fa_year, $fa_month, $fa_day);
2469 $delta = ($fa_yr == $year) ? ($wkno - $fa_wkno) : ( ($year-$fa_yr-1)*52 + (52-$fa_wkno+$wkno) );
2470 } elsif($unit eq 'month') {
2471 $delta = ($fa_year == $year)
2472 ? ($month - $fa_month)
2473 : ( ($year-$fa_year-1)*12 + (12-$fa_month+$month) );
2474 } elsif($unit eq 'year') {
2475 $delta = $year - $fa_year;
2477 if($frequency->{'unitsperissue'} == 1) {
2478 $issueno = $delta * $frequency->{'issuesperunit'} + $subscription->{'countissuesperunit'};
2480 # Assuming issuesperunit == 1
2481 $issueno = int( ($delta + $frequency->{'unitsperissue'}) / $frequency->{'unitsperissue'} );
2489 $resultdate = GetNextDate($publisheddate,$subscription)
2491 this function it takes the publisheddate and will return the next issue's date
2492 and will skip dates if there exists an irregularity.
2493 $publisheddate has to be an ISO date
2494 $subscription is a hashref containing at least 'periodicity', 'firstacquidate', 'irregularity', and 'countissuesperunit'
2495 $updatecount is a boolean value which, when set to true, update the 'countissuesperunit' in database
2496 - eg if periodicity is monthly and $publisheddate is 2007-02-10 but if March and April is to be
2497 skipped then the returned date will be 2007-05-10
2500 $resultdate - then next date in the sequence (ISO date)
2502 Return $publisheddate if subscription is irregular
2507 my ( $subscription, $publisheddate, $updatecount ) = @_;
2509 my $freqdata = GetSubscriptionFrequency($subscription->{'periodicity'});
2511 if ($freqdata->{'unit'}) {
2512 my ( $year, $month, $day ) = split /-/, $publisheddate;
2514 # Process an irregularity Hash
2515 # Suppose that irregularities are stored in a string with this structure
2516 # irreg1;irreg2;irreg3
2517 # where irregX is the number of issue which will not be received
2518 # (the first issue takes the number 1, the 2nd the number 2 and so on)
2519 my @irreg = split /;/, $subscription->{'irregularity'} ;
2521 foreach my $irregularity (@irreg) {
2522 $irregularities{$irregularity} = 1;
2525 # Get the 'fictive' next issue number
2526 # It is used to check if next issue is an irregular issue.
2527 my $issueno = GetFictiveIssueNumber($subscription, $publisheddate) + 1;
2529 # Then get the next date
2530 my $unit = lc $freqdata->{'unit'};
2531 if ($unit eq 'day') {
2532 while ($irregularities{$issueno}) {
2533 if ($subscription->{'countissuesperunit'} + 1 > $freqdata->{'issuesperunit'}){
2534 ($year,$month,$day) = Add_Delta_Days($year,$month, $day , $freqdata->{'unitsperissue'} );
2535 $subscription->{'countissuesperunit'} = 1;
2537 $subscription->{'countissuesperunit'}++;
2541 if ($subscription->{'countissuesperunit'} + 1 > $freqdata->{'issuesperunit'}){
2542 ($year,$month,$day) = Add_Delta_Days($year,$month, $day , $freqdata->{"unitsperissue"} );
2543 $subscription->{'countissuesperunit'} = 1;
2545 $subscription->{'countissuesperunit'}++;
2548 elsif ($unit eq 'week') {
2549 my ($wkno, $yr) = Week_of_Year($year, $month, $day);
2550 while ($irregularities{$issueno}) {
2551 if ($subscription->{'countissuesperunit'} + 1 > $freqdata->{'issuesperunit'}){
2552 $subscription->{'countissuesperunit'} = 1;
2553 $wkno += $freqdata->{"unitsperissue"};
2558 my $dow = Day_of_Week($year, $month, $day);
2559 ($year,$month,$day) = Monday_of_Week($wkno, $yr);
2560 if($freqdata->{'issuesperunit'} == 1) {
2561 ($year, $month, $day) = Add_Delta_Days($year, $month, $day, $dow - 1);
2564 $subscription->{'countissuesperunit'}++;
2568 if ($subscription->{'countissuesperunit'} + 1 > $freqdata->{'issuesperunit'}){
2569 $subscription->{'countissuesperunit'} = 1;
2570 $wkno += $freqdata->{"unitsperissue"};
2572 $wkno = $wkno % 52 ;
2575 my $dow = Day_of_Week($year, $month, $day);
2576 ($year,$month,$day) = Monday_of_Week($wkno, $yr);
2577 if($freqdata->{'issuesperunit'} == 1) {
2578 ($year, $month, $day) = Add_Delta_Days($year, $month, $day, $dow - 1);
2581 $subscription->{'countissuesperunit'}++;
2584 elsif ($unit eq 'month') {
2585 while ($irregularities{$issueno}) {
2586 if ($subscription->{'countissuesperunit'} + 1 > $freqdata->{'issuesperunit'}){
2587 $subscription->{'countissuesperunit'} = 1;
2588 ($year,$month,$day) = Add_Delta_YM($year,$month,$day, 0,$freqdata->{"unitsperissue"});
2589 unless($freqdata->{'issuesperunit'} == 1) {
2590 $day = 1; # Jumping to the first day of month, because we don't know what day is expected
2593 $subscription->{'countissuesperunit'}++;
2597 if ($subscription->{'countissuesperunit'} + 1 > $freqdata->{'issuesperunit'}){
2598 $subscription->{'countissuesperunit'} = 1;
2599 ($year,$month,$day) = Add_Delta_YM($year,$month,$day, 0,$freqdata->{"unitsperissue"});
2600 unless($freqdata->{'issuesperunit'} == 1) {
2601 $day = 1; # Jumping to the first day of month, because we don't know what day is expected
2604 $subscription->{'countissuesperunit'}++;
2607 elsif ($unit eq 'year') {
2608 while ($irregularities{$issueno}) {
2609 if ($subscription->{'countissuesperunit'} + 1 > $freqdata->{'issuesperunit'}){
2610 $subscription->{'countissuesperunit'} = 1;
2611 ($year,$month,$day) = Add_Delta_YM($year,$month,$day, $freqdata->{"unitsperissue"},0);
2612 unless($freqdata->{'issuesperunit'} == 1) {
2613 # Jumping to the first day of year, because we don't know what day is expected
2618 $subscription->{'countissuesperunit'}++;
2622 if ($subscription->{'countissuesperunit'} + 1 > $freqdata->{'issuesperunit'}){
2623 $subscription->{'countissuesperunit'} = 1;
2624 ($year,$month,$day) = Add_Delta_YM($year,$month,$day, $freqdata->{"unitsperissue"},0);
2625 unless($freqdata->{'issuesperunit'} == 1) {
2626 # Jumping to the first day of year, because we don't know what day is expected
2631 $subscription->{'countissuesperunit'}++;
2635 my $dbh = C4::Context->dbh;
2638 SET countissuesperunit = ?
2639 WHERE subscriptionid = ?
2641 my $sth = $dbh->prepare($query);
2642 $sth->execute($subscription->{'countissuesperunit'}, $subscription->{'subscriptionid'});
2644 return sprintf("%04d-%02d-%02d", $year, $month, $day);
2647 return $publisheddate;
2653 $string = &_numeration($value,$num_type,$locale);
2655 _numeration returns the string corresponding to $value in the num_type
2665 my ($value, $num_type, $locale) = @_;
2667 my $initlocale = setlocale(LC_TIME);
2668 if($locale and $locale ne $initlocale) {
2669 $locale = setlocale(LC_TIME, $locale);
2671 $locale ||= $initlocale;
2675 when (/^dayname$/) {
2676 $value = $value % 7;
2677 $string = POSIX::strftime("%A",0,0,0,0,0,0,$value);
2679 when (/^monthname$/) {
2680 $value = $value % 12;
2681 $string = POSIX::strftime("%B",0,0,0,1,$value,0,0,0,0);
2684 my $seasonlocale = ($locale)
2685 ? (substr $locale,0,2)
2689 [qw(Spring Summer Fall Winter)],
2691 [qw(Printemps Été Automne Hiver)],
2693 $value = $value % 4;
2694 $string = ($seasons{$seasonlocale})
2695 ? $seasons{$seasonlocale}->[$value]
2696 : $seasons{'en'}->[$value];
2702 if($locale ne $initlocale) {
2703 setlocale(LC_TIME, $initlocale);
2708 =head2 is_barcode_in_use
2710 Returns number of occurence of the barcode in the items table
2711 Can be used as a boolean test of whether the barcode has
2712 been deployed as yet
2716 sub is_barcode_in_use {
2717 my $barcode = shift;
2718 my $dbh = C4::Context->dbh;
2719 my $occurences = $dbh->selectall_arrayref(
2720 'SELECT itemnumber from items where barcode = ?',
2725 return @{$occurences};
2728 =head2 CloseSubscription
2729 Close a subscription given a subscriptionid
2731 sub CloseSubscription {
2732 my ( $subscriptionid ) = @_;
2733 return unless $subscriptionid;
2734 my $dbh = C4::Context->dbh;
2735 my $sth = $dbh->prepare( qq{
2738 WHERE subscriptionid = ?
2740 $sth->execute( $subscriptionid );
2742 # Set status = missing when status = stopped
2743 $sth = $dbh->prepare( qq{
2746 WHERE subscriptionid = ?
2749 $sth->execute( $subscriptionid );
2752 =head2 ReopenSubscription
2753 Reopen a subscription given a subscriptionid
2755 sub ReopenSubscription {
2756 my ( $subscriptionid ) = @_;
2757 return unless $subscriptionid;
2758 my $dbh = C4::Context->dbh;
2759 my $sth = $dbh->prepare( qq{
2762 WHERE subscriptionid = ?
2764 $sth->execute( $subscriptionid );
2766 # Set status = expected when status = stopped
2767 $sth = $dbh->prepare( qq{
2770 WHERE subscriptionid = ?
2773 $sth->execute( $subscriptionid );
2776 =head2 subscriptionCurrentlyOnOrder
2778 $bool = subscriptionCurrentlyOnOrder( $subscriptionid );
2780 Return 1 if subscription is currently on order else 0.
2784 sub subscriptionCurrentlyOnOrder {
2785 my ( $subscriptionid ) = @_;
2786 my $dbh = C4::Context->dbh;
2788 SELECT COUNT(*) FROM aqorders
2789 WHERE subscriptionid = ?
2790 AND datereceived IS NULL
2791 AND datecancellationprinted IS NULL
2793 my $sth = $dbh->prepare( $query );
2794 $sth->execute($subscriptionid);
2795 return $sth->fetchrow_array;
2798 =head2 can_edit_subscription
2800 $can = can_edit_subscription( $subscriptionid[, $userid] );
2802 Return 1 if the subscription is editable by the current logged user (or a given $userid), else 0.
2806 sub can_edit_subscription {
2807 my ( $subscription, $userid ) = @_;
2808 my $flags = C4::Context->userenv->{flags};
2809 $userid ||= C4::Context->userenv->{'id'};
2810 my $independent_branches = C4::Context->preference('IndependentBranches');
2811 return 1 unless $independent_branches;
2812 if( $flags % 2 == 1 # superlibrarian
2813 or C4::Auth::haspermission( $userid, {serials => 'superserials'}),
2814 or C4::Auth::haspermission( $userid, {serials => 'edit_subscription'}),
2815 or not defined $subscription->{branchcode}
2816 or $subscription->{branchcode} eq ''
2817 or $subscription->{branchcode} eq C4::Context->userenv->{'branch'}
2829 Koha Development Team <http://koha-community.org/>