<?include_once("../../settings.php");?>
<form method='post' name='search'>
<?
	if (isset($_REQUEST['action']) && $_REQUEST['action']!=""){
		if ($_REQUEST['action']=="Update"){

			$SQL = "UPDATE tbl_registrant SET
			registrant_salutation='".$_REQUEST['registrant_salutation']."',
			registrant_surname='".$_REQUEST['registrant_surname']."',
			registrant_givenname='".$_REQUEST['registrant_givenname']."',
			registrant_designation='".$_REQUEST['registrant_designation']."',
			registrant_organisation='".$_REQUEST['registrant_organisation']."',
			registrant_email='".$_REQUEST['registrant_email']."',
			registrant_tel='".$_REQUEST['registrant_tel']."',
			registrant_mobile='".$_REQUEST['registrant_mobile']."',
			registrant_add1='".$_REQUEST['registrant_add1']."',
			registrant_add2='".$_REQUEST['registrant_add2']."',
			registrant_postal='".$_REQUEST['registrant_postal']."',
			registrant_paname='".$_REQUEST['registrant_paname']."',
			registrant_pamail='".$_REQUEST['registrant_pamail']."',
			registrant_attending='".$_REQUEST['registrant_attending']."',
			registrant_confirmation='".$_REQUEST['registrant_confirmation']."',
			registrant_diet='".$_REQUEST['registrant_diet']."',
			registrant_dietremarks='".$_REQUEST['registrant_dietremarks']."',
			seat_number='".$_REQUEST['seat_number']."' WHERE registrant_id='".$_REQUEST['registrant_id']."';";
			$qry->queryExecute($SQL);

			//,tbl_id='".$_REQUEST['table_id']."'

			$SQL = "SELECT count(*) as total from tbl_registrant WHERE tbl_id='".$_REQUEST['table_id']."'";
			$currentcount = $qry->querySelectSingle($SQL);
			$SQL = "SELECT tbl_max as total from tbl_table WHERE tbl_id='".$_REQUEST['table_id']."'";
			$maxcount = $qry->querySelectSingle($SQL);
			if ($_REQUEST['table_id']=='0'){
				$SQL = "UPDATE tbl_registrant SET tbl_id='".$_REQUEST['table_id']."' WHERE registrant_id='".$_REQUEST['registrant_id']."';";
				$qry->queryExecute($SQL);
			}else if ($currentcount['total']>=$maxcount['total']){
				echo "<script>alert('Table is full. Delegate not assigned.');</script>";
			}else{
				$SQL = "UPDATE tbl_registrant SET tbl_id='".$_REQUEST['table_id']."' WHERE registrant_id='".$_REQUEST['registrant_id']."';";
				$qry->queryExecute($SQL);
			}

			if (isset($_REQUEST['role_id']) && $_REQUEST['role_id']!=""){
				$SQL = "SELECT registrant_number FROM tbl_registrant WHERE registrant_id='".$_REQUEST['registrant_id']."';";
				$result = $qry->querySelectSingle($SQL);
				if ($result['registrant_number']==0){
					$SQL = "SELECT * FROM tbl_role WHERE role_id='".$_REQUEST['role_id']."';";
					$result = $qry->querySelectSingle($SQL);
					$SQL = "SELECT id_itself FROM tbl_id;";
					$idresult = $qry->querySelectSingle($SQL);
					$id = $idresult['id_itself'];
					$SQL = "UPDATE tbl_id SET id_itself=id_itself+1 WHERE id_id=1;";
					$qry->queryExecute($SQL);
					$sn=$result['role_prefix'].str_pad($id, 3, "0", STR_PAD_LEFT);
					$SQL = "UPDATE tbl_registrant SET registrant_sn='$sn' WHERE registrant_id='".$_REQUEST['registrant_id']."';";
					$qry->queryExecute($SQL);
					$SQL = "UPDATE tbl_registrant SET registrant_number='$id' WHERE registrant_id='".$_REQUEST['registrant_id']."';";
					$qry->queryExecute($SQL);
				}else{
					$id = $result['registrant_number'];
					$SQL = "SELECT * FROM tbl_role WHERE role_id='".$_REQUEST['role_id']."';";
					$result = $qry->querySelectSingle($SQL);
					$sn=$result['role_prefix'].str_pad($id, 3, "0", STR_PAD_LEFT);
					$SQL = "UPDATE tbl_registrant SET registrant_sn='$sn' WHERE registrant_id='".$_REQUEST['registrant_id']."';";
					$qry->queryExecute($SQL);
				}
			}
			echo "<script>alert('Record updated.');</script>";
		}
		if ($_REQUEST['action']=="Send Confirmation Email"){
			$SQL = "SELECT * FROM tbl_registrant WHERE registrant_id='".$_REQUEST['registrant_id']."';";
			echo $SQL;
			$result = $qry->querySelectSingle($SQL);
			$SQL = "SELECT * FROM tbl_email WHERE email_key='confirmation';";
			$emailresult=$qry->querySelectSingle($SQL);
			$subject = $emailresult['email_subject'];
			$message = $emailresult['email_content'];

			$datetime = date("F j, Y");
			$registrant_salutation = $result['registrant_salutation'];
			$registrant_name = $result['registrant_name'];
			$registrant_sn = $result['registrant_sn'];
			$email = $result['registrant_email'];

			$message = str_replace("@registrant_salutation@",$registrant_salutation,$message);
			$message = str_replace("@registrant_surname@",$result['registrant_surname'],$message);
			$message = str_replace("@registrant_givenname@",$result['registrant_givenname'],$message);
			$message = str_replace("@registrant_sn@",$registrant_sn,$message);
			$message = str_replace("@datetime@",$datetime,$message);

			$mail->IsHTML(true);
			$mail->SetFrom("secretariat@ace-gala.com.sg","ACE Gala Dinner Secretariat");
			$mail->AddAddress($email);
			if ($result['registrant_pamail']!='')
				$mail->AddCC($result['registrant_pamail']);
			$mail->Subject = $subject;
			$mail->Body = $message;
			echo $email;echo $result['registrant_pamail'];
			$mail->Send(); // send message

			$SQL = "UPDATE tbl_registrant SET registrant_email_sent='1' WHERE registrant_id='".$_REQUEST['registrant_id']."';";
			$qry->queryExecute($SQL);
			echo "<script>alert('Email Sent');parent.thickBoxDone();</script>";

		}
		if ($_REQUEST['action']=="Delete"){
			$SQL = "DELETE FROM tbl_registrant_attendance WHERE registrant_id='".$_REQUEST['registrant_id']."';";
			$qry->queryExecute($SQL);
			$SQL = "DELETE FROM tbl_registrant WHERE registrant_id='".$_REQUEST['registrant_id']."';";
			$qry->queryExecute($SQL);
			echo "<script>alert('Delegate deleted.');parent.thickBoxDone();</script>";
		}
	}
	$SQL = "SELECT * FROM tbl_registrant WHERE registrant_id='".$_REQUEST['registrant_id']."';";
	$result = $qry->querySelectSingle($SQL);
	echo "<center><font face='Arial' size='-1'>";
	echo "<table style='font-family:verdana;font-size:12px;width:550px;background:#CCCCFF;border:1px solid #000099;padding:3px;'>";
	echo "<tr><td width='30%'><b>Salutation: </b></td><td><select name='registrant_salutation'>";
	if ($result['registrant_salutation']=="Prof")
		echo "<option value='Prof' selected>Prof</option>";
	else
		echo "<option value='Prof'>Prof</option>";
	if ($result['registrant_salutation']=="A/Prof")
		echo "<option value='A/Prof' selected>A/Prof</option>";
	else
		echo "<option value='A/Prof'>A/Prof</option>";
	if ($result['registrant_salutation']=="Dr")
		echo "<option value='Dr' selected>Dr</option>";
	else
		echo "<option value='Dr'>Dr</option>";
	if ($result['registrant_salutation']=="Mr")
		echo "<option value='Mr' selected>Mr</option>";
	else
		echo "<option value='Mr'>Mr</option>";
	if ($result['registrant_salutation']=="Mrs")
		echo "<option value='Mrs' selected>Mrs</option>";
	else
		echo "<option value='Mrs'>Mrs</option>";
	if ($result['registrant_salutation']=="Ms")
		echo "<option value='Ms' selected>Ms</option>";
	else
		echo "<option value='Ms'>Ms</option>";
	echo "</select></td></tr>";
	echo "<tr><td width='30%'><b>Surname: </b></td><td><input type='text' name='registrant_surname' value='".$result['registrant_surname']."' size='50'></td></tr>";
	echo "<tr><td width='30%'><b>Given Name: </b></td><td><input type='text' name='registrant_givenname' value='".$result['registrant_givenname']."' size='50'></td></tr>";
	echo "<tr><td width='30%'><b>Designation: </b></td><td><input type='text' name='registrant_designation' value='".$result['registrant_designation']."' size='50'></td></tr>";
	echo "<tr><td><b>Company Name: </b></td><td><input type='text' name='registrant_organisation' value='".$result['registrant_organisation']."' size='50'></td></tr>";
	echo "<tr><td><b>Email: </b></td><td><input type='text' name='registrant_email' value='".$result['registrant_email']."' size='50'></td></tr>";
	echo "<tr><td><b>Company Telephone: </b></td><td><input type='text' name='registrant_tel' value='".$result['registrant_tel']."' size='50'></td></tr>";
	echo "<tr><td><b>Mobile: </b></td><td><input type='text' name='registrant_mobile' value='".$result['registrant_mobile']."' size='50'></td></tr>";
	echo "<tr><td><b>Address 1: </b></td><td><input type='text' name='registrant_add1' value='".$result['registrant_add1']."' size='50'></td></tr>";
	echo "<tr><td><b>Address 2: </b></td><td><input type='text' name='registrant_add2' value='".$result['registrant_add2']."' size='50'></td></tr>";
	echo "<tr><td><b>Postal Code: </b></td><td><input type='text' name='registrant_postal' value='".$result['registrant_postal']."' size='50'></td></tr>";
	echo "<tr><td><b>PA Name: </b></td><td><input type='text' name='registrant_paname' value='".$result['registrant_paname']."' size='50'></td></tr>";
	echo "<tr><td><b>PA Email: </b></td><td><input type='text' name='registrant_pamail' value='".$result['registrant_pamail']."' size='50'></td></tr>";
	echo "<tr><td width='30%' align='left'><b>Attending: </b></td><td width='60%' align='left'><input type='radio' name='registrant_attending' value='ATTENDING' "; if($result['registrant_attending']=='ATTENDING') {echo "checked";} echo "> Yes <input type='radio' value='NOT ATTENDING' name='registrant_attending' "; if($result['registrant_attending']=='NOT ATTENDING') {echo "checked";} echo "> No </td></tr>";
	echo "<tr><td width='30%'><b>Require confirmation via: </b></td><td><select name='registrant_confirmation'>";
	if ($result['registrant_confirmation']=="0")
		echo "<option value='0' selected>Please Select</option>";
	else
		echo "<option value='0'>Please Select</option>";
	if ($result['registrant_confirmation']=="SMS")
		echo "<option value='SMS' selected>SMS</option>";
	else
		echo "<option value='SMS'>SMS</option>";
	if ($result['registrant_confirmation']=="Email")
		echo "<option value='Email' selected>Email</option>";
	else
		echo "<option value='Email'>Email</option>";
	if ($result['registrant_confirmation']=="Both")
		echo "<option value='Both' selected>Both</option>";
	else
		echo "<option value='Both'>Both</option>";
	echo "</select></td></tr>";
	echo "<tr><td width='30%'><b>Dietary Selection: </b></td><td><select name='registrant_diet'>";
	if ($result['registrant_diet']=="0")
		echo "<option value='0' selected>Please Select</option>";
	else
		echo "<option value='0'>Please Select</option>";
	if ($result['registrant_diet']=="Vegetarian")
		echo "<option value='Vegetarian' selected>Vegetarian</option>";
	else
		echo "<option value='Vegetarian'>Vegetarian</option>";
	if ($result['registrant_diet']=="Fish")
		echo "<option value='Fish' selected>Fish</option>";
	else
		echo "<option value='Fish'>Fish</option>";
	if ($result['registrant_diet']=="Beef")
		echo "<option value='Beef' selected>Beef</option>";
	else
		echo "<option value='Beef'>Beef</option>";
	echo "</select></td></tr>";
	echo "<tr><td width='30%'><b>Other Dietary Restriction: </b></td><td><input type='text' name='registrant_dietremarks' value='".$result['registrant_dietremarks']."' size='20'></td></tr>";
	$SQL = "SELECT * FROM tbl_table ORDER BY tbl_name";
	$tableresults=$qry->queryMultiSelect($SQL);
	$tablechoices = "<option value='0'>N.A.</option>";
	foreach ($tableresults as $tableresult){
		if ($result['tbl_id']==$tableresult['tbl_id'])
			$tablechoices.= "<option value='".$tableresult['tbl_id']."' selected>".$tableresult['tbl_name']."</option>";
		else
			$tablechoices.= "<option value='".$tableresult['tbl_id']."'>".$tableresult['tbl_name']."</option>";
	}
	echo "<tr><td><b>Table Seating:</b></td><td><select name='table_id' $enabled>$tablechoices</select></td></tr>";
	echo "<tr><td><b>Seat Number: </b></td><td><input type='text' name='seat_number' value='".$result['seat_number']."' size='50'></td></tr>";
	echo "</table><br><br>";
	echo "<input type='hidden' name='registrant_id' value='".$_REQUEST['registrant_id']."'>";
	$SQL = "SELECT count(*) as number FROM tbl_registrant_attendance WHERE registrant_id='".$_REQUEST['registrant_id']."';";
	$result = $qry->querySelectSingle($SQL);
	if ($result['number']!=0){
		$SQL = "SELECT * FROM tbl_registrant_attendance WHERE registrant_id='".$_REQUEST['registrant_id']."';";
		$result = $qry->querySelectSingle($SQL);
		echo "<br>This delegate has registered his attendance at ".date("d-m-Y @ h:ia",strtotime($result['attendance_datetime']));
		echo "<br>";
	}
	echo "</font>";
	echo "<input type='submit' name='action' value='Send Confirmation Email'><input type='submit' name='action' value='Update'><input type='submit' name='action' value='Delete' onclick=\"return confirm('Do you really want to delete?');\">";
	echo "</center>";

?>
</form>