X-Git-Url: http://git.rot13.org/?a=blobdiff_plain;f=C4%2FSerials.pm;h=8731434729909870db35f437994fb758a8cc134e;hb=d6271788fb71662dd3ff0b43e75607d38abb5b79;hp=ed6edc7e09aa76ae7f89ad0a53478a6ff20a1b16;hpb=0a9ee0c59bca2ceed0e6ea9a4493cc3774ab032b;p=koha.git diff --git a/C4/Serials.pm b/C4/Serials.pm index ed6edc7e09..8731434729 100644 --- a/C4/Serials.pm +++ b/C4/Serials.pm @@ -17,7 +17,6 @@ package C4::Serials; #assumes C4/Serials.pm # 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; @@ -103,6 +102,7 @@ sub GetSuppliersWithLateIssues { 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; @@ -139,12 +139,12 @@ sub GetLateIssues { 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); @@ -152,11 +152,11 @@ sub GetLateIssues { 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); @@ -365,12 +365,12 @@ sub GetSubscription { 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"; @@ -623,11 +623,11 @@ sub GetSubscriptions { 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 && @@ -642,12 +642,12 @@ sub GetSubscriptions { 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){ @@ -655,16 +655,16 @@ sub GetSubscriptions { } $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 && @@ -678,12 +678,14 @@ sub GetSubscriptions { } 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){ @@ -691,7 +693,7 @@ sub GetSubscriptions { } $query.=" ORDER BY title"; $sth = $dbh->prepare($query); - $sth->execute( "%" . $title . "%" ); + $sth->execute; } } } @@ -739,7 +741,7 @@ sub GetSerials { "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 ) { @@ -755,7 +757,7 @@ sub GetSerials { 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); @@ -972,7 +974,8 @@ sub GetNextSeq { $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 @@ -1034,18 +1037,12 @@ sub GetExpirationDate { 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} ){ @@ -1054,20 +1051,9 @@ sub GetExpirationDate { $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; @@ -1158,7 +1144,7 @@ sub ModSerialStatus { # change status & update subscriptionhistory my $val; if ( $status eq 6 ) { - DelIssue( $serialseq, $subscriptionid ); + DelIssue( {'serialid'=>$serialid, 'subscriptionid'=>$subscriptionid,'serialseq'=>$serialseq} ); } else { my $query = @@ -1427,9 +1413,9 @@ sub ReNewSubscription { 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} ); @@ -1822,20 +1808,22 @@ this function delete an issue which has $serialseq and $subscriptionid given on =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| @@ -1843,18 +1831,18 @@ sub DelIssue { 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; } @@ -2045,9 +2033,9 @@ sub getsupplierbyserialid { 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); @@ -2072,9 +2060,9 @@ sub check_routing { 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); @@ -2235,9 +2223,10 @@ sub getroutinglist { 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); @@ -2303,8 +2292,9 @@ sub abouttoexpire { "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; @@ -2718,8 +2708,8 @@ sub itemdata { 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;