BaseDAO.java

package org.mifosx.admin.domain;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

@Repository
public class BaseDAO implements IBaseDAO {

	@Autowired
	JdbcTemplate jdbcTemplate;

	private static final class TenantMapper implements RowMapper<Tenant> {

		@Override
		public Tenant mapRow(ResultSet rs, @SuppressWarnings("unused") int rowNum) throws SQLException {
			Integer id = rs.getInt("id");
			String name = rs.getString("name");
			String timeZone = rs.getString("timezone_id");
			String identifier = rs.getString("identifier");
			String schemaName = rs.getString("schema_name");
			return new Tenant(id, identifier, name, timeZone, schemaName);
		}
	}

	@Override
	public List<Tenant> retrieveTenants() {
		String fetchTenantsSQL = "select t.id, t.name, t.identifier, t.timezone_id, tsc.schema_name from tenants t,tenant_server_connections tsc where t.oltp_id=tsc.id";
		List<Tenant> tenants = jdbcTemplate.query(fetchTenantsSQL, new TenantMapper());
		return tenants;
	}

	@Override
	public Tenant retrieveTenant(Integer id) {
		String fetchTenantsSQL = "select t.id, t.name, t.identifier, t.timezone_id, tsc.schema_name from tenants t,tenant_server_connections tsc where t.oltp_id=tsc.id"
				+ " and t.id = ?";
		Tenant tenant = jdbcTemplate.queryForObject(fetchTenantsSQL, new Object[] { id }, new TenantMapper());
		return tenant;
	}

	@Override
	public void appendTenantStatistics(List<Tenant> tenants) {

		StringBuilder lastLoginDateQuery = new StringBuilder();

		StringBuilder activeCentersQuery = new StringBuilder();

		StringBuilder activeGroupsQuery = new StringBuilder();

		StringBuilder activeClientsQuery = new StringBuilder();

		StringBuilder activeLoanAccountsQuery = new StringBuilder();

		StringBuilder activeSavingsAccountsQuery = new StringBuilder();

		// generate queries for statistics across tenants
		for (Tenant tenant : tenants) {
			lastLoginDateQuery.append(" union all select max(made_on_date) from `" + tenant.getSchemaName()
					+ "`.m_portfolio_command_source");

			activeCentersQuery.append(" union all select count(*) from `" + tenant.getSchemaName()
					+ "`.m_group where status_enum >=300 and status_enum < 600 and level_id=1 ");

			activeGroupsQuery.append(" union all select  count(*) from `" + tenant.getSchemaName()
					+ "`.m_group where status_enum >=300 and status_enum < 600 and level_id=2 ");

			activeClientsQuery.append(" union all select count(*) from `" + tenant.getSchemaName()
					+ "`.m_client where status_enum >=300 and status_enum < 600 ");

			activeLoanAccountsQuery.append(" union all select count(*) from `" + tenant.getSchemaName()
					+ "`.m_loan  where (loan_status_id > 200 and loan_status_id < 400) or loan_status_id = 700 ");

			activeSavingsAccountsQuery.append(" union all select count(*) from `" + tenant.getSchemaName()
					+ "`.m_savings_account  where (status_enum > 200 and status_enum < 400) or status_enum = 800 ");

		}

		// Run SQL queries for generating statistics
		String lastLoginDateQueryAsString = convertToValidSQLQuery(lastLoginDateQuery);
		String activeCentersQueryAsString = convertToValidSQLQuery(activeCentersQuery);
		String activeGroupsQueryAsString = convertToValidSQLQuery(activeGroupsQuery);
		String activeClientsQueryAsString = convertToValidSQLQuery(activeClientsQuery);
		String activeLoanAccountsQueryAsString = convertToValidSQLQuery(activeLoanAccountsQuery);
		String activeSavingsAccountsQueryAsString = convertToValidSQLQuery(activeSavingsAccountsQuery);

		List<Date> lastLoginDates = jdbcTemplate.queryForList(lastLoginDateQueryAsString, Date.class);
		List<Integer> activeCenters = jdbcTemplate.queryForList(activeCentersQueryAsString, Integer.class);
		List<Integer> activeGroups = jdbcTemplate.queryForList(activeGroupsQueryAsString, Integer.class);
		List<Integer> activeClients = jdbcTemplate.queryForList(activeClientsQueryAsString, Integer.class);
		List<Integer> activeLoanAccounts = jdbcTemplate.queryForList(activeLoanAccountsQueryAsString, Integer.class);
		List<Integer> activeSavingsAccounts = jdbcTemplate.queryForList(activeSavingsAccountsQueryAsString,
				Integer.class);

		// update statistics back in Tenants
		for (int i = 0; i < tenants.size(); i++) {
			tenants.get(i).setStatistics(
					new TenantStatistics(lastLoginDates.get(i), activeCenters.get(i), activeGroups.get(i),
							activeClients.get(i), activeLoanAccounts.get(i), activeSavingsAccounts.get(i)));
		}
	}

	/**
	 * @param lastLoginDateQuery
	 * @return
	 */
	private String convertToValidSQLQuery(StringBuilder lastLoginDateQuery) {
		return lastLoginDateQuery.toString().replaceFirst("union all", "");
	}

	@Override
	public void deleteTenant(Integer id) {
		this.jdbcTemplate.update("delete from tenants,tenant_server_connections t where t.id=tenants.id and id = ?",
				id);
	}

	@Override
	public Tenant createTenant(Tenant tenant) {
		// create an entry in tenants Table
		this.jdbcTemplate.update(
				"INSERT INTO `tenants` (`identifier`, `name`, `schema_name`, `timezone_id`) VALUES (?, ?, ?, ?)",
				tenant.getIdentifier(), tenant.getName(), tenant.getSchemaName());
		// create a database
		this.jdbcTemplate.execute("create database " + tenant.getSchemaName());

		return tenant;
	}
}