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