X-Git-Url: http://koha-dev.rot13.org:8081/gitweb/?a=blobdiff_plain;f=reports%2Fguided_reports.pl;h=4512ef574dea2525dffc9e4673f66fd06d715a28;hb=392a7e1a1ffb327986a5c0cb6863bbb87e43deaf;hp=9c4286353f049adeb4b10f2457c85fe437fc16b9;hpb=ddb1ecb9d8b4a29a3c0360a07aa539d378193747;p=srvgit

diff --git a/reports/guided_reports.pl b/reports/guided_reports.pl
index 9c4286353f..4512ef574d 100755
--- a/reports/guided_reports.pl
+++ b/reports/guided_reports.pl
@@ -23,8 +23,8 @@ use Text::CSV::Encoded;
 use Encode qw( decode );
 use URI::Escape;
 use File::Temp;
-use File::Basename qw( dirname );
 use C4::Reports::Guided;
+use Koha::Reports;
 use C4::Auth qw/:DEFAULT get_session/;
 use C4::Output;
 use C4::Debug;
@@ -37,6 +37,8 @@ use Koha::AuthorisedValues;
 use Koha::BiblioFrameworks;
 use Koha::Libraries;
 use Koha::Patron::Categories;
+use Koha::SharedContent;
+use Koha::Util::OpenDocument;
 
 =head1 NAME
 
@@ -53,7 +55,8 @@ my $usecache = Koha::Caches->get_instance->memcached_cache;
 
 my $phase = $input->param('phase') // '';
 my $flagsrequired;
-if ( $phase eq 'Build new' ) {
+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' ) {
@@ -89,6 +92,7 @@ elsif ($session and not $input->param('clear_filters')) {
     $filter = $session->param('report_filter');
 }
 
+my $op = $input->param('op') || q||;
 
 my @errors = ();
 if ( !$phase ) {
@@ -106,6 +110,27 @@ elsif ( $phase eq 'Build new' ) {
     );
 } elsif ( $phase eq 'Use saved' ) {
 
+    if ( $op eq 'convert' ) {
+        my $report_id = $input->param('report_id');
+        my $report    = Koha::Reports->find($report_id);
+        if ($report) {
+            my $updated_sql = C4::Reports::Guided::convert_sql( $report->savedsql );
+            C4::Reports::Guided::update_sql(
+                $report_id,
+                {
+                    sql          => $updated_sql,
+                    name         => $report->report_name,
+                    group        => $report->report_group,
+                    subgroup     => $report->report_subgroup,
+                    notes        => $report->notes,
+                    public       => $report->public,
+                    cache_expiry => $report->cache_expiry,
+                }
+            );
+            $template->param( report_converted => $report->report_name );
+        }
+    }
+
     # use a saved report
     # get list of reports and display them
     my $group = $input->param('group');
@@ -113,15 +138,22 @@ elsif ( $phase eq 'Build new' ) {
     $filter->{group} = $group;
     $filter->{subgroup} = $subgroup;
     my $reports = get_saved_reports($filter);
+    my $has_obsolete_reports;
     for my $report ( @$reports ) {
         $report->{results} = C4::Reports::Guided::get_results( $report->{id} );
+        if ( $report->{savedsql} =~ m|biblioitems| and $report->{savedsql} =~ m|marcxml| ) {
+            $report->{seems_obsolete} = 1;
+            $has_obsolete_reports++;
+        }
     }
     $template->param(
-        'saved1' => 1,
-        'savedreports' => $reports,
-        'usecache' => $usecache,
-        'groups_with_subgroups'=> groups_with_subgroups($group, $subgroup),
-        filters => $filter,
+        'manamsg' => $input->param('manamsg') || '',
+        'saved1'                => 1,
+        'savedreports'          => $reports,
+        'usecache'              => $usecache,
+        'groups_with_subgroups' => groups_with_subgroups( $group, $subgroup ),
+        filters                 => $filter,
+        has_obsolete_reports    => $has_obsolete_reports,
     );
 }
 
@@ -144,31 +176,37 @@ elsif ( $phase eq 'Delete Saved') {
 elsif ( $phase eq 'Show SQL'){
 	
     my $id = $input->param('reports');
-    my $report = get_saved_report($id);
+    my $report = Koha::Reports->find($id);
     $template->param(
         'id'      => $id,
-        'reportname' => $report->{report_name},
-        'notes'      => $report->{notes},
-	'sql'     => $report->{savedsql},
-	'showsql' => 1,
+        'reportname' => $report->report_name,
+        '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}
     );
 }
 
 elsif ( $phase eq 'Edit SQL'){
     my $id = $input->param('reports');
-    my $report = get_saved_report($id);
-    my $group = $report->{report_group};
-    my $subgroup  = $report->{report_subgroup};
+    my $report = Koha::Reports->find($id);
+    my $group = $report->report_group;
+    my $subgroup  = $report->report_subgroup;
     $template->param(
-        'sql'        => $report->{savedsql},
-        'reportname' => $report->{report_name},
+        'sql'        => $report->savedsql,
+        'reportname' => $report->report_name,
         'groups_with_subgroups' => groups_with_subgroups($group, $subgroup),
-        'notes'      => $report->{notes},
+        'notes'      => $report->notes,
         'id'         => $id,
-        'cache_expiry' => $report->{cache_expiry},
-        'public' => $report->{public},
+        'cache_expiry' => $report->cache_expiry,
+        'public' => $report->public,
         'usecache' => $usecache,
         'editsql'    => 1,
+        'mana_id' => $report->{mana_id},
+        'mana_comments' => $report->{comments}
     );
 }
 
@@ -196,7 +234,7 @@ elsif ( $phase eq 'Update SQL'){
         $cache_expiry *= 86400; # 60 * 60 * 24
       }
     }
-    # check $cache_expiry isnt too large, Memcached::set requires it to be less than 30 days or it will be treated as if it were an absolute time stamp
+    # check $cache_expiry isn't too large, Memcached::set requires it to be less than 30 days or it will be treated as if it were an absolute time stamp
     if( $cache_expiry >= 2592000 ){
       push @errors, {cache_expiry => $cache_expiry};
     }
@@ -251,6 +289,13 @@ elsif ( $phase eq 'Update SQL'){
                 'save_successful'       => 1,
                 'reportname'            => $reportname,
                 'id'                    => $id,
+                'editsql'               => 1,
+                'sql'                   => $sql,
+                'groups_with_subgroups' => groups_with_subgroups($group, $subgroup),
+                'notes'                 => $notes,
+                'cache_expiry'          => $cache_expiry,
+                'public'                => $public,
+                'usecache'              => $usecache,
             );
             logaction( "REPORTS", "MODIFY", $id, "$reportname | $sql" ) if C4::Context->preference("ReportsLog");
         }
@@ -286,7 +331,7 @@ elsif ( $phase eq 'Report on this Area' ) {
     } elsif( $cache_expiry_units eq "days" ){
       $cache_expiry *= 86400; # 60 * 60 * 24
     }
-    # check $cache_expiry isnt too large, Memcached::set requires it to be less than 30 days or it will be treated as if it were an absolute time stamp
+    # check $cache_expiry isn't too large, Memcached::set requires it to be less than 30 days or it will be treated as if it were an absolute time stamp
     if( $cache_expiry >= 2592000 ){ # oops, over the limit of 30 days
       # report error to user
       $template->param(
@@ -298,7 +343,7 @@ elsif ( $phase eq 'Report on this Area' ) {
         'public' => scalar $input->param('public'),
       );
     } else {
-      # they have choosen a new report and the area to report on
+      # they have chosen a new report and the area to report on
       $template->param(
           'build2' => 1,
           'area'   => scalar $input->param('area'),
@@ -511,7 +556,7 @@ elsif ( $phase eq 'Build report' ) {
 
 elsif ( $phase eq 'Save' ) {
     # Save the report that has just been built
-    my $area           = $input->param('area');
+    my $area = $input->param('area');
     my $sql  = $input->param('sql');
     my $type = $input->param('type');
     $template->param(
@@ -550,13 +595,12 @@ elsif ( $phase eq 'Save Report' ) {
         $cache_expiry *= 86400; # 60 * 60 * 24
       }
     }
-    # check $cache_expiry isnt too large, Memcached::set requires it to be less than 30 days or it will be treated as if it were an absolute time stamp
+    # check $cache_expiry isn't too large, Memcached::set requires it to be less than 30 days or it will be treated as if it were an absolute time stamp
     if( $cache_expiry && $cache_expiry >= 2592000 ){
       push @errors, {cache_expiry => $cache_expiry};
     }
 
     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};
@@ -620,17 +664,37 @@ elsif ( $phase eq 'Save Report' ) {
                 'save_successful' => 1,
                 'reportname'      => $name,
                 'id'              => $id,
+                'editsql'         => 1,
+                'sql'             => $sql,
+                'groups_with_subgroups' => groups_with_subgroups($group, $subgroup),
+                'notes'      => $notes,
+                'cache_expiry' => $cache_expiry,
+                'public' => $public,
+                'usecache' => $usecache,
             );
         }
     }
 }
 
+elsif ($phase eq 'Share'){
+    my $lang = $input->param('mana_language') || '';
+    my $reportid = $input->param('reportid');
+    my $result = Koha::SharedContent::send_entity($lang, $borrowernumber, $reportid, 'report');
+    if ( $result ) {
+        print $input->redirect("/cgi-bin/koha/reports/guided_reports.pl?phase=Use%20saved&manamsg=".$result->{msg});
+    }else{
+        print $input->redirect("/cgi-bin/koha/reports/guided_reports.pl?phase=Use%20saved&manamsg=noanswer");
+    }
+}
 elsif ($phase eq 'Run this report'){
     # execute a saved report
     my $limit      = $input->param('limit') || 20;
     my $offset     = 0;
     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')) {
         $offset = ($input->param('page') - 1) * $limit;
@@ -642,20 +706,27 @@ elsif ($phase eq 'Run this report'){
     );
 
     my ( $sql, $original_sql, $type, $name, $notes );
-    if (my $report = get_saved_report($report_id)) {
-        $sql   = $original_sql = $report->{savedsql};
-        $name  = $report->{report_name};
-        $notes = $report->{notes};
+    if (my $report = Koha::Reports->find($report_id)) {
+        $sql   = $original_sql = $report->savedsql;
+        $name  = $report->report_name;
+        $notes = $report->notes;
 
         my @rows = ();
+        my @allrows = ();
         # if we have at least 1 parameter, and it's not filled, then don't execute but ask for parameters
         if ($sql =~ /<</ && !@sql_params) {
             # split on ??. Each odd (2,4,6,...) entry should be a parameter to fill
             my @split = split /<<|>>/,$sql;
             my @tmpl_parameters;
             my @authval_errors;
+            my %uniq_params;
             for(my $i=0;$i<($#split/2);$i++) {
-                my ($text,$authorised_value) = split /\|/,$split[$i*2+1];
+                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_all} = "$i"; }
+                my ($authorised_value, $all) = split /:/, $authorised_value_all;
                 my $input;
                 my $labelid;
                 if ( not defined $authorised_value ) {
@@ -747,7 +818,7 @@ elsif ($phase eq 'Run this report'){
                     };
                 }
 
-                push @tmpl_parameters, {'entry' => $text, 'input' => $input, 'labelid' => $labelid };
+                push @tmpl_parameters, {'entry' => $text, 'input' => $input, 'labelid' => $labelid, 'name' => $text.$sep.$authorised_value_all, 'include_all' => $all };
             }
             $template->param('sql'         => $sql,
                             'name'         => $name,
@@ -757,22 +828,9 @@ elsif ($phase eq 'Run this report'){
                             'reports'      => $report_id,
                             );
         } else {
-            # OK, we have parameters, or there are none, we run the report
-            # if there were parameters, replace before running
-            # split on ??. Each odd (2,4,6,...) entry should be a parameter to fill
-            my @split = split /<<|>>/,$sql;
-            my @tmpl_parameters;
-            for(my $i=0;$i<$#split/2;$i++) {
-                my $quoted = $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/;
-            }
-            my ($sth, $errors) = execute_query($sql, $offset, $limit);
+            my ($sql,$header_types) = get_prepped_report( $sql, \@param_names, \@sql_params);
+            $template->param(header_types => $header_types);
+            my ( $sth, $errors ) = execute_query( $sql, $offset, $limit, undef, $report_id );
             my $total = nb_rows($sql) || 0;
             unless ($sth) {
                 die "execute_query failed to return sth for report $report_id: $sql";
@@ -783,15 +841,27 @@ elsif ($phase eq 'Run this report'){
                     my @cells = map { +{ cell => $_ } } @$row;
                     push @rows, { cells => \@cells };
                 }
+                if( $want_full_chart ){
+                    my ($sth2, $errors2) = execute_query($sql);
+                    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";
+            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,
                 'sql'     => $sql,
                 original_sql => $original_sql,
                 'id'      => $report_id,
@@ -799,9 +869,10 @@ elsif ($phase eq 'Run this report'){
                 'name'    => $name,
                 'notes'   => $notes,
                 'errors'  => defined($errors) ? [ $errors ] : undef,
-                'pagination_bar'  => pagination_bar($url, $totpages, $input->param('page')),
+                'pagination_bar'  => pagination_bar($url, $totpages, scalar $input->param('page')),
                 'unlimited_total' => $total,
                 'sql_params'      => \@sql_params,
+                'param_names'     => \@param_names,
             );
         }
     }
@@ -813,10 +884,16 @@ elsif ($phase eq 'Run this report'){
 elsif ($phase eq 'Export'){
 
 	# export results to tab separated text or CSV
-	my $sql    = $input->param('sql');  # FIXME: use sql from saved report ID#, not new user-supplied SQL!
-    my $format = $input->param('format');
-    my $reportname = $input->param('reportname');
+    my $report_id      = $input->param('report_id');
+    my $report         = Koha::Reports->find($report_id);
+    my $sql            = $report->savedsql;
+    my @param_names    = $input->multi_param('param_name');
+    my @sql_params     = $input->multi_param('sql_params');
+    my $format         = $input->param('format');
+    my $reportname     = $input->param('reportname');
     my $reportfilename = $reportname ? "$reportname-reportresults.$format" : "reportresults.$format" ;
+
+    ($sql, undef) = get_prepped_report( $sql, \@param_names, \@sql_params );
 	my ($sth, $q_errors) = execute_query($sql);
     unless ($q_errors and @$q_errors) {
         my ( $type, $content );
@@ -825,11 +902,12 @@ 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';
                 $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();
@@ -850,39 +928,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;
-
-                use OpenOffice::OODoc;
-                my $tmpdir = dirname $ods_filepath;
-                odfWorkingDirectory( $tmpdir );
-                my $container = odfContainer( $ods_filepath, create => 'spreadsheet' );
-                my $doc = odfDocument (
-                    container => $container,
-                    part      => 'content'
-                );
-                my $table = $doc->getTable(0);
-                my @headers = header_cell_values( $sth );
-                my $rows = $sth->fetchall_arrayref();
-                my ( $nb_rows, $nb_cols ) = ( 0, 0 );
-                $nb_rows = @$rows;
-                $nb_cols = @headers;
-                $doc->expandTable( $table, $nb_rows + 1, $nb_cols );
-
-                my $row = $doc->getRow( $table, 0 );
-                my $j = 0;
-                for my $header ( @headers ) {
-                    $doc->cellValue( $row, $j, $header );
-                    $j++;
-                }
-                my $i = 1;
-                for ( @$rows ) {
-                    $row = $doc->getRow( $table, $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;
                 }
-                $doc->save();
+
+                # Process
+                generate_ods($ods_filepath, $ods_content);
+
+                # Output
                 binmode(STDOUT);
                 open $ods_fh, '<', $ods_filepath;
                 $content .= $_ while <$ods_fh>;
@@ -909,42 +974,36 @@ 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 // '';
     }
+
     $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,
-    );
-}
-
-elsif ($phase eq 'Create Compound Report'){
-	$template->param( 'savedreports' => get_saved_reports(),
-		'compound' => 1,
-	);
-}
 
-elsif ($phase eq 'Save Compound'){
-    my $master    = $input->param('master');
-	my $subreport = $input->param('subreport');
-	my ($mastertables,$subtables) = create_compound($master,$subreport);
-	$template->param( 'save_compound' => 1,
-		master=>$mastertables,
-		subsql=>$subtables
-	);
+    );
 }
 
 # pass $sth, get back an array of names for the column headers
@@ -1000,7 +1059,6 @@ sub groups_with_subgroups {
 
 sub create_non_existing_group_and_subgroup {
     my ($input, $group, $subgroup) = @_;
-
     if (defined $group and $group ne '') {
         my $report_groups = C4::Reports::Guided::get_report_groups;
         if (not exists $report_groups->{$group}) {
@@ -1010,6 +1068,9 @@ sub create_non_existing_group_and_subgroup {
                 authorised_value => $group,
                 lib => $groupdesc,
             })->store;
+            my $cache_key = "AuthorisedValues-REPORT_GROUP-0-".C4::Context->userenv->{"branch"};
+            my $cache  = Koha::Caches->get_instance();
+            my $result = $cache->clear_from_cache($cache_key);
         }
         if (defined $subgroup and $subgroup ne '') {
             if (not exists $report_groups->{$group}->{subgroups}->{$subgroup}) {
@@ -1020,7 +1081,46 @@ sub create_non_existing_group_and_subgroup {
                     lib => $subgroupdesc,
                     lib_opac => $group,
                 })->store;
+            my $cache_key = "AuthorisedValues-REPORT_SUBGROUP-0-".C4::Context->userenv->{"branch"};
+            my $cache  = Koha::Caches->get_instance();
+            my $result = $cache->clear_from_cache($cache_key);
             }
         }
     }
 }
+
+# pass $sth and sql_params, get back an executable query
+sub get_prepped_report {
+    my ($sql, $param_names, $sql_params ) = @_;
+
+    # First we split out the placeholders
+    # This part of the code supports using [[ table.field | alias ]] in the
+    # query and replaces it by table.field AS alias. Not sure why we would
+    # need it if we can type the latter (which is simpler)?
+    my @split = split /\[\[|\]\]/,$sql;
+    my $headers;
+    for(my $i=0;$i<$#split/2;$i++){ #The placeholders are always the odd elements of the array
+        my ($type,$name) = split /\|/,$split[$i*2+1]; # We split them on '|'
+        $headers->{$name} = $type; # Store as a lookup for the template
+        $headers->{$name} =~ s/^\w*\.//; # strip the table name just as in $sth->{NAME} array
+        $split[$i*2+1] =~ s/(\||\?|\.|\*|\(|\)|\%)/\\$1/g; #Quote any special characters so we can replace the placeholders
+        $name = C4::Context->dbh->quote($name);
+        $sql =~ s/\[\[$split[$i*2+1]\]\]/$type AS $name/; # Remove placeholders from SQL
+    }
+
+    my %lookup;
+    @lookup{@$param_names} = @$sql_params;
+    @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,$headers;
+}