def controls_fetched(control_id):
# TODO: need to do any TZ checking with the fetched_datetime?
# check if sensor is in DB
if not util.getRowFromTableById("control", control_id):
abort(404, "Not found.")
# TODO: check to see if command already marked as fetched
# parse parameters
fetched_p = bool(request.params.get("fetched", False)) # did it work?
if not fetched_p:
util.updateRowById(
"control", control_id, {
"fetched_note":
"MID reported update failure at " +
util.datetimeToReadable(datetime.now()) + "."
})
return HTTPResponse(
output="Remote control value update failure recorded.", status=204)
# update row to reflect new value was applied to device
util.updateRowById(
"control", control_id, {
"fetched_note": "MID reports update success.",
"fetched_datetime": datetime.datetime.now()
})
return HTTPResponse(output="Remote control value update success recorded.",
status=204)
def accounts_recover_update():
recovery_hash = request.params.get('code', None)
new_password = request.params.get('new_password', None)
if (not recovery_hash or not new_password):
abort(400, 'Missing parameters.')
rows = util.getRowsFromTable(
table="account",
columns="id",
extraWhere=
"recovery_hash=%s AND recovery_datetime > now() - interval '24h'",
extraArgs=(recovery_hash, ),
checkEnabled=True)
if not rows:
abort(400, 'Invalid code')
user = rows[0]
seed = generatePassword() + generatePassword()
passwordHash = hashlib.sha1(seed + new_password).hexdigest()
util.updateRowById(
"account", user["id"], {
'seed': seed,
'password': passwordHash,
'recovery_hash': None,
'recovery_datetime': None
})
return HTTPResponse(output="Password updated", status=204)
def subscription_event_delete(subscriber_id):
conn = util.getConn()
cur = conn.cursor()
row = util.getRowsFromTable("subscription",
extraWhere="subscriber_id=%s",
extraArgs=(subscriber_id, ),
conn=conn)
if not row:
conn.close()
abort(404, 'Subscriber not found')
util.updateRowById("subscription",
row[0]["id"],
{"last_datetime": util.getDateFromParam("now")},
cursor=cur)
rows = util.getRowsFromTable('subscription_event',
extraWhere="subscriber_id=%s",
extraArgs=(subscriber_id, ),
conn=conn)
if rows:
cur.execute("DELETE FROM subscription_event where subscriber_id = %s",
(subscriber_id, ))
conn.commit()
conn.close()
return util.responseJSON({"events": rows})
def addRemoteControlsToConfig(mid_name):
# TODO: need to add anything about year column in device table?
retVal = []
# get latest control settings for each sensor in table
conn = util.getConn()
if mid_name:
sql = """SELECT c.sensor_id, d.id as device_id, d.mid_name, d.device_type_id, s.sensor_type_id, d.address, c.id as ctrl_id, c.value, c.fetched_datetime from control c, sensor s, device d WHERE c.fetched_datetime is NULL and c.sensor_id = s.id and s.device_id = d.id and d.year = %s and d.mid_name = %s"""
cur = conn.cursor(cursor_factory=dbapi2extras.DictCursor)
cur.execute(sql, (CURRENT_YEAR, mid_name))
else:
sql = """SELECT c.sensor_id, d.id as device_id, d.mid_name, d.device_type_id, s.sensor_type_id, d.address, c.id as ctrl_id, c.value, c.fetched_datetime from control c, sensor s, device d WHERE c.fetched_datetime is NULL and c.sensor_id = s.id and s.device_id = d.id and d.year = %s and d.mid_name IS NULL"""
cur = conn.cursor(cursor_factory=dbapi2extras.DictCursor)
cur.execute(sql, (CURRENT_YEAR,))
for ncv in cur:
# get sensor info
retVal += [{"sensor_id": ncv["sensor_id"],
"device_id": ncv["device_id"],
"mid_name": mid_name,
"device_type_id": ncv["device_type_id"],
"sensor_type_id": ncv["sensor_type_id"],
"address": ncv["address"],
"ctrl_id": ncv["ctrl_id"],
"value": ncv["value"]}]
# note that cmd has been sent
# TODO: throw error if this fails
util.updateRowById("control", ncv["ctrl_id"], {"fetched_datetime": datetime.datetime.now()})
conn.close()
return retVal
def accounts_update(id):
if request.user.id == int(id) or request.user.is_power_user():
row = util.getRowFromTableById('account', int(id), checkEnabled=True)
if (row):
if int(row['privilege_id']) > request.user.privilege_id:
unauthorized()
else:
parameters = {}
parameters['name'] = request.params.get('name', None)
parameters['email'] = request.params.get('email', None)
parameters['contact_news'] = request.params.get(
'contact_news', None)
configs = request.params.get('configs', None)
if configs:
try:
json.loads(configs)
except:
abort(400, 'Invalid configs')
parameters['configs'] = configs
password = request.params.get('password', None)
if (password):
parameters['seed'] = generatePassword() + generatePassword(
)
parameters['password'] = hashlib.sha1(
parameters['seed'] + password).hexdigest()
#TODO: Check phone format
privilege_id = request.params.get('privilege_id', None)
#TODO: Check for lengths, instead of relying on db?
if privilege_id:
try:
privilege_id = int(privilege_id)
except:
abort(400, 'Invalid privilege_id')
if privilege_id > request.user.privilege_id:
unauthorized()
parameters['privilege_id'] = privilege_id
newParameters = {}
for key, value in parameters.items():
if (value):
newParameters[key] = value
parameters = newParameters
parameters['phone'] = request.params.get('phone', None)
#This 400 will never happen because will always assume phone should be removed.
if (not parameters):
abort(400, 'No parameters given.')
#TODO: What about password?
util.updateRowById('account', id, parameters)
#TODO: Send email to account that got change informing them?
return HTTPResponse(output="Account updated.", status=204)
else:
abort(404, 'Account not found.')
else:
unauthorized()
def general_update():
parameters = {}
try:
parameters['configs'] = request.params.get('configs', None)
if not parameters['configs']:
abort(400, 'Bad arguments')
except:
abort(400, 'Bad arguments')
util.updateRowById('general_config', 1, parameters)
return HTTPResponse(output="General Config updated.",
status=204) # Local Variables:
def accounts_change_password():
old_password = request.params.get('old_password', None)
new_password = request.params.get('new_password', None)
if not old_password or not new_password:
abort(400, 'Missing parameters')
if not util.getAccount(request.user.email, old_password):
abort(400, 'Invalid old password')
seed = generatePassword() + generatePassword()
password_hash = hashlib.sha1(seed + new_password).hexdigest()
util.updateRowById("account", request.user.id, {
'seed': seed,
'password': password_hash
})
return HTTPResponse(output="Password updated", status=204)
def air_deductions_put(id):
air_deduct = util.getRowFromTableById("air_deduct", id)
if not air_deduct:
abort(404, 'Air deduct not found')
beginDate = util.getDateFromParam(request.params.get("begin_datetime"))
endDate = util.getDateFromParam(request.params.get("end_datetime"))
parameters = {}
if (beginDate):
parameters['begin_datetime'] = beginDate
if (endDate):
parameters['end_datetime'] = endDate
if (request.params.get("end_datetime") == 'empty'):
parameters['end_datetime'] = None
if (not parameters):
abort(400, 'Arguments missing.')
util.updateRowById("air_deduct", id, parameters)
return HTTPResponse(output="Air Deduction Updated", status=202)
def sensors_update(id):
row = util.getRowFromTableById("sensor", int(id))
if not row:
abort(404, "Sensor not found.")
parameters = {}
try:
parameters["device_id"] = int(request.params.get("device_id", None))
parameters["sensor_type_id"] = int(
request.params.get("sensor_type_id", None))
parameters["convert_py"] = request.params.get("convert_py", None)
parameters["extra_info"] = request.params.get("extra_info", None)
if string.strip(parameters["extra_info"]) == '':
parameters["extra_info"] = "null"
parameters["bias"] = float(request.params.get("bias", None))
parameters["enabled_p"] = request.params.get("enabled_p",
None).lower() == "true"
except:
abort(400, "Bad parameters")
util.updateRowById("sensor", int(id), parameters)
return HTTPResponse(output="Sensor updated.", status=204)
def subscription_event_update(subscriber_id):
subscribed = request.params.get("subscribed", None)
if not subscribed:
abort(400, 'subscribed parameter missing')
try:
json.loads(subscribed)
except:
abort(400, 'subscribed invalid json')
conn = util.getConn()
cur = conn.cursor()
row = util.getRowsFromTable("subscription",
extraWhere="subscriber_id=%s",
extraArgs=(subscriber_id, ),
conn=conn)
util.updateRowById('subscription', row[0]["id"], {
"last_datetime": util.getDateFromParam("now"),
"subscribed": subscribed
}, cur)
conn.commit()
conn.close()
return HTTPResponse(output="Subscription updated", status=204)
def accounts_recover():
email = request.params.get('email', None)
if (not email):
abort(400, 'Email parameter not given.')
user = util.getAccountByEmail(email)
#TODO: See if recovery has been tried lately and refuse new one if so?
if (not user):
abort(400, 'Email does not exist.')
recovery_hash = hashlib.sha1(generatePassword() + user.email).hexdigest()
while (len(
util.getRowsFromTable(table="account",
columns="id",
extraWhere="recovery_hash = %s",
extraArgs=(recovery_hash, ),
checkEnabled=True)) > 0):
recovery_hash = hashlib.sha1(generatePassword() +
user.email).hexdigest()
util.updateRowById("account", user.id, {'recovery_hash': recovery_hash})
message = """%s:
A request has been sent to reset the password to your Isadore account. If you did not intend to reset your password you may ignore this message. To continue the reset process follow the instructions below:
Do one of the following:
1) Goto the following:
https://%s/isadore/s/login.html?c=%s#fs2
OR
2) Type in the reset code in the form at:
https://%s/isadore/s/login.html#fs2
Using the code:
%s
After 24 hours the reset code will expire and will have to send a new reset request if you wish to reset your password.\n\n""" % \
(user.name, request.urlparts[1], recovery_hash, request.urlparts[1], recovery_hash)
# logging.debug(message)
util.sendEmail(user.email, '*****@*****.**',
'Isadore Password Recovery', message)
return HTTPResponse(output="Recovery Email Sent", status=204)
def luts_mc_maxtemp_put(id):
name, hours_per_mc, mcs, maxtemps = luts_mc_maxtemp_args()
row = util.getRowFromTableById('mc_maxtemp_lut', id)
if not row:
abort(404, 'LUT not found.')
conn = util.getConn()
cur = conn.cursor()
util.updateRowById("mc_maxtemp_lut", id, {
'name': name,
'hours_per_mc': hours_per_mc
}, cur)
cur.execute('DELETE FROM mc_maxtemp_lut_value WHERE mc_maxtemp_lut_id=%s',
(id, ))
for idx in xrange(0, len(mcs)):
util.insertRow("mc_maxtemp_lut_value", {
'mc': mcs[idx],
'maxtemp': maxtemps[idx],
'mc_maxtemp_lut_id': id
}, cur)
conn.commit()
cur.close()
conn.close()
return HTTPResponse(output='LUT Updated', status=204)
def devices_update(id):
row = util.getRowFromTableById("device", int(id))
if not row:
abort(404, "Device not found.")
parameters = {}
try:
parameters["device_type_id"] = int(
request.params.get("device_type_id", None))
parameters["name"] = request.params.get("name", None)
parameters["info"] = request.params.get("info", None)
parameters["address"] = int(request.params.get("address", None))
parameters["port"] = int(request.params.get("port", None))
parameters["enabled_p"] = request.params.get("enabled_p",
None).lower() == 'true'
parameters["bin_id"] = int(request.params.get("bin_id", None))
parameters["bin_section_id"] = int(
request.params.get("bin_section_id", None))
parameters["year"] = int(request.params.get("year", None))
parameters["mid_name"] = request.params.get("mid_name", None)
except:
abort(400, 'Bad parameters')
util.updateRowById("device", int(id), parameters)
return HTTPResponse(output="Device updated.", status=204)
def closeGlobalEvent(self, globalEventId):
util.updateRowById("alarm_global_event", globalEventId,
{"end_datetime": self.now})
self.conn.commit()
def alarms_update(alarm_id):
# get alarm info
alarm = util.getRowFromTableById('alarm', int(alarm_id))
# return error if row not found
if not alarm:
abort(404, "Alarm not found.")
if request.user.id == int(alarm["account_id"]) or request.user.is_power_user():
# get parameter values
alarm_type_id = request.params.get("alarm_type_id")
account_id = request.params.get("account_id")
greater_than_p = request.params.get("greater_than_p", '').lower() == 'true'
alarm_contact_type_ids = request.params.get('alarm_contact_type_ids', None)
value = request.params.get("value")
# check parameter values
if not util.getRowFromTableById('alarm_type', alarm_type_id):
abort(400, 'Invalid alarm_type_id')
if not util.getRowFromTableById("account", account_id, checkEnabled=True):
abort(400, "Invalid account_id")
alarm_type = util.getRowFromTableById('alarm_type', alarm_type_id)
if not alarm_type:
abort(400, 'Invalid alarm type.')
# can only create alarms for self or be super-user
if not request.user.is_power_user() and not request.user.id == int(account_id):
# print("User " + str(request.user.id) + " cannot change alarm to " + account_id)
unauthorized()
conctact_type_ids = []
contact_type_ids = []
if alarm_contact_type_ids:
try:
contact_type_ids = [int(c) for c in alarm_contact_type_ids.split(',')]
except:
abort(400, 'invalid alarm_contact_type_ids parameter.')
column_data = {"alarm_type_id": alarm_type_id,
"account_id": account_id}
if alarm_type['threshold_p']:
column_data["greater_than_p"] = greater_than_p
try:
column_data["value"] = float(value)
except:
abort(400, 'Invalid value.')
else:
column_data['greater_than_p'] = None
column_data['value'] = None
# TODO: alarm and alarm_contact should be in single transaction.
util.updateRowById('alarm', alarm['id'], column_data)
conn = util.getConn()
cur = conn.cursor()
cur.execute('DELETE from alarm_contact WHERE alarm_id = %s', (alarm['id'],))
conn.commit()
cur.close()
conn.close()
for alarm_contact_type_id in contact_type_ids:
util.insertRow('alarm_contact', {'alarm_id': alarm['id'], 'alarm_contact_type_id': alarm_contact_type_id})
else:
unauthorized()
return HTTPResponse(output="Alarm updated.", status=202)
def run(self):
logging.basicConfig(filename='./alarm_watcher.log',
level=logging.DEBUG,
format='%(asctime)s %(levelname)s: %(message)s')
# Set last_read = last reading time
# set last_contact_time = long time ago
last_read = datetime.datetime(year=1970,
month=1,
day=1,
tzinfo=LocalTimezone())
contact_last_read = None
while True:
try:
# Get current datetime
now = util.getDateFromParam("now")
# Get general config interval
conn = util.getConn()
general_config = util.getRowFromTableById("general_config",
1,
conn=conn)
gc_configs = {}
if "configs" in general_config and general_config["configs"]:
gc_configs = json.loads(general_config["configs"])
# Set working_last_time to last reading
cur = conn.cursor()
# Check for MID down
cur.execute(
"""SELECT id, datetime from reading_subsample WHERE sample_period = 5
ORDER BY datetime desc limit 2""")
row = cur.fetchone()
readDeltaAlt = now - row[1]
row = cur.fetchone()
working_last_read = row[1]
working_last_read_id = row[0]
readDelta = now - working_last_read
midGlobalEvent = self.hasGlobalAlert(self.ALARM_TYPE_MID_DOWN,
cur)
logging.debug("readDelta: " + str(readDelta))
if readDeltaAlt > datetime.timedelta(seconds=(20 * 60.0)):
if not midGlobalEvent:
util.insertRow(
"alarm_global_event", {
"alarm_type_id": self.ALARM_TYPE_MID_DOWN,
"begin_datetime": now
}, cur)
conn.commit()
self.sendGlobalNotices(
general_config["customer_short_name"],
self.ALARM_TYPE_MID_DOWN, now, conn)
elif midGlobalEvent:
# MID is back up.
util.updateRowById("alarm_global_event", midGlobalEvent,
{"end_datetime": now})
conn.commit()
# TODO: Contact that it is back up?
if working_last_read > last_read:
# If any new errors
# post alarms row
# if last contact time is greater than contact_time_interval then
# for everyone who needs to be contacted
# contact them about error
# set last contact time for type
tempRows = util.getRowsFromTable(
"alarm",
extraWhere=" alarm_type_id = %s ",
extraArgs=(self.ALARM_TYPE_SENSOR_TEMP, ),
conn=conn)
for row in tempRows:
sql = "SELECT id FROM alarm_event WHERE alarm_id=%s AND end_datetime IS NULL"
cur.execute(sql, (row["id"], ))
erow = cur.fetchone()
ae_id = None
if erow:
ae_id = erow[0]
if row["greater_than_p"]:
gtlt = ">"
else:
gtlt = "<"
aironsql = ""
if "alarms" in gc_configs and "aironly" in gc_configs[
"alarms"] and gc_configs["alarms"]["aironly"]:
aironsql = " AND bin_fill_airon(b.id) IS TRUE "
if "alarms" in gc_configs and "bottom_only" in gc_configs[
"alarms"] and gc_configs["alarms"][
"bottom_only"]:
sql = """select rd.bin_id, rd.avg_value as hottest from
reading_data_subsample rd, bin b WHERE
rd.reading_subsample_id = %s AND
rd.read_type_id = 10 and
rd.bin_id = b.id and
b.name like 'Bin %%' and
rd.bin_section_id = 14 """ + aironsql + """ AND
rd.avg_value """ + gtlt + """ %s"""
cur.execute(sql,
(working_last_read_id, row["value"]))
rows = cur.fetchall()
countRow = [len(rows)]
else:
sql = """select rd.bin_id, greatest(rd.avg_value, rd2.avg_value) as hottest from
reading_data_subsample rd, reading_data_subsample rd2, bin b WHERE
rd.reading_subsample_id = %s AND
rd.read_type_id = 10 and
rd.bin_id = b.id and
b.name like 'Bin %%' and
(rd.bin_section_id = 13) """ + aironsql + """ AND
rd2.reading_subsample_id = %s and
rd2.read_type_id = %s and
rd2.bin_id = b.id and
rd2.bin_section_id = 14 and greatest(rd.avg_value, rd2.avg_value) """ + gtlt + """ %s"""
cur.execute(
sql,
(working_last_read_id, working_last_read_id,
self.READ_TYPE_TEMP, row["value"]))
rows = cur.fetchall()
countRow = [len(rows)]
if countRow[0] > 0 and not ae_id:
sql = "INSERT INTO alarm_event (alarm_id, begin_datetime) VALUES (%s, %s)"
cur.execute(sql, (row["id"], now))
conn.commit()
self.sendAlarmNotice(
general_config["customer_short_name"],
row["id"], self.ALARM_TYPE_SENSOR_TEMP, now,
conn, gtlt + " %.2f" % row["value"])
# TODO: contact
elif countRow[0] == 0 and ae_id:
sql = "UPDATE alarm_event SET end_datetime = %s WHERE id = %s"
cur.execute(sql, (now, ae_id))
conn.commit()
last_read = working_last_read
cur.close()
conn.close()
time.sleep(180)
except:
logging.error(traceback.format_exc())
time.sleep(180)
if post_mc_data == "empty":
update_dict['post_mc'] = None
if roll_datetime == "empty":
update_dict['roll_datetime'] = None
for key in update_dict.keys():
if update_dict[key] == 'empty':
update_dict[key] = None
conn = util.getConn()
cur = conn.cursor()
oldrow = util.getRowFromTableById("fill", fid, conn=conn)
if update_dict:
# create new DB entry
util.updateRowById("fill", fid, update_dict, cursor=cur)
if during_mc_data == "empty":
cur.execute('DELETE FROM fill_during_mc WHERE fill_id = %s', (fid, ))
elif during_mc_array:
cur.execute('DELETE FROM fill_during_mc WHERE fill_id = %s', (fid, ))
for duringMC in zip(during_mc_array, during_mc_dates_array):
cur.execute(
"INSERT INTO fill_during_mc (fill_id, mc, datetime) VALUES (%s, %s, %s)",
(fill_id, duringMC[0], duringMC[1]))
elif not update_dict:
abort(400, 'No parameters given.')
sheller_windows = request.params.get("sheller_windows", None)
if sheller_windows:
sheller_windows = json.loads(sheller_windows)
