From 44f9047e1eeb23b83953fb8e601e96f44575c1d1 Mon Sep 17 00:00:00 2001 From: Fridolin Somers Date: Tue, 29 Nov 2016 15:14:31 +0100 Subject: [PATCH] Bug 17703 - Add ISSN column in serials claims table - fix Bug 11861 added ISSN column in serials claims table and a SQL query in C4::Serials::GetLateOrMissingIssues(). But this SQL query is in a if $supplierid condition, the same change must be done if $serialid to allow ISSN in CVS export of claims. This patch rewrites the method to have a common SQL query with conditions only on WHERE. Removes the order input arg since it is never used, order in TT is managed by DataTable and CSV export is generated with the order of selected rows. Test plan: - 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" => With patch you have ISSN value - Run UT t/db_dependent/Serials/Claims.t --- C4/Serials.pm | 92 +++++++++++++++++++++++++++++------------------------------ 1 file changed, 46 insertions(+), 46 deletions(-) diff --git a/C4/Serials.pm b/C4/Serials.pm index b4897c2..5aeb162 100644 --- a/C4/Serials.pm +++ b/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 ) { -- 2.7.4