<?php
function setVacancy($title, $position, $organisation, $duties, $accademic, $skills, $notes, $expDate, $creator, $cid, $image, $intro, $experience, $industry){
$query = "INSERT INTO `vacances`(`jobName`, `position`, `organisation`, `duties`, `academic`, `skills`, `notes`, `expDate`, `creator`, `cid`, `image`, `intro`, `experience`, `industry`) 
 VALUES ('$title','$position','$organisation','$duties','$accademic','$skills','$notes','$expDate','$creator','$cid', '$image', '$intro', '$experience', '$industry')";
     exe($query);
}

function  updateVacancy($title, $position, $organisation, $duties, $accademic, $skills, $notes, $expDate, $creator, $cid, $image, $intro, $experience, $industry, $vid){
    $query = "UPDATE `vacances` SET `jobName`='$title',`position`='$position',`organisation`='$organisation',`duties`='$duties',`academic`='$accademic',
    `skills`='$skills',`notes`='$notes',`expDate`='$expDate',`creator`='$creator',`cid`='$cid',`image`='$image',`intro`='$intro',
    `experience`='$experience',`industry`='$industry' WHERE `vid` = '$vid'";
    exe($query);
}

function getVacancy($type){
    if($type == "all"){
    $query = "SELECT * FROM `vacances` ORDER BY `vid` DESC";
    }else if($type == "exp"){
        $date = date("Y-m-d H:i:s");
        $query = "SELECT * FROM `vacances` WHERE `expDate` < '$date'"; 
    }else{
        $date = date("Y-m-d H:i:s");
        $query = "SELECT * FROM `vacances` WHERE `expDate` > '$date'"; 
    }

    $result = exe($query);
    return $result;
}
function clogin($username, $password){
    $query = "SELECT * FROM `candidates` WHERE `email` = '$username' AND `password` = '$password'";
    $result = exe($query);
    if(mysqli_num_rows($result) > 0){
        $candidate = getCanditateByEmail($username);
        $candidateId = $candidate['id'];
        $_SESSION['candidate'] = $candidateId;
        header('location: profile');
    }else{
        array_push($GLOBALS['errors'], "Wrong username or password");
    }
}

function createCanditate($fullname, $email, $password, $password1){
    $fullname = strtoupper($fullname);
    $email =strtolower($email);
    if($password == $password1){
        $query = "INSERT INTO `candidates`(`fullname`, `email`, `password`) 
        VALUES ('$fullname','$email','$password')";
        exe($query);
        $candidate = getCanditateByEmail($email);
        $candidateId = $candidate['id'];
        $_SESSION['candidate'] = $candidateId;
        header('location: profile');
    }
    
}
function isCandidate($email){
    $query = "SELECT * FROM `candidates` WHERE `email` = '$email'";
    $result = exe($query);

    if(!$result){
        return false;
    }else{return true;}
}

function getCanditateByEmail($email){
$query = "SELECT * FROM `candidates` WHERE `email` = '$email'";
$result = exe($query);
$result = mysqli_fetch_array($result);
return $result;
}

function updateBio($name, $email, $tell, $idNo, $nationality, $county, $disability, $gender, $dob, $id, $division, $location, $sublocation,$village, $license, $profession, $code){
    $name = strtoupper($name);
    $email = strtolower($email);
    $division = strtoupper($division);
    $location = strtoupper($location);
    $sublocation = strtoupper($sublocation);
    $village = strtoupper($village);
   $profession = strtoupper($profession);
   $nationality = strtoupper($nationality);
   $county = strtoupper($county);
   $gender = strtoupper($gender);
   $disability = strtoupper($disability);
   $license = strtoupper($license);
   $code = strtoupper($code);
$query = "UPDATE `candidates` SET `fullname`='$name',`email`='$email',`tell`='$tell',
`idNo`='$idNo',`nationality`='$nationality',`county`='$county',`disability`='$disability',`gender`='$gender',
`dob`='$dob', `division`='$division', `location`='$location', `subLocation`='$sublocation', `village`='$village', `drivingLicence`='$license', `profession` = '$profession', `code`=$code WHERE `id` = '$id'";
exe($query);

}

function getCanditateById($candidateId){
$query = "SELECT * FROM `candidates` WHERE `id` = '$candidateId'";
$result = exe($query);
$result = mysqli_fetch_array($result);
return $result;
}
function setAcc($candidateId, $institution, $level, $program, $start, $end, $score){
    //echo $candidateId;
    $institution = strtoupper($institution);
    $level = strtoupper($level);
    $program = strtoupper($program);
$query = "INSERT INTO `academic`(`candidateId`, `institution`, `level`, `program`, `start`, `end`, `score`) 
VALUES ('$candidateId','$institution','$level','$program','$start','$end', '$score')";
exe($query);
}
function getAccademicById($candidateId){
    $query = "SELECT * FROM `academic` WHERE `candidateId` = '$candidateId'";
  return  exe($query);
}
function setExp($candidateId, $employer,$position, $roles, $from, $to){
    $employer = strtoupper($employer);
    $position = strtoupper($position);
    $query = "INSERT INTO `experience`(`candidateId`, `employer`, `position`, `roles`, `start`, `end`) 
    VALUES ('$candidateId','$employer','$position','$roles','$from','$to')";
    exe($query);
}
function getExpById($candidateId){
    $query = "SELECT * FROM `experience` WHERE `candidateId` = '$candidateId'";
    return  exe($query);
}

function getExp($id){
    $query = "SELECT * FROM `experience` WHERE `id` = '$id'";
    $result = exe($query);
    $result = mysqli_fetch_array($result);
    return $result;
}

function setFinal($applicantID, $jobID, $cletter, $salary, $availability, $attachments){
    $query = "INSERT INTO `application`( `applicantID`, `jobID`, `cletter`, `salary`, `availability`, `attachments`) 
    VALUES ('$applicantID','$jobID','$cletter','$salary','$availability','$attachments')";
    exe($query);
    $candidate = getCanditateById($applicantID);
    $job = getJobById($jobID);
    $email = $candidate['email'];
    $jobName = $job['jobName'];
    $subject = "Job Application Confirmation";
    $massage = "Your application for the position of $jobName 

    We have received your application for the position of
   $jobName Your application will be
    reviewed alongside others and only the most qualified
    candidates will be contacted to proceed to the next stage of
    recruitment process.

    We appreciate your interest to work with the Authority.

    Regards,



    Human Resources
    Employee Resourcing";
    @send_mail_by_PHPMailer($email, $subject, $massage, $path=null);
}

function attachments($applicantID){

    $errors= array();
    $file_name = $_FILES['attach1']['name'];
    $file_size = $_FILES['attach1']['size'];
    $file_tmp = $_FILES['attach1']['tmp_name'];
    $file_type = $_FILES['attach1']['type'];
    $file_ext=@strtolower(end(explode('.',$_FILES['attach1']['name'])));
    $docRoot = dirname(__DIR__)."/files";
    
    $expensions= array("jpeg","jpg","png","pdf","doc","docx","xml","xls","apk","mp4","mp3","ini","img","ppt","pps","xls","zip");
    
    if(in_array($file_ext,$expensions)=== false){
      $errors[]="extension not allowed, please choose a PDF, JPEG or PNG file.";
    }
    
    if($file_size > 300971520) {
      $errors[]='File size must be excately 300 MB';
    }
    
    if(empty($errors)==true) {
      move_uploaded_file($file_tmp, $docRoot."/"."cv/".$applicantID.$file_name); //The folder where you would like your file to be saved
      //echo "Success";
    }else{
      print_r($errors);
    }
  //
  //$attach = $docRoot."/"."uploads/".$file_name;
  $attach = "/hrm/files/"."cv/".$applicantID.$file_name;

  return $attach;

}

function getJobById($jobID){
    $query = "SELECT * FROM `vacances` WHERE `vid`= '$jobID'";
    $result =  exe($query);
    $result = mysqli_fetch_array($result);
return $result;
}

function getApplication($applicantID,$jobID){
    $query ="SELECT * FROM `application` WHERE `applicantID`='$applicantID' and `jobID`='$jobID'";
    $result = exe($query);
    return mysqli_fetch_array($result);
}

function getApplicationByUserId($applicantID){
$query = "SELECT * FROM `application` WHERE `applicantID` = '$applicantID'";
return exe($query);

}

function getApplicationByJobId($jobID){

    $query = "SELECT * FROM `application` WHERE `jobID` = '$jobID'";
return exe($query);
}

function shortlist($jobID, $applicantID){
    $query = "INSERT INTO `shortlist`(`jobID`, `applicantID`) 
    VALUES ('$jobID','$applicantID')";
    exe($query);
    
}

function updateStatus($status,$applicantID,$jobID){
    $query = "UPDATE `application` SET `status`='$status' WHERE `applicantID`='$applicantID' and `jobID`='$jobID'";
    exe($query);
}

function shotlistcount($jobID){
    $query = "SELECT COUNT(*) as count FROM `shortlist` WHERE `jobID` = '$jobID'";
    $result =  exe($query);
    $result = mysqli_fetch_array($result);
    return $result['count'];
}

function getShortlistByJobId($jobID){
    $query = "SELECT * FROM `shortlist` WHERE `jobID` = '$jobID'";
    return  exe($query);
}
function getShortlistByID($id){
    $query = "SELECT * FROM `shortlist` WHERE `id` = '$id'";
    $result =  exe($query);
    $result = mysqli_fetch_array($result);
return $result;
}

function updateShortList($part,$value, $id){
    $query = "UPDATE `shortlist` SET `$part` = '$value' WHERE `id`= $id";
    exe($query);
}

function getShortlistByJobIdRanked($jobID){
    $query = "SELECT * FROM `shortlist` WHERE `jobID` = '$jobID' ORDER BY `total` DESC";
    return  exe($query);
}


   
   

function getTotalApplicants($jobID){
    $query = "SELECT count(*) as count FROM `application` WHERE `jobID` = '$jobID'";
    $result =  exe($query);
    $result = mysqli_fetch_array($result);
    return $result['count'];
}
function getTotalShotlisted($jobID){
    $query = "SELECT count(*) as count FROM `shortlist` WHERE `jobID` = '$jobID'";
    $result =  exe($query);
    $result = mysqli_fetch_array($result);
    return $result['count'];
}

function getTotalSelected($jobID){
    $query = "SELECT count(*) as count FROM `shortlist` WHERE `jobID` = '$jobID' AND `status` = 'selected'";
    $result =  exe($query);
    $result = mysqli_fetch_array($result);
    return $result['count'];

}
function getSelected($jobID){
    $query = "SELECT * FROM `shortlist` WHERE `jobID` = '$jobID' AND `status` = 'selected'";
    $result =  exe($query);
    return $result;
}

function updateVacancyStatus($value,$id){
    $query = "UPDATE `vacances` SET `status` = '$value' WHERE `vid` = '$id'";
    exe($query);
}

function  getVacancyPublidhed(){

    $date = date("Y-m-d H:i:s");
    $query = "SELECT * FROM `vacances` WHERE `expDate` > '$date' AND `status` = 'published'"; 
   return exe($query);
}

function setReferee($fullname, $email, $tell, $organisation, $id){
    $query = "INSERT INTO `referrees`(`fullname`, `email`, `tell`, `applicantID`, `organisation`) 
    VALUES ('$fullname','$email','$tell','$id','$organisation')";
    exe($query);

}

function getReferee($applicantID){
    $query = "SELECT * FROM `referrees` WHERE `applicantID`= '$applicantID'";
    return  exe($query);
}

function getRefereeById($id){
    $query = "SELECT * FROM `referrees` WHERE `id`= '$id'";
    $result =  exe($query);
    $result = mysqli_fetch_array($result);
    return $result;

}

function  updatePic($image, $id){
    $query = "UPDATE `candidates` SET `image` = '$image' WHERE `id` = '$id'";
    exe($query);
}

function  getAccById($id){
    $query = "SELECT * FROM `academic` WHERE `id` = '$id'";
    $result =  exe($query);
    $result = mysqli_fetch_array($result);
    return $result;
}

function updateAcc($institution, $level, $program, $start, $end, $score, $id){
$query = "UPDATE `academic` SET `institution`='$institution',`level`='$level',
`program`='$program',`start`='$start',`end`='$end',`score`='$score' WHERE `id`='$id'";
exe($query);

}

function setProf($candidateId, $institution, $level, $program, $start, $Mno){
    //echo $candidateId;
    $institution = strtoupper($institution);
    $level = strtoupper($level);
    $program = strtoupper($program);
$query = "INSERT INTO `profesional`(`candidateId`, `institution`, `level`, `program`, `start`,  `mno`) 
VALUES ('$candidateId','$institution','$level','$program','$start', '$Mno')";
exe($query);
}

function getProfByUserID($applicantID){
    $query="SELECT * FROM `profesional` WHERE `candidateId` = '$applicantID'";
    return  exe($query);
}

function getProfByID($id){
    $query = "SELECT * FROM `profesional` WHERE `id` = '$id'";
    $result =  exe($query);
    $result = mysqli_fetch_array($result);
    return $result; 
}

function updateProf($institution, $level, $program, $start, $Mna, $id){
    $institution = strtoupper($institution);
    $level = strtoupper($level);
    $program = strtoupper($program);

 $query = "UPDATE `profesional` SET `institution`='$institution',`level`='$level',
`program`='$program',`start`='$start',`mno`='$Mna' WHERE `id`='$id'";
exe($query);
}

function setAward($applicantID,$title,$body,$class,$date){
    $query = "INSERT INTO `awards`(`applicantID`, `tile`, `body`, `class`, `dateOfAward`) 
    VALUES ('$applicantID','$title','$body','$class','$date')";
    exe($query);
}

function getAwardByID($applicantID){

    $query="SELECT * FROM `awards` WHERE `applicantID` = '$applicantID'";
    return  exe($query);
}
function forgotPassword($email){
    if(isCandidate($email)){
        $passcode = "#".rand(200,800)."@"."MNc".".".rand(2000,3025);
        $subject = "PASSWORD RESET";
        $massage = "Dear candidate you have chossen to reset your password for the account $email please use $passcode as your one-time password ! there after you can login and change you password";
        send_mail_by_PHPMailer($email, $subject, $massage, $path=null);
        $query = "UPDATE `candidates` SET `password`='$passcode' WHERE `email`='$email'";
        exe($query);
       
    }else{
        array_push($GLOBALS['errors'], "email is not registered");
    }

}
function getTarget($jobID,$target){
    $query = "SELECT * FROM `application` WHERE `jobID`= '$jobID' and `status`='$target'";
    $result = exe($query);
    $emails = array();
    foreach($result as $row){
        $applicantID = $row['applicantID'];
        $candidate = getCanditateById($applicantID);
        $email = $candidate['email'];
        array_push($emails,$email);
    }
    return $emails;
}
function getTarget1($jobID,$target){
    $query = "SELECT * FROM `application` WHERE `jobID`= '$jobID' and `status`='$target'";
    $result = exe($query);
    $tells = array();
    foreach($result as $row){
        $applicantID = $row['applicantID'];
        $candidate = getCanditateById($applicantID);
        $tell = $candidate['tell'];
        array_push($tells,$tell);
    }
    return $tells;
}



?>