Commits

Dan Connolly committed 002aead

mint matching; excluded categories

Comments (0)

Files changed (3)

+SELECT * FROM `gnucash`.`mintexport`;
+
+use gnucash;
+
+create index trx_sig on mintexport (date, original_description(300), transaction_type, amount);
+
+select count(*), date, original_description, transaction_type, amount
+from mintexport
+group by date, original_description, transaction_type, amount
+having count(*) > 1
+order by date desc;
+
+select distinct name from slots;
+
+-- create table mintmatch as
+select tx.guid as tx_guid, sp.guid, acct.guid, acct.name, mx.id as mint_id from
+transactions tx
+join splits sp on sp.tx_guid = tx.guid
+join accounts acct on acct.guid = sp.account_guid
+join
+(
+select obj_guid, substring_index(string_val, 'Memo:', -1) as ofx_memo
+from slots
+where name = 'notes') ofx on ofx.obj_guid = tx.guid
+join mintexport mx
+on mx.original_description = ofx.ofx_memo
+  and timestampdiff(day, tx.post_date, mx.date) = 0;
+
+select count(*) from mintmatch;
+select count(*) from mintexport where account_name='PERFORMANCE CHECKING';
+select max(post_date) from transactions;
+
+select *
+from mintexport mx
+left join mintmatch mm on mx.id = mm.mint_id
+where mm.mint_id is null;
 
 Base = declarative_base()
 Session = sqlalchemy.orm.sessionmaker()
+
 Money = DECIMAL(precision=8, scale=2)
+FreeText = String(500)
+TagList = String(500)
+Name = String(500)
 
 log = logging.getLogger(__name__)
 
     __tablename__ = 'mintexport'
     id = Column(Integer, primary_key=True)
     date = Column(Date, nullable=False)
-    description = Column(String(2048))
-    original_description = Column(String(2048))
+    description = Column(FreeText)
+    original_description = Column(FreeText)
     amount = Column(Money, nullable=False)
     transaction_type = Column(Enum('debit', 'credit'))
-    category = Column(String(2048))
-    account_name = Column(String(2048))
-    labels = Column(String(2048))
-    notes = Column(String(2048))
+    category = Column(Name)
+    account_name = Column(Name)
+    labels = Column(TagList)
+    notes = Column(FreeText)
 
 
-def import_csv(lines, engine):
+def import_csv(lines, engine,
+               excluded_categories=('Exclude From Mint')):
+    Base.metadata.drop_all(engine)
     Base.metadata.create_all(engine)
 
     rows = csv.reader(lines)
     rows.next()  # skip schema
     cols = [c.name for c in Trx.__table__.columns][1:]  # id is not imported
+    cat_col = cols.index('category')
     log.debug('cols: %s', cols)
     engine.execute(Trx.__table__.insert(),
                    [dict(zip(cols, [mkdate(row[0])] + row[1:]))
-                    for row in rows])
+                    for row in rows
+                    if row[cat_col] not in excluded_categories])
 
 
 def mkdate(mm_dd_yyyy):
 import sqlalchemy
 from sqlalchemy import Column
 from sqlalchemy.ext.declarative import declarative_base
-from sqlalchemy.types import Integer, String, Boolean, Enum
+from sqlalchemy.types import Integer, String, Boolean
 
 log = logging.getLogger(__name__)