def save(self):
""" Class method to save Item record in DB
"""
logger.info("Saving Item...")
# Verify for update
if self.item_uuid:
if not Item.exists({'item_uuid': self.item_uuid}):
# If wants to update but wrong UUID, return Error
raise errors.ApiError(70006, "Cannot update, UUID not in DB!")
# Verify for insert
elif Item.exists({'gtin': self.gtin}):
self.message = 'Item already exists!'
self.item_uuid = Item.get(self.gtin)[0]['item_uuid']
return True
# Load model
m_item = g._db.model('item', 'item_uuid')
if self.item_uuid:
m_item.item_uuid = self.item_uuid
m_item.gtin = self.gtin
try:
m_item.checksum = int(self.gtin[-1])
except:
m_item.checksum = None
m_item.name = self.name
m_item.description = self.description
m_item.last_modified = str(datetime.datetime.utcnow())
try:
self.message = "Correctly {} Item!".format('updated' \
if self.item_uuid else 'stored')
m_item.save()
self.item_uuid = m_item.last_id
logger.info(self.message)
except Exception as e:
logger.error(e)
raise errors.ApiError(70002, "Issues saving in DB!")
def get(_val, by='gtin', _cols=['item_uuid'], limit=None):
""" Static method to get Item info
Params:
-----
_val : str
Value to query from
by : str
Column to query in
_cols : list
Columns to retrieve
limit : int
Elements to limit query
Returns:
-----
_items : list
List of elements
"""
_cols = ','.join(_cols) if _cols else 'item_uuid'
_query = "SELECT {} FROM item WHERE {} IN {}" \
.format(_cols, by, tuplify(_val))
if limit:
_query += ' LIMIT {}'.format(limit)
logger.debug(_query)
try:
_items = g._db.query(_query).fetch()
logger.debug("Got {} items".format(len(_items)))
except Exception as e:
logger.error(e)
raise errors.ApiError(70003, "Issues fetching elements in DB")
return _items
def it_list(**kwargs):
""" Static method to get all items by query
Params:
-----
kwargs : dict
Extra arguments, such as (p, ipp, cols, etc..)
Returns:
-----
_resp : list
List of product objects
"""
logger.debug('Querying items')
logger.debug('fetching: {}'.format(kwargs))
# Format columns
if kwargs['cols']:
_cols = ','.join([x for x in \
(Item.__base_q \
+ kwargs['cols'].split(',')) \
if x in Item.__attrs__])
else:
_cols = ','.join([x for x in Item.__base_q])
# Format querying keys
_keys = 'WHERE item_uuid IS NOT NULL'
# Format paginators
_p = int(kwargs['p'])
if _p < 1:
_p = 1
_ipp = int(kwargs['ipp'])
if _ipp > 10000:
_ipp = 10000
# Order by statement
if 'orderby' in kwargs:
_orderby = kwargs['orderby'] if kwargs['orderby'] else 'item_uuid'
else:
_orderby = 'item_uuid'
if _orderby not in Item.__base_q:
_orderby = 'item_uuid'
# Build query
_qry = """SELECT {} FROM item {} ORDER BY {} OFFSET {} LIMIT {} """\
.format(_cols, _keys, _orderby, (_p - 1)*_ipp, _ipp)
logger.debug(_qry)
# Query DB
try:
_resp = g._db.query(_qry).fetch()
logger.info("Found {} items".format(len(_resp)))
except Exception as e:
logger.error(e)
logger.warning("Issues fetching elements in DB!")
if APP_MODE == "CONSUMER":
return False
if APP_MODE == "SERVICE":
raise errors.ApiError(70003, "Issues fetching elements in DB")
return _resp
def it_count():
""" Static method to count existent Items
"""
_keys = 'WHERE item_uuid IS NOT NULL'
# Build query
_qry = """SELECT COUNT(*) as n_items FROM item {} """\
.format(_keys)
logger.debug(_qry)
# Query DB
try:
_resp = g._db.query(_qry).fetch()
logger.info("Found {} items".format(_resp[0]['n_items']))
except Exception as e:
logger.error(e)
logger.warning("Issues fetching elements in DB!")
if APP_MODE == "CONSUMER":
return False
if APP_MODE == "SERVICE":
raise errors.ApiError(70003, "Issues fetching elements in DB")
return _resp[0]['n_items']
def delete(i_uuid):
""" Static method to delete Item
Params:
-----
i_uuid : str
Item UUID to delete
Returns:
-----
resp : bool
Transaction status
"""
logger.debug("Deleting Item...")
if not Item.exists({'item_uuid': i_uuid}):
return {'message': "Item UUID not in DB!"}
try:
g._db.query("DELETE FROM item WHERE item_uuid='{}'" \
.format(i_uuid))
except Exception as e:
logger.error(e)
raise errors.ApiError(70004, "Could not apply transaction in DB")
return {'message': "Item ({}) correctly deleted!".format(i_uuid)}
def query(_by, **kwargs):
""" Static method to query by defined column values
Params:
-----
_by : str
Key from which query is performed
kwargs : dict
Extra arguments, such as (p, ipp, cols, etc..)
Returns:
-----
_resp : list
List of product objects
"""
logger.debug('Querying by: {}'.format(_by))
logger.debug('fetching: {}'.format(kwargs))
# Format columns
if kwargs['cols']:
_cols = ','.join([x for x in \
(kwargs['cols'].split(',') \
+ Item.__base_q) \
if x in Item.__attrs__])
else:
_cols = ','.join([x for x in Item.__base_q])
# Format querying keys
if kwargs['keys']:
_keys = 'WHERE ' + _by + ' IN ' + str(tuplify(kwargs['keys']))
else:
if _by != 'item_uuid':
_keys = 'WHERE {} IS NULL'.format(_by)
else:
_keys = ''
# Format paginators
_p = int(kwargs['p'])
if _p < 1:
_p = 1
_ipp = int(kwargs['ipp'])
# if _ipp > 10000:
# _ipp = 10000
# Order by statement
if 'orderby' in kwargs:
_orderby = kwargs['orderby'] if kwargs['orderby'] else 'item_uuid'
else:
_orderby = 'item_uuid'
if _orderby not in Item.__base_q:
_orderby = 'item_uuid'
# Build query
_qry = """SELECT {} FROM item {} ORDER BY {} OFFSET {} LIMIT {} """\
.format(_cols, _keys, _orderby, (_p - 1)*_ipp, _ipp)
logger.debug(_qry)
# Query DB
try:
_resp = g._db.query(_qry).fetch()
logger.info("Found {} items".format(len(_resp)))
except Exception as e:
logger.error(e)
logger.warning("Issues fetching elements in DB!")
if APP_MODE == "CONSUMER":
return False
if APP_MODE == "SERVICE":
raise errors.ApiError(70003, "Issues fetching elements in DB")
return _resp
def details(u_type, _uuid):
""" Method to query details from related Product or Item
Params:
-----
u_type : str
`item_uuid` or `product_uuid`
_uuid : str
UUID to query info
Returns:
-----
_details : dict
Product/Item Details
"""
item_uuid = None
# Fetch info from all retailers
try:
if u_type == 'item_uuid':
_qry = """SELECT i.name, i.gtin, i.description,
p.product_uuid, p.item_uuid as item_uuid,
p.images, p.ingredients, p.source,
s.hierarchy, s.retailer as show_label, s.name as r_name
FROM product p
INNER JOIN source s
ON (p.source = s.key)
INNER JOIN item i
ON (i.item_uuid = p.item_uuid)
WHERE p.{} = '{}';
""".format(u_type, _uuid)
else:
_qry = """SELECT p.name, p.gtin, p.description,
p.product_uuid,
p.images, p.ingredients, p.source,
s.hierarchy, s.retailer as show_label,
s.name as r_name, p.item_uuid as item_uuid
FROM product p
INNER JOIN source s
ON (p.source = s.key)
WHERE p.{} = '{}';
""".format(u_type, _uuid)
logger.debug(_qry)
info_rets = pd.read_sql(_qry, g._db.conn).to_dict(orient='records')
logger.debug(info_rets)
item_uuid = info_rets[0].get('item_uuid')
if not info_rets:
raise errors.ApiError(70008, "Not existing elements in DB!")
except Exception as e:
logger.error(e)
logger.warning("Issues fetching retailers info: {}".format(_uuid))
info_rets = []
# Fetch Attributes
if info_rets:
info_attrs = Item.fetch_attrs(
[str(z['product_uuid']) for z in info_rets])
else:
info_attrs = []
# Aux vars
prov, brand = {'name': '', 'key': ''}, {'name': '', 'key': ''}
ingreds, attrs, categs = [], [], []
ingred_options, categ_options = [], []
# Fetch Provider, Brand, Categories
for k in info_attrs:
if k['class_key'] == 'provider':
if len(k['attr']) > len(prov['name']):
prov = {'name': k['attr'], 'key': k['attr_key']}
elif k['class_key'] == 'brand':
if k['source'] == 'byprice':
brand = {'name': k['attr'], 'key': k['attr_key']}
elif k['class_key'] == 'ingredient':
if k['source'] == 'byprice':
ingreds.append(k['attr'])
else:
ingred_options.append(k['attr'])
elif k['class_key'] == 'category':
if k['source'] == 'byprice':
categs.append(k['attr'])
else:
categ_options.append(k['attr'])
else:
attrs.append(k)
# Fetch Attributes
if info_rets:
categs += Item.fetch_categs(
[str(z['product_uuid']) for z in info_rets])
# Add Normalized Ingredietns
_normalized_attrs = Item.fetch_normalized_attrs(u_type, _uuid)
ingreds += _normalized_attrs['ingredients']
if _normalized_attrs['brand']:
brand = _normalized_attrs['brand']
# Filter info from no valid retailers
df_rets = pd.DataFrame(info_rets)
if 'source' in df_rets.columns:
df_rets = df_rets[~df_rets.source.
isin(['ims', 'plm', 'gs1', 'nielsen'])]
# df_rets = df_rets[df_rets.show_label == 1]
if df_rets.empty:
raise errors.ApiError(70003, "Issues fetching elements in DB", 404)
data = {
'name': sorted(df_rets['name'].tolist(),
key=lambda x: len(x) if x else 0,
reverse=True)[0].strip().capitalize(),
u_type: _uuid,
'names': df_rets['name'].tolist(),
'description': sorted(df_rets['description'].dropna().tolist(),
key=lambda x: len(x) if x else 0, reverse=True),
'gtin': sorted(df_rets['gtin'].dropna().tolist(),
key=lambda x: len(x) if x else 0)[0] \
if len(df_rets['gtin'].dropna()) > 0 \
else '',
'retailers': df_rets['r_name'].tolist(),
'attributes': attrs,
'ingredients': ingreds,
'ingredients_options': ingred_options,
'brand': brand,
'provider': prov,
'categories': categs,
'categories_options': categ_options
}
if item_uuid:
data.update({'item_uuid': item_uuid})
return data