def get_list_of_locked_metakey(sheet):
meta_key_row_number = xwu.find_row_by_metakey(sheet, "MetaKey")
database_table_row_number = xwu.find_row_by_metakey(sheet, "DatabaseTable")
db_status_col_number = xwu.find_col_by_metakey("DbStatus", sheet)
meta_key_col_number = xwu.find_col_by_metakey("MetaKey", sheet)
first_data_row = xwu.find_first_data_row(sheet)
locked_grey_column_colour = sheet.range(first_data_row,
db_status_col_number).color
formula_blue_column_colour = sheet.range(meta_key_row_number,
meta_key_col_number).color
locked_blue_column_colour = c.FORMULA_VALUE_CELL_COLOUR
column = 1
set_of_locked_metakeys = set()
while sheet.range(
database_table_row_number,
column).color == formula_blue_column_colour or sheet.range(
database_table_row_number, column).color == (255, 255, 0):
data_cell_colour = sheet.range(first_data_row, column).color
if data_cell_colour == locked_grey_column_colour or data_cell_colour == locked_blue_column_colour or sheet.range(
database_table_row_number, column).value == "Formula":
set_of_locked_metakeys.add(
sheet.range(meta_key_row_number, column).value)
column += 1
list_of_locked_metakeys = list(set_of_locked_metakeys)
return list_of_locked_metakeys
def get_metakey_col_numbers(metakey_list): metakey_number_list = [] for metakey in metakey_list: col_number = xwu.find_col_by_metakey(metakey) print(col_number) metakey_number_list.append(col_number) return metakey_number_list
def insert_data_into_existing_rows(sheet_tab, metakey_list):
data_start_row = xwu.find_first_data_row(sheet_tab)
db_status_col_number = xwu.find_col_by_metakey('DbStatus')
while sheet_tab.range(data_start_row, db_status_col_number).value == 2:
fc.test_populator(sheet_tab, data_start_row, metakey_list,
partner_list)
data_start_row = data_start_row + 1
def order_book_insert(sheet, row, brand_code=c.BRAND_CODE):
#turn events off
xw.apps.active.api.EnableEvents = False
#fetch strings for book name and tab name
order_book_order_book_tab_list = dbg.get_random_order_book_and_order_book_tab(
brand_code)
order_book_name_value = order_book_order_book_tab_list[0]
order_book_tab_name_value = order_book_order_book_tab_list[1]
#find book name field - write in
order_book_name_col = xwu.find_col_by_metakey("OrderBookName")
sheet.range(row, order_book_name_col).value = order_book_name_value
#find tab name field - write in
order_book_tab_name_col = xwu.find_col_by_metakey("OrderBookTabName")
sheet.range(row, order_book_tab_name_col).value = order_book_tab_name_value
#turn events back on!
xw.apps.active.api.EnableEvents = True
def get_wholesale_ready_metakey_list(column_tab, exclude_order_book=True):
meta_key_col_number = xwu.find_col_by_metakey("Meta Key", column_tab)
wholesale_ready_col_number = xwu.find_col_by_metakey(
"Wholesale Ready", column_tab)
row = 1
meta_key_value = ""
meta_key_list = []
while meta_key_value is not None:
whole_sale_ready_binary_value = column_tab.range(
row, wholesale_ready_col_number).value
if whole_sale_ready_binary_value == 1:
meta_key_value = column_tab.range(row, meta_key_col_number).value
meta_key_list.append(meta_key_value)
row += 1
meta_key_value = column_tab.range(row, meta_key_col_number).value
if exclude_order_book is True:
if "OrderBookName" in meta_key_list:
meta_key_list.remove("OrderBookName")
if "OrderBookTabName" in meta_key_list:
meta_key_list.remove("OrderBookTabName")
return meta_key_list
def gen_csv_from_sheet(xwsheet: xw.Sheet):
first_row = xwu.find_first_data_row(xwsheet)
last_row = xwu.get_insert_data_row_number(xwsheet) + 1
first_col = xwu.find_col_by_metakey('IsFinalised', xwsheet)
last_col = xwu.get_last_col(xwsheet, first_col) + 1
with open(filename, 'w') as file:
for r in range(first_row, last_row):
for c in range(first_col, last_col):
extract_range = xwsheet.range(r, c)
ref_string = get_ref_from_cell(extract_range)
if (ref_string is not None):
file.write(ref_string + '\n')
def validate_size(size_dict, range):
sheet = xwu.get_sheet_from_range(range)
size_range = str(
sheet.range(range.row, xwu.find_col_by_metakey("SizeRange",
sheet)).value)
size_metakey = xwu.find_size_metakey_by_col(range.column, sheet)
range_length = 0
try:
range_length = len(size_dict[size_range].split(';'))
except KeyError:
range_length = len(size_dict[float(size_range)].split(';'))
if (size_metakey > range_length):
return False
else:
return True
def get_random_row_from_sht(sheet):
data_start_row_num = xwu.find_first_data_row(sheet)
print(data_start_row_num)
print(101)
orid_col_num = xwu.find_col_by_metakey("OrderRowId", sheet)
print(orid_col_num)
print(102)
data_end_row_num = data_start_row_num
while sheet.range(data_end_row_num, orid_col_num).value != None:
print(data_end_row_num)
data_end_row_num += 1
print(data_start_row_num)
print(data_end_row_num)
random_order_row_row_num = random.randrange(data_start_row_num,
data_end_row_num, 1)
print(random_order_row_row_num)
return random_order_row_row_num
def generate_dictionary_for_all_db_status_rows(metakey_col_number_list,
sheet_tab):
data_start_row = xwu.find_first_data_row(sheet_tab)
db_status_col_number = xwu.find_col_by_metakey('DbStatus')
dictionary = {}
values_dictionary = {}
while sheet_tab.range(data_start_row, db_status_col_number).value != None:
for column in metakey_col_number_list:
if sheet_tab.range(data_start_row,
column).value != None and sheet_tab.range(
data_start_row, column).value != '':
meta_key = xwu.find_metakey_value_by_col(column)
values_dictionary[meta_key] = sheet_tab.range(
data_start_row, column).value
dictionary[data_start_row] = values_dictionary
values_dictionary = {}
data_start_row = data_start_row + 1
return dictionary
def get_list_of_dropdown_metakeys(sheet):
"""
Summary: Creates a list of all dropdown metakeys (columns that
can be filled by clicking in the dropdown list and chossing
a value from there).
Return: A list with the names of all dropdown metakeys
Defined: fieldcreator.py
"""
meta_key_row_number = xwu.find_row_by_metakey(sheet, "MetaKey")
validation_row_number = xwu.find_row_by_metakey(sheet, "Validation")
meta_key_col_number = xwu.find_col_by_metakey("MetaKey", sheet)
column = 2
set_of_dropdown_metakeys = set()
while sheet.range(meta_key_row_number, column).color != None:
if sheet.range(validation_row_number, column).value is not None:
set_of_dropdown_metakeys.add(
sheet.range(meta_key_row_number, column).value)
column += 1
list_of_dropdown_metakeys = list(set_of_dropdown_metakeys)
return list_of_dropdown_metakeys
#action = "Create order book " + order_book_name
# if(order_book_details[order_book_name] is not None):
# log_result = "Creation Successful"
# else:
# log_result = "Creation Failed"
# logger.write_log(process, action, log_result)
##Open the order sheet (if not automated, pass in a test sheet id as parameter)
order_sheet = xw.Book(order_sheet_xl_name)
order_sheet_tab = order_sheet.sheets(xl_tab_name)
#Choose a line on which to operate
data_row = xwu.find_first_data_row(order_sheet_tab)
test_col = xwu.find_col_by_metakey("OrderBookName")
while (order_sheet_tab.range(data_row, test_col).value is not None):
data_row = data_row + 1
#input book and tab names onto the order sheet.
book_col = xwu.find_col_by_metakey("OrderBookName")
order_sheet_tab.range(data_row, book_col).value = order_book_name
tab_col = xwu.find_col_by_metakey("OrderBookTabName")
order_sheet_tab.range(data_row, tab_col).value = order_book_tab_name
##Turn events back on once the book has been added to the worksheet.
g.enable_events()
#create dictionary object for storing the input values
inputs = {}
def finalise_check_orig_rrp_an_cp():
# set up book
book_test = setup_order_sheet(os_path)
# workbook for getting all partners from config
workbook = book_test.order_sheet
# set up sheet
sheet = workbook.sheets[book_test.order_sheet_tabs[0]]
# partner tab sheet
partner_tab_sht = workbook.sheets['Partners']
rrp_gbp_col_num = xwu.find_col_by_metakey("OriginalRrpGBP", sheet)
rrp_eur_col_num = xwu.find_col_by_metakey("OriginalRrpEUR", sheet)
rrp_usd_col_num = xwu.find_col_by_metakey("OriginalRrpUSD", sheet)
rrp_aud_col_num = xwu.find_col_by_metakey("OriginalRrpAUD", sheet)
is_finalised_col_num = xwu.find_col_by_metakey("IsFinalised", sheet)
partner_list = find_all_partners_in_config_tab(workbook, partner_tab_sht)
random_partner = random.choice(partner_list)
print(random_partner)
print(1)
partner_currency_cost_price_col_num = xwu.find_col_by_metakey_and_partner_metakey(
sheet, "PartnerCpCurrency", random_partner)
print(2)
# random order row row number
row_num = get_random_row_from_sht(sheet)
print(row_num)
print(3)
#click finalise
pop_up_box_threading_7 = et.popupBoxDetect(
'7',
'Click OK finalised',
book_test.os_app,
buttonId='OK',
popup_box_title=c.POPUP_BOX_TITLE)
pop_up_box_threading_7.start()
pop_up_box_threading_7.join(timeout=10)
#finalise sheet
xw.apps[book_test.process_id].books
book_test.finalise_current_sheet()
#check if row is finalised
if sheet.range(row_num, is_finalised_col_num).value == 1:
print('Passed:Automated tests can continue')
else:
print(
'Failed: The line needs finalising before running the test cases')
#test 1 check GBP RRP
#get thread ready and start8
pop_up_box_threading_8 = et.popupBoxDetect(
'8',
'Click OK cant edit wholesale ready fields',
book_test.os_app,
buttonId='OK',
popup_box_title=c.POPUP_BOX_TITLE)
pop_up_box_threading_8.start()
pop_up_box_threading_9 = et.buttonPress('9',
'Click OK undo appication error',
book_test.os_app,
buttonID="OK")
pop_up_box_threading_9.start()
#try adding a value
sheet.range(row_num, rrp_gbp_col_num).value = 20
pop_up_box_threading_8.join()
#check test
if pop_up_box_threading_8.is_alive() == True:
#or pop_up_box_threading_9.is_alive() == True:
print('FAiled: thread not completed')
else:
print('Passed: thread completed')
# test 2 check EUR RRP
# get thread ready and start8
pop_up_box_threading_10 = et.popupBoxDetect(
'10',
'Click OK cant edit wholesale ready fields',
book_test.os_app,
buttonId='OK',
popup_box_title=c.POPUP_BOX_TITLE)
pop_up_box_threading_10.start()
pop_up_box_threading_11 = et.buttonPress('11',
'Click OK undo appication error',
book_test.os_app,
buttonID="OK")
pop_up_box_threading_11.start()
# try adding a value
sheet.range(row_num, rrp_eur_col_num).value = 20
pop_up_box_threading_10.join()
# check test
if pop_up_box_threading_10.is_alive() == True:
print('FAiled: thread not completed')
else:
print('Passed: thread completed')
# test 3 check AUD RRP
#get thread ready and start8
pop_up_box_threading_12 = et.popupBoxDetect(
'12',
'Click OK cant edit wholesale ready fields',
book_test.os_app,
buttonId='OK',
popup_box_title=c.POPUP_BOX_TITLE)
pop_up_box_threading_12.start()
pop_up_box_threading_13 = et.buttonPress('13',
'Click OK undo appication error',
book_test.os_app,
buttonID="OK")
pop_up_box_threading_13.start()
# try adding a value
sheet.range(row_num, rrp_aud_col_num).value = 20
pop_up_box_threading_12.join()
# check test
if pop_up_box_threading_12.is_alive() == True:
print('FAiled: thread not completed')
else:
print('Passed: thread completed')
# # test 4 check USD RRP
# # get thread ready and start8
pop_up_box_threading_14 = et.popupBoxDetect(
'14',
'Click OK cant edit wholesale ready fields',
book_test.os_app,
buttonId='OK',
popup_box_title=c.POPUP_BOX_TITLE)
pop_up_box_threading_14.start()
pop_up_box_threading_15 = et.buttonPress('15',
'Click OK undo appication error',
book_test.os_app,
buttonID="OK")
pop_up_box_threading_15.start()
# try adding a value
sheet.range(row_num, rrp_usd_col_num).value = 20
pop_up_box_threading_14.join()
# check test
if pop_up_box_threading_14.is_alive() == True:
print('FAiled: thread not completed')
else:
print('Passed: thread completed')
#check if you can still edit Partner Cost Price Currency column
randon_number = 26
#add value to the row for partner currency cost price
sheet.range(row_num,
partner_currency_cost_price_col_num).value = randon_number
#check if the value has been added
if sheet.range(row_num,
partner_currency_cost_price_col_num).value == randon_number:
print('Passed: Column is editable')
else:
print(
'Failed: Column value is not updated and/or the column is not editable'
)
def test_cases_for_rrp_cost_prices():
# set up book
book_test = setup_order_sheet(os_path)
# workbook for getting all partners from config
workbook = book_test.order_sheet
# set up sheet
sheet = workbook.sheets[book_test.order_sheet_tabs[0]]
#partner tab sheet
partner_tab_sht = workbook.sheets['Partners']
rrp_gbp_col_num = xwu.find_col_by_metakey("OriginalRrpGBP", sheet)
rrp_eur_col_num = xwu.find_col_by_metakey("OriginalRrpEUR", sheet)
partner_list = find_all_partners_in_config_tab(workbook, partner_tab_sht)
random_partner = random.choice(partner_list)
print(random_partner)
print(1)
partner_currency_cost_price_col_num = xwu.find_col_by_metakey_and_partner_metakey(
sheet, "PartnerCpCurrency", random_partner)
print(2)
#random order row row number
row_num = get_random_row_from_sht(sheet)
print(row_num)
print(3)
#change currency to GBP
change_currency_in_config(partner_tab_sht, "EUR", random_partner)
# refresh the second ordersheet
xw.apps[book_test.process_id].books
book_test.refresh_all_sheets()
print('REFRESHHEED')
#get partner by size unit column number to insert a unit to for that partner
partner_by_size_unit_col_num = xwu.find_col_by_metakey_and_partner_metakey(
sheet, "PartnerOriginalUnitsBySize", random_partner)
#insert unit into partner by size units
sheet.range(row_num, partner_by_size_unit_col_num).value = 11
#get partner currency cost price value
old_partner_currency_cost_price = sheet.range(
row_num, partner_currency_cost_price_col_num).value
#insert/change the value for RRP
if sheet.range(row_num, rrp_gbp_col_num).value != 20:
sheet.range(row_num, rrp_gbp_col_num).value = 20
else:
sheet.range(row_num, rrp_gbp_col_num).value = 24
#validation test 1
if sheet.range(row_num, rrp_gbp_col_num).value != None:
print('Passed: RRP GBP value is populated')
else:
print('Failed: RRP GBP value is NOT populated')
#get new partner currenct cost price
new_partner_currency_cost_price = sheet.range(
row_num, partner_currency_cost_price_col_num).value
#test 2: check if RRP had changed and has auto-populated partner currency cost price
print('old')
print(old_partner_currency_cost_price)
print('new')
print(new_partner_currency_cost_price)
if old_partner_currency_cost_price != new_partner_currency_cost_price and old_partner_currency_cost_price != None and new_partner_currency_cost_price != None:
print(
'Passed: Partner Currency Cost Price Value has change since RRP GBP value has changed'
)
else:
print('Failed: Partner Currency Cost Price Value has NOT changed')
#Change currency to EURO for that random partner
change_currency_in_config(partner_tab_sht, "GBP", random_partner)
#refresh all sheets
book_test.refresh_all_sheets()
print('REFRESHHEED')
#get partner currency cost price value
old_partner_currency_cost_price = sheet.range(
row_num, partner_currency_cost_price_col_num).value
#insert/change the value for RRP
if sheet.range(row_num, rrp_eur_col_num).value != 25:
sheet.range(row_num, rrp_eur_col_num).value = 25
else:
sheet.range(row_num, rrp_gbp_col_num).value = 30
# validation test 3
if sheet.range(row_num, rrp_eur_col_num).value != None:
print('Passed: RRP EUR value is populated')
else:
print('Failed: RRP EUR value is NOT populated')
#get new partner currenct cost price
new_partner_currency_cost_price = sheet.range(
row_num, partner_currency_cost_price_col_num).value
#test 4: check if RRP had changed and has auto-populated partner currency cost price
if old_partner_currency_cost_price != new_partner_currency_cost_price and old_partner_currency_cost_price != None and new_partner_currency_cost_price != None:
print(
'Passed: Partner Currency Cost Price Value has change since RRP EUR value has changed'
)
else:
print('Failed: Partner Currency Cost Price Value has NOT changed')
while (iterator < 26):
#select a random cell
random_row = rnd.randint(
xwu.find_first_data_row(order_sheet.sheets[order_sheet_tab1]),
lastDataRow)
random_col = rnd.randint(
find_col_by_metakey("WholesaleReadyStatus") + 1, lastDataCol)
random_cell_value = order_sheet_tab1.range(random_row, random_col).value
#find the metakey for that cell
cell_metakey = xwu.find_metakey_value_by_col(
random_col, order_sheet.sheets[order_sheet_tab1])
#find the order row id for that cell
order_row_col = xwu.find_col_by_metakey("OrderRowId")
order_row_id = order_sheet_tab1.range(random_row, order_row_col).value
#use the metakey linked with the dictionary to get the method in order to retrieve the selected data from the db
try:
db_value = dictionary[cell_metakey](order_row_id)
#compare db_value with random_cell
if random_cell_value != db_value:
test_pass = 0
except:
comparison_result = "The randomly selected column " + cell_metakey + "is not stored in the database"
columns_not_stored_in_database_count += 1
print(comparison_result)
print(columns_not_stored_in_database_count)
#loop
iterator += 1