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  => { date => 'checkin_time' },
  11                         alias   => '::PARAM4',
  12                 },
  13                 {       relation=> 'circ-checkin_lib-aou',
  14                         column  => 'shortname',
  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  => { count => '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          => 'checkin_time',
  56                         condition       => { between => '::PARAM2' },
  57                 },
  58         ],
  59 };
  60
  61 my $params = {
  62         PARAM1 => [ 1, 2, 3, 4, 5, 6 ],
  63         PARAM2 => [ '2006-09-01', '2006-10-01' ],
  64         PARAM3 => 'Circ Count',
  65         PARAM4 => 'Checkin Date',
  66 };
  67
  68 my $r = OpenILS::Reporter::SQLBuilder->new;
  69
  70 $r->register_params( $params );
  71 $r->parse_report( $report );
  72
  73 print $r->toSQL;
  74