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_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_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 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 []
while True:
# 计算每日预估能耗nage
print('开始计算每日预估能耗')
start_time = '"' + (date.today() -
timedelta(7)).strftime('%Y-%m-%d') + " 00:00:00" + '"'
last_time = '"' + (date.today()).strftime('%Y-%m-%d') + " 23:59:59" + '"'
# end_time = "'" + (date.today()).strftime('%Y-%m-%d ') + datetime.now().strftime('%H:%M:%S') + "'"
# start_time = "'" + date.today().strftime('%Y-%m-%d') + " 00:00:00" + "'"
# today_end_time = "'" + datetime.now().strftime('%Y-%m-%d %H:%M:%S') + "'"
# 今日运行小时能耗
sql1 = f'select sum(IncremenValue) as value from IncrementElectricTable where CollectionDate between {start_time} and {last_time} '
# # 昨日同等小时能耗
# sql2 = f'select sum(IncremenValue) as value from IncrementElectricTable where CollectionDate between {yesterday_start_time} and {yesterday_end_time} '
# # 昨日总能耗
# sql3 = f'select sum(IncremenValue) as value from IncrementElectricTable where CollectionDate between {yesterday_start_time} and {yesterday_last_time} '
result1 = db_session.execute(sql1).fetchall()
# result2 = db_session.execute(sql2).fetchall()
# result3 = db_session.execute(sql3).fetchall()
save_energy = redis_coon.hget(REDIS_TABLENAME, 'save_energy')
if result1[0]['value'] is not None:
print(result1[0]['value'])
data = float(132900) - float('%.2f' % result1[0]['value'])
value = float(save_energy) + float(data)
redis_coon.hset(REDIS_TABLENAME, 'save_energy', data)
redis_coon.hset(REDIS_TABLENAME, 'save_energy_time',
datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
# if result2[0]['value'] is not None:
# print(result2[0]['value'])
# redis_coon.hset(REDIS_TABLENAME, 'yesterday_energy', result2[0]['value'])
# if result3[0]['value'] is not None:
# print(result3[0]['value'])
def exportx(start_time, end_time, energy_type):
try:
# 创建数据流
output = BytesIO()
# 创建excel work book
writer = pd.ExcelWriter(output, engine='xlsxwriter')
workbook = writer.book
# 创建excel sheet
worksheet = workbook.add_worksheet('sheet1')
# cell 样式
cell_format = workbook.add_format({
'bold': 1,
'border': 1,
'align': 'center',
'valign': 'vcenter',
'fg_color': 'Blue'
})
col = 0
columns = ['区域', '设备', '能耗值', '开始时间', '结束时间', '单位']
for item in columns:
worksheet.write(0, col, item, cell_format)
col += 1
sql = ''
if energy_type == '电':
sql = "select AreaName,Address,sum(IncremenValue) as value,Unit from IncrementElectricTable where CollectionDate between" + start_time + " and " + end_time + " group by Address"
if energy_type == '水':
sql = "select AreaName,Address,sum(IncremenValue) as value,Unit from IncrementWaterTable where CollectionDate between" + start_time + " and " + end_time + " group by AreaName"
all_data = db_session.execute(sql).fetchall()
print(all_data)
i = 1
for ta in all_data:
for cum in columns:
if cum == '区域':
worksheet.write(i, columns.index(cum), ta[0])
if cum == '设备':
worksheet.write(i, columns.index(cum), ta[1])
if cum == '能耗值':
value = '%.2f' % float(
ta[2]) if ta[2] is not None else '0.0'
worksheet.write(i, columns.index(cum), value)
if cum == '单位':
worksheet.write(i, columns.index(cum), ta[3])
# if cum == '单位' and energy_type == '水':
# worksheet.write(i, columns.index(cum), 'm³')
if cum == '开始时间':
worksheet.write(i, columns.index(cum),
request.values.get('start_time'))
if cum == '结束时间':
worksheet.write(i, columns.index(cum),
request.values.get('end_time'))
i += 1
writer.close()
output.seek(0)
return output
except Exception as e:
print(str(e))
return json.dumps({
'code': '200',
'mes': '查询失败',
'error': str(e)
},
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)
now_year = date.today().strftime('%Y')
now_month = date.today().strftime('%m')
now_day = date.today().strftime('%d')
year_day_data = get_day_of_year(now_year, int(now_month), int(now_day))
# 今日用电总量
sql1 = f'select sum(IncremenValue) as value from IncrementElectricTable where CollectionDate between {today_start_time} and {today_end_time} '
# 昨日同等小时能耗
sql2 = f'select sum(IncremenValue) as value from IncrementElectricTable where CollectionDate between {yesterday_start_time} and {yesterday_end_time} '
# 昨日总能耗
sql3 = f'select sum(IncremenValue) as value from IncrementElectricTable where CollectionDate between {yesterday_start_time} and {yesterday_last_time} '
# 今年用电总量
sql4 = f'select sum(IncremenValue) as value from IncrementElectricTable where CollectionDate between {this_year_start} and {this_year_end} '
# 本月用电总量
sql5 = f'select sum(IncremenValue) as value from IncrementElectricTable where CollectionDate between {this_month_start} and {this_month_end} '
result1 = db_session.execute(sql1).fetchall()
result2 = db_session.execute(sql2).fetchall()
result3 = db_session.execute(sql3).fetchall()
result4 = db_session.execute(sql4).fetchall()
result5 = db_session.execute(sql5).fetchall()
today_energy = 1 if result1[0]['value'] is None else result1[0]['value']
if result1[0]['value'] is not None:
print(result1[0]['value'])
redis_coon.hset(REDIS_TABLENAME, 'today_energy',
result1[0]['value'])
if result2[0]['value'] is not None:
print(result2[0]['value'])
redis_coon.hset(REDIS_TABLENAME, 'yesterday_energy',
result2[0]['value'])
if result3[0]['value'] is not None:
print(result3[0]['value'])