def update_thing2(self, code_things, description, num1, num2, localizacao,
price, situation, state, note):
try:
sql = "UPDATE patr_bens SET pabe_num_patr1 = '" + str(
num1
) + "', pabe_num_patr2 = '" + str(
num2
) + "', pabe_descricao = '" + str(
description
) + "', pabe_loca_id = '" + str(
localizacao
) + "', pabe_valor = '" + str(
price
) + "', pabe_dt_cadastro = CURRENT_TIMESTAMP(), pabe_situacao = '" + str(
situation
) + "', pabe_estado = '" + str(
state
) + "', pabe_observacao = '" + str(
note
) + "', pabe_data_atualizacao = CURRENT_TIMESTAMP() WHERE pabe_id = '" + str(
code_things) + "'"
conn = Connection()
conn.execute_sql(sql)
conn.commit()
return True
except Exception as e:
conn.rollback()
print(e)
return False
finally:
conn.close_connection()
def generate_sql_insert_locations(self):
try:
conn = Connection()
data = ""
cursor = conn.execute_sql("SELECT * FROM `localizacao`;")
for row in cursor.fetchall():
data += "INSERT INTO `localizacao` VALUES("
first = True
for field in row:
if not first:
data += ', '
if field == None:
data += 'null'
else:
data += '"' + str("" if field == None else field) + '"'
first = False
data += ");\n"
data += "\n\n"
return data
except Exception as e:
return False
finally:
conn.close_connection()
def search_things_inactives_by_location(self, loca_id):
try:
conn = Connection()
cursor = conn.execute_sql(
"SELECT pabe_id, pabe_num_patr1, pabe_num_patr2, pabe_descricao,"
" pabe_situacao, pabe_valor, pabe_dt_cadastro, pabe_estado,"
" loca_1.loca_id AS pabe_location_id, loca_1.loca_sala AS pabe_location_sala,"
" pabe_observacao, pabe_etiqueta_ativa, loca_2.loca_id AS pblo_location_id,"
" loca_2.loca_sala AS pblo_location_sala FROM patr_bens "
" LEFT JOIN patr_bens_x_localizacao ON pabe_id = pblo_pabe_id"
" INNER JOIN localizacao AS loca_1 ON loca_1.loca_id = pabe_loca_id"
" LEFT JOIN localizacao AS loca_2 ON loca_2.loca_id = pblo_loca_id"
" WHERE pabe_etiqueta_ativa = 0 AND pabe_loca_id ='" +
str(loca_id) + "'")
# print("SELECT pabe_id, pabe_num_patr1, pabe_num_patr2, pabe_descricao,"
# " pabe_situacao, pabe_valor, pabe_dt_cadastro, pabe_estado,"
# " loca_1.loca_id AS pabe_location_id, loca_1.loca_sala AS pabe_location_sala,"
# " pabe_observacao, pabe_etiqueta_ativa, loca_2.loca_id AS pblo_location_id,"
# " loca_2.loca_sala AS pblo_location_sala FROM patr_bens "
# " LEFT JOIN patr_bens_x_localizacao ON pabe_id = pblo_pabe_id"
# " INNER JOIN localizacao AS loca_1 ON loca_1.loca_id = pabe_loca_id"
# " LEFT JOIN localizacao AS loca_2 ON loca_2.loca_id = pblo_loca_id"
# " WHERE pabe_etiqueta_ativa = 0 AND pabe_loca_id ='" + str(loca_id) + "'")
if cursor.rowcount == 0:
return False
listThings = []
for data in cursor.fetchall():
location = LocationModel(
loca_id=str("0" if data[8] == None else data[8]),
loca_room=str("" if data[9] == None else data[9]))
location_current = LocationModel(
loca_id=str("0" if data[12] == None else data[12]),
loca_room=str("" if data[13] == None else data[13]))
thingsModel = ThingsModel(
code_things=str(data[0]),
nr_things1=str("0" if data[1] == None else data[1]),
nr_things2=str("0" if data[2] == None else data[2]),
description=str("" if data[3] == None else data[3]),
situation=str("" if data[4] == None else data[4]),
value=str("0" if data[5] == None else data[5]),
date_registre=str("" if data[6] == None else data[6]),
state=str("" if data[7] == None else data[7]),
location=location,
note=str("" if data[10] == None else data[10]),
tag_activated=str("0" if data[11] == None else data[11]),
location_current=location_current)
listThings.append(thingsModel)
print(thingsModel.nr_things2)
return listThings
except Exception as e:
print(e)
return 'ERRO'
finally:
conn.close_connection()
def update_thing_location(self, pabe_id, loca_id, user):
try:
sql = "UPDATE patr_bens_x_localizacao SET pblo_loca_id = '"+str(loca_id)+"', pblo_usua_id = '"+str(user)+"' WHERE pblo_pabe_id = "+pabe_id
conn = Connection()
conn.execute_sql(sql)
conn.commit()
return True
except Exception as e:
print(e)
conn.rollback()
return False
finally:
conn.close_connection()
def check_thing_location_exists(self, nr_things1):
try:
sql = "SELECT * FROM patr_bens_x_localizacao INNER JOIN patr_bens ON pblo_pabe_id = pabe_id WHERE pabe_num_patr1 = "+ str(nr_things1)
conn = Connection()
cursor = conn.execute_sql(sql)
if(cursor.rowcount == 0):
return False
return True
except Exception as e:
print(e)
return 'ERRO'
finally:
conn.close_connection()
def get_location_current(self, pabe_id):
try:
sql = "SELECT pblo_loca_id FROM patr_bens_x_localizacao WHERE pblo_pabe_id = " + str(pabe_id)
conn = Connection()
cursor = conn.execute_sql(sql)
if (cursor.rowcount == 0):
return False
return cursor.fetchone()[0]
except Exception as e:
print(e)
return 'ERRO'
finally:
conn.close_connection()
def insert_patr_bens_x_localizacao(self, pabe_id, loca_id, user):
try:
sql = "INSERT INTO patr_bens_x_localizacao (pblo_pabe_id, pblo_loca_id, pblo_usua_id) VALUES('"+str(pabe_id)+"', '"+str(loca_id)+"', '"+str(user)+"')"
conn = Connection()
conn.execute_sql(sql)
conn.commit()
return True
except Exception as e:
print(e)
conn.rollback()
return False
finally:
conn.close_connection()
def delete_location(self, loca_id):
try:
sql = "UPDATE localizacao set loca_excluido = 1, loca_data_atualizacao = CURRENT_TIMESTAMP() WHERE loca_id = " + str(
loca_id)
conn = Connection()
conn.execute_sql(sql)
conn.commit()
return True
except Exception as e:
conn.rollback()
print(e)
return False
finally:
conn.close_connection()
def active_things_by_num1(self, nr_things1):
try:
sql = "UPDATE patr_bens set pabe_etiqueta_ativa = 1, pabe_data_atualizacao = CURRENT_TIMESTAMP() WHERE pabe_num_patr1 = " + str(
nr_things1)
conn = Connection()
conn.execute_sql(sql)
conn.commit()
return True
except Exception as e:
print(e)
conn.rollback()
return False
finally:
conn.close_connection()
def search_things_missing_by_location(self, loca_id):
try:
sql = "SELECT pabe_id, pabe_num_patr1, pabe_num_patr2," \
" pabe_descricao, pabe_situacao, pabe_valor, pabe_dt_cadastro," \
" pabe_estado,loca_1.loca_sala as pabe_loca_sala, pabe_observacao," \
" pabe_etiqueta_ativa, pblo_id, loca_2.loca_sala as pblo_loca_sala," \
" pblo_usua_id, pblo_dt_primeira_leitura, pblo_dt_ultima_leitura," \
" loca_1.loca_id as pabe_location_id, loca_2.loca_id as pblo_location_id " \
" FROM patr_bens_x_localizacao INNER JOIN patr_bens ON pabe_id = pblo_pabe_id" \
" INNER JOIN localizacao AS loca_1 ON loca_1.loca_id = pabe_loca_id" \
" INNER JOIN localizacao AS loca_2 ON loca_2.loca_id = pblo_loca_id" \
" WHERE pabe_excluido = 0 AND pblo_loca_id <> '" + str(loca_id) + "' AND pabe_loca_id ='" + str(
loca_id) + "'"
print(sql)
conn = Connection()
cursor = conn.execute_sql(sql)
if cursor.rowcount == 0:
return False
listThings = []
for data in cursor.fetchall():
location = LocationModel(loca_id=str("0" if data[16] == None else data[16]),
loca_room=str("" if data[8] == None else data[8]))
location_current = LocationModel(loca_id=str("0" if data[17] == None else data[17]),
loca_room=str("" if data[12] == None else data[12]))
thingsModel = ThingsXLocationModel(code_things=str(data[0]),
nr_things1=str("0" if data[1] == None else data[1]),
nr_things2=str("0" if data[2] == None else data[2]),
description=str(data[3]),
situation=str("" if data[4] == None else data[4]),
value=str("0" if data[5] == None else data[5]),
date_registre=str("" if data[6] == None else data[6]),
state=str("" if data[7] == None else data[7]), location=location,
note=str("" if data[9] == None else data[9]),
tag_activated=str("0" if data[10] == None else data[10]),
location_current=location_current,
pblo_id=str("0" if data[11] == None else data[11]),
pblo_loca_id=str("0" if data[17] == None else data[17]),
pblo_usua_id=str("0" if data[13] == None else data[13]),
pblo_dt_first_read=str("" if data[14] == None else data[14]),
pblo_dt_last_read=str("" if data[15] == None else data[15]))
listThings.append(thingsModel)
print(thingsModel.nr_things2)
return listThings
except Exception as e:
print(e)
return 'ERRO'
finally:
conn.close_connection()
def search_user_by_id(self, id):
try:
sql = "SELECT * FROM usuarios WHERE usua_id = " + str(id)
conn = Connection()
cursor = conn.execute_sql(sql)
data = cursor.fetchone()
if data != None:
return UserModel(id=data[0], name=data[1], email=data[2], permission=data[4])
return False
except Exception as e:
print(e)
return 'ERRO'
finally:
conn.close_connection()
def edit_user(self, id, name, email, password, permission):
try:
sql = "UPDATE usuarios set usua_nome = '" + str(name) + "', usua_email = '" + str(
email) + "', usua_senha='" + str(password) + "', usua_permissao = '" + str(
permission) + "' WHERE usua_id = " + str(id) + ""
conn = Connection()
conn.execute_sql(sql)
conn.commit()
return True
except Exception as e:
conn.rollback()
print(e)
return False
finally:
conn.close_connection()
def insert_new_user(self, name, email, password, token, permission='3'):
try:
sql = "INSERT INTO usuarios (usua_nome, usua_email, usua_senha, usua_permissao, usua_token) VALUES('" + str(
name) + "','" + str(email) + "','" + str(password) + "', '" + str(permission) + "','" + str(
token) + "')"
conn = Connection()
conn.execute_sql(sql)
conn.commit()
return True
except Exception as e:
conn.rollback()
print(e)
return False
finally:
conn.close_connection()
def verify_token(self, token):
try:
conn = Connection()
cursor = conn.execute_sql("SELECT * FROM usuarios WHERE usua_token = '" + token + "'")
if cursor.rowcount == 0:
return False
else:
data = cursor.fetchone()
return UserModel(id=str(data[0]), name=str(data[1]), email=str(data[2]), permission=str(data[4]),
token=str(data[5]))
except Exception as e:
print(e)
return 'ERRO'
finally:
conn.close_connection()
def update_thing(self, code_things, situation, state, note):
try:
sql = "UPDATE patr_bens SET pabe_situacao = '" + str(
situation) + "', pabe_estado = '" + str(
state) + "', pabe_observacao = '" + str(
note) + "' WHERE pabe_id = " + code_things
conn = Connection()
conn.execute_sql(sql)
conn.commit()
return True
except Exception as e:
print(e)
conn.rollback()
return False
finally:
conn.close_connection()
def search_location_by_id(self, loca_id):
try:
sql = "SELECT * FROM localizacao WHERE loca_excluido = 0 AND loca_id = " + str(
loca_id)
conn = Connection()
cursor = conn.execute_sql(sql)
data = cursor.fetchone()
if data != None:
return LocationModel(loca_id=data[0], loca_room=data[1])
return False
except Exception as e:
print(e)
return 'ERRO'
finally:
conn.close_connection()
def autenticate(self, email, password):
conn = Connection()
try:
cursor = conn.execute_sql(
"SELECT * FROM usuarios WHERE usua_email ='" + email + "' AND usua_senha = '" + password + "'")
if cursor.rowcount == 0:
return False
else:
data = cursor.fetchone();
return UserModel(id=str(data[0]), name=str(data[1]), email=str(data[2]), permission=str(data[4]),
token=str(data[5]))
# return cursor.fetchone()[0]
except Exception as e:
print(e)
return 'ERRO'
finally:
conn.close_connection()
def edit_location(self, loca_id, loca_room):
try:
sql = "UPDATE localizacao set loca_sala = '" + str(
loca_room
) + "', loca_data_atualizacao = CURRENT_TIMESTAMP() WHERE loca_id = " + str(
loca_id) + ""
print(sql)
conn = Connection()
conn.execute_sql(sql)
conn.commit()
return True
except Exception as e:
conn.rollback()
print(e)
return False
finally:
conn.close_connection()
def search_locations(self):
try:
sql = "SELECT * FROM localizacao ORDER BY loca_sala ASC"
conn = Connection()
cursor = conn.execute_sql(sql)
if cursor.rowcount == 0:
return False
listLocations = []
for data in cursor.fetchall():
listLocations.append(
LocationModel(loca_id=str(data[0]), loca_room=data[1]))
return listLocations
except Exception as e:
print(e)
return 'ERRO'
finally:
conn.close_connection()
def search_all_users(self):
try:
sql = "SELECT * FROM usuarios ORDER BY usua_nome"
conn = Connection()
cursor = conn.execute_sql(sql)
if (cursor.rowcount == 0):
return False
listUsers = []
for data in cursor.fetchall():
userModel = UserModel(id=data[0], name=data[1], email=data[2], permission=data[4])
listUsers.append(userModel)
return listUsers
except Exception as e:
print(e)
return 'ERRO'
finally:
conn.close_connection()
def search_things_by_num1(self, nr_things1):
try:
conn = Connection()
cursor = conn.execute_sql(
"SELECT pabe_id, pabe_num_patr1, pabe_num_patr2,"
" pabe_descricao, pabe_situacao, pabe_valor, pabe_dt_cadastro,"
" pabe_estado, loca_1.loca_id AS pabe_location_id,"
" loca_1.loca_sala AS pabe_location_sala, pabe_observacao,"
" pabe_etiqueta_ativa, loca_2.loca_id AS pblo_location_id,"
" loca_2.loca_sala AS pblo_location_sala"
" FROM patr_bens LEFT JOIN patr_bens_x_localizacao ON pabe_id = pblo_pabe_id"
" LEFT JOIN localizacao AS loca_1 ON loca_1.loca_id = pabe_loca_id"
" LEFT JOIN localizacao AS loca_2 ON loca_2.loca_id = pblo_loca_id"
" WHERE pabe_excluido = 0 AND pabe_num_patr1 = '" +
str(nr_things1) + "'")
if cursor.rowcount == 0:
return False
data = cursor.fetchone()
location = LocationModel(
loca_id=str("0" if data[8] == None else data[8]),
loca_room=str("" if data[9] == None else data[9]))
location_current = LocationModel(
loca_id=str("0" if data[12] == None else data[12]),
loca_room=str("" if data[13] == None else data[13]))
thingsModel = ThingsModel(
code_things=str(data[0]),
nr_things1=str("0" if data[1] == None else data[1]),
nr_things2=str("0" if data[2] == None else data[2]),
description=str("" if data[3] == None else data[3]),
situation=str("" if data[4] == None else data[4]),
value=str("0" if data[5] == None else data[5]),
date_registre=str("" if data[6] == None else data[6]),
state=str("" if data[7] == None else data[7]),
location=location,
note=str("" if data[10] == None else data[10]),
tag_activated=str("0" if data[11] == None else data[11]),
location_current=location_current)
return thingsModel
except Exception as e:
print(e)
return 'ERRO'
finally:
conn.close_connection()
def insert_new_thing(self, thingNum1, thingNum2, thingName, thingPrice,
thingSituation, thingCondition, thingObservation):
try:
sql = "INSERT INTO patr_bens (pabe_num_patr1, pabe_num_patr2, pabe_descricao, pabe_valor, pabe_dt_cadastro, pabe_situacao, pabe_estado, pabe_observacao, pabe_etiqueta_ativa) VALUES('" + str(
thingNum1) + "','" + str(thingNum2) + "','" + str(
thingName) + "','" + str(
thingPrice) + "', CURRENT_TIMESTAMP() ,'" + str(
thingSituation) + "','" + str(
thingCondition) + "','" + str(
thingObservation) + "', '0')"
print(sql)
conn = Connection()
conn.execute_sql(sql)
conn.commit()
return True
except Exception as e:
conn.rollback()
print(e)
return False
finally:
conn.close_connection()
def insert_location(self, loca_room):
conn = Connection()
try:
sql = "INSERT INTO localizacao (loca_sala) VALUES('" + str(
loca_room) + "')"
conn = Connection()
conn.execute_sql(sql)
conn.commit()
return True
except Exception as e:
conn.rollback()
print(e)
return False
finally:
conn.close_connection()