Open-ILS/src/perlmods/lib/OpenILS/Reporter/SQLBuilder.pm

   1 #-------------------------------------------------------------------------------------------------
   2 package OpenILS::Reporter::SQLBuilder;
   3
   4 sub new {
   5     my $class = shift;
   6     $class = ref($class) || $class;
   7
   8     return bless { _sql => undef } => $class;
   9 }
  10
  11 sub register_params {
  12     my $self  = shift;
  13     my $p = shift;
  14     $self->{_params} = $p;
  15 }
  16
  17 sub get_param {
  18     my $self = shift;
  19     my $p = shift;
  20     return $self->{_builder}->{_params}->{$p};
  21 }
  22
  23 sub set_builder {
  24     my $self = shift;
  25     $self->{_builder} = shift;
  26     return $self;
  27 }
  28
  29 sub builder {
  30     my $self = shift;
  31     return $self->{_builder};
  32 }
  33
  34 sub relative_time {
  35     my $self = shift;
  36     my $t = shift;
  37     $self->builder->{_relative_time} = $t if (defined $t);
  38     return $self->builder->{_relative_time};
  39 }
  40
  41 sub resultset_limit {
  42     my $self = shift;
  43     my $limit = shift;
  44     $self->builder->{_resultset_limit} = $limit if (defined $limit);
  45     return $self->builder->{_resultset_limit};
  46 }
  47
  48 sub resolve_param {
  49     my $self = shift;
  50     my $val = shift;
  51
  52     if (defined($val) && $val =~ /^::(.+)$/o) {
  53         $val = $self->get_param($1);
  54     }
  55
  56     if (defined($val) && !ref($val)) {
  57         $val =~ s/\\/\\\\/go;
  58         $val =~ s/"/\\"/go;
  59     }
  60
  61     return $val;
  62 }
  63
  64 sub parse_report {
  65     my $self = shift;
  66     my $report = shift;
  67
  68     my $rs = OpenILS::Reporter::SQLBuilder::ResultSet->new;
  69
  70     if (!$report->{order_by} || @{$report->{order_by}} == 0) {
  71         $report->{order_by} = $report->{select};
  72     }
  73
  74     $rs->is_subquery( 1 ) if ( $report->{alias} );
  75
  76     $rs ->set_builder( $self )
  77         ->set_subquery_alias( $report->{alias} )
  78         ->set_select( $report->{select} )
  79         ->set_from( $report->{from} )
  80         ->set_where( $report->{where} )
  81         ->set_having( $report->{having} )
  82         ->set_order_by( $report->{order_by} )
  83         ->set_pivot_data( $report->{pivot_data} )
  84         ->set_pivot_label( $report->{pivot_label} )
  85         ->set_pivot_default( $report->{pivot_default} );
  86
  87     return $rs;
  88 }
  89
  90
  91 #-------------------------------------------------------------------------------------------------
  92 package OpenILS::Reporter::SQLBuilder::ResultSet;
  93 use base qw/OpenILS::Reporter::SQLBuilder/;
  94
  95 sub is_subquery {
  96     my $self = shift;
  97     my $flag = shift;
  98     $self->{_is_subquery} = $flag if (defined $flag);
  99     return $self->{_is_subquery};
 100 }
 101
 102 sub pivot_data {
 103     my $self = shift;
 104     return $self->builder->{_pivot_data};
 105 }
 106
 107 sub pivot_label {
 108     my $self = shift;
 109     return $self->builder->{_pivot_label};
 110 }
 111
 112 sub pivot_default {
 113     my $self = shift;
 114     return $self->builder->{_pivot_default};
 115 }
 116
 117 sub set_pivot_default {
 118     my $self = shift;
 119     my $p = shift;
 120     $self->builder->{_pivot_default} = $p if (defined $p);
 121     return $self;
 122 }
 123
 124 sub set_pivot_data {
 125     my $self = shift;
 126     my $p = shift;
 127     $self->builder->{_pivot_data} = $p if (defined $p);
 128     return $self;
 129 }
 130
 131 sub set_pivot_label {
 132     my $self = shift;
 133     my $p = shift;
 134     $self->builder->{_pivot_label} = $p if (defined $p);
 135     return $self;
 136 }
 137
 138 sub set_subquery_alias {
 139     my $self = shift;
 140     my $alias = shift;
 141     $self->{_alias} = $alias if (defined $alias);
 142     return $self;
 143 }
 144
 145 sub set_select {
 146     my $self = shift;
 147     my @cols = @_;
 148
 149     $self->{_select} = [];
 150
 151     return $self unless (@cols && defined($cols[0]));
 152     @cols = @{ $cols[0] } if (@cols == 1 && ref($cols[0]) eq 'ARRAY');
 153
 154     push @{ $self->{_select} }, map { OpenILS::Reporter::SQLBuilder::Column::Select->new( $_ )->set_builder( $self->builder ) } @cols;
 155
 156     return $self;
 157 }
 158
 159 sub set_from {
 160     my $self = shift;
 161     my $f = shift;
 162
 163     $self->{_from} = OpenILS::Reporter::SQLBuilder::Relation->parse( $f, $self->builder );
 164
 165     return $self;
 166 }
 167
 168 sub set_where {
 169     my $self = shift;
 170     my @cols = @_;
 171
 172     $self->{_where} = [];
 173
 174     return $self unless (@cols && defined($cols[0]));
 175     @cols = @{ $cols[0] } if (@cols == 1 && ref($cols[0]) eq 'ARRAY');
 176
 177     push @{ $self->{_where} }, map { OpenILS::Reporter::SQLBuilder::Column::Where->new( $_ )->set_builder( $self->builder ) } @cols;
 178
 179     return $self;
 180 }
 181
 182 sub set_having {
 183     my $self = shift;
 184     my @cols = @_;
 185
 186     $self->{_having} = [];
 187
 188     return $self unless (@cols && defined($cols[0]));
 189     @cols = @{ $cols[0] } if (@cols == 1 && ref($cols[0]) eq 'ARRAY');
 190
 191     push @{ $self->{_having} }, map { OpenILS::Reporter::SQLBuilder::Column::Having->new( $_ )->set_builder( $self->builder ) } @cols;
 192
 193     return $self;
 194 }
 195
 196 sub set_order_by {
 197     my $self = shift;
 198     my @cols = @_;
 199
 200     $self->{_order_by} = [];
 201
 202     return $self unless (@cols && defined($cols[0]));
 203     @cols = @{ $cols[0] } if (@cols == 1 && ref($cols[0]) eq 'ARRAY');
 204
 205     push @{ $self->{_order_by} }, map { OpenILS::Reporter::SQLBuilder::Column::OrderBy->new( $_ )->set_builder( $self->builder ) } @cols;
 206
 207     return $self;
 208 }
 209
 210 sub column_label_list {
 211     my $self = shift;
 212
 213     my @labels;
 214     push @labels, $self->resolve_param( $_->{_alias} ) for ( @{ $self->{_select} } );
 215     return @labels;
 216 }
 217
 218 sub group_by_list {
 219     my $self = shift;
 220     my $base = shift;
 221     $base = 1 unless (defined $base);
 222
 223     my $seen_label = 0;
 224     my $gcount = $base;
 225     my @group_by;
 226     for my $c ( @{ $self->{_select} } ) {
 227         if ($base == 0 && !$seen_label  && defined($self->pivot_label) && $gcount == $self->pivot_label - 1) {
 228             $seen_label++;
 229             next;
 230         }
 231         push @group_by, $gcount if (!$c->is_aggregate);
 232         $gcount++;
 233     }
 234
 235     return @group_by;
 236 }
 237
 238 sub toSQL {
 239     my $self = shift;
 240
 241     return $self->{_sql} if ($self->{_sql});
 242
 243     my $sql = '';
 244
 245     if ($self->is_subquery) {
 246         $sql = '(';
 247     } elsif ($self->resultset_limit) {
 248         $sql = 'SELECT * FROM (';
 249     }
 250
 251     $sql .= "SELECT\t" . join(",\n\t", map { $_->toSQL } @{ $self->{_select} }) . "\n" if (@{ $self->{_select} });
 252     $sql .= "  FROM\t" . $self->{_from}->toSQL . "\n" if ($self->{_from});
 253     $sql .= "  WHERE\t" . join("\n\tAND ", map { $_->toSQL } @{ $self->{_where} }) . "\n" if (@{ $self->{_where} });
 254
 255     my @group_by = $self->group_by_list;
 256
 257     $sql .= '  GROUP BY ' . join(', ', @group_by) . "\n" if (@group_by);
 258     $sql .= "  HAVING " . join("\n\tAND ", map { $_->toSQL } @{ $self->{_having} }) . "\n" if (@{ $self->{_having} });
 259     $sql .= '  ORDER BY ' . join(', ', map { $_->toSQL } @{ $self->{_order_by} }) . "\n" if (@{ $self->{_order_by} });
 260
 261     if ($self->is_subquery) {
 262         $sql .= ') '. $self->{_alias} . "\n";
 263     } elsif ($self->resultset_limit) {
 264         $sql .= ') limited_to_' . $self->resultset_limit .
 265                 '_hits LIMIT ' . $self->resultset_limit . "\n";
 266     }
 267
 268     return $self->{_sql} = $sql;
 269 }
 270
 271
 272 #-------------------------------------------------------------------------------------------------
 273 package OpenILS::Reporter::SQLBuilder::Input;
 274 use base qw/OpenILS::Reporter::SQLBuilder/;
 275
 276 sub new {
 277     my $class = shift;
 278     my $self = $class->SUPER::new;
 279
 280     my $col_data = shift;
 281
 282     if (ref($col_data)) {
 283         $self->{params} = $col_data->{params};
 284         my $trans = $col_data->{transform} || 'Bare';
 285         my $pkg = "OpenILS::Reporter::SQLBuilder::Input::Transform::$trans";
 286         if (UNIVERSAL::can($pkg => 'toSQL')) {
 287             $self->{_transform} = $trans;
 288         } else {
 289             $self->{_transform} = 'GenericTransform';
 290         }
 291     } elsif( defined($col_data) ) {
 292         $self->{_transform} = 'Bare';
 293         $self->{params} = $col_data;
 294     } else {
 295         $self->{_transform} = 'NULL';
 296     }
 297
 298
 299
 300     return $self;
 301 }
 302
 303 sub toSQL {
 304     my $self = shift;
 305     my $type = $self->{_transform};
 306     return $self->{_sql} if ($self->{_sql});
 307     my $toSQL = "OpenILS::Reporter::SQLBuilder::Input::Transform::${type}::toSQL";
 308     return $self->{_sql} = $self->$toSQL;
 309 }
 310
 311 #-------------------------------------------------------------------------------------------------
 312 package OpenILS::Reporter::SQLBuilder::Input::Transform::GenericTransform;
 313
 314 sub toSQL {
 315     my $self = shift;
 316     my $func = $self->{transform};
 317
 318     my @params;
 319     @params = @{ $self->{params} } if ($self->{params});
 320
 321     my $sql = $func . "(\$_$$\$";
 322     $sql .= join("\$_$$\$,\$_$$\$", @params) if (@params);
 323     $sql .= "\$_$$\$)";
 324
 325     return $sql;
 326 }
 327
 328
 329 #-------------------------------------------------------------------------------------------------
 330 package OpenILS::Reporter::SQLBuilder::Input::Transform::NULL;
 331
 332 sub toSQL {
 333     return "NULL";
 334 }
 335
 336
 337 #-------------------------------------------------------------------------------------------------
 338 package OpenILS::Reporter::SQLBuilder::Input::Transform::Bare;
 339
 340 sub toSQL {
 341     my $self = shift;
 342
 343     my $val = $self->{params};
 344     $val = $$val[0] if (ref($val));
 345
 346     return "\$_$$\$$val\$_$$\$";
 347 }
 348
 349
 350 #-------------------------------------------------------------------------------------------------
 351 package OpenILS::Reporter::SQLBuilder::Input::Transform::age;
 352
 353 sub toSQL {
 354     my $self = shift;
 355
 356     my $val = $self->{params};
 357     $val = $$val[0] if (ref($val));
 358
 359     return "AGE(NOW(),\$_$$\$$val\$_$$\$::TIMESTAMPTZ)";
 360 }
 361
 362 sub is_aggregate { return 0 }
 363
 364
 365 #-------------------------------------------------------------------------------------------------
 366 package OpenILS::Reporter::SQLBuilder::Input::Transform::relative_year;
 367
 368 sub toSQL {
 369     my $self = shift;
 370
 371     my $rtime = $self->relative_time || 'now';
 372     my $val = $self->{params};
 373     $val = $$val[0] if (ref($val));
 374
 375     return "EXTRACT(YEAR FROM \$_$$\$$rtime\$_$$\$::TIMESTAMPTZ + \$_$$\$$val years\$_$$\$)";
 376 }
 377
 378
 379 #-------------------------------------------------------------------------------------------------
 380 package OpenILS::Reporter::SQLBuilder::Input::Transform::relative_month;
 381
 382 sub toSQL {
 383     my $self = shift;
 384
 385     my $rtime = $self->relative_time || 'now';
 386     my $val = $self->{params};
 387     $val = $$val[0] if (ref($val));
 388
 389     return "(EXTRACT(YEAR FROM \$_$$\$$rtime\$_$$\$::TIMESTAMPTZ + \$_$$\$$val months\$_$$\$)" .
 390         " || \$_$$\$-\$_$$\$ || LPAD(EXTRACT(MONTH FROM \$_$$\$$rtime\$_$$\$::TIMESTAMPTZ + \$_$$\$$val months\$_$$\$)::text,2,\$_$$\$0\$_$$\$))";
 391 }
 392
 393
 394 #-------------------------------------------------------------------------------------------------
 395 package OpenILS::Reporter::SQLBuilder::Input::Transform::relative_date;
 396
 397 sub toSQL {
 398     my $self = shift;
 399
 400     my $rtime = $self->relative_time || 'now';
 401     my $val = $self->{params};
 402     $val = $$val[0] if (ref($val));
 403
 404     return "DATE(\$_$$\$$rtime\$_$$\$::TIMESTAMPTZ + \$_$$\$$val days\$_$$\$)";
 405 }
 406
 407
 408 #-------------------------------------------------------------------------------------------------
 409 package OpenILS::Reporter::SQLBuilder::Input::Transform::relative_week;
 410
 411 sub toSQL {
 412     my $self = shift;
 413
 414     my $rtime = $self->relative_time || 'now';
 415     my $val = $self->{params};
 416     $val = $$val[0] if (ref($val));
 417
 418     return "EXTRACT(WEEK FROM \$_$$\$rtime\$_$$\$::TIMESTAMPTZ + \$_$$\$val weeks\$_$$\$)";
 419 }
 420
 421
 422 #-------------------------------------------------------------------------------------------------
 423 package OpenILS::Reporter::SQLBuilder::Column;
 424 use base qw/OpenILS::Reporter::SQLBuilder/;
 425
 426 sub new {
 427     my $class = shift;
 428     my $self = $class->SUPER::new;
 429
 430     my $col_data = shift;
 431     $self->{_relation} = $col_data->{relation};
 432     $self->{_column} = $col_data->{column};
 433
 434     $self->{_aggregate} = $col_data->{aggregate};
 435
 436     if (ref($self->{_column})) {
 437         my $trans = $self->{_column}->{transform} || 'Bare';
 438         my $pkg = "OpenILS::Reporter::SQLBuilder::Column::Transform::$trans";
 439         if (UNIVERSAL::can($pkg => 'toSQL')) {
 440             $self->{_transform} = $trans;
 441         } else {
 442             $self->{_transform} = 'GenericTransform';
 443         }
 444     } elsif( defined($self->{_column}) ) {
 445         $self->{_transform} = 'Bare';
 446     } else {
 447         $self->{_transform} = 'NULL';
 448     }
 449
 450
 451     return $self;
 452 }
 453
 454 sub find_relation {
 455     my $self = shift;
 456     return $self->builder->{_rels}->{$self->{_relation}};
 457 }
 458
 459 sub name {
 460     my $self = shift;
 461     if (ref($self->{_column})) {
 462          return $self->{_column}->{colname};
 463     } else {
 464         return $self->{_column};
 465     }
 466 }
 467
 468 sub toSQL {
 469     my $self = shift;
 470     my $type = $self->{_transform};
 471     return $self->{_sql} if ($self->{_sql});
 472     my $toSQL = "OpenILS::Reporter::SQLBuilder::Column::Transform::${type}::toSQL";
 473     return $self->{_sql} = $self->$toSQL;
 474 }
 475
 476 sub is_aggregate {
 477     my $self = shift;
 478     my $type = $self->{_transform};
 479     my $is_agg = "OpenILS::Reporter::SQLBuilder::Column::Transform::${type}::is_aggregate";
 480     return $self->$is_agg;
 481 }
 482
 483
 484 #-------------------------------------------------------------------------------------------------
 485 package OpenILS::Reporter::SQLBuilder::Column::OrderBy;
 486 use base qw/OpenILS::Reporter::SQLBuilder::Column/;
 487
 488 sub new {
 489     my $class = shift;
 490     my $self = $class->SUPER::new(@_);
 491
 492     my $col_data = shift;
 493     $self->{_direction} = $col_data->{direction} || 'ascending';
 494     return $self;
 495 }
 496
 497 sub toSQL {
 498     my $self = shift;
 499     my $dir = ($self->{_direction} =~ /^d/oi) ? 'DESC' : 'ASC';
 500     return $self->{_sql} if ($self->{_sql});
 501     return $self->{_sql} = $self->SUPER::toSQL .  " $dir";
 502 }
 503
 504
 505 #-------------------------------------------------------------------------------------------------
 506 package OpenILS::Reporter::SQLBuilder::Column::Select;
 507 use base qw/OpenILS::Reporter::SQLBuilder::Column/;
 508
 509 sub new {
 510     my $class = shift;
 511     my $self = $class->SUPER::new(@_);
 512
 513     my $col_data = shift;
 514     $self->{_alias} = $col_data->{alias} || $self->name;
 515     return $self;
 516 }
 517
 518 sub toSQL {
 519     my $self = shift;
 520     return $self->{_sql} if ($self->{_sql});
 521     return $self->{_sql} = $self->SUPER::toSQL .  ' AS "' . $self->resolve_param( $self->{_alias} ) . '"';
 522 }
 523
 524
 525 #-------------------------------------------------------------------------------------------------
 526 package OpenILS::Reporter::SQLBuilder::Column::Transform::GenericTransform;
 527
 528 sub toSQL {
 529     my $self = shift;
 530     my $name = $self->name;
 531     my $func = $self->{_column}->{transform};
 532
 533     my @params;
 534     @params = @{ $self->resolve_param( $self->{_column}->{params} ) } if ($self->{_column}->{params});
 535
 536     my $sql = $func . '("' . $self->{_relation} . '"."' . $self->name . '"';
 537     $sql .= ",\$_$$\$" . join("\$_$$\$,\$_$$\$", @params) . "\$_$$\$" if (@params);
 538     $sql .= ')';
 539
 540     return $sql;
 541 }
 542
 543 sub is_aggregate { return $self->{_aggregate} }
 544
 545 #-------------------------------------------------------------------------------------------------
 546 package OpenILS::Reporter::SQLBuilder::Column::Transform::Bare;
 547
 548 sub toSQL {
 549     my $self = shift;
 550     return '"' . $self->{_relation} . '"."' . $self->name . '"';
 551 }
 552
 553 sub is_aggregate { return 0 }
 554
 555 #-------------------------------------------------------------------------------------------------
 556 package OpenILS::Reporter::SQLBuilder::Column::Transform::upper;
 557
 558 sub toSQL {
 559     my $self = shift;
 560     my $params = $self->resolve_param( $self->{_column}->{params} );
 561     return 'UPPER("' . $self->{_relation} . '"."' . $self->name . '")';
 562 }
 563
 564 sub is_aggregate { return 0 }
 565
 566
 567 #-------------------------------------------------------------------------------------------------
 568 package OpenILS::Reporter::SQLBuilder::Column::Transform::lower;
 569
 570 sub toSQL {
 571     my $self = shift;
 572     my $params = $self->resolve_param( $self->{_column}->{params} );
 573     return 'evergreen.lowercase("' . $self->{_relation} . '"."' . $self->name . '")';
 574 }
 575
 576 sub is_aggregate { return 0 }
 577
 578
 579 #-------------------------------------------------------------------------------------------------
 580 package OpenILS::Reporter::SQLBuilder::Column::Transform::substring;
 581
 582 sub toSQL {
 583     my $self = shift;
 584     my $params = $self->resolve_param( $self->{_column}->{params} );
 585     my $start = $$params[0];
 586     my $len = $$params[1];
 587     return 'SUBSTRING("' . $self->{_relation} . '"."' . $self->name . "\",$start,$len)";
 588 }
 589
 590 sub is_aggregate { return 0 }
 591
 592
 593 #-------------------------------------------------------------------------------------------------
 594 package OpenILS::Reporter::SQLBuilder::Column::Transform::day_name;
 595
 596 sub toSQL {
 597     my $self = shift;
 598     return 'TO_CHAR("' . $self->{_relation} . '"."' . $self->name . '", \'Day\')';
 599 }
 600
 601 sub is_aggregate { return 0 }
 602
 603
 604 #-------------------------------------------------------------------------------------------------
 605 package OpenILS::Reporter::SQLBuilder::Column::Transform::month_name;
 606
 607 sub toSQL {
 608     my $self = shift;
 609     return 'TO_CHAR("' . $self->{_relation} . '"."' . $self->name . '", \'Month\')';
 610 }
 611
 612 sub is_aggregate { return 0 }
 613
 614
 615 #-------------------------------------------------------------------------------------------------
 616 package OpenILS::Reporter::SQLBuilder::Column::Transform::doy;
 617
 618 sub toSQL {
 619     my $self = shift;
 620     return 'EXTRACT(DOY FROM "' . $self->{_relation} . '"."' . $self->name . '")';
 621 }
 622
 623 sub is_aggregate { return 0 }
 624
 625
 626 #-------------------------------------------------------------------------------------------------
 627 package OpenILS::Reporter::SQLBuilder::Column::Transform::woy;
 628
 629 sub toSQL {
 630     my $self = shift;
 631     return 'EXTRACT(WEEK FROM "' . $self->{_relation} . '"."' . $self->name . '")';
 632 }
 633
 634 sub is_aggregate { return 0 }
 635
 636
 637 #-------------------------------------------------------------------------------------------------
 638 package OpenILS::Reporter::SQLBuilder::Column::Transform::moy;
 639
 640 sub toSQL {
 641     my $self = shift;
 642     return 'EXTRACT(MONTH FROM "' . $self->{_relation} . '"."' . $self->name . '")';
 643 }
 644
 645 sub is_aggregate { return 0 }
 646
 647
 648 #-------------------------------------------------------------------------------------------------
 649 package OpenILS::Reporter::SQLBuilder::Column::Transform::qoy;
 650
 651 sub toSQL {
 652     my $self = shift;
 653     return 'EXTRACT(QUARTER FROM "' . $self->{_relation} . '"."' . $self->name . '")';
 654 }
 655
 656 sub is_aggregate { return 0 }
 657
 658
 659 #-------------------------------------------------------------------------------------------------
 660 package OpenILS::Reporter::SQLBuilder::Column::Transform::dom;
 661
 662 sub toSQL {
 663     my $self = shift;
 664     return 'EXTRACT(DAY FROM "' . $self->{_relation} . '"."' . $self->name . '")';
 665 }
 666
 667 sub is_aggregate { return 0 }
 668
 669
 670 #-------------------------------------------------------------------------------------------------
 671 package OpenILS::Reporter::SQLBuilder::Column::Transform::dow;
 672
 673 sub toSQL {
 674     my $self = shift;
 675     return 'EXTRACT(DOW FROM "' . $self->{_relation} . '"."' . $self->name . '")';
 676 }
 677
 678 sub is_aggregate { return 0 }
 679
 680
 681 #-------------------------------------------------------------------------------------------------
 682 package OpenILS::Reporter::SQLBuilder::Column::Transform::year_trunc;
 683
 684 sub toSQL {
 685     my $self = shift;
 686     return 'EXTRACT(YEAR FROM "' . $self->{_relation} . '"."' . $self->name . '")';
 687 }
 688
 689 sub is_aggregate { return 0 }
 690
 691
 692 #-------------------------------------------------------------------------------------------------
 693 package OpenILS::Reporter::SQLBuilder::Column::Transform::month_trunc;
 694
 695 sub toSQL {
 696     my $self = shift;
 697     return '(EXTRACT(YEAR FROM "' . $self->{_relation} . '"."' . $self->name . '")' .
 698         ' || \'-\' || LPAD(EXTRACT(MONTH FROM "' . $self->{_relation} . '"."' . $self->name . '")::text,2,\'0\'))';
 699 }
 700
 701 sub is_aggregate { return 0 }
 702
 703
 704 #-------------------------------------------------------------------------------------------------
 705 package OpenILS::Reporter::SQLBuilder::Column::Transform::date_trunc;
 706
 707 sub toSQL {
 708     my $self = shift;
 709     return 'DATE("' . $self->{_relation} . '"."' . $self->name . '")';
 710 }
 711
 712 sub is_aggregate { return 0 }
 713
 714
 715 #-------------------------------------------------------------------------------------------------
 716 package OpenILS::Reporter::SQLBuilder::Column::Transform::hour_trunc;
 717
 718 sub toSQL {
 719     my $self = shift;
 720     return 'EXTRACT(HOUR FROM "' . $self->{_relation} . '"."' . $self->name . '")';
 721 }
 722
 723 sub is_aggregate { return 0 }
 724
 725
 726 #-------------------------------------------------------------------------------------------------
 727 package OpenILS::Reporter::SQLBuilder::Column::Transform::quarter;
 728
 729 sub toSQL {
 730     my $self = shift;
 731     return '(EXTRACT(YEAR FROM "' . $self->{_relation} . '"."' . $self->name . '")' .
 732         ' || \'-Q\' || EXTRACT(QUARTER FROM "' . $self->{_relation} . '"."' . $self->name . '"))';
 733 }
 734
 735 sub is_aggregate { return 0 }
 736
 737
 738 #-------------------------------------------------------------------------------------------------
 739 package OpenILS::Reporter::SQLBuilder::Column::Transform::months_ago;
 740
 741 sub toSQL {
 742     my $self = shift;
 743     return 'EXTRACT(MONTH FROM AGE(NOW(),"' . $self->{_relation} . '"."' . $self->name . '"))';
 744 }
 745
 746 sub is_aggregate { return 0 }
 747
 748
 749 #-------------------------------------------------------------------------------------------------
 750 package OpenILS::Reporter::SQLBuilder::Column::Transform::hod;
 751
 752 sub toSQL {
 753     my $self = shift;
 754     return 'EXTRACT(HOUR FROM "' . $self->{_relation} . '"."' . $self->name . '")';
 755 }
 756
 757 sub is_aggregate { return 0 }
 758
 759
 760 #-------------------------------------------------------------------------------------------------
 761 package OpenILS::Reporter::SQLBuilder::Column::Transform::quarters_ago;
 762
 763 sub toSQL {
 764     my $self = shift;
 765     return 'EXTRACT(QUARTER FROM AGE(NOW(),"' . $self->{_relation} . '"."' . $self->name . '"))';
 766 }
 767
 768 sub is_aggregate { return 0 }
 769
 770
 771 #-------------------------------------------------------------------------------------------------
 772 package OpenILS::Reporter::SQLBuilder::Column::Transform::age;
 773
 774 sub toSQL {
 775     my $self = shift;
 776     return 'AGE(NOW(),"' . $self->{_relation} . '"."' . $self->name . '")';
 777 }
 778
 779 sub is_aggregate { return 0 }
 780
 781
 782 #-------------------------------------------------------------------------------------------------
 783 package OpenILS::Reporter::SQLBuilder::Column::Transform::first;
 784
 785 sub toSQL {
 786     my $self = shift;
 787     return 'FIRST("' . $self->{_relation} . '"."' . $self->name . '")';
 788 }
 789
 790 sub is_aggregate { return 1 }
 791
 792
 793 #-------------------------------------------------------------------------------------------------
 794 package OpenILS::Reporter::SQLBuilder::Column::Transform::last;
 795
 796 sub toSQL {
 797     my $self = shift;
 798     return 'LAST("' . $self->{_relation} . '"."' . $self->name . '")';
 799 }
 800
 801 sub is_aggregate { return 1 }
 802
 803
 804 #-------------------------------------------------------------------------------------------------
 805 package OpenILS::Reporter::SQLBuilder::Column::Transform::min;
 806
 807 sub toSQL {
 808     my $self = shift;
 809     return 'MIN("' . $self->{_relation} . '"."' . $self->name . '")';
 810 }
 811
 812 sub is_aggregate { return 1 }
 813
 814
 815 #-------------------------------------------------------------------------------------------------
 816 package OpenILS::Reporter::SQLBuilder::Column::Transform::max;
 817
 818 sub toSQL {
 819     my $self = shift;
 820     return 'MAX("' . $self->{_relation} . '"."' . $self->name . '")';
 821 }
 822
 823 sub is_aggregate { return 1 }
 824
 825
 826 #-------------------------------------------------------------------------------------------------
 827 package OpenILS::Reporter::SQLBuilder::Column::Transform::count;
 828
 829 sub toSQL {
 830     my $self = shift;
 831     return 'COUNT("' . $self->{_relation} . '"."' . $self->name . '")';
 832 }
 833
 834 sub is_aggregate { return 1 }
 835
 836
 837 #-------------------------------------------------------------------------------------------------
 838 package OpenILS::Reporter::SQLBuilder::Column::Transform::count_distinct;
 839
 840 sub toSQL {
 841     my $self = shift;
 842     return 'COUNT(DISTINCT "' . $self->{_relation} . '"."' . $self->name . '")';
 843 }
 844
 845 sub is_aggregate { return 1 }
 846
 847
 848 #-------------------------------------------------------------------------------------------------
 849 package OpenILS::Reporter::SQLBuilder::Column::Transform::sum;
 850
 851 sub toSQL {
 852     my $self = shift;
 853     return 'SUM("' . $self->{_relation} . '"."' . $self->name . '")';
 854 }
 855
 856 sub is_aggregate { return 1 }
 857
 858
 859 #-------------------------------------------------------------------------------------------------
 860 package OpenILS::Reporter::SQLBuilder::Column::Transform::average;
 861
 862 sub toSQL {
 863     my $self = shift;
 864     return 'AVG("' . $self->{_relation} . '"."' . $self->name .  '")';
 865 }
 866
 867 sub is_aggregate { return 1 }
 868
 869
 870 #-------------------------------------------------------------------------------------------------
 871 package OpenILS::Reporter::SQLBuilder::Column::Where;
 872 use base qw/OpenILS::Reporter::SQLBuilder::Column/;
 873
 874 sub new {
 875     my $class = shift;
 876     my $self = $class->SUPER::new(@_);
 877
 878     my $col_data = shift;
 879     $self->{_condition} = $col_data->{condition};
 880
 881     return $self;
 882 }
 883
 884 sub _flesh_conditions {
 885     my $cond = shift;
 886     my $builder = shift;
 887     $cond = [$cond] unless (ref($cond) eq 'ARRAY');
 888
 889     my @out;
 890     for my $c (@$cond) {
 891         push @out, OpenILS::Reporter::SQLBuilder::Input->new( $c )->set_builder( $builder );
 892     }
 893
 894     return \@out;
 895 }
 896
 897 sub toSQL {
 898     my $self = shift;
 899
 900     return $self->{_sql} if ($self->{_sql});
 901
 902     my $sql = '';
 903
 904     my $rel = $self->find_relation();
 905     if ($rel && $rel->is_nullable) {
 906         $sql = "((". $self->SUPER::toSQL .") IS NULL OR ";
 907     }
 908
 909     $sql .= $self->SUPER::toSQL;
 910
 911     my ($op) = keys %{ $self->{_condition} };
 912     my $val = _flesh_conditions( $self->resolve_param( $self->{_condition}->{$op} ), $self->builder );
 913
 914     if (lc($op) eq 'in') {
 915         $sql .= " IN (". join(",", map { $_->toSQL } @$val).")";
 916
 917     } elsif (lc($op) eq 'not in') {
 918         $sql .= " NOT IN (". join(",", map { $_->toSQL } @$val).")";
 919
 920     } elsif (lc($op) eq '= any') {
 921         $val = $$val[0] if (ref($val) eq 'ARRAY');
 922         $val = $val->toSQL;
 923         if ($rel && $rel->is_nullable) { # need to redo this
 924             $sql = "((". $self->SUPER::toSQL .") IS NULL OR ";
 925         } else {
 926             $sql = '';
 927         }
 928         $sql .= "$val = ANY (".$self->SUPER::toSQL.")";
 929
 930     } elsif (lc($op) eq '<> any') {
 931         $val = $$val[0] if (ref($val) eq 'ARRAY');
 932         $val = $val->toSQL;
 933         if ($rel && $rel->is_nullable) { # need to redo this
 934             $sql = "((". $self->SUPER::toSQL .") IS NULL OR ";
 935         } else {
 936             $sql = '';
 937         }
 938         $sql .= "$val <> ANY (".$self->SUPER::toSQL.")";
 939
 940     } elsif (lc($op) eq 'is blank') {
 941         if ($rel && $rel->is_nullable) { # need to redo this
 942             $sql = "((". $self->SUPER::toSQL .") IS NULL OR ";
 943         } else {
 944             $sql = '';
 945         }
 946         $sql .= '('. $self->SUPER::toSQL ." IS NULL OR ". $self->SUPER::toSQL ." = '')";
 947
 948     } elsif (lc($op) eq 'is not blank') {
 949         if ($rel && $rel->is_nullable) { # need to redo this
 950             $sql = "((". $self->SUPER::toSQL .") IS NULL OR ";
 951         } else {
 952             $sql = '';
 953         }
 954         $sql .= '('. $self->SUPER::toSQL ." IS NOT NULL AND ". $self->SUPER::toSQL ." <> '')";
 955
 956     } elsif (lc($op) eq 'between') {
 957         $sql .= " BETWEEN SYMMETRIC ". join(" AND ", map { $_->toSQL } @$val);
 958
 959     } elsif (lc($op) eq 'not between') {
 960         $sql .= " NOT BETWEEN SYMMETRIC ". join(" AND ", map { $_->toSQL } @$val);
 961
 962     } elsif (lc($op) eq 'like') {
 963         $val = $$val[0] if (ref($val) eq 'ARRAY');
 964         $val = $val->toSQL;
 965         $val =~ s/\$_$$\$//g;
 966         $val =~ s/%/\\%/o;
 967         $val =~ s/_/\\_/o;
 968         $sql .= " LIKE \$_$$\$\%$val\%\$_$$\$";
 969
 970     } elsif (lc($op) eq 'ilike') {
 971         $val = $$val[0] if (ref($val) eq 'ARRAY');
 972         $val = $val->toSQL;
 973         $val =~ s/\$_$$\$//g;
 974         $val =~ s/%/\\%/o;
 975         $val =~ s/_/\\_/o;
 976         $sql .= " ILIKE \$_$$\$\%$val\%\$_$$\$";
 977
 978     } else {
 979         $val = $$val[0] if (ref($val) eq 'ARRAY');
 980         $sql .= " $op " . $val->toSQL;
 981     }
 982
 983     if ($rel && $rel->is_nullable) {
 984         $sql .= ")";
 985     }
 986
 987     return $self->{_sql} = $sql;
 988 }
 989
 990
 991 #-------------------------------------------------------------------------------------------------
 992 package OpenILS::Reporter::SQLBuilder::Column::Having;
 993 use base qw/OpenILS::Reporter::SQLBuilder::Column::Where/;
 994
 995 #-------------------------------------------------------------------------------------------------
 996 package OpenILS::Reporter::SQLBuilder::Relation;
 997 use base qw/OpenILS::Reporter::SQLBuilder/;
 998
 999 sub parse {
1000     my $self = shift;
1001     $self = $self->SUPER::new if (!ref($self));
1002
1003     my $rel_data = shift;
1004     my $b = shift;
1005     $self->set_builder($b);
1006
1007     $self->{_table} = $rel_data->{table};
1008     $self->{_alias} = $rel_data->{alias} || $self->{_table};
1009     $self->{_join} = [];
1010     $self->{_columns} = [];
1011
1012     $self->builder->{_rels}{$self->{_alias}} = $self;
1013
1014     if ($rel_data->{join}) {
1015         $self->add_join(
1016             $_ => OpenILS::Reporter::SQLBuilder::Relation->parse( $rel_data->{join}->{$_}, $b ) => $rel_data->{join}->{$_}->{key} => $rel_data->{join}->{$_}->{type}
1017         ) for ( keys %{ $rel_data->{join} } );
1018     }
1019
1020     return $self;
1021 }
1022
1023 sub add_column {
1024     my $self = shift;
1025     my $col = shift;
1026
1027     push @{ $self->{_columns} }, $col;
1028 }
1029
1030 sub find_column {
1031     my $self = shift;
1032     my $col = shift;
1033     return (grep { $_->name eq $col} @{ $self->{_columns} })[0];
1034 }
1035
1036 sub add_join {
1037     my $self = shift;
1038     my $col = shift;
1039     my $frel = shift;
1040     my $fkey = shift;
1041     my $type = lc(shift()) || 'inner';
1042
1043     if (UNIVERSAL::isa($col,'OpenILS::Reporter::SQLBuilder::Join')) {
1044         push @{ $self->{_join} }, $col;
1045     } else {
1046         push @{ $self->{_join} }, OpenILS::Reporter::SQLBuilder::Join->build( $self => $col, $frel => $fkey, $type );
1047     }
1048
1049     return $self;
1050 }
1051
1052 sub is_nullable {
1053     my $self = shift;
1054     return $self->{_nullable};
1055 }
1056
1057 sub is_join {
1058     my $self = shift;
1059     my $j = shift;
1060     $self->{_is_join} = $j if ($j);
1061     return $self->{_is_join};
1062 }
1063
1064 sub join_type {
1065     my $self = shift;
1066     my $j = shift;
1067     $self->{_join_type} = $j if ($j);
1068     return $self->{_join_type};
1069 }
1070
1071 sub toSQL {
1072     my $self = shift;
1073     return $self->{_sql} if ($self->{_sql});
1074
1075     my $sql = $self->{_table} .' AS "'. $self->{_alias} .'"';
1076
1077     if (!$self->is_join) {
1078         for my $j ( @{ $self->{_join} } ) {
1079             $sql .= $j->toSQL;
1080         }
1081     }
1082
1083     return $self->{_sql} = $sql;
1084 }
1085
1086 #-------------------------------------------------------------------------------------------------
1087 package OpenILS::Reporter::SQLBuilder::Join;
1088 use base qw/OpenILS::Reporter::SQLBuilder/;
1089
1090 sub build {
1091     my $class = shift;
1092     my $self = $class->SUPER::new if (!ref($class));
1093
1094     $self->{_left_rel} = shift;
1095     ($self->{_left_col}) = split(/-/,shift());
1096
1097     $self->{_right_rel} = shift;
1098     $self->{_right_col} = shift;
1099
1100     $self->{_join_type} = shift;
1101
1102     $self->{_right_rel}->set_builder($self->{_left_rel}->builder);
1103
1104     $self->{_right_rel}->is_join(1);
1105     $self->{_right_rel}->join_type($self->{_join_type});
1106
1107     bless $self => "OpenILS::Reporter::SQLBuilder::Join::$self->{_join_type}";
1108
1109     if ( $self->{_join_type} eq 'inner' or !$self->{_join_type}) {
1110         $self->{_join_type} = 'i';
1111     } else {
1112         if ($self->{_join_type} eq 'left') {
1113             $self->{_right_rel}->{_nullable} = 'l';
1114         } elsif ($self->{_join_type} eq 'right') {
1115             $self->{_left_rel}->{_nullable} = 'r';
1116         } else {
1117             $self->{_right_rel}->{_nullable} = 'f';
1118             $self->{_left_rel}->{_nullable} = 'f';
1119         }
1120     }
1121
1122     return $self;
1123 }
1124
1125 sub toSQL {
1126     my $self = shift;
1127     my $dir = shift;
1128
1129     my $sql = "JOIN " . $self->{_right_rel}->toSQL .
1130         ' ON ("' . $self->{_left_rel}->{_alias} . '"."' . $self->{_left_col} .
1131         '" = "' . $self->{_right_rel}->{_alias} . '"."' . $self->{_right_col} . '")';
1132
1133     $sql .= $_->toSQL($dir) for (@{ $self->{_right_rel}->{_join} });
1134
1135     return $sql;
1136 }
1137
1138 #-------------------------------------------------------------------------------------------------
1139 package OpenILS::Reporter::SQLBuilder::Join::left;
1140 use base qw/OpenILS::Reporter::SQLBuilder::Join/;
1141
1142 sub toSQL {
1143     my $self = shift;
1144     my $dir = shift;
1145     #return $self->{_sql} if ($self->{_sql});
1146
1147     my $j = $dir && $dir eq 'r' ? 'FULL OUTER' : 'LEFT OUTER';
1148
1149     my $sql = "\n\t$j ". $self->SUPER::toSQL('l');
1150
1151     #$sql .= $_->toSQL for (@{ $self->{_right_rel}->{_join} });
1152
1153     return $self->{_sql} = $sql;
1154 }
1155
1156 #-------------------------------------------------------------------------------------------------
1157 package OpenILS::Reporter::SQLBuilder::Join::right;
1158 use base qw/OpenILS::Reporter::SQLBuilder::Join/;
1159
1160 sub toSQL {
1161     my $self = shift;
1162     my $dir = shift;
1163     #return $self->{_sql} if ($self->{_sql});
1164
1165     my $_nullable_rel = $dir && $dir eq 'l' ? '_right_rel' : '_left_rel';
1166     $self->{_left_rel}->{_nullable} = 'r';
1167     $self->{$_nullable_rel}->{_nullable} = $dir;
1168
1169     my $j = $dir && $dir eq 'l' ? 'FULL OUTER' : 'RIGHT OUTER';
1170
1171     my $sql = "\n\t$j ". $self->SUPER::toSQL('r');
1172
1173     #$sql .= $_->toSQL for (@{ $self->{_right_rel}->{_join} });
1174
1175     return $self->{_sql} = $sql;
1176 }
1177
1178 #-------------------------------------------------------------------------------------------------
1179 package OpenILS::Reporter::SQLBuilder::Join::inner;
1180 use base qw/OpenILS::Reporter::SQLBuilder::Join/;
1181
1182 sub toSQL {
1183     my $self = shift;
1184     my $dir = shift;
1185     #return $self->{_sql} if ($self->{_sql});
1186
1187     my $_nullable_rel = $dir && $dir eq 'l' ? '_right_rel' : '_left_rel';
1188     $self->{$_nullable_rel}->{_nullable} = $dir;
1189
1190     my $j = 'INNER';
1191
1192     my $sql = "\n\t$j ". $self->SUPER::toSQL;
1193
1194     #$sql .= $_->toSQL for (@{ $self->{_right_rel}->{_join} });
1195
1196     return $self->{_sql} = $sql;
1197 }
1198
1199 #-------------------------------------------------------------------------------------------------
1200 package OpenILS::Reporter::SQLBuilder::Join::cross;
1201 use base qw/OpenILS::Reporter::SQLBuilder::Join/;
1202
1203 sub toSQL {
1204     my $self = shift;
1205     #return $self->{_sql} if ($self->{_sql});
1206
1207     $self->{_right_rel}->{_nullable} = 'f';
1208     $self->{_left_rel}->{_nullable} = 'f';
1209
1210     my $sql = "\n\tFULL OUTER ". $self->SUPER::toSQL('f');
1211
1212     #$sql .= $_->toSQL for (@{ $self->{_right_rel}->{_join} });
1213
1214     return $self->{_sql} = $sql;
1215 }
1216
1217 1;