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