def _start_plotly_graph(graph_data):
""" Creates a Offline Plotly graph from a SQL database. """
logger.primary_logger.debug("SQL Columns: " + str(graph_data.selected_sensors_list))
logger.primary_logger.debug("SQL Table(s): " + graph_data.graph_db_table)
logger.primary_logger.debug("SQL Start DateTime: " + graph_data.graph_datetime_start)
logger.primary_logger.debug("SQL End DateTime: " + graph_data.graph_datetime_end)
try:
# Adjust dates to Database timezone in UTC 0
new_time_offset = graph_data.datetime_offset * -1
get_sql_graph_start = adjust_datetime(graph_data.graph_datetime_start, new_time_offset)
get_sql_graph_end = adjust_datetime(graph_data.graph_datetime_end, new_time_offset)
graph_data.sql_ip = get_one_db_entry(graph_data.graph_db_table, db_v.ip, database=graph_data.db_location)
for var_column in graph_data.selected_sensors_list:
var_sql_query = "SELECT " + var_column + \
" FROM " + graph_data.graph_db_table + \
" WHERE " + var_column + \
" IS NOT NULL AND DateTime BETWEEN datetime('" + get_sql_graph_start + \
"') AND datetime('" + get_sql_graph_end + \
"') AND ROWID % " + str(graph_data.sql_queries_skip + 1) + " = 0" + \
" ORDER BY " + db_v.all_tables_datetime + " DESC" + \
" LIMIT " + str(graph_data.max_sql_queries)
var_time_sql_query = "SELECT " + db_v.all_tables_datetime + \
" FROM " + graph_data.graph_db_table + \
" WHERE " + var_column + \
" IS NOT NULL AND DateTime BETWEEN datetime('" + get_sql_graph_start + \
"') AND datetime('" + get_sql_graph_end + \
"') AND ROWID % " + str(graph_data.sql_queries_skip + 1) + " = 0" + \
" ORDER BY " + db_v.all_tables_datetime + " DESC" + \
" LIMIT " + str(graph_data.max_sql_queries)
original_sql_column_date_time = sql_execute_get_data(var_time_sql_query, graph_data.db_location)
sql_column_date_time = []
for var_d_time in original_sql_column_date_time:
sql_column_date_time.append(adjust_datetime(var_d_time[0], graph_data.datetime_offset))
if var_column == db_v.all_tables_datetime:
graph_data.datetime_entries_in_db = len(sql_column_date_time)
elif var_column == db_v.sensor_name or var_column == db_v.ip:
graph_data.graph_data_dic[var_column][0] = _get_clean_sql_data(
var_sql_query, graph_data.db_location, data_to_float=False
)
graph_data.graph_data_dic[var_column][1] = sql_column_date_time
else:
graph_data.graph_data_dic[var_column][0] = _get_clean_sql_data(var_sql_query, graph_data.db_location)
graph_data.graph_data_dic[var_column][1] = sql_column_date_time
_plotly_graph(graph_data)
except Exception as error:
logger.primary_logger.warning("Plotly Graph Generation Failed: " + str(error))
def get_main_db_first_last_date(utc0_hour_offset=0):
""" Returns First and Last recorded date in the SQL Database as a String. """
sql_query = "SELECT Min(" + str(db_v.all_tables_datetime) + ") AS First, " + \
"Max(" + str(db_v.all_tables_datetime) + ") AS Last " + \
"FROM " + str(db_v.table_interval)
textbox_db_dates = "Database Access Error: "
try:
first_date, last_date = sql_execute_get_data(sql_query)[0]
first_date = adjust_datetime(first_date, utc0_hour_offset)
last_date = adjust_datetime(last_date, utc0_hour_offset)
textbox_db_dates = str(first_date) + " < -- > " + str(last_date)
except Exception as error:
logger.primary_logger.error("Get First & Last DateTime from Interval Recording DB Failed: " + str(error))
textbox_db_dates += str(error)
return textbox_db_dates
def _get_file_creation_date(file_location):
file_creation_date = "File Not Found"
if os.path.isfile(file_location):
utc0_hour_offset = app_config_access.primary_config.utc0_hour_offset
file_creation_date = os.path.getmtime(file_location)
file_creation_date = datetime.utcfromtimestamp(file_creation_date).strftime("%Y-%m-%d %H:%M:%S")
file_creation_date = adjust_datetime(file_creation_date, hour_offset=utc0_hour_offset)
return file_creation_date
def _get_sensor_html_table_code(sensor_id):
sensor_id = get_clean_sql_table_name(sensor_id)
sensor_name = get_one_db_entry_wrapper(sensor_id, db_v.sensor_name)
sensor_ip = get_one_db_entry_wrapper(sensor_id, db_v.ip)
last_contact = adjust_datetime(
get_one_db_entry_wrapper(sensor_id, db_v.all_tables_datetime),
app_config_access.primary_config.utc0_hour_offset)
html_code = mqtt_sub_table_entry_template.replace("{{ SensorID }}",
sensor_id)
html_code = html_code.replace("{{ SensorHostName }}", sensor_name)
html_code = html_code.replace("{{ IPAddress }}", sensor_ip)
html_code = html_code.replace("{{ LastContact }}", last_contact)
return [html_code, last_contact]
def _get_sensor_id_and_last_checkin_date_as_list(sort_by_date_time=True):
utc0_hour_offset = app_config_access.primary_config.utc0_hour_offset
sensor_id_list = get_sqlite_tables_in_list(db_loc)
return_sensor_ids_list = []
for sensor_id in sensor_id_list:
last_checkin_datetime = get_one_db_entry_wrapper(
sensor_id, db_v.all_tables_datetime)
last_checkin_datetime = _get_converted_datetime(
adjust_datetime(last_checkin_datetime, utc0_hour_offset))
return_sensor_ids_list.append([sensor_id, last_checkin_datetime])
if sort_by_date_time:
return_sensor_ids_list.sort(key=lambda x: x[1], reverse=True)
return return_sensor_ids_list
def html_atpro_mqtt_subscriber_sensors_list():
utc0_hour_offset = app_config_access.primary_config.utc0_hour_offset
mqtt_table_last_updated = app_cached_variables.mqtt_subscriber_sensors_html_list_last_updated
mqtt_info_last_updated = adjust_datetime(mqtt_table_last_updated,
utc0_hour_offset)
run_script = ""
if app_cached_variables.mqtt_subscriber_sensors_html_list == generating_mqtt_table_html:
run_script = "CreatingMQTTSubTable();"
return render_template(
"ATPro_admin/page_templates/mqtt-subscriber-sensors-list.html",
DateTimeOffset=str(app_config_access.primary_config.utc0_hour_offset),
SQLMQTTSensorsInDB=str(
app_cached_variables.mqtt_subscriber_sensors_count),
MQTTLastTableUpdateDatetime=str(mqtt_info_last_updated) + " UTC" +
str(utc0_hour_offset),
HTMLSensorsTableCode=app_cached_variables.
mqtt_subscriber_sensors_html_list,
RunScript=run_script)
def _get_sensor_html_table_code(sensor_id):
utc0_hour_offset = app_config_access.primary_config.utc0_hour_offset
sensor_name = get_one_db_entry_wrapper(sensor_id, db_v.sensor_name)
sensor_ip = get_one_db_entry_wrapper(sensor_id, db_v.ip)
sw_version = get_one_db_entry_wrapper(sensor_id,
db_v.kootnet_sensors_version)
first_contact = get_one_db_entry_wrapper(sensor_id,
db_v.all_tables_datetime,
order="ASC")
raw_datetime = get_one_db_entry_wrapper(sensor_id,
db_v.all_tables_datetime)
last_contact = adjust_datetime(raw_datetime, utc0_hour_offset)
html_code = checkin_table_entry_template.replace("{{ SensorID }}",
sensor_id)
html_code = html_code.replace("{{ SensorHostName }}", sensor_name)
html_code = html_code.replace("{{ IPAddress }}", sensor_ip)
html_code = html_code.replace("{{ SoftwareVersion }}", sw_version)
html_code = html_code.replace("{{ FirstContact }}", first_contact)
html_code = html_code.replace("{{ LastContact }}", last_contact)
return [html_code, last_contact]
def _get_sensor_info_string(sensor_id):
get_sensor_checkin_count_per_id_sql = "SELECT count('" + db_v.all_tables_datetime + "') FROM '" + sensor_id + "';"
checkin_count = sql_execute_get_data(get_sensor_checkin_count_per_id_sql,
sql_database_location=db_loc)
utc0_hour_offset = app_config_access.primary_config.utc0_hour_offset
db_datetime_entry = get_one_db_entry_wrapper(sensor_id,
db_v.all_tables_datetime)
adjusted_datetime = adjust_datetime(db_datetime_entry, utc0_hour_offset)
installed_sensors = get_one_db_entry_wrapper(
sensor_id, db_v.sensor_check_in_installed_sensors)
return render_template(
"ATPro_admin/page_templates/sensor_checkins/sensor-checkin-info-template.html",
SensorID=sensor_id,
SensorName=get_one_db_entry_wrapper(sensor_id, db_v.sensor_name),
SensorIP=get_one_db_entry_wrapper(sensor_id, db_v.ip),
LastCheckinDate=adjusted_datetime,
TotalCheckins=get_sql_element(checkin_count),
SoftwareVersion=get_one_db_entry_wrapper(sensor_id,
db_v.kootnet_sensors_version),
SensorUptime=get_one_db_entry_wrapper(sensor_id, db_v.sensor_uptime),
InstalledSensors=installed_sensors)
def html_atpro_checkin_sensors_list():
utc0_hour_offset = app_config_access.primary_config.utc0_hour_offset
checkin_info_last_updated = app_cached_variables.checkins_sensors_html_list_last_updated
checkins_sensors_html_list_last_updated = adjust_datetime(
checkin_info_last_updated, utc0_hour_offset)
run_script = ""
if app_cached_variables.checkins_sensors_html_table_list == updating_checkin_info_html_msg:
run_script = "CreatingSensorCheckinsTable();"
return render_template(
"ATPro_admin/page_templates/sensor_checkins/checkin-sensors-list.html",
DateTimeOffset=str(app_config_access.primary_config.utc0_hour_offset),
SQLSensorsInDB=str(app_cached_variables.checkins_db_sensors_count),
CheckinsLastTableUpdateDatetime=str(
checkins_sensors_html_list_last_updated) + " UTC" +
str(utc0_hour_offset),
ContactInPastDays=str(
app_config_access.checkin_config.count_contact_days),
TotalSensorsContactDays=str(
app_cached_variables.checkins_db_sensors_count_from_past_days),
HTMLSensorsTableCode=app_cached_variables.
checkins_sensors_html_table_list,
RunScript=run_script)