/** * * Gets a list of problems where current user is the owner * * @param Request $r */ public static function apiMyList(Request $r) { self::authenticateRequest($r); self::validateList($r); $response = array(); $response['results'] = array(); try { $problems = null; if (Authorization::IsSystemAdmin($r['current_user_id'])) { $problems = ProblemsDAO::getAll(null, null, 'problem_id', 'DESC'); } else { $problem_mask = new Problems(array('author_id' => $r['current_user_id'])); $problems = ProblemsDAO::search($problem_mask, 'problem_id', 'DESC', $r['offset'], $r['rowcount']); } foreach ($problems as $problem) { $problemArray = $problem->asArray(); $problemArray['tags'] = ProblemsDAO::getTagsForProblem($problem, false); array_push($response['results'], $problemArray); } } catch (Exception $e) { throw new InvalidDatabaseOperationException($e); } $response['status'] = 'ok'; return $response; }
public static final function byUserType($user_type, $order, $mode, $offset, $rowcount, $query, $user_id, $tag, &$total) { global $conn; // Just in case. if ($mode !== 'asc' && $mode !== 'desc') { $mode = 'desc'; } // Use BINARY mode to force case sensitive comparisons when ordering by title. $collation = $order === 'title' ? 'COLLATE utf8_bin' : ''; $select = ''; $sql = ''; $args = array(); if ($user_type === USER_ADMIN) { $args = array($user_id); $select = ' SELECT 100 / LOG2(GREATEST(accepted, 1) + 1) AS points, accepted / GREATEST(1, submissions) AS ratio, ROUND(100 * COALESCE(ps.score, 0)) AS score, p.*'; $sql = ' FROM Problems p LEFT JOIN ( SELECT Problems.problem_id, MAX(Runs.score) AS score FROM Problems INNER JOIN Runs ON Runs.user_id = ? AND Runs.problem_id = Problems.problem_id GROUP BY Problems.problem_id ) ps ON ps.problem_id = p.problem_id'; $added_where = false; if (!is_null($tag)) { $sql .= ' INNER JOIN Problems_Tags pt ON pt.problem_id = p.problem_id'; $sql .= ' INNER JOIN Tags t ON pt.tag_id = t.tag_id'; $sql .= ' WHERE t.name = ?'; $args[] = $tag; $added_where = true; } if (!is_null($query)) { if (!$added_where) { $sql .= ' WHERE'; } else { $sql .= ' AND'; } $sql .= " title LIKE CONCAT('%', ?, '%') "; $args[] = $query; } } elseif ($user_type === USER_NORMAL && !is_null($user_id)) { $select = ' SELECT 100 / LOG2(GREATEST(p.accepted, 1) + 1) AS points, p.accepted / GREATEST(1, p.submissions) AS ratio, ROUND(100 * COALESCE(ps.score, 0), 2) AS score, p.*'; $sql = ' FROM Problems p LEFT JOIN ( SELECT pi.problem_id, MAX(r.score) AS score FROM Problems pi INNER JOIN Runs r ON r.user_id = ? AND r.problem_id = pi.problem_id GROUP BY pi.problem_id ) ps ON ps.problem_id = p.problem_id LEFT JOIN User_Roles ur ON ur.user_id = ? AND p.problem_id = ur.contest_id LEFT JOIN Groups_Users gu ON gu.user_id = ? LEFT JOIN Group_Roles gr ON gr.group_id = gu.group_id AND p.problem_id = gr.contest_id'; $args[] = $user_id; $args[] = $user_id; $args[] = $user_id; if (!is_null($tag)) { $sql .= ' INNER JOIN Problems_Tags pt ON pt.problem_id = p.problem_id'; $sql .= ' INNER JOIN Tags t ON pt.tag_id = t.tag_id'; $sql .= ' WHERE t.name = ? AND pt.public = 1 AND'; $args[] = $tag; } else { $sql .= ' WHERE'; } $sql .= ' (p.public = 1 OR p.author_id = ? OR ur.role_id = 3 OR gr.role_id = 3) '; $args[] = $user_id; if (!is_null($query)) { $sql .= " AND p.title LIKE CONCAT('%', ?, '%')"; $args[] = $query; } } elseif ($user_type === USER_ANONYMOUS) { $select = ' SELECT 0 AS score, 100 / LOG2(GREATEST(p.accepted, 1) + 1) AS points, accepted / GREATEST(1, p.submissions) AS ratio, p.*'; $sql = ' FROM Problems p'; if (!is_null($tag)) { $sql .= ' INNER JOIN Problems_Tags pt ON pt.problem_id = p.problem_id'; $sql .= ' INNER JOIN Tags t ON pt.tag_id = t.tag_id'; $sql .= ' WHERE t.name = ? AND pt.public = 1 AND'; $args[] = $tag; } else { $sql .= ' WHERE'; } $sql .= ' p.public = 1 '; if (!is_null($query)) { $sql .= " AND p.title LIKE CONCAT('%', ?, '%') "; $args[] = $query; } } $total = $conn->GetOne("SELECT COUNT(*) {$sql}", $args); // Reset the offset to 0 if out of bounds. if ($offset < 0 || $offset > $total) { $offset = 0; } if ($order == 'problem_id') { $sql .= " ORDER BY p.problem_id {$collation} {$mode}"; } elseif ($order == 'points' && $mode == 'desc') { $sql .= ' ORDER BY `points` DESC, `accepted` ASC, `submissions` DESC'; } else { $sql .= " ORDER BY `{$order}` {$collation} {$mode}"; } $sql .= ' LIMIT ?, ?'; $args[] = $offset; $args[] = $rowcount; $result = $conn->Execute("{$select} {$sql}", $args); // Only these fields (plus score, points and ratio) will be returned. $filters = array('title', 'submissions', 'accepted', 'alias', 'public'); $problems = array(); if (!is_null($result)) { foreach ($result as $row) { $temp = new Problems($row); $problem = $temp->asFilteredArray($filters); // score, points and ratio are not actually fields of a Problems object. $problem['score'] = $row['score']; $problem['points'] = $row['points']; $problem['ratio'] = $row['ratio']; $problem['tags'] = ProblemsDAO::getTagsForProblem($temp, true); array_push($problems, $problem); } } return $problems; }
/** * * Gets a list of problems where current user is the owner * * @param Request $r */ public static function apiMyList(Request $r) { self::authenticateRequest($r); self::validateList($r); $response = array(); $response["results"] = array(); try { $problems = NULL; if (Authorization::IsSystemAdmin($r["current_user_id"])) { $problems = ProblemsDAO::getAll(NULL, NULL, "problem_id", 'DESC'); } else { $problem_mask = new Problems(array("author_id" => $r["current_user_id"])); $problems = ProblemsDAO::search($problem_mask, "problem_id", 'DESC', $r["offset"], $r["rowcount"]); } foreach ($problems as $problem) { $problemArray = $problem->asArray(); $problemArray['tags'] = ProblemsDAO::getTagsForProblem($problem, false); array_push($response["results"], $problemArray); } } catch (Exception $e) { throw new InvalidDatabaseOperationException($e); } $response["status"] = "ok"; return $response; }