# Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
# Suite 330, Boston, MA 02111-1307 USA
-# $Id$
use strict;
use C4::Date;
my $dbh = C4::Context->dbh;
my $query = qq|
SELECT DISTINCT id, name
- FROM subscription, serial
+ FROM subscription
+ LEFT JOIN serial ON serial.subscriptionid=subscription.subscriptionid
LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id
WHERE subscription.subscriptionid = serial.subscriptionid
AND (planneddate < now() OR serial.STATUS = 3 OR serial.STATUS = 4)
+ ORDER BY name
|;
my $sth = $dbh->prepare($query);
$sth->execute;
if ($supplierid) {
my $query = qq|
SELECT name,title,planneddate,serialseq,serial.subscriptionid
- FROM subscription, serial, biblio
+ FROM subscription
+ LEFT JOIN serial ON subscription.subscriptionid = serial.subscriptionid
+ LEFT JOIN biblio ON biblio.biblionumber = subscription.biblionumber
LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id
- WHERE subscription.subscriptionid = serial.subscriptionid
- AND ((planneddate < now() AND serial.STATUS =1) OR serial.STATUS = 3)
+ WHERE ((planneddate < now() AND serial.STATUS =1) OR serial.STATUS = 3)
AND subscription.aqbooksellerid=$supplierid
- AND biblio.biblionumber = subscription.biblionumber
ORDER BY title
|;
$sth = $dbh->prepare($query);
else {
my $query = qq|
SELECT name,title,planneddate,serialseq,serial.subscriptionid
- FROM subscription, serial, biblio
+ FROM subscription
+ LEFT JOIN serial ON subscription.subscriptionid = serial.subscriptionid
+ LEFT JOIN biblio ON biblio.biblionumber = subscription.biblionumber
LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id
- WHERE subscription.subscriptionid = serial.subscriptionid
- AND ((planneddate < now() AND serial.STATUS =1) OR serial.STATUS = 3)
- AND biblio.biblionumber = subscription.biblionumber
+ WHERE ((planneddate < now() AND serial.STATUS =1) OR serial.STATUS = 3)
ORDER BY title
|;
$sth = $dbh->prepare($query);
my ($serialid) = @_;
my $dbh = C4::Context->dbh;
my $query = qq|
- SELECT serial.*, serial.notes as sernotes, serial.status as serstatus,subscription.*,subscription.subscriptionid as subsid
+ SELECT serial.*, serial.notes as sernotes, serial.status as serstatus,subscription.*,subscription.subscriptionid as subsid|;
+ if (C4::Context->preference('IndependantBranches') &&
+ C4::Context->userenv &&
+ C4::Context->userenv->{'flags'} != 1 && C4::Context->userenv->{'branch'}){
+ $query.="
+ , ((subscription.branchcode <>\"".C4::Context->userenv->{'branch'}."\") and subscription.branchcode <>\"\" and subscription.branchcode IS NOT NULL) as cannotedit ";
+ }
+ $query .= qq|
FROM serial LEFT JOIN subscription ON subscription.subscriptionid=serial.subscriptionid
WHERE serialid = ?
|;
return $data;
}
-=head2 GetSerialInformation
+=head2 AddItem2Serial
=over 4
aqbudget.bookfundid,
aqbooksellers.name AS aqbooksellername,
biblio.title AS bibliotitle,
- subscription.biblionumber as bibnum
+ subscription.biblionumber as bibnum);
+ if (C4::Context->preference('IndependantBranches') &&
+ C4::Context->userenv &&
+ C4::Context->userenv->{'flags'} != 1 && C4::Context->userenv->{'branch'}){
+ $query.="
+ , ((subscription.branchcode <>\"".C4::Context->userenv->{'branch'}."\") and subscription.branchcode <>\"\" and subscription.branchcode IS NOT NULL) as cannotedit ";
+ }
+ $query .= qq(
FROM subscription
LEFT JOIN subscriptionhistory ON subscription.subscriptionid=subscriptionhistory.subscriptionid
LEFT JOIN aqbudget ON subscription.aqbudgetid=aqbudget.aqbudgetid
LEFT JOIN biblio ON biblio.biblionumber=subscription.biblionumber
WHERE subscription.subscriptionid = ?
);
- if (C4::Context->preference('IndependantBranches') &&
- C4::Context->userenv &&
- C4::Context->userenv->{'flags'} != 1){
-# warn "flags: ".C4::Context->userenv->{'flags'};
- $query.=" AND subscription.branchcode IN ('".C4::Context->userenv->{'branch'}."',\"''\")";
- }
-# warn "query : $query";
+# if (C4::Context->preference('IndependantBranches') &&
+# C4::Context->userenv &&
+# C4::Context->userenv->{'flags'} != 1){
+# # warn "flags: ".C4::Context->userenv->{'flags'};
+# $query.=" AND subscription.branchcode IN ('".C4::Context->userenv->{'branch'}."',\"\")";
+# }
+# warn "query : $query";
my $sth = $dbh->prepare($query);
+# warn "subsid :$subscriptionid";
$sth->execute($subscriptionid);
my $subs = $sth->fetchrow_hashref;
return $subs;
aqbudget.bookfundid,aqbooksellers.name as aqbooksellername,
biblio.title as bibliotitle,
subscription.branchcode AS branchcode,
- subscription.subscriptionid AS subscriptionid
+ subscription.subscriptionid AS subscriptionid |;
+ if (C4::Context->preference('IndependantBranches') &&
+ C4::Context->userenv &&
+ C4::Context->userenv->{'flags'} != 1 && C4::Context->userenv->{'branch'}){
+ $query.="
+ , ((subscription.branchcode <>\"".C4::Context->userenv->{'branch'}."\") and subscription.branchcode <>\"\" and subscription.branchcode IS NOT NULL) as cannotedit ";
+ }
+ $query.=qq|
FROM serial
LEFT JOIN subscription ON
- (serial.subscriptionid=subscription.subscriptionid AND subscription.biblionumber=serial.biblionumber)
+ (serial.subscriptionid=subscription.subscriptionid )
LEFT JOIN aqbudget ON subscription.aqbudgetid=aqbudget.aqbudgetid
LEFT JOIN aqbooksellers on subscription.aqbooksellerid=aqbooksellers.id
LEFT JOIN biblio on biblio.biblionumber=subscription.biblionumber
- WHERE serial.subscriptionid = ? |;
- if (C4::Context->preference('IndependantBranches') &&
- C4::Context->userenv &&
- C4::Context->userenv->{'flags'} != 1){
- $query.="
- AND subscription.branchcode IN ('".C4::Context->userenv->{'branch'}."',\"''\")";
- }
- $query .=qq|
+ WHERE serial.subscriptionid = ?
ORDER BY year DESC,
IF(serial.publisheddate="00-00-0000",serial.planneddate,serial.publisheddate) DESC,
serial.subscriptionid
|;
+# warn $query;
my $sth = $dbh->prepare($query);
$sth->execute($subscriptionid);
my $subs = $sth->fetchall_arrayref({});
'bibliotitle' => $subs->{'bibliotitle'},
'serials' => [$subs],
'first' => $first,
- 'branchcode' => $subs->{'branchcode'},
- 'subscriptionid' => $subs->{'subscriptionid'},
+# 'branchcode' => $subs->{'branchcode'},
+# 'subscriptionid' => $subs->{'subscriptionid'},
};
}
LEFT JOIN branches ON branches.branchcode=subscription.branchcode
WHERE subscription.biblionumber = ?
);
- if (C4::Context->preference('IndependantBranches') &&
- C4::Context->userenv &&
- C4::Context->userenv->{'flags'} != 1){
- $query.=" AND subscription.branchcode IN ('".C4::Context->userenv->{'branch'}."',\"''\")";
- }
+# if (C4::Context->preference('IndependantBranches') &&
+# C4::Context->userenv &&
+# C4::Context->userenv->{'flags'} != 1){
+# $query.=" AND subscription.branchcode IN ('".C4::Context->userenv->{'branch'}."',\"\")";
+# }
my $sth = $dbh->prepare($query);
$sth->execute($biblionumber);
my @res;
$subs->{ "periodicity" . $subs->{periodicity} } = 1;
$subs->{ "numberpattern" . $subs->{numberpattern} } = 1;
$subs->{ "status" . $subs->{'status'} } = 1;
+ $subs->{'cannotedit'}=(C4::Context->preference('IndependantBranches') &&
+ C4::Context->userenv &&
+ C4::Context->userenv->{flags} !=1 &&
+ C4::Context->userenv->{branch} && $subs->{branchcode} &&
+ (C4::Context->userenv->{branch} ne $subs->{branchcode}));
if ( $subs->{enddate} eq '0000-00-00' ) {
$subs->{enddate} = '';
}
aqbudget.bookfundid,aqbooksellers.name as aqbooksellername,
biblio.title as bibliotitle,
subscription.branchcode AS branchcode,
- subscription.subscriptionid AS subscriptionid
+ subscription.subscriptionid AS subscriptionid|;
+ if (C4::Context->preference('IndependantBranches') &&
+ C4::Context->userenv &&
+ C4::Context->userenv->{'flags'} != 1 && C4::Context->userenv->{'branch'}){
+ $query.="
+ , ((subscription.branchcode <>\"".C4::Context->userenv->{'branch'}."\") and subscription.branchcode <>\"\" and subscription.branchcode IS NOT NULL) as cannotedit ";
+ }
+
+ $query.=qq|
FROM serial
LEFT JOIN subscription ON
- (serial.subscriptionid=subscription.subscriptionid AND subscription.biblionumber=serial.biblionumber)
+ (serial.subscriptionid=subscription.subscriptionid)
LEFT JOIN aqbudget ON subscription.aqbudgetid=aqbudget.aqbudgetid
LEFT JOIN aqbooksellers on subscription.aqbooksellerid=aqbooksellers.id
LEFT JOIN biblio on biblio.biblionumber=subscription.biblionumber
- WHERE subscription.biblionumber = ? |;
- if (C4::Context->preference('IndependantBranches') &&
- C4::Context->userenv &&
- C4::Context->userenv->{'flags'} != 1){
- $query.="
- AND subscription.branchcode IN ('".C4::Context->userenv->{'branch'}."',\"''\")";
- }
- $query .=qq|
+ WHERE subscription.biblionumber = ?
ORDER BY year DESC,
IF(serial.publisheddate="00-00-0000",serial.planneddate,serial.publisheddate) DESC,
serial.subscriptionid
my $sth;
if ($biblionumber) {
my $query = qq(
- SELECT subscription.subscriptionid,biblio.title,biblioitems.issn,subscription.notes,subscription.branchcode,biblio.biblionumber
- FROM subscription,biblio,biblioitems
- WHERE biblio.biblionumber = biblioitems.biblionumber
- AND biblio.biblionumber = subscription.biblionumber
- AND biblio.biblionumber=?
+ SELECT subscription.*,biblio.title,biblioitems.issn,biblio.biblionumber
+ FROM subscription
+ LEFT JOIN biblio ON biblio.biblionumber = subscription.biblionumber
+ LEFT JOIN biblioitems ON biblio.biblionumber = biblioitems.biblionumber
+ WHERE biblio.biblionumber=?
);
- if (C4::Context->preference('IndependantBranches') &&
- C4::Context->userenv &&
- C4::Context->userenv->{'flags'} != 1){
- $query.=" AND subscription.branchcode IN ('".C4::Context->userenv->{'branch'}."',\"''\")";
- }
$query.=" ORDER BY title";
# warn "query :$query";
$sth = $dbh->prepare($query);
else {
if ( $ISSN and $title ) {
my $query = qq|
- SELECT subscription.subscriptionid,biblio.title,biblioitems.issn,subscription.notes,subscription.branchcode,biblio.biblionumber
- FROM subscription,biblio,biblioitems
- WHERE biblio.biblionumber = biblioitems.biblionumber
- AND biblio.biblionumber= subscription.biblionumber
- AND (biblio.title LIKE ? or biblioitems.issn = ?)
- |;
- if (C4::Context->preference('IndependantBranches') &&
- C4::Context->userenv &&
- C4::Context->userenv->{'flags'} != 1){
- $query.=" AND subscription.branchcode IN ('".C4::Context->userenv->{'branch'}."',\"''\")";
- }
+ SELECT subscription.*,biblio.title,biblioitems.issn,biblio.biblionumber
+ FROM subscription
+ LEFT JOIN biblio ON biblio.biblionumber = subscription.biblionumber
+ LEFT JOIN biblioitems ON biblio.biblionumber = biblioitems.biblionumber
+ WHERE (biblioitems.issn = ? or|. join('and ',map{"biblio.title LIKE \"%$_%\""}split (" ",$title))." )";
$query.=" ORDER BY title";
$sth = $dbh->prepare($query);
- $sth->execute( "%$title%", $ISSN );
+ $sth->execute( $ISSN );
}
else {
if ($ISSN) {
my $query = qq(
- SELECT subscription.subscriptionid,biblio.title,biblioitems.issn,subscription.notes,subscription.branchcode,biblio.biblionumber
- FROM subscription,biblio,biblioitems
- WHERE biblio.biblionumber = biblioitems.biblionumber
- AND biblio.biblionumber=subscription.biblionumber
- AND biblioitems.issn LIKE ?
+ SELECT subscription.*,biblio.title,biblioitems.issn,biblio.biblionumber
+ FROM subscription
+ LEFT JOIN biblio ON biblio.biblionumber = subscription.biblionumber
+ LEFT JOIN biblioitems ON biblio.biblionumber = biblioitems.biblionumber
+ WHERE biblioitems.issn LIKE ?
);
- if (C4::Context->preference('IndependantBranches') &&
- C4::Context->userenv &&
- C4::Context->userenv->{'flags'} != 1){
- $query.=" AND subscription.branchcode IN ('".C4::Context->userenv->{'branch'}."',\"''\")";
- }
$query.=" ORDER BY title";
# warn "query :$query";
$sth = $dbh->prepare($query);
}
else {
my $query = qq(
- SELECT subscription.subscriptionid,biblio.title,biblioitems.issn,subscription.notes,subscription.branchcode,biblio.biblionumber
- FROM subscription,biblio,biblioitems
- WHERE biblio.biblionumber = biblioitems.biblionumber
- AND biblio.biblionumber=subscription.biblionumber
- AND biblio.title LIKE ?
- );
- if (C4::Context->preference('IndependantBranches') &&
- C4::Context->userenv &&
- C4::Context->userenv->{'flags'} != 1){
- $query.=" AND subscription.branchcode IN ('".C4::Context->userenv->{'branch'}."',\"''\")";
- }
+ SELECT subscription.*,biblio.title,biblioitems.issn,biblio.biblionumber
+ FROM subscription
+ LEFT JOIN biblio ON biblio.biblionumber = subscription.biblionumber
+ LEFT JOIN biblioitems ON biblio.biblionumber = biblioitems.biblionumber
+ WHERE 1
+ ).($title?" and ":""). join('and ',map{"biblio.title LIKE \"%$_%\""} split (" ",$title) );
+
$query.=" ORDER BY title";
+# warn $query;
$sth = $dbh->prepare($query);
- $sth->execute( "%" . $title . "%" );
+ $sth->execute;
}
}
}
$odd = -$odd;
$line->{toggle} = 1 if $odd == 1;
}
+ $line->{'cannotedit'}=(C4::Context->preference('IndependantBranches') &&
+ C4::Context->userenv &&
+ C4::Context->userenv->{flags} !=1 &&
+ C4::Context->userenv->{branch} && $line->{branchcode} &&
+ (C4::Context->userenv->{branch} ne $line->{branchcode}));
push @results, $line;
}
return @results;
$count=5 unless ($count);
my @serials;
my $query =
- "SELECT serialid,serialseq, status, publisheddate, planneddate,notes
+ "SELECT serialid,serialseq, status, publisheddate, planneddate,notes, routingnotes
FROM serial
WHERE subscriptionid = ? AND status NOT IN (2,4,5)
- ORDER BY publisheddate,serialid DESC";
+ ORDER BY IF(publisheddate<>'0000-00-00',publisheddate,planneddate) DESC";
my $sth = $dbh->prepare($query);
$sth->execute($subscriptionid);
while ( my $line = $sth->fetchrow_hashref ) {
}
# OK, now add the last 5 issues arrives/missing
$query =
- "SELECT serialid,serialseq, status, planneddate, publisheddate,notes
+ "SELECT serialid,serialseq, status, planneddate, publisheddate,notes, routingnotes
FROM serial
WHERE subscriptionid = ?
AND (status in (2,4,5))
- ORDER BY publisheddate,serialid DESC
+ ORDER BY IF(publisheddate<>'0000-00-00',publisheddate,planneddate) DESC
";
$sth = $dbh->prepare($query);
$sth->execute($subscriptionid);
my ($subscription,$status) = @_;
my $dbh = C4::Context->dbh;
my $query = qq|
- SELECT serialid,serialseq, status, planneddate, publisheddate,notes
+ SELECT serialid,serialseq, status, planneddate, publisheddate,notes, routingnotes
FROM serial
- WHERE subscriptionid=$subscription AND status=$status
+ WHERE subscriptionid=$subscription AND status IN ($status)
ORDER BY publisheddate,serialid DESC
|;
# warn $query;
$newlastvalue1 = $val->{lastvalue1};
$newlastvalue2 = $val->{lastvalue2};
$newlastvalue3 = $val->{lastvalue3};
-
- if ( $newlastvalue3 > 0 ) { # if x y and z columns are used
- $newlastvalue3 = $newlastvalue3 + 1;
- if ( $newlastvalue3 > $val->{whenmorethan3} ) {
- $newlastvalue3 = $val->{setto3};
- $newlastvalue2++;
- if ( $newlastvalue2 > $val->{whenmorethan2} ) {
- $newlastvalue1++;
- $newlastvalue2 = $val->{setto2};
- }
- }
- $calculated =~ s/\{X\}/$newlastvalue1/g;
- if ( $pattern == 6 ) {
- if ( $val->{hemisphere} == 2 ) {
- my $newlastvalue2seq = $southern_seasons[$newlastvalue2];
- $calculated =~ s/\{Y\}/$newlastvalue2seq/g;
- }
- else {
- my $newlastvalue2seq = $seasons[$newlastvalue2];
- $calculated =~ s/\{Y\}/$newlastvalue2seq/g;
- }
- }
- else {
- $calculated =~ s/\{Y\}/$newlastvalue2/g;
- }
- $calculated =~ s/\{Z\}/$newlastvalue3/g;
- }
- if ( $newlastvalue2 > 0 && $newlastvalue3 < 1 )
- { # if x and y columns are used
- $newlastvalue2 = $newlastvalue2 + 1;
- if ( $newlastvalue2 > $val->{whenmorethan2} ) {
- $newlastvalue2 = $val->{setto2};
- $newlastvalue1++;
- }
- $calculated =~ s/\{X\}/$newlastvalue1/g;
- if ( $pattern == 6 ) {
- if ( $val->{hemisphere} == 2 ) {
- my $newlastvalue2seq = $southern_seasons[$newlastvalue2];
- $calculated =~ s/\{Y\}/$newlastvalue2seq/g;
- }
- else {
- my $newlastvalue2seq = $seasons[$newlastvalue2];
- $calculated =~ s/\{Y\}/$newlastvalue2seq/g;
- }
- }
- else {
- $calculated =~ s/\{Y\}/$newlastvalue2/g;
- }
+ $newlastvalue1 = $val->{lastvalue1};
+ # check if we have to increase the new value.
+ $newinnerloop1 = $val->{innerloop1} + 1;
+ $newinnerloop1=0 if ($newinnerloop1 >= $val->{every1});
+ $newlastvalue1 += $val->{add1} if ($newinnerloop1<1); # <1 to be true when 0 or empty.
+ $newlastvalue1=$val->{setto1} if ($newlastvalue1>$val->{whenmorethan1}); # reset counter if needed.
+ $calculated =~ s/\{X\}/$newlastvalue1/g;
+
+ $newlastvalue2 = $val->{lastvalue2};
+ # check if we have to increase the new value.
+ $newinnerloop2 = $val->{innerloop2} + 1;
+ $newinnerloop2=0 if ($newinnerloop2 >= $val->{every2});
+ $newlastvalue2 += $val->{add2} if ($newinnerloop2<1); # <1 to be true when 0 or empty.
+ $newlastvalue2=$val->{setto2} if ($newlastvalue2>$val->{whenmorethan2}); # reset counter if needed.
+ if ( $pattern == 6 ) {
+ if ( $val->{hemisphere} == 2 ) {
+ my $newlastvalue2seq = $southern_seasons[$newlastvalue2];
+ $calculated =~ s/\{Y\}/$newlastvalue2seq/g;
}
- if ( $newlastvalue1 > 0 && $newlastvalue2 < 1 && $newlastvalue3 < 1 )
- { # if column x only
- $newlastvalue1 = $newlastvalue1 + 1;
- if ( $newlastvalue1 > $val->{whenmorethan1} ) {
- $newlastvalue1 = $val->{setto2};
- }
- $calculated =~ s/\{X\}/$newlastvalue1/g;
+ else {
+ my $newlastvalue2seq = $seasons[$newlastvalue2];
+ $calculated =~ s/\{Y\}/$newlastvalue2seq/g;
}
- return ( $calculated, $newlastvalue1, $newlastvalue2, $newlastvalue3 );
+ }
+ else {
+ $calculated =~ s/\{Y\}/$newlastvalue2/g;
+ }
+
+
+ $newlastvalue3 = $val->{lastvalue3};
+ # check if we have to increase the new value.
+ $newinnerloop3 = $val->{innerloop3} + 1;
+ $newinnerloop3=0 if ($newinnerloop3 >= $val->{every3});
+ $newlastvalue3 += $val->{add3} if ($newinnerloop3<1); # <1 to be true when 0 or empty.
+ $newlastvalue3=$val->{setto3} if ($newlastvalue3>$val->{whenmorethan3}); # reset counter if needed.
+ $calculated =~ s/\{Z\}/$newlastvalue3/g;
+
+ return ( $calculated, $newlastvalue1, $newlastvalue2, $newlastvalue3 ,
+ $newinnerloop1, $newinnerloop2, $newinnerloop3);
}
=head2 GetSeq
my $enddate = $subscription->{startdate};
# we don't do the same test if the subscription is based on X numbers or on X weeks/months
-# warn "SUBSCRIPTIONID :$subscriptionid";
-# use Data::Dumper; warn Dumper($subscription);
-
- if ( $subscription->{numberlength} ) {
- #calculate the date of the last issue.
- my $length = $subscription->{numberlength};
-# warn "ENDDATE ".$enddate;
- for ( my $i = 1 ; $i <= $length ; $i++ ) {
- $enddate = GetNextDate( $enddate, $subscription );
-# warn "AFTER ENDDATE ".$enddate;
- }
- }
- elsif ( $subscription->{monthlength} ){
-# warn "dateCHECKRESERV :".$subscription->{startdate};
- my @date=split (/-/,$subscription->{startdate});
- my @enddate = Add_Delta_YM($date[0],$date[1],$date[2],0,$subscription->{monthlength});
- $enddate=sprintf("%04d-%02d-%02d",$enddate[0],$enddate[1],$enddate[2]);
- } elsif ( $subscription->{weeklength} ){
- my @date=split (/-/,$subscription->{startdate});
-# warn "dateCHECKRESERV :".$subscription->{startdate};
-#### An other way to do it
-# if ( $subscription->{weeklength} ){
-# my ($weeknb,$year)=Week_of_Year(@startdate);
-# $weeknb += $subscription->{weeklength};
-# my $weeknbcalc= $weeknb % 52;
-# $year += int($weeknb/52);
-# # warn "year : $year weeknb :$weeknb weeknbcalc $weeknbcalc";
-# @endofsubscriptiondate=Monday_of_Week($weeknbcalc,$year);
-# }
- my @enddate = Add_Delta_Days($date[0],$date[1],$date[2],$subscription->{weeklength}*7);
- $enddate=sprintf("%04d-%02d-%02d",$enddate[0],$enddate[1],$enddate[2]);
- }
-# warn "date de fin :$enddate";
- return $enddate;
+ if (($subscription->{periodicity} % 16) >0){
+ if ( $subscription->{numberlength} ) {
+ #calculate the date of the last issue.
+ my $length = $subscription->{numberlength};
+ for ( my $i = 1 ; $i <= $length ; $i++ ) {
+ $enddate = GetNextDate( $enddate, $subscription );
+ }
+ }
+ elsif ( $subscription->{monthlength} ){
+ my @date=split (/-/,$subscription->{startdate});
+ my @enddate = Add_Delta_YM($date[0],$date[1],$date[2],0,$subscription->{monthlength});
+ $enddate=sprintf("%04d-%02d-%02d",$enddate[0],$enddate[1],$enddate[2]);
+ } elsif ( $subscription->{weeklength} ){
+ my @date=split (/-/,$subscription->{startdate});
+ my @enddate = Add_Delta_Days($date[0],$date[1],$date[2],$subscription->{weeklength}*7);
+ $enddate=sprintf("%04d-%02d-%02d",$enddate[0],$enddate[1],$enddate[2]);
+ }
+ return $enddate;
+ } else {
+ return 0;
+ }
}
=head2 CountSubscriptionFromBiblionumber
=over 4
-ModSerialStatus($serialid,$serialseq, $publisheddate,$planneddate,$status,$notes)
+ModSerialStatus($serialid,$serialseq, $planneddate,$publisheddate,$status,$notes)
This function modify the serial status. Serial status is a number.(eg 2 is "arrived")
Note : if we change from "waited" to something else,then we will have to create a new "waited" entry
=cut
sub ModSerialStatus {
- my ( $serialid, $serialseq, $publisheddate, $planneddate, $status, $notes )
+ my ( $serialid, $serialseq, $planneddate,$publisheddate, $status, $notes )
= @_;
#It is a usual serial
# change status & update subscriptionhistory
my $val;
if ( $status eq 6 ) {
- DelIssue( $serialseq, $subscriptionid );
+ DelIssue( {'serialid'=>$serialid, 'subscriptionid'=>$subscriptionid,'serialseq'=>$serialseq} );
}
else {
my $query =
my $val = $sth->fetchrow_hashref;
# next issue number
+# warn "Next Seq";
my (
$newserialseq, $newlastvalue1, $newlastvalue2, $newlastvalue3,
$newinnerloop1, $newinnerloop2, $newinnerloop3
) = GetNextSeq($val);
+# warn "Next Seq End";
# next date (calculated from actual date & frequency parameters)
# warn "publisheddate :$publisheddate ";
$lastvalue3, $innerloop3,
$numberingmethod, "$status",
$notes, $letter,
- $firstacquidate, $irregularity,
+ format_date_in_iso($firstacquidate), $irregularity,
$numberpattern, $callnumber,
$hemisphere, $manualhistory,
$internalnotes
= @_;
my $dbh = C4::Context->dbh;
my $subscription = GetSubscription($subscriptionid);
- my $query = qq|
- SELECT *
- FROM biblio,biblioitems
- WHERE biblio.biblionumber=biblioitems.biblionumber
- AND biblio.biblionumber=?
- |;
- my $sth = $dbh->prepare($query);
- $sth->execute( $subscription->{biblionumber} );
- my $biblio = $sth->fetchrow_hashref;
- NewSuggestion(
- $user, $subscription->{bibliotitle},
- $biblio->{author}, $biblio->{publishercode},
- $biblio->{note}, '',
- '', '',
- '', '',
- $subscription->{biblionumber}
- );
+ my $query = qq|
+ SELECT *
+ FROM biblio
+ LEFT JOIN biblioitems ON biblio.biblionumber=biblioitems.biblionumber
+ WHERE biblio.biblionumber=?
+ |;
+ my $sth = $dbh->prepare($query);
+ $sth->execute( $subscription->{biblionumber} );
+ my $biblio = $sth->fetchrow_hashref;
+ NewSuggestion(
+ $user, $subscription->{bibliotitle},
+ $biblio->{author}, $biblio->{publishercode},
+ $biblio->{note}, '',
+ '', '',
+ '', '',
+ $subscription->{biblionumber}
+ );
# renew subscription
- $query = qq|
+ my $query = qq|
UPDATE subscription
SET startdate=?,numberlength=?,weeklength=?,monthlength=?
WHERE subscriptionid=?
|;
- $sth = $dbh->prepare($query);
+ my $sth = $dbh->prepare($query);
$sth->execute( format_date_in_iso($startdate),
$numberlength, $weeklength, $monthlength, $subscriptionid );
=over 4
-NewIssue($serialseq,$subscriptionid,$biblionumber,$status, $publisheddate, $planneddate)
+NewIssue($serialseq,$subscriptionid,$biblionumber,$status, $planneddate, $publisheddate, $notes)
Create a new issue stored on the database.
Note : we have to update the recievedlist and missinglist on subscriptionhistory for this subscription.
=cut
sub NewIssue {
- my ( $serialseq, $subscriptionid, $biblionumber, $status, $publisheddate,
- $planneddate, $notes )
+ my ( $serialseq, $subscriptionid, $biblionumber, $status,
+ $planneddate, $publisheddate, $notes )
= @_;
### FIXME biblionumber CAN be provided by subscriptionid. So Do we STILL NEED IT ?
my ($subscriptionid) = @_;
my $dbh = C4::Context->dbh;
my $subscription = GetSubscription($subscriptionid);
- my $expirationdate = GetExpirationDate($subscriptionid);
- my $query = qq|
+ if (($subscription->{periodicity} % 16)>0){
+ my $expirationdate = GetExpirationDate($subscriptionid);
+ my $query = qq|
SELECT max(planneddate)
FROM serial
WHERE subscriptionid=?
- |;
- my $sth = $dbh->prepare($query);
- $sth->execute($subscriptionid);
- my ($res) = $sth->fetchrow ;
- my @res=split (/-/,$res);
- my @endofsubscriptiondate=split(/-/,$expirationdate);
- return 1 if ( (@endofsubscriptiondate && Delta_Days($res[0],$res[1],$res[2],
+ |;
+ my $sth = $dbh->prepare($query);
+ $sth->execute($subscriptionid);
+ my ($res) = $sth->fetchrow ;
+ my @res=split (/-/,$res);
+# warn "date expiration :$expirationdate";
+ my @endofsubscriptiondate=split(/-/,$expirationdate);
+ return 1 if ( (@endofsubscriptiondate && Delta_Days($res[0],$res[1],$res[2],
$endofsubscriptiondate[0],$endofsubscriptiondate[1],$endofsubscriptiondate[2]) <= 0)
|| (!$res));
+ return 0;
+ } else {
+ if ($subscription->{'numberlength'}){
+ my $countreceived=countissuesfrom($subscriptionid,$subscription->{'startdate'});
+ return 1 if ($countreceived >$subscription->{'numberlentgh'});
+ return 0;
+ } else {
+ return 0;
+ }
+ }
return 0;
}
=cut
sub DelIssue {
- my ( $serialseq, $subscriptionid ) = @_;
+ my ( $dataissue) = @_;
my $dbh = C4::Context->dbh;
+ ### TODO Add itemdeletion. Would need to get itemnumbers. Should be in a pref ?
+
my $query = qq|
DELETE FROM serial
- WHERE serialseq= ?
+ WHERE serialid= ?
AND subscriptionid= ?
|;
my $mainsth = $dbh->prepare($query);
- $mainsth->execute( $serialseq, $subscriptionid );
+ $mainsth->execute( $dataissue->{'serialid'}, $dataissue->{'subscriptionid'});
#Delete element from subscription history
$query = "SELECT * FROM subscription WHERE subscriptionid = ?";
my $sth = $dbh->prepare($query);
- $sth->execute($subscriptionid);
+ $sth->execute($dataissue->{'subscriptionid'});
my $val = $sth->fetchrow_hashref;
unless ( $val->{manualhistory} ) {
my $query = qq|
WHERE subscriptionid= ?
|;
my $sth = $dbh->prepare($query);
- $sth->execute($subscriptionid);
+ $sth->execute($dataissue->{'subscriptionid'});
my $data = $sth->fetchrow_hashref;
- $data->{'missinglist'} =~ s/$serialseq//;
- $data->{'recievedlist'} =~ s/$serialseq//;
+ my $serialseq= $dataissue->{'serialseq'};
+ $data->{'missinglist'} =~ s/\b$serialseq\b//;
+ $data->{'recievedlist'} =~ s/\b$serialseq\b//;
my $strsth = "UPDATE subscriptionhistory SET "
. join( ",",
map { join( "=", $_, $dbh->quote( $data->{$_} ) ) } keys %$data )
. " WHERE subscriptionid=?";
$sth = $dbh->prepare($strsth);
- $sth->execute($subscriptionid);
+ $sth->execute($dataissue->{'subscriptionid'});
}
- ### TODO Add itemdeletion. Should be in a pref ?
return $mainsth->rows;
}
claimdate
FROM serial
LEFT JOIN subscription ON serial.subscriptionid=subscription.subscriptionid
-LEFT JOIN biblio ON serial.biblionumber=biblio.biblionumber
+LEFT JOIN biblio ON subscription.biblionumber=biblio.biblionumber
LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id
WHERE subscription.subscriptionid = serial.subscriptionid
AND (serial.STATUS = 4 OR ((planneddate < now() AND serial.STATUS =1) OR serial.STATUS = 3))
LEFT JOIN subscription
ON serial.subscriptionid=subscription.subscriptionid
LEFT JOIN biblio
-ON serial.biblionumber=biblio.biblionumber
+ON subscription.biblionumber=biblio.biblionumber
LEFT JOIN aqbooksellers
ON subscription.aqbooksellerid = aqbooksellers.id
WHERE
subscription.subscriptionid = serial.subscriptionid
AND (serial.STATUS = 4 OR ((planneddate < now() AND serial.STATUS =1) OR serial.STATUS = 3))
-AND biblio.biblionumber = subscription.biblionumber
$byserial
ORDER BY $order"
);
my $dbh = C4::Context->dbh;
my $sth = $dbh->prepare(
"SELECT serialid, serial.subscriptionid, aqbooksellerid
- FROM serial, subscription
- WHERE serial.subscriptionid = subscription.subscriptionid
- AND serialid = ?
+ FROM serial
+ LEFT JOIN subscription ON serial.subscriptionid = subscription.subscriptionid
+ WHERE serialid = ?
"
);
$sth->execute($serialid);
my ($subscriptionid) = @_;
my $dbh = C4::Context->dbh;
my $sth = $dbh->prepare(
-"SELECT count(routingid) routingids FROM subscriptionroutinglist, subscription
- WHERE subscription.subscriptionid = subscriptionroutinglist.subscriptionid
- AND subscription.subscriptionid = ? ORDER BY ranking ASC
+"SELECT count(routingid) routingids FROM subscription LEFT JOIN subscriptionroutinglist
+ ON subscription.subscriptionid = subscriptionroutinglist.subscriptionid
+ WHERE subscription.subscriptionid = ? ORDER BY ranking ASC
"
);
$sth->execute($subscriptionid);
my $dbh = C4::Context->dbh;
my $sth = $dbh->prepare(
"SELECT routingid, borrowernumber,
- ranking, biblionumber FROM subscriptionroutinglist, subscription
- WHERE subscription.subscriptionid = subscriptionroutinglist.subscriptionid
- AND subscription.subscriptionid = ? ORDER BY ranking ASC
+ ranking, biblionumber
+ FROM subscription
+ LEFT JOIN subscriptionroutinglist ON subscription.subscriptionid = subscriptionroutinglist.subscriptionid
+ WHERE subscription.subscriptionid = ? ORDER BY ranking ASC
"
);
$sth->execute($subscriptionid);
return ( $count, @routinglist );
}
+=head2 countissuesfrom
+
+=over 4
+
+$result = &countissuesfrom($subscriptionid,$startdate)
+
+
+=back
+
+=cut
+
+sub countissuesfrom {
+ my ($subscriptionid,$startdate) = @_;
+ my $dbh = C4::Context->dbh;
+ my $query = qq|
+ SELECT count(*)
+ FROM serial
+ WHERE subscriptionid=?
+ AND serial.publisheddate>?
+ |;
+ my $sth=$dbh->prepare($query);
+ $sth->execute($subscriptionid, $startdate);
+ my ($countreceived)=$sth->fetchrow;
+ return $countreceived;
+}
+
=head2 abouttoexpire
=over 4
my ($subscriptionid) = @_;
my $dbh = C4::Context->dbh;
my $subscription = GetSubscription($subscriptionid);
- my $expirationdate = GetExpirationDate($subscriptionid);
- my $sth =
- $dbh->prepare(
- "select max(planneddate) from serial where subscriptionid=?");
- $sth->execute($subscriptionid);
- my ($res) = $sth->fetchrow ;
-# warn "date expiration : ".$expirationdate." date courante ".$res;
- my @res=split /-/,$res;
- my @endofsubscriptiondate=split/-/,$expirationdate;
my $per = $subscription->{'periodicity'};
- my $x;
- if ( $per == 1 ) {$x=7;}
- if ( $per == 2 ) {$x=7; }
- if ( $per == 3 ) {$x=14;}
- if ( $per == 4 ) { $x = 21; }
- if ( $per == 5 ) { $x = 31; }
- if ( $per == 6 ) { $x = 62; }
- if ( $per == 7 || $per == 8 ) { $x = 93; }
- if ( $per == 9 ) { $x = 190; }
- if ( $per == 10 ) { $x = 365; }
- if ( $per == 11 ) { $x = 730; }
- my @datebeforeend=Add_Delta_Days( $endofsubscriptiondate[0],$endofsubscriptiondate[1],$endofsubscriptiondate[2],
- - (3 * $x)) if (@endofsubscriptiondate);
- # warn "DATE BEFORE END: $datebeforeend";
- return 1 if ( @res &&
- (@datebeforeend &&
- Delta_Days($res[0],$res[1],$res[2],
- $datebeforeend[0],$datebeforeend[1],$datebeforeend[2]) <= 0) &&
- (@endofsubscriptiondate &&
- Delta_Days($res[0],$res[1],$res[2],
- $endofsubscriptiondate[0],$endofsubscriptiondate[1],$endofsubscriptiondate[2]) >= 0) );
+ if ($per % 16>0){
+ my $expirationdate = GetExpirationDate($subscriptionid);
+ my $sth =
+ $dbh->prepare(
+ "select max(planneddate) from serial where subscriptionid=?");
+ $sth->execute($subscriptionid);
+ my ($res) = $sth->fetchrow ;
+# warn "date expiration : ".$expirationdate." date courante ".$res;
+ my @res=split /-/,$res;
+ @res=Date::Calc::Today if ($res[0]*$res[1]==0);
+ my @endofsubscriptiondate=split/-/,$expirationdate;
+ my $x;
+ if ( $per == 1 ) {$x=7;}
+ if ( $per == 2 ) {$x=7; }
+ if ( $per == 3 ) {$x=14;}
+ if ( $per == 4 ) { $x = 21; }
+ if ( $per == 5 ) { $x = 31; }
+ if ( $per == 6 ) { $x = 62; }
+ if ( $per == 7 || $per == 8 ) { $x = 93; }
+ if ( $per == 9 ) { $x = 190; }
+ if ( $per == 10 ) { $x = 365; }
+ if ( $per == 11 ) { $x = 730; }
+ my @datebeforeend=Add_Delta_Days( $endofsubscriptiondate[0],$endofsubscriptiondate[1],$endofsubscriptiondate[2],
+ - (3 * $x)) if (@endofsubscriptiondate);
+ # warn "DATE BEFORE END: $datebeforeend";
+ return 1 if ( @res &&
+ (@datebeforeend &&
+ Delta_Days($res[0],$res[1],$res[2],
+ $datebeforeend[0],$datebeforeend[1],$datebeforeend[2]) <= 0) &&
+ (@endofsubscriptiondate &&
+ Delta_Days($res[0],$res[1],$res[2],
+ $endofsubscriptiondate[0],$endofsubscriptiondate[1],$endofsubscriptiondate[2]) >= 0) );
return 0;
+ } elsif ($subscription->{numberlength}>0) {
+ return (countissuesfrom($subscriptionid,$subscription->{'startdate'}) >=$subscription->{numberlength}-1);
+ } else {return 0}
}
=head2 old_newsubscription
return :
$resultdate - then next date in the sequence
-FIXME : have to replace Date::Manip by Date::Calc in this function to improve performances.
+Return 0 if periodicity==0
=cut
sub in_array { # used in next sub down
my @resultdate;
# warn "DOW $dayofweek";
+ if ( $subscription->{periodicity} % 16 == 0 ) {
+ return 0;
+ }
if ( $subscription->{periodicity} == 1 ) {
my $dayofweek = Day_of_Week( $year,$month, $day );
for ( my $i = 0 ; $i < @irreg ; $i++ ) {
my ($barcode) = @_;
my $dbh = C4::Context->dbh;
my $sth = $dbh->prepare(
- "Select * from items,biblioitems where barcode=?
- and items.biblioitemnumber=biblioitems.biblioitemnumber"
+ "Select * from items LEFT JOIN biblioitems ON items.biblioitemnumber=biblioitems.biblioitemnumber
+ WHERE barcode=?"
);
$sth->execute($barcode);
my $data = $sth->fetchrow_hashref;