def create():
data = dict(
title="錄入員工"
)
if request.method == "POST":
res = dict(code=0)
form = EmployeeAddForm()
if form.validate():
emp_id = save_employee(form)
session = ORM.db()
try:
for v in form.hobby.data:
hobby = Hobby(
employee_id=emp_id,
hobby_key=int(v),
createdAt=dt(),
updatedAt=dt()
)
session.add(hobby)
except Exception as e:
session.rollback()
else:
session.commit()
finally:
session.close()
res["code"] = 1
else:
res = form.errors
res["code"] = 0
return jsonify(res)
else:
return render_template("create.html", data=data)
def get_mes(id):
session = ORM.db()
emp, hob = None, None
try:
mes = session.query(Message).filter_by(id=int(id)).first()
except Exception as e:
session.rollback()
else:
session.commit()
finally:
session.close()
return mes
def get_gam(id):
session = ORM.db()
gam = None
try:
gam = session.query(Game).filter_by(id=int(id)).first()
except Exception as e:
session.rollback()
else:
session.commit()
finally:
session.close()
return gam
def get_emp(id):
session = ORM.db()
emp, hob = None, None
try:
emp = session.query(Employee).filter_by(id=int(id)).first()
hob = session.query(Hobby).filter_by(employee_id=int(id)).all()
except Exception as e:
session.rollback()
else:
session.commit()
finally:
session.close()
return emp, hob
def update_mes(form):
session = ORM.db()
try:
# 修改員工訊息
emp = session.query(Message).filter_by(id=int(form.id.data)).first()
#emp.game_id = form.game_id.data
emp.content = form.info.data
emp.title = form.name.data
emp.updatedAt = dt()
except Exception as e:
session.rollback()
else:
session.commit()
finally:
session.close()
def delete_menu():
id = request.args.get("id", None)
game_id = request.args.get("game_id", None)
if id:
session = ORM.db()
try:
menu = session.query(Menu).filter_by(id=int(id)).first()
session.delete(menu)
except Exception as e:
session.rollback()
else:
session.commit()
finally:
session.close()
return redirect(url_for('crud.read_one', id=game_id))
def save_menu(form):
session = ORM.db()
try:
menu = Menu(game_id=form.id.data,
item=form.name.data,
url=form.url.data,
createdAt=dt())
session.add(menu)
except Exception as e:
session.rollback()
else:
session.commit()
finally:
session.close()
return
def save_game(form):
session = ORM.db()
try:
game = Game(name=form.name.data,
email=form.email.data,
createdAt=dt(),
updatedAt=dt())
session.add(game)
except Exception as e:
session.rollback()
else:
session.commit()
finally:
session.close()
return
def save_message(form):
session = ORM.db()
try:
message = Message(game_id=form.id.data,
title=form.name.data,
content=form.info.data,
createdAt=dt(),
updatedAt=dt())
session.add(message)
except Exception as e:
session.rollback()
else:
session.commit()
finally:
session.close()
return
def read_list():
data = dict(title="賽事列表")
arr = None
session = ORM.db()
try:
model = session.query(Game).order_by(Game.id.desc())
arr = page(model)
except Exception as e:
session.rollback()
else:
session.commit()
finally:
session.close()
data["arr"] = arr
return render_template("read_list.html", data=data)
def read_list():
data = dict(title="員工列表")
arr = None
session = ORM.db()
try:
model = session.query(Employee).order_by(Employee.id.desc())
arr = page(model)
# 按照職位分組統計查詢
job = session.query(Employee.job, func.count(Employee.id)).group_by(
Employee.job).all()
job_dict = {v[0]: v[1] for v in job}
data['job_pie'] = pie_html(
"職位統計", job_list,
[job_dict.get(k + 1, 0) for k, v in enumerate(job_list)])
# 按照學歷分組統計查詢
edu = session.query(Employee.edu, func.count(Employee.id)).group_by(
Employee.edu).all()
edu_dict = {v[0]: v[1] for v in edu}
data["edu_funnel"] = funnel_html(
"學歷統計", edu_list,
[edu_dict.get(k + 1, 0) for k, v in enumerate(edu_list)])
# 按照性別分組統計查詢
sex = session.query(Employee.sex, func.count(Employee.id)).group_by(
Employee.sex).all()
sex_dict = {v[0]: v[1] for v in sex}
data["sex_bar"] = bar_html(
"性別統計", sex_list,
[sex_dict.get(k + 1, 0) for k, v in enumerate(sex_list)])
# 按照愛好分組統計查詢
hobby = session.query(Hobby.hobby_key, func.count(Hobby.id)).group_by(
Hobby.hobby_key).all()
hobby_dict = {v[0]: v[1] for v in hobby}
data['hobby_wc'] = wordcloud_html(
"愛好詞雲", hobby_list,
[hobby_dict.get(k + 1, 0) for k, v in enumerate(hobby_list)])
except Exception as e:
session.rollback()
else:
session.commit()
finally:
session.close()
data["arr"] = arr
return render_template("read_list.html", data=data)
def update_emp(form):
session = ORM.db()
try:
# 修改員工訊息
emp = session.query(Employee).filter_by(id=int(form.id.data)).first()
emp.name = form.name.data
emp.job = int(form.job.data)
emp.sex = int(form.sex.data)
emp.edu = int(form.edu.data)
emp.birth = form.birth.data
emp.email = form.email.data
emp.phone = form.phone.data
emp.info = form.info.data
emp.face = form.face.data
emp.updatedAt = dt()
# 2.修改愛好
hob = session.query(Hobby).filter_by(
employee_id=int(form.id.data)).all()
old_hob_keys = [v.hobby_key for v in hob]
new_hob_keys = [int(v) for v in form.hobby.data]
# 2-1.新增愛好添加到數據庫中
for v in new_hob_keys:
if v not in old_hob_keys:
ch = Hobby(employee_id=emp.id,
hobby_key=v,
createdAt=dt(),
updatedAt=dt())
session.add(ch)
# 2-2.取消爱好從數據庫中中删除
for v in old_hob_keys:
if v not in new_hob_keys:
ch = session.query(Hobby).filter(
and_(Hobby.employee_id == emp.id,
Hobby.hobby_key == v)).first()
session.delete(ch)
session.add(emp)
except Exception as e:
session.rollback()
else:
session.commit()
finally:
session.close()
def save_subgame(form):
session = ORM.db()
try:
subgame = Subgame(
game_id=form.id.data,
name=form.name.data,
url=form.url.data,
img=form.img.data,
createdAt=dt(),
updatedAt=dt()
)
session.add(subgame)
except Exception as e:
session.rollback()
else:
session.commit()
finally:
session.close()
return
def read_game():
id = request.args.get("id", None)
if id:
session = ORM.db()
data = dict()
try:
data['gam'] = session.query(Game).filter_by(id=int(id)).first()
data['sub'] = session.query(Subgame).filter_by(
game_id=int(id)).all()
data['mes'] = session.query(Message).order_by(
Message.updatedAt.desc()).filter_by(game_id=int(id)).all()
data['men'] = session.query(Menu).filter_by(game_id=int(id)).all()
except Exception as e:
session.rollback()
else:
session.commit()
finally:
session.close()
return render_template("front.html", data=data)
else:
return 404
def read_one():
id = request.args.get("id", None)
if id:
data = dict(title="大賽詳情")
session = ORM.db()
try:
data['gam'] = session.query(Game).filter_by(id=int(id)).first()
data['sub'] = session.query(Subgame).filter_by(
game_id=int(id)).all()
data['mes'] = session.query(Message).filter_by(
game_id=int(id)).all()
data['men'] = session.query(Menu).filter_by(game_id=int(id)).all()
except Exception as e:
session.rollback()
else:
session.commit()
finally:
session.close()
return render_template("read_one.html", data=data)
else:
return redirect(url_for('crud.read_list'))
def delete():
id = request.args.get("id", None)
if id:
session = ORM.db()
try:
game = session.query(Game).filter_by(id=int(id)).first()
subgame = session.query(Subgame).filter_by(game_id=int(id)).all()
session.delete(game)
for item in subgame:
session.delete(item)
message = session.query(Message).filter_by(game_id=int(id)).all()
for item in message:
session.delete(item)
menu = session.query(Menu).filter_by(game_id=int(id)).all()
for item in menu:
session.delete(item)
except Exception as e:
session.rollback()
else:
session.commit()
finally:
session.close()
return redirect(url_for('crud.read_list'))
def save_employee(form):
session = ORM.db()
try:
employee = Employee(
name=form.name.data,
job=int(form.job.data),
sex=int(form.sex.data),
edu=int(form.edu.data),
birth=form.birth.data,
email=form.email.data,
phone=form.phone.data,
info=form.info.data,
face=form.face.data,
createdAt=dt(),
updatedAt=dt()
)
session.add(employee)
except Exception as e:
session.rollback()
else:
session.commit()
finally:
session.close()
return employee.id