67c8af6ca96d858fd6841fa806a92aea9086b3bb
[working/Evergreen.git] / 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 = $dir ? ( $dir eq 'l' ? 'LEFT OUTER' : ( $dir eq 'r' ? 'RIGHT OUTER' : 'FULL OUTER' ) ) : '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;