1841 lines
72 KiB
PHP
1841 lines
72 KiB
PHP
<?php
|
|
/*
|
|
Crowd Discusses Alternatives is a web application for more organized discussions that help people create alternative solutions, evaluate and rank them.
|
|
|
|
Copyright 2021-2024 Stavros Kalognomos
|
|
|
|
This file is part of Crowd Discusses Alternatives.
|
|
|
|
Crowd Discusses Alternatives 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.
|
|
|
|
Crowd Discusses Alternatives 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 Crowd Discusses Alternatives. If not, see <https://www.gnu.org/licenses/>.
|
|
*/
|
|
class Cda extends Dbh
|
|
{
|
|
protected function getUsername($username)
|
|
{
|
|
$sql = 'SELECT id, user_name FROM users_of_cda
|
|
WHERE user_name = :username;';
|
|
|
|
$params = array(':username' => $username);
|
|
|
|
$stmt = $this->executeStmt($sql, $params);
|
|
$results = $stmt->fetch();
|
|
return $results;
|
|
}
|
|
|
|
protected function getUserCheckPwd($username, $password)
|
|
{
|
|
$sql = 'SELECT id, user_name, psw FROM users_of_cda
|
|
WHERE user_name = :username;';
|
|
|
|
$params = array(':username' => $username);
|
|
|
|
$stmt = $this->executeStmt($sql, $params);
|
|
$results = $stmt->fetch();
|
|
$pwdCheck = false;
|
|
if (!$results) {
|
|
return array('id' => null, 'username' => null, 'pwdCheck' => false);
|
|
} else {
|
|
$pwdCheck = password_verify($password, $results["psw"]);
|
|
return array('id' => $results["id"], 'username' => $results["user_name"], 'pwdCheck' => $pwdCheck);
|
|
}
|
|
}
|
|
|
|
protected function setUser($username, $pwd)
|
|
{
|
|
$hashedPwd = password_hash($pwd, PASSWORD_DEFAULT);
|
|
|
|
$sql = 'INSERT INTO users_of_cda (id, user_name, psw)
|
|
VALUES (LAST_INSERT_ID(), :username, :hashedPwd);';
|
|
|
|
$params = array(':username' => $username, ':hashedPwd' => $hashedPwd);
|
|
|
|
$stmt = $this->executeStmt($sql, $params);
|
|
}
|
|
|
|
protected function addProposalToGroup(int $groupId, int $proposalId)
|
|
{
|
|
$sql = 'INSERT INTO groups_proposals (id, group_id, proposal_id)
|
|
VALUES (LAST_INSERT_ID(), :groupId, :proposalId);';
|
|
|
|
$params = array(':groupId' => $groupId, ':proposalId' => $proposalId);
|
|
$stmt = $this->executeStmt($sql, $params);
|
|
}
|
|
|
|
protected function addReference(int $referenceId, $proposalId, int $topicId)
|
|
{
|
|
if ($proposalId == 0) {
|
|
$proposalId = NULL;
|
|
}
|
|
|
|
$sql = 'INSERT INTO proposals_references (id, topic_id, proposal_id, reference_id)
|
|
VALUES (LAST_INSERT_ID(), :topicId, :proposalId, :referenceId);';
|
|
|
|
$params = array(':topicId' => $topicId, ':proposalId' => $proposalId, ':referenceId' => $referenceId);
|
|
|
|
$stmt = $this->executeStmt($sql, $params);
|
|
}
|
|
|
|
protected function findIdsOfLatestCommentedProposals(int $topicId)
|
|
{
|
|
$sql1 = 'SELECT comments_in_cda.id, category_id, in_category_id
|
|
FROM comments_in_cda
|
|
WHERE topic_id = ? AND category_id = 3';
|
|
$sql2 = ' ORDER BY comments_in_cda.id DESC';
|
|
$sql3 = ' LIMIT 0,100000;';
|
|
$sql = $sql1 . $sql2. $sql3; //first JOIN is for the table in ORDER BY.
|
|
|
|
$params = [$topicId];
|
|
$stmt = $this->execStmtWithBindParam($sql, $params);
|
|
$results = $stmt->fetchAll();
|
|
return $results;
|
|
}
|
|
|
|
protected function findGroupsThatContainSelectedProposals($arrayOfProposalsAsStr)
|
|
{
|
|
$arrayOfIds = $this->convertStrOfIdsToArray($arrayOfProposalsAsStr, ',');
|
|
$arrayOfProposalsAsStr = $this->convertArrayOfIdsToString($arrayOfIds, null, ',');
|
|
$numberOfProposals = count($arrayOfIds);
|
|
|
|
$sql = 'SELECT group_id, COUNT(proposal_id) AS count_p
|
|
FROM `groups_proposals`
|
|
WHERE proposal_id IN (' . $arrayOfProposalsAsStr . ')
|
|
GROUP BY group_id
|
|
HAVING count_p = ?;';
|
|
|
|
$params = [$numberOfProposals];
|
|
$stmt = $this->execStmtWithBindParam($sql, $params);
|
|
$results = $stmt->fetchAll();
|
|
return $results;
|
|
}
|
|
|
|
protected function getAllGroupsOfTopic(int $topicId, $orderByDateOrVotesSumOrVotesNum, $sortByAscOrDesc)
|
|
{
|
|
$dateOrVotes = $this->orderByDateOrVotes($orderByDateOrVotesSumOrVotesNum, 'groups_of_p');
|
|
$ascOrDesc = $this->sortByAscOrDesc($sortByAscOrDesc);
|
|
|
|
$sql1 = 'SELECT groups_of_p.id, group_name, votes_sum_result ,number_of_votes, user_name, user_id, topic_id, groups_of_p.date_time, ballot_box_id, group_str
|
|
FROM groups_of_p
|
|
JOIN ballot_boxes ON groups_of_p.ballot_box_id = ballot_boxes.id
|
|
JOIN users_of_cda ON groups_of_p.user_id = users_of_cda.id
|
|
WHERE topic_id = ?';
|
|
$sql2 = ' ORDER BY id ' . $ascOrDesc . ';'; //first JOIN is for the table in ORDER BY.
|
|
$sql = $sql1 . $sql2;
|
|
|
|
$params = [$topicId];
|
|
$stmt = $this->execStmtWithBindParam($sql, $params);
|
|
$results = $stmt->fetchAll();
|
|
return $results;
|
|
}
|
|
|
|
protected function getAllRecipientsOfMessage($messageId, $sortByAscOrDesc)
|
|
{
|
|
$ascOrDesc = $this->sortByAscOrDesc($sortByAscOrDesc);
|
|
//TO DO: recipient_id can be the id of any user (code must be changed not to write member_id).
|
|
$sql1 = 'SELECT msg_recipients.id, recipient_id AS member_id, user_name
|
|
FROM msg_recipients
|
|
JOIN users_of_cda ON msg_recipients.recipient_id = users_of_cda.id
|
|
WHERE message_id = ?';
|
|
$sql2 = ' ORDER BY users_of_cda.user_name ' . $ascOrDesc . ';'; //first JOIN is for the table in ORDER BY.
|
|
$sql = $sql1 . $sql2;
|
|
|
|
$params = [$messageId];
|
|
$stmt = $this->execStmtWithBindParam($sql, $params);
|
|
$results = $stmt->fetchAll();
|
|
|
|
return $results;
|
|
}
|
|
|
|
protected function getAllTeamMembersIds(int $topicId, $sortByAscOrDesc)
|
|
{
|
|
$ascOrDesc = $this->sortByAscOrDesc($sortByAscOrDesc);
|
|
|
|
$sql1 = 'SELECT member_id
|
|
FROM topics_members
|
|
WHERE topic_id = ?';
|
|
$sql2 = ' ORDER BY member_id ' . $ascOrDesc . ';'; //first JOIN is for the table in ORDER BY.
|
|
$sql = $sql1 . $sql2;
|
|
|
|
$params = [$topicId];
|
|
$stmt = $this->execStmtWithBindParam($sql, $params);
|
|
$results = $stmt->fetchAll();
|
|
return $results;
|
|
}
|
|
|
|
protected function getAllTeamMembersNames(int $topicId, $sortByAscOrDesc)
|
|
{
|
|
$ascOrDesc = $this->sortByAscOrDesc($sortByAscOrDesc);
|
|
|
|
$sql1 = 'SELECT topics_members.id, member_id, user_name
|
|
FROM topics_members
|
|
JOIN users_of_cda ON topics_members.member_id = users_of_cda.id
|
|
WHERE topic_id = ?';
|
|
$sql2 = ' ORDER BY users_of_cda.user_name ' . $ascOrDesc . ';'; //first JOIN is for the table in ORDER BY.
|
|
$sql = $sql1 . $sql2;
|
|
|
|
$params = [$topicId];
|
|
$stmt = $this->execStmtWithBindParam($sql, $params);
|
|
$results = $stmt->fetchAll();
|
|
return $results;
|
|
}
|
|
|
|
protected function getAllTopics($orderByDateOrVotesSumOrVotesNum, $sortByAscOrDesc)
|
|
{
|
|
$dateOrVotes = $this->orderByDateOrVotes($orderByDateOrVotesSumOrVotesNum, 'topics'); //date_time OR votes_sum_result OR number_of_votes.
|
|
|
|
$ascOrDesc = $this->sortByAscOrDesc($sortByAscOrDesc);
|
|
|
|
$sql1 = 'SELECT topics.id, topic, votes_sum_result ,number_of_votes, user_name, topics.date_time
|
|
FROM topics
|
|
JOIN ballot_boxes ON topics.ballot_box_id = ballot_boxes.id
|
|
JOIN users_of_cda ON topics.user_id = users_of_cda.id';
|
|
$sql2 = ' ORDER BY ' . $dateOrVotes . ' ' . $ascOrDesc . ';'; //first JOIN is for the table in ORDER BY.
|
|
$sql = $sql1 . $sql2;
|
|
//$stmt = $this->connect()->query($sql); //works.
|
|
$stmt = $this->executeStmt($sql, null); //also works.
|
|
$results = $stmt->fetchAll();
|
|
return $results;
|
|
}
|
|
|
|
private function orderByDateOrVotes($orderByDateOrVotesSumOrVotesNum, $categ)
|
|
{
|
|
$dateOrVotes = '';
|
|
if ($orderByDateOrVotesSumOrVotesNum == 'VOTES_SUM') {
|
|
$dateOrVotes = 'ballot_boxes.votes_sum_result';
|
|
} elseif ($orderByDateOrVotesSumOrVotesNum == 'VOTES_NUM') {
|
|
$dateOrVotes = 'ballot_boxes.number_of_votes';
|
|
} else {
|
|
$dateOrVotes = $categ . '.id'; //Order by id instead of date.
|
|
}
|
|
|
|
return $dateOrVotes;
|
|
}
|
|
|
|
private function sortByAscOrDesc($sortByAscOrDesc)
|
|
{
|
|
$ascOrDesc = '';
|
|
if ($sortByAscOrDesc == 'DESC') {
|
|
$ascOrDesc = 'DESC';
|
|
} else {
|
|
$ascOrDesc = 'ASC';
|
|
}
|
|
|
|
return $ascOrDesc;
|
|
}
|
|
|
|
protected function getComments($categOfParent, int $parentId, $offset, $numOfRows, $orderByDateOrVotesSumOrVotesNum, $sortByAscOrDesc)
|
|
{
|
|
/*NOTE: TO BE USED IN THE FUTURE IF THERE WILL BE VOTING FOR COMMENTS. $dateOrVotes = $this->orderByDateOrVotes($orderByDateOrVotesSumOrVotesNum, 'comments_in_cda'); //date_time OR votes_sum_result OR number_of_votes.*/
|
|
|
|
$dateOrVotes = 'comments_in_cda.id';
|
|
|
|
$ascOrDesc = $this->sortByAscOrDesc($sortByAscOrDesc);
|
|
|
|
$sql1 = 'SELECT comments_in_cda.id, comment_text, category_id, in_category_id, user_name, user_id, comments_in_cda.date_time
|
|
FROM comments_in_cda
|
|
JOIN users_of_cda ON comments_in_cda.user_id = users_of_cda.id
|
|
WHERE category_id = ? AND in_category_id = ?';
|
|
$sql2 = ' ORDER BY ' . $dateOrVotes . ' ' . $ascOrDesc; //first JOIN is for the table in ORDER BY.
|
|
$sql3 = ' LIMIT ?,?;';
|
|
$sql = $sql1 . $sql2 . $sql3;
|
|
|
|
$params = [$categOfParent, $parentId, $offset, $numOfRows];
|
|
$stmt = $this->execStmtWithBindParam($sql, $params);
|
|
$results = $stmt->fetchAll();
|
|
return $results;
|
|
}
|
|
|
|
protected function getCommentsOfSelectedCategThatContainStr(int $topicId, $stringsToSearchFor, $searchInCateg, $Ofmembers, $sortByAscOrDesc)
|
|
{
|
|
$dateOrVotes = 'comments_in_cda.id';
|
|
$limitOfRows = '1000'; //Change it also in viewcomments.php.
|
|
//$stringsToSearchFor = '%' . $stringsToSearchFor . '%';
|
|
|
|
$ascOrDesc = $this->sortByAscOrDesc($sortByAscOrDesc);
|
|
|
|
if ($searchInCateg == 'proposals' || $searchInCateg == 'p') {
|
|
$searchInCateg = '3';
|
|
} elseif ($searchInCateg == 'groups' || $searchInCateg == 'g') {
|
|
$searchInCateg = '2';
|
|
} else {
|
|
exit("<br><br>-- Error: Category not found!</b>");
|
|
}
|
|
|
|
$sql1 = 'SELECT comments_in_cda.id, comment_text, category_id, in_category_id, user_name, user_id, comments_in_cda.date_time
|
|
FROM comments_in_cda
|
|
JOIN users_of_cda ON comments_in_cda.user_id = users_of_cda.id
|
|
WHERE topic_id = :topicId AND category_id = ' . $searchInCateg;
|
|
|
|
if ($Ofmembers == $_SESSION["userUid"]) {
|
|
$sql2 = ' AND user_id = ' . $_SESSION["userId"];
|
|
} elseif ($Ofmembers == 'any team-member') {
|
|
$sql2 = '';
|
|
} else {
|
|
$selectedMember = $this->getUsername($Ofmembers);
|
|
if ($selectedMember['id'] == null) {
|
|
exit("<br><br><b>-- Error: Username not found. Please check that you have written the username correctly.</b>");
|
|
}
|
|
$sql2 = ' AND user_id = ' . $selectedMember["id"];
|
|
}
|
|
|
|
$sql3 = ' AND comment_text LIKE :stringsToSearchFor';
|
|
//$sql3 = ' AND (SELECT comment_text REGEXP :stringsToSearchFor)';
|
|
|
|
$sql4 = ' ORDER BY ' . $dateOrVotes . ' ' . $ascOrDesc; //first JOIN is for the table in ORDER BY.
|
|
$sql5 = ' LIMIT 0,' . $limitOfRows . ';';
|
|
$sql = $sql1 . $sql2 . $sql3 . $sql4 . $sql5;
|
|
|
|
$params = array(':topicId' => $topicId, ':stringsToSearchFor' => $stringsToSearchFor);
|
|
|
|
$stmt = $this->executeStmt($sql, $params);
|
|
$results = $stmt->fetchAll();
|
|
return $results;
|
|
}
|
|
|
|
protected function getCategIdsThatTheirCommentsContainStr(int $topicId, $stringsToSearchFor, $searchInCateg, $Ofmembers, $sortByAscOrDesc)
|
|
{
|
|
$dateOrVotes = 'in_category_id';
|
|
$limitOfRows = '1000'; //Change it also in viewcomments.php.
|
|
//$stringsToSearchFor = '%' . $stringsToSearchFor . '%';
|
|
|
|
$ascOrDesc = $this->sortByAscOrDesc($sortByAscOrDesc);
|
|
|
|
if ($searchInCateg == 'proposals' || $searchInCateg == 'p') {
|
|
$searchInCateg = '3';
|
|
} elseif ($searchInCateg == 'groups' || $searchInCateg == 'g') {
|
|
$searchInCateg = '2';
|
|
} else {
|
|
exit("<br><br>-- Error: Category not found!</b>");
|
|
}
|
|
|
|
$sql1 = 'SELECT DISTINCT in_category_id
|
|
FROM comments_in_cda
|
|
WHERE topic_id = :topicId AND category_id = ' . $searchInCateg;
|
|
|
|
if ($Ofmembers == 'currentmember') {
|
|
$sql2 = ' AND user_id = ' . $_SESSION["userId"];
|
|
} elseif ($Ofmembers == 'allmembers') {
|
|
$sql2 = '';
|
|
} else {
|
|
exit("<br><br>-- Error: m variable not found!</b>");
|
|
}
|
|
|
|
$sql3 = ' AND comment_text LIKE :stringsToSearchFor';
|
|
//$sql3 = ' AND (SELECT comment_text REGEXP :stringsToSearchFor)';
|
|
|
|
$sql4 = ' ORDER BY ' . $dateOrVotes . ' ' . $ascOrDesc; //first JOIN is for the table in ORDER BY.
|
|
$sql5 = ' LIMIT 0,' . $limitOfRows . ';';
|
|
$sql = $sql1 . $sql2 . $sql3 . $sql4 . $sql5;
|
|
|
|
$params = array(':topicId' => $topicId, ':stringsToSearchFor' => $stringsToSearchFor);
|
|
|
|
$stmt = $this->executeStmt($sql, $params);
|
|
$results = $stmt->fetchAll();
|
|
return $results;
|
|
}
|
|
|
|
protected function getGroups(int $topicId, int $offset, int $numOfRows, $orderByDateOrVotesSumOrVotesNum, $sortByAscOrDesc)
|
|
{
|
|
$dateOrVotes = $this->orderByDateOrVotes($orderByDateOrVotesSumOrVotesNum, 'groups_of_p'); //date_time OR votes_sum_result OR number_of_votes.
|
|
|
|
$ascOrDesc = $this->sortByAscOrDesc($sortByAscOrDesc);
|
|
|
|
$sql1 = 'SELECT groups_of_p.id, group_name, votes_sum_result ,number_of_votes, user_name, user_id, topic_id, groups_of_p.date_time, ballot_box_id, group_str
|
|
FROM groups_of_p
|
|
JOIN ballot_boxes ON groups_of_p.ballot_box_id = ballot_boxes.id
|
|
JOIN users_of_cda ON groups_of_p.user_id = users_of_cda.id
|
|
WHERE topic_id = ?';
|
|
$sql2 = ' ORDER BY ' . $dateOrVotes . ' ' . $ascOrDesc; //first JOIN is for the table in ORDER BY.
|
|
$sql3 = ' LIMIT ?,?;';
|
|
$sql = $sql1 . $sql2 . $sql3;
|
|
|
|
$params = [$topicId, $offset, $numOfRows];
|
|
$stmt = $this->execStmtWithBindParam($sql, $params);
|
|
$results = $stmt->fetchAll();
|
|
return $results;
|
|
}
|
|
|
|
protected function getGroupsByTheirIds($groupsIdsAsStr, $orderByDateOrVotesSumOrVotesNum, $sortByAscOrDesc)
|
|
{
|
|
$arrayOfIds = $this->convertStrOfIdsToArray($groupsIdsAsStr, ',');
|
|
$numOfIdsLimit = 1000;
|
|
if (count($arrayOfIds) > $numOfIdsLimit) {
|
|
exit("<br><br><b>-- Error: Limit of number of IDs exceeded!</b>");
|
|
}
|
|
|
|
$groupsIdsAsStr = $this->convertArrayOfIdsToString($arrayOfIds, null, ',');
|
|
|
|
$dateOrVotes = $this->orderByDateOrVotes($orderByDateOrVotesSumOrVotesNum, 'groups_of_p'); //date_time OR votes_sum_result OR number_of_votes.
|
|
|
|
$ascOrDesc = $this->sortByAscOrDesc($sortByAscOrDesc);
|
|
|
|
$sql1 = 'SELECT groups_of_p.id, group_name, votes_sum_result ,number_of_votes, user_name, user_id, topic_id, groups_of_p.date_time, ballot_box_id, group_str
|
|
FROM groups_of_p
|
|
JOIN ballot_boxes ON groups_of_p.ballot_box_id = ballot_boxes.id
|
|
JOIN users_of_cda ON groups_of_p.user_id = users_of_cda.id
|
|
WHERE groups_of_p.id IN (' . $groupsIdsAsStr . ')';
|
|
$sql2 = ' ORDER BY ' . $dateOrVotes . ' ' . $ascOrDesc . ';'; //first JOIN is for the table in ORDER BY.
|
|
$sql = $sql1 . $sql2;
|
|
|
|
$stmt = $this->executeStmt($sql, null); //also works.
|
|
$results = $stmt->fetchAll();
|
|
return $results;
|
|
}
|
|
|
|
protected function getMembersWithHighScoreInSelectedCateg(int $topicId, $selectedCategForScore, $numberOfMembers)
|
|
{
|
|
switch ($selectedCategForScore) {
|
|
case 'references':
|
|
$selectedCateg = 'score_in_references';
|
|
break;
|
|
|
|
case 'proposals':
|
|
$selectedCateg = 'score_in_proposals';
|
|
break;
|
|
|
|
case 'groups':
|
|
$selectedCateg = 'score_in_groups';
|
|
break;
|
|
|
|
case 'similarities':
|
|
$selectedCateg = 'score_in_similarities';
|
|
break;
|
|
|
|
default:
|
|
return [];
|
|
break;
|
|
}
|
|
|
|
$sql1 = 'SELECT member_id
|
|
FROM topics_members
|
|
WHERE topic_id = ?';
|
|
$sql2 = ' ORDER BY ' . $selectedCateg . ' DESC';
|
|
$sql3 = ' LIMIT 0,?;';
|
|
$sql = $sql1 . $sql2 . $sql3;
|
|
|
|
$params = [$topicId, $numberOfMembers];
|
|
$stmt = $this->execStmtWithBindParam($sql, $params);
|
|
$results = $stmt->fetchAll();
|
|
return $results;
|
|
}
|
|
|
|
protected function getProposals(int $topicId, $offset, $numOfRows, $orderByDateOrVotesSumOrVotesNum, $sortByAscOrDesc)
|
|
{
|
|
$dateOrVotes = $this->orderByDateOrVotes($orderByDateOrVotesSumOrVotesNum, 'proposals'); //date_time OR votes_sum_result OR number_of_votes.
|
|
|
|
$ascOrDesc = $this->sortByAscOrDesc($sortByAscOrDesc);
|
|
|
|
$sql1 = 'SELECT proposals.id, proposal, votes_sum_result ,number_of_votes, user_name, user_id, proposals.date_time, topic_id, ';
|
|
$sql2 = 'SIGN(TO_SECONDS(edit_closing_date)-TO_SECONDS(CURRENT_TIMESTAMP)) AS edit_state, edit_closing_date
|
|
FROM proposals
|
|
JOIN ballot_boxes ON proposals.ballot_box_id = ballot_boxes.id
|
|
JOIN users_of_cda ON proposals.user_id = users_of_cda.id
|
|
WHERE topic_id = ?'; //or SIGN(SUBTIME()) or SIGN(DATEDIFF()).
|
|
$sql3 = ' ORDER BY ' . $dateOrVotes . ' ' . $ascOrDesc; //first JOIN is for the table in ORDER BY.
|
|
$sql4 = ' LIMIT ?,?;';
|
|
$sql = $sql1 . $sql2 . $sql3 . $sql4;
|
|
|
|
$params = [$topicId, $offset, $numOfRows];
|
|
$stmt = $this->execStmtWithBindParam($sql, $params);
|
|
$results = $stmt->fetchAll();
|
|
return $results;
|
|
}
|
|
|
|
protected function getProposalsByTheirIds($proposalsIdsAsStr, $orderByDateOrVotesSumOrVotesNum, $sortByAscOrDesc)
|
|
{
|
|
$arrayOfIds = $this->convertStrOfIdsToArray($proposalsIdsAsStr, ',');
|
|
$groupsIdsAsStr = $this->convertArrayOfIdsToString($arrayOfIds, null, ',');
|
|
|
|
$numOfIdsLimit = 1000;
|
|
if (count($arrayOfIds) > $numOfIdsLimit) {
|
|
exit("<br><br><b>-- Error: Limit of number of IDs exceeded!</b>");
|
|
}
|
|
|
|
$dateOrVotes = $this->orderByDateOrVotes($orderByDateOrVotesSumOrVotesNum, 'proposals'); //date_time OR votes_sum_result OR number_of_votes.
|
|
|
|
$ascOrDesc = $this->sortByAscOrDesc($sortByAscOrDesc);
|
|
|
|
$sql1 = 'SELECT proposals.id, proposal, votes_sum_result ,number_of_votes, user_name, user_id, proposals.date_time, topic_id, ';
|
|
$sql2 = 'SIGN(TO_SECONDS(edit_closing_date)-TO_SECONDS(CURRENT_TIMESTAMP)) AS edit_state, edit_closing_date
|
|
FROM proposals
|
|
JOIN ballot_boxes ON proposals.ballot_box_id = ballot_boxes.id
|
|
JOIN users_of_cda ON proposals.user_id = users_of_cda.id
|
|
WHERE proposals.id IN (' . $groupsIdsAsStr . ')';
|
|
$sql3 = ' ORDER BY ' . $dateOrVotes . ' ' . $ascOrDesc . ';'; //first JOIN is for the table in ORDER BY.
|
|
$sql = $sql1 . $sql2 . $sql3;
|
|
|
|
$stmt = $this->executeStmt($sql, null); //also works.
|
|
$results = $stmt->fetchAll();
|
|
return $results;
|
|
}
|
|
|
|
protected function getProposalsExcluding2ndPsFromSimilaritiesThatUserVoted1(int $topicId, int $userId, $orderByDateOrVotesSumOrVotesNum, $sortByAscOrDesc)
|
|
{
|
|
$limitOfRows = '1000';
|
|
|
|
$dateOrVotes = $this->orderByDateOrVotes($orderByDateOrVotesSumOrVotesNum, 'proposals'); //date_time OR votes_sum_result OR number_of_votes.
|
|
|
|
$ascOrDesc = $this->sortByAscOrDesc($sortByAscOrDesc);
|
|
|
|
$sql4 = 'SELECT proposal_b_id
|
|
FROM ballotboxes_users_votes
|
|
JOIN similar_proposals ON ballotboxes_users_votes.ballot_box_id = similar_proposals.ballot_box_id
|
|
WHERE ballotboxes_users_votes.topic_id = ? AND ballotboxes_users_votes.category_id = 7 AND ballotboxes_users_votes.user_id = ? AND vote = 1';
|
|
|
|
$sql1 = 'SELECT proposals.id, proposal, votes_sum_result ,number_of_votes, user_name, proposals.user_id, proposals.date_time, topic_id, ';
|
|
$sql2 = 'ballot_box_id, SIGN(TO_SECONDS(edit_closing_date)-TO_SECONDS(CURRENT_TIMESTAMP)) AS edit_state, edit_closing_date
|
|
FROM proposals
|
|
JOIN ballot_boxes ON proposals.ballot_box_id = ballot_boxes.id
|
|
JOIN users_of_cda ON proposals.user_id = users_of_cda.id
|
|
WHERE proposals.topic_id = ? AND proposals.id NOT IN (' . $sql4 . ')';
|
|
$sql3 = ' ORDER BY ' . $dateOrVotes . ' ' . $ascOrDesc .
|
|
' LIMIT 0,' . $limitOfRows . ';';
|
|
|
|
$sql = $sql1 . $sql2 . $sql3;
|
|
|
|
$params = [$topicId, $topicId, $userId];
|
|
$stmt = $this->execStmtWithBindParam($sql, $params);
|
|
$results = $stmt->fetchAll();
|
|
return $results;
|
|
}
|
|
|
|
protected function getProposalsOfGroup(int $groupId)
|
|
{
|
|
$sql1 = 'SELECT proposals.id, proposal, votes_sum_result ,number_of_votes, user_name, user_id, proposals.date_time, topic_id, ';
|
|
$sql2 = 'ballot_box_id, SIGN(TO_SECONDS(edit_closing_date)-TO_SECONDS(CURRENT_TIMESTAMP)) AS edit_state, edit_closing_date
|
|
FROM groups_proposals
|
|
JOIN proposals ON groups_proposals.proposal_id = proposals.id
|
|
JOIN ballot_boxes ON proposals.ballot_box_id = ballot_boxes.id
|
|
JOIN users_of_cda ON proposals.user_id = users_of_cda.id';
|
|
|
|
$sql3 = ' WHERE groups_proposals.group_id = ?';
|
|
|
|
$sql4 = ' ORDER BY groups_proposals.proposal_id;';
|
|
$sql = $sql1 . $sql2 . $sql3 . $sql4;
|
|
|
|
$params = [$groupId];
|
|
|
|
$stmt = $this->execStmtWithBindParam($sql, $params);
|
|
$results = $stmt->fetchAll();
|
|
return $results;
|
|
}
|
|
|
|
protected function getProposalsOfSelectedMembers(int $topicId, $membersIdsAsString, $orderByDateOrVotesSumOrVotesNum, $sortByAscOrDesc)
|
|
{
|
|
$arrayOfIds = $this->convertStrOfIdsToArray($membersIdsAsString, ',');
|
|
$numOfIdsLimit = 1000;
|
|
if (count($arrayOfIds) > $numOfIdsLimit) {
|
|
exit("<br><br><b>-- Error: Limit of number of IDs exceeded!</b>");
|
|
}
|
|
|
|
$membersIdsAsString = $this->convertArrayOfIdsToString($arrayOfIds, null, ',');
|
|
|
|
$dateOrVotes = $this->orderByDateOrVotes($orderByDateOrVotesSumOrVotesNum, 'proposals'); //date_time OR votes_sum_result OR number_of_votes.
|
|
|
|
$ascOrDesc = $this->sortByAscOrDesc($sortByAscOrDesc);
|
|
|
|
$sql1 = 'SELECT proposals.id, proposal, votes_sum_result ,number_of_votes, user_name, user_id, proposals.date_time, topic_id, ';
|
|
$sql2 = 'ballot_box_id, SIGN(TO_SECONDS(edit_closing_date)-TO_SECONDS(CURRENT_TIMESTAMP)) AS edit_state, edit_closing_date
|
|
FROM proposals
|
|
JOIN ballot_boxes ON proposals.ballot_box_id = ballot_boxes.id
|
|
JOIN users_of_cda ON proposals.user_id = users_of_cda.id
|
|
WHERE topic_id = ? AND user_id IN (' . $membersIdsAsString . ')';
|
|
$sql3 = ' ORDER BY ' . $dateOrVotes . ' ' . $ascOrDesc . ';';
|
|
$sql = $sql1 . $sql2 . $sql3;
|
|
|
|
$params = [$topicId];
|
|
$stmt = $this->execStmtWithBindParam($sql, $params);
|
|
$results = $stmt->fetchAll();
|
|
return $results;
|
|
}
|
|
|
|
protected function getProposalsThatUserVoted1(int $topicId, int $userId)
|
|
{
|
|
$limitOfRows = '10000';
|
|
|
|
$sql = 'SELECT proposals.id, proposal, votes_sum_result ,number_of_votes, user_name, ballotboxes_users_votes.user_id, proposals.date_time, ballotboxes_users_votes.topic_id, ballotboxes_users_votes.ballot_box_id, SIGN(TO_SECONDS(edit_closing_date)-TO_SECONDS(CURRENT_TIMESTAMP)) AS edit_state, edit_closing_date
|
|
FROM ballotboxes_users_votes
|
|
JOIN proposals ON ballotboxes_users_votes.ballot_box_id = proposals.ballot_box_id
|
|
JOIN ballot_boxes ON ballotboxes_users_votes.ballot_box_id = ballot_boxes.id
|
|
JOIN users_of_cda ON ballotboxes_users_votes.user_id = users_of_cda.id
|
|
WHERE ballotboxes_users_votes.topic_id = ? AND ballotboxes_users_votes.category_id = 3 AND ballotboxes_users_votes.user_id = ? AND vote = 1
|
|
ORDER BY ballotboxes_users_votes.ballot_box_id ASC
|
|
LIMIT 0,'. $limitOfRows .';';
|
|
|
|
$params = [$topicId, $userId];
|
|
$stmt = $this->execStmtWithBindParam($sql, $params);
|
|
$results = $stmt->fetchAll();
|
|
return $results;
|
|
}
|
|
|
|
protected function getReceivedMessagesFromUser(int $offset, int $numOfRows, $sortByAscOrDesc)
|
|
{
|
|
$ascOrDesc = $this->sortByAscOrDesc($sortByAscOrDesc);
|
|
|
|
$sql1 = 'SELECT messages.id, message, user_name AS sender_name, sender_id, messages.date_time, reply_to_msg
|
|
FROM msg_recipients
|
|
JOIN messages ON msg_recipients.message_id = messages.id
|
|
JOIN users_of_cda ON messages.sender_id = users_of_cda.id
|
|
WHERE recipient_id = ?';
|
|
$sql2 = ' ORDER BY messages.id ' . $ascOrDesc;
|
|
$sql3 = ' LIMIT ?,?;';
|
|
$sql = $sql1 . $sql2 . $sql3;
|
|
|
|
$params = [$_SESSION['userId'], $offset, $numOfRows];
|
|
$stmt = $this->execStmtWithBindParam($sql, $params);
|
|
$results = $stmt->fetchAll();
|
|
|
|
return $results;
|
|
}
|
|
|
|
protected function getReferences($parentCateg, int $parentId, int $offset, int $numOfRows, $orderByDateOrVotesSumOrVotesNum, $sortByAscOrDesc)
|
|
{
|
|
$dateOrVotes = $this->orderByDateOrVotes($orderByDateOrVotesSumOrVotesNum, 'references_of_p'); //date_time OR votes_sum_result OR number_of_votes.
|
|
|
|
$ascOrDesc = $this->sortByAscOrDesc($sortByAscOrDesc);
|
|
|
|
$sql1 = 'SELECT references_of_p.id, ref_description, reference_text, votes_sum_result, number_of_votes, user_name, user_id, references_of_p.date_time, references_of_p.proposal_id, references_of_p.topic_id, ballot_box_id
|
|
FROM references_of_p
|
|
JOIN ballot_boxes ON references_of_p.ballot_box_id = ballot_boxes.id
|
|
JOIN users_of_cda ON references_of_p.user_id = users_of_cda.id';
|
|
|
|
if ($parentCateg !== null) {
|
|
if ($parentCateg === 'topic') { //Show references in a specific topic.
|
|
$sql2 = '';
|
|
$sql3 = ' WHERE references_of_p.topic_id = ?';
|
|
} elseif ($parentCateg === 'proposal') { //Show references of a specific proposal.
|
|
$sql2 = ' WHERE references_of_p.id IN (
|
|
SELECT reference_id
|
|
FROM proposals_references';
|
|
$sql3 = ' WHERE proposals_references.proposal_id = ?)'; //There is no need for SELECT DISTINCT since the changes in tables references_of_p and proposals_references!
|
|
} else {
|
|
return null;
|
|
}
|
|
} else { //Show references in CDA.
|
|
$sql2 = '';
|
|
$sql3 = '';
|
|
}
|
|
|
|
$sql4 = ' ORDER BY ' . $dateOrVotes . ' ' . $ascOrDesc; //first JOIN is for the table in ORDER BY.
|
|
$sql5 = ' LIMIT ?,?;';
|
|
$sql = $sql1 . $sql2 . $sql3 . $sql4 . $sql5;
|
|
|
|
if ($parentCateg !== null) {
|
|
$params = [$parentId, $offset, $numOfRows];
|
|
} else { //Show references in CDA.
|
|
$params = [$offset, $numOfRows];
|
|
}
|
|
|
|
$stmt = $this->execStmtWithBindParam($sql, $params);
|
|
$results = $stmt->fetchAll();
|
|
return $results;
|
|
}
|
|
|
|
protected function getScore(int $topicId, int $authorsId)
|
|
{
|
|
$sql = 'SELECT id, topic_id, member_id, score_in_references, score_in_proposals, score_in_groups, score_in_similarities
|
|
FROM topics_members
|
|
WHERE topic_id = ? AND member_id = ?;';
|
|
|
|
$params = [$topicId, $authorsId];
|
|
|
|
$stmt = $this->execStmtWithBindParam($sql, $params);
|
|
$results = $stmt->fetch();
|
|
|
|
if (!($results === false || $results === null)) { //If there are no results, it returns false.
|
|
return $results;
|
|
} else {
|
|
return null;
|
|
}
|
|
}
|
|
|
|
protected function getSentMessagesFromUser(int $offset, int $numOfRows, $sortByAscOrDesc)
|
|
{
|
|
$ascOrDesc = $this->sortByAscOrDesc($sortByAscOrDesc);
|
|
|
|
$sql1 = 'SELECT messages.id, message, user_name AS sender_name, sender_id, messages.date_time, reply_to_msg
|
|
FROM messages
|
|
JOIN users_of_cda ON messages.sender_id = users_of_cda.id
|
|
WHERE sender_id = ?';
|
|
$sql2 = ' ORDER BY messages.id ' . $ascOrDesc;
|
|
$sql3 = ' LIMIT ?,?;';
|
|
$sql = $sql1 . $sql2 . $sql3;
|
|
|
|
$params = [$_SESSION['userId'], $offset, $numOfRows];
|
|
$stmt = $this->execStmtWithBindParam($sql, $params);
|
|
$results = $stmt->fetchAll();
|
|
|
|
return $results;
|
|
}
|
|
|
|
protected function getSimilarities(int $topicId, int $offset, int $numOfRows, $orderByDateOrVotesSumOrVotesNum, $sortByAscOrDesc)
|
|
{
|
|
$dateOrVotes = $this->orderByDateOrVotes($orderByDateOrVotesSumOrVotesNum, 'similar_proposals'); //date_time OR votes_sum_result OR number_of_votes.
|
|
|
|
$ascOrDesc = $this->sortByAscOrDesc($sortByAscOrDesc);
|
|
|
|
$sql1 = 'SELECT similar_proposals.id, proposal_a_id, proposal_b_id, votes_sum_result, number_of_votes, user_name, user_id, topic_id, ballot_box_id
|
|
FROM similar_proposals
|
|
JOIN ballot_boxes ON similar_proposals.ballot_box_id = ballot_boxes.id
|
|
JOIN users_of_cda ON similar_proposals.user_id = users_of_cda.id
|
|
WHERE topic_id = ?';
|
|
$sql2 = ' ORDER BY ' . $dateOrVotes . ' ' . $ascOrDesc; //first JOIN is for the table in ORDER BY.
|
|
$sql3 = ' LIMIT ?,?;';
|
|
$sql = $sql1 . $sql2 . $sql3;
|
|
|
|
$params = [$topicId, $offset, $numOfRows];
|
|
$stmt = $this->execStmtWithBindParam($sql, $params);
|
|
$results = $stmt->fetchAll();
|
|
return $results;
|
|
}
|
|
|
|
protected function getSimilaritiesThatUserVoted1(int $topicId, int $userId)
|
|
{
|
|
$limitOfRows = '10000';
|
|
|
|
$sql = 'SELECT similar_proposals.id, proposal_a_id, proposal_b_id, votes_sum_result, number_of_votes, user_name, ballotboxes_users_votes.topic_id, ballotboxes_users_votes.ballot_box_id, ballotboxes_users_votes.user_id
|
|
FROM ballotboxes_users_votes
|
|
JOIN similar_proposals ON ballotboxes_users_votes.ballot_box_id = similar_proposals.ballot_box_id
|
|
JOIN ballot_boxes ON ballotboxes_users_votes.ballot_box_id = ballot_boxes.id
|
|
JOIN users_of_cda ON ballotboxes_users_votes.user_id = users_of_cda.id
|
|
WHERE ballotboxes_users_votes.topic_id = ? AND ballotboxes_users_votes.category_id = 7 AND ballotboxes_users_votes.user_id = ? AND vote = 1
|
|
ORDER BY ballotboxes_users_votes.ballot_box_id ASC
|
|
LIMIT 0,'. $limitOfRows .';';
|
|
|
|
$params = [$topicId, $userId];
|
|
$stmt = $this->execStmtWithBindParam($sql, $params);
|
|
$results = $stmt->fetchAll();
|
|
return $results;
|
|
}
|
|
|
|
protected function getSimilaritiesReportedByCurrentMember(int $topicId, $orderByDateOrVotesSumOrVotesNum, $sortByAscOrDesc)
|
|
{
|
|
$dateOrVotes = $this->orderByDateOrVotes($orderByDateOrVotesSumOrVotesNum, 'similar_proposals'); //date_time OR votes_sum_result OR number_of_votes.
|
|
|
|
$ascOrDesc = $this->sortByAscOrDesc($sortByAscOrDesc);
|
|
|
|
$limitOfRows = '10000';
|
|
$userId = $_SESSION['userId'];
|
|
|
|
$sql1 = 'SELECT similar_proposals.id, proposal_a_id, proposal_b_id, votes_sum_result, number_of_votes, user_name, user_id, topic_id, ballot_box_id
|
|
FROM similar_proposals
|
|
JOIN ballot_boxes ON similar_proposals.ballot_box_id = ballot_boxes.id
|
|
JOIN users_of_cda ON similar_proposals.user_id = users_of_cda.id
|
|
WHERE topic_id = ? AND user_id = ?';
|
|
$sql2 = ' ORDER BY ' . $dateOrVotes . ' ' . $ascOrDesc; //first JOIN is for the table in ORDER BY.
|
|
$sql3 = ' LIMIT 0,' . $limitOfRows . ';';
|
|
$sql = $sql1 . $sql2 . $sql3;
|
|
|
|
$params = [$topicId, $userId];
|
|
$stmt = $this->execStmtWithBindParam($sql, $params);
|
|
$results = $stmt->fetchAll();
|
|
return $results;
|
|
}
|
|
|
|
protected function getTopics($offset, $numOfRows, $orderByDateOrVotesSumOrVotesNum, $sortByAscOrDesc)
|
|
{
|
|
$dateOrVotes = $this->orderByDateOrVotes($orderByDateOrVotesSumOrVotesNum, 'topics'); //date_time OR votes_sum_result OR number_of_votes.
|
|
|
|
$ascOrDesc = $this->sortByAscOrDesc($sortByAscOrDesc);
|
|
|
|
$sql1 = 'SELECT topics.id, topic, votes_sum_result ,number_of_votes, user_name, user_id, topics.date_time
|
|
FROM topics
|
|
JOIN ballot_boxes ON topics.ballot_box_id = ballot_boxes.id
|
|
JOIN users_of_cda ON topics.user_id = users_of_cda.id';
|
|
$sql2 = ' ORDER BY ' . $dateOrVotes . ' ' . $ascOrDesc; //first JOIN is for the table in ORDER BY.
|
|
$sql3 = ' LIMIT ?,?;';
|
|
$sql = $sql1 . $sql2 . $sql3;
|
|
|
|
$params = [$offset, $numOfRows];
|
|
$stmt = $this->execStmtWithBindParam($sql, $params);
|
|
$results = $stmt->fetchAll();
|
|
return $results;
|
|
}
|
|
|
|
protected function getTopicsIdsThatRefExists($referenceId)
|
|
{
|
|
$sql = 'SELECT DISTINCT topic_id
|
|
FROM proposals_references
|
|
WHERE reference_id = ?;';
|
|
|
|
$params = [$referenceId];
|
|
$stmt = $this->execStmtWithBindParam($sql, $params);
|
|
$results = $stmt->fetchAll();
|
|
return $results;
|
|
}
|
|
|
|
protected function countAllTopics()
|
|
{
|
|
$sql = 'SELECT COUNT(*) AS total_num
|
|
FROM topics;';
|
|
$stmt = $this->executeStmt($sql, null);
|
|
$results = $stmt->fetch();
|
|
return $results['total_num'];
|
|
}
|
|
|
|
protected function getApprovedRequirements(int $topicId, $offset, $numOfRows, $orderByDateOrVotesSumOrVotesNum, $sortByAscOrDesc)
|
|
{
|
|
$dateOrVotes = $this->orderByDateOrVotes($orderByDateOrVotesSumOrVotesNum, 'requirements_of_t'); //date_time OR votes_sum_result OR number_of_votes.
|
|
|
|
$ascOrDesc = $this->sortByAscOrDesc($sortByAscOrDesc);
|
|
|
|
$sql1 = 'SELECT requirements_of_t.id, requirement_text, approval_status, votes_sum_result ,number_of_votes, user_name, user_id, topic_id, requirements_of_t.date_time, ballot_box_id
|
|
FROM requirements_of_t
|
|
JOIN ballot_boxes ON requirements_of_t.ballot_box_id = ballot_boxes.id
|
|
JOIN users_of_cda ON requirements_of_t.user_id = users_of_cda.id
|
|
WHERE topic_id = ? AND approval_status >= 1';
|
|
$sql2 = ' ORDER BY ' . $dateOrVotes . ' ' . $ascOrDesc; //first JOIN is for the table in ORDER BY.
|
|
$sql3 = ' LIMIT ?,?;';
|
|
$sql = $sql1 . $sql2 . $sql3;
|
|
|
|
$params = [$topicId, $offset, $numOfRows];
|
|
$stmt = $this->execStmtWithBindParam($sql, $params);
|
|
$results = $stmt->fetchAll();
|
|
return $results;
|
|
}
|
|
|
|
protected function getRequirements(int $topicId, $offset, $numOfRows, $orderByDateOrVotesSumOrVotesNum, $sortByAscOrDesc)
|
|
{
|
|
$dateOrVotes = $this->orderByDateOrVotes($orderByDateOrVotesSumOrVotesNum, 'requirements_of_t'); //date_time OR votes_sum_result OR number_of_votes.
|
|
|
|
$ascOrDesc = $this->sortByAscOrDesc($sortByAscOrDesc);
|
|
|
|
$sql1 = 'SELECT requirements_of_t.id, requirement_text, approval_status, votes_sum_result ,number_of_votes, user_name, user_id, topic_id, requirements_of_t.date_time, ballot_box_id
|
|
FROM requirements_of_t
|
|
JOIN ballot_boxes ON requirements_of_t.ballot_box_id = ballot_boxes.id
|
|
JOIN users_of_cda ON requirements_of_t.user_id = users_of_cda.id
|
|
WHERE topic_id = ?';
|
|
$sql2 = ' ORDER BY ' . $dateOrVotes . ' ' . $ascOrDesc; //first JOIN is for the table in ORDER BY.
|
|
$sql3 = ' LIMIT ?,?;';
|
|
$sql = $sql1 . $sql2 . $sql3;
|
|
|
|
$params = [$topicId, $offset, $numOfRows];
|
|
$stmt = $this->execStmtWithBindParam($sql, $params);
|
|
$results = $stmt->fetchAll();
|
|
return $results;
|
|
}
|
|
/*
|
|
protected function rankMembersByUsersVoteOfReferences(int $userId, int $topicId, $sortByAscOrDesc)
|
|
{
|
|
$ascOrDesc = $this->sortByAscOrDesc($sortByAscOrDesc);
|
|
|
|
$sql0 = 'SELECT reference_id
|
|
FROM proposals_references
|
|
WHERE proposals_references.topic_id = ?';
|
|
$sql1 = 'SELECT member_id
|
|
FROM topics_members
|
|
WHERE topics_members.topic_id = ?';
|
|
$sql = 'SELECT user_name, SUM(vote) AS member_votes_sum_result, references_of_p.user_id AS member_id, references_of_p.user_id AS id
|
|
FROM ballotboxes_users_votes
|
|
JOIN references_of_p ON ballotboxes_users_votes.ballot_box_id = references_of_p.ballot_box_id
|
|
JOIN users_of_cda ON references_of_p.user_id = users_of_cda.id
|
|
WHERE ballotboxes_users_votes.user_id = ? AND ballotboxes_users_votes.category_id = 5 AND references_of_p.id IN ('
|
|
. $sql0 . ') AND references_of_p.user_id IN (' . $sql1 . ')
|
|
GROUP BY references_of_p.user_id
|
|
ORDER BY member_votes_sum_result ' . $ascOrDesc . ';';
|
|
|
|
$params = [$userId, $topicId, $topicId];
|
|
$stmt = $this->execStmtWithBindParam($sql, $params);
|
|
$results = $stmt->fetchAll();
|
|
return $results;
|
|
}
|
|
*/
|
|
|
|
//TO DO: This function to be checked. Maybe is better to use the above function which should be correct.
|
|
protected function rankMembersByUsersVoteOfReferences(int $userId, int $topicId, $sortByAscOrDesc)
|
|
{
|
|
$ascOrDesc = $this->sortByAscOrDesc($sortByAscOrDesc);
|
|
|
|
$sql = 'SELECT user_name, SUM(vote) AS member_votes_sum_result, references_of_p.user_id AS member_id
|
|
FROM ballotboxes_users_votes
|
|
JOIN references_of_p ON ballotboxes_users_votes.ballot_box_id = references_of_p.ballot_box_id
|
|
JOIN users_of_cda ON references_of_p.user_id = users_of_cda.id
|
|
WHERE references_of_p.topic_id = ? AND ballotboxes_users_votes.user_id = ?
|
|
GROUP BY references_of_p.user_id
|
|
ORDER BY member_votes_sum_result ' . $ascOrDesc . ';';
|
|
|
|
$params = [$topicId, $userId];
|
|
$stmt = $this->execStmtWithBindParam($sql, $params);
|
|
$results = $stmt->fetchAll();
|
|
return $results;
|
|
}
|
|
|
|
protected function countAllGroups(int $topicId)
|
|
{
|
|
$sql = 'SELECT COUNT(*) AS total_num
|
|
FROM groups_of_p
|
|
WHERE topic_id = ?;';
|
|
$params = [$topicId];
|
|
$stmt = $this->execStmtWithBindParam($sql, $params);
|
|
$results = $stmt->fetch();
|
|
return $results['total_num'];
|
|
}
|
|
|
|
protected function countAllApprovedRequirements(int $topicId)
|
|
{
|
|
$sql = 'SELECT COUNT(*) AS total_num
|
|
FROM requirements_of_t
|
|
WHERE topic_id = ? AND approval_status >=1;';
|
|
$params = [$topicId];
|
|
$stmt = $this->execStmtWithBindParam($sql, $params);
|
|
$results = $stmt->fetch();
|
|
return $results['total_num'];
|
|
}
|
|
|
|
protected function countAllPendingRequirements(int $topicId)
|
|
{
|
|
$sql = 'SELECT COUNT(*) AS total_num
|
|
FROM requirements_of_t
|
|
WHERE topic_id = ? AND approval_status IS NULL;';
|
|
$params = [$topicId];
|
|
$stmt = $this->execStmtWithBindParam($sql, $params);
|
|
$results = $stmt->fetch();
|
|
return $results['total_num'];
|
|
}
|
|
|
|
protected function countAllRequirements(int $topicId)
|
|
{
|
|
$sql = 'SELECT COUNT(*) AS total_num
|
|
FROM requirements_of_t
|
|
WHERE topic_id = ?;';
|
|
$params = [$topicId];
|
|
$stmt = $this->execStmtWithBindParam($sql, $params);
|
|
$results = $stmt->fetch();
|
|
return $results['total_num'];
|
|
}
|
|
|
|
protected function countAllProposals(int $topicId)
|
|
{
|
|
$sql = 'SELECT COUNT(*) AS total_num
|
|
FROM proposals
|
|
WHERE topic_id = ?;';
|
|
$params = [$topicId];
|
|
$stmt = $this->execStmtWithBindParam($sql, $params);
|
|
$results = $stmt->fetch();
|
|
return $results['total_num'];
|
|
}
|
|
|
|
//Counts the number of references in CDA, or in a topic, or in a proposal (depending $parentCateg).
|
|
protected function countReferences($parentCateg, int $parentId)
|
|
{
|
|
if ($parentCateg === null) {
|
|
$sql1 = 'SELECT COUNT(id) AS total_num
|
|
FROM references_of_p';
|
|
$sql2 = ';';
|
|
} elseif ($parentCateg === 'topic') {
|
|
$sql1 = 'SELECT COUNT(id) AS total_num
|
|
FROM references_of_p';
|
|
$sql2 = ' WHERE topic_id = ?;';
|
|
} elseif ($parentCateg === 'proposal') {
|
|
$sql1 = 'SELECT COUNT(DISTINCT reference_id) AS total_num
|
|
FROM proposals_references';
|
|
$sql2 = ' WHERE proposal_id = ?;'; //There is no need for COUNT(DISTINCT reference_id) since the changes in tables references_of_p and proposals_references!
|
|
}
|
|
|
|
$sql = $sql1 . $sql2;
|
|
if ($parentCateg === null) {
|
|
$stmt = $this->executeStmt($sql, null);
|
|
} else {
|
|
$params = [$parentId];
|
|
$stmt = $this->execStmtWithBindParam($sql, $params);
|
|
}
|
|
$results = $stmt->fetch();
|
|
return $results['total_num'];
|
|
}
|
|
|
|
protected function countAllReceivedMessagesFromUser()
|
|
{
|
|
$sql = 'SELECT COUNT(*) AS total_num
|
|
FROM msg_recipients
|
|
WHERE recipient_id = ?;';
|
|
$params = [$_SESSION['userId']];
|
|
$stmt = $this->execStmtWithBindParam($sql, $params);
|
|
$results = $stmt->fetch();
|
|
return $results['total_num'];
|
|
}
|
|
|
|
protected function countAllSentMessagesFromUser()
|
|
{
|
|
$sql = 'SELECT COUNT(*) AS total_num
|
|
FROM messages
|
|
WHERE sender_id = ?;';
|
|
$params = [$_SESSION['userId']];
|
|
$stmt = $this->execStmtWithBindParam($sql, $params);
|
|
$results = $stmt->fetch();
|
|
return $results['total_num'];
|
|
}
|
|
|
|
protected function countAllSimilarities(int $topicId)
|
|
{
|
|
$sql = 'SELECT COUNT(*) AS total_num
|
|
FROM similar_proposals
|
|
WHERE topic_id = ?;';
|
|
$params = [$topicId];
|
|
$stmt = $this->execStmtWithBindParam($sql, $params);
|
|
$results = $stmt->fetch();
|
|
return $results['total_num'];
|
|
}
|
|
|
|
protected function countCommentsOfSelectedCategoryId(int $categOfParent, int $parentId)
|
|
{
|
|
$sql = 'SELECT COUNT(*) AS total_num
|
|
FROM comments_in_cda
|
|
WHERE category_id = ? AND in_category_id = ?';
|
|
$params = [$categOfParent, $parentId];
|
|
$stmt = $this->execStmtWithBindParam($sql, $params);
|
|
$results = $stmt->fetch();
|
|
return $results['total_num'];
|
|
}
|
|
|
|
protected function countAllTeamMembers(int $topicId)
|
|
{
|
|
$sql = 'SELECT COUNT(*) AS total_num
|
|
FROM topics_members
|
|
WHERE topic_id = ?;';
|
|
$params = [$topicId];
|
|
$stmt = $this->execStmtWithBindParam($sql, $params);
|
|
$results = $stmt->fetch();
|
|
return $results['total_num'];
|
|
}
|
|
|
|
protected function countRateOfNewEntries($selectedCateg, int $userId)
|
|
{
|
|
//TO DO: Insert time-table column in users_of_cda table.
|
|
//This function is substituted by the function ratesOfEnteredData.
|
|
|
|
$period = 604800; //One week in seconds (60*60*24*7 = 604800).
|
|
|
|
switch ($selectedCateg) {
|
|
case 'u':
|
|
$sql1 = 'users_of_cda';
|
|
break;
|
|
|
|
case 't':
|
|
$sql1 = 'topics';
|
|
break;
|
|
|
|
case 'g':
|
|
$sql1 = 'groups_of_p';
|
|
break;
|
|
|
|
case 'p':
|
|
$sql1 = 'proposals';
|
|
break;
|
|
|
|
case 'c':
|
|
$sql1 = 'comments_in_cda';
|
|
break;
|
|
|
|
case 'r':
|
|
$sql1 = 'references_of_p';
|
|
break;
|
|
|
|
case 'q':
|
|
$sql1 = 'requirements_of_t';
|
|
break;
|
|
|
|
case 's':
|
|
$sql1 = 'similar_proposals';
|
|
break;
|
|
|
|
case 'm':
|
|
$sql1 = 'messages';
|
|
break;
|
|
|
|
default:
|
|
return 0;
|
|
break;
|
|
}
|
|
|
|
if ($selectedCateg == 'u') {
|
|
$sql2 = '';
|
|
} elseif ($selectedCateg == 'm') {
|
|
$sql2 = ' AND sender_id = :userId';
|
|
} else {
|
|
$sql2 = ' AND user_id = :userId';
|
|
}
|
|
|
|
$sql = 'SELECT COUNT(*) AS total_num
|
|
FROM ' . $sql1 . '
|
|
WHERE TO_SECONDS(CURRENT_TIMESTAMP)-TO_SECONDS(date_time) < $period' . $sql2 . ';';
|
|
|
|
if ($selectedCateg == 'u') {
|
|
$stmt = $this->executeStmt($sql, null);
|
|
} else {
|
|
$params = array(':userId' => $userId);
|
|
$stmt = $this->executeStmt($sql, $params);
|
|
}
|
|
|
|
$results = $stmt->fetch();
|
|
return $results['total_num'];
|
|
}
|
|
|
|
protected function getSelectedTgpcr($selectedCateg, int $selectedTgpcrID)
|
|
{
|
|
switch ($selectedCateg) {
|
|
case 't':
|
|
$sql = 'SELECT topics.id, topic, votes_sum_result ,number_of_votes, user_name, topics.date_time, ballot_box_id, user_id
|
|
FROM topics
|
|
JOIN ballot_boxes ON topics.ballot_box_id = ballot_boxes.id
|
|
JOIN users_of_cda ON topics.user_id = users_of_cda.id
|
|
WHERE topics.id = :selectedtgpcrid;';
|
|
break;
|
|
|
|
case 'g':
|
|
$sql = 'SELECT groups_of_p.id, group_name, votes_sum_result ,number_of_votes, user_name, groups_of_p.date_time, topic_id, ballot_box_id, group_str, user_id
|
|
FROM groups_of_p
|
|
JOIN ballot_boxes ON groups_of_p.ballot_box_id = ballot_boxes.id
|
|
JOIN users_of_cda ON groups_of_p.user_id = users_of_cda.id
|
|
WHERE groups_of_p.id = :selectedtgpcrid;';
|
|
break;
|
|
|
|
case 'p':
|
|
$sql1 = 'SELECT proposals.id, proposal, votes_sum_result ,number_of_votes, user_name, user_id, proposals.date_time, topic_id, ';
|
|
$sql2 = 'ballot_box_id, SIGN(TO_SECONDS(edit_closing_date)-TO_SECONDS(CURRENT_TIMESTAMP)) AS edit_state, edit_closing_date, TO_SECONDS(CURRENT_TIMESTAMP)-TO_SECONDS(proposals.date_time) AS seconds_from_date_time, TO_SECONDS(edit_closing_date)-TO_SECONDS(CURRENT_TIMESTAMP) AS seconds_from_edit_closing_date
|
|
FROM proposals
|
|
JOIN ballot_boxes ON proposals.ballot_box_id = ballot_boxes.id
|
|
JOIN users_of_cda ON proposals.user_id = users_of_cda.id
|
|
WHERE proposals.id = :selectedtgpcrid;';
|
|
$sql = $sql1 . $sql2;
|
|
break;
|
|
|
|
case 'c':
|
|
$sql = 'SELECT comments_in_cda.id, comment_text, category_id, in_category_id, user_name, comments_in_cda.date_time, user_id, topic_id
|
|
FROM comments_in_cda
|
|
JOIN users_of_cda ON comments_in_cda.user_id = users_of_cda.id
|
|
WHERE comments_in_cda.id = :selectedtgpcrid;';
|
|
break;
|
|
|
|
case 'r':
|
|
$sql = 'SELECT references_of_p.id, ref_description, reference_text, votes_sum_result ,number_of_votes, user_name, references_of_p.date_time, proposal_id, topic_id, ballot_box_id, user_id
|
|
FROM references_of_p
|
|
JOIN ballot_boxes ON references_of_p.ballot_box_id = ballot_boxes.id
|
|
JOIN users_of_cda ON references_of_p.user_id = users_of_cda.id
|
|
WHERE references_of_p.id = :selectedtgpcrid;';
|
|
break;
|
|
|
|
case 'q':
|
|
$sql = 'SELECT requirements_of_t.id, requirement_text, approval_status, votes_sum_result ,number_of_votes, user_name, requirements_of_t.date_time, topic_id, ballot_box_id, user_id
|
|
FROM requirements_of_t
|
|
JOIN ballot_boxes ON requirements_of_t.ballot_box_id = ballot_boxes.id
|
|
JOIN users_of_cda ON requirements_of_t.user_id = users_of_cda.id
|
|
WHERE requirements_of_t.id = :selectedtgpcrid;';
|
|
break;
|
|
|
|
case 's':
|
|
$sql = 'SELECT similar_proposals.id, proposal_a_id, proposal_b_id, votes_sum_result, number_of_votes, user_name, topic_id, ballot_box_id, user_id
|
|
FROM similar_proposals
|
|
JOIN ballot_boxes ON similar_proposals.ballot_box_id = ballot_boxes.id
|
|
JOIN users_of_cda ON similar_proposals.user_id = users_of_cda.id
|
|
WHERE similar_proposals.id = :selectedtgpcrid';
|
|
break;
|
|
|
|
default:
|
|
$sql = '';
|
|
break;
|
|
}
|
|
|
|
if ($sql != '') {
|
|
$params = array(':selectedtgpcrid' => $selectedTgpcrID);
|
|
|
|
$stmt = $this->executeStmt($sql, $params);
|
|
$results = $stmt->fetch();
|
|
return $results;
|
|
} else {
|
|
return 0;
|
|
}
|
|
}
|
|
|
|
protected function getSelectedTopicWithTimeTableInfo(int $topicId)
|
|
{
|
|
$sql = 'SELECT topics.id, topic, votes_sum_result ,number_of_votes, user_name, topics.date_time, ballot_box_id, user_id, invitation_phase_closing_date, requirements_phase_closing_date, proposals_phase_closing_date, groups_phase_closing_date, timetable_changed, (TO_SECONDS(invitation_phase_closing_date)-TO_SECONDS(CURRENT_TIMESTAMP)) AS inv_state, (TO_SECONDS(requirements_phase_closing_date)-TO_SECONDS(CURRENT_TIMESTAMP)) AS req_state, (TO_SECONDS(proposals_phase_closing_date)-TO_SECONDS(CURRENT_TIMESTAMP)) AS prop_state, (TO_SECONDS(groups_phase_closing_date)-TO_SECONDS(CURRENT_TIMESTAMP)) AS groups_state
|
|
FROM topics
|
|
JOIN ballot_boxes ON topics.ballot_box_id = ballot_boxes.id
|
|
JOIN users_of_cda ON topics.user_id = users_of_cda.id
|
|
WHERE topics.id = :topicid;';
|
|
|
|
$params = array(':topicid' => $topicId);
|
|
|
|
$stmt = $this->executeStmt($sql, $params);
|
|
$results = $stmt->fetch();
|
|
|
|
if (!($results === false || $results === null || $results['id'] === null)) { //If there is no id, it returns false.
|
|
return $results;
|
|
} else {
|
|
return null;
|
|
}
|
|
}
|
|
|
|
protected function getSelectedTopicInfo(int $topicId)
|
|
{
|
|
$sql = 'SELECT topics.id, invitation_phase_closing_date, requirements_phase_closing_date, proposals_phase_closing_date, groups_phase_closing_date, timetable_changed, (TO_SECONDS(invitation_phase_closing_date)-TO_SECONDS(CURRENT_TIMESTAMP)) AS inv_state, (TO_SECONDS(requirements_phase_closing_date)-TO_SECONDS(CURRENT_TIMESTAMP)) AS req_state, (TO_SECONDS(proposals_phase_closing_date)-TO_SECONDS(CURRENT_TIMESTAMP)) AS prop_state, (TO_SECONDS(groups_phase_closing_date)-TO_SECONDS(CURRENT_TIMESTAMP)) AS groups_state
|
|
FROM topics
|
|
WHERE topics.id = :topicid;';
|
|
|
|
$params = array(':topicid' => $topicId);
|
|
|
|
$stmt = $this->executeStmt($sql, $params);
|
|
$results = $stmt->fetch();
|
|
|
|
if (!($results === false || $results === null || $results['id'] === null)) { //If there is no id, it returns false.
|
|
return $results;
|
|
} else {
|
|
return null;
|
|
}
|
|
}
|
|
|
|
protected function insertRowForEntryRatesOfData()
|
|
{
|
|
$sql = 'INSERT INTO entry_rates_of_data (id, start_of_current_period, entries_of_users_allowed, entries_of_users_rejected, entries_of_topics_allowed, entries_of_topics_rejected, entries_of_requirements_allowed, entries_of_requirements_rejected, entries_of_proposals_allowed, entries_of_proposals_rejected, entries_of_references_allowed, entries_of_references_rejected, entries_of_similarities_allowed, entries_of_similarities_rejected, entries_of_groups_allowed, entries_of_groups_rejected, entries_of_comments_allowed, entries_of_comments_rejected, entries_of_messages_allowed, entries_of_messages_rejected)
|
|
VALUES (LAST_INSERT_ID(), CURRENT_TIMESTAMP(), 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0);';
|
|
|
|
$stmt = $this->executeStmt($sql, null);
|
|
|
|
return $this->lastId;
|
|
}
|
|
|
|
protected function updateTimeTableOfTopic(int $topicId, $membersInvitationPhaseDuration, $requirementsPhaseDuration, $proposalsPhaseDuration, $proposalsGroupsPhaseDuration, $timeTableChanged)
|
|
{
|
|
$membersInvitationPhaseEnd = $membersInvitationPhaseDuration;
|
|
// in how many days from now the requirements phase will close. Details of that phase are written in timetableinfotext.php.
|
|
$requirementsPhaseEnd = $membersInvitationPhaseEnd + $requirementsPhaseDuration;
|
|
// in how many days from now the proposals phase will close. Details in timetableinfotext.php
|
|
$proposalsPhaseEnd = $requirementsPhaseEnd + $proposalsPhaseDuration;
|
|
// in how many days from now the proposals-groups phase will close. Details in timetableinfotext.php
|
|
$proposalsGroupsPhaseEnd = $proposalsPhaseEnd + $proposalsGroupsPhaseDuration;
|
|
|
|
$sql = 'UPDATE topics
|
|
SET invitation_phase_closing_date = TIMESTAMPADD(SQL_TSI_DAY,:membersInvitationPhaseEnd,CURRENT_TIMESTAMP), requirements_phase_closing_date = TIMESTAMPADD(SQL_TSI_DAY,:requirementsPhaseEnd,CURRENT_TIMESTAMP), proposals_phase_closing_date = TIMESTAMPADD(SQL_TSI_DAY,:proposalsPhaseEnd,CURRENT_TIMESTAMP), groups_phase_closing_date = TIMESTAMPADD(SQL_TSI_DAY,:proposalsGroupsPhaseEnd,CURRENT_TIMESTAMP), timetable_changed = :timeTableChanged
|
|
WHERE topics.id = :topicid;';
|
|
$params = array(':membersInvitationPhaseEnd' => $membersInvitationPhaseEnd, ':requirementsPhaseEnd' => $requirementsPhaseEnd, ':proposalsPhaseEnd' => $proposalsPhaseEnd, ':proposalsGroupsPhaseEnd' => $proposalsGroupsPhaseEnd, ':timeTableChanged' => $timeTableChanged, ':topicid' => $topicId);
|
|
|
|
$stmt = $this->executeStmt($sql, $params);
|
|
}
|
|
|
|
protected function postponeEndOfCurrentPhase(int $topicId, $postoneEndOfCurrentPhaseInDays, $currentPhase)
|
|
{
|
|
$sql1 = 'UPDATE topics ';
|
|
|
|
switch ($currentPhase) {
|
|
case 'inv_state':
|
|
$sql2 = ' SET invitation_phase_closing_date = TIMESTAMPADD(SQL_TSI_DAY,:postoneEndOfCurrentPhaseInDays,invitation_phase_closing_date), requirements_phase_closing_date = TIMESTAMPADD(SQL_TSI_DAY,:postoneEndOfCurrentPhaseInDays,requirements_phase_closing_date), proposals_phase_closing_date = TIMESTAMPADD(SQL_TSI_DAY,:postoneEndOfCurrentPhaseInDays,proposals_phase_closing_date), groups_phase_closing_date = TIMESTAMPADD(SQL_TSI_DAY,:postoneEndOfCurrentPhaseInDays,groups_phase_closing_date)';
|
|
break;
|
|
|
|
case 'req_state':
|
|
$sql2 = ' SET requirements_phase_closing_date = TIMESTAMPADD(SQL_TSI_DAY,:postoneEndOfCurrentPhaseInDays,requirements_phase_closing_date), proposals_phase_closing_date = TIMESTAMPADD(SQL_TSI_DAY,:postoneEndOfCurrentPhaseInDays,proposals_phase_closing_date), groups_phase_closing_date = TIMESTAMPADD(SQL_TSI_DAY,:postoneEndOfCurrentPhaseInDays,groups_phase_closing_date)';
|
|
break;
|
|
|
|
case 'prop_state':
|
|
$sql2 = ' SET proposals_phase_closing_date = TIMESTAMPADD(SQL_TSI_DAY,:postoneEndOfCurrentPhaseInDays,proposals_phase_closing_date), groups_phase_closing_date = TIMESTAMPADD(SQL_TSI_DAY,:postoneEndOfCurrentPhaseInDays,groups_phase_closing_date)';
|
|
break;
|
|
|
|
case 'groups_state':
|
|
$sql2 = ' SET groups_phase_closing_date = TIMESTAMPADD(SQL_TSI_DAY,:postoneEndOfCurrentPhaseInDays,groups_phase_closing_date)';
|
|
break;
|
|
|
|
default:
|
|
return 'currentphaseerror';
|
|
break;
|
|
}
|
|
|
|
$sql3 = ', timetable_changed = timetable_changed + 1
|
|
WHERE topics.id = :topicid;';
|
|
|
|
$sql = $sql1 . $sql2 . $sql3;
|
|
$params = array(':postoneEndOfCurrentPhaseInDays' => $postoneEndOfCurrentPhaseInDays, ':topicid' => $topicId);
|
|
|
|
$stmt = $this->executeStmt($sql, $params);
|
|
return 'success';
|
|
}
|
|
|
|
protected function setBallotBox()
|
|
{
|
|
$sql = 'INSERT INTO ballot_boxes (id, date_time, votes_sum_result, number_of_votes)
|
|
VALUES (LAST_INSERT_ID(), DEFAULT, DEFAULT, DEFAULT);';
|
|
|
|
$stmt = $this->executeStmt($sql, null);
|
|
|
|
return $this->lastId;
|
|
}
|
|
|
|
protected function setGroup(int $userId, $groupTitle, int $ballotBoxId, int $topicId, $selectedProposalsAsString)
|
|
{
|
|
$sql = 'INSERT INTO groups_of_p (id, user_id, group_name, date_time, ballot_box_id, topic_id, group_str)
|
|
VALUES (LAST_INSERT_ID(), :userId, :groupTitle, DEFAULT, :ballotBoxId, :topicId, :selectedProposalsAsString);';
|
|
|
|
$params = array(':userId' => $userId,':groupTitle' => $groupTitle, ':ballotBoxId' => $ballotBoxId, ':topicId' => $topicId, ':selectedProposalsAsString' => $selectedProposalsAsString);
|
|
|
|
$stmt = $this->executeStmt($sql, $params);
|
|
}
|
|
|
|
protected function setTopic($topic, $userId, $ballotBoxId)
|
|
{
|
|
$sql = 'INSERT INTO topics (id, topic, user_id, date_time, ballot_box_id, invitation_phase_closing_date, requirements_phase_closing_date, proposals_phase_closing_date, groups_phase_closing_date, timetable_changed)
|
|
VALUES (LAST_INSERT_ID(), :topic, :userId, DEFAULT, :ballotBoxId, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT);';
|
|
|
|
$params = array(':topic' => $topic, ':userId' => $userId, ':ballotBoxId' => $ballotBoxId);
|
|
|
|
$stmt = $this->executeStmt($sql, $params);
|
|
|
|
return $this->lastId;
|
|
}
|
|
|
|
protected function setMessage(int $senderId, $newMessage, $replyToMessage)
|
|
{
|
|
$encryptedMsg = $this->encryptData($newMessage);
|
|
|
|
$sql = 'INSERT INTO messages (id, sender_id, message, date_time, reply_to_msg)
|
|
VALUES (LAST_INSERT_ID(), :senderId, :encryptedMsg, DEFAULT, :replyToMessage);';
|
|
|
|
$params = array(':senderId' => $senderId, ':encryptedMsg' => $encryptedMsg, ':replyToMessage' => $replyToMessage);
|
|
|
|
$stmt = $this->executeStmt($sql, $params);
|
|
|
|
return $this->lastId;
|
|
}
|
|
|
|
protected function setReference($newReferenceDescription, $newReferenceUrl, $userId, $proposalId, $topicId, $ballotBoxId)
|
|
{
|
|
if ($proposalId == 0) {
|
|
$proposalId = NULL;
|
|
}
|
|
|
|
$sql = 'INSERT INTO references_of_p (id, user_id, reference_text, ref_description, date_time, ballot_box_id, proposal_id, topic_id)
|
|
VALUES (LAST_INSERT_ID(), :userId, :newReferenceUrl, :newReferenceDescription, DEFAULT, :ballotBoxId, :proposalId, :topicId);';
|
|
|
|
$params = array(':userId' => $userId, ':newReferenceUrl' => $newReferenceUrl, ':newReferenceDescription' => $newReferenceDescription, ':ballotBoxId' => $ballotBoxId, ':proposalId' => $proposalId, ':topicId' => $topicId);
|
|
|
|
$stmt = $this->executeStmt($sql, $params);
|
|
|
|
return $this->lastId;
|
|
}
|
|
|
|
protected function setRequirement($requirement, $userId, $topicId, $ballotBoxId)
|
|
{
|
|
$sql = 'INSERT INTO requirements_of_t (id, user_id, requirement_text, approval_status, topic_id ,date_time, ballot_box_id)
|
|
VALUES (LAST_INSERT_ID(), :userId, :requirement, DEFAULT, :topicId, DEFAULT, :ballotBoxId);';
|
|
|
|
$params = array(':userId' => $userId,':requirement' => $requirement, ':topicId' => $topicId, ':ballotBoxId' => $ballotBoxId);
|
|
|
|
$stmt = $this->executeStmt($sql, $params);
|
|
}
|
|
|
|
protected function setProposal($proposal, $userId, $editingPhaseDuration, $topicId, $ballotBoxId)
|
|
{
|
|
$sql = 'INSERT INTO proposals (id, user_id, proposal, date_time, ballot_box_id, edit_closing_date, topic_id)
|
|
VALUES (LAST_INSERT_ID(), :userId, :proposal, DEFAULT, :ballotBoxId, TIMESTAMPADD(SQL_TSI_DAY,:editingPhaseDuration,CURRENT_TIMESTAMP), :topicId);';
|
|
|
|
$params = array(':userId' => $userId, ':proposal' => $proposal, ':ballotBoxId' => $ballotBoxId, ':editingPhaseDuration' => $editingPhaseDuration, ':topicId' => $topicId);
|
|
|
|
$stmt = $this->executeStmt($sql, $params);
|
|
}
|
|
|
|
protected function setComment($comment, int $userId, int $categOfParent, int $parentId, $topicId)
|
|
{
|
|
if ($categOfParent == 5) { //If categOfParent = reference.
|
|
$topicId = NULL;
|
|
}
|
|
|
|
$sql = 'INSERT INTO comments_in_cda (id, user_id, comment_text, date_time, category_id ,in_category_id, topic_id)
|
|
VALUES (LAST_INSERT_ID(), :userId, :comment, DEFAULT, :categOfParent, :parentId, :topicId);';
|
|
|
|
$params = array(':userId' => $userId,':comment' => $comment, ':categOfParent' => $categOfParent, ':parentId' => $parentId, ':topicId' => $topicId);
|
|
|
|
$stmt = $this->executeStmt($sql, $params);
|
|
}
|
|
|
|
protected function setSimilarity(int $userId, int $proposalAId, int $proposalBId, int $topicId, int $ballotBoxId)
|
|
{
|
|
$sql = 'INSERT INTO similar_proposals (id, user_id, proposal_a_id, proposal_b_id, ballot_box_id ,topic_id)
|
|
VALUES (LAST_INSERT_ID(), :userId, :proposalAId, :proposalBId, :ballotBoxId, :topicId);';
|
|
|
|
$params = array(':userId' => $userId, ':proposalAId' => $proposalAId, ':proposalBId' => $proposalBId, ':ballotBoxId' => $ballotBoxId, ':topicId' => $topicId);
|
|
|
|
$stmt = $this->executeStmt($sql, $params);
|
|
}
|
|
|
|
protected function voteToBallotBoxId(int $ballotBoxId, int $userId, int $vote, $topicId, int $categ)
|
|
{
|
|
$sql = 'INSERT INTO ballotboxes_users_votes (id, ballot_box_id, user_id, vote, topic_id, category_id)
|
|
VALUES (LAST_INSERT_ID(), ?, ?, ?, ?, ?);';
|
|
|
|
$params = [$ballotBoxId, $userId, $vote, $topicId, $categ];
|
|
|
|
$stmt = $this->execStmtWithBindParam($sql, $params);
|
|
|
|
$sql = 'UPDATE ballot_boxes
|
|
SET
|
|
votes_sum_result = ?,
|
|
number_of_votes = ?
|
|
WHERE id = ?;';
|
|
|
|
$NumberOfVotes = $this->countNumberOfVotes($ballotBoxId);
|
|
|
|
if ($NumberOfVotes === null) {
|
|
return;
|
|
}
|
|
|
|
$votesSumResult = $this->countVotesSumResult($ballotBoxId);
|
|
|
|
if ($votesSumResult === null) {
|
|
return;
|
|
}
|
|
|
|
$params = [$votesSumResult, $NumberOfVotes, $ballotBoxId];
|
|
|
|
/*
|
|
$sql = 'UPDATE ballot_boxes
|
|
SET
|
|
votes_sum_result = votes_sum_result + :vote,
|
|
number_of_votes = number_of_votes + 1
|
|
WHERE id = :ballotBoxId;';
|
|
|
|
$params = [$vote, $ballotBoxId];
|
|
*/
|
|
|
|
$stmt = $this->execStmtWithBindParam($sql, $params);
|
|
}
|
|
|
|
protected function countNumberOfVotes($ballotBoxId)
|
|
{
|
|
$sql = 'SELECT COUNT(*) AS count_number_of_votes
|
|
FROM ballotboxes_users_votes
|
|
WHERE ballot_box_id = ?;';
|
|
|
|
$params = [$ballotBoxId];
|
|
|
|
$stmt = $this->execStmtWithBindParam($sql, $params);
|
|
$results = $stmt->fetch();
|
|
|
|
if ($results !== 0) {
|
|
return $results['count_number_of_votes']; //If there are no votes, it returns 0.
|
|
} else {
|
|
return null;
|
|
}
|
|
}
|
|
|
|
protected function countVotesSumResult($ballotBoxId)
|
|
{
|
|
$sql = 'SELECT SUM(vote) AS count_votes_sum_result
|
|
FROM ballotboxes_users_votes
|
|
WHERE ballot_box_id = ?;';
|
|
|
|
$params = [$ballotBoxId];
|
|
|
|
$stmt = $this->execStmtWithBindParam($sql, $params);
|
|
$results = $stmt->fetch();
|
|
|
|
return $results['count_votes_sum_result']; //If there are no votes, it returns null.
|
|
}
|
|
|
|
protected function checkIfUserHasVotedReturnVote($ballotBoxId, $userId)
|
|
{
|
|
$sql = 'SELECT vote
|
|
FROM ballotboxes_users_votes
|
|
WHERE ballot_box_id = ? AND user_id = ?;';
|
|
|
|
$params = [$ballotBoxId, $userId];
|
|
|
|
$stmt = $this->execStmtWithBindParam($sql, $params);
|
|
$results = $stmt->fetch();
|
|
|
|
if (!($results === false || $results === null)) { //If there are no votes, it returns false.
|
|
return $results['vote'];
|
|
} else {
|
|
return null;
|
|
}
|
|
}
|
|
|
|
protected function addMember(int $topicId, int $newMemberId)
|
|
{
|
|
$sql = 'INSERT INTO topics_members (id, topic_id, member_id, score_in_references, score_in_proposals, score_in_groups, score_in_similarities)
|
|
VALUES (LAST_INSERT_ID(), :topicId, :newMemberId, DEFAULT, DEFAULT, DEFAULT, DEFAULT);';
|
|
|
|
$params = array(':topicId' => $topicId, ':newMemberId' => $newMemberId);
|
|
|
|
$stmt = $this->executeStmt($sql, $params);
|
|
}
|
|
|
|
protected function checkIfAlreadyMember(int $topicId, $memberId)
|
|
{
|
|
$sql = 'SELECT topics_members.id
|
|
FROM topics_members
|
|
WHERE topic_id = ? AND member_id = ?;';
|
|
|
|
$params = [$topicId, $memberId];
|
|
|
|
$stmt = $this->execStmtWithBindParam($sql, $params);
|
|
$result = $stmt->fetch();
|
|
|
|
if (!($result === false || $result === null || $result['id'] === null)) { //If there is no id, it returns false.
|
|
return $result['id'];
|
|
} else {
|
|
return null;
|
|
}
|
|
}
|
|
|
|
protected function checkIfGroupExists($selectedProposalsAsString)
|
|
{
|
|
$sql = 'SELECT id
|
|
FROM groups_of_p
|
|
WHERE group_str = :selectedProposalsAsString;';
|
|
|
|
$params = array(':selectedProposalsAsString' => $selectedProposalsAsString);
|
|
|
|
$stmt = $this->executeStmt($sql, $params);
|
|
$result = $stmt->fetch();
|
|
|
|
if (!($result === false || $result === null || $result['id'] === null)) { //If there is no id, it returns false.
|
|
return $result['id'];
|
|
} else {
|
|
return null;
|
|
}
|
|
}
|
|
|
|
protected function checkIfGroupTitleExists($groupTitle)
|
|
{
|
|
$sql = 'SELECT id
|
|
FROM groups_of_p
|
|
WHERE group_name = :groupTitle;';
|
|
|
|
$params = array(':groupTitle' => $groupTitle);
|
|
|
|
$stmt = $this->executeStmt($sql, $params);
|
|
$result = $stmt->fetch();
|
|
|
|
if (!($result === false || $result === null || $result['id'] === null)) { //If there is no id, it returns false.
|
|
return $result['id'];
|
|
} else {
|
|
return null;
|
|
}
|
|
}
|
|
|
|
protected function checkIfTopicExists($topic)
|
|
{
|
|
$sql = 'SELECT id
|
|
FROM topics
|
|
WHERE topic = :topic;';
|
|
|
|
$params = array(':topic' => $topic);
|
|
|
|
$stmt = $this->executeStmt($sql, $params);
|
|
$result = $stmt->fetch();
|
|
|
|
if (!($result === false || $result === null || $result['id'] === null)) { //If there is no id, it returns false.
|
|
return $result['id'];
|
|
} else {
|
|
return null;
|
|
}
|
|
}
|
|
|
|
protected function checkIfRequirementExists($requirement, $topicId)
|
|
{
|
|
$sql = 'SELECT id
|
|
FROM requirements_of_t
|
|
WHERE requirement_text = :requirement AND topic_id = :topicid;';
|
|
|
|
$params = array(':requirement' => $requirement, ':topicid' => $topicId);
|
|
|
|
$stmt = $this->executeStmt($sql, $params);
|
|
$result = $stmt->fetch();
|
|
|
|
if (!($result === false || $result === null || $result['id'] === null)) { //If there is no id, it returns false.
|
|
return $result['id'];
|
|
} else {
|
|
return null;
|
|
}
|
|
}
|
|
|
|
protected function checkIfReferenceExists($newReferenceUrl)
|
|
{
|
|
$sql = 'SELECT id
|
|
FROM references_of_p
|
|
WHERE reference_text = :newReferenceUrl;';
|
|
|
|
$params = array(':newReferenceUrl' => $newReferenceUrl);
|
|
|
|
$stmt = $this->executeStmt($sql, $params);
|
|
$result = $stmt->fetch();
|
|
|
|
if (!($result === false || $result === null || $result['id'] === null)) { //If there is no id, it returns false.
|
|
return $result['id'];
|
|
} else {
|
|
return null;
|
|
}
|
|
}
|
|
|
|
protected function checkIfReferenceExistsInProposal($newReferenceUrl, $ProposalID)
|
|
{
|
|
$sql = 'SELECT proposals_references.reference_id
|
|
FROM proposals_references
|
|
JOIN references_of_p ON proposals_references.reference_id = references_of_p.id
|
|
WHERE proposals_references.proposal_id = :ProposalID AND reference_text = :newReferenceUrl;';
|
|
|
|
$params = array(':ProposalID' => $ProposalID, ':newReferenceUrl' => $newReferenceUrl);
|
|
|
|
$stmt = $this->executeStmt($sql, $params);
|
|
$result = $stmt->fetch();
|
|
|
|
if (!($result === false || $result === null || $result['reference_id'] === null)) { //If there is no id, it returns false.
|
|
return $result['reference_id'];
|
|
} else {
|
|
return null;
|
|
}
|
|
}
|
|
|
|
protected function checkIfReferenceExistsInTopic($newReferenceUrl, $topicID)
|
|
{
|
|
$sql = 'SELECT proposals_references.reference_id
|
|
FROM proposals_references
|
|
JOIN references_of_p ON proposals_references.reference_id = references_of_p.id
|
|
WHERE proposals_references.topic_id = :topicID AND reference_text = :newReferenceUrl;';
|
|
|
|
$params = array(':topicID' => $topicID, ':newReferenceUrl' => $newReferenceUrl);
|
|
|
|
$stmt = $this->executeStmt($sql, $params);
|
|
$result = $stmt->fetch();
|
|
|
|
if (!($result === false || $result === null || $result['reference_id'] === null)) { //If there is no id, it returns false.
|
|
return $result['reference_id'];
|
|
} else {
|
|
return null;
|
|
}
|
|
}
|
|
|
|
protected function checkIfUsersReversedPairOfSimilarProposalsExists(int $topicId, int $proposalAid, int $proposalBid, int $userId)
|
|
{
|
|
$sql = 'SELECT id
|
|
FROM similar_proposals
|
|
WHERE topic_id = :topicId AND user_id = :userId AND proposal_a_id = :proposalBid AND proposal_b_id = :proposalAid;';
|
|
|
|
$params = array(':topicId' => $topicId, ':userId' => $userId, ':proposalBid' => $proposalBid, ':proposalAid' => $proposalAid);
|
|
|
|
$stmt = $this->executeStmt($sql, $params);
|
|
$result = $stmt->fetch();
|
|
|
|
if (!($result === false || $result === null || $result['id'] === null)) { //If there is no id, it returns false.
|
|
return $result['id'];
|
|
} else {
|
|
return null;
|
|
}
|
|
}
|
|
|
|
protected function checkIfReversedPairOfSimilarProposalsExists(int $topicId, int $proposalAid, int $proposalBid)
|
|
{
|
|
$sql = 'SELECT id, ballot_box_id
|
|
FROM similar_proposals
|
|
WHERE topic_id = :topicId AND proposal_a_id = :proposalBid AND proposal_b_id = :proposalAid;';
|
|
|
|
$params = array(':topicId' => $topicId, ':proposalBid' => $proposalBid, ':proposalAid' => $proposalAid);
|
|
|
|
$stmt = $this->executeStmt($sql, $params);
|
|
$result = $stmt->fetch();
|
|
|
|
if (!($result === false || $result === null || $result['id'] === null)) { //If there is no id, it returns false.
|
|
return $result;
|
|
} else {
|
|
return null;
|
|
}
|
|
}
|
|
|
|
protected function checkIfProposalExists($proposal, $topicId)
|
|
{
|
|
$sql = 'SELECT id
|
|
FROM proposals
|
|
WHERE proposal = :proposal AND topic_id = :topicid;';
|
|
|
|
$params = array(':proposal' => $proposal, ':topicid' => $topicId);
|
|
|
|
$stmt = $this->executeStmt($sql, $params);
|
|
$result = $stmt->fetch();
|
|
|
|
if (!($result === false || $result === null || $result['id'] === null)) { //If there is no id, it returns false.
|
|
return $result['id'];
|
|
} else {
|
|
return null;
|
|
}
|
|
}
|
|
|
|
protected function checkIfSimilarityExists(int $proposalAid, int $proposalBid)
|
|
{
|
|
$sql = 'SELECT id
|
|
FROM similar_proposals
|
|
WHERE proposal_a_id = :proposalAid AND proposal_b_id = :proposalBid;';
|
|
|
|
$params = array(':proposalAid' => $proposalAid, ':proposalBid' => $proposalBid);
|
|
|
|
$stmt = $this->executeStmt($sql, $params);
|
|
$result = $stmt->fetch();
|
|
|
|
if (!($result === false || $result === null || $result['id'] === null)) { //If there is no id, it returns false.
|
|
return $result['id'];
|
|
} else {
|
|
return null;
|
|
}
|
|
}
|
|
|
|
protected function ratesOfEnteredData()
|
|
{
|
|
$sql = 'SELECT id, start_of_current_period, entries_of_users_allowed, entries_of_users_rejected, entries_of_topics_allowed, entries_of_topics_rejected, entries_of_requirements_allowed, entries_of_requirements_rejected, entries_of_proposals_allowed, entries_of_proposals_rejected, entries_of_references_allowed, entries_of_references_rejected, entries_of_similarities_allowed, entries_of_similarities_rejected, entries_of_groups_allowed, entries_of_groups_rejected, entries_of_comments_allowed, entries_of_comments_rejected, entries_of_messages_allowed, entries_of_messages_rejected, TIMESTAMPDIFF(SQL_TSI_SECOND,start_of_current_period,CURRENT_TIMESTAMP) AS period_in_seconds
|
|
FROM entry_rates_of_data
|
|
ORDER BY id DESC
|
|
LIMIT 1;';
|
|
|
|
$stmt = $this->executeStmt($sql, null);
|
|
$result = $stmt->fetch();
|
|
|
|
return $result;
|
|
}
|
|
|
|
protected function recipientOfMessage(int $idOfRecipient, int $msgId)
|
|
{
|
|
$sql = 'INSERT INTO msg_recipients (id, message_id, recipient_id)
|
|
VALUES (LAST_INSERT_ID(), :msgId, :idOfRecipient);';
|
|
|
|
$params = array(':msgId' => $msgId, ':idOfRecipient' => $idOfRecipient);
|
|
|
|
$stmt = $this->executeStmt($sql, $params);
|
|
}
|
|
|
|
protected function updateApprovalStatus($approvalStatus, $requirementId, $topicId)
|
|
{
|
|
$sql = 'UPDATE requirements_of_t
|
|
SET approval_status = :approvalstatus
|
|
WHERE id = :requirementid AND topic_id = :topicid;';
|
|
|
|
$params = array(':approvalstatus' => $approvalStatus, ':requirementid' => $requirementId, ':topicid' => $topicId);
|
|
|
|
$stmt = $this->executeStmt($sql, $params);
|
|
}
|
|
|
|
protected function updateScore(int $topicId, int $memberId, $categOfScore, int $vote)
|
|
{
|
|
$sql = 'UPDATE topics_members
|
|
SET ' . $categOfScore . ' = ' . $categOfScore . ' + :vote
|
|
WHERE topic_id = :topicId AND member_id = :memberId;';
|
|
|
|
$params = array(':vote' => $vote, ':topicId' => $topicId, ':memberId' => $memberId);
|
|
|
|
$stmt = $this->executeStmt($sql, $params);
|
|
}
|
|
|
|
protected function updateProposal($proposal, $editingPhaseDuration, $proposalId)
|
|
{
|
|
$editingPhaseDuration = $editingPhaseDuration*24*3600;
|
|
|
|
$sql = 'UPDATE proposals
|
|
SET proposal = :proposal, edit_closing_date = TIMESTAMPADD(SQL_TSI_SECOND,:editingPhaseDuration,CURRENT_TIMESTAMP)
|
|
WHERE id = :proposalId;';
|
|
|
|
$params = array(':proposal' => $proposal, ':editingPhaseDuration' => $editingPhaseDuration, ':proposalId' => $proposalId);
|
|
|
|
$stmt = $this->executeStmt($sql, $params);
|
|
}
|
|
|
|
protected function updateRatesOfData($id, $allowedOrRejected, $categoryName)
|
|
{
|
|
if ($allowedOrRejected != 'allowed' && $allowedOrRejected != 'rejected') {
|
|
return 'error';
|
|
}
|
|
|
|
if ($categoryName != 'users' && $categoryName != 'topics' && $categoryName != 'requirements' && $categoryName != 'proposals' && $categoryName != 'references' && $categoryName != 'similarities' && $categoryName != 'groups' && $categoryName != 'comments' && $categoryName != 'messages') {
|
|
return 'error';
|
|
}
|
|
|
|
$sql1 = 'UPDATE entry_rates_of_data ';
|
|
$sql2 = 'SET entries_of_' . $categoryName . '_' . $allowedOrRejected . ' = entries_of_' . $categoryName . '_' . $allowedOrRejected . ' + 1 ';
|
|
$sql3 = 'WHERE id = :id;';
|
|
|
|
$sql = $sql1 . $sql2 . $sql3;
|
|
|
|
$params = array(':id' => $id);
|
|
|
|
$stmt = $this->executeStmt($sql, $params);
|
|
|
|
return 'done';
|
|
}
|
|
|
|
//Checks if ids are integers greater or equal to 1. If not, it redirects to error page or exits with an error message if $redirectToErrorPage = null.
|
|
public function validateIds($arrayOfIds, $redirectToErrorPage)
|
|
{
|
|
$i = 0;
|
|
$results = [];
|
|
foreach ($arrayOfIds as $arrayOfIdsKey => $arrayOfIdsValue) {
|
|
if (intval($arrayOfIdsValue) < 1) {
|
|
if ($redirectToErrorPage != null) {
|
|
header($redirectToErrorPage);
|
|
exit();
|
|
} else {
|
|
exit("<br><br><b>-- Error: No valid ID!</b>");
|
|
}
|
|
}
|
|
|
|
$results[$i] = intval($arrayOfIdsValue);
|
|
++$i;
|
|
}
|
|
|
|
return $results;
|
|
}
|
|
|
|
public function convertArrayOfIdsToString($arrayOfIds, $columnName, $delimiterStr)
|
|
{
|
|
if ($arrayOfIds == []) {
|
|
return '0';
|
|
}
|
|
|
|
$arrayOfIdsAsStr = '';
|
|
foreach ($arrayOfIds as $arrayOfIdsKey => $arrayOfIdsValue) {
|
|
if ($columnName != null) {
|
|
$arrayOfIdsAsStr .= $delimiterStr . $arrayOfIdsValue[$columnName];
|
|
} else {
|
|
$arrayOfIdsAsStr .= $delimiterStr . $arrayOfIdsValue;
|
|
}
|
|
}
|
|
|
|
$lengthOfDelimiterStr = strlen($delimiterStr);
|
|
|
|
$result = substr($arrayOfIdsAsStr, $lengthOfDelimiterStr);
|
|
return $result;
|
|
}
|
|
|
|
public function convertStrOfIdsToArray($arrayOfIdsAsStr, $delimiterStr)
|
|
{
|
|
$arrayOfIds = explode($delimiterStr, $arrayOfIdsAsStr);
|
|
|
|
$i = 0;
|
|
$results = [];
|
|
foreach ($arrayOfIds as $arrayOfIdsKey => $arrayOfIdsValue) {
|
|
$temp = intval($arrayOfIdsValue);
|
|
if ($temp >= 1) {
|
|
$results[$i] = $temp;
|
|
++$i;
|
|
}
|
|
}
|
|
|
|
return $results;
|
|
}
|
|
|
|
public function addStrToStartAndEndOfString($str, $addedStr)
|
|
{
|
|
$result = $addedStr . $str . $addedStr;
|
|
|
|
return $result;
|
|
}
|
|
|
|
public function removeStrFromStartAndEndOfString($str, $removedStr)
|
|
{
|
|
$result = "";
|
|
|
|
$lengthOfRemovedStr = strlen($removedStr);
|
|
$newLength = strlen($str) - ($lengthOfRemovedStr + $lengthOfRemovedStr);
|
|
|
|
if ($newLength >= 0) {
|
|
$result = substr($str, $lengthOfRemovedStr, $newLength);
|
|
}
|
|
|
|
return $result;
|
|
}
|
|
} |