Open-ILS/examples/reporter-sql-builder-test.pl

   1 #!/usr/bin/perl
   2 use diagnostics;
   3 use warnings;
   4 use strict;
   5 use OpenILS::Reporter::SQLBuilder;
   6
   7 my $report = {
   8         select => [
   9                 {       relation=> 'circ',
  10                         column  => { transform => month_trunc => colname => 'checkin_time' },
  11                         alias   => '::PARAM4',
  12                 },
  13                 {       relation=> 'circ-checkin_lib-aou',
  14                         column  => { colname => 'shortname', transform => 'Bare'},
  15                         alias   => 'Library Short Name',
  16                 },
  17                 {       relation=> 'circ-circ_staff-au-card-ac',
  18                         column  => 'barcode',
  19                         alias   => 'User Barcode',
  20                 },
  21                 {       relation=> 'circ-id-mb',
  22                         column  => { transform => sum => colname => 'amount' },
  23                         alias   => '::PARAM3',
  24                 },
  25         ],
  26         from => {
  27                 table   => 'action.circulation',
  28                 alias   => 'circ',
  29                 join    => {
  30                         checkin_staff => {
  31                                 table   => 'actor.usr',
  32                                 alias   => 'circ-circ_staff-au',
  33                                 type    => 'inner',
  34                                 key     => 'id',
  35                                 join    => {
  36                                         card => {
  37                                                 table   => 'actor.card',
  38                                                 alias   => 'circ-circ_staff-au-card-ac',
  39                                                 type    => 'inner',
  40                                                 key     => 'id',
  41                                         },
  42                                 },
  43                         },
  44                         checkin_lib => {
  45                                 table   => 'actor.org_unit',
  46                                 alias   => 'circ-checkin_lib-aou',
  47                                 type    => 'inner',
  48                                 key     => 'id',
  49                         },
  50                         'id-billings' => {
  51                                 table   => 'money.billing',
  52                                 alias   => 'circ-id-mb',
  53                                 type    => 'left',
  54                                 key     => 'xact',
  55                         },
  56                 },
  57         },
  58         where => [
  59                 {       relation        => 'circ-checkin_lib-aou',
  60                         column          => 'id',
  61                         condition       => { 'in' => '::PARAM1' },
  62                 },
  63                 {       relation        => 'circ',
  64                         column          => { transform => month_trunc => colname => 'checkin_time' },
  65                         condition       => { 'in' => '::PARAM2' },
  66                 },
  67                 {       relation        => 'circ-id-mb',
  68                         column          => 'voided',
  69                         condition       => { '=' => '::PARAM7' },
  70                 },
  71         ],
  72         having => [],
  73         order_by => [
  74                 {       relation=> 'circ-checkin_lib-aou',
  75                         column  => { colname => 'shortname', transform => 'Bare' },
  76                 },
  77                 {       relation=> 'circ',
  78                         column  => { transform => month_trunc => colname => 'checkin_time' },
  79                         direction => 'descending'
  80                 },
  81                 {       relation=> 'circ-circ_staff-au-card-ac',
  82                         column  => 'barcode',
  83                 },
  84         ],
  85         pivot_default => 0,
  86         pivot_data => 4,
  87         pivot_label => 2,
  88 };
  89
  90 my $params = {
  91         PARAM1 => [ 18, 19, 20, 21, 22, 23 ],
  92         #PARAM2 => ['2006-07','2006-08','2006-09'],
  93         PARAM2 => [{transform => 'relative_month', params => [-2]},{transform => 'relative_month', params => [-3]}],
  94         PARAM3 => 'Billed Amount',
  95         PARAM4 => 'Checkin Date',
  96         PARAM5 => [{ transform => 'Bare', params => [10] },{ transform => 'Bare', params => [100] }],
  97         PARAM6 => [ 1, 4 ],
  98         PARAM7 => 'f',
  99 };
 100
 101 my $r = OpenILS::Reporter::SQLBuilder->new;
 102
 103 $r->register_params( $params );
 104 my $rs = $r->parse_report( $report );
 105 $rs->relative_time('2006-10-01T00:00:00-4');
 106
 107 print "Column Labels: " . join(', ', $rs->column_label_list) . "\n";
 108 print $rs->toSQL;
 109
 110 print "\n\n";
 111
 112 print "SQL group by list: ".join(',',$rs->group_by_list)."\n";
 113 print "Perl group by list: ".join(',',$rs->group_by_list(0))."\n";
 114