PageRenderTime 57ms CodeModel.GetById 26ms RepoModel.GetById 0ms app.codeStats 0ms

/recruitment/reports/recruitmentreport.ajax.php

https://bitbucket.org/lecturer34/hrmis
PHP | 714 lines | 547 code | 162 blank | 5 comment | 730 complexity | 8530ff10c08bd4e0bbc9e06a9a492b6b MD5 | raw file
Possible License(s): LGPL-2.1

Large files files are truncated, but you can click here to view the full file

  1. <?php
  2. require_once("../../lib/globals.php");
  3. require_once("../../lib/database.php");
  4. if (isset($_REQUEST['getdepartments']) && isset($_REQUEST['faculty'])) {
  5. echo showDepartments($_REQUEST['faculty']);
  6. exit;
  7. }
  8. function showDepartments($faculty = "", $depts = "") {
  9. openConnection();
  10. $output = '';
  11. if ($faculty != "") {
  12. $sql = "SELECT * FROM `tbldepartment` WHERE facultyid = $faculty ORDER BY name ASC";
  13. } else {
  14. $sql = "SELECT * FROM `tbldepartment`";
  15. }
  16. $result = mysql_query($sql);
  17. while ($row = mysql_fetch_array($result)) {
  18. if ($row['facid'] == $faculty) {
  19. $output .= "<option value='" . $row['departmentid'] . "'>" . $row['name'] . "</option>";
  20. } else {
  21. $output .= "<option value='" . $row['departmentid'] . "'>" . $row['name'] . "</option>";
  22. }
  23. }
  24. $output .= "<option selected='selected' value='0'>Show for All</option>";
  25. echo $output;
  26. }
  27. if (isset($_REQUEST['getsummary']) && isset($_REQUEST['department'])) {
  28. echo theSummary($_REQUEST['department']);
  29. exit;
  30. }
  31. function theSummary($department = "0", $summary = "") {
  32. openConnection();
  33. $output = '';
  34. if (($department != 0) && ($department > 0)) {
  35. $sql1 = "SELECT COUNT(vacancyid) as vacqty FROM tblvacancies INNER JOIN tbldepartment ON (tblvacancies.departmentid = tbldepartment.departmentid) WHERE tblvacancies.departmentid = $department";
  36. $sql2 = "SELECT COUNT(advertisementid) as advqty FROM tbladvertisements INNER JOIN tblvacancies ON (tbladvertisements.vacancyid = tblvacancies.vacancyid) INNER JOIN tbldepartment ON (tblvacancies.departmentid = tbldepartment.departmentid) WHERE tbldepartment.departmentid = $department";
  37. $sql3 = "SELECT COUNT(applicationid) as appqty FROM tblapplication INNER JOIN tbladvertisements ON (tblapplication.advertisementid = tbladvertisements.advertisementid) INNER JOIN tblvacancies ON (tbladvertisements.vacancyid = tblvacancies.vacancyid) INNER JOIN tbldepartment ON (tblvacancies.departmentid = tbldepartment.departmentid) WHERE tbldepartment.departmentid = $department";
  38. $sql4 = "SELECT SUM(vacantslots) as empqty, status, tbldepartment.name as dname FROM tblvacancies INNER JOIN tbldepartment ON (tblvacancies.departmentid = tbldepartment.departmentid) WHERE status = 'Occupied' AND tbldepartment.departmentid = $department";
  39. } elseif ($department == 0) {
  40. $sql1 = "SELECT COUNT(vacancyid) as vacqty FROM tblvacancies INNER JOIN tbldepartment ON (tblvacancies.departmentid = tbldepartment.departmentid)";
  41. $sql2 = "SELECT COUNT(advertisementid) as advqty FROM tbladvertisements INNER JOIN tblvacancies ON (tbladvertisements.vacancyid = tblvacancies.vacancyid) INNER JOIN tbldepartment ON (tblvacancies.departmentid = tbldepartment.departmentid)";
  42. $sql3 = "SELECT COUNT(applicationid) as appqty FROM tblapplication INNER JOIN tbladvertisements ON (tblapplication.advertisementid = tbladvertisements.advertisementid) INNER JOIN tblvacancies ON (tbladvertisements.vacancyid = tblvacancies.vacancyid) INNER JOIN tbldepartment ON (tblvacancies.departmentid = tbldepartment.departmentid)";
  43. $sql4 = "SELECT SUM(vacantslots) as empqty, status FROM tblvacancies INNER JOIN tbldepartment ON (tblvacancies.departmentid = tbldepartment.departmentid) WHERE status = 'Occupied'";
  44. }
  45. $result1 = mysql_query($sql1);
  46. $result2 = mysql_query($sql2);
  47. $result3 = mysql_query($sql3);
  48. $result4 = mysql_query($sql4);
  49. $row1 = mysql_fetch_assoc($result1);
  50. $row2 = mysql_fetch_assoc($result2);
  51. $row3 = mysql_fetch_assoc($result3);
  52. $row4 = mysql_fetch_assoc($result4);
  53. if (($department != 0) && ($department > 0)) {
  54. $output .= "<h3 style='text-align: center;'>Summary Report for ".$row4['dname']."</h3>";
  55. } elseif ($department == 0) {
  56. $output .= "<h3 style='text-align: center;'>Summary Report for ALL Departments</h3>";
  57. }
  58. $output .= "<table style='width: auto; font-size: 16px; padding: 2px; border: none; margin-bottom: 0;'><tr style='border: none;'><td>S/N</td><td style='vertical-align: middle'>REPORT TYPE</td><td style='vertical-align: middle'>TOTALS</td><td style='vertical-align: middle'>Detail</td></tr>";
  59. if ($row4['empqty'] == NULL) { $returnedvalue = 0; } else { $returnedvalue = $row4['empqty']; }
  60. $output .= "<tr style='border: none;'><td>1.</td><td>VACANCIES</td><td>".$row1['vacqty']."</td><td>View</td></tr><tr style='border: none;'><td>2.</td><td>JOB ADVERTS</td><td>".$row2['advqty']."</td><td>View</td></tr><tr style='border: none;'><td>3.</td><td>APPLICANTS</td><td>".$row3['appqty']."</td><td>View</td></tr><tr style='border: none;'><td>4.</td><td>EMPLOYMENTS</td><td>".$returnedvalue."</td><td>View</td></tr></table>";
  61. echo $output;
  62. exit;
  63. }
  64. if (isset($_REQUEST['getvacancybydept']) && isset($_REQUEST['department'])) {
  65. echo VacancyByDept($_REQUEST['department']);
  66. exit;
  67. }
  68. function VacancyByDept($department = "0", $vacancy = "") {
  69. openConnection();
  70. $output = '';
  71. $dd = substr($date, 3, 2);
  72. $mm = substr($date, 0, 2);
  73. $yy = substr($date, 6, 4);
  74. $dbdate = "'"."$yy-$mm-$dd"."'";
  75. if ($department != "0") {
  76. $sql = "SELECT tbldepartment.name AS dnm, tblrank.name AS rnm, tblvacancies.vacantslots as vsl, tblvacancies.jobschedule as jsh, tblvacancies.jobshifts as jsf, tblvacancies.status as sts, tblvacancies.vacancyreason as vrs, tblvacancies.isadvertised as adv, tblvacancies.effectivedate as eff FROM tblvacancies INNER JOIN tbldepartment ON (tblvacancies.departmentid = tbldepartment.departmentid) INNER JOIN tblrank ON (tblvacancies.rankid = tblrank.rankid) WHERE tblvacancies.departmentid = $department";
  77. } else {
  78. $sql = "SELECT tbldepartment.name AS dnm, tblrank.name AS rnm, tblvacancies.vacantslots as vsl, tblvacancies.jobschedule as jsh, tblvacancies.jobshifts as jsf, tblvacancies.status as sts, tblvacancies.vacancyreason as vrs, tblvacancies.isadvertised as adv, tblvacancies.effectivedate as eff FROM tblvacancies INNER JOIN tbldepartment ON (tblvacancies.departmentid = tbldepartment.departmentid) INNER JOIN tblrank ON (tblvacancies.rankid = tblrank.rankid)";
  79. }
  80. $result = mysql_query($sql);
  81. $num_rows = mysql_num_rows($result);
  82. $output .= "<table style='width: auto; font-size: 16px; padding: 2px; border: none; margin-bottom: 0;'><tr style='border: none;'><td>S/N</td><td style='vertical-align: middle'>DEPARTMENT</td><td style='vertical-align: middle'>RANK</td><td style='vertical-align: middle'>SLOTS</td><td style='vertical-align: middle'>SCHEDULE</td><td style='vertical-align: middle'>SHIFTS</td><td style='vertical-align: middle'>STATUS</td><td style='vertical-align: middle'>REASON</td><td style='vertical-align: middle'>ADVERT STATUS</td><td style='vertical-align: middle'>EFFECTIVE DATE</td></tr>";
  83. $c = 1;
  84. if ($num_rows > 0) {
  85. while ($row = mysql_fetch_assoc($result)) {
  86. $output .= "<tr style='border: none;'><td>".$c."</td><td>".$row['dnm']."</td><td>".$row['rnm']."</td><td>".$row['vsl']."</td><td>".$row['jsh']."</td><td>".$row['jsf']."</td><td>".$row['sts']."</td><td>".$row['vrs']."</td><td>".$row['adv']."</td><td>".$row['eff']."</td></tr>";
  87. $c++;
  88. }
  89. $output .= "</table>";
  90. echo $output;
  91. } else {
  92. $output .= "<tr style='border: none;'><td colspan='10'>No vacancy exists.</td></tr></table>";
  93. echo $output;
  94. }
  95. exit;
  96. }
  97. if (isset($_REQUEST['getvacancybydate']) && isset($_REQUEST['department']) && isset($_REQUEST['date'])) {
  98. echo VacancyByDate($_REQUEST['department'],$_REQUEST['date']);
  99. exit;
  100. }
  101. function VacancyByDate($department = "0", $date = "", $vacancy = "") {
  102. openConnection();
  103. $output = '';
  104. $dd = substr($date, 3, 2);
  105. $mm = substr($date, 0, 2);
  106. $yy = substr($date, 6, 4);
  107. $dbdate = "'"."$yy-$mm-$dd"."'";
  108. if ($date != "") {
  109. $sql = "SELECT tbldepartment.name AS dnm, tblrank.name AS rnm, tblvacancies.vacantslots as vsl, tblvacancies.jobschedule as jsh, tblvacancies.jobshifts as jsf, tblvacancies.status as sts, tblvacancies.vacancyreason as vrs, tblvacancies.isadvertised as adv, tblvacancies.effectivedate as eff FROM tblvacancies INNER JOIN tbldepartment ON (tblvacancies.departmentid = tbldepartment.departmentid) INNER JOIN tblrank ON (tblvacancies.rankid = tblrank.rankid) WHERE tblvacancies.departmentid = $department AND effectivedate >= $dbdate";
  110. } else {
  111. $sql = "SELECT tbldepartment.name AS dnm, tblrank.name AS rnm, tblvacancies.vacantslots as vsl, tblvacancies.jobschedule as jsh, tblvacancies.jobshifts as jsf, tblvacancies.status as sts, tblvacancies.vacancyreason as vrs, tblvacancies.isadvertised as adv, tblvacancies.effectivedate as eff FROM tblvacancies INNER JOIN tbldepartment ON (tblvacancies.departmentid = tbldepartment.departmentid) INNER JOIN tblrank ON (tblvacancies.rankid = tblrank.rankid)";
  112. }
  113. $result = mysql_query($sql);
  114. $num_rows = mysql_num_rows($result);
  115. $output .= "<table style='width: auto; font-size: 16px; padding: 2px; border: none; margin-bottom: 0;'><tr style='border: none;'><td>S/N</td><td style='vertical-align: middle'>DEPARTMENT</td><td style='vertical-align: middle'>RANK</td><td style='vertical-align: middle'>SLOTS</td><td style='vertical-align: middle'>SCHEDULE</td><td style='vertical-align: middle'>SHIFTS</td><td style='vertical-align: middle'>STATUS</td><td style='vertical-align: middle'>REASON</td><td style='vertical-align: middle'>ADVERT STATUS</td><td style='vertical-align: middle'>EFFECTIVE DATE</td></tr>";
  116. $c = 1;
  117. if ($num_rows > 0) {
  118. while ($row = mysql_fetch_assoc($result)) {
  119. $output .= "<tr style='border: none;'><td>".$c."</td><td>".$row['dnm']."</td><td>".$row['rnm']."</td><td>".$row['vsl']."</td><td>".$row['jsh']."</td><td>".$row['jsf']."</td><td>".$row['sts']."</td><td>".$row['vrs']."</td><td>".$row['adv']."</td><td>".$row['eff']."</td></tr>";
  120. $c++;
  121. }
  122. $output .= "</table>";
  123. echo $output;
  124. } else {
  125. $output .= "<tr style='border: none;'><td colspan='10'>No vacancy exists.</td></tr></table>";
  126. echo $output;
  127. }
  128. exit;
  129. }
  130. if (isset($_REQUEST['getvacancy']) && isset($_REQUEST['faculty']) && isset($_REQUEST['department']) && isset($_REQUEST['mindate']) && isset($_REQUEST['maxdate']) && isset($_REQUEST['position']) && isset($_REQUEST['advert'])) {
  131. echo Vacancy($_REQUEST['faculty'],$_REQUEST['department'],$_REQUEST['mindate'],$_REQUEST['maxdate'],$_REQUEST['position'],$_REQUEST['advert']);
  132. exit;
  133. }
  134. function Vacancy($faculty = 0, $department = 0, $mindate = "", $maxdate = "", $position = 0, $advert = 0, $vacancy = "") {
  135. openConnection();
  136. $output = '';
  137. if (($mindate != "") && ($maxdate != "")) {
  138. $mindd = substr($mindate, 3, 2);
  139. $minmm = substr($mindate, 0, 2);
  140. $minyy = substr($mindate, 6, 4);
  141. $dbmindate = "'"."$minyy-$minmm-$mindd"."'";
  142. $maxdd = substr($maxdate, 3, 2);
  143. $maxmm = substr($maxdate, 0, 2);
  144. $maxyy = substr($maxdate, 6, 4);
  145. $dbmaxdate = "'"."$maxyy-$maxmm-$maxdd"."'";
  146. } elseif (($mindate != "") && ($maxdate == "")) {
  147. $mindd = substr($mindate, 3, 2);
  148. $minmm = substr($mindate, 0, 2);
  149. $minyy = substr($mindate, 6, 4);
  150. $dbmindate = "'"."$minyy-$minmm-$mindd"."'";
  151. $dbmaxdate = "";
  152. } elseif (($mindate == "") && ($maxdate != "")) {
  153. $maxdd = substr($maxdate, 3, 2);
  154. $maxmm = substr($maxdate, 0, 2);
  155. $maxyy = substr($maxdate, 6, 4);
  156. $dbmaxdate = "'"."$maxyy-$maxmm-$maxdd"."'";
  157. $dbmindate = "";
  158. } elseif (($mindate == "") && ($maxdate == "")) {
  159. $dbmindate = "";
  160. $dbmaxdate = "";
  161. }
  162. $advertstatus = "'"."$advert"."'";
  163. if (($faculty == 0) && ($department == 0) && ($mindate == '') && ($maxdate == '') && ($position == 0) && ($advert == "0")) {
  164. $sql = "SELECT tbldepartment.name AS dnm, tblrank.name AS rnm, tblvacancies.vacantslots as vsl, tblvacancies.jobschedule as jsh, tblvacancies.jobshifts as jsf, tblvacancies.status as sts, tblvacancies.vacancyreason as vrs, tblvacancies.isadvertised as adv, tblvacancies.effectivedate as eff FROM tblvacancies INNER JOIN tbldepartment ON (tblvacancies.departmentid = tbldepartment.departmentid) INNER JOIN tblfaccentres ON (tbldepartment.facultyid = tblfaccentres.facid) INNER JOIN tblrank ON (tblvacancies.rankid = tblrank.rankid)";
  165. } elseif (($faculty != 0) || ($department != 0) || ($mindate != "") || ($maxdate != "") || ($position != 0) || ($advert != "0")) {
  166. $sql = "SELECT tbldepartment.name AS dnm, tblrank.name AS rnm, tblvacancies.vacantslots as vsl, tblvacancies.jobschedule as jsh, tblvacancies.jobshifts as jsf, tblvacancies.status as sts, tblvacancies.vacancyreason as vrs, tblvacancies.isadvertised as adv, tblvacancies.effectivedate as eff FROM tblvacancies INNER JOIN tbldepartment ON (tblvacancies.departmentid = tbldepartment.departmentid) INNER JOIN tblfaccentres ON (tbldepartment.facultyid = tblfaccentres.facid) INNER JOIN tblrank ON (tblvacancies.rankid = tblrank.rankid) WHERE ";
  167. if (($faculty != 0) && ($department != 0) && ($dbmindate != "") && ($dbmaxdate != "") && ($position != 0) && ($advert != "0")) {
  168. $sql .= "tbldepartment.facultyid = $faculty AND tblvacancies.departmentid = $department AND tblvacancies.effectivedate >= $dbmindate AND tblvacancies.effectivedate <= $dbmaxdate AND tblvacancies.isadvertised = $advertstatus AND tblvacancies.rankid = $position AND tblvacancies.isadvertised = $advertstatus";
  169. } elseif (($department != 0) && ($dbmindate != "") && ($dbmaxdate != "") && ($position != 0) && ($advert != "0")) {
  170. $sql .= "tblvacancies.departmentid = $department AND tblvacancies.effectivedate >= $dbmindate AND tblvacancies.effectivedate <= $dbmaxdate AND tblvacancies.rankid = $position AND tblvacancies.isadvertised = $advertstatus";
  171. } elseif (($faculty != 0) && ($dbmindate != "") && ($dbmaxdate != "") && ($position != 0) && ($advert != "0")) {
  172. $sql .= "tbldepartment.facultyid = $faculty AND tblvacancies.effectivedate >= $dbmindate AND tblvacancies.effectivedate <= $dbmaxdate AND tblvacancies.rankid = $position AND tblvacancies.isadvertised = $advertstatus";
  173. } elseif (($faculty != 0) && ($department != 0) && ($dbmaxdate != "") && ($position != 0) && ($advert != "0")) {
  174. $sql .= "tbldepartment.facultyid = $faculty AND tblvacancies.departmentid = $department AND tblvacancies.effectivedate <= $dbmaxdate AND tblvacancies.rankid = $position AND tblvacancies.isadvertised = $advertstatus";
  175. } elseif (($faculty != 0) && ($department != 0) && ($dbmindate != "") && ($dbmaxdate != "") && ($position != 0)) {
  176. $sql .= "tbldepartment.facultyid = $faculty AND tblvacancies.departmentid = $department AND tblvacancies.effectivedate >= $dbmindate AND tblvacancies.effectivedate <= $dbmaxdate AND tblvacancies.isadvertised = $advertstatus AND tblvacancies.rankid = $position";
  177. } elseif (($dbmindate != "") && ($dbmaxdate != "") && ($position != 0) && ($advert != "0")) {
  178. $sql .= "tblvacancies.effectivedate >= $dbmindate AND tblvacancies.effectivedate <= $dbmaxdate AND tblvacancies.rankid = $position AND tblvacancies.isadvertised = $advertstatus";
  179. } elseif (($department != 0) && ($dbmaxdate != "") && ($position != 0) && ($advert != "0")) {
  180. $sql .= "tblvacancies.departmentid = $department AND tblvacancies.effectivedate <= $dbmaxdate AND tblvacancies.rankid = $position AND tblvacancies.isadvertised = $advertstatus";
  181. } elseif (($department != 0) && ($dbmindate != "") && ($position != "0") && ($advert != "0")) {
  182. $sql .= "tblvacancies.departmentid = $department AND tblvacancies.effectivedate >= $dbmindate AND tblvacancies.rankid = $position AND tblvacancies.isadvertised = $advertstatus";
  183. } elseif (($department != 0) && ($dbmindate != "") && ($dbmaxdate != "") && ($advert != "0")) {
  184. $sql .= "tblvacancies.departmentid = $department AND tblvacancies.effectivedate >= $dbmindate AND tblvacancies.effectivedate <= $dbmaxdate AND tblvacancies.isadvertised = $advertstatus";
  185. } elseif (($department != 0) && ($dbmindate != "") && ($dbmaxdate != "") && ($position != 0)) {
  186. $sql .= "tblvacancies.departmentid = $department AND tblvacancies.effectivedate >= $dbmindate AND tblvacancies.effectivedate <= $dbmaxdate AND tblvacancies.rankid = $position";
  187. } elseif (($faculty != 0) && ($dbmaxdate != "") && ($position != 0) && ($advert != "0")) {
  188. $sql .= "tbldepartment.facultyid = $faculty AND tblvacancies.effectivedate <= $dbmaxdate AND tblvacancies.rankid = $position AND tblvacancies.isadvertised = $advertstatus";
  189. } elseif (($faculty != 0) && ($dbmindate != "") && ($position != 0) && ($advert != "0")) {
  190. $sql .= "tbldepartment.facultyid = $faculty AND tblvacancies.effectivedate >= $dbmindate AND tblvacancies.rankid = $position AND tblvacancies.isadvertised = $advertstatus";
  191. } elseif (($faculty != 0) && ($dbmindate != "") && ($dbmaxdate != "") && ($advert != "0")) {
  192. $sql .= "tbldepartment.facultyid = $faculty AND tblvacancies.effectivedate >= $dbmindate AND tblvacancies.effectivedate <= $dbmaxdate AND tblvacancies.isadvertised = $advertstatus";
  193. } elseif (($faculty != 0) && ($dbmindate != "") && ($dbmaxdate != "") && ($position != 0)) {
  194. $sql .= "tbldepartment.facultyid = $faculty AND tblvacancies.effectivedate >= $dbmindate AND tblvacancies.effectivedate <= $dbmaxdate AND tblvacancies.rankid = $position";
  195. } elseif (($faculty != 0) && ($department != 0) && ($position != 0) && ($advert != "0")) {
  196. $sql .= "tbldepartment.facultyid = $faculty AND tblvacancies.departmentid = $department AND tblvacancies.rankid = $position AND tblvacancies.isadvertised = $advertstatus";
  197. } elseif (($faculty != 0) && ($department != 0) && ($dbmaxdate != "") && ($advert != "0")) {
  198. $sql .= "tbldepartment.facultyid = $faculty AND tblvacancies.departmentid = $department AND tblvacancies.effectivedate <= $dbmaxdate AND tblvacancies.isadvertised = $advertstatus";
  199. } elseif (($faculty != 0) && ($department != 0) && ($dbmaxdate != "") && ($position != 0)) {
  200. $sql .= "tbldepartment.facultyid = $faculty AND tblvacancies.departmentid = $department AND tblvacancies.effectivedate <= $dbmaxdate AND tblvacancies.rankid = $position";
  201. } elseif (($faculty != 0) && ($department != 0) && ($dbmindate != "") && ($advert != "0")) {
  202. $sql .= "tbldepartment.facultyid = $faculty AND tblvacancies.departmentid = $department AND tblvacancies.effectivedate >= $dbmindate AND tblvacancies.isadvertised = $advertstatus";
  203. } elseif (($dbmaxdate != "") && ($position != 0) && ($advert != "0")) {
  204. $sql .= "tblvacancies.effectivedate <= $dbmaxdate AND tblvacancies.rankid = $position AND tblvacancies.isadvertised = $advertstatus";
  205. } elseif (($dbmindate != "") && ($position != 0) && ($advert != "0")) {
  206. $sql .= "tblvacancies.effectivedate >= $dbmindate AND tblvacancies.rankid = $position AND tblvacancies.isadvertised = $advertstatus";
  207. } elseif (($dbmindate != "") && ($dbmaxdate != "") && ($position != 0)) {
  208. $sql .= "tblvacancies.effectivedate >= $dbmindate AND tblvacancies.effectivedate <= $dbmaxdate AND tblvacancies.rankid = $position";
  209. } elseif (($department != 0) && ($position != 0) && ($advert != "0")) {
  210. $sql .= "tblvacancies.departmentid = $department AND tblvacancies.rankid = $position AND tblvacancies.isadvertised = $advertstatus";
  211. } elseif (($department != 0) && ($dbmaxdate != "") && ($advert != 0)) {
  212. $sql .= "tblvacancies.departmentid = $department AND tblvacancies.effectivedate <= $dbmaxdate AND tblvacancies.isadvertised = $advertised";
  213. } elseif (($department != 0) && ($dbmindate != "") && ($advert != "0")) {
  214. $sql .= "tblvacancies.departmentid = $department AND tblvacancies.effectivedate >= $dbmindate AND tblvacancies.isadvertised = $advertstatus";
  215. } elseif (($department != 0) && ($dbmindate != "") && ($position != "0")) {
  216. $sql .= "tblvacancies.departmentid = $department AND tblvacancies.effectivedate >= $dbmindate AND tblvacancies.rankid = $position";
  217. } elseif (($department != 0) && ($dbmindate != "") && ($dbmaxdate != "")) {
  218. $sql .= "tblvacancies.departmentid = $department AND tblvacancies.effectivedate >= $dbmindate AND tblvacancies.effectivedate <= $dbmaxdate ";
  219. } elseif (($faculty != 0) && ($position != 0) && ($advert != "0")) {
  220. $sql .= "tbldepartment.facultyid = $faculty AND tblvacancies.rankid = $position AND tblvacancies.isadvertised = $advertstatus";
  221. } elseif (($faculty != 0) && ($position != 0) && ($advert != "0")) {
  222. $sql .= "tbldepartment.facultyid = $faculty AND tblvacancies.rankid = $position AND tblvacancies.isadvertised = $advertstatus";
  223. } elseif (($faculty != 0) && ($dbmaxdate != "") && ($advert != "0")) {
  224. $sql .= "tbldepartment.facultyid = $faculty AND tblvacancies.effectivedate <= $dbmaxdate AND tblvacancies.isadvertised = $advertstatus";
  225. } elseif (($faculty != 0) && ($dbmaxdate != "") && ($position != 0)) {
  226. $sql .= "tbldepartment.facultyid = $faculty AND tblvacancies.effectivedate <= $dbmaxdate AND tblvacancies.rankid = $position";
  227. } elseif (($faculty != 0) && ($dbmindate != "") && ($advert != "0")) {
  228. $sql .= "tbldepartment.facultyid = $faculty AND tblvacancies.effectivedate >= $dbmindate AND tblvacancies.isadvertised = $advertstatus";
  229. } elseif (($faculty != 0) && ($dbmindate != "") && ($position != 0)) {
  230. $sql .= "tbldepartment.facultyid = $faculty AND tblvacancies.effectivedate >= $dbmindate AND tblvacancies.rankid = $position";
  231. } elseif (($faculty != 0) && ($dbmindate != "") && ($dbmaxdate != "")) {
  232. $sql .= "tbldepartment.facultyid = $faculty AND tblvacancies.effectivedate >= $dbmindate AND tblvacancies.effectivedate <= $dbmaxdate";
  233. } elseif (($faculty != 0) && ($department != 0) && ($advert != "0")) {
  234. $sql .= "tbldepartment.facultyid = $faculty AND tblvacancies.departmentid = $department AND tblvacancies.isadvertised = $advertstatus";
  235. } elseif (($faculty != 0) && ($department != 0) && ($position != 0)) {
  236. $sql .= "tbldepartment.facultyid = $faculty AND tblvacancies.departmentid = $department AND tblvacancies.rankid = $position";
  237. } elseif (($faculty != 0) && ($department != 0) && ($dbmaxdate != "")) {
  238. $sql .= "tbldepartment.facultyid = $faculty AND tblvacancies.departmentid = $department AND tblvacancies.effectivedate <= $dbmaxdate";
  239. } elseif (($faculty != 0) && ($department != 0) && ($dbmaxdate != "")) {
  240. $sql .= "tbldepartment.facultyid = $faculty AND tblvacancies.departmentid = $department AND tblvacancies.effectivedate <= $dbmaxdate";
  241. } elseif (($faculty != 0) && ($department != 0) && ($dbmindate != "")) {
  242. $sql .= "tbldepartment.facultyid = $faculty AND tblvacancies.departmentid = $department AND tblvacancies.effectivedate >= $dbmindate";
  243. } elseif (($position != 0) && ($advert != "0")) {
  244. $sql .= "tblvacancies.rankid = $position AND tblvacancies.isadvertised != 0 AND tblvacancies.isadvertised = $advertstatus";
  245. } elseif (($dbmaxdate != "") && ($advert != "0")) {
  246. $sql .= "tblvacancies.effectivedate <= $dbmaxdate AND tblvacancies.isadvertised = $advertstatus";
  247. } elseif (($dbmaxdate != "") && ($position != 0)) {
  248. $sql .= "tblvacancies.effectivedate <= $dbmaxdate AND tblvacancies.rankid = $position";
  249. } elseif (($dbmindate != "") && ($advert != "0")) {
  250. $sql .= "tblvacancies.effectivedate >= $dbmindate AND tblvacancies.isadvertised = $advertstatus";
  251. } elseif (($dbmindate != "") && ($position != 0)) {
  252. $sql .= "tblvacancies.effectivedate >= $dbmindate AND tblvacancies.rankid = $position";
  253. } elseif (($dbmindate != "") && ($dbmaxdate != "")) {
  254. $sql .= "tblvacancies.effectivedate >= $dbmindate AND tblvacancies.effectivedate <= $dbmaxdate";
  255. } elseif (($department != 0) && ($advert != "0")) {
  256. $sql .= "tblvacancies.departmentid = $department AND tblvacancies.isadvertised = $advertstatus";
  257. } elseif (($department != 0) && ($position != 0)) {
  258. $sql .= "tblvacancies.departmentid = $department AND tblvacancies.rankid = $position";
  259. } elseif (($department != 0) && ($dbmaxdate != "")) {
  260. $sql .= "tblvacancies.departmentid = $department AND tblvacancies.effectivedate <= $dbmaxdate";
  261. } elseif (($department != 0) && ($dbmindate != "")) {
  262. $sql .= "tblvacancies.departmentid = $department AND tblvacancies.effectivedate >= $dbmindate";
  263. } elseif (($faculty != 0) && ($advert != "0")) {
  264. $sql .= "tbldepartment.facultyid = $faculty AND tblvacancies.isadvertised = $advertstatus";
  265. } elseif (($faculty != 0) && ($position != 0)) {
  266. $sql .= "tbldepartment.facultyid = $faculty AND tblvacancies.rankid = $position";
  267. } elseif (($faculty != 0) && ($dbmaxdate != "")) {
  268. $sql .= "tbldepartment.facultyid = $faculty AND tblvacancies.effectivedate <= $dbmaxdate";
  269. } elseif (($faculty != 0) && ($dbmindate != "")) {
  270. $sql .= "tbldepartment.facultyid = $faculty AND tblvacancies.effectivedate >= $dbmindate";
  271. } elseif (($faculty != 0) && ($department != 0)) {
  272. $sql .= "tbldepartment.facultyid = $faculty AND tblvacancies.departmentid = $department";
  273. } elseif ($advert != "0") {
  274. $sql .= "tblvacancies.isadvertised = $advertstatus";
  275. } elseif ($position != 0) {
  276. $sql .= "tblvacancies.rankid = $position";
  277. } elseif ($dbmaxdate != "") {
  278. $sql .= "tblvacancies.effectivedate <= $dbmaxdate";
  279. } elseif ($dbmindate != "") {
  280. $sql .= "tblvacancies.effectivedate >= $dbmindate";
  281. } elseif ($department != 0) {
  282. $sql .= "tblvacancies.departmentid = $department";
  283. } elseif ($faculty != 0) {
  284. $sql .= "tbldepartment.facultyid = $faculty";
  285. }
  286. }
  287. // if ($date != "") {
  288. // $sql = "SELECT tbldepartment.name AS dnm, tblrank.name AS rnm, tblvacancies.vacantslots as vsl, tblvacancies.jobschedule as jsh, tblvacancies.jobshifts as jsf, tblvacancies.status as sts, tblvacancies.vacancyreason as vrs, tblvacancies.isadvertised as adv, tblvacancies.effectivedate as eff FROM tblvacancies INNER JOIN tbldepartment ON (tblvacancies.departmentid = tbldepartment.departmentid) INNER JOIN tblrank ON (tblvacancies.rankid = tblrank.rankid) WHERE tblvacancies.departmentid = $department AND effectivedate >= $dbdate";
  289. // } else {
  290. // $sql = "SELECT tbldepartment.name AS dnm, tblrank.name AS rnm, tblvacancies.vacantslots as vsl, tblvacancies.jobschedule as jsh, tblvacancies.jobshifts as jsf, tblvacancies.status as sts, tblvacancies.vacancyreason as vrs, tblvacancies.isadvertised as adv, tblvacancies.effectivedate as eff FROM tblvacancies INNER JOIN tbldepartment ON (tblvacancies.departmentid = tbldepartment.departmentid) INNER JOIN tblrank ON (tblvacancies.rankid = tblrank.rankid)";
  291. // }
  292. $result = mysql_query($sql);
  293. $num_rows = mysql_num_rows($result);
  294. $output .= "<br /><table border='1' style='width: 100%; font-size: 14px; padding: 2px; margin-bottom: 0'><tr><td style='width:3%; text-align:center; vertical-align:middle'>S/N</td><td style='width:25%; text-align:center; vertical-align:middle'>DEPARTMENT</td><td style='width:30%; text-align:center; vertical-align:middle'>RANK</td><td style='width:5%; text-align:center; vertical-align:middle'>SLOTS</td><td style='width:8%; text-align:center; vertical-align:middle'>SCHEDULE</td><td style='width:5%; text-align:center; vertical-align:middle'>SHIFTS</td><td style='width:9%; text-align:center; vertical-align:middle'>VACANCY</td><td style='width:8%; text-align:center; vertical-align:middle'>REASON</td><td style='width:7%; text-align:center; vertical-align:middle'>ADVERTISED</td><td style='width:15%; text-align:center; vertical-align:middle'>DATE</td></tr>";
  295. $c = 1;
  296. if ($num_rows > 0) {
  297. while ($row = mysql_fetch_assoc($result)) {
  298. $output .= "<tr><td style='width:3%; text-align:center'>".$c."</td><td style='width:25%; text-align:left'>".$row['dnm']."</td><td style='width:30%; text-align:left'>".$row['rnm']."</td><td style='width:5%; text-align:center'>".$row['vsl']."</td><td style='width:8%; text-align:center'>".$row['jsh']."</td><td style='width:5%; text-align:center'>".$row['jsf']."</td><td style='width:9%; text-align:center'>".$row['sts']."</td><td style='width:8%; text-align:center'>".$row['vrs']."</td><td style='width:7%; text-align:center'>".$row['adv']."</td><td style='width:15%; text-align:center'>".$row['eff']."</td></tr>";
  299. $c++;
  300. }
  301. $output .= "</table>";
  302. echo $output;
  303. } else {
  304. $output .= "<tr style='border: none;'><td colspan='10' style='text-align:center'>No vacancy for this search.</td></tr></table>";
  305. echo $output;
  306. }
  307. exit;
  308. }
  309. if (isset($_REQUEST['getadverts']) && isset($_REQUEST['faculty']) && isset($_REQUEST['department']) && isset($_REQUEST['mindate']) && isset($_REQUEST['maxdate']) && isset($_REQUEST['position']) && isset($_REQUEST['advert'])) {
  310. echo Adverts($_REQUEST['faculty'],$_REQUEST['department'],$_REQUEST['mindate'],$_REQUEST['maxdate'],$_REQUEST['position'],$_REQUEST['advert']);
  311. exit;
  312. }
  313. function Adverts($faculty = 0, $department = 0, $mindate = "", $maxdate = "", $position = 0, $advert = 0, $vacancy = "") {
  314. openConnection();
  315. $output = '';
  316. if (($mindate != "") && ($maxdate != "")) {
  317. $mindd = substr($mindate, 3, 2);
  318. $minmm = substr($mindate, 0, 2);
  319. $minyy = substr($mindate, 6, 4);
  320. $dbmindate = "'"."$minyy-$minmm-$mindd"."'";
  321. $maxdd = substr($maxdate, 3, 2);
  322. $maxmm = substr($maxdate, 0, 2);
  323. $maxyy = substr($maxdate, 6, 4);
  324. $dbmaxdate = "'"."$maxyy-$maxmm-$maxdd"."'";
  325. } elseif (($mindate != "") && ($maxdate == "")) {
  326. $mindd = substr($mindate, 3, 2);
  327. $minmm = substr($mindate, 0, 2);
  328. $minyy = substr($mindate, 6, 4);
  329. $dbmindate = "'"."$minyy-$minmm-$mindd"."'";
  330. $dbmaxdate = "";
  331. } elseif (($mindate == "") && ($maxdate != "")) {
  332. $maxdd = substr($maxdate, 3, 2);
  333. $maxmm = substr($maxdate, 0, 2);
  334. $maxyy = substr($maxdate, 6, 4);
  335. $dbmaxdate = "'"."$maxyy-$maxmm-$maxdd"."'";
  336. $dbmindate = "";
  337. } elseif (($mindate == "") && ($maxdate == "")) {
  338. $dbmindate = "";
  339. $dbmaxdate = "";
  340. }
  341. $advertstatus = "'"."$advert"."'";
  342. if (($faculty == 0) && ($department == 0) && ($mindate == '') && ($maxdate == '') && ($position == 0) && ($advert == "0")) {
  343. $sql = "SELECT tbldepartment.name AS dnm, tblrank.name AS rnm, tblvacancies.vacantslots as vsl, tblvacancies.jobschedule as jsh, tblvacancies.jobshifts as jsf, tblvacancies.status as sts, tblvacancies.vacancyreason as vrs, tblvacancies.isadvertised as adv, tblvacancies.effectivedate as eff FROM tblvacancies INNER JOIN tbldepartment ON (tblvacancies.departmentid = tbldepartment.departmentid) INNER JOIN tblfaccentres ON (tbldepartment.facultyid = tblfaccentres.facid) INNER JOIN tblrank ON (tblvacancies.rankid = tblrank.rankid)";
  344. } elseif (($faculty != 0) || ($department != 0) || ($mindate != "") || ($maxdate != "") || ($position != 0) || ($advert != "0")) {
  345. $sql = "SELECT tbldepartment.name AS dnm, tblrank.name AS rnm, tblvacancies.vacantslots as vsl, tblvacancies.jobschedule as jsh, tblvacancies.jobshifts as jsf, tblvacancies.status as sts, tblvacancies.vacancyreason as vrs, tblvacancies.isadvertised as adv, tblvacancies.effectivedate as eff FROM tblvacancies INNER JOIN tbldepartment ON (tblvacancies.departmentid = tbldepartment.departmentid) INNER JOIN tblfaccentres ON (tbldepartment.facultyid = tblfaccentres.facid) INNER JOIN tblrank ON (tblvacancies.rankid = tblrank.rankid) WHERE ";
  346. if (($faculty != 0) && ($department != 0) && ($dbmindate != "") && ($dbmaxdate != "") && ($position != 0) && ($advert != "0")) {
  347. $sql .= "tbldepartment.facultyid = $faculty AND tblvacancies.departmentid = $department AND tblvacancies.effectivedate >= $dbmindate AND tblvacancies.effectivedate <= $dbmaxdate AND tblvacancies.isadvertised = $advertstatus AND tblvacancies.rankid = $position AND tblvacancies.isadvertised = $advertstatus";
  348. } elseif (($department != 0) && ($dbmindate != "") && ($dbmaxdate != "") && ($position != 0) && ($advert != "0")) {
  349. $sql .= "tblvacancies.departmentid = $department AND tblvacancies.effectivedate >= $dbmindate AND tblvacancies.effectivedate <= $dbmaxdate AND tblvacancies.rankid = $position AND tblvacancies.isadvertised = $advertstatus";
  350. } elseif (($faculty != 0) && ($dbmindate != "") && ($dbmaxdate != "") && ($position != 0) && ($advert != "0")) {
  351. $sql .= "tbldepartment.facultyid = $faculty AND tblvacancies.effectivedate >= $dbmindate AND tblvacancies.effectivedate <= $dbmaxdate AND tblvacancies.rankid = $position AND tblvacancies.isadvertised = $advertstatus";
  352. } elseif (($faculty != 0) && ($department != 0) && ($dbmaxdate != "") && ($position != 0) && ($advert != "0")) {
  353. $sql .= "tbldepartment.facultyid = $faculty AND tblvacancies.departmentid = $department AND tblvacancies.effectivedate <= $dbmaxdate AND tblvacancies.rankid = $position AND tblvacancies.isadvertised = $advertstatus";
  354. } elseif (($faculty != 0) && ($department != 0) && ($dbmindate != "") && ($dbmaxdate != "") && ($position != 0)) {
  355. $sql .= "tbldepartment.facultyid = $faculty AND tblvacancies.departmentid = $department AND tblvacancies.effectivedate >= $dbmindate AND tblvacancies.effectivedate <= $dbmaxdate AND tblvacancies.isadvertised = $advertstatus AND tblvacancies.rankid = $position";
  356. } elseif (($dbmindate != "") && ($dbmaxdate != "") && ($position != 0) && ($advert != "0")) {
  357. $sql .= "tblvacancies.effectivedate >= $dbmindate AND tblvacancies.effectivedate <= $dbmaxdate AND tblvacancies.rankid = $position AND tblvacancies.isadvertised = $advertstatus";
  358. } elseif (($department != 0) && ($dbmaxdate != "") && ($position != 0) && ($advert != "0")) {
  359. $sql .= "tblvacancies.departmentid = $department AND tblvacancies.effectivedate <= $dbmaxdate AND tblvacancies.rankid = $position AND tblvacancies.isadvertised = $advertstatus";
  360. } elseif (($department != 0) && ($dbmindate != "") && ($position != "0") && ($advert != "0")) {
  361. $sql .= "tblvacancies.departmentid = $department AND tblvacancies.effectivedate >= $dbmindate AND tblvacancies.rankid = $position AND tblvacancies.isadvertised = $advertstatus";
  362. } elseif (($department != 0) && ($dbmindate != "") && ($dbmaxdate != "") && ($advert != "0")) {
  363. $sql .= "tblvacancies.departmentid = $department AND tblvacancies.effectivedate >= $dbmindate AND tblvacancies.effectivedate <= $dbmaxdate AND tblvacancies.isadvertised = $advertstatus";
  364. } elseif (($department != 0) && ($dbmindate != "") && ($dbmaxdate != "") && ($position != 0)) {
  365. $sql .= "tblvacancies.departmentid = $department AND tblvacancies.effectivedate >= $dbmindate AND tblvacancies.effectivedate <= $dbmaxdate AND tblvacancies.rankid = $position";
  366. } elseif (($faculty != 0) && ($dbmaxdate != "") && ($position != 0) && ($advert != "0")) {
  367. $sql .= "tbldepartment.facultyid = $faculty AND tblvacancies.effectivedate <= $dbmaxdate AND tblvacancies.rankid = $position AND tblvacancies.isadvertised = $advertstatus";
  368. } elseif (($faculty != 0) && ($dbmindate != "") && ($position != 0) && ($advert != "0")) {
  369. $sql .= "tbldepartment.facultyid = $faculty AND tblvacancies.effectivedate >= $dbmindate AND tblvacancies.rankid = $position AND tblvacancies.isadvertised = $advertstatus";
  370. } elseif (($faculty != 0) && ($dbmindate != "") && ($dbmaxdate != "") && ($advert != "0")) {
  371. $sql .= "tbldepartment.facultyid = $faculty AND tblvacancies.effectivedate >= $dbmindate AND tblvacancies.effectivedate <= $dbmaxdate AND tblvacancies.isadvertised = $advertstatus";
  372. } elseif (($faculty != 0) && ($dbmindate != "") && ($dbmaxdate != "") && ($position != 0)) {
  373. $sql .= "tbldepartment.facultyid = $faculty AND tblvacancies.effectivedate >= $dbmindate AND tblvacancies.effectivedate <= $dbmaxdate AND tblvacancies.rankid = $position";
  374. } elseif (($faculty != 0) && ($department != 0) && ($position != 0) && ($advert != "0")) {
  375. $sql .= "tbldepartment.facultyid = $faculty AND tblvacancies.departmentid = $department AND tblvacancies.rankid = $position AND tblvacancies.isadvertised = $advertstatus";
  376. } elseif (($faculty != 0) && ($department != 0) && ($dbmaxdate != "") && ($advert != "0")) {
  377. $sql .= "tbldepartment.facultyid = $faculty AND tblvacancies.departmentid = $department AND tblvacancies.effectivedate <= $dbmaxdate AND tblvacancies.isadvertised = $advertstatus";
  378. } elseif (($faculty != 0) && ($department != 0) && ($dbmaxdate != "") && ($position != 0)) {
  379. $sql .= "tbldepartment.facultyid = $faculty AND tblvacancies.departmentid = $department AND tblvacancies.effectivedate <= $dbmaxdate AND tblvacancies.rankid = $position";
  380. } elseif (($faculty != 0) && ($department != 0) && ($dbmindate != "") && ($advert != "0")) {
  381. $sql .= "tbldepartment.facultyid = $faculty AND tblvacancies.departmentid = $department AND tblvacancies.effectivedate >= $dbmindate AND tblvacancies.isadvertised = $advertstatus";
  382. } elseif (($dbmaxdate != "") && ($position != 0) && ($advert != "0")) {
  383. $sql .= "tblvacancies.effectivedate <= $dbmaxdate AND tblvacancies.rankid = $position AND tblvacancies.isadvertised = $advertstatus";
  384. } elseif (($dbmindate != "") && ($position != 0) && ($advert != "0")) {
  385. $sql .= "tblvacancies.effectivedate >= $dbmindate AND tblvacancies.rankid = $position AND tblvacancies.isadvertised = $advertstatus";
  386. } elseif (($dbmindate != "") && ($dbmaxdate != "") && ($position != 0)) {
  387. $sql .= "tblvacancies.effectivedate >= $dbmindate AND tblvacancies.effectivedate <= $dbmaxdate AND tblvacancies.rankid = $position";
  388. } elseif (($department != 0) && ($position != 0) && ($advert != "0")) {
  389. $sql .= "tblvacancies.departmentid = $department AND tblvacancies.rankid = $position AND tblvacancies.isadvertised = $advertstatus";
  390. } elseif (($department != 0) && ($dbmaxdate != "") && ($advert != 0)) {
  391. $sql .= "tblvacancies.departmentid = $department AND tblvacancies.effectivedate <= $dbmaxdate AND tblvacancies.isadvertised = $advertised";
  392. } elseif (($department != 0) && ($dbmindate != "") && ($advert != "0")) {
  393. $sql .= "tblvacancies.departmentid = $department AND tblvacancies.effectivedate >= $dbmindate AND tblvacancies.isadvertised = $advertstatus";
  394. } elseif (($department != 0) && ($dbmindate != "") && ($position != "0")) {
  395. $sql .= "tblvacancies.departmentid = $department AND tblvacancies.effectivedate >= $dbmindate AND tblvacancies.rankid = $position";
  396. } elseif (($department != 0) && ($dbmindate != "") && ($dbmaxdate != "")) {
  397. $sql .= "tblvacancies.departmentid = $department AND tblvacancies.effectivedate >= $dbmindate AND tblvacancies.effectivedate <= $dbmaxdate ";
  398. } elseif (($faculty != 0) && ($position != 0) && ($advert != "0")) {
  399. $sql .= "tbldepartment.facultyid = $faculty AND tblvacancies.rankid = $position AND tblvacancies.isadvertised = $advertstatus";
  400. } elseif (($faculty != 0) && ($position != 0) && ($advert != "0")) {
  401. $sql .= "tbldepartment.facultyid = $faculty AND tblvacancies.rankid = $position AND tblvacancies.isadvertised = $advertstatus";
  402. } elseif (($faculty != 0) && ($dbmaxdate != "") && ($advert != "0")) {
  403. $sql .= "tbldepartment.facultyid = $faculty AND tblvacancies.effectivedate <= $dbmaxdate AND tblvacancies.isadvertised = $advertstatus";
  404. } elseif (($faculty != 0) && ($dbmaxdate != "") && ($position != 0)) {
  405. $sql .= "tbldepartment.facultyid = $faculty AND tblvacancies.effectivedate <= $dbmaxdate AND tblvacancies.rankid = $position";
  406. } elseif (($faculty != 0) && ($dbmindate != "") && ($advert != "0")) {
  407. $sql .= "tbldepartment.facultyid = $faculty AND tblvacancies.effectivedate >= $dbmindate AND tblvacancies.isadvertised = $advertstatus";
  408. } elseif (($faculty != 0) && ($dbmindate != "") && ($position != 0)) {
  409. $sql .= "tbldepartment.facultyid = $faculty AND tblvacancies.effectivedate >= $dbmindate AND tblvacancies.rankid = $position";
  410. } elseif (($faculty != 0) && ($dbmindate != "") && ($dbmaxdate != "")) {
  411. $sql .= "tbldepartment.facultyid = $faculty AND tblvacancies.effectivedate >= $dbmindate AND tblvacancies.effectivedate <= $dbmaxdate";
  412. } elseif (($faculty != 0) && ($department != 0) && ($advert != "0")) {
  413. $sql .= "tbldepartment.facultyid = $faculty AND tblvacancies.departmentid = $department AND tblvacancies.isadvertised = $advertstatus";
  414. } elseif (($faculty != 0) && ($department != 0) && ($position != 0)) {
  415. $sql .= "tbldepartment.facultyid = $faculty AND tblvacancies.departmentid = $department AND tblvacancies.rankid = $position";
  416. } elseif (($faculty != 0) && ($department != 0) && ($dbmaxdate != "")) {
  417. $sql .= "tbldepartment.facultyid = $faculty AND tblvacancies.departmentid = $department AND tblvacancies.effectivedate <= $dbmaxdate";
  418. } elseif (($faculty != 0) && ($department != 0) && ($dbmaxdate != "")) {
  419. $sql .= "tbldepartment.facultyid = $faculty AND tblvacancies.departmentid = $department AND tblvacancies.effectivedate <= $dbmaxdate";
  420. } elseif (($faculty != 0) && ($department != 0) && ($dbmindate != "")) {
  421. $sql .= "tbldepartment.facultyid = $faculty AND tblvacancies.departmentid = $department AND tblvacancies.effectivedate >= $dbmindate";
  422. } elseif (($position != 0) && ($advert != "0")) {
  423. $sql .= "tblvacancies.rankid = $position AND tblvacancies.isadvertised != 0 AND tblvacancies.isadvertised = $advertstatus";
  424. } elseif (($dbmaxdate != "") && ($advert != "0")) {
  425. $sql .= "tblvacancies.effectivedate <= $dbmaxdate AND tblvacancies.isadvertised = $advertstatus";
  426. } elseif (($dbmaxdate != "") && ($position != 0)) {
  427. $sql .= "tblvacancies.effectivedate <= $dbmaxdate AND tblvacancies.rankid = $position";
  428. } elseif (($dbmindate != "") && ($advert != "0")) {
  429. $sql .= "tblvacancies.effectivedate >= $dbmindate AND tblvacancies.isadvertised = $advertstatus";
  430. } elseif (($dbmindate != "") && ($position != 0)) {
  431. $sql .= "tblvacancies.effectivedate >= $dbmindate AND tblvacancies.rankid = $position";
  432. } elseif (($dbmindate != "") && ($dbmaxdate != "")) {
  433. $sql .= "tblvacancies.effectivedate >= $dbmindate AND tblvacancies.effectivedate <= $dbmaxdate";
  434. } elseif (($department != 0) && ($advert != "0")) {
  435. $sql .= "tblvacancies.departmentid = $department AND tblvacancies.isadvertised = $advertstatus";
  436. } elseif (($department != 0) && ($position != 0)) {
  437. $sql .= "tblvacancies.departmentid = $department AND tblvacancies.rankid = $position";
  438. } elseif (($department != 0) && ($dbmaxdate != "")) {
  439. $sql .= "tblvacancies.departmentid = $department AND tblvacancies.effectivedate <= $dbmaxdate";
  440. } elseif (($department != 0) && ($dbmindate != "")) {
  441. $sql .= "tblvacancies.departmentid = $department AND tblvacancies.effectivedate >= $dbmindate";
  442. } elseif (($faculty != 0) && ($advert != "0")) {
  443. $sql .= "tbldepartment.facultyid = $faculty AND tblvacancies.isadvertised = $advertstatus";
  444. } elseif (($faculty != 0) && ($position != 0)) {
  445. $sql .= "tbldepartment.facultyid = $faculty AND tblvacancies.rankid = $position";
  446. } elseif (($faculty != 0) && ($dbmaxdate != "")) {
  447. $sql .= "tbldepartment.facultyid = $faculty AND tblvacancies.effectivedate <= $dbmaxdate";
  448. } elseif (($faculty != 0) && ($dbmindate != "")) {
  449. $sql .= "tbldepartment.facultyid = $faculty AND tblvacancies.effectivedate >= $dbmindate";
  450. } elseif (($faculty != 0) && ($department != 0)) {
  451. $sql .= "tbldepartment.facultyid = $faculty AND tblvacancies.departmentid = $department";
  452. } elseif ($advert != "0") {
  453. $sql .= "tblvacancies.isadvertised = $advertstatus";
  454. } elseif ($position != 0) {
  455. $sql .= "tblvacancies.rankid = $position";
  456. } elseif ($dbmaxdate != "") {
  457. $sql .= "tblvacancies.effectivedate <= $dbmaxdate";
  458. } elseif ($dbmindate != "") {
  459. $sql .= "tblvacancies.effectivedate >= $dbmindate";
  460. } elseif ($department != 0) {
  461. $sql .= "tblvacancies.departmentid = $department";
  462. } elseif ($faculty != 0) {
  463. $sql .= "tbldepartment.facultyid = $faculty";
  464. }
  465. }
  466. $result = mysql_query($sql);
  467. $num_rows = mysql_num_rows($result);
  468. $output .= "<br /><table border='1' style='width: 100%; font-size: 14px; padding: 2px; margin-bottom: 0;'><tr><td style='width:3%; text-align:center; vertical-align:middle'>S/N</td><td style='width:25%; text-align:center; vertical-align:middle'>DEPARTMENT</td><td style='width:30%; text-align:center; vertical-align:middle'>RANK</td><td style='width:10%; text-align:center; vertical-align:middle'>VACANCY</td><td style='width:8%; text-align:center; vertical-align:middle'>REASON</td><td style='width:8%; text-align:center; vertical-align:middle'>ADVERTISED</td><td style='width:10%; text-align:center; vertical-align:middle'>DATE</td><td style='width:8%; text-align:center; vertical-align:middle'>DETAIL</td><td style='width:8%; text-align:center; vertical-align:middle'>ACTION</td></tr>";
  469. $c = 1;
  470. if ($num_rows > 0) {
  471. while ($row = mysql_fetch_assoc($result)) {
  472. $output .= "<tr><td style='width:3%; text-align:center'>".$c."</td><td style='width:25%; text-align:left'>".$row['dnm']."</td><td style='width:30%; text-align:left'>".$row['rnm']."</td><td style='width:10%; text-align:center'>".$row['sts']."</td><td style='width:8%; text-align:center'>".$row['vrs']."</td><td style='width:8%; text-align:center'>".$row['adv']."</td><td style='width:10%; text-align:center'>".$row['eff']."</td><td style='width:8%; text-align:center'><a href='#'>VIEW</a></td><td style='width:8%; text-align:center'><a href='#'>EDIT</a></td></tr>";
  473. $c++;
  474. }
  475. $output .= "</table>";
  476. echo $output;
  477. } else {
  478. $output .= "<tr style='border: none;'><td colspan='9' style='text-align:center'>No vacancy for this search.</td></tr></table>";
  479. echo $output;
  480. }
  481. exit;
  482. }
  483. if (isset($_REQUEST['getapplicants']) && isset($_REQUEST['fullname'])) {
  484. echo showApplicants($_REQUEST['fullname']);
  485. exit;
  486. }
  487. function showApplicants($fullname = "", $applicants = "") {
  488. openConnection();
  489. searchName($fullname);
  490. }
  491. function searchName($postedname) {
  492. $returnedValue = "<br /><table border='1' style='width: 100%; font-size: 14px; padding: 2px; margin-bottom: 1;'><tr><td style='width:3%; text-align:center; vertical-align:center'>S/N</td><td style='width:30%; text-align:left; vertical-align:middle'>FULL NAME</td><td style='width:30%; text-align:left; vertical-align:middle'>EMAIL</td><td style='width:9%; text-align:left; vertical-align:middle'>BIRTH DATE</td><td style='width:10%; text-align:left; vertical-align:middle'>BIRTH PLACE</td><td style='width:10%; text-align:left; vertical-align:middle'>HOME TOWN</td><td style='width:8%; text-align:center; vertical-align:middle'>ACTION</td></tr>";
  493. $names = explode(" ", $postedname);
  494. $c = 1;
  495. foreach ($names as $nam) {
  496. $name = "'%".$nam."%'";
  497. $sqlsearch = "SELECT tblapplicantsdetails.appaccountrowid as rowid, tblapplicantsdetails.titleid as tid, tblsalutations_lookup.salutation as salute, tblapplicantsdetails.surname as sname, tblapplicantsdetails.firstname as fname, tblapplicantsdetails.othernames as oname, concat(salutation,' ', surname,', ', firstname,' ', othernames) as fullname, tblapplicantsaccount.accountemail as email, tblapplicantsdetails.birthdate as dob, tblapplicantsdetails.birthplace as pob, tblapplicantsdetails.hometown as htw FROM tblapplicantsaccount INNER JOIN tblapplicantsdetails ON (tblapplicantsdetails.appaccountrowid = tblapplicantsaccount.account_rowid) INNER JOIN tblsalutations_lookup ON (tblapplicantsdetails.titleid = tblsalutations_lookup.salutationid) WHERE tblapplicantsdetails.surname LIKE $name OR tblapplicantsdetails.firstname LIKE $name OR tblapplicantsdetails.othernames LIKE $name OR tblsalutations_lookup.salutation LIKE $name";
  498. $querysearch = mysql_query($sqlsearch);
  499. $searchrow = mysql_fetch_assoc($querysearch);
  500. $searchrows = mysql_num_rows($querysearch);
  501. if ($searchrow > 0) {
  502. do {
  503. $returnedValue .= "<tr><td style='width:3%; text-align:center';>".$c."</td><td style='width:30%; text-align:left';>".$searchrow['fullname']."</td><td style='width:30%; text-align:left';>".$searchrow['email']."</td><td style='width:9%; text-align:left';>".$searchrow['dob']."</td><td style='width:10%; text-align:left;'>".$searchrow['pob']."</td><td style='width:10%; text-align:left';>".$searchrow['htw']."</td><td style='width:8%; text-align:center';><a href='profile.php?rid=".$searchrow['rowid']."'>VIEW</a></td></tr>";
  504. $c++;
  505. } while ($searchrow = mysql_fetch_assoc($querysearch));
  506. $searchrows = mysql_num_rows($querysearch);
  507. if ($searchrows > 0) {
  508. mysql_data_seek($querysearch, 0);
  509. $searchrow = mysql_fetch_assoc($querysearch);
  510. }
  511. } else {
  512. $returnedValue = "<br /><table border='1' style='width: 100%; font-size: 14px; padding: 2px; margin-bottom: 1;'><tr><td style='width:100%; text-align:center'; colspan='7'>The search returned no result. Pls be sure of the name to search, then trying again.</td></tr>";
  513. }
  514. }
  515. $returnedValue .= "</table>";
  516. echo $returnedValue;
  517. }
  518. if (isset($_REQUEST['getjobs']) && isset($_REQUEST['jobtitle'])) {
  519. echo showJobs($_REQUEST['jobtitle']);
  520. exit;
  521. }
  522. function showJobs($jobtitle = "", $jobtitles = "") {
  523. openConnection();
  524. searchJobs($jobtitle);
  525. }
  526. function searchJobs($postedname) {
  527. $returnedValue = "<br /><table border='1' style='width: 100%; font-size: 14px; padding: 2px; margin-bottom: 1;'><tr><td style='width:3%; text-align:center; vertical-align:middle'>S/N</td><td style='width:28%; text-align:center; vertical-align:middle'>DEPARTMENT</td><td style='width:31%; text-align:center; vertical-align:middle'>JOB TITLE</td><td style='width:10%; text-align:center; vertical-align:middle'>VACANCY</td><td style='width:8%; text-align:center; vertical-align:middle'>REASON</td><td style='width:10%; text-align:center; vertical-align:middle'>ADVERTISED</td><td style='width:10%; text-align:center; vertical-align:middle'>DATE</td><td style='width:10%; text-align:center; vertical-align:middle'>ACTION</td></tr>";
  528. $jobnames = explode(" ", $postedname);
  529. $c = 1;
  530. foreach ($jobnames as $name) {
  531. $jname = "'%"

Large files files are truncated, but you can click here to view the full file