/**
* @see DatabaseObjectList::readObjects()
*/
public function readObjects()
{
$userID = WCF::getUser()->userID;
$userID = $userID ? $userID : -1;
$sql = "SELECT\t\t" . (!empty($this->sqlSelects) ? $this->sqlSelects . ',' : '') . "\n\t\t\t\t\tavatar_table.*,\n\t\t\t\t\tcontest_participant.*,\n\t\t\t\t\tcontest_solution.*,\n\t\t\t\t\tcontest_price.priceID,\n\t\t\t\t\tgroup_table.groupName,\n\t\t\t\t\tuser_table.username,\n\t\t\t\t\tscore,\n\t\t\t\t\tcount,\n\t\t\t\t\tjuryscore,\n\t\t\t\t\tjurycount,\n\t\t\t\t\tmyscore\n\t\t\tFROM\t\twcf" . WCF_N . "_contest_solution contest_solution\n\t\t\tLEFT JOIN\twcf" . WCF_N . "_contest_participant contest_participant\n\t\t\tON\t\tcontest_participant.participantID = contest_solution.participantID\n\t\t\t\n\t\t\tLEFT JOIN (\n\t\t\t\tSELECT\t\tpriceID,\n\t\t\t\t\t\tsolutionID\n\t\t\t\tFROM\t\twcf" . WCF_N . "_contest_price contest_solution\n\t\t\t\t" . (!empty($this->sqlConditions) ? "WHERE " . $this->sqlConditions : '') . "\n\t\t\t\t\n\t\t\t) contest_price\tON (contest_price.solutionID = contest_solution.solutionID)\n\t\t\t\n\t\t\tLEFT JOIN (\n\t\t\t\t-- total score\n\t\t\t\tSELECT\t\tcontest_solution.solutionID,\n\t\t\t\t\t\tAVG(IF(score > 5, 5, score)) AS score,\n\t\t\t\t\t\tAVG(score) AS realscore,\n\t\t\t\t\t\tCOUNT(DISTINCT contest_solution_rating.userID) AS count\n\t\t\t\tFROM\t\twcf" . WCF_N . "_contest_solution contest_solution\n\t\t\t\tINNER JOIN\twcf" . WCF_N . "_contest_solution_rating contest_solution_rating\n\t\t\t\tON\t\tcontest_solution.solutionID = contest_solution_rating.solutionID\n\t\t\t\t" . (!empty($this->sqlConditions) ? "WHERE " . $this->sqlConditions : '') . "\n\t\t\t\tGROUP BY\tcontest_solution.solutionID\n\t\t\t\tHAVING\t\tNOT ISNULL(contest_solution.solutionID)\n\t\t\t) x ON contest_solution.solutionID = x.solutionID\n\t\t\t\n\t\t\tLEFT JOIN (\n\t\t\t\t-- jury score\n\t\t\t\tSELECT\t\tcontest_solution.solutionID,\n\t\t\t\t\t\tAVG(IF(score > 5, 5, score)) AS juryscore,\n\t\t\t\t\t\tAVG(score) AS juryrealscore,\n\t\t\t\t\t\tCOUNT(DISTINCT contest_solution_rating.userID) AS jurycount\n\t\t\t\tFROM\t\twcf" . WCF_N . "_contest_solution contest_solution\n\t\t\t\tINNER JOIN\twcf" . WCF_N . "_contest_solution_rating contest_solution_rating\n\t\t\t\tON\t\tcontest_solution.solutionID = contest_solution_rating.solutionID\n\t\t\t\tINNER JOIN (\n\t\t\t\t\tSELECT\t\tcontestID,\n\t\t\t\t\t\t\tIF(contest_jury.userID > 0, contest_jury.userID, user_to_groups.userID) AS userID\n\t\t\t\t\tFROM\t\twcf" . WCF_N . "_contest_jury contest_jury\n\t\t\t\t\tLEFT JOIN\twcf" . WCF_N . "_user_to_groups user_to_groups USING(groupID)\n\t\t\t\t\tWHERE\t\tcontest_jury.userID > 0\n\t\t\t\t\tAND\t\tcontest_jury.state = 'accepted'\n\t\t\t\t) contest_jury\n\t\t\t\tON\t\tcontest_jury.contestID = contest_solution.contestID\n\t\t\t\tAND\t\tcontest_jury.userID = contest_solution_rating.userID\n\t\t\t\t" . (!empty($this->sqlConditions) ? "WHERE (" . $this->sqlConditions . ')' : '') . "\n\t\t\t\tGROUP BY\tcontest_solution.solutionID\n\t\t\t\tHAVING\t\tNOT ISNULL(contest_solution.solutionID)\n\t\t\t) y ON contest_solution.solutionID = y.solutionID\n\t\t\t\n\t\t\tLEFT JOIN (\n\t\t\t\t-- my score\n\t\t\t\tSELECT\t\tcontest_solution.solutionID,\n\t\t\t\t\t\tAVG(IF(score > 5, 5, score)) AS myscore,\n\t\t\t\t\t\tAVG(score) AS myrealscore\n\t\t\t\tFROM\t\twcf" . WCF_N . "_contest_solution contest_solution\n\t\t\t\tINNER JOIN\twcf" . WCF_N . "_contest_solution_rating contest_solution_rating\n\t\t\t\tON\t\tcontest_solution.solutionID = contest_solution_rating.solutionID\n\t\t\t\tWHERE \t\tcontest_solution_rating.userID = " . $userID . "\n\t\t\t\t" . (!empty($this->sqlConditions) ? "AND (" . $this->sqlConditions . ')' : '') . "\n\t\t\t\tGROUP BY\tcontest_solution.solutionID\n\t\t\t\tHAVING\t\tNOT ISNULL(contest_solution.solutionID)\n\t\t\t) z ON contest_solution.solutionID = z.solutionID\n\t\t\t\n\t\t\tLEFT JOIN\twcf" . WCF_N . "_user user_table\n\t\t\tON\t\t(user_table.userID = contest_participant.userID)\n\t\t\tLEFT JOIN\twcf" . WCF_N . "_avatar avatar_table\n\t\t\tON\t\t(avatar_table.avatarID = user_table.avatarID)\n\t\t\tLEFT JOIN\twcf" . WCF_N . "_group group_table\n\t\t\tON\t\t(group_table.groupID = contest_participant.groupID)\n\t\t\t" . $this->sqlJoins . "\n\n\t\t\tWHERE\t\t(" . ContestSolution::getStateConditions() . ")\n\t\t\t" . (!empty($this->sqlConditions) ? "AND " . $this->sqlConditions : '') . "\n\t\t\t" . (!empty($this->sqlOrderBy) ? "ORDER BY " . $this->sqlOrderBy : '');
$result = WCF::getDB()->sendQuery($sql, $this->sqlLimit, $this->sqlOffset);
while ($row = WCF::getDB()->fetchArray($result)) {
$this->solutions[] = new ViewableContestSolution(null, $row);
}
}
/**
* Creates a new ViewableContest object.
*
* @param integer $solutionID
* @param array<mixed> $row
*/
public function __construct($solutionID, $row = null)
{
if ($solutionID !== null) {
$userID = WCF::getUser()->userID;
$userID = $userID ? $userID : -1;
$sql = "SELECT\t\tavatar_table.*, \n\t\t\t\t\t\tcontest_solution.*,\n\t\t\t\t\t\tcontest_price.priceID,\n\t\t\t\t\t\tcontest_participant.userID,\n\t\t\t\t\t\tcontest_participant.groupID,\n\t\t\t\t\t\tuser_table.username, \n\t\t\t\t\t\tgroup_table.groupName,\n\t\t\t\t\t\tscore,\n\t\t\t\t\t\tcount,\n\t\t\t\t\t\tjuryscore,\n\t\t\t\t\t\tjurycount,\n\t\t\t\t\t\tmyscore\n\t\t\t\tFROM \t\twcf" . WCF_N . "_contest_solution contest_solution\n\t\t\t\tLEFT JOIN\twcf" . WCF_N . "_contest_participant contest_participant\n\t\t\t\tON\t\t(contest_participant.participantID = contest_solution.participantID)\n\t\t\t\n\t\t\t\tLEFT JOIN\twcf" . WCF_N . "_contest_price contest_price\n\t\t\t\tON\t\tcontest_price.contestID = contest_solution.contestID\n\t\t\t\tAND\t\tcontest_price.solutionID = contest_solution.solutionID\n\t\t\t\t\n\t\t\t\tLEFT JOIN (\n\t\t\t\t\t-- total score\n\t\t\t\t\tSELECT\t\tcontest_solution.solutionID,\n\t\t\t\t\t\t\tAVG(IF(score > 5, 5, score)) AS score,\n\t\t\t\t\t\t\tAVG(score) AS realscore,\n\t\t\t\t\t\t\tCOUNT(DISTINCT contest_solution_rating.userID) AS count\n\t\t\t\t\tFROM\t\twcf" . WCF_N . "_contest_solution contest_solution\n\t\t\t\t\tINNER JOIN\twcf" . WCF_N . "_contest_solution_rating contest_solution_rating\n\t\t\t\t\tON\t\tcontest_solution.solutionID = contest_solution_rating.solutionID\n\t\t\t\t\tWHERE \t\tcontest_solution.solutionID = " . intval($solutionID) . "\n\t\t\t\t\tGROUP BY\tcontest_solution.solutionID\n\t\t\t\t\tHAVING\t\tNOT ISNULL(contest_solution.solutionID)\n\t\t\t\t) x ON contest_solution.solutionID = x.solutionID\n\t\t\t\n\t\t\t\tLEFT JOIN (\n\t\t\t\t\t-- jury score\n\t\t\t\t\tSELECT\t\tcontest_solution.solutionID,\n\t\t\t\t\t\t\tAVG(IF(score > 5, 5, score)) AS juryscore,\n\t\t\t\t\t\t\tAVG(score) AS juryrealscore,\n\t\t\t\t\t\t\tCOUNT(DISTINCT contest_solution_rating.userID) AS jurycount\n\t\t\t\t\tFROM\t\twcf" . WCF_N . "_contest_solution contest_solution\n\t\t\t\t\tINNER JOIN\twcf" . WCF_N . "_contest_solution_rating contest_solution_rating\n\t\t\t\t\tON\t\tcontest_solution.solutionID = contest_solution_rating.solutionID\n\t\t\t\t\tINNER JOIN\twcf" . WCF_N . "_contest_jury contest_jury\n\t\t\t\t\tON\t\tcontest_jury.userID = contest_solution_rating.userID\n\t\t\t\t\tWHERE \t\tcontest_solution.solutionID = " . intval($solutionID) . "\n\t\t\t\t\tAND \t\tcontest_jury.state = 'accepted'\n\t\t\t\t\tGROUP BY\tcontest_solution.solutionID\n\t\t\t\t\tHAVING\t\tNOT ISNULL(contest_solution.solutionID)\n\t\t\t\t) y ON contest_solution.solutionID = y.solutionID\n\t\t\t\n\t\t\t\tLEFT JOIN (\n\t\t\t\t\t-- my score\n\t\t\t\t\tSELECT\t\tcontest_solution.solutionID,\n\t\t\t\t\t\t\tAVG(IF(score > 5, 5, score)) AS myscore,\n\t\t\t\t\t\t\tAVG(score) AS myrealscore\n\t\t\t\t\tFROM\t\twcf" . WCF_N . "_contest_solution contest_solution\n\t\t\t\t\tINNER JOIN\twcf" . WCF_N . "_contest_solution_rating contest_solution_rating\n\t\t\t\t\tON\t\tcontest_solution.solutionID = contest_solution_rating.solutionID\n\t\t\t\t\tWHERE \t\tcontest_solution.solutionID = " . intval($solutionID) . "\n\t\t\t\t\tAND \t\tcontest_solution_rating.userID = " . $userID . "\n\t\t\t\t\tGROUP BY\tcontest_solution.solutionID\n\t\t\t\t\tHAVING\t\tNOT ISNULL(contest_solution.solutionID)\n\t\t\t\t) z ON contest_solution.solutionID = z.solutionID\n\t\t\t\t\n\t\t\t\tLEFT JOIN\twcf" . WCF_N . "_user user_table\n\t\t\t\tON\t\t(user_table.userID = contest_participant.userID)\n\t\t\t\tLEFT JOIN\twcf" . WCF_N . "_avatar avatar_table\n\t\t\t\tON\t\t(avatar_table.avatarID = user_table.avatarID)\n\t\t\t\tLEFT JOIN\twcf" . WCF_N . "_group group_table\n\t\t\t\tON\t\t(group_table.groupID = contest_participant.groupID)\n\t\t\t\tWHERE \t\tcontest_solution.solutionID = " . intval($solutionID) . "\n\t\t\t\tAND\t\t(" . ContestSolution::getStateConditions() . ")";
$row = WCF::getDB()->getFirstRow($sql);
}
DatabaseObject::__construct($row);
}
