#!c:/perl/bin/perl.exe -w
# classe responsável pela ligação à Base de dados
package LigacaoBD;

use DBI;

$database = 'inquiricao3';
$host = 'localhost';
$port = '5432';
$user = 'admin';
$pwd = 'admin';

my $dbh;

sub Open
{
	if(!defined($dbh))
	{
		$dbh = DBI->connect("dbi:PgPP:dbname=$database;host=$host;port=$port",$user, $pwd) or die "Erro ao estabelecer ligação à BD";
	}
}

sub Close
{
	if(defined($dbh))
	{
		$dbh->disconnect;
	}
}

sub ObterListaConcelhos
{
	my $str;
	my $sql = "Select * From concelhos Union Select 0, 'Todos', 0";
	my $sth = $dbh->prepare($sql);
	$sth->execute;

	while ($data = $sth->fetchrow_hashref) 
	{
		$str .=  "<option value=$data->{concelho}>$data->{nome}</option>";
	}
	
	return $str;
}

sub ObterListaDistritos
{
	my $str;
	my $sql = "Select * From distritos Union Select 0, 'Todos'";
	my $sth = $dbh->prepare($sql);
	$sth->execute;

	while ($data = $sth->fetchrow_hashref) 
	{
		$str .=  "<option value=$data->{distrito}>$data->{nome}</option>";
	}
	
	return $str;
}

sub ObterListaFreguesias
{
	my $str;
	my $sql = "Select * From freguesias Union Select 0, 'Todas', 0";
	my $sth = $dbh->prepare($sql);
	$sth->execute;

	while ($data = $sth->fetchrow_hashref) 
	{
		$str .=  "<option value=$data->{freguesia}>$data->{nome}</option>";
	}
	
	return $str;
}

sub ObterListaResultados
{	
	my ($self, $sql) = @_; 
	
	Open;
	my $sth = $dbh->prepare($sql);
	$sth->execute; 

	my $str = '<table border=1><tr><td><b>Processo</b></td><td><b>Pasta</b></td><td><b>Data</b></td><td><b>Nome</b></td><td><b>Pai</b></td><td><b>Mãe</b></td><td><b>Observações</b></td></tr>';
	if ($sth->{NUM_OF_FIELDS}) 
	{
		while (my $data = $sth->fetchrow_hashref)
		{
			$str .= "<tr><td>$data->{processo}</td>";
			$str .= "<td>$data->{pasta}</td>";
			$str .= "<td>$data->{data}</td>";
			$str .= "<td>$data->{nome}</td>";
			$str .= "<td>$data->{pai}</td>";
			$str .= "<td>$data->{mae}</td>";
			$str .= "<td>$data->{observacoes}</td></tr>";
		}
	}
	$str .= '</table>';
	Close;
	return $str;
}
1;