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

        }

        unless( $split[$i*2+1] =~ /\|\s*list\s*$/ && $quoted ){

            $quoted = C4::Context->dbh->quote($quoted);

        } else {

            my @list = split /\n/, $quoted;

            my @quoted_list;

            foreach my $item ( @list ){

                $item =~ s/\r//;

              push @quoted_list, C4::Context->dbh->quote($item);

            }

            $quoted="(".join(",",@quoted_list).")";

        }

        $sql =~ s/<<$split[$i*2+1]>>/$quoted/;

    }

    return $sql,$headers;

}

-