def validatecsv(file_path, unique_id):
needed_column = ['電話', '教授姓名', 'email', '研究領域', '簡介', '教授id']
record_status = 1
validate_flag = True
df1 = pd.read_csv(file_path, dtype=str)
csv_column = df1.keys().tolist()
all_include = set(needed_column).issubset(csv_column)
if all_include == False:
record_status = 0
err_col = str(set(needed_column) - set(csv_column))
message = "錯誤：名稱有誤：" + err_col
validate_flag = False
checkFile.recordLog(unique_id, record_status, message, mycursor,
connection)
return validate_flag
df1['是否退休'] = 0
os.remove(file_path)
df1.to_csv(file_path, index=False, encoding='utf-8')
df2 = pd.DataFrame(columns=['教授id', '教授姓名'])
df2['教授id'] = df1['教授id']
df2['教授姓名'] = df1['教授姓名']
output_path = os.getcwd() + "/temp_teacher.csv"
df2.to_csv(output_path, index=False, encoding='utf-8')
return output_path, validate_flag
def validateCSV(file_path, unique_id):
needed_column = ['學號', '姓名', '班級', '性別', '入學身份', '在學身份', '在學狀況', 'eMail']
record_status = 1
validate_flag = True
df = pd.read_csv(file_path, dtype={'學號': object})
csv_column = df.keys().tolist()
all_include = set(needed_column).issubset(csv_column)
if all_include == False:
record_status = 0
error_column = str(set(needed_column) - set(csv_column))
message = "錯誤：名稱有誤 : " + error_column
validate_flag = False
checkFile.recordLog(unique_id, record_status, message, mycursor,
connection)
return validate_flag
#check student length == 7 or 9
student_len = [7, 9]
df['student_id_len'] = df['學號'].str.len()
student_id_check = all(_ in student_len
for _ in list(df['student_id_len']))
if student_id_check == False:
error_count = len(df[~df['student_id_len'].isin(student_len)])
message = "錯誤：學號長度有誤 (可能為開頭缺少0) 共 : " + str(error_count) + "筆"
validate_flag = False
checkFile.recordLog(unique_id, record_status, message, mycursor,
connection)
return validate_flag
return validate_flag
def validatecsv(file_path, unique_id):
needed_column = ['學號', '身分', '考試年度', '考試年級', '英檢狀態代碼']
record_status = 1
validate_flag = True
df = pd.read_csv(file_path)
csv_column = df.keys().tolist()
all_include = set(needed_column).issubset(csv_column)
if all_include == False:
record_status = 0
err_col = str(set(needed_column) - set(csv_column))
message = "錯誤：名稱有誤：" + err_col
validate_flag = False
checkFile.recordLog(unique_id, record_status, message, mycursor,
connection)
return validate_flag
return validate_flag
def ValidateCSV(file_path, unique_id, mycursor, connection):
needed_column = ['學號', '學期', '專題一或二']
record_status = 1
validate_flag = True
df = pd.read_csv(file_path, encoding='utf-8')
csv_column = df.keys().tolist()
all_include = set(needed_column).issubset(csv_column)
if all_include == False:
record_status = 0
err_col = str(set(needed_column) - set(csv_column))
message = "錯誤：名稱有誤：" + err_col
validate_flag = False
checkFile.recordLog(unique_id, record_status, message, mycursor,
connection)
return validate_flag
return validate_flag
def preValidateCSV(file_path, unique_id, mycursor, connection):
#since score has NaN, so its type will be float64
needed_column = ['學號', '班別', '姓名', '學期', '當期課號', '永久課號', '課名',\
'學分數', '開課單位', '選別', '摘要', '評分方式', '成績', '備註', 'GP']
record_status = 1
validate_flag = True
df = pd.read_csv(file_path, dtype={'學號': object, '當期課號': object})
csv_column = df.keys().tolist()
all_include = set(needed_column).issubset(csv_column)
if all_include == False:
record_status = 0
error_column = str(set(needed_column) - set(csv_column))
message = "錯誤：名稱有誤 : " + error_column
validate_flag = False
checkFile.recordLog(unique_id, record_status, message, mycursor,
connection)
return validate_flag
def validateCSV(file_path, unique_id):
needed_column = [
'學號', '申請年度', '申請學期', '修課年度', '修課學期', '修課當期課號', '修課課名', '欲抵免免修永久課號',
'欲抵免免修課名', '學分數', '抵免或免修', '原課程向度', '學生選別'
]
record_status = 1
validate_flag = True
df = pd.read_csv(file_path, encoding='utf-8')
csv_column = df.keys().tolist()
all_include = set(needed_column).issubset(csv_column)
if all_include == False:
record_status = 0
err_col = str(set(needed_column) - set(csv_column))
message = "錯誤：名稱有誤：" + err_col
validate_flag = False
checkFile.recordLog(unique_id, record_status, message, mycursor,
connection)
return validate_flag
return validate_flag
def validateCSV(file_path, unique_id, mycursor, connection):
#since score has NaN, so its type will be float64
needed_column = [
'學號', '學期', '當期課號', '永久課號', '課名', '學分數', '開課單位', '選別', '評分方式', '評分狀態',
'成績', 'GP'
]
record_status = 1
validate_flag = True
df = pd.read_csv(file_path, dtype={'學號': object, '當期課號': object})
csv_column = df.keys().tolist()
all_include = set(needed_column).issubset(csv_column)
if all_include == False:
record_status = 0
error_column = str(set(needed_column) - set(csv_column))
message = "錯誤：名稱有誤 : " + error_column
validate_flag = False
checkFile.recordLog(unique_id, record_status, message, mycursor,
connection)
return validate_flag
#check cos_id length == 4
cos_len = 4
df['cos_id_len'] = df['當期課號'].str.len()
cos_id_check = list(df['cos_id_len'] != cos_len)
if True in cos_id_check:
record_status = 0
error_cos_id_count = len(
df[df['cos_id_len'] != cos_len]['當期課號'].values)
message = "錯誤：當期課號長度有誤 (可能為開頭缺少0) 共 : " + str(error_cos_id_count) + "筆"
validate_flag = False
checkFile.recordLog(unique_id, record_status, message, mycursor,
connection)
return validate_flag
#check student length == 7 or 9
student_len = [7, 9]
df['student_id_len'] = df['學號'].str.len()
student_id_check = all(_ in student_len
for _ in list(df['student_id_len']))
if student_id_check == False:
error_count = len(df[~df['student_id_len'].isin(student_len)])
message = "錯誤：學號長度有誤 (可能為開頭缺少0) 共 : " + str(error_count) + "筆"
validate_flag = False
checkFile.recordLog(unique_id, record_status, message, mycursor,
connection)
return validate_flag
return validate_flag
validate_flag = preValidateCSV(file_path, unique_id, mycursor, connection)
if validate_flag:
# Parse csv to fit format
convertData(file_path, csv_path, year, semester)
#Check csv file
validate_flag = validateCSV(csv_path, unique_id, mycursor, connection)
if validate_flag == True:
#Delete last semester's on cos data
record_status, code, message, affect_count = deleteLastCourse(
mycursor, connection)
if record_status == 0:
message = "刪除上學期課程錯誤：" + message
checkFile.recordLog(unique_id, record_status, message, mycursor,
connection)
elif record_status == 1:
#Import this semester's on cos data
record_status, code, message, affect_count = insertDB(
csv_path, mycursor, connection)
if record_status == 0:
message = "匯入當期課程錯誤：" + message
checkFile.recordLog(unique_id, record_status, message,
mycursor, connection)
if record_status == 1:
message = "已匯入本學期當期課程共 " + str(affect_count) + ' 筆'
checkFile.recordLog(unique_id, record_status, message, mycursor,
connection)
mycursor.close() ## here all loops done
connection.close() ## close db connection
def convert_coscode(file_path, mycursor, connection, unique_id):
df = pd.read_csv(file_path,
encoding='utf-8',
dtype={
'學號': object,
'修課年度': object,
'修課學期': object,
'修課當期課號': object
})
# insert "修課永久課號"
col_name = df.columns.tolist()
col_name.insert(6, "修課永久課號")
df = df.reindex(columns=col_name)
year = df['修課年度'].tolist()
semester = df['修課學期'].tolist()
code = df['修課當期課號'].tolist()
offset_type = df['抵免或免修'].tolist()
cos_code_old = df['修課永久課號'].tolist()
for i in range(len(year)):
if (offset_type[i] == '抵免'):
continue
elif (offset_type[i] == '免修'):
year[i] = str(year[i]).replace('.0', '') # 以防裡面有轉換失敗產生的小數
item = str(year[i]) + '-' + str(semester[i]) + '-' + str(code[i])
mycursor.execute(
"select cos_code from cos_name where unique_id = '%s';" % item)
tmp = mycursor.fetchall()
if tmp != ():
cos_code_old[i] = tmp[0][0]
else:
msg = item + " 沒有找到修課永久課號"
print(msg)
df['修課永久課號'] = cos_code_old
df['修課年度'] = year
try:
df['欲抵免免修課名'] = df['欲抵免免修課名'].astype(str).str.replace(
'院基本素養', '外院基本能力')
df['欲抵免免修課名'] = df['欲抵免免修課名'].astype(str).str.replace('校基本素養', '校基本能力')
df['原課程向度'] = df['原課程向度'].astype(str).str.replace('跨院基本素養', '外院基本能力')
df['原課程向度'] = df['原課程向度'].astype(str).str.replace('校基本素養', '校基本能力')
df['原課程向度'] = df['原課程向度'].astype(str).str.replace('nan', '')
col = [
'學號', '申請年度', '申請學期', '修課年度', '修課學期', '修課當期課號', '修課永久課號', '修課課名',
'欲抵免免修永久課號', '欲抵免免修課名', '學分數', '抵免或免修', '原課程向度', '學生選別'
]
df = df[col]
except Exception as e:
record_status = 0
msg = '錯誤：欄位資料可能有錯，'
msg += str(e)
checkFile.recordLog(unique_id, record_status, msg, mycursor,
connection)
exit(0)
output_path = os.getcwd() + "/temp_offset.csv"
df.to_csv(output_path, index=False, encoding='utf-8')
return output_path
def convertData(file_path, output_path, selected_year, selected_semester,
unique_id, mycursor, connection):
validate_flag = True
record_status = 1
df = pd.read_csv(file_path, dtype={'學號': object, '當期課號': object})
concat_year_semester = str(selected_year) + str(selected_semester)
df = df[df['學期'] != int(concat_year_semester)].reset_index(drop=True)
df_parse = df.copy()
df_parse = df_parse.drop(columns=['班別', '姓名', '摘要', '備註'])
# Processing to fit table columns
year = []
semester = []
brief = []
pass_fail = []
score_level = []
for i in range(len(df)):
year.append(df['學期'][i] // 10)
semester.append(df['學期'][i] % 10)
brief.append(df['摘要'][i])
# Parse score level by score
if df['成績'][i].isnumeric():
if int(df['成績'][i]) >= 90 and int(df['成績'][i]) <= 100:
score_level.append('A+')
elif int(df['成績'][i]) >= 85 and int(df['成績'][i]) <= 89:
score_level.append('A')
elif int(df['成績'][i]) >= 80 and int(df['成績'][i]) <= 84:
score_level.append('A-')
elif int(df['成績'][i]) >= 77 and int(df['成績'][i]) <= 79:
score_level.append('B+')
elif int(df['成績'][i]) >= 73 and int(df['成績'][i]) <= 76:
score_level.append('B')
elif int(df['成績'][i]) >= 70 and int(df['成績'][i]) <= 72:
score_level.append('B-')
elif int(df['成績'][i]) >= 67 and int(df['成績'][i]) <= 69:
score_level.append('C+')
elif int(df['成績'][i]) >= 63 and int(df['成績'][i]) <= 66:
score_level.append('C')
elif int(df['成績'][i]) >= 60 and int(df['成績'][i]) <= 62:
score_level.append('C-')
elif int(df['成績'][i]) >= 50 and int(df['成績'][i]) <= 59:
score_level.append('D')
elif int(df['成績'][i]) >= 1 and int(df['成績'][i]) <= 49:
score_level.append('E')
elif int(df['成績'][i]) == 0:
score_level.append('X')
else:
message = "錯誤：成績為數字但不在0~100分，第 : " + str(i + 1) + "筆"
validate_flag = False
record_status = 0
checkFile.recordLog(unique_id, record_status, message,
mycursor, connection)
return validate_flag
else:
if df['評分方式'][i] == '通過不通過' or df['評分方式'][i] == '等級' or (
df['評分方式'][i] == '百分'):
score_level.append(None)
else:
message = "錯誤：成績不為數字且評分方式不為通過不通過、等級或百分，第 : " + str(i + 1) + "筆"
validate_flag = False
record_status = 0
checkFile.recordLog(unique_id, record_status, message,
mycursor, connection)
return validate_flag
# Parse pass fail by score
if df['成績'][i].isnumeric():
if int(df['成績'][i]) >= 60:
pass_fail.append('通過')
else:
pass_fail.append('不通過')
elif df['成績'][i] == 'A+' or df['成績'][i] == 'A' or df['成績'][i] == 'A-' or \
df['成績'][i] == 'B+' or df['成績'][i] == 'B' or df['成績'][i] == 'B-' or \
df['成績'][i] == 'C+' or df['成績'][i] == 'C' or df['成績'][i] == 'C-':
pass_fail.append('通過')
df_parse['成績'][i] = None
elif df['成績'][i] == 'D' or df['成績'][i] == 'E' or df['成績'][i] == 'X':
pass_fail.append('不通過')
df_parse['成績'][i] = None
elif df['成績'][i] == 'P':
pass_fail.append('通過')
df_parse['成績'][i] = None
elif df['成績'][i] == 'F':
pass_fail.append('不通過')
df_parse['成績'][i] = None
elif df['成績'][i] == 'W':
pass_fail.append('W')
df_parse['成績'][i] = None
elif df['成績'][i] == 'Y':
pass_fail.append('Y')
else:
message = "錯誤：成績不為數字且不為訂定標準(A~F、W、Y等)，第 : " + str(i + 1) + "筆"
validate_flag = False
record_status = 0
checkFile.recordLog(unique_id, record_status, message, mycursor,
connection)
return validate_flag
df_parse['學年度'] = year
df_parse['學期'] = semester
df_parse['課程向度'] = brief
df_parse['評分狀態'] = pass_fail
df_parse['等級成績'] = score_level
# Swap columns
columns_order = [
'學號', '學年度', '學期', '當期課號', '開課單位', '課名', '永久課號', '課程向度', '選別', '學分數',
'評分方式', '評分狀態', '成績', '等級成績', 'GP'
]
cols = list(df.columns)
df_parse = df_parse[columns_order]
df_parse.to_csv(output_path, index=False, encoding='utf-8')
return validate_flag
