def openNewGlobalEvent(self):
cursor = self.conn.cursor()
util.insertRow("alarm_global_event", {
"alarm_type_id": self.alarm_type_id,
"begin_datetime": self.now
}, cursor)
self.conn.commit()
cursor.close()
def add_event(event, conn=None, cur=None, from_subscription_id=None):
logging.debug('add_event'+str(event))
madeConn = False
if not conn:
madeConn = True
conn = util.getConn()
cur = conn.cursor()
subs = get_subscribers(event, conn)
for s in subs:
if s != from_subscription_id:
util.insertRow("subscription_event", {"subscriber_id": s, "event": json.dumps(event, default=util.customJSONHandler)}, cursor=cur)
if madeConn:
conn.commit()
conn.close()
def alarms_new():
# 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 create account for " + account_id)
unauthorized()
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.')
# create new alarm
# TODO: alarm and alarm_contact should be in single transaction.
alarm_id = util.insertRow("alarm", column_data)
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})
# return the newly created alarm's id url
return {'xlink': ['/resources/alarms/' + str(alarm_id)]}
def accounts_new():
name = request.params.get('name')
email = request.params.get('email')
#TODO: Check phone format
phone = request.params.get('phone')
try:
privilege_id = int(request.params.get('privilege_id'))
except ValueError:
abort(400, 'Invalid privilege_id')
if privilege_id > request.user.privilege_id:
unauthorized()
#TODO: Check for lengths, instead of relying on db?
password = generatePassword()
seed = generatePassword() + generatePassword()
passwordHash = hashlib.sha1(seed + password).hexdigest()
rowData = {
"name": name,
"email": email,
"password": passwordHash,
"seed": seed,
"privilege_id": privilege_id
}
if (phone):
rowData["phone"] = phone
id = util.insertRow("account", rowData)
#TODO: Send new account email
util.sendEmail(
email, '*****@*****.**', 'Welcome to Isadore',
"Welcome to the Isadore system. You can login by going to https://" +
request.urlparts[1] + "\n\n" +
"To login use the following\n\nEmail: " + email + "\nPassword: "******"\n\n")
return {'xlink': ['/resources/accounts/' + str(id)]}
def controls_set():
try:
sensor_id = int(request.params.get('sensor_id', None))
except:
abort(400, 'Invalid sensor_id parameter.')
try:
value = float(request.params.get('value', None))
except:
abort(400, 'Invalid value parameter.')
sensor = util.getRowFromTableById('sensor', sensor_id, checkEnabled=True)
if not sensor:
abort(400, 'Wrong sensor_id')
sensor_type = util.getRowFromTableById('sensor_type',
sensor['sensor_type_id'])
if not sensor_type['controllable']:
abort(400, 'Sensor is not controllable')
# TODO: Check against type
control_id = util.insertRow(
'control', {
'sensor_id': sensor_id,
'sensor_type_id': sensor_type['id'],
'value': value
})
return {'xlink': ['/resources/consols/' + str(control_id)]}
def sensors_new():
parameters = {}
#TODO: Should non-super users set conver_py? They might break it, but deleting sensor could be just as bad.
try:
parameters["device_id"] = int(request.params.get("device_id", None))
parameters["extra_info"] = request.params.get("extra_info", None)
if string.strip(parameters["extra_info"]) == '':
parameters["extra_info"] = "null"
parameters["sensor_type_id"] = int(
request.params.get("sensor_type_id", None))
parameters["convert_py"] = request.params.get("convert_py", None)
parameters["bias"] = float(request.params.get("bias", None))
parameters["enabled_p"] = request.params.get("enabled_p",
None).lower() == "true"
except:
abort(400, "Bad parameters")
#try:
# util.safeEval(parameters["convert_py"], 1.0)
#except:
# abort(400, "Bad parameter: Invalid convert py string.")
id = util.insertRow("sensor", parameters)
return {"xlink": ["/resources/conf/sensors/" + str(id)]}
def sensor_mirrors_new():
parameters = {}
try:
parameters["sensor_id"] = int(request.params.get("sensor_id", None))
parameters["bin_id"] = int(request.params.get("bin_id", None))
parameters["bin_section_id"] = int(
request.params.get("bin_section_id", None))
except:
abort(400, "Bad parameters")
id = util.insertRow("sensor_mirror", parameters)
return {"xlink": ["/resources/conf/sensor_mirrors/" + str(id)]}
def fill_new():
# TODO: Check that fillNumber is not already taken for the year.
# XXX: I have a feeling that this needs to be rewritten
# get parameter values
fill_number = request.params.get("fill_number", None)
start_datetime = util.getDateFromParam(
request.params.get("air_begin_datetime"))
fill_datetime = util.getDateFromParam(request.params.get("start_datetime"))
bin_id = request.params.get("bin_id", None)
from_subscription_id = request.params.get("from_subscription_id", None)
conn = util.getConn()
cur = conn.cursor()
# check if bin_id exists
if not util.getRowFromTableById("bin", bin_id, conn=conn):
conn.close()
abort(400, "Bin id does not exist.")
if (not fill_datetime and not start_datetime) or not fill_number:
conn.close()
abort(
400,
'(air_begin_datetime and start_datetime) and fill_number are required.'
)
# create new DB entry
fill_id = util.insertRow("fill", {
"fill_number": fill_number,
"air_begin_datetime": start_datetime,
"filled_datetime": fill_datetime,
"bin_id": bin_id
},
cursor=cur)
if fill_datetime:
year = fill_datetime.year
else:
year = start_datetime.year
subscription_handler.add_event(
{
"key": 'fill',
"year": year,
"ids": [fill_id],
"type": "add",
"when": util.getDateFromParam("now")
}, conn, cur, from_subscription_id)
conn.commit()
conn.close()
# return the newly created fill's id url
return {"xlink": ["/resources/data/fills/" + str(fill_id)]}
def devices_clone():
# TODO: Clone sensor mirrors even though not part of device anymore?
from_year = request.params.get('from_year')
to_year = request.params.get('to_year')
if not from_year or not to_year:
abort(400, 'Missing parameters')
try:
from_year = int(from_year)
to_year = int(to_year)
except ValueError:
abort(400, 'Bad parameters')
conn = util.getConn()
cursor = conn.cursor()
devicesl = util.getRowsFromTable("device",
extraWhere='year=%s',
extraArgs=(from_year, ),
conn=conn)
if len(devicesl) == 0:
abort(400, 'Bad parameters')
cursor.execute('DELETE FROM device WHERE year=%s', (to_year, ))
for device in devicesl:
nd = device.copy()
del (nd['id'])
nd['year'] = to_year
nd['id'] = util.insertRow('device', nd, cursor)
# Sensors
sensors = util.getRowsFromTable('sensor',
extraWhere='device_id=%s',
extraArgs=(device['id'], ),
conn=conn)
for sensor in sensors:
ns = sensor.copy()
del (ns['id'])
ns['device_id'] = nd['id']
ns['id'] = util.insertRow('sensor', ns, cursor)
conn.commit()
cursor.close()
conn.close()
return HTTPResponse(output='devices configuration cloned.', status=204)
def subscription_new():
subscriber_id = str(uuid.uuid4())
conn = util.getConn()
cur = conn.cursor()
sid = util.insertRow('subscription', {
"subscriber_id": subscriber_id,
"last_datetime": util.getDateFromParam('now')
},
cursor=cur)
#cur.execute("DELETE FROM subscription WHERE last_datetime < now() - interval '2 hours'")
conn.commit()
conn.close()
return {"subscriber_id": subscriber_id}
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 luts_mc_maxtemp_post():
name, hours_per_mc, mcs, maxtemps = luts_mc_maxtemp_args()
conn = util.getConn()
cur = conn.cursor()
newId = util.insertRow("mc_maxtemp_lut", {
'name': name,
'hours_per_mc': hours_per_mc
}, cur)
for idx in xrange(0, len(mcs)):
util.insertRow("mc_maxtemp_lut_value", {
'mc': mcs[idx],
'maxtemp': maxtemps[idx],
'mc_maxtemp_lut_id': newId
}, cur)
general = util.getRowFromTableById("general_config", 1, conn=conn)
if not general["default_mc_maxtemp_lut_id"]:
cur.execute('UPDATE general_config SET default_mc_maxtemp_lut_id=%s',
(newId, ))
conn.commit()
cur.close()
conn.close()
return {'xlink': ['/resources/luts/mc_maxtemp/' + str(newId)]}
def air_deductions_post():
# rows = util.getRowsFromTable("air_deduct", extraWhere=" end_datetime IS NULL ")
# if(rows):
# abort(400, 'There exists an already open air deduction close that one first.')
beginDate = util.getDateFromParam(request.params.get("begin_datetime"))
if (not beginDate):
abort(400, 'begin_datetime is required.')
parameters = {'begin_datetime': beginDate}
endDate = util.getDateFromParam(request.params.get("end_datetime"))
if (endDate):
if (beginDate > endDate):
abort(400, 'Begin date is after end date.')
parameters["end_datetime"] = endDate
id = util.insertRow("air_deduct", parameters)
return {'xlink': ['/resources/data/air_deductions/' + str(id)]}
def devices_new():
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["mid_name"] = request.params.get("mid_name", None)
parameters["year"] = int(request.params.get("year", None))
except:
abort(400, 'Bad parameters')
id = util.insertRow("device", parameters)
return {'xlink': ['/resources/conf/devices/' + str(id)]}
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)
def data_new():
global sensor_data_insert_lock
# Parameters
# data = [{'sensor_id': int, 'value': float, 'raw_data': float, 'datetime': str#iso8601, error_code: int}, ...]
try:
data = json.loads(request.params.get('data'))
except:
logging.exception('Bad parameters, json: %s' %
(json.dumps(request.params.get('data'))))
abort(400, 'Bad parameters.')
# TODO: Check on any concurrent database issues with trigger, then maybe can remove thread lock.
conn = None
cur = None
try:
sensor_data_insert_lock.acquire()
conn = util.getConn()
cur = conn.cursor()
idx = 0
for record in data:
db_args = {
'sensor_id': None,
'value': None,
'raw_data': None,
'datetime': None,
'error_code': None
}
if 'sensor_id' in record:
db_args['sensor_id'] = record['sensor_id']
if 'value' in record:
db_args['value'] = record['value']
if 'raw_data' in record:
db_args['raw_data'] = record['raw_data']
if 'datetime' in record:
db_args['datetime'] = util.getDateFromParam(record['datetime'])
if 'error_code' in record:
db_args['error_code'] = record['error_code']
# Verify, must have sensor_id, datetime, and at least a value, raw_data, or error_code
if not db_args['sensor_id']:
logging.error(
'Bad parameter: Entry in data array missing sensor_id: idx=%d, record=%s'
% (idx, json.dumps(record)))
abort(400, 'Entry in data array missing sensor_id')
if not db_args['datetime']:
logging.error(
'Bad parameter: Entry in data array missing datetime: idx=%d, record=%s'
% (idx, json.dumps(record)))
abort(400, 'Entry in data array missing datetime')
if 'value' not in db_args and 'raw_data' not in db_args and 'error_code' not in db_args:
logging.error(
'Bad parameter: Entry in data array missing something to store (value, raw_data, '
+ 'or error_code: idx=%d, record=%s' %
(idx, json.dumps(record)))
abort(
400,
'Entry in data array missing something to store (value, raw_data, or error_code'
)
util.insertRow('sensor_data', db_args, cur)
idx += 1
conn.commit()
cur.close()
conn.close()
cur = None
conn = None
# TODO: add a little more info in response
return HTTPResponse(output="Data added.", status=204)
finally:
sensor_data_insert_lock.release()
if cur:
cur.close()
if conn:
conn.rollback()
conn.close()