def postDevice(dispositivo):
try:
ServiceSQL.getConector().execute(
"SELECT count(*) from Dispositivos where codigo = '" +
dispositivo['codigo'] + "'")
row = ServiceSQL.getConector().fetchall()
print(row)
data = []
for r in row:
data.append([x for x in r])
items = []
for item in row:
items.append(item[0])
if items[0] == 0:
strinsert = cmdinsert("Dispositivos", dispositivo)
#print(strinsert)
#ServiceSQL.getConector().execute("INSERT INTO InventDB (ID,nombre,apellido_materno,apellido_paterno,contrasena,tipoUsuario,fechaContratacion,telefono,correo) VALUES ('" + usuario['ID'] + "','" + usuario['nombre'] + "','" + usuario['apellido_materno'] + "','" + usuario['apellido_paterno'] + "','" + usuario['contrasena'] + "','" + usuario['tipoUsuario'] + "','" + usuario['fechaContratacion'] + "','" + usuario['telefono'] + "','" + usuario['correo'] + "')")
ServiceSQL.getConector().execute(strinsert)
ServiceSQL.getcnxn().commit()
return 0
else:
return 1
return 0
except Exception as e:
print(e)
return 2
def delhistorialByID(id):
print("eliminando historial")
try:
ServiceSQL.getConector().execute(
"SELECT * from Movimientos where ID = " + id + "")
row = ServiceSQL.getConector().fetchall()
if len(row) == 0:
return 1
data = []
for r in row:
data.append([x for x in r])
items = []
for item in row:
items.append(item[0])
if item[0] > 0:
#delete user
ServiceSQL.getConector().execute(
"Delete from Movimientos WHERE ID = " + id + "")
ServiceSQL.getcnxn().commit()
print('deleted')
return 0
else:
return 1
except:
print('server error')
return 2
def postLugar(objeto):
print(objeto)
try:
ServiceSQL.getConector().execute(
"SELECT count(*) from Lugares where Lugar = '" +
objeto['Lugar'] + "'")
row = ServiceSQL.getConector().fetchall()
#print(row)
data = []
for r in row:
data.append([x for x in r])
items = []
for item in row:
items.append(item[0])
if items[0] == 0:
cmdinsert = "insert into Lugares(Lugar) values ('" + objeto[
'Lugar'] + "')"
print(cmdinsert)
ServiceSQL.getConector().execute(cmdinsert)
ServiceSQL.getcnxn().commit()
return 0
else:
return 1
except Exception as e:
print(e)
return 2
def putLugar(id, objeto):
try:
print('starting')
print(id)
ServiceSQL.getConector().execute(
"SELECT count(*) from Lugares where ID = " + id + "")
row = ServiceSQL.getConector().fetchall()
items = []
for item in row:
items.append(item[0])
print(items[0])
if items[0] > 0:
#update user
print('updating')
ServiceSQL.getConector().execute(
"UPDATE Lugares SET Lugar = '" + objeto['Lugar'] +
"' WHERE ID = " + id + "")
ServiceSQL.getcnxn().commit()
print('updated')
return 0
else:
return 1
except Exception as e:
print(e)
return 2
def deleteLugar(id):
try:
ServiceSQL.getConector().execute(
"SELECT * from Lugares where ID = " + id + "")
row = ServiceSQL.getConector().fetchall()
print(row)
data = []
for r in row:
data.append([x for x in r])
items = []
for item in row:
items.append(item[0])
print(items[0])
if items[0] > 0:
#delete user
ServiceSQL.getConector().execute(
"Delete from Lugares WHERE iD = " + id + "")
ServiceSQL.getcnxn().commit()
print('deleted')
return 0
else:
return 1
except ValueError:
print(ValueError)
def delReportsBydevice(id):
print("eliminando reportes")
try:
ServiceSQL.getConector().execute(
"SELECT * from Reportes where IDdevice = " + id + "")
row = ServiceSQL.getConector().fetchall()
data = []
if len(row) == 0:
return 1
for r in row:
data.append([x for x in r])
items = []
for item in row:
items.append(item[0])
if item[0] > 0:
#delete resports
ServiceSQL.getConector().execute(
"Delete from Reportes WHERE IDdevice = " + id + "")
ServiceSQL.getcnxn().commit()
print('deleted')
return 0
else:
return 1
except Exception as e:
print(e)
return 2
def putUser(usuario, name):
#check if use exist
try:
ServiceSQL.getConector().execute(
"SELECT * from Usuarios where ID = " + name + "")
row = ServiceSQL.getConector().fetchall()
print('queried')
if len(row) >= 0:
# check if user by name already exist
#
ServiceSQL.getConector().execute(
"SELECT * from Usuarios where nombre = '" +
usuario['nombre'] + "' and ID != " + name + " ")
row = ServiceSQL.getConector().fetchall()
print(len(row))
if (len(row) > 0):
return 3
#
#update user
sqlupdate = cmdupdate("Usuarios", usuario, name)
ServiceSQL.getConector().execute(sqlupdate)
ServiceSQL.getcnxn().commit()
print('updated')
return 0
else:
return 1
except Exception as e:
print(e)
return 2
def postUser(usuario):
try:
#print(usuario)
ServiceSQL.getConector().execute(
"SELECT count(*) from Usuarios where nombre = '" +
usuario['nombre'] + "'")
row = ServiceSQL.getConector().fetchall()
#print(row)
data = []
for r in row:
data.append([x for x in r])
items = []
for item in row:
items.append(item[0])
if items[0] == 0:
sqlinsert = cmdinsert("Usuarios", usuario)
#print(sqlinsert)
ServiceSQL.getConector().execute(sqlinsert)
ServiceSQL.getcnxn().commit()
return 0
else:
return 1
except Exception as e:
print(e)
return 2
def putMove(id, objeto):
try:
print('starting')
print(id)
ServiceSQL.getConector().execute(
"SELECT count(*) from Moves where ID = " + id + "")
row = ServiceSQL.getConector().fetchall()
data = []
for r in row:
data.append([x for x in r])
items = []
for item in row:
items.append(item[0])
print(items[0])
if items[0] > 0:
#update user
print('updating')
ServiceSQL.getConector().execute(
"UPDATE Moves SET tipomovimiento = '" +
objeto['tipomovimiento'] + "' WHERE ID = " + id + "")
ServiceSQL.getcnxn().commit()
print('updated')
return 0
else:
return 1
except ValueError:
print(ValueError)
return 2
def postRole(objeto):
print(objeto)
try:
cmdinsert = "insert into Roles values ('" + objeto['rol'] + "')"
print(cmdinsert)
ServiceSQL.getConector().execute(cmdinsert)
ServiceSQL.getcnxn().commit()
return 0
except Exception as e:
print(e)
return 2
def postReport(reporte):
try:
insert = cmdinsert("Reportes", reporte)
#ServiceSQL.getConector().execute("INSERT INTO InventDB (ID,nombre,apellido_materno,apellido_paterno,contrasena,tipoUsuario,fechaContratacion,telefono,correo) VALUES ('" + usuario['ID'] + "','" + usuario['nombre'] + "','" + usuario['apellido_materno'] + "','" + usuario['apellido_paterno'] + "','" + usuario['contrasena'] + "','" + usuario['tipoUsuario'] + "','" + usuario['fechaContratacion'] + "','" + usuario['telefono'] + "','" + usuario['correo'] + "')")
ServiceSQL.getConector().execute(insert)
ServiceSQL.getcnxn().commit()
return 0
except Exception as e:
print(e)
return 2
def postMove(objeto):
print(objeto)
try:
cmdinsert = "insert into Moves values ('" + objeto[
'tipomovimiento'] + "')"
print(cmdinsert)
ServiceSQL.getConector().execute(cmdinsert)
ServiceSQL.getcnxn().commit()
return 0
except Exception as e:
print(e)
return 2
def delDevice(id):
print(id)
try:
ServiceSQL.getConector().execute(
"SELECT * from Dispositivos where ID = " + id + "")
row = ServiceSQL.getConector().fetchall()
if len(row) == 0:
return 1
data = []
for r in row:
data.append([x for x in r])
items = []
for item in row:
items.append(item[0])
if len(items) > 0:
#delete user
# first of all check for historial an reports
resreport = ReportDB.delReportsBydevice(id)
reshist = HistorialDB.delhistorialByDevice(id)
if (resreport != 2 and reshist != 2):
ServiceSQL.getConector().execute(
"Delete from Dispositivos WHERE ID = " + id + "")
ServiceSQL.getcnxn().commit()
print('deleted')
return 0
else:
return 3
else:
return 1
except Exception as e:
print(e)
return 2
def deleteMove(id):
try:
ServiceSQL.getConector().execute(
"SELECT * from Moves where ID = " + id + "")
row = ServiceSQL.getConector().fetchall()
print(row)
if len(row) > 0:
#delete user
ServiceSQL.getConector().execute(
"Delete from Moves WHERE iD = " + id + "")
ServiceSQL.getcnxn().commit()
print('deleted')
return 0
else:
return 1
except ValueError:
print(ValueError)
def postHistorial(movimiento):
try:
columns = []
for r in ServiceSQL.getConector().columns(table='Movimientos'):
columns.append(r.column_name)
#print(columns)
insert = cmdinsert("Movimientos", movimiento)
#ServiceSQL.getConector().execute("INSERT INTO InventDB (ID,nombre,apellido_materno,apellido_paterno,contrasena,tipoUsuario,fechaContratacion,telefono,correo) VALUES ('" + usuario['ID'] + "','" + usuario['nombre'] + "','" + usuario['apellido_materno'] + "','" + usuario['apellido_paterno'] + "','" + usuario['contrasena'] + "','" + usuario['tipoUsuario'] + "','" + usuario['fechaContratacion'] + "','" + usuario['telefono'] + "','" + usuario['correo'] + "')")
ServiceSQL.getConector().execute(insert)
ServiceSQL.getcnxn().commit()
return 0
except Exception as e:
print(e)
return 2
def delDevice(codigo):
print(codigo)
try:
ServiceSQL.getConector().execute(
"SELECT * from InventDB where codigo = '" + codigo + "'")
row = ServiceSQL.getConector().fetchall()
print(len(row))
if len(row) > 0:
#delete user
ServiceSQL.getConector().execute(
"Delete from InventDB WHERE codigo = '" + codigo + "'")
ServiceSQL.getcnxn().commit()
print('deleted')
return 0
else:
return 1
except:
print('server error')
return 2
def putpass(usuario, id):
try:
ServiceSQL.getConector().execute(
"SELECT * from Usuarios where ID = " + id + "")
row = ServiceSQL.getConector().fetchall()
if len(row) >= 0:
#update pass
newpass = generate_password_hash(usuario['password'])
sqlupdate = "UPDATE Usuarios SET password = '******' WHERE ID = " + id + ""
ServiceSQL.getConector().execute(sqlupdate)
ServiceSQL.getcnxn().commit()
print('updated')
return 0
else:
return 1
except Exception as e:
print(e)
return 2
def putDevice(dispositivo):
#check if use exist
try:
ServiceSQL.getConector().execute(
"SELECT * from InventDB where nombre = '" +
dispositivo['codigo'] + "'")
row = ServiceSQL.getConector().fetchall()
print(row)
if len(row) >= 0:
#update user
ServiceSQL.getConector().execute(
"UPDATE InventDB SET codigo = '" + dispositivo['codigo'] +
"',nombre = '" + dispositivo['nombre'] + "',marca = '" +
dispositivo['marca'] + "',foto = '" + dispositivo['foto'] +
"',cantidad = '" + dispositivo['cantidad'] +
"',origen = '" + dispositivo['origen'] +
"',observaciones = '" + dispositivo['observaciones'] +
"',lugar = '" + dispositivo['lugar'] + "',pertenece = '" +
dispositivo['pertenece'] + "',descompostura = '" +
dispositivo['descompostura'] + "',costo = '" +
dispositivo['costo'] + "',compra = '" +
dispositivo['compra'] + "', serie = '" +
dispositivo['serie'] + "',proveedor = '" +
dispositivo['proveedor'] + "' WHERE ID = '" +
dispositivo['ID'] + "'")
ServiceSQL.getcnxn().commit()
print('updated')
return 0
else:
return 1
except:
print('server error')
return 2
def putDevice(dispositivo, id):
#check if use exist
try:
ServiceSQL.getConector().execute(
"SELECT * from Dispositivos where ID = " + id + "")
row = ServiceSQL.getConector().fetchall()
#print(row)
if len(row) >= 0:
#update device
strupdate = cmdupdate("Dispositivos", dispositivo, id)
#print(strupdate)
ServiceSQL.getConector().execute(strupdate)
ServiceSQL.getcnxn().commit()
print('updated')
return 0
else:
return 1
except Exception as e:
print(e)
return 2
