@@ -, +, @@ - Make sure you have a subscription with some late or missing issues - Go to Tool > CSV profils - Create a profile : Profile name = Issues to claim Profile type = SQL Usage = Late serial issues claims Profile SQL fields = SUPPLIER=aqbooksellers.name|TITLE=subscription.title|ISSN=subscription.issn|ISSUE NUMBER=serial.serialseq|LATE SINCE=serial.planneddate - Go to serials claims page : /cgi-bin/koha/serials/claims.pl - Choose a vendor and click OK - You see serials to claim, with ISSN column filled with subscription's ISSN - Check all lines - Select "Issues to claim" CSV profile - Click on "Download selected claims" - Run UT t/db_dependent/Serials/Claims.t --- C4/Serials.pm | 92 +++++++++++++++++++++++++++++------------------------------ 1 file changed, 46 insertions(+), 46 deletions(-) --- a/C4/Serials.pm +++ a/C4/Serials.pm @@ -1812,64 +1812,64 @@ this function selects missing issues on database - where serial.status = MISSING return : the issuelist as an array of hash refs. Each element of this array contains -name,title,planneddate,serialseq,serial.subscriptionid from tables : subscription, serial & biblio +name,title,issn,planneddate,serialseq,serial.subscriptionid from tables : subscription, serial & biblio =cut sub GetLateOrMissingIssues { - my ( $supplierid, $serialid, $order ) = @_; + my ( $supplierid, $serialid ) = @_; return unless ( $supplierid or $serialid ); my $dbh = C4::Context->dbh; - my $sth; - my $byserial = ''; - if ($serialid) { - $byserial = "and serialid = " . $serialid; - } - if ($order) { - $order .= ", title"; - } else { - $order = "title"; - } my $missing_statuses_string = join ',', (MISSING_STATUSES); + my @params = (EXPECTED, LATE, CLAIMED); + + my $query = qq{ + SELECT + serial.serialid, + serial.planneddate, + serial.serialseq, + serial.status, + serial.subscriptionid, + serial.claimdate, + serial.claims_count, + subscription.branchcode, + subscription.aqbooksellerid, + biblio.title, + biblioitems.issn, + aqbooksellers.name + FROM serial + LEFT JOIN subscription ON serial.subscriptionid = subscription.subscriptionid + LEFT JOIN biblio ON subscription.biblionumber = biblio.biblionumber + LEFT JOIN biblioitems ON subscription.biblionumber = biblioitems.biblionumber + LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id + WHERE subscription.subscriptionid = serial.subscriptionid + AND ( + serial.STATUS IN ($missing_statuses_string) + OR ((serial.planneddate < now() AND serial.STATUS = ?) OR serial.STATUS = ? OR serial.STATUS = ?) + ) + }; if ($supplierid) { - $sth = $dbh->prepare( - "SELECT - serialid, aqbooksellerid, name, - biblio.title, biblioitems.issn, planneddate, serialseq, - serial.status, serial.subscriptionid, claimdate, claims_count, - subscription.branchcode - FROM serial - LEFT JOIN subscription ON serial.subscriptionid=subscription.subscriptionid - LEFT JOIN biblio ON subscription.biblionumber=biblio.biblionumber - LEFT JOIN biblioitems ON subscription.biblionumber=biblioitems.biblionumber - LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id - WHERE subscription.subscriptionid = serial.subscriptionid - AND (serial.STATUS IN ($missing_statuses_string) OR ((planneddate < now() AND serial.STATUS = ?) OR serial.STATUS = ? OR serial.STATUS = ?)) - AND subscription.aqbooksellerid=$supplierid - $byserial - ORDER BY $order" - ); - } else { - $sth = $dbh->prepare( - "SELECT - serialid, aqbooksellerid, name, - biblio.title, planneddate, serialseq, - serial.status, serial.subscriptionid, claimdate, claims_count, - subscription.branchcode - FROM serial - LEFT JOIN subscription ON serial.subscriptionid=subscription.subscriptionid - LEFT JOIN biblio ON subscription.biblionumber=biblio.biblionumber - LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id - WHERE subscription.subscriptionid = serial.subscriptionid - AND (serial.STATUS IN ($missing_statuses_string) OR ((planneddate < now() AND serial.STATUS = ?) OR serial.STATUS = ? OR serial.STATUS = ?)) - $byserial - ORDER BY $order" - ); + $query .= qq{ + AND subscription.aqbooksellerid = ? + }; + push @params, $supplierid; + } + if ($serialid) { + $query .= qq{ + AND serial.serialid = ? + }; + push @params, $serialid; } - $sth->execute( EXPECTED, LATE, CLAIMED ); + $query .= q{ + ORDER BY biblio.title + }; + + my $sth = $dbh->prepare($query); + $sth->execute(@params); + my @issuelist; while ( my $line = $sth->fetchrow_hashref ) { --