def readfile():
if request.method == "POST":
msg = "成功"
table_title = []
f = request.files["file"]
filename = secure_filename("".join(lazy_pinyin(f.filename)))
print(filename)
if filename.endswith(".xlsx"):
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))
table_title = table.row_values(0)
# print(table_title)
# print(len(table_title))
os.remove(upload_path)
else:
msg = "请导入xlsx格式的文件"
return jsonify({"msg": msg, "table_title": table_title})
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 readfile():
if request.method == "POST":
msg = "成功"
f = request.files["file"]
filename = current_time() + secure_filename("".join(
lazy_pinyin(f.filename)))
if not filename.endswith(".xlsx"):
msg = "请导入xlsx格式的文件"
return jsonify({"msg": msg})
# 保存文件
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)
# 获取合并单元格（起始行，结束行，起始列，结束列）
merged_cells = table.merged_cells
# 获取数据行索引
row_data = 1
for cell in merged_cells:
if cell[1] > row_data:
row_data = cell[1]
pass
table_col = []
items = {
"姓名": -1,
"考号": -1,
"班级": -1,
"总分": -1,
"语文": -1,
"数学": -1,
"英语": -1,
"物理": -1,
"化学": -1,
"道法": -1,
"历史": -1,
"地理": -1,
"生物": -1,
}
# items = ['总分', '语文', '数学', '英语', '物理', '化学', '生物', '历史', '地理', '道法']
for row_title in range(0, row_data):
title = table.row_values(row_title)
for i in range(0, len(title)):
# 返回每列的标题数据
if row_title == 0:
table_col.append("第" + str(i + 1) + "列：[ " + title[i] +
" ] ")
else:
table_col[i] = table_col[i] + "[ " + title[i] + " ] "
# 获取分数列索引
if title[i] in items:
items[title[i]] = i
pass
pass
# 分数列索引
col_index = []
for item in items:
col_index.append(items[item])
# 前9行数据
table_row = []
for i in range(0, 9):
data_row = str(table.row_values(i))
table_row.append("第" + str(i + 1) + "行：" + data_row)
pass
os.remove(upload_path)
pass
return jsonify({
"msg": msg,
"table_col": table_col,
"col_index": col_index,
"table_row": table_row,
"row_data": row_data,
})
def resultStatistics():
if request.method == "POST":
msg = "成功"
f = request.files["file"]
f2 = request.files["file2"]
filename = secure_filename("".join(lazy_pinyin(f.filename)))
filename2 = secure_filename("".join(lazy_pinyin(f2.filename)))
if not filename2.endswith(".xlsx"):
msg = "请导入xlsx格式的文件"
return jsonify({"msg": msg})
# 防止文件重名
if filename == filename2:
filename2 = "1" + filename2
# 获取request数据
class1 = int(request.form.get("class1"))
class2 = int(request.form.get("class2"))
delete_id_list = request.form.get("delete_id").split("\n")
change_examid_list = request.form.get("change_examid").split("\n")
change_class_list = request.form.get("change_class").split("\n")
if len(change_examid_list) != len(change_class_list):
msg = "调整班级数据有错误"
return jsonify({"msg": msg})
# 保存文件
upload_path = list_to_path(["flaskr", "static", "uploads", filename])
f.save(upload_path)
upload_path2 = list_to_path(["flaskr", "static", "uploads", filename2])
f2.save(upload_path2)
# 打开文件
data = xlrd.open_workbook(upload_path)
table = data.sheet_by_index(0)
data2 = xlrd.open_workbook(upload_path2)
table2 = data2.sheet_by_index(0)
# 获取成绩表格列索引
items = {
"姓名": -1,
"考号": -1,
"班级": -1,
"总分": -1,
"语文": -1,
"数学": -1,
"英语": -1,
"物理": -1,
"化学": -1,
"道法": -1,
"历史": -1,
"地理": -1,
"生物": -1,
}
item_title = [
"姓名",
"考号",
"班级",
"语文",
"数学",
"英语",
"物理",
"化学",
"生物",
"历史",
"地理",
"道法",
"总分",
]
item_id_list = request.form.get("item_id_list").split(",")
it = 0
for item in items:
items[item] = int(item_id_list[it]) - 1
it = it + 1
if items[item] == -1:
item_title.remove(item)
pass
# 获取各科老师、人数表格索引
items_table2 = item_title[2:]
items_table2[-1] = "人数"
data_title_teacher = table2.row_values(0)
item_table2_id_list = []
for item in items_table2:
flag = False
for i in range(len(data_title_teacher)):
if item in data_title_teacher[i]:
flag = True
item_table2_id_list.append(i)
pass
pass
if not flag:
if item == "班级":
msg = "教师、有效人数表格：缺少班级"
elif item == "人数":
msg = "教师、有效人数表格：缺少班级有效人数"
else:
msg = "教师、有效人数表格：缺少" + item + "教师姓名"
return jsonify({"msg": msg})
pass
# 获取成绩数据、班级数据
data_table = []
class_list = []
index_data = int(request.form.get("index_data")) - 1
for i in range(index_data, table.nrows):
row_value = table.row_values(i)
class_name = float_int_string(row_value[items["班级"]])
if class_name not in class_list:
class_list.append(class_name)
data_row = []
for item in item_title:
if row_value[items[item]] == "":
data_row.append(0.0)
else:
if item == "班级":
data_row.append(
int(float_int_string(row_value[items[item]])))
elif item == "考号":
data_row.append(
float_int_string(row_value[items[item]]))
else:
data_row.append(row_value[items[item]])
pass
pass
data_table.append(data_row)
pass
# 获取老师、有效人数数据
data_table2 = []
for i in range(1, table2.nrows):
row = []
data_row = table2.row_values(i)
for teacher_id in item_table2_id_list:
row.append(float_int_string(data_row[teacher_id]))
data_table2.append(row)
os.remove(upload_path)
os.remove(upload_path2)
# 处理数据
# 调整部分学生的班级
for i in range(len(change_examid_list)):
change_exam_id = change_examid_list[i]
flag = False
for j in range(len(data_table)):
if change_exam_id == data_table[j][1]:
flag = True
data_table[j][2] = int(change_class_list[i])
pass
pass
if not flag:
if change_exam_id == "":
pass
else:
msg = "要调整班级的学生学号找不到：" + change_exam_id
return jsonify({"msg": msg})
pass
pass
# 判断调整的班级是否存在
for class_name in change_class_list:
if class_name not in class_list:
if class_name == "":
pass
else:
msg = "要调整班级的班级不存在：" + class_name
return jsonify({"msg": msg})
pass
pass
# 通过学号删除不计入统计的学生
index_delele = []
delete_id_dict = {}
for i in delete_id_list:
if i == "":
continue
i = i.replace("\r", "")
delete_id_dict[i] = True
pass
for i in range(len(data_table)):
exam_id = data_table[i][1]
if exam_id in delete_id_dict:
delete_id_dict[exam_id] = False
index_delele.append(i)
pass
for i in delete_id_dict:
if delete_id_dict[i]:
msg = "要删除的学生学号找不到：" + i
return jsonify({"msg": msg})
pass
index_delele = sorted(index_delele, reverse=True)
for i in index_delele:
del data_table[i]
#############################################################
export_title = []
export_data = []
item_subject = item_title[3:]
# 找到总分索引
index_total = -1
for i in range(len(item_title)):
if "总分" == item_title[i]:
index_total = i
break
pass
# 按总分排序
data_table.sort(key=itemgetter(index_total), reverse=True)
# 找到尖子生边界分数
class1_total_result = data_table[class1 - 1][-1]
class2_total_result = data_table[class1 + class2 - 1][-1]
# 找到班级索引
index_class = -1
for i in range(len(item_title)):
if "班级" == item_title[i]:
index_class = i
break
pass
# 按班级排序、按班级分组
data_table.sort(key=itemgetter(index_class))
for class_name, class_students in groupby(data_table,
itemgetter(index_class)):
# 一个班的所有学生数据
class_students_result = []
for student in class_students:
class_students_result.append(student)
# 判断教师、有效人数表格中是否有对应的班级
index_teacher_number = -1
for i in range(len(data_table2)):
if str(class_name) == data_table2[i][0]:
index_teacher_number = i
break
if index_teacher_number == -1:
msg = "教师、有效人数表格：找不到班级 %s" % str(class_name)
return jsonify({"msg": msg})
# # 有效人数
average_num = int(data_table2[index_teacher_number][-1])
# if average_num > len(class_students_result):
# msg = "教师、有效人数表格：%s班有效人数错误" % str(class_name)
# return jsonify({"msg": msg})
# 每个班级的数据
data_class = []
data_class.append(class_name)
# 分科目计算平均分
for item in item_subject:
# 科目老师的名字
index_teacher_name = -1
for i in range(len(items_table2)):
if item == items_table2[i]:
index_teacher_name = i
break
if index_teacher_name != -1:
data_class.append(
data_table2[index_teacher_number][index_teacher_name])
# 找到分数索引
index_item = -1
for i in range(len(item_title)):
if item == item_title[i]:
index_item = i
break
# 计算平均分
sum_result = 0
if average_num <= len(class_students_result):
for i in range(0, average_num):
sum_result = sum_result + class_students_result[i][
index_item]
pass
else:
for i in range(0, len(class_students_result)):
sum_result = sum_result + class_students_result[i][
index_item]
pass
average_result = sum_result / average_num
data_class.append(average_result)
# 预留排名
data_class.append(0)
# 统计尖子生个数
number_class1 = 0
number_class2 = 0
for student in class_students_result:
if student[-1] >= class1_total_result:
number_class1 = number_class1 + 1
elif student[-1] >= class2_total_result:
number_class2 = number_class2 + 1
else:
pass
data_class.append(number_class1)
data_class.append(0)
data_class.append(number_class2)
data_class.append(0)
export_data.append(data_class)
# 标题
export_title.append("班级")
it = 0
sort_id_list = []
for item in item_subject:
if item != "总分":
export_title.append(item + "教师")
it = it + 1
export_title.append(item + "人平")
export_title.append(item + "排名")
it = it + 2
sort_id_list.append(it)
pass
export_title.append("一类人数")
export_title.append("排名")
it = it + 2
sort_id_list.append(it)
export_title.append("二类人数")
export_title.append("排名")
it = it + 2
sort_id_list.append(it)
# 统计排名
for i in sort_id_list:
export_data.sort(key=itemgetter(i - 1), reverse=True)
it = 1
old_result = export_data[0][i - 1]
old_paiming = 1
for row in export_data:
if old_result == row[i - 1]:
row[i] = old_paiming
else:
row[i] = it
old_result = row[i - 1]
old_paiming = row[i]
it = it + 1
pass
pass
# 按班级排序
export_data.sort(key=itemgetter(0))
# 统计全校人平
total_school_row = []
for i in range(len(export_title)):
if i == 0:
total_school_row.append("全校人平")
else:
total_school_row.append("")
pass
for i in sort_id_list:
sum_result = 0
for row in export_data:
sum_result = sum_result + row[i - 1]
total_school_row[i - 1] = sum_result / len(class_list)
pass
total_school_row[-2] = total_school_row[-2] * len(class_list)
total_school_row[-4] = total_school_row[-4] * len(class_list)
export_data.append(total_school_row)
##############################################################
# 导出数据
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")
for it in range(len(export_title)):
worksheet.write(0, it, export_title[it])
for i in range(len(export_data)):
data_row = export_data[i]
for j in range(len(data_row)):
worksheet.write(i + 1, j, data_row[j])
pass
workbook.close()
return jsonify({"msg": msg, "filename": out_file_name})
def exportData():
if request.method == "POST":
msg = "成功"
f = request.files["file"]
filename = current_time() + secure_filename("".join(
lazy_pinyin(f.filename)))
# 保存文件
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)
# 获取要导出的列
items = {
"姓名": -1,
"考号": -1,
"班级": -1,
"总分": -1,
"语文": -1,
"数学": -1,
"英语": -1,
"物理": -1,
"化学": -1,
"道法": -1,
"历史": -1,
"地理": -1,
"生物": -1,
}
item_title = [
"考号",
"班级",
"姓名",
"语文",
"数学",
"英语",
"物理",
"化学",
"生物",
"历史",
"地理",
"道法",
"总分",
]
item_id_list = request.form.get("item_id_list").split(",")
it = 0
for item in items:
items[item] = int(item_id_list[it]) - 1
it = it + 1
if items[item] == -1:
item_title.remove(item)
pass
# 获取数据
data_table = []
index_data = int(request.form.get("index_data")) - 1
for i in range(index_data, table.nrows):
row_value = table.row_values(i)
data_row = []
for item in item_title:
if row_value[items[item]] == "":
data_row.append(0.0)
else:
data_row.append(row_value[items[item]])
pass
data_table.append(data_row)
pass
os.remove(upload_path)
# 计算排名
# 找到总分
index_total = -1
for i in range(len(item_title)):
if "总分" == item_title[i]:
index_total = i
pass
item_title.append("学校排名")
# 按总分排序
data_table.sort(key=itemgetter(index_total), reverse=True)
# data_table = sorted(data_table, key=(
# lambda x: x[index_total]), reverse=True)
for i in range(len(data_table)):
if i == 0:
data_table[i].append(i + 1)
else:
if data_table[i][index_total] == data_table[i -
1][index_total]:
data_table[i].append(data_table[i - 1][-1])
else:
data_table[i].append(i + 1)
pass
pass
# 找到班级
index_class = -1
for i in range(len(item_title)):
if "班级" == item_title[i]:
index_class = i
pass
item_title.append("班级排名")
# 按班级排序
data_table.sort(key=itemgetter(index_class))
for elt, items in groupby(data_table, itemgetter(index_class)):
it = 1
totol_old = 0
for i in items:
if it == 1:
i.append(it)
totol_old = i[index_total]
else:
if totol_old == i[index_total]:
i.append(i[-1])
else:
i.append(it)
pass
it = it + 1
pass
pass
# 导出数据
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")
it = 0
for it in range(len(item_title)):
worksheet.write(0, it, item_title[it])
for i in range(len(data_table)):
data_row = data_table[i]
for j in range(len(data_row)):
worksheet.write(i + 1, j, data_row[j])
pass
workbook.close()
return jsonify({"msg": msg, "filename": out_file_name})
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})
def exportData():
db = get_lijing_db()
year = session["year_current"]
person = "person_" + year
clas = "class_" + year
school = "school_" + year
job = "job_" + year
workload = "workload_" + year
rank = "rank_" + year
sql_search = (
"select p.person_name, class.class_name, school.school_name, job.job_name, work.lesson_number, work.year_result from "
+ person
+ " as p left join "
+ clas
+ " as class on p.person_id = class.person_id left join "
+ job
+ " as job on p.person_id = job.person_id left join "
+ workload
+ " as work on p.person_id = work.person_id left join "
+ school
+ " as school on job.school_id = school.school_id where p.person_id = ?"
)
sql_rank = (
"select subject, class_name, rank_up_school, rank_up_country, rank_down_school, rank_down_country from "
+ rank
+ " as r join "
+ clas
+ " as c on r.class_id = c.class_id where r.person_id = ?"
)
print(sql_rank)
flag_search = request.args.get("flag_search")
id_list = []
if flag_search == "true":
id_list = request.args.getlist("id_list[]")
else:
person_data = db.execute("select person_id from " + person).fetchall()
for i in person_data:
id_list.append(i["person_id"])
export_item = [
"person_name",
"class_name",
"school_name",
"job_name",
"lesson_number",
"year_result",
]
rank_item = [
"subject",
"class_name",
"rank_up_school",
"rank_up_country",
"rank_down_school",
"rank_down_country",
]
table_data = []
rank_number = 0
for i in id_list:
result = db.execute(sql_search, (i,)).fetchone()
rank = db.execute(sql_rank, (i,)).fetchall()
if len(rank) >= rank_number:
rank_number = len(rank)
row = []
for item in export_item:
row.append(result[item])
for r in rank:
for item in rank_item:
row.append(r[item])
table_data.append(row)
item_name_dict = {
"person_name": "姓名",
"class_name": "是否班主任",
"school_name": "所在分校",
"job_name": "行政职务",
"lesson_number": "总课时数",
"year_result": "年度考核",
}
rank_name_list = ["任教班级", "任教学科", "上学期全校排名", "上学期全县排名", "下学期全校排名", "下学期全县排名"]
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")
for i in range(len(export_item)):
worksheet.write(0, i, item_name_dict[export_item[i]])
num = 6
for i in range(0, rank_number):
for j in range(len(rank_name_list)):
worksheet.write(0, num + j, rank_name_list[j] + str(i + 1))
num = num + 6
for i in range(len(id_list)):
for j in range(len(table_data[i])):
worksheet.write(i + 1, j, table_data[i][j])
workbook.close()
msg = str(len(id_list))
return jsonify({"msg": msg, "filename": out_file_name})
def importData():
if request.method == "POST":
db = get_lijing_db()
# year_select = session['year_current']
# print(session['year_current'])
f = request.files["file"]
year_select = request.form.get("year")
item_id_list = request.form.get("item_id_list").split(",")
filename = secure_filename("".join(lazy_pinyin(f.filename)))
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)
item = [
"person_name",
"school_name",
"job_name",
"lesson_number",
"year_result",
"class_id",
"subject",
"rank_up_school",
"rank_up_country",
"rank_down_school",
"rank_down_country",
]
update_item = {}
for i in range(len(item)):
if item_id_list[i] != "0":
update_item[item[i]] = int(item_id_list[i]) - 1
print(update_item)
person_data = db.execute(
"select person_id, person_name from person_" + year_select
).fetchall()
person_id_dict = {}
person_name_list = []
for i in person_data:
person_id_dict[i["person_name"]] = i["person_id"]
person_name_list.append(i["person_name"])
for i in range(1, table.nrows):
row = table.row_values(i)
person_name = float_int_string(row[update_item["person_name"]])
if person_name not in person_name_list:
return jsonify({"msg": "教师姓名：“" + person_name + "” 不存在"})
# 表中有不存在基本信息的教师
person_id = person_id_dict[person_name]
if "lesson_number" in update_item:
lesson_number = float_int_string(row[update_item["lesson_number"]])
if (
db.execute(
"select workload_id from workload_"
+ year_select
+ " where person_id = ?",
(person_id,),
).fetchone()
is None
):
db.execute(
"insert into workload_"
+ year_select
+ " (lesson_number, year_result, person_id) values (?,?,?)",
(
lesson_number,
"",
person_id,
),
)
else:
db.execute(
"update workload_"
+ year_select
+ " set lesson_number = ? where person_id = ?",
(
lesson_number,
person_id,
),
)
if "year_result" in update_item:
year_result = float_int_string(row[update_item["year_result"]])
if (
db.execute(
"select workload_id from workload_"
+ year_select
+ " where person_id = ?",
(person_id,),
).fetchone()
is None
):
db.execute(
"insert into workload_"
+ year_select
+ " (lesson_number, year_result, person_id) values (?,?,?)",
(
"",
year_result,
person_id,
),
)
else:
db.execute(
"update workload_"
+ year_select
+ " set year_result = ? where person_id = ?",
(
year_result,
person_id,
),
)
if "school_name" in update_item:
school_name = float_int_string(row[update_item["school_name"]])
school_id_data = db.execute(
"select school_id from school_"
+ year_select
+ " where school_name = ?",
(school_name,),
).fetchone()
if school_id_data is None:
return jsonify({"msg": "分校：“" + school_name + "” 不存在"})
school_id = school_id_data["school_id"]
db.execute(
"update job_"
+ year_select
+ " set school_id = ? where person_id = ?",
(
school_id,
person_id,
),
)
if "job_name" in update_item:
job_name = float_int_string(row[update_item["job_name"]])
if job_name == "":
job_name = "无"
db.execute(
"update job_"
+ year_select
+ " set job_name = ? where person_id = ?",
(
job_name,
person_id,
),
)
if "class_id" in update_item:
school_name = row[update_item["school_name"]]
school_id = db.execute(
"select school_id from school_"
+ year_select
+ " where school_name = ?",
(school_name,),
).fetchone()["school_id"]
class_name = float_int_string(row[update_item["class_id"]])
class_id_data = db.execute(
"select class_id from class_"
+ year_select
+ " where school_id = ? and class_name = ? ",
(
school_id,
class_name,
),
).fetchone()
if class_id_data is None:
return jsonify(
{"msg": "分校“" + school_name + "”中不存在班级：“" + class_name + "”"}
)
class_id = class_id_data["class_id"]
subject = row[update_item["subject"]]
rank_data = db.execute(
"select rank_up_school from rank_"
+ year_select
+ " where person_id = ? and subject = ? and class_id = ?",
(
person_id,
subject,
class_id,
),
).fetchone()
if rank_data is None:
db.execute(
"insert into rank_"
+ year_select
+ " (subject, class_id, person_id, rank_up_school, rank_up_country, rank_down_school, rank_down_country) values (?,?,?,?,?,?,?)",
(
subject,
class_id,
person_id,
"暂无",
"暂无",
"暂无",
"暂无",
),
)
if "rank_up_school" in update_item:
rank_up_school = float_int_string(row[update_item["rank_up_school"]])
db.execute(
"update rank_"
+ year_select
+ " set rank_up_school = ? where subject = ? and person_id = ? and class_id = ?",
(
rank_up_school,
subject,
person_id,
class_id,
),
)
if "rank_up_country" in update_item:
rank_up_country = float_int_string(row[update_item["rank_up_country"]])
db.execute(
"update rank_"
+ year_select
+ " set rank_up_country = ? where subject = ? and person_id = ? and class_id = ?",
(
rank_up_country,
subject,
person_id,
class_id,
),
)
if "rank_down_school" in update_item:
rank_down_school = float_int_string(
row[update_item["rank_down_school"]]
)
db.execute(
"update rank_"
+ year_select
+ " set rank_down_school = ? where subject = ? and person_id = ? and class_id = ?",
(
rank_down_school,
subject,
person_id,
class_id,
),
)
if "rank_down_country" in update_item:
rank_down_country = float_int_string(
row[update_item["rank_down_country"]]
)
db.execute(
"update rank_"
+ year_select
+ " set rank_down_country = ? where subject = ? and person_id = ? and class_id = ?",
(
rank_down_country,
subject,
person_id,
class_id,
),
)
db.commit()
os.remove(upload_path)
return jsonify({"msg": "成功导入"})
def exportData():
db = get_lijing_db()
year = session["year_current"]
sql = (
"select p.person_name, school_name, honor_time, get_time, honor_unit, honor_name, honor_grade, honor_number, honor_remark from person_"
+ year + " as p left join honor_" + year +
" as h on p.person_id = h.person_id where p.person_id = ?")
flag_search = request.args.get("flag_search")
id_list = []
if flag_search == "true":
id_list = request.args.getlist("id_list[]")
else:
person_data = db.execute("select person_id from person_" +
year).fetchall()
for i in person_data:
id_list.append(i["person_id"])
export_item = [
"person_name",
"honor_name",
"honor_grade",
"honor_time",
"honor_number",
"honor_unit",
"get_time",
"school_name",
"honor_remark",
]
table_data = []
for i in id_list:
result = db.execute(sql, (i, )).fetchall()
row = []
for r in result:
for item in export_item:
row.append(r[item])
table_data.append(row)
item_name_dict = {
"person_name": "姓名",
"honor_name": "获奖名称",
"honor_grade": "证书级别",
"honor_time": "发证时间",
"honor_number": "证书编号",
"honor_unit": "发证单位",
"get_time": "获奖时间",
"school_name": "获奖时所在分校",
"honor_remark": "备注",
}
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")
for i in range(len(export_item)):
worksheet.write(0, i, item_name_dict[export_item[i]])
for i in range(len(table_data)):
for j in range(len(export_item)):
worksheet.write(i + 1, j, table_data[i][j])
workbook.close()
msg = str(len(table_data))
return jsonify({"msg": msg, "filename": out_file_name})
def importData():
if request.method == "POST":
db = get_lijing_db()
# year_select = request.form.get('year_select')
year_select = session["year_current"]
f = request.files["file"]
filename = secure_filename("".join(lazy_pinyin(f.filename)))
if filename.endswith(".xlsx"):
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 = {
"姓名": 0,
"所在分校": 1,
"发证时间": 2,
"获得时间": 3,
"发证单位": 4,
"获奖名称": 5,
"证书级别": 6,
"证书编号": 7,
"备注": 8,
}
row_title = table.row_values(0)
title_id = [-1 for i in range(9)]
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
# 教师基本信息
person_data = db.execute(
"select person_id, person_name from person_" +
year_select).fetchall()
person_id_dict = {}
# person_name_list = []
for i in person_data:
person_id_dict[i["person_name"]] = i["person_id"]
# person_name_list.append(i['person_name'])
# 导入数据
for i in range(1, table.nrows):
row_value = table.row_values(i)
person_name = float_int_string(row_value[title_id[0]])
if person_name not in person_id_dict:
error = "教师姓名：“" + person_name + "” 不存在"
flash(error)
return redirect(url_for("lijing_honorinfo.hello"))
# 表中有不存在基本信息的教师
person_id = person_id_dict[person_name]
print(person_id, person_name)
insert_data = [0 for i in range(9)]
for j in range(0, len(title_id)):
if title_id[j] == -1:
insert_data[j] = "待添加"
else:
insert_data[j] = float_int_string(
row_value[title_id[j]])
if len(insert_data[j]) == 0:
insert_data[j] = "待添加"
print(insert_data)
db.execute(
"insert into honor_" + year_select +
" (person_id, school_name, honor_time, get_time, honor_unit, honor_name, honor_grade, honor_number, honor_remark) values (?,?,?,?,?,?,?,?,?)",
(
person_id,
insert_data[1],
insert_data[2],
insert_data[3],
insert_data[4],
insert_data[5],
insert_data[6],
insert_data[7],
insert_data[8],
),
)
db.commit()
os.remove(upload_path)
return redirect(url_for("lijing_honorinfo.hello"))
else:
error = "请导入xlsx格式的文件"
flash(error)
return redirect(url_for("lijing_honorinfo.hello"))
def calculator_bonus():
if request.method == "POST":
msg = "成功"
# 接收文件
f = request.files["file"]
filename = secure_filename("".join(lazy_pinyin(f.filename)))
# 接收参数
item_value = request.form.get("item_value").split(",")
item_subject = [
"chinese",
"math",
"english",
"wl",
"hx",
"zz",
"ls",
"dl",
"sw",
]
item = [
"chinese",
"math",
"english",
"wl",
"hx",
"zz",
"ls",
"dl",
"sw",
"class1",
"class2",
"bonus1",
"bonus2",
"ebonus1",
"ebonus2",
"bonus_average",
"bonus3",
]
# 参数字典
value_dict = {}
for i in range(len(item)):
value_dict[item[i]] = float(item_value[i])
# 科目系数
item_coefficient = []
for i in item_subject:
item_coefficient.append(value_dict[i])
# 判断文件格式
if filename.endswith(".xlsx"):
# 保存文件
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)
# 标题栏
table_title = table.row_values(0)
item_average = [
"语文人平",
"数学人平",
"英语人平",
"物理人平",
"化学人平",
"道法人平",
"历史人平",
"地理人平",
"生物人平",
]
item_teacher = [
"语文教师",
"数学教师",
"英语教师",
"物理教师",
"化学教师",
"道法教师",
"历史教师",
"地理教师",
"生物教师",
]
# 日志数据
log = {}
# 奖金数据
bonus_dict = {}
# 标题序号
id_dict = {}
for i in range(len(table_title)):
if table_title[i] == "班级":
id_dict["班级"] = i
if table_title[i] in item_average:
id_dict[table_title[i]] = i
if table_title[i] in item_teacher:
id_dict[table_title[i]] = i
if "一类" in table_title[i]:
id_dict["一类"] = i
if "二类" in table_title[i]:
id_dict["二类"] = i
# 检查是否有班级数据
if "班级" not in id_dict:
msg = "找不到班级数据，读取失败\n"
return jsonify({"msg": msg})
# 获取班级数据
class_list = []
for i in range(1, table.nrows):
table_row = table.row_values(i)
class_list.append(float_int_string(table_row[id_dict["班级"]]))
class_list.remove("全校人平")
# 获取老师列表
teacher_list = []
for item in item_teacher:
for i in range(1, table.nrows):
table_row = table.row_values(i)
if item in id_dict:
if table_row[id_dict[item]] != "":
teacher_list.append(table_row[id_dict[item]])
teacher_list = list(set(teacher_list))
print(teacher_list)
teacher_list = sorted(teacher_list)
# 平均分奖金计算
# 按九个科目依次计算
for i in range(9):
# 没有某科目老师
if item_teacher[i] in id_dict:
teacher_id = id_dict[item_teacher[i]]
else:
continue
# 没有某科目平均分
if item_average[i] in id_dict:
average_id = id_dict[item_average[i]]
else:
continue
# 拿到某科目全校平均分
average_total = table.row_values(table.nrows - 1)[average_id]
# 计算每行数据
for row in range(1, table.nrows - 1):
table_row = table.row_values(row)
class_name = table_row[id_dict["班级"]]
teacher = table_row[teacher_id]
average = table_row[average_id]
diff = round((average - average_total), 1)
# 日志
log_string = []
log_string.append(float_int_string(class_name) + "班")
log_string.append(item_average[i])
log_string.append(str(average))
log_string.append(str(average_total))
log_string.append("%.2f-%.2f=%.2f" %
(average, average_total, diff))
# 判断是否有老师奖金的信息
if teacher in bonus_dict:
sum = bonus_dict[teacher]
else:
# 没有某老师信息则初始化
log[teacher] = teacher + "\n"
sum = 0
# 计算基本奖金：100*系数
basic = value_dict["bonus_average"] * item_coefficient[i]
log_string.append("%.2f*%.2f=%.2f" %
(value_dict["bonus_average"],
item_coefficient[i], basic))
# 计算额外奖金：多一分8元
addition = value_dict["bonus3"] * diff
log_string.append("%.2f*%.2f=%.2f" %
(value_dict["bonus3"], diff, addition))
# 奖金相加
bonus_dict[teacher] = sum + basic + addition
log_string.append(
"%.2f+%.2f=%.2f" %
(sum, basic + addition, bonus_dict[teacher]))
# 保存日志
log[teacher] = log[teacher] + "\t".join(log_string) + "\n"
# 计算系数和
total_coefficient = 0
for i in range(len(item_teacher)):
if item_teacher[i] in id_dict:
total_coefficient = total_coefficient + item_coefficient[i]
total_coefficient = round(total_coefficient, 2)
# 计算尖子生奖金
# 按每行一个班级计算
for i in range(1, table.nrows - 1):
# 日志
log_string = []
table_row = table.row_values(i)
class_name = float_int_string(table_row[id_dict["班级"]]) + "班"
log_string.append(class_name)
# 尖子生个数
class1 = table_row[id_dict["一类"]]
class2 = table_row[id_dict["二类"]]
log_string.append("一类：%d个，二类：%d个" % (class1, class2))
# 尖子生差距
diff1 = class1 - value_dict["class1"]
diff2 = class2 - value_dict["class2"]
# 尖子生奖金
bonus1 = value_dict["bonus1"] + value_dict["ebonus1"] * diff1
bonus2 = value_dict["bonus2"] + value_dict["ebonus2"] * diff2
log_string.append("一类奖金：%d，二类奖金：%d" % (bonus1, bonus2))
# 总奖金
bonus_total = bonus1 + bonus2
log_string.append("总奖金：%d" % (bonus_total))
# 计算某个班级的老师奖金
for j in range(len(item_teacher)):
# 没有某科目老师
if item_teacher[j] not in id_dict:
continue
# 老师名称
teacher_id = id_dict[item_teacher[j]]
teacher = table_row[teacher_id]
log_string.append("\n\t%s：%s" % (item_teacher[j], teacher))
# 奖金比例
ratio = item_coefficient[j] / total_coefficient
log_string.append(
"%.2f/%.2f=%.2f" %
(item_coefficient[j], total_coefficient, ratio))
# 尖子生奖金
bonus_class = bonus_total * ratio
log_string.append("%.2f*%.2f=%.2f" %
(bonus_total, ratio, bonus_class))
# 汇入奖金数据
origin = bonus_dict[teacher]
bonus_dict[teacher] = origin + bonus_class
log[teacher] = log[teacher] + "%s %.2f+%.2f=%.2f\n" % (
class_name,
origin,
bonus_class,
bonus_dict[teacher],
)
# 日志保存
log[class_name] = "\t".join(log_string) + "\n"
# 输出数据到表格
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")
worksheet.write(0, 0, "教师")
worksheet.write(0, 1, "奖金")
for i in range(len(teacher_list)):
teacher = teacher_list[i]
worksheet.write(i + 1, 0, teacher)
# 四舍五入
bonus_dict[teacher] = int(bonus_dict[teacher] + 0.5)
worksheet.write(i + 1, 1, bonus_dict[teacher])
workbook.close()
os.remove(upload_path)
else:
msg = "请导入xlsx格式的文件\n"
return jsonify({"msg": msg})
return jsonify({
"msg": msg,
"class_list": class_list,
"teacher_list": teacher_list,
"log": log,
"filename": out_file_name,
})