def get_last_x_radio_message_data(noOfMessages):
db = DB(DatabaseHelper.database_name, DataMapping())
row_list = db.get_table_objects_by_SQL(
RadioMessageData,
"SELECT * FROM RadioMessageData ORDER BY id desc LIMIT " +
str(noOfMessages))
return row_list
def get_punches_to_send_to_meos():
db = DB(DatabaseHelper.database_name, DataMapping())
row_list = db.get_table_objects_by_SQL(
PunchData,
"SELECT * FROM PunchData " + "WHERE sentToMeos = 0 AND " +
"stationNumberNotFound = 0 ORDER BY id asc")
return row_list
def get_radio_message(id):
db = DB(DatabaseHelper.database_name, DataMapping())
row_list = db.get_table_objects_by_SQL(RadioMessageData, "SELECT * FROM RadioMessageData WHERE id = " + str(id) + " ORDER BY id")
if row_list[0].messageType == PUNCH or row_list[0].messageType == COMBINED_PUNCH:
dataRecords = db.get_table_objects_by_SQL(PunchData, "SELECT * FROM PunchData WHERE radioMessageId = " + str(id))
row_list[0].dataRecordArray = dataRecords
return row_list[0]
def get_control_number_by_node_number(node_number):
db = DB(DatabaseHelper.database_name, DataMapping())
rows = db.get_table_objects_by_SQL(NodeToControlNumberData, "SELECT * FROM NodeToControlNumberData " +
"WHERE NodeNumber = " + str(node_number))
if len(rows) >= 1:
return rows[0].ControlNumber
return None
def get_last_x_radio_message_data_not_acked(radioNumber, noOfMessages=1):
db = DB(DatabaseHelper.database_name, DataMapping())
row_list = db.get_table_objects_by_SQL(RadioMessageData, "SELECT * FROM RadioMessageData " +
"WHERE ackSent = 0 "
"and radioNumber = " + str(radioNumber) +
" ORDER BY id desc LIMIT " +
str(noOfMessages))
return row_list
def get_control_number_by_node_number(node_number):
db = DB(DatabaseHelper.database_name, DataMapping())
rows = db.get_table_objects_by_SQL(
NodeToControlNumberData, "SELECT * FROM NodeToControlNumberData " +
"WHERE NodeNumber = " + str(node_number))
if len(rows) >= 1:
return rows[0].ControlNumber
return None
def get_last_x_radio_message_data_not_acked(radioNumber, noOfMessages=1):
db = DB(DatabaseHelper.database_name, DataMapping())
row_list = db.get_table_objects_by_SQL(
RadioMessageData,
"SELECT * FROM RadioMessageData " + "WHERE ackSent = 0 "
"and radioNumber = " + str(radioNumber) +
" ORDER BY id desc LIMIT " + str(noOfMessages))
return row_list
def get_channel(channel_id):
if channel_id is None:
return None
db = DB(DatabaseHelper.database_name, DataMapping())
sql = "SELECT * FROM ChannelData WHERE Id = " + str(channel_id)
rows = db.get_table_objects_by_SQL(RadioSettingsData, sql)
if len(rows) >= 1:
return rows[0].FrequencyName + rows[0].SpeedName
return None
def get_channel(channel_id):
if channel_id is None:
return None
db = DB(DatabaseHelper.database_name, DataMapping())
sql = "SELECT * FROM ChannelData WHERE Id = " + str(channel_id)
rows = db.get_table_objects_by_SQL(RadioSettingsData, sql)
if len(rows) >= 1:
return rows[0].FrequencyName + rows[0].SpeedName
return None
def get_main_settings_data():
db = DB(DatabaseHelper.database_name, DataMapping())
row_list = db.get_table_objects_by_SQL(MainSettingsData, "SELECT * FROM MainSettingsData ORDER BY id desc LIMIT 1")
if len(row_list) == 0:
return None
main_settings = row_list[0]
node_to_control_number_list = db.get_table_objects_by_SQL(NodeToControlNumberData, "SELECT * FROM NodeToControlNumberData ORDER BY id asc")
main_settings.NodeToControlNumberMapping = node_to_control_number_list
return main_settings
def save_radio_message(radioMessageData):
db = DB(DatabaseHelper.database_name, DataMapping())
rm = db.save_table_object(radioMessageData)
print(radioMessageData.dataRecordArray)
if radioMessageData.dataRecordArray is not None:
for rec in radioMessageData.dataRecordArray:
rec.radioMessageId = rm.id
if rm.messageType == PUNCH:
rec.origFromNode = rm.fromNode
db.save_table_object(rec)
return DatabaseHelper.get_radio_message(rm.id)
def save_radio_message(radioMessageData):
db = DB(DatabaseHelper.database_name, DataMapping())
rm = db.save_table_object(radioMessageData)
print(radioMessageData.dataRecordArray)
if radioMessageData.dataRecordArray is not None:
for rec in radioMessageData.dataRecordArray:
rec.radioMessageId = rm.id
if rm.messageType == PUNCH:
rec.origFromNode = rm.fromNode
db.save_table_object(rec)
return DatabaseHelper.get_radio_message(rm.id)
def get_radio_message(id):
db = DB(DatabaseHelper.database_name, DataMapping())
row_list = db.get_table_objects_by_SQL(
RadioMessageData, "SELECT * FROM RadioMessageData WHERE id = " +
str(id) + " ORDER BY id")
if row_list[0].messageType == PUNCH or row_list[
0].messageType == COMBINED_PUNCH:
dataRecords = db.get_table_objects_by_SQL(
PunchData,
"SELECT * FROM PunchData WHERE radioMessageId = " + str(id))
row_list[0].dataRecordArray = dataRecords
return row_list[0]
def get_main_settings_data():
db = DB(DatabaseHelper.database_name, DataMapping())
row_list = db.get_table_objects_by_SQL(
MainSettingsData,
"SELECT * FROM MainSettingsData ORDER BY id desc LIMIT 1")
if len(row_list) == 0:
return None
main_settings = row_list[0]
node_to_control_number_list = db.get_table_objects_by_SQL(
NodeToControlNumberData,
"SELECT * FROM NodeToControlNumberData ORDER BY id asc")
main_settings.NodeToControlNumberMapping = node_to_control_number_list
return main_settings
def get_radio_settings_data(radioSettingsId=None):
db = DB(DatabaseHelper.database_name, DataMapping())
sql = ""
if radioSettingsId is None:
sql = "SELECT * FROM RadioSettingsData ORDER BY id"
elif radioSettingsId is int:
sql = "SELECT * FROM RadioSettingsData WHERE id = " + str(radioSettingsId)
row_list = db.get_table_objects_by_SQL(RadioSettingsData, sql)
for radioSetting in row_list:
nodes = db.get_table_objects_by_SQL(InboundRadioNodeData, "SELECT * FROM InboundRadioNodeData WHERE RadioSettingsId = " + str(radioSetting.id))
radioSetting.InboundRadioNodes = nodes
return row_list
def save_main_settings(main_settings_data):
db = DB(DatabaseHelper.database_name, DataMapping())
old_main_settings_data = DatabaseHelper.get_main_settings_data()
if old_main_settings_data is not None:
main_settings_data.id = old_main_settings_data.id
db.save_table_object(main_settings_data)
db.execute_SQL("DELETE FROM NodeToControlNumberData")
for nodeToControl in main_settings_data.NodeToControlNumberMapping:
db.save_table_object(nodeToControl)
return DatabaseHelper.get_main_settings_data()
def get_radio_settings_data(radioSettingsId=None):
db = DB(DatabaseHelper.database_name, DataMapping())
sql = ""
if radioSettingsId is None:
sql = "SELECT * FROM RadioSettingsData ORDER BY id"
elif radioSettingsId is int:
sql = "SELECT * FROM RadioSettingsData WHERE id = " + str(
radioSettingsId)
row_list = db.get_table_objects_by_SQL(RadioSettingsData, sql)
for radioSetting in row_list:
nodes = db.get_table_objects_by_SQL(
InboundRadioNodeData,
"SELECT * FROM InboundRadioNodeData WHERE RadioSettingsId = " +
str(radioSetting.id))
radioSetting.InboundRadioNodes = nodes
return row_list
def save_main_settings(main_settings_data):
db = DB(DatabaseHelper.database_name, DataMapping())
old_main_settings_data = DatabaseHelper.get_main_settings_data()
if old_main_settings_data is not None:
main_settings_data.id = old_main_settings_data.id
db.save_table_object(main_settings_data)
db.execute_SQL("DELETE FROM NodeToControlNumberData")
for nodeToControl in main_settings_data.NodeToControlNumberMapping:
db.save_table_object(nodeToControl)
return DatabaseHelper.get_main_settings_data()
def save_radio_settings(radio_settings_data):
db = DB(DatabaseHelper.database_name, DataMapping())
print("save radio settings")
saved_radio_settings_data = db.save_table_object(radio_settings_data)
print("save radio settings 2")
db.execute_SQL(
"DELETE FROM InboundRadioNodeData WHERE RadioSettingsId = " +
str(saved_radio_settings_data.id))
for node in radio_settings_data.InboundRadioNodes:
node.id = None
node.RadioSettingsId = saved_radio_settings_data.id
db.save_table_object(node)
return DatabaseHelper.get_radio_settings_data(
saved_radio_settings_data.id)
def save_radio_settings(radio_settings_data):
db = DB(DatabaseHelper.database_name, DataMapping())
print("save radio settings")
saved_radio_settings_data = db.save_table_object(radio_settings_data)
print("save radio settings 2")
db.execute_SQL("DELETE FROM InboundRadioNodeData WHERE RadioSettingsId = " + str(saved_radio_settings_data.id))
for node in radio_settings_data.InboundRadioNodes:
node.id = None
node.RadioSettingsId = saved_radio_settings_data.id
db.save_table_object(node)
return DatabaseHelper.get_radio_settings_data(saved_radio_settings_data.id)
def drop_all_tables():
db = DB(DatabaseHelper.database_name, DataMapping())
radioMessageData = RadioMessageData()
db.drop_table(radioMessageData)
punchData = PunchData()
db.drop_table(punchData)
nodeSettingsData = NodeSettingsData()
db.drop_table(nodeSettingsData)
inboundRadioNodeData = InboundRadioNodeData()
db.drop_table(inboundRadioNodeData)
mainSettingsData = MainSettingsData()
db.drop_table(mainSettingsData)
radioSettingsData = RadioSettingsData()
db.drop_table(radioSettingsData)
channelData = ChannelData()
db.drop_table(channelData)
def save_channel(channel):
db = DB(DatabaseHelper.database_name, DataMapping())
db.save_table_object(channel)
def get_channels():
db = DB(DatabaseHelper.database_name, DataMapping())
sql = "SELECT * FROM ChannelData ORDER BY id"
row_list = db.get_table_objects_by_SQL(RadioSettingsData, sql)
return row_list
def ensure_tables_created():
db = DB(DatabaseHelper.database_name, DataMapping())
radioMessageData = RadioMessageData()
db.ensure_table_created(radioMessageData)
punchData = PunchData()
db.ensure_table_created(punchData)
nodeSettingsData = NodeSettingsData()
db.ensure_table_created(nodeSettingsData)
inboundRadioNodeData = InboundRadioNodeData()
db.ensure_table_created(inboundRadioNodeData)
mainSettingsData = MainSettingsData()
db.ensure_table_created(mainSettingsData)
radioSettingsData = RadioSettingsData()
db.ensure_table_created(radioSettingsData)
channelData = ChannelData()
db.ensure_table_created(channelData)
nodeToControlNumberData = NodeToControlNumberData()
db.ensure_table_created(nodeToControlNumberData)
def drop_all_tables():
db = DB(DatabaseHelper.database_name, DataMapping())
radioMessageData = RadioMessageData()
db.drop_table(radioMessageData)
punchData = PunchData()
db.drop_table(punchData)
nodeSettingsData = NodeSettingsData()
db.drop_table(nodeSettingsData)
inboundRadioNodeData = InboundRadioNodeData()
db.drop_table(inboundRadioNodeData)
mainSettingsData = MainSettingsData()
db.drop_table(mainSettingsData)
radioSettingsData = RadioSettingsData()
db.drop_table(radioSettingsData)
channelData = ChannelData()
db.drop_table(channelData)
def set_no_station_number_found(punchDataId):
db = DB(DatabaseHelper.database_name, DataMapping())
db.execute_SQL(
"UPDATE PunchData SET stationNumberNotFound = 1 WHERE id=" +
str(punchDataId))
def remove_all_punches():
db = DB(DatabaseHelper.database_name, DataMapping())
db.execute_SQL("DELETE FROM PunchData")
db.execute_SQL("DELETE FROM RadioMessageData")
def drop_tables():
db = DB(DatabaseHelper.database_name, DataMapping())
def get_channels():
db = DB(DatabaseHelper.database_name, DataMapping())
sql = "SELECT * FROM ChannelData ORDER BY id"
row_list = db.get_table_objects_by_SQL(RadioSettingsData, sql)
return row_list
def save_channel(channel):
db = DB(DatabaseHelper.database_name, DataMapping())
db.save_table_object(channel)
def get_last_x_radio_message_data(noOfMessages):
db = DB(DatabaseHelper.database_name, DataMapping())
row_list = db.get_table_objects_by_SQL(RadioMessageData, "SELECT * FROM RadioMessageData ORDER BY id desc LIMIT " + str(noOfMessages))
return row_list
def remove_all_punches():
db = DB(DatabaseHelper.database_name, DataMapping())
db.execute_SQL("DELETE FROM PunchData")
db.execute_SQL("DELETE FROM RadioMessageData")
def ensure_tables_created():
db = DB(DatabaseHelper.database_name, DataMapping())
radioMessageData = RadioMessageData()
db.ensure_table_created(radioMessageData)
punchData = PunchData()
db.ensure_table_created(punchData)
nodeSettingsData = NodeSettingsData()
db.ensure_table_created(nodeSettingsData)
inboundRadioNodeData = InboundRadioNodeData()
db.ensure_table_created(inboundRadioNodeData)
mainSettingsData = MainSettingsData()
db.ensure_table_created(mainSettingsData)
radioSettingsData = RadioSettingsData()
db.ensure_table_created(radioSettingsData)
channelData = ChannelData()
db.ensure_table_created(channelData)
nodeToControlNumberData = NodeToControlNumberData()
db.ensure_table_created(nodeToControlNumberData)
def set_punch_sent_to_meos(punchDataId):
db = DB(DatabaseHelper.database_name, DataMapping())
db.execute_SQL("UPDATE PunchData SET sentToMeos = 1 WHERE id=" + str(punchDataId))
def get_punches_to_send_to_meos():
db = DB(DatabaseHelper.database_name, DataMapping())
row_list = db.get_table_objects_by_SQL(PunchData, "SELECT * FROM PunchData " +
"WHERE sentToMeos = 0 AND " +
"stationNumberNotFound = 0 ORDER BY id asc")
return row_list
def set_punch_sent_to_meos(punchDataId):
db = DB(DatabaseHelper.database_name, DataMapping())
db.execute_SQL("UPDATE PunchData SET sentToMeos = 1 WHERE id=" +
str(punchDataId))
def set_no_station_number_found(punchDataId):
db = DB(DatabaseHelper.database_name, DataMapping())
db.execute_SQL("UPDATE PunchData SET stationNumberNotFound = 1 WHERE id=" + str(punchDataId))