File Coverage

blib/lib/HTML/EP/EditTable.pm

Criterion	Covered	Total	%
statement	6	113	5.3
branch	0	64	0.0
condition	0	50	0.0
subroutine	2	5	40.0
pod			n/a
total	8	232	3.4

# -*- perl -*-

use strict;

use HTML::EP ();


package HTML::EP::EditTable;

@HTML::EP::EditTable::ISA = qw(HTML::EP);



sub _ep_edittable_edit {
    my $self = shift;  my $attr = shift;
    my $cgi = $self->{'cgi'};
    my $action = $cgi->param('what-to-do');
    my $table = $attr->{'table'}
        || die "Missing attribute: table (Table name)";
    my $id_col = $attr->{'id'} || 'ID';
    my $result;
    my($query, $id);

    my $dest = ($attr->{'dest'} ||= $table);
    $attr->{'prefix'} ||= "$dest\_";
    $attr->{'sqlquery'} ||= 1;

    if ($action eq 'insert') {
	$self->_ep_input($attr);
	$result = $self->{$dest};
	$query = "INSERT INTO $table (" . $result->{'names'}. ") VALUES"
	    . " (" . $result->{'values'}. ")";
    } elsif ($action eq 'update') {
	$self->_ep_input($attr);
	$result = $self->{$dest};
	$id = $cgi->param($id_col);
	$query = "UPDATE $table SET " . $result->{'update'} .
	    " WHERE $id_col = $id";
    } elsif ($id = $cgi->param($id_col)) {
	my $q = "SELECT * FROM $table WHERE $id_col = $id";
	print "Select query: $q\n" if $self->{'debug'};
	my $sth = $self->{'dbh'}->prepare($q);
	$sth->execute();
	my $names = $sth->{'NAME'};
	my $types = $sth->{'TYPE'};
	my $row = $sth->fetchrow_arrayref()
	    or die "Failed to fetch row with ID $id: No such row";
	$sth->finish();
	my %result;
	for (my $i = 0;  $i < @$row;  $i++) {
	    my $type = $types->[$i];
	    my $name = $names->[$i];
	    my $ref = { 'col' => $name,
			'val' => $row->[$i] };
	    if ($type == DBI::SQL_DATE()) {
		$ref->{'type'} = 'd';
		if (!defined($row->[$i])) {
		    $ref->{'day'} = $ref->{'month'} = $ref->{'year'} = '';
		} elsif ($row->[$i] =~ /^(\d\d\d\d)\-(\d\d)\-(\d\d)/) {
		    $ref->{'day'} = $3;
		    $ref->{'month'} = $2;
		    $ref->{'year'} = $1;
		} else {
		    die "Cannot parse date: $row->[$i]";
		}
	    } elsif ($type == DBI::SQL_NUMERIC()   ||
		     $type == DBI::SQL_DECIMAL()   ||
		     $type == DBI::SQL_INTEGER()   ||
		     $type == DBI::SQL_SMALLINT()  ||
		     $type == DBI::SQL_FLOAT()     ||
		     $type == DBI::SQL_REAL()      ||
		     $type == DBI::SQL_DOUBLE()    ||
		     $type == DBI::SQL_BIGINT()    ||
		     $type == DBI::SQL_TINYINT()) {
		$ref->{'type'} = 'n';
	    } else {
		$ref->{'type'} = 't';
	    }
	    $result->{$name} = $ref;
	}
	$self->{$dest} = $result;
	if ($action eq 'delete') {
	    $query = "DELETE FROM $table WHERE $id_col = $id";
	}
    }
    if ($query) {
	print "Executing query: $query\n" if $self->{'debug'};
	my $dbh = $self->{'dbh'};
	$dbh->do($query);
	if (!defined($id)  &&  $dbh->{'Driver'}->{'Name'} eq 'mysql') {
	    $id = $dbh->{'mysql_insertid'};
	    print "Auto-ID is $id.\n" if $self->{'debug'};
	    $cgi->param($id_col, $id);
	}
    }
    '';
}


sub _ep_edittable_select {
    my $self = shift; my $attr = shift;
    my $table = $attr->{'table'}
        || die "Missing attribute: table (Table name)";
    my $dbh = $self->{'dbh'};
    my $cgi = $self->{'cgi'};
    my $debug = $self->{'debug'};

    my(@where, @order, @url);
    foreach my $key ($cgi->param()) {
	if ($key =~ /^select_(\w+)_(.*)/) {
	    my $col = $2;
	    my $type = $1;
	    my $val = $cgi->param($col);
	    if ($type eq 'like') {
		push(@where, "$col LIKE " . $dbh->quote("%$val%")) if $val;
	    } elsif ($type eq 'like_') {
		push(@where, "$col LIKE " . $dbh->quote("$val%")) if $val;
	    } elsif ($type eq '_like') {
		push(@where, "$col LIKE " . $dbh->quote("%$val")) if $val;
	    }
	    push(@url, "$key=" . CGI->escape($val));
	} elsif ($key =~ /^order_(\w+)_(.*)/) {
	    push(@order, "$2 $1");
	    my $val = $cgi->param($2);
	    push(@url, "$key=" . CGI->escape($val));
	}
    }

    my $start = $cgi->param('start') || 0;
    my $max = $cgi->param('max') || $attr->{'max'} || 20;
    my $count_query = "SELECT COUNT(*) FROM $table"
	. (@where ? " WHERE " . join(" AND ", @where) : "");
    print "Count query is: $count_query\n" if $debug;
    my $query = "SELECT * FROM $table"
	. (@where ? " WHERE " . join(" AND ", @where) : "")
	. (@order ? " ORDER BY " . join(", ", @order) : "")
	. " LIMIT $start, $max";
    print "Query is: $query\n" if $debug;

    $self->{'start'} = $start;
    $self->{'max'} = $max;
    $self->{'query_url'} = @url ? ("&" . join("&", @url)) : "";
    my $sth = $dbh->prepare($count_query);
    $sth->execute();
    $self->{'num_rows'} = $sth->fetchrow_array(); # Array context!
    $self->_ep_query({'statement' => $query,
		      'result' => $table});
    '';
}


sub _ep_edittable_links {
    my $self = shift;  my $attr = shift;
    my $max = $self->{'max'};
    my $start = $self->{'start'};
    my $page = $attr->{'path'} || $ENV{'PATH_INFO'};
    my $max_links = $attr->{'max_links'} || 10;
    $self->{'prev'} = $start ?
	"{'query_url'} .
        ">Zurück" : "";
    my $links = '';
    $self->{'next'} = ($self->{'num_rows'} > $start + $max) ?
	"{'query_url'} .
        ">Weiter" : "";
    my $base = $max * $max_links;
    my $first = int(($start + $base - 1) / $base);
    for (my $i = 0;  $i < $max_links;  $i++) {
	my $num = $first + $i;
	if ($self->{'num_rows'} > $num * $max) {
	    if ($num * $max == $start) {
		$links .= $num+1;
	    } else {
		$links .= "
		    $self->{'query_url'} . ">" . ($num+1) . "";
	    }
	}
    }
    $self->{'prev'} . $links . $self->{'next'};
}


1;


__END__