def write_row(worksheet,
invoice,
element,
row,
fmt_error,
side='left',
write_errors=True):
col = 0
if side == 'right':
col = 8
worksheet.write(row, col, element.commission_type)
worksheet.write(row, col + 1, element.client)
worksheet.write(row, col + 2, element.referrer)
format_ = fmt_error if not element.equal_amount_paid else None
worksheet.write(row, col + 3, element.amount_paid, format_)
format_ = fmt_error if not element.equal_gst_paid else None
worksheet.write(row, col + 4, element.gst_paid, format_)
format_ = fmt_error if not element.equal_total else None
worksheet.write(row, col + 5, element.total, format_)
errors = []
line_a = element.row_number
if element.pair is not None:
line_b = element.pair.row_number
if write_errors:
if not element.equal_amount_paid:
errors.append(
new_error(invoice.filename, invoice.pair.filename,
'Amount Paid does not match', line_a, line_b,
element.amount_paid,
element.pair.amount_paid))
if not element.equal_gst_paid:
errors.append(
new_error(invoice.filename, invoice.pair.filename,
'GST Paid does not match', line_a, line_b,
element.gst_paid, element.pair.gst_paid))
if not element.equal_total:
errors.append(
new_error(invoice.filename, invoice.pair.filename,
'Total does not match', line_a, line_b,
element.total, element.pair.total))
else:
if write_errors:
errors.append(
new_error(invoice.filename, invoice.pair.filename,
'No corresponding row in commission file',
line_a, ''))
else:
errors.append(
new_error(invoice.filename, invoice.pair.filename,
'No corresponding row in commission file', '',
line_a))
return errors
def process_comparison(self):
if self.pair is None:
return None
workbook = self.create_workbook(OUTPUT_DIR_ABA)
worksheet = workbook.add_worksheet('ABA Comparison Results')
fmt_error = get_error_format(workbook)
row = 0
col_a = 0
col_b = 13
keys_unmatched = set(self.pair.datarows.keys()) - set(
self.datarows.keys())
for key in self.datarows.keys():
self_row = self.datarows[key]
pair_row = self.pair.datarows.get(key, None)
if pair_row is None:
worksheet.write_row(row, col_a, self_row, fmt_error)
error = new_error(self.filename, self.pair.filename,
f'No match found for row', '', '',
' '.join(self_row))
self.summary_errors.append(error)
else:
for index, value in enumerate(self_row):
format_ = None if u.sanitize(value) == u.sanitize(
pair_row[index]) else fmt_error
worksheet.write(row, index, value, format_)
worksheet.write(row, index + col_b, pair_row[index],
format_)
if value != pair_row[index]:
column = self.get_column(value[0], index)
error = new_error(
self.filename, self.pair.filename,
f'Values of {column} does not match', '', '',
value, pair_row[index])
self.summary_errors.append(error)
row += 1
for key in keys_unmatched:
worksheet.write_row(row, col_b, self.pair.datarows[key], fmt_error)
error = new_error(self.filename, self.pair.filename,
f'No match found for row', '', '', '',
' '.join(self.pair.datarows[key]))
self.summary_errors.append(error)
row += 1
if len(self.summary_errors) > 0:
workbook.close()
else:
del workbook
return self.summary_errors
def run_comparison(files_a, files_b, margin, summary_filname, summary_title,
filepath_a, filepath_b):
create_dirs()
summary_errors = []
# Set each invoice pair
for key in files_a.keys():
if files_b.get(key, None) is not None:
files_a[key].pair = files_b[key]
files_b[key].pair = files_a[key]
else:
# Log in the summary files that don't have a match
msg = 'No corresponding commission file found'
error = new_error(files_a[key].filename, '', msg)
summary_errors.append(error)
# Find all Infynity files that don't have a match
alone_keys_infynity = set(files_b.keys()) - set(files_a.keys())
for key in alone_keys_infynity:
msg = 'No corresponding commission file found'
error = new_error('', files_b[key].filename, msg)
summary_errors.append(error)
counter = 1
for key in files_a.keys():
print(f'Processing {counter} of {len(files_a)} files', end='\r')
errors = files_a[key].process_comparison(margin)
if errors is not None:
summary_errors = summary_errors + errors
counter += 1
print()
# Create summary based on errors
file = f"{OUTPUT_DIR_SUMMARY}{summary_filname}.xlsx"
workbook = xlsxwriter.Workbook(file)
worksheet = workbook.add_worksheet('Summary')
fmt_title = get_title_format(workbook)
fmt_table_header = get_header_format(workbook)
worksheet.merge_range('A1:I1', summary_title, fmt_title)
row = 1
col = 0
worksheet.write(row, col, f"Number of issues: {str(len(summary_errors))}")
row += 2
worksheet = write_errors(summary_errors, worksheet, row, col,
fmt_table_header, filepath_a, filepath_b)
workbook.close()
def parse(self):
file = open(self.full_path, 'r')
for index, line in enumerate(file.readlines()):
if line.startswith('0'):
aba_line = self.parse_line_type_0(line)
key = u.sanitize(''.join(aba_line))
self.datarows[key] = aba_line
elif line.startswith('1'):
aba_line = self.parse_line_type_1(line)
key = u.sanitize(aba_line[7])
self.datarows[key] = aba_line
elif line.startswith('7'):
aba_line = self.parse_line_type_7(line)
key = u.sanitize(''.join(aba_line))
self.datarows[key] = aba_line
else:
msg = f'There is an invalid ABA line on line {index}'
error = new_error(self.filename, self.pair.filename, msg)
self.summary_errors.append(error)
def read_files_broker(dir_: str, files: list) -> dict:
records = {}
counter = 1
errors = []
for file in files:
print(
f"Parsing {counter} of {len(files)} files from {bcolors.BLUE}{dir_}{bcolors.ENDC}",
end="\r")
if os.path.isdir(dir_ + file):
continue
try:
print(file)
ti = BrokerTaxInvoice(dir_, file)
records[ti.key] = ti
except IndexError:
# handle exception when there is a column missing or an issue with the file.
errors.append(
new_error(f"{dir_}/{file}", "", "ERROR PARSING FILE!!!"))
counter += 1
print()
return records, errors
def write_row(worksheet,
invoice,
element,
row,
fmt_error,
side="left",
write_errors=True):
col = 0
if side == "right":
col = 10
worksheet.write(row, col, element.commission_type)
worksheet.write(row, col + 1, element.client)
worksheet.write(row, col + 2, element.reference_id)
format_ = fmt_error if not element.equal_bank else None
worksheet.write(row, col + 3, element.bank, format_)
format_ = fmt_error if not element.equal_loan_balance else None
worksheet.write(row, col + 4, element.loan_balance, format_)
format_ = fmt_error if not element.equal_amount_paid else None
worksheet.write(row, col + 5, element.amount_paid, format_)
format_ = fmt_error if not element.equal_gst_paid else None
worksheet.write(row, col + 6, element.gst_paid, format_)
format_ = fmt_error if not element.equal_total_amount_paid else None
worksheet.write(row, col + 7, element.total_amount_paid, format_)
format_ = fmt_error if not element.equal_comments else None
worksheet.write(row, col + 8, element.comments, format_)
errors = []
line_a = element.row_number
description = f"Reference ID: {element.reference_id}"
if element.pair is not None:
line_b = element.pair.row_number
if write_errors:
if not element.equal_bank:
errors.append(
new_error(
invoice.filename,
invoice.pair.filename,
"Bank does not match",
line_a,
line_b,
element.bank,
element.pair.bank,
))
if not element.equal_loan_balance:
errors.append(
new_error(
invoice.filename,
invoice.pair.filename,
"Loan Balance does not match",
line_a,
line_b,
element.loan_balance,
element.pair.loan_balance,
))
if not element.equal_amount_paid:
errors.append(
new_error(
invoice.filename,
invoice.pair.filename,
"Amount Paid does not match",
line_a,
line_b,
element.amount_paid,
element.pair.amount_paid,
))
if not element.equal_gst_paid:
errors.append(
new_error(
invoice.filename,
invoice.pair.filename,
"Amount does not match",
line_a,
line_b,
element.gst_paid,
element.pair.gst_paid,
))
if not element.equal_total_amount_paid:
errors.append(
new_error(
invoice.filename,
invoice.pair.filename,
"Total Amount Paid does not match",
line_a,
line_b,
element.total_amount_paid,
element.pair.total_amount_paid,
))
if not element.equal_comments:
errors.append(
new_error(
invoice.filename,
invoice.pair.filename,
"Total Amount Paid does not match",
line_a,
line_b,
element.comments,
element.pair.comments,
))
else:
# pdb.set_trace()
if write_errors:
errors.append(
new_error(
invoice.filename,
invoice.pair.filename,
"No corresponding row in commission file",
line_a,
"",
value_a=description,
))
else:
errors.append(
new_error(
invoice.filename,
invoice.pair.filename,
"No corresponding row in commission file",
"",
line_a,
value_b=description,
))
return errors
def process_comparison(self, margin=0.000001):
if self.pair is None:
return None
assert type(
self.pair) == type(self), "self.pair is not of the correct type"
workbook = self.create_workbook(OUTPUT_DIR_BROKER)
fmt_table_header = get_header_format(workbook)
fmt_error = get_error_format(workbook)
worksheet = workbook.add_worksheet()
row = 0
col_a = 0
col_b = 10
format_ = fmt_error if not self.equal_from else None
worksheet.write(row, col_a, "From")
worksheet.write(row, col_a + 1, self.from_, format_)
worksheet.write(row, col_b, "From")
worksheet.write(row, col_b + 1, self.pair.from_, format_)
row += 1
format_ = fmt_error if not self.equal_to else None
worksheet.write(row, col_a, "To")
worksheet.write(row, col_a + 1, self.to, format_)
worksheet.write(row, col_b, "To")
worksheet.write(row, col_b + 1, self.pair.to, format_)
row += 1
format_ = fmt_error if not self.equal_abn else None
worksheet.write(row, col_a, "ABN")
worksheet.write(row, col_a + 1, self.abn, format_)
worksheet.write(row, col_b, "ABN")
worksheet.write(row, col_b + 1, self.pair.abn, format_)
row += 1
format_ = fmt_error if not self.equal_bsb else None
worksheet.write(row, col_a, "BSB")
worksheet.write(row, col_a + 1, self.bsb, format_)
worksheet.write(row, col_b, "BSB")
worksheet.write(row, col_b + 1, self.pair.bsb, format_)
row += 1
format_ = fmt_error if not self.equal_account else None
worksheet.write(row, col_a, "Account")
worksheet.write(row, col_a + 1, self.account, format_)
worksheet.write(row, col_b, "Account")
worksheet.write(row, col_b + 1, self.pair.account, format_)
if not self.equal_from:
self.summary_errors.append(
new_error(self.filename, self.pair.filename,
"From does not match", "", "", self.from_,
self.pair.from_))
if not self.equal_to:
self.summary_errors.append(
new_error(self.filename, self.pair.filename,
"To does not match", "", "", self.to, self.pair.to))
if not self.equal_abn:
self.summary_errors.append(
new_error(self.filename, self.pair.filename,
"ABN does not match", "", "", self.abn,
self.pair.abn))
if not self.equal_bsb:
self.summary_errors.append(
new_error(self.filename, self.pair.filename,
"BSB does not match", "", "", self.bsb,
self.pair.bsb))
if not self.equal_account:
self.summary_errors.append(
new_error(
self.filename,
self.pair.filename,
"Account does not match",
"",
"",
self.account,
self.pair.account,
))
row += 2
for index, item in enumerate(HEADER_BROKER):
worksheet.write(row, col_a + index, item, fmt_table_header)
worksheet.write(row, col_b + index, item, fmt_table_header)
row += 1
keys_unmatched = set(self.pair.datarows.keys() -
set(self.datarows.keys()))
# Code below is just to find the errors and write them into the spreadsheets
for key_full in self.datarows.keys():
self_row = self.datarows[key_full]
self_row.margin = margin
pair_row = self.find_pair_row(self_row)
self_row.pair = pair_row
if pair_row is not None:
# delete from pair list so it doesn't get matched again
del self.pair.datarows[pair_row.key_full]
# Remove the key from the keys_unmatched if it is there
if pair_row.key_full in keys_unmatched:
keys_unmatched.remove(pair_row.key_full)
pair_row.margin = margin
pair_row.pair = self_row
self.summary_errors += BrokerInvoiceRow.write_row(
worksheet,
self,
pair_row,
row,
fmt_error,
"right",
write_errors=False)
self.summary_errors += BrokerInvoiceRow.write_row(
worksheet, self, self_row, row, fmt_error)
row += 1
# Write unmatched records
for key in keys_unmatched:
self.summary_errors += BrokerInvoiceRow.write_row(
worksheet,
self,
self.pair.datarows[key],
row,
fmt_error,
"right",
write_errors=False)
row += 1
if len(self.summary_errors) > 0:
workbook.close()
else:
del workbook
return self.summary_errors