/recruitment/reports/vacancyreport.ajax.php

<?php

require_once("../../lib/globals.php");

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



if (isset($_REQUEST['getdepartments']) && isset($_REQUEST['faculty'])) {

    echo showDepartments($_REQUEST['faculty']);

    exit;

}



function showDepartments($faculty = "", $depts = "") {

    openConnection();



	$output = '';



    if ($faculty != "") {

        $sql = "SELECT * FROM `tbldepartment` WHERE facid = $faculty ORDER BY name ASC";

    } else {

        $sql = "SELECT * FROM `tbldepartment`";

    }

	

    $result = mysql_query($sql);



    while ($row = mysql_fetch_array($result)) {

        if ($row['facid'] == $faculty) {

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

        } else {

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

        }

    }

	$output .= "<option selected='selected' value='0'>Show for All</option>";

	echo $output;

}





if (isset($_REQUEST['getvacancies']) && isset($_REQUEST['faculty']) && isset($_REQUEST['department']) && isset($_REQUEST['vacancystatus'])) {

    echo theVacancies($_REQUEST['faculty'],$_REQUEST['department'],$_REQUEST['vacancystatus']);

    exit;

}



if (isset($_REQUEST['getvacancies']) && isset($_REQUEST['faculty']) && isset($_REQUEST['department']) && isset($_REQUEST['vacancystatus'])) {

    echo theVacancies($_REQUEST['faculty'],$_REQUEST['department'],$_REQUEST['vacancystatus']);

    exit;

}



function theVacancies($faculty = "0", $department = "0", $vacancystatus = "", $vacancies = "") {

    openConnection();



	$output = '';

 

	if (($department != 0) && ($department > 0)) {

        $sql1 = "SELECT COUNT(vacancyid) as vacqty FROM tblvacancies INNER JOIN tbldepartment ON (tblvacancies.departmentid = tbldepartment.departmentid) WHERE tblvacancies.departmentid = $department";

        $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";

        $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";

        $sql4 = "SELECT SUM(vacantslots) as empqty, status FROM tblvacancies INNER JOIN tbldepartment ON (tblvacancies.departmentid = tbldepartment.departmentid) WHERE status = 'Occupied' WHERE tbldepartment.departmentid = $department";

	} elseif ($department == 0) {

        $sql1 = "SELECT COUNT(vacancyid) as vacqty FROM tblvacancies INNER JOIN tbldepartment ON (tblvacancies.departmentid = tbldepartment.departmentid)";

        $sql2 = "SELECT COUNT(advertisementid) as advqty FROM tbladvertisements INNER JOIN tblvacancies ON (tbladvertisements.vacancyid = tblvacancies.vacancyid) INNER JOIN tbldepartment ON (tblvacancies.departmentid = tbldepartment.departmentid)";

        $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)";

        $sql4 = "SELECT SUM(vacantslots) as empqty, status FROM tblvacancies INNER JOIN tbldepartment ON (tblvacancies.departmentid = tbldepartment.departmentid) WHERE status = 'Occupied'";

	}



    $result1 = mysql_query($sql1);

    $result2 = mysql_query($sql2);

    $result3 = mysql_query($sql3);

    $result4 = mysql_query($sql4);



    $row1 = mysql_fetch_assoc($result1);

    $row2 = mysql_fetch_assoc($result2);

    $row3 = mysql_fetch_assoc($result3);

    $row4 = mysql_fetch_assoc($result4);



	$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>";



	$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>".$row4['empqty']."</td><td>View</td></tr></table>";



	echo $output;



	exit;

}