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