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..8becfc3 100644 --- a/Layer-0__Site_entry_point/doc/GNUHerds__SQL_Implementation.sql +++ b/Layer-0__Site_entry_point/doc/GNUHerds__SQL_Implementation.sql @@ -589,11 +589,18 @@ 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 R1_Donations2JobOffers ( + -- Identifiers + R1_Id SERIAL PRIMARY KEY, + R1_J1_Id integer REFERENCES J1_JobOffers(J1_Id) NOT NULL, -- Offer identifier + R1_E1_Id integer REFERENCES E1_Entities(E1_Id) NOT NULL, -- Donator's identity, being a Person, Company or non-profit Organization. + + -- The donation + R1_Donation varchar(15) NOT NULL, + + -- To confirm donation + R1_DonationMagic varchar(512) DEFAULT NULL, + R1_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..36cf2f4 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,16 +321,16 @@ 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) @@ -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/Donation.php b/Layer-5__DB_operation/Donation.php new file mode 100644 index 0000000..10fd05d --- /dev/null +++ b/Layer-5__DB_operation/Donation.php @@ -0,0 +1,143 @@ + +// +// 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 R1_Donation,E1_Email,EP_FirstName,EP_LastName,EP_MiddleName,EC_CompanyName,EO_OrganizationName FROM R1_Donations2JobOffers,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_Donations2JobOffers 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_Donations2JobOffers (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_Donations2JobOffers 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_Donations2JobOffers 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_Donations2JobOffers 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 IsAlreadyDonator($EntityId,$JobOfferId) + { + $sqlQuery = "PREPARE query(integer,integer) AS SELECT R1_J1_Id FROM R1_Donations2JobOffers 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; + } +} +?> diff --git a/Layer-5__DB_operation/Job_Offer.php b/Layer-5__DB_operation/Job_Offer.php index 94302cc..4b8853e 100644 --- a/Layer-5__DB_operation/Job_Offer.php +++ b/Layer-5__DB_operation/Job_Offer.php @@ -503,105 +503,6 @@ class JobOffer } - 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 +526,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);