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
9 # under the terms of the GNU General Public License as published by
10 # the Free Software Foundation; either version 3 of the License, or
11 # (at your option) any later version.
13 # Koha is distributed in the hope that it will be useful, but
14 # WITHOUT ANY WARRANTY; without even the implied warranty of
15 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
16 # GNU General Public License for more details.
18 # You should have received a copy of the GNU General Public License
19 # along with Koha; if not, see <http://www.gnu.org/licenses>.
23 use C4::Auth qw(haspermission);
25 use C4::Dates qw(format_date format_date_in_iso);
27 use Date::Calc qw(:all);
28 use POSIX qw(strftime);
30 use C4::Log; # logaction
32 use C4::Serials::Frequency;
33 use C4::Serials::Numberpattern;
35 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
43 MISSING_NEVER_RECIEVED => 41,
44 MISSING_SOLD_OUT => 42,
45 MISSING_DAMAGED => 43,
53 use constant MISSING_STATUSES => (
54 MISSING, MISSING_NEVER_RECIEVED,
55 MISSING_SOLD_OUT, MISSING_DAMAGED,
61 $VERSION = 3.07.00.049; # set version for version checking
65 &NewSubscription &ModSubscription &DelSubscription
66 &GetSubscription &CountSubscriptionFromBiblionumber &GetSubscriptionsFromBiblionumber
68 &GetFullSubscriptionsFromBiblionumber &GetFullSubscription &ModSubscriptionHistory
69 &HasSubscriptionStrictlyExpired &HasSubscriptionExpired &GetExpirationDate &abouttoexpire
70 &GetSubscriptionHistoryFromSubscriptionId
72 &GetNextSeq &GetSeq &NewIssue &ItemizeSerials &GetSerials
73 &GetLatestSerials &ModSerialStatus &GetNextDate &GetSerials2
74 &ReNewSubscription &GetLateOrMissingIssues
75 &GetSerialInformation &AddItem2Serial
76 &PrepareSerialsData &GetNextExpected &ModNextExpected
78 &UpdateClaimdateIssues
79 &GetSuppliersWithLateIssues &getsupplierbyserialid
80 &GetDistributedTo &SetDistributedTo
81 &getroutinglist &delroutingmember &addroutingmember
83 &check_routing &updateClaim &removeMissingIssue
86 &GetSubscriptionsFromBorrower
87 &subscriptionCurrentlyOnOrder
94 C4::Serials - Serials Module Functions
102 Functions for handling subscriptions, claims routing etc.
107 =head2 GetSuppliersWithLateIssues
109 $supplierlist = GetSuppliersWithLateIssues()
111 this function get all suppliers with late issues.
114 an array_ref of suppliers each entry is a hash_ref containing id and name
115 the array is in name order
119 sub GetSuppliersWithLateIssues {
120 my $dbh = C4::Context->dbh;
121 my $statuses = join(',', ( LATE, MISSING_STATUSES, CLAIMED ) );
123 SELECT DISTINCT id, name
125 LEFT JOIN serial ON serial.subscriptionid=subscription.subscriptionid
126 LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id
129 (planneddate < now() AND serial.status=1)
130 OR serial.STATUS IN ( $statuses )
132 AND subscription.closed = 0
134 return $dbh->selectall_arrayref($query, { Slice => {} });
137 =head2 GetSubscriptionHistoryFromSubscriptionId
139 $history = GetSubscriptionHistoryFromSubscriptionId($subscriptionid);
141 This function returns the subscription history as a hashref
145 sub GetSubscriptionHistoryFromSubscriptionId {
146 my ($subscriptionid) = @_;
148 return unless $subscriptionid;
150 my $dbh = C4::Context->dbh;
153 FROM subscriptionhistory
154 WHERE subscriptionid = ?
156 my $sth = $dbh->prepare($query);
157 $sth->execute($subscriptionid);
158 my $results = $sth->fetchrow_hashref;
164 =head2 GetSerialStatusFromSerialId
166 $sth = GetSerialStatusFromSerialId();
167 this function returns a statement handle
168 After this function, don't forget to execute it by using $sth->execute($serialid)
170 $sth = $dbh->prepare($query).
174 sub GetSerialStatusFromSerialId {
175 my $dbh = C4::Context->dbh;
181 return $dbh->prepare($query);
184 =head2 GetSerialInformation
187 $data = GetSerialInformation($serialid);
188 returns a hash_ref containing :
189 items : items marcrecord (can be an array)
191 subscription table field
192 + information about subscription expiration
196 sub GetSerialInformation {
198 my $dbh = C4::Context->dbh;
200 SELECT serial.*, serial.notes as sernotes, serial.status as serstatus,subscription.*,subscription.subscriptionid as subsid
201 FROM serial LEFT JOIN subscription ON subscription.subscriptionid=serial.subscriptionid
204 my $rq = $dbh->prepare($query);
205 $rq->execute($serialid);
206 my $data = $rq->fetchrow_hashref;
208 # create item information if we have serialsadditems for this subscription
209 if ( $data->{'serialsadditems'} ) {
210 my $queryitem = $dbh->prepare("SELECT itemnumber from serialitems where serialid=?");
211 $queryitem->execute($serialid);
212 my $itemnumbers = $queryitem->fetchall_arrayref( [0] );
214 if ( scalar(@$itemnumbers) > 0 ) {
215 foreach my $itemnum (@$itemnumbers) {
217 #It is ASSUMED that GetMarcItem ALWAYS WORK...
218 #Maybe GetMarcItem should return values on failure
219 $debug and warn "itemnumber :$itemnum->[0], bibnum :" . $data->{'biblionumber'};
220 my $itemprocessed = C4::Items::PrepareItemrecordDisplay( $data->{'biblionumber'}, $itemnum->[0], $data );
221 $itemprocessed->{'itemnumber'} = $itemnum->[0];
222 $itemprocessed->{'itemid'} = $itemnum->[0];
223 $itemprocessed->{'serialid'} = $serialid;
224 $itemprocessed->{'biblionumber'} = $data->{'biblionumber'};
225 push @{ $data->{'items'} }, $itemprocessed;
228 my $itemprocessed = C4::Items::PrepareItemrecordDisplay( $data->{'biblionumber'}, '', $data );
229 $itemprocessed->{'itemid'} = "N$serialid";
230 $itemprocessed->{'serialid'} = $serialid;
231 $itemprocessed->{'biblionumber'} = $data->{'biblionumber'};
232 $itemprocessed->{'countitems'} = 0;
233 push @{ $data->{'items'} }, $itemprocessed;
236 $data->{ "status" . $data->{'serstatus'} } = 1;
237 $data->{'subscriptionexpired'} = HasSubscriptionExpired( $data->{'subscriptionid'} ) && $data->{'status'} == 1;
238 $data->{'abouttoexpire'} = abouttoexpire( $data->{'subscriptionid'} );
239 $data->{cannotedit} = not can_edit_subscription( $data );
243 =head2 AddItem2Serial
245 $rows = AddItem2Serial($serialid,$itemnumber);
246 Adds an itemnumber to Serial record
247 returns the number of rows affected
252 my ( $serialid, $itemnumber ) = @_;
254 return unless ($serialid and $itemnumber);
256 my $dbh = C4::Context->dbh;
257 my $rq = $dbh->prepare("INSERT INTO `serialitems` SET serialid=? , itemnumber=?");
258 $rq->execute( $serialid, $itemnumber );
262 =head2 UpdateClaimdateIssues
264 UpdateClaimdateIssues($serialids,[$date]);
266 Update Claimdate for issues in @$serialids list with date $date
271 sub UpdateClaimdateIssues {
272 my ( $serialids, $date ) = @_;
274 return unless ($serialids);
276 my $dbh = C4::Context->dbh;
277 $date = strftime( "%Y-%m-%d", localtime ) unless ($date);
282 claims_count = claims_count + 1
283 WHERE serialid in (" . join( ",", map { '?' } @$serialids ) . ")
285 my $rq = $dbh->prepare($query);
286 $rq->execute($date, @$serialids);
290 =head2 GetSubscription
292 $subs = GetSubscription($subscriptionid)
293 this function returns the subscription which has $subscriptionid as id.
295 a hashref. This hash containts
296 subscription, subscriptionhistory, aqbooksellers.name, biblio.title
300 sub GetSubscription {
301 my ($subscriptionid) = @_;
302 my $dbh = C4::Context->dbh;
304 SELECT subscription.*,
305 subscriptionhistory.*,
306 aqbooksellers.name AS aqbooksellername,
307 biblio.title AS bibliotitle,
308 subscription.biblionumber as bibnum
310 LEFT JOIN subscriptionhistory ON subscription.subscriptionid=subscriptionhistory.subscriptionid
311 LEFT JOIN aqbooksellers ON subscription.aqbooksellerid=aqbooksellers.id
312 LEFT JOIN biblio ON biblio.biblionumber=subscription.biblionumber
313 WHERE subscription.subscriptionid = ?
316 $debug and warn "query : $query\nsubsid :$subscriptionid";
317 my $sth = $dbh->prepare($query);
318 $sth->execute($subscriptionid);
319 my $subscription = $sth->fetchrow_hashref;
320 $subscription->{cannotedit} = not can_edit_subscription( $subscription );
321 return $subscription;
324 =head2 GetFullSubscription
326 $array_ref = GetFullSubscription($subscriptionid)
327 this function reads the serial table.
331 sub GetFullSubscription {
332 my ($subscriptionid) = @_;
334 return unless ($subscriptionid);
336 my $dbh = C4::Context->dbh;
338 SELECT serial.serialid,
341 serial.publisheddate,
343 serial.notes as notes,
344 year(IF(serial.publisheddate="00-00-0000",serial.planneddate,serial.publisheddate)) as year,
345 aqbooksellers.name as aqbooksellername,
346 biblio.title as bibliotitle,
347 subscription.branchcode AS branchcode,
348 subscription.subscriptionid AS subscriptionid
350 LEFT JOIN subscription ON
351 (serial.subscriptionid=subscription.subscriptionid )
352 LEFT JOIN aqbooksellers on subscription.aqbooksellerid=aqbooksellers.id
353 LEFT JOIN biblio on biblio.biblionumber=subscription.biblionumber
354 WHERE serial.subscriptionid = ?
356 IF(serial.publisheddate="00-00-0000",serial.planneddate,serial.publisheddate) DESC,
357 serial.subscriptionid
359 $debug and warn "GetFullSubscription query: $query";
360 my $sth = $dbh->prepare($query);
361 $sth->execute($subscriptionid);
362 my $subscriptions = $sth->fetchall_arrayref( {} );
363 for my $subscription ( @$subscriptions ) {
364 $subscription->{cannotedit} = not can_edit_subscription( $subscription );
366 return $subscriptions;
369 =head2 PrepareSerialsData
371 $array_ref = PrepareSerialsData($serialinfomation)
372 where serialinformation is a hashref array
376 sub PrepareSerialsData {
379 return unless ($lines);
385 my $aqbooksellername;
389 my $previousnote = "";
391 foreach my $subs (@{$lines}) {
392 for my $datefield ( qw(publisheddate planneddate) ) {
393 # handle 0000-00-00 dates
394 if (defined $subs->{$datefield} and $subs->{$datefield} =~ m/^00/) {
395 $subs->{$datefield} = undef;
398 $subs->{ "status" . $subs->{'status'} } = 1;
399 if ( grep { $_ == $subs->{status} } ( EXPECTED, LATE, MISSING_STATUSES, CLAIMED ) ) {
400 $subs->{"checked"} = 1;
403 if ( $subs->{'year'} && $subs->{'year'} ne "" ) {
404 $year = $subs->{'year'};
408 if ( $tmpresults{$year} ) {
409 push @{ $tmpresults{$year}->{'serials'} }, $subs;
411 $tmpresults{$year} = {
413 'aqbooksellername' => $subs->{'aqbooksellername'},
414 'bibliotitle' => $subs->{'bibliotitle'},
415 'serials' => [$subs],
420 foreach my $key ( sort { $b cmp $a } keys %tmpresults ) {
421 push @res, $tmpresults{$key};
426 =head2 GetSubscriptionsFromBiblionumber
428 $array_ref = GetSubscriptionsFromBiblionumber($biblionumber)
429 this function get the subscription list. it reads the subscription table.
431 reference to an array of subscriptions which have the biblionumber given on input arg.
432 each element of this array is a hashref containing
433 startdate, histstartdate,opacnote,missinglist,recievedlist,periodicity,status & enddate
437 sub GetSubscriptionsFromBiblionumber {
438 my ($biblionumber) = @_;
440 return unless ($biblionumber);
442 my $dbh = C4::Context->dbh;
444 SELECT subscription.*,
446 subscriptionhistory.*,
447 aqbooksellers.name AS aqbooksellername,
448 biblio.title AS bibliotitle
450 LEFT JOIN subscriptionhistory ON subscription.subscriptionid=subscriptionhistory.subscriptionid
451 LEFT JOIN aqbooksellers ON subscription.aqbooksellerid=aqbooksellers.id
452 LEFT JOIN biblio ON biblio.biblionumber=subscription.biblionumber
453 LEFT JOIN branches ON branches.branchcode=subscription.branchcode
454 WHERE subscription.biblionumber = ?
456 my $sth = $dbh->prepare($query);
457 $sth->execute($biblionumber);
459 while ( my $subs = $sth->fetchrow_hashref ) {
460 $subs->{startdate} = format_date( $subs->{startdate} );
461 $subs->{histstartdate} = format_date( $subs->{histstartdate} );
462 $subs->{histenddate} = format_date( $subs->{histenddate} );
463 $subs->{opacnote} =~ s/\n/\<br\/\>/g;
464 $subs->{missinglist} =~ s/\n/\<br\/\>/g;
465 $subs->{recievedlist} =~ s/\n/\<br\/\>/g;
466 $subs->{ "periodicity" . $subs->{periodicity} } = 1;
467 $subs->{ "numberpattern" . $subs->{numberpattern} } = 1;
468 $subs->{ "status" . $subs->{'status'} } = 1;
470 if ( $subs->{enddate} eq '0000-00-00' ) {
471 $subs->{enddate} = '';
473 $subs->{enddate} = format_date( $subs->{enddate} );
475 $subs->{'abouttoexpire'} = abouttoexpire( $subs->{'subscriptionid'} );
476 $subs->{'subscriptionexpired'} = HasSubscriptionExpired( $subs->{'subscriptionid'} );
477 $subs->{cannotedit} = not can_edit_subscription( $subs );
483 =head2 GetFullSubscriptionsFromBiblionumber
485 $array_ref = GetFullSubscriptionsFromBiblionumber($biblionumber)
486 this function reads the serial table.
490 sub GetFullSubscriptionsFromBiblionumber {
491 my ($biblionumber) = @_;
492 my $dbh = C4::Context->dbh;
494 SELECT serial.serialid,
497 serial.publisheddate,
499 serial.notes as notes,
500 year(IF(serial.publisheddate="00-00-0000",serial.planneddate,serial.publisheddate)) as year,
501 biblio.title as bibliotitle,
502 subscription.branchcode AS branchcode,
503 subscription.subscriptionid AS subscriptionid
505 LEFT JOIN subscription ON
506 (serial.subscriptionid=subscription.subscriptionid)
507 LEFT JOIN aqbooksellers on subscription.aqbooksellerid=aqbooksellers.id
508 LEFT JOIN biblio on biblio.biblionumber=subscription.biblionumber
509 WHERE subscription.biblionumber = ?
511 IF(serial.publisheddate="00-00-0000",serial.planneddate,serial.publisheddate) DESC,
512 serial.subscriptionid
514 my $sth = $dbh->prepare($query);
515 $sth->execute($biblionumber);
516 my $subscriptions = $sth->fetchall_arrayref( {} );
517 for my $subscription ( @$subscriptions ) {
518 $subscription->{cannotedit} = not can_edit_subscription( $subscription );
520 return $subscriptions;
523 =head2 SearchSubscriptions
525 @results = SearchSubscriptions($args);
527 This function returns a list of hashrefs, one for each subscription
528 that meets the conditions specified by the $args hashref.
530 The valid search fields are:
544 The expiration_date search field is special; it specifies the maximum
545 subscription expiration date.
549 sub SearchSubscriptions {
554 subscription.notes AS publicnotes,
555 subscriptionhistory.*,
557 biblio.notes AS biblionotes,
563 LEFT JOIN subscriptionhistory USING(subscriptionid)
564 LEFT JOIN biblio ON biblio.biblionumber = subscription.biblionumber
565 LEFT JOIN biblioitems ON biblioitems.biblionumber = subscription.biblionumber
566 LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id
570 if( $args->{biblionumber} ) {
571 push @where_strs, "biblio.biblionumber = ?";
572 push @where_args, $args->{biblionumber};
574 if( $args->{title} ){
575 my @words = split / /, $args->{title};
577 foreach my $word (@words) {
578 push @strs, "biblio.title LIKE ?";
579 push @args, "%$word%";
582 push @where_strs, '(' . join (' AND ', @strs) . ')';
583 push @where_args, @args;
587 push @where_strs, "biblioitems.issn LIKE ?";
588 push @where_args, "%$args->{issn}%";
591 push @where_strs, "biblioitems.ean LIKE ?";
592 push @where_args, "%$args->{ean}%";
594 if ( $args->{callnumber} ) {
595 push @where_strs, "subscription.callnumber LIKE ?";
596 push @where_args, "%$args->{callnumber}%";
598 if( $args->{publisher} ){
599 push @where_strs, "biblioitems.publishercode LIKE ?";
600 push @where_args, "%$args->{publisher}%";
602 if( $args->{bookseller} ){
603 push @where_strs, "aqbooksellers.name LIKE ?";
604 push @where_args, "%$args->{bookseller}%";
606 if( $args->{branch} ){
607 push @where_strs, "subscription.branchcode = ?";
608 push @where_args, "$args->{branch}";
610 if ( $args->{location} ) {
611 push @where_strs, "subscription.location = ?";
612 push @where_args, "$args->{location}";
614 if ( $args->{expiration_date} ) {
615 push @where_strs, "subscription.enddate <= ?";
616 push @where_args, "$args->{expiration_date}";
618 if( defined $args->{closed} ){
619 push @where_strs, "subscription.closed = ?";
620 push @where_args, "$args->{closed}";
623 $query .= " WHERE " . join(" AND ", @where_strs);
626 $query .= " ORDER BY " . $args->{orderby} if $args->{orderby};
628 my $dbh = C4::Context->dbh;
629 my $sth = $dbh->prepare($query);
630 $sth->execute(@where_args);
631 my $results = $sth->fetchall_arrayref( {} );
634 for my $subscription ( @$results ) {
635 $subscription->{cannotedit} = not can_edit_subscription( $subscription );
636 $subscription->{cannotdisplay} = not can_show_subscription( $subscription );
645 ($totalissues,@serials) = GetSerials($subscriptionid);
646 this function gets every serial not arrived for a given subscription
647 as well as the number of issues registered in the database (all types)
648 this number is used to see if a subscription can be deleted (=it must have only 1 issue)
650 FIXME: We should return \@serials.
655 my ( $subscriptionid, $count ) = @_;
657 return unless $subscriptionid;
659 my $dbh = C4::Context->dbh;
661 # status = 2 is "arrived"
663 $count = 5 unless ($count);
665 my $statuses = join( ',', ( ARRIVED, MISSING_STATUSES, NOT_ISSUED ) );
666 my $query = "SELECT serialid,serialseq, status, publisheddate, planneddate,notes, routingnotes
668 WHERE subscriptionid = ? AND status NOT IN ( $statuses )
669 ORDER BY IF(publisheddate<>'0000-00-00',publisheddate,planneddate) DESC";
670 my $sth = $dbh->prepare($query);
671 $sth->execute($subscriptionid);
673 while ( my $line = $sth->fetchrow_hashref ) {
674 $line->{ "status" . $line->{status} } = 1; # fills a "statusX" value, used for template status select list
675 for my $datefield ( qw( planneddate publisheddate) ) {
676 if ($line->{$datefield} && $line->{$datefield}!~m/^00/) {
677 $line->{$datefield} = format_date( $line->{$datefield});
679 $line->{$datefield} = q{};
682 push @serials, $line;
685 # OK, now add the last 5 issues arrives/missing
686 $query = "SELECT serialid,serialseq, status, planneddate, publisheddate,notes, routingnotes
688 WHERE subscriptionid = ?
689 AND status IN ( $statuses )
690 ORDER BY IF(publisheddate<>'0000-00-00',publisheddate,planneddate) DESC
692 $sth = $dbh->prepare($query);
693 $sth->execute($subscriptionid);
694 while ( ( my $line = $sth->fetchrow_hashref ) && $counter < $count ) {
696 $line->{ "status" . $line->{status} } = 1; # fills a "statusX" value, used for template status select list
697 for my $datefield ( qw( planneddate publisheddate) ) {
698 if ($line->{$datefield} && $line->{$datefield}!~m/^00/) {
699 $line->{$datefield} = format_date( $line->{$datefield});
701 $line->{$datefield} = q{};
705 push @serials, $line;
708 $query = "SELECT count(*) FROM serial WHERE subscriptionid=?";
709 $sth = $dbh->prepare($query);
710 $sth->execute($subscriptionid);
711 my ($totalissues) = $sth->fetchrow;
712 return ( $totalissues, @serials );
717 @serials = GetSerials2($subscriptionid,$status);
718 this function returns every serial waited for a given subscription
719 as well as the number of issues registered in the database (all types)
720 this number is used to see if a subscription can be deleted (=it must have only 1 issue)
725 my ( $subscription, $status ) = @_;
727 return unless ($subscription and $status);
729 my $dbh = C4::Context->dbh;
731 SELECT serialid,serialseq, status, planneddate, publisheddate,notes, routingnotes
733 WHERE subscriptionid=$subscription AND status IN ($status)
734 ORDER BY publisheddate,serialid DESC
736 $debug and warn "GetSerials2 query: $query";
737 my $sth = $dbh->prepare($query);
741 while ( my $line = $sth->fetchrow_hashref ) {
742 $line->{ "status" . $line->{status} } = 1; # fills a "statusX" value, used for template status select list
743 # Format dates for display
744 for my $datefield ( qw( planneddate publisheddate ) ) {
745 if (!defined($line->{$datefield}) || $line->{$datefield} =~m/^00/) {
746 $line->{$datefield} = q{};
749 $line->{$datefield} = format_date( $line->{$datefield} );
752 push @serials, $line;
757 =head2 GetLatestSerials
759 \@serials = GetLatestSerials($subscriptionid,$limit)
760 get the $limit's latest serials arrived or missing for a given subscription
762 a ref to an array which contains all of the latest serials stored into a hash.
766 sub GetLatestSerials {
767 my ( $subscriptionid, $limit ) = @_;
769 return unless ($subscriptionid and $limit);
771 my $dbh = C4::Context->dbh;
773 # status = 2 is "arrived"
774 my $strsth = "SELECT serialid,serialseq, status, planneddate, publisheddate, notes
776 WHERE subscriptionid = ?
777 AND status IN (2, 4, 41, 42, 43, 44)
778 ORDER BY publisheddate DESC LIMIT 0,$limit
780 my $sth = $dbh->prepare($strsth);
781 $sth->execute($subscriptionid);
783 while ( my $line = $sth->fetchrow_hashref ) {
784 $line->{ "status" . $line->{status} } = 1; # fills a "statusX" value, used for template status select list
785 $line->{"planneddate"} = format_date( $line->{"planneddate"} );
786 $line->{"publisheddate"} = format_date( $line->{"publisheddate"} );
787 push @serials, $line;
793 =head2 GetDistributedTo
795 $distributedto=GetDistributedTo($subscriptionid)
796 This function returns the field distributedto for the subscription matching subscriptionid
800 sub GetDistributedTo {
801 my $dbh = C4::Context->dbh;
803 my ($subscriptionid) = @_;
805 return unless ($subscriptionid);
807 my $query = "SELECT distributedto FROM subscription WHERE subscriptionid=?";
808 my $sth = $dbh->prepare($query);
809 $sth->execute($subscriptionid);
810 return ($distributedto) = $sth->fetchrow;
816 $nextseq, $newlastvalue1, $newlastvalue2, $newlastvalue3,
817 $newinnerloop1, $newinnerloop2, $newinnerloop3
818 ) = GetNextSeq( $subscription, $pattern, $planneddate );
820 $subscription is a hashref containing all the attributes of the table
822 $pattern is a hashref containing all the attributes of the table
823 'subscription_numberpatterns'.
824 $planneddate is a C4::Dates object.
825 This function get the next issue for the subscription given on input arg
830 my ($subscription, $pattern, $planneddate) = @_;
832 return unless ($subscription and $pattern);
834 my ( $newlastvalue1, $newlastvalue2, $newlastvalue3,
835 $newinnerloop1, $newinnerloop2, $newinnerloop3 );
838 if ($subscription->{'skip_serialseq'}) {
839 my @irreg = split /;/, $subscription->{'irregularity'};
841 my $irregularities = {};
842 $irregularities->{$_} = 1 foreach(@irreg);
843 my $issueno = GetFictiveIssueNumber($subscription, $planneddate) + 1;
844 while($irregularities->{$issueno}) {
851 my $numberingmethod = $pattern->{numberingmethod};
853 if ($numberingmethod) {
854 $calculated = $numberingmethod;
855 my $locale = $subscription->{locale};
856 $newlastvalue1 = $subscription->{lastvalue1} || 0;
857 $newlastvalue2 = $subscription->{lastvalue2} || 0;
858 $newlastvalue3 = $subscription->{lastvalue3} || 0;
859 $newinnerloop1 = $subscription->{innerloop1} || 0;
860 $newinnerloop2 = $subscription->{innerloop2} || 0;
861 $newinnerloop3 = $subscription->{innerloop3} || 0;
864 $calc{$_} = 1 if ($numberingmethod =~ /\{$_\}/);
867 for(my $i = 0; $i < $count; $i++) {
869 # check if we have to increase the new value.
871 if ($newinnerloop1 >= $pattern->{every1}) {
873 $newlastvalue1 += $pattern->{add1};
875 # reset counter if needed.
876 $newlastvalue1 = $pattern->{setto1} if ($newlastvalue1 > $pattern->{whenmorethan1});
879 # check if we have to increase the new value.
881 if ($newinnerloop2 >= $pattern->{every2}) {
883 $newlastvalue2 += $pattern->{add2};
885 # reset counter if needed.
886 $newlastvalue2 = $pattern->{setto2} if ($newlastvalue2 > $pattern->{whenmorethan2});
889 # check if we have to increase the new value.
891 if ($newinnerloop3 >= $pattern->{every3}) {
893 $newlastvalue3 += $pattern->{add3};
895 # reset counter if needed.
896 $newlastvalue3 = $pattern->{setto3} if ($newlastvalue3 > $pattern->{whenmorethan3});
900 my $newlastvalue1string = _numeration( $newlastvalue1, $pattern->{numbering1}, $locale );
901 $calculated =~ s/\{X\}/$newlastvalue1string/g;
904 my $newlastvalue2string = _numeration( $newlastvalue2, $pattern->{numbering2}, $locale );
905 $calculated =~ s/\{Y\}/$newlastvalue2string/g;
908 my $newlastvalue3string = _numeration( $newlastvalue3, $pattern->{numbering3}, $locale );
909 $calculated =~ s/\{Z\}/$newlastvalue3string/g;
914 $newlastvalue1, $newlastvalue2, $newlastvalue3,
915 $newinnerloop1, $newinnerloop2, $newinnerloop3);
920 $calculated = GetSeq($subscription, $pattern)
921 $subscription is a hashref containing all the attributes of the table 'subscription'
922 $pattern is a hashref containing all the attributes of the table 'subscription_numberpatterns'
923 this function transforms {X},{Y},{Z} to 150,0,0 for example.
925 the sequence in string format
930 my ($subscription, $pattern) = @_;
932 return unless ($subscription and $pattern);
934 my $locale = $subscription->{locale};
936 my $calculated = $pattern->{numberingmethod};
938 my $newlastvalue1 = $subscription->{'lastvalue1'} || 0;
939 $newlastvalue1 = _numeration($newlastvalue1, $pattern->{numbering1}, $locale) if ($pattern->{numbering1}); # reset counter if needed.
940 $calculated =~ s/\{X\}/$newlastvalue1/g;
942 my $newlastvalue2 = $subscription->{'lastvalue2'} || 0;
943 $newlastvalue2 = _numeration($newlastvalue2, $pattern->{numbering2}, $locale) if ($pattern->{numbering2}); # reset counter if needed.
944 $calculated =~ s/\{Y\}/$newlastvalue2/g;
946 my $newlastvalue3 = $subscription->{'lastvalue3'} || 0;
947 $newlastvalue3 = _numeration($newlastvalue3, $pattern->{numbering3}, $locale) if ($pattern->{numbering3}); # reset counter if needed.
948 $calculated =~ s/\{Z\}/$newlastvalue3/g;
952 =head2 GetExpirationDate
954 $enddate = GetExpirationDate($subscriptionid, [$startdate])
956 this function return the next expiration date for a subscription given on input args.
963 sub GetExpirationDate {
964 my ( $subscriptionid, $startdate ) = @_;
966 return unless ($subscriptionid);
968 my $dbh = C4::Context->dbh;
969 my $subscription = GetSubscription($subscriptionid);
972 # we don't do the same test if the subscription is based on X numbers or on X weeks/months
973 $enddate = $startdate || $subscription->{startdate};
974 my @date = split( /-/, $enddate );
976 return if ( scalar(@date) != 3 || not check_date(@date) );
978 my $frequency = C4::Serials::Frequency::GetSubscriptionFrequency($subscription->{periodicity});
979 if ( $frequency and $frequency->{unit} ) {
982 if ( my $length = $subscription->{numberlength} ) {
984 #calculate the date of the last issue.
985 for ( my $i = 1 ; $i <= $length ; $i++ ) {
986 $enddate = GetNextDate( $subscription, $enddate );
988 } elsif ( $subscription->{monthlength} ) {
989 if ( $$subscription{startdate} ) {
990 my @enddate = Add_Delta_YM( $date[0], $date[1], $date[2], 0, $subscription->{monthlength} );
991 $enddate = sprintf( "%04d-%02d-%02d", $enddate[0], $enddate[1], $enddate[2] );
993 } elsif ( $subscription->{weeklength} ) {
994 if ( $$subscription{startdate} ) {
995 my @date = split( /-/, $subscription->{startdate} );
996 my @enddate = Add_Delta_Days( $date[0], $date[1], $date[2], $subscription->{weeklength} * 7 );
997 $enddate = sprintf( "%04d-%02d-%02d", $enddate[0], $enddate[1], $enddate[2] );
1000 $enddate = $subscription->{enddate};
1004 return $subscription->{enddate};
1008 =head2 CountSubscriptionFromBiblionumber
1010 $subscriptionsnumber = CountSubscriptionFromBiblionumber($biblionumber)
1011 this returns a count of the subscriptions for a given biblionumber
1013 the number of subscriptions
1017 sub CountSubscriptionFromBiblionumber {
1018 my ($biblionumber) = @_;
1020 return unless ($biblionumber);
1022 my $dbh = C4::Context->dbh;
1023 my $query = "SELECT count(*) FROM subscription WHERE biblionumber=?";
1024 my $sth = $dbh->prepare($query);
1025 $sth->execute($biblionumber);
1026 my $subscriptionsnumber = $sth->fetchrow;
1027 return $subscriptionsnumber;
1030 =head2 ModSubscriptionHistory
1032 ModSubscriptionHistory($subscriptionid,$histstartdate,$enddate,$recievedlist,$missinglist,$opacnote,$librariannote);
1034 this function modifies the history of a subscription. Put your new values on input arg.
1035 returns the number of rows affected
1039 sub ModSubscriptionHistory {
1040 my ( $subscriptionid, $histstartdate, $enddate, $receivedlist, $missinglist, $opacnote, $librariannote ) = @_;
1042 return unless ($subscriptionid);
1044 my $dbh = C4::Context->dbh;
1045 my $query = "UPDATE subscriptionhistory
1046 SET histstartdate=?,histenddate=?,recievedlist=?,missinglist=?,opacnote=?,librariannote=?
1047 WHERE subscriptionid=?
1049 my $sth = $dbh->prepare($query);
1050 $receivedlist =~ s/^; // if $receivedlist;
1051 $missinglist =~ s/^; // if $missinglist;
1052 $opacnote =~ s/^; // if $opacnote;
1053 $sth->execute( $histstartdate, $enddate, $receivedlist, $missinglist, $opacnote, $librariannote, $subscriptionid );
1057 =head2 ModSerialStatus
1059 ModSerialStatus($serialid,$serialseq, $planneddate,$publisheddate,$status,$notes)
1061 This function modify the serial status. Serial status is a number.(eg 2 is "arrived")
1062 Note : if we change from "waited" to something else,then we will have to create a new "waited" entry
1066 sub ModSerialStatus {
1067 my ( $serialid, $serialseq, $planneddate, $publisheddate, $status, $notes ) = @_;
1069 return unless ($serialid);
1071 #It is a usual serial
1072 # 1st, get previous status :
1073 my $dbh = C4::Context->dbh;
1074 my $query = "SELECT serial.subscriptionid,serial.status,subscription.periodicity
1075 FROM serial, subscription
1076 WHERE serial.subscriptionid=subscription.subscriptionid
1078 my $sth = $dbh->prepare($query);
1079 $sth->execute($serialid);
1080 my ( $subscriptionid, $oldstatus, $periodicity ) = $sth->fetchrow;
1081 my $frequency = GetSubscriptionFrequency($periodicity);
1083 # change status & update subscriptionhistory
1085 if ( $status == 6 ) {
1086 DelIssue( { 'serialid' => $serialid, 'subscriptionid' => $subscriptionid, 'serialseq' => $serialseq } );
1089 my $query = 'UPDATE serial SET serialseq=?,publisheddate=?,planneddate=?,status=?,notes=? WHERE serialid = ?';
1090 $sth = $dbh->prepare($query);
1091 $sth->execute( $serialseq, $publisheddate, $planneddate, $status, $notes, $serialid );
1092 $query = "SELECT * FROM subscription WHERE subscriptionid = ?";
1093 $sth = $dbh->prepare($query);
1094 $sth->execute($subscriptionid);
1095 my $val = $sth->fetchrow_hashref;
1096 unless ( $val->{manualhistory} ) {
1097 $query = "SELECT missinglist,recievedlist FROM subscriptionhistory WHERE subscriptionid=?";
1098 $sth = $dbh->prepare($query);
1099 $sth->execute($subscriptionid);
1100 my ( $missinglist, $recievedlist ) = $sth->fetchrow;
1102 if ( $status == 2 || ($oldstatus == 2 && $status != 2) ) {
1103 $recievedlist .= "; $serialseq"
1104 if ($recievedlist !~ /(^|;)\s*$serialseq(?=;|$)/);
1107 # in case serial has been previously marked as missing
1108 if (grep /$status/, (1,2,3,7)) {
1109 $missinglist=~ s/(^|;)\s*$serialseq(?=;|$)//g;
1112 my @missing_statuses = qw( 4 41 42 43 44 );
1113 $missinglist .= "; $serialseq"
1114 if ( ( grep { $_ == $status } @missing_statuses ) && ( $missinglist !~/(^|;)\s*$serialseq(?=;|$)/ ) );
1115 $missinglist .= "; not issued $serialseq"
1116 if ( $status == 5 && $missinglist !~ /(^|;)\s*$serialseq(?=;|$)/ );
1118 $query = "UPDATE subscriptionhistory SET recievedlist=?, missinglist=? WHERE subscriptionid=?";
1119 $sth = $dbh->prepare($query);
1120 $recievedlist =~ s/^; //;
1121 $missinglist =~ s/^; //;
1122 $sth->execute( $recievedlist, $missinglist, $subscriptionid );
1126 # create new waited entry if needed (ie : was a "waited" and has changed)
1127 if ( $oldstatus == 1 && $status != 1 ) {
1128 my $subscription = GetSubscription($subscriptionid);
1129 my $pattern = C4::Serials::Numberpattern::GetSubscriptionNumberpattern($subscription->{numberpattern});
1133 $newserialseq, $newlastvalue1, $newlastvalue2, $newlastvalue3,
1134 $newinnerloop1, $newinnerloop2, $newinnerloop3
1136 = GetNextSeq( $subscription, $pattern, $publisheddate );
1138 # next date (calculated from actual date & frequency parameters)
1139 my $nextpublisheddate = GetNextDate($subscription, $publisheddate, 1);
1140 my $nextpubdate = $nextpublisheddate;
1141 NewIssue( $newserialseq, $subscriptionid, $subscription->{'biblionumber'}, 1, $nextpubdate, $nextpubdate );
1142 $query = "UPDATE subscription SET lastvalue1=?, lastvalue2=?, lastvalue3=?, innerloop1=?, innerloop2=?, innerloop3=?
1143 WHERE subscriptionid = ?";
1144 $sth = $dbh->prepare($query);
1145 $sth->execute( $newlastvalue1, $newlastvalue2, $newlastvalue3, $newinnerloop1, $newinnerloop2, $newinnerloop3, $subscriptionid );
1147 # check if an alert must be sent... (= a letter is defined & status became "arrived"
1148 if ( $subscription->{letter} && $status == 2 && $oldstatus != 2 ) {
1149 require C4::Letters;
1150 C4::Letters::SendAlerts( 'issue', $subscription->{subscriptionid}, $subscription->{letter} );
1157 =head2 GetNextExpected
1159 $nextexpected = GetNextExpected($subscriptionid)
1161 Get the planneddate for the current expected issue of the subscription.
1167 planneddate => ISO date
1172 sub GetNextExpected {
1173 my ($subscriptionid) = @_;
1175 my $dbh = C4::Context->dbh;
1179 WHERE subscriptionid = ?
1183 my $sth = $dbh->prepare($query);
1185 # Each subscription has only one 'expected' issue, with serial.status==1.
1186 $sth->execute( $subscriptionid, 1 );
1187 my $nextissue = $sth->fetchrow_hashref;
1188 if ( !$nextissue ) {
1192 WHERE subscriptionid = ?
1193 ORDER BY publisheddate DESC
1196 $sth = $dbh->prepare($query);
1197 $sth->execute($subscriptionid);
1198 $nextissue = $sth->fetchrow_hashref;
1200 foreach(qw/planneddate publisheddate/) {
1201 if ( !defined $nextissue->{$_} ) {
1202 # or should this default to 1st Jan ???
1203 $nextissue->{$_} = strftime( '%Y-%m-%d', localtime );
1205 $nextissue->{$_} = ($nextissue->{$_} ne '0000-00-00')
1213 =head2 ModNextExpected
1215 ModNextExpected($subscriptionid,$date)
1217 Update the planneddate for the current expected issue of the subscription.
1218 This will modify all future prediction results.
1220 C<$date> is an ISO date.
1226 sub ModNextExpected {
1227 my ( $subscriptionid, $date ) = @_;
1228 my $dbh = C4::Context->dbh;
1230 #FIXME: Would expect to only set planneddate, but we set both on new issue creation, so updating it here
1231 my $sth = $dbh->prepare('UPDATE serial SET planneddate=?,publisheddate=? WHERE subscriptionid=? AND status=?');
1233 # Each subscription has only one 'expected' issue, with serial.status==1.
1234 $sth->execute( $date, $date, $subscriptionid, 1 );
1239 =head2 GetSubscriptionIrregularities
1243 =item @irreg = &GetSubscriptionIrregularities($subscriptionid);
1244 get the list of irregularities for a subscription
1250 sub GetSubscriptionIrregularities {
1251 my $subscriptionid = shift;
1253 return unless $subscriptionid;
1255 my $dbh = C4::Context->dbh;
1259 WHERE subscriptionid = ?
1261 my $sth = $dbh->prepare($query);
1262 $sth->execute($subscriptionid);
1264 my ($result) = $sth->fetchrow_array;
1265 my @irreg = split /;/, $result;
1270 =head2 ModSubscription
1272 this function modifies a subscription. Put all new values on input args.
1273 returns the number of rows affected
1277 sub ModSubscription {
1279 $auser, $branchcode, $aqbooksellerid, $cost, $aqbudgetid, $startdate,
1280 $periodicity, $firstacquidate, $irregularity, $numberpattern, $locale,
1281 $numberlength, $weeklength, $monthlength, $lastvalue1, $innerloop1,
1282 $lastvalue2, $innerloop2, $lastvalue3, $innerloop3, $status,
1283 $biblionumber, $callnumber, $notes, $letter, $manualhistory,
1284 $internalnotes, $serialsadditems, $staffdisplaycount, $opacdisplaycount,
1285 $graceperiod, $location, $enddate, $subscriptionid, $skip_serialseq
1288 my $dbh = C4::Context->dbh;
1289 my $query = "UPDATE subscription
1290 SET librarian=?, branchcode=?, aqbooksellerid=?, cost=?, aqbudgetid=?,
1291 startdate=?, periodicity=?, firstacquidate=?, irregularity=?,
1292 numberpattern=?, locale=?, numberlength=?, weeklength=?, monthlength=?,
1293 lastvalue1=?, innerloop1=?, lastvalue2=?, innerloop2=?,
1294 lastvalue3=?, innerloop3=?, status=?, biblionumber=?,
1295 callnumber=?, notes=?, letter=?, manualhistory=?,
1296 internalnotes=?, serialsadditems=?, staffdisplaycount=?,
1297 opacdisplaycount=?, graceperiod=?, location = ?, enddate=?,
1299 WHERE subscriptionid = ?";
1301 my $sth = $dbh->prepare($query);
1303 $auser, $branchcode, $aqbooksellerid, $cost,
1304 $aqbudgetid, $startdate, $periodicity, $firstacquidate,
1305 $irregularity, $numberpattern, $locale, $numberlength,
1306 $weeklength, $monthlength, $lastvalue1, $innerloop1,
1307 $lastvalue2, $innerloop2, $lastvalue3, $innerloop3,
1308 $status, $biblionumber, $callnumber, $notes,
1309 $letter, ($manualhistory ? $manualhistory : 0),
1310 $internalnotes, $serialsadditems, $staffdisplaycount, $opacdisplaycount,
1311 $graceperiod, $location, $enddate, $skip_serialseq,
1314 my $rows = $sth->rows;
1316 logaction( "SERIAL", "MODIFY", $subscriptionid, "" ) if C4::Context->preference("SubscriptionLog");
1320 =head2 NewSubscription
1322 $subscriptionid = &NewSubscription($auser,branchcode,$aqbooksellerid,$cost,$aqbudgetid,$biblionumber,
1323 $startdate,$periodicity,$numberlength,$weeklength,$monthlength,
1324 $lastvalue1,$innerloop1,$lastvalue2,$innerloop2,$lastvalue3,$innerloop3,
1325 $status, $notes, $letter, $firstacquidate, $irregularity, $numberpattern,
1326 $locale, $callnumber, $manualhistory, $internalnotes, $serialsadditems,
1327 $staffdisplaycount, $opacdisplaycount, $graceperiod, $location, $enddate, $skip_serialseq);
1329 Create a new subscription with value given on input args.
1332 the id of this new subscription
1336 sub NewSubscription {
1338 $auser, $branchcode, $aqbooksellerid, $cost, $aqbudgetid, $biblionumber,
1339 $startdate, $periodicity, $numberlength, $weeklength, $monthlength,
1340 $lastvalue1, $innerloop1, $lastvalue2, $innerloop2, $lastvalue3,
1341 $innerloop3, $status, $notes, $letter, $firstacquidate, $irregularity,
1342 $numberpattern, $locale, $callnumber, $manualhistory, $internalnotes,
1343 $serialsadditems, $staffdisplaycount, $opacdisplaycount, $graceperiod,
1344 $location, $enddate, $skip_serialseq
1346 my $dbh = C4::Context->dbh;
1348 #save subscription (insert into database)
1350 INSERT INTO subscription
1351 (librarian, branchcode, aqbooksellerid, cost, aqbudgetid,
1352 biblionumber, startdate, periodicity, numberlength, weeklength,
1353 monthlength, lastvalue1, innerloop1, lastvalue2, innerloop2,
1354 lastvalue3, innerloop3, status, notes, letter, firstacquidate,
1355 irregularity, numberpattern, locale, callnumber,
1356 manualhistory, internalnotes, serialsadditems, staffdisplaycount,
1357 opacdisplaycount, graceperiod, location, enddate, skip_serialseq)
1358 VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
1360 my $sth = $dbh->prepare($query);
1362 $auser, $branchcode, $aqbooksellerid, $cost, $aqbudgetid, $biblionumber,
1363 $startdate, $periodicity, $numberlength, $weeklength,
1364 $monthlength, $lastvalue1, $innerloop1, $lastvalue2, $innerloop2,
1365 $lastvalue3, $innerloop3, $status, $notes, $letter,
1366 $firstacquidate, $irregularity, $numberpattern, $locale, $callnumber,
1367 $manualhistory, $internalnotes, $serialsadditems, $staffdisplaycount,
1368 $opacdisplaycount, $graceperiod, $location, $enddate, $skip_serialseq
1371 my $subscriptionid = $dbh->{'mysql_insertid'};
1373 $enddate = GetExpirationDate( $subscriptionid, $startdate );
1377 WHERE subscriptionid=?
1379 $sth = $dbh->prepare($query);
1380 $sth->execute( $enddate, $subscriptionid );
1383 # then create the 1st expected number
1385 INSERT INTO subscriptionhistory
1386 (biblionumber, subscriptionid, histstartdate)
1389 $sth = $dbh->prepare($query);
1390 $sth->execute( $biblionumber, $subscriptionid, $startdate);
1392 # reread subscription to get a hash (for calculation of the 1st issue number)
1393 my $subscription = GetSubscription($subscriptionid);
1394 my $pattern = C4::Serials::Numberpattern::GetSubscriptionNumberpattern($subscription->{numberpattern});
1396 # calculate issue number
1397 my $serialseq = GetSeq($subscription, $pattern) || q{};
1400 (serialseq,subscriptionid,biblionumber,status, planneddate, publisheddate)
1401 VALUES (?,?,?,?,?,?)
1403 $sth = $dbh->prepare($query);
1404 $sth->execute( $serialseq, $subscriptionid, $biblionumber, 1, $firstacquidate, $firstacquidate );
1406 logaction( "SERIAL", "ADD", $subscriptionid, "" ) if C4::Context->preference("SubscriptionLog");
1408 #set serial flag on biblio if not already set.
1409 my $bib = GetBiblio($biblionumber);
1410 if ( $bib and !$bib->{'serial'} ) {
1411 my $record = GetMarcBiblio($biblionumber);
1412 my ( $tag, $subf ) = GetMarcFromKohaField( 'biblio.serial', $bib->{'frameworkcode'} );
1414 eval { $record->field($tag)->update( $subf => 1 ); };
1416 ModBiblio( $record, $biblionumber, $bib->{'frameworkcode'} );
1418 return $subscriptionid;
1421 =head2 ReNewSubscription
1423 ReNewSubscription($subscriptionid,$user,$startdate,$numberlength,$weeklength,$monthlength,$note)
1425 this function renew a subscription with values given on input args.
1429 sub ReNewSubscription {
1430 my ( $subscriptionid, $user, $startdate, $numberlength, $weeklength, $monthlength, $note ) = @_;
1431 my $dbh = C4::Context->dbh;
1432 my $subscription = GetSubscription($subscriptionid);
1436 LEFT JOIN biblioitems ON biblio.biblionumber=biblioitems.biblionumber
1437 WHERE biblio.biblionumber=?
1439 my $sth = $dbh->prepare($query);
1440 $sth->execute( $subscription->{biblionumber} );
1441 my $biblio = $sth->fetchrow_hashref;
1443 if ( C4::Context->preference("RenewSerialAddsSuggestion") ) {
1444 require C4::Suggestions;
1445 C4::Suggestions::NewSuggestion(
1446 { 'suggestedby' => $user,
1447 'title' => $subscription->{bibliotitle},
1448 'author' => $biblio->{author},
1449 'publishercode' => $biblio->{publishercode},
1450 'note' => $biblio->{note},
1451 'biblionumber' => $subscription->{biblionumber}
1456 # renew subscription
1459 SET startdate=?,numberlength=?,weeklength=?,monthlength=?,reneweddate=NOW()
1460 WHERE subscriptionid=?
1462 $sth = $dbh->prepare($query);
1463 $sth->execute( $startdate, $numberlength, $weeklength, $monthlength, $subscriptionid );
1464 my $enddate = GetExpirationDate($subscriptionid);
1465 $debug && warn "enddate :$enddate";
1469 WHERE subscriptionid=?
1471 $sth = $dbh->prepare($query);
1472 $sth->execute( $enddate, $subscriptionid );
1474 UPDATE subscriptionhistory
1476 WHERE subscriptionid=?
1478 $sth = $dbh->prepare($query);
1479 $sth->execute( $enddate, $subscriptionid );
1481 logaction( "SERIAL", "RENEW", $subscriptionid, "" ) if C4::Context->preference("SubscriptionLog");
1487 NewIssue($serialseq,$subscriptionid,$biblionumber,$status, $planneddate, $publisheddate, $notes)
1489 Create a new issue stored on the database.
1490 Note : we have to update the recievedlist and missinglist on subscriptionhistory for this subscription.
1491 returns the serial id
1496 my ( $serialseq, $subscriptionid, $biblionumber, $status, $planneddate, $publisheddate, $notes ) = @_;
1497 ### FIXME biblionumber CAN be provided by subscriptionid. So Do we STILL NEED IT ?
1499 return unless ($subscriptionid);
1501 my $dbh = C4::Context->dbh;
1504 (serialseq,subscriptionid,biblionumber,status,publisheddate,planneddate,notes)
1505 VALUES (?,?,?,?,?,?,?)
1507 my $sth = $dbh->prepare($query);
1508 $sth->execute( $serialseq, $subscriptionid, $biblionumber, $status, $publisheddate, $planneddate, $notes );
1509 my $serialid = $dbh->{'mysql_insertid'};
1511 SELECT missinglist,recievedlist
1512 FROM subscriptionhistory
1513 WHERE subscriptionid=?
1515 $sth = $dbh->prepare($query);
1516 $sth->execute($subscriptionid);
1517 my ( $missinglist, $recievedlist ) = $sth->fetchrow;
1519 if ( $status == 2 ) {
1520 ### TODO Add a feature that improves recognition and description.
1521 ### As such count (serialseq) i.e. : N18,2(N19),N20
1522 ### Would use substr and index But be careful to previous presence of ()
1523 $recievedlist .= "; $serialseq" unless (index($recievedlist,$serialseq)>0);
1525 if ( $status == 4 ) {
1526 $missinglist .= "; $serialseq" unless (index($missinglist,$serialseq)>0);
1529 UPDATE subscriptionhistory
1530 SET recievedlist=?, missinglist=?
1531 WHERE subscriptionid=?
1533 $sth = $dbh->prepare($query);
1534 $recievedlist =~ s/^; //;
1535 $missinglist =~ s/^; //;
1536 $sth->execute( $recievedlist, $missinglist, $subscriptionid );
1540 =head2 ItemizeSerials
1542 ItemizeSerials($serialid, $info);
1543 $info is a hashref containing barcode branch, itemcallnumber, status, location
1544 $serialid the serialid
1546 1 if the itemize is a succes.
1547 0 and @error otherwise. @error containts the list of errors found.
1551 sub ItemizeSerials {
1552 my ( $serialid, $info ) = @_;
1554 return unless ($serialid);
1556 my $now = POSIX::strftime( "%Y-%m-%d", localtime );
1558 my $dbh = C4::Context->dbh;
1564 my $sth = $dbh->prepare($query);
1565 $sth->execute($serialid);
1566 my $data = $sth->fetchrow_hashref;
1567 if ( C4::Context->preference("RoutingSerials") ) {
1569 # check for existing biblioitem relating to serial issue
1570 my ( $count, @results ) = GetBiblioItemByBiblioNumber( $data->{'biblionumber'} );
1572 for ( my $i = 0 ; $i < $count ; $i++ ) {
1573 if ( $results[$i]->{'volumeddesc'} eq $data->{'serialseq'} . ' (' . $data->{'planneddate'} . ')' ) {
1574 $bibitemno = $results[$i]->{'biblioitemnumber'};
1578 if ( $bibitemno == 0 ) {
1579 my $sth = $dbh->prepare( "SELECT * FROM biblioitems WHERE biblionumber = ? ORDER BY biblioitemnumber DESC" );
1580 $sth->execute( $data->{'biblionumber'} );
1581 my $biblioitem = $sth->fetchrow_hashref;
1582 $biblioitem->{'volumedate'} = $data->{planneddate};
1583 $biblioitem->{'volumeddesc'} = $data->{serialseq} . ' (' . format_date( $data->{'planneddate'} ) . ')';
1584 $biblioitem->{'dewey'} = $info->{itemcallnumber};
1588 my $fwk = GetFrameworkCode( $data->{'biblionumber'} );
1589 if ( $info->{barcode} ) {
1591 if ( is_barcode_in_use( $info->{barcode} ) ) {
1592 push @errors, 'barcode_not_unique';
1594 my $marcrecord = MARC::Record->new();
1595 my ( $tag, $subfield ) = GetMarcFromKohaField( "items.barcode", $fwk );
1596 my $newField = MARC::Field->new( "$tag", '', '', "$subfield" => $info->{barcode} );
1597 $marcrecord->insert_fields_ordered($newField);
1598 if ( $info->{branch} ) {
1599 my ( $tag, $subfield ) = GetMarcFromKohaField( "items.homebranch", $fwk );
1601 #warn "items.homebranch : $tag , $subfield";
1602 if ( $marcrecord->field($tag) ) {
1603 $marcrecord->field($tag)->add_subfields( "$subfield" => $info->{branch} );
1605 my $newField = MARC::Field->new( "$tag", '', '', "$subfield" => $info->{branch} );
1606 $marcrecord->insert_fields_ordered($newField);
1608 ( $tag, $subfield ) = GetMarcFromKohaField( "items.holdingbranch", $fwk );
1610 #warn "items.holdingbranch : $tag , $subfield";
1611 if ( $marcrecord->field($tag) ) {
1612 $marcrecord->field($tag)->add_subfields( "$subfield" => $info->{branch} );
1614 my $newField = MARC::Field->new( "$tag", '', '', "$subfield" => $info->{branch} );
1615 $marcrecord->insert_fields_ordered($newField);
1618 if ( $info->{itemcallnumber} ) {
1619 my ( $tag, $subfield ) = GetMarcFromKohaField( "items.itemcallnumber", $fwk );
1621 if ( $marcrecord->field($tag) ) {
1622 $marcrecord->field($tag)->add_subfields( "$subfield" => $info->{itemcallnumber} );
1624 my $newField = MARC::Field->new( "$tag", '', '', "$subfield" => $info->{itemcallnumber} );
1625 $marcrecord->insert_fields_ordered($newField);
1628 if ( $info->{notes} ) {
1629 my ( $tag, $subfield ) = GetMarcFromKohaField( "items.itemnotes", $fwk );
1631 if ( $marcrecord->field($tag) ) {
1632 $marcrecord->field($tag)->add_subfields( "$subfield" => $info->{notes} );
1634 my $newField = MARC::Field->new( "$tag", '', '', "$subfield" => $info->{notes} );
1635 $marcrecord->insert_fields_ordered($newField);
1638 if ( $info->{location} ) {
1639 my ( $tag, $subfield ) = GetMarcFromKohaField( "items.location", $fwk );
1641 if ( $marcrecord->field($tag) ) {
1642 $marcrecord->field($tag)->add_subfields( "$subfield" => $info->{location} );
1644 my $newField = MARC::Field->new( "$tag", '', '', "$subfield" => $info->{location} );
1645 $marcrecord->insert_fields_ordered($newField);
1648 if ( $info->{status} ) {
1649 my ( $tag, $subfield ) = GetMarcFromKohaField( "items.notforloan", $fwk );
1651 if ( $marcrecord->field($tag) ) {
1652 $marcrecord->field($tag)->add_subfields( "$subfield" => $info->{status} );
1654 my $newField = MARC::Field->new( "$tag", '', '', "$subfield" => $info->{status} );
1655 $marcrecord->insert_fields_ordered($newField);
1658 if ( C4::Context->preference("RoutingSerials") ) {
1659 my ( $tag, $subfield ) = GetMarcFromKohaField( "items.dateaccessioned", $fwk );
1660 if ( $marcrecord->field($tag) ) {
1661 $marcrecord->field($tag)->add_subfields( "$subfield" => $now );
1663 my $newField = MARC::Field->new( "$tag", '', '', "$subfield" => $now );
1664 $marcrecord->insert_fields_ordered($newField);
1668 C4::Items::AddItemFromMarc( $marcrecord, $data->{'biblionumber'} );
1671 return ( 0, @errors );
1675 =head2 HasSubscriptionStrictlyExpired
1677 1 or 0 = HasSubscriptionStrictlyExpired($subscriptionid)
1679 the subscription has stricly expired when today > the end subscription date
1682 1 if true, 0 if false, -1 if the expiration date is not set.
1686 sub HasSubscriptionStrictlyExpired {
1688 # Getting end of subscription date
1689 my ($subscriptionid) = @_;
1691 return unless ($subscriptionid);
1693 my $dbh = C4::Context->dbh;
1694 my $subscription = GetSubscription($subscriptionid);
1695 my $expirationdate = $subscription->{enddate} || GetExpirationDate($subscriptionid);
1697 # If the expiration date is set
1698 if ( $expirationdate != 0 ) {
1699 my ( $endyear, $endmonth, $endday ) = split( '-', $expirationdate );
1701 # Getting today's date
1702 my ( $nowyear, $nowmonth, $nowday ) = Today();
1704 # if today's date > expiration date, then the subscription has stricly expired
1705 if ( Delta_Days( $nowyear, $nowmonth, $nowday, $endyear, $endmonth, $endday ) < 0 ) {
1712 # There are some cases where the expiration date is not set
1713 # As we can't determine if the subscription has expired on a date-basis,
1719 =head2 HasSubscriptionExpired
1721 $has_expired = HasSubscriptionExpired($subscriptionid)
1723 the subscription has expired when the next issue to arrive is out of subscription limit.
1726 0 if the subscription has not expired
1727 1 if the subscription has expired
1728 2 if has subscription does not have a valid expiration date set
1732 sub HasSubscriptionExpired {
1733 my ($subscriptionid) = @_;
1735 return unless ($subscriptionid);
1737 my $dbh = C4::Context->dbh;
1738 my $subscription = GetSubscription($subscriptionid);
1739 my $frequency = C4::Serials::Frequency::GetSubscriptionFrequency($subscription->{periodicity});
1740 if ( $frequency and $frequency->{unit} ) {
1741 my $expirationdate = $subscription->{enddate} || GetExpirationDate($subscriptionid);
1742 if (!defined $expirationdate) {
1743 $expirationdate = q{};
1746 SELECT max(planneddate)
1748 WHERE subscriptionid=?
1750 my $sth = $dbh->prepare($query);
1751 $sth->execute($subscriptionid);
1752 my ($res) = $sth->fetchrow;
1753 if (!$res || $res=~m/^0000/) {
1756 my @res = split( /-/, $res );
1757 my @endofsubscriptiondate = split( /-/, $expirationdate );
1758 return 2 if ( scalar(@res) != 3 || scalar(@endofsubscriptiondate) != 3 || not check_date(@res) || not check_date(@endofsubscriptiondate) );
1760 if ( ( @endofsubscriptiondate && Delta_Days( $res[0], $res[1], $res[2], $endofsubscriptiondate[0], $endofsubscriptiondate[1], $endofsubscriptiondate[2] ) <= 0 )
1765 if ( $subscription->{'numberlength'} ) {
1766 my $countreceived = countissuesfrom( $subscriptionid, $subscription->{'startdate'} );
1767 return 1 if ( $countreceived > $subscription->{'numberlength'} );
1773 return 0; # Notice that you'll never get here.
1776 =head2 SetDistributedto
1778 SetDistributedto($distributedto,$subscriptionid);
1779 This function update the value of distributedto for a subscription given on input arg.
1783 sub SetDistributedto {
1784 my ( $distributedto, $subscriptionid ) = @_;
1785 my $dbh = C4::Context->dbh;
1789 WHERE subscriptionid=?
1791 my $sth = $dbh->prepare($query);
1792 $sth->execute( $distributedto, $subscriptionid );
1796 =head2 DelSubscription
1798 DelSubscription($subscriptionid)
1799 this function deletes subscription which has $subscriptionid as id.
1803 sub DelSubscription {
1804 my ($subscriptionid) = @_;
1805 my $dbh = C4::Context->dbh;
1806 $subscriptionid = $dbh->quote($subscriptionid);
1807 $dbh->do("DELETE FROM subscription WHERE subscriptionid=$subscriptionid");
1808 $dbh->do("DELETE FROM subscriptionhistory WHERE subscriptionid=$subscriptionid");
1809 $dbh->do("DELETE FROM serial WHERE subscriptionid=$subscriptionid");
1811 logaction( "SERIAL", "DELETE", $subscriptionid, "" ) if C4::Context->preference("SubscriptionLog");
1816 DelIssue($serialseq,$subscriptionid)
1817 this function deletes an issue which has $serialseq and $subscriptionid given on input arg.
1819 returns the number of rows affected
1824 my ($dataissue) = @_;
1825 my $dbh = C4::Context->dbh;
1826 ### TODO Add itemdeletion. Would need to get itemnumbers. Should be in a pref ?
1831 AND subscriptionid= ?
1833 my $mainsth = $dbh->prepare($query);
1834 $mainsth->execute( $dataissue->{'serialid'}, $dataissue->{'subscriptionid'} );
1836 #Delete element from subscription history
1837 $query = "SELECT * FROM subscription WHERE subscriptionid = ?";
1838 my $sth = $dbh->prepare($query);
1839 $sth->execute( $dataissue->{'subscriptionid'} );
1840 my $val = $sth->fetchrow_hashref;
1841 unless ( $val->{manualhistory} ) {
1843 SELECT * FROM subscriptionhistory
1844 WHERE subscriptionid= ?
1846 my $sth = $dbh->prepare($query);
1847 $sth->execute( $dataissue->{'subscriptionid'} );
1848 my $data = $sth->fetchrow_hashref;
1849 my $serialseq = $dataissue->{'serialseq'};
1850 $data->{'missinglist'} =~ s/\b$serialseq\b//;
1851 $data->{'recievedlist'} =~ s/\b$serialseq\b//;
1852 my $strsth = "UPDATE subscriptionhistory SET " . join( ",", map { join( "=", $_, $dbh->quote( $data->{$_} ) ) } keys %$data ) . " WHERE subscriptionid=?";
1853 $sth = $dbh->prepare($strsth);
1854 $sth->execute( $dataissue->{'subscriptionid'} );
1857 return $mainsth->rows;
1860 =head2 GetLateOrMissingIssues
1862 @issuelist = GetLateMissingIssues($supplierid,$serialid)
1864 this function selects missing issues on database - where serial.status = 4 or serial.status=3 or planneddate<now
1867 the issuelist as an array of hash refs. Each element of this array contains
1868 name,title,planneddate,serialseq,serial.subscriptionid from tables : subscription, serial & biblio
1872 sub GetLateOrMissingIssues {
1873 my ( $supplierid, $serialid, $order ) = @_;
1875 return unless ( $supplierid or $serialid );
1877 my $dbh = C4::Context->dbh;
1881 $byserial = "and serialid = " . $serialid;
1884 $order .= ", title";
1889 $sth = $dbh->prepare(
1891 serialid, aqbooksellerid, name,
1892 biblio.title, biblioitems.issn, planneddate, serialseq,
1893 serial.status, serial.subscriptionid, claimdate, claims_count,
1894 subscription.branchcode
1896 LEFT JOIN subscription ON serial.subscriptionid=subscription.subscriptionid
1897 LEFT JOIN biblio ON subscription.biblionumber=biblio.biblionumber
1898 LEFT JOIN biblioitems ON subscription.biblionumber=biblioitems.biblionumber
1899 LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id
1900 WHERE subscription.subscriptionid = serial.subscriptionid
1901 AND (serial.STATUS IN (4, 41, 42, 43, 44) OR ((planneddate < now() AND serial.STATUS =1) OR serial.STATUS = 3 OR serial.STATUS = 7))
1902 AND subscription.aqbooksellerid=$supplierid
1907 $sth = $dbh->prepare(
1909 serialid, aqbooksellerid, name,
1910 biblio.title, planneddate, serialseq,
1911 serial.status, serial.subscriptionid, claimdate, claims_count,
1912 subscription.branchcode
1914 LEFT JOIN subscription ON serial.subscriptionid=subscription.subscriptionid
1915 LEFT JOIN biblio ON subscription.biblionumber=biblio.biblionumber
1916 LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id
1917 WHERE subscription.subscriptionid = serial.subscriptionid
1918 AND (serial.STATUS IN (4, 41, 42, 43, 44) OR ((planneddate < now() AND serial.STATUS =1) OR serial.STATUS = 3 OR serial.STATUS = 7))
1925 while ( my $line = $sth->fetchrow_hashref ) {
1927 if ($line->{planneddate} && $line->{planneddate} !~/^0+\-/) {
1928 $line->{planneddateISO} = $line->{planneddate};
1929 $line->{planneddate} = format_date( $line->{planneddate} );
1931 if ($line->{claimdate} && $line->{claimdate} !~/^0+\-/) {
1932 $line->{claimdateISO} = $line->{claimdate};
1933 $line->{claimdate} = format_date( $line->{claimdate} );
1935 $line->{"status".$line->{status}} = 1;
1936 push @issuelist, $line;
1941 =head2 removeMissingIssue
1943 removeMissingIssue($subscriptionid)
1945 this function removes an issue from being part of the missing string in
1946 subscriptionlist.missinglist column
1948 called when a missing issue is found from the serials-recieve.pl file
1952 sub removeMissingIssue {
1953 my ( $sequence, $subscriptionid ) = @_;
1955 return unless ($sequence and $subscriptionid);
1957 my $dbh = C4::Context->dbh;
1958 my $sth = $dbh->prepare("SELECT * FROM subscriptionhistory WHERE subscriptionid = ?");
1959 $sth->execute($subscriptionid);
1960 my $data = $sth->fetchrow_hashref;
1961 my $missinglist = $data->{'missinglist'};
1962 my $missinglistbefore = $missinglist;
1964 # warn $missinglist." before";
1965 $missinglist =~ s/($sequence)//;
1967 # warn $missinglist." after";
1968 if ( $missinglist ne $missinglistbefore ) {
1969 $missinglist =~ s/\|\s\|/\|/g;
1970 $missinglist =~ s/^\| //g;
1971 $missinglist =~ s/\|$//g;
1972 my $sth2 = $dbh->prepare(
1973 "UPDATE subscriptionhistory
1975 WHERE subscriptionid = ?"
1977 $sth2->execute( $missinglist, $subscriptionid );
1984 &updateClaim($serialid)
1986 this function updates the time when a claim is issued for late/missing items
1988 called from claims.pl file
1993 my ($serialid) = @_;
1994 my $dbh = C4::Context->dbh;
1997 SET claimdate = NOW(),
1998 claims_count = claims_count + 1
2004 =head2 getsupplierbyserialid
2006 $result = getsupplierbyserialid($serialid)
2008 this function is used to find the supplier id given a serial id
2011 hashref containing serialid, subscriptionid, and aqbooksellerid
2015 sub getsupplierbyserialid {
2016 my ($serialid) = @_;
2017 my $dbh = C4::Context->dbh;
2018 my $sth = $dbh->prepare(
2019 "SELECT serialid, serial.subscriptionid, aqbooksellerid
2021 LEFT JOIN subscription ON serial.subscriptionid = subscription.subscriptionid
2025 $sth->execute($serialid);
2026 my $line = $sth->fetchrow_hashref;
2027 my $result = $line->{'aqbooksellerid'};
2031 =head2 check_routing
2033 $result = &check_routing($subscriptionid)
2035 this function checks to see if a serial has a routing list and returns the count of routingid
2036 used to show either an 'add' or 'edit' link
2041 my ($subscriptionid) = @_;
2043 return unless ($subscriptionid);
2045 my $dbh = C4::Context->dbh;
2046 my $sth = $dbh->prepare(
2047 "SELECT count(routingid) routingids FROM subscription LEFT JOIN subscriptionroutinglist
2048 ON subscription.subscriptionid = subscriptionroutinglist.subscriptionid
2049 WHERE subscription.subscriptionid = ? ORDER BY ranking ASC
2052 $sth->execute($subscriptionid);
2053 my $line = $sth->fetchrow_hashref;
2054 my $result = $line->{'routingids'};
2058 =head2 addroutingmember
2060 addroutingmember($borrowernumber,$subscriptionid)
2062 this function takes a borrowernumber and subscriptionid and adds the member to the
2063 routing list for that serial subscription and gives them a rank on the list
2064 of either 1 or highest current rank + 1
2068 sub addroutingmember {
2069 my ( $borrowernumber, $subscriptionid ) = @_;
2071 return unless ($borrowernumber and $subscriptionid);
2074 my $dbh = C4::Context->dbh;
2075 my $sth = $dbh->prepare( "SELECT max(ranking) rank FROM subscriptionroutinglist WHERE subscriptionid = ?" );
2076 $sth->execute($subscriptionid);
2077 while ( my $line = $sth->fetchrow_hashref ) {
2078 if ( $line->{'rank'} > 0 ) {
2079 $rank = $line->{'rank'} + 1;
2084 $sth = $dbh->prepare( "INSERT INTO subscriptionroutinglist (subscriptionid,borrowernumber,ranking) VALUES (?,?,?)" );
2085 $sth->execute( $subscriptionid, $borrowernumber, $rank );
2088 =head2 reorder_members
2090 reorder_members($subscriptionid,$routingid,$rank)
2092 this function is used to reorder the routing list
2094 it takes the routingid of the member one wants to re-rank and the rank it is to move to
2095 - it gets all members on list puts their routingid's into an array
2096 - removes the one in the array that is $routingid
2097 - then reinjects $routingid at point indicated by $rank
2098 - then update the database with the routingids in the new order
2102 sub reorder_members {
2103 my ( $subscriptionid, $routingid, $rank ) = @_;
2104 my $dbh = C4::Context->dbh;
2105 my $sth = $dbh->prepare( "SELECT * FROM subscriptionroutinglist WHERE subscriptionid = ? ORDER BY ranking ASC" );
2106 $sth->execute($subscriptionid);
2108 while ( my $line = $sth->fetchrow_hashref ) {
2109 push( @result, $line->{'routingid'} );
2112 # To find the matching index
2114 my $key = -1; # to allow for 0 being a valid response
2115 for ( $i = 0 ; $i < @result ; $i++ ) {
2116 if ( $routingid == $result[$i] ) {
2117 $key = $i; # save the index
2122 # if index exists in array then move it to new position
2123 if ( $key > -1 && $rank > 0 ) {
2124 my $new_rank = $rank - 1; # $new_rank is what you want the new index to be in the array
2125 my $moving_item = splice( @result, $key, 1 );
2126 splice( @result, $new_rank, 0, $moving_item );
2128 for ( my $j = 0 ; $j < @result ; $j++ ) {
2129 my $sth = $dbh->prepare( "UPDATE subscriptionroutinglist SET ranking = '" . ( $j + 1 ) . "' WHERE routingid = '" . $result[$j] . "'" );
2135 =head2 delroutingmember
2137 delroutingmember($routingid,$subscriptionid)
2139 this function either deletes one member from routing list if $routingid exists otherwise
2140 deletes all members from the routing list
2144 sub delroutingmember {
2146 # if $routingid exists then deletes that row otherwise deletes all with $subscriptionid
2147 my ( $routingid, $subscriptionid ) = @_;
2148 my $dbh = C4::Context->dbh;
2150 my $sth = $dbh->prepare("DELETE FROM subscriptionroutinglist WHERE routingid = ?");
2151 $sth->execute($routingid);
2152 reorder_members( $subscriptionid, $routingid );
2154 my $sth = $dbh->prepare("DELETE FROM subscriptionroutinglist WHERE subscriptionid = ?");
2155 $sth->execute($subscriptionid);
2160 =head2 getroutinglist
2162 @routinglist = getroutinglist($subscriptionid)
2164 this gets the info from the subscriptionroutinglist for $subscriptionid
2167 the routinglist as an array. Each element of the array contains a hash_ref containing
2168 routingid - a unique id, borrowernumber, ranking, and biblionumber of subscription
2172 sub getroutinglist {
2173 my ($subscriptionid) = @_;
2174 my $dbh = C4::Context->dbh;
2175 my $sth = $dbh->prepare(
2176 'SELECT routingid, borrowernumber, ranking, biblionumber
2178 JOIN subscriptionroutinglist ON subscription.subscriptionid = subscriptionroutinglist.subscriptionid
2179 WHERE subscription.subscriptionid = ? ORDER BY ranking ASC'
2181 $sth->execute($subscriptionid);
2182 my $routinglist = $sth->fetchall_arrayref({});
2183 return @{$routinglist};
2186 =head2 countissuesfrom
2188 $result = countissuesfrom($subscriptionid,$startdate)
2190 Returns a count of serial rows matching the given subsctiptionid
2191 with published date greater than startdate
2195 sub countissuesfrom {
2196 my ( $subscriptionid, $startdate ) = @_;
2197 my $dbh = C4::Context->dbh;
2201 WHERE subscriptionid=?
2202 AND serial.publisheddate>?
2204 my $sth = $dbh->prepare($query);
2205 $sth->execute( $subscriptionid, $startdate );
2206 my ($countreceived) = $sth->fetchrow;
2207 return $countreceived;
2212 $result = CountIssues($subscriptionid)
2214 Returns a count of serial rows matching the given subsctiptionid
2219 my ($subscriptionid) = @_;
2220 my $dbh = C4::Context->dbh;
2224 WHERE subscriptionid=?
2226 my $sth = $dbh->prepare($query);
2227 $sth->execute($subscriptionid);
2228 my ($countreceived) = $sth->fetchrow;
2229 return $countreceived;
2234 $result = HasItems($subscriptionid)
2236 returns a count of items from serial matching the subscriptionid
2241 my ($subscriptionid) = @_;
2242 my $dbh = C4::Context->dbh;
2244 SELECT COUNT(serialitems.itemnumber)
2246 LEFT JOIN serialitems USING(serialid)
2247 WHERE subscriptionid=? AND serialitems.serialid IS NOT NULL
2249 my $sth=$dbh->prepare($query);
2250 $sth->execute($subscriptionid);
2251 my ($countitems)=$sth->fetchrow_array();
2255 =head2 abouttoexpire
2257 $result = abouttoexpire($subscriptionid)
2259 this function alerts you to the penultimate issue for a serial subscription
2261 returns 1 - if this is the penultimate issue
2267 my ($subscriptionid) = @_;
2268 my $dbh = C4::Context->dbh;
2269 my $subscription = GetSubscription($subscriptionid);
2270 my $per = $subscription->{'periodicity'};
2271 my $frequency = C4::Serials::Frequency::GetSubscriptionFrequency($per);
2272 if ($frequency and $frequency->{unit}){
2274 my $expirationdate = GetExpirationDate($subscriptionid);
2276 my ($res) = $dbh->selectrow_array('select max(planneddate) from serial where subscriptionid = ?', undef, $subscriptionid);
2277 my $nextdate = GetNextDate($subscription, $res);
2279 # only compare dates if both dates exist.
2280 if ($nextdate and $expirationdate) {
2281 if(Date::Calc::Delta_Days(
2282 split( /-/, $nextdate ),
2283 split( /-/, $expirationdate )
2289 } elsif ($subscription->{numberlength}>0) {
2290 return (countissuesfrom($subscriptionid,$subscription->{'startdate'}) >=$subscription->{numberlength}-1);
2296 sub in_array { # used in next sub down
2297 my ( $val, @elements ) = @_;
2298 foreach my $elem (@elements) {
2299 if ( $val == $elem ) {
2306 =head2 GetSubscriptionsFromBorrower
2308 ($count,@routinglist) = GetSubscriptionsFromBorrower($borrowernumber)
2310 this gets the info from subscriptionroutinglist for each $subscriptionid
2313 a count of the serial subscription routing lists to which a patron belongs,
2314 with the titles of those serial subscriptions as an array. Each element of the array
2315 contains a hash_ref with subscriptionID and title of subscription.
2319 sub GetSubscriptionsFromBorrower {
2320 my ($borrowernumber) = @_;
2321 my $dbh = C4::Context->dbh;
2322 my $sth = $dbh->prepare(
2323 "SELECT subscription.subscriptionid, biblio.title
2325 JOIN biblio ON biblio.biblionumber = subscription.biblionumber
2326 JOIN subscriptionroutinglist USING (subscriptionid)
2327 WHERE subscriptionroutinglist.borrowernumber = ? ORDER BY title ASC
2330 $sth->execute($borrowernumber);
2333 while ( my $line = $sth->fetchrow_hashref ) {
2335 push( @routinglist, $line );
2337 return ( $count, @routinglist );
2341 =head2 GetFictiveIssueNumber
2343 $issueno = GetFictiveIssueNumber($subscription, $publishedate);
2345 Get the position of the issue published at $publisheddate, considering the
2346 first issue (at firstacquidate) is at position 1, the next is at position 2, etc...
2347 This issuenumber doesn't take into account irregularities, so, for instance, if the 3rd
2348 issue is declared as 'irregular' (will be skipped at receipt), the next issue number
2349 will be 4, not 3. It's why it is called 'fictive'. It is NOT a serial seq, and is not
2350 depending on how many rows are in serial table.
2351 The issue number calculation is based on subscription frequency, first acquisition
2352 date, and $publisheddate.
2356 sub GetFictiveIssueNumber {
2357 my ($subscription, $publisheddate) = @_;
2359 my $frequency = GetSubscriptionFrequency($subscription->{'periodicity'});
2360 my $unit = $frequency->{unit} ? lc $frequency->{'unit'} : undef;
2364 my ($year, $month, $day) = split /-/, $publisheddate;
2365 my ($fa_year, $fa_month, $fa_day) = split /-/, $subscription->{'firstacquidate'};
2369 if($unit eq 'day') {
2370 $delta = Delta_Days($fa_year, $fa_month, $fa_day, $year, $month, $day);
2371 } elsif($unit eq 'week') {
2372 ($wkno, $year) = Week_of_Year($year, $month, $day);
2373 my ($fa_wkno, $fa_yr) = Week_of_Year($fa_year, $fa_month, $fa_day);
2374 $delta = ($fa_yr == $year) ? ($wkno - $fa_wkno) : ( ($year-$fa_yr-1)*52 + (52-$fa_wkno+$wkno) );
2375 } elsif($unit eq 'month') {
2376 $delta = ($fa_year == $year)
2377 ? ($month - $fa_month)
2378 : ( ($year-$fa_year-1)*12 + (12-$fa_month+$month) );
2379 } elsif($unit eq 'year') {
2380 $delta = $year - $fa_year;
2382 if($frequency->{'unitsperissue'} == 1) {
2383 $issueno = $delta * $frequency->{'issuesperunit'} + $subscription->{'countissuesperunit'};
2385 # Assuming issuesperunit == 1
2386 $issueno = int( ($delta + $frequency->{'unitsperissue'}) / $frequency->{'unitsperissue'} );
2394 $resultdate = GetNextDate($publisheddate,$subscription)
2396 this function it takes the publisheddate and will return the next issue's date
2397 and will skip dates if there exists an irregularity.
2398 $publisheddate has to be an ISO date
2399 $subscription is a hashref containing at least 'periodicity', 'firstacquidate', 'irregularity', and 'countissuesperunit'
2400 $updatecount is a boolean value which, when set to true, update the 'countissuesperunit' in database
2401 - eg if periodicity is monthly and $publisheddate is 2007-02-10 but if March and April is to be
2402 skipped then the returned date will be 2007-05-10
2405 $resultdate - then next date in the sequence (ISO date)
2407 Return undef if subscription is irregular
2412 my ( $subscription, $publisheddate, $updatecount ) = @_;
2414 return unless $subscription and $publisheddate;
2416 my $freqdata = GetSubscriptionFrequency($subscription->{'periodicity'});
2418 if ($freqdata->{'unit'}) {
2419 my ( $year, $month, $day ) = split /-/, $publisheddate;
2421 # Process an irregularity Hash
2422 # Suppose that irregularities are stored in a string with this structure
2423 # irreg1;irreg2;irreg3
2424 # where irregX is the number of issue which will not be received
2425 # (the first issue takes the number 1, the 2nd the number 2 and so on)
2427 if ( $subscription->{irregularity} ) {
2428 my @irreg = split /;/, $subscription->{'irregularity'} ;
2429 foreach my $irregularity (@irreg) {
2430 $irregularities{$irregularity} = 1;
2434 # Get the 'fictive' next issue number
2435 # It is used to check if next issue is an irregular issue.
2436 my $issueno = GetFictiveIssueNumber($subscription, $publisheddate) + 1;
2438 # Then get the next date
2439 my $unit = lc $freqdata->{'unit'};
2440 if ($unit eq 'day') {
2441 while ($irregularities{$issueno}) {
2442 if ($subscription->{'countissuesperunit'} + 1 > $freqdata->{'issuesperunit'}){
2443 ($year,$month,$day) = Add_Delta_Days($year,$month, $day , $freqdata->{'unitsperissue'} );
2444 $subscription->{'countissuesperunit'} = 1;
2446 $subscription->{'countissuesperunit'}++;
2450 if ($subscription->{'countissuesperunit'} + 1 > $freqdata->{'issuesperunit'}){
2451 ($year,$month,$day) = Add_Delta_Days($year,$month, $day , $freqdata->{"unitsperissue"} );
2452 $subscription->{'countissuesperunit'} = 1;
2454 $subscription->{'countissuesperunit'}++;
2457 elsif ($unit eq 'week') {
2458 my ($wkno, $yr) = Week_of_Year($year, $month, $day);
2459 while ($irregularities{$issueno}) {
2460 if ($subscription->{'countissuesperunit'} + 1 > $freqdata->{'issuesperunit'}){
2461 $subscription->{'countissuesperunit'} = 1;
2462 $wkno += $freqdata->{"unitsperissue"};
2467 my $dow = Day_of_Week($year, $month, $day);
2468 ($year,$month,$day) = Monday_of_Week($wkno, $yr);
2469 if($freqdata->{'issuesperunit'} == 1) {
2470 ($year, $month, $day) = Add_Delta_Days($year, $month, $day, $dow - 1);
2473 $subscription->{'countissuesperunit'}++;
2477 if ($subscription->{'countissuesperunit'} + 1 > $freqdata->{'issuesperunit'}){
2478 $subscription->{'countissuesperunit'} = 1;
2479 $wkno += $freqdata->{"unitsperissue"};
2481 $wkno = $wkno % 52 ;
2484 my $dow = Day_of_Week($year, $month, $day);
2485 ($year,$month,$day) = Monday_of_Week($wkno, $yr);
2486 if($freqdata->{'issuesperunit'} == 1) {
2487 ($year, $month, $day) = Add_Delta_Days($year, $month, $day, $dow - 1);
2490 $subscription->{'countissuesperunit'}++;
2493 elsif ($unit eq 'month') {
2494 while ($irregularities{$issueno}) {
2495 if ($subscription->{'countissuesperunit'} + 1 > $freqdata->{'issuesperunit'}){
2496 $subscription->{'countissuesperunit'} = 1;
2497 ($year,$month,$day) = Add_Delta_YM($year,$month,$day, 0,$freqdata->{"unitsperissue"});
2498 unless($freqdata->{'issuesperunit'} == 1) {
2499 $day = 1; # Jumping to the first day of month, because we don't know what day is expected
2502 $subscription->{'countissuesperunit'}++;
2506 if ($subscription->{'countissuesperunit'} + 1 > $freqdata->{'issuesperunit'}){
2507 $subscription->{'countissuesperunit'} = 1;
2508 ($year,$month,$day) = Add_Delta_YM($year,$month,$day, 0,$freqdata->{"unitsperissue"});
2509 unless($freqdata->{'issuesperunit'} == 1) {
2510 $day = 1; # Jumping to the first day of month, because we don't know what day is expected
2513 $subscription->{'countissuesperunit'}++;
2516 elsif ($unit eq 'year') {
2517 while ($irregularities{$issueno}) {
2518 if ($subscription->{'countissuesperunit'} + 1 > $freqdata->{'issuesperunit'}){
2519 $subscription->{'countissuesperunit'} = 1;
2520 ($year,$month,$day) = Add_Delta_YM($year,$month,$day, $freqdata->{"unitsperissue"},0);
2521 unless($freqdata->{'issuesperunit'} == 1) {
2522 # Jumping to the first day of year, because we don't know what day is expected
2527 $subscription->{'countissuesperunit'}++;
2531 if ($subscription->{'countissuesperunit'} + 1 > $freqdata->{'issuesperunit'}){
2532 $subscription->{'countissuesperunit'} = 1;
2533 ($year,$month,$day) = Add_Delta_YM($year,$month,$day, $freqdata->{"unitsperissue"},0);
2534 unless($freqdata->{'issuesperunit'} == 1) {
2535 # Jumping to the first day of year, because we don't know what day is expected
2540 $subscription->{'countissuesperunit'}++;
2544 my $dbh = C4::Context->dbh;
2547 SET countissuesperunit = ?
2548 WHERE subscriptionid = ?
2550 my $sth = $dbh->prepare($query);
2551 $sth->execute($subscription->{'countissuesperunit'}, $subscription->{'subscriptionid'});
2553 return sprintf("%04d-%02d-%02d", $year, $month, $day);
2559 $string = &_numeration($value,$num_type,$locale);
2561 _numeration returns the string corresponding to $value in the num_type
2571 my ($value, $num_type, $locale) = @_;
2576 if ( $num_type =~ /^dayname$/ ) {
2577 # 1970-11-01 was a Sunday
2578 $value = $value % 7;
2579 my $dt = DateTime->new(
2585 $string = $dt->strftime("%A");
2586 } elsif ( $num_type =~ /^monthname$/ ) {
2587 $value = $value % 12;
2588 my $dt = DateTime->new(
2590 month => $value + 1,
2593 $string = $dt->strftime("%B");
2594 } elsif ( $num_type =~ /^season$/ ) {
2595 my @seasons= qw( Spring Summer Fall Winter );
2596 $value = $value % 4;
2597 $string = $seasons[$value];
2605 =head2 is_barcode_in_use
2607 Returns number of occurence of the barcode in the items table
2608 Can be used as a boolean test of whether the barcode has
2609 been deployed as yet
2613 sub is_barcode_in_use {
2614 my $barcode = shift;
2615 my $dbh = C4::Context->dbh;
2616 my $occurences = $dbh->selectall_arrayref(
2617 'SELECT itemnumber from items where barcode = ?',
2622 return @{$occurences};
2625 =head2 CloseSubscription
2626 Close a subscription given a subscriptionid
2628 sub CloseSubscription {
2629 my ( $subscriptionid ) = @_;
2630 return unless $subscriptionid;
2631 my $dbh = C4::Context->dbh;
2632 my $sth = $dbh->prepare( qq{
2635 WHERE subscriptionid = ?
2637 $sth->execute( $subscriptionid );
2639 # Set status = missing when status = stopped
2640 $sth = $dbh->prepare( qq{
2643 WHERE subscriptionid = ?
2646 $sth->execute( $subscriptionid );
2649 =head2 ReopenSubscription
2650 Reopen a subscription given a subscriptionid
2652 sub ReopenSubscription {
2653 my ( $subscriptionid ) = @_;
2654 return unless $subscriptionid;
2655 my $dbh = C4::Context->dbh;
2656 my $sth = $dbh->prepare( qq{
2659 WHERE subscriptionid = ?
2661 $sth->execute( $subscriptionid );
2663 # Set status = expected when status = stopped
2664 $sth = $dbh->prepare( qq{
2667 WHERE subscriptionid = ?
2670 $sth->execute( $subscriptionid );
2673 =head2 subscriptionCurrentlyOnOrder
2675 $bool = subscriptionCurrentlyOnOrder( $subscriptionid );
2677 Return 1 if subscription is currently on order else 0.
2681 sub subscriptionCurrentlyOnOrder {
2682 my ( $subscriptionid ) = @_;
2683 my $dbh = C4::Context->dbh;
2685 SELECT COUNT(*) FROM aqorders
2686 WHERE subscriptionid = ?
2687 AND datereceived IS NULL
2688 AND datecancellationprinted IS NULL
2690 my $sth = $dbh->prepare( $query );
2691 $sth->execute($subscriptionid);
2692 return $sth->fetchrow_array;
2695 =head2 can_edit_subscription
2697 $can = can_edit_subscription( $subscriptionid[, $userid] );
2699 Return 1 if the subscription can be edited by the current logged user (or a given $userid), else 0.
2703 sub can_edit_subscription {
2704 my ( $subscription, $userid ) = @_;
2705 return _can_do_on_subscription( $subscription, $userid, 'edit_subscription' );
2708 =head2 can_show_subscription
2710 $can = can_show_subscription( $subscriptionid[, $userid] );
2712 Return 1 if the subscription can be shown by the current logged user (or a given $userid), else 0.
2716 sub can_show_subscription {
2717 my ( $subscription, $userid ) = @_;
2718 return _can_do_on_subscription( $subscription, $userid, '*' );
2721 sub _can_do_on_subscription {
2722 my ( $subscription, $userid, $permission ) = @_;
2723 return 0 unless C4::Context->userenv;
2724 my $flags = C4::Context->userenv->{flags};
2725 $userid ||= C4::Context->userenv->{'id'};
2727 if ( C4::Context->preference('IndependentBranches') ) {
2729 if C4::Context->IsSuperLibrarian()
2731 C4::Auth::haspermission( $userid, { serials => 'superserials' } )
2733 C4::Auth::haspermission( $userid,
2734 { serials => $permission } )
2735 and ( not defined $subscription->{branchcode}
2736 or $subscription->{branchcode} eq ''
2737 or $subscription->{branchcode} eq
2738 C4::Context->userenv->{'branch'} )
2743 if C4::Context->IsSuperLibrarian()
2745 C4::Auth::haspermission( $userid, { serials => 'superserials' } )
2746 or C4::Auth::haspermission(
2747 $userid, { serials => $permission }
2759 Koha Development Team <http://koha-community.org/>