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";
}
