#! /usr/bin/perl

use DBI;
use Text::Wrap;

# Some generic stuff for all LaTeX output files
$latex_foot = '\end{document}';
$latex_headline = '\centerline{\textbf{\large LinuxTag 2001 -- %s}}' . "\n\\vspace{2ex}";

$latex_lthead = '\documentclass{lt}\begin{document}';
%datum = ('2001-07-05' => 'Donnerstag, 5.7.',
	  '2001-07-06' => 'Freitag, 6.7.',
	  '2001-07-07' => 'Samstag, 7.7.',
	  '2001-07-08' => 'Sonntag, 8.7.');

$engine  = "dbi:Pg:dbname=elt2k1;host=kuolema";
$dbh = DBI->connect($engine);
if (!$dbh) {
    print "Access to database denied!\n";
    return 1;
}


sub night
{
    my $loc = shift;
    my $query = "SELECT name,email,project,nightplace FROM person WHERE night = 1";
    my $sth;
    my $rc;
    my @row;
    my $nr = 0;
    my $name;

    $query .= " AND nightplace = '$loc'" if ($loc);
    $query .= " ORDER BY name";

    $sth = $dbh->prepare($query);
    if ($sth && ($rc = $sth->execute) > 0) {
        while (@row = $sth->fetchrow_array) {
	    if ($row[1]) {
		$name = $row[0] . " <" . $row[1] . ">";
	    } else {
		$name = $row[0];
	    }
	    printf "%2d. %-45s  %-20s  %s\n", ++$nr, substr($name, 0, 45), $row[2], $row[3];
        }
    }

}

sub get_projects
{
    my $query = "SELECT name,boothnr FROM project";
    my $sth;
    my $rc;
    my @row;
    my %projects;

    $query .= " WHERE boothnr <> ''" if ($opt_onlybooth);
    $query .= " ORDER BY name";
    $sth = $dbh->prepare($query);
    if ($sth && ($rc = $sth->execute) > 0) {
        while (@row = $sth->fetchrow_array) {
	    $projects{$row[0]} = $row[1];
        }
    }
    return %projects;
}

sub supplies
{
    my $query;
    my $sth;
    my $rc;
    my @row;
    my $project;
    my $text;
    my $booth;
    my %projects = get_projects ();

    foreach $project (keys %projects) {
	$text = '';
	$booth = '';

	$query = sprintf ("SELECT supplies.amount,supplylist.name,supplylist.id"
			  ." FROM supplylist,supplies "
			  ."WHERE supplylist.id = supplies.supply AND project.name = project AND project='%s'"
			  ." ORDER by supplylist.name",
			  $project);

	$sth = $dbh->prepare($query);
	if ($sth && ($rc = $sth->execute) > 0) {
	    while (@row = $sth->fetchrow_array) {
		$booth = $row[3] if (!$booth);
		$text .= sprintf ("%3d  %-10s  %s\n", $row[0], $row[2], $row[1]);
	    }
	} else {
	    $text = "Missing";
	}
	printf "%s (%s)\n\n%s\n\n", $projects{$project}, $project, $text if ($text);
    }
}

sub list
{
    my $query = "SELECT name,boothnr FROM project ";
    my $sth;
    my $rc;
    my @row;
    my $nr = 0;

    $query .= " WHERE boothnr <> ''" if ($opt_onlybooth);
    $query .= " ORDER BY name";

    $sth = $dbh->prepare($query);
    if ($sth && ($rc = $sth->execute) > 0) {
        while (@row = $sth->fetchrow_array) {
	    printf "%2d. %-25s  %s\n", ++$nr, $row[0], $row[1];
        }
    }
}

sub list_mails
{
    my $onlyproject = shift;
    my $query = "SELECT person.project,person.name,person.email "
	    ."FROM project,person "
	    ."WHERE project.name = person.project AND ( infomail = 1 OR list = 1) AND email <> ''";
    my $sth;
    my $rc;
    my @row;
    my $project = "";
    my $space;

    
    $query .= sprintf (" AND project='%s'", $onlyproject) if ($onlyproject);
    $query .= " ORDER BY project,name";

    if ($opt_nospace) {
	$space = "";
    } else {
	$space = "";
    }
    $sth = $dbh->prepare($query);
    if ($sth && ($rc = $sth->execute) > 0) {
        while (@row = $sth->fetchrow_array) {
	    if ($project ne $row[0]) {
		print "\n" if ($project);
		printf "%s\n%s\n\n", $row[0], "-" x length ($row[0]);
		$project = $row[0];
	    }
	    printf "%s%s <%s>,\n", $space, $row[1], $row[2];
        }
    }
}

sub list_titles
{
    my $query;
    my $sth;
    my $rc;
    my @row;

    $query = "SELECT DISTINCT project.boothnr,appendix.boothtitle "
	. "FROM project,appendix WHERE project.name = appendix.project";
    $query .= " AND boothnr <> ''" if ($opt_onlybooth);
    $query .= "  ORDER BY boothnr,boothtitle";
    $sth = $dbh->prepare($query);
    if ($sth && ($rc = $sth->execute) > 0) {
        while (@row = $sth->fetchrow_array) {
	    printf "%s: %s\n", $row[0], $row[1] if ($row[1]);
        }
    }
}

sub latex_head
{
    my $result = '\documentclass[12pt]{article}\usepackage{humanist}';
    my @args = @_;

    $result .= '\usepackage[latin1]{inputenc}\usepackage[T1]{fontenc}';
    if (@args) {
	$result .= join ("\n", @args);
    }

    $result .= '\topmargin=-1cm\setlength{\oddsidemargin}{-1cm}'
    . '\setlength{\textheight}{25.0cm}\thispagestyle{empty}'
    . '\begin{document}';

    return $result;
}

sub latex_members
{
    my $query;
    my $sth;
    my $rc;
    my @row;
    my $count = 0;

    my $thead = '\parbox{5cm}{\textbf{Name}} &\parbox{8cm}{\textbf{Project}}\\\\';
    my $head = '\tablefirsthead{' . $thead .'\hline\hline}'
             . '\tablehead{'
	       . '\multicolumn{2}{l}{Members continued...} \\\\[2ex]'
	       . $thead . '\hline\hline}'
             . '\begin{supertabular}{l|l}' . "\n";

    my $foot = '\end{supertabular}';
    my $trow = '%s & %s \\\\';

    print latex_head ("\\usepackage{supertabular}");
    printf $latex_headline, "Projects-Staff";

    $query = "SELECT name,project FROM person WHERE list=0 ORDER BY name";
    $sth = $dbh->prepare($query);
    if ($sth && ($rc = $sth->execute) > 0) {
	print $head;
        while (@row = $sth->fetchrow_array) {
	    $count++;
	    printf $trow, $row[0], $row[1];
	    print '\hline' if (!($count % 5));
	    print "\n";
        }
	print $foot;
    }

    print "\\clearpage\n";

    $query = "SELECT name,project FROM person WHERE list=0 ORDER BY project,name";
    $sth = $dbh->prepare($query);
    if ($sth && ($rc = $sth->execute) > 0) {
	print $head;
        while (@row = $sth->fetchrow_array) {
	    $count++;
	    printf $trow, $row[0], $row[1];
	    print '\hline' if (!($count % 5));
	    print "\n";
        }
	print $foot;
    }
    print $latex_foot;
}

sub latex_table
{
    my $query;
    my $sth;
    my $rc;
    my @row;
    my $head;
    my $trow;

    my $foot = '\end{tabular}';
    if ($opt_onlybooth) {
	$head = '\renewcommand{\arraystretch}{1.5}\begin{tabular}{lll}';
	$trow = '%s &%s & \hspace{9.5cm}\ \\\\\cline{3-3}';
    } else {
	$head = '\renewcommand{\arraystretch}{1.5}\begin{tabular}{ll}';
	$trow = '%s & \hspace{11cm}\ \\\\\cline{2-2}';
    }

    $query = "SELECT name";
    $query .= ",boothnr" if ($opt_onlybooth);
    $query .= " FROM project WHERE name <> 'intern' AND name <> 'faveve'";
    $query .= " AND boothnr <> ''" if ($opt_onlybooth);
    $query .= " ORDER BY name";
    $sth = $dbh->prepare($query);
    if ($sth && ($rc = $sth->execute) > 0) {
	print latex_head ();
	printf $latex_headline, "Projekte";
	print $head;
        while (@row = $sth->fetchrow_array) {
	    if ($opt_onlybooth) {
		printf $trow . "\n", $row[0], $row[1];
	    } else {
		printf $trow . "\n", $row[0];
	    }
        }
	print $foot;
	print $latex_foot;
    }
}

sub descr
{
    my $query;
    my $sth;
    my $rc;
    my @row;
    my %project;
    my $project;
    my $space;
    my $cols;

    $query = "SELECT name,url,boothnr FROM project";
    $query .= " WHERE boothnr <> ''" if ($opt_onlybooth);
    $sth = $dbh->prepare($query);
    if ($sth && ($rc = $sth->execute) > 0) {
        while (@row = $sth->fetchrow_array) {
	    $project{$row[0]}{'url'} = $row[1];
	    $project{$row[0]}{'boothnr'} = $row[2];
        }
    }

    if ($opt_onlybooth) {
	$query = "SELECT appendix.project,appendix.description,appendix.description_en FROM appendix,project WHERE ";
	$query .= "project.name = appendix.project AND project.boothnr <> ''";
    } else {
	$query = "SELECT project,description,description_en FROM appendix";
    }

    $sth = $dbh->prepare($query);
    if ($sth && ($rc = $sth->execute) > 0) {
        while (@row = $sth->fetchrow_array) {
	    $project{$row[0]}{'descr'} = $row[1];
	    $project{$row[0]}{'descr_en'} = $row[2];
        }
    }

    if ($opt_nospace) {
	$space = "";
	$cols = 0;
    } else {
	$space = "  ";
	$cols = 2;
    }
    foreach $project (sort keys %project) {
	printf "%s\n%s\n\n", $project, "-" x length ($project);
	printf "%sStand: %s\n", $space, $project{$project}{'boothnr'}
	    if (exists $project{$project}{'boothnr'} && length ($project{$project}{'boothnr'}));
	printf "%sURL  : %s\n", $space, $project{$project}{'url'}
	    if (exists $project{$project}{'url'} && length ($project{$project}{'url'}));

	$Text::Wrap::columns = $opt_cols-$cols;
	if (exists $project{$project}{'descr'} && length ($project{$project}{'descr'})) {
	    printf "%s", Text::Wrap::fill ("\n" . " "x$cols," "x$cols,$project{$project}{'descr'}) . "\n";
	} else {
	    if (exists $project{$project}{'descr_en'} && length ($project{$project}{'descr_en'})) {
		printf "\n%s-- ENGLISH --\n", $space;
		printf "%s", Text::Wrap::fill (" "x$cols," "x$cols,$project{$project}{'descr_en'}) . "\n";
	    }
	}

	print  "\n";
    }
}

sub missing_descr
{
    my $query;
    my $sth;
    my $rc;
    my @row;
    my %project;
    my $project;
    my $space;
    my $cols;

    $query = "SELECT name FROM project";
    $sth = $dbh->prepare($query);
    if ($sth && ($rc = $sth->execute) > 0) {
        while (@row = $sth->fetchrow_array) {
	    $project{$row[0]}{'url'} = $row[1];
	    $project{$row[0]}{'boothnr'} = $row[2];
        }
    }

    $query = "SELECT project,description,description_en FROM appendix";
    $sth = $dbh->prepare($query);
    if ($sth && ($rc = $sth->execute) > 0) {
        while (@row = $sth->fetchrow_array) {
	    $project{$row[0]}{'descr'} = $row[1];
	    $project{$row[0]}{'descr_en'} = $row[2];
        }
    }

    foreach $project (sort keys %project) {
	if (!length ($project{$project}{'descr'}) && !length ($project{$project}{'descr_en'})) {
	    list_mails ($project);
	    print "\n";
	    # printf "%s\n%s\n\n", $project, "-" x length ($project);
	}
    }
}

sub descrweb
{
    my $query;
    my $sth;
    my $rc;
    my @row;

    my $name;
    my $descr;
    my $res = '';

    my $t_head = '<table width=100% cellspacing=0 cellpadding=0 border=0>';
    my $t_foot = '</table>';
    my $t_title = '<tr bgcolor="#0d1651"><td align="left"><b><font face=Helvetica,Arial>&nbsp;%s</font></b></td>'
	         .'<td align="right"><b><font color="#ffffff" face=Helvetica,Arial>%s&nbsp;</font></b></td></tr>';
    my $t_descr = '<tr><td colspan=2 align="left"><font face=Helvetica,Arial>%s</font></td></tr>';

    if (@_) {
	$query = "SELECT name,url,boothnr,description_en,description FROM project,appendix ";
    } else {
	$query = "SELECT name,url,boothnr,description,description_en FROM project,appendix ";
    }
    $query .= "WHERE project.name = appendix.project AND boothnr <> '' ORDER BY name";
    $sth = $dbh->prepare($query);
    if ($sth && ($rc = $sth->execute) > 0) {
        while (@row = $sth->fetchrow_array) {
	    if (length ($row[1])) {
		$name = sprintf ("<a href=\"%s\"><font color=\"#ffffff\">%s</font></a>", $row[1], $row[0]);
	    } else {
		$name = sprintf ("<font color=\"#ffffff\">%s</font>", $row[0]);
	    }
	    if (length ($row[3])) { $descr = $row[3]; }
	    elsif (length ($row[4])) { $descr = $row[4]; }
	    else { next; }

	    $res .= sprintf ($t_title, $name, $row[2]);

	    $res .= sprintf ($t_descr, $descr);
        }
    }

    print $t_head . $res . $t_foot . "\n" if ($res);
}

sub nili
{
    my $query;
    my $sth;
    my $rc;
    my @row;
    my $date;
    my $name = "";
    my %nili;
    my %res;
    my $count;

    $dbh->do("SET DateStyle = 'ISO'"); 

    $query = "SELECT person,date,breakfast,dinner,sleeping "
	    ."FROM nili,person "
	    ."WHERE person.name = nili.person AND person.nightplace = 'Nili' ORDER BY nili.person";

    $sth = $dbh->prepare($query);
    if ($sth && ($rc = $sth->execute) > 0) {
	printf "%-25s  Wednesday   Thursday    Friday    Saturday    Sunday     Monday\n", "Name";
	$line = "--------------------------+----------+----------+----------+----------+----------+---------+-----------\n";
	$count = 0;
        while (@row = $sth->fetchrow_array) {
	    if ($name ne $row[0]) {
		if ($name ne "") {
		    print $line if (($count % 5) == 0);
		    $count++;
		    printf "%-24s       %s %s       %s %s %s      %s %s %s      %s %s %s      %s %s %s     %s"
			. "        %dB, %dD, %dS\n",
		    $name,
		                                      $nili{"dinner-07-04"}?"D":" ", $nili{"sleeping-07-04"}?"S":" ",
		    $nili{"breakfast-07-05"}?"B":" ", $nili{"dinner-07-05"}?"D":" ", $nili{"sleeping-07-05"}?"S":" ",
		    $nili{"breakfast-07-06"}?"B":" ", $nili{"dinner-07-06"}?"D":" ", $nili{"sleeping-07-06"}?"S":" ",
		    $nili{"breakfast-07-07"}?"B":" ", $nili{"dinner-07-07"}?"D":" ", $nili{"sleeping-07-07"}?"S":" ",
		    $nili{"breakfast-07-08"}?"B":" ", $nili{"dinner-07-08"}?"D":" ", $nili{"sleeping-07-08"}?"S":" ",
		    $nili{"breakfast-07-09"}?"B":" ",

		    $nili{"breakfast-07-05"} +
		    $nili{"breakfast-07-06"} +
		    $nili{"breakfast-07-07"} +
		    $nili{"breakfast-07-08"} +
		    $nili{"breakfast-07-09"},

		    $nili{"dinner-07-04"} +
		    $nili{"dinner-07-05"} +
		    $nili{"dinner-07-06"} +
		    $nili{"dinner-07-07"} +
		    $nili{"dinner-07-08"},

		    $nili{"sleeping-07-04"} +
		    $nili{"sleeping-07-05"} +
		    $nili{"sleeping-07-06"} +
		    $nili{"sleeping-07-07"} +
		    $nili{"sleeping-07-08"};
		}
		%nili = ();
		$name = $row[0];
	    }

	    $row[1] =~ /^(\d+)-(\d+-\d+)/;
	    $date = $2;

	    $nili{"breakfast-$date"} = $row[2]; $res{"breakfast-$date"} += $row[2];
	    $nili{"dinner-$date"} = $row[3];    $res{"dinner-$date"} += $row[3];
	    $nili{"sleeping-$date"} = $row[4];  $res{"sleeping-$date"} += $row[4];
	}
	print $line if (($count % 5) == 0);
	printf "%-24s       %s %s       %s %s %s      %s %s %s      %s %s %s      %s %s %s     %s"
	    . "        %dB, %dD, %dS\n", $name,
	                                  $nili{"dinner-07-04"}?"D":" ", $nili{"sleeping-07-04"}?"S":" ",
	$nili{"breakfast-07-05"}?"B":" ", $nili{"dinner-07-05"}?"D":" ", $nili{"sleeping-07-05"}?"S":" ",
	$nili{"breakfast-07-06"}?"B":" ", $nili{"dinner-07-06"}?"D":" ", $nili{"sleeping-07-06"}?"S":" ",
	$nili{"breakfast-07-07"}?"B":" ", $nili{"dinner-07-07"}?"D":" ", $nili{"sleeping-07-07"}?"S":" ",
	$nili{"breakfast-07-08"}?"B":" ", $nili{"dinner-07-08"}?"D":" ", $nili{"sleeping-07-08"}?"S":" ",
	$nili{"breakfast-07-09"}?"B":" ",


		    $nili{"breakfast-07-05"} +
		    $nili{"breakfast-07-06"} +
		    $nili{"breakfast-07-07"} +
		    $nili{"breakfast-07-08"} +
		    $nili{"breakfast-07-09"},

		    $nili{"dinner-07-04"} +
		    $nili{"dinner-07-05"} +
		    $nili{"dinner-07-06"} +
		    $nili{"dinner-07-07"} +
		    $nili{"dinner-07-08"},

		    $nili{"sleeping-07-04"} +
		    $nili{"sleeping-07-05"} +
		    $nili{"sleeping-07-06"} +
		    $nili{"sleeping-07-07"} +
		    $nili{"sleeping-07-08"};

    }
    print "\nSummary:\n";
    foreach $name (('04','05','06','07','08','09')) {
	printf "  %s.07.2001\n", $name;
	printf "    Breakfast: %d\n", $res{"breakfast-07-$name"} if ($res{"breakfast-07-$name"} > 0);
	printf "    Dinner ..: %d\n", $res{"dinner-07-$name"} if ($res{"dinner-07-$name"} > 0);
	printf "    Sleeping : %d\n", $res{"sleeping-07-$name"} if ($res{"sleeping-07-$name"} > 0);
    }
}

sub nili_details
{
    my $query;
    my $sth;
    my $rc;
    my @row;
    my $date;

    $dbh->do("SET DateStyle = 'ISO'"); 

    $query = "SELECT nili.person,person.email,nili.date,nili.breakfast,nili.dinner,nili.sleeping,nili.comment "
	    ."FROM nili,person "
	    ."WHERE person.name = nili.person AND person.nightplace = 'Nili' ORDER BY nili.person,nili.date";

    $sth = $dbh->prepare($query);
    if ($sth && ($rc = $sth->execute) > 0) {
        while (@row = $sth->fetchrow_array) {
	    print  "<tag>\n";
	    printf "%sDatum: %s\n", $opt_nospace?"":"  ", (split(/ /,$row[2]))[0];
	    printf "%sName: %s\n", $opt_nospace?"":"  ", $row[0];
	    printf "%sE-Mail: %s\n", $opt_nospace?"":"  ", $row[1];
	    printf "%sFrühstück: %s\n", $opt_nospace?"":"  ", $row[3]==1?"ja":"nein";
	    printf "%sAbendessen: %s\n", $opt_nospace?"":"  ", $row[4]==1?"ja":"nein";
	    printf "%sÜbernachtung: %s\n", $opt_nospace?"":"  ", $row[5]==1?"ja":"nein";
	    printf "%sBemerkung: %s\n", $opt_nospace?"":"  ", $row[6] if (length($row[6]));
	    print  "</tag>\n\n";
	}
    }
}

sub centerstring
{
    my $len = shift;
    my $foo = shift;
    my $half = int (($len - length ($foo))/2);
    my $bar = $half*2+length ($foo) < $len?1:0;

    return " " x $half . $foo . " " x $half . " " x $bar;
}

sub workshops
{
    my $query;
    my $sth;
    my $rc;
    my @row;
    my $date;
    my $time;
    my $room;
    my %length;
    my $line;

    $dbh->do("SET DateStyle = 'ISO'"); 

    $query = "SELECT room,date_time,title,speaker FROM workshop";

    $sth = $dbh->prepare($query);
    if ($sth && ($rc = $sth->execute) > 0) {
        while (@row = $sth->fetchrow_array) {
	    $row[1] =~ /(\d+-\d+-\d+) (\d+:\d+):.*/;
	    $date = $1;
	    $time = $2;
	    $schedule{$date}{$row[0]}{$time}{talk} = $row[2];
	    $schedule{$date}{$row[0]}{$time}{speaker} = $row[3];
	}
    }

    # Calculating lengths
    foreach $date (keys %schedule) {
	foreach $room (keys %{$schedule{$date}}) {
	    foreach $time (keys %{$schedule{$date}{$room}}) {
		$length{$room} = length ($schedule{$date}{$room}{$time}{talk})
		    if (length ($schedule{$date}{$room}{$time}{talk}) > $length{$room});
		$length{$room} = length ($schedule{$date}{$room}{$time}{speaker})
		    if (length ($schedule{$date}{$room}{$time}{speaker}) > $length{$room});
	    }
	}
    }

    $line = sprintf ("-----+-%s-+-%s-\n", "-" x $length{"CCA III"}, "-" x $length{"CCA IV"});

    foreach $date (('2001-07-05','2001-07-06','2001-07-07','2001-07-08')) {
	printf "\n%s\n%s\n\n", $date, "=" x length($date);
	printf "Time   %s   %s\n", centerstring ($length{"CCA III"}, "CCA III"), centerstring ($length{"CCA IV"}, "CCA IV");

	foreach $time (('10:00','11:00','12:00','13:00','14:00','15:00','16:00','17:00')) {
	    print  $line if ($time eq "10:00" || !$opt_nospace);
	    printf "%s| %s | %s\n", $time,
	        centerstring ($length{"CCA III"}, $schedule{$date}{"CCA III"}{$time}{talk}),
	        centerstring ($length{"CCA IV"}, $schedule{$date}{"CCA IV"}{$time}{talk});
	    printf "%s| %s | %s\n", "     ",
	        centerstring ($length{"CCA III"}, $schedule{$date}{"CCA III"}{$time}{speaker}),
	        centerstring ($length{"CCA IV"}, $schedule{$date}{"CCA IV"}{$time}{speaker});
	}
    }
}

sub latex_workshops
{
    my $query;
    my $sth;
    my $rc;
    my @row;
    my $date;
    my $time;
    my $room;
    my %length;
    my $line;

    $dbh->do("SET DateStyle = 'ISO'"); 

    $query = "SELECT room,date_time,title,speaker FROM workshop";

    $sth = $dbh->prepare($query);
    if ($sth && ($rc = $sth->execute) > 0) {
        while (@row = $sth->fetchrow_array) {
	    $row[1] =~ /(\d+-\d+-\d+) (\d+:\d+):.*/;
	    $date = $1;
	    $time = $2;
	    $row[2] =~ s/#/\\#/g;
	    $row[2] =~ s/&/\\&/g;
	    $row[3] =~ s/#/\\#/g;
	    $row[3] =~ s/&/\\&/g;
	    $schedule{$date}{$row[0]}{$time}{talk} = $row[2];
	    $schedule{$date}{$row[0]}{$time}{speaker} = $row[3];
	}
    }

    #print latex_head ();
    print $latex_lthead;
    foreach $date (keys %datum) {

	#printf $latex_headline, $datum{$date};
	printf "\\headline{Workshops}{%s}\n\n\\vspace{1cm}", $date;
	printf "\\textbf{\\Large %s}\n\n", $datum{$date};

	printf '\begin{tabular}{l||c|c}'.
	       '\textbf{Zeit} &\parbox{6.5cm}{\centerline{\textbf{CCA III}}} '.
		   '&\parbox{6.5cm}{\centerline{\textbf{CCA IV}}}\\\\\hline\hline';

	foreach $time (('10:00','11:00','12:00','13:00','14:00','15:00','16:00','17:00')) {

	    printf "\\textbf{%s} & %s & %s\\\\", $time,
	           $schedule{$date}{"CCA III"}{$time}{talk},
	           $schedule{$date}{"CCA IV"}{$time}{talk};

	    printf "\\ & %s & %s\\\\\\hline",
	           $schedule{$date}{"CCA III"}{$time}{speaker},
	           $schedule{$date}{"CCA IV"}{$time}{speaker};
	}
	print '\end{tabular}';
	print "\\clearpage\n";
    }
    print $latex_foot;
}

sub exportlt
{
    my $query = "SELECT name,boothnr,url FROM project";
    my $sth;
    my $rc;
    my @row;
    my $nr = 0;

    $query .= " WHERE boothnr <> ''" if ($opt_onlybooth);
    $query .= " ORDER BY name";
    $sth = $dbh->prepare($query);
    if ($sth && ($rc = $sth->execute) > 0) {
        while (@row = $sth->fetchrow_array) {
	    printf "INSERT INTO exh_profile (name,booth) VALUES ('%s','%s');\n", $row[0], $row[1];
	    printf "INSERT INTO exh_addresses (url) VALUES ('%s');\n", $row[2] if (length ($row[2]));
        }
    }
}

sub phone
{
    my $query = "SELECT name,comment,project FROM person WHERE comment <> '' ORDER BY name";
    my $sth;
    my $rc;
    my @row;
    my $nr = 0;

    $sth = $dbh->prepare($query);
    if ($sth && ($rc = $sth->execute) > 0) {
        while (@row = $sth->fetchrow_array) {
	    if ($row[1] =~ /.*(\+\d+ \d+ \d+).*/) {
		printf "%-30s %s\n", $row[0], $1;
	    }
        }
    }
}

sub help
{
    print "ltp [options] [commands]\n";
    print "Options:\n";
    print "   --cols <n>         Text will be wrapped at n columns\n";
    print "   --nospace          No leading spaces\n";
    print "   --onlybooth        Only projects with a non-empty boothnr\n";
    print "Commands:\n";
    print "   --help             This help\n";
    print "   --descr            List project descriptions\n";
    print "   --descr-miss       List projects and addrs that lack a description\n";
    print "   --descrweb         Export HTML junk for www.linuxtag.org (add en)\n";
    print "   --exportlt         Export for LT database\n";
    print "   --list|-l          List all projects\n";
    print "   --mails            List mail addresses of all projects\n";
    print "   --members          LaTeX file with all project members\n";
    print "   --nili             Table for Nili accommodation\n";
    print "   --nili-details     Details for Nili accommodation\n";
    print "   --night [<place>]  List people that need a place to stay\n";
    print "   --phone            Extract phone numbers\n";
    print "   --supplies         List supplies for booths\n";
    print "   --titles           List booth titles\n";
    print "   --table            LaTeX table with all projects\n";
    print "   --workshops        Workshop Schedule\n";
    print "   --workshops.tex    Workshop Schedule for LaTeX\n";

}

# First check our arguments
#
$argc = 0;
$opt_nospace = 0;
$opt_onlybooth = 0;
$opt_cols = 70;
while ($argc <= $#ARGV) {
    if ($ARGV[$argc] eq "-h" || $ARGV[$argc] eq "--help") {
	help ();
        exit;
    } elsif ($ARGV[$argc] eq "--cols") {
	if (($argc < $#ARGV) && ($ARGV[$argc+1] !~ /^-/)) {
	    $opt_cols = $ARGV[++$argc];
	}
    } elsif ($ARGV[$argc] eq "--list" || $ARGV[$argc] eq "-l") {
        list ();
    } elsif ($ARGV[$argc] eq "--descr") {
	descr ();
    } elsif ($ARGV[$argc] eq "--descrweb") {
	descrweb ();
    } elsif ($ARGV[$argc] eq "--descrweben") {
	descrweb ('en');
    } elsif ($ARGV[$argc] eq "--descr-miss" || $ARGV[$argc] eq "--missing-descr") {
	missing_descr ();
    } elsif ($ARGV[$argc] eq "--exportlt") {
	exportlt ();
    } elsif ($ARGV[$argc] eq "--mails") {
	list_mails ('');
    } elsif ($ARGV[$argc] eq "--members") {
	latex_members ('');
    } elsif ($ARGV[$argc] eq "--nili") {
	nili ();
    } elsif ($ARGV[$argc] eq "--nili-details") {
	nili_details ();
    } elsif ($ARGV[$argc] eq "--night") {
	if (($argc < $#ARGV) && ($ARGV[$argc+1] !~ /^-/)) {
	    night ($ARGV[++$argc]);
	} else {
	    night ();
	}
    } elsif ($ARGV[$argc] eq "--nospace") {
        $opt_nospace++;
    } elsif ($ARGV[$argc] eq "--onlybooth") {
        $opt_onlybooth++;
    } elsif ($ARGV[$argc] eq "--phone") {
	phone ();
    } elsif ($ARGV[$argc] eq "--supplies") {
	supplies ();
    } elsif ($ARGV[$argc] eq "--table") {
	latex_table ();
    } elsif ($ARGV[$argc] eq "--titles") {
	list_titles ();
    } elsif ($ARGV[$argc] eq "--workshops") {
	workshops ();
    } elsif ($ARGV[$argc] eq "--workshops.tex") {
	latex_workshops ();
    }
    $argc++;
}