Dan Connolly avatar Dan Connolly committed f9f7358

- incremental matching
- don't update categories for transfers
- tweak dates in mint_re_export
- work around default limit 1000 in mint_re_export.csv

Comments (0)

Files changed (1)

 /* We need unique names for accounts for this work. */
 create unique index accounts_name on accounts (name(250));
 
-drop table if exists mintmatch;
 
 /* match by date, memo, amount */
-create table mintmatch as
+-- drop table if exists mintmatch;
+-- create table mintmatch as
+insert into mintmatch
 select tx.post_date
   , mx.original_description
   , (mx.amount * case when mx.transaction_type = 'debit' then -1 else 1 end) amount
   and sp.value_num = sp.value_denom * (mx.amount * case when mx.transaction_type = 'credit' then -1 else 1 end)
 join accounts on accounts.name = mx.account_name
 join accounts cat on cat.name = mx.category
+left join mintmatch mm on mm.mint_id = mx.id
+where mm.mint_id is null
 order by 1;
 
 -- TODO: fix 'Dave & Buster''s'
          and mtx.original_description = ms.original_description
         join accounts on accounts.name = mtx.account_name
         join accounts cat on cat.name = mtx.category
-        where not exists (select * from mintmatch mm where mm.mint_id = mtx.id)
+        left join mintmatch mm on mm.mint_id = mtx.id
+        where mm.mint_id is null
 order by post_date;
 
 select * from mintmatch where cat_split_guid is null;
 
 /* And now the moment we've all been waiting for...
 what are we about to update? */
-select *
+select mm.post_date, mm.original_description, cat.name ocat, mm.category, cat_split.*
 from splits cat_split
 join mintmatch mm on mm.cat_split_guid = cat_split.guid
-where cat_split.account_guid != mm.cat_guid;
+join accounts cat on cat_split.account_guid = cat.guid
+where cat.account_type in ('CASH', 'INCOME', 'EXPENSE')
+ and cat_split.account_guid != mm.cat_guid;
 
 update splits cat_split
 join mintmatch mm on mm.cat_split_guid = cat_split.guid
 join mintexport mx on mm.mint_id = mx.id
+join accounts cat on cat_split.account_guid = cat.guid
 set cat_split.account_guid = mm.cat_guid,
 cat_split.memo = mx.notes
+where cat.account_type in ('CASH', 'INCOME', 'EXPENSE')
 -- todo: labels
 ;
 
 update transactions tx
 join mintmatch mm on mm.tx_guid = tx.guid
 join mintexport mx on mm.mint_id = mx.id
-set tx.description = mx.description;
+set tx.description = mx.description
+where mm.split_qty = 1;
 
 /* manual clean-up */
 select * from mintexport mx
 
 /* reproduce mint export
 select date_format(date '2010-07-26', '%m/%d/%Y');
+select round(13.6700, 2);
  */
-select round(13.6700, 2);
 
 create or replace view mint_re_export as
-select date_format(date_add(tx.post_date, interval - 1 day), '%c/%d/%Y') as date, tx.description
+select date_format(
+       -- wierd... mint dates are off by 1 and then they're not.
+       case when tx.post_date < date '2011-04-20'
+       then date_add(tx.post_date, interval - 1 day)
+       else tx.post_date end, '%c/%d/%Y') as date
+     , tx.description
      , sp0.memo as original_description
      , round(abs(sp.value_num / sp.value_denom), 2) amount
      , case when sp.value_num > 0 then 'debit' else 'credit' end as transaction_type
 -- TODO: labels, notes
 from mint_re_export
 order by to_days(post_date) desc, mint_id
-into outfile '/home/connolly/qtrx/dm93finance/transactions.csv'
+limit 100000
+into outfile '/home/connolly/qtrx/dm93finance/mint_re_export.csv'
 fields terminated by ',' enclosed by '"'
 ;
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.