def update_idis(dd: DregData, dreg_id_list: list, config: dict):
idis = IdisWebController(portal_url=config['portal_url'],
idis_url=config['idis_url'],
login=config['login'],
password=config['password'],
browser=config['browser'],
browser_binary=config['browser_binary'],
profile_path=config['profile_path'],
driver_exe_path=config['driver_exe_path'])
for dreg_id in dreg_id_list:
mylog.info("Start update: Dreg ID = {0}".format(dreg_id))
new_status = dd.get_action_value(dreg_id)
if new_status != 'Approved' and new_status != 'Rejected':
mylog.info(
"New Status = {0} - Skipping the DREG".format(new_status))
continue
reason = dd.get_reason_value(dreg_id)
approver = 'Vasily Basov'
category = dd.get_category_by_id(dreg_id)
if category == '':
category = 'Demand Creation'
res = idis.update_dreg(dreg_id, new_status, reason, approver, category)
dd.set_idis_result(dreg_id, res)
mylog.info("{0} finished with result: {1}".format(dreg_id, res))
def get_intersection_in_cust_and_subcon(self,
customer,
subcon,
exclude_disti,
dd: DregData,
lookup_id_list=None):
output = set(
dd.id_list_filter(core_cust=customer,
exclude_disti=exclude_disti,
lookup_id_list=lookup_id_list))
output.update(
dd.id_list_filter(core_subcon=customer,
exclude_disti=exclude_disti,
lookup_id_list=lookup_id_list))
if len(subcon) > 0:
output.update(
dd.id_list_filter(core_cust=subcon,
exclude_disti=exclude_disti,
lookup_id_list=lookup_id_list))
output.update(
dd.id_list_filter(core_subcon=subcon,
exclude_disti=exclude_disti,
lookup_id_list=lookup_id_list))
return list(output)
def process_duplication_check(self, dd: DregData):
id_list = dd.id_list_by_status(Status.APPROVED)
print("Duplication check {0} lines".format(len(id_list)))
for i, dreg_id in enumerate(id_list):
if len(dd.get_core_customer_by_id(dreg_id)) > 1:
self.process_dreg_by_id(dd, dreg_id)
print("{0} {1}".format(i, dreg_id), end='\r')
print("Duplication check done!")
def process_all_new(self, dd: DregData):
dreg_id_status_new = dd.id_list_status_new()
dreg_id_status_pending = dd.id_list_status_pending()
print("{0} NEW design registrations".format(len(dreg_id_status_new)))
print("{0} PENDING design registrations".format(
len(dreg_id_status_pending)))
self.process_dregs_id_list(dd,
dreg_id_status_new + dreg_id_status_pending)
return
def format_comment(self, say, dreg_id_list, dd: DregData):
def is_not_approved(st):
if not st == Status.APPROVED:
return ' ' + st
else:
return ''
if len(dreg_id_list) > 0:
output = say + ' '
for dreg_id in dreg_id_list:
output += '{0}-{1}:{2}{3}; '.format(
dd.get_disti_by_id(dreg_id)[:3], dreg_id,
dd.get_date_by_id(dreg_id),
is_not_approved(dd.get_status_by_id(dreg_id)))
return output
def save_dreg(self, file_name, dd: DregData):
print('Formatting and saving DREG results...')
writer = pd.ExcelWriter(file_name, engine='xlsxwriter')
dd.get_dreg_data().to_excel(writer, index=False, sheet_name='DREGs')
workbook = writer.book
worksheet = writer.sheets['DREGs']
key_names = ['bg_color', 'font_color', 'bold', 'italic']
wb_format_dict = dict()
wb_format_dict.update(
{tuple([False] * 4):
None}) # default cell format: all val = False => no format
print('{0} lines in total...'.format(len(dd.id_list_all)))
for row_count, dreg_id in enumerate(dd.id_list_all):
f_options = [False] * 4 # [bg_color, font_color, bold, italic]
for key in self.row_rules:
val = dd.get_value_from_col_by_id(key, dreg_id)
if val in self.row_rules[key]:
if 'bg_color' in self.row_rules[key][val]:
f_options[0] = self.row_rules[key][val]['bg_color']
if 'font_color' in self.row_rules[key][val]:
f_options[1] = self.row_rules[key][val]['font_color']
if 'bold' in self.row_rules[key][val]:
f_options[2] = self.row_rules[key][val]['bold']
if 'italic' in self.row_rules[key][val]:
f_options[3] = self.row_rules[key][val]['italic']
if tuple(f_options) not in wb_format_dict:
new_wb_format = workbook.add_format(
dict(zip(key_names, f_options)))
wb_format_dict.update({tuple(f_options): new_wb_format})
print('Done {0} lines'.format(row_count + 1), end='\r')
worksheet.set_row(row_count + 1,
cell_format=wb_format_dict[tuple(f_options)])
writer.save()
print('Done! Result saved to {0}'.format(file_name))
return
def test_update(self):
init_df = pd.read_excel(
os.path.join("Tests", "class_DregData", "Data",
"test_update_initial_df.xlsx"))
upd_df = pd.read_excel(
os.path.join("Tests", "class_DregData", "Data",
"test_update_upd_df.xlsx"))
# synonyms_df = pd.read_excel(os.path.join("Tests", "class_DregData", "Data", 'synonyms.xlsx'))
# core_part_name_df = pd.read_excel(os.path.join("Tests", "class_DregData", "Data", "coreproduct.xlsx"))
# core_part_name_df = core_part_name_df[['Type', 'Core Product']]
# core_part_name_dict = core_part_name_df.set_index('Type')['Core Product'].to_dict()
init_dd = DregData(init_df, add_working_columns=False)
upd_df = DregData(upd_df, add_working_columns=False)
init_dd.update(upd_df)
# result_df = init_dd.get_dreg_data()
# writer = pd.ExcelWriter(os.path.join("Tests", "class_DregData", "Data", "update_result_df.xlsx"),
# engine='xlsxwriter')
# result_df.to_excel(writer, index=False)
# writer.save()
self.assertEqual(len(init_dd.id_list_all), 10)
self.assertEqual(len(init_dd.column_list_all), 23)
self.assertEqual(init_dd.get_orig_customer_by_id('20010744'),
'IZEVSKIY RADIOZAVOD')
self.assertEqual(init_dd.get_orig_part_num_by_id('20013667'),
'FD650R17IE4')
self.assertEqual(str(init_dd.latest_dreg_date), "2011-03-02")
self.assertEqual(str(init_dd.earliest_dreg_date), "2008-12-15")
return
def test_id_list_by_value_in_col(self):
init_df = pd.read_excel(
os.path.join("Tests", "class_DregData", "Data",
"test_id_list_by_val_in_col_df.xlsx"))
init_dd = DregData(init_df, add_working_columns=False)
lst1 = init_dd.id_list_by_value_in_col(
ColName.ORIGINAL_SUBCON_NAME, "EFO OOO",
['20009703', '20010744', '20012702', '20012849', '20013651'])
lst2 = init_dd.id_list_exclude_value_in_col(
ColName.ORIGINAL_SUBCON_NAME, "EFO OOO",
['20009703', '20010744', '20012702', '20012849', '20013651'])
self.assertEqual(set(lst1), {'20012849', '20013651'})
self.assertEqual(set(lst2), {'20009703', '20010744', '20012702'})
return
def run_idis_clicker(dd: DregData, dreg_ids_with_action: list):
ic = IdisClicker()
restart_browser = True
for dreg_id in dreg_ids_with_action:
if restart_browser:
ic.start_browser()
restart_browser = False
idis_success = True
if dd.is_action_approve(dreg_id):
idis_success = ic.approve_DREG_by_id(str(dreg_id))
dd.set_idis_result(dreg_id, idis_success)
elif dd.is_action_reject(
dreg_id
) and dd.is_rejection_reason_already_registered_for_other(dreg_id):
idis_success = ic.reject_DREG_by_id(str(dreg_id))
dd.set_idis_result(dreg_id, idis_success)
if not idis_success:
print("restart!!!")
ic.try_to_close_current_dreg_page()
ic.shutdown_browser()
restart_browser = True
dreg_df = dd.get_dreg_data()
output_file_name = wrk_file(FN.DATA_FOLDER,
"last_clicker_result.xlsx",
is_timestamp=True)
writer = pd.ExcelWriter(output_file_name, engine='xlsxwriter')
dreg_df.to_excel(writer, index=False)
writer.save()
dreg_df = dd.get_dreg_data()
output_file_name = wrk_file(FN.DATA_FOLDER,
"clicker_result.xlsx",
is_timestamp=True)
writer = pd.ExcelWriter(output_file_name, engine='xlsxwriter')
dreg_df.to_excel(writer, index=False)
writer.save()
return
def test_dreg_writer():
df = pd.read_excel(
os.path.join("Tests", "class_DregWriter", "Data",
"test_dregwriter.xlsx"))
dd = DregData(df, add_working_columns=False)
writer = DregWriter()
writer.set_default_rules()
writer.save_dreg(os.path.join("Tests", "output", "res.xlsx"), dd)
return
def main():
while True:
print("0 - Show working files info")
print("1 - Make ''look like'' file")
print("2 - Make synonym file from alias file and lookslike file")
print("3 - Process DREGs")
print("4 - Run Clicker")
print("5 - Run clicker for failed lines")
print("6 - Update dreg file")
print("7 - update one DREG")
answer = input("->")
if answer == '0':
print_file_info(os.path.join(FN.DATA_FOLDER, 'exportdreg.xlsx'),
Modes.DREG_FILE)
print_file_info(os.path.join(FN.DATA_FOLDER, 'updatedreg.xlsx'),
Modes.DREG_FILE)
print_file_info(os.path.join(FN.DATA_FOLDER, 'lookslike.xlsx'))
print_file_info(os.path.join(FN.DATA_FOLDER, 'alias.xlsx'))
print_file_info(os.path.join(FN.DATA_FOLDER, 'alias.xlsx'))
elif answer == '1':
#print("Max difference between words in % [0..100]?")
#answer = input("->")
answer = 0.35
lookslike_df = pd.read_excel(
wrk_file(FN.DATA_FOLDER, 'lookslike.xlsx'))
alias_df = pd.read_excel(wrk_file(FN.DATA_FOLDER, 'alias.xlsx'))
dreg_df = pd.read_excel(wrk_file(FN.DATA_FOLDER,
'exportdreg.xlsx'))
all_customer_names = DregData.customer_name_list_all(dreg_df)
all_customers_status_new = DregData.customer_name_list_status_new(
dreg_df)
lookslike_df = ccm.process_lookslike(lookslike_df,
all_customer_names,
all_customers_status_new,
float(answer) / 100, alias_df)
output_file_name = wrk_file(FN.DATA_FOLDER,
'lookslike.xlsx',
is_timestamp=True)
writer = pd.ExcelWriter(output_file_name, engine='xlsxwriter')
lookslike_df.to_excel(writer, index=False)
writer.save()
print(
"Open lookslike_XXX.xlsx; fix question marks and save as lookslike.xlsx"
)
elif answer == "2":
lookslike_df = pd.read_excel(
wrk_file(FN.DATA_FOLDER, 'lookslike.xlsx'))
alias_df = pd.read_excel(wrk_file(FN.DATA_FOLDER, 'alias.xlsx'))
#try:
synonyms_df = ccm.process_alias(lookslike_df, alias_df)
#except Exception as e:
# print(e)
output_file_name = wrk_file(FN.DATA_FOLDER,
'synonyms.xlsx',
is_timestamp=True)
writer = pd.ExcelWriter(output_file_name, engine='xlsxwriter')
synonyms_df.to_excel(writer, index=False)
writer.save()
print("Check latest synonym_XXX")
elif answer == '3':
synonyms_df = pd.read_excel(
wrk_file(FN.DATA_FOLDER, 'synonyms.xlsx'))
dreg_df = pd.read_excel(wrk_file(FN.DATA_FOLDER,
'exportdreg.xlsx'))
q = count_synonym_file_questions(synonyms_df)
print('{} of ? in synonyms'.format(q))
if q > 0:
print(
"Open synonym_XXX.xlsx; fix question marks and save as synonym.xlsx"
)
continue
core_part_name_df = pd.read_excel(
wrk_file(FN.DATA_FOLDER, "coreproduct.xlsx"))
core_part_name_df = core_part_name_df[['Type', 'Core Product']]
core_product_list = core_part_name_df['Core Product'].tolist()
core_part_name_dict = core_part_name_df.set_index(
'Type')['Core Product'].to_dict()
for cp in core_product_list:
if cp not in core_part_name_dict:
core_part_name_dict.update({cp: cp})
synonyms_dict = ccm.get_dict(synonyms_df)
dd = DregData(dreg_df, synonyms_dict, core_part_name_dict)
solver = DregSolver()
solver.process_all_new(dd)
solver.process_duplication_check(dd)
writer = DregWriter()
writer.set_default_rules()
output_file_name = wrk_file(FN.DATA_FOLDER,
"dreg_analysis.xlsx",
is_timestamp=True)
writer.save_dreg(output_file_name, dd)
print("Done!")
print(solver.get_statistics())
elif answer == "4":
mylog.info('Starting update IDIS...')
mylog.debug("Reading 'dreg_analysis.xlsx' file")
dreg_df = pd.read_excel(
wrk_file(FN.DATA_FOLDER, 'dreg_analysis.xlsx'))
mylog.debug("Init DregData database...")
dd = DregData(dreg_df, add_working_columns=False)
dreg_ids_with_action = dd.id_list_action_not_empty
#dreg_ids_with_action = ['20408789']
mylog.debug("Staring IDIS update with {0} registrations...".format(
len(dreg_ids_with_action)))
update_idis(dd, dreg_ids_with_action, get_config('idis'))
output_file_name = wrk_file(FN.DATA_FOLDER,
"last_clicker_result.xlsx",
is_timestamp=True)
writer = pd.ExcelWriter(output_file_name, engine='xlsxwriter')
dreg_df.to_excel(writer, index=False)
writer.save()
# run_idis_clicker(dd, dreg_ids_with_action)
elif answer == "5":
dreg_df = pd.read_excel(
wrk_file(FN.DATA_FOLDER, 'dreg_analysis.xlsx'))
dd = DregData(dreg_df)
dreg_ids_with_action = dd.id_list_clicker_fail()
run_idis_clicker(dd, dreg_ids_with_action)
elif answer == "6":
p_old_file = os.path.join(FN.DATA_FOLDER, 'exportdreg.xlsx')
p_new_file = os.path.join(FN.DATA_FOLDER, 'updatedreg.xlsx')
print_file_info(p_old_file, mode=Modes.DREG_FILE)
print_file_info(p_new_file, mode=Modes.DREG_FILE)
print("Result will be saved in exportdreg.xlsx")
is_yes = input("Continue y/n?")
if is_yes == 'y':
init = pd.read_excel(p_old_file)
upd = pd.read_excel(p_new_file)
init_dd = DregData(init, add_working_columns=False)
upd_dd = DregData(upd, add_working_columns=False)
init_dd.update(upd_dd)
output_df = init_dd.get_dreg_data()
output_file_name = wrk_file(FN.DATA_FOLDER, "exportdreg.xlsx")
writer = pd.ExcelWriter(output_file_name, engine='xlsxwriter')
output_df.to_excel(writer, index=False)
writer.save()
print("Done!")
print_file_info(p_old_file, mode=Modes.DREG_FILE)
elif answer == "7":
mylog.info('Starting update one DREG...')
dreg_id = str(input("DREG ID:"))
mylog.debug("Reading 'dreg_analysis.xlsx' file")
dreg_df = pd.read_excel(
wrk_file(FN.DATA_FOLDER, 'dreg_analysis.xlsx'))
mylog.debug("Init DregData database...")
dd = DregData(dreg_df, add_working_columns=False)
dreg_ids_with_action = [dreg_id]
mylog.debug("Staring IDIS update with {0} registrations...".format(
len(dreg_ids_with_action)))
update_idis(dd, dreg_ids_with_action, get_config('idis'))
elif answer == "q":
break
def if_new_reject_if_approved_conflict(self, dreg_id_list: list,
dreg_status, comment, dd: DregData):
if dreg_status == Status.NEW:
dd.add_comment(dreg_id_list[0], comment)
dd.reject(dreg_id_list[0])
dd.set_rejection_reason_already_registered(dreg_id_list[0])
elif dreg_status == Status.APPROVED:
for dreg_id in dreg_id_list:
if not dd.is_marked_conflict(dreg_id):
dd.mark_dreg_conflict(dreg_id)
dd.add_comment(dreg_id, comment)
return
def process_dreg_by_id(self, dd: DregData, dreg_id):
dreg_status = dd.get_status_by_id(dreg_id)
core_part = dd.get_core_part_num_by_id(dreg_id)
orig_part = dd.get_orig_part_num_by_id(dreg_id)
if not dd.is_part_in_pricelist(orig_part):
dd.add_comment(dreg_id, "{0} not found;".format(orig_part))
customer = dd.get_core_customer_by_id(dreg_id)
disti = dd.get_disti_by_id(dreg_id)
subcon = dd.get_subcon_by_id(dreg_id)
base_list = dd.id_list_filter(core_part=core_part,
status=Status.APPROVED,
exclude_dreg_id=dreg_id)
# Case 1&2: same customer, same part, different disti
same_cust_diff_disti = dd.id_list_filter(core_cust=customer,
exclude_disti=disti,
lookup_id_list=base_list)
if not len(same_cust_diff_disti) == 0:
comment = self.format_comment('Customer conflict:',
[dreg_id] + same_cust_diff_disti, dd)
self.if_new_reject_if_approved_conflict(
[dreg_id] + same_cust_diff_disti, dreg_status, comment, dd)
return
# check subcontructor intersection
subcon_conflict_dreg_id_list = self.get_intersection_in_cust_and_subcon(
customer, subcon, disti, dd, base_list)
if not len(subcon_conflict_dreg_id_list) == 0:
comment = self.format_comment('Subcon conflict:', [dreg_id] +
subcon_conflict_dreg_id_list, dd)
self.if_new_reject_if_approved_conflict(
[dreg_id] + subcon_conflict_dreg_id_list, dreg_status, comment,
dd)
return
# check BW in the same customer
bw_with_same_part_list = dd.id_list_filter(core_part=core_part,
stage='BW')
bw_list = self.get_intersection_in_cust_and_subcon(
customer, subcon, disti, dd, bw_with_same_part_list)
if not len(bw_list) == 0:
comment = self.format_comment('BW conflict: ', [dreg_id] + bw_list,
dd)
self.if_new_reject_if_approved_conflict([dreg_id] + bw_list,
dreg_status, comment, dd)
return
# check if more than one "New" -> Manual decision
if dreg_status == Status.NEW:
status_new_list = dd.id_list_filter(core_part=core_part,
core_cust=customer,
status=Status.NEW,
exclude_disti=disti,
exclude_dreg_id=dreg_id)
if not len(status_new_list) == 0:
comment = self.format_comment('NEW from other disti too: ',
[dreg_id] + status_new_list, dd)
dd.ask_for_manual_decision(dreg_id)
dd.add_comment(dreg_id, comment)
return
# Approve if nothing from above
li = dd.customers_which_use_the_part(core_part,
base_list,
except_customer=customer)
if li:
comment = "Same part in: {0};".format(", ".join(li))
dd.add_comment(dreg_id, comment)
if dreg_status == Status.NEW:
dd.approve(dreg_id)
elif dreg_status == Status.APPROVED:
dd.mark_conflict_check_ok(dreg_id)
return
def test_process_dreg_by_id1(self):
synonyms_df = pd.read_excel(
os.path.join("Tests", "class_DregSolver", "Data", "synonyms.xlsx"))
dreg_df = pd.read_excel(
os.path.join("Tests", "class_DregSolver", "Data",
"exportdreg.xlsx"))
core_part_name_df = pd.read_excel(
os.path.join("Tests", "class_DregSolver", "Data",
"coreproduct.xlsx"))
core_part_name_df = core_part_name_df[['Type', 'Core Product']]
core_part_name_dict = core_part_name_df.set_index(
'Type')['Core Product'].to_dict()
synonyms_dict = ccm.get_dict(synonyms_df)
dd = DregData(dreg_df, synonyms_dict, core_part_name_dict)
solver = DregSolver()
# Case 1: reject new if already exist for other disti
dreg_id = '20412507'
solver.process_dreg_by_id(dd, dreg_id)
res = dd.get_action_value(dreg_id)
reason = dd.get_reason_value(dreg_id)
self.assertEqual(
res, Action.REJECT,
"New reg, Same part, same customer, diff disti => Reject")
self.assertEqual(reason, Reason.ALREADY_REGISTERED_BY_OTHER)
# Case 2: conflict of existing registrations
dreg_id = '20246272'
solver.process_dreg_by_id(dd, dreg_id)
res = dd.get_action_value(dreg_id)
self.assertEqual(
res, Action.CONFLICT,
"Approved regs, Same part, same customer, diff disti => Conflict")
res = dd.get_action_value('20340467')
self.assertEqual(
res, Action.CONFLICT,
"Approved regs, Same part, same customer, diff disti => Conflict")
# Case 3: conflict in subcontructors
dreg_id = '20244170'
solver.process_dreg_by_id(dd, dreg_id)
res = dd.get_action_value(dreg_id)
self.assertEqual(res, Action.CONFLICT, "Same Subcon 1 => Conflict")
res = dd.get_action_value('20259923')
self.assertEqual(res, Action.CONFLICT, "Same subcon 2 => Conflict")
# Case 4: conflict with BW
dreg_id = '20208347'
solver.process_dreg_by_id(dd, dreg_id)
res = dd.get_action_value(dreg_id)
self.assertEqual(res, Action.CONFLICT, "Same Subcon 1 => Conflict")
res = dd.get_action_value('20025746')
self.assertEqual(res, Action.CONFLICT, "Same subcon 2 => Conflict")
# Case 5: New from other customer too
dreg_id = '20412524'
solver.process_dreg_by_id(dd, dreg_id)
res = dd.get_action_value(dreg_id)
self.assertEqual(res, Action.MANUAL_DECISION,
"New from other => Manual Decision")
dreg_id = '20412557'
solver.process_dreg_by_id(dd, dreg_id)
res = dd.get_action_value(dreg_id)
self.assertEqual(res, Action.APPROVE, "Approve")
dreg_id = '20081364'
solver.process_dreg_by_id(dd, dreg_id)
res = dd.get_action_value(dreg_id)
self.assertEqual(res, Action.CHECK_OK, "Check Ok")