- ( my $xlsname = $opt{'name'} ) =~ s/\W//g;
- #$opt{'name'} =~ s/s$// if $total == 1;
- $opt{'name'} =~ s/((s)e)?s$/$2/ if $total == 1; #should use Lingua::bs
- # to "depluralize"
-
- my @menubar = ();
- if ( $opt{'menubar'} ) {
- @menubar = @{ $opt{'menubar'} };
- } else {
- @menubar = ( 'Main menu' => $p );
- }
-
-
- %>
- <%= include( '/elements/header.html', $opt{'title'},
- include( '/elements/menubar.html', @menubar )
- )
- %>
- <%= defined($opt{'html_init'})
- ? ( ref($opt{'html_init'})
- ? &{$opt{'html_init'}}()
- : $opt{'html_init'}
- )
- : ''
- %>
- <% my $pager = include ( '/elements/pager.html',
- 'offset' => $offset,
- 'num_rows' => scalar(@$rows),
- 'total' => $total,
- 'maxrecords' => $maxrecords,
- );
- %>
- <% unless ( $total ) { %>
- <% unless ( $opt{'disable_nonefound'} ) { %>
- No matching <%= $opt{'name'} %> found.<BR>
- <% } %>
- <% } else { %>
-
- <TABLE>
- <TR>
- <TD VALIGN="bottom">
- <%= $total %> total <%= $opt{'name'} %>
- <%= defined($opt{'html_posttotal'})
- ? ( ref($opt{'html_posttotal'})
- ? &{$opt{'html_posttotal'}}()
- : $opt{'html_posttotal'}
- )
- : ''
- %>
- <BR>
- <% if ( $opt{'count_addl'} ) { %>
- <% my $n=0; foreach my $count ( @{$opt{'count_addl'}} ) { %>
- <%= sprintf( $count, $count_arrayref->[++$n] ) %><BR>
- <% } %>
- <% } %>
- </TD>
- <% unless ( $opt{'disable_download'} ) { %>
- <TD ALIGN="right">
- <% $cgi->param('_type', "$xlsname.xls" ); %>
- Download full results<BR>
- as <A HREF="<%= $cgi->self_url %>">Excel spreadsheet</A><BR>
- <% $cgi->param('_type', 'csv'); %>
- as <A HREF="<%= $cgi->self_url %>">CSV file</A>
- </TD>
- <% } %>
- </TR>
- <TR>
- <TD COLSPAN=2>
- <%= $pager %>
-
- <%= include('/elements/table-grid.html') %>
-
- <TR>
- <%
- foreach my $header ( @$header ) { %>
- <TH CLASS="grid" BGCOLOR="#cccccc"><%= $header %></TH>
- <% } %>
- </TR>
- <% my $bgcolor1 = '#eeeeee';
- my $bgcolor2 = '#ffffff';
- my $bgcolor;
- foreach my $row ( @$rows ) {
- if ( $bgcolor eq $bgcolor1 ) {
- $bgcolor = $bgcolor2;
- } else {
- $bgcolor = $bgcolor1;
- }
- %>
- <TR>
- <% if ( $opt{'fields'} ) {
-
- my $links = $opt{'links'} ? [ @{$opt{'links'}} ] : '';
- my $aligns = $opt{'align'} ? [ @{$opt{'align'}} ] : '';
- my $colors = $opt{'color'} ? [ @{$opt{'color'}} ] : [];
- my $sizes = $opt{'size'} ? [ @{$opt{'size'}} ] : [];
- my $styles = $opt{'style'} ? [ @{$opt{'style'}} ] : [];
-
- foreach my $field (
-
- map {
- if ( ref($_) eq 'ARRAY' ) {
-
- my $tableref = $_;
-
- '<TABLE BORDER=0 CELLSPACING=0 CELLPADDING=0'.
- ' STYLE="border:none">'.
-
- join('', map {
-
- my $rowref = $_;
-
- '<tr>'.
-
- join('', map {
-
- my $element = $_;
-
- '<TD STYLE="border:none"'.
- ( $element->{'align'}
- ? ' ALIGN="'. $element->{'align'}. '"'
- : ''
- ). '>'.
- ( $element->{'link'}
- ? '<A HREF="'. $element->{'link'}.'">'
- : ''
- ).
- $element->{'data'}.
- ( $element->{'link'}
- ? '</A>'
- : ''
- ).
- '</td>';
-
- } @$rowref ).
-
- '</tr>';
- } @$tableref ).
-
- '</table>';
-
- } else {
- $_;
- }
- }
-
- map {
- if ( ref($_) eq 'CODE' ) {
- &{$_}($row);
- } else {
- $row->$_();
- }
- }
- @{$opt{'fields'}}
-
- ) {
-
- my $align = $aligns ? shift @$aligns : '';
- $align = " ALIGN=$align" if $align;
-
- my $a = '';
- if ( $links ) {
- my $link = shift @$links;
- $link = &{$link}($row) if ref($link) eq 'CODE';
- if ( $link ) {
- my( $url, $method ) = @{$link};
- if ( ref($method) eq 'CODE' ) {
- $a = $url. &{$method}($row);
- } else {
- $a = $url. $row->$method();
- }
- $a = qq(<A HREF="$a">);
- }
- }
-
- my $font = '';
- my $color = shift @$colors;
- $color = &{$color}($row) if ref($color) eq 'CODE';
- my $size = shift @$sizes;
- $size = &{$size}($row) if ref($size) eq 'CODE';
- if ( $color || $size ) {
- $font = '<FONT '.
- ( $color ? "COLOR=#$color " : '' ).
- ( $size ? qq(SIZE="$size" ) : '' ).
- '>';
- }
-
- my($s, $es) = ( '', '' );
- my $style = shift @$styles;
- $style = &{$style}($row) if ref($style) eq 'CODE';
- if ( $style ) {
- $s = join( '', map "<$_>", split('', $style) );
- $es = join( '', map "</$_>", split('', $style) );
- }
-
- %>
- <TD CLASS="grid" BGCOLOR="<%= $bgcolor %>"<%= $align %>><%= $font %><%= $a %><%= $s %><%= $field %><%= $es %><%= $a ? '</A>' : '' %><%= $font ? '</FONT>' : '' %></TD>
- <% } %>
- <% } else { %>
- <% foreach ( @$row ) { %>
- <TD CLASS="grid" BGCOLOR="<%= $bgcolor %>"><%= $_ %></TD>
- <% } %>
- <% } %>
- </TR>
- <% } %>
-
- <% if ( $opt{'footer'} ) { %>
- <TR>
- <% foreach my $footer ( @{ $opt{'footer'} } ) { %>
- <TD CLASS="grid" BGCOLOR="#dddddd" STYLE="border-top: dashed 1px black;"><i><%= $footer %></i></TH>
- <% } %>
- </TR>
- <% } %>
-
- </TABLE>
- <%= $pager %>
-
- </TD>
- </TR>
- </TABLE>
-
- <% } %>
- <%= defined($opt{'html_foot'})
- ? ( ref($opt{'html_foot'})
- ? &{$opt{'html_foot'}}()
- : $opt{'html_foot'}
- )
- : ''
- %>
- <%= include( '/elements/footer.html' ) %>
- <% } %>
-<% } %>
+ $maxrecords ||= $confmax;
+ }
+
+ $limit = $maxrecords ? "LIMIT $maxrecords" : '';
+
+ $offset = $cgi->param('offset') =~ /^(\d+)$/ ? $1 : 0;
+ $limit .= " OFFSET $offset" if $offset;
+
+ }
+
+ my $count_sth = dbh->prepare($opt{'count_query'})
+ or die "Error preparing $opt{'count_query'}: ". dbh->errstr;
+ $count_sth->execute
+ or die "Error executing $opt{'count_query'}: ". $count_sth->errstr;
+ $count_arrayref = $count_sth->fetchrow_arrayref;
+ $total = $count_arrayref->[0];
+
+}
+
+# run the query
+
+my $header = $opt{header};
+my $rows;
+if ( ref($opt{query}) ) {
+
+ if ( $opt{disableable} && ! $cgi->param('showdisabled') ) {
+ #%search = ( 'disabled' => '' );
+ $opt{'query'}->{'hashref'}->{'disabled'} = '';
+ $opt{'query'}->{'extra_sql'} =~ s/^\s*WHERE/ AND/i;
+ }
+
+ #eval "use FS::$opt{'query'};";
+ $rows = [ qsearch({
+ 'select' => $opt{'query'}->{'select'},
+ 'table' => $opt{'query'}->{'table'},
+ 'addl_from' => (exists($opt{'query'}->{'addl_from'}) ? $opt{'query'}->{'addl_from'} : ''),
+ 'hashref' => $opt{'query'}->{'hashref'} || {},
+ 'extra_sql' => $opt{'query'}->{'extra_sql'},
+ 'order_by' => $opt{'query'}->{'order_by'}. " $limit",
+ }) ];
+} else {
+ my $sth = dbh->prepare("$opt{'query'} $limit")
+ or die "Error preparing $opt{'query'}: ". dbh->errstr;
+ $sth->execute
+ or die "Error executing $opt{'query'}: ". $sth->errstr;
+
+ #can get # of rows without fetching them all?
+ $rows = $sth->fetchall_arrayref;
+
+ $header ||= $sth->{NAME};
+}
+
+</%init>