[META] Ledger dev version release updates

I updated the default Views provided with the module to work with the latest releases of Entity and Views. Turns out I was using outdated modules on my dev site. So when you grab the latest DEV release, be sure to "Revert" both Views so they use the new code.

Thanks to Beakerboy for reporting the issue.

I just applied a patch provided by coreyp_1 that adds the ability to edit fields that are added to Account Type entities within the Account form. See #1481302: Ledger Accounts not allowing for custom entity fields to be added

The patch also changes the name of the 'name' column in the {ledger_account_type} table to 'type', which makes more sense.

The easiest way to update is with the "Execute PHP" page of the Devel module (/devel/php). Open that up BEFORE you upgrade your code, then replace the Ledger code with the latest DEV, and run the following code to clear your cache table and update your database:

<?php
cache_clear_all();
db_query('ALTER TABLE {ledger_account_type} CHANGE name type varchar (32)');
?>

You can also clear the cache table and change the column name manually in the database.

I've converted Ledger's value storage mechanism from a single float column in the database to two integer columns: value_num, and value_denom. See #1477534: Store values in integer form to avoid issues with float rounding.

To update your database, use the following code in the Devel module's Execute PHP page (/devel/php):

<?php
db_query('UPDATE {ledger_account_entry} SET value = value * 100');
db_query('ALTER TABLE {ledger_account_entry} CHANGE value value_num BIGINT DEFAULT 0 NOT NULL');
db_query('ALTER TABLE {ledger_account_entry} ADD value_denom BIGINT DEFAULT 1 NOT NULL');
db_query('UPDATE {ledger_account_entry} SET value_denom = 100');
cache_clear_all();
// revert views
?>

If you've already installed the Budget module, the same changes have been made to it, so use the following code to update it's databases as well:

<?php
db_query('UPDATE {ledger_budget} SET value = value * 100');
db_query('ALTER TABLE {ledger_budget} CHANGE value value_num BIGINT DEFAULT 0 NOT NULL');
db_query('ALTER TABLE {ledger_budget} ADD value_denom BIGINT DEFAULT 1 NOT NULL');
db_query('UPDATE {ledger_budget} SET value_denom = 100');
cache_clear_all();
// revert views
?>

Make sure you revert any existing Views (ledger_accounts, ledger_transactions, ledger_budgets), because changes have been made to all of them to use the new value storage. And of course... clear your cache.

You're right! My bad. I've updated comment #3 to reflect that. Thanks!

I added an "account balance" field to account entries, so you can see the balance of an account at the time of the account entry (as opposed to the overall account balance that just shows you the current balance). To update your database, you just need to add two columns, and fill them in by running the new function that calculates the balances.

The code below can be run in the Devel module's Execute PHP form to do the updates.

<?php

// Add new balance_num and balance_denom columns.
db_query('ALTER TABLE {ledger_account_entry} ADD balance_num BIGINT DEFAULT 0 NOT NULL');
db_query('ALTER TABLE {ledger_account_entry} ADD balance_denom BIGINT DEFAULT 1 NOT NULL');

// Clear all the caches
cache_clear_all();

// Calculate all the account entry balances.
ledger_account_entry_calculate_balances();

?>

The new account entry balance field is included in the default Transactions by Account View. So just revert your ledger_transaction View and you should see it (or add it manually).

I added a 'fundamental' column to the {ledger_account_type} table for defining the fundamental account type, which must be one of the following: asset, liability, equity, income, expense (or 0). These will be used for reporting purposes. I updated the ledger_account_types module to include fundamental account types with the five provided account types. So revert those account types to get the change.

I also added a new account settings page at /admin/ledger/settings/account, with a new setting for changing the way certain account balances are displayed. Ledger stores credits to liability, equity, and income accounts as negative numbers, which is counter-intuitive if you're coming to Ledger from a traditional accounting background. So this setting allows you to reverse the sign on liability, equity, and income account balances (this will be the default behavior). You can also reverse signs on income and expense accounts, or you can turn off sign reversal entirely if you want to see the values as they actually appear in the database.

Use the following PHP code to update your database and rebuild the menu to get access to the new settings page:

<?php

// Add new balance_num and balance_denom columns.
db_query('ALTER TABLE {ledger_account_type} ADD fundamental VARCHAR(10) DEFAULT 0 NOT NULL');

// Clear all the caches
cache_clear_all();

// Rebuild the menu.
menu_rebuild();

?>

I decided to split the import module out into it's own project: Ledger Import.

This shouldn't be a major change to those of you using Ledger, but I would recommend uninstalling your existing Ledger Import modules before updating Ledger to the latest dev, then download and install the new module. This will ensure you avoid any D7 code registry errors due to the module's directory change.

Small update: I added Views Bulk Operations fields to the Accounts and Transactions default Views, to make bulk deleting those entities easier via the UI. So make sure you have VBO installed before updating to the latest dev.

There was some back and forth in my mind about whether or not to add a dependency on VBO, but I think it will be worth it in the long run.

A few updates:

1. I moved all the UI modules into dedicated subdirectories. This means that some module locations have changed. After updating to the latest dev release, go to /admin/modules and click "Save Configuration" to ensure that the module location is updated.
2. I moved all balance-calculating and display logic into a new module called "ledger_period". After updating to the latest version, run the following PHP code via /devel/php:

<?php

// Move account entry balances from the {ledger_account_entry} table to the new {ledger_account_entry_balance} table.
$query = db_select('ledger_account_entry', 'e');
$query->fields('e', array('eid', 'balance_num', 'balance_denom'));
$result = $query->execute();
foreach ($result as $record) {
  drupal_write_record('ledger_account_entry_balance', $record);
}

// Remove fields from the {ledger_account_entry} table.
db_drop_field('ledger_account_entry', 'balance_num');
db_drop_field('ledger_account_entry', 'balance_denom');

// Move variables.
variable_set('ledger_period_sign_reverse', variable_get('ledger_account_balance_sign_reverse', 1));
variable_del('ledger_account_balance_sign_reverse');

// Clear cache.
cache_clear_all();

// Rebuild the menu.
menu_rebuild();

?>

3. The ledger_transactions default View has changed, so if you are overriding it you will need to either revert it or update it manually (remove the existing account entry balance field and add the new one in it's place).

Pretty big change: #2031573: Use Fraction module for price storage

To update: replace the Ledger and Ledger Input modules with the new versions. Then open up Devel's "Execute PHP" (/devel/php) and follow these instructions:

Backup your database before doing this!!!

Also: Run each of the following code blocks SEPARATELY. Don't try to run them all at once, because some of them are hefty and could cause a PHP timeout, which could lead to errors in the database.

1. Enable the Fraction module

module_enable(array('fraction'));

2. Configure new fields.

ledger_account_entry_configure_account_entry_types();
ledger_period_configure_account_types();
ledger_period_configure_account_entry_types();

3. Copy data from the old schema columns to the new value field.

variable_set('ledger_period_balance_calculate', 0);
$result = db_query('SELECT eid, value_num, value_denom FROM {ledger_account_entry}');
while ($row = $result->fetchAssoc()) {
  if (empty($row['eid'])) {
    continue;
  }
  $account_entry = ledger_account_entry_load($row['eid']);
  $account_entry->ledger_value[LANGUAGE_NONE][0]['numerator'] = $row['value_num'];
  $account_entry->ledger_value[LANGUAGE_NONE][0]['denominator'] = $row['value_denom'];
  ledger_account_entry_save($account_entry);
}
variable_set('ledger_period_balance_calculate', 1);

4. Recalculate the account and account entry balances.

ledger_period_calculate_balances();

5. Delete the old data

Warning: the next will DELETE your data The next step will remove the old data that was copied into the new field. If the data was successfully copied to the new fraction field, then this is fine. If the copy did NOT work, and you delete the old data, it's gone forever (you made a backup, right?).

db_drop_field('ledger_account_entry', 'value_num');
db_drop_field('ledger_account_entry', 'value_denom');
db_drop_table('ledger_account_entry_balance');

6. Reset Views

Reset the following Views to get the changes automatically: Ledger Accounts, Ledger Transactions, Ledger Budgets.

Or, if you have customizations made to the default Views that come with Ledger, you'll need to fix them manually. Basically this is just a matter of replacing Value and Balance fields with the new ones. You'll see a "Broken/missing handler" for each, so just delete those and replace them.

7. Clear the cache for good luck

cache_clear_all();

Budget module

If you're using the Budget module, you'll have to manually transfer your budget values. Or write a upgrade path. I didn't make one because I figured no one is using Budgets for anything yet anyway.

If you ARE, here are a few things you'll need to do:

1. Add the value field to Budget entities:

ledger_budget_configure_budget_types();

2. Copy old values to new field (this is where you come in).

3. Delete old schema.

db_drop_field('ledger_budget', 'value_num');
db_drop_field('ledger_budget', 'value_num');

That's it!

Let me know if I messed anything up.

Oh, and check out the new #2046251: [META] Ledger Roadmap!

Big update! I added a ledger_book module with encapsulates groups of accounts and gives them ownership. With this comes a HUGE UI overhaul and new access controls and permissions. For relevant issues, see:

#2046231: Ledger Book
#2186883: Should accounts depend on books?
#2186525: Reorganized menu paths
#2185707: Access control per entity and operation

There are a few manual changes required. Perform these via /devel/php or equivalent:

// Install ledger_book and ledger_book_ui.
module_enable(array('ledger_book', 'ledger_book_ui'));

// Add 'bid' column to {ledger_account}
$schema = drupal_get_schema('ledger_account', TRUE);
db_add_field('ledger_account', 'bid', $schema['fields']['bid']);
db_add_index('ledger_account', 'bid', array('bid'));

// Add 'bid' column to {ledger_transaction}
$schema = drupal_get_schema('ledger_transaction', TRUE);
db_add_field('ledger_transaction', 'bid', $schema['fields']['bid']);
db_add_index('ledger_transaction', 'bid', array('bid'));

// Add 'bid' column to {ledger_budget}
$schema = drupal_get_schema('ledger_budget', TRUE);
db_add_field('ledger_budget', 'book_id', $schema['fields']['book_id']);
db_add_index('ledger_budget', 'book_id', array('book_id'));

// Create a new book and assign all existing accounts to it.
global $user
$values = array(
  'bid' => NULL,
  'name' => 'My Book',
  'uid' => $user->uid,
);
$book = ledger_book_new($values);
ledger_book_save($book);
db_query("INSERT INTO {ledger_book_account} (SELECT :bid as bid, aid FROM {ledger_account})", array(':bid' => $book->bid));

Couple of small changes. Dylan moved the administrative entity lists back under the /admin namespace, which cleaned things up nicely.

A few database columns have been removed. Update your schema with the following:

db_drop_field('ledger_transaction', 'bid');
db_drop_field('ledger_budget', 'book_id');
db_drop_field('ledger_account_type', 'weight');
db_drop_field('ledger_account_type', 'data');

Yikes! I accidentally had db_drop_field('ledger_budget', 'bid'); in the last comment at first. I corrected it to be db_drop_field('ledger_budget', 'book_id');.

Hopefully no one ran that query, because it would have deleted the primary key column from your budget database. If you did, contact me and I'll try to help you get back on track. Or, if you don't mind losing your budgets, uninstall and reinstall the budget modules.

If you did not yet run the queries in comment #13 above, disregard this comment. Those queries are now correct.

@Yuri: Absolutely. I've already started an 8.x-1.x branch of Fraction, which is functional, and an important step in that direction.

An 8.x version of Ledger is definitely going to happen, but I do not plan on starting it until Drupal 8 is released and the APIs are stable. In the meantime I want to keep working on building the basic features that Ledger still lacks. See the roadmap for more specifics: #2046251: [META] Ledger Roadmap ... these are things that need to happen regardless of major version. It's true that many things will need to be changed for D8, and that will be a huge task in itself, but I don't want to stop working on the actual Ledger-specific features in the meantime.

I created a new task specifically to discuss the D8 transition: #2192217: [META] Drupal 8

If you are interested in helping with the discussion and development, please feel free to start some discussion in that issue.

Added "[META]" to issue title to make it more visible in the queue, and moved to the "Documentation" component.

The ledger_account_types module has been removed, and the account types it provides have been moved into ledger_account.

Run the following in /devel/php to update:

db_query("UPDATE {ledger_account_type} SET module=NULL WHERE module='ledger_account_types'");
cache_clear_all();

If you find that your account types all disappear for some reason (@Beakerboy experienced that here: #2375057: New version introduces several bugs). Then run the following code (which is now part of the ledger_account install hook):

<?php
// Create default account types.
  $items['assets'] = entity_import('ledger_account_type', '{ "fundamental" : "asset", "type" : "assets", "label" : "Assets" }');
  $items['equity'] = entity_import('ledger_account_type', '{ "fundamental" : "equity", "type" : "equity", "label" : "Equity" }');
  $items['expenses'] = entity_import('ledger_account_type', '{ "fundamental" : "expense", "type" : "expenses", "label" : "Expenses" }');
  $items['income'] = entity_import('ledger_account_type', '{ "fundamental" : "income", "type" : "income", "label" : "Income" }');
  $items['liabilities'] = entity_import('ledger_account_type', '{ "fundamental" : "liability", "type" : "liabilities", "label" : "Liabilities" }');
  foreach ($items as $item) {
    ledger_account_type_save($item);
  }
?>

Also, I'm looking for feedback on this: #2376593: Remove "Account Type" bundles?

I'm wondering if there is really a need for different Account Type bundles at all, or if they can be removed. We would keep the concept of "fundamental" account types (asset, income, expense, equity, and liability)... but remove the ability to have different bundles with different fields.