# 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;
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);
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'}."',\"\")";
- }
+# 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";
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 &&
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 = ?)
- |;
+ 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))." )";
+
if (C4::Context->preference('IndependantBranches') &&
C4::Context->userenv &&
C4::Context->userenv->{'flags'} != 1){
}
$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 &&
}
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 ?
- );
+ 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) );
+
+ warn $query;
if (C4::Context->preference('IndependantBranches') &&
C4::Context->userenv &&
C4::Context->userenv->{'flags'} != 1){
}
$query.=" ORDER BY title";
$sth = $dbh->prepare($query);
- $sth->execute( "%" . $title . "%" );
+ $sth->execute;
}
}
}
"SELECT serialid,serialseq, status, publisheddate, planneddate,notes
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 ) {
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);
$newlastvalue3=$val->{setto3} if ($newlastvalue3>$val->{whenmorethan3}); # reset counter if needed.
$calculated =~ s/\{Z\}/$newlastvalue3/g;
- return ( $calculated, $newlastvalue1, $newlastvalue2, $newlastvalue3 );
+ 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);
-
-# warn "dateCHECKRESERV :".$subscription->{startdate};
if ($subscription->{periodicity}){
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} ){
$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;
} else {
return 0;
# change status & update subscriptionhistory
my $val;
if ( $status eq 6 ) {
- DelIssue( $serialseq, $subscriptionid );
+ DelIssue( {'serialid'=>$serialid, 'subscriptionid'=>$subscriptionid,'serialseq'=>$serialseq} );
}
else {
my $query =
my $subscription = GetSubscription($subscriptionid);
my $query = qq|
SELECT *
- FROM biblio,biblioitems
- WHERE biblio.biblionumber=biblioitems.biblionumber
- AND biblio.biblionumber=?
+ FROM biblio
+ LEFT JOIN biblioitems ON biblio.biblionumber=biblioitems.biblionumber
+ WHERE biblio.biblionumber=?
|;
my $sth = $dbh->prepare($query);
$sth->execute( $subscription->{biblionumber} );
=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;
}
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);
"select max(planneddate) from serial where subscriptionid=?");
$sth->execute($subscriptionid);
my ($res) = $sth->fetchrow ;
-# warn "date expiration : ".$expirationdate." date courante ".$res;
+# 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 $per = $subscription->{'periodicity'};
my $x;
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;
projects / koha.git / blobdiff