def targetGetProductInventory(itemCode, datetime=None):
# Setup database connection, table, and query
con = targetDbEng.connect()
products = Table('products', MetaData(targetDbEng), autoload=True)
inventory_transactions = Table('inventory_transactions',
MetaData(targetDbEng),
autoload=True)
stm = select([
func.coalesce(
func.sum(
func.IF(
inventory_transactions.c.unit == 3,
inventory_transactions.c.amount *
products.c.units_per_case,
inventory_transactions.c.amount)), 0)
]).select_from(
inventory_transactions.join(
products,
inventory_transactions.c.item_code == products.c.item_code)).where(
inventory_transactions.c.item_code == itemCode)
if datetime:
stm = stm.where(inventory_transactions.c.creation_datetime <= datetime)
current_inventory = con.execute(stm).fetchone()
con.close()
return int(current_inventory[0])
def targetEditProduct(itemCode, data):
# Setup database connection and table
con = targetDbEng.connect()
products = Table('products', MetaData(targetDbEng), autoload=True)
# Main update statement
stm = products.update().where(products.c.item_code == itemCode)
# Check potential passed in params to update
if 'price' in data:
stm = stm.values(price=data['price'])
con.execute(stm)
con.close()
return itemCode
def targetGetUnit(unit):
# Setup database connection, table, and query
con = targetDbEng.connect()
units = Table('units', MetaData(targetDbEng), autoload=True)
stm = select([units]).where(units.c.id == unit)
items = resultSetToJson(con.execute(stm).fetchall())
con.close()
if items:
item = items[0]
else:
item = None
return item
def targetCreateInventoryTransaction(data):
# Setup database connection and table
con = targetDbEng.connect()
inventory_transactions = Table('inventory_transactions',
MetaData(targetDbEng),
autoload=True)
# Create inventory transactions
stm = inventory_transactions.insert().values(
item_code=data['item_code'],
amount=data['amount'],
unit=data['unit'],
creation_datetime=data['datetime'])
result = con.execute(stm)
con.close()
return result.lastrowid
def targetGetProducts(data):
# Setup database connection, table, and query
con = targetDbEng.connect()
products = Table('products', MetaData(targetDbEng), autoload=True)
plus = Table('plus', MetaData(targetDbEng), autoload=True)
stm = select([
products.c.item_code, products.c.name, products.c.price,
plus.c.name.label('plu'),
products.c.plu.label('plu_number'), products.c.image_url
]).select_from(products.join(plus, products.c.plu == plus.c.plu))
# Handle optional filters
if 'name' in data:
stm = stm.where(products.c.name.like('%' + data['name'] + '%'))
if 'item_code' in data:
stm = stm.where(products.c.item_code == data['item_code'])
if 'plu' in data:
stm = stm.where(products.c.plu == data['plu'])
if 'barcode' in data:
barcode = '%' + data['barcode'] + '%'
stm = stm.where(products.c.barcode.like(barcode))
if 'department' in data:
stm = stm.where(products.c.department == data['department'])
# Handle search_term
if 'search_term' in data:
search_term = '%' + data['search_term'] + '%'
stm = stm.where(
or_(products.c.name.like(search_term),
products.c.item_code.like(search_term),
products.c.plu.like(search_term)))
# Handle page_size and page
stm = setPagination(stm, data)
# Get items
items = resultSetToJson(con.execute(stm).fetchall())
con.close()
for item in items:
item['price'] = float(item['price'])
return items
def targetCreateSale(data):
# Setup database connection and table
con = targetDbEng.connect()
sales = Table('sales', MetaData(targetDbEng), autoload=True)
products = Table('products', MetaData(targetDbEng), autoload=True)
sale_items = Table('sale_items', MetaData(targetDbEng), autoload=True)
# Create sale
stm = sales.insert()
sale = con.execute(stm)
sale_id = sale.lastrowid
# Create inventory transactions and sale items
for item in data['items']:
# Get the item to figure out what unit it is sold in and what its price is
stm = select([products
]).where(products.c.item_code == item['item_code'])
item_obj = con.execute(stm).fetchone()
item['unit'] = item_obj['unit']
sale_item_price = float(item['amount']) * float(item_obj['price'])
item['amount'] = -float(item['amount'])
# Create the inventory transaction
inventory_transaction_id = targetCreateInventoryTransaction(item)
# Create the sale item
stm = sale_items.insert().values(
sale=sale_id,
inventory_transaction=inventory_transaction_id,
price=sale_item_price)
result = con.execute(stm)
con.close()
return sale_id
def targetGetProductSales(itemCode, date):
# Setup database connection, table, and query
con = targetDbEng.connect()
sale_items = Table('sale_items', MetaData(targetDbEng), autoload=True)
inventory_transactions = Table('inventory_transactions',
MetaData(targetDbEng),
autoload=True)
stm = select([
func.coalesce(func.sum(inventory_transactions.c.amount), 0)
]).select_from(
sale_items.join(
inventory_transactions,
sale_items.c.inventory_transaction == inventory_transactions.c.id)
).where(inventory_transactions.c.item_code == itemCode).where(
inventory_transactions.c.creation_datetime.like(date + '%'))
num_sales = con.execute(stm).fetchone()
con.close()
return -int(num_sales[0])
def targetGetProduct(itemCode):
# Setup database connection, table, and query
con = targetDbEng.connect()
products = Table('products', MetaData(targetDbEng), autoload=True)
departments = Table('departments', MetaData(targetDbEng), autoload=True)
units = Table('units', MetaData(targetDbEng), autoload=True)
plus = Table('plus', MetaData(targetDbEng), autoload=True)
stm = select([
products.c.item_code, products.c.name, products.c.price,
departments.c.name.label('department'),
products.c.department.label('department_number'),
plus.c.name.label('plu'),
products.c.plu.label('plu_number'), products.c.barcode,
units.c.name.label('unit'), products.c.units_per_case,
products.c.image_url
]).select_from(
products.join(departments,
products.c.department == departments.c.id).join(
units, products.c.unit == units.c.id).join(
plus, products.c.plu == plus.c.plu)).where(
products.c.item_code == itemCode)
items = resultSetToJson(con.execute(stm).fetchall())
con.close()
if items:
item = items[0]
item['units_per_case'] = int(item['units_per_case'])
item['price'] = float(item['price'])
if item['barcode'] == "None":
item['barcode'] = None
item['current_inventory'] = targetGetProductInventory(itemCode)
else:
item = None
return item
