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 Koha
::Subscriptions
;
37 use Koha
::Subscription
::Histories
;
39 use vars
qw(@ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
47 MISSING_NEVER_RECIEVED => 41,
48 MISSING_SOLD_OUT => 42,
49 MISSING_DAMAGED => 43,
57 use constant MISSING_STATUSES => (
58 MISSING, MISSING_NEVER_RECIEVED,
59 MISSING_SOLD_OUT, MISSING_DAMAGED,
67 &NewSubscription &ModSubscription &DelSubscription
68 &GetSubscription &CountSubscriptionFromBiblionumber &GetSubscriptionsFromBiblionumber
70 &GetFullSubscriptionsFromBiblionumber &GetFullSubscription &ModSubscriptionHistory
71 &HasSubscriptionStrictlyExpired &HasSubscriptionExpired &GetExpirationDate &abouttoexpire
72 &GetSubscriptionHistoryFromSubscriptionId
74 &GetNextSeq &GetSeq &NewIssue &GetSerials
75 &GetLatestSerials &ModSerialStatus &GetNextDate &GetSerials2
76 &ReNewSubscription &GetLateOrMissingIssues
77 &GetSerialInformation &AddItem2Serial
78 &PrepareSerialsData &GetNextExpected &ModNextExpected
80 &UpdateClaimdateIssues
81 &GetSuppliersWithLateIssues &getsupplierbyserialid
82 &GetDistributedTo &SetDistributedTo
83 &getroutinglist &delroutingmember &addroutingmember
85 &check_routing &updateClaim
88 &GetSubscriptionsFromBorrower
89 &subscriptionCurrentlyOnOrder
96 C4::Serials - Serials Module Functions
104 Functions for handling subscriptions, claims routing etc.
109 =head2 GetSuppliersWithLateIssues
111 $supplierlist = GetSuppliersWithLateIssues()
113 this function get all suppliers with late issues.
116 an array_ref of suppliers each entry is a hash_ref containing id and name
117 the array is in name order
121 sub GetSuppliersWithLateIssues
{
122 my $dbh = C4
::Context
->dbh;
123 my $statuses = join(',', ( LATE
, MISSING_STATUSES
, CLAIMED
) );
125 SELECT DISTINCT id
, name
127 LEFT JOIN serial ON serial
.subscriptionid
=subscription
.subscriptionid
128 LEFT JOIN aqbooksellers ON subscription
.aqbooksellerid
= aqbooksellers
.id
131 (planneddate
< now
() AND serial
.status
=1)
132 OR serial
.STATUS IN
( $statuses )
134 AND subscription
.closed
= 0
136 return $dbh->selectall_arrayref($query, { Slice
=> {} });
139 =head2 GetSubscriptionHistoryFromSubscriptionId
141 $history = GetSubscriptionHistoryFromSubscriptionId($subscriptionid);
143 This function returns the subscription history as a hashref
147 sub GetSubscriptionHistoryFromSubscriptionId
{
148 my ($subscriptionid) = @_;
150 return unless $subscriptionid;
152 my $dbh = C4
::Context
->dbh;
155 FROM subscriptionhistory
156 WHERE subscriptionid
= ?
158 my $sth = $dbh->prepare($query);
159 $sth->execute($subscriptionid);
160 my $results = $sth->fetchrow_hashref;
166 =head2 GetSerialStatusFromSerialId
168 $sth = GetSerialStatusFromSerialId();
169 this function returns a statement handle
170 After this function, don't forget to execute it by using $sth->execute($serialid)
172 $sth = $dbh->prepare($query).
176 sub GetSerialStatusFromSerialId
{
177 my $dbh = C4
::Context
->dbh;
183 return $dbh->prepare($query);
186 =head2 GetSerialInformation
189 $data = GetSerialInformation($serialid);
190 returns a hash_ref containing :
191 items : items marcrecord (can be an array)
193 subscription table field
194 + information about subscription expiration
198 sub GetSerialInformation
{
200 my $dbh = C4
::Context
->dbh;
202 SELECT serial
.*, serial
.notes as sernotes
, serial
.status as serstatus
,subscription
.*,subscription
.subscriptionid as subsid
203 FROM serial LEFT JOIN subscription ON subscription
.subscriptionid
=serial
.subscriptionid
206 my $rq = $dbh->prepare($query);
207 $rq->execute($serialid);
208 my $data = $rq->fetchrow_hashref;
210 # create item information if we have serialsadditems for this subscription
211 if ( $data->{'serialsadditems'} ) {
212 my $queryitem = $dbh->prepare("SELECT itemnumber from serialitems where serialid=?");
213 $queryitem->execute($serialid);
214 my $itemnumbers = $queryitem->fetchall_arrayref( [0] );
216 if ( scalar(@
$itemnumbers) > 0 ) {
217 foreach my $itemnum (@
$itemnumbers) {
219 #It is ASSUMED that GetMarcItem ALWAYS WORK...
220 #Maybe GetMarcItem should return values on failure
221 $debug and warn "itemnumber :$itemnum->[0], bibnum :" . $data->{'biblionumber'};
222 my $itemprocessed = C4
::Items
::PrepareItemrecordDisplay
( $data->{'biblionumber'}, $itemnum->[0], $data );
223 $itemprocessed->{'itemnumber'} = $itemnum->[0];
224 $itemprocessed->{'itemid'} = $itemnum->[0];
225 $itemprocessed->{'serialid'} = $serialid;
226 $itemprocessed->{'biblionumber'} = $data->{'biblionumber'};
227 push @
{ $data->{'items'} }, $itemprocessed;
230 my $itemprocessed = C4
::Items
::PrepareItemrecordDisplay
( $data->{'biblionumber'}, '', $data );
231 $itemprocessed->{'itemid'} = "N$serialid";
232 $itemprocessed->{'serialid'} = $serialid;
233 $itemprocessed->{'biblionumber'} = $data->{'biblionumber'};
234 $itemprocessed->{'countitems'} = 0;
235 push @
{ $data->{'items'} }, $itemprocessed;
238 $data->{ "status" . $data->{'serstatus'} } = 1;
239 $data->{'subscriptionexpired'} = HasSubscriptionExpired
( $data->{'subscriptionid'} ) && $data->{'status'} == 1;
240 $data->{'abouttoexpire'} = abouttoexpire
( $data->{'subscriptionid'} );
241 $data->{cannotedit
} = not can_edit_subscription
( $data );
245 =head2 AddItem2Serial
247 $rows = AddItem2Serial($serialid,$itemnumber);
248 Adds an itemnumber to Serial record
249 returns the number of rows affected
254 my ( $serialid, $itemnumber ) = @_;
256 return unless ($serialid and $itemnumber);
258 my $dbh = C4
::Context
->dbh;
259 my $rq = $dbh->prepare("INSERT INTO `serialitems` SET serialid=? , itemnumber=?");
260 $rq->execute( $serialid, $itemnumber );
264 =head2 UpdateClaimdateIssues
266 UpdateClaimdateIssues($serialids,[$date]);
268 Update Claimdate for issues in @$serialids list with date $date
273 sub UpdateClaimdateIssues
{
274 my ( $serialids, $date ) = @_;
276 return unless ($serialids);
278 my $dbh = C4
::Context
->dbh;
279 $date = strftime
( "%Y-%m-%d", localtime ) unless ($date);
284 claims_count = claims_count + 1
285 WHERE serialid in (" . join( ",", map { '?' } @
$serialids ) . ")
287 my $rq = $dbh->prepare($query);
288 $rq->execute($date, CLAIMED
, @
$serialids);
292 =head2 GetSubscription
294 $subs = GetSubscription($subscriptionid)
295 this function returns the subscription which has $subscriptionid as id.
297 a hashref. This hash containts
298 subscription, subscriptionhistory, aqbooksellers.name, biblio.title
302 sub GetSubscription
{
303 my ($subscriptionid) = @_;
304 my $dbh = C4
::Context
->dbh;
306 SELECT subscription
.*,
307 subscriptionhistory
.*,
308 aqbooksellers
.name AS aqbooksellername
,
309 biblio
.title AS bibliotitle
,
310 subscription
.biblionumber as bibnum
312 LEFT JOIN subscriptionhistory ON subscription
.subscriptionid
=subscriptionhistory
.subscriptionid
313 LEFT JOIN aqbooksellers ON subscription
.aqbooksellerid
=aqbooksellers
.id
314 LEFT JOIN biblio ON biblio
.biblionumber
=subscription
.biblionumber
315 WHERE subscription
.subscriptionid
= ?
318 $debug and warn "query : $query\nsubsid :$subscriptionid";
319 my $sth = $dbh->prepare($query);
320 $sth->execute($subscriptionid);
321 my $subscription = $sth->fetchrow_hashref;
323 $subscription->{cannotedit
} = not can_edit_subscription
( $subscription );
325 # Add additional fields to the subscription into a new key "additional_fields"
326 my $additional_field_values = Koha
::AdditionalField
->fetch_all_values({
327 tablename
=> 'subscription',
328 record_id
=> $subscriptionid,
330 $subscription->{additional_fields
} = $additional_field_values->{$subscriptionid};
332 return $subscription;
335 =head2 GetFullSubscription
337 $array_ref = GetFullSubscription($subscriptionid)
338 this function reads the serial table.
342 sub GetFullSubscription
{
343 my ($subscriptionid) = @_;
345 return unless ($subscriptionid);
347 my $dbh = C4
::Context
->dbh;
349 SELECT serial
.serialid
,
352 serial
.publisheddate
,
353 serial
.publisheddatetext
,
355 serial
.notes as notes
,
356 year
(IF
(serial
.publisheddate
="00-00-0000",serial
.planneddate
,serial
.publisheddate
)) as year
,
357 aqbooksellers
.name as aqbooksellername
,
358 biblio
.title as bibliotitle
,
359 subscription
.branchcode AS branchcode
,
360 subscription
.subscriptionid AS subscriptionid
362 LEFT JOIN subscription ON
363 (serial
.subscriptionid
=subscription
.subscriptionid
)
364 LEFT JOIN aqbooksellers on subscription
.aqbooksellerid
=aqbooksellers
.id
365 LEFT JOIN biblio on biblio
.biblionumber
=subscription
.biblionumber
366 WHERE serial
.subscriptionid
= ?
368 IF
(serial
.publisheddate
="00-00-0000",serial
.planneddate
,serial
.publisheddate
) DESC
,
369 serial
.subscriptionid
371 $debug and warn "GetFullSubscription query: $query";
372 my $sth = $dbh->prepare($query);
373 $sth->execute($subscriptionid);
374 my $subscriptions = $sth->fetchall_arrayref( {} );
375 for my $subscription ( @
$subscriptions ) {
376 $subscription->{cannotedit
} = not can_edit_subscription
( $subscription );
378 return $subscriptions;
381 =head2 PrepareSerialsData
383 $array_ref = PrepareSerialsData($serialinfomation)
384 where serialinformation is a hashref array
388 sub PrepareSerialsData
{
391 return unless ($lines);
397 my $aqbooksellername;
401 my $previousnote = "";
403 foreach my $subs (@
{$lines}) {
404 for my $datefield ( qw(publisheddate planneddate) ) {
405 # handle 0000-00-00 dates
406 if (defined $subs->{$datefield} and $subs->{$datefield} =~ m/^00/) {
407 $subs->{$datefield} = undef;
410 $subs->{ "status" . $subs->{'status'} } = 1;
411 if ( grep { $_ == $subs->{status
} } ( EXPECTED
, LATE
, MISSING_STATUSES
, CLAIMED
) ) {
412 $subs->{"checked"} = 1;
415 if ( $subs->{'year'} && $subs->{'year'} ne "" ) {
416 $year = $subs->{'year'};
420 if ( $tmpresults{$year} ) {
421 push @
{ $tmpresults{$year}->{'serials'} }, $subs;
423 $tmpresults{$year} = {
425 'aqbooksellername' => $subs->{'aqbooksellername'},
426 'bibliotitle' => $subs->{'bibliotitle'},
427 'serials' => [$subs],
432 foreach my $key ( sort { $b cmp $a } keys %tmpresults ) {
433 push @res, $tmpresults{$key};
438 =head2 GetSubscriptionsFromBiblionumber
440 $array_ref = GetSubscriptionsFromBiblionumber($biblionumber)
441 this function get the subscription list. it reads the subscription table.
443 reference to an array of subscriptions which have the biblionumber given on input arg.
444 each element of this array is a hashref containing
445 startdate, histstartdate,opacnote,missinglist,recievedlist,periodicity,status & enddate
449 sub GetSubscriptionsFromBiblionumber
{
450 my ($biblionumber) = @_;
452 return unless ($biblionumber);
454 my $dbh = C4
::Context
->dbh;
456 SELECT subscription
.*,
458 subscriptionhistory
.*,
459 aqbooksellers
.name AS aqbooksellername
,
460 biblio
.title AS bibliotitle
462 LEFT JOIN subscriptionhistory ON subscription
.subscriptionid
=subscriptionhistory
.subscriptionid
463 LEFT JOIN aqbooksellers ON subscription
.aqbooksellerid
=aqbooksellers
.id
464 LEFT JOIN biblio ON biblio
.biblionumber
=subscription
.biblionumber
465 LEFT JOIN branches ON branches
.branchcode
=subscription
.branchcode
466 WHERE subscription
.biblionumber
= ?
468 my $sth = $dbh->prepare($query);
469 $sth->execute($biblionumber);
471 while ( my $subs = $sth->fetchrow_hashref ) {
472 $subs->{startdate
} = output_pref
( { dt
=> dt_from_string
( $subs->{startdate
} ), dateonly
=> 1 } );
473 $subs->{histstartdate
} = output_pref
( { dt
=> dt_from_string
( $subs->{histstartdate
} ), dateonly
=> 1 } );
474 $subs->{histenddate
} = output_pref
( { dt
=> dt_from_string
( $subs->{histenddate
} ), dateonly
=> 1 } );
475 $subs->{opacnote
} =~ s/\n/\<br\/\
>/g
;
476 $subs->{missinglist
} =~ s/\n/\<br\/\
>/g
;
477 $subs->{recievedlist
} =~ s/\n/\<br\/\
>/g
;
478 $subs->{ "periodicity" . $subs->{periodicity
} } = 1;
479 $subs->{ "numberpattern" . $subs->{numberpattern
} } = 1;
480 $subs->{ "status" . $subs->{'status'} } = 1;
482 if ( $subs->{enddate
} eq '0000-00-00' ) {
483 $subs->{enddate
} = '';
485 $subs->{enddate
} = output_pref
( { dt
=> dt_from_string
( $subs->{enddate
}), dateonly
=> 1 } );
487 $subs->{'abouttoexpire'} = abouttoexpire
( $subs->{'subscriptionid'} );
488 $subs->{'subscriptionexpired'} = HasSubscriptionExpired
( $subs->{'subscriptionid'} );
489 $subs->{cannotedit
} = not can_edit_subscription
( $subs );
495 =head2 GetFullSubscriptionsFromBiblionumber
497 $array_ref = GetFullSubscriptionsFromBiblionumber($biblionumber)
498 this function reads the serial table.
502 sub GetFullSubscriptionsFromBiblionumber
{
503 my ($biblionumber) = @_;
504 my $dbh = C4
::Context
->dbh;
506 SELECT serial
.serialid
,
509 serial
.publisheddate
,
510 serial
.publisheddatetext
,
512 serial
.notes as notes
,
513 year
(IF
(serial
.publisheddate
="00-00-0000",serial
.planneddate
,serial
.publisheddate
)) as year
,
514 biblio
.title as bibliotitle
,
515 subscription
.branchcode AS branchcode
,
516 subscription
.subscriptionid AS subscriptionid
518 LEFT JOIN subscription ON
519 (serial
.subscriptionid
=subscription
.subscriptionid
)
520 LEFT JOIN aqbooksellers on subscription
.aqbooksellerid
=aqbooksellers
.id
521 LEFT JOIN biblio on biblio
.biblionumber
=subscription
.biblionumber
522 WHERE subscription
.biblionumber
= ?
524 IF
(serial
.publisheddate
="00-00-0000",serial
.planneddate
,serial
.publisheddate
) DESC
,
525 serial
.subscriptionid
527 my $sth = $dbh->prepare($query);
528 $sth->execute($biblionumber);
529 my $subscriptions = $sth->fetchall_arrayref( {} );
530 for my $subscription ( @
$subscriptions ) {
531 $subscription->{cannotedit
} = not can_edit_subscription
( $subscription );
533 return $subscriptions;
536 =head2 SearchSubscriptions
538 @results = SearchSubscriptions($args);
540 This function returns a list of hashrefs, one for each subscription
541 that meets the conditions specified by the $args hashref.
543 The valid search fields are:
557 The expiration_date search field is special; it specifies the maximum
558 subscription expiration date.
562 sub SearchSubscriptions
{
565 my $additional_fields = $args->{additional_fields
} // [];
566 my $matching_record_ids_for_additional_fields = [];
567 if ( @
$additional_fields ) {
568 $matching_record_ids_for_additional_fields = Koha
::AdditionalField
->get_matching_record_ids({
569 fields
=> $additional_fields,
570 tablename
=> 'subscription',
573 return () unless @
$matching_record_ids_for_additional_fields;
578 subscription
.notes AS publicnotes
,
579 subscriptionhistory
.*,
581 biblio
.notes AS biblionotes
,
587 LEFT JOIN subscriptionhistory USING
(subscriptionid
)
588 LEFT JOIN biblio ON biblio
.biblionumber
= subscription
.biblionumber
589 LEFT JOIN biblioitems ON biblioitems
.biblionumber
= subscription
.biblionumber
590 LEFT JOIN aqbooksellers ON subscription
.aqbooksellerid
= aqbooksellers
.id
592 $query .= q
| WHERE
1|;
595 if( $args->{biblionumber
} ) {
596 push @where_strs, "biblio.biblionumber = ?";
597 push @where_args, $args->{biblionumber
};
600 if( $args->{title
} ){
601 my @words = split / /, $args->{title
};
603 foreach my $word (@words) {
604 push @strs, "biblio.title LIKE ?";
605 push @args, "%$word%";
608 push @where_strs, '(' . join (' AND ', @strs) . ')';
609 push @where_args, @args;
613 push @where_strs, "biblioitems.issn LIKE ?";
614 push @where_args, "%$args->{issn}%";
617 push @where_strs, "biblioitems.ean LIKE ?";
618 push @where_args, "%$args->{ean}%";
620 if ( $args->{callnumber
} ) {
621 push @where_strs, "subscription.callnumber LIKE ?";
622 push @where_args, "%$args->{callnumber}%";
624 if( $args->{publisher
} ){
625 push @where_strs, "biblioitems.publishercode LIKE ?";
626 push @where_args, "%$args->{publisher}%";
628 if( $args->{bookseller
} ){
629 push @where_strs, "aqbooksellers.name LIKE ?";
630 push @where_args, "%$args->{bookseller}%";
632 if( $args->{branch
} ){
633 push @where_strs, "subscription.branchcode = ?";
634 push @where_args, "$args->{branch}";
636 if ( $args->{location
} ) {
637 push @where_strs, "subscription.location = ?";
638 push @where_args, "$args->{location}";
640 if ( $args->{expiration_date
} ) {
641 push @where_strs, "subscription.enddate <= ?";
642 push @where_args, "$args->{expiration_date}";
644 if( defined $args->{closed
} ){
645 push @where_strs, "subscription.closed = ?";
646 push @where_args, "$args->{closed}";
650 $query .= ' AND ' . join(' AND ', @where_strs);
652 if ( @
$additional_fields ) {
653 $query .= ' AND subscriptionid IN ('
654 . join( ', ', @
$matching_record_ids_for_additional_fields )
658 $query .= " ORDER BY " . $args->{orderby
} if $args->{orderby
};
660 my $dbh = C4
::Context
->dbh;
661 my $sth = $dbh->prepare($query);
662 $sth->execute(@where_args);
663 my $results = $sth->fetchall_arrayref( {} );
665 for my $subscription ( @
$results ) {
666 $subscription->{cannotedit
} = not can_edit_subscription
( $subscription );
667 $subscription->{cannotdisplay
} = not can_show_subscription
( $subscription );
669 my $additional_field_values = Koha
::AdditionalField
->fetch_all_values({
670 record_id
=> $subscription->{subscriptionid
},
671 tablename
=> 'subscription'
673 $subscription->{additional_fields
} = $additional_field_values->{$subscription->{subscriptionid
}};
682 ($totalissues,@serials) = GetSerials($subscriptionid);
683 this function gets every serial not arrived for a given subscription
684 as well as the number of issues registered in the database (all types)
685 this number is used to see if a subscription can be deleted (=it must have only 1 issue)
687 FIXME: We should return \@serials.
692 my ( $subscriptionid, $count ) = @_;
694 return unless $subscriptionid;
696 my $dbh = C4
::Context
->dbh;
698 # status = 2 is "arrived"
700 $count = 5 unless ($count);
702 my $statuses = join( ',', ( ARRIVED
, MISSING_STATUSES
, NOT_ISSUED
) );
703 my $query = "SELECT serialid,serialseq, status, publisheddate,
704 publisheddatetext, planneddate,notes, routingnotes
706 WHERE subscriptionid = ? AND status NOT IN ( $statuses )
707 ORDER BY IF(publisheddate<>'0000-00-00',publisheddate,planneddate) DESC";
708 my $sth = $dbh->prepare($query);
709 $sth->execute($subscriptionid);
711 while ( my $line = $sth->fetchrow_hashref ) {
712 $line->{ "status" . $line->{status
} } = 1; # fills a "statusX" value, used for template status select list
713 for my $datefield ( qw( planneddate publisheddate) ) {
714 if ($line->{$datefield} && $line->{$datefield}!~m/^00/) {
715 $line->{$datefield} = output_pref
( { dt
=> dt_from_string
( $line->{$datefield} ), dateonly
=> 1 } );
717 $line->{$datefield} = q{};
720 push @serials, $line;
723 # OK, now add the last 5 issues arrives/missing
724 $query = "SELECT serialid,serialseq, status, planneddate, publisheddate,
725 publisheddatetext, notes, routingnotes
727 WHERE subscriptionid = ?
728 AND status IN ( $statuses )
729 ORDER BY IF(publisheddate<>'0000-00-00',publisheddate,planneddate) DESC
731 $sth = $dbh->prepare($query);
732 $sth->execute($subscriptionid);
733 while ( ( my $line = $sth->fetchrow_hashref ) && $counter < $count ) {
735 $line->{ "status" . $line->{status
} } = 1; # fills a "statusX" value, used for template status select list
736 for my $datefield ( qw( planneddate publisheddate) ) {
737 if ($line->{$datefield} && $line->{$datefield}!~m/^00/) {
738 $line->{$datefield} = output_pref
( { dt
=> dt_from_string
( $line->{$datefield} ), dateonly
=> 1 } );
740 $line->{$datefield} = q{};
744 push @serials, $line;
747 $query = "SELECT count(*) FROM serial WHERE subscriptionid=?";
748 $sth = $dbh->prepare($query);
749 $sth->execute($subscriptionid);
750 my ($totalissues) = $sth->fetchrow;
751 return ( $totalissues, @serials );
756 @serials = GetSerials2($subscriptionid,$statuses);
757 this function returns every serial waited for a given subscription
758 as well as the number of issues registered in the database (all types)
759 this number is used to see if a subscription can be deleted (=it must have only 1 issue)
761 $statuses is an arrayref of statuses and is mandatory.
766 my ( $subscription, $statuses ) = @_;
768 return unless ($subscription and @
$statuses);
770 my $statuses_string = join ',', @
$statuses;
772 my $dbh = C4
::Context
->dbh;
774 SELECT serialid
,serialseq
, status
, planneddate
, publisheddate
,
775 publisheddatetext
, notes
, routingnotes
777 WHERE subscriptionid
=$subscription AND status IN
($statuses_string)
778 ORDER BY publisheddate
,serialid DESC
780 $debug and warn "GetSerials2 query: $query";
781 my $sth = $dbh->prepare($query);
785 while ( my $line = $sth->fetchrow_hashref ) {
786 $line->{ "status" . $line->{status
} } = 1; # fills a "statusX" value, used for template status select list
787 # Format dates for display
788 for my $datefield ( qw( planneddate publisheddate ) ) {
789 if (!defined($line->{$datefield}) || $line->{$datefield} =~m/^00/) {
790 $line->{$datefield} = q{};
793 $line->{$datefield} = output_pref
( { dt
=> dt_from_string
( $line->{$datefield} ), dateonly
=> 1 } );
796 push @serials, $line;
801 =head2 GetLatestSerials
803 \@serials = GetLatestSerials($subscriptionid,$limit)
804 get the $limit's latest serials arrived or missing for a given subscription
806 a ref to an array which contains all of the latest serials stored into a hash.
810 sub GetLatestSerials
{
811 my ( $subscriptionid, $limit ) = @_;
813 return unless ($subscriptionid and $limit);
815 my $dbh = C4
::Context
->dbh;
817 my $statuses = join( ',', ( ARRIVED
, MISSING_STATUSES
) );
818 my $strsth = "SELECT serialid,serialseq, status, planneddate, publisheddate, notes
820 WHERE subscriptionid = ?
821 AND status IN ($statuses)
822 ORDER BY publisheddate DESC LIMIT 0,$limit
824 my $sth = $dbh->prepare($strsth);
825 $sth->execute($subscriptionid);
827 while ( my $line = $sth->fetchrow_hashref ) {
828 $line->{ "status" . $line->{status
} } = 1; # fills a "statusX" value, used for template status select list
829 $line->{planneddate
} = output_pref
( { dt
=> dt_from_string
( $line->{planneddate
} ), dateonly
=> 1 } );
830 $line->{publisheddate
} = output_pref
( { dt
=> dt_from_string
( $line->{publisheddate
} ), dateonly
=> 1 } );
831 push @serials, $line;
837 =head2 GetDistributedTo
839 $distributedto=GetDistributedTo($subscriptionid)
840 This function returns the field distributedto for the subscription matching subscriptionid
844 sub GetDistributedTo
{
845 my $dbh = C4
::Context
->dbh;
847 my ($subscriptionid) = @_;
849 return unless ($subscriptionid);
851 my $query = "SELECT distributedto FROM subscription WHERE subscriptionid=?";
852 my $sth = $dbh->prepare($query);
853 $sth->execute($subscriptionid);
854 return ($distributedto) = $sth->fetchrow;
860 $nextseq, $newlastvalue1, $newlastvalue2, $newlastvalue3,
861 $newinnerloop1, $newinnerloop2, $newinnerloop3
862 ) = GetNextSeq( $subscription, $pattern, $planneddate );
864 $subscription is a hashref containing all the attributes of the table
866 $pattern is a hashref containing all the attributes of the table
867 'subscription_numberpatterns'.
868 $planneddate is a date string in iso format.
869 This function get the next issue for the subscription given on input arg
874 my ($subscription, $pattern, $planneddate) = @_;
876 return unless ($subscription and $pattern);
878 my ( $newlastvalue1, $newlastvalue2, $newlastvalue3,
879 $newinnerloop1, $newinnerloop2, $newinnerloop3 );
882 if ($subscription->{'skip_serialseq'}) {
883 my @irreg = split /;/, $subscription->{'irregularity'};
885 my $irregularities = {};
886 $irregularities->{$_} = 1 foreach(@irreg);
887 my $issueno = GetFictiveIssueNumber
($subscription, $planneddate) + 1;
888 while($irregularities->{$issueno}) {
895 my $numberingmethod = $pattern->{numberingmethod
};
897 if ($numberingmethod) {
898 $calculated = $numberingmethod;
899 my $locale = $subscription->{locale
};
900 $newlastvalue1 = $subscription->{lastvalue1
} || 0;
901 $newlastvalue2 = $subscription->{lastvalue2
} || 0;
902 $newlastvalue3 = $subscription->{lastvalue3
} || 0;
903 $newinnerloop1 = $subscription->{innerloop1
} || 0;
904 $newinnerloop2 = $subscription->{innerloop2
} || 0;
905 $newinnerloop3 = $subscription->{innerloop3
} || 0;
908 $calc{$_} = 1 if ($numberingmethod =~ /\{$_\}/);
911 for(my $i = 0; $i < $count; $i++) {
913 # check if we have to increase the new value.
915 if ($newinnerloop1 >= $pattern->{every1
}) {
917 $newlastvalue1 += $pattern->{add1
};
919 # reset counter if needed.
920 $newlastvalue1 = $pattern->{setto1
} if ($newlastvalue1 > $pattern->{whenmorethan1
});
923 # check if we have to increase the new value.
925 if ($newinnerloop2 >= $pattern->{every2
}) {
927 $newlastvalue2 += $pattern->{add2
};
929 # reset counter if needed.
930 $newlastvalue2 = $pattern->{setto2
} if ($newlastvalue2 > $pattern->{whenmorethan2
});
933 # check if we have to increase the new value.
935 if ($newinnerloop3 >= $pattern->{every3
}) {
937 $newlastvalue3 += $pattern->{add3
};
939 # reset counter if needed.
940 $newlastvalue3 = $pattern->{setto3
} if ($newlastvalue3 > $pattern->{whenmorethan3
});
944 my $newlastvalue1string = _numeration
( $newlastvalue1, $pattern->{numbering1
}, $locale );
945 $calculated =~ s/\{X\}/$newlastvalue1string/g;
948 my $newlastvalue2string = _numeration
( $newlastvalue2, $pattern->{numbering2
}, $locale );
949 $calculated =~ s/\{Y\}/$newlastvalue2string/g;
952 my $newlastvalue3string = _numeration
( $newlastvalue3, $pattern->{numbering3
}, $locale );
953 $calculated =~ s/\{Z\}/$newlastvalue3string/g;
958 $newlastvalue1, $newlastvalue2, $newlastvalue3,
959 $newinnerloop1, $newinnerloop2, $newinnerloop3);
964 $calculated = GetSeq($subscription, $pattern)
965 $subscription is a hashref containing all the attributes of the table 'subscription'
966 $pattern is a hashref containing all the attributes of the table 'subscription_numberpatterns'
967 this function transforms {X},{Y},{Z} to 150,0,0 for example.
969 the sequence in string format
974 my ($subscription, $pattern) = @_;
976 return unless ($subscription and $pattern);
978 my $locale = $subscription->{locale
};
980 my $calculated = $pattern->{numberingmethod
};
982 my $newlastvalue1 = $subscription->{'lastvalue1'} || 0;
983 $newlastvalue1 = _numeration
($newlastvalue1, $pattern->{numbering1
}, $locale) if ($pattern->{numbering1
}); # reset counter if needed.
984 $calculated =~ s/\{X\}/$newlastvalue1/g;
986 my $newlastvalue2 = $subscription->{'lastvalue2'} || 0;
987 $newlastvalue2 = _numeration
($newlastvalue2, $pattern->{numbering2
}, $locale) if ($pattern->{numbering2
}); # reset counter if needed.
988 $calculated =~ s/\{Y\}/$newlastvalue2/g;
990 my $newlastvalue3 = $subscription->{'lastvalue3'} || 0;
991 $newlastvalue3 = _numeration
($newlastvalue3, $pattern->{numbering3
}, $locale) if ($pattern->{numbering3
}); # reset counter if needed.
992 $calculated =~ s/\{Z\}/$newlastvalue3/g;
996 =head2 GetExpirationDate
998 $enddate = GetExpirationDate($subscriptionid, [$startdate])
1000 this function return the next expiration date for a subscription given on input args.
1003 the enddate or undef
1007 sub GetExpirationDate
{
1008 my ( $subscriptionid, $startdate ) = @_;
1010 return unless ($subscriptionid);
1012 my $dbh = C4
::Context
->dbh;
1013 my $subscription = GetSubscription
($subscriptionid);
1016 # we don't do the same test if the subscription is based on X numbers or on X weeks/months
1017 $enddate = $startdate || $subscription->{startdate
};
1018 my @date = split( /-/, $enddate );
1020 return if ( scalar(@date) != 3 || not check_date
(@date) );
1022 my $frequency = C4
::Serials
::Frequency
::GetSubscriptionFrequency
($subscription->{periodicity
});
1023 if ( $frequency and $frequency->{unit
} ) {
1026 if ( my $length = $subscription->{numberlength
} ) {
1028 #calculate the date of the last issue.
1029 for ( my $i = 1 ; $i <= $length ; $i++ ) {
1030 $enddate = GetNextDate
( $subscription, $enddate );
1032 } elsif ( $subscription->{monthlength
} ) {
1033 if ( $$subscription{startdate
} ) {
1034 my @enddate = Add_Delta_YM
( $date[0], $date[1], $date[2], 0, $subscription->{monthlength
} );
1035 $enddate = sprintf( "%04d-%02d-%02d", $enddate[0], $enddate[1], $enddate[2] );
1037 } elsif ( $subscription->{weeklength
} ) {
1038 if ( $$subscription{startdate
} ) {
1039 my @date = split( /-/, $subscription->{startdate
} );
1040 my @enddate = Add_Delta_Days
( $date[0], $date[1], $date[2], $subscription->{weeklength
} * 7 );
1041 $enddate = sprintf( "%04d-%02d-%02d", $enddate[0], $enddate[1], $enddate[2] );
1044 $enddate = $subscription->{enddate
};
1048 return $subscription->{enddate
};
1052 =head2 CountSubscriptionFromBiblionumber
1054 $subscriptionsnumber = CountSubscriptionFromBiblionumber($biblionumber)
1055 this returns a count of the subscriptions for a given biblionumber
1057 the number of subscriptions
1061 sub CountSubscriptionFromBiblionumber
{
1062 my ($biblionumber) = @_;
1064 return unless ($biblionumber);
1066 my $dbh = C4
::Context
->dbh;
1067 my $query = "SELECT count(*) FROM subscription WHERE biblionumber=?";
1068 my $sth = $dbh->prepare($query);
1069 $sth->execute($biblionumber);
1070 my $subscriptionsnumber = $sth->fetchrow;
1071 return $subscriptionsnumber;
1074 =head2 ModSubscriptionHistory
1076 ModSubscriptionHistory($subscriptionid,$histstartdate,$enddate,$recievedlist,$missinglist,$opacnote,$librariannote);
1078 this function modifies the history of a subscription. Put your new values on input arg.
1079 returns the number of rows affected
1083 sub ModSubscriptionHistory
{
1084 my ( $subscriptionid, $histstartdate, $enddate, $receivedlist, $missinglist, $opacnote, $librariannote ) = @_;
1086 return unless ($subscriptionid);
1088 my $dbh = C4
::Context
->dbh;
1089 my $query = "UPDATE subscriptionhistory
1090 SET histstartdate=?,histenddate=?,recievedlist=?,missinglist=?,opacnote=?,librariannote=?
1091 WHERE subscriptionid=?
1093 my $sth = $dbh->prepare($query);
1094 $receivedlist =~ s/^; // if $receivedlist;
1095 $missinglist =~ s/^; // if $missinglist;
1096 $opacnote =~ s/^; // if $opacnote;
1097 $sth->execute( $histstartdate, $enddate, $receivedlist, $missinglist, $opacnote, $librariannote, $subscriptionid );
1101 =head2 ModSerialStatus
1103 ModSerialStatus($serialid, $serialseq, $planneddate, $publisheddate,
1104 $publisheddatetext, $status, $notes);
1106 This function modify the serial status. Serial status is a number.(eg 2 is "arrived")
1107 Note : if we change from "waited" to something else,then we will have to create a new "waited" entry
1111 sub ModSerialStatus
{
1112 my ($serialid, $serialseq, $planneddate, $publisheddate, $publisheddatetext,
1113 $status, $notes) = @_;
1115 return unless ($serialid);
1117 #It is a usual serial
1118 # 1st, get previous status :
1119 my $dbh = C4
::Context
->dbh;
1120 my $query = "SELECT serial.subscriptionid,serial.status,subscription.periodicity
1121 FROM serial, subscription
1122 WHERE serial.subscriptionid=subscription.subscriptionid
1124 my $sth = $dbh->prepare($query);
1125 $sth->execute($serialid);
1126 my ( $subscriptionid, $oldstatus, $periodicity ) = $sth->fetchrow;
1127 my $frequency = GetSubscriptionFrequency
($periodicity);
1129 # change status & update subscriptionhistory
1131 if ( $status == DELETED
) {
1132 DelIssue
( { 'serialid' => $serialid, 'subscriptionid' => $subscriptionid, 'serialseq' => $serialseq } );
1137 SET serialseq = ?, publisheddate = ?, publisheddatetext = ?,
1138 planneddate = ?, status = ?, notes = ?
1141 $sth = $dbh->prepare($query);
1142 $sth->execute( $serialseq, $publisheddate, $publisheddatetext,
1143 $planneddate, $status, $notes, $serialid );
1144 $query = "SELECT * FROM subscription WHERE subscriptionid = ?";
1145 $sth = $dbh->prepare($query);
1146 $sth->execute($subscriptionid);
1147 my $val = $sth->fetchrow_hashref;
1148 unless ( $val->{manualhistory
} ) {
1149 $query = "SELECT missinglist,recievedlist FROM subscriptionhistory WHERE subscriptionid=?";
1150 $sth = $dbh->prepare($query);
1151 $sth->execute($subscriptionid);
1152 my ( $missinglist, $recievedlist ) = $sth->fetchrow;
1154 if ( $status == ARRIVED
|| ($oldstatus == ARRIVED
&& $status != ARRIVED
) ) {
1155 $recievedlist .= "; $serialseq"
1156 if ($recievedlist !~ /(^|;)\s*$serialseq(?=;|$)/);
1159 # in case serial has been previously marked as missing
1160 if (grep /$status/, (EXPECTED
, ARRIVED
, LATE
, CLAIMED
)) {
1161 $missinglist=~ s/(^|;)\s*$serialseq(?=;|$)//g;
1164 $missinglist .= "; $serialseq"
1165 if ( ( grep { $_ == $status } ( MISSING_STATUSES
) ) && ( $missinglist !~/(^|;)\s*$serialseq(?=;|$)/ ) );
1166 $missinglist .= "; not issued $serialseq"
1167 if ( $status == NOT_ISSUED
&& $missinglist !~ /(^|;)\s*$serialseq(?=;|$)/ );
1169 $query = "UPDATE subscriptionhistory SET recievedlist=?, missinglist=? WHERE subscriptionid=?";
1170 $sth = $dbh->prepare($query);
1171 $recievedlist =~ s/^; //;
1172 $missinglist =~ s/^; //;
1173 $sth->execute( $recievedlist, $missinglist, $subscriptionid );
1177 # create new expected entry if needed (ie : was "expected" and has changed)
1178 # BUG 12748: Check if there are no other expected issues.
1179 my $otherIssueExpected = scalar findSerialByStatus
(EXPECTED
, $subscriptionid);
1180 if ( !$otherIssueExpected && $oldstatus == EXPECTED
&& $status != EXPECTED
) {
1181 my $subscription = GetSubscription
($subscriptionid);
1182 my $pattern = C4
::Serials
::Numberpattern
::GetSubscriptionNumberpattern
($subscription->{numberpattern
});
1186 $newserialseq, $newlastvalue1, $newlastvalue2, $newlastvalue3,
1187 $newinnerloop1, $newinnerloop2, $newinnerloop3
1189 = GetNextSeq
( $subscription, $pattern, $publisheddate );
1191 # next date (calculated from actual date & frequency parameters)
1192 my $nextpublisheddate = GetNextDate
($subscription, $publisheddate, 1);
1193 my $nextpubdate = $nextpublisheddate;
1194 $query = "UPDATE subscription SET lastvalue1=?, lastvalue2=?, lastvalue3=?, innerloop1=?, innerloop2=?, innerloop3=?
1195 WHERE subscriptionid = ?";
1196 $sth = $dbh->prepare($query);
1197 $sth->execute( $newlastvalue1, $newlastvalue2, $newlastvalue3, $newinnerloop1, $newinnerloop2, $newinnerloop3, $subscriptionid );
1199 NewIssue
( $newserialseq, $subscriptionid, $subscription->{'biblionumber'}, 1, $nextpubdate, $nextpubdate );
1201 # check if an alert must be sent... (= a letter is defined & status became "arrived"
1202 if ( $subscription->{letter
} && $status == ARRIVED
&& $oldstatus != ARRIVED
) {
1203 require C4
::Letters
;
1204 C4
::Letters
::SendAlerts
( 'issue', $serialid, $subscription->{letter
} );
1211 =head2 GetNextExpected
1213 $nextexpected = GetNextExpected($subscriptionid)
1215 Get the planneddate for the current expected issue of the subscription.
1221 planneddate => ISO date
1226 sub GetNextExpected
{
1227 my ($subscriptionid) = @_;
1229 my $dbh = C4
::Context
->dbh;
1233 WHERE subscriptionid
= ?
1237 my $sth = $dbh->prepare($query);
1239 # Each subscription has only one 'expected' issue.
1240 $sth->execute( $subscriptionid, EXPECTED
);
1241 my $nextissue = $sth->fetchrow_hashref;
1242 if ( !$nextissue ) {
1246 WHERE subscriptionid
= ?
1247 ORDER BY publisheddate DESC
1250 $sth = $dbh->prepare($query);
1251 $sth->execute($subscriptionid);
1252 $nextissue = $sth->fetchrow_hashref;
1254 foreach(qw
/planneddate publisheddate/) {
1255 if ( !defined $nextissue->{$_} ) {
1256 # or should this default to 1st Jan ???
1257 $nextissue->{$_} = strftime
( '%Y-%m-%d', localtime );
1259 $nextissue->{$_} = ($nextissue->{$_} ne '0000-00-00')
1267 =head2 ModNextExpected
1269 ModNextExpected($subscriptionid,$date)
1271 Update the planneddate for the current expected issue of the subscription.
1272 This will modify all future prediction results.
1274 C<$date> is an ISO date.
1280 sub ModNextExpected
{
1281 my ( $subscriptionid, $date ) = @_;
1282 my $dbh = C4
::Context
->dbh;
1284 #FIXME: Would expect to only set planneddate, but we set both on new issue creation, so updating it here
1285 my $sth = $dbh->prepare('UPDATE serial SET planneddate=?,publisheddate=? WHERE subscriptionid=? AND status=?');
1287 # Each subscription has only one 'expected' issue.
1288 $sth->execute( $date, $date, $subscriptionid, EXPECTED
);
1293 =head2 GetSubscriptionIrregularities
1297 =item @irreg = &GetSubscriptionIrregularities($subscriptionid);
1298 get the list of irregularities for a subscription
1304 sub GetSubscriptionIrregularities
{
1305 my $subscriptionid = shift;
1307 return unless $subscriptionid;
1309 my $dbh = C4
::Context
->dbh;
1313 WHERE subscriptionid
= ?
1315 my $sth = $dbh->prepare($query);
1316 $sth->execute($subscriptionid);
1318 my ($result) = $sth->fetchrow_array;
1319 my @irreg = split /;/, $result;
1324 =head2 ModSubscription
1326 this function modifies a subscription. Put all new values on input args.
1327 returns the number of rows affected
1331 sub ModSubscription
{
1333 $auser, $branchcode, $aqbooksellerid, $cost, $aqbudgetid, $startdate,
1334 $periodicity, $firstacquidate, $irregularity, $numberpattern, $locale,
1335 $numberlength, $weeklength, $monthlength, $lastvalue1, $innerloop1,
1336 $lastvalue2, $innerloop2, $lastvalue3, $innerloop3, $status,
1337 $biblionumber, $callnumber, $notes, $letter, $manualhistory,
1338 $internalnotes, $serialsadditems, $staffdisplaycount, $opacdisplaycount,
1339 $graceperiod, $location, $enddate, $subscriptionid, $skip_serialseq
1342 my $dbh = C4
::Context
->dbh;
1343 my $query = "UPDATE subscription
1344 SET librarian=?, branchcode=?, aqbooksellerid=?, cost=?, aqbudgetid=?,
1345 startdate=?, periodicity=?, firstacquidate=?, irregularity=?,
1346 numberpattern=?, locale=?, numberlength=?, weeklength=?, monthlength=?,
1347 lastvalue1=?, innerloop1=?, lastvalue2=?, innerloop2=?,
1348 lastvalue3=?, innerloop3=?, status=?, biblionumber=?,
1349 callnumber=?, notes=?, letter=?, manualhistory=?,
1350 internalnotes=?, serialsadditems=?, staffdisplaycount=?,
1351 opacdisplaycount=?, graceperiod=?, location = ?, enddate=?,
1353 WHERE subscriptionid = ?";
1355 my $sth = $dbh->prepare($query);
1357 $auser, $branchcode, $aqbooksellerid, $cost,
1358 $aqbudgetid, $startdate, $periodicity, $firstacquidate,
1359 $irregularity, $numberpattern, $locale, $numberlength,
1360 $weeklength, $monthlength, $lastvalue1, $innerloop1,
1361 $lastvalue2, $innerloop2, $lastvalue3, $innerloop3,
1362 $status, $biblionumber, $callnumber, $notes,
1363 $letter, ($manualhistory ?
$manualhistory : 0),
1364 $internalnotes, $serialsadditems, $staffdisplaycount, $opacdisplaycount,
1365 $graceperiod, $location, $enddate, $skip_serialseq,
1368 my $rows = $sth->rows;
1370 logaction
( "SERIAL", "MODIFY", $subscriptionid, "" ) if C4
::Context
->preference("SubscriptionLog");
1374 =head2 NewSubscription
1376 $subscriptionid = &NewSubscription($auser,branchcode,$aqbooksellerid,$cost,$aqbudgetid,$biblionumber,
1377 $startdate,$periodicity,$numberlength,$weeklength,$monthlength,
1378 $lastvalue1,$innerloop1,$lastvalue2,$innerloop2,$lastvalue3,$innerloop3,
1379 $status, $notes, $letter, $firstacquidate, $irregularity, $numberpattern,
1380 $locale, $callnumber, $manualhistory, $internalnotes, $serialsadditems,
1381 $staffdisplaycount, $opacdisplaycount, $graceperiod, $location, $enddate, $skip_serialseq);
1383 Create a new subscription with value given on input args.
1386 the id of this new subscription
1390 sub NewSubscription
{
1392 $auser, $branchcode, $aqbooksellerid, $cost, $aqbudgetid, $biblionumber,
1393 $startdate, $periodicity, $numberlength, $weeklength, $monthlength,
1394 $lastvalue1, $innerloop1, $lastvalue2, $innerloop2, $lastvalue3,
1395 $innerloop3, $status, $notes, $letter, $firstacquidate, $irregularity,
1396 $numberpattern, $locale, $callnumber, $manualhistory, $internalnotes,
1397 $serialsadditems, $staffdisplaycount, $opacdisplaycount, $graceperiod,
1398 $location, $enddate, $skip_serialseq
1400 my $dbh = C4
::Context
->dbh;
1402 #save subscription (insert into database)
1404 INSERT INTO subscription
1405 (librarian
, branchcode
, aqbooksellerid
, cost
, aqbudgetid
,
1406 biblionumber
, startdate
, periodicity
, numberlength
, weeklength
,
1407 monthlength
, lastvalue1
, innerloop1
, lastvalue2
, innerloop2
,
1408 lastvalue3
, innerloop3
, status
, notes
, letter
, firstacquidate
,
1409 irregularity
, numberpattern
, locale
, callnumber
,
1410 manualhistory
, internalnotes
, serialsadditems
, staffdisplaycount
,
1411 opacdisplaycount
, graceperiod
, location
, enddate
, skip_serialseq
)
1412 VALUES
(?
,?
,?
,?
,?
,?
,?
,?
,?
,?
,?
,?
,?
,?
,?
,?
,?
,?
,?
,?
,?
,?
,?
,?
,?
,?
,?
,?
,?
,?
,?
,?
,?
,?
)
1414 my $sth = $dbh->prepare($query);
1416 $auser, $branchcode, $aqbooksellerid, $cost, $aqbudgetid, $biblionumber,
1417 $startdate, $periodicity, $numberlength, $weeklength,
1418 $monthlength, $lastvalue1, $innerloop1, $lastvalue2, $innerloop2,
1419 $lastvalue3, $innerloop3, $status, $notes, $letter,
1420 $firstacquidate, $irregularity, $numberpattern, $locale, $callnumber,
1421 $manualhistory, $internalnotes, $serialsadditems, $staffdisplaycount,
1422 $opacdisplaycount, $graceperiod, $location, $enddate, $skip_serialseq
1425 my $subscriptionid = $dbh->{'mysql_insertid'};
1427 $enddate = GetExpirationDate
( $subscriptionid, $startdate );
1431 WHERE subscriptionid
=?
1433 $sth = $dbh->prepare($query);
1434 $sth->execute( $enddate, $subscriptionid );
1437 # then create the 1st expected number
1439 INSERT INTO subscriptionhistory
1440 (biblionumber
, subscriptionid
, histstartdate
)
1443 $sth = $dbh->prepare($query);
1444 $sth->execute( $biblionumber, $subscriptionid, $startdate);
1446 # reread subscription to get a hash (for calculation of the 1st issue number)
1447 my $subscription = GetSubscription
($subscriptionid);
1448 my $pattern = C4
::Serials
::Numberpattern
::GetSubscriptionNumberpattern
($subscription->{numberpattern
});
1450 # calculate issue number
1451 my $serialseq = GetSeq
($subscription, $pattern) || q{};
1455 serialseq
=> $serialseq,
1456 serialseq_x
=> $subscription->{'lastvalue1'},
1457 serialseq_y
=> $subscription->{'lastvalue2'},
1458 serialseq_z
=> $subscription->{'lastvalue3'},
1459 subscriptionid
=> $subscriptionid,
1460 biblionumber
=> $biblionumber,
1462 planneddate
=> $firstacquidate,
1463 publisheddate
=> $firstacquidate,
1467 logaction
( "SERIAL", "ADD", $subscriptionid, "" ) if C4
::Context
->preference("SubscriptionLog");
1469 #set serial flag on biblio if not already set.
1470 my $bib = GetBiblio
($biblionumber);
1471 if ( $bib and !$bib->{'serial'} ) {
1472 my $record = GetMarcBiblio
($biblionumber);
1473 my ( $tag, $subf ) = GetMarcFromKohaField
( 'biblio.serial', $bib->{'frameworkcode'} );
1475 eval { $record->field($tag)->update( $subf => 1 ); };
1477 ModBiblio
( $record, $biblionumber, $bib->{'frameworkcode'} );
1479 return $subscriptionid;
1482 =head2 ReNewSubscription
1484 ReNewSubscription($subscriptionid,$user,$startdate,$numberlength,$weeklength,$monthlength,$note)
1486 this function renew a subscription with values given on input args.
1490 sub ReNewSubscription
{
1491 my ( $subscriptionid, $user, $startdate, $numberlength, $weeklength, $monthlength, $note ) = @_;
1492 my $dbh = C4
::Context
->dbh;
1493 my $subscription = GetSubscription
($subscriptionid);
1497 LEFT JOIN biblioitems ON biblio
.biblionumber
=biblioitems
.biblionumber
1498 WHERE biblio
.biblionumber
=?
1500 my $sth = $dbh->prepare($query);
1501 $sth->execute( $subscription->{biblionumber
} );
1502 my $biblio = $sth->fetchrow_hashref;
1504 if ( C4
::Context
->preference("RenewSerialAddsSuggestion") ) {
1505 require C4
::Suggestions
;
1506 C4
::Suggestions
::NewSuggestion
(
1507 { 'suggestedby' => $user,
1508 'title' => $subscription->{bibliotitle
},
1509 'author' => $biblio->{author
},
1510 'publishercode' => $biblio->{publishercode
},
1511 'note' => $biblio->{note
},
1512 'biblionumber' => $subscription->{biblionumber
}
1517 # renew subscription
1520 SET startdate
=?
,numberlength
=?
,weeklength
=?
,monthlength
=?
,reneweddate
=NOW
()
1521 WHERE subscriptionid
=?
1523 $sth = $dbh->prepare($query);
1524 $sth->execute( $startdate, $numberlength, $weeklength, $monthlength, $subscriptionid );
1525 my $enddate = GetExpirationDate
($subscriptionid);
1526 $debug && warn "enddate :$enddate";
1530 WHERE subscriptionid
=?
1532 $sth = $dbh->prepare($query);
1533 $sth->execute( $enddate, $subscriptionid );
1535 UPDATE subscriptionhistory
1537 WHERE subscriptionid
=?
1539 $sth = $dbh->prepare($query);
1540 $sth->execute( $enddate, $subscriptionid );
1542 logaction
( "SERIAL", "RENEW", $subscriptionid, "" ) if C4
::Context
->preference("SubscriptionLog");
1548 NewIssue($serialseq,$subscriptionid,$biblionumber,$status, $planneddate, $publisheddate, $notes)
1550 Create a new issue stored on the database.
1551 Note : we have to update the recievedlist and missinglist on subscriptionhistory for this subscription.
1552 returns the serial id
1557 my ( $serialseq, $subscriptionid, $biblionumber, $status, $planneddate,
1558 $publisheddate, $publisheddatetext, $notes ) = @_;
1559 ### FIXME biblionumber CAN be provided by subscriptionid. So Do we STILL NEED IT ?
1561 return unless ($subscriptionid);
1563 my $schema = Koha
::Database
->new()->schema();
1565 my $subscription = Koha
::Subscriptions
->find( $subscriptionid );
1567 my $serial = Koha
::Serial
->new(
1569 serialseq
=> $serialseq,
1570 serialseq_x
=> $subscription->lastvalue1(),
1571 serialseq_y
=> $subscription->lastvalue2(),
1572 serialseq_z
=> $subscription->lastvalue3(),
1573 subscriptionid
=> $subscriptionid,
1574 biblionumber
=> $biblionumber,
1576 planneddate
=> $planneddate,
1577 publisheddate
=> $publisheddate,
1578 publisheddatetext
=> $publisheddatetext,
1583 my $serialid = $serial->id();
1585 my $subscription_history = Koha
::Subscription
::Histories
->find($subscriptionid);
1586 my $missinglist = $subscription_history->missinglist();
1587 my $recievedlist = $subscription_history->recievedlist();
1589 if ( $status == ARRIVED
) {
1590 ### TODO Add a feature that improves recognition and description.
1591 ### As such count (serialseq) i.e. : N18,2(N19),N20
1592 ### Would use substr and index But be careful to previous presence of ()
1593 $recievedlist .= "; $serialseq" unless ( index( $recievedlist, $serialseq ) > 0 );
1595 if ( grep { /^$status$/ } (MISSING_STATUSES
) ) {
1596 $missinglist .= "; $serialseq" unless ( index( $missinglist, $serialseq ) > 0 );
1599 $recievedlist =~ s/^; //;
1600 $missinglist =~ s/^; //;
1602 $subscription_history->recievedlist($recievedlist);
1603 $subscription_history->missinglist($missinglist);
1604 $subscription_history->update();
1609 =head2 HasSubscriptionStrictlyExpired
1611 1 or 0 = HasSubscriptionStrictlyExpired($subscriptionid)
1613 the subscription has stricly expired when today > the end subscription date
1616 1 if true, 0 if false, -1 if the expiration date is not set.
1620 sub HasSubscriptionStrictlyExpired
{
1622 # Getting end of subscription date
1623 my ($subscriptionid) = @_;
1625 return unless ($subscriptionid);
1627 my $dbh = C4
::Context
->dbh;
1628 my $subscription = GetSubscription
($subscriptionid);
1629 my $expirationdate = $subscription->{enddate
} || GetExpirationDate
($subscriptionid);
1631 # If the expiration date is set
1632 if ( $expirationdate != 0 ) {
1633 my ( $endyear, $endmonth, $endday ) = split( '-', $expirationdate );
1635 # Getting today's date
1636 my ( $nowyear, $nowmonth, $nowday ) = Today
();
1638 # if today's date > expiration date, then the subscription has stricly expired
1639 if ( Delta_Days
( $nowyear, $nowmonth, $nowday, $endyear, $endmonth, $endday ) < 0 ) {
1646 # There are some cases where the expiration date is not set
1647 # As we can't determine if the subscription has expired on a date-basis,
1653 =head2 HasSubscriptionExpired
1655 $has_expired = HasSubscriptionExpired($subscriptionid)
1657 the subscription has expired when the next issue to arrive is out of subscription limit.
1660 0 if the subscription has not expired
1661 1 if the subscription has expired
1662 2 if has subscription does not have a valid expiration date set
1666 sub HasSubscriptionExpired
{
1667 my ($subscriptionid) = @_;
1669 return unless ($subscriptionid);
1671 my $dbh = C4
::Context
->dbh;
1672 my $subscription = GetSubscription
($subscriptionid);
1673 my $frequency = C4
::Serials
::Frequency
::GetSubscriptionFrequency
($subscription->{periodicity
});
1674 if ( $frequency and $frequency->{unit
} ) {
1675 my $expirationdate = $subscription->{enddate
} || GetExpirationDate
($subscriptionid);
1676 if (!defined $expirationdate) {
1677 $expirationdate = q{};
1680 SELECT max
(planneddate
)
1682 WHERE subscriptionid
=?
1684 my $sth = $dbh->prepare($query);
1685 $sth->execute($subscriptionid);
1686 my ($res) = $sth->fetchrow;
1687 if (!$res || $res=~m/^0000/) {
1690 my @res = split( /-/, $res );
1691 my @endofsubscriptiondate = split( /-/, $expirationdate );
1692 return 2 if ( scalar(@res) != 3 || scalar(@endofsubscriptiondate) != 3 || not check_date
(@res) || not check_date
(@endofsubscriptiondate) );
1694 if ( ( @endofsubscriptiondate && Delta_Days
( $res[0], $res[1], $res[2], $endofsubscriptiondate[0], $endofsubscriptiondate[1], $endofsubscriptiondate[2] ) <= 0 )
1699 if ( $subscription->{'numberlength'} ) {
1700 my $countreceived = countissuesfrom
( $subscriptionid, $subscription->{'startdate'} );
1701 return 1 if ( $countreceived > $subscription->{'numberlength'} );
1707 return 0; # Notice that you'll never get here.
1710 =head2 SetDistributedto
1712 SetDistributedto($distributedto,$subscriptionid);
1713 This function update the value of distributedto for a subscription given on input arg.
1717 sub SetDistributedto
{
1718 my ( $distributedto, $subscriptionid ) = @_;
1719 my $dbh = C4
::Context
->dbh;
1723 WHERE subscriptionid
=?
1725 my $sth = $dbh->prepare($query);
1726 $sth->execute( $distributedto, $subscriptionid );
1730 =head2 DelSubscription
1732 DelSubscription($subscriptionid)
1733 this function deletes subscription which has $subscriptionid as id.
1737 sub DelSubscription
{
1738 my ($subscriptionid) = @_;
1739 my $dbh = C4
::Context
->dbh;
1740 $dbh->do("DELETE FROM subscription WHERE subscriptionid=?", undef, $subscriptionid);
1741 $dbh->do("DELETE FROM subscriptionhistory WHERE subscriptionid=?", undef, $subscriptionid);
1742 $dbh->do("DELETE FROM serial WHERE subscriptionid=?", undef, $subscriptionid);
1744 my $afs = Koha
::AdditionalField
->all({tablename
=> 'subscription'});
1745 foreach my $af (@
$afs) {
1746 $af->delete_values({record_id
=> $subscriptionid});
1749 logaction
( "SERIAL", "DELETE", $subscriptionid, "" ) if C4
::Context
->preference("SubscriptionLog");
1754 DelIssue($serialseq,$subscriptionid)
1755 this function deletes an issue which has $serialseq and $subscriptionid given on input arg.
1757 returns the number of rows affected
1762 my ($dataissue) = @_;
1763 my $dbh = C4
::Context
->dbh;
1764 ### TODO Add itemdeletion. Would need to get itemnumbers. Should be in a pref ?
1769 AND subscriptionid
= ?
1771 my $mainsth = $dbh->prepare($query);
1772 $mainsth->execute( $dataissue->{'serialid'}, $dataissue->{'subscriptionid'} );
1774 #Delete element from subscription history
1775 $query = "SELECT * FROM subscription WHERE subscriptionid = ?";
1776 my $sth = $dbh->prepare($query);
1777 $sth->execute( $dataissue->{'subscriptionid'} );
1778 my $val = $sth->fetchrow_hashref;
1779 unless ( $val->{manualhistory
} ) {
1781 SELECT
* FROM subscriptionhistory
1782 WHERE subscriptionid
= ?
1784 my $sth = $dbh->prepare($query);
1785 $sth->execute( $dataissue->{'subscriptionid'} );
1786 my $data = $sth->fetchrow_hashref;
1787 my $serialseq = $dataissue->{'serialseq'};
1788 $data->{'missinglist'} =~ s/\b$serialseq\b//;
1789 $data->{'recievedlist'} =~ s/\b$serialseq\b//;
1790 my $strsth = "UPDATE subscriptionhistory SET " . join( ",", map { join( "=", $_, $dbh->quote( $data->{$_} ) ) } keys %$data ) . " WHERE subscriptionid=?";
1791 $sth = $dbh->prepare($strsth);
1792 $sth->execute( $dataissue->{'subscriptionid'} );
1795 return $mainsth->rows;
1798 =head2 GetLateOrMissingIssues
1800 @issuelist = GetLateMissingIssues($supplierid,$serialid)
1802 this function selects missing issues on database - where serial.status = MISSING* or serial.status = LATE or planneddate<now
1805 the issuelist as an array of hash refs. Each element of this array contains
1806 name,title,planneddate,serialseq,serial.subscriptionid from tables : subscription, serial & biblio
1810 sub GetLateOrMissingIssues
{
1811 my ( $supplierid, $serialid, $order ) = @_;
1813 return unless ( $supplierid or $serialid );
1815 my $dbh = C4
::Context
->dbh;
1820 $byserial = "and serialid = " . $serialid;
1823 $order .= ", title";
1827 my $missing_statuses_string = join ',', (MISSING_STATUSES
);
1829 $sth = $dbh->prepare(
1831 serialid, aqbooksellerid, name,
1832 biblio.title, biblioitems.issn, planneddate, serialseq,
1833 serial.status, serial.subscriptionid, claimdate, claims_count,
1834 subscription.branchcode
1836 LEFT JOIN subscription ON serial.subscriptionid=subscription.subscriptionid
1837 LEFT JOIN biblio ON subscription.biblionumber=biblio.biblionumber
1838 LEFT JOIN biblioitems ON subscription.biblionumber=biblioitems.biblionumber
1839 LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id
1840 WHERE subscription.subscriptionid = serial.subscriptionid
1841 AND (serial.STATUS IN ($missing_statuses_string) OR ((planneddate < now() AND serial.STATUS = ?) OR serial.STATUS = ? OR serial.STATUS = ?))
1842 AND subscription.aqbooksellerid=$supplierid
1847 $sth = $dbh->prepare(
1849 serialid, aqbooksellerid, name,
1850 biblio.title, planneddate, serialseq,
1851 serial.status, serial.subscriptionid, claimdate, claims_count,
1852 subscription.branchcode
1854 LEFT JOIN subscription ON serial.subscriptionid=subscription.subscriptionid
1855 LEFT JOIN biblio ON subscription.biblionumber=biblio.biblionumber
1856 LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id
1857 WHERE subscription.subscriptionid = serial.subscriptionid
1858 AND (serial.STATUS IN ($missing_statuses_string) OR ((planneddate < now() AND serial.STATUS = ?) OR serial.STATUS = ? OR serial.STATUS = ?))
1863 $sth->execute( EXPECTED
, LATE
, CLAIMED
);
1865 while ( my $line = $sth->fetchrow_hashref ) {
1867 if ($line->{planneddate
} && $line->{planneddate
} !~/^0+\-/) {
1868 $line->{planneddateISO
} = $line->{planneddate
};
1869 $line->{planneddate
} = output_pref
( { dt
=> dt_from_string
( $line->{"planneddate"} ), dateonly
=> 1 } );
1871 if ($line->{claimdate
} && $line->{claimdate
} !~/^0+\-/) {
1872 $line->{claimdateISO
} = $line->{claimdate
};
1873 $line->{claimdate
} = output_pref
( { dt
=> dt_from_string
( $line->{"claimdate"} ), dateonly
=> 1 } );
1875 $line->{"status".$line->{status
}} = 1;
1877 my $additional_field_values = Koha
::AdditionalField
->fetch_all_values({
1878 record_id
=> $line->{subscriptionid
},
1879 tablename
=> 'subscription'
1881 %$line = ( %$line, additional_fields
=> $additional_field_values->{$line->{subscriptionid
}} );
1883 push @issuelist, $line;
1890 &updateClaim($serialid)
1892 this function updates the time when a claim is issued for late/missing items
1894 called from claims.pl file
1899 my ($serialid) = @_;
1900 my $dbh = C4
::Context
->dbh;
1903 SET claimdate
= NOW
(),
1904 claims_count
= claims_count
+ 1
1910 =head2 getsupplierbyserialid
1912 $result = getsupplierbyserialid($serialid)
1914 this function is used to find the supplier id given a serial id
1917 hashref containing serialid, subscriptionid, and aqbooksellerid
1921 sub getsupplierbyserialid
{
1922 my ($serialid) = @_;
1923 my $dbh = C4
::Context
->dbh;
1924 my $sth = $dbh->prepare(
1925 "SELECT serialid, serial.subscriptionid, aqbooksellerid
1927 LEFT JOIN subscription ON serial.subscriptionid = subscription.subscriptionid
1931 $sth->execute($serialid);
1932 my $line = $sth->fetchrow_hashref;
1933 my $result = $line->{'aqbooksellerid'};
1937 =head2 check_routing
1939 $result = &check_routing($subscriptionid)
1941 this function checks to see if a serial has a routing list and returns the count of routingid
1942 used to show either an 'add' or 'edit' link
1947 my ($subscriptionid) = @_;
1949 return unless ($subscriptionid);
1951 my $dbh = C4
::Context
->dbh;
1952 my $sth = $dbh->prepare(
1953 "SELECT count(routingid) routingids FROM subscription LEFT JOIN subscriptionroutinglist
1954 ON subscription.subscriptionid = subscriptionroutinglist.subscriptionid
1955 WHERE subscription.subscriptionid = ? ORDER BY ranking ASC
1958 $sth->execute($subscriptionid);
1959 my $line = $sth->fetchrow_hashref;
1960 my $result = $line->{'routingids'};
1964 =head2 addroutingmember
1966 addroutingmember($borrowernumber,$subscriptionid)
1968 this function takes a borrowernumber and subscriptionid and adds the member to the
1969 routing list for that serial subscription and gives them a rank on the list
1970 of either 1 or highest current rank + 1
1974 sub addroutingmember
{
1975 my ( $borrowernumber, $subscriptionid ) = @_;
1977 return unless ($borrowernumber and $subscriptionid);
1980 my $dbh = C4
::Context
->dbh;
1981 my $sth = $dbh->prepare( "SELECT max(ranking) rank FROM subscriptionroutinglist WHERE subscriptionid = ?" );
1982 $sth->execute($subscriptionid);
1983 while ( my $line = $sth->fetchrow_hashref ) {
1984 if ( $line->{'rank'} > 0 ) {
1985 $rank = $line->{'rank'} + 1;
1990 $sth = $dbh->prepare( "INSERT INTO subscriptionroutinglist (subscriptionid,borrowernumber,ranking) VALUES (?,?,?)" );
1991 $sth->execute( $subscriptionid, $borrowernumber, $rank );
1994 =head2 reorder_members
1996 reorder_members($subscriptionid,$routingid,$rank)
1998 this function is used to reorder the routing list
2000 it takes the routingid of the member one wants to re-rank and the rank it is to move to
2001 - it gets all members on list puts their routingid's into an array
2002 - removes the one in the array that is $routingid
2003 - then reinjects $routingid at point indicated by $rank
2004 - then update the database with the routingids in the new order
2008 sub reorder_members
{
2009 my ( $subscriptionid, $routingid, $rank ) = @_;
2010 my $dbh = C4
::Context
->dbh;
2011 my $sth = $dbh->prepare( "SELECT * FROM subscriptionroutinglist WHERE subscriptionid = ? ORDER BY ranking ASC" );
2012 $sth->execute($subscriptionid);
2014 while ( my $line = $sth->fetchrow_hashref ) {
2015 push( @result, $line->{'routingid'} );
2018 # To find the matching index
2020 my $key = -1; # to allow for 0 being a valid response
2021 for ( $i = 0 ; $i < @result ; $i++ ) {
2022 if ( $routingid == $result[$i] ) {
2023 $key = $i; # save the index
2028 # if index exists in array then move it to new position
2029 if ( $key > -1 && $rank > 0 ) {
2030 my $new_rank = $rank - 1; # $new_rank is what you want the new index to be in the array
2031 my $moving_item = splice( @result, $key, 1 );
2032 splice( @result, $new_rank, 0, $moving_item );
2034 for ( my $j = 0 ; $j < @result ; $j++ ) {
2035 my $sth = $dbh->prepare( "UPDATE subscriptionroutinglist SET ranking = '" . ( $j + 1 ) . "' WHERE routingid = '" . $result[$j] . "'" );
2041 =head2 delroutingmember
2043 delroutingmember($routingid,$subscriptionid)
2045 this function either deletes one member from routing list if $routingid exists otherwise
2046 deletes all members from the routing list
2050 sub delroutingmember
{
2052 # if $routingid exists then deletes that row otherwise deletes all with $subscriptionid
2053 my ( $routingid, $subscriptionid ) = @_;
2054 my $dbh = C4
::Context
->dbh;
2056 my $sth = $dbh->prepare("DELETE FROM subscriptionroutinglist WHERE routingid = ?");
2057 $sth->execute($routingid);
2058 reorder_members
( $subscriptionid, $routingid );
2060 my $sth = $dbh->prepare("DELETE FROM subscriptionroutinglist WHERE subscriptionid = ?");
2061 $sth->execute($subscriptionid);
2066 =head2 getroutinglist
2068 @routinglist = getroutinglist($subscriptionid)
2070 this gets the info from the subscriptionroutinglist for $subscriptionid
2073 the routinglist as an array. Each element of the array contains a hash_ref containing
2074 routingid - a unique id, borrowernumber, ranking, and biblionumber of subscription
2078 sub getroutinglist
{
2079 my ($subscriptionid) = @_;
2080 my $dbh = C4
::Context
->dbh;
2081 my $sth = $dbh->prepare(
2082 'SELECT routingid, borrowernumber, ranking, biblionumber
2084 JOIN subscriptionroutinglist ON subscription.subscriptionid = subscriptionroutinglist.subscriptionid
2085 WHERE subscription.subscriptionid = ? ORDER BY ranking ASC'
2087 $sth->execute($subscriptionid);
2088 my $routinglist = $sth->fetchall_arrayref({});
2089 return @
{$routinglist};
2092 =head2 countissuesfrom
2094 $result = countissuesfrom($subscriptionid,$startdate)
2096 Returns a count of serial rows matching the given subsctiptionid
2097 with published date greater than startdate
2101 sub countissuesfrom
{
2102 my ( $subscriptionid, $startdate ) = @_;
2103 my $dbh = C4
::Context
->dbh;
2107 WHERE subscriptionid
=?
2108 AND serial
.publisheddate
>?
2110 my $sth = $dbh->prepare($query);
2111 $sth->execute( $subscriptionid, $startdate );
2112 my ($countreceived) = $sth->fetchrow;
2113 return $countreceived;
2118 $result = CountIssues($subscriptionid)
2120 Returns a count of serial rows matching the given subsctiptionid
2125 my ($subscriptionid) = @_;
2126 my $dbh = C4
::Context
->dbh;
2130 WHERE subscriptionid
=?
2132 my $sth = $dbh->prepare($query);
2133 $sth->execute($subscriptionid);
2134 my ($countreceived) = $sth->fetchrow;
2135 return $countreceived;
2140 $result = HasItems($subscriptionid)
2142 returns a count of items from serial matching the subscriptionid
2147 my ($subscriptionid) = @_;
2148 my $dbh = C4
::Context
->dbh;
2150 SELECT COUNT
(serialitems
.itemnumber
)
2152 LEFT JOIN serialitems USING
(serialid
)
2153 WHERE subscriptionid
=? AND serialitems
.serialid IS NOT NULL
2155 my $sth=$dbh->prepare($query);
2156 $sth->execute($subscriptionid);
2157 my ($countitems)=$sth->fetchrow_array();
2161 =head2 abouttoexpire
2163 $result = abouttoexpire($subscriptionid)
2165 this function alerts you to the penultimate issue for a serial subscription
2167 returns 1 - if this is the penultimate issue
2173 my ($subscriptionid) = @_;
2174 my $dbh = C4
::Context
->dbh;
2175 my $subscription = GetSubscription
($subscriptionid);
2176 my $per = $subscription->{'periodicity'};
2177 my $frequency = C4
::Serials
::Frequency
::GetSubscriptionFrequency
($per);
2178 if ($frequency and $frequency->{unit
}){
2180 my $expirationdate = GetExpirationDate
($subscriptionid);
2182 my ($res) = $dbh->selectrow_array('select max(planneddate) from serial where subscriptionid = ?', undef, $subscriptionid);
2183 my $nextdate = GetNextDate
($subscription, $res);
2185 # only compare dates if both dates exist.
2186 if ($nextdate and $expirationdate) {
2187 if(Date
::Calc
::Delta_Days
(
2188 split( /-/, $nextdate ),
2189 split( /-/, $expirationdate )
2195 } elsif ($subscription->{numberlength
}>0) {
2196 return (countissuesfrom
($subscriptionid,$subscription->{'startdate'}) >=$subscription->{numberlength
}-1);
2202 sub in_array
{ # used in next sub down
2203 my ( $val, @elements ) = @_;
2204 foreach my $elem (@elements) {
2205 if ( $val == $elem ) {
2212 =head2 GetSubscriptionsFromBorrower
2214 ($count,@routinglist) = GetSubscriptionsFromBorrower($borrowernumber)
2216 this gets the info from subscriptionroutinglist for each $subscriptionid
2219 a count of the serial subscription routing lists to which a patron belongs,
2220 with the titles of those serial subscriptions as an array. Each element of the array
2221 contains a hash_ref with subscriptionID and title of subscription.
2225 sub GetSubscriptionsFromBorrower
{
2226 my ($borrowernumber) = @_;
2227 my $dbh = C4
::Context
->dbh;
2228 my $sth = $dbh->prepare(
2229 "SELECT subscription.subscriptionid, biblio.title
2231 JOIN biblio ON biblio.biblionumber = subscription.biblionumber
2232 JOIN subscriptionroutinglist USING (subscriptionid)
2233 WHERE subscriptionroutinglist.borrowernumber = ? ORDER BY title ASC
2236 $sth->execute($borrowernumber);
2239 while ( my $line = $sth->fetchrow_hashref ) {
2241 push( @routinglist, $line );
2243 return ( $count, @routinglist );
2247 =head2 GetFictiveIssueNumber
2249 $issueno = GetFictiveIssueNumber($subscription, $publishedate);
2251 Get the position of the issue published at $publisheddate, considering the
2252 first issue (at firstacquidate) is at position 1, the next is at position 2, etc...
2253 This issuenumber doesn't take into account irregularities, so, for instance, if the 3rd
2254 issue is declared as 'irregular' (will be skipped at receipt), the next issue number
2255 will be 4, not 3. It's why it is called 'fictive'. It is NOT a serial seq, and is not
2256 depending on how many rows are in serial table.
2257 The issue number calculation is based on subscription frequency, first acquisition
2258 date, and $publisheddate.
2262 sub GetFictiveIssueNumber
{
2263 my ($subscription, $publisheddate) = @_;
2265 my $frequency = GetSubscriptionFrequency
($subscription->{'periodicity'});
2266 my $unit = $frequency->{unit
} ?
lc $frequency->{'unit'} : undef;
2270 my ($year, $month, $day) = split /-/, $publisheddate;
2271 my ($fa_year, $fa_month, $fa_day) = split /-/, $subscription->{'firstacquidate'};
2275 if($unit eq 'day') {
2276 $delta = Delta_Days
($fa_year, $fa_month, $fa_day, $year, $month, $day);
2277 } elsif($unit eq 'week') {
2278 ($wkno, $year) = Week_of_Year
($year, $month, $day);
2279 my ($fa_wkno, $fa_yr) = Week_of_Year
($fa_year, $fa_month, $fa_day);
2280 $delta = ($fa_yr == $year) ?
($wkno - $fa_wkno) : ( ($year-$fa_yr-1)*52 + (52-$fa_wkno+$wkno) );
2281 } elsif($unit eq 'month') {
2282 $delta = ($fa_year == $year)
2283 ?
($month - $fa_month)
2284 : ( ($year-$fa_year-1)*12 + (12-$fa_month+$month) );
2285 } elsif($unit eq 'year') {
2286 $delta = $year - $fa_year;
2288 if($frequency->{'unitsperissue'} == 1) {
2289 $issueno = $delta * $frequency->{'issuesperunit'} + $subscription->{'countissuesperunit'};
2291 # Assuming issuesperunit == 1
2292 $issueno = int( ($delta + $frequency->{'unitsperissue'}) / $frequency->{'unitsperissue'} );
2298 sub _get_next_date_day
{
2299 my ($subscription, $freqdata, $year, $month, $day) = @_;
2301 if ($subscription->{countissuesperunit
} + 1 > $freqdata->{issuesperunit
}){
2302 ($year,$month,$day) = Add_Delta_Days
($year,$month, $day , $freqdata->{unitsperissue
} );
2303 $subscription->{countissuesperunit
} = 1;
2305 $subscription->{countissuesperunit
}++;
2308 return ($year, $month, $day);
2311 sub _get_next_date_week
{
2312 my ($subscription, $freqdata, $year, $month, $day) = @_;
2314 my ($wkno, $yr) = Week_of_Year
($year, $month, $day);
2315 my $fa_dow = Day_of_Week
(split /-/, $subscription->{firstacquidate
});
2317 if ($subscription->{countissuesperunit
} + 1 > $freqdata->{issuesperunit
}){
2318 $subscription->{countissuesperunit
} = 1;
2319 $wkno += $freqdata->{unitsperissue
};
2324 ($year,$month,$day) = Monday_of_Week
($wkno, $yr);
2325 ($year,$month,$day) = Add_Delta_Days
($year, $month, $day, $fa_dow - 1);
2327 # Try to guess the next day of week
2328 my $delta_days = int((7 - ($fa_dow - 1)) / $freqdata->{issuesperunit
});
2329 ($year,$month,$day) = Add_Delta_Days
($year, $month, $day, $delta_days);
2330 $subscription->{countissuesperunit
}++;
2333 return ($year, $month, $day);
2336 sub _get_next_date_month
{
2337 my ($subscription, $freqdata, $year, $month, $day) = @_;
2340 (undef, undef, $fa_day) = split /-/, $subscription->{firstacquidate
};
2342 if ($subscription->{countissuesperunit
} + 1 > $freqdata->{issuesperunit
}){
2343 $subscription->{countissuesperunit
} = 1;
2344 ($year,$month,$day) = Add_Delta_YM
($year,$month,$day, 0,
2345 $freqdata->{unitsperissue
});
2346 my $days_in_month = Days_in_Month
($year, $month);
2347 $day = $fa_day <= $days_in_month ?
$fa_day : $days_in_month;
2349 # Try to guess the next day in month
2350 my $days_in_month = Days_in_Month
($year, $month);
2351 my $delta_days = int(($days_in_month - ($fa_day - 1)) / $freqdata->{issuesperunit
});
2352 ($year,$month,$day) = Add_Delta_Days
($year, $month, $day, $delta_days);
2353 $subscription->{countissuesperunit
}++;
2356 return ($year, $month, $day);
2359 sub _get_next_date_year
{
2360 my ($subscription, $freqdata, $year, $month, $day) = @_;
2362 my ($fa_year, $fa_month, $fa_day) = split /-/, $subscription->{firstacquidate
};
2364 if ($subscription->{countissuesperunit
} + 1 > $freqdata->{issuesperunit
}){
2365 $subscription->{countissuesperunit
} = 1;
2366 ($year) = Add_Delta_YM
($year,$month,$day, $freqdata->{"unitsperissue"},0);
2368 my $days_in_month = Days_in_Month
($year, $month);
2369 $day = $fa_day <= $days_in_month ?
$fa_day : $days_in_month;
2371 # Try to guess the next day in year
2372 my $days_in_year = Days_in_Year
($year,12); #Sum the days of all the months of this year
2373 my $delta_days = int(($days_in_year - ($fa_day - 1)) / $freqdata->{issuesperunit
});
2374 ($year,$month,$day) = Add_Delta_Days
($year, $month, $day, $delta_days);
2375 $subscription->{countissuesperunit
}++;
2378 return ($year, $month, $day);
2383 $resultdate = GetNextDate($publisheddate,$subscription)
2385 this function it takes the publisheddate and will return the next issue's date
2386 and will skip dates if there exists an irregularity.
2387 $publisheddate has to be an ISO date
2388 $subscription is a hashref containing at least 'periodicity', 'firstacquidate', 'irregularity', and 'countissuesperunit'
2389 $updatecount is a boolean value which, when set to true, update the 'countissuesperunit' in database
2390 - eg if periodicity is monthly and $publisheddate is 2007-02-10 but if March and April is to be
2391 skipped then the returned date will be 2007-05-10
2394 $resultdate - then next date in the sequence (ISO date)
2396 Return undef if subscription is irregular
2401 my ( $subscription, $publisheddate, $updatecount ) = @_;
2403 return unless $subscription and $publisheddate;
2405 my $freqdata = GetSubscriptionFrequency
($subscription->{'periodicity'});
2407 if ($freqdata->{'unit'}) {
2408 my ( $year, $month, $day ) = split /-/, $publisheddate;
2410 # Process an irregularity Hash
2411 # Suppose that irregularities are stored in a string with this structure
2412 # irreg1;irreg2;irreg3
2413 # where irregX is the number of issue which will not be received
2414 # (the first issue takes the number 1, the 2nd the number 2 and so on)
2416 if ( $subscription->{irregularity
} ) {
2417 my @irreg = split /;/, $subscription->{'irregularity'} ;
2418 foreach my $irregularity (@irreg) {
2419 $irregularities{$irregularity} = 1;
2423 # Get the 'fictive' next issue number
2424 # It is used to check if next issue is an irregular issue.
2425 my $issueno = GetFictiveIssueNumber
($subscription, $publisheddate) + 1;
2427 # Then get the next date
2428 my $unit = lc $freqdata->{'unit'};
2429 if ($unit eq 'day') {
2430 while ($irregularities{$issueno}) {
2431 ($year, $month, $day) = _get_next_date_day
($subscription,
2432 $freqdata, $year, $month, $day);
2435 ($year, $month, $day) = _get_next_date_day
($subscription, $freqdata,
2436 $year, $month, $day);
2438 elsif ($unit eq 'week') {
2439 while ($irregularities{$issueno}) {
2440 ($year, $month, $day) = _get_next_date_week
($subscription,
2441 $freqdata, $year, $month, $day);
2444 ($year, $month, $day) = _get_next_date_week
($subscription,
2445 $freqdata, $year, $month, $day);
2447 elsif ($unit eq 'month') {
2448 while ($irregularities{$issueno}) {
2449 ($year, $month, $day) = _get_next_date_month
($subscription,
2450 $freqdata, $year, $month, $day);
2453 ($year, $month, $day) = _get_next_date_month
($subscription,
2454 $freqdata, $year, $month, $day);
2456 elsif ($unit eq 'year') {
2457 while ($irregularities{$issueno}) {
2458 ($year, $month, $day) = _get_next_date_year
($subscription,
2459 $freqdata, $year, $month, $day);
2462 ($year, $month, $day) = _get_next_date_year
($subscription,
2463 $freqdata, $year, $month, $day);
2467 my $dbh = C4
::Context
->dbh;
2470 SET countissuesperunit
= ?
2471 WHERE subscriptionid
= ?
2473 my $sth = $dbh->prepare($query);
2474 $sth->execute($subscription->{'countissuesperunit'}, $subscription->{'subscriptionid'});
2477 return sprintf("%04d-%02d-%02d", $year, $month, $day);
2483 $string = &_numeration($value,$num_type,$locale);
2485 _numeration returns the string corresponding to $value in the num_type
2498 my ($value, $num_type, $locale) = @_;
2503 if ( $num_type =~ /^dayname$/ or $num_type =~ /^dayabrv$/ ) {
2504 # 1970-11-01 was a Sunday
2505 $value = $value % 7;
2506 my $dt = DateTime
->new(
2512 $string = $num_type =~ /^dayname$/
2513 ?
$dt->strftime("%A")
2514 : $dt->strftime("%a");
2515 } elsif ( $num_type =~ /^monthname$/ or $num_type =~ /^monthabrv$/ ) {
2516 $value = $value % 12;
2517 my $dt = DateTime
->new(
2519 month
=> $value + 1,
2522 $string = $num_type =~ /^monthname$/
2523 ?
$dt->strftime("%B")
2524 : $dt->strftime("%b");
2525 } elsif ( $num_type =~ /^season$/ ) {
2526 my @seasons= qw( Spring Summer Fall Winter );
2527 $value = $value % 4;
2528 $string = $seasons[$value];
2529 } elsif ( $num_type =~ /^seasonabrv$/ ) {
2530 my @seasonsabrv= qw( Spr Sum Fal Win );
2531 $value = $value % 4;
2532 $string = $seasonsabrv[$value];
2540 =head2 is_barcode_in_use
2542 Returns number of occurrences of the barcode in the items table
2543 Can be used as a boolean test of whether the barcode has
2544 been deployed as yet
2548 sub is_barcode_in_use
{
2549 my $barcode = shift;
2550 my $dbh = C4
::Context
->dbh;
2551 my $occurrences = $dbh->selectall_arrayref(
2552 'SELECT itemnumber from items where barcode = ?',
2557 return @
{$occurrences};
2560 =head2 CloseSubscription
2561 Close a subscription given a subscriptionid
2563 sub CloseSubscription
{
2564 my ( $subscriptionid ) = @_;
2565 return unless $subscriptionid;
2566 my $dbh = C4
::Context
->dbh;
2567 my $sth = $dbh->prepare( q{
2570 WHERE subscriptionid = ?
2572 $sth->execute( $subscriptionid );
2574 # Set status = missing when status = stopped
2575 $sth = $dbh->prepare( q{
2578 WHERE subscriptionid = ?
2581 $sth->execute( STOPPED
, $subscriptionid, EXPECTED
);
2584 =head2 ReopenSubscription
2585 Reopen a subscription given a subscriptionid
2587 sub ReopenSubscription
{
2588 my ( $subscriptionid ) = @_;
2589 return unless $subscriptionid;
2590 my $dbh = C4
::Context
->dbh;
2591 my $sth = $dbh->prepare( q{
2594 WHERE subscriptionid = ?
2596 $sth->execute( $subscriptionid );
2598 # Set status = expected when status = stopped
2599 $sth = $dbh->prepare( q{
2602 WHERE subscriptionid = ?
2605 $sth->execute( EXPECTED
, $subscriptionid, STOPPED
);
2608 =head2 subscriptionCurrentlyOnOrder
2610 $bool = subscriptionCurrentlyOnOrder( $subscriptionid );
2612 Return 1 if subscription is currently on order else 0.
2616 sub subscriptionCurrentlyOnOrder
{
2617 my ( $subscriptionid ) = @_;
2618 my $dbh = C4
::Context
->dbh;
2620 SELECT COUNT
(*) FROM aqorders
2621 WHERE subscriptionid
= ?
2622 AND datereceived IS NULL
2623 AND datecancellationprinted IS NULL
2625 my $sth = $dbh->prepare( $query );
2626 $sth->execute($subscriptionid);
2627 return $sth->fetchrow_array;
2630 =head2 can_claim_subscription
2632 $can = can_claim_subscription( $subscriptionid[, $userid] );
2634 Return 1 if the subscription can be claimed by the current logged user (or a given $userid), else 0.
2638 sub can_claim_subscription
{
2639 my ( $subscription, $userid ) = @_;
2640 return _can_do_on_subscription
( $subscription, $userid, 'claim_serials' );
2643 =head2 can_edit_subscription
2645 $can = can_edit_subscription( $subscriptionid[, $userid] );
2647 Return 1 if the subscription can be edited by the current logged user (or a given $userid), else 0.
2651 sub can_edit_subscription
{
2652 my ( $subscription, $userid ) = @_;
2653 return _can_do_on_subscription
( $subscription, $userid, 'edit_subscription' );
2656 =head2 can_show_subscription
2658 $can = can_show_subscription( $subscriptionid[, $userid] );
2660 Return 1 if the subscription can be shown by the current logged user (or a given $userid), else 0.
2664 sub can_show_subscription
{
2665 my ( $subscription, $userid ) = @_;
2666 return _can_do_on_subscription
( $subscription, $userid, '*' );
2669 sub _can_do_on_subscription
{
2670 my ( $subscription, $userid, $permission ) = @_;
2671 return 0 unless C4
::Context
->userenv;
2672 my $flags = C4
::Context
->userenv->{flags
};
2673 $userid ||= C4
::Context
->userenv->{'id'};
2675 if ( C4
::Context
->preference('IndependentBranches') ) {
2677 if C4
::Context
->IsSuperLibrarian()
2679 C4
::Auth
::haspermission
( $userid, { serials
=> 'superserials' } )
2681 C4
::Auth
::haspermission
( $userid,
2682 { serials
=> $permission } )
2683 and ( not defined $subscription->{branchcode
}
2684 or $subscription->{branchcode
} eq ''
2685 or $subscription->{branchcode
} eq
2686 C4
::Context
->userenv->{'branch'} )
2691 if C4
::Context
->IsSuperLibrarian()
2693 C4
::Auth
::haspermission
( $userid, { serials
=> 'superserials' } )
2694 or C4
::Auth
::haspermission
(
2695 $userid, { serials
=> $permission }
2702 =head2 findSerialByStatus
2704 @serials = findSerialByStatus($status, $subscriptionid);
2706 Returns an array of serials matching a given status and subscription id.
2710 sub findSerialByStatus
{
2711 my($status, $subscriptionid) = @_;
2712 my $dbh = C4
::Context
->dbh;
2713 my $query = q
| SELECT
* from serial
2715 AND subscriptionid
= ?
2717 my $serials = $dbh->selectall_arrayref( $query, { Slice
=> {} }, $status, $subscriptionid );
2725 Koha Development Team <http://koha-community.org/>