Exemple #1
0
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)
    )
Exemple #2
0
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)
Exemple #3
0
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)
Exemple #4
0
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))
Exemple #5
0
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)
Exemple #6
0
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)
Exemple #7
0
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)
Exemple #8
0
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")
Exemple #9
0
def bad(question):
    query_db("UPDATE questions SET failures = failures + 1 WHERE id = ?", (question,))
    return jsonify(success=True)
Exemple #10
0
def good(question):
    query_db("UPDATE questions SET success=success+1 WHERE id = ?", (question,))
    return jsonify(success=True)
Exemple #11
0
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)
Exemple #12
0
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')
Exemple #13
0
def bad(question):
    query_db("UPDATE questions SET failures = failures + 1 WHERE id = ?",
             (question, ))
    return jsonify(success=True)
Exemple #14
0
def good(question):
    query_db("UPDATE questions SET success=success+1 WHERE id = ?",
             (question, ))
    return jsonify(success=True)