# handleform -- Send FORM data to databases

#!/usr/bin/perl
###########################################
# handleform -- Send FORM data to databases
# Mike Schilli, 2002 ([email protected])
###########################################
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 ) ]);
}