#!/usr/bin/perl
#

use DBI;
use CGI;
use err;
use pdat;
#use baseq;

#$XY=baseq->new;

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;

# Define the variables.

my $query= new CGI;
my $queryn= new CGI({});

my $button = $query->param('but');
my %val = $query->Vars;

if ($val{umfid}==0) { $val{umfid}=0;}
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 $ausfuhren='Ausführen';
my $speichern='Stichprobe Speichern';
my $ende='    Ende    ';
my $endebut = $query->startform . $query->submit("but",$ende) . $query->endform;

if ($button eq $ende) { print $query->redirect("$conf{htmlsource}/admin.htm")}
if ($button eq $verw) { reset()}

$sth=$dbh->prepare("select * from krit");
$sth->execute;

my @kritl;
while(($num,$krit)=$sth->fetchrow_array){@kritl[$num]=$krit}

$sthvh=$dbh->prepare("select * from vhaus");
$sthvh->execute;

my @vhl;
while(($num,$vh)=$sthvh->fetchrow_array){@vhl[$num]=$vh}

my @bildl=('Hauptschule','Realschule','Gymnasium','Fachhochschule','Hochschule','Magister','Promotion');
my @beschl=('Vollzeit','Teilzeit','Rentner','Student','Schüler','Arbeitslos');



if ($val{but} eq "" || $val{but} eq "$ausfuhren" || $val{but} eq "$speichern"){
  print  $query->header,
       $query->start_html(-title=>'Teilnehmerselektion',
		     -text=>'#000000', -bgcolor=>'#D8D5D8', -link=>'#0084FF',
		     -vlink=>'#0084FF', -alink=>'#0084FF', 
		     -background=>'' ),

	"<center><img src='$conf{picsource}/$conf{proratalogo}'>",
	"<hr><font face='$conf{stdfont}' size=5><b>Teilnehmerselection für Umfragenummer $val{umfid}</b><br>",
	$query->startform,"<table border=1 width='100%'>",
	$query->hidden(umfid,$val{umfid}),
	"<TR><TD>",$query->checkbox_group("anzahl","Anzahl"),"</TD><TD>&nbsp;&nbsp;&nbsp;Personen</TD><TD>",$query->textfield(-name=>"Personen",-value=>"0",-size=>"5"),"</TD></TR>",
	"<TR><TD>",$query->checkbox_group("calterv","Alter"),"</TD><TD>&nbsp;&nbsp;&nbsp;von</TD><TD>",$query->textfield(-name=>"alterv",-value=>"0",-size=>"5"),"</TD></TR>",
	"<TR><TD>",$query->checkbox_group("calterb","Alter"),"</TD><TD>&nbsp;&nbsp;&nbsp;bis</TD><TD>",$query->textfield(-name=>"alterb",-value=>"0",-size=>"5"),"</TD></TR>",
	"<TR><TD>",$query->checkbox_group("cbis3","Haushalt"),"</TD><TD>&nbsp;&nbsp;&nbsp;Anzahl 0-3 jährige</TD><TD>",$query->textfield(-name=>"bis3",-value=>"0",-size=>"5"),"</TD><TR>",
	"<TR><TD>",$query->checkbox_group("cbis12","Haushalt"),"</TD><TD>&nbsp;&nbsp;&nbsp;Anzahl 4-12 jährige</TD><TD>",$query->textfield(-name=>"bis12",-value=>"0",-size=>"5"),"</TD><TR>",
	"<TR><TD>",$query->checkbox_group("cbis22","Haushalt"),"</TD><TD>&nbsp;&nbsp;&nbsp;Anzahl 13-22 jährige</TD><TD>",$query->textfield(-name=>"bis22",-value=>"0",-size=>"5"),"</TD><TR>",
    "<TR><TD>",$query->checkbox_group("cbis55","Haushalt"),"</TD><TD>&nbsp;&nbsp;&nbsp;Anzahl 23-54 jährige</TD><TD>",$query->textfield(-name=>"bis55",-value=>"0",-size=>"5"),"</TD><TR>",
	"<TR><TD>",$query->checkbox_group("cab55","Haushalt"),"</TD><TD>&nbsp;&nbsp;&nbsp;Anzahl ab 55</TD><TD>",$query->textfield(-name=>"ab55",-value=>"0",-size=>"5"),"</TD><TR>",
	"<TR><TD>",$query->checkbox_group("sex","Geschlecht"),"</TD><TD>&nbsp;&nbsp;&nbsp;",$queryn->popup_menu('anrede',pdat::hf(),$val{anrede},pdat::hfh()),"</TD></TR>",
	"<TR><TD>",$query->checkbox_group("fam","Familienstand"),"</TD><TD>&nbsp;&nbsp;&nbsp;", $queryn->popup_menu('famstand',pdat::fs(),$val{famstand},pdat::fsh()),"</TD></TR>",
	"<TR><TD>",$query->checkbox_group("bild","Ausbildung"),"</TD><TD>&nbsp;&nbsp;&nbsp;",$queryn->popup_menu('bildung',pdat::bi(),$val{bildung},pdat::bih()),"</TD></TR>",
	"<TR><TD>",$query->checkbox_group("besch","Beschäftigung"),"</TD><TD>&nbsp;&nbsp;&nbsp;", $queryn->popup_menu('arbeit',pdat::bs(),$val{arbeit},pdat::bsh()),"</TD></TR>",
    "</Table><br>",
	

   	#--------------------------------------------------------------------------------------------------------
	#Ausgewählte Produktkategorien & Hobbys
	
	"<p align='center'><font face='Tahoma' color='#FF8000'><strong>Ausgewählte Produktkategorien</strong></font>",
	"<br><table border='1' width='100%'><tr>";
    my $xa=3;
	for ($x=1; $x<51 ;$x++) {
       if (@kritl[$x] eq "") {next}
	   print "<td ><font color='#000000'>",$query->checkbox_group("pk${x}",@kritl[$x]),"</td>";
	          #"</font></td><td width='27%'><font color='#000000'>@kritl[$x]</font></td>";
       $xa--; if ($xa==0) {$xa=3; print "</tr><tr>";}
       if ($x==25) {print "</table><p align='center'><font face='Tahoma' color='#FF8000'><strong>Hobbys</strong></font><br><table border='1' width='100%'>"}
	}
	print "</table>";

	#--------------------------------------------------------------------------------------------------------
	#Angegebene Online-Häuser
	$C="checked";
	if ($val{vhtest} eq "and") {$C1=$C;	} else {$C2=$C;}
    if ($val{vhtestx} ne "") {$C3=$C;}
	print "<br><p align='center'><font face='Tahoma' color='#FF8000'><strong>Versandhäuser</strong></font>",
	"<br><table border='1' width='100%'><tr>",
	"<tr><td><center><input type='radio' $C1 name='vhtest' value='and' >&nbsp;&nbsp;AND</center></td>",
	"<td><center><input type='radio' $C2 name='vhtest' value='or' >&nbsp;&nbsp;OR</center></td>",
	"<td><center><input type='checkbox' $C3 name='vhtestx' value='not' >&nbsp;&nbsp;NICHT</center></td></tr><tr></tr>";

    my $xa=3;
	for ($x=0; $x<20 ;$x++) {
       if (@vhl[$x] eq "") {next}
	   print "<td ><font color='#000000'>",$query->checkbox_group("vh${x}",@vhl[$x]),"</td>";
       $xa--; if ($xa==0) {$xa=3; print "</tr><tr>";}
	}
	 
	#-----------------------------------------------------------------------------------------------------------------------------
	print "</table><br>";
	print "<center>",$query->submit('but',$ausfuhren),"&nbsp;&nbsp;",
	           $query->submit('but',$ende),"&nbsp;&nbsp;</center><P>\n",
	"</center>";
}

my $rlist;
my $jahr = (localtime)[5]+1900;

if ($val{but} eq "$ausfuhren"){
   if ($val{anzahl} eq "")  {$val{Personen}=-1} else {$limit=" limit 0,$val{Personen}"} 	
   unshift @chk,"pin>0";
   if ($val{calterv} ne "")	{$geb=$jahr-$val{alterv}; unshift @chk,"gdat<'$geb-12-31'" }else{$val{alterv}=-1}
   if ($val{calterb} ne "")	{$geb=$jahr-$val{alterb}; unshift @chk,"gdat>'$geb-01-01'" }else{$val{alterb}=-1}
   if ($val{cbis3} ne "")	{unshift @chk,"pers3=$val{bis3}" }else{$val{bis3}=-1} 
   if ($val{cbis12} ne "")	{unshift @chk,"pers12=$val{bis12}" }else{$val{bis12}=-1} 
   if ($val{cbis22} ne "")	{unshift @chk,"pers22=$val{bis22}" }else{$val{bis22}=-1}  
   if ($val{cbis55} ne "")	{unshift @chk,"pers54=$val{bis55}" }else{$val{bis55}=-1} 
   if ($val{cab55} ne "")	{unshift @chk,"pers55=$val{ab55}" }else{$val{ab55}=-1} 
   if ($val{sex} ne "")	{unshift @chk,"anrede='$val{anrede}'" }else{$val{anrede}=""}
   if ($val{fam} ne ""){unshift @chk,"famstand='$val{famstand}'" }else{$val{famstand}=""}
   if ($val{bild} ne "")	{unshift @chk,"bildung='$val{bildung}'" }else{$val{bildung}=""} 
   if ($val{besch} ne "")	{unshift @chk,"arbeit='$val{arbeit}'" }else{$val{arbeit}=""}

	for ($x=1; $x<49 ;$x++ ) {
        $nam="pk$x";
		if ($val{$nam} ne "") {$sqlist.= ",1"} else {$sqlist=$sqlist . ",0"};
		if ($val{$nam} ne "") {$rlist=$rlist . "pk${x}='1' and "};
		}
     for($y=0; $y<20;$y++){
		$nam="vh$y";
		if ($val{$nam} ne "") {$sqlist2=$sqlist2 . ",1"} else {$sqlist2=$sqlist2 . ",0"};
        if ($val{$nam} ne "") {$rlist2=$rlist2 . "vh${y}='1' $val{vhtest} "}

	 }
  $rlist2=~s/\w{2,3}\s$//;
  if ($rlist2 ne "" ) { $rlist2=" $val{vhtestx} ( ".$rlist2." ) and "; }
$rlist.=$rlist2;
   # Benutzer für Stichprobe selektieren
  $sql="select count(pin) from benutzer where " . $rlist .  join(" and ",@chk) . $limit;
   $benanz=$dbh->selectrow_array( $sql );
  print $sql, "<center>",

  $query->submit('but',"$speichern"),"</center><br>",

  "Gefundene Übereinstimmungen : $benanz", 
  "<P><CENTER><TABLE width='100%' cellspacing='4' cellpadding='2' BORDER=1><tr><b><center>",
  "<TD><b><CENTER>Pos.</CENTER></TD><TD><b><CENTER>PIN</CENTER></TD><TD><b><CENTER>Anrede</CENTER></TD><TD><CENTER><b>Vorname</CENTER></TD><TD><CENTER><b>Name</CENTER></TD><TD><CENTER><b>Land</CENTER>",
  "</TD><TD><CENTER><b>PLZ</CENTER></TD><TD><CENTER><b>Stadt</CENTER></TD>",
  "<TD><CENTER><b>Straße</CENTER></TD><TD><CENTER><b>E-Mail</CENTER></TD><TD><CENTER><b>Telefon</CENTER></tr>";

  $maxstpro=$dbh->selectrow_array("select max(stprobe) from umfidpin");

  $sql="select PIN from umfidpin where stprobe=$maxstpro" ;
  $sth = $dbh->prepare($sql);
  $sth->execute;
  while (@result = $sth->fetchrow_array) {$lastpin=@result[0]}

  if ($lastpin eq "") {$lastpin="10000"};
  $zah=0; if ($val{Personen} == -1) {$zah=-100000}
  $sql="select PIN, ANREDE,VNAME,NNAME,LAND,PLZ,STADT,STRASSE,EMAIL,TEL from benutzer where " . $rlist . join(" and ",@chk) . " and pin>$lastpin order by pin" .$limit ;
  $sth = $dbh->prepare($sql);
  $sth->execute;
  while (@result = $sth->fetchrow_array) {
    print  "<TR><TD>$zah</td><td>", join("</TD><TD>", @result), "</TD></TR>\n";
	}
  $sql="select PIN,ANREDE,VNAME,NNAME,LAND,PLZ,STADT,STRASSE,EMAIL,TEL from benutzer where " . $rlist . join(" and ",@chk) . " and pin<=$lastpin order by pin" . $limit ;
  $sth = $dbh->prepare($sql);
  $sth->execute;
  while ((@result = $sth->fetchrow_array) && (++$zah <= $val{Personen})) {
    print  "<TR><TD>$zah</td><td>", join("</TD><TD>", @result), "</TD></TR>\n";
	}


  print "</table><br>";
}


#--------------------------------------------------------  Speichern
if ($val{but} eq "$speichern"){

   if ($val{anzahl} eq "")  {$val{Personen}=-1}	
   if ($val{calterv} ne "")	{$geb=$jahr-$val{alterv}; unshift @chk,"gdat<'$geb-12-31'" }else{$val{alterv}=-1}
   if ($val{calterb} ne "")	{$geb=$jahr-$val{alterb}; unshift @chk,"gdat>'$geb-01-01'" }else{$val{alterb}=-1}
   if ($val{cbis3} ne "")	{unshift @chk,"pers3=$val{bis3}" }else{$val{bis3}=-1} 
   if ($val{cbis12} ne "")	{unshift @chk,"pers12=$val{bis12}" }else{$val{bis12}=-1} 
   if ($val{cbis22} ne "")	{unshift @chk,"pers22=$val{bis22}" }else{$val{bis22}=-1}  
   if ($val{cbis55} ne "")	{unshift @chk,"pers54=$val{bis55}" }else{$val{bis55}=-1} 
   if ($val{cab55} ne "")	{unshift @chk,"pers55=$val{ab55}" }else{$val{ab55}=-1} 
   if ($val{anrede} ne "")	{unshift @chk,"anrede='$val{anrede}'" }else{$val{anrede}="-1"}
   if ($val{famstand} ne ""){unshift @chk,"famstand='$val{famstand}'" }else{$val{famstand}="-1"}
   if ($val{bildung} ne "")	{unshift @chk,"bildung='$val{bildung}'" }else{$val{bildung}="-1"} 
   if ($val{arbeit} ne "")	{unshift @chk,"arbeit='$val{arbeit}'" }else{$val{arbeit}="-1"}
   	for ($x=1; $x<49 ;$x++ ) {
        $nam="pk$x";
		if ($val{$nam} ne "") {$sqlist=$sqlist . ",1"} else {$sqlist=$sqlist . ",0"};
		if ($val{$nam} ne "") {$rlist=$rlist . "pk${x}='1' and "};
		}
	for($y=0; $y<20;$y++){
		$nam="vh$y";
		if ($val{$nam} ne "") {$sqlist2=$sqlist2 . ",1"} else {$sqlist2=$sqlist2 . ",0"};
        if ($val{$nam} ne "") {$rlist2=$rlist2 . "vh${y}='1' $val{vhtest} "}

	 }

	$rlist2=~s/\w{2,3}\s$//;
	if ($rlist2 ne "" ) { $rlist2=" $val{vhtestx} ( ".$rlist2." ) and "; }
	$rlist.=$rlist2;

   # Stichprobe Abspeichern
   $stpro=($dbh->selectrow_array("select max(stpro) from stprobe"))+1;
   $sql="insert into stprobe values ( $val{umfid},$val{Personen},$val{alterv},$val{alterb},$val{bis3},$val{bis12},$val{bis22},$val{bis55},$val{ab55},'$val{anrede}','$val{famstand}','$val{bildung}','$val{arbeit}' $sqlist , $stpro, 0 $sqlist2)" ;
   #$XY->sqldo($sql);
   $dbh->do($sql);
   $benanz=$dbh->selectrow_array("select count(pin) from benutzer where " . $rlist . join(" and ",@chk) . $limit );

   # Benutzer für Stichprobe selektieren
   $maxstpro=$dbh->selectrow_array("select max(stprobe) from umfidpin");
   $sql="select PIN from umfidpin where stprobe=$maxstpro" ;
   $sth = $dbh->prepare($sql);
   $sth->execute;
   while (@result = $sth->fetchrow_array) {$lastpin=@result[0]}

   if ($lastpin eq "") {$lastpin="10000"};
   $sql="select PIN, VNAME from benutzer where " . $rlist . join(" and ",@chk) . " and pin>$lastpin order by pin" . $limit ;
   $sth = $dbh->prepare($sql);
   $sth->execute;
   while (@result = $sth->fetchrow_array)  {
     $pin = @result[0];
	 $dbh->do("insert into umfidpin values ($val{umfid}, $pin, $stpro)");
   }
   $sql="select PIN, VNAME from benutzer where " . $rlist . join(" and ",@chk)  . " and pin<=$lastpin order by pin". $limit ;
   $sth = $dbh->prepare($sql);
   $sth->execute;
   while (@result = $sth->fetchrow_array) {
   $pin = @result[0];
   $dbh->do("insert into umfidpin values ($val{umfid}, $pin, $stpro)");

   }

}


print  $query->endform,
       $query->end_html, "\n";

       $dbh->disconnect();

exit;