def getTracker(self): engine = db.Engine.instance with engine.connect() as conn: sql = """ select im.item_id, cantidad, m.tipo, m.fecha, "MOVIMIENTO" as evento , m.cliente_id, rv.precio from items_movido as im left join movimiento as m on m.id == im.movimiento_id left join (select pv.item_id, pv.precio, m.id from precio_venta as pv left join items_movido im on im.item_id = pv.item_id left join movimiento as m on m.id == im.movimiento_id and m.fecha between pv.fecha_inicio and ifnull(pv.fecha_final,'2999-12-31')) as rv on rv.item_id == im.item_id and rv.id == m.id where im.item_id==%d union select item_id, cantidad, "NONE", fecha, "VENTA", cliente_id, precio from venta as v where v.item_id==%d order by fecha """ % (self.id, self.id) results = conn.execute(text(sql)) obj_result = [] session = db.session() for r in results: o = type('', (), {})() o.cliente = session.query(Cliente).filter( Cliente.id == r['cliente_id']).one() o.cantidad = r['cantidad'] o.tipo = r['evento'] o.valor = r['precio'] o.fecha = datetime.strptime(r['fecha'], '%Y-%m-%d %H:%M:%S.%f') obj_result.append(o) return obj_result
def getItemsByClient(procedencia): # TODO: Make this from another way. This is for going fast and is a shit engine = db.Engine.instance with engine.connect() as conn: sql = """ select r1.item_id, (ifnull(r1.total_sal,0) - ifnull(r2.total_dev,0) - ifnull(rv.vendido,0)) as tiene, r1.cliente_id from (select im.item_id, m.tipo, c.nombre, sum(im.cantidad) as total_sal, m.cliente_id from movimiento as m left join items_movido as im on im.movimiento_id == m.id left join item as i on i.id == im.item_id left join cliente as c on c.id == m.cliente_id where m.tipo = "SALIDA" and i.procedencia_id == %d group by im.item_id, m.tipo,m.cliente_id) as r1 left join (select im.item_id, m.tipo, c.nombre, sum(im.cantidad)as total_dev, m.cliente_id from movimiento as m left join items_movido as im on im.movimiento_id == m.id left join cliente as c on c.id == m.cliente_id where m.tipo = "DEVOLUCION" group by im.item_id, m.tipo) as r2 on r1.item_id == r2.item_id and r1.cliente_id == r2.cliente_id left join item on item.id == r1.item_id left join (select v.item_id, v.cliente_id, sum(v.cantidad) as vendido from venta as v group by v.item_id, v.cliente_id) as rv on r1.item_id == rv.item_id and r1.cliente_id == rv.cliente_id where tiene > 0; """ % procedencia results = conn.execute(text(sql)) res = [] items_id = [] clients_id = [] for row in results: items_id.append(row['item_id']) clients_id.append(row['cliente_id']) res.append((row['item_id'], row['cliente_id'], row['tiene'])) session = db.session() items = session.query(Item).filter(Item.id.in_(items_id)).all() clientes = session.query(Cliente).filter( Cliente.id.in_(clients_id)).all() obj_res = [] for r in res: o = type('', (), {})() for i in items: if i.id == r[0]: o.item = i for c in clientes: if c.id == r[1]: o.cliente = c o.tiene = r[2] obj_res.append(o) return obj_res
def posession_items(self): # TODO: Make this from another way. This is for going fast and is a shit engine = db.Engine.instance with engine.connect() as conn: sql = """ select r1.item_id, (ifnull(r1.total_sal,0) - ifnull(r2.total_dev,0) - ifnull(rv.vendido,0)) as tiene from (select im.item_id, m.tipo, c.nombre, sum(im.cantidad) as total_sal, m.cliente_id from movimiento as m left join items_movido as im on im.movimiento_id == m.id left join cliente as c on c.id == m.cliente_id where m.tipo = "SALIDA" and c.id == %d group by im.item_id, m.tipo,m.cliente_id) as r1 left join (select im.item_id, m.tipo, c.nombre, sum(im.cantidad)as total_dev, m.cliente_id from movimiento as m left join items_movido as im on im.movimiento_id == m.id left join cliente as c on c.id == m.cliente_id where m.tipo = "DEVOLUCION" group by im.item_id, m.tipo, m.cliente_id) as r2 on r1.item_id == r2.item_id and r1.cliente_id == r2.cliente_id left join item on item.id == r1.item_id left join (select v.item_id, v.cliente_id, sum(v.cantidad) as vendido from venta as v where v.cliente_id == %d group by v.item_id) as rv on r1.item_id == rv.item_id where tiene > 0; """ % (self.id, self.id) results = conn.execute(text(sql)) r = [] ids = [] for row in results: ids.append(row['item_id']) r.append((row['item_id'], row['tiene'])) # i = Item() # i.id = row['id'] # i.cantidad = row['cantidad'] # i.procedencia_id = row['procedencia_id'] # i.parent_id = row['parent_id'] # i.costo = row['costo'] # i.tiene = row['tiene'] # items.append(i) session = db.session() items = session.query(Item).filter(Item.id.in_(ids)).all() for i in items: for row in r: if row[0] == i.id: i.tiene = row[1] break return items
def test_db_create(self): filename = os.tmpfile() db.create_db(filename) cliente = orm.Cliente() cliente.nombre = "cliente" cliente.direccion = "somewhere" cliente.telefono = '555' cliente.tipo = orm.TipoClienteEnum.MAYORITARIO session = db.session() session.add(cliente) session.commit() #test input with sqlite conn = sqlite3.connect(filename) c = conn.cursor() result = c.execute('select * from cliente') r = result.fetchone() self.assertEqual(r[1], cliente.nombre) self.assertEqual(r[2], cliente.telefono) self.assertEqual(r[3], cliente.direccion) self.assertEqual(r[4], cliente.tipo.name)
from items_control import orm from items_control.data import db import unittest import datetime db.open_db( "/home/mbolivar/Projects/items_control/items_control/data/db.sqlite", True) session = db.session() # m = orm.Movimiento() # m.tipo = orm.TipoMovimiento.SALIDA # m.fecha = datetime.datetime.now() # # i = session.query(orm.Item).first() # # itemsmovido = orm.ItemMovido() # itemsmovido.cantidad=2 # itemsmovido.item = i # m.items.append(itemsmovido) # # session.add(m) # session.commit() client = session.query(orm.Cliente).all() i = client[0].posession_items() items = session.query(i).all() print items
def addUser(self, user): self.clientedata.append(user) session = db.session() session.add(user) session.commit()