Commits

Dan Connolly  committed 2b463e2

mint_re_export view is mostly working

  • Participants
  • Parent commits 9c91ff4

Comments (0)

Files changed (2)

File mint_match.sql

 -- todo: labels
 ;
 
+-- TODO: clean up transaction descriptions for 1-N splits
 update transactions tx
 join mintmatch mm on mm.tx_guid = tx.guid
 join mintexport mx on mm.mint_id = mx.id
 select round(13.6700, 2);
 
 create or replace view mint_re_export as
-select date_format(tx.post_date, '%m/%d/%Y') as date, tx.description
+select date_format(date_add(tx.post_date, interval - 1 day), '%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
      , tx.guid as tx_guid
      , sp0.guid as main_split_guid
      , sp.guid as cat_split_guid
+     , mm.mint_id -- for ordering
 from transactions tx
 join slots ofx on ofx.obj_guid = tx.guid and ofx.name = 'notes'
 join splits sp on sp.tx_guid = tx.guid
  and sp0.guid != sp.guid
 join slots ofx_id on ofx_id.name = 'online_id'
  and ofx_id.obj_guid = sp0.guid
-join accounts on accounts.guid = sp0.account_guid;
+join accounts on accounts.guid = sp0.account_guid
+left join mintmatch mm on mm.cat_split_guid = sp.guid;
 
 select * from mint_re_export
-order by post_date desc;
+order by to_days(post_date) desc, mint_id;
+
+select date, description, original_description
+     , amount, transaction_type, category, account_name
+-- TODO: labels, notes
+from mint_re_export
+order by to_days(post_date) desc, mint_id
+into outfile '/home/connolly/qtrx/dm93finance/transactions.csv'
+fields terminated by ',' enclosed by '"'
+;
 
 
 def main(argv):
+    if '--project' in argv:
+        trx_in, trx_out = argv[2:5]
+        project(open(trx_in), open(trx_out, 'w'))
+        return
+
     export_file, engine_url = argv[1:3]
     import_csv(open(export_file), sqlalchemy.create_engine(engine_url))
 
     return datetime.date(y, m, d)
 
 
+def project(lines, outfp,
+            exclude_cols=('labels', 'notes'),
+            select_accts=('PERFORMANCE CHECKING',),
+            exclude_cats=('Exclude From Mint',)):
+    inrows = csv.reader(lines)
+    cols = [c.name for c in Trx.__table__.columns][2:]  # id, num not imported
+    ax = cols.index('account_name')
+    cx = cols.index('category')
+    colx = [cols.index(c) for c in cols
+            if c not in exclude_cols]
+    log.debug('cols, colx: %s, %s', cols, colx)
+    outrows = csv.writer(outfp, quoting=csv.QUOTE_ALL,
+                         lineterminator='\n')
+    outrows.writerows([
+        [row[ix] for ix in colx] for row in inrows
+        if row[ax] in select_accts and row[cx] not in exclude_cats])
+
+
 if __name__ == '__main__':
     import sys
     logging.basicConfig(level=logging.DEBUG)