t/db_dependent/Reports/Guided.t

   1 # Copyright 2012 Catalyst IT Ltd.
   2 # Copyright 2015 Koha Development team
   3 #
   4 # This file is part of Koha.
   5 #
   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.
  10 #
  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.
  15 #
  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>.
  18
  19 use Modern::Perl;
  20
  21 use Test::More tests => 9;
  22 use Test::Warn;
  23
  24 use t::lib::TestBuilder;
  25 use C4::Context;
  26 use Koha::Database;
  27 use Koha::Reports;
  28
  29 use_ok('C4::Reports::Guided');
  30 can_ok(
  31     'C4::Reports::Guided',
  32     qw(save_report delete_report execute_query)
  33 );
  34
  35 my $schema = Koha::Database->new->schema;
  36 $schema->storage->txn_begin;
  37 my $builder = t::lib::TestBuilder->new;
  38
  39 subtest 'strip_limit' => sub {
  40     # This is the query I found that triggered bug 8594.
  41     my $sql = "SELECT aqorders.ordernumber, biblio.title, biblio.biblionumber, items.homebranch,
  42         aqorders.entrydate, aqorders.datereceived,
  43         (SELECT DATE(datetime) FROM statistics
  44             WHERE itemnumber=items.itemnumber AND
  45                 (type='return' OR type='issue') LIMIT 1)
  46         AS shelvedate,
  47         DATEDIFF(COALESCE(
  48             (SELECT DATE(datetime) FROM statistics
  49                 WHERE itemnumber=items.itemnumber AND
  50                 (type='return' OR type='issue') LIMIT 1),
  51         aqorders.datereceived), aqorders.entrydate) AS totaldays
  52     FROM aqorders
  53     LEFT JOIN biblio USING (biblionumber)
  54     LEFT JOIN items ON (items.biblionumber = biblio.biblionumber
  55         AND dateaccessioned=aqorders.datereceived)
  56     WHERE (entrydate >= '2011-01-01' AND (datereceived < '2011-02-01' OR datereceived IS NULL))
  57         AND items.homebranch LIKE 'INFO'
  58     ORDER BY title";
  59
  60     my ($res_sql, $res_lim1, $res_lim2) = C4::Reports::Guided::strip_limit($sql);
  61     is($res_sql, $sql, "Not breaking subqueries");
  62     is($res_lim1, 0, "Returns correct default offset");
  63     is($res_lim2, undef, "Returns correct default LIMIT");
  64
  65     # Now the same thing, but we want it to remove the LIMIT from the end
  66
  67     my $test_sql = $res_sql . " LIMIT 242";
  68     ($res_sql, $res_lim1, $res_lim2) = C4::Reports::Guided::strip_limit($test_sql);
  69     # The replacement drops a ' ' where the limit was
  70     is(trim($res_sql), $sql, "Correctly removes only final LIMIT");
  71     is($res_lim1, 0, "Returns correct default offset");
  72     is($res_lim2, 242, "Returns correct extracted LIMIT");
  73
  74     $test_sql = $res_sql . " LIMIT 13,242";
  75     ($res_sql, $res_lim1, $res_lim2) = C4::Reports::Guided::strip_limit($test_sql);
  76     # The replacement drops a ' ' where the limit was
  77     is(trim($res_sql), $sql, "Correctly removes only final LIMIT (with offset)");
  78     is($res_lim1, 13, "Returns correct extracted offset");
  79     is($res_lim2, 242, "Returns correct extracted LIMIT");
  80
  81     # After here is the simpler case, where there isn't a WHERE clause to worry
  82     # about.
  83
  84     # First case with nothing to change
  85     $sql = "SELECT * FROM items";
  86     ($res_sql, $res_lim1, $res_lim2) = C4::Reports::Guided::strip_limit($sql);
  87     is($res_sql, $sql, "Not breaking simple queries");
  88     is($res_lim1, 0, "Returns correct default offset");
  89     is($res_lim2, undef, "Returns correct default LIMIT");
  90
  91     $test_sql = $sql . " LIMIT 242";
  92     ($res_sql, $res_lim1, $res_lim2) = C4::Reports::Guided::strip_limit($test_sql);
  93     is(trim($res_sql), $sql, "Correctly removes LIMIT in simple case");
  94     is($res_lim1, 0, "Returns correct default offset");
  95     is($res_lim2, 242, "Returns correct extracted LIMIT");
  96
  97     $test_sql = $sql . " LIMIT 13,242";
  98     ($res_sql, $res_lim1, $res_lim2) = C4::Reports::Guided::strip_limit($test_sql);
  99     is(trim($res_sql), $sql, "Correctly removes LIMIT in simple case (with offset)");
 100     is($res_lim1, 13, "Returns correct extracted offset");
 101     is($res_lim2, 242, "Returns correct extracted LIMIT");
 102 };
 103
 104 $_->delete for Koha::AuthorisedValues->search({ category => 'XXX' });
 105 Koha::AuthorisedValue->new({category => 'LOC'})->store;
 106
 107 subtest 'GetReservedAuthorisedValues' => sub {
 108     plan tests => 1;
 109     # This one will catch new reserved words not added
 110     # to GetReservedAuthorisedValues
 111     my %test_authval = (
 112         'date' => 1,
 113         'branches' => 1,
 114         'itemtypes' => 1,
 115         'cn_source' => 1,
 116         'categorycode' => 1,
 117         'biblio_framework' => 1,
 118     );
 119
 120     my $reserved_authorised_values = GetReservedAuthorisedValues();
 121     is_deeply(\%test_authval, $reserved_authorised_values,
 122                 'GetReservedAuthorisedValues returns a fixed list');
 123 };
 124
 125 subtest 'IsAuthorisedValueValid' => sub {
 126     plan tests => 8;
 127     ok( IsAuthorisedValueValid('LOC'),
 128         'User defined authorised value category is valid');
 129
 130     ok( ! IsAuthorisedValueValid('XXX'),
 131         'Not defined authorised value category is invalid');
 132
 133     # Loop through the reserved authorised values
 134     foreach my $authorised_value ( keys %{GetReservedAuthorisedValues()} ) {
 135         ok( IsAuthorisedValueValid($authorised_value),
 136             '\''.$authorised_value.'\' is a reserved word, and thus a valid authorised value');
 137     }
 138 };
 139
 140 subtest 'GetParametersFromSQL+ValidateSQLParameters' => sub  {
 141     plan tests => 3;
 142     my $test_query_1 = "
 143         SELECT date_due
 144         FROM old_issues
 145         WHERE YEAR(timestamp) = <<Year|custom_list>> AND
 146               branchcode = <<Branch|branches>> AND
 147               borrowernumber = <<Borrower>>
 148     ";
 149
 150     my @test_parameters_with_custom_list = (
 151         { 'name' => 'Year', 'authval' => 'custom_list' },
 152         { 'name' => 'Branch', 'authval' => 'branches' },
 153         { 'name' => 'Borrower', 'authval' => undef }
 154     );
 155
 156     is_deeply( GetParametersFromSQL($test_query_1), \@test_parameters_with_custom_list,
 157         'SQL params are correctly parsed');
 158
 159     my @problematic_parameters = ();
 160     push @problematic_parameters, { 'name' => 'Year', 'authval' => 'custom_list' };
 161     is_deeply( ValidateSQLParameters( $test_query_1 ),
 162                \@problematic_parameters,
 163                '\'custom_list\' not a valid category' );
 164
 165     my $test_query_2 = "
 166         SELECT date_due
 167         FROM old_issues
 168         WHERE YEAR(timestamp) = <<Year|date>> AND
 169               branchcode = <<Branch|branches>> AND
 170               borrowernumber = <<Borrower|LOC>>
 171     ";
 172
 173     is_deeply( ValidateSQLParameters( $test_query_2 ),
 174         [],
 175         'All parameters valid, empty problematic authvals list'
 176     );
 177 };
 178
 179 subtest 'get_saved_reports' => sub {
 180     plan tests => 16;
 181     my $dbh = C4::Context->dbh;
 182     $dbh->do(q|DELETE FROM saved_sql|);
 183     $dbh->do(q|DELETE FROM saved_reports|);
 184
 185     #Test save_report
 186     my $count = scalar @{ get_saved_reports() };
 187     is( $count, 0, "There is no report" );
 188
 189     my @report_ids;
 190     foreach my $ii ( 1..3 ) {
 191         my $id = $builder->build({ source => 'Borrower' })->{ borrowernumber };
 192         push @report_ids, save_report({
 193             borrowernumber => $id,
 194             sql            => "SQL$id",
 195             name           => "Name$id",
 196             area           => "area$ii", # ii vs id area is varchar(6)
 197             group          => "group$id",
 198             subgroup       => "subgroup$id",
 199             type           => "type$id",
 200             notes          => "note$id",
 201             cache_expiry   => undef,
 202             public         => 0,
 203         });
 204         $count++;
 205     }
 206     like( $report_ids[0], '/^\d+$/', "Save_report returns an id for first" );
 207     like( $report_ids[1], '/^\d+$/', "Save_report returns an id for second" );
 208     like( $report_ids[2], '/^\d+$/', "Save_report returns an id for third" );
 209
 210     is( scalar @{ get_saved_reports() },
 211         $count, "$count reports have been added" );
 212
 213     ok( 0 < scalar @{ get_saved_reports( $report_ids[0] ) }, "filter takes report id" );
 214
 215     #Test delete_report
 216     is (delete_report(),undef, "Without id delete_report returns undef");
 217
 218     is( delete_report( $report_ids[0] ), 1, "report 1 is deleted" );
 219     $count--;
 220
 221     is( scalar @{ get_saved_reports() }, $count, "Report1 has been deleted" );
 222
 223     is( delete_report( $report_ids[1], $report_ids[2] ), 2, "report 2 and 3 are deleted" );
 224     $count -= 2;
 225
 226     is( scalar @{ get_saved_reports() },
 227         $count, "Report2 and report3 have been deleted" );
 228
 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');
 232
 233     my $version = C4::Context->preference('Version');
 234     $sth = execute_query(
 235         'SELECT value FROM systempreferences WHERE variable = ?',
 236         0,
 237         10,
 238         [ 'Version' ],
 239     );
 240     $results = $sth->fetchall_arrayref;
 241     is_deeply(
 242         $results,
 243         [ [ $version ] ],
 244         'running a query with a parameter returned the expected result'
 245     );
 246
 247     # for next test, we want to let execute_query capture any SQL errors
 248     $dbh->{RaiseError} = 0;
 249     my $errors;
 250     warning_like { ($sth, $errors) = execute_query(
 251             'SELECT surname FRM borrowers',  # error in the query is intentional
 252             0, 10 ) }
 253             qr/^DBD::mysql::st execute failed: You have an error in your SQL syntax;/,
 254             "Wrong SQL syntax raises warning";
 255     ok(
 256         defined($errors) && exists($errors->{queryerr}),
 257         'attempting to run a report with an SQL syntax error returns error message (Bug 12214)'
 258     );
 259
 260     is_deeply( get_report_areas(), [ 'CIRC', 'CAT', 'PAT', 'ACQ', 'ACC', 'SER' ],
 261         "get_report_areas returns the correct array of report areas");
 262 };
 263
 264 subtest 'Ensure last_run is populated' => sub {
 265     plan tests => 3;
 266
 267     my $rs = Koha::Database->new()->schema()->resultset('SavedSql');
 268
 269     my $report = $rs->new(
 270         {
 271             report_name => 'Test Report',
 272             savedsql    => 'SELECT * FROM branches',
 273             notes       => undef,
 274         }
 275     )->insert();
 276
 277     is( $report->last_run, undef, 'Newly created report has null last_run ' );
 278
 279     execute_query( $report->savedsql, undef, undef, undef, $report->id );
 280     $report->discard_changes();
 281
 282     isnt( $report->last_run, undef, 'First run of report populates last_run' );
 283
 284     my $previous_last_run = $report->last_run;
 285     sleep(1); # last_run is stored to the second, so we need to ensure at least one second has passed between runs
 286     execute_query( $report->savedsql, undef, undef, undef, $report->id );
 287     $report->discard_changes();
 288
 289     isnt( $report->last_run, $previous_last_run, 'Second run of report updates last_run' );
 290 };
 291
 292 subtest 'convert_sql' => sub {
 293     plan tests => 4;
 294
 295     my $sql = q|
 296     SELECT biblionumber, ExtractValue(marcxml,
 297 'count(//datafield[@tag="505"])') AS count505
 298     FROM biblioitems
 299     HAVING count505 > 1|;
 300     my $expected_converted_sql = q|
 301     SELECT biblionumber, ExtractValue(metadata,
 302 'count(//datafield[@tag="505"])') AS count505
 303     FROM biblio_metadata
 304     HAVING count505 > 1|;
 305
 306     is( C4::Reports::Guided::convert_sql( $sql ), $expected_converted_sql, "Simple query should have been correctly converted");
 307
 308     $sql = q|
 309     SELECT biblionumber, substring(
 310 ExtractValue(marcxml,'//controlfield[@tag="008"]'), 8,4 ) AS 'PUB DATE',
 311 title
 312     FROM biblioitems
 313     INNER JOIN biblio USING (biblionumber)
 314     WHERE biblionumber = 14|;
 315
 316     $expected_converted_sql = q|
 317     SELECT biblionumber, substring(
 318 ExtractValue(metadata,'//controlfield[@tag="008"]'), 8,4 ) AS 'PUB DATE',
 319 title
 320     FROM biblio_metadata
 321     INNER JOIN biblio USING (biblionumber)
 322     WHERE biblionumber = 14|;
 323     is( C4::Reports::Guided::convert_sql( $sql ), $expected_converted_sql, "Query with biblio info should have been correctly converted");
 324
 325     $sql = q|
 326     SELECT concat(b.title, ' ', ExtractValue(m.marcxml,
 327 '//datafield[@tag="245"]/subfield[@code="b"]')) AS title, b.author,
 328 count(h.reservedate) AS 'holds'
 329     FROM biblio b
 330     LEFT JOIN biblioitems m USING (biblionumber)
 331     LEFT JOIN reserves h ON (b.biblionumber=h.biblionumber)
 332     GROUP BY b.biblionumber
 333     HAVING count(h.reservedate) >= 42|;
 334
 335     $expected_converted_sql = q|
 336     SELECT concat(b.title, ' ', ExtractValue(m.metadata,
 337 '//datafield[@tag="245"]/subfield[@code="b"]')) AS title, b.author,
 338 count(h.reservedate) AS 'holds'
 339     FROM biblio b
 340     LEFT JOIN biblio_metadata m USING (biblionumber)
 341     LEFT JOIN reserves h ON (b.biblionumber=h.biblionumber)
 342     GROUP BY b.biblionumber
 343     HAVING count(h.reservedate) >= 42|;
 344     is( C4::Reports::Guided::convert_sql( $sql ), $expected_converted_sql, "Query with 2 joins should have been correctly converted");
 345
 346     $sql = q|
 347     SELECT t1.marcxml AS first, t2.marcxml AS second,
 348     FROM biblioitems t1
 349     LEFT JOIN biblioitems t2 USING ( biblionumber )|;
 350
 351     $expected_converted_sql = q|
 352     SELECT t1.metadata AS first, t2.metadata AS second,
 353     FROM biblio_metadata t1
 354     LEFT JOIN biblio_metadata t2 USING ( biblionumber )|;
 355     is( C4::Reports::Guided::convert_sql( $sql ), $expected_converted_sql, "Query with multiple instances of marcxml and biblioitems should have them all replaced");
 356 };
 357
 358 $schema->storage->txn_rollback;
 359
 360 sub trim {
 361     my ($s) = @_;
 362     $s =~ s/^\s*(.*?)\s*$/$1/s;
 363     return $s;
 364 }