def getAll(**kwargs):
"""
option keywords can be 'macAddress' or 'dataId'
:return: Array of Collect
"""
result = []
con = serverConnect()
with con:
cur = con.cursor()
sql = """SELECT c.id id, c.id_data id_data , d.name dname, d.unit unit, c.mac_address mac, ca.name caname, \
c.date cdate, c.value cvalue FROM collect c \
JOIN data d ON d.id = c.id_data \
JOIN `caption` ca ON ca.mac_address = c.mac_address"""
whereArgs = []
whereValues = ()
if 'macAddress' in kwargs or 'dataId' in kwargs:
sql = sql + " WHERE "
if ('macAddress' in kwargs):
whereArgs.append("ca.mac_address = %s")
whereValues = whereValues + (kwargs['macAddress'],)
if ('dataId' in kwargs):
whereArgs.append("c.id_data = %s")
whereValues = whereValues + (kwargs['dataId'],)
sql = sql + " and ".join(whereArgs) + " ORDER BY c.date DESC limit 20"
cur.execute(sql,whereValues)
rows = cur.fetchall()
for row in rows:
data = Data(row["id_data"], row["dname"], row["unit"])
caption = Caption(row["mac"], row["caname"])
result.append(Collect(row["id"], data, caption, row["cdate"], row["cvalue"]))
cur.close()
con.close()
return result
def create(threshold):
"""
:param threshold: Threshold
:return: Threshold
error : 'pymysql.err.IntegrityError: (1062, "Duplicata du champ 'xxx' pour la clef 'PRIMARY'")
id, data, caption, value, higher, lastDate, frequency, contacts
"""
connection = serverConnect()
with connection.cursor() as cursor:
sql = """INSERT INTO `threshold` (`id_data`, `mac_address`, `value`, `higher`, `last_date`, `frequency`) \
VALUES (%s, %s, %s, %s, %s, %s)"""
cursor.execute(sql,
(threshold.data.id, threshold.caption.macAddress,
threshold.value, threshold.higher,
threshold.lastDate, threshold.frequency))
threshold.id = int(connection.insert_id())
connection.commit()
cursor.close()
# type Contact
for contact in threshold.contacts:
with connection.cursor() as cursor:
sql = "INSERT INTO `recipient` (`address`, `id_threshold`) VALUES (%s, %s)"
cursor.execute(sql, (contact.email, threshold.id))
connection.commit()
cursor.close()
connection.close()
return threshold
def delete(threshold):
"""
:param threshold: Threshold
"""
connection = serverConnect()
with connection.cursor() as cursor:
sql = "DELETE FROM `threshold` WHERE `id` = %s"
cursor.execute(sql, (int(threshold.id)))
connection.commit()
cursor.close()
connection.close()
def delete(data):
"""
:param data: Data
"""
connection = serverConnect()
with connection.cursor() as cursor:
sql = "DELETE FROM `data` WHERE `id` = %s"
cursor.execute(sql, (int(data.id)))
connection.commit()
cursor.close()
connection.close()
def delete(caption):
"""
:param caption: Caption
"""
connection = serverConnect()
with connection.cursor() as cursor:
sql = "DELETE FROM `caption` WHERE `mac_address` = %s"
cursor.execute(sql, (caption.macAddress))
connection.commit()
cursor.close()
connection.close()
def delete(contact):
"""
:param contact: Contact
"""
connection = serverConnect()
with connection.cursor() as cursor:
sql = "DELETE FROM `contact` WHERE `address` = %s"
cursor.execute(sql, (contact.email))
connection.commit()
cursor.close()
connection.close()
def delete(collect):
"""
:param collect: Collect
"""
connection = serverConnect()
with connection.cursor() as cursor:
sql = "DELETE FROM `collect` WHERE `id` = %s"
cursor.execute(sql, collect.id)
connection.commit()
cursor.close()
connection.close()
def update(data):
"""
:param data: Data
"""
connection = serverConnect()
with connection.cursor() as cursor:
sql = "UPDATE `data` SET `name` =%s, `unit` = %s WHERE `id` = %s"
cursor.execute(sql, (data.name, data.unit, int(data.id)))
connection.commit()
cursor.close()
connection.close()
def update(contact):
"""
:param contact: Contact
"""
connection = serverConnect()
with connection.cursor() as cursor:
sql = "UPDATE `contact` SET `firstname` =%s, `lastname` =%s WHERE `address` = %s"
cursor.execute(sql, (contact.firstname, contact.lastname, contact.email))
connection.commit()
cursor.close()
connection.close()
def update(collect):
"""
:param collect: Collect
"""
connection = serverConnect()
with connection.cursor() as cursor:
sql = "UPDATE `collect` SET `id_data` =%s, `mac_address` =%s, `date` =%s, `value` =%s WHERE `id` = %s"
cursor.execute(sql, (collect.data.id, collect.caption.macAddress, collect.date, collect.value, collect.id))
connection.commit()
cursor.close()
connection.close()
def create(contact):
"""
:param contact: Contact
error : 'pymysql.err.IntegrityError: (1062, "Duplicata du champ '*****@*****.**' pour la clef 'PRIMARY'")
"""
connection = serverConnect()
with connection.cursor() as cursor:
sql = "INSERT INTO `contact` (`address`, `firstname`, `lastname`) VALUES (%s, %s, %s)"
cursor.execute(sql, (contact.email, contact.firstname, contact.lastname))
connection.commit()
cursor.close()
connection.close()
def update(caption):
"""
:param caption: Caption
"""
connection = serverConnect()
with connection.cursor() as cursor:
sql = "UPDATE `caption` SET `name` =%s, `active` =%s WHERE `mac_address` = %s"
cursor.execute(sql,
(caption.name, caption.active, caption.macAddress))
connection.commit()
cursor.close()
connection.close()
def create(caption):
"""
:param caption: Caption
error : 'pymysql.err.IntegrityError: (1062, "Duplicata du champ 'mac1' pour la clef 'PRIMARY'")
"""
connection = serverConnect()
with connection.cursor() as cursor:
sql = "INSERT INTO `caption` (`mac_address`, `name`, `active`) VALUES (%s, %s, %s)"
cursor.execute(sql,
(caption.macAddress, caption.name, caption.active))
connection.commit()
cursor.close()
connection.close()
def create(collect):
"""
:param collect: Collect
error : 'pymysql.err.IntegrityError: (1062, "Duplicata du champ 'xxx' pour la clef 'PRIMARY'")
"""
connection = serverConnect()
with connection.cursor() as cursor:
sql = "INSERT INTO `collect` (`id_data`, `mac_address`, `date` , `value`) VALUES (%s, %s, %s, %s)"
cursor.execute(sql, (collect.data.id, collect.caption.macAddress, collect.date,collect.value))
collect.id = int(connection.insert_id())
connection.commit()
cursor.close()
connection.close()
def create(data):
"""
:param data: Data
:return: Data
error : 'pymysql.err.IntegrityError: (1062, "Duplicata du champ 'xxx' pour la clef 'PRIMARY'")
"""
connection = serverConnect()
with connection.cursor() as cursor:
sql = "INSERT INTO `data` (`name`, `unit`) VALUES (%s, %s)"
cursor.execute(sql, (data.name, data.unit))
data.id = int(connection.insert_id())
connection.commit()
cursor.close()
connection.close()
return data
def getAll():
"""
:return: Array of Data
"""
result = []
con = serverConnect()
with con:
cur = con.cursor()
cur.execute("SELECT `id`, `name`, `unit` FROM `data`")
rows = cur.fetchall()
for row in rows:
result.append(Data(row["id"], row["name"], row["unit"]))
cur.close()
con.close()
return result
def getAll():
"""
:return: Array of Contact
"""
result = []
con = serverConnect()
with con:
cur = con.cursor()
cur.execute("SELECT `address`, `firstname`, `lastname` FROM `contact`")
rows = cur.fetchall()
for row in rows:
result.append(Contact(row["address"], row["firstname"], row["lastname"]))
cur.close()
con.close()
return result
def getByEmail(email):
"""
:param email: String
:return: Contact
"""
con = serverConnect()
result = None
with con:
cur = con.cursor()
cur.execute("SELECT `address`, `firstname`, `lastname` FROM `contact` WHERE `address` = %s", email)
row = cur.fetchone()
if row is not None:
result = Contact(row["address"], row["firstname"], row["lastname"])
cur.close()
con.close()
return result
def getById(id):
"""
:param id: Integer
:return: Data
"""
con = serverConnect()
result = None
with con:
cur = con.cursor()
cur.execute(
"SELECT `id`, `name`, `unit` FROM `data` WHERE `id` = %s",
int(id))
row = cur.fetchone()
if row is not None:
result = Data(row["id"], row["name"], row["unit"])
cur.close()
con.close()
return result
def getByMacAddress(macAddress):
"""
:param macAddress: String
:return: Caption
"""
con = serverConnect()
result = None
with con:
cur = con.cursor()
cur.execute(
"SELECT `mac_address`, `name`, `active` FROM `caption` WHERE `mac_address` = %s",
macAddress)
row = cur.fetchone()
if row is not None:
result = Caption(row["mac_address"], row["name"],
row["active"])
cur.close()
con.close()
return result
def getById(id):
"""
:param id: Integer
:return: Collect
"""
con = serverConnect()
result = None
with con:
cur = con.cursor()
cur.execute("""SELECT c.id id, c.id_data id_data , d.name dname, d.unit unit, c.mac_address mac, ca.name caname, \
c.date cdate, c.value cvalue FROM collect c \
JOIN data d ON d.id = c.id_data \
JOIN `caption` ca ON ca.mac_address = c.mac_address WHERE c.id = %s""", int(id))
row = cur.fetchone()
if row is not None:
data = Data(row["id_data"], row["dname"], row["unit"])
caption = Caption(row["mac"], row["caname"])
result = Collect(row["id"], data, caption, row["cdate"], row["cvalue"])
cur.close()
con.close()
return result
def getAll(**kwargs):
"""
option keywords is active : type Boolean
:return: Array of Caption
"""
result = []
con = serverConnect()
with con:
cur = con.cursor()
sql = "SELECT `mac_address`, `name`, `active` FROM `caption`"
if 'active' in kwargs:
sql = sql + " where active = " + str(
1 if kwargs['active'] else 0)
sql = sql + " order by name"
cur.execute(sql)
rows = cur.fetchall()
for row in rows:
result.append(
Caption(row["mac_address"], row["name"], row["active"]))
cur.close()
con.close()
return result
def update(threshold):
"""
:param threshold: Threshold
"""
connection = serverConnect()
cursor = connection.cursor()
sql = """UPDATE `threshold` SET `id_data`=%s, `mac_address`=%s, `value`=%s, \
`higher`=%s, `last_date`=%s, `frequency`=%s \
WHERE `id` = %s"""
cursor.execute(sql,
(threshold.data.id, threshold.caption.macAddress,
threshold.value, threshold.higher, threshold.lastDate,
threshold.frequency, threshold.id))
connection.commit()
cursor.close()
cursor = connection.cursor()
sql = """SELECT address FROM recipient WHERE id_threshold = %s"""
cursor.execute(sql, threshold.id)
rows = cursor.fetchall()
addresses = []
for row in rows:
if not threshold.containsContact(row["address"]):
cursor2 = connection.cursor()
sql = "DELETE FROM recipient WHERE address = %s AND id_threshold = %s"
cursor2.execute(sql, (row["address"], threshold.id))
connection.commit()
cursor2.close()
else:
addresses.append(row["address"])
cursor.close()
newContacts = threshold.getExtraEmails(addresses)
for contact in newContacts:
cursor2 = connection.cursor()
sql = "INSERT INTO recipient (address,id_threshold) VALUES (%s, %s)"
cursor2.execute(sql, (contact.email, threshold.id))
connection.commit()
cursor2.close()
connection.close()
def getById(id):
"""
:param id: Integer
:return: Threshold
"""
con = serverConnect()
result = None
with con:
cur = con.cursor()
sql = """SELECT t.id id, d.id did, d.name dname, d.unit dunit, c.mac_address mac, c.name cname, \
t.value tvalue, t.higher higher, t.last_date last_date, t.frequency frequency \
FROM threshold t \
JOIN caption c ON c.mac_address = t.mac_address \
JOIN `data` d ON d.id = t.id_data
WHERE t.id = %s"""
cur.execute(sql, id)
row = cur.fetchone()
if row is not None:
cur2 = con.cursor()
sql2 = """SELECT c.address address, c.firstname firstname, c.lastname lastname \
FROM recipient r JOIN contact c on r.address = c.address \
WHERE r.id_threshold = %s"""
cur2.execute(sql2, (row["id"]))
rows2 = cur2.fetchall()
contacts = []
for row2 in rows2:
contacts.append(
Contact(row2["address"], row2["firstname"],
row2["lastname"]))
data = Data(row["did"], row["dname"], row["dunit"])
caption = Caption(row["mac"], row["cname"])
result = Threshold(row["id"], data, caption, row["tvalue"],
row["higher"], row["last_date"],
row["frequency"], contacts)
cur.close()
con.close()
return result
