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 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 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
@staticmethod
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()
if __name__ == '__main__':
aCaption = Caption("macAdress", "name", False)
CaptionDAO.create(aCaption)
aCaption = CaptionDAO.getByMacAddress("macAdress")
print("Adresse Mac:{0} Nom:{1} active:{2}".format(aCaption.macAddress,
aCaption.name,
aCaption.active))
if aCaption.active:
print("actif")
else:
print("inactif")
aCaption.active = True
if aCaption.active:
print("actif")
else:
print("inactif")
CaptionDAO.update(aCaption)
# type Contact
for aContact in self.contacts:
if contact is not None and aContact.email == contact.email:
found = True
index = i
i = i + 1
if found:
del self.contacts[index]
if __name__ == '__main__':
from entity.data import Data
from entity.caption import Caption
from entity.contact import Contact
from datetime import datetime
data = Data(1,"temp","%")
caption = Caption("mac","aname")
th = Threshold(1, data, caption, 10, True, datetime.now(), 10, [])
for i in range(1, 5):
th.addContact(Contact("email{0}".format(i), "fi", "las"))
th.addContact(None)
th.removeContact(None)
for contact in th.contacts:
print(contact.email)
print("--------------------")
th.removeContact(Contact("email3", "fi", "las"))
for contact in th.contacts:
print(contact.email)
print("--------------------")
th.removeContact(Contact("email8", "fi", "las"))
for contact in th.contacts:
print(contact.email)