def apply_single_ofx_mapping(mapping_id):
mapping = db.session.query(Mappings).filter(
Mappings.id == mapping_id).one()
matched_transactions = (db.session.query(Transactions).outerjoin(
JournalEntries,
JournalEntries.transaction_id == Transactions.id).filter(
JournalEntries.transaction_id.is_(None)).filter(
func.lower(Transactions.description).like(
'%' + '%'.join(mapping.keyword.lower().split()) +
'%')).order_by(Transactions.date.desc()).all())
for transaction in matched_transactions:
new_journal_entry = JournalEntries()
new_journal_entry.transaction_id = transaction.id
new_journal_entry.transaction_source = 'ofx'
new_journal_entry.timestamp = transaction.date
if transaction.amount > 0:
new_journal_entry.debit_subaccount = transaction.account
try:
db.session.query(Subaccounts).filter(
Subaccounts.name ==
mapping.positive_credit_subaccount_id).one()
except NoResultFound:
new_subaccount = Subaccounts()
new_subaccount.name = mapping.positive_credit_subaccount_id
new_subaccount.parent = 'Discretionary Costs'
db.session.add(new_subaccount)
db.session.commit()
new_journal_entry.credit_subaccount = mapping.positive_credit_subaccount_id
elif transaction.amount < 0:
new_journal_entry.credit_subaccount = transaction.account
try:
db.session.query(Subaccounts).filter(
Subaccounts.name ==
mapping.negative_debit_subaccount_id).one()
except NoResultFound:
new_subaccount = Subaccounts()
new_subaccount.name = mapping.negative_debit_subaccount_id
new_subaccount.parent = 'Discretionary Costs'
db.session.add(new_subaccount)
db.session.commit()
new_journal_entry.debit_subaccount = mapping.negative_debit_subaccount_id
else:
raise Exception()
new_journal_entry.functional_amount = abs(transaction.amount)
new_journal_entry.functional_currency = 'USD'
new_journal_entry.source_amount = abs(transaction.amount)
new_journal_entry.source_currency = 'USD'
db.session.add(new_journal_entry)
db.session.commit()
def apply_all_mappings():
for mapping in db.session.query(Mappings).all():
matches = (db.session.query(AmazonItems)
.outerjoin(JournalEntries, JournalEntries.transaction_id == str(AmazonItems.id))
.filter(JournalEntries.transaction_id.is_(None))
.filter(or_(func.lower(AmazonItems.title).like('%' + mapping.keyword.lower() + '%'),
func.lower(AmazonItems.category_id).like('%' + mapping.keyword.lower() + '%')))
.order_by(AmazonItems.shipment_date.desc()).all())
for match in matches:
new_journal_entry = JournalEntries()
new_journal_entry.transaction_id = match.id
new_journal_entry.transaction_source = 'amazon'
new_journal_entry.timestamp = match.shipment_date
if match.amount > 0:
try:
db.session.query(Subaccounts).filter(Subaccounts.name == mapping.positive_debit_subaccount_id).one()
except NoResultFound:
new_subaccount = Subaccounts()
new_subaccount.name = mapping.positive_debit_subaccount_id
new_subaccount.parent = 'Discretionary Costs'
db.session.add(new_subaccount)
db.session.commit()
new_journal_entry.debit_subaccount = mapping.positive_debit_subaccount_id
new_journal_entry.credit_subaccount = mapping.positive_credit_subaccount_id
else:
raise Exception()
new_journal_entry.functional_amount = match.item_total
new_journal_entry.functional_currency = 'USD'
new_journal_entry.source_amount = match.item_total
new_journal_entry.source_currency = 'USD'
db.session.add(new_journal_entry)
db.session.commit()
def apply_single_ofx_mapping(mapping_id):
mapping = db.session.query(Mappings).filter(Mappings.id == mapping_id).one()
matched_transactions = (db.session.query(Transactions)
.outerjoin(JournalEntries, JournalEntries.transaction_id == Transactions.id)
.filter(JournalEntries.transaction_id.is_(None))
.filter(func.lower(Transactions.description).like('%' + '%'.join(mapping.keyword.lower().split()) + '%'))
.order_by(Transactions.date.desc()).all())
for transaction in matched_transactions:
new_journal_entry = JournalEntries()
new_journal_entry.transaction_id = transaction.id
new_journal_entry.transaction_source = 'ofx'
new_journal_entry.timestamp = transaction.date
if transaction.amount > 0:
new_journal_entry.debit_subaccount = transaction.account
try:
db.session.query(Subaccounts).filter(Subaccounts.name == mapping.positive_credit_subaccount_id).one()
except NoResultFound:
new_subaccount = Subaccounts()
new_subaccount.name = mapping.positive_credit_subaccount_id
new_subaccount.parent = 'Discretionary Costs'
db.session.add(new_subaccount)
db.session.commit()
new_journal_entry.credit_subaccount = mapping.positive_credit_subaccount_id
elif transaction.amount < 0:
new_journal_entry.credit_subaccount = transaction.account
try:
db.session.query(Subaccounts).filter(Subaccounts.name == mapping.negative_debit_subaccount_id).one()
except NoResultFound:
new_subaccount = Subaccounts()
new_subaccount.name = mapping.negative_debit_subaccount_id
new_subaccount.parent = 'Discretionary Costs'
db.session.add(new_subaccount)
db.session.commit()
new_journal_entry.debit_subaccount = mapping.negative_debit_subaccount_id
else:
raise Exception()
new_journal_entry.functional_amount = abs(transaction.amount)
new_journal_entry.functional_currency = 'USD'
new_journal_entry.source_amount = abs(transaction.amount)
new_journal_entry.source_currency = 'USD'
db.session.add(new_journal_entry)
db.session.commit()
def populate_chart_of_accounts():
chart_of_accounts_csv = os.path.join(os.path.dirname(__file__), 'Generic Chart of Accounts.csv')
with open(chart_of_accounts_csv) as csv_file:
reader = csv.reader(csv_file)
rows = [pair for pair in reader]
header = rows.pop(0)
for row in rows:
line = zip(header, row)
line = dict(line)
element = Elements()
element.name = line['Element']
try:
db.session.add(element)
db.session.commit()
except IntegrityError:
db.session.rollback()
classification = Classifications()
classification.name = line['Classification']
classification.parent = line['Element']
try:
db.session.add(classification)
db.session.commit()
except IntegrityError:
db.session.rollback()
account = Accounts()
account.name = line['Account']
account.cash_source = line['Cash Source']
account.parent = line['Classification']
try:
db.session.add(account)
db.session.commit()
except IntegrityError:
db.session.rollback()
subaccount = Subaccounts()
subaccount.name = line['Subaccount']
subaccount.parent = line['Account']
try:
db.session.add(subaccount)
db.session.commit()
except IntegrityError:
db.session.rollback()
return True
def populate_chart_of_accounts():
chart_of_accounts_csv = os.path.join(os.path.dirname(__file__), 'Generic Chart of Accounts.csv')
with open(chart_of_accounts_csv) as csv_file:
reader = csv.reader(csv_file)
rows = [pair for pair in reader]
header = rows.pop(0)
for row in rows:
line = zip(header, row)
line = dict(line)
element = Elements()
element.name = line['Element']
try:
db.session.add(element)
db.session.commit()
except IntegrityError:
db.session.rollback()
classification = Classifications()
classification.name = line['Classification']
classification.parent = line['Element']
try:
db.session.add(classification)
db.session.commit()
except IntegrityError:
db.session.rollback()
account = Accounts()
account.name = line['Account']
account.cash_source = line['Cash Source']
account.parent = line['Classification']
try:
db.session.add(account)
db.session.commit()
except IntegrityError:
db.session.rollback()
subaccount = Subaccounts()
subaccount.name = line['Subaccount']
subaccount.parent = line['Account']
try:
db.session.add(subaccount)
db.session.commit()
except IntegrityError:
db.session.rollback()
return True
def apply_all_mappings():
for mapping in db.session.query(Mappings).all():
matches = (db.session.query(AmazonItems).outerjoin(
JournalEntries,
JournalEntries.transaction_id == str(AmazonItems.id)).filter(
JournalEntries.transaction_id.is_(None)).filter(
or_(
func.lower(
AmazonItems.title).like('%' +
mapping.keyword.lower() +
'%'),
func.lower(AmazonItems.category_id).like(
'%' + mapping.keyword.lower() + '%'))).order_by(
AmazonItems.shipment_date.desc()).all())
for match in matches:
new_journal_entry = JournalEntries()
new_journal_entry.transaction_id = match.id
new_journal_entry.transaction_source = 'amazon'
new_journal_entry.timestamp = match.shipment_date
if match.amount > 0:
try:
db.session.query(Subaccounts).filter(
Subaccounts.name ==
mapping.positive_debit_subaccount_id).one()
except NoResultFound:
new_subaccount = Subaccounts()
new_subaccount.name = mapping.positive_debit_subaccount_id
new_subaccount.parent = 'Discretionary Costs'
db.session.add(new_subaccount)
db.session.commit()
new_journal_entry.debit_subaccount = mapping.positive_debit_subaccount_id
new_journal_entry.credit_subaccount = mapping.positive_credit_subaccount_id
else:
raise Exception()
new_journal_entry.functional_amount = match.item_total
new_journal_entry.functional_currency = 'USD'
new_journal_entry.source_amount = match.item_total
new_journal_entry.source_currency = 'USD'
db.session.add(new_journal_entry)
db.session.commit()