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 jsondata():
data = []
try:
result = select_table(
"person",
session["year_current"],
{"person_id": "person", "person_name": "person", "gender": "person"},
)
for row in result:
d = {}
d["person_id"] = row["person_id"]
d["person_name"] = row["person_name"]
d["gender"] = row["gender"]
data.append(d)
except:
return jsonify({"total": len(data), "rows": data})
if request.method == "POST":
print("post")
if request.method == "GET":
info = request.values
limit = info.get("limit", 10)
offset = info.get("offset", 0)
return jsonify(
{"total": len(data), "rows": data[int(offset) : (int(offset) + int(limit))]}
)
def search_data():
# 获取要查询的项目和值
search_item = request.args.get("search_item", "暂无", type=str)
search_string = request.args.get("search_string", "暂无", type=str)
# 查询结果
year = session["year_current"]
result = select_table(
["person", "education", "skill", "workinfo"],
year,
{"person_id": "person", "person_name": "person", "gender": "person"},
{search_item: " like '%" + search_string + "%'"},
)
data = []
if type(result) == dict:
data.append(result)
else:
data = result
# 返回结果
if len(data) == 0:
# 查询失败
msg = "没有查询到相关信息"
else:
# 查询成功
msg = "成功查询到" + str(len(data)) + "条信息"
return jsonify({"msg": msg, "total": len(data), "rows": data})
def jsondata():
data = []
try:
result = select_table('person', session['year_current'], {
'person_id': 'person',
'person_name': 'person',
'gender': 'person'
})
for row in result:
d = {}
d['person_id'] = row['person_id']
d['person_name'] = row['person_name']
d['gender'] = row['gender']
data.append(d)
except:
return jsonify({'total': len(data), 'rows': data})
if request.method == 'GET':
info = request.values
limit = info.get('limit', 10) # 每页显示的条数
offset = info.get('offset', 0) # 分片数，(页码-1)*limit，它表示一段数据的起点
return jsonify({
'total': len(data),
'rows': data[int(offset):(int(offset) + int(limit))]
})
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_data():
#获取搜索选的字段，然后获取查询的某个字
search_item = request.args.get('search_item', '暂无', type=str)
search_string = request.args.get('search_string', '暂无', type=str)
year = session['year_current']
result = select_table(['person', 'education', 'skill', 'workinfo'], year, {
'person_id': 'person',
'person_name': 'person',
'gender': 'person'
}, {search_item: ' like \'%' + search_string + '%\''})
data = []
if type(result) == dict:
data.append(result)
else:
data = result
msg = '成功查询到' + str(len(data)) + '条信息'
return jsonify({'msg': msg, 'total': len(data), 'rows': data})
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 hello():
year_list = []
year_data = get_lijing_db().execute(
"select year from year_list").fetchall()
if len(year_data) == 0:
year_list = ["暂无数据"]
session["year_current"] = year_list[0]
return render_template("lijing/search.html", year_list=year_list)
else:
for year in year_data:
year_list.insert(0, year["year"])
session["year_current"] = year_list[0]
school_list = []
school_id = []
school_data = select_table(
"school",
session["year_current"],
{
"school_id": "school",
"school_name": "school"
},
)
if type(school_data) == dict:
school_list.append(school_data["school_name"])
school_id.append(school_data["school_id"])
else:
for i in school_data:
school_list.append(i["school_name"])
school_id.append(i["school_id"])
if len(school_list) != 1:
school_list = school_list[1:]
return render_template("lijing/search.html",
year_list=year_list,
school_list=school_list)
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'})
def search_data():
# 获取要查询的表
table_list = request.args.getlist("select_table_list[]")
# 获取要查询的年份和分校
year = request.args.get("year", type=str)
school = request.args.get("school", type=str)
# 获取要查询的项
select_item = {}
select_item_list = request.args.getlist("select_item_list[]")
for item in select_item_list:
select_item[item] = request.args.get(item, type=str)
table_list.append(select_item[item])
# 获取要查询的表
table_list = list(set(table_list))
print(table_list)
# 获得要查询的字段和值
search_item_list = request.args.getlist("condition_item_list[]")
search_string_list = request.args.getlist("search_string_list[]")
# 拼接condition_dict字典
condition_dict = {}
for i in range(0, len(search_item_list)):
condition_dict[
search_item_list[i]] = " LIKE '%" + search_string_list[i] + "%'"
# 查询结果
data = []
if school == "全部数据":
# 查所有分校
data = select_table(table_list, year, select_item, condition_dict)
else:
# 查特定分校
person_id_data = select_table(
["school", "job"],
year,
{"person_id": "job"},
{"school_" + year + ".school_name": " = '" + school + "'"},
)
select_item["person_id"] = "person"
search_data = select_table(table_list, year, select_item,
condition_dict)
person_id_list = []
for i in person_id_data:
person_id_list.append(i["person_id"])
for i in search_data:
if i["person_id"] in person_id_list:
del i["person_id"]
data.append(i)
# 未查询到结果
if len(data) == 0:
total = 0
msg = "没有查询到相关信息"
return jsonify({"msg": msg, "total": total})
# 写入表格数据
item_name_dict = {
"person_id": "编号",
"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": "退休时间",
"job_name": "行政职务",
"class_name": "班级名称",
"lesson_number": "总课时数",
"year_result": "年度考核",
"school_name": "所在分校",
"honor_time": "发证时间",
"honor_unit": "发证单位",
"honor_name": "获奖名称",
"honor_grade": "证书级别",
"honor_number": "证书编号",
"honor_remark": "备注",
"get_time": "获得时间",
}
out_file_name = "exportData(%s).xlsx" % current_time()
save_path = list_to_path(["flaskr", "static", "downloads", out_file_name])
workbook = xlsxwriter.Workbook(save_path)
worksheet = workbook.add_worksheet("Sheet1")
# 写标题
col = 0
for i in data[0]:
worksheet.write(0, col, item_name_dict[i])
col = col + 1
# 写数据
for i in range(len(data)):
col = 0
row = data[i]
for j in row:
worksheet.write(i + 1, col, row[j])
col = col + 1
workbook.close()
total = len(data)
msg = "查询到" + str(len(data)) + "条信息"
return jsonify({"msg": msg, "total": total, "filename": out_file_name})
