def update_status(status, new_type_of_status, username):
if new_type_of_status == 6:
end_date = datetime.now()
else:
end_date = '0000-00-00 00:00:00'
#end_date = None
query = "UPDATE status SET end_date ='{}' WHERE incidence_id='{}' and username='******' and status_id={}" \
.format(datetime.now(), status.incidence_id,
status.username, status.status_id)
logger.info('update......................')
logger.info(query)
insert_query(query)
#repasar el concepto de username en status PONER EL USERNAME
# query = query + " ;" + " INSERT INTO status VALUES ('{}','{}',{},'{}')" \
# .format(status.incidence_id, username, new_type_of_status, end_date)
logger.info('insert..................')
query = "INSERT INTO status VALUES ('{}','{}',{},'{}')"\
.format(status.incidence_id, username, new_type_of_status, end_date)
logger.info(query)
insert_query(query)
cnx = connect_db()
try:
cursor = cnx.cursor()
cursor.execute(query)
cnx.commit()
cursor.close()
except Exception as err:
logger.error(err)
def select_user(username: str):
result_set = ''
# query3 = "SELECT * FROM users WHERE user_username= '******'".format(user_name)
query = "SELECT t1.username_id, t1.name, " \
"t1.email, t1.password, t2.role_name " \
"FROM users AS t1 " \
"JOIN " \
"roles t2 " \
"ON t1.role_id = t2.role_id " \
"WHERE username_id='{}'".format(username)
# logger.info(query)
cnx = connect_db()
try:
cursor = cnx.cursor()
cursor.execute(query)
result_set = cursor.fetchmany(size=1)
cursor.close()
except Exception as err:
logger.error(err)
# logger.info('result_set: {}'.format(result_set))
return result_set
def insert_incidence(incidencia):
incidence_id = incidencia.incidence_id
title = incidencia.title
description = incidencia.description
username = incidencia.username
incidence_date = incidencia.incidence_date
category_id = incidencia.category_id
priority_id = incidencia.priority_id
technician_hours = incidencia.technician_hours
resolve = incidencia.resolve
query = "INSERT INTO incidences " \
"VALUES ('{}','{}','{}'," \
" '{}','{}','{}','{}','{}'," \
"'{}' )".format(incidence_id, title, description,
username, incidence_date, category_id,
priority_id, technician_hours, resolve)
logger.info(query)
cnx = connect_db()
try:
cursor = cnx.cursor()
cursor.execute(query)
cnx.commit()
cursor.close()
except Exception as err:
logger.error(err)
def select_open_incidences(usuario) -> tuple:
result_set = []
query = "SELECT t1.incidence_id, t1.title, t1.description, t1.username," \
" t1.incidence_date, t5.status_name, t3.priority_name," \
"t1.technician_hours, t1.resolve,t2.category_name,t1.priority " \
"FROM incidences AS t1 " \
"JOIN (categories AS t2, priorities AS t3, status AS t4, type_of_status AS t5)" \
"ON (t1.category=t2.category_id AND t1.priority=t3.priority_id " \
"AND t1.incidence_id=t4.incidence_id AND t4.status_id=t5.status_id) " \
"WHERE t1.username='******' AND " \
"t4.end_date='00-00-00 00:00:00' AND t4.status_id " \
"IN(1,2,4,5) " \
"order by t1.priority desc".format(usuario, usuario)
logger.info(query)
cnx = connect_db()
try:
cursor = cnx.cursor()
cursor.execute(query)
cursor.close()
for value in cursor:
result_set.append(value)
cursor.close()
except Exception as err:
logger.error(err)
logger.info(result_set)
return result_set
def select_closed_incidences() -> tuple:
result_set = []
query = "SELECT t1.incidence_id, t1.title, t1.description, t1.username," \
" t1.incidence_date, t5.status_name, t3.priority_name," \
"t1.technician_hours, t1.resolve,t2.category_name " \
"FROM incidences AS t1 " \
"JOIN (categories AS t2, priorities AS t3, status AS t4, type_of_status AS t5)" \
"ON (t1.category=t2.category_id AND t1.priority=t3.priority_id " \
"AND t1.incidence_id=t4.incidence_id AND t4.status_id=t5.status_id) " \
"WHERE " \
" t1.incidence_id IN( " \
"SELECT incidence_id FROM status " \
"WHERE status_id=6) AND t4.status_id=6"
logger.info(query)
cnx = connect_db()
try:
cursor = cnx.cursor()
cursor.execute(query)
for value in cursor:
result_set.append(value)
cursor.close()
except Exception as err:
logger.error(err)
return result_set
def get_next_id():
query = "Select count(*) from incidences "
cnx = connect_db()
try:
cursor = cnx.cursor()
cursor.execute(query)
result_set = cursor.fetchmany(1)
cursor.close()
last_row = result_set[0][0] + 1
logger.info('las_row: {}'.format(last_row))
incidence_id = ''
incidence_id += 'INC_'
incidence_id += str(date.today().year) + "_"
logger.info(type(incidence_id))
logger.info(incidence_id)
if last_row < 10:
incidence_id = incidence_id + "000" + str(last_row)
elif last_row < 100:
incidence_id = incidence_id + "00" + str(last_row)
elif last_row < 1000:
incidence_id = incidence_id + "0" + str(last_row)
else:
incidence_id = incidence_id + last_row
except Exception as err:
logger.error(err)
logger.info(incidence_id)
return incidence_id
def callback_ambient(ch, method, properties, body):
path = os.getcwd()
file = path + '/logs/ambient'
ambient = json.loads(body.decode())
logger.info('sensor: {}, date: {} temp: {}ºC, humi: {}%'
.format(ambient['sensor'],
ambient['date'],
ambient['temperature'],
ambient['humidity']))
cnx = connect_db()
send_data(cnx, ambient)
def insert_status(status):
query="INSERT INTO status VALUES ('{}','{}','{}','0000-00-00 00:00:00')" \
.format(status.incidence_id, status.username, status.status_id)
logger.info(query)
cnx = connect_db()
try:
cursor = cnx.cursor()
cursor.execute(query)
cnx.commit()
cursor.close()
except Exception as err:
logger.error(err)
def update_resolve(incidence_id, resolve):
query = "UPDATE incidences SET " \
"resolve={} " \
"WHERE incidence_id='{}'".format(resolve, incidence_id)
logger.info(query)
cnx = connect_db()
try:
cursor = cnx.cursor()
cursor.execute(query)
cnx.commit()
cursor.close()
except Exception as err:
logger.error(err)
def update_technician_hours(incidence_id, hours):
query = "UPDATE incidences SET " \
"technician_hours=technician_hours+{} " \
"WHERE incidence_id='{}'".format(hours, incidence_id)
logger.info(query)
cnx = connect_db()
try:
cursor = cnx.cursor()
cursor.execute(query)
cnx.commit()
cursor.close()
except Exception as err:
logger.error(err)
def update_priority(incidence_id, priority):
query = "UPDATE incidences SET " \
"priority={} " \
"WHERE incidence_id='{}'".format(priority, incidence_id)
logger.info(query)
cnx = connect_db()
try:
cursor = cnx.cursor()
cursor.execute(query)
cnx.commit()
cursor.close()
except Exception as err:
logger.error(err)
def assign_devices(incidence_id, devices_ids):
query = ""
for device_id in devices_ids:
if device_id != ",":
query = query +"INSERT INTO assigned_devices VALUES( " \
"'{}',{})".format(incidence_id,device_id)+";"
logger.info(query)
cnx = connect_db()
try:
cursor = cnx.cursor()
cursor.execute(query)
cnx.commit()
cursor.close()
except Exception as err:
logger.error(err)
def insert_comment(comment):
incidence_id = comment.incidence_id
username = comment.username
status = comment.status
content = comment.content
query = "INSERT INTO comments " \
"VALUES (DEFAULT,'{}','{}','{}'," \
" '{}' )".format(incidence_id, username, status,
content)
logger.info(query)
cnx = connect_db()
try:
cursor = cnx.cursor()
cursor.execute(query)
cnx.commit()
cursor.close()
except Exception as err:
logger.error(err)
