/Layer-5__DB_operation/Qualifications.php
PHP | 326 lines | 197 code | 76 blank | 53 comment | 8 complexity | 17a553e8a5a8f9bea38aec11dd6e3578 MD5 | raw file
Possible License(s): AGPL-3.0
- <?php
- // Authors: Davi Leal
- //
- // Copyright (C) 2002, 2003, 2004, 2005, 2006, 2007, 2008, 2009 Davi Leal <davi at leals dot com>
- //
- // This program is free software: you can redistribute it and/or modify it under
- // the terms of the GNU Affero General Public License as published by the Free Software Foundation,
- // either version 3 of the License, or (at your option) any later version.
- //
- // This program is distributed in the hope that it will be useful,
- // but WITHOUT ANY WARRANTY; without even the implied
- // warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU Affero
- // General Public License for more details.
- //
- // You should have received a copy of the GNU Affero General Public License along with this
- // program in the COPYING file. If not, see <http://www.gnu.org/licenses/>.
- require_once "../Layer-5__DB_operation/PostgreSQL.php";
- require_once "../Layer-5__DB_operation/FS_Experiences.php";
- // Methods take the values form the global $_POST[] array.
- class Qualifications
- {
- private $postgresql;
- function __construct()
- {
- $this->postgresql = new PostgreSQL();
- }
- public function getQualificationsForEntity($Id)
- {
- $sqlQuery = "PREPARE query(integer) AS SELECT Q1_ProfessionalExperienceSinceYear,QP_LK_DesiredContractType,QP_DesiredWageRank,QP_LU_WageRankCurrency,QP_LB_WageRankByPeriod,QP_CurrentEmployability,QP_AvailableToTravel,QP_AvailableToChangeResidence,Q1_CompletedEdition FROM Q1_Qualifications WHERE Q1_E1_Id=$1; EXECUTE query('$Id');";
- $result = $this->postgresql->getPostgreSQLObject($sqlQuery,1);
- $array = array();
- $array[0] = pg_fetch_all_columns($result, 0); // Q1_ProfessionalExperienceSinceYear
- $array[2] = pg_fetch_all_columns($result, 1); // QP_LK_DesiredContractType
- $array[3] = pg_fetch_all_columns($result, 2); // QP_DesiredWageRank
- $array[4] = pg_fetch_all_columns($result, 3); // QP_LU_WageRankCurrency
- $array[5] = pg_fetch_all_columns($result, 4); // QP_LB_WageRankByPeriod
- $array[6] = pg_fetch_all_columns($result, 5); // QP_CurrentEmployability
- $array[7] = pg_fetch_all_columns($result, 6); // QP_AvailableToTravel
- $array[8] = pg_fetch_all_columns($result, 7); // QP_AvailableToChangeResidence
- $array[10] = pg_fetch_all_columns($result, 8); // Q1_CompletedEdition
- // The curreny name.
- $currencies = new Currencies();
- if (isset($array[4][0]) )
- $array[15] = $currencies->getCurrencyName($array[4][0]);
- else
- $array[15] = '';
- // Profiles
- $productProfiles = new ProductProfiles();
- $array[20] = $productProfiles->getProductProfilesForEntity($Id);
- $professionalProfiles = new ProfessionalProfiles();
- $array[21] = $professionalProfiles->getProfessionalProfilesForEntity($Id);
- $fieldProfiles = new FieldProfiles();
- $array[22] = $fieldProfiles->getFieldProfilesForEntity($Id);
- // Academic
- $academic = new Academic();
- $arrayAC = $academic->getAcademicForEntity($Id);
- $array[40] = $arrayAC[0];
- $array[41] = $arrayAC[1];
- $array[42] = $arrayAC[2];
- $array[43] = $arrayAC[3];
- $array[44] = $arrayAC[4];
- $array[45] = $arrayAC[5];
- $array[46] = $arrayAC[6];
- $array[47] = $arrayAC[7];
- // Certifications
- $certifications = new Certifications();
- $arrayCE = $certifications->getRequestedCertificationsForEntity($Id);
- $array[23] = $arrayCE[0];
- $array[24] = $arrayCE[1];
- // Contributions, FreeSoftwareExperiences
- $freeSoftwareExperiences = new FreeSoftwareExperiences();
- $arrayEX = $freeSoftwareExperiences->getFreeSoftwareExperiencesForEntity($Id);
- $array[25] = $arrayEX[0];
- $array[26] = $arrayEX[1];
- $array[27] = $arrayEX[2];
- // Qualification Languages table
- $languages = new Languages();
- $arrayLL = $languages->getLanguagesForEntity($Id);
- $array[28] = $arrayLL[0];
- $array[29] = $arrayLL[1];
- $array[30] = $arrayLL[2];
- // Qualification Skills table
- $skills = new Skills();
- $arrayLS = $skills->getSkillsForEntity($Id);
- $array[31] = $arrayLS[0];
- $array[32] = $arrayLS[1];
- $array[33] = $arrayLS[2];
- $array[34] = $arrayLS[3];
- return $array;
- }
- public function addQualifications($completedEdition)
- {
- // As there are several tables involved, we use a transaction to be sure, all operations are done, or nothing is done.
- $this->postgresql->execute("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE READ WRITE",0);
- $this->postgresql->execute("BEGIN",0);
- // Qualifications
- $EntityId = isset($_SESSION['EntityId']) ? trim($_SESSION['EntityId']) : '';
- $ProfessionalExperienceSinceYear = isset($_POST['ProfessionalExperienceSinceYear']) ? trim($_POST['ProfessionalExperienceSinceYear']) : '';
- $sqlQuery = "PREPARE query(integer,text) AS INSERT INTO Q1_Qualifications (Q1_E1_Id,Q1_ProfessionalExperienceSinceYear) VALUES ($1,$2); EXECUTE query('$EntityId','".pg_escape_string($ProfessionalExperienceSinceYear)."');";
- $this->postgresql->execute($sqlQuery,1);
- // Profiles
- $productProfiles = new ProductProfiles();
- $productProfiles->setProductProfilesForEntity();
- $professionalProfiles = new ProfessionalProfiles();
- $professionalProfiles->setProfessionalProfilesForEntity();
- $fieldProfiles = new FieldProfiles();
- $fieldProfiles->setFieldProfilesForEntity();
- // Saving only the first section. It is not need to update the Q1_CompletedEdition flag due to it is 'false' by default.
- $this->postgresql->execute("COMMIT",0); // Note: The result is not checked, but any error is managed by the 'query' fuction.
- }
- public function deleteQualifications()
- {
- // Delete the subscriptions to job offers
- $sqlQuery = "PREPARE query(integer) AS DELETE FROM R0_Qualifications2JobOffersJoins WHERE R0_E1_Id=$1; EXECUTE query('$_SESSION[EntityId]');";
- $result = $this->postgresql->execute($sqlQuery,1);
- // NotYetRequestedCertifications to requested certifications
- $certifications = new Certifications();
- $certifications->delRequestedCertificationsForEntity();
- // Profiles
- $productProfiles = new ProductProfiles();
- $productProfiles->delProductProfilesForEntity();
- $professionalProfiles = new ProfessionalProfiles();
- $professionalProfiles->delProfessionalProfilesForEntity();
- $fieldProfiles = new FieldProfiles();
- $fieldProfiles->delFieldProfilesForEntity();
- // Academic table
- $academic = new Academic();
- $academic->delAcademicForEntity();
- // Contributions/FreeSoftwareExperiences
- $freeSoftwareExperiences = new FreeSoftwareExperiences();
- $freeSoftwareExperiences->delFreeSoftwareExperiencesForEntity();
- // Languages table
- $languages = new Languages();
- $languages->delLanguagesForEntity();
- // Skills table
- $skills = new Skills();
- $skills->delSkillsForEntity();
- // Qualifications
- $sqlQuery = "PREPARE query(integer) AS DELETE FROM Q1_Qualifications WHERE Q1_E1_Id=$1; EXECUTE query('$_SESSION[EntityId]');";
- $result = $this->postgresql->execute($sqlQuery,1);
- }
- public function updateQualifications($section,$completedEdition)
- {
- // As there are several tables involved, we use a transaction to be sure, all operations are done, or nothing is done.
- $this->postgresql->execute("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE READ WRITE",0);
- $this->postgresql->execute("BEGIN",0);
- switch($section)
- {
- case 'profiles_etc':
- // Q1_Qualifications table
- $ProfessionalExperienceSinceYear = isset($_POST['ProfessionalExperienceSinceYear']) ? trim($_POST['ProfessionalExperienceSinceYear']) : '';
- $sqlQuery = "PREPARE query(text,bool,integer) AS UPDATE Q1_Qualifications SET Q1_ProfessionalExperienceSinceYear=$1,Q1_CompletedEdition=$2 WHERE Q1_E1_Id=$3; EXECUTE query('".pg_escape_string($ProfessionalExperienceSinceYear)."','$completedEdition','{$_SESSION['EntityId']}');";
- $this->postgresql->execute($sqlQuery,1);
- // Profiles
- $productProfiles = new ProductProfiles();
- $productProfiles->setProductProfilesForEntity();
- $professionalProfiles = new ProfessionalProfiles();
- $professionalProfiles->setProfessionalProfilesForEntity();
- $fieldProfiles = new FieldProfiles();
- $fieldProfiles->setFieldProfilesForEntity();
- break;
- case 'academic':
- // Academic table
- $academic = new Academic();
- $academic->setAcademicForEntity();
- // Update the Q1_CompletedEdition flag
- $sqlQuery = "PREPARE query(bool,integer) AS UPDATE Q1_Qualifications SET Q1_CompletedEdition=$1 WHERE Q1_E1_Id=$2; EXECUTE query('$completedEdition','{$_SESSION['EntityId']}');";
- $this->postgresql->execute($sqlQuery,1);
- break;
- case 'certifications':
- // NotYetRequestedCertifications to requested certifications
- $certifications = new Certifications();
- $certifications->setRequestedCertificationsForEntity();
- // Update the Q1_CompletedEdition flag
- $sqlQuery = "PREPARE query(bool,integer) AS UPDATE Q1_Qualifications SET Q1_CompletedEdition=$1 WHERE Q1_E1_Id=$2; EXECUTE query('$completedEdition','{$_SESSION['EntityId']}');";
- $this->postgresql->execute($sqlQuery,1);
- break;
- case 'projects':
- // Contributions, FreeSoftwareExperiences
- $freeSoftwareExperiences = new FreeSoftwareExperiences();
- $freeSoftwareExperiences->setFreeSoftwareExperiencesForEntity();
- // Update the Q1_CompletedEdition flag
- $sqlQuery = "PREPARE query(bool,integer) AS UPDATE Q1_Qualifications SET Q1_CompletedEdition=$1 WHERE Q1_E1_Id=$2; EXECUTE query('$completedEdition','{$_SESSION['EntityId']}');";
- $this->postgresql->execute($sqlQuery,1);
- break;
- case 'languages':
- // Languages table
- $languages = new Languages();
- $languages->setLanguagesForEntity();
- // Update the Q1_CompletedEdition flag
- $sqlQuery = "PREPARE query(bool,integer) AS UPDATE Q1_Qualifications SET Q1_CompletedEdition=$1 WHERE Q1_E1_Id=$2; EXECUTE query('$completedEdition','{$_SESSION['EntityId']}');";
- $this->postgresql->execute($sqlQuery,1);
- break;
- case 'skills':
- // Skills table
- $skills = new Skills();
- $skills->setSkillsForEntity();
- // Update the Q1_CompletedEdition flag
- $sqlQuery = "PREPARE query(bool,integer) AS UPDATE Q1_Qualifications SET Q1_CompletedEdition=$1 WHERE Q1_E1_Id=$2; EXECUTE query('$completedEdition','{$_SESSION['EntityId']}');";
- $this->postgresql->execute($sqlQuery,1);
- break;
- case 'location':
- // Q1_Qualifications table
- if (isset($_POST['AvailableToTravel']) and $_POST['AvailableToTravel'] == 'on')
- $AvailableToTravel = 'true';
- else $AvailableToTravel = 'false';
- if (isset($_POST['AvailableToChangeResidence']) and $_POST['AvailableToChangeResidence'] == 'on')
- $AvailableToChangeResidence = 'true';
- else $AvailableToChangeResidence = 'false';
- $AvailableToTravel = trim($AvailableToTravel);
- $AvailableToChangeResidence = trim($AvailableToChangeResidence);
- $sqlQuery = "PREPARE query(bool,bool,bool,integer) AS UPDATE Q1_Qualifications SET QP_AvailableToTravel=$1,QP_AvailableToChangeResidence=$2,Q1_CompletedEdition=$3 WHERE Q1_E1_Id=$4; EXECUTE query('$AvailableToTravel','$AvailableToChangeResidence','$completedEdition','{$_SESSION['EntityId']}');";
- $this->postgresql->execute($sqlQuery,1);
- break;
- case 'contract':
- // Q1_Qualifications table
- $DesiredContractType = isset($_POST['DesiredContractType']) ? trim($_POST['DesiredContractType']) : '';
- $DesiredWageRank = isset($_POST['DesiredWageRank']) ? trim($_POST['DesiredWageRank']) : '';
- $WageRankCurrency = isset($_POST['WageRankCurrency']) ? trim($_POST['WageRankCurrency']) : '';
- $WageRankByPeriod = isset($_POST['WageRankByPeriod']) ? trim($_POST['WageRankByPeriod']) : '';
- $CurrentEmployability = isset($_POST['CurrentEmployability']) ? trim($_POST['CurrentEmployability']) : '';
- $sqlQuery = "PREPARE query(text,text,text,text,text,bool,integer) AS UPDATE Q1_Qualifications SET QP_LK_DesiredContractType=$1,QP_DesiredWageRank=$2,QP_LU_WageRankCurrency=$3,QP_LB_WageRankByPeriod=$4,QP_CurrentEmployability=$5,Q1_CompletedEdition=$6 WHERE Q1_E1_Id=$7; EXECUTE query('$DesiredContractType','".pg_escape_string($DesiredWageRank)."','".pg_escape_string($WageRankCurrency)."','$WageRankByPeriod','$CurrentEmployability','$completedEdition','{$_SESSION['EntityId']}');";
- $this->postgresql->execute($sqlQuery,1);
- break;
- default:
- $error = "<p>".$_SERVER["REQUEST_URI"].": ".gettext('ERROR: Unexpected condition')."</p>";
- throw new Exception($error,false);
- }
- $this->postgresql->execute("COMMIT",0);
- }
- // methods to check the Access Control List
- // Check if the request comes from the Qualifications owner
- public function isOwner($E1_Id)
- {
- if ( $_SESSION['EntityId'] == $E1_Id ) // There is not need to query the Data Base :-)
- return true;
- else
- return false;
- }
- // Check if the request comes from an Entity that has a JobOffer with such Qualifications subscribed
- public function isJobOfferApplication($E1_Id)
- {
- $sqlQuery = "PREPARE query(integer,integer) AS SELECT count(*) FROM R0_Qualifications2JobOffersJoins WHERE R0_J1_Id IN ( SELECT J1_Id FROM J1_JobOffers WHERE J1_E1_Id=$1 AND J1_ExpirationDate > 'now' ) AND R0_E1_Id=$2; EXECUTE query('$_SESSION[EntityId]','$E1_Id');"; // AND J1_Closed='f'
- $result = $this->postgresql->getOneField($sqlQuery,1);
- if ( intval($result[0]) >= 1 )
- return true;
- else
- return false;
- }
- }