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 => 'substring', params => [ 1, 4 ] },
  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                 },
  48         },
  49         where => [
  50                 {       relation        => 'circ-checkin_lib-aou',
  51                         column          => 'id',
  52                         condition       => { 'in' => '::PARAM1' },
  53                 },
  54                 {       relation        => 'circ',
  55                         column          => { transform => month_trunc => colname => 'checkin_time' },
  56                         condition       => { 'in' => '::PARAM2' },
  57                 },
  58         ],
  59         having => [
  60                 {       relation        => 'circ',
  61                         column          => { transform => count => colname => 'id' },
  62                         condition       => { '>' => '::PARAM5' },
  63                 },
  64         ],
  65         order_by => [
  66                 {       relation=> 'circ',
  67                         column  => { transform => count => colname => 'id' },
  68                         direction => 'descending',
  69                 },
  70                 {       relation=> 'circ-checkin_lib-aou',
  71                         column  => 'shortname',
  72                 },
  73                 {       relation=> 'circ',
  74                         column  => { transform => month_trunc => colname => 'checkin_time' },
  75                         direction => 'descending'
  76                 },
  77                 {       relation=> 'circ-circ_staff-au-card-ac',
  78                         column  => 'barcode',
  79                 },
  80         ],
  81
  82 };
  83
  84 my $params = {
  85         PARAM1 => [ 18, 19, 20, 21, 22, 23 ],
  86         PARAM2 => ['2006-07','2006-08','2006-09'],
  87         PARAM3 => 'Circ Count',
  88         PARAM4 => 'Checkin Date',
  89         PARAM5 => 100,
  90 };
  91
  92 my $r = OpenILS::Reporter::SQLBuilder->new;
  93
  94 $r->register_params( $params );
  95 $r->parse_report( $report );
  96
  97 print $r->toSQL;
  98