AccountStatusMigration.java

/*
    This file is part of Cyclos (www.cyclos.org).
    A project of the Social Trade Organisation (www.socialtrade.org).

    Cyclos is free software; you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
    the Free Software Foundation; either version 2 of the License, or
    (at your option) any later version.

    Cyclos is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
    GNU General Public License for more details.

    You should have received a copy of the GNU General Public License
    along with Cyclos; if not, write to the Free Software
    Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA

 */
package nl.strohalm.cyclos.setup.migrations.version3_5;

import static nl.strohalm.cyclos.utils.JDBCWrapper.closeQuietly;

import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;

import nl.strohalm.cyclos.entities.settings.LocalSettings;
import nl.strohalm.cyclos.entities.settings.LocalSettings.Precision;
import nl.strohalm.cyclos.setup.UntraceableMigration;
import nl.strohalm.cyclos.utils.JDBCWrapper;
import nl.strohalm.cyclos.utils.conversion.CoercionHelper;

/**
 * Populates the account_status and account_fee_charges tables
 * @author luis
 */
@SuppressWarnings("deprecation")
public class AccountStatusMigration implements UntraceableMigration {

    public void execute(final JDBCWrapper jdbc) throws SQLException {
        final String accountQuery = "select id, subclass, credit_limit, upper_credit_limit from accounts";
        final String transferQuery = "select id, date, amount, from_account_id, to_account_id, parent_id, account_fee_log_id from transfers where from_account_id = ? or to_account_id = ? order by date";
        final String initialStatusInsert = "insert into account_status (subclass, account_id, date, credit_limit, upper_credit_limit) select subclass, id, creation_date, credit_limit, upper_credit_limit from accounts";
        final String statusInsert = "insert into account_status (subclass, account_id, date, root_credits_count, root_credits_amount, root_debits_count, root_debits_amount, nested_credits_count, nested_credits_amount, nested_debits_count, nested_debits_amount, credit_limit, upper_credit_limit, transfer_id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        final ResultSet rsAccounts = jdbc.query(accountQuery);

        // Read the local settings
        final LocalSettings settings = new LocalSettings();
        final Precision precision = CoercionHelper.coerce(Precision.class, jdbc.readScalarAsString("select value from settings where name=?", "precision"));
        if (precision != null) {
            settings.setPrecision(precision);
        }

        // Insert an account status for every account creation
        jdbc.execute(initialStatusInsert);

        // Get each account
        while (rsAccounts.next()) {
            final long accountId = rsAccounts.getLong("id");
            final String subclass = rsAccounts.getString("subclass");
            final BigDecimal creditLimit = settings.round(rsAccounts.getBigDecimal("credit_limit"));
            final BigDecimal upperCreditLimit = settings.round(rsAccounts.getBigDecimal("upper_credit_limit"));
            BigDecimal rootCreditsAmount = BigDecimal.ZERO;
            int rootCreditsCount = 0;
            BigDecimal rootDebitsAmount = BigDecimal.ZERO;
            int rootDebitsCount = 0;
            BigDecimal nestedCreditsAmount = BigDecimal.ZERO;
            int nestedCreditsCount = 0;
            BigDecimal nestedDebitsAmount = BigDecimal.ZERO;
            int nestedDebitsCount = 0;

            // Insert a new status for every transfer from or to that account
            final ResultSet rsTransfers = jdbc.query(transferQuery, accountId, accountId);
            while (rsTransfers.next()) {
                final long transferId = rsTransfers.getLong("id");
                final Timestamp date = rsTransfers.getTimestamp("date");
                final BigDecimal amount = settings.round(rsTransfers.getBigDecimal("amount"));
                final long fromAccountId = rsTransfers.getLong("from_account_id");
                final boolean isRoot = rsTransfers.getString("parent_id") == null && rsTransfers.getString("account_fee_log_id") == null;

                // Check if is a credit or a debit
                final boolean isDebit = accountId == fromAccountId;
                if (isDebit) {
                    if (isRoot) {
                        rootDebitsAmount = rootDebitsAmount.add(amount);
                        rootDebitsCount++;
                    } else {
                        nestedDebitsAmount = nestedDebitsAmount.add(amount);
                        nestedDebitsCount++;
                    }
                } else {
                    if (isRoot) {
                        rootCreditsAmount = rootCreditsAmount.add(amount);
                        rootCreditsCount++;
                    } else {
                        nestedCreditsAmount = nestedCreditsAmount.add(amount);
                        nestedCreditsCount++;
                    }
                }

                // Insert the account status
                jdbc.execute(statusInsert, subclass, accountId, date, rootCreditsCount, rootCreditsAmount, rootDebitsCount, rootDebitsAmount, nestedCreditsCount, nestedCreditsAmount, nestedDebitsCount, nestedDebitsAmount, creditLimit, upperCreditLimit, transferId);
            }
            closeQuietly(rsTransfers);
        }
        closeQuietly(rsAccounts);
    }
}