protected function loadNumberOfUser()
{
$tableName = get_module_main_tbl(array('user'));
if (!is_null($this->getSelector())) {
$where = $this->getSelector()->getStrategy();
} else {
$where = "";
}
$sql = "SELECT count(*)" . " FROM `" . $tableName['user'] . "`" . " " . $where;
$this->numberOfUser = claro_sql_query_fetch_single_value($sql);
}
/**
* load the list of recipient list of the current message
*/
protected function loadRecipientList()
{
$tableName = get_module_main_tbl(array('im_recipient', 'user'));
$recipientListSQL = "SELECT U.user_id, U.nom as lastName, U.prenom as firstName, U.username\n" . " FROM `" . $tableName['im_recipient'] . "` AS R\n" . " LEFT JOIN `" . $tableName['user'] . "` AS U ON R.user_id = U.user_id\n" . " WHERE R.message_id = " . (int) $this->getId() . " AND R.user_id > 0\n";
$userList = claro_sql_query_fetch_all_rows($recipientListSQL);
$sentToSQL = "SELECT DISTINCT (R.sent_to)\n" . " FROM `" . $tableName['im_recipient'] . "` AS R\n" . " WHERE R.message_id = " . (int) $this->getId() . "\n" . " LIMIT 1\n";
$sentTo = claro_sql_query_fetch_single_value($sentToSQL);
$this->recipientList = array();
$this->recipientList['sentTo'] = $sentTo;
$this->recipientList['userList'] = $userList;
}
protected function loadNumberOfMessage()
{
if (!$this->numberOfMessage) {
if (!is_null($this->messageFilter)) {
$strategy = $this->messageFilter->getStrategy();
} else {
$strategy = "";
}
$tableName = get_module_main_tbl(array('im_message'));
$readSQL = "SELECT count(*) \n" . "FROM `" . $tableName['im_message'] . "` AS M \n" . "WHERE M.sender = " . (int) $this->userId . "\n" . " " . $strategy;
$this->numberOfMessage = claro_sql_query_fetch_single_value($readSQL);
}
}
/**
* Move tracking data from old tables to new ones.
*
* @return upgrade status
*/
function upgrade_main_database_tracking_data_to_19()
{
$tbl_mdb_names = claro_sql_get_main_tbl();
$tool = 'MAIN_TRACKING_DATA_19';
switch ($step = get_upgrade_status($tool)) {
case 1:
// drop id to be able to recreate it with correct autoincrement values at last step
$sql = "ALTER TABLE `" . $tbl_mdb_names['tracking_event'] . "` DROP `id`";
if (upgrade_sql_query($sql)) {
$step = set_upgrade_status($tool, $step + 1);
} else {
return $step;
}
unset($sql);
case 2:
// get total number of rows in track_e_login
$sql = "SELECT COUNT(*)\n FROM `" . get_conf('statsDbName') . '`.`' . get_conf('statsTblPrefix') . "track_e_login`";
$tableRows = (int) claro_sql_query_fetch_single_value($sql);
$recoveredOffset = UpgradeTrackingOffset::retrieve();
// get a subgroup of 250 rows and insert group by group in tracking_event table
for ($offset = $recoveredOffset; $offset < $tableRows; $offset += 250) {
// we have to store offset to start again from it if something failed
UpgradeTrackingOffset::store($offset);
$query = "SELECT `login_id`, `login_user_id`, `login_date`, `login_ip`\n FROM `" . get_conf('statsDbName') . '`.`' . get_conf('statsTblPrefix') . "track_e_login`\n ORDER BY `login_date`, `login_id`\n LIMIT " . $offset . ", 250";
// then copy these 250 rows to tracking_event
$eventList = claro_sql_query_fetch_all_rows($query);
// build query to insert all 250 rows
$sql = "INSERT INTO `" . get_conf('statsDbName') . '`.`' . get_conf('statsTblPrefix') . "tracking_event`\n ( `user_id`, `date`, `type`, `data` )\n VALUES\n ";
//inject former data into new table structure
foreach ($eventList as $event) {
$sql .= "(" . (int) $event['login_user_id'] . ",'" . claro_sql_escape($event['login_date']) . "','user_login','" . claro_sql_escape(serialize(array('ip' => $event['login_ip']))) . "'),\n";
}
unset($eventList);
if (upgrade_sql_query(rtrim($sql, ",\n"))) {
unset($sql);
//continue;
} else {
return $step;
}
}
UpgradeTrackingOffset::reset();
$step = set_upgrade_status($tool, $step + 1);
case 3:
// get total number of rows in track_e_login
$sql = "SELECT COUNT(*)\n FROM `" . get_conf('statsDbName') . '`.`' . get_conf('statsTblPrefix') . "track_e_open`";
$tableRows = (int) claro_sql_query_fetch_single_value($sql);
$recoveredOffset = UpgradeTrackingOffset::retrieve();
// get a subgroup of 250 rows and insert group by group in tracking_event table
for ($offset = $recoveredOffset; $offset < $tableRows; $offset += 250) {
// we have to store offset to start again from it if something failed
UpgradeTrackingOffset::store($offset);
$query = "SELECT `open_id`, `open_date`\n FROM `" . get_conf('statsDbName') . '`.`' . get_conf('statsTblPrefix') . "track_e_open`\n ORDER BY `open_date`, `open_id`\n LIMIT " . $offset . ", 250";
// then copy these 250 rows to tracking_event
$eventList = claro_sql_query_fetch_all_rows($query);
// build query to insert all 250 rows
$sql = "INSERT INTO `" . get_conf('statsDbName') . '`.`' . get_conf('statsTblPrefix') . "tracking_event`\n ( `user_id`, `date`, `type`, `data` )\n VALUES\n ";
//inject former data into new table structure
foreach ($eventList as $event) {
$sql .= "(NULL,'" . claro_sql_escape($event['open_date']) . "','platform_access',''),\n";
}
unset($eventList);
if (upgrade_sql_query(rtrim($sql, ",\n"))) {
unset($sql);
//continue;
} else {
return $step;
}
}
UpgradeTrackingOffset::reset();
$step = set_upgrade_status($tool, $step + 1);
case 4:
// order table using dates then recreate primary key with correct autoincrement value
$sqlForUpdate[] = "ALTER TABLE `" . $tbl_mdb_names['tracking_event'] . "` ORDER BY `date`";
$sqlForUpdate[] = "ALTER TABLE `" . $tbl_mdb_names['tracking_event'] . "` ADD `id` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST";
if (upgrade_apply_sql($sqlForUpdate)) {
$step = set_upgrade_status($tool, $step + 1);
} else {
return $step;
}
case 5:
//drop deprecated tracking tables
$sqlForUpdate[] = "DROP TABLE IF EXISTS `" . get_conf('statsDbName') . '`.`' . get_conf('statsTblPrefix') . "track_e_open`";
$sqlForUpdate[] = "DROP TABLE IF EXISTS `" . get_conf('statsDbName') . '`.`' . get_conf('statsTblPrefix') . "track_e_login`";
// we should probably keep this table as it may be usefull for history purpose. By the way it is not used in
// any tracking interface.
//$sqlForUpdate[] = "DROP TABLE IF EXISTS `" . get_conf( 'mainTblPrefix' ) . "track_e_default`";
if (upgrade_apply_sql($sqlForUpdate)) {
$step = set_upgrade_status($tool, $step + 1);
} else {
return $step;
}
unset($sqlForUpdate);
default:
$step = set_upgrade_status($tool, 0);
return $step;
}
return false;
}
/**
* return true if the current message is a platform message,
* false if this is not a platform message
*
* @return boolean true if the current message is a platform message
* false if the current message is not a platform message
*/
public function isPlatformMessage()
{
$tableName = get_module_main_tbl(array('im_recipient'));
$sql = "SELECT DISTINCT sent_to \n" . " FROM `" . $tableName['im_recipient'] . "` \n" . " WHERE message_id = " . $this->getId();
$sentto = claro_sql_query_fetch_single_value($sql);
if ($sentto == 'toAll') {
return true;
} else {
return false;
}
}
/**
* return true if the user in parameter is manager of the course in 2nd parameters
*
* @param int $userId user id
* @param string $courseCode syscode du cours
* @return boolean true if the user is manager of the course
* false if the user is not manager of the course
*/
function claro_is_user_course_manager($userId, $courseCode)
{
$tableName = get_module_main_tbl(array('rel_course_user'));
$sql = "SELECT count(*)" . " FROM `" . $tableName['rel_course_user'] . "`" . " WHERE code_cours = '" . claro_sql_escape($courseCode) . "'" . " AND user_id = " . (int) $userId . " AND isCourseManager = 1";
return claro_sql_query_fetch_single_value($sql) > 0;
}
/**
* load the number of message the message box
*
*/
protected function loadNumberOfMessage()
{
if (!$this->numberOfMessage) {
$strategy = $this->getSelector();
$whereClause = $strategy->getStrategy();
$tableName = get_module_main_tbl(array('im_message', 'user', 'im_recipient'));
$sql = "SELECT count(distinct M.message_id)\n" . " FROM `" . $tableName['im_message'] . "` AS M\n" . " LEFT JOIN `" . $tableName['user'] . "` as U ON M.sender = U.user_id\n" . " LEFT JOIN `" . $tableName['im_recipient'] . "` AS R ON R.message_id = M.message_id" . " " . $whereClause;
$this->numberOfMessage = claro_sql_query_fetch_single_value($sql);
}
}
/**
* Is the module registered in the given course ?
* @param int $toolId main tool id
* @param string $courseId course code
* @return boolean
*/
function is_module_registered_in_course($toolId, $courseId)
{
$tbl_cdb_names = claro_sql_get_course_tbl(claro_get_course_db_name_glued($courseId));
$tblCourseToolList = $tbl_cdb_names['tool'];
$sql = "SELECT COUNT(*) FROM `{$tblCourseToolList}`\n" . "WHERE tool_id = " . (int) $toolId;
$res = claro_sql_query_fetch_single_value($sql);
return $res;
}
public function merge($uidToRemove, $uidToKeep)
{
$mainTbl = claro_sql_get_main_tbl();
// inherit platform admin status ? harmful !
/*$toKeep_isPlatformAdmin = claro_sql_query_fetch_single_value("
SELECT isPlatformAdmin FROM `{$mainTbl['user']}` WHERE user_id = " . (int) $uidToKeep . "
");
$toRemove_isPlatformAdmin = claro_sql_query_fetch_single_value("
SELECT isPlatformAdmin FROM `{$mainTbl['user']}` WHERE user_id = " . (int) $uidToRemove . "
");
if ( $toKeep_isPlatformAdmin && ! $toRemove_isPlatformAdmin )
{
claro_sql_query("UPDATE `{$mainTbl['user']}` SET `isPlatformAdmin` = 1 WHERE user_id = ".(int) $uidToKeep );
}*/
// inherit course creator status
$toKeep_isCourseCreator = claro_sql_query_fetch_single_value("\n SELECT isCourseCreator FROM `{$mainTbl['user']}` WHERE user_id = " . (int) $uidToKeep . "\n ");
$toRemove_isCourseCreator = claro_sql_query_fetch_single_value("\n SELECT isCourseCreator FROM `{$mainTbl['user']}` WHERE user_id = " . (int) $uidToRemove . "\n ");
if ($toRemove_isCourseCreator && !$toKeep_isCourseCreator) {
claro_sql_query("UPDATE `{$mainTbl['user']}` SET `isCourseCreator` = 1 WHERE user_id = " . (int) $uidToKeep);
}
// Get course list for the user to remove
$sql = "\n SELECT\n c.`code` AS `code`,\n cu.`isCourseManager`,\n cu.`profile_id`\n FROM\n `{$mainTbl['course']}` c,\n `{$mainTbl['rel_course_user']}` cu\n WHERE\n cu.user_id = " . (int) $uidToRemove . "\n AND\n c.code = cu.code_cours";
$courseList = claro_sql_query_fetch_all_rows($sql);
foreach ($courseList as $thisCourse) {
// Check if the user to keep is registered to the course
$sql = "\n SELECT\n `code_cours`,\n `isCourseManager`,\n `profile_id`\n FROM\n `{$mainTbl['rel_course_user']}`\n WHERE\n code_cours = '" . claro_sql_escape($thisCourse['code']) . "'\n AND\n user_id = " . (int) $uidToKeep;
$userToKeepCourseList = claro_sql_query_fetch_single_row($sql);
if (!empty($userToKeepCourseList)) {
// inherit isCourseManager
if ($thisCourse['isCourseManager'] == 1 && $userToKeepCourseList['isCourseManager'] != 1) {
if (!claro_sql_query("\n UPDATE `{$mainTbl['rel_course_user']}`\n SET `isCourseManager` = 1\n WHERE code_cours = '" . claro_sql_escape($thisCourse['code']) . "'\n AND user_id = " . (int) $uidToKeep)) {
Console::error("Cannot change rel_course_user from -{$uidToRemove} to +{$uidToKeep} isCourseManager in {$thisCourse['code']}");
$this->hasError = true;
}
}
// inherit profile
if ($thisCourse['profile_id'] > $userToKeepCourseList['profile_id']) {
if (!claro_sql_query("\n UPDATE `{$mainTbl['rel_course_user']}`\n SET `profile_id` = " . (int) $thisCourse['profile_id'] . "\n WHERE code_cours = '" . claro_sql_escape($thisCourse['code']) . "'\n AND user_id = " . (int) $uidToKeep)) {
Console::error("Cannot change rel_course_user from -{$uidToRemove} to +{$uidToKeep} profile in {$thisCourse['code']}");
$this->hasError = true;
}
}
// Remove the user to remove from the course
$sql = "DELETE FROM `{$mainTbl['rel_course_user']}`\n WHERE user_id = " . (int) $uidToRemove . "\n AND code_cours = '" . claro_sql_escape($thisCourse['code']) . "'";
if (!claro_sql_query($sql)) {
Console::error("Cannot change rel_course_user from -{$uidToRemove} to +{$uidToKeep} in {$thisCourse['code']}");
$this->hasError = true;
}
} else {
// Replace the user id of the user to remove
$sql = "UPDATE `{$mainTbl['rel_course_user']}`\n SET user_id = " . (int) $uidToKeep . "\n WHERE user_id = " . (int) $uidToRemove . "\n AND code_cours = '" . claro_sql_escape($thisCourse['code']) . "'";
if (!claro_sql_query($sql)) {
Console::error("Cannot change rel_course_user from -{$uidToRemove} to +{$uidToKeep} in {$thisCourse['code']}");
$this->hasError = true;
}
}
if (!claro_sql_query($sql)) {
Console::error("Cannot change rel_course_user from -{$uidToRemove} to +{$uidToKeep} in {$thisCourse['code']}");
$this->hasError = true;
}
$sql = "UPDATE `{$mainTbl['rel_class_user']}`\n SET user_id = " . (int) $uidToKeep . "\n WHERE user_id = " . (int) $uidToRemove;
if (!claro_sql_query($sql)) {
Console::error("Cannot change rel_class_user from -{$uidToRemove} to +{$uidToKeep} in {$thisCourse['code']}");
$this->hasError = true;
}
// Update course
self::mergeCourseUsers($uidToRemove, $uidToKeep, $thisCourse['code']);
self::mergeCourseModuleUsers($uidToRemove, $uidToKeep, $thisCourse['code']);
// update course messaging
self::mergeCourseMessaging($uidToRemove, $uidToKeep, $thisCourse['code']);
}
// Update modules
self::mergeModuleUsers($uidToRemove, $uidToKeep);
// Update main tracking
self::mergeMainTrackingUsers($uidToRemove, $uidToKeep);
// updtae main messaging
self::mergeMainMessaging($uidToRemove, $uidToKeep);
// Delete old user
$sql = "DELETE FROM `{$mainTbl['user']}`\n WHERE user_id = " . (int) $uidToRemove;
if (!claro_sql_query($sql)) {
Console::error("Cannot delete old user -{$uidToRemove}");
$this->hasError = true;
}
return !self::hasError();
}
function can_answer_message($messageId)
{
$tableName = get_module_main_tbl(array('im_message_status'));
$select = "SELECT count(*)\n" . " FROM `" . $tableName['im_message_status'] . "` as M\n" . " WHERE (M.user_id = " . (int) claro_get_current_user_id() . " OR M.user_id = 0)\n" . " AND M.message_id = " . (int) $messageId;
$nbMessage = claro_sql_query_fetch_single_value($select);
return $nbMessage > 0 ? true : false;
}
/**
* CLAROLINE SQL query wrapper returning only a single result value.
* Useful in some cases because, it avoid nested arrays of results.
*
* @param string $sqlQuery the sql query
* @param handler $dbHandler optional
* @return associative array containing all the result column
* @since 1.5.1
* @see claro_sql_query_fetch_single_value()
*
* @author Hugues Peeters <*****@*****.**>,
* @deprecated since Claroline 1.9, use Claroline::getDatabase() and new classes
* in database/database.lib.php instead
*/
function claro_sql_query_get_single_value($sqlQuery, $dbHandler = '#')
{
return claro_sql_query_fetch_single_value($sqlQuery, $dbHandler);
}
public static function checkUserNameField($data)
{
$errors = array();
$tbl_mdb_names = claro_sql_get_main_tbl();
$tbl_user = $tbl_mdb_names['user'];
foreach ($data as $key => $value) {
if (empty($value)) {
$errors[] = get_lang('Username is empty at line %key', array('%key' => $key));
} elseif (array_search($value, $data) != $key) {
$errors[] = get_lang('Username seems to be duplicate at line %key', array('%key' => $key));
} else {
$sql = "SELECT `user_id` FROM `" . $tbl_user . "` WHERE 1=0 ";
$sql .= " OR `username` like '" . claro_sql_escape($value) . "'";
$userId = claro_sql_query_fetch_single_value($sql);
if ($userId && !is_null($userId)) {
$errors[] = get_lang('Username already exists in the database at line %key', array('%key' => $key));
}
}
}
return $errors;
}
/**
* return the number of platform message
*
* @return int the number of platform message
*/
public function numberOfPlatformMessage()
{
if (!$this->numberOfUnreadMessage) {
$tableName = get_module_main_tbl(array('im_message', 'im_message_status', 'user'));
if (!is_null($this->messageFilter)) {
$strategy = $this->messageFilter->getStrategy();
} else {
$strategy = "";
}
$sql = "SELECT count(*)\n" . " FROM `" . $tableName['im_message'] . "` as M\n" . " LEFT JOIN `" . $tableName['im_message_status'] . "` as R ON M.message_id = R.message_id\n" . " LEFT JOIN `" . $tableName['user'] . "` AS U ON M.sender = U.user_id\n" . " WHERE R.user_id = 0" . "\n" . " " . $strategy;
$this->numberOfPlatformMessage = claro_sql_query_fetch_single_value($sql);
}
return $this->numberOfPlatformMessage;
}
} else {
// If the category can't have course child, look if they haven't already
if ($canHaveCoursesChild == 'FALSE') {
$sql_SearchCourses = " SELECT count(cours_id) num" . " FROM `" . $tbl_course . "`" . " WHERE faculte = '" . claro_sql_escape($facultyEdit['code']) . "'";
$array = claro_sql_query_fetch_all($sql_SearchCourses);
if ($array[0]['num'] > 0) {
$dialogBox->error(get_lang('This category include some courses, you must delete or move them before'));
$canHaveCoursesChild = "TRUE";
} else {
$sql_ChangeInfoFaculty = " UPDATE `" . $tbl_course_node . "`\n SET name='" . claro_sql_escape($_REQUEST['nameCat']) . "',\n code='" . claro_sql_escape($_REQUEST['codeCat']) . "',\n canHaveCoursesChild='" . $canHaveCoursesChild . "'\n WHERE id='" . (int) $_REQUEST['id'] . "'";
claro_sql_query($sql_ChangeInfoFaculty);
$dialogBox->success(get_lang('Changes have been saved'));
}
} else {
$sql_ChangeInfoFaculty = "SELECT id\n FROM `" . $tbl_course_node . "`\n WHERE code = '" . claro_sql_escape($_REQUEST["codeCat"]) . "'\n AND id != '" . (int) $_REQUEST["id"] . "'";
$_id = claro_sql_query_fetch_single_value($sql_ChangeInfoFaculty);
if ($_id) {
$dialogBox->error(get_lang('Code already exists for an other category'));
} else {
$sql_ChangeInfoFaculty = " UPDATE `" . $tbl_course_node . "`\n SET name='" . claro_sql_escape($_REQUEST["nameCat"]) . "',\n code='" . claro_sql_escape($_REQUEST["codeCat"]) . "',\n canHaveCoursesChild='" . $canHaveCoursesChild . "'\n WHERE id='" . (int) $_REQUEST["id"] . "'";
$result = claro_sql_query($sql_ChangeInfoFaculty);
if (!$result) {
$dialogBox->error(get_lang('Code already exists for an other category'));
} else {
// Change code_P for his childeren
if ($_REQUEST['codeCat'] != $facultyEdit['code']) {
$sql_ChangeCodeParent = " UPDATE `" . $tbl_course_node . "`\n SET code_P='" . claro_sql_escape($_REQUEST['codeCat']) . "'\n WHERE code_P='" . claro_sql_escape($facultyEdit['code']) . "'";
claro_sql_query($sql_ChangeCodeParent);
}
// Confirm edition
$dialogBox->success(get_lang('Changes have been saved'));
/**
* load last random question list
*
* @author Dimitri Rambout <*****@*****.**>
* @param $userId id of the user
* @param $exerciseId id of the exercise
* @return array of id
*/
function loadLastRandomQuestionList($userId, $exerciseId)
{
$sql = "SELECT `questions`\n FROM `" . $this->tblRandomQuestions . "`\n WHERE `user_id` = " . (int) $userId . " AND `exercise_id` = " . (int) $exerciseId . " ORDER BY `id` DESC LIMIT 1";
$questions = @unserialize(stripslashes(claro_sql_query_fetch_single_value($sql)));
if (is_array($questions)) {
return $questions;
} else {
return array();
}
}
/**
* Move tracking data from old tables to new ones.
* Note that exercise tracking update is made in quiz_upgrade_to_19 function
* and that tmp table is mostly created to insert in date order data from different old tables
* to the new one
*
* @param integer $course_code
* @return upgrade status
*/
function tracking_data_upgrade_to_19($course_code)
{
$versionRequiredToProceed = '/^1.8/';
$tool = 'CLSTATS_DATA';
global $currentCourseVersion;
$currentCourseDbNameGlu = claro_get_course_db_name_glued($course_code);
if (preg_match($versionRequiredToProceed, $currentCourseVersion)) {
switch ($step = get_upgrade_status($tool, $course_code)) {
case 1:
// drop id to be able to recreate it with correct autoincrement values at last step
$sql = "ALTER TABLE `" . $currentCourseDbNameGlu . "tracking_event` DROP `id`";
if (upgrade_sql_query($sql)) {
$step = set_upgrade_status($tool, $step + 1);
} else {
return $step;
}
unset($sql);
case 2:
// get total number of rows in track_e_access
$sql = "SELECT COUNT(*)\n FROM `" . $currentCourseDbNameGlu . "track_e_access`";
$tableRows = (int) claro_sql_query_fetch_single_value($sql);
$recoveredOffset = UpgradeTrackingOffset::retrieve();
// get a subgroup of 250 rows and insert group by group in tracking_event table
for ($offset = $recoveredOffset; $offset < $tableRows; $offset += 250) {
// we have to store offset to start again from it if something failed
UpgradeTrackingOffset::store($offset);
$query = "SELECT `access_id`, `access_user_id`, `access_date`, `access_tid`\n FROM `" . $currentCourseDbNameGlu . "track_e_access`\n ORDER BY `access_date`, `access_id`\n LIMIT " . $offset . ", 250";
// then copy these 250 rows to tracking_event
$eventList = claro_sql_query_fetch_all_rows($query);
// build query to insert all 250 rows
$sql = "INSERT INTO `" . $currentCourseDbNameGlu . "tracking_event`\n ( `user_id`, `tool_id`, `date`, `type`, `data` )\n VALUES\n ";
//inject former data into new table structure
foreach ($eventList as $event) {
$user_id = !is_null($event['access_user_id']) ? $event['access_user_id'] : "null";
$tool_id = !is_null($event['access_tid']) ? $event['access_tid'] : "null";
$date = $event['access_date'];
$type = !is_null($event['access_tid']) ? 'tool_access' : 'course_access';
$data = '';
$sql .= "(" . $user_id . "," . $tool_id . "," . "'" . claro_sql_escape($date) . "'," . "'" . claro_sql_escape($type) . "'," . "'" . claro_sql_escape($data) . "'),\n";
}
unset($eventList);
if (upgrade_sql_query(rtrim($sql, ",\n"))) {
unset($sql);
//continue;
} else {
return $step;
}
}
UpgradeTrackingOffset::reset();
$step = set_upgrade_status($tool, $step + 1);
case 3:
// get total number of rows in track_e_downloads
$sql = "SELECT COUNT(*)\n FROM `" . $currentCourseDbNameGlu . "track_e_downloads`";
$tableRows = (int) claro_sql_query_fetch_single_value($sql);
$recoveredOffset = UpgradeTrackingOffset::retrieve();
// get a subgroup of 250 rows and insert group by group in tracking_event table
for ($offset = $recoveredOffset; $offset < $tableRows; $offset += 250) {
// we have to store offset to start again from it if something failed
UpgradeTrackingOffset::store($offset);
$query = "SELECT `down_id`, `down_user_id`, `down_date`, `down_doc_path`\n FROM `" . $currentCourseDbNameGlu . "track_e_downloads`\n ORDER BY `down_date`, `down_id`\n LIMIT " . $offset . ", 250";
// then copy these 250 rows to tracking_event
$eventList = claro_sql_query_fetch_all_rows($query);
// build query to insert all 250 rows
$sql = "INSERT INTO `" . $currentCourseDbNameGlu . "tracking_event`\n ( `user_id`, `tool_id`, `date`, `type`, `data` )\n VALUES\n ";
//inject former data into new table structure
foreach ($eventList as $event) {
$user_id = !is_null($event['down_user_id']) ? $event['down_user_id'] : "null";
$tool_id = "null";
$date = $event['down_date'];
$type = 'download';
$data = serialize(array('url' => $event['down_doc_path']));
$sql .= "(" . $user_id . "," . $tool_id . "," . "'" . claro_sql_escape($date) . "'," . "'" . claro_sql_escape($type) . "'," . "'" . claro_sql_escape($data) . "'),\n";
}
unset($eventList);
if (upgrade_sql_query(rtrim($sql, ",\n"))) {
unset($sql);
//continue;
} else {
return $step;
}
}
UpgradeTrackingOffset::reset();
$step = set_upgrade_status($tool, $step + 1);
case 4:
// order table using dates then recreate primary key with correct autoincrement value
$sqlForUpdate[] = "ALTER TABLE `" . $currentCourseDbNameGlu . "tracking_event` ORDER BY `date`";
$sqlForUpdate[] = "ALTER TABLE `" . $currentCourseDbNameGlu . "tracking_event` ADD `id` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST";
if (upgrade_apply_sql($sqlForUpdate)) {
$step = set_upgrade_status($tool, $step + 1);
} else {
return $step;
}
case 5:
//drop deprecated tracking tables and temporary table
$sqlForUpdate[] = "DROP TABLE IF EXISTS `" . $currentCourseDbNameGlu . "track_e_uploads`";
$sqlForUpdate[] = "DROP TABLE IF EXISTS `" . $currentCourseDbNameGlu . "track_e_access`";
$sqlForUpdate[] = "DROP TABLE IF EXISTS `" . $currentCourseDbNameGlu . "track_e_downloads`";
if (upgrade_apply_sql($sqlForUpdate)) {
$step = set_upgrade_status($tool, $step + 1);
} else {
return $step;
}
unset($sqlForUpdate);
default:
$step = set_upgrade_status($tool, 0);
return $step;
}
}
return false;
}
