def generate(self, db: Database) -> List:
salespersons = db.get_table('MST_SALESPERSONS')
customer_salesperson_mapping = {
6: 6,
85: 1,
28: 3,
14: 8,
55: 4,
134: 13
} # x:y -> customer x is salesperson y
customers = []
for id in range(cfg.INIT_GEN_CUSTOMER_COUNT):
if id in customer_salesperson_mapping.keys():
salesperson_record = salespersons.get_record(
customer_salesperson_mapping[id])
address_id = salesperson_record['address_id']
name = salesperson_record['name']
else:
address = get_random_address()
address_id = db.get_table('MST_ADDRESSES').insert_record(
[address[0], address[1], address[2], address[3]])
name = get_random_name()
customers.append([name, address_id])
return customers
def __init__(self):
# from Database import Database
database = Database()
self._db = database.getDb()
self._cursor = database.getCursor()
self._id = None
self._email = None
self._password = None
def __init__(self):
database = Database()
self._db = database.getDb()
self._cursor = database.getCursor()
self._id = None
self._customer_id = None
self._type = None
self._balance = None
def __init__(self):
database = Database()
self._db = database.getDb()
self._cursor = database.getCursor()
self._id = None
self._account_id = None
self._time = None
self._type = None
self._amount = None
self._note = None
def new(self, id, customer_id, type, balance):
database = Database()
self._db = database.getDb()
self._cursor = database.getCursor()
self._id = id
self._customer_id = customer_id
self._type = type
self._balance = balance
return self
def extract_trc_sales(self, db: Database):
tbl_sales = db.get_table('TRC_SALES')
tbl_salespersons = db.get_table('MST_SALESPERSONS')
for index, sap_sale in self._sap_vbak.iterrows():
salesperson_id = tbl_salespersons.find_record_index('name', sap_sale['Angelegt von'])
if salesperson_id is None:
salesperson_id = tbl_salespersons.insert_record_with_id(sap_sale['Angelegt von'], [sap_sale['Angelegt von'], sap_sale['Angelegt von']])
tbl_sales.insert_record_with_id(sap_sale['Verkaufsbeleg'], ['', sap_sale['Auftraggeber'], salesperson_id, False, '', self.get_precise_datestamp(sap_sale['Bestelldatum'], sap_sale['Uhrzeit'])])
def serialize_timeseries(self, db: Database, max_slice_length,
attributes: List[str],
types: List[str]) -> (np.ndarray, str, set):
feature_count = len(attributes) + len(types)
tbl_meta_changes = db.get_table('MTA_CHANGES').get_data()
series = np.ndarray(shape=(max_slice_length, feature_count))
series.fill(np.nan)
label = 0 # no fraud
fraud_ids = set()
# generate nodes
row_index = 0
for _, row in tbl_meta_changes.iterrows():
dst_table_name, dst_column_name = row['table_column_ref'].split(
'.', 2)
# insert change record
for type_index, type in enumerate(types):
series[row_index,
type_index] = 1 if type == 'MTA_CHANGES' else 0
for attribute_index, attribute in enumerate(attributes):
series[row_index, len(types) +
attribute_index] = self.get_attribute_normalized(
row, attribute)
if 'is_fraud' in row and row['is_fraud'] == True:
label = 1 # fraud
fraud_ids.add(row['fraud_id'])
row_index += 1
# insert actual record
for type_index, type in enumerate(types):
series[row_index,
type_index] = 1 if type == dst_table_name else 0
dst_table = db.get_table(dst_table_name)
dst_record = dst_table.get_record(row['record_id'])
for attribute_index, attribute in enumerate(attributes):
series[row_index, len(types) +
attribute_index] = self.get_attribute_normalized(
dst_record, attribute)
if 'is_fraud' in dst_record and dst_record['is_fraud'] == True:
label = 1 # fraud
row_index += 1
return series, label, fraud_ids
def _schedule_fraud_purchase(self, db: Database, args: Dict) -> bool:
db.get_table('TRC_SALES').insert_record([
f'FPurchase of Customer {args["buyer_customer_id"]}',
args["buyer_customer_id"], args['fraudster_salesperson_id'], True,
args['fraud_id'], args['buy_time']
])
db.get_table('TRM_SALE_PRODUCTS').insert_record(
[
self._product_id,
len(db.get_table('TRC_SALES')) - 1, self._purchase_amount,
args['buy_time']
],
changed_by=args['fraudster_salesperson_id'])
return True
def exec(self, db: Database) -> bool:
table = db.get_table(self._table_name)
if table is not None:
table.insert_record(self._new_record, changed_by=self._changed_by)
return True
else:
return False
def exec(self, db: Database) -> bool:
table = db.get_table(self._table_name)
if table is not None:
table.remove_record(self._item_id,
is_fraud=self._is_fraud,
fraud_id=self._fraud_id,
changed_by=self._changed_by)
return True
else:
return False
def generate(self, db: Database) -> List:
salespersons = []
for id in range(cfg.INIT_GEN_SALESPERSON_COUNT):
address = get_random_address()
address_id = db.get_table('MST_ADDRESSES').insert_record(
[address[0], address[1], address[2], address[3]])
salespersons.append([get_random_name(), address_id])
return salespersons
def _schedule_fraud_price_changes(self, db: Database, args: Dict) -> bool:
initial_product_price = db.get_table('MST_PRODUCTS').get_record(
args['product_id'])['price']
current_price = initial_product_price
new_price = int(
initial_product_price * self._new_product_price_percent
) if self._new_product_price_percent is not None else self._new_product_price if self._new_product_price is not None else initial_product_price
for increment_time_offset in range(self._price_decrease_increments):
current_time = args[
'buy_time'] - self._price_decrease_to_buy_delay - self._price_decrease_increments + increment_time_offset
if increment_time_offset == self._price_decrease_increments - 1:
current_price = new_price
else:
current_price -= self._price_decrement_function(
current_time, self._price_decrease_increments,
initial_product_price, new_price)
args['simulation'].add_scheduled_task(
task.UpdateTask(exec_at=current_time,
table_name='MST_PRODUCTS',
item_id=self._product_id,
field_name='price',
new_value=current_price,
is_fraud=True,
fraud_id=args['fraud_id'],
changed_by=args['fraudster_salesperson_id']))
# increase price
for increment_time_offset in range(self._price_increase_increments):
current_time = args[
'buy_time'] + self._price_buy_to_increment_delay + increment_time_offset + 1
if increment_time_offset == self._price_increase_increments - 1:
current_price = initial_product_price
else:
current_price += self._price_increment_function(
current_time, self._price_increase_increments, new_price,
initial_product_price)
args['simulation'].add_scheduled_task(
task.UpdateTask(exec_at=current_time,
table_name='MST_PRODUCTS',
item_id=self._product_id,
field_name='price',
new_value=current_price,
is_fraud=True,
fraud_id=args['fraud_id'],
changed_by=args['fraudster_salesperson_id']))
return True
def exec(self, db: Database) -> bool:
table = db.get_table(self._table_name)
if table is not None:
table.update_record(num=self._item_id,
col_name=self._field_name,
new_value=self._new_value,
is_fraud=self._is_fraud,
fraud_id=self._fraud_id,
changed_by=self._changed_by)
return True
else:
return False
def insert_new_device(client, userdata, message):
device_name = mqtt_to_string(message)
if not device_name:
return
current_time = datetime.now().strftime("%H:%M:%S")
database_connection = Database()
database_connection.insert_into(Database.dispositivo_table, [(device_name, current_time)])
device_type = get_device_type(device_name)
device_id = database_connection.get_from("ID", Database.dispositivo_table, "Direccion", device_name)[0][0]
actuador_id = ""
# Puerta y Apagadores, solamente es push.
if device_type in (Devices.Other, Devices.Camara, Devices.Alarma):
print("1")
database_connection.insert_into(Database.actuador_table, [(0, device_id)])
actuador_id = database_connection.get_from("ID", Database.actuador_table, "Dispositivo_ID", device_id)[0][0]
subscribrer_id = str(uuid.uuid1())
if device_type == Devices.Sensor:
print("2")
database_connection.insert_into(Database.sensor_table, [(0.0, device_id)])
#device_sub = SubEntity(subscribrer_id, SERVER_IP, SERVER_PORT, SERVER_USER, SERVER_PASSWORD)
#device_sub.connect_and_subscribe_to_topic(device_name , listen_and_insert_values_from_sensor)
elif device_type == Devices.Camara:
print("3")
database_connection.insert_into(Database.camara_table, [("RutaArchivoCamara", actuador_id)])
#camera_sub = SubEntity(subscribrer_id, SERVER_IP, SERVER_PORT, SERVER_USER, SERVER_PASSWORD)
#camera_sub.connect_and_subscribe_to_topic(device_name, listen_and_insert_ip_from_camera)
elif device_type == Devices.Alarma:
print("4") #nosotros no nos subscribimos a nada
#nosotros publicamos a la alarma el mensaje que recibamos del cliente
database_connection.insert_into(Database.alarma_table, [("MensajeAlarma", actuador_id)])
general_sub = SubEntity(subscribrer_id, SERVER_IP, SERVER_PORT, SERVER_USER, SERVER_PASSWORD)
general_sub.connect_and_subscribe_to_topic(device_name, general_listen_and_insert)
pub = PubEntity("cliente4", SERVER_IP, SERVER_PORT, SERVER_USER, SERVER_PASSWORD)
pub.connect_and_publish_to_topic(NEW_DEVICES_TOPIC, "")
# All topics when new device
publish_all_devices()
from data.Database import Database
from data.DataTable import DataTable
from data.DataColumn import DataColumn
from data.ForeignKey import ForeignKey
# ===============================================================
# ======================== TABLE SETUP ==========================
# ===============================================================
db = Database('SDG_DATA')
# TABLE: PRODUCTS
table_products = DataTable('MST_PRODUCTS', True)
table_products.add_columns(
[DataColumn('id', True),
DataColumn('name'),
DataColumn('price')])
db.add_table(table_products)
# TABLE: SALES
table_sales = DataTable('TRC_SALES')
table_sales.add_columns([
DataColumn('id', True),
DataColumn('description'),
DataColumn('customer_id', is_hidden=True),
DataColumn('salesperson_id', is_hidden=True),
DataColumn('is_fraud'),
DataColumn('fraud_id'),
DataColumn('timestamp', is_timestamp=True)
])
db.add_table(table_sales)
def extract_trm_sale_products(self, db: Database):
tbl_sale_products = db.get_table('TRM_SALE_PRODUCTS')
for index, sap_purchase in self._sap_vbap.iterrows():
tbl_sale_products.insert_record([self.get_product_id_from_name(sap_purchase['Material']), sap_purchase['Verkaufsbeleg'], sap_purchase['Auftragsmenge'], self.get_precise_datestamp(sap_purchase['Angelegt am'], sap_purchase['Uhrzeit'])])
def extract_mst_addresses(self, db: Database):
tbl_addresses = db.get_table('MST_ADDRESSES')
for index, sap_address in self._sap_kna1.iterrows():
tbl_addresses.insert_record_with_id(sap_address['Debitor'], [sap_address['Straße'], 0, sap_address['Ort'], sap_address['Postleitzahl']])
def extract_mst_customers(self, db: Database):
tbl_customers = db.get_table('MST_CUSTOMERS')
for index, sap_customer in self._sap_kna1.iterrows():
tbl_customers.insert_record_with_id(sap_customer['Debitor'], [sap_customer['Name 1'], sap_customer['Debitor']])
def publish_all_devices():
database_connection = Database()
all_topics = database_connection.get_all_topics()
formatted_topics = ','.join([str(elem[0]) for elem in all_topics])
pub = PubEntity(CLIENT_SUBSCRIBER_NAME, SERVER_IP, SERVER_PORT, SERVER_USER, SERVER_PASSWORD)
pub.connect_and_publish_to_topic(CLIENT_TOPIC, formatted_topics)
def generate_graph(self, db: Database) -> Graph:
print(f'generating graph for {db.get_name()}')
graph = Graph(name=db.get_name())
# generate nodes
# time_start = time.perf_counter()
for table in db.get_tables():
# print(f'adding nodes for table {table.get_name()}')
table_data = table.get_data()
for index, row in table_data.iterrows():
properties = {
column.get_name(): row[column.get_name()]
for column in table.get_columns().values() if
not column.get_is_primary() and not column.get_is_hidden()
}
graph.add_node(
properties,
f'{table.get_name()}[{index}]',
node_color=self._fraud_node_color if 'is_fraud' in row
and row['is_fraud'] else self._default_node_color,
node_type=table.get_name())
# time_end = time.perf_counter()
# print(f"generate nodes took {time_end - time_start:0.4f} seconds")
# generate DELETE nodes from MTA_CHANGES
# time_start = time.perf_counter()
for _, src_record in db.get_table('MTA_CHANGES').get_data().iterrows():
if (src_record['change_type']) == 'delete':
dst_table_name, _ = src_record['table_column_ref'].split(
'.', 2)
graph.add_node(src_record['old_value'],
f'{dst_table_name}[{src_record["record_id"]}]',
self._fraud_node_color if src_record['is_fraud']
else self._default_node_color,
node_type='MTA_CHANGES')
# time_end = time.perf_counter()
# print(f"delete nodes took {time_end - time_start:0.4f} seconds")
# generate links
# time_start = time.perf_counter()
for table in db.get_tables():
# print(f'TABLE KEYS: {table.get_name()}')
foreign_keys = table.get_all_foreign_keys()
table_data = table.get_data()
for index, src_record in table_data.iterrows():
src_node = graph.get_node_by_key(
f'{table.get_name()}[{index}]')
for foreign_key in foreign_keys:
if foreign_key.get_src_table() != table.get_name():
continue
dst_table = db.get_table(foreign_key.get_dst_table())
dst_data_entry_index = dst_table.find_record_index(
foreign_key.get_dst_column(),
src_record[foreign_key.get_src_column()])
dst_node = graph.get_node_by_key(
f'{foreign_key.get_dst_table()}[{dst_data_entry_index}]'
)
if dst_node is not None:
if foreign_key.get_reverse_relation():
dst_node.add_neighbor(src_node,
foreign_key.get_color())
else:
src_node.add_neighbor(dst_node,
foreign_key.get_color())
# time_end = time.perf_counter()
# print(f"generate links took {time_end - time_start:0.4f} seconds")
# generate meta links
# time_start = time.perf_counter()
for index, src_record in db.get_table(
'MTA_CHANGES').get_data().iterrows():
src_node = graph.get_node_by_key(f'MTA_CHANGES[{index}]')
dst_table_name, dst_column_name = src_record[
'table_column_ref'].split('.', 2)
dst_node = graph.get_node_by_key(
f'{dst_table_name}[{src_record["record_id"]}]')
src_node.add_neighbor(dst_node,
self._edge_colors[src_record['change_type']])
# time_end = time.perf_counter()
# print(f"generate meta links took {time_end - time_start:0.4f} seconds")
return graph
def db(self, val):
f = Database(val)
if self._db is not None:
self._db.close()
self._db = f
def config(self, main):
self.main = main
for name in self.main.databases_names:
database = Database()
if (os.path.getsize(name) > 10000000):
small_databases_names = self.file_split(name)
self.main.small_databases_names.append(
small_databases_names[-1])
titles = database.database_get_titles(small_databases_names)
for small_name in small_databases_names:
new_database = Database()
new_database.database_txt(self.main, small_name, titles)
os.remove(small_name)
self.databases.append(new_database)
elif (name.split(".")[-1] == "txt"):
database.database_txt(self.main, name)
elif (name.split(".")[-1] == "xls"):
database.database_xls(self.main, name)
elif (name.split(".")[-1] == "wrds"):
database.database_wrds(self.main, name)
else:
print("functionality not available yet")
if (database.title != None):
self.databases.append(database)
def general_listen_and_insert(client, userdata, message):
received_value = mqtt_to_string(message)
subscribed_topic = message.topic
device_type = get_device_type(subscribed_topic)
database_connection = Database()
device_id = database_connection.get_from("ID", Database.dispositivo_table, "Direccion", subscribed_topic)[0][0]
if device_type == Devices.Sensor:
print("U Sensor")
database_connection.update_sensor_table_using(device_id, received_value)
elif device_type == Devices.Camara:
print("U Camara")
actuador_id = database_connection.get_from("ID", Database.actuador_table, "Dispositivo_ID", device_id)[0][0]
database_connection.update_row(Database.camara_table, Database.camara_foreign_key ,actuador_id,[received_value,None])
elif device_type == Devices.Alarma:
print("U Alarma")
actuador_id = database_connection.get_from("ID", Database.actuador_table, "Dispositivo_ID", device_id)[0][0]
database_connection.update_alarma_table_using(actuador_id,received_value)
elif device_type == Devices.Other:
print("U Other")
database_connection.update_actuadores_using(device_id, received_value)
def delete_device(client, userdata, message):
received_value = mqtt_to_string(message)
if not received_value:
return
device_type = get_device_type(received_value)
database_connection = Database()
device_id = database_connection.get_from("ID", Database.dispositivo_table, "Direccion", received_value)[0][0]
print(device_id)
if device_type == Devices.Sensor:
print("D Sensor")
sensor_id = database_connection.get_id_from_sensores_with_device_id(device_id)
print(sensor_id)
database_connection.delete_from_single(Database.sensor_table,sensor_id)
database_connection.delete_from_single(Database.dispositivo_table,device_id)
elif device_type == Devices.Camara:
print("D Camara")
actuador_id = database_connection.get_from("ID", Database.actuador_table, "Dispositivo_ID", device_id)[0][0]
camara_id = database_connection.get_id_from_camara_with_actuador_id(actuador_id)
database_connection.delete_from_single(Database.camara_table,camara_id)
database_connection.delete_from_single(Database.actuador_table,actuador_id)
database_connection.delete_from_single(Database.dispositivo_table,device_id)
elif device_type == Devices.Alarma:
print("D Alarma")
actuador_id = database_connection.get_from("ID", Database.actuador_table, "Dispositivo_ID", device_id)[0][0]
alarma_id = database_connection.get_id_from_alarma_with_actuador_id(actuador_id)
database_connection.delete_from_single(Database.alarma_table,alarma_id)
database_connection.delete_from_single(Database.actuador_table,actuador_id)
database_connection.delete_from_single(Database.dispositivo_table,device_id)
elif device_type == Devices.Other:
print("D Other")
actuador_id = database_connection.get_from("ID", Database.actuador_table, "Dispositivo_ID", device_id)[0][0]
database_connection.delete_from_single(Database.actuador_table,actuador_id)
database_connection.delete_from_single(Database.dispositivo_table,device_id)
publish_all_devices()
pub = PubEntity("cliente6", SERVER_IP, SERVER_PORT, SERVER_USER, SERVER_PASSWORD)
pub.connect_and_publish_to_topic(DELETE_DEVICES_TOPIC, "")
"""