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()