def sync_ofx():
for connection in db.session.query(Connections).filter(Connections.source == 'ofx').all():
if connection.type in ['Checking', 'Savings']:
try:
start, = (db.session.query(Transactions.date)
.join(AccountsFrom, Transactions.account_id == AccountsFrom.id)
.filter(BankAccounts.acctid == connection.account_number)
.order_by(Transactions.date.desc()).first())
start = start.date()
end = date.today()
except TypeError:
start = None
end = None
account = BankAcct(connection.routing_number, connection.account_number, connection.type)
elif connection.type == 'Credit Card':
try:
start, = (db.session.query(Transactions.date)
.join(AccountsFrom, Transactions.account_id == AccountsFrom.id)
.join(CreditCardAccounts, CreditCardAccounts.id == AccountsFrom.id)
.filter(CreditCardAccounts.acctid == connection.account_number)
.order_by(Transactions.date.desc()).first())
start = start.date()
end = date.today()
except TypeError:
start = None
end = None
account = CcAcct(connection.account_number)
else:
raise Exception('Unrecognized account/connection type: {0}'.format(connection.type))
ofx_client = OFXClient(connection.url, connection.org, connection.fid)
if start and end:
statement_request = ofx_client.statement_request(connection.user, connection.password, connection.clientuid,
[account], dtstart=start, dtend=end)
else:
statement_request = ofx_client.statement_request(connection.user, connection.password,
connection.clientuid, [account])
response = ofx_client.download(statement_request)
new_response = ConnectionResponses()
new_response.connection_id = connection.id
new_response.connected_at = datetime.now(tzlocal())
new_response.response = response.read()
db.session.add(new_response)
db.session.commit()
response.seek(0)
parser = OFXParser()
engine = create_engine(current_app.config['SQLALCHEMY_DATABASE_URI'], echo=False)
DBSession.configure(bind=engine)
parser.parse(response)
parser.instantiate()
DBSession.commit()
connection.synced_at = datetime.now(tzlocal())
db.session.commit()
for account in db.session.query(AccountsFrom).filter(AccountsFrom.name.is_(None)).all():
account.name = ''
db.session.commit()
def import_ofx():
from pacioli.functions.ofx_functions import fix_ofx_file
directory = os.path.abspath(os.path.join('configuration_files', 'data'))
files = [ofx_file for ofx_file in os.listdir(directory) if ofx_file.endswith(('.ofx', '.OFX', '.qfx', '.QFX'))]
for ofx_file_name in files:
ofx_file_path = os.path.join(directory, ofx_file_name)
parser = OFXParser()
engine = create_engine(current_app.config['SQLALCHEMY_DATABASE_URI'], echo=False)
DBSession.configure(bind=engine)
if 'fixed' not in ofx_file_path:
ofx_file_path = fix_ofx_file(ofx_file_path)
parser.parse(ofx_file_path)
parser.instantiate()
DBSession.commit()
def import_ofx():
from pacioli.functions.ofx_functions import fix_ofx_file
directory = os.path.abspath(os.path.join('configuration_files', 'data'))
files = [ofx_file for ofx_file in os.listdir(directory) if ofx_file.endswith(('.ofx', '.OFX', '.qfx', '.QFX'))]
for ofx_file_name in files:
ofx_file_path = os.path.join(directory, ofx_file_name)
parser = OFXParser()
engine = create_engine(current_app.config['SQLALCHEMY_DATABASE_URI'], echo=False)
DBSession = scoped_session(sessionmaker())
DBSession.configure(bind=engine)
if 'fixed' not in ofx_file_path:
ofx_file_path = fix_ofx_file(ofx_file_path)
parser.parse(ofx_file_path)
parser.instantiate()
DBSession.commit()
def ofx_to_database(filename):
with sessionmanager() as DBSession:
parser = OFXParser()
parser.parse(filename)
parser.instantiate()
def log(message, end='\n'):
print(message, end=end)
sys.stdout.flush()
if __name__ == '__main__':
# CLI arg handling
parser = argparse.ArgumentParser()
parser.add_argument('files', nargs='+')
parser.add_argument('-v', '--verbose', action='store_true')
parser.add_argument('--output',
default='sqlite:///test.db',
help='Destination database URI')
args = parser.parse_args()
# DB setup
engine = create_engine(args.output, echo=args.verbose)
DBSession.configure(bind=engine)
Base.metadata.create_all(engine)
parser = OFXParser()
for filename in args.files:
log('Parsing "{}"...'.format(filename), end='')
parser.parse(filename)
log('done. Commiting to database...', end='')
parser.instantiate()
DBSession.commit()
log('done!')
def sync_ofx_connection(connection):
if connection.type in ['Checking', 'Savings']:
filter_boolean = BankAccounts.acctid == connection.account_number
account = BankAcct(connection.routing_number,
connection.account_number, connection.type)
elif connection.type == 'Credit Card':
filter_boolean = CreditCardAccounts.acctid == connection.account_number
account = CcAcct(connection.account_number)
else:
raise Exception('Unrecognized account/'
'connection type: {0}'.format(connection.type))
start, = (db.session.query(
Transactions.date).filter(filter_boolean).order_by(
Transactions.date.desc()).first())
if start:
start = start.date()
end = date.today()
else:
start = None
end = None
# start = start.join(AccountsFrom,
# Transactions.account_id == AccountsFrom.id)
# elif connection.type == 'Credit Card':
# start = db.session.query(Transactions.date)
# start = start.join(AccountsFrom, Transactions.account_id == AccountsFrom.id)
# start = start.filter(CreditCardAccounts.acctid == connection.account_number)
# start = start.order_by(Transactions.date.desc())
# start, = start.first()
# if start:
# start = start.date()
# end = date.today()
# else:
# start = None
# end = None
ofx_client = OFXClient(connection.url,
connection.org,
connection.fid,
version=220,
appid='QWIN',
appver='2500')
if start and end:
statement_request = ofx_client.statement_request(
connection.user,
connection.password, [account],
clientuid=connection.clientuid,
dtstart=start,
dtend=end)
else:
statement_request = ofx_client.statement_request(
connection.user, connection.password, connection.clientuid,
[account])
response = ofx_client.download(statement_request)
new_response = ConnectionResponses()
new_response.connection_id = connection.id
new_response.connected_at = datetime.now(tzlocal())
new_response.response = response.read()
db.session.add(new_response)
db.session.commit()
if '<SEVERITY>ERROR' in new_response.response:
status = new_response.response.lower().title()
send_error_message(status)
return False
response.seek(0)
ofx_session = get_session(current_app.config['SQLALCHEMY_DATABASE_URI'],
'ofx')
parser = OFXParser()
parser.parse(response)
parser.instantiate()
ofx_session.commit()
connection.synced_at = datetime.now(tzlocal())
db.session.commit()
return True
# 3rd party libraries
from sqlalchemy import create_engine
# local imports
from ofxtools.ofxalchemy import (
DBSession,
Base,
OFXParser,
)
if __name__ == "__main__":
# CLI arg handling
parser = argparse.ArgumentParser()
parser.add_argument('files', nargs='+')
parser.add_argument('-v', '--verbose', action='store_true')
args = parser.parse_args()
# DB setup
engine = create_engine('sqlite:///test.db', echo=args.verbose)
DBSession.configure(bind=engine)
Base.metadata.create_all(engine)
parser = OFXParser()
for f in args.files:
print("Parsing %s" % f)
parser.parse(f)
parser.instantiate()
DBSession.commit()
from ofxtools.ofxalchemy import Base, DBSession, OFXParser
def log(message, end='\n'):
print(message, end=end)
sys.stdout.flush()
if __name__ == '__main__':
# CLI arg handling
parser = argparse.ArgumentParser()
parser.add_argument('files', nargs='+')
parser.add_argument('-v', '--verbose', action='store_true')
parser.add_argument('--output', default='sqlite:///test.db',
help='Destination database URI')
args = parser.parse_args()
# DB setup
engine = create_engine(args.output, echo=args.verbose)
DBSession.configure(bind=engine)
Base.metadata.create_all(engine)
parser = OFXParser()
for filename in args.files:
log('Parsing "{}"...'.format(filename), end='')
parser.parse(filename)
log('done. Commiting to database...', end='')
parser.instantiate()
DBSession.commit()
log('done!')
def ofx_to_database(filename):
parser = OFXParser()
parser.parse(filename)
parser.instantiate()
DBSession.commit()
from sqlalchemy import create_engine
# local imports
from ofxtools.ofxalchemy import (
DBSession,
Base,
OFXParser,
)
if __name__ == "__main__":
# CLI arg handling
parser = argparse.ArgumentParser()
parser.add_argument('files', nargs='+')
parser.add_argument('-v', '--verbose', action='store_true')
args = parser.parse_args()
# DB setup
engine = create_engine('sqlite:///test.db', echo=args.verbose)
DBSession.configure(bind=engine)
Base.metadata.create_all(engine)
parser = OFXParser()
for f in args.files:
print("Parsing %s" % f)
parser.parse(f)
parser.instantiate()
DBSession.commit()
def ofx_to_database(filename):
parser = OFXParser()
parser.parse(filename)
parser.instantiate()
DBSession.commit()
def ofx_to_database(filename):
with session_scope(Session) as DBSession:
parser = OFXParser()
parser.parse(filename)
parser.instantiate(DBSession)
def sync_ofx_connection(connection):
if connection.type in ['Checking', 'Savings']:
filter_boolean = BankAccounts.acctid == connection.account_number
account = BankAcct(connection.routing_number,
connection.account_number,
connection.type)
elif connection.type == 'Credit Card':
filter_boolean = CreditCardAccounts.acctid == connection.account_number
account = CcAcct(connection.account_number)
else:
raise Exception('Unrecognized account/'
'connection type: {0}'.format(connection.type))
start, = (db.session.query(Transactions.date)
.filter(filter_boolean)
.order_by(Transactions.date.desc())
.first())
if start:
start = start.date()
end = date.today()
else:
start = None
end = None
# start = start.join(AccountsFrom,
# Transactions.account_id == AccountsFrom.id)
# elif connection.type == 'Credit Card':
# start = db.session.query(Transactions.date)
# start = start.join(AccountsFrom, Transactions.account_id == AccountsFrom.id)
# start = start.filter(CreditCardAccounts.acctid == connection.account_number)
# start = start.order_by(Transactions.date.desc())
# start, = start.first()
# if start:
# start = start.date()
# end = date.today()
# else:
# start = None
# end = None
ofx_client = OFXClient(connection.url, connection.org, connection.fid)
if start and end:
statement_request = ofx_client.statement_request(connection.user,
connection.password,
connection.clientuid,
[account],
dtstart=start,
dtend=end)
else:
statement_request = ofx_client.statement_request(connection.user,
connection.password,
connection.clientuid,
[account])
response = ofx_client.download(statement_request)
new_response = ConnectionResponses()
new_response.connection_id = connection.id
new_response.connected_at = datetime.now(tzlocal())
new_response.response = response.read()
db.session.add(new_response)
db.session.commit()
if '<SEVERITY>ERROR' in new_response.response:
status = new_response.response.lower().title()
send_error_message(status)
return False
response.seek(0)
parser = OFXParser()
engine = create_engine(current_app.config['SQLALCHEMY_DATABASE_URI'],
echo=False)
DBSession.configure(bind=engine)
parser.parse(response)
parser.instantiate()
DBSession.commit()
connection.synced_at = datetime.now(tzlocal())
db.session.commit()
return True
