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)
