/*
* The Kuali Financial System, a comprehensive financial management system for higher education.
*
* Copyright 2005-2014 The Kuali Foundation
*
* This program is free software: you can redistribute it and/or modify
* it under the terms of the GNU Affero General Public License as
* published by the Free Software Foundation, either version 3 of the
* License, or (at your option) any later version.
*
* This program 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 Affero General Public License for more details.
*
* You should have received a copy of the GNU Affero General Public License
* along with this program. If not, see <http://www.gnu.org/licenses/>.
*/
package org.kuali.kfs.gl.dataaccess.impl;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.lang.StringUtils;
import org.kuali.kfs.gl.businessobject.TrialBalanceReport;
import org.kuali.kfs.gl.dataaccess.TrialBalanceDao;
import org.kuali.kfs.sys.KFSConstants;
import org.kuali.rice.core.api.util.type.KualiDecimal;
import org.kuali.rice.core.framework.persistence.jdbc.dao.PlatformAwareDaoBaseJdbc;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ResultSetExtractor;
/**
* A class to do the database queries needed to calculate Balance By Consolidation Balance Inquiry Screen
*/
public class TrialBalanceDaoJdbc extends PlatformAwareDaoBaseJdbc implements TrialBalanceDao {
private static org.apache.log4j.Logger LOG = org.apache.log4j.Logger.getLogger(TrialBalanceDaoJdbc.class);
/*
* Helper method used to build the YTD sum depending on the selected fiscal period
* If the the period code specified is either empty or invalid, return the current balance amount + begining balance
* Actuals to be totaled by BB + period1 Total + period2 Total + etc...
*
* @param periodCode
* @return
*/
private static String buildYTDQueryString( String periodCode ){
if ( StringUtils.isBlank(periodCode)) {
return " SUM(A0.FIN_BEG_BAL_LN_AMT + A0.ACLN_ANNL_BAL_AMT)";
}
int number = 0;
try {
number = Integer.parseInt( periodCode );
} catch (NumberFormatException e){
//if periodCode is not a number, then consider it blank
return " SUM(A0.FIN_BEG_BAL_LN_AMT + A0.ACLN_ANNL_BAL_AMT)";
}
StringBuilder ytdQuery = new StringBuilder(" SUM(A0.FIN_BEG_BAL_LN_AMT + ");
for ( int i = 1; i<=number; i++){
ytdQuery.append("MO" + i);
ytdQuery.append( i<number?"_ACCT_LN_AMT + ":"_ACCT_LN_AMT ");
}
return ytdQuery.append( ")" ).toString();
}
@Override
public List<TrialBalanceReport> findBalanceByFields(String selectedFiscalYear, String chartCode, String periodCode) {
final List<TrialBalanceReport> report = new ArrayList<TrialBalanceReport>();
List<Object> queryArguments = new ArrayList<Object>(2);
String YTDQuery = buildYTDQueryString(periodCode);
StringBuilder queryBuilder = new StringBuilder();
queryBuilder.append("SELECT A0.FIN_OBJECT_CD, A0.FIN_COA_CD, A1.FIN_OBJ_CD_NM, A2.FIN_OBJTYP_DBCR_CD,");
queryBuilder.append(YTDQuery + " AS YTD ");
queryBuilder.append("FROM GL_BALANCE_T A0 JOIN CA_OBJECT_CODE_T A1 on A1.FIN_COA_CD = A0.FIN_COA_CD AND A1.UNIV_FISCAL_YR = A0.UNIV_FISCAL_YR and A1.FIN_OBJECT_CD = A0.FIN_OBJECT_CD ");
queryBuilder.append("JOIN CA_OBJ_TYPE_T A2 on A2.FIN_OBJ_TYP_CD = A1.FIN_OBJ_TYP_CD ");
queryBuilder.append("JOIN CA_ACCTG_CTGRY_T A3 on A3.ACCTG_CTGRY_CD = A2.ACCTG_CTGRY_CD ");
queryBuilder.append("WHERE A0.FIN_BALANCE_TYP_CD = 'AC' ");
queryBuilder.append("AND A0.UNIV_FISCAL_YR = ? ");
queryArguments.add(selectedFiscalYear);
if (StringUtils.isNotBlank(chartCode)) {
queryBuilder.append("AND A0.FIN_COA_CD=? ");
queryArguments.add(chartCode);
}
queryBuilder.append("GROUP BY A0.FIN_OBJECT_CD, A0.FIN_COA_CD, A1.FIN_OBJ_CD_NM, A2.FIN_OBJTYP_DBCR_CD, A3.FIN_REPORT_SORT_CD ");
queryBuilder.append("HAVING "+YTDQuery+" <> 0 ");
queryBuilder.append("ORDER BY A0.FIN_COA_CD, A3.FIN_REPORT_SORT_CD, A0.FIN_OBJECT_CD");
getJdbcTemplate().query(queryBuilder.toString(), queryArguments.toArray(), new ResultSetExtractor() {
@Override
public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
TrialBalanceReport reportLine = null;
KualiDecimal ytdAmount = null;
KualiDecimal totalDebit = KualiDecimal.ZERO;
KualiDecimal totalCredit = KualiDecimal.ZERO;
String objectTypeDebitCreditCd = null;
int index = 1;
// Iterator the search result and build the lookup object for trial balance report
while (rs != null && rs.next()) {
reportLine = new TrialBalanceReport();
reportLine.setIndex(index++);
reportLine.setChartOfAccountsCode(rs.getString("FIN_COA_CD"));
reportLine.setObjectCode(rs.getString("FIN_OBJECT_CD"));
reportLine.setFinancialObjectCodeName(rs.getString("FIN_OBJ_CD_NM"));
objectTypeDebitCreditCd = rs.getString("FIN_OBJTYP_DBCR_CD");
ytdAmount = new KualiDecimal(rs.getBigDecimal("YTD"));
if ((ytdAmount.isPositive() && KFSConstants.GL_CREDIT_CODE.equals(objectTypeDebitCreditCd)) || (ytdAmount.isNegative() && KFSConstants.GL_DEBIT_CODE.equals(objectTypeDebitCreditCd))) {
reportLine.setCreditAmount(ytdAmount.abs());
// sum the total credit
totalCredit = totalCredit.add(reportLine.getCreditAmount());
}
else if ((ytdAmount.isPositive() && KFSConstants.GL_DEBIT_CODE.equals(objectTypeDebitCreditCd)) || (ytdAmount.isNegative() && KFSConstants.GL_CREDIT_CODE.equals(objectTypeDebitCreditCd))) {
reportLine.setDebitAmount(ytdAmount.abs());
// sum the total debit
totalDebit = totalDebit.add(reportLine.getDebitAmount());
}
report.add(reportLine);
}
// add a final line for total credit and debit
if (!report.isEmpty()) {
reportLine = new TrialBalanceReport();
reportLine.setIndex(index++);
reportLine.setChartOfAccountsCode("Total");
reportLine.setDebitAmount(totalDebit);
reportLine.setCreditAmount(totalCredit);
report.add(reportLine);
}
return null;
}
});
return report;
}
}