+
+# 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;
+}