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