#!/usr/bin/perl
###########################################
# handleform -- Send FORM data to databases
# Mike Schilli, 2002 (m@perlmeister.com)
###########################################
use warnings;
use strict;

use CGI qw(:all);
use DBI;
use Log::Log4perl qw(get_logger);

###########################################
my $DB_DIR      = "/tmp/data";
my $DB_HOST     = "localhost";
my $DB_NAME     = "webdata";

    # CSV-File
my $DB_DRIVER   = "CSV";
my $DB_PAR      = "f_dir=$DB_DIR";

    # MySQL database
#my $DB_DRIVER  = "mysql";
#my $DB_PAR     = "database=$DB_NAME;" .
#                 "host=$DB_HOST";

my $DB_USER     = "root";
my $DB_PASSWD   = "";
my $DB_TABLE    = "survey";

my $THANK_YOU   = "/thankyou.html";
my $ERROR       = "/error.html";

my @FIELDS = qw(
    name hobbies glueck einkommen
);

my %MAP = (
    einkommen => { e1 => "Unter 100.000", 
                   e2 => "Über 100.000" },
);
###########################################

Log::Log4perl::init(\ <<'EOT');
Log4perl.logger = WARN, File
Log4perl.appender.File= Log::Dispatch::File
Log4perl.appender.File.layout=\
  Log::Log4perl::Layout::PatternLayout
Log4perl.appender.File.filename=/tmp/hf.log
Log4perl.appender.File.layout.Conversion\
Pattern=%d %p %F (%L) %m %n
EOT

my $DB_DSN = "DBI:$DB_DRIVER:$DB_PAR";
my $DATE   = "i_date";

my $logger = Log::Log4perl::get_logger();

$SIG{__DIE__} = sub { 
    $logger->fatal(@_);
    print redirect($ERROR);
    exit 0 };

my %val = ();

for my $field (@FIELDS) {
    if(defined param($field)) {
        my @v;
        for(param($field)) {
            if(exists $MAP{$field} and
               exists $MAP{$field}->{$_}) {
               push @v, $MAP{$field}->{$_};
            } else {
               push @v, $_;
            }
        }
        $val{$field} = join '|', @v;
    }
}

init_db();

my $dbh = DBI->connect($DB_DSN, $DB_USER, 
    $DB_PASSWD, { RaiseError => 1 } ) or 
    die "Cannot connect to DB";

init_table($dbh);

unshift @FIELDS, $DATE;
$val{$DATE} = nicedate();

my $fieldlist = join(",", @FIELDS);
my $valuelist = join(",", 
    map { $dbh->quote($val{$_}) } @FIELDS);

my $sql = qq[
    INSERT INTO $DB_TABLE ( $fieldlist )
    VALUES ( $valuelist ) ];
my $sth = $dbh->do($sql);

print redirect($THANK_YOU);

###########################################
sub nicedate {
###########################################

    my ($s,$mi,$h,$d,$mo,$y) = localtime();

    return sprintf(
        "%02d-%02d-%d %02d:%02d:%02d",
        $mo+1, $d, $y+1900, $h, $mi, $s);
}

###########################################
sub init_db {
###########################################

    my($drh) = DBI->install_driver(
                               $DB_DRIVER);
    my @dbs = $drh->data_sources(
                  { 'f_dir' => $DB_DIR } );
    @dbs = () unless defined $dbs[0];
    return if grep { /\b$DB_NAME/ } @dbs;

    return if $DB_DRIVER eq "CSV";

    $drh->func("createdb", $DB_NAME, 
        $DB_HOST, $DB_USER, $DB_PASSWD, 
        "admin");
}

###########################################
sub init_table {
###########################################
    my $dbh = shift;

    if($DB_DRIVER eq "CSV") {
        die "$DB_DIR missing/protected" if
           !-d $DB_DIR or !-w _ or !-x _;
    }

    my @tables = $dbh->tables();

    return if grep { 
     $_ =~ /\b$DB_TABLE$/ } $dbh->tables();

    my $defs = join ",", map { 
        "$_ VARCHAR(50)" } $DATE, @FIELDS;

    $dbh->do(qq[
       CREATE TABLE $DB_TABLE ( $defs ) ]);
}