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