Mutual Credit v3: Views

Users have been asking for more and better statistical aggregation of transactions, but have been unable to articulate what that would look like, so I decided to concentrate on exposing everything to views, so that use can be made of exposed filters, the new GROUP BY utility, and the theme layer.

I set out to ensure that the following analysis was available to user 1, preferably in views.

• Trade indicators per user are per-currency stats such as balance, trades, volume, income, outgoing, promiscuity.
• System indicators are the per-currencies aggregates of the above
• Trade rankings, show the users with the most and least of a given indicator
• Performance over time shows the indicators between intervals such as volume per month.
• Running balance shows a user's balance after each transaction.
• Accounting periods should be supported
• Category compare needs to show the total number/volume of trade in each category
• In mutual credit, and with transaction states, the transaction history is somewhat less fixed than in conventional accounting.

After some days of experimentation, I decided to use a mySQL VIEW as virtual table which could be exposed to Drupal's views. Drupal's database API doesn't support this, but I figured it was worth it anyway.

The virtual table contains 2 rows for each transaction, seen from each user's perspective, and it contains columns designed mostly for GROUP BY functions. so the table looks like this:

The table is associated with the field API fields, similar to how the entity table itself is. This design meets all the requirements and some default views are provided which show how to make the most of this presentation of the data.