X-Git-Url: http://koha-dev.rot13.org:8081/gitweb/?a=blobdiff_plain;ds=inline;f=reports%2Fguided_reports.pl;h=f4ed6cfdfc78e9ec474a40936d570464835c0c18;hb=17e20f4ec4a1674a2faac9df9ad5231ffd9ddaf1;hp=4a6ace361df852945b7f168f11102b42dac3f812;hpb=28edd8848320fd0f0f284245f2f0567a5907619a;p=koha-ffzg.git

diff --git a/reports/guided_reports.pl b/reports/guided_reports.pl
index 4a6ace361d..f4ed6cfdfc 100755
--- a/reports/guided_reports.pl
+++ b/reports/guided_reports.pl
@@ -23,22 +23,21 @@ use Text::CSV::Encoded;
 use Encode qw( decode );
 use URI::Escape;
 use File::Temp;
-use File::Basename qw( dirname );
-use C4::Reports::Guided;
+use C4::Reports::Guided qw( delete_report get_report_areas convert_sql update_sql get_saved_reports get_results ValidateSQLParameters format_results get_report_types get_columns get_from_dictionary get_criteria build_query save_report execute_query nb_rows get_report_groups );
 use Koha::Reports;
-use C4::Auth qw/:DEFAULT get_session/;
-use C4::Output;
-use C4::Debug;
+use C4::Auth qw( get_template_and_user get_session );
+use C4::Output qw( pagination_bar output_html_with_http_headers );
 use C4::Context;
 use Koha::Caches;
-use C4::Log;
-use Koha::DateUtils qw/dt_from_string output_pref/;
+use C4::Log qw( logaction );
 use Koha::AuthorisedValue;
 use Koha::AuthorisedValues;
 use Koha::BiblioFrameworks;
 use Koha::Libraries;
 use Koha::Patron::Categories;
 use Koha::SharedContent;
+use Koha::Util::OpenDocument qw( generate_ods );
+use C4::ClassSource qw( GetClassSources );
 
 =head1 NAME
 
@@ -50,12 +49,13 @@ Script to control the guided report creation
 
 =cut
 
-my $input = new CGI;
+my $input = CGI->new;
 my $usecache = Koha::Caches->get_instance->memcached_cache;
 
 my $phase = $input->param('phase') // '';
 my $flagsrequired;
-if ( ( $phase eq 'Build new' ) || ( $phase eq 'Create report from SQL' ) || ( $phase eq 'Edit SQL' ) ){
+if ( ( $phase eq 'Build new' ) || ( $phase eq 'Create report from SQL' ) || ( $phase eq 'Edit SQL' )
+   || ( $phase eq 'Build new from existing' ) ) {
     $flagsrequired = 'create_reports';
 }
 elsif ( $phase eq 'Use saved' ) {
@@ -73,12 +73,11 @@ my ( $template, $borrowernumber, $cookie ) = get_template_and_user(
         template_name   => "reports/guided_reports_start.tt",
         query           => $input,
         type            => "intranet",
-        authnotrequired => 0,
         flagsrequired   => { reports => $flagsrequired },
-        debug           => 1,
     }
 );
-my $session = $cookie ? get_session($cookie->value) : undef;
+my $session_id = $input->cookie('CGISESSID');
+my $session = $session_id ? get_session($session_id) : undef;
 
 my $filter;
 if ( $input->param("filter_set") or $input->param('clear_filters') ) {
@@ -164,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(
@@ -182,7 +181,6 @@ elsif ( $phase eq 'Show SQL'){
         'notes'      => $report->notes,
         'sql'     => $report->savedsql,
         'showsql' => 1,
-        'mana_success' => $input->param('mana_success'),
         'mana_success' => scalar $input->param('mana_success'),
         'mana_id' => $report->{mana_id},
         'mana_comments' => $report->{comments}
@@ -194,6 +192,7 @@ elsif ( $phase eq 'Edit SQL'){
     my $report = Koha::Reports->find($id);
     my $group = $report->report_group;
     my $subgroup  = $report->report_subgroup;
+    my $tables = get_tables();
     $template->param(
         'sql'        => $report->savedsql,
         'reportname' => $report->report_name,
@@ -205,7 +204,8 @@ elsif ( $phase eq 'Edit SQL'){
         'usecache' => $usecache,
         'editsql'    => 1,
         'mana_id' => $report->{mana_id},
-        'mana_comments' => $report->{comments}
+        'mana_comments' => $report->{comments},
+        'tables' => $tables
     );
 }
 
@@ -220,8 +220,8 @@ elsif ( $phase eq 'Update SQL'){
     my $cache_expiry_units = $input->param('cache_expiry_units');
     my $public = $input->param('public');
     my $save_anyway = $input->param('save_anyway');
-
     my @errors;
+    my $tables = get_tables();
 
     # if we have the units, then we came from creating a report from SQL and thus need to handle converting units
     if( $cache_expiry_units ){
@@ -240,12 +240,8 @@ elsif ( $phase eq 'Update SQL'){
 
     create_non_existing_group_and_subgroup($input, $group, $subgroup);
 
-    if ($sql =~ /;?\W?(UPDATE|DELETE|DROP|INSERT|SHOW|CREATE)\W/i) {
-        push @errors, {sqlerr => $1};
-    }
-    elsif ($sql !~ /^(SELECT)/i) {
-        push @errors, {queryerr => "No SELECT"};
-    }
+    my ( $is_sql_valid, $validation_errors ) = Koha::Report->new({ savedsql => $sql })->is_sql_valid;
+    push(@errors, @$validation_errors) unless $is_sql_valid;
 
     if (@errors) {
         $template->param(
@@ -270,7 +266,7 @@ elsif ( $phase eq 'Update SQL'){
                 'public' => $public,
                 'problematic_authvals' => $problematic_authvals,
                 'warn_authval_problem' => 1,
-                'phase_update' => 1
+                'phase_update' => 1,
             );
 
         } else {
@@ -295,6 +291,7 @@ elsif ( $phase eq 'Update SQL'){
                 'cache_expiry'          => $cache_expiry,
                 'public'                => $public,
                 'usecache'              => $usecache,
+                'tables'                => $tables
             );
             logaction( "REPORTS", "MODIFY", $id, "$reportname | $sql" ) if C4::Context->preference("ReportsLog");
         }
@@ -407,32 +404,13 @@ elsif ( $phase eq 'Choose these criteria' ) {
 
         # If value is not defined, then it may be range values
         if (!defined $value) {
-
             my $fromvalue = $input->param( "from_" . $crit . "_value" );
             my $tovalue   = $input->param( "to_"   . $crit . "_value" );
 
-            # If the range values are dates
-            my $fromvalue_dt;
-            $fromvalue_dt = eval { dt_from_string( $fromvalue ); } if ( $fromvalue );
-            my $tovalue_dt;
-            $tovalue_dt = eval { dt_from_string( $tovalue ); } if ($tovalue);
-            if ( $fromvalue_dt && $tovalue_dt ) {
-                $fromvalue = output_pref( { dt => dt_from_string( $fromvalue_dt ), dateonly => 1, dateformat => 'iso' } );
-                $tovalue   = output_pref( { dt => dt_from_string( $tovalue_dt ), dateonly => 1, dateformat => 'iso' } );
-            }
-
             if ($fromvalue && $tovalue) {
                 $query_criteria .= " AND $crit >= '$fromvalue' AND $crit <= '$tovalue'";
             }
-
         } else {
-
-            # If value is a date
-            my $value_dt;
-            $value_dt  =  eval { dt_from_string( $value ); } if ( $value );
-            if ( $value_dt ) {
-                $value = output_pref( { dt => dt_from_string( $value_dt ), dateonly => 1, dateformat => 'iso' } );
-            }
             # don't escape runtime parameters, they'll be at runtime
             if ($value =~ /<<.*>>/) {
                 $query_criteria .= " AND $crit=$value";
@@ -582,6 +560,7 @@ elsif ( $phase eq 'Save Report' ) {
     my $cache_expiry_units = $input->param('cache_expiry_units');
     my $public = $input->param('public');
     my $save_anyway = $input->param('save_anyway');
+    my $tables = get_tables();
 
 
     # if we have the units, then we came from creating a report from SQL and thus need to handle converting units
@@ -601,12 +580,8 @@ elsif ( $phase eq 'Save Report' ) {
 
     create_non_existing_group_and_subgroup($input, $group, $subgroup);
     ## FIXME this is AFTER entering a name to save the report under
-    if ($sql =~ /;?\W?(UPDATE|DELETE|DROP|INSERT|SHOW|CREATE)\W/i) {
-        push @errors, {sqlerr => $1};
-    }
-    elsif ($sql !~ /^(SELECT)/i) {
-        push @errors, {queryerr => "No SELECT"};
-    }
+    my ( $is_sql_valid, $validation_errors ) = Koha::Report->new({ savedsql => $sql })->is_sql_valid;
+    push(@errors, @$validation_errors) unless $is_sql_valid;
 
     if (@errors) {
         $template->param(
@@ -670,6 +645,7 @@ elsif ( $phase eq 'Save Report' ) {
                 'cache_expiry' => $cache_expiry,
                 'public' => $public,
                 'usecache' => $usecache,
+                'tables' => $tables
             );
         }
     }
@@ -692,6 +668,7 @@ elsif ($phase eq 'Run this report'){
     my $report_id  = $input->param('reports');
     my @sql_params = $input->multi_param('sql_params');
     my @param_names = $input->multi_param('param_name');
+    my $want_full_chart = $input->param('want_full_chart') || 0;
 
     # offset algorithm
     if ($input->param('page')) {
@@ -719,11 +696,12 @@ elsif ($phase eq 'Run this report'){
             my @authval_errors;
             my %uniq_params;
             for(my $i=0;$i<($#split/2);$i++) {
-                my ($text,$authorised_value) = split /\|/,$split[$i*2+1];
-                my $sep = $authorised_value ? "|" : "";
-                if( defined $uniq_params{$text.$sep.$authorised_value} ){
+                my ($text,$authorised_value_all) = split /\|/,$split[$i*2+1];
+                my $sep = $authorised_value_all ? "|" : "";
+                if( defined $uniq_params{$text.$sep.$authorised_value_all} ){
                     next;
-                } else { $uniq_params{$text.$sep.$authorised_value} = "$i"; }
+                } else { $uniq_params{$text.$sep.$authorised_value_all} = "$i"; }
+                my ($authorised_value, $all) = split /:/, $authorised_value_all;
                 my $input;
                 my $labelid;
                 if ( not defined $authorised_value ) {
@@ -732,6 +710,9 @@ elsif ($phase eq 'Run this report'){
                 } elsif ( $authorised_value eq "date" ) {
                     # require a date, provide a date picker
                     $input = 'date';
+                } elsif ( $authorised_value eq "list" ) {
+                    # require a list, provide a textarea
+                    $input = 'textarea';
                 } else {
                     # defined $authorised_value, and not 'date'
                     my $dbh=C4::Context->dbh;
@@ -754,7 +735,7 @@ elsif ($phase eq 'Run this report'){
                         }
                     }
                     elsif ( $authorised_value eq "biblio_framework" ) {
-                        my @frameworks = Koha::BiblioFrameworks->search({}, { order_by => ['frameworktext'] });
+                        my @frameworks = Koha::BiblioFrameworks->search({}, { order_by => ['frameworktext'] })->as_list;
                         my $default_source = '';
                         push @authorised_values,$default_source;
                         $authorised_lib{$default_source} = 'Default';
@@ -774,10 +755,34 @@ elsif ($phase eq 'Run this report'){
                         }
                     }
                     elsif ( $authorised_value eq "categorycode" ) {
-                        my @patron_categories = Koha::Patron::Categories->search({}, { order_by => ['description']});
+                        my @patron_categories = Koha::Patron::Categories->search({}, { order_by => ['description']})->as_list;
                         %authorised_lib = map { $_->categorycode => $_->description } @patron_categories;
                         push @authorised_values, $_->categorycode for @patron_categories;
                     }
+                    elsif ( $authorised_value eq "cash_registers" ) {
+                        my $sth = $dbh->prepare("SELECT id, name FROM cash_registers ORDER BY description");
+                        $sth->execute;
+                        while ( my ( $id, $name ) = $sth->fetchrow_array ) {
+                            push @authorised_values, $id;
+                            $authorised_lib{$id} = $name;
+                        }
+                    }
+                    elsif ( $authorised_value eq "debit_types" ) {
+                        my $sth = $dbh->prepare("SELECT code, description FROM account_debit_types ORDER BY code");
+                        $sth->execute;
+                        while ( my ( $code, $description ) = $sth->fetchrow_array ) {
+                           push @authorised_values, $code;
+                           $authorised_lib{$code} = $description;
+                        }
+                    }
+                    elsif ( $authorised_value eq "credit_types" ) {
+                        my $sth = $dbh->prepare("SELECT code, description FROM account_credit_types ORDER BY code");
+                        $sth->execute;
+                        while ( my ( $code, $description ) = $sth->fetchrow_array ) {
+                           push @authorised_values, $code;
+                           $authorised_lib{$code} = $description;
+                        }
+                    }
                     else {
                         if ( Koha::AuthorisedValues->search({ category => $authorised_value })->count ) {
                             my $query = '
@@ -815,7 +820,7 @@ elsif ($phase eq 'Run this report'){
                     };
                 }
 
-                push @tmpl_parameters, {'entry' => $text, 'input' => $input, 'labelid' => $labelid, 'name' => $text.$sep.$authorised_value };
+                push @tmpl_parameters, {'entry' => $text, 'input' => $input, 'labelid' => $labelid, 'name' => $text.$sep.$authorised_value_all, 'include_all' => $all };
             }
             $template->param('sql'         => $sql,
                             'name'         => $name,
@@ -825,48 +830,61 @@ elsif ($phase eq 'Run this report'){
                             'reports'      => $report_id,
                             );
         } else {
-            my $sql = get_prepped_report( $sql, \@param_names, \@sql_params);
-            my ( $sth, $errors ) = execute_query( $sql, $offset, $limit, undef, $report_id );
-            my ($sth2, $errors2) = execute_query($sql);
-            my $total = nb_rows($sql) || 0;
-            unless ($sth) {
+            my ($sql,$header_types) = $report->prep_report( \@param_names, \@sql_params );
+            $template->param(header_types => $header_types);
+            my ( $sth, $errors ) = execute_query(
+                {
+                    sql        => $sql,
+                    offset     => $offset,
+                    limit      => $limit,
+                    report_id  => $report_id,
+                }
+            );
+            my $total;
+            if (!$sth) {
                 die "execute_query failed to return sth for report $report_id: $sql";
-            } else {
+            } elsif ( !$errors ) {
+                $total = nb_rows($sql) || 0;
                 my $headers = header_cell_loop($sth);
                 $template->param(header_row => $headers);
                 while (my $row = $sth->fetchrow_arrayref()) {
                     my @cells = map { +{ cell => $_ } } @$row;
                     push @rows, { cells => \@cells };
                 }
-                while (my $row = $sth2->fetchrow_arrayref()) {
-                    my @cells = map { +{ cell => $_ } } @$row;
-                    push @allrows, { cells => \@cells };
+                if( $want_full_chart ){
+                    my ( $sth2, $errors2 ) = execute_query( { sql => $sql, report_id => $report_id } );
+                    while (my $row = $sth2->fetchrow_arrayref()) {
+                        my @cells = map { +{ cell => $_ } } @$row;
+                        push @allrows, { cells => \@cells };
+                    }
                 }
-            }
 
-            my $totpages = int($total/$limit) + (($total % $limit) > 0 ? 1 : 0);
-            my $url = "/cgi-bin/koha/reports/guided_reports.pl?reports=$report_id&amp;phase=Run%20this%20report&amp;limit=$limit";
-            if (@param_names) {
-                $url = join('&amp;param_name=', $url, map { URI::Escape::uri_escape_utf8($_) } @param_names);
-            }
-            if (@sql_params) {
-                $url = join('&amp;sql_params=', $url, map { URI::Escape::uri_escape_utf8($_) } @sql_params);
-            }
+                my $totpages = int($total/$limit) + (($total % $limit) > 0 ? 1 : 0);
+                my $url = "/cgi-bin/koha/reports/guided_reports.pl?reports=$report_id&amp;phase=Run%20this%20report&amp;limit=$limit&amp;want_full_chart=$want_full_chart";
+                if (@param_names) {
+                    $url = join('&amp;param_name=', $url, map { URI::Escape::uri_escape_utf8($_) } @param_names);
+                }
+                if (@sql_params) {
+                    $url = join('&amp;sql_params=', $url, map { URI::Escape::uri_escape_utf8($_) } @sql_params);
+                }
 
+                $template->param(
+                    'results'        => \@rows,
+                    'allresults'     => \@allrows,
+                    'pagination_bar' => pagination_bar($url, $totpages, scalar $input->param('page')),
+                    'unlimited_total' => $total,
+                );
+            }
             $template->param(
-                'results' => \@rows,
-                'allresults' => \@allrows,
-                'sql'     => $sql,
-                original_sql => $original_sql,
-                'id'      => $report_id,
-                'execute' => 1,
-                'name'    => $name,
-                'notes'   => $notes,
-                'errors'  => defined($errors) ? [ $errors ] : undef,
-                'pagination_bar'  => pagination_bar($url, $totpages, scalar $input->param('page')),
-                'unlimited_total' => $total,
-                'sql_params'      => \@sql_params,
-                'param_names'     => \@param_names,
+                'sql'         => $sql,
+                original_sql  => $original_sql,
+                'id'          => $report_id,
+                'execute'     => 1,
+                'name'        => $name,
+                'notes'       => $notes,
+                'errors'      => defined($errors) ? [$errors] : undef,
+                'sql_params'  => \@sql_params,
+                'param_names' => \@param_names,
             );
         }
     }
@@ -887,8 +905,8 @@ elsif ($phase eq 'Export'){
     my $reportname     = $input->param('reportname');
     my $reportfilename = $reportname ? "$reportname-reportresults.$format" : "reportresults.$format" ;
 
-    $sql = get_prepped_report( $sql, \@param_names, \@sql_params );
-	my ($sth, $q_errors) = execute_query($sql);
+    ($sql, undef) = $report->prep_report( \@param_names, \@sql_params );
+    my ( $sth, $q_errors ) = execute_query( { sql => $sql, report_id => $report_id } );
     unless ($q_errors and @$q_errors) {
         my ( $type, $content );
         if ($format eq 'tab') {
@@ -896,12 +914,11 @@ elsif ($phase eq 'Export'){
             $content .= join("\t", header_cell_values($sth)) . "\n";
             $content = Encode::decode('UTF-8', $content);
             while (my $row = $sth->fetchrow_arrayref()) {
-                $content .= join("\t", @$row) . "\n";
+                $content .= join("\t", map { $_ // '' } @$row) . "\n";
             }
         } else {
-            my $delimiter = C4::Context->preference('delimiter') || ',';
             if ( $format eq 'csv' ) {
-                $delimiter = "\t" if $delimiter eq 'tabulation';
+                my $delimiter = C4::Context->csv_delimiter;
                 $type = 'application/csv';
                 my $csv = Text::CSV::Encoded->new({ encoding_out => 'UTF-8', sep_char => $delimiter});
                 $csv or die "Text::CSV::Encoded->new({binary => 1}) FAILED: " . Text::CSV::Encoded->error_diag();
@@ -922,43 +939,26 @@ elsif ($phase eq 'Export'){
                 $type = 'application/vnd.oasis.opendocument.spreadsheet';
                 my $ods_fh = File::Temp->new( UNLINK => 0 );
                 my $ods_filepath = $ods_fh->filename;
-
-                # Create document
-                use OpenOffice::OODoc;
-                my $tmpdir = dirname $ods_filepath;
-                odfWorkingDirectory( $tmpdir );
-                my $doc = odfDocument( file => $ods_filepath, create => 'spreadsheet' );
-
-                # Prepare sheet
-                my @headers = header_cell_values( $sth );
-                my $rows = $sth->fetchall_arrayref();
-                my ( $nb_rows, $nb_cols ) = ( 0, 0 );
-                $nb_rows = @$rows;
-                $nb_cols = @headers;
-                my $sheet = $doc->expandTable( 0, $nb_rows + 1, $nb_cols );
-                my @rows = $doc->getTableRows($sheet);
-
-                # Write headers row
-                my $row = $rows[0];
-                my $j = 0;
-                for my $header ( @headers ) {
-                    $doc->cellValue( $row, $j, $header );
-                    $j++;
-                }
-
-                # Write all rows
-                my $i = 1;
-                for ( @$rows ) {
-                    $row = $rows[$i];
-                    for ( my $j = 0 ; $j < $nb_cols ; $j++ ) {
-                        my $value = Encode::encode( 'UTF8', $rows->[$i - 1][$j] );
-                        $doc->cellValue( $row, $j, $value );
+                my $ods_content;
+
+                # First line is headers
+                my @headers = header_cell_values($sth);
+                push @$ods_content, \@headers;
+
+                # Other line in Unicode
+                my $sql_rows = $sth->fetchall_arrayref();
+                foreach my $sql_row ( @$sql_rows ) {
+                    my @content_row;
+                    foreach my $sql_cell ( @$sql_row ) {
+                        push @content_row, Encode::encode( 'UTF8', $sql_cell );
                     }
-                    $i++;
+                    push @$ods_content, \@content_row;
                 }
 
-                # Done
-                $doc->save();
+                # Process
+                generate_ods($ods_filepath, $ods_content);
+
+                # Output
                 binmode(STDOUT);
                 open $ods_fh, '<', $ods_filepath;
                 $content .= $_ while <$ods_fh>;
@@ -985,25 +985,38 @@ elsif ($phase eq 'Export'){
     );
 }
 
-elsif ( $phase eq 'Create report from SQL' ) {
+elsif ( $phase eq 'Create report from SQL' || $phase eq 'Create report from existing' ) {
 
-    my ($group, $subgroup);
-    # allow the user to paste in sql
+    my ($group, $subgroup, $sql, $reportname, $notes);
     if ( $input->param('sql') ) {
-        $group = $input->param('report_group');
-        $subgroup  = $input->param('report_subgroup');
-        $template->param(
-            'sql'           => scalar $input->param('sql') // '',
-            'reportname'    => scalar $input->param('reportname') // '',
-            'notes'         => scalar $input->param('notes') // '',
-        );
+        $group      = $input->param('report_group');
+        $subgroup   = $input->param('report_subgroup');
+        $sql        = $input->param('sql') // '';
+        $reportname = $input->param('reportname') // '';
+        $notes      = $input->param('notes') // '';
     }
+    elsif ( my $report_id = $input->param('report_id') ) {
+        my $report = Koha::Reports->find($report_id);
+        $group      = $report->report_group;
+        $subgroup   = $report->report_subgroup;
+        $sql        = $report->savedsql // '';
+        $reportname = $report->report_name // '';
+        $notes      = $report->notes // '';
+    }
+
+    my $tables = get_tables();
+
     $template->param(
+        sql        => $sql,
+        reportname => $reportname,
+        notes      => $notes,
         'create' => 1,
         'groups_with_subgroups' => groups_with_subgroups($group, $subgroup),
         'public' => '0',
         'cache_expiry' => 300,
         'usecache' => $usecache,
+        'tables' => $tables,
+
     );
 }
 
@@ -1020,6 +1033,27 @@ sub header_cell_loop {
     return \@headers;
 }
 
+#get a list of available tables for auto-complete
+sub get_tables {
+    my $result = {};
+    my $cache  = Koha::Caches->get_instance();
+    my $tables = $cache->get_from_cache("Reports-SQL_tables-for-autocomplete");
+
+    return $tables
+      if $tables;
+
+    $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};
+        }
+    }
+    $cache->set_in_cache("Reports-SQL_tables-for-autocomplete",$result);
+    return $result;
+}
+
 foreach (1..6) {
      $template->{VARS}->{'build' . $_} and last;
 }
@@ -1090,22 +1124,3 @@ sub create_non_existing_group_and_subgroup {
     }
 }
 
-# pass $sth and sql_params, get back an executable query
-sub get_prepped_report {
-    my ($sql, $param_names, $sql_params ) = @_;
-    my %lookup;
-    @lookup{@$param_names} = @$sql_params;
-    my @split = split /<<|>>/,$sql;
-    my @tmpl_parameters;
-    for(my $i=0;$i<$#split/2;$i++) {
-        my $quoted = @$param_names ? $lookup{ $split[$i*2+1] } : @$sql_params[$i];
-        # if there are special regexp chars, we must \ them
-        $split[$i*2+1] =~ s/(\||\?|\.|\*|\(|\)|\%)/\\$1/g;
-        if ($split[$i*2+1] =~ /\|\s*date\s*$/) {
-            $quoted = output_pref({ dt => dt_from_string($quoted), dateformat => 'iso', dateonly => 1 }) if $quoted;
-        }
-        $quoted = C4::Context->dbh->quote($quoted);
-        $sql =~ s/<<$split[$i*2+1]>>/$quoted/;
-    }
-    return $sql;
-}