From f14e1272fc83f5b34562518fefcbbe78ca00a129 Mon Sep 17 00:00:00 2001
From: Hammat Wele <hammat.wele@inlibro.com>
Date: Tue, 24 Jan 2023 21:47:59 +0000
Subject: [PATCH] Bug 32511: Circulation statistics result Error 500 where
 select Collection, Shelving location, Home library or Holding library

Circulation statistics show error 500 when the tables 'items' and 'deleteditems' do not have the same number of columns. The error 'The used SELECT statements have a different number of columns' is shown in apache log.

This patch resolvs the problem by selecting the common columns before execute the 'UNION' operator.

To Test

     1. In Staff interface, click on Reports module
     2. Click on Circulation
     3. Fill the form
        Period: Click on 'Row' and Select a date interval
     4. Click on 'Submit'
        --> Koha gives a 500 error message
     5. Apply the patch
     6. Repeat step 1,2,3,4
         --> There's no more error

Signed-off-by: Mohd Hafiz Yusoff <mhby87@gmail.com>
---
 reports/issues_stats.pl | 17 ++++++++++++++++-
 1 file changed, 16 insertions(+), 1 deletion(-)

diff --git a/reports/issues_stats.pl b/reports/issues_stats.pl
index 52e904ed5f..925346dba5 100755
--- a/reports/issues_stats.pl
+++ b/reports/issues_stats.pl
@@ -30,6 +30,7 @@ use C4::Reports qw( GetDelimiterChoices );
 use Koha::AuthorisedValues;
 use Koha::ItemTypes;
 use Koha::Patron::Attribute::Types;
+use Array::Utils qw( intersect );
 
 =head1 NAME
 
@@ -495,7 +496,21 @@ sub calculate {
             $strcalc .= " LEFT JOIN borrower_attributes AS attribute_$_ ON (statistics.borrowernumber = attribute_$_.borrowernumber AND attribute_$_.code = '$_') ";
         }
     }
-    $strcalc .= "LEFT JOIN (SELECT * FROM items UNION SELECT * FROM deleteditems) items ON statistics.itemnumber=items.itemnumber "
+    my @listfield_items;
+    my @listfield_deleteditems;
+    my $colunmlist;
+    my $sth3 = $dbh->prepare("SHOW COLUMNS FROM items;");
+    $sth3->execute;
+    while ( (my $field) = $sth3->fetchrow_array ) {
+        push @listfield_items, $field;
+    }
+    $sth3 = $dbh->prepare("SHOW COLUMNS FROM deleteditems;");
+    $sth3->execute;
+    while ( (my $field) = $sth3->fetchrow_array ) {
+        push @listfield_deleteditems, $field;
+    }
+    $colunmlist = join(",",intersect(@listfield_items, @listfield_deleteditems));
+    $strcalc .= "LEFT JOIN (SELECT $colunmlist FROM items UNION SELECT $colunmlist FROM deleteditems) items ON statistics.itemnumber=items.itemnumber "
       if ( $linefield =~ /^items\./
         or $colfield =~ /^items\./
         or $process == 5
-- 
2.30.2