def rename_tabs(self, one_by_one, dry=False):
"""Rename each tab title to reflect the day number of the receipt."""
names_registry = set()
for worksheet in self.spreadsheet.worksheets():
title_before = worksheet.title
try:
normalized_title = get_normalized_title(
tab_title=title_before,
filename=self.spreadsheet.title,
names_registry=names_registry,
)
except ValueError as e:
conversion_error = RESULT_WARNING.format(
e) if dry else RESULT_SKIPPED
normalized_title = None
else:
conversion_error = None
names_registry.add(normalized_title or title_before)
click.echo(f"{title_before} ==> " +
f"{normalized_title or conversion_error}")
if dry or conversion_error:
continue
if not one_by_one or one_by_one and click.confirm(f"Rename?",
default=True):
result_msg = RESULT_SKIPPED if conversion_error else RESULT_OK
try:
worksheet.update_title(normalized_title)
except Exception as e:
result_msg = RESULT_ERROR.format(e)
click.echo(result_msg)
def move_tabs(self, one_by_one, dry=False, unambiguous_only=False):
"""
Move each tab of the workbook to an appropriate Receipt book.
"""
for worksheet in self.spreadsheet.worksheets():
receipt = Receipt(worksheet)
click.echo(
f"'{worksheet.title}' ({receipt.store}) will go to ==> ",
nl=False)
try:
date = parse(extract_date_string(worksheet.title))
except ValueError as e:
click.echo(RESULT_WARNING.format(e))
continue
dest_filename = f"{date.year}-{date.month:02d}"
is_unambiguous = date.day > 12 or date.day == date.month
if unambiguous_only and not is_unambiguous:
click.echo(
RESULT_WARNING.format(
"Skipped because date is ambiguous."))
continue
click.echo(f"'{dest_filename}'")
if dry:
continue
is_unambiguous = date.day > 12 or date.day == date.month
if unambiguous_only and not is_unambiguous:
click.echo(
RESULT_WARNING.format(
"Skipped because date is ambiguous."))
continue
if not one_by_one or (one_by_one
and click.confirm(f"Move?", default=True)):
try:
new_title = self.spreadsheet.client.copy_worksheet_to(
worksheet=worksheet, dest_filename=dest_filename)
self.spreadsheet.del_worksheet(worksheet)
except Exception as e:
result_msg = RESULT_ERROR.format(e)
else:
result_msg = f"{RESULT_OK} New title: '{new_title}'."
click.echo(result_msg)
def _month_billings_map(self):
result = {}
for worksheet in self.spreadsheet.worksheets():
try:
month = parse(worksheet.title).month
except ValueError:
continue
result[month] = MonthBilling(worksheet=worksheet)
if len(result) != 12:
click.echo(
RESULT_WARNING.format(
f"Only {len(result)} moths found in year billing book."))
return result
def find_duplicates(self):
comparison_attrs = []
for receipt in self.receipts:
click.echo(f"Reading receipt {receipt.worksheet.title}")
try:
comparison_attrs.append((
receipt.date,
receipt.subtotal,
receipt.total,
receipt.actually_paid,
))
except (ValueError, NotImplementedError) as e:
click.echo(
RESULT_WARNING.format(
f"Receipt {receipt.worksheet.title} has wrong data and skipped from analysis: {e}"
))
for attrs, count in Counter(comparison_attrs).items():
if count > 1:
click.echo(
RESULT_WARNING.format(
f"There are likely {count} duplicates of receipt from {attrs[0]}"
))
click.echo(RESULT_OK.format(f"No other duplicates found."))
def validate(filenames):
"""
Validate prices in all tabs of specified files.
If numbers don't add up there - shows the warning.
Shows the summary with issues across all files at the end.
"""
suspicious_receipts = []
total = 0
for filename in filenames:
try:
receipt_book = ReceiptBook(filename)
except SpreadsheetNotFound:
click.echo(
RESULT_ERROR.format(
f"'{filename}' not found. Check the name or permissions."))
continue
click.echo(f"Validating prices in '{filename}'...")
suspicious_receipts.extend(
receipt for receipt in receipt_book.receipts
if not receipt.prices_are_valid(
raise_exception=False) or receipt.discount)
total += len(receipt_book.receipts)
click.echo(
"\n" + RESULT_OK +
f"{total} receipts analyzed. {len(suspicious_receipts)} suspicious found:\n"
)
for receipt in suspicious_receipts:
click.echo(
f"{receipt.worksheet.spreadsheet.title} : {receipt.worksheet.title} ==> ",
nl=False,
)
try:
receipt.prices_are_valid(raise_exception=True)
if receipt.discount:
click.echo(
RESULT_OK +
f"Receipt has a discount/loyalty of {receipt.discount}.")
except ValueError as e:
click.echo(RESULT_WARNING.format(e))
except Exception as e:
click.echo(RESULT_ERROR.format(e))
def fetch_transactions(self):
"""Read the transactions from transaction history spreadsheet into memory."""
result = []
for worksheet_title, row_containers in self.content.items():
worksheet = self._tabs[worksheet_title]
for row, cells in enumerate(row_containers, 1):
if row == 1:
continue
try:
transaction = Transaction.from_cells(
worksheet=worksheet, row=row, cells=cells
)
except ValueError as e:
click.echo(RESULT_WARNING.format(e))
continue
result.append(transaction)
return result
def validate(self):
"""Check if the prices in each tab add up correctly."""
for receipt in self.receipts:
click.echo(f"{receipt.worksheet.title} ==> ", nl=False)
try:
receipt.prices_are_valid(raise_exception=True)
discount = receipt.discount
except ValueError as e:
click.echo(RESULT_WARNING.format(e))
continue
except Exception as e:
click.echo(RESULT_ERROR.format(e))
continue
if discount:
click.echo(
RESULT_OK +
f"Receipt has a discount/loyalty of {receipt.discount}.")
else:
click.echo(RESULT_OK)
def find_transactions(self, created, price, has_receipt):
"""
Looks up the transaction for a certain day and price and certain has_receipt state.
If the exact matches are not found on specified day, the closest days are
checked because sometimes banks post transaction to the history on the next
day or few. If the transaction with *exact* amount is found on next day,
then it is returned.
If the exact match by price is not found not on specified day, nor the next one,
then the closest matches are logged and None is returned.
:rtype: list or None
"""
transactions_this_day = self._transactions_by_date[created]
transactions_next_days = []
for day in range(self.day_match_threshold):
transactions_next_days.extend(
self._transactions_by_date[created + timedelta(days=day + 1)]
)
transactions = transactions_this_day + transactions_next_days
if has_receipt is not None:
transactions = [
transaction
for transaction in transactions
if transaction.has_receipt == has_receipt
]
exact_matches = []
for transaction in transactions:
if math.isclose(transaction.price, price):
if transaction.created > created:
click.echo(f"Found on the day {transaction.created}")
exact_matches.append(transaction)
if exact_matches:
return exact_matches
close_matches = []
for transaction in transactions:
difference = abs(transaction.price - price)
if difference <= self.price_match_threshold:
close_matches.append((transaction, difference))
if not close_matches:
return None
min_diff = min(diff for _, diff in close_matches)
close_matches = [
transaction for transaction, diff in close_matches if diff == min_diff
]
if close_matches:
msg = "\n".join(str(t) for t in close_matches)
click.echo(
RESULT_WARNING.format(
f"Exact transaction for ({created}, {price}) was not found "
f"but there are close matches: {msg}"
)
)
return None
def import_receipt(self, receipt: Receipt, note_threshold=50):
"""
Adds the data from the receipt to the month billing spreadsheet.
If a purchase in receipt exceeds threshold, it's name will be included
into a note for a cell.
Rules for HST/taxes:
if all purchases are groceries, then it is added too total grocery price;
if there are other categories, then it is added to the biggest one.
"""
date_match = receipt.date.month == self.month and receipt.date.year == self.year
if not date_match:
raise ValueError(
f"The receipt from {receipt.date} does not belong "
f"to '{self.year}-{self.month} billing sheet.")
if receipt.tax_belongs_to is None:
click.echo(
RESULT_WARNING.format(
f"Can't determine which category the tax belongs to in receipt '{receipt.worksheet.title}'"
))
cells_to_update = []
notes_to_add = {}
for good_type, purchases in receipt.purchases_by_type.items():
if not purchases:
continue
destination_label = self.get_destination_label(
created=purchases[0].created, good_type=purchases[0].good_type)
cell = self.worksheet.acell(destination_label,
value_render_option="FORMULA")
cells_to_update.append(cell)
cell_str_value = self.worksheet.acell(destination_label).value
cell_price_before = Decimal(
cell_str_value) if cell_str_value else 0
cell_formula = cell.value
for purchase in purchases:
cell_formula += (f"+{purchase.price}"
if cell_formula else f"={purchase.price}")
is_tax_here = receipt.tax and good_type == receipt.tax_belongs_to
if is_tax_here:
cell_formula += f"+{receipt.tax}"
if receipt.discount and good_type == receipt.most_expensive_category:
cell_formula += f"-{receipt.discount}"
cell.value = cell_formula
category_price = receipt.get_category_price(good_type=good_type)
added_price = category_price + (receipt.tax if is_tax_here else 0)
note = "\n".join(
purchase.good_name if purchase.price > 0 else
f"Return/Discount: {purchase.good_name}"
for purchase in purchases
if purchase.price > note_threshold or purchase.price < 0)
if note:
notes_to_add[destination_label] = f"{receipt.store}: \n {note}"
if cell_price_before and math.isclose(
cell_price_before % added_price, 0):
click.echo(
RESULT_WARNING.format(
f"Purchase in a cell {destination_label} is likely imported multiple times."
))
if notes_to_add:
self.worksheet.spreadsheet.client.insert_notes(
worksheet=self.worksheet,
labels_notes=notes_to_add,
replace=False)
self.worksheet.update_cells(cells_to_update,
value_input_option="USER_ENTERED")
def transactions_to_billing(transactions_filename, billing_filename,
note_threshold, one_by_one, unambiguous_only):
"""
Import from the Transaction history into Billing book.
This command takes year from the billing filename and imports only those transactions
which correspond to that year.
"""
click.echo(
f"Reading the transactions history from '{transactions_filename}'")
history = TransactionHistory(filename=transactions_filename)
if history.transactions:
click.echo(RESULT_OK)
click.echo(f"Reading the destination billing file '{billing_filename}'")
billing_book = BillingBook(billing_filename)
if billing_book.month_billings:
click.echo(RESULT_OK)
try:
for transaction in history.transactions:
if transaction.has_receipt or transaction.created.year != billing_book.year:
continue
click.echo(f"Importing {transaction}...")
if (not one_by_one or one_by_one
and click.confirm(f"Continue?", default=True)):
result_msg = RESULT_OK
month_billing = billing_book.get_month_billing(
month=transaction.created.month)
if unambiguous_only and len(transaction.matching_types) != 1:
click.echo("Ambiguous type. Skipped.")
continue
if len(transaction.matching_types) > 1:
choices = "\n".join(f"{i} - {cell_type.name}"
for i, cell_type in enumerate(
transaction.matching_types))
msg = f"Transaction {transaction} can be one of: \n{choices}\n. Nothing to skip."
selected_index = click.prompt(text=msg,
default="",
show_choices=True)
if not selected_index.strip():
click.echo("Skipped.")
continue
preferred_type = transaction.matching_types[int(
selected_index)]
elif len(transaction.matching_types) < 1:
available_types = {
i: cell_type
for i, cell_type in enumerate(CellType)
}
choices = "\n".join(
f"{i} - {cell_type.name}"
for i, cell_type in available_types.items())
msg = f"Can't determine good type for {transaction}. Choose one of: \n{choices}\n. Nothing to skip."
selected_index = click.prompt(text=msg,
default="",
show_choices=True)
if not selected_index.strip():
click.echo("Skipped.")
continue
preferred_type = available_types[int(selected_index)]
else:
preferred_type = transaction.good_type
try:
month_billing.import_transaction(
transaction,
note_threshold=note_threshold,
preferred_type=preferred_type,
)
except ValueError as e:
result_msg = RESULT_WARNING.format(e)
except Exception as e:
result_msg = RESULT_ERROR.format(e)
click.echo(result_msg)
transaction.has_receipt = True
finally:
click.echo("Updating the history spreadsheet...")
history.post_to_spreadsheet()
click.echo(RESULT_OK)