From e489c50fb8e4f8e8b0f37668a1534315e056aea0 Mon Sep 17 00:00:00 2001
From: Jacob O'Mara <jacob.omara@ptfs-europe.com>
Date: Wed, 1 Mar 2023 11:02:34 +0000
Subject: [PATCH] Bug 32613: Add database tables to autocomplete

This patch adds a database call to get the table and column names in the
database and parse them into the autocomplete engine.

Test Plan:
As previous commit but also check for column/table names in the
autocomplete popup

Signed-off-by: Lucas Gass <lucas@bywatersolutions.com>
---
 .../modules/reports/guided_reports_start.tt   |  5 ++++
 reports/guided_reports.pl                     | 23 ++++++++++++++++---
 2 files changed, 25 insertions(+), 3 deletions(-)

diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tt
index 997fb25a80..4f3107d82b 100644
--- a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tt
+++ b/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tt
@@ -1547,6 +1547,8 @@
 
         [% IF ( create || editsql || save ) %]
 
+            let tables = JSON.parse('[% To.json(tables) | $raw %]');
+
             var editor = CodeMirror.fromTextArea(sql, {
                 lineNumbers: true,
                 mode: "text/x-sql",
@@ -1558,6 +1560,9 @@
                 },
                 extraKeys: {"Tab": "autocomplete"}, //enable tab to accept auto-complete
                 hint: CodeMirror.hint.sql,
+                hintOptions: {
+                    tables: tables
+                }
             });
             var ExcludedTriggerKeys = { //key-code combinations of keys that will not fire the auto-complete script
                 "8": "backspace",
diff --git a/reports/guided_reports.pl b/reports/guided_reports.pl
index ab79b0f463..9fe12d6f06 100755
--- a/reports/guided_reports.pl
+++ b/reports/guided_reports.pl
@@ -163,16 +163,16 @@ elsif ( $phase eq 'Delete Multiple') {
 }
 
 elsif ( $phase eq 'Delete Saved') {
-	
+
 	# delete a report from the saved reports list
     my $ids = $input->param('reports');
     delete_report($ids);
     print $input->redirect("/cgi-bin/koha/reports/guided_reports.pl?phase=Use%20saved");
 	exit;
-}		
+}
 
 elsif ( $phase eq 'Show SQL'){
-	
+
     my $id = $input->param('reports');
     my $report = Koha::Reports->find($id);
     $template->param(
@@ -998,6 +998,8 @@ elsif ( $phase eq 'Create report from SQL' || $phase eq 'Create report from exis
         $notes      = $report->notes // '';
     }
 
+    my $tables = get_tables();
+
     $template->param(
         sql        => $sql,
         reportname => $reportname,
@@ -1007,6 +1009,7 @@ elsif ( $phase eq 'Create report from SQL' || $phase eq 'Create report from exis
         'public' => '0',
         'cache_expiry' => 300,
         'usecache' => $usecache,
+        'tables' => $tables,
 
     );
 }
@@ -1024,6 +1027,20 @@ sub header_cell_loop {
     return \@headers;
 }
 
+#get a list of available tables for auto-complete
+sub get_tables {
+    my $result = {};
+    my $tables = C4::Reports::Guided->get_all_tables();
+    for my $table (@{$tables}) {
+        my $sql = "SHOW COLUMNS FROM $table";
+        my $rows = C4::Context->dbh->selectall_arrayref($sql, { Slice => {} });
+        for my $row (@{$rows}) {
+            push @{$result->{$table}}, $row->{Field};
+        }
+    }
+    return $result;
+}
+
 foreach (1..6) {
      $template->{VARS}->{'build' . $_} and last;
 }
-- 
2.30.2