helpers.php

<?php

#region [Mail Sendings]
function mailToResp($surname, $name, $job){
  global $CONFIG;
  $EMAIL_FORMATION = $CONFIG["EMAIL_FORMATION"];
  $EMAIL_FROM = $CONFIG["EMAIL_FROM"];
  $MAIL_REPLYTO = $CONFIG["MAIL_REPLYTO"];
  $MAIL_CONTACT = $CONFIG['MAIL_CONTACT'];

  $subject = 'Nouvelle Candidature';

  $message = "Candidat: $surname $name\r\n".
      "Profession: $job\r\n".
      "Consulter la candidature sur: https://canap-gest.epfl.ch/";

  $headers = "From: $EMAIL_FROM\r\n" .
      "Content-type: text/plain; charset=utf8\r\n" .
      "Reply-To: $MAIL_REPLYTO\r\n" .
      "X-Mailer: PHP/" . phpversion();

  if (ENVIRONMENT !== 'prod') {
    mail($MAIL_CONTACT, $subject, $message, $headers);
  } else {
    mail($EMAIL_FORMATION, $subject, $message, $headers);
  } 
}

function mailToApprenti($to){
  global $CONFIG;
  $EMAIL_FROM = $CONFIG["EMAIL_FROM"]; 
  $MAIL_REPLYTO = $CONFIG["MAIL_REPLYTO"];
  $MAIL_CONTACT = $CONFIG['MAIL_CONTACT'];

  $subject = 'Votre candidature pour une place d\'apprentissage';

  $message = file_get_contents('./templates/mails/apprenticeMailTemplate.html');

  $headers = "From: $EMAIL_FROM\r\n" .
      "Content-type: text/html; charset=utf8\r\n" .
      "Reply-To: $MAIL_REPLYTO\r\n" .
      "X-Mailer: PHP/" . phpversion();

  if (ENVIRONMENT !== 'prod') {
    mail($MAIL_CONTACT, $subject, $message, $headers);
  } else {
    mail($to, $subject, $message, $headers);
  }
}
#endregion

#region [Folder & Files]
// Crée le dossier annexes
function createFilesFolder ($rootpath, $applicantId) {
  $path = $rootpath . $applicantId . "/";
  if (!mkdir($path, 0777, true)) {
    die('Echec lors de la création du dossier');
  }
  return $path;
}

function uploadFile(&$candidateData, $pathAnnexes, $file, $key, $name){
  $extension = strtolower(strrchr($file['name'], '.'));
  $file['name'] = $name . $extension;
  move_uploaded_file($file['tmp_name'], $pathAnnexes . $file['name']);
  $candidateData->fichiers[$name]['name'] = $file['name'];
}

function uploadAllFiles($pathAnnexes, $postedFiles, $candidateData) {
  uploadFile($candidateData, $pathAnnexes, $postedFiles['photo-passeport'], 'photo-passeport', "photo-passeport");
  uploadFile($candidateData, $pathAnnexes, $postedFiles['carte-identite'], 'carte-identite', "carte-identite");
  uploadFile($candidateData, $pathAnnexes, $postedFiles['curriculum-vitae'], 'curriculum-vitae', "curriculum-vitae");
  uploadFile($candidateData, $pathAnnexes, $postedFiles['lettre-motivation'], 'lettre-motivation', "lettre-motivation");

  for ($i = 1; $i <= 9; $i++){
    if (array_key_exists('annexe'.$i, $postedFiles)){
      if (!($postedFiles['annexe'.$i]['name'] == "")) {
        uploadFile($candidateData, $pathAnnexes, $postedFiles['annexe'.$i], 'annexe'.$i, "annexe".$i);
      }
    }
  }

  if (isset($postedFiles['certificat-gimch'])) {
    uploadFile($candidateData, $pathAnnexes, $postedFiles['certificat-gimch'], 'certificat-gimch', "certificat-gimch");
  }
  if (isset($postedFiles['certificat-gri'])) {
    uploadFile($candidateData, $pathAnnexes, $postedFiles['certificat-gri'], 'certificat-gri', "certificat-gri");
  }

  return $candidateData;
}
#endregion

#region [DB Interactions]
function insertDataIntoDB ($pdo, $candidateData) {

  $id = insertPersonalInfos($pdo, $candidateData);

  insertResponsibles($pdo, $candidateData->representants, $id);
  insertScolarities($pdo, $candidateData->scolarite, $id);
  insertProActivities($pdo, $candidateData->activitesProfessionnelles, $id);
  insertTrainings ($pdo, $candidateData->stages, $id);

  return $id;
}

function insertPersonalInfos ($pdo, $candidateData) {
  $sqlreq = "INSERT INTO applicant
	(applicant_guest_sciper, fk_position, applicant_maturity, applicant_gender, applicant_name, applicant_fsname, applicant_address_street, applicant_address_npa, applicant_home_phone, applicant_personal_phone, applicant_mail, applicant_birthdate, applicant_origin, applicant_nationality, applicant_foreign_authorization, applicant_avs, applicant_main_language, applicant_speaks_french, applicant_speaks_german, applicant_speaks_english, applicant_speaks_other, applicant_has_majority, applicant_scolarity_end, applicant_already_applicant, applicant_already_applicant_year, applicant_application_date, fk_status, fk_applicant_main_responsible, fk_applicant_sec_responsible)
  VALUES (:applicant_guest_sciper, :fk_position, :applicant_maturity, :applicant_gender, :applicant_name, :applicant_fsname, :applicant_address_street, :applicant_address_npa, :applicant_home_phone, :applicant_personal_phone, :applicant_mail, :applicant_birthdate, :applicant_origin, :applicant_nationality, :applicant_foreign_authorization, :applicant_avs, :applicant_main_language, :applicant_speaks_french, :applicant_speaks_german, :applicant_speaks_english, :applicant_speaks_other, :applicant_has_majority, :applicant_scolarity_end, :applicant_already_applicant, :applicant_already_applicant_year, NOW(), 'Nouveau', null, null)";

  $query = $pdo->prepare($sqlreq);
  $query->bindParam(':applicant_guest_sciper', $candidateData->guest_sciper, PDO::PARAM_STR);
  $query->bindParam(':fk_position', $candidateData->formation, PDO::PARAM_INT);
  $query->bindParam(':applicant_maturity', $candidateData->maturite, PDO::PARAM_BOOL);
  $query->bindParam(':applicant_gender', $candidateData->genreApprenti, PDO::PARAM_STR);
  $query->bindParam(':applicant_name', $candidateData->nomApprenti, PDO::PARAM_STR);
  $query->bindParam(':applicant_fsname', $candidateData->prenomApprenti, PDO::PARAM_STR);
  $query->bindParam(':applicant_address_street', $candidateData->addresseApprentiComplete['rue'], PDO::PARAM_STR);
  $query->bindParam(':applicant_address_npa', $candidateData->addresseApprentiComplete['NPA'], PDO::PARAM_STR);
  $query->bindParam(':applicant_home_phone', $candidateData->telFixeApprenti, PDO::PARAM_STR);
  $query->bindParam(':applicant_personal_phone', $candidateData->telMobileApprenti, PDO::PARAM_STR);
  $query->bindParam(':applicant_mail', $candidateData->mailApprenti, PDO::PARAM_STR);
  $query->bindParam(':applicant_birthdate', $candidateData->dateNaissanceApprenti, PDO::PARAM_STR);
  $query->bindParam(':applicant_origin', $candidateData->origineApprenti, PDO::PARAM_STR);
  $query->bindParam(':applicant_nationality', $candidateData->nationaliteApprenti, PDO::PARAM_STR);
  $query->bindParam(':applicant_foreign_authorization', $candidateData->permisEtranger, PDO::PARAM_STR);
  $query->bindParam(':applicant_avs', $candidateData->numeroAVS, PDO::PARAM_STR);
  $query->bindParam(':applicant_main_language', $candidateData->langueMaternelleApprenti, PDO::PARAM_STR);

  $query->bindParam(':applicant_speaks_french', $candidateData->speaksFrench, PDO::PARAM_BOOL);
  $query->bindParam(':applicant_speaks_german', $candidateData->speaksGerman, PDO::PARAM_BOOL);
  $query->bindParam(':applicant_speaks_english', $candidateData->speaksEnglish, PDO::PARAM_BOOL);
  $query->bindParam(':applicant_speaks_other', $candidateData->speaksOther, PDO::PARAM_BOOL);

  $query->bindParam(':applicant_has_majority', $candidateData->majeur, PDO::PARAM_BOOL);
  $query->bindParam(':applicant_scolarity_end', $candidateData->anneeFinScolarite, PDO::PARAM_STR);
  $query->bindParam(':applicant_already_applicant', $candidateData->dejaCandidat, PDO::PARAM_BOOL);
  $query->bindParam(':applicant_already_applicant_year', $candidateData->anneeCandidature, PDO::PARAM_STR);

  $query->execute();
  return $pdo->lastInsertId();
}

function insertResponsibles ($pdo, $representants, $applicantId) {
  $addedRepIds = [];

  // insert responsibles
  $sqlreq = "INSERT INTO responsible (responsible_gender, responsible_name, responsible_fsname, responsible_street, responsible_npa, responsible_phone)
    VALUES (:responsible_gender, :responsible_name, :responsible_fsname, :responsible_street, :responsible_npa, :responsible_phone)";
  foreach ($representants as $rep) {
    if ($rep['nom'] != '') {
      $query = $pdo->prepare($sqlreq);
      $query->bindParam(':responsible_gender', $rep['genre'], PDO::PARAM_STR);
      $query->bindParam(':responsible_name', $rep['nom'], PDO::PARAM_STR);
      $query->bindParam(':responsible_fsname', $rep['prenom'], PDO::PARAM_STR);
      $query->bindParam(':responsible_street', $rep['addresse']['rue'], PDO::PARAM_STR);
      $query->bindParam(':responsible_npa', $rep['addresse']['NPA'], PDO::PARAM_STR);
      $query->bindParam(':responsible_phone', $rep['telephone'], PDO::PARAM_STR);
      $query->execute();
      array_push($addedRepIds, $pdo->lastInsertId());
    }
  }

  // link responsibles
  $main_rep_id = isset($addedRepIds[0]) ? $addedRepIds[0] : null;
  $sec_rep_id = isset($addedRepIds[1]) ? $addedRepIds[1] : null;

  $sqlreq = "UPDATE applicant SET fk_applicant_main_responsible=:fk_applicant_main_responsible, fk_applicant_sec_responsible=:fk_applicant_sec_responsible WHERE applicant_id=:applicant_id";
  $query = $pdo->prepare($sqlreq);
  $query->bindParam(':fk_applicant_main_responsible', $main_rep_id, PDO::PARAM_INT);
  $query->bindParam(':fk_applicant_sec_responsible', $sec_rep_id, PDO::PARAM_INT);
  $query->bindParam(':applicant_id', $applicantId, PDO::PARAM_INT);
  $query->execute();
}

function insertScolarities ($pdo, $scolarities, $applicantId) {
  // insert scolarities
  $sqlreq = "INSERT INTO scolarity (scolarity_school, scolarity_location, scolarity_level, scolarity_years, fk_applicant_id)
    VALUES (:scolarity_school, :scolarity_location, :scolarity_level, :scolarity_years, :fk_applicant_id)";
  foreach ($scolarities as $scolarity) {
    if ($scolarity['ecole'] != '') {
      $query = $pdo->prepare($sqlreq);
      $query->bindParam(':scolarity_school', $scolarity['ecole'], PDO::PARAM_STR);
      $query->bindParam(':scolarity_location', $scolarity['lieu'], PDO::PARAM_STR);
      $query->bindParam(':scolarity_level', $scolarity['niveau'], PDO::PARAM_STR);
      $query->bindParam(':scolarity_years', $scolarity['annees'], PDO::PARAM_STR);
      $query->bindParam(':fk_applicant_id', $applicantId, PDO::PARAM_INT);
      $query->execute();
    }
  }
}

function insertProActivities ($pdo, $activities, $applicantId) {
  // insert professionnal activities
  $sqlreq = "INSERT INTO professional_activity (professional_activity_company, professional_activity_location, professional_activity_activity, professional_activity_years, fk_applicant_id)
    VALUES (:professional_activity_company, :professional_activity_location, :professional_activity_activity, :professional_activity_years, :fk_applicant_id)";
  foreach ($activities as $activity) {
    if ($activity['employeur'] != '') {
      $query = $pdo->prepare($sqlreq);
      $query->bindParam(':professional_activity_company', $activity['employeur'], PDO::PARAM_STR);
      $query->bindParam(':professional_activity_location', $activity['lieu'], PDO::PARAM_STR);
      $query->bindParam(':professional_activity_activity', $activity['activite'], PDO::PARAM_STR);
      $query->bindParam(':professional_activity_years', $activity['annees'], PDO::PARAM_STR);
      $query->bindParam(':fk_applicant_id', $applicantId, PDO::PARAM_INT);
      $query->execute();
    }
  }
}

function insertTrainings ($pdo, $trainings, $applicantId) {
  // insert training / stages
  $sqlreq = "INSERT INTO training (training_job, training_company, fk_applicant_id)
    VALUES (:training_job, :training_company, :fk_applicant_id)";
  foreach ($trainings as $training) {
    if ($training['metier'] != '') {
      $query = $pdo->prepare($sqlreq);
      $query->bindParam(':training_job', $training['metier'], PDO::PARAM_STR);
      $query->bindParam(':training_company', $training['employeur'], PDO::PARAM_STR);
      $query->bindParam(':fk_applicant_id', $applicantId, PDO::PARAM_INT);
      $query->execute();
    }
  }
}

function insertFiles ($pdo, $files, $applicantId, $filesPath) {
  // insert files
  // $userPath = $filesPath . $applicantId . "\\"; WTF is that ???
  $userPath = $filesPath . $applicantId . DIRECTORY_SEPARATOR;
  $sqlreq = "INSERT INTO file (file_name, file_path, fk_applicant_id)
    VALUES (:file_name, :file_path, :fk_applicant_id)";
  foreach ($files as $file) {
    $filePath = $userPath . $file['name'];
    $query = $pdo->prepare($sqlreq);
    $query->bindParam(':file_name', $file['name'], PDO::PARAM_STR);
    $query->bindParam(':file_path', $filePath, PDO::PARAM_STR);
    $query->bindParam(':fk_applicant_id', $applicantId, PDO::PARAM_INT);
    $query->execute();
  }
}

function getPostulationBySciper ($pdo, $sciper) {
  $sqlreq = "SELECT * FROM applicant JOIN position ON position_id = fk_position JOIN location ON fk_location = location_id JOIN job ON fk_job = job_id WHERE applicant_guest_sciper = :sciper";
  $query = $pdo->prepare($sqlreq);
  $query->bindParam(':sciper', $sciper, PDO::PARAM_STR);
  $query->execute();
  return $query->fetchAll();
}

function getResponsibleById ($pdo, $id) {
  $sqlreq = "SELECT * FROM responsible WHERE responsible_id = :id";
  $query = $pdo->prepare($sqlreq);
  $query->bindParam(':id', $id, PDO::PARAM_INT);
  $query->execute();
  return $query->fetchAll();
}

function getScolaritiesById ($pdo, $id) {
  $sqlreq = "SELECT * FROM scolarity WHERE fk_applicant_id = :id";
  $query = $pdo->prepare($sqlreq);
  $query->bindParam(':id', $id, PDO::PARAM_INT);
  $query->execute();
  return $query->fetchAll();
}

function getProActivitiesById ($pdo, $id) {
  $sqlreq = "SELECT * FROM professional_activity WHERE fk_applicant_id = :id";
  $query = $pdo->prepare($sqlreq);
  $query->bindParam(':id', $id, PDO::PARAM_INT);
  $query->execute();
  return $query->fetchAll();
}

function getTrainingById ($pdo, $id) {
  $sqlreq = "SELECT * FROM training WHERE fk_applicant_id = :id";
  $query = $pdo->prepare($sqlreq);
  $query->bindParam(':id', $id, PDO::PARAM_INT);
  $query->execute();
  return $query->fetchAll();
}

function getFilesById ($pdo, $id) {
  $sqlreq = "SELECT * FROM file WHERE fk_applicant_id = :id";
  $query = $pdo->prepare($sqlreq);
  $query->bindParam(':id', $id, PDO::PARAM_INT);
  $query->execute();
  return $query->fetchAll();
}

function getOneFile($pdo, $file_id, $applicant_id) {
  $sqlreq = "SELECT * FROM file WHERE file_id=:file_id AND fk_applicant_id=:applicant_id";
  $query = $pdo->prepare($sqlreq);
  $query->bindParam(':applicant_id', $applicant_id, PDO::PARAM_INT);
  $query->bindParam(':file_id', $file_id, PDO::PARAM_INT);
  $query->execute();
  return $query->fetch();
}

function validFileAccess ($pdo, $id, $sciper) {
  $sqlreq = "SELECT file_name, file_path FROM file INNER JOIN applicant ON file.fk_applicant_id = applicant.applicant_id WHERE applicant.applicant_guest_sciper = :sciper AND file_id = :id";
  $query = $pdo->prepare($sqlreq);
  $query->bindParam(':id', $id, PDO::PARAM_STR);
  $query->bindParam(':sciper', $sciper, PDO::PARAM_STR);
  $query->execute();
  return $query->fetchAll();
}

function isAllInputsEmpty ($inputs) {
  // check if items are empty
  foreach ($inputs as $key => $value) {
    if ($value != "" && $key != 'id') {
      return false;
    }
  }
  return true;
}

function deletePostulation ($pdo, $id, $sciper, $rep1, $rep2) {
  // delete scolarities
  $sqlreq = "DELETE FROM scolarity WHERE fk_applicant_id=:id";
  $query = $pdo->prepare($sqlreq);
  $query->bindParam(':id', $id, PDO::PARAM_STR);
  $query->execute();

  // delete Pro Activities
  $sqlreq = "DELETE FROM professional_activity WHERE fk_applicant_id=:id";
  $query = $pdo->prepare($sqlreq);
  $query->bindParam(':id', $id, PDO::PARAM_STR);
  $query->execute();

  // delete trainings
  $sqlreq = "DELETE FROM training WHERE fk_applicant_id=:id";
  $query = $pdo->prepare($sqlreq);
  $query->bindParam(':id', $id, PDO::PARAM_STR);
  $query->execute();

  // delete files from server
  $filesToRemove = getFilesById($pdo, $id);
  foreach ($filesToRemove as $key => $file) {
    unlink($file['file_path']);
  }
  // delete applicant folder
  $dir = dirname($file['file_path']);
  rmdir($dir);

  // delete files from DB
  $sqlreq = "DELETE FROM file WHERE fk_applicant_id=:id";
  $query = $pdo->prepare($sqlreq);
  $query->bindParam(':id', $id, PDO::PARAM_STR);
  $query->execute();

  // delete applicant personal data
  $sqlreq = "DELETE FROM applicant WHERE applicant_id=:id";
  $query = $pdo->prepare($sqlreq);
  $query->bindParam(':id', $id, PDO::PARAM_STR);
  $query->execute();

  // delete responsibles
  $sqlreq = "DELETE FROM responsible WHERE responsible_id=:id1 OR responsible_id=:id2";
  $query = $pdo->prepare($sqlreq);
  $query->bindParam(':id1', $rep1, PDO::PARAM_STR);
  $query->bindParam(':id2', $rep2, PDO::PARAM_STR);
  $query->execute();

  header('Location: viewpostulation.php');
}

function updatePostulation ($pdo, $posted_data) {

  $posted_files = $posted_data->fichiers;
  // update applicant infos
  $sqlreq = "UPDATE applicant
	SET
		applicant_maturity= :applicant_maturity,
    applicant_gender= :applicant_gender,
		applicant_address_street= :applicant_address_street,
		applicant_address_npa= :applicant_address_npa,
		applicant_home_phone= :applicant_home_phone,
		applicant_personal_phone= :applicant_personal_phone,
    applicant_birthdate= :applicant_birthdate,
		applicant_origin= :applicant_origin,
		applicant_nationality= :applicant_nationality,
		applicant_foreign_authorization= :applicant_foreign_authorization,
		applicant_avs= :applicant_avs,
		applicant_main_language= :applicant_main_language,
    applicant_speaks_french= :applicant_speaks_french,
    applicant_speaks_german= :applicant_speaks_german,
    applicant_speaks_english= :applicant_speaks_english,
    applicant_speaks_other= :applicant_speaks_other,
		applicant_has_majority= :applicant_has_majority,
		applicant_scolarity_end= :applicant_scolarity_end,
    applicant_already_applicant= :applicant_already_applicant,
    applicant_already_applicant_year= :applicant_already_applicant_year,
    applicant_application_updated_date= :applicant_application_updated_date
  WHERE applicant_id=:id";

  $datetime_now = date("Y-m-d H:i:s");

  // TODO: remove useless
  $query = $pdo->prepare($sqlreq);
  $query->bindParam(':id', $posted_data->id, PDO::PARAM_STR);
  $query->bindParam(':applicant_maturity', $posted_data->maturite, PDO::PARAM_BOOL);
  $query->bindParam(':applicant_gender', $posted_data->genreApprenti, PDO::PARAM_STR);
  $query->bindParam(':applicant_address_street', $posted_data->addresseApprentiComplete['rue'], PDO::PARAM_STR);
  $query->bindParam(':applicant_address_npa', $posted_data->addresseApprentiComplete['NPA'], PDO::PARAM_STR);
  $query->bindParam(':applicant_home_phone', $posted_data->telFixeApprenti, PDO::PARAM_STR);
  $query->bindParam(':applicant_personal_phone', $posted_data->telMobileApprenti, PDO::PARAM_STR);
  $query->bindParam(':applicant_birthdate', $posted_data->dateNaissanceApprenti, PDO::PARAM_STR);
  $query->bindParam(':applicant_origin', $posted_data->origineApprenti, PDO::PARAM_STR);
  $query->bindParam(':applicant_nationality', $posted_data->nationaliteApprenti, PDO::PARAM_STR);
  $query->bindParam(':applicant_foreign_authorization', $posted_data->permisEtranger, PDO::PARAM_STR);
  $query->bindParam(':applicant_avs', $posted_data->numeroAVS, PDO::PARAM_STR);
  $query->bindParam(':applicant_main_language', $posted_data->langueMaternelleApprenti, PDO::PARAM_STR);
  $query->bindParam(':applicant_speaks_french', $posted_data->speaksFrench, PDO::PARAM_BOOL);
  $query->bindParam(':applicant_speaks_german', $posted_data->speaksGerman, PDO::PARAM_BOOL);
  $query->bindParam(':applicant_speaks_english', $posted_data->speaksEnglish, PDO::PARAM_BOOL);
  $query->bindParam(':applicant_speaks_other', $posted_data->speaksOther, PDO::PARAM_BOOL);
  $query->bindParam(':applicant_has_majority', $posted_data->majeur, PDO::PARAM_BOOL);
  $query->bindParam(':applicant_already_applicant', $posted_data->dejaCandidat, PDO::PARAM_BOOL);
  $query->bindParam(':applicant_already_applicant_year', $posted_data->anneeCandidature, PDO::PARAM_STR);
  $query->bindParam(':applicant_scolarity_end', $posted_data->anneeFinScolarite, PDO::PARAM_STR);
  $query->bindParam(':applicant_application_updated_date', $datetime_now, PDO::PARAM_STR);
  $query->execute();

  // update responsibles
  $sqlreq = "UPDATE responsible
	SET
    responsible_name= :responsible_name,
    responsible_fsname= :responsible_fsname,
	  responsible_gender= :responsible_gender,
		responsible_street= :responsible_street,
		responsible_npa= :responsible_npa,
		responsible_phone= :responsible_phone
  WHERE responsible_id=:responsible_id";

  if (isset($posted_data->representants[0])) {
    $main_rep = $posted_data->representants[0];
    $query = $pdo->prepare($sqlreq);
    $query->bindParam(':responsible_id', $main_rep['id'], PDO::PARAM_INT);
    $query->bindParam(':responsible_name', $main_rep['nom'], PDO::PARAM_STR);
    $query->bindParam(':responsible_fsname', $main_rep['prenom'], PDO::PARAM_STR);
    $query->bindParam(':responsible_gender', $main_rep['genre'], PDO::PARAM_STR);
    $query->bindParam(':responsible_street', $main_rep['addresse']['rue'], PDO::PARAM_STR);
    $query->bindParam(':responsible_npa', $main_rep['addresse']['NPA'], PDO::PARAM_STR);
    $query->bindParam(':responsible_phone', $main_rep['telephone'], PDO::PARAM_STR);
    $query->execute();
  }

  if (isset($posted_data->representants[1])) {
    $sec_rep = $posted_data->representants[1];
    $query = $pdo->prepare($sqlreq);
    $query->bindParam(':responsible_id', $sec_rep['id'], PDO::PARAM_INT);
    $query->bindParam(':responsible_name', $sec_rep['nom'], PDO::PARAM_STR);
    $query->bindParam(':responsible_fsname', $sec_rep['prenom'], PDO::PARAM_STR);
    $query->bindParam(':responsible_gender', $sec_rep['genre'], PDO::PARAM_STR);
    $query->bindParam(':responsible_street', $sec_rep['addresse']['rue'], PDO::PARAM_STR);
    $query->bindParam(':responsible_npa', $sec_rep['addresse']['NPA'], PDO::PARAM_STR);
    $query->bindParam(':responsible_phone', $sec_rep['telephone'], PDO::PARAM_STR);
    $query->execute();
  }

  // Si majeur, delete les valeurs
  // if ($posted_data->majeur == 1) {
  //   echo $main_rep['id'];
  //   if ($main_rep['id'] != '') {
  //     $sqlreq = "DELETE FROM responsible WHERE responsible_id=:id";
  //     $query = $pdo->prepare($sqlreq);
  //     $query->bindParam(':id1', $main_rep['id'], PDO::PARAM_STR);
  //     $query->execute();
  //   }
  //   if ($sec_rep['id'] != '') {
  //     $sqlreq = "DELETE FROM responsible WHERE responsible_id=:id";
  //     $query = $pdo->prepare($sqlreq);
  //     $query->bindParam(':id', $sec_rep['id'], PDO::PARAM_STR);
  //     $query->execute();
  //   }
  // }

  // Si nouveau, insert
  if (!isset($main_rep['id']) || $main_rep['id'] == '' || !isset($sec_rep['id']) || $sec_rep['id'] == '') {
    insertResponsibles($pdo, [$main_rep, $sec_rep], $posted_data->id);
  }

  // update scolarities
  $to_add = [];
  $sqlreq = "UPDATE scolarity SET scolarity_school=:scolarity_school, scolarity_location=:scolarity_location, scolarity_level=:scolarity_level, scolarity_years=:scolarity_years
  WHERE fk_applicant_id=:applicant_id AND scolarity_id=:scolarity_id";

  foreach ($posted_data->scolarite as $scolarity) {
    if (isset($scolarity['id'])) {
      $query = $pdo->prepare($sqlreq);
      $query->bindParam(':applicant_id', $posted_data->id, PDO::PARAM_INT);
      $query->bindParam(':scolarity_id', $scolarity['id'], PDO::PARAM_INT);
      $query->bindParam(':scolarity_school', $scolarity['ecole'], PDO::PARAM_STR);
      $query->bindParam(':scolarity_location', $scolarity['lieu'], PDO::PARAM_STR);
      $query->bindParam(':scolarity_level', $scolarity['niveau'], PDO::PARAM_STR);
      $query->bindParam(':scolarity_years', $scolarity['annees'], PDO::PARAM_STR);
      $query->execute();
    } else {
      array_push($to_add, $scolarity);
    }
  }
  insertScolarities($pdo, $to_add, $posted_data->id);

  // update pro activities
  $to_add = [];
  $sqlreq = "UPDATE professional_activity SET professional_activity_company=:professional_activity_company, professional_activity_location=:professional_activity_location, professional_activity_activity=:professional_activity_activity, professional_activity_years=:professional_activity_years
  WHERE fk_applicant_id=:applicant_id AND professional_activity_id=:professional_activity_id";

  foreach ($posted_data->activitesProfessionnelles as $activity) {
    if (isset($activity['id'])) {
      if (isAllInputsEmpty($activity)) {
        // data is empty, delete record
        $sqlreq = "DELETE FROM professional_activity WHERE fk_applicant_id=:applicant_id AND professional_activity_id=:professional_activity_id";
        $query = $pdo->prepare($sqlreq);
        $query->bindParam(':applicant_id', $posted_data->id, PDO::PARAM_INT);
        $query->bindParam(':professional_activity_id', $activity['id'], PDO::PARAM_INT);
        $query->execute();
      } else {
        $query = $pdo->prepare($sqlreq);
        $query->bindParam(':applicant_id', $posted_data->id, PDO::PARAM_INT);
        $query->bindParam(':professional_activity_id', $activity['id'], PDO::PARAM_INT);
        $query->bindParam(':professional_activity_company', $activity['employeur'], PDO::PARAM_STR);
        $query->bindParam(':professional_activity_location', $activity['lieu'], PDO::PARAM_STR);
        $query->bindParam(':professional_activity_activity', $activity['activite'], PDO::PARAM_STR);
        $query->bindParam(':professional_activity_years', $activity['annees'], PDO::PARAM_STR);
        $query->execute();
      }
    } else {
      array_push($to_add, $activity);
    }
  }
  insertProActivities($pdo, $to_add, $posted_data->id);

  // update trainings
  $to_add = [];
  $sqlreq = "UPDATE training SET training_job=:training_job, training_company=:training_company
  WHERE fk_applicant_id=:applicant_id AND training_id=:training_id";

  foreach ($posted_data->stages as $training) {
    if (isset($training['id'])) {
      if (isAllInputsEmpty($training)) {
        // data is empty, delete record
        $sqlreq = "DELETE FROM training WHERE fk_applicant_id=:applicant_id AND training_id=:training_id";
        $query = $pdo->prepare($sqlreq);
        $query->bindParam(':applicant_id', $posted_data->id, PDO::PARAM_INT);
        $query->bindParam(':training_id', $training['id'], PDO::PARAM_INT);
        $query->execute();
      } else {
        $query = $pdo->prepare($sqlreq);
        $query->bindParam(':applicant_id', $posted_data->id, PDO::PARAM_INT);
        $query->bindParam(':training_id', $training['id'], PDO::PARAM_INT);
        $query->bindParam(':training_company', $training['metier'], PDO::PARAM_STR);
        $query->bindParam(':training_job', $training['employeur'], PDO::PARAM_STR);
        $query->execute();
      }
    } else {
      array_push($to_add, $training);
    }
  }
  insertTrainings($pdo, $to_add, $posted_data->id);

  // Update files
  $sqlreq = "UPDATE file SET file_path=:file_path, file_name=:file_name
  WHERE fk_applicant_id=:applicant_id AND file_id=:file_id";

  foreach ($posted_files as $key => $file) {
    $file_data = getOneFile($pdo, $file['id'], $posted_data->id);
    $extension = strtolower("." . pathinfo($file['name'])['extension']);
    $filename = $key . $extension;
    $path = preg_replace('/\\.[^.\\s]{3,4}$/', '', $file_data['file_path']) . $extension;
    unlink($file_data['file_path']);

    if (move_uploaded_file($file['tmp_name'], $path)) {
      // success, update db
      $query = $pdo->prepare($sqlreq);
      $query->bindParam(':applicant_id', $posted_data->id, PDO::PARAM_INT);
      $query->bindParam(':file_path', $path, PDO::PARAM_STR);
      $query->bindParam(':file_name', $filename, PDO::PARAM_STR);
      $query->bindParam(':file_id', $file_data['file_id'], PDO::PARAM_STR);
      $query->execute();
    }
  }
}
#endregion
?>