Example #1
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()
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()
Example #3
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()
Example #4
0
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
Example #5
0
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
Example #6
0
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()