/*
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_6;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Calendar;
import nl.strohalm.cyclos.entities.accounts.transactions.Payment;
import nl.strohalm.cyclos.setup.TraceableMigration;
import nl.strohalm.cyclos.utils.JDBCWrapper;
import org.apache.commons.lang.time.DateUtils;
/**
* A migration class which replaces the old AccountStatus by the new ClosedAccountBalance approach
*
* @author luis
*/
public class ClosedAccountBalancesMigration implements TraceableMigration {
@Override
public int execute(final JDBCWrapper jdbc) throws SQLException {
// First, ensure the account status still exists, ie, not already migrated
ResultSet accountStatusExists = null;
try {
accountStatusExists = jdbc.query("select 1 from account_status limit 1");
} catch (final SQLException e) {
// The already does not exists. Exit.
return 0;
} finally {
JDBCWrapper.closeQuietly(accountStatusExists);
}
// Populate the account limit logs from both the account status and the pending account status tables
jdbc.execute("insert into account_limit_logs " +
" (account_id, date, by_id, credit_limit, upper_credit_limit) " +
" select account_id, date, credit_limit_by_id, credit_limit, upper_credit_limit" +
" from account_status" +
" where credit_limit_by_id is not null");
jdbc.execute("insert into account_limit_logs " +
" (account_id, date, by_id, credit_limit, upper_credit_limit) " +
" select account_id, date, by_id, lower_limit, upper_limit" +
" from pending_account_status" +
" where type = 'lim'");
// Populate the amount_reservations table from pending transfers, scheduled payments which reserves the total amount and their installments
jdbc.execute("insert into amount_reservations" +
" (subclass, account_id, date, amount, transfer_id)" +
" select 'P', from_account_id, date, amount, id " +
" from transfers t " +
" where t.status = ? ", Payment.Status.PENDING.getValue());
jdbc.execute("insert into amount_reservations" +
" (subclass, account_id, date, amount, scheduled_payment_id)" +
" select 'S', from_account_id, date, amount, id " +
" from scheduled_payments " +
" where reserve_amount = true ");
jdbc.execute("insert into amount_reservations" +
" (subclass, account_id, date, amount, transfer_id)" +
" select 'I', t.from_account_id, ifnull(t.process_date, t.date), -t.amount, t.id " +
" from transfers t inner join scheduled_payments sp on t.scheduled_payment_id = sp.id" +
" where sp.reserve_amount = true and t.status <> ? ", Payment.Status.SCHEDULED.getValue());
// Iterate each account
int results = 0;
final ResultSet accounts = jdbc.query("select id, creation_date from accounts");
try {
while (accounts.next()) {
final long accountId = accounts.getLong("id");
final Date creationDate = new Date(DateUtils.truncate(accounts.getTimestamp("creation_date"), Calendar.DAY_OF_MONTH).getTime());
// Get, by day, each diff, either for balance or reserved amount
ResultSet diffs = jdbc.query(" select * from ( " +
" select 'B' as type, b.date, b.balance as diff" +
" from ( " +
" select date(date) as date, sum(amount) as balance " +
" from ( " +
" select t.process_date as date, " +
" case when t.chargeback_of_id is null then " +
" case when t.from_account_id = ? then -t.amount else t.amount end " +
" else " +
" case when t.to_account_id = ? then t.amount else -t.amount end " +
" end as amount " +
" from transfers t " +
" where (t.from_account_id = ? or t.to_account_id = ?) " +
" and t.process_date is not null " +
" ) t " +
" group by date(date) " +
" ) b " +
" union " +
" select 'R', date(r.date), sum(r.amount) " +
" from amount_reservations r " +
" where r.account_id = ? " +
" group by date(r.date) " +
" ) t " +
" where date < current_date() " +
" order by date", accountId, accountId, accountId, accountId, accountId);
Date lastDate = creationDate;
double balance = 0;
double reserved = 0;
try {
boolean hasData = false;
while (diffs.next()) {
hasData = true;
boolean isBalance = "B".equals(diffs.getString("type"));
Date date = diffs.getDate("date");
double diff = diffs.getDouble("diff");
if (!lastDate.equals(date)) {
// Insert a closed balance when the date changes
results += jdbc.execute("insert into closed_account_balances (date, account_id, balance, reserved) values (?, ?, ?, ?)", nextDay(lastDate), accountId, balance, reserved);
}
if (isBalance) {
balance += diff;
} else {
reserved += diff;
}
lastDate = date;
}
if (hasData) {
// There is a last closed balance to insert
results += jdbc.execute("insert into closed_account_balances (date, account_id, balance, reserved) values (?, ?, ?, ?)", nextDay(lastDate), accountId, balance, reserved);
}
} finally {
JDBCWrapper.closeQuietly(diffs);
}
// Set the last closing date
jdbc.execute("update accounts set last_closing_date = ? where id = ?", lastDate, accountId);
}
} finally {
JDBCWrapper.closeQuietly(accounts);
}
// Now it is safe to drop the account_status table
jdbc.execute("drop table account_status");
jdbc.execute("drop table pending_account_status");
return results;
}
private Date nextDay(final Date date) {
final Calendar calendar = Calendar.getInstance();
calendar.setTime(date);
calendar.add(Calendar.DAY_OF_MONTH, 1);
return new Date(calendar.getTimeInMillis());
}
}