/** * Calculates the cumulative grade for a course and user. * @param Integer $courseID The ID of the course. * @param Integer $userID The ID of the user. * * @return String The progress for the course/user, or n/a if there's nothing to report. */ function WPCW_courses_getCourseCumulativeGrade($courseID, $userID) { // Get all the quizzes for this course $quizIDList = array(); $quizIDListForSQL = false; $quizzesForCourse = WPCW_quizzes_getAllQuizzesForCourse($courseID); // Create a simple list of IDs to use in SQL queries if ($quizzesForCourse) { foreach ($quizzesForCourse as $singleQuiz) { $quizIDList[$singleQuiz->quiz_id] = $singleQuiz; } // Convert list of IDs into an SQL list $quizIDListForSQL = '(' . implode(',', array_keys($quizIDList)) . ')'; } else { return __('n/a', 'wp_courseware'); } // Get quiz results for this user $quizResults = WPCW_quizzes_getQuizResultsForUser($userID, $quizIDListForSQL); // Track cumulative data $quizScoresSoFar = 0; $quizScoresSoFar_count = 0; // ### Now render results for each quiz foreach ($quizIDList as $aQuizID => $singleQuiz) { // Got progress data, process the result if (isset($quizResults[$aQuizID])) { // Extract results and unserialise the data array. $theResults = $quizResults[$aQuizID]; $theResults->quiz_data = maybe_unserialize($theResults->quiz_data); // We've got something that needs grading. if ($theResults->quiz_needs_marking == 0) { // Calculate score, and use for cumulative. $score = number_format($theResults->quiz_grade); $quizScoresSoFar += $score; $quizScoresSoFar_count++; } } // end of quiz result check. } // end foreach ($quizIDList as $aQuizID => $singleQuiz) // Calculate the cumulative grade return $quizScoresSoFar_count > 0 ? number_format($quizScoresSoFar / $quizScoresSoFar_count, 1) . '%' : __('n/a', 'wp_courseware'); }
/** * Generates a verbose output of the gradebook data for a specific course. */ function WPCW_data_export_gradebookData() { global $wpcwdb, $wpdb; $wpdb->show_errors(); // #### 1 - See if the course exists first. $courseDetails = false; if (isset($_GET['course_id']) && ($courseID = $_GET['course_id'])) { $courseDetails = WPCW_courses_getCourseDetails($courseID); } // Course does not exist, simply output an error using plain text. if (!$courseDetails) { header('Content-Type: text/plain'); _e('Sorry, but that course could not be found.', 'wp_courseware'); return; } // #### 2 - Need a list of all quizzes for this course, excluding surveys. $quizzesForCourse = WPCW_quizzes_getAllQuizzesForCourse($courseDetails->course_id); // Handle situation when there are no quizzes. if (!$quizzesForCourse) { header('Content-Type: text/plain'); _e('There are no quizzes for this course, therefore no grade information to show.', 'wp_courseware'); return; } // Do we want certificates? $usingCertificates = 'use_certs' == $courseDetails->course_opt_use_certificate; // Create a simple list of IDs to use in SQL queries $quizIDList = array(); foreach ($quizzesForCourse as $singleQuiz) { $quizIDList[] = $singleQuiz->quiz_id; } // Convert list of IDs into an SQL list $quizIDListForSQL = '(' . implode(',', $quizIDList) . ')'; // Course does exist, so now we really output the data WPCW_data_export_sendHeaders_CSV(); // Start CSV $out = fopen('php://output', 'w'); // #### 3 - The headings for the CSV data $headings = array(__('Name', 'wp_courseware'), __('Username', 'wp_courseware'), __('Email Address', 'wp_courseware'), __('Course Progress', 'wp_courseware'), __('Cumulative Grade', 'wp_courseware'), __('Has Grade Been Sent?', 'wp_courseware')); // Check if we're using certificates or not. if ($usingCertificates) { $headings[] = __('Is Certificate Available?', 'wp_courseware'); } // #### 4 - Add the headings for the quiz titles. foreach ($quizzesForCourse as $singleQuiz) { $headings[] = sprintf('%s (quiz_%d)', $singleQuiz->quiz_title, $singleQuiz->quiz_id); } // #### 6 - Render the headings fputcsv($out, $headings); // #### 7 - Select all users that exist for this course $SQL = $wpdb->prepare("\n\t\tSELECT * \n\t\tFROM {$wpcwdb->user_courses} uc\t\t\t\t\t\t\t\t\t\n\t\tLEFT JOIN {$wpdb->users} u ON u.ID = uc.user_id\n\t\tWHERE uc.course_id = %d\n\t\t AND u.ID IS NOT NULL\t\t\t\n\t\t", $courseDetails->course_id); $userData = $wpdb->get_results($SQL); if (!$userData) { // All done fclose($out); return; } // #### 8 - Render the specific user details. foreach ($userData as $userObj) { $quizResults = WPCW_quizzes_getQuizResultsForUser($userObj->ID, $quizIDListForSQL); // Track cumulative data $quizScoresSoFar = 0; $quizScoresSoFar_count = 0; // Track the quiz scores in order $thisUsersQuizData = array(); // ### Now render results for each quiz foreach ($quizIDList as $aQuizID) { // Got progress data, process the result if (isset($quizResults[$aQuizID])) { // Extract results and unserialise the data array. $theResults = $quizResults[$aQuizID]; $theResults->quiz_data = maybe_unserialize($theResults->quiz_data); // We've got something that needs grading. if ($theResults->quiz_needs_marking > 0) { $thisUsersQuizData['quiz_' . $aQuizID] = __('Manual Grade Required', 'wp_courseware'); } else { // Calculate score, and use for cumulative. $score = number_format($theResults->quiz_grade); $quizScoresSoFar += $score; $thisUsersQuizData['quiz_' . $aQuizID] = $score . '%'; $quizScoresSoFar_count++; } } else { $thisUsersQuizData['quiz_' . $aQuizID] = __('Not Taken', 'wp_courseware'); } } $dataToOutput = array(); // These must be in the order of the columns specified above for it all to match up. $dataToOutput['name'] = $userObj->display_name; $dataToOutput['username'] = $userObj->user_login; $dataToOutput['email_address'] = $userObj->user_email; // Progress Details $dataToOutput['course_progress'] = $userObj->course_progress . '%'; $dataToOutput['cumulative_grade'] = $quizScoresSoFar_count > 0 ? number_format($quizScoresSoFar / $quizScoresSoFar_count, 1) . '%' : __('n/a', 'wp_courseware'); $dataToOutput['has_grade_been_sent'] = 'sent' == $userObj->course_final_grade_sent ? __('Yes', 'wp_courseware') : __('No', 'wp_courseware'); // Show if there's a certificate that can be downloaded. if ($usingCertificates) { $dataToOutput['is_certificate_available'] = __('No', 'wp_courseware'); if (WPCW_certificate_getCertificateDetails($userObj->ID, $courseDetails->course_id, false)) { $dataToOutput['is_certificate_available'] = __('Yes', 'wp_courseware'); } } // Output the quiz summary here.. $dataToOutput += $thisUsersQuizData; fputcsv($out, $dataToOutput); } // All done fclose($out); }
/** * Handle sending out emails to users when they have completed the course and we're sending them their final grade. * * @param Object $courseDetails The details of the course that we're sending details out for. * @param PageBuilder $page The page that's rendering the page structure. */ function WPCW_showPage_GradeBook_handleFinalGradesEmail($courseDetails, $page) { // This could take a long time, hence setting time limit to unlimited. set_time_limit(0); global $wpdb, $wpcwdb; $wpdb->show_errors(); // Get users to email final grades to $usersNeedGrades_SQL = $wpdb->prepare("\n\t\tSELECT * \n\t\tFROM {$wpcwdb->user_courses} uc\t\t\t\t\t\t\t\t\t\n\t\tLEFT JOIN {$wpdb->users} u ON u.ID = uc.user_id\n\t\tWHERE uc.course_id = %d\n\t\t AND u.ID IS NOT NULL\n\t\t AND uc.course_progress = 100\n\t\t AND uc.course_final_grade_sent != 'sent'\n\t\t", $courseDetails->course_id); // Allow the list of users to email to be customised. $usersNeedGrades = $wpdb->get_results(apply_filters("wpcw_back_query_filter_gradebook_users_final_grades_email", $usersNeedGrades_SQL, $courseDetails)); // Abort if there's nothing to do, showing a useful error message to the user. if (empty($usersNeedGrades)) { $page->showMessage(__('There are currently no users that are eligible to receive their final grade.', 'wp_courseware') . ' ' . __('No emails have been sent.', 'wp_courseware'), true); return; } $totalUserCount = count($usersNeedGrades); //WPCW_debug_showArray($courseDetails); // ### Email Template - Construct the from part of the email $headers = false; if ($courseDetails->course_from_email) { $headers = sprintf('From: %s <%s>' . "\r\n", $courseDetails->course_from_name, $courseDetails->course_from_email); } // Start the status pane to wrap the updates. printf('<div id="wpcw_gradebook_email_progress">'); // Little summary of how many users there are. printf('<h3>%s <b>%d %s</b>...</h3>', __('Sending final grade emails to', 'wp_courseware'), $totalUserCount, _n('user', 'users', $totalUserCount, 'wp_courseware')); // Get all the quizzes for this course $quizIDList = array(); $quizIDListForSQL = false; $quizzesForCourse = WPCW_quizzes_getAllQuizzesForCourse($courseDetails->course_id); // Create a simple list of IDs to use in SQL queries if ($quizzesForCourse) { foreach ($quizzesForCourse as $singleQuiz) { $quizIDList[$singleQuiz->quiz_id] = $singleQuiz; } // Convert list of IDs into an SQL list $quizIDListForSQL = '(' . implode(',', array_keys($quizIDList)) . ')'; } // Run through each user, and generate their details. $userCount = 1; foreach ($usersNeedGrades as $aSingleUser) { printf('<p>%s (%s) - <b>%d%% %s</b></p>', $aSingleUser->display_name, $aSingleUser->user_email, number_format($userCount / $totalUserCount * 100, 1), __('complete', 'wp_courseware')); // Work out what tags we have to replace in the body and subject and replace // the generic ones. $messageBody = $courseDetails->email_complete_course_grade_summary_body; $tagList_Body = WPCW_email_getTagList($messageBody); $messageBody = WPCW_email_replaceTags_generic($courseDetails, $aSingleUser, $tagList_Body, $messageBody); $messageSubject = $courseDetails->email_complete_course_grade_summary_subject; $tagList_Subject = WPCW_email_getTagList($messageSubject); $messageSubject = WPCW_email_replaceTags_generic($courseDetails, $aSingleUser, $tagList_Subject, $messageSubject); // Generate the data for all of the quizzes, and add it to the email. $quizGradeMessage = "\n"; // Only add quiz summary if we have one! if (!empty($quizIDList)) { // Get quiz results for this user $quizResults = WPCW_quizzes_getQuizResultsForUser($aSingleUser->ID, $quizIDListForSQL); // Track cumulative data $quizScoresSoFar = 0; $quizScoresSoFar_count = 0; // ### Now render results for each quiz foreach ($quizIDList as $aQuizID => $singleQuiz) { // Got progress data, process the result if (isset($quizResults[$aQuizID])) { // Extract results and unserialise the data array. $theResults = $quizResults[$aQuizID]; $theResults->quiz_data = maybe_unserialize($theResults->quiz_data); // We've got something that needs grading. if ($theResults->quiz_needs_marking == 0) { // Calculate score, and use for cumulative. $score = number_format($theResults->quiz_grade); $quizScoresSoFar += $score; $quizScoresSoFar_count++; // Add to string with the quiz name and each grade. $quizGradeMessage .= sprintf("%s #%d - %s\n%s: %s%%\n\n", __('Quiz', 'wp_courseware'), $quizScoresSoFar_count, $singleQuiz->quiz_title, __('Grade', 'wp_courseware'), $score); } } // end of quiz result check. } } // end of check for quizzes for course // Calculate the cumulative grade $cumulativeGrade = $quizScoresSoFar_count > 0 ? number_format($quizScoresSoFar / $quizScoresSoFar_count, 1) . '%' : __('n/a', 'wp_courseware'); // Now replace the cumulative grades. $messageBody = str_ireplace('{QUIZ_SUMMARY}', trim($quizGradeMessage), $messageBody); $messageBody = str_ireplace('{CUMULATIVE_GRADE}', $cumulativeGrade, $messageBody); // Set up the target email address $targetEmail = $aSingleUser->user_email; // Send the actual email if (!wp_mail($targetEmail, $messageSubject, $messageBody, $headers)) { error_log('WPCW_email_sendEmail() - email did not send.'); } // Update the user record to mark as being sent $wpdb->query($wpdb->prepare("\n\t\t \tUPDATE {$wpcwdb->user_courses}\n\t\t \t SET course_final_grade_sent = 'sent'\n\t\t \tWHERE user_id = %d\n\t\t \t AND course_id = %d\n\t\t ", $aSingleUser->ID, $courseDetails->course_id)); flush(); $userCount++; } // Tell the user we're complete. printf('<h3>%s</h3>', __('All done.', 'wp_courseware')); printf('</div>'); }