/recruitment/helper_functions.php

<?php

	require_once(realpath(dirname(__FILE__) . "/../lib/globals.php"));

	

	function clean($str) {

		$str = @trim($str);

		if(get_magic_quotes_gpc()) {

			$str = stripslashes($str);

		}

		return mysql_real_escape_string(strip_tags($str,'<br><br /><p></p>'));

	}





	function get_my_date_format($mySQLDate, $MediumLong){

		//$mySQLDate comes in as '2012-01-06' for Jan 6, 2012 or '2012-01-21' for Jan 21, 2012

		

		$returnVal = get_month_name(substr($mySQLDate,5,2),$MediumLong) . substr($mySQLDate,8,2) . ", " . substr($mySQLDate,0,4);

		

		return $returnVal;

	}

	

	

	function get_month_name($monthNumber,$MediumLong) {

		$mthName = "???";

		switch ($monthNumber) {

			case '01':

				$mthName = 'January';

				break;

			case '02':

				$mthName = 'February';

				break;

			case '03':

				$mthName = 'March';

				break;

			case '04':

				$mthName= 'April';

				break;

			case '05':

				$mthName = 'May';

				break;

			case '06':

				$mthName = 'June';

				break;

			case '07':

				$mthName = 'July';

				break;

			case '08':

				$mthName = 'August';

				break;

			case '09':

				$mthName = 'September';

				break;

			case '10':

				$mthName = 'October';

				break;

			case '11':

				$mthName = 'November';

				break;

			case '12':

				$mthName = 'December';

				break;																																								

			default:

				$mthName = 'January';

				break;

		}//end switch

		

		if(strtolower($MediumLong) == "m"){

			$mthName = substr($mthName,0,3);

		}

		return $mthName . ' ';

	}

	

	function get_next_3days($date_reserved){

		//used in pay_advice.php to get the 3-day deadline

		//for applicants to pay and complete their forms



		$q = "SELECT DATE_ADD('$date_reserved', INTERVAL 3 DAY) AS expiry_three_days";

		

		$query = mysql_query($q);

		$row = mysql_fetch_assoc($query);



		return get_my_date_format($row['expiry_three_days'],'L');

	}

	

	function get_age_from_date($date_value, $return_format){

//		$q = "SELECT DATEDIFF(NOW(),'" . $date_value . "') AS age_days";

		$q = "SELECT DATEDIFF('" . $date_value . "', NOW()) AS age_days";

		

		$query = mysql_query($q);

		$row = mysql_fetch_assoc($query);

		$age_in_days = $row['age_days'];

		$returnVal = '&nbsp;&nbsp;&nbsp;(' . $age_in_days . ' days to go).';

		return $returnVal;

	}

	

	

	function get_days_to_go	($date_value){

		$q = "SELECT DATEDIFF('" . $date_value . "', NOW()) AS age_days";

		

		$query = mysql_query($q);

		$row = mysql_fetch_assoc($query);

		$age_in_days = $row['age_days'];



		$returnVal = '&nbsp;&nbsp;&nbsp;(';

		

		switch($age_in_days){

			case 0:

				$returnVal .= 'expires today at 12:00 midnight!)';

				break;

			case 1:

				$returnVal .= 'expires tomorrow at 12:00 midnight!)';

				break;

			default:

				$returnVal .=  $age_in_days . ' days to go).';

				break;

		}

		

		return $returnVal;

	}

	

	function is_future_date($date_value){

		$q = "SELECT DATEDIFF('" . $date_value . "', NOW()) AS date_difference";

		

		$query = mysql_query($q);

		$row = mysql_fetch_assoc($query);

		$date_diff = $row['date_difference'];



		return ($date_diff > 0);

	}

	

	

	function is_date_past($date_value){

		$date_value = get_mysql_date($date_value);

		$q = "SELECT DATEDIFF('" . $date_value . "', NOW()) AS date_difference";

		

		$query = mysql_query($q);

		if(mysql_num_rows($query) > 0){

			$row = mysql_fetch_assoc($query);

			$date_diff = $row['date_difference'];

	

		}else{

			$date_diff = -1;

		}

		

		return ($date_diff < 0);

	}

	

	function is_dateformat_ddmmyyyy($dateStr){

		//we want to date that $dateStr came in the format 'DD/MM/YYYY'

		if(strlen($dateStr) != 10){

			return false;

		}

		

		$dd = substr($dateStr,0,2);

		$mm = substr($dateStr,3,2);

		$yy = substr($dateStr,6,4);

	

		return (checkdate($mm,$dd,$yy));

	}



	function get_mysql_date($dateparameter){

		//$dateparameter = 'DD/MM/YYYY'

		//we want to return 'YYYY-MM-DD'

		$returnDate = "'". substr($dateparameter,6,4) . "-" . substr($dateparameter,3,2) ."-" . substr($dateparameter,0,2) . "'";

		return $returnDate ;

	}

	

	

	function get_date_ddmmyyyy($dateparameter){

		//$dateparameter = mySQL date 'YYYY-MM-DD'

		//we want to return 'DD/MM/YYYY'

		

		$returnDate = substr($dateparameter,8,2) . "/" . substr($dateparameter,5,2) . "/" . substr($dateparameter,0,4);

		return $returnDate ;

	}

	

	

	function is_dateformat_mmmddyyyy($dateStr){

		/*

			$dt=$_POST['dt'];

			//$dt="02/28/2007";

			$arr=split("/",$dt); // splitting the array

			$mm=$arr[0]; // first element of the array is month

			$dd=$arr[1]; // second element is date

			$yy=$arr[2]; // third element is year

			

			If(!checkdate($mm,$dd,$yy)){

				echo "invalid date";

			}else {

				echo "Entry date is correct";

			} */

			

		$rT = 0;

		

		$dateStr = trim($dateStr);

		

		if (!isset($dateStr) || strlen($dateStr) != 10){

			//1980-01-01

			$rT = 0;

		}else{

			$arr = split("-",$dateStr); // splitting the array

			$yy = $arr[0];

			$mm = $arr[1];

			$dd = $arr[2];



			if (strlen($yy) == 4 && strlen($mm) == 2 && strlen($dd) == 2){

				if (is_numeric($yy) && is_numeric($mm) && is_numeric($dd)){

					if(checkdate($mm,$dd,$yy)){

						$rT = 1;

					}

				}

			}

		}//end if (!isset($dateStr) || strlen($dateStr)==0)

	  

		return $rT;

	}

	

	

	function get_full_age_from_date($date_value, $return_format){

		if(!isset($date_value) || strlen($date_value) < 10){

			return 'Unknown Age';

		}

		

		$q = "SELECT DATEDIFF(NOW(),'" . $date_value . "') AS age_days";

		

		$query = mysql_query($q);

		$row = mysql_fetch_assoc($query);

		$age_in_days = $row['age_days'];



		$q = "SELECT FROM_DAYS($age_in_days) AS age_full";

		$query = mysql_query($q);

		$row = mysql_fetch_assoc($query);

		$age_in_full = $row['age_full'];

		

		$age_array = explode("-", $age_in_full);

		$age_years = (int)$age_array[0];

		

		$returnVal = $age_years;

		

		if($return_format == 'Full Details'){

			$age_months = (int)$age_array[1];

			$age_days = (int)$age_array[2];



			$y = ' year';

			$m = ' month';

			$d = ' day';

			

			if($age_years > 1){

				$y .= 's';

			}

			

			if($age_months > 1){

				$m .= 's';

			}

			

			if($age_days > 1){

				$d .= 's';

			}

			

			$y .= ', ';

			$m .= ' and ';

			$d .= ' old today';

			

			$returnVal = $age_years . $y . $age_months . $m . $age_days . $d;

		}

		

		return $returnVal;

	}

	

	function is_biodata_complete($applicantid){

		$returnVal = false;



		$q = "SELECT rowid, surname, firstname, othernames, gender, maritalstatus, physicalchallenge, birthdate, birthplace, hometown, lga, nationality ";

		$q .= "FROM tblapplicantsdetails WHERE appaccountrowid = $applicantid";

		

		$query = mysql_query($q);

		$foundRecord = mysql_num_rows($query);

		

		if ($foundRecord == 1) {

			$row = mysql_fetch_assoc($query);

			

		$returnVal = ( isset($row['gender']) && (strlen($row['gender']) == 1) && isset($row['birthdate']) && isset($row['hometown']) && isset($row['nationality']) && $row['nationality'] > 0);

		}

		

		return $returnVal;

	}

	

	

	function is_academic_qualification_complete($applicantid){

		$returnVal = false;



		$q = "SELECT rowid FROM tblapplicantshighestqual WHERE appaccountrowid = $applicantid";

		

		$query = mysql_query($q);

		$foundRecord = mysql_num_rows($query);

		

		return ($foundRecord == 1);

	}

	

	

	function is_primary_contact_complete($applicantid){

		$returnVal = false;



		$q = "SELECT currentaddress, permanenthomeaddress ";

		$q .= "FROM tblapplicantscontacts WHERE appaccountrowid = $applicantid";

		

		$query = mysql_query($q);

		$foundRecord = mysql_num_rows($query);

		

		if ($foundRecord == 1) {

			$row = mysql_fetch_assoc($query);

			$returnVal = ( isset($row['currentaddress']) && (strlen($row['currentaddress']) > 0) && isset($row['permanenthomeaddress']) && (strlen($row['permanenthomeaddress']) > 0) );

		}

		

		return $returnVal;

	}

	

	

	function count_applicant_professional_quals($applicantid){

		$returnVal = 0;



		$q = "SELECT COUNT(rowid) AS tot FROM tblapplicantsprofqualifications WHERE appaccountrowid = $applicantid";

		$query = mysql_query($q);

		$foundRecord = mysql_num_rows($query);

		

		if($foundRecord > 0){

			$row = mysql_fetch_assoc($query);

			$returnVal = $row['tot'];

		}

		

		return $returnVal;

	}





	function count_applicant_work_experience($applicantid){

		$returnVal = 0;



		$q = "SELECT COUNT(workingexpid) AS tot FROM tblapplicantsworkexperience WHERE appaccountrowid = $applicantid";

		$query = mysql_query($q);

		$foundRecord = mysql_num_rows($query);

		

		if($foundRecord > 0){

			$row = mysql_fetch_assoc($query);

			$returnVal = $row['tot'];

		}

		

		return $returnVal;

	}

	

	function get_job_title_from_advert_id($ad_id){

		$returnVal = '';

		

		if(!isset($ad_id) || !is_numeric($ad_id) || ((int)$ad_id < 0) ){

			$ad_id = 0;

		}

		

		$q = "SELECT tbladvertisements.vacancyid, tblvacancies.rankid, tblrank.name as position_name, tbldepartment.name as department_name ";

		$q .= "FROM tbladvertisements LEFT JOIN tblvacancies ON tbladvertisements.vacancyid = tblvacancies.vacancyid LEFT JOIN tblrank ON ";

		$q .= "tblvacancies.rankid = tblrank.rankid LEFT JOIN tbldepartment ON tblvacancies.departmentid = tbldepartment.departmentid WHERE advertisementid = $ad_id";

		

		$query = mysql_query($q);

		$foundRecord = mysql_num_rows($query);



		if ($foundRecord == 1) {

			$row = mysql_fetch_assoc($query);

			$returnVal = '<strong>'. $row['position_name'] . ' (' . $row['department_name'].')</strong>';

		}else {

			$returnVal = 'Job Position Undefined';

		}//end if ($foundRecord == 1)

		

		return $returnVal;

	}





	function is_exist_item($item_value, $item_value_is_num, $column_name, $table_name){

		//for this function to work, the target table must have a column called "row_id"

		

		$param_item_value = clean($item_value);

		$param_column_name = clean($column_name);

		$param_table_name = clean($table_name);

		

		$q = "SELECT row_id FROM $param_table_name WHERE $param_column_name = ";



		if($item_value_is_num == 1){

			$q .= "$param_item_value";

		}else{

			$q .= "'$param_item_value'";

		}



		$query = mysql_query($q);

		$foundRecord = mysql_num_rows($query);

		

		return $foundRecord > 0;

	}





	function is_exist_nok($ad_response_id){

		$ad_response_id = clean($ad_response_id);

		

		$q = "SELECT rowid FROM tblapplicantsnok WHERE advertresponseid = $ad_response_id";

		

		$query = mysql_query($q);

		$foundRecord = mysql_num_rows($query);

		

		return ($foundRecord > 0);	

	}

	

	function is_exist_score_item($item){

		$item = clean($item);

		

		$q = "SELECT scoreitemid FROM tblinterviewscoreitems WHERE scoreitemdescription = '$item'";

		

		$query = mysql_query($q);

		$foundRecord = mysql_num_rows($query);

		

		return ($foundRecord > 0);

	}





	function is_exist_position($pos_id){

		$pos_id = clean($pos_id);

		

		if(! is_numeric($pos_id)){

			$pos_id = 0;

		}

		

		$q = "SELECT name FROM tblrank WHERE rankid = $pos_id";

		

		$query = mysql_query($q);

		$foundRecord = mysql_num_rows($query);

		

		return ($foundRecord > 0);

	}

	

	function is_exist_job_application_id($job_app_id){

		$job_app_id = clean($job_app_id);

		

		if(! is_numeric($job_app_id)){

			$job_app_id = 0;

		}

		

		$q = "SELECT advertid FROM tbladvertresponses WHERE responseid = $job_app_id";

		

		$query = mysql_query($q);

		$foundRecord = mysql_num_rows($query);

		

		return ($foundRecord > 0);

	}

	

	

	function is_exist_vacancy_id($vacancy_id){

		$vacancy_id = clean($vacancy_id);

		

		if(! is_numeric($vacancy_id)){

			$vacancy_id = 0;

		}

		

		$_SESSION['vac_PositionID'] = 0;

		$_SESSION['vac_DepartmentID'] = 0;

		$_SESSION['vac_WorkSchedule'] = '';

		$_SESSION['vac_WorkShifts'] = '';

		$_SESSION['vac_WorkHours'] = '';

		$_SESSION['vac_Slots'] = '';

		$_SESSION['vac_PosSummary'] = '';

		$_SESSION['vac_ExperienceRequirements'] = '';

		$_SESSION['vac_EducationRequirements'] = '';

		$_SESSION['vac_Advantage'] = '';

		$_SESSION['vac_Reason'] = 0;

		$_SESSION['vac_Status'] = '';

		$_SESSION['vac_VacantDate'] = '';



		$q = "SELECT rankid, departmentid, vacantslots, jobschedule, jobshifts, jobhours, positionsummary, educationrequirements, experiencerequirements, ";

		$q .= "additional_requirements, status, vacancyreasonid, effectivedate FROM tblvacancies WHERE vacancyid = $vacancy_id";



		$query = mysql_query($q);

		$foundRecord = mysql_num_rows($query);



		if($foundRecord == 1){

			$row = mysql_fetch_assoc($query);



			$_SESSION['vac_PositionID'] = $row['rankid'];

			$_SESSION['vac_DepartmentID'] = $row['departmentid'];

			$_SESSION['vac_WorkSchedule'] = $row['jobschedule'];

			$_SESSION['vac_WorkShifts'] = $row['jobshifts'];

			$_SESSION['vac_WorkHours'] = $row['jobhours'];

			$_SESSION['vac_Slots'] = $row['vacantslots'];

			$_SESSION['vac_PosSummary'] = $row['positionsummary'];

			$_SESSION['vac_ExperienceRequirements'] = $row['experiencerequirements'];

			$_SESSION['vac_EducationRequirements'] = $row['educationrequirements'];

			$_SESSION['vac_Advantage'] = $row['additional_requirements'];

			$_SESSION['vac_Reason'] = $row['vacancyreasonid'];

			$_SESSION['vac_Status'] = $row['status'];

			$_SESSION['vac_VacantDate'] = $row['effectivedate'];

		}

				

		return ($foundRecord > 0);

	}





	function is_exist_advert_id($advert_id){

		$advert_id = clean($advert_id);

		

		if(! is_numeric($advert_id)){

			$advert_id = 0;

		}

		$q = "SELECT adverttype FROM tbladvertisements WHERE advertisementid = $advert_id";



		$query = mysql_query($q);

		$foundRecord = mysql_num_rows($query);

				

		return ($foundRecord > 0);

	}





	function load_schedule_session_vars($schedule_id){

		$_SESSION['biz_MaxThreshold'] = 0;

		$_SESSION['biz_MaxPixChange'] = 0;

		$_SESSION['biz_MaxBiodataEdit'] = 0;

		$_SESSION['biz_MaxJobApps'] = 0;

		$_SESSION['biz_Referees'] = 0;



		$q = "SELECT threshold, maxpicturechange, maxbiodataedit, maxjobapplications, requiredreferees FROM tblrecruitmentsettings WHERE rowid = 1";



		$query = mysql_query($q);

		$foundRecord = mysql_num_rows($query);



		if($foundRecord == 1){

			$row = mysql_fetch_assoc($query);

	

			$_SESSION['biz_MaxThreshold'] = $row['threshold'];

			$_SESSION['biz_MaxPixChange'] = $row['maxpicturechange'];

			$_SESSION['biz_MaxBiodataEdit'] = $row['maxbiodataedit'];

			$_SESSION['biz_MaxJobApps'] = $row['maxjobapplications'];

			$_SESSION['biz_Referees'] = $row['requiredreferees'];

		}

	}

	

	

	function load_settings_into_session_vars(){

		$_SESSION['biz_MaxThreshold'] = 0;

		$_SESSION['biz_MaxPixChange'] = 0;

		$_SESSION['biz_MaxBiodataEdit'] = 0;

		$_SESSION['biz_MaxJobApps'] = 0;

		$_SESSION['biz_Referees'] = 0;



		$q = "SELECT threshold, maxpicturechange, maxbiodataedit, maxjobapplications, requiredreferees FROM tblrecruitmentsettings WHERE rowid = 1";



		$query = mysql_query($q);

		$foundRecord = mysql_num_rows($query);



		if($foundRecord == 1){

			$row = mysql_fetch_assoc($query);

	

			$_SESSION['biz_MaxThreshold'] = $row['threshold'];

			$_SESSION['biz_MaxPixChange'] = $row['maxpicturechange'];

			$_SESSION['biz_MaxBiodataEdit'] = $row['maxbiodataedit'];

			$_SESSION['biz_MaxJobApps'] = $row['maxjobapplications'];

			$_SESSION['biz_Referees'] = $row['requiredreferees'];

		}

	}

	

	

	function load_workschedules_into_combo($sess_schedule){

		$returnVal = "<option value = '-1'>-- Please Select --</option>";

		switch ($sess_schedule){

			case 'Full-Time';

				$returnVal .= "<option value = 'Full-Time' selected='selected'>Full Time</option>";

           		$returnVal .= "<option value = 'Part-Time'>Part Time</option>";

          		$returnVal .= "<option value = 'Full and Part Time'>Full and Part Time</option>";

				break;

			case 'Part-Time';

				$returnVal .= "<option value = 'Full-Time'>Full Time</option>";

           		$returnVal .= "<option value = 'Part-Time' selected='selected'>Part Time</option>";

          		$returnVal .= "<option value = 'Full and Part Time'>Full and Part Time</option>";

				break;

			case 'Full and Part Time';

				$returnVal .= "<option value = 'Full-Time'>Full Time</option>";

           		$returnVal .= "<option value = 'Part-Time'>Part Time</option>";

          		$returnVal .= "<option value = 'Full and Part Time' selected='selected'>Full and Part Time</option>";

				break;

			default:

				$returnVal .= "<option value = 'Full-Time'>Full Time</option>";

           		$returnVal .= "<option value = 'Part-Time'>Part Time</option>";

          		$returnVal .= "<option value = 'Full and Part Time'>Full and Part Time</option>";

				break;

		}



		return $returnVal;

	}

	



	function load_workshifts_into_combo($sess_shift){

		$returnVal = "<option value = '-1'>-- Please Select --</option>";

		switch ($sess_shift){

			case 'Days';

				$returnVal .= "<option value = 'Days' selected='selected'>Days</option>";

           		$returnVal .= "<option value = 'Nights'>Nights</option>";

          		$returnVal .= "<option value = 'Days and Nights'>Days and Nights</option>";

				break;

			case 'Nights';

				$returnVal .= "<option value = 'Days'>Days</option>";

           		$returnVal .= "<option value = 'Nights' selected='selected'>Nights</option>";

          		$returnVal .= "<option value = 'Days and Nights'>Days and Nights</option>";

				break;

			case 'Days and Nights';

				$returnVal .= "<option value = 'Days'>Days</option>";

           		$returnVal .= "<option value = 'Nights'>Nights</option>";

          		$returnVal .= "<option value = 'Days and Nights' selected='selected'>Days and Nights</option>";

				break;

			default:

				$returnVal .= "<option value = 'Days'>Days</option>";

           		$returnVal .= "<option value = 'Nights'>Nights</option>";

          		$returnVal .= "<option value = 'Days and Nights'>Days and Nights</option>";

				break;

		}



		return $returnVal;

	}

	

	function load_vacancyreasons_into_combo($sess_reason){

		$returnVal = '';



		$q = "SELECT rowid, vacancyreason FROM tblvacancyreasons ORDER BY vacancyreason";

		$query = mysql_query($q);		

		$foundRecord = mysql_num_rows($query);



		if ($foundRecord > 0) {

			$returnVal .= "<option value = '-1'>-- Please Select --</option>";

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

				if($row['rowid'] == $sess_reason){

					$returnVal .= "<option value='" . $row['rowid'] . "' selected='selected'>" . $row['vacancyreason'] . "</option>";

				}else{

					$returnVal .= "<option value='" . $row['rowid'] . "'>" . $row['vacancyreason'] . "</option>";

				}

			}

		}else{

			$returnVal = "<option value = '-1'>No Data in Vacancy Reasons Table</option>";

		}//end if ($foundRecord == 1)



		return $returnVal;

	}

	

	

	function load_positionstatus_into_combo($sess_status){

		$returnVal = "<option value = '-1'>-- Please Select --</option>";



		switch ($sess_status){

			case 'Occupied';

				$returnVal .= "<option value = 'Occupied' selected='selected'>Occupied</option>";

           		$returnVal .= "<option value = 'Not Occupied'>Not Occupied</option>";

				break;

			case 'Not Occupied';

				$returnVal .= "<option value = 'Occupied'>Occupied</option>";

           		$returnVal .= "<option value = 'Not Occupied' selected='selected'>Not Occupied</option>";

				break;

			default:

				$returnVal .= "<option value = 'Occupied'>Occupied</option>";

           		$returnVal .= "<option value = 'Not Occupied'>Not Occupied</option>";

				break;

		}



		return $returnVal;

	}

	

	function load_interview_panels_into_combo($sess_panel_id){

		$returnVal = '';



		$q = "SELECT rowid, panelname FROM tblinterviewpanels ORDER BY panelname";

		$query = mysql_query($q);		

		$foundRecord = mysql_num_rows($query);



		if ($foundRecord > 0) {

			$returnVal .= "<option value = '-1'>-- Please Select --</option>";

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

				if($row['rowid'] == $sess_panel_id){

					$returnVal .= "<option value='" . $row['rowid'] . "' selected='selected'>" . $row['panelname'] . "</option>";

				}else{

					$returnVal .= "<option value='" . $row['rowid'] . "'>" . $row['panelname'] . "</option>";

				}

			}

		}else{

			$returnVal = "<option value = '-1'>No Data in Panels Table</option>";

		}//end if ($foundRecord == 1)



		return $returnVal;

	}

	

	

	function is_exist_interview_panel_id($panel_ID){

		$_SESSION['pnl_PanelName'] = '';

		$_SESSION['pnl_DateConstituted'] = '';

		$_SESSION['pnl_ConstitutedBy'] = '';



		$panel_ID = clean($panel_ID);

		

		if(! is_numeric($panel_ID)){

			$panel_ID = 0;

		}

		

		$q = "SELECT panelname, dateconstituted, constitutedby FROM tblinterviewpanels WHERE rowid = $panel_ID";



		$query = mysql_query($q);

		$foundRecord = mysql_num_rows($query);



		if($foundRecord == 1){

			$row = mysql_fetch_assoc($query);

			$_SESSION['pnl_PanelName'] = $row['panelname'];

			$_SESSION['pnl_DateConstituted'] = $row['dateconstituted'];

			$_SESSION['pnl_ConstitutedBy'] = $row['constitutedby'];

		}

		

		return ($foundRecord == 1);

	}

	

	

	function panel_is_used_in_records($panel_id){

		$returnVal = true; //assume is used

//tblinterviewresults

		

		$q = "SELECT schedulerowid FROM tblinterviewschedules WHERE panelrowid = $panel_id";



		$query = mysql_query($q);

		$returnVal = (mysql_num_rows($query) > 0);

		

		return $returnVal;

	}

	

	

	function is_exist_interview_panel_member_id($member_ID){

		$_SESSION['mem_PanelID'] = 0;

		$_SESSION['mem_FullNames'] = '';

		$_SESSION['mem_PNumber'] = '';

		$_SESSION['mem_Department'] = 0;

		$_SESSION['mem_Role'] = '';



		$member_ID = clean($member_ID);

		

		if(! is_numeric($member_ID)){

			$member_ID = 0;

		}

		

		$q = "SELECT panelrowid, memberfullname, personnelnumber, memberdepartmentid, memberrole, tbldepartment.name as department FROM tblinterviewpanelmembers ";

		$q .= "LEFT JOIN tbldepartment ON tblinterviewpanelmembers.memberdepartmentid = tbldepartment.departmentid WHERE tblinterviewpanelmembers.rowid = $member_ID";

		

		$query = mysql_query($q);

		$foundRecord = mysql_num_rows($query);

		

		if($foundRecord == 1){

			$row = mysql_fetch_assoc($query);

			$_SESSION['mem_PanelID'] = $row['panelrowid'];

			$_SESSION['mem_FullNames'] = $row['memberfullname'];

			$_SESSION['mem_PNumber'] = $row['personnelnumber'];

			$_SESSION['mem_Department'] = $row['memberdepartmentid'];

			$_SESSION['mem_Role'] = $row['memberrole'];

		}

		

		return ($foundRecord == 1);

	}

	

	function is_exist_interview_schedule_id($schedule_id){

		$schedule_id = clean($schedule_id);

		

		if(! is_numeric($schedule_id)){

			$schedule_id = 0;

		}

		

		$_SESSION['schedule_AdvertID'] = 0;

		$_SESSION['schedule_StartDate'] = '';

		$_SESSION['schedule_EndDate'] = '';

		$_SESSION['schedule_Venue'] = '';

		$_SESSION['schedule_Times'] = '';

		$_SESSION['schedule_Parentpanel'] = 'Unknown Panel';

		

		$q = "SELECT panelrowid, advertid, startdate, enddate, sittingvenue, sittingtimes, panelname ";

		$q .= "FROM tblinterviewschedules INNER JOIN tblinterviewpanels ON tblinterviewschedules.panelrowid = tblinterviewpanels.rowid ";

		$q .= "WHERE schedulerowid = $schedule_id";



		$query = mysql_query($q);

		$foundRecord = mysql_num_rows($query);

		

		if($foundRecord == 1){

			$row = mysql_fetch_assoc($query);

			$_SESSION['schedule_AdvertID'] = $row['advertid'];

			$_SESSION['schedule_StartDate'] = $row['startdate'];

			$_SESSION['schedule_EndDate'] = $row['enddate'];

			$_SESSION['schedule_Venue'] = $row['sittingvenue'];

			$_SESSION['schedule_Times'] = $row['sittingtimes'];

			$_SESSION['schedule_Parentpanel'] = $row['panelname'];

		}

		

		return ($foundRecord > 0);

	}

	

	

	function is_exist_department($dept_id){

		$dept_id = clean($dept_id);

		

		if(! is_numeric($dept_id)){

			$dept_id = 0;

		}

		

		$q = "SELECT name FROM tbldepartment WHERE departmentid = $dept_id";



		$query = mysql_query($q);

		$foundRecord = mysql_num_rows($query);

		

		return ($foundRecord > 0);

	}

	

	function is_exist_unit($unit_id){

		$unit_id = clean($unit_id);

		

		if(! is_numeric($unit_id)){

			$unit_id = 0;

		}

		

		$q = "SELECT unitname FROM tblunits WHERE unitid = $unit_id";



		$query = mysql_query($q);

		$foundRecord = mysql_num_rows($query);

		

		return ($foundRecord > 0);

	}

	

	

	function is_exist_vacancyreason($vac_reason_id){

		$vac_reason_id = clean($vac_reason_id);

		

		if(! is_numeric($vac_reason_id)){

			$vac_reason_id = 0;

		}

		

		$q = "SELECT vacancyreason FROM tblvacancyreasons WHERE rowid = $vac_reason_id";



		$query = mysql_query($q);

		$foundRecord = mysql_num_rows($query);

		

		return ($foundRecord > 0);

	}

	

	

	

	function is_exist_user_account_id($user_id){

		$user_id = clean($user_id);

		

		if(! is_numeric($user_id)){

			$user_id = 0;

		}

		

		$q = "SELECT datecreated FROM tblapplicantsaccount WHERE account_rowid = $user_id";



		$query = mysql_query($q);

		$foundRecord = mysql_num_rows($query);

		

		return ($foundRecord > 0);

	}

	

	function is_exist_user_alternate_phone($user_id){

		$user_id = clean($user_id);

		

		if(! is_numeric($user_id)){

			$user_id = 0;

		}

		

		$q = "SELECT rowid FROM tblapplicantsotherphones WHERE appaccountrowid = $user_id";



		$query = mysql_query($q);

		$foundRecord = mysql_num_rows($query);

		

		return ($foundRecord > 0);

	}

	

	

	function is_exist_user_highest_qualification($user_id){

		$user_id = clean($user_id);

		

		if(! is_numeric($user_id)){

			$user_id = 0;

		}

		

		$q = "SELECT rowid FROM tblapplicantshighestqual WHERE appaccountrowid = $user_id";



		$query = mysql_query($q);

		$foundRecord = mysql_num_rows($query);

		

		return ($foundRecord > 0);

	}

	

	

	function is_exist_user_prof_qualification($user_id, $qual_id){

		$user_id = clean($user_id);

		$qual_id = clean($qual_id);

		

		if(!is_numeric($user_id) || !is_numeric($qual_id)){

			return true;

		}

		

		$q = "SELECT rowid FROM tblapplicantsprofqualifications WHERE appaccountrowid = $user_id AND qualificationid = $qual_id";



		$query = mysql_query($q);

		$foundRecord = mysql_num_rows($query);

		

		return ($foundRecord > 0);

	}

	

	

	function is_exist_prof_qualification($qual_ID){

		$_SESSION['prof_Qualification'] = 0;

		$_SESSION['prof_AwardBody'] = '';

		$_SESSION['prof_AwardYear'] = '';

		

		$qual_row_id = (int)$qual_ID;		

		if(!is_numeric($qual_row_id)){

			return false;

		}

		

		$q = "SELECT qualificationid, yearobtained, awardingbody FROM tblapplicantsprofqualifications WHERE rowid = $qual_row_id";

		$query = mysql_query($q);

		$foundRecord = mysql_num_rows($query);

		

		if($foundRecord == 1){

			$row = mysql_fetch_assoc($query);

			$_SESSION['prof_Qualification'] = $row['qualificationid'];

			$_SESSION['prof_AwardBody'] = $row['awardingbody'];

			$_SESSION['prof_AwardYear'] = $row['yearobtained'];

		}

		

		return ($foundRecord == 1);

	}

	

	

	function is_exist_work_experience($exp_ID){

		$_SESSION['exp_Employer'] = '';

		$_SESSION['exp_Position'] = '';

		$_SESSION['exp_StartDate'] = '';

		$_SESSION['exp_EndDate'] = '';

		$_SESSION['exp_Duties'] = '';

		

		$exp_row_id = (int)$exp_ID;

		if(!is_numeric($exp_row_id)){

			return false;

		}

		

		$q = "SELECT employer, positionheld, responsibilities, datefrom, dateto FROM tblapplicantsworkexperience WHERE workingexpid = $exp_ID";

	

		$query = mysql_query($q);

		$foundRecord = mysql_num_rows($query);

		

		if($foundRecord == 1){

			$row = mysql_fetch_assoc($query);

			$_SESSION['exp_Employer'] = $row['employer'];

			$_SESSION['exp_Position'] = $row['positionheld'];

			$_SESSION['exp_StartDate'] = $row['datefrom'];

			$_SESSION['exp_EndDate'] = $row['dateto'];

			$_SESSION['exp_Duties'] = $row['responsibilities'];

		}

		

		return ($foundRecord == 1);

	}

	

	

	function get_ranks($sess_rank){

		$returnVal = '';



		$q = "SELECT rankid, name FROM tblrank ORDER BY name";

		$query = mysql_query($q);		

		$foundRecord = mysql_num_rows($query);



		if ($foundRecord >= 1) {

			$returnVal .= "<option value = '-1'>-- Please Select --</option>";

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

				if($row['rankid'] == $sess_rank){

					$returnVal .= "<option value='" . $row['rankid'] . "' selected='selected'>" . $row['name'] . "</option>";

				}else{

					$returnVal .= "<option value='" . $row['rankid'] . "'>" . $row['name'] . "</option>";

				}

			}

		}else{

			$returnVal = "<option value = '-1'>No Data in Ranks Table</option>";

		}//end if ($foundRecord == 1)



		return $returnVal;

	}

	



	function get_departments($sess_dept){

		$returnVal = '';



		$q = "SELECT departmentid, name FROM tbldepartment ORDER BY name";

		$query = mysql_query($q);

		$foundRecord = mysql_num_rows($query);



		if ($foundRecord >= 1) {

			$returnVal .= "<option value = '-1'>-- Please Select --</option>";

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

				if($row['departmentid'] == $sess_dept){

					$returnVal .= "<option value='" . $row['departmentid'] . "' selected='selected'>" . $row['name'] . "</option>";

				}else{

					$returnVal .= "<option value='" . $row['departmentid'] . "'>" . $row['name'] . "</option>";

				}

			}

		}else{

			$returnVal = "<option value = '-1'>No Data in Departments Table</option>";

		}//end if ($foundRecord == 1)



		return $returnVal;

	}

	

	

	function get_units($sess_unit){

		$returnVal = '';



		$q = "SELECT unitid, unitname FROM tblunits ORDER BY unitname";

		$query = mysql_query($q);

		$foundRecord = mysql_num_rows($query);



		if ($foundRecord >= 1) {

			$returnVal .= "<option value = '-1'>-- Please Select --</option>";

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

				if($row['unitid'] == $sess_unit){

					$returnVal .= "<option value='" . $row['unitid'] . "' selected='selected'>" . $row['unitname'] . "</option>";

				}else{

					$returnVal .= "<option value='" . $row['unitid'] . "'>" . $row['unitname'] . "</option>";

				}

			}

		}else{

			$returnVal = "<option value = '-1'>No Data in Units Table</option>";

		}//end if ($foundRecord == 1)



		return $returnVal;

	}

	

	

	function get_titles_into_combo($sess_title){

		$returnVal = '';



		$q = "SELECT salutationid, salutation FROM tblsalutationslookup ORDER BY salutation";

		$query = mysql_query($q);		

		$foundRecord = mysql_num_rows($query);



		if ($foundRecord >= 1) {

			$returnVal .= "<option value = '-1'>-- Please Select --</option>";

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

				if($row['salutationid'] == $sess_title){

					$returnVal .= "<option value='" . $row['salutationid'] . "' selected='selected'>" . $row['salutation'] . "</option>";

				}else{

					$returnVal .= "<option value='" . $row['salutationid'] . "'>" . $row['salutation'] . "</option>";

				}

			}

			$returnVal .= "<option value = '0'>-- Others --</option>";

		}else{

			$returnVal = "<option value = '-1'>No Data in Salutations Table</option>";

		}//end if ($foundRecord == 1)



		return $returnVal;

	}

	

	

	function get_countries_into_combo($sess_country){

		$returnVal = '';



		$q = "SELECT countryid, countryname FROM tblcountries ORDER BY countryname";

		$query = mysql_query($q);		

		$foundRecord = mysql_num_rows($query);



		if ($foundRecord >= 1) {

			$returnVal .= "<option value = '-1'>-- Please Select --</option>";

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

				if($row['countryid'] == $sess_country){

					$returnVal .= "<option value='" . $row['countryid'] . "' selected='selected'>" . $row['countryname'] . "</option>";

				}else if (strtoupper($row['countryname']) == 'NIGERIA'){

					$returnVal .= "<option value='" . $row['countryid'] . "' selected='selected'>" . $row['countryname'] . "</option>";

				}else{

					$returnVal .= "<option value='" . $row['countryid'] . "'>" . $row['countryname'] . "</option>";

				}

			}

//			$returnVal .= "<option value = '0'>-- Others --</option>";

		}else{

			$returnVal = "<option value = '-1'>No Data in Countries Table</option>";

		}//end if ($foundRecord == 1)



		return $returnVal;

	}

	

	

	function get_states_into_combo($sess_state){

		$returnVal = '';



		$q = "SELECT stateid, statename FROM tblstate ORDER BY statename";

		$query = mysql_query($q);

		$foundRecord = mysql_num_rows($query);



		if ($foundRecord >= 1) {

			$returnVal .= "<option value = '-1'>-- Please Select --</option>";

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

				if($row['stateid'] == $sess_state){

					$returnVal .= "<option value='" . $row['stateid'] . "' selected='selected'>" . $row['statename'] . "</option>";

				}else{

					$returnVal .= "<option value='" . $row['stateid'] . "'>" . $row['statename'] . "</option>";

				}

			}

			$returnVal .= "<option value = '0'>Others [Non-Nigerian]</option>";

		}else{

			$returnVal = "<option value = '-1'>No Data in States Table</option>";

		}//end if ($foundRecord == 1)



		return $returnVal;

	}

	

	function get_lgas_into_combo($sess_lga, $state_id){

		$returnVal = '';



		$q = "SELECT lgaid, stateid, lganame FROM tbllga ";

		

		if(isset($state_id) && $state_id > 0){

			$q .= " WHERE stateid = $state_id ";

		}

		$q .= "ORDER BY lganame";

		

		$query = mysql_query($q);

		$foundRecord = mysql_num_rows($query);



		if ($foundRecord >= 1) {

			$returnVal .= "<option value = '-1'>-- Please Select --</option>";

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

				if($row['lgaid'] == $sess_lga){

					$returnVal .= "<option value='" . $row['lgaid'] . "' selected='selected'>" . $row['lganame'] . "</option>";

				}else{

					$returnVal .= "<option value='" . $row['lgaid'] . "'>" . $row['lganame'] . "</option>";

				}

			}

			$returnVal .= "<option value = '0'>Others [Non-Nigerian]</option>";

		}else{

			$returnVal = "<option value = '-1'>No Data in LGAs Table</option>";

		}//end if ($foundRecord == 1)



		return $returnVal;

	}

	

	

	function get_title_from_id($title_id){

		$returnVal = '';



		$q = "SELECT salutation FROM tblsalutationslookup WHERE salutationid = $title_id";

		$query = mysql_query($q);

		$foundRecord = mysql_num_rows($query);



		if ($foundRecord >= 1) {

			$row = mysql_fetch_assoc($query);

			$returnVal = $row['salutation'];

		}



		return $returnVal;

	}

	

	

	function get_unit_name_from_id($unit_id){

		$returnVal = '';



		$q = "SELECT unitname FROM tblunits WHERE unitid = $unit_id";

		$query = mysql_query($q);

		$foundRecord = mysql_num_rows($query);



		if ($foundRecord >= 1) {

			$row = mysql_fetch_assoc($query);

			$returnVal = $row['unitname'];

		}



		return $returnVal;

	}

	

	function get_department_name_from_id($dept_id){

		$returnVal = '';



		$q = "SELECT name FROM tbldepartment WHERE departmentid = $dept_id";

		$query = mysql_query($q);

		$foundRecord = mysql_num_rows($query);



		if ($foundRecord >= 1) {

			$row = mysql_fetch_assoc($query);

			$returnVal = $row['name'];

		}



		return $returnVal;

	}

	

	

	function get_position_name_from_id($pos_id){

		$returnVal = '';



		$q = "SELECT name FROM tblrank WHERE rankid = $pos_id";

		$query = mysql_query($q);		

		$foundRecord = mysql_num_rows($query);



		if ($foundRecord >= 1) {

			$row = mysql_fetch_assoc($query);

			$returnVal = $row['name'];

		}



		return $returnVal;

	}

	



	function get_vacancyreason_from_id($reason_id){

		$returnVal = '';



		$q = "SELECT vacancyreason FROM tblvacancyreasons WHERE rowid = $reason_id";

		$query = mysql_query($q);		

		$foundRecord = mysql_num_rows($query);



		if ($foundRecord > 0) {

			$row = mysql_fetch_assoc($query);

			$returnVal = $row['vacancyreason'];

		}



		return $returnVal;

	}

	

	function get_schedule_parent_id($schedule_id){

		$returnVal = '';



		$q = "SELECT panelrowid FROM tblinterviewschedules WHERE schedulerowid = $schedule_id";

		

		$query = mysql_query($q);		

		$foundRecord = mysql_num_rows($query);



		if ($foundRecord > 0) {

			$row = mysql_fetch_assoc($query);

			$returnVal = $row['panelrowid'];

		}



		return $returnVal;

	}

	

	

	function load_unadvertised_jobs_into_combo($sess_vacancyid){

		$returnVal = '';



		$q = "SELECT tblvacancies.vacancyid, vacantslots, effectivedate, tblrank.name as position_name, tbldepartment.name as department_name FROM tblvacancies LEFT JOIN tblrank ON tblvacancies.rankid = tblrank.rankid LEFT JOIN tbldepartment ON tblvacancies.departmentid = tbldepartment.departmentid WHERE isadvertised = 'no' ORDER BY tblrank.name DESC";



		$query = mysql_query($q);

		$foundRecord = mysql_num_rows($query);



		if ($foundRecord > 0) {

			$returnVal .= "<option value = '-1'>-- Please Select --</option>";

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

				if($row['vacancyid'] == $sess_vacancyid){

					$returnVal .= "<option value='" . $row['vacancyid'] . "' selected='selected'>" . $row['position_name'] . "&nbsp;&nbsp;&nbsp;(" . $row['department_name']. ")</option>";

				}else{

					$returnVal .= "<option value='" . $row['vacancyid'] . "'>" . $row['position_name'] . "&nbsp;&nbsp;&nbsp;(" . $row['department_name']. ")</option>";

				}

			}

		}else{

			$returnVal = "<option value = '-1'>No Registered Job Posting Found</option>";

		}//end if ($foundRecord == 1)



		return $returnVal;

	}

	

	function commit_advert_response($advert_ID, $applicant_ID){

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

	//	$transcode = gettranscode('JB'); //from fee manager functions

		

		$q = "INSERT INTO tbladvertresponses (advertid, applicantid, responsedate, responsetime) ";

		$q .= "VALUES ($advert_ID, $applicant_ID, '$dt', curtime())";



		$query = mysql_query($q);

		return mysql_insert_id();

		

		/*

		$session = substr($dt,0,4);

		$amount = 2000.00;//should get this from a table: tblfeeitems or tblformfees?

		

		$q = "INSERT INTO tbl_feemngr_transaction(`transcode`, `studreg`,`code`,`transamount`,`session`, `paymentstatus`,`dategen`) ";

		$q .= "VALUES('$transcode','$applicant_ID','JOB',$amount,'$session','Not Paid', now())";

		$query = mysql_query($q);

		*/

	}

	

	

	function count_adverts_responded_to($applicant_id){

		$q = "SELECT COUNT(responseid) AS tot_responses FROM tbladvertresponses WHERE applicantid = $applicant_id";

		$query = mysql_query($q);

		$foundRecord = mysql_num_rows($query);

		

		$c = 0;

		if($foundRecord == 1){

			$row = mysql_fetch_assoc($query);

			$c = $row['tot_responses'];

		}

		

		return $c;

	}

	

	

	function applicant_responded_to_this_advert($advert_id, $applicant_id){

		$q = "SELECT responseid FROM tbladvertresponses WHERE advertid = $advert_id AND applicantid = $applicant_id";

		$query = mysql_query($q);

		$foundRecord = mysql_num_rows($query);

		

		if($foundRecord == 1){

			$row = mysql_fetch_assoc($query);

			$_SESSION['advt_response_id'] = $row['responseid'];

		}

		

		return ($foundRecord > 0);

	}



	function get_advert_response_id_from_advert_id($advert_id, $applicant_id){

		$q = "SELECT responseid FROM tbladvertresponses WHERE advertid = $advert_id AND applicantid = $applicant_id";



		$query = mysql_query($q);

		$foundRecord = mysql_num_rows($query);

		

		$r = 0;

		if($foundRecord == 1){

			$row = mysql_fetch_assoc($query);

			$r = $row['responseid'];

		}

		

		return $r;

	}

	

	function applicant_submitted_this_application($advert_id, $applicant_id){

		$response_id = get_advert_response_id_from_advert_id($advert_id, $applicant_id);

		

		$q = "SELECT rowid FROM tblapplicantscvs WHERE advertresponseid = $response_id";

		

		$query = mysql_query($q);

		$foundRecord = mysql_num_rows($query);

		

		$returnVal = 0;

		if($foundRecord == 1){

			$row = mysql_fetch_assoc($query);

			$returnVal = $row['rowid'];

		}

		

		return $returnVal;

	}

	

	function get_applicant_row_id_from_cv_id($app_cv_id){

		$q = "SELECT cv.advertresponseid, resp.applicantid FROM tblapplicantscvs AS cv INNER JOIN tbladvertresponses AS resp ";

		$q .= "ON cv.advertresponseid = resp.responseid WHERE cv.rowid = $app_cv_id";



		$query = mysql_query($q);

		$foundRecord = mysql_num_rows($query);

		

		$r = 0;

		if($foundRecord == 1){

			$row = mysql_fetch_assoc($query);

			$r = $row['applicantid'];

		}

		

		return $r;

	}

	



	function get_applicant_fullnames($applicant_row_id){

		$q = "SELECT d.titleid, d.surname, d.firstname, d.othernames, t.salutation ";

		$q .= "FROM tblapplicantsdetails AS d LEFT JOIN tblsalutationslookup AS t ";

		$q .= "ON d.titleid = t.salutationid WHERE d.appaccountrowid = $applicant_row_id";



		$query = mysql_query($q);

		$foundRecord = mysql_num_rows($query);

		

		$r = '';

		if($foundRecord == 1){

			$row = mysql_fetch_assoc($query);

			$r = trim($row['salutation'] . ' ' . strtoupper($row['surname']) . ', ' . $row['firstname'] . ' ' . $row['othernames']);

		}

		

		return $r;

	}

	

	

	function get_advert_id_from_cv_id($app_cv_id){

		$q = "SELECT cv.advertresponseid, resp.advertid FROM tblapplicantscvs AS cv INNER JOIN tbladvertresponses AS resp ";

		$q .= "ON cv.advertresponseid = resp.responseid WHERE cv.rowid = $app_cv_id";



		$query = mysql_query($q);

		$foundRecord = mysql_num_rows($query);

		

		$r = 0;

		if($foundRecord == 1){

			$row = mysql_fetch_assoc($query);

			$r = $row['advertid'];

		}

		

		return $r;

	}

	

	

	function get_job_reference_number($advert_ID){

		$appyear = substr(date('Y-d-m'),0,4);

		return ('PJA-ABU' . substr($appyear,2,2) . '-' . str_pad($advert_ID, 3, "0", STR_PAD_LEFT)); //eg: ABU/PJA12/005

//		PJA12#ABU#005

	}





	function embed_user_profile_pic($userRowID, $path_shift){

		/*	**************************************************************

		**	Function to try retrieve the URL of user's profile picture	**

		**	Returns the URL pre-embedded in the <img > tag:				**

		**	**************************************************************/

		$finalURL = get_current_photo($userRowID, $path_shift);

		if (user_photo_not_uploaded($userRowID)){

			$finalURL .= '<div style="float:center; text-align:center; width:100px; margin: 5px; margin-left:-3px; background-color:#eeeeee;  border: 1px solid #339933"><a href="photo_upload.php" title="click to upload your photo">upload photo</a></div>';

		}

		

		echo $finalURL;

	}//end function getUserProfilePicURL()

	

	

	function getPicNA($gender){

		if($gender == 'F'){

			$p = 'naF';

		}else{

			$p = 'naM';

		}

		return $p;

	}

	

	function user_photo_not_uploaded($userRowID){

		$returnVal = true;

		

		if ($userRowID > 0){

			$q = "SELECT profile_pic FROM tblapplicantsdetails WHERE appaccountrowid = " . $userRowID;

			

			$query = mysql_query($q);

			$foundRecord = mysql_num_rows($query);

			

			if ($foundRecord == 1) {

				$row = mysql_fetch_assoc($query);				

				if (isset($row['profile_pic']) && strlen($row['profile_pic']) == 16) {

					$returnVal = false;

				}

			}

		}

		

		return $returnVal;

	}

	

	

	function get_current_photo($userRowID, $path_shift){

		$finalURL = "";

		$returnVal = "";

		$altTitle = "Image Not Available";

		$naFile = 'naM';

		

		if ($userRowID > 0){			

			$q = "SELECT surname, firstname, othernames, profile_pic, gender FROM tblapplicantsdetails WHERE appaccountrowid = " . $userRowID;

	

			$query = mysql_query($q);

			$foundRecord = mysql_num_rows($query);



			if ($foundRecord == 1) {			

				$row = mysql_fetch_assoc($query);

	

				if (isset($row['profile_pic']) && strlen($row['profile_pic']) == 16) {

					$returnVal = $row['profile_pic'];

					$altTitle = $row['surname'] . ' ' . $row['firstname'] . ' ' . $row['othernames'];

				}else{

					$naFile = getPicNA($row['gender']);

				}//end if (isset($row['m_profile_pic']) && $row['m_profile_pic'] != "")

			}else {

				$returnVal = $naFile;

			}



			$returnVal = $path_shift . "profilepics/" . $returnVal . ".jpg";

		}else{

		

			$r = 'recruitment/profile/profilepics/naM.jpg';

			$returnVal = $path_shift . "profilepics/" . $returnVal . ".jpg";

		}

		

		// Whatever is to be returned, check that the image exists and is a file:

		if (file_exists ($returnVal) && (is_file($returnVal))) {

			$finalURL= "<img src='" . $returnVal . "' alt='" . $altTitle . "' title='" . $altTitle . "' width='95px' height='110px' style='border:solid 1px #000000'>";

		}else{

			$finalURL= "<img src='" . $path_shift . "profilepics/" . getPicNA($row['gender']) . ".jpg' alt='" . $altTitle . "' title='" . $altTitle . "' width='95px' height='110px'  style='border:solid 1px #000000'>";

		}//end if (file_exists ($returnVal) && (is_file($returnVal)))

		

		return $finalURL;

	}

	

	

	function generate_random_chars($num_chars) {

		$returnVal = '';

		if ((is_numeric($num_chars)) && ($num_chars > 0) && (! is_null($num_chars))) {

			

			$accepted_chars = 'abcdefghijklmnpqrstuvwxyz1234567890ABCDEFGHIJKLMNPQRSTUVWXYZ{_}(-)~^$';

			

			// Seed the generator if necessary.

			srand(((int)((double)microtime()*1000003)) );

			

			for ($i = 0; $i < $num_chars; $i++) {

				$random_number = rand(0, (strlen($accepted_chars) -1));

				$returnVal .= $accepted_chars[$random_number];

			}

		 }

		return $returnVal;

	}





	function get_qualification_types_into_combo($sess_qualID){

		$returnVal = '';



		$q = "SELECT id, certificate FROM tblqualificationslookup ORDER BY certificate";

		$query = mysql_query($q);

		$foundRecord = mysql_num_rows($query);



		if ($foundRecord >= 1) {

			$returnVal .= "<option value = '-1'>-- Please Select --</option>";

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

				if($row['id'] == $sess_qualID){

					$returnVal .= "<option value='" . $row['id'] . "' selected='selected'>" . $row['certificate'] . "</option>";

				}else{

					$returnVal .= "<option value='" . $row['id'] . "'>" . $row['certificate'] . "</option>";

				}

			}

			$returnVal .= "<option value = '0'>-- NONE --</option>";

		}else{

			$returnVal = "<option value = '-1'>No Data in Qualifications Table</option>";

		}//end if ($foundRecord == 1)



		return $returnVal;

	}

	

	

	function get_qualification_classes_into_combo($sess_qualclassID){

		$returnVal = '';



		$q = "SELECT qualificationclassid, qualification FROM tblqualificationclass ORDER BY qualification";

		$query = mysql_query($q);

		$foundRecord = mysql_num_rows($query);

		

		if ($foundRecord >= 1) {

			$returnVal .= "<option value = '-1'>-- Please Select --</option>";

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

				if($row['qualificationclassid'] == $sess_qualclassID){

					$returnVal .= "<option value='" . $row['qualificationclassid'] . "' selected='selected'>" . $row['qualification'] . "</option>";

				}else{

					$returnVal .= "<option value='" . $row['qualificationclassid'] . "'>" . $row['qualification'] . "</option>";

				}

			}

			$returnVal .= "<option value = '0'>-- Not Applicable --</option>";

		}else{

			$returnVal = "<option value = '-1'>No Data in Qualifications Table</option>";

		}//end if ($foundRecord == 1)



		return $returnVal;

	}

	

	

	function get_professional_qualifications_into_combo($sess_qualID){

		$returnVal = '';



		$q = "SELECT id, name FROM tblprofessionalqualslookup ORDER BY name";

		$query = mysql_query($q);

		$foundRecord = mysql_num_rows($query);

		if ($foundRecord >= 1) {

			$returnVal .= "<option value = '-1'>-- Please Select --</option>";

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

				if($row['id'] == $sess_qualID){

					$returnVal .= "<option value='" . $row['id'] . "' selected='selected'>" . $row['name'] . "</option>";

				}else{

					$returnVal .= "<option value='" . $row['id'] . "'>" . $row['name'] . "</option>";

				}

			}

			$returnVal .= "<option value = '0'>-- NONE --</option>";

		}else{

			$returnVal = "<option value = '-1'>No Data in Qualifications Table</option>";

		}//end if ($foundRecord == 1)



		return $returnVal;

	}

	

	

	function get_programmes_of_study_into_combo($sess_studyID){

		$returnVal = '';



		$q = "SELECT id, studyfield FROM tblstudyfieldlookup ORDER BY studyfield";

		$query = mysql_query($q);

		$foundRecord = mysql_num_rows($query);



		if ($foundRecord >= 1) {

			$returnVal .= "<option value = '-1'>-- Please Select --</option>";

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

				if($row['id'] == $sess_studyID){

					$returnVal .= "<option value='" . $row['id'] . "' selected='selected'>" . $row['studyfield'] . "</option>";

				}else{

					$returnVal .= "<option value='" . $row['id'] . "'>" . $row['studyfield'] . "</option>";

				}

			}

			$returnVal .= "<option value = '0'>-- Others --</option>";

		}else{

			$returnVal = "<option value = '-1'>No Data in Fields of Study Table</option>";

		}//end if ($foundRecord == 1)



		return $returnVal;

	}

	

	

	function get_applicant_advert_to_track($advert_ID, $applicant_id){

		$returnVal = '<table border="1px" style="border-collapse: collapse; width:100%; margin-top:10px" cellpadding="5px" cellspacing="10px">';

		

		if(!is_numeric($advert_ID) || ((int)$advert_ID < 1) || !is_numeric($applicant_id) || ((int)$applicant_id < 1)){

			$returnVal .= '<tr style="border: solid 1px;"><td colspan="2">Error while retrieving the requested information: <strong>"Invalid Advert ID"</strong></td></tr>';

		}else{		

			$q = "SELECT tbladvertresponses.responseid, tbladvertresponses.advertid, applicantid, responsedate, ";

			$q .= "datecompleted, applicantcomments, tbladvertisements.advertisementid, adverttype, tbladvertisements.vacancyid, ";

			$q .= "tblvacancies.vacancyid, tblvacancies.rankid, tblvacancies.departmentid, tblrank.name as position_name, tbldepartment.name as department_name ";

			$q .= "FROM tbladvertresponses LEFT JOIN tbladvertisements ON tbladvertresponses.advertid = tbladvertisements.advertisementid ";

			$q .= "LEFT JOIN tblvacancies ON tbladvertisements.vacancyid = tblvacancies.vacancyid ";

			$q .= "LEFT JOIN tblrank ON tblvacancies.rankid = tblrank.rankid ";

			$q .= "LEFT JOIN tbldepartment ON tblvacancies.departmentid = tbldepartment.departmentid ";

			$q .= "WHERE tbladvertresponses.advertid = $advert_ID AND tbladvertresponses.applicantid = $applicant_id";



			$query = mysql_query($q);

			$foundRecord = mysql_num_rows($query);

			

			if ($foundRecord > 0) {

				$row = mysql_fetch_assoc($query);

				$returnVal .= '<tr style="border: solid 1px;text-align:center"><td colspan="2"><strong>' . $row['position_name'] . '</strong></td></tr>';

				

				$returnVal .= '<tr style="border: solid 1px;"><td>Department</td><td>' . $row['department_name'] . ' </td></tr>';

				$returnVal .= '<tr style="border: solid 1px;"><td>Response Date</td><td>' . get_my_date_format($row['responsedate'], 'L') . ' </td></tr>';

				$returnVal .= '<tr style="border: solid 1px;"><td>Your Comments</td><td>' . $row['applicantcomments'] . '</td></tr>';

				$returnVal .= '<tr style="border: solid 1px;"><td>Application Status</td><td>' . $row['datecompleted'] . ' </td></tr>';

				$returnVal .= '<tr style="border: solid 1px;"><td>ABU&rsquo;s Action</td><td>Pending / Acknowledged / Shortlisted for Interview</td></tr>';

				$returnVal .= '<tr style="border: solid 1px; text-align:right"><td colspan="2"><a href="#" title="click to complete your application for this job">[complete application]</a>&nbsp;&nbsp;&nbsp;<a href="#" title="click to cancel your application for this job">[cancel application]</a>&nbsp;&nbsp;&nbsp;<a href="../adverts/running_adverts.php" title="click to go back to adverts page">[back to adverts]</a></td></tr>';

			}else {

				$returnVal .= '<tr style="border: solid 1px;"><td colspan="2">Error while retrieving the requested information: <strong>"Invalid User ID"</strong></td></tr>';

			}//end if ($foundRecord == 1)

		}//end if(!is_numeric($user_row_id) || ((int)$user_row_id < 1)

		

		$returnVal .= '</table></br>';

		

		echo $returnVal;

	}

	

	function get_applicant_other_job_apps($applicant_id){

		$returnVal = '<table border="1px" style="border-collapse: collapse; width:100%; margin-top:10px" cellpadding="5px" cellspacing="10px">';

		

		if(!is_numeric($applicant_id) || ((int)$applicant_id < 1)){

			$returnVal .= '<tr style="border: solid 1px;"><td colspan="2">Error while retrieving the requested information: <strong>"Invalid Advert ID"</strong></td></tr>';

		}else{		

			$q = "SELECT tbladvertresponses.responseid, tbladvertresponses.advertid, applicantid, responsedate, ";

			$q .= "datecompleted, applicantcomments, tbladvertisements.advertisementid, adverttype, tbladvertisements.vacancyid, ";

			$q .= "tblvacancies.vacancyid, tblvacancies.rankid, tblvacancies.departmentid, tblrank.name as position_name, tbldepartment.name as department_name ";

			$q .= "FROM tbladvertresponses LEFT JOIN tbladvertisements ON tbladvertresponses.advertid = tbladvertisements.advertisementid ";

			$q .= "LEFT JOIN tblvacancies ON tbladvertisements.vacancyid = tblvacancies.vacancyid ";

			$q .= "LEFT JOIN tblrank ON tblvacancies.rankid = tblrank.rankid ";

			$q .= "LEFT JOIN tbldepartment ON tblvacancies.departmentid = tbldepartment.departmentid ";

			$q .= "WHERE tbladvertresponses.applicantid = $applicant_id";



			$query = mysql_query($q);

			$foundRecord = mysql_num_rows($query);

			

			if ($foundRecord > 0) {

				$row = mysql_fetch_assoc($query);

				$returnVal .= '<tr style="border: solid 1px;text-align:center"><td colspan="2"><strong>' . $row['position_name'] . '</strong></td></tr>';

				

				$returnVal .= '<tr style="border: solid 1px;"><td>Department</td><td>' . $row['department_name'] . ' </td></tr>';

				$returnVal .= '<tr style="border: solid 1px;"><td>Response Date</td><td>' . get_my_date_format($row['responsedate'], 'L') . ' </td></tr>';

				$returnVal .= '<tr style="border: solid 1px;"><td>Your Comments</td><td>' . $row['applicantcomments'] . '</td></tr>';

				$returnVal .= '<tr style="border: solid 1px;"><td>Application Status</td><td>' . $row['datecompleted'] . ' </td></tr>';

				$returnVal .= '<tr style="border: solid 1px;"><td>ABU&rsquo;s Action</td><td>Pending / Acknowledged / Shortlisted for Interview</td></tr>';

				$returnVal .= '<tr style="border: solid 1px; text-align:right"><td colspan="2"><a href="#" title="click to complete your application for this job">[complete application]</a>&nbsp;&nbsp;&nbsp;<a href="#" title="click to cancel your application for this job">[cancel application]</a>&nbsp;&nbsp;&nbsp;<a href="../adverts/running_adverts.php" title="click to go back to adverts page">[back to adverts]</a></td></tr>';

			}else {

				$returnVal .= '<tr style="border: solid 1px;"><td colspan="2">Error while retrieving the requested information: <strong>"Invalid User ID"</strong></td></tr>';

			}//end if ($foundRecord == 1)

		}//end if(!is_numeric($user_row_id) || ((int)$user_row_id < 1)

		

		$returnVal .= '</table></br>';

		

		echo $returnVal;

	}

	

	

	

	function send_email($recepient_email, $msg_title, $msg_body, $msg_sender){

		/*

		$subject = "2011/2012 Ahmadu Bello University Zaria Postgraduate Application";

		// Build the message

		$message = "Dear $surname,\n\n";

		$message .="Thank you very much for indicating interest in our post graduate programs. Please take note of your login paramerets: \n\n";

		$message .= "Username: $email\n\n";

		$message .= "Password: $pwd \n\n";

		$message .= "Please use these to login at any time. \n\n However, for you to continue your application, you must go to any designated bank and pay the prescribed fees for the form. \n\n";

		$message .= "Thank you. \n\n";

		*/

		

		$msg_body .= $msg_body ."\n\n" . $msg_sender;

		$msg_body = wordwrap($msg_body, 70);	// limit line length to 70 characters

			//mail(to,subject,message,headers,parameters) 

		$msg_sender = "From: " . $msg_sender;

		return mail($recepient_email, $msg_title, $msg_body, $msg_sender);

	}



	function get_applicant_prof_qual($applicantid){

			$prof_qualification="";

			$query=mysql_query("SELECT tblprofessionalqualslookup.name AS profqual,tblapplicantsprofqualifications.yearobtained

			FROM tblapplicantsprofqualifications 

			INNER JOIN tblprofessionalqualslookup ON tblapplicantsprofqualifications.qualificationid = tblprofessionalqualslookup.id

			WHERE tblapplicantsprofqualifications.appaccountrowid=$applicantid") 

			or die("query cannot be executed: ".mysql_error());

			if(mysql_num_rows($query)>0){

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

				$profqualification=$row['profqual'];

				$yearobtained=$row['yearobtained'];

				$prof_qualification.=$profqualification." (".$yearobtained.")"."<br/>";

				}

			}

			return $prof_qualification;

	}

	function is_applicant_shortlisted($applicantid){

		$query=mysql_query("SELECT * FROM tbladvertresponses WHERE applicantid=$applicantid AND applicationstatus='shortlisted'")

		or die("Query cannot be executed! ".mysql_error());

		if(mysql_num_rows($query)>0){

		return 1;

		}else{

		return 0;

		}

	}

	

	

function get_max_score_obtainable($scoreitemid,$scheduleid){

	$scorequery=mysql_query("SELECT tblinterviewpanelscoresettings.scoreobtainable FROM tblinterviewpanelscoresettings WHERE tblinterviewpanelscoresettings.scoreitemid=$scoreitemid AND scheduleid = $scheduleid") or die("Query cannot be executed: " . mysql_error());

	if(mysql_num_rows($scorequery) > 0){

		$row=mysql_fetch_assoc($scorequery);

		return $row['scoreobtainable'];

	}else{

		return 0;

	}

}



	function interview_score_exists($applicant_id,$schedule_id,$scoreitem_id,$member_id){	

		$q = "SELECT rowid FROM tblinterviewresults WHERE appaccountrowid = $applicant_id AND scheduleid = $schedule_id ";

		$q .= "AND scoreitemid = $scoreitem_id AND scorerid = $member_id";

		

		$query = mysql_query($q);

		$foundRecord = mysql_num_rows($query);

		

		return ($foundRecord > 0);

	}

	// Validation of the form of Appointment

	function validate_appointment_form($appointment_type,$contract_effective_date,$contract_end_date,$temporary_part_time_session,$grade_level,$step,$salary_category,$staff_category){

		$errormsg='';

		//,$contract_effective_date,$contract_end_date,

		if(!isset($appointment_type) || $appointment_type==0){

			$errormsg.="<li>Please specify the type of appointment</li>";

		}

		if(isset($appointment_type) && $appointment_type!=0){

			if(get_appointment_type_from_id($appointment_type)=="Contract"){

				if(!isset($contract_effective_date) || $contract_effective_date==""){

					$errormsg.="<li>Please, specify the effective date of contract!</li>";

				}

				if(!isset($contract_end_date) || $contract_end_date==""){

					$errormsg.="<li>Please, specify the end date of  contract!</li>";

				}

			}

			if(get_appointment_type_from_id($appointment_type)=="Temporary Part-Time"){

				if(!isset($temporary_part_time_session) || $temporary_part_time_session==""){

					$errormsg.="<li>Please, specify the session for temporary part-time appointment!</li>";

				}

			}

			if(get_appointment_type_from_id($appointment_type)=="Tenure" || get_appointment_type_from_id($appointment_type)=="Visiting" || 

			   get_appointment_type_from_id($appointment_type)=="Sabbatical" || get_appointment_type_from_id($appointment_type)=="Junior Staff" ||

			   get_appointment_type_from_id($appointment_type)=="Temporary Month-to-Month"){

			   

				if(!isset($salary_category) || $salary_category==0){

					$errormsg.="<li>Please, specify the salary scale!</li>";

				}

				if((get_salary_category_from_id($salary_category)=="CONTISS" && $staff_category=="Academic")){

					$errormsg.="<li>This salary scale (CONTISS) is applicable to only Non-Academic staff!</li>";

				}

				if((get_salary_category_from_id($salary_category)=="CONUASS" && $staff_category=="Non-Academic")){

					$errormsg.="<li>This salary scale (CONUASS) is applicable to only Academic staff!</li>";

				}

				if(!isset($grade_level) || $grade_level=="0"){

					$errormsg.="<li>Please, specify the grade level!</li>";

				}

				if(!isset($step) || $step=="0"){

					$errormsg.="<li>Please, specify the step!</li>";

				}

			}

		}

		return $errormsg;

	}

	

	// function to determine whether the appointment is for Academic or Non-Academic

	function is_staff_academic($applicantid,$advertid){

		$advertresponseid=get_advertresponse_id($applicantid,$advertid);

		$query=mysql_query("select staffcategory from tblapplicantsappointments where advertresponseid=$advertresponseid");

		if(mysql_num_rows($query)==1){

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

			$staffcategory=$row['staffcategory'];

			$status=($staffcategory=="Academic")?true:false;

			}

		}

		return $status;

	}

	

	//function to determine whether the staff's cadre is Graduate Assistance to show the changes in appoinment letter

	function is_staff_graduate_assitance($applicantid,$advertid){

		$advertresponseid=get_advertresponse_id($applicantid,$advertid);

		$status=false;

		$query=mysql_query("select designation from tblapplicantsappointments where advertresponseid=$advertresponseid");

		if(mysql_num_rows($query)==1){

			$designationid=mysql_result($query,0,'designation');

			$rankquery=mysql_query("select name from tblrank where rankid=$designationid");

			if(mysql_num_rows($rankquery)){

				$designation=mysql_result($rankquery,0,'name');

				if($designation=="Graduate Assistant"){

				$status=true;

				}

			}

		}

		return $status;

	}

	//this is a function determine whether a rank is academic or non-academic

	function is_rank_academic($advertid){

		$status=false;

		$staff_category='';

				$query=mysql_query("SELECT tblstaffcategory.name AS category FROM tblstaffcategory 

									INNER JOIN tblcadre on tblstaffcategory.categoryid=tblcadre.categoryid

									INNER JOIN tblrank  on tblrank.cadreid=tblcadre.cadreid where tblrank.rankid=

									(SELECT tblrank.rankid FROM tbladvertisements

									INNER JOIN tblvacancies ON tblvacancies.vacancyid = tbladvertisements.vacancyid

									INNER JOIN tblrank ON tblrank.rankid = tblvacancies.rankid

									INNER JOIN tbladvertresponses ON tbladvertresponses.advertid=tbladvertisements.advertisementid 

									WHERE tbladvertisements.advertisementid=$advertid LIMIT 1)") 

									or die("Query cannot be executed: ".mysql_error());

				if(mysql_num_rows($query)==1){

					$staff_category=mysql_result($query,0,'category');

				}

				switch($staff_category){

					case "academic" : case "research":  

					$status=true;

					break;

					case "non academic":

					$status=false;

					break;

					default :

					$status=false;

					break;

				}

		return $status;

	}

	// function return the initial grade level for a particular rank

	function initial_gradelevel($advertid){

		$initial_grade=0;

		$query=mysql_query("SELECT tblrank.grade FROM tbladvertisements

							INNER JOIN tblvacancies ON tblvacancies.vacancyid = tbladvertisements.vacancyid

							INNER JOIN tblrank ON tblrank.rankid = tblvacancies.rankid

							INNER JOIN tbladvertresponses ON tbladvertresponses.advertid=tbladvertisements.advertisementid 

							WHERE tbladvertisements.advertisementid=$advertid LIMIT 1");

		if(mysql_num_rows($query)==1){

			$initial_grade=mysql_result($query,0,'grade');

		}

	return $initial_grade;

	}

	// get maximum step( for salary) based on the rank and grade

	function get_maximum_step($advertid){

		$maxstep=0;

		$gradelevel=initial_gradelevel($advertid);

			$query=mysql_query("select maxstep from  tblsalary where gradelevel=$gradelevel") or die("Query cannot be executed: ".mysql_error());

		if(mysql_num_rows($query)==1){

			$maxstep=mysql_result($query,0,'maxstep');

		}

	 return $maxstep;

	}

	// To show in the Appointment letter as an applicant's address

	function get_applicant_contactaddress($applicantid) {

		$returnVal ="";

		$address='';



		$query= mysql_query("SELECT currentaddress, permanenthomeaddress FROM tblapplicantscontacts WHERE appaccountrowid = $applicantid")or die("query cannot be execued: ".mysql_error());

		$foundRecord = mysql_num_rows($query);

		if ($foundRecord == 1) {

			$row = mysql_fetch_assoc($query);

			$address = explode(',',$row["currentaddress"]);

		}

		foreach($address as $splite_address){

		$returnVal.="<span>".$splite_address."</span>"."<br/>";

		}

		

		

		return $returnVal;

	}



	

	/*this is a function that returns the difference between two date in months

	and use it to determine when salary increment will be due

	*/

	function monthsBetween($startDate, $endDate) {

		$retval = "";

		// Assume YYYY-mm-dd - as is common MYSQL format

		$splitStart = explode('-', $startDate);

		$splitEnd = explode('-', $endDate);



		if (is_array($splitStart) && is_array($splitEnd)) {

			$difYears = $splitEnd[0] - $splitStart[0];

			$difMonths = $splitEnd[1] - $splitStart[1];

			$difDays = $splitEnd[2] - $splitStart[2];



			$retval = ($difDays > 0) ? $difMonths : $difMonths - 1;

			$retval += $difYears * 12;

		}

			return $retval;

	}

	// determine date of next salary increment after an appointment

	function get_next_salary_increment_date(){

		$strdate=date("Y")."-".date("m")."-".date("d");

		$enddate=date("Y")."-10-01";

		if(monthsBetween($strdate,$enddate)>=6){ // you must work for atleast 6 months before October

				echo date("Y");

		}else{

				echo date("Y")+1;

			}

	}

	

	function get_appointment_type_from_id($appointment_typeid){

			$appointment_type='';

			$query=mysql_query("select appointmenttype from tblapplicantappointmenttype where appointmenttypeid=$appointment_typeid")

						or die("Query cannot be executed: ".mysql_error());

		if(mysql_num_rows($query)>0){

			$row=mysql_fetch_assoc($query);

			$appointment_type=trim($row['appointmenttype']);

		}else{

			echo "<font>No such type of appointment exist!</font>";

		}

		return $appointment_type;

	}

	

	// To determine which template to show when Appointing applicant(Tenure, Temporary, Visiting, etc)

	function get_appointment_template($appointment_typeid){

		$Url_redirect="";

		$appointment_type=get_appointment_type_from_id($appointment_typeid);

		

			switch($appointment_type){

				case "Tenure":

					$Url_redirect="tenure_appointment_letter.php";

					break;

				case "Temporary Part-Time":

					$Url_redirect="temporary_part-time_appoint_letter.php";

					break;

				case "Temporary Month-to-Month":

					$Url_redirect="month-month_appoint_letter.php";

					break;

				case "Visiting":

					$Url_redirect="visiting_appointment_letter.php";

					break;

				case "Contract":

					$Url_redirect="contract_appointment_letter.php";

					break;

				case "Sabbatical":

					$Url_redirect="sabbatical_appoint_letter.php";

					break;

				case "Junior Staff":

					$Url_redirect="junior_staff_appointment_letter.php";

					break;



			}

		return $Url_redirect;

	}

	//function to determines you as either Sir or Madam to be used in the appointment letter

	function get_salutation($applicant_id){

		$salution='';

		$gender='';

		$query=mysql_query("select gender from tblapplicantscvs inner join tbladvertresponses on 

		tbladvertresponses.responseid=tblapplicantscvs.advertresponseid where tbladvertresponses.applicantid=$applicant_id");

		if(mysql_num_rows($query)==1){

			$row=mysql_fetch_assoc($query);

			$gender=$row['gender'];

		}

		switch($gender){

			case 'M':

			$salution='Sir';

			break;

			

			case 'F':

			$salution='Madam';

			break;

		}

		return $salution;

	}

	//function to format the date from the date(example 1st July, 2012) from the format dd/mm/yyyy 

	function format_day_from_date($date){

		$splitedate=explode('/',$date);

		$day=$splitedate[0];

		$month=$splitedate[1];

		$year=$splitedate[2];

		$format='';

		switch($day){

			case 1: case 21: case 31:

			$format='<sup>st</sup>';

			break;

			case 2: case 22:

			$format='<sup>nd</sup>';

			break;

			case 3: case 23:

			$format='<sup>rd</sup>';

			break;

			default:

			$format='<sup>th</sup>';

		}

		return ($day.$format.' '.trim(get_month_name($month,'???')).','.' '.$year);

	}

	// function to get salary amount for any Grade Level, Step, and Salary Category;

	function get_salary($grade_level,$step,$salaryid){

	$amount=0.0;

		$query=mysql_query("SELECT amount from tblsalaryplacement WHERE (grade=$grade_level AND step=$step AND salaryid=$salaryid)")or 

			die("Query cannot be executed: ".mysql_error());

		if(mysql_num_rows($query)>0){

			$amount=mysql_result($query,0,'amount');

		}

		return $amount;

	}

	// function to show salary amount fro a specific applicant

	function get_applicant_salary($applicantid, $advertid){

		$advertresponseid=get_advertresponse_id($applicantid,$advertid);

		$gradelevel='';

		$salaryid=0;

		$amount=0.0;

		$query=mysql_query("select gradelevel, salaryid from tblapplicantsappointments where advertresponseid=$advertresponseid");

		if(mysql_num_rows($query)>0){

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

			$gradelevel=$row['gradelevel'];

			$salaryid=$row['salaryid'];

			}

			$splited_grade_step=explode('/',$gradelevel);

			$grade=$splited_grade_step[0];

			$step=$splited_grade_step[1];

		}

		$amount=get_salary($grade,$step,$salaryid);

		return number_format($amount,2,":",",");

	}

	//function to get the maximum step for a particular salary category and grade

	function get_max_step($grade,$salaryid){

		$maxstep=0;

		$query=mysql_query("select max(step) as maxstep from tblsalaryplacement where grade=$grade and salaryid=$salaryid");

		if(mysql_num_rows($query)==1){

		$maxstep=mysql_result($query,0,'maxstep');

		}

		return $maxstep;

	}

	

	// function get range of salary to be displayed in appointment letter

	function get_range_of_salary($applicantid, $advertid){

		$advertresponseid=get_advertresponse_id($applicantid,$advertid);

		$maxstep=0;

		$query=mysql_query("select gradelevel,salaryid from tblapplicantsappointments where advertresponseid=$advertresponseid");

		if(mysql_num_rows($query)>0){

			$row=mysql_fetch_assoc($query);

			$grade_step=$row['gradelevel'];

			$salaryid=$row['salaryid'];

			$splited_grade_step=explode('/',$grade_step);

			$grade=$splited_grade_step[0];

			$maxstep=get_max_step($grade,$salaryid);

		}

		$amount="N".number_format(get_salary($grade,1,$salaryid),2,":",",")." - "."N".number_format(get_salary($grade,$maxstep,$salaryid),2,":",",");

		return $amount;

	}

	

	// function to get Salary Category CONTISS CONNUAS

	function get_salary_category_from_id($salary_categoryid){

		$salary_category="";

		if($salary_categoryid>1 || $salary_categoryid !=""){

			$query=mysql_query("select category from tblsalarytable where salaryid=$salary_categoryid");

			if(mysql_num_rows($query)==1){

				$salary_category=mysql_result($query,0,'category');

			}

		}

			return $salary_category;

			

	}

	// function to get applicant Salary Category weather CONTISS CONNUAS

	function get_applicant_salary_category($applicantid, $advertid){

		$advertreponseid=get_advertresponse_id($applicantid,$advertid);

		$salaryid=0;

		$query=mysql_query("select salaryid from tblapplicantsappointments where advertresponseid=$advertreponseid");

		if(mysql_num_rows($query)>0){

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

				$salaryid=$row['salaryid'];

			}

		}

		return get_salary_category_from_id($salaryid);

	}

	// grade like 07/02, 08/03 etc

	function get_applicant_grade_level($applicantid, $advertid){

		$advertreponseid=get_advertresponse_id($applicantid,$advertid);

		$gradelevel='';

		$query=mysql_query("select gradelevel from tblapplicantsappointments where advertresponseid=$advertreponseid");

		if(mysql_num_rows($query)>0){

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

				$gradelevel=$row['gradelevel'];

			}

		}

		return $gradelevel;

	}

	// function to return the effective and end date of contract appointment

	function get_contract_date($applicantid, $advertid){

		$advertreponseid=get_advertresponse_id($applicantid,$advertid);

		$gradelevel='';

		$query=mysql_query("select contracteffectivedate,contractenddate from tblapplicantsappointments where advertresponseid=$advertreponseid");

		if(mysql_num_rows($query)>0){

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

				$contracteffectivedate=get_date_ddmmyyyy($row['contracteffectivedate']);

				$contractenddate=get_date_ddmmyyyy($row['contractenddate']);

			}

		}

		return format_day_from_date($contracteffectivedate)." to ".format_day_from_date($contractenddate);

	}

	//function to return session for Temporary Part-Time Appointement

	function get_temporary_part_time_session($applicantid, $advertid){

		$advertreponseid=get_advertresponse_id($applicantid,$advertid);

		$temporarysession='';

		$query=mysql_query("select temporarysession from tblapplicantsappointments where advertresponseid=$advertreponseid");

		if(mysql_num_rows($query)>0){

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

				$temporarysession=$row['temporarysession'];

			}

		}

		return $temporarysession;

	}

	//function to return hourly rate for temporary part-time appointment

	function get_hourly_rate($applicantid,$advertid){

		$amount=0;

		$advertreponseid=get_advertresponse_id($applicantid,$advertid);

		$query=mysql_query("select hourlyrate from tblapplicanthourlyrate where rankid=(select designation from tblapplicantsappointments where  advertresponseid=$advertreponseid)")or  

		die("Query cannot be executed: ".mysql_error());

		if(mysql_num_rows($query)<1){

		$amount=number_format(0,2,'.',',');

		}else{

			$amount=number_format(mysql_result($query,0,'hourlyrate'),2,'.',',');

		}

		

		return $amount;

	}

	

	// function that will retrieve the posted applicant's records from tblapplicantappointments to be edited

	function get_applicant_appointment_settings($applicantid, $advertid){

		$advertreponseid=get_advertresponse_id($applicantid,$advertid);

		$query=mysql_query("select appointmenttype,contracteffectivedate,contractenddate,temporarysession,departmentid,

							gradelevel,salaryid from tblapplicantsappointments where advertresponseid=$advertreponseid");

			if(mysql_num_rows($query)>0){

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

				$appointmenttype=$row['appointmenttype'];

				$contracteffectivedate=$row['contracteffectivedate'];

				$contractenddate=$row['contractenddate'];

				$gradelevel=$row['gradelevel'];

				$salaryid=$row['salaryid'];

				$temporarysession=$row['temporarysession'];

			}

		}

		if($appointmenttype!=0){

			$sql=mysql_query("SELECT * FROM tblapplicantappointmenttype") or die("Query cannot be executed! ".mysql_error());

			echo "<div>";

			echo "<div>Appintment Type:</div>";

			echo "<div><select name='appointtype'>";

			echo "<option value='0'>--Please select--</option>";

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

				$appint_type=$row['appointmenttype'];

				$appointmenttype_id=$row['appointmenttypeid'];

				if($appointmenttype==$appointmenttype_id){

				echo "<option value='$appointmenttype_id' selected>$appint_type</option>";

				}else{

				echo "<option value='$appointmenttype_id'>$appint_type</option>";

				}

			}

			echo "</select></div>";

			echo "</div>";

		}

		if(get_appointment_type_from_id($appointmenttype)=="Contract"){

			$contract_from=get_date_ddmmyyyy($contracteffectivedate);

			$contract_to=get_date_ddmmyyyy($contractenddate);

			echo "<div class='span2'>";

			echo "<div>Effective Date:</div>";

			echo "<div><input type='text' name='contract_from' value='$contract_from'/></div>";

			echo "</div>";

			echo "<div class='span2'>";

			echo "<div>End Date:</div>";

			echo "<div><input type='text' name='contract_to' value='$contract_to'/></div>";

			echo "</div>";

		}

		if(get_appointment_type_from_id($appointmenttype)=="Tenure" || get_appointment_type_from_id($appointmenttype)=="Junior Staff" ||  get_appointment_type_from_id($appointmenttype)=="Temporary Month-to-Month"

			|| get_appointment_type_from_id($appointmenttype)=="Sabbatical" || get_appointment_type_from_id($appointmenttype)=="Visiting"){

				if(get_appointment_type_from_id($appointmenttype)=="Tenure"){

					if(is_rank_academic($advertid)==true){

						$checktrue="checked";

					}else{

						$checkfalse="checked";	

					}

					echo "<div class='span3 control-group'>";

					echo "<div><input type='radio' name='staff_category' value='Academic' $checktrue />Academic Staff &nbsp;&nbsp; <input type='radio' name='staff_category' value='Non-Academic' $checkfalse />Non Academic Staff</div>";

					echo "</div>";



				}

			echo "<div class='span2'>";

			echo "<div>Salary Scale:</div>";

										echo "<div><select name='salary_category' id='salary_category'>";

										echo "<option value='0'>--Please select--</option>";

										$query=mysql_query("select salaryid,category from tblsalarytable");

												if (mysql_num_rows($query)>0){

													for($i=0;$i<mysql_num_rows($query);$i++){

														$salary_id=mysql_result($query,$i,'salaryid');

														$category=mysql_result($query,$i,'category');

														if($salaryid==$salary_id){

														echo "<option value='$salary_id' selected>$category</option>";

														}else{

														echo "<option value='$salary_id'>$category</option>";

														}

													}

												}	

										echo "</select></div>";

			echo "</div>";

			$gradelevel_step=explode('/',$gradelevel);

			$gradelevel=$gradelevel_step[0];

			$step=$gradelevel_step[1];

			echo "<div class='span2'>";

			echo "<div>Grade Level:</div>";

			echo "<div><select name='gradelevel' id='gradelevel'>";

														echo "<option value='0'>--Please Select--</option>";

														for($i=1;$i<=15;$i++){

															if($i<10)

															$i='0'.$i;

															if($gradelevel==$i){

															echo "<option value='$i' selected>$i</option>";

															}else{

															echo "<option value='$i'>$i</option>";

															}

														}

														echo "</select></div>";

			echo "</div>";

			echo "<div>Step:</div>";

			echo "<div><select name='step' id='step'>";

														echo "<option value='0'>--Please Select--</option>";

														for($i=1;$i<=15;$i++){

															if($i<10)

															$i='0'.$i;

															if($step==$i){

															echo "<option value='$i' selected>$i</option>";

															}else{

															echo "<option value='$i'>$i</option>";

															}

														}

														echo "</select></div>";

			echo "</div>";

		}//end if Tunure

		if(get_appointment_type_from_id($appointmenttype)=="Temporary Part-Time"){

			echo "<div class='span2'>";

			echo "<div>Session:<div>";

			echo "<div><input type='text' name='temporary_parttime_session' value='$temporarysession'/></div>";

			echo "</div>";

		}//end if temporary part time

	}// end function get_applicant_appointment_settings

	

	// function to determine where to send a copy of appointment letter

	function send_copy_of_appointment_to($departmentid){

		$result="";

		$query=mysql_query("select tblunitheads.unitheaddescription from tblunitheads

							inner join tbldepartment on tbldepartment.unitheadid=tblunitheads.unitheadid where tbldepartment.departmentid=$departmentid") 

							or die("Query cannot be executed: ".mysql_error());

						if(mysql_num_rows($query)==1)

						$result=mysql_result($query,0,'unitheaddescription');

						else

						$result="The unit is not defined to recieved a copy";

		return $result;

	}

	

	// function to display either as in the department of or in the Iya Abubakar Computer Centre etc.

	function show_department($departmentid){

		$append="";

		$result=send_copy_of_appointment_to($departmentid);

		switch($result){

			case "Head of Department" :

				$append="Department of";

				break;

			default:

				$append="";

				break;

		}

		return $append;

	}

	// function to determine whether is VC's Office or Office of the Bursar

	function is_vc_office_or_bursar($departmentid){

		$status=false;

		$result=send_copy_of_appointment_to($departmentid);

		switch($result){

			case "Vice-Chancellor" : case "Bursar" :

				$status=true;

				break;

			default:

				$status=false;

				break;

		}

		return $status;

	}

	

	function get_advertresponse_id($applicantid, $advert_id){

		$advertresponseid=0;

		$query=mysql_query("select tbladvertresponses.responseid from tbladvertresponses where 

							tbladvertresponses.applicantid=$applicantid AND tbladvertresponses.advertid=$advert_id");

							if(mysql_num_rows($query)>0){

							$advertresponseid=mysql_result($query,0,'responseid');

							}

		return  $advertresponseid;

	}

	function is_applicant_appointment_ready($applicantid,$advert_id){

		$status=false;

		$advertresponseid=get_advertresponse_id($applicantid, $advert_id);

		$query=mysql_query("select notified from tblapplicantsappointments where advertresponseid=$advertresponseid ")

			   or die("Query cannot be executed: ".mysql_error());

			if(mysql_num_rows($query)>0){

				$notified=mysql_result($query,0,'notified');

				if($notified=="yes"){

				$status=true;

				}

			}

		return $status;

	}

	

	function show_Applicant_appointment($applicantid,$advert_id){

		$advertresponseid=get_advertresponse_id($applicantid, $advert_id);

		$pnumber='';

		$salaryscale='';

		$appointmenttypeid=0;

			$query=mysql_query("select appointmenttype, gradelevel from tblapplicantsappointments where advertresponseid=$advertresponseid");

			if(mysql_num_rows($query)>0){

				$row=mysql_fetch_assoc($query);

				$appointmenttypeid=$row['appointmenttype'];

				//$appointmenttype=get_appointment_type_from_id($row['appointmenttype']);

			}

				

		

		return get_appointment_template($appointmenttypeid);;

	

	}

	

	function load_relationships(){

		

		$cmb = '<option value = "-1" selected="selected">-- Please Select --</option>';		



			$cmb .= '<option value = "Spouse">Spouse</option>';

			$cmb .= '<option value = "Biological Child">Biological Child</option>';

			$cmb .= '<option value = "Legal Ward">Legal Ward</option>';



		return $cmb;

	}





function load_state(){

		$cmb = '';



			$q = "SELECT stateid, statename FROM tblstate ORDER BY statename";

						

			$query = mysql_query($q);

			$foundRecord = mysql_num_rows($query);

			

			if ($foundRecord >= 1) {

				$cmb = '<option value = "-1">-- Please Select --</option>';				

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

					//$i++;

					if($row['stateid'] == $stateID){

					$cmb .= "<option value='" . $row['stateid'] . "' selected='selected'>" . $row['statename'] . "</option>";

					}else{

					$cmb .= "<option value='" . $row['stateid'] . "'>" . $row['statename'] . "</option>";

					}

				}//end while

				$cmb .= "<option value = '0'>-- Others --</option>";

			}else{

			$cmb = "<option value = '-1'>No Data in State Table</option>";

		}//end if ($foundRecord == 1)

			

		echo $cmb;

	}

	

	function load_senatorialdistrict($stateID, $session_senatorialDistrict){

		$cmb = '';



			$q = "SELECT senatorial_id, senatorialdistrictname FROM tbldistrict  WHERE stateid = $stateID";

						

			$query = mysql_query($q);

			$foundRecord = mysql_num_rows($query);

			

			if ($foundRecord >= 1) {

				$cmb .= "<option value = '-1'>-- Please Select --</option>";				

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

					if($session_senatorialDistrict == $row['senatorialdistrictname']){

						$cmb .= "<option value='" . $row['senatorial_id'] . "' selected='selected'>" . $row['senatorialdistrictname'] . "</option>";

					}else{

						$cmb .= "<option value='" . $row['senatorial_id'] . "'>" . $row['senatorialdistrictname'] . "</option>";

					}

				}//end while

			}else{

				$cmb = "<option value = '-1'>No Data in Senatorial District Table</option>";

		}//end if ($foundRecord == 1)

		//$stateID = $row['senatorial_id'];

		

		return $cmb;

	}

	

	function load_senatorialID($state_id){

		$cmb = '';



			$q = "SELECT senatorial_id, senatorialdistrictname FROM tbldistrict WHERE stateid = $state_id";



			/*if(isset($state_id) && $state_id > 0){

				$q .= "WHERE stateid = $state_id";

			}*/

			//$q .= "ORDER BY senatorialdistrictname";

			

			$query = mysql_query($q) or die("Query cannoe be executed: ".mysql_error());

			$foundRecord = mysql_num_rows($query);

			

			if ($foundRecord >= 1) {

				$cmb .= "<option value = '-1'>-- Please Select --</option>";				

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

					

						$cmb .= "<option value='" . $row['senatorial_id'] . "'>" . $row['senatorialdistrictname'] . "</option>";

				

				}//end while

			}else{

				$cmb = "<option value = '-1'>No Data in Senatorial District Table</option>";

		}//end if ($foundRecord == 1)

		//$stateID = $row['senatorial_id'];

		

		return $cmb;

	}

	

	function load_gender(){

		$cmb = '<option value = "-1">-- Please Select --</option>';		



		if(isset($_SESSION['bio_Gender']) && $_SESSION['bio_Gender'] == 'M'){

			$cmb .= '<option value = "M" selected="selected">Male</option>';

		}else{

			$cmb .= '<option value = "M">Male</option>';

		}



		if(isset($_SESSION['bio_Gender']) && $_SESSION['bio_Gender'] == 'F'){

			$cmb .= '<option value = "F" selected="selected">Female</option>';

		}else{

			$cmb .= '<option value = "F">Female</option>';

		}

		

		echo $cmb;

	}

	

	function loadGender($session_Gender){

		

		$cmb = '<option value = "-1">-- Please Select --</option>';		



		

		if(isset($session_Gender) && $session_Gender == 'Male'){	

				$cmb .= '<option value = "M" selected="selected">Male</option>';

			}else{

				$cmb .= '<option value = "Male">Male</option>';

			}



			if(isset($session_Gender) && $session_Gender == 'Female'){

				$cmb .= '<option value = "Female" selected="selected">Female</option>';

			}else{

				$cmb .= '<option value = "Female">Female</option>';

			}

		

		return $cmb;

	}



	

?>