root/trac/hacks/marketplugin/0.9/tracmarket/ledger.py

# Ledger plugin

import time

from trac.core import *
from trac.db import Table, Column, Index
from trac.env import IEnvironmentSetupParticipant
from trac.web.chrome import INavigationContributor, ITemplateProvider, add_stylesheet
from trac.web.main import IRequestHandler
from trac.util import escape, Markup

from tracmarket.api import *
from tracmarket.db import *
from tracmarket.util import *

class Ledger(Component):
    implements(ILedger)

    # ILedger methods

    def post(self, db, author, legs, memo=''):
        """Record a transaction; 'legs' is a list of transaction legs
        that have been created using mkleg().  Memo is a string.  Legs
        can be mixed-currency.  Ensures debits and credits are
        balanced within each currency.  Returns integer transaction
        id."""
        env = self.env
        x = Transaction(env, db, author=author, legs=legs, memo=memo)
        x.insert()
        return x.id

    def mkleg(self, db,
            entity, account, symbol, debit=None, credit=None, serial=None):
        """Convenience method to help create a proper transaction leg.
        Does no storage.  Returns an object suitable for sending to
        post().  Entity is an entity string.  Account is an account name
        string.  Symbol is a string, and can be any symbol previously
        defined by mksymbol().  Serial is an optional string, and is 
        used to designate a unique instance of symbol to be transferred, 
        e.g. equipment, stock certificate, or banknote serial number."""
        env = self.env
        debit = Price(debit)
        credit = Price(credit)
        acct = Account.selectone(env, db, entity=entity, account=account)
        if not acct:
            raise MissingAccount, dict(entity=entity, account=account)
        if (debit and credit) \
                or not (debit or credit) \
                or (debit and debit < 0) \
                or (credit and credit < 0):
            raise ValueError("%s %s %s debit=%s, credit=%s" % 
                    (entity, account, symbol, debit, credit))
        balance = acct.balance(symbol)
        # print "mkleg balance", entity, account, symbol, debit, credit, balance
        if credit and acct.normal == 'debit' and balance < credit:
            raise InsufficientFunds, dict(entity=entity, account=account,
                    symbol=symbol, balance=balance, debit=debit,
                    credit=credit, amount=credit)
        if debit and acct.normal == 'credit' and balance < debit:
            raise InsufficientFunds, dict(entity=entity, account=account,
                    symbol=symbol, balance=balance, debit=debit,
                    credit=credit, amount=debit)
        leg = Leg(env, db,
                 entity=entity,
                 account=account,
                 symbol=symbol,
                 debit=debit,
                 credit=credit,
                 serial=serial,
                 )
        return leg

    def mkaccount(self, db, entity, account, type, description=None):
        """Create a currency or inventory account for entity.  Account
        is an account name string.  Description is a string.  Returns
        silently if account already exists and is same type."""
        acct = Account.selectone(self.env, db, entity=entity, account=account)
        if acct and acct.type == type:
            return
        acct = Account(self.env, db,
            entity=entity, account=account, description=description, type=type)
        acct.insert()

    def getacct(self, db, entity, account):
        return Account.selectone(self.env, db, entity=entity, account=account)

    def history(self, db, entity=None, account=None, symbol=None,
            start_time=None, end_time=None, limit=None, desc=False):
        """Return a generator of transaction objects, sorted by
        transaction id."""
        kwargs = dict(
                    entity=entity,
                    account=account,
                    symbol=symbol,
                    limit=limit,
                    desc=desc,
                )
        args = []
        if start_time:
            args.append(dict(time=('>', start_time)))
        if end_time:
            args.append(dict(time=('>', end_time)))
        gen = Transaction.select(self.env, db, *args, **kwargs)
        return gen

    def last(self, db, symbol, denomination, moving_average=None):
        """Return last trade price and time for symbol, denominated in
        'denomination' units.  If specified, moving_average is a
        floating point number of days to use in calculating a
        volume-weighted moving average price."""
        assert not moving_average
        h = self.history(db, symbol=symbol, desc=True)
        for x in h:
            legs = list(x.legs)
            if len(legs) != 4:
                continue
            qty = None
            price = None
            for l in legs:
                if l.debit and l.symbol == denomination:
                    price = l.debit
                if l.debit and l.symbol == symbol:
                    qty = l.debit
            if qty and price:
                return price/qty
        return None

    def balance(self, db, entity, account, symbol, serial=None):
        """Return quantity of symbol in account."""
        env = self.env
        return AccountBalance.balance(env, db, entity=entity, 
                account=account, symbol=symbol, serial=serial)

    def balances(self, db, entity=None, account=None, symbol=None,
            serial=None):
        """Return quantity of symbol in all accounts which match kwargs."""
        assert not serial
        env = self.env
        s = Select(env, db, AccountBalance,
            columns='entity,account,symbol,debit,credit,normal',
            entity=entity, account=account, symbol=symbol)
        for row in s.rows:
            if row.normal == 'debit':
                balance = row.debit - row.credit
            else:
                balance = row.credit - row.debit
            yield row.entity,row.account,row.symbol,Price(balance)

    def normal(self, db, entity, account):
        """Return normal balance of account as 'debit' or 'credit' string."""
        env = self.env
        acct = Account.selectone(env, db, entity=entity, account=account)
        if not acct:
            raise MissingAccount, dict(entity=entity, account=account)
        return acct.normal
        
    def accounts(self, db, entity=None):
        '''List accounts.  Returns a generator of account objects.'''
        env = self.env
        accts = Account.select(env, db, entity=entity, order='entity,account')
        return accts

    def symbols(self, db, entity=None, account=None):
        '''List symbols from ledger.  Returns a generator of sorted, unique
        symbol names.'''
        names = []
        s = Select(self.env, db, Leg, columns='distinct symbol',
                entity=entity, account=account, order='symbol')
        for row in s.rows:
            yield row.symbol

class Account(DbObject):

    VERSION = 1
    TABLE = Table('account', key=('entity', 'account'))[
                Column('id', type='integer', auto_increment=True),
                Column('entity'),
                Column('account'),
                Column('description'),
                Column('type')
            ]
    NAME = TABLE.name
    COLUMNS = [ c.name for c in TABLE.columns ]

    def _ck_values(self):
        self._ignore('id')
        # check account type
        self.normal

    def normal(self):
        # Account Type    Debit(+)    Credit(-) 
        # ASSETS          Increases   Decreases 
        # LIABILITIES     Decreases   Increases 
        # EQUITY          Decreases   Increases 
        # INCOME          Decreases   Increases 
        # EXPENSES        Increases   Decreases 
        normal = {
            'asset': 'debit',
            'liability': 'credit',
            'equity': 'credit',
            'income': 'credit',
            'expense': 'debit',
        }
        normal = normal.get(self['type'], None)
        if normal is None:
            raise ValueError("invalid account type: %s" % self['type'])
        return normal
    normal = property(normal)

    def balance(self, symbol):
        # bal = AccountBalance
        return AccountBalance.balance(self._env, self._db, 
            entity=self.entity, account=self.account, symbol=symbol)
        
class AccountBalance(DbObject):

    VERSION = 1
    TABLE = Table('account_balance', key=('entity', 'account', 'symbol'))[
                Column('id', type='integer', auto_increment=True),
                Column('entity'),
                Column('account'),
                Column('symbol'),
                Column('serial'),
                Column('debit', type='numeric'),
                Column('credit', type='numeric'),
                Column('normal'),
            ]
    NAME = TABLE.name
    COLUMNS = [ c.name for c in TABLE.columns ]

    def _set_defaults(self):
        self.chattr(debit=0, credit=0)

    def _ck_values(self):
        self._ignore('id','serial')

    def balance(cls, env, db,
            entity=None, account=None, symbol=None, serial=None):
        assert not serial
        bal = cls.selectone(env, db, 
                entity=entity, account=account, symbol=symbol)
        if not bal:
            return 0.0
        if bal.normal == 'debit':
            balance = bal.debit - bal.credit
        else:
            balance = bal.credit - bal.debit
        return Price(balance)
    balance = classmethod(balance)

class Leg(DbObject):

    VERSION = 1
    TABLE = Table('leg', key=('id'))[
                Column('id', type='integer', auto_increment=True),
                Column('xid', type='integer'),
                Column('entity'),
                Column('account'),
                Column('debit', type='numeric'),
                Column('credit', type='numeric'),
                Column('symbol'),
                Column('serial'),
            ]
    NAME = TABLE.name
    COLUMNS = [ c.name for c in TABLE.columns ]

    def __str__(self):
        if self.debit:
            s = "%4s %15s %15s %6.2f %8s %s\n" % (
                    (self.id, self.entity, self.account, self.debit,
                        '', self.symbol))
        else:
            s = "%4s %15s %15s %8s %6.2f %s\n" % (
                    (self.id, self.entity, self.account, '',
                        self.credit, self.symbol))
        return s

    def _set_defaults(self):
        self.chattr(debit=0, credit=0)

    def _ck_values(self):
        self._ignore('id', 'serial')

class Transaction(DbObject):

    VERSION = 1
    TABLE = Table('ledger', key=('id'))[
                Column('id', type='integer', auto_increment=True),
                Column('memo'),
                Column('author'),
                Column('ref_site'),
                Column('ref_type'),
                Column('ref_id'),
                Column('time', type='numeric')
            ]
    NAME = TABLE.name
    COLUMNS = [ c.name for c in TABLE.columns ]

    def __str__(self):
        s = "%4s %s\n\t%s" % (self.id, self.memo,
                '\t'.join([ str(leg) for leg in self._legs ]))
        return s

    def _set_defaults(self):
        self.chattr(memo='', time=time.time())
        self._legs = []
        self.readonly = False 

    def _ck_values(self):
        self._ignore('id', 'ref_site', 'ref_type', 'ref_id')
        legs = self.pop('legs', None)
        if legs:
            assert not self._legs
            self._legs = legs
        # if not self._legs:
        #     raise ValueError("missing legs: \n %s" % str(self))
        if not self.balanced():
            raise ValueError("unbalanced transaction: \n %s" % str(self))

    def leg(self, n):
        return self._legs[n]

    def legs(self):
        for leg in self._legs:
            yield leg
    legs = property(legs)

    def select(cls, env, db, append=None, parms=None, desc=False, **kwargs):
        xtable = cls.NAME
        ltable = Leg.NAME
        if desc:
            order = "%s.id DESC, %s.id" % (xtable, ltable)
        else:
            order = "%s.id, %s.id" % (xtable, ltable)
        xcolumns = cls.COLUMNS
        lcolumns = Leg.COLUMNS
        xkwargs = {}
        lkwargs = {}
        if kwargs:
            for var, val in kwargs.items():
                if var in xcolumns:
                    xkwargs[var] = val
                if var in lcolumns:
                    lkwargs[var] = val
            lkwargs.pop('id', None)
        if not append:
            append = ''
        append += "%s.id = %s.xid" % (xtable, ltable)
        if lkwargs:
            lselect = Select(env, db, Leg, columns='xid', **lkwargs)
            xselect = Select(env, db, (Transaction, Leg),
                    id=('IN', lselect),
                    append=append, parms=parms, order=order, **xkwargs)
        else:
            xselect = Select(env, db, (Transaction, Leg),
                    append=append, parms=parms, order=order, **xkwargs)
        cursor = xselect.cursor()
        transaction = None
        state = 'build'
        while True:
            row = cursor.fetchone() 
            if not row: 
                state = 'end'
            elif transaction and transaction.id != row.get("%s.xid" % ltable):
                state = 'send'
            if state in ('send', 'end'):
                if transaction and transaction.balanced():
                    transaction.readonly = True
                    yield transaction
                transaction = None
            if state == 'end':
                raise StopIteration
            if not transaction:
                kw = {}
                for var in xcolumns:
                    kw[var] = row.get('%s.%s' % (xtable, var))
                transaction = cls(env, db, **kw)
            kw = {}
            for var in lcolumns:
                kw[var] = row.get('%s.%s' % (ltable, var))
            leg = Leg(env, db, **kw)
            transaction._legs.append(leg)
            state = 'build'
    select = classmethod(select)

    def insert(self, db=None, cursor=None):
        assert not self.readonly
        env = self._env
        if not db:
            db = self._db
        if not cursor:
            cursor = db.cursor()
        self.pop('id', None)
        # import pdb; pdb.set_trace()
        super(Transaction, self).insert(
                db=self._db, cursor=cursor)
        self['id'] = self._db.get_last_id(cursor, self.NAME)
        for leg in self._legs:
            leg.chattr(xid=self.id)
            leg.insert(db=self._db)
            bal = AccountBalance.selectone(env, db, 
                entity=leg.entity, account=leg.account, symbol=leg.symbol)
            if not bal:
                # create balance record
                columns = 'sum(debit) as d, sum(credit) as c'
                s = Select(env, db, Leg, columns=columns,
                    entity=leg.entity, account=leg.account,
                    symbol=leg.symbol)
                debit = 0
                credit = 0
                rows = list(s.rows)
                assert len(rows) == 1
                for row in rows:
                    debit = row.d
                    credit = row.c
                acct = Account.selectone(env, db, entity=leg.entity,
                        account=leg.account)
                if not acct:
                    raise MissingAccount, dict(entity=leg.entity,
                            account=leg.account)
                normal = acct.normal
                bal = AccountBalance(env, db, 
                    entity=leg.entity, account=leg.account, symbol=leg.symbol,
                    debit=debit, credit=credit, normal=normal)
                bal.insert(db=db)
            else:
                # update balance record
                set = ["debit=debit+%s", "credit=credit+%s"]
                parms = [leg.debit, leg.credit]
                count = bal.update(set=set, parms=parms)
                assert count == 1
        self.readonly = True

    def balanced(self):
        debits = {}
        credits = {}
        symbols = {}
        for leg in self._legs:
            symbol = leg.symbol
            symbols[symbol] = 1
            debits.setdefault(symbol, 0)
            credits.setdefault(symbol, 0)
            debits[symbol] += leg.debit
            credits[symbol] += leg.credit
        for s in symbols.keys():
            if debits[s] != credits[s]:
                return False
        return True


    def addleg(self, leg=None, **kwargs):
        assert not self.readonly
        if leg is None:
            leg = Leg(self._env, self._db, **kwargs)
        self._legs.append(leg)


class InitLedger(InitDB):
    implements(IEnvironmentSetupParticipant)
    dbobjects = (Account, AccountBalance, Leg, Transaction)