def preprocess_rows(self, results: typing.Iterable, is_html=True) -> typing.Iterable: item_formatter = HtmlFormatter() if is_html else CsvFormatter() if self.query_type == "month_category": user_categories: typing.Iterable[Category] = Category.user_objects( self.request) user_category_ids: typing.Dict[int, int] = {} cat_totals: typing.Dict[int, typing.Union[float, decimal.Decimal]] = {} for n, cat in enumerate(user_categories, 1): user_category_ids[cat.pk] = n cat_totals[cat.pk] = 0 cat_count = len(user_category_ids) for yearmonth, items in itertools.groupby(results, operator.itemgetter(0)): row = [format_yearmonth(yearmonth) ] + [item_formatter.format_money(0)] * cat_count row_total = 0 for _ym, category_id, value in items: row[user_category_ids[ category_id]] = item_formatter.format_money(value) row_total += value cat_totals[category_id] += value row.append(item_formatter.format_money(row_total)) yield row cat_totals_values = list(cat_totals.values()) yield [_("Grand Total")] + [ item_formatter.format_money(i) for i in cat_totals_values ] + [item_formatter.format_money(sum(cat_totals_values))] elif self.query_type == "month": total = 0 for yearmonth, value in results: yield format_yearmonth(yearmonth), item_formatter.format_money( value) total += value yield _("Grand Total"), item_formatter.format_money(total) else: # category user_categories: typing.Dict[int, Category] = { c.pk: c for c in Category.user_objects(self.request) } total = 0 for category, value in results: yield ( item_formatter.format_category(user_categories[category]), item_formatter.format_money(value), ) total += value yield _("Grand Total"), item_formatter.format_money(total)
def expense_list(request, bills_only=False): exp = revchron(Expense.objects.filter(user=request.user).select_related("category")) if bills_only: exp = exp.filter(is_bill=True) htmltitle = _("Bills") pid = "bill_list" else: htmltitle = _("Expenses") pid = "expense_list" paginator = Paginator(exp, settings.EXPENSES_PAGE_SIZE) page = request.GET.get("page", "1") expenses = paginator.get_page(page) if page == "1" and not bills_only: show_form = True categories = Category.user_objects(request) else: show_form = False categories = None return render( request, "expenses/expense_list.html", { "htmltitle": htmltitle, "pid": pid, "expenses": expenses, "bills_only": bills_only, "show_form": show_form, "categories": categories, }, )
def category_delete(request, slug): category = get_object_or_404(Category, slug=slug, user=request.user) move_succeeded = True if request.method == "POST": if category.total_count != 0: dest = request.POST.get("move_destination") move_succeeded = category.prepare_deletion(dest, request.user) if move_succeeded: category.delete() messages.add_message(request, messages.SUCCESS, _("%s has been deleted.") % category.name) return HttpResponseRedirect(reverse("expenses:category_list")) categories = Category.user_objects(request) show_del_button = True if categories.count == 1 and category.total_count > 0: show_del_button = False return render( request, "expenses/category_delete.html", { "object": category, "deletion_failed": not move_succeeded, "htmltitle": _("Delete category %s") % category.name, "pid": "category_delete", "categories": categories, "show_del_button": show_del_button, }, )
def get_column_headers(self, engine: Engine, is_html=True) -> (typing.List[str], typing.List[str]): item_formatter = HtmlFormatter() if is_html else CsvFormatter() if self.query_type == "month_category": user_categories: typing.Iterable[Category] = Category.user_objects(self.request) names = [_("Month")] + [item_formatter.format_category(c) for c in user_categories] + [_("Total")] return names, ["right"] * len(names) elif self.query_type == "category": return ([_("Category"), _("Total")], ["left", "right"]) elif self.query_type == "month": return ([_("Month"), _("Total")], ["right", "right"])
def get_column_headers( self, engine: Engine) -> (typing.List[str], typing.List[str]): if self.query_type == "month_category": user_categories: typing.Iterable[Category] = Category.user_objects( self.request) names = [_("Month")] + [c.html_link() for c in user_categories] + [_("Total")] return names, ["right"] * len(names) elif self.query_type == "category": return ([_("Category"), _("Total")], ["left", "right"]) elif self.query_type == "month": return ([_("Month"), _("Total")], ["right", "right"])
def run(self): engine: Engine = Engine.get_from_connection(connection) days: typing.Dict[str, int] = {} days_names = ("expense_days", "all_days") with connection.cursor() as cursor: sql: str = self.get_query("day_counts", engine) cursor.execute(sql, [self.request.user.id]) expense_days, all_days = cursor.fetchone() days["expense_days"] = int(expense_days) days["all_days"] = int(all_days) sql: str = self.get_query("data", engine) cursor.execute(sql, [self.request.user.id]) cat_data: typing.List[tuple] = cursor.fetchall() if days["all_days"] == 0: return no_results_to_show() user_categories: typing.Iterable[Category] = Category.user_objects( self.request) timescales = [1, 7, 30, 365] timescale_names = { 1: _("Per 1 day"), 7: _("Per week (7 days)"), 30: _("Per month (30 days)"), 365: _("Per year (365 days)"), } all_time_count = all_time_sum = 0 for _cat, at_cat_count, at_cat_sum in cat_data: all_time_count += at_cat_count all_time_sum += at_cat_sum daily_data = self.compute_daily_data(all_time_count, all_time_sum, days, days_names, timescales, timescale_names) cat_tables = self.compute_category_data(cat_data, user_categories, days, days_names, timescales, timescale_names) return mark_safe( render_to_string( "expenses/reports/report_daily_spending.html", { "days": days, "daily_data": daily_data, "cat_links": [cat.html_link() for cat in user_categories], "cat_tables": cat_tables, }, self.request, ))
def category_list(request): paginator = Paginator(Category.user_objects(request), settings.EXPENSES_PAGE_SIZE) page = request.GET.get("page") categories = paginator.get_page(page) return render( request, "expenses/category_list.html", { "htmltitle": _("Categories"), "pid": "category_list", "categories": categories, }, )
def preprocess(self, is_html=True): engine: Engine = Engine.get_from_connection(connection) days: typing.Dict[str, int] = {} days_names = ("expense_days", "all_days") with connection.cursor() as cursor: sql: str = self.get_query("day_counts", engine) cursor.execute(sql, [self.request.user.id]) expense_days, all_days = cursor.fetchone() days["expense_days"] = int(expense_days) days["all_days"] = int(all_days) sql: str = self.get_query("data", engine) cursor.execute(sql, [self.request.user.id]) cat_data: typing.List[tuple] = cursor.fetchall() if days["all_days"] == 0: return no_results_to_show() user_categories: typing.Iterable[Category] = Category.user_objects( self.request) timescales = [1, 7, 30, 365] timescale_names = { 1: _("Per 1 day"), 7: _("Per week (7 days)"), 30: _("Per month (30 days)"), 365: _("Per year (365 days)"), } all_time_count = all_time_sum = 0 for _cat, at_cat_count, at_cat_sum in cat_data: all_time_count += at_cat_count all_time_sum += at_cat_sum daily_data = self.compute_daily_data(all_time_count, all_time_sum, days, days_names, timescales, timescale_names) cat_tables = self.compute_category_data(cat_data, user_categories, days, days_names, timescales, timescale_names, is_html) return days, daily_data, user_categories, cat_tables
def category_bulk_edit(request): categories = Category.user_objects(request) if request.method == "POST": added_count = 0 changed_count = 0 unchanged_count = 0 failure_count = 0 failure_list = [] for cat in categories: prefix = "cat_{}_".format(cat.pk) new_name = request.POST.get(prefix + "name") new_order = request.POST.get(prefix + "order") if new_name and new_order and new_order.isnumeric(): # can be changed new_order = int(new_order) if cat.name != new_name or cat.order != new_order: cat.name = new_name cat.order = new_order cat.save() changed_count += 1 else: unchanged_count += 1 else: failure_count += 1 failure_list.append(cat.name) additions = defaultdict(dict) print(request.POST) for k, v in request.POST.items(): if k.startswith("add_"): print(k, v) _add, aid, key = k.split("_") additions[aid][key] = v for k, fields in additions.items(): new_name = fields.get("name") new_order = fields.get("order") if new_name and new_order and new_order.isnumeric(): c = Category() c.name = new_name c.order = new_order c.user = request.user c.save() added_count += 1 else: failure_count += 1 failure_list.append("+{}/{}".format(new_name, new_order)) return render( request, "expenses/category_bulk_edit_results.html", { "htmltitle": _("Edit categories"), "title": _("Edit categories"), "pid": "category_bulk_edit_results", "added_count": added_count, "changed_count": changed_count, "unchanged_count": unchanged_count, "failure_count": failure_count, "failure_list": failure_list, }, ) return render( request, "expenses/category_bulk_edit.html", { "categories": categories, "htmltitle": _("Edit categories"), "title": _("Edit categories"), "pid": "category_bulk_edit", }, )
def search(request): opt = { "q": "", "vendor": "", "search_for": "purchases", "date_spec": "any", "date_start": "", "date_end": "" } categories = Category.user_objects(request) if "q" in request.GET or "vendor" in request.GET: opt["has_query"] = True # Set search options (will be copied into template) dict_overwrite(opt, "q", request.GET) dict_overwrite(opt, "vendor", request.GET) dict_overwrite(opt, "search_for", request.GET, "for") dict_overwrite(opt, "date_spec", request.GET, "date-spec") dict_overwrite(opt, "date_start", request.GET, "date-start") dict_overwrite(opt, "date_end", request.GET, "date-end") includes = request.GET.getlist("include", []) opt["include_expenses"] = "expenses" in includes opt["include_bills"] = "bills" in includes if request.GET.get("category_all"): cat_pks = {cat.pk for cat in categories} else: cat_pks = {int(i) for i in request.GET.getlist("category", [])} categories_with_status = [(c, c.pk in cat_pks) for c in categories] # Do the search if opt["search_for"] == "expenses": items = Expense.objects.filter( user=request.user, category__in=cat_pks).select_related("category") if opt["q"]: items = items.filter(description_cache__icontains=opt["q"]) if opt["vendor"]: items = items.filter(vendor__icontains=opt["vendor"]) if opt["include_expenses"] and opt["include_bills"]: pass elif opt["include_expenses"]: items = items.filter(is_bill=False) elif opt["include_bills"]: items = items.filter(is_bill=True) if opt["date_start"] and not opt["date_end"]: items = items.filter(date__gte=opt["date_start"]) elif opt["date_start"] and opt["date_end"]: items = items.filter(date__gte=opt["date_start"], date__lte=opt["date_end"]) items = revchron(items) elif opt["search_for"] == "billitems": items = BillItem.objects.filter( user=request.user, bill__category__in=cat_pks).select_related("bill") if opt["q"]: items = items.filter(product__icontains=opt["q"]) if opt["vendor"]: items = items.filter(bill__vendor__icontains=opt["vendor"]) if opt["date_start"] and not opt["date_end"]: items = items.filter(bill__date__gte=opt["date_start"]) elif opt["date_start"] and opt["date_end"]: items = items.filter(bill__date__gte=opt["date_start"], bill__date__lte=opt["date_end"]) items = items.order_by("-date_added") elif opt["search_for"] == "purchases": cat_pks = {int(i) for i in request.GET.getlist("category", [])} if opt["date_start"] and not opt["date_end"]: date_clause = "AND d.date >= %s" date_args = [opt["date_start"]] elif opt["date_start"] and opt["date_end"]: date_clause = "AND d.date BETWEEN %s AND %s" date_args = [opt["date_start"], opt["date_end"]] else: date_clause = "" date_args = [] ilike_word = "LIKE" if connection.settings_dict[ "ENGINE"] == "django.db.backends.sqlite3" else "ILIKE" query_clause = "" query_args = [] if opt["q"]: query_clause += " AND d.product " + ilike_word + " %s" query_args.append("%" + opt["q"] + "%") if opt["vendor"]: query_clause += " AND d.vendor " + ilike_word + " %s" query_args.append("%" + opt["vendor"] + "%") items = RawQueryWithSlicing( """ SELECT {{selected_fields}} FROM ( SELECT date, vendor, description AS product, amount AS unit_price, category_id, date_added FROM expenses_expense WHERE is_bill = false AND user_id = %s UNION SELECT date, vendor, product, unit_price, category_id, expenses_billitem.date_added FROM expenses_billitem LEFT JOIN expenses_expense ON expenses_billitem.bill_id = expenses_expense.id WHERE expenses_billitem.user_id = %s ) AS d WHERE d.category_id in ({cat_pks}) {date_clause}{query_clause} {{order_clause}} {{limit_clause}};""".format( cat_pks=", ".join(str(i) for i in cat_pks), date_clause=date_clause, query_clause=query_clause), [request.user.pk, request.user.pk] + date_args + query_args, "d.date, d.vendor, d.product, d.unit_price", "ORDER BY d.date DESC, d.date_added DESC", ) else: raise Exception("Unknown search type") else: opt["include_expenses"] = True opt["include_bills"] = True opt["has_query"] = False categories_with_status = [(c, True) for c in categories] items = None context = { "htmltitle": _("Search"), "pid": "search", "categories_with_status": categories_with_status } context.update(opt) if items is not None: paginator = Paginator(items, settings.EXPENSES_PAGE_SIZE) page = request.GET.get("page", "1") context["items"] = paginator.get_page(page) return render(request, "expenses/search.html", context)