00001 <?php
00002 if(!defined('__PRAGYAN_CMS'))
00003 {
00004 header($_SERVER['SERVER_PROTOCOL'].' 403 Forbidden');
00005 echo "<h1>403 Forbidden<h1><h4>You are not authorized to access the page.</h4>";
00006 echo '<hr/>'.$_SERVER['SERVER_SIGNATURE'];
00007 exit(1);
00008 }
00009
00010
00011
00012
00013 function isQuizEvaluated($quizId) {
00014 $countQuery = "SELECT COUNT(*) FROM `quiz_userattempts` WHERE `quiz_marksallotted` IS NULL AND `page_modulecomponentid` = $quizId";
00015 $countResult = mysql_query($countQuery);
00016 $countRow = mysql_fetch_row($countResult);
00017 return $countRow[0] == 0;
00018 }
00019
00024 function evaluateQuiz($quizId) {
00025 $updateQuery = <<<UPDATEQUERY
00026 UPDATE `quiz_answersubmissions` SET `quiz_marksallotted` = (
00027 SELECT
00028 IF(`quiz_submittedanswer` IS NULL OR `quiz_submittedanswer` = '', 0,
00029 IF(`ques`.`quiz_questiontype` = 'subjective', `quiz_answersubmissions`.`quiz_marksallotted`,
00030 IF(`quiz_submittedanswer` = `quiz_rightanswer`, `question_positivemarks`, -`question_negativemarks`)
00031 )
00032 ) AS `mark`
00033 FROM `quiz_questions` AS `ques`, (SELECT * FROM `quiz_answersubmissions`) AS `ans`, `quiz_weightmarks` AS `weights`
00034 WHERE
00035 `ques`.`page_modulecomponentid` = $quizId AND
00036 `ans`.`page_modulecomponentid` = $quizId AND
00037 `weights`.`page_modulecomponentid` = $quizId AND
00038 `weights`.`question_weight` = `ques`.`quiz_questionweight` AND
00039 `ques`.`quiz_sectionid` = `ans`.`quiz_sectionid` AND
00040 `ques`.`quiz_questionid` = `ans`.`quiz_questionid` AND
00041 `ques`.`quiz_questiontype` IN ('sso', 'mso') AND
00042 `ques`.`quiz_sectionid` = `quiz_answersubmissions`.`quiz_sectionid` AND
00043 `ques`.`quiz_questionid` = `quiz_answersubmissions`.`quiz_questionid` AND
00044 `ans`.`user_id` = `quiz_answersubmissions`.`user_id`
00045 )
00046 WHERE `page_modulecomponentid` = $quizId
00047 UPDATEQUERY;
00048
00049
00050 $updateResult = mysql_query($updateQuery);
00051 if (!$updateResult) {
00052 displayerror('Database Error. Could not correct questions.');
00053 return false;
00054 }
00055
00056 return true;
00057 }
00062 function updateSectionMarks($quizId) {
00063 $updateQuery = "UPDATE `quiz_userattempts` SET `quiz_marksallotted` = (" .
00064 "SELECT SUM(`quiz_marksallotted`) FROM `quiz_answersubmissions` WHERE " .
00065 "`quiz_answersubmissions`.`user_id` = `quiz_userattempts`.`user_id` AND " .
00066 "`quiz_answersubmissions`.`quiz_sectionid` = `quiz_userattempts`.`quiz_sectionid` AND " .
00067 "`quiz_answersubmissions`.`page_modulecomponentid` = $quizId" .
00068 ") WHERE `page_modulecomponentid` = $quizId";
00069 $updateResult = mysql_query($updateQuery);
00070
00071 if (!$updateResult) {
00072 displayerror('Database Error. Could not update section marks. ' . $updateQuery . ' ' . mysql_error());
00073 return false;
00074 }
00075
00076 return true;
00077 }
00078
00083 function getWeights($quizId) {
00084 $weighs = array();
00085 $result = mysql_query("SELECT `quiz_questionweight` FROM `quiz_questions` WHERE `page_modulecomponentid` = $quizId AND `quiz_questionweight` NOT IN (SELECT `question_weight` FROM `quiz_weightmarks` WHERE `page_modulecomponentid` = $quizId)");
00086 while($row = mysql_fetch_assoc($result))
00087 $weighs[] = $row['quiz_questionweight'];
00088 return $weighs;
00089 }
00090
00095 function getQuizUserListHtml($quizId) {
00096
00097
00098 $quizRow = getQuizRow($quizId);
00099 $weights = getWeights($quizId);
00100
00101 if(count($weights) > 0) {
00102 displayerror("Marks for questions with weight " . join(", ", $weights) . " is not set. Correct the quiz after setting marks for all weigh. You can set that in <a href='./+edit#quizWeightMarks'>Edit</a>.");
00103 return '';
00104 }
00105
00106 if (isset($_POST['btnCalculateMarks']))
00107 {
00108 evaluateQuiz($quizId);
00109 updateSectionMarks($quizId);
00110 }
00111
00112
00113 $tableJqueryStuff="";
00114 $numSecColumns=0;
00115 $userTable = MYSQL_DATABASE_PREFIX . 'users';
00116 $markQuery = "SELECT `$userTable`.`user_email` AS `email`, `$userTable`.`user_id` AS `user_id`, SUM(`quiz_marksallotted`) AS `total`, MIN(`quiz_attemptstarttime`) AS `starttime`, MAX(`quiz_submissiontime`) AS `finishtime`, TIMEDIFF(MAX(`quiz_submissiontime`), MIN(`quiz_attemptstarttime`)) AS `timetaken` FROM `$userTable`, `quiz_userattempts` WHERE " .
00117 "`$userTable`.`user_id` = `quiz_userattempts`.`user_id` AND " .
00118 "`quiz_userattempts`.`page_modulecomponentid` = $quizId " .
00119 "GROUP BY `quiz_userattempts`.`user_id` ORDER BY `total` DESC, `timetaken`, `starttime`, `finishtime`, `email`";
00120
00121 $profileQuery = 'SELECT `form_elementname` FROM `form_elementdesc` WHERE `page_modulecomponentid` = 0 ORDER BY `form_elementrank`';
00122 $profileResult = mysql_query($profileQuery);
00123 $profilecolumns=array();
00124 while($profileRow = mysql_fetch_row($profileResult)) {
00125 $profilecolumns['form0_' . $profileRow[0]] = $profileRow[0];
00126 }
00127
00128
00129 $markResult = mysql_query($markQuery);
00130 if (!$markResult) {
00131 displayerror($markQuery . ' ' . mysql_error());
00132 }
00133 $query = mysql_fetch_array(mysql_query("SELECT `quiz_title` FROM `quiz_descriptions` WHERE `page_modulecomponentid` = $quizId"));
00134 $result = mysql_query("SELECT `quiz_sectiontitle` FROM `quiz_sections` WHERE `page_modulecomponentid` = '$quizId' ORDER BY `quiz_sectionid`");
00135 $sectionHead = "";
00136 $secCols="";
00137 $toggleColumns="<tr><td><input type='checkbox' onclick='fnShowHide(0);' checked />User Full Name<br/></td>";
00138 $toggleColumns.="<td><input type='checkbox' onclick='fnShowHide(1);' />User Email<br/></td>";
00139 $toggleColumns.="<td><input type='checkbox' onclick='fnShowHide(2);' checked />Marks<br/></td>";
00140 $toggleColumns.="<td><input type='checkbox' onclick='fnShowHide(3);' checked />Time Taken<br/></td>";
00141 $toggleColumns.="<td><input type='checkbox' onclick='fnShowHide(4);' />Started<br/></td>";
00142 $toggleColumns.="<td><input type='checkbox' onclick='fnShowHide(5);' />Finished<br/></td>";
00143
00144 $c=6;
00145 while($row = mysql_fetch_array($result))
00146 {
00147 $sectionHead .= "<th>Section : {$row['quiz_sectiontitle']}</th>";
00148 $tableJqueryStuff.="null,";
00149 if($c%6==0) $secCols.="</tr><tr>";
00150 $secCols.="<td><input type='checkbox' onclick='fnShowHide($c);' checked />Section : {$row['quiz_sectiontitle']}<br/></td>";
00151 $numSecColumns++;
00152 $c++;
00153 }
00154 $toggleColumns.=$secCols;
00155
00156 $columnNames=array();
00157 foreach($profilecolumns as $columnName => $columnTitle)
00158 {
00159 $sectionHead .= "<th>$columnTitle</th>\n";
00160 $columnNames[] = $columnName;
00161
00162
00163 $checked="checked";
00164 if(!($columnName=="useremail" || $columnName=="registrationdate" || $columnName=="lastupdated"))
00165 {
00166 $tableJqueryStuff.="/* $columnTitle */ { \"bVisible\": false },";
00167 $checked="";
00168 }
00169 else $tableJqueryStuff.="null,";
00170
00171 if($c%6==0)
00172 $toggleColumns.="</tr><tr>";
00173 $toggleColumns.="<td><input type='checkbox' onclick='fnShowHide($c);' $checked />$columnTitle <br/></td>";
00174
00175 $c=$c+1;
00176 }
00177
00178 $toggleColumns.="</tr>";
00179
00180 global $urlRequestRoot, $cmsFolder, $STARTSCRIPTS;
00181
00182 $tableJqueryStuff=<<<STUFF
00183 null,
00184 { "bVisible": false },
00185 null,
00186 null,
00187 { "bVisible": false },
00188 { "bVisible": false },
00189 $tableJqueryStuff
00190 null
00191 STUFF;
00192
00193 $smarttable = smarttable::render(array('userstable'),array('userstable'=>array('aoColumns'=>"$tableJqueryStuff")));
00194
00195 $STARTSCRIPTS.="initSmartTable();";
00196
00197
00198 $userListHtml = <<<HEAD
00199 $smarttable
00200 <script type="text/javascript" charset="utf-8">
00201 function fnShowHide( iCol )
00202 {
00203 var bVis = oTable.fnSettings().aoColumns[iCol].bVisible;
00204 oTable.fnSetColumnVis( iCol, bVis ? false : true );
00205 }
00206
00207 </script>
00208
00209 HEAD;
00210
00211 global $ICONS_SRC,$ICONS;
00212 $quizName=$query[0];
00213 $userListHtml .= "<h3>User Submissions for Quiz: {$query[0]}</h3>
00214 <fieldset><legend>Select Columns</legend><table>$toggleColumns</table></fieldset>" .
00215 "<form action='./+correct' method=POST><input type='submit' value='Calculate Marks' name='btnCalculateMarks' />
00216 <form action='./+correct' method=POST><input type='submit' value='Save As Excel' name='save_as_excel' /></form>";
00217 $userListTable = "
00218 <table class=\"userlisttable display\" border=\"1\" id='userstable'>" .
00219 "<thead><tr><th>User Full Name</th><th>User Email</th><th>Total Marks</th><th>Time Taken</th><th>Started</th><th>Finished</th>$sectionHead<th>Action</th></tr></thead><tbody>";
00220
00221
00222 while ($markRow = mysql_fetch_assoc($markResult)) {
00223 $userMarks = "";
00224 $marksResult = mysql_query("SELECT `quiz_marksallotted`,`quiz_sectionid` FROM `quiz_userattempts` WHERE `user_id` = {$markRow['user_id']} AND `page_modulecomponentid` = $quizId ORDER BY `quiz_sectionid`");
00225 $cc=1;
00226
00227 while($row = mysql_fetch_array($marksResult))
00228 {
00229
00230 if($row['quiz_sectionid']!=$cc)
00231 {
00232 while($row['quiz_sectionid']>$cc) { $userMarks .= "<td>-0</td>"; $cc++; }
00233 }
00234 $userMarks .= "<td>{$row['quiz_marksallotted']}</td>";
00235 $cc++;
00236
00237 }
00238
00239 while($cc<=$numSecColumns) { $userMarks .= "<td>-0</td>"; $cc++;}
00240
00241 if (is_null($markRow['finishtime'])) {
00242 $markRow['finished'] = 0;
00243 $markRow['finishtime'] = 'NULL';
00244 }
00245 $userfullname=getUserFullNameFromEmail($markRow['email']);
00246
00247 $elementDataQuery = 'SELECT `form_elementdata`, `form_elementdesc`.`form_elementid`, `form_elementdesc`.`form_elementname`, `form_elementdesc`.`form_elementtype` FROM `form_elementdesc`, `form_elementdata` WHERE ' .
00248 "`form_elementdata`.`page_modulecomponentid` = 0 AND `user_id` = {$markRow['user_id']} AND " .
00249 "`form_elementdata`.`page_modulecomponentid` = `form_elementdesc`.`page_modulecomponentid` AND " .
00250 "`form_elementdata`.`form_elementid` = `form_elementdesc`.`form_elementid` ORDER BY `form_elementrank`";
00251 $elementDataResult = mysql_query($elementDataQuery) or die($elementDataQuery . '<br />' . mysql_error());
00252 $elementRow=array();
00253 while($elementDataRow = mysql_fetch_assoc($elementDataResult)) {
00254 $elementRow['form0_' . $elementDataRow['form_elementname']] = $elementDataRow['form_elementdata'];
00255 if($elementDataRow['form_elementtype'] == 'file') {
00256 $elementRow['form0_' . $elementDataRow['form_elementname']] = '<a href="./'.$elementDataRow['form_elementdata'].'">' . $elementDataRow['form_elementdata'] . '</a>';
00257 }
00258 }
00259
00260
00261 $display=array();
00262 $columnCount = count($columnNames);
00263 for($i = 0; $i < count($columnNames); $i++) {
00264 if(isset($elementRow[$columnNames[$i]])) {
00265 $display[] = $elementRow[$columnNames[$i]];
00266 }
00267
00268
00269
00270 }
00271
00272 $profileStuff = '';
00273 if(count($display))
00274 $profileStuff='<td>'.join($display,'</td><td>').'</td>';
00275
00276
00277
00278 if($userfullname=="") $userfullname="Anonymous";
00279
00280 $userListTable .= "<tr><td>$userfullname</td><td>{$markRow['email']}</td><td>{$markRow['total']}</td><td>{$markRow['timetaken']}</td><td>{$markRow['starttime']}</td><td>{$markRow['finishtime']}</td>$userMarks $profileStuff";
00281
00282
00283 $userListTable .= '<td><form name="userclearform" method="POST" action=""><input type="hidden" name="hdnUserId" id="hdnUserId" value="' . $markRow['user_id'] . "\" /><a href=\"./+correct&useremail={$markRow['email']}\">".$ICONS['Correct']['small'].'</a><input type="image" src="'.$ICONS_SRC["Delete"]["small"].'" name="btnDeleteUser" id="btnDeleteUser" value="Reject Submission" title="Reject Submission"/></form></td>';
00284
00285
00286 $userListTable .= "</tr>\n";
00287 }
00288 $userListTable .= "</tbody></table>\n";
00289
00290
00291 if(isset($_POST['save_as_excel']))
00292 {
00293 header("Pragma: public");
00294 header("Expires: 0");
00295 header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
00296 header("Cache-Control: private",false);
00297 header("Content-Type: application/vnd.ms-excel");
00298 header("Content-Disposition: attachment; filename=\"$quizName.xls\";" );
00299 header("Content-Transfer-Encoding: binary");
00300 echo $userListTable;
00301 exit(1);
00302 }
00303
00304 return $userListHtml.$userListTable;
00305 }
00306
00311 function getQuizCorrectForm($quizId, $userId) {
00312 $marks = mysql_fetch_array(mysql_query("SELECT SUM(`quiz_marksallotted`) AS `total`, MIN(`quiz_attemptstarttime`) AS `starttime`, MAX(`quiz_submissiontime`) AS `finishtime`, TIMEDIFF(MAX(`quiz_submissiontime`), MIN(`quiz_attemptstarttime`)) AS `timetaken` FROM `quiz_userattempts` WHERE `user_id` = {$userId} AND `page_modulecomponentid` = $quizId"));
00313 $title = mysql_fetch_array(mysql_query("SELECT `quiz_title` FROM `quiz_descriptions` WHERE `page_modulecomponentid` = '$quizId'"));
00314
00315 $correctFormHtml = "";
00316 $sectionHead="";
00317
00318 $sections = mysql_query("SELECT `quiz_sections`.`quiz_sectiontitle` AS `quiz_sectiontitle`, `quiz_sections`.`quiz_sectionid` AS `quiz_sectionid`, `quiz_marksallotted` FROM `quiz_userattempts` JOIN `quiz_sections` ON `quiz_userattempts`.`quiz_sectionid` = `quiz_sections`.`quiz_sectionid` WHERE `user_id` = $userId AND `quiz_userattempts`.`page_modulecomponentid` = $quizId AND `quiz_sections`.`page_modulecomponentid` = $quizId");
00319
00320 while($sectionsRow = mysql_fetch_array($sections)) {
00321 $correctFormHtml .= "<h4>{$sectionsRow['quiz_sectiontitle']}(Marks: {$sectionsRow['quiz_marksallotted']})</h4>";
00322 $sectionHead .="<td><b>{$sectionsRow['quiz_sectiontitle']}</b> section marks: {$sectionsRow['quiz_marksallotted']}</td>";
00323
00324
00325 $questionQuery = "SELECT `quiz_questions`.`quiz_questionid` AS `quiz_questionid`, " .
00326 "`quiz_questions`.`quiz_question` AS `quiz_question`, `quiz_questiontype`, " .
00327 "`quiz_rightanswer`, `quiz_submittedanswer`, `quiz_marksallotted`,`quiz_questions`.`quiz_sectionid` " .
00328 "FROM `quiz_questions`, `quiz_answersubmissions` WHERE " .
00329 "`quiz_questions`.`page_modulecomponentid` = $quizId AND " .
00330 "`quiz_questions`.`page_modulecomponentid` = `quiz_answersubmissions`.`page_modulecomponentid` AND " .
00331 "`quiz_questions`.`quiz_sectionid` = `quiz_answersubmissions`.`quiz_sectionid` AND " .
00332 "`quiz_questions`.`quiz_questionid` = `quiz_answersubmissions`.`quiz_questionid` AND " .
00333 "`quiz_questions`.`quiz_sectionid` = {$sectionsRow['quiz_sectionid']} AND " .
00334 "`user_id` = $userId ORDER BY `quiz_answersubmissions`.`quiz_questionrank`";
00335
00336
00337 $questionResult = mysql_query($questionQuery);
00338
00339 if (!$questionResult)
00340 displayerror($questionQuery . '<br />' . mysql_error());
00341
00342 while ($questionRow = mysql_fetch_assoc($questionResult)) {
00343 $correctFormHtml .= '<table class="quiz_' . (is_null($questionRow['quiz_marksallotted']) || floatval($questionRow['quiz_marksallotted']) <= 0 ? 'wrong' : 'right') . "answer\"><tr><td colspan=\"2\">{$questionRow['quiz_question']}</td></tr>\n";
00344 if ($questionRow['quiz_questiontype'] == 'subjective') {
00345 $submittedAnswers = array();
00346 $submittedAnswers[] = $questionRow['quiz_submittedanswer'];
00347 $correctAnswers = array();
00348 $correctAnswers[] = $questionRow['quiz_rightanswer'];
00349 $correctFormHtml .= '<tr><td nowrap="nowrap" width="10%">Submitted Answer:</td><td>'.implode("<br />\n", $submittedAnswers) . "</td></tr>\n";
00350 $correctFormHtml .= '<tr><td nowrap="nowrap" width="10%">Correct Answer:</td><td>'.implode("<br />\n", $correctAnswers) . "</td></tr>\n";
00351 $correctFormHtml .= "<tr><td>Mark:</td><td><form method=POST action='./+correct&useremail=" . safe_html($_GET['useremail']) . "'><input type=hidden name=quizid value='{$quizId}'><input type=hidden name=sectionid value={$questionRow['quiz_sectionid']}><input type=hidden name=questionid value={$questionRow['quiz_questionid']}><input type=hidden name=userid value={$userId}><input type=text name=mark size=5 value='{$questionRow['quiz_marksallotted']}'><input type=submit value='Submit' name=btnSetMark></form></td></tr>";
00352 }
00353 elseif ($questionRow['quiz_questiontype'] == 'sso' || $questionRow['quiz_questiontype'] == 'mso') {
00354 $optionList = getQuestionOptionList($quizId, $sectionsRow['quiz_sectionid'], $questionRow['quiz_questionid']);
00355 $options = array();
00356 for ($i = 0; $i < count($optionList); ++$i)
00357 $options[$optionList[$i]['quiz_optionid']] = $optionList[$i];
00358
00359 $correctAnswers = array();
00360 $rightAnswerIds = explode('|', $questionRow['quiz_rightanswer']);
00361 for ($i = 0; $i < count($rightAnswerIds); ++$i)
00362 $correctAnswers[] = $options[$rightAnswerIds[$i]]['quiz_optiontext'];
00363
00364 $submittedAnswers = array();
00365 $submittedAnswerIds = explode('|', $questionRow['quiz_submittedanswer']);
00366 for ($i = 0; $i < count($submittedAnswerIds); ++$i)
00367 $submittedAnswers[] = $options[$submittedAnswerIds[$i]]['quiz_optiontext'];
00368 $correctFormHtml .= '<tr><td nowrap="nowrap" width="10%">Submitted Answer:</td><td>' . implode("<br />\n", $submittedAnswers) . "</td></tr>\n";
00369 $correctFormHtml .= "<tr><td nowrap=\"nowrap\" width=\"10%\">Correct Answer:</td><td>" . implode("<br />\n", $correctAnswers) . "</td></tr>\n";
00370 $correctFormHtml .= "<tr><td>Mark:</td><td>{$questionRow['quiz_marksallotted']}</td></tr>";
00371 }
00372 $correctFormHtml .= "</table>\n";
00373 }
00374 }
00375 $quizcorrectinfo="<h3>{$title['quiz_title']} - Quiz Answers Correct form for user: " . safe_html($_GET['useremail']) . "</h3><form name='userclearform' method='POST' action='./+correct'><a href='./+correct'><<Back</a> <input type='hidden' name='hdnUserId' id='hdnUserId' value='{$userId}' /><input type='submit' name='btnDeleteUser' id='btnDeleteUser' value='Reject Submission' /></form><table width=100%><tr><td>Total marks: {$marks['total']}</td>$sectionHead<td>Start time: {$marks['starttime']}</td><td>Finish time: {$marks['finishtime']}</td><td>Time taken: {$marks['timetaken']}</td></tr></table>";
00376 $correctFormHtml .= "<a href='./+correct'><<Back</a>";
00377 return $quizcorrectinfo.$correctFormHtml;
00378 }