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',
  22                         column  => { transform => count => colname => 'id' },
  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                                 key     => 'id',
  34                                 join    => {
  35                                         card => {
  36                                                 table   => 'actor.card',
  37                                                 alias   => 'circ-circ_staff-au-card-ac',
  38                                                 key     => 'id',
  39                                         },
  40                                 },
  41                         },
  42                         checkin_lib => {
  43                                 table   => 'actor.org_unit',
  44                                 alias   => 'circ-checkin_lib-aou',
  45                                 key     => 'id',
  46                         },
  47                         'id-billings' => {
  48                                 table   => 'money.billing',
  49                                 alias   => 'circ-id-mb',
  50                                 key     => 'xact',
  51                         },
  52                 },
  53         },
  54         where => [
  55                 {       relation        => 'circ-checkin_lib-aou',
  56                         column          => 'id',
  57                         condition       => { 'in' => '::PARAM1' },
  58                 },
  59                 {       relation        => 'circ',
  60                         column          => { transform => month_trunc => colname => 'checkin_time' },
  61                         condition       => { 'in' => '::PARAM2' },
  62                 },
  63                 {       relation        => 'circ-id-mb',
  64                         column          => 'voided',
  65                         condition       => { '=' => '::PARAM7' },
  66                 },
  67         ],
  68         having => [
  69                 {       relation        => 'circ',
  70                         column          => { transform => count => colname => 'id' },
  71                         condition       => { 'between' => '::PARAM5' },
  72                 },
  73         ],
  74         order_by => [
  75                 {       relation=> 'circ',
  76                         column  => { transform => count => colname => 'id' },
  77                         direction => 'descending',
  78                 },
  79                 {       relation=> 'circ-checkin_lib-aou',
  80                         column  => { colname => 'shortname', transform => 'Bare' },
  81                 },
  82                 {       relation=> 'circ',
  83                         column  => { transform => month_trunc => colname => 'checkin_time' },
  84                         direction => 'descending'
  85                 },
  86                 {       relation=> 'circ-circ_staff-au-card-ac',
  87                         column  => 'barcode',
  88                 },
  89         ],
  90         pivot_default => 0,
  91         pivot_data => 4,
  92         pivot_label => 2,
  93 };
  94
  95 my $params = {
  96         PARAM1 => [ 18, 19, 20, 21, 22, 23 ],
  97         #PARAM2 => ['2006-07','2006-08','2006-09'],
  98         PARAM2 => [{transform => 'relative_month', params => [-2]},{transform => 'relative_month', params => [-3]}],
  99         PARAM3 => 'Circ Count',
 100         PARAM4 => 'Checkin Date',
 101         PARAM5 => [{ transform => 'Bare', params => [10] },{ transform => 'Bare', params => [100] }],
 102         PARAM6 => [ 1, 4 ],
 103         PARAM7 => 'f',
 104 };
 105
 106 my $r = OpenILS::Reporter::SQLBuilder->new;
 107
 108 $r->register_params( $params );
 109 my $rs = $r->parse_report( $report );
 110 $rs->relative_time('2006-10-01T00:00:00-4');
 111
 112 print "Column Labels: " . join(', ', $rs->column_label_list) . "\n";
 113 print $rs->toSQL;
 114