def retrieve_gfis_data(path_wildcard):
"""
:param path_wildcard: path to directory that contains *.xlsx files
:return: updates the dictionary GFIS_DATA where invoice_number is a key, and
schedule_date, parsed_date, payments are values.
"""
for file in glob.glob(path_wildcard):
try:
DataFile.remove_row(file)
gfis_excel = load_workbook(filename=f'{file}')
gfis_sheet = gfis_excel.active
except FileNotFoundError:
print('File *.xlsx in <gfis> not found')
else:
invoice_numbers = [
str(invoice[0])
for invoice in gfis_sheet.iter_rows(min_row=MIN_ROW,
min_col=GFIS_INVOICE_COL,
max_col=GFIS_INVOICE_COL,
values_only=True)
]
schedule_dates = [
datetime.strftime(schedule[0], '%Y-%m-%d')
if schedule[0] else 'no data'
for schedule in gfis_sheet.iter_rows(min_row=MIN_ROW,
min_col=GFIS_SCHEDULE_COL,
max_col=GFIS_SCHEDULE_COL,
values_only=True)
]
spread_payment_dates = [
payment_date[0] for payment_date in gfis_sheet.iter_rows(
min_row=MIN_ROW,
min_col=last_column(file),
max_col=last_column(file),
values_only=True)
]
parsed_payments_dates = [
datetime.strftime(date, '%Y-%m-%d')
if date is not None else 'NOT PAID'
for date in spread_payment_dates
]
payments = [
payment[0]
for payment in gfis_sheet.iter_rows(min_row=MIN_ROW,
min_col=GFIS_PAYMENT_COL,
max_col=GFIS_PAYMENT_COL,
values_only=True)
]
for invoice, schedule_date, payment_date, payment in zip(
invoice_numbers, schedule_dates, parsed_payments_dates,
payments):
if invoice not in GFIS_DATA.keys():
GFIS_DATA[invoice] = schedule_date, payment_date, payment
elif payment > GFIS_DATA.get(invoice)[2]:
GFIS_DATA[invoice] = schedule_date, payment_date, payment
