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)