def main_case_count_calculations(self):
"""This method calculates the entire Case Count KPIs set."""
if not (self.filtered_scif.empty or self.matches.empty):
try:
if not self.filtered_mdis.empty:
self._prepare_data_for_calculation()
self._generate_adj_graphs()
facings_res = self._calculate_display_size_facings()
sku_cases_res = self._count_number_of_cases()
unshoppable_cases_res = self._non_shoppable_case_kpi()
implied_cases_res = self._implied_shoppable_cases_kpi()
else:
facings_res = self._calculate_display_size_facings()
sku_cases_res = self._count_number_of_cases()
unshoppable_cases_res = []
implied_cases_res = []
sku_cases_res = self._remove_nonshoppable_cases_from_shoppable_cases(
sku_cases_res, unshoppable_cases_res)
total_res = self._calculate_total_cases(sku_cases_res +
implied_cases_res +
unshoppable_cases_res)
placeholder_res = self._generate_placeholder_results(
facings_res, sku_cases_res, unshoppable_cases_res,
implied_cases_res)
self._save_results_to_db(facings_res + sku_cases_res +
unshoppable_cases_res +
implied_cases_res + total_res +
placeholder_res)
self._calculate_total_score_level_res(total_res)
except Exception as err:
Log.error(
"DiageoUS Case Count calculation failed due to the following error: {}"
.format(err))
def commit_results(self, queries):
"""
This function commits the results into the DB in batches.
query_num is the number of queires that were executed in the current batch
After batch_size is reached, the function re-connects the DB and cursor.
"""
self.rds_conn.connect_rds()
cursor = self.rds_conn.db.cursor()
batch_size = 1000
query_num = 0
failed_queries = []
for query in queries:
try:
cursor.execute(query)
# print query
except Exception as e:
Log.warning(
'Committing to DB failed to due to: {}. Query: {}'.format(
e, query))
self.rds_conn.db.commit()
failed_queries.append(query)
self.rds_conn.connect_rds()
cursor = self.rds_conn.db.cursor()
continue
if query_num > batch_size:
self.rds_conn.db.commit()
self.rds_conn.connect_rds()
cursor = self.rds_conn.db.cursor()
query_num = 0
query_num += 1
self.rds_conn.db.commit()
def main_case_count_calculations(self):
"""This method calculates the entire Case Count KPIs set."""
if self.filtered_mdis.empty or self.filtered_scif.empty:
return
try:
self._prepare_data_for_calculation()
total_facings_per_brand_res = self._calculate_total_bottler_and_carton_facings(
)
self._save_results_to_db(total_facings_per_brand_res,
Ccc.TOTAL_FACINGS_KPI)
cases_per_brand_res = self._count_number_of_cases()
self._save_results_to_db(cases_per_brand_res, Ccc.CASE_COUNT_KPI)
unshoppable_brands_lst = self._non_shoppable_case_kpi()
self._save_results_to_db(unshoppable_brands_lst,
Ccc.NON_SHOPPABLE_CASES_KPI)
implied_shoppable_cases_kpi_res = self._implied_shoppable_cases_kpi(
)
self._save_results_to_db(implied_shoppable_cases_kpi_res,
Ccc.IMPLIED_SHOPPABLE_CASES_KPI)
total_cases_res = self._calculate_and_total_cases(
cases_per_brand_res + implied_shoppable_cases_kpi_res)
self._save_results_to_db(total_cases_res,
Ccc.TOTAL_CASES_KPI,
should_enter=False)
except Exception as err:
Log.error(
"DiageoUS Case Count calculation failed due to the following error: {}"
.format(err))
def p1_assortment_validator(self):
"""
This function validates the store assortment template.
It compares the OUTLET_ID (= store_number_1) and the products ean_code to the stores and products from the DB
:return: False in case of an error and True in case of a valid template
"""
raw_data = self.parse_assortment_template()
legal_template = True
invalid_inputs = {INVALID_STORES: [], INVALID_PRODUCTS: []}
valid_stores = self.store_data.loc[
self.store_data['store_number'].isin(raw_data[OUTLET_ID])]
if len(valid_stores) != len(raw_data[OUTLET_ID].unique()):
invalid_inputs[INVALID_STORES] = list(
set(raw_data[OUTLET_ID].unique()) -
set(valid_stores['store_number']))
Log.debug("The following stores don't exist in the DB: {}".format(
invalid_inputs[INVALID_STORES]))
legal_template = False
valid_product = self.all_products.loc[self.all_products[EAN_CODE].isin(
raw_data[EAN_CODE])]
if len(valid_product) != len(raw_data[EAN_CODE].unique()):
invalid_inputs[INVALID_PRODUCTS] = list(
set(raw_data[EAN_CODE].unique()) -
set(valid_product[EAN_CODE]))
Log.debug(
"The following products don't exist in the DB: {}".format(
invalid_inputs[INVALID_PRODUCTS]))
legal_template = False
return legal_template, invalid_inputs
def menu_count(self):
kpi_fk = self.get_kpi_fk_by_kpi_type(Consts.MENU_KPI_CHILD)
parent_kpi = self.get_kpi_fk_by_kpi_type(Consts.TOTAL_MENU_KPI_SCORE)
# we need to save a second set of KPIs with heirarchy for the mobile report
kpi_fk_mr = self.get_kpi_fk_by_kpi_type(Consts.MENU_KPI_CHILD_MR)
parent_kpi_mr = self.get_kpi_fk_by_kpi_type(Consts.TOTAL_MENU_KPI_SCORE_MR)
if self.targets.empty:
return
try:
menu_product_fks = [t for t in self.targets.product_fk.unique().tolist() if pd.notna(t)]
except AttributeError:
Log.warning('Menu Count targets are corrupt for this store')
return
filtered_scif = self.scif[self.scif['template_group'].str.contains('Menu')]
present_menu_scif_sub_brands = filtered_scif.sub_brand.unique().tolist()
passed_products = 0
for product_fk in menu_product_fks:
result = 0
sub_brand = self.all_products['sub_brand'][self.all_products['product_fk'] == product_fk].iloc[0]
custom_entity_df = self.custom_entity['pk'][self.custom_entity['name'] == sub_brand]
if custom_entity_df.empty:
custom_entity_pk = -1
else:
custom_entity_pk = custom_entity_df.iloc[0]
if sub_brand in present_menu_scif_sub_brands:
result = 1
passed_products += 1
self.write_to_db(fk=kpi_fk_mr, numerator_id=product_fk, numerator_result=0, denominator_result=0,
denominator_id=custom_entity_pk,
result=result, score=0, identifier_parent=parent_kpi_mr, identifier_result=kpi_fk_mr,
should_enter=True)
self.write_to_db(fk=kpi_fk, numerator_id=product_fk, numerator_result=0, denominator_result=0,
denominator_id=custom_entity_pk, result=result, score=0)
target_products = len(menu_product_fks)
self.write_to_db(fk=parent_kpi_mr, numerator_id=self.manufacturer_fk, numerator_result=0, denominator_result=0,
denominator_id=self.store_id,
result=passed_products, score=0, target=target_products, identifier_result=parent_kpi_mr)
self.write_to_db(fk=parent_kpi, numerator_id=self.manufacturer_fk, numerator_result=0, denominator_result=0,
denominator_id=self.store_id,
result=passed_products, score=0, target=target_products)
def get_closest_point(origin_point, other_points_df):
"""This method gets a point (x & y coordinates) and checks what is the closet point the could be
found in the DataFrame that is being received.
@param: origin_point (tuple): coordinates of x and y
@param: other_points_df (DataFrame): A DF that has rect_x' and 'rect_y' columns
@return: A DataFrame with the closest points and the rest of the data
"""
other_points = other_points_df[['rect_x', 'rect_y']].values
# Euclidean geometry magic
distances = np.sum((other_points - origin_point) ** 2, axis=1)
# get the shortest hypotenuse
try:
closest_point = other_points[np.argmin(distances)]
except ValueError:
Log.error('Unable to find a matching opposite point for supplied anchor!')
return other_points_df
return other_points_df[
(other_points_df['rect_x'] == closest_point[0]) & (other_points_df['rect_y'] == closest_point[1])]
def set_end_date_for_irrelevant_assortments(self, stores_list):
"""
This function sets an end_date to all of the irrelevant stores in the assortment.
:param stores_list: List of the stores from the assortment template
"""
Log.debug("Closing assortment for stores out of template")
irrelevant_stores = self.store_data.loc[
~self.store_data['store_number'].
isin(stores_list)]['store_fk'].unique().tolist()
current_assortment_stores = self.current_top_skus['store_fk'].unique(
).tolist()
stores_to_remove = list(
set(irrelevant_stores).intersection(
set(current_assortment_stores)))
for store in stores_to_remove:
query = [
self.get_store_deactivation_query(store, self.deactivate_date)
]
self.commit_results(query)
Log.debug("Assortment is closed for ({}) stores".format(
len(stores_to_remove)))
def upload_store_assortment_file(self):
raw_data = self.parse_assortment_template()
data = []
list_of_stores = raw_data[OUTLET_ID].unique().tolist()
if not self.partial_update:
self.set_end_date_for_irrelevant_assortments(list_of_stores)
Log.debug("Preparing assortment data for update")
store_counter = 0
for store in list_of_stores:
store_data = {}
store_products = raw_data.loc[raw_data[OUTLET_ID] ==
store][EAN_CODE].tolist()
store_data[store] = store_products
data.append(store_data)
store_counter += 1
if store_counter % 1000 == 0 or store_counter == len(
list_of_stores):
Log.debug("Assortment is prepared for {}/{} stores".format(
store_counter, len(list_of_stores)))
Log.debug("Updating assortment data in DB")
store_counter = 0
for store_data in data:
self.update_db_from_json(store_data)
if self.all_queries:
queries = self.merge_insert_queries(self.all_queries)
self.commit_results(queries)
self.all_queries = []
store_counter += 1
if store_counter % 1000 == 0 or store_counter == len(data):
Log.debug(
"Assortment is updated in DB for {}/{} stores".format(
store_counter, len(data)))
def upload_assortment(self):
"""
This is the main function of the assortment.
It does the validation and then upload the assortment.
:return:
"""
Log.debug("Parsing and validating the assortment template")
is_valid, invalid_inputs = self.p1_assortment_validator()
Log.info("Assortment upload is started")
self.upload_store_assortment_file()
if not is_valid:
Log.warning("Errors were found during the template validation")
if invalid_inputs[INVALID_STORES]:
Log.warning("The following stores don't exist in the DB: {}"
"".format(invalid_inputs[INVALID_STORES]))
if invalid_inputs[INVALID_PRODUCTS]:
Log.warning("The following products don't exist in the DB: {}"
"".format(invalid_inputs[INVALID_PRODUCTS]))
Log.info("Assortment upload is finished")
def update_db_from_json(self, data):
update_products = set()
missing_products = set()
store_number = data.keys()[0]
if store_number is None:
Log.debug("'{}' column or value is missing".format(STORE_NUMBER))
return
store_fk = self.get_store_fk(store_number)
if store_fk is None:
Log.debug(
'Store Number {} does not exist in DB'.format(store_number))
return
for key in data[store_number]:
validation = False
if isinstance(key, (float, int)):
validation = True
elif isinstance(key, (str, unicode)):
validation = True
if validation:
product_ean_code = str(key).split(',')[-1]
product_fk = self.get_product_fk(product_ean_code)
if product_fk is None:
missing_products.add(product_ean_code)
else:
update_products.add(product_fk)
if missing_products:
Log.debug(
'The following EAN Codes for Store Number {} do not exist in DB: {}.'
''.format(store_number, list(missing_products)))
queries = []
current_products = self.current_top_skus[
self.current_top_skus['store_fk'] ==
store_fk]['product_fk'].tolist()
products_to_deactivate = tuple(
set(current_products).difference(update_products))
products_to_activate = tuple(
set(update_products).difference(current_products))
if products_to_deactivate:
if len(products_to_deactivate) == 1:
queries.append(
self.get_deactivation_query(
store_fk, "(" + str(products_to_deactivate[0]) + ")",
self.deactivate_date))
else:
queries.append(
self.get_deactivation_query(store_fk,
tuple(products_to_deactivate),
self.deactivate_date))
for product_fk in products_to_activate:
queries.append(
self.get_activation_query(store_fk, product_fk,
self.activate_date))
self.all_queries.extend(queries)
Log.debug(
'Store Number {} - Products to update {}: Deactivated {}, Activated {}'
''.format(store_number, len(update_products),
len(products_to_deactivate), len(products_to_activate)))
