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 categories_add(request):
try:
text = request.POST['text']
except KeyError:
# Redisplay the transaction voting form.
return render(request, 'expenses/categories', {
'error_message': "All fields mandatory",
})
else:
if text != '' and text != UNTAGGED_SUBCATEGORY_TEXT:
category = Category(text=text, owner=request.user)
category.save()
return HttpResponseRedirect(reverse('expenses:categories'))
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 get_response(self, request, req_data: dict):
cat = Category(name=req_data["name"], order=req_data["order"])
cat.save()
return {"success": True}, 200
def format_category(self, c: Category) -> str:
return c.html_link()
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)
def test_unicode(self):
cat = Category(name='test')
self.assertEquals(str(cat), 'test')
