diff --git a/Layer-0__Site_entry_point/doc/GNUHerds__SQL_Implementation.sql b/Layer-0__Site_entry_point/doc/GNUHerds__SQL_Implementation.sql index 7196a02..5544b18 100644 --- a/Layer-0__Site_entry_point/doc/GNUHerds__SQL_Implementation.sql +++ b/Layer-0__Site_entry_point/doc/GNUHerds__SQL_Implementation.sql @@ -45,7 +45,7 @@ DROP TABLE JS_JobOfferSearches; -- Relations DROP TABLE R0_Qualifications2JobOffersJoins; -- Application state. Resume, that is to say, entities Qualifications to JobOffer relationships. -DROP TABLE R1_Donations2JobOffersJoins; -- Donation pledge groups. +DROP TABLE D1_Donations2JobOffers; -- Donation pledge groups. DROP TABLE E2_EntityFreeSoftwareExperiences; -- Contributions to Free Software projects. It extends the E1_Entities table. DROP TABLE E3_Nationalities; -- List the entity nationalities. It extends the E1_Entities table. DROP TABLE E4_EntityJobLicenseAt; -- List the countries where the entity has license to work. It extends the E1_Entities table. @@ -589,11 +589,17 @@ CREATE TABLE R0_Qualifications2JobOffersJoins ( PRIMARY KEY (R0_J1_Id,R0_E1_Id) ); -CREATE TABLE R1_Donations2JobOffersJoins ( - R1_Id SERIAL PRIMARY KEY, -- Identifier - R1_J1_Id integer REFERENCES J1_JobOffers(J1_Id) NOT NULL, -- Offer identifier - R1_Donation varchar(15) NOT NULL, - R1_E1_Id integer REFERENCES E1_Entities(E1_Id) NOT NULL -- Donator's identity, being a Person, Company or non-profit Organization. +CREATE TABLE D1_Donations2JobOffers ( + -- Identifiers + D1_Id SERIAL PRIMARY KEY, -- Identifier + D1_J1_Id integer REFERENCES J1_JobOffers(J1_Id) NOT NULL, -- Offer identifier + D1_E1_Id integer REFERENCES E1_Entities(E1_Id) NOT NULL, -- Donator's identity, being a Person, Company or non-profit Organization. + -- The donation + D1_Donation varchar(15) NOT NULL, + + -- To confirm donation + D1_DonationMagic varchar(512) DEFAULT NULL, + D1_DonationMagicExpire timestamp NOT NULL DEFAULT 'now' ); diff --git a/Layer-4__DBManager_etc/DB_Manager.php b/Layer-4__DBManager_etc/DB_Manager.php index 4c5fb48..c8eb832 100644 --- a/Layer-4__DBManager_etc/DB_Manager.php +++ b/Layer-4__DBManager_etc/DB_Manager.php @@ -22,6 +22,7 @@ require_once "../Layer-5__DB_operation/Entity.php"; require_once "../Layer-5__DB_operation/Qualifications.php"; require_once "../Layer-5__DB_operation/Job_Offer.php"; require_once "../Layer-5__DB_operation/Alerts.php"; +require_once "../Layer-5__DB_operation/Donation.php"; // Lists require_once "../Layer-5__DB_operation/Countries.php"; @@ -320,24 +321,24 @@ class DBManager { // This method does not need ACL check. It gets public information. - $jobOffer = new JobOffer(); - return $jobOffer->getDonators($Id); + $donation = new Donation(); + return $donation->getDonators($Id); } public function getDonationsForPledgeGroup($Id) { // This method does not need ACL check. It gets public information. - $jobOffer = new JobOffer(); - return $jobOffer->getDonationsForPledgeGroup($Id); + $donation = new Donation(); + return $donation->getDonationsForPledgeGroup($Id); } public function addDonation($Id) { // This method does not need ACL check. Everybody is allowed to donate. - $jobOffer = new JobOffer(); - $jobOffer->addDonation($Id); + $donation = new Donation(); + $donation->addDonation($Id); } public function cancelSelectedDonations() @@ -346,8 +347,8 @@ class DBManager //XXX: TODO: for ($i=0; $i < count($_POST['DeleteJobOffers']); $i++) //XXX: TODO: $acl->checkJobOfferAccess("WRITE",$_POST['DeleteJobOffers'][$i]); - $jobOffer = new JobOffer(); - return $jobOffer->cancelSelectedDonations(); + $donation = new Donation(); + return $donation->cancelSelectedDonations(); } public function getMyDonations() @@ -355,8 +356,8 @@ class DBManager //XXX: TODO: $acl = new AccessControlList(); //XXX: TODO: $acl->checkJobOfferAccess("WRITE",$_POST['DeleteJobOffers'][$i]); - $jobOffer = new JobOffer(); - return $jobOffer->getMyDonations(); + $donation = new Donation(); + return $donation->getMyDonations(); } public function getApplicationsMeterForJobOffer($Id,$meter) @@ -388,8 +389,8 @@ class DBManager { // With the current use of this method, it does not need ACL check. - $jobOffer = new JobOffer(); - return $jobOffer->IsAlreadyDonator($EntityId,$JobOfferId); + $donation = new Donation(); + return $donation->IsAlreadyDonator($EntityId,$JobOfferId); } public function getJobOfferApplications($JobOfferId) diff --git a/Layer-5__DB_operation/Job_Offer.php b/Layer-5__DB_operation/Job_Offer.php index 94302cc..563fd54 100644 --- a/Layer-5__DB_operation/Job_Offer.php +++ b/Layer-5__DB_operation/Job_Offer.php @@ -18,6 +18,7 @@ require_once "../Layer-5__DB_operation/PostgreSQL.php"; // A lot of files from the Layer-5__DB_operation directory are loaded at the Layer-4 DB_Manager.php file. So it is not needed to load it here. +require_once "../Layer-5__DB_operation/Donation.php"; // Methods take the values form the global $_POST[] array. @@ -304,7 +305,10 @@ class JobOffer if ( $offerType == 'Donation pledge group' ) - $this->addDonation($J1_Id); + { + $donation = new Donation(); + $donation->addDonation($J1_Id); + } return $J1_Id; } @@ -502,106 +506,6 @@ class JobOffer $this->postgresql->execute("COMMIT",0); } - - public function getDonators($JobOfferId) - { - $sqlQuery = "PREPARE query(integer) AS SELECT R1_Donation,E1_Email,EP_FirstName,EP_LastName,EP_MiddleName,EC_CompanyName,EO_OrganizationName FROM R1_Donations2JobOffersJoins,E1_Entities WHERE R1_E1_Id=E1_Id AND R1_J1_Id=$1 ; EXECUTE query('$JobOfferId');"; - $result = $this->postgresql->getPostgreSQLObject($sqlQuery,1); - - $array['Donation'] = pg_fetch_all_columns($result, 0); - - $array['Email'] = pg_fetch_all_columns($result, 1); - - $array['FirstName'] = pg_fetch_all_columns($result, 2); - $array['LastName'] = pg_fetch_all_columns($result, 3); - $array['MiddleName'] = pg_fetch_all_columns($result, 4); - - $array['CompanyName'] = pg_fetch_all_columns($result, 5); - - $array['NonprofitName'] = pg_fetch_all_columns($result, 6); - - return $array; - } - - - public function getDonationsForPledgeGroup($JobOfferId) - { - $sqlQuery = "PREPARE query(integer) AS SELECT R1_Donation FROM R1_Donations2JobOffersJoins WHERE R1_J1_Id=$1 ; EXECUTE query('$JobOfferId');"; - $result = $this->postgresql->getPostgreSQLObject($sqlQuery,1); - - $donations = 0; - - foreach (pg_fetch_all_columns($result,0) as $donation) - { - $donations = $donations + $donation; - } - - return $donations; - } - - - public function addDonation($JobOfferId) - { - $entity = new Entity(); - $EntityId = isset($_SESSION['EntityId']) ? trim($_SESSION['EntityId']) : $entity->getEntityId(trim($_POST['Email']),'REQUEST_ADD_DONATION_TO_NOTICE_OPERATION'); // It registers the email and send the verification email if it is needed - - $WageRank = isset($_POST['WageRank']) ? trim($_POST['WageRank']) : ''; - - // We do not increase the value of previous donations. We just add another donation to the notice, with the email - // the user used. No DELETE + INSERT, just INSERT. - // If the user was not logged when [s]he filled the donation then [s]he have to confirm the donation clicking the - // link sent via email. - - $sqlQuery = "PREPARE query(integer,text,integer) AS INSERT INTO R1_Donations2JobOffersJoins (R1_J1_Id,R1_Donation,R1_E1_Id) VALUES ($1,$2,$3); EXECUTE query('$JobOfferId','".pg_escape_string($WageRank)."','$EntityId');"; - $this->postgresql->execute($sqlQuery,1); - } - - - public function cancelSelectedDonations() - { - // Cancel selected donations - for ($i=0; $i < count($_POST['CancelDonations']); $i++) - { - $donationId = $_POST['DonationId'][ $_POST['CancelDonations'][$i] ]; - - $sqlQuery = "PREPARE query(integer) AS DELETE FROM R1_Donations2JobOffersJoins WHERE R1_Id=$1; EXECUTE query('$donationId');"; - $result = $this->postgresql->execute($sqlQuery,1); - } - - // If after the canceling there is not any donation for a donations-pledge-group then auto-delete such donation-pledge-group - for ($i=0; $i < count($_POST['CancelDonations']); $i++) - { - $jobOfferId = $_POST['JobOfferId'][ $_POST['CancelDonations'][$i] ]; - - if ( $already_processed[$jobOfferId] != true ) // Avoid double-delete error - { - $sqlQuery = "PREPARE query(integer) AS SELECT count(*) FROM R1_Donations2JobOffersJoins WHERE R1_J1_Id=$1; EXECUTE query('$jobOfferId');"; - $result = $this->postgresql->getOneField($sqlQuery,1); - - if ( intval($result[0]) == 0 ) - { - $this->deleteJobOffer($jobOfferId); // XXX: We could optimize this calling a custom method due to deleteJobOffer() tries to delete Skills, Language, etc. and DonationPledgeGroups do not use any of such properties. - } - - $already_processed[$jobOfferId] = true; - } - } - } - - - public function getMyDonations() - { - $sqlQuery = "PREPARE query(integer) AS SELECT R1_Id, R1_Donation, R1_J1_Id FROM R1_Donations2JobOffersJoins WHERE R1_E1_Id=$1 ; EXECUTE query('$_SESSION[EntityId]');"; - $result = $this->postgresql->getPostgreSQLObject($sqlQuery,1); - - $array['DonationId'] = pg_fetch_all_columns($result, 0); - $array['Donation'] = pg_fetch_all_columns($result, 1); - $array['DonationPledgeGroupId'] = pg_fetch_all_columns($result, 2); - - return $array; - } - - public function getApplicationsMeterForJobOffer($Id, $meter) { $sqlQuery = "PREPARE query(integer,text) AS SELECT count(R0_E1_Id) FROM R0_Qualifications2JobOffersJoins WHERE R0_J1_Id=$1 AND R0_State=$2; EXECUTE query('$Id','$meter');"; @@ -625,16 +529,6 @@ class JobOffer return false; } - public function IsAlreadyDonator($EntityId,$JobOfferId) - { - $sqlQuery = "PREPARE query(integer,integer) AS SELECT R1_J1_Id FROM R1_Donations2JobOffersJoins WHERE R1_J1_Id=$1 AND R1_E1_Id=$2; EXECUTE query('$JobOfferId','$EntityId');"; - $result = $this->postgresql->getOneField($sqlQuery,1); - if ( is_array($result) and count($result)>=1 ) - return true; - else - return false; - } - public function getJobOfferApplications($JobOfferId) { $array['VacancyTitle'] = $this->makeUp_VacancyTitle($JobOfferId); diff --git a/Layer-5__DB_operation/Donation.php b/Layer-5__DB_operation/Donation.php new file mode 100644 index 0000000..9dac1ec --- /dev/null +++ b/Layer-5__DB_operation/Donation.php @@ -0,0 +1,145 @@ + +// +// 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 . + + +require_once "../Layer-5__DB_operation/PostgreSQL.php"; +// A lot of files from the Layer-5__DB_operation directory are loaded at the Layer-4 DB_Manager.php file. So it is not needed to load it here. + +// Methods take the values form the global $_POST[] array. + + +class Donation +{ + private $postgresql; + + + function __construct() + { + $this->postgresql = new PostgreSQL(); + } + + + public function getDonators($JobOfferId) + { + $sqlQuery = "PREPARE query(integer) AS SELECT D1_Donation,E1_Email,EP_FirstName,EP_LastName,EP_MiddleName,EC_CompanyName,EO_OrganizationName FROM D1_Donations2JobOffers,E1_Entities WHERE D1_E1_Id=E1_Id AND D1_J1_Id=$1 ; EXECUTE query('$JobOfferId');"; + $result = $this->postgresql->getPostgreSQLObject($sqlQuery,1); + + $array['Donation'] = pg_fetch_all_columns($result, 0); + + $array['Email'] = pg_fetch_all_columns($result, 1); + + $array['FirstName'] = pg_fetch_all_columns($result, 2); + $array['LastName'] = pg_fetch_all_columns($result, 3); + $array['MiddleName'] = pg_fetch_all_columns($result, 4); + + $array['CompanyName'] = pg_fetch_all_columns($result, 5); + + $array['NonprofitName'] = pg_fetch_all_columns($result, 6); + + return $array; + } + + + public function getDonationsForPledgeGroup($JobOfferId) + { + $sqlQuery = "PREPARE query(integer) AS SELECT D1_Donation FROM D1_Donations2JobOffers WHERE D1_J1_Id=$1 ; EXECUTE query('$JobOfferId');"; + $result = $this->postgresql->getPostgreSQLObject($sqlQuery,1); + + $donations = 0; + + foreach (pg_fetch_all_columns($result,0) as $donation) + { + $donations = $donations + $donation; + } + + return $donations; + } + + + public function addDonation($JobOfferId) + { + $entity = new Entity(); + $EntityId = isset($_SESSION['EntityId']) ? trim($_SESSION['EntityId']) : $entity->getEntityId(trim($_POST['Email']),'REQUEST_ADD_DONATION_TO_NOTICE_OPERATION'); // It registers the email and send the verification email if it is needed + + $WageRank = isset($_POST['WageRank']) ? trim($_POST['WageRank']) : ''; + + // We do not increase the value of previous donations. We just add another donation to the notice, with the email + // the user used. No DELETE + INSERT, just INSERT. + // If the user was not logged when [s]he filled the donation then [s]he have to confirm the donation clicking the + // link sent via email. + + $sqlQuery = "PREPARE query(integer,text,integer) AS INSERT INTO D1_Donations2JobOffers (D1_J1_Id,D1_Donation,D1_E1_Id) VALUES ($1,$2,$3); EXECUTE query('$JobOfferId','".pg_escape_string($WageRank)."','$EntityId');"; + $this->postgresql->execute($sqlQuery,1); + } + + + public function cancelSelectedDonations() + { + // Cancel selected donations + for ($i=0; $i < count($_POST['CancelDonations']); $i++) + { + $donationId = $_POST['DonationId'][ $_POST['CancelDonations'][$i] ]; + + $sqlQuery = "PREPARE query(integer) AS DELETE FROM D1_Donations2JobOffers WHERE D1_Id=$1; EXECUTE query('$donationId');"; + $result = $this->postgresql->execute($sqlQuery,1); + } + + // If after the canceling there is not any donation for a donations-pledge-group then auto-delete such donation-pledge-group + for ($i=0; $i < count($_POST['CancelDonations']); $i++) + { + $jobOfferId = $_POST['JobOfferId'][ $_POST['CancelDonations'][$i] ]; + + if ( $already_processed[$jobOfferId] != true ) // Avoid double-delete error + { + $sqlQuery = "PREPARE query(integer) AS SELECT count(*) FROM D1_Donations2JobOffers WHERE D1_J1_Id=$1; EXECUTE query('$jobOfferId');"; + $result = $this->postgresql->getOneField($sqlQuery,1); + + if ( intval($result[0]) == 0 ) + { + $this->deleteJobOffer($jobOfferId); // XXX: We could optimize this calling a custom method due to deleteJobOffer() tries to delete Skills, Language, etc. and DonationPledgeGroups do not use any of such properties. + } + + $already_processed[$jobOfferId] = true; + } + } + } + + + public function getMyDonations() + { + $sqlQuery = "PREPARE query(integer) AS SELECT D1_Id, D1_Donation, D1_J1_Id FROM D1_Donations2JobOffers WHERE D1_E1_Id=$1 ; EXECUTE query('$_SESSION[EntityId]');"; + $result = $this->postgresql->getPostgreSQLObject($sqlQuery,1); + + $array['DonationId'] = pg_fetch_all_columns($result, 0); + $array['Donation'] = pg_fetch_all_columns($result, 1); + $array['DonationPledgeGroupId'] = pg_fetch_all_columns($result, 2); + + return $array; + } + + + public function IsAlreadyDonator($EntityId,$JobOfferId) + { + $sqlQuery = "PREPARE query(integer,integer) AS SELECT D1_J1_Id FROM D1_Donations2JobOffers WHERE D1_J1_Id=$1 AND D1_E1_Id=$2; EXECUTE query('$JobOfferId','$EntityId');"; + $result = $this->postgresql->getOneField($sqlQuery,1); + if ( is_array($result) and count($result)>=1 ) + return true; + else + return false; + } +} +?>