#!/usr/bin/perl
#
# registration program
# (C) 2004 Richard Fincher, Room101
# Written for National schools partnership 
# Modification or distribution not permitted

$ip = $ENV{'REMOTE_ADDR'};
$thisscript="join_us.pl";

$mailprog = '/usr/sbin/sendmail';


#fields - adding fields? need to add here, in tamplate and to database the name needs to be the same in all 3. - note database may contain additional fields not listed here such as an auto increment id - all names must conform to usual standatrds - no spaces etc
#orginal names based on nsp family reistration doc sent 23 oct 04

@fields=("competition","Title","FirstName","LastName","Address1","Address2","Town","County","Postcode","HomeTel","Mobile","Email","Username","Password","DOB",
"MaritalStatus","Individualsinhousehold","AgeUnder18_1","AgeUnder18_2","AgeUnder18_3","AgeUnder18_4","AgeUnder18_5","AgeUnder18_6","AgeUnder18_7","AgeUnder18_8","TotalUnder18","PrimaryID","SecondaryID",
"FamilyIncome","Occupation","TypeofAccomodation","Bedrooms","Cars","PhoneNetworks", "DailyNewspapers","SundayNewspapers",
"ContactViaEmail","ContactViaPhone","ContactViaPost","DoNotContactReSchoolFundraisingInitiatives","DoNotContactVia3rdParty"
);

#title missing on form?


#school addresse not needed unless not in school database - 

&connectsql;
&decode_form;

#DOB - needs combining
#TotalUnder18 - needs calcualting

($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = gmtime(time);
 
$year = $year + 1900;
$mon++;

print "Content-type: text/html\r\n\r\n";

$step = "$FORM{step}"; #registration = step1 



if ($step ne ""){
	✓
}




 $templatefile = "join_us$step.html";
    	
 
open ( FILE, "$templatefile"); $/ = undef;
$page = <FILE>; $/ ="\n"; close ( FILE );

open ( FILE, "countries.txt"); @countries = <FILE>; close ( FILE );


foreach $country ( @countries ) {

    chop ( $country );
    $country_select .= "<OPTION VALUE=\"$country\">$country</OPTION> \n";

    }

 

if ($FORM{year} ne ""){
	$FORM{DOB} = "$FORM{year}:$FORM{month}:$FORM{day}";
}


if ($FORM{comp} eq "cp"){
	$page=~s/<comp>.*<\/comp>/Camping pack<input type=hidden name=competition value="Camping Pack">/is;
}elsif ($FORM{comp} eq "tpt"){
	$page=~s/<comp>.*<\/comp>/Theme park tickets<input type=hidden name=competition value="Theme park tickets">/is;
}else{
	#$diag.="*";
	$page=~s/<comp>.*<\/comp>/ /is;
}



$page =~ s/\!thisscript\!/$thisscript/;
$page =~ s/\!countries\!/$country_select/;
$page =~ s/\!error\_message\!/$error_message/g;


 

if ($error == 1){

	#set selects to the selected values
	$page =~ s/ selected>/>/gi;  #remove default selects
	#the s at the end forces the dot. to include line breaks
	#the day terminates at month otherwise the pattern match matches the month as it will always go for the longest pattern
	
	$page =~ s/(<select name\=\"day\">.*<option value\=\"$FORM{day}\")(.*month)/$1 selected $2/si;   
	$page =~ s/(<select name\=\"month\">.*<option value\=\"$FORM{month}\")/$1 selected/si;
	$page =~ s/(<select name\=\"year\">.*<option value\=\"$FORM{year}\")/$1 selected/si;
}


if ($FORM{competition} eq "Theme park tickets"){
	$page =~ s/!jstermslink!/terms_family/gi;
}else{
	$page =~ s/!jstermslink!/terms_cotswold/gi;
}


$page =~ s/\!([a-zA-Z0-9_.]+)\!/$FORM{$1}/g;  #all subs need to be done before this line!!!!!!!!!!!!!


 

if ( $step == 4 ) { &process_form; }


print $page;
print $diag;
exit;

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

sub process_form {

	

  	&write_to_database;
 
   	&sendmail_cust; #needs to go to cust
        
}

# --------------------------------------------------
sub check{

	#need to check for valid email, unique id name
	
if ($FORM{'Email'} !~ /^[0-9A-Za-z\.\-\_]+\@[0-9A-Za-z\.\-\_]+\.[a-zA-Z]{2,4}$/){
	$error=1;
	$error_message = "Your email address is invalid.";
	$step=1; #force to reload step 1.
}	
	
$test2="select * from family3 where Username='$FORM{Username}'";

$test2 = $dbh -> prepare( "$test2");
$test2 -> execute();
$test = $test2 -> fetchrow_hashref;


if ($$test{Username} ne ""){
	$error=1;
	$error_message = "Your username is already taken please choose a different one.";
	$step=""; #force to reload step 1.
}

$test2="select * from family2 where Username='$FORM{Username}'";

$test2 = $dbh -> prepare( "$test2");
$test2 -> execute();
$test = $test2 -> fetchrow_hashref;


if ($$test{Username} ne ""){
	$error=1;
	$error_message = "Your username is already taken please choose a different one.";
	$step=""; #force to reload step 1.
}	
	

}

# --------------------------------------------------
sub sendmail_cust{

	$fromtxt = "National Schools Partnership";
	$fromemail = "info@nationalschoolspartnership.com";
	$totext = "$FORM{Title} $FORM{FirstName} $FORM{LastName}";
	$recipient = "$FORM{Email}";

	$subject = "National Schools Partnership confirmation email";

	
	#$diag.="h1: $recipient";
	
	open (EMAILFILE, "email.txt"); $/ = undef; $emailtext = <EMAILFILE>; $/ = "\n"; close ( EMAILFILE );

		
	$emailtext =~ s/<Title>/$FORM{Title}/g;
	$emailtext =~ s/<FirstName>/$FORM{FirstName}/g;
	$emailtext =~ s/<LastName>/$FORM{LastName}/g;
	$link="http://www.myschoolplus.co.uk/confirmation.pl?id=$newid&email=$recipient";
	$emailtext =~ s/<Link>/$link/g;

	
#	$diag.="h2: $emailtext";
	
	open (MAIL, "| $mailprog $recipient") || die "Can't open $mailprog!\n";
#	open (MAIL, "| $mailprog -f $fromemail $FORM{'toaddress'}");
	print MAIL "From: \"$fromtxt\" <$fromemail>\n";
	print MAIL "To: \"$totext\" <$recipient>\n";
	print MAIL "Subject: $subject\n";
	print MAIL "\n"; #MARKS THE END OF THE HEADER
	print MAIL "$emailtext\n";
	close MAIL;
}

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

sub write_to_database {


#fix checkboxes
if ($FORM{ContactViaEmail} ne "Yes"){
	$FORM{ContactViaEmail} = "No";
}

if ($FORM{ContactViaPhone} ne "Yes"){
	$FORM{ContactViaPhone} = "No";
}

if ($FORM{ContactViaPost} ne "Yes"){
	$FORM{ContactViaPost} = "No";
}

#if ($FORM{DoNotContactReSchoolFundraisingInitiatives} ne "Yes"){
#	$FORM{DoNotContactReSchoolFundraisingInitiatives} = "No";
#}

if ($FORM{DoNotContactVia3rdParty} eq "Yes"){
	$FORM{DoNotContactVia3rdParty} = "Ticked";
}else{
	$FORM{DoNotContactVia3rdParty} = "Not Ticked";
}

#passing 0 as a field value does not work! so if age is "" then could be zero or ""
#since ages are sorted then if it is before and age above 0 then it must be a 0 other wise it will be a blank - ok so long as at least one kid is above 1



for ($x=1; $x<9; $x++){ 
	if ($xf != 1){
			$agething="AgeUnder18_$x";
		if ($FORM{$agething} eq ""){
			$FORM{$agething} = 0;
		}
		if ($FORM{$agething} > 0){
			$xf=1;
		}
	}
}



	$sql ="insert into family3 ( ";
	
	for ($x=0; $x<@fields; $x++){
		$sql.="$fields[$x],";
	}
	
	#chop ($sql); #remove last comma
	
	$sql.="DateRegistered) values (";
	
	for ($x=0; $x<@fields; $x++){
		$sql.="\"$FORM{$fields[$x]}\",";
	}
	
	#chop ($sql); #remove last comma
	$sql.="now())";
 #	$dbh -> do ("$sql"); #this method will not net your return last inserted id!
	
	$sqlstring = $dbh->prepare($sql);
	$sqlstring->execute;
	$newid = $dbh->{'mysql_insertid'};
	 
 	
}

 sub connectsql{
	use DBI;
	$passwd="7HwsRnqD";
	$dbh = DBI->connect('DBI:mysql:database=schoolsdb;host=212.67.202.137','schools', $passwd);
}

sub decode_form {
	$decoded=1;
	(*fval) = @_ if @_ ;

	local ($buf);
	if ($ENV{'REQUEST_METHOD'} eq 'POST') {
		read(STDIN,$buf,$ENV{'CONTENT_LENGTH'});
	}
	else {
		$buf=$ENV{'QUERY_STRING'};
	}
	if ($buf eq "") {
			return 0 ;
		}
	else {
 		@fval=split(/&/,$buf);
		foreach $i (0 .. $#fval){
			($name,$val)=split (/=/,$fval[$i],2);
			$val=~tr/+/ /;
			$val=~ s/%(..)/pack("c",hex($1))/ge;
			$name=~tr/+/ /;
			$name=~ s/%(..)/pack("c",hex($1))/ge;
			if (!$val){ next; }#ie if empty (if we dont do this, a multiple named field will become something like ",,,"
			if (!defined($FORM{$name})) {
				$FORM{$name}=$val;
			}
			else {
				$FORM{$name} .= ",$val";

				#if you want multi-selects to goto into an array change to:
				#$FORM{$name} .= "\0$val";
			}


		   }
		}
return 1;
}