<commit_before>"""
    Loop Dashboard utils

    Contains methods to process data for dashboards dynamically

    Todo:
    - Pandas has bug that is stopping the database from being connected to using a connection string from the settings.py file

"""

from django.db import connections
from datetime import datetime, date, timedelta
import time
from cloop.models import Course, CourseRepeatingEvent, CourseSingleEvent, CourseSubmissionEvent
import json
import pandas as pd
from sqlalchemy import create_engine
from collections import defaultdict


def get_contenttypes(course_type):
    communication_types = None
    assessment_types = None
    if course_type == "Moodle":
        communication_types = ['forum']
        assessment_types = ['quiz', 'assign']
    elif course_type == "MoodleMacquarie":
        communication_types = ['forum_discussions', 'oublog', 'forum', 'oublog_posts', 'forum_posts', 'dialogue', 'dialogue_conversations']
        assessment_types = ['quiz','quiz_attempts','grade_grades','turnitintool', 'assign']
    elif course_type == "Blackboard":
        communication_types = ['resource/x-bb-discussionboard', 'course/x-bb-collabsession', 'resource/x-bb-discussionfolder']
        assessment_types = ['assessment/x-bb-qti-test', 'course/x-bb-courseassessment', 'resource/x-turnitin-assignment']

    assessment_types_str = ','.join("'{0}'".format(x) for x in assessment_types)
    communication_types_str = ','.join("'{0}'".format(x) for x in communication_types)

    return  communication_types, assessment_types, communication_types_str, assessment_types_str

def get_prepostevent_treetable(course_id, course_weeks, treelist_json, curr_evt, course_type):

    communication_types, assessment_types, communication_types_str, assessment_types_str = get_contenttypes()

    engine = create_engine('mysql://root:root@localhost/cloop_olap?unix_socket=/Applications/MAMP/tmp/mysql/mysql.sock', echo=False)
    sql = "SELECT F.user_id, F.page_id, F.pageview, D.date_week, D.date_dayinweek FROM fact_coursevisits F INNER JOIN dim_dates  D ON F.Date_Id = D.Id WHERE F.course_id=%d"%(course_id)
    connection = engine.raw_connection()
    df = pd.read_sql(sql, connection)

    treelist  = json.loads(treelist_json)
    weeks_str_list = ','.join(map(str, course_weeks))

    table = []
    table.append("<thead><tr>")
    table.append("<th>Name</th><th>Type</th><th>View</th>")
    for x in range(len(course_weeks)):
        table.append("<th>Week %d</th>" % (x+1))
    table.append("</tr>")
    table.append("</thead>")
    table.append("<tbody>\n")
    for page in treelist:
        pageid2 = int(page[0]) #int(row[2])

        cursor = connections['olap'].cursor()
        res_sql = "SELECT title, content_type, content_id, parent_id, order_no FROM dim_pages WHERE content_id=%d AND course_id=%d order by order_no;" %(pageid2, course_id)
        row_count = cursor.execute(res_sql);
        row = cursor.fetchall()
        cursor.close()

        order_no = row[0][4]
        content_type = row[0][1]
        classname = ""
        parentidstring =""

        pagestoinclude = None
        if course_type=="Moodle":
            pagestoinclude = ['page', 'folder', 'section', 'course_modules']
        else:
            pagestoinclude = ['course/x-bb-coursetoc', 'resource/x-bb-folder', 'resource/x-bb-stafffolder', 'resource/x-bb-discussionfolder']

        if row[0][1] in pagestoinclude:
            classname = "folder"
        else:
            classname = "file"

        if row[0][3]==0:
            parentidstring = ""
        else:
            parentidstring = "data-tt-parent-id='%s'" % (str(row[0][3]))
        if (row[0][1]=='section'):
            table.append("<tr data-tt-id='%s' %s class='stats-row'><td class='stats-title'><span class='%s'>%s</span></td><td class='stats-title'>%s</td><td class='stats-title'><a href='/coursepage?course_id=%d&page_id=%s&section_order=%s' class='btn btn-small btn-primary'>View</a></td></td>" % (str(pageid2), parentidstring, classname, row[0][0], row[0][1], course_id, str(pageid2), str(row[0][4])))
        else:
            table.append("<tr data-tt-id='%s' %s class='stats-row'><td class='stats-title'><span class='%s'>%s</span></td><td class='stats-title'>%s</td><td class='stats-title'><a href='/coursepage?course_id=%d&page_id=%s' class='btn btn-small btn-primary'>View</a></td>" % (str(pageid2), parentidstring, classname, row[0][0], row[0][1], course_id, str(pageid2)))

        filterd_df = df[(df.page_id == int(pageid2))]
        grouped_df = filterd_df.groupby('date_week').sum()

        total_counts_dict = defaultdict(int)

        for index, row in grouped_df.iterrows():
            total_counts_dict[index] = int(row['pageview'])

        for week in course_weeks:
            totalcounts = total_counts_dict[week]

            if totalcounts > 0:
                # Insert sparkline
                evt_pre_list = range(0,curr_evt)
                eventpre_df = filterd_df[filterd_df.date_dayinweek.isin(evt_pre_list)]
                eventpre_counts = len(eventpre_df.index)
                eventpost_counts = totalcounts - eventpre_counts
                width, height = scale_chart(totalcounts)
                table.append('<td><span class="sparklines2" sparkType="pie" values="%d,%d" sparkwidth="%d" sparkheight="%d"></span></td>' % (eventpre_counts, eventpost_counts, width, height))
            else:
                table.append('<td>&nbsp;</td>')

        table.append("</tr>\n")

    table.append("</tr>\n")
    table.append("</tbody>\n")
    table = ''.join(table)

    return table

def get_prepostevent_table(course_id, curr_evt, contenttype, course_weeks, course_type):

    weeks_str_list = ','.join(map(str, course_weeks))

    communication_types, assessment_types, communication_types_str, assessment_types_str = get_contenttypes()

    if (contenttype == "communication" or contenttype == "forum"):
        #contenttype = "'forum'"
        contentdisplaytype = ','.join("'{0}'".format(x) for x in communication_types)
<commit_msg>getcontenttypes methods requires coursetype as an argument.<commit_after>"""
    Loop Dashboard utils

    Contains methods to process data for dashboards dynamically

    Todo:
    - Pandas has bug that is stopping the database from being connected to using a connection string from the settings.py file

"""

from django.db import connections
from datetime import datetime, date, timedelta
import time
from cloop.models import Course, CourseRepeatingEvent, CourseSingleEvent, CourseSubmissionEvent
import json
import pandas as pd
from sqlalchemy import create_engine
from collections import defaultdict


def get_contenttypes(course_type):
    communication_types = None
    assessment_types = None
    if course_type == "Moodle":
        communication_types = ['forum']
        assessment_types = ['quiz', 'assign']
    elif course_type == "MoodleMacquarie":
        communication_types = ['forum_discussions', 'oublog', 'forum', 'oublog_posts', 'forum_posts', 'dialogue', 'dialogue_conversations']
        assessment_types = ['quiz','quiz_attempts','grade_grades','turnitintool', 'assign']
    elif course_type == "Blackboard":
        communication_types = ['resource/x-bb-discussionboard', 'course/x-bb-collabsession', 'resource/x-bb-discussionfolder']
        assessment_types = ['assessment/x-bb-qti-test', 'course/x-bb-courseassessment', 'resource/x-turnitin-assignment']

    assessment_types_str = ','.join("'{0}'".format(x) for x in assessment_types)
    communication_types_str = ','.join("'{0}'".format(x) for x in communication_types)

    return  communication_types, assessment_types, communication_types_str, assessment_types_str

def get_prepostevent_treetable(course_id, course_weeks, treelist_json, curr_evt, course_type):

    communication_types, assessment_types, communication_types_str, assessment_types_str = get_contenttypes(course_type)

    engine = create_engine('mysql://root:root@localhost/cloop_olap?unix_socket=/Applications/MAMP/tmp/mysql/mysql.sock', echo=False)
    sql = "SELECT F.user_id, F.page_id, F.pageview, D.date_week, D.date_dayinweek FROM fact_coursevisits F INNER JOIN dim_dates  D ON F.Date_Id = D.Id WHERE F.course_id=%d"%(course_id)
    connection = engine.raw_connection()
    df = pd.read_sql(sql, connection)

    treelist  = json.loads(treelist_json)
    weeks_str_list = ','.join(map(str, course_weeks))

    table = []
    table.append("<thead><tr>")
    table.append("<th>Name</th><th>Type</th><th>View</th>")
    for x in range(len(course_weeks)):
        table.append("<th>Week %d</th>" % (x+1))
    table.append("</tr>")
    table.append("</thead>")
    table.append("<tbody>\n")
    for page in treelist:
        pageid2 = int(page[0]) #int(row[2])

        cursor = connections['olap'].cursor()
        res_sql = "SELECT title, content_type, content_id, parent_id, order_no FROM dim_pages WHERE content_id=%d AND course_id=%d order by order_no;" %(pageid2, course_id)
        row_count = cursor.execute(res_sql);
        row = cursor.fetchall()
        cursor.close()

        order_no = row[0][4]
        content_type = row[0][1]
        classname = ""
        parentidstring =""

        pagestoinclude = None
        if course_type=="Moodle":
            pagestoinclude = ['page', 'folder', 'section', 'course_modules']
        else:
            pagestoinclude = ['course/x-bb-coursetoc', 'resource/x-bb-folder', 'resource/x-bb-stafffolder', 'resource/x-bb-discussionfolder']

        if row[0][1] in pagestoinclude:
            classname = "folder"
        else:
            classname = "file"

        if row[0][3]==0:
            parentidstring = ""
        else:
            parentidstring = "data-tt-parent-id='%s'" % (str(row[0][3]))
        if (row[0][1]=='section'):
            table.append("<tr data-tt-id='%s' %s class='stats-row'><td class='stats-title'><span class='%s'>%s</span></td><td class='stats-title'>%s</td><td class='stats-title'><a href='/coursepage?course_id=%d&page_id=%s&section_order=%s' class='btn btn-small btn-primary'>View</a></td></td>" % (str(pageid2), parentidstring, classname, row[0][0], row[0][1], course_id, str(pageid2), str(row[0][4])))
        else:
            table.append("<tr data-tt-id='%s' %s class='stats-row'><td class='stats-title'><span class='%s'>%s</span></td><td class='stats-title'>%s</td><td class='stats-title'><a href='/coursepage?course_id=%d&page_id=%s' class='btn btn-small btn-primary'>View</a></td>" % (str(pageid2), parentidstring, classname, row[0][0], row[0][1], course_id, str(pageid2)))

        filterd_df = df[(df.page_id == int(pageid2))]
        grouped_df = filterd_df.groupby('date_week').sum()

        total_counts_dict = defaultdict(int)

        for index, row in grouped_df.iterrows():
            total_counts_dict[index] = int(row['pageview'])

        for week in course_weeks:
            totalcounts = total_counts_dict[week]

            if totalcounts > 0:
                # Insert sparkline
                evt_pre_list = range(0,curr_evt)
                eventpre_df = filterd_df[filterd_df.date_dayinweek.isin(evt_pre_list)]
                eventpre_counts = len(eventpre_df.index)
                eventpost_counts = totalcounts - eventpre_counts
                width, height = scale_chart(totalcounts)
                table.append('<td><span class="sparklines2" sparkType="pie" values="%d,%d" sparkwidth="%d" sparkheight="%d"></span></td>' % (eventpre_counts, eventpost_counts, width, height))
            else:
                table.append('<td>&nbsp;</td>')

        table.append("</tr>\n")

    table.append("</tr>\n")
    table.append("</tbody>\n")
    table = ''.join(table)

    return table

def get_prepostevent_table(course_id, curr_evt, contenttype, course_weeks, course_type):

    weeks_str_list = ','.join(map(str, course_weeks))

    communication_types, assessment_types, communication_types_str, assessment_types_str = get_contenttypes(course_type)

    if (contenttype == "communication" or contenttype == "forum"):
        #contenttype = "'forum'"
        contentdisplaytype = ','.join("'{0}'".format(x) for x in communication_types)

{'fraction_non_alphanumeric': 0.14820605661619488, 'fraction_numerical': 0.0064186965108624094, 'mean_word_length': 3.6886574074074074, 'pattern_counts': {'":': 8, '<': 93, '<?xml version=': 0, '>': 95, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '888057', 'n_tokens_mistral': 3985, 'n_tokens_neox': 3874, 'n_words': 752}