def set_timetable(data_json):
if not _check_sharedkey():
return "Forbidden", 403
try:
days = [
'monday', 'tuesday', 'wednesday', 'thursday', 'friday', 'saturday',
'sunday', 'pre_holiday', 'holiday'
]
_data = json.loads(data_json)
_id = int(_data['id'])
_day_type_id = int(_data['day_type_id'])
dbmgr = db.DatabaseManager()
if _data['day'] in days:
dbmgr.query(
"""UPDATE gm_timetable
SET {} = ?
WHERE id = ?
""".format(_data['day']), (_day_type_id, _id))
else:
return "Error", 400 # BAD_REQUEST
except Exception:
print("set_timetable_data({}): error".format(data_json))
print(traceback.format_exc())
return "Error", 400 # BAD_REQUEST
return "Ok"
def set_timetable_data(data_json):
if not _check_sharedkey():
return "Forbidden", 403
try:
_data = json.loads(data_json)
dbmgr = db.DatabaseManager()
first = True
for _tt_item in _data:
_orderby = int(_tt_item['orderby'])
_temp_id = int(_tt_item['temp_id'])
_time_hhmm = int(_tt_item['time_hhmm'])
_day_type_id = int(_tt_item['day_type_id'])
if first:
first = False
dbmgr.query(
"""DELETE FROM gm_timetable_type_data
WHERE day_type_id = ?""", (_tt_item['day_type_id'], ))
dbmgr.query(
"""INSERT INTO gm_timetable_type_data
(orderby, temp_id, time_hhmm, day_type_id)
VALUES(?,?,?,?)""",
(_orderby, _temp_id, _time_hhmm, _day_type_id))
_signal_server()
except Exception:
print("set_timetable_data({}): error".format(data_json))
print(traceback.format_exc())
return "Error", 400 # BAD_REQUEST
return "Ok"
def get_temp_chart():
if not _check_sharedkey():
return "Forbidden", 403
dbmgr = db.DatabaseManager()
try:
# , description
cur = dbmgr.query("""
SELECT
datetime(datetime,'unixepoch') AS datetime,
datetime AS datetime_epoch, int_temp_c,
ext_temp_c, req_temp_c, description
FROM gm_log
WHERE event = '.'
AND date(datetime,'unixepoch') > datetime(date('now','-7 day'))
""")
r = [
dict((cur.description[i][0], value) for i, value in enumerate(row))
for row in cur.fetchall()
]
# one = False
# return json.dumps((r[0] if r else None) if one else r)
return json.dumps(r)
except Exception:
print("get_temp_chart(): error")
print(traceback.format_exc())
return "Error", 500 # INTERNAL_SERVER_ERROR
def get_list(list, key=None):
if not _check_sharedkey():
return "Forbidden", 403
list_table = ('gm_timetable' if list == 'timetable' else (
'gm_timetable_day_type' if list == 'daytype' else
('gm_temp' if list == 'temp' else
('gm_timetable_type_data' if list == 'typedata' else 'gm_control'))))
try:
dbmgr = db.DatabaseManager()
if list == 'temp':
if key:
_key = int(key)
cur = dbmgr.query(
"""
SELECT `{}`.*, `timetable_id` FROM `{}`
INNER JOIN `gm_timetable_temp` ON
`temp_id` = `gm_temp`.`id`
WHERE `gm_timetable_temp`.`timetable_id` = ?
ORDER BY `gm_temp`.`id`
""".format(list_table, list_table), (_key))
else:
cur = dbmgr.query("""
SELECT `{}`.*, `timetable_id` FROM `{}`
INNER JOIN `gm_timetable_temp` ON `temp_id` = `gm_temp`.`id`
ORDER BY `gm_temp`.`id`
""".format(list_table, list_table))
else:
if key:
_key = int(key)
if list == 'typedata':
cur = dbmgr.query(
"""
SELECT * FROM {}
WHERE `day_type_id` = ?
ORDER BY `id`
""".format(list_table), (_key, ))
else:
cur = dbmgr.query(
"""
SELECT * FROM {}
WHERE `id` = ?
ORDER BY `id`
""".format(list_table), (_key, ))
else:
cur = dbmgr.query(
"SELECT * FROM {} ORDER BY id".format(list_table))
r = [
dict((cur.description[i][0], value) for i, value in enumerate(row))
for row in cur.fetchall()
]
# one = False
# return json.dumps((r[0] if r else None) if one else r)
return json.dumps(r)
except Exception:
print("get_list({},{}): error".format(list, key))
print(traceback.format_exc())
return "Error", 500 # INTERNAL_SERVER_ERROR
def _get_remote_address(server):
dbmgr = db.DatabaseManager()
try:
status = json.loads(
dbmgr.query("SELECT data FROM gm_output").fetchone()[0])
return status["hompi_slaves"][server]["address"]
except Exception:
print("_get_remote_address({}): error".format(server))
print(traceback.format_exc())
return None
def get_status():
if not _check_sharedkey():
return "Forbidden", 403
dbmgr = db.DatabaseManager()
try:
return dbmgr.query("SELECT data FROM gm_output").fetchone()[0]
except Exception:
print("get_status(): error")
print(traceback.format_exc())
return "Error", 500 # INTERNAL_SERVER_ERROR
def get_server_list():
if not _check_sharedkey():
return "Forbidden", 403
dbmgr = db.DatabaseManager()
try:
status = json.loads(
dbmgr.query("SELECT data FROM gm_output").fetchone()[0])
return json.dumps(status["hompi_slaves"].keys())
except Exception:
print("get_server_list(): error")
print(traceback.format_exc())
return "Error", 500 # INTERNAL_SERVER_ERROR
def send_command(command_json):
if not _check_sharedkey():
return "Forbidden", 403
try:
_command = json.loads(command_json)
dbmgr = db.DatabaseManager()
# dbmgr.insert('gm_input', { 'data' : _command['data'] })
dbmgr.query('INSERT INTO gm_input(data) VALUES(?)', [_command['data']])
_signal_server()
except Exception:
print("send_command({}): error".format(command_json))
print(traceback.format_exc())
return "Error", 400 # BAD_REQUEST
return "Ok"
def set_control(data_json):
if not _check_sharedkey():
return "Forbidden", 403
try:
_data = json.loads(data_json)
_id = int(_data['timetable_id'])
dbmgr = db.DatabaseManager()
dbmgr.query('UPDATE gm_control SET timetable_id = ?', (_id, ))
_signal_server()
except Exception:
print("set_control({}): error".format(data_json))
print(traceback.format_exc())
return "Error", 400 # BAD_REQUEST
return "Ok"
def update_output():
global current_status
dbmgr = db.DatabaseManager()
# update status
if current_status != io_status.get_output():
io_status.current_image = resources.current_image(config.IMAGE_PATH)
io_status.last_update = datetime.datetime.now().isoformat()
dbmgr.query(
"""UPDATE gm_output
SET data = '{}', last_update = strftime('%s','now')
WHERE id = 0""".format(
io_status.get_output().replace('\'', '\'\'')))
current_status = io_status.get_output()
print('OUTPUT: ' + current_status.replace('\n', ''))
update_lcd_content(change=False)
def set_temp(data_json):
if not _check_sharedkey():
return "Forbidden", 403
try:
_data = json.loads(data_json)
_temp_c = float(_data['temp_c'])
_id = int(_data['id'])
dbmgr = db.DatabaseManager()
dbmgr.query(
"""UPDATE gm_temp SET temp_c = ?
WHERE id = ?""", (_temp_c, _id))
_signal_server()
except Exception:
print("set_temp({}): error".format(data_json))
print(traceback.format_exc())
return "Error", 400 # BAD_REQUEST
return "Ok"
def log_data(event):
try:
description = ''
if not event:
event = 'null'
else:
if event == '.':
# log all temperatures
for id, data in io_status.hompi_slaves.items():
description += str(data['int_temp_c']) + ';'
event = "'{}'".format(event.replace('\'', '\'\''))
if not description:
if config.VERBOSE_LOG:
description = "'{}'".format(
io_status.get_output().replace('\'', '\'\''))
else:
description = 'null'
else:
# remove last ; and add quotes
description = "'{}'".format(description[:-1])
if not config.MODULE_DB_LOG:
return
dbmgr = db.DatabaseManager()
dbmgr.query("""
INSERT INTO gm_log
(datetime, int_temp_c, ext_temp_c, req_temp_c, event, description)
VALUES (strftime('%s','now'), {:f}, {:f}, {:f}, {}, {})
""".format(io_status.int_temp_c, io_status.ext_temp_c,
io_status.req_temp_c, event, description))
print('Logged data: {}'.format(event))
except (KeyboardInterrupt, SystemExit):
raise
except Exception:
log_stderr('log_data error: {}'.format(traceback.format_exc()))
time.sleep(1)
def process_input():
global sig_command, is_status_changed
# TT=[timetable_id] ### set current timetable
# TEMP=[temp_id],[temp_c] ### set temperature
# PROG_T=[timetable_id],[ [daytype_id],[daytype_id],[daytype_id],
# [daytype_id],[daytype_id],[daytype_id],[daytype_id] ]
dbmgr = db.DatabaseManager()
rows = dbmgr.query(
"""SELECT id, last_update, data FROM gm_input
ORDER BY last_update""").fetchall()
show_ack = False
for row in rows:
_command = row[2]
dbmgr = db.DatabaseManager()
dbmgr.query(
"DELETE FROM gm_input WHERE id = {:d}".format(row[0]))
parser = _command.split('=')
log_data('command: {}'.format(_command))
if len(parser) > 1:
if parser[0].upper() == 'TT':
try:
if (int(parser[1])):
dbmgr.query('UPDATE gm_control SET timetable_id = ?',
(parser[1]))
show_message('TT CHANGE', 'TT CHANGE: ' + parser[1])
sig_command = show_ack = True
except Exception:
log_data('PARSERROR: {}'.format(_command))
elif parser[0].upper() == 'TEMP':
try:
parser2 = parser[1].split(',')
if int(parser2[0]) and float(parser2[1]):
dbmgr.query(
'UPDATE gm_temp SET temp_c = ? WHERE id = ?',
(parser2[1], parser2[0]))
sig_command = show_ack = True
show_message('TP CHANGE', 'TP CHANGE: ' + parser2[1])
except Exception as e:
log_data('PARSERROR: {}'.format(_command))
elif parser[0].upper() == 'LCD':
if parser[1] == '0':
lcd.set_backlight(0,
datetime.datetime.now() +
datetime.timedelta(hours=4))
else:
show_message('LCD ON')
lcd.set_backlight(1)
show_ack = True
elif parser[0].upper() == 'MESSAGE':
io_status.send_message(parser[1])
show_message('', 'MESSAGE: ' + parser[1])
is_status_changed = True
show_ack = True
elif parser[0].upper() == 'AMBIENT' or \
parser[0].upper() == 'AMBIENT_COLOR':
try:
if config.MODULE_AMBIENT:
io_status.current_ambient_color = \
ambient.set_ambient_crossfade(
parser[1],
datetime.datetime.now() +
datetime.timedelta(hours=4))
show_message('COLOR', 'AMBIENT COLOR: #' + parser[1])
except Exception as e:
log_data('PARSERROR: {}\n{}'.format(
_command,
traceback.format_exc()))
elif parser[0].upper() == 'AMBIENT_XMAS':
try:
if config.MODULE_AMBIENT:
ambient.set_ambient_xmas_daisy(parser[1])
show_message('COLOR', 'AMBIENT XMAS')
except Exception as e:
log_data('PARSERROR: {}\n{}'.format(
_command,
traceback.format_exc()))
elif parser[0].upper() == 'GATE':
# execute gate only once per cycle, and not while another
# is running
if len(config.BUTTONS):
gate_button_index = -1
index = 0
for button in config.BUTTONS:
if 'GATE' == button[1].upper():
gate_button_index = index
break
index += 1
if gate_button_index > -1 and \
not io_status.sw_status[gate_button_index]:
io_status.send_switch_command(gate_button_index)
show_ack = True
show_message('GATE', 'GATE OPEN')
elif parser[0].upper() == 'BUTTON':
try:
button_no = int(parser[1])
# execute switch only once per cycle, and not while another
# is running
if button_no < len(config.BUTTONS)\
and not io_status.sw_status[button_no]:
io_status.send_switch_command(button_no)
show_ack = True
show_message('BUTTON' + button_no)
except Exception as e:
log_data('PARSERROR: {}\n{}'.format(
_command,
traceback.format_exc()))
show_message('', 'PARSERROR: {}'.format(_command))
else:
log_data('NOTIMPLEMENTED: {}'.format(_command))
else:
log_data('PARSERROR: {}'.format(_command))
show_message('PARSERROR: {}'.format(_command))
# show ambient ack
if config.MODULE_AMBIENT and show_ack:
ambient.ambient_ack()
is_status_changed |= sig_command
def init_output():
dbmgr = db.DatabaseManager()
dbmgr.query("""DELETE FROM `gm_output`""")
dbmgr.query("""INSERT INTO `gm_output` (`id`,`data`) VALUES (0,null)""")
def refresh_program(time_):
global current_status
dbmgr = db.DatabaseManager()
# refresh heating program
row = dbmgr.query(
"""SELECT
ttb.id, ttb.description, ttb.short_description,
monday, tuesday, wednesday, thursday, friday, saturday, sunday,
pre_holiday, holiday
FROM gm_control AS ctl
INNER JOIN gm_timetable AS ttb ON ttb.id = ctl.timetable_id""")\
.fetchone()
if holiday_list.get(datetime.datetime.today()):
# today is a holiday
day_type = row[11]
elif holiday_list.get(
datetime.datetime.today() + datetime.timedelta(days=1)):
# today is a pre-holiday
day_type = row[10]
else:
# normal day
day_type = row[datetime.datetime.today().weekday() + 3]
io_status.mode_id = row[0]
io_status.mode_desc = io_status.timetable_desc = row[1]
io_status.short_mode_desc = row[2].upper()[0:1]
print('Timetable: {} ({})'.format(io_status.mode_desc,
io_status.short_mode_desc))
sensor.hompi_slaves_forward_command(io_status.hompi_slaves,
'TT={}'.format(row[0]))
row = dbmgr.query(
"""SELECT
tdata.orderby, tdtype.description, time_hhmm, delta_calc_mm,
temp.description, temp_c
FROM gm_timetable_day_type AS tdtype
INNER JOIN gm_timetable_type_data AS tdata
ON tdata.day_type_id = tdtype.id
INNER JOIN gm_temp AS temp
ON temp.id = tdata.temp_id
WHERE tdtype.id = {:d}
AND time_hhmm <= {:d}
ORDER BY orderby DESC""".format(day_type, time_)
).fetchone()
orderby = row[0]
io_status.day_type_desc = row[1]
io_status.req_temp_c = row[5]
io_status.req_start_time = row[2]
io_status.req_temp_desc = row[4]
print('Day: {}({:02d}:{:02d}) - Temp({}): {:.2f}°'.format(
row[1], datetime.datetime.today().hour,
datetime.datetime.today().minute, row[4], row[5]))
# get next change
row = dbmgr.query(
"""SELECT
time_hhmm, delta_calc_mm
FROM gm_timetable_day_type AS tdtype
INNER JOIN gm_timetable_type_data AS tdata
ON tdata.day_type_id = tdtype.id
INNER JOIN gm_temp AS temp
ON temp.id = tdata.temp_id
WHERE tdtype.id = {:d}
AND tdata.orderby > {:d}
ORDER BY orderby""".format(day_type, orderby)
).fetchone()
if row:
io_status.req_end_time = row[0]
else:
io_status.req_end_time = 0000
print('Time range: ({:02.0f}:{:02.0f}) - ({:02.0f}:{:02.0f})'.format(
math.floor(io_status.req_start_time // 100),
io_status.req_start_time - math.floor(
io_status.req_start_time // 100) * 100,
math.floor(io_status.req_end_time // 100),
io_status.req_end_time - math.floor(
io_status.req_end_time // 100) * 100
))
import benchmarkCalc as bcCalc
import datetime as dt
import db
dbmgr = db.DatabaseManager("dev.db")
date_today = dt.datetime(2017, 6, 18, 14, 0, 2)
date_t = date_today.strftime('%Y-%m-%d %H:%M:%S.000')
date_t_2 = (date_today - dt.timedelta(days=2)).strftime('%Y-%m-%d %H:%M:%S')
date_t_30 = (date_today - dt.timedelta(days=30)).strftime('%Y-%m-%d %H:%M:%S')
#history_dates = sorted(history_dates,reverse=True)
x = bcCalc.main('isin/US0394831020', 41, date_t, date_t_2)
#'isin/US0394831020'
#dbmgr.print_table("sqlite_master", "*", constraint="type='table' and name = 'Alert_History'")
#dbmgr.print_table("sqlite_master", "name", constraint="type='table'")
# dbmgr.insert_table("Price_History","'isin/US0394831020',41.25,'2017-06-21 16:00:00.000'")
# dbmgr.insert_table("Alert_History","1,5.03,1,0,0,''")
# dbmgr.insert_table("Alert_History","2,5.03,5,1,0,''")
# dbmgr.insert_table("Alert_History","4,0.33,0.22,0,0,''")
# dbmgr.insert_table("Alert_History","4,0.44,0.22,0,0,''")
# dbmgr.insert_table("Alert_History","5,0.44,0.33,0,0,''")
#dbmgr.delete_table("Alert_History")
#dbmgr.execute("Create table Alert_History (users_subscription_id, alert_calc real, alert_benchmark real, alert_sent integer, alert_responded integer, alert_responded_when text, date_insert text)")
#dbmgr.execute("UPDATE Price_History set date_insert = '2017-06-22 13:34:23.142' where rowid = 13")
#
#dbmgr.print_df_table("Alert_History")
# dbmgr.print_df_table("Users_Subscription")
def main(security, value, date_t, date_alert_cutoff):
global dbmgr
try:
# Connect to database
dbmgr = db.DatabaseManager("dev.db")
# Query requested benchmarks for the subscribed security
subscription = dbmgr.select_df_table(
'Subscription',
column="*",
orderby=None,
limit=None,
constraint="security = '{}' and active = 1".format(security))
# Check if subscription table returned any results
if subscription.empty:
print("No activate subscriptions for '{}' security.".format(
security))
else:
# Obtain the highest look back value to query an appropriate amount of historical data
max_lookback = subscription['period'].max()
# Select historial data for security
security_history = dbmgr.select_df_price_table(security,
column="*",
limit=max_lookback,
date_to=date_t,
constraint=None)
# Check if history table returned any results
if security_history.empty:
print("No history prices for '{}' security.".format(security))
# Check if history table returned enough results
elif len(security_history['value']) < max_lookback:
print(
"Not enough historical prices for subscriptions to '{}' security. Required {} historical data points."
.format(security, max_lookback))
else:
# Select only relevant columns
security_history = security_history[['value', 'date_value']]
# Create a dataframe row with today's value
security_today = pd.DataFrame([[value, date_t]],
columns=['value', 'date_value'])
# Combine today's value with historical data
security_history = security_today.append(security_history)
# Reset index
security_history.reset_index(drop=True, inplace=True)
# Calculate Today's benchmark value per required calculation type, benchmark and look back period
subscription = subscription.apply(
lambda y: calc_concat(security_history['value'], y),
axis=1)
# Delete history table as it's no longer required
del security_history
# Drop subscriptions that did not trigger an alert
subscription = subscription[subscription['alert_flag'] == True]
# Drop columns which are not needed
subscription.drop(['alert_flag', 'active', 'date_insert'],
axis=1,
inplace=True)
# Create a list of subscription ids to query in a database
subscription_ids = list(subscription['id'])
sub_id_list = ",".join(str(i) for i in subscription_ids)
# Rename column
subscription.rename(columns={'id': 'subscription_id'},
inplace=True)
# Query Users that subscribe to the prices that triggered an alert
subscribed_users = dbmgr.select_df_table(
'Users_Subscription',
column="*",
orderby=None,
limit=None,
constraint="subscription_id in ({}) and status = 'ACTIVE'".
format(sub_id_list))
# Check if subscribed users table returned any results
if subscribed_users.empty:
print(
"No subscribed users to subscriptions for '{}' security."
.format(security))
else:
# Drop columns which are not needed
subscribed_users = subscribed_users.drop([
'status', 'status_approved_by_id',
'status_approved_date', 'date_insert'
],
axis=1)
# Join subscriptions and their users
active_subscriptions = subscribed_users.merge(
subscription,
left_on='subscription_id',
right_on='subscription_id',
how='inner')
# Create a list of subscribed users
user_sub_ids = list(subscribed_users['id'])
user_sub_id_list = ",".join(str(i) for i in user_sub_ids)
# Delete dataframes that are no longer needed
del subscription, subscribed_users
# Query alert history for relevant users and subscriptions
alert_history = dbmgr.select_df_table(
'Alert_History',
column="users_subscription_id,alert_sent,date_insert",
orderby='date_insert desc',
limit=None,
constraint=
"users_subscription_id in ({}) and date_insert < '{}' and date_insert > '{}'"
.format(user_sub_id_list, date_t, date_alert_cutoff))
# Check if alert history table returned any results
if alert_history.empty:
history_flag = 0
print(
"No alert history for subscriptions under {} security."
.format(security))
else:
history_flag = 1
# Drop columns which are not needed
alert_history.drop(['id'], axis=1, inplace=True)
# Keep only the latest alert for each subscription
alert_history = remove_old_alerts(alert_history)
# Join subscriptions and their history
active_subscriptions = active_subscriptions.merge(
alert_history,
left_on='id',
right_on='users_subscription_id',
how='left')
# Delete dataframes that are no longer needed
del alert_history
# Drop columns which are not needed
active_subscriptions.drop(['users_subscription_id'],
axis=1,
inplace=True)
# Rename column
active_subscriptions.rename(
columns={'id': 'users_subscription_id'}, inplace=True)
# Query Users that subscribe to the prices that triggered an alert
users = dbmgr.select_df_table('Users',
column="user_login",
orderby=None,
limit=None,
constraint="date_to = ''")
# Check if users table returned any results
if users.empty:
print("No stored users in the database.")
else:
# Join subscriptions and their history
active_subscriptions = active_subscriptions.merge(
users,
left_on='user_id',
right_on='id',
how='left')
# Delete dataframes that are no longer needed
del users
# Drop columns which are not needed
active_subscriptions.drop(
['user_id', 'subscription_id', 'id'],
axis=1,
inplace=True)
# Log alerts in history and send when appropriate
active_subscriptions.apply(
lambda y: log_alert(y, date_t, history_flag),
axis=1)
except Error as ex:
print("Error in event handler:", ex)
