Viewing file:  database_functions.py (5.97 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 = 'almazen_test'
qr_store_folder = './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 nombre, descripcion, precio, codigo_qr, numero from productos")
products = [dict(name=row[0], description=row[1], price=row[2], qr=row[3], code=row[4]) 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 nombre, descripcion, precio, numero 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]), 'code': product_row[3] }
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:
product_data = get_product_by_name(product['name'])
seller_price = product_data['price']
insert_query = 'INSERT INTO detalle_pedido(id_pedido, nombre_producto, cantidad, precio_venta) VALUES (%d, "%s", %d, %f)' % (order_id, product['name'], int(product['count']), seller_price)
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, products, date):
import datetime
iva = 1.21
subtotal = 0.0
total = 0.0
#json_products = json.loads(str(products))
json_products = products
print 'products %s' % str(json_products)
for product in json_products:
# get product info
product_data = get_product_by_name(product['name'])
subtotal += product_data['price']*product['count']
total += product_data['price']*product['count']*iva
print 'total=%f, subtotal=%f' % (total, subtotal)
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, cotizador, descuento, subtotal) VALUES (%d, '%s', '%s', %f, '%s', '%s', 0.0, %f)" % (1, str(date), client_id, total, seller_id, seller_id, subtotal)
con.execute(query)
order_id = con.lastrowid
# Insert order detail
insert_product_oder(order_id, json_products)
def insert_order_client_name(seller_id, client_name, products, date):
client = get_client_by_business_name(client_name)
#TODO: Ver si no existe cliente darlo de alta
insert_order(seller_id, client['id'], products, date)
####### CLIENTS
def get_all_clients():
with mdb.connect(host=db_host, user=db_user, passwd=db_password, db=db_database, use_unicode=True) as con:
con.execute("SELECT id, nombre, razon_social, direccion, localidad, telefono, cuit, mail, provincia, expreso from clientes")
clients = [dict(id=row[0], name=row[1], business_name=row[2], address=row[3], city=row[4], telephone=row[5], cuit=row[6], mail=row[7], province=row[8], expresso=row[9]) for row in con.fetchall()]
return clients
def insert_client(name, business_name, address, city, phone, cuit, mail, expresso, province):
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, provincia) VALUES('%s','%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')" % (name, business_name, address, city, phone, business_name, cuit, mail, expresso, province)
con.execute(insert_query)
return {'id' : con.lastrowid, 'business_name': business_name}
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' or nombre='%s'" % (client_business_name, 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 * from clientes WHERE cuit='%s'" % client_cuit
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]}
|