From 4cc6b14896461cfd099653e72821333c9d23044b Mon Sep 17 00:00:00 2001
From: Kyle M Hall <kyle@bywatersolutions.com>
Date: Fri, 20 Oct 2017 10:49:38 -0400
Subject: [PATCH] Bug 19495 - Automatic report conversion needs to do global
 replace on 'biblioitems' and 'marcxml'

Bug 17898 provides a way of converting reports that use biblioitems.marcxml so that they will use biblio_metadata.metadata instead.

This only works with reports that do not refer to other columns in the biblioitems table. This is a known limitation. It means that we should be able to do a substitution of every occurrence of biblioitems with biblio_metadata, and every occurrence of marcxml with metadata.

Unfortunately, we're not doing a global replace, we're only replacing the first occurrence.

Test Plan:
1) Apply this patch
2) prove t/db_dependent/Reports/Guided.t
---
 C4/Reports/Guided.pm            |  4 ++--
 t/db_dependent/Reports/Guided.t | 13 ++++++++++++-
 2 files changed, 14 insertions(+), 3 deletions(-)

diff --git a/C4/Reports/Guided.pm b/C4/Reports/Guided.pm
index 717d44f..d61b282 100644
--- a/C4/Reports/Guided.pm
+++ b/C4/Reports/Guided.pm
@@ -1001,8 +1001,8 @@ sub convert_sql {
     my ( $sql ) = @_;
     my $updated_sql = $sql;
     if ( $sql =~ m|biblioitems| and $sql =~ m|marcxml| ) {
-        $updated_sql =~ s|biblioitems|biblio_metadata|;
-        $updated_sql =~ s|marcxml|metadata|;
+        $updated_sql =~ s|biblioitems|biblio_metadata|g;
+        $updated_sql =~ s|marcxml|metadata|g;
     }
     return $updated_sql;
 }
diff --git a/t/db_dependent/Reports/Guided.t b/t/db_dependent/Reports/Guided.t
index 0c410dc..0a2c734 100644
--- a/t/db_dependent/Reports/Guided.t
+++ b/t/db_dependent/Reports/Guided.t
@@ -289,7 +289,7 @@ subtest 'Ensure last_run is populated' => sub {
 };
 
 subtest 'convert_sql' => sub {
-    plan tests => 3;
+    plan tests => 4;
 
     my $sql = q|
     SELECT biblionumber, ExtractValue(marcxml,
@@ -341,6 +341,17 @@ count(h.reservedate) AS 'holds'
     GROUP BY b.biblionumber
     HAVING count(h.reservedate) >= 42|;
     is( C4::Reports::Guided::convert_sql( $sql ), $expected_converted_sql, "Query with 2 joins should have been correctly converted");
+
+    $sql = q|
+    SELECT t1.marcxml AS first, t2.marcxml AS second,
+    FROM biblioitems t1
+    LEFT JOIN biblioitems t2 USING ( biblionumber )|;
+
+    $expected_converted_sql = q|
+    SELECT t1.metadata AS first, t2.metadata AS second,
+    FROM biblio_metadata t1
+    LEFT JOIN biblio_metadata t2 USING ( biblionumber )|;
+    is( C4::Reports::Guided::convert_sql( $sql ), $expected_converted_sql, "Query with multiple instances of marcxml and biblioitems should have them all replaced");
 };
 
 $schema->storage->txn_rollback;
-- 
2.10.2