def get_student_chapter_data(last_sync_dt): conn_intel = cuedb.get_cuemath_redshift_connection() cur_intel = conn_intel.cursor() # Add chapter.expected_time_complete, chapter.,made_live_on, sql_query = "SELECT std_chap.program_id, program.code, program.name, std_chap.entity_id, std_chap.entity_type, std_chap.user_id, std_chap.created_on, \ chapter.code, chapter.name, chapter.position, chapter.version, std_chap.started_on, std_chap.completed_on \ FROM fnrenames.service_concepts_user_entity std_chap left join fnrenames.service_concepts_cue_chapter chapter \ on (std_chap.entity_id = chapter.id) left join fnrenames.service_concepts_cue_program program \ on (std_chap.entity_id = program.id) \ WHERE ((std_chap.entity_type = 'CHAPTER' and chapter.is_live='true') or std_chap.entity_type = 'PROGRAM'\ and std_chap.updated_on > date(%s));" cur_intel.execute(sql_query, [ last_sync_dt, ]) df_student_program = pd.DataFrame( cur_intel.fetchall(), columns=[ 'program_id', 'program_code', 'program_name', 'entity_id', 'entity_type', 'user_id', 'created_on', 'chapter_code', 'chapter_name', 'chapter_position', 'chapter_version', 'started_on', 'completed_on' ]) cur_intel.close() conn_intel.close() return (df_student_program)
def get_student_attempts_data_by_attempt_id(session_id): conn_intel = cuedb.get_cuemath_redshift_connection() cur_intel = conn_intel.cursor() student_attempt_cols = [ "id", "worksheet_id", "node_id", "user_id", "started_on", "completed_on", "last_attempted_on", "points_earned", "accuracy", ] sql_query = "SELECT " + ", ".join( student_attempt_cols ) + " FROM fnrenames.service_concepts_user_attempt WHERE id=(%s)" cur_intel.execute(sql_query, [ session_id, ]) df_student_attempts = pd.DataFrame(cur_intel.fetchall(), columns=student_attempt_cols) cur_intel.close() conn_intel.close() return (df_student_attempts)
def get_node_schema(): conn = cuedb.get_cuemath_redshift_connection() cur = conn.cursor() node_cols = [ "id", "created_on", "updated_on", "chapter_id", "worksheet_id", "course_type", "is_live", "is_timed", "complete_in", "sheet_time", "points_available", "complete_criteria", "reattempt_criteria", "unlock_criteria", "position", "attempt_location" ] node_cols = ["node." + w for w in node_cols] chapter_cols = [ "id", "name", "code", "description", "is_default", "position", "program_id", "version", "is_live", "latest_chapter_id" ] chapter_cols = ['chapter.' + c for c in chapter_cols] program_cols = [ "id", "name", "code", "description", "grade", "is_live" ] program_cols = ['program.' + p for p in program_cols] sql_select = "SELECT " + ", ".join(node_cols) \ + ", " + ", ".join(chapter_cols) \ + ", " + ", ".join(program_cols) \ + " FROM fnrenames.service_concepts_cue_node node LEFT JOIN fnrenames.service_concepts_cue_chapter chapter on (node.chapter_id = chapter.id)" \ + " LEFT JOIN fnrenames.service_concepts_cue_program program on (chapter.program_id = program.id)" cur.execute(sql_select) df = pd.DataFrame(cur.fetchall(), columns=node_cols + chapter_cols + program_cols) cur.close() conn.close() return (df)
def get_classroom_data(last_sync_dt): ''' Save all classroom data; Classrooms are empty mostly, initially. They will be populated as session fetching happens. Get all student classrooms and find respective teacher classroom. Save schedule, student, teacher, class_timings_data. ''' conn_intel = cuedb.get_cuemath_redshift_connection() cur_intel = conn_intel.cursor() student_classroom_cols = [ "id", "student_id", "date", "scheduled_start_time", "scheduled_end_time", "enter_time", "exit_time", "_status" ] teacher_classroom_cols = [ "id", "teacher_id", "date", "scheduled_start_time", "scheduled_end_time", "attendance", "enter_time", "exit_time", "_status" ] sql_query = "SELECT " + ", ".join(["service_classroom_student_classroom."+ w for w in student_classroom_cols]) \ + ", " + ", ".join(["service_classroom_teacher_classroom."+ w for w in teacher_classroom_cols]) \ + " FROM service_classroom_student_classroom left join service_classroom_teacher_classroom on \ (service_classroom_student_classroom.teacher_classroom_id = service_classroom_teacher_classroom.id)\ WHERE (date(service_classroom_student_classroom.created_on) >= (%s));" cur_intel.execute(sql_query, [ last_sync_dt, ]) df_student_classrooms = pd.DataFrame(cur_intel.fetchall(), columns= ["student_classroom_"+ w for w in student_classroom_cols] \ +["teacher_classroom_" + b for b in teacher_classroom_cols]) cur_intel.close() conn_intel.close() return (df_student_classrooms)
def get_student_node_data(last_sync_dt): conn_intel = cuedb.get_cuemath_redshift_connection() cur_intel = conn_intel.cursor() student_worksheet_cols = [ 'program_id', 'worksheet_id', 'node_id', 'user_id', 'created_on', 'updated_on', 'points_earned', 'started_on', 'last_attempted_on', 'completed_on', 'attempt_location', 'is_timed', 'course_type', "is_live", 'is_attempt_in_progress', 'complete_status', 'reattempt_status', 'unlock_status', 'last_attempt_id' ] student_worksheet_cols_qry = [ 'std_nd.' + col for col in student_worksheet_cols ] sql_query = "SELECT " + ", ".join( student_worksheet_cols_qry ) + ", uent.entity_id FROM fnrenames.service_concepts_user_node std_nd LEFT JOIN fnrenames.service_concepts_user_entity uent ON (uent.id=std_nd.user_chapter_id) WHERE std_nd.updated_on > date(%s)-2;" cur_intel.execute(sql_query, [ last_sync_dt, ]) student_worksheet_cols.append('chapter_id') df_student_worksheet = pd.DataFrame(cur_intel.fetchall(), columns=student_worksheet_cols) cur_intel.close() conn_intel.close() return (df_student_worksheet)
def get_all_chapter_list(): conn = cuedb.get_cuemath_redshift_connection() cur = conn.cursor() sql_query = "select distinct(chp.code), prg.code, chp.name from fnrenames.service_concepts_cue_chapter chp" \ + " inner join fnrenames.service_concepts_cue_program prg on (chp.program_id = prg.id);" cur.execute(sql_query) df = pd.DataFrame( cur.fetchall(), columns=['chapter_code', 'program_code', 'chapter_name']) cur.close() conn.close() return (df)
def get_all_program_list(): conn = cuedb.get_cuemath_redshift_connection() cur = conn.cursor() sql_query = "select id, name, code, description, is_live, grade, created_on from fnrenames.service_concepts_cue_program;" cur.execute(sql_query) df = pd.DataFrame(cur.fetchall(), columns=[ 'id', 'name', 'code', 'description', 'is_live', 'grade', 'created_on' ]) cur.close() conn.close() return (df)
def get_schedules(): conn_intel = cuedb.get_cuemath_redshift_connection() cur_intel = conn_intel.cursor() sql_query = "SELECT id, created_on, updated_on, meta, schedule_code FROM service_classroom_schedules;" cur_intel.execute(sql_query) df_schedules = pd.DataFrame(cur_intel.fetchall(), columns=[ "id", "created_on", "updated_on", "meta", "schedule_code" ]) cur_intel.close() conn_intel.close() return (df_schedules)
def get_teacher_slots(): conn_intel = cuedb.get_cuemath_redshift_connection() cur_intel = conn_intel.cursor() teacher_slot_cols = [ "id", "created_on", "updated_on", "teacher_id", "schedule_id", "grades", "duration", "is_demo", "_status", "_type" ] sql_query = "SELECT " + ", ".join( teacher_slot_cols) + " FROM service_classroom_teacher_slots;" cur_intel.execute(sql_query) df_teacher_slots = pd.DataFrame(cur_intel.fetchall(), columns=teacher_slot_cols) cur_intel.close() conn_intel.close() return (df_teacher_slots)
def get_all_teachers(): ''' Save all Teachers Information; Edit info when reqd. ''' conn = cuedb.get_cuemath_redshift_connection() cur = conn.cursor() teacher_cols = [ "id", "created_on", "updated_on", "meta", "first_name", "last_name", "gender", "phone", "email", "is_demo" ] sql_query = "SELECT " + ", ".join( teacher_cols) + " FROM service_teacher_teacher;" cur.execute(sql_query) df_teachers = pd.DataFrame(cur.fetchall(), columns=teacher_cols) return (df_teachers)
def get_video_log_data(session_ids): conn = cuedb.get_cuemath_redshift_connection() cur = conn.cursor() sql_query = "SELECT DISTINCT JSON_EXTRACT_PATH_TEXT(_activity_data,'id', TRUE)||JSON_EXTRACT_PATH_TEXT(_activity_data,'sessionId', TRUE), "+ \ "'https://s3-ap-southeast-1.amazonaws.com/cuemath-tokbox/'||JSON_EXTRACT_PATH_TEXT(_activity_data,'projectId',TRUE)::character varying||'/'||JSON_EXTRACT_PATH_TEXT(_activity_data,'id',TRUE)::character varying||'/archive.mp4' " +\ "from service_communication_avmonitoring where session_id=ANY(%s) and event='archive';" cur.execute(sql_query, [ session_ids, ]) df_video_logs = pd.DataFrame(cur.fetchall(), columns=["row", "video_url"]) video_url_list = list(df_video_logs["video_url"]) # print("DEBUG Videolog fetching: ", df_video_logs) cur.close() conn.close() return (video_url_list)
def get_classroom_ratings_data(last_sync_dt): conn_intel = cuedb.get_cuemath_redshift_connection() cur_intel = conn_intel.cursor() classroom_ratings_cols = [ 'id', 'meta', 'created_on', 'rating', 'student_classroom_id' ] sql_query = "SELECT " + ", ".join( classroom_ratings_cols ) + " FROM fnrenames.service_classroom_classroom_rating WHERE DATE(created_on) > DATE(%s);" cur_intel.execute(sql_query, [ last_sync_dt, ]) df_student_classroom_ratings = pd.DataFrame( cur_intel.fetchall(), columns=classroom_ratings_cols) cur_intel.close() conn_intel.close() return (df_student_classroom_ratings)
def get_tags_data(): conn = cuedb.get_cuemath_redshift_connection() cur = conn.cursor() entity_tag_cols = ["entity_id", "tag_id", "entity_type"] entity_tag_cols = ['ent.' + et for et in entity_tag_cols] tag_cols = ["id", "meta_data", "type", "code", "name"] tag_cols = ['tg.' + tg for tg in tag_cols] sql_query = "SELECT "+ ", ".join(entity_tag_cols) \ + ", " + ", ".join(tag_cols) \ + " FROM fnrenames.service_concepts_cue_entity_tag ent" \ + " INNER JOIN fnrenames.service_concepts_cue_tag tg on (ent.tag_id = tg.id);" cur.execute(sql_query) df_remedials = pd.DataFrame(cur.fetchall(), columns=entity_tag_cols + tag_cols) cur.close() conn.close() return (df_remedials)
def get_worksheet_db_data(): #Get Data from Cuemath's Intel Database conn = cuedb.get_cuemath_redshift_connection() cur = conn.cursor() cols = [ "id", "name", "course_type", "created_on", "updated_on", "code", "learnosity_activity_ref", "target_accuracy", "description", "total_questions", "meta_data", "points_available", "sheet_time", "complete_in", "is_live" ] sql_select = "SELECT " + ", ".join( cols) + " FROM fnrenames.service_concepts_cue_worksheet;" #print(sql_select) cur.execute(sql_select) df = pd.DataFrame(cur.fetchall(), columns=cols) cur.close() conn.close() return (df)
def get_student_program_data(): conn_intel = cuedb.get_cuemath_redshift_connection() cur_intel = conn_intel.cursor() sql_query = "SELECT std_prog.program_id, std_prog.user_id, program.code, program.description, program.grade, program.is_live, std_prog.started_on, std_prog.completed_on \ FROM fnrenames.service_concepts_user_entity std_prog inner join fnrenames.service_concepts_cue_program program on (std_prog.program_id = program.id)\ WhERE std_prog.entity_type = 'PROGRAM' and program.is_live='true';" cur_intel.execute(sql_query) df_student_program = pd.DataFrame(cur_intel.fetchall(), columns=[ 'program_id', 'user_id', 'program_code', 'program_description', 'grade', 'is_live', 'started_on', 'completed_on' ]) cur_intel.close() conn_intel.close() return (df_student_program)
def get_av_data(dt, user_id): ''' Fetches all AV Related data for a student on a particular day ''' conn = cuedb.get_cuemath_redshift_connection() cur = conn.cursor() av_monitor_cols = [ "id", "created_on", "updated_on", "meta", "session_id", "event", "_activity_data" ] # Next Query is bombastic sql_av_monitor = "SELECT " + ", ".join( av_monitor_cols ) + " FROM service_communication_avmonitoring where date(updated_on) = (%s) and (event='streamDestroyed' or event='streamCreated') and _activity_data::jsonb @> \'{\"stream\":{\"connection\":{\"data\":\"{\\\"userId\\\": \\\"" + user_id + "\\\", \\\"userRole\\\": \\\"publisher\\\"}\"}}}\'::jsonb;" cur.execute(sql_av_monitor, [ dt, ]) df_av_monitor = pd.DataFrame( cur.fetchall(), columns=av_monitor_cols).sort_values('created_on') av_request_cols = [ "id", "created_on", "updated_on", "meta", "requested_by", "requested_on", "is_handled" ] sql_av_requests = "SELECT " + ", ".join( av_request_cols ) + " FROM service_communication_avrequest where DATE(requested_on) = (%s) and requested_by=(%s);" cur.execute(sql_av_requests, [dt, user_id]) df_av_request = pd.DataFrame(cur.fetchall(), columns=av_request_cols) av_whiteboard_cols = [ "id", "created_on", "updated_on", "ref_id", "image_url", "user_id" ] sql_av_whiteboard = "SELECT " + ", ".join( av_whiteboard_cols ) + " FROM service_communication_whiteboardexport where date(updated_on) = (%s) and user_id=(%s);" cur.execute(sql_av_whiteboard, [dt, user_id]) df_av_whiteboard = pd.DataFrame( cur.fetchall(), columns=av_whiteboard_cols).sort_values('created_on') cur.close() conn.close() return (df_av_monitor, df_av_request, df_av_whiteboard)
def get_student_slots(): conn_intel = cuedb.get_cuemath_redshift_connection() cur_intel = conn_intel.cursor() student_slot_cols = [ "id", "created_on", "updated_on", "student_id", "is_demo", "_status", "_type" ] teacher_slot_cols = [ "id", "created_on", "updated_on", "teacher_id", "schedule_id", "grades", "duration", "is_demo", "start_date", "end_date" ] sql_query = "SELECT " + ", ".join(["service_classroom_student_slots."+ w for w in student_slot_cols]) \ + ", " + ", ".join(["service_classroom_teacher_slots."+ w for w in teacher_slot_cols]) \ + " FROM service_classroom_student_slots left join service_classroom_teacher_slots on \ (service_classroom_student_slots.teacher_slot_id = service_classroom_teacher_slots.id);" #print(sql_query) cur_intel.execute(sql_query) df_student_slots = pd.DataFrame(cur_intel.fetchall(), columns= ["student_slots_"+ w for w in student_slot_cols] \ +["teacher_slots_" + b for b in teacher_slot_cols]) cur_intel.close() conn_intel.close() return (df_student_slots)
def get_student_data(student_list): ''' Gets Data of Students Enrolled under the Cue_User db. ''' conn_auth = cuedb.get_cuemath_redshift_connection() cur_auth = conn_auth.cursor() # Get User from cue_user table. Check email ID; Return True and False cols = [ 'id', 'created_on', 'updated_on', 'email', 'phone', 'first_name', 'last_name', 'gender', 'username' ] sql_student_query = "SELECT " + ", ".join( cols ) + " FROM service_auth_cue_user WHERE is_active='true' AND id=ANY(%s);" cur_auth.execute(sql_student_query, [ student_list, ]) student_dat = pd.DataFrame(cur_auth.fetchall(), columns=cols) cur_auth.close() conn_auth.close() if (student_dat.shape[0] == 0): raise Exception("No Students in DB") else: return (student_dat)
def get_av_data_by_date(dt): ''' Fetches all AV Related data for a student on a particular day ''' conn = cuedb.get_cuemath_redshift_connection() cur = conn.cursor() av_monitor_cols = [ "id", "created_on", "updated_on", "meta", "session_id", "event", "_activity_data" ] # Next Query is bombastic sql_av_monitor = "SELECT " + ", ".join( av_monitor_cols ) + " FROM service_communication_avmonitoring where date(created_on) = (%s) and (event='streamDestroyed' or event='streamCreated');" ''' sql_av_monitor = "select " + ", ".join(av_monitor_cols)+ ", "+ \ " JSON_EXTRACT_PATH_TEXT(regexp_replace(JSON_EXTRACT_PATH_TEXT(_activity_data, 'stream', 'connection', 'data', TRUE)::character varying, %s, %s), 'userId'),"+\ " JSON_EXTRACT_PATH_TEXT(regexp_replace(JSON_EXTRACT_PATH_TEXT(_activity_data, 'stream', 'connection', 'data', TRUE)::character varying, %s, %s), 'userRole')"+\ " from service_communication_avmonitoring"+\ " where date(created_on) = (%s) and (event='streamCreated');" #print(sql_av_monitor) ''' cur.execute(sql_av_monitor, [ dt, ]) # av_monitor_cols_2 = av_monitor_cols + ['user_id', 'user_role'] df_av_monitor = pd.DataFrame(cur.fetchall(), columns=av_monitor_cols) body_txt = [ IntelDB.get_av_dict_user(x) for idx, x in enumerate(list(df_av_monitor['_activity_data'])) ] df_av_monitor["user_id"] = pd.Series( [body_txt[i][0] for i in range(len(body_txt))]) df_av_monitor["user_role"] = pd.Series( [body_txt[i][1] for i in range(len(body_txt))]) av_request_cols = [ "id", "created_on", "updated_on", "meta", "requested_by", "requested_on", "is_handled" ] sql_av_requests = "SELECT " + ", ".join( av_request_cols ) + " FROM service_communication_avrequest where date(created_on) = (%s);" cur.execute(sql_av_requests, [ dt, ]) df_av_request = pd.DataFrame(cur.fetchall(), columns=av_request_cols) av_whiteboard_cols = [ "id", "created_on", "updated_on", "ref_id", "image_url", "user_id" ] sql_av_whiteboard = "SELECT " + ", ".join( av_whiteboard_cols ) + " FROM service_communication_whiteboardexport where date(created_on) = (%s);" cur.execute(sql_av_whiteboard, [ dt, ]) df_av_whiteboard = pd.DataFrame( cur.fetchall(), columns=av_whiteboard_cols).sort_values('created_on') cur.close() conn.close() return (df_av_monitor, df_av_request, df_av_whiteboard)