def login():
if request.method == "POST":
username = request.form["username"]
password = request.form["password"]
if (username == ''):
flash("用户名不能为空")
elif (password == ''):
flash("密码不能为空")
else:
value = md5(password)
user = User.query.filter(
db.or_(
db.and_(User.username == username, User.password == value),
db.and_(User.truename == username, User.password == value),
db.and_(User.EnrollNumber == username,
User.password == value))).first()
if not user:
flash('账号或密码错误！')
else:
login_user(user, remember=True)
# menus = dict(menu=MenuRole.query.filter_by(RoleID=current_user.RoleID).outerjoin(Menu, MenuRole.MenuID == Menu.ID))
flash("登陆成功")
return redirect(request.args.get("next") or url_for('.index'))
return render_template("login.html")
def Application(page=1):
# if request.method == "POST":
# wid = request.form.get('wid', '')
leaves = writtenForleave.query.order_by(
writtenForleave.LeaveID.desc()).filter(
db.or_(
writtenForleave.LeaveUserId == current_user.userid,
writtenForleave.WriteUserID == current_user.userid)).paginate(
page, per_page=10, error_out=False)
cats = Cat.query.all()
return render_template("t-application.html", leaves=leaves, cats=cats)
def s_qingjia(st, ed):
stt = datetime.datetime.strptime(st, '%Y-%m-%d')
edt = datetime.datetime.strptime(ed, '%Y-%m-%d')
users = User.query.filter(User.DepID != None).all()
# 循环每个用户，但是，有些跨月的，提取不到，比如产假，一请就是半年以上的，这种提取不到。所以这种应该生成一个表，记录每一天的请假情况。
for item in users:
leaves = writtenForleave.query.filter(
db.or_(
db.and_(writtenForleave.StartTime >= st,
writtenForleave.EndTime <= ed,
writtenForleave.LeaveUserId == item.userid,
writtenForleave.ifComplete == 1,
writtenForleave.Agree == 1),
db.and_(
db.between(st, writtenForleave.StartTime,
writtenForleave.EndTime),
writtenForleave.LeaveUserId == item.userid,
writtenForleave.ifComplete == 1,
writtenForleave.Agree == 1),
db.and_(
db.between(ed, writtenForleave.StartTime,
writtenForleave.EndTime),
writtenForleave.LeaveUserId == item.userid,
writtenForleave.ifComplete == 1,
writtenForleave.Agree == 1))).all()
for i in range(0, len(leaves)):
jiange = (leaves[i].EndTime - leaves[i].StartTime).days
# print(leaves[i].LeaveID)
sql = "update t_kuanggong set zhuangtai=" + str(
leaves[i].Category
) + " where userid=" + str(item.userid) + " and sttime >= '" + str(
leaves[i].StartTime) + "' and sttime <= '" + str(
leaves[i].EndTime) + "' and zhuangtai=13 or userid=" + str(
item.userid) + " and edtime >= '" + str(
leaves[i].StartTime) + "' and edtime<= '" + str(
leaves[i].EndTime) + "' and zhuangtai=13"
# print(sql)
db.session.execute(sql)
db.session.commit()
dataviews = db.session.query(
db.func.count(kuanggong.schName).label("cishu"), kuanggong.schName,
kuanggong.userid, kuanggong.riqi, kuanggong.zhuangtai).filter(
db.and_(kuanggong.riqi >= st, kuanggong.riqi <= ed,
kuanggong.zhuangtai == 13)).group_by(
kuanggong.schName, kuanggong.userid, kuanggong.riqi,
kuanggong.zhuangtai).all()
# print(dataviews)把旷工的导进去
for i in range(0, len(dataviews)):
# print (dataviews[i].cishu)
sql = "update t_shidao set zhuangtai=" + str(
dataviews[i].zhuangtai
) + " where userid =" + str(dataviews[i].userid) + " and riqi='" + str(
dataviews[i].riqi) + "' and schName='" + dataviews[i].schName + "'"
print(sql)
db.session.execute(sql)
db.session.commit()
dataviews = db.session.query(
db.func.count(kuanggong.schName).label("cishu"), kuanggong.schName,
kuanggong.userid, kuanggong.riqi, kuanggong.zhuangtai).filter(
db.and_(kuanggong.riqi >= st, kuanggong.riqi <= ed,
kuanggong.zhuangtai != 9,
kuanggong.zhuangtai != 13)).group_by(
kuanggong.schName, kuanggong.userid, kuanggong.riqi,
kuanggong.zhuangtai).all()
# print(dataviews)把不是旷工也不是实到的更新进去
for i in range(0, len(dataviews)):
# print (dataviews[i].cishu)
sql = "update t_shidao set zhuangtai=" + str(
dataviews[i].zhuangtai
) + " where userid =" + str(dataviews[i].userid) + " and riqi='" + str(
dataviews[i].riqi) + "' and schName='" + dataviews[i].schName + "'"
print(sql)
db.session.execute(sql)
db.session.commit()
print("请假生成完成！")
def authenticate(cls, user_id, password):
user = cls.query.filter(
db.or_(cls.username == user_id, cls.email == user_id)).first()
if user is not None and check_password_hash(user.password, password):
return user
def importdata():
list = association.query.all()
dataview = []
for item in list:
items = []
sql = "d_association.AssociationID=" + str(item.DepID)
sumlist = db.session.query(
Sumlist.UserID, User.truename, User.EnrollNumber,
association.DepName,
(db.func.count(db.case([(Sumlist.LeaveID == 1, Sumlist.UserID)])) /
2.00).label("事假"),
(db.func.count(db.case([(Sumlist.LeaveID == 2, Sumlist.UserID)])) /
2.00).label("病假"),
(db.func.count(db.case([(Sumlist.LeaveID == 3, Sumlist.UserID)])) /
2.00).label("公差"),
(db.func.count(db.case([(Sumlist.LeaveID == 4, Sumlist.UserID)])) /
2.00).label("产假"),
(db.func.count(db.case([(Sumlist.LeaveID == 5, Sumlist.UserID)])) /
2.00).label("婚假"),
(db.func.count(db.case([(Sumlist.LeaveID == 6, Sumlist.UserID)])) /
2.00).label("休假"),
(db.func.count(db.case([(Sumlist.LeaveID == 7, Sumlist.UserID)])) /
2.00).label("其他假"),
db.func.count(db.case([(Sumlist.LeaveID == 8, Sumlist.UserID)
])).label("应到"),
(db.func.count(db.case([(Sumlist.LeaveID == 9, Sumlist.UserID)])) /
2.00).label("实到"),
db.func.count(
db.case([(db.or_(Sumlist.LeaveID == 10, Sumlist.LeaveID == 11),
Sumlist.UserID)])).label("迟到早退"),
db.func.count(db.case([(Sumlist.LeaveID == 12, Sumlist.UserID)
])).label("脱岗"),
(db.func.count(db.case([(Sumlist.LeaveID == 13, Sumlist.UserID)]))
/ 2.00).label("未按指纹")).group_by(
Sumlist.UserID, User.truename,
User.EnrollNumber, association.DepName).outerjoin(
User, Sumlist.UserID == User.userid).outerjoin(
association,
association.DepID == User.DepID).filter(sql).order_by(
association.DepName).all()
items.append(sumlist)
pj = len(sumlist)
dwlist = db.session.query(
(db.func.count(db.case([(Sumlist.LeaveID == 1, Sumlist.UserID)])) /
2.00 / pj).label("事假"),
(db.func.count(db.case([(Sumlist.LeaveID == 2, Sumlist.UserID)])) /
2.00 / pj).label("病假"),
(db.func.count(db.case([(Sumlist.LeaveID == 3, Sumlist.UserID)])) /
2.00 / pj).label("公差"),
(db.func.count(db.case([(Sumlist.LeaveID == 4, Sumlist.UserID)])) /
2.00 / pj).label("产假"),
(db.func.count(db.case([(Sumlist.LeaveID == 5, Sumlist.UserID)])) /
2.00 / pj).label("婚假"),
(db.func.count(db.case([(Sumlist.LeaveID == 6, Sumlist.UserID)])) /
2.00 / pj).label("休假"),
(db.func.count(db.case([(Sumlist.LeaveID == 7, Sumlist.UserID)])) /
2.00 / pj).label("其他假"),
(db.func.count(db.case([(Sumlist.LeaveID == 8, Sumlist.UserID)])) /
pj).label("应到"),
(db.func.count(db.case([(Sumlist.LeaveID == 9, Sumlist.UserID)])) /
2.00 / pj).label("实到"),
(db.func.count(
db.case([(db.or_(Sumlist.LeaveID == 10, Sumlist.LeaveID == 11),
Sumlist.UserID)])) / pj).label("迟到早退"),
(db.func.count(db.case([(Sumlist.LeaveID == 12, Sumlist.UserID)]))
/ pj).label("脱岗"),
(db.func.count(db.case([(Sumlist.LeaveID == 13, Sumlist.UserID)]))
/ 2.00 / pj).label("未按指纹"),
association.DepName,
).group_by(association.DepName).outerjoin(
User, Sumlist.UserID == User.userid).outerjoin(
association,
association.DepID == User.DepID).filter(sql).order_by(
association.DepName).all()
items.append(dwlist)
dataview.append(items)
return render_template("t-importdata.html",
list=dataview,
Dept=association.query.all(),
user=User.query.all(),
roles=Role.query.all(),
sttime=datetime.now().strftime('%Y-%m-01'),
edtime=datetime.now().strftime('%Y-%m-%d')) # 节日设置
def search():
list = association.query.all()
dataview = []
sql = "1<>1"
dept = request.args.get('dept')
qx = request.args.get('role')
startime = request.args.get('startime')
endtime = request.args.get('endtime')
custname = request.args.get('custname')
for item in list:
items = []
if (startime != None and endtime != None):
sql = "d_association.AssociationID=" + str(
item.DepID
) + " and d_user.AssociationID <>1 and d_sumlist.Date BETWEEN '" + startime + "' and '" + endtime + "' "
if (dept != 'all' and dept != None):
sql = sql + " and d_association.AssociationID=" + dept
if qx != "all":
try:
sql = sql + " and d_user.RoleID=" + qx
except TypeError:
pass
if (custname != '' and custname != None):
sql = sql + " and d_user.LoginName='" + custname + "' or " + sql + " and d_user.UserName='******' or " + sql + " and d_user.EnrollNumber='" + custname + "'"
sumlist = db.session.query(
Sumlist.UserID, User.truename, User.EnrollNumber,
association.DepName,
(db.func.count(db.case([(Sumlist.LeaveID == 1, Sumlist.UserID)])) /
2.00).label("事假"),
(db.func.count(db.case([(Sumlist.LeaveID == 2, Sumlist.UserID)])) /
2.00).label("病假"),
(db.func.count(db.case([(Sumlist.LeaveID == 3, Sumlist.UserID)])) /
2.00).label("公差"),
(db.func.count(db.case([(Sumlist.LeaveID == 4, Sumlist.UserID)])) /
2.00).label("产假"),
(db.func.count(db.case([(Sumlist.LeaveID == 5, Sumlist.UserID)])) /
2.00).label("婚假"),
(db.func.count(db.case([(Sumlist.LeaveID == 6, Sumlist.UserID)])) /
2.00).label("休假"),
(db.func.count(db.case([(Sumlist.LeaveID == 7, Sumlist.UserID)])) /
2.00).label("其他假"),
db.func.count(db.case([(Sumlist.LeaveID == 8, Sumlist.UserID)
])).label("应到"),
(db.func.count(db.case([(Sumlist.LeaveID == 9, Sumlist.UserID)])) /
2.00).label("实到"),
db.func.count(
db.case([(db.or_(Sumlist.LeaveID == 10, Sumlist.LeaveID == 11),
Sumlist.UserID)])).label("迟到早退"),
db.func.count(db.case([(Sumlist.LeaveID == 12, Sumlist.UserID)
])).label("脱岗"),
(db.func.count(db.case([(Sumlist.LeaveID == 13, Sumlist.UserID)]))
/ 2.00).label("未按指纹")).group_by(
Sumlist.UserID, User.truename,
User.EnrollNumber, association.DepName).outerjoin(
User, Sumlist.UserID == User.userid).outerjoin(
association,
association.DepID == User.DepID).filter(sql).order_by(
association.DepName).all()
items.append(sumlist)
pj = len(sumlist)
dwlist = db.session.query(
(db.func.count(db.case([(Sumlist.LeaveID == 1, Sumlist.UserID)])) /
2.00 / pj).label("事假"),
(db.func.count(db.case([(Sumlist.LeaveID == 2, Sumlist.UserID)])) /
2.00 / pj).label("病假"),
(db.func.count(db.case([(Sumlist.LeaveID == 3, Sumlist.UserID)])) /
2.00 / pj).label("公差"),
(db.func.count(db.case([(Sumlist.LeaveID == 4, Sumlist.UserID)])) /
2.00 / pj).label("产假"),
(db.func.count(db.case([(Sumlist.LeaveID == 5, Sumlist.UserID)])) /
2.00 / pj).label("婚假"),
(db.func.count(db.case([(Sumlist.LeaveID == 6, Sumlist.UserID)])) /
2.00 / pj).label("休假"),
(db.func.count(db.case([(Sumlist.LeaveID == 7, Sumlist.UserID)])) /
2.00 / pj).label("其他假"),
(db.func.count(db.case([(Sumlist.LeaveID == 8, Sumlist.UserID)])) /
pj).label("应到"),
(db.func.count(db.case([(Sumlist.LeaveID == 9, Sumlist.UserID)])) /
2.00 / pj).label("实到"),
(db.func.count(
db.case([(db.or_(Sumlist.LeaveID == 10, Sumlist.LeaveID == 11),
Sumlist.UserID)])) / pj).label("迟到早退"),
(db.func.count(db.case([(Sumlist.LeaveID == 12, Sumlist.UserID)]))
/ pj).label("脱岗"),
(db.func.count(db.case([(Sumlist.LeaveID == 13, Sumlist.UserID)]))
/ 2.00 / pj).label("未按指纹"),
association.DepName,
).group_by(association.DepName).outerjoin(
User, Sumlist.UserID == User.userid).outerjoin(
association,
association.DepID == User.DepID).filter(sql).order_by(
association.DepName).all()
items.append(dwlist)
dataview.append(items)
return render_template("t-search.html",
Dept=association.query.all(),
user=User.query.all(),
roles=Role.query.all(),
dataview=dataview,
sttime=datetime.now().strftime('%Y-%m-01'),
edtime=datetime.now().strftime('%Y-%m-%d'))
def statistics(page=1):
page = request.args.get('page')
dept = request.args.get('dept')
startime = request.args.get('startime')
endtime = request.args.get('endtime')
custname = request.args.get('custname')
sql = " d_sumlist.LeaveID<>0 "
if page != None:
page = int(page)
if (startime != None and endtime != None):
sql = sql + " and d_sumlist.Date BETWEEN '" + startime + "' and '" + endtime + "' "
if (dept != 'all' and dept != None):
sql = sql + " and d_association.AssociationID=" + dept
if (custname != '' and custname != None):
sql = sql + " and d_user.LoginName='" + custname + "' or " + sql + " and d_user.UserName='******' or " + sql + " and d_user.EnrollNumber='" + custname + "'"
print(sql)
dataview = db.session.query(
Sumlist.UserID, User.truename, User.EnrollNumber, association.DepName,
Sumlist.Date,
(db.func.count(db.case([(Sumlist.LeaveID == 1, Sumlist.UserID)])) /
2.00).label("事假"),
(db.func.count(db.case([(Sumlist.LeaveID == 2, Sumlist.UserID)])) /
2.00).label("病假"),
(db.func.count(db.case([(Sumlist.LeaveID == 3, Sumlist.UserID)])) /
2.00).label("公差"),
(db.func.count(db.case([(Sumlist.LeaveID == 4, Sumlist.UserID)])) /
2.00).label("产假"),
(db.func.count(db.case([(Sumlist.LeaveID == 5, Sumlist.UserID)])) /
2.00).label("婚假"),
(db.func.count(db.case([(Sumlist.LeaveID == 6, Sumlist.UserID)])) /
2.00).label("休假"),
(db.func.count(db.case([(Sumlist.LeaveID == 7, Sumlist.UserID)])) /
2.00).label("其他假"),
db.func.count(db.case([(Sumlist.LeaveID == 8, Sumlist.UserID)
])).label("应到"),
(db.func.count(db.case([(Sumlist.LeaveID == 9, Sumlist.UserID)])) /
2.00).label("实到"),
db.func.count(
db.case([(db.or_(Sumlist.LeaveID == 10, Sumlist.LeaveID == 11),
Sumlist.UserID)])).label("迟到早退"),
db.func.count(db.case([(Sumlist.LeaveID == 12, Sumlist.UserID)
])).label("脱岗"),
(db.func.count(db.case([(Sumlist.LeaveID == 13, Sumlist.UserID)])) /
2.00).label("未按指纹")).outerjoin(
User, Sumlist.UserID == User.userid).outerjoin(
association,
association.DepID == User.DepID).filter(sql).group_by(
Sumlist.Date, Sumlist.UserID, User.EnrollNumber,
User.truename, association.DepName).order_by(
association.DepName, Sumlist.UserID,
Sumlist.Date).paginate(page,
per_page=10,
error_out=False)
#
#
# dataview = db.session.query(Sumlist.Date, Sumlist.LeaveID, User.EnrollNumber, User.truename,
# association.DepName).outerjoin(User,
# Sumlist.UserID == User.userid).outerjoin(
# association, association.DepID == User.DepID).paginate(page, per_page=10, error_out=False)
# # return redirect(url_for('.statistics'))
return render_template("t-statistics.html",
dataview=dataview,
Dept=association.query.all(),
sttime=datetime.now().strftime('%Y-%m-01'),
edtime=datetime.now().strftime('%Y-%m-%d'))