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 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 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
def getUsers():
response = Response()
# Ensure user has permission for this endpoint
userId = authenticateRequest(response, request, mustBeManager=True)
if response.hasError(): return response.getJson()
# Ensure required input parameters are received
required = []
optional = [
'username', 'first_name', 'last_name', 'search_term', 'type',
'page_size', 'page'
]
data = checkVars(response, request.values.to_dict(), required, optional)
if response.hasError(): return response.getJson()
# Setup database connection and table
con = mimsDbEng.connect()
users = Table('users', MetaData(mimsDbEng), autoload=True)
# Get business of user making request
stm = select([users]).where(users.c.id == userId)
userBusiness = con.execute(stm).fetchone()['business']
# Setup main select statement
stm = select([users]).where(
and_(users.c.business == userBusiness, users.c.is_deleted == 0))
# Handle optional filters
if 'username' in data:
stm = stm.where(users.c.username.like('%' + data['username'] + '%'))
if 'first_name' in data:
stm = stm.where(users.c.first_name.like('%' + data['first_name'] +
'%'))
if 'last_name' in data:
stm = stm.where(users.c.last_name.like('%' + data['last_name'] + '%'))
if 'type' in data:
stm = stm.where(users.c.type == data['type'])
# Handle search_term
if 'search_term' in data:
search_term = '%' + data['search_term'] + '%'
stm = stm.where(
or_(
users.c.first_name.like(search_term),
users.c.last_name.like(search_term),
users.c.username.like(search_term),
func.concat(users.c.first_name, ' ',
users.c.last_name).like(search_term)))
# Handle page_size and page
stm = setPagination(stm, data)
response.data['users'] = resultSetToJson(
con.execute(stm).fetchall(), [
'password_hash', 'business', 'is_deleted', 'updated_datetime',
'creation_datetime'
])
con.close()
for item in response.data['users']:
item['id'] = int(item['id'])
item['type'] = int(item['type'])
return response.getJson()