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] # 列表生成