1 # Copyright 2012 Catalyst IT Ltd.
2 # Copyright 2015 Koha Development team
4 # This file is part of Koha.
6 # Koha is free software; you can redistribute it and/or modify it
7 # under the terms of the GNU General Public License as published by
8 # the Free Software Foundation; either version 3 of the License, or
9 # (at your option) any later version.
11 # Koha is distributed in the hope that it will be useful, but
12 # WITHOUT ANY WARRANTY; without even the implied warranty of
13 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
14 # GNU General Public License for more details.
16 # You should have received a copy of the GNU General Public License
17 # along with Koha; if not, see <http://www.gnu.org/licenses>.
21 use Test::More tests => 7;
24 use t::lib::TestBuilder;
28 use_ok('C4::Reports::Guided');
30 'C4::Reports::Guided',
31 qw(save_report delete_report execute_query)
34 my $schema = Koha::Database->new->schema;
35 $schema->storage->txn_begin;
36 my $builder = t::lib::TestBuilder->new;
38 subtest 'strip_limit' => sub {
39 # This is the query I found that triggered bug 8594.
40 my $sql = "SELECT aqorders.ordernumber, biblio.title, biblio.biblionumber, items.homebranch,
41 aqorders.entrydate, aqorders.datereceived,
42 (SELECT DATE(datetime) FROM statistics
43 WHERE itemnumber=items.itemnumber AND
44 (type='return' OR type='issue') LIMIT 1)
47 (SELECT DATE(datetime) FROM statistics
48 WHERE itemnumber=items.itemnumber AND
49 (type='return' OR type='issue') LIMIT 1),
50 aqorders.datereceived), aqorders.entrydate) AS totaldays
52 LEFT JOIN biblio USING (biblionumber)
53 LEFT JOIN items ON (items.biblionumber = biblio.biblionumber
54 AND dateaccessioned=aqorders.datereceived)
55 WHERE (entrydate >= '2011-01-01' AND (datereceived < '2011-02-01' OR datereceived IS NULL))
56 AND items.homebranch LIKE 'INFO'
59 my ($res_sql, $res_lim1, $res_lim2) = C4::Reports::Guided::strip_limit($sql);
60 is($res_sql, $sql, "Not breaking subqueries");
61 is($res_lim1, 0, "Returns correct default offset");
62 is($res_lim2, undef, "Returns correct default LIMIT");
64 # Now the same thing, but we want it to remove the LIMIT from the end
66 my $test_sql = $res_sql . " LIMIT 242";
67 ($res_sql, $res_lim1, $res_lim2) = C4::Reports::Guided::strip_limit($test_sql);
68 # The replacement drops a ' ' where the limit was
69 is(trim($res_sql), $sql, "Correctly removes only final LIMIT");
70 is($res_lim1, 0, "Returns correct default offset");
71 is($res_lim2, 242, "Returns correct extracted LIMIT");
73 $test_sql = $res_sql . " LIMIT 13,242";
74 ($res_sql, $res_lim1, $res_lim2) = C4::Reports::Guided::strip_limit($test_sql);
75 # The replacement drops a ' ' where the limit was
76 is(trim($res_sql), $sql, "Correctly removes only final LIMIT (with offset)");
77 is($res_lim1, 13, "Returns correct extracted offset");
78 is($res_lim2, 242, "Returns correct extracted LIMIT");
80 # After here is the simpler case, where there isn't a WHERE clause to worry
83 # First case with nothing to change
84 $sql = "SELECT * FROM items";
85 ($res_sql, $res_lim1, $res_lim2) = C4::Reports::Guided::strip_limit($sql);
86 is($res_sql, $sql, "Not breaking simple queries");
87 is($res_lim1, 0, "Returns correct default offset");
88 is($res_lim2, undef, "Returns correct default LIMIT");
90 $test_sql = $sql . " LIMIT 242";
91 ($res_sql, $res_lim1, $res_lim2) = C4::Reports::Guided::strip_limit($test_sql);
92 is(trim($res_sql), $sql, "Correctly removes LIMIT in simple case");
93 is($res_lim1, 0, "Returns correct default offset");
94 is($res_lim2, 242, "Returns correct extracted LIMIT");
96 $test_sql = $sql . " LIMIT 13,242";
97 ($res_sql, $res_lim1, $res_lim2) = C4::Reports::Guided::strip_limit($test_sql);
98 is(trim($res_sql), $sql, "Correctly removes LIMIT in simple case (with offset)");
99 is($res_lim1, 13, "Returns correct extracted offset");
100 is($res_lim2, 242, "Returns correct extracted LIMIT");
103 $_->delete for Koha::AuthorisedValues->search({ category => 'XXX' });
104 Koha::AuthorisedValue->new({category => 'LOC'})->store;
106 subtest 'GetReservedAuthorisedValues' => sub {
108 # This one will catch new reserved words not added
109 # to GetReservedAuthorisedValues
116 'biblio_framework' => 1,
119 my $reserved_authorised_values = GetReservedAuthorisedValues();
120 is_deeply(\%test_authval, $reserved_authorised_values,
121 'GetReservedAuthorisedValues returns a fixed list');
124 subtest 'IsAuthorisedValueValid' => sub {
126 ok( IsAuthorisedValueValid('LOC'),
127 'User defined authorised value category is valid');
129 ok( ! IsAuthorisedValueValid('XXX'),
130 'Not defined authorised value category is invalid');
132 # Loop through the reserved authorised values
133 foreach my $authorised_value ( keys %{GetReservedAuthorisedValues()} ) {
134 ok( IsAuthorisedValueValid($authorised_value),
135 '\''.$authorised_value.'\' is a reserved word, and thus a valid authorised value');
139 subtest 'GetParametersFromSQL+ValidateSQLParameters' => sub {
144 WHERE YEAR(timestamp) = <<Year|custom_list>> AND
145 branchcode = <<Branch|branches>> AND
146 borrowernumber = <<Borrower>>
149 my @test_parameters_with_custom_list = (
150 { 'name' => 'Year', 'authval' => 'custom_list' },
151 { 'name' => 'Branch', 'authval' => 'branches' },
152 { 'name' => 'Borrower', 'authval' => undef }
155 is_deeply( GetParametersFromSQL($test_query_1), \@test_parameters_with_custom_list,
156 'SQL params are correctly parsed');
158 my @problematic_parameters = ();
159 push @problematic_parameters, { 'name' => 'Year', 'authval' => 'custom_list' };
160 is_deeply( ValidateSQLParameters( $test_query_1 ),
161 \@problematic_parameters,
162 '\'custom_list\' not a valid category' );
167 WHERE YEAR(timestamp) = <<Year|date>> AND
168 branchcode = <<Branch|branches>> AND
169 borrowernumber = <<Borrower|LOC>>
172 is_deeply( ValidateSQLParameters( $test_query_2 ),
174 'All parameters valid, empty problematic authvals list'
178 subtest 'get_saved_reports' => sub {
180 my $dbh = C4::Context->dbh;
181 $dbh->do(q|DELETE FROM saved_sql|);
182 $dbh->do(q|DELETE FROM saved_reports|);
185 my $count = scalar @{ get_saved_reports() };
186 is( $count, 0, "There is no report" );
190 my $id = $builder->build({ source => 'Borrower' })->{ borrowernumber };
191 push @report_ids, save_report({
192 borrowernumber => $id,
197 subgroup => "subgroup$id",
200 cache_expiry => "null",
205 like( $report_ids[0], '/^\d+$/', "Save_report returns an id for first" );
206 like( $report_ids[1], '/^\d+$/', "Save_report returns an id for second" );
207 like( $report_ids[2], '/^\d+$/', "Save_report returns an id for third" );
209 is( scalar @{ get_saved_reports() },
210 $count, "$count reports have been added" );
212 is( scalar @{ get_saved_reports( $report_ids[0] ) },
213 1, "filter takes report id" );
216 is (delete_report(),undef, "Without id delete_report returns undef");
218 is( delete_report( $report_ids[0] ), 1, "report 1 is deleted" );
221 is( scalar @{ get_saved_reports() }, $count, "Report1 has been deleted" );
223 is( delete_report( $report_ids[1], $report_ids[2] ), 2, "report 2 and 3 are deleted" );
226 is( scalar @{ get_saved_reports() },
227 $count, "Report2 and report3 have been deleted" );
229 my $sth = execute_query('SELECT COUNT(*) FROM systempreferences', 0, 10);
230 my $results = $sth->fetchall_arrayref;
231 is(scalar @$results, 1, 'running a query returned a result');
233 my $version = C4::Context->preference('Version');
234 $sth = execute_query(
235 'SELECT value FROM systempreferences WHERE variable = ?',
240 $results = $sth->fetchall_arrayref;
244 'running a query with a parameter returned the expected result'
247 # for next test, we want to let execute_query capture any SQL errors
248 $dbh->{RaiseError} = 0;
250 warning_like { ($sth, $errors) = execute_query(
251 'SELECT surname FRM borrowers', # error in the query is intentional
253 qr/^DBD::mysql::st execute failed: You have an error in your SQL syntax;/,
254 "Wrong SQL syntax raises warning";
256 defined($errors) && exists($errors->{queryerr}),
257 'attempting to run a report with an SQL syntax error returns error message (Bug 12214)'
260 is_deeply( get_report_areas(), [ 'CIRC', 'CAT', 'PAT', 'ACQ', 'ACC', 'SER' ],
261 "get_report_areas returns the correct array of report areas");
264 $schema->storage->txn_rollback;
268 $s =~ s/^\s*(.*?)\s*$/$1/s;