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_amazon_mapping(mapping_id):
mapping = db.session.query(Mappings).filter(
Mappings.id == mapping_id).one()
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.mapping_id = mapping_id
new_journal_entry.transaction_source = 'amazon'
new_journal_entry.timestamp = match.shipment_date
if match.item_total > 0:
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_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_amazon_mapping(mapping_id):
mapping = db.session.query(Mappings).filter(Mappings.id == mapping_id).one()
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.mapping_id = mapping_id
new_journal_entry.transaction_source = 'amazon'
new_journal_entry.timestamp = match.shipment_date
if match.item_total > 0:
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 test_income_accrual(self):
today = datetime.now(tzlocal())
a_month_ago = today - timedelta(days=40)
income_account = 'Salary'
receivables_account = 'Accounts Receivable'
cash_account = 'Chase Checking'
amount = Decimal('100')
currency = 'USD'
expense_accrual = JournalEntries()
expense_accrual.timestamp = a_month_ago
expense_accrual.debit_subaccount = receivables_account
expense_accrual.credit_subaccount = income_account
expense_accrual.functional_amount = amount
expense_accrual.functional_currency = currency
expense_accrual.source_amount = amount
expense_accrual.source_currency = currency
db.session.add(expense_accrual)
db.session.commit()
expense_payment = JournalEntries()
expense_payment.timestamp = today
expense_payment.debit_subaccount = cash_account
expense_payment.credit_subaccount = receivables_account
expense_payment.functional_amount = amount
expense_payment.functional_currency = currency
expense_payment.source_amount = amount
expense_payment.source_currency = currency
db.session.add(expense_payment)
db.session.commit()
prior_month_balance = (
db.session.query(TrialBalances)
.filter(TrialBalances.period_interval == 'YYYY-MM')
.filter(TrialBalances.subaccount == receivables_account)
.order_by(TrialBalances.period.desc()).offset(1).limit(1).first()
)
self.assertEqual(prior_month_balance.net_balance, Decimal('100'))
current_month_balance = (
db.session.query(TrialBalances)
.filter(TrialBalances.period_interval == 'YYYY-MM')
.filter(TrialBalances.subaccount == receivables_account)
.order_by(TrialBalances.period.desc()).limit(1).first()
)
self.assertEqual(current_month_balance.net_balance, Decimal('0'))
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()