|
Lines 45-50
BEGIN {
Link Here
|
| 45 |
get_column_type get_distinct_values save_dictionary get_from_dictionary |
45 |
get_column_type get_distinct_values save_dictionary get_from_dictionary |
| 46 |
delete_definition delete_report format_results get_sql |
46 |
delete_definition delete_report format_results get_sql |
| 47 |
nb_rows update_sql build_authorised_value_list |
47 |
nb_rows update_sql build_authorised_value_list |
|
|
48 |
GetReservedAuthorisedValues |
| 49 |
GetParametersFromSQL |
| 50 |
IsAuthorisedValueValid |
| 51 |
ValidateSQLParameters |
| 48 |
); |
52 |
); |
| 49 |
} |
53 |
} |
| 50 |
|
54 |
|
|
Lines 62-70
C4::Reports::Guided - Module for generating guided reports
Link Here
|
| 62 |
|
66 |
|
| 63 |
=head1 METHODS |
67 |
=head1 METHODS |
| 64 |
|
68 |
|
| 65 |
=over 2 |
69 |
=head2 get_report_areas |
| 66 |
|
|
|
| 67 |
=item get_report_areas() |
| 68 |
|
70 |
|
| 69 |
This will return a list of all the available report areas |
71 |
This will return a list of all the available report areas |
| 70 |
|
72 |
|
|
Lines 129-135
if ( C4::Context->preference('item-level_itypes') ) {
Link Here
|
| 129 |
unshift @{ $criteria{'CAT'} }, 'biblioitems.itemtype'; |
131 |
unshift @{ $criteria{'CAT'} }, 'biblioitems.itemtype'; |
| 130 |
} |
132 |
} |
| 131 |
|
133 |
|
| 132 |
=item get_report_types() |
134 |
=head2 get_report_types |
| 133 |
|
135 |
|
| 134 |
This will return a list of all the available report types |
136 |
This will return a list of all the available report types |
| 135 |
|
137 |
|
|
Lines 151-157
sub get_report_types {
Link Here
|
| 151 |
|
153 |
|
| 152 |
} |
154 |
} |
| 153 |
|
155 |
|
| 154 |
=item get_report_groups() |
156 |
=head2 get_report_groups |
| 155 |
|
157 |
|
| 156 |
This will return a list of all the available report areas with groups |
158 |
This will return a list of all the available report areas with groups |
| 157 |
|
159 |
|
|
Lines 180-186
sub get_report_groups {
Link Here
|
| 180 |
return \%groups_with_subgroups |
182 |
return \%groups_with_subgroups |
| 181 |
} |
183 |
} |
| 182 |
|
184 |
|
| 183 |
=item get_all_tables() |
185 |
=head2 get_all_tables |
| 184 |
|
186 |
|
| 185 |
This will return a list of all tables in the database |
187 |
This will return a list of all tables in the database |
| 186 |
|
188 |
|
|
Lines 200-206
sub get_all_tables {
Link Here
|
| 200 |
|
202 |
|
| 201 |
} |
203 |
} |
| 202 |
|
204 |
|
| 203 |
=item get_columns($area) |
205 |
=head2 get_columns($area) |
| 204 |
|
206 |
|
| 205 |
This will return a list of all columns for a report area |
207 |
This will return a list of all columns for a report area |
| 206 |
|
208 |
|
|
Lines 244-250
sub _get_columns {
Link Here
|
| 244 |
return (@columns); |
246 |
return (@columns); |
| 245 |
} |
247 |
} |
| 246 |
|
248 |
|
| 247 |
=item build_query($columns,$criteria,$orderby,$area) |
249 |
=head2 build_query($columns,$criteria,$orderby,$area) |
| 248 |
|
250 |
|
| 249 |
This will build the sql needed to return the results asked for, |
251 |
This will build the sql needed to return the results asked for, |
| 250 |
$columns is expected to be of the format tablename.columnname. |
252 |
$columns is expected to be of the format tablename.columnname. |
|
Lines 316-322
sub _build_query {
Link Here
|
| 316 |
return ($query); |
318 |
return ($query); |
| 317 |
} |
319 |
} |
| 318 |
|
320 |
|
| 319 |
=item get_criteria($area,$cgi); |
321 |
=head2 get_criteria($area,$cgi); |
| 320 |
|
322 |
|
| 321 |
Returns an arraref to hashrefs suitable for using in a tmpl_loop. With the criteria and available values. |
323 |
Returns an arraref to hashrefs suitable for using in a tmpl_loop. With the criteria and available values. |
| 322 |
|
324 |
|
|
Lines 388-394
sub get_criteria {
Link Here
|
| 388 |
return ( \@criteria_array ); |
390 |
return ( \@criteria_array ); |
| 389 |
} |
391 |
} |
| 390 |
|
392 |
|
| 391 |
sub nb_rows($) { |
393 |
sub nb_rows { |
| 392 |
my $sql = shift or return; |
394 |
my $sql = shift or return; |
| 393 |
my $sth = C4::Context->dbh->prepare($sql); |
395 |
my $sth = C4::Context->dbh->prepare($sql); |
| 394 |
$sth->execute(); |
396 |
$sth->execute(); |
|
Lines 396-402
sub nb_rows($) {
Link Here
|
| 396 |
return scalar (@$rows); |
398 |
return scalar (@$rows); |
| 397 |
} |
399 |
} |
| 398 |
|
400 |
|
| 399 |
=item execute_query |
401 |
=head2 execute_query |
| 400 |
|
402 |
|
| 401 |
($results, $error) = execute_query($sql, $offset, $limit) |
403 |
($results, $error) = execute_query($sql, $offset, $limit) |
| 402 |
|
404 |
|
|
Lines 422-428
the user in a user-supplied SQL query WILL apply in any case.
Link Here
|
| 422 |
# ~ remove any LIMIT clause |
424 |
# ~ remove any LIMIT clause |
| 423 |
# ~ repace SELECT clause w/ SELECT count(*) |
425 |
# ~ repace SELECT clause w/ SELECT count(*) |
| 424 |
|
426 |
|
| 425 |
sub select_2_select_count ($) { |
427 |
sub select_2_select_count { |
| 426 |
# Modify the query passed in to create a count query... (I think this covers all cases -crn) |
428 |
# Modify the query passed in to create a count query... (I think this covers all cases -crn) |
| 427 |
my ($sql) = strip_limit(shift) or return; |
429 |
my ($sql) = strip_limit(shift) or return; |
| 428 |
$sql =~ s/\bSELECT\W+(?:\w+\W+){1,}?FROM\b|\bSELECT\W\*\WFROM\b/SELECT count(*) FROM /ig; |
430 |
$sql =~ s/\bSELECT\W+(?:\w+\W+){1,}?FROM\b|\bSELECT\W\*\WFROM\b/SELECT count(*) FROM /ig; |
|
Lines 512-518
sub execute_query ($;$$$) {
Link Here
|
| 512 |
# store_results($id,$xml); |
514 |
# store_results($id,$xml); |
| 513 |
} |
515 |
} |
| 514 |
|
516 |
|
| 515 |
=item save_report($sql,$name,$type,$notes) |
517 |
=head2 save_report($sql,$name,$type,$notes) |
| 516 |
|
518 |
|
| 517 |
Given some sql and a name this will saved it so that it can reused |
519 |
Given some sql and a name this will saved it so that it can reused |
| 518 |
Returns id of the newly created report |
520 |
Returns id of the newly created report |
|
Lines 700-706
sub get_saved_report {
Link Here
|
| 700 |
return $dbh->selectrow_hashref($query, undef, $report_arg); |
702 |
return $dbh->selectrow_hashref($query, undef, $report_arg); |
| 701 |
} |
703 |
} |
| 702 |
|
704 |
|
| 703 |
=item create_compound($masterID,$subreportID) |
705 |
=head2 create_compound($masterID,$subreportID) |
| 704 |
|
706 |
|
| 705 |
This will take 2 reports and create a compound report using both of them |
707 |
This will take 2 reports and create a compound report using both of them |
| 706 |
|
708 |
|
|
Lines 730-736
sub create_compound {
Link Here
|
| 730 |
return ( $mastertables, $subtables ); |
732 |
return ( $mastertables, $subtables ); |
| 731 |
} |
733 |
} |
| 732 |
|
734 |
|
| 733 |
=item get_column_type($column) |
735 |
=head2 get_column_type($column) |
| 734 |
|
736 |
|
| 735 |
This takes a column name of the format table.column and will return what type it is |
737 |
This takes a column name of the format table.column and will return what type it is |
| 736 |
(free text, set values, date) |
738 |
(free text, set values, date) |
|
Lines 758-764
sub get_column_type {
Link Here
|
| 758 |
} |
760 |
} |
| 759 |
} |
761 |
} |
| 760 |
|
762 |
|
| 761 |
=item get_distinct_values($column) |
763 |
=head2 get_distinct_values($column) |
| 762 |
|
764 |
|
| 763 |
Given a column name, return an arrary ref of hashrefs suitable for use as a tmpl_loop |
765 |
Given a column name, return an arrary ref of hashrefs suitable for use as a tmpl_loop |
| 764 |
with the distinct values of the column |
766 |
with the distinct values of the column |
|
Lines 852-858
sub _get_column_defs {
Link Here
|
| 852 |
return \%columns; |
854 |
return \%columns; |
| 853 |
} |
855 |
} |
| 854 |
|
856 |
|
| 855 |
=item build_authorised_value_list($authorised_value) |
857 |
=head2 build_authorised_value_list($authorised_value) |
| 856 |
|
858 |
|
| 857 |
Returns an arrayref - hashref pair. The hashref consists of |
859 |
Returns an arrayref - hashref pair. The hashref consists of |
| 858 |
various code => name lists depending on the $authorised_value. |
860 |
various code => name lists depending on the $authorised_value. |
|
Lines 917-927
sub build_authorised_value_list {
Link Here
|
| 917 |
return (\@authorised_values, \%authorised_lib); |
919 |
return (\@authorised_values, \%authorised_lib); |
| 918 |
} |
920 |
} |
| 919 |
|
921 |
|
|
|
922 |
=head2 GetReservedAuthorisedValues |
| 923 |
|
| 924 |
my %reserved_authorised_values = GetReservedAuthorisedValues(); |
| 925 |
|
| 926 |
Returns a hash containig all reserved words |
| 927 |
|
| 928 |
=cut |
| 929 |
|
| 930 |
sub GetReservedAuthorisedValues { |
| 931 |
my %reserved_authorised_values = |
| 932 |
map { $_ => 1 } ( 'date', |
| 933 |
'branches', |
| 934 |
'itemtypes', |
| 935 |
'cn_source', |
| 936 |
'categorycode' ); |
| 937 |
|
| 938 |
return \%reserved_authorised_values; |
| 939 |
} |
| 940 |
|
| 941 |
|
| 942 |
=head2 IsAuthorisedValueValid |
| 943 |
|
| 944 |
my $is_valid_ath_value = IsAuthorisedValueValid($authorised_value) |
| 945 |
|
| 946 |
Returns 1 if $authorised_value is on the reserved authorised values list or |
| 947 |
in the authorised value categories defined in |
| 948 |
|
| 949 |
=cut |
| 950 |
|
| 951 |
sub IsAuthorisedValueValid { |
| 952 |
|
| 953 |
my $authorised_value = shift; |
| 954 |
my $reserved_authorised_values = GetReservedAuthorisedValues(); |
| 955 |
|
| 956 |
if ( exists $reserved_authorised_values->{$authorised_value} || |
| 957 |
IsAuthorisedValueCategory($authorised_value) ) { |
| 958 |
return 1; |
| 959 |
} |
| 960 |
|
| 961 |
return 0; |
| 962 |
} |
| 963 |
|
| 964 |
=head2 GetParametersFromSQL |
| 965 |
|
| 966 |
my @sql_parameters = GetParametersFromSQL($sql) |
| 967 |
|
| 968 |
Returns an arrayref of hashes containing the keys name and authval |
| 969 |
|
| 970 |
=cut |
| 971 |
|
| 972 |
sub GetParametersFromSQL { |
| 973 |
|
| 974 |
my $sql = shift ; |
| 975 |
my @split = split(/<<|>>/,$sql); |
| 976 |
my @sql_parameters = (); |
| 977 |
|
| 978 |
for ( my $i = 0; $i < ($#split/2) ; $i++ ) { |
| 979 |
my ($name,$authval) = split(/\|/,$split[$i*2+1]); |
| 980 |
push @sql_parameters, { 'name' => $name, 'authval' => $authval }; |
| 981 |
} |
| 982 |
|
| 983 |
return \@sql_parameters; |
| 984 |
} |
| 985 |
|
| 986 |
=head2 ValidateSQLParameters |
| 987 |
|
| 988 |
my @problematic_parameters = ValidateSQLParameters($sql) |
| 989 |
|
| 990 |
Returns an arrayref of hashes containing the keys name and authval of |
| 991 |
those SQL parameters that do not correspond to valid authorised names |
| 992 |
|
| 993 |
=cut |
| 994 |
|
| 995 |
sub ValidateSQLParameters { |
| 996 |
|
| 997 |
my $sql = shift; |
| 998 |
my @problematic_parameters = (); |
| 999 |
my $sql_parameters = GetParametersFromSQL($sql); |
| 1000 |
|
| 1001 |
foreach my $sql_parameter (@$sql_parameters) { |
| 1002 |
if ( defined $sql_parameter->{'authval'} ) { |
| 1003 |
push @problematic_parameters, $sql_parameter unless |
| 1004 |
IsAuthorisedValueValid($sql_parameter->{'authval'}); |
| 1005 |
} |
| 1006 |
} |
| 1007 |
|
| 1008 |
return \@problematic_parameters; |
| 1009 |
} |
| 1010 |
|
| 920 |
1; |
1011 |
1; |
| 921 |
__END__ |
1012 |
__END__ |
| 922 |
|
1013 |
|
| 923 |
=back |
|
|
| 924 |
|
| 925 |
=head1 AUTHOR |
1014 |
=head1 AUTHOR |
| 926 |
|
1015 |
|
| 927 |
Chris Cormack <crc@liblime.com> |
1016 |
Chris Cormack <crc@liblime.com> |