def index():
courses = query_db("SELECT * FROM cours")
parties = {}
parties_cours = query_db("SELECT * FROM partie_cours")
for partie in parties_cours:
if partie["cours_id"] not in parties:
parties[partie["cours_id"]] = []
parties[partie["cours_id"]].append(partie)
# Requête récursive pour trouver les catégories, les sous-catégories, les sous-sous-catégories...
categories = query_db(
"""
WITH RECURSIVE children_categories(id, name, level) AS (
SELECT id, name, 0
FROM categories WHERE category_id IS NULL
UNION ALL
SELECT categories.id, categories.name, children_categories.level + 1
FROM categories
JOIN children_categories ON children_categories.id=categories.category_id
ORDER BY children_categories.level + 1 DESC
) SELECT * FROM children_categories"""
)
return render_template(
"index.html", courses=courses, parties=parties, categories=categories, len_categories=len(categories)
)
def search():
pattern = request.form["pattern"]
op = " AND " if request.form["all"] == "true" else " OR "
if not pattern:
return jsonify(success=True)
words = list(map(str.lower, map(lambda x: x if "%" in x else "%" + x + "%", pattern.split(" "))))
questions = query_db(
"SELECT id, content AS text FROM questions WHERE "
+ op.join(["LOWER(content) LIKE ?" for i in range(len(words))])
+ " LIMIT 10",
words,
)
categories = query_db(
"SELECT id, name AS text FROM categories WHERE "
+ op.join(["LOWER(name) LIKE ?" for i in range(len(words))])
+ " LIMIT 10",
words,
)
cours = query_db(
"SELECT sigle AS id, name AS text FROM cours WHERE "
+ op.join(["LOWER(name) LIKE ?" for i in range(len(words))])
+ " LIMIT 10",
words,
)
parties_cours = query_db(
"SELECT id, name AS text FROM partie_cours WHERE "
+ op.join(["LOWER(name) LIKE ?" for i in range(len(words))])
+ " LIMIT 10",
words,
)
return jsonify(success=True, questions=questions, categories=categories, cours=cours, partie_cours=parties_cours)
def search():
pattern = request.form['pattern']
op = ' AND ' if request.form['all'] == 'true' else ' OR '
if not pattern:
return jsonify(success=True)
words = list(
map(
str.lower,
map(lambda x: x
if '%' in x else '%' + x + '%', pattern.split(' '))))
questions = query_db(
'SELECT id, content AS text FROM questions WHERE ' +
op.join(['LOWER(content) LIKE ?'
for i in range(len(words))]) + ' LIMIT 10', words)
categories = query_db(
'SELECT id, name AS text FROM categories WHERE ' +
op.join(['LOWER(name) LIKE ?'
for i in range(len(words))]) + ' LIMIT 10', words)
cours = query_db(
'SELECT sigle AS id, name AS text FROM cours WHERE ' +
op.join(['LOWER(name) LIKE ?'
for i in range(len(words))]) + ' LIMIT 10', words)
parties_cours = query_db(
'SELECT id, name AS text FROM partie_cours WHERE ' +
op.join(['LOWER(name) LIKE ?'
for i in range(len(words))]) + ' LIMIT 10', words)
return jsonify(success=True,
questions=questions,
categories=categories,
cours=cours,
partie_cours=parties_cours)
def index():
courses = query_db("SELECT * FROM cours")
parties = {}
parties_cours = query_db("SELECT * FROM partie_cours")
for partie in parties_cours:
if partie["cours_id"] not in parties:
parties[partie["cours_id"]] = []
parties[partie["cours_id"]].append(partie)
# Requête récursive pour trouver les catégories, les sous-catégories, les sous-sous-catégories...
categories = query_db("""
WITH RECURSIVE children_categories(id, name, level) AS (
SELECT id, name, 0
FROM categories WHERE category_id IS NULL
UNION ALL
SELECT categories.id, categories.name, children_categories.level + 1
FROM categories
JOIN children_categories ON children_categories.id=categories.category_id
ORDER BY children_categories.level + 1 DESC
) SELECT * FROM children_categories""")
return render_template('index.html',
courses=courses,
parties=parties,
categories=categories,
len_categories=len(categories))
def stats():
stats = [
("Réponses par cours",
query_db("""
SELECT sigle AS `Sigle`, cours.name AS `Nom`, COUNT(reponses.id) AS `Nombre de réponses`
FROM cours
JOIN partie_cours ON partie_cours.cours_id=cours.sigle
JOIN questions ON partie_cours.id=questions.partie_cours_id
JOIN reponses ON reponses.question_id=questions.id
GROUP BY cours.sigle
ORDER BY cours.sigle""")),
("Moyenne de questions par cours",
query_db("""
SELECT AVG(cnt) AS `Nombre moyen` FROM (
SELECT COUNT(questions.id) as cnt
FROM cours
JOIN partie_cours ON partie_cours.cours_id=cours.sigle
JOIN questions ON partie_cours.id=questions.partie_cours_id
GROUP BY cours.sigle
)""")),
("Nombre de questions par professeur",
query_db("""
SELECT prenom AS `Prénom`, nom AS `Nom`, COUNT(questions.id) AS `Nombre de questions` FROM professeurs AS professeur
JOIN professeur_cours ON professeur.id=professeur_cours.professeur_id
JOIN cours ON professeur_cours.cours_id=cours.sigle
JOIN partie_cours ON partie_cours.cours_id=cours.sigle
JOIN questions ON questions.partie_cours_id=partie_cours.id
GROUP BY professeur.id""")),
]
return render_template('stats.html', stats=stats)
def question(by, id=None):
questions = []
if by == "cours":
questions = query_db(
"""
SELECT * FROM questions
WHERE partie_cours_id IN (
SELECT id FROM partie_cours
WHERE cours_id=?
)""",
(id,),
)
elif by == "partie_cours":
questions = query_db("SELECT * FROM questions WHERE partie_cours_id=?", (id,))
elif by == "categories":
categories = query_db(
"""
WITH RECURSIVE children_categories(id, name, level) AS (
SELECT id, name, 0
FROM categories WHERE category_id = ?
UNION ALL
SELECT categories.id, categories.name, children_categories.level + 1
FROM categories
JOIN children_categories ON children_categories.id=categories.category_id
ORDER BY children_categories.level + 1 DESC
) SELECT * FROM children_categories UNION SELECT id, name, 0 FROM categories WHERE id = ?""",
(id, id),
)
questions = query_db(
"""
SELECT * FROM questions AS question
JOIN question_categories
ON question_categories.question_id=question.id AND question_categories.category_id IN ("""
+ ", ".join([str(x["id"]) for x in categories])
+ ")"
)
elif by == "questions":
questions = query_db("SELECT * FROM questions WHERE id=?", (id,))
categories = query_db(
"""
WITH RECURSIVE parent_categories(category_id, name, level) AS (
SELECT category_id, name, 0
FROM categories WHERE id=?
UNION ALL
SELECT categories.category_id, categories.name, parent_categories.level + 1
FROM categories
JOIN parent_categories ON parent_categories.category_id=categories.id
) SELECT category_id FROM parent_categories UNION SELECT ?""",
(id, id),
)
categories = [str(i["category_id"]) for i in categories if i["category_id"] is not None]
question = random.choice(questions)
reponses = query_db("SELECT * FROM reponses WHERE question_id=? ORDER BY RANDOM()", (question["id"],))
return render_template("question.html", question=question, reponses=reponses)
def stats():
stats = [
(
"Réponses par cours",
query_db(
"""
SELECT sigle AS `Sigle`, cours.name AS `Nom`, COUNT(reponses.id) AS `Nombre de réponses`
FROM cours
JOIN partie_cours ON partie_cours.cours_id=cours.sigle
JOIN questions ON partie_cours.id=questions.partie_cours_id
JOIN reponses ON reponses.question_id=questions.id
GROUP BY cours.sigle
ORDER BY cours.sigle"""
),
),
(
"Moyenne de questions par cours",
query_db(
"""
SELECT AVG(cnt) AS `Nombre moyen` FROM (
SELECT COUNT(questions.id) as cnt
FROM cours
JOIN partie_cours ON partie_cours.cours_id=cours.sigle
JOIN questions ON partie_cours.id=questions.partie_cours_id
GROUP BY cours.sigle
)"""
),
),
(
"Nombre de questions par professeur",
query_db(
"""
SELECT prenom AS `Prénom`, nom AS `Nom`, COUNT(questions.id) AS `Nombre de questions` FROM professeurs AS professeur
JOIN professeur_cours ON professeur.id=professeur_cours.professeur_id
JOIN cours ON professeur_cours.cours_id=cours.sigle
JOIN partie_cours ON partie_cours.cours_id=cours.sigle
JOIN questions ON questions.partie_cours_id=partie_cours.id
GROUP BY professeur.id"""
),
),
]
return render_template("stats.html", stats=stats)
def prof():
if "nom" in request.form and "pwd" in request.form and "prenom" in request.form:
prof = query_db(
"SELECT * FROM professeurs WHERE nom=? AND mot_de_passe=? AND prenom=?",
(request.form["nom"], request.form["pwd"], request.form["prenom"]),
)
if len(prof) == 1:
prof = prof[0]
cours = query_db(
"""
SELECT COUNT(questions.id) AS nbr_questions, cours.name AS name,
cours.sigle AS id, cours.sigle AS sigle
FROM cours
JOIN professeur_cours ON professeur_cours.professeur_id=?
AND cours.sigle=professeur_cours.cours_id
JOIN partie_cours ON partie_cours.cours_id=cours.sigle
JOIN questions ON questions.partie_cours_id=partie_cours.id
GROUP BY cours.sigle""",
(prof["id"],),
)
moyenne_reussite = query_db(
"""
SELECT ((SUM(success)*100)/NULLIF(SUM(success) + SUM(failures), 0)) AS average_reussite
FROM questions
JOIN professeur_cours ON professeur_cours.professeur_id = ?
JOIN partie_cours ON questions.partie_cours_id = partie_cours.id
JOIN cours ON partie_cours.cours_id=cours.sigle
WHERE cours.sigle=professeur_cours.cours_id""",
[prof["id"]],
True,
)
taux_reussite_questions = query_db(
"""
SELECT sigle, content, (success*100)/MAX(success + failures, 1) AS rapport, (success + failures) AS nbr_answers
FROM questions
JOIN partie_cours ON questions.partie_cours_id = partie_cours.id
JOIN cours ON partie_cours.cours_id=cours.sigle
JOIN professeur_cours ON professeur_cours.professeur_id = ?
AND cours.sigle=professeur_cours.cours_id
ORDER BY rapport DESC, nbr_answers DESC""",
[prof["id"]],
)
collegues = query_db(
"""
SELECT * FROM professeurs AS professeur
JOIN professeur_cours ON professeur_cours.professeur_id=professeur.id
JOIN cours ON professeur_cours.cours_id=cours.sigle
WHERE cours.sigle IN (
SELECT cours_id FROM professeurs
JOIN professeur_cours AS pc ON pc.professeur_id=professeurs.id AND professeur_id=?
)
AND professeur.id != ?""",
(prof["id"], prof["id"]),
)
taux_reussite_questions_cours = {}
for taux_reussite_question in taux_reussite_questions:
sub_content = (
taux_reussite_question["content"]
if len(taux_reussite_question["content"]) < 55
else taux_reussite_question["content"][0:55] + "..."
)
if taux_reussite_question["sigle"] not in taux_reussite_questions_cours:
taux_reussite_questions_cours[taux_reussite_question["sigle"]] = []
taux_reussite_questions_cours[taux_reussite_question["sigle"]].append(
[
taux_reussite_question["content"],
taux_reussite_question["rapport"],
sub_content,
taux_reussite_question["nbr_answers"],
]
)
return render_template(
"prof.html",
prof=prof,
courses=cours,
moyenne_reussite=moyenne_reussite,
taux_reussite_questions_cours=taux_reussite_questions_cours,
collegues=collegues,
)
return render_template("prof-login.html")
def bad(question):
query_db("UPDATE questions SET failures = failures + 1 WHERE id = ?", (question,))
return jsonify(success=True)
def good(question):
query_db("UPDATE questions SET success=success+1 WHERE id = ?", (question,))
return jsonify(success=True)
def question(by, id=None):
questions = []
if by == "cours":
questions = query_db(
"""
SELECT * FROM questions
WHERE partie_cours_id IN (
SELECT id FROM partie_cours
WHERE cours_id=?
)""", (id, ))
elif by == "partie_cours":
questions = query_db("SELECT * FROM questions WHERE partie_cours_id=?",
(id, ))
elif by == "categories":
categories = query_db(
"""
WITH RECURSIVE children_categories(id, name, level) AS (
SELECT id, name, 0
FROM categories WHERE category_id = ?
UNION ALL
SELECT categories.id, categories.name, children_categories.level + 1
FROM categories
JOIN children_categories ON children_categories.id=categories.category_id
ORDER BY children_categories.level + 1 DESC
) SELECT * FROM children_categories UNION SELECT id, name, 0 FROM categories WHERE id = ?""",
(id, id))
questions = query_db("""
SELECT * FROM questions AS question
JOIN question_categories
ON question_categories.question_id=question.id AND question_categories.category_id IN ("""
+ ', '.join([str(x['id'])
for x in categories]) + ')')
elif by == "questions":
questions = query_db("SELECT * FROM questions WHERE id=?", (id, ))
categories = query_db(
"""
WITH RECURSIVE parent_categories(category_id, name, level) AS (
SELECT category_id, name, 0
FROM categories WHERE id=?
UNION ALL
SELECT categories.category_id, categories.name, parent_categories.level + 1
FROM categories
JOIN parent_categories ON parent_categories.category_id=categories.id
) SELECT category_id FROM parent_categories UNION SELECT ?""",
(id, id))
categories = [
str(i['category_id']) for i in categories
if i['category_id'] is not None
]
question = random.choice(questions)
reponses = query_db(
"SELECT * FROM reponses WHERE question_id=? ORDER BY RANDOM()",
(question["id"], ))
return render_template('question.html',
question=question,
reponses=reponses)
def prof():
if 'nom' in request.form and 'pwd' in request.form and 'prenom' in request.form:
prof = query_db(
"SELECT * FROM professeurs WHERE nom=? AND mot_de_passe=? AND prenom=?",
(request.form['nom'], request.form['pwd'], request.form['prenom']))
if len(prof) == 1:
prof = prof[0]
cours = query_db(
"""
SELECT COUNT(questions.id) AS nbr_questions, cours.name AS name,
cours.sigle AS id, cours.sigle AS sigle
FROM cours
JOIN professeur_cours ON professeur_cours.professeur_id=?
AND cours.sigle=professeur_cours.cours_id
JOIN partie_cours ON partie_cours.cours_id=cours.sigle
JOIN questions ON questions.partie_cours_id=partie_cours.id
GROUP BY cours.sigle""", (prof['id'], ))
moyenne_reussite = query_db(
"""
SELECT ((SUM(success)*100)/NULLIF(SUM(success) + SUM(failures), 0)) AS average_reussite
FROM questions
JOIN professeur_cours ON professeur_cours.professeur_id = ?
JOIN partie_cours ON questions.partie_cours_id = partie_cours.id
JOIN cours ON partie_cours.cours_id=cours.sigle
WHERE cours.sigle=professeur_cours.cours_id""", [prof['id']],
True)
taux_reussite_questions = query_db(
"""
SELECT sigle, content, (success*100)/MAX(success + failures, 1) AS rapport, (success + failures) AS nbr_answers
FROM questions
JOIN partie_cours ON questions.partie_cours_id = partie_cours.id
JOIN cours ON partie_cours.cours_id=cours.sigle
JOIN professeur_cours ON professeur_cours.professeur_id = ?
AND cours.sigle=professeur_cours.cours_id
ORDER BY rapport DESC, nbr_answers DESC""", [prof['id']])
collegues = query_db(
"""
SELECT * FROM professeurs AS professeur
JOIN professeur_cours ON professeur_cours.professeur_id=professeur.id
JOIN cours ON professeur_cours.cours_id=cours.sigle
WHERE cours.sigle IN (
SELECT cours_id FROM professeurs
JOIN professeur_cours AS pc ON pc.professeur_id=professeurs.id AND professeur_id=?
)
AND professeur.id != ?""", (prof['id'], prof['id']))
taux_reussite_questions_cours = {}
for taux_reussite_question in taux_reussite_questions:
sub_content = taux_reussite_question["content"] if len(
taux_reussite_question["content"]
) < 55 else taux_reussite_question["content"][0:55] + "..."
if taux_reussite_question[
"sigle"] not in taux_reussite_questions_cours:
taux_reussite_questions_cours[
taux_reussite_question["sigle"]] = []
taux_reussite_questions_cours[
taux_reussite_question["sigle"]].append([
taux_reussite_question["content"],
taux_reussite_question["rapport"], sub_content,
taux_reussite_question['nbr_answers']
])
return render_template(
'prof.html',
prof=prof,
courses=cours,
moyenne_reussite=moyenne_reussite,
taux_reussite_questions_cours=taux_reussite_questions_cours,
collegues=collegues)
return render_template('prof-login.html')
def bad(question):
query_db("UPDATE questions SET failures = failures + 1 WHERE id = ?",
(question, ))
return jsonify(success=True)
def good(question):
query_db("UPDATE questions SET success=success+1 WHERE id = ?",
(question, ))
return jsonify(success=True)