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 => 11;
24 use t::lib::TestBuilder;
29 use Koha::Notice::Messages;
31 use_ok('C4::Reports::Guided', qw( execute_query save_report delete_report strip_limit GetReservedAuthorisedValues IsAuthorisedValueValid GetParametersFromSQL ValidateSQLParameters get_saved_reports update_sql get_report_areas convert_sql EmailReport nb_rows ));
33 'C4::Reports::Guided',
34 qw(save_report delete_report execute_query)
37 my $schema = Koha::Database->new->schema;
38 $schema->storage->txn_begin;
39 my $builder = t::lib::TestBuilder->new;
41 subtest 'strip_limit' => sub {
42 # This is the query I found that triggered bug 8594.
43 my $sql = "SELECT aqorders.ordernumber, biblio.title, biblio.biblionumber, items.homebranch,
44 aqorders.entrydate, aqorders.datereceived,
45 (SELECT DATE(datetime) FROM statistics
46 WHERE itemnumber=items.itemnumber AND
47 (type='return' OR type='issue') LIMIT 1)
50 (SELECT DATE(datetime) FROM statistics
51 WHERE itemnumber=items.itemnumber AND
52 (type='return' OR type='issue') LIMIT 1),
53 aqorders.datereceived), aqorders.entrydate) AS totaldays
55 LEFT JOIN biblio USING (biblionumber)
56 LEFT JOIN items ON (items.biblionumber = biblio.biblionumber
57 AND dateaccessioned=aqorders.datereceived)
58 WHERE (entrydate >= '2011-01-01' AND (datereceived < '2011-02-01' OR datereceived IS NULL))
59 AND items.homebranch LIKE 'INFO'
62 my ($res_sql, $res_lim1, $res_lim2) = C4::Reports::Guided::strip_limit($sql);
63 is($res_sql, $sql, "Not breaking subqueries");
64 is($res_lim1, 0, "Returns correct default offset");
65 is($res_lim2, undef, "Returns correct default LIMIT");
67 # Now the same thing, but we want it to remove the LIMIT from the end
69 my $test_sql = $res_sql . " LIMIT 242";
70 ($res_sql, $res_lim1, $res_lim2) = C4::Reports::Guided::strip_limit($test_sql);
71 # The replacement drops a ' ' where the limit was
72 is(trim($res_sql), $sql, "Correctly removes only final LIMIT");
73 is($res_lim1, 0, "Returns correct default offset");
74 is($res_lim2, 242, "Returns correct extracted LIMIT");
76 $test_sql = $res_sql . " LIMIT 13,242";
77 ($res_sql, $res_lim1, $res_lim2) = C4::Reports::Guided::strip_limit($test_sql);
78 # The replacement drops a ' ' where the limit was
79 is(trim($res_sql), $sql, "Correctly removes only final LIMIT (with offset)");
80 is($res_lim1, 13, "Returns correct extracted offset");
81 is($res_lim2, 242, "Returns correct extracted LIMIT");
83 # After here is the simpler case, where there isn't a WHERE clause to worry
86 # First case with nothing to change
87 $sql = "SELECT * FROM items";
88 ($res_sql, $res_lim1, $res_lim2) = C4::Reports::Guided::strip_limit($sql);
89 is($res_sql, $sql, "Not breaking simple queries");
90 is($res_lim1, 0, "Returns correct default offset");
91 is($res_lim2, undef, "Returns correct default LIMIT");
93 $test_sql = $sql . " LIMIT 242";
94 ($res_sql, $res_lim1, $res_lim2) = C4::Reports::Guided::strip_limit($test_sql);
95 is(trim($res_sql), $sql, "Correctly removes LIMIT in simple case");
96 is($res_lim1, 0, "Returns correct default offset");
97 is($res_lim2, 242, "Returns correct extracted LIMIT");
99 $test_sql = $sql . " LIMIT 13,242";
100 ($res_sql, $res_lim1, $res_lim2) = C4::Reports::Guided::strip_limit($test_sql);
101 is(trim($res_sql), $sql, "Correctly removes LIMIT in simple case (with offset)");
102 is($res_lim1, 13, "Returns correct extracted offset");
103 is($res_lim2, 242, "Returns correct extracted LIMIT");
106 $_->delete for Koha::AuthorisedValues->search({ category => 'XXX' });
107 Koha::AuthorisedValue->new({category => 'LOC'})->store;
109 subtest 'GetReservedAuthorisedValues' => sub {
111 # This one will catch new reserved words not added
112 # to GetReservedAuthorisedValues
119 'biblio_framework' => 1,
121 'cash_registers' => 1,
126 my $reserved_authorised_values = GetReservedAuthorisedValues();
127 is_deeply(\%test_authval, $reserved_authorised_values,
128 'GetReservedAuthorisedValues returns a fixed list');
131 subtest 'IsAuthorisedValueValid' => sub {
133 ok( IsAuthorisedValueValid('LOC'),
134 'User defined authorised value category is valid');
136 ok( ! IsAuthorisedValueValid('XXX'),
137 'Not defined authorised value category is invalid');
139 # Loop through the reserved authorised values
140 foreach my $authorised_value ( keys %{GetReservedAuthorisedValues()} ) {
141 ok( IsAuthorisedValueValid($authorised_value),
142 '\''.$authorised_value.'\' is a reserved word, and thus a valid authorised value');
146 subtest 'GetParametersFromSQL+ValidateSQLParameters' => sub {
151 WHERE YEAR(timestamp) = <<Year|custom_list>> AND
152 branchcode = <<Branch|branches>> AND
153 borrowernumber = <<Borrower>> AND
154 itemtype = <<Item type|itemtypes:all>>
157 my @test_parameters_with_custom_list = (
158 { 'name' => 'Year', 'authval' => 'custom_list' },
159 { 'name' => 'Branch', 'authval' => 'branches' },
160 { 'name' => 'Borrower', 'authval' => undef },
161 { 'name' => 'Item type', 'authval' => 'itemtypes' }
164 is_deeply( GetParametersFromSQL($test_query_1), \@test_parameters_with_custom_list,
165 'SQL params are correctly parsed');
167 my @problematic_parameters = ();
168 push @problematic_parameters, { 'name' => 'Year', 'authval' => 'custom_list' };
169 is_deeply( ValidateSQLParameters( $test_query_1 ),
170 \@problematic_parameters,
171 '\'custom_list\' not a valid category' );
176 WHERE YEAR(timestamp) = <<Year|date>> AND
177 branchcode = <<Branch|branches>> AND
178 borrowernumber = <<Borrower|LOC>>
181 is_deeply( ValidateSQLParameters( $test_query_2 ),
183 'All parameters valid, empty problematic authvals list'
187 subtest 'get_saved_reports' => sub {
189 my $dbh = C4::Context->dbh;
190 $dbh->do(q|DELETE FROM saved_sql|);
191 $dbh->do(q|DELETE FROM saved_reports|);
194 my $count = scalar @{ get_saved_reports() };
195 is( $count, 0, "There is no report" );
198 foreach my $ii ( 1..3 ) {
199 my $id = $builder->build({ source => 'Borrower' })->{ borrowernumber };
200 push @report_ids, save_report({
201 borrowernumber => $id,
204 area => "area$ii", # ii vs id area is varchar(6)
206 subgroup => "subgroup$id",
209 cache_expiry => undef,
214 like( $report_ids[0], '/^\d+$/', "Save_report returns an id for first" );
215 like( $report_ids[1], '/^\d+$/', "Save_report returns an id for second" );
216 like( $report_ids[2], '/^\d+$/', "Save_report returns an id for third" );
218 is( scalar @{ get_saved_reports() },
219 $count, "$count reports have been added" );
221 ok( 0 < scalar @{ get_saved_reports( $report_ids[0] ) }, "filter takes report id" );
223 my $r1 = Koha::Reports->find($report_ids[0]);
224 $r1 = update_sql($r1->id, { %{$r1->unblessed}, borrowernumber => $r1->borrowernumber, name => 'Just another report' });
225 is( $r1->cache_expiry, 300, 'cache_expiry has the correct default value, from DBMS' );
228 is (delete_report(),undef, "Without id delete_report returns undef");
230 is( delete_report( $report_ids[0] ), 1, "report 1 is deleted" );
233 is( scalar @{ get_saved_reports() }, $count, "Report1 has been deleted" );
235 is( delete_report( $report_ids[1], $report_ids[2] ), 2, "report 2 and 3 are deleted" );
238 is( scalar @{ get_saved_reports() },
239 $count, "Report2 and report3 have been deleted" );
241 my $sth = execute_query('SELECT COUNT(*) FROM systempreferences', 0, 10);
242 my $results = $sth->fetchall_arrayref;
243 is(scalar @$results, 1, 'running a query returned a result');
245 my $version = C4::Context->preference('Version');
246 $sth = execute_query(
247 'SELECT value FROM systempreferences WHERE variable = ?',
252 $results = $sth->fetchall_arrayref;
256 'running a query with a parameter returned the expected result'
259 # for next test, we want to let execute_query capture any SQL errors
261 warning_like {local $dbh->{RaiseError} = 0; ($sth, $errors) = execute_query(
262 'SELECT surname FRM borrowers', # error in the query is intentional
264 qr/DBD::mysql::st execute failed: You have an error in your SQL syntax;/,
265 "Wrong SQL syntax raises warning";
267 defined($errors) && exists($errors->{queryerr}),
268 'attempting to run a report with an SQL syntax error returns error message (Bug 12214)'
271 is_deeply( get_report_areas(), [ 'CIRC', 'CAT', 'PAT', 'ACQ', 'ACC', 'SER' ],
272 "get_report_areas returns the correct array of report areas");
275 subtest 'Ensure last_run is populated' => sub {
278 my $rs = Koha::Database->new()->schema()->resultset('SavedSql');
280 my $report = $rs->new(
282 report_name => 'Test Report',
283 savedsql => 'SELECT * FROM branches',
288 is( $report->last_run, undef, 'Newly created report has null last_run ' );
290 execute_query( $report->savedsql, undef, undef, undef, $report->id );
291 $report->discard_changes();
293 isnt( $report->last_run, undef, 'First run of report populates last_run' );
295 my $previous_last_run = $report->last_run;
296 sleep(1); # last_run is stored to the second, so we need to ensure at least one second has passed between runs
297 execute_query( $report->savedsql, undef, undef, undef, $report->id );
298 $report->discard_changes();
300 isnt( $report->last_run, $previous_last_run, 'Second run of report updates last_run' );
303 subtest 'convert_sql' => sub {
307 SELECT biblionumber, ExtractValue(marcxml,
308 'count(//datafield[@tag="505"])') AS count505
310 HAVING count505 > 1|;
311 my $expected_converted_sql = q|
312 SELECT biblionumber, ExtractValue(metadata,
313 'count(//datafield[@tag="505"])') AS count505
315 HAVING count505 > 1|;
317 is( C4::Reports::Guided::convert_sql( $sql ), $expected_converted_sql, "Simple query should have been correctly converted");
320 SELECT biblionumber, substring(
321 ExtractValue(marcxml,'//controlfield[@tag="008"]'), 8,4 ) AS 'PUB DATE',
324 INNER JOIN biblio USING (biblionumber)
325 WHERE biblionumber = 14|;
327 $expected_converted_sql = q|
328 SELECT biblionumber, substring(
329 ExtractValue(metadata,'//controlfield[@tag="008"]'), 8,4 ) AS 'PUB DATE',
332 INNER JOIN biblio USING (biblionumber)
333 WHERE biblionumber = 14|;
334 is( C4::Reports::Guided::convert_sql( $sql ), $expected_converted_sql, "Query with biblio info should have been correctly converted");
337 SELECT concat(b.title, ' ', ExtractValue(m.marcxml,
338 '//datafield[@tag="245"]/subfield[@code="b"]')) AS title, b.author,
339 count(h.reservedate) AS 'holds'
341 LEFT JOIN biblioitems m USING (biblionumber)
342 LEFT JOIN reserves h ON (b.biblionumber=h.biblionumber)
343 GROUP BY b.biblionumber
344 HAVING count(h.reservedate) >= 42|;
346 $expected_converted_sql = q|
347 SELECT concat(b.title, ' ', ExtractValue(m.metadata,
348 '//datafield[@tag="245"]/subfield[@code="b"]')) AS title, b.author,
349 count(h.reservedate) AS 'holds'
351 LEFT JOIN biblio_metadata m USING (biblionumber)
352 LEFT JOIN reserves h ON (b.biblionumber=h.biblionumber)
353 GROUP BY b.biblionumber
354 HAVING count(h.reservedate) >= 42|;
355 is( C4::Reports::Guided::convert_sql( $sql ), $expected_converted_sql, "Query with 2 joins should have been correctly converted");
358 SELECT t1.marcxml AS first, t2.marcxml AS second,
360 LEFT JOIN biblioitems t2 USING ( biblionumber )|;
362 $expected_converted_sql = q|
363 SELECT t1.metadata AS first, t2.metadata AS second,
364 FROM biblio_metadata t1
365 LEFT JOIN biblio_metadata t2 USING ( biblionumber )|;
366 is( C4::Reports::Guided::convert_sql( $sql ), $expected_converted_sql, "Query with multiple instances of marcxml and biblioitems should have them all replaced");
369 subtest 'Email report test' => sub {
372 my $dbh = C4::Context->dbh;
374 my $id1 = $builder->build({ source => 'Borrower',value => { surname => 'mailer', email => 'a@b.com', emailpro => 'b@c.com' } })->{ borrowernumber };
375 my $id2 = $builder->build({ source => 'Borrower',value => { surname => 'nomailer', email => undef, emailpro => 'd@e.com' } })->{ borrowernumber };
376 my $id3 = $builder->build({ source => 'Borrower',value => { surname => 'norman', email => 'a@b.com', emailpro => undef } })->{ borrowernumber };
377 my $report1 = $builder->build({ source => 'SavedSql', value => { savedsql => "SELECT surname,borrowernumber,email,emailpro FROM borrowers WHERE borrowernumber IN ($id1,$id2,$id3)" } })->{ id };
378 my $report2 = $builder->build({ source => 'SavedSql', value => { savedsql => "SELECT potato FROM mashed" } })->{ id };
380 my $letter1 = $builder->build({
383 content => "[% surname %]",
385 message_transport_type => 'email',
389 my $letter2 = $builder->build({
392 content => "[% firstname %]",
394 message_transport_type => 'email',
399 my $letter3 = $builder->build({
402 content => "[% surname %]",
404 message_transport_type => 'email',
409 my $message_count = Koha::Notice::Messages->search({})->count;
411 my ( $emails, $errors ) = C4::Reports::Guided::EmailReport();
412 is( $errors->[0]{FATAL}, 'MISSING_PARAMS', "Need to enter required params");
414 ($emails, $errors ) = C4::Reports::Guided::EmailReport({report_id => $report1, module => $letter1->{module}, code => $letter2->{code}});
415 is( $errors->[0]{FATAL}, 'NO_LETTER', "Must have a letter that exists");
417 # for next test, we want to let execute_query capture any SQL errors
418 warning_like { local $dbh->{RaiseError} = 0; ($emails, $errors ) = C4::Reports::Guided::EmailReport({report_id => $report2, module => $letter1->{module} , code => $letter1->{code} }) }
419 qr/DBD::mysql::st execute failed/,
420 'Error from bad report';
421 is( $errors->[0]{FATAL}, 'REPORT_FAIL', "Bad report returns failure");
423 ($emails, $errors ) = C4::Reports::Guided::EmailReport({report_id => $report1, module => $letter1->{module} , code => $letter1->{code} });
424 is( $errors->[0]{NO_FROM_COL} == 1 && $errors->[1]{NO_EMAIL_COL} == 2 && $errors->[2]{NO_FROM_COL} == 2, 1, "Correct warnings from the routine");
426 ($emails, $errors ) = C4::Reports::Guided::EmailReport({report_id => $report1, module => $letter1->{module} , code => $letter1->{code}, from => 'the@future.ooh' });
427 is( $errors->[0]{NO_EMAIL_COL}, 2, "Warning only for patron with no email");
429 is( $message_count, Koha::Notice::Messages->search({})->count, "Messages not added without commit");
431 ($emails, $errors ) = C4::Reports::Guided::EmailReport({report_id => $report1, module => $letter1->{module} , code => $letter1->{code}, from => 'the@future.ooh' });
432 is( $emails->[0]{letter}->{content}, "mailer", "Message has expected content");
433 is( $emails->[1]{letter}->{content}, "norman", "Message has expected content");
434 is( $emails->[0]{letter}->{'content-type'}, undef, "Message content type is not set for plain text mail");
436 ($emails, $errors ) = C4::Reports::Guided::EmailReport({report_id => $report1, module => $letter1->{module} , code => $letter1->{code}, from => 'the@future.ooh', email => 'emailpro' });
437 is_deeply( $errors, [{'NO_EMAIL_COL'=>3}],"We report missing email in emailpro column");
438 is( $emails->[0]->{to_address}, 'b@c.com', "Message uses correct email");
439 is( $emails->[1]->{to_address}, 'd@e.com', "Message uses correct email");
441 ($emails) = C4::Reports::Guided::EmailReport({report_id => $report1, module => $letter3->{module} , code => $letter3->{code}, from => 'the@future.ooh' });
442 is( $emails->[0]{letter}->{'content-type'}, 'text/html; charset="UTF-8"', "Message has expected content type");
446 $schema->storage->txn_rollback;
448 subtest 'nb_rows() tests' => sub {
452 my $dbh = C4::Context->dbh;
453 $schema->storage->txn_begin;
455 my $items_count = Koha::Items->search->count;
456 $builder->build_object({ class => 'Koha::Items' });
457 $builder->build_object({ class => 'Koha::Items' });
461 SELECT * FROM items xxx
464 my $nb_rows = nb_rows( $query );
466 is( $nb_rows, $items_count, 'nb_rows returns the right value' );
472 # for next test, we want to let execute_query capture any SQL errors
475 { $nb_rows = nb_rows( $bad_query ) }
476 qr/DBD::mysql::st execute failed:/,
477 'Bad queries raise a warning';
479 is( $nb_rows, 0, 'nb_rows returns 0 on bad queries' );
481 $schema->storage->txn_rollback;
486 $s =~ s/^\s*(.*?)\s*$/$1/s;