Viewing file:  database_functions.py (10.72 KB) -rw-r--r--
Select action/file-type:
 (+) |  (+) |  (+) | Code (+) | Session (+) |  (+) | SDB (+) |  (+) |  (+) |  (+) |  (+) |  (+) |
import pymysql as mdb
import json
db_host = 'localhost'
db_user = 'db_user'
db_password = 'pacman'
db_database = 'antonella'
qr_scale = 12
def get_photo_from_product(product_name):
with mdb.connect(db_host, db_user, db_password, db_database) as con:
con.execute("SELECT imagen from productos WHERE nombre_producto='%s'" % product_name)
row = con.fetchone()
if row is not None:
return row[0]
return None
def update_product_photo(product_name, photo):
with mdb.connect(db_host, db_user, db_password, db_database) as con:
query = "UPDATE productos SET imagen='%s' WHERE nombre_producto='%s'" % (photo, product_name)
con.execute(query)
def get_qr_filename_from_product(product_name):
# return a qr filename for a product id
with mdb.connect(db_host, db_user, db_password, db_database) as con:
con.execute("SELECT codigo_qr from productos WHERE nombre_producto='%s'" % product_name)
row = con.fetchone()
if row is not None:
return row[0]
return None
def insert_product(name, description, price, seller_price, count, model, category, supplier, code, photo=None):
default_photo = '/var/www/FlaskAppTest/images/NotAvailable300.png'
status_id = 1
if photo is None:
default_photo = photo
with mdb.connect(db_host, db_user, db_password, db_database) as con:
category_id = get_category_id_from_name(category)
model_id = get_model_id_from_name(model)
supplier_id = get_supplier_id_from_name(supplier)
query = "INSERT INTO productos(nombre_producto, descripcion, numero, precio_ingreso, id_proveedor, id_categoria, id_modelo, precio_venta, imagen, id_estado) VALUES ('%s', '%s', '%s', %f, %d, %d, %d, %f, '%s', %d)" % (name, description, code, price, supplier_id, category_id, model_id, seller_price, photo, status_id)
print query
con.execute(query)
stock_query = "INSERT INTO stock_productos(nombre_producto, stock) VALUES ('%s', %d)" % (name, count)
con.execute(stock_query)
def update_product(name, description, price, seller_price, count, model, category, supplier, code, photo=None):
with mdb.connect(db_host, db_user, db_password, db_database) as con:
category_id = get_category_id_from_name(category)
model_id = get_model_id_from_name(model)
supplier_id = get_supplier_id_from_name(supplier)
query = "UPDATE productos SET descripcion='%s', numero='%s', precio_ingreso=%f, id_proveedor=%d, id_categoria=%d, id_modelo=%d, precio_venta=%f, imagen='%s' WHERE nombre_producto='%s'" % (description, code, price, supplier_id, category_id, model_id, seller_price, photo, name)
if photo is None:
query = "UPDATE productos SET descripcion='%s', numero='%s', precio_ingreso=%f, id_proveedor=%d, id_categoria=%d, id_modelo=%d, precio_venta=%f WHERE nombre_producto='%s'" % (description, code, price, supplier_id, category_id, model_id, seller_price,name)
con.execute(query)
stock_query = "UPDATE stock_productos set stock=%d WHERE nombre_producto='%s'" % (count, name)
con.execute(stock_query)
def get_all_products():
with mdb.connect(db_host, db_user, db_password, db_database) as con:
con.execute("SELECT p.nombre_producto, p.numero, p.descripcion, p.precio_ingreso, p.precio_venta, s.stock, c.categoria, m.modelo, pr.nombre, p.imagen from productos as p, stock_productos as s, categorias_productos c, modelos m, proveedores pr where p.nombre_producto = s.nombre_producto and p.id_categoria = c.id and m.id = p.id_modelo and pr.id = p.id_proveedor")
products = [dict(name=row[0], code=row[1], description=row[2], price=row[3], seller_price=row[4], stock=row[5], category=row[6], model=row[7], supplier=row[8]) for row in con.fetchall()]
return products
def get_product_by_name(product_name):
with mdb.connect(db_host, db_user, db_password, db_database) as con:
con.execute("SELECT p.nombre_producto, p.numero, p.descripcion, p.precio_ingreso, p.precio_venta, s.stock, c.categoria, m.modelo, pr.nombre from productos as p, stock_productos as s, categorias_productos c, modelos m, proveedores pr where p.nombre_producto = s.nombre_producto and p.id_categoria = c.id and m.id = p.id_modelo and pr.id = p.id_proveedor and p.nombre_producto='%s'" % product_name)
# query = "SELECT nombre_producto, descripcion, precio_ingreso, precio_venta from productos WHERE nombre_producto='%s'" % product_name
product_row = con.fetchone()
return {
'name': product_row[0], 'code': product_row[1], 'description': product_row[2],
'price' : float(product_row[3]), 'seller_price': float(product_row[4]), 'stock': product_row[5],
'category': product_row[6], 'model': product_row[7], 'supplier': product_row[8]
}
def insert_product_oder(order_id, json_products):
for product in json_products:
with mdb.connect(db_host, db_user, db_password, db_database) as con:
insert_query = 'INSERT INTO detalle_pedido(id_pedido, nombre_producto, cantidad) VALUES (%d, "%s", %d)' % (order_id, product['name'], int(product['count']))
con.execute(insert_query)
update_stock(product['name'], int(product['count']))
update_product_sell_count(product['name'], int(product['count']))
def update_stock(product_name, count):
with mdb.connect(db_host, db_user, db_password, db_database) as con:
query = 'UPDATE stock_productos SET stock=stock -%d WHERE nombre_producto="%s"'% (count, product_name)
con.execute(query)
def update_product_sell_count(product_name, count):
with mdb.connect(db_host, db_user, db_password, db_database) as con:
query = 'UPDATE productos SET cantidad_vendida=cantidad_vendida + %d WHERE nombre_producto="%s"'% (count, product_name)
con.execute(query)
def insert_order(seller_id, client_id, products):
import datetime
date = datetime.datetime.now()
total = 0.0
#json_products = json.loads(str(products))
json_products = products
for product in json_products:
print product
product_data = get_product_by_name(product['name'])
total += product_data['seller_price']*int(product['count'])
order_id = 1
with mdb.connect(db_host, db_user, db_password, db_database) as con:
query = "INSERT INTO pedidos(id_estado, fecha, id_cliente, total, vendedor) VALUES (%d, '%s', '%s', %f, '%s')" % (1, str(date), client_id, total, seller_id)
con.execute(query)
order_id = con.lastrowid
# Insert order detail
insert_product_oder(order_id, json_products)
def get_all_orders():
with mdb.connect(db_host, db_user, db_password, db_database) as con:
con.execute("SELECT id_pedido, id_estado, fecha, id_cliente, total, iva, descuento, vendedor from pedidos;")
orders = [dict(order_id=row[0], status=row[1], date=row[2].strftime("%Y-%m-%d %H:%M:%S"), client_id=row[3], total=row[4], iva=row[5], discount=row[6], seller=row[7]) for row in con.fetchall()]
return orders
####### CLIENTS
def get_all_clients():
with mdb.connect(db_host, db_user, db_password, db_database) as con:
con.execute("SELECT id, nombre, direccion, localidad, telefono, cuit, mail from clientes")
clients = [dict(id=row[0], name=row[1], address=row[2], city=row[3], phone=row[4], cuit=row[5], mail=row[6]) for row in con.fetchall()]
return clients
def insert_client(name, business_name, address, city, phone, cuit, mail, expresso):
with mdb.connect(db_host, db_user, db_password, db_database) as con:
insert_query = "INSERT INTO clientes(nombre, razon_social, direccion, localidad, telefono, nombre_comercial, cuit, mail, expreso) VALUES('%s','%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')" % (name, business_name, address, city, phone, business_name, cuit, mail, expresso)
con.execute(insert_query)
def insert_client(name, address, phone, cuit, mail):
with mdb.connect(db_host, db_user, db_password, db_database) as con:
insert_query = "INSERT INTO clientes(nombre, direccion, telefono, cuit, mail) VALUES('%s','%s', '%s', '%s', '%s')" % (name, address, phone, cuit, mail)
con.execute(insert_query)
def get_client_by_business_name(client_business_name):
with mdb.connect(db_host, db_user, db_password, db_database) as con:
query = "SELECT * from clientes WHERE razon_social='%s'" % client_business_name
con.execute(query)
row = con.fetchone()
return { 'id': row[0], 'name': row[1], 'business name': row[2], 'address' : row[3], 'city': row[4], 'phone': row[5], 'cuit': row[7]}
def get_client_by_cuit(client_cuit):
with mdb.connect(db_host, db_user, db_password, db_database) as con:
query = "SELECT id, nombre, direccion, cuit, telefono, mail from clientes WHERE cuit='%s'" % client_cuit
con.execute(query)
row = con.fetchone()
return { 'id': row[0], 'name': row[1], 'address': row[2], 'cuit': row[3], 'phone': row[4], 'mail': row[5]}
## MODELS
def get_models():
with mdb.connect(db_host, db_user, db_password, db_database) as con:
con.execute("SELECT idCategoria, modelo from modelos")
models = [dict(category_id=row[0], model=row[1]) for row in con.fetchall()]
return models
def get_model_id_from_name(model):
with mdb.connect(db_host, db_user, db_password, db_database) as con:
con.execute("SELECT id from modelos where modelo='%s'" % model)
row = con.fetchone()
if row is not None:
return row[0]
return None
## CATEGORIES
def get_categories():
with mdb.connect(db_host, db_user, db_password, db_database) as con:
con.execute("SELECT id, categoria from categorias_productos")
categories = [dict(category_id=row[0], category=row[1]) for row in con.fetchall()]
return categories
def get_category_id_from_name(category_name):
with mdb.connect(db_host, db_user, db_password, db_database) as con:
con.execute("SELECT id from categorias_productos where categoria='%s'" % category_name)
row = con.fetchone()
if row is not None:
return row[0]
return None
## SUPPLIER
def get_suppliers():
with mdb.connect(db_host, db_user, db_password, db_database) as con:
con.execute("SELECT nombre, razon_social, direccion, localidad, telefono, cuit, mail from proveedores")
suppliers = [dict(name=row[0], business_name=row[1], address=row[2], city=row[3], telephone=row[4], cuit=row[5], mail=row[6]) for row in con.fetchall()]
return suppliers
def get_supplier_id_from_name(supplier):
with mdb.connect(db_host, db_user, db_password, db_database) as con:
con.execute("SELECT id from proveedores where nombre='%s'" % supplier)
row = con.fetchone()
if row is not None:
return row[0]
return None
|