static function main() { global $wpdb; $_courserep = new NamasteRepCourse(); $_lessonrep = new NamasteRepLesson(); // select all courses $courses = $wpdb->get_results("SELECT * FROM {$wpdb->posts} WHERE post_type='namaste_course' AND post_status='publish' ORDER BY post_title"); if (!empty($_GET['course_id'])) { // select course $course = get_post($_GET['course_id']); // total and avg pageviews per user list($total_pageviews, $avg_pageviews) = $_courserep->pageviews($course->ID); // num students in the course $num_students = $wpdb->get_var($wpdb->prepare("SELECT COUNT(tU.ID) FROM {$wpdb->users} tU\n\t\t\t\tJOIN " . NAMASTE_STUDENT_COURSES . " tS ON tU.ID = tS.user_id \n\t\t\t\tAND (tS.status = 'completed' OR tS.status = 'enrolled') AND tS.course_id=%d", $course->ID)); // avg % completeness $num_completed = $wpdb->get_var($wpdb->prepare("SELECT COUNT(tU.ID) FROM {$wpdb->users} tU\n\t\t\t\tJOIN " . NAMASTE_STUDENT_COURSES . " tS ON tU.ID = tS.user_id \n\t\t\t\tAND tS.status = 'completed' AND tS.course_id=%d", $course->ID)); $percent_completed = empty($num_students) ? 0 : round(100 * $num_completed / $num_students); // avg grade if grading system is used $use_grading_system = get_option('namaste_use_grading_system'); if ($use_grading_system) { $avg_grade = $_courserep->avg_grade($course->ID); } // select lessons $lessons = $wpdb->get_results($wpdb->prepare("SELECT tL.* FROM {$wpdb->posts} tL \n\t\t\t\tJOIN {$wpdb->postmeta} tM ON tM.meta_key = 'namaste_course' AND tM.meta_value = %d AND tM.post_id = tL.ID\n\t\t\t\tWHERE tL.post_type='namaste_lesson' AND tL.post_status='publish' \n\t\t\t\tORDER BY tL.post_title", $course->ID)); $lids = array(0); foreach ($lessons as $lesson) { $lids[] = $lesson->ID; } // now select all studetns who have started any of the lessons $students = $wpdb->get_results("SELECT * FROM " . NAMASTE_STUDENT_LESSONS . " WHERE lesson_id IN (" . implode(',', $lids) . ")"); // select all homeworks in these lessons $homeworks = $wpdb->get_results("SELECT * FROM " . NAMASTE_HOMEWORKS . " WHERE lesson_id IN (" . implode(',', $lids) . ")"); $hids = array(0); foreach ($homeworks as $homework) { $hids[] = $homework->id; } // select all solutions to these homeworks $solutions = $wpdb->get_results("SELECT * FROM " . NAMASTE_STUDENT_HOMEWORKS . " WHERE homework_id IN (" . implode(',', $hids) . ")"); // for each lesson select: // total pageviews & pageviews per student // % completed of all // avg grade // num homeworks & % completed foreach ($lessons as $cnt => $lesson) { list($lesson_total_pageviews, $lesson_avg_pageviews) = $_lessonrep->pageviews($lesson->ID); $lessons[$cnt]->total_pageviews = $lesson_total_pageviews; $lessons[$cnt]->avg_pageviews = $lesson_avg_pageviews; $num_lesson_students = $num_lesson_completed = 0; foreach ($students as $student) { if ($student->lesson_id != $lesson->ID) { continue; } $num_lesson_students++; if ($student->status == 1) { $num_lesson_completed++; } } // end foreach student $percent_completed_lesson = empty($num_lesson_students) ? 0 : round(100 * $num_lesson_completed / $num_lesson_students); $lessons[$cnt]->num_students = $num_lesson_students; $lessons[$cnt]->num_completed = $num_lesson_completed; $lessons[$cnt]->percent_completed = $percent_completed_lesson; if ($use_grading_system) { $lessons[$cnt]->avg_grade = $_lessonrep->avg_grade($lesson->ID); } $num_homeworks = $num_solutions = $num_approved_solutions = 0; $lesson_homeworks = array(); foreach ($homeworks as $hct => $homework) { $homework_num_solutions = $homework_num_approved_solutions = $homework_num_students = 0; $homework_uids = array(); if ($homework->lesson_id != $lesson->ID) { continue; } $num_homeworks++; foreach ($solutions as $solution) { if ($solution->homework_id != $homework->id) { continue; } if (!in_array($solution->student_id, $homework_uids)) { $homework_uids[] = $solution->student_id; } $num_solutions++; $homework_num_solutions++; if ($solution->status == 'approved') { $num_approved_solutions++; $homework_num_approved_solutions++; } } // end foreach solution $homework_num_students = sizeof($homework_uids); $homeworks[$hct]->num_solutions = $homework_num_solutions; $homeworks[$hct]->num_approved_solutions = $homework_num_approved_solutions; $homeworks[$hct]->num_students = $homework_num_students; $homeworks[$hct]->num_per_student = empty($homework_num_students) ? 0 : round($homework_num_solutions / $homework_num_students); $homeworks[$hct]->percent_approved = empty($homework_num_solutions) ? 0 : round(100 * $homework_num_approved_solutions / $homework_num_solutions); $lesson_homeworks[] = $homeworks[$hct]; } // end foreach homework $lessons[$cnt]->homeworks = $lesson_homeworks; $lessons[$cnt]->num_homeworks = $num_homeworks; $lessons[$cnt]->num_solutions = $num_solutions; $lessons[$cnt]->num_approved_solutions = $num_approved_solutions; $lessons[$cnt]->percent_approved_solutions = empty($num_solutions) ? 0 : round(100 * $num_approved_solutions / $num_solutions); } // end foreach lesson // prepare filters for studens query below $status_sql = ''; if (!empty($_POST['status_filter'])) { $status_sql = $wpdb->prepare(" AND tSC.status = %s ", $_POST['status_filter']); if (!empty($_POST['completed_clause'])) { $status_sql .= $wpdb->prepare(" AND tSC.enrollment_date >= tSC.completion_date - INTERVAL %d DAY ", $_POST['completed_filter']); } } // students in this course join to pageviews, num lessons strarted, num lessons completed, and grade $students = $wpdb->get_results($wpdb->prepare("SELECT tU.ID as student_id, tU.user_login as user_login, tU.display_name as display_name,\n\t\t\t\ttU.user_email as user_email, \n\t\t\t\ttSC.enrollment_date as enrollment_date, tSC.completion_date as completion_date, tSC.grade as grade, tSC.status as status,\n\t\t\t\t(SELECT COUNT(tSL.id) FROM " . NAMASTE_STUDENT_LESSONS . " tSL \n\t\t\t\t\tWHERE tSL.student_id=tU.ID AND tSL.lesson_id IN (" . implode(',', $lids) . ")) as lessons_started,\n\t\t\t\t(SELECT COUNT(tSL2.id) FROM " . NAMASTE_STUDENT_LESSONS . " tSL2 \n\t\t\t\t\tWHERE tSL2.student_id=tU.ID AND tSL2.lesson_id IN (" . implode(',', $lids) . ") AND tSL2.status=1) as lessons_completed\n\t\t\t\tFROM {$wpdb->users} tU \n\t\t\t\tJOIN " . NAMASTE_STUDENT_COURSES . " tSC ON tSC.user_id=tU.ID AND tSC.course_id=%d {$status_sql}\t\t\t\t \n\t\t\t\tORDER BY tU.user_login", $course->ID, $course->ID)); } $dateformat = get_option('date_format'); include NAMASTEREP_PATH . "/views/course-reports.html.php"; }
static function reports($user_id = 0, $topnav = true) { global $wpdb; if ($user_id) { $_GET['user_id'] = $user_id; } // when calling from my_reports or shortcode. it has priority over $_GET['user_id'] // get all users who have some courses assigned $users = $wpdb->get_results("SELECT tU.* FROM {$wpdb->users} tU JOIN " . NAMASTE_STUDENT_COURSES . " tC\n\t\t\tON tC.user_id=tU.ID AND tC.status!='rejected' AND status!='pending' \n\t\t\tGROUP BY tU.ID ORDER BY tU.user_nicename"); // if specific user is selected, select reports: // overal circle charts showing pageviews per course and % completeness per course - done // circle chart for each course showing pageviews on lessons // table with lessons: visits, status, solutions to assignments, % accepted, grades if any, % completeness for the lesson // history of actions in the course // prepare 10 colors to use for the charts $colors = array('green', 'red', 'blue', 'yellow', 'orange', 'black', 'pink', 'brown', 'navy', 'maroon'); $colorindex = 0; // This will probably be placed in another method to make it easier to output by shortcode and // on user's own reports page if (!empty($_GET['user_id'])) { // select user $user = get_userdata($_GET['user_id']); $_lreport = new NamasteRepLesson(); // select the courses he is in, along with all the visits in them $courses = $wpdb->get_results($wpdb->prepare("SELECT tC.* FROM {$wpdb->posts} tC\n\t\t\t\tJOIN " . NAMASTE_STUDENT_COURSES . " tS ON tS.course_id = tC.ID AND tS.user_id = %d \n\t\t\t\tWHERE post_type='namaste_course' ORDER BY tC.post_title", $user->ID)); $cids = array(0); foreach ($courses as $course) { $cids[] = $course->ID; } // selects all lessons in the courses allong with visits $lessons = $wpdb->get_results($wpdb->prepare("SELECT tL.*, tM.meta_value as course_id, tSL.status as lesson_status,\n\t\t\t\ttSL.grade as grade \n\t\t\t\tFROM {$wpdb->posts} tL\n\t\t\t\tJOIN {$wpdb->postmeta} tM ON tM.meta_key = 'namaste_course' AND tM.meta_value IN (" . implode(',', $cids) . ")\n\t\t\t\tAND tM.post_id = tL.ID\n\t\t\t\tLEFT JOIN " . NAMASTE_STUDENT_LESSONS . " tSL ON tSL.lesson_id = tL.ID AND tSL.student_id = %d\n\t\t\t\tWHERE tL.post_type = 'namaste_lesson' AND tL.post_status = 'publish' \n\t\t\t\tORDER BY tL.post_title", $user->ID)); // select all visits of this user $visits = $wpdb->get_results($wpdb->prepare("SELECT * FROM " . NAMASTE_VISITS . " WHERE user_id=%d", $user->ID)); // select all homeworks in these courses $homeworks = $wpdb->get_results("SELECT * FROM " . NAMASTE_HOMEWORKS . " WHERE course_id IN (" . implode(',', $cids) . ")"); $hids = array(0); foreach ($homeworks as $homework) { $hids[] = $homework->id; } // select all solutions submitted by this user $solutions = $wpdb->get_results($wpdb->prepare("SELECT tS.*, tH.lesson_id as lesson_id \n\t\t\t\tFROM " . NAMASTE_STUDENT_HOMEWORKS . " tS JOIN " . NAMASTE_HOMEWORKS . " tH ON tH.id = tS.homework_id \n\t\t\t\tWHERE tS.student_id=%d AND tS.homework_id IN (" . implode(',', $hids) . ")", $user->ID)); // match visits and % completeness in lessons foreach ($lessons as $cnt => $lesson) { $lesson_visits = 0; foreach ($visits as $visit) { if ($visit->lesson_id != $lesson->ID) { continue; } $lesson_visits += $visit->visits; } $lessons[$cnt]->completeness = $_lreport->completeness($lesson, $user->ID, $homeworks, $solutions); $lessons[$cnt]->visits = $lesson_visits; // add solutions $num_solutions = $accepted_solutions = 0; foreach ($solutions as $solution) { if ($solution->lesson_id == $lesson->ID) { $num_solutions++; if ($solution->status == 'approved') { $accepted_solutions++; } } } if ($num_solutions == 0) { $percent_accepted_solutions = 0; } else { $percent_accepted_solutions = round(100 * ($accepted_solutions / $num_solutions)); } $lessons[$cnt]->num_solutions = $num_solutions; $lessons[$cnt]->percent_accepted_solutions = $percent_accepted_solutions; } // end foreach lesson // now match lessons to courses, and visits foreach ($courses as $cnt => $course) { $course_visits = $course_completeness = 0; $course_lessons = array(); foreach ($visits as $visit) { if ($visit->course_id == $course->ID) { $course_visits += $visit->visits; } } // match the lessons and add the pageviews of each lesson foreach ($lessons as $lesson) { if ($lesson->course_id != $course->ID) { continue; } $course_visits += $lesson->visits; $course_lessons[] = $lesson; $course_completeness += $lesson->completeness; } $courses[$cnt]->visits = $course_visits; $courses[$cnt]->lessons = $course_lessons; // % completeness of this course $courses[$cnt]->completeness = round($course_completeness / sizeof($course_lessons)); $courses[$cnt]->color = $colors[$colorindex]; $colorindex++; if ($colorindex >= 10) { $colorindex = 0; } // in case of too many courses, reset color index } // end foreach course // select actions history $actions = $wpdb->get_results($wpdb->prepare("SELECT * FROM " . NAMASTE_HISTORY . " WHERE user_id=%d ORDER BY id DESC", $user->ID)); $use_grading_system = get_option('namaste_use_grading_system'); $date_format = get_option('date_format'); $time_format = get_option('time_format'); } // end if user is selected include NAMASTEREP_PATH . "/views/users-reports.html.php"; }