def doGet(request): to_render = {} video = request.GET.get('video', False) with connections['ResultDB'].cursor() as cursor: cursor.execute( "SELECT video_time FROM course_material_data WHERE video = %s", [video]) result = namedtuplefetchall(cursor) videoLong = int(result[0].video_time) - 1 viewTimes = [0 for i in range(videoLong + 1)] startTimes = [0 for i in range(videoLong + 1)] endTimes = [0 for i in range(videoLong + 1)] cursor.execute( "SELECT start_video_time,end_video_time " "FROM video_watch_activity " "WHERE module_id = %s and event = 'play' and end_video_time > 0", [video]) result = namedtuplefetchall(cursor) for rs in result: startTime = int(rs.start_video_time) endTime = int(rs.end_video_time) if startTime > videoLong: startTime = videoLong if endTime > videoLong: endTime = videoLong if startTime > endTime: startTime = 0 startTimes[startTime] = startTimes[startTime] + 1 endTimes[endTime] = endTimes[endTime] + 1 for i in range(startTime, endTime + 1): viewTimes[i] = viewTimes[i] + 1 jsonObject = {} json_viewRecord = [] for i in range(len(viewTimes)): jsonObject.clear() jsonObject['int'] = i jsonObject['value'] = viewTimes[i] jsonObject['start'] = startTimes[i] jsonObject['end'] = endTimes[i] json_viewRecord.append(jsonObject.copy()) to_render['viewRecord'] = json.dumps(json_viewRecord) to_render['haveData'] = 1 return to_render
def getUserIDByEmail(email): sql = 'SELECT id FROM edxapp.auth_user WHERE email = %s' cursor = get_connection() try: cursor.execute(sql, email) result = namedtuplefetchall(cursor) userid = result[-1].id return userid except Exception: print('Error') finally: cursor.close() return None
def getUserEmailByUserName(username): sql = 'SELECT email FROM edxapp.auth_user WHERE username = %s' cursor = get_connection() try: cursor.execute(sql, username) result = namedtuplefetchall(cursor) email = result[-1].enail return email except DatabaseError: print('Error') finally: cursor.close() return None
def get_Teacher_Courses(userid): connection = None sql = 'SELECT course_id FROM edxapp.student_courseaccessrole WHERE role = "instructor" AND user_id = %s' cursor = get_connection() try: cursor = connection.cursor() cursor.execute(sql, userid) result = namedtuplefetchall(cursor) return result except Exception: print('Error') finally: cursor.close() return None
def isTeacher(userid): sql = 'SELECT id FROM edxapp.student_courseaccessrole WHERE role = "instructor" AND user_id = %s' cursor = get_connection() try: cursor.execute(sql, userid) result = namedtuplefetchall(cursor) if result is not None: isteacher = True return isteacher except DatabaseError: print('Error') finally: cursor.close() return None
def test1_view(request): to_render = {} with connections['ResultDB'].cursor() as cursor: # 搜尋所有修同一課程之學生 course_id = 'course-v1:FCUx+2015015+201512' cursor.execute( "SELECT distinct(user_id) as uid FROM edxresult.student_total_data WHERE course_id = %s", [course_id]) result = namedtuplefetchall(cursor) n = 0 for rs in result: to_render['ttt'] = type(result) print(type(result)) return render(request, 'test1.html', to_render)
def certificate_view(request): request.encoding = 'utf-8' request.GET = request.GET.copy() request.POST = request.POST.copy() to_render = {} if request.method == 'GET': jsonObject = {} jsonArray_temp = [] avg_watch = [0 for i in range(5)] avg_realWatch = [0 for i in range(5)] with connections['ResultDB'].cursor() as cursor: # 顯示資料 cursor.execute( "SELECT max(統計日期) as max_update FROM course_total_data_v2") result = namedtuplefetchall(cursor) maxUpdate = None if len(result) != 0: maxUpdate = result[-1].max_update cursor.execute( "SELECT max(run_date) as max_run_date FROM student_total_data0912" ) result = namedtuplefetchall(cursor) maxRunDate = None if len(result) != 0: maxRunDate = result[-1].max_run_date cursor.execute( "SELECT sum(證書人數) as sum_pass " "FROM course_total_data_v2 WHERE" " 統計日期 = %s", [maxUpdate]) result = namedtuplefetchall(cursor) sumPassPeople = 0 if len(result) != 0: sumPassPeople = result[-1].sum_pass cursor.execute( "SELECT 證書人數,course_name " "FROM course_total_data_v2 " "WHERE 統計日期 = %s order by 證書人數 desc limit 0, 5", [maxUpdate]) result = namedtuplefetchall(cursor) for rs in result: course_name = rs.course_name certificate = rs.證書人數 jsonObject.clear() jsonObject['courseName'] = course_name jsonObject['value'] = certificate jsonArray_temp.append(jsonObject.copy()) cursor.execute( "SELECT 證書人數,course_name,課程代碼 " "FROM course_total_data_v2 " "WHERE 證書人數>0 and 統計日期 = %s order by 證書人數 desc", [maxUpdate]) result = namedtuplefetchall(cursor) jsonArray_temp_1 = [] jsonArray_table = [] for rs in result: course_name = rs.course_name course_id = rs.課程代碼 certificate = rs.證書人數 jsonArray_temp_1.clear() jsonArray_temp_1.append(course_name) jsonArray_temp_1.append("ChartDataServlet?mode=2&course=" + course_id) jsonArray_temp_1.append(certificate) jsonArray_table.append(jsonArray_temp_1[:]) # 平均開課週數 cursor.execute( "SELECT user_id, count(c_table.course_id) as count_cid, name " "FROM student_total_data0912 as c_table inner join course_total_data_v2 as s_table on (c_table.course_id=s_table.course_id) " "WHERE 統計日期= %s and run_date= %s group by user_id ,name order by count(c_table.course_id) desc limit 0,100", [maxUpdate, maxRunDate]) result = namedtuplefetchall(cursor) jsonArray_course = [] user_id = '' for rs in result: # 判斷是否該名字是中文,如果是的話以'*'字號來消音保護隱私 if len(rs.name.encode()) != len(rs.name): name = rs.name[0:1] + '*' + rs.name[2:] else: name = rs.name countCourse = rs.count_cid user_id = str(rs.user_id) jsonArray_temp_1.clear() jsonArray_temp_1.append(name) jsonArray_temp_1.append("studentInfoServlet?userId=" + user_id) jsonArray_temp_1.append(countCourse) jsonArray_course.append(jsonArray_temp_1[:]) cursor.execute( "SELECT count(user_id) as count_pass,user_id,name " "FROM edxresult.student_total_data0912 " "WHERE certificate=1 group by user_id,name order by count(user_id) desc limit 0,100" ) result = namedtuplefetchall(cursor) jsonArray_pass = [] for rs in result: # 判斷是否該名字是中文,如果是的話以'*'字號來消音保護隱私 if len(rs.name.encode()) != len(rs.name): name = rs.name[0:1] + '*' + rs.name[2:] else: name = rs.name user_id = str(rs.user_id) passCount = rs.count_pass jsonArray_temp_1.clear() jsonArray_temp_1.append(name) jsonArray_temp_1.append("studentInfoServlet?userId=" + user_id) jsonArray_temp_1.append(passCount) jsonArray_pass.append(jsonArray_temp_1[:]) now = datetime.now() time_start = (now + relativedelta(years=-3)) time_end = (now + relativedelta(years=-2)) now = (now + relativedelta(years=-3)) count = 0 recentYearDuration = [0 for i in range(5)] count_watch = [0 for i in range(5)] while count < 5: Standard_Deviation = 0 countMember = 0 cursor.execute( "SELECT sum(證書人數) as sum_c " "FROM edxresult.course_total_data_v2 " "WHERE start_date < %s and start_date > %s and 統計日期 = %s", [ time_end.strftime('%Y-01-01'), time_start.strftime('%Y-01-01'), maxUpdate ]) result = namedtuplefetchall(cursor) if len(result) != 0: recentYearDuration[count] = now.year count_watch[count] = result[-1].sum_c count = count + 1 now = now + relativedelta(years=1) time_start = time_start + relativedelta(years=1) time_end = time_end + relativedelta(years=1) to_render['sumPassPeople'] = sumPassPeople to_render['jsonArray_temp'] = json.dumps(jsonArray_temp) to_render['jsonArray_table'] = json.dumps(jsonArray_table) to_render['recentYearDuration'] = recentYearDuration to_render['count_watch'] = count_watch to_render['avg_watch'] = avg_watch to_render['avg_realWatch'] = avg_realWatch to_render['user_id'] = user_id to_render['jsonArray_pass'] = json.dumps(jsonArray_pass) to_render['jsonArray_course'] = json.dumps(jsonArray_course) return render(request, 'certificate.html', to_render)
def doGet(request): mode = request.GET.get('mode', False) course = request.GET.get('course', False) jsonArray_temp = [] jsonArray_RegisteredPersons = [] jsonArray_temp_1 = [] jsonArray_active = [] jsonArray_temp_2 = [] jsonArray_answerRatio = [] to_render = {} islogin = cookiegetter.isLogined(request) haveThisCourse = False if islogin: userEmail = cookiegetter.getEmail(request) userID = cookiegetter.getUserIDByEmail(userEmail) if cookiegetter.isTeacher(userID): teachersCourse = cookiegetter.get_Teacher_Courses(userID) for j in range(len(teachersCourse)): if getCourseIDByCourseCode(course) == teachersCourse[j]: # 由於course_total_data裡面的欄位無法執行,於是以下的資料全改成從course_total_data_v2取出 with connections['ResultDB'].cursor() as cursor: cursor.execute( "select * from course_total_data_v2 where 課程代碼 = %s", [course]) result = namedtuplefetchall(cursor) count = 0 courseCode = '' courseId = '' courseName = '' totalRegisteredPersons = '' age_17 = '' age_18_25 = '' age_26_ = '' withDrew = '' for rs in result: courseCode = str(rs.課程代碼) courseId = str( rs.course_id) # 在course_total_data裡為 'id' courseName = str( rs.course_name ) # 在這之下所有欄位名稱內有'_'的項目,在course_total_data裡全是'.' totalRegisteredPersons = str(rs.註冊人數) age_17 = str(rs.age_17) age_18_25 = str(rs.age_18_25) age_26_ = str(rs.age_26_) withDrew = str(rs.退選人數) count += 1 cursor.execute( "SELECT count(*) as dataCount " "FROM course_total_data_v2 " "WHERE 課程代碼 = %s and 統計日期>=start_date and end_date>=統計日期 order by 統計日期 asc", [course]) result = namedtuplefetchall(cursor) count = result[-1].dataCount RegisteredPersons = [0 for i in range(count)] updateDate = ['' for i in range(count)] cursor.execute( "select 註冊人數 as RegisteredPersons,練習題作答率_活躍 as ActiveAnswerRatio,統計日期 as updateDate,活躍 as active,非活躍 as nonActive,練習題作答率 as answerRatio " "from course_total_data_v2 " "where 課程代碼 = %s and 統計日期>=start_date and end_date>=統計日期 order by 統計日期 asc", [course]) result = namedtuplefetchall(cursor) jsonArray_temp.clear() jsonArray_temp.append('日期') jsonArray_temp.append('註冊人數') jsonArray_temp_1.clear() jsonArray_temp_1.append('日期') jsonArray_temp_1.append('活躍人數') jsonArray_temp_1.append('非活躍人數') jsonArray_temp_2.append('日期') jsonArray_temp_2.append('練習題作答率') jsonArray_temp_2.append('練習題作答率_活躍') jsonArray_RegisteredPersons.append( jsonArray_temp.copy()) jsonArray_active.append(jsonArray_temp_1.copy()) jsonArray_answerRatio.append(jsonArray_temp_2.copy()) i = 0 for rs in result: RegisteredPersons[i] = int(rs.RegisteredPersons) answerRatio = float(rs.answerRatio) ActiveAnswerRatio = float(rs.ActiveAnswerRatio) updateDate[i] = str(rs.updateDate) active = int(rs.active) nonActive = int(rs.nonActive) jsonArray_temp.clear() jsonArray_temp.append(updateDate[i]) jsonArray_temp.append(RegisteredPersons[i]) jsonArray_RegisteredPersons.append( jsonArray_temp.copy()) jsonArray_temp_1.clear() jsonArray_temp_1.append(updateDate[i]) jsonArray_temp_1.append(active) jsonArray_temp_1.append(nonActive) jsonArray_active.append(jsonArray_temp_1.copy()) jsonArray_temp_2.clear() jsonArray_temp_2.append(updateDate[i]) jsonArray_temp_2.append(answerRatio) jsonArray_temp_2.append(ActiveAnswerRatio) jsonArray_answerRatio.append( jsonArray_temp_2.copy()) i = i + 1 weekChageRegisteredPersons = RegisteredPersons[ count - 1] - RegisteredPersons[count - 2] registeredPersons_Taiwan = None registeredPersons_Foreign = None education_master = None education_Bachelo = None education_Associate = None education_senior = None education_junior = None education_primary = None male = None female = None to_render['courseCode'] = courseCode to_render['courseId'] = courseId to_render['courseName'] = courseName to_render[ 'registeredPersons_Taiwan'] = registeredPersons_Taiwan to_render[ 'registeredPersons_Foreign'] = registeredPersons_Foreign to_render['age_17'] = age_17 to_render['age_18_25'] = age_18_25 to_render['age_26_'] = age_26_ to_render['RegisteredPersons'] = RegisteredPersons to_render['updateDate'] = updateDate to_render['jsonArray_RegisteredPersons'] = json.dumps( jsonArray_RegisteredPersons) to_render['jsonArray_active'] = json.dumps( jsonArray_active) to_render['jsonArray_answerRatio'] = json.dumps( jsonArray_answerRatio) to_render[ 'weekChageRegisteredPersons'] = weekChageRegisteredPersons to_render['withDrew'] = withDrew to_render['education_master'] = education_master to_render['education_Bachelo'] = education_Bachelo to_render['education_Associate'] = education_Associate to_render['education_senior'] = education_senior to_render['education_junior'] = education_junior to_render['education_primary'] = education_primary to_render[ 'totalRegisteredPersons'] = totalRegisteredPersons to_render['mode'] = mode to_render['male'] = male to_render['female'] = female with connections['SurveyDB'].cursor() as cursor: checkBefore = False checkAfter = False to_render['IsLogin'] = 1 print('teacher') haveThisCourse = True break if haveThisCourse is False: print('not have') to_render['IsLogin'] = 2 else: print('student') to_render['IsLogin'] = 2 else: print('not login') to_render['IsLogin'] = 2 return to_render
def doGet(request): to_render = {} with connections['ResultDB'].cursor() as cursor: cursor.execute("SELECT max(統計日期) as date FROM course_total_data_v2") result = namedtuplefetchall(cursor) update_course_total_data = result[-1].date to_render['update_course_total_data'] = update_course_total_data # 取得所有課程資料 cursor.execute("SELECT * FROM course_total_data_v2 WHERE 統計日期 = %s", [update_course_total_data]) result = namedtuplefetchall(cursor) c1 = [] c2 = [] c3_v = [] c3_q = [] c3_f = [] c3 = [] registeredPersons = [] courseNumberOfVideos = [] numberOfTestQuestions = [] numbersOfPostInForum = [] courseLoginCount = [] timesOfVideosViewed =[] totalTestAnswerNumber = [] numberOfForumAreas = [] Participation = [] for rs in result: registeredPersons.append(rs.註冊人數) courseNumberOfVideos.append(rs.課程影片數目) numberOfTestQuestions.append(rs.測驗題數量) numbersOfPostInForum.append(rs.討論區發文數) courseLoginCount.append(rs.login_count) timesOfVideosViewed.append(rs.影片觀看人次_台灣 + rs.影片觀看人次_非台灣) totalTestAnswerNumber.append(rs.總作答數) numberOfForumAreas.append(rs.討論區回應數) c1.append(rs.c1) c2.append(rs.c2) c3_v.append(rs.c_v) c3_q.append(rs.c_q) c3_f.append(rs.c_f) c3.append(rs.c3) Participation.append(rs.熱門參與度) # 取得未標準化資料的區間 interval_value_registeredPersons = getInterval(registeredPersons) interval_value_courseNumberOfVideos = getInterval(courseNumberOfVideos) interval_value_numberOfTestQuestions = getInterval(numberOfTestQuestions) interval_value_numbersOfPostInForum = getInterval(numbersOfPostInForum) interval_value_courseLoginCount = getInterval(courseLoginCount) interval_value_timesOfVideosViewed = getInterval(timesOfVideosViewed) interval_value_totalTestAnswerNumber = getInterval(totalTestAnswerNumber) interval_value_numberOfForumAreas = getInterval(numberOfForumAreas) # 將未標準化數據以區間統計 length = 10 interval_registeredPersons = interval(registeredPersons, interval_value_registeredPersons, length) interval_courseNumberOfVideos = interval(courseNumberOfVideos, interval_value_courseNumberOfVideos, length) interval_numberOfTestQuestions = interval(numberOfTestQuestions, interval_value_numberOfTestQuestions, length) interval_numbersOfPostInForum = interval(numbersOfPostInForum, interval_value_numbersOfPostInForum, length) interval_courseLoginCount = interval(courseLoginCount, interval_value_courseLoginCount, length) interval_timesOfVideosViewed = interval(timesOfVideosViewed, interval_value_timesOfVideosViewed, length) interval_totalTestAnswerNumber = interval(totalTestAnswerNumber, interval_value_totalTestAnswerNumber, length) interval_numberOfForumAreas = interval(numberOfForumAreas, interval_value_numberOfForumAreas, length) interval_c1 = interval(c1, 10, length) interval_c2 = interval(c2, 10, length) interval_c3_v = interval(c3_v, 10, length) interval_c3_q = interval(c3_q, 10, length) interval_c3_f = interval(c3_f, 10, length) interval_c3 = interval(c3, 10, length) interval_Participation = interval(Participation, 10, length) # 將未標準化數據區間轉為Json jsonObject = {'category': 0} Json_registeredPersons = [jsonObject.copy()] Json_courseNumberOfVideos = [jsonObject.copy()] Json_numberOfTestQuestions = [jsonObject.copy()] Json_numbersOfPostInForum = [jsonObject.copy()] Json_courseLoginCount = [jsonObject.copy()] Json_timesOfVideosViewed = [jsonObject.copy()] Json_totalTestAnswerNumber = [jsonObject.copy()] Json_numberOfForumAreas = [jsonObject.copy()] Json_c1 = [jsonObject.copy()] Json_c2 = [jsonObject.copy()] Json_c3_v = [jsonObject.copy()] Json_c3_q = [jsonObject.copy()] Json_c3_f = [jsonObject.copy()] Json_c3 = [jsonObject.copy()] Json_Participation = [jsonObject.copy()] for i in range(10): jsonObject.clear() jsonObject['category'] = str(interval_value_registeredPersons*i) + '~' + str(interval_value_registeredPersons*(i+1)-1) jsonObject['count'] = interval_registeredPersons[i] Json_registeredPersons.append(jsonObject.copy()) jsonObject.clear() jsonObject['category'] = str(interval_value_courseNumberOfVideos*i) + '~' + str(interval_value_courseNumberOfVideos*(i+1)-1) jsonObject['count'] = interval_courseNumberOfVideos[i] Json_courseNumberOfVideos.append(jsonObject.copy()) jsonObject.clear() jsonObject['category'] = str(interval_value_numberOfTestQuestions*i) + '~' + str(interval_value_numberOfTestQuestions*(i+1)-1) jsonObject['count'] = interval_numberOfTestQuestions[i] Json_numberOfTestQuestions.append(jsonObject.copy()) jsonObject.clear() jsonObject['category'] = str(interval_value_numbersOfPostInForum*i) + '~' + str(interval_value_numbersOfPostInForum*(i+1)-1) jsonObject['count'] = interval_numbersOfPostInForum[i] Json_numbersOfPostInForum.append(jsonObject.copy()) jsonObject.clear() jsonObject['category'] = str(interval_value_courseLoginCount*i) + '~' + str(interval_value_courseLoginCount*(i+1)-1) jsonObject['count'] = interval_courseLoginCount[i] Json_courseLoginCount.append(jsonObject.copy()) jsonObject.clear() jsonObject['category'] = str(interval_value_timesOfVideosViewed*i) + '~' + str(interval_value_timesOfVideosViewed*(i+1)-1) jsonObject['count'] = interval_timesOfVideosViewed[i] Json_timesOfVideosViewed.append(jsonObject.copy()) jsonObject.clear() jsonObject['category'] = str(interval_value_totalTestAnswerNumber*i) + '~' + str(interval_value_totalTestAnswerNumber*(i+1)-1) jsonObject['count'] = interval_totalTestAnswerNumber[i] Json_totalTestAnswerNumber.append(jsonObject.copy()) jsonObject.clear() jsonObject['category'] = str(interval_value_numberOfForumAreas) + '~' + str(interval_value_numberOfForumAreas*(i+1)-1) jsonObject['count'] = interval_numberOfForumAreas[i] Json_numberOfForumAreas.append(jsonObject.copy()) jsonObject.clear() jsonObject['category'] = str(5*i) + '~' + str(5*(i+1)-1) jsonObject['count'] = interval_c1[i] Json_c1.append(jsonObject.copy()) jsonObject.clear() jsonObject['category'] = str(5*i) + '~' + str(5*(i+1)-1) jsonObject['count'] = interval_c2[i] Json_c2.append(jsonObject.copy()) jsonObject.clear() jsonObject['category'] = str(5*i) + '~' + str(5*(i+1)-1) jsonObject['count'] = interval_c3_v[i] Json_c3_v.append(jsonObject.copy()) jsonObject.clear() jsonObject['category'] = str(5*i) + '~' + str(5*(i+1)-1) jsonObject['count'] = interval_c3_q[i] Json_c3_q.append(jsonObject.copy()) jsonObject.clear() jsonObject['category'] = str(5*i) + '~' + str(5*(i+1)-1) jsonObject['count'] = interval_c3_f[i] Json_c3_f.append(jsonObject.copy()) jsonObject.clear() jsonObject['category'] = str(5*i) + '~' + str(5*(i+1)-1) jsonObject['count'] = interval_c3[i] Json_c3.append(jsonObject.copy()) jsonObject.clear() jsonObject['category'] = str(5*i) + '~' + str(5*(i+1)-1) jsonObject['count'] = interval_Participation[i] Json_Participation.append(jsonObject.copy()) jsonObject.clear() jsonObject['category'] = 'unlimit' Json_registeredPersons.append(jsonObject.copy()) Json_courseNumberOfVideos.append(jsonObject.copy()) Json_numberOfTestQuestions.append(jsonObject.copy()) Json_numbersOfPostInForum.append(jsonObject.copy()) Json_courseLoginCount.append(jsonObject.copy()) Json_timesOfVideosViewed.append(jsonObject.copy()) Json_totalTestAnswerNumber.append(jsonObject.copy()) Json_numberOfForumAreas.append(jsonObject.copy()) Json_c1.append(jsonObject.copy()) Json_c2.append(jsonObject.copy()) Json_c3_v.append(jsonObject.copy()) Json_c3_q.append(jsonObject.copy()) Json_c3_f.append(jsonObject.copy()) Json_c3.append(jsonObject.copy()) Json_Participation.append(jsonObject.copy()) to_render['Json_registeredPersons'] = json.dumps(Json_registeredPersons) to_render['Json_courseNumberOfVideos'] = json.dumps(Json_courseNumberOfVideos) to_render['Json_numberOfTestQuestions'] = json.dumps(Json_numberOfTestQuestions) to_render['Json_numbersOfPostInForum'] = json.dumps(Json_numbersOfPostInForum) to_render['Json_courseLoginCount'] = json.dumps(Json_courseLoginCount) to_render['Json_timesOfVideosViewed'] = json.dumps(Json_timesOfVideosViewed) to_render['Json_totalTestAnswerNumber'] = json.dumps(Json_totalTestAnswerNumber) to_render['Json_numberOfForumAreas'] = json.dumps(Json_numberOfForumAreas) to_render['Json_c1'] = json.dumps(Json_c1) to_render['Json_c2'] = json.dumps(Json_c2) to_render['Json_c3_v'] = json.dumps(Json_c3_v) to_render['Json_c3_q'] = json.dumps(Json_c3_q) to_render['Json_c3_f'] = json.dumps(Json_c3_f) to_render['Json_c3'] = json.dumps(Json_c3) to_render['Json_Participation'] = json.dumps(Json_Participation) # 將數據標準化 registeredPersons = standardization(registeredPersons) courseNumberOfVideos = standardization(courseNumberOfVideos) numberOfTestQuestions = standardization(numberOfTestQuestions) numbersOfPostInForum = standardization(numbersOfPostInForum) courseLoginCount = standardization(courseLoginCount) timesOfVideosViewed = standardization(timesOfVideosViewed) totalTestAnswerNumber = standardization(totalTestAnswerNumber) numberOfForumAreas = standardization(numberOfForumAreas) c1 = standardization(c1) c2 = standardization(c2) c3_v = standardization(c3_v) c3_q = standardization(c3_q) c3_f = standardization(c3_f) c3 = standardization(c3) Participation = standardization(Participation) registeredPersons.pop() registeredPersons.pop() courseNumberOfVideos.pop() courseNumberOfVideos.pop() numberOfTestQuestions.pop() numberOfTestQuestions.pop() numbersOfPostInForum.pop() numbersOfPostInForum.pop() courseLoginCount.pop() courseLoginCount.pop() timesOfVideosViewed.pop() timesOfVideosViewed.pop() totalTestAnswerNumber.pop() totalTestAnswerNumber.pop() numberOfForumAreas.pop() numberOfForumAreas.pop() c1.pop() c1.pop() c2.pop() c2.pop() c3_v.pop() c3_v.pop() c3_q.pop() c3_q.pop() c3_f.pop() c3_f.pop() c3.pop() c3.pop() Participation.pop() Participation.pop() registeredPersons = standardization(registeredPersons) courseNumberOfVideos = standardization(courseNumberOfVideos) numberOfTestQuestions = standardization(numberOfTestQuestions) numbersOfPostInForum = standardization(numbersOfPostInForum) courseLoginCount = standardization(courseLoginCount) timesOfVideosViewed = standardization(timesOfVideosViewed) totalTestAnswerNumber = standardization(totalTestAnswerNumber) numberOfForumAreas = standardization(numberOfForumAreas) c1 = standardization(c1) c2 = standardization(c2) c3_v = standardization(c3_v) c3_q = standardization(c3_q) c3_f = standardization(c3_f) c3 = standardization(c3) Participation= standardization(Participation) # 取得標準差 sdd_registeredPersons = registeredPersons[-1] registeredPersons.pop() sdd_courseNumberOfVideos = courseNumberOfVideos[-1] courseNumberOfVideos.pop() sdd_numberOfTestQuestions = numberOfTestQuestions[-1] numberOfTestQuestions.pop() sdd_numbersOfPostInForum = numbersOfPostInForum[-1] numbersOfPostInForum.pop() sdd_courseLoginCount = courseLoginCount[-1] courseLoginCount.pop() sdd_timesOfVideosViewed = timesOfVideosViewed[-1] timesOfVideosViewed.pop() sdd_totalTestAnswerNumber = totalTestAnswerNumber[-1] totalTestAnswerNumber.pop() sdd_numberOfForumAreas = numberOfForumAreas[-1] numberOfForumAreas.pop() sdd_c1 = c1[-1] c1.pop() sdd_c2 = c2[-1] c2.pop() sdd_c3_v = c3_v[-1] c3_v.pop() sdd_c3_q = c3_q[-1] c3_q.pop() sdd_c3_f = c3_f[-1] c3_f.pop() sdd_c3 = c3[-1] c3.pop() sdd_Participation = Participation[-1] Participation.pop() # 取得平均 avg_registeredPersons = registeredPersons[-1] registeredPersons.pop() avg_courseNumberOfVideos = courseNumberOfVideos[-1] courseNumberOfVideos.pop() avg_numberOfTestQuestions = numberOfTestQuestions[-1] numberOfTestQuestions.pop() avg_numbersOfPostInForum = numbersOfPostInForum[-1] numbersOfPostInForum.pop() avg_courseLoginCount = courseLoginCount[-1] courseLoginCount.pop() avg_timesOfVideosViewed = timesOfVideosViewed[-1] timesOfVideosViewed.pop() avg_totalTestAnswerNumber = totalTestAnswerNumber[-1] totalTestAnswerNumber.pop() avg_numberOfForumAreas = numberOfForumAreas[-1] numberOfForumAreas.pop() avg_c1 = c1[-1] c1.pop() avg_c2 = c2[-1] c2.pop() avg_c3_v = c3_v[-1] c3_v.pop() avg_c3_q = c3_q[-1] c3_q.pop() avg_c3_f = c3_f[-1] c3_f.pop() avg_c3 = c3[-1] c3.pop() avg_Participation = Participation[-1] Participation.pop() # 將標準化後數據以標準差為區間統計 sd_interval_registeredPersons = standardization_interval(registeredPersons, sdd_registeredPersons, avg_registeredPersons) sd_interval_courseNumberOfVideos = standardization_interval(courseNumberOfVideos, sdd_courseNumberOfVideos, avg_courseNumberOfVideos) sd_interval_numberOfTestQuestions = standardization_interval(numberOfTestQuestions, sdd_numberOfTestQuestions, avg_numberOfTestQuestions) sd_interval_numbersOfPostInForum = standardization_interval(numbersOfPostInForum, sdd_numbersOfPostInForum, avg_numbersOfPostInForum) sd_interval_courseLoginCount = standardization_interval(courseLoginCount, sdd_courseLoginCount, avg_courseLoginCount) sd_interval_timesOfVideosViewed = standardization_interval(timesOfVideosViewed, sdd_timesOfVideosViewed, avg_timesOfVideosViewed) sd_interval_totalTestAnswerNumber = standardization_interval(totalTestAnswerNumber, sdd_totalTestAnswerNumber, avg_totalTestAnswerNumber) sd_interval_numberOfForumAreas = standardization_interval(numberOfForumAreas, sdd_numberOfForumAreas, avg_numberOfForumAreas) sd_interval_c1 = standardization_interval(c1, sdd_c1, avg_c1) sd_interval_c2 = standardization_interval(c2, sdd_c2, avg_c2) sd_interval_c3_v = standardization_interval(c3_v, sdd_c3_v, avg_c3_v) sd_interval_c3_q = standardization_interval(c3_q, sdd_c3_q, avg_c3_q) sd_interval_c3_f = standardization_interval(c3_f, sdd_c3_f, avg_c3_f) sd_interval_c3 = standardization_interval(c3, sdd_c3, avg_c3) sd_interval_Participation = standardization_interval(Participation, sdd_Participation, avg_Participation) # 將標準化後數據區間轉為Json jsonObject = {'category': 0} Json_sd_registeredPersons = [jsonObject.copy()] Json_sd_courseNumberOfVideos = [jsonObject.copy()] Json_sd_numberOfTestQuestions = [jsonObject.copy()] Json_sd_numbersOfPostInForum = [jsonObject.copy()] Json_sd_courseLoginCount = [jsonObject.copy()] Json_sd_timesOfVideosViewed = [jsonObject.copy()] Json_sd_totalTestAnswerNumber = [jsonObject.copy()] Json_sd_numberOfForumAreas = [jsonObject.copy()] Json_sd_c1 = [jsonObject.copy()] Json_sd_c2 = [jsonObject.copy()] Json_sd_c3_v = [jsonObject.copy()] Json_sd_c3_q = [jsonObject.copy()] Json_sd_c3_f = [jsonObject.copy()] Json_sd_c3 = [jsonObject.copy()] Json_sd_Participation = [jsonObject.copy()] for i in range(12): jsonObject.clear() jsonObject['category'] = magic(avg_registeredPersons, i) jsonObject['count'] = sd_interval_registeredPersons[i] Json_sd_registeredPersons.append(jsonObject.copy()) jsonObject.clear() jsonObject['category'] = magic(avg_courseNumberOfVideos, i) jsonObject['count'] = sd_interval_courseNumberOfVideos[i] Json_sd_courseNumberOfVideos.append(jsonObject.copy()) jsonObject.clear() jsonObject['category'] = magic(avg_numberOfTestQuestions, i) jsonObject['count'] = sd_interval_numberOfTestQuestions[i] Json_sd_numberOfTestQuestions.append(jsonObject.copy()) jsonObject.clear() jsonObject['category'] = magic(avg_numbersOfPostInForum, i) jsonObject['count'] = sd_interval_numbersOfPostInForum[i] Json_sd_numbersOfPostInForum.append(jsonObject.copy()) jsonObject.clear() jsonObject['category'] = magic(avg_courseLoginCount, i) jsonObject['count'] = sd_interval_courseLoginCount[i] Json_sd_courseLoginCount.append(jsonObject.copy()) jsonObject.clear() jsonObject['category'] = magic(avg_timesOfVideosViewed, i) jsonObject['count'] = sd_interval_timesOfVideosViewed[i] Json_sd_timesOfVideosViewed.append(jsonObject.copy()) jsonObject.clear() jsonObject['category'] = magic(avg_totalTestAnswerNumber, i) jsonObject['count'] = sd_interval_totalTestAnswerNumber[i] Json_sd_totalTestAnswerNumber.append(jsonObject.copy()) jsonObject.clear() jsonObject['category'] = magic(avg_numberOfForumAreas, i) jsonObject['count'] = sd_interval_numberOfForumAreas[i] Json_sd_numberOfForumAreas.append(jsonObject.copy()) jsonObject.clear() jsonObject['category'] = magic(avg_c1, i) jsonObject['count'] = sd_interval_c1[i] Json_sd_c1.append(jsonObject.copy()) jsonObject.clear() jsonObject['category'] = magic(avg_c2, i) jsonObject['count'] = sd_interval_c2[i] Json_sd_c2.append(jsonObject.copy()) jsonObject.clear() jsonObject['category'] = magic(avg_c3_v, i) jsonObject['count'] = sd_interval_c3_v[i] Json_sd_c3_v.append(jsonObject.copy()) jsonObject.clear() jsonObject['category'] = magic(avg_c3_q, i) jsonObject['count'] = sd_interval_c3_q[i] Json_sd_c3_q.append(jsonObject.copy()) jsonObject.clear() jsonObject['category'] = magic(avg_c3_f, i) jsonObject['count'] = sd_interval_c3_f[i] Json_sd_c3_f.append(jsonObject.copy()) jsonObject.clear() jsonObject['category'] = magic(avg_c3, i) jsonObject['count'] = sd_interval_c3[i] Json_sd_c3.append(jsonObject.copy()) jsonObject.clear() jsonObject['category'] = magic(avg_Participation, i) jsonObject['count'] = sd_interval_Participation[i] Json_sd_Participation.append(jsonObject.copy()) jsonObject.clear() jsonObject['category'] = 'unlimit' Json_sd_registeredPersons.append(jsonObject.copy()) Json_sd_courseNumberOfVideos.append(jsonObject.copy()) Json_sd_numberOfTestQuestions.append(jsonObject.copy()) Json_sd_numbersOfPostInForum.append(jsonObject.copy()) Json_sd_courseLoginCount.append(jsonObject.copy()) Json_sd_timesOfVideosViewed.append(jsonObject.copy()) Json_sd_totalTestAnswerNumber.append(jsonObject.copy()) Json_sd_numberOfForumAreas.append(jsonObject.copy()) Json_sd_c1.append(jsonObject.copy()) Json_sd_c2.append(jsonObject.copy()) Json_sd_c3_v.append(jsonObject.copy()) Json_sd_c3_q.append(jsonObject.copy()) Json_sd_c3_f.append(jsonObject.copy()) Json_sd_c3.append(jsonObject.copy()) Json_sd_Participation.append(jsonObject.copy()) to_render['Json_sd_registeredPersons'] = json.dumps(Json_sd_registeredPersons) to_render['Json_sd_courseNumberOfVideos'] = json.dumps(Json_sd_courseNumberOfVideos) to_render['Json_sd_numberOfTestQuestions'] = json.dumps(Json_sd_numberOfTestQuestions) to_render['Json_sd_numbersOfPostInForum'] = json.dumps(Json_sd_numbersOfPostInForum) to_render['Json_sd_courseLoginCount'] = json.dumps(Json_sd_courseLoginCount) to_render['Json_sd_timesOfVideosViewed'] = json.dumps(Json_sd_timesOfVideosViewed) to_render['Json_sd_totalTestAnswerNumber'] = json.dumps(Json_sd_totalTestAnswerNumber) to_render['Json_sd_numberOfForumAreas'] = json.dumps(Json_sd_numberOfForumAreas) to_render['Json_sd_c1'] = json.dumps(Json_sd_c1) to_render['Json_sd_c2'] = json.dumps(Json_sd_c2) to_render['Json_sd_c3_v'] = json.dumps(Json_sd_c3_v) to_render['Json_sd_c3_q'] = json.dumps(Json_sd_c3_q) to_render['Json_sd_c3_f'] = json.dumps(Json_sd_c3_f) to_render['Json_sd_c3'] = json.dumps(Json_sd_c3) to_render['Json_sd_Participation'] = json.dumps(Json_sd_Participation) to_render['sdd_registeredPersons'] = '{:,.1f}'.format(sdd_registeredPersons) to_render['sdd_courseNumberOfVideos'] = '{:,.1f}'.format(sdd_courseNumberOfVideos) to_render['sdd_numberOfTestQuestions'] = '{:,.1f}'.format(sdd_numberOfTestQuestions) to_render['sdd_numbersOfPostInForum'] = '{:,.1f}'.format(sdd_numbersOfPostInForum) to_render['sdd_courseLoginCount'] = '{:,.1f}'.format(sdd_courseLoginCount) to_render['sdd_timesOfVideosViewed'] = '{:,.1f}'.format(sdd_timesOfVideosViewed) to_render['sdd_totalTestAnswerNumber'] = '{:,.1f}'.format(sdd_totalTestAnswerNumber) to_render['sdd_numberOfForumAreas'] = '{:,.1f}'.format(sdd_numberOfForumAreas) to_render['sdd_c1'] = '{:,.1f}'.format(sdd_c1) to_render['sdd_c2'] = '{:,.1f}'.format(sdd_c2) to_render['sdd_c3_v'] = '{:,.1f}'.format(sdd_c3_v) to_render['sdd_c3_q'] = '{:,.1f}'.format(sdd_c3_q) to_render['sdd_c3_f'] = '{:,.1f}'.format(sdd_c3_f) to_render['sdd_c3'] = '{:,.1f}'.format(sdd_c3) to_render['sdd_Participation'] = '{:,.1f}'.format(sdd_Participation) to_render['avg_registeredPersons'] = '{:,.1f}'.format(avg_registeredPersons) to_render['avg_courseNumberOfVideos'] = '{:,.1f}'.format(avg_courseNumberOfVideos) to_render['avg_numberOfTestQuestions'] = '{:,.1f}'.format(avg_numberOfTestQuestions) to_render['avg_numbersOfPostInForum'] = '{:,.1f}'.format(avg_numbersOfPostInForum) to_render['avg_courseLoginCount'] = '{:,.1f}'.format(avg_courseLoginCount) to_render['avg_timesOfVideosViewed'] = '{:,.1f}'.format(avg_timesOfVideosViewed) to_render['avg_totalTestAnswerNumber'] = '{:,.1f}'.format(avg_totalTestAnswerNumber) to_render['avg_numberOfForumAreas'] = '{:,.1f}'.format(avg_numberOfForumAreas) to_render['avg_c1'] = '{:,.1f}'.format(avg_c1) to_render['avg_c2'] = '{:,.1f}'.format(avg_c2) to_render['avg_c3_v'] = '{:,.1f}'.format(avg_c3_v) to_render['avg_c3_q'] = '{:,.1f}'.format(avg_c3_q) to_render['avg_c3_f'] = '{:,.1f}'.format(avg_c3_f) to_render['avg_c3'] = '{:,.1f}'.format(avg_c3) to_render['avg_Participation'] = '{:,.1f}'.format(avg_Participation) return to_render
def suggested_by_education(request): to_render = {} with connections['ResultDB'].cursor() as cursor: cursor.execute( "SELECT 課程代碼, course_id, course_name, 博士, 碩士, 學士, 副學士, 高中, 國中, 國小 FROM course_total_data_v2" ) result = namedtuplefetchall(cursor) max_p = max_m = max_b = max_a = max_hs = max_jhs = max_el = 0 totalall = 0 total_p = total_m = total_b = total_a = total_hs = total_jhs = total_el = 0 output_p = ['' for i in range(4)] output_m = ['' for i in range(4)] output_b = ['' for i in range(4)] output_a = ['' for i in range(4)] output_hs = ['' for i in range(4)] output_jhs = ['' for i in range(4)] output_el = ['' for i in range(4)] for rs in result: totalall = rs.博士 + rs.碩士 + rs.學士 + rs.副學士 + rs.高中 + rs.國中 + rs.國小 if max_p < rs.博士: max_p = rs.博士 total_p = totalall output_p[0] = rs.課程代碼 output_p[1] = rs.course_id output_p[2] = rs.course_name output_p[3] = rs.博士 elif max_p == rs.博士: if total_p > totalall: output_p[0] = rs.課程代碼 output_p[1] = rs.course_id output_p[2] = rs.course_name output_p[3] = rs.博士 if max_m < rs.碩士: max_m = rs.碩士 total_m = totalall output_m[0] = rs.課程代碼 output_m[1] = rs.course_id output_m[2] = rs.course_name output_m[3] = rs.碩士 elif max_m == rs.碩士: if total_m > totalall: output_m[0] = rs.課程代碼 output_m[1] = rs.course_id output_m[2] = rs.course_name output_m[3] = rs.碩士 if max_b < rs.學士: max_b = rs.學士 total_b = totalall output_b[0] = rs.課程代碼 output_b[1] = rs.course_id output_b[2] = rs.course_name output_b[3] = rs.學士 elif max_b == rs.學士: if total_b > totalall: output_b[0] = rs.課程代碼 output_b[1] = rs.course_id output_b[2] = rs.course_name output_b[3] = rs.學士 if max_a < rs.副學士: max_a = rs.副學士 total_a = totalall output_a[0] = rs.課程代碼 output_a[1] = rs.course_id output_a[2] = rs.course_name output_a[3] = rs.副學士 elif max_a == rs.副學士: if total_a > totalall: output_a[0] = rs.課程代碼 output_a[1] = rs.course_id output_a[2] = rs.course_name output_a[3] = rs.副學士 if max_hs < rs.高中: max_hs = rs.高中 total_hs = totalall output_hs[0] = rs.課程代碼 output_hs[1] = rs.course_id output_hs[2] = rs.course_name output_hs[3] = rs.高中 elif max_hs == rs.高中: if total_hs > totalall: output_hs[0] = rs.課程代碼 output_hs[1] = rs.course_id output_hs[2] = rs.course_name output_hs[3] = rs.高中 if max_jhs < rs.國中: max_jhs = rs.國中 total_jhs = totalall output_jhs[0] = rs.課程代碼 output_jhs[1] = rs.course_id output_jhs[2] = rs.course_name output_jhs[3] = rs.國中 elif max_jhs == rs.國中: if total_jhs > totalall: output_jhs[0] = rs.課程代碼 output_jhs[1] = rs.course_id output_jhs[2] = rs.course_name output_jhs[3] = rs.國中 if max_el < rs.國小: max_el = rs.國小 total_el = totalall output_el[0] = rs.課程代碼 output_el[1] = rs.course_id output_el[2] = rs.course_name output_el[3] = rs.國小 elif max_el == rs.國小: if total_el > totalall: output_el[0] = rs.課程代碼 output_el[1] = rs.course_id output_el[2] = rs.course_name output_el[3] = rs.國小 to_render['output_p'] = output_p to_render['output_m'] = output_m to_render['output_b'] = output_b to_render['output_a'] = output_a to_render['output_hs'] = output_hs to_render['output_jhs'] = output_jhs to_render['output_el'] = output_el return to_render
def analysis_student_view(request): request.encoding = 'utf-8' if request.method == 'GET': to_render = {} mode = request.GET['mode'] course = request.GET['course'] to_render['course'] = course optradio = request.GET['optradio'] select = int(optradio) count_pass = 0 count_pass1 = 0 count_person = 0 jsonArray_temp = [] jsonArray_temp_grade = [] jsonArray_grade = [] jsonArray_studentInfo = [] avgWatch_el = None avgWatch_jhs = None avgWatch_hs = None avgWatch_a = None avgWatch_b = None avgWatch_m = None avgWatch_p = None with connections['ResultDB'].cursor() as cursor: cursor.execute( "SELECT 課程代碼 as courseCode,course_id as id,course_name as courseName, 課程影片數目 " "FROM course_total_data_v2 " "WHERE 課程代碼 = %s", [course]) result = namedtuplefetchall(cursor) # 顯示資料 count = 0 courseCode = result[-1].courseCode courseId = result[-1].id courseName = result[-1].courseName video = result[-1].課程影片數目 count = count + 1 gradeIsFind = False totalIsFind = False gradeUseTable = ['student_grade'] # 四種不同的table totalDataUseTable = [ 'student_total_data0912', 'student_total_data', 'student_total_data_old', 'student_total_data2' ] # 四種不同的資料庫語法 radio = [ "and active = 1", "and active = 0", "", "and certificate=1" ] table = 0 totalUse = 0 gradeUse = 0 runDate = '' runDate_grade = '' while totalIsFind is False and table < len(totalDataUseTable): sql = "SELECT max(run_date) as run_date " \ "FROM {} " \ "WHERE course_id = '{}' ".format(totalDataUseTable[table], courseId) cursor.execute(sql) result = namedtuplefetchall(cursor) for rs in result: runDate = rs.run_date if runDate is not None: totalIsFind = True totalUse = table table = table + 1 i = 0 table = 0 while gradeIsFind is False and table < len(gradeUseTable): sql = "SELECT max(run_date) as runDate_grade " \ "FROM {} " \ "WHERE course_id= '{}'".format(gradeUseTable[table], courseId) cursor.execute(sql) result = namedtuplefetchall(cursor) for rs in result: runDate_grade = rs.runDate_grade if runDate_grade is not None: gradeIsFind = True gradeUse = table table = table + 1 cursor.execute( "SELECT name,forum_num, complete_rate,c_complete_rate,video_count,watch_count,login_count,last_login, " "level_of_education,total_grade_1st_try_p, total_grade_best_p " "FROM " + totalDataUseTable[totalUse] + "as t_table INNER JOIN " + gradeUseTable[gradeUse] + " as g_table on (t_table.user_id=g_table.user_id) WHERE t_table.course_id='" + courseId + "'and g_table.course_id='" + courseId + "' and t_table.run_date='" + runDate + "' and g_table.run_date='" + runDate_grade + "' " + radio[select]) result = namedtuplefetchall(cursor) # 顯示資料 for rs in result: # 判斷是否該名字是中文,如果是的話以'*'字號來消音保護隱私 if len(rs.name.encode()) != len(rs.name): user_id = rs.name[0:1] + '*' + rs.name[2:] userName = rs.name[0:1] + '*' + rs.name[2:] else: user_id = rs.name userName = rs.name grade_1st_try_p = int(rs.total_grade_1st_try_p * 100) grade_best_p = int(rs.total_grade_best_p * 100) forum_num = rs.forum_num videoCount = rs.video_count login_Count = rs.login_count if rs.last_login is None: last_Login = '******' else: last_Login = rs.last_login if video == 0: percentWatch = 0 else: percentWatch = float(videoCount / video) watchCount = rs.watch_count level_of_education = rs.level_of_education if level_of_education is None: level_of_education = '無資料' elif level_of_education == 'p': level_of_education = '博士' elif level_of_education == 'm': level_of_education = '碩士' elif level_of_education == 'b': level_of_education = '學士' elif level_of_education == 'a': level_of_education = '副學士' elif level_of_education == 'hs': level_of_education = '高中' elif level_of_education == 'jhs': level_of_education = '國中' elif level_of_education == 'el': level_of_education = '國小' else: level_of_education = '無資料' c_completeRate = rs.c_complete_rate if c_completeRate >= 0.5: count_person = count_person + 1 count_pass1 = count_pass1 + 1 if select == 3: count_pass = count_pass + 1 completeRate = rs.complete_rate jsonArray_temp.clear() jsonArray_temp.append(userName) jsonArray_temp.append(level_of_education) jsonArray_temp.append(videoCount) jsonArray_temp.append(percentWatch) jsonArray_temp.append(watchCount) jsonArray_temp.append(forum_num) jsonArray_temp.append(completeRate) jsonArray_temp.append(c_completeRate) jsonArray_temp.append(login_Count) jsonArray_temp.append(last_Login) jsonArray_studentInfo.append(jsonArray_temp.copy()) jsonArray_temp_grade.clear() jsonArray_temp_grade.append(user_id) jsonArray_temp_grade.append(level_of_education) jsonArray_temp_grade.append(grade_1st_try_p) jsonArray_temp_grade.append(grade_best_p) jsonArray_grade.append(jsonArray_temp_grade.copy()) cursor.execute( "select 註冊人數+退選人數 as countPerson from course_total_data_v2 where course_id= %s", [courseId]) result = namedtuplefetchall(cursor) countPerson = result[-1].countPerson if select < 3: noHalfPerson = countPerson - count_person else: count_person = count_pass1 noHalfPerson = count_pass - count_pass1 cursor.execute("SELECT video_count " "FROM " + totalDataUseTable[totalUse] + " WHERE course_id='" + courseId + "' and run_date='" + runDate + "'" + radio[select]) result = namedtuplefetchall(cursor) videoCount = video video_temp = float(video) / 10 # 將學生觀看影片數目分成10等分,數字越少代表觀看越少 video_watch = [0 for i in range(10)] for rs in result: video = rs.video_count if 0 <= video <= video_temp: video_watch[0] = video_watch[0] + 1 elif video_temp < video <= 2 * video_temp: video_watch[1] = video_watch[1] + 1 elif 2 * video_temp < video <= 3 * video_temp: video_watch[2] = video_watch[2] + 1 elif 3 * video_temp < video <= 4 * video_temp: video_watch[3] = video_watch[3] + 1 elif 4 * video_temp < video <= 5 * video_temp: video_watch[4] = video_watch[4] + 1 elif 5 * video_temp < video <= 6 * video_temp: video_watch[5] = video_watch[5] + 1 elif 6 * video_temp < video <= 7 * video_temp: video_watch[6] = video_watch[6] + 1 elif 7 * video_temp < video <= 8 * video_temp: video_watch[7] = video_watch[7] + 1 elif 8 * video_temp < video <= 9 * video_temp: video_watch[8] = video_watch[8] + 1 elif 9 * video_temp < video <= 10 * video_temp: video_watch[9] = video_watch[9] + 1 str_watch_1 = "0-" + str(int(video_temp)) str_watch_2 = str(int(video_temp + 1)) + "-" + str( int(2 * video_temp)) str_watch_3 = str(int(2 * video_temp + 1)) + "-" + str( int(3 * video_temp)) str_watch_4 = str(int(3 * video_temp + 1)) + "-" + str( int(4 * video_temp)) str_watch_5 = str(int(4 * video_temp + 1)) + "-" + str( int(5 * video_temp)) str_watch_6 = str(int(5 * video_temp + 1)) + "-" + str( int(6 * video_temp)) str_watch_7 = str(int(6 * video_temp + 1)) + "-" + str( int(7 * video_temp)) str_watch_8 = str(int(7 * video_temp + 1)) + "-" + str( int(8 * video_temp)) str_watch_9 = str(int(8 * video_temp + 1)) + "-" + str( int(9 * video_temp)) str_watch_10 = str(int(9 * video_temp + 1)) + "-" + str(videoCount) to_render['courseCode'] = courseCode to_render['courseId'] = courseId to_render['courseName'] = courseName to_render['runDate'] = runDate to_render['count_person'] = count_person to_render['countPerson'] = countPerson to_render['noHalfPerson'] = noHalfPerson to_render['jsonArray_studentInfo'] = json.dumps( jsonArray_studentInfo) to_render['avgWatch_el'] = avgWatch_el to_render['avgWatch_jhs'] = avgWatch_jhs to_render['avgWatch_hs'] = avgWatch_hs to_render['avgWatch_a'] = avgWatch_a to_render['avgWatch_b'] = avgWatch_b to_render['avgWatch_m'] = avgWatch_m to_render['avgWatch_p'] = avgWatch_p to_render['jsonArray_grade'] = json.dumps(jsonArray_grade) to_render['video_watch_1'] = video_watch[0] to_render['video_watch_2'] = video_watch[1] to_render['video_watch_3'] = video_watch[2] to_render['video_watch_4'] = video_watch[3] to_render['video_watch_5'] = video_watch[4] to_render['video_watch_6'] = video_watch[5] to_render['video_watch_7'] = video_watch[6] to_render['video_watch_8'] = video_watch[7] to_render['video_watch_9'] = video_watch[8] to_render['video_watch_10'] = video_watch[9] to_render['str_watch_1'] = str_watch_1 to_render['str_watch_2'] = str_watch_2 to_render['str_watch_3'] = str_watch_3 to_render['str_watch_4'] = str_watch_4 to_render['str_watch_5'] = str_watch_5 to_render['str_watch_6'] = str_watch_6 to_render['str_watch_7'] = str_watch_7 to_render['str_watch_8'] = str_watch_8 to_render['str_watch_9'] = str_watch_9 to_render['str_watch_10'] = str_watch_10 to_render['mode'] = mode to_render['optradio'] = optradio to_render['courseCode'] = courseCode return render(request, 'AnalysisStudent.html', to_render)
def before_survey_view(request): request.encoding = 'utf-8' if request.method == 'GET': to_render = {} islogin = cookiegetter.isLogined(request) if islogin is True: userEmail = cookiegetter.getEmail(request) userID = cookiegetter.getUserIDByEmail(userEmail) if cookiegetter.isTeacher(userID): teachersCourse = cookiegetter.get_Teacher_Courses(userID) with connections['SurveyDB'].cursor() as cursor: # 找出所有課程的id(不重複) cursor.execute( "SELECT DISTINCT course_id FROM survey.before_class_survey order by course_id asc" ) result = namedtuplefetchall(cursor) courseNumber = 0 for rs in result: for i in range(len(teachersCourse)): if rs.course_id == teachersCourse[i]: courseNumber += 1 # 宣告陣列長度 data = [['' for i in range(11)] for i in range(courseNumber)] allCourseID = [] for rs in result: for i in range(len(teachersCourse)): if rs.course_id == teachersCourse[i]: allCourseID.append(rs.course_id) count = 0 i = 0 surveyNumber = 0 while i < courseNumber: cursor.execute( "select count(*) as surveyNumber " "from before_class_survey " "where course_id = %s", [allCourseID[i]]) result = namedtuplefetchall(cursor) surveyNumber = result[-1].surveyNumber # 找出所有該課程的問卷資料 cursor.execute( "select course_name,course_id,selection1,selection2,selection3 " "from before_class_survey " "where course_id = %s", [allCourseID[i]]) result = namedtuplefetchall(cursor) data[count][6] = '0' data[count][7] = '0' data[count][8] = '0' data[count][9] = '0' data[count][10] = '0' RegisteredPersonsAndCourseCode = [] for rs in result: RegisteredPersonsAndCourseCode = getRegisteredPersonsAndCourseCodeByCourseID( rs.course_id) data[count][0] = RegisteredPersonsAndCourseCode[1] data[count][3] = RegisteredPersonsAndCourseCode[0] break for rs in result: data[count][1] = rs.course_name data[count][2] = rs.course_id data[count][4] = str(surveyNumber) data[count][5] = '{:.1f}'.format( surveyNumber / int(data[count][3]) * 100) + '%' if rs.selection1 == 0: data[count][6] = str(int(data[count][6]) + 1) elif rs.selection1 == 1: data[count][7] = str(int(data[count][7]) + 1) elif rs.selection1 == 2: data[count][8] = str(int(data[count][8]) + 1) data[count][9] = str( int(data[count][9]) + (int(rs.selection2) - 5) * (-1)) data[count][10] = str( int(data[count][10]) + (int(rs.selection3) - 5) * (-1)) data[count][6] = '{:.1f}'.format( float(data[count][6]) / surveyNumber * 100) + '%' data[count][7] = '{:.1f}'.format( float(data[count][7]) / surveyNumber * 100) + '%' data[count][8] = '{:.1f}'.format( float(data[count][8]) / surveyNumber * 100) + '%' data[count][9] = '{:.1f}'.format( float(data[count][9]) / surveyNumber) data[count][10] = '{:.1f}'.format( float(data[count][10]) / surveyNumber) count = count + 1 i = i + 1 to_render['data'] = data to_render['IsLogin'] = 1 print('teacher') return render(request, 'beforeSurvey.html', to_render) else: to_render['IsLogin'] = 2 print('student') return render(request, 'beforeSurvey.html', to_render) else: to_render['IsLogin'] = 2 print('not login') return render(request, 'beforeSurvey.html', to_render)
def doGET(request): RadarColumnName = ['影片觀看人數', '註冊人數', '討論區討論次數', '練習題答對率', '練習題作答率'] mode = request.GET['mode'] course = request.GET['course'] dict2json = {} jsonArray_RegisteredPersons = [] to_render = {} jsonArray_active = [] jsonArray_answerRatio = [] jsonArray_charter = [] with connections['ResultDB'].cursor() as cursor: # course_total_data_v2 更新日期 cursor.execute( "SELECT max(統計日期) as date FROM course_total_data_v2 where 課程代碼 = %s", [course]) result = namedtuplefetchall(cursor) update_course_total_data = result[-1].date # 取得課程基本資料 cursor.execute( "SELECT 課程代碼,course_id,course_name,start_date,end_date,註冊人數,退選人數 " "FROM course_total_data_v2 " "WHERE 課程代碼 = %s and 統計日期 = %s", [course, update_course_total_data]) result = namedtuplefetchall(cursor) courseCode = result[-1].課程代碼 courseId = result[-1].course_id courseName = result[-1].course_name start_date = result[-1].start_date end_date = result[-1].end_date totalRegisteredPersons = result[-1].註冊人數 withDrew = result[-1].退選人數 # ------------------------------------------------ # update_video_activity 更新日期 cursor.execute( "SELECT max(run_date) as max_date FROM student_total_data0912 WHERE course_id = %s", [courseId]) result = namedtuplefetchall(cursor) update_student_total_data = result[-1].max_date # update_video_activity 更新日期 cursor.execute( "SELECT max(date) as max_date FROM video_activity WHERE course_id= %s", [courseId]) result = namedtuplefetchall(cursor) update_video_activity = result[-1].max_date # update_login_data 更新日期 cursor.execute( "SELECT max(date) as max_date FROM login_date WHERE course_id= %s", [courseId]) result = namedtuplefetchall(cursor) update_login_data = result[-1].max_date # 統計課程的註冊人數變化 cursor.execute( "SELECT count(first_login) as countFirst_login ,first_login " "FROM student_total_data0912 " "WHERE course_id= %s and run_date= %s and first_login<= %s and first_login>= %s" "group by first_login", [courseId, update_student_total_data, end_date, start_date]) result = namedtuplefetchall(cursor) for rs in result: first_login = rs.first_login logincount = rs.countFirst_login dict2json.clear() dict2json['date'] = first_login dict2json['value'] = logincount jsonArray_RegisteredPersons.append(dict2json[:]) # ---------------------------------------------------- '''課程雷達圖''' # 將所有課程的練習題作答率抓出來 all_test = [] cursor.execute( "SELECT 練習題作答率 from course_total_data_v2 where 統計日期 = %s order by 練習題作答率 asc", [update_course_total_data]) result = namedtuplefetchall(cursor) for rs in result: all_test.append(float(rs.練習題作答率)) # 課程總數 totalCourceNumber = len(all_test) # 將所有課程的練習題答對率抓出來 all_testCorrectPercent = [] cursor.execute( "SELECT 練習題答對率 " "FROM course_total_data_v2 " "WHERE 統計日期 = %s order by 練習題答對率 asc", [update_course_total_data]) result = namedtuplefetchall(cursor) for rs in result: all_testCorrectPercent.append(float(rs.練習題答對率)) # 將所有課程的討論區討論次數抓出來 all_numberOfForum = [] cursor.execute( "SELECT 討論區討論次數 " "FROM course_total_data_v2 " "WHERE 統計日期 = %s order by 討論區討論次數 asc", [update_course_total_data]) result = namedtuplefetchall(cursor) for rs in result: all_numberOfForum.append(int(rs.討論區討論次數)) # 將所有課程的註冊人數抓出來 all_registered = [] cursor.execute( "SELECT 註冊人數 " "FROM course_total_data_v2 " "WHERE 統計日期 = %s order by 註冊人數 asc", [update_course_total_data]) result = namedtuplefetchall(cursor) for rs in result: all_registered.append(int(rs.註冊人數)) # 將所有課程的影片觀看人數抓出來 all_watchVideo = [] cursor.execute( "SELECT ('影片觀看人數台灣'+'影片觀看人數_非台灣') as video " "FROM course_total_data_v2 " "WHERE 統計日期 = %s order by video asc", [update_course_total_data]) result = namedtuplefetchall(cursor) for rs in result: all_watchVideo.append(int(rs.video)) # 將原始資料的最大值抓出來 originalMax = [ all_watchVideo[-1], all_registered[-1], all_numberOfForum[-1], all_testCorrectPercent[-1], all_test[-1] ] # 將資料去除極限值 all_watchVideo = removeExtremum(all_watchVideo) all_registered = removeExtremum(all_registered) all_numberOfForum = removeExtremum(all_numberOfForum) all_testCorrectPercent = removeExtremum(all_testCorrectPercent) all_test = removeExtremum(all_test) # 將去除極限值後的最大值抓出來,即為雷達最大值 maxRadar = [ all_watchVideo[-2], all_registered[-2], all_numberOfForum[-2], all_testCorrectPercent[-2], all_test[-2] ] # 取出去除極限值後的最大值在原始資料的落點 maxRadarSite = [ all_watchVideo[-1], all_registered[-1], all_numberOfForum[-1], all_testCorrectPercent[-1], all_test[-1] ] # 取出去除極限值後的資料的平均 avgRadar = [ getListAvg(all_watchVideo), getListAvg(all_registered), getListAvg(all_numberOfForum), getListAvg(all_testCorrectPercent), getListAvg(all_test) ] # 取出該課程的資料 cursor.execute( "SELECT 影片觀看人數台灣+('影片觀看人數_非台灣') as watchVideo,註冊人數 as registered, " "討論區討論次數 as numberOfForum,練習題答對率 as testCorrectPercent,練習題作答率 as test " "FROM course_total_data_v2 " "WHERE 統計日期= %s and 課程代碼= %s", [update_course_total_data, course]) result = namedtuplefetchall(cursor) RadarColumn = [ result[-1].watchVideo, result[-1].registered, result[-1].numberOfForum, result[-1].testCorrectPercent, result[-1].test ] # 計算該課程除以雷達最大值的百分比 avgRadar_percent = [0 for i in range(5)] RadarColumn_percent = [0 for i in range(5)] for i in range(len(avgRadar_percent)): RadarColumn_percent[i] = RadarColumn[i] / maxRadar[i] * 100 avgRadar_percent[i] = avgRadar[i] / maxRadar[i] * 100 if avgRadar_percent[i] > 100: avgRadar_percent[i] = 100 if RadarColumn_percent[i] > 100: RadarColumn_percent[i] = 100 # 將資料整理後放到 Data 準備回傳 RadarData = [] Data = [] for i in range(len(avgRadar_percent)): Data.clear() Data.append(RadarColumnName[i]) Data.append(':,'.format(originalMax[i])) Data.append(':,'.format(maxRadar[i])) Data.append(':,'.format(maxRadarSite[i] / totalCourceNumber * 100) + '%') Data.append(':,'.format(avgRadar[i]) + "(" + ':,'.format(avgRadar_percent[i]) + '%)') Data.append(':,'.format(RadarColumn[i]) + '(' + ':,'.format(RadarColumn_percent[i]) + '%)') RadarData.append(Data[:]) # -----------(課程雷達圖)------------ # 統計課程的登入人數變化 now = datetime.now() time = datetime.now() if end_date != 'NA': time = datetime.strptime(end_date, '%Y-%m-%d') if time > now: time = now.strftime('%Y-%m-%d') cursor.execute( "SELECT date ,course_id,count(user_id) as count_uid " "FROM login_date " "WHERE course_id= %s and date<= %s and date>= %s group by course_id,date", [courseId, time, start_date]) result = namedtuplefetchall(cursor) jsonObject = {} jsonArray_temp = [] for rs in result: login_date = rs.date loginIn = rs.count_uid jsonObject.clear() jsonObject['date'] = login_date jsonObject['value'] = loginIn jsonArray_temp.append(jsonObject) else: cursor.execute( "SELECT date ,course_id,count(user_id) as count_uid " "FROM login_date " "WHERE course_id= %s and date<= %s and date>= %s group by course_id,date", [courseId, now.strftime('%Y-%m-%d'), start_date]) result = namedtuplefetchall(cursor) jsonObject = {} jsonArray_temp = [] for rs in result: login_date = rs.date loginIn = rs.count_uid jsonObject.clear() jsonObject['date'] = login_date jsonObject['value'] = loginIn jsonArray_temp.append(jsonObject) to_render['courseCode'] = courseCode to_render['courseId'] = courseId to_render['courseName'] = courseName to_render['start_date'] = start_date to_render['end_date'] = end_date to_render['avg_video_percent'] = avgRadar_percent[0] to_render['avg_registered_percent'] = avgRadar_percent[1] to_render['avg_countVideo_percent'] = avgRadar_percent[2] to_render['avg_mobile_percent'] = avgRadar_percent[3] to_render['avg_test_percent'] = avgRadar_percent[4] to_render['video_percent'] = RadarColumn_percent[0] to_render['registered_percent'] = RadarColumn_percent[1] to_render['countVideo_percent'] = RadarColumn_percent[2] to_render['mobile_percent'] = RadarColumn_percent[3] to_render['test_percent'] = RadarColumn_percent[4] to_render['RadarData'] = RadarData to_render['jsonArray_RegisteredPersons'] = json.dumps( jsonArray_RegisteredPersons) to_render['jsonArray_active'] = json.dumps(jsonArray_active) to_render['jsonArray_answerRatio'] = json.dumps(jsonArray_answerRatio) to_render['withDrew'] = withDrew to_render['jsonArray_temp'] = json.dumps(jsonArray_temp) to_render['jsonArray_charter'] = json.dumps(jsonArray_charter) to_render['totalRegisteredPersons'] = totalRegisteredPersons to_render['mode'] = mode to_render['update_course_total_data'] = update_course_total_data to_render['update_student_total_data'] = update_student_total_data to_render['update_login_data'] = update_login_data to_render['update_video_activity'] = update_video_activity return to_render
def basic_course_data_view(request): request.encoding = 'utf-8' if request.method == 'GET': to_render = {} coursedata = [] courseId = [] studentsCourse = [] islogin = cookiegetter.isLogined(request) if islogin: userEmail = cookiegetter.getEmail(request) userID = cookiegetter.getUserIDByEmail(userEmail) with connections['ResultDB'].cursor() as cursor: # 取得統計日期,方式是取得最後一筆資料,此筆資料的統計日期就是最新日期 cursor.execute( "SELECT max(run_date) as day FROM student_total_data0912") result = namedtuplefetchall(cursor) finalUpdate_Student = result[-1].day cursor.execute( "SELECT max(統計日期) as day FROM course_total_data_v2") result = namedtuplefetchall(cursor) finalUpdate_course = result[-1].day # 取得學生擁有的課程的資料 cursor.execute( "SELECT course_id,name " "FROM student_total_data0912 " "WHERE run_date = %s and user_id = %s", [finalUpdate_Student, userID]) result = namedtuplefetchall(cursor) studentName = None for rs in result: courseId.append(rs.course_id) studentName = rs.name cursor.execute( "select * from course_total_data_v2 where 統計日期 = %s", [finalUpdate_course]) result = namedtuplefetchall(cursor) data = [] for rs in result: for i in range(len(courseId)): if rs.course_id == courseId[i]: data.clear() data.append(rs.課程代碼) data.append(rs.course_id) data.append(rs.course_name) data.append(rs.註冊人數) data.append(rs.start_date) data.append(rs.end_date) coursedata.append(data.copy()) to_render['result'] = coursedata # 設定日期範圍下拉式選單,預設值為1 to_render['select'] = 1 to_render['finalUpdate'] = '最後資料更新時間 : ' + finalUpdate_Student to_render['studentName'] = studentName to_render['IsLogin'] = 1 print('teacher') return render(request, '1_BasicCourseData.html', to_render) else: print('not login') to_render['IsLogin'] = 2 return render(request, 'noLogin.html', to_render) if request.method == 'POST': to_render = {} # 要傳給html的資料 coursedata = [] courseId = [] # 學生擁有的課程 studentsCourse = [] islogin = cookiegetter.isLogined(request) if islogin: userEmail = cookiegetter.getEmail(request) userID = cookiegetter.getUserIDByEmail(userEmail) choose = request.GET.get('choose', False) S_startDate = request.GET.get('startDate', False) D_userChooseStartDate = getChooseDate(choose, S_startDate) if choose is None: to_render['select'] = 1 to_render['selectStartDate'] = S_startDate else: to_render['select'] = choose to_render['selectStartDate'] = '年/月/日' with connections['ResultDB'].cursor() as cursor: # 取得統計日期,方式是取得最後一筆資料,此筆資料的統計日期就是最新日期 cursor.execute("select * from student_total_data0912") result = namedtuplefetchall(cursor) finalUpdate_Student = result[-1].run_date cursor.execute("select * from course_total_data_v2") result = namedtuplefetchall(cursor) finalUpdate_course = result[-1].統計日期 # 依據使用者選擇的日期,取得資料 cursor.execute( "SELECT course_id,name " "FROM student_total_data0912 " "WHERE run_date = %s and user_id = %s", [finalUpdate_Student, userID]) result = namedtuplefetchall(cursor) studentName = None for rs in result: courseId.append(rs.course_id) studentName = rs.name cursor.execute( "SELECT * " "FROM course_total_data_v2 " "WHERE 統計日期 = %s AND start_date > %s", [finalUpdate_course, D_userChooseStartDate]) result = namedtuplefetchall(cursor) data = [] for rs in result: for i in range(len(courseId)): if rs.course_id == courseId[i]: data.clear() data.append(rs.課程代碼) data.append(rs.course_id) data.append(rs.course_name) data.append(rs.註冊人數) data.append(rs.start_date) data.append(rs.end_date) coursedata.append(data.copy()) to_render['result'] = coursedata to_render['select'] = 1 to_render['finalUpdate'] = '最後資料更新時間 : ' + finalUpdate_Student to_render['studentName'] = studentName to_render['IsLogin'] = 1 return render(request, '1_BasicCourseData.html', to_render) else: to_render['IsLogin'] = 2 return render(request, 'noLogin.html', to_render)
def index_view(request): request.encoding = 'utf-8' if request.method == "GET": to_render = {} islogin = cookiegetter.isLogined(request) if islogin is True: userEmail = cookiegetter.getEmail(request) userID = cookiegetter.getUserIDByEmail(userEmail) with connections['ResultDB'].cursor() as cursor: # 取得學生統計日期 cursor.execute("SELECT max(run_date) as max_run " "FROM student_total_data0912 " "WHERE user_id = %s", [userID]) result = namedtuplefetchall(cursor) StudentfinalUpdate = result[-1].max_run to_render['finalUpdate'] = '最後資料更新時間 : ' + StudentfinalUpdate # 取得統計日期,方式是取得最後一筆資料,此筆資料的統計日期就是最新日期 cursor.execute("select max(統計日期) as max_date from course_total_data_v2") result = namedtuplefetchall(cursor) CoursefinalUpdate = result[-1].max_date to_render['CoursefinalUpdate'] = CoursefinalUpdate courseData = [] cursor.execute( "SELECT (影片觀看人次_台灣 + 影片觀看人次_非台灣 + login_count)/(註冊人數+退選人數) as a,start_date,註冊人數,`course_name`,course_id,課程代碼,end_date " "FROM course_total_data_v2 " "WHERE course_id in (select course_id from student_total_data0912 where run_date ='" + StudentfinalUpdate + "'" + " and user_id = '" + userID + "') and 統計日期='" + CoursefinalUpdate + "' order by course_id" ) result = namedtuplefetchall(cursor) Data = ['' for i in range(9)] duration_week = '' for rs in result: Data[0] = rs.course_name Data[1] = rs.start_date Data[2] = rs.註冊人數 Data[3] = rs.課程代碼 # 計算課程剩餘幾天 if rs.end_date != 'NA': today = datetime.today() day = (datetime.strptime(rs.end_date, '%Y-%m-%d') - today).days duration_week = str(day) Data[4] = duration_week # 判斷課程是否已結束 if rs.end_date != 'NA' and datetime.strptime(rs.end_date, '%Y-%m-%d') < datetime.today(): # 結束 Data[5] = rs.course_name else: # 開課中 Data[5] = '0' Data[6] = rs.end_date Data[7] = rs.course_id if rs.a is not None: Data[8] = rs.a else: Data[8] = '0' courseData.append(Data.copy()) cursor.execute( "SELECT watch_count,login_count,login_count,certificate,last_login,course_id as id,name " "FROM student_total_data0912 " "WHERE run_date = %s and user_id = %s order by id", [StudentfinalUpdate, userID] ) result = namedtuplefetchall(cursor) countAllCourse = 0 countExistCourse = 0 basiccourse = [] selfstudycourse = [] registerdate = getStudentLastlogin(userID) registerdate = datetime.strptime(registerdate, '%Y-%m-%d %H:%M:%S.%f') to_render['date_joined'] = datetime.strftime(registerdate, '%Y-%m-%d') finalCourseData = ['' for i in range(12)] i = 0 readyendexist = False endexist = False startexist = False selfstudyexist = False noData = 0 studentName = '' for rs in result: studentWatchTemp = 0 studentLoginTemp = 0 studentName = rs.name if rs.id == courseData[i][7]: finalCourseData[0] = courseData[i][0] finalCourseData[1] = courseData[i][1] finalCourseData[2] = courseData[i][2] finalCourseData[3] = courseData[i][3] finalCourseData[4] = courseData[i][4] finalCourseData[5] = courseData[i][5] finalCourseData[6] = courseData[i][6] finalCourseData[7] = courseData[i][7] # 若此課程為開課中,此欄為零 if finalCourseData[5] == '0': countExistCourse += 1 if courseData[i][8] is not None: avgcourse = float(courseData[i][8]) else: avgcourse = 0 studentWatchTemp = rs.watch_count studentLoginTemp = rs.login_count if studentWatchTemp + studentLoginTemp < avgcourse: finalCourseData[11] = '待加強' finalCourseData[8] = rs.login_count finalCourseData[9] = rs.last_login if finalCourseData[9] is None: finalCourseData[9] = '無登入紀錄' if rs.certificate == 1: finalCourseData[10] = '是' else: finalCourseData[10] = '否' if finalCourseData[6] != 'NA': basiccourse.append(finalCourseData.copy()) i += 1 if finalCourseData[4] != '0': readyendexist = True if finalCourseData[5] != '0': endexist = True if finalCourseData[5] == '0': startexist = True else: selfstudycourse.append(finalCourseData.copy()) i += 1 selfstudyexist = True else: # 資料庫內無此課程資料 noData += 1 countAllCourse += 1 to_render['studentName'] = studentName to_render['basiccourse'] = basiccourse to_render['selfstudycourse'] = selfstudycourse to_render['selfstudyexist'] = selfstudyexist to_render['startexist'] = startexist to_render['endexist'] = endexist to_render['readyendexist'] = readyendexist courseLogin = [['' for i in range(4)] for i in range(10)] k = 0 cursor.execute( "SELECT course_name,course_id,`課程代碼`,login_count_month " "FROM course_total_data_v2 " "WHERE 統計日期 = %s order by login_count_month desc", [CoursefinalUpdate] ) result = namedtuplefetchall(cursor) for rs in result: if k < 10: courseLogin[k][0] = rs.course_name courseLogin[k][1] = rs.course_id courseLogin[k][2] = '{:,.2f}'.format(rs.login_count_month) courseLogin[k][3] = rs.課程代碼 k += 1 else: break to_render['courseLogin'] = courseLogin totalcourse = countAllCourse - noData endcourse = totalcourse - countExistCourse startcourse = countExistCourse to_render['totalcourse'] = totalcourse to_render['startcourse'] = startcourse to_render['endcourse'] = endcourse with connections['OpenEduDB'].cursor() as cursor: now = datetime.strftime(datetime.today(), '%Y-%m-%d') cursor.execute( "SELECT count(*) as number FROM edxapp.auth_user where last_login > %s", [datetime.strftime] ) result = namedtuplefetchall(cursor) todayLogin = result[-1].number to_render['todayLogin'] = todayLogin to_render['IsLogin'] = 1 print('student') return render(request, 'index.html', to_render) else: to_render['IsLogin'] = 2 print('not login') return render(request, 'noLogin.html', to_render)
def suggest_course(request): request.encoding = 'utf-8' to_render = {} data = [] output = [] with connections['ResultDB'].cursor() as cursor: # 搜尋所有修同一課程之學生 course_id = 'course-v1:FCUx+2015015+201512' cursor.execute( "SELECT distinct(user_id) FROM student_total_data0912 WHERE course_id = %s", [course_id]) users = namedtuplefetchall(cursor) # 所有學生修課資料 cursor.execute("SELECT course_id, user_id FROM student_total_data0912") courses = namedtuplefetchall(cursor) # 課程資料 cursor.execute( "SELECT distinct(course_id), course_name,課程代碼 FROM course_total_data_v2" ) course_data = namedtuplefetchall(cursor) student_count = {} for user in users: for course in courses: if course.user_id == user.user_id: if course.course_id in student_count: student_count[course.course_id] += 1 else: student_count[course.course_id] = 1 # 排序 student_count = dict( sorted(student_count.items(), key=lambda x: x[1], reverse=True)) # 計算人數 total_user = 0 for value in student_count.values(): total_user += value temp = [] c = 0 for key, value in student_count.items(): temp.clear() check = ExistorNot(key, course_data) if c < 5: if check is not None: temp.append(check[0]) temp.append(check[1]) temp.append(check[2]) temp.append(value) temp.append(str(int(value / total_user * 100)) + '%') output.append(temp.copy()) c += 1 else: break cursor.execute( "SELECT course_name FROM course_total_data_v2 WHERE course_id = %s", [course_id]) result = namedtuplefetchall(cursor) CourseName = result[-1].course_name to_render['data'] = output to_render['CourseName'] = CourseName return to_render
def movie_data_view(request): request.encoding = 'utf-8' request.GET = request.GET.copy() request.POST = request.POST.copy() if request.method == 'GET': # 存放最後回傳的資料 data = [] to_render = {} # 資料庫最後更新日期 finalUpdate = None with connections['ResultDB'].cursor() as cursor: cursor.execute("select * from course_total_data_v2") result = namedtuplefetchall(cursor) # 取得統計日期,方式是取得最後一筆資料,此筆資料的統計日期就是最新日期 for rs in result: finalUpdate = rs.統計日期 cursor.execute( "select * from course_total_data_v2 where 統計日期 = %s", [finalUpdate]) result = namedtuplefetchall(cursor) maxRS = len(result) i = 0 list_ = [] for rs in result: data.clear() data.append(rs.課程代碼) data.append(rs.course_id) data.append(rs.course_name) data.append(rs.課程影片數目) data.append('{:,.2f}'.format(rs.影片平均長度)) data.append(rs.影片觀看人數台灣) data.append(rs.影片觀看人數_非台灣) list_.append(data[:]) # 回傳要呈現的資料 to_render['result'] = list_ # 預設日期範圍的下拉式選單,預設值為1:-請選擇- to_render['select'] = 1 # 預設開課中、即將開課等等選項,預設值為3:所有課程 to_render['optradio'] = 1 # 僅提供給DownloadServlet使用,若使用者未設定開課日期的值, # 直接透過網址傳到DownloadServlet會有錯誤 所以若未設定開課日期,將其值設定為no to_render['selectStartDate_forDownload'] = 'no' # 最後更新 to_render['finalUpdate'] = '最後資料更新時間 : ' + finalUpdate # 僅提供給DownloadServlet使用,將日期格式中的橫線去除 for_download = datetime.strptime(finalUpdate, '%Y-%m-%d') to_render['finalUpdate_forDownload'] = for_download.strftime( '%Y%m%d') return render(request, '2_MovieData.html', to_render) if request.method == 'POST': # 存後最後輸出的資料,過濾所有條件 list_ = [] to_render = {} # 資料庫最後更新時間 finalUpdate = None # 經由判斷後取得的使用者選擇的開課日期,從介面中的開課日期及日期範圍的值取得 D_userChooseStartDate = None # 日期範圍 select = request.POST['select'] # 開課日期 S_startDate = request.POST['startDate'] # radio: 開課中、即將開課、已結束、全部課程,之後簡稱為radio optradio = request.POST['optradio'] # 開課日期相關的資料庫語法 date_ = None # 當日期範圍值不是1(-請選擇-)或開課日期不是空字串,代表使用者有使用日期的搜尋 if select != '1' or S_startDate != '': # 將值傳入function取得資料庫語法需要的值,因為開課日期跟日期範圍,其實意義相同,只是設定方式不同 D_userChooseStartDate = getChooseDate(select, S_startDate) # 若日期範圍的值是1(-請選擇-),代表使用者選擇的日期條件是開課日期,這兩個條件一定是二選一的,不可能共存 if select == '1': # 設定值為1,為了回傳到html後使用者所輸入的值不會被初始化 to_render['select'] = 1 to_render['selectStartDate'] = S_startDate # 僅提供給DownloadServlet,因為DownloadServlet是透過網址,取得介面上的值較為麻煩,所以在此設定 for_download = datetime.strptime(S_startDate, '%Y-%m-%d') to_render[ 'selectStartDate_forDownload'] = for_download.strftime( '%Y%m%d') else: to_render['select'] = select to_render['selectStartDate'] = '年/月/日' to_render['selectStartDate_forDownload'] = 'no' date_ = D_userChooseStartDate # 進入else代表使用者沒有使用日期搜尋 else: date_ = '' to_render['select'] = 1 to_render['selectStartDate'] = '年/月/日' to_render['selectStartDate_forDownload'] = 'no' # 若使用者選擇已結束或是即將開課,即將日期語法清除,因為已結束或即將開課都不適用日期搜尋 if (optradio == '2') or (optradio == '3'): date_ = '' to_render['select'] = 1 to_render['selectStartDate'] = '年/月/日' to_render['selectStartDate_forDownload'] = 'no' now = datetime.today().strftime('%Y-%m-%d') to_render['optradio'] = optradio with connections['ResultDB'].cursor() as cursor: # 取得統計日期,方式是取得最後一筆資料,此筆資料的統計日期就是最新日期 cursor.execute("select * from course_total_data_v2 ") result = namedtuplefetchall(cursor) for rs in result: finalUpdate = rs.統計日期 # 依據使用者選擇的條件,取得資料 if date_ == '': if optradio == '0': cursor.execute( "select * from course_total_data_v2" "where 統計日期 = %s" "AND (start_date < %s AND (end_date > %s OR end_date = 'NA'))", [finalUpdate, now, now]) result = namedtuplefetchall(cursor) elif optradio == '1': cursor.execute( "select * from course_total_data_v2" "where 統計日期 = %s", [finalUpdate]) result = namedtuplefetchall(cursor) elif optradio == '2': cursor.execute( "select * from course_total_data_v2" "where 統計日期 = %s" "AND (start_date > %s AND start_date != '2030-01-01')", [finalUpdate, now]) result = namedtuplefetchall(cursor) elif optradio == '3': cursor.execute( "select * from course_total_data_v2" "where 統計日期 = %s" "AND (start_date < %s AND end_date < %s)", [finalUpdate, now, now]) result = namedtuplefetchall(cursor) else: if optradio == '0': cursor.execute( "select * from course_total_data_v2" "where 統計日期 = %s" "AND start_date > %s" "AND (start_date < %s AND (end_date > %s OR end_date = 'NA'))", [finalUpdate, date_, now, now]) result = namedtuplefetchall(cursor) elif optradio == '1': cursor.execute( "select * from course_total_data_v2" "where 統計日期 = %s" "AND start_date > %s", [finalUpdate, date_]) result = namedtuplefetchall(cursor) elif optradio == '2': cursor.execute( "select * from course_total_data_v2" "where 統計日期 = %s" "AND (start_date > %s AND start_date != '2030-01-01')", [finalUpdate, now]) result = namedtuplefetchall(cursor) elif optradio == '3': cursor.execute( "select * from course_total_data_v2" "where 統計日期 = %s" "AND (start_date < %s AND end_date < %s)", [finalUpdate, now, now]) result = namedtuplefetchall(cursor) # ------------------------------------- # 依據使用者選擇的日期,取得資料 data = [] for rs in result: data.clear() data.append(rs.課程代碼) data.append(rs.course_id) data.append(rs.course_name) data.append(rs.課程影片數目) data.append('{:,.2f}'.format(rs.影片平均長度)) data.append(rs.影片觀看人數台灣) data.append(rs.影片觀看人數_非台灣) list_.append(data[:]) to_render['result'] = list_ to_render['finalUpdate'] = '最後資料更新時間 : ' + finalUpdate for_download = datetime.strptime(finalUpdate, '%Y-%m-%d') to_render['finalUpdate_forDownload'] = for_download.strftime( '%Y%m%d') return render(request, '2_MovieData.html', to_render)
def suggested_by_age(request): to_render = {} with connections['ResultDB'].cursor() as cursor: cursor.execute( "SELECT 課程代碼, course_id, course_name, age_17, age_18_25, age_26_ FROM course_total_data_v2" ) result = namedtuplefetchall(cursor) max_17 = 0 max_18_25 = 0 max_26_ = 0 total_17 = 0 total_18_25 = 0 total_26_ = 0 output_17 = ['' for i in range(4)] output_18_25 = ['' for i in range(4)] output_26_ = ['' for i in range(4)] for rs in result: totalage = rs.age_17 + rs.age_18_25 + rs.age_26_ if max_17 < rs.age_17: max_17 = rs.age_17 total_17 = totalage output_17[0] = rs.課程代碼 output_17[1] = rs.course_id output_17[2] = rs.course_name output_17[3] = str(max_17) elif max_17 == rs.age_17: if total_17 > totalage: output_17[0] = rs.課程代碼 output_17[1] = rs.course_id output_17[2] = rs.course_name output_17[3] = str(max_17) if max_18_25 < rs.age_18_25: max_18_25 = rs.age_18_25 total_18_25 = totalage output_18_25[0] = rs.課程代碼 output_18_25[1] = rs.course_id output_18_25[2] = rs.course_name output_18_25[3] = str(max_18_25) elif max_18_25 == rs.age_18_25: if total_18_25 > totalage: output_18_25[0] = rs.課程代碼 output_18_25[1] = rs.course_id output_18_25[2] = rs.course_name output_18_25[3] = str(max_18_25) if max_26_ < rs.age_26_: max_26_ = rs.age_26_ total_26_ = totalage output_26_[0] = rs.課程代碼 output_26_[1] = rs.course_id output_26_[2] = rs.course_name output_26_[3] = str(max_26_) elif max_26_ == rs.age_26_: if total_26_ > totalage: output_26_[0] = rs.課程代碼 output_26_[1] = rs.course_id output_26_[2] = rs.course_name output_26_[3] = str(max_26_) to_render['output_26_'] = output_26_ to_render['output_18_25'] = output_18_25 to_render['output_17'] = output_17 return to_render
def before_survey_view(request): request.encoding = 'utf-8' request.GET = request.GET.copy() request.POST = request.POST.copy() to_render = {} if request.method == 'GET': courseNumber = 0 surveyNumber = 0 data = None allCourseID = [] count = 0 with connections['SurveyDB'].cursor() as cursor: # 取得course_total_data_v2 更新日期 cursor.execute( "SELECT max(統計日期) as date FROM edxresult.course_total_data_v2") result = namedtuplefetchall(cursor) update_course_total_data = result[-1].date # 找出所有課程的id(不重複) cursor.execute( "SELECT DISTINCT(RR.course_id) " "FROM survey.recent_report as RR,edxresult.course_total_data_v2 as CTD " "WHERE RR.survey_id = '15' && RR.course_id = CTD.course_id && CTD.統計日期 = %s order by RR.course_id", [update_course_total_data]) result = namedtuplefetchall(cursor) courseNumber = len(result) data = [['' for i in range(11)] for i in range(courseNumber)] for rs in result: allCourseID.append(rs.course_id) cursor.execute( "SELECT RR.course_id,RR.questions " "FROM survey.recent_report as RR,edxresult.course_total_data_v2 as CTD " "WHERE RR.survey_id = '15' && RR.course_id = CTD.course_id && CTD.統計日期 = %s order by RR.course_id", [update_course_total_data]) result = namedtuplefetchall(cursor) for rs in result: CourseData = getCourseData(rs.course_id) data[count][0] = CourseData[0] data[count][1] = CourseData[1] data[count][3] = CourseData[2] data[count][2] = rs.course_id break count = 0 data[count][6] = '0' data[count][7] = '0' data[count][8] = '0' data[count][9] = '0' data[count][10] = '0' j = [] json1 = {} json2 = [] for i in range(len(result)): if result[i].course_id == allCourseID[count]: selection1 = 0 selection2 = 0 selection3 = 0 json = result[i].questions j.clear() j.append(json) jsonFirstArray = j[0] json1 = jsonFirstArray.copy() jsonProblem = json1['problems'] json2 = jsonProblem.copy() jsonQuestionOne = json2[0] json3 = jsonQuestionOne.copy() selection1 = int(json3['select']) jsonQuestionTwo = json2[1] json4 = jsonQuestionTwo.copy() selection2 = int(json4['select']) jsonQuestionThree = json2[2] json5 = jsonQuestionThree.copy() selection3 = int(json5['select']) surveyNumber = surveyNumber + 1 data[count][int(6 + selection1)] = str( int(data[count][6 + selection1]) + 1) data[count][9] = str( int(data[count][9]) + (selection2 - 5) * (-1)) data[count][10] = str( int(data[count][10]) + (selection3 - 5) * (-1)) else: data[count][4] = str(surveyNumber) data[count][5] = '{:.1f}'.format( surveyNumber / int(data[count][3]) * 100) + '%' data[count][6] = '{:.1f}'.format( float(data[count][6]) / surveyNumber * 100) + '%' data[count][7] = '{:.1f}'.format( float(data[count][7]) / surveyNumber * 100) + '%' data[count][8] = '{:.1f}'.format( float(data[count][8]) / surveyNumber * 100) + '%' data[count][9] = '{:.1f}'.format( float(data[count][9]) / surveyNumber) data[count][10] = '{:.1f}'.format( float(data[count][10]) / surveyNumber) count = count + 1 CourseData = getCourseData(result[i].course_id) data[count][0] = CourseData[0] data[count][1] = CourseData[1] data[count][3] = CourseData[2] data[count][2] = result[i].course_id data[count][6] = '0' data[count][7] = '0' data[count][8] = '0' data[count][9] = '0' data[count][10] = '0' surveyNumber = 0 i = i - 1 data[count][4] = str(surveyNumber) data[count][5] = '{:.1f}'.format( surveyNumber / int(data[count][3]) * 100) + '%' data[count][6] = '{:.1f}'.format( float(data[count][6]) / surveyNumber * 100) + '%' data[count][7] = '{:.1f}'.format( float(data[count][7]) / surveyNumber * 100) + '%' data[count][8] = '{:.1f}'.format( float(data[count][8]) / surveyNumber * 100) + '%' data[count][9] = '{:.1f}'.format( float(data[count][9]) / surveyNumber) data[count][10] = '{:.1f}'.format( float(data[count][10]) / surveyNumber) to_render['data'] = data return render(request, 'beforeSurvey.html', to_render)
def analysis_data_view(request): request.encoding = 'utf-8' request.GET = request.GET.copy() request.POST = request.POST.copy() to_render = {} if request.method == 'GET': select = request.GET.get('select', 0) select_int = int(select) to_render['select'] = select wc4sb = [['%SE%', '%SD%'], ['%SE%', '%SD%', '%X%']] statisticsBy = [" and (課程代碼 like '%%SE%%' or 課程代碼 like '%%SD%%' )", " and 課程代碼 not like '%%SE%%' and 課程代碼 not like '%%SD%%' and 課程代碼 not like '%%X%%'", " "] # 取得兩個月前的日期 now = datetime.now() twoMonthAgo = (now + relativedelta(months=-2)).strftime('%Y-%m-%d') with connections['OpenEduDB'].cursor() as cursor: '''____________________年度____________________''' getYear = 0 # 每筆資料的年份 # -----------------註冊人數------------------ cursor.execute("SELECT date_joined FROM auth_user order by date_joined") result = namedtuplefetchall(cursor) # 取得最大年分 MaxYear = datetime.strptime(result[-1].date_joined, '%Y-%m-%d %H:%M:%S').year # 創建列表並初始化 registeredYear = [0 for i in range(int(MaxYear) - 2014 + 1)] # 最後回傳 Json_registeredYear = [] # 加入欄位定義 jsonArray_temp = ["Element", "註冊人數"] jsonObject = {'role': 'style'} jsonArray_temp.append(jsonObject.copy()) Json_registeredYear.append(jsonArray_temp.copy()) # 計算資料 for rs in result: getYear = datetime.strptime(rs.date_joined, '%Y-%m-%d %H:%M:%S').year registeredYear[getYear-2014] = registeredYear[getYear-2014] + 1 # 加入JSON for i in range(len(registeredYear)): jsonArray_temp.clear() jsonArray_temp.append(str(i+2014)) jsonArray_temp.append(registeredYear[i]) jsonArray_temp.append('50a4e5') Json_registeredYear.append(jsonArray_temp.copy()) to_render['Json_registeredYear'] = json.dumps(Json_registeredYear) # ============================================= # --------------------性別--------------------- Json_genderYear = [] # 最後回傳 maleYear = [0 for i in range(int(MaxYear) - 2014 + 1)] # 儲存男性數量 femaleYear = [0 for i in range(int(MaxYear) - 2014 + 1)] # 儲存女性數量 otherYear = [0 for i in range(int(MaxYear) - 2014 + 1)] # 儲存其他性別數量 # 計算女性資料 cursor.execute( "SELECT gender,date_joined " "FROM edxapp.auth_user as AU,edxapp.auth_userprofile as AUP " "WHERE AU.id = AUP.user_id and gender = 'f'" ) result = namedtuplefetchall(cursor) for rs in result: getYear = datetime.strptime(rs.date_joined, '%Y-%m-%d').year femaleYear[getYear-2014] = femaleYear[getYear-2014] + 1 # 計算男性資料 cursor.execute( "SELECT gender,date_joined " "FROM edxapp.auth_user as AU,edxapp.auth_userprofile as AUP " "WHERE AU.id = AUP.user_id and gender = 'm'" ) result = namedtuplefetchall(cursor) for rs in result: getYear = datetime.strptime(rs.date_joined, '%Y-%m-%d').year maleYear[getYear-2014] = maleYear[getYear-2014] + 1 # 計算其他性別資料 cursor.execute( "SELECT gender,date_joined " "FROM edxapp.auth_user as AU,edxapp.auth_userprofile as AUP " "WHERE AU.id = AUP.user_id and gender != 'f' and gender != 'm'" ) result = namedtuplefetchall(cursor) for rs in result: getYear = datetime.strptime(rs.date_joined, '%Y-%m-%d').year otherYear[getYear-2014] = otherYear[getYear-2014] + 1 # 加入欄位定義 jsonArray_temp.clear() jsonArray_temp.append(' ') jsonArray_temp.append('男性') jsonArray_temp.append('女性') jsonArray_temp.append('其他') jsonObject['role'] = 'annotation' jsonArray_temp.append(jsonObject.copy()) Json_genderYear.append(jsonArray_temp.copy()) # 加入JSON for i in range(len(registeredYear)): jsonArray_temp.clear() jsonArray_temp.append(str(2014+i)) jsonArray_temp.append(maleYear[i]) jsonArray_temp.append(femaleYear[i]) jsonArray_temp.append(otherYear[i]) jsonArray_temp.append(' ') Json_genderYear.append(jsonArray_temp.copy()) to_render['Json_genderYear'] = json.dumps(Json_genderYear) '''___________________分布___________________''' # --------------------學歷--------------------- education_p = 0 # 博士 education_m = 0 # 碩士 education_b = 0 # 學士 education_a = 0 # 副學士 education_hs = 0 # 高中 education_jhs = 0 # 國中 education_el = 0 # 國小 education_other = 0 # 其他 cursor.execute("SELECT id,level_of_education FROM auth_userprofile") result = namedtuplefetchall(cursor) for rs in result: if rs.level_of_education is not None: if rs.level_of_education == 'p': education_p = education_p + 1 elif rs.level_of_education == 'm': education_m = education_m + 1 elif rs.level_of_education == 'jhs': education_jhs = education_jhs +1 elif rs.level_of_education == 'hs': education_hs = education_hs + 1 elif rs.level_of_education == 'el': education_el = education_el + 1 elif rs.level_of_education == 'b': education_b = education_b +1 elif rs.level_of_education == 'a': education_a = education_a + 1 else: education_other = education_other + 1 else: education_other = education_other + 1 to_render['education_p'] = education_p to_render['education_m'] = education_m to_render['education_jhs'] = education_jhs to_render['education_hs'] = education_hs to_render['education_el'] = education_el to_render['education_b'] = education_b to_render['education_a'] = education_a to_render['education_other'] = education_other # =============================================== # --------------------年齡----------------------- age = [0, 0, 0, 0, 0, 0, 0, 0] cursor.execute("SELECT year_of_birth FROM auth_userprofile") result = namedtuplefetchall(cursor) for rs in result: year_of_birth = int(rs.year_of_birth) index = (int(MaxYear) - year_of_birth) / 4 - 3 if 0 <= index <= 6: age[int(index)] = age[int(index)] + 1 elif 6 < index < 20: age[6] = age[6] + 1 elif index == -1: age[0] = age[0] + 1 else: age[7] = age[7] + 1 to_render['age'] = age # 性別 gender = [0, 0, 0] for i in range(int(MaxYear) - 2014 + 1): gender[0] = gender[0] + maleYear[i] gender[1] = gender[1] + femaleYear[i] gender[2] = gender[2] + otherYear[i] to_render['gender'] = gender with connections['ResultDB'].cursor() as cursor: # course_total_data_v2 更新日期 cursor.execute("SELECT max(統計日期) as date FROM course_total_data_v2") result = namedtuplefetchall(cursor) update_course_total_data = result[-1].date to_render['update_course_total_data'] = update_course_total_data # 取得年份 MaxYear = datetime.strptime(update_course_total_data, '%Y-%m-%d').year minYear = 2014 currentYear = minYear '''___________________年度___________________''' # -----------------開課週數------------------ Json_DurationWeekYear = [] Json_DurationWeekYear_RE = [] data = [] boxPlotData = [] jsonArray_temp = [] jsonObject = {} while currentYear <= int(MaxYear): data.clear() boxPlotData.clear() wildcard = '%X%' cursor.execute( "SELECT duration_week " "FROM course_total_data_v2 " "WHERE 統計日期 = %s and start_date > %s and start_date < %s " "and duration_week is not NULL and 課程代碼 not like '%%X%%'", [update_course_total_data, str(currentYear), str(currentYear+1)] ) result = namedtuplefetchall(cursor) for rs in result: data.append(rs.duration_week) # 一般 boxPlotData = getBoxPlotValue(data) jsonArray_temp.clear() jsonObject.clear() jsonObject['exp'] = currentYear jsonObject['high'] = boxPlotData[0] jsonObject['open'] = boxPlotData[1] jsonObject['mid'] = boxPlotData[2] jsonObject['close'] = boxPlotData[3] jsonObject['low'] = boxPlotData[4] Json_DurationWeekYear.append(jsonObject.copy()) # 標準化 data = standardization(data) boxPlotData = getBoxPlotValue(data) jsonObject.clear() jsonObject['exp'] = currentYear jsonObject['high'] = boxPlotData[0] jsonObject['open'] = boxPlotData[1] jsonObject['mid'] = boxPlotData[2] jsonObject['close'] = boxPlotData[3] jsonObject['low'] = boxPlotData[4] Json_DurationWeekYear_RE.append(jsonObject.copy()) currentYear = currentYear + 1 to_render['Json_DurationWeekYear'] = json.dumps(Json_DurationWeekYear) to_render['Json_DurationWeekYear_RE'] = json.dumps(Json_DurationWeekYear_RE) # 影片數量、討論區討論次數、作答過半人數比例、觀看過半人數比例、退選率 Json_NumberOfMovieYear = [] # 影片數量 Json_NumberOfDiscussionsYear = [] # 討論區討論次數 Json_AnswerHalfYear = [] # 作答過半人數比例 Json_WatchHalfYear = [] # 觀看過半人數比例 Json_DropYear = [] # 退選率 Json_RegisterCourseYear = [] # 註冊課程人次 Json_NumberOfMovieYear_RE = [] # 影片數量_標準化 Json_NumberOfDiscussionsYear_RE = [] # 討論區討論次數_標準化 Json_AnswerHalfYear_RE = [] # 作答過半人數比例_標準化 Json_WatchHalfYear_RE = [] # 觀看過半人數比例_標準化 Json_DropYear_RE = [] # 退選率_標準化 Json_RegisterCourseYear_RE = [] # 註冊課程人次_標準化 NumberOfMovieYear = [] NumberOfDiscussionsYear = [] AnswerHalfYear = [] WatchHalfYear = [] DropYear = [] RegisterCourseYear = [] haveData = False currentYear = minYear while currentYear <= int(MaxYear): boxPlotData.clear() NumberOfMovieYear.clear() NumberOfDiscussionsYear.clear() AnswerHalfYear.clear() WatchHalfYear.clear() DropYear.clear() RegisterCourseYear.clear() cursor.execute( "SELECT 課程影片數目 as NumberOfMovie, 討論區討論次數 as NumberOfDiscussions, " "作答過半人數/註冊人數 as AnswerHalf, " "(影片觀看過半人數_台灣 + 影片觀看過半人數_非台灣)/(影片觀看人數_非台灣 + 影片觀看人數台灣) as WatchHalf, " "退選人數/註冊人數 as droppercent, 註冊人數 as RegisterCourse " "FROM course_total_data_v2 " "WHERE 統計日期 = %s and start_date > %s and start_date < %s " + statisticsBy[select_int], [update_course_total_data, str(currentYear), str(currentYear + 1)] ) result = namedtuplefetchall(cursor) for rs in result: haveData = True if rs.AnswerHalf is not None: AnswerHalfYear.append(float(rs.AnswerHalf)) else: AnswerHalfYear.append(0) if rs.WatchHalf is not None: WatchHalfYear.append(float(rs.WatchHalf)) else: WatchHalfYear.append(0) if rs.droppercent is not None: DropYear.append(float(rs.droppercent)) else: DropYear.append(0) NumberOfMovieYear.append(rs.NumberOfMovie) NumberOfDiscussionsYear.append(rs.NumberOfDiscussions) # AnswerHalfYear.append(float(rs.AnswerHalf)) # WatchHalfYear.append(rs.WatchHalf) # DropYear.append(rs.droppercent) RegisterCourseYear.append(rs.RegisterCourse) if haveData: # 加入影片數量資料 boxPlotData = getBoxPlotValue(NumberOfMovieYear) jsonArray_temp.clear() jsonObject.clear() jsonObject['exp'] = currentYear jsonObject['high'] = boxPlotData[0] jsonObject['open'] = boxPlotData[1] jsonObject['mid'] = boxPlotData[2] jsonObject['close'] = boxPlotData[3] jsonObject['low'] = boxPlotData[4] Json_NumberOfMovieYear.append(jsonObject.copy()) # 加入討論區次數資料 boxPlotData = getBoxPlotValue(NumberOfDiscussionsYear) jsonArray_temp.clear() jsonObject.clear() jsonObject['exp'] = currentYear jsonObject['high'] = boxPlotData[0] jsonObject['open'] = boxPlotData[1] jsonObject['mid'] = boxPlotData[2] jsonObject['close'] = boxPlotData[3] jsonObject['low'] = boxPlotData[4] Json_NumberOfDiscussionsYear.append(jsonObject.copy()) # 加入作答過半人數比例資料 boxPlotData = getBoxPlotValue(AnswerHalfYear) jsonArray_temp.clear() jsonObject.clear() jsonObject['exp'] = currentYear jsonObject['high'] = boxPlotData[0]*100 jsonObject['open'] = boxPlotData[1]*100 jsonObject['mid'] = boxPlotData[2]*100 jsonObject['close'] = boxPlotData[3]*100 jsonObject['low'] = boxPlotData[4]*100 Json_AnswerHalfYear.append(jsonObject.copy()) # 加入觀看過半人數比例資料 boxPlotData = getBoxPlotValue(WatchHalfYear) jsonArray_temp.clear() jsonObject.clear() jsonObject['exp'] = currentYear jsonObject['high'] = boxPlotData[0] * 100 jsonObject['open'] = boxPlotData[1] * 100 jsonObject['mid'] = boxPlotData[2] * 100 jsonObject['close'] = boxPlotData[3] * 100 jsonObject['low'] = boxPlotData[4] * 100 Json_WatchHalfYear.append(jsonObject.copy()) # 加入退選率資料 boxPlotData = getBoxPlotValue(DropYear) jsonArray_temp.clear() jsonObject.clear() jsonObject['exp'] = currentYear jsonObject['high'] = boxPlotData[0] * 100 jsonObject['open'] = boxPlotData[1] * 100 jsonObject['mid'] = boxPlotData[2] * 100 jsonObject['close'] = boxPlotData[3] * 100 jsonObject['low'] = boxPlotData[4] * 100 Json_DropYear.append(jsonObject.copy()) # 加入註冊課程人次資料 boxPlotData = getBoxPlotValue(RegisterCourseYear) jsonArray_temp.clear() jsonObject.clear() jsonObject['exp'] = currentYear jsonObject['high'] = boxPlotData[0] jsonObject['open'] = boxPlotData[1] jsonObject['mid'] = boxPlotData[2] jsonObject['close'] = boxPlotData[3] jsonObject['low'] = boxPlotData[4] Json_RegisterCourseYear.append(jsonObject.copy()) '''_____以下為標準化_____''' # 加入影片數量資料 NumberOfMovieYear = standardization(NumberOfMovieYear) boxPlotData = getBoxPlotValue(NumberOfMovieYear) jsonArray_temp.clear() jsonObject.clear() jsonObject['exp'] = currentYear jsonObject['high'] = boxPlotData[0] jsonObject['open'] = boxPlotData[1] jsonObject['mid'] = boxPlotData[2] jsonObject['close'] = boxPlotData[3] jsonObject['low'] = boxPlotData[4] Json_NumberOfMovieYear_RE.append(jsonObject.copy()) # 加入討論區次數資料 NumberOfDiscussionsYear = standardization(NumberOfDiscussionsYear) boxPlotData = getBoxPlotValue(NumberOfDiscussionsYear) jsonArray_temp.clear() jsonObject.clear() jsonObject['exp'] = currentYear jsonObject['high'] = boxPlotData[0] jsonObject['open'] = boxPlotData[1] jsonObject['mid'] = boxPlotData[2] jsonObject['close'] = boxPlotData[3] jsonObject['low'] = boxPlotData[4] Json_NumberOfDiscussionsYear_RE.append(jsonObject.copy()) # 加入作答過半人數比例資料 AnswerHalfYear = standardization(AnswerHalfYear) boxPlotData = getBoxPlotValue(AnswerHalfYear) jsonArray_temp.clear() jsonObject.clear() jsonObject['exp'] = currentYear jsonObject['high'] = boxPlotData[0] * 100 jsonObject['open'] = boxPlotData[1] * 100 jsonObject['mid'] = boxPlotData[2] * 100 jsonObject['close'] = boxPlotData[3] * 100 jsonObject['low'] = boxPlotData[4] * 100 Json_AnswerHalfYear_RE.append(jsonObject.copy()) # 加入觀看過半人數比例資料 WatchHalfYear = standardization(WatchHalfYear) boxPlotData = getBoxPlotValue(WatchHalfYear) jsonArray_temp.clear() jsonObject.clear() jsonObject['exp'] = currentYear jsonObject['high'] = boxPlotData[0] * 100 jsonObject['open'] = boxPlotData[1] * 100 jsonObject['mid'] = boxPlotData[2] * 100 jsonObject['close'] = boxPlotData[3] * 100 jsonObject['low'] = boxPlotData[4] * 100 Json_WatchHalfYear_RE.append(jsonObject.copy()) # 加入退選率資料 DropYear = standardization(DropYear) boxPlotData = getBoxPlotValue(DropYear) jsonArray_temp.clear() jsonObject.clear() jsonObject['exp'] = currentYear jsonObject['high'] = boxPlotData[0] * 100 jsonObject['open'] = boxPlotData[1] * 100 jsonObject['mid'] = boxPlotData[2] * 100 jsonObject['close'] = boxPlotData[3] * 100 jsonObject['low'] = boxPlotData[4] * 100 Json_DropYear_RE.append(jsonObject.copy()) # 加入註冊課程人次資料 RegisterCourseYear = standardization(RegisterCourseYear) boxPlotData = getBoxPlotValue(RegisterCourseYear) jsonArray_temp.clear() jsonObject.clear() jsonObject['exp'] = currentYear jsonObject['high'] = boxPlotData[0] jsonObject['open'] = boxPlotData[1] jsonObject['mid'] = boxPlotData[2] jsonObject['close'] = boxPlotData[3] jsonObject['low'] = boxPlotData[4] Json_RegisterCourseYear_RE.append(jsonObject.copy()) currentYear = currentYear + 1 to_render['Json_NumberOfMovieYear'] = json.dumps(Json_NumberOfMovieYear) to_render['Json_NumberOfDiscussionsYear'] = json.dumps(Json_NumberOfDiscussionsYear) to_render['Json_AnswerHalfYear'] = json.dumps(Json_AnswerHalfYear) to_render['Json_WatchHalfYear'] = json.dumps(Json_WatchHalfYear) to_render['Json_DropYear'] = json.dumps(Json_DropYear) to_render['Json_RegisterCourseYear'] = json.dumps(Json_RegisterCourseYear) to_render['Json_NumberOfMovieYear_RE'] = json.dumps(Json_NumberOfMovieYear_RE) to_render['Json_NumberOfDiscussionsYear_RE'] = json.dumps(Json_NumberOfDiscussionsYear_RE) to_render['Json_AnswerHalfYear_RE'] = json.dumps(Json_AnswerHalfYear_RE) to_render['Json_WatchHalfYear_RE'] = json.dumps(Json_WatchHalfYear_RE) to_render['Json_DropYear_RE'] = json.dumps(Json_DropYear_RE) to_render['Json_RegisterCourseYear_RE'] = json.dumps(Json_RegisterCourseYear_RE) # ======================================================================== Json_NumberOfCourseYear = [] # 開課數量 NumberOfCourseYear_Self = [0 for i in range(int(MaxYear) - minYear + 1)] NumberOfCourseYear_Normal = [0 for i in range(int(MaxYear) - minYear + 1)] currentYear = minYear while currentYear <= int(MaxYear): cursor.execute( "SELECT 課程代碼 " "FROM course_total_data_v2 " "WHERE 統計日期 = %s and start_date > %s and start_date < %s", [update_course_total_data, str(currentYear), str(currentYear+1)] ) result = namedtuplefetchall(cursor) for rs in result: if rs.課程代碼[0] == 'S': NumberOfCourseYear_Self[currentYear-2014] = NumberOfCourseYear_Self[currentYear-2014] + 1 else: NumberOfCourseYear_Normal[currentYear-2014] = NumberOfCourseYear_Normal[currentYear-2014] + 1 currentYear = currentYear + 1 for i in range(int(MaxYear) - 2014 + 1): # 加入開課數量資料 jsonArray_temp.clear() jsonObject.clear() jsonObject['year'] = str(2014+i) jsonObject['europe'] = NumberOfCourseYear_Self[i] jsonObject['namerica'] = NumberOfCourseYear_Normal[i] Json_NumberOfCourseYear.append(jsonObject.copy()) to_render['Json_NumberOfCourseYear'] = json.dumps(Json_NumberOfCourseYear) # ================================================================ '''_____分布圖-開課時間達兩個月以上課程_____''' # -------------影片平均長度(AML) & 影片觀看過半人數比例(WHP)-------------- Json_AML_WHP = [] avgMovieLong = [] WatchHalfPercent = [] # 設定欄位 jsonArray_temp.clear() jsonArray_temp.append('影片平均長度') jsonArray_temp.append('影片觀看過半人數比例') Json_AML_WHP.append(jsonArray_temp.copy()) cursor.execute( "SELECT 影片平均長度,(影片觀看過半人數_台灣 + 影片觀看過半人數_非台灣)/註冊人數 as percent " "FROM edxresult.course_total_data_v2 " "WHERE 統計日期 = %s and start_date < %s and 課程影片數目 != 0 and 影片平均長度 != 0 " "" + statisticsBy[select_int], [update_course_total_data, twoMonthAgo] ) result = namedtuplefetchall(cursor) for rs in result: avgMovieLong.append(rs.影片平均長度) WatchHalfPercent.append(rs.percent) jsonArray_temp.clear() jsonArray_temp.append(rs.影片平均長度) jsonArray_temp.append(rs.percent) Json_AML_WHP.append(jsonArray_temp.copy()) avgAML = getListAvg(avgMovieLong) avgWHP = getListAvg(WatchHalfPercent) cc_AML_WHP_normal = CorrelationCoefficient(avgMovieLong, WatchHalfPercent, avgAML, avgWHP) degree_AML_WHP_normal = degree(cc_AML_WHP_normal) to_render['Json_AML_WHP'] = json.dumps(Json_AML_WHP, cls=DecimalEncoder) to_render['cc_AML_WHP_normal'] = '{:,.2f}'.format(cc_AML_WHP_normal) to_render['degree_AML_WHP_normal'] = degree_AML_WHP_normal to_render['avgAML'] = '{:,.2f}'.format(avgAML) to_render['avgWHP'] = '{:,.2f}'.format(avgWHP) '''_______以下為標準化_______''' Json_AML_WHP_RE = [] AML_WHP_List_RE = standardizationForCorrelationCoefficient(WatchHalfPercent, avgMovieLong) avgMovieLong_RE = [] WatchHalfPercent_RE = [] # 設定欄位 jsonArray_temp.clear() jsonArray_temp.append('影片平均長度') jsonArray_temp.append('影片觀看過半人數比例') Json_AML_WHP_RE.append(jsonArray_temp.copy()) for i in range(len(AML_WHP_List_RE)): avgMovieLong_RE.append(AML_WHP_List_RE[i][1]) WatchHalfPercent_RE.append(AML_WHP_List_RE[i][0]) # 加入資料 jsonArray_temp.clear() jsonArray_temp.append(AML_WHP_List_RE[i][1]) jsonArray_temp.append(AML_WHP_List_RE[i][0]) Json_AML_WHP_RE.append(jsonArray_temp.copy()) avgAML_RE = getListAvg(avgMovieLong_RE) avgWHP_RE = getListAvg(WatchHalfPercent_RE) cc_AML_WHP_RE = CorrelationCoefficient(avgMovieLong_RE, WatchHalfPercent_RE, avgAML_RE, avgWHP_RE) degree_AML_WHP_RE = degree(cc_AML_WHP_RE) to_render['Json_AML_WHP_RE'] = json.dumps(Json_AML_WHP_RE, cls=DecimalEncoder) to_render['cc_AML_WHP_RE'] = '{:,.2f}'.format(cc_AML_WHP_RE) to_render['degree_AML_WHP_RE'] = degree_AML_WHP_RE to_render['avgAML_RE'] = '{:,.2f}'.format(avgAML_RE) to_render['avgWHP_RE'] = '{:,.2f}'.format(avgWHP_RE) # ================================================= # ----------影片平均長度(AML) & 影片觀看人數比例(WP)---------- Json_AML_WP = [] WatchPercent = [] # 設定欄位 jsonArray_temp.clear() jsonArray_temp.append('影片平均長度') jsonArray_temp.append('影片觀看過半人數比例') Json_AML_WP.append(jsonArray_temp.copy()) cursor.execute( "SELECT 影片平均長度,(影片觀看人數台灣 + 影片觀看人數_非台灣)/註冊人數 as percent " "FROM edxresult.course_total_data_v2 " "WHERE 統計日期 = %s and start_date < %s and 課程影片數目 != 0 and 影片平均長度 != 0 " "" + statisticsBy[select_int], [update_course_total_data, twoMonthAgo] ) result = namedtuplefetchall(cursor) for rs in result: WatchPercent.append(rs.percent) jsonArray_temp.clear() jsonArray_temp.append(rs.影片平均長度) jsonArray_temp.append(rs.percent) Json_AML_WP.append(jsonArray_temp.copy()) avgWP = getListAvg(WatchPercent) cc_AML_WP_normal = CorrelationCoefficient(avgMovieLong, WatchPercent, avgAML, avgWP) degree_AML_WP_normal = degree(cc_AML_WP_normal) to_render['Json_AML_WP'] = json.dumps(Json_AML_WP, cls=DecimalEncoder) to_render['cc_AML_WP_normal'] = '{:,.2f}'.format(cc_AML_WP_normal) to_render['degree_AML_WP_normal'] = degree_AML_WP_normal to_render['avgWP'] = '{:,.2f}'.format(avgWP) '''_______以下為標準化_______''' Json_AML_WP_RE = [] AML_WP_List_RE = standardizationForCorrelationCoefficient(WatchPercent, avgMovieLong) avgMovieLong_RE.clear() WatchPercent_RE = [] # 設定欄位 jsonArray_temp.clear() jsonArray_temp.append('影片平均長度') jsonArray_temp.append('影片觀看過半人數比例') Json_AML_WP_RE.append(jsonArray_temp.copy()) for i in range(len(AML_WP_List_RE)): avgMovieLong_RE.append(AML_WP_List_RE[i][1]) WatchPercent_RE.append(AML_WP_List_RE[i][0]) # 加入資料 jsonArray_temp.clear() jsonArray_temp.append(AML_WP_List_RE[i][1]) jsonArray_temp.append(AML_WP_List_RE[i][0]) Json_AML_WP_RE.append(jsonArray_temp.copy()) avgAML_RE = getListAvg(avgMovieLong_RE) avgWP_RE = getListAvg(WatchPercent_RE) cc_AML_WP_RE = CorrelationCoefficient(avgMovieLong_RE, WatchPercent_RE, avgAML_RE, avgWP_RE) degree_AML_WP_RE = degree(cc_AML_WP_RE) to_render['Json_AML_WP_RE'] = json.dumps(Json_AML_WP_RE, cls=DecimalEncoder) to_render['cc_AML_WP_RE'] = '{:,.2f}'.format(cc_AML_WP_RE) to_render['degree_AML_WP_RE'] = degree_AML_WP_RE to_render['avgAML_RE'] = '{:,.2f}'.format(avgAML_RE) to_render['avgWP_RE'] = '{:,.2f}'.format(avgWHP_RE) return render(request, 'analysisData.html', to_render)
def forum_data_view(request): request.encoding = 'utf-8' if request.method == "GET": to_render = {} islogin = cookiegetter.isLogined(request) list_ = [] if islogin is True: userEmail = cookiegetter.getEmail(request) userID = cookiegetter.getUserIDByEmail(userEmail) if cookiegetter.isTeacher(userID): teachersCourse = cookiegetter.get_Teacher_Courses(userID) with connections['ResultDB'].cursor() as cursor: # 取得最新統計日期 # 原Java版式搜尋course_total_data,但因為field name有問題因此改用course_total_data_v2 # 兩者間域名域名有些不同,如id -> course_id, 其餘包含.之域名全改為_ cursor.execute("SELECT 統計日期 " "FROM edxresult.course_total_data_v2") result = namedtuplefetchall(cursor) finalUpdate = result[-1].統計日期 # 取得老師擁有的課程資料 cursor.execute("SELECT * " "FROM edxresult.course_total_data_v2 " "WHERE 統計日期 = %s order by course_id", [finalUpdate]) result = namedtuplefetchall(cursor) temp_site = 0 data = [] for rs in result: for i in range(len(teachersCourse)): if rs.course_id == teachersCourse[i]: data.clear() data.append(rs.課程代碼) data.append(rs.course_id) data.append(rs.course_name) data.append(rs.討論區討論次數) data.append(rs.討論區參與度) list_.append(data.copy()) # 回傳要呈現的資料 to_render['result'] = list_ # 預設日期範圍的下拉式選單,預設值為1:-請選擇- to_render['select'] = 1 to_render['finalUpdate'] = "最後資料更新時間 : " + finalUpdate to_render['IsLogin'] = 1 print('teacher') return render(request, '3_ForumData.html', to_render) else: print("student") to_render['IsLogin'] = 2 return render(request, '3_ForumData.html', to_render) else: print('not login') to_render['IsLogin'] = 2 return render(request, '3_ForumData.html', to_render) if request.method == 'POST': to_render = {} list_ = [] islogin = cookiegetter.isLogined(request) if islogin is True: userEmail = cookiegetter.getEmail(request) userID = cookiegetter.getUserIDByEmail(userEmail) if cookiegetter.isTeacher(userID): teachersCourse = cookiegetter.get_Teacher_Courses(userID) choose = request.POST.get('choose', None) # 使用者選擇範圍 S_startDate = request.POST.get('startDate', None) # 使用者選擇日期 D_userChooseStartDate = getChooseDate(choose, S_startDate) if choose is None: to_render['select'] = 1 to_render['selectStartDate'] = S_startDate else: to_render['select'] = choose to_render['selectStartDate'] = '年/月/日' with connections['ResultDB'].cursor() as cursor: # 取得統計日期,方式是取得最後一筆資料,此筆資料的統計日期就是最新日期 # 原Java版式搜尋course_total_data,但因為field name有問題因此改用course_total_data_v2 # 兩者間域名域名有些不同,如id -> course_id, 其餘包含.之域名全改為_ cursor.execute("select 統計日期 from course_total_data_v2") result = namedtuplefetchall(cursor) finalUpdate = result[-1].統計日期 # 依據使用者選擇的日期,取得資料 cursor.execute( "SELECT * " "FROM course_total_data_v2 " "WHERE 統計日期 = %s AND start_date > %s", [finalUpdate, D_userChooseStartDate] ) result = namedtuplefetchall(cursor) data = [] for rs in result: for i in range(len(teachersCourse)): if rs.course_id == teachersCourse[i]: data.clear() data.append(rs.課程代碼) data.append(rs.course_id) data.append(rs.course_name) data.append(rs.討論區討論次數) data.append(rs.討論區參與度) list_.append(data.copy()) to_render['result'] = list_ to_render['finalUpdate'] = '最後資料更新時間 : ' + finalUpdate to_render['IsLogin'] = 1 print('teacher') return render(request, '3_ForumData.html', to_render) else: to_render['IsLogin'] = 2 print('student') return render(request, '3_ForumData.html', to_render) else: to_render['IsLogin'] = 2 print('not login') return render(request, '3_ForumData.html', to_render)
def index_view(request): request.encoding = 'utf-8' to_render = {} # 各年度每個月分的上下底,搜尋統計用,假設要找四月人數,就要找在三月最後一天之後,及五月第一天之前 everyMonthTopAndBottomToSearch_2014 = [ "2014-04-30", "2014-05-01", "2014-05-31", "2014-06-01", "2014-06-30", "2014-07-01", "2014-07-31", "2014-08-01", "2014-08-31", "2014-09-01", "2014-09-30", "2014-10-01", "2014-10-31", "2014-11-01", "2014-11-30", "2014-12-01", "2014-12-31", "2015-01-01" ] everyMonthTopAndBottomToSearch_2015 = [ "2014-12-31", "2015-01-01", "2015-01-31", "2015-02-01", "2015-02-28", "2015-03-01", "2015-03-31", "2015-04-01", "2015-04-30", "2015-05-01", "2015-05-31", "2015-06-01", "2015-06-30", "2015-07-01", "2015-07-31", "2015-08-01", "2015-08-31", "2015-09-01", "2015-09-30", "2015-10-01", "2015-10-31", "2015-11-01", "2015-11-30", "2015-12-01", "2015-12-31", "2016-01-01" ] everyMonthTopAndBottomToSearch_2016 = [ "2015-12-31", "2016-01-01", "2016-01-31", "2016-02-01", "2016-02-29", "2016-03-01", "2016-03-31", "2016-04-01", "2016-04-30", "2016-05-01", "2016-05-31", "2016-06-01", "2016-06-30", "2016-07-01", "2016-07-31", "2016-08-01", "2016-08-31", "2016-09-01", "2016-09-30", "2016-10-01", "2016-10-31", "2016-11-01", "2016-11-30", "2016-12-01", "2016-12-31", "2017-01-01" ] everyMonthTopAndBottomToSearch_2017 = [ "2016-12-31", "2017-01-01", "2017-01-31", "2017-02-01", "2017-02-28", "2017-03-01", "2017-03-31", "2017-04-01", "2017-04-30", "2017-05-01", "2017-05-31", "2017-06-01", "2017-06-30", "2017-07-01", "2017-07-31", "2017-08-01", "2017-08-31", "2017-09-01", "2017-09-30", "2017-10-01", "2017-10-31", "2017-11-01", "2017-11-30", "2017-12-01", "2017-12-31", "2018-01-01" ] # 每年註冊人數總和 max_2014 = 0 max_2015 = 0 max_2016 = 0 max_2017 = 0 max_course = 0 # 最近兩週內課程登入人數排名 courseRecentLogin = [[None for i in range(4)] for i in range(10)] # 近一月內課程登入人數排名 courseLogin = [[None for i in range(4)] for i in range(10)] # 開課中課程 studyingCourse = 0 # 最近一年日期 recentYearDay = [] # 資料庫更新日 finalUpdate_course_total_data = None # 今日登入人數 todayLogin = 0 jsonArray_temp = [] if request.method == 'GET': select = request.GET.get('optradio', None) recent = request.GET.get('recent', None) recentRangeStart = request.GET.get('recentRangeStart', None) recentRangeEnd = request.GET.get('recentRangeEnd', None) Recentnow = datetime.now() # 存放每個月分的人數,openedu從2014年5月開始 numberOfPeopleEveryMonth_2014 = [0 for i in range(8)] numberOfPeopleEveryMonth_2015 = [0 for i in range(12)] numberOfPeopleEveryMonth_2016 = [0 for i in range(12)] numberOfPeopleEveryMonth_2017 = [0 for i in range(12)] request.GET = request.GET.copy() if select is None: to_render['recent'] = 7 to_render['select'] = 0 Recentnow = Recentnow + timedelta(days=1) to_render['recentRangeEnd'] = Recentnow.strftime('%Y-%m-%d') Recentnow = Recentnow + timedelta(days=-7) to_render['recentRangeStart'] = Recentnow.strftime('%Y-%m-%d') select = '0' recent = '7' elif select == '0': to_render['recent'] = recent to_render['select'] = select Recentnow = Recentnow + timedelta(days=1) to_render['recentRangeEnd'] = Recentnow.strftime('%Y-%m-%d') Recentnow = Recentnow + timedelta(days=-7) to_render['recentRangeStart'] = Recentnow.strftime('%Y-%m-%d') elif select == '1': to_render['recent'] = 7 to_render['select'] = select to_render['recentRangeStart'] = recentRangeStart to_render['recentRangeEnd'] = recentRangeEnd # 以下使用OpenEduDB try: ''' ------------2014------------ ''' # 搜尋2014年的註冊總人數 cursor = connections['test1'].cursor() # OpenEduDB cursor.execute( "SELECT id,date_joined FROM auth_user where date_joined < '2015-01-01' AND date_joined > '2014-05-01'" ) result = namedtuplefetchall(cursor) # 統計2014總註冊人數 max_2014 = len(result) # 該變數為存放所以2014年資料,並將資料存入 data_2014 = [] for i in range(max_2014): data_2014.append(result[i].date_joined) # count為跑迴圈用,month_20XX用來作為numberOfPeopleEveryMonth_20XX中的月份 count = 0 month_2014 = 0 # 判斷並計算每月人數 while count < max_2014: # 判斷是否在上個月最後一天之後,及下個月第一天之前 if data_2014[count] > everyMonthTopAndBottomToSearch_2014[month_2014*2] and data_2014[count] \ < everyMonthTopAndBottomToSearch_2014[month_2014*2+3]: # 若是的話,該月人數+1 numberOfPeopleEveryMonth_2014[ month_2014] = numberOfPeopleEveryMonth_2014[ month_2014] + 1 else: # 因為月份資料有經過排序,所以當有一筆資料不符合,就將月份+1 month_2014 = month_2014 + 1 numberOfPeopleEveryMonth_2014[month_2014] = 0 # count減1是因為不減的話會略過這筆資料 count = count + 1 ''' ------------2014------------ ''' ''' ------------2015------------ ''' # 搜尋2015年的註冊總人數 cursor.execute( "SELECT id,date_joined FROM auth_user where date_joined < '2016-01-01' AND date_joined > '2015-01-01'" ) result = namedtuplefetchall(cursor) # 統計2015總註冊人數 max_2015 = len(result) # 該變數為存放所以2015年資料,並將資料存入 data_2015 = [] for i in range(max_2015): data_2015 = data_2015.append(result[i].date_joined) # count為跑迴圈用,month_20XX用來作為numberOfPeopleEveryMonth_20XX中的月份 count = 0 month_2015 = 0 # 判斷並計算每月人數 while count < max_2015: # 判斷是否在上個月最後一天之後,及下個月第一天之前 if data_2015[count] > everyMonthTopAndBottomToSearch_2015[month_2015 * 2] and data_2015[count] \ < everyMonthTopAndBottomToSearch_2015[month_2015 * 2 + 3]: # 若是的話,該月人數+1 numberOfPeopleEveryMonth_2015[ month_2015] = numberOfPeopleEveryMonth_2015[ month_2015] + 1 else: # 因為月份資料有經過排序,所以當有一筆資料不符合,就將月份+1 month_2015 = month_2015 + 1 numberOfPeopleEveryMonth_2015[month_2015] = 0 # count減1是因為不減的話會略過這筆資料 count = count + 1 ''' ------------2015------------ ''' ''' ------------2016------------ ''' # 搜尋2016年的註冊總人數 cursor.execute( "SELECT id,date_joined FROM auth_user where date_joined < '2017-01-01' AND date_joined > '2016-01-01'" ) result = namedtuplefetchall(cursor) # 統計2016總註冊人數 max_2016 = len(result) # 該變數為存放所以2016年資料,並將資料存入 data_2016 = [] for i in range(max_2016): data_2016 = data_2016.append(result[i].date_joined) # count為跑迴圈用,month_20XX用來作為numberOfPeopleEveryMonth_20XX中的月份 count = 0 month_2016 = 0 # 判斷並計算每月人數 while count < max_2016: # 判斷是否在上個月最後一天之後,及下個月第一天之前 if data_2016[count] > everyMonthTopAndBottomToSearch_2016[month_2016 * 2] and data_2016[count] \ < everyMonthTopAndBottomToSearch_2016[month_2016 * 2 + 3]: # 若是的話,該月人數+1 numberOfPeopleEveryMonth_2016[ month_2016] = numberOfPeopleEveryMonth_2016[ month_2016] + 1 else: # 因為月份資料有經過排序,所以當有一筆資料不符合,就將月份+1 month_2016 = month_2016 + 1 numberOfPeopleEveryMonth_2016[month_2016] = 0 # count減1是因為不減的話會略過這筆資料 count = count + 1 ''' ------------2016------------ ''' ''' 2017年 ''' # 搜尋2017年的註冊總人數 cursor.execute( "SELECT id,date_joined FROM auth_user where date_joined < '2018-01-01' AND date_joined > '2017-01-01'" ) result = namedtuplefetchall(cursor) # 統計2017總註冊人數 max_2017 = len(result) # 該變數為存放所以2016年資料, 並將資料存入 data_2017 = [] for i in range(max_2017): data_2017 = data_2017.append(result[i].date_joined) # count為跑迴圈用, month_20XX用來作為numberOfPeopleEveryMonth_20XX中的月份 count = 0 month_2017 = 0 # 判斷並計算每月人數 while count < max_2017: # 判斷是否在上個月最後一天之後,及下個月第一天之前 if data_2017[count] > everyMonthTopAndBottomToSearch_2017[month_2017 * 2] and data_2017[count] \ < everyMonthTopAndBottomToSearch_2017[month_2017 * 2 + 3]: # 若是的話,該月人數+1 numberOfPeopleEveryMonth_2017[ month_2017] = numberOfPeopleEveryMonth_2017[ month_2017] + 1 else: # 因為月份資料有經過排序, 所以當有一筆資料不符合, 就將月份+1 month_2017 = month_2017 + 1 numberOfPeopleEveryMonth_2017[month_2017] = 0 # count減1是因為不減的話會略過這筆資料 count = count + 1 ''' 2017年 ''' ''' 今日登入人數 ''' now = datetime.today() now = now.strftime('%Y-%m-%d') cursor.execute( "SELECT count(*) as number FROM auth_user where last_login > %s", [now]) result = namedtuplefetchall(cursor) for rs in result: todayLogin = rs.number ''' 今日登入人數完''' cursor.close() except DatabaseError: pass try: # 顯示資料 cursor = connections['test1'].cursor() cursor.execute("select * from course_total_data_v2") result = namedtuplefetchall(cursor) finalUpdate_course_total_data = '' i = 0 maxRS = len(result) for rs in result: if i + 1 == maxRS - 1: finalUpdate_course_total_data = rs.統計日期 break i = i + 1 cursor.execute( "select * from course_total_data_v2 where 統計日期 = %s", [finalUpdate_course_total_data]) result = namedtuplefetchall(cursor) max_course = len(result) # 顯示資料 # ---------------------------------------- # ---------------------------------------- # 自學課程 now = datetime.today().strftime('%Y-%m-%d') cursor.execute( "SELECT * FROM course_total_data_v2 where start_date < %s and end_date > %s and 統計日期 = %s", [now, now, finalUpdate_course_total_data]) result = namedtuplefetchall(cursor) studyingCourse = len(result) # 自學課程 # ---------------------------------------- # 近一年登入數 loginUpdate = None cursor.execute("SELECT max(date) as A from edxresult.login_date") result = namedtuplefetchall(cursor) for rs in result: loginUpdate = rs.A now = datetime.today() longday = (now - datetime.strptime(loginUpdate, '%Y-%m-%d')).days now = now + timedelta(days=-longday) now = now + timedelta(days=-729) cursor.execute( "SELECT date,count(distinct(user_id)) as loginPeople FROM edxresult.login_date " "where date>= %s group by date", [now.strftime('%Y-%m-%d')]) result = namedtuplefetchall(cursor) # recentYearDay:最近一年日期, YearLogin:近一年登入數 recentYearDay = [None for i in range(730)] YearLogin = [0 for i in range(730)] day = 0 for i in range(len(result)): recentYearDay[day] = now.strftime('%Y-%m-%d') if result[i].date == now.strftime('%Y-%m-%d'): YearLogin[day] = result[i].loginPeople else: YearLogin[day] = 0 i = i - 1 print( str(day) + ' ' + str(recentYearDay[day]) + ' ' + str(YearLogin[day])) day = day + 1 now = now + timedelta(days=1) count = 0 jsonArray_temp = [] forloop = int(len(recentYearDay) / 2) while count < forloop: jsonObject = {} jsonObject['date'] = recentYearDay[count + 365] jsonObject['value'] = YearLogin[count] jsonObject['value2'] = YearLogin[count + 365] jsonArray_temp.append(jsonObject.copy()) count = count + 1 # jsonArray_temp = json.dumps(array) # 近一年登入人數 # ----------------------------- # 最近一個月內, 課程參與度排行 # courseLogin是用來存放近一個月內課程登入人數排名 i = 0 cursor.execute( "select course_name,course_id,`課程代碼`,login_count_month from course_total_data_v2 " "where 統計日期 = %s order by login_count_month desc", [finalUpdate_course_total_data]) result = namedtuplefetchall(cursor) for rs in result: if i < 10: courseLogin[i][0] = rs.course_name courseLogin[i][1] = rs.course_id courseLogin[i][2] = rs.login_count_month courseLogin[i][3] = rs.課程代碼 i = i + 1 else: break # ----------------------------- cursor.close() except DatabaseError: print('can\'t connect mysql database') to_render[ 'numberOfPeopleEveryMonth_2014'] = numberOfPeopleEveryMonth_2014 to_render[ 'numberOfPeopleEveryMonth_2015'] = numberOfPeopleEveryMonth_2015 to_render[ 'numberOfPeopleEveryMonth_2016'] = numberOfPeopleEveryMonth_2016 to_render[ 'numberOfPeopleEveryMonth_2017'] = numberOfPeopleEveryMonth_2017 to_render['registered_All'] = '{:,}'.format(max_2017 + max_2016 + max_2015 + max_2014) to_render['course_All'] = '{:,}'.format(max_course) to_render['studyingCourse'] = studyingCourse to_render['todayLogin'] = todayLogin to_render['recentYearLogin'] = json.dumps(jsonArray_temp) to_render['courseRecentLogin'] = courseRecentLogin to_render['courseLogin'] = courseLogin to_render[ 'finalUpdate_course_total_data'] = finalUpdate_course_total_data to_render['finalUpdate_recent_Year_Login'] = recentYearDay[ len(recentYearDay) - 1] return render(request, 'index-admin.html', to_render)