def upload_file(store_folder, file, year=None):
if not os.path.isdir(store_folder):
os.makedirs(store_folder)
if year is not None:
name = str(year) + "_" + str(
datetime.now().strftime("%d-%m-%Y")) + "_" + file.filename
else:
name = str(datetime.now().strftime("%d-%m-%Y")) + "_" + file.filename
filename = secure_filename(name)
destination = "/".join([store_folder, filename])
try:
file.save(destination)
out = inner_res_helper.make_inner_response(response=True,
message="File was Saved",
value=destination)
except Exception as e:
out = inner_res_helper.make_inner_response(response=False,
message=str(e),
value=str(e.args[0]))
print(e)
return out
def alumni_delete_survey(self, key: str = None):
db_ref = db.reference('alumni_survey/%s' % key)
try:
db_ref.delete()
except Exception as e:
print("Error %d: %s" % (e.args[0], e.args[1]))
return inner_res_helper.make_inner_response(False, str(e.args[0]), str(e.args[1]))
return inner_res_helper.make_inner_response(True, "Delete data successful", "Success")
def read_table_header(sheet_url: str):
# this function read only first sheet in wokbook
sheet = gc.open_by_url(sheet_url)
worksheet = sheet.get_worksheet(0)
header = worksheet.row_values(1)
if header is None:
return inner_res_helper.make_inner_response(
response=False, message="Cannot find table head", value=header)
return inner_res_helper.make_inner_response(response=True,
message="Table header",
value=header)
def alumni_update_survey(self, key: str = None, data=None):
db_ref = db.reference('alumni_survey')
try:
data_ref = db_ref.child(key)
data_ref.update(data)
except Exception as e:
print("Error %d: %s" % (e.args[0], e.args[1]))
return inner_res_helper.make_inner_response(False, str(e.args[0]), str(e.args[1]))
return inner_res_helper.make_inner_response(True, "Update data successful", "Success")
def read_sheet_data(sheet_url: str):
sheet = gc.open_by_url(sheet_url)
worksheet = sheet.get_worksheet(0)
read = worksheet.get_all_values()
if read is None:
return inner_res_helper.make_inner_response(
response=False, message="Cannot find table", value=read)
return inner_res_helper.make_inner_response(response=True,
message="Table data",
value=read)
def alumni_get_survey(self):
db_ref = db.reference('alumni_survey')
try:
snapshot = db_ref.get()
except Exception as e:
print("Error %d: %s" % (e.args[0], e.args[1]))
return inner_res_helper.make_inner_response(False, str(e.args[0]), str(e.args[1]))
if snapshot is not None:
return inner_res_helper.make_inner_response(True, "Query successful", [dict(snapshot)])
else:
return inner_res_helper.make_inner_response(False, "Can not found data", "Can not found data")
def alumni_get_survey_by_year(self, year: int):
year = int(year)
db_ref = db.reference('alumni_survey')
try:
snapshot = db_ref.order_by_child("educationYear").equal_to(year).get()
except Exception as e:
print(e)
return inner_res_helper.make_inner_response(False, "Error", e)
if snapshot is not None:
return inner_res_helper.make_inner_response(True, "Query successful", [dict(snapshot)])
else:
return inner_res_helper.make_inner_response(False, "Can not found data", "Can not found data")
def student_tracking(self, id_student):
connect = DatabaseHelper()
data = connect.get_student_tracking(id_student)
value = {}
if data['value']:
df = pd.DataFrame(data['value'])
if (not df.gpa.empty) & (not df.current_gpax.empty):
df = df.sort_values(by=['education_year', 'semester'])
df_drop_s = df[df['semester'] != 'S']
df_drop_s.reset_index(inplace=True)
df_tracking = df_drop_s.gpa
value = {
'student_id': id_student,
'firstname': df_drop_s.loc[0, 'firstname'],
'lastname': df_drop_s.loc[0, 'lastname'],
'gpax': df_drop_s.current_gpax.loc[0],
'trackking': df_tracking.to_dict()
}
response = True
message = "Analyze Subject Successfully"
else:
response = False
message = "Don't have Data"
else:
response = False
message = "Don't have Data"
return inner_res_helper.make_inner_response(response, message, value)
def read_activity_participant(self, file_location, activity_id):
try:
participant_data = pd.read_excel(file_location)
participant_data['activity_id'] = activity_id
cols = participant_data.columns.tolist()
cols = cols[-1:] + cols[:-1]
participant_data = participant_data[cols]
except Exception as e:
print(e)
return inner_res_helper.make_inner_response(
False, "Error", "Having problem when prepare data.")
out_data = {
'participant_data': participant_data.to_json(orient='index')
}
return inner_res_helper.make_inner_response(
True, "Data for insert to database.", out_data)
def alumni_add_survey(self, year: int = None, url: str = None, table_header: list = None, personal_header: list = None):
year = int(year)
data = {
"educationYear": year,
"tableHeader": table_header,
"sheetUrl": url,
"personalHeader": personal_header
}
db_ref = db.reference('alumni_survey')
try:
db_ref.push(data)
except Exception as e:
print("Error %d: %s" % (e.args[0], e.args[1]))
return inner_res_helper.make_inner_response(False, str(e.args[0]), str(e.args[1]))
return inner_res_helper.make_inner_response(True, "Add data successful", "Success")
def read_sheet_data_by_column(sheet_url: str, header: list):
sheet = gc.open_by_url(sheet_url)
worksheet = sheet.get_worksheet(0)
sheet_header = read_table_header(sheet_url)
if not sheet_header['response']:
return header
sheet_header = sheet_header['value']
column_index = []
count = 1
for name in sheet_header:
if name in header:
column_index.append(count)
count += 1
df = pd.DataFrame()
count = 0
max_row = len(worksheet.col_values(1))
for index in column_index:
read = worksheet.col_values(index)
if len(read) < max_row:
for i in range(0, (max_row - len(read))):
read.append("")
df[count] = read
count += 1
df.columns = df.iloc[0]
df = df[1:]
if df is None:
return inner_res_helper.make_inner_response(
response=False, message="Cannot find table", value=df)
return inner_res_helper.make_inner_response(response=True,
message="Table data",
value=df)
def analyze_survey(self, sheet_url, column):
# read and analyze survey data
read = read_sheet.read_sheet_data(sheet_url)
if not read['response']:
return inner_res_helper.make_inner_response(response=True,
message="Developing",
value="Developing")
data = read['value']
header = data[0]
data = data[1:]
select_column = list(column)
data = pd.DataFrame(data, columns=header)
data = data.loc[:, select_column].apply(pd.to_numeric)
mean = pd.Series(data.mean(), name="mean")
sd = pd.Series(data.std(), name="std")
df = pd.concat([mean, sd], axis=1)
return inner_res_helper.make_inner_response(
response=True,
message="Developing",
value=[df.round(2).to_dict('index')])
def subject_by_branch(self, branch_id, semester, education_year):
connect = DatabaseHelper()
data = connect.subject_by_branch(branch_id, semester, education_year)
value = {}
if data['value']:
df = pd.DataFrame(data['value'])
list_grade = {
'A': 4,
'B+': 3.5,
'B': 3,
'C+': 2.5,
'C': 2,
'D+': 1.5,
'D': 1,
'F': 0,
'Fe': 0
}
df['grade'].replace(list_grade, inplace=True)
df = df[df['grade'] != 'W']
df['grade'] = df.grade.astype(int)
grouped = df.groupby(
["subject_code", "subject_name_en",
"subject_weigth"])["grade"].agg(['size', 'mean', 'std'])
grouped = grouped.reset_index(
level=['subject_name_en', 'subject_weigth'])
grouped = grouped.round(2)
value = {
'subject': grouped.to_dict('index'),
}
response = True
message = "Analyze Subject Successfully"
else:
response = False
message = "Don't have Data"
return inner_res_helper.make_inner_response(response, message, value)
def analyze_publicize(self, year=None):
connect = DatabaseHelper()
data = connect.get_activity_publicize(year)
if data['value']:
data = pd.DataFrame(data['value'])
activity = analyze_helper.set_fullname(connect.get_activity_list())
if year:
year_s = int(year)
activity = activity.loc[(activity['education_year'] == year_s)
|
(activity['education_year'] == year_s -
1)]
dupli_activity = activity.activity_name.duplicated(keep=False)
activity.loc[dupli_activity, 'activity_name'] = activity.loc[dupli_activity, 'activity_name'] + ' (' + \
activity['education_year'].astype(str) + ')'
count_duplicate = 1
for i in activity.loc[dupli_activity, 'activity_name'].index:
activity.loc[i, 'activity_name'] = activity.loc[
i, 'activity_name'] + " " + str(count_duplicate)
count_duplicate = count_duplicate + 1
activityNoAr = activity[activity['project_type'] == 0]
activity_data = activityNoAr.drop(columns='project_type')
activityNoAr = activityNoAr[['activity_name']]
activity_dict = analyze_helper.set_dict(activityNoAr.index,
activityNoAr.activity_name)
data_year = data.copy()
year_select = []
if year:
year = int(year)
data_year = data[data['activity_year'] == year]
year_select.append(int(year))
year_select.append(int(year - 1))
activity_data = activity_data[activity_data['education_year']
== year]
else:
max_year = data.activity_year.max()
year_select.append(int(max_year))
year_select.append(int(max_year - 1))
activity_data.set_index(['activity_name'], inplace=True)
# data_compare is data all person who joined activity not ar
data_compare = data[data['activity_year'].isin(year_select)]
# data_student_compare is data all person who joined activity not ar and studied in KMUTT
data_student_join = data_compare.dropna()
compare_year = data_compare.groupby(
['activity_id', 'activity_year']).size().unstack(fill_value=0)
# student_joined = data_student_join.groupby(['activity_id', 'activity_year']).size().unstack(fill_value=0)
if not compare_year.empty:
compare_year_data = analyze_helper.check_list(
activityNoAr.index, compare_year)
compare_year_data = analyze_helper.check_list_column(
year_select, compare_year_data)
compare_year_data = analyze_helper.set_fullname_index(
activity_dict, compare_year_data)
# student_joined_compare = analyze_helper.check_list(activityNoAr.index, student_joined)
# student_joined_compare = analyze_helper.check_list_column(year_select, student_joined_compare)
# student_joined_compare = analyze_helper.set_fullname_index(activity_dict, student_joined_compare)
else:
compare_year_data = pd.DataFrame(columns=year_select)
activity_name = activityNoAr.activity_name.tolist()
compare_year_data['activity_name'] = activity_name
compare_year_data.fillna(0, inplace=True)
compare_year_data.set_index('activity_name', inplace=True)
# student_joined_compare = compare_year_data.copy()
activity_group = data_year.groupby('activity_id').size()
activity_count_check = analyze_helper.check_list(
activityNoAr.index, activity_group)
activity_count = analyze_helper.set_fullname_index(
activity_dict, activity_count_check)
student_join = data_student_join.groupby('activity_id').size()
student_join_check = analyze_helper.check_list(
activityNoAr.index, student_join)
student_join_count = analyze_helper.set_fullname_index(
activity_dict, student_join_check)
budget = activity.loc[(activity['education_year'] == year)]
value = {
'activity_count': activity_count.to_dict(),
# 'student_join_count': student_join_count.to_dict(),
'activity_year_compare': compare_year_data.to_dict(),
# 'student_joined_compare': student_joined_compare.to_dict(),
'budget': activity_data.to_dict('index')
}
response = True
message = "Analyze Successfully"
else:
value = {}
response = False
message = "Don't have Data"
return inner_res_helper.make_inner_response(response=response,
message=message,
value=value)
def analyze_project_ar(self, year=None):
connect = DatabaseHelper()
data = connect.get_activity_ar(year)
if data['value']:
data = pd.DataFrame(data['value'])
project = analyze_helper.set_fullname(connect.get_project_list())
project = project[project['project_type'] == 1]
project.drop(columns=['project_type'], inplace=True)
project_dict = analyze_helper.set_dict(project.index,
project.project_name)
# get_branch=connect.get_department(None)
# branch=[]
# for i in get_branch['value']:
# for index in range(len(i['branch'])):
# branch.append(i['branch'][index])
# branch_data = analyze_helper.set_branch(branch)
# branch_data.drop(columns=['amount_student'],inplace=True)
# branch_dict = analyze_helper.set_dict(branch_data.index, branch_data.branch_name)
get_branch = connect.get_department_ds()
get_branch = pd.DataFrame(get_branch['value'])
branch_data = get_branch[['branch_id', 'branch_name']]
branch_data = branch_data.set_index('branch_id')
branch_dict = analyze_helper.set_dict(branch_data.index,
branch_data.branch_name)
list_project = project.index.tolist()
project_set = []
i = 0
for pindex in list_project:
df = data[data['project_id'] == pindex]
name_project = project_dict[pindex]
if not df.empty:
analyze_by_activity = df.groupby(['branch_name']).size()
# analyze_by_activity = analyze_helper.set_fullname_index(branch_dict, analyze_by_activity)
analyze_by_activity_gpax = df.groupby(['branch_name'
])['gpax'].mean()
# analyze_by_activity_gpax = analyze_helper.set_fullname_index(branch_dict, analyze_by_activity_gpax)
analyze_by_activity_gpax = analyze_by_activity_gpax.round(
2)
list_pr = {
'project_name':
name_project,
'analyze_by_activity':
analyze_by_activity.to_dict(),
'analyze_by_activity_gpax':
analyze_by_activity_gpax.to_dict()
}
project_set.append(list_pr)
else:
list_branch = branch_data.branch_name.tolist()
df_empty = pd.DataFrame(list_branch,
columns=['branch_name'])
df_empty['value'] = 0
df_empty.set_index('branch_name', inplace=True)
list_pr = {
'project_name': name_project,
'analyze_by_activity': df_empty.value.to_dict(),
'analyze_by_activity_gpax': df_empty.value.to_dict()
}
project_set.append(list_pr)
i = i + 1
value = {
'project_set': project_set,
}
response = True
message = "Analyze Successfully"
else:
value = {}
response = False
message = "Don't have Data"
return inner_res_helper.make_inner_response(response=response,
message=message,
value=value)
def analyze_ar(self, year=None):
connect = DatabaseHelper()
data = connect.get_activity_ar(year)
if data['value']:
data = pd.DataFrame(data['value'])
activity = analyze_helper.set_fullname(connect.get_activity_list())
dupli_activity = activity.activity_name.duplicated(keep=False)
activity.loc[dupli_activity, 'activity_name'] = activity.loc[dupli_activity, 'activity_name'] + ' (' + \
activity['education_year'].astype(str) + ')'
activityAr = activity[activity['project_type'] == 1]
activity_data = activityAr.drop(columns='project_type')
activityAr = activityAr[['activity_name']]
activity_dict = analyze_helper.set_dict(activityAr.index,
activityAr.activity_name)
# get_branch=connect.get_department(None)
# branch=[]
# for i in get_branch['value']:
# for index in range(len(i['branch'])):
# branch.append(i['branch'][index])
# branch_data = analyze_helper.set_branch(branch)
# branch_data.drop(columns=['amount_student'],inplace=True)
# branch_dict = analyze_helper.set_dict(branch_data.index, branch_data.branch_name)
get_branch = connect.get_department_ds()
get_branch = pd.DataFrame(get_branch['value'])
branch_data = get_branch[['branch_id', 'branch_name']]
branch_data = branch_data.set_index('branch_id')
branch_dict = analyze_helper.set_dict(branch_data.index,
branch_data.branch_name)
# activity_data keep data activity such as activity_year and activity_budget
# activity_dict keep data activity such as activity_year and activity_budget in syntex dic
count_school = data.school_name.value_counts()
sort_count_school = count_school.sort_values(
ascending=False).head()
gpax_school = data.groupby(['school_name'])['gpax'].mean()
sort_gpax_school = gpax_school.sort_values(ascending=False).head()
analyze_by_activity = data.groupby(['project_id', 'branch_name'
]).size().unstack(fill_value=0)
# analyze_by_activity = analyze_helper.check_list(activityAr.index, analyze_by_activity)
# analyze_by_activity = analyze_helper.set_fullname_column(branch_dict, analyze_by_activity)
# analyze_by_activity = analyze_helper.set_fullname_index(activity_dict, analyze_by_activity)
analyze_by_activity_gpax = data.groupby(
['project_id',
'branch_name'])['gpax'].mean().unstack(fill_value=0)
# analyze_by_activity_gpax = analyze_helper.check_list(activityAr.index, analyze_by_activity_gpax)
# analyze_by_activity_gpax = analyze_helper.set_fullname_index(activity_dict, analyze_by_activity_gpax)
# analyze_by_activity_gpax = analyze_helper.set_fullname_column(branch_dict, analyze_by_activity_gpax)
analyze_by_activity_gpax = analyze_by_activity_gpax.round(2)
value = {
'count_school':
sort_count_school.to_dict(),
'gpax':
sort_gpax_school.to_dict(),
'activity_by_branch_count':
[analyze_by_activity.to_dict('index')],
'activity_by_branch_gpax':
[analyze_by_activity_gpax.to_dict('index')]
}
response = True
message = "Analyze Successfully"
else:
value = {}
response = False
message = "Don't have Data"
return inner_res_helper.make_inner_response(response=response,
message=message,
value=value)
def analyze_admission_admin(self, year=None):
connect = DatabaseHelper()
data = connect.get_all_admission_admin(year)
value = {}
if data['value']:
df = pd.DataFrame(data['value'])
df = df[df['admission_year'] == int(year)]
if not df.empty:
branch = connect.get_branch()
branch_data = analyze_helper.set_branch(branch['value'])
branch_dict = analyze_helper.set_dict(branch_data.index,
branch_data.branch_name)
channel_data = analyze_helper.set_fullname(
connect.get_admission_channel())
channel_sample = self.split_channel(channel_data)
data_split = self.split_channel(df)
channel_round = channel_sample.channel_round.drop_duplicates(
).to_list()
analyze_by_round = []
for i in channel_round:
analyze_by_round_s = {}
data_in_round = data_split[data_split['channel_round'] ==
i]
if data_in_round.empty:
data_channel_sample = channel_sample[
channel_sample['channel_round'] == i]
data_group = data_channel_sample.groupby(
['channel_round',
'channel_name']).size().unstack(fill_value=0)
data_group.iloc[:] = 0
data_group.reset_index(inplace=True)
name = data_group.iloc[0, 0]
data_group = data_group.drop(columns=['channel_round'])
analyze_by_round_s['name'] = name
analyze_by_round_s['analyze'] = data_group.to_dict(
'index')
else:
data_group = data_in_round.groupby(
['channel_round',
'channel_name']).size().unstack(fill_value=0)
channel_sample_selector = (channel_sample[
channel_sample['channel_round'] == i])
data_group_check_channel = analyze_helper.check_list_column(
channel_sample_selector.channel_name, data_group)
data_group_check_channel.reset_index(inplace=True)
name = data_group_check_channel.iloc[0, 0]
data_group_check_channel = data_group_check_channel.drop(
columns=['channel_round'])
analyze_by_round_s['name'] = name
analyze_by_round_s[
'analyze'] = data_group_check_channel.to_dict(
'index')
analyze_by_round.append(analyze_by_round_s)
value = {'analyze_by_round': analyze_by_round}
response = True
message = "Analyze Successfully"
else:
value = {}
response = False
message = "Don't have Data"
else:
value = {}
response = False
message = "Don't have Data"
return inner_res_helper.make_inner_response(response=response,
message=message,
value=value)
def read_admission(self, channel, year, file_location):
df = pd.read_excel(file_location,
converters={
'เลขที่ใบสมัคร': str,
'รหัสสถานศึกษา': str
})
try:
df = df.loc[1:, [
'เลขที่ใบสมัคร', 'คำนำหน้านาม(ไทย)', 'ชื่อ(ไทย)',
'นามสกุล(ไทย)', 'GPAX', 'รหัสสถานศึกษา', 'สาขาวิชาที่สมัคร',
'ได้เข้าศึกษา'
]]
df.rename(columns={
'เลขที่ใบสมัคร': 'application_no',
'คำนำหน้านาม(ไทย)': 'gender',
'ชื่อ(ไทย)': 'firstname',
'นามสกุล(ไทย)': 'lastname',
'รหัสสถานศึกษา': 'school_id',
'สาขาวิชาที่สมัคร': 'branch',
'ได้เข้าศึกษา': 'decision'
},
inplace=True)
except Exception as e:
print(e)
return inner_res_helper.make_inner_response(
False,
"Please check your file or table head " + str(e.args[0]),
"Please check your file or table head " + str(e.args[0]))
# admission table
admission_table = df.loc[:, [
'application_no', 'firstname', 'lastname', 'gender', 'decision'
]]
admission_table['admission_year'] = year
admission_table['upload_date'] = datetime.now().timestamp()
admission_table.loc[admission_table['gender'] == 'นาย',
['gender']] = 'M'
admission_table.loc[admission_table['gender'].str.contains('นาง'),
['gender']] = 'F'
admission_table.loc[admission_table['decision'] == 'ไม่',
['decision']] = -1
admission_table.loc[admission_table['decision'] == 'ใช่',
['decision']] = 1
admission_table['decision'].fillna(-1, inplace=True)
# admission in branch table
admission_branch = df.loc[:, ['application_no', 'branch']]
# get branch data from database
db = DatabaseHelper()
branch = db.get_branch()
branch = branch['value']
for i in branch:
branch_name = i['branch_name']
if admission_branch.loc[admission_branch['branch'].str.
contains(branch_name.split()[0]),
['branch']].shape[0] > 0:
admission_branch.loc[admission_branch['branch'].str.
contains(branch_name.split()[0]),
['branch']] = str(i['branch_id'])
admission_branch.rename(columns={'branch': 'branch_id'}, inplace=True)
# admission from table
admission_from = df.loc[:, ['application_no']]
admission_from['channel_id'] = channel
# admission studied
admission_studied = df.loc[:, ['application_no', 'GPAX', 'school_id']]
# admission_studied['school_id'] = '1010335002'
# make json data to send to database helper class
out_function_data = {
'admission_table': admission_table.to_json(orient='index'),
'admission_branch': admission_branch.to_json(orient='index'),
'admission_from': admission_from.to_json(orient='index'),
'admission_studied': admission_studied.to_json(orient='index')
}
return inner_res_helper.make_inner_response(
True, "Data for insert in to database", out_function_data)
def read_alumni_personal_data(self, data: pd.DataFrame, personal_header,
graduated_year):
try:
data = data.loc[1:, :]
data.drop_duplicates(subset=personal_header[0],
keep=False,
inplace=True)
data.rename(columns={
personal_header[0]: 'alumni_id',
personal_header[1]: 'gpax',
personal_header[2]: 'branch_name',
personal_header[3]: 'company',
personal_header[4]: 'status',
personal_header[5]: 'job_description',
personal_header[6]: 'salary',
personal_header[7]: 'institution',
personal_header[9]: 'branch',
personal_header[10]: 'apprentice',
personal_header[8]: 'faculty'
},
inplace=True)
data.astype({'alumni_id': str})
# alumni table
alumni = data.loc[:, ['alumni_id', 'gpax']]
alumni['graduated_year'] = graduated_year
alumni.loc[alumni['gpax'] == 'ไม่ระบุ', ['gpax']] = -1
alumni.astype({'gpax': float})
# alumni graduated table
db = DatabaseHelper()
branch = db.get_branch()
branch = branch['value']
alumni_graduated = data.loc[:, ['alumni_id', 'branch_name']]
for i in branch:
branch_name = i['branch_name']
if \
alumni_graduated.loc[
alumni_graduated['branch_name'].str.contains(branch_name.split()[0]), [
'branch_name']].shape[
0] > 0:
alumni_graduated.loc[alumni_graduated['branch_name'].str.
contains(branch_name.split()[0]),
['branch_name']] = str(i['branch_id'])
alumni_graduated.rename(columns={'branch_name': 'branch_id'},
inplace=True)
# alumni apprentice table
apprentice = db.get_apprentice_status_list()
apprentice = apprentice['value']
apprentice_table = data.loc[:, ['alumni_id', 'apprentice']]
for i in apprentice:
title = i['status_title']
title_id = i['status_id']
if apprentice_table.loc[
apprentice_table['apprentice'].str.contains(title),
['apprentice']].shape[0] > 0:
apprentice_table.loc[
apprentice_table['apprentice'].str.contains(title),
['apprentice']] = str(title_id)
apprentice_table.rename(columns={'apprentice': 'apprentice_id'},
inplace=True)
# alumni working table
working_status = db.get_working_status_list()
working_status = working_status['value']
working_table = data.loc[:, [
'alumni_id', 'status', 'company', 'institution',
'job_description', 'faculty', 'branch', 'salary'
]]
for i in working_status:
title = i['status_title']
title_id = i['status_id']
if working_table.loc[working_table['status'].str.
contains(title), ['status']].shape[0] > 0:
working_table.loc[
working_table['status'].str.contains(title),
['status']] = str(title_id)
working_table.rename(columns={'status': 'status_id'}, inplace=True)
working_table.loc[working_table['salary'].str.contains("ไม่ระบุ"),
['salary']] = np.nan
working_table.loc[working_table['salary'] == "",
['salary']] = np.nan
working_table['salary'] = working_table['salary'].astype(float)
working_table.loc[working_table['company'] == "",
['company']] = None
working_table.loc[working_table['institution'] == "",
['institution']] = None
working_table.loc[working_table['job_description'] == "",
['job_description']] = None
working_table.loc[working_table['faculty'] == "",
['faculty']] = None
working_table.loc[working_table['branch'] == "", ['branch']] = None
out_function_data = {
'alumni': alumni.to_json(orient='index'),
'alumni_graduated': alumni_graduated.to_json(orient='index'),
'working_table': working_table.to_json(orient='index'),
'apprentice_table': apprentice_table.to_json(orient='index')
}
return inner_res_helper.make_inner_response(
True, "Data for insert to data base", out_function_data)
except Exception as e:
print(e)
return inner_res_helper.make_inner_response(
False, "Error", "Having problem when prepare data.")
def read_new_student_file(self, file_location):
df = pd.read_excel(file_location,
converters={
'STUDENT_CODE': str,
'APPLICATION_NO': str,
'INSTITUTE_CODE': str
})
if df is None:
return inner_res_helper.make_inner_response(
response=False,
message="Cannot read file",
value="Cannot read file")
try:
df.rename(columns={
'STUDENT_CODE': 'student_id',
'APPLICATION_NO': 'application_no',
'FIRSTNAME_TH': 'firstname',
'LASTNAME_TH': 'lastname',
'SEX_NAME': 'gender',
'PROGRAM_PROJECT_NAME_TH': 'branch_name',
'INSTITUTE_CODE': 'school_id',
'OLDGPA': 'old_gpa'
},
inplace=True)
except Exception as e:
print(e)
return inner_res_helper.make_inner_response(
False,
"Please check your file or table head " + str(e.args[0]),
"Please check your file or table head " + str(e.args[0]))
# change gender from full text to M or F
df.loc[df['gender'] == 'ชาย', ['gender']] = 'M'
df.loc[df['gender'] == 'หญิง', ['gender']] = 'F'
# get branch data
db = DatabaseHelper()
branch = db.get_branch()
branch = branch['value']
# change branch name to branch id
for i in branch:
branch_name = i['branch_name']
if df.loc[df['branch_name'].str.contains(branch_name.split()[0]),
['branch_name']].shape[0] > 0:
df.loc[df['branch_name'].str.contains(branch_name.split()[0]),
['branch_name']] = str(i['branch_id'])
# data frame for student table
student = df.loc[:, ['student_id', 'firstname', 'lastname', 'gender']]
# data frame for entrance table
entrance = df.loc[:, ['student_id', 'application_no']]
# data frame for graduated
graduated = df.loc[:, ['student_id', 'school_id', 'old_gpa']]
graduated.rename(columns={'old_gpa': 'gpax'}, inplace=True)
graduated.fillna("0000000000", inplace=True)
# data frame for has status table
has_status = df.loc[:, ['student_id']]
has_status['status_id'] = 1
# data frame for study in
study_in = df.loc[:, ['student_id', 'branch_name']]
study_in.rename(columns={'branch_name': 'branch_id'}, inplace=True)
out_function_data = {
'student': student.to_json(orient='index'),
'entrance': entrance.to_json(orient='index'),
'graduated': graduated.to_json(orient='index'),
'has_status': has_status.to_json(orient='index'),
'study_in': study_in.to_json(orient='index')
}
return inner_res_helper.make_inner_response(
True, "Data for insert in to database", out_function_data)
def read_academic_file(self, file_location, year, semester):
df = pd.read_excel(file_location,
converters={'รหัส': str},
sheet_name=None)
if df is None:
return inner_res_helper.make_inner_response(
response=False,
message="Cannot read file",
value="Cannot read file")
sheet_name = list(df.keys())
academic_record = []
gpa_record = []
gpax_record = []
status_record = []
try:
for sheet_number in range(len(sheet_name)):
# get sheet from workbook
sheet = df[sheet_name[sheet_number]]
sheet.dropna(how='all', axis=1, inplace=True)
# read data from sheet
for std in range(sheet.shape[0]):
temp = sheet.iloc[std, :]
temp = temp.dropna()
temp = temp.reset_index()
subject_list = list(temp.index)
std_id = temp.iloc[0, 1]
gpa = [std_id, temp.iloc[-2, 1], semester, year]
gpa = list(map(str, gpa))
gpa_record.append(tuple(gpa))
gpax = [temp.iloc[-1, 1], std_id]
gpax = list(map(str, gpax))
gpax_record.append(tuple(gpax))
status_record.append(
self.__calculate_education_status(
std_id, temp.iloc[-1, 1]))
# get data per student
for subject in range(1, len(subject_list) - 2):
data = [std_id]
code = temp.iloc[subject, 0][:6]
grade = temp.iloc[subject, 1]
data.append(code)
data.append(grade)
data.append(semester)
data.append(year)
data = list(map(str, data))
academic_record.append(tuple(data))
except Exception as e:
print(e)
return inner_res_helper.make_inner_response(
response=False, message="Error in read data", value=str(e))
out_function_data = {
'academic_record': academic_record,
'gpa_record': gpa_record,
'gpax_record': gpax_record,
'status_record': status_record
}
return inner_res_helper.make_inner_response(
True, "Data for insert in to database", out_function_data)
def analyze_student_status(self, year=None):
connect = DatabaseHelper()
data = connect.get_all_admission(year)
value = {}
if data['value']:
df = pd.DataFrame(data['value'])
df = df[df['admission_year'] == int(year)]
if not df.empty:
status_data = analyze_helper.set_fullname(
connect.get_status_list())
status_dic = analyze_helper.set_dict(status_data.index,
status_data.status_title)
channel_data = analyze_helper.set_fullname(
connect.get_admission_channel())
channel_sample = self.split_channel(channel_data)
dupli_channel = channel_sample.channel_name.duplicated(
keep=False)
channel_sample.loc[dupli_channel, 'channel_name'] = channel_sample.loc[dupli_channel, 'channel_name'] + ' (' + \
channel_sample['channel_round'] + ')'
channel_dict = analyze_helper.set_dict(
channel_sample.index, channel_sample.channel_name)
branch = connect.get_branch()
branch_data = analyze_helper.set_branch(branch['value'])
branch_dict = analyze_helper.set_dict(branch_data.index,
branch_data.branch_name)
group_brance = df.groupby(['channel_id', 'branch_id'
]).size().unstack(fill_value=0)
group_brance = analyze_helper.check_list_column(
branch_data.index, group_brance)
group_brance = analyze_helper.check_list(
channel_data.index, group_brance)
group_brance = analyze_helper.set_fullname_column(
branch_dict, group_brance)
group_brance = analyze_helper.set_fullname_index(
channel_dict, group_brance)
branch_list = group_brance.columns.tolist()
channel_id_list = channel_data.index.tolist()
table_count = []
for c_id in channel_id_list:
by_channel = {}
data = df[df['channel_id'] == c_id]
if not data.empty:
count = len(data)
max_data = data.branch_id.value_counts().max()
min_data = data.branch_id.value_counts().min()
else:
count = 0
max_data = 0
min_data = 0
by_channel['channel'] = channel_dict[c_id]
by_channel['count'] = str(count)
by_channel['max_data'] = str(max_data)
by_channel['min_data'] = str(min_data)
table_count.append(by_channel)
all_student = len(df)
channel_count = df.channel_id.value_counts()
group = df[(df['status_id'] == 2) | (df['status_id'] == 3)]
group = group.groupby(['channel_id', 'status_id'
]).size().unstack(fill_value=0)
if group.empty:
group = pd.DataFrame(0,
index=np.arange(len(channel_count)),
columns=[2, 3])
group['channel'] = channel_count.index
group.set_index('channel', inplace=True)
group = group.rename(columns={2: "probation", 3: "drop"})
list_name = group.columns.tolist()
group = pd.merge(channel_count,
group,
left_index=True,
right_index=True,
how='inner')
group.rename(columns={group.columns[0]: "all"}, inplace=True)
all_admission = group['all'].sum()
for name in list_name:
group['per_Type_' + str(name)] = group.apply(
lambda row: (row[name] / row['all']) * 100, axis=1)
group['per_Stu_' + str(name)] = group.apply(
lambda row: (row[name] / all_student) * 100, axis=1)
group['per_all_student'] = (group['all'] / all_admission) * 100
group = group.round(2).sort_index()
group_check_index = analyze_helper.check_list(
channel_data.index, group)
group_fullname = analyze_helper.set_fullname_index(
channel_dict, group_check_index)
value = {
'branch': branch_list,
'count_by_brance': group_brance.to_dict('index'),
'all_student': str(all_student),
'table': group_check_index.to_dict('index'),
'table_count': table_count,
}
response = True
message = "Don't have Data"
else:
value = {}
response = False
message = "Don't have Data"
else:
value = {}
response = False
message = "Don't have Data"
return inner_res_helper.make_inner_response(response=response,
message=message,
value=value)
def analyze_admission(self, year=None):
connect = DatabaseHelper()
data = connect.get_all_admission(year)
value = {}
if data['value']:
df = pd.DataFrame(data['value'])
# real data
# deparment = connect.get_department()
# deparment = pd.io.json.json_normalize(deparment['value'], max_level=0)
# # branch_data = analyze_helper.set_branch(branch['value'])
# deparment_data = deparment[['dept_id','dept_name']]
# deparment_data.set_index('dept_id',inplace=True)
deparment = connect.get_department_ds()
deparment_data = pd.DataFrame(deparment['value'])
deparment_data = deparment_data[['dept_id', 'dept_name']]
deparment_data.set_index('dept_id', inplace=True)
status_data = analyze_helper.set_fullname(
connect.get_status_list())
channel_data = analyze_helper.set_fullname(
connect.get_admission_channel())
channel_sample = self.split_channel(channel_data)
###check duplicate channel anme
dupli_channel = channel_sample.channel_name.duplicated(keep=False)
channel_sample.loc[dupli_channel, 'channel_name'] = channel_sample.loc[dupli_channel, 'channel_name'] + ' (' + \
channel_sample['channel_round'] + ')'
channel_sample_for_dict = channel_sample[['channel_name']]
round_data = channel_sample[['round_id', 'round_name']]
school = analyze_helper.set_fullname(connect.get_school_lis())
deparment_dict = analyze_helper.set_dict(deparment_data.index,
deparment_data.dept_name)
school_dict = analyze_helper.set_dict(school.index,
school.school_title)
status_dic = analyze_helper.set_dict(status_data.index,
status_data.status_title)
channel_dic = analyze_helper.set_dict(
channel_sample_for_dict.index,
channel_sample_for_dict.channel_name)
data_split_now = df.copy()
data_not_year = df.copy()
if year:
data_split_now = df.loc[df['admission_year'] == int(year)]
round_list = channel_sample['round_id'].unique().tolist()
data_not_none = data_split_now.dropna()
gpa_by_branch = []
response_round = False
for i in round_list:
gpa_by_count = {}
channel_list = channel_sample[channel_sample['round_id'] == i]
list_channel = channel_list.index.tolist()
if (not data_not_none.empty):
count_by_branch = data_not_none['channel_id'].isin(
list_channel)
count_by_branch = data_not_none[count_by_branch]
count_by_branch = count_by_branch.groupby([
'channel_id', 'dept_id'
])['current_gpax'].mean().unstack(fill_value=0)
count_by_branch = count_by_branch.round(2)
count_by_branch_check_branch = analyze_helper.check_list_column(
deparment_data.index, count_by_branch)
count_by_branch_check_channel = analyze_helper.check_list(
channel_list.index, count_by_branch_check_branch)
check_by_round_channel = analyze_helper.set_fullname_column(
deparment_dict, count_by_branch_check_branch)
check_by_round_channel = analyze_helper.set_fullname_index(
channel_dic, check_by_round_channel)
gpa_by_count[
'gpa_by_branch'] = check_by_round_channel.to_dict(
'index')
gpa_by_branch.append(gpa_by_count)
response_round = True
count_channel = data_split_now.groupby('channel_id').size()
count_channel_check_channel = analyze_helper.check_list(
channel_sample.index, count_channel)
count_channel_check_channel = analyze_helper.set_fullname_index(
channel_dic, count_channel_check_channel)
count_school = data_split_now.school_id.value_counts()
sort_count_school = count_school.sort_values(
ascending=False).head()
sort_count_school_fullname = analyze_helper.set_fullname_index(
school_dict, sort_count_school)
count_by_status = data_split_now.groupby(
['channel_id', 'status_id']).size().unstack(fill_value=0)
count_by_status_check_status = analyze_helper.check_list_column(
status_data.index, count_by_status)
count_by_status_check_channel = analyze_helper.check_list(
channel_sample.index, count_by_status_check_status)
count_by_status_fullname = analyze_helper.set_fullname_column(
status_dic, count_by_status_check_channel)
count_by_status_fullname = analyze_helper.set_fullname_index(
channel_dic, count_by_status_fullname)
# used year and branch but used year and year-1
year_select = []
if year:
year_select.append(int(year))
year_select.append(int(year) - 1)
else:
max_year = data_not_year.admission_year.max()
year_select.append(max_year)
year_select.append(max_year - 1)
data_compare = data_not_year[data_not_year['admission_year'].isin(
year_select)]
compare_year = data_compare.groupby(
['channel_id', 'admission_year']).size().unstack(fill_value=0)
if compare_year.empty:
channel_name = channel_sample['channel_name'].unique().tolist()
compare_year_success = pd.DataFrame(0,
index=np.arange(
len(channel_name)),
columns=year_select)
compare_year_success['channel_name'] = channel_name
compare_year_success.set_index('channel_name', inplace=True)
else:
compare_year_check_channel = analyze_helper.check_list(
channel_sample.index, compare_year)
compare_year_success = analyze_helper.check_list_column(
year_select, compare_year_check_channel)
compare_year_success = analyze_helper.set_fullname_index(
channel_dic, compare_year_success)
value = {
'count_channel': count_channel_check_channel.to_dict(),
'count_by_branch': dict(zip(round_list, gpa_by_branch)),
'count_by_school': [sort_count_school_fullname.to_dict()],
'compare_year': [compare_year_success.to_dict('index')],
'count_by_status': [count_by_status_fullname.to_dict('index')],
'response_round': response_round
}
response = True
message = "Analyze Successfully"
else:
response = False
message = "Don't have Data"
return inner_res_helper.make_inner_response(response=response,
message=message,
value=value)
def student_admin(self):
value = {}
connect = DatabaseHelper()
data = connect.get_all_student()
if data['value']:
df = pd.DataFrame(data['value'])
# get_branch=connect.get_department(None)
# dept_data = pd.io.json.json_normalize(get_branch['value'], 'branch', ['dept_id','dept_name'])
# department_data = dept_data[['dept_id','dept_name']].set_index('dept_id')
# department_data.drop_duplicates(inplace=True)
get_branch = connect.get_department_ds()
get_branch = pd.DataFrame(get_branch['value'])
department_data = get_branch[['dept_id', 'dept_name']]
department_data = department_data.set_index('dept_id')
status_data = analyze_helper.set_fullname(
connect.get_status_list())
status_dic = analyze_helper.set_dict(status_data.index,
status_data.status_title)
list_department = department_data.index.unique().tolist()
analyze_by_dept = []
for dept in list_department:
analyze = {}
df_dept = df[df['dept_id'] == dept]
department_selector = get_branch[get_branch['dept_id'] == dept]
branch_data = analyze_helper.set_branch(
department_selector[['branch_id', 'branch_name']])
branch_dic = analyze_helper.set_dict(branch_data.index,
branch_data.branch_name)
status_by_branch = self.__status_by_branch(
df_dept, list(branch_data.index.values),
list(status_data.index.values))
status_by_branch_index = analyze_helper.set_fullname_index(
branch_dic, status_by_branch)
status_by_branch_finist = analyze_helper.set_fullname_column(
status_dic, status_by_branch_index)
status_by_year = self.__count_status(
df_dept[['student_year', 'education_status']],
list(status_data.index.values))
status_by_year_finist = analyze_helper.set_fullname_column(
status_dic, status_by_year)
count_by_branch = df_dept.groupby(['branch_id']).size()
count_by_branch = analyze_helper.check_list(
branch_data.index, count_by_branch)
count_by_branch = analyze_helper.set_fullname_index(
branch_dic, count_by_branch)
analyze['dept_id'] = dept
# print(branch_data)
analyze['branch'] = count_by_branch.to_dict()
analyze['status_by_year'] = [
status_by_year_finist.to_dict('index')
]
analyze['df_status_by_branch'] = [
status_by_branch_finist.to_dict('index')
]
analyze_by_dept.append(analyze)
value = {'analyze_by_dept': analyze_by_dept}
response = True
message = "Analyze Student Successfully"
else:
response = False
message = "Don't have Data"
return inner_res_helper.make_inner_response(response, message, value)
def analyze_alumni_work(self, year=None):
connect = DatabaseHelper()
data = connect.get_all_alumni(year)
if data['value']:
df = pd.DataFrame(data['value'])
df['graduated_gpax'] = df['graduated_gpax'].astype(int)
branch = connect.get_branch()
branch_data = analyze_helper.set_branch(branch['value'])
status_working = analyze_helper.set_fullname(
connect.get_working_status_list())
status_apprentice = analyze_helper.set_fullname(
connect.get_apprentice_status_list())
branch_dic = analyze_helper.set_dict(branch_data.index,
branch_data.branch_name)
status_working_dic = analyze_helper.set_dict(
status_working.index, status_working.status_title)
status_apprentice_dic = analyze_helper.set_dict(
status_apprentice.index, status_apprentice.status_title)
df_brach = df.groupby('branch_id').size()
df_branch_finish = analyze_helper.check_list(
branch_data.index.values, df_brach)
count_by_status = df.groupby('work_id').size()
count_by_status_finish = analyze_helper.check_list(
status_working.index.values, count_by_status)
count_by_training = df.groupby('apprentice_id').size()
count_by_training_finish = analyze_helper.check_list(
status_apprentice.index.values, count_by_training)
df_gpax = df[df['graduated_gpax'] != -1]
gpax_by_branch = df_gpax.groupby(
'branch_id')['graduated_gpax'].mean()
gpax_by_branch_2decimal = gpax_by_branch.round(2)
gpax_by_branch_finish = analyze_helper.check_list(
branch_data.index.values, gpax_by_branch_2decimal)
list_salary = {
1: 'น้อยกว่า 10,000',
2: '10,000-19,999',
3: '20,000-30,000',
4: 'มากกว่า 30,000'
}
salary_branch_trining = []
list_analze = {}
df_salary = df[df['salary'].notna()]
df_salary = df_salary.copy()
df_salary['salary'] = df_salary['salary'].astype(int)
# df_salary.loc[:, ['salary']] =df_salary['salary'].astype(int)
salary_all_branch_trining = self.__salary_branch_training(
df_salary[['salary', 'apprentice_id']])
salary_all_branch_trining_check_index = analyze_helper.check_list_column(
status_apprentice.index.values, salary_all_branch_trining)
salary_all_branch_trining_check_column = analyze_helper.check_list(
list_salary.keys(), salary_all_branch_trining_check_index)
salary_all_branch_trining_index = analyze_helper.set_fullname_column(
status_apprentice_dic, salary_all_branch_trining_check_column)
salary_all_branch_trining_finist = analyze_helper.set_fullname_index(
list_salary, salary_all_branch_trining_index)
list_analze['dept_name'] = 'ทั้งหมด'
list_analze['num_student'] = len(df)
list_analze[
'salary_all_branch_training'] = salary_all_branch_trining_finist.to_dict(
'index')
salary_branch_trining.append(list_analze)
list_branch_traning = df_brach.index.tolist()
for i in list_branch_traning:
list_analze = {}
data = df[df['branch_id'] == i]
if not data.empty:
analyze_salart = self.__salary_branch_training(
data[['salary', 'apprentice_id']])
analyze_salart = analyze_helper.check_list_column(
status_apprentice.index.values, analyze_salart)
analyze_salart = analyze_helper.check_list(
list_salary.keys(), analyze_salart)
analyze_salart = analyze_helper.set_fullname_column(
status_apprentice_dic, analyze_salart)
analyze_salart = analyze_helper.set_fullname_index(
list_salary, analyze_salart)
list_analze['dept_name'] = branch_dic[i]
list_analze['num_student'] = len(data)
list_analze[
'salary_all_branch_training'] = analyze_salart.to_dict(
'index')
else:
analyze_salart = pd.DataFrame(
0,
index=np.arange(len(list_salary)),
columns=status_apprentice.status_title.tolist())
analyze_salart['list_salary'] = list_salary.values()
analyze_salart.set_index('list_salary', inplace=True)
list_analze['dept_name'] = branch_dic[i]
list_analze['num_student'] = 0
list_analze[
'salary_all_branch_training'] = analyze_salart.to_dict(
'index')
salary_branch_trining.append(list_analze)
list_branch_traning.insert(0, 'all')
value = {
'count_student':
len(df.index),
'count_by_branch':
analyze_helper.set_fullname_index(branch_dic,
df_branch_finish).to_dict(),
'count_by_status':
analyze_helper.set_fullname_index(
status_working_dic, count_by_status_finish).to_dict(),
'count_by_training':
analyze_helper.set_fullname_index(
status_apprentice_dic, count_by_training_finish).to_dict(),
'salary_all_branch_training':
dict(zip(list_branch_traning, salary_branch_trining)),
'gpax_by_branch':
analyze_helper.set_fullname_index(
branch_dic, gpax_by_branch_finish).to_dict(),
}
response = True
message = "Analyze Successfully"
else:
value = {}
response = False
message = "Don't have Data"
return inner_res_helper.make_inner_response(response=response,
message=message,
value=value)
def analyze_by_dept(self, dept):
value = {}
connect = DatabaseHelper()
data = connect.get_all_student(dept)
if data['value']:
df = pd.DataFrame(data['value'])
# get_branch=connect.get_department(dept)
# if dept:
# branch_data = analyze_helper.set_branch(get_branch['value'][0][2])
# else :
# print(get_branch)
# branch=[]
# for i in get_branch['value']:
# for index in range(len(i['branch'])):
# branch.append(i['branch'][index])
# branch_data = analyze_helper.set_branch(branch)
get_branch = connect.get_department_ds()
get_branch = pd.DataFrame(get_branch['value'])
if dept:
get_branch = get_branch[get_branch['dept_id'] == dept]
branch_data = get_branch[['branch_id', 'branch_name']]
branch_data = branch_data.set_index('branch_id')
status_data = analyze_helper.set_fullname(
connect.get_status_list())
branch_dic = analyze_helper.set_dict(branch_data.index,
branch_data.branch_name)
status_dic = analyze_helper.set_dict(status_data.index,
status_data.status_title)
status_by_branch = self.__status_by_branch(
df, list(branch_data.index.values),
list(status_data.index.values))
# print(df)
status_by_branch_index = analyze_helper.set_fullname_index(
branch_dic, status_by_branch)
status_by_branch_finist = analyze_helper.set_fullname_column(
status_dic, status_by_branch_index)
status_by_year = self.__count_status(
df[['student_year', 'education_status']],
list(status_data.index.values))
status_by_year_finist = analyze_helper.set_fullname_column(
status_dic, status_by_year)
count_by_branch = df.groupby(['branch_id']).size()
count_by_branch = analyze_helper.check_list(
branch_data.index, count_by_branch)
count_by_branch = analyze_helper.set_fullname_index(
branch_dic, count_by_branch)
value['dept_name'] = get_branch.iloc[0, 1]
value['all_stu_dept'] = self.__count_student_dept(df)
value['branch'] = [count_by_branch.to_dict()]
value['status_by_year'] = [status_by_year_finist.to_dict('index')]
value['df_status_by_branch'] = [
status_by_branch_finist.to_dict('index')
]
response = True
message = "Analyze Student Successfully"
else:
response = False
message = "Don't have Data"
return inner_res_helper.make_inner_response(response, message, value)
