1 package C4::Serials; #assumes C4/Serials.pm
3 # Copyright 2000-2002 Katipo Communications
5 # This file is part of Koha.
7 # Koha is free software; you can redistribute it and/or modify it under the
8 # terms of the GNU General Public License as published by the Free Software
9 # Foundation; either version 2 of the License, or (at your option) any later
12 # Koha is distributed in the hope that it will be useful, but WITHOUT ANY
13 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
14 # A PARTICULAR PURPOSE. See the GNU General Public License for more details.
16 # You should have received a copy of the GNU General Public License along
17 # with Koha; if not, write to the Free Software Foundation, Inc.,
18 # 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
21 use C4::Dates qw(format_date);
22 use Date::Calc qw(:all);
23 use POSIX qw(strftime);
31 use C4::Log; # logaction
34 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
37 $VERSION = 3.01; # set version for version checking
41 &NewSubscription &ModSubscription &DelSubscription &GetSubscriptions
42 &GetSubscription &CountSubscriptionFromBiblionumber &GetSubscriptionsFromBiblionumber
43 &GetFullSubscriptionsFromBiblionumber &GetFullSubscription &ModSubscriptionHistory
44 &HasSubscriptionStrictlyExpired &HasSubscriptionExpired &GetExpirationDate &abouttoexpire
46 &GetNextSeq &NewIssue &ItemizeSerials &GetSerials
47 &GetLatestSerials &ModSerialStatus &GetNextDate &GetSerials2
48 &ReNewSubscription &GetLateIssues &GetLateOrMissingIssues
49 &GetSerialInformation &AddItem2Serial
50 &PrepareSerialsData &GetNextExpected &ModNextExpected
52 &UpdateClaimdateIssues
53 &GetSuppliersWithLateIssues &getsupplierbyserialid
54 &GetDistributedTo &SetDistributedTo
55 &getroutinglist &delroutingmember &addroutingmember
57 &check_routing &updateClaim &removeMissingIssue
64 =head2 GetSuppliersWithLateIssues
68 C4::Serials - Give functions for serializing.
76 Give all XYZ functions
82 %supplierlist = &GetSuppliersWithLateIssues
84 this function get all suppliers with late issues.
87 an array_ref of suppliers each entry is a hash_ref containing id and name
88 the array is in name order
94 sub GetSuppliersWithLateIssues {
95 my $dbh = C4::Context->dbh;
97 SELECT DISTINCT id, name
99 LEFT JOIN serial ON serial.subscriptionid=subscription.subscriptionid
100 LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id
101 WHERE subscription.subscriptionid = serial.subscriptionid
102 AND (planneddate < now() OR serial.STATUS = 3 OR serial.STATUS = 4)
105 return $dbh->selectall_arrayref($query, { Slice => {} });
112 @issuelist = &GetLateIssues($supplierid)
114 this function select late issues on database
117 the issuelist into an table. Each line of this table containts a ref to a hash which it containts
118 name,title,planneddate,serialseq,serial.subscriptionid from tables : subscription, serial & biblio
125 my ($supplierid) = @_;
126 my $dbh = C4::Context->dbh;
130 SELECT name,title,planneddate,serialseq,serial.subscriptionid
132 LEFT JOIN serial ON subscription.subscriptionid = serial.subscriptionid
133 LEFT JOIN biblio ON biblio.biblionumber = subscription.biblionumber
134 LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id
135 WHERE ((planneddate < now() AND serial.STATUS =1) OR serial.STATUS = 3)
136 AND subscription.aqbooksellerid=$supplierid
139 $sth = $dbh->prepare($query);
142 SELECT name,title,planneddate,serialseq,serial.subscriptionid
144 LEFT JOIN serial ON subscription.subscriptionid = serial.subscriptionid
145 LEFT JOIN biblio ON biblio.biblionumber = subscription.biblionumber
146 LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id
147 WHERE ((planneddate < now() AND serial.STATUS =1) OR serial.STATUS = 3)
150 $sth = $dbh->prepare($query);
156 while ( my $line = $sth->fetchrow_hashref ) {
157 $odd++ unless $line->{title} eq $last_title;
158 $line->{title} = "" if $line->{title} eq $last_title;
159 $last_title = $line->{title} if ( $line->{title} );
160 $line->{planneddate} = format_date( $line->{planneddate} );
161 push @issuelist, $line;
166 =head2 GetSubscriptionHistoryFromSubscriptionId
170 $sth = GetSubscriptionHistoryFromSubscriptionId()
171 this function just prepare the SQL request.
172 After this function, don't forget to execute it by using $sth->execute($subscriptionid)
174 $sth = $dbh->prepare($query).
180 sub GetSubscriptionHistoryFromSubscriptionId() {
181 my $dbh = C4::Context->dbh;
184 FROM subscriptionhistory
185 WHERE subscriptionid = ?
187 return $dbh->prepare($query);
190 =head2 GetSerialStatusFromSerialId
194 $sth = GetSerialStatusFromSerialId();
195 this function just prepare the SQL request.
196 After this function, don't forget to execute it by using $sth->execute($serialid)
198 $sth = $dbh->prepare($query).
204 sub GetSerialStatusFromSerialId() {
205 my $dbh = C4::Context->dbh;
211 return $dbh->prepare($query);
214 =head2 GetSerialInformation
218 $data = GetSerialInformation($serialid);
219 returns a hash containing :
220 items : items marcrecord (can be an array)
222 subscription table field
223 + information about subscription expiration
229 sub GetSerialInformation {
231 my $dbh = C4::Context->dbh;
233 SELECT serial.*, serial.notes as sernotes, serial.status as serstatus,subscription.*,subscription.subscriptionid as subsid |;
234 if ( C4::Context->preference('IndependantBranches')
235 && C4::Context->userenv
236 && C4::Context->userenv->{'flags'} != 1
237 && C4::Context->userenv->{'branch'} ) {
239 , ((subscription.branchcode <>\"" . C4::Context->userenv->{'branch'} . "\") and subscription.branchcode <>\"\" and subscription.branchcode IS NOT NULL) as cannotedit ";
242 FROM serial LEFT JOIN subscription ON subscription.subscriptionid=serial.subscriptionid
245 my $rq = $dbh->prepare($query);
246 $rq->execute($serialid);
247 my $data = $rq->fetchrow_hashref;
249 # create item information if we have serialsadditems for this subscription
250 if ( $data->{'serialsadditems'} ) {
251 my $queryitem = $dbh->prepare("SELECT itemnumber from serialitems where serialid=?");
252 $queryitem->execute($serialid);
253 my $itemnumbers = $queryitem->fetchall_arrayref( [0] );
254 if ( scalar(@$itemnumbers) > 0 ) {
255 foreach my $itemnum (@$itemnumbers) {
257 #It is ASSUMED that GetMarcItem ALWAYS WORK...
258 #Maybe GetMarcItem should return values on failure
259 $debug and warn "itemnumber :$itemnum->[0], bibnum :" . $data->{'biblionumber'};
260 my $itemprocessed = PrepareItemrecordDisplay( $data->{'biblionumber'}, $itemnum->[0], $data );
261 $itemprocessed->{'itemnumber'} = $itemnum->[0];
262 $itemprocessed->{'itemid'} = $itemnum->[0];
263 $itemprocessed->{'serialid'} = $serialid;
264 $itemprocessed->{'biblionumber'} = $data->{'biblionumber'};
265 push @{ $data->{'items'} }, $itemprocessed;
268 my $itemprocessed = PrepareItemrecordDisplay( $data->{'biblionumber'}, '', $data );
269 $itemprocessed->{'itemid'} = "N$serialid";
270 $itemprocessed->{'serialid'} = $serialid;
271 $itemprocessed->{'biblionumber'} = $data->{'biblionumber'};
272 $itemprocessed->{'countitems'} = 0;
273 push @{ $data->{'items'} }, $itemprocessed;
276 $data->{ "status" . $data->{'serstatus'} } = 1;
277 $data->{'subscriptionexpired'} = HasSubscriptionExpired( $data->{'subscriptionid'} ) && $data->{'status'} == 1;
278 $data->{'abouttoexpire'} = abouttoexpire( $data->{'subscriptionid'} );
282 =head2 AddItem2Serial
286 $data = AddItem2Serial($serialid,$itemnumber);
287 Adds an itemnumber to Serial record
294 my ( $serialid, $itemnumber ) = @_;
295 my $dbh = C4::Context->dbh;
296 my $rq = $dbh->prepare("INSERT INTO `serialitems` SET serialid=? , itemnumber=?");
297 $rq->execute( $serialid, $itemnumber );
301 =head2 UpdateClaimdateIssues
305 UpdateClaimdateIssues($serialids,[$date]);
307 Update Claimdate for issues in @$serialids list with date $date
314 sub UpdateClaimdateIssues {
315 my ( $serialids, $date ) = @_;
316 my $dbh = C4::Context->dbh;
317 $date = strftime( "%Y-%m-%d", localtime ) unless ($date);
319 UPDATE serial SET claimdate=$date,status=7
320 WHERE serialid in (" . join( ",", @$serialids ) . ")";
321 my $rq = $dbh->prepare($query);
326 =head2 GetSubscription
330 $subs = GetSubscription($subscriptionid)
331 this function get the subscription which has $subscriptionid as id.
333 a hashref. This hash containts
334 subscription, subscriptionhistory, aqbudget.bookfundid, biblio.title
340 sub GetSubscription {
341 my ($subscriptionid) = @_;
342 my $dbh = C4::Context->dbh;
344 SELECT subscription.*,
345 subscriptionhistory.*,
346 aqbooksellers.name AS aqbooksellername,
347 biblio.title AS bibliotitle,
348 subscription.biblionumber as bibnum);
349 if ( C4::Context->preference('IndependantBranches')
350 && C4::Context->userenv
351 && C4::Context->userenv->{'flags'} != 1
352 && C4::Context->userenv->{'branch'} ) {
354 , ((subscription.branchcode <>\"" . C4::Context->userenv->{'branch'} . "\") and subscription.branchcode <>\"\" and subscription.branchcode IS NOT NULL) as cannotedit ";
358 LEFT JOIN subscriptionhistory ON subscription.subscriptionid=subscriptionhistory.subscriptionid
359 LEFT JOIN aqbooksellers ON subscription.aqbooksellerid=aqbooksellers.id
360 LEFT JOIN biblio ON biblio.biblionumber=subscription.biblionumber
361 WHERE subscription.subscriptionid = ?
364 # if (C4::Context->preference('IndependantBranches') &&
365 # C4::Context->userenv &&
366 # C4::Context->userenv->{'flags'} != 1){
367 # # $debug and warn "flags: ".C4::Context->userenv->{'flags'};
368 # $query.=" AND subscription.branchcode IN ('".C4::Context->userenv->{'branch'}."',\"\")";
370 $debug and warn "query : $query\nsubsid :$subscriptionid";
371 my $sth = $dbh->prepare($query);
372 $sth->execute($subscriptionid);
373 return $sth->fetchrow_hashref;
376 =head2 GetFullSubscription
380 \@res = GetFullSubscription($subscriptionid)
381 this function read on serial table.
387 sub GetFullSubscription {
388 my ($subscriptionid) = @_;
389 my $dbh = C4::Context->dbh;
391 SELECT serial.serialid,
394 serial.publisheddate,
396 serial.notes as notes,
397 year(IF(serial.publisheddate="00-00-0000",serial.planneddate,serial.publisheddate)) as year,
398 aqbooksellers.name as aqbooksellername,
399 biblio.title as bibliotitle,
400 subscription.branchcode AS branchcode,
401 subscription.subscriptionid AS subscriptionid |;
402 if ( C4::Context->preference('IndependantBranches')
403 && C4::Context->userenv
404 && C4::Context->userenv->{'flags'} != 1
405 && C4::Context->userenv->{'branch'} ) {
407 , ((subscription.branchcode <>\"" . C4::Context->userenv->{'branch'} . "\") and subscription.branchcode <>\"\" and subscription.branchcode IS NOT NULL) as cannotedit ";
411 LEFT JOIN subscription ON
412 (serial.subscriptionid=subscription.subscriptionid )
413 LEFT JOIN aqbooksellers on subscription.aqbooksellerid=aqbooksellers.id
414 LEFT JOIN biblio on biblio.biblionumber=subscription.biblionumber
415 WHERE serial.subscriptionid = ?
417 IF(serial.publisheddate="00-00-0000",serial.planneddate,serial.publisheddate) DESC,
418 serial.subscriptionid
420 $debug and warn "GetFullSubscription query: $query";
421 my $sth = $dbh->prepare($query);
422 $sth->execute($subscriptionid);
423 return $sth->fetchall_arrayref( {} );
426 =head2 PrepareSerialsData
430 \@res = PrepareSerialsData($serialinfomation)
431 where serialinformation is a hashref array
437 sub PrepareSerialsData {
443 my $aqbooksellername;
447 my $previousnote = "";
449 foreach my $subs (@$lines) {
450 $subs->{'publisheddate'} = (
451 $subs->{'publisheddate'}
452 ? format_date( $subs->{'publisheddate'} )
455 $subs->{'branchname'} = GetBranchName( $subs->{'branchcode'} );
456 $subs->{'planneddate'} = format_date( $subs->{'planneddate'} );
457 $subs->{ "status" . $subs->{'status'} } = 1;
458 $subs->{"checked"} = $subs->{'status'} =~ /1|3|4|7/;
460 if ( $subs->{'year'} && $subs->{'year'} ne "" ) {
461 $year = $subs->{'year'};
465 if ( $tmpresults{$year} ) {
466 push @{ $tmpresults{$year}->{'serials'} }, $subs;
468 $tmpresults{$year} = {
470 'aqbooksellername' => $subs->{'aqbooksellername'},
471 'bibliotitle' => $subs->{'bibliotitle'},
472 'serials' => [$subs],
477 foreach my $key ( sort { $b cmp $a } keys %tmpresults ) {
478 push @res, $tmpresults{$key};
480 $res[0]->{'first'} = 1;
484 =head2 GetSubscriptionsFromBiblionumber
486 \@res = GetSubscriptionsFromBiblionumber($biblionumber)
487 this function get the subscription list. it reads on subscription table.
489 table of subscription which has the biblionumber given on input arg.
490 each line of this table is a hashref. All hashes containt
491 startdate, histstartdate,opacnote,missinglist,recievedlist,periodicity,status & enddate
495 sub GetSubscriptionsFromBiblionumber {
496 my ($biblionumber) = @_;
497 my $dbh = C4::Context->dbh;
499 SELECT subscription.*,
501 subscriptionhistory.*,
502 aqbooksellers.name AS aqbooksellername,
503 biblio.title AS bibliotitle
505 LEFT JOIN subscriptionhistory ON subscription.subscriptionid=subscriptionhistory.subscriptionid
506 LEFT JOIN aqbooksellers ON subscription.aqbooksellerid=aqbooksellers.id
507 LEFT JOIN biblio ON biblio.biblionumber=subscription.biblionumber
508 LEFT JOIN branches ON branches.branchcode=subscription.branchcode
509 WHERE subscription.biblionumber = ?
511 my $sth = $dbh->prepare($query);
512 $sth->execute($biblionumber);
514 while ( my $subs = $sth->fetchrow_hashref ) {
515 $subs->{startdate} = format_date( $subs->{startdate} );
516 $subs->{histstartdate} = format_date( $subs->{histstartdate} );
517 $subs->{histenddate} = format_date( $subs->{histenddate} );
518 $subs->{opacnote} =~ s/\n/\<br\/\>/g;
519 $subs->{missinglist} =~ s/\n/\<br\/\>/g;
520 $subs->{recievedlist} =~ s/\n/\<br\/\>/g;
521 $subs->{ "periodicity" . $subs->{periodicity} } = 1;
522 $subs->{ "numberpattern" . $subs->{numberpattern} } = 1;
523 $subs->{ "status" . $subs->{'status'} } = 1;
524 $subs->{'cannotedit'} =
525 ( C4::Context->preference('IndependantBranches')
526 && C4::Context->userenv
527 && C4::Context->userenv->{flags} % 2 != 1
528 && C4::Context->userenv->{branch}
529 && $subs->{branchcode}
530 && ( C4::Context->userenv->{branch} ne $subs->{branchcode} ) );
532 if ( $subs->{enddate} eq '0000-00-00' ) {
533 $subs->{enddate} = '';
535 $subs->{enddate} = format_date( $subs->{enddate} );
537 $subs->{'abouttoexpire'} = abouttoexpire( $subs->{'subscriptionid'} );
538 $subs->{'subscriptionexpired'} = HasSubscriptionExpired( $subs->{'subscriptionid'} );
544 =head2 GetFullSubscriptionsFromBiblionumber
548 \@res = GetFullSubscriptionsFromBiblionumber($biblionumber)
549 this function read on serial table.
555 sub GetFullSubscriptionsFromBiblionumber {
556 my ($biblionumber) = @_;
557 my $dbh = C4::Context->dbh;
559 SELECT serial.serialid,
562 serial.publisheddate,
564 serial.notes as notes,
565 year(IF(serial.publisheddate="00-00-0000",serial.planneddate,serial.publisheddate)) as year,
566 biblio.title as bibliotitle,
567 subscription.branchcode AS branchcode,
568 subscription.subscriptionid AS subscriptionid|;
569 if ( C4::Context->preference('IndependantBranches')
570 && C4::Context->userenv
571 && C4::Context->userenv->{'flags'} != 1
572 && C4::Context->userenv->{'branch'} ) {
574 , ((subscription.branchcode <>\"" . C4::Context->userenv->{'branch'} . "\") and subscription.branchcode <>\"\" and subscription.branchcode IS NOT NULL) as cannotedit ";
579 LEFT JOIN subscription ON
580 (serial.subscriptionid=subscription.subscriptionid)
581 LEFT JOIN aqbooksellers on subscription.aqbooksellerid=aqbooksellers.id
582 LEFT JOIN biblio on biblio.biblionumber=subscription.biblionumber
583 WHERE subscription.biblionumber = ?
585 IF(serial.publisheddate="00-00-0000",serial.planneddate,serial.publisheddate) DESC,
586 serial.subscriptionid
588 my $sth = $dbh->prepare($query);
589 $sth->execute($biblionumber);
590 return $sth->fetchall_arrayref( {} );
593 =head2 GetSubscriptions
597 @results = GetSubscriptions($title,$ISSN,$biblionumber);
598 this function get all subscriptions which has title like $title,ISSN like $ISSN and biblionumber like $biblionumber.
600 a table of hashref. Each hash containt the subscription.
606 sub GetSubscriptions {
607 my ( $string, $issn, $biblionumber ) = @_;
609 #return unless $title or $ISSN or $biblionumber;
610 my $dbh = C4::Context->dbh;
613 SELECT subscription.*, subscriptionhistory.*, biblio.title,biblioitems.issn,biblio.biblionumber
615 LEFT JOIN subscriptionhistory USING(subscriptionid)
616 LEFT JOIN biblio ON biblio.biblionumber = subscription.biblionumber
617 LEFT JOIN biblioitems ON biblio.biblionumber = biblioitems.biblionumber
622 $sqlwhere = " WHERE biblio.biblionumber=?";
623 push @bind_params, $biblionumber;
627 my @strings_to_search;
628 @strings_to_search = map { "%$_%" } split( / /, $string );
629 foreach my $index qw(biblio.title subscription.callnumber subscription.location subscription.notes subscription.internalnotes) {
630 push @bind_params, @strings_to_search;
631 my $tmpstring = "AND $index LIKE ? " x scalar(@strings_to_search);
632 $debug && warn "$tmpstring";
633 $tmpstring =~ s/^AND //;
634 push @sqlstrings, $tmpstring;
636 $sqlwhere .= ( $sqlwhere ? " AND " : " WHERE " ) . "(" . join( ") OR (", @sqlstrings ) . ")";
640 my @strings_to_search;
641 @strings_to_search = map { "%$_%" } split( / /, $issn );
642 foreach my $index qw(biblioitems.issn subscription.callnumber) {
643 push @bind_params, @strings_to_search;
644 my $tmpstring = "OR $index LIKE ? " x scalar(@strings_to_search);
645 $debug && warn "$tmpstring";
646 $tmpstring =~ s/^OR //;
647 push @sqlstrings, $tmpstring;
649 $sqlwhere .= ( $sqlwhere ? " AND " : " WHERE " ) . "(" . join( ") OR (", @sqlstrings ) . ")";
651 $sql .= "$sqlwhere ORDER BY title";
652 $debug and warn "GetSubscriptions query: $sql params : ", join( " ", @bind_params );
653 $sth = $dbh->prepare($sql);
654 $sth->execute(@bind_params);
656 my $previoustitle = "";
659 while ( my $line = $sth->fetchrow_hashref ) {
660 if ( $previoustitle eq $line->{title} ) {
664 $previoustitle = $line->{title};
667 $line->{toggle} = 1 if $odd == 1;
668 $line->{'cannotedit'} =
669 ( C4::Context->preference('IndependantBranches')
670 && C4::Context->userenv
671 && C4::Context->userenv->{flags} % 2 != 1
672 && C4::Context->userenv->{branch}
673 && $line->{branchcode}
674 && ( C4::Context->userenv->{branch} ne $line->{branchcode} ) );
675 push @results, $line;
684 ($totalissues,@serials) = GetSerials($subscriptionid);
685 this function get every serial not arrived for a given subscription
686 as well as the number of issues registered in the database (all types)
687 this number is used to see if a subscription can be deleted (=it must have only 1 issue)
689 FIXME: We should return \@serials.
696 my ( $subscriptionid, $count ) = @_;
697 my $dbh = C4::Context->dbh;
699 # status = 2 is "arrived"
701 $count = 5 unless ($count);
703 my $query = "SELECT serialid,serialseq, status, publisheddate, planneddate,notes, routingnotes
705 WHERE subscriptionid = ? AND status NOT IN (2,4,5)
706 ORDER BY IF(publisheddate<>'0000-00-00',publisheddate,planneddate) DESC";
707 my $sth = $dbh->prepare($query);
708 $sth->execute($subscriptionid);
710 while ( my $line = $sth->fetchrow_hashref ) {
711 $line->{ "status" . $line->{status} } = 1; # fills a "statusX" value, used for template status select list
712 $line->{"publisheddate"} = format_date( $line->{"publisheddate"} );
713 $line->{"planneddate"} = format_date( $line->{"planneddate"} );
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 (2,4,5))
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 $line->{"planneddate"} = format_date( $line->{"planneddate"} );
730 $line->{"publisheddate"} = format_date( $line->{"publisheddate"} );
731 push @serials, $line;
734 $query = "SELECT count(*) FROM serial WHERE subscriptionid=?";
735 $sth = $dbh->prepare($query);
736 $sth->execute($subscriptionid);
737 my ($totalissues) = $sth->fetchrow;
738 return ( $totalissues, @serials );
745 @serials = GetSerials2($subscriptionid,$status);
746 this function gets every serial waited for a given subscription
747 as well as the number of issues registered in the database (all types)
748 this number is used to see if a subscription can be deleted (=it must have only 1 issue)
755 my ( $subscription, $status ) = @_;
756 my $dbh = C4::Context->dbh;
758 SELECT serialid,serialseq, status, planneddate, publisheddate,notes, routingnotes
760 WHERE subscriptionid=$subscription AND status IN ($status)
761 ORDER BY publisheddate,serialid DESC
763 $debug and warn "GetSerials2 query: $query";
764 my $sth = $dbh->prepare($query);
768 while ( my $line = $sth->fetchrow_hashref ) {
769 $line->{ "status" . $line->{status} } = 1; # fills a "statusX" value, used for template status select list
770 $line->{"planneddate"} = format_date( $line->{"planneddate"} );
771 $line->{"publisheddate"} = format_date( $line->{"publisheddate"} );
772 push @serials, $line;
777 =head2 GetLatestSerials
781 \@serials = GetLatestSerials($subscriptionid,$limit)
782 get the $limit's latest serials arrived or missing for a given subscription
784 a ref to a table which it containts all of the latest serials stored into a hash.
790 sub GetLatestSerials {
791 my ( $subscriptionid, $limit ) = @_;
792 my $dbh = C4::Context->dbh;
794 # status = 2 is "arrived"
795 my $strsth = "SELECT serialid,serialseq, status, planneddate, notes
797 WHERE subscriptionid = ?
798 AND (status =2 or status=4)
799 ORDER BY planneddate DESC LIMIT 0,$limit
801 my $sth = $dbh->prepare($strsth);
802 $sth->execute($subscriptionid);
804 while ( my $line = $sth->fetchrow_hashref ) {
805 $line->{ "status" . $line->{status} } = 1; # fills a "statusX" value, used for template status select list
806 $line->{"planneddate"} = format_date( $line->{"planneddate"} );
807 push @serials, $line;
813 =head2 GetDistributedTo
817 $distributedto=GetDistributedTo($subscriptionid)
818 This function select the old previous value of distributedto in the database.
824 sub GetDistributedTo {
825 my $dbh = C4::Context->dbh;
827 my $subscriptionid = @_;
828 my $query = "SELECT distributedto FROM subscription WHERE subscriptionid=?";
829 my $sth = $dbh->prepare($query);
830 $sth->execute($subscriptionid);
831 return ($distributedto) = $sth->fetchrow;
839 $val is a hashref containing all the attributes of the table 'subscription'
840 This function get the next issue for the subscription given on input arg
842 all the input params updated.
850 # my ($calculated,$newlastvalue1,$newlastvalue2,$newlastvalue3,$newinnerloop1,$newinnerloop2,$newinnerloop3);
851 # $calculated = $val->{numberingmethod};
852 # # calculate the (expected) value of the next issue recieved.
853 # $newlastvalue1 = $val->{lastvalue1};
854 # # check if we have to increase the new value.
855 # $newinnerloop1 = $val->{innerloop1}+1;
856 # $newinnerloop1=0 if ($newinnerloop1 >= $val->{every1});
857 # $newlastvalue1 += $val->{add1} if ($newinnerloop1<1); # <1 to be true when 0 or empty.
858 # $newlastvalue1=$val->{setto1} if ($newlastvalue1>$val->{whenmorethan1}); # reset counter if needed.
859 # $calculated =~ s/\{X\}/$newlastvalue1/g;
861 # $newlastvalue2 = $val->{lastvalue2};
862 # # check if we have to increase the new value.
863 # $newinnerloop2 = $val->{innerloop2}+1;
864 # $newinnerloop2=0 if ($newinnerloop2 >= $val->{every2});
865 # $newlastvalue2 += $val->{add2} if ($newinnerloop2<1); # <1 to be true when 0 or empty.
866 # $newlastvalue2=$val->{setto2} if ($newlastvalue2>$val->{whenmorethan2}); # reset counter if needed.
867 # $calculated =~ s/\{Y\}/$newlastvalue2/g;
869 # $newlastvalue3 = $val->{lastvalue3};
870 # # check if we have to increase the new value.
871 # $newinnerloop3 = $val->{innerloop3}+1;
872 # $newinnerloop3=0 if ($newinnerloop3 >= $val->{every3});
873 # $newlastvalue3 += $val->{add3} if ($newinnerloop3<1); # <1 to be true when 0 or empty.
874 # $newlastvalue3=$val->{setto3} if ($newlastvalue3>$val->{whenmorethan3}); # reset counter if needed.
875 # $calculated =~ s/\{Z\}/$newlastvalue3/g;
876 # return ($calculated,$newlastvalue1,$newlastvalue2,$newlastvalue3,$newinnerloop1,$newinnerloop2,$newinnerloop3);
881 my ( $calculated, $newlastvalue1, $newlastvalue2, $newlastvalue3, $newinnerloop1, $newinnerloop2, $newinnerloop3 );
882 my $pattern = $val->{numberpattern};
883 my @seasons = ( 'nothing', 'Winter', 'Spring', 'Summer', 'Autumn' );
884 my @southern_seasons = ( '', 'Summer', 'Autumn', 'Winter', 'Spring' );
885 $calculated = $val->{numberingmethod};
886 $newlastvalue1 = $val->{lastvalue1};
887 $newlastvalue2 = $val->{lastvalue2};
888 $newlastvalue3 = $val->{lastvalue3};
889 $newlastvalue1 = $val->{lastvalue1};
891 # check if we have to increase the new value.
892 $newinnerloop1 = $val->{innerloop1} + 1;
893 $newinnerloop1 = 0 if ( $newinnerloop1 >= $val->{every1} );
894 $newlastvalue1 += $val->{add1} if ( $newinnerloop1 < 1 ); # <1 to be true when 0 or empty.
895 $newlastvalue1 = $val->{setto1} if ( $newlastvalue1 > $val->{whenmorethan1} ); # reset counter if needed.
896 $calculated =~ s/\{X\}/$newlastvalue1/g;
898 $newlastvalue2 = $val->{lastvalue2};
900 # check if we have to increase the new value.
901 $newinnerloop2 = $val->{innerloop2} + 1;
902 $newinnerloop2 = 0 if ( $newinnerloop2 >= $val->{every2} );
903 $newlastvalue2 += $val->{add2} if ( $newinnerloop2 < 1 ); # <1 to be true when 0 or empty.
904 $newlastvalue2 = $val->{setto2} if ( $newlastvalue2 > $val->{whenmorethan2} ); # reset counter if needed.
905 if ( $pattern == 6 ) {
906 if ( $val->{hemisphere} == 2 ) {
907 my $newlastvalue2seq = $southern_seasons[$newlastvalue2];
908 $calculated =~ s/\{Y\}/$newlastvalue2seq/g;
910 my $newlastvalue2seq = $seasons[$newlastvalue2];
911 $calculated =~ s/\{Y\}/$newlastvalue2seq/g;
914 $calculated =~ s/\{Y\}/$newlastvalue2/g;
917 $newlastvalue3 = $val->{lastvalue3};
919 # check if we have to increase the new value.
920 $newinnerloop3 = $val->{innerloop3} + 1;
921 $newinnerloop3 = 0 if ( $newinnerloop3 >= $val->{every3} );
922 $newlastvalue3 += $val->{add3} if ( $newinnerloop3 < 1 ); # <1 to be true when 0 or empty.
923 $newlastvalue3 = $val->{setto3} if ( $newlastvalue3 > $val->{whenmorethan3} ); # reset counter if needed.
924 $calculated =~ s/\{Z\}/$newlastvalue3/g;
926 return ( $calculated, $newlastvalue1, $newlastvalue2, $newlastvalue3, $newinnerloop1, $newinnerloop2, $newinnerloop3 );
933 $calculated = GetSeq($val)
934 $val is a hashref containing all the attributes of the table 'subscription'
935 this function transforms {X},{Y},{Z} to 150,0,0 for example.
937 the sequence in integer format
945 my $pattern = $val->{numberpattern};
946 my @seasons = ( 'nothing', 'Winter', 'Spring', 'Summer', 'Autumn' );
947 my @southern_seasons = ( '', 'Summer', 'Autumn', 'Winter', 'Spring' );
948 my $calculated = $val->{numberingmethod};
949 my $x = $val->{'lastvalue1'};
950 $calculated =~ s/\{X\}/$x/g;
951 my $newlastvalue2 = $val->{'lastvalue2'};
953 if ( $pattern == 6 ) {
954 if ( $val->{hemisphere} == 2 ) {
955 my $newlastvalue2seq = $southern_seasons[$newlastvalue2];
956 $calculated =~ s/\{Y\}/$newlastvalue2seq/g;
958 my $newlastvalue2seq = $seasons[$newlastvalue2];
959 $calculated =~ s/\{Y\}/$newlastvalue2seq/g;
962 $calculated =~ s/\{Y\}/$newlastvalue2/g;
964 my $z = $val->{'lastvalue3'};
965 $calculated =~ s/\{Z\}/$z/g;
969 =head2 GetExpirationDate
971 $sensddate = GetExpirationDate($subscriptionid)
973 this function return the next expiration date for a subscription given on input args.
980 sub GetExpirationDate {
981 my ( $subscriptionid, $startdate ) = @_;
982 my $dbh = C4::Context->dbh;
983 my $subscription = GetSubscription($subscriptionid);
986 # we don't do the same test if the subscription is based on X numbers or on X weeks/months
987 $enddate = $startdate || $subscription->{startdate};
988 my @date = split( /-/, $enddate );
989 return if ( scalar(@date) != 3 || not check_date(@date) );
990 if ( ( $subscription->{periodicity} % 16 ) > 0 ) {
993 if ( my $length = $subscription->{numberlength} ) {
995 #calculate the date of the last issue.
996 for ( my $i = 1 ; $i <= $length ; $i++ ) {
997 $enddate = GetNextDate( $enddate, $subscription );
999 } elsif ( $subscription->{monthlength} ) {
1000 if ( $$subscription{startdate} ) {
1001 my @enddate = Add_Delta_YM( $date[0], $date[1], $date[2], 0, $subscription->{monthlength} );
1002 $enddate = sprintf( "%04d-%02d-%02d", $enddate[0], $enddate[1], $enddate[2] );
1004 } elsif ( $subscription->{weeklength} ) {
1005 if ( $$subscription{startdate} ) {
1006 my @date = split( /-/, $subscription->{startdate} );
1007 my @enddate = Add_Delta_Days( $date[0], $date[1], $date[2], $subscription->{weeklength} * 7 );
1008 $enddate = sprintf( "%04d-%02d-%02d", $enddate[0], $enddate[1], $enddate[2] );
1017 =head2 CountSubscriptionFromBiblionumber
1021 $subscriptionsnumber = CountSubscriptionFromBiblionumber($biblionumber)
1022 this count the number of subscription for a biblionumber given.
1024 the number of subscriptions with biblionumber given on input arg.
1030 sub CountSubscriptionFromBiblionumber {
1031 my ($biblionumber) = @_;
1032 my $dbh = C4::Context->dbh;
1033 my $query = "SELECT count(*) FROM subscription WHERE biblionumber=?";
1034 my $sth = $dbh->prepare($query);
1035 $sth->execute($biblionumber);
1036 my $subscriptionsnumber = $sth->fetchrow;
1037 return $subscriptionsnumber;
1040 =head2 ModSubscriptionHistory
1044 ModSubscriptionHistory($subscriptionid,$histstartdate,$enddate,$recievedlist,$missinglist,$opacnote,$librariannote);
1046 this function modify the history of a subscription. Put your new values on input arg.
1052 sub ModSubscriptionHistory {
1053 my ( $subscriptionid, $histstartdate, $enddate, $recievedlist, $missinglist, $opacnote, $librariannote ) = @_;
1054 my $dbh = C4::Context->dbh;
1055 my $query = "UPDATE subscriptionhistory
1056 SET histstartdate=?,histenddate=?,recievedlist=?,missinglist=?,opacnote=?,librariannote=?
1057 WHERE subscriptionid=?
1059 my $sth = $dbh->prepare($query);
1060 $recievedlist =~ s/^; //;
1061 $missinglist =~ s/^; //;
1062 $opacnote =~ s/^; //;
1063 $sth->execute( $histstartdate, $enddate, $recievedlist, $missinglist, $opacnote, $librariannote, $subscriptionid );
1067 =head2 ModSerialStatus
1071 ModSerialStatus($serialid,$serialseq, $planneddate,$publisheddate,$status,$notes)
1073 This function modify the serial status. Serial status is a number.(eg 2 is "arrived")
1074 Note : if we change from "waited" to something else,then we will have to create a new "waited" entry
1080 sub ModSerialStatus {
1081 my ( $serialid, $serialseq, $planneddate, $publisheddate, $status, $notes ) = @_;
1083 #It is a usual serial
1084 # 1st, get previous status :
1085 my $dbh = C4::Context->dbh;
1086 my $query = "SELECT subscriptionid,status FROM serial WHERE serialid=?";
1087 my $sth = $dbh->prepare($query);
1088 $sth->execute($serialid);
1089 my ( $subscriptionid, $oldstatus ) = $sth->fetchrow;
1091 # change status & update subscriptionhistory
1093 if ( $status eq 6 ) {
1094 DelIssue( { 'serialid' => $serialid, 'subscriptionid' => $subscriptionid, 'serialseq' => $serialseq } );
1096 my $query = "UPDATE serial SET serialseq=?,publisheddate=?,planneddate=?,status=?,notes=? WHERE serialid = ?";
1097 $sth = $dbh->prepare($query);
1098 $sth->execute( $serialseq, $publisheddate, $planneddate, $status, $notes, $serialid );
1099 $query = "SELECT * FROM subscription WHERE subscriptionid = ?";
1100 $sth = $dbh->prepare($query);
1101 $sth->execute($subscriptionid);
1102 my $val = $sth->fetchrow_hashref;
1103 unless ( $val->{manualhistory} ) {
1104 $query = "SELECT missinglist,recievedlist FROM subscriptionhistory WHERE subscriptionid=?";
1105 $sth = $dbh->prepare($query);
1106 $sth->execute($subscriptionid);
1107 my ( $missinglist, $recievedlist ) = $sth->fetchrow;
1108 if ( $status eq 2 ) {
1110 $recievedlist .= "; $serialseq"
1111 unless ( index( "$recievedlist", "$serialseq" ) >= 0 );
1114 # warn "missinglist : $missinglist serialseq :$serialseq, ".index("$missinglist","$serialseq");
1115 $missinglist .= "; $serialseq"
1117 and not index( "$missinglist", "$serialseq" ) >= 0 );
1118 $missinglist .= "; $serialseq"
1120 and index( "$missinglist", "$serialseq" ) >= 0 );
1121 $query = "UPDATE subscriptionhistory SET recievedlist=?, missinglist=? WHERE subscriptionid=?";
1122 $sth = $dbh->prepare($query);
1123 $recievedlist =~ s/^; //;
1124 $missinglist =~ s/^; //;
1125 $sth->execute( $recievedlist, $missinglist, $subscriptionid );
1129 # create new waited entry if needed (ie : was a "waited" and has changed)
1130 if ( $oldstatus eq 1 && $status ne 1 ) {
1131 my $query = "SELECT * FROM subscription WHERE subscriptionid = ?";
1132 $sth = $dbh->prepare($query);
1133 $sth->execute($subscriptionid);
1134 my $val = $sth->fetchrow_hashref;
1138 my ( $newserialseq, $newlastvalue1, $newlastvalue2, $newlastvalue3, $newinnerloop1, $newinnerloop2, $newinnerloop3 ) = GetNextSeq($val);
1140 # warn "Next Seq End";
1142 # next date (calculated from actual date & frequency parameters)
1143 # warn "publisheddate :$publisheddate ";
1144 my $nextpublisheddate = GetNextDate( $publisheddate, $val );
1145 NewIssue( $newserialseq, $subscriptionid, $val->{'biblionumber'}, 1, $nextpublisheddate, $nextpublisheddate );
1146 $query = "UPDATE subscription SET lastvalue1=?, lastvalue2=?, lastvalue3=?, innerloop1=?, innerloop2=?, innerloop3=?
1147 WHERE subscriptionid = ?";
1148 $sth = $dbh->prepare($query);
1149 $sth->execute( $newlastvalue1, $newlastvalue2, $newlastvalue3, $newinnerloop1, $newinnerloop2, $newinnerloop3, $subscriptionid );
1151 # check if an alert must be sent... (= a letter is defined & status became "arrived"
1152 if ( $val->{letter} && $status eq 2 && $oldstatus ne 2 ) {
1153 SendAlerts( 'issue', $val->{subscriptionid}, $val->{letter} );
1158 =head2 GetNextExpected
1162 $nextexpected = GetNextExpected($subscriptionid)
1164 Get the planneddate for the current expected issue of the subscription.
1170 planneddate => C4::Dates object
1177 sub GetNextExpected($) {
1178 my ($subscriptionid) = @_;
1179 my $dbh = C4::Context->dbh;
1180 my $sth = $dbh->prepare('SELECT serialid, planneddate FROM serial WHERE subscriptionid=? AND status=?');
1182 # Each subscription has only one 'expected' issue, with serial.status==1.
1183 $sth->execute( $subscriptionid, 1 );
1184 my ($nextissue) = $sth->fetchrow_hashref;
1185 if ( not $nextissue ) {
1186 $sth = $dbh->prepare('SELECT serialid,planneddate FROM serial WHERE subscriptionid = ? ORDER BY planneddate DESC LIMIT 1');
1187 $sth->execute($subscriptionid);
1188 $nextissue = $sth->fetchrow_hashref;
1190 $nextissue->{planneddate} = C4::Dates->new( $nextissue->{planneddate}, 'iso' );
1195 =head2 ModNextExpected
1199 ModNextExpected($subscriptionid,$date)
1201 Update the planneddate for the current expected issue of the subscription.
1202 This will modify all future prediction results.
1204 C<$date> is a C4::Dates object.
1210 sub ModNextExpected($$) {
1211 my ( $subscriptionid, $date ) = @_;
1212 my $dbh = C4::Context->dbh;
1214 #FIXME: Would expect to only set planneddate, but we set both on new issue creation, so updating it here
1215 my $sth = $dbh->prepare('UPDATE serial SET planneddate=?,publisheddate=? WHERE subscriptionid=? AND status=?');
1217 # Each subscription has only one 'expected' issue, with serial.status==1.
1218 $sth->execute( $date->output('iso'), $date->output('iso'), $subscriptionid, 1 );
1223 =head2 ModSubscription
1227 this function modify a subscription. Put all new values on input args.
1233 sub ModSubscription {
1234 my ($auser, $branchcode, $aqbooksellerid, $cost, $aqbudgetid, $startdate, $periodicity, $firstacquidate,
1235 $dow, $irregularity, $numberpattern, $numberlength, $weeklength, $monthlength, $add1, $every1,
1236 $whenmorethan1, $setto1, $lastvalue1, $innerloop1, $add2, $every2, $whenmorethan2, $setto2,
1237 $lastvalue2, $innerloop2, $add3, $every3, $whenmorethan3, $setto3, $lastvalue3, $innerloop3,
1238 $numberingmethod, $status, $biblionumber, $callnumber, $notes, $letter, $hemisphere, $manualhistory,
1239 $internalnotes, $serialsadditems, $staffdisplaycount, $opacdisplaycount, $graceperiod, $location, $enddate, $subscriptionid
1242 # warn $irregularity;
1243 my $dbh = C4::Context->dbh;
1244 my $query = "UPDATE subscription
1245 SET librarian=?, branchcode=?,aqbooksellerid=?,cost=?,aqbudgetid=?,startdate=?,
1246 periodicity=?,firstacquidate=?,dow=?,irregularity=?, numberpattern=?, numberlength=?,weeklength=?,monthlength=?,
1247 add1=?,every1=?,whenmorethan1=?,setto1=?,lastvalue1=?,innerloop1=?,
1248 add2=?,every2=?,whenmorethan2=?,setto2=?,lastvalue2=?,innerloop2=?,
1249 add3=?,every3=?,whenmorethan3=?,setto3=?,lastvalue3=?,innerloop3=?,
1250 numberingmethod=?, status=?, biblionumber=?, callnumber=?, notes=?,
1251 letter=?, hemisphere=?,manualhistory=?,internalnotes=?,serialsadditems=?,
1252 staffdisplaycount = ?,opacdisplaycount = ?, graceperiod = ?, location = ?
1254 WHERE subscriptionid = ?";
1256 #warn "query :".$query;
1257 my $sth = $dbh->prepare($query);
1259 $auser, $branchcode, $aqbooksellerid, $cost,
1260 $aqbudgetid, $startdate, $periodicity, $firstacquidate,
1261 $dow, "$irregularity", $numberpattern, $numberlength,
1262 $weeklength, $monthlength, $add1, $every1,
1263 $whenmorethan1, $setto1, $lastvalue1, $innerloop1,
1264 $add2, $every2, $whenmorethan2, $setto2,
1265 $lastvalue2, $innerloop2, $add3, $every3,
1266 $whenmorethan3, $setto3, $lastvalue3, $innerloop3,
1267 $numberingmethod, $status, $biblionumber, $callnumber,
1268 $notes, $letter, $hemisphere, ( $manualhistory ? $manualhistory : 0 ),
1269 $internalnotes, $serialsadditems, $staffdisplaycount, $opacdisplaycount,
1270 $graceperiod, $location, $enddate, $subscriptionid
1272 my $rows = $sth->rows;
1275 logaction( "SERIAL", "MODIFY", $subscriptionid, "" ) if C4::Context->preference("SubscriptionLog");
1279 =head2 NewSubscription
1283 $subscriptionid = &NewSubscription($auser,branchcode,$aqbooksellerid,$cost,$aqbudgetid,$biblionumber,
1284 $startdate,$periodicity,$dow,$numberlength,$weeklength,$monthlength,
1285 $add1,$every1,$whenmorethan1,$setto1,$lastvalue1,$innerloop1,
1286 $add2,$every2,$whenmorethan2,$setto2,$lastvalue2,$innerloop2,
1287 $add3,$every3,$whenmorethan3,$setto3,$lastvalue3,$innerloop3,
1288 $numberingmethod, $status, $notes, $serialsadditems,
1289 $staffdisplaycount, $opacdisplaycount, $graceperiod, $location, $enddate);
1291 Create a new subscription with value given on input args.
1294 the id of this new subscription
1300 sub NewSubscription {
1301 my ($auser, $branchcode, $aqbooksellerid, $cost, $aqbudgetid, $biblionumber, $startdate, $periodicity,
1302 $dow, $numberlength, $weeklength, $monthlength, $add1, $every1, $whenmorethan1, $setto1,
1303 $lastvalue1, $innerloop1, $add2, $every2, $whenmorethan2, $setto2, $lastvalue2, $innerloop2,
1304 $add3, $every3, $whenmorethan3, $setto3, $lastvalue3, $innerloop3, $numberingmethod, $status,
1305 $notes, $letter, $firstacquidate, $irregularity, $numberpattern, $callnumber, $hemisphere, $manualhistory,
1306 $internalnotes, $serialsadditems, $staffdisplaycount, $opacdisplaycount, $graceperiod, $location, $enddate
1308 my $dbh = C4::Context->dbh;
1310 #save subscription (insert into database)
1312 INSERT INTO subscription
1313 (librarian,branchcode,aqbooksellerid,cost,aqbudgetid,biblionumber,
1314 startdate,periodicity,dow,numberlength,weeklength,monthlength,
1315 add1,every1,whenmorethan1,setto1,lastvalue1,innerloop1,
1316 add2,every2,whenmorethan2,setto2,lastvalue2,innerloop2,
1317 add3,every3,whenmorethan3,setto3,lastvalue3,innerloop3,
1318 numberingmethod, status, notes, letter,firstacquidate,irregularity,
1319 numberpattern, callnumber, hemisphere,manualhistory,internalnotes,serialsadditems,
1320 staffdisplaycount,opacdisplaycount,graceperiod,location,enddate)
1321 VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
1323 my $sth = $dbh->prepare($query);
1325 $auser, $branchcode, $aqbooksellerid, $cost, $aqbudgetid, $biblionumber, $startdate, $periodicity,
1326 $dow, $numberlength, $weeklength, $monthlength, $add1, $every1, $whenmorethan1, $setto1,
1327 $lastvalue1, $innerloop1, $add2, $every2, $whenmorethan2, $setto2, $lastvalue2, $innerloop2,
1328 $add3, $every3, $whenmorethan3, $setto3, $lastvalue3, $innerloop3, $numberingmethod, "$status",
1329 $notes, $letter, $firstacquidate, $irregularity, $numberpattern, $callnumber, $hemisphere, $manualhistory,
1330 $internalnotes, $serialsadditems, $staffdisplaycount, $opacdisplaycount, $graceperiod, $location, $enddate
1333 #then create the 1st waited number
1334 my $subscriptionid = $dbh->{'mysql_insertid'};
1336 INSERT INTO subscriptionhistory
1337 (biblionumber, subscriptionid, histstartdate, opacnote, librariannote)
1340 $sth = $dbh->prepare($query);
1341 $sth->execute( $biblionumber, $subscriptionid, $startdate, $notes, $internalnotes );
1343 # reread subscription to get a hash (for calculation of the 1st issue number)
1347 WHERE subscriptionid = ?
1349 $sth = $dbh->prepare($query);
1350 $sth->execute($subscriptionid);
1351 my $val = $sth->fetchrow_hashref;
1353 # calculate issue number
1354 my $serialseq = GetSeq($val);
1357 (serialseq,subscriptionid,biblionumber,status, planneddate, publisheddate)
1358 VALUES (?,?,?,?,?,?)
1360 $sth = $dbh->prepare($query);
1361 $sth->execute( "$serialseq", $subscriptionid, $biblionumber, 1, $firstacquidate, $firstacquidate );
1363 logaction( "SERIAL", "ADD", $subscriptionid, "" ) if C4::Context->preference("SubscriptionLog");
1365 #set serial flag on biblio if not already set.
1366 my ( $null, ($bib) ) = GetBiblio($biblionumber);
1367 if ( !$bib->{'serial'} ) {
1368 my $record = GetMarcBiblio($biblionumber);
1369 my ( $tag, $subf ) = GetMarcFromKohaField( 'biblio.serial', $bib->{'frameworkcode'} );
1371 eval { $record->field($tag)->update( $subf => 1 ); };
1373 ModBiblio( $record, $biblionumber, $bib->{'frameworkcode'} );
1375 return $subscriptionid;
1378 =head2 ReNewSubscription
1382 ReNewSubscription($subscriptionid,$user,$startdate,$numberlength,$weeklength,$monthlength,$note)
1384 this function renew a subscription with values given on input args.
1390 sub ReNewSubscription {
1391 my ( $subscriptionid, $user, $startdate, $numberlength, $weeklength, $monthlength, $note ) = @_;
1392 my $dbh = C4::Context->dbh;
1393 my $subscription = GetSubscription($subscriptionid);
1397 LEFT JOIN biblioitems ON biblio.biblionumber=biblioitems.biblionumber
1398 WHERE biblio.biblionumber=?
1400 my $sth = $dbh->prepare($query);
1401 $sth->execute( $subscription->{biblionumber} );
1402 my $biblio = $sth->fetchrow_hashref;
1404 if ( C4::Context->preference("RenewSerialAddsSuggestion") ) {
1407 { 'suggestedby' => $user,
1408 'title' => $subscription->{bibliotitle},
1409 'author' => $biblio->{author},
1410 'publishercode' => $biblio->{publishercode},
1411 'note' => $biblio->{note},
1412 'biblionumber' => $subscription->{biblionumber}
1417 # renew subscription
1420 SET startdate=?,numberlength=?,weeklength=?,monthlength=?
1421 WHERE subscriptionid=?
1423 $sth = $dbh->prepare($query);
1424 $sth->execute( $startdate, $numberlength, $weeklength, $monthlength, $subscriptionid );
1425 my $enddate = GetExpirationDate($subscriptionid);
1426 $debug && warn "enddate :$enddate";
1430 WHERE subscriptionid=?
1432 $sth = $dbh->prepare($query);
1433 $sth->execute( $enddate, $subscriptionid );
1435 UPDATE subscriptionhistory
1437 WHERE subscriptionid=?
1439 $sth = $dbh->prepare($query);
1440 $sth->execute( $enddate, $subscriptionid );
1442 logaction( "SERIAL", "RENEW", $subscriptionid, "" ) if C4::Context->preference("SubscriptionLog");
1449 NewIssue($serialseq,$subscriptionid,$biblionumber,$status, $planneddate, $publisheddate, $notes)
1451 Create a new issue stored on the database.
1452 Note : we have to update the recievedlist and missinglist on subscriptionhistory for this subscription.
1459 my ( $serialseq, $subscriptionid, $biblionumber, $status, $planneddate, $publisheddate, $notes ) = @_;
1460 ### FIXME biblionumber CAN be provided by subscriptionid. So Do we STILL NEED IT ?
1462 my $dbh = C4::Context->dbh;
1465 (serialseq,subscriptionid,biblionumber,status,publisheddate,planneddate,notes)
1466 VALUES (?,?,?,?,?,?,?)
1468 my $sth = $dbh->prepare($query);
1469 $sth->execute( $serialseq, $subscriptionid, $biblionumber, $status, $publisheddate, $planneddate, $notes );
1470 my $serialid = $dbh->{'mysql_insertid'};
1472 SELECT missinglist,recievedlist
1473 FROM subscriptionhistory
1474 WHERE subscriptionid=?
1476 $sth = $dbh->prepare($query);
1477 $sth->execute($subscriptionid);
1478 my ( $missinglist, $recievedlist ) = $sth->fetchrow;
1480 if ( $status eq 2 ) {
1481 ### TODO Add a feature that improves recognition and description.
1482 ### As such count (serialseq) i.e. : N18,2(N19),N20
1483 ### Would use substr and index But be careful to previous presence of ()
1484 $recievedlist .= "; $serialseq" unless ( index( $recievedlist, $serialseq ) > 0 );
1486 if ( $status eq 4 ) {
1487 $missinglist .= "; $serialseq" unless ( index( $missinglist, $serialseq ) > 0 );
1490 UPDATE subscriptionhistory
1491 SET recievedlist=?, missinglist=?
1492 WHERE subscriptionid=?
1494 $sth = $dbh->prepare($query);
1495 $recievedlist =~ s/^; //;
1496 $missinglist =~ s/^; //;
1497 $sth->execute( $recievedlist, $missinglist, $subscriptionid );
1501 =head2 ItemizeSerials
1505 ItemizeSerials($serialid, $info);
1506 $info is a hashref containing barcode branch, itemcallnumber, status, location
1507 $serialid the serialid
1509 1 if the itemize is a succes.
1510 0 and @error else. @error containts the list of errors found.
1516 sub ItemizeSerials {
1517 my ( $serialid, $info ) = @_;
1518 my $now = POSIX::strftime( "%Y-%m-%d", localtime );
1520 my $dbh = C4::Context->dbh;
1526 my $sth = $dbh->prepare($query);
1527 $sth->execute($serialid);
1528 my $data = $sth->fetchrow_hashref;
1529 if ( C4::Context->preference("RoutingSerials") ) {
1531 # check for existing biblioitem relating to serial issue
1532 my ( $count, @results ) = GetBiblioItemByBiblioNumber( $data->{'biblionumber'} );
1534 for ( my $i = 0 ; $i < $count ; $i++ ) {
1535 if ( $results[$i]->{'volumeddesc'} eq $data->{'serialseq'} . ' (' . $data->{'planneddate'} . ')' ) {
1536 $bibitemno = $results[$i]->{'biblioitemnumber'};
1540 if ( $bibitemno == 0 ) {
1541 my $sth = $dbh->prepare( "SELECT * FROM biblioitems WHERE biblionumber = ? ORDER BY biblioitemnumber DESC" );
1542 $sth->execute( $data->{'biblionumber'} );
1543 my $biblioitem = $sth->fetchrow_hashref;
1544 $biblioitem->{'volumedate'} = $data->{planneddate};
1545 $biblioitem->{'volumeddesc'} = $data->{serialseq} . ' (' . format_date( $data->{'planneddate'} ) . ')';
1546 $biblioitem->{'dewey'} = $info->{itemcallnumber};
1550 my $fwk = GetFrameworkCode( $data->{'biblionumber'} );
1551 if ( $info->{barcode} ) {
1553 my $exists = itemdata( $info->{'barcode'} );
1554 push @errors, "barcode_not_unique" if ($exists);
1556 my $marcrecord = MARC::Record->new();
1557 my ( $tag, $subfield ) = GetMarcFromKohaField( "items.barcode", $fwk );
1558 my $newField = MARC::Field->new( "$tag", '', '', "$subfield" => $info->{barcode} );
1559 $marcrecord->insert_fields_ordered($newField);
1560 if ( $info->{branch} ) {
1561 my ( $tag, $subfield ) = GetMarcFromKohaField( "items.homebranch", $fwk );
1563 #warn "items.homebranch : $tag , $subfield";
1564 if ( $marcrecord->field($tag) ) {
1565 $marcrecord->field($tag)->add_subfields( "$subfield" => $info->{branch} );
1567 my $newField = MARC::Field->new( "$tag", '', '', "$subfield" => $info->{branch} );
1568 $marcrecord->insert_fields_ordered($newField);
1570 ( $tag, $subfield ) = GetMarcFromKohaField( "items.holdingbranch", $fwk );
1572 #warn "items.holdingbranch : $tag , $subfield";
1573 if ( $marcrecord->field($tag) ) {
1574 $marcrecord->field($tag)->add_subfields( "$subfield" => $info->{branch} );
1576 my $newField = MARC::Field->new( "$tag", '', '', "$subfield" => $info->{branch} );
1577 $marcrecord->insert_fields_ordered($newField);
1580 if ( $info->{itemcallnumber} ) {
1581 my ( $tag, $subfield ) = GetMarcFromKohaField( "items.itemcallnumber", $fwk );
1583 if ( $marcrecord->field($tag) ) {
1584 $marcrecord->field($tag)->add_subfields( "$subfield" => $info->{itemcallnumber} );
1586 my $newField = MARC::Field->new( "$tag", '', '', "$subfield" => $info->{itemcallnumber} );
1587 $marcrecord->insert_fields_ordered($newField);
1590 if ( $info->{notes} ) {
1591 my ( $tag, $subfield ) = GetMarcFromKohaField( "items.itemnotes", $fwk );
1593 if ( $marcrecord->field($tag) ) {
1594 $marcrecord->field($tag)->add_subfields( "$subfield" => $info->{notes} );
1596 my $newField = MARC::Field->new( "$tag", '', '', "$subfield" => $info->{notes} );
1597 $marcrecord->insert_fields_ordered($newField);
1600 if ( $info->{location} ) {
1601 my ( $tag, $subfield ) = GetMarcFromKohaField( "items.location", $fwk );
1603 if ( $marcrecord->field($tag) ) {
1604 $marcrecord->field($tag)->add_subfields( "$subfield" => $info->{location} );
1606 my $newField = MARC::Field->new( "$tag", '', '', "$subfield" => $info->{location} );
1607 $marcrecord->insert_fields_ordered($newField);
1610 if ( $info->{status} ) {
1611 my ( $tag, $subfield ) = GetMarcFromKohaField( "items.notforloan", $fwk );
1613 if ( $marcrecord->field($tag) ) {
1614 $marcrecord->field($tag)->add_subfields( "$subfield" => $info->{status} );
1616 my $newField = MARC::Field->new( "$tag", '', '', "$subfield" => $info->{status} );
1617 $marcrecord->insert_fields_ordered($newField);
1620 if ( C4::Context->preference("RoutingSerials") ) {
1621 my ( $tag, $subfield ) = GetMarcFromKohaField( "items.dateaccessioned", $fwk );
1622 if ( $marcrecord->field($tag) ) {
1623 $marcrecord->field($tag)->add_subfields( "$subfield" => $now );
1625 my $newField = MARC::Field->new( "$tag", '', '', "$subfield" => $now );
1626 $marcrecord->insert_fields_ordered($newField);
1629 AddItemFromMarc( $marcrecord, $data->{'biblionumber'} );
1632 return ( 0, @errors );
1636 =head2 HasSubscriptionStrictlyExpired
1640 1 or 0 = HasSubscriptionStrictlyExpired($subscriptionid)
1642 the subscription has stricly expired when today > the end subscription date
1645 1 if true, 0 if false, -1 if the expiration date is not set.
1651 sub HasSubscriptionStrictlyExpired {
1653 # Getting end of subscription date
1654 my ($subscriptionid) = @_;
1655 my $dbh = C4::Context->dbh;
1656 my $subscription = GetSubscription($subscriptionid);
1657 my $expirationdate = $subscription->{enddate} || GetExpirationDate($subscriptionid);
1659 # If the expiration date is set
1660 if ( $expirationdate != 0 ) {
1661 my ( $endyear, $endmonth, $endday ) = split( '-', $expirationdate );
1663 # Getting today's date
1664 my ( $nowyear, $nowmonth, $nowday ) = Today();
1666 # if today's date > expiration date, then the subscription has stricly expired
1667 if ( Delta_Days( $nowyear, $nowmonth, $nowday, $endyear, $endmonth, $endday ) < 0 ) {
1674 # There are some cases where the expiration date is not set
1675 # As we can't determine if the subscription has expired on a date-basis,
1681 =head2 HasSubscriptionExpired
1685 $has_expired = HasSubscriptionExpired($subscriptionid)
1687 the subscription has expired when the next issue to arrive is out of subscription limit.
1690 0 if the subscription has not expired
1691 1 if the subscription has expired
1692 2 if has subscription does not have a valid expiration date set
1698 sub HasSubscriptionExpired {
1699 my ($subscriptionid) = @_;
1700 my $dbh = C4::Context->dbh;
1701 my $subscription = GetSubscription($subscriptionid);
1702 if ( ( $subscription->{periodicity} % 16 ) > 0 ) {
1703 my $expirationdate = $subscription->{enddate};
1705 SELECT max(planneddate)
1707 WHERE subscriptionid=?
1709 my $sth = $dbh->prepare($query);
1710 $sth->execute($subscriptionid);
1711 my ($res) = $sth->fetchrow;
1712 return 0 unless $res;
1713 my @res = split( /-/, $res );
1714 my @endofsubscriptiondate = split( /-/, $expirationdate );
1715 return 2 if ( scalar(@res) != 3 || scalar(@endofsubscriptiondate) != 3 || not check_date(@res) || not check_date(@endofsubscriptiondate) );
1717 if ( ( @endofsubscriptiondate && Delta_Days( $res[0], $res[1], $res[2], $endofsubscriptiondate[0], $endofsubscriptiondate[1], $endofsubscriptiondate[2] ) <= 0 )
1721 if ( $subscription->{'numberlength'} ) {
1722 my $countreceived = countissuesfrom( $subscriptionid, $subscription->{'startdate'} );
1723 return 1 if ( $countreceived > $subscription->{'numberlength'} );
1729 return 0; # Notice that you'll never get here.
1732 =head2 SetDistributedto
1736 SetDistributedto($distributedto,$subscriptionid);
1737 This function update the value of distributedto for a subscription given on input arg.
1743 sub SetDistributedto {
1744 my ( $distributedto, $subscriptionid ) = @_;
1745 my $dbh = C4::Context->dbh;
1749 WHERE subscriptionid=?
1751 my $sth = $dbh->prepare($query);
1752 $sth->execute( $distributedto, $subscriptionid );
1755 =head2 DelSubscription
1759 DelSubscription($subscriptionid)
1760 this function delete the subscription which has $subscriptionid as id.
1766 sub DelSubscription {
1767 my ($subscriptionid) = @_;
1768 my $dbh = C4::Context->dbh;
1769 $subscriptionid = $dbh->quote($subscriptionid);
1770 $dbh->do("DELETE FROM subscription WHERE subscriptionid=$subscriptionid");
1771 $dbh->do("DELETE FROM subscriptionhistory WHERE subscriptionid=$subscriptionid");
1772 $dbh->do("DELETE FROM serial WHERE subscriptionid=$subscriptionid");
1774 logaction( "SERIAL", "DELETE", $subscriptionid, "" ) if C4::Context->preference("SubscriptionLog");
1781 DelIssue($serialseq,$subscriptionid)
1782 this function delete an issue which has $serialseq and $subscriptionid given on input arg.
1789 my ($dataissue) = @_;
1790 my $dbh = C4::Context->dbh;
1791 ### TODO Add itemdeletion. Would need to get itemnumbers. Should be in a pref ?
1796 AND subscriptionid= ?
1798 my $mainsth = $dbh->prepare($query);
1799 $mainsth->execute( $dataissue->{'serialid'}, $dataissue->{'subscriptionid'} );
1801 #Delete element from subscription history
1802 $query = "SELECT * FROM subscription WHERE subscriptionid = ?";
1803 my $sth = $dbh->prepare($query);
1804 $sth->execute( $dataissue->{'subscriptionid'} );
1805 my $val = $sth->fetchrow_hashref;
1806 unless ( $val->{manualhistory} ) {
1808 SELECT * FROM subscriptionhistory
1809 WHERE subscriptionid= ?
1811 my $sth = $dbh->prepare($query);
1812 $sth->execute( $dataissue->{'subscriptionid'} );
1813 my $data = $sth->fetchrow_hashref;
1814 my $serialseq = $dataissue->{'serialseq'};
1815 $data->{'missinglist'} =~ s/\b$serialseq\b//;
1816 $data->{'recievedlist'} =~ s/\b$serialseq\b//;
1817 my $strsth = "UPDATE subscriptionhistory SET " . join( ",", map { join( "=", $_, $dbh->quote( $data->{$_} ) ) } keys %$data ) . " WHERE subscriptionid=?";
1818 $sth = $dbh->prepare($strsth);
1819 $sth->execute( $dataissue->{'subscriptionid'} );
1822 return $mainsth->rows;
1825 =head2 GetLateOrMissingIssues
1829 @issuelist = &GetLateMissingIssues($supplierid,$serialid)
1831 this function select missing issues on database - where serial.status = 4 or serial.status=3 or planneddate<now
1834 the issuelist into a table. Each line of this table containts a ref to a hash which it containts
1835 name,title,planneddate,serialseq,serial.subscriptionid from tables : subscription, serial & biblio
1841 sub GetLateOrMissingIssues {
1842 my ( $supplierid, $serialid, $order ) = @_;
1843 my $dbh = C4::Context->dbh;
1847 $byserial = "and serialid = " . $serialid;
1850 $order .= ", title";
1855 $sth = $dbh->prepare(
1857 serialid, aqbooksellerid, name,
1858 biblio.title, planneddate, serialseq,
1859 serial.status, serial.subscriptionid, claimdate
1861 LEFT JOIN subscription ON serial.subscriptionid=subscription.subscriptionid
1862 LEFT JOIN biblio ON subscription.biblionumber=biblio.biblionumber
1863 LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id
1864 WHERE subscription.subscriptionid = serial.subscriptionid
1865 AND (serial.STATUS = 4 OR ((planneddate < now() AND serial.STATUS =1) OR serial.STATUS = 3 OR serial.STATUS = 7))
1866 AND subscription.aqbooksellerid=$supplierid
1871 $sth = $dbh->prepare(
1873 serialid, aqbooksellerid, name,
1874 biblio.title, planneddate, serialseq,
1875 serial.status, serial.subscriptionid, claimdate
1877 LEFT JOIN subscription ON serial.subscriptionid=subscription.subscriptionid
1878 LEFT JOIN biblio ON subscription.biblionumber=biblio.biblionumber
1879 LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id
1880 WHERE subscription.subscriptionid = serial.subscriptionid
1881 AND (serial.STATUS = 4 OR ((planneddate < now() AND serial.STATUS =1) OR serial.STATUS = 3 OR serial.STATUS = 7))
1888 while ( my $line = $sth->fetchrow_hashref ) {
1889 if ($line->{planneddate}) {
1890 $line->{planneddate} = format_date( $line->{planneddate} );
1892 if ($line->{claimdate}) {
1893 $line->{claimdate} = format_date( $line->{claimdate} );
1895 $line->{"status".$line->{status}} = 1;
1896 push @issuelist, $line;
1901 =head2 removeMissingIssue
1905 removeMissingIssue($subscriptionid)
1907 this function removes an issue from being part of the missing string in
1908 subscriptionlist.missinglist column
1910 called when a missing issue is found from the serials-recieve.pl file
1916 sub removeMissingIssue {
1917 my ( $sequence, $subscriptionid ) = @_;
1918 my $dbh = C4::Context->dbh;
1919 my $sth = $dbh->prepare("SELECT * FROM subscriptionhistory WHERE subscriptionid = ?");
1920 $sth->execute($subscriptionid);
1921 my $data = $sth->fetchrow_hashref;
1922 my $missinglist = $data->{'missinglist'};
1923 my $missinglistbefore = $missinglist;
1925 # warn $missinglist." before";
1926 $missinglist =~ s/($sequence)//;
1928 # warn $missinglist." after";
1929 if ( $missinglist ne $missinglistbefore ) {
1930 $missinglist =~ s/\|\s\|/\|/g;
1931 $missinglist =~ s/^\| //g;
1932 $missinglist =~ s/\|$//g;
1933 my $sth2 = $dbh->prepare(
1934 "UPDATE subscriptionhistory
1936 WHERE subscriptionid = ?"
1938 $sth2->execute( $missinglist, $subscriptionid );
1946 &updateClaim($serialid)
1948 this function updates the time when a claim is issued for late/missing items
1950 called from claims.pl file
1957 my ($serialid) = @_;
1958 my $dbh = C4::Context->dbh;
1959 my $sth = $dbh->prepare(
1960 "UPDATE serial SET claimdate = now()
1964 $sth->execute($serialid);
1967 =head2 getsupplierbyserialid
1971 ($result) = &getsupplierbyserialid($serialid)
1973 this function is used to find the supplier id given a serial id
1976 hashref containing serialid, subscriptionid, and aqbooksellerid
1982 sub getsupplierbyserialid {
1983 my ($serialid) = @_;
1984 my $dbh = C4::Context->dbh;
1985 my $sth = $dbh->prepare(
1986 "SELECT serialid, serial.subscriptionid, aqbooksellerid
1988 LEFT JOIN subscription ON serial.subscriptionid = subscription.subscriptionid
1992 $sth->execute($serialid);
1993 my $line = $sth->fetchrow_hashref;
1994 my $result = $line->{'aqbooksellerid'};
1998 =head2 check_routing
2002 ($result) = &check_routing($subscriptionid)
2004 this function checks to see if a serial has a routing list and returns the count of routingid
2005 used to show either an 'add' or 'edit' link
2012 my ($subscriptionid) = @_;
2013 my $dbh = C4::Context->dbh;
2014 my $sth = $dbh->prepare(
2015 "SELECT count(routingid) routingids FROM subscription LEFT JOIN subscriptionroutinglist
2016 ON subscription.subscriptionid = subscriptionroutinglist.subscriptionid
2017 WHERE subscription.subscriptionid = ? ORDER BY ranking ASC
2020 $sth->execute($subscriptionid);
2021 my $line = $sth->fetchrow_hashref;
2022 my $result = $line->{'routingids'};
2026 =head2 addroutingmember
2030 &addroutingmember($borrowernumber,$subscriptionid)
2032 this function takes a borrowernumber and subscriptionid and add the member to the
2033 routing list for that serial subscription and gives them a rank on the list
2034 of either 1 or highest current rank + 1
2040 sub addroutingmember {
2041 my ( $borrowernumber, $subscriptionid ) = @_;
2043 my $dbh = C4::Context->dbh;
2044 my $sth = $dbh->prepare( "SELECT max(ranking) rank FROM subscriptionroutinglist WHERE subscriptionid = ?" );
2045 $sth->execute($subscriptionid);
2046 while ( my $line = $sth->fetchrow_hashref ) {
2047 if ( $line->{'rank'} > 0 ) {
2048 $rank = $line->{'rank'} + 1;
2053 $sth = $dbh->prepare( "INSERT INTO subscriptionroutinglist (subscriptionid,borrowernumber,ranking) VALUES (?,?,?)" );
2054 $sth->execute( $subscriptionid, $borrowernumber, $rank );
2057 =head2 reorder_members
2061 &reorder_members($subscriptionid,$routingid,$rank)
2063 this function is used to reorder the routing list
2065 it takes the routingid of the member one wants to re-rank and the rank it is to move to
2066 - it gets all members on list puts their routingid's into an array
2067 - removes the one in the array that is $routingid
2068 - then reinjects $routingid at point indicated by $rank
2069 - then update the database with the routingids in the new order
2075 sub reorder_members {
2076 my ( $subscriptionid, $routingid, $rank ) = @_;
2077 my $dbh = C4::Context->dbh;
2078 my $sth = $dbh->prepare( "SELECT * FROM subscriptionroutinglist WHERE subscriptionid = ? ORDER BY ranking ASC" );
2079 $sth->execute($subscriptionid);
2081 while ( my $line = $sth->fetchrow_hashref ) {
2082 push( @result, $line->{'routingid'} );
2085 # To find the matching index
2087 my $key = -1; # to allow for 0 being a valid response
2088 for ( $i = 0 ; $i < @result ; $i++ ) {
2089 if ( $routingid == $result[$i] ) {
2090 $key = $i; # save the index
2095 # if index exists in array then move it to new position
2096 if ( $key > -1 && $rank > 0 ) {
2097 my $new_rank = $rank - 1; # $new_rank is what you want the new index to be in the array
2098 my $moving_item = splice( @result, $key, 1 );
2099 splice( @result, $new_rank, 0, $moving_item );
2101 for ( my $j = 0 ; $j < @result ; $j++ ) {
2102 my $sth = $dbh->prepare( "UPDATE subscriptionroutinglist SET ranking = '" . ( $j + 1 ) . "' WHERE routingid = '" . $result[$j] . "'" );
2107 =head2 delroutingmember
2111 &delroutingmember($routingid,$subscriptionid)
2113 this function either deletes one member from routing list if $routingid exists otherwise
2114 deletes all members from the routing list
2120 sub delroutingmember {
2122 # if $routingid exists then deletes that row otherwise deletes all with $subscriptionid
2123 my ( $routingid, $subscriptionid ) = @_;
2124 my $dbh = C4::Context->dbh;
2126 my $sth = $dbh->prepare("DELETE FROM subscriptionroutinglist WHERE routingid = ?");
2127 $sth->execute($routingid);
2128 reorder_members( $subscriptionid, $routingid );
2130 my $sth = $dbh->prepare("DELETE FROM subscriptionroutinglist WHERE subscriptionid = ?");
2131 $sth->execute($subscriptionid);
2135 =head2 getroutinglist
2139 ($count,@routinglist) = &getroutinglist($subscriptionid)
2141 this gets the info from the subscriptionroutinglist for $subscriptionid
2144 a count of the number of members on routinglist
2145 the routinglist into a table. Each line of this table containts a ref to a hash which containts
2146 routingid - a unique id, borrowernumber, ranking, and biblionumber of subscription
2152 sub getroutinglist {
2153 my ($subscriptionid) = @_;
2154 my $dbh = C4::Context->dbh;
2155 my $sth = $dbh->prepare(
2156 "SELECT routingid, borrowernumber, ranking, biblionumber
2158 LEFT JOIN subscriptionroutinglist ON subscription.subscriptionid = subscriptionroutinglist.subscriptionid
2159 WHERE subscription.subscriptionid = ? ORDER BY ranking ASC
2162 $sth->execute($subscriptionid);
2165 while ( my $line = $sth->fetchrow_hashref ) {
2167 push( @routinglist, $line );
2169 return ( $count, @routinglist );
2172 =head2 countissuesfrom
2176 $result = &countissuesfrom($subscriptionid,$startdate)
2183 sub countissuesfrom {
2184 my ( $subscriptionid, $startdate ) = @_;
2185 my $dbh = C4::Context->dbh;
2189 WHERE subscriptionid=?
2190 AND serial.publisheddate>?
2192 my $sth = $dbh->prepare($query);
2193 $sth->execute( $subscriptionid, $startdate );
2194 my ($countreceived) = $sth->fetchrow;
2195 return $countreceived;
2202 $result = &CountIssues($subscriptionid)
2210 my ($subscriptionid) = @_;
2211 my $dbh = C4::Context->dbh;
2215 WHERE subscriptionid=?
2217 my $sth = $dbh->prepare($query);
2218 $sth->execute($subscriptionid);
2219 my ($countreceived) = $sth->fetchrow;
2220 return $countreceived;
2227 $result = &HasItems($subscriptionid)
2235 my ($subscriptionid) = @_;
2236 my $dbh = C4::Context->dbh;
2238 SELECT COUNT(serialitems.itemnumber)
2240 LEFT JOIN serialitems USING(serialid)
2241 WHERE subscriptionid=? AND serialitems.serialid NOT NULL
2243 my $sth=$dbh->prepare($query);
2244 $sth->execute($subscriptionid);
2245 my ($countitems)=$sth->fetchrow;
2249 =head2 abouttoexpire
2253 $result = &abouttoexpire($subscriptionid)
2255 this function alerts you to the penultimate issue for a serial subscription
2257 returns 1 - if this is the penultimate issue
2265 my ($subscriptionid) = @_;
2266 my $dbh = C4::Context->dbh;
2267 my $subscription = GetSubscription($subscriptionid);
2268 my $per = $subscription->{'periodicity'};
2269 if ( $per % 16 > 0 ) {
2270 my $expirationdate = $subscription->{enddate};
2271 my $sth = $dbh->prepare("select max(planneddate) from serial where subscriptionid=?");
2272 $sth->execute($subscriptionid);
2273 my ($res) = $sth->fetchrow;
2274 my @res = split( /-/, $res );
2275 @res = Date::Calc::Today if ( $res[0] * $res[1] == 0 );
2276 my @endofsubscriptiondate = split( /-/, $expirationdate );
2278 if ( $per == 1 ) { $x = 7; }
2279 if ( $per == 2 ) { $x = 7; }
2280 if ( $per == 3 ) { $x = 14; }
2281 if ( $per == 4 ) { $x = 21; }
2282 if ( $per == 5 ) { $x = 31; }
2283 if ( $per == 6 ) { $x = 62; }
2284 if ( $per == 7 || $per == 8 ) { $x = 93; }
2285 if ( $per == 9 ) { $x = 190; }
2286 if ( $per == 10 ) { $x = 365; }
2287 if ( $per == 11 ) { $x = 730; }
2288 my @datebeforeend = Add_Delta_Days( $endofsubscriptiondate[0], $endofsubscriptiondate[1], $endofsubscriptiondate[2], -( 3 * $x ) )
2289 if ( @endofsubscriptiondate && $endofsubscriptiondate[0] * $endofsubscriptiondate[1] * $endofsubscriptiondate[2] );
2291 # warn "DATE BEFORE END: $datebeforeend";
2296 && Delta_Days( $res[0], $res[1], $res[2], $datebeforeend[0], $datebeforeend[1], $datebeforeend[2] ) <= 0 )
2297 && ( @endofsubscriptiondate
2298 && Delta_Days( $res[0], $res[1], $res[2], $endofsubscriptiondate[0], $endofsubscriptiondate[1], $endofsubscriptiondate[2] ) >= 0 )
2301 } elsif ( $subscription->{numberlength} > 0 ) {
2302 return ( countissuesfrom( $subscriptionid, $subscription->{'startdate'} ) >= $subscription->{numberlength} - 1 );
2310 ($resultdate) = &GetNextDate($planneddate,$subscription)
2312 this function is an extension of GetNextDate which allows for checking for irregularity
2314 it takes the planneddate and will return the next issue's date and will skip dates if there
2315 exists an irregularity
2316 - eg if periodicity is monthly and $planneddate is 2007-02-10 but if March and April is to be
2317 skipped then the returned date will be 2007-05-10
2320 $resultdate - then next date in the sequence
2322 Return 0 if periodicity==0
2326 sub in_array { # used in next sub down
2327 my ( $val, @elements ) = @_;
2328 foreach my $elem (@elements) {
2329 if ( $val == $elem ) {
2336 sub GetNextDate(@) {
2337 my ( $planneddate, $subscription ) = @_;
2338 my @irreg = split( /\,/, $subscription->{irregularity} );
2340 #date supposed to be in ISO.
2342 my ( $year, $month, $day ) = split( /-/, $planneddate );
2343 $month = 1 unless ($month);
2344 $day = 1 unless ($day);
2347 # warn "DOW $dayofweek";
2348 if ( $subscription->{periodicity} % 16 == 0 ) { # 'without regularity' || 'irregular'
2353 # Since we're interpreting irregularity here as which days of the week to skip an issue,
2354 # renaming this pattern from 1/day to " n / week ".
2355 if ( $subscription->{periodicity} == 1 ) {
2356 my $dayofweek = eval { Day_of_Week( $year, $month, $day ) };
2357 if ($@) { warn "year month day : $year $month $day $subscription->{subscriptionid} : $@"; }
2359 for ( my $i = 0 ; $i < @irreg ; $i++ ) {
2360 $dayofweek = 0 if ( $dayofweek == 7 );
2361 if ( in_array( ( $dayofweek + 1 ), @irreg ) ) {
2362 ( $year, $month, $day ) = Add_Delta_Days( $year, $month, $day, 1 );
2366 @resultdate = Add_Delta_Days( $year, $month, $day, 1 );
2371 if ( $subscription->{periodicity} == 2 ) {
2372 my ( $wkno, $year ) = eval { Week_of_Year( $year, $month, $day ) };
2373 if ($@) { warn "year month day : $year $month $day $subscription->{subscriptionid} : $@"; }
2375 for ( my $i = 0 ; $i < @irreg ; $i++ ) {
2377 #FIXME: if two consecutive irreg, do we only skip one?
2378 if ( $irreg[$i] == ( ( $wkno != 51 ) ? ( $wkno + 1 ) % 52 : 52 ) ) {
2379 ( $year, $month, $day ) = Add_Delta_Days( $year, $month, $day, 7 );
2380 $wkno = ( ( $wkno != 51 ) ? ( $wkno + 1 ) % 52 : 52 );
2383 @resultdate = Add_Delta_Days( $year, $month, $day, 7 );
2388 if ( $subscription->{periodicity} == 3 ) {
2389 my ( $wkno, $year ) = eval { Week_of_Year( $year, $month, $day ) };
2390 if ($@) { warn "year month day : $year $month $day $subscription->{subscriptionid} : $@"; }
2392 for ( my $i = 0 ; $i < @irreg ; $i++ ) {
2393 if ( $irreg[$i] == ( ( $wkno != 50 ) ? ( $wkno + 2 ) % 52 : 52 ) ) {
2394 ### BUGFIX was previously +1 ^
2395 ( $year, $month, $day ) = Add_Delta_Days( $year, $month, $day, 14 );
2396 $wkno = ( ( $wkno != 50 ) ? ( $wkno + 2 ) % 52 : 52 );
2399 @resultdate = Add_Delta_Days( $year, $month, $day, 14 );
2404 if ( $subscription->{periodicity} == 4 ) {
2405 my ( $wkno, $year ) = eval { Week_of_Year( $year, $month, $day ) };
2406 if ($@) { warn "année mois jour : $year $month $day $subscription->{subscriptionid} : $@"; }
2408 for ( my $i = 0 ; $i < @irreg ; $i++ ) {
2409 if ( $irreg[$i] == ( ( $wkno != 49 ) ? ( $wkno + 3 ) % 52 : 52 ) ) {
2410 ( $year, $month, $day ) = Add_Delta_Days( $year, $month, $day, 21 );
2411 $wkno = ( ( $wkno != 49 ) ? ( $wkno + 3 ) % 52 : 52 );
2414 @resultdate = Add_Delta_Days( $year, $month, $day, 21 );
2417 my $tmpmonth = $month;
2418 if ( $year && $month && $day ) {
2419 if ( $subscription->{periodicity} == 5 ) {
2420 for ( my $i = 0 ; $i < @irreg ; $i++ ) {
2421 if ( $irreg[$i] == ( ( $tmpmonth != 11 ) ? ( $tmpmonth + 1 ) % 12 : 12 ) ) {
2422 ( $year, $month, $day ) = Add_Delta_YMD( $year, $month, $day, 0, 1, 0 );
2423 $tmpmonth = ( ( $tmpmonth != 11 ) ? ( $tmpmonth + 1 ) % 12 : 12 );
2426 @resultdate = Add_Delta_YMD( $year, $month, $day, 0, 1, 0 );
2428 if ( $subscription->{periodicity} == 6 ) {
2429 for ( my $i = 0 ; $i < @irreg ; $i++ ) {
2430 if ( $irreg[$i] == ( ( $tmpmonth != 10 ) ? ( $tmpmonth + 2 ) % 12 : 12 ) ) {
2431 ( $year, $month, $day ) = Add_Delta_YMD( $year, $month, $day, 0, 2, 0 );
2432 $tmpmonth = ( ( $tmpmonth != 10 ) ? ( $tmpmonth + 2 ) % 12 : 12 );
2435 @resultdate = Add_Delta_YMD( $year, $month, $day, 0, 2, 0 );
2437 if ( $subscription->{periodicity} == 7 ) {
2438 for ( my $i = 0 ; $i < @irreg ; $i++ ) {
2439 if ( $irreg[$i] == ( ( $tmpmonth != 9 ) ? ( $tmpmonth + 3 ) % 12 : 12 ) ) {
2440 ( $year, $month, $day ) = Add_Delta_YMD( $year, $month, $day, 0, 3, 0 );
2441 $tmpmonth = ( ( $tmpmonth != 9 ) ? ( $tmpmonth + 3 ) % 12 : 12 );
2444 @resultdate = Add_Delta_YMD( $year, $month, $day, 0, 3, 0 );
2446 if ( $subscription->{periodicity} == 8 ) {
2447 for ( my $i = 0 ; $i < @irreg ; $i++ ) {
2448 if ( $irreg[$i] == ( ( $tmpmonth != 9 ) ? ( $tmpmonth + 3 ) % 12 : 12 ) ) {
2449 ( $year, $month, $day ) = Add_Delta_YMD( $year, $month, $day, 0, 3, 0 );
2450 $tmpmonth = ( ( $tmpmonth != 9 ) ? ( $tmpmonth + 3 ) % 12 : 12 );
2453 @resultdate = Add_Delta_YMD( $year, $month, $day, 0, 3, 0 );
2455 if ( $subscription->{periodicity} == 9 ) {
2456 for ( my $i = 0 ; $i < @irreg ; $i++ ) {
2457 if ( $irreg[$i] == ( ( $tmpmonth != 9 ) ? ( $tmpmonth + 3 ) % 12 : 12 ) ) {
2458 ### BUFIX Seems to need more Than One ?
2459 ( $year, $month, $day ) = Add_Delta_YM( $year, $month, $day, 0, 6 );
2460 $tmpmonth = ( ( $tmpmonth != 6 ) ? ( $tmpmonth + 6 ) % 12 : 12 );
2463 @resultdate = Add_Delta_YM( $year, $month, $day, 0, 6 );
2465 if ( $subscription->{periodicity} == 10 ) {
2466 @resultdate = Add_Delta_YM( $year, $month, $day, 1, 0 );
2468 if ( $subscription->{periodicity} == 11 ) {
2469 @resultdate = Add_Delta_YM( $year, $month, $day, 2, 0 );
2472 my $resultdate = sprintf( "%04d-%02d-%02d", $resultdate[0], $resultdate[1], $resultdate[2] );
2474 return "$resultdate";
2479 $item = &itemdata($barcode);
2481 Looks up the item with the given barcode, and returns a
2482 reference-to-hash containing information about that item. The keys of
2483 the hash are the fields from the C<items> and C<biblioitems> tables in
2491 my $dbh = C4::Context->dbh;
2492 my $sth = $dbh->prepare(
2493 "Select * from items LEFT JOIN biblioitems ON items.biblioitemnumber=biblioitems.biblioitemnumber
2496 $sth->execute($barcode);
2497 my $data = $sth->fetchrow_hashref;
2507 Koha Developement team <info@koha.org>