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; }