def repairs():
"""
添加设备维修任务
:return:
"""
if request.method == 'GET':
data = db_session.query(Repair).all()
return json.dumps({
'code': '10001',
'message': '操作成功',
'data': data
},
cls=AlchemyEncoder,
ensure_ascii=False)
if request.method == 'POST':
json_data = request.values
data = Repair(EquipmentCode=json_data.get('EquipmentCode'),
No=get_no(json_data.get('ApplyTime')),
Worker=current_user.Name,
ApplyTime=json_data.get('ApplyTime'),
FaultExpound=json_data.get('FaultExpound'))
equipment = db_session.query(Equipment).filter_by(
EquipmentCode=json_data.get('EquipmentCode')).first()
equipment.Status = '待接单'
db_session.add_all([data, equipment])
db_session.commit()
db_session.close()
return json.dumps({
'code': '10000',
'message': '操作成功'
},
cls=AlchemyEncoder,
ensure_ascii=False)
def error_handler(e):
"""全局捕获异常"""
db_session.rollback()
re_path = request.path
re_func = request.url_rule.endpoint.split('.')[1]
re_method = request.method
# root_path = os.path.abspath(os.path.dirname(os.path.dirname(__file__)))
# file_path = os.path.join(root_path, 'logs\\logs.txt')
ip = socket.gethostbyname(socket.gethostname())
now_time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
result = f"{now_time} -- {ip} -- {re_path} -- {re_func} -- {re_method} -- {e}"
print(result)
db_session.add(
RunError(Time=now_time,
IP=ip,
Path=re_path,
Func=re_func,
Method=re_method,
Error=e))
db_session.commit()
db_session.close()
my_log(e)
return json.dumps({
'code': '2000',
'msg': result
},
cls=MyEncoder,
ensure_ascii=False)
def energy_contrast():
try:
hours = [
'00:00:00', '01:00:00', '02:00:00', '03:00:00', '04:00:00',
'05:00:00', '06:00:00', '07:00:00', '08:00:00', '09:00:00',
'10:00:00', '11:00:00', '12:00:00', '13:00:00', '14:00:00',
'15:00:00', '16:00:00', '17:00:00', '18:00:00', '19:00:00',
'20:00:00', '21:00:00', '22:00:00', '23:00:00', '24:00:00'
]
# yesterday = (datetime.now() - timedelta(1)).strftime('%Y-%m-%d ')
yesterday = request.values.get('date')
now = datetime.now().strftime('%Y-%m-%d ')
now_hour = datetime.now().strftime('%H:00:00')
rows = []
for i in range(0, len(hours)):
if hours[i] != '24:00:00':
yesterday_start_time = '"' + yesterday + ' ' + hours[i] + '"'
yesterday_end_time = '"' + yesterday + ' ' + hours[i + 1] + '"'
now_start_time = '"' + now + hours[i] + '"'
now_end_time = '"' + now + hours[i + 1] + '"'
sql1 = f'select sum(IncremenValue) as value from IncrementElectricTable where CollectionDate between {yesterday_start_time} and {yesterday_end_time}'
result1 = db_session.execute(sql1).fetchall()
yesterday_value = result1[0]['value'] if result1[0][
'value'] is not None else 0
if hours[i] <= now_hour:
sql2 = f'select sum(IncremenValue) as value from IncrementElectricTable where CollectionDate between {now_start_time} and {now_end_time}'
result2 = db_session.execute(sql2).fetchall()
today_value = result2[0]['value'] if result2[0][
'value'] is not None else 0
else:
today_value = ' '
data = {
'时间': hours[i],
'今日能耗': today_value,
'对比日能耗': yesterday_value
}
rows.append(data)
db_session.close()
return json.dumps({
'code': '200',
'mes': '查询成功',
'data': rows
},
cls=MyEncoder,
ensure_ascii=False)
except Exception as e:
print(str(e))
return json.dumps({
'code': '200',
'mes': '查询失败',
'error': str(e)
},
ensure_ascii=False)
def get_index_equipment():
try:
query_type_data = db_session.query(
Equipment.EquipmentType).filter_by().all()
query_floor_data = db_session.query(
Equipment.Floor).filter_by().order_by(Equipment.Floor.asc()).all()
equipment_type = list(set(i[0] for i in query_type_data))
equipment_floor = list(set(i[0] for i in query_floor_data))
data = []
for item_floor in equipment_floor:
result = {"楼层": item_floor}
for item_type in equipment_type:
query_result = db_session.query(Equipment).filter_by(
Floor=item_floor, EquipmentType=item_type).all()
result[item_type] = len(query_result)
data.append(result)
db_session.close()
return json.dumps({
'code': '200',
'mes': '查询成功',
'data': data
},
ensure_ascii=False)
except InvalidRequestError as e:
print('/IndexEquipment', '1')
db_session.rollback()
print('/IndexEquipment', '2')
re_path = request.path
re_func = request.url_rule.endpoint.split('.')[1]
re_method = request.method
# root_path = os.path.abspath(os.path.dirname(os.path.dirname(__file__)))
# file_path = os.path.join(root_path, 'logs\\logs.txt')
ip = socket.gethostbyname(socket.gethostname())
now_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
db_session.add(
RunError(Time=now_time,
IP=ip,
Path=re_path,
Func=re_func,
Method=re_method,
Error=str(e)))
db_session.commit()
my_log(str(e))
return json.dumps({'code': '200', 'mes': '事务回滚'}, ensure_ascii=False)
except Exception as e:
print(str(e))
return json.dumps({
'code': '200',
'mes': '查询失败',
'error': str(e)
},
ensure_ascii=False)
def repair_tasks(p):
"""
更新维修任务表的状态
:param p: 更改工单状态
:return:
"""
if p == 'jiedan':
json_data = request.values
data = db_session.query(Repair).filter_by(
No=json_data.get('No')).first()
data.Status = '维修中'
data.ReceiveWorker = current_user.Name
data.ReceiveTime = json_data.get('Time')
equipment = db_session.query(Equipment).filter_by(
EquipmentCode=json_data.get('EquipmentCode')).first()
equipment.Status = '维修中'
db_session.add_all([data, equipment])
db_session.commit()
db_session.close()
return json.dumps({
'code': '10001',
'message': '操作成功'
},
cls=AlchemyEncoder,
ensure_ascii=False)
if p == 'over':
json_data = request.values
data = db_session.query(Repair).filter_by(
No=json_data.get('No')).first()
task_data = RepairTask(EquipmentCode=data.EquipmentCode,
No=data.No,
Status='维修完成',
Worker=data.Worker,
ReceiveWorker=data.ReceiveWorker,
Content=json_data.get('Content'),
ApplyTime=data.ApplyTime,
ReceiveTime=data.ReceiveTime,
EndTime=json_data.get('EndTime'))
equipment = db_session.query(Equipment).filter_by(
EquipmentCode=json_data.get('EquipmentCode')).first()
equipment.Status = '运行中'
db_session.add_all([task_data, equipment])
db_session.delete(data)
db_session.commit()
db_session.close()
return json.dumps({
'code': '10001',
'message': '操作成功'
},
cls=AlchemyEncoder,
ensure_ascii=False)
def count_energy(tags, start_time, end_time):
try:
result = 0.0
for tag in tags:
sql = f'select sum(IncremenValue) as value from IncrementElectricTable where Address="{tag}" and CollectionDate between {start_time} and {end_time} '
data = db_session.execute(sql).fetchall()
if data[0]['value'] is not None:
result += float(data[0]['value'])
else:
result += 0.0
return result
except Exception as err:
db_session.rollback()
db_session.close()
my_log(err)
def get_pie():
start_time = "'" + request.values.get('StartTime') + "'"
end_time = "'" + request.values.get('EndTime') + "'"
if request.values.get('energy_type') == '水':
sql = f'select sum(IncremenValue) as value from IncrementWaterTable where CollectionDate between {start_time} and {end_time} '
result = db_session.execute(sql).fetchall()
value_data = 0 if result[0]['value'] is None else result[0]['value']
data = [{'设备类型': '设备能耗', '能耗': value_data}]
return json.dumps({
'code': '200',
'mes': '查询成功',
'data': data
},
ensure_ascii=False)
if request.values.get('energy_type') == '电':
sql1 = f'select sum(IncremenValue) as value from IncrementElectricTable where CollectionDate between {start_time} and {end_time} and AreaName like "%照明%"'
sql2 = f'select sum(IncremenValue) as value from IncrementElectricTable where CollectionDate between {start_time} and {end_time} and AreaName like "%空調%"'
result1 = db_session.execute(sql1).fetchall()
result2 = db_session.execute(sql2).fetchall()
light_data = 0 if result1[0]['value'] is None else result1[0]['value']
kt_data = 0 if result2[0]['value'] is None else result2[0]['value']
data = [{
'设备类型': '照明设备',
'能耗': light_data
}, {
'设备类型': '制冷设备',
'能耗': kt_data
}]
db_session.close()
return json.dumps({
'code': '200',
'mes': '查询成功',
'data': data
},
ensure_ascii=False)
else:
return json.dumps({
'code': '200',
'mes': '查询成功',
'data': []
},
ensure_ascii=False)
def count_floor_energy(tags, start_time, end_time, water_day_total,
total_energy):
try:
total = 0.0
i = 0
floorData = []
print('total_energy_2: ', total_energy)
for tag in tags:
i += 1
floor_total_energy = 0.0
AreaName = f'厚德楼{i}楼'
for item in tag:
print('采集点: ', item)
sql = f'select sum(IncremenValue) as value from IncrementElectricTable where Address="{item}" and CollectionDate between {start_time} and {end_time} '
data = db_session.execute(sql).fetchall()
if data[0]['value'] is not None:
floor_total_energy += float(data[0]['value'])
total += floor_total_energy
else:
floor_total_energy += 0.0
total += 0.0
ratio = '%.2f' % (floor_total_energy / total_energy * 100)
if i == 7 or i == 8:
floorData.append({
'areaName': AreaName,
'electricity': floor_total_energy,
'water': water_day_total,
'ratio': ratio
})
else:
floorData.append({
'areaName': AreaName,
'electricity': floor_total_energy,
'water': 0.0,
'ratio': ratio
})
return floorData
except Exception as err:
db_session.rollback()
db_session.close()
my_log(err)
return []
def keep_plans():
"""保养计划"""
try:
json_data = request.get_json()
# equipments = ['XXF-2', 'XXF-1', 'PYF-1']
equipments = json_data.get('EquipmentCode')
if len(equipments) == 1:
equipment_code = equipments[0]
else:
equipment_code = ' '.join(equipments)
work_time = add_date(json_data.get('WeekTime'),
json_data.get('StartTime'))
work_type = json_data.get('Type')
week_time = '单次' if work_type == '单次' else json_data.get('WeekTime')
data = KeepPlan(EquipmentCode=equipment_code,
No=get_no(json_data.get('ApplyTime')),
Worker=current_user.Name,
ApplyTime=json_data.get('ApplyTime'),
Type=json_data.get('Type'),
StartTime=json_data.get('StartTime'),
Describe=json_data.get('Describe'),
WorkTime=work_time,
WeekTime=week_time)
db_session.add(data)
db_session.commit()
db_session.close()
return json.dumps({
'code': '10001',
'message': '操作成功'
},
cls=AlchemyEncoder,
ensure_ascii=False)
except Exception as e:
logger.error(e)
insertSyslog("error", "保养计划表添加错误：" + str(e), current_user.Name)
return json.dumps({
'code': '20002',
'message': str(e)
},
cls=AlchemyEncoder,
ensure_ascii=False)
def my_log(e):
"""
程序日志记录
:param e:捕获异常参数`
"""
root_path = os.path.abspath(os.path.dirname(os.path.dirname(__file__)))
file_path = os.path.join(root_path, 'run_log\\logs.txt')
call_func = sys._getframe().f_back.f_code.co_name
# user = login_user if login_user is None else 'no login'
with open(file_path, 'a', encoding='utf-8') as f:
print(f'{datetime.datetime.now()} -- {call_func} --- {e}' + "\n\n")
f.write(f'{datetime.datetime.now()} -- {call_func} --- {e}' + "\n\n")
f.close()
db_session.add(
RunError(
Time=datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
Func=call_func,
Error=e))
db_session.commit()
db_session.close()
return '日志写入成功'
def keep_tasks():
"""保养任务表"""
try:
query_data = db_session.query(KeepPlan).filter_by(Status='待保养').all()
if request.method == 'GET':
# 每页多少条
limit = int(request.values.get('limit', '5'))
# 当前页
offset = int(request.values.get('offset', '1'))
for item in query_data:
q = db_session.query(KeepTask).filter_by(No=item.No).first()
if not q and get_time_stamp(item.WorkTime):
data = KeepTask(EquipmentCode=item.EquipmentCode,
No=item.No,
Worker=item.Worker,
Status=item.Status,
ApplyTime=item.ApplyTime,
StartTime=item.StartTime,
WorkTime=item.WorkTime,
WeekTime=item.WeekTime,
Type=item.Type)
db_session.add(data)
db_session.commit()
# if item.Type == '单次':
# pass
# db_session.delete(item)
# db_session.commit()
data = db_session.query(KeepTask).order_by(
KeepTask.ApplyTime.desc()).limit(limit).offset(
(offset - 1) * limit)
total = db_session.query(KeepTask).count()
return json.dumps(
{
'code': '10001',
'message': '操作成功',
'data': {
'rows': data.all(),
'total': total
}
},
cls=AlchemyEncoder,
ensure_ascii=False)
if request.method == 'POST':
json_data = request.values
no = json_data.get('No')
end_time = json_data.get('EndTime')
content = json_data.get('Content')
item = db_session.query(KeepTask).filter_by(No=no).first()
data = KeepRecord(EquipmentCode=item.EquipmentCode,
No=no,
Worker=item.Worker,
Status='已完成',
Type=item.Type,
KeepWorker=current_user.Name,
ApplyTime=item.ApplyTime,
StartTime=item.StartTime,
Describe=item.Describe,
Content=content,
WeekTime=item.WeekTime,
EndTime=end_time)
db_session.delete(item)
db_session.commit()
keep_plan = db_session.query(KeepPlan).filter_by(No=no).first()
if keep_plan and keep_plan.Type == '周期':
keep_plan.WorkTime = add_date(keep_plan.WeekTime,
keep_plan.WorkTime)
db_session.add_all([data, keep_plan])
db_session.commit()
db_session.close()
return json.dumps({
'code': '10001',
'message': '操作成功'
},
cls=AlchemyEncoder,
ensure_ascii=False)
else:
db_session.delete(keep_plan)
db_session.add(data)
db_session.commit()
db_session.close()
return json.dumps({
'code': '10001',
'message': '操作成功'
},
cls=AlchemyEncoder,
ensure_ascii=False)
except Exception as e:
logger.error(e)
insertSyslog("error", "保养任务表修改错误：" + str(e), current_user.Name)
return json.dumps({
'code': '20002',
'message': str(e)
},
cls=AlchemyEncoder,
ensure_ascii=False)
def energys():
try:
energy_type = request.values.get('energy_type')
start_time = '"' + request.values.get('start_time') + '"'
end_time = '"' + request.values.get('end_time') + '"'
# start_time = '"' + '2020-01-31 23:00:00' + '"'
# end_time = '"' + '22020-10-31 23:00:00' + '"'
data = []
if energy_type == '电':
sql = f'select AreaName,Address, sum(IncremenValue) as value from IncrementElectricTable where CollectionDate between {start_time} and {end_time} group by Address'
results = db_session.execute(sql).fetchall()
db_session.close()
# print(results)
for result in results:
value = '%.2f' % result[2] if result[2] is not None else '0.0'
data.append({
"AreaName": result[0],
"Address": result[1],
"Value": value,
"StartTime": request.values.get('start_time'),
"EndTime": request.values.get('end_time'),
"Unit": "KW/h"
})
else:
sql = f'select AreaName,Address, sum(IncremenValue) as value from IncrementWaterTable where CollectionDate between {start_time} and {end_time} group by AreaName'
results = db_session.execute(sql).fetchall()
db_session.close()
# print(results)
for result in results:
value = '%.2f' % float(
result[2]) if result[2] is not None else '0.0'
data.append({
"AreaName": result[0],
"Address": result[1],
"Value": value,
"StartTime": request.values.get('start_time'),
"EndTime": request.values.get('end_time'),
"Unit": "m³"
})
# data = [(result[0], result[1], '%.2f' % result[2], request.values.get('start_time'), request.values.get('end_time'), 'm³') for result in results]
db_session.close()
return json.dumps({
'code': '200',
'mes': '查询成功',
'data': data
},
ensure_ascii=False)
except InvalidRequestError as e:
print('/energy', '1')
db_session.rollback()
print('/energy', '2')
re_path = request.path
re_func = request.url_rule.endpoint.split('.')[1]
re_method = request.method
# root_path = os.path.abspath(os.path.dirname(os.path.dirname(__file__)))
# file_path = os.path.join(root_path, 'logs\\logs.txt')
ip = socket.gethostbyname(socket.gethostname())
now_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
db_session.add(
RunError(Time=now_time,
IP=ip,
Path=re_path,
Func=re_func,
Method=re_method,
Error=str(e)))
my_log(str(e))
return json.dumps({'code': '200', 'mes': '事务回滚'}, ensure_ascii=False)
except Exception as e:
# db_session.rollback()
print(str(e))
return json.dumps({
'code': '200',
'mes': '查询失败',
'error': str(e)
},
ensure_ascii=False)
redis_coon.hset(REDIS_TABLENAME, 'indicator', json_data)
start_m = datetime.now().strftime("%Y-%m-01 00:00:00")
end_m = datetime.now().strftime("%Y-%m-01 00:00:00")
# 当前月用电量总和
now_month_sql = f'select sum(IncremenValue) as value from IncrementElectricTable and CollectionDate between {start_m} and {end_m} '
# 楼层实时用能数据
L_tags = [['COM2.KT1F.总有功电量', 'COM2.LIGHT1F.总有功电量'],
['COM2.KT2F.总有功电量', 'COM2.LIGHT2F.总有功电量'],
['COM2.KT3F.总有功电量', 'COM2.LIGHT3F.总有功电量'],
['COM2.KT4F.总有功电量', 'COM2.LIGHT4F.总有功电量'],
['COM2.KT5F.总有功电量', 'COM2.LIGHT5F.总有功电量'],
['COM2.KT6F.总有功电量', 'COM2.LIGHT6F.总有功电量'],
['COM2.KT7F.总有功电量', 'COM2.LIGHT7F.总有功电量'],
['COM2.KT8F.总有功电量', 'COM2.LIGHT8F.总有功电量'],
['COM2.KT9F.总有功电量', 'COM2.LIGHT9F.总有功电量'],
['COM2.KT10F.总有功电量', 'COM2.LIGHT10F.总有功电量'],
['COM2.KT11F.总有功电量', 'COM2.LIGHT11F.总有功电量'],
['COM2.KT12F.总有功电量', 'COM2.LIGHT12F.总有功电量']]
print('today_energy: ', today_energy)
floor_data = count_floor_energy(L_tags, today_start_time,
today_end_time, water_day_total,
today_energy)
data = json.dumps(floor_data, ensure_ascii=False)
redis_coon.hset(REDIS_TABLENAME, 'floorData', data)
print('结束计算能耗数据')
time.sleep(180)
except Exception as e:
db_session.rollback()
db_session.close()
my_log(e)
