def getDevicesMissing(id):
print("starting")
try:
ServiceSQL.getConector().execute(
"select Dispositivos.ID,codigo,producto,marca,fecha,modelo,foto,cantidad,observaciones,IDlugar,pertenece,descompostura,costo,compra,serie,proveedor,Lugares.lugar,origen,Dispositivos.IDlugar,IDmov from Dispositivos inner join Lugares on Dispositivos.IDlugar = Lugares.ID where Dispositivos.IDmov= '"
+ id + "' ")
print("queried")
row = ServiceSQL.getConector().fetchall()
if len(row) == 0:
return 1
rows = [x for x in row]
cols = [x[0] for x in ServiceSQL.getConector().description]
filas = []
for row in rows:
fila = {}
for prop, val in zip(cols, row):
if isinstance(val, (datetime, date)):
fila[prop] = val.isoformat()
else:
fila[prop] = val
filas.append(fila)
print(filas)
datos = json.dumps(filas)
return datos
except ValueError:
print('error sql')
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 SearchMissingEntries(id):
try:
strsel = "select Movimientos.ID,Movimientos.IDmovimiento,Movimientos.IDtipomov,Movimientos.IDusuario,Movimientos.fechamovimiento,Dispositivos.codigo,Dispositivos.producto,Dispositivos.serie,Dispositivos.marca,Dispositivos.modelo,Dispositivos.IDlugar,Usuarios.nombre,Lugares.Lugar,observacionesMov,tipomovimiento,fotomov1,fotomov2,cantidad from Movimientos inner join Dispositivos on Movimientos.IDdevice = Dispositivos.ID inner join Moves on Movimientos.IDtipomov = Moves.ID inner join Usuarios on Movimientos.IDusuario = Usuarios.ID inner join Lugares on Dispositivos.IDlugar = Lugares.ID where IDlugar!=1 and IDtipomov =2 and IDmovimiento = '" + id + "'"
ServiceSQL.getConector().execute(strsel)
print("queried")
row = ServiceSQL.getConector().fetchall()
if len(row) == 0:
return 1
rows = [x for x in row]
cols = [x[0] for x in ServiceSQL.getConector().description]
filas = []
for row in rows:
fila = {}
for prop, val in zip(cols, row):
#print(prop,val)
if isinstance(val, (datetime, date)):
fila[prop] = val.isoformat()
else:
fila[prop] = val
#print(fila)
filas.append(fila)
datos = json.dumps(filas)
return datos
except Exception as e:
return 2
def getMove(id):
try:
print("atarting")
print(id)
ServiceSQL.getConector().execute(
"SELECT * from Moves where ID = " + id + "")
print("queried")
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({'ID': item[0], 'tipomovimiento': item[1]})
print(items)
datos = json.dumps(items)
return datos
except ValueError:
print(ValueError)
return 2
def getmoves():
try:
ServiceSQL.getConector().execute("SELECT * from Moves")
print("queried")
row = ServiceSQL.getConector().fetchall()
if len(row) == 0:
return 1
data = []
print(row)
for r in row:
data.append([x for x in r])
items = []
for item in row:
items.append({'ID': item[0], 'tipomovimiento': item[1]})
datos = json.dumps(items)
#print(json.dumps(data))
return datos
except ValueError:
print(ValueError)
return 2
def GetUserByName(name):
try:
ServiceSQL.getConector().execute(
"SELECT Usuarios.ID,Usuarios.nombre,Usuarios.apellido_paterno,Usuarios.apellido_materno,Usuarios.IDtipoUsuario,Usuarios.fecha,Usuarios.telefono,Usuarios.correo,Roles.rol,Usuarios.foto from Usuarios inner join Roles on Usuarios.IDtipoUsuario = Roles.ID where Usuarios.nombre = '"
+ name + "'")
print("queried")
row = ServiceSQL.getConector().fetchall()
if len(row) == 0:
return 1
rows = [x for x in row]
cols = [x[0] for x in ServiceSQL.getConector().description]
filas = []
for row in rows:
fila = {}
for prop, val in zip(cols, row):
if isinstance(val, (datetime, date)):
fila[prop] = val.isoformat()
else:
fila[prop] = val
filas.append(fila)
#print(filas)
datos = json.dumps(filas)
#print(json.dumps(data))
return datos
except:
print("Error de SQL")
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 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 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 GetDevice(id):
try:
ServiceSQL.getConector().execute(
"select * from Dispositivos inner join Lugares on Dispositivos.IDlugar = Lugares.ID where Dispositivos.ID= "
+ id + "")
print("queried")
row = ServiceSQL.getConector().fetchall()
if len(row) == 0:
return 1
data = []
items = []
print(row)
#for r in row:
# data.append([x for x in r])
for item in row:
items.append({
'ID': item[0],
'nombre': item[1],
'apellido_materno': item[2],
'apellido_paterno': item[3],
'contrasena': item[4],
'tipoUsuario': item[5],
'fechaContratacion': item[6],
'telefono': item[7],
'correo': item[8]
})
objectouser = {
"ID": item[0],
"nombre": item[1],
"apellido_materno": item[2],
"apellido_paterno": item[3],
"contrasena": item[4],
"tipoUsuario": item[5],
"fechaContratacion": item[6],
"telefono": item[7],
"correo": item[8]
}
datos = json.dumps(objectouser)
#print(json.dumps(data))
return datos
except:
print("Error de SQL")
return None
def ValidateSamePlaces(data):
try:
idlugarActual = 0
idlugarpas = 0
isfirst = True
for key, value in data.items():
strsel = "select IDlugar from Dispositivos where ID = "
if type(value) is int:
strsel += str(value)
elif type(value) is str:
strsel += "'" + value + "'"
elif value is None:
strsel += "'None'"
print(strsel)
ServiceSQL.getConector().execute(strsel)
#ServiceSQL.getcnxn().commit()
row = ServiceSQL.getConector().fetchall()
strsel = ""
items = []
for item in row:
items.append(item[0])
if len(items) > 0:
if isfirst:
isfirst = False
idlugarpas = int(items[0])
else:
idlugarActual = int(items[0])
if idlugarActual != idlugarpas:
return 2
else:
return 1
return 0
except Exception as e:
print(e)
return 3
def getHistorialbysearch(listaargs):
print("starting")
try:
columnas = [
"IDmovimiento", "IDtipomov", "IDlugar", "IDusuario",
"producto", "fechamovimiento", "modelo", "marca", "codigo",
"serie"
]
strsel = strselect(listaargs, columnas)
print(strsel)
ServiceSQL.getConector().execute(strsel)
print("queried")
row = ServiceSQL.getConector().fetchall()
if len(row) == 0:
return 1
rows = [x for x in row]
cols = [x[0] for x in ServiceSQL.getConector().description]
filas = []
for row in rows:
fila = {}
for prop, val in zip(cols, row):
#print(prop,val)
if isinstance(val, (datetime, date)):
fila[prop] = val.isoformat()
else:
fila[prop] = val
#print(fila)
filas.append(fila)
datos = json.dumps(filas)
return datos
except Exception as e:
print(e)
return 2
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 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 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 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 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 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 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 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 hello():
status = ServiceSQL.reconncetOnce()
return status
if status == True:
return "SQL Connected"
else:
return "SQL Disconnected"
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 GetUsers():
print("starting")
try:
ServiceSQL.getConector().execute(
"SELECT Usuarios.ID,Usuarios.nombre,Usuarios.apellido_paterno,Usuarios.apellido_materno,Usuarios.IDtipoUsuario,Usuarios.fecha,Usuarios.telefono,Usuarios.correo,Roles.rol from Usuarios inner join Roles on Usuarios.IDtipoUsuario = Roles.ID"
)
print("queried")
row = ServiceSQL.getConector().fetchall()
if len(row) == 0:
return 1
#data = []
#print(row)
#for r in row:
# data.append([x for x in r])
#items = []
#for item in row:
# items.append({'ID':item[0],'nombre':item[1],'apellido_materno':item[2],'apellido_paterno':item[3],'contrasena':item[4],'tipoUsuario':item[5],'fechaContratacion':item[6],'telefono':item[7],'correo':item[8]})
#datos = json.dumps(items)
rows = [x for x in row]
cols = [x[0] for x in ServiceSQL.getConector().description]
filas = []
for row in rows:
fila = {}
for prop, val in zip(cols, row):
if isinstance(val, (datetime, date)):
fila[prop] = val.isoformat()
else:
fila[prop] = val
filas.append(fila)
datos = json.dumps(filas)
#print(json.dumps(data))
return datos
except Exception as e:
print(e)
return 2
def getReportsbycode(id):
print("starting")
try:
ServiceSQL.getConector().execute(
"select Reportes.ID,Reportes.IDreporte, Reportes.IDdevice,Reportes.IDusuario,Reportes.foto2,Reportes.fechareporte,Dispositivos.codigo,Dispositivos.producto,Dispositivos.marca,Dispositivos.serie,Usuarios.nombre,Reportes.foto2,comentario from Reportes inner join Dispositivos on Reportes.IDdevice = Dispositivos.ID inner join Usuarios on Reportes.IDusuario = Usuarios.ID where codigo = '"
+ id + "' order by fechareporte")
print("queried")
row = ServiceSQL.getConector().fetchall()
if len(row) == 0:
return 1
rows = [x for x in row]
cols = [x[0] for x in ServiceSQL.getConector().description]
filas = []
for row in rows:
fila = {}
for prop, val in zip(cols, row):
#print(prop,val)
if isinstance(val, (datetime, date)):
fila[prop] = val.isoformat()
else:
fila[prop] = val
#print(fila)
filas.append(fila)
datos = json.dumps(filas)
return datos
except ValueError:
print('error sql')
return 2
def loginprev(usuario):
try:
ServiceSQL.getConector().execute(
"select nombre,password from Usuarios where nombre = '" +
usuario['nombre'] + "'")
row = ServiceSQL.getConector().fetchall()
print(row[0][1])
if len(row) == 0:
return 1
#if usuario['password'] == row[0][1]:
if check_password_hash(row[0][1], usuario['password']) == True:
return 0
else:
return 2
except ValueError:
print("error de transaccion")
return 3
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 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
