def add_daily(**kwargs):
kwargs["userid"] = g.user.ID
kwargs["createuser"] = g.user.ID
kwargs["updateuser"] = g.user.ID
kwargs["isdelete"] = 0
condition = and_(TDailyRecord.WorkDate == kwargs["WorkDate"],
TDailyRecord.userid == kwargs["userid"],
TDailyRecord.WorkMatters == kwargs["WorkMatters"])
res = session.query(TDailyRecord).filter(condition).first()
if res is not None:
return False, "您今天已经写过一模一样内容的的日报了"
daily = TDailyRecord(**kwargs)
date_p = datetime.strptime(kwargs["WorkDate"], '%Y-%m-%d').date()
years, daily.Weeks, daily.DayInWeek = date_p.isocalendar() # 获取周数和星期几
daily.JobDescription = session.query(
TbIntro.workintro).filter_by(id=daily.workintroId).first()[0]
daily.StaffName = session.query(
TStaff.StaffName).filter_by(ID=g.user.ID).first()[0]
daily.ProjectName = session.query(
TbProject.ProjectName).filter_by(ID=kwargs["ProjectID"]).first()[0]
try:
session.add(daily)
session.commit()
return True, "日报添加成功"
except exc.SQLAlchemyError as e:
session.rollback()
return False, "工作日报数据新增失败"
def get_assembly(frame_id, start, end):
"""
获取晨报
:param frame_id:
:param start:
:param end:
:return:
"""
tb_daily = TDailyRecord
if frame_id is None:
frame_name = session.query(
TStaff.Department).filter_by(ID=g.user.ID).first()
frame_name = frame_name[0] if frame_name else ""
T_frame = CompanyFrame().T_frame
frame_id = session.query(
T_frame.ID).filter(T_frame.Name == frame_name).first()
staff_li = CompanyFrame().get_staff_li(frame_id)
start = datetime.strptime(start, '%Y-%m-%d')
end = datetime.strptime(end, '%Y-%m-%d')
condition = and_(tb_daily.userid.in_(staff_li),
tb_daily.WorkDate.between(start, end))
assembly = session.query(
tb_daily.ID, tb_daily.WorkDate, tb_daily.Weeks, tb_daily.WorkHours,
tb_daily.JobDescription, tb_daily.WorkMatters, tb_daily.StaffName,
tb_daily.ProjectName, tb_daily.workintroId, tb_daily.userid,
tb_daily.ProjectID).filter(condition).order_by(
tb_daily.userid, tb_daily.WorkDate).all()
return tb_daily.pack_daily_data(assembly, True)
def history_projects_in_intros():
tb_intro = TWorkIntroduction
# 查询该用户的工作简介出现的项目id集合
project_ids = session.query(tb_intro.projectid).filter(
tb_intro.create_user == g.user.ID).distinct().all()
# 修改数据结构 [(id,),] -> [id,]
project_ids = [ids[0] for ids in project_ids]
# 查询前面id集合对应的项目Id与项目名组成的集合 按时间降序排列
projects = session.query(TbProject.ID, TbProject.ProjectName).filter(TbProject.ID.in_(project_ids))\
.order_by(desc(TbProject.create_date)).distinct().all()
return projects
def update_concern(concern_li):
new_concern_li = []
for i in concern_li:
id_exist = session.query(TStaff.ID).filter_by(ID=i).first()
if id_exist:
new_concern_li.append(str(i))
new_concern = ",".join(new_concern_li)
the_concern = session.query(TConcernStaff).filter_by(
StaffID=g.user.ID).first()
the_concern.ConcernIDGroup = new_concern
session.commit()
return True
def get_his_concern():
his_concern = session.query(
TConcernStaff.ConcernIDGroup).filter_by(StaffID=g.user.ID).first()
if not his_concern:
return []
his_concern = his_concern[0].strip(',').split(',') # 去掉首尾, 再以,分割成列表
id_names = session.query(TStaff.ID, TStaff.StaffName).filter(
TStaff.ID.in_(his_concern)).all()
return [{
"id": id_name.ID,
"name": id_name.StaffName
} for id_name in id_names]
def update_password(target_id, password):
current_staff = session.query(TStaff).filter_by(ID=g.user.ID).first()
target_staff = session.query(TStaff).filter_by(ID=target_id).first()
if current_staff.ID == target_staff.ID: # 修改自己的密码 无条件允许
target_staff.LoginPassword = TStaff().delete_zero(password)
session.commit()
else:
if current_staff.staffrole > target_staff.staffrole: # 修改他人的密码需要权限高于被修改者权限
target_staff.LoginPassword = TStaff().delete_zero(password)
session.commit()
else:
return False, "权限不够无法修改密码"
return True, "密码修改成功"
def staff_center():
"""
个人中心数据接口
:return: dst.my_json字典
"""
result = deepcopy(my_json) # 存储给用户的提示信息msg以及给前端的状态码
success, result["message"], result["data"] = TStaff.staff_center_data()
result["data"]["hours_count"] = session.query(
func.sum(
TDailyRecord.WorkHours)).filter_by(userid=g.user.ID).first()[0]
result["data"]["daycounts"] = session.query(
func.count(
TDailyRecord.WorkDate)).filter_by(userid=g.user.ID).first()[0]
result["status"] = 1 if success else 0
return result
def add_intro(**intro):
Intro = TWorkIntroduction
the_intro = TWorkIntroduction(**intro)
the_intro.userid = g.user.ID
the_intro.create_user = g.user.ID
the_intro.username = session.query(
TStaff.StaffName).filter_by(ID=g.user.ID).first()[0]
the_intro.snumber = session.query(func.max(
Intro.snumber)).first()[0] + 1
try:
session.add(the_intro)
session.commit()
except exc.SQLAlchemyError as e:
session.rollback()
return False, "数据提交失败"
return True, "数据提交成功"
def pack_intro_data(intros, detail=None):
data_li = []
for intro in intros: # 遍历当前用户的所有工作简介条目
data = dict() # 每次循环需要重新新建data字典
data["intro_id"] = intro.id # 工作日报id
# 项目名称
data["project_name"] = session.query(
TbProject.ProjectName).filter_by(ID=intro.projectid).first()[0]
data["work_intro"] = intro.workintro # 工作日期
if detail: # 详细查询要多出工时,具体事项字段
data["work_address"] = intro.workaddress # 工作地址
data["work_property"] = session.query(TWorkProperty.workpropertyname) \
.filter_by(id=intro.workproperty).first()
data_li.append(data) # 将data字典添加到data_li数组尾部
return data_li
def intros_of_project(project_id):
tb_intro = TWorkIntroduction # 名字太长 换个短点的名字
# 查询当前项目 当前用户对应 的工作简介
his_intros = session.query(tb_intro.id, tb_intro.workintro) \
.filter(and_(tb_intro.create_user == g.user.ID, tb_intro.projectid == project_id)) \
.order_by(desc(tb_intro.create_date)).all()
return [{"id": i.id, "name": i.workintro} for i in his_intros] # 列表生成
def his_all_daily(**kwargs):
"""
获取当前用户的工作日报
:return: data字典
"""
start = kwargs["start"] # 必选
end = kwargs["end"] # 必选
detail = kwargs["detail"] # 必选
staff_id = kwargs["staff_id"]
current_user = g.user.ID
tb_daily = TDailyRecord # 名字太长 换个短点的名字
dailies = session.query(tb_daily.ID, tb_daily.WorkDate, tb_daily.Weeks,
tb_daily.WorkHours, tb_daily.WorkMatters,
tb_daily.StaffName, tb_daily.ProjectName,
tb_daily.workintroId, tb_daily.userid,
tb_daily.ProjectID, tb_daily.StaffName,
tb_daily.JobDescription)
user_id = staff_id if staff_id else current_user # 如果有staff_id 则为查看他人日报 否则则为查看自己的工作日报
dailies = dailies.filter(TDailyRecord.userid == user_id)
if start and end: # 筛选时间
start = datetime.strptime(start, '%Y-%m-%d') # 将日期字符串格式化成日期对象
end = datetime.strptime(end, '%Y-%m-%d')
dailies = dailies.filter(TDailyRecord.WorkDate.between(
start, end)).order_by(TDailyRecord.WorkDate.desc())
return tb_daily.pack_daily_data(dailies, detail)
def all_projects(items):
tb_project = TProjectSummary
projects = session.query(tb_project.ID, tb_project.ProjectName) \
.order_by(desc(tb_project.create_date))
if not items: # 默认返回所有的数条目
projects = projects.all() # 获取所有的项目数据
else:
projects = projects.limit(items).all() # 获取最新的items条项目数据
return [(i[0], i[1]) for i in projects] # 列表生成
def get_the_daily(daily_id):
data = {} # 每次循环需要重新新建data字典
daily = session.query(TDailyRecord).filter_by(ID=daily_id).first()
if daily.userid != g.user.ID:
return False, "不能查看他人的工作日报", data
data["daily_id"] = daily.ID # 工作日报id
# 工作简介名称
# 项目名称
data["work_date"] = str(daily.WorkDate) # 工作日期
data["work_hours"] = str(daily.WorkHours) # 工时
data["work_matters"] = daily.WorkMatters # 具体事项
data["project_name"] = session.query(
TbProject.ProjectName).filter_by(ID=daily.ProjectID).first()[0]
data["project_id"] = daily.ProjectID # 当前日报对应的的项目id
data["workintro_id"] = daily.workintroId # 当前日报对应的的工作简介id
data["work_intro"] = session.query(
TbIntro.workintro).filter_by(id=daily.workintroId).first()[0]
return True, "数据查询成功", data
def edit_daily(daily_id, **kwargs):
the_daily = session.query(TDailyRecord).filter_by(ID=daily_id)
date_p = datetime.strptime(kwargs["WorkDate"], '%Y-%m-%d').date()
kwargs["JobDescription"] = session.query(
TbIntro.workintro).filter_by(id=kwargs["workintroId"]).first()[0]
kwargs["updateuser"] = g.user.ID
years, kwargs["Weeks"], kwargs["DayInWeek"] = date_p.isocalendar(
) # 获取周数和星期几
if the_daily.first().userid != g.user.ID:
return False, "不能修改他人的工作简介"
else:
try:
the_daily.update(kwargs)
session.commit()
except exc.SQLAlchemyError as e:
session.rollback()
return False, "工作日报数据修改失败"
return True, "工作日报数据修改成功"
def __init__(self):
self.T_frame = t_T_CompanyFrame.columns
self.frame_query = session.query(
self.T_frame.ID, self.T_frame.Name, self.T_frame.staff,
self.T_frame.IsLeaf,
self.T_frame.AllParentId).order_by(self.T_frame.ID.desc()).all()
# 查询ID, Name, staff, AllParentId
self.frame_dict = self.frame_dict_init()
def search_his_intros(query_like, project_id):
# 查询用户的工作简介名称中含query_like的条目的id与work_intro
tb_intro = TWorkIntroduction # 名字太长 换个短点的名字
query_like = "%{}%".format(query_like)
intros = session.query(tb_intro.id, tb_intro.workintro) \
.filter(and_(tb_intro.workintro.like(query_like), tb_intro.create_user == g.user.ID,
tb_intro.projectid == project_id)) \
.order_by(desc(tb_intro.create_date)).all()
data = [{"id": i[0], "name": i[1]} for i in intros] # 列表生成
return data
def all_properties():
"""
查询工作性质
:return: [{"id": ***, "name": ***}, ... ]
"""
tb_property = TWorkProperty
# 查询工作性质
properties = session.query(tb_property.id,
tb_property.workpropertyname).all()
return [{"id": i[0], "name": i[1]} for i in properties] # 列表生成
def staff_center_data():
staff_msg = session.query(TStaff.StaffName, TStaff.Department, TStaff.create_date)\
.filter_by(ID=g.user.ID).first()
firstday = staff_msg.create_date
today = datetime.now()
workage = (today - firstday).days
msg = {
"name": staff_msg.StaffName,
"department": staff_msg.Department,
"workage": workage
}
return True, "数据获取成功", msg
def himself_intros(detail):
"""
获取当前用户的工作简介
:param detail: 详细与否
:return: data字典
"""
data_li = []
current_user = g.user.ID
# current_user = 199
tb_intro = TWorkIntroduction # 名字太长 换个短点的名字
# 查询当前用户的所有工作简介 按创建时间排序
his_dailies = session.query(tb_intro).filter_by(userid=current_user) \
.order_by(desc(tb_intro.create_date)).all()
return tb_intro.pack_intro_data(his_dailies, detail)
def edit_intro(intro_id, **kwargs):
tb_intro = TWorkIntroduction # 名字太长 换个短点的名字
the_intro = session.query(tb_intro).filter_by(id=intro_id)
if the_intro.first().userid != g.user.ID:
return False, "不能修改他人创建的工作简介"
else:
try:
the_intro.update(kwargs)
session.commit()
except exc.SQLAlchemyError as e:
session.rollback()
raise e
# return False, "工作简介数据修改失败"
return True, "工作简介修改成功"
def fuzzy_query_by_name(query_like):
"""
查询项目名称中含query_like的条目 并按照日期降序排列
:param query_like: like查询字符串
:return:[{"id": ***, "name": ***}, ... ]
"""
tb_project = TProjectSummary
# 查询项目名称中含query_like的条目的id与project_name
query_like = "%{}%".format(query_like)
projects = session.query(tb_project.ID, tb_project.ProjectName) \
.filter(tb_project.ProjectName.like(query_like)) \
.order_by(desc(tb_project.create_date)).all()
data = [{"id": i[0], "name": i[1]} for i in projects] # 列表生成
return True, "数据查询成功", data
def get_the_intro(intro_id):
data = {} # 每次循环需要重新新建data字典
intro = session.query(TWorkIntroduction).filter_by(id=intro_id).first()
if intro is None:
return False, "无此工作简介", data
if intro.userid != g.user.ID:
return False, "不能查看他人的工作简介", data
data["intro_id"] = intro.id # 工作简介id
data["work_intro"] = intro.workintro # 工作简介名称
data["project_id"] = intro.projectid # 工作简介id
data["project_name"] = session.query(TbProject.ProjectName) \
.filter_by(ID=intro.projectid).first()[0] # 项目名称
data["work_address"] = intro.workaddress # 工作地址
# 查询这个工作简介对应的工作性质
the_property = session.query(TWorkProperty.workpropertyname, TWorkProperty.id) \
.filter_by(id=intro.workproperty).first()
if the_property is None:
data["work_property"], data["work_property_id"] = "无", 0
else:
data["work_property"], data[
"work_property_id"] = the_property.workpropertyname, the_property.id
data["remarks"] = intro.remarks # 备注
return True, "数据查询成功", data
def fuzzy_query_by_name(project_id, query_like):
"""
查询工作简介名称中含query_like的条目(且属于当前项目,可选) 并按照日期降序排列
:param query_like: 限制条件 like查询字符串
:param project_id: 限制条件 项目id
:return:[{"id": ***, "name": ***}, ... ]
"""
tb_intros = TWorkIntroduction
# 查询工作简介名称中含query_like的条目的id与work_intro
condition = and_(tb_intros.workintro.like(query_like),
tb_intros.userid == g.user.ID)
if project_id: # 如果有project_id 则将其加入and条件中
condition = and_(condition, tb_intros.projectid == project_id)
intros = session.query(tb_intros.id, tb_intros.workintro) \
.filter(condition).order_by(desc(tb_intros.create_date)).all()
return [{"id": i[0], "name": i[1]} for i in intros] # 列表生成
def get_node_li(self):
"""
获取结点列表
:return:
"""
node_li = []
frams = session.query(self.t_frame).all()
for fram in frams:
new_code = copy.deepcopy(self.node)
new_code["ID"] = fram.ID
new_code["Name"] = fram.Name
new_code["children_li"] = fram.ChildId.strip(',').split(
',') if fram.ChildId else []
new_code["staff_li"] = fram.staff.strip(',').split(
',') if fram.staff else []
new_code["level"] = fram.Level
node_li.append(new_code)
return node_li
def get_staff_li(self, frame_id):
staff = session.query(self.T_frame.staff) \
.filter(self.T_frame.ID == frame_id).first()
staff_str = staff[0] if staff else ""
staff_li = staff_str.strip(',').split(',')
return staff_li
def get_all_staff():
all_staff = session.query(TStaff.ID, TStaff.StaffName).all()
return [{
"id": staff.ID,
"name": staff.StaffName
} for staff in all_staff]
def get_department_by_id(id):
department = session.query(TStaff.Department).filter_by(ID=id).first()
department = department[0] if department else ""
return department
def his_all_intros():
tb_intro = TWorkIntroduction # 名字太长 换个短点的名字
his_intros = session.query(
tb_intro.id, tb_intro.workintro).filter_by(userid=g.user.ID).all()
return [{"id": i.id, "name": i.workintro} for i in his_intros] # 列表生成
