View | Details | Raw Unified | Return to bug 21215
Collapse All | Expand All

(-)a/C4/Reports/Guided.pm (-7 / +206 lines)
Lines 45-50 BEGIN { Link Here
45
      nb_rows update_sql
45
      nb_rows update_sql
46
      GetReservedAuthorisedValues
46
      GetReservedAuthorisedValues
47
      GetParametersFromSQL
47
      GetParametersFromSQL
48
      GetSQLAuthValueResult
49
      GetPreppedSQLReport
48
      IsAuthorisedValueValid
50
      IsAuthorisedValueValid
49
      ValidateSQLParameters
51
      ValidateSQLParameters
50
      nb_rows update_sql
52
      nb_rows update_sql
Lines 838-843 sub _get_column_defs { Link Here
838
    return \%columns;
840
    return \%columns;
839
}
841
}
840
842
843
sub _authval_resultvalue_authval {
844
    my $parm = shift;
845
    my %tmp = ( 'input' => $parm->{'authval'} );
846
    return \%tmp;
847
}
848
849
sub _authval_resultvalue_branches {
850
    my $parm = shift;
851
852
    my @authorised_values;
853
    my %authorised_lib;
854
855
    # builds list, depending on authorised value...
856
    my $libraries = Koha::Libraries->search( {}, { order_by => ['branchname'] } );
857
    while ( my $library = $libraries->next ) {
858
	push @authorised_values, $library->branchcode;
859
	$authorised_lib{$library->branchcode} = $library->branchname;
860
    }
861
862
    my %tmp = ( 'values' => \@authorised_values, 'labels' => \%authorised_lib );
863
    return \%tmp;
864
}
865
866
sub _authval_resultvalue_itemtypes {
867
    my $parm = shift;
868
869
    my @authorised_values;
870
    my %authorised_lib;
871
    my $dbh=C4::Context->dbh;
872
    # builds list, depending on authorised value...
873
    my $sth = $dbh->prepare("SELECT itemtype,description FROM itemtypes ORDER BY description");
874
    $sth->execute;
875
    while ( my ( $itemtype, $description ) = $sth->fetchrow_array ) {
876
	push @authorised_values, $itemtype;
877
	$authorised_lib{$itemtype} = $description;
878
    }
879
880
    my %tmp = ( 'values' => \@authorised_values, 'labels' => \%authorised_lib );
881
    return \%tmp;
882
}
883
884
sub _authval_resultvalue_biblio_framework {
885
    my $parm = shift;
886
887
    my @authorised_values;
888
    my %authorised_lib;
889
890
    # builds list, depending on authorised value...
891
    my @frameworks = Koha::BiblioFrameworks->search({}, { order_by => ['frameworktext'] });
892
    my $default_source = '';
893
    push @authorised_values,$default_source;
894
    $authorised_lib{$default_source} = 'Default';
895
    foreach my $framework (@frameworks) {
896
	push @authorised_values, $framework->frameworkcode;
897
	$authorised_lib{$framework->frameworkcode} = $framework->frameworktext;
898
    }
899
900
    my %tmp = ( 'values' => \@authorised_values, 'labels' => \%authorised_lib );
901
    return \%tmp;
902
}
903
904
sub _authval_resultvalue_biblio_cn_source {
905
    my $parm = shift;
906
907
    my @authorised_values;
908
    my %authorised_lib;
909
910
    # builds list, depending on authorised value...
911
    my $class_sources = GetClassSources();
912
    my $default_source = C4::Context->preference("DefaultClassificationSource");
913
    foreach my $class_source (sort keys %$class_sources) {
914
	next unless $class_sources->{$class_source}->{'used'} or
915
	    ($class_source eq $default_source);
916
	push @authorised_values, $class_source;
917
	$authorised_lib{$class_source} = $class_sources->{$class_source}->{'description'};
918
    }
919
920
    my %tmp = ( 'values' => \@authorised_values, 'labels' => \%authorised_lib );
921
    return \%tmp;
922
}
923
924
sub _authval_resultvalue_biblio_categorycode {
925
    my $parm = shift;
926
927
    my @authorised_values;
928
    my %authorised_lib;
929
930
    # builds list, depending on authorised value...
931
    my @patron_categories = Koha::Patron::Categories->search({}, { order_by => ['description']});
932
    %authorised_lib = map { $_->categorycode => $_->description } @patron_categories;
933
    push @authorised_values, $_->categorycode for @patron_categories;
934
935
    my %tmp = ( 'values' => \@authorised_values, 'labels' => \%authorised_lib );
936
    return \%tmp;
937
}
938
939
sub authval_prepvalue_date {
940
    my ($parm, $quoted) = @_;
941
    $quoted = output_pref({ dt => dt_from_string($quoted), dateformat => 'iso', dateonly => 1 }) if $quoted;
942
    return $quoted;
943
}
944
945
# reserved_savedsql_auth_values contains the special replacement authority values
946
# used in saved SQL queries.
947
#
948
# The 'auth' function takes a hashref - one element returned from GetParametersFromSQL - and
949
# must return a hashref with one of:
950
# - 'values' and 'labels'
951
#      (for a dropdown menu)
952
# - 'input'
953
#      (for an input field)
954
# - 'auth_val_error' and 'data'
955
#      (in case of error)
956
#
957
# The 'prep' function is used to reformat the input we got from the user, so it can
958
# be used in SQL query. It takes in a hashref (one element from GetParametersFromSQL) and
959
# the input string, and must return the correctly formatted string which will be used in
960
# the SQL query.
961
#
962
my %reserved_savedsql_auth_values = (
963
    'date' => {'auth' => \&_authval_resultvalue_authval, 'prep' => \&_authval_prepvalue_date},
964
    'text' => {'auth' => \&_authval_resultvalue_authval},
965
    'branches' => {'auth' => \&_authval_resultvalue_branches},
966
    'itemtypes' => {'auth' => \&_authval_resultvalue_itemtypes},
967
    'cn_source' => {'auth' => \&_authval_resultvalue_biblio_cn_source},
968
    'categorycode' => {'auth' => \&_authval_resultvalue_biblio_categorycode},
969
    'biblio_framework' => {'auth' => \&_authval_resultvalue_biblio_framework},
970
    );
971
841
=head2 GetReservedAuthorisedValues
972
=head2 GetReservedAuthorisedValues
842
973
843
    my %reserved_authorised_values = GetReservedAuthorisedValues();
974
    my %reserved_authorised_values = GetReservedAuthorisedValues();
Lines 848-863 Returns a hash containig all reserved words Link Here
848
979
849
sub GetReservedAuthorisedValues {
980
sub GetReservedAuthorisedValues {
850
    my %reserved_authorised_values =
981
    my %reserved_authorised_values =
851
            map { $_ => 1 } ( 'date',
982
            map { $_ => 1 } ( keys(%reserved_savedsql_auth_values) );
852
                              'branches',
853
                              'itemtypes',
854
                              'cn_source',
855
                              'categorycode',
856
                              'biblio_framework' );
857
983
858
   return \%reserved_authorised_values;
984
   return \%reserved_authorised_values;
859
}
985
}
860
986
987
=head2 GetSQLAuthValueResult
988
989
  my $params = GetParametersFromSQL($sql);
990
  my $authdata = GetSQLAuthValueResult(@{$params}[0]);
991
992
=cut
993
994
sub GetSQLAuthValueResult {
995
    my $authparam = shift;
996
    my $authorised_value = $authparam->{'authval'};
997
    my %ret;
998
999
    if (defined($reserved_savedsql_auth_values{$authorised_value})) {
1000
	return &{$reserved_savedsql_auth_values{$authorised_value}{'auth'}}($authparam);
1001
    } elsif ( Koha::AuthorisedValues->search({ category => $authorised_value })->count ) {
1002
        my @authorised_values;
1003
        my %authorised_lib;
1004
        my $query = '
1005
                    SELECT authorised_value,lib
1006
                    FROM authorised_values
1007
                    WHERE category=?
1008
                    ORDER BY lib
1009
                    ';
1010
        my $dbh=C4::Context->dbh;
1011
        my $authorised_values_sth = $dbh->prepare($query);
1012
        $authorised_values_sth->execute( $authorised_value);
1013
1014
        while ( my ( $value, $lib ) = $authorised_values_sth->fetchrow_array ) {
1015
            push @authorised_values, $value;
1016
            $authorised_lib{$value} = $lib;
1017
        }
1018
        %ret = ( 'values' => \@authorised_values, 'labels' => \%authorised_lib );
1019
    } else {
1020
        %ret = ( 'auth_val_error' => 1,
1021
                 'data' => {
1022
                     'entry' => $authparam->{'name'},
1023
                     'auth_val' => $authorised_value
1024
                 } );
1025
    }
1026
    return \%ret;
1027
}
861
1028
862
=head2 IsAuthorisedValueValid
1029
=head2 IsAuthorisedValueValid
863
1030
Lines 895-908 sub GetParametersFromSQL { Link Here
895
    my @split = split(/<<|>>/,$sql);
1062
    my @split = split(/<<|>>/,$sql);
896
    my @sql_parameters = ();
1063
    my @sql_parameters = ();
897
1064
1065
    # split on ??. Each odd (2,4,6,...) entry should be a parameter to fill
898
    for ( my $i = 0; $i < ($#split/2) ; $i++ ) {
1066
    for ( my $i = 0; $i < ($#split/2) ; $i++ ) {
899
        my ($name,$authval) = split(/\|/,$split[$i*2+1]);
1067
        my ($name,$authval) = split(/\|/,$split[$i*2+1]);
900
        push @sql_parameters, { 'name' => $name, 'authval' => $authval };
1068
        $authval ||= 'text';
1069
        push @sql_parameters, { 'name' => $name, 'authval' => $authval, 'rawparam' => $split[$i*2+1] };
901
    }
1070
    }
902
1071
903
    return \@sql_parameters;
1072
    return \@sql_parameters;
904
}
1073
}
905
1074
1075
=head2 GetPreppedSQLReport
1076
1077
    my $sql = GetPreppedSQLReport( $sql, \@param_names, \@sql_params );
1078
1079
Returns an executable query
1080
1081
=cut
1082
1083
sub GetPreppedSQLReport {
1084
    my ($sql, $param_names, $sql_params ) = @_;
1085
    my %lookup;
1086
    @lookup{@$param_names} = @$sql_params;
1087
    my $split = GetParametersFromSQL( $sql );
1088
    my @tmpl_parameters;
1089
    my $i = 0;
1090
    foreach my $parm (@$split) {
1091
        my $raw = $parm->{'rawparam'};
1092
        my $quoted = @$param_names ? $lookup{ $raw } : @$sql_params[$i];
1093
        # if there are special regexp chars, we must \ them
1094
        $raw =~ s/(\||\?|\.|\*|\(|\)|\%)/\\$1/g;
1095
        if (defined($reserved_savedsql_auth_values{$parm->{'authval'}}{'prep'})) {
1096
            $quoted = &{$reserved_savedsql_auth_values{$parm->{'authval'}}{'prep'}}($parm, $quoted);
1097
        }
1098
        $quoted = C4::Context->dbh->quote($quoted);
1099
        $sql =~ s/<<$raw>>/$quoted/;
1100
        $i++;
1101
    }
1102
    return $sql;
1103
}
1104
906
=head2 ValidateSQLParameters
1105
=head2 ValidateSQLParameters
907
1106
908
    my @problematic_parameters = ValidateSQLParameters($sql)
1107
    my @problematic_parameters = ValidateSQLParameters($sql)
(-)a/reports/guided_reports.pl (-109 / +23 lines)
Lines 693-801 elsif ($phase eq 'Run this report'){ Link Here
693
        $notes = $report->notes;
693
        $notes = $report->notes;
694
694
695
        my @rows = ();
695
        my @rows = ();
696
        my $split = GetParametersFromSQL( $sql );
697
696
        # if we have at least 1 parameter, and it's not filled, then don't execute but ask for parameters
698
        # if we have at least 1 parameter, and it's not filled, then don't execute but ask for parameters
697
        if ($sql =~ /<</ && !@sql_params) {
699
        if (scalar(@$split) && !@sql_params) {
698
            # split on ??. Each odd (2,4,6,...) entry should be a parameter to fill
699
            my @split = split /<<|>>/,$sql;
700
            my @tmpl_parameters;
700
            my @tmpl_parameters;
701
            my @authval_errors;
701
            my @authval_errors;
702
            my %uniq_params;
702
            my %uniq_params;
703
            for(my $i=0;$i<($#split/2);$i++) {
703
            my $i = 0;
704
                my ($text,$authorised_value) = split /\|/,$split[$i*2+1];
704
            foreach my $parm (@$split) {
705
                my $text = $parm->{'name'};
706
                my $authorised_value = $parm->{'authval'};
705
                my $sep = $authorised_value ? "|" : "";
707
                my $sep = $authorised_value ? "|" : "";
708
                $i++;
706
                if( defined $uniq_params{$text.$sep.$authorised_value} ){
709
                if( defined $uniq_params{$text.$sep.$authorised_value} ){
707
                    next;
710
                    next;
708
                } else { $uniq_params{$text.$sep.$authorised_value} = "$i"; }
711
                } else { $uniq_params{$text.$sep.$authorised_value} = "$i"; }
709
                my $input;
712
                my $input;
710
                my $labelid;
713
                my $labelid;
711
                if ( not defined $authorised_value ) {
714
712
                    # no authorised value input, provide a text box
715
		my $ret = GetSQLAuthValueResult($parm);
713
                    $input = "text";
716
714
                } elsif ( $authorised_value eq "date" ) {
717
		if (defined($ret->{'values'})) {
715
                    # require a date, provide a date picker
716
                    $input = 'date';
717
                } else {
718
                    # defined $authorised_value, and not 'date'
719
                    my $dbh=C4::Context->dbh;
720
                    my @authorised_values;
721
                    my %authorised_lib;
722
                    # builds list, depending on authorised value...
723
                    if ( $authorised_value eq "branches" ) {
724
                        my $libraries = Koha::Libraries->search( {}, { order_by => ['branchname'] } );
725
                        while ( my $library = $libraries->next ) {
726
                            push @authorised_values, $library->branchcode;
727
                            $authorised_lib{$library->branchcode} = $library->branchname;
728
                        }
729
                    }
730
                    elsif ( $authorised_value eq "itemtypes" ) {
731
                        my $sth = $dbh->prepare("SELECT itemtype,description FROM itemtypes ORDER BY description");
732
                        $sth->execute;
733
                        while ( my ( $itemtype, $description ) = $sth->fetchrow_array ) {
734
                            push @authorised_values, $itemtype;
735
                            $authorised_lib{$itemtype} = $description;
736
                        }
737
                    }
738
                    elsif ( $authorised_value eq "biblio_framework" ) {
739
                        my @frameworks = Koha::BiblioFrameworks->search({}, { order_by => ['frameworktext'] });
740
                        my $default_source = '';
741
                        push @authorised_values,$default_source;
742
                        $authorised_lib{$default_source} = 'Default';
743
                        foreach my $framework (@frameworks) {
744
                            push @authorised_values, $framework->frameworkcode;
745
                            $authorised_lib{$framework->frameworkcode} = $framework->frameworktext;
746
                        }
747
                    }
748
                    elsif ( $authorised_value eq "cn_source" ) {
749
                        my $class_sources = GetClassSources();
750
                        my $default_source = C4::Context->preference("DefaultClassificationSource");
751
                        foreach my $class_source (sort keys %$class_sources) {
752
                            next unless $class_sources->{$class_source}->{'used'} or
753
                                        ($class_source eq $default_source);
754
                            push @authorised_values, $class_source;
755
                            $authorised_lib{$class_source} = $class_sources->{$class_source}->{'description'};
756
                        }
757
                    }
758
                    elsif ( $authorised_value eq "categorycode" ) {
759
                        my @patron_categories = Koha::Patron::Categories->search({}, { order_by => ['description']});
760
                        %authorised_lib = map { $_->categorycode => $_->description } @patron_categories;
761
                        push @authorised_values, $_->categorycode for @patron_categories;
762
                    }
763
                    else {
764
                        if ( Koha::AuthorisedValues->search({ category => $authorised_value })->count ) {
765
                            my $query = '
766
                            SELECT authorised_value,lib
767
                            FROM authorised_values
768
                            WHERE category=?
769
                            ORDER BY lib
770
                            ';
771
                            my $authorised_values_sth = $dbh->prepare($query);
772
                            $authorised_values_sth->execute( $authorised_value);
773
774
                            while ( my ( $value, $lib ) = $authorised_values_sth->fetchrow_array ) {
775
                                push @authorised_values, $value;
776
                                $authorised_lib{$value} = $lib;
777
                                # For item location, we show the code and the libelle
778
                                $authorised_lib{$value} = $lib;
779
                            }
780
                        } else {
781
                            # not exists $authorised_value_categories{$authorised_value})
782
                            push @authval_errors, {'entry' => $text,
783
                                                   'auth_val' => $authorised_value };
784
                            # tell the template there's an error
785
                            $template->param( auth_val_error => 1 );
786
                            # skip scrolling list creation and params push
787
                            next;
788
                        }
789
                    }
790
                    $labelid = $text;
718
                    $labelid = $text;
791
                    $labelid =~ s/\W//g;
719
                    $labelid =~ s/\W//g;
792
                    $input = {
720
                    $input = {
793
                        name    => "sql_params",
721
                        name    => "sql_params",
794
                        id      => "sql_params_".$labelid,
722
                        id      => "sql_params_".$labelid,
795
                        values  => \@authorised_values,
723
                        values  => $ret->{'values'},
796
                        labels  => \%authorised_lib,
724
                        labels  => $ret->{'labels'},
797
                    };
725
                    };
798
                }
726
		} elsif (defined($ret->{'auth_val_error'})) {
727
		    push(@authval_errors, $ret->{'data'});
728
		    $template->param( auth_val_error => 1 );
729
		    next;
730
		} else {
731
		    $input = $ret->{'input'};
732
		}
799
733
800
                push @tmpl_parameters, {'entry' => $text, 'input' => $input, 'labelid' => $labelid, 'name' => $text.$sep.$authorised_value };
734
                push @tmpl_parameters, {'entry' => $text, 'input' => $input, 'labelid' => $labelid, 'name' => $text.$sep.$authorised_value };
801
            }
735
            }
Lines 807-813 elsif ($phase eq 'Run this report'){ Link Here
807
                            'reports'      => $report_id,
741
                            'reports'      => $report_id,
808
                            );
742
                            );
809
        } else {
743
        } else {
810
            my $sql = get_prepped_report( $sql, \@param_names, \@sql_params);
744
            my $sql = GetPreppedSQLReport( $sql, \@param_names, \@sql_params);
811
            my ( $sth, $errors ) = execute_query( $sql, $offset, $limit, undef, $report_id );
745
            my ( $sth, $errors ) = execute_query( $sql, $offset, $limit, undef, $report_id );
812
            my $total = nb_rows($sql) || 0;
746
            my $total = nb_rows($sql) || 0;
813
            unless ($sth) {
747
            unless ($sth) {
Lines 859-865 elsif ($phase eq 'Export'){ Link Here
859
    my $reportname     = $input->param('reportname');
793
    my $reportname     = $input->param('reportname');
860
    my $reportfilename = $reportname ? "$reportname-reportresults.$format" : "reportresults.$format" ;
794
    my $reportfilename = $reportname ? "$reportname-reportresults.$format" : "reportresults.$format" ;
861
795
862
    $sql = get_prepped_report( $sql, \@param_names, \@sql_params );
796
    $sql = GetPreppedSQLReport( $sql, \@param_names, \@sql_params );
863
	my ($sth, $q_errors) = execute_query($sql);
797
	my ($sth, $q_errors) = execute_query($sql);
864
    unless ($q_errors and @$q_errors) {
798
    unless ($q_errors and @$q_errors) {
865
        my ( $type, $content );
799
        my ( $type, $content );
Lines 1052-1074 sub create_non_existing_group_and_subgroup { Link Here
1052
        }
986
        }
1053
    }
987
    }
1054
}
988
}
1055
1056
# pass $sth and sql_params, get back an executable query
1057
sub get_prepped_report {
1058
    my ($sql, $param_names, $sql_params ) = @_;
1059
    my %lookup;
1060
    @lookup{@$param_names} = @$sql_params;
1061
    my @split = split /<<|>>/,$sql;
1062
    my @tmpl_parameters;
1063
    for(my $i=0;$i<$#split/2;$i++) {
1064
        my $quoted = @$param_names ? $lookup{ $split[$i*2+1] } : @$sql_params[$i];
1065
        # if there are special regexp chars, we must \ them
1066
        $split[$i*2+1] =~ s/(\||\?|\.|\*|\(|\)|\%)/\\$1/g;
1067
        if ($split[$i*2+1] =~ /\|\s*date\s*$/) {
1068
            $quoted = output_pref({ dt => dt_from_string($quoted), dateformat => 'iso', dateonly => 1 }) if $quoted;
1069
        }
1070
        $quoted = C4::Context->dbh->quote($quoted);
1071
        $sql =~ s/<<$split[$i*2+1]>>/$quoted/;
1072
    }
1073
    return $sql;
1074
}
(-)a/t/db_dependent/Reports/Guided.t (-7 / +107 lines)
Lines 18-24 Link Here
18
18
19
use Modern::Perl;
19
use Modern::Perl;
20
20
21
use Test::More tests => 9;
21
use Test::More tests => 11;
22
use Test::Warn;
22
use Test::Warn;
23
23
24
use t::lib::TestBuilder;
24
use t::lib::TestBuilder;
Lines 110-115 subtest 'GetReservedAuthorisedValues' => sub { Link Here
110
    # to GetReservedAuthorisedValues
110
    # to GetReservedAuthorisedValues
111
    my %test_authval = (
111
    my %test_authval = (
112
        'date' => 1,
112
        'date' => 1,
113
	'text' => 1,
113
        'branches' => 1,
114
        'branches' => 1,
114
        'itemtypes' => 1,
115
        'itemtypes' => 1,
115
        'cn_source' => 1,
116
        'cn_source' => 1,
Lines 123-129 subtest 'GetReservedAuthorisedValues' => sub { Link Here
123
};
124
};
124
125
125
subtest 'IsAuthorisedValueValid' => sub {
126
subtest 'IsAuthorisedValueValid' => sub {
126
    plan tests => 8;
127
    plan tests => 9;
127
    ok( IsAuthorisedValueValid('LOC'),
128
    ok( IsAuthorisedValueValid('LOC'),
128
        'User defined authorised value category is valid');
129
        'User defined authorised value category is valid');
129
130
Lines 148-163 subtest 'GetParametersFromSQL+ValidateSQLParameters' => sub { Link Here
148
    ";
149
    ";
149
150
150
    my @test_parameters_with_custom_list = (
151
    my @test_parameters_with_custom_list = (
151
        { 'name' => 'Year', 'authval' => 'custom_list' },
152
        { 'name' => 'Year', 'authval' => 'custom_list', 'rawparam' => 'Year|custom_list' },
152
        { 'name' => 'Branch', 'authval' => 'branches' },
153
        { 'name' => 'Branch', 'authval' => 'branches', 'rawparam' => 'Branch|branches' },
153
        { 'name' => 'Borrower', 'authval' => undef }
154
        { 'name' => 'Borrower', 'authval' => 'text', 'rawparam' => 'Borrower' }
154
    );
155
    );
155
156
156
    is_deeply( GetParametersFromSQL($test_query_1), \@test_parameters_with_custom_list,
157
    is_deeply( GetParametersFromSQL($test_query_1), \@test_parameters_with_custom_list,
157
        'SQL params are correctly parsed');
158
        'SQL params are correctly parsed');
158
159
159
    my @problematic_parameters = ();
160
    my @problematic_parameters = ();
160
    push @problematic_parameters, { 'name' => 'Year', 'authval' => 'custom_list' };
161
    push @problematic_parameters, { 'name' => 'Year', 'authval' => 'custom_list', 'rawparam' => 'Year|custom_list' };
161
    is_deeply( ValidateSQLParameters( $test_query_1 ),
162
    is_deeply( ValidateSQLParameters( $test_query_1 ),
162
               \@problematic_parameters,
163
               \@problematic_parameters,
163
               '\'custom_list\' not a valid category' );
164
               '\'custom_list\' not a valid category' );
Lines 176-181 subtest 'GetParametersFromSQL+ValidateSQLParameters' => sub { Link Here
176
    );
177
    );
177
};
178
};
178
179
180
subtest 'GetSQLAuthValueResult' => sub {
181
    plan tests => 4;
182
183
    my $test_query_1 = "<<Name>> <<Surname|text>> foo <<Year|date>> <<Branch|branches>> <<Temp|LOC>> <<bar|baz>>";
184
    my $params = GetParametersFromSQL( $test_query_1 );
185
186
187
    my %test_1_return = ( 'input' => 'text' );
188
189
    is_deeply( GetSQLAuthValueResult(@{$params}[0]),
190
               \%test_1_return,
191
               'Returned implicit text parameter correctly');
192
193
194
    my %test_2_return = ( 'input' => 'text' );
195
196
    is_deeply( GetSQLAuthValueResult(@{$params}[1]),
197
               \%test_1_return,
198
               'Returned explicit text parameter correctly');
199
200
201
    my %test_3_return = ( 'input' => 'date' );
202
203
    is_deeply( GetSQLAuthValueResult(@{$params}[2]),
204
               \%test_3_return,
205
               'Returned date parameter correctly');
206
207
208
    # same as _authval_resultvalue_branches() in C4/Reports/Guided.pm
209
    my @authorised_values;
210
    my %authorised_lib;
211
    my $libraries = Koha::Libraries->search( {}, { order_by => ['branchname'] } );
212
    while ( my $library = $libraries->next ) {
213
	push @authorised_values, $library->branchcode;
214
	$authorised_lib{$library->branchcode} = $library->branchname;
215
    }
216
    my %test_4_return = ( 'values' => \@authorised_values, 'labels' => \%authorised_lib );
217
218
    is_deeply( GetSQLAuthValueResult(@{$params}[3]),
219
               \%test_4_return,
220
               'Returned branches parameter correctly');
221
};
222
223
subtest 'GetPreppedSQLReport' => sub {
224
    plan tests => 5;
225
226
    # Test without parameters
227
    my @paramnames = ();
228
    my @sql_params = ();
229
230
    my $test_query_1 = "SELECT foo FROM bar WHERE qux";
231
232
    is( GetPreppedSQLReport( $test_query_1, \@paramnames, \@sql_params ),  $test_query_1,
233
        'Returns exact same SQL when no parameters to substitute');
234
235
236
    # Test completely unknown parameters
237
    my $test_query_2 = "SELECT foo FROM bar WHERE <<qux>>";
238
239
    is( GetPreppedSQLReport( $test_query_2, \@paramnames, \@sql_params ),
240
        "SELECT foo FROM bar WHERE NULL",
241
        'Returns completely unknown parameters as NULLs');
242
243
244
    # Test unknown parameters
245
    @paramnames = ( "qux" );
246
    my $test_query_3 = "SELECT foo FROM bar WHERE <<qux>>";
247
248
    is( GetPreppedSQLReport( $test_query_3, \@paramnames, \@sql_params ),
249
        "SELECT foo FROM bar WHERE NULL",
250
        'Returns parameters with unknown sql_param as NULLs');
251
252
253
254
    # Test parameter substitution
255
    @paramnames = ( "qux" );
256
    @sql_params = ( "XXXX" );
257
258
    my $test_query_4 = "foo qux <<qux>> bar";
259
    my $test_query_4_temp = "foo qux ? bar";
260
261
    my $dbh = C4::Context->dbh;
262
    my $test_query_4_ok = "foo qux " . $dbh->quote($sql_params[0]) . " bar";
263
264
    is( GetPreppedSQLReport( $test_query_4, \@paramnames, \@sql_params ),
265
        $test_query_4_ok,
266
        'Returns parameter substituted');
267
268
269
    # Test character escaping (and NULL params, too)
270
    @paramnames = ( "qu*x" );
271
    @sql_params = ( "XXX" );
272
    my $test_query_5 = "foo <<qx|text>> <<quux>> <<qu*x>> bar";
273
    my $test_query_5_ok = "foo NULL NULL " . $dbh->quote($sql_params[0]) . " bar";
274
275
    is( GetPreppedSQLReport( $test_query_5, \@paramnames, \@sql_params ),
276
        $test_query_5_ok,
277
        'Handles escaping parameters');
278
};
279
179
subtest 'get_saved_reports' => sub {
280
subtest 'get_saved_reports' => sub {
180
    plan tests => 16;
281
    plan tests => 16;
181
    my $dbh = C4::Context->dbh;
282
    my $dbh = C4::Context->dbh;
182
- 

Return to bug 21215