Table driven reporting from Remedy (was RE: [Arsperl-users] question regarding the query() method)

[Richard H. <rl...@ls...>]

	With all this discussion about remembering which symbols to use when
(i.e. when assembling qualifiers or embedding field-ids ), I thought I'd
share the way I generate reports/emails etc from Remedy.  Precisely because
of the differences between inner/outer labels and the need to add new fields
to reports or change column order or sort order, what I do now is to hold
the fields of interest in a table which is traversed automatically (e.g. for
ars_GetListEntryWithFields() calls) and also for the subsequent formatting.

For example, for a "helpdesk" schema, you could have:

My @field_tab = (
[ 'Userid',	'User ID', [1,1], undef ],
[ 'Create Date', undef, [2,1], \&fix_date ],
[ 'Description', undef, undef, undef],
	:
	:
	etc
);

Before showing how this is used, I'll explain what each row contains.  There
are four fields in each line:
1)	Internal name of the field 
2)	External name of the field (will be later used as a column header
when HTML/spreadsheet is generated). If the same as (1), then undef is used.
3)	Sort specifier.  If undef, then this field is ignored for sort
purposes but otherwise the number pair specifies the sort order and whether
this field should be sorted up or down
4)	Lastly there is a function pointer that (if not undef) refers to a
perl function that will be called on each "ticket" to post-process a
particular field (e.g. to turn 111181810 into "July 10th ..."). No fixup
means that the field's contents are emitted verbatim.

OK, to find the "tickets" matching some $qualifier using GLEWF, I put (with
ctrl, schema, fids having their normal meaning):

my @ticket_list = ars_GetListEntryWithFields(
                $ctrl
                $schema,
                $qualifier,
                0,
                0,
                [
                        map {
                                $fids->{$_->[0]}
                        } grep {
                                defined($_->[0])
                        } @field_tab
                ],
                map {
                        ($fids->{$_->[0]}, $_->[2]->[1])
                } sort {
                        $a->[2]->[0] <=> $b->[2]->[0]
                } grep {
                        defined($_->[2])
                } @field_tab
        );

OK, so far we have a list of matches generated from the @field_tab above and
the choice of fields to use/sort on is kept separate from the query itself
so different queries can be handled by using alternative field tables. The
gain here is when the boss comes in and says, "No, I want this column here
and that one there and the whole thing sorted on such-and-such".  All you do
is change the order of the rows in the table above and (maybe) change the
sort specifiers (Adding / Deleting rows has the obvious effect as does
commenting out some lines during testing).

Next, displaying the results.  I do (more or less (using the Perl CGI module
to get HTML shortcuts)):


First the column headers are extracted from the field table (using the
"default" mechanism I mentioned above)


my @rows;
push @rows, Tr(
       {
             -class => 'odd',
       },
       th(
               [
                      map {
                            $_->[1] || $_->[0]
                      } @field_tab
                ]
        )
);

Then the "meat" is emitted by traversing the @ticket_list, and for each one,
the @field_tab to pick out and (possibly) post-process the value.

for (my $i = 0; $i < scalar(@ticket_list); $i += 2) {
	my ($ticket_no, $ticket_props) = @ticket_list[$i, $i + 1];

      my @this_row;

      foreach my $f (@$field_tab) {
          my ($arLabel, $webLabel, $sortKey, $fixup) = @$f;

          next unless ($arLabel);

          my $value = $ticket_props->{$fids->{$arLabel}};

          $value = $fixup->($q, $value) if ($fixup);

          push @this_row, $value;
      }

      push @this_row, a(
            {
                   -title=>"Show full details of ticket $ticket_no",
                   -target=>"_new",
                   -href=> SOME URL ...
            },
            '... more',
     );

     push @rows, Tr(
                       td(\@this_row)
	);
}

The @rows structure is then emitted using the table() function.


----------------------


If this wasn't clear, please let me know.

Cheers,

Richard.