X-Git-Url: http://git.rot13.org/?a=blobdiff_plain;f=C4%2FSerials.pm;h=30b5deb4e001be6256c583d7b8afd7cf63d6c4f2;hb=231415e5be425546eafec11c7c004dd9cffb5e8a;hp=f21f3be13cc737290e4677126aa1e78280f1511e;hpb=c5999dbbbd1bcfb45f258140c9b1f0041bb848f6;p=koha.git diff --git a/C4/Serials.pm b/C4/Serials.pm index f21f3be13c..30b5deb4e0 100644 --- a/C4/Serials.pm +++ b/C4/Serials.pm @@ -103,6 +103,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 +140,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 +153,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,14 +366,15 @@ 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'}."',\"''\")"; - } -# 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; @@ -525,7 +527,7 @@ sub GetSubscriptionsFromBiblionumber { if (C4::Context->preference('IndependantBranches') && C4::Context->userenv && C4::Context->userenv->{'flags'} != 1){ - $query.=" AND subscription.branchcode IN ('".C4::Context->userenv->{'branch'}."',\"''\")"; + $query.=" AND subscription.branchcode IN ('".C4::Context->userenv->{'branch'}."',\"\")"; } my $sth = $dbh->prepare($query); $sth->execute($biblionumber); @@ -622,11 +624,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 && @@ -641,12 +643,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){ @@ -654,16 +656,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 && @@ -677,12 +679,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){ @@ -690,7 +694,7 @@ sub GetSubscriptions { } $query.=" ORDER BY title"; $sth = $dbh->prepare($query); - $sth->execute( "%" . $title . "%" ); + $sth->execute; } } } @@ -738,7 +742,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 ) { @@ -754,7 +758,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); @@ -792,7 +796,7 @@ sub GetSerials2 { my $query = qq| SELECT serialid,serialseq, status, planneddate, publisheddate,notes FROM serial - WHERE subscriptionid=$subscription AND status=$status + WHERE subscriptionid=$subscription AND status IN ($status) ORDER BY publisheddate,serialid DESC |; # warn $query; @@ -934,63 +938,45 @@ sub GetNextSeq { $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 @@ -1152,7 +1138,7 @@ sub ModSubscriptionHistory { =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 @@ -1162,7 +1148,7 @@ Note : if we change from "waited" to something else,then we will have to create =cut sub ModSerialStatus { - my ( $serialid, $serialseq, $publisheddate, $planneddate, $status, $notes ) + my ( $serialid, $serialseq, $planneddate,$publisheddate, $status, $notes ) = @_; #It is a usual serial @@ -1176,7 +1162,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 = @@ -1223,10 +1209,12 @@ sub ModSerialStatus { 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 "; @@ -1441,31 +1429,31 @@ sub ReNewSubscription { = @_; 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 ); @@ -1477,7 +1465,7 @@ sub ReNewSubscription { =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. @@ -1838,20 +1826,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| @@ -1859,18 +1849,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; } @@ -2061,9 +2051,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); @@ -2088,9 +2078,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); @@ -2251,9 +2241,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); @@ -2319,8 +2310,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; @@ -2734,8 +2726,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;