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.")