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 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_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 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 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()