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 with
17 # Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
18 # Suite 330, Boston, MA 02111-1307 USA
30 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
32 # set the version for version checking
33 $VERSION = do { my @v = '$Revision$' =~ /\d+/g;
34 shift(@v) . "." . join("_", map {sprintf "%03d", $_ } @v); };
39 C4::Serials - Give functions for serializing.
47 Give all XYZ functions
54 &NewSubscription &ModSubscription &DelSubscription &GetSubscriptions &GetSubscription
55 &CountSubscriptionFromBiblionumber &GetSubscriptionsFromBiblionumber
56 &GetFullSubscriptionsFromBiblionumber &GetNextSeq
57 &ModSubscriptionHistory &NewIssue
58 &GetSerials &GetLatestSerials &ModSerialStatus
59 &HasSubscriptionExpired &GetSubscriptionExpirationDate &ReNewSubscription
60 &GetSuppliersWithLateIssues &GetLateIssues &GetMissingIssues
61 &GetDistributedTo &SetDistributedto
62 &getroutinglist &delroutingmember &addroutingmember &reorder_members
63 &check_routing &getsupplierbyserialid &updateClaim &removeMissingIssue &abouttoexpire
67 =head2 GetSuppliersWithLateIssues
71 %supplierlist = &GetSuppliersWithLateIssues
73 this function get all suppliers with late issues.
76 the supplierlist into a hash. this hash containts id & name of the supplier
81 sub GetSuppliersWithLateIssues {
82 my $dbh = C4::Context->dbh;
84 SELECT DISTINCT id, name
85 FROM subscription, serial
86 LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id
87 WHERE subscription.subscriptionid = serial.subscriptionid
88 AND (planneddate < now() OR serial.STATUS = 3 OR serial.STATUS = 4)
90 my $sth = $dbh->prepare($query);
93 while (my ($id,$name) = $sth->fetchrow) {
94 $supplierlist{$id} = $name;
96 if(C4::Context->preference("RoutingSerials")){
97 $supplierlist{''} = "All Suppliers";
106 @issuelist = &GetLateIssues($supplierid)
108 this function select late issues on database
111 the issuelist into an table. Each line of this table containts a ref to a hash which it containts
112 name,title,planneddate,serialseq,serial.subscriptionid from tables : subscription, serial & biblio
118 my ($supplierid) = shift;
119 my $dbh = C4::Context->dbh;
123 SELECT name,title,planneddate,serialseq,serial.subscriptionid
124 FROM subscription, serial, biblio
125 LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id
126 WHERE subscription.subscriptionid = serial.subscriptionid
127 AND ((planneddate < now() AND serial.STATUS =1) OR serial.STATUS = 3)
128 AND subscription.aqbooksellerid=$supplierid
129 AND biblio.biblionumber = subscription.biblionumber
132 $sth = $dbh->prepare($query);
135 SELECT name,title,planneddate,serialseq,serial.subscriptionid
136 FROM subscription, serial, biblio
137 LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id
138 WHERE subscription.subscriptionid = serial.subscriptionid
139 AND ((planneddate < now() AND serial.STATUS =1) OR serial.STATUS = 3)
140 AND biblio.biblionumber = subscription.biblionumber
143 $sth = $dbh->prepare($query);
150 while (my $line = $sth->fetchrow_hashref) {
151 $odd++ unless $line->{title} eq $last_title;
152 $line->{title} = "" if $line->{title} eq $last_title;
153 $last_title = $line->{title} if ($line->{title});
154 $line->{planneddate} = format_date($line->{planneddate});
155 $line->{'odd'} = 1 if $odd %2 ;
157 push @issuelist,$line;
159 return $count,@issuelist;
162 =head2 GetSubscriptionHistoryFromSubscriptionId
166 $sth = GetSubscriptionHistoryFromSubscriptionId()
167 this function just prepare the SQL request.
168 After this function, don't forget to execute it by using $sth->execute($subscriptionid)
170 $sth = $dbh->prepare($query).
175 sub GetSubscriptionHistoryFromSubscriptionId() {
176 my $dbh = C4::Context->dbh;
179 FROM subscriptionhistory
180 WHERE subscriptionid = ?
182 return $dbh->prepare($query);
185 =head2 GetSerialStatusFromSerialId
189 $sth = GetSerialStatusFromSerialId();
190 this function just prepare the SQL request.
191 After this function, don't forget to execute it by using $sth->execute($serialid)
193 $sth = $dbh->prepare($query).
198 sub GetSerialStatusFromSerialId(){
199 my $dbh = C4::Context->dbh;
205 return $dbh->prepare($query);
209 =head2 GetSubscription
213 $subs = GetSubscription($subscriptionid)
214 this function get the subscription which has $subscriptionid as id.
216 a hashref. This hash containts
217 subscription, subscriptionhistory, aqbudget.bookfundid, biblio.title
222 sub GetSubscription {
223 my ($subscriptionid) = @_;
224 my $dbh = C4::Context->dbh;
226 SELECT subscription.*,
227 subscriptionhistory.*,
229 aqbooksellers.name AS aqbooksellername,
230 biblio.title AS bibliotitle
232 LEFT JOIN subscriptionhistory ON subscription.subscriptionid=subscriptionhistory.subscriptionid
233 LEFT JOIN aqbudget ON subscription.aqbudgetid=aqbudget.aqbudgetid
234 LEFT JOIN aqbooksellers ON subscription.aqbooksellerid=aqbooksellers.id
235 LEFT JOIN biblio ON biblio.biblionumber=subscription.biblionumber
236 WHERE subscription.subscriptionid = ?
238 my $sth = $dbh->prepare($query);
239 $sth->execute($subscriptionid);
240 my $subs = $sth->fetchrow_hashref;
244 =head2 GetSubscriptionsFromBiblionumber
248 \@res = GetSubscriptionsFromBiblionumber($biblionumber)
249 this function get the subscription list. it reads on subscription table.
251 table of subscription which has the biblionumber given on input arg.
252 each line of this table is a hashref. All hashes containt
253 planned, histstartdate,opacnote,missinglist,receivedlist,periodicity,status & enddate
258 sub GetSubscriptionsFromBiblionumber {
259 my ($biblionumber) = @_;
260 my $dbh = C4::Context->dbh;
262 SELECT subscription.*,
263 subscriptionhistory.*,
265 aqbooksellers.name AS aqbooksellername,
266 biblio.title AS bibliotitle
268 LEFT JOIN subscriptionhistory ON subscription.subscriptionid=subscriptionhistory.subscriptionid
269 LEFT JOIN aqbudget ON subscription.aqbudgetid=aqbudget.aqbudgetid
270 LEFT JOIN aqbooksellers ON subscription.aqbooksellerid=aqbooksellers.id
271 LEFT JOIN biblio ON biblio.biblionumber=subscription.biblionumber
272 WHERE subscription.biblionumber = ?
274 my $sth = $dbh->prepare($query);
275 $sth->execute($biblionumber);
277 while (my $subs = $sth->fetchrow_hashref) {
278 $subs->{planneddate} = format_date($subs->{planneddate});
279 $subs->{publisheddate} = format_date($subs->{publisheddate});
280 $subs->{histstartdate} = format_date($subs->{histstartdate});
281 $subs->{opacnote} =~ s/\n/\<br\/\>/g;
282 $subs->{missinglist} =~ s/\n/\<br\/\>/g;
283 $subs->{receivedlist} =~ s/\n/\<br\/\>/g;
284 $subs->{"periodicity".$subs->{periodicity}} = 1;
285 $subs->{"status".$subs->{'status'}} = 1;
286 if ($subs->{enddate} eq '0000-00-00') {
289 $subs->{enddate} = format_date($subs->{enddate});
295 =head2 GetFullSubscriptionsFromBiblionumber
299 \@res = GetFullSubscriptionsFromBiblionumber($biblionumber)
300 this function read on serial table.
305 sub GetFullSubscriptionsFromBiblionumber {
306 my ($biblionumber) = @_;
307 my $dbh = C4::Context->dbh;
309 SELECT serial.serialseq,
311 serial.publisheddate,
314 year(serial.publisheddate) AS year,
315 aqbudget.bookfundid,aqbooksellers.name AS aqbooksellername,
316 biblio.title AS bibliotitle
318 LEFT JOIN subscription ON
319 (serial.subscriptionid=subscription.subscriptionid AND subscription.biblionumber=serial.biblionumber)
320 LEFT JOIN aqbudget ON subscription.aqbudgetid=aqbudget.aqbudgetid
321 LEFT JOIN aqbooksellers on subscription.aqbooksellerid=aqbooksellers.id
322 LEFT JOIN biblio on biblio.biblionumber=subscription.biblionumber
323 WHERE subscription.biblionumber = ?
324 ORDER BY year,serial.publisheddate,serial.subscriptionid,serial.planneddate
327 my $sth = $dbh->prepare($query);
328 $sth->execute($biblionumber);
332 my $aqbooksellername;
337 while (my $subs = $sth->fetchrow_hashref) {
338 ### BUG To FIX: When there is no published date, will create many null ids!!!
340 if ($year and ($year==$subs->{year})){
341 if ($first eq 1){$first=0;}
342 my $temp=$res[scalar(@res)-1]->{'serials'};
344 {'publisheddate' =>format_date($subs->{'publisheddate'}),
345 'planneddate' => format_date($subs->{'planneddate'}),
346 'serialseq' => $subs->{'serialseq'},
347 "status".$subs->{'status'} => 1,
348 'notes' => $subs->{'notes'} eq $previousnote?"":$subs->{notes},
351 $first=1 if (not $year);
352 $year= $subs->{'year'};
353 $startdate= format_date($subs->{'startdate'});
354 $aqbooksellername= $subs->{'aqbooksellername'};
355 $bibliotitle= $subs->{'bibliotitle'};
358 {'publisheddate' =>format_date($subs->{'publisheddate'}),
359 'planneddate' => format_date($subs->{'planneddate'}),
360 'serialseq' => $subs->{'serialseq'},
361 "status".$subs->{'status'} => 1,
362 'notes' => $subs->{'notes'} eq $previousnote?"":$subs->{notes},
367 'startdate'=>$startdate,
368 'aqbooksellername'=>$aqbooksellername,
369 'bibliotitle'=>$bibliotitle,
374 $previousnote=$subs->{notes};
380 =head2 GetSubscriptions
384 @results = GetSubscriptions($title,$ISSN,$biblionumber);
385 this function get all subscriptions which has title like $title,ISSN like $ISSN and biblionumber like $biblionumber.
387 a table of hashref. Each hash containt the subscription.
392 sub GetSubscriptions {
393 my ($title,$ISSN,$biblionumber,$supplierid) = @_;
394 return unless $title or $ISSN or $biblionumber or $supplierid;
395 my $dbh = C4::Context->dbh;
399 SELECT subscription.subscriptionid,biblio.title,biblio.issn,subscription.notes,biblio.biblionumber
400 FROM subscription,biblio
401 WHERE biblio.biblionumber = subscription.biblionumber
402 AND biblio.biblionumber=?
405 $sth = $dbh->prepare($query);
406 $sth->execute($biblionumber);
407 } elsif ($ISSN and $title){
409 SELECT subscription.subscriptionid,biblio.title,biblio.issn,subscription.notes,biblio.biblionumber,aqbooksellerid
410 FROM subscription,biblio
411 WHERE biblio.biblionumber= subscription.biblionumber
412 AND (biblio.title LIKE ? or biblio.issn = ?)
415 $sth = $dbh->prepare($query);
416 $sth->execute("%$title%",$ISSN);
419 SELECT subscription.subscriptionid,biblio.title,biblio.issn,subscription.notes,biblio.biblionumber,aqbooksellerid
420 FROM subscription,biblio
421 WHERE biblio.biblionumber=subscription.biblionumber
425 $sth = $dbh->prepare($query);
426 $sth->execute($ISSN);
427 }elsif ($supplierid){
429 SELECT subscription.subscriptionid,biblio.title,biblio.issn,subscription.notes,biblio.biblionumber,aqbooksellerid
430 FROM subscription,biblio
431 WHERE biblio.biblionumber=subscription.biblionumber
432 AND subscription.aqbooksellerid = ?
435 $sth = $dbh->prepare($query);
436 $sth->execute($supplierid);
439 SELECT subscription.subscriptionid,biblio.title,biblio.issn,subscription.notes,biblio.biblionumber,aqbooksellerid
440 FROM subscription,biblio
441 WHERE biblio.biblionumber=subscription.biblionumber
442 AND biblio.title LIKE ?
445 $sth = $dbh->prepare($query);
446 $sth->execute("%$title%");
451 my $previoustitle="";
453 while (my $line = $sth->fetchrow_hashref) {
454 if ($previoustitle eq $line->{title}) {
457 $line->{toggle} = 1 if $odd==1;
459 $previoustitle=$line->{title};
461 $line->{toggle} = 1 if $odd==1;
463 push @results, $line;
472 ($totalissues,@serials) = GetSerials($subscriptionid);
473 this function get every serial not arrived for a given subscription
474 as well as the number of issues registered in the database (all types)
475 this number is used to see if a subscription can be deleted (=it must have only 1 issue)
481 my ($subscriptionid) = @_;
482 my $dbh = C4::Context->dbh;
487 # status = 2 is "arrived"
491 WHERE subscriptionid = ? AND status NOT IN (2,4,5)
493 my $sth=$dbh->prepare($query);
494 $sth->execute($subscriptionid);
495 while(my $line = $sth->fetchrow_hashref) {
496 $line->{"status".$line->{status}} = 1; # fills a "statusX" value, used for template status select list
497 $line->{"publisheddate"} = format_date($line->{"publisheddate"});
498 $line->{"planneddate"} = format_date($line->{"planneddate"});
501 # OK, now add the last 5 issues arrived/missing
505 WHERE subscriptionid = ?
506 AND (status in (2,4,5))
507 ORDER BY serialid DESC
509 my $sth=$dbh->prepare($query);
510 $sth->execute($subscriptionid);
511 while((my $line = $sth->fetchrow_hashref) && $counter <5) {
513 $line->{"status".$line->{status}} = 1; # fills a "statusX" value, used for template status select list
514 $line->{"planneddate"} = format_date($line->{"planneddate"});
515 $line->{"publisheddate"} = format_date($line->{"publisheddate"});
521 WHERE subscriptionid=?
523 $sth=$dbh->prepare($query);
524 $sth->execute($subscriptionid);
525 my ($totalissues) = $sth->fetchrow;
526 return ($totalissues,@serials);
529 =head2 GetLatestSerials
533 \@serials = GetLatestSerials($subscriptionid,$limit)
534 get the $limit's latest serials arrived or missing for a given subscription
536 a ref to a table which it containts all of the latest serials stored into a hash.
541 sub GetLatestSerials {
542 my ($subscriptionid,$limit) = @_;
543 my $dbh = C4::Context->dbh;
544 # status = 2 is "arrived"
546 SELECT serialid,serialseq, status, planneddate
548 WHERE subscriptionid = ?
549 AND (status =2 or status=4)
550 ORDER BY planneddate DESC LIMIT 0,$limit
552 my $sth=$dbh->prepare($strsth);
553 $sth->execute($subscriptionid);
555 while(my $line = $sth->fetchrow_hashref) {
556 $line->{"status".$line->{status}} = 1; # fills a "statusX" value, used for template status select list
557 $line->{"planneddate"} = format_date($line->{"planneddate"});
563 # WHERE subscriptionid=?
565 # $sth=$dbh->prepare($query);
566 # $sth->execute($subscriptionid);
567 # my ($totalissues) = $sth->fetchrow;
571 =head2 GetDistributedTo
575 $distributedto=GetDistributedTo($subscriptionid)
576 This function select the old previous value of distributedto in the database.
581 sub GetDistributedTo {
582 my $dbh = C4::Context->dbh;
584 my $subscriptionid = @_;
588 WHERE subscriptionid=?
590 my $sth = $dbh->prepare($query);
591 $sth->execute($subscriptionid);
592 return ($distributedto) = $sth->fetchrow;
600 $val is a hashref containing all the attributes of the table 'subscription'
601 This function get the next issue for the subscription given on input arg
603 all the input params updated.
610 my ($calculated,$newlastvalue1,$newlastvalue2,$newlastvalue3,$newinnerloop1,$newinnerloop2,$newinnerloop3);
611 $calculated = $val->{numberingmethod};
612 # calculate the (expected) value of the next issue received.
613 $newlastvalue1 = $val->{lastvalue1};
614 # check if we have to increase the new value.
615 $newinnerloop1 = $val->{innerloop1}+1;
616 $newinnerloop1=0 if ($newinnerloop1 >= $val->{every1});
617 $newlastvalue1 += $val->{add1} if ($newinnerloop1<1); # <1 to be true when 0 or empty.
618 $newlastvalue1=$val->{setto1} if ($newlastvalue1>$val->{whenmorethan1}); # reset counter if needed.
619 $calculated =~ s/\{X\}/$newlastvalue1/g;
621 $newlastvalue2 = $val->{lastvalue2};
622 # check if we have to increase the new value.
623 $newinnerloop2 = $val->{innerloop2}+1;
624 $newinnerloop2=0 if ($newinnerloop2 >= $val->{every2});
625 $newlastvalue2 += $val->{add2} if ($newinnerloop2<1); # <1 to be true when 0 or empty.
626 $newlastvalue2=$val->{setto2} if ($newlastvalue2>$val->{whenmorethan2}); # reset counter if needed.
627 $calculated =~ s/\{Y\}/$newlastvalue2/g;
629 $newlastvalue3 = $val->{lastvalue3};
630 # check if we have to increase the new value.
631 $newinnerloop3 = $val->{innerloop3}+1;
632 $newinnerloop3=0 if ($newinnerloop3 >= $val->{every3});
633 $newlastvalue3 += $val->{add3} if ($newinnerloop3<1); # <1 to be true when 0 or empty.
634 $newlastvalue3=$val->{setto3} if ($newlastvalue3>$val->{whenmorethan3}); # reset counter if needed.
635 $calculated =~ s/\{Z\}/$newlastvalue3/g;
636 return ($calculated,$newlastvalue1,$newlastvalue2,$newlastvalue3,$newinnerloop1,$newinnerloop2,$newinnerloop3);
642 my ($calculated,$newlastvalue1,$newlastvalue2,$newlastvalue3,$newinnerloop1,$newinnerloop2,$newinnerloop3);
643 my $pattern = $val->{numberpattern};
644 my @seasons = ('nothing','Winter','Spring','Summer','Autumn');
645 my @southern_seasons = ('','Summer','Autumn','Winter','Spring');
646 $calculated = $val->{numberingmethod};
647 $newlastvalue1 = $val->{lastvalue1};
648 $newlastvalue2 = $val->{lastvalue2};
649 $newlastvalue3 = $val->{lastvalue3};
650 if($newlastvalue3 > 0){ # if x y and z columns are used
651 $newlastvalue3 = $newlastvalue3+1;
652 if($newlastvalue3 > $val->{whenmorethan3}){
653 $newlastvalue3 = $val->{setto3};
655 if($newlastvalue2 > $val->{whenmorethan2}){
657 $newlastvalue2 = $val->{setto2};
660 $calculated =~ s/\{X\}/$newlastvalue1/g;
662 if($val->{hemisphere} == 2){
663 my $newlastvalue2seq = $southern_seasons[$newlastvalue2];
664 $calculated =~ s/\{Y\}/$newlastvalue2seq/g;
666 my $newlastvalue2seq = $seasons[$newlastvalue2];
667 $calculated =~ s/\{Y\}/$newlastvalue2seq/g;
670 $calculated =~ s/\{Y\}/$newlastvalue2/g;
672 $calculated =~ s/\{Z\}/$newlastvalue3/g;
674 if($newlastvalue2 > 0 && $newlastvalue3 < 1){ # if x and y columns are used
675 $newlastvalue2 = $newlastvalue2+1;
676 if($newlastvalue2 > $val->{whenmorethan2}){
677 $newlastvalue2 = $val->{setto2};
680 $calculated =~ s/\{X\}/$newlastvalue1/g;
682 if($val->{hemisphere} == 2){
683 my $newlastvalue2seq = $southern_seasons[$newlastvalue2];
684 $calculated =~ s/\{Y\}/$newlastvalue2seq/g;
686 my $newlastvalue2seq = $seasons[$newlastvalue2];
687 $calculated =~ s/\{Y\}/$newlastvalue2seq/g;
690 $calculated =~ s/\{Y\}/$newlastvalue2/g;
693 if($newlastvalue1 > 0 && $newlastvalue2 < 1 && $newlastvalue3 < 1){ # if column x only
694 $newlastvalue1 = $newlastvalue1+1;
695 if($newlastvalue1 > $val->{whenmorethan1}){
696 $newlastvalue1 = $val->{setto2};
698 $calculated =~ s/\{X\}/$newlastvalue1/g;
700 return ($calculated,$newlastvalue1,$newlastvalue2,$newlastvalue3);
708 $resultdate = GetNextDate($planneddate,$subscription)
710 this function get the date after $planneddate.
712 the date on ISO format.
722 $calculated = GetSeq($val)
723 $val is a hashref containing all the attributes of the table 'subscription'
724 this function transforms {X},{Y},{Z} to 150,0,0 for example.
726 the sequence in integer format
733 my $calculated = $val->{numberingmethod};
734 my $x=$val->{'lastvalue1'};
735 $calculated =~ s/\{X\}/$x/g;
736 my $y=$val->{'lastvalue2'};
737 $calculated =~ s/\{Y\}/$y/g;
738 my $z=$val->{'lastvalue3'};
739 $calculated =~ s/\{Z\}/$z/g;
743 =head2 GetSubscriptionExpirationDate
747 $sensddate = GetSubscriptionExpirationDate($subscriptionid)
749 this function return the expiration date for a subscription given on input args.
757 sub GetSubscriptionExpirationDate {
758 my ($subscriptionid) = @_;
759 my $dbh = C4::Context->dbh;
760 my $subscription = GetSubscription($subscriptionid);
761 my $enddate=$subscription->{startdate};
762 # we don't do the same test if the subscription is based on X numbers or on X weeks/months
763 if ($subscription->{numberlength}) {
764 #calculate the date of the last issue.
765 for (my $i=1;$i<=$subscription->{numberlength};$i++) {
766 $enddate = GetNextDate($enddate,$subscription);
771 $duration=get_duration($subscription->{monthlength}." months") if ($subscription->{monthlength});
772 $duration=get_duration($subscription->{weeklength}." weeks") if ($subscription->{weeklength});
773 $enddate = DATE_Add_Duration($subscription->{startdate},$duration) ;
778 =head2 CountSubscriptionFromBiblionumber
782 $subscriptionsnumber = CountSubscriptionFromBiblionumber($biblionumber)
783 this count the number of subscription for a biblionumber given.
785 the number of subscriptions with biblionumber given on input arg.
790 sub CountSubscriptionFromBiblionumber {
791 my ($biblionumber) = @_;
792 my $dbh = C4::Context->dbh;
798 my $sth = $dbh->prepare($query);
799 $sth->execute($biblionumber);
800 my $subscriptionsnumber = $sth->fetchrow;
801 return $subscriptionsnumber;
805 =head2 ModSubscriptionHistory
809 ModSubscriptionHistory($subscriptionid,$histstartdate,$enddate,$receivedlist,$missinglist,$opacnote,$librariannote);
811 this function modify the history of a subscription. Put your new values on input arg.
816 sub ModSubscriptionHistory {
817 my ($subscriptionid,$histstartdate,$enddate,$receivedlist,$missinglist,$opacnote,$librariannote)=@_;
818 my $dbh=C4::Context->dbh;
820 UPDATE subscriptionhistory
821 SET histstartdate=?,enddate=?,receivedlist=?,missinglist=?,opacnote=?,librariannote=?
822 WHERE subscriptionid=?
824 my $sth = $dbh->prepare($query);
825 $receivedlist =~ s/^,//g;
826 $missinglist =~ s/^,//g;
827 $opacnote =~ s/^,//g;
828 $sth->execute($histstartdate,$enddate,$receivedlist,$missinglist,$opacnote,$librariannote,$subscriptionid);
831 =head2 ModSerialStatus
835 ModSerialStatus($serialid,$serialseq, $publisheddate,$planneddate,$status,$notes)
837 This function modify the serial status. Serial status is a number.(eg 2 is "arrived")
838 Note : if we change from "waited" to something else,then we will have to create a new "waited" entry
843 sub ModSerialStatus {
844 my ($serialid,$serialseq, $publisheddate,$planneddate,$status,$notes,$itemnumber)=@_;
846 # 1st, get previous status :
847 my $dbh = C4::Context->dbh;
849 SELECT subscriptionid,status
853 my $sth = $dbh->prepare($query);
854 $sth->execute($serialid);
855 my ($subscriptionid,$oldstatus) = $sth->fetchrow;
856 # change status & update subscriptionhistory
858 DelIssue($serialseq, $subscriptionid)
862 SET serialseq=?,publisheddate=?,planneddate=?,status=?,notes=?,itemnumber=?
865 $sth = $dbh->prepare($query);
866 $sth->execute($serialseq,format_date_in_iso($publisheddate),format_date_in_iso($planneddate),$status,$notes,$itemnumber,$serialid);
868 SELECT missinglist,receivedlist
869 FROM subscriptionhistory
870 WHERE subscriptionid=?
872 $sth = $dbh->prepare($query);
873 $sth->execute($subscriptionid);
874 my ($missinglist,$receivedlist) = $sth->fetchrow;
875 if ($status == 2 && $oldstatus != 2) {
876 $receivedlist .= ",$serialseq";
878 $missinglist .= ",$serialseq" if ($status eq 4) ;
879 $missinglist .= ",not issued $serialseq" if ($status eq 5);
881 UPDATE subscriptionhistory
882 SET receivedlist=?, missinglist=?
883 WHERE subscriptionid=?
885 $sth=$dbh->prepare($query);
886 $sth->execute($receivedlist,$missinglist,$subscriptionid);
888 # create new waited entry if needed (ie : was a "waited" and has changed)
889 if ($oldstatus eq 1 && $status ne 1) {
893 WHERE subscriptionid = ?
895 $sth = $dbh->prepare($query);
896 $sth->execute($subscriptionid);
897 my $val = $sth->fetchrow_hashref;
899 my ($newserialseq,$newlastvalue1,$newlastvalue2,$newlastvalue3,$newinnerloop1,$newinnerloop2,$newinnerloop3) = GetNextSeq($val);
900 # next date (calculated from actual date & frequency parameters)
901 my $nextplanneddate = GetNextDate($planneddate,$val);
902 my $nextpublisheddate = GetNextDate($publisheddate,$val);
903 NewIssue($newserialseq, $subscriptionid, $val->{'biblionumber'}, 1, $nextpublisheddate,$nextplanneddate,0);
906 SET lastvalue1=?, lastvalue2=?, lastvalue3=?,
907 innerloop1=?, innerloop2=?, innerloop3=?
908 WHERE subscriptionid = ?
910 $sth = $dbh->prepare($query);
911 $sth->execute($newlastvalue1,$newlastvalue2,$newlastvalue3,$newinnerloop1,$newinnerloop2,$newinnerloop3,$subscriptionid);
915 =head2 ModSubscription
919 this function modify a subscription. Put all new values on input args.
924 sub ModSubscription {
925 my ($auser,$aqbooksellerid,$cost,$aqbudgetid,$startdate,
926 $periodicity,$dow,$numberlength,$weeklength,$monthlength,
927 $add1,$every1,$whenmorethan1,$setto1,$lastvalue1,$innerloop1,
928 $add2,$every2,$whenmorethan2,$setto2,$lastvalue2,$innerloop2,
929 $add3,$every3,$whenmorethan3,$setto3,$lastvalue3,$innerloop3,
930 $numberingmethod, $status, $biblionumber, $notes, $letter, $subscriptionid,$irregularity,$hemisphere,$callnumber,$numberpattern,$publisheddate)= @_;
931 my $dbh = C4::Context->dbh;
934 SET librarian=?, aqbooksellerid=?,cost=?,aqbudgetid=?,startdate=?,
935 periodicity=?,dow=?,numberlength=?,weeklength=?,monthlength=?,
936 add1=?,every1=?,whenmorethan1=?,setto1=?,lastvalue1=?,innerloop1=?,
937 add2=?,every2=?,whenmorethan2=?,setto2=?,lastvalue2=?,innerloop2=?,
938 add3=?,every3=?,whenmorethan3=?,setto3=?,lastvalue3=?,innerloop3=?,
939 numberingmethod=?, status=?, biblionumber=?, notes=?, letter=?,irregularity=?,hemisphere=?,callnumber=?,numberpattern=? ,publisheddate=?
940 WHERE subscriptionid = ?
942 my $sth=$dbh->prepare($query);
943 $sth->execute($auser,$aqbooksellerid,$cost,$aqbudgetid,$startdate,
944 $periodicity,$dow,$numberlength,$weeklength,$monthlength,
945 $add1,$every1,$whenmorethan1,$setto1,$lastvalue1,$innerloop1,
946 $add2,$every2,$whenmorethan2,$setto2,$lastvalue2,$innerloop2,
947 $add3,$every3,$whenmorethan3,$setto3,$lastvalue3,$innerloop3,
948 $numberingmethod, $status, $biblionumber, $notes, $letter, $irregularity,$hemisphere,$callnumber,$numberpattern,$publisheddate,$subscriptionid);
953 =head2 NewSubscription
957 $subscriptionid = &NewSubscription($auser,$aqbooksellerid,$cost,$aqbudgetid,$biblionumber,
958 $startdate,$periodicity,$dow,$numberlength,$weeklength,$monthlength,
959 $add1,$every1,$whenmorethan1,$setto1,$lastvalue1,$innerloop1,
960 $add2,$every2,$whenmorethan2,$setto2,$lastvalue2,$innerloop2,
961 $add3,$every3,$whenmorethan3,$setto3,$lastvalue3,$innerloop3,
962 $numberingmethod, $status, $notes)
964 Create a new subscription with value given on input args.
967 the id of this new subscription
972 sub NewSubscription {
973 my ($auser,$aqbooksellerid,$cost,$aqbudgetid,$biblionumber,
974 $startdate,$periodicity,$dow,$numberlength,$weeklength,$monthlength,
975 $add1,$every1,$whenmorethan1,$setto1,$lastvalue1,
976 $add2,$every2,$whenmorethan2,$setto2,$lastvalue2,
977 $add3,$every3,$whenmorethan3,$setto3,$lastvalue3,
978 $numberingmethod, $status, $notes, $letter,$irregularity,$hemisphere,$callnumber,$numberpattern,$publisheddate) = @_;
980 my $dbh = C4::Context->dbh;
981 #save subscription (insert into database)
983 INSERT INTO subscription
984 (librarian,aqbooksellerid,cost,aqbudgetid,biblionumber,
985 startdate,periodicity,dow,numberlength,weeklength,monthlength,
986 add1,every1,whenmorethan1,setto1,lastvalue1,
987 add2,every2,whenmorethan2,setto2,lastvalue2,
988 add3,every3,whenmorethan3,setto3,lastvalue3,
989 numberingmethod, status, notes, letter,irregularity,hemisphere,callnumber,numberpattern,publisheddate)
990 VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
992 my $sth=$dbh->prepare($query);
994 $auser,$aqbooksellerid,$cost,$aqbudgetid,$biblionumber,
995 format_date_in_iso($startdate),$periodicity,$dow,$numberlength,$weeklength,$monthlength,
996 $add1,$every1,$whenmorethan1,$setto1,$lastvalue1,
997 $add2,$every2,$whenmorethan2,$setto2,$lastvalue2,
998 $add3,$every3,$whenmorethan3,$setto3,$lastvalue3,
999 $numberingmethod, $status, $notes, $letter,$irregularity,$hemisphere,$callnumber,$numberpattern,format_date_in_iso($publisheddate));
1002 #then create the 1st waited number
1003 my $subscriptionid = $dbh->{'mysql_insertid'};
1004 my $enddate = GetSubscriptionExpirationDate($subscriptionid);
1006 INSERT INTO subscriptionhistory
1007 (biblionumber, subscriptionid, histstartdate, enddate, missinglist, receivedlist, opacnote, librariannote)
1008 VALUES (?,?,?,?,?,?,?,?)
1010 $sth = $dbh->prepare($query);
1011 $sth->execute($biblionumber, $subscriptionid, format_date_in_iso($startdate), format_date_in_iso($enddate), "", "", "", $notes);
1012 ## User may have subscriptionid stored in MARC so check and fill it
1013 my $record=XMLgetbiblio($dbh,$biblionumber);
1014 $record=XML_xml2hash_onerecord($record);
1015 XML_writeline( $record, "subscriptionid", $subscriptionid,"biblios" );
1016 my $frameworkcode=MARCfind_frameworkcode($dbh,$biblionumber);
1017 NEWmodbiblio($dbh,$biblionumber,$record,$frameworkcode);
1018 # reread subscription to get a hash (for calculation of the 1st issue number)
1022 WHERE subscriptionid = ?
1024 $sth = $dbh->prepare($query);
1025 $sth->execute($subscriptionid);
1026 my $val = $sth->fetchrow_hashref;
1028 # calculate issue number
1029 my $serialseq = GetSeq($val);
1032 (serialseq,subscriptionid,biblionumber,status, planneddate,publisheddate)
1033 VALUES (?,?,?,?,?,?)
1036 $sth = $dbh->prepare($query);
1037 $sth->execute($serialseq, $subscriptionid, $val->{'biblionumber'}, 1, format_date_in_iso($startdate),format_date_in_iso($publisheddate));
1038 return $subscriptionid;
1042 =head2 ReNewSubscription
1046 ReNewSubscription($subscriptionid,$user,$startdate,$numberlength,$weeklength,$monthlength,$note)
1048 this function renew a subscription with values given on input args.
1053 sub ReNewSubscription {
1054 my ($subscriptionid,$user,$startdate,$numberlength,$weeklength,$monthlength,$note) = @_;
1055 my $dbh = C4::Context->dbh;
1056 my $subscription = GetSubscription($subscriptionid);
1057 my $record=XMLgetbiblio($dbh,$subscription->{biblionumber});
1058 $record=XML_xml2hash_onerecord($record);
1059 my $biblio = XMLmarc2koha_onerecord($dbh,$record,"biblios");
1060 NewSuggestion($user,$subscription->{bibliotitle},$biblio->{author},$biblio->{publishercode},$biblio->{note},'','','','','',$subscription->{biblionumber});
1061 # renew subscription
1064 SET startdate=?,numberlength=?,weeklength=?,monthlength=?
1065 WHERE subscriptionid=?
1067 my $sth=$dbh->prepare($query);
1068 $sth->execute(format_date_in_iso($startdate),$numberlength,$weeklength,$monthlength, $subscriptionid);
1076 NewIssue($serialseq,$subscriptionid,$biblionumber,$status, $publisheddate, $planneddate)
1078 Create a new issue stored on the database.
1079 Note : we have to update the receivedlist and missinglist on subscriptionhistory for this subscription.
1085 my ($serialseq,$subscriptionid,$biblionumber,$status, $publisheddate, $planneddate,$itemnumber) = @_;
1086 my $dbh = C4::Context->dbh;
1089 (serialseq,subscriptionid,biblionumber,status,publisheddate,planneddate,itemnumber)
1090 VALUES (?,?,?,?,?,?,?)
1092 my $sth = $dbh->prepare($query);
1093 $sth->execute($serialseq,$subscriptionid,$biblionumber,$status,format_date_in_iso($publisheddate), format_date_in_iso($planneddate),$itemnumber);
1096 SELECT missinglist,receivedlist
1097 FROM subscriptionhistory
1098 WHERE subscriptionid=?
1100 $sth = $dbh->prepare($query);
1101 $sth->execute($subscriptionid);
1102 my ($missinglist,$receivedlist) = $sth->fetchrow;
1104 $receivedlist .= ",$serialseq";
1107 $missinglist .= ",$serialseq";
1110 UPDATE subscriptionhistory
1111 SET receivedlist=?, missinglist=?
1112 WHERE subscriptionid=?
1114 $sth=$dbh->prepare($query);
1115 $sth->execute($receivedlist,$missinglist,$subscriptionid);
1118 =head2 serialchangestatus
1122 serialchangestatus($serialid,$serialseq,$planneddate,$status,$notes)
1124 Change the status of a serial issue.
1125 Note: this was the older subroutine
1130 sub serialchangestatus {
1131 my ($serialid,$serialseq,$planneddate,$status,$notes)=@_;
1132 # 1st, get previous status : if we change from "waited" to something else, then we will have to create a new "waited" entry
1133 my $dbh = C4::Context->dbh;
1134 my $sth = $dbh->prepare("select subscriptionid,status from serial where serialid=?");
1135 $sth->execute($serialid);
1136 my ($subscriptionid,$oldstatus) = $sth->fetchrow;
1137 # change status & update subscriptionhistory
1139 delissue($serialseq, $subscriptionid)
1141 $sth = $dbh->prepare("update serial set serialseq=?,planneddate=?,status=?,notes=? where serialid = ?");
1142 $sth->execute($serialseq,format_date_in_iso($planneddate),$status,$notes,$serialid);
1144 $sth = $dbh->prepare("select missinglist,receivedlist from subscriptionhistory where subscriptionid=?");
1145 $sth->execute($subscriptionid);
1146 my ($missinglist,$receivedlist) = $sth->fetchrow;
1148 $receivedlist .= "| $serialseq";
1149 $receivedlist =~ s/^\| //g;
1151 $missinglist .= "| $serialseq" if ($status eq 4) ;
1152 $missinglist .= "| not issued $serialseq" if ($status eq 5);
1153 $missinglist =~ s/^\| //g;
1154 $sth=$dbh->prepare("update subscriptionhistory set receivedlist=?, missinglist=? where subscriptionid=?");
1155 $sth->execute($receivedlist,$missinglist,$subscriptionid);
1157 # create new waited entry if needed (ie : was a "waited" and has changed)
1158 if ($oldstatus eq 1 && $status ne 1) {
1159 $sth = $dbh->prepare("select * from subscription where subscriptionid = ? ");
1160 $sth->execute($subscriptionid);
1161 my $val = $sth->fetchrow_hashref;
1163 my ($newserialseq,$newlastvalue1,$newlastvalue2,$newlastvalue3) = New_Get_Next_Seq($val);
1164 my $nextplanneddate = GetNextDate($planneddate,$val);
1165 NewIssue($newserialseq, $subscriptionid, $val->{'biblionumber'}, 1, $nextplanneddate);
1166 $sth = $dbh->prepare("update subscription set lastvalue1=?, lastvalue2=?,lastvalue3=? where subscriptionid = ?");
1167 $sth->execute($newlastvalue1,$newlastvalue2,$newlastvalue3,$subscriptionid);
1169 # check if an alert must be sent... (= a letter is defined & status became "arrived"
1170 $sth = $dbh->prepare("select * from subscription where subscriptionid = ? ");
1171 $sth->execute($subscriptionid);
1172 my $subscription = $sth->fetchrow_hashref;
1173 if ($subscription->{letter} && $status eq 2) {
1174 sendalerts('issue',$subscription->{subscriptionid},$subscription->{letter});
1181 =head2 HasSubscriptionExpired
1185 1 or 0 = HasSubscriptionExpired($subscriptionid)
1187 the subscription has expired when the next issue to arrive is out of subscription limit.
1190 1 if true, 0 if false.
1195 sub HasSubscriptionExpired {
1196 my ($subscriptionid) = @_;
1197 my $dbh = C4::Context->dbh;
1198 my $subscription = GetSubscription($subscriptionid);
1199 # we don't do the same test if the subscription is based on X numbers or on X weeks/months
1200 if ($subscription->{numberlength} ) {
1204 WHERE subscriptionid=? AND planneddate>=?
1206 my $sth = $dbh->prepare($query);
1207 $sth->execute($subscriptionid,$subscription->{startdate});
1208 my $res = $sth->fetchrow;
1209 if ($subscription->{numberlength}>=$res) {
1215 #a little bit more tricky if based on X weeks/months : search if the latest issue waited is not after subscription startdate + duration
1217 SELECT max(planneddate)
1219 WHERE subscriptionid=?
1221 my $sth = $dbh->prepare($query);
1222 $sth->execute($subscriptionid);
1223 my $res = $sth->fetchrow;
1224 my $endofsubscriptiondate;
1226 $duration=get_duration($subscription->{monthlength}." months") if ($subscription->{monthlength});
1227 $duration=get_duration($subscription->{weeklength}." weeks") if ($subscription->{weeklength});
1229 $endofsubscriptiondate = DATE_Add_Duration($subscription->{startdate},$duration) ;
1230 return 1 if ($res ge $endofsubscriptiondate);
1235 =head2 SetDistributedto
1239 SetDistributedto($distributedto,$subscriptionid);
1240 This function update the value of distributedto for a subscription given on input arg.
1245 sub SetDistributedto {
1246 my ($distributedto,$subscriptionid) = @_;
1247 my $dbh = C4::Context->dbh;
1251 WHERE subscriptionid=?
1253 my $sth = $dbh->prepare($query);
1254 $sth->execute($distributedto,$subscriptionid);
1257 =head2 DelSubscription
1261 DelSubscription($subscriptionid)
1262 this function delete the subscription which has $subscriptionid as id.
1267 sub DelSubscription {
1268 my ($subscriptionid,$biblionumber) = @_;
1269 my $dbh = C4::Context->dbh;
1270 ## User may have subscriptionid stored in MARC so check and remove it
1271 my $record=XMLgetbibliohash($dbh,$biblionumber);
1272 XML_writeline( $record, "subscriptionid", "","biblios" );
1273 my $frameworkcode=MARCfind_frameworkcode($dbh,$biblionumber);
1274 NEWmodbiblio($dbh,$biblionumber,$record,$frameworkcode);
1275 $subscriptionid=$dbh->quote($subscriptionid);
1276 $dbh->do("DELETE FROM subscription WHERE subscriptionid=$subscriptionid");
1277 $dbh->do("DELETE FROM subscriptionhistory WHERE subscriptionid=$subscriptionid");
1278 $dbh->do("DELETE FROM serial WHERE subscriptionid=$subscriptionid");
1286 DelIssue($serialseq,$subscriptionid)
1287 this function delete an issue which has $serialseq and $subscriptionid given on input arg.
1293 my ($serialseq,$subscriptionid) = @_;
1294 my $dbh = C4::Context->dbh;
1298 AND subscriptionid= ?
1300 my $sth = $dbh->prepare($query);
1301 $sth->execute($serialseq,$subscriptionid);
1304 =head2 GetMissingIssues
1308 ($count,@issuelist) = &GetMissingIssues($supplierid,$serialid)
1310 this function select missing issues on database - where serial.status = 4
1313 a count of the number of missing issues
1314 the issuelist into a table. Each line of this table containts a ref to a hash which it containts
1315 name,title,planneddate,serialseq,serial.subscriptionid from tables : subscription, serial & biblio
1320 sub GetMissingIssues {
1321 my ($supplierid,$serialid) = @_;
1322 my $dbh = C4::Context->dbh;
1326 $byserial = "and serialid = ".$serialid;
1329 $sth = $dbh->prepare("SELECT serialid,aqbooksellerid,name,title,planneddate,serialseq,serial.subscriptionid,claimdate
1330 FROM subscription, serial, biblio
1331 LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id
1332 WHERE subscription.subscriptionid = serial.subscriptionid AND
1333 serial.STATUS = 4 and
1334 subscription.aqbooksellerid=$supplierid and
1335 biblio.biblionumber = subscription.biblionumber ".$byserial." order by title
1338 $sth = $dbh->prepare("SELECT serialid,aqbooksellerid,name,title,planneddate,serialseq,serial.subscriptionid,claimdate
1339 FROM subscription, serial, biblio
1340 LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id
1341 WHERE subscription.subscriptionid = serial.subscriptionid AND
1342 serial.STATUS =4 and
1343 biblio.biblionumber = subscription.biblionumber ".$byserial." order by title
1351 while (my $line = $sth->fetchrow_hashref) {
1352 $odd++ unless $line->{title} eq $last_title;
1353 $last_title = $line->{title} if ($line->{title});
1354 $line->{planneddate} = format_date($line->{planneddate});
1355 $line->{claimdate} = format_date($line->{claimdate});
1356 $line->{'odd'} = 1 if $odd %2 ;
1358 push @issuelist,$line;
1360 return $count,@issuelist;
1363 =head2 removeMissingIssue
1367 removeMissingIssue($subscriptionid)
1369 this function removes an issue from being part of the missing string in
1370 subscriptionlist.missinglist column
1372 called when a missing issue is found from the statecollection.pl file
1377 sub removeMissingIssue {
1378 my ($sequence,$subscriptionid) = @_;
1379 my $dbh = C4::Context->dbh;
1380 my $sth = $dbh->prepare("SELECT * FROM subscriptionhistory WHERE subscriptionid = ?");
1381 $sth->execute($subscriptionid);
1382 my $data = $sth->fetchrow_hashref;
1383 my $missinglist = $data->{'missinglist'};
1384 my $missinglistbefore = $missinglist;
1385 # warn $missinglist." before";
1386 $missinglist =~ s/($sequence)//;
1387 # warn $missinglist." after";
1388 if($missinglist ne $missinglistbefore){
1389 $missinglist =~ s/\|\s\|/\|/g;
1390 $missinglist =~ s/^\| //g;
1391 $missinglist =~ s/\|$//g;
1392 my $sth2= $dbh->prepare("UPDATE subscriptionhistory
1394 WHERE subscriptionid = ?");
1395 $sth2->execute($missinglist,$subscriptionid);
1403 &updateClaim($serialid)
1405 this function updates the time when a claim is issued for late/missing items
1407 called from claims.pl file
1413 my ($serialid) = @_;
1414 my $dbh = C4::Context->dbh;
1415 my $sth = $dbh->prepare("UPDATE serial SET claimdate = now()
1418 $sth->execute($serialid);
1421 =head2 getsupplierbyserialid
1425 ($result) = &getsupplierbyserialid($serialid)
1427 this function is used to find the supplier id given a serial id
1430 hashref containing serialid, subscriptionid, and aqbooksellerid
1435 sub getsupplierbyserialid {
1436 my ($serialid) = @_;
1437 my $dbh = C4::Context->dbh;
1438 my $sth = $dbh->prepare("SELECT serialid, serial.subscriptionid, aqbooksellerid
1439 FROM serial, subscription
1440 WHERE serial.subscriptionid = subscription.subscriptionid
1443 $sth->execute($serialid);
1444 my $line = $sth->fetchrow_hashref;
1445 my $result = $line->{'aqbooksellerid'};
1449 =head2 check_routing
1453 ($result) = &check_routing($subscriptionid)
1455 this function checks to see if a serial has a routing list and returns the count of routingid
1456 used to show either an 'add' or 'edit' link
1461 my ($subscriptionid) = @_;
1462 my $dbh = C4::Context->dbh;
1463 my $sth = $dbh->prepare("SELECT count(routingid) routingids FROM subscriptionroutinglist, subscription
1464 WHERE subscription.subscriptionid = subscriptionroutinglist.subscriptionid
1465 AND subscription.subscriptionid = ? ORDER BY ranking ASC
1467 $sth->execute($subscriptionid);
1468 my $line = $sth->fetchrow_hashref;
1469 my $result = $line->{'routingids'};
1473 =head2 addroutingmember
1477 &addroutingmember($bornum,$subscriptionid)
1479 this function takes a borrowernumber and subscriptionid and add the member to the
1480 routing list for that serial subscription and gives them a rank on the list
1481 of either 1 or highest current rank + 1
1486 sub addroutingmember {
1487 my ($bornum,$subscriptionid) = @_;
1489 my $dbh = C4::Context->dbh;
1490 my $sth = $dbh->prepare("SELECT max(ranking) rank FROM subscriptionroutinglist WHERE subscriptionid = ?");
1491 $sth->execute($subscriptionid);
1492 while(my $line = $sth->fetchrow_hashref){
1493 if($line->{'rank'}>0){
1494 $rank = $line->{'rank'}+1;
1499 $sth = $dbh->prepare("INSERT INTO subscriptionroutinglist VALUES (null,?,?,?,null)");
1500 $sth->execute($subscriptionid,$bornum,$rank);
1503 =head2 reorder_members
1507 &reorder_members($subscriptionid,$routingid,$rank)
1509 this function is used to reorder the routing list
1511 it takes the routingid of the member one wants to re-rank and the rank it is to move to
1512 - it gets all members on list puts their routingid's into an array
1513 - removes the one in the array that is $routingid
1514 - then reinjects $routingid at point indicated by $rank
1515 - then update the database with the routingids in the new order
1520 sub reorder_members {
1521 my ($subscriptionid,$routingid,$rank) = @_;
1522 my $dbh = C4::Context->dbh;
1523 my $sth = $dbh->prepare("SELECT * FROM subscriptionroutinglist WHERE subscriptionid = ? ORDER BY ranking ASC");
1524 $sth->execute($subscriptionid);
1526 while(my $line = $sth->fetchrow_hashref){
1527 push(@result,$line->{'routingid'});
1529 # To find the matching index
1531 my $key = -1; # to allow for 0 being a valid response
1532 for ($i = 0; $i < @result; $i++) {
1533 if ($routingid == $result[$i]) {
1534 $key = $i; # save the index
1538 # if index exists in array then move it to new position
1539 if($key > -1 && $rank > 0){
1540 my $new_rank = $rank-1; # $new_rank is what you want the new index to be in the array
1541 my $moving_item = splice(@result, $key, 1);
1542 splice(@result, $new_rank, 0, $moving_item);
1544 for(my $j = 0; $j < @result; $j++){
1545 my $sth = $dbh->prepare("UPDATE subscriptionroutinglist SET ranking = '" . ($j+1) . "' WHERE routingid = '". $result[$j]."'");
1550 =head2 delroutingmember
1554 &delroutingmember($routingid,$subscriptionid)
1556 this function either deletes one member from routing list if $routingid exists otherwise
1557 deletes all members from the routing list
1562 sub delroutingmember {
1563 # if $routingid exists then deletes that row otherwise deletes all with $subscriptionid
1564 my ($routingid,$subscriptionid) = @_;
1565 my $dbh = C4::Context->dbh;
1567 my $sth = $dbh->prepare("DELETE FROM subscriptionroutinglist WHERE routingid = ?");
1568 $sth->execute($routingid);
1569 reorder_members($subscriptionid,$routingid);
1571 my $sth = $dbh->prepare("DELETE FROM subscriptionroutinglist WHERE subscriptionid = ?");
1572 $sth->execute($subscriptionid);
1576 =head2 getroutinglist
1580 ($count,@routinglist) = &getroutinglist($subscriptionid)
1582 this gets the info from the subscriptionroutinglist for $subscriptionid
1585 a count of the number of members on routinglist
1586 the routinglist into a table. Each line of this table containts a ref to a hash which containts
1587 routingid - a unique id, borrowernumber, ranking, and biblionumber of subscription
1592 sub getroutinglist {
1593 my ($subscriptionid) = @_;
1594 my $dbh = C4::Context->dbh;
1595 my $sth = $dbh->prepare("SELECT routingid, borrowernumber,
1596 ranking, biblionumber FROM subscriptionroutinglist, subscription
1597 WHERE subscription.subscriptionid = subscriptionroutinglist.subscriptionid
1598 AND subscription.subscriptionid = ? ORDER BY ranking ASC
1600 $sth->execute($subscriptionid);
1603 while (my $line = $sth->fetchrow_hashref) {
1605 push(@routinglist,$line);
1607 return ($count,@routinglist);
1610 =head2 abouttoexpire
1614 $result = &abouttoexpire($subscriptionid)
1616 this function alerts you to the penultimate issue for a serial subscription
1618 returns 1 - if this is the penultimate issue
1626 my ($subscriptionid) = @_;
1627 my $dbh = C4::Context->dbh;
1628 my $subscription = GetSubscription($subscriptionid);
1629 # we don't do the same test if the subscription is based on X numbers or on X weeks/months
1630 if ($subscription->{numberlength}) {
1631 my $sth = $dbh->prepare("select count(*) from serial where subscriptionid=? and planneddate>=?");
1632 $sth->execute($subscriptionid,$subscription->{startdate});
1633 my $res = $sth->fetchrow;
1634 # warn "length: ".$subscription->{numberlength}." vs count: ".$res;
1635 if ($subscription->{numberlength}==$res) {
1641 # a little bit more tricky if based on X weeks/months : search if the latest issue waited is not after subscription startdate + duration
1642 my $sth = $dbh->prepare("select max(planneddate) from serial where subscriptionid=?");
1643 $sth->execute($subscriptionid);
1644 my $res = $sth->fetchrow;
1645 my $endofsubscriptiondate;
1647 $duration=get_duration($subscription->{monthlength}." months") if ($subscription->{monthlength});
1648 $duration=get_duration($subscription->{weeklength}." weeks") if ($subscription->{weeklength});
1650 $endofsubscriptiondate = DATE_Add_Duration($subscription->{startdate},$duration) ;
1651 my $per = $subscription->{'periodicity'};
1653 if ($per == 1) { $x = '1 days'; }
1654 if ($per == 2) { $x = '1 weeks'; }
1655 if ($per == 3) { $x = '2 weeks'; }
1656 if ($per == 4) { $x = '3 weeks'; }
1657 if ($per == 5) { $x = '1 months'; }
1658 if ($per == 6) { $x = '2 months'; }
1659 if ($per == 7 || $per == 8) { $x = '3 months'; }
1660 if ($per == 9) { $x = '6 months'; }
1661 if ($per == 10) { $x = '1 years'; }
1662 if ($per == 11) { $x = '2 years'; }
1663 my $duration=get_duration("-".$x) ;
1664 my $datebeforeend = DATE_Add_Duration($endofsubscriptiondate,$duration); # if ($subscription->{weeklength});
1665 # warn "DATE BEFORE END: $datebeforeend";
1666 return 1 if ($res >= $datebeforeend && $res < $endofsubscriptiondate);
1677 ($resultdate) = &GetNextDate($planneddate,$subscription)
1679 this function takes the planneddate and will return the next issue's date and will skip dates if there
1680 exists an irregularity
1681 - eg if periodicity is monthly and $planneddate is 2007-02-10 but if March and April is to be
1682 skipped then the returned date will be 2007-05-10
1685 $resultdate - then next date in the sequence
1691 my ($planneddate,$subscription) = @_;
1692 my @irreg = split(/\|/,$subscription->{irregularity});
1693 my $dateobj=DATE_obj($planneddate);
1694 my $dayofweek = $dateobj->day_of_week;
1695 my $month=$dateobj->month;
1698 if ($subscription->{periodicity} == 1) {
1700 for(my $i=0;$i<@irreg;$i++){
1701 $irreghash{$irreg[$i]}=1;
1703 my $duration=get_duration("1 days");
1704 for(my $i=0;$i<@irreg;$i++){
1705 if($dayofweek == 7){ $dayofweek = 0; }
1707 if($irreghash{$dayofweek+1}){
1708 $planneddate = DATE_Add_Duration($planneddate,$duration);
1712 $resultdate=DATE_Add_Duration($planneddate,$duration);
1714 if ($subscription->{periodicity} == 2) {
1715 my $wkno = $dateobj->week_number;
1716 my $duration=get_duration("1 weeks");
1717 for(my $i = 0;$i < @irreg; $i++){
1718 if($wkno > 52) { $wkno = 0; } # need to rollover at January
1719 if($irreg[$i] == ($wkno+1)){
1720 $planneddate = DATE_Add_Duration($planneddate,$duration);
1724 $resultdate=DATE_Add_Duration($planneddate,$duration);
1726 if ($subscription->{periodicity} == 3) {
1727 my $wkno = $dateobj->week_number;
1728 my $duration=get_duration("2 weeks");
1729 for(my $i = 0;$i < @irreg; $i++){
1730 if($wkno > 52) { $wkno = 0; } # need to rollover at January
1731 if($irreg[$i] == ($wkno+1)){
1732 $planneddate = DATE_Add_Duration($planneddate,$duration);
1736 $resultdate=DATE_Add_Duration($planneddate,$duration);
1738 if ($subscription->{periodicity} == 4) {
1739 my $wkno = $dateobj->week_number;
1740 my $duration=get_duration("3 weeks");
1741 for(my $i = 0;$i < @irreg; $i++){
1742 if($wkno > 52) { $wkno = 0; } # need to rollover at January
1743 if($irreg[$i] == ($wkno+1)){
1744 $planneddate = DATE_Add_Duration($planneddate,$duration);
1748 $resultdate=DATE_Add_Duration($planneddate,$duration);
1750 if ($subscription->{periodicity} == 5) {
1751 my $duration=get_duration("1 months");
1752 for(my $i = 0;$i < @irreg; $i++){
1755 if($month == 12) { $month = 0; } # need to rollover to check January
1756 if($irreg[$i] == ($month+1)){ # check next one to see if is to be skipped
1757 $planneddate = DATE_Add_Duration($planneddate,$duration);
1758 $month++; # to check if following ones are to be skipped too
1761 $resultdate=DATE_Add_Duration($planneddate,$duration);
1763 if ($subscription->{periodicity} == 6) {
1764 my $duration=get_duration("2 months");
1765 for(my $i = 0;$i < @irreg; $i++){
1768 if($month == 12) { $month = 0; } # need to rollover to check January
1769 if($irreg[$i] == ($month+1)){ # check next one to see if is to be skipped
1770 $planneddate = DATE_Add_Duration($planneddate,$duration);
1771 $month++; # to check if following ones are to be skipped too
1774 $resultdate=DATE_Add_Duration($planneddate,$duration);
1776 if ($subscription->{periodicity} == 7 || $subscription->{periodicity} == 8 ) {
1777 my $duration=get_duration("3 months");
1778 for(my $i = 0;$i < @irreg; $i++){
1781 if($month == 12) { $month = 0; } # need to rollover to check January
1782 if($irreg[$i] == ($month+1)){ # check next one to see if is to be skipped
1783 $planneddate = DATE_Add_Duration($planneddate,$duration);
1784 $month++; # to check if following ones are to be skipped too
1787 $resultdate=DATE_Add_Duration($planneddate,$duration);
1790 if ($subscription->{periodicity} == 9) {
1791 my $duration=get_duration("6 months");
1792 for(my $i = 0;$i < @irreg; $i++){
1795 if($month == 12) { $month = 0; } # need to rollover to check January
1796 if($irreg[$i] == ($month+1)){ # check next one to see if is to be skipped
1797 $planneddate = DATE_Add_Duration($planneddate,$duration);
1798 $month++; # to check if following ones are to be skipped too
1801 $resultdate=DATE_Add_Duration($planneddate,$duration);
1803 if ($subscription->{periodicity} == 10) {
1804 my $duration=get_duration("1 years");
1805 $resultdate=DATE_Add_Duration($planneddate,$duration);
1807 if ($subscription->{periodicity} == 11) {
1808 my $duration=get_duration("2 years");
1809 $resultdate=DATE_Add_Duration($planneddate,$duration);
1811 # warn "date: ".$resultdate;
1817 END { } # module clean-up code here (global destructor)