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     $val =~ s/\\/\\\\/go;
 347     $val =~ s/'/\\'/go;
 348
 349     return "'$val'";
 350 }
 351
 352
 353 #-------------------------------------------------------------------------------------------------
 354 package OpenILS::Reporter::SQLBuilder::Input::Transform::age;
 355
 356 sub toSQL {
 357     my $self = shift;
 358
 359     my $val = $self->{params};
 360     $val = $$val[0] if (ref($val));
 361
 362     $val =~ s/\\/\\\\/go;
 363     $val =~ s/'/\\'/go;
 364
 365     return "AGE(NOW(),'" . $val . "'::TIMESTAMPTZ)";
 366 }
 367
 368 sub is_aggregate { return 0 }
 369
 370
 371 #-------------------------------------------------------------------------------------------------
 372 package OpenILS::Reporter::SQLBuilder::Input::Transform::relative_year;
 373
 374 sub toSQL {
 375     my $self = shift;
 376
 377     my $rtime = $self->relative_time || 'now';
 378
 379     $rtime =~ s/\\/\\\\/go;
 380     $rtime =~ s/'/\\'/go;
 381
 382     my $val = $self->{params};
 383     $val = $$val[0] if (ref($val));
 384
 385     $val =~ s/\\/\\\\/go;
 386     $val =~ s/'/\\'/go;
 387
 388     return "EXTRACT(YEAR FROM '$rtime'::TIMESTAMPTZ + '$val years')";
 389 }
 390
 391
 392 #-------------------------------------------------------------------------------------------------
 393 package OpenILS::Reporter::SQLBuilder::Input::Transform::relative_month;
 394
 395 sub toSQL {
 396     my $self = shift;
 397
 398     my $rtime = $self->relative_time || 'now';
 399
 400     $rtime =~ s/\\/\\\\/go;
 401     $rtime =~ s/'/\\'/go;
 402
 403     my $val = $self->{params};
 404     $val = $$val[0] if (ref($val));
 405
 406     $val =~ s/\\/\\\\/go;
 407     $val =~ s/'/\\'/go;
 408
 409     return "EXTRACT(YEAR FROM '$rtime'::TIMESTAMPTZ + '$val months')" .
 410         " || '-' || LPAD(EXTRACT(MONTH FROM '$rtime'::TIMESTAMPTZ + '$val months')::text,2,'0')";
 411 }
 412
 413
 414 #-------------------------------------------------------------------------------------------------
 415 package OpenILS::Reporter::SQLBuilder::Input::Transform::relative_date;
 416
 417 sub toSQL {
 418     my $self = shift;
 419
 420     my $rtime = $self->relative_time || 'now';
 421
 422     $rtime =~ s/\\/\\\\/go;
 423     $rtime =~ s/'/\\'/go;
 424
 425     my $val = $self->{params};
 426     $val = $$val[0] if (ref($val));
 427
 428     $val =~ s/\\/\\\\/go;
 429     $val =~ s/'/\\'/go;
 430
 431     return "DATE('$rtime'::TIMESTAMPTZ + '$val days')";
 432 }
 433
 434
 435 #-------------------------------------------------------------------------------------------------
 436 package OpenILS::Reporter::SQLBuilder::Input::Transform::relative_week;
 437
 438 sub toSQL {
 439     my $self = shift;
 440
 441     my $rtime = $self->relative_time || 'now';
 442
 443     $rtime =~ s/\\/\\\\/go;
 444     $rtime =~ s/'/\\'/go;
 445
 446     my $val = $self->{params};
 447     $val = $$val[0] if (ref($val));
 448
 449     $val =~ s/\\/\\\\/go;
 450     $val =~ s/'/\\'/go;
 451
 452     return "EXTRACT(WEEK FROM '$rtime'::TIMESTAMPTZ + '$val weeks')";
 453 }
 454
 455
 456 #-------------------------------------------------------------------------------------------------
 457 package OpenILS::Reporter::SQLBuilder::Column;
 458 use base qw/OpenILS::Reporter::SQLBuilder/;
 459
 460 sub new {
 461     my $class = shift;
 462     my $self = $class->SUPER::new;
 463
 464     my $col_data = shift;
 465     $self->{_relation} = $col_data->{relation};
 466     $self->{_column} = $col_data->{column};
 467
 468     $self->{_aggregate} = $col_data->{aggregate};
 469
 470     if (ref($self->{_column})) {
 471         my $trans = $self->{_column}->{transform} || 'Bare';
 472         my $pkg = "OpenILS::Reporter::SQLBuilder::Column::Transform::$trans";
 473         if (UNIVERSAL::can($pkg => 'toSQL')) {
 474             $self->{_transform} = $trans;
 475         } else {
 476             $self->{_transform} = 'GenericTransform';
 477         }
 478     } elsif( defined($self->{_column}) ) {
 479         $self->{_transform} = 'Bare';
 480     } else {
 481         $self->{_transform} = 'NULL';
 482     }
 483
 484
 485     return $self;
 486 }
 487
 488 sub find_relation {
 489     my $self = shift;
 490     return $self->builder->{_rels}->{$self->{_relation}};
 491 }
 492
 493 sub name {
 494     my $self = shift;
 495     if (ref($self->{_column})) {
 496          return $self->{_column}->{colname};
 497     } else {
 498         return $self->{_column};
 499     }
 500 }
 501
 502 sub toSQL {
 503     my $self = shift;
 504     my $type = $self->{_transform};
 505     return $self->{_sql} if ($self->{_sql});
 506     my $toSQL = "OpenILS::Reporter::SQLBuilder::Column::Transform::${type}::toSQL";
 507     return $self->{_sql} = $self->$toSQL;
 508 }
 509
 510 sub is_aggregate {
 511     my $self = shift;
 512     my $type = $self->{_transform};
 513     my $is_agg = "OpenILS::Reporter::SQLBuilder::Column::Transform::${type}::is_aggregate";
 514     return $self->$is_agg;
 515 }
 516
 517
 518 #-------------------------------------------------------------------------------------------------
 519 package OpenILS::Reporter::SQLBuilder::Column::OrderBy;
 520 use base qw/OpenILS::Reporter::SQLBuilder::Column/;
 521
 522 sub new {
 523     my $class = shift;
 524     my $self = $class->SUPER::new(@_);
 525
 526     my $col_data = shift;
 527     $self->{_direction} = $col_data->{direction} || 'ascending';
 528     return $self;
 529 }
 530
 531 sub toSQL {
 532     my $self = shift;
 533     my $dir = ($self->{_direction} =~ /^d/oi) ? 'DESC' : 'ASC';
 534     return $self->{_sql} if ($self->{_sql});
 535     return $self->{_sql} = $self->SUPER::toSQL .  " $dir";
 536 }
 537
 538
 539 #-------------------------------------------------------------------------------------------------
 540 package OpenILS::Reporter::SQLBuilder::Column::Select;
 541 use base qw/OpenILS::Reporter::SQLBuilder::Column/;
 542
 543 sub new {
 544     my $class = shift;
 545     my $self = $class->SUPER::new(@_);
 546
 547     my $col_data = shift;
 548     $self->{_alias} = $col_data->{alias} || $self->name;
 549     return $self;
 550 }
 551
 552 sub toSQL {
 553     my $self = shift;
 554     return $self->{_sql} if ($self->{_sql});
 555     return $self->{_sql} = $self->SUPER::toSQL .  ' AS "' . $self->resolve_param( $self->{_alias} ) . '"';
 556 }
 557
 558
 559 #-------------------------------------------------------------------------------------------------
 560 package OpenILS::Reporter::SQLBuilder::Column::Transform::GenericTransform;
 561
 562 sub toSQL {
 563     my $self = shift;
 564     my $name = $self->name;
 565     my $func = $self->{_column}->{transform};
 566
 567     my @params;
 568     @params = @{ $self->resolve_param( $self->{_column}->{params} ) } if ($self->{_column}->{params});
 569
 570     my $sql = $func . '("' . $self->{_relation} . '"."' . $self->name . '"';
 571     $sql .= ",'" . join("','", @params) . "'" if (@params);
 572     $sql .= ')';
 573
 574     return $sql;
 575 }
 576
 577 sub is_aggregate { return $self->{_aggregate} }
 578
 579 #-------------------------------------------------------------------------------------------------
 580 package OpenILS::Reporter::SQLBuilder::Column::Transform::Bare;
 581
 582 sub toSQL {
 583     my $self = shift;
 584     return '"' . $self->{_relation} . '"."' . $self->name . '"';
 585 }
 586
 587 sub is_aggregate { return 0 }
 588
 589 #-------------------------------------------------------------------------------------------------
 590 package OpenILS::Reporter::SQLBuilder::Column::Transform::upper;
 591
 592 sub toSQL {
 593     my $self = shift;
 594     my $params = $self->resolve_param( $self->{_column}->{params} );
 595     my $start = $$params[0];
 596     my $len = $$params[1];
 597     return 'UPPER("' . $self->{_relation} . '"."' . $self->name . '")';
 598 }
 599
 600 sub is_aggregate { return 0 }
 601
 602
 603 #-------------------------------------------------------------------------------------------------
 604 package OpenILS::Reporter::SQLBuilder::Column::Transform::lower;
 605
 606 sub toSQL {
 607     my $self = shift;
 608     my $params = $self->resolve_param( $self->{_column}->{params} );
 609     my $start = $$params[0];
 610     my $len = $$params[1];
 611     return 'evergreen.lowercase("' . $self->{_relation} . '"."' . $self->name . '")';
 612 }
 613
 614 sub is_aggregate { return 0 }
 615
 616
 617 #-------------------------------------------------------------------------------------------------
 618 package OpenILS::Reporter::SQLBuilder::Column::Transform::substring;
 619
 620 sub toSQL {
 621     my $self = shift;
 622     my $params = $self->resolve_param( $self->{_column}->{params} );
 623     my $start = $$params[0];
 624     my $len = $$params[1];
 625     return 'SUBSTRING("' . $self->{_relation} . '"."' . $self->name . "\",$start,$len)";
 626 }
 627
 628 sub is_aggregate { return 0 }
 629
 630
 631 #-------------------------------------------------------------------------------------------------
 632 package OpenILS::Reporter::SQLBuilder::Column::Transform::day_name;
 633
 634 sub toSQL {
 635     my $self = shift;
 636     return 'TO_CHAR("' . $self->{_relation} . '"."' . $self->name . '", \'Day\')';
 637 }
 638
 639 sub is_aggregate { return 0 }
 640
 641
 642 #-------------------------------------------------------------------------------------------------
 643 package OpenILS::Reporter::SQLBuilder::Column::Transform::month_name;
 644
 645 sub toSQL {
 646     my $self = shift;
 647     return 'TO_CHAR("' . $self->{_relation} . '"."' . $self->name . '", \'Month\')';
 648 }
 649
 650 sub is_aggregate { return 0 }
 651
 652
 653 #-------------------------------------------------------------------------------------------------
 654 package OpenILS::Reporter::SQLBuilder::Column::Transform::doy;
 655
 656 sub toSQL {
 657     my $self = shift;
 658     return 'EXTRACT(DOY FROM "' . $self->{_relation} . '"."' . $self->name . '")';
 659 }
 660
 661 sub is_aggregate { return 0 }
 662
 663
 664 #-------------------------------------------------------------------------------------------------
 665 package OpenILS::Reporter::SQLBuilder::Column::Transform::woy;
 666
 667 sub toSQL {
 668     my $self = shift;
 669     return 'EXTRACT(WEEK FROM "' . $self->{_relation} . '"."' . $self->name . '")';
 670 }
 671
 672 sub is_aggregate { return 0 }
 673
 674
 675 #-------------------------------------------------------------------------------------------------
 676 package OpenILS::Reporter::SQLBuilder::Column::Transform::moy;
 677
 678 sub toSQL {
 679     my $self = shift;
 680     return 'EXTRACT(MONTH FROM "' . $self->{_relation} . '"."' . $self->name . '")';
 681 }
 682
 683 sub is_aggregate { return 0 }
 684
 685
 686 #-------------------------------------------------------------------------------------------------
 687 package OpenILS::Reporter::SQLBuilder::Column::Transform::qoy;
 688
 689 sub toSQL {
 690     my $self = shift;
 691     return 'EXTRACT(QUARTER FROM "' . $self->{_relation} . '"."' . $self->name . '")';
 692 }
 693
 694 sub is_aggregate { return 0 }
 695
 696
 697 #-------------------------------------------------------------------------------------------------
 698 package OpenILS::Reporter::SQLBuilder::Column::Transform::dom;
 699
 700 sub toSQL {
 701     my $self = shift;
 702     return 'EXTRACT(DAY FROM "' . $self->{_relation} . '"."' . $self->name . '")';
 703 }
 704
 705 sub is_aggregate { return 0 }
 706
 707
 708 #-------------------------------------------------------------------------------------------------
 709 package OpenILS::Reporter::SQLBuilder::Column::Transform::dow;
 710
 711 sub toSQL {
 712     my $self = shift;
 713     return 'EXTRACT(DOW FROM "' . $self->{_relation} . '"."' . $self->name . '")';
 714 }
 715
 716 sub is_aggregate { return 0 }
 717
 718
 719 #-------------------------------------------------------------------------------------------------
 720 package OpenILS::Reporter::SQLBuilder::Column::Transform::year_trunc;
 721
 722 sub toSQL {
 723     my $self = shift;
 724     return 'EXTRACT(YEAR FROM "' . $self->{_relation} . '"."' . $self->name . '")';
 725 }
 726
 727 sub is_aggregate { return 0 }
 728
 729
 730 #-------------------------------------------------------------------------------------------------
 731 package OpenILS::Reporter::SQLBuilder::Column::Transform::month_trunc;
 732
 733 sub toSQL {
 734     my $self = shift;
 735     return 'EXTRACT(YEAR FROM "' . $self->{_relation} . '"."' . $self->name . '")' .
 736         ' || \'-\' || LPAD(EXTRACT(MONTH FROM "' . $self->{_relation} . '"."' . $self->name . '")::text,2,\'0\')';
 737 }
 738
 739 sub is_aggregate { return 0 }
 740
 741
 742 #-------------------------------------------------------------------------------------------------
 743 package OpenILS::Reporter::SQLBuilder::Column::Transform::date_trunc;
 744
 745 sub toSQL {
 746     my $self = shift;
 747     return 'DATE("' . $self->{_relation} . '"."' . $self->name . '")';
 748 }
 749
 750 sub is_aggregate { return 0 }
 751
 752
 753 #-------------------------------------------------------------------------------------------------
 754 package OpenILS::Reporter::SQLBuilder::Column::Transform::hour_trunc;
 755
 756 sub toSQL {
 757     my $self = shift;
 758     return 'EXTRACT(HOUR FROM "' . $self->{_relation} . '"."' . $self->name . '")';
 759 }
 760
 761 sub is_aggregate { return 0 }
 762
 763
 764 #-------------------------------------------------------------------------------------------------
 765 package OpenILS::Reporter::SQLBuilder::Column::Transform::quarter;
 766
 767 sub toSQL {
 768     my $self = shift;
 769     return 'EXTRACT(YEAR FROM "' . $self->{_relation} . '"."' . $self->name . '")' .
 770         ' || \'-Q\' || EXTRACT(QUARTER FROM "' . $self->{_relation} . '"."' . $self->name . '")';
 771 }
 772
 773 sub is_aggregate { return 0 }
 774
 775
 776 #-------------------------------------------------------------------------------------------------
 777 package OpenILS::Reporter::SQLBuilder::Column::Transform::months_ago;
 778
 779 sub toSQL {
 780     my $self = shift;
 781     return 'EXTRACT(MONTH FROM AGE(NOW(),"' . $self->{_relation} . '"."' . $self->name . '"))';
 782 }
 783
 784 sub is_aggregate { return 0 }
 785
 786
 787 #-------------------------------------------------------------------------------------------------
 788 package OpenILS::Reporter::SQLBuilder::Column::Transform::hod;
 789
 790 sub toSQL {
 791     my $self = shift;
 792     return 'EXTRACT(HOUR FROM "' . $self->{_relation} . '"."' . $self->name . '")';
 793 }
 794
 795 sub is_aggregate { return 0 }
 796
 797
 798 #-------------------------------------------------------------------------------------------------
 799 package OpenILS::Reporter::SQLBuilder::Column::Transform::quarters_ago;
 800
 801 sub toSQL {
 802     my $self = shift;
 803     return 'EXTRACT(QUARTER FROM AGE(NOW(),"' . $self->{_relation} . '"."' . $self->name . '"))';
 804 }
 805
 806 sub is_aggregate { return 0 }
 807
 808
 809 #-------------------------------------------------------------------------------------------------
 810 package OpenILS::Reporter::SQLBuilder::Column::Transform::age;
 811
 812 sub toSQL {
 813     my $self = shift;
 814     return 'AGE(NOW(),"' . $self->{_relation} . '"."' . $self->name . '")';
 815 }
 816
 817 sub is_aggregate { return 0 }
 818
 819
 820 #-------------------------------------------------------------------------------------------------
 821 package OpenILS::Reporter::SQLBuilder::Column::Transform::first;
 822
 823 sub toSQL {
 824     my $self = shift;
 825     return 'FIRST("' . $self->{_relation} . '"."' . $self->name . '")';
 826 }
 827
 828 sub is_aggregate { return 1 }
 829
 830
 831 #-------------------------------------------------------------------------------------------------
 832 package OpenILS::Reporter::SQLBuilder::Column::Transform::last;
 833
 834 sub toSQL {
 835     my $self = shift;
 836     return 'LAST("' . $self->{_relation} . '"."' . $self->name . '")';
 837 }
 838
 839 sub is_aggregate { return 1 }
 840
 841
 842 #-------------------------------------------------------------------------------------------------
 843 package OpenILS::Reporter::SQLBuilder::Column::Transform::min;
 844
 845 sub toSQL {
 846     my $self = shift;
 847     return 'MIN("' . $self->{_relation} . '"."' . $self->name . '")';
 848 }
 849
 850 sub is_aggregate { return 1 }
 851
 852
 853 #-------------------------------------------------------------------------------------------------
 854 package OpenILS::Reporter::SQLBuilder::Column::Transform::max;
 855
 856 sub toSQL {
 857     my $self = shift;
 858     return 'MAX("' . $self->{_relation} . '"."' . $self->name . '")';
 859 }
 860
 861 sub is_aggregate { return 1 }
 862
 863
 864 #-------------------------------------------------------------------------------------------------
 865 package OpenILS::Reporter::SQLBuilder::Column::Transform::count;
 866
 867 sub toSQL {
 868     my $self = shift;
 869     return 'COUNT("' . $self->{_relation} . '"."' . $self->name . '")';
 870 }
 871
 872 sub is_aggregate { return 1 }
 873
 874
 875 #-------------------------------------------------------------------------------------------------
 876 package OpenILS::Reporter::SQLBuilder::Column::Transform::count_distinct;
 877
 878 sub toSQL {
 879     my $self = shift;
 880     return 'COUNT(DISTINCT "' . $self->{_relation} . '"."' . $self->name . '")';
 881 }
 882
 883 sub is_aggregate { return 1 }
 884
 885
 886 #-------------------------------------------------------------------------------------------------
 887 package OpenILS::Reporter::SQLBuilder::Column::Transform::sum;
 888
 889 sub toSQL {
 890     my $self = shift;
 891     return 'SUM("' . $self->{_relation} . '"."' . $self->name . '")';
 892 }
 893
 894 sub is_aggregate { return 1 }
 895
 896
 897 #-------------------------------------------------------------------------------------------------
 898 package OpenILS::Reporter::SQLBuilder::Column::Transform::average;
 899
 900 sub toSQL {
 901     my $self = shift;
 902     return 'AVG("' . $self->{_relation} . '"."' . $self->name .  '")';
 903 }
 904
 905 sub is_aggregate { return 1 }
 906
 907
 908 #-------------------------------------------------------------------------------------------------
 909 package OpenILS::Reporter::SQLBuilder::Column::Where;
 910 use base qw/OpenILS::Reporter::SQLBuilder::Column/;
 911
 912 sub new {
 913     my $class = shift;
 914     my $self = $class->SUPER::new(@_);
 915
 916     my $col_data = shift;
 917     $self->{_condition} = $col_data->{condition};
 918
 919     return $self;
 920 }
 921
 922 sub _flesh_conditions {
 923     my $cond = shift;
 924     my $builder = shift;
 925     $cond = [$cond] unless (ref($cond) eq 'ARRAY');
 926
 927     my @out;
 928     for my $c (@$cond) {
 929         push @out, OpenILS::Reporter::SQLBuilder::Input->new( $c )->set_builder( $builder );
 930     }
 931
 932     return \@out;
 933 }
 934
 935 sub toSQL {
 936     my $self = shift;
 937
 938     return $self->{_sql} if ($self->{_sql});
 939
 940     my $sql = '';
 941
 942     my $rel = $self->find_relation();
 943     if ($rel && $rel->is_nullable) {
 944         $sql = "((". $self->SUPER::toSQL .") IS NULL OR ";
 945     }
 946
 947     $sql .= $self->SUPER::toSQL;
 948
 949     my ($op) = keys %{ $self->{_condition} };
 950     my $val = _flesh_conditions( $self->resolve_param( $self->{_condition}->{$op} ), $self->builder );
 951
 952     if (lc($op) eq 'in') {
 953         $sql .= " IN (". join(",", map { $_->toSQL } @$val).")";
 954
 955     } elsif (lc($op) eq 'not in') {
 956         $sql .= " NOT IN (". join(",", map { $_->toSQL } @$val).")";
 957
 958     } elsif (lc($op) eq '= any') {
 959         $val = $$val[0] if (ref($val) eq 'ARRAY');
 960         $val = $val->toSQL;
 961         if ($rel && $rel->is_nullable) { # need to redo this
 962             $sql = "((". $self->SUPER::toSQL .") IS NULL OR ";
 963         } else {
 964             $sql = '';
 965         }
 966         $sql .= "$val = ANY (".$self->SUPER::toSQL.")";
 967
 968     } elsif (lc($op) eq '<> any') {
 969         $val = $$val[0] if (ref($val) eq 'ARRAY');
 970         $val = $val->toSQL;
 971         if ($rel && $rel->is_nullable) { # need to redo this
 972             $sql = "((". $self->SUPER::toSQL .") IS NULL OR ";
 973         } else {
 974             $sql = '';
 975         }
 976         $sql .= "$val <> ANY (".$self->SUPER::toSQL.")";
 977
 978     } elsif (lc($op) eq 'is blank') {
 979         if ($rel && $rel->is_nullable) { # need to redo this
 980             $sql = "((". $self->SUPER::toSQL .") IS NULL OR ";
 981         } else {
 982             $sql = '';
 983         }
 984         $sql .= '('. $self->SUPER::toSQL ." IS NULL OR ". $self->SUPER::toSQL ." = '')";
 985
 986     } elsif (lc($op) eq 'is not blank') {
 987         if ($rel && $rel->is_nullable) { # need to redo this
 988             $sql = "((". $self->SUPER::toSQL .") IS NULL OR ";
 989         } else {
 990             $sql = '';
 991         }
 992         $sql .= '('. $self->SUPER::toSQL ." IS NOT NULL AND ". $self->SUPER::toSQL ." <> '')";
 993
 994     } elsif (lc($op) eq 'between') {
 995         $sql .= " BETWEEN ". join(" AND ", map { $_->toSQL } @$val);
 996
 997     } elsif (lc($op) eq 'not between') {
 998         $sql .= " NOT BETWEEN ". join(" AND ", map { $_->toSQL } @$val);
 999
1000     } elsif (lc($op) eq 'like') {
1001         $val = $$val[0] if (ref($val) eq 'ARRAY');
1002         $val = $val->toSQL;
1003         $val =~ s/^'(.*)'$/$1/o;
1004         $val =~ s/%/\\\\%/o;
1005         $val =~ s/_/\\\\_/o;
1006         $sql .= " LIKE '\%$val\%'";
1007
1008     } elsif (lc($op) eq 'ilike') {
1009         $val = $$val[0] if (ref($val) eq 'ARRAY');
1010         $val = $val->toSQL;
1011         $val =~ s/^'(.*)'$/$1/o;
1012         $val =~ s/%/\\\\%/o;
1013         $val =~ s/_/\\\\_/o;
1014         $sql .= " ILIKE '\%$val\%'";
1015
1016     } else {
1017         $val = $$val[0] if (ref($val) eq 'ARRAY');
1018         $sql .= " $op " . $val->toSQL;
1019     }
1020
1021     if ($rel && $rel->is_nullable) {
1022         $sql .= ")";
1023     }
1024
1025     return $self->{_sql} = $sql;
1026 }
1027
1028
1029 #-------------------------------------------------------------------------------------------------
1030 package OpenILS::Reporter::SQLBuilder::Column::Having;
1031 use base qw/OpenILS::Reporter::SQLBuilder::Column::Where/;
1032
1033 #-------------------------------------------------------------------------------------------------
1034 package OpenILS::Reporter::SQLBuilder::Relation;
1035 use base qw/OpenILS::Reporter::SQLBuilder/;
1036
1037 sub parse {
1038     my $self = shift;
1039     $self = $self->SUPER::new if (!ref($self));
1040
1041     my $rel_data = shift;
1042     my $b = shift;
1043     $self->set_builder($b);
1044
1045     $self->{_table} = $rel_data->{table};
1046     $self->{_alias} = $rel_data->{alias} || $self->{_table};
1047     $self->{_join} = [];
1048     $self->{_columns} = [];
1049
1050     $self->builder->{_rels}{$self->{_alias}} = $self;
1051
1052     if ($rel_data->{join}) {
1053         $self->add_join(
1054             $_ => OpenILS::Reporter::SQLBuilder::Relation->parse( $rel_data->{join}->{$_}, $b ) => $rel_data->{join}->{$_}->{key} => $rel_data->{join}->{$_}->{type}
1055         ) for ( keys %{ $rel_data->{join} } );
1056     }
1057
1058     return $self;
1059 }
1060
1061 sub add_column {
1062     my $self = shift;
1063     my $col = shift;
1064
1065     push @{ $self->{_columns} }, $col;
1066 }
1067
1068 sub find_column {
1069     my $self = shift;
1070     my $col = shift;
1071     return (grep { $_->name eq $col} @{ $self->{_columns} })[0];
1072 }
1073
1074 sub add_join {
1075     my $self = shift;
1076     my $col = shift;
1077     my $frel = shift;
1078     my $fkey = shift;
1079     my $type = lc(shift()) || 'inner';
1080
1081     if (UNIVERSAL::isa($col,'OpenILS::Reporter::SQLBuilder::Join')) {
1082         push @{ $self->{_join} }, $col;
1083     } else {
1084         push @{ $self->{_join} }, OpenILS::Reporter::SQLBuilder::Join->build( $self => $col, $frel => $fkey, $type );
1085     }
1086
1087     return $self;
1088 }
1089
1090 sub is_nullable {
1091     my $self = shift;
1092     return $self->{_nullable};
1093 }
1094
1095 sub is_join {
1096     my $self = shift;
1097     my $j = shift;
1098     $self->{_is_join} = $j if ($j);
1099     return $self->{_is_join};
1100 }
1101
1102 sub join_type {
1103     my $self = shift;
1104     my $j = shift;
1105     $self->{_join_type} = $j if ($j);
1106     return $self->{_join_type};
1107 }
1108
1109 sub toSQL {
1110     my $self = shift;
1111     return $self->{_sql} if ($self->{_sql});
1112
1113     my $sql = $self->{_table} .' AS "'. $self->{_alias} .'"';
1114
1115     if (!$self->is_join) {
1116         for my $j ( @{ $self->{_join} } ) {
1117             $sql .= $j->toSQL;
1118         }
1119     }
1120
1121     return $self->{_sql} = $sql;
1122 }
1123
1124 #-------------------------------------------------------------------------------------------------
1125 package OpenILS::Reporter::SQLBuilder::Join;
1126 use base qw/OpenILS::Reporter::SQLBuilder/;
1127
1128 sub build {
1129     my $class = shift;
1130     my $self = $class->SUPER::new if (!ref($class));
1131
1132     $self->{_left_rel} = shift;
1133     ($self->{_left_col}) = split(/-/,shift());
1134
1135     $self->{_right_rel} = shift;
1136     $self->{_right_col} = shift;
1137
1138     $self->{_join_type} = shift;
1139
1140     $self->{_right_rel}->set_builder($self->{_left_rel}->builder);
1141
1142     $self->{_right_rel}->is_join(1);
1143     $self->{_right_rel}->join_type($self->{_join_type});
1144
1145     bless $self => "OpenILS::Reporter::SQLBuilder::Join::$self->{_join_type}";
1146
1147     if ( $self->{_join_type} eq 'inner' or !$self->{_join_type}) {
1148         $self->{_join_type} = 'i';
1149     } else {
1150         if ($self->{_join_type} eq 'left') {
1151             $self->{_right_rel}->{_nullable} = 'l';
1152         } elsif ($self->{_join_type} eq 'right') {
1153             $self->{_left_rel}->{_nullable} = 'r';
1154         } else {
1155             $self->{_right_rel}->{_nullable} = 'f';
1156             $self->{_left_rel}->{_nullable} = 'f';
1157         }
1158     }
1159
1160     return $self;
1161 }
1162
1163 sub toSQL {
1164     my $self = shift;
1165     my $dir = shift;
1166
1167     my $sql = "JOIN " . $self->{_right_rel}->toSQL .
1168         ' ON ("' . $self->{_left_rel}->{_alias} . '"."' . $self->{_left_col} .
1169         '" = "' . $self->{_right_rel}->{_alias} . '"."' . $self->{_right_col} . '")';
1170
1171     $sql .= $_->toSQL($dir) for (@{ $self->{_right_rel}->{_join} });
1172
1173     return $sql;
1174 }
1175
1176 #-------------------------------------------------------------------------------------------------
1177 package OpenILS::Reporter::SQLBuilder::Join::left;
1178 use base qw/OpenILS::Reporter::SQLBuilder::Join/;
1179
1180 sub toSQL {
1181     my $self = shift;
1182     my $dir = shift;
1183     #return $self->{_sql} if ($self->{_sql});
1184
1185     my $j = $dir && $dir eq 'r' ? 'FULL OUTER' : 'LEFT OUTER';
1186
1187     my $sql = "\n\t$j ". $self->SUPER::toSQL('l');
1188
1189     #$sql .= $_->toSQL for (@{ $self->{_right_rel}->{_join} });
1190
1191     return $self->{_sql} = $sql;
1192 }
1193
1194 #-------------------------------------------------------------------------------------------------
1195 package OpenILS::Reporter::SQLBuilder::Join::right;
1196 use base qw/OpenILS::Reporter::SQLBuilder::Join/;
1197
1198 sub toSQL {
1199     my $self = shift;
1200     my $dir = shift;
1201     #return $self->{_sql} if ($self->{_sql});
1202
1203     my $_nullable_rel = $dir && $dir eq 'l' ? '_right_rel' : '_left_rel';
1204     $self->{_left_rel}->{_nullable} = 'r';
1205     $self->{$_nullable_rel}->{_nullable} = $dir;
1206
1207     my $j = $dir && $dir eq 'l' ? 'FULL OUTER' : 'RIGHT OUTER';
1208
1209     my $sql = "\n\t$j ". $self->SUPER::toSQL('r');
1210
1211     #$sql .= $_->toSQL for (@{ $self->{_right_rel}->{_join} });
1212
1213     return $self->{_sql} = $sql;
1214 }
1215
1216 #-------------------------------------------------------------------------------------------------
1217 package OpenILS::Reporter::SQLBuilder::Join::inner;
1218 use base qw/OpenILS::Reporter::SQLBuilder::Join/;
1219
1220 sub toSQL {
1221     my $self = shift;
1222     my $dir = shift;
1223     #return $self->{_sql} if ($self->{_sql});
1224
1225     my $_nullable_rel = $dir && $dir eq 'l' ? '_right_rel' : '_left_rel';
1226     $self->{$_nullable_rel}->{_nullable} = $dir;
1227
1228     my $j = 'INNER';
1229
1230     my $sql = "\n\t$j ". $self->SUPER::toSQL;
1231
1232     #$sql .= $_->toSQL for (@{ $self->{_right_rel}->{_join} });
1233
1234     return $self->{_sql} = $sql;
1235 }
1236
1237 #-------------------------------------------------------------------------------------------------
1238 package OpenILS::Reporter::SQLBuilder::Join::cross;
1239 use base qw/OpenILS::Reporter::SQLBuilder::Join/;
1240
1241 sub toSQL {
1242     my $self = shift;
1243     #return $self->{_sql} if ($self->{_sql});
1244
1245     $self->{_right_rel}->{_nullable} = 'f';
1246     $self->{_left_rel}->{_nullable} = 'f';
1247
1248     my $sql = "\n\tFULL OUTER ". $self->SUPER::toSQL('f');
1249
1250     #$sql .= $_->toSQL for (@{ $self->{_right_rel}->{_join} });
1251
1252     return $self->{_sql} = $sql;
1253 }
1254
1255 1;