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)