<commit_before>import os
from django.core.management.base import BaseCommand
from uw_sws.term import get_current_term
from data_aggregator.utilities import get_week_of_term, get_view_name
from django.db import connection


def create(sis_term_id, week):
    """
    Create rad db view for given week and sis-term-id
    """

    view_name = get_view_name(sis_term_id, week, "rad")
    assignments_view_name = get_view_name(sis_term_id,
                                          week,
                                          "assignments")
    participations_view_name = get_view_name(sis_term_id,
                                             week,
                                             "participations")

    cursor = connection.cursor()

    env = os.getenv("ENV")
    if env == "localdev" or not env:
        create_action = (
            'CREATE VIEW "{view_name}"'.format(view_name=view_name))
        cursor.execute(
            'DROP VIEW IF EXISTS "{view_name}"'.format(view_name=view_name)
        )
    else:
        create_action = (
            'CREATE OR REPLACE VIEW "{view_name}"'.format(view_name=view_name))

    cursor.execute(
        '''
        {create_action} AS
        SELECT DISTINCT
            u.canvas_user_id,
            u.full_name,
            '{sis_term_id}' as term,
            {week} as week,
            assignment_score,
            participation_score,
            grade
        FROM
        (
            SELECT
                norm_ra.user_id,
                AVG(normalized_assignment_score) AS assignment_score,
                AVG(normalized_participation_score) AS participation_score
            FROM
            (
                SELECT
                    p1.user_id,
                    p1.course_id,
                    p1.week_id,
                        coalesce(
                            cast((p1.participations - min_raw_participation_score) as decimal) /
                            NULLIF( cast((max_raw_participation_score - min_raw_participation_score) as decimal) / 10, 0),
                        0) - 5
                    AS normalized_participation_score,
                        coalesce(
                            cast(((2 * p1.time_on_time + p1.time_late) - min_raw_assignment_score) as decimal) /
                            NULLIF( cast((max_raw_assignment_score - min_raw_assignment_score) as decimal) / 10, 0)
                                , 0) - 5
                    AS normalized_assignment_score
                FROM `{participations_view_name}` p1
                JOIN (
                    SELECT
                        course_id,
                        MIN(p2.participations) AS min_raw_participation_score,
                        MAX(p2.participations) AS max_raw_participation_score,
                        MIN(2 * p2.time_on_time + p2.time_late) AS min_raw_assignment_score,
                        MAX(2 * p2.time_on_time + p2.time_late) AS max_raw_assignment_score
                    FROM `{participations_view_name}` p2
                    GROUP BY 
                        course_id
                ) ra ON p1.course_id  = ra.course_id
                GROUP BY
                    p1.user_id,
                    p1.course_id,
                    p1.week_id,
                    participations,
                    p1.time_on_time,
                    p1.time_late,
                    normalized_participation_score,
                    normalized_assignment_score
            ) norm_ra
            GROUP BY
                norm_ra.user_id
        ) AS p JOIN 
        (
            SELECT DISTINCT
                a1.user_id,
                AVG(normalized_score) AS grade
            FROM (
            SELECT
                a2.user_id,
                CASE
                WHEN (COALESCE(a2.max_score, 0) - COALESCE(a2.min_score, 0)) = 0 THEN 0
                WHEN a2.score  IS NULL THEN -5
                ELSE ((10 * (COALESCE(a2.score, 0) - COALESCE(a2.min_score, 0))) /
                        (COALESCE(a2.max_score, 0) - COALESCE(a2.min_score, 0))) - 5
                END AS normalized_score
            FROM `{assignments_view_name}` a2
            WHERE a2.status = 'on_time' OR a2.status = 'late' OR a2.status = 'missing'
            GROUP BY a2.user_id, normalized_score 
            ) a1
            GROUP BY
                a1.user_id
        ) AS a
        ON p.user_id = a.user_id
        JOIN data_aggregator_user u ON p.user_id = u.id
        '''  # noqa
        .format(
            create_action=create_action,
            week=week,
            sis_term_id=sis_term_id,
            participations_view_name=participations_view_name,
            assignments_view_name=assignments_view_name)
    )
    return True


class Command(BaseCommand):

    help = ("Creates RAD db view for given week.")

    def handle(self, *args, **options):
        """
        Create rad db view for current week
        """
        sws_term = get_current_term()
        sis_term_id = sws_term.canvas_sis_id()
        week = get_week_of_term(sws_term.first_day_quarter)
        create(sis_term_id, week)
<commit_msg>Fix rad db view query string<commit_after>import os
from django.core.management.base import BaseCommand
from uw_sws.term import get_current_term
from data_aggregator.utilities import get_week_of_term, get_view_name
from django.db import connection


def create(sis_term_id, week):
    """
    Create rad db view for given week and sis-term-id
    """

    view_name = get_view_name(sis_term_id, week, "rad")
    assignments_view_name = get_view_name(sis_term_id,
                                          week,
                                          "assignments")
    participations_view_name = get_view_name(sis_term_id,
                                             week,
                                             "participations")

    cursor = connection.cursor()

    env = os.getenv("ENV")
    if env == "localdev" or not env:
        create_action = (
            'CREATE VIEW "{view_name}"'.format(view_name=view_name))
        cursor.execute(
            'DROP VIEW IF EXISTS "{view_name}"'.format(view_name=view_name)
        )
    else:
        create_action = (
            'CREATE OR REPLACE VIEW "{view_name}"'.format(view_name=view_name))

    cursor.execute(
        '''
        {create_action} AS
        SELECT DISTINCT
            u.canvas_user_id,
            u.full_name,
            '{sis_term_id}' as term,
            {week} as week,
            assignment_score,
            participation_score,
            grade
        FROM
        (
            SELECT
                norm_ra.user_id,
                AVG(normalized_assignment_score) AS assignment_score,
                AVG(normalized_participation_score) AS participation_score
            FROM
            (
                SELECT
                    p1.user_id,
                    p1.course_id,
                    p1.week_id,
                        coalesce(
                            cast((p1.participations - min_raw_participation_score) as decimal) /
                            NULLIF( cast((max_raw_participation_score - min_raw_participation_score) as decimal) / 10, 0),
                        0) - 5
                    AS normalized_participation_score,
                        coalesce(
                            cast(((2 * p1.time_on_time + p1.time_late) - min_raw_assignment_score) as decimal) /
                            NULLIF( cast((max_raw_assignment_score - min_raw_assignment_score) as decimal) / 10, 0)
                                , 0) - 5
                    AS normalized_assignment_score
                FROM "{participations_view_name}" p1
                JOIN (
                    SELECT
                        course_id,
                        MIN(p2.participations) AS min_raw_participation_score,
                        MAX(p2.participations) AS max_raw_participation_score,
                        MIN(2 * p2.time_on_time + p2.time_late) AS min_raw_assignment_score,
                        MAX(2 * p2.time_on_time + p2.time_late) AS max_raw_assignment_score
                    FROM "{participations_view_name}" p2
                    GROUP BY 
                        course_id
                ) ra ON p1.course_id  = ra.course_id
                GROUP BY
                    p1.user_id,
                    p1.course_id,
                    p1.week_id,
                    participations,
                    p1.time_on_time,
                    p1.time_late,
                    normalized_participation_score,
                    normalized_assignment_score
            ) norm_ra
            GROUP BY
                norm_ra.user_id
        ) AS p JOIN 
        (
            SELECT DISTINCT
                a1.user_id,
                AVG(normalized_score) AS grade
            FROM (
            SELECT
                a2.user_id,
                CASE
                WHEN (COALESCE(a2.max_score, 0) - COALESCE(a2.min_score, 0)) = 0 THEN 0
                WHEN a2.score  IS NULL THEN -5
                ELSE ((10 * (COALESCE(a2.score, 0) - COALESCE(a2.min_score, 0))) /
                        (COALESCE(a2.max_score, 0) - COALESCE(a2.min_score, 0))) - 5
                END AS normalized_score
            FROM "{assignments_view_name}" a2
            WHERE a2.status = 'on_time' OR a2.status = 'late' OR a2.status = 'missing'
            GROUP BY a2.user_id, normalized_score 
            ) a1
            GROUP BY
                a1.user_id
        ) AS a
        ON p.user_id = a.user_id
        JOIN data_aggregator_user u ON p.user_id = u.id
        '''  # noqa
        .format(
            create_action=create_action,
            week=week,
            sis_term_id=sis_term_id,
            participations_view_name=participations_view_name,
            assignments_view_name=assignments_view_name)
    )
    return True


class Command(BaseCommand):

    help = ("Creates RAD db view for given week.")

    def handle(self, *args, **options):
        """
        Create rad db view for current week
        """
        sws_term = get_current_term()
        sis_term_id = sws_term.canvas_sis_id()
        week = get_week_of_term(sws_term.first_day_quarter)
        create(sis_term_id, week)

{'fraction_non_alphanumeric': 0.07054467504628276, 'fraction_numerical': 0.011887362369677482, 'mean_word_length': 1.3391066545123063, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 3, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 18, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '6538023', 'n_tokens_mistral': 3073, 'n_tokens_neox': 2851, 'n_words': 585}