def get_branch_data():
branch_id = request.args.get('branch_id')
db = DatabaseHelper()
data = db.get_branch(branch_id)
return api_helper.return_response(data)
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 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_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 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)