def r_alarm_current_events_fast(conn=None):
conn_given = False
if conn:
conn_given = True
else:
conn = util.getConn()
extra_where = " (begin_datetime < %s AND end_datetime IS NULL) "
now = util.getDateFromParam("now")
global_events = util.getRowsFromTable("alarm_global_event",
extraWhere=extra_where,
extraArgs=(now, ),
conn=conn)
# TODO: Add user events
sql = "SELECT alarm_event.* from alarm_event, alarm WHERE alarm_id = alarm.id AND alarm.account_id = %s AND (alarm_event.begin_datetime < %s AND alarm_event.end_datetime IS NULL) "
cur = conn.cursor(cursor_factory=dbapi2extras.DictCursor)
cur.execute(sql, (request.user.id, now))
results = []
for row in cur:
result_row = {}
for key in row.keys():
result_row[key] = row[key]
results.append(result_row)
global_events.extend(results)
cur.close()
if not conn_given:
conn.close()
return util.responseJSON({'events': global_events})
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 alarm_global_events_get(global_id):
row = util.getRowFromTableById("alarm_global_event", global_id)
if row:
return util.responseJSON(row)
else:
abort(404, "Global Alarm Event not found.")
def alarm_past_events_fast():
after_dt = util.getDateFromParam(request.params.get("after_datetime"))
if not after_dt:
abort(400, 'No after_datetime argument.')
conn = util.getConn()
extra_where = " (begin_datetime > %s AND end_datetime IS NOT NULL) "
global_events = util.getRowsFromTable("alarm_global_event",
extraWhere=extra_where,
extraArgs=(after_dt, ),
conn=conn)
sql = "SELECT alarm_event.id FROM alarm_event, alarm WHERE alarm_id = alarm.id AND alarm.account_id = %s AND (alarm_event.begin_datetime < %s AND alarm_event.end_datetime IS NOT NULL) "
cur = conn.cursor(cursor_factory=dbapi2extras.DictCursor)
cur.execute(sql, (request.user.id, after_dt))
results = []
for row in cur:
result_rows = {}
for key in row.keys():
result_rows[key] = row[key]
results.append(result_rows)
global_events.extend(results)
cur.close()
conn.close()
return util.responseJSON({'events': global_events})
def data_graph_data():
"""
query format
[ [TYPE_STR, [OPTS]], ...]
Type:
TYPE_STR: "sensor" - Single sensor readings
OPTS: [bin_section_id, read_type_id]
TYPE_STR: "inlet" - Inlet data
OPTS: [read_type_id]
TYPE_STR: "outlet" - Outlet data
OPTS: [read_type_id]
TYPE_STR: "maxtemp"
OPTS: []
"""
bin_id = util.getRequiredIntParam('bin_id')
begin_datetime = util.getDateFromParam(
request.params.get('begin_datetime', None))
end_datetime = util.getDateFromParam(
request.params.get('end_datetime', None))
sample_period = util.getRequiredIntParam('sample_period')
query_param = json.loads(request.params.get('query'))
return util.responseJSON(
graph_data_struct(bin_id, query_param, sample_period, begin_datetime,
end_datetime))
def readings_last_time_delta():
sample_period = request.params.get("sample_period")
if not sample_period:
sample_period = 5
conn = util.getConn()
cur = conn.cursor()
cur.execute(
'SELECT extract(epoch from (now()-datetime))/60 from reading_subsample WHERE sample_period = %s ORDER by datetime desc limit 1',
(sample_period, ))
row = cur.fetchone()
if not row:
cur.close()
conn.close()
abort(404, "Last reading not found. No readings in the database?")
minutes = row[0]
latest = r_diag_sensor_data_latest(conn)
error_info = []
now = util.getDateFromParam('now')
for row in latest:
if (now - row['datetime']).total_seconds() > 300:
error_info.append({
"bin_name": row['bin_name'],
"bin_section_name": row['bin_section_name'],
"device_name": row['device_name'],
"sensor_type_name": row['sensor_type_name'],
"port": row['port'],
"address": row['address'],
"delta": (now - row['datetime']).total_seconds()
})
return util.responseJSON({'td': minutes, "info": error_info})
def sensor_last_error_code_count():
sql = "SELECT count(error_code) FROM sensor_data WHERE error_code IS NOT NULL and datetime > now() - interval '10 minutes'"
conn = util.getConn()
cur = conn.cursor()
cur.execute(sql)
row = cur.fetchone()
count = 0
error_info = []
if row:
count = row[0]
sql = """SELECT distinct ON (bin_name, bin_section_name, sensor_type_name, port, address) bin.name as bin_name,
bin_section.name as bin_section_name,
device.name as device_name,
sensor_type.name as sensor_type_name,
device.port,
device.address
FROM bin, bin_section, sensor, device, sensor_type, sensor_data
WHERE sensor_data.error_code IS NOT NULL and sensor_data.datetime > now() - interval '10 minutes' and
sensor.id = sensor_data.sensor_id and
device.id=sensor.device_id and
sensor_type.id=sensor.sensor_type_id and
bin.id=device.bin_id and bin_section.id=device.bin_section_id"""
cur.execute(sql)
for row in cur:
error_info.append({
"bin_name": row[0],
"bin_section_name": row[1],
"device_name": row[2],
"sensor_type_name": row[3],
"port": row[4],
"address": row[5]
})
return util.responseJSON({"error_code_count": count, "info": error_info})
def alarm_events_get(alarm_event_id):
row = util.getRowFromTableById("alarm_event", int(alarm_event_id))
if row:
return util.responseJSON(row)
else:
abort(404, "Alarm event not found")
def luts_mc_maxtemp_getsingle(id):
conn = util.getConn()
lut = util.getRowFromTableById("mc_maxtemp_lut", id, conn=conn)
values = util.getRowsFromTable("mc_maxtemp_lut_value",
extraWhere="mc_maxtemp_lut_id=%s",
extraArgs=(lut['id'], ),
orderStatement=" ORDER BY mc ",
conn=conn)
lut["values"] = values
return util.responseJSON(lut)
def luts_mc_maxtemp_fast():
conn = util.getConn()
luts = util.getRowsFromTable("mc_maxtemp_lut", conn=conn)
for lut in luts:
values = util.getRowsFromTable("mc_maxtemp_lut_value",
extraWhere="mc_maxtemp_lut_id=%s",
extraArgs=(lut['id'], ),
orderStatement=" ORDER BY mc ",
conn=conn)
lut["values"] = values
return util.responseJSON({'luts': luts})
def readings_last():
sample_period = request.params.get("sample_period")
if not sample_period:
sample_period = 5
# connect to DB
conn = util.getConn()
cur = conn.cursor()
# perform SQL query
cur.execute(
"SELECT id, datetime from reading_subsample WHERE sample_period = %s ORDER BY datetime desc limit 2",
(sample_period, ))
# check if query returned anything
rows = []
for row in cur:
rows.append(row)
if len(rows) == 0:
cur.close()
conn.close()
abort(404, "Last reading not found. No readings in the database?")
row = rows[0]
dt = row[1]
grow = util.getRowFromTableById('general_config', 1)
countdown = (dt + datetime.timedelta(seconds=grow['interval']) -
datetime.datetime.now(dt.tzinfo) +
datetime.timedelta(seconds=30)).total_seconds()
countdown2 = 9999999
if len(rows) == 2:
countdown2 = 1.5 * (rows[0][1] - rows[1][1]).total_seconds() - (
datetime.datetime.now(dt.tzinfo) - rows[0][1]).total_seconds()
if (rows[0][1] - rows[1][1]) < datetime.timedelta(seconds=3 *
grow['interval']):
countdown = countdown2
# if countdown2 < countdown:
# countdown = countdown2
# return the id URL
reading_id = row[0]
d = row[1]
id2 = None
if len(rows) == 2:
id2 = rows[1][0]
cur.close()
conn.close()
xlinks = ['/resources/data/readings/%d' % (reading_id, )]
if id2:
xlinks.append('/resources/data/readings/%d' % (id2, ))
return util.responseJSON({
'xlink': xlinks,
'datetime': d,
'countdown': countdown
})
def air_deductions_fast():
beginDate = util.getDateFromParam(request.params.get("begin_span1"))
endDate = util.getDateFromParam(request.params.get("begin_span2"))
if (not beginDate or not endDate):
rows = util.getRowsFromTable(
"air_deduct", orderStatement=" ORDER BY begin_datetime ")
else:
rows = util.getRowsFromTable(
"air_deduct",
extraWhere=" ( begin_datetime >= %s AND begin_datetime <= %s ) ",
extraArgs=(beginDate, endDate),
orderStatement=" ORDER BY begin_datetime ")
return util.responseJSON({'air_deductions': rows})
def fill_list_fast():
begin_span1 = util.getDateFromParam(request.params.get("begin_span1"))
begin_span2 = util.getDateFromParam(request.params.get("begin_span2"))
bin_id = request.params.get("bin_id", None)
extra_where = ""
extra_args = tuple()
if bin_id:
try:
bin_id = int(bin_id)
except:
abort(400, 'Invalid bin_id')
if extra_where:
extra_where += " AND "
extra_where += " bin_id = %s "
extra_args += (bin_id, )
if begin_span1 and begin_span2:
if extra_where:
extra_where += " AND "
extra_where += "((air_begin_datetime >= %s AND air_begin_datetime <= %s) OR (filled_datetime >= %s AND filled_datetime <= %s) ) "
extra_args += (begin_span1, begin_span2, begin_span1, begin_span2)
conn = util.getConn()
rows = util.getRowsFromTable(
'fill',
extraWhere=extra_where,
extraArgs=extra_args,
orderStatement=
" ORDER by coalesce(filled_datetime, air_begin_datetime) ",
conn=conn)
cur = conn.cursor()
for row in rows:
cur.execute(
"SELECT mc, datetime FROM fill_during_mc WHERE fill_id = %s ORDER BY datetime",
(row["id"], ))
during_mc = cur.fetchall()
row['during_mc'] = during_mc
sheller_windows = util.getRowsFromTable(
'fill_sheller_window',
extraWhere=" fill_id = %s ",
extraArgs=(row['id'], ),
orderStatement=" ORDER BY begin_datetime ",
conn=conn)
row['sheller_windows'] = sheller_windows
cur.close()
conn.close()
return util.responseJSON({'fills': rows})
def r_controls_last_readings(conn=None):
year = datetime.datetime.now().year
conn_given = False
if conn:
conn_given = True
else:
conn = util.getConn()
cur = conn.cursor()
cur2 = conn.cursor()
cur.execute(
"SELECT device.bin_id, device.bin_section_id, device.id as device_id, sensor.id as sensor_id, \
sensor_type.id as sensor_type_id, sensor_type.read_type_id from sensor, device, sensor_type WHERE device.id=sensor.device_id and \
device.year=%s and sensor_type.id = sensor.sensor_type_id and sensor_type.controllable=true",
(year, ))
ret = []
for row in cur:
bin_id = row[0]
bin_section_id = row[1]
device_id = row[2]
sensor_id = row[3]
sensor_type_id = row[4]
read_type_id = row[5]
cur2.execute(
"SELECT value, datetime FROM sensor_data_latest WHERE sensor_id = %s AND \
error_code IS NULL ORDER BY datetime desc LIMIT 1",
(sensor_id, ))
row2 = cur2.fetchone()
value = None
dt = None
if row2:
value = row2[0]
dt = row2[1]
ret.append({
'bin_id': bin_id,
'bin_section_id': bin_section_id,
"device_id": device_id,
"sensor_id": sensor_id,
"value": value,
"datetime": dt,
"sensor_type_id": sensor_type_id,
"read_type_id": read_type_id
})
if not conn_given:
conn.close()
return util.responseJSON({'sensor_data': ret})
def r_readings_data_latest(conn=None):
# get parameter values
bin_id = request.params.get("bin_id")
bin_section_id = request.params.get("bin_section_id")
read_type_id = request.params.get("read_type_id")
extra_where = ""
extra_args = ()
if bin_id:
try:
extra_where += " bin_id = %s"
extra_args += (bin_id, )
except ValueError:
abort(400, 'Invalid bin_id.')
if bin_section_id:
try:
if extra_where:
extra_where += " AND "
extra_where += " bin_section_id = %s "
extra_args += (bin_section_id, )
except ValueError:
abort(400, 'Invalid bin_section_id')
if read_type_id:
try:
if extra_where:
extra_where += " AND "
extra_where += " read_type_id = %s "
extra_args += (read_type_id, )
except ValueError:
abort(400, 'Invalid sensor_type_id')
# find read data
reading_data = util.getRowsFromTable('reading_data_latest',
columns='*',
extraWhere=extra_where,
extraArgs=extra_args,
conn=conn)
if not reading_data:
abort(404, 'reading data latest not found')
# return the newly created alarm's id url
return util.responseJSON({"results": reading_data})
def sensor_data_latest():
sensor_ids = request.params.get("sensor_ids")
try:
sensor_ids = sensor_ids.split(',')
sensor_ids = map(int, sensor_ids)
except:
abort(400, 'Invalid sensor_ids')
extra_where = ""
extra_args = []
for i in sensor_ids:
if extra_where:
extra_where += " OR "
extra_where += " sensor_id = %s "
extra_args.append(i)
rows = util.getRowsFromTable('sensor_data_latest',
extraWhere=extra_where,
extraArgs=extra_args)
return util.responseJSON({"results": rows})
def optimized_updates():
account_id = request.user.id
conn = util.getConn()
readings_jsonstr = readings.r_readings_data_latest(conn=conn)
alarms_fast = alarms.r_alarms_fast(account_id=account_id, conn=conn)
controls_last_readings = controls.r_controls_last_readings(conn=conn)
current_events_fast = events.r_alarm_current_events_fast(conn=conn)
general = util.getRowFromTableById('general_config', 1)
del general['id']
del general['mid_pass']
ret = {
"general": general,
"sensor_data": json.loads(controls_last_readings)["sensor_data"],
"alarms": json.loads(alarms_fast)["alarms"],
"readings": json.loads(readings_jsonstr)["results"],
"events": json.loads(current_events_fast)["events"]
}
conn.close()
return util.responseJSON(ret)
def r_alarms_fast(account_id, conn):
conn_given = False
if not account_id:
account_id = request.params.get("account_id")
if account_id:
try:
account_id = int(account_id)
except:
abort(400, "Bad account_id parameter")
if not request.user.is_power_user():
account_id = request.user.id
if conn:
conn_given = True
else:
conn = util.getConn()
cur = conn.cursor(cursor_factory=dbapi2extras.DictCursor) # DictCursor doesn't give real dictionaries
sql = "SELECT alarm.* FROM alarm, account WHERE account.id = alarm.account_id AND account.privilege_id <= %s "
args = [request.user.privilege_id]
if account_id:
sql += " AND alarm.account_id = %s"
args.append(account_id)
cur.execute(sql, tuple(args))
results = []
for row in cur:
result_row = {}
for key in row.keys():
result_row[key] = row[key]
results.append(result_row)
cur.close()
if not conn_given:
conn.close()
return util.responseJSON({'alarms': results})
def sheller_sensor_mc_get():
fill_id = int(request.params.get('fill_id'))
ret = get_fill_sheller_sensor_mc(fill_id)
ret['fill_id'] = fill_id
return util.responseJSON(fill_id)
def diag_sensor_data_latest():
return util.responseJSON({"results": r_diag_sensor_data_latest()})
def air_deductions_get(id):
row = util.getRowFromTableById("air_deductions", id)
if (row):
return util.responseJSON(row)
else:
abort(404, 'Air deduction not found.')
def fill_types_get():
# TODO: Cache
types = util.getRowsFromTable('fill_type')
return util.responseJSON({'fill_types': types})
def controls_get(control_id):
row = util.getRowFromTableById('control', int(control_id))
if row:
return util.responseJSON(row)
else:
abort(404, 'Control not found.')
def fill_get(fill_id):
row = get_fill_by_id(fill_id)
if row:
return util.responseJSON(row)
else:
abort(404, "Fill not found.")
