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