function getUserPaperDuration($uid, $courseId)
{
$tblUserSection = new DB_Haodu_UserSection();
$list = $tblUserSection->fetchAll("section_id", "where uid={$uid} and course_id={$courseId}");
if (!$list) {
return 0;
}
$sectionIds = $tblUserSection->columnRow($list, "section_id");
$tblPractiseAnswerHistory = new DB_Haodu_PractiseAnswerHistory();
$practiseList = $tblPractiseAnswerHistory->fetchAll("practise_id", "where uid={$uid} and section_id in (" . implode(",", $sectionIds) . ")");
if (!$practiseList) {
return 0;
}
$practiseIds = $tblPractiseAnswerHistory->columnRow($practiseList, "practise_id");
$tblCoursePractise = new DB_Haodu_CoursePractise();
$videoList = $tblCoursePractise->fetchAll("video_id", "where practise_id in (" . implode(",", $practiseIds) . ")");
if (!$videoList) {
return 0;
}
$videoIds = $tblCoursePractise->columnRow($videoList, "video_id");
$tblUserVideo = new DB_Haodu_UserVideo();
$videoList = $tblUserVideo->fetchAll("video_id", "where uid={$uid} and video_id in (" . implode(",", $videoIds) . ")");
if (!$videoList) {
return 0;
}
$videoIds = $tblCoursePractise->columnRow($videoList, "video_id");
$tblVideo = new DB_Haodu_CourseVideo();
$video = $tblVideo->scalar("sum(duration) as total_duration", "where id in (" . implode(',', $videoIds) . ")");
return (int) $video['total_duration'];
}
/**
* 用户练习题报告
* 综合排名的计算方式为：
计算所有人的 正确率*0.7+(最慢的答题时间-我的答题时间)/(最慢的答题时间-最快的答题时间)*0.3
并按照计算结果排序得到我的综合排名
*/
public function getUserPractiseReport($uid, $sectionId)
{
$tblUserPractise = new DB_Haodu_UserPractise();
$userPractise = $tblUserPractise->scalar("*", "where uid={$uid} and section_id={$sectionId}");
if (!$userPractise) {
return false;
}
$practiseUserNum = $tblUserPractise->queryCount("where section_id={$sectionId}");
$scoreWinUserNum = $tblUserPractise->queryCount("where correct_num <= {$userPractise['correct_num']} and section_id={$sectionId}");
$spendTimeWinUserNum = $tblUserPractise->queryCount("where spend_time >= {$userPractise['spend_time']} and section_id={$sectionId}");
$tblCoursePractise = new DB_Haodu_CoursePractise();
$sectionPractiseList = $tblCoursePractise->fetchAll("*", "where section_id={$sectionId}");
$sectionPractiseList = $tblCoursePractise->kv($sectionPractiseList, "practise_id", "video_id");
$sectionPractiseNum = count($sectionPractiseList);
$userPractise['practise_num'] = $sectionPractiseNum;
$tblPractiseAnswerHistory = new DB_Haodu_PractiseAnswerHistory();
$userAnswerHistory = $tblPractiseAnswerHistory->fetchAll("*", "where uid={$uid} and section_id={$sectionId}", "order by id asc");
//总的答对数量
$correctTotal = $tblPractiseAnswerHistory->queryCount("where section_id={$sectionId} and option_id!=0 and option_id=answer_id");
//最快最慢的时间
$max = $tblUserPractise->scalar("max(spend_time) as spend_time", "where section_id={$sectionId}");
$min = $tblUserPractise->scalar("min(spend_time) as spend_time", "where section_id={$sectionId}");
$rankByScore = $sectionPractiseNum == 0 ? 0 : $userPractise['correct_num'] / $sectionPractiseNum * 0.7;
$rankBySpendTime = $max['spend_time'] - $userPractise['spend_time'] == 0 || $max['spend_time'] == $min['spend_time'] ? 0 : ($max['spend_time'] - $userPractise['spend_time']) / ($max['spend_time'] - $min['spend_time']) * 0.3;
$rankVal = $rankByScore + $rankBySpendTime;
$where = "where section_id={$sectionId}";
$where .= $sectionPractiseNum == 0 ? "0" : " and correct_num/{$sectionPractiseNum}*0.7";
$where .= $max['spend_time'] - $userPractise['spend_time'] == 0 ? "0" : "+({$max['spend_time']}-spend_time)/({$max['spend_time']}-{$min['spend_time']})*0.3";
$where .= ">{$rankVal}";
$rank = $tblUserPractise->queryCount($where);
$rank = $rankByScore == 0 ? $practiseUserNum : $rank + 1;
$userPractise['create_time_fmt'] = date("Y-m-d H:i:s", $userPractise['create_time']);
//$userPractise['correct_rate'] = $practiseUserNum == 0 ? 0 : "{$userPractise['correct_num']}/{$practiseUserNum}";
$userPractise['correct_rate'] = "{$userPractise['correct_num']}/{$sectionPractiseNum}";
$userPractise['spend_time_fmt'] = sprintf("%02d", floor($userPractise['spend_time'] / 60)) . ":" . sprintf("%02d", $userPractise['spend_time'] % 60);
$userPractise['score_win_rate'] = round($scoreWinUserNum / $practiseUserNum, 2) * 100 . "%";
$userPractise['spendtime_win_rate'] = round($spendTimeWinUserNum / $practiseUserNum, 2) * 100 . "%";
$userPractise['total_rank'] = "{$rank}/{$practiseUserNum}";
//封号的设置方式为：
//排名百分比按区间向下取整，例如如果有2人，一个排名的区间在[0,0.5]，一个排名的区间在[0.5,1]。那么排名百分比分别取0%和50%
//-排名 0-5% 学魔
//-排名 5%-15% 学神
//-排名 15%-40% 学霸
//-排名 40%-75% 学民
//-排名 75%-90% 菜鸟
//-排名 90%-100% 学沫
foreach (self::$levelList as $k => $level) {
if (100 * ($rank - 1) / $practiseUserNum >= intval($k)) {
$userPractise['rank_level'] = $level;
break;
}
}
$seq = 1;
foreach ($userAnswerHistory as $answer) {
$correct = $answer['option_id'] && $answer['answer_id'] == $answer['option_id'] ? 1 : 0;
$letter = chr(320 + $answer['answer_id']);
$userPractise['answer_list'][] = array("id" => $answer["practise_id"], "seq" => $seq, "letter_seq" => $letter, "correct" => $correct, "video_id" => isset($sectionPractiseList[$answer["practise_id"]]['video_id']) ? $sectionPractiseList[$answer["practise_id"]]['video_id'] : 0);
$seq++;
}
return $userPractise;
}
