/**
* Update the quiz results in the database. Assume that the data exists when doing this update.
* @param Object $quizResultsSoFar The updated list of results data.
*/
function WPCW_quizzes_updateQuizResults($quizResultsSoFar)
{
global $wpcwdb, $wpdb;
$wpdb->show_errors();
$markinglistCount = 0;
$markinglist = false;
// Relabel variables for clarity.
$needsMarkingList = $quizResultsSoFar->quiz_needs_marking_list;
$newQuizData = $quizResultsSoFar->quiz_data;
// Got items that need marking.
if (!empty($needsMarkingList)) {
$markinglist = serialize($needsMarkingList);
$markinglistCount = count($needsMarkingList);
}
$dataToUpdate = array('user_id' => $quizResultsSoFar->user_id, 'unit_id' => $quizResultsSoFar->unit_id, 'quiz_id' => $quizResultsSoFar->quiz_id, 'quiz_needs_marking_list' => $markinglist, 'quiz_needs_marking' => $markinglistCount, 'quiz_data' => serialize($newQuizData), 'quiz_grade' => -1, 'quiz_attempt_id' => $quizResultsSoFar->quiz_attempt_id);
// Update with the quiz grade
$dataToUpdate['quiz_grade'] = WPCW_quizzes_calculateGradeForQuiz($newQuizData, $markinglistCount);
$SQL = arrayToSQLUpdate($wpcwdb->user_progress_quiz, $dataToUpdate, array('user_id', 'unit_id', 'quiz_id', 'quiz_attempt_id'));
$wpdb->query($SQL);
}
/**
* Function to upgrade the database tables.
*
* @param Integer $installedVersion The version that exists prior to the upgrade.
* @param Boolean $forceUpgrade If true, we force an upgrade.
* @param Boolean $showErrors If true, show any debug errors.
*/
function WPCW_database_upgradeTables($installedVersion, $forceUpgrade, $showErrors = false)
{
global $wpdb, $wpcwdb;
if ($showErrors) {
$wpdb->show_errors();
}
// Always upgrade tables. Conditionally execute any other table changes.
$upgradeNow = true;
// Only enable if debugging
//$wpdb->show_errors();
// #### Courses Table
$SQL = "CREATE TABLE {$wpcwdb->courses} (\n\t\t\t course_id int(11) unsigned NOT NULL AUTO_INCREMENT,\n\t\t\t course_title varchar(150) NOT NULL,\n\t\t\t course_desc text NULL,\n\t\t\t course_opt_completion_wall varchar(20) NOT NULL,\n\t\t\t course_opt_use_certificate varchar(20) NOT NULL DEFAULT 'no_certs',\n\t\t\t course_opt_user_access varchar(20) NOT NULL,\n\t\t\t course_unit_count int(11) unsigned NULL DEFAULT '0',\n\t\t\t course_from_name varchar(150) NOT NULL,\n\t\t\t course_from_email varchar(150) NOT NULL,\n\t\t\t course_to_email varchar(150) NOT NULL,\n\t\t\t course_message_unit_complete text NULL,\n\t\t\t course_message_course_complete text NULL,\n\t\t\t course_message_unit_not_logged_in text NULL,\n\t\t\t course_message_unit_pending text NULL,\n\t\t\t course_message_unit_no_access text NULL,\n\t\t\t course_message_unit_not_yet text NULL,\n\t\t\t course_message_quiz_open_grading_blocking text NULL,\n\t\t\t course_message_quiz_open_grading_non_blocking text NULL,\n\t\t\t email_complete_module_option_admin varchar(20) NOT NULL,\n\t\t\t email_complete_module_option varchar(20) NOT NULL,\n\t\t\t email_complete_module_subject varchar(300) NOT NULL,\n\t\t\t email_complete_module_body text NULL,\n\t\t\t email_complete_course_option_admin varchar(20) NOT NULL,\n\t\t\t email_complete_course_option varchar(20) NOT NULL,\n\t\t\t email_complete_course_subject varchar(300) NOT NULL,\n\t\t\t email_complete_course_body text NULL,\t\t\t \n\t\t\t email_quiz_grade_option varchar(20) NOT NULL,\n\t\t\t email_quiz_grade_subject varchar(300) NOT NULL,\n\t\t\t email_quiz_grade_body text NULL,\n\t\t\t email_complete_course_grade_summary_subject varchar(300) NOT NULL,\n\t\t\t email_complete_course_grade_summary_body text NULL,\n\t\t\t PRIMARY KEY (course_id)\n\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8";
WPCW_database_installTable($wpcwdb->courses, $SQL, $upgradeNow);
// Added in 2.2 - New course completed message
if ($forceUpgrade || $installedVersion < 2.2) {
$SQL = $wpdb->query($wpdb->prepare("\n\t\t\tUPDATE {$wpcwdb->courses} SET course_message_course_complete = %s \n\t\t\tWHERE course_message_course_complete IS NULL \n\t\t\t OR course_message_course_complete = ''", __("You have now completed the whole course. Congratulations!", 'wp_courseware')));
}
// Added in 2.4 - New quiz messages
if ($forceUpgrade || $installedVersion < 2.4) {
$SQL = $wpdb->query($wpdb->prepare("\n\t\t\tUPDATE {$wpcwdb->courses} \n\t\t\t SET course_message_quiz_open_grading_blocking = %s \n\t\t\tWHERE course_message_quiz_open_grading_blocking IS NULL \n\t\t\t OR course_message_quiz_open_grading_blocking = ''", __('Your quiz has been submitted for grading by the course instructor. Once your grade has been entered, you will be able access the next unit.', 'wp_courseware')));
$SQL = $wpdb->query($wpdb->prepare("\n\t\t\tUPDATE {$wpcwdb->courses} \n\t\t\t SET course_message_quiz_open_grading_non_blocking = %s \n\t\t\tWHERE course_message_quiz_open_grading_non_blocking IS NULL \n\t\t\t OR course_message_quiz_open_grading_non_blocking = ''", __('Your quiz has been submitted for grading by the course instructor. You have now completed this unit.', 'wp_courseware')));
$SQL = $wpdb->query($wpdb->prepare("\n\t\t\tUPDATE {$wpcwdb->courses} \n\t\t\t SET email_quiz_grade_option = %s \n\t\t\tWHERE email_quiz_grade_option IS NULL \n\t\t\t OR email_quiz_grade_option = ''", 'send_email'));
// After grade completion email.
$SQL = $wpdb->query($wpdb->prepare("\n\t\t\tUPDATE {$wpcwdb->courses} \n\t\t\t SET email_quiz_grade_subject = %s \n\t\t\tWHERE email_quiz_grade_subject IS NULL \n\t\t\t OR email_quiz_grade_subject = ''", EMAIL_TEMPLATE_QUIZ_GRADE_SUBJECT));
$SQL = $wpdb->query($wpdb->prepare("\n\t\t\tUPDATE {$wpcwdb->courses} \n\t\t\t SET email_quiz_grade_body = %s \n\t\t\tWHERE email_quiz_grade_body IS NULL \n\t\t\t OR email_quiz_grade_body = ''", EMAIL_TEMPLATE_QUIZ_GRADE_BODY));
// Grade summary email
$SQL = $wpdb->query($wpdb->prepare("\n\t\t\tUPDATE {$wpcwdb->courses} \n\t\t\t SET email_complete_course_grade_summary_subject = %s \n\t\t\tWHERE email_complete_course_grade_summary_subject IS NULL \n\t\t\t OR email_complete_course_grade_summary_subject = ''", EMAIL_TEMPLATE_COURSE_SUMMARY_WITH_GRADE_SUBJECT));
$SQL = $wpdb->query($wpdb->prepare("\n\t\t\tUPDATE {$wpcwdb->courses} \n\t\t\t SET email_complete_course_grade_summary_body = %s \n\t\t\tWHERE email_complete_course_grade_summary_body IS NULL \n\t\t\t OR email_complete_course_grade_summary_body = ''", EMAIL_TEMPLATE_COURSE_SUMMARY_WITH_GRADE_BODY));
}
// #### Modules Table
$SQL = "CREATE TABLE {$wpcwdb->modules} (\n\t\t\t module_id int(11) unsigned NOT NULL AUTO_INCREMENT,\n\t\t\t parent_course_id int(11) unsigned NOT NULL DEFAULT '0',\n\t\t\t module_title varchar(150) NOT NULL,\n\t\t\t module_desc text NULL,\n\t\t\t module_order int(11) unsigned NOT NULL DEFAULT '10000',\n\t\t\t module_number int(11) unsigned NOT NULL DEFAULT '0',\n\t\t\t PRIMARY KEY (module_id)\n\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
WPCW_database_installTable($wpcwdb->modules, $SQL, $upgradeNow);
// #### Units Meta Table
$SQL = "CREATE TABLE {$wpcwdb->units_meta} (\n\t\t\t unit_id int(11) unsigned NOT NULL,\n\t\t\t parent_module_id int(11) unsigned NOT NULL DEFAULT '0',\n\t\t\t parent_course_id int(11) unsigned NOT NULL DEFAULT '0',\n\t\t\t unit_order int(11) unsigned NOT NULL DEFAULT '0',\n\t\t\t unit_number int(11) unsigned NOT NULL DEFAULT '0',\n\t\t\t PRIMARY KEY (unit_id)\n\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
WPCW_database_installTable($wpcwdb->units_meta, $SQL, $upgradeNow);
// #### User Courses Allocations Table
$SQL = "CREATE TABLE {$wpcwdb->user_courses} (\n\t\t\t user_id int(11) unsigned NOT NULL,\n\t\t\t course_id int(11) unsigned NOT NULL,\n\t\t\t course_progress int(11) NOT NULL DEFAULT '0',\n\t\t\t course_final_grade_sent VARCHAR(30) NOT NULL DEFAULT '',\n\t\t\t UNIQUE KEY user_id (user_id,course_id)\n\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
WPCW_database_installTable($wpcwdb->user_courses, $SQL, $upgradeNow);
// #### User Progress Table
$SQL = "CREATE TABLE {$wpcwdb->user_progress} (\n\t\t\t user_id int(11) unsigned NOT NULL,\n\t\t\t unit_id int(11) unsigned NOT NULL,\n\t\t\t unit_completed_date datetime DEFAULT NULL,\n\t\t\t unit_completed_status varchar(20) NOT NULL,\n\t\t\t PRIMARY KEY (user_id,unit_id)\n\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
WPCW_database_installTable($wpcwdb->user_progress, $SQL, $upgradeNow);
// #### Quizzes
// Test code - removing any existing primary keys
/*if ($forceUpgrade || $installedVersion < 2.4)
{
$SQL = "SHOW COLUMNS FROM $wpcwdb->quiz LIKE 'quiz_id'";
if (!$wpdb->get_row($SQL))
{
$SQL = "ALTER TABLE $wpcwdb->quiz ADD COLUMN quiz_id int(11) unsigned NOT NULL";
$wpdb->query($SQL);
}
}*/
$SQL = "CREATE TABLE {$wpcwdb->quiz} (\n\t\t\t quiz_id int(11) unsigned NOT NULL AUTO_INCREMENT,\n\t\t\t quiz_title varchar(150) NOT NULL,\n\t\t\t quiz_desc text NULL,\n\t\t\t parent_unit_id int(11) unsigned NOT NULL DEFAULT '0',\n\t\t\t parent_course_id int(11) NOT NULL DEFAULT '0',\t\t\t \n\t\t\t quiz_type varchar(15) NOT NULL,\n\t\t\t quiz_pass_mark int(11) NOT NULL DEFAULT '0',\n\t\t\t quiz_show_answers varchar(15) NOT NULL DEFAULT 'no_answers',\t\t\t\n\t\t\t quiz_show_survey_responses varchar(15) NOT NULL DEFAULT 'no_responses',\n\t\t\t quiz_attempts_allowed int(11) NOT NULL DEFAULT '-1',\n\t\t\t show_answers_settings VARCHAR(500) NOT NULL DEFAULT '',\n\t\t\t quiz_paginate_questions VARCHAR(15) NOT NULL DEFAULT 'no_paging',\n\t\t\t quiz_paginate_questions_settings VARCHAR(500) NOT NULL DEFAULT '',\n\t\t\t quiz_timer_mode varchar(25) NOT NULL DEFAULT 'no_timer',\n\t\t\t quiz_timer_mode_limit int(11) unsigned NOT NULL DEFAULT '15',\n\t\t\t quiz_results_by_tag varchar(10) NOT NULL DEFAULT 'on',\n\t\t\t quiz_results_by_timer varchar(10) NOT NULL DEFAULT 'on',\n\t\t\t quiz_recommended_score varchar(20) NOT NULL DEFAULT 'no_recommended',\n\t\t\t show_recommended_percentage int(10) unsigned NOT NULL DEFAULT 50,\n\t\t\t PRIMARY KEY (quiz_id)\n\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
WPCW_database_installTable($wpcwdb->quiz, $SQL, $upgradeNow);
if ($forceUpgrade || $installedVersion < 2.61) {
// These are the defaults for the new quiz show answers settings that have been added in V2.61
$default_showAnswers = 'a:5:{s:19:"show_correct_answer";s:2:"on";s:16:"show_user_answer";s:2:"on";s:16:"show_explanation";s:2:"on";s:12:"mark_answers";s:2:"on";s:18:"show_results_later";s:2:"on";}';
$SQL = $wpdb->prepare("\n\t\t\tUPDATE {$wpcwdb->quiz} \n\t\t\tSET show_answers_settings = %s \n\t\t\tWHERE show_answers_settings = ''\n\t\t", $default_showAnswers);
$wpdb->query($SQL);
}
// Defaults for the new paging option that was added in V3.00
if ($forceUpgrade || $installedVersion < 3.0) {
// These are the defaults for the new quiz paging settings that have been added in V2.61
$default_pagingOption = 'a:3:{s:30:"allow_review_before_submission";s:2:"on";s:30:"allow_students_to_answer_later";s:2:"on";s:28:"allow_nav_previous_questions";s:2:"on";}';
$SQL = $wpdb->prepare("\n\t\t\tUPDATE {$wpcwdb->quiz} \n\t\t\tSET quiz_paginate_questions_settings = %s \n\t\t\tWHERE quiz_paginate_questions_settings = ''\n\t\t", $default_pagingOption);
$wpdb->query($SQL);
}
// #### Quiz - Questions
$SQL = "CREATE TABLE {$wpcwdb->quiz_qs} (\n\t\t\t question_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,\n\t\t\t question_type VARCHAR(20) NOT NULL DEFAULT 'multi',\n\t\t\t question_question text NULL,\n\t\t\t question_answers text NULL,\n\t\t\t question_data_answers text NULL,\n\t\t\t question_correct_answer VARCHAR(300) NOT NULL,\n\t\t\t question_answer_type VARCHAR(50) NOT NULL DEFAULT '',\n\t\t\t question_answer_hint text NULL,\n\t\t\t question_answer_explanation text NULL,\n\t\t\t question_image VARCHAR(300) NOT NULL DEFAULT '',\n\t\t\t question_answer_file_types VARCHAR(300) NOT NULL DEFAULT '',\n\t\t\t question_usage_count int(11) UNSIGNED DEFAULT 0,\n\t\t\t question_expanded_count int(11) UNSIGNED DEFAULT 1,\n\t\t\t question_multi_random_enable int(2) UNSIGNED DEFAULT 0,\n\t\t\t question_multi_random_count int(4) UNSIGNED DEFAULT 5,\n\t\t\t PRIMARY KEY (question_id)\n\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
WPCW_database_installTable($wpcwdb->quiz_qs, $SQL, $upgradeNow);
// #### Added in 2.4 - Quiz - User Progress - remove old unique index by checking for it first.
if ($forceUpgrade || $installedVersion < 2.8) {
$SQL = "SHOW INDEX FROM {$wpcwdb->user_progress_quiz} WHERE KEY_NAME = 'user_id'";
if ($wpdb->get_row($SQL)) {
$SQL = "ALTER TABLE {$wpcwdb->user_progress_quiz} DROP INDEX user_id";
$wpdb->query($SQL);
}
}
// #### Added in 2.5 - Quiz - Upgrade questions to use the new serialised array format.
if ($forceUpgrade || $installedVersion < 2.5) {
WPCW_database_upgrade_quizQuestions();
}
// #### Quiz - User Progress
$SQL = "CREATE TABLE {$wpcwdb->user_progress_quiz} (\n\t\t\t user_id int(11) NOT NULL,\n\t\t\t unit_id int(11) NOT NULL,\n\t\t\t quiz_id bigint(20) NOT NULL,\n\t\t\t quiz_attempt_id int(11) NOT NULL DEFAULT '0',\t\t\t \n\t\t\t quiz_completed_date datetime NOT NULL,\n\t\t\t quiz_started_date datetime NOT NULL,\n\t\t\t quiz_correct_questions int(11) unsigned NOT NULL,\n\t\t\t quiz_grade FLOAT(8,2) NOT NULL DEFAULT '-1',\n\t\t\t quiz_question_total int(11) unsigned NOT NULL,\n\t\t\t quiz_data text NULL,\n\t\t\t quiz_is_latest VARCHAR(50) DEFAULT 'latest',\n\t\t\t quiz_needs_marking int(11) unsigned NOT NULL DEFAULT '0',\n\t\t\t quiz_needs_marking_list TEXT NULL,\n\t\t\t quiz_next_step_type VARCHAR(50) DEFAULT '',\t\t\t \n\t\t\t quiz_next_step_msg TEXT DEFAULT '',\n\t\t\t quiz_paging_status VARCHAR(20) NOT NULL DEFAULT 'complete',\t\t\n\t\t\t quiz_paging_next_q int(11) NOT NULL DEFAULT 0,\n\t\t\t quiz_paging_incomplete int(11) NOT NULL DEFAULT 0,\n\t\t\t quiz_completion_time_seconds BIGINT NOT NULL DEFAULT 0,\n\t\t\t UNIQUE KEY unique_progress_item (user_id,unit_id,quiz_id,quiz_attempt_id)\n\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
WPCW_database_installTable($wpcwdb->user_progress_quiz, $SQL, $upgradeNow);
// #### Added in 2.4 - Quiz - User Progress - grade data added to the table.
if ($forceUpgrade || $installedVersion < 2.4) {
set_time_limit(0);
// All quizzes with a grade of -1 and all items are graded.
$SQL_NEED_UPDATE = "\n\t\t\tSELECT user_id, unit_id, quiz_id, quiz_attempt_id, quiz_data\n\t\t\tFROM {$wpcwdb->user_progress_quiz} \n\t\t\tWHERE quiz_grade = -1\n\t\t\t AND quiz_needs_marking = 0";
$quizProgressToUpdate = $wpdb->get_row($SQL_NEED_UPDATE);
while ($quizProgressToUpdate) {
$quizData = maybe_unserialize($quizProgressToUpdate->quiz_data);
$newGrade = WPCW_quizzes_calculateGradeForQuiz($quizData);
if ($newGrade > -1) {
$wpdb->query($wpdb->prepare("\n\t\t\t\t\tUPDATE {$wpcwdb->user_progress_quiz} \n\t\t\t\t\t SET quiz_grade = %s\n\t\t\t\t\tWHERE unit_id = %d\n\t\t\t\t\t AND user_id = %d\n\t\t\t\t\t AND quiz_id = %d\n\t\t\t\t\t AND quiz_attempt_id = %d\n\t\t\t\t", $newGrade, $quizProgressToUpdate->unit_id, $quizProgressToUpdate->user_id, $quizProgressToUpdate->quiz_id, $quizProgressToUpdate->quiz_attempt_id));
}
$quizProgressToUpdate = $wpdb->get_row($SQL_NEED_UPDATE);
flush();
}
}
// #### Mapping of membership levels
$SQL = "CREATE TABLE {$wpcwdb->map_member_levels} (\n\t\t\t \tcourse_id int(11) NOT NULL,\n \t\t\t\tmember_level_id varchar(100) NOT NULL,\n \t\t\t\tUNIQUE KEY course_id (course_id,member_level_id) \t\t\t\t\n\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
WPCW_database_installTable($wpcwdb->map_member_levels, $SQL, $upgradeNow);
// #### Mapping of certificates
$SQL = "CREATE TABLE {$wpcwdb->certificates} (\n\t\t\t cert_user_id int(11) NOT NULL,\n\t\t\t cert_course_id int(11) NOT NULL,\n\t\t\t cert_access_key varchar(50) NOT NULL,\n\t\t\t cert_generated datetime NOT NULL,\n\t\t\t UNIQUE KEY cert_user_id (cert_user_id,cert_course_id)\t\t\t\n\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
WPCW_database_installTable($wpcwdb->certificates, $SQL, $upgradeNow);
// #### Questions - Tags
$SQL = "CREATE TABLE {$wpcwdb->question_tags} (\n\t\t\t\tquestion_tag_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,\n\t\t\t\tquestion_tag_name varchar(150) NOT NULL,\n\t\t\t\tquestion_tag_usage int(11) unsigned NOT NULL,\n\t\t\t\tPRIMARY KEY (question_tag_id)\t\t\t\n\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
WPCW_database_installTable($wpcwdb->question_tags, $SQL, $upgradeNow);
// #### Questions - Tag Mappings
$SQL = "CREATE TABLE {$wpcwdb->question_tag_mapping} (\n\t\t\t\tquestion_id bigint(20) unsigned NOT NULL,\n\t\t\t\ttag_id bigint(20) unsigned NOT NULL,\n\t\t\t\tUNIQUE KEY question_tag_id (question_id,tag_id)\t\t\t\n\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
WPCW_database_installTable($wpcwdb->question_tag_mapping, $SQL, $upgradeNow);
// #### Questions/Quizzes - Mappings
$SQL = "CREATE TABLE {$wpcwdb->quiz_qs_mapping} (\n\t\t\t\tparent_quiz_id bigint(20) unsigned NULL,\n\t\t\t\tquestion_id bigint(20) unsigned NOT NULL,\n\t\t\t\tquestion_order int(11) unsigned NOT NULL DEFAULT '0',\n\t\t\t\tUNIQUE KEY question_assoc_id (parent_quiz_id,question_id)\t\t\t\n\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
WPCW_database_installTable($wpcwdb->quiz_qs_mapping, $SQL, $upgradeNow);
// #### Random Questions - Lock selections to a user so that they don't
// see different variations if refreshed.
$SQL = "CREATE TABLE {$wpcwdb->question_rand_lock} (\n\t\t\t\tquestion_user_id int(11) unsigned NOT NULL,\n\t\t\t\trand_question_id int(11) unsigned NOT NULL,\n\t\t\t\tparent_unit_id int(11) unsigned NOT NULL,\n\t\t\t\tquestion_selection_list text NOT NULL,\n\t\t\t\tUNIQUE KEY wpcw_question_rand_lock (question_user_id,rand_question_id)\t\t\t\n\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
WPCW_database_installTable($wpcwdb->question_rand_lock, $SQL, $upgradeNow);
// #### Added in 3.0 - Upgrade questions to have multiple quiz parents
// by creating a quiz-to-question mapping. We need to migrate all of
// the previous questions to refer to the new quiz parent.
if ($forceUpgrade || $installedVersion < 3.0) {
// See if we still have the old versions of the columns. Simply as parent_quiz_id and question_order
// are now deprecated.
$gotCol_parent_quiz_id = $wpdb->get_row("SHOW COLUMNS FROM {$wpcwdb->quiz_qs} LIKE 'parent_quiz_id'");
$gotCol_question_order = $wpdb->get_row("SHOW COLUMNS FROM {$wpcwdb->quiz_qs} LIKE 'question_order'");
// Yep, found both versions of the columns.
if ($gotCol_parent_quiz_id && $gotCol_question_order) {
// Need to get data from all questions
$SQL_NEED_UPDATE = "\n\t\t\t\tSELECT question_id, parent_quiz_id, question_order\n\t\t\t\tFROM {$wpcwdb->quiz_qs} \t\t\t\t\n\t\t\t";
$questionsToUpdate = $wpdb->get_results($SQL_NEED_UPDATE);
if ($questionsToUpdate) {
set_time_limit(0);
foreach ($questionsToUpdate as $singleQuestion) {
// Create the association in the new table, ignoring if it exists already.
$wpdb->query($wpdb->prepare("\n\t\t\t\t\t\tINSERT IGNORE INTO {$wpcwdb->quiz_qs_mapping} \n\t\t\t\t\t\t(parent_quiz_id, question_id, question_order)\n\t\t\t\t\t\tVALUES (%d, %d, %d)\n\t\t\t\t\t", $singleQuestion->parent_quiz_id, $singleQuestion->question_id, $singleQuestion->question_order));
// Update question to indicate it's usage
$countNumber = $wpdb->get_var($wpdb->prepare("\n\t\t\t\t\t\tSELECT COUNT(parent_quiz_id)\n\t\t\t\t\t\tFROM {$wpcwdb->quiz_qs_mapping} \n\t\t\t\t\t\tWHERE question_id = %d\n\t\t\t\t\t\t", $singleQuestion->question_id));
$wpdb->query($wpdb->prepare("\n\t\t\t\t\t\tUPDATE {$wpcwdb->quiz_qs}\n\t\t\t\t\t\tSET question_usage_count = %d \n\t\t\t\t\t\tWHERE question_id = %d\n\t\t\t\t\t\t", $countNumber, $singleQuestion->question_id));
flush();
}
}
// end if $questionsToUpdate
// Data migrated, now we just rename the old columns, rather than delete. Just in case something
// goes wrong. We can remove some day in the future.
$wpdb->query("ALTER TABLE {$wpcwdb->quiz_qs} CHANGE parent_quiz_id deprecated_parent_quiz_id bigint(20) AFTER question_answers");
$wpdb->query("ALTER TABLE {$wpcwdb->quiz_qs} CHANGE question_order deprecated_question_order int(11) AFTER question_answers");
}
// end if ($gotCol_parent_quiz_id && $gotCol_question_order)
}
// #### List of quiz custom feedback
$SQL = "CREATE TABLE {$wpcwdb->quiz_feedback} (\n\t\t\t \tqfeedback_id int(11) unsigned NOT NULL AUTO_INCREMENT,\n \t\t\t\tqfeedback_tag_id bigint(20) unsigned NOT NULL,\n \t\t\t\tqfeedback_quiz_id int(1) unsigned NOT NULL,\n \t\t\t\tqfeedback_summary varchar(300) NOT NULL,\n \t\t\t\tqfeedback_score_type varchar(20) NOT NULL DEFAULT 'below',\n \t\t\t\tqfeedback_score_grade int(11) unsigned NOT NULL DEFAULT '50',\n \t\t\t\tqfeedback_message text NOT NULL,\n \t\t\t\tPRIMARY KEY (qfeedback_id)\t \t\t\t\t\n\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
WPCW_database_installTable($wpcwdb->quiz_feedback, $SQL, $upgradeNow);
// Update settings once upgrade has happened
update_option(WPCW_DATABASE_KEY, WPCW_DATABASE_VERSION);
}
/**
* Save the user's quiz progress to the database using their verbose answers.
*
* @param Array $resultDetails The full result details for the user who's completed the quiz.
* @param Array $checkedAnswerList The full list of checked answers.
*/
function update_quizzes_saveUserProgress_completeQuiz($resultDetails, $checkedAnswerList)
{
global $wpdb, $wpcwdb;
$wpdb->show_errors();
$data = array();
$data['quiz_correct_questions'] = count($resultDetails['correct']);
$data['quiz_question_total'] = count($this->unitQuizDetails->questions);
$data['quiz_completed_date'] = current_time('mysql');
$data['user_id'] = $this->currentUserID;
$data['unit_id'] = $this->unitPost->ID;
$data['quiz_id'] = $this->unitQuizDetails->quiz_id;
// Store which questions need marking
$data['quiz_needs_marking'] = false;
$data['quiz_needs_marking_list'] = false;
// Calculate how long the quiz took, if it was timed.
if (!empty($this->unitQuizProgress) && '0000-00-00 00-00-00' != $this->unitQuizProgress->quiz_started_date) {
$data['quiz_completion_time_seconds'] = strtotime($data['quiz_completed_date']) - strtotime($this->unitQuizProgress->quiz_started_date);
}
// Generate a full list of the quiz and the answers given.
$fullDetails = $this->fetch_quizzes_extractQuizStatusFromAnswers($checkedAnswerList);
// Track how many questions need marking
$data['quiz_needs_marking'] = 0;
if (isset($resultDetails['needs_marking'])) {
// Just check that we have some items that need marking.
if (is_array($resultDetails['needs_marking'])) {
$data['quiz_needs_marking_list'] = serialize(array_keys($resultDetails['needs_marking']));
// Only store the IDs.
// If we have items that need answering, copy the number of items to be answered.
if (!empty($resultDetails['needs_marking'])) {
$data['quiz_needs_marking'] = count($resultDetails['needs_marking']);
}
} else {
$data['quiz_needs_marking_list'] = serialize(array());
// Creates an empty list.
}
}
// Use serialised data for storing full results.
$data['quiz_data'] = serialize($fullDetails);
$data['quiz_grade'] = WPCW_quizzes_calculateGradeForQuiz($fullDetails, $data['quiz_needs_marking']);
$SQL = $wpdb->prepare("\n\t\t\tSELECT * \n\t\t\tFROM {$wpcwdb->user_progress_quiz}\n\t\t\tWHERE user_id = %d\n\t\t\t AND unit_id = %d\n\t\t\t AND quiz_id = %d\n\t\t\tORDER BY quiz_attempt_id DESC \n\t\t\tLIMIT 1\n\t\t", $this->currentUserID, $this->unitPost->ID, $this->unitQuizDetails->quiz_id);
$weNeedToUpdateTheMostRecentProgress = false;
// Ensure this is marked as the latest and considered complete.
$data['quiz_is_latest'] = 'latest';
$data['quiz_paging_status'] = 'complete';
// Already exists, so increment the quiz_attempt_id
// If it doesn't exist, we'll just use the database default of 0.
if ($existingProgress = $wpdb->get_row($SQL)) {
// Is the last attempt incomplete? If so, then we want to update the existing progress
// to avoid incorrect duplication of attempts.
if ('incomplete' == $existingProgress->quiz_paging_status) {
$weNeedToUpdateTheMostRecentProgress = true;
// Retain attempt ID from previous item
$data['quiz_attempt_id'] = $existingProgress->quiz_attempt_id;
} else {
$data['quiz_attempt_id'] = $existingProgress->quiz_attempt_id + 1;
}
// Regardless, we need to mark all previous progress items as not being the latest.
$SQL = $wpdb->prepare("\n\t\t\t\tUPDATE {$wpcwdb->user_progress_quiz}\n\t\t\t\tSET quiz_is_latest = ''\n\t\t\t\tWHERE user_id = %d\n\t\t\t\t AND unit_id = %d\n\t\t\t\t AND quiz_id = %d\n\t\t\t", $this->currentUserID, $this->unitPost->ID, $this->unitQuizDetails->quiz_id);
$wpdb->query($SQL);
}
// We're updating the last progress to be complete now.
if ($weNeedToUpdateTheMostRecentProgress) {
$SQL = arrayToSQLUpdate($wpcwdb->user_progress_quiz, $data, array('user_id', 'unit_id', 'quiz_id', 'quiz_attempt_id'));
} else {
$SQL = arrayToSQLInsert($wpcwdb->user_progress_quiz, $data);
}
$wpdb->query($SQL);
// #### Update internal store of results once saved to database.
$this->unitQuizProgress = WPCW_quizzes_getUserResultsForQuiz($this->currentUserID, $this->unitPost->ID, $this->unitQuizDetails->quiz_id);
// Try to work out which questions are still incomplete.
$this->updateInternalVariable_quizzes_getIncompleteQuestions();
}
/**
* Save the user's quiz progress to the database using their verbose answers.
*
* @param Integer $userID The ID of the user completing the quiz.
* @param Array $quizDetails The details of the quiz that's been completed.
* @param Array $resultDetails The full result details for the user who's completed the quiz.
* @param Array $checkedAnswerList The full list of checked answers.
*/
function WPCW_quizzes_saveUserProgress($userID, $quizDetails, $resultDetails, $checkedAnswerList)
{
global $wpdb, $wpcwdb;
$wpdb->show_errors();
$data = array();
$data['quiz_correct_questions'] = count($resultDetails['correct']);
$data['quiz_question_total'] = count($quizDetails->questions);
$data['quiz_completed_date'] = current_time('mysql');
$data['user_id'] = $userID;
$data['unit_id'] = $quizDetails->parent_unit_id;
$data['quiz_id'] = $quizDetails->quiz_id;
// Store which questions need marking
$data['quiz_needs_marking'] = false;
$data['quiz_needs_marking_list'] = false;
// Generate a full list of the quiz and the answers given.
$fullDetails = array();
foreach ($quizDetails->questions as $singleQuestion) {
$qItem = array();
$qItem['title'] = $singleQuestion->question_question;
$openEndedQuestion = false;
// We know we have enough answers at this point, so know
switch ($singleQuestion->question_type) {
// There's definitely a right or wrong answer, so determine that now.
case 'truefalse':
case 'multi':
$qItem['their_answer'] = WPCW_quizzes_getCorrectAnswer($singleQuestion, $checkedAnswerList[$singleQuestion->question_id]);
break;
// Uploaded files and open-ended questions need marking, so it's just their raw answer.
// Uploaded files and open-ended questions need marking, so it's just their raw answer.
case 'upload':
case 'open':
$openEndedQuestion = true;
$qItem['their_answer'] = $checkedAnswerList[$singleQuestion->question_id];
break;
}
// If a survey, there are no correct answers.
if ('survey' == $quizDetails->quiz_type || $openEndedQuestion) {
$qItem['correct'] = false;
$qItem['got_right'] = false;
} else {
// Get the correct answer
$qItem['correct'] = WPCW_quizzes_getCorrectAnswer($singleQuestion);
// Did the answers match?
$qItem['got_right'] = $qItem['their_answer'] == $qItem['correct'] ? 'yes' : 'no';
}
$fullDetails[$singleQuestion->question_id] = $qItem;
}
// Track how many questions need marking
$data['quiz_needs_marking'] = count($resultDetails['needs_marking']);
$data['quiz_needs_marking_list'] = serialize(array_keys($resultDetails['needs_marking']));
// Only store the IDs.
// Use serialised data for storing full results.
$data['quiz_data'] = serialize($fullDetails);
$data['quiz_grade'] = WPCW_quizzes_calculateGradeForQuiz($fullDetails, $data['quiz_needs_marking']);
$SQL = $wpdb->prepare("\n\t\tSELECT * \n\t\tFROM {$wpcwdb->user_progress_quiz}\n\t\tWHERE user_id = %d\n\t\t AND unit_id = %d\n\t\t AND quiz_id = %d\n\t\tORDER BY quiz_attempt_id DESC \n\t\tLIMIT 1\n\t", $userID, $quizDetails->parent_unit_id, $quizDetails->quiz_id);
// Already exists, so increment the quiz_attempt_id
// If it doesn't exist, we'll just use the database default of 0.
if ($existingProgress = $wpdb->get_row($SQL)) {
// Mark all previous progress items as not being the latest.
$SQL = $wpdb->prepare("\n\t\t\tUPDATE {$wpcwdb->user_progress_quiz}\n\t\t\tSET quiz_is_latest = ''\n\t\t\tWHERE user_id = %d\n\t\t\t AND unit_id = %d\n\t\t\t AND quiz_id = %d\n\t\t", $userID, $quizDetails->parent_unit_id, $quizDetails->quiz_id);
$wpdb->query($SQL);
// Ensure this is marked as the latest.
$data['quiz_is_latest'] = 'latest';
$data['quiz_attempt_id'] = $existingProgress->quiz_attempt_id + 1;
}
$SQL = arrayToSQLInsert($wpcwdb->user_progress_quiz, $data);
$wpdb->query($SQL);
}
/**
* Function to upgrade the database tables.
*
* @param Integer $installedVersion The version that exists prior to the upgrade.
* @param Boolean $forceUpgrade If true, we force an upgrade.
* @param Boolean $showErrors If true, show any debug errors.
*/
function WPCW_database_upgradeTables($installedVersion, $forceUpgrade, $showErrors = false)
{
global $wpdb, $wpcwdb;
if ($showErrors) {
$wpdb->show_errors();
}
// Always upgrade tables. Conditionally execute any other table changes.
$upgradeNow = true;
// Only enable if debugging
//$wpdb->show_errors();
// #### Courses Table
$SQL = "CREATE TABLE {$wpcwdb->courses} (\n\t\t\t course_id int(10) unsigned NOT NULL AUTO_INCREMENT,\n\t\t\t course_title varchar(150) NOT NULL,\n\t\t\t course_desc text NOT NULL,\n\t\t\t course_opt_completion_wall varchar(20) NOT NULL,\n\t\t\t course_opt_use_certificate varchar(20) NOT NULL DEFAULT 'no_certs',\n\t\t\t course_opt_user_access varchar(20) NOT NULL,\n\t\t\t course_unit_count int(10) unsigned NULL DEFAULT '0',\n\t\t\t course_from_name varchar(150) NOT NULL,\n\t\t\t course_from_email varchar(150) NOT NULL,\n\t\t\t course_to_email varchar(150) NOT NULL,\n\t\t\t course_message_unit_complete text NOT NULL,\n\t\t\t course_message_course_complete text NOT NULL,\n\t\t\t course_message_unit_not_logged_in text NOT NULL,\n\t\t\t course_message_unit_pending text NOT NULL,\n\t\t\t course_message_unit_no_access text NOT NULL,\n\t\t\t course_message_unit_not_yet text NOT NULL,\n\t\t\t course_message_quiz_open_grading_blocking text NOT NULL,\n\t\t\t course_message_quiz_open_grading_non_blocking text NOT NULL,\n\t\t\t email_complete_module_option_admin varchar(20) NOT NULL,\n\t\t\t email_complete_module_option varchar(20) NOT NULL,\n\t\t\t email_complete_module_subject varchar(300) NOT NULL,\n\t\t\t email_complete_module_body text NOT NULL,\n\t\t\t email_complete_course_option_admin varchar(20) NOT NULL,\n\t\t\t email_complete_course_option varchar(20) NOT NULL,\n\t\t\t email_complete_course_subject varchar(300) NOT NULL,\n\t\t\t email_complete_course_body text NOT NULL,\t\t\t \n\t\t\t email_quiz_grade_option varchar(20) NOT NULL,\n\t\t\t email_quiz_grade_subject varchar(300) NOT NULL,\n\t\t\t email_quiz_grade_body text NOT NULL,\n\t\t\t email_complete_course_grade_summary_subject varchar(300) NOT NULL,\n\t\t\t email_complete_course_grade_summary_body text NOT NULL,\n\t\t\t PRIMARY KEY (course_id)\n\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8";
WPCW_database_installTable($wpcwdb->courses, $SQL, $upgradeNow);
// Added in 1.22 - New course completed message
if ($forceUpgrade || $installedVersion < 1.22) {
$SQL = $wpdb->query($wpdb->prepare("\n\t\t\tUPDATE {$wpcwdb->courses} SET course_message_course_complete = %s \n\t\t\tWHERE course_message_course_complete IS NULL \n\t\t\t OR course_message_course_complete = ''", __("You have now completed the whole course. Congratulations!", 'wp_courseware')));
}
// Added in 1.24 - New quiz messages
if ($forceUpgrade || $installedVersion < 1.24) {
$SQL = $wpdb->query($wpdb->prepare("\n\t\t\tUPDATE {$wpcwdb->courses} \n\t\t\t SET course_message_quiz_open_grading_blocking = %s \n\t\t\tWHERE course_message_quiz_open_grading_blocking IS NULL \n\t\t\t OR course_message_quiz_open_grading_blocking = ''", __('Your quiz has been submitted for grading by the course instructor. Once your grade has been entered, you will be able access the next unit.', 'wp_courseware')));
$SQL = $wpdb->query($wpdb->prepare("\n\t\t\tUPDATE {$wpcwdb->courses} \n\t\t\t SET course_message_quiz_open_grading_non_blocking = %s \n\t\t\tWHERE course_message_quiz_open_grading_non_blocking IS NULL \n\t\t\t OR course_message_quiz_open_grading_non_blocking = ''", __('Your quiz has been submitted for grading by the course instructor. You have now completed this unit.', 'wp_courseware')));
$SQL = $wpdb->query($wpdb->prepare("\n\t\t\tUPDATE {$wpcwdb->courses} \n\t\t\t SET email_quiz_grade_option = %s \n\t\t\tWHERE email_quiz_grade_option IS NULL \n\t\t\t OR email_quiz_grade_option = ''", 'send_email'));
// After grade completion email.
$SQL = $wpdb->query($wpdb->prepare("\n\t\t\tUPDATE {$wpcwdb->courses} \n\t\t\t SET email_quiz_grade_subject = %s \n\t\t\tWHERE email_quiz_grade_subject IS NULL \n\t\t\t OR email_quiz_grade_subject = ''", EMAIL_TEMPLATE_QUIZ_GRADE_SUBJECT));
$SQL = $wpdb->query($wpdb->prepare("\n\t\t\tUPDATE {$wpcwdb->courses} \n\t\t\t SET email_quiz_grade_body = %s \n\t\t\tWHERE email_quiz_grade_body IS NULL \n\t\t\t OR email_quiz_grade_body = ''", EMAIL_TEMPLATE_QUIZ_GRADE_BODY));
// Grade summary email
$SQL = $wpdb->query($wpdb->prepare("\n\t\t\tUPDATE {$wpcwdb->courses} \n\t\t\t SET email_complete_course_grade_summary_subject = %s \n\t\t\tWHERE email_complete_course_grade_summary_subject IS NULL \n\t\t\t OR email_complete_course_grade_summary_subject = ''", EMAIL_TEMPLATE_COURSE_SUMMARY_WITH_GRADE_SUBJECT));
$SQL = $wpdb->query($wpdb->prepare("\n\t\t\tUPDATE {$wpcwdb->courses} \n\t\t\t SET email_complete_course_grade_summary_body = %s \n\t\t\tWHERE email_complete_course_grade_summary_body IS NULL \n\t\t\t OR email_complete_course_grade_summary_body = ''", EMAIL_TEMPLATE_COURSE_SUMMARY_WITH_GRADE_BODY));
}
// #### Modules Table
$SQL = "CREATE TABLE {$wpcwdb->modules} (\n\t\t\t module_id int(10) unsigned NOT NULL AUTO_INCREMENT,\n\t\t\t parent_course_id int(10) unsigned NOT NULL DEFAULT '0',\n\t\t\t module_title varchar(150) NOT NULL,\n\t\t\t module_desc text NOT NULL,\n\t\t\t module_order int(10) unsigned NOT NULL DEFAULT '10000',\n\t\t\t module_number int(10) unsigned NOT NULL DEFAULT '0',\n\t\t\t PRIMARY KEY (module_id)\n\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
WPCW_database_installTable($wpcwdb->modules, $SQL, $upgradeNow);
// #### Units Meta Table
$SQL = "CREATE TABLE {$wpcwdb->units_meta} (\n\t\t\t unit_id int(10) unsigned NOT NULL,\n\t\t\t parent_module_id int(10) unsigned NOT NULL DEFAULT '0',\n\t\t\t parent_course_id int(10) unsigned NOT NULL DEFAULT '0',\n\t\t\t unit_order int(10) unsigned NOT NULL DEFAULT '0',\n\t\t\t unit_number int(10) unsigned NOT NULL DEFAULT '0',\n\t\t\t PRIMARY KEY (unit_id)\n\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
WPCW_database_installTable($wpcwdb->units_meta, $SQL, $upgradeNow);
// #### User Courses Allocations Table
$SQL = "CREATE TABLE {$wpcwdb->user_courses} (\n\t\t\t user_id int(10) unsigned NOT NULL,\n\t\t\t course_id int(10) unsigned NOT NULL,\n\t\t\t course_progress int(11) NOT NULL DEFAULT '0',\n\t\t\t course_final_grade_sent VARCHAR(30) NOT NULL DEFAULT '',\n\t\t\t UNIQUE KEY user_id (user_id,course_id)\n\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
WPCW_database_installTable($wpcwdb->user_courses, $SQL, $upgradeNow);
// #### User Progress Table
$SQL = "CREATE TABLE {$wpcwdb->user_progress} (\n\t\t\t user_id int(10) unsigned NOT NULL,\n\t\t\t unit_id int(10) unsigned NOT NULL,\n\t\t\t unit_completed_date datetime DEFAULT NULL,\n\t\t\t unit_completed_status varchar(20) NOT NULL,\n\t\t\t PRIMARY KEY (user_id,unit_id)\n\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
WPCW_database_installTable($wpcwdb->user_progress, $SQL, $upgradeNow);
// #### Quizzes
$SQL = "CREATE TABLE {$wpcwdb->quiz} (\n\t\t\t quiz_id int(10) unsigned NOT NULL AUTO_INCREMENT,\n\t\t\t quiz_title varchar(150) NOT NULL,\n\t\t\t quiz_desc text NOT NULL,\n\t\t\t parent_unit_id int(10) unsigned NOT NULL DEFAULT '0',\n\t\t\t parent_course_id int(11) NOT NULL DEFAULT '0',\t\t\t \n\t\t\t quiz_type varchar(15) NOT NULL,\n\t\t\t quiz_pass_mark int(11) NOT NULL DEFAULT '0',\n\t\t\t quiz_show_answers varchar(15) NOT NULL DEFAULT 'no_answers',\n\t\t\t PRIMARY KEY (quiz_id)\n\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
WPCW_database_installTable($wpcwdb->quiz, $SQL, $upgradeNow);
// #### Quiz - Questions
$SQL = "CREATE TABLE {$wpcwdb->quiz_qs} (\n\t\t\t question_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,\n\t\t\t parent_quiz_id bigint(20) unsigned NOT NULL DEFAULT '0',\n\t\t\t question_type varchar(20) NOT NULL DEFAULT 'multi',\n\t\t\t question_question text NOT NULL,\n\t\t\t question_answers text NOT NULL,\n\t\t\t question_correct_answer varchar(300) NOT NULL,\n\t\t\t question_answer_type varchar(50) NOT NULL DEFAULT '',\n\t\t\t question_order int(11) unsigned NOT NULL DEFAULT '0',\n\t\t\t question_answer_hint text NOT NULL DEFAULT '',\n\t\t\t question_answer_file_types varchar(300) NOT NULL DEFAULT '',\n\t\t\t PRIMARY KEY (question_id)\n\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
WPCW_database_installTable($wpcwdb->quiz_qs, $SQL, $upgradeNow);
// #### Added in 1.24 - Quiz - User Progress - remove old unique index by checking for it first.
if ($forceUpgrade || $installedVersion < 1.24) {
$SQL = "SHOW INDEX FROM {$wpcwdb->user_progress_quiz} WHERE KEY_NAME = 'user_id'";
if ($wpdb->get_row($SQL)) {
$SQL = "ALTER TABLE {$wpcwdb->user_progress_quiz} DROP INDEX user_id";
$wpdb->query($SQL);
}
}
// #### Quiz - User Progress
$SQL = "CREATE TABLE {$wpcwdb->user_progress_quiz} (\n\t\t\t user_id int(11) NOT NULL,\n\t\t\t unit_id int(11) NOT NULL,\n\t\t\t quiz_id bigint(20) NOT NULL,\n\t\t\t quiz_attempt_id int(11) NOT NULL DEFAULT '0',\t\t\t \n\t\t\t quiz_completed_date datetime NOT NULL,\n\t\t\t quiz_correct_questions int(10) unsigned NOT NULL,\n\t\t\t quiz_grade FLOAT(8,2) NOT NULL DEFAULT '-1',\n\t\t\t quiz_question_total int(10) unsigned NOT NULL,\n\t\t\t quiz_data text NOT NULL,\n\t\t\t quiz_is_latest VARCHAR(50) DEFAULT 'latest',\n\t\t\t quiz_needs_marking int(10) unsigned NOT NULL DEFAULT '0',\n\t\t\t quiz_needs_marking_list TEXT NOT NULL,\n\t\t\t quiz_next_step_type VARCHAR(50) DEFAULT '',\t\t\t \n\t\t\t quiz_next_step_msg TEXT DEFAULT '',\t\t\t \n\t\t\t UNIQUE KEY unique_progress_item (user_id,unit_id,quiz_id,quiz_attempt_id)\n\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
WPCW_database_installTable($wpcwdb->user_progress_quiz, $SQL, $upgradeNow);
// #### Added in 1.24 - Quiz - User Progress - grade data added to the table.
if ($forceUpgrade || $installedVersion < 1.24) {
set_time_limit(0);
// All quizzes with a grade of -1 and all items are graded.
$SQL_NEED_UPDATE = "\n\t\t\tSELECT user_id, unit_id, quiz_id, quiz_attempt_id, quiz_data\n\t\t\tFROM {$wpcwdb->user_progress_quiz} \n\t\t\tWHERE quiz_grade = -1\n\t\t\t AND quiz_needs_marking = 0";
$quizProgressToUpdate = $wpdb->get_row($SQL_NEED_UPDATE);
while ($quizProgressToUpdate) {
$quizData = maybe_unserialize($quizProgressToUpdate->quiz_data);
$newGrade = WPCW_quizzes_calculateGradeForQuiz($quizData);
if ($newGrade > -1) {
$wpdb->query($wpdb->prepare("\n\t\t\t\t\tUPDATE {$wpcwdb->user_progress_quiz} \n\t\t\t\t\t SET quiz_grade = %s\n\t\t\t\t\tWHERE unit_id = %d\n\t\t\t\t\t AND user_id = %d\n\t\t\t\t\t AND quiz_id = %d\n\t\t\t\t\t AND quiz_attempt_id = %d\n\t\t\t\t", $newGrade, $quizProgressToUpdate->unit_id, $quizProgressToUpdate->user_id, $quizProgressToUpdate->quiz_id, $quizProgressToUpdate->quiz_attempt_id));
}
$quizProgressToUpdate = $wpdb->get_row($SQL_NEED_UPDATE);
flush();
}
}
// #### Mapping of membership levels
$SQL = "CREATE TABLE {$wpcwdb->map_member_levels} (\n\t\t\t \tcourse_id int(11) NOT NULL,\n \t\t\t\tmember_level_id varchar(100) NOT NULL,\n \t\t\t\tUNIQUE KEY course_id (course_id,member_level_id) \t\t\t\t\n\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
WPCW_database_installTable($wpcwdb->map_member_levels, $SQL, $upgradeNow);
// #### Mapping of certificates
$SQL = "CREATE TABLE {$wpcwdb->certificates} (\n\t\t\t cert_user_id int(11) NOT NULL,\n\t\t\t cert_course_id int(11) NOT NULL,\n\t\t\t cert_access_key varchar(50) NOT NULL,\n\t\t\t cert_generated datetime NOT NULL,\n\t\t\t UNIQUE KEY cert_user_id (cert_user_id,cert_course_id)\t\t\t\n\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
WPCW_database_installTable($wpcwdb->certificates, $SQL, $upgradeNow);
}