Finance Manager Website

[carnageX]

Awesome!

Hope you're not using in-line SQL that's injectable btw .

 

[S0ULphIRE]

I *think* technically it would be considered "inline" if I understand that correctly, but I don't think it's injectable :s I hope.
e.g.:

SELECT t1.account AS account, t1.value AS value, COALESCE(t2.budget,0) AS total_budget
FROM
	(SELECT public.Accounts.name AS account, SUM(public.Splits.value_num)/AVG(public.accounts.commodity_scu) AS value 
	FROM public.Accounts 
	INNER JOIN public.Splits ON public.Accounts.guid=public.Splits.account_guid 
	INNER JOIN public.transactions ON public.splits.tx_guid = public.transactions.guid
	WHERE public.Accounts.account_type = 'EXPENSE' AND public.transactions.post_date >= date_trunc('month', CURRENT_DATE)
	GROUP BY public.accounts.name) t1
LEFT JOIN
	(SELECT public.accounts.name AS account, public.budget_amounts.amount_num/public.budget_amounts.amount_denom AS budget
	FROM public.budget_amounts 
	INNER JOIN public.accounts ON public.budget_amounts.account_guid = public.accounts.guid 
	WHERE public.accounts.account_type = 'EXPENSE' AND public.budget_amounts.period_num = date_part('month', CURRENT_DATE)) t2
ON
	t1.account = t2.account
ORDER BY value DESC

 

[carnageX]

It'll really only be injectable if you're putting variables straight from user-input into the query.

Stored procedures on the database, input-value checks, etc. would help sanitize those inputs. Though, that's why I like using C# as my backend, because then I can use EntityFramework + LINQ queries against my databases, and EF sanitizes inputs automatically .

 

[S0ULphIRE]

The month rolled around today, and holey crap there was a lot of bugs waiting to surface! For a start my Monthly Expense bar graphs *completely* disappeared, took me a while to realize that's because I haven't spent anything this month yet -_- doh.

Still, I definitely want columns showing no matter what, so I've rejigged it now to permanently show any Expense accounts that have a budget set for them. If you spend something in an expense account that doesn't have a budget set, the graph will automatically update and start tracking that expenditure too.

The grey bars below represent your budget, the blue represents your actual spending, and it'll turn red past the budget level if you exceed your budget.
So far I've only spent money on Takeaway this month

Pretty much done at this point, just need to add the ability to add/remove transactions directly from the website. I'll host a live copy after that's done with some dummy data, so any interested parties can have a quick play around

Side note: I'd *really* like the last header in my table to extend over the scroll bar, but every time I try something the table explodes in a rather catastrophic fashion.
So unless an actual dev *cough*carnage*cough* wants to take a look, that's how it's going to stay
I switched from JTable to DynaTable btw! Wanted to stick with jquery only

 

[carnageX]

Looking good!

The month rolled around today, and holey crap there was a lot of bugs waiting to surface! For a start my Monthly Expense bar graphs *completely* disappeared, took me a while to realize that's because I haven't spent anything this month yet -_- doh.

Still, I definitely want columns showing no matter what, so I've rejigged it now to permanently show any Expense accounts that have a budget set for them. If you spend something in an expense account that doesn't have a budget set, the graph will automatically update and start tracking that expenditure too.

Maybe if there's an empty or null set from the server, just give the graph an empty object with the column definitions, then you shouldn't need any hacky/custom logic.

Side note: I'd *really* like the last header in my table to extend over the scroll bar, but every time I try something the table explodes in a rather catastrophic fashion.
So unless an actual dev *cough*carnage*cough* wants to take a look, that's how it's going to stay
I switched from JTable to DynaTable btw! Wanted to stick with jquery only

Lol, it's probably because of how the table styles it's columns, and the columns are dynamic and will only be within the bounds of the data.


A couple suggestions:
Make negative numbers red (standard accounting practices)
If you can, add the "View transactions" and "View details" links to the side menu.
Make the side menu collapsible/expandable.

 

[S0ULphIRE]

Maybe if there's an empty or null set from the server, just give the graph an empty object with the column definitions, then you shouldn't need any hacky/custom logic.

That's pretty much what I've done, the SQL logic is:
Get Accounts that are (Expense AND Budgeted) + Accounts that are (Expense AND Value != 0).

I didn't want to have columns for *all* expense accounts because there's currently 19 "main" expense accounts (some are split into sub accounts), so this way I just show the main acccounts that you've got a budget set for + any that you just happened to spend money in as well.
When I get time to make the "View Details" bit actually go somewhere, I'll add a 2nd page with *everything* included and visualized in a few different ways I think.

Lol, it's probably because of how the table styles it's columns, and the columns are dynamic and will only be within the bounds of the data.

Yeah probably :/ I think it's something to do with the fact that it's nested inside a bootstrap column, and I'm not applying CSS with enough finesse. Ahh well it'll do for now.

A couple suggestions:
Make negative numbers red (standard accounting practices)
If you can, add the "View transactions" and "View details" links to the side menu.
Make the side menu collapsible/expandable.

[/quote]
Thanks! Bootstrap's already got the side menu examples + expand/collapse built in so that should be easy I'll give that a shot today/tonight

 

[carnageX]

Yeah probably :/ I think it's something to do with the fact that it's nested inside a bootstrap column, and I'm not applying CSS with enough finesse. Ahh well it'll do for now.

You could possibly hack around it and put a blue background DIV with a different z-index...but that's pretty hacky .

 

[S0ULphIRE]

If I need to use another table anywhere on the page then what I've done is gonna come back and bite me in the *** probably gonna seek help on stackoverflow or somewhere, I'm *sure* I'm not the first person who's tried to add a table plugin to a bootstrap site. But yeah, that's gonna take a back seat to getting those changes you suggested done + database writes working from the page for now.

 

[carnageX]

Lol what have you done to make it difficult to use multiple tables on the page?

 

[S0ULphIRE]

This in my CSS which incidentally also breaks the responsiveness of the table. It should also be "165px * 3 columns" not 140, forgot to update that comment
*Literally* all I'm trying to do is set a max height for the table and make it scrollable if it exceeds. Apparently it's really easy to do in bootstrap with Flex now, but I couldn't get it working :/
That "display: block" bit is what makes the scroll bar work (& overflow-y), the rest is just to stop the table freaking out when I set "display:block"

table {
    width: 511px; /* 140px * 3 column + 16px scrollbar width */
    border-spacing: 0;
}

tbody, thead tr { display: block; }

tbody {
    height: 325px;
    overflow-y: auto;
    overflow-x: hidden;
}

tbody td, thead th {
    width: 165px;
}