From ee989384bd78b4abade524cc6d84295821bbb1c9 Mon Sep 17 00:00:00 2001
From: Alex Buckley <alexbuckley@catalyst.net.nz>
Date: Thu, 2 May 2024 23:40:09 +0000
Subject: [PATCH] Bug 36770: Export records using a report

Enable the export_records.pl script use a report output to export biblios or authorities

Test plan:
1. Apply patches and restart services
2. Create a SQL report (id=1)
    SELECT biblionumber
    FROM biblio
3. Create a SQL report (id=2) and set an item as notforloan = -1
    SELECT title, author, biblio.biblionumber
    FROM biblio
    LEFT JOIN items USING (biblionumber)
    WHERE items.notforloan = <<Not for loan|NOT_LOAN>>
4. Create a SQL report (id=3)
    SELECT title, author
    FROM biblio
5. Create a SQL report (id=4)
    SELECT authid
    FROM auth_header
6. Run export_records.pl using report id=1 and confirm a koha.mrc file is created in the misc directory:
cd misc
./export_records.pl --report_id=1

7. Delete the koha.mrc file

8. Run export_records.pl using report id=2
./export_records.pl --report_id=2

9. Notice you are prompted to supply a parameter

10. Re-run report id=2 supplying a parameter. Confirm the koha.mrc file is created and contains bib data
./export_records.pl --report_id=2 --report_param=-1

11. Run export_records.pl using report id=3
./export_records.pl --report_id=3

12. Notice you get the message: The --report_id you specified does not fetch a biblionumber

13. Delete the koha.mrc file

14. Run export_records.pl using report id=4
./export_records.pl --report_id=4

15. Notice you get a message 'The --report_id you specified does not fetch a biblionumber'

16. Re-run export_records.pl setting the record-type=auths
./export_records.pl --record-type=auths --report_id=4

17. Notice the koha.mrc file is generated and contains auth data

Sponsored-by: Horowhenua Libraries, Toi Ohomai Institute of Technology, Plant and Food Research Limited, Waitaki District Council, South Taranaki District Council New Zealand

Signed-off-by: alexandre <alexandre.noel@inlibro.com>

Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>
---
 misc/export_records.pl | 81 ++++++++++++++++++++++++++++++++++++++++--
 1 file changed, 78 insertions(+), 3 deletions(-)

diff --git a/misc/export_records.pl b/misc/export_records.pl
index 1ec3bd8d0f4..0066d858627 100755
--- a/misc/export_records.pl
+++ b/misc/export_records.pl
@@ -26,12 +26,14 @@ use Koha::Script;
 use C4::Auth;
 use C4::Context;
 use C4::Record;
+use C4::Reports::Guided qw( execute_query );
 
 use Koha::Biblioitems;
 use Koha::Database;
 use Koha::CsvProfiles;
 use Koha::Exporter::Record;
 use Koha::DateUtils qw( dt_from_string output_pref );
+use Koha::Reports;
 
 my (
     $output_format,
@@ -55,6 +57,11 @@ my (
     $end_accession,
     $marc_conditions,
     $embed_see_from_headings,
+    $report_id,
+    @report_params,
+    $report,
+    $sql,
+    $params_needed,
     $help
 );
 
@@ -80,6 +87,8 @@ GetOptions(
     'end_accession=s'         => \$end_accession,
     'marc_conditions=s'       => \$marc_conditions,
     'embed_see_from_headings' => \$embed_see_from_headings,
+    'report_id=s'             => \$report_id,
+    'report_param=s'          => \@report_params,
     'h|help|?'                => \$help
 ) || pod2usage(1);
 
@@ -102,7 +111,6 @@ if ( $output_format eq 'csv' and not $csv_profile_id ) {
     pod2usage(q|Define a csv profile to export in CSV|);
 }
 
-
 if ( $record_type ne 'bibs' and $record_type ne 'auths' ) {
     pod2usage(q|--record_type is not valid|);
 }
@@ -111,6 +119,27 @@ if ( $deleted_barcodes and $record_type ne 'bibs' ) {
     pod2usage(q|--deleted_barcodes can only be used with biblios|);
 }
 
+if ( $report_id ) {
+
+    # Check report exists
+    $report = Koha::Reports->find( $report_id );
+    unless ( $report ) {
+        pod2usage( sprintf( "No saved report (%s) found", $report_id ) );
+    }
+    $sql = $report->savedsql;
+
+    # Check defined report can be used to export the record_type
+    if ( $sql !~ /biblionumber/ && $record_type eq 'bibs' ) {
+        pod2usage(q|The --report_id you specified does not fetch a biblionumber|);
+    } elsif ( $sql !~ /authid/ && $record_type eq 'auths' ) {
+        pod2usage(q|The --report_id you specified does not fetch an authid|);
+    }
+
+    # convert SQL parameters to placeholders
+    my $params_needed = ( $sql =~ s/(<<[^>]+>>)/\?/g );
+    die("You supplied ". scalar @report_params . " parameter(s) and $params_needed are required by the report") if scalar @report_params != $params_needed;
+}
+
 $start_accession = dt_from_string( $start_accession ) if $start_accession;
 $end_accession   = dt_from_string( $end_accession )   if $end_accession;
 
@@ -141,7 +170,23 @@ my @record_ids;
 $timestamp = ($timestamp) ? output_pref({ dt => dt_from_string($timestamp), dateformat => 'iso', dateonly => 0, }): '';
 
 if ( $record_type eq 'bibs' ) {
-    if ( $timestamp ) {
+    if ( $report ) {
+        # Run the report and fetch biblionumbers
+        my ($sth) = execute_query(
+            {
+                sql        => $sql,
+                sql_params => \@report_params,
+                report_id  => $report_id,
+            }
+        );
+        while ( my $row = $sth->fetchrow_hashref() ) {
+            if ( $row->{biblionumber} ) {
+                push @record_ids, $row->{biblionumber};
+            } else {
+                pod2usage(q|The --report_id you specified returned no biblionumbers|);
+            }
+        }
+    } elsif ( $timestamp ) {
         if (!$dont_export_items) {
             push @record_ids, $_->{biblionumber} for @{
                 $dbh->selectall_arrayref(q| (
@@ -209,7 +254,23 @@ if ( $record_type eq 'bibs' ) {
     }
 }
 elsif ( $record_type eq 'auths' ) {
-    if ($timestamp) {
+    if ( $report ) {
+        # Run the report and fetch authids
+        my ($sth) = execute_query(
+            {
+                sql        => $sql,
+                sql_params => \@report_params,
+                report_id  => $report_id,
+            }
+        );
+        while ( my $row = $sth->fetchrow_hashref() ) {
+            if ( $row->{authid} ) {
+                push @record_ids, $row->{authid};
+            } else {
+                pod2usage(q|The --report_id you specified returned no authids|);
+            }
+        }
+    } elsif ($timestamp) {
         push @record_ids, $_->{authid} for @{
             $dbh->selectall_arrayref(
                 q| (
@@ -402,6 +463,20 @@ Print a brief help message.
 
  --embed_see_from_headings      Embed see from (non-preferred form) headings in bibliographic record.
 
+=item B<--report_id>
+
+--report_id=ID                  Export biblionumbers or authids from a given saved report output.
+                                If you want to export authority records then your report must
+                                select authid and you must define --record-type=auths when
+                                running this script.
+
+=item B<--report_param>
+
+--report_param=PARAM            Repeatable, should provide one param per param requested for the
+                                report.
+                                Report params are not combined as on the staff side, so you may
+                                need to repeat params.
+
 =back
 
 =head1 AUTHOR
-- 
2.30.2