def get_project_data(start_day, end_day, user_id=1):
"""
统计工时数据
:param start_day:
:param end_day:
:param user_id:
:return:
"""
all_apps = Apps.query.all()
all_app_hours = []
user_app_hours = []
for app in all_apps:
# 查询每个项目的总工时
score = db.session.query(func.sum(ManHour.hour)).filter(
db.and_(ManHour.app_id == app.id,
ManHour.day.between(start_day, end_day))).scalar()
# 如果有数据就收集
if score:
hour_info = {'project': app.name, 'hours': float('%.1f' % score)}
all_app_hours.append(hour_info)
# 查询单个用户的项目总工时
user_score = db.session.query(func.sum(ManHour.hour)).filter(
db.and_(ManHour.app_id == app.id,
ManHour.day.between(start_day, end_day),
ManHour.user_id == user_id)).scalar()
if user_score:
user_hour_info = {
'project': app.name,
'hours': float('%.1f' % user_score)
}
user_app_hours.append(user_hour_info)
return all_app_hours, user_app_hours
def add_hours():
"""
录入工时
:return:
"""
apps = Apps.query.all()
if request.method == "POST":
# 记录的时间
day = request.form.get('day')
# 查询每一条记录
for key in request.form.keys():
if key.startswith('app_id'):
index = key.split('_')[-1]
# 所属项目
app_id = request.form.get('app_id_' + index)
# 消耗时间
hour = request.form.get('spend_hour_' + index)
# 查询是否已经有此项目记录,如果有就更新数据
man_hour = ManHour.query.filter(db.and_(ManHour.app_id == app_id,ManHour.user_id == current_user.id,ManHour.day == day)).first()
if not man_hour:
man_hour = ManHour()
man_hour.app_id = app_id
man_hour.user_id = current_user.id
man_hour.day = day
man_hour.hour = hour
db.session.add(man_hour)
db.session.commit()
flash('工时录入成功', 'success')
return redirect(url_for('hours.my_hours'))
return render_template('hours/add_hours.html', apps=apps)
def export_hour_data():
"""导出数据"""
# 获取今天日期
today = datetime.strftime(datetime.now(), '%Y-%m-%d')
# 获取月份
month = datetime.strftime(datetime.now(), '%Y-%m')
# 起始时间
start_day = month + '-01'
# 查哪些项目有数据,同时统计总数
all_apps = Apps.query.all()
all_app_hours = []
has_hour_apps = []
user_hour_infos = []
user_total_info = {}
all_users = User.query.all()
# 项目数据
for app in all_apps:
# 查询每个项目的总工时
score = db.session.query(func.sum(ManHour.hour)).filter(
db.and_(ManHour.app_id == app.id,
ManHour.day.between(start_day, today))).scalar()
# 如果有数据就收集
if score:
has_hour_apps.append(app)
hour_info = {app.name: float('%.1f' % score)}
all_app_hours.append(hour_info)
# 所有项目总工时
apps_total = db.session.query(func.sum(ManHour.hour)).filter(
ManHour.day.between(start_day, today)).scalar()
total_info = {'total': float('%.1f' % apps_total)}
# 个人数据
for user in all_users:
if user.username in ['tianyong']:
continue
user_hour_info = {'username': user.display_name}
user_app_infos = []
for has_hour_app in has_hour_apps:
user_sum = db.session.query(func.sum(ManHour.hour)).filter(
db.and_(ManHour.app_id == has_hour_app.id,
ManHour.day.between(start_day, today),
ManHour.user_id == user.id)).scalar()
sum_hour = float('%.1f' % user_sum) if user_sum else 0
user_app_infos.append({has_hour_app.name: sum_hour})
user_all_sum = db.session.query(func.sum(ManHour.hour)).filter(
db.and_(ManHour.day.between(start_day, today),
ManHour.user_id == user.id)).scalar()
sum_hour = float('%.1f' % user_all_sum) if user_all_sum else 0
user_total_info[user.display_name] = sum_hour
user_hour_info['info'] = user_app_infos
user_hour_infos.append(user_hour_info)
# 生成Excel
# 字体风格
font = xlwt.Font()
font.name = '等线'
# 字体大小*20
font.height = 220
# 边框风格
borders = xlwt.Borders()
borders.right = xlwt.Borders.THIN
borders.left = xlwt.Borders.THIN
borders.top = xlwt.Borders.THIN
borders.bottom = xlwt.Borders.THIN
# 标题风格
title_style = xlwt.XFStyle() # 创建一个样式对象，初始化样式
al = xlwt.Alignment()
al.horz = 0x02 # 设置水平居中
al.vert = 0x01 # 设置垂直居中
title_style.alignment = al
title_style.font = font
title_style.borders = borders
# 正文风格
body_style = xlwt.XFStyle()
body_al = xlwt.Alignment()
body_al.horz = 0x01 # 设置水平靠左
al.vert = 0x01 # 设置垂直居中
body_style.alignment = body_al
body_style.font = font
body_style.borders = borders
# 创建excel
excel_name = '研发中心%s月工时报表%s.xls' % (
month, datetime.now().strftime("%Y%m%d%H%M%S"))
excel_path = os.path.join(Config.EXCEL_DIR, excel_name)
excel = xlwt.Workbook(encoding='utf-8')
sheet = excel.add_sheet('%s月工时报表' % month)
for i in range(len(has_hour_apps) + 1):
if i == 0:
sheet.col(i).width = 256 * 10
sheet.col(i).width = 256 * 20
sheet.write_merge(0, 0, 0,
len(has_hour_apps) + 1, '研发中心%s月工时报表(单位h)' % month,
title_style)
sheet.write(1, 0, '姓名', body_style)
for i in range(len(has_hour_apps)):
sheet.write(1, i + 1, has_hour_apps[i].name, body_style)
sheet.write(1, len(has_hour_apps) + 1, '总计', body_style)
user_no = 2
for user_hour_info in user_hour_infos:
# 姓名
sheet.write(user_no, 0, user_hour_info['username'], body_style)
# 项目时间
for i in range(len(has_hour_apps)):
for hour_info in user_hour_info['info']:
if has_hour_apps[i].name in hour_info:
sheet.write(user_no, i + 1,
hour_info[has_hour_apps[i].name], body_style)
sheet.write(user_no,
len(has_hour_apps) + 1,
user_total_info[user_hour_info['username']], body_style)
user_no += 1
# 总时间
sheet.write(user_no, 0, '总计', body_style)
for i in range(len(has_hour_apps)):
for app_total in all_app_hours:
if has_hour_apps[i].name in app_total:
sheet.write(user_no, i + 1, app_total[has_hour_apps[i].name],
body_style)
sheet.write(user_no,
len(has_hour_apps) + 1, total_info['total'], body_style)
excel.save(excel_path)
return send_from_directory(Config.EXCEL_DIR,
excel_name,
as_attachment=True,
attachment_filename=excel_name)
def edit_menu(menu_id):
"""
修改菜单
:param menu_id:
:return:
"""
menu = Menu.query.filter(Menu.id == menu_id).first()
if request.method == 'POST':
menu_name = request.form['menu_name']
menu_url = request.form['menu_url']
active = True if request.form.get('activate', 'off') == 'on' else False
menu_icon = request.form['menu_icon']
menu_desc = request.form['menu_desc']
menu_order = request.form['menu_order']
parent_id = request.form['parent_id']
is_parent = False if parent_id else True
if menu:
menu.menu_name = menu_name
menu.menu_url = menu_url
menu.active = active
menu.menu_icon = menu_icon
menu.menu_desc = menu_desc
menu.menu_order = menu_order
if parent_id:
menu.parent_id = parent_id
menu.is_parent = is_parent
db.session.add(menu)
db.session.commit()
flash('菜单修改成功', 'success')
else:
new_menu = Menu()
new_menu.menu_name = menu_name
new_menu.menu_url = menu_url
new_menu.active = active
new_menu.menu_icon = menu_icon
new_menu.menu_desc = menu_desc
new_menu.menu_order = menu_order
if parent_id:
new_menu.parent_id = parent_id
new_menu.is_parent = is_parent
db.session.add(new_menu)
db.session.commit()
flash('菜单添加成功', 'success')
role = current_user.role
if role and role.menu:
menu_ids = [menu_id for menu_id in role.menu.split(',') if id]
menus = [
menu for menu in Menu.query.filter(
db.and_(Menu.active == True, Menu.id.in_(
menu_ids))).order_by(Menu.menu_order).all()
]
else:
menus = []
session['menu'] = create_menu(menus)
return redirect(url_for('manager.show_menus'))
else:
menu_icons = SystemParameter.query.filter(
SystemParameter.key == 'menu_icons').first().value.split(',')
parent_menus = Menu.query.filter(Menu.is_parent).all()
return render_template('manager/add_menu.html',
menu=menu,
menu_icons=menu_icons,
parent_menus=parent_menus)
def edit_role(role_id):
role = Role.query.filter(Role.id == role_id).first()
if request.method == 'POST':
all_id = [
str(menu_id) for menu_id in request.form.getlist('role_menu')
if menu_id
]
if all_id:
query_sql = "SELECT DISTINCT parent_id FROM omp_menu WHERE id IN(%s)" % ','.join(
all_id)
for row in db.engine.execute(query_sql):
if row[0] != 'None' and row[0] != None:
all_id.append(str(row[0]))
role.menu = ','.join(list(set(all_id)))
else:
role.menu = ''
role.name = request.form['name']
role.index_page = request.form['index_page']
db.session.add(role)
db.session.commit()
user_ids = request.form.getlist('role_member')
member = User.query.filter_by(role_id=role.id).all()
for user in member:
if user.id in user_ids:
user_ids.remove(user.id)
else:
user.role = None
db.session.add(user)
users = User.query.filter(User.id.in_(user_ids))
for user in users:
user.role = role
db.session.add(user)
db.session.commit()
flash('修改成功', 'success')
session['index_page'] = request.form['index_page']
role = current_user.role
if role and role.menu:
menu_ids = [menu_id for menu_id in role.menu.split(',') if id]
menus = [
menu for menu in Menu.query.filter(
db.and_(Menu.active == True, Menu.id.in_(
menu_ids))).order_by(Menu.menu_order).all()
]
else:
menus = []
session['menu'] = create_menu(menus)
return redirect(url_for('manager.role_manage'))
role_menu = [int(id) for id in role.menu.split(',')
if id] if role.menu else []
menus = Menu.query.all()
users = User.query.all()
return render_template('manager/role_edit.html',
role=role,
role_menu=role_menu,
menus=menus,
users=users)
def login():
"""
登录模块
"""
if request.method == 'POST':
username = request.form.get('username').strip()
password = request.form.get('password')
password_base = base64.b64encode(password.encode('utf-8'))
# 调用ldap进行登录认证,因为部分用户简历在dc=oraro,dc=com下面所以要做不同的处理
try:
conn = Connection(Config.LDAP_HOST,
'cn=%s,dc=oraro,dc=com' % username,
password,
auto_bind=True)
# 如果是第一次登录就往数据库写入user数据,否则只更新last seen的值
user = User.query.filter(User.username == username).first()
if user is not None:
if user.active:
user.last_seen = datetime.now()
user.password_hash = user.pass_exchange(password)
db.session.add(user)
db.session.commit()
login_user(user, True)
role = user.role
if role and role.menu:
menu_ids = [
menu_id for menu_id in role.menu.split(',') if id
]
menus = [
menu for menu in Menu.query.filter(
db.and_(Menu.active == True,
Menu.id.in_(menu_ids))).order_by(
Menu.menu_order).all()
]
else:
menus = []
# 添加session
session['user'] = {
'username': user.username,
'password': password_base,
'display_name': user.display_name,
'email': user.email,
'role': user.role.name,
'id': user.id,
'avatar': user.gravatar()
}
session['menu'] = create_menu(menus)
session['index_page'] = user.role.index_page
return redirect(
request.args.get('next')
or url_for(user.role.index_page))
else:
flash('账号处于未激活状态,请联系管理员!!!', 'warning')
return redirect(url_for('auth.login'))
else:
user = User()
user.username = username
user.password_hash = user.pass_exchange(password)
user.email = username + Config.MAIL_TAIL
user.display_name = username
user.active = True
user.member_since = datetime.now()
user.last_seen = datetime.now()
# 初始化一个角色,后面管理员再去改
role = Role.query.filter(Role.name == 'developer').first()
user.role = role
user.rank = 'normal'
db.session.add(user)
db.session.commit()
login_user(user, True)
role = user.role
if role and role.menu:
menu_ids = [
menu_id for menu_id in role.menu.split(',') if id
]
menus = [
menu for menu in Menu.query.filter(
db.and_(Menu.active == True, Menu.id.in_(
menu_ids))).order_by(Menu.menu_order).all()
]
else:
menus = []
# 添加session
session['user'] = {
'username': user.username,
'password': password_base,
'display_name': user.display_name,
'email': user.email,
'role': user.role.name,
'id': user.id,
'avatar': user.gravatar()
}
session['menu'] = create_menu(menus)
session['index_page'] = user.role.index_page
return redirect(
request.args.get('next') or url_for(user.role.index_page))
except Exception as e:
print(e)
try:
conn = Connection(Config.LDAP_HOST,
'cn=%s,' % username +
Config.LDAP_SEARCH_BASE,
password,
auto_bind=True)
# 如果是第一次登录就往数据库写入user数据,否则只更新last seen的值
user = User.query.filter(User.username == username).first()
if user is not None:
if user.active:
user.last_seen = datetime.now()
user.password_hash = user.pass_exchange(password)
db.session.add(user)
db.session.commit()
login_user(user, True)
role = user.role
if role and role.menu:
menu_ids = [
menu_id for menu_id in role.menu.split(',')
if id
]
menus = [
menu for menu in Menu.query.filter(
db.and_(Menu.active == True,
Menu.id.in_(menu_ids))).order_by(
Menu.menu_order).all()
]
else:
menus = []
# 添加session
session['user'] = {
'username': user.username,
'password': password_base,
'display_name': user.display_name,
'email': user.email,
'role': user.role.name,
'id': user.id,
'avatar': user.gravatar()
}
session['menu'] = create_menu(menus)
session['index_page'] = user.role.index_page
return redirect(
request.args.get('next')
or url_for(user.role.index_page))
else:
flash('账号处于未激活状态,请联系管理员!!!', 'warning')
return redirect(url_for('auth.login'))
else:
user = User()
user.username = username
user.password_hash = user.pass_exchange(password)
user.email = username + Config.MAIL_TAIL
user.display_name = username
user.active = True
user.member_since = datetime.now()
user.last_seen = datetime.now()
# 初始化一个角色,后面管理员再去改
role = Role.query.filter(Role.name == 'developer').first()
user.role = role
user.rank = 'normal'
db.session.add(user)
db.session.commit()
login_user(user, True)
role = user.role
if role and role.menu:
menu_ids = [
menu_id for menu_id in role.menu.split(',') if id
]
menus = [
menu for menu in Menu.query.filter(
db.and_(Menu.active == True,
Menu.id.in_(menu_ids))).order_by(
Menu.menu_order).all()
]
else:
menus = []
# 添加session
session['user'] = {
'username': user.username,
'password': password_base,
'display_name': user.display_name,
'email': user.email,
'role': user.role.name,
'id': user.id,
'avatar': user.gravatar()
}
session['menu'] = create_menu(menus)
session['index_page'] = user.role.index_page
return redirect(
request.args.get('next')
or url_for(user.role.index_page))
except Exception as e:
print(e)
flash('用户名或密码错误!', 'danger')
return redirect(url_for('auth.login'))
else:
return render_template('auth/login.html')