File Coverage

blib/lib/CGI/Lazy/DB/RecordSet.pm

Criterion	Covered	Total	%
statement	9	408	2.2
branch	0	214	0.0
condition	0	21	0.0
subroutine	3	45	6.6
pod	38	42	90.4
total	50	730	6.8

package CGI::Lazy::DB::RecordSet;

use strict;

use Tie::IxHash;
use Data::Dumper;

#------------------------------------------------------------------
sub basewhere {
	my $self = shift;

	return $self->{_basewhere};
}

#------------------------------------------------------------------------------
sub checkboxes {
	my $self = shift;

	return $self->{_checkboxes};
}

#--------------------------------------------------------------------
sub createSelect {
	my $self = shift;

	my $joinstring = '';
	my $orderbystring = $self->orderby ? ' order by '.$self->orderby : '';

	my $wherestring;
	my @binds;

	if (ref $self->where) {
		my @wherelist = @{$self->where};
		my $where = shift @wherelist;
		@binds = @wherelist;

		if ($self->basewhere) {
			$wherestring = $self->where ? ' where '.$self->basewhere. ' and '.$where : ' where '.$self->basewhere;
		} else {
			$wherestring = $self->where ? ' where '.$where : '';
		}

	} else {
		if ($self->basewhere) {
			$wherestring = $self->where ? ' where '.$self->basewhere. ' and '.$self->where : ' where '.$self->basewhere;
		} else {
			$wherestring = $self->where ? ' where '.$self->where : '';
		}
	}

	my @fieldlist;

	foreach my $field (keys %{$self->fieldlist}) {
		unless ($self->displayOnly($field)) {
			if ($self->readfunc($field)) {
				push @fieldlist, $self->readfunc($field);
			} elsif ($self->passwd($field)) {
				next;
			} else {
				push @fieldlist, $field;
			}
		}
	}

	if ($self->joins) {
		foreach (@{$self->joins}) {
			$joinstring .= " " if $joinstring;
			my $type 		= $_->{type};
			my $table 		= $_->{table};
			my $field1		= $_->{field1};
			my $field2 		= $_->{field2};
			my $and			= $_->{and};

			$joinstring .= " $type join $table on $field1 = $field2 ";
			$joinstring .= " and $and" if $and;
		}
	}

	return "select ". join (', ', @fieldlist)." from ".$self->table.$joinstring.$wherestring.$orderbystring, @binds;
}

#------------------------------------------------------------------
sub data {
	my $self = shift;
	return $self->{_data};
}

#------------------------------------------------------------------
sub db {
	my $self = shift;

	return $self->{_db};
}

#------------------------------------------------------------------------------
sub delete {
	my $self = shift;
	my $data = shift;

	my $table = $self->table;
	my $primarykey = $self->primarykey;

	foreach my $ID (keys %$data) {
		my $query = "delete from $table where $primarykey = ?";

		${$self->primarykeyhandle} = $ID;
#		$self->q->util->debug->edump($query.", $ID");
		$self->db->do($query, $ID);

	}
}

#------------------------------------------------------------------------------
sub displayOnly {
	my $self = shift;
	my $field = shift;

	if (exists $self->fieldlist->{$field}) {
		if ($self->fieldlist->{$field}->{displayOnly}) {
			return $self->fieldlist->{$field}->{displayOnly};
		} elsif ($self->fieldlist->{$field}->{displayonly}) {
			return $self->fieldlist->{$field}->{displayonly};
		}
	} else {
		return;
	}
}

#------------------------------------------------------------------------------
sub fieldlist {
	my $self = shift;

	return $self->{_fieldlist};
}

#------------------------------------------------------------------
sub handle {
	my $self = shift;
	my $field = shift;

	if (exists $self->fieldlist->{$field}) {
		return $self->fieldlist->{$field}->{handle};
	} else {
		return;
	}
}

#------------------------------------------------------------------------------
sub hidden {
	my $self = shift;
	my $field = shift;

	if (exists $self->fieldlist->{$field}) {
		return $self->fieldlist->{$field}->{hidden};
	} else {
		return;
	}
}

#-------------------------------------------------------------------------------
sub inputMask {
	my $self = shift;
	my $field = shift;

	if (exists $self->fieldlist->{$field}) {
		if ($self->fieldlist->{$field}->{inputMask}) {
			return $self->fieldlist->{$field}->{inputMask};
		} elsif ($self->fieldlist->{$field}->{inputmask}) {
			return $self->fieldlist->{$field}->{inputmask};
		}
	} else {
		return;
	}
}

#------------------------------------------------------------------------------
sub insert {
	my $self = shift;
	my $data = shift;
	my $vars = shift;

	my $table = $self->table;
	my $primarykey = $self->primarykey;
	my $defaults = $self->insertdefaults;
	my $additional = $self->insertadditional;

	foreach my $row (keys %$data) {
		my @fieldlist;
		my @binds;
		my @bindvalues;

		if (%$vars) {
			foreach (keys %$vars) {
				if ($vars->{$_}->{value}) {
					$data->{$row}->{$_} = ref $vars->{$_}->{value} ? ${$vars->{$_}->{value}} : $vars->{$_}->{value};
#					$self->q->util->debug->edump("var: ".$vars->{$_}->{value}." -- ".${$vars->{$_}->{value}});
				}
			}
		}

		if ($defaults) {
			foreach my $field (keys %$defaults) {
				if ($defaults->{$field}->{value}) { #static quanities
					$data->{$row}->{$field} = $defaults->{$field}->{value};
					if ($vars->{$field}->{handle}) {
						${$vars->{$field}->{handle}} = $defaults->{$field}->{value};
					}
				} else { #values pulled from queries and such
					my $result = $self->db->getarray(@{$defaults->{$field}->{sql}});

					if (defined $result->[1] || defined $result->[0]->[1]) { #we got more than a single value, better warn
						$self->q->errorHandler->dbReturnedMoreThanSingleValue;
					}

					my $value = $result->[0]->[0];
					$data->{$row}->{$field} = $value;

					if ($vars->{$field}->{handle}) {
						${$vars->{$field}->{handle}} = $value;
					}

					if ($vars->{$field}->{primarykey}) {
						${$self->primarykeyhandle} = $value;
					}
				}
			}
		}

		foreach (keys %{$data->{$row}}) {
			my $field = $self->verify($_);
			if ($field) {
				unless ($self->displayOnly($field) || $self->readOnly($field)) {
					push @fieldlist, $field;

					my $value;


					if ($self->inputMask($field)) {
						$value = sprintf $self->inputMask($field), $data->{$row}->{$field};
					} elsif ($self->passwd($field)){
						if ($self->q->authn) {
							$value = $self->q->authn->passwdhash($data->{$row}->{$self->passwd($field)->{userField}}, $data->{$row}->{$field});
						}
					} else {
						$value = $data->{$row}->{$field};
					}

					if ($vars->{$field}->{handle}) {
						${$vars->{$field}->{handle}} = $value;
					}

					if ($field eq $self->primarykey) {
						${$self->primarykeyhandle} = $value;

					}

					push @bindvalues, $value;

					if ($self->writefunc($field) ) {
						push @binds,  $self->fieldlist->{$field}->{writefunc};
					} else {
						push @binds, "?";
					}

				}
			}
		}

		my $insertclause = join ', ', @fieldlist;
		my $binds = join ', ', @binds;
		my $query = "insert into $table ($insertclause) values ($binds)";
#		$self->q->util->debug->edump($query."\n".join ',', @bindvalues);

		$self->db->do($query, @bindvalues);

		if ($self->mysqlAuto) {
			my $query = 'select LAST_INSERT_ID()';
			${$self->primarykeyhandle} = $self->db->get($query);
		}


		if ($additional) { #addional queries run on insert
			foreach my $field (keys %$additional) {
				my $result = $self->db->getarray($additional->{$field}->{sql});

				if (defined $result->[1] || defined $result->[0]->[1]) { #we got more than a single value, better warn
					$self->q->errorHandler->dbReturnedMoreThanSingleValue;
				}

				my $value = $result->[0]->[0];

				if ($additional->{$field}->{handle}) {
					${$additional->{$field}->{handle}} = $value ;
				}
			}
		}
	}
}

#----------------------------------------------------------------------
sub insertadditional {
	my $self = shift;

	return $self->{_insertadditional};
}

#----------------------------------------------------------------------
sub insertdefaults {
	my $self = shift;

	return $self->{_insertdefaults};
}

#--------------------------------------------------------------------
sub joins {
	my $self = shift;

	return wantarray ? @{$self->{_joins}} : $self->{_joins};
}

#--------------------------------------------------------------------
sub label {
	my $self = shift;
	my $field = shift;

	return $self->fieldlist->{$field}->{label} ? $self->fieldlist->{$field}->{label} : $self->fieldlist->{$field}->{name};
}

#----------------------------------------------------------------------
sub new {
	my $class = shift;
	my $db = shift;
	my $args = shift;

	my $var = undef;

	my $self = {
		_data 			=> [],
		_db			=> $db,
		_table			=> $args->{table},
		_basewhere		=> $args->{basewhere},
		_primarykey		=> $args->{primarykey},
		_orderby		=> $args->{orderby},
		_joins			=> $args->{joins},
		_insertdefaults		=> $args->{insertdefaults},
		_insertadditional	=> $args->{insertadditional},
		_updatedefaults		=> $args->{updatedefaults},
		_updateadditional	=> $args->{updateadditional},
		_where			=> '',
		_mysqlAuto		=> $args->{mysqlAuto},
		_primarykeyhandle		=> \$var,
		_checkboxes		=> [],

	};

	$self->{_fieldlist} = {};
	tie (%{$self->{_fieldlist}}, 'Tie::IxHash');

	foreach (@{$args->{fieldlist}}) {
		$self->{_fieldlist}{$_->{name}} = $_;
		if ($_->{webcontrol} && ($_->{webcontrol}->{type} eq 'checkbox')) {
			push @{$self->{_checkboxes}}, $_->{name};
		}
	}

	return bless $self, $class;
}

#------------------------------------------------------------------------------
sub noLabel {
	my $self = shift;
	my $field = shift;

	if (exists $self->fieldlist->{$field}) {
		return $self->fieldlist->{$field}->{noLabel};
	} else {
		return;
	}
}

#--------------------------------------------------------------------
sub orderby {
	my $self = shift;
	my $value = shift;

	if ($value) {
		return $self->{_orderby} = $value;
	} else {
		return $self->{_orderby};
	}
}

#-------------------------------------------------------------------------------
sub outputMask {
	my $self = shift;
	my $field = shift;

	if (exists $self->fieldlist->{$field}) {
		if ($self->fieldlist->{$field}->{outputMask}) {
			return $self->fieldlist->{$field}->{outputMask};
		} elsif ($self->fieldlist->{$field}->{outputmask}) {
			return $self->fieldlist->{$field}->{outputmask};
		}
	} else {
		return;
	}
}

#------------------------------------------------------------------------------
sub multipleField {
	my $self = shift;
	my $field = shift;

	if (exists $self->fieldlist->{$field}) {
		return $self->fieldlist->{$field}->{multi};
	} else {
		return;
	}
}

#----------------------------------------------------------------------------------------
sub multipleFieldList {
	my $self = shift;

	my @multipleFieldList;
	foreach my $field (keys %{$self->{_fieldlist}}) {
		if ($self->multipleField($field)) {
			push @multipleFieldList, $self->fieldlist->{$field}->{name};
		}
	}

	return wantarray ? @multipleFieldList : \@multipleFieldList;

}

#-----------------------------------------------------------------------------
sub multipleFieldLabels {
	my $self = shift;

	my @multipleFieldLabels;
	foreach my $field (keys %{$self->{_fieldlist}}) {
		if ($self->fieldlist->{$field}->{multi}) {
			push @multipleFieldLabels, $self->fieldlist->{$field}->{label} ? $self->fieldlist->{$field}->{label} : $self->fieldlist->{$field}->{name};
		}
	}

	return wantarray ? @multipleFieldLabels : \@multipleFieldLabels;

}

#------------------------------------------------------------------------------
sub mysqlAuto {
	my $self = shift;

	return $self->{_mysqlAuto};

}

#------------------------------------------------------------------------------
sub passwd {
	my $self = shift;
	my $field = shift;

	if (exists $self->fieldlist->{$field}) {
		if ($self->fieldlist->{$field}->{passwd}) {
			return $self->fieldlist->{$field}->{passwd};
		} else {
			return;
		}
	} else {
		return;
	}
}

#------------------------------------------------------------------------------
sub primarykey {
	my $self = shift;
	my $value = shift;

	if ($value) {
		return $self->{_primarykey} = $value;
	} else {
		return $self->{_primarykey};
	}
}

#------------------------------------------------------------------------------
sub primarykeyhandle {
	my $self = shift;

	return $self->{_primarykeyhandle};
}

#------------------------------------------------------------------
sub q {
	my $self = shift;

	return $self->db->q;
}

#-----------------------------------------------------------------------------
sub readfunc {
	my $self = shift;
	my $field = shift;

	if (exists $self->fieldlist->{$field}) {
		return $self->fieldlist->{$field}->{readfunc};
	} else {
		return;
	}
}

#------------------------------------------------------------------------------
sub readOnly {
	my $self = shift;
	my $field = shift;

	if (exists $self->fieldlist->{$field}) {
		if ($self->fieldlist->{$field}->{readOnly}) {
			return $self->fieldlist->{$field}->{readOnly};
		} elsif ($self->fieldlist->{$field}->{readonly}) {
			return $self->fieldlist->{$field}->{readonly};
		}
	} else {
		return;
	}
}

#--------------------------------------------------------------------
sub select {
	my $self = shift;
	my @bindvars = @_;

	my ($query, @wherebinds)  = $self->createSelect;

	if (@wherebinds) {
		unshift @bindvars, $_ for @wherebinds;
	}

	my @data;
	my $sth;

	my ($pkg, $file, $line) = caller;

	eval {
		$sth = $self->db->dbh->prepare($query);
		$sth->execute(@bindvars);
#		$self->q->util->debug->edump($query, @bindvars);
	};

	if ($@) {
		$self->q->errorHandler->dbError($pkg, $file, $line, $query);
	} else {

		while (my @record = $sth->fetchrow_array) {
			my @fieldlist = keys %{$self->fieldlist};

			my $record = {};
			tie (%$record, 'Tie::IxHash');

			for (0..$#fieldlist) {
				next if	$self->passwd($fieldlist[$_]);
				$record->{$fieldlist[$_]} = $record[$_];
			}

			push @data, $record;
		}
	}

	$self->{_data} = \@data;

	#$self->q->util->debug->edump(\@data);
	return $self->{_data};
}

#-------------------------------------------------------------------------------
sub table {
	my $self = shift;
	my $value = shift;

	if ($value) {
		return $self->{_table} = $value;
	} else {
		return $self->{_table};
	}
}

#-------------------------------------------------------------------------------
sub update {
	my $self = shift;
	my $data = shift;
	my $vars = shift;

	my $table = $self->table;
	my $primarykey = $self->primarykey;
	my $defaults = $self->updatedefaults;
	my $additional = $self->updateadditional;

	foreach my $ID (keys %$data) {
		my @updates;
		my @binds;

		if (%$vars) {
			foreach (keys %$vars) {
				if ($vars->{$_}->{value}) {
					$data->{$ID}->{$_} = ref $vars->{$_}->{value} ? ${$vars->{$_}->{value}} : $vars->{$_}->{value};
#					$self->q->util->debug->edump("var: ".$vars->{$_}->{value}." -- ".${$vars->{$_}->{value}});
				}
			}
		}

		if ($defaults) {
			foreach my $field (keys %$defaults) {
				if ($defaults->{$field}->{value}) { #static quanities
					$data->{$ID}->{$field} = $defaults->{$field}->{value};
					if ($vars->{$field}->{handle}) {
						${$vars->{$field}->{handle}} = $defaults->{$field}->{value};
					}
				} else { #values pulled from queries and such
					my $result = $self->db->getarray(@{$defaults->{$field}->{sql}});

					if (defined $result->[1] || defined $result->[0]->[1]) { #we got more than a single value, better warn
						$self->q->errorHandler->dbReturnedMoreThanSingleValue;
					}

					my $value = $result->[0]->[0];
					$data->{$ID}->{$field} = $value;

					if ($vars->{$field}->{handle}) {
						${$vars->{$field}->{handle}} = $value;
					}
				}
			}
		}


		foreach (keys %{$data->{$ID}}) {
			my $field = $self->verify($_);

			if ($field) {
				unless ($self->displayOnly($field) || $self->readOnly($field)) {
					if ($vars->{$field}->{handle}) {
						${$vars->{$field}->{handle}} = $data->{$ID}->{$field};

					}

					if ($field eq $self->primarykey) {
						${$self->primarykeyhandle} = $data->{$ID}->{$field};

					}

					if ($self->inputMask($field)) {
						push @binds, sprintf $self->inputMask($field), $data->{$ID}->{$field};
					} elsif ($self->passwd($field)){
						if ($data->{$ID}->{$field}) {
							if ($self->q->authn) {
								push @binds, $self->q->authn->passwdhash($data->{$ID}->{$self->passwd($field)->{userField}}, $data->{$ID}->{$field});
							}
						}
					} else {
						push @binds, $data->{$ID}->{$field};
					}

					if ($self->writefunc($field) ) {
						push @updates,  "$field = ".$self->fieldlist->{$field}->{writefunc};

					} elsif ($self->passwd($field)) {
						if ($self->q->authn) {
							if ($data->{$ID}->{$field}) {
								push @updates,  "$field = ?";
							}
						}
					} else {
						push @updates,  "$field = ?";
					}
				}
			}
		}

		if (@{$self->checkboxes}) {
			foreach (@{$self->checkboxes}) {
				next if exists $data->{$ID}->{$_};

				if ($vars->{$_}->{handle}) {
					${$vars->{$_}->{handle}} = '';
				}

				push @updates,  "$_ = ?";
				push @binds, '';

			}
		}

		my $updateclause = join ',', @updates;

		my $query = "update $table set $updateclause where $primarykey = ?";

#		$self->q->util->debug->edump($query, join ',', @binds. " key: $ID");

		$self->db->do($query, @binds, $ID);

		${$self->primarykeyhandle} = $ID;

		if ($additional) { #addional queries run on insert
			foreach my $field (keys %$additional) {
				my $result = $self->db->getarray($additional->{$field}->{sql});

				if (defined $result->[1] || defined $result->[0]->[1]) { #we got more than a single value, better warn
					$self->q->errorHandler->dbReturnedMoreThanSingleValue;
				}

				my $value = $result->[0]->[0];

				if ($additional->{$field}->{handle}) {
					${$additional->{$field}->{handle}} = $value ;
				}
			}
		}
	}


}

#----------------------------------------------------------------------
sub updateadditional {
	my $self = shift;

	return $self->{_updateadditional};
}

#----------------------------------------------------------------------
sub updatedefaults {
	my $self = shift;

	return $self->{_updatedefaults};
}

#-----------------------------------------------------------------------------
sub validator {
	my $self = shift;
	my $field = shift;

	if (exists $self->fieldlist->{$field}) {
		return $self->fieldlist->{$field}->{validator};
	} else {
		return;
	}

}

#----------------------------------------------------------------------------------------
sub verify {
	my $self = shift;
	my $value = shift;

	$value =~ /^([\w\d\-\.]+)$/; #letters, numbers, underscores, dots, and dashes only please.
	my $field = $1;

	if (exists $self->fieldlist->{$field}) { #fieldname has to be in recordset
		if ($field =~ /\./) {		 #if there's a . in the fieldname
			my $table = $self->table;
			if ($field =~ /^$table/) { #the first part has to be the recordset's table
				return $field;
			} else {		# its a joined field, no modification allowed
				return;
			}
		}
		return $field;
	}

	return;
}

#-----------------------------------------------------------------------------
sub visibleFieldLabels {
	my $self = shift;

	my @visibleFieldLabels;
	foreach my $field (keys %{$self->{_fieldlist}}) {
		unless ($self->fieldlist->{$field}->{hidden}) {
			push @visibleFieldLabels, $self->fieldlist->{$field}->{label} ? $self->fieldlist->{$field}->{label} : $self->fieldlist->{$field}->{name};
		}
	}

	return wantarray ? @visibleFieldLabels : \@visibleFieldLabels;

}

#-----------------------------------------------------------------------------
sub visibleFields {
	my $self = shift;

	my @visibleFieldList;
	foreach my $field (keys %{$self->{_fieldlist}}) {
		unless ($self->fieldlist->{$field}->{hidden}) {
			push @visibleFieldList, $self->fieldlist->{$field}->{name};
		}
	}

	return wantarray ? @visibleFieldList : \@visibleFieldList;
}

#------------------------------------------------------------------------------
sub webcontrol {
	my $self = shift;
	my $field = shift;

	if (exists $self->fieldlist->{$field}) {
		return $self->fieldlist->{$field}->{webcontrol};
	} else {
		return;
	}
}

#-----------------------------------------------------------------------------
sub where {
	my $self = shift;
	my @values = @_;

	if (@values) {
		if (scalar @values > 1) {
			return $self->{_where} = \@values; #theres a list, store an arrayref

		} else {
			return $self->{_where} = $values[0]; #where is a single string, store a scalar
		}
	} else {
		return $self->{_where};
	}
}

#-----------------------------------------------------------------------------
sub writefunc {
	my $self = shift;
	my $field = shift;

	if (exists $self->fieldlist->{$field}) {
		return $self->fieldlist->{$field}->{writefunc};
	} else {
		return;
	}
}

1;

__END__