/recruitment/apply/submit_application_exec.php

<?php

	if (!isset($_SESSION)) {

		session_start();

	}



	if(!isset($_SESSION['user_account_row_id']) || $_SESSION['user_account_row_id'] < 1){

		include_once('../access_error.php');

		$redirectToURL = '../';

		

		header("location: $redirectToURL");

		exit();

	}



	$accountID = (int)$_SESSION['user_account_row_id'];

	

	if(!isset($_SESSION['msgDetails'])){

		$_SESSION['msgCaption'] = '';

		$_SESSION['msgDetails'] = '';

	}

	

	require_once('../helper_functions.php');

	require_once('../../lib/database.php');

	openConnection();

	

	$redirectToURL = '../profile/profile.php';

	//clear_application_submit_session_vars();

	$advert_id = $_SESSION['advert_id'] + 1024;

	

	if( isset($_POST['btnSubmitApplication']) || isset($_POST['btnCancel']) ){

		if (isset($_POST['btnCancel']) ) {

			header("location: $redirectToURL");

			exit();

		}else if (isset($_POST['btnSubmitApplication'])) {

			$comments = trim($_POST['comments']);

		

			if(!$comments || mb_strlen($comments = trim($comments)) == 0){

				$_SESSION['app_Comments'] = 'No Comments';

			}else{

				$_SESSION['app_Comments'] = $comments;

			}

			

			if (submit_validation_errors() > 0){

				//there were validation errors

				//and the result is stored in $_SESSION['msgDetails']

				

				$redirectToURL = './?ref_tag=' . $advert_id;

			}else{

				//there were NO validation errors; poceed to save data:

				

				$r_id = $_SESSION['advt_response_id'];

				$comments = clean($_SESSION['app_Comments']);

				$dt = date('Y-m-d');

				

				$q = "UPDATE tbladvertresponses SET datecompleted = '$dt', timecompleted = CURTIME(), applicantcomments = '$comments' ";

				$q .= "WHERE responseid = $r_id";

				$query = mysql_query($q);

			

				if(mysql_affected_rows() == 1){

					$c = 'OK';

					$e = '';

					

					//capture the CV 'as is' at the time of submitting this application:

					$applicant_row_id = $_SESSION['user_account_row_id'];

					

					$q = "SELECT d.titleid, d.surname, d.firstname, d.othernames, d.gender, d.maritalstatus, d.physicalchallenge, d.birthdate, ";

					$q .= "d.birthplace, d.hometown, d.lga, d.state, d.nationality, d.personnelid, d.profile_pic, q.yearobtained, q.institutionobtained, ";

					$q .= "q.fieldofstudyid, q.qualificationtypeid, q.degreeclassid, ql.certificate, qc.qualification AS degreeclass, sf.studyfield, ";

					$q .= "c.primaryphone, c.currentaddress, c.permanenthomeaddress, op.phonenumber as otherphone, op.alternateemail, ";

					$q .= "lg.lganame, st.statename, cc.countryname  FROM tblapplicantsdetails AS d ";

					$q .= "LEFT JOIN tblapplicantshighestqual AS q ON d.appaccountrowid = q.appaccountrowid ";

					$q .= "LEFT JOIN tblqualificationslookup AS ql ON q.qualificationtypeid = ql.id ";

					$q .= "LEFT JOIN tblqualificationclass AS qc ON q.degreeclassid = qc.qualificationclassid ";

					$q .= "LEFT JOIN tblstudyfieldlookup AS sf ON q.fieldofstudyid = sf.id ";

					$q .= "LEFT JOIN tblapplicantscontacts AS c ON q.appaccountrowid = c.appaccountrowid ";

					$q .= "LEFT JOIN tblapplicantsotherphones AS op ON q.appaccountrowid = op.appaccountrowid ";

					$q .= "LEFT JOIN tbllga as lg ON d.lga = lg.lgaid ";

					$q .= "LEFT JOIN tblstate AS st ON d.state = st.stateid ";

					$q .= "LEFT JOIN tblcountries AS cc ON d.nationality = cc.countryid ";

					$q .= "WHERE d.appaccountrowid = $applicant_row_id";



					$query = mysql_query($q);

					$foundRecord = mysql_num_rows($query);



					if ($foundRecord == 1) {

						$row = mysql_fetch_assoc($query);

						

						$t_id = $row['titleid'];

						$s_name = $row['surname'];

						$f_name = $row['firstname'];

						$o_name = $row['othernames'];

						$gender = $row['gender'];

						$m_status = $row['maritalstatus'];

						$p_challenge = $row['physicalchallenge'];

						$b_date = $row['birthdate'];

						$b_place = $row['birthplace'];

						$h_town = $row['hometown'];

						$p_id = $row['personnelid'];

						$pic = $row['profile_pic'];

						$qual = $row['certificate'];

						$q_year = $row['yearobtained'];

						$q_inst = $row['institutionobtained'];

						$q_field = $row['studyfield'];

						$q_class = $row['degreeclass'];

						$p_phone = $row['primaryphone'];

						$c_address = $row['currentaddress'];

						$p_address = $row['permanenthomeaddress'];

						$o_phone = $row['otherphone'];

						$alt_email = $row['alternateemail'];



						$lga = 'N/A';

						if(isset($row['lganame'])){

							$lga = $row['lganame'];

						}

						

						$state = 'N/A';

						if(isset($row['statename'])){

							$state = $row['statename'];

						}

						

						$nationality = 'N/A';

						if(isset($row['countryname'])){

							$nationality = $row['countryname'];

						}

						

						$q = "INSERT INTO tblapplicantscvs (advertresponseid, titleid, surname, firstname, othernames, gender, maritalstatus, physicalchallenge, ";

						$q .= "birthdate, birthplace, hometown, lga, state, nationality, personnelid, profile_pic, highestqualification, yearobtained, ";

						$q .= "institutionobtained, fieldofstudy, degreeclass, mainphone, otherphone, alternateemail, contactaddress, permanenthomeaddress) ";

						$q .= "VALUES ($r_id, $t_id, '$s_name', '$f_name', '$o_name', '$gender', '$m_status', '$p_challenge', '$b_date', '$b_place', '$h_town', ";

						$q .= "'$lga', '$state', '$nationality', '$p_id', '$pic', '$qual', '$q_year', '$q_inst', '$q_field', '$q_class', '$p_phone', ";

						$q .= "'$o_phone', '$alt_email', '$c_address', '$p_address')";

						

						$query = mysql_query($q);

						$lastID = mysql_insert_id();

						

						//also catpture applicant's professional qualifications "as is" at this point:

						$q = "SELECT p.qualificationid, p.yearobtained, p.awardingbody, lk.name AS qualification_name ";

						$q .= "FROM tblapplicantsprofqualifications AS p INNER JOIN tblprofessionalqualslookup AS lk ";

						$q .= "ON p.qualificationid = lk.id WHERE p.appaccountrowid = $applicant_row_id";

						

						$query = mysql_query($q);

						$foundRecord = mysql_num_rows($query);

						

						if ($foundRecord > 0) {

							while($row = mysql_fetch_assoc($query)){

								$q_name = $row['qualification_name'];

								$q_year = $row['yearobtained'];

								$q_body = $row['awardingbody'];

								

								$q2 = "INSERT INTO tblapplicantscvsprofquals (cvrowid, qualification, yearobtained, awardingbody) ";

								$q2 .= "VALUES ($lastID, '$q_name', '$q_year', '$q_body')";

								

								$query2 = mysql_query($q2);

							}//end while

						}

												

						//also catpture applicant's work experience "as is" at this point:

						$q = "SELECT employer, positionheld, responsibilities, datefrom, dateto FROM tblapplicantsworkexperience WHERE appaccountrowid = $applicant_row_id";

						$query = mysql_query($q);

						$foundRecord = mysql_num_rows($query);

						

						if ($foundRecord > 0) {

							while($row = mysql_fetch_assoc($query)){

								$e_employer = $row['employer'];

								$e_position = $row['positionheld'];

								$e_responsibility = $row['responsibilities'];

								$e_datefrom = $row['datefrom'];

								$e_dateto = $row['dateto'];

								

								$q2 = "INSERT INTO tblapplicantscvsworkexp (cvrowid, employer, positionheld, responsibilities, datefrom, dateto) ";

								$q2 .= "VALUES ($lastID, '$e_employer', '$e_position', '$e_responsibility', '$e_datefrom', '$e_dateto')";

								

								$query2 = mysql_query($q2);

							}//end while

						}

					

					}

							

					clear_application_submit_session_vars();

					$app_id = applicant_submitted_this_application(($advert_id - 1024), $applicant_row_id);

					$redirectToURL = 'print_application.php?ref_tag=' . ($app_id + 1024);

				}else{

					$redirectToURL = './?ref_tag=' . $advert_id;

					$c = 'Error';

					$e = 'There was an error while submitting the job application to the Server: <br />' . mysql_error();

				}

				

				$_SESSION['msgCaption'] = $c;

				$_SESSION['msgDetails'] = '<font style="color:red; font-size:16px">' . $e . '</font>';

			}//end if (submit_validation_errors() > 0)

		}//end if (isset($_POST['btnCancel']))

	}else{

		include_once('access_error.php');

		$redirectToURL = '../';

	}//end if( isset($_POST['btnSave']) || isset($_POST['btnCancel']) )

	

	header("location: $redirectToURL");

	exit();

	

	function submit_validation_errors(){

		$comments = $_SESSION['app_Comments'];

		

		$rT = '';

		$errCount = 0;



		if(mb_strlen(trim($comments)) > 500){

			$rT .= '<li>Your comments should not exceed 500 characters.</li>';

			$errCount += 1;

		}		

		

		if(! is_numeric($_SESSION['advt_response_id']) || (int)$_SESSION['advt_response_id'] < 1){

			$rT .= '<li>Invalid job application ID.</li>';

			$errCount += 1;

		}

		

		$_SESSION['msgCaption'] = '';

		$_SESSION['msgDetails'] = '';

		

		if($errCount > 0){

			if ($errCount == 1){

				$errCaption = 'Ooops! You Have 1 Issue to Fix';

			}else{

				$errCaption = 'Ooops! You Have ' . $errCount . ' Issues to Fix';

			}

			

			$rT = '<ul style="color:#FF0000">' . $rT . '</ul>';

			

			$_SESSION['msgCaption'] = $errCaption;

			$_SESSION['msgDetails'] = $rT;

		}

		

		return $errCount;

	}

	

	

	function clear_application_submit_session_vars(){

		$_SESSION['app_Comments'] = '';

	}

?>