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                 {       relation=> 'circ-id-mb',
  26                         column  => { transform => sum => colname => 'amount' },
  27                         alias   => 'total bills',
  28                 },
  29         ],
  30         from => {
  31                 table   => 'action.circulation',
  32                 alias   => 'circ',
  33                 join    => {
  34                         checkin_staff => {
  35                                 table   => 'actor.usr',
  36                                 alias   => 'circ-circ_staff-au',
  37                                 key     => 'id',
  38                                 join    => {
  39                                         card => {
  40                                                 table   => 'actor.card',
  41                                                 alias   => 'circ-circ_staff-au-card-ac',
  42                                                 key     => 'id',
  43                                         },
  44                                 },
  45                         },
  46                         checkin_lib => {
  47                                 table   => 'actor.org_unit',
  48                                 alias   => 'circ-checkin_lib-aou',
  49                                 key     => 'id',
  50                         },
  51                         'id-billings' => {
  52                                 table   => 'money.billing',
  53                                 alias   => 'circ-id-mb',
  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                 {       relation        => 'circ',
  74                         column          => { transform => count => colname => 'id' },
  75                         condition       => { 'between' => '::PARAM5' },
  76                 },
  77         ],
  78         order_by => [
  79                 {       relation=> 'circ',
  80                         column  => { transform => count => colname => 'id' },
  81                         direction => 'descending',
  82                 },
  83                 {       relation=> 'circ-checkin_lib-aou',
  84                         column  => { colname => 'shortname', transform => 'Bare' },
  85                 },
  86                 {       relation=> 'circ',
  87                         column  => { transform => month_trunc => colname => 'checkin_time' },
  88                         direction => 'descending'
  89                 },
  90                 {       relation=> 'circ-circ_staff-au-card-ac',
  91                         column  => 'barcode',
  92                 },
  93         ],
  94 };
  95
  96 my $params = {
  97         PARAM1 => [ 18, 19, 20, 21, 22, 23 ],
  98         #PARAM2 => ['2006-07','2006-08','2006-09'],
  99         PARAM2 => [{transform => 'relative_month', params => [-2]},{transform => 'relative_month', params => [-3]}],
 100         PARAM3 => 'Circ Count',
 101         PARAM4 => 'Checkin Date',
 102         PARAM5 => [{ transform => 'Bare', params => [10] },{ transform => 'Bare', params => [100] }],
 103         PARAM6 => [ 1, 4 ],
 104         PARAM7 => 'f',
 105 };
 106
 107 my $r = OpenILS::Reporter::SQLBuilder->new;
 108
 109 $r->register_params( $params );
 110 my $rs = $r->parse_report( $report );
 111
 112 print "Column Labels: " . join(', ', $rs->column_label_list) . "\n";
 113 print $rs->toSQL;
 114