315 lines
15 KiB
Python
315 lines
15 KiB
Python
import time
|
|
from datetime import datetime, timedelta
|
|
import mysql.connector
|
|
from openpyxl import load_workbook
|
|
from decimal import Decimal
|
|
import config
|
|
|
|
################################################################################################################
|
|
# PROCEDURES:
|
|
# STEP 1: get all 'new' offline meter files
|
|
# STEP 2: for each new files, iterate all rows and read cell's value and store data to energy data list
|
|
# STEP 3: insert or update energy data to table offline meter hourly in energy database
|
|
# STEP 4: update file status to 'done' or 'error'
|
|
################################################################################################################
|
|
|
|
|
|
def calculate_hourly(logger):
|
|
while True:
|
|
# outer loop to reconnect server if there is a connection error
|
|
################################################################################################################
|
|
# STEP 1: get all 'new' offline meter files
|
|
################################################################################################################
|
|
cnx = None
|
|
cursor = None
|
|
try:
|
|
cnx = mysql.connector.connect(**config.myems_historical_db)
|
|
cursor = cnx.cursor()
|
|
except Exception as e:
|
|
logger.error("Error in step 1.1 of offline meter.calculate_hourly " + str(e))
|
|
if cursor:
|
|
cursor.close()
|
|
if cnx:
|
|
cnx.close()
|
|
# sleep several minutes and continue the outer loop to reconnect the database
|
|
print("Could not connect the MyEMS Historical Database, and go to sleep 60 seconds...")
|
|
time.sleep(60)
|
|
continue
|
|
|
|
print("Connected to MyEMS Historical Database")
|
|
|
|
print("Getting all new offline meter files")
|
|
try:
|
|
query = (" SELECT id, file_name, file_object "
|
|
" FROM tbl_offline_meter_files "
|
|
" WHERE status = 'new' "
|
|
" ORDER BY id ")
|
|
|
|
cursor.execute(query, )
|
|
rows_files = cursor.fetchall()
|
|
except Exception as e:
|
|
logger.error("Error in step 1.2 of offline meter.calculate_hourly " + str(e))
|
|
time.sleep(60)
|
|
continue
|
|
finally:
|
|
if cursor:
|
|
cursor.close()
|
|
if cnx:
|
|
cnx.close()
|
|
|
|
excel_file_list = list()
|
|
if rows_files is not None and len(rows_files) > 0:
|
|
for row_file in rows_files:
|
|
excel_file_list.append({"id": row_file[0],
|
|
"name": row_file[1],
|
|
"file_object": row_file[2]})
|
|
else:
|
|
print("there isn't any new files found, and go to sleep 60 seconds...")
|
|
time.sleep(60)
|
|
continue
|
|
|
|
################################################################################################################
|
|
# STEP 2: for each new files, dump file object to local file and then load workbook from the local file
|
|
################################################################################################################
|
|
for excel_file in excel_file_list:
|
|
print("read data from offline meter file" + excel_file['name'])
|
|
is_valid_file = True
|
|
fw = None
|
|
try:
|
|
fw = open("myems-normalization.blob", 'wb')
|
|
fw.write(excel_file['file_object'])
|
|
fw.close()
|
|
except Exception as e:
|
|
logger.error("Error in step 2.1 of offline meter.calculate_hourly " + str(e))
|
|
if fw:
|
|
fw.close()
|
|
# mark as invalid file
|
|
is_valid_file = False
|
|
|
|
fr = None
|
|
wb = None
|
|
try:
|
|
fr = open("myems-normalization.blob", 'rb')
|
|
wb = load_workbook(fr, data_only=True)
|
|
fr.close()
|
|
except Exception as e:
|
|
logger.error("Error in step 2.2 of offline meter.calculate_hourly " + str(e))
|
|
if fr:
|
|
fr.close()
|
|
# mark as invalid file
|
|
is_valid_file = False
|
|
|
|
energy_data_list = list()
|
|
# grab the active worksheet
|
|
|
|
if is_valid_file:
|
|
ws = wb.active
|
|
|
|
# get timezone offset in minutes, this value will be returned to client
|
|
timezone_offset = int(config.utc_offset[1:3]) * 60 + int(config.utc_offset[4:6])
|
|
if config.utc_offset[0] == '-':
|
|
timezone_offset = -timezone_offset
|
|
|
|
for row in ws.iter_rows(min_row=3, max_row=1024, min_col=1, max_col=34):
|
|
offline_meter_data = dict()
|
|
offline_meter_data['offline_meter_id'] = None
|
|
offline_meter_data['offline_meter_name'] = None
|
|
offline_meter_data['data'] = dict()
|
|
col_num = 0
|
|
|
|
for cell in row:
|
|
col_num += 1
|
|
print(cell.value)
|
|
if col_num == 1:
|
|
# get offline meter ID
|
|
if cell.value is not None:
|
|
offline_meter_data['offline_meter_id'] = cell.value
|
|
else:
|
|
break
|
|
elif col_num == 2:
|
|
# get offline meter name
|
|
if cell.value is None:
|
|
break
|
|
else:
|
|
offline_meter_data['offline_meter_name'] = cell.value
|
|
elif col_num > 3:
|
|
# get date of the cell
|
|
try:
|
|
start_datetime_local = datetime(year=ws['A2'].value,
|
|
month=ws['B2'].value,
|
|
day=col_num - 3)
|
|
except ValueError:
|
|
# invalid date and go to next cell in this row until reach max_col
|
|
continue
|
|
|
|
start_datetime_utc = start_datetime_local - timedelta(minutes=timezone_offset)
|
|
|
|
if cell.value is None:
|
|
# if the cell is empty then stop at that day
|
|
break
|
|
else:
|
|
offline_meter_data['data'][start_datetime_utc] = Decimal(cell.value)
|
|
|
|
if len(offline_meter_data['data']) > 0:
|
|
print("offline_meter_data:" + str(offline_meter_data))
|
|
energy_data_list.append(offline_meter_data)
|
|
|
|
############################################################################################################
|
|
# STEP 3: insert or update energy data to table offline meter hourly in energy database
|
|
############################################################################################################
|
|
print("to valid offline meter id in excel file...")
|
|
if len(energy_data_list) == 0:
|
|
print("Could not find any offline meters in the file...")
|
|
print("and go to process the next file...")
|
|
is_valid_file = False
|
|
else:
|
|
try:
|
|
cnx = mysql.connector.connect(**config.myems_system_db)
|
|
cursor = cnx.cursor()
|
|
except Exception as e:
|
|
logger.error("Error in step 3.1 of offlinemeter.calculate_hourly " + str(e))
|
|
if cursor:
|
|
cursor.close()
|
|
if cnx:
|
|
cnx.close()
|
|
time.sleep(60)
|
|
continue
|
|
|
|
try:
|
|
cursor.execute(" SELECT id, name, hourly_low_limit, hourly_high_limit"
|
|
" FROM tbl_offline_meters ")
|
|
rows_offline_meters = cursor.fetchall()
|
|
except Exception as e:
|
|
logger.error("Error in step 3.2 of offlinemeter.calculate_hourly " + str(e))
|
|
time.sleep(60)
|
|
continue
|
|
finally:
|
|
if cursor:
|
|
cursor.close()
|
|
if cnx:
|
|
cnx.close()
|
|
|
|
if rows_offline_meters is None or len(rows_offline_meters) == 0:
|
|
print("Could not find any offline meters in the MyEMS System Database...")
|
|
time.sleep(60)
|
|
continue
|
|
else:
|
|
offline_meter_id_set = set()
|
|
for row_offline_meter in rows_offline_meters:
|
|
# valid offline meter id in excel file
|
|
offline_meter_id_set.add(row_offline_meter[0])
|
|
|
|
for energy_data_item in energy_data_list:
|
|
if energy_data_item['offline_meter_id'] not in offline_meter_id_set:
|
|
is_valid_file = False
|
|
break
|
|
|
|
for row_offline_meter in rows_offline_meters:
|
|
if row_offline_meter[0] == energy_data_item['offline_meter_id']:
|
|
for key in energy_data_item['data']:
|
|
if row_offline_meter[2] > (energy_data_item['data'][key]/24):
|
|
is_valid_file = False
|
|
break
|
|
elif row_offline_meter[3] < (energy_data_item['data'][key]/24):
|
|
is_valid_file = False
|
|
break
|
|
break
|
|
|
|
if is_valid_file:
|
|
####################################################################################################
|
|
# delete possibly exists offline meter hourly data in myems energy database,
|
|
# and then insert new offline meter hourly data
|
|
####################################################################################################
|
|
try:
|
|
cnx = mysql.connector.connect(**config.myems_energy_db)
|
|
cursor = cnx.cursor()
|
|
except Exception as e:
|
|
logger.error("Error in step 3.2 of offlinemeter.calculate_hourly " + str(e))
|
|
if cursor:
|
|
cursor.close()
|
|
if cnx:
|
|
cnx.close()
|
|
time.sleep(60)
|
|
continue
|
|
|
|
try:
|
|
for energy_data_item in energy_data_list:
|
|
offline_meter_id = energy_data_item['offline_meter_id']
|
|
print(energy_data_item['data'].items())
|
|
for start_datetime_utc, daily_value in energy_data_item['data'].items():
|
|
end_datetime_utc = start_datetime_utc + timedelta(hours=24)
|
|
actual_value = \
|
|
daily_value / (Decimal(24) * Decimal(60) / Decimal(config.minutes_to_count))
|
|
cursor.execute(" DELETE FROM tbl_offline_meter_hourly "
|
|
" WHERE offline_meter_id = %s "
|
|
" AND start_datetime_utc >= %s "
|
|
" AND start_datetime_utc < %s ",
|
|
(offline_meter_id,
|
|
start_datetime_utc.isoformat()[0:19],
|
|
end_datetime_utc.isoformat()[0:19]))
|
|
cnx.commit()
|
|
# todo: check with hourly low limit and hourly high limit
|
|
add_values = (" INSERT INTO tbl_offline_meter_hourly "
|
|
" (offline_meter_id, start_datetime_utc, actual_value) "
|
|
" VALUES ")
|
|
|
|
while start_datetime_utc < end_datetime_utc:
|
|
add_values += " (" + str(offline_meter_id) + ","
|
|
add_values += "'" + start_datetime_utc.isoformat()[0:19] + "',"
|
|
add_values += str(actual_value) + "), "
|
|
start_datetime_utc += timedelta(minutes=config.minutes_to_count)
|
|
|
|
print("add_values:" + add_values)
|
|
# trim ", " at the end of string and then execute
|
|
cursor.execute(add_values[:-2])
|
|
cnx.commit()
|
|
except Exception as e:
|
|
logger.error("Error in step 3.3 of offlinemeter.calculate_hourly " + str(e))
|
|
time.sleep(60)
|
|
continue
|
|
finally:
|
|
if cursor:
|
|
cursor.close()
|
|
if cnx:
|
|
cnx.close()
|
|
|
|
############################################################################################################
|
|
# STEP 4: update file status to 'done' or 'error'
|
|
############################################################################################################
|
|
print("to update offline meter file status to done...")
|
|
try:
|
|
cnx = mysql.connector.connect(**config.myems_historical_db)
|
|
cursor = cnx.cursor()
|
|
except Exception as e:
|
|
logger.error("Error in step 4.1 of offlinemeter.calculate_hourly " + str(e))
|
|
if cursor:
|
|
cursor.close()
|
|
if cnx:
|
|
cnx.close()
|
|
time.sleep(60)
|
|
continue
|
|
|
|
try:
|
|
update_row = (" UPDATE tbl_offline_meter_files "
|
|
" SET status = %s "
|
|
" WHERE id = %s ")
|
|
cursor.execute(update_row, ('done' if is_valid_file else 'error', excel_file['id'],))
|
|
cnx.commit()
|
|
except Exception as e:
|
|
logger.error("Error in step 4.2 of offlinemeter.calculate_hourly " + str(e))
|
|
time.sleep(60)
|
|
continue
|
|
finally:
|
|
if cursor:
|
|
cursor.close()
|
|
if cnx:
|
|
cnx.close()
|
|
|
|
# end of for excel_file in excel_file_list
|
|
|
|
print("go to sleep ...")
|
|
time.sleep(300)
|
|
print("wake from sleep, and go to work...")
|
|
# end of outer while
|
|
|