Source

quacken / budget_sync2.sql

The default branch has multiple heads

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
SET sql_safe_updates=0;
delete from budget_import;

load data infile '/home/connolly/qtrx/dm93finance/monthly-budget - 2003 H1.csv'
into table budget_import
COLUMNS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;


select * -- distinct bi.t_lo, bi.code, bi.name
from (
 select bi.*, STR_TO_DATE(bi.t_lo,'%m/%d/%Y') d_lo
 from budget_import bi ) bi
left join budgets b
on b.name=bi.budget_name
left join accounts a
on a.code=bi.code
where bi.code > ''
-- and b.guid is null
order by bi.budget_name, bi.code, t_lo
;
select * from budget_amounts;

/* *************** */
drop table if exists acct_ancestors;

create table acct_ancestors as
select a0.guid a0guid, a1.guid a1guid, a2.guid a2guid, a3.guid a3guid, a4.guid a4guid,
 concat(
 coalesce(concat(a4.name, ':'), ''),
 coalesce(concat(a3.name, ':'), ''),
 coalesce(concat(a2.name, ':'), ''),
 coalesce(concat(a1.name, ':'), ''),
 a0.name) path,
 coalesce(a4.code, a3.code, a2.code, a1.code, a0.code) code,
 coalesce(a4.account_type, a3.account_type, a2.account_type, a1.account_type, a0.account_type) account_type
from accounts a0
join books on books.root_account_guid is not null     
left join accounts a1
  on a0.parent_guid = a1.guid
 and a1.guid != books.root_account_guid
left join accounts a2
  on a1.parent_guid = a2.guid
 and a2.guid != books.root_account_guid
left join accounts a3
  on a2.parent_guid = a3.guid
 and a3.guid != books.root_account_guid
left join accounts a4
  on a3.parent_guid = a4.guid
 and a4.guid != books.root_account_guid
;

select aa.* 
from acct_ancestors aa;

drop table if exists account_closure;
create table account_closure
as
select guid, parent_guid
from (
select a0guid guid, a0guid parent_guid
from acct_ancestors
union all
select a0guid guid, a1guid parent_guid
from acct_ancestors
union all
select a0guid guid, a2guid parent_guid
from acct_ancestors
union all
select a0guid guid, a3guid parent_guid
from acct_ancestors
union all
select a0guid guid, a4guid parent_guid
from acct_ancestors) t
where t.parent_guid is not null
;
select * from account_closure;

select ac.*, aa.*, ch.name
from account_closure ac
join acct_ancestors aa
  on aa.a0guid = ac.parent_guid
join accounts ch
  on ch.guid = ac.guid
order by aa.path;

;;;;
create or replace view budget_accts
as
select
  b.name budget_name,
  r.recurrence_period_type,
  (r.recurrence_period_start
    + interval ba.period_num month) as t_lo,
/*  (r.recurrence_period_start
    + interval ba.period_num + 1 month) as t_hi,
*/
  a.guid account_guid,
  a.account_type,
  a.code,
  pa.name parent,
  a.name,
  round(ba.amount_num / ba.amount_denom, 2) budget
from
budget_amounts ba
join budgets b on b.guid = ba.budget_guid
join accounts a on ba.account_guid = a.guid
join accounts pa on a.parent_guid = pa.guid
join recurrences r
  on b.guid = r.obj_guid
;
select * from budget_accts;

select sum(amount), account_type from (
select budget_name, t_lo, account_type, code, parent, name,
case when account_type = 'EXPENSE' then -budget
else budget end amount from budget_accts
where budget_name in ('2012 Q2')
-- and recurrence_period_type = 'month'
order by code, t_lo, name
) t2
group by account_type
;

select *
from acct_ancestors aa1
join acct_ancestors aa2
where aa2.path like (concat(aa1.path, '_%')) 
-- and aa2.code = '1203'
and aa1.account_type != 'ROOT'
and aa2.account_type != 'ROOT';

select account_type, recurrence_period_type, sum(budget)
from
(select account_type, budget, recurrence_period_type
from budget_accts
where budget_name = '2012 Q1'
) t
group by account_type, recurrence_period_type
;

select * from accounts;


select budget.parent_guid, budget.y, budget.q,
       budget.code, budget.path,
       budget, actual
/*
TODO: income/expense to cashflow
       (budget.budget * if(budget.account_type in ('EXPENSE'), -1, 1)) budget,
       (coalesce(actual.actual, 0) * if(budget.account_type in ('INCOME', 'EXPENSE', 'ASSET'), -1, 1)) actual
*/
from
(
select ac.parent_guid, budget.y, budget.q,
       aa.account_type, aa.code, aa.path,
       sum(budget.budget) budget
from
(
select
  year (r.recurrence_period_start
    + interval ba.period_num month) as y,
  quarter (r.recurrence_period_start
    + interval ba.period_num month) as q,
  ba.account_guid,
  ba.amount_num / ba.amount_denom budget
from
budget_amounts ba
join budgets b on b.guid = ba.budget_guid
join recurrences r
  on b.guid = r.obj_guid
where b.name = '2012 H1'
and r.recurrence_period_type = 'month'
) budget
join account_closure ac
  on ac.guid = budget.account_guid
join acct_ancestors aa
  on aa.a0guid = ac.parent_guid
group by budget.y, budget.q, ac.parent_guid
)
budget
left join
 (
select ac.parent_guid, actual.y, actual.q, aa.path, sum(actual.actual) actual 
from
(
select year(tx.post_date) y, quarter(tx.post_date) q,
       s.account_guid,
       sum(s.value_num / s.value_denom) actual
from splits s
join transactions tx
  on s.tx_guid = tx.guid
and tx.post_date >= (
  select r.recurrence_period_start
  from budgets b
  join recurrences r
    on b.guid = r.obj_guid
  where b.name = '2012 H1')
group by year(tx.post_date), quarter(tx.post_date), s.account_guid
) actual
join account_closure ac
  on ac.guid = actual.account_guid
join acct_ancestors aa
  on aa.a0guid = ac.parent_guid
group by actual.y, actual.q, ac.parent_guid
)
actual
on budget.parent_guid = actual.parent_guid
and budget.y = actual.y
and budget.q = actual.q
order by
 budget.code, budget.path, budget.y, budget.q
;

select if(account_type in ('INCOME'), -1, 1)
from (
select 'INCOME' account_type
union all
select 'EXPENSE' account_type
) td
;

select a1.account_type, a1.name,
       a2.account_type, a2.name,
       a3.account_type, a3.name,
       a4.account_type, a4.name
from accounts a0
left join accounts a1
  on a1.parent_guid = a0.guid
left join accounts a2
  on a2.parent_guid = a1.guid
left join accounts a3
  on a3.parent_guid = a2.guid
left join accounts a4
  on a4.parent_guid = a3.guid
where a0.guid = (select root_account_guid from books)
;

select
concat(case when a4.name = 'Root Account' then '' else concat(a4.name, ':') end, 'abc')
from (select 'xyz' as name
union all
select 'Root Account'
union all
select null) a4;

select a.guid, a.account_type,
       concat(
	case when a4.name is null or a4.name = 'Root Account' then '' else concat(a4.name, ':') end,
	case when a3.name is null or a3.name = 'Root Account' then '' else concat(a3.name, ':') end,
	case when a2.name is null or a2.name = 'Root Account' then '' else concat(a2.name, ':') end,
	case when a1.name is null or a1.name = 'Root Account' then '' else concat(a1.name, ':') end) as path,
       a.name
from accounts a
left join accounts a1
  on a.parent_guid = a1.guid
left join accounts a2
  on a1.parent_guid = a2.guid
left join accounts a3
  on a2.parent_guid = a3.guid
left join accounts a4
  on a3.parent_guid = a4.guid
;

  
;;;;;;;;;;;;;;;;;;
SELECT * FROM `dm93finance`.`accounts`;

select count(*) from mint_gc_matches;

select * from accounts
where hidden=1
;

select * from tx_split_detail td
order by td.post_date desc, td.tx_guid, td.split_guid;

select * from budgets;
select a.account_type, a.name, period_num, round(amount_num / amount_denom, 2) amount
     , ba.* from budget_amounts ba
join budgets b on b.guid = ba.budget_guid
join accounts a on ba.account_guid = a.guid
order by a.account_type desc, a.name, period_num;

select * from budget_amounts;

delete ba
-- select *
from budget_amounts ba
join budgets b on b.guid = ba.budget_guid
where b.name='2012 H1';

delete b from budgets b where b.name='2012 H1';

select * from gdocs_budget;

drop table if exists budget_decisions;
create table budget_decisions as
select a.account_type, cat, subcat
     , case
       when bd.style = 'ALL' then 'ALL'
       when qty = 1 and gp.mstart = gp.mend then 'ONE'
       else 'TWO' end as periods
     , bd.*, gp.mstart, gp.mend
from (
select count(*) qty, acct_id, style, amount
from (select acct_id
      , case when period is null then 'ALL'
        else 'SOME' end style
      , amount
 from gdocs_budget) bd
group by acct_id, style, amount) bd
join (select distinct cat, subcat, acct_id from gdocs_budget) ga
on ga.acct_id = bd.acct_id
left join (select acct_id, amount, min(period) mstart, max(period) mend
      from gdocs_budget
      where period is not null
      group by acct_id, amount) gp
on gp.acct_id = bd.acct_id and gp.amount = bd.amount
join accounts a on a.code = bd.acct_id;
select * from budget_decisions
order by account_type desc, cat, subcat;

select * from budgets;

select bu.name, h1.period, a.name, bd.* from budget_decisions bd join
(
select 0 period union all
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5) h1 on h1.period = case
       when bd.periods = 'ALL' then h1.period
       when qty = 1 and bd.mstart = bd.mend then bd.mstart
       when qty = 2 and h1.period in (bd.mstart, bd.mend) then h1.period
       else -1 end
join accounts a on a.code = bd.acct_id
join budgets bu on bu.name = '2012 H1' -- (select distinct budget from gdocs_budget)
-- where bd.subcat = a.name and a.account_type not in ( 'INCOME')
order by account_type desc, cat, subcat, h1.period;



-- insert into budget_amounts
select null id
     , b.guid budget_guid
     , a.guid account_guid
     , gb.period period_num
     , gb.amount * 100 amount_num
     , 100 amount_denom
from gdocs_budget gb
join budgets b on gb.budget = b.name
left join accounts a
  on a.code = gb.acct_id
;
select distinct cat, subcat, acct_id from gdocs_budget gb;

use dm93finance;
select * from accounts;


select date_add(date '2012-04-01', interval x.n month)
from (select 1 n) x;

create or replace view Monthly_Budgets as
select b.name Budget
     , a.account_type
     , case
         when a.account_type in ('INCOME', 'LIABILITY') then 1
	else -1
       end as sign
     , case
         when a.description like '%#monthly%' then 'monthly'
	 else 'occasional'
       end as periods 
	 , pa.name Parent
     , a.name Account
     , date_add(r.recurrence_period_start, interval ba.period_num month) as period
     , ba.amount_num / ba.amount_denom amount
from recurrences r
join budgets b on b.guid = r.obj_guid
join budget_amounts ba on ba.budget_guid = b.guid
join accounts a on a.guid = ba.account_guid
join accounts pa on pa.guid = a.parent_guid
where r.recurrence_period_type='month'
;

select Budget, Parent, Account, period, periods, amount*sign amount
from Monthly_Budgets
order by budget, Account, period;

select Budget, quarter(period) Quarter, sum(sign * amount) Net
from Monthly_Budgets
group by Budget, quarter(period)
order by 1, 2;

select account_type, period, periods, sum(amount) from (
select Budget, account_type, Account, amount*sign amount, period, periods
from Monthly_Budgets
where Budget = '2012 Q1'
-- and period = date '2012-01-01'
order by amount
) monthly_expenses
group by account_type, period, periods
order by account_type, period, periods;

select account_type, Account, amount*sign amount, period, periods
from Monthly_Budgets
where Budget = '2012 Q1'
-- and period = date '2012-01-01'
and account_type in ('EXPENSE', 'LIABILITY')
and periods = 'occasional'
order by period, amount
;

select * from accounts;
select * from transactions;
select * from splits;

select *
from budget_amounts ba
join accounts a
  on a.guid = ba.account_guid
join budgets b
  on b.guid = ba.budget_guid
where b.name = '2012 Q2'
order by a.name, ba.period_num
;


@@delete ba
from budget_amounts ba
join accounts a
  on a.guid = ba.account_guid
join budgets b
  on b.guid = ba.budget_guid
where b.name = 'Copy of 2012 H1'
;

select * from budget_amounts
;
insert
into budget_amounts
 (budget_guid, account_guid, period_num, amount_num, amount_denom)
select (select guid from budgets where name = '2012 Q2') bq2, ba.account_guid, ba.period_num - 3, ba.amount_num, ba.amount_denom
from budget_amounts ba
join accounts a
  on a.guid = ba.account_guid
join budgets b
  on b.guid = ba.budget_guid
where b.name = '2012 H1'
 and ba.period_num > 2
order by a.name, ba.period_num
;


-- Flat view for export
create or replace view split_detail as
select
tx.post_date,
a.code,
coalesce(
 case when s.value_num < 0
  then a.path
  else null
end, '') account_db,
coalesce(
 case when s.value_num < 0
  then s.value_num / s.value_denom
  else null
end, '') amount_db,
coalesce(
 case when s.value_num >= 0
  then a.path
  else null
end, '') account_cr,
coalesce(
 case when s.value_num > 0
  then s.value_num / s.value_denom
  else null
end, '') amount_cr,
tx.description,
s.memo,
coalesce(slots.string_val, '') online_id,
s.guid,
tx.guid tx_guid
from transactions tx
join splits s on s.tx_guid = tx.guid
join acct_ancestors a on s.account_guid = a.a0guid
left join slots on slots.obj_guid = s.guid and slots.name = 'online_id'
;

select * from split_detail
order by post_date desc, tx_guid
;


select sd.* from split_detail sd
where timestampdiff(day, sd.post_date, current_timestamp) < 120
order by post_date desc, tx_guid
;
Tip: Filter by directory path e.g. /media app.js to search for public/media/app.js.
Tip: Use camelCasing e.g. ProjME to search for ProjectModifiedEvent.java.
Tip: Filter by extension type e.g. /repo .js to search for all .js files in the /repo directory.
Tip: Separate your search with spaces e.g. /ssh pom.xml to search for src/ssh/pom.xml.
Tip: Use ↑ and ↓ arrow keys to navigate and return to view the file.
Tip: You can also navigate files with Ctrl+j (next) and Ctrl+k (previous) and view the file with Ctrl+o.
Tip: You can also navigate files with Alt+j (next) and Alt+k (previous) and view the file with Alt+o.