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);
26 use Date::Calc qw(:all);
27 use POSIX qw(strftime);
29 use C4::Log; # logaction
31 use C4::Serials::Frequency;
32 use C4::Serials::Numberpattern;
33 use Koha::AdditionalField;
36 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
44 MISSING_NEVER_RECIEVED => 41,
45 MISSING_SOLD_OUT => 42,
46 MISSING_DAMAGED => 43,
54 use constant MISSING_STATUSES => (
55 MISSING, MISSING_NEVER_RECIEVED,
56 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 &GetSerials
73 &GetLatestSerials &ModSerialStatus &GetNextDate &GetSerials2
74 &ReNewSubscription &GetLateOrMissingIssues
75 &GetSerialInformation &AddItem2Serial
76 &PrepareSerialsData &GetNextExpected &ModNextExpected
78 &GetSuppliersWithLateIssues &getsupplierbyserialid
79 &GetDistributedTo &SetDistributedTo
80 &getroutinglist &delroutingmember &addroutingmember
82 &check_routing &updateClaim
85 &GetSubscriptionsFromBorrower
86 &subscriptionCurrentlyOnOrder
93 C4::Serials - Serials Module Functions
101 Functions for handling subscriptions, claims routing etc.
106 =head2 GetSuppliersWithLateIssues
108 $supplierlist = GetSuppliersWithLateIssues()
110 this function get all suppliers with late issues.
113 an array_ref of suppliers each entry is a hash_ref containing id and name
114 the array is in name order
118 sub GetSuppliersWithLateIssues {
119 my $dbh = C4::Context->dbh;
120 my $statuses = join(',', ( LATE, MISSING_STATUSES, CLAIMED ) );
122 SELECT DISTINCT id, name
124 LEFT JOIN serial ON serial.subscriptionid=subscription.subscriptionid
125 LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id
128 (planneddate < now() AND serial.status=1)
129 OR serial.STATUS IN ( $statuses )
131 AND subscription.closed = 0
133 return $dbh->selectall_arrayref($query, { Slice => {} });
136 =head2 GetSubscriptionHistoryFromSubscriptionId
138 $history = GetSubscriptionHistoryFromSubscriptionId($subscriptionid);
140 This function returns the subscription history as a hashref
144 sub GetSubscriptionHistoryFromSubscriptionId {
145 my ($subscriptionid) = @_;
147 return unless $subscriptionid;
149 my $dbh = C4::Context->dbh;
152 FROM subscriptionhistory
153 WHERE subscriptionid = ?
155 my $sth = $dbh->prepare($query);
156 $sth->execute($subscriptionid);
157 my $results = $sth->fetchrow_hashref;
163 =head2 GetSerialStatusFromSerialId
165 $sth = GetSerialStatusFromSerialId();
166 this function returns a statement handle
167 After this function, don't forget to execute it by using $sth->execute($serialid)
169 $sth = $dbh->prepare($query).
173 sub GetSerialStatusFromSerialId {
174 my $dbh = C4::Context->dbh;
180 return $dbh->prepare($query);
183 =head2 GetSerialInformation
186 $data = GetSerialInformation($serialid);
187 returns a hash_ref containing :
188 items : items marcrecord (can be an array)
190 subscription table field
191 + information about subscription expiration
195 sub GetSerialInformation {
197 my $dbh = C4::Context->dbh;
199 SELECT serial.*, serial.notes as sernotes, serial.status as serstatus,subscription.*,subscription.subscriptionid as subsid
200 FROM serial LEFT JOIN subscription ON subscription.subscriptionid=serial.subscriptionid
203 my $rq = $dbh->prepare($query);
204 $rq->execute($serialid);
205 my $data = $rq->fetchrow_hashref;
207 # create item information if we have serialsadditems for this subscription
208 if ( $data->{'serialsadditems'} ) {
209 my $queryitem = $dbh->prepare("SELECT itemnumber from serialitems where serialid=?");
210 $queryitem->execute($serialid);
211 my $itemnumbers = $queryitem->fetchall_arrayref( [0] );
213 if ( scalar(@$itemnumbers) > 0 ) {
214 foreach my $itemnum (@$itemnumbers) {
216 #It is ASSUMED that GetMarcItem ALWAYS WORK...
217 #Maybe GetMarcItem should return values on failure
218 $debug and warn "itemnumber :$itemnum->[0], bibnum :" . $data->{'biblionumber'};
219 my $itemprocessed = C4::Items::PrepareItemrecordDisplay( $data->{'biblionumber'}, $itemnum->[0], $data );
220 $itemprocessed->{'itemnumber'} = $itemnum->[0];
221 $itemprocessed->{'itemid'} = $itemnum->[0];
222 $itemprocessed->{'serialid'} = $serialid;
223 $itemprocessed->{'biblionumber'} = $data->{'biblionumber'};
224 push @{ $data->{'items'} }, $itemprocessed;
227 my $itemprocessed = C4::Items::PrepareItemrecordDisplay( $data->{'biblionumber'}, '', $data );
228 $itemprocessed->{'itemid'} = "N$serialid";
229 $itemprocessed->{'serialid'} = $serialid;
230 $itemprocessed->{'biblionumber'} = $data->{'biblionumber'};
231 $itemprocessed->{'countitems'} = 0;
232 push @{ $data->{'items'} }, $itemprocessed;
235 $data->{ "status" . $data->{'serstatus'} } = 1;
236 $data->{'subscriptionexpired'} = HasSubscriptionExpired( $data->{'subscriptionid'} ) && $data->{'status'} == 1;
237 $data->{'abouttoexpire'} = abouttoexpire( $data->{'subscriptionid'} );
238 $data->{cannotedit} = not can_edit_subscription( $data );
242 =head2 AddItem2Serial
244 $rows = AddItem2Serial($serialid,$itemnumber);
245 Adds an itemnumber to Serial record
246 returns the number of rows affected
251 my ( $serialid, $itemnumber ) = @_;
253 return unless ($serialid and $itemnumber);
255 my $dbh = C4::Context->dbh;
256 my $rq = $dbh->prepare("INSERT INTO `serialitems` SET serialid=? , itemnumber=?");
257 $rq->execute( $serialid, $itemnumber );
261 =head2 GetSubscription
263 $subs = GetSubscription($subscriptionid)
264 this function returns the subscription which has $subscriptionid as id.
266 a hashref. This hash containts
267 subscription, subscriptionhistory, aqbooksellers.name, biblio.title
271 sub GetSubscription {
272 my ($subscriptionid) = @_;
273 my $dbh = C4::Context->dbh;
275 SELECT subscription.*,
276 subscriptionhistory.*,
277 aqbooksellers.name AS aqbooksellername,
278 biblio.title AS bibliotitle,
279 subscription.biblionumber as bibnum
281 LEFT JOIN subscriptionhistory ON subscription.subscriptionid=subscriptionhistory.subscriptionid
282 LEFT JOIN aqbooksellers ON subscription.aqbooksellerid=aqbooksellers.id
283 LEFT JOIN biblio ON biblio.biblionumber=subscription.biblionumber
284 WHERE subscription.subscriptionid = ?
287 $debug and warn "query : $query\nsubsid :$subscriptionid";
288 my $sth = $dbh->prepare($query);
289 $sth->execute($subscriptionid);
290 my $subscription = $sth->fetchrow_hashref;
292 $subscription->{cannotedit} = not can_edit_subscription( $subscription );
294 # Add additional fields to the subscription into a new key "additional_fields"
295 my $additional_field_values = Koha::AdditionalField->fetch_all_values({
296 tablename => 'subscription',
297 record_id => $subscriptionid,
299 $subscription->{additional_fields} = $additional_field_values->{$subscriptionid};
301 return $subscription;
304 =head2 GetFullSubscription
306 $array_ref = GetFullSubscription($subscriptionid)
307 this function reads the serial table.
311 sub GetFullSubscription {
312 my ($subscriptionid) = @_;
314 return unless ($subscriptionid);
316 my $dbh = C4::Context->dbh;
318 SELECT serial.serialid,
321 serial.publisheddate,
322 serial.publisheddatetext,
324 serial.notes as notes,
325 year(IF(serial.publisheddate="00-00-0000",serial.planneddate,serial.publisheddate)) as year,
326 aqbooksellers.name as aqbooksellername,
327 biblio.title as bibliotitle,
328 subscription.branchcode AS branchcode,
329 subscription.subscriptionid AS subscriptionid
331 LEFT JOIN subscription ON
332 (serial.subscriptionid=subscription.subscriptionid )
333 LEFT JOIN aqbooksellers on subscription.aqbooksellerid=aqbooksellers.id
334 LEFT JOIN biblio on biblio.biblionumber=subscription.biblionumber
335 WHERE serial.subscriptionid = ?
337 IF(serial.publisheddate="00-00-0000",serial.planneddate,serial.publisheddate) DESC,
338 serial.subscriptionid
340 $debug and warn "GetFullSubscription query: $query";
341 my $sth = $dbh->prepare($query);
342 $sth->execute($subscriptionid);
343 my $subscriptions = $sth->fetchall_arrayref( {} );
344 for my $subscription ( @$subscriptions ) {
345 $subscription->{cannotedit} = not can_edit_subscription( $subscription );
347 return $subscriptions;
350 =head2 PrepareSerialsData
352 $array_ref = PrepareSerialsData($serialinfomation)
353 where serialinformation is a hashref array
357 sub PrepareSerialsData {
360 return unless ($lines);
366 my $aqbooksellername;
370 my $previousnote = "";
372 foreach my $subs (@{$lines}) {
373 for my $datefield ( qw(publisheddate planneddate) ) {
374 # handle 0000-00-00 dates
375 if (defined $subs->{$datefield} and $subs->{$datefield} =~ m/^00/) {
376 $subs->{$datefield} = undef;
379 $subs->{ "status" . $subs->{'status'} } = 1;
380 if ( grep { $_ == $subs->{status} } ( EXPECTED, LATE, MISSING_STATUSES, CLAIMED ) ) {
381 $subs->{"checked"} = 1;
384 if ( $subs->{'year'} && $subs->{'year'} ne "" ) {
385 $year = $subs->{'year'};
389 if ( $tmpresults{$year} ) {
390 push @{ $tmpresults{$year}->{'serials'} }, $subs;
392 $tmpresults{$year} = {
394 'aqbooksellername' => $subs->{'aqbooksellername'},
395 'bibliotitle' => $subs->{'bibliotitle'},
396 'serials' => [$subs],
401 foreach my $key ( sort { $b cmp $a } keys %tmpresults ) {
402 push @res, $tmpresults{$key};
407 =head2 GetSubscriptionsFromBiblionumber
409 $array_ref = GetSubscriptionsFromBiblionumber($biblionumber)
410 this function get the subscription list. it reads the subscription table.
412 reference to an array of subscriptions which have the biblionumber given on input arg.
413 each element of this array is a hashref containing
414 startdate, histstartdate,opacnote,missinglist,recievedlist,periodicity,status & enddate
418 sub GetSubscriptionsFromBiblionumber {
419 my ($biblionumber) = @_;
421 return unless ($biblionumber);
423 my $dbh = C4::Context->dbh;
425 SELECT subscription.*,
427 subscriptionhistory.*,
428 aqbooksellers.name AS aqbooksellername,
429 biblio.title AS bibliotitle
431 LEFT JOIN subscriptionhistory ON subscription.subscriptionid=subscriptionhistory.subscriptionid
432 LEFT JOIN aqbooksellers ON subscription.aqbooksellerid=aqbooksellers.id
433 LEFT JOIN biblio ON biblio.biblionumber=subscription.biblionumber
434 LEFT JOIN branches ON branches.branchcode=subscription.branchcode
435 WHERE subscription.biblionumber = ?
437 my $sth = $dbh->prepare($query);
438 $sth->execute($biblionumber);
440 while ( my $subs = $sth->fetchrow_hashref ) {
441 $subs->{startdate} = output_pref( { dt => dt_from_string( $subs->{startdate} ), dateonly => 1 } );
442 $subs->{histstartdate} = output_pref( { dt => dt_from_string( $subs->{histstartdate} ), dateonly => 1 } );
443 $subs->{histenddate} = output_pref( { dt => dt_from_string( $subs->{histenddate} ), dateonly => 1 } );
444 $subs->{opacnote} =~ s/\n/\<br\/\>/g;
445 $subs->{missinglist} =~ s/\n/\<br\/\>/g;
446 $subs->{recievedlist} =~ s/\n/\<br\/\>/g;
447 $subs->{ "periodicity" . $subs->{periodicity} } = 1;
448 $subs->{ "numberpattern" . $subs->{numberpattern} } = 1;
449 $subs->{ "status" . $subs->{'status'} } = 1;
451 if ( $subs->{enddate} eq '0000-00-00' ) {
452 $subs->{enddate} = '';
454 $subs->{enddate} = output_pref( { dt => dt_from_string( $subs->{enddate}), dateonly => 1 } );
456 $subs->{'abouttoexpire'} = abouttoexpire( $subs->{'subscriptionid'} );
457 $subs->{'subscriptionexpired'} = HasSubscriptionExpired( $subs->{'subscriptionid'} );
458 $subs->{cannotedit} = not can_edit_subscription( $subs );
464 =head2 GetFullSubscriptionsFromBiblionumber
466 $array_ref = GetFullSubscriptionsFromBiblionumber($biblionumber)
467 this function reads the serial table.
471 sub GetFullSubscriptionsFromBiblionumber {
472 my ($biblionumber) = @_;
473 my $dbh = C4::Context->dbh;
475 SELECT serial.serialid,
478 serial.publisheddate,
479 serial.publisheddatetext,
481 serial.notes as notes,
482 year(IF(serial.publisheddate="00-00-0000",serial.planneddate,serial.publisheddate)) as year,
483 biblio.title as bibliotitle,
484 subscription.branchcode AS branchcode,
485 subscription.subscriptionid AS subscriptionid
487 LEFT JOIN subscription ON
488 (serial.subscriptionid=subscription.subscriptionid)
489 LEFT JOIN aqbooksellers on subscription.aqbooksellerid=aqbooksellers.id
490 LEFT JOIN biblio on biblio.biblionumber=subscription.biblionumber
491 WHERE subscription.biblionumber = ?
493 IF(serial.publisheddate="00-00-0000",serial.planneddate,serial.publisheddate) DESC,
494 serial.subscriptionid
496 my $sth = $dbh->prepare($query);
497 $sth->execute($biblionumber);
498 my $subscriptions = $sth->fetchall_arrayref( {} );
499 for my $subscription ( @$subscriptions ) {
500 $subscription->{cannotedit} = not can_edit_subscription( $subscription );
502 return $subscriptions;
505 =head2 SearchSubscriptions
507 @results = SearchSubscriptions($args);
509 This function returns a list of hashrefs, one for each subscription
510 that meets the conditions specified by the $args hashref.
512 The valid search fields are:
526 The expiration_date search field is special; it specifies the maximum
527 subscription expiration date.
531 sub SearchSubscriptions {
534 my $additional_fields = $args->{additional_fields} // [];
535 my $matching_record_ids_for_additional_fields = [];
536 if ( @$additional_fields ) {
537 $matching_record_ids_for_additional_fields = Koha::AdditionalField->get_matching_record_ids({
538 fields => $additional_fields,
539 tablename => 'subscription',
542 return () unless @$matching_record_ids_for_additional_fields;
547 subscription.notes AS publicnotes,
548 subscriptionhistory.*,
550 biblio.notes AS biblionotes,
556 LEFT JOIN subscriptionhistory USING(subscriptionid)
557 LEFT JOIN biblio ON biblio.biblionumber = subscription.biblionumber
558 LEFT JOIN biblioitems ON biblioitems.biblionumber = subscription.biblionumber
559 LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id
561 $query .= q| WHERE 1|;
564 if( $args->{biblionumber} ) {
565 push @where_strs, "biblio.biblionumber = ?";
566 push @where_args, $args->{biblionumber};
569 if( $args->{title} ){
570 my @words = split / /, $args->{title};
572 foreach my $word (@words) {
573 push @strs, "biblio.title LIKE ?";
574 push @args, "%$word%";
577 push @where_strs, '(' . join (' AND ', @strs) . ')';
578 push @where_args, @args;
582 push @where_strs, "biblioitems.issn LIKE ?";
583 push @where_args, "%$args->{issn}%";
586 push @where_strs, "biblioitems.ean LIKE ?";
587 push @where_args, "%$args->{ean}%";
589 if ( $args->{callnumber} ) {
590 push @where_strs, "subscription.callnumber LIKE ?";
591 push @where_args, "%$args->{callnumber}%";
593 if( $args->{publisher} ){
594 push @where_strs, "biblioitems.publishercode LIKE ?";
595 push @where_args, "%$args->{publisher}%";
597 if( $args->{bookseller} ){
598 push @where_strs, "aqbooksellers.name LIKE ?";
599 push @where_args, "%$args->{bookseller}%";
601 if( $args->{branch} ){
602 push @where_strs, "subscription.branchcode = ?";
603 push @where_args, "$args->{branch}";
605 if ( $args->{location} ) {
606 push @where_strs, "subscription.location = ?";
607 push @where_args, "$args->{location}";
609 if ( $args->{expiration_date} ) {
610 push @where_strs, "subscription.enddate <= ?";
611 push @where_args, "$args->{expiration_date}";
613 if( defined $args->{closed} ){
614 push @where_strs, "subscription.closed = ?";
615 push @where_args, "$args->{closed}";
619 $query .= ' AND ' . join(' AND ', @where_strs);
621 if ( @$additional_fields ) {
622 $query .= ' AND subscriptionid IN ('
623 . join( ', ', @$matching_record_ids_for_additional_fields )
627 $query .= " ORDER BY " . $args->{orderby} if $args->{orderby};
629 my $dbh = C4::Context->dbh;
630 my $sth = $dbh->prepare($query);
631 $sth->execute(@where_args);
632 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 );
638 my $additional_field_values = Koha::AdditionalField->fetch_all_values({
639 record_id => $subscription->{subscriptionid},
640 tablename => 'subscription'
642 $subscription->{additional_fields} = $additional_field_values->{$subscription->{subscriptionid}};
651 ($totalissues,@serials) = GetSerials($subscriptionid);
652 this function gets every serial not arrived for a given subscription
653 as well as the number of issues registered in the database (all types)
654 this number is used to see if a subscription can be deleted (=it must have only 1 issue)
656 FIXME: We should return \@serials.
661 my ( $subscriptionid, $count ) = @_;
663 return unless $subscriptionid;
665 my $dbh = C4::Context->dbh;
667 # status = 2 is "arrived"
669 $count = 5 unless ($count);
671 my $statuses = join( ',', ( ARRIVED, MISSING_STATUSES, NOT_ISSUED ) );
672 my $query = "SELECT serialid,serialseq, status, publisheddate,
673 publisheddatetext, planneddate,notes, routingnotes
675 WHERE subscriptionid = ? AND status NOT IN ( $statuses )
676 ORDER BY IF(publisheddate<>'0000-00-00',publisheddate,planneddate) DESC";
677 my $sth = $dbh->prepare($query);
678 $sth->execute($subscriptionid);
680 while ( my $line = $sth->fetchrow_hashref ) {
681 $line->{ "status" . $line->{status} } = 1; # fills a "statusX" value, used for template status select list
682 for my $datefield ( qw( planneddate publisheddate) ) {
683 if ($line->{$datefield} && $line->{$datefield}!~m/^00/) {
684 $line->{$datefield} = output_pref( { dt => dt_from_string( $line->{$datefield} ), dateonly => 1 } );
686 $line->{$datefield} = q{};
689 push @serials, $line;
692 # OK, now add the last 5 issues arrives/missing
693 $query = "SELECT serialid,serialseq, status, planneddate, publisheddate,
694 publisheddatetext, notes, routingnotes
696 WHERE subscriptionid = ?
697 AND status IN ( $statuses )
698 ORDER BY IF(publisheddate<>'0000-00-00',publisheddate,planneddate) DESC
700 $sth = $dbh->prepare($query);
701 $sth->execute($subscriptionid);
702 while ( ( my $line = $sth->fetchrow_hashref ) && $counter < $count ) {
704 $line->{ "status" . $line->{status} } = 1; # fills a "statusX" value, used for template status select list
705 for my $datefield ( qw( planneddate publisheddate) ) {
706 if ($line->{$datefield} && $line->{$datefield}!~m/^00/) {
707 $line->{$datefield} = output_pref( { dt => dt_from_string( $line->{$datefield} ), dateonly => 1 } );
709 $line->{$datefield} = q{};
713 push @serials, $line;
716 $query = "SELECT count(*) FROM serial WHERE subscriptionid=?";
717 $sth = $dbh->prepare($query);
718 $sth->execute($subscriptionid);
719 my ($totalissues) = $sth->fetchrow;
720 return ( $totalissues, @serials );
725 @serials = GetSerials2($subscriptionid,$statuses);
726 this function returns every serial waited for a given subscription
727 as well as the number of issues registered in the database (all types)
728 this number is used to see if a subscription can be deleted (=it must have only 1 issue)
730 $statuses is an arrayref of statuses and is mandatory.
735 my ( $subscription, $statuses ) = @_;
737 return unless ($subscription and @$statuses);
739 my $statuses_string = join ',', @$statuses;
741 my $dbh = C4::Context->dbh;
743 SELECT serialid,serialseq, status, planneddate, publisheddate,
744 publisheddatetext, notes, routingnotes
746 WHERE subscriptionid=$subscription AND status IN ($statuses_string)
747 ORDER BY publisheddate,serialid DESC
749 $debug and warn "GetSerials2 query: $query";
750 my $sth = $dbh->prepare($query);
754 while ( my $line = $sth->fetchrow_hashref ) {
755 $line->{ "status" . $line->{status} } = 1; # fills a "statusX" value, used for template status select list
756 # Format dates for display
757 for my $datefield ( qw( planneddate publisheddate ) ) {
758 if (!defined($line->{$datefield}) || $line->{$datefield} =~m/^00/) {
759 $line->{$datefield} = q{};
762 $line->{$datefield} = output_pref( { dt => dt_from_string( $line->{$datefield} ), dateonly => 1 } );
765 push @serials, $line;
770 =head2 GetLatestSerials
772 \@serials = GetLatestSerials($subscriptionid,$limit)
773 get the $limit's latest serials arrived or missing for a given subscription
775 a ref to an array which contains all of the latest serials stored into a hash.
779 sub GetLatestSerials {
780 my ( $subscriptionid, $limit ) = @_;
782 return unless ($subscriptionid and $limit);
784 my $dbh = C4::Context->dbh;
786 my $statuses = join( ',', ( ARRIVED, MISSING_STATUSES ) );
787 my $strsth = "SELECT serialid,serialseq, status, planneddate, publisheddate, notes
789 WHERE subscriptionid = ?
790 AND status IN ($statuses)
791 ORDER BY publisheddate DESC LIMIT 0,$limit
793 my $sth = $dbh->prepare($strsth);
794 $sth->execute($subscriptionid);
796 while ( my $line = $sth->fetchrow_hashref ) {
797 $line->{ "status" . $line->{status} } = 1; # fills a "statusX" value, used for template status select list
798 $line->{planneddate} = output_pref( { dt => dt_from_string( $line->{planneddate} ), dateonly => 1 } );
799 $line->{publisheddate} = output_pref( { dt => dt_from_string( $line->{publisheddate} ), dateonly => 1 } );
800 push @serials, $line;
806 =head2 GetDistributedTo
808 $distributedto=GetDistributedTo($subscriptionid)
809 This function returns the field distributedto for the subscription matching subscriptionid
813 sub GetDistributedTo {
814 my $dbh = C4::Context->dbh;
816 my ($subscriptionid) = @_;
818 return unless ($subscriptionid);
820 my $query = "SELECT distributedto FROM subscription WHERE subscriptionid=?";
821 my $sth = $dbh->prepare($query);
822 $sth->execute($subscriptionid);
823 return ($distributedto) = $sth->fetchrow;
829 $nextseq, $newlastvalue1, $newlastvalue2, $newlastvalue3,
830 $newinnerloop1, $newinnerloop2, $newinnerloop3
831 ) = GetNextSeq( $subscription, $pattern, $planneddate );
833 $subscription is a hashref containing all the attributes of the table
835 $pattern is a hashref containing all the attributes of the table
836 'subscription_numberpatterns'.
837 $planneddate is a date string in iso format.
838 This function get the next issue for the subscription given on input arg
843 my ($subscription, $pattern, $planneddate) = @_;
845 return unless ($subscription and $pattern);
847 my ( $newlastvalue1, $newlastvalue2, $newlastvalue3,
848 $newinnerloop1, $newinnerloop2, $newinnerloop3 );
851 if ($subscription->{'skip_serialseq'}) {
852 my @irreg = split /;/, $subscription->{'irregularity'};
854 my $irregularities = {};
855 $irregularities->{$_} = 1 foreach(@irreg);
856 my $issueno = GetFictiveIssueNumber($subscription, $planneddate) + 1;
857 while($irregularities->{$issueno}) {
864 my $numberingmethod = $pattern->{numberingmethod};
866 if ($numberingmethod) {
867 $calculated = $numberingmethod;
868 my $locale = $subscription->{locale};
869 $newlastvalue1 = $subscription->{lastvalue1} || 0;
870 $newlastvalue2 = $subscription->{lastvalue2} || 0;
871 $newlastvalue3 = $subscription->{lastvalue3} || 0;
872 $newinnerloop1 = $subscription->{innerloop1} || 0;
873 $newinnerloop2 = $subscription->{innerloop2} || 0;
874 $newinnerloop3 = $subscription->{innerloop3} || 0;
877 $calc{$_} = 1 if ($numberingmethod =~ /\{$_\}/);
880 for(my $i = 0; $i < $count; $i++) {
882 # check if we have to increase the new value.
884 if ($newinnerloop1 >= $pattern->{every1}) {
886 $newlastvalue1 += $pattern->{add1};
888 # reset counter if needed.
889 $newlastvalue1 = $pattern->{setto1} if ($newlastvalue1 > $pattern->{whenmorethan1});
892 # check if we have to increase the new value.
894 if ($newinnerloop2 >= $pattern->{every2}) {
896 $newlastvalue2 += $pattern->{add2};
898 # reset counter if needed.
899 $newlastvalue2 = $pattern->{setto2} if ($newlastvalue2 > $pattern->{whenmorethan2});
902 # check if we have to increase the new value.
904 if ($newinnerloop3 >= $pattern->{every3}) {
906 $newlastvalue3 += $pattern->{add3};
908 # reset counter if needed.
909 $newlastvalue3 = $pattern->{setto3} if ($newlastvalue3 > $pattern->{whenmorethan3});
913 my $newlastvalue1string = _numeration( $newlastvalue1, $pattern->{numbering1}, $locale );
914 $calculated =~ s/\{X\}/$newlastvalue1string/g;
917 my $newlastvalue2string = _numeration( $newlastvalue2, $pattern->{numbering2}, $locale );
918 $calculated =~ s/\{Y\}/$newlastvalue2string/g;
921 my $newlastvalue3string = _numeration( $newlastvalue3, $pattern->{numbering3}, $locale );
922 $calculated =~ s/\{Z\}/$newlastvalue3string/g;
927 $newlastvalue1, $newlastvalue2, $newlastvalue3,
928 $newinnerloop1, $newinnerloop2, $newinnerloop3);
933 $calculated = GetSeq($subscription, $pattern)
934 $subscription is a hashref containing all the attributes of the table 'subscription'
935 $pattern is a hashref containing all the attributes of the table 'subscription_numberpatterns'
936 this function transforms {X},{Y},{Z} to 150,0,0 for example.
938 the sequence in string format
943 my ($subscription, $pattern) = @_;
945 return unless ($subscription and $pattern);
947 my $locale = $subscription->{locale};
949 my $calculated = $pattern->{numberingmethod};
951 my $newlastvalue1 = $subscription->{'lastvalue1'} || 0;
952 $newlastvalue1 = _numeration($newlastvalue1, $pattern->{numbering1}, $locale) if ($pattern->{numbering1}); # reset counter if needed.
953 $calculated =~ s/\{X\}/$newlastvalue1/g;
955 my $newlastvalue2 = $subscription->{'lastvalue2'} || 0;
956 $newlastvalue2 = _numeration($newlastvalue2, $pattern->{numbering2}, $locale) if ($pattern->{numbering2}); # reset counter if needed.
957 $calculated =~ s/\{Y\}/$newlastvalue2/g;
959 my $newlastvalue3 = $subscription->{'lastvalue3'} || 0;
960 $newlastvalue3 = _numeration($newlastvalue3, $pattern->{numbering3}, $locale) if ($pattern->{numbering3}); # reset counter if needed.
961 $calculated =~ s/\{Z\}/$newlastvalue3/g;
965 =head2 GetExpirationDate
967 $enddate = GetExpirationDate($subscriptionid, [$startdate])
969 this function return the next expiration date for a subscription given on input args.
976 sub GetExpirationDate {
977 my ( $subscriptionid, $startdate ) = @_;
979 return unless ($subscriptionid);
981 my $dbh = C4::Context->dbh;
982 my $subscription = GetSubscription($subscriptionid);
985 # we don't do the same test if the subscription is based on X numbers or on X weeks/months
986 $enddate = $startdate || $subscription->{startdate};
987 my @date = split( /-/, $enddate );
989 return if ( scalar(@date) != 3 || not check_date(@date) );
991 my $frequency = C4::Serials::Frequency::GetSubscriptionFrequency($subscription->{periodicity});
992 if ( $frequency and $frequency->{unit} ) {
995 if ( my $length = $subscription->{numberlength} ) {
997 #calculate the date of the last issue.
998 for ( my $i = 1 ; $i <= $length ; $i++ ) {
999 $enddate = GetNextDate( $subscription, $enddate );
1001 } elsif ( $subscription->{monthlength} ) {
1002 if ( $$subscription{startdate} ) {
1003 my @enddate = Add_Delta_YM( $date[0], $date[1], $date[2], 0, $subscription->{monthlength} );
1004 $enddate = sprintf( "%04d-%02d-%02d", $enddate[0], $enddate[1], $enddate[2] );
1006 } elsif ( $subscription->{weeklength} ) {
1007 if ( $$subscription{startdate} ) {
1008 my @date = split( /-/, $subscription->{startdate} );
1009 my @enddate = Add_Delta_Days( $date[0], $date[1], $date[2], $subscription->{weeklength} * 7 );
1010 $enddate = sprintf( "%04d-%02d-%02d", $enddate[0], $enddate[1], $enddate[2] );
1013 $enddate = $subscription->{enddate};
1017 return $subscription->{enddate};
1021 =head2 CountSubscriptionFromBiblionumber
1023 $subscriptionsnumber = CountSubscriptionFromBiblionumber($biblionumber)
1024 this returns a count of the subscriptions for a given biblionumber
1026 the number of subscriptions
1030 sub CountSubscriptionFromBiblionumber {
1031 my ($biblionumber) = @_;
1033 return unless ($biblionumber);
1035 my $dbh = C4::Context->dbh;
1036 my $query = "SELECT count(*) FROM subscription WHERE biblionumber=?";
1037 my $sth = $dbh->prepare($query);
1038 $sth->execute($biblionumber);
1039 my $subscriptionsnumber = $sth->fetchrow;
1040 return $subscriptionsnumber;
1043 =head2 ModSubscriptionHistory
1045 ModSubscriptionHistory($subscriptionid,$histstartdate,$enddate,$recievedlist,$missinglist,$opacnote,$librariannote);
1047 this function modifies the history of a subscription. Put your new values on input arg.
1048 returns the number of rows affected
1052 sub ModSubscriptionHistory {
1053 my ( $subscriptionid, $histstartdate, $enddate, $receivedlist, $missinglist, $opacnote, $librariannote ) = @_;
1055 return unless ($subscriptionid);
1057 my $dbh = C4::Context->dbh;
1058 my $query = "UPDATE subscriptionhistory
1059 SET histstartdate=?,histenddate=?,recievedlist=?,missinglist=?,opacnote=?,librariannote=?
1060 WHERE subscriptionid=?
1062 my $sth = $dbh->prepare($query);
1063 $receivedlist =~ s/^; // if $receivedlist;
1064 $missinglist =~ s/^; // if $missinglist;
1065 $opacnote =~ s/^; // if $opacnote;
1066 $sth->execute( $histstartdate, $enddate, $receivedlist, $missinglist, $opacnote, $librariannote, $subscriptionid );
1070 =head2 ModSerialStatus
1072 ModSerialStatus($serialid, $serialseq, $planneddate, $publisheddate,
1073 $publisheddatetext, $status, $notes);
1075 This function modify the serial status. Serial status is a number.(eg 2 is "arrived")
1076 Note : if we change from "waited" to something else,then we will have to create a new "waited" entry
1080 sub ModSerialStatus {
1081 my ($serialid, $serialseq, $planneddate, $publisheddate, $publisheddatetext,
1082 $status, $notes) = @_;
1084 return unless ($serialid);
1086 #It is a usual serial
1087 # 1st, get previous status :
1088 my $dbh = C4::Context->dbh;
1089 my $query = "SELECT serial.subscriptionid,serial.status,subscription.periodicity
1090 FROM serial, subscription
1091 WHERE serial.subscriptionid=subscription.subscriptionid
1093 my $sth = $dbh->prepare($query);
1094 $sth->execute($serialid);
1095 my ( $subscriptionid, $oldstatus, $periodicity ) = $sth->fetchrow;
1096 my $frequency = GetSubscriptionFrequency($periodicity);
1098 # change status & update subscriptionhistory
1100 if ( $status == DELETED ) {
1101 DelIssue( { 'serialid' => $serialid, 'subscriptionid' => $subscriptionid, 'serialseq' => $serialseq } );
1106 SET serialseq = ?, publisheddate = ?, publisheddatetext = ?,
1107 planneddate = ?, status = ?, notes = ?
1110 $sth = $dbh->prepare($query);
1111 $sth->execute( $serialseq, $publisheddate, $publisheddatetext,
1112 $planneddate, $status, $notes, $serialid );
1113 $query = "SELECT * FROM subscription WHERE subscriptionid = ?";
1114 $sth = $dbh->prepare($query);
1115 $sth->execute($subscriptionid);
1116 my $val = $sth->fetchrow_hashref;
1117 unless ( $val->{manualhistory} ) {
1118 $query = "SELECT missinglist,recievedlist FROM subscriptionhistory WHERE subscriptionid=?";
1119 $sth = $dbh->prepare($query);
1120 $sth->execute($subscriptionid);
1121 my ( $missinglist, $recievedlist ) = $sth->fetchrow;
1123 if ( $status == ARRIVED || ($oldstatus == ARRIVED && $status != ARRIVED) ) {
1124 $recievedlist .= "; $serialseq"
1125 if ($recievedlist !~ /(^|;)\s*$serialseq(?=;|$)/);
1128 # in case serial has been previously marked as missing
1129 if (grep /$status/, (EXPECTED, ARRIVED, LATE, CLAIMED)) {
1130 $missinglist=~ s/(^|;)\s*$serialseq(?=;|$)//g;
1133 $missinglist .= "; $serialseq"
1134 if ( ( grep { $_ == $status } ( MISSING_STATUSES ) ) && ( $missinglist !~/(^|;)\s*$serialseq(?=;|$)/ ) );
1135 $missinglist .= "; not issued $serialseq"
1136 if ( $status == NOT_ISSUED && $missinglist !~ /(^|;)\s*$serialseq(?=;|$)/ );
1138 $query = "UPDATE subscriptionhistory SET recievedlist=?, missinglist=? WHERE subscriptionid=?";
1139 $sth = $dbh->prepare($query);
1140 $recievedlist =~ s/^; //;
1141 $missinglist =~ s/^; //;
1142 $sth->execute( $recievedlist, $missinglist, $subscriptionid );
1146 # create new expected entry if needed (ie : was "expected" and has changed)
1147 my $otherIssueExpected = scalar findSerialsByStatus(EXPECTED, $subscriptionid);
1148 if ( !$otherIssueExpected && $oldstatus == EXPECTED && $status != EXPECTED ) {
1149 my $subscription = GetSubscription($subscriptionid);
1150 my $pattern = C4::Serials::Numberpattern::GetSubscriptionNumberpattern($subscription->{numberpattern});
1154 $newserialseq, $newlastvalue1, $newlastvalue2, $newlastvalue3,
1155 $newinnerloop1, $newinnerloop2, $newinnerloop3
1157 = GetNextSeq( $subscription, $pattern, $publisheddate );
1159 # next date (calculated from actual date & frequency parameters)
1160 my $nextpublisheddate = GetNextDate($subscription, $publisheddate, 1);
1161 my $nextpubdate = $nextpublisheddate;
1162 NewIssue( $newserialseq, $subscriptionid, $subscription->{'biblionumber'}, 1, $nextpubdate, $nextpubdate );
1163 $query = "UPDATE subscription SET lastvalue1=?, lastvalue2=?, lastvalue3=?, innerloop1=?, innerloop2=?, innerloop3=?
1164 WHERE subscriptionid = ?";
1165 $sth = $dbh->prepare($query);
1166 $sth->execute( $newlastvalue1, $newlastvalue2, $newlastvalue3, $newinnerloop1, $newinnerloop2, $newinnerloop3, $subscriptionid );
1168 # check if an alert must be sent... (= a letter is defined & status became "arrived"
1169 if ( $subscription->{letter} && $status == ARRIVED && $oldstatus != ARRIVED ) {
1170 require C4::Letters;
1171 C4::Letters::SendAlerts( 'issue', $serialid, $subscription->{letter} );
1178 =head2 GetNextExpected
1180 $nextexpected = GetNextExpected($subscriptionid)
1182 Get the planneddate for the current expected issue of the subscription.
1188 planneddate => ISO date
1193 sub GetNextExpected {
1194 my ($subscriptionid) = @_;
1196 my $dbh = C4::Context->dbh;
1200 WHERE subscriptionid = ?
1204 my $sth = $dbh->prepare($query);
1206 # Each subscription has only one 'expected' issue.
1207 $sth->execute( $subscriptionid, EXPECTED );
1208 my $nextissue = $sth->fetchrow_hashref;
1209 if ( !$nextissue ) {
1213 WHERE subscriptionid = ?
1214 ORDER BY publisheddate DESC
1217 $sth = $dbh->prepare($query);
1218 $sth->execute($subscriptionid);
1219 $nextissue = $sth->fetchrow_hashref;
1221 foreach(qw/planneddate publisheddate/) {
1222 if ( !defined $nextissue->{$_} ) {
1223 # or should this default to 1st Jan ???
1224 $nextissue->{$_} = strftime( '%Y-%m-%d', localtime );
1226 $nextissue->{$_} = ($nextissue->{$_} ne '0000-00-00')
1234 =head2 ModNextExpected
1236 ModNextExpected($subscriptionid,$date)
1238 Update the planneddate for the current expected issue of the subscription.
1239 This will modify all future prediction results.
1241 C<$date> is an ISO date.
1247 sub ModNextExpected {
1248 my ( $subscriptionid, $date ) = @_;
1249 my $dbh = C4::Context->dbh;
1251 #FIXME: Would expect to only set planneddate, but we set both on new issue creation, so updating it here
1252 my $sth = $dbh->prepare('UPDATE serial SET planneddate=?,publisheddate=? WHERE subscriptionid=? AND status=?');
1254 # Each subscription has only one 'expected' issue.
1255 $sth->execute( $date, $date, $subscriptionid, EXPECTED );
1260 =head2 GetSubscriptionIrregularities
1264 =item @irreg = &GetSubscriptionIrregularities($subscriptionid);
1265 get the list of irregularities for a subscription
1271 sub GetSubscriptionIrregularities {
1272 my $subscriptionid = shift;
1274 return unless $subscriptionid;
1276 my $dbh = C4::Context->dbh;
1280 WHERE subscriptionid = ?
1282 my $sth = $dbh->prepare($query);
1283 $sth->execute($subscriptionid);
1285 my ($result) = $sth->fetchrow_array;
1286 my @irreg = split /;/, $result;
1291 =head2 ModSubscription
1293 this function modifies a subscription. Put all new values on input args.
1294 returns the number of rows affected
1298 sub ModSubscription {
1300 $auser, $branchcode, $aqbooksellerid, $cost, $aqbudgetid, $startdate,
1301 $periodicity, $firstacquidate, $irregularity, $numberpattern, $locale,
1302 $numberlength, $weeklength, $monthlength, $lastvalue1, $innerloop1,
1303 $lastvalue2, $innerloop2, $lastvalue3, $innerloop3, $status,
1304 $biblionumber, $callnumber, $notes, $letter, $manualhistory,
1305 $internalnotes, $serialsadditems, $staffdisplaycount, $opacdisplaycount,
1306 $graceperiod, $location, $enddate, $subscriptionid, $skip_serialseq
1309 my $dbh = C4::Context->dbh;
1310 my $query = "UPDATE subscription
1311 SET librarian=?, branchcode=?, aqbooksellerid=?, cost=?, aqbudgetid=?,
1312 startdate=?, periodicity=?, firstacquidate=?, irregularity=?,
1313 numberpattern=?, locale=?, numberlength=?, weeklength=?, monthlength=?,
1314 lastvalue1=?, innerloop1=?, lastvalue2=?, innerloop2=?,
1315 lastvalue3=?, innerloop3=?, status=?, biblionumber=?,
1316 callnumber=?, notes=?, letter=?, manualhistory=?,
1317 internalnotes=?, serialsadditems=?, staffdisplaycount=?,
1318 opacdisplaycount=?, graceperiod=?, location = ?, enddate=?,
1320 WHERE subscriptionid = ?";
1322 my $sth = $dbh->prepare($query);
1324 $auser, $branchcode, $aqbooksellerid, $cost,
1325 $aqbudgetid, $startdate, $periodicity, $firstacquidate,
1326 $irregularity, $numberpattern, $locale, $numberlength,
1327 $weeklength, $monthlength, $lastvalue1, $innerloop1,
1328 $lastvalue2, $innerloop2, $lastvalue3, $innerloop3,
1329 $status, $biblionumber, $callnumber, $notes,
1330 $letter, ($manualhistory ? $manualhistory : 0),
1331 $internalnotes, $serialsadditems, $staffdisplaycount, $opacdisplaycount,
1332 $graceperiod, $location, $enddate, $skip_serialseq,
1335 my $rows = $sth->rows;
1337 logaction( "SERIAL", "MODIFY", $subscriptionid, "" ) if C4::Context->preference("SubscriptionLog");
1341 =head2 NewSubscription
1343 $subscriptionid = &NewSubscription($auser,branchcode,$aqbooksellerid,$cost,$aqbudgetid,$biblionumber,
1344 $startdate,$periodicity,$numberlength,$weeklength,$monthlength,
1345 $lastvalue1,$innerloop1,$lastvalue2,$innerloop2,$lastvalue3,$innerloop3,
1346 $status, $notes, $letter, $firstacquidate, $irregularity, $numberpattern,
1347 $locale, $callnumber, $manualhistory, $internalnotes, $serialsadditems,
1348 $staffdisplaycount, $opacdisplaycount, $graceperiod, $location, $enddate, $skip_serialseq);
1350 Create a new subscription with value given on input args.
1353 the id of this new subscription
1357 sub NewSubscription {
1359 $auser, $branchcode, $aqbooksellerid, $cost, $aqbudgetid, $biblionumber,
1360 $startdate, $periodicity, $numberlength, $weeklength, $monthlength,
1361 $lastvalue1, $innerloop1, $lastvalue2, $innerloop2, $lastvalue3,
1362 $innerloop3, $status, $notes, $letter, $firstacquidate, $irregularity,
1363 $numberpattern, $locale, $callnumber, $manualhistory, $internalnotes,
1364 $serialsadditems, $staffdisplaycount, $opacdisplaycount, $graceperiod,
1365 $location, $enddate, $skip_serialseq
1367 my $dbh = C4::Context->dbh;
1369 #save subscription (insert into database)
1371 INSERT INTO subscription
1372 (librarian, branchcode, aqbooksellerid, cost, aqbudgetid,
1373 biblionumber, startdate, periodicity, numberlength, weeklength,
1374 monthlength, lastvalue1, innerloop1, lastvalue2, innerloop2,
1375 lastvalue3, innerloop3, status, notes, letter, firstacquidate,
1376 irregularity, numberpattern, locale, callnumber,
1377 manualhistory, internalnotes, serialsadditems, staffdisplaycount,
1378 opacdisplaycount, graceperiod, location, enddate, skip_serialseq)
1379 VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
1381 my $sth = $dbh->prepare($query);
1383 $auser, $branchcode, $aqbooksellerid, $cost, $aqbudgetid, $biblionumber,
1384 $startdate, $periodicity, $numberlength, $weeklength,
1385 $monthlength, $lastvalue1, $innerloop1, $lastvalue2, $innerloop2,
1386 $lastvalue3, $innerloop3, $status, $notes, $letter,
1387 $firstacquidate, $irregularity, $numberpattern, $locale, $callnumber,
1388 $manualhistory, $internalnotes, $serialsadditems, $staffdisplaycount,
1389 $opacdisplaycount, $graceperiod, $location, $enddate, $skip_serialseq
1392 my $subscriptionid = $dbh->{'mysql_insertid'};
1394 $enddate = GetExpirationDate( $subscriptionid, $startdate );
1398 WHERE subscriptionid=?
1400 $sth = $dbh->prepare($query);
1401 $sth->execute( $enddate, $subscriptionid );
1404 # then create the 1st expected number
1406 INSERT INTO subscriptionhistory
1407 (biblionumber, subscriptionid, histstartdate)
1410 $sth = $dbh->prepare($query);
1411 $sth->execute( $biblionumber, $subscriptionid, $startdate);
1413 # reread subscription to get a hash (for calculation of the 1st issue number)
1414 my $subscription = GetSubscription($subscriptionid);
1415 my $pattern = C4::Serials::Numberpattern::GetSubscriptionNumberpattern($subscription->{numberpattern});
1417 # calculate issue number
1418 my $serialseq = GetSeq($subscription, $pattern) || q{};
1421 (serialseq,subscriptionid,biblionumber,status, planneddate, publisheddate)
1422 VALUES (?,?,?,?,?,?)
1424 $sth = $dbh->prepare($query);
1425 $sth->execute( $serialseq, $subscriptionid, $biblionumber, EXPECTED, $firstacquidate, $firstacquidate );
1427 logaction( "SERIAL", "ADD", $subscriptionid, "" ) if C4::Context->preference("SubscriptionLog");
1429 #set serial flag on biblio if not already set.
1430 my $bib = GetBiblio($biblionumber);
1431 if ( $bib and !$bib->{'serial'} ) {
1432 my $record = GetMarcBiblio($biblionumber);
1433 my ( $tag, $subf ) = GetMarcFromKohaField( 'biblio.serial', $bib->{'frameworkcode'} );
1435 eval { $record->field($tag)->update( $subf => 1 ); };
1437 ModBiblio( $record, $biblionumber, $bib->{'frameworkcode'} );
1439 return $subscriptionid;
1442 =head2 ReNewSubscription
1444 ReNewSubscription($subscriptionid,$user,$startdate,$numberlength,$weeklength,$monthlength,$note)
1446 this function renew a subscription with values given on input args.
1450 sub ReNewSubscription {
1451 my ( $subscriptionid, $user, $startdate, $numberlength, $weeklength, $monthlength, $note ) = @_;
1452 my $dbh = C4::Context->dbh;
1453 my $subscription = GetSubscription($subscriptionid);
1457 LEFT JOIN biblioitems ON biblio.biblionumber=biblioitems.biblionumber
1458 WHERE biblio.biblionumber=?
1460 my $sth = $dbh->prepare($query);
1461 $sth->execute( $subscription->{biblionumber} );
1462 my $biblio = $sth->fetchrow_hashref;
1464 if ( C4::Context->preference("RenewSerialAddsSuggestion") ) {
1465 require C4::Suggestions;
1466 C4::Suggestions::NewSuggestion(
1467 { 'suggestedby' => $user,
1468 'title' => $subscription->{bibliotitle},
1469 'author' => $biblio->{author},
1470 'publishercode' => $biblio->{publishercode},
1471 'note' => $biblio->{note},
1472 'biblionumber' => $subscription->{biblionumber}
1477 # renew subscription
1480 SET startdate=?,numberlength=?,weeklength=?,monthlength=?,reneweddate=NOW()
1481 WHERE subscriptionid=?
1483 $sth = $dbh->prepare($query);
1484 $sth->execute( $startdate, $numberlength, $weeklength, $monthlength, $subscriptionid );
1485 my $enddate = GetExpirationDate($subscriptionid);
1486 $debug && warn "enddate :$enddate";
1490 WHERE subscriptionid=?
1492 $sth = $dbh->prepare($query);
1493 $sth->execute( $enddate, $subscriptionid );
1495 UPDATE subscriptionhistory
1497 WHERE subscriptionid=?
1499 $sth = $dbh->prepare($query);
1500 $sth->execute( $enddate, $subscriptionid );
1502 logaction( "SERIAL", "RENEW", $subscriptionid, "" ) if C4::Context->preference("SubscriptionLog");
1508 NewIssue($serialseq,$subscriptionid,$biblionumber,$status, $planneddate, $publisheddate, $notes)
1510 Create a new issue stored on the database.
1511 Note : we have to update the recievedlist and missinglist on subscriptionhistory for this subscription.
1512 returns the serial id
1517 my ( $serialseq, $subscriptionid, $biblionumber, $status, $planneddate,
1518 $publisheddate, $publisheddatetext, $notes ) = @_;
1519 ### FIXME biblionumber CAN be provided by subscriptionid. So Do we STILL NEED IT ?
1521 return unless ($subscriptionid);
1523 my $dbh = C4::Context->dbh;
1525 INSERT INTO serial (serialseq, subscriptionid, biblionumber, status,
1526 publisheddate, publisheddatetext, planneddate, notes)
1527 VALUES (?,?,?,?,?,?,?,?)
1529 my $sth = $dbh->prepare($query);
1530 $sth->execute( $serialseq, $subscriptionid, $biblionumber, $status,
1531 $publisheddate, $publisheddatetext, $planneddate, $notes );
1532 my $serialid = $dbh->{'mysql_insertid'};
1534 SELECT missinglist,recievedlist
1535 FROM subscriptionhistory
1536 WHERE subscriptionid=?
1538 $sth = $dbh->prepare($query);
1539 $sth->execute($subscriptionid);
1540 my ( $missinglist, $recievedlist ) = $sth->fetchrow;
1542 if ( $status == ARRIVED ) {
1543 ### TODO Add a feature that improves recognition and description.
1544 ### As such count (serialseq) i.e. : N18,2(N19),N20
1545 ### Would use substr and index But be careful to previous presence of ()
1546 $recievedlist .= "; $serialseq" unless (index($recievedlist,$serialseq)>0);
1548 if ( grep {/^$status$/} ( MISSING_STATUSES ) ) {
1549 $missinglist .= "; $serialseq" unless (index($missinglist,$serialseq)>0);
1552 UPDATE subscriptionhistory
1553 SET recievedlist=?, missinglist=?
1554 WHERE subscriptionid=?
1556 $sth = $dbh->prepare($query);
1557 $recievedlist =~ s/^; //;
1558 $missinglist =~ s/^; //;
1559 $sth->execute( $recievedlist, $missinglist, $subscriptionid );
1563 =head2 HasSubscriptionStrictlyExpired
1565 1 or 0 = HasSubscriptionStrictlyExpired($subscriptionid)
1567 the subscription has stricly expired when today > the end subscription date
1570 1 if true, 0 if false, -1 if the expiration date is not set.
1574 sub HasSubscriptionStrictlyExpired {
1576 # Getting end of subscription date
1577 my ($subscriptionid) = @_;
1579 return unless ($subscriptionid);
1581 my $dbh = C4::Context->dbh;
1582 my $subscription = GetSubscription($subscriptionid);
1583 my $expirationdate = $subscription->{enddate} || GetExpirationDate($subscriptionid);
1585 # If the expiration date is set
1586 if ( $expirationdate != 0 ) {
1587 my ( $endyear, $endmonth, $endday ) = split( '-', $expirationdate );
1589 # Getting today's date
1590 my ( $nowyear, $nowmonth, $nowday ) = Today();
1592 # if today's date > expiration date, then the subscription has stricly expired
1593 if ( Delta_Days( $nowyear, $nowmonth, $nowday, $endyear, $endmonth, $endday ) < 0 ) {
1600 # There are some cases where the expiration date is not set
1601 # As we can't determine if the subscription has expired on a date-basis,
1607 =head2 HasSubscriptionExpired
1609 $has_expired = HasSubscriptionExpired($subscriptionid)
1611 the subscription has expired when the next issue to arrive is out of subscription limit.
1614 0 if the subscription has not expired
1615 1 if the subscription has expired
1616 2 if has subscription does not have a valid expiration date set
1620 sub HasSubscriptionExpired {
1621 my ($subscriptionid) = @_;
1623 return unless ($subscriptionid);
1625 my $dbh = C4::Context->dbh;
1626 my $subscription = GetSubscription($subscriptionid);
1627 my $frequency = C4::Serials::Frequency::GetSubscriptionFrequency($subscription->{periodicity});
1628 if ( $frequency and $frequency->{unit} ) {
1629 my $expirationdate = $subscription->{enddate} || GetExpirationDate($subscriptionid);
1630 if (!defined $expirationdate) {
1631 $expirationdate = q{};
1634 SELECT max(planneddate)
1636 WHERE subscriptionid=?
1638 my $sth = $dbh->prepare($query);
1639 $sth->execute($subscriptionid);
1640 my ($res) = $sth->fetchrow;
1641 if (!$res || $res=~m/^0000/) {
1644 my @res = split( /-/, $res );
1645 my @endofsubscriptiondate = split( /-/, $expirationdate );
1646 return 2 if ( scalar(@res) != 3 || scalar(@endofsubscriptiondate) != 3 || not check_date(@res) || not check_date(@endofsubscriptiondate) );
1648 if ( ( @endofsubscriptiondate && Delta_Days( $res[0], $res[1], $res[2], $endofsubscriptiondate[0], $endofsubscriptiondate[1], $endofsubscriptiondate[2] ) <= 0 )
1653 if ( $subscription->{'numberlength'} ) {
1654 my $countreceived = countissuesfrom( $subscriptionid, $subscription->{'startdate'} );
1655 return 1 if ( $countreceived > $subscription->{'numberlength'} );
1661 return 0; # Notice that you'll never get here.
1664 =head2 SetDistributedto
1666 SetDistributedto($distributedto,$subscriptionid);
1667 This function update the value of distributedto for a subscription given on input arg.
1671 sub SetDistributedto {
1672 my ( $distributedto, $subscriptionid ) = @_;
1673 my $dbh = C4::Context->dbh;
1677 WHERE subscriptionid=?
1679 my $sth = $dbh->prepare($query);
1680 $sth->execute( $distributedto, $subscriptionid );
1684 =head2 DelSubscription
1686 DelSubscription($subscriptionid)
1687 this function deletes subscription which has $subscriptionid as id.
1691 sub DelSubscription {
1692 my ($subscriptionid) = @_;
1693 my $dbh = C4::Context->dbh;
1694 $dbh->do("DELETE FROM subscription WHERE subscriptionid=?", undef, $subscriptionid);
1695 $dbh->do("DELETE FROM subscriptionhistory WHERE subscriptionid=?", undef, $subscriptionid);
1696 $dbh->do("DELETE FROM serial WHERE subscriptionid=?", undef, $subscriptionid);
1698 my $afs = Koha::AdditionalField->all({tablename => 'subscription'});
1699 foreach my $af (@$afs) {
1700 $af->delete_values({record_id => $subscriptionid});
1703 logaction( "SERIAL", "DELETE", $subscriptionid, "" ) if C4::Context->preference("SubscriptionLog");
1708 DelIssue($serialseq,$subscriptionid)
1709 this function deletes an issue which has $serialseq and $subscriptionid given on input arg.
1711 returns the number of rows affected
1716 my ($dataissue) = @_;
1717 my $dbh = C4::Context->dbh;
1718 ### TODO Add itemdeletion. Would need to get itemnumbers. Should be in a pref ?
1723 AND subscriptionid= ?
1725 my $mainsth = $dbh->prepare($query);
1726 $mainsth->execute( $dataissue->{'serialid'}, $dataissue->{'subscriptionid'} );
1728 #Delete element from subscription history
1729 $query = "SELECT * FROM subscription WHERE subscriptionid = ?";
1730 my $sth = $dbh->prepare($query);
1731 $sth->execute( $dataissue->{'subscriptionid'} );
1732 my $val = $sth->fetchrow_hashref;
1733 unless ( $val->{manualhistory} ) {
1735 SELECT * FROM subscriptionhistory
1736 WHERE subscriptionid= ?
1738 my $sth = $dbh->prepare($query);
1739 $sth->execute( $dataissue->{'subscriptionid'} );
1740 my $data = $sth->fetchrow_hashref;
1741 my $serialseq = $dataissue->{'serialseq'};
1742 $data->{'missinglist'} =~ s/\b$serialseq\b//;
1743 $data->{'recievedlist'} =~ s/\b$serialseq\b//;
1744 my $strsth = "UPDATE subscriptionhistory SET " . join( ",", map { join( "=", $_, $dbh->quote( $data->{$_} ) ) } keys %$data ) . " WHERE subscriptionid=?";
1745 $sth = $dbh->prepare($strsth);
1746 $sth->execute( $dataissue->{'subscriptionid'} );
1749 return $mainsth->rows;
1752 =head2 GetLateOrMissingIssues
1754 @issuelist = GetLateMissingIssues($supplierid,$serialid)
1756 this function selects missing issues on database - where serial.status = MISSING* or serial.status = LATE or planneddate<now
1759 the issuelist as an array of hash refs. Each element of this array contains
1760 name,title,planneddate,serialseq,serial.subscriptionid from tables : subscription, serial & biblio
1764 sub GetLateOrMissingIssues {
1765 my ( $supplierid, $serialid, $order ) = @_;
1767 return unless ( $supplierid or $serialid );
1769 my $dbh = C4::Context->dbh;
1774 $byserial = "and serialid = " . $serialid;
1777 $order .= ", title";
1781 my $missing_statuses_string = join ',', (MISSING_STATUSES);
1783 $sth = $dbh->prepare(
1785 serialid, aqbooksellerid, name,
1786 biblio.title, biblioitems.issn, planneddate, serialseq,
1787 serial.status, serial.subscriptionid, claimdate, claims_count,
1788 subscription.branchcode
1790 LEFT JOIN subscription ON serial.subscriptionid=subscription.subscriptionid
1791 LEFT JOIN biblio ON subscription.biblionumber=biblio.biblionumber
1792 LEFT JOIN biblioitems ON subscription.biblionumber=biblioitems.biblionumber
1793 LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id
1794 WHERE subscription.subscriptionid = serial.subscriptionid
1795 AND (serial.STATUS IN ($missing_statuses_string) OR ((planneddate < now() AND serial.STATUS = ?) OR serial.STATUS = ? OR serial.STATUS = ?))
1796 AND subscription.aqbooksellerid=$supplierid
1801 $sth = $dbh->prepare(
1803 serialid, aqbooksellerid, name,
1804 biblio.title, planneddate, serialseq,
1805 serial.status, serial.subscriptionid, claimdate, claims_count,
1806 subscription.branchcode
1808 LEFT JOIN subscription ON serial.subscriptionid=subscription.subscriptionid
1809 LEFT JOIN biblio ON subscription.biblionumber=biblio.biblionumber
1810 LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id
1811 WHERE subscription.subscriptionid = serial.subscriptionid
1812 AND (serial.STATUS IN ($missing_statuses_string) OR ((planneddate < now() AND serial.STATUS = ?) OR serial.STATUS = ? OR serial.STATUS = ?))
1817 $sth->execute( EXPECTED, LATE, CLAIMED );
1819 while ( my $line = $sth->fetchrow_hashref ) {
1821 if ($line->{planneddate} && $line->{planneddate} !~/^0+\-/) {
1822 $line->{planneddateISO} = $line->{planneddate};
1823 $line->{planneddate} = output_pref( { dt => dt_from_string( $line->{"planneddate"} ), dateonly => 1 } );
1825 if ($line->{claimdate} && $line->{claimdate} !~/^0+\-/) {
1826 $line->{claimdateISO} = $line->{claimdate};
1827 $line->{claimdate} = output_pref( { dt => dt_from_string( $line->{"claimdate"} ), dateonly => 1 } );
1829 $line->{"status".$line->{status}} = 1;
1831 my $additional_field_values = Koha::AdditionalField->fetch_all_values({
1832 record_id => $line->{subscriptionid},
1833 tablename => 'subscription'
1835 %$line = ( %$line, additional_fields => $additional_field_values->{$line->{subscriptionid}} );
1837 push @issuelist, $line;
1844 &updateClaim($serialid)
1846 this function updates the time when a claim is issued for late/missing items
1848 called from claims.pl file
1853 my ($serialids) = @_;
1854 return unless $serialids;
1855 unless ( ref $serialids ) {
1856 $serialids = [ $serialids ];
1858 my $dbh = C4::Context->dbh;
1861 SET claimdate = NOW(),
1862 claims_count = claims_count + 1,
1864 WHERE serialid in (| . join( q|,|, (q|?|) x @$serialids ) . q|)|,
1865 {}, CLAIMED, @$serialids );
1868 =head2 getsupplierbyserialid
1870 $result = getsupplierbyserialid($serialid)
1872 this function is used to find the supplier id given a serial id
1875 hashref containing serialid, subscriptionid, and aqbooksellerid
1879 sub getsupplierbyserialid {
1880 my ($serialid) = @_;
1881 my $dbh = C4::Context->dbh;
1882 my $sth = $dbh->prepare(
1883 "SELECT serialid, serial.subscriptionid, aqbooksellerid
1885 LEFT JOIN subscription ON serial.subscriptionid = subscription.subscriptionid
1889 $sth->execute($serialid);
1890 my $line = $sth->fetchrow_hashref;
1891 my $result = $line->{'aqbooksellerid'};
1895 =head2 check_routing
1897 $result = &check_routing($subscriptionid)
1899 this function checks to see if a serial has a routing list and returns the count of routingid
1900 used to show either an 'add' or 'edit' link
1905 my ($subscriptionid) = @_;
1907 return unless ($subscriptionid);
1909 my $dbh = C4::Context->dbh;
1910 my $sth = $dbh->prepare(
1911 "SELECT count(routingid) routingids FROM subscription LEFT JOIN subscriptionroutinglist
1912 ON subscription.subscriptionid = subscriptionroutinglist.subscriptionid
1913 WHERE subscription.subscriptionid = ? ORDER BY ranking ASC
1916 $sth->execute($subscriptionid);
1917 my $line = $sth->fetchrow_hashref;
1918 my $result = $line->{'routingids'};
1922 =head2 addroutingmember
1924 addroutingmember($borrowernumber,$subscriptionid)
1926 this function takes a borrowernumber and subscriptionid and adds the member to the
1927 routing list for that serial subscription and gives them a rank on the list
1928 of either 1 or highest current rank + 1
1932 sub addroutingmember {
1933 my ( $borrowernumber, $subscriptionid ) = @_;
1935 return unless ($borrowernumber and $subscriptionid);
1938 my $dbh = C4::Context->dbh;
1939 my $sth = $dbh->prepare( "SELECT max(ranking) rank FROM subscriptionroutinglist WHERE subscriptionid = ?" );
1940 $sth->execute($subscriptionid);
1941 while ( my $line = $sth->fetchrow_hashref ) {
1942 if ( $line->{'rank'} > 0 ) {
1943 $rank = $line->{'rank'} + 1;
1948 $sth = $dbh->prepare( "INSERT INTO subscriptionroutinglist (subscriptionid,borrowernumber,ranking) VALUES (?,?,?)" );
1949 $sth->execute( $subscriptionid, $borrowernumber, $rank );
1952 =head2 reorder_members
1954 reorder_members($subscriptionid,$routingid,$rank)
1956 this function is used to reorder the routing list
1958 it takes the routingid of the member one wants to re-rank and the rank it is to move to
1959 - it gets all members on list puts their routingid's into an array
1960 - removes the one in the array that is $routingid
1961 - then reinjects $routingid at point indicated by $rank
1962 - then update the database with the routingids in the new order
1966 sub reorder_members {
1967 my ( $subscriptionid, $routingid, $rank ) = @_;
1968 my $dbh = C4::Context->dbh;
1969 my $sth = $dbh->prepare( "SELECT * FROM subscriptionroutinglist WHERE subscriptionid = ? ORDER BY ranking ASC" );
1970 $sth->execute($subscriptionid);
1972 while ( my $line = $sth->fetchrow_hashref ) {
1973 push( @result, $line->{'routingid'} );
1976 # To find the matching index
1978 my $key = -1; # to allow for 0 being a valid response
1979 for ( $i = 0 ; $i < @result ; $i++ ) {
1980 if ( $routingid == $result[$i] ) {
1981 $key = $i; # save the index
1986 # if index exists in array then move it to new position
1987 if ( $key > -1 && $rank > 0 ) {
1988 my $new_rank = $rank - 1; # $new_rank is what you want the new index to be in the array
1989 my $moving_item = splice( @result, $key, 1 );
1990 splice( @result, $new_rank, 0, $moving_item );
1992 for ( my $j = 0 ; $j < @result ; $j++ ) {
1993 my $sth = $dbh->prepare( "UPDATE subscriptionroutinglist SET ranking = '" . ( $j + 1 ) . "' WHERE routingid = '" . $result[$j] . "'" );
1999 =head2 delroutingmember
2001 delroutingmember($routingid,$subscriptionid)
2003 this function either deletes one member from routing list if $routingid exists otherwise
2004 deletes all members from the routing list
2008 sub delroutingmember {
2010 # if $routingid exists then deletes that row otherwise deletes all with $subscriptionid
2011 my ( $routingid, $subscriptionid ) = @_;
2012 my $dbh = C4::Context->dbh;
2014 my $sth = $dbh->prepare("DELETE FROM subscriptionroutinglist WHERE routingid = ?");
2015 $sth->execute($routingid);
2016 reorder_members( $subscriptionid, $routingid );
2018 my $sth = $dbh->prepare("DELETE FROM subscriptionroutinglist WHERE subscriptionid = ?");
2019 $sth->execute($subscriptionid);
2024 =head2 getroutinglist
2026 @routinglist = getroutinglist($subscriptionid)
2028 this gets the info from the subscriptionroutinglist for $subscriptionid
2031 the routinglist as an array. Each element of the array contains a hash_ref containing
2032 routingid - a unique id, borrowernumber, ranking, and biblionumber of subscription
2036 sub getroutinglist {
2037 my ($subscriptionid) = @_;
2038 my $dbh = C4::Context->dbh;
2039 my $sth = $dbh->prepare(
2040 'SELECT routingid, borrowernumber, ranking, biblionumber
2042 JOIN subscriptionroutinglist ON subscription.subscriptionid = subscriptionroutinglist.subscriptionid
2043 WHERE subscription.subscriptionid = ? ORDER BY ranking ASC'
2045 $sth->execute($subscriptionid);
2046 my $routinglist = $sth->fetchall_arrayref({});
2047 return @{$routinglist};
2050 =head2 countissuesfrom
2052 $result = countissuesfrom($subscriptionid,$startdate)
2054 Returns a count of serial rows matching the given subsctiptionid
2055 with published date greater than startdate
2059 sub countissuesfrom {
2060 my ( $subscriptionid, $startdate ) = @_;
2061 my $dbh = C4::Context->dbh;
2065 WHERE subscriptionid=?
2066 AND serial.publisheddate>?
2068 my $sth = $dbh->prepare($query);
2069 $sth->execute( $subscriptionid, $startdate );
2070 my ($countreceived) = $sth->fetchrow;
2071 return $countreceived;
2076 $result = CountIssues($subscriptionid)
2078 Returns a count of serial rows matching the given subsctiptionid
2083 my ($subscriptionid) = @_;
2084 my $dbh = C4::Context->dbh;
2088 WHERE subscriptionid=?
2090 my $sth = $dbh->prepare($query);
2091 $sth->execute($subscriptionid);
2092 my ($countreceived) = $sth->fetchrow;
2093 return $countreceived;
2098 $result = HasItems($subscriptionid)
2100 returns a count of items from serial matching the subscriptionid
2105 my ($subscriptionid) = @_;
2106 my $dbh = C4::Context->dbh;
2108 SELECT COUNT(serialitems.itemnumber)
2110 LEFT JOIN serialitems USING(serialid)
2111 WHERE subscriptionid=? AND serialitems.serialid IS NOT NULL
2113 my $sth=$dbh->prepare($query);
2114 $sth->execute($subscriptionid);
2115 my ($countitems)=$sth->fetchrow_array();
2119 =head2 abouttoexpire
2121 $result = abouttoexpire($subscriptionid)
2123 this function alerts you to the penultimate issue for a serial subscription
2125 returns 1 - if this is the penultimate issue
2131 my ($subscriptionid) = @_;
2132 my $dbh = C4::Context->dbh;
2133 my $subscription = GetSubscription($subscriptionid);
2134 my $per = $subscription->{'periodicity'};
2135 my $frequency = C4::Serials::Frequency::GetSubscriptionFrequency($per);
2136 if ($frequency and $frequency->{unit}){
2138 my $expirationdate = GetExpirationDate($subscriptionid);
2140 my ($res) = $dbh->selectrow_array('select max(planneddate) from serial where subscriptionid = ?', undef, $subscriptionid);
2141 my $nextdate = GetNextDate($subscription, $res);
2143 # only compare dates if both dates exist.
2144 if ($nextdate and $expirationdate) {
2145 if(Date::Calc::Delta_Days(
2146 split( /-/, $nextdate ),
2147 split( /-/, $expirationdate )
2153 } elsif ($subscription->{numberlength}>0) {
2154 return (countissuesfrom($subscriptionid,$subscription->{'startdate'}) >=$subscription->{numberlength}-1);
2160 sub in_array { # used in next sub down
2161 my ( $val, @elements ) = @_;
2162 foreach my $elem (@elements) {
2163 if ( $val == $elem ) {
2170 =head2 GetSubscriptionsFromBorrower
2172 ($count,@routinglist) = GetSubscriptionsFromBorrower($borrowernumber)
2174 this gets the info from subscriptionroutinglist for each $subscriptionid
2177 a count of the serial subscription routing lists to which a patron belongs,
2178 with the titles of those serial subscriptions as an array. Each element of the array
2179 contains a hash_ref with subscriptionID and title of subscription.
2183 sub GetSubscriptionsFromBorrower {
2184 my ($borrowernumber) = @_;
2185 my $dbh = C4::Context->dbh;
2186 my $sth = $dbh->prepare(
2187 "SELECT subscription.subscriptionid, biblio.title
2189 JOIN biblio ON biblio.biblionumber = subscription.biblionumber
2190 JOIN subscriptionroutinglist USING (subscriptionid)
2191 WHERE subscriptionroutinglist.borrowernumber = ? ORDER BY title ASC
2194 $sth->execute($borrowernumber);
2197 while ( my $line = $sth->fetchrow_hashref ) {
2199 push( @routinglist, $line );
2201 return ( $count, @routinglist );
2205 =head2 GetFictiveIssueNumber
2207 $issueno = GetFictiveIssueNumber($subscription, $publishedate);
2209 Get the position of the issue published at $publisheddate, considering the
2210 first issue (at firstacquidate) is at position 1, the next is at position 2, etc...
2211 This issuenumber doesn't take into account irregularities, so, for instance, if the 3rd
2212 issue is declared as 'irregular' (will be skipped at receipt), the next issue number
2213 will be 4, not 3. It's why it is called 'fictive'. It is NOT a serial seq, and is not
2214 depending on how many rows are in serial table.
2215 The issue number calculation is based on subscription frequency, first acquisition
2216 date, and $publisheddate.
2220 sub GetFictiveIssueNumber {
2221 my ($subscription, $publisheddate) = @_;
2223 my $frequency = GetSubscriptionFrequency($subscription->{'periodicity'});
2224 my $unit = $frequency->{unit} ? lc $frequency->{'unit'} : undef;
2228 my ($year, $month, $day) = split /-/, $publisheddate;
2229 my ($fa_year, $fa_month, $fa_day) = split /-/, $subscription->{'firstacquidate'};
2233 if($unit eq 'day') {
2234 $delta = Delta_Days($fa_year, $fa_month, $fa_day, $year, $month, $day);
2235 } elsif($unit eq 'week') {
2236 ($wkno, $year) = Week_of_Year($year, $month, $day);
2237 my ($fa_wkno, $fa_yr) = Week_of_Year($fa_year, $fa_month, $fa_day);
2238 $delta = ($fa_yr == $year) ? ($wkno - $fa_wkno) : ( ($year-$fa_yr-1)*52 + (52-$fa_wkno+$wkno) );
2239 } elsif($unit eq 'month') {
2240 $delta = ($fa_year == $year)
2241 ? ($month - $fa_month)
2242 : ( ($year-$fa_year-1)*12 + (12-$fa_month+$month) );
2243 } elsif($unit eq 'year') {
2244 $delta = $year - $fa_year;
2246 if($frequency->{'unitsperissue'} == 1) {
2247 $issueno = $delta * $frequency->{'issuesperunit'} + $subscription->{'countissuesperunit'};
2249 # Assuming issuesperunit == 1
2250 $issueno = int( ($delta + $frequency->{'unitsperissue'}) / $frequency->{'unitsperissue'} );
2256 sub _get_next_date_day {
2257 my ($subscription, $freqdata, $year, $month, $day) = @_;
2259 if ($subscription->{countissuesperunit} + 1 > $freqdata->{issuesperunit}){
2260 ($year,$month,$day) = Add_Delta_Days($year,$month, $day , $freqdata->{unitsperissue} );
2261 $subscription->{countissuesperunit} = 1;
2263 $subscription->{countissuesperunit}++;
2266 return ($year, $month, $day);
2269 sub _get_next_date_week {
2270 my ($subscription, $freqdata, $year, $month, $day) = @_;
2272 my ($wkno, $yr) = Week_of_Year($year, $month, $day);
2273 my $fa_dow = Day_of_Week(split /-/, $subscription->{firstacquidate});
2275 if ($subscription->{countissuesperunit} + 1 > $freqdata->{issuesperunit}){
2276 $subscription->{countissuesperunit} = 1;
2277 $wkno += $freqdata->{unitsperissue};
2282 ($year,$month,$day) = Monday_of_Week($wkno, $yr);
2283 ($year,$month,$day) = Add_Delta_Days($year, $month, $day, $fa_dow - 1);
2285 # Try to guess the next day of week
2286 my $delta_days = int((7 - ($fa_dow - 1)) / $freqdata->{issuesperunit});
2287 ($year,$month,$day) = Add_Delta_Days($year, $month, $day, $delta_days);
2288 $subscription->{countissuesperunit}++;
2291 return ($year, $month, $day);
2294 sub _get_next_date_month {
2295 my ($subscription, $freqdata, $year, $month, $day) = @_;
2298 (undef, undef, $fa_day) = split /-/, $subscription->{firstacquidate};
2300 if ($subscription->{countissuesperunit} + 1 > $freqdata->{issuesperunit}){
2301 $subscription->{countissuesperunit} = 1;
2302 ($year,$month,$day) = Add_Delta_YM($year,$month,$day, 0,
2303 $freqdata->{unitsperissue});
2304 my $days_in_month = Days_in_Month($year, $month);
2305 $day = $fa_day <= $days_in_month ? $fa_day : $days_in_month;
2307 # Try to guess the next day in month
2308 my $days_in_month = Days_in_Month($year, $month);
2309 my $delta_days = int(($days_in_month - ($fa_day - 1)) / $freqdata->{issuesperunit});
2310 ($year,$month,$day) = Add_Delta_Days($year, $month, $day, $delta_days);
2311 $subscription->{countissuesperunit}++;
2314 return ($year, $month, $day);
2317 sub _get_next_date_year {
2318 my ($subscription, $freqdata, $year, $month, $day) = @_;
2320 my ($fa_year, $fa_month, $fa_day) = split /-/, $subscription->{firstacquidate};
2322 if ($subscription->{countissuesperunit} + 1 > $freqdata->{issuesperunit}){
2323 $subscription->{countissuesperunit} = 1;
2324 ($year) = Add_Delta_YM($year,$month,$day, $freqdata->{"unitsperissue"},0);
2326 my $days_in_month = Days_in_Month($year, $month);
2327 $day = $fa_day <= $days_in_month ? $fa_day : $days_in_month;
2329 # Try to guess the next day in year
2330 my $days_in_year = Days_in_Year($year,12); #Sum the days of all the months of this year
2331 my $delta_days = int(($days_in_year - ($fa_day - 1)) / $freqdata->{issuesperunit});
2332 ($year,$month,$day) = Add_Delta_Days($year, $month, $day, $delta_days);
2333 $subscription->{countissuesperunit}++;
2336 return ($year, $month, $day);
2341 $resultdate = GetNextDate($publisheddate,$subscription)
2343 this function it takes the publisheddate and will return the next issue's date
2344 and will skip dates if there exists an irregularity.
2345 $publisheddate has to be an ISO date
2346 $subscription is a hashref containing at least 'periodicity', 'firstacquidate', 'irregularity', and 'countissuesperunit'
2347 $updatecount is a boolean value which, when set to true, update the 'countissuesperunit' in database
2348 - eg if periodicity is monthly and $publisheddate is 2007-02-10 but if March and April is to be
2349 skipped then the returned date will be 2007-05-10
2352 $resultdate - then next date in the sequence (ISO date)
2354 Return undef if subscription is irregular
2359 my ( $subscription, $publisheddate, $updatecount ) = @_;
2361 return unless $subscription and $publisheddate;
2363 my $freqdata = GetSubscriptionFrequency($subscription->{'periodicity'});
2365 if ($freqdata->{'unit'}) {
2366 my ( $year, $month, $day ) = split /-/, $publisheddate;
2368 # Process an irregularity Hash
2369 # Suppose that irregularities are stored in a string with this structure
2370 # irreg1;irreg2;irreg3
2371 # where irregX is the number of issue which will not be received
2372 # (the first issue takes the number 1, the 2nd the number 2 and so on)
2374 if ( $subscription->{irregularity} ) {
2375 my @irreg = split /;/, $subscription->{'irregularity'} ;
2376 foreach my $irregularity (@irreg) {
2377 $irregularities{$irregularity} = 1;
2381 # Get the 'fictive' next issue number
2382 # It is used to check if next issue is an irregular issue.
2383 my $issueno = GetFictiveIssueNumber($subscription, $publisheddate) + 1;
2385 # Then get the next date
2386 my $unit = lc $freqdata->{'unit'};
2387 if ($unit eq 'day') {
2388 while ($irregularities{$issueno}) {
2389 ($year, $month, $day) = _get_next_date_day($subscription,
2390 $freqdata, $year, $month, $day);
2393 ($year, $month, $day) = _get_next_date_day($subscription, $freqdata,
2394 $year, $month, $day);
2396 elsif ($unit eq 'week') {
2397 while ($irregularities{$issueno}) {
2398 ($year, $month, $day) = _get_next_date_week($subscription,
2399 $freqdata, $year, $month, $day);
2402 ($year, $month, $day) = _get_next_date_week($subscription,
2403 $freqdata, $year, $month, $day);
2405 elsif ($unit eq 'month') {
2406 while ($irregularities{$issueno}) {
2407 ($year, $month, $day) = _get_next_date_month($subscription,
2408 $freqdata, $year, $month, $day);
2411 ($year, $month, $day) = _get_next_date_month($subscription,
2412 $freqdata, $year, $month, $day);
2414 elsif ($unit eq 'year') {
2415 while ($irregularities{$issueno}) {
2416 ($year, $month, $day) = _get_next_date_year($subscription,
2417 $freqdata, $year, $month, $day);
2420 ($year, $month, $day) = _get_next_date_year($subscription,
2421 $freqdata, $year, $month, $day);
2425 my $dbh = C4::Context->dbh;
2428 SET countissuesperunit = ?
2429 WHERE subscriptionid = ?
2431 my $sth = $dbh->prepare($query);
2432 $sth->execute($subscription->{'countissuesperunit'}, $subscription->{'subscriptionid'});
2435 return sprintf("%04d-%02d-%02d", $year, $month, $day);
2441 $string = &_numeration($value,$num_type,$locale);
2443 _numeration returns the string corresponding to $value in the num_type
2453 my ($value, $num_type, $locale) = @_;
2458 if ( $num_type =~ /^dayname$/ ) {
2459 # 1970-11-01 was a Sunday
2460 $value = $value % 7;
2461 my $dt = DateTime->new(
2467 $string = $dt->strftime("%A");
2468 } elsif ( $num_type =~ /^monthname$/ ) {
2469 $value = $value % 12;
2470 my $dt = DateTime->new(
2472 month => $value + 1,
2475 $string = $dt->strftime("%B");
2476 } elsif ( $num_type =~ /^season$/ ) {
2477 my @seasons= qw( Spring Summer Fall Winter );
2478 $value = $value % 4;
2479 $string = $seasons[$value];
2487 =head2 is_barcode_in_use
2489 Returns number of occurrences of the barcode in the items table
2490 Can be used as a boolean test of whether the barcode has
2491 been deployed as yet
2495 sub is_barcode_in_use {
2496 my $barcode = shift;
2497 my $dbh = C4::Context->dbh;
2498 my $occurrences = $dbh->selectall_arrayref(
2499 'SELECT itemnumber from items where barcode = ?',
2504 return @{$occurrences};
2507 =head2 CloseSubscription
2508 Close a subscription given a subscriptionid
2510 sub CloseSubscription {
2511 my ( $subscriptionid ) = @_;
2512 return unless $subscriptionid;
2513 my $dbh = C4::Context->dbh;
2514 my $sth = $dbh->prepare( q{
2517 WHERE subscriptionid = ?
2519 $sth->execute( $subscriptionid );
2521 # Set status = missing when status = stopped
2522 $sth = $dbh->prepare( q{
2525 WHERE subscriptionid = ?
2528 $sth->execute( STOPPED, $subscriptionid, EXPECTED );
2531 =head2 ReopenSubscription
2532 Reopen a subscription given a subscriptionid
2534 sub ReopenSubscription {
2535 my ( $subscriptionid ) = @_;
2536 return unless $subscriptionid;
2537 my $dbh = C4::Context->dbh;
2538 my $sth = $dbh->prepare( q{
2541 WHERE subscriptionid = ?
2543 $sth->execute( $subscriptionid );
2545 # Set status = expected when status = stopped
2546 $sth = $dbh->prepare( q{
2549 WHERE subscriptionid = ?
2552 $sth->execute( EXPECTED, $subscriptionid, STOPPED );
2555 =head2 subscriptionCurrentlyOnOrder
2557 $bool = subscriptionCurrentlyOnOrder( $subscriptionid );
2559 Return 1 if subscription is currently on order else 0.
2563 sub subscriptionCurrentlyOnOrder {
2564 my ( $subscriptionid ) = @_;
2565 my $dbh = C4::Context->dbh;
2567 SELECT COUNT(*) FROM aqorders
2568 WHERE subscriptionid = ?
2569 AND datereceived IS NULL
2570 AND datecancellationprinted IS NULL
2572 my $sth = $dbh->prepare( $query );
2573 $sth->execute($subscriptionid);
2574 return $sth->fetchrow_array;
2577 =head2 can_claim_subscription
2579 $can = can_claim_subscription( $subscriptionid[, $userid] );
2581 Return 1 if the subscription can be claimed by the current logged user (or a given $userid), else 0.
2585 sub can_claim_subscription {
2586 my ( $subscription, $userid ) = @_;
2587 return _can_do_on_subscription( $subscription, $userid, 'claim_serials' );
2590 =head2 can_edit_subscription
2592 $can = can_edit_subscription( $subscriptionid[, $userid] );
2594 Return 1 if the subscription can be edited by the current logged user (or a given $userid), else 0.
2598 sub can_edit_subscription {
2599 my ( $subscription, $userid ) = @_;
2600 return _can_do_on_subscription( $subscription, $userid, 'edit_subscription' );
2603 =head2 can_show_subscription
2605 $can = can_show_subscription( $subscriptionid[, $userid] );
2607 Return 1 if the subscription can be shown by the current logged user (or a given $userid), else 0.
2611 sub can_show_subscription {
2612 my ( $subscription, $userid ) = @_;
2613 return _can_do_on_subscription( $subscription, $userid, '*' );
2616 sub _can_do_on_subscription {
2617 my ( $subscription, $userid, $permission ) = @_;
2618 return 0 unless C4::Context->userenv;
2619 my $flags = C4::Context->userenv->{flags};
2620 $userid ||= C4::Context->userenv->{'id'};
2622 if ( C4::Context->preference('IndependentBranches') ) {
2624 if C4::Context->IsSuperLibrarian()
2626 C4::Auth::haspermission( $userid, { serials => 'superserials' } )
2628 C4::Auth::haspermission( $userid,
2629 { serials => $permission } )
2630 and ( not defined $subscription->{branchcode}
2631 or $subscription->{branchcode} eq ''
2632 or $subscription->{branchcode} eq
2633 C4::Context->userenv->{'branch'} )
2638 if C4::Context->IsSuperLibrarian()
2640 C4::Auth::haspermission( $userid, { serials => 'superserials' } )
2641 or C4::Auth::haspermission(
2642 $userid, { serials => $permission }
2649 =head2 findSerialsByStatus
2651 @serials = findSerialsByStatus($status, $subscriptionid);
2653 Returns an array of serials matching a given status and subscription id.
2657 sub findSerialsByStatus {
2658 my ( $status, $subscriptionid ) = @_;
2659 my $dbh = C4::Context->dbh;
2660 my $query = q| SELECT * from serial
2662 AND subscriptionid = ?
2664 my $serials = $dbh->selectall_arrayref( $query, { Slice => {} }, $status, $subscriptionid );
2673 Koha Development Team <http://koha-community.org/>