Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
This is how I implement budgets for ledger web
Sequel.migration do
up do
create_table(:budget_periods) do
String :account
BigDecimal :amount
Date :from_date
Date :to_date
end
create_or_replace_view :budget_months, "select xtn_month, account, amount from (select distinct xtn_month from accounts_months) x cross join budget_periods where xtn_month between budget_periods.from_date and (coalesce(budget_periods.to_date, now()::date))"
end
down do
drop_view :budget_months
drop_table :budget_periods
end
end
<div class="page-header">
<h1>Budget Summary</h1>
</div>
<div class="row">
<div class="span16">
<%= table BudgetSummaryReport.run, :links => {/Month/ => '/reports/budget_detail?month=:0'} %>
</div>
</div>
class BudgetSummaryReport < LedgerWeb::Report
def self.run(limit=nil)
limit_sql = limit ? "limit #{limit.to_i}" : ""
month_where = params[:month] ? 'xtn_month = :month' : '1 = 1'
from_query("""
with budget_summary as (
select
b.xtn_month,
x.account as account,
x.amount as expense,
b.amount as budgeted
from
budget_months b
left outer join (
select
xtn_month,
account,
sum(amount) as amount
from
expenses
where
account in (select distinct account from budget_periods)
group by
xtn_month,
account
) x on x.account = b.account and x.xtn_month = b.xtn_month
order by
b.xtn_month,
x.account
)
select
xtn_month as \"Month\",
sum(budgeted) as \"Budget\",
sum(expense) as \"Spent\",
sum(budgeted) - sum(expense) as \"Diff\"
from
budget_summary
where
#{month_where}
group by
xtn_month
order by
xtn_month desc
#{limit_sql}
""")
end
end
<% default 'month', Date.today.strftime('%Y-%m-01') %>
<% @query = query do %>
select
b.account as "Account",
b.amount as "Budget",
coalesce(x.amount, 0) as "Spent",
b.amount - coalesce(x.amount, 0) as "Diff"
from
(select * from budget_months where xtn_month = :month) b
left outer join (
select
xtn_month,
account,
sum(amount) as amount
from
expenses
where
account in (select distinct account from budget_periods)
and xtn_month = :month
group by
xtn_month,
account
) x using (account)
order by
b.xtn_month,
x.account
<% end %>
<% @budget_summary = BudgetSummaryReport.run(1) %>
<%
this_month = Date.strptime(LedgerWeb::Report.params[:month], "%Y-%m-%d")
@prev_month = (this_month << 1).strftime("%Y-%m-01")
@next_month = (this_month >> 1).strftime("%Y-%m-01")
%>
<div class="page-header">
<h1>Budget Detail <small><%= LedgerWeb::Report.params[:month] %> <a href="/reports/budget_detail?month=<%= @prev_month %>">prev</a> <a href="/reports/budget_detail?month=<%= @next_month %>">next</a></small></h1>
</div>
<div class="row">
<div class="span16">
<%= table(@budget_summary) %>
</div>
</div>
<div class="row">
<div class="span16">
<%= table @query, :links => {/Account/ => "/reports/register?account=:0&month=#{LedgerWeb::Report.params[:month]}" } %>
</div>
</div>
account amount from_date to_date
Expenses:Rent 123 1970-01-01 2009-01-31
Expenses:Rent 456 2010-12-01
LedgerWeb::Config.new do |config|
config.add_hook :after_load do |db|
puts "Loading budget"
db["delete from budget_periods"].delete
path = File.join(File.dirname(ENV['LEDGER_FILE']), "budget_periods.csv")
return unless File.exists?(path)
CSV.foreach(path, :headers => true) do |row|
db[:budget_periods].insert(row.to_hash)
end
puts "Done loading budget"
end
end
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment