#!/usr/bin/perl
# igc.woa
# copyright hot24 computer technologies gmbh - martin beierl, liniengasse 46, a-1060 vienna
# Bem. R.Hu. 24.01.2022 "sub list_witness ab Zeile 1937" 

use CGI qw(:standard);
use CGI::Cookie;
use DBI;
use strict;
use Digest::MD5 qw(md5 md5_hex md5_base64);
use MIME::Lite;
#use MIME::Base64;
#use Authen::SASL;
#use Net::SMTP::TLS;
use Time::Local;
use vars qw($query $DBH $sid $page_size);
use constant COOKIE_NAME => 'SessionID';

$query = new CGI;
$page_size = 20;

$DBH = DBI->connect("dbi:mysql:baumann:localhost:3306","baumi","GottLieb#2019"), {PrintError => 0}
    || die "Couldn't open database: " . $DBI::errstr;

#$DBH->do(qq{SET NAMES 'latin1';});

my %Routine = (
	admin						=> \&admin,
	search					=> \&search,
	search_admin		=> \&search_admin,
	rating					=> \&rating,
	show						=> \&show,
	show_admin			=> \&show_admin,
	show_detail			=> \&show_detail,
	show_detail_admin	=> \&show_detail_admin,
	show_detail_user	=> \&show_detail_user,
	show_pict				=> \&show_pict,
	user_reg				=> \&user_reg,
	user_check			=> \&user_check,
	user_save				=> \&user_save,
	user_save_admin	=> \&user_save_admin,
	user_delete			=> \&user_delete,
	list_witness		=> \&list_witness,
	login						=> \&login,
	do_login				=> \&do_login,
	login_admin			=> \&login_admin,
	do_login_admin	=> \&do_login_admin,
	logout					=> \&logout,
	announce				=> \&announce,
	announce_admin			=> \&announce_admin,
	error_mod				=> \&error_mod,
	error_igc				=> \&error_igc,
	passwort				=> \&passwort,
	passwort_send		=> \&passwort_send,
	passwort_sent		=> \&passwort_sent,
	passwort_error	=> \&passwort_error,
	igc_check				=> \&igc_check,
	igc_check_admin			=> \&igc_check_admin,
	igc_save				=> \&igc_save,
	igc_save_admin			=> \&igc_save_admin,
	igc_saved				=> \&igc_saved,
	igc_accept			=> \&igc_accept,
	igc_publish			=> \&igc_publish,
	igc_delete			=> \&igc_delete,
	do_import			=> \&do_import
);

my $cmd = $query->param('cmd');

# Wartung
# if ($cmd ne "do_import") {
# 	&wartung();
# 	exit;
# }

if (defined $Routine{$cmd}) {
		&{$Routine{$cmd}};
} else {
	&search();
}

$DBH->disconnect;

sub do_import_off() {
	my $filename = $ENV{DOCUMENT_ROOT} . "/igc/abzeichen.csv";
	my $count = 0;
	my $pagestr = "";

	open (FILE, "<", $filename) or die "Fehler beim Lesen der Datei $filename!\n";
	
	while (<FILE>) {
		chomp;
		my ($lizenz, $nachname, $vorname, $geb_datum, $flight_subgroup, $date_flight, $reg_id) = split(";", $_);
		
		$pagestr .= $lizenz . "; " . $nachname . "; " . $vorname . "; ";
		
		$nachname = &uft2db($nachname);
		$vorname = &uft2db($vorname);
		$geb_datum = &date_to_database($geb_datum);
		$date_flight = &date_to_database($date_flight);
		
		my $user_id = 0;
		
		my $sth = $DBH->prepare("SELECT user_id,lizenz FROM user WHERE lizenz = '$lizenz'");
		$sth->execute;
		my $hash_ref = $sth->fetchrow_hashref;
		my $lizenz_exists = $hash_ref->{lizenz};
		$user_id = $hash_ref->{user_id};
		$sth->finish;
		
		if ($lizenz_exists ne $lizenz) {
			$DBH->do("INSERT INTO user (lizenz, nachname, vorname, geb_datum, plz, ort, strasse, passwort, email) VALUES ('$lizenz', '$nachname', '$vorname', '$geb_datum', '', '', '', '', '')");
			
			$sth = $DBH->prepare("SELECT MAX(user_id) FROM user WHERE lizenz='$lizenz'");
			$sth->execute;
			$user_id = $sth->fetchrow_array;
			$sth->finish;
			
			$count ++;
		}
		
		$DBH->do("INSERT INTO igc (user_id, flight_subgroup, date_flight, reg_id, file, date_uploaded, date_reg, airplane_type, witness, recognized) VALUES ('$user_id', '$flight_subgroup', '$date_flight', '$reg_id', '00000000.igc', '', '', '', '', '1')");
		
		$geb_datum = &date_to_display($geb_datum);
		$date_flight = &date_to_display($date_flight);
		
		$pagestr .= $geb_datum . " / " . $user_id . "; " . $flight_subgroup . "; " . $date_flight . "; " . $reg_id . "<br />";
	}
	
	$pagestr .= "<br />" . $count . " User importiert!";
	
	close (FILE);
		
	$query->print($query->header(-cookie=>save_state($sid),-expires=>'-1d', -content=>'text/html', -charset=>'utf-8'));
	$query->print($pagestr);
}

sub admin() {
	# pruefe ob eingeloggt, sonst login ueber routine auth_ok
	my $license = &admin_ok();

	my $user_id = $query->param('user_id');
	my $pos = (defined $query->param('pos')) ? $query->param('pos') : '0';
	my $page_str = &file_to_string("templates/list_admin.html");
	my $count = 0;
	
	my $sth = $DBH->prepare("SELECT count(*) FROM igc WHERE recognized='0'");
	$sth->execute;
	my $count = $sth->fetchrow_array;
	
	if ($count == 0) { 
		$page_str =~ s/\[page_bar\]//g;
		$page_str =~ s/\[page_dn\]//g;
		$page_str =~ s/\[page_up\]//g;
	}
	
	my $page_bar = sprintf("%d - %d von %d", $pos+1, &min($pos+$page_size, $count), $count);
	$page_str =~ s/\[page_bar\]/$page_bar/g;

	my $down_pos = &max(0, $pos - $page_size);
	my $up_pos = &min($pos + $page_size, $count);

	my $page_dn = ($pos - $page_size >= 0) ? "<A HREF=\"igc.woa?cmd=show&user_id=$user_id&pos=$down_pos\"><IMG SRC=\"/igc/images/left.gif\" ALT=\"\" WIDTH=\"8\" HEIGHT=\"16\" BORDER=\"0\"></A>" : "<IMG SRC=\"/igc/images/left_off.gif\" ALT=\"\" WIDTH=\"8\" HEIGHT=\"16\" BORDER=\"0\">";
	$page_str =~ s/\[page_dn\]/$page_dn/g;
	my $page_up = ($pos + $page_size <$count) ? "<A HREF=\"igc.woa?cmd=show&user_id=$user_id&pos=$up_pos\"><IMG SRC=\"/igc/images/right.gif\" ALT=\"\" WIDTH=\"8\" HEIGHT=\"16\" BORDER=\"0\"></A>" : "<IMG SRC=\"/igc/images/right_off.gif\" ALT=\"\" WIDTH=\"8\" HEIGHT=\"16\" BORDER=\"0\">";
	$page_str =~ s/\[page_up\]/$page_up/g;

	$sth = $DBH->prepare("SELECT * FROM igc WHERE recognized='0' LIMIT $pos," . $page_size);
	$sth->execute;

	my $sth1 = $DBH->prepare("SELECT * FROM user WHERE user_id='$user_id'");
	$sth1->execute;
	my $hash_ref1 = $sth1->fetchrow_hashref;
	my $name = $hash_ref1->{nachname} . ' ' . $hash_ref1->{vorname};
	$sth1->finish;
	
	$page_str =~ s/\[name\]/$name/g;
	$page_str =~ s/\[pos\]/$pos/g;

	my $list_str = '<TABLE WIDTH="600" CELLSPACING="0" CELLPADDING="3" BORDER="0">';

	my $row_bg = 0;
	my $hash_ref;
	my $length;
	
	while ($hash_ref = $sth->fetchrow_hashref) {
		#my $user_id = $hash_ref->{user_id};
		my $file = $hash_ref->{file};
		my $id = $hash_ref->{id};
		
# Color the Backgroud Cell
		my $tdstyle = ($row_bg++%2 eq 0) ? "#EDF6FF" : "#CDE6FF";
		my $local_row = "<TR><TD WIDTH=\"90\" BGCOLOR=\"[tdstyle]\" CLASS=\"pagelist\">[date]</TD>
		<TD WIDTH=\"240\" BGCOLOR=\"[tdstyle]\" CLASS=\"pagelist\">[flugart]</TD>
		<TD BGCOLOR=\"[tdstyle]\" CLASS=\"pagelist\">[airplane]</TD>
		<TD WIDTH=\"16\" BGCOLOR=\"[tdstyle]\" ALIGN=\"right\" CLASS=\"pagelist\"><A HREF=\"igc.woa?cmd=igc_delete&id=$id\" onClick=\"return confirm('Sind sie sicher, diesen Flug vom [date] zu l&ouml;schen?')\"><IMG SRC=\"/igc/images/delete.gif\" ALT=\"\" WIDTH=\"16\" HEIGHT=\"16\" BORDER=\"0\"></A></TD>
		<TD WIDTH=\"3\" BGCOLOR=\"[tdstyle]\" ALIGN=\"right\" CLASS=\"pagelist\"><IMG SRC=\"/igc/images/leer.gif\" ALT=\"\" WIDTH=\"3\" HEIGHT=\"1\" BORDER=\"0\"></TD>
		<TD WIDTH=\"16\" BGCOLOR=\"[tdstyle]\" ALIGN=\"right\" CLASS=\"pagelist\"><A HREF=\"igc.woa?cmd=show_detail_admin&id=$id\"><IMG SRC=\"/igc/images/info.gif\" ALT=\"\" WIDTH=\"16\" HEIGHT=\"16\" BORDER=\"0\"></A></TD>
		<TD WIDTH=\"3\" BGCOLOR=\"[tdstyle]\" ALIGN=\"right\" CLASS=\"pagelist\"><IMG SRC=\"/igc/images/leer.gif\" ALT=\"\" WIDTH=\"3\" HEIGHT=\"1\" BORDER=\"0\"></TD></TR>";
		
		#my $year = "20" . &igc_value(substr($file,0,1));
		#my $month = &igc_value(substr($file,1,1));
		#my $day = &igc_value(substr($file,2,1));
		#my $date = $day . "." . $month . "." . $year;
		#my $hersteller = &igc_logger(substr($file,3,1));
		#my $flights = &igc_value(substr($file,7,1));
		my $date = &get_igc_date($id);
		$date = &date_to_display($date);
		
		$sth1 = $DBH->prepare("SELECT * FROM igc WHERE id='$id'");
		$sth1->execute;
		my $hash_ref1 = $sth1->fetchrow_hashref;
		my $flight_subgroup = $hash_ref1->{flight_subgroup};
		my $airplane_type = $hash_ref1->{airplane_type};
		$sth1->finish;
		
		$sth1 = $DBH->prepare("SELECT * FROM flight_subgroup WHERE id='$flight_subgroup'");
		$sth1->execute;	
		$hash_ref1 = $sth1 ->fetchrow_hashref;
		my $group_id = $hash_ref1->{group_id};
		my $type = $hash_ref1->{type};
		$sth1	->finish;
		
		$sth1 = $DBH->prepare("SELECT * FROM flight_group WHERE id='$group_id'");
		$sth1->execute;	
		$hash_ref1 = $sth1 ->fetchrow_hashref;
		$type = $hash_ref1->{type} . ": " . $type;
		$sth1	->finish;
		
		$sth1 = $DBH->prepare("SELECT * FROM airplane_type WHERE id='$airplane_type'");
		$sth1->execute;	
		$hash_ref1 = $sth1 ->fetchrow_hashref;
		my $airplane = $hash_ref1->{type};
		$sth1	->finish;
		
		$local_row =~ s/\[tdstyle\]/$tdstyle/g;
		$local_row =~ s/\[file\]/$hash_ref->{file}/g;
		$local_row =~ s/\[date\]/$date/g;
		#$local_row =~ s/\[hersteller\]/$hersteller/g;
		$local_row =~ s/\[flugart\]/$type/g;
		$local_row =~ s/\[airplane\]/$airplane/g;
		#$local_row =~ s/\[flights\]/$flights/g;
		
		$list_str .= $local_row;
	}

	if ($count == 0) {
# Color the Backgroud Cell
		my $tdstyle = ($row_bg++%2 eq 0) ? "#EDF6FF" : "#CDE6FF";
		my $local_row = "<TR><TD COLSPAN=\"7\" BGCOLOR=\"[tdstyle]\" CLASS=\"pagelist\">Keine Fl&uuml;ge vorhanden!</TD></TR>";
		$local_row =~ s/\[tdstyle\]/$tdstyle/g;
		$list_str .= $local_row;
	}

	while ($row_bg < $page_size) {
# Color the Backgroud Cell
		my $tdstyle = ($row_bg++%2 eq 0) ? "#EDF6FF" : "#CDE6FF";
		my $local_row = "<TR><TD COLSPAN=\"7\" BGCOLOR=\"[tdstyle]\" CLASS=\"pagelist\">&nbsp;</TD></TR>";
		$local_row =~ s/\[tdstyle\]/$tdstyle/g;
		$list_str .= $local_row;
	}
	
	$list_str .= "</TABLE>";
	$page_str =~ s/\[igc table\]/$list_str/g;

	#$query->print( $query->header(-cookie=>save_state($sid),-expires=>'-1d'));
	$query->print($query->header(-expires=>'-1d'));
	$query->print($page_str);
	$sth->finish;
}

sub user_reg() {
	my ($errorcount,$error_vorname,$error_nachname,$error_plz,$error_ort,$error_strasse,$error_geb_datum,$error_lizenz,$error_passwort,$error_email,$error_tel) = 0;
	($errorcount,$error_vorname,$error_nachname,$error_plz,$error_ort,$error_strasse,$error_geb_datum,$error_lizenz,$error_passwort,$error_email,$error_tel) = @_;
	
	my $page_str = &file_to_string("templates/user_reg.html");
	
	my $vorname = $query->param('vorname');
	my $nachname = $query->param('nachname');
	my $plz = $query->param('plz');
	my $ort = $query->param('ort');
	my $strasse = $query->param('strasse');
	my $geb_datum = defined ($query->param('geb_datum')) ? $query->param('geb_datum') : '31.12.2099';
	my $lizenz = $query->param('lizenz');
	my $passwort = $query->param('passwort');
	my $email = defined ($query->param('email')) ? $query->param('email') : 'name@domain.at';
	my $tel = $query->param('tel');
	
	my $focus_geb_datum = "onfocus=\"javascript:if(this.value=='$geb_datum') {this.value='';}\" onblur=\"javascript:if(this.value=='') {this.value='$geb_datum'}\"";
	my $focus_email = "onfocus=\"javascript:if(this.value=='$email') {this.value='';}\" onblur=\"javascript:if(this.value=='') {this.value='$email'}\"";
	
	$page_str =~ s/\[vorname\]/$vorname/g;
	$page_str =~ s/\[nachname\]/$nachname/g;
	$page_str =~ s/\[plz\]/$plz/g;
	$page_str =~ s/\[ort\]/$ort/g;
	$page_str =~ s/\[strasse\]/$strasse/g;
	$page_str =~ s/\[geb_datum\]/$geb_datum/g;
	$page_str =~ s/\[lizenz\]/$lizenz/g;
	$page_str =~ s/\[passwort\]/$passwort/g;
	$page_str =~ s/\[email\]/$email/g;
	$page_str =~ s/\[tel\]/$tel/g;
	
	if ($geb_datum eq '31.12.2099') { $page_str =~ s/\[focus_geb_datum\]/$focus_geb_datum/g; }
	else { $page_str =~ s/\[focus_geb_datum\]//g; }
	
	if ($email eq 'name@domain.at') { $page_str =~ s/\[focus_email\]/$focus_email/g; }
	else { $page_str =~ s/\[focus_email\]//g; }
	
	if($errorcount !~ /[^0-9]/) {
		if ($errorcount eq 1) { $page_str =~ s/\[errormsg\]/$errorcount rot markiertes Feld hat Fehler!/g; }
		elsif ($errorcount gt 1) { $page_str =~ s/\[errormsg\]/$errorcount rot markierte Felder haben Fehler!/g; }
		else { $page_str =~ s/\[errormsg\]//g; }
	} else {
		{ $page_str =~ s/\[errormsg\]//g; }
	}
	
	if ($error_vorname eq 1) { $page_str =~ s/\[class_vorname\]/error/g; } else { $page_str =~ s/\[class_vorname\]/td/g; }
	if ($error_nachname eq 1) { $page_str =~ s/\[class_nachname\]/error/g; } else { $page_str =~ s/\[class_nachname\]/td/g; }
	if ($error_plz eq 1) { $page_str =~ s/\[class_plz\]/error/g; } else { $page_str =~ s/\[class_plz\]/td/g; }
	if ($error_ort eq 1) { $page_str =~ s/\[class_ort\]/error/g; } else { $page_str =~ s/\[class_ort\]/td/g; }
	if ($error_strasse eq 1) { $page_str =~ s/\[class_strasse\]/error/g; } else { $page_str =~ s/\[class_strasse\]/td/g; }
	if ($error_geb_datum eq 1) { $page_str =~ s/\[class_geb_datum\]/error/g; } else { $page_str =~ s/\[class_geb_datum\]/td/g; }
	if ($error_lizenz eq 1) { $page_str =~ s/\[class_lizenz\]/error/g; } else { $page_str =~ s/\[class_lizenz\]/td/g; }
	if ($error_passwort eq 1) { $page_str =~ s/\[class_passwort\]/error/g; } else { $page_str =~ s/\[class_passwort\]/td/g; }
	if ($error_email eq 1) { $page_str =~ s/\[class_email\]/error/g; } else { $page_str =~ s/\[class_email\]/td/g; }
	if ($error_tel eq 1) { $page_str =~ s/\[class_tel\]/error/g; } else { $page_str =~ s/\[class_tel\]/td/g; }
	
	$query->print($query->header(-expires=>'-1d'));
	$query->print($page_str);
}

sub user_check() {
	my $vorname = $query->param('vorname');
	my $nachname = $query->param('nachname');
	my $plz = $query->param('plz');
	my $ort = $query->param('ort');
	my $strasse = $query->param('strasse');
	my $geb_datum = $query->param('geb_datum');
	my $lizenz = $query->param('lizenz');
	my $passwort = $query->param('passwort');
	my $email = $query->param('email');
	my $tel = $query->param('tel');
	
	my ($reg_error,$errorcount,$error_vorname,$error_nachname,$error_plz,$error_ort,$error_strasse,$error_geb_datum,$error_lizenz,$error_passwort,$error_email,$error_tel) = 0;

	if (&check_name($vorname) eq 0) { $errorcount +=1; $error_vorname = 1; }
	if (&check_name($nachname) eq 0) { $errorcount +=1; $error_nachname = 1; }
	if (&check_plz($plz) eq 0) { $errorcount +=1; $error_plz = 1; }
	if (&check_name($ort) eq 0) { $errorcount +=1; $error_ort = 1; }
	if (&check_field($strasse) eq 0) { $errorcount +=1; $error_strasse = 1; }
	if (&check_datum($geb_datum) eq 0) { $errorcount +=1; $error_geb_datum = 1; }
	if (&check_passwort($passwort) eq 0) { $errorcount +=1; $error_passwort = 1; }
	
	# prüfen ob lizenz bereits existiert
	if (&check_passwort($lizenz) eq 1) {
		my $sth = $DBH->prepare("SELECT * FROM user WHERE lizenz LIKE '$lizenz'");
		$sth->execute;
		if ($sth->rows gt 0) {
			my $error = "Die eingegebene Lizenznummer existiert bereits, loggen sie sich ein!";
			$query->print( $query->redirect(-location=>"igc.woa?cmd=error_mod&error=$error&path=login") );
			exit;
		}
		$sth->finish;
	} else { $errorcount +=1; $error_lizenz = 1; }
	
	# prüfen ob email adresse bereits in anderem datensatz existiert, soferne nicht leer
	if ($email gt "") {
		if (&check_email($email) eq 1) {
			my $sth = $DBH->prepare("SELECT * FROM user WHERE email LIKE '$email'");
			$sth->execute;
			if ($sth->rows gt 0) {
				my $error = "Die eingegebene Email-Adresse existiert bereits, loggen sie sich ein!";
				$query->print( $query->redirect(-location=>"igc.woa?cmd=error_mod&error=$error&path=login") );
				exit;
			}
			$sth->finish;
		} else { $errorcount +=1; $error_email = 1; }
	}
	
	# prüfen ob nachname, vorname und plz bereits in anderem datensatz existiert
	if ($vorname ne "" && $nachname ne "" && $plz ne "") {
		my $sth1 = $DBH->prepare("SELECT * FROM user WHERE vorname LIKE '$vorname' AND nachname LIKE '$nachname' AND plz LIKE '$plz'");
		$sth1->execute;
		my $hash_ref = $sth1->fetchrow_hashref;
		if ($sth1->rows gt 0) {
			$lizenz = $hash_ref->{lizenz};
			my $error = "Der eingegebene Name existiert bereits in diesem Ort, loggen sie sich ein!";
			$query->print( $query->redirect(-location=>"igc.woa?cmd=error_mod&error=$error&path=login") );
			exit;
		}
		$sth1->finish;
	}
	
	if ($reg_error eq 0) {
		if ($errorcount gt 0) {
			&user_reg($errorcount,$error_vorname,$error_nachname,$error_plz,$error_ort,$error_strasse,$error_geb_datum,$error_lizenz,$error_passwort,$error_email,$error_tel);
		} else { &user_save(); }	
	}
}

sub user_save() {
	my $vorname = $query->param('vorname');
	my $nachname = $query->param('nachname');
	my $plz = $query->param('plz');
	my $ort = $query->param('ort');
	my $strasse = $query->param('strasse');
	my $geb_datum = &date_to_database($query->param('geb_datum'));
	my $lizenz = $query->param('lizenz');
	my $passwort = $query->param('passwort');
	my $email = $query->param('email');
	my $tel = $query->param('tel');
	
	if ($email eq 'name@domain.at') { $email =""; }
	
	my $sth = $DBH->prepare("SELECT max(user_id) FROM user");
	$sth->execute;	
	my $user_id = $sth->fetchrow_array + 1;
	$sth->finish;
	
	$sth = $DBH->prepare("INSERT INTO user (user_id,vorname,nachname,plz,ort,strasse,geb_datum,lizenz,passwort,email,tel) VALUES ('$user_id','$vorname','$nachname','$plz','$ort','$strasse','$geb_datum','$lizenz','$passwort','$email','$tel')");
	$sth->execute();
	$sth->finish;
	
	if ($email gt "") {



# 		my $mailer = new Net::SMTP::TLS( 'kunde5.selfnet.at',
# 				Hello   =>      'mail.kias.at',
# 				Port    =>      25,
# 				User    =>      'presse@dans.kias.at',
# 				Password=>      'Öffentlichkeitsarbeit76');
# 
# 		my $email1 = $hash_ref->{email1};
# 				my $email2 = $hash_ref->{email2};
# 				my $subject = $hash_ref->{"subject_" . $client_language};
# 				my $content = &get_html_content($newsID,$client_language,$email1);
# 				
# 				my $mctype = "Content-type: text/html\n\n";
# 				my $msubject = "Subject: $subject\n";
# 				my $mfrom = "From: \"DANS.KIAS Newsletter <$sender>\n";
# 				my $mto1 = "To: \"$email1\n";
# 				my $mheaders = $mfrom . $mto1 . $msubject . $mctype . "\n";
# 				my $memail = $mheaders . $content;
# 				
# 				$mailer->mail("DANS.KIAS Newsletter <$sender>");
# 				$mailer->to("$email1");
# 				$mailer->data;
# 				$mailer->datasend("$memail");
# 				$mailer->dataend;
# 				$mailer->quit;


		my $sender = 'info@onf-online.at';
		my $subject = "Registrierungs Bestaetigung";
		my $content = "Sehr geehrte/r $vorname $nachname!\n
Danke, dass Sie sich in der IGC-Datenbank registriert haben!\n
Bitte bewahren sie die folgenden Login-Daten auf:
   Lizenznummer:   $lizenz
   Passwort:       $passwort
   E-Mail-Adresse: $email
   Telefonnummer:  $tel\n

Wir wünschen ihnen viel Erfolg!\n
http://www.onf-online.at/";

		my $msg = MIME::Lite->new(From =>"IGC-Datenbank <$sender>", To => $email, Subject =>"$subject", Type =>'multipart/mixed');
		$msg->attach(Type =>'text/plain; charset=iso-8859-1', Data =>"$content" );
		$msg->send('smtp','smtp.hot24.at', SSL => 1, Port => 25, AuthUser=>'info@onf-online.at', AuthPass=>'5SC7tpW#mk');
	}
	
	$sid = &generate_id($lizenz);
	$query->print( $query->redirect(-cookie=>save_state($sid),-location=>"igc.woa?cmd=announce&lizenz=$lizenz") );
}

sub user_save_admin() {
	my $user_id = $query->param('id');
	my $vorname = $query->param('vorname');
	my $nachname = $query->param('nachname');
	my $plz = $query->param('plz');
	my $ort = $query->param('ort');
	my $strasse = $query->param('strasse');
	my $geb_datum = &date_to_database($query->param('geb_datum'));
	my $lizenz = $query->param('lizenz');
	my $passwort = $query->param('passwort');
	my $email = $query->param('email');
	my $tel = $query->param('tel');
	
	my $sth = $DBH->prepare("UPDATE user SET vorname='$vorname',nachname='$nachname',plz='$plz',ort='$ort',strasse='$strasse',geb_datum='$geb_datum',lizenz='$lizenz',passwort='$passwort',email='$email',tel='$tel' WHERE user_id='$user_id'");
	$sth->execute();
	$sth->finish;

	$query->print( $query->redirect(-cookie=>save_state($sid),-location=>"igc.woa?cmd=search_admin&search_name=%25") );
}

sub user_delete {
  my $user_id = defined ($query->param('id')) ? $query->param('id') : '';
	
	# pruefe ob eingeloggt, sonst login ueber routine auth_ok
	my $license = &admin_ok($user_id);
	
	my $file;
	my $file_path;
	my $id;
	my $hash_ref;
	
	my $sth = $DBH->prepare("SELECT file,id FROM igc WHERE user_id = '$user_id'");
	$sth->execute;
	while ($hash_ref = $sth->fetchrow_hashref) {
		$file = $hash_ref->{file};
		$id = $hash_ref->{id};
		$file_path = $ENV{DOCUMENT_ROOT} . "/igc/data/" . $file;
		if (-e $file_path) {
			system ("ssh root\@customer.hot24.at rm $file_path");
		}
		$file_path = $ENV{DOCUMENT_ROOT} . "/igc/data/map_" . $id . ".gif";
		if (-e $file_path) {
			system ("ssh root\@customer.hot24.at rm $file_path");
		}
		$file_path = $ENV{DOCUMENT_ROOT} . "/igc/data/baro_" . $id . ".gif";
		if (-e $file_path) {
			system ("ssh root\@customer.hot24.at rm $file_path");
		}
	}
	$sth->finish;
	
	$DBH->do("DELETE FROM igc WHERE user_id='$user_id'");
	$DBH->do("DELETE FROM user WHERE user_id='$user_id'");

	$query->print( $query->redirect(-cookie=>save_state($sid),-location=>"igc.woa?cmd=search_admin&search_name=%25") );
}

sub announce () {
	# pruefe ob eingeloggt, sonst login ueber routine auth_ok
	my $license = &auth_ok();
	
	my ($errorcount,$error_type,$error_airplane,$error_witness,$error_file) = 0;
	($errorcount,$error_type,$error_airplane,$error_witness,$error_file) = @_;
	
	my $page_str = &file_to_string("templates/announce.html");
	
	my $type = $query->param('type');
	my $airplane = $query->param('airplane');
	my $witness = $query->param('witness');
	my $file = $query->param('file');
		
	my $flug_menue = &build_flug_menu($type);
	$page_str =~ s/\[flug_menue\]/$flug_menue/g;
	
	my $diplom_menue = &build_diplom_menu($type);
	$page_str =~ s/\[diplom_menue\]/$diplom_menue/g;
	
	my $flugzeug_menue = &build_flugzeug_menu($airplane);
	$page_str =~ s/\[flugzeug_menue\]/$flugzeug_menue/g;
	
	my $witness_menue = &build_witness_menu($witness);
	$page_str =~ s/\[witness_menue\]/$witness_menue/g;
	
	$page_str =~ s/\[type\]/$type/g;
	$page_str =~ s/\[airplane\]/$airplane/g;
	$page_str =~ s/\[file\]/$file/g;
		
	if($errorcount !~ /[^0-9]/) {
		if ($errorcount eq 1) { $page_str =~ s/\[errormsg\]/$errorcount rot markiertes Feld hat Fehler!/g; }
		elsif ($errorcount gt 1) { $page_str =~ s/\[errormsg\]/$errorcount rot markierte Felder haben Fehler!/g; }
		else { $page_str =~ s/\[errormsg\]//g; }
	} else {
		{ $page_str =~ s/\[errormsg\]//g; }
	}
	
	if ($error_type eq 1) { $page_str =~ s/\[class_type\]/error/g; } else { $page_str =~ s/\[class_type\]/td/g; }
	if ($error_airplane eq 1) { $page_str =~ s/\[class_airplane\]/error/g; } else { $page_str =~ s/\[class_airplane\]/td/g; }
	if ($error_witness eq 1) { $page_str =~ s/\[class_witness\]/error/g; } else { $page_str =~ s/\[class_witness\]/td/g; }
	if ($error_file eq 1) { $page_str =~ s/\[class_file\]/error/g; } else { $page_str =~ s/\[class_file\]/td/g; }
	
	$query->print($query->header(-expires=>'-1d'));
	$query->print($page_str);
}

sub announce_admin () {
	# pruefe ob eingeloggt, sonst login ueber routine auth_ok
	my $license = &admin_ok();
	
	my ($errorcount,$error_type,$error_airplane,$error_witness,$error_date,$error_lizenz) = 0;
	($errorcount,$error_type,$error_airplane,$error_witness,$error_date,$error_lizenz) = @_;
	
	my $page_str = &file_to_string("templates/announce_admin.html");
	
	my $lizenz = $query->param('lizenz');
	my $type = $query->param('type');
	my $vorname = $query->param('vorname');
	my $nachname = $query->param('nachname');
	my $reg_id = $query->param('reg_id');
	my $date = $query->param('date');
		
	my $flug_menue = &build_flug_menu($type);
	$page_str =~ s/\[flug_menue\]/$flug_menue/g;

	$page_str =~ s/\[lizenz\]/$lizenz/g;
	$page_str =~ s/\[vorname\]/$vorname/g;
	$page_str =~ s/\[nachname\]/$nachname/g;
	$page_str =~ s/\[reg_id\]/$reg_id/g;
	$page_str =~ s/\[date\]/$date/g;
		
	if($errorcount !~ /[^0-9]/) {
		if ($errorcount eq 1) { $page_str =~ s/\[errormsg\]/$errorcount rot markiertes Feld hat Fehler!/g; }
		elsif ($errorcount gt 1) { $page_str =~ s/\[errormsg\]/$errorcount rot markierte Felder haben Fehler!/g; }
		else { $page_str =~ s/\[errormsg\]//g; }
	} else {
		{ $page_str =~ s/\[errormsg\]//g; }
	}
	
	if ($error_lizenz eq 1) { $page_str =~ s/\[class_lizenz\]/error/g; } else { $page_str =~ s/\[class_lizenz\]/td/g; }
	if ($error_type eq 1) { $page_str =~ s/\[class_type\]/error/g; } else { $page_str =~ s/\[class_type\]/td/g; }
	if ($error_date eq 1) { $page_str =~ s/\[class_date\]/error/g; } else { $page_str =~ s/\[class_date\]/td/g; }
	
	$query->print($query->header(-expires=>'-1d'));
	$query->print($page_str);
}

sub igc_check() {
	# pruefe ob eingeloggt, sonst login ueber routine auth_ok
	my $license = &auth_ok();
	
	my $type = $query->param('type');
	my $airplane = $query->param('airplane');
	my $witness = $query->param('witness');
	my $file = $query->param('file');
	$file =~ s/.*[\/\\](.*)/$1/;
	
	my ($reg_error,$errorcount,$error_type,$error_airplane,$error_witness,$error_file) = 0;
	
	if ($type == 0) { $errorcount +=1; $error_type = 1; }
	if ($airplane == 0) { $errorcount +=1; $error_airplane = 1; }
	if ($witness == 0) { $errorcount +=1; $error_witness = 1; }
	if (&check_file($file) eq 0) { $errorcount +=1; $error_file = 1; }
	
	# prüfen ob igc file bereits existiert
	#my $sth = $DBH->prepare("SELECT * FROM igc WHERE file LIKE '$file'");
	#$sth->execute;
	#if ($sth->rows gt 0) {
	#	my $error = "Die gewählte ICG-Datei existiert bereits!";
	#	$query->print( $query->redirect(-location=>"igc.woa?cmd=error_igc&error=$error&path=icg_error") );
	#	exit;
	#}
	
	if ($reg_error eq 0) {
		if ($errorcount gt 0) {
			&announce($errorcount,$error_type,$error_airplane,$error_witness,$error_file);
		} else { &igc_save(); }	
	}
}

sub igc_check_admin() {
	# pruefe ob eingeloggt, sonst login ueber routine auth_ok
	my $license = &admin_ok();
	
	my $lizenz = $query->param('lizenz');
	my $type = $query->param('type');
	my $vorname = $query->param('vorname');
	my $nachname = $query->param('nachname');
	my $reg_id = $query->param('reg_id');
	my $date = $query->param('date');
	
	my ($reg_error,$errorcount,$error_type,$error_airplane,$error_witness,$error_date,$error_lizenz) = 0;
	
	if ($type == 0) { $errorcount +=1; $error_type = 1; }
	if (!$date) { $errorcount +=1; $error_date = 1; }
	if (!$lizenz) { $errorcount +=1; $error_lizenz = 1; }

	if ($reg_error eq 0) {
		if ($errorcount gt 0) {
			&announce_admin($errorcount,$error_type,$error_date,$error_lizenz);
		} else {
			&igc_save_admin();
			#$query->print( $query->redirect(-cookie=>save_state($sid),-location=>"igc.woa?cmd=igc_save_admin&lizenz=$lizenz&type=$type&vorname=$vorname&nachname=$nachname&reg_id=$reg_id&date=$date") );
		}	
	}
}

sub igc_save() {
	# pruefe ob eingeloggt, sonst login ueber routine auth_ok
	my $license = &auth_ok();
	
	my $type = $query->param('type');
	my $airplane = $query->param('airplane');
	my $date_flight = "00.00.0000";
	my $date_reg = "0000-00-00";
	my $witness = $query->param('witness');
	my $witness_name = &get_witness_name($witness);
	my $file = $query->param('file');
	my $filename = $file;
	$file =~ s/.*[\/\\](.*)/$1/;
	my $file_path = $ENV{DOCUMENT_ROOT} . "/igc/data/" . $file;
	my $buffer = "";
	my $recognized = defined ($query->param('recognized')) ? $query->param('recognized') : '0';
	my $sender = 'info@onf-online.at';
	
	#if (-e $file_path) {
	#if (substr($filename,-3) eq "zip") {
		open (SAVE,">$file_path") || die $!;
		while (<$filename>) {
			print SAVE $_;
		}
		close SAVE;
		
		open(FILE, "$file_path") or die "Kann den Pfad $file_path nicht oeffnen: $!\n" ;
		while (defined ($buffer = <FILE>)) { 
			if ($buffer =~ /HFDTE(.+?)/i) {
				$date_flight = substr($buffer, 5, 2) . "." . substr($buffer, 7, 2) . ".20" . substr($buffer, 9, 2);		
			}
		}
		close(FILE) ;
		
		$date_flight = &date_to_database($date_flight);

		my $sth = $DBH->prepare("SELECT * FROM user WHERE lizenz LIKE '$license'");
		$sth->execute;
		my $hash_ref = $sth->fetchrow_hashref;
		my $user_id = $hash_ref->{user_id};
		my $vorname = $hash_ref->{vorname};
		my $nachname = $hash_ref->{nachname};
		my $email = $hash_ref->{email};
		$sth->finish;

		my ($MIN,$HOUR,$DAY,$MONTH,$YEAR) = (localtime)[1,2,3,4,5];
		my $date_uploaded = sprintf("%#4ld-%#02d-%#02d", $YEAR+1900, $MONTH+1, $DAY);
		
		$sth = $DBH->prepare("INSERT INTO igc (user_id,file,date_uploaded,date_flight,date_reg,flight_subgroup,airplane_type,witness,recognized,reg_id) VALUES ('$user_id','$file','$date_uploaded','$date_flight','$date_reg','$type','$airplane','$witness','$recognized','')");
		$sth->execute();
		$sth->finish;
		
		$sth = $DBH->prepare("SELECT max(id) FROM igc");
		$sth->execute;	
		my $id = $sth->fetchrow_array;
		$sth->finish;
		
		my $datum = &date_to_display($date_flight);
		
		$type = &get_flight_type($type);
	
		if ($email gt "") {
			my $text = "Sehr geehrte/r $vorname $nachname!\n
	 Ihre Anmeldung zum Flug vom $datum wurde registriert!\n
	 Die IGC-Datei \"$file\" und die weiteren Daten gelangen nun zur Überprüfung und werden bei positivem Ergebnis unter folgendem Link veröffentlicht.\n
	 http://www.onf-online.at/igc/igc.woa?cmd=show&user_id=$user_id";
		
			my $msg = MIME::Lite->new(From =>"IGC-Datenbank <$sender>", To => $email, Subject =>"Bestaetigung zur Fluganmeldung", Type =>'multipart/mixed');
			$msg->attach(Type =>'text/plain; charset=iso-8859-1', Data =>"$text" );
			$msg->send('smtp','smtp.hot24.at', SSL => 1, Port => 25, AuthUser=>'info@onf-online.at', AuthPass=>'5SC7tpW#mk');
		}
		
		$email ='onf-seg@aeroclub.at'; 	# administrators email
		my $emailcc = 'p.wittwer@gmx.at'; # vertretung email
		
		my $text = "Hallo Administrator,\n
	Folgender Flug wurde neu registriert:\n
	Pilot Name:  $vorname $nachname
	Sportlizenz: $license
	Flug Datum:  $datum
	IGC-Datei:   $file
	Flugart:     $type
	Sportzeuge:  $witness_name\n
	Überprüfe die angegebenen Daten unter:
	http://www.onf-online.at/igc/igc.woa?cmd=show_detail_admin&id=$id\n
	Dein IGC-System,
	http://www.onf-online.at/";
		
		my $msg = MIME::Lite->new(From =>"IGC-Datenbank <$sender>", To => $email, Cc => $emailcc, Subject =>"Neue Fluganmeldung", Type =>'multipart/mixed');
		$msg->attach(Type =>'text/plain; charset=iso-8859-1', Data =>"$text" );
		$msg->send('smtp','smtp.hot24.at', SSL => 1, Port => 25, AuthUser=>'info@onf-online.at', AuthPass=>'5SC7tpW#mk');
	#}

	$sid = &generate_id($license);
	$query->print( $query->redirect(-cookie=>save_state($sid),-location=>"igc.woa?cmd=igc_saved&license=$license") );
}

sub igc_save_admin() {
	# pruefe ob eingeloggt, sonst login ueber routine auth_ok
	my $license = &admin_ok();
	
	my $lizenz = $query->param('lizenz');
	my $type = $query->param('type');
	my $vorname = $query->param('vorname');
	my $nachname = $query->param('nachname');
	my $reg_id = $query->param('reg_id');
	my $date_flight = $query->param('date');
	my $date_reg = "0000-00-00";
	my $recognized = defined ($query->param('recognized')) ? $query->param('recognized') : '1'; ######## soll es nicht gleich auf 1 stehen?
	my $file = "00000000.igc";
		
	$date_flight = &date_to_database($date_flight);
	
	my ($MIN,$HOUR,$DAY,$MONTH,$YEAR) = (localtime)[1,2,3,4,5];
	my $date_uploaded = sprintf("%#4ld-%#02d-%#02d", $YEAR+1900, $MONTH+1, $DAY);
	
	my $sth = $DBH->prepare("SELECT user_id FROM user WHERE lizenz = '$lizenz'");
	$sth->execute;	
	my $user_id = $sth->fetchrow_array;
	$sth->finish;
	
	if (!$user_id) {	
		my $sth = $DBH->prepare("SELECT max(user_id) FROM user");
		$sth->execute;	
		$user_id = $sth->fetchrow_array + 1;
		$sth->finish;
	
		$sth = $DBH->prepare("INSERT INTO user (user_id,vorname,nachname,lizenz) VALUES ('$user_id','$vorname','$nachname','$lizenz')");
		$sth->execute();
		$sth->finish;
	}
	
	$sth = $DBH->prepare("INSERT INTO igc (user_id,file,date_uploaded,date_flight,date_reg,flight_subgroup,recognized,reg_id) VALUES ($user_id','$file','$date_uploaded','$date_flight','$date_reg','$type','$recognized','$reg_id')");
	$sth->execute();
	$sth->finish;

	$sid = &generate_id($license);
	$query->print( $query->redirect(-cookie=>save_state($sid),-location=>"igc.woa?cmd=admin") );
}

sub igc_saved() {
	my $lizenz = $query->param('license');
	my $page_str = &file_to_string("templates/igc_saved.html");
	
	my $sth = $DBH->prepare("SELECT * FROM user WHERE lizenz LIKE '$lizenz'");
	$sth->execute;
	my $hash_ref = $sth->fetchrow_hashref;
	my $name = $hash_ref->{nachname} . " " . $hash_ref->{vorname};
	$sth->finish;
	
	$page_str =~ s/\[name\]/$name/g;
	
	$query->print($query->header(-expires=>'-1d'));
	$query->print($page_str);
}

sub igc_accept() {
	my $file = $query->param('file');
	my $page_str = &file_to_string("templates/igc_accept.html");
	
	my $sth = $DBH->prepare("SELECT * FROM igc WHERE file LIKE '$file'");
	$sth->execute;
	my $hash_ref = $sth->fetchrow_hashref;
	my $user_id = $hash_ref->{user_id};
	$sth->finish;
	
	$sth = $DBH->prepare("SELECT * FROM user WHERE user_id LIKE '$user_id'");
	$sth->execute;
	$hash_ref = $sth->fetchrow_hashref;
	my $name = $hash_ref->{nachname} . " " . $hash_ref->{vorname};
	$sth->finish;
	
	$DBH->do("UPDATE igc SET recognized='1' WHERE file='$file'");
	
	$page_str =~ s/\[name\]/$name/g;
	$page_str =~ s/\[file\]/$file/g;
	
	$query->print($query->header(-expires=>'-1d'));
	$query->print($page_str);
}

sub igc_publish() {
	# pruefe ob eingeloggt, sonst login ueber routine auth_ok
	my $license = &admin_ok();
	
	my $id = $query->param('id');
	my $map = $query->param('map');
	my $mapname = $map;
	my $baro = $query->param('baro');
	my $baroname = $baro;
	my $ok = $query->param('ok');
	my $reg_id = $query->param('reg_id');
	my $date_reg = "0000-00-00";
	$date_reg = &date_to_database($date_reg);
	my $file_path;
	
	my $sth = $DBH->prepare("SELECT recognized FROM igc WHERE id LIKE '$id'");
	$sth->execute;
	my $hash_ref = $sth->fetchrow_hashref;
	my $recognized = $hash_ref->{recognized};
	$sth->finish;
	
	if ($map) {
		$map =~ s/.*[\/\\](.*)/$1/;
		$file_path = $ENV{DOCUMENT_ROOT} . "/igc/data/map_" . $id . ".gif";
	
		if (substr($map,-3) eq "gif") {
				open (SAVE,">$file_path") || die $!;
				while (<$mapname>) {
					print SAVE $_;
				}
				close SAVE;
		}
	}
	
	if ($baro) {
		$baro =~ s/.*[\/\\](.*)/$1/;
		$file_path = $ENV{DOCUMENT_ROOT} . "/igc/data/baro_" . $id . ".gif";
	
		if (substr($baro,-3) eq "gif") {
				open (SAVE,">$file_path") || die $!;
				while (<$baroname>) {
					print SAVE $_;
				}
				close SAVE;
		}
	}
		
	my $sth = $DBH->prepare("UPDATE igc SET recognized='$ok',reg_id='$reg_id',date_reg='$date_reg' WHERE id='$id'");
	$sth->execute();
	$sth->finish;
	
	if ($recognized eq "0" && $ok eq "1") {
		my $email ='seidl.gerda@aeroclub.at'; 	# aeroclub email
		#my $email ='martin.beierl@hot24.at'; 	# test email
		my $text = "Hallo Gerda,\n
	Folgender Flug wurde neu registriert:\n
	http://www.onf-online.at/igc/igc.woa?cmd=show_detail&id=$id\n
	Dein IGC-System,
	http://www.onf-online.at/";
	
		my $sender = 'info@onf-online.at';
		
		my $msg = MIME::Lite->new(From =>"IGC-Datenbank <$sender>", To => $email, Subject =>"Neue Fluganmeldung", Type =>'multipart/mixed');
		$msg->attach(Type =>'text/plain; charset=iso-8859-1', Data =>"$text" );
		$msg->send('smtp','smtp.hot24.at', SSL => 1, Port => 25, AuthUser=>'info@onf-online.at', AuthPass=>'5SC7tpW#mk');
	}
	
	$query->print( $query->redirect(-cookie=>save_state($sid),-location=>"igc.woa?cmd=admin") );
}

sub igc_delete {
  my $id = defined ($query->param('id')) ? $query->param('id') : '';
	
	# pruefe ob eingeloggt, sonst login ueber routine auth_ok
	my $license = &admin_ok($id);
	
	my $file_path = $ENV{DOCUMENT_ROOT} . "/igc/data/map_" . $id . ".gif";
	if (-e $file_path) {
		system ("ssh root\@customer.hot24.at rm $file_path");
	}
	$file_path = $ENV{DOCUMENT_ROOT} . "/igc/data/baro_" . $id . ".gif";
	if (-e $file_path) {
		system ("ssh root\@customer.hot24.at rm $file_path");
	}
	
	my $sth = $DBH->prepare("SELECT file FROM igc WHERE id = '$id'");
	$sth->execute;
	my $hash_ref = $sth->fetchrow_hashref;
	my $file = $hash_ref->{file};
	$sth->finish;
	
	#$file_path = "/web/onf-online.at/igc/data/" . $file;
	#if (-e $file_path) {
	#	system ("ssh root\@customer.hot24.at rm $file_path");
	#}
	
	$DBH->do("DELETE FROM igc WHERE id='$id'");

	$query->print( $query->redirect(-cookie=>save_state($sid),-location=>"igc.woa?cmd=admin") );
}

sub search() {
	my $count = 0;
	my $not_found_msg = "Name nicht gefunden!";
	
	my $search_name = (defined $query->param('search_name')) ? $query->param('search_name') : 'undefined_value';
	my $pos = (defined $query->param('pos')) ? $query->param('pos') : '0';
	my $page_str = &file_to_string("/templates/search.html");

	if ($search_name eq "") {
		$search_name = "undefined_value";
	}

	if ($search_name) {
		my $sth = $DBH->prepare("SELECT count(*) FROM user WHERE vorname LIKE '%$search_name%' OR nachname LIKE '%$search_name%'");
		$sth->execute;
		$count = $sth->fetchrow_array;
		$sth->finish;
	}
	
	if ($count == 0) { 
		$page_str =~ s/\[page_bar\]//g;
		$page_str =~ s/\[page_dn\]//g;
		$page_str =~ s/\[page_up\]//g;
	}
	
	my $page_bar = sprintf("%d - %d von %d", $pos+1, &min($pos+$page_size, $count), $count);
	$page_str =~ s/\[page_bar\]/$page_bar/g;

	my $down_pos = &max(0, $pos - $page_size);
	my $up_pos = &min($pos + $page_size, $count);

	my $page_dn = ($pos - $page_size >= 0) ? "<A HREF=\"igc.woa?cmd=search&search_name=$search_name&pos=$down_pos\"><IMG SRC=\"/igc/images/left.gif\" ALT=\"\" WIDTH=\"8\" HEIGHT=\"16\" BORDER=\"0\"></A>" : "<IMG SRC=\"/igc/images/left_off.gif\" ALT=\"\" WIDTH=\"8\" HEIGHT=\"16\" BORDER=\"0\">";
	$page_str =~ s/\[page_dn\]/$page_dn/g;
	my $page_up = ($pos + $page_size <$count) ? "<A HREF=\"igc.woa?cmd=search&search_name=$search_name&pos=$up_pos\"><IMG SRC=\"/igc/images/right.gif\" ALT=\"\" WIDTH=\"8\" HEIGHT=\"16\" BORDER=\"0\"></A>" : "<IMG SRC=\"/igc/images/right_off.gif\" ALT=\"\" WIDTH=\"8\" HEIGHT=\"16\" BORDER=\"0\">";
	$page_str =~ s/\[page_up\]/$page_up/g;

	my $sth = $DBH->prepare("SELECT * FROM user WHERE vorname LIKE '%$search_name%' OR nachname LIKE '%$search_name%' ORDER BY nachname LIMIT $pos," . $page_size);
	$sth->execute;

	$page_str =~ s/\[pos\]/$pos/g;
	if ($search_name eq 'undefined_value') {
		$not_found_msg = "Bitte Vor- oder Nachnamen in Suchfeld eingeben!";
		$page_str =~ s/\[search_name\]//g;
	} elsif ($search_name eq '%') {
		$page_str =~ s/\[search_name\]//g;
	} else {
		$page_str =~ s/\[search_name\]/$search_name/g;
	}
	
	my $list_str = '<TABLE WIDTH="600" CELLSPACING="0" CELLPADDING="0" BORDER="0">';

	my $row_bg = 0;
	my $hash_ref;
	my $length;
	
	while ($hash_ref = $sth->fetchrow_hashref) {
		my $user_id = $hash_ref->{user_id};
		my ($flights,$reg_id) = &get_flights($user_id);
		my $name = $hash_ref->{nachname} . ' ' . $hash_ref->{vorname};
		
# Color the Backgroud Cell
		my $tdstyle = ($row_bg++%2 eq 0) ? "#EDF6FF" : "#CDE6FF";
		my $local_row = "<TR><TD WIDTH=\"200\" BGCOLOR=\"[tdstyle]\" CLASS=\"pagelist\"><A HREF=\"igc.woa?cmd=show&user_id=[user_id]\">[name]</A></TD>
		<TD WIDTH=\"100\" BGCOLOR=\"[tdstyle]\" CLASS=\"pagelist\">[flights]</TD>
		<TD WIDTH=\"300\" BGCOLOR=\"[tdstyle]\" CLASS=\"pagelist\">[reg_id]</TD></TR>";
		
		if ($flights == 0) {
			$local_row = "<TR><TD WIDTH=\"200\" BGCOLOR=\"[tdstyle]\" CLASS=\"pagelist\">[name]</TD>
			<TD WIDTH=\"100\" BGCOLOR=\"[tdstyle]\" CLASS=\"pagelist\">[flights]</TD>
			<TD WIDTH=\"300\" BGCOLOR=\"[tdstyle]\" ALIGN=\"RIGHT\" CLASS=\"pagelist\"></TD></TR>";
		}
		
		$local_row =~ s/\[tdstyle\]/$tdstyle/g;
		$local_row =~ s/\[user_id\]/$user_id/g;
		$local_row =~ s/\[name\]/$name/g;
		if ($flights == 0) {$flights = "-"}
		$local_row =~ s/\[flights\]/$flights/g;
		$local_row =~ s/\[reg_id\]/$reg_id/g;
		
		$list_str .= $local_row;
	}

	if ($count == 0) {
# Color the Backgroud Cell
		my $tdstyle = ($row_bg++%2 eq 0) ? "#EDF6FF" : "#CDE6FF";
		my $local_row = "<TR><TD WIDTH=\"600\" COLSPAN=\"4\" BGCOLOR=\"[tdstyle]\" CLASS=\"pagelist\">$not_found_msg</TD></TR>";
		$local_row =~ s/\[tdstyle\]/$tdstyle/g;
		$list_str .= $local_row;
	}

	while ($row_bg < $page_size) {
# Color the Backgroud Cell
		my $tdstyle = ($row_bg++%2 eq 0) ? "#EDF6FF" : "#CDE6FF";
		my $local_row = "<TR><TD WIDTH=\"600\" COLSPAN=\"4\" BGCOLOR=\"[tdstyle]\" CLASS=\"pagelist\">&nbsp;</TD></TR>";
		$local_row =~ s/\[tdstyle\]/$tdstyle/g;
		$list_str .= $local_row;
	}
	
	$list_str .= "</TABLE>";
	$page_str =~ s/\[user table\]/$list_str/g;
	
	my $alle_zeigen = "&#187; alle zeigen";
	if ($search_name eq "") { $alle_zeigen = "" }
	$page_str =~ s/\[alle_zeigen\]/$alle_zeigen/g;

	$query->print($query->header(-expires=>'-1d'));
	$query->print($page_str);
	
	$sth->finish;
}

sub search_admin() {
	# pruefe ob eingeloggt, sonst login ueber routine auth_ok
	my $license = &admin_ok();

	my $count = 0;
	my $not_found_msg = "Name nicht gefunden!";
	
	my $search_name = (defined $query->param('search_name')) ? $query->param('search_name') : 'undefined_value';
	my $pos = (defined $query->param('pos')) ? $query->param('pos') : '0';
	my $page_str = &file_to_string("templates/search_admin.html");

	if ($search_name eq "") {
		$search_name = "undefined_value";
	}

	if ($search_name) {
		my $sth = $DBH->prepare("SELECT count(*) FROM user WHERE vorname LIKE '%$search_name%' OR nachname LIKE '%$search_name%'");
		$sth->execute;
		$count = $sth->fetchrow_array;
		$sth->finish;
	}
	
	if ($count == 0) { 
		$page_str =~ s/\[page_bar\]//g;
		$page_str =~ s/\[page_dn\]//g;
		$page_str =~ s/\[page_up\]//g;
	}
	
	my $page_bar = sprintf("%d - %d von %d", $pos+1, &min($pos+$page_size, $count), $count);
	$page_str =~ s/\[page_bar\]/$page_bar/g;

	my $down_pos = &max(0, $pos - $page_size);
	my $up_pos = &min($pos + $page_size, $count);

	my $page_dn = ($pos - $page_size >= 0) ? "<A HREF=\"igc.woa?cmd=search_admin&search_name=$search_name&pos=$down_pos\"><IMG SRC=\"/igc/images/left.gif\" ALT=\"\" WIDTH=\"8\" HEIGHT=\"16\" BORDER=\"0\"></A>" : "<IMG SRC=\"/igc/images/left_off.gif\" ALT=\"\" WIDTH=\"8\" HEIGHT=\"16\" BORDER=\"0\">";
	$page_str =~ s/\[page_dn\]/$page_dn/g;
	my $page_up = ($pos + $page_size <$count) ? "<A HREF=\"igc.woa?cmd=search_admin&search_name=$search_name&pos=$up_pos\"><IMG SRC=\"/igc/images/right.gif\" ALT=\"\" WIDTH=\"8\" HEIGHT=\"16\" BORDER=\"0\"></A>" : "<IMG SRC=\"/igc/images/right_off.gif\" ALT=\"\" WIDTH=\"8\" HEIGHT=\"16\" BORDER=\"0\">";
	$page_str =~ s/\[page_up\]/$page_up/g;

	my $sth = $DBH->prepare("SELECT * FROM user WHERE vorname LIKE '%$search_name%' OR nachname LIKE '%$search_name%' ORDER BY nachname LIMIT $pos," . $page_size);
	$sth->execute;

	$page_str =~ s/\[pos\]/$pos/g;
	if ($search_name eq 'undefined_value') {
		$not_found_msg = "Bitte Vor- oder Nachnamen in Suchfeld eingeben!";
		$page_str =~ s/\[search_name\]//g;
	} elsif ($search_name eq '%') {
		$page_str =~ s/\[search_name\]//g;
	} else {
		$page_str =~ s/\[search_name\]/$search_name/g;
	}
	
	my $list_str = '<TABLE WIDTH="600" CELLSPACING="0" CELLPADDING="0" BORDER="0">';

	my $row_bg = 0;
	my $hash_ref;
	my $length;
	
	while ($hash_ref = $sth->fetchrow_hashref) {
		my $user_id = $hash_ref->{user_id};
		my ($flights,$reg_id) = &get_flights($user_id);
		my $name = $hash_ref->{nachname} . ' ' . $hash_ref->{vorname};
		
# Color the Backgroud Cell
		my $tdstyle = ($row_bg++%2 eq 0) ? "#EDF6FF" : "#CDE6FF";
		my $local_row = "<TR><TD WIDTH=\"240\" BGCOLOR=\"[tdstyle]\" CLASS=\"pagelist\"><A HREF=\"igc.woa?cmd=show_admin&user_id=[user_id]\">[name]</A></TD>
		<TD WIDTH=\"140\" BGCOLOR=\"[tdstyle]\" CLASS=\"pagelist\">[flights]</TD>
		<TD BGCOLOR=\"[tdstyle]\" CLASS=\"pagelist\">[reg_id]</TD>
		<TD WIDTH=\"20\" BGCOLOR=\"[tdstyle]\" ALIGN=\"left\" CLASS=\"pagelist\"><A HREF=\"igc.woa?cmd=show_detail_user&id=[user_id]\"><IMG SRC=\"/igc/images/edit.gif\" ALT=\"\" WIDTH=\"15\" HEIGHT=\"16\" BORDER=\"0\"></A>
		<TD WIDTH=\"16\" BGCOLOR=\"[tdstyle]\" ALIGN=\"right\" CLASS=\"pagelist\"><A HREF=\"igc.woa?cmd=user_delete&id=[user_id]\" onClick=\"return confirm('Sind sie sicher, den Piloten [name] zu l&ouml;schen?')\"><IMG SRC=\"/igc/images/delete.gif\" ALT=\"\" WIDTH=\"16\" HEIGHT=\"16\" BORDER=\"0\"></A></TD>
		<TD WIDTH=\"3\" BGCOLOR=\"[tdstyle]\" ALIGN=\"right\" CLASS=\"pagelist\"><IMG SRC=\"/igc/images/leer.gif\" ALT=\"\" WIDTH=\"3\" HEIGHT=\"1\" BORDER=\"0\"></TD></TR>";
		
		if ($flights == 0) {
			$local_row = "<TR><TD WIDTH=\"240\" BGCOLOR=\"[tdstyle]\" CLASS=\"pagelist\">[name]</TD>
			<TD WIDTH=\"140\" BGCOLOR=\"[tdstyle]\" CLASS=\"pagelist\">[flights]</TD>
			<TD BGCOLOR=\"[tdstyle]\" CLASS=\"pagelist\"></TD>
			<TD WIDTH=\"20\" BGCOLOR=\"[tdstyle]\" ALIGN=\"left\" CLASS=\"pagelist\"><A HREF=\"igc.woa?cmd=show_detail_user&id=[user_id]\"><IMG SRC=\"/igc/images/edit.gif\" ALT=\"\" WIDTH=\"15\" HEIGHT=\"16\" BORDER=\"0\"></A>
			<TD WIDTH=\"16\" BGCOLOR=\"[tdstyle]\" ALIGN=\"right\" CLASS=\"pagelist\"><A HREF=\"igc.woa?cmd=user_delete&id=[user_id]\" onClick=\"return confirm('Sind sie sicher, den Piloten [name] zu l&ouml;schen?')\"><IMG SRC=\"/igc/images/delete.gif\" ALT=\"\" WIDTH=\"16\" HEIGHT=\"16\" BORDER=\"0\"></A></TD>
			<TD WIDTH=\"3\" BGCOLOR=\"[tdstyle]\" ALIGN=\"right\" CLASS=\"pagelist\"><IMG SRC=\"/igc/images/leer.gif\" ALT=\"\" WIDTH=\"3\" HEIGHT=\"1\" BORDER=\"0\"></TD></TR>";
		}
		
		$local_row =~ s/\[tdstyle\]/$tdstyle/g;
		$local_row =~ s/\[user_id\]/$user_id/g;
		$local_row =~ s/\[name\]/$name/g;
		if ($flights == 0) {$flights = "-"}
		$local_row =~ s/\[flights\]/$flights/g;
		$local_row =~ s/\[reg_id\]/$reg_id/g;
		
		$list_str .= $local_row;
	}

	if ($count == 0) {
# Color the Backgroud Cell
		my $tdstyle = ($row_bg++%2 eq 0) ? "#EDF6FF" : "#CDE6FF";
		my $local_row = "<TR><TD COLSPAN=\"5\" BGCOLOR=\"[tdstyle]\" CLASS=\"pagelist\">$not_found_msg</TD></TR>";
		$local_row =~ s/\[tdstyle\]/$tdstyle/g;
		$list_str .= $local_row;
	}

	while ($row_bg < $page_size) {
# Color the Backgroud Cell
		my $tdstyle = ($row_bg++%2 eq 0) ? "#EDF6FF" : "#CDE6FF";
		my $local_row = "<TR><TD COLSPAN=\"5\" BGCOLOR=\"[tdstyle]\" CLASS=\"pagelist\">&nbsp;</TD></TR>";
		$local_row =~ s/\[tdstyle\]/$tdstyle/g;
		$list_str .= $local_row;
	}
	
	$list_str .= "</TABLE>";
	$page_str =~ s/\[user table\]/$list_str/g;
	
	my $alle_zeigen = "&#187; alle zeigen";
	if ($search_name eq "") { $alle_zeigen = "" }
	$page_str =~ s/\[alle_zeigen\]/$alle_zeigen/g;

	#$query->print($query->header(-cookie=>save_state($sid),-expires=>'-1d'));
	$query->print($query->header(-expires=>'-1d'));
	$query->print($page_str);
	
	$sth->finish;
}

sub rating() {
	my $count = 0;
	my $not_found_msg = "Kein Suchergebnis!";
	
	my $search_name = (defined $query->param('search_name')) ? $query->param('search_name') : '';
	my $pos = (defined $query->param('pos')) ? $query->param('pos') : '0';
	my $year = (defined $query->param('year')) ? $query->param('year') : '0';
	my $year_criteria = "%";
	if ($year ne "0") { $year_criteria = $year . "%"; }
	my $lv = (defined $query->param('lv')) ? $query->param('lv') : '0';
	my $lv_criteria = "%";
	if ($lv ne "0") { $lv_criteria = $lv . "%"; }
	#my $criteria = (defined $query->param('criteria')) ? $query->param('criteria') : '1';
	my $criteria = $query->param('criteria');
	if (!$criteria) { $criteria = 1; }
	my $page_str = &file_to_string("templates/rating.html");

	#my $sth = $DBH->prepare("SELECT count(*) FROM user,igc,flight_group,flight_subgroup WHERE (vorname LIKE '%$search_name%' OR nachname LIKE '%$search_name%') AND user.user_id=igc.user_id AND igc.flight_subgroup=flight_subgroup.id AND flight_group.id=flight_subgroup.group_id AND flight_group.id='$criteria' AND igc.reg_id>''");
	
	#my $sth = $DBH->prepare("SELECT * FROM user,igc,flight_group,flight_subgroup WHERE (vorname LIKE '%$search_name%' OR nachname LIKE '%$search_name%') AND user.user_id=igc.user_id AND igc.flight_subgroup=flight_subgroup.id AND flight_group.id=flight_subgroup.group_id AND flight_group.id='$criteria' AND igc.reg_id>'' GROUP BY user.user_id");
	
	my $sth = $DBH->prepare("SELECT user.user_id, user.vorname, user.nachname, flight_group.id FROM user,igc,flight_group,flight_subgroup WHERE (vorname LIKE '%$search_name%' OR nachname LIKE '%$search_name%') AND igc.date_flight LIKE '$year_criteria' AND user.lizenz LIKE '$lv_criteria' AND user.user_id=igc.user_id AND igc.flight_subgroup=flight_subgroup.id AND flight_group.id=flight_subgroup.group_id AND flight_group.id='$criteria' GROUP BY flight_group.id, user.user_id ORDER BY nachname");
	
	$sth->execute;
	#$count = $sth->fetchrow_array();
	while (my $hash_ref = $sth->fetchrow_hashref) {
		$count ++;
	}
	$sth->finish;
	
	if ($count == 0) { 
		$page_str =~ s/\[page_bar\]//g;
		$page_str =~ s/\[page_dn\]//g;
		$page_str =~ s/\[page_up\]//g;
	}
	
	if ($count < $page_size) { $pos = 0; }
	
	my $page_bar = sprintf("%d - %d von %d", $pos+1, &min($pos+$page_size, $count), $count);
	$page_str =~ s/\[page_bar\]/$page_bar/g;

	my $down_pos = &max(0, $pos - $page_size);
	my $up_pos = &min($pos + $page_size, $count);

	my $page_dn = ($pos - $page_size >= 0) ? "<A HREF=\"igc.woa\?cmd=rating&search_name=$search_name&pos=$down_pos&criteria=$criteria&year=$year&lv=$lv\"><IMG SRC=\"/igc/images/left.gif\" ALT=\"\" WIDTH=\"8\" HEIGHT=\"16\" BORDER=\"0\"></A>" : "<IMG SRC=\"/igc/images/left_off.gif\" ALT=\"\" WIDTH=\"8\" HEIGHT=\"16\" BORDER=\"0\">";
	$page_str =~ s/\[page_dn\]/$page_dn/g;
	my $page_up = ($pos + $page_size <$count) ? "<A HREF=\"igc.woa\?cmd=rating&search_name=$search_name&pos=$up_pos&criteria=$criteria&year=$year&lv=$lv\"><IMG SRC=\"/igc/images/right.gif\" ALT=\"\" WIDTH=\"8\" HEIGHT=\"16\" BORDER=\"0\"></A>" : "<IMG SRC=\"/igc/images/right_off.gif\" ALT=\"\" WIDTH=\"8\" HEIGHT=\"16\" BORDER=\"0\">";
	$page_str =~ s/\[page_up\]/$page_up/g;
	
	my $sth = $DBH->prepare("SELECT user.user_id, user.vorname, user.nachname, igc.reg_id, igc.witness, flight_group.id, flight_subgroup.id AS subgroup_id FROM user,igc,flight_group,flight_subgroup WHERE (vorname LIKE '%$search_name%' OR nachname LIKE '%$search_name%') AND igc.date_flight LIKE '$year_criteria' AND user.lizenz LIKE '$lv_criteria' AND user.user_id=igc.user_id AND igc.flight_subgroup=flight_subgroup.id AND flight_group.id=flight_subgroup.group_id AND flight_group.id='$criteria' GROUP BY user.user_id ORDER BY nachname LIMIT $pos," . $page_size);
	
	my $test = "SELECT user.user_id, user.vorname, user.nachname, igc.reg_id, igc.witness, flight_group.id, flight_subgroup.id AS subgroup_id FROM user,igc,flight_group,flight_subgroup WHERE (vorname LIKE '%$search_name%' OR nachname LIKE '%$search_name%') AND igc.date_flight LIKE '$year_criteria' AND user.lizenz LIKE '$lv_criteria' AND user.user_id=igc.user_id AND igc.flight_subgroup=flight_subgroup.id AND flight_group.id=flight_subgroup.group_id AND flight_group.id='$criteria' GROUP BY user.user_id ORDER BY nachname;";
	
	$sth->execute;

	$page_str =~ s/\[pos\]/$pos/g;
	$page_str =~ s/\[search_name\]/$search_name/g;
	
	my $list_str = '<TABLE WIDTH="600" CELLSPACING="0" CELLPADDING="0" BORDER="0">';

	my $row_bg = 0;
	my $hash_ref;
	my $length;
	
	while ($hash_ref = $sth->fetchrow_hashref) {
		my $user_id = $hash_ref->{user_id};
		my $group_id = $hash_ref->{id};
		my $subgroup_id = $hash_ref->{subgroup_id};
		my $flights = &get_flights_all($group_id, $user_id, $year);
		#my ($flights,$reg_id) = &get_flights($user_id);
		my $reg_id = $hash_ref->{reg_id};
		
		my $name = $hash_ref->{nachname} . ' ' . $hash_ref->{vorname};
		
# Color the Backgroud Cell
		my $tdstyle = ($row_bg++%2 eq 0) ? "#EDF6FF" : "#CDE6FF";
		my $local_row = "<TR><TD WIDTH=\"200\" BGCOLOR=\"[tdstyle]\" CLASS=\"pagelist\"><A HREF=\"igc.woa?cmd=show&user_id=[user_id]\">[name]</A></TD>
		<TD WIDTH=\"100\" BGCOLOR=\"[tdstyle]\" CLASS=\"pagelist\">[flights]</TD>
		<TD WIDTH=\"300\" BGCOLOR=\"[tdstyle]\" CLASS=\"pagelist\">[reg_id]</TD></TR>";
		
		if ($flights == 0) {
			$local_row = "<TR><TD WIDTH=\"200\" BGCOLOR=\"[tdstyle]\" CLASS=\"pagelist\">[name]</TD>
			<TD WIDTH=\"100\" BGCOLOR=\"[tdstyle]\" CLASS=\"pagelist\">[flights]</TD>
			<TD WIDTH=\"300\" BGCOLOR=\"[tdstyle]\" ALIGN=\"RIGHT\" CLASS=\"pagelist\"></TD></TR>";
		}
		
		$local_row =~ s/\[tdstyle\]/$tdstyle/g;
		$local_row =~ s/\[user_id\]/$user_id/g;
		$local_row =~ s/\[name\]/$name/g;
		if (!$hash_ref->{witness}) { $flights = "-"; }
		if ($flights == 0) { $flights = "-"; }
		$local_row =~ s/\[flights\]/$flights/g;
		my $flight_subgroup = &get_flight_subgroup($subgroup_id);
		if ($group_id < 4) { $flight_subgroup = ""; }
		if ($reg_id) { $flight_subgroup = $flight_subgroup ." " . $reg_id; }
		$local_row =~ s/\[reg_id\]/$flight_subgroup/g;
		
		$list_str .= $local_row;
	}

	if ($count == 0) {
# Color the Backgroud Cell
		my $tdstyle = ($row_bg++%2 eq 0) ? "#EDF6FF" : "#CDE6FF";
		my $local_row = "<TR><TD WIDTH=\"600\" COLSPAN=\"4\" BGCOLOR=\"[tdstyle]\" CLASS=\"pagelist\">$not_found_msg</TD></TR>";
		$local_row =~ s/\[tdstyle\]/$tdstyle/g;
		$list_str .= $local_row;
	}

	while ($row_bg < $page_size) {
# Color the Backgroud Cell
		my $tdstyle = ($row_bg++%2 eq 0) ? "#EDF6FF" : "#CDE6FF";
		my $local_row = "<TR><TD WIDTH=\"600\" COLSPAN=\"4\" BGCOLOR=\"[tdstyle]\" CLASS=\"pagelist\">&nbsp;</TD></TR>";
		$local_row =~ s/\[tdstyle\]/$tdstyle/g;
		$list_str .= $local_row;
	}
	
	$list_str .= "</TABLE>";

	$page_str =~ s/\[user table\]/$list_str/g;
	
	my $criteria_menu = &build_criteria_menu($criteria);
	$page_str =~ s/\[criteria_menu\]/$criteria_menu/g;

	my $year_menu = &build_year_menu($year);
	$page_str =~ s/\[year_menu\]/$year_menu/g;
	
	my $lv_menu = &build_lv_menu($lv);
	$page_str =~ s/\[lv_menu\]/$lv_menu/g;
	
	$query->print($query->header(-expires=>'-1d'));
	$query->print($page_str);
	
	$sth->finish;
}

sub show() {
	my $user_id = $query->param('user_id');
	my $pos = (defined $query->param('pos')) ? $query->param('pos') : '0';
	my $page_str = &file_to_string("templates/list.html");
	my $count = 0;
	
	my $sth = $DBH->prepare("SELECT recognized FROM igc WHERE user_id = '$user_id'");
	$sth->execute;
	my $hash_ref;
	while ($hash_ref = $sth->fetchrow_hashref) {
		my $recognized = $hash_ref->{recognized};
		if ($recognized == 1) {
			$count += 1;
		}
	}
	$sth->finish;

	if ($count == 0) { 
		$page_str =~ s/\[page_bar\]//g;
		$page_str =~ s/\[page_dn\]//g;
		$page_str =~ s/\[page_up\]//g;
	}
	
	my $page_bar = sprintf("%d - %d von %d", $pos+1, &min($pos+$page_size, $count), $count);
	$page_str =~ s/\[page_bar\]/$page_bar/g;

	my $down_pos = &max(0, $pos - $page_size);
	my $up_pos = &min($pos + $page_size, $count);

	my $page_dn = ($pos - $page_size >= 0) ? "<A HREF=\"igc.woa?cmd=show&user_id=$user_id&pos=$down_pos\"><IMG SRC=\"/igc/images/left.gif\" ALT=\"\" WIDTH=\"8\" HEIGHT=\"16\" BORDER=\"0\"></A>" : "<IMG SRC=\"/igc/images/left_off.gif\" ALT=\"\" WIDTH=\"8\" HEIGHT=\"16\" BORDER=\"0\">";
	$page_str =~ s/\[page_dn\]/$page_dn/g;
	my $page_up = ($pos + $page_size <$count) ? "<A HREF=\"igc.woa?cmd=show&user_id=$user_id&pos=$up_pos\"><IMG SRC=\"/igc/images/right.gif\" ALT=\"\" WIDTH=\"8\" HEIGHT=\"16\" BORDER=\"0\"></A>" : "<IMG SRC=\"/igc/images/right_off.gif\" ALT=\"\" WIDTH=\"8\" HEIGHT=\"16\" BORDER=\"0\">";
	$page_str =~ s/\[page_up\]/$page_up/g;

	$sth = $DBH->prepare("SELECT * FROM igc WHERE user_id LIKE '$user_id' AND recognized LIKE '1' LIMIT $pos," . $page_size);
	$sth->execute;

	my $sth1 = $DBH->prepare("SELECT * FROM user WHERE user_id='$user_id'");
	$sth1->execute;
	my $hash_ref1 = $sth1->fetchrow_hashref;
	my $name = $hash_ref1->{nachname} . ' ' . $hash_ref1->{vorname};
	$sth1->finish;
	
	$page_str =~ s/\[name\]/$name/g;
	$page_str =~ s/\[pos\]/$pos/g;

	my $list_str = '<TABLE CELLSPACING="0" CELLPADDING="3" BORDER="0">';

	my $row_bg = 0;
	my $hash_ref;
	my $length;
	
	while ($hash_ref = $sth->fetchrow_hashref) {
		my $file = $hash_ref->{file};
		my $id = $hash_ref->{id};
		
# Color the Backgroud Cell
		my $tdstyle = ($row_bg++%2 eq 0) ? "#EDF6FF" : "#CDE6FF";
		my $local_row = "<TR><TD WIDTH=\"90\" BGCOLOR=\"[tdstyle]\" CLASS=\"pagelist\">[date]</TD>
		<TD WIDTH=\"240\" BGCOLOR=\"[tdstyle]\" CLASS=\"pagelist\">[flugart]</TD>
		<TD WIDTH=\"251\" BGCOLOR=\"[tdstyle]\" CLASS=\"pagelist\">[airplane]</TD>
		<TD WIDTH=\"16\" BGCOLOR=\"[tdstyle]\" ALIGN=\"right\" CLASS=\"pagelist\"><A HREF=\"igc.woa?cmd=show_detail&id=$id\"><IMG SRC=\"/igc/images/info.gif\" ALT=\"\" WIDTH=\"16\" HEIGHT=\"16\" BORDER=\"0\"></A></TD>
		<TD WIDTH=\"3\" BGCOLOR=\"[tdstyle]\" ALIGN=\"right\" CLASS=\"pagelist\"><IMG SRC=\"/igc/images/leer.gif\" ALT=\"\" WIDTH=\"3\" HEIGHT=\"1\" BORDER=\"0\"></TD></TR>";
		
		#my $year = "20" . &igc_value(substr($file,0,1));
		#my $month = &igc_value(substr($file,1,1));
		#my $day = &igc_value(substr($file,2,1));
		#my $date = $day . "." . $month . "." . $year;
		#my $hersteller = &igc_logger(substr($file,3,1));
		#my $flights = &igc_value(substr($file,7,1));
		my $date = &get_igc_date($id);
		$date = &date_to_display($date);
		
		$sth1 = $DBH->prepare("SELECT * FROM igc WHERE id='$id'");
		$sth1->execute;
		my $hash_ref1 = $sth1->fetchrow_hashref;
		my $flight_subgroup = $hash_ref1->{flight_subgroup};
		my $airplane_type = $hash_ref1->{airplane_type};
		$sth1->finish;
		
		$sth1 = $DBH->prepare("SELECT * FROM flight_subgroup WHERE id='$flight_subgroup'");
		$sth1->execute;	
		$hash_ref1 = $sth1 ->fetchrow_hashref;
		my $group_id = $hash_ref1->{group_id};
		my $type = $hash_ref1->{type};
		$sth1	->finish;
		
		$sth1 = $DBH->prepare("SELECT * FROM flight_group WHERE id='$group_id'");
		$sth1->execute;	
		$hash_ref1 = $sth1 ->fetchrow_hashref;
		$type = $hash_ref1->{type} . ": " . $type;
		$sth1	->finish;
		
		$sth1 = $DBH->prepare("SELECT * FROM airplane_type WHERE id='$airplane_type'");
		$sth1->execute;	
		$hash_ref1 = $sth1 ->fetchrow_hashref;
		my $airplane = $hash_ref1->{type};
		$sth1	->finish;
		
		$local_row =~ s/\[tdstyle\]/$tdstyle/g;
		$local_row =~ s/\[file\]/$hash_ref->{file}/g;
		$local_row =~ s/\[date\]/$date/g;
		#$local_row =~ s/\[hersteller\]/$hersteller/g;
		$local_row =~ s/\[flugart\]/$type/g;
		$local_row =~ s/\[airplane\]/$airplane/g;
		#$local_row =~ s/\[flights\]/$flights/g;
		
		$list_str .= $local_row;
	}

	if ($count == 0) {
# Color the Backgroud Cell
		my $tdstyle = ($row_bg++%2 eq 0) ? "#EDF6FF" : "#CDE6FF";
		my $local_row = "<TR><TD WIDTH=\"600\" COLSPAN=\"6\" BGCOLOR=\"[tdstyle]\" CLASS=\"pagelist\">Keine Fl&uuml;ge vorhanden!</TD></TR>";
		$local_row =~ s/\[tdstyle\]/$tdstyle/g;
		$list_str .= $local_row;
	}

	while ($row_bg < $page_size) {
# Color the Backgroud Cell
		my $tdstyle = ($row_bg++%2 eq 0) ? "#EDF6FF" : "#CDE6FF";
		my $local_row = "<TR><TD WIDTH=\"600\" COLSPAN=\"6\" BGCOLOR=\"[tdstyle]\" CLASS=\"pagelist\">&nbsp;</TD></TR>";
		$local_row =~ s/\[tdstyle\]/$tdstyle/g;
		$list_str .= $local_row;
	}
	
	$list_str .= "</TABLE>";
	$page_str =~ s/\[igc table\]/$list_str/g;

	#$query->print( $query->header(-cookie=>save_state($sid),-expires=>'-1d'));
	$query->print($query->header(-expires=>'-1d'));
	$query->print($page_str);
	$sth->finish;
}

sub show_admin() {
	# pruefe ob eingeloggt, sonst login ueber routine auth_ok
	my $license = &admin_ok();

	my $user_id = $query->param('user_id');
	my $pos = (defined $query->param('pos')) ? $query->param('pos') : '0';
	my $page_str = &file_to_string("templates/list_admin.html");
	my $count = 0;
	
	my $sth = $DBH->prepare("SELECT recognized FROM igc WHERE user_id = '$user_id'");
	$sth->execute;
	my $hash_ref;
	while ($hash_ref = $sth->fetchrow_hashref) {
		my $recognized = $hash_ref->{recognized};
		if ($recognized == 1) {
			$count += 1;
		}
	}
	$sth->finish;

	if ($count == 0) { 
		$page_str =~ s/\[page_bar\]//g;
		$page_str =~ s/\[page_dn\]//g;
		$page_str =~ s/\[page_up\]//g;
	}
	
	my $page_bar = sprintf("%d - %d von %d", $pos+1, &min($pos+$page_size, $count), $count);
	$page_str =~ s/\[page_bar\]/$page_bar/g;

	my $down_pos = &max(0, $pos - $page_size);
	my $up_pos = &min($pos + $page_size, $count);

	my $page_dn = ($pos - $page_size >= 0) ? "<A HREF=\"igc.woa?cmd=show_admin&user_id=$user_id&pos=$down_pos\"><IMG SRC=\"/igc/images/left.gif\" ALT=\"\" WIDTH=\"8\" HEIGHT=\"16\" BORDER=\"0\"></A>" : "<IMG SRC=\"/igc/images/left_off.gif\" ALT=\"\" WIDTH=\"8\" HEIGHT=\"16\" BORDER=\"0\">";
	$page_str =~ s/\[page_dn\]/$page_dn/g;
	my $page_up = ($pos + $page_size <$count) ? "<A HREF=\"igc.woa?cmd=show_admin&user_id=$user_id&pos=$up_pos\"><IMG SRC=\"/igc/images/right.gif\" ALT=\"\" WIDTH=\"8\" HEIGHT=\"16\" BORDER=\"0\"></A>" : "<IMG SRC=\"/igc/images/right_off.gif\" ALT=\"\" WIDTH=\"8\" HEIGHT=\"16\" BORDER=\"0\">";
	$page_str =~ s/\[page_up\]/$page_up/g;

	$sth = $DBH->prepare("SELECT * FROM igc WHERE user_id LIKE '$user_id' LIMIT $pos," . $page_size);
	$sth->execute;

	my $sth1 = $DBH->prepare("SELECT * FROM user WHERE user_id='$user_id'");
	$sth1->execute;
	my $hash_ref1 = $sth1->fetchrow_hashref;
	my $name = $hash_ref1->{nachname} . ' ' . $hash_ref1->{vorname};
	$sth1->finish;
	
	$page_str =~ s/\[name\]/$name/g;
	$page_str =~ s/\[pos\]/$pos/g;

	my $list_str = '<TABLE WIDTH="600" CELLSPACING="0" CELLPADDING="3" BORDER="0">';

	my $row_bg = 0;
	my $hash_ref;
	my $length;
	
	while ($hash_ref = $sth->fetchrow_hashref) {
		my $file = $hash_ref->{file};
		my $id = $hash_ref->{id};
		
# Color the Backgroud Cell
		my $tdstyle = ($row_bg++%2 eq 0) ? "#EDF6FF" : "#CDE6FF";
		my $local_row = "<TR><TD WIDTH=\"90\" BGCOLOR=\"[tdstyle]\" CLASS=\"pagelist\">[date]</TD>
		<TD WIDTH=\"240\" BGCOLOR=\"[tdstyle]\" CLASS=\"pagelist\">[flugart]</TD>
		<TD BGCOLOR=\"[tdstyle]\" CLASS=\"pagelist\">[airplane]</TD>
		<TD WIDTH=\"16\" BGCOLOR=\"[tdstyle]\" ALIGN=\"right\" CLASS=\"pagelist\"><A HREF=\"igc.woa?cmd=igc_delete&id=$id\" onClick=\"return confirm('Sind sie sicher, diesen Flug vom [date] zu l&ouml;schen?')\"><IMG SRC=\"/igc/images/delete.gif\" ALT=\"\" WIDTH=\"16\" HEIGHT=\"16\" BORDER=\"0\"></A></TD>
		<TD WIDTH=\"3\" BGCOLOR=\"[tdstyle]\" ALIGN=\"right\" CLASS=\"pagelist\"><IMG SRC=\"/igc/images/leer.gif\" ALT=\"\" WIDTH=\"3\" HEIGHT=\"1\" BORDER=\"0\"></TD>
		<TD WIDTH=\"16\" BGCOLOR=\"[tdstyle]\" ALIGN=\"right\" CLASS=\"pagelist\"><A HREF=\"igc.woa?cmd=show_detail&id=$id\"><IMG SRC=\"/igc/images/info.gif\" ALT=\"\" WIDTH=\"16\" HEIGHT=\"16\" BORDER=\"0\"></A></TD>
		<TD WIDTH=\"3\" BGCOLOR=\"[tdstyle]\" ALIGN=\"right\" CLASS=\"pagelist\"><IMG SRC=\"/igc/images/leer.gif\" ALT=\"\" WIDTH=\"3\" HEIGHT=\"1\" BORDER=\"0\"></TD></TR>";
		
		#my $year = "20" . &igc_value(substr($file,0,1));
		#my $month = &igc_value(substr($file,1,1));
		#my $day = &igc_value(substr($file,2,1));
		#my $date = $day . "." . $month . "." . $year;
		#my $hersteller = &igc_logger(substr($file,3,1));
		#my $flights = &igc_value(substr($file,7,1));
		my $date = &get_igc_date($id);
		$date = &date_to_display($date);
		
		$sth1 = $DBH->prepare("SELECT * FROM igc WHERE id='$id'");
		$sth1->execute;
		my $hash_ref1 = $sth1->fetchrow_hashref;
		my $flight_subgroup = $hash_ref1->{flight_subgroup};
		my $airplane_type = $hash_ref1->{airplane_type};
		$sth1->finish;
		
		$sth1 = $DBH->prepare("SELECT * FROM flight_subgroup WHERE id='$flight_subgroup'");
		$sth1->execute;	
		$hash_ref1 = $sth1 ->fetchrow_hashref;
		my $group_id = $hash_ref1->{group_id};
		my $type = $hash_ref1->{type};
		$sth1	->finish;
		
		$sth1 = $DBH->prepare("SELECT * FROM flight_group WHERE id='$group_id'");
		$sth1->execute;	
		$hash_ref1 = $sth1 ->fetchrow_hashref;
		$type = $hash_ref1->{type} . ": " . $type;
		$sth1	->finish;
		
		$sth1 = $DBH->prepare("SELECT * FROM airplane_type WHERE id='$airplane_type'");
		$sth1->execute;	
		$hash_ref1 = $sth1 ->fetchrow_hashref;
		my $airplane = $hash_ref1->{type};
		$sth1	->finish;
		
		$local_row =~ s/\[tdstyle\]/$tdstyle/g;
		$local_row =~ s/\[file\]/$hash_ref->{file}/g;
		$local_row =~ s/\[date\]/$date/g;
		#$local_row =~ s/\[hersteller\]/$hersteller/g;
		$local_row =~ s/\[flugart\]/$type/g;
		$local_row =~ s/\[airplane\]/$airplane/g;
		#$local_row =~ s/\[flights\]/$flights/g;
		
		$list_str .= $local_row;
	}

	if ($count == 0) {
# Color the Backgroud Cell
		my $tdstyle = ($row_bg++%2 eq 0) ? "#EDF6FF" : "#CDE6FF";
		my $local_row = "<TR><TD COLSPAN=\"7\" BGCOLOR=\"[tdstyle]\" CLASS=\"pagelist\">Keine Fl&uuml;ge vorhanden!</TD></TR>";
		$local_row =~ s/\[tdstyle\]/$tdstyle/g;
		$list_str .= $local_row;
	}

	while ($row_bg < $page_size) {
# Color the Backgroud Cell
		my $tdstyle = ($row_bg++%2 eq 0) ? "#EDF6FF" : "#CDE6FF";
		my $local_row = "<TR><TD COLSPAN=\"7\" BGCOLOR=\"[tdstyle]\" CLASS=\"pagelist\">&nbsp;</TD></TR>";
		$local_row =~ s/\[tdstyle\]/$tdstyle/g;
		$list_str .= $local_row;
	}
	
	$list_str .= "</TABLE>";
	$page_str =~ s/\[igc table\]/$list_str/g;

	#$query->print( $query->header(-cookie=>save_state($sid),-expires=>'-1d'));
	$query->print($query->header(-expires=>'-1d'));
	$query->print($page_str);
	$sth->finish;
}

sub show_detail() {
	my $id = defined ($query->param('id')) ? $query->param('id') : '';
	
	$sid = $query->cookie(-name=>COOKIE_NAME);
	
	my $sth = $DBH->prepare("SELECT * FROM AuthSession WHERE sid='$sid'");
	$sth->execute;	
	my $hash_ref = $sth->fetchrow_hashref;
	my $pw_name = $hash_ref->{lizenz};
	$sth->finish;
	
	$sth = $DBH->prepare("SELECT * FROM AuthAdmin WHERE pw_name LIKE '$pw_name'");
	$sth->execute;
	$hash_ref = $sth->fetchrow_hashref;
	$pw_name = $hash_ref->{pw_name};
	
	if ($pw_name) { $query->print( $query->redirect(-location=>"igc.woa?cmd=show_detail_admin&id=$id") ); exit; }
	$sth->finish;

	my $page_str = &file_to_string("templates/details.html");
	
	my $sth = $DBH->prepare("SELECT * FROM igc WHERE id = '$id'");
	$sth->execute;
	my $hash_ref = $sth->fetchrow_hashref;
	my $user_id = $hash_ref->{user_id};
	my $file = $hash_ref->{file};
	my $date_uploaded = $hash_ref->{date_uploaded};
	my $date_flight = $hash_ref->{date_flight};
	my $flight_subgroup = $hash_ref->{flight_subgroup};
	my $airplane_type = $hash_ref->{airplane_type};
	my $witness = &get_witness($hash_ref->{witness});
	my $recognized = $hash_ref->{recognized};
	my $reg_id = $hash_ref->{reg_id};

	$sth = $DBH->prepare("SELECT * FROM user WHERE user_id = '$user_id'");
	$sth->execute;
	$hash_ref = $sth->fetchrow_hashref;
	my $vorname = $hash_ref->{vorname};
	my $nachname = $hash_ref->{nachname};
	my $plz = $hash_ref->{plz};
	my $ort = $hash_ref->{ort};
	my $strasse = $hash_ref->{strasse};
	my $geb_datum = $hash_ref->{geb_datum};
	my $lizenz = $hash_ref->{lizenz};
	my $passwort = $hash_ref->{passwort};
	my $email = $hash_ref->{email};
	
	$sth = $DBH->prepare("SELECT * FROM flight_subgroup WHERE id='$flight_subgroup'");
	$sth->execute;	
	$hash_ref = $sth ->fetchrow_hashref;
	my $group_id = $hash_ref->{group_id};
	my $type = $hash_ref->{type};
	
	$sth = $DBH->prepare("SELECT * FROM flight_group WHERE id='$group_id'");
	$sth->execute;	
	$hash_ref = $sth ->fetchrow_hashref;
	$type = $hash_ref->{type} . ": " . $type;
	
	$sth = $DBH->prepare("SELECT * FROM airplane_type WHERE id='$airplane_type'");
	$sth->execute;	
	$hash_ref = $sth ->fetchrow_hashref;
	my $airplane = $hash_ref->{type};

	$page_str =~ s/\[name\]/$vorname $nachname/g;
	$page_str =~ s/\[adresse\]/$strasse, $plz $ort/g;
	$geb_datum = &date_to_display($geb_datum);
	$page_str =~ s/\[geb_datum\]/$geb_datum/g;
	$page_str =~ s/\[lizenz\]/$lizenz/g;
	$page_str =~ s/\[passwort\]/$passwort/g;
	$page_str =~ s/\[email\]/<A HREF=\"mailto:$email\">$email<\/A>/g;
	my $file_str;
	if ($file eq '00000000.igc') { $file_str = ""; }
	$page_str =~ s/\[file\]/$file_str/g;
	
	my $logger = "";
	if (length($file) == 12) { # alter logger
		$logger = &igc_logger(substr($file,3,1));
	} else { # neuer logger
		$logger = &igc_logger(substr($file,12,1)); # neuer Logger liefert einen Dateinamen mit 24 Zeichen z.B. 2017-11-20-LXV-EOB-01.igc 
	}

	$page_str =~ s/\[logger\]/$logger/g;
	$date_uploaded = &date_to_display($date_uploaded);
	$page_str =~ s/\[date_uploaded\]/$date_uploaded/g;
	$date_flight = &date_to_display($date_flight);
	$page_str =~ s/\[date_flight\]/$date_flight/g;
	$page_str =~ s/\[type\]/$type/g;
	$page_str =~ s/\[airplane\]/$airplane/g;
	$page_str =~ s/\[witness\]/$witness/g;
	$page_str =~ s/\[recognized\]/$recognized/g;
	
	my $reg_id_str = "";
	
	if ($reg_id) { $reg_id_str = "<TR>
					<TD>Registrationsnummer</TD>
					<TD>$reg_id</TD>
					<TD>&nbsp;<\/TD>
					<TD>&nbsp;<\/TD>
				</TR>"; }
	$page_str =~ s/\[reg_id_str\]/$reg_id_str/g;

	my $pict_str = "<TR>
					<TD WIDTH=\"1\" HEIGHT=\"5\" COLSPAN=\"4\"><IMG SRC=\"\/igc\/images\/leer.gif\" WIDTH=\"1\" HEIGHT=\"5\" BORDER=\"0\" ALT=\"\"><\/TD>
				<\/TR><TR>
					<TD COLSPAN=\"2\" VALIGN=\"top\">[map_txt]<BR><BR>[map_ref]<\/TD>
					<TD COLSPAN=\"2\" VALIGN=\"top\">[baro_txt]<BR><BR>[baro_ref]<\/TD>
				<\/TR>";
	my $pict_path = "/igc/data/map_" . $id . ".gif";
	my $abs_pict_path = $ENV{DOCUMENT_ROOT} . "/" . $pict_path;
	my $pict_ref = "<A HREF=\"javascript:void(0)\" onClick=\"window.open(\'igc.woa?cmd=show_pict&pict=$pict_path\',\'Map\',\'width=806,height=500,scrollbars=yes,resizable=yes\')\"><IMG SRC=\"$pict_path\" WIDTH=\"280\" HEIGHT=\"164\" BORDER=\"0\" ALT=\"\"><\/A>";
	if (-e $abs_pict_path) {
		$pict_str =~ s/\[map_ref\]/$pict_ref/g;
		$pict_str =~ s/\[map_txt\]/Karte vom Flug/g;
	} else {
		$pict_str =~ s/\[map_ref\]//g;
		$pict_str =~ s/\[map_txt\]//g;
	}
	
	$pict_path = "/igc/data/baro_" . $id . ".gif";
	$abs_pict_path = $ENV{DOCUMENT_ROOT} . "/" . $pict_path;
	$pict_ref = "<A HREF=\"javascript:void(0)\" onClick=\"window.open(\'igc.woa?cmd=show_pict&pict=$pict_path\',\'Baro\',\'width=806,height=449,scrollbars=yes,resizable=yes\')\"><IMG SRC=\"$pict_path\" WIDTH=\"280\" HEIGHT=\"146\" BORDER=\"0\" ALT=\"\"><\/A>";
	if (-e $abs_pict_path) {
		$pict_str =~ s/\[baro_ref\]/$pict_ref/g;
		$pict_str =~ s/\[baro_txt\]/Barogramm vom Flug/g;
	} else {
		$pict_str =~ s/\[baro_ref\]//g;
		$pict_str =~ s/\[baro_txt\]//g;
	}
	$page_str =~ s/\[pict_str\]/$pict_str/g;
	
	my $file_path = $ENV{DOCUMENT_ROOT} . "/igc/data/" . $file;
	my $glider_id;
	my $buffer = "";
	open(FILE, "$file_path") or die "Kann den Pfad $file_path nicht oeffnen: $!\n" ;
	while (defined ($buffer = <FILE>)) { 
		if ($buffer =~ /HFGIDGLIDER ID(.+?)/i) {
			$glider_id = substr($buffer, 15, 7);		
		} elsif ($buffer =~ /HFGIDGLIDERID(.+?)/i) {
			$glider_id = substr($buffer, 14, 7);		
		} elsif ($buffer =~ /HFGID Glider ID(.+?)/i) {
			$glider_id = substr($buffer, 16, 7);		
		}
	}
	close(FILE) ;
	$page_str =~ s/\[glider_id\]/$glider_id/g;
	
	$sth->finish;
	
	$query->print($query->header(-expires=>'-1d'));
	$query->print($page_str);
}

sub show_detail_admin() {
	my $id = defined ($query->param('id')) ? $query->param('id') : '';
	
	# pruefe ob eingeloggt, sonst login ueber routine auth_ok
	my $license = &admin_ok($id);

	my $page_str = &file_to_string("templates/details_admin.html");

	my $sth = $DBH->prepare("SELECT * FROM igc WHERE id = '$id'");
	$sth->execute;
	my $hash_ref = $sth->fetchrow_hashref;
	my $user_id = $hash_ref->{user_id};
	my $file = $hash_ref->{file};
	my $date_uploaded = $hash_ref->{date_uploaded};
	my $date_flight = $hash_ref->{date_flight};
	my $date_reg = $hash_ref->{date_reg};
	my $flight_subgroup = $hash_ref->{flight_subgroup};
	my $airplane_type = $hash_ref->{airplane_type};
	my $witness = &get_witness($hash_ref->{witness});
	my $recognized = $hash_ref->{recognized};
	my $reg_id = $hash_ref->{reg_id};

	$sth = $DBH->prepare("SELECT * FROM user WHERE user_id = '$user_id'");
	$sth->execute;
	$hash_ref = $sth->fetchrow_hashref;
	my $vorname = $hash_ref->{vorname};
	my $nachname = $hash_ref->{nachname};
	my $plz = $hash_ref->{plz};
	my $ort = $hash_ref->{ort};
	my $strasse = $hash_ref->{strasse};
	my $geb_datum = $hash_ref->{geb_datum};
	my $lizenz = $hash_ref->{lizenz};
	my $passwort = $hash_ref->{passwort};
	my $email = $hash_ref->{email};
	my $tel = $hash_ref->{tel};
	
	$sth = $DBH->prepare("SELECT * FROM flight_subgroup WHERE id='$flight_subgroup'");
	$sth->execute;	
	$hash_ref = $sth ->fetchrow_hashref;
	my $group_id = $hash_ref->{group_id};
	my $type = $hash_ref->{type};
	
	$sth = $DBH->prepare("SELECT * FROM flight_group WHERE id='$group_id'");
	$sth->execute;	
	$hash_ref = $sth ->fetchrow_hashref;
	$type = $hash_ref->{type} . ": " . $type;
	
	$sth = $DBH->prepare("SELECT * FROM airplane_type WHERE id='$airplane_type'");
	$sth->execute;	
	$hash_ref = $sth ->fetchrow_hashref;
	my $airplane = $hash_ref->{type};	

	$page_str =~ s/\[id\]/$id/g;
	$page_str =~ s/\[name\]/$vorname $nachname/g;
	$page_str =~ s/\[adresse\]/$strasse, $plz $ort/g;
	$geb_datum = &date_to_display($geb_datum);
	$page_str =~ s/\[geb_datum\]/$geb_datum/g;
	$page_str =~ s/\[lizenz\]/$lizenz/g;
	$page_str =~ s/\[passwort\]/$passwort/g;
	$page_str =~ s/\[email\]/<A HREF=\"mailto:$email\">$email<\/A>/g;
	$page_str =~ s/\[tel\]/$tel/g;
	my $file_str = $file;
	if ($file eq '00000000.igc') { $file_str = ""; }
	$page_str =~ s/\[file\]/$file_str/g;
	$page_str =~ s/\[file\]/$file/g;
	
	my $logger = "";
	if (length($file) == 12) { # alter logger
		$logger = &igc_logger(substr($file,3,1));
	} else { # neuer logger
		$logger = &igc_logger(substr($file,12,1)); # neuer Logger liefert einen Dateinamen mit 24 Zeichen z.B. 2017-11-20-LXV-EOB-01.igc
	}
	
	$page_str =~ s/\[logger\]/$logger/g;
	$date_uploaded = &date_to_display($date_uploaded);
	$page_str =~ s/\[date_uploaded\]/$date_uploaded/g;
	$date_flight = &date_to_display($date_flight);
	$page_str =~ s/\[date_flight\]/$date_flight/g;
	$date_reg = &date_to_display($date_reg);
	$page_str =~ s/\[date_reg\]/$date_reg/g;
	$page_str =~ s/\[type\]/$type/g;
	$page_str =~ s/\[airplane\]/$airplane/g;
	$page_str =~ s/\[witness\]/$witness/g;
	$page_str =~ s/\[reg_id\]/$reg_id/g;
	
	my $pict_path = "/igc/data/map_" . $id . ".gif";
	my $abs_pict_path = $ENV{DOCUMENT_ROOT} . "/" . $pict_path;
	if (-e $abs_pict_path) {
		my $pict_str = "aktuelles Bild: <A HREF=\"$pict_path\">" . "map_" . $id . ".gif</A>";
		$page_str =~ s/\[map\]/$pict_str/g;
	} else {
		$page_str =~ s/\[map\]//g;
	}
	
	$pict_path = "/igc/data/baro_" . $id . ".gif";
	$abs_pict_path = $ENV{DOCUMENT_ROOT} . "/" . $pict_path;
	if (-e $abs_pict_path) {
		my $pict_str = "aktuelles Bild: <A HREF=\"$pict_path\">" . "baro_" . $id . ".gif</A>";
		$page_str =~ s/\[baro\]/$pict_str/g;
	} else {
		$page_str =~ s/\[baro\]//g;
	}
	
	my $ok = '<INPUT NAME="ok" TYPE="checkbox" VALUE="1">';
	if ($recognized == 1) { $ok = '<INPUT NAME="ok" TYPE="checkbox" VALUE="1" CHECKED>'; }
	$page_str =~ s/\[ok\]/$ok/g;
	
	my $file_path = $ENV{DOCUMENT_ROOT} . "/igc/data/" . $file;
	my $glider_id;
	my $buffer = "";
	open(FILE, "$file_path") or die "Kann den Pfad $file_path nicht oeffnen: $!\n" ;
	while (defined ($buffer = <FILE>)) { 
		if ($buffer =~ /HFGIDGLIDER ID\:(.+?)/i) {
			$glider_id = substr($buffer, 15, 7);		
		} elsif ($buffer =~ /HFGIDGLIDERID(.+?)/i) {
			$glider_id = substr($buffer, 14, 7);		
		} elsif ($buffer =~ /HFGID Glider ID(.+?)/i) {
			$glider_id = substr($buffer, 16, 7);		
		}
	}
	close(FILE) ;
	$page_str =~ s/\[glider_id\]/$glider_id/g;
	
	$sth->finish;
	
	$query->print($query->header(-expires=>'-1d'));
	$query->print($page_str);
}

sub show_detail_user() {
	my $user_id = defined ($query->param('id')) ? $query->param('id') : '';
	
	# pruefe ob eingeloggt, sonst login ueber routine auth_ok
	my $license = &admin_ok($user_id);

	my $page_str = &file_to_string("templates/details_user.html");

	my $sth = $DBH->prepare("SELECT * FROM user WHERE user_id = '$user_id'");
	$sth->execute;
	my $hash_ref = $sth->fetchrow_hashref;
	my $vorname = $hash_ref->{vorname};
	my $nachname = $hash_ref->{nachname};
	my $plz = $hash_ref->{plz};
	my $ort = $hash_ref->{ort};
	my $strasse = $hash_ref->{strasse};
	my $geb_datum = $hash_ref->{geb_datum};
	my $lizenz = $hash_ref->{lizenz};
	my $passwort = $hash_ref->{passwort};
	my $email = $hash_ref->{email};
	my $tel = $hash_ref->{tel};

	$page_str =~ s/\[id\]/$user_id/g;
	$page_str =~ s/\[vorname\]/$vorname/g;
	$page_str =~ s/\[nachname\]/$nachname/g;
	$page_str =~ s/\[strasse\]/$strasse/g;
	$page_str =~ s/\[plz\]/$plz/g;
	$page_str =~ s/\[ort\]/$ort/g;
	$geb_datum = &date_to_display($geb_datum);
	$page_str =~ s/\[geb_datum\]/$geb_datum/g;
	$page_str =~ s/\[lizenz\]/$lizenz/g;
	$page_str =~ s/\[passwort\]/$passwort/g;
	$page_str =~ s/\[email\]/$email/g;
	$page_str =~ s/\[tel\]/$tel/g;
	
	$sth->finish;
	
	$query->print($query->header(-expires=>'-1d'));
	$query->print($page_str);
}

sub show_pict() {
	my $page_str = &file_to_string("templates/show_pict.html");
	my $pict = $query->param('pict');

	$page_str =~ s/\[pict\]/$pict/g;
	
	$query->print($query->header(-expires=>'-1d'));
	$query->print($page_str);
}

sub list_witness() {
	my $count = 0;
	my $not_found_msg = "Name nicht gefunden!";
	
	my $search_name = (defined $query->param('search_name')) ? $query->param('search_name') : '%';
	my $pos = (defined $query->param('pos')) ? $query->param('pos') : '0';
	
	my $lv = (defined $query->param('sz_lv')) ? $query->param('sz_lv') : '0';
	my $lv_criteria = "%";
	my $sz_id_mode = ">";
	my $sz_id_length = 3;
	if ($lv eq "KO") {
#		$sz_id_mode = "=";
#		$lv_criteria = "SK%";
#		$sz_id_length = 4;
		$sz_id_mode = "=";
		$lv_criteria = "K%";
		$sz_id_length = 3;
	} elsif ($lv eq "S") {
		$sz_id_mode = "=";
		$lv_criteria = "S%";
		$sz_id_length = 4;
	} elsif ($lv ne "0") {
		$lv_criteria = $lv . "%";
	}
	
	my $page_str = &file_to_string("templates/list_witness.html");
	
# 	if ($search_name eq "") {
# 		$search_name = "undefined_value";
# 	} elsif ($search_name ne "%") {
# 		$pos = 0;
# 	}

	my $year = (localtime)[5] + 1900;
	
	#if ($search_name) {
		my $sth = $DBH->prepare("SELECT count(*) FROM witness WHERE (nachname LIKE '%$search_name%' OR vorname LIKE '%$search_name%') AND id>'0' AND valid>='$year' AND sz_id LIKE '$lv_criteria' AND LENGTH(sz_id) " . $sz_id_mode . " " . $sz_id_length . " ORDER BY nachname");
		
		$sth->execute;
		$count = $sth->fetchrow_array;
		$sth->finish;
	#}
	
	if ($count == 0) { 
		$page_str =~ s/\[page_bar\]//g;
		$page_str =~ s/\[page_dn\]//g;
		$page_str =~ s/\[page_up\]//g;
	}
	
	if ($count < $page_size) { $pos = 0; }
	
	my $page_bar = sprintf("%d - %d von %d", $pos+1, &min($pos+$page_size, $count), $count);
	$page_str =~ s/\[page_bar\]/$page_bar/g;
	
	my $down_pos = &max(0, $pos - $page_size);
	my $up_pos = &min($pos + $page_size, $count);

	my $page_dn = ($pos - $page_size >= 0) ? "<A HREF=\"igc.woa\?cmd=list_witness&search_name=$search_name&sz_lv=$lv&pos=$down_pos\"><IMG SRC=\"/igc/images/left.gif\" ALT=\"\" WIDTH=\"8\" HEIGHT=\"16\" BORDER=\"0\"></A>" : "<IMG SRC=\"/igc/images/left_off.gif\" ALT=\"\" WIDTH=\"8\" HEIGHT=\"16\" BORDER=\"0\">";
	$page_str =~ s/\[page_dn\]/$page_dn/g;
	my $page_up = ($pos + $page_size <$count) ? "<A HREF=\"igc.woa\?cmd=list_witness&search_name=$search_name&sz_lv=$lv&pos=$up_pos\"><IMG SRC=\"/igc/images/right.gif\" ALT=\"\" WIDTH=\"8\" HEIGHT=\"16\" BORDER=\"0\"></A>" : "<IMG SRC=\"/igc/images/right_off.gif\" ALT=\"\" WIDTH=\"8\" HEIGHT=\"16\" BORDER=\"0\">";
	$page_str =~ s/\[page_up\]/$page_up/g;

	my $sth = $DBH->prepare("SELECT * FROM witness WHERE (nachname LIKE '%$search_name%' OR vorname LIKE '%$search_name%') AND id>'0' AND valid>='$year' AND sz_id LIKE '$lv_criteria' AND LENGTH(sz_id) " . $sz_id_mode . " " . $sz_id_length . "  ORDER BY nachname LIMIT $pos," . $page_size);
	$sth->execute;

	$page_str =~ s/\[pos\]/$pos/g;
	if ($search_name eq '%') {
		$page_str =~ s/\[search_name\]//g;
	} else {
		$page_str =~ s/\[search_name\]/$search_name/g;
	}
	
# 	if ($search_name eq 'undefined_value') {
# 		$not_found_msg = "Bitte Nachnamen in Suchfeld eingeben!";
# 		$page_str =~ s/\[search_name\]//g;
# 	} elsif ($search_name eq '%') {
# 		$page_str =~ s/\[search_name\]//g;
# 	} else {
# 		$page_str =~ s/\[search_name\]/$search_name/g;
# 	}
	
	my $lv_menu = &build_sz_lv_menu($lv);
	$page_str =~ s/\[lv_menu\]/$lv_menu/g;
	
	my $list_str = '<TABLE WIDTH="600" CELLSPACING="0" CELLPADDING="0" BORDER="0">';

	my $row_bg = 0;
	my $hash_ref;
	my $length;
	
	while ($hash_ref = $sth->fetchrow_hashref) {
		my $sz_id = $hash_ref->{sz_id};
		my $verein = $hash_ref->{verein};
		my $nachname = $hash_ref->{nachname};
		my $vorname = $hash_ref->{vorname};
		
# Color the Backgroud Cell
		my $tdstyle = ($row_bg++%2 eq 0) ? "#EDF6FF" : "#CDE6FF";

		my $local_row = "<TR><TD WIDTH=\"130\" BGCOLOR=\"[tdstyle]\" CLASS=\"pagelist\">[nachname]</TD>
		<TD WIDTH=\"130\" BGCOLOR=\"[tdstyle]\" CLASS=\"pagelist\">[vorname]</TD>
		<TD WIDTH=\"110\" BGCOLOR=\"[tdstyle]\" CLASS=\"pagelist\">[sz_id]</TD>
		<TD WIDTH=\"230\" BGCOLOR=\"[tdstyle]\" CLASS=\"pagelist\">[verein]</TD></TR>";

		$local_row =~ s/\[tdstyle\]/$tdstyle/g;
		$local_row =~ s/\[sz_id\]/$sz_id/g;
		$local_row =~ s/\[verein\]/$verein/g;
		$local_row =~ s/\[nachname\]/$nachname/g;
		$local_row =~ s/\[vorname\]/$vorname/g;
		
		$list_str .= $local_row;
	}

	if ($count == 0) {
# Color the Backgroud Cell
		my $tdstyle = ($row_bg++%2 eq 0) ? "#EDF6FF" : "#CDE6FF";
		my $local_row = "<TR><TD WIDTH=\"600\" COLSPAN=\"4\" BGCOLOR=\"[tdstyle]\" CLASS=\"pagelist\">$not_found_msg</TD></TR>";
		$local_row =~ s/\[tdstyle\]/$tdstyle/g;
		$list_str .= $local_row;
	}

	while ($row_bg < $page_size) {
# Color the Backgroud Cell
		my $tdstyle = ($row_bg++%2 eq 0) ? "#EDF6FF" : "#CDE6FF";
		my $local_row = "<TR><TD WIDTH=\"600\" COLSPAN=\"4\" BGCOLOR=\"[tdstyle]\" CLASS=\"pagelist\">&nbsp;</TD></TR>";
		$local_row =~ s/\[tdstyle\]/$tdstyle/g;
		$list_str .= $local_row;
	}
	
	$list_str .= "</TABLE>";
	$page_str =~ s/\[user table\]/$list_str/g;
	
	my $alle_zeigen = "&#187; alle zeigen";
	if ($search_name eq "") { $alle_zeigen = "" }
	$page_str =~ s/\[alle_zeigen\]/$alle_zeigen/g;

	$query->print($query->header(-expires=>'-1d'));
	$query->print($page_str);
	
	$sth->finish;
}

sub login() {
	my $page_str = &file_to_string("templates/login.html");
	my $lizenz = defined ($query->param('lizenz')) ? $query->param('lizenz') : '';

	$page_str =~ s/\[lizenz\]/$lizenz/g;		
	$page_str =~ s/\[passwort\]//g;
	$page_str =~ s/\[errormsg\]//g;
	
	$query->print($query->header(-expires=>'-1d'));
	$query->print($page_str);
}

sub do_login() {
	my $lizenz = $query->param('lizenz');
	my $passwort = $query->param('passwort');
	my $link = (defined $query->param('link')) ? $query->param('link') : 'announce';
	my $errormsg;
	
	if (!$lizenz) {
		$errormsg .= "Geben Sie Ihre Sportlizenznummer ein. ";
	}

	if (!$passwort) {
		$errormsg .= "Geben Sie Ihr Passwort ein. ";
	}
	
	if ($lizenz && $passwort) {
		my $sth = $DBH->prepare("SELECT * FROM user WHERE lizenz='$lizenz'");
		$sth->execute;	
		my $hash_ref = $sth->fetchrow_hashref;
		my $pass = $hash_ref->{passwort};
		$sth->finish;
	 	
	 	if (!($passwort eq $pass)) {
			$errormsg .= "Ihre Nummer/Passwort Kombination ist falsch. ";
		}
	}

	if ($errormsg) {
		my $page_str = &file_to_string("templates/login.html");
		
		$page_str =~ s/\[errormsg\]/$errormsg/g;
		$page_str =~ s/\[lizenz\]/$lizenz/g;		
		$page_str =~ s/\[passwort\]/$passwort/g;
		
		$query->print($query->header(-expires=>'-1d'));
		$query->print($page_str);
		return;
	}

	$sid = &generate_id($lizenz);
	$query->print( $query->redirect(-cookie=>save_state($sid),-location=>"igc.woa?cmd=$link") );
}

sub login_admin() {
	my $id = $query->param('id');
	my $page_str = &file_to_string("templates/login_admin.html");
	
	$page_str =~ s/\[id\]/$id/g;
	$page_str =~ s/\[pw_name\]//g;		
	$page_str =~ s/\[pw_passwd\]//g;
	$page_str =~ s/\[errormsg\]//g;

	$query->print($query->header(-expires=>'-1d'));
	$query->print($query->header(-expires=>'-1d'));
	$query->print($page_str);	
}

sub do_login_admin() {
	my $id = $query->param('id');
	my $pw_name = $query->param('pw_name');
	my $pw_passwd = $query->param('pw_passwd');
	my $errormsg;
	my $link = "igc.woa?cmd=admin";
	
	if ($id) {
		$link = "igc.woa?cmd=show_detail_admin&id=$id";
	} 
	
	if (!$pw_name) {
		$errormsg .= "Geben Sie Ihren Benutzernamen ein. ";
	} 

	if (!$pw_passwd) {
		$errormsg .= "Geben Sie Ihr Passwort ein. ";
	}
	
	if ($pw_name && $pw_passwd) {
		my $sth = $DBH->prepare("SELECT * FROM AuthAdmin WHERE pw_name='$pw_name'");
		$sth->execute;	
		my $hash_ref = $sth->fetchrow_hashref;
		my $passwd = $hash_ref->{pw_passwd};
		$sth->finish;
	 	
	 	if (!($pw_passwd eq $passwd)) {
			$errormsg .= "Ihre User-ID/Password Kombination ist falsch. ";
		}
	}

	if ($errormsg) {
		my $page_str = &file_to_string("templates/login_admin.html");
	
		$page_str =~ s/\[errormsg\]/$errormsg/g;
		$page_str =~ s/\[pw_name\]/$pw_name/g;		
		$page_str =~ s/\[pw_passwd\]//g;

		$query->print($query->header(-expires=>'-1d'));
		$query->print($page_str);	
		return;
	}

	$sid = &generate_id($pw_name);
	$query->print( $query->redirect(-cookie=>save_state($sid),-location=>"$link") );
}

sub logout() {
	$query->print( $query->redirect(-cookie=>quit_state($sid),-location=>"http://www.onf-online.at/") );
}

# div. lib functions
sub check_name() {
	my $text = $_[0];
	if ($text !~ /^[a-zA-Z\-\.\äÄüÜöÖß\ ]*$/ ||
		$text =~ /^$/ || 						# $text darf nicht leer sein
		$text =~ /^ | {2,}| $/) {		# $text darf keine Leerzeichen zu Beginn oder Ende enthalten oder mehr als 1 Leerzeichen hintereinander haben
		return 0;
	} else {
		return 1;
	}
}

sub error_mod() {
	my $page_str = &file_to_string("templates/error_mod.html");
	my $error = $query->param('error');
	my $path = $query->param('path');
	
	$page_str =~ s/\[error\]/$error/g;
	$page_str =~ s/\[path\]/$path/g;

	$query->print($query->header(-expires=>'-1d'));
	$query->print($page_str);
}

sub error_igc() {
	my $page_str = &file_to_string("templates/error_igc.html");
	my $error = $query->param('error');
	my $path = $query->param('path');
	
	$page_str =~ s/\[error\]/$error/g;
	$page_str =~ s/\[path\]/$path/g;

	$query->print($query->header(-expires=>'-1d'));
	$query->print($page_str);
}

sub check_field() {
	my $text = $_[0];
	if ($text !~ /^[a-zA-Z0-9\-\.\_\äÄüÜöÖß\/\ ]*$/ ||
		$text =~ /^$/ || 						# $text darf nicht leer sein
		$text =~ /^ | {2,}| $/) {		# $text darf keine Leerzeichen zu Beginn oder Ende enthalten oder mehr als 1 Leerzeichen hintereinander haben
		return 0;
	} else {
		return 1;
	}
}

sub check_passwort() {
	my $text = $_[0];
	if ($text !~ /^[a-zA-Z0-9\-\.\_]*$/ ||
		$text =~ /^$/ || 					# $text darf nicht leer sein
		$text =~ /[\s]|,/) {			# $passwort darf keine Leerzeichen enthalten
		return 0;
	} else {
		return 1;
	}
}

sub check_plz() {
	my $plz = $_[0];
	if ($plz =~ /\d{4}/) {
		return 1;
	} else {
		return 0;
	}
}

sub check_datum() {
	my $datum = $_[0];
	my @part = split /\./,$datum;
	
	my $year = (localtime)[5] + 1900;
	
	if ($datum eq '31.12.2099') {
		return 0;
	} elsif ($datum =~ /(^\d{2}\.\d{2}\.\d{4})/) {
		if ($part[0] > 31) { return 0; }
		elsif ($part[1] > 12) { return 0; }
		elsif ($part[2] < 1900) { return 0; }
		elsif ($part[2] > ($year - 12)) { return 0; }				# mindestens 12 Jahre alt
		elsif ($part[1] == 2) { if ($part[0] > 29) { return 0; } }
		else { return 1; }
	} else {
		return 0;
	}
}

sub check_email() {
	my $email = $_[0];
	if ($email eq 'name@domain.at') {
		return 1;
	} elsif ($email =~ /(@.*@)|(\.\.)|(@\.)|(\.@)|(^\.)/ ||
		$email !~ /^.+\@(\[?)[a-zA-Z0-9\-\.]+\.([a-zA-Z]{2,3}|[0-9]{1,3})(\]?)$/ ||
		$email =~ /^$/ || 				# $email darf nicht leer sein
		$email =~ /[\s]|,/) {			# $email darf keine Leerzeichen enthalten
		return 0;
	} else {
		return 1;
	}
}

sub check_file() {
	my $text = $_[0];
	if ($text !~ /^([a-zA-Z0-9\-]+)(\.igc$)/i || # $text bestimmte Zeichen min. 1x enthalten + .igc sein
		$text =~ /^$/ || 					# $text darf nicht leer sein
		$text =~ /[\s]|,/) {			# $text darf keine Leerzeichen enthalten
		return 0;
	} else {
		return 1;
	}
}

sub date_to_display() {
	my $datum = $_[0];
	$datum =~ s/-/\./g;	# tausche "-" gegen "."
	my @part = split /\./,$datum;
	$datum = $part[2] . '.' . $part[1] . '.' . $part[0];
	return ($datum);
}

sub date_to_database() {
	my $datum = $_[0];
	$datum =~ s/\./\-/g; 	# tausche "." gegen "-"
	my @part = split /-/,$datum;
	$datum = $part[2] . '-' . $part[1] . '-' . $part[0];
	return ($datum);
}

sub get_flights {
	# this routine returns recognized flights only
	my ($user_id) = @_;
	my $flights = 0;
	my $count = 0;
	my $reg_id_table;
	
	my $sth = $DBH->prepare("SELECT count(*) FROM igc WHERE user_id = '$user_id'");
	$sth->execute;
	$count = $sth->fetchrow_array;
	
	$sth = $DBH->prepare("SELECT recognized,reg_id FROM igc WHERE user_id = '$user_id'");
	$sth->execute;
	my $hash_ref;
	while ($hash_ref = $sth->fetchrow_hashref) {
		my $recognized = $hash_ref->{recognized};
		if ($recognized == 1) {
			$flights += 1;
			my $reg_id = $hash_ref->{reg_id};
			$reg_id =~ s/^\s+//g;
			$reg_id =~ s/\s+$//g;
			$reg_id_table .= $reg_id;
			if ($count > 1) {
				if ($reg_id ne '') { $reg_id_table .= ", "; }
			}
		}
	}
	$sth->finish;

	$reg_id_table =~ s/, *$//;

	return($flights,$reg_id_table);
}

sub get_flights_neu {
	# this routine returns recognized flights only
	my ($subgroup_id, $user_id) = @_;
	my $flights;
		
	my $sth = $DBH->prepare("SELECT count(*) FROM igc,flight_subgroup WHERE igc.user_id = '$user_id' AND igc.flight_subgroup = flight_subgroup.id AND flight_subgroup.id =  '$subgroup_id'");
	$sth->execute;
	$flights = $sth->fetchrow_array;
	
	return($flights);
}

sub get_flights_all {
	# this routine returns recognized flights only
	my ($group_id, $user_id, $year) = @_;
	my $flights;
	if ($year ne "0") { $year = $year . "%"; } else { $year = "%"; }

		
	my $sth = $DBH->prepare("SELECT count(*) FROM igc,flight_group, flight_subgroup WHERE igc.user_id = '$user_id' AND igc.flight_subgroup = flight_subgroup.id AND flight_subgroup.group_id = flight_group.id AND flight_group.id = '$group_id' AND igc.date_flight LIKE '$year%'");
	$sth->execute;
	$flights = $sth->fetchrow_array;
	
	return($flights);
}

sub get_igc_date {
	# this routine returns the date from igc
	my ($id) = @_;
	
	my $sth = $DBH->prepare("SELECT date_flight FROM igc WHERE id = '$id'");
	$sth->execute;
	my $hash_ref = $sth->fetchrow_hashref;
	my $date = $hash_ref->{date_flight};
	$sth->finish;

	return($date);
}

sub get_fligh_date {
	# not done!!!
	my ($user_id) = @_;
	my $flights = 0;
	
	my $sth = $DBH->prepare("SELECT * FROM igc WHERE user_id LIKE '$user_id'");
	$sth->execute;
	my $hash_ref;
	
	while ($hash_ref = $sth->fetchrow_hashref) {
		my $user_id = $hash_ref->{user_id};
		
		my $sth1 = $DBH->prepare("SELECT * FROM igc WHERE user_id LIKE '$user_id'");
		$sth1->execute;
		my $hash_ref1;
		$sth1->finish;
		
		my $sth1 = $DBH->prepare("SELECT count(*) FROM igc WHERE user_id LIKE '$user_id'");
		$sth1->execute;
		$flights = $sth->fetchrow_array;
		$sth1->finish;
		
	}
	$sth->finish;

	return($flights);
}

sub get_flight_subgroup {
	# this routine returns flight subgroup
	my ($id) = @_;
	my $type;
	
	my $sth = $DBH->prepare("SELECT type FROM flight_subgroup WHERE id = '$id'");
	$sth->execute;
	my $hash_ref = $sth->fetchrow_hashref;
	$type = $hash_ref->{type};
	$sth->finish;

	return($type);
}

sub get_flight_type {
	# this routine returns flight type
	my ($id) = @_;
	my $type;
	
	my $sth = $DBH->prepare("SELECT flight_subgroup.type AS subgroup, flight_group.type AS maingroup FROM flight_subgroup,flight_group WHERE flight_subgroup.id = '$id' AND flight_subgroup.group_id = flight_group.id");
	$sth->execute;
	my $hash_ref = $sth->fetchrow_hashref;
	$type = $hash_ref->{maingroup} . " - " . $hash_ref->{subgroup};
	$sth->finish;

	return($type);
}

sub get_witness {
	# this routine returns witness name
	my ($id) = @_;
	my $witness;
	
	my $sth = $DBH->prepare("SELECT sz_id,nachname,vorname FROM witness WHERE id = '$id'");
	$sth->execute;
	my $hash_ref = $sth->fetchrow_hashref;
	$witness = $hash_ref->{nachname} . " " . $hash_ref->{vorname} . " (" . $hash_ref->{sz_id} . ")";
	$sth->finish;

	return($witness);
}

sub passwort() {
	my $page_str = &file_to_string("templates/passwort.html");
	
	$query->print($query->header(-expires=>'-1d'));
	$query->print($page_str);
}

sub passwort_send() {
	my $page_str = &file_to_string("templates/passwort.html");
	my $lizenz = $query->param('lizenz');

	# prüfen ob lizenznummer in user vorkommt
	my $sth = $DBH->prepare("SELECT * FROM user WHERE lizenz LIKE '$lizenz'");
	$sth->execute;
	my $hash_ref = $sth->fetchrow_hashref;
	my $name = $hash_ref->{vorname} . " " . $hash_ref->{nachname};
	my $email = $hash_ref->{email};
	my $passwort = $hash_ref->{passwort};
	$sth->finish;
	
	# sende email wenn email fŸr user gefunden wurde
	if ($email ne "") {

	my $text = "Sehr geehrte/r $name!\n
Sie haben ihr Passwort angefordert!\n
Bitte bewahren sie die folgenden Login-Daten auf:
   Sportlizenznummer: $lizenz
   Passwort:          $passwort
   E-Mail Adresse:    $email\n
Wir wünschen ihnen weiterhin viel Erfolg!\n
http://www.onf-online.at/";

		my $sender = 'info@onf-online.at';
		
		my $msg = MIME::Lite->new(From =>"IGC-Datenbank <$sender>", To => $email, Subject =>"Passwort Anforderung", Type =>'multipart/mixed');
		$msg->attach(Type =>'text/plain; charset=iso-8859-1', Data =>"$text" );
		$msg->send('smtp','smtp.hot24.at', SSL => 1, Port => 25, AuthUser=>'info@onf-online.at', AuthPass=>'5SC7tpW#mk');
	} else {
		$query->print( $query->redirect(-location=>"igc.woa?cmd=passwort_error") );
		exit;
	}
	
	$query->print( $query->redirect(-location=>"igc.woa?cmd=passwort_sent&lizenz=$lizenz") );
}

sub wartung() {
	my $page_str = &file_to_string("templates/wartung.html");
	
	$query->print($query->header(-expires=>'-1d'));
	$query->print($page_str);
}

sub passwort_error() {
	my $page_str = &file_to_string("templates/passwort_error.html");
	
	$query->print($query->header(-expires=>'-1d'));
	$query->print($page_str);
}

sub passwort_sent() {
	my $lizenz = $query->param('lizenz');
	my $page_str = &file_to_string("templates/passwort_sent.html");
	
	$page_str =~ s/\[lizenz\]/$lizenz/g;
	
	$query->print($query->header(-expires=>'-1d'));
	$query->print($page_str);
}

sub build_flug_menu() {
	my ($selected) = @_;
	my $counter = 0;
	my $sth = $DBH->prepare("SELECT * FROM flight_group ORDER BY id");
	$sth->execute;

	my $flug_menu = '<SELECT NAME="type" CLASS="menue">';
	my $hash_ref;
	while ($hash_ref = $sth->fetchrow_hashref) {
		$flug_menu .= "<OPTGROUP LABEL=\"$hash_ref->{type}\">";
		
		my $sth1 = $DBH->prepare("SELECT * FROM flight_subgroup WHERE group_id LIKE '$hash_ref->{id}' ORDER BY id");
		$sth1->execute;
		
		my $hash_ref1;
		while ($hash_ref1 = $sth1->fetchrow_hashref) {
			my $value = $hash_ref1->{type};
			if ($hash_ref->{type} gt '') { $value = $hash_ref->{type} . ": " . $hash_ref1->{type}; }
			if ($selected eq $counter) {
				$flug_menu .= "<OPTION VALUE=\"$hash_ref1->{id}\" SELECTED>$value</OPTION>\n";
			} else {
				$flug_menu .= "<OPTION VALUE=\"$hash_ref1->{id}\">$value</OPTION>\n";
			}
			$counter += 1;
		}
		$sth1->finish;
		$flug_menu .= '</OPTGROUP>';
	}
	$flug_menu .= '</SELECT>';
	$sth->finish;
	
	return ($flug_menu);
}

sub build_diplom_menu() {
	my ($selected) = @_;
	my $counter = 0;
	
	my $sth = $DBH->prepare("SELECT type FROM flight_subgroup WHERE group_id=4 ORDER BY id");
	$sth->execute;

	my $diplom_menu = '<SELECT NAME="diplom" CLASS="menue">';
	my $hash_ref;
	while ($hash_ref = $sth->fetchrow_hashref) {
		my $value = $hash_ref->{type};
		if ($selected eq $counter) {
			$diplom_menu .= "<OPTION VALUE=\"$hash_ref->{id}\" SELECTED>$value</OPTION>\n";
		} else {
			$diplom_menu .= "<OPTION VALUE=\"$hash_ref->{id}\">$value</OPTION>\n";
		}
		$counter += 1;
	}
		
	$diplom_menu .= '</SELECT>';
	$sth->finish;
	
	return ($diplom_menu);
}

sub build_flugzeug_menu() {
	my ($selected) = @_;
	my $counter = 0;
	my $sth = $DBH->prepare("SELECT * FROM airplane_class ORDER BY id");
	$sth->execute;

	my $flugzeug_menu = '<SELECT NAME="airplane" CLASS="menue">';
	my $hash_ref;
	while ($hash_ref = $sth->fetchrow_hashref) {
		$flugzeug_menu .= "<OPTGROUP LABEL=\"$hash_ref->{class}\">";
		
		my $sth1 = $DBH->prepare("SELECT * FROM airplane_type WHERE class_id LIKE '$hash_ref->{id}' ORDER BY type");
		$sth1->execute;
		
		my $hash_ref1;
		while ($hash_ref1 = $sth1->fetchrow_hashref) {
			if ($selected eq $counter) {
				$flugzeug_menu .= "<OPTION VALUE=\"$hash_ref1->{id}\" SELECTED>$hash_ref1->{type}</OPTION>\n";
			} else {
				$flugzeug_menu .= "<OPTION VALUE=\"$hash_ref1->{id}\">$hash_ref1->{type}</OPTION>\n";
			}
			$counter += 1;
		}
		$sth1->finish;
		$flugzeug_menu .= '</OPTGROUP>';
	}
	$flugzeug_menu .= '</SELECT>';
	$sth->finish;
	
	return ($flugzeug_menu);
}

sub build_witness_menu() {
	my ($selected) = @_;
	my $year = (localtime)[5] + 1900;
	
	my $sth = $DBH->prepare("SELECT id,sz_id,nachname,vorname FROM witness WHERE valid >= '$year' ORDER BY nachname");
	$sth->execute;

	my $witness_menu = '<SELECT NAME="witness" CLASS="menue">';
	my $hash_ref;
	while ($hash_ref = $sth->fetchrow_hashref) {
		my $value = $hash_ref->{nachname} . " " . $hash_ref->{vorname} . " (" . $hash_ref->{sz_id} . ")";
		if ($hash_ref->{id} == 0) { $value = $hash_ref->{nachname}; }
		if ($selected eq $hash_ref->{id}) {
			$witness_menu .= "<OPTION VALUE=\"$hash_ref->{id}\" SELECTED>$value</OPTION>\n";
		} else {
			$witness_menu .= "<OPTION VALUE=\"$hash_ref->{id}\">$value</OPTION>\n";
		}
	}
	
	$witness_menu .= "<OPTION VALUE=\"\">-</OPTION>\n";
	
	if ($selected eq "999") {
		$witness_menu .= "<OPTION VALUE=\"999\" SELECTED>nicht in der Liste</OPTION>\n";
	}
	$witness_menu .= "<OPTION VALUE=\"999\">nicht in der Liste</OPTION>\n";
	
	$witness_menu .= '</SELECT>';
	$sth->finish;
	
	return ($witness_menu);
}

sub build_criteria_menu() {
	my ($selected) = @_;
	my $counter = 1;
	
	my $sth = $DBH->prepare("SELECT * FROM flight_group WHERE id>0 ORDER BY id");
	$sth->execute;

	my $criteria_menu = '<SELECT NAME="criteria" CLASS="menue" ONCHANGE="javascript: document.mainForm.pos.value=0; document.mainForm.submit()">';
	while (my $hash_ref = $sth->fetchrow_hashref) {
		my $value = $hash_ref->{type};
		if ($selected eq $counter) {
			$criteria_menu .= "<OPTION VALUE=\"$hash_ref->{id}\" SELECTED>$value</OPTION>\n";
		} else {
			$criteria_menu .= "<OPTION VALUE=\"$hash_ref->{id}\">$value</OPTION>\n";
		}
		$counter += 1;
	}
		
	$criteria_menu .= '</SELECT>';
	$sth->finish;
	
	return ($criteria_menu);
}

sub build_year_menu() {
	my ($thisyear) = @_;
	my $now = 1900 + (localtime)[5];

	my $criteria_menu = '<SELECT NAME="year" CLASS="menue" ONCHANGE="javascript: document.mainForm.pos.value=0; document.mainForm.submit()">';
	
	if ($thisyear eq "0") {
		$criteria_menu .= "<OPTION VALUE=\"0\" SELECTED>alle</OPTION>\n";
	} else {
		$criteria_menu .= "<OPTION VALUE=\"0\">alle</OPTION>\n";
	}

	for (my $year = $now; $year >= $now - 10; $year--) {
		if ($year eq $thisyear) {
			$criteria_menu .= "<OPTION VALUE=\"$year\" SELECTED>$year</OPTION>\n";
		} else {
			$criteria_menu .= "<OPTION VALUE=\"$year\">$year</OPTION>\n";
		}
	}

	$criteria_menu .= '</SELECT>';
	
	return ($criteria_menu);
}

sub build_lv_menu() {
	my ($lv) = @_;
	
	my $criteria_menu = '<SELECT NAME="lv" CLASS="menue" ONCHANGE="javascript: document.mainForm.pos.value=0; document.mainForm.submit()">';


	if ($lv eq "0") {
		$criteria_menu .= "<OPTION VALUE=\"0\" SELECTED>alle</OPTION>\n";
	} else {
		$criteria_menu .= "<OPTION VALUE=\"0\">alle</OPTION>\n";
	}
	if ($lv eq "1") {
		$criteria_menu .= "<OPTION VALUE=\"1\" SELECTED>Bgl.</OPTION>\n";
	} else {
		$criteria_menu .= "<OPTION VALUE=\"1\">Bgl.</OPTION>\n";
	}
	if ($lv eq "2") {
		$criteria_menu .= "<OPTION VALUE=\"2\" SELECTED>Knt.</OPTION>\n";
	} else {
		$criteria_menu .= "<OPTION VALUE=\"2\">Knt.</OPTION>\n";
	}
	if ($lv eq "3") {
		$criteria_menu .= "<OPTION VALUE=\"3\" SELECTED>N&Ouml;</OPTION>\n";
	} else {
		$criteria_menu .= "<OPTION VALUE=\"3\">N&Ouml;</OPTION>\n";
	}
	if ($lv eq "4") {
		$criteria_menu .= "<OPTION VALUE=\"4\" SELECTED>O&Ouml;</OPTION>\n";
	} else {
		$criteria_menu .= "<OPTION VALUE=\"4\">O&Ouml;</OPTION>\n";
	}
	if ($lv eq "5") {
		$criteria_menu .= "<OPTION VALUE=\"5\" SELECTED>Sbg.</OPTION>\n";
	} else {
		$criteria_menu .= "<OPTION VALUE=\"5\">Sbg.</OPTION>\n";
	}
	if ($lv eq "6") {
		$criteria_menu .= "<OPTION VALUE=\"6\" SELECTED>Stmk.</OPTION>\n";
	} else {
		$criteria_menu .= "<OPTION VALUE=\"6\">Stmk.</OPTION>\n";
	}
	if ($lv eq "7") {
		$criteria_menu .= "<OPTION VALUE=\"7\" SELECTED>Tirol</OPTION>\n";
	} else {
		$criteria_menu .= "<OPTION VALUE=\"7\">Tirol</OPTION>\n";
	}
	if ($lv eq "8") {
		$criteria_menu .= "<OPTION VALUE=\"8\" SELECTED>Vbg.</OPTION>\n";
	} else {
		$criteria_menu .= "<OPTION VALUE=\"8\">Vbg.</OPTION>\n";
	}
	if ($lv eq "9") {
		$criteria_menu .= "<OPTION VALUE=\"9\" SELECTED>Wien</OPTION>\n";
	} else {
		$criteria_menu .= "<OPTION VALUE=\"9\">Wien</OPTION>\n";
	}

	$criteria_menu .= '</SELECT>';
	
	return ($criteria_menu);
}

sub build_sz_lv_menu() {
	my ($lv) = @_;
	
	my $criteria_menu = '<SELECT NAME="sz_lv" CLASS="menue" ONCHANGE="javascript: document.mainForm.pos.value=0; document.mainForm.submit()">';


	if ($lv eq "0") {
		$criteria_menu .= "<OPTION VALUE=\"0\" SELECTED>alle</OPTION>\n";
	} else {
		$criteria_menu .= "<OPTION VALUE=\"0\">alle</OPTION>\n";
	}
	if ($lv eq "B") {
		$criteria_menu .= "<OPTION VALUE=\"B\" SELECTED>Bgl.</OPTION>\n";
	} else {
		$criteria_menu .= "<OPTION VALUE=\"B\">Bgl.</OPTION>\n";
	}
	if ($lv eq "K") {
		$criteria_menu .= "<OPTION VALUE=\"K\" SELECTED>Knt.</OPTION>\n";
	} else {
		$criteria_menu .= "<OPTION VALUE=\"K\">Knt.</OPTION>\n";
	}
	if ($lv eq "N") {
		$criteria_menu .= "<OPTION VALUE=\"N\" SELECTED>N&Ouml;</OPTION>\n";
	} else {
		$criteria_menu .= "<OPTION VALUE=\"N\">N&Ouml;</OPTION>\n";
	}
	if ($lv eq "O") {
		$criteria_menu .= "<OPTION VALUE=\"O\" SELECTED>O&Ouml;</OPTION>\n";
	} else {
		$criteria_menu .= "<OPTION VALUE=\"O\">O&Ouml;</OPTION>\n";
	}
	if ($lv eq "S") {
		$criteria_menu .= "<OPTION VALUE=\"S\" SELECTED>Sbg.</OPTION>\n";
	} else {
		$criteria_menu .= "<OPTION VALUE=\"S\">Sbg.</OPTION>\n";
	}
	if ($lv eq "ST") {
		$criteria_menu .= "<OPTION VALUE=\"ST\" SELECTED>Stmk.</OPTION>\n";
	} else {
		$criteria_menu .= "<OPTION VALUE=\"ST\">Stmk.</OPTION>\n";
	}
	if ($lv eq "T") {
		$criteria_menu .= "<OPTION VALUE=\"T\" SELECTED>Tirol</OPTION>\n";
	} else {
		$criteria_menu .= "<OPTION VALUE=\"T\">Tirol</OPTION>\n";
	}
	if ($lv eq "V") {
		$criteria_menu .= "<OPTION VALUE=\"V\" SELECTED>Vbg.</OPTION>\n";
	} else {
		$criteria_menu .= "<OPTION VALUE=\"V\">Vbg.</OPTION>\n";
	}
	if ($lv eq "W") {
		$criteria_menu .= "<OPTION VALUE=\"W\" SELECTED>Wien</OPTION>\n";
	} else {
		$criteria_menu .= "<OPTION VALUE=\"W\">Wien</OPTION>\n";
	}
	if ($lv eq "KO") {
		$criteria_menu .= "<OPTION VALUE=\"KO\" SELECTED>Kommissare</OPTION>\n";
	} else {
		$criteria_menu .= "<OPTION VALUE=\"KO\">Kommissare</OPTION>\n";
	}

	$criteria_menu .= '</SELECT>';
	
	return ($criteria_menu);
}

sub file_to_string {
	my ($filename) = @_;
	$filename = $ENV{DOCUMENT_ROOT} . "/igc/" . $filename;
	my $row_str;
	undef $row_str;
	
	open (FILE, $filename) or die "Content-type: text/html\n\nFehler beim Lesen der Datei $filename\n";
	while (<FILE>) { $row_str .= $_; }
	close(FILE);

	return($row_str);
}

sub igc_value {
	my %value = ('0','00','1','01','2','02','3','03','4','04','5','05','6','06','7','07','8','08','9','09',
	'A','10','B','11','C','12','D','13','E','14','F','15','G','16','H','17','I','18','J','19','K','20','L','21',
	'M','22','N','23','O','24','P','25','Q','26','R','27','S','28','T','29','U','30','V','31','W','31','X','32','Y','32','Z','33');
	
	my ($wert) = @_;
	my $wert = $value {$wert};
		
	return($wert);
}

sub igc_logger {
	my %hersteller = ('A','Volkslogger','P','Peschke','R','Print-Technik','S','Posigraph','Z','Zander','F','Filser','L','LXNavigation','X','Software','G','FLARM');
		
	my ($logger) = @_;
	my $logger = $hersteller {$logger};
		
	return($logger);
}

sub max() {
	my ($a, $b) = @_;
	return (($a > $b) ? $a : $b);	
}

sub min() {
	my ($a, $b) = @_;
	return (($a < $b) ? $a : $b);
}

# returns only if user is logged in
sub auth_ok () {
	my $lizenz = "";
	
	$sid = $query->cookie(-name=>COOKIE_NAME);
	my $id = &check_session($sid);

	# wenn gar nicht eingeloggt dann exit
	if ($id eq '') { $query->print( $query->redirect(-location=>"igc.woa?cmd=login") ); exit; }
	
	my $sth = $DBH->prepare("SELECT * FROM AuthSession WHERE sid='$sid'");
	$sth->execute;	
	my $hash_ref = $sth->fetchrow_hashref;
	$lizenz = $hash_ref->{lizenz};
	$sth->finish;
	
	my $sth = $DBH->prepare("SELECT * FROM user WHERE lizenz LIKE '$lizenz'");
	$sth->execute;
	$hash_ref = $sth->fetchrow_hashref;
	my $lizenz = $hash_ref->{lizenz};
	
	# wenn user nicht existiert dann zu login
	if ($lizenz eq '') { $query->print( $query->redirect(-location=>"igc.woa?cmd=login") ); exit; }
	$sth->finish;
	
	return ($lizenz);
}

# returns only if user is logged in
sub admin_ok () {
	my ($ref_id) = @_;
	my $pw_name = "";
	
	$sid = $query->cookie(-name=>COOKIE_NAME);
	my $id = &check_session($sid);

	# wenn gar nicht eingeloggt dann exit
	if ($id eq '') { $query->print( $query->redirect(-location=>"igc.woa?cmd=login_admin&id=$ref_id") ); exit; }
	
	my $sth = $DBH->prepare("SELECT * FROM AuthSession WHERE sid='$sid'");
	$sth->execute;	
	my $hash_ref = $sth->fetchrow_hashref;
	$pw_name = $hash_ref->{lizenz};
	$sth->finish;
	
	$sth = $DBH->prepare("SELECT * FROM AuthAdmin WHERE pw_name LIKE '$pw_name'");
	$sth->execute;
	$hash_ref = $sth->fetchrow_hashref;
	$pw_name = $hash_ref->{pw_name};
	
	# wenn user nicht existiert dann zu login
	if ($pw_name eq '') { $query->print( $query->redirect(-location=>"igc.woa?cmd=login_admin&id=$ref_id") ); exit; }
	$sth->finish;
	
	return ($pw_name);
}

sub uft2db() {
	my ($str) = @_;

	$str =~ s/ß/ß/g;
	$str =~ s/ö/ö/g;
	$str =~ s/ü/ü/g;
	$str =~ s/ä/ä/g;
	$str =~ s/Ãœ/Ü/g;
	$str =~ s/Ö/Ö/g;
	$str =~ s/Ä/Ä/g;
	
	return($str);	
}

# get session from the database
sub get_session() {
	my ($sid) = @_;
	
	my $sth = $DBH->prepare("SELECT * FROM AuthSession WHERE sid='$sid'");
	$sth->execute;
	my $hash_ref = $sth->fetchrow_hashref;
	my $lizenz = $hash_ref->{lizenz};
	$sth->finish;
	
	return($lizenz);
}

sub get_witness_name() {
	my ($witness) = @_;
	
	my $sth = $DBH->prepare("SELECT nachname,vorname FROM witness WHERE id='$witness'");
	$sth->execute;
	my $hash_ref = $sth->fetchrow_hashref;
	my $name = $hash_ref->{nachname} . " " . $hash_ref->{vorname};
	$sth->finish;
	
	return($name);
}

sub check_session {
	my ($sid) = @_;
    
	$DBH->do("DELETE FROM AuthSession WHERE (unix_timestamp()-unix_timestamp(modified)) > 60*60*24"); # 24 Stunden

	my $sth = $DBH->prepare("SELECT * FROM AuthSession WHERE sid='$sid'");
	$sth->execute;
	
	if ($sth->rows gt 0) {
		return($sid);
	}
	else {
		return(0);
	}
	$sth->finish;	
}

# Find a new unique ID and insert it into the database
sub generate_id {
	my ($lizenz) = @_;
    # Create a new session id
    my $tries = 0;
    my $sid = substr(md5_hex('horst' . rand()), 0, 20);
    while ($tries++ < 10) {
    	last if $DBH->do("INSERT INTO AuthSession (sid,lizenz,remote_addr) VALUES ('$sid','$lizenz','$ENV{REMOTE_ADDR}')");
		$sid = substr(md5_hex($sid), 0, 20);
	}
    return undef if $tries >= 10;  # we failed
    return $sid;
}

# Save the current session
sub save_state {
  my ($sid) = @_;
	$DBH->do("UPDATE AuthSession SET modified = NULL WHERE sid='$sid'");
    return CGI::Cookie->new(-name => COOKIE_NAME,
			    -value => $sid,
			    -domain => '.onf-online.at',
			    -path => '/');
			    #-expires => '+60m'
}

# Save the current session
sub quit_state {
  my ($sid) = @_;
	$DBH->do("DELETE FROM AuthSession WHERE sid='$sid'");
    return CGI::Cookie->new(-name => COOKIE_NAME,
			    -value => $sid,
			    -domain => '.onf-online.at',
			    -path => '/',
			    -expires => '-10m');
}