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;
34 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 &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
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, CLAIMED, @$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;
321 $subscription->{cannotedit} = not can_edit_subscription( $subscription );
323 # Add additional fields to the subscription into a new key "additional_fields"
324 my $additional_field_values = Koha::AdditionalField->fetch_all_values({
325 tablename => 'subscription',
326 record_id => $subscriptionid,
328 $subscription->{additional_fields} = $additional_field_values->{$subscriptionid};
330 return $subscription;
333 =head2 GetFullSubscription
335 $array_ref = GetFullSubscription($subscriptionid)
336 this function reads the serial table.
340 sub GetFullSubscription {
341 my ($subscriptionid) = @_;
343 return unless ($subscriptionid);
345 my $dbh = C4::Context->dbh;
347 SELECT serial.serialid,
350 serial.publisheddate,
352 serial.notes as notes,
353 year(IF(serial.publisheddate="00-00-0000",serial.planneddate,serial.publisheddate)) as year,
354 aqbooksellers.name as aqbooksellername,
355 biblio.title as bibliotitle,
356 subscription.branchcode AS branchcode,
357 subscription.subscriptionid AS subscriptionid
359 LEFT JOIN subscription ON
360 (serial.subscriptionid=subscription.subscriptionid )
361 LEFT JOIN aqbooksellers on subscription.aqbooksellerid=aqbooksellers.id
362 LEFT JOIN biblio on biblio.biblionumber=subscription.biblionumber
363 WHERE serial.subscriptionid = ?
365 IF(serial.publisheddate="00-00-0000",serial.planneddate,serial.publisheddate) DESC,
366 serial.subscriptionid
368 $debug and warn "GetFullSubscription query: $query";
369 my $sth = $dbh->prepare($query);
370 $sth->execute($subscriptionid);
371 my $subscriptions = $sth->fetchall_arrayref( {} );
372 for my $subscription ( @$subscriptions ) {
373 $subscription->{cannotedit} = not can_edit_subscription( $subscription );
375 return $subscriptions;
378 =head2 PrepareSerialsData
380 $array_ref = PrepareSerialsData($serialinfomation)
381 where serialinformation is a hashref array
385 sub PrepareSerialsData {
388 return unless ($lines);
394 my $aqbooksellername;
398 my $previousnote = "";
400 foreach my $subs (@{$lines}) {
401 for my $datefield ( qw(publisheddate planneddate) ) {
402 # handle 0000-00-00 dates
403 if (defined $subs->{$datefield} and $subs->{$datefield} =~ m/^00/) {
404 $subs->{$datefield} = undef;
407 $subs->{ "status" . $subs->{'status'} } = 1;
408 if ( grep { $_ == $subs->{status} } ( EXPECTED, LATE, MISSING_STATUSES, CLAIMED ) ) {
409 $subs->{"checked"} = 1;
412 if ( $subs->{'year'} && $subs->{'year'} ne "" ) {
413 $year = $subs->{'year'};
417 if ( $tmpresults{$year} ) {
418 push @{ $tmpresults{$year}->{'serials'} }, $subs;
420 $tmpresults{$year} = {
422 'aqbooksellername' => $subs->{'aqbooksellername'},
423 'bibliotitle' => $subs->{'bibliotitle'},
424 'serials' => [$subs],
429 foreach my $key ( sort { $b cmp $a } keys %tmpresults ) {
430 push @res, $tmpresults{$key};
435 =head2 GetSubscriptionsFromBiblionumber
437 $array_ref = GetSubscriptionsFromBiblionumber($biblionumber)
438 this function get the subscription list. it reads the subscription table.
440 reference to an array of subscriptions which have the biblionumber given on input arg.
441 each element of this array is a hashref containing
442 startdate, histstartdate,opacnote,missinglist,recievedlist,periodicity,status & enddate
446 sub GetSubscriptionsFromBiblionumber {
447 my ($biblionumber) = @_;
449 return unless ($biblionumber);
451 my $dbh = C4::Context->dbh;
453 SELECT subscription.*,
455 subscriptionhistory.*,
456 aqbooksellers.name AS aqbooksellername,
457 biblio.title AS bibliotitle
459 LEFT JOIN subscriptionhistory ON subscription.subscriptionid=subscriptionhistory.subscriptionid
460 LEFT JOIN aqbooksellers ON subscription.aqbooksellerid=aqbooksellers.id
461 LEFT JOIN biblio ON biblio.biblionumber=subscription.biblionumber
462 LEFT JOIN branches ON branches.branchcode=subscription.branchcode
463 WHERE subscription.biblionumber = ?
465 my $sth = $dbh->prepare($query);
466 $sth->execute($biblionumber);
468 while ( my $subs = $sth->fetchrow_hashref ) {
469 $subs->{startdate} = format_date( $subs->{startdate} );
470 $subs->{histstartdate} = format_date( $subs->{histstartdate} );
471 $subs->{histenddate} = format_date( $subs->{histenddate} );
472 $subs->{opacnote} =~ s/\n/\<br\/\>/g;
473 $subs->{missinglist} =~ s/\n/\<br\/\>/g;
474 $subs->{recievedlist} =~ s/\n/\<br\/\>/g;
475 $subs->{ "periodicity" . $subs->{periodicity} } = 1;
476 $subs->{ "numberpattern" . $subs->{numberpattern} } = 1;
477 $subs->{ "status" . $subs->{'status'} } = 1;
479 if ( $subs->{enddate} eq '0000-00-00' ) {
480 $subs->{enddate} = '';
482 $subs->{enddate} = format_date( $subs->{enddate} );
484 $subs->{'abouttoexpire'} = abouttoexpire( $subs->{'subscriptionid'} );
485 $subs->{'subscriptionexpired'} = HasSubscriptionExpired( $subs->{'subscriptionid'} );
486 $subs->{cannotedit} = not can_edit_subscription( $subs );
492 =head2 GetFullSubscriptionsFromBiblionumber
494 $array_ref = GetFullSubscriptionsFromBiblionumber($biblionumber)
495 this function reads the serial table.
499 sub GetFullSubscriptionsFromBiblionumber {
500 my ($biblionumber) = @_;
501 my $dbh = C4::Context->dbh;
503 SELECT serial.serialid,
506 serial.publisheddate,
508 serial.notes as notes,
509 year(IF(serial.publisheddate="00-00-0000",serial.planneddate,serial.publisheddate)) as year,
510 biblio.title as bibliotitle,
511 subscription.branchcode AS branchcode,
512 subscription.subscriptionid AS subscriptionid
514 LEFT JOIN subscription ON
515 (serial.subscriptionid=subscription.subscriptionid)
516 LEFT JOIN aqbooksellers on subscription.aqbooksellerid=aqbooksellers.id
517 LEFT JOIN biblio on biblio.biblionumber=subscription.biblionumber
518 WHERE subscription.biblionumber = ?
520 IF(serial.publisheddate="00-00-0000",serial.planneddate,serial.publisheddate) DESC,
521 serial.subscriptionid
523 my $sth = $dbh->prepare($query);
524 $sth->execute($biblionumber);
525 my $subscriptions = $sth->fetchall_arrayref( {} );
526 for my $subscription ( @$subscriptions ) {
527 $subscription->{cannotedit} = not can_edit_subscription( $subscription );
529 return $subscriptions;
532 =head2 SearchSubscriptions
534 @results = SearchSubscriptions($args);
536 This function returns a list of hashrefs, one for each subscription
537 that meets the conditions specified by the $args hashref.
539 The valid search fields are:
553 The expiration_date search field is special; it specifies the maximum
554 subscription expiration date.
558 sub SearchSubscriptions {
561 my $additional_fields = $args->{additional_fields} // [];
562 my $matching_record_ids_for_additional_fields = [];
563 if ( @$additional_fields ) {
564 $matching_record_ids_for_additional_fields = Koha::AdditionalField->get_matching_record_ids({
565 fields => $args->{additional_fields},
566 tablename => 'subscription',
568 return () unless @$matching_record_ids_for_additional_fields;
573 subscription.notes AS publicnotes,
574 subscriptionhistory.*,
576 biblio.notes AS biblionotes,
582 LEFT JOIN subscriptionhistory USING(subscriptionid)
583 LEFT JOIN biblio ON biblio.biblionumber = subscription.biblionumber
584 LEFT JOIN biblioitems ON biblioitems.biblionumber = subscription.biblionumber
585 LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id
587 $query .= q| WHERE 1|;
590 if( $args->{biblionumber} ) {
591 push @where_strs, "biblio.biblionumber = ?";
592 push @where_args, $args->{biblionumber};
595 if( $args->{title} ){
596 my @words = split / /, $args->{title};
598 foreach my $word (@words) {
599 push @strs, "biblio.title LIKE ?";
600 push @args, "%$word%";
603 push @where_strs, '(' . join (' AND ', @strs) . ')';
604 push @where_args, @args;
608 push @where_strs, "biblioitems.issn LIKE ?";
609 push @where_args, "%$args->{issn}%";
612 push @where_strs, "biblioitems.ean LIKE ?";
613 push @where_args, "%$args->{ean}%";
615 if ( $args->{callnumber} ) {
616 push @where_strs, "subscription.callnumber LIKE ?";
617 push @where_args, "%$args->{callnumber}%";
619 if( $args->{publisher} ){
620 push @where_strs, "biblioitems.publishercode LIKE ?";
621 push @where_args, "%$args->{publisher}%";
623 if( $args->{bookseller} ){
624 push @where_strs, "aqbooksellers.name LIKE ?";
625 push @where_args, "%$args->{bookseller}%";
627 if( $args->{branch} ){
628 push @where_strs, "subscription.branchcode = ?";
629 push @where_args, "$args->{branch}";
631 if ( $args->{location} ) {
632 push @where_strs, "subscription.location = ?";
633 push @where_args, "$args->{location}";
635 if ( $args->{expiration_date} ) {
636 push @where_strs, "subscription.enddate <= ?";
637 push @where_args, "$args->{expiration_date}";
639 if( defined $args->{closed} ){
640 push @where_strs, "subscription.closed = ?";
641 push @where_args, "$args->{closed}";
645 $query .= ' AND ' . join(' AND ', @where_strs);
647 if ( @$additional_fields ) {
648 $query .= ' AND subscriptionid IN ('
649 . join( ', ', @$matching_record_ids_for_additional_fields )
653 $query .= " ORDER BY " . $args->{orderby} if $args->{orderby};
655 my $dbh = C4::Context->dbh;
656 my $sth = $dbh->prepare($query);
657 $sth->execute(@where_args);
658 my $results = $sth->fetchall_arrayref( {} );
660 for my $subscription ( @$results ) {
661 $subscription->{cannotedit} = not can_edit_subscription( $subscription );
662 $subscription->{cannotdisplay} = not can_show_subscription( $subscription );
664 my $additional_field_values = Koha::AdditionalField->fetch_all_values({
665 record_id => $subscription->{subscriptionid},
666 tablename => 'subscription'
668 $subscription->{addition_fields} = $additional_field_values->{$subscription->{subscriptionid}};
677 ($totalissues,@serials) = GetSerials($subscriptionid);
678 this function gets every serial not arrived for a given subscription
679 as well as the number of issues registered in the database (all types)
680 this number is used to see if a subscription can be deleted (=it must have only 1 issue)
682 FIXME: We should return \@serials.
687 my ( $subscriptionid, $count ) = @_;
689 return unless $subscriptionid;
691 my $dbh = C4::Context->dbh;
693 # status = 2 is "arrived"
695 $count = 5 unless ($count);
697 my $statuses = join( ',', ( ARRIVED, MISSING_STATUSES, NOT_ISSUED ) );
698 my $query = "SELECT serialid,serialseq, status, publisheddate, planneddate,notes, routingnotes
700 WHERE subscriptionid = ? AND status NOT IN ( $statuses )
701 ORDER BY IF(publisheddate<>'0000-00-00',publisheddate,planneddate) DESC";
702 my $sth = $dbh->prepare($query);
703 $sth->execute($subscriptionid);
705 while ( my $line = $sth->fetchrow_hashref ) {
706 $line->{ "status" . $line->{status} } = 1; # fills a "statusX" value, used for template status select list
707 for my $datefield ( qw( planneddate publisheddate) ) {
708 if ($line->{$datefield} && $line->{$datefield}!~m/^00/) {
709 $line->{$datefield} = format_date( $line->{$datefield});
711 $line->{$datefield} = q{};
714 push @serials, $line;
717 # OK, now add the last 5 issues arrives/missing
718 $query = "SELECT serialid,serialseq, status, planneddate, publisheddate,notes, routingnotes
720 WHERE subscriptionid = ?
721 AND status IN ( $statuses )
722 ORDER BY IF(publisheddate<>'0000-00-00',publisheddate,planneddate) DESC
724 $sth = $dbh->prepare($query);
725 $sth->execute($subscriptionid);
726 while ( ( my $line = $sth->fetchrow_hashref ) && $counter < $count ) {
728 $line->{ "status" . $line->{status} } = 1; # fills a "statusX" value, used for template status select list
729 for my $datefield ( qw( planneddate publisheddate) ) {
730 if ($line->{$datefield} && $line->{$datefield}!~m/^00/) {
731 $line->{$datefield} = format_date( $line->{$datefield});
733 $line->{$datefield} = q{};
737 push @serials, $line;
740 $query = "SELECT count(*) FROM serial WHERE subscriptionid=?";
741 $sth = $dbh->prepare($query);
742 $sth->execute($subscriptionid);
743 my ($totalissues) = $sth->fetchrow;
744 return ( $totalissues, @serials );
749 @serials = GetSerials2($subscriptionid,$statuses);
750 this function returns every serial waited for a given subscription
751 as well as the number of issues registered in the database (all types)
752 this number is used to see if a subscription can be deleted (=it must have only 1 issue)
754 $statuses is an arrayref of statuses and is mandatory.
759 my ( $subscription, $statuses ) = @_;
761 return unless ($subscription and @$statuses);
763 my $statuses_string = join ',', @$statuses;
765 my $dbh = C4::Context->dbh;
767 SELECT serialid,serialseq, status, planneddate, publisheddate,notes, routingnotes
769 WHERE subscriptionid=$subscription AND status IN ($statuses_string)
770 ORDER BY publisheddate,serialid DESC
772 $debug and warn "GetSerials2 query: $query";
773 my $sth = $dbh->prepare($query);
777 while ( my $line = $sth->fetchrow_hashref ) {
778 $line->{ "status" . $line->{status} } = 1; # fills a "statusX" value, used for template status select list
779 # Format dates for display
780 for my $datefield ( qw( planneddate publisheddate ) ) {
781 if (!defined($line->{$datefield}) || $line->{$datefield} =~m/^00/) {
782 $line->{$datefield} = q{};
785 $line->{$datefield} = format_date( $line->{$datefield} );
788 push @serials, $line;
793 =head2 GetLatestSerials
795 \@serials = GetLatestSerials($subscriptionid,$limit)
796 get the $limit's latest serials arrived or missing for a given subscription
798 a ref to an array which contains all of the latest serials stored into a hash.
802 sub GetLatestSerials {
803 my ( $subscriptionid, $limit ) = @_;
805 return unless ($subscriptionid and $limit);
807 my $dbh = C4::Context->dbh;
809 my $statuses = join( ',', ( ARRIVED, MISSING_STATUSES ) );
810 my $strsth = "SELECT serialid,serialseq, status, planneddate, publisheddate, notes
812 WHERE subscriptionid = ?
813 AND status IN ($statuses)
814 ORDER BY publisheddate DESC LIMIT 0,$limit
816 my $sth = $dbh->prepare($strsth);
817 $sth->execute($subscriptionid);
819 while ( my $line = $sth->fetchrow_hashref ) {
820 $line->{ "status" . $line->{status} } = 1; # fills a "statusX" value, used for template status select list
821 $line->{"planneddate"} = format_date( $line->{"planneddate"} );
822 $line->{"publisheddate"} = format_date( $line->{"publisheddate"} );
823 push @serials, $line;
829 =head2 GetDistributedTo
831 $distributedto=GetDistributedTo($subscriptionid)
832 This function returns the field distributedto for the subscription matching subscriptionid
836 sub GetDistributedTo {
837 my $dbh = C4::Context->dbh;
839 my ($subscriptionid) = @_;
841 return unless ($subscriptionid);
843 my $query = "SELECT distributedto FROM subscription WHERE subscriptionid=?";
844 my $sth = $dbh->prepare($query);
845 $sth->execute($subscriptionid);
846 return ($distributedto) = $sth->fetchrow;
852 $nextseq, $newlastvalue1, $newlastvalue2, $newlastvalue3,
853 $newinnerloop1, $newinnerloop2, $newinnerloop3
854 ) = GetNextSeq( $subscription, $pattern, $planneddate );
856 $subscription is a hashref containing all the attributes of the table
858 $pattern is a hashref containing all the attributes of the table
859 'subscription_numberpatterns'.
860 $planneddate is a C4::Dates object.
861 This function get the next issue for the subscription given on input arg
866 my ($subscription, $pattern, $planneddate) = @_;
868 return unless ($subscription and $pattern);
870 my ( $newlastvalue1, $newlastvalue2, $newlastvalue3,
871 $newinnerloop1, $newinnerloop2, $newinnerloop3 );
874 if ($subscription->{'skip_serialseq'}) {
875 my @irreg = split /;/, $subscription->{'irregularity'};
877 my $irregularities = {};
878 $irregularities->{$_} = 1 foreach(@irreg);
879 my $issueno = GetFictiveIssueNumber($subscription, $planneddate) + 1;
880 while($irregularities->{$issueno}) {
887 my $numberingmethod = $pattern->{numberingmethod};
889 if ($numberingmethod) {
890 $calculated = $numberingmethod;
891 my $locale = $subscription->{locale};
892 $newlastvalue1 = $subscription->{lastvalue1} || 0;
893 $newlastvalue2 = $subscription->{lastvalue2} || 0;
894 $newlastvalue3 = $subscription->{lastvalue3} || 0;
895 $newinnerloop1 = $subscription->{innerloop1} || 0;
896 $newinnerloop2 = $subscription->{innerloop2} || 0;
897 $newinnerloop3 = $subscription->{innerloop3} || 0;
900 $calc{$_} = 1 if ($numberingmethod =~ /\{$_\}/);
903 for(my $i = 0; $i < $count; $i++) {
905 # check if we have to increase the new value.
907 if ($newinnerloop1 >= $pattern->{every1}) {
909 $newlastvalue1 += $pattern->{add1};
911 # reset counter if needed.
912 $newlastvalue1 = $pattern->{setto1} if ($newlastvalue1 > $pattern->{whenmorethan1});
915 # check if we have to increase the new value.
917 if ($newinnerloop2 >= $pattern->{every2}) {
919 $newlastvalue2 += $pattern->{add2};
921 # reset counter if needed.
922 $newlastvalue2 = $pattern->{setto2} if ($newlastvalue2 > $pattern->{whenmorethan2});
925 # check if we have to increase the new value.
927 if ($newinnerloop3 >= $pattern->{every3}) {
929 $newlastvalue3 += $pattern->{add3};
931 # reset counter if needed.
932 $newlastvalue3 = $pattern->{setto3} if ($newlastvalue3 > $pattern->{whenmorethan3});
936 my $newlastvalue1string = _numeration( $newlastvalue1, $pattern->{numbering1}, $locale );
937 $calculated =~ s/\{X\}/$newlastvalue1string/g;
940 my $newlastvalue2string = _numeration( $newlastvalue2, $pattern->{numbering2}, $locale );
941 $calculated =~ s/\{Y\}/$newlastvalue2string/g;
944 my $newlastvalue3string = _numeration( $newlastvalue3, $pattern->{numbering3}, $locale );
945 $calculated =~ s/\{Z\}/$newlastvalue3string/g;
950 $newlastvalue1, $newlastvalue2, $newlastvalue3,
951 $newinnerloop1, $newinnerloop2, $newinnerloop3);
956 $calculated = GetSeq($subscription, $pattern)
957 $subscription is a hashref containing all the attributes of the table 'subscription'
958 $pattern is a hashref containing all the attributes of the table 'subscription_numberpatterns'
959 this function transforms {X},{Y},{Z} to 150,0,0 for example.
961 the sequence in string format
966 my ($subscription, $pattern) = @_;
968 return unless ($subscription and $pattern);
970 my $locale = $subscription->{locale};
972 my $calculated = $pattern->{numberingmethod};
974 my $newlastvalue1 = $subscription->{'lastvalue1'} || 0;
975 $newlastvalue1 = _numeration($newlastvalue1, $pattern->{numbering1}, $locale) if ($pattern->{numbering1}); # reset counter if needed.
976 $calculated =~ s/\{X\}/$newlastvalue1/g;
978 my $newlastvalue2 = $subscription->{'lastvalue2'} || 0;
979 $newlastvalue2 = _numeration($newlastvalue2, $pattern->{numbering2}, $locale) if ($pattern->{numbering2}); # reset counter if needed.
980 $calculated =~ s/\{Y\}/$newlastvalue2/g;
982 my $newlastvalue3 = $subscription->{'lastvalue3'} || 0;
983 $newlastvalue3 = _numeration($newlastvalue3, $pattern->{numbering3}, $locale) if ($pattern->{numbering3}); # reset counter if needed.
984 $calculated =~ s/\{Z\}/$newlastvalue3/g;
988 =head2 GetExpirationDate
990 $enddate = GetExpirationDate($subscriptionid, [$startdate])
992 this function return the next expiration date for a subscription given on input args.
999 sub GetExpirationDate {
1000 my ( $subscriptionid, $startdate ) = @_;
1002 return unless ($subscriptionid);
1004 my $dbh = C4::Context->dbh;
1005 my $subscription = GetSubscription($subscriptionid);
1008 # we don't do the same test if the subscription is based on X numbers or on X weeks/months
1009 $enddate = $startdate || $subscription->{startdate};
1010 my @date = split( /-/, $enddate );
1012 return if ( scalar(@date) != 3 || not check_date(@date) );
1014 my $frequency = C4::Serials::Frequency::GetSubscriptionFrequency($subscription->{periodicity});
1015 if ( $frequency and $frequency->{unit} ) {
1018 if ( my $length = $subscription->{numberlength} ) {
1020 #calculate the date of the last issue.
1021 for ( my $i = 1 ; $i <= $length ; $i++ ) {
1022 $enddate = GetNextDate( $subscription, $enddate );
1024 } elsif ( $subscription->{monthlength} ) {
1025 if ( $$subscription{startdate} ) {
1026 my @enddate = Add_Delta_YM( $date[0], $date[1], $date[2], 0, $subscription->{monthlength} );
1027 $enddate = sprintf( "%04d-%02d-%02d", $enddate[0], $enddate[1], $enddate[2] );
1029 } elsif ( $subscription->{weeklength} ) {
1030 if ( $$subscription{startdate} ) {
1031 my @date = split( /-/, $subscription->{startdate} );
1032 my @enddate = Add_Delta_Days( $date[0], $date[1], $date[2], $subscription->{weeklength} * 7 );
1033 $enddate = sprintf( "%04d-%02d-%02d", $enddate[0], $enddate[1], $enddate[2] );
1036 $enddate = $subscription->{enddate};
1040 return $subscription->{enddate};
1044 =head2 CountSubscriptionFromBiblionumber
1046 $subscriptionsnumber = CountSubscriptionFromBiblionumber($biblionumber)
1047 this returns a count of the subscriptions for a given biblionumber
1049 the number of subscriptions
1053 sub CountSubscriptionFromBiblionumber {
1054 my ($biblionumber) = @_;
1056 return unless ($biblionumber);
1058 my $dbh = C4::Context->dbh;
1059 my $query = "SELECT count(*) FROM subscription WHERE biblionumber=?";
1060 my $sth = $dbh->prepare($query);
1061 $sth->execute($biblionumber);
1062 my $subscriptionsnumber = $sth->fetchrow;
1063 return $subscriptionsnumber;
1066 =head2 ModSubscriptionHistory
1068 ModSubscriptionHistory($subscriptionid,$histstartdate,$enddate,$recievedlist,$missinglist,$opacnote,$librariannote);
1070 this function modifies the history of a subscription. Put your new values on input arg.
1071 returns the number of rows affected
1075 sub ModSubscriptionHistory {
1076 my ( $subscriptionid, $histstartdate, $enddate, $receivedlist, $missinglist, $opacnote, $librariannote ) = @_;
1078 return unless ($subscriptionid);
1080 my $dbh = C4::Context->dbh;
1081 my $query = "UPDATE subscriptionhistory
1082 SET histstartdate=?,histenddate=?,recievedlist=?,missinglist=?,opacnote=?,librariannote=?
1083 WHERE subscriptionid=?
1085 my $sth = $dbh->prepare($query);
1086 $receivedlist =~ s/^; // if $receivedlist;
1087 $missinglist =~ s/^; // if $missinglist;
1088 $opacnote =~ s/^; // if $opacnote;
1089 $sth->execute( $histstartdate, $enddate, $receivedlist, $missinglist, $opacnote, $librariannote, $subscriptionid );
1093 =head2 ModSerialStatus
1095 ModSerialStatus($serialid,$serialseq, $planneddate,$publisheddate,$status,$notes)
1097 This function modify the serial status. Serial status is a number.(eg 2 is "arrived")
1098 Note : if we change from "waited" to something else,then we will have to create a new "waited" entry
1102 sub ModSerialStatus {
1103 my ( $serialid, $serialseq, $planneddate, $publisheddate, $status, $notes ) = @_;
1105 return unless ($serialid);
1107 #It is a usual serial
1108 # 1st, get previous status :
1109 my $dbh = C4::Context->dbh;
1110 my $query = "SELECT serial.subscriptionid,serial.status,subscription.periodicity
1111 FROM serial, subscription
1112 WHERE serial.subscriptionid=subscription.subscriptionid
1114 my $sth = $dbh->prepare($query);
1115 $sth->execute($serialid);
1116 my ( $subscriptionid, $oldstatus, $periodicity ) = $sth->fetchrow;
1117 my $frequency = GetSubscriptionFrequency($periodicity);
1119 # change status & update subscriptionhistory
1121 if ( $status == DELETED ) {
1122 DelIssue( { 'serialid' => $serialid, 'subscriptionid' => $subscriptionid, 'serialseq' => $serialseq } );
1125 my $query = 'UPDATE serial SET serialseq=?,publisheddate=?,planneddate=?,status=?,notes=? WHERE serialid = ?';
1126 $sth = $dbh->prepare($query);
1127 $sth->execute( $serialseq, $publisheddate, $planneddate, $status, $notes, $serialid );
1128 $query = "SELECT * FROM subscription WHERE subscriptionid = ?";
1129 $sth = $dbh->prepare($query);
1130 $sth->execute($subscriptionid);
1131 my $val = $sth->fetchrow_hashref;
1132 unless ( $val->{manualhistory} ) {
1133 $query = "SELECT missinglist,recievedlist FROM subscriptionhistory WHERE subscriptionid=?";
1134 $sth = $dbh->prepare($query);
1135 $sth->execute($subscriptionid);
1136 my ( $missinglist, $recievedlist ) = $sth->fetchrow;
1138 if ( $status == ARRIVED || ($oldstatus == ARRIVED && $status != ARRIVED) ) {
1139 $recievedlist .= "; $serialseq"
1140 if ($recievedlist !~ /(^|;)\s*$serialseq(?=;|$)/);
1143 # in case serial has been previously marked as missing
1144 if (grep /$status/, (EXPECTED, ARRIVED, LATE, CLAIMED)) {
1145 $missinglist=~ s/(^|;)\s*$serialseq(?=;|$)//g;
1148 $missinglist .= "; $serialseq"
1149 if ( ( grep { $_ == $status } ( MISSING_STATUSES ) ) && ( $missinglist !~/(^|;)\s*$serialseq(?=;|$)/ ) );
1150 $missinglist .= "; not issued $serialseq"
1151 if ( $status == NOT_ISSUED && $missinglist !~ /(^|;)\s*$serialseq(?=;|$)/ );
1153 $query = "UPDATE subscriptionhistory SET recievedlist=?, missinglist=? WHERE subscriptionid=?";
1154 $sth = $dbh->prepare($query);
1155 $recievedlist =~ s/^; //;
1156 $missinglist =~ s/^; //;
1157 $sth->execute( $recievedlist, $missinglist, $subscriptionid );
1161 # create new waited entry if needed (ie : was a "waited" and has changed)
1162 if ( $oldstatus == EXPECTED && $status != EXPECTED ) {
1163 my $subscription = GetSubscription($subscriptionid);
1164 my $pattern = C4::Serials::Numberpattern::GetSubscriptionNumberpattern($subscription->{numberpattern});
1168 $newserialseq, $newlastvalue1, $newlastvalue2, $newlastvalue3,
1169 $newinnerloop1, $newinnerloop2, $newinnerloop3
1171 = GetNextSeq( $subscription, $pattern, $publisheddate );
1173 # next date (calculated from actual date & frequency parameters)
1174 my $nextpublisheddate = GetNextDate($subscription, $publisheddate, 1);
1175 my $nextpubdate = $nextpublisheddate;
1176 NewIssue( $newserialseq, $subscriptionid, $subscription->{'biblionumber'}, 1, $nextpubdate, $nextpubdate );
1177 $query = "UPDATE subscription SET lastvalue1=?, lastvalue2=?, lastvalue3=?, innerloop1=?, innerloop2=?, innerloop3=?
1178 WHERE subscriptionid = ?";
1179 $sth = $dbh->prepare($query);
1180 $sth->execute( $newlastvalue1, $newlastvalue2, $newlastvalue3, $newinnerloop1, $newinnerloop2, $newinnerloop3, $subscriptionid );
1182 # check if an alert must be sent... (= a letter is defined & status became "arrived"
1183 if ( $subscription->{letter} && $status == ARRIVED && $oldstatus != ARRIVED ) {
1184 require C4::Letters;
1185 C4::Letters::SendAlerts( 'issue', $serialid, $subscription->{letter} );
1192 =head2 GetNextExpected
1194 $nextexpected = GetNextExpected($subscriptionid)
1196 Get the planneddate for the current expected issue of the subscription.
1202 planneddate => ISO date
1207 sub GetNextExpected {
1208 my ($subscriptionid) = @_;
1210 my $dbh = C4::Context->dbh;
1214 WHERE subscriptionid = ?
1218 my $sth = $dbh->prepare($query);
1220 # Each subscription has only one 'expected' issue.
1221 $sth->execute( $subscriptionid, EXPECTED );
1222 my $nextissue = $sth->fetchrow_hashref;
1223 if ( !$nextissue ) {
1227 WHERE subscriptionid = ?
1228 ORDER BY publisheddate DESC
1231 $sth = $dbh->prepare($query);
1232 $sth->execute($subscriptionid);
1233 $nextissue = $sth->fetchrow_hashref;
1235 foreach(qw/planneddate publisheddate/) {
1236 if ( !defined $nextissue->{$_} ) {
1237 # or should this default to 1st Jan ???
1238 $nextissue->{$_} = strftime( '%Y-%m-%d', localtime );
1240 $nextissue->{$_} = ($nextissue->{$_} ne '0000-00-00')
1248 =head2 ModNextExpected
1250 ModNextExpected($subscriptionid,$date)
1252 Update the planneddate for the current expected issue of the subscription.
1253 This will modify all future prediction results.
1255 C<$date> is an ISO date.
1261 sub ModNextExpected {
1262 my ( $subscriptionid, $date ) = @_;
1263 my $dbh = C4::Context->dbh;
1265 #FIXME: Would expect to only set planneddate, but we set both on new issue creation, so updating it here
1266 my $sth = $dbh->prepare('UPDATE serial SET planneddate=?,publisheddate=? WHERE subscriptionid=? AND status=?');
1268 # Each subscription has only one 'expected' issue.
1269 $sth->execute( $date, $date, $subscriptionid, EXPECTED );
1274 =head2 GetSubscriptionIrregularities
1278 =item @irreg = &GetSubscriptionIrregularities($subscriptionid);
1279 get the list of irregularities for a subscription
1285 sub GetSubscriptionIrregularities {
1286 my $subscriptionid = shift;
1288 return unless $subscriptionid;
1290 my $dbh = C4::Context->dbh;
1294 WHERE subscriptionid = ?
1296 my $sth = $dbh->prepare($query);
1297 $sth->execute($subscriptionid);
1299 my ($result) = $sth->fetchrow_array;
1300 my @irreg = split /;/, $result;
1305 =head2 ModSubscription
1307 this function modifies a subscription. Put all new values on input args.
1308 returns the number of rows affected
1312 sub ModSubscription {
1314 $auser, $branchcode, $aqbooksellerid, $cost, $aqbudgetid, $startdate,
1315 $periodicity, $firstacquidate, $irregularity, $numberpattern, $locale,
1316 $numberlength, $weeklength, $monthlength, $lastvalue1, $innerloop1,
1317 $lastvalue2, $innerloop2, $lastvalue3, $innerloop3, $status,
1318 $biblionumber, $callnumber, $notes, $letter, $manualhistory,
1319 $internalnotes, $serialsadditems, $staffdisplaycount, $opacdisplaycount,
1320 $graceperiod, $location, $enddate, $subscriptionid, $skip_serialseq
1323 my $dbh = C4::Context->dbh;
1324 my $query = "UPDATE subscription
1325 SET librarian=?, branchcode=?, aqbooksellerid=?, cost=?, aqbudgetid=?,
1326 startdate=?, periodicity=?, firstacquidate=?, irregularity=?,
1327 numberpattern=?, locale=?, numberlength=?, weeklength=?, monthlength=?,
1328 lastvalue1=?, innerloop1=?, lastvalue2=?, innerloop2=?,
1329 lastvalue3=?, innerloop3=?, status=?, biblionumber=?,
1330 callnumber=?, notes=?, letter=?, manualhistory=?,
1331 internalnotes=?, serialsadditems=?, staffdisplaycount=?,
1332 opacdisplaycount=?, graceperiod=?, location = ?, enddate=?,
1334 WHERE subscriptionid = ?";
1336 my $sth = $dbh->prepare($query);
1338 $auser, $branchcode, $aqbooksellerid, $cost,
1339 $aqbudgetid, $startdate, $periodicity, $firstacquidate,
1340 $irregularity, $numberpattern, $locale, $numberlength,
1341 $weeklength, $monthlength, $lastvalue1, $innerloop1,
1342 $lastvalue2, $innerloop2, $lastvalue3, $innerloop3,
1343 $status, $biblionumber, $callnumber, $notes,
1344 $letter, ($manualhistory ? $manualhistory : 0),
1345 $internalnotes, $serialsadditems, $staffdisplaycount, $opacdisplaycount,
1346 $graceperiod, $location, $enddate, $skip_serialseq,
1349 my $rows = $sth->rows;
1351 logaction( "SERIAL", "MODIFY", $subscriptionid, "" ) if C4::Context->preference("SubscriptionLog");
1355 =head2 NewSubscription
1357 $subscriptionid = &NewSubscription($auser,branchcode,$aqbooksellerid,$cost,$aqbudgetid,$biblionumber,
1358 $startdate,$periodicity,$numberlength,$weeklength,$monthlength,
1359 $lastvalue1,$innerloop1,$lastvalue2,$innerloop2,$lastvalue3,$innerloop3,
1360 $status, $notes, $letter, $firstacquidate, $irregularity, $numberpattern,
1361 $locale, $callnumber, $manualhistory, $internalnotes, $serialsadditems,
1362 $staffdisplaycount, $opacdisplaycount, $graceperiod, $location, $enddate, $skip_serialseq);
1364 Create a new subscription with value given on input args.
1367 the id of this new subscription
1371 sub NewSubscription {
1373 $auser, $branchcode, $aqbooksellerid, $cost, $aqbudgetid, $biblionumber,
1374 $startdate, $periodicity, $numberlength, $weeklength, $monthlength,
1375 $lastvalue1, $innerloop1, $lastvalue2, $innerloop2, $lastvalue3,
1376 $innerloop3, $status, $notes, $letter, $firstacquidate, $irregularity,
1377 $numberpattern, $locale, $callnumber, $manualhistory, $internalnotes,
1378 $serialsadditems, $staffdisplaycount, $opacdisplaycount, $graceperiod,
1379 $location, $enddate, $skip_serialseq
1381 my $dbh = C4::Context->dbh;
1383 #save subscription (insert into database)
1385 INSERT INTO subscription
1386 (librarian, branchcode, aqbooksellerid, cost, aqbudgetid,
1387 biblionumber, startdate, periodicity, numberlength, weeklength,
1388 monthlength, lastvalue1, innerloop1, lastvalue2, innerloop2,
1389 lastvalue3, innerloop3, status, notes, letter, firstacquidate,
1390 irregularity, numberpattern, locale, callnumber,
1391 manualhistory, internalnotes, serialsadditems, staffdisplaycount,
1392 opacdisplaycount, graceperiod, location, enddate, skip_serialseq)
1393 VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
1395 my $sth = $dbh->prepare($query);
1397 $auser, $branchcode, $aqbooksellerid, $cost, $aqbudgetid, $biblionumber,
1398 $startdate, $periodicity, $numberlength, $weeklength,
1399 $monthlength, $lastvalue1, $innerloop1, $lastvalue2, $innerloop2,
1400 $lastvalue3, $innerloop3, $status, $notes, $letter,
1401 $firstacquidate, $irregularity, $numberpattern, $locale, $callnumber,
1402 $manualhistory, $internalnotes, $serialsadditems, $staffdisplaycount,
1403 $opacdisplaycount, $graceperiod, $location, $enddate, $skip_serialseq
1406 my $subscriptionid = $dbh->{'mysql_insertid'};
1408 $enddate = GetExpirationDate( $subscriptionid, $startdate );
1412 WHERE subscriptionid=?
1414 $sth = $dbh->prepare($query);
1415 $sth->execute( $enddate, $subscriptionid );
1418 # then create the 1st expected number
1420 INSERT INTO subscriptionhistory
1421 (biblionumber, subscriptionid, histstartdate)
1424 $sth = $dbh->prepare($query);
1425 $sth->execute( $biblionumber, $subscriptionid, $startdate);
1427 # reread subscription to get a hash (for calculation of the 1st issue number)
1428 my $subscription = GetSubscription($subscriptionid);
1429 my $pattern = C4::Serials::Numberpattern::GetSubscriptionNumberpattern($subscription->{numberpattern});
1431 # calculate issue number
1432 my $serialseq = GetSeq($subscription, $pattern) || q{};
1435 (serialseq,subscriptionid,biblionumber,status, planneddate, publisheddate)
1436 VALUES (?,?,?,?,?,?)
1438 $sth = $dbh->prepare($query);
1439 $sth->execute( $serialseq, $subscriptionid, $biblionumber, EXPECTED, $firstacquidate, $firstacquidate );
1441 logaction( "SERIAL", "ADD", $subscriptionid, "" ) if C4::Context->preference("SubscriptionLog");
1443 #set serial flag on biblio if not already set.
1444 my $bib = GetBiblio($biblionumber);
1445 if ( $bib and !$bib->{'serial'} ) {
1446 my $record = GetMarcBiblio($biblionumber);
1447 my ( $tag, $subf ) = GetMarcFromKohaField( 'biblio.serial', $bib->{'frameworkcode'} );
1449 eval { $record->field($tag)->update( $subf => 1 ); };
1451 ModBiblio( $record, $biblionumber, $bib->{'frameworkcode'} );
1453 return $subscriptionid;
1456 =head2 ReNewSubscription
1458 ReNewSubscription($subscriptionid,$user,$startdate,$numberlength,$weeklength,$monthlength,$note)
1460 this function renew a subscription with values given on input args.
1464 sub ReNewSubscription {
1465 my ( $subscriptionid, $user, $startdate, $numberlength, $weeklength, $monthlength, $note ) = @_;
1466 my $dbh = C4::Context->dbh;
1467 my $subscription = GetSubscription($subscriptionid);
1471 LEFT JOIN biblioitems ON biblio.biblionumber=biblioitems.biblionumber
1472 WHERE biblio.biblionumber=?
1474 my $sth = $dbh->prepare($query);
1475 $sth->execute( $subscription->{biblionumber} );
1476 my $biblio = $sth->fetchrow_hashref;
1478 if ( C4::Context->preference("RenewSerialAddsSuggestion") ) {
1479 require C4::Suggestions;
1480 C4::Suggestions::NewSuggestion(
1481 { 'suggestedby' => $user,
1482 'title' => $subscription->{bibliotitle},
1483 'author' => $biblio->{author},
1484 'publishercode' => $biblio->{publishercode},
1485 'note' => $biblio->{note},
1486 'biblionumber' => $subscription->{biblionumber}
1491 # renew subscription
1494 SET startdate=?,numberlength=?,weeklength=?,monthlength=?,reneweddate=NOW()
1495 WHERE subscriptionid=?
1497 $sth = $dbh->prepare($query);
1498 $sth->execute( $startdate, $numberlength, $weeklength, $monthlength, $subscriptionid );
1499 my $enddate = GetExpirationDate($subscriptionid);
1500 $debug && warn "enddate :$enddate";
1504 WHERE subscriptionid=?
1506 $sth = $dbh->prepare($query);
1507 $sth->execute( $enddate, $subscriptionid );
1509 UPDATE subscriptionhistory
1511 WHERE subscriptionid=?
1513 $sth = $dbh->prepare($query);
1514 $sth->execute( $enddate, $subscriptionid );
1516 logaction( "SERIAL", "RENEW", $subscriptionid, "" ) if C4::Context->preference("SubscriptionLog");
1522 NewIssue($serialseq,$subscriptionid,$biblionumber,$status, $planneddate, $publisheddate, $notes)
1524 Create a new issue stored on the database.
1525 Note : we have to update the recievedlist and missinglist on subscriptionhistory for this subscription.
1526 returns the serial id
1531 my ( $serialseq, $subscriptionid, $biblionumber, $status, $planneddate, $publisheddate, $notes ) = @_;
1532 ### FIXME biblionumber CAN be provided by subscriptionid. So Do we STILL NEED IT ?
1534 return unless ($subscriptionid);
1536 my $dbh = C4::Context->dbh;
1539 (serialseq,subscriptionid,biblionumber,status,publisheddate,planneddate,notes)
1540 VALUES (?,?,?,?,?,?,?)
1542 my $sth = $dbh->prepare($query);
1543 $sth->execute( $serialseq, $subscriptionid, $biblionumber, $status, $publisheddate, $planneddate, $notes );
1544 my $serialid = $dbh->{'mysql_insertid'};
1546 SELECT missinglist,recievedlist
1547 FROM subscriptionhistory
1548 WHERE subscriptionid=?
1550 $sth = $dbh->prepare($query);
1551 $sth->execute($subscriptionid);
1552 my ( $missinglist, $recievedlist ) = $sth->fetchrow;
1554 if ( $status == ARRIVED ) {
1555 ### TODO Add a feature that improves recognition and description.
1556 ### As such count (serialseq) i.e. : N18,2(N19),N20
1557 ### Would use substr and index But be careful to previous presence of ()
1558 $recievedlist .= "; $serialseq" unless (index($recievedlist,$serialseq)>0);
1560 if ( grep {/^$status$/} ( MISSING_STATUSES ) ) {
1561 $missinglist .= "; $serialseq" unless (index($missinglist,$serialseq)>0);
1564 UPDATE subscriptionhistory
1565 SET recievedlist=?, missinglist=?
1566 WHERE subscriptionid=?
1568 $sth = $dbh->prepare($query);
1569 $recievedlist =~ s/^; //;
1570 $missinglist =~ s/^; //;
1571 $sth->execute( $recievedlist, $missinglist, $subscriptionid );
1575 =head2 ItemizeSerials
1577 ItemizeSerials($serialid, $info);
1578 $info is a hashref containing barcode branch, itemcallnumber, status, location
1579 $serialid the serialid
1581 1 if the itemize is a succes.
1582 0 and @error otherwise. @error containts the list of errors found.
1586 sub ItemizeSerials {
1587 my ( $serialid, $info ) = @_;
1589 return unless ($serialid);
1591 my $now = POSIX::strftime( "%Y-%m-%d", localtime );
1593 my $dbh = C4::Context->dbh;
1599 my $sth = $dbh->prepare($query);
1600 $sth->execute($serialid);
1601 my $data = $sth->fetchrow_hashref;
1602 if ( C4::Context->preference("RoutingSerials") ) {
1604 # check for existing biblioitem relating to serial issue
1605 my ( $count, @results ) = GetBiblioItemByBiblioNumber( $data->{'biblionumber'} );
1607 for ( my $i = 0 ; $i < $count ; $i++ ) {
1608 if ( $results[$i]->{'volumeddesc'} eq $data->{'serialseq'} . ' (' . $data->{'planneddate'} . ')' ) {
1609 $bibitemno = $results[$i]->{'biblioitemnumber'};
1613 if ( $bibitemno == 0 ) {
1614 my $sth = $dbh->prepare( "SELECT * FROM biblioitems WHERE biblionumber = ? ORDER BY biblioitemnumber DESC" );
1615 $sth->execute( $data->{'biblionumber'} );
1616 my $biblioitem = $sth->fetchrow_hashref;
1617 $biblioitem->{'volumedate'} = $data->{planneddate};
1618 $biblioitem->{'volumeddesc'} = $data->{serialseq} . ' (' . format_date( $data->{'planneddate'} ) . ')';
1619 $biblioitem->{'dewey'} = $info->{itemcallnumber};
1623 my $fwk = GetFrameworkCode( $data->{'biblionumber'} );
1624 if ( $info->{barcode} ) {
1626 if ( is_barcode_in_use( $info->{barcode} ) ) {
1627 push @errors, 'barcode_not_unique';
1629 my $marcrecord = MARC::Record->new();
1630 my ( $tag, $subfield ) = GetMarcFromKohaField( "items.barcode", $fwk );
1631 my $newField = MARC::Field->new( "$tag", '', '', "$subfield" => $info->{barcode} );
1632 $marcrecord->insert_fields_ordered($newField);
1633 if ( $info->{branch} ) {
1634 my ( $tag, $subfield ) = GetMarcFromKohaField( "items.homebranch", $fwk );
1636 #warn "items.homebranch : $tag , $subfield";
1637 if ( $marcrecord->field($tag) ) {
1638 $marcrecord->field($tag)->add_subfields( "$subfield" => $info->{branch} );
1640 my $newField = MARC::Field->new( "$tag", '', '', "$subfield" => $info->{branch} );
1641 $marcrecord->insert_fields_ordered($newField);
1643 ( $tag, $subfield ) = GetMarcFromKohaField( "items.holdingbranch", $fwk );
1645 #warn "items.holdingbranch : $tag , $subfield";
1646 if ( $marcrecord->field($tag) ) {
1647 $marcrecord->field($tag)->add_subfields( "$subfield" => $info->{branch} );
1649 my $newField = MARC::Field->new( "$tag", '', '', "$subfield" => $info->{branch} );
1650 $marcrecord->insert_fields_ordered($newField);
1653 if ( $info->{itemcallnumber} ) {
1654 my ( $tag, $subfield ) = GetMarcFromKohaField( "items.itemcallnumber", $fwk );
1656 if ( $marcrecord->field($tag) ) {
1657 $marcrecord->field($tag)->add_subfields( "$subfield" => $info->{itemcallnumber} );
1659 my $newField = MARC::Field->new( "$tag", '', '', "$subfield" => $info->{itemcallnumber} );
1660 $marcrecord->insert_fields_ordered($newField);
1663 if ( $info->{notes} ) {
1664 my ( $tag, $subfield ) = GetMarcFromKohaField( "items.itemnotes", $fwk );
1666 if ( $marcrecord->field($tag) ) {
1667 $marcrecord->field($tag)->add_subfields( "$subfield" => $info->{notes} );
1669 my $newField = MARC::Field->new( "$tag", '', '', "$subfield" => $info->{notes} );
1670 $marcrecord->insert_fields_ordered($newField);
1673 if ( $info->{location} ) {
1674 my ( $tag, $subfield ) = GetMarcFromKohaField( "items.location", $fwk );
1676 if ( $marcrecord->field($tag) ) {
1677 $marcrecord->field($tag)->add_subfields( "$subfield" => $info->{location} );
1679 my $newField = MARC::Field->new( "$tag", '', '', "$subfield" => $info->{location} );
1680 $marcrecord->insert_fields_ordered($newField);
1683 if ( $info->{status} ) {
1684 my ( $tag, $subfield ) = GetMarcFromKohaField( "items.notforloan", $fwk );
1686 if ( $marcrecord->field($tag) ) {
1687 $marcrecord->field($tag)->add_subfields( "$subfield" => $info->{status} );
1689 my $newField = MARC::Field->new( "$tag", '', '', "$subfield" => $info->{status} );
1690 $marcrecord->insert_fields_ordered($newField);
1693 if ( C4::Context->preference("RoutingSerials") ) {
1694 my ( $tag, $subfield ) = GetMarcFromKohaField( "items.dateaccessioned", $fwk );
1695 if ( $marcrecord->field($tag) ) {
1696 $marcrecord->field($tag)->add_subfields( "$subfield" => $now );
1698 my $newField = MARC::Field->new( "$tag", '', '', "$subfield" => $now );
1699 $marcrecord->insert_fields_ordered($newField);
1703 C4::Items::AddItemFromMarc( $marcrecord, $data->{'biblionumber'} );
1706 return ( 0, @errors );
1710 =head2 HasSubscriptionStrictlyExpired
1712 1 or 0 = HasSubscriptionStrictlyExpired($subscriptionid)
1714 the subscription has stricly expired when today > the end subscription date
1717 1 if true, 0 if false, -1 if the expiration date is not set.
1721 sub HasSubscriptionStrictlyExpired {
1723 # Getting end of subscription date
1724 my ($subscriptionid) = @_;
1726 return unless ($subscriptionid);
1728 my $dbh = C4::Context->dbh;
1729 my $subscription = GetSubscription($subscriptionid);
1730 my $expirationdate = $subscription->{enddate} || GetExpirationDate($subscriptionid);
1732 # If the expiration date is set
1733 if ( $expirationdate != 0 ) {
1734 my ( $endyear, $endmonth, $endday ) = split( '-', $expirationdate );
1736 # Getting today's date
1737 my ( $nowyear, $nowmonth, $nowday ) = Today();
1739 # if today's date > expiration date, then the subscription has stricly expired
1740 if ( Delta_Days( $nowyear, $nowmonth, $nowday, $endyear, $endmonth, $endday ) < 0 ) {
1747 # There are some cases where the expiration date is not set
1748 # As we can't determine if the subscription has expired on a date-basis,
1754 =head2 HasSubscriptionExpired
1756 $has_expired = HasSubscriptionExpired($subscriptionid)
1758 the subscription has expired when the next issue to arrive is out of subscription limit.
1761 0 if the subscription has not expired
1762 1 if the subscription has expired
1763 2 if has subscription does not have a valid expiration date set
1767 sub HasSubscriptionExpired {
1768 my ($subscriptionid) = @_;
1770 return unless ($subscriptionid);
1772 my $dbh = C4::Context->dbh;
1773 my $subscription = GetSubscription($subscriptionid);
1774 my $frequency = C4::Serials::Frequency::GetSubscriptionFrequency($subscription->{periodicity});
1775 if ( $frequency and $frequency->{unit} ) {
1776 my $expirationdate = $subscription->{enddate} || GetExpirationDate($subscriptionid);
1777 if (!defined $expirationdate) {
1778 $expirationdate = q{};
1781 SELECT max(planneddate)
1783 WHERE subscriptionid=?
1785 my $sth = $dbh->prepare($query);
1786 $sth->execute($subscriptionid);
1787 my ($res) = $sth->fetchrow;
1788 if (!$res || $res=~m/^0000/) {
1791 my @res = split( /-/, $res );
1792 my @endofsubscriptiondate = split( /-/, $expirationdate );
1793 return 2 if ( scalar(@res) != 3 || scalar(@endofsubscriptiondate) != 3 || not check_date(@res) || not check_date(@endofsubscriptiondate) );
1795 if ( ( @endofsubscriptiondate && Delta_Days( $res[0], $res[1], $res[2], $endofsubscriptiondate[0], $endofsubscriptiondate[1], $endofsubscriptiondate[2] ) <= 0 )
1800 if ( $subscription->{'numberlength'} ) {
1801 my $countreceived = countissuesfrom( $subscriptionid, $subscription->{'startdate'} );
1802 return 1 if ( $countreceived > $subscription->{'numberlength'} );
1808 return 0; # Notice that you'll never get here.
1811 =head2 SetDistributedto
1813 SetDistributedto($distributedto,$subscriptionid);
1814 This function update the value of distributedto for a subscription given on input arg.
1818 sub SetDistributedto {
1819 my ( $distributedto, $subscriptionid ) = @_;
1820 my $dbh = C4::Context->dbh;
1824 WHERE subscriptionid=?
1826 my $sth = $dbh->prepare($query);
1827 $sth->execute( $distributedto, $subscriptionid );
1831 =head2 DelSubscription
1833 DelSubscription($subscriptionid)
1834 this function deletes subscription which has $subscriptionid as id.
1838 sub DelSubscription {
1839 my ($subscriptionid) = @_;
1840 my $dbh = C4::Context->dbh;
1841 $subscriptionid = $dbh->quote($subscriptionid);
1842 $dbh->do("DELETE FROM subscription WHERE subscriptionid=$subscriptionid");
1843 $dbh->do("DELETE FROM subscriptionhistory WHERE subscriptionid=$subscriptionid");
1844 $dbh->do("DELETE FROM serial WHERE subscriptionid=$subscriptionid");
1846 logaction( "SERIAL", "DELETE", $subscriptionid, "" ) if C4::Context->preference("SubscriptionLog");
1851 DelIssue($serialseq,$subscriptionid)
1852 this function deletes an issue which has $serialseq and $subscriptionid given on input arg.
1854 returns the number of rows affected
1859 my ($dataissue) = @_;
1860 my $dbh = C4::Context->dbh;
1861 ### TODO Add itemdeletion. Would need to get itemnumbers. Should be in a pref ?
1866 AND subscriptionid= ?
1868 my $mainsth = $dbh->prepare($query);
1869 $mainsth->execute( $dataissue->{'serialid'}, $dataissue->{'subscriptionid'} );
1871 #Delete element from subscription history
1872 $query = "SELECT * FROM subscription WHERE subscriptionid = ?";
1873 my $sth = $dbh->prepare($query);
1874 $sth->execute( $dataissue->{'subscriptionid'} );
1875 my $val = $sth->fetchrow_hashref;
1876 unless ( $val->{manualhistory} ) {
1878 SELECT * FROM subscriptionhistory
1879 WHERE subscriptionid= ?
1881 my $sth = $dbh->prepare($query);
1882 $sth->execute( $dataissue->{'subscriptionid'} );
1883 my $data = $sth->fetchrow_hashref;
1884 my $serialseq = $dataissue->{'serialseq'};
1885 $data->{'missinglist'} =~ s/\b$serialseq\b//;
1886 $data->{'recievedlist'} =~ s/\b$serialseq\b//;
1887 my $strsth = "UPDATE subscriptionhistory SET " . join( ",", map { join( "=", $_, $dbh->quote( $data->{$_} ) ) } keys %$data ) . " WHERE subscriptionid=?";
1888 $sth = $dbh->prepare($strsth);
1889 $sth->execute( $dataissue->{'subscriptionid'} );
1892 return $mainsth->rows;
1895 =head2 GetLateOrMissingIssues
1897 @issuelist = GetLateMissingIssues($supplierid,$serialid)
1899 this function selects missing issues on database - where serial.status = MISSING* or serial.status = LATE or planneddate<now
1902 the issuelist as an array of hash refs. Each element of this array contains
1903 name,title,planneddate,serialseq,serial.subscriptionid from tables : subscription, serial & biblio
1907 sub GetLateOrMissingIssues {
1908 my ( $supplierid, $serialid, $order ) = @_;
1910 return unless ( $supplierid or $serialid );
1912 my $dbh = C4::Context->dbh;
1916 $byserial = "and serialid = " . $serialid;
1919 $order .= ", title";
1923 my $missing_statuses_string = join ',', (MISSING_STATUSES);
1925 $sth = $dbh->prepare(
1927 serialid, aqbooksellerid, name,
1928 biblio.title, biblioitems.issn, planneddate, serialseq,
1929 serial.status, serial.subscriptionid, claimdate, claims_count,
1930 subscription.branchcode
1932 LEFT JOIN subscription ON serial.subscriptionid=subscription.subscriptionid
1933 LEFT JOIN biblio ON subscription.biblionumber=biblio.biblionumber
1934 LEFT JOIN biblioitems ON subscription.biblionumber=biblioitems.biblionumber
1935 LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id
1936 WHERE subscription.subscriptionid = serial.subscriptionid
1937 AND (serial.STATUS IN ($missing_statuses_string) OR ((planneddate < now() AND serial.STATUS = ?) OR serial.STATUS = ? OR serial.STATUS = ?))
1938 AND subscription.aqbooksellerid=$supplierid
1943 $sth = $dbh->prepare(
1945 serialid, aqbooksellerid, name,
1946 biblio.title, planneddate, serialseq,
1947 serial.status, serial.subscriptionid, claimdate, claims_count,
1948 subscription.branchcode
1950 LEFT JOIN subscription ON serial.subscriptionid=subscription.subscriptionid
1951 LEFT JOIN biblio ON subscription.biblionumber=biblio.biblionumber
1952 LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id
1953 WHERE subscription.subscriptionid = serial.subscriptionid
1954 AND (serial.STATUS IN ($missing_statuses_string) OR ((planneddate < now() AND serial.STATUS = ?) OR serial.STATUS = ? OR serial.STATUS = ?))
1959 $sth->execute( EXPECTED, LATE, CLAIMED );
1961 while ( my $line = $sth->fetchrow_hashref ) {
1963 if ($line->{planneddate} && $line->{planneddate} !~/^0+\-/) {
1964 $line->{planneddateISO} = $line->{planneddate};
1965 $line->{planneddate} = format_date( $line->{planneddate} );
1967 if ($line->{claimdate} && $line->{claimdate} !~/^0+\-/) {
1968 $line->{claimdateISO} = $line->{claimdate};
1969 $line->{claimdate} = format_date( $line->{claimdate} );
1971 $line->{"status".$line->{status}} = 1;
1972 push @issuelist, $line;
1979 &updateClaim($serialid)
1981 this function updates the time when a claim is issued for late/missing items
1983 called from claims.pl file
1988 my ($serialid) = @_;
1989 my $dbh = C4::Context->dbh;
1992 SET claimdate = NOW(),
1993 claims_count = claims_count + 1
1999 =head2 getsupplierbyserialid
2001 $result = getsupplierbyserialid($serialid)
2003 this function is used to find the supplier id given a serial id
2006 hashref containing serialid, subscriptionid, and aqbooksellerid
2010 sub getsupplierbyserialid {
2011 my ($serialid) = @_;
2012 my $dbh = C4::Context->dbh;
2013 my $sth = $dbh->prepare(
2014 "SELECT serialid, serial.subscriptionid, aqbooksellerid
2016 LEFT JOIN subscription ON serial.subscriptionid = subscription.subscriptionid
2020 $sth->execute($serialid);
2021 my $line = $sth->fetchrow_hashref;
2022 my $result = $line->{'aqbooksellerid'};
2026 =head2 check_routing
2028 $result = &check_routing($subscriptionid)
2030 this function checks to see if a serial has a routing list and returns the count of routingid
2031 used to show either an 'add' or 'edit' link
2036 my ($subscriptionid) = @_;
2038 return unless ($subscriptionid);
2040 my $dbh = C4::Context->dbh;
2041 my $sth = $dbh->prepare(
2042 "SELECT count(routingid) routingids FROM subscription LEFT JOIN subscriptionroutinglist
2043 ON subscription.subscriptionid = subscriptionroutinglist.subscriptionid
2044 WHERE subscription.subscriptionid = ? ORDER BY ranking ASC
2047 $sth->execute($subscriptionid);
2048 my $line = $sth->fetchrow_hashref;
2049 my $result = $line->{'routingids'};
2053 =head2 addroutingmember
2055 addroutingmember($borrowernumber,$subscriptionid)
2057 this function takes a borrowernumber and subscriptionid and adds the member to the
2058 routing list for that serial subscription and gives them a rank on the list
2059 of either 1 or highest current rank + 1
2063 sub addroutingmember {
2064 my ( $borrowernumber, $subscriptionid ) = @_;
2066 return unless ($borrowernumber and $subscriptionid);
2069 my $dbh = C4::Context->dbh;
2070 my $sth = $dbh->prepare( "SELECT max(ranking) rank FROM subscriptionroutinglist WHERE subscriptionid = ?" );
2071 $sth->execute($subscriptionid);
2072 while ( my $line = $sth->fetchrow_hashref ) {
2073 if ( $line->{'rank'} > 0 ) {
2074 $rank = $line->{'rank'} + 1;
2079 $sth = $dbh->prepare( "INSERT INTO subscriptionroutinglist (subscriptionid,borrowernumber,ranking) VALUES (?,?,?)" );
2080 $sth->execute( $subscriptionid, $borrowernumber, $rank );
2083 =head2 reorder_members
2085 reorder_members($subscriptionid,$routingid,$rank)
2087 this function is used to reorder the routing list
2089 it takes the routingid of the member one wants to re-rank and the rank it is to move to
2090 - it gets all members on list puts their routingid's into an array
2091 - removes the one in the array that is $routingid
2092 - then reinjects $routingid at point indicated by $rank
2093 - then update the database with the routingids in the new order
2097 sub reorder_members {
2098 my ( $subscriptionid, $routingid, $rank ) = @_;
2099 my $dbh = C4::Context->dbh;
2100 my $sth = $dbh->prepare( "SELECT * FROM subscriptionroutinglist WHERE subscriptionid = ? ORDER BY ranking ASC" );
2101 $sth->execute($subscriptionid);
2103 while ( my $line = $sth->fetchrow_hashref ) {
2104 push( @result, $line->{'routingid'} );
2107 # To find the matching index
2109 my $key = -1; # to allow for 0 being a valid response
2110 for ( $i = 0 ; $i < @result ; $i++ ) {
2111 if ( $routingid == $result[$i] ) {
2112 $key = $i; # save the index
2117 # if index exists in array then move it to new position
2118 if ( $key > -1 && $rank > 0 ) {
2119 my $new_rank = $rank - 1; # $new_rank is what you want the new index to be in the array
2120 my $moving_item = splice( @result, $key, 1 );
2121 splice( @result, $new_rank, 0, $moving_item );
2123 for ( my $j = 0 ; $j < @result ; $j++ ) {
2124 my $sth = $dbh->prepare( "UPDATE subscriptionroutinglist SET ranking = '" . ( $j + 1 ) . "' WHERE routingid = '" . $result[$j] . "'" );
2130 =head2 delroutingmember
2132 delroutingmember($routingid,$subscriptionid)
2134 this function either deletes one member from routing list if $routingid exists otherwise
2135 deletes all members from the routing list
2139 sub delroutingmember {
2141 # if $routingid exists then deletes that row otherwise deletes all with $subscriptionid
2142 my ( $routingid, $subscriptionid ) = @_;
2143 my $dbh = C4::Context->dbh;
2145 my $sth = $dbh->prepare("DELETE FROM subscriptionroutinglist WHERE routingid = ?");
2146 $sth->execute($routingid);
2147 reorder_members( $subscriptionid, $routingid );
2149 my $sth = $dbh->prepare("DELETE FROM subscriptionroutinglist WHERE subscriptionid = ?");
2150 $sth->execute($subscriptionid);
2155 =head2 getroutinglist
2157 @routinglist = getroutinglist($subscriptionid)
2159 this gets the info from the subscriptionroutinglist for $subscriptionid
2162 the routinglist as an array. Each element of the array contains a hash_ref containing
2163 routingid - a unique id, borrowernumber, ranking, and biblionumber of subscription
2167 sub getroutinglist {
2168 my ($subscriptionid) = @_;
2169 my $dbh = C4::Context->dbh;
2170 my $sth = $dbh->prepare(
2171 'SELECT routingid, borrowernumber, ranking, biblionumber
2173 JOIN subscriptionroutinglist ON subscription.subscriptionid = subscriptionroutinglist.subscriptionid
2174 WHERE subscription.subscriptionid = ? ORDER BY ranking ASC'
2176 $sth->execute($subscriptionid);
2177 my $routinglist = $sth->fetchall_arrayref({});
2178 return @{$routinglist};
2181 =head2 countissuesfrom
2183 $result = countissuesfrom($subscriptionid,$startdate)
2185 Returns a count of serial rows matching the given subsctiptionid
2186 with published date greater than startdate
2190 sub countissuesfrom {
2191 my ( $subscriptionid, $startdate ) = @_;
2192 my $dbh = C4::Context->dbh;
2196 WHERE subscriptionid=?
2197 AND serial.publisheddate>?
2199 my $sth = $dbh->prepare($query);
2200 $sth->execute( $subscriptionid, $startdate );
2201 my ($countreceived) = $sth->fetchrow;
2202 return $countreceived;
2207 $result = CountIssues($subscriptionid)
2209 Returns a count of serial rows matching the given subsctiptionid
2214 my ($subscriptionid) = @_;
2215 my $dbh = C4::Context->dbh;
2219 WHERE subscriptionid=?
2221 my $sth = $dbh->prepare($query);
2222 $sth->execute($subscriptionid);
2223 my ($countreceived) = $sth->fetchrow;
2224 return $countreceived;
2229 $result = HasItems($subscriptionid)
2231 returns a count of items from serial matching the subscriptionid
2236 my ($subscriptionid) = @_;
2237 my $dbh = C4::Context->dbh;
2239 SELECT COUNT(serialitems.itemnumber)
2241 LEFT JOIN serialitems USING(serialid)
2242 WHERE subscriptionid=? AND serialitems.serialid IS NOT NULL
2244 my $sth=$dbh->prepare($query);
2245 $sth->execute($subscriptionid);
2246 my ($countitems)=$sth->fetchrow_array();
2250 =head2 abouttoexpire
2252 $result = abouttoexpire($subscriptionid)
2254 this function alerts you to the penultimate issue for a serial subscription
2256 returns 1 - if this is the penultimate issue
2262 my ($subscriptionid) = @_;
2263 my $dbh = C4::Context->dbh;
2264 my $subscription = GetSubscription($subscriptionid);
2265 my $per = $subscription->{'periodicity'};
2266 my $frequency = C4::Serials::Frequency::GetSubscriptionFrequency($per);
2267 if ($frequency and $frequency->{unit}){
2269 my $expirationdate = GetExpirationDate($subscriptionid);
2271 my ($res) = $dbh->selectrow_array('select max(planneddate) from serial where subscriptionid = ?', undef, $subscriptionid);
2272 my $nextdate = GetNextDate($subscription, $res);
2274 # only compare dates if both dates exist.
2275 if ($nextdate and $expirationdate) {
2276 if(Date::Calc::Delta_Days(
2277 split( /-/, $nextdate ),
2278 split( /-/, $expirationdate )
2284 } elsif ($subscription->{numberlength}>0) {
2285 return (countissuesfrom($subscriptionid,$subscription->{'startdate'}) >=$subscription->{numberlength}-1);
2291 sub in_array { # used in next sub down
2292 my ( $val, @elements ) = @_;
2293 foreach my $elem (@elements) {
2294 if ( $val == $elem ) {
2301 =head2 GetSubscriptionsFromBorrower
2303 ($count,@routinglist) = GetSubscriptionsFromBorrower($borrowernumber)
2305 this gets the info from subscriptionroutinglist for each $subscriptionid
2308 a count of the serial subscription routing lists to which a patron belongs,
2309 with the titles of those serial subscriptions as an array. Each element of the array
2310 contains a hash_ref with subscriptionID and title of subscription.
2314 sub GetSubscriptionsFromBorrower {
2315 my ($borrowernumber) = @_;
2316 my $dbh = C4::Context->dbh;
2317 my $sth = $dbh->prepare(
2318 "SELECT subscription.subscriptionid, biblio.title
2320 JOIN biblio ON biblio.biblionumber = subscription.biblionumber
2321 JOIN subscriptionroutinglist USING (subscriptionid)
2322 WHERE subscriptionroutinglist.borrowernumber = ? ORDER BY title ASC
2325 $sth->execute($borrowernumber);
2328 while ( my $line = $sth->fetchrow_hashref ) {
2330 push( @routinglist, $line );
2332 return ( $count, @routinglist );
2336 =head2 GetFictiveIssueNumber
2338 $issueno = GetFictiveIssueNumber($subscription, $publishedate);
2340 Get the position of the issue published at $publisheddate, considering the
2341 first issue (at firstacquidate) is at position 1, the next is at position 2, etc...
2342 This issuenumber doesn't take into account irregularities, so, for instance, if the 3rd
2343 issue is declared as 'irregular' (will be skipped at receipt), the next issue number
2344 will be 4, not 3. It's why it is called 'fictive'. It is NOT a serial seq, and is not
2345 depending on how many rows are in serial table.
2346 The issue number calculation is based on subscription frequency, first acquisition
2347 date, and $publisheddate.
2351 sub GetFictiveIssueNumber {
2352 my ($subscription, $publisheddate) = @_;
2354 my $frequency = GetSubscriptionFrequency($subscription->{'periodicity'});
2355 my $unit = $frequency->{unit} ? lc $frequency->{'unit'} : undef;
2359 my ($year, $month, $day) = split /-/, $publisheddate;
2360 my ($fa_year, $fa_month, $fa_day) = split /-/, $subscription->{'firstacquidate'};
2364 if($unit eq 'day') {
2365 $delta = Delta_Days($fa_year, $fa_month, $fa_day, $year, $month, $day);
2366 } elsif($unit eq 'week') {
2367 ($wkno, $year) = Week_of_Year($year, $month, $day);
2368 my ($fa_wkno, $fa_yr) = Week_of_Year($fa_year, $fa_month, $fa_day);
2369 $delta = ($fa_yr == $year) ? ($wkno - $fa_wkno) : ( ($year-$fa_yr-1)*52 + (52-$fa_wkno+$wkno) );
2370 } elsif($unit eq 'month') {
2371 $delta = ($fa_year == $year)
2372 ? ($month - $fa_month)
2373 : ( ($year-$fa_year-1)*12 + (12-$fa_month+$month) );
2374 } elsif($unit eq 'year') {
2375 $delta = $year - $fa_year;
2377 if($frequency->{'unitsperissue'} == 1) {
2378 $issueno = $delta * $frequency->{'issuesperunit'} + $subscription->{'countissuesperunit'};
2380 # Assuming issuesperunit == 1
2381 $issueno = int( ($delta + $frequency->{'unitsperissue'}) / $frequency->{'unitsperissue'} );
2387 sub _get_next_date_day {
2388 my ($subscription, $freqdata, $year, $month, $day) = @_;
2390 if ($subscription->{countissuesperunit} + 1 > $freqdata->{issuesperunit}){
2391 ($year,$month,$day) = Add_Delta_Days($year,$month, $day , $freqdata->{unitsperissue} );
2392 $subscription->{countissuesperunit} = 1;
2394 $subscription->{countissuesperunit}++;
2397 return ($year, $month, $day);
2400 sub _get_next_date_week {
2401 my ($subscription, $freqdata, $year, $month, $day) = @_;
2403 my ($wkno, $yr) = Week_of_Year($year, $month, $day);
2404 my $fa_dow = Day_of_Week(split /-/, $subscription->{firstacquidate});
2406 if ($subscription->{countissuesperunit} + 1 > $freqdata->{issuesperunit}){
2407 $subscription->{countissuesperunit} = 1;
2408 $wkno += $freqdata->{unitsperissue};
2413 ($year,$month,$day) = Monday_of_Week($wkno, $yr);
2414 ($year,$month,$day) = Add_Delta_Days($year, $month, $day, $fa_dow - 1);
2416 # Try to guess the next day of week
2417 my $delta_days = int((7 - ($fa_dow - 1)) / $freqdata->{issuesperunit});
2418 ($year,$month,$day) = Add_Delta_Days($year, $month, $day, $delta_days);
2419 $subscription->{countissuesperunit}++;
2422 return ($year, $month, $day);
2425 sub _get_next_date_month {
2426 my ($subscription, $freqdata, $year, $month, $day) = @_;
2429 (undef, undef, $fa_day) = split /-/, $subscription->{firstacquidate};
2431 if ($subscription->{countissuesperunit} + 1 > $freqdata->{issuesperunit}){
2432 $subscription->{countissuesperunit} = 1;
2433 ($year,$month,$day) = Add_Delta_YM($year,$month,$day, 0,
2434 $freqdata->{unitsperissue});
2435 my $days_in_month = Days_in_Month($year, $month);
2436 $day = $fa_day <= $days_in_month ? $fa_day : $days_in_month;
2438 # Try to guess the next day in month
2439 my $days_in_month = Days_in_Month($year, $month);
2440 my $delta_days = int(($days_in_month - ($fa_day - 1)) / $freqdata->{issuesperunit});
2441 ($year,$month,$day) = Add_Delta_Days($year, $month, $day, $delta_days);
2442 $subscription->{countissuesperunit}++;
2445 return ($year, $month, $day);
2448 sub _get_next_date_year {
2449 my ($subscription, $freqdata, $year, $month, $day) = @_;
2451 my ($fa_year, $fa_month, $fa_day) = split /-/, $subscription->{firstacquidate};
2453 if ($subscription->{countissuesperunit} + 1 > $freqdata->{issuesperunit}){
2454 $subscription->{countissuesperunit} = 1;
2455 ($year) = Add_Delta_YM($year,$month,$day, $freqdata->{"unitsperissue"},0);
2457 my $days_in_month = Days_in_Month($year, $month);
2458 $day = $fa_day <= $days_in_month ? $fa_day : $days_in_month;
2460 # Try to guess the next day in year
2461 my $days_in_year = Days_in_Year($year,12); #Sum the days of all the months of this year
2462 my $delta_days = int(($days_in_year - ($fa_day - 1)) / $freqdata->{issuesperunit});
2463 ($year,$month,$day) = Add_Delta_Days($year, $month, $day, $delta_days);
2464 $subscription->{countissuesperunit}++;
2467 return ($year, $month, $day);
2472 $resultdate = GetNextDate($publisheddate,$subscription)
2474 this function it takes the publisheddate and will return the next issue's date
2475 and will skip dates if there exists an irregularity.
2476 $publisheddate has to be an ISO date
2477 $subscription is a hashref containing at least 'periodicity', 'firstacquidate', 'irregularity', and 'countissuesperunit'
2478 $updatecount is a boolean value which, when set to true, update the 'countissuesperunit' in database
2479 - eg if periodicity is monthly and $publisheddate is 2007-02-10 but if March and April is to be
2480 skipped then the returned date will be 2007-05-10
2483 $resultdate - then next date in the sequence (ISO date)
2485 Return undef if subscription is irregular
2490 my ( $subscription, $publisheddate, $updatecount ) = @_;
2492 return unless $subscription and $publisheddate;
2494 my $freqdata = GetSubscriptionFrequency($subscription->{'periodicity'});
2496 if ($freqdata->{'unit'}) {
2497 my ( $year, $month, $day ) = split /-/, $publisheddate;
2499 # Process an irregularity Hash
2500 # Suppose that irregularities are stored in a string with this structure
2501 # irreg1;irreg2;irreg3
2502 # where irregX is the number of issue which will not be received
2503 # (the first issue takes the number 1, the 2nd the number 2 and so on)
2505 if ( $subscription->{irregularity} ) {
2506 my @irreg = split /;/, $subscription->{'irregularity'} ;
2507 foreach my $irregularity (@irreg) {
2508 $irregularities{$irregularity} = 1;
2512 # Get the 'fictive' next issue number
2513 # It is used to check if next issue is an irregular issue.
2514 my $issueno = GetFictiveIssueNumber($subscription, $publisheddate) + 1;
2516 # Then get the next date
2517 my $unit = lc $freqdata->{'unit'};
2518 if ($unit eq 'day') {
2519 while ($irregularities{$issueno}) {
2520 ($year, $month, $day) = _get_next_date_day($subscription,
2521 $freqdata, $year, $month, $day);
2524 ($year, $month, $day) = _get_next_date_day($subscription, $freqdata,
2525 $year, $month, $day);
2527 elsif ($unit eq 'week') {
2528 while ($irregularities{$issueno}) {
2529 ($year, $month, $day) = _get_next_date_week($subscription,
2530 $freqdata, $year, $month, $day);
2533 ($year, $month, $day) = _get_next_date_week($subscription,
2534 $freqdata, $year, $month, $day);
2536 elsif ($unit eq 'month') {
2537 while ($irregularities{$issueno}) {
2538 ($year, $month, $day) = _get_next_date_month($subscription,
2539 $freqdata, $year, $month, $day);
2542 ($year, $month, $day) = _get_next_date_month($subscription,
2543 $freqdata, $year, $month, $day);
2545 elsif ($unit eq 'year') {
2546 while ($irregularities{$issueno}) {
2547 ($year, $month, $day) = _get_next_date_year($subscription,
2548 $freqdata, $year, $month, $day);
2551 ($year, $month, $day) = _get_next_date_year($subscription,
2552 $freqdata, $year, $month, $day);
2556 my $dbh = C4::Context->dbh;
2559 SET countissuesperunit = ?
2560 WHERE subscriptionid = ?
2562 my $sth = $dbh->prepare($query);
2563 $sth->execute($subscription->{'countissuesperunit'}, $subscription->{'subscriptionid'});
2566 return sprintf("%04d-%02d-%02d", $year, $month, $day);
2572 $string = &_numeration($value,$num_type,$locale);
2574 _numeration returns the string corresponding to $value in the num_type
2584 my ($value, $num_type, $locale) = @_;
2589 if ( $num_type =~ /^dayname$/ ) {
2590 # 1970-11-01 was a Sunday
2591 $value = $value % 7;
2592 my $dt = DateTime->new(
2598 $string = $dt->strftime("%A");
2599 } elsif ( $num_type =~ /^monthname$/ ) {
2600 $value = $value % 12;
2601 my $dt = DateTime->new(
2603 month => $value + 1,
2606 $string = $dt->strftime("%B");
2607 } elsif ( $num_type =~ /^season$/ ) {
2608 my @seasons= qw( Spring Summer Fall Winter );
2609 $value = $value % 4;
2610 $string = $seasons[$value];
2618 =head2 is_barcode_in_use
2620 Returns number of occurrences of the barcode in the items table
2621 Can be used as a boolean test of whether the barcode has
2622 been deployed as yet
2626 sub is_barcode_in_use {
2627 my $barcode = shift;
2628 my $dbh = C4::Context->dbh;
2629 my $occurrences = $dbh->selectall_arrayref(
2630 'SELECT itemnumber from items where barcode = ?',
2635 return @{$occurrences};
2638 =head2 CloseSubscription
2639 Close a subscription given a subscriptionid
2641 sub CloseSubscription {
2642 my ( $subscriptionid ) = @_;
2643 return unless $subscriptionid;
2644 my $dbh = C4::Context->dbh;
2645 my $sth = $dbh->prepare( q{
2648 WHERE subscriptionid = ?
2650 $sth->execute( $subscriptionid );
2652 # Set status = missing when status = stopped
2653 $sth = $dbh->prepare( q{
2656 WHERE subscriptionid = ?
2659 $sth->execute( STOPPED, $subscriptionid, EXPECTED );
2662 =head2 ReopenSubscription
2663 Reopen a subscription given a subscriptionid
2665 sub ReopenSubscription {
2666 my ( $subscriptionid ) = @_;
2667 return unless $subscriptionid;
2668 my $dbh = C4::Context->dbh;
2669 my $sth = $dbh->prepare( q{
2672 WHERE subscriptionid = ?
2674 $sth->execute( $subscriptionid );
2676 # Set status = expected when status = stopped
2677 $sth = $dbh->prepare( q{
2680 WHERE subscriptionid = ?
2683 $sth->execute( EXPECTED, $subscriptionid, STOPPED );
2686 =head2 subscriptionCurrentlyOnOrder
2688 $bool = subscriptionCurrentlyOnOrder( $subscriptionid );
2690 Return 1 if subscription is currently on order else 0.
2694 sub subscriptionCurrentlyOnOrder {
2695 my ( $subscriptionid ) = @_;
2696 my $dbh = C4::Context->dbh;
2698 SELECT COUNT(*) FROM aqorders
2699 WHERE subscriptionid = ?
2700 AND datereceived IS NULL
2701 AND datecancellationprinted IS NULL
2703 my $sth = $dbh->prepare( $query );
2704 $sth->execute($subscriptionid);
2705 return $sth->fetchrow_array;
2708 =head2 can_edit_subscription
2710 $can = can_edit_subscription( $subscriptionid[, $userid] );
2712 Return 1 if the subscription can be edited by the current logged user (or a given $userid), else 0.
2716 sub can_edit_subscription {
2717 my ( $subscription, $userid ) = @_;
2718 return _can_do_on_subscription( $subscription, $userid, 'edit_subscription' );
2721 =head2 can_show_subscription
2723 $can = can_show_subscription( $subscriptionid[, $userid] );
2725 Return 1 if the subscription can be shown by the current logged user (or a given $userid), else 0.
2729 sub can_show_subscription {
2730 my ( $subscription, $userid ) = @_;
2731 return _can_do_on_subscription( $subscription, $userid, '*' );
2734 sub _can_do_on_subscription {
2735 my ( $subscription, $userid, $permission ) = @_;
2736 return 0 unless C4::Context->userenv;
2737 my $flags = C4::Context->userenv->{flags};
2738 $userid ||= C4::Context->userenv->{'id'};
2740 if ( C4::Context->preference('IndependentBranches') ) {
2742 if C4::Context->IsSuperLibrarian()
2744 C4::Auth::haspermission( $userid, { serials => 'superserials' } )
2746 C4::Auth::haspermission( $userid,
2747 { serials => $permission } )
2748 and ( not defined $subscription->{branchcode}
2749 or $subscription->{branchcode} eq ''
2750 or $subscription->{branchcode} eq
2751 C4::Context->userenv->{'branch'} )
2756 if C4::Context->IsSuperLibrarian()
2758 C4::Auth::haspermission( $userid, { serials => 'superserials' } )
2759 or C4::Auth::haspermission(
2760 $userid, { serials => $permission }
2772 Koha Development Team <http://koha-community.org/>