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 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 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 get_avg_data(args): sensor_exist = session.query(Sensor.sid).filter_by(sid=args["sensor_id"]).first() if not sensor_exist: return False, "", [] table_name = 'sensor_data_%s_avg' % str(args["sensor_id"]) start = int(args["start"] + "0000") end = int(args["end"] + "0000") sql_0 = """ (SELECT `value`, `time` FROM {0} WHERE time <= {1} ORDER BY `time` DESC LIMIT 1) UNION ALL (SELECT `value`, `time` FROM {0} WHERE (time BETWEEN {1} AND {2}) AND accuracy={3}) UNION ALL (SELECT `value`, `time` FROM {0} WHERE time >= {2} ORDER BY `time` LIMIT 1); """.format(table_name, start, end, args["interval"]) all_data = session.execute(sql_0).fetchall() return True, "数据查询成功", [{"value": i.value, "datetime": time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(i.time/10000))} for i in all_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 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 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 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 pack_daily_data(dailies, detail=None): data_li = [] for daily in dailies: # 遍历当前用户的所有工作简介条目 data = {} # 每次循环需要重新新建data字典 data["daily_id"] = daily.ID # 工作日报id # 工作简介名称 work_intro = session.query( TbIntro.workintro).filter_by(id=daily.workintroId).first() data[ "work_intro"] = "None" if work_intro is None else work_intro[0] # 项目名称 data["project_name"] = session.query( TbProject.ProjectName).filter_by(ID=daily.ProjectID).first()[0] data["work_date"] = str(daily.WorkDate) # 工作日期 if detail: # 详细查询要多出工时,具体事项字段 data["work_hours"] = str(daily.WorkHours) # 工时 data["work_matters"] = daily.WorkMatters # 具体事项 data_li.append(data) # 将data字典添加到data_li数组尾部 return data_li
def edit_node(nid, args): the_node = session.query(Node).filter_by(nid=nid) if not the_node.first(): # 记录不存在 return False, "无此基站" try: the_node.update(args) session.commit() except exc.SQLAlchemyError as e: session.rollback() return False, "基站{0}数据修改失败, 错误原因{1}".format(nid, e) return True, "基站{0} 数据成功修改为{1}".format(nid, args)
def add_daily(**kwargs): kwargs["userid"] = g.user.ID kwargs["createuser"] = g.user.ID kwargs["updateuser"] = g.user.ID kwargs["isdelete"] = 0 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() except exc.SQLAlchemyError as e: session.rollback() return False, "工作日报数据新增失败"
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 sensors_vary_modify(sensor_id, value): the_sensor = session.query(Sensor.sid).filter_by(sid=sensor_id) if not the_sensor.first(): return False, "无此传感器" if not re.match(r'^[1-9]\d*\.\d*|0\.\d*[1-9]\d*|0?\.0+|0|[1-9]\d*$', str(value)): return False, "value值输入有误,应为整数或浮点型" try: the_sensor.update({"allow_vary": float(value)}) session.commit() except exc.SQLAlchemyError as e: session.rollback() return False, "传感器{0}阈值修改失败, 错误原因{1}".format(sensor_id, e) return True, "传感器{0} 阈值成功修改为{1}".format(sensor_id, value)
def his_all_daily(detail, start, end): """ 获取当前用户的工作日报 :param detail: 详细与否 :param start: 开始时间 :param end: 结束时间 :return: data字典 """ current_user = g.user.ID # current_user = 199 tb_daily = TDailyRecord # 名字太长 换个短点的名字 # 查询当前用户的所有工作日报 按创建时间排序 if start and end: start = datetime.strptime(start, '%Y-%m-%d') # 将日期字符串格式化成日期对象 end = datetime.strptime(end, '%Y-%m-%d') condition = and_(TDailyRecord.userid == current_user, TDailyRecord.WorkDate.between(start, end)) his_dailies = session.query(tb_daily).filter(condition).order_by( desc(tb_daily.WorkDate)).all() else: his_dailies = session.query(tb_daily).filter_by( userid=current_user).order_by(desc(tb_daily.WorkDate)).all() return tb_daily.pack_daily_data(his_dailies, detail)
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 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 get_detail_data(args): sensor_exist = session.query(Sensor.sid).filter_by(sid=args["sensor_id"]).first() if not sensor_exist: return False, "无此传感器", [] table_name = 'sensor_data_%s_avg' % str(args["sensor_id"]) start = int(args["start"] + "0000") end = int(args["end"] + "0000") sql_2 = """ SELECT `value`, `time` FROM {0} WHERE time BETWEEN {1} AND {2} ORDER BY `time` DESC; """.format(table_name, start, end) all_data = session.execute(sql_2).fetchall() return True, "数据查询成功", [{"value": i.value, "datetime": time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(i.time/10000))} for i in all_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_the_node(nid): node = session.query(Node).filter_by(nid=nid).first() if not node: return False, "无此基站" return True, "数据获取成功", {"nid": node.nid, "name": node.address, "location": node.location, "status": node.status}
def get_all_nodes(): nodes = session.query(Node).all() return True, "数据获取成功", [{"nid": n.nid, "name": n.address, "location": n.location, "status": n.status} for n in nodes]
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] # 列表生成