Viewing file:  database_functions.py (3.86 KB) -rw-rw-r--
Select action/file-type:
 (+) |  (+) |  (+) | Code (+) | Session (+) |  (+) | SDB (+) |  (+) |  (+) |  (+) |  (+) |  (+) |
import pymysql as mdb
import json
db_host = 'localhost'
db_user = 'root'
db_password = '123456789'
db_database = 'pedidos'
qr_store_folder = '/var/www/FlaskApp/qrcodes'
qr_scale = 12
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='%s'" % product_name)
row = con.fetchone()
if row is not None:
return row[0]
return None
def create_qr_for_new_product(product_name):
import pyqrcode
with mdb.connect(db_host, db_user, db_password, db_database) as con:
qr = pyqrcode.create('%s' % product_name)
qr_filename = '%s/%s.png' % (qr_store_folder, product_name)
qr.png(qr_filename, scale=qr_scale, module_color=[0, 0, 0, 128], background=[0xff, 0xff, 0xcc])
return qr_filename
def insert_product(name, description, price):
qr_code = create_qr_for_new_product(name)
query = "INSERT INTO productos(nombre, descripcion, precio, codigo_qr) VALUES ('%s', '%s', %f, '%s')" % (name, description, price, qr_code)
with mdb.connect(db_host, db_user, db_password, db_database) as con:
con.execute(query)
stock_query = "INSERT INTO stock_productos(nombre_producto, stock) VALUES ('%s', %d)" % (name, 0)
con.execute(stock_query)
def get_all_products():
with mdb.connect(db_host, db_user, db_password, db_database) as con:
con.execute("SELECT * from productos")
products = [dict(name=row[0], description=row[1], price=row[2], qr=row[3]) 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:
query = "SELECT * from productos WHERE nombre='%s'" % product_name
con.execute(query)
product_row = con.fetchone()
return { 'name': product_row[0], 'description': product_row[1], 'price' : float(product_row[2]) }
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']))
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 insert_order(seller_id, client_id, iva, discount, products):
import datetime
date = datetime.datetime.now()
total = 0.0
json_products = json.loads(str(products))
for product in json_products:
# get product info
product_data = get_product_by_name(product['name'])
total += (product_data['price']*(1 + float(iva)) - float(discount)*product_data['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, iva, descuento, vendedor) VALUES (%d, '%s', '%s', %f, %f, %f, '%s')" % (1, str(date), client_id, total, float(iva), float(discount), seller_id)
con.execute(query)
order_id = con.lastrowid
# Insert order detail
insert_product_oder(order_id, json_products)
####### CLIENTS
def get_all_clients():
with mdb.connect(db_host, db_user, db_password, db_database) as con:
con.execute("SELECT * from clientes")
products = [dict(id=row[0], name=row[1], business_name=row[2], address=row[3], telephone=row[4], cuit=row[5], city=row[6], iva=row[7], mail=row[8]) for row in con.fetchall()]
return products
|