def search(): person_id = request.args.get("id", 0, type=int) db = get_lijing_db() data = [] item = [ "school_name", "honor_time", "get_time", "honor_unit", "honor_name", "honor_grade", "honor_number", "honor_remark", ] result = db.execute( "select school_name, honor_time, get_time, honor_unit, honor_name, honor_grade, honor_number, honor_remark from honor_" + session["year_current"] + " where person_id = ?", (person_id, ), ).fetchall() for row in result: d = {} for i in range(len(row)): d[item[i]] = row[item[i]] data.append(d) return jsonify({"data_list": data})
def jsondata(): db = get_lijing_db() data = [] try: result = db.execute( 'select person_id, person_name, gender from person_' + session['year_current']).fetchall() for row in result: d = {} d['id'] = row['person_id'] d['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) # 分片数,(页码-1)*limit,它表示一段数据的起点 # print('get', limit) # print('get offset', offset) 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) db = get_lijing_db() sql = ( "select p.person_id, p.person_name, count(h.person_id) num from person_" + session["year_current"] + " as p LEFT JOIN honor_" + session["year_current"] + " as h on p.person_id = h.person_id where " + search_item + " like '%" + search_string + "%' GROUP by p.person_id;") result = db.execute(sql).fetchall() data = [] for row in result: d = {} d["id"] = row["person_id"] d["name"] = row["person_name"] if row["num"] == 0: d["num"] = "无" else: d["num"] = row["num"] data.append(d) msg = "成功查询到" + str(len(data)) + "条信息" return jsonify({"msg": msg, "total": len(data), "rows": data})
def delete_data(): person_id = int(request.args.get("person_id")) class_name = request.args.get("class_name") subject = request.args.get("subject") db = get_lijing_db() year = session["year_current"] class_id = db.execute( "select class_id from class_" + year + " where class_name = ?", (class_name,) ).fetchone()["class_id"] db.execute( "delete from rank_" + year + " where person_id = ? and class_id = ? and subject = ?", ( person_id, class_id, subject, ), ) db.commit() person_name = db.execute( "select person_name from person_" + year + " where person_id = ?", (person_id,) ).fetchone()["person_name"] return jsonify( {"msg": "成功删除记录(" + person_name + "," + class_name + "班," + subject + ")"} )
def add_data(): if request.method == "POST": person_name = request.form["person_name"] db = get_lijing_db() # 教师基本信息 person_data = db.execute("select person_id, person_name from person_" + session["year_current"]).fetchall() person_id_dict = {} # person_name_list = [] for i in person_data: person_id_dict[i["person_name"]] = i["person_id"] if person_name not in person_id_dict: flash("教师 “" + person_name + "” 不存在") return redirect(url_for("lijing_honorinfo.hello")) person_id = person_id_dict[person_name] item = [ "school_name", "honor_time", "get_time", "honor_unit", "honor_name", "honor_grade", "honor_number", "honor_remark", ] add_data = [] for i in item: add_data.append(request.form[i]) db.execute( "insert into honor_" + session["year_current"] + " (person_id, school_name, honor_time, get_time, honor_unit, honor_name, honor_grade, honor_number, honor_remark) values (?,?,?,?,?,?,?,?,?)", ( person_id, add_data[0], add_data[1], add_data[2], add_data[3], add_data[4], add_data[5], add_data[6], add_data[7], ), ) db.commit() return redirect(url_for("lijing_honorinfo.hello"))
def add_school(): school_name = request.args.get("school") year_select = request.args.get("year") db = get_lijing_db() school_data = db.execute("select school_name from school_" + year_select).fetchall() school_list = [] for s in school_data: school_list.append(s["school_name"]) if school_name in school_list: return jsonify({"msg": "error", "error": "分校已经存在"}) sql_school = "insert into school_" + year_select + " (school_name) values (?)" db.execute(sql_school, (school_name,)) db.commit() school_dict = {} school_data = db.execute( "select school_id, school_name from school_" + year_select ).fetchall() for i in school_data: school_dict[i["school_name"]] = i["school_id"] class_list = {} for i in school_dict: class_data = db.execute( "select class_name from class_" + year_select + " where school_id = ?", (school_dict[i],), ).fetchall() clas = [] for j in class_data: clas.append(j["class_name"]) class_list[str(i)] = clas school_list = list(school_dict.keys()) if len(school_dict.keys()) != 1: school_list = school_list[1:] del class_list["暂无分校"] return jsonify( { "msg": "成功添加" + school_name, "school_list": school_list, "class_list": class_list, } )
def hello(): db = get_lijing_db() year_data = db.execute('select year from year_list').fetchall() year_new = datetime.datetime.now().year year_list = [] if len(year_data) == 0: year_list = ['暂无数据'] else: for year in year_data: year_list.insert(0, year['year']) year_new = int(year_list[0]) + 1 # if 'year_current' not in session: session['year_current'] = year_list[0] return render_template('lijing/basicInfo.html', year_list=year_list, year_new=year_new)
def hello(): db = get_lijing_db() year_data = db.execute("select year from year_list").fetchall() year_new = datetime.datetime.now().year year_list = [] if len(year_data) == 0: year_list = ["暂无数据"] else: for year in year_data: year_list.insert(0, year["year"]) year_new = int(year_list[0]) + 1 session["year_current"] = year_list[0] return render_template( "lijing/basicInfo.html", year_list=year_list, year_new=year_new )
def importData(): if request.method == 'POST': db = get_lijing_db() year_select = request.form.get('year_select') sql_data = {} with open('flaskr\sql_lijing.json', 'r') as f: sql_data = json.load(f) sql_create = ['', '', '', ''] sql_create[0] = 'CREATE TABLE person_' + \ year_select+sql_data['person'] sql_create[1] = 'CREATE TABLE education_' + \ year_select+sql_data['education'] sql_create[2] = 'CREATE TABLE skill_' + \ year_select+sql_data['skill'] sql_create[3] = 'CREATE TABLE workinfo_' + \ year_select+sql_data['workinfo'] year_data = db.execute('select year from year_list').fetchall() year_list = [] # year_new = datetime.datetime.now().year 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, )) for sql in sql_create: db.execute(sql) db.commit() return redirect(url_for('lijing_basicinfo.hello'))
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 jsondata(): db = get_lijing_db() data = [] try: sql = ( "select p.person_id, p.person_name, count(h.person_id) num from person_" + session["year_current"] + " as p LEFT JOIN honor_" + session["year_current"] + " as h on p.person_id = h.person_id GROUP by p.person_id") result = db.execute(sql).fetchall() for row in result: d = {} d["id"] = row["person_id"] d["name"] = row["person_name"] if row["num"] == 0: d["num"] = "无" else: d["num"] = row["num"] 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) # 分片数,(页码-1)*limit,它表示一段数据的起点 # print('get', limit) # print('get offset', offset) return jsonify({ "total": len(data), "rows": data[int(offset):(int(offset) + int(limit))] })
def hello(): db = get_lijing_db() year_data = db.execute("select year from year_list").fetchall() year_list = [] if len(year_data) == 0: year_list = ["暂无数据"] session["year_current"] = year_list[0] return render_template("lijing/honorInfo.html", year_list=year_list) else: for year in year_data: year_list.insert(0, year["year"]) session["year_current"] = year_list[0] flag_table = db.execute("select honorinfo from year_list where year = ?", (year_list[0], )).fetchone()["honorinfo"] if flag_table == 0: # sql_data = {} # with open('flaskr\sql_lijing.json', 'r') as f: # sql_data = json.load(f) # sql_create = 'CREATE TABLE honor_' + \ # session['year_current'] + sql_data['honor'] # db.execute(sql_create) create_table(["honor"], session["year_current"]) db.execute( "update year_list set honorinfo = 1 where year = ?", (session["year_current"], ), ) db.commit() return render_template("lijing/honorInfo.html", year_list=year_list)
def update_data(): item = [ "person_name", "school_name", "job_name", "lesson_number", "year_result", "class_master", ] rank_item = [ "class_id", "subject", "rank_up_school", "rank_up_country", "rank_down_school", "rank_down_country", ] update_data = {} update_data["person_id"] = int(request.args.get("person_id")) for i in item: update_data[i] = request.args.get(i) rank_number = int(request.args.get("rank_number")) rank_data = [] for i in range(rank_number): rank = [] for r in rank_item: rank.append(request.args.get(r + str(i))) rank_data.append(rank) db = get_lijing_db() year_select = session["year_current"] person_id = update_data["person_id"] school_name = update_data["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": "error", "error": "分校:“" + 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, ), ) job_name = update_data["job_name"] db.execute( "update job_" + year_select + " set job_name = ? where person_id = ?", ( job_name, person_id, ), ) lesson_number = update_data["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, ), ) year_result = update_data["year_result"] db.execute( "update workload_" + year_select + " set year_result = ? where person_id = ?", ( year_result, person_id, ), ) # 更新排名信息 for rank in rank_data: class_name = rank[0] 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": "error", "error": "分校“" + school_name + "”中不存在班级:“" + class_name + "”", } ) class_id = class_id_data["class_id"] subject = rank[1] 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, rank[2], rank[3], rank[4], rank[5], ), ) else: # db.execute('delete from rank_'+year_select + # ' where person_id = ? and subject = ? and class_id = ?', (person_id, subject, class_id, )) db.execute( "update rank_" + year_select + " set rank_up_school = ?, rank_up_country = ?, rank_down_school = ?, rank_down_country = ? where person_id = ? and subject = ? and class_id = ?", ( rank[2], rank[3], rank[4], rank[5], person_id, subject, class_id, ), ) # 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, rank[2], rank[3], rank[4], rank[5], )) db.commit() return jsonify({"msg": "成功更新教师“" + update_data["person_name"] + "”的业务档案"})
def search(): person_id = request.args.get("id") db = get_lijing_db() datas = {} item = [ "person_name", "school_name", "job_name", "lesson_number", "year_result", "class_master", ] year = session["year_current"] person_name = db.execute( "select person_name from person_" + year + " where person_id = ?", (person_id,) ).fetchone()["person_name"] job_data = db.execute( "select school_id, job_name from job_" + year + " where person_id = ?", (person_id,), ).fetchone() school_id = job_data["school_id"] job_name = job_data["job_name"] school_name = db.execute( "select school_name from school_" + year + " where school_id = ?", (school_id,) ).fetchone()["school_name"] workload_data = db.execute( "select lesson_number, year_result from workload_" + year + " where person_id = ?", (person_id,), ).fetchone() if workload_data is None: lesson_number = "暂无" year_result = "暂无" else: lesson_number = workload_data["lesson_number"] year_result = workload_data["year_result"] class_data = db.execute( "select class_name from class_" + year + " where person_id = ?", (person_id,) ).fetchone() if class_data is None: class_master = "否" else: class_master = class_data["class_name"] datas["person_name"] = person_name datas["school_name"] = school_name datas["job_name"] = job_name datas["lesson_number"] = lesson_number datas["year_result"] = year_result datas["class_master"] = class_master rank_data = db.execute( "select subject, class_id, rank_up_school, rank_up_country, rank_down_school, rank_down_country from rank_" + year + " where person_id = ?", (person_id,), ).fetchall() rank_list = [] for i in rank_data: d = [] d.append( db.execute( "select class_name from class_" + year + " where class_id = ?", (i["class_id"],), ).fetchone()["class_name"] ) d.append(i["subject"]) d.append(i["rank_up_school"]) d.append(i["rank_up_country"]) d.append(i["rank_down_school"]) d.append(i["rank_down_country"]) rank_list.append(d) datas["rank"] = rank_list return jsonify(datas)
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 del_class(): year_select = request.args.get("year") school_name = request.args.get("school") class_name = request.args.get("class") db = get_lijing_db() school_id = db.execute( "select school_id from school_" + year_select + " where school_name = ?", (school_name,), ).fetchone()["school_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": "error", "error": "分校 “" + school_name + "” 中不存在" + class_name + "班,删除班级失败", } ) class_id = class_id_data["class_id"] rank_data = db.execute( "select person_id from rank_" + year_select + " where class_id = ?", (class_id,) ).fetchone() if rank_data is not None: return jsonify( {"msg": "error", "error": "存在教师在班级 “" + class_name + "” 中任课,删除班级失败"} ) sql_class = "delete from class_" + year_select + " where class_id = ?" db.execute(sql_class, (class_id,)) db.commit() school_dict = {} school_data = db.execute( "select school_id, school_name from school_" + year_select ).fetchall() for i in school_data: school_dict[i["school_name"]] = i["school_id"] class_list = {} for i in school_dict: class_data = db.execute( "select class_name from class_" + year_select + " where school_id = ?", (school_dict[i],), ).fetchall() clas = [] for j in class_data: clas.append(j["class_name"]) class_list[str(i)] = clas school_list = list(school_dict.keys()) if len(school_dict.keys()) != 1: school_list = school_list[1:] del class_list["暂无分校"] return jsonify( { "msg": "成功删除班级:" + class_name, "school_list": school_list, "class_list": class_list, } )
def hello(): db = get_lijing_db() year_data = db.execute("select year from year_list").fetchall() year_list = [] if len(year_data) == 0: year_list = ["暂无数据"] session["year_current"] = year_list[0] return render_template( "lijing/workInfo.html", year_list=year_list, school_list=["暂无分校"], class_list={}, ) else: for year in year_data: year_list.insert(0, year["year"]) # if 'year_current' not in session: session["year_current"] = year_list[0] flag_table = db.execute( "select workinfo from year_list where year = ?", (year_list[0],) ).fetchone()["workinfo"] if flag_table == 0: # sql_data = {} # with open('flaskr\sql_lijing.json', 'r') as f: # sql_data = json.load(f) # sql_create = ['', '', '', '', ''] # sql_create[0] = 'CREATE TABLE school_' + \ # session['year_current'] + sql_data['school'] # sql_create[1] = 'CREATE TABLE job_' + \ # session['year_current']+sql_data['job'] # sql_create[2] = 'CREATE TABLE class_' + \ # session['year_current']+sql_data['class'] # sql_create[3] = 'CREATE TABLE rank_' + \ # session['year_current']+sql_data['rank'] # sql_create[4] = 'CREATE TABLE workload_' + \ # session['year_current']+sql_data['workload'] # for sql in sql_create: # db.execute(sql) create_table( ["school", "job", "class", "rank", "workload"], session["year_current"] ) db.execute( "insert into school_" + session["year_current"] + " (school_name) values (?)", ("暂无分校",), ) db.execute( "update year_list set workinfo = 1 where year = ?", (session["year_current"],), ) db.commit() school_list = [] school_id = [] school_data = db.execute( "select school_id, school_name from school_" + session["year_current"] ).fetchall() for i in school_data: school_list.append(i["school_name"]) school_id.append(i["school_id"]) class_list = {} for i in range(len(school_list)): class_data = db.execute( "select class_name from class_" + session["year_current"] + " where school_id = ?", (school_id[i],), ).fetchall() clas = [] for j in class_data: clas.append(j["class_name"]) class_list[str(school_list[i])] = clas if len(school_list) != 1: school_list = school_list[1:] del class_list["暂无分校"] return render_template( "lijing/workInfo.html", year_list=year_list, school_list=school_list, class_list=class_list, )
def del_school(): school_name = request.args.get("school") year_select = request.args.get("year") db = get_lijing_db() school_data = db.execute("select school_name from school_" + year_select).fetchall() school_list = [] for s in school_data: school_list.append(s["school_name"]) if school_name not in school_list: return jsonify({"msg": "error", "error": "分校 “" + school_name + "” 不存在"}) school_id = db.execute( "select school_id from school_" + year_select + " where school_name = ?", (school_name,), ).fetchone()["school_id"] if ( db.execute( "select person_id from job_" + year_select + " where school_id = ?", (school_id,), ).fetchone() is not None ): return jsonify( {"msg": "error", "error": "存在教师属于分校 “" + school_name + "” ,删除分校失败"} ) sql_class = "delete from class_" + year_select + " where school_id = ?" sql_school = "delete from school_" + year_select + " where school_name = ?" db.execute(sql_class, (school_id,)) db.execute(sql_school, (school_name,)) db.commit() school_dict = {} school_data = db.execute( "select school_id, school_name from school_" + year_select ).fetchall() for i in school_data: school_dict[i["school_name"]] = i["school_id"] class_list = {} for i in school_dict: class_data = db.execute( "select class_name from class_" + year_select + " where school_id = ?", (school_dict[i],), ).fetchall() clas = [] for j in class_data: clas.append(j["class_name"]) class_list[str(i)] = clas school_list = list(school_dict.keys()) if len(school_dict.keys()) != 1: school_list = school_list[1:] del class_list["暂无分校"] return jsonify( { "msg": "成功删除分校:" + school_name, "school_list": school_list, "class_list": class_list, } )
def add_class(): year_select = request.args.get("year") school_name = request.args.get("school") class_name = request.args.get("class") person_name = request.args.get("person") # print(year_select, school_name, class_name, person_name) db = get_lijing_db() school_id = db.execute( "select school_id from school_" + year_select + " where school_name = ?", (school_name,), ).fetchone()["school_id"] person_data = db.execute( "select person_id from person_" + year_select + " where person_name = ?", (person_name,), ).fetchone() class_data = db.execute( "select class_name from class_" + year_select + " where school_id = ?", (school_id,), ).fetchall() class_list = [] for clas in class_data: class_list.append(clas["class_name"]) # print(school_id, person_data, class_list) if class_name in class_list: return jsonify({"msg": "error", "error": "班级已经存在"}) if person_data == None: return jsonify({"msg": "error", "error": "不存在该教师"}) sql_class = ( "insert into class_" + year_select + "(class_name, school_id, person_id) values (?,?,?)" ) db.execute( sql_class, ( class_name, school_id, person_data["person_id"], ), ) db.commit() school_dict = {} school_data = db.execute( "select school_id, school_name from school_" + year_select ).fetchall() for i in school_data: school_dict[i["school_name"]] = i["school_id"] class_lists = {} for i in school_dict: class_data = db.execute( "select class_name from class_" + year_select + " where school_id = ?", (school_dict[i],), ).fetchall() clas = [] for j in class_data: clas.append(j["class_name"]) class_lists[str(i)] = clas school_list = list(school_dict.keys()) if len(school_dict.keys()) != 1: school_list = school_list[1:] del class_lists["暂无分校"] return jsonify( {"msg": "成功添加班级", "school_list": school_list, "class_list": class_lists} )
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 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 update_data(): person_id = request.args.get("person_id") update_number = request.args.get("update_number", type=int) print(person_id, update_number) print(type(person_id), type(update_number)) item = [ "school_name", "honor_time", "get_time", "honor_unit", "honor_name", "honor_grade", "honor_number", "honor_remark", ] honor_data = [] for i in range(update_number): honor = [] for r in item: honor.append(request.args.get(r + str(i))) honor_data.append(honor) db = get_lijing_db() honor_id_data = db.execute( "select honor_id from honor_" + session["year_current"] + " where person_id = ?", (person_id, ), ).fetchall() honor_id = [] for i in honor_id_data: honor_id.append(i["honor_id"]) print(honor_id) print(honor_data) for i in range(update_number): db.execute( "update honor_" + session["year_current"] + " set school_name = ?, honor_time = ?, get_time = ?, honor_unit = ?, honor_name = ?, honor_grade = ?, honor_number = ?, honor_remark = ? where honor_id = ?", ( honor_data[i][0], honor_data[i][1], honor_data[i][2], honor_data[i][3], honor_data[i][4], honor_data[i][5], honor_data[i][6], honor_data[i][7], honor_id[i], ), ) db.commit() person_name = db.execute( "select person_name from person_" + session["year_current"] + " where person_id = ?", (person_id, ), ).fetchone()["person_name"] msg = "成功修改教师“" + person_name + "”的荣誉档案" return jsonify({"msg": msg})
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 search_data(): search_item = request.args.get("search_item", "暂无", type=str) search_string = request.args.get("search_string", "暂无", type=str) search_school = request.args.get("school_select", "暂无", type=str) db = get_lijing_db() year = session["year_current"] print(search_school, search_item, search_string) # var item = { # '姓名': 'person_name', '所在分校': 'school_name', '行政职务': 'job_name', '总课时数': 'lesson_number', # '年度考核': 'year_result', '班主任': 'class_master', '任教班级': 'class_name', '任教学科': 'subject' # }; person_dict = {} if search_school != "#全部数据": person_data = db.execute( "select person_" + year + ".person_id, person_name from job_" + year + " join school_" + year + ", person_" + year + " on job_" + year + ".school_id = school_" + year + ".school_id and job_" + year + ".person_id = person_" + year + ".person_id where school_name = ?", (search_school,), ).fetchall() else: person_data = db.execute( "select person_id, person_name from person_" + year ).fetchall() for person in person_data: person_dict[person["person_id"]] = person["person_name"] data = [] if search_item == "person_name": result = db.execute( "select person_id from person_" + year + ' where person_name like "%' + search_string + '%"' ).fetchall() if search_item == "school_name": school_data = db.execute( "select school_id from school_" + year + ' where school_name like "%' + search_string + '%"' ).fetchone() if school_data is None: return jsonify({"msg": "不存在该分校", "total": len(data), "rows": data}) result = db.execute( "select person_id from job_" + year + " where school_id = ?", (school_data["school_id"],), ).fetchall() if search_item == "job_name": result = db.execute( "select person_id from job_" + year + ' where job_name like "%' + search_string + '%"' ).fetchall() if search_item == "lesson_number": result = db.execute( "select person_id from workload_" + year + ' where lesson_number like "%' + search_string + '%"' ).fetchall() if search_item == "year_result": result = db.execute( "select person_id from workload_" + year + ' where year_result like "%' + search_string + '%"' ).fetchall() if search_item == "class_master": result = db.execute( "select person_id from class_" + year + ' where class_name like "%' + search_string + '%"' ).fetchall() if search_item == "class_name": class_data = db.execute( "select class_id from class_" + year + ' where class_name like "%' + search_string + '%"' ).fetchall() if len(class_data) == 0: return jsonify({"msg": "不存在该班级", "total": len(data), "rows": data}) elif len(class_data) == 1: result = db.execute( "select person_id from rank_" + year + " where class_id = ?", (class_data[0]["class_id"],), ).fetchall() else: if search_school == "#全部数据": return jsonify({"msg": "请选择分校", "total": len(data), "rows": data}) else: class_id = db.execute( "select class_id from class_" + year + " join school_" + year + " on class_" + year + ".school_id = school_" + year + '.school_id where class_name like "%' + search_string + '%"' ).fetchone()["class_id"] result = db.execute( "select person_id from rank_" + year + " where class_id = ?", (class_id,), ).fetchall() if search_item == "subject": result = db.execute( "select person_id from rank_" + year + ' where subject like "%' + search_string + '%"' ).fetchall() person_id_list = [] for i in result: person_id = i["person_id"] if type(person_id) != int: person_id = int(person_id) if person_id not in person_dict: continue person_id_list.append(person_id) person_id_list = list(set(person_id_list)) for person_id in person_id_list: d = {} d["id"] = person_id d["name"] = person_dict[person_id] data.append(d) msg = "成功查询到 " + str(len(data)) + " 条信息" return jsonify({"msg": msg, "total": len(data), "rows": data})
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 jsondata(): db = get_lijing_db() data = [] year = session["year_current"] if year == "暂无数据": return jsonify({"total": len(data), "rows": data}) person = "person_" + year rank = "rank_" + year clas = "class_" + year school = "school_" + year job = "job_" + year person_list = [] person_id_list = [] person_data = db.execute("select person_id, person_name from " + person).fetchall() for i in person_data: person_list.append(i["person_name"]) person_id_list.append(i["person_id"]) school_name_list = [] for i in person_id_list: school_data = db.execute( "select school_name from " + job + " join " + school + " on " + job + ".school_id = " + school + ".school_id where person_id = ?", (i,), ).fetchone() if school_data is None: db.execute( "insert into " + job + " (job_name, school_id, person_id) values (?,?,?)", ( "无", 1, i, ), ) db.commit() school_name_list.append("暂无分校") else: school_name_list.append(school_data["school_name"]) for i in range(len(person_id_list)): d = {} d["id"] = person_id_list[i] d["name"] = person_list[i] d["school"] = school_name_list[i] data.append(d) if request.method == "POST": print("post") if request.method == "GET": info = request.values limit = info.get("limit", 10) # 每页显示的条数 offset = info.get("offset", 0) # 分片数,(页码-1)*limit,它表示一段数据的起点 # print('get', limit) # print('get offset', offset) return jsonify( {"total": len(data), "rows": data[int(offset) : (int(offset) + int(limit))]} )