def update_data():
# 获取要更新的数据
year_select = session["year_current"]
update_dict = {}
item = get_item_list(["person", "education", "skill", "workinfo"])
for i in item:
update_dict[i] = request.args.get(i, "暂无", type=str)
if update_dict[i] == "":
update_dict[i] = "暂无"
# 获取要更新的person_id，拼接条件字典
person_id = request.args.get("person_id", "0", type=str)
condition_dict = {"person_id": " = '" + person_id + "'"}
# 更新数据
item_person = get_item_list("person")
update_table("person", year_select, item_person, update_dict, condition_dict)
item_education = get_item_list("education")
update_table("education", year_select, item_education, update_dict, condition_dict)
item_skill = get_item_list("skill")
update_table("skill", year_select, item_skill, update_dict, condition_dict)
item_workinfo = get_item_list("workinfo")
update_table("workinfo", year_select, item_workinfo, update_dict, condition_dict)
# 返回结果
msg = "成功修改教师“" + update_dict["person_name"] + "”的基本信息"
return jsonify({"msg": msg})
def update_data():
year_select = session['year_current']
update_dict = {}
item = get_item_list(['person', 'education', 'skill', 'workinfo'])
for i in item:
update_dict[i] = request.args.get(i, '暂无', type=str)
if update_dict[i] == '':
update_dict[i] = '暂无'
person_id = request.args.get('person_id', '0', type=str)
condition_dict = {'person_id': ' = \'' + person_id + '\''}
item_person = get_item_list('person')
update_table('person', year_select, item_person, update_dict,
condition_dict)
item_education = get_item_list('education')
update_table('education', year_select, item_education, update_dict,
condition_dict)
item_skill = get_item_list('skill')
update_table('skill', year_select, item_skill, update_dict, condition_dict)
item_workinfo = get_item_list('workinfo')
update_table('workinfo', year_select, item_workinfo, update_dict,
condition_dict)
msg = '成功修改教师' + update_dict['person_name'] + '的信息'
return jsonify({'msg': msg})
def add_data():
insert_dict = {}
item = get_item_list(['person', 'education', 'skill',
'workinfo']) #返回的是所有25个字段
year_select = session['year_current'] #当前年份
for i in item: #就是一个一个增加，如果没有增加，就先设置成暂无
insert_dict[i] = request.args.get(i, '暂无', type=str)
if insert_dict[i] == '':
insert_dict[i] = '暂无'
item_person = get_item_list('person')
insert_table('person', year_select, item_person, insert_dict)
person_id = select_table(
'person', year_select, {'person_id': 'person'},
{'person_name': insert_dict['person_name']})['person_id']
insert_dict['person_id'] = float_int_string(person_id)
item_education = get_item_list('education')
item_education.append('person_id')
insert_table('education', year_select, item_education, insert_dict)
item_skill = get_item_list('skill')
item_skill.append('person_id')
insert_table('skill', year_select, item_skill, insert_dict)
item_workinfo = get_item_list('workinfo')
item_workinfo.append('person_id')
insert_table('workinfo', year_select, item_workinfo, insert_dict)
msg = '成功添加教师' + insert_dict['person_name'] + '的信息'
return jsonify({'msg': msg})
def add_data():
# 获取要添加的数据
year_select = session["year_current"]
insert_dict = {}
item = get_item_list(["person", "education", "skill", "workinfo"])
for i in item:
insert_dict[i] = request.args.get(i, "暂无", type=str)
if insert_dict[i] == "":
insert_dict[i] = "暂无"
# 添加数据到person表
item_person = get_item_list("person")
insert_table("person", year_select, item_person, insert_dict)
# 获取自动生成的person_id
person_id = select_table(
"person",
year_select,
{"person_id": "person"},
{"person_name": " = '" + insert_dict["person_name"] + "'"},
)["person_id"]
insert_dict["person_id"] = float_int_string(person_id)
# 添加数据到其他表
item_education = get_item_list("education")
item_education.append("person_id")
insert_table("education", year_select, item_education, insert_dict)
item_skill = get_item_list("skill")
item_skill.append("person_id")
insert_table("skill", year_select, item_skill, insert_dict)
item_workinfo = get_item_list("workinfo")
item_workinfo.append("person_id")
insert_table("workinfo", year_select, item_workinfo, insert_dict)
# 返回结果
msg = "成功添加教师" + insert_dict["person_name"] + "的信息"
return jsonify({"msg": msg})
def search():
person_id = request.args.get('id', 0, type=int)
year = session['year_current']
item = {}
table = ['person', 'education', 'skill', 'workinfo']
for table_name in table:
item_list = get_item_list(table_name)
for i in item_list:
item[i] = table_name
condition = 'person_' + year + '.person_id'
result = select_table(
table, year, item,
{condition: '=\'' + float_int_string(person_id) + '\''})
return jsonify(result)
def search():
# 获取要查看详情的教师id
person_id = request.args.get("id", 0, type=int)
# 拼接要查询的项目
year = session["year_current"]
item = {}
table = ["person", "education", "skill", "workinfo"]
for table_name in table:
item_list = get_item_list(table_name)
for i in item_list:
item[i] = table_name
# 查询结果
condition = "person_" + year + ".person_id"
result = select_table(
["person", "education", "skill", "workinfo"],
year,
item,
{condition: " = '" + float_int_string(person_id) + "'"},
)
# 返回结果
return jsonify(result)
def importData():
if request.method == "POST":
# 获取年份列表
db = get_lijing_db()
year_data = db.execute("select year from year_list").fetchall()
year_list = []
for year in year_data:
year_list.insert(0, year["year"])
# 获取要导入的年份，判断是否在已有年份中
year_select = request.form.get("year_select")
if year_select not in year_list:
# 插入新的年份
db.execute(
"insert into year_list (year, basicinfo, workinfo, honorinfo) values (?,?,?,?)",
(
year_select,
0,
0,
0,
),
)
# 新年份创建新的表
create_table(["person", "education", "workinfo", "skill"], year_select)
# 获取选择的表格文件
f = request.files["file"]
# 获取文件名，判断是否是excel文件
filename = secure_filename("".join(lazy_pinyin(f.filename)))
if filename.endswith(".xlsx"):
# 保存文件到服务器uploads文件夹
upload_path = list_to_path(["flaskr", "static", "uploads", filename])
f.save(upload_path)
# 打开保存后的文件
data = xlrd.open_workbook(upload_path)
table = data.sheet_by_index(0)
# print("总行数：" + str(table.nrows))
# print("总列数：" + str(table.ncols))
# 找到标题
dict_title = {
"姓名": "person_name",
"性别": "gender",
"身份证号": "id_number",
"联系电话": "phone",
"政治面貌": "political_status",
"入党时间": "time_Party",
"参加工作时间": "time_work",
"家庭住址": "address",
"工作简历": "resume",
"第一学历": "edu_start",
"第一学历毕业时间": "time_edu_start",
"第一学历毕业学校": "school_edu_start",
"第一学历专业": "major_edu_start",
"最高学历": "edu_end",
"最高学历毕业时间": "time_edu_end",
"最高学历毕业学校": "school_edu_end",
"最高学历专业": "major_edu_end",
"专业技术职称": "skill_title",
"取得时间": "time_skill",
"发证单位": "skill_unit",
"发证文件批号": "skill_number",
"调入大集中学时间": "time_school",
"用工性质": "work_kind",
"工作岗位": "job_post",
"退休时间": "time_retire",
}
row_title = table.row_values(0)
title_id = {}
for i in dict_title:
# 初始化-1
title_id[dict_title[i]] = -1
for i in range(0, len(row_title)):
title_name = row_title[i]
if title_name in dict_title:
# 保存标题序号
title_id[dict_title[title_name]] = i
# 导入数据
item = get_item_list(["person", "education", "skill", "workinfo"])
for i in range(1, table.nrows):
# 获取每一行的值
row_value = table.row_values(i)
insert_dict = {}
for j in item:
if title_id[j] == -1:
# 没有找到标题
insert_dict[j] = "暂无"
else:
# 找到标题
insert_dict[j] = float_int_string(row_value[title_id[j]])
if len(insert_dict[j]) == 0:
# 找到标题，但没有值
insert_dict[j] = "暂无"
# 插入数据到person表
item_person = get_item_list("person")
insert_table("person", year_select, item_person, insert_dict)
# 获取自动生成的person_id
condition = {"person_name": " = '" + insert_dict["person_name"] + "'"}
person_id = select_table(
"person", year_select, {"person_id": "person"}, condition
)["person_id"]
insert_dict["person_id"] = float_int_string(person_id)
# 插入其他表
item_education = get_item_list("education")
item_education.append("person_id")
insert_table("education", year_select, item_education, insert_dict)
item_skill = get_item_list("skill")
item_skill.append("person_id")
insert_table("skill", year_select, item_skill, insert_dict)
item_workinfo = get_item_list("workinfo")
item_workinfo.append("person_id")
insert_table("workinfo", year_select, item_workinfo, insert_dict)
# 删除保存的表格文件
os.remove(upload_path)
return redirect(url_for("lijing_basicinfo.hello"))
else:
# 不是execl文件
error = "请导入xlsx格式的文件"
flash(error)
return redirect(url_for("lijing_basicinfo.hello"))
def exportData():
# 获取要导出的项
export_item = {}
table = ["person", "education", "skill", "workinfo"]
item = get_item_list(table)
for table_name in table:
item_table = get_item_list(table_name)
for i in item_table:
if request.args.get(i) == "true":
export_item[i] = table_name
# 判断是否导出全部数据
year = session["year_current"]
flag_search = request.args.get("flag_search")
id_list = []
if flag_search == "true":
# 导出部分数据
id_list = request.args.getlist("id_list[]")
else:
# 导出全部数据
person_data = select_table("person", year, {"person_id": "person"})
for i in person_data:
id_list.append(str(i["person_id"]))
# 获取要导出的数据
table_data = []
for i in id_list:
result = select_table(
table,
year,
export_item,
{"person_" + year + ".person_id": " = '" + i + "'"},
)
table_data.append(result)
# 写入数据到表格
item_name_dict = {
"person_name": "姓名",
"gender": "性别",
"id_number": "身份证号",
"phone": "联系电话",
"political_status": "政治面貌",
"time_Party": "入党时间",
"time_work": "参加工作时间",
"address": "家庭住址",
"resume": "个人简历",
"edu_start": "第一学历",
"time_edu_start": "第一学历毕业时间",
"school_edu_start": "第一学历毕业学校",
"major_edu_start": "第一学历专业",
"edu_end": "最高学历",
"time_edu_end": "最高学历毕业时间",
"school_edu_end": "最高学历毕业学校",
"major_edu_end": "最高学历专业",
"skill_title": "专业技术职称",
"time_skill": "职称取得时间",
"skill_unit": "职称发证单位",
"skill_number": "发证文件批号",
"time_school": "调入大集中学时间",
"work_kind": "用工性质",
"job_post": "工作岗位",
"time_retire": "退休时间",
}
save_path = list_to_path(["flaskr", "static", "downloads", "exportData.xlsx"])
workbook = xlsxwriter.Workbook(save_path)
worksheet = workbook.add_worksheet("Sheet1")
col = 0
for i in export_item:
worksheet.write(0, col, item_name_dict[i])
col = col + 1
for i in range(len(id_list)):
col = 0
for j in export_item:
worksheet.write(i + 1, col, table_data[i][j])
col = col + 1
workbook.close()
# 完成导出
msg = "成功导出" + str(len(id_list)) + "条信息"
return jsonify({"msg": msg, "filename": "exportData.xlsx"})
def importData():
if request.method == 'POST':
db = get_lijing_db()
year_select = request.form['year_select']
year_data = db.execute('select year from year_list').fetchall()
year_list = []
for year in year_data:
year_list.insert(0, year['year'])
if year_select not in year_list:
db.execute(
'insert into year_list (year, basicinfo, workinfo, honorinfo) values (?,?,?,?)',
(
year_select,
0,
0,
0,
))
create_table(['person', 'education', 'workinfo', 'skill'],
year_select)
f = request.files['file']
filename = secure_filename(''.join(lazy_pinyin(f.filename)))
if filename.endswith('.xlsx'):
basepath = os.path.dirname(__file__)
upload_path = os.path.join(basepath, '..\\static\\uploads',
filename)
f.save(upload_path)
data = xlrd.open_workbook(upload_path)
table = data.sheet_by_index(0)
print("总行数：" + str(table.nrows))
print("总列数：" + str(table.ncols))
# 找到标题
dict_title = {
'姓名': 'person_name',
'性别': 'gender',
'身份证号': 'id_number',
'联系电话': 'phone',
'政治面貌': 'political_status',
'入党时间': 'time_Party',
'参加工作时间': 'time_work',
'家庭住址': 'address',
'工作简历': 'resume',
'第一学历': 'edu_start',
'第一学历毕业时间': 'time_edu_start',
'第一学历毕业学校': 'school_edu_start',
'第一学历专业': 'major_edu_start',
'最高学历': 'edu_end',
'最高学历毕业时间': 'time_edu_end',
'最高学历毕业学校': 'school_edu_end',
'最高学历专业': 'major_edu_end',
'专业技术职称': 'skill_title',
'取得时间': 'time_skill',
'发证单位': 'skill_unit',
'发证文件批号': 'skill_number',
'调入大集中学时间': 'time_school',
'用工性质': 'work_kind',
'工作岗位': 'job_post',
'退休时间': 'time_retire'
}
row_title = table.row_values(0)
print(row_title)
title_id = {} # {'person_name':'1','gender':'2'}
for i in dict_title:
title_id[dict_title[i]] = -1
print(title_id)
for i in range(0, len(row_title)):
title_name = row_title[i]
if title_name in dict_title:
title_id[dict_title[title_name]] = i
print(title_id)
# 导入数据
item = get_item_list(['person', 'education', 'skill', 'workinfo'])
for i in range(1, table.nrows):
row_value = table.row_values(i)
insert_dict = {}
for j in item:
if title_id[j] == -1:
insert_dict[j] = '暂无'
else:
insert_dict[j] = float_int_string(
row_value[title_id[j]])
if len(insert_dict[j]) == 0:
insert_dict[j] = '暂无'
item_person = get_item_list('person')
insert_table('person', year_select, item_person, insert_dict)
person_id = select_table(
'person', year_select, {'person_id': 'person'}, {
'person_name':
' = \'' + insert_dict['person_name'] + '\''
})['person_id']
insert_dict['person_id'] = float_int_string(person_id)
item_education = get_item_list('education')
item_education.append('person_id')
insert_table('education', year_select, item_education,
insert_dict)
item_skill = get_item_list('skill')
item_skill.append('person_id')
insert_table('skill', year_select, item_skill, insert_dict)
item_workinfo = get_item_list('workinfo')
item_workinfo.append('person_id')
insert_table('workinfo', year_select, item_workinfo,
insert_dict)
os.remove(upload_path)
return redirect(url_for('lijing_basicinfo.hello'))
else:
error = '请导入xlsx格式的文件'
flash(error)
return redirect(url_for('lijing_basicinfo.hello'))
def exportData():
export_item = {}
table = ['person', 'education', 'skill', 'workinfo']
item = get_item_list(table)
for table_name in table:
item_table = get_item_list(table_name)
for i in item_table:
if request.args.get(i) == 'true':
export_item[i] = table_name
year = session['year_current']
flag_search = request.args.get('flag_search')
id_list = []
if flag_search == 'true':
id_list = request.args.getlist("id_list[]")
else:
person_data = select_table('person', year, {'person_id': 'person'})
for i in person_data:
id_list.append(str(i['person_id']))
table_data = []
for i in id_list:
result = select_table(
table, year, export_item,
{'person_' + year + '.person_id': ' = \'' + i + '\''})
table_data.append(result)
item_name_dict = {
'person_name': '姓名',
"gender": '性别',
"id_number": '身份证号',
"phone": '联系电话',
"political_status": '政治面貌',
"time_Party": '入党时间',
"time_work": '参加工作时间',
"address": '家庭住址',
"resume": '个人简历',
"edu_start": '第一学历',
"time_edu_start": '第一学历毕业时间',
"school_edu_start": '第一学历毕业学校',
"major_edu_start": '第一学历专业',
"edu_end": '最高学历',
"time_edu_end": '最高学历毕业时间',
"school_edu_end": '最高学历毕业学校',
"major_edu_end": '最高学历专业',
"skill_title": '专业技术职称',
"time_skill": '职称取得时间',
"skill_unit": '职称发证单位',
"skill_number": '发证文件批号',
"time_school": '调入大集中学时间',
"work_kind": '用工性质',
"job_post": '工作岗位',
"time_retire": '退休时间'
}
workbook = xlsxwriter.Workbook(
'flaskr\\static\\downloads\\exportData.xlsx')
worksheet = workbook.add_worksheet('Sheet1')
col = 0
for i in export_item:
worksheet.write(0, col, item_name_dict[i])
col = col + 1
for i in range(len(id_list)):
col = 0
print(i)
for j in export_item:
worksheet.write(i + 1, col, table_data[i][j])
col = col + 1
print(j)
workbook.close()
# # print(os.path.join(os.path.dirname(
# # __file__), 'static', 'downloads', 'exportData.xlsx'))
msg = '成功导出' + str(len(id_list)) + '条信息'
return jsonify({'msg': msg, 'filename': 'exportData.xlsx'})