#!/usr/local/bin/perl -w

use CGI::Pretty ':standard',':html';
use DBI;
use strict;
use Data::Dumper;

my $q = new CGI;

if ($q->param("getpict")) {
  getpict($q);
} elsif ($q->param("change")) {
  change_values($q);
} elsif ($q->param("Suchen")) {
  show_results($q);
} elsif ($q->param("update")) {
  update_values($q);
} else {
  show_form();
}


sub show_results {
  my ($q) = @_;
  my $s_schlag = $q->param("schlag");
  my $s_autor  = $q->param("autor");
  my $s_verlag = $q->param("verlag");
  my $s_order  = $q->param("order");
  $q->delete("order") if $s_order; 
  my $dbh = DBI->connect("DBI:mysql:biblio","ck");
  my (@autoren, @table,$autorcount);
  @autoren = ();

  $autorcount= 0;
  if ($s_autor) {
    my ($id );
    my $querya = "select id from autoren where " .
      "upper(nachname) like '%" . uc($s_autor) . "%'";
    my $sth = $dbh->prepare($querya);
    $sth->execute();
    $sth->bind_col(1,\$id);
    while ($sth->fetch) {
      push @autoren,$id;
      $autorcount++;
    }
    if (! $autorcount && ! $s_schlag && ! $s_verlag) {
      print header,start_html(-title => "Nicht gefunden"),
      h2("Keine Bücher von Autor '$s_autor' gefunden"),
      end_html;
      exit(0);
    }
  }
  my ($id, $titel, $untertitel, $jahr, $seiten, $waehrung, $preis,
      $r_vorname, $r_nachname, $r_titel,$verlag, $ort, $a_vorname, $a_nachname,
     $r_jahr,$r_monat, $bild, $isbn, $a_id);
  my $querya = "select a.id, a.vorname, a.nachname from autoren a, rez_aut r ".
    "where r.buch_id = ? and a.id = r.autor_id order by a.nachname";

  my $queryb = "select distinct b.id, b.titel, b.untertitel, b.jahr, " .
    " b.waehrung, b.preis, b.seiten, b.rez_monat,b.rez_jahr, b.bild, ".
    " b.isbn, r.vorname, r.nachname,r.titel,v.name,v.ort ".
      "from buecher b, rezensenten r, verlage v, rez_aut a " .
	"where b.rez_id = r.id and b.verlag_id = v.id ".
        "and a.buch_id = b.id";
  if ($s_schlag || $s_verlag || $s_autor) {
    my @conditions = ();
    push @conditions, 
      "upper(b.titel) like '%".uc($s_schlag)."%'" if $s_schlag;
    push @conditions, 
      "upper(v.name) like '%".uc($s_verlag)."%'" if $s_verlag;
    push @conditions, 
      "a.autor_id in (" . join(",",@autoren) . ") "
	if $autorcount;
    $queryb .= " and (" . join (" or ",@conditions) . ")" if @conditions;
  }
  $queryb .= " order by $s_order" if $s_order;
#  print STDERR $queryb;
  my $sthb = $dbh->prepare($queryb);
  my $stha = $dbh->prepare($querya);

  $sthb->execute;
  $sthb->bind_columns(undef,\$id, \$titel,\$untertitel,\$jahr,\$waehrung,
		      \$preis,\$seiten,\$r_monat,\$r_jahr,\$bild,\$isbn,
		      \$r_vorname,\$r_nachname,\$r_titel,\$verlag,\$ort);
  @table=();
  my $myself = $q->self_url;
  #
  # Trenner für "order" bestimmen. Neuere Versionen
  # von CGI.pm benutzen ":" statt "?" und "?" statt "&"
  #
  my $sep = ($myself =~ /&/ ? "?" : ";");
  $myself .= $sep;
  push @table, TR(th({-halign => "center"},
		      [" ",
		       a({-href => "$myself"."order=b.titel"},"Titel").
		       a({-href => "$myself"."order=b.preis"},"/Preis"),
		       " ",
		       a({-href => "$myself"."order=b.verlag"},"Verlag").
		       a({-href => "$myself"."order=b.jahr"},"/Jahr"),
		       a({-href => "$myself"."order=r.nachname"},"Autor").
		       a({-href => "$myself"."order=b.rez_jahr,b.rez_monat"},
			 "/Ausgabe")]));
  
  while ($sthb->fetch) {
    @autoren=();
    $stha->execute($id);
    $stha->bind_columns(undef,\$a_id,\$a_vorname,\$a_nachname);
    while ($stha->fetch) {
      push @autoren, [$a_id,"$a_vorname $a_nachname"];
    }
    #
    # Alle Daten sind da: in @table speichern
    #
#    push @table, TR(td([img({-src => "/rez_icons/$bild"}),

    push @table, TR(td([img({-src => $myself."getpict=$id"}),
			b(a({-href => $myself."change=buecher+$id"},$titel)). br . 
			($untertitel ? "$untertitel<br>" :"") .
			"$seiten Seiten<br>$preis $waehrung",
			join ("<br>",
			      map {a({-href => $myself."change=autoren+$_->[0]"},
				     $_->[1])}
				  @autoren),
			"$verlag, <br>$ort $jahr<br>ISBN: $isbn",
			"in <I>iX</I> $r_monat/$r_jahr<br>".
			"von ". ($r_titel ? "$r_titel ": "") .
			"$r_vorname $r_nachname"]));
  }
  print header,start_html(-title => "Suchergebnisse"),
  $q->table(@table), end_html;
  $stha->finish;
  $sthb->finish;
  $dbh->disconnect;
}

sub getpict {
  my ($q) = @_;
  my $dbh = DBI->connect("DBI:mysql:biblio","ck"); 
  $dbh->{LongReadLen} = 30*1024;
  
  my $sth = $dbh->prepare("select bild from buecher where id=?");
  $sth->execute($q->param("getpict"));
  my $bild;
  $sth->bind_col(1,\$bild);
  $sth->fetch;
  $sth->finish;
  $dbh->disconnect;
  print $q->header(-type => "image/jpeg"),$bild;

}

sub show_form {
  print header, 
  start_html(-title => "Suche in iX-Buchbesprechungen"),
  start_form(-name => "Suchen", -method => "GET"),
  h2("Suche in iX-Buchbesprechungen");
  my @table;
  push @table, TR(td(["Schlagwort&nbsp;",
		     textfield(-name => "schlag",
			       -size => 20,
			       -maxlength => 50)]));
  push @table, TR(td(["Autor&nbsp;",
		     textfield(-name => "autor",
			       -size => 20,
			       -maxlength => 50)]));
  push @table, TR(td(["Verlag&nbsp;",
		     textfield(-name => "verlag",
			       -size => 20,
			       -maxlength => 50)]));
  print table(@table),
  submit(-name => "Suchen"),
  end_form;
  
  print end_html;
}

sub change_values {
  my ($q) = @_;
  my $code = $q->param("change");
  my ($id) = ($code =~ /\D(\d+)/);
  my ($table) = ($code =~ /(\D+)/);
  $q->delete("change");
  my $dbh = DBI->connect("DBI:mysql:biblio","ck"); 
  my $sth = $dbh->prepare("LISTFIELDS $table");
  $sth->execute();
  my @fieldnames = @{$sth->{NAME}};
  my @fieldtypes = @{$sth->{TYPE}};
  my @fieldprecs = @{$sth->{PRECISION}};
  print header, start_html("Eintrag in $table ändern"),
  h2("Eintrag #$id in Tabelle '$table' ändern"),
  start_form(-name => "change", -method => "POST");
  my $i = 0;
  my $query = "select " . join (",",@fieldnames) . " from $table " .
    "where id = $id";
  $sth = $dbh->prepare($query);
  $sth->execute();
  my @result = $sth->fetchrow_array();
  my @table = ();
  $sth->finish;
  $dbh->disconnect;
  foreach (@fieldnames) {
    my $prec= $fieldprecs[$i];
    my $type = $fieldtypes[$i] == 1 || $fieldtypes[$i] == 12 ? "c" : "n";
    # *c*haracter, *n*umeric
    push @table, TR(td(["$_:",
			$q->textfield(-name => "t$type+$_",
				      -maxlength => $prec,
				      -size => $prec > 60 ? 60 : $prec,
				      -default => $result[$i])
		       ])) 
      unless /id/ || /bild/;
    $i++;
  }
  print table(@table),
  hidden(-name => "table", -value => "$table"),
  hidden(-name => "id", -value => "$id"),
  submit(-name => "update",
	 -value => "Ändern"),
  submit(-name => "ignore",
	 -value => "Abbrechen"),
  end_form, end_html;
}

sub update_values {
  my ($q) = @_;
  my @fieldnames;
  my $table = $q->param("table");
  my $id = $q->param("id");
  my $dbh = DBI->connect("DBI:mysql:biblio","ck");
  foreach ($q->param()) {
    if (/^t(.)\+/) {
      push @fieldnames,substr($_,3)."=". 
	($1 eq "c" ? $dbh->quote($q->param($_)) : $q->param($_)||"NULL") ;
    }
  }
  if (@fieldnames) {
    my $query = "update $table set " . join(",",@fieldnames) .
      " where id = $id";
    my $sth = $dbh->prepare($query);
    print STDERR $query;
    $sth->execute();
    $sth->finish();
  }
  $dbh->disconnect();
  print $q->redirect($q->url());
}