public function removeStar($level, $lesson)
{
$sql = 'DELETE FROM star WHERE user_id = :user_id AND level = :level AND lesson = :lesson';
$stmt = PdoFactory::getInstance()->prepare($sql);
$stmt->bindValue(':user_id', $this->id, PDO::PARAM_INT);
$stmt->bindValue(':level', $level, PDO::PARAM_INT);
$stmt->bindValue(':lesson', $lesson, PDO::PARAM_INT);
$stmt->execute();
return $stmt->rowCount();
}
public static function calculateLeaderboard($limit = 5, $join_year = null, $join_month = null)
{
$where_clause = '';
if ($join_year !== null) {
$where_clause .= ' AND YEAR(student.added) = :year';
}
if ($join_month !== null) {
$where_clause .= ' AND MONTH(student.added) = :month';
}
$sql = <<<SQL
\t\t\tSELECT
\t\t\t\tstudent.user_id,
\t\t\t\tstudent.name_f,
\t\t\t\tstudent.name_l,
\t\t\t\tstudent.country,
\t\t\t\tSUM(score.points) AS total_points
\t\t\tFROM
\t\t\t\tamember4.am_user AS student,
\t\t\t\tpathtoarabic.test_result AS score
\t\t\tWHERE student.user_id = score.user_id {$where_clause}
\t\t\tGROUP BY student.user_id
\t\t\tORDER BY total_points DESC
SQL;
$stmt = PdoFactory::getInstance()->prepare($sql);
$stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
if ($join_year !== null) {
$stmt->bindValue(':year', $join_year, PDO::PARAM_INT);
}
if ($join_month !== null) {
$stmt->bindValue(':month', $join_month, PDO::PARAM_INT);
}
$stmt->execute();
for ($i = 0; $i < count($board); $i++) {
$board[$i]['rank'] = $i + 1;
}
return $board;
}