|
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) |