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(YEAR FROM AGE(NOW(),"' . $self->{_relation} . '"."' . $self->name . '")) * 12) +'.
 764            ' EXTRACT(MONTH FROM AGE(NOW(),"' . $self->{_relation} . '"."' . $self->name . '"))';
 765 }
 766
 767 sub is_aggregate { return 0 }
 768
 769
 770 #-------------------------------------------------------------------------------------------------
 771 package OpenILS::Reporter::SQLBuilder::Column::Transform::hod;
 772
 773 sub toSQL {
 774     my $self = shift;
 775     return 'EXTRACT(HOUR FROM "' . $self->{_relation} . '"."' . $self->name . '")';
 776 }
 777
 778 sub is_aggregate { return 0 }
 779
 780
 781 #-------------------------------------------------------------------------------------------------
 782 package OpenILS::Reporter::SQLBuilder::Column::Transform::quarters_ago;
 783
 784 sub toSQL {
 785     my $self = shift;
 786     return '(EXTRACT(YEAR FROM AGE(NOW(),"' . $self->{_relation} . '"."' . $self->name . '")) * 4) +'.
 787            ' EXTRACT(QUARTER FROM AGE(NOW(),"' . $self->{_relation} . '"."' . $self->name . '"))';
 788 }
 789
 790 sub is_aggregate { return 0 }
 791
 792
 793 #-------------------------------------------------------------------------------------------------
 794 package OpenILS::Reporter::SQLBuilder::Column::Transform::age;
 795
 796 sub toSQL {
 797     my $self = shift;
 798     return 'AGE(NOW(),"' . $self->{_relation} . '"."' . $self->name . '")';
 799 }
 800
 801 sub is_aggregate { return 0 }
 802
 803
 804 #-------------------------------------------------------------------------------------------------
 805 package OpenILS::Reporter::SQLBuilder::Column::Transform::first;
 806
 807 sub toSQL {
 808     my $self = shift;
 809     return 'FIRST("' . $self->{_relation} . '"."' . $self->name . '")';
 810 }
 811
 812 sub is_aggregate { return 1 }
 813
 814
 815 #-------------------------------------------------------------------------------------------------
 816 package OpenILS::Reporter::SQLBuilder::Column::Transform::last;
 817
 818 sub toSQL {
 819     my $self = shift;
 820     return 'LAST("' . $self->{_relation} . '"."' . $self->name . '")';
 821 }
 822
 823 sub is_aggregate { return 1 }
 824
 825
 826 #-------------------------------------------------------------------------------------------------
 827 package OpenILS::Reporter::SQLBuilder::Column::Transform::min;
 828
 829 sub toSQL {
 830     my $self = shift;
 831     return 'MIN("' . $self->{_relation} . '"."' . $self->name . '")';
 832 }
 833
 834 sub is_aggregate { return 1 }
 835
 836
 837 #-------------------------------------------------------------------------------------------------
 838 package OpenILS::Reporter::SQLBuilder::Column::Transform::max;
 839
 840 sub toSQL {
 841     my $self = shift;
 842     return 'MAX("' . $self->{_relation} . '"."' . $self->name . '")';
 843 }
 844
 845 sub is_aggregate { return 1 }
 846
 847
 848 #-------------------------------------------------------------------------------------------------
 849 package OpenILS::Reporter::SQLBuilder::Column::Transform::count;
 850
 851 sub toSQL {
 852     my $self = shift;
 853     return 'COUNT("' . $self->{_relation} . '"."' . $self->name . '")';
 854 }
 855
 856 sub is_aggregate { return 1 }
 857
 858
 859 #-------------------------------------------------------------------------------------------------
 860 package OpenILS::Reporter::SQLBuilder::Column::Transform::count_distinct;
 861
 862 sub toSQL {
 863     my $self = shift;
 864     return 'COUNT(DISTINCT "' . $self->{_relation} . '"."' . $self->name . '")';
 865 }
 866
 867 sub is_aggregate { return 1 }
 868
 869
 870 #-------------------------------------------------------------------------------------------------
 871 package OpenILS::Reporter::SQLBuilder::Column::Transform::sum;
 872
 873 sub toSQL {
 874     my $self = shift;
 875     return 'SUM("' . $self->{_relation} . '"."' . $self->name . '")';
 876 }
 877
 878 sub is_aggregate { return 1 }
 879
 880
 881 #-------------------------------------------------------------------------------------------------
 882 package OpenILS::Reporter::SQLBuilder::Column::Transform::average;
 883
 884 sub toSQL {
 885     my $self = shift;
 886     return 'AVG("' . $self->{_relation} . '"."' . $self->name .  '")';
 887 }
 888
 889 sub is_aggregate { return 1 }
 890
 891
 892 #-------------------------------------------------------------------------------------------------
 893 package OpenILS::Reporter::SQLBuilder::Column::Where;
 894 use base qw/OpenILS::Reporter::SQLBuilder::Column/;
 895
 896 sub new {
 897     my $class = shift;
 898     my $self = $class->SUPER::new(@_);
 899
 900     my $col_data = shift;
 901     $self->{_condition} = $col_data->{condition};
 902
 903     return $self;
 904 }
 905
 906 sub _flesh_conditions {
 907     my $cond = shift;
 908     my $builder = shift;
 909     $cond = [$cond] unless (ref($cond) eq 'ARRAY');
 910
 911     my @out;
 912     for my $c (@$cond) {
 913         push @out, OpenILS::Reporter::SQLBuilder::Input->new( $c )->set_builder( $builder );
 914     }
 915
 916     return \@out;
 917 }
 918
 919 sub toSQL {
 920     my $self = shift;
 921
 922     return $self->{_sql} if ($self->{_sql});
 923
 924     my $sql = '';
 925
 926     my $rel = $self->find_relation();
 927     if ($rel && $rel->is_nullable) {
 928         $sql = "((". $self->SUPER::toSQL .") IS NULL OR ";
 929     }
 930
 931     $sql .= $self->SUPER::toSQL;
 932
 933     my ($op) = keys %{ $self->{_condition} };
 934     my $val = _flesh_conditions( $self->resolve_param( $self->{_condition}->{$op} ), $self->builder );
 935
 936     if (lc($op) eq 'in') {
 937         $sql .= " IN (". join(",", map { $_->toSQL } @$val).")";
 938
 939     } elsif (lc($op) eq 'not in') {
 940         $sql .= " NOT IN (". join(",", map { $_->toSQL } @$val).")";
 941
 942     } elsif (lc($op) eq '= any') {
 943         $val = $$val[0] if (ref($val) eq 'ARRAY');
 944         $val = $val->toSQL;
 945         if ($rel && $rel->is_nullable) { # need to redo this
 946             $sql = "((". $self->SUPER::toSQL .") IS NULL OR ";
 947         } else {
 948             $sql = '';
 949         }
 950         $sql .= "(".$self->SUPER::toSQL.") = ANY ($val)";
 951
 952     } elsif (lc($op) eq '<> any') {
 953         $val = $$val[0] if (ref($val) eq 'ARRAY');
 954         $val = $val->toSQL;
 955         if ($rel && $rel->is_nullable) { # need to redo this
 956             $sql = "((". $self->SUPER::toSQL .") IS NULL OR ";
 957         } else {
 958             $sql = '';
 959         }
 960         $sql .= "(".$self->SUPER::toSQL.") <> ANY ($val)";
 961
 962     } elsif (lc($op) eq 'is blank') {
 963         if ($rel && $rel->is_nullable) { # need to redo this
 964             $sql = "((". $self->SUPER::toSQL .") IS NULL OR ";
 965         } else {
 966             $sql = '';
 967         }
 968         $sql .= '('. $self->SUPER::toSQL ." IS NULL OR ". $self->SUPER::toSQL ." = '')";
 969
 970     } elsif (lc($op) eq 'is not blank') {
 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 .= '('. $self->SUPER::toSQL ." IS NOT NULL AND ". $self->SUPER::toSQL ." <> '')";
 977
 978     } elsif (lc($op) eq 'between') {
 979         $sql .= " BETWEEN SYMMETRIC ". join(" AND ", map { $_->toSQL } @$val);
 980
 981     } elsif (lc($op) eq 'not between') {
 982         $sql .= " NOT BETWEEN SYMMETRIC ". join(" AND ", map { $_->toSQL } @$val);
 983
 984     } elsif (lc($op) eq 'like') {
 985         $val = $$val[0] if (ref($val) eq 'ARRAY');
 986         $val = $val->toSQL;
 987         $val =~ s/\$_$$\$//g;
 988         $val =~ s/%/\\%/o;
 989         $val =~ s/_/\\_/o;
 990         $sql .= " LIKE \$_$$\$\%$val\%\$_$$\$";
 991
 992     } elsif (lc($op) eq 'ilike') {
 993         $val = $$val[0] if (ref($val) eq 'ARRAY');
 994         $val = $val->toSQL;
 995         $val =~ s/\$_$$\$//g;
 996         $val =~ s/%/\\%/o;
 997         $val =~ s/_/\\_/o;
 998         $sql .= " ILIKE \$_$$\$\%$val\%\$_$$\$";
 999
1000     } else {
1001         $val = $$val[0] if (ref($val) eq 'ARRAY');
1002         $sql .= " $op " . $val->toSQL;
1003     }
1004
1005     if ($rel && $rel->is_nullable) {
1006         $sql .= ")";
1007     }
1008
1009     return $self->{_sql} = $sql;
1010 }
1011
1012
1013 #-------------------------------------------------------------------------------------------------
1014 package OpenILS::Reporter::SQLBuilder::Column::Having;
1015 use base qw/OpenILS::Reporter::SQLBuilder::Column::Where/;
1016
1017 #-------------------------------------------------------------------------------------------------
1018 package OpenILS::Reporter::SQLBuilder::Relation;
1019 use base qw/OpenILS::Reporter::SQLBuilder/;
1020
1021 sub parse {
1022     my $self = shift;
1023     $self = $self->SUPER::new if (!ref($self));
1024
1025     my $rel_data = shift;
1026     my $b = shift;
1027     $self->set_builder($b);
1028
1029     $self->{_table} = $rel_data->{table};
1030     $self->{_alias} = $rel_data->{alias} || $self->{_table};
1031     $self->{_join} = [];
1032     $self->{_columns} = [];
1033
1034     $self->builder->{_rels}{$self->{_alias}} = $self;
1035
1036     if ($rel_data->{join}) {
1037         $self->add_join(
1038             $_ => OpenILS::Reporter::SQLBuilder::Relation->parse( $rel_data->{join}->{$_}, $b ) => $rel_data->{join}->{$_}->{key} => $rel_data->{join}->{$_}->{type}
1039         ) for ( keys %{ $rel_data->{join} } );
1040     }
1041
1042     return $self;
1043 }
1044
1045 sub add_column {
1046     my $self = shift;
1047     my $col = shift;
1048
1049     push @{ $self->{_columns} }, $col;
1050 }
1051
1052 sub find_column {
1053     my $self = shift;
1054     my $col = shift;
1055     return (grep { $_->name eq $col} @{ $self->{_columns} })[0];
1056 }
1057
1058 sub add_join {
1059     my $self = shift;
1060     my $col = shift;
1061     my $frel = shift;
1062     my $fkey = shift;
1063     my $type = lc(shift()) || 'inner';
1064
1065     if (UNIVERSAL::isa($col,'OpenILS::Reporter::SQLBuilder::Join')) {
1066         push @{ $self->{_join} }, $col;
1067     } else {
1068         push @{ $self->{_join} }, OpenILS::Reporter::SQLBuilder::Join->build( $self => $col, $frel => $fkey, $type );
1069     }
1070
1071     return $self;
1072 }
1073
1074 sub is_nullable {
1075     my $self = shift;
1076     return $self->{_nullable};
1077 }
1078
1079 sub is_join {
1080     my $self = shift;
1081     my $j = shift;
1082     $self->{_is_join} = $j if ($j);
1083     return $self->{_is_join};
1084 }
1085
1086 sub join_type {
1087     my $self = shift;
1088     my $j = shift;
1089     $self->{_join_type} = $j if ($j);
1090     return $self->{_join_type};
1091 }
1092
1093 sub toSQL {
1094     my $self = shift;
1095     return $self->{_sql} if ($self->{_sql});
1096
1097     my $sql = $self->{_table} .' AS "'. $self->{_alias} .'"';
1098
1099     if (!$self->is_join) {
1100         for my $j ( @{ $self->{_join} } ) {
1101             $sql .= $j->toSQL;
1102         }
1103     }
1104
1105     return $self->{_sql} = $sql;
1106 }
1107
1108 #-------------------------------------------------------------------------------------------------
1109 package OpenILS::Reporter::SQLBuilder::Join;
1110 use base qw/OpenILS::Reporter::SQLBuilder/;
1111
1112 sub build {
1113     my $class = shift;
1114     my $self = $class->SUPER::new if (!ref($class));
1115
1116     $self->{_left_rel} = shift;
1117     ($self->{_left_col}) = split(/-/,shift());
1118
1119     $self->{_right_rel} = shift;
1120     $self->{_right_col} = shift;
1121
1122     $self->{_join_type} = shift;
1123
1124     $self->{_right_rel}->set_builder($self->{_left_rel}->builder);
1125
1126     $self->{_right_rel}->is_join(1);
1127     $self->{_right_rel}->join_type($self->{_join_type});
1128
1129     bless $self => "OpenILS::Reporter::SQLBuilder::Join::$self->{_join_type}";
1130
1131     if ( $self->{_join_type} eq 'inner' or !$self->{_join_type}) {
1132         $self->{_join_type} = 'i';
1133     } else {
1134         if ($self->{_join_type} eq 'left') {
1135             $self->{_right_rel}->{_nullable} = 'l';
1136         } elsif ($self->{_join_type} eq 'right') {
1137             $self->{_left_rel}->{_nullable} = 'r';
1138         } else {
1139             $self->{_right_rel}->{_nullable} = 'f';
1140             $self->{_left_rel}->{_nullable} = 'f';
1141         }
1142     }
1143
1144     return $self;
1145 }
1146
1147 sub toSQL {
1148     my $self = shift;
1149     my $dir = shift;
1150
1151     my $sql = "JOIN " . $self->{_right_rel}->toSQL .
1152         ' ON ("' . $self->{_left_rel}->{_alias} . '"."' . $self->{_left_col} .
1153         '" = "' . $self->{_right_rel}->{_alias} . '"."' . $self->{_right_col} . '")';
1154
1155     $sql .= $_->toSQL($dir) for (@{ $self->{_right_rel}->{_join} });
1156
1157     return $sql;
1158 }
1159
1160 #-------------------------------------------------------------------------------------------------
1161 package OpenILS::Reporter::SQLBuilder::Join::left;
1162 use base qw/OpenILS::Reporter::SQLBuilder::Join/;
1163
1164 sub toSQL {
1165     my $self = shift;
1166     my $dir = shift;
1167     #return $self->{_sql} if ($self->{_sql});
1168
1169     my $j = $dir && $dir eq 'r' ? 'FULL OUTER' : 'LEFT OUTER';
1170
1171     my $sql = "\n\t$j ". $self->SUPER::toSQL('l');
1172
1173     #$sql .= $_->toSQL for (@{ $self->{_right_rel}->{_join} });
1174
1175     return $self->{_sql} = $sql;
1176 }
1177
1178 #-------------------------------------------------------------------------------------------------
1179 package OpenILS::Reporter::SQLBuilder::Join::right;
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->{_left_rel}->{_nullable} = 'r';
1189     $self->{$_nullable_rel}->{_nullable} = $dir;
1190
1191     my $j = $dir && $dir eq 'l' ? 'FULL OUTER' : 'RIGHT OUTER';
1192
1193     my $sql = "\n\t$j ". $self->SUPER::toSQL('r');
1194
1195     #$sql .= $_->toSQL for (@{ $self->{_right_rel}->{_join} });
1196
1197     return $self->{_sql} = $sql;
1198 }
1199
1200 #-------------------------------------------------------------------------------------------------
1201 package OpenILS::Reporter::SQLBuilder::Join::inner;
1202 use base qw/OpenILS::Reporter::SQLBuilder::Join/;
1203
1204 sub toSQL {
1205     my $self = shift;
1206     my $dir = shift;
1207     #return $self->{_sql} if ($self->{_sql});
1208
1209     my $_nullable_rel = $dir && $dir eq 'l' ? '_right_rel' : '_left_rel';
1210     $self->{$_nullable_rel}->{_nullable} = $dir;
1211
1212     my $j = 'INNER';
1213
1214     my $sql = "\n\t$j ". $self->SUPER::toSQL;
1215
1216     #$sql .= $_->toSQL for (@{ $self->{_right_rel}->{_join} });
1217
1218     return $self->{_sql} = $sql;
1219 }
1220
1221 #-------------------------------------------------------------------------------------------------
1222 package OpenILS::Reporter::SQLBuilder::Join::cross;
1223 use base qw/OpenILS::Reporter::SQLBuilder::Join/;
1224
1225 sub toSQL {
1226     my $self = shift;
1227     #return $self->{_sql} if ($self->{_sql});
1228
1229     $self->{_right_rel}->{_nullable} = 'f';
1230     $self->{_left_rel}->{_nullable} = 'f';
1231
1232     my $sql = "\n\tFULL OUTER ". $self->SUPER::toSQL('f');
1233
1234     #$sql .= $_->toSQL for (@{ $self->{_right_rel}->{_join} });
1235
1236     return $self->{_sql} = $sql;
1237 }
1238
1239 1;