#!/usr/bin/perl -w

use strict;
use warnings;
use DBI;
use HTML::Template;

my ($dbh, $sth);
my @firma_loop;
my @contact_loop;

$dbh = DBI->connect ("DBI:mysql:host=192.168.0.1;database=myDB",
                     "user", "pw", {PrintError => 0, RaiseError => 1});

&fetch_ent();

$dbh->disconnect();
exit(0);

sub fetch_ent {
  my $sth = $dbh->prepare ("	SELECT Firma, Strasse, PLZ, Ort, EID
  						FROM firma
  						WHERE Firma LIKE 'N%'
  						");
  $sth->execute();

  while (my ($firma, $strasse, $plz, $ort, $eid) = $sth->fetchrow_array()) {
  my @inner_loop = &fetch_contact($eid);
  my %row = (
             firma 		=> $firma,
             strasse 	=> $strasse,
             plz 		=> $plz,
             ort 		=> $ort,
             contact_loop => \@inner_loop
            );
  push(@firma_loop, \%row);
  }
  $sth->finish();

  my $template = HTML::Template->new(filename => 'customer2.tmpl');
  $template->param(firma_loop => \@firma_loop);
  #print "Content-Type: text/html\n\n";
  print $template->output;
}

sub fetch_contact {

  my $eid = shift @_;
  my @contact_loop;
  my $sth = $dbh->prepare ("	SELECT Name, Vorname, Telefon, Fax, Email
  						FROM person
  						WHERE EID = '$eid'
  						");
  $sth->execute();

  while (my ($nachname, $vorname, $telefon, $fax, $email) = $sth->fetchrow_array()) {
  my %row = (
             nachname 	=> $nachname,
             vorname 	=> $vorname,
             telefon 	=> $telefon,
             fax 		=> $fax,
             email 		=> $email
            );

  push(@contact_loop, \%row);
  }
  $sth->finish();
  return @contact_loop;
}

my @inner_loop = &fetch_contact($eid);
  my %row = (
             firma 		=> $firma,
             strasse 	=> $strasse,
             plz 		=> $plz,
             ort 		=> $ort,
             contact_loop => \@inner_loop
            );