http://atutor.ca/atutor/mantis/view.php?id=2362

diff --git a/docs/install/db/atutor_schema.sql b/docs/install/db/atutor_schema.sql
index 333bf14..f118873 100644 (file)
--- a/docs/install/db/atutor_schema.sql
+++ b/docs/install/db/atutor_schema.sql
@@ -859,8 +859,7 @@ CREATE TABLE `tests_questions_assoc` (
   `weight` varchar(4) NOT NULL default '',
   `ordering` mediumint(8) unsigned NOT NULL default '0',
   `required` tinyint(3) unsigned NOT NULL default '0',
-  PRIMARY KEY  (`test_id`,`question_id`),
-  KEY `test_id` (`test_id`)
+  PRIMARY KEY  (`test_id`,`question_id`)
 ) TYPE=MyISAM;
 
 # --------------------------------------------------------
@@ -883,6 +882,7 @@ CREATE TABLE `tests_results` (
   `member_id` mediumint(8) unsigned NOT NULL default '0',
   `date_taken` TIMESTAMP NOT NULL,
   `final_score` char(5) NOT NULL default '',
+  `status` TINYINT NOT NULL DEFAULT '0',
   PRIMARY KEY  (`result_id`),
   KEY `test_id` (`test_id`)
 ) TYPE=MyISAM;

diff --git a/docs/install/db/atutor_upgrade_1.5.4_to_1.5.5.sql b/docs/install/db/atutor_upgrade_1.5.4_to_1.5.5.sql
index c462d14..cfc9908 100644 (file)
--- a/docs/install/db/atutor_upgrade_1.5.4_to_1.5.5.sql
+++ b/docs/install/db/atutor_upgrade_1.5.4_to_1.5.5.sql
@@ -5,3 +5,8 @@
 
 ## remove login field - #3032
 ALTER TABLE `forums_threads` DROP `login`;
+
+## refresh test issue - #2362
+ALTER TABLE `tests_questions_assoc` DROP INDEX `test_id`;
+ALTER TABLE `tests_results` ADD `status` TINYINT NOT NULL DEFAULT '0';
+UPDATE TABLE `tests_results` SET status=1;

diff --git a/docs/tools/my_tests.php b/docs/tools/my_tests.php
index 29b9f6d..fd78eaa 100644 (file)
--- a/docs/tools/my_tests.php
+++ b/docs/tools/my_tests.php
@@ -46,7 +46,7 @@ while ($row = mysql_fetch_assoc($result)) {
        $count++;
        echo '<tr>';
        echo '<td>';
-       $sql            = "SELECT COUNT(test_id) AS cnt FROM ".TABLE_PREFIX."tests_results WHERE test_id=".$row['test_id']." AND member_id=".$_SESSION['member_id'];
+       $sql = "SELECT COUNT(test_id) AS cnt FROM ".TABLE_PREFIX."tests_results WHERE status=1 AND test_id=".$row['test_id']." AND member_id=".$_SESSION['member_id'];
 
        $takes_result= mysql_query($sql, $db);
        $takes = mysql_fetch_assoc($takes_result);
@@ -113,7 +113,7 @@ if (!$count) {
 </thead>
 <tbody>
 <?php
-$sql   = "SELECT T.*, R.* FROM ".TABLE_PREFIX."tests T, ".TABLE_PREFIX."tests_results R, ".TABLE_PREFIX."tests_questions_assoc Q WHERE Q.test_id=T.test_id AND R.member_id=$_SESSION[member_id] AND R.test_id=T.test_id AND T.course_id=$_SESSION[course_id] GROUP BY R.result_id ORDER BY R.date_taken DESC";
+$sql   = "SELECT T.*, R.* FROM ".TABLE_PREFIX."tests T, ".TABLE_PREFIX."tests_results R, ".TABLE_PREFIX."tests_questions_assoc Q WHERE R.status=1 AND Q.test_id=T.test_id AND R.member_id=$_SESSION[member_id] AND R.test_id=T.test_id AND T.course_id=$_SESSION[course_id] GROUP BY R.result_id ORDER BY R.date_taken DESC";
 
 $result        = mysql_query($sql, $db);
 $num_results = mysql_num_rows($result);

diff --git a/docs/tools/take_test.php b/docs/tools/take_test.php
index 9a2ea70..6865b40 100644 (file)
--- a/docs/tools/take_test.php
+++ b/docs/tools/take_test.php
@@ -42,7 +42,6 @@ $sql          = "SELECT COUNT(*) AS cnt FROM ".TABLE_PREFIX."tests_results WHERE test_id
 $takes_result= mysql_query($sql, $db);
 $takes = mysql_fetch_assoc($takes_result);     
 
-
 if ( (($test_row['start_date'] > time()) || ($test_row['end_date'] < time())) || 
    ( ($test_row['num_takes'] != AT_TESTS_TAKE_UNLIMITED) && ($takes['cnt'] >= $test_row['num_takes']) )  ) {
        require(AT_INCLUDE_PATH.'header.inc.php');
@@ -54,10 +53,16 @@ if ( (($test_row['start_date'] > time()) || ($test_row['end_date'] < time())) ||
 
 if (isset($_POST['submit'])) {
        // insert
-
-       $sql    = "INSERT INTO ".TABLE_PREFIX."tests_results VALUES (NULL, $tid, $_SESSION[member_id], NOW(), '')";
-       $result = mysql_query($sql, $db);
-       $result_id = mysql_insert_id($db);
+       if ($_SESSION['member_id']) {
+               $sql    = "SELECT result_id FROM ".TABLE_PREFIX."tests_results WHERE test_id=$tid AND member_id=$_SESSION[member_id] AND status=0";
+               $result = mysql_query($sql, $db);
+               $row    = mysql_fetch_assoc($result);
+               $result_id = $row['result_id'];
+       } else {
+               $sql    = "INSERT INTO ".TABLE_PREFIX."tests_results VALUES (NULL, $tid, 0, NOW(), '', 0)";
+               $result = mysql_query($sql, $db);
+               $result_id = mysql_insert_id($db);
+       }
 
        $final_score     = 0;
        $set_final_score = TRUE; // whether or not to save the final score in the results table.
@@ -69,18 +74,21 @@ if (isset($_POST['submit'])) {
                        $obj = TestQuestions::getQuestion($row['type']);
                        $score = $obj->mark($row);
 
-                       $sql    = "INSERT INTO ".TABLE_PREFIX."tests_answers VALUES ($result_id, $row[question_id], $_SESSION[member_id], '{$_POST[answers][$row[question_id]]}', '$score', '')";
+                       if ($_SESSION['member_id']) {
+                               $sql    = "UPDATE ".TABLE_PREFIX."tests_answers SET answer='{$_POST[answers][$row[question_id]]}', score='$score' WHERE result_id=$result_id AND question_id=$row[question_id]";
+                       } else {
+                               $sql    = "INSERT INTO ".TABLE_PREFIX."tests_answers VALUES ($result_id, $row[question_id], 0, '{$_POST[answers][$row[question_id]]}', '$score', '')";
+                       }
                        mysql_query($sql, $db);
 
                        $final_score += $score;
                }
        }
 
-       if ($final_score) {
-               // update the final score (when no open ended questions are found)
-               $sql    = "UPDATE ".TABLE_PREFIX."tests_results SET final_score=$final_score, date_taken=date_taken WHERE result_id=$result_id AND member_id=$_SESSION[member_id]";
-               $result = mysql_query($sql, $db);
-       }
+       // update the final score
+       // update status to complate to fix refresh test issue.
+       $sql    = "UPDATE ".TABLE_PREFIX."tests_results SET final_score=$final_score, date_taken=date_taken, status=1 WHERE result_id=$result_id AND member_id=$_SESSION[member_id]";
+       $result = mysql_query($sql, $db);
 
        $msg->addFeedback('ACTION_COMPLETED_SUCCESSFULLY');
        if (!$_SESSION['enroll']) {
@@ -108,7 +116,20 @@ $title = $test_row['title'];
 
 $_letters = array(_AT('A'), _AT('B'), _AT('C'), _AT('D'), _AT('E'), _AT('F'), _AT('G'), _AT('H'), _AT('I'), _AT('J'));
 
-if ($test_row['random']) {
+// first check if there's an 'in progress' test.
+// this is the only place in the code that makes sure there is only ONE 'in progress' test going on.
+$in_progress = false;
+$sql = "SELECT result_id FROM ".TABLE_PREFIX."tests_results WHERE member_id={$_SESSION['member_id']} AND test_id=$tid AND status=0";
+$result  = mysql_query($sql);
+if ($row = mysql_fetch_assoc($result)) {
+       $result_id = $row['result_id'];
+       $in_progress = true;
+
+       // retrieve the test questions that were saved to `tests_answers`
+
+       $sql    = "SELECT R.*, A.*, Q.* FROM ".TABLE_PREFIX."tests_answers R INNER JOIN ".TABLE_PREFIX."tests_questions_assoc A USING (question_id) INNER JOIN ".TABLE_PREFIX."tests_questions Q USING (question_id) WHERE R.result_id=$result_id AND A.test_id=$tid";
+       
+} else if ($test_row['random']) {
        /* Retrieve 'num_questions' question_id randomly choosed from those who are related to this test_id*/
 
        $non_required_questions = array();
@@ -134,7 +155,6 @@ if ($test_row['random']) {
        $random_id_string = implode(',', $required_questions);
 
        $sql = "SELECT TQ.*, TQA.* FROM ".TABLE_PREFIX."tests_questions TQ INNER JOIN ".TABLE_PREFIX."tests_questions_assoc TQA USING (question_id) WHERE TQ.course_id=$_SESSION[course_id] AND TQA.test_id=$tid AND TQA.question_id IN ($random_id_string)";
-
 } else {
        $sql    = "SELECT TQ.*, TQA.* FROM ".TABLE_PREFIX."tests_questions TQ INNER JOIN ".TABLE_PREFIX."tests_questions_assoc TQA USING (question_id) WHERE TQ.course_id=$_SESSION[course_id] AND TQA.test_id=$tid ORDER BY TQA.ordering, TQA.question_id";
 }
@@ -156,6 +176,12 @@ if ($test_row['random']) {
        shuffle($questions);
 }
 
+// save $questions with no response, and set status to 'in progress' in test_results <---
+if ($_SESSION['member_id'] && !$in_progress) {
+       $sql    = "INSERT INTO ".TABLE_PREFIX."tests_results VALUES (NULL, $tid, $_SESSION[member_id], NOW(), '', 0)";
+       $result = mysql_query($sql, $db);
+       $result_id = mysql_insert_id($db);
+}
 ?>
 <form method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">
 <input type="hidden" name="tid" value="<?php echo $tid; ?>" />
@@ -175,6 +201,11 @@ if ($test_row['random']) {
 
        <?php
        foreach ($questions as $row) {
+               if ($_SESSION['member_id'] && !$in_progress) {
+                       $sql    = "INSERT INTO ".TABLE_PREFIX."tests_answers VALUES ($result_id, $row[question_id], $_SESSION[member_id], '', '', '')";
+                       mysql_query($sql, $db);
+               }
+
                $obj = TestQuestions::getQuestion($row['type']);
                $obj->display($row);
        }

diff --git a/docs/tools/tests/index.php b/docs/tools/tests/index.php
index 1be609c..c4b6018 100644 (file)
--- a/docs/tools/tests/index.php
+++ b/docs/tools/tests/index.php
@@ -115,13 +115,13 @@ $cols=6;
                        ?></td>
                        <td><?php
                                //get # marked submissions
-                               $sql_sub = "SELECT COUNT(*) AS sub_cnt FROM ".TABLE_PREFIX."tests_results WHERE test_id=".$row['test_id'];
+                               $sql_sub = "SELECT COUNT(*) AS sub_cnt FROM ".TABLE_PREFIX."tests_results WHERE status=1 AND test_id=".$row['test_id'];
                                $result_sub     = mysql_query($sql_sub, $db);
                                $row_sub = mysql_fetch_assoc($result_sub);
                                echo $row_sub['sub_cnt'].' '._AT('submissions').', ';
 
                                //get # submissions
-                               $sql_sub = "SELECT COUNT(*) AS marked_cnt FROM ".TABLE_PREFIX."tests_results WHERE test_id=".$row['test_id']." AND final_score=''";
+                               $sql_sub = "SELECT COUNT(*) AS marked_cnt FROM ".TABLE_PREFIX."tests_results WHERE status=1 AND test_id=".$row['test_id']." AND final_score=''";
                                $result_sub     = mysql_query($sql_sub, $db);
                                $row_sub = mysql_fetch_assoc($result_sub);
                                echo $row_sub['marked_cnt'].' '._AT('unmarked');

diff --git a/docs/tools/tests/results.php b/docs/tools/tests/results.php
index 1850b8e..abdfbb0 100644 (file)
--- a/docs/tools/tests/results.php
+++ b/docs/tools/tests/results.php
@@ -75,16 +75,16 @@ $anonymous = $row['anonymous'];
 $random = $row['random'];
 
 //count total
-$sql   = "SELECT count(*) as cnt FROM ".TABLE_PREFIX."tests_results R LEFT JOIN ".TABLE_PREFIX."members M USING (member_id) WHERE R.test_id=$tid";
+$sql   = "SELECT count(*) as cnt FROM ".TABLE_PREFIX."tests_results R LEFT JOIN ".TABLE_PREFIX."members M USING (member_id) WHERE R.test_id=$tid AND R.status=1";
 $result        = mysql_query($sql, $db);
 $row   = mysql_fetch_array($result);
 $num_sub = $row['cnt'];
 
 //get results based on filtre and sorting
 if ($anonymous == 1) {
-       $sql    = "SELECT R.*, '<em>"._AT('anonymous')."</em>' AS login FROM ".TABLE_PREFIX."tests_results R WHERE R.test_id=$tid $status ORDER BY $col $order";
+       $sql    = "SELECT R.*, '<em>"._AT('anonymous')."</em>' AS login FROM ".TABLE_PREFIX."tests_results R WHERE R.test_id=$tid AND R.status=1 $status ORDER BY $col $order";
 } else {       
-       $sql    = "SELECT R.*, login, CONCAT(first_name, ' ', second_name, ' ', last_name) AS full_name, R.final_score+0.0 AS fs FROM ".TABLE_PREFIX."tests_results R LEFT JOIN  ".TABLE_PREFIX."members M USING (member_id) WHERE R.test_id=$tid $status ORDER BY $col $order, R.final_score $order";
+       $sql    = "SELECT R.*, login, CONCAT(first_name, ' ', second_name, ' ', last_name) AS full_name, R.final_score+0.0 AS fs FROM ".TABLE_PREFIX."tests_results R LEFT JOIN  ".TABLE_PREFIX."members M USING (member_id) WHERE R.test_id=$tid AND R.status=1 $status ORDER BY $col $order, R.final_score $order";
 }
 
 $result = mysql_query($sql, $db);
@@ -105,7 +105,7 @@ $num_results = mysql_num_rows($result);
 if (isset($_GET['status']) && ($_GET['status'] != '') && ($_GET['status'] == 0)) {
        $num_unmarked = $num_results;
 } else {
-       $sql            = "SELECT count(*) as cnt FROM ".TABLE_PREFIX."tests_results R, ".TABLE_PREFIX."members M WHERE R.test_id=$tid AND R.member_id=M.member_id AND R.final_score=''";
+       $sql            = "SELECT count(*) as cnt FROM ".TABLE_PREFIX."tests_results R, ".TABLE_PREFIX."members M WHERE R.test_id=$tid AND R.status=1 AND R.member_id=M.member_id AND R.final_score=''";
        $result = mysql_query($sql, $db);
        $row = mysql_fetch_array($result);
        $num_unmarked = $row['cnt'];

diff --git a/docs/tools/tests/results_all.php b/docs/tools/tests/results_all.php
index ffd759c..adf36e6 100644 (file)
--- a/docs/tools/tests/results_all.php
+++ b/docs/tools/tests/results_all.php
@@ -77,7 +77,7 @@ $num_questions = count($questions);
 
 //get all the marked tests for this test
 $guest_text = '- '._AT('guest').' -';
-$sql   = "SELECT R.*, M.login FROM ".TABLE_PREFIX."tests_results R LEFT JOIN ".TABLE_PREFIX."members M USING (member_id) WHERE R.test_id=$tid AND R.final_score<>'' ORDER BY M.login, R.date_taken";
+$sql   = "SELECT R.*, M.login FROM ".TABLE_PREFIX."tests_results R LEFT JOIN ".TABLE_PREFIX."members M USING (member_id) WHERE R.status=1 AND R.test_id=$tid AND R.final_score<>'' ORDER BY M.login, R.date_taken";
 $result = mysql_query($sql, $db);
 $num_results = mysql_num_rows($result);
 if ($row = mysql_fetch_assoc($result)) {

diff --git a/docs/tools/tests/results_all_csv.php b/docs/tools/tests/results_all_csv.php
index a6ad1e5..5846b86 100644 (file)
--- a/docs/tools/tests/results_all_csv.php
+++ b/docs/tools/tests/results_all_csv.php
@@ -79,7 +79,7 @@ echo $nl;
 $guest_text = '- '._AT('guest').' -';
 
 //get test results
-$sql   = "SELECT R.*, M.login FROM ".TABLE_PREFIX."tests_results R LEFT JOIN ".TABLE_PREFIX."members M USING (member_id) WHERE R.test_id=$tid AND R.final_score<>'' ORDER BY M.login, R.date_taken";
+$sql   = "SELECT R.*, M.login FROM ".TABLE_PREFIX."tests_results R LEFT JOIN ".TABLE_PREFIX."members M USING (member_id) WHERE R.status=1 AND R.test_id=$tid AND R.final_score<>'' ORDER BY M.login, R.date_taken";
 $result = mysql_query($sql, $db);
 $num_results = mysql_num_rows($result);
 if ($row = mysql_fetch_array($result)) {

diff --git a/docs/tools/tests/results_all_quest.php b/docs/tools/tests/results_all_quest.php
index a364bf7..1340004 100644 (file)
--- a/docs/tools/tests/results_all_quest.php
+++ b/docs/tools/tests/results_all_quest.php
@@ -62,7 +62,7 @@ $long_qs = substr($long_qs, 0, -1);
 //get the answers:  count | q_id | answer\r
 $sql = "SELECT count(*), A.question_id, A.answer, A.score\r
                FROM ".TABLE_PREFIX."tests_answers A, ".TABLE_PREFIX."tests_results R\r
-               WHERE R.result_id=A.result_id AND R.final_score<>'' AND R.test_id=$tid\r
+               WHERE R.status=1 AND R.result_id=A.result_id AND R.final_score<>'' AND R.test_id=$tid\r
                GROUP BY A.question_id, A.answer\r
                ORDER BY A.question_id, A.answer";\r
 $result = mysql_query($sql, $db);\r

diff --git a/docs/tools/tests/view_results.php b/docs/tools/tests/view_results.php
index b02851c..8952bef 100644 (file)
--- a/docs/tools/tests/view_results.php
+++ b/docs/tools/tests/view_results.php
@@ -53,7 +53,7 @@ if ($_POST['cancel']) {
                }
        }
 
-       $sql    = "UPDATE ".TABLE_PREFIX."tests_results SET final_score='$final_score', date_taken=date_taken WHERE result_id=$rid";
+       $sql    = "UPDATE ".TABLE_PREFIX."tests_results SET final_score='$final_score', date_taken=date_taken WHERE result_id=$rid AND status=1";
        $result = mysql_query($sql, $db);
 
        $msg->addFeedback('RESULTS_UPDATED');