#!/usr/bin/perl
#
#

use DBI;
use CGI;
use err;


$mailprog = '/usr/sbin/sendmail';
$absender = 'info@prorata.de';
$subject = 'Ihre Zugangsdaten';


open (FILE, "<panel2.conf") or die "AAAArrrrrgghhh";
while ($line = <FILE>) {
  ($key,$val) = ($line =~/^(\S*)\s*=\s*(\S*)/);
    if ($key ne "") { $conf{$key}=$val }
}
close FILE;

my $query = new CGI;

my $DB_DSN      = "DBI:$conf{database}:$conf{dataname}:$conf{datahost}";
my $DB_USER     = "$conf{datauser}";
my $DB_PASSWD   = "$conf{datapass}";

my $dbh = DBI->connect($DB_DSN, $DB_USER, 
   $DB_PASSWD) or err::error_seite{"Cannot connect to DB"};


my $pin = $query->param(pin);
my $pass = $query->param(pass);
my $vname = $query->param(vname);
my $nname = $query->param(nname);
my $tel = $query->param(tel);
my %val = $query->Vars;


my $but = $query->param(but);	#-----------Einloggen
my $but1 = $query->param(but1);	#-----------Zurück
my $but2 = $query->param(but2);	#-----------Ende
my $but3 = $query->param(but3);	#-----------Abschicken

#-------------------------------------------
if ($but2 eq "Ende")  {	
    print $query->redirect("$conf{htmlsource}/webhaup1.htm");   #"$ENV{HTTP_REFERER}"
	$dbh->disconnect();
	exit;
}
#-------------------------------------------
if ($but1 eq "Zurück")  {	
    print $query->redirect("$conf{htmlsource}/webhaup1.htm");   #"$ENV{HTTP_REFERER}"
	$dbh->disconnect();
	exit;
}
#------------LOGIN FALSE------------------------------------------------
if ($but eq "Einloggen"){$pass=crypt($pass,"xy")}
	#else { $dbh->disconnect(); exit(0); }

if ($but3 eq "") {
 if ($but eq "Einloggen" || $but1 eq "Zurück") {
  $err=0;
  $err=!($dbpass=$dbh->selectrow_array("select pass from benutzer where pin=$pin"));
  $dbpass=crypt($dbpass,"xy");
  if ($dbpass ne $pass) {$err=1}

  if ($err==1) {
	print  $query->header,
	$query->start_html(-title=>"Prorata Login",
  		     -text=>'#000000', -bgcolor=>'#FFFFFF', -link=>'#0084FF',
		     -vlink=>'#0084FF', -alink=>'#0084FF', 
		     -background=>'' ),

    "<center><img src='$conf{picsource}/$conf{proratalogo}'><hr><br>\n",
    "<font size=6 color='#0482BD' face='$conf{stdfont}'>Überprüfen Sie bitte Ihre PIN-Nummer und Ihr Passwort !</font><hr>\n",  
   
	$query->startform,"<center><br><table>",
	$query->submit('but1','Zurück'),"</center></table>\n",
	$query->endform,
	$query->end_html;
	$dbh->disconnect;    
	exit 0;
  } 
  else {
  #---------------------------------------------------------------------------
  #Passwort und PIN sind in Ordnung!

		$sql="select * from benutzer where pin=$pin";
		$sth=$dbh->prepare($sql) or {err::error_seite("Tabelle Benutzer konnte nicht geöffnet werden!",$ende)};
		$sth->execute;		
#------------------------------------------------------------------------------------
print  $query->header,
	   $query->start_html(-title=>'Teilnehmer Angaben',
		     -text=>'#000000', -bgcolor=>'#FFFFFF', -link=>'#0084FF',
		     -vlink=>'#0084FF', -alink=>'#0084FF', 
		     -background=>'' ),

	"<center><img src='$conf{picsource}/$conf{proratalogo}'>",
	"<hr><b><br><br><font face='$conf{stdfont}'>",
  #-------------------------------------------------------------------------------------
	"<P><CENTER><b>",
  #-------------------------------------------------------------------------------------
  "<P><CENTER><TABLE BORDER=1  width='100%'><tr><b><center>",
  "<TD><b><CENTER><font color='#0080C0'>Anrede</font></CENTER></TD><TD><CENTER><b><font color='#0080C0'>Vorname</font>",
  "</CENTER></TD><TD><CENTER><b><font color='#0080C0'>Name</font></CENTER></TD><TD><CENTER><b><font color='#0080C0'>Land</font></CENTER>",
  "</TD><TD><CENTER><b><font color='#0080C0'>PLZ</font></CENTER></TD><TD><CENTER><b><font color='#0080C0'>Stadt</font></CENTER></TD>",
  "<TD><CENTER><b><font color='#0080C0'>Straße</font></CENTER></TD><TD><CENTER><b><font color='#0080C0'>E-Mail</font></CENTER></TD>",
  "<TD><CENTER><b><font color='#0080C0'>Provider</font></CENTER></TD><TD><CENTER><b><font color='#0080C0'>Telefon</font></CENTER></TD></tr>";

$sth = $dbh->prepare("select ANREDE,VNAME,NNAME,LAND,PLZ,STADT,STRASSE,EMAIL,provider,TEL from benutzer where pin=$pin ");
$sth->execute;

@arr = $sth->fetchrow_array;

	 my @AN=("","Herr","Frau");
	 $val{an}=$arr[0];
	 my $anrede=$AN[$val{an}];
	 $arr[0] =$anrede;

      @LA=("","Deutschland","&Ouml;sterreich","Schweiz");
	  $val{la}=$arr[3];
	  my $land=$LA[$val{la}];
      $arr[3]=$land;

	  @PR=("","T-Online","AOL","Arcor","Otello","Freenet","Talknet","1 & 1","Netcologne","OWL-Online","Surf Callino","Viag Interkom","Compuserve","Okaynet","Debitel","Nikoma","UU-Net","Germanynet","Uni-Server","FH-Server","Andere");
	  $val{pr}=$arr[8];
	  my $provider=$PR[$val{pr}];
	  $arr[8]=$provider;

print "<TR><TD>" . join( "</TD><TD>" , @arr) . "</TD></TR>";
print "</TABLE>";

#------------------------------------------------------------------------------------------------------------
#Persönliche Angaben
  print "<P><CENTER><table border='1' width='100%'>",
 " <tr>",
    "<td width='14%' rowspan='2'><b><CENTER><font color='#0080C0'>Geb.Datum</font></CENTER></b></td>",
    "<td width='14%' rowspan='2'><b><CENTER><font color='#0080C0'>Ausbildung</font></CENTER></b></td>",
    "<td width='14%' rowspan='2'><b><CENTER><font color='#0080C0'>Fam.Stand</font></CENTER></b></td>",
    "<td width='14%' rowspan='2'><b><CENTER><font color='#0080C0'>Tätigkeit</font></CENTER></b></td>",
    "<td width='14%' rowspan='2'><b><CENTER><font color='#0080C0'>Entlohnungsart</font></CENTER></b></td>",
    "<td width='15%' colspan='5'>",
     " <p align='center'><b><font color='#0080C0'>Personen im Haushalt</font></b></td>",
  "</tr>",
  "<tr>",
	"<td><b><font color='#0080C0'>bis 3</font></b></td>",
    "<td><b><font color='#0080C0'>4 bis 12</font></b></td>",
    "<td><b><font color='#0080C0'>13 bis 22</font></b></td>",
    "<td><b><font color='#0080C0'>23 bis 54</font></b></td>",
    "<td><b><font color='#0080C0'>ab 55</font></td>",
    "</tr></center>";

$sth = $dbh->prepare("select gdat,bildung,famstand,arbeit,lohnart,pers3,pers12,pers22,pers54,pers55 from benutzer where pin=$pin ");
$sth->execute;

@arr = $sth->fetchrow_array;

my @BI =("Keine Angaben","Hauptschule","Realschule","Gymnasium","Fachhochschule","Hochschule","Magister","Promotion"); 
my @FA=("Keine Angaben","Verheiratet","Ledig");
my @AR=("Keine Angaben","Vollzeitbesch&auml;ftigt","Teilzeitbesch&auml;ftigt","Rentner/in","Student/in","Sch&uuml;ler/in","zur Zeit erwerbslos");

$arr[1]=$BI[$arr[1]];
$arr[2]=$FA[$arr[2]];
$arr[3]=$AR[$arr[3]];

print "<TR><TD><center>" . join( "</center></TD><TD><center>" , @arr) . "</center></TD></TR>";
print "</TABLE></CENTER><br><br>";

#--------------------------------------------------------------------------------------------
#Ausgewählte Produktkategorien
my @liste6=();				
foreach  $key (keys %val) {


		$sql="select * from benutzer where pin=$pin";
		$sth=$dbh->prepare($sql) or {err::error_seite("Tabelle Benutzer konnte nicht geöffnet werden!",$ende)};
		$sth->execute;

		$liste=$sth->fetchrow_hashref;

        while(($key,$item) = each %$liste) {

			if ((($num)=$key=~/^PK(\d+)$/i) && ($item == "1")) {
				$teil=$dbh->selectrow_array("select krit from krit where nr=$num");
				if ($num < 25) {unshift (@liste4,$teil)}
						   else {unshift (@liste5,$teil)}
						  
            }
#------------------------------------------------------------------------------------------

				if  ( (($num)=$key=~/^vh(\d+)$/i) && ($item == "1") ) {
				    $vhteil=$dbh->selectrow_array("select  vh from vhaus where vhnr=$num");
					unshift (@liste6,$vhteil);
				}

#-------------------------------------------------------------------------------------------
        }


	if (@liste5[0] eq "" && @liste4[0] eq "") {
		print "<br><br>Sie haben keine Kategorien und Hobbies ausgesucht !";
	}

	if (@liste4[0] ne "") {
 		print "<b><font color='#FF8000'>Bevorzugte Produktkategorien</b></font><br><br>",
		"<Table border=1><TR><TD>";
		while  (@liste4) {     # 4 Kriterien in eine Reihe, dann nächste Reihe
				@liste3=();
			for ($x=0;$x<4 ; $x++) {unshift (@liste3, shift @liste4)}
		    print join("</TD><TD>", @liste3), "</TD></TR><TR><TD>";
		}
	    print "</TD></TR></table>\n";
	}
	
 	if (@liste5[0] ne "") {
	 	print "<br><br><b><font color='#FF8000'>Hobbies</b></font><br><br>",
		"<Table border=1><TR><TD>";
		while  (@liste5) {     # 4 Kriterien in eine Reihe, dann nächste Reihe
				@liste3=();
			for ($x=0;$x<4 ; $x++) {unshift (@liste3, shift @liste5)}
			print join("</TD><TD>", @liste3), "</TD></TR><TR><TD>";
		}
		print "</TD></TR></table>\n";
	}


#---------------------------------------------------------------------------------------------
 if ( $liste6[0] ne "") {

 	 	print "<br><br><b><font color='#FF8000'>Versandhäuser, deren Kunde Sie sind</b></font><br><br>",
		"<Table border=1><TR><TD>";
		while  (@liste6) {     # 4 Kriterien in eine Reihe, dann nächste Reihe
				@liste3=();
			for ($x=0;$x<4 ; $x++) {unshift (@liste3, shift @liste6)}
			print join("</TD><TD>", @liste3), "</TD></TR><TR><TD>";
		}
		print "</TD></TR></table>\n";
 }


#-----------------------------------------------------------------------------------------------
print      $query->startform,"<CENTER><p>\n",

	   $query->submit('but2','Ende'),"</CENTER><P>\n",
       $query->endform,
	   $query->end_html, "\n";
	   $dbh->disconnect();
	   exit 0;

}
}
}
}
#--------------------------------------------------------------------------------------------------------------------------
#PIN oder PASSWORT vergessen

if ($but3 eq "Anfordern") {  # print "content-type: text/html\n\nHalloooo\n";

	#----------------------------------------------------------------------------------------------
	#Telefonnummerangabe überprüfen
    
	$val{tel}=~s/\D//g;   #Löscht alle nicht digits aus der Telefonnummer
	if ($val{tel} !~/0\d{5,}/)   #Tel.Nr muß mit 0 anfangen und mind. 6 zeichen lang sein
		{
	 	print  $query->header,
		       $query->start_html(-title=>'Falsche Telefonnummer?',
		         -text=>'#000000', -bgcolor=>'#FFFFFF', -link=>'#0084FF',
		         -vlink=>'#0084FF', -alink=>'#0084FF', 
		         -background=>'' ),
                 "<center><img src='$conf{picsource}/$conf{proratalogo}'>",
	             "<hr><b><br><br><font face='$conf{stdfont}'>",
		        $query->startform,"<CENTER><p>\n",
				"Überprüfen Sie bitte, ob Sie Ihre Telefonnummer korrekt mit Vorwahl eingegeben haben.<br><hr>",
				$query->submit('but1','Zurück'),"</CENTER><P>\n",
				$query->endform,
				$query->end_html, "\n";
				$dbh->disconnect();
				exit 1;
	
		}
	else{

#------------------------------------------------------------------------------------------------

	$sth = $dbh->prepare("select PIN, PASS, EMAIL from benutzer where vname='$vname' and nname='$nname' and tel=$val{tel} ");
	$sth->execute;
	@arr = $sth->fetchrow_array;
	$EMAIL=@arr[2]; $PIN=@arr[0]; $PASS=@arr[1];
#----------------------------------------------------------------------------------------

	print  $query->header,
		   $query->start_html(-title=>'Teilnehmer Anfrage',
		       -text=>'#000000', -bgcolor=>'#FFFFFF', -link=>'#0084FF',
		       -vlink=>'#0084FF', -alink=>'#0084FF', 
		       -background=>'' ),

	 "<center><img src='$conf{picsource}/$conf{proratalogo}'>",
	 "<hr><b><br><br><font face='$conf{stdfont}'>";

if ( $EMAIL ne "" and $PIN ne "") {

	print "Ihre PIN-Nummer und Ihr Passwort wurden Ihnen per E-Mail zugesandt<br><hr>";
	print  $query->startform,"<CENTER><p>\n",
	   $query->submit('but2','Ende'),"</CENTER><P>\n",
       $query->endform,
	   $query->end_html, "\n";
	   $dbh->disconnect();
		#E-Mail-generieren
		&get_date;
		&send_mail;
	   exit 0;
	}
else { print "Ihre Angaben : '$vname $nname $tel' wurden in der Datenbank nicht gefunden<br><hr>";
	 print  $query->startform,"<CENTER><p>\n",
			$query->submit('but1','Zurück'),"</CENTER><P>\n",
			$query->endform,
			$query->end_html, "\n";
			$dbh->disconnect();
			exit 0;
	 } 

#-----------------------------------------------------------------------------------------------
print  $query->startform,"<CENTER><p>\n",
	   $query->submit('but2','Ende'),"</CENTER><P>\n",
       $query->endform,
	   $query->end_html, "\n";
	   $dbh->disconnect();
	   exit 0;

}
}
#-------------------------------------------------------------------------------------------------

sub get_date {

    # Define arrays for the day of the week and month of the year.           #
    @days   = ('Sonntag','Montag','Dienstag','Mittwoch',
               'Donnerstag','Freitag','Samstag');
    @months = ('Januar','Februar','März','April','Mai','Juni','Juli',
         'August','September','Oktober','November','Dezember');

    ($sec,$min,$hour,$mday,$mon,$year,$wday) = (localtime(time))[0,1,2,3,4,5,6];
    $time = sprintf("%02d:%02d:%02d",$hour,$min,$sec);
    $year += 1900;

    # Format the date.                                                       #
    $date = "$days[$wday] den $mday $months[$mon] $year um $time";

}

	sub send_mail {
    # Localize variables used in this subroutine.                            #
    local($print_config,$key,$sort_order,$sorted_field,$env_report);

    # Open The Mail Program
    open(MAIL,"|$mailprog -t");

    print MAIL "To: $EMAIL\n";
    print MAIL "From: $absender\n";

    # Print Message Subject
    print MAIL "Subject: $subject\n\n";
    print MAIL "Automatisch erstellt am $date\n";
    print MAIL "-" x 75 . "\n";
	print MAIL "Sie haben uns gebeten, Ihnen Ihre Zugangsdaten\nfür die Teilnahme an unseren Umfragen\nzuzusenden.\n\n";
	print MAIL "Ihre PIN ist : $PIN\n";
	print MAIL "Ihr Passwort ist : $PASS\n\n";
    print MAIL "Mit freundlichem Gruß\n\nIhr prorata.web - TEAM!";
    close (MAIL);
}