#!/usr/bin/perl

use warnings;
use strict;

use DBI;
use CGI;
use HTML::Template;
use CGI::Carp qw(fatalsToBrowser);

my $q = new CGI;
my ($dbh, $sth);
my @firma_loop;
my @contact_loop;
my $daten;

my $aktion = $q->param('aktion') || '';

$dbh = DBI->connect ("DBI:mysql:host=192.168.42.6;database=cdmcrm",
                     "www-data", "www-data", {PrintError => 0, RaiseError => 1});

&fetch_IDs();
#&fetch_ent() if ($aktion eq 'Suchen');
&fetch_ent();
&html_fill();
$dbh->disconnect();
exit(0);

#
# Ab hier Subroutinen
#

sub fetch_IDs {
  my $sth = $dbh->prepare ("	SELECT firma.eid, pid
  						FROM firma
  						JOIN person
  						ON person.eid = firma.eid
  						WHERE Firma LIKE 'N%'
  						");
  $sth->execute();

  while (my ($eid, $pid) = $sth->fetchrow_array()) {
    push @{$daten->{$eid}}, $pid;
  }
  $sth->finish();

# DEBUG
#  for (keys(%$daten)) {
#    print $_,"\n";
#    print "--", $_,"\n" for (@{$daten->{$_}});
#  }

}

sub html_fill {

  # Parameter-loop fuer Suchformular fuellen
  my @loop = (
  			{ value=>'Firma', selected=>'', label=>'Firma' },
  			{ value=>'Konzern', selected=>'', label=>'Konzern' },
  			{ value=>'PLZ', selected=>'', label=>'PLZ' },
  			{ value=>'Ort', selected=>'', label=>'Ort' }
  		   );

  # Vorlagen definieren und ausfuellen
  my $template = HTML::Template->new(filename => 'selectlist2.tmpl');
  $template->param( title => 'Suchformular',
                    url => $q->url(),
                    field1_loop => \@loop,
                    field2_loop => \@loop,
                    firma_loop => \@firma_loop	);

  print $template->output;

}

sub fetch_ent {
  for (keys(%$daten)) {

    my $eid = $_;
    my $sth = $dbh->prepare ("	SELECT Firma, Strasse, PLZ, Ort
    				 		FROM firma
    				 		WHERE eid = $eid
    				 		");
    $sth->execute();

    while (my ($firma, $strasse, $plz, $ort) = $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();
  }
}

sub fetch_contact {
  my $eid = shift @_;
  my @contact_loop;

  for (@{$daten->{$eid}}) {
    my $pid = $_;
    my $sth = $dbh->prepare ("	SELECT Name, Vorname, Telefon, Fax, Email
    						FROM person
    						WHERE pid = '$pid'
    						");
    $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;
}

<TMPL_INCLUDE NAME="header.tmpl">
<form action="<TMPL_VAR NAME="url">" method="POST" target="">
<TMPL_INCLUDE NAME="search.tmpl">
</form>
<table>
<TMPL_LOOP name="firma_loop">
 <tr>
  <td>
   <table>
     <tr>
       <td><TMPL_VAR name="firma"></td>
       <td><TMPL_VAR name="strasse"></td>
       <td><TMPL_VAR name="plz"></td>
       <td><TMPL_VAR name="ort"></td>
     </tr>
   </table>
  </td>
 </tr>
 <TMPL_LOOP name="contact_loop">
 <tr>
  <td>
   <table>
     <tr>
        <td><TMPL_VAR name="nachname"></td>
        <td><TMPL_VAR name="vorname"></td>
        <td><TMPL_VAR name="telefon"></td>
        <td><TMPL_VAR name="fax"></td>
        <td><TMPL_VAR name="email"></td>
     </tr>
   </table>
  </td>
 </tr>
 </TMPL_LOOP>
</TMPL_LOOP>
</table>
<TMPL_INCLUDE NAME="footer.tmpl">