File Coverage

blib/lib/SQLite/Work/CGI.pm

Criterion	Covered	Total	%
statement	15	600	2.5
branch	0	200	0.0
condition	0	39	0.0
subroutine	5	25	20.0
pod	20	20	100.0
total	40	884	4.5

package SQLite::Work::CGI;
$SQLite::Work::CGI::VERSION = '0.15';
use strict;
use warnings;

=head1 NAME

SQLite::Work::CGI - Report and update a SQLite database using CGI

=head1 VERSION

version 0.15

=head1 SYNOPSIS

    use SQLite::Work::CGI;

    my $obj = SQLite::Work::CGI->new(%args);

=head1 DESCRIPTION

This module is an expansion of SQLite::Work suitable for use
in a CGI script to report and update a SQLite database.

=cut

use CGI;
use POSIX;
use SQLite::Work;

our @ISA = qw(SQLite::Work);

=head1 CLASS METHODS

=head2 new

my $obj = SQLite::Work->new(
    database=>$database_file,
    row_ids=>{
	    episodes=>'title_id',
	},
    join_cols=>{
	    'episodes+recordings'=>'title_id',
	    }
	},
    report_template=>$report_template,
    default_format=>{
	    'episodes' => {
		'title'=>'title',
		'series_title'=>'title',
	    }
	},
    input_format=>{
	    'reviews' => {
		'Review'=>{
		    type=>'textarea',
		    cols=>60,
		    rows=>4,
		}
	    }
    },
    max_sort_fields=>10,
    sort_label=>'Zsort',
    sort_reversed_prefix=>'Zsort_reversed_',
    headers_label=>'Zheader_',
    show_label=>'Zshow',
    where_prefix=>'Zwhere_',
    not_prefix=>'Znot_',
    );

Make a new report object.

Takes the same arguments as L::new() plus the
following additions:

=over

=item input_format

This contains information about what style of input field
should be used for this particular column in this table.
This is used for the Edit and Add forms.

=item max_sort_fields

The maximum number of sort fields required (default: 10)

=item sort_label

Name of the sort parameter.

=item sort_reversed_prefix

Prefix of the sort-reversed parameters.

=item headers_label

Name of the headers parameter.

=item show_label

Name of the columns-to-show parameter.

=item where_prefix

Prefix of the 'where' parameters.

=item not_prefix

Prefix of the not-where parameters.

=back

=cut

sub new {
    my $class = shift;
    my %parameters = (@_);
    my $self = SQLite::Work->new(%parameters);

    # CGI-related defaults
    $self->{max_sort_fields} = 10;
    $self->{max_headers} = 4;
    $self->{sort_label} ||= 'Zsort';
    $self->{sort_reversed_prefix} ||= 'Zsort_reversed_';
    $self->{headers_label} ||= 'Zheader';
    $self->{show_label} ||= 'Zshow';
    $self->{where_prefix} ||= 'Zwhere_';
    $self->{not_prefix} ||= 'Znot_';

    my $ldelim = $self->{ldelim};
    my $rdelim = $self->{rdelim};
    $self->{message} = '';

    # this creates a new CGI object which has already parsed the query
    $self->{cgi} = new CGI;
    bless ($self, ref ($class) || $class);
} # new

=head1 OBJECT METHODS

=head2 do_select

$obj->do_select($table,
    command=>'Search');

Select data from a table in the database.
Uses CGI to get most of the parameters.

The 'command' is 'Search' by default; if it is something else,
then the result generated has edit fields and buttons in it.

=cut
sub do_select {
    my $self = shift;
    my $table = shift;
    my %args = (
	command=>'Search',
	outfile=>'',
	@_
    );
    my $command = $args{command};

    my $where_prefix = $self->{where_prefix};
    my $not_prefix = $self->{not_prefix};
    my $show_label = $self->{show_label};
    my $sort_label = $self->{sort_label};
    my $sort_reversed_prefix = $self->{sort_reversed_prefix};
    my $headers_label = $self->{headers_label};
    my @columns = ();
    my %where = ();
    my %not_where = ();
    my @sort_by = ();
    my @sort_r = ();
    my %sort_reverse = ();
    my @headers = ();
    my $limit = $self->{cgi}->param('Limit');
    $limit = 0 if !$limit;
    my $page = $self->{cgi}->param('Page');
    $page = 1 if !$page;
    my $row_id_name = $self->get_id_colname($table);

    # build up the data
    foreach my $pfield ($self->{cgi}->param())
    {
	my $pval = $self->{cgi}->param($pfield);
	if ($pfield eq $show_label)
	{
	    my (@show) = $self->{cgi}->param($pfield);
	    foreach my $scol (@show)
	    {
		# only show non-empty values!
		if ($scol)
		{
		    push @columns, $scol;
		}
	    }
	}
	elsif ($pfield =~ /^${where_prefix}(.*)/o)
	{
	    my $colname = $1;
	    if ($pval)
	    {
		my $not_where_field = "${not_prefix}${colname}";
		$pval =~ m#([^`]*)#;
		my $where_val = $1;
		$where_val =~ s/\s$//;
		$where_val =~ s/^\s//;
		if ($where_val)
		{
		    $where{$colname} = $where_val;
		    if ($self->{cgi}->param($not_where_field))
		    {
			$not_where{$colname} = 1;
		    }
		}
	    }
	}
	elsif ($pfield eq 'Edit_Row')
	{
	    # show the row given in the Edit_Row value
	    if ($pval)
	    {
		$pval =~ m#Edit Row ([\d]+)#;
		my $where_val = $1;
		if ($where_val)
		{
		    $where{$row_id_name} = $where_val;
		}
	    }
	}
	elsif ($pfield eq $sort_label)
	{
	    my (@vals) = $self->{cgi}->param($pfield);
	    foreach my $val (@vals)
	    {
		# only non-empty values!
		if ($val)
		{
		    push @sort_by, $val;
		}
	    }
	}
	elsif ($pfield eq $headers_label)
	{
	    my (@vals) = $self->{cgi}->param($pfield);
	    foreach my $val (@vals)
	    {
		# only non-empty values!
		if ($val)
		{
		    push @headers, $val;
		}
	    }
	}
	elsif ($pfield =~ /^${sort_reversed_prefix}(.*)/o)
	{
	    my $ind = $1;
	    $sort_r[$ind] = ($pval ? 1 : 0);
	}
    }
    @columns = $self->get_colnames($table) if !@columns;
    if (@sort_by)
    {
	for (my $i=0; $i < @sort_r; $i++)
	{
	    if ($sort_r[$i])
	    {
		$sort_reverse{$sort_by[$i]} = 1;
	    }
	}
    }

    $self->do_report(
	table=>$table,
	table2=>($self->{cgi}->param('Table2')
		 ? $self->{cgi}->param('Table2') : ''),
	command=>$command,
	where=>\%where,
	not_where=>\%not_where,
	sort_by=>\@sort_by,
	sort_reversed=>\%sort_reverse,
	show=>\@columns,
	headers=>\@headers,
	limit=>$limit,
	page=>$page,
	report_style=>($self->{cgi}->param('ReportStyle')
	    ? $self->{cgi}->param('ReportStyle') : 'compact'),
	layout=>($self->{cgi}->param('ReportLayout')
		 ? $self->{cgi}->param('ReportLayout') : 'table'),
	outfile=>$args{outfile},
    );

} # do_select

=head2 do_single_update

Update a single column in a single row, or all columns
in a single row.

=cut
sub do_single_update {
    my $self = shift;
    my $table = shift;
    my %args = (
	command=>'Update',
	@_
    );

    my $row_id_name = $self->get_id_colname($table);
    my $row_id = $self->{cgi}->param($row_id_name);
    if (!$row_id)
    {
	$self->print_message("Can't update table $table: row-id $row_id_name is NULL");
	return 0;
    }
    my $update_field = $self->{cgi}->param('Update');
    my %update_values = ();
    if ($update_field eq $row_id_name)
    {
	my @columns = $self->get_colnames($table, do_rowid=>0);
	foreach my $col (@columns)
	{
	    if ($col ne $row_id_name)
	    {
		$update_values{$col} = $self->{cgi}->param($col);
		$update_values{$col} =~ s/\r//g;
	    }
	}
    }
    else # update a single value
    {
	$update_values{$update_field} = $self->{cgi}->param($update_field);
	$update_values{$update_field} =~ s/\r//g;
    }
    if ($self->update_one_row(table=>$table,
			      command=>$args{command},
			      row_id=>$row_id,
			      field=>$update_field,
			      update_values=>\%update_values))
    {
	# display the edit fields again
	$self->{cgi}->param(-name=>"Zwhere_$row_id_name", -value=>$row_id);
	$self->do_select($table, 'Edit');
    }

} # do_single_update

=head2 do_add_form

$obj->do_add_form($table);

Set up for adding a row to the database.

=cut
sub do_add_form {
    my $self = shift;
    my $table = shift;
    my $command = 'Add';

    # read the template
    my $template;
    if ($self->{report_template} !~ /\n/
	&& -r $self->{report_template})
    {
	local $/ = undef;
	my $fh;
	open($fh, $self->{report_template})
	    or die "Could not open ", $self->{report_template};
	$template = <$fh>;
	close($fh);
    }
    else
    {
	$template = $self->{report_template};
    }
    # generate the form
    my $form = $self->make_add_form($table);
    my $title = $command . ' ' . $table;

    # Now print the page for the user to see...
    my $out = $template;
    $out =~ s//$title/g;
    $out =~ s//$form/g;

    print "Content-Type: text/html\n";
    print "\n";
    print $out;

} # do_add_form

=head2 do_add

Add a row to a table.

=cut
sub do_add {
    my $self = shift;
    my $table = shift;
    my %args = (
	command=>'Add',
	@_
    );

    my @columns = $self->get_colnames($table, do_rowid=>0);
    my $row_id_name = $self->get_id_colname($table);

    my %vals = ();
    foreach my $col (@columns)
    {
	$vals{$col} = $self->{cgi}->param($col);
	$vals{$col} =~ s/\r//g;
    }
    if ($self->add_one_row(
	table=>$table,
	add_values=>\%vals))
    {
	# display the edit fields again
	my $row_id = $self->{dbh}->last_insert_id(undef, undef, $table, undef);
	$self->{cgi}->param(-name=>"Zwhere_$row_id_name", -value=>$row_id);
	$self->do_select($table, 'Edit');
    }

} # do_add

=head2 do_single_delete

Delete a single row.

=cut
sub do_single_delete {
    my $self = shift;
    my $table = shift;
    my %args = (
	command=>'Delete',
	@_
    );

    my $row_id_name = $self->get_id_colname($table);
    my $row_id = $self->{cgi}->param($row_id_name);

    # delete the row given in the Delete_Row value
    my $pval = $self->{cgi}->param('Delete_Row');
    if ($pval)
    {
	$pval =~ m#Delete Row ([\d]+)#;
	$row_id = $1;
    }
    if (!$row_id)
    {
	$self->print_message("Can't delete from table $table: row-id $row_id_name is NULL");
	return 0;
    }
    if ($self->delete_one_row(
	table=>$table, row_id=>$row_id))
    {
	# display the edit search
	$self->do_search_form($table, command=>'Edit');
    }

} # do_single_delete

=head2 make_search_form

Create the search form for the given table.

my $form = $obj->make_search_form($table, %args);

=cut
sub make_search_form {
    my $self = shift;
    my $table = shift;
    my %args = (
	command=>'Search',
	@_
    );

    my $table2 = $self->{cgi}->param('Table2');

    # read the template
    my $template;
    if ($self->{report_template} !~ /\n/
	&& -r $self->{report_template})
    {
	local $/ = undef;
	my $fh;
	open($fh, $self->{report_template})
	    or die "Could not open ", $self->{report_template};
	$template = <$fh>;
	close($fh);
    }
    else
    {
	$template = $self->{report_template};
    }
    # generate the search form
    my $form = $self->search_form($table,
	command=>$args{command},
	table2=>$table2);
    my $title = $args{command} . ' ' . $table;

    $form = "$self->{message}\n" . $form if $self->{message};

    my $out = $template;
    $out =~ s//$title/g;
    $out =~ s//$form/g;
    return $out;

} # make_search_form

=head2 do_search_form

Display the search form for the given table.

=cut
sub do_search_form {
    my $self = shift;

    my $out = $self->make_search_form(@_);

    # Now print the page for the user to see...
    print "Content-Type: text/html\n";
    print "\n";
    print $out;

} # do_search_form

=head2 make_table_form

Make the table selection form.

=cut
sub make_table_form {
    my $self = shift;
    my $command = (@_ ? shift : '');

    # read the template
    my $template;
    if ($self->{report_template} !~ /\n/
	&& -r $self->{report_template})
    {
	local $/ = undef;
	my $fh;
	open($fh, $self->{report_template})
	    or die "Could not open ", $self->{report_template};
	$template = <$fh>;
	close($fh);
    }
    else
    {
	$template = $self->{report_template};
    }

    # get the list of tables (and views)
    my @tables = sort $self->get_tables(views=>($command ne 'Editing'));

    # generate the search form
    my $url = $self->{cgi}->url();
    my $form =<

Table:
EOT
    foreach my $table (@tables)
    {
	$form .= "$table";
    }
    $form .=<




EOT
    my $title = "Select table";
    $title .= " for $command" if $command;

    my $out = $template;
    $out =~ s//$title/g;
    $out =~ s//$form/g;

    return $out;

} # make_table_form

=head2 do_table_form

Display the table selection form.

=cut
sub do_table_form {
    my $self = shift;

    my $out = $self->make_table_form(@_);

    # Now print the page for the user to see...
    print "Content-Type: text/html\n";
    print "\n";
    print $out;

} # do_table_form

=head1 Helper Methods

Lower-level methods, generally just called from other methods,
but possibly suitable for other things.

=head2 print_message

Print an (error) message to the user.

$self->print_message($message); # error message

$self->print_message($message, 0); # non-error message

=cut
sub print_message {
    my $self = shift;
    my $message = shift;
    my $is_error = (@_ ? shift : 1); # assume error message

    # read the template
    my $template;
    if ($self->{report_template} !~ /\n/
	&& -r $self->{report_template})
    {
	local $/ = undef;
	my $fh;
	open($fh, $self->{report_template})
	    or die "Could not open ", $self->{report_template};
	$template = <$fh>;
	close($fh);
    }
    else
    {
	$template = $self->{report_template};
    }
    my $title = ($is_error
	? "Error Message"
	: "Message"
    );

    my $contents = ($is_error
	? "$message\n"
	: "$message\n"
    );

    my $out = $template;
    $out =~ s//$title/g;
    $out =~ s//$contents/g;
    # Now print the page for the user to see...
    print "Content-Type: text/html\n";
    print "\n";
    print $out;
} # print_message

=head2 search_form

Construct a search-a-table form

=cut
sub search_form {
    my $self = shift;
    my $table = shift;
    my %args = (
	command=>'Search',
	@_
    );

    my @columns = $self->get_colnames($table);
    my $command = $args{command};
    my $where_prefix = $self->{where_prefix};
    my $not_prefix = $self->{not_prefix};
    my $show_label = $self->{show_label};
    my $sort_label = $self->{sort_label};
    my $sort_reversed_prefix = $self->{sort_reversed_prefix};
    my $headers_label = $self->{headers_label};

    my $action = $self->{cgi}->url();
    my $out_str =<



EOT
    if ($command eq 'Edit')
    {
	$out_str .=<

EOT
    }
    $out_str .=<


 




693




 





 





 





 





 





 



  




694




 





 





 





 





 





 



 Match by column: use * as a wildcard match, 




695




 





 





 





 





 





 



 and the ? character to match 




696




 





 





 





 





 





 



 any single character. 




697




 





 





 





 





 





 



 Click on the "NOT" checkbox to negate a match. 




698




 





 





 





 





 





 



  




699




 





 





 





 





 





 



 \n\n"; \n";  




700




 





 





 





 





 





 



  




701




 





 





 





 





 





 



 Columns 




702




 





 





 





 





 





 



 Match 




703




 





 





 





 





 





 



   




704




 





 





 





 





 





 



  




705




 





 





 





 





 





 



 EOT 




706




0





 





 





 





 





 



     for (my $i = 0; $i < @columns; $i++) { 




707




0





 





 





 





 





 



 	my $col = $columns[$i]; 




708




0





 





 





 





 





 



 	my $wcol_label = "${where_prefix}${col}"; 




709




0





 





 





 





 





 



 	my $ncol_label = "${not_prefix}${col}"; 




710




 





 





 





 





 





 



   




711




0





 





 





 





 





 



 	$out_str .= ""; 




712




0





 





 





 





 





 



 	$out_str .= "$col"; 




713




0





 





 





 





 





 



 	$out_str .= ""; 




714




0





 





 





 





 





 



 	$out_str .= ""; 




715




0





 





 





 





 





 



 	$out_str .= ""; 




716




0





 





 





 





 





 



 	$out_str .= "NOT"; 




717




0





 





 





 





 





 



 	$out_str .= "




718




0





 





 





 





 





 



 	$out_str .= "




719




 





 





 





 





 





 



 } 




720




0





 





 





 





 





 



     $out_str .=<




721




 





 





 





 





 





 



  




722




 





 





 





 





 





 



  




723




 





 





 





 





 





 



 Select the order of columns to display; 




724




 





 





 





 





 





 



 and which columns not to display. 




725




 





 





 





 





 





 



 "; \n";  




726




 





 





 





 





 





 



 EOT 




727




0





 





 





 





 





 



     for (my $i = 0; $i < @columns; $i++) { 




728




0





 





 





 





 





 



 	my $col = $columns[$i]; 




729




 





 





 





 





 





 



   




730




0





 





 





 





 





 



 	$out_str .= ""; 




731




0





 





 





 





 





 



 	$out_str .= "\n"; 




732




0





 





 





 





 





 



 	$out_str .= "-- not displayed --\n"; 




733




0





 





 





 





 





 



 	foreach my $fname (@columns) 




734




 





 





 





 





 





 



 	{ 




735




0




  
  0
  




 





 





 





 



 	    if ($fname eq $col) 




736




 





 





 





 





 





 



 	    { 




737




0





 





 





 





 





 



 		$out_str .= "${fname}\n"; 




738




 





 





 





 





 





 



 	    } 




739




 





 





 





 





 





 



 	    else 




740




 





 





 





 





 





 



 	    { 




741




0





 





 





 





 





 



 		$out_str .= "${fname}\n"; 




742




 





 





 





 





 





 



 	    } 




743




 





 





 





 





 





 



 	} 




744




0





 





 





 





 





 



 	$out_str .= ""; 




745




0





 





 





 





 





 



 	$out_str .= "




746




0





 





 





 





 





 



 	$out_str .= "




747




 





 





 





 





 





 



 } 




748




0





 





 





 





 





 



     $out_str .=<




749




 





 





 





 





 





 



  




750




 





 





 





 





 





 



 EOT 




751




0





 





 





 





 





 



     $out_str .=<




752




 





 





 





 





 





 



 Num Results: 




753




 





 





 





 





 





 



 All 




754




 





 





 





 





 





 



 1 




755




 





 





 





 





 





 



 10 




756




 





 





 





 





 





 



 20 




757




 





 





 





 





 





 



 50 




758




 





 





 





 





 





 



 100 




759




 





 





 





 





 





 



  




760




 





 





 





 





 





 



  




761




 





 





 





 





 





 



 Page: 




762




 





 





 





 





 





 



  




763




 





 





 





 





 





 



  




764




 





 





 





 





 





 



 EOT 




765




0




  
  0
  




 





 





 





 



     if ($command eq 'Search') 




766




 





 





 





 





 





 



     { 




767




0





 





 





 





 





 



 	$out_str .=<




768




 





 





 





 





 





 



 Report Layout: 




769




 





 





 





 





 





 



 table 




770




 





 





 





 





 





 



 paragraph 




771




 





 





 





 





 





 



 list 




772




 





 





 





 





 





 



  




773




 





 





 





 





 





 



  




774




 





 





 





 





 





 



 EOT 




775




 





 





 





 





 





 



     } 




776




 





 





 





 





 





 



   




777




0





 





 





 





 





 



     $out_str .=<




778




 





 





 





 





 





 



 Report Style: 




779




 





 





 





 





 





 



 Full 




780




 





 





 





 





 





 



 Medium 




781




 





 





 





 





 





 



 Compact 




782




 





 





 





 





 





 



 Bare 




783




 





 





 





 





 





 



  




784




 





 





 





 





 





 



  




785




 





 





 





 





 





 



 EOT 




786




0





 





 





 





 





 



     my @tables = $self->get_tables(); 




787




0




  
  0
  




 





 





 





 



     if (@tables > 1) 




788




 





 





 





 





 





 



     { 




789




0





 





 





 





 





 



 	$out_str .=<




790




 





 





 





 





 





 



 Table #2 




791




 





 





 





 





 





 



 NONE 




792




 





 





 





 





 





 



 EOT 




793




0





 





 





 





 





 



 	foreach my $tn (@tables) 




794




 





 





 





 





 





 



 	{ 




795




0




  
  0
  




 





 





 





 



 	    if ($tn ne $table) 




796




 





 





 





 





 





 



 	    { 




797




0





 





 





 





 





 



 		$out_str .= "$tn\n"; 




798




 





 





 





 





 





 



 	    } 




799




 





 





 





 





 





 



 	} 




800




0





 





 





 





 





 



 	$out_str .= "\n"; 




801




 





 





 





 





 





 



     } 




802




 





 





 





 





 





 



   




803




0





 





 





 





 





 



     $out_str .=<




804

Match by column: use * as a wildcard match,
and the ? character to match
any single character.
Click on the "NOT" checkbox to negate a match.

\n\n"; \n";  




700




 





 





 





 





 





 



  




701




 





 





 





 





 





 



 Columns 




702




 





 





 





 





 





 



 Match 




703




 





 





 





 





 





 



   




704




 





 





 





 





 





 



  




705




 





 





 





 





 





 



 EOT 




706




0





 





 





 





 





 



     for (my $i = 0; $i < @columns; $i++) { 




707




0





 





 





 





 





 



 	my $col = $columns[$i]; 




708




0





 





 





 





 





 



 	my $wcol_label = "${where_prefix}${col}"; 




709




0





 





 





 





 





 



 	my $ncol_label = "${not_prefix}${col}"; 




710




 





 





 





 





 





 



   




711




0





 





 





 





 





 



 	$out_str .= ""; 




712




0





 





 





 





 





 



 	$out_str .= "$col"; 




713




0





 





 





 





 





 



 	$out_str .= ""; 




714




0





 





 





 





 





 



 	$out_str .= ""; 




715




0





 





 





 





 





 



 	$out_str .= ""; 




716




0





 





 





 





 





 



 	$out_str .= "NOT"; 




717




0





 





 





 





 





 



 	$out_str .= "




718




0





 





 





 





 





 



 	$out_str .= "




719




 





 





 





 





 





 



 } 




720




0





 





 





 





 





 



     $out_str .=<




721





EOT
    for (my $i = 0; $i < @columns; $i++) {
	my $col = $columns[$i];
	my $wcol_label = "${where_prefix}${col}";
	my $ncol_label = "${not_prefix}${col}";

	$out_str .= "
	$out_str .= "$col";
	$out_str .= "
	$out_str .= "";
	$out_str .= "
	$out_str .= "NOT";
	$out_str .= "
	$out_str .= "
}
    $out_str .=<


Select the order of columns to display;
and which columns not to display.
"; \n";  




726




 





 





 





 





 





 



 EOT 




727




0





 





 





 





 





 



     for (my $i = 0; $i < @columns; $i++) { 




728




0





 





 





 





 





 



 	my $col = $columns[$i]; 




729




 





 





 





 





 





 



   




730




0





 





 





 





 





 



 	$out_str .= ""; 




731




0





 





 





 





 





 



 	$out_str .= "\n"; 




732




0





 





 





 





 





 



 	$out_str .= "-- not displayed --\n"; 




733




0





 





 





 





 





 



 	foreach my $fname (@columns) 




734




 





 





 





 





 





 



 	{ 




735




0




  
  0
  




 





 





 





 



 	    if ($fname eq $col) 




736




 





 





 





 





 





 



 	    { 




737




0





 





 





 





 





 



 		$out_str .= "${fname}\n"; 




738




 





 





 





 





 





 



 	    } 




739




 





 





 





 





 





 



 	    else 




740




 





 





 





 





 





 



 	    { 




741




0





 





 





 





 





 



 		$out_str .= "${fname}\n"; 




742




 





 





 





 





 





 



 	    } 




743




 





 





 





 





 





 



 	} 




744




0





 





 





 





 





 



 	$out_str .= ""; 




745




0





 





 





 





 





 



 	$out_str .= "




746




0





 





 





 





 





 



 	$out_str .= "




747




 





 





 





 





 





 



 } 




748




0





 





 





 





 





 



     $out_str .=<




749
EOT
    for (my $i = 0; $i < @columns; $i++) {
	my $col = $columns[$i];

	$out_str .= "
	$out_str .= "\n";
	$out_str .= "-- not displayed --\n";
	foreach my $fname (@columns)
	{
	    if ($fname eq $col)
	    {
		$out_str .= "${fname}\n";
	    }
	    else
	    {
		$out_str .= "${fname}\n";
	    }
	}
	$out_str .= "";
	$out_str .= "
	$out_str .= "
}
    $out_str .=<

EOT
    $out_str .=<
Num Results:
All
1
10
20
50
100


Page:


EOT
    if ($command eq 'Search')
    {
	$out_str .=<
Report Layout:
table
paragraph
list


EOT
    }

    $out_str .=<
Report Style:
Full
Medium
Compact
Bare


EOT
    my @tables = $self->get_tables();
    if (@tables > 1)
    {
	$out_str .=<
Table #2
NONE
EOT
	foreach my $tn (@tables)
	{
	    if ($tn ne $table)
	    {
		$out_str .= "$tn\n";
	    }
	}
	$out_str .= "\n";
    }

    $out_str .=<

 




806




 





 





 





 





 





 



  




807




 





 





 





 





 





 



 Sort by: To set the sort order, select the column names. 




808




 





 





 





 





 





 



 To sort that column in reverse order, click on the Reverse 




809




 





 





 





 





 





 



 checkbox. 




810




 





 





 





 





 





 



  




811




 





 





 





 





 





 



 "; \n"; ";  




812




 





 





 





 





 





 



 EOT 




813




 





 





 





 





 





 



   




814




0




  
  0
  




 





 





 





 



     my $num_sort_fields = ($self->{max_sort_fields} < @columns 




815




 





 





 





 





 





 



 	? $self->{max_sort_fields} : @columns); 




816




0





 





 





 





 





 



     for (my $i=0; $i < $num_sort_fields; $i++) 




817




 





 





 





 





 





 



     { 




818




0





 





 





 





 





 



 	my $col = $columns[$i]; 




819




0





 





 





 





 





 



 	$out_str .= ""; 




820




0





 





 





 





 





 



 	$out_str .= "\n"; 




821




0





 





 





 





 





 



 	$out_str .= "--choose a sort column--\n"; 




822




0





 





 





 





 





 



 	foreach my $fname (@columns) 




823




 





 





 





 





 





 



 	{ 




824




0





 





 





 





 





 



 	    $out_str .= "${fname}\n"; 




825




 





 





 





 





 





 



 	} 




826




0





 





 





 





 





 



 	$out_str .= ""; 




827




0





 





 





 





 





 



 	$out_str .= "




828




0





 





 





 





 





 



 	$out_str .= "Reverse "; 




829




0





 





 





 





 





 



 	$out_str .= "




830




0





 





 





 





 





 



 	$out_str .= "




831




 





 





 





 





 





 



     } 




832




0





 





 





 





 





 



     $out_str .=<




833




 





 





 





 





 





 



  




834




 





 





 





 





 





 



  




835




 





 





 





 





 





 



 EOT 




836




0




  
  0
  




 





 





 





 



     if ($command eq 'Search') 




837




 





 





 





 





 





 



     { 




838




0





 





 





 





 





 



 	$out_str .=<




839




 





 





 





 





 





 



 Headers: 




840




 





 





 





 





 





 



 Indicate which columns you wish to be in headers by giving 




841




 





 





 





 





 





 



 the columns in template form; for example: 




842




 





 





 





 





 





 



 {\$Col1} {\$Col2} 




843




 





 





 





 





 





 



 means that the header contains columns Col1 and Col2. 




844




 





 





 





 





 





 



  




845




 





 





 





 





 





 



 EOT 




846




0





 





 





 





 





 



 	for (my $i=1; $i <= $self->{max_headers}; $i++) 




847




 





 





 





 





 





 



 	{ 




848




0





 





 





 





 





 



 	    $out_str .=<




849




 





 





 





 





 





 



 Header $i 




850




 





 





 





 





 





 



  




851




 





 





 





 





 





 



 EOT 




852




 





 





 





 





 





 



 	} 




853




0





 





 





 





 





 



 	$out_str .= "\n"; 




854




 





 





 





 





 





 



     } 




855




 





 





 





 





 





 



   




856




0





 





 





 





 





 



     $out_str .=<




857




 





 





 





 





 





 



  




858

Sort by: To set the sort order, select the column names.
To sort that column in reverse order, click on the Reverse
checkbox.

"; \n"; ";  




812




 





 





 





 





 





 



 EOT 




813




 





 





 





 





 





 



   




814




0




  
  0
  




 





 





 





 



     my $num_sort_fields = ($self->{max_sort_fields} < @columns 




815




 





 





 





 





 





 



 	? $self->{max_sort_fields} : @columns); 




816




0





 





 





 





 





 



     for (my $i=0; $i < $num_sort_fields; $i++) 




817




 





 





 





 





 





 



     { 




818




0





 





 





 





 





 



 	my $col = $columns[$i]; 




819




0





 





 





 





 





 



 	$out_str .= ""; 




820




0





 





 





 





 





 



 	$out_str .= "\n"; 




821




0





 





 





 





 





 



 	$out_str .= "--choose a sort column--\n"; 




822




0





 





 





 





 





 



 	foreach my $fname (@columns) 




823




 





 





 





 





 





 



 	{ 




824




0





 





 





 





 





 



 	    $out_str .= "${fname}\n"; 




825




 





 





 





 





 





 



 	} 




826




0





 





 





 





 





 



 	$out_str .= ""; 




827




0





 





 





 





 





 



 	$out_str .= "




828




0





 





 





 





 





 



 	$out_str .= "Reverse "; 




829




0





 





 





 





 





 



 	$out_str .= "




830




0





 





 





 





 





 



 	$out_str .= "




831




 





 





 





 





 





 



     } 




832




0





 





 





 





 





 



     $out_str .=<




833
EOT

    my $num_sort_fields = ($self->{max_sort_fields} < @columns
	? $self->{max_sort_fields} : @columns);
    for (my $i=0; $i < $num_sort_fields; $i++)
    {
	my $col = $columns[$i];
	$out_str .= "
	$out_str .= "\n";
	$out_str .= "--choose a sort column--\n";
	foreach my $fname (@columns)
	{
	    $out_str .= "${fname}\n";
	}
	$out_str .= "";
	$out_str .= "
	$out_str .= "
	$out_str .= "
	$out_str .= "
    }
    $out_str .=<


EOT
    if ($command eq 'Search')
    {
	$out_str .=<
Headers:
Indicate which columns you wish to be in headers by giving
the columns in template form; for example:
{\$Col1} {\$Col2}
means that the header contains columns Col1 and Col2.

EOT
	for (my $i=1; $i <= $self->{max_headers}; $i++)
	{
	    $out_str .=<
Header $i

EOT
	}
	$out_str .= "\n";
    }

    $out_str .=<



EOT
    if ($command eq 'Edit')
    {
	$out_str .=<

EOT
    }
    $out_str .=<


EOT
    return $out_str;
} # search_form

=head2 make_add_form

Construct an add-a-row form.

=cut
sub make_add_form {
    my $self = shift;
    my $table = shift;
    my %args = (
	command=>'Add',
	@_
    );

    my $row_id_name = $self->get_id_colname($table);
    my @columns = $self->get_colnames($table, do_rowid=>0);
    my $command = $args{command};
    my $table2_str = ($args{table2}
	?  ""
	: ''
    );

    my $action = $self->{cgi}->url();
    my $out_str =<




$table2_str

\n"; \n";  




904




 





 





 





 





 





 



  




905




 





 





 





 





 





 



 Columns 




906




 





 





 





 





 





 



 Value 




907




 





 





 





 





 





 



  




908




 





 





 





 





 





 



 EOT 




909




0





 





 





 





 





 



     for (my $i = 0; $i < @columns; $i++) { 




910




0





 





 





 





 





 



 	my $col = $columns[$i]; 




911




 





 





 





 





 





 



   




912




0





 





 





 





 





 



 	$out_str .= ""; 




913




0





 





 





 





 





 



 	$out_str .= "$col"; 




914




0





 





 





 





 





 



 	$out_str .= ""; 




915




0




  
  0
  




 





 





 





 



 	if ($col eq $row_id_name) 




916




 





 





 





 





 





 



 	{ 




917




0





 





 





 





 





 



 	    $out_str .= ""; 




918




 





 





 





 





 





 



 	} 




919




 





 





 





 





 





 



 	else 




920




 





 





 





 





 





 



 	{ 




921




0





 





 





 





 





 



 	    $out_str .= $self->get_input_field(table=>$table, 




922




 





 





 





 





 





 



 		colname=>$col, value=>''); 




923




 





 





 





 





 





 



 	} 




924




0





 





 





 





 





 



 	$out_str .= "




925




0





 





 





 





 





 



 	$out_str .= "




926




 





 





 





 





 





 



 } 




927




0





 





 





 





 





 



     $out_str .=<




928




EOT
    for (my $i = 0; $i < @columns; $i++) {
	my $col = $columns[$i];

	$out_str .= "
	$out_str .= "$col";
	$out_str .= "
	if ($col eq $row_id_name)
	{
	    $out_str .= "";
	}
	else
	{
	    $out_str .= $self->get_input_field(table=>$table,
		colname=>$col, value=>'');
	}
	$out_str .= "
	$out_str .= "
}
    $out_str .=<


EOT
    return $out_str;
} # make_add_form

=head2 make_buttons

Make the buttons for the forms.

=cut
sub make_buttons {
    my $self = shift;
    my %args = (
	table=>'',
	command=>'Search',
	@_
    );
    my $table = $args{table};
    my $table2 = $args{table2};
    my $page = $args{page};
    my $limit = $args{limit};
    my $total = $args{total};
    my $command = $args{command};

    my $num_pages = ($limit ? ceil($total / $limit) : 0);

    my $url = $self->{cgi}->url();
    my @out = ();
    push @out,<
\n"; \n"; \n"; \n";  




959




 





 





 





 





 





 



  




960




 





 





 





 





 





 



  




961




 





 





 





 





 





 



  




962




 





 





 





 





 





 



  




963




 





 





 





 





 





 



  




964




 





 





 





 





 





 



 EOT 




965




0




  
  0
  




 





 





 





 



     if ($command eq 'Edit') 




966




 





 





 





 





 





 



     { 




967




0





 





 





 





 





 



 	push @out,<




968




 





 





 





 





 





 



  




969




 





 





 





 





 





 



 EOT 




970




 





 





 





 





 





 



     } 




971




0





 





 





 





 





 



     push @out,<




972




 





 





 





 





 





 



  




973




 





 





 





 





 





 



 EOT 




974




 





 





 





 





 





 



   




975




0




  
  0
  




 





 





 





 



     if ($args{limit}) 




976




 





 





 





 





 





 



     { 




977




 





 





 





 





 





 



 	# reproduce the query ops, with a different page 




978




 





 





 





 





 





 



 	# first 




979




0





 





 





 





 





 



 	push @out, ""; 




980




0





 





 





 





 





 



 	push @out, $self->make_page_button(command=>$command, 




981




 





 





 





 





 





 



 	    the_page=>1, 




982




 





 





 





 





 





 



 	    page_label=>' |< '); 




983




0





 





 





 





 





 



 	push @out, "




984




 





 





 





 





 





 



 	# prev 




985




0





 





 





 





 





 



 	push @out, ""; 




986




0





 





 





 





 





 



 	push @out, $self->make_page_button(command=>$command, 




987




 





 





 





 





 





 



 	    the_page=>$page - 1, 




988




 





 





 





 





 





 



 	    page_label=>' < '); 




989




0





 





 





 





 





 



 	push @out, "




990




 





 





 





 





 





 



 	# next 




991




0





 





 





 





 





 



 	push @out, ""; 




992




0





 





 





 





 





 



 	push @out, $self->make_page_button(command=>$command, 




993




 





 





 





 





 





 



 	    the_page=>$page + 1, 




994




 





 





 





 





 





 



 	    page_label=>' > '); 




995




0





 





 





 





 





 



 	push @out, "




996




 





 





 





 





 





 



 	# last 




997




0





 





 





 





 





 



 	push @out, ""; 




998




0





 





 





 





 





 



 	push @out, $self->make_page_button(command=>$command, 




999




 





 





 





 





 





 



 	    the_page=>$num_pages, 




1000




 





 





 





 





 





 



 	    page_label=>' >| '); 




1001




0





 





 





 





 





 



 	push @out, "




1002




0





 





 





 





 





 



 	push @out, "\n";





EOT
    if ($command eq 'Edit')
    {
	push @out,<

EOT
    }
    push @out,<

EOT

    if ($args{limit})
    {
	# reproduce the query ops, with a different page
	# first
	push @out, "
	push @out, $self->make_page_button(command=>$command,
	    the_page=>1,
	    page_label=>' |< ');
	push @out, "
	# prev
	push @out, "
	push @out, $self->make_page_button(command=>$command,
	    the_page=>$page - 1,
	    page_label=>' < ');
	push @out, "
	# next
	push @out, "
	push @out, $self->make_page_button(command=>$command,
	    the_page=>$page + 1,
	    page_label=>' > ');
	push @out, "
	# last
	push @out, "
	push @out, $self->make_page_button(command=>$command,
	    the_page=>$num_pages,
	    page_label=>' >| ');
	push @out, "
	push @out, "
    }
    else # no pages
    {
	push @out,<