1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19 package org.kuali.kfs.gl.dataaccess.impl;
20
21 import java.sql.ResultSet;
22 import java.sql.SQLException;
23 import java.util.ArrayList;
24 import java.util.List;
25
26 import org.apache.commons.lang.StringUtils;
27 import org.kuali.kfs.gl.businessobject.TrialBalanceReport;
28 import org.kuali.kfs.gl.dataaccess.TrialBalanceDao;
29 import org.kuali.kfs.sys.KFSConstants;
30 import org.kuali.rice.core.api.util.type.KualiDecimal;
31 import org.kuali.rice.core.framework.persistence.jdbc.dao.PlatformAwareDaoBaseJdbc;
32 import org.springframework.dao.DataAccessException;
33 import org.springframework.jdbc.core.ResultSetExtractor;
34
35
36
37
38 public class TrialBalanceDaoJdbc extends PlatformAwareDaoBaseJdbc implements TrialBalanceDao {
39 private static org.apache.log4j.Logger LOG = org.apache.log4j.Logger.getLogger(TrialBalanceDaoJdbc.class);
40
41
42
43
44
45
46
47
48
49
50
51 private static String buildYTDQueryString( String periodCode ){
52
53 if ( StringUtils.isBlank(periodCode)) {
54 return " SUM(A0.FIN_BEG_BAL_LN_AMT + A0.ACLN_ANNL_BAL_AMT)";
55 }
56
57 int number = 0;
58 try {
59 number = Integer.parseInt( periodCode );
60 } catch (NumberFormatException e){
61
62 return " SUM(A0.FIN_BEG_BAL_LN_AMT + A0.ACLN_ANNL_BAL_AMT)";
63 }
64
65 StringBuilder ytdQuery = new StringBuilder(" SUM(A0.FIN_BEG_BAL_LN_AMT + ");
66 for ( int i = 1; i<=number; i++){
67 ytdQuery.append("MO" + i);
68 ytdQuery.append( i<number?"_ACCT_LN_AMT + ":"_ACCT_LN_AMT ");
69 }
70
71 return ytdQuery.append( ")" ).toString();
72 }
73
74
75 @Override
76 public List<TrialBalanceReport> findBalanceByFields(String selectedFiscalYear, String chartCode, String periodCode) {
77 final List<TrialBalanceReport> report = new ArrayList<TrialBalanceReport>();
78 List<Object> queryArguments = new ArrayList<Object>(2);
79
80 String YTDQuery = buildYTDQueryString(periodCode);
81 StringBuilder queryBuilder = new StringBuilder();
82 queryBuilder.append("SELECT A0.FIN_OBJECT_CD, A0.FIN_COA_CD, A1.FIN_OBJ_CD_NM, A2.FIN_OBJTYP_DBCR_CD,");
83 queryBuilder.append(YTDQuery + " AS YTD ");
84 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 ");
85 queryBuilder.append("JOIN CA_OBJ_TYPE_T A2 on A2.FIN_OBJ_TYP_CD = A1.FIN_OBJ_TYP_CD ");
86 queryBuilder.append("JOIN CA_ACCTG_CTGRY_T A3 on A3.ACCTG_CTGRY_CD = A2.ACCTG_CTGRY_CD ");
87 queryBuilder.append("WHERE A0.FIN_BALANCE_TYP_CD = 'AC' ");
88 queryBuilder.append("AND A0.UNIV_FISCAL_YR = ? ");
89 queryArguments.add(selectedFiscalYear);
90
91 if (StringUtils.isNotBlank(chartCode)) {
92 queryBuilder.append("AND A0.FIN_COA_CD=? ");
93 queryArguments.add(chartCode);
94 }
95 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 ");
96 queryBuilder.append("HAVING "+YTDQuery+" <> 0 ");
97 queryBuilder.append("ORDER BY A0.FIN_COA_CD, A3.FIN_REPORT_SORT_CD, A0.FIN_OBJECT_CD");
98
99 getJdbcTemplate().query(queryBuilder.toString(), queryArguments.toArray(), new ResultSetExtractor() {
100 @Override
101 public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
102
103 TrialBalanceReport reportLine = null;
104 KualiDecimal ytdAmount = null;
105 KualiDecimal totalDebit = KualiDecimal.ZERO;
106 KualiDecimal totalCredit = KualiDecimal.ZERO;
107 String objectTypeDebitCreditCd = null;
108 int index = 1;
109
110
111 while (rs != null && rs.next()) {
112 reportLine = new TrialBalanceReport();
113 reportLine.setIndex(index++);
114 reportLine.setChartOfAccountsCode(rs.getString("FIN_COA_CD"));
115 reportLine.setObjectCode(rs.getString("FIN_OBJECT_CD"));
116 reportLine.setFinancialObjectCodeName(rs.getString("FIN_OBJ_CD_NM"));
117 objectTypeDebitCreditCd = rs.getString("FIN_OBJTYP_DBCR_CD");
118 ytdAmount = new KualiDecimal(rs.getBigDecimal("YTD"));
119
120 if ((ytdAmount.isPositive() && KFSConstants.GL_CREDIT_CODE.equals(objectTypeDebitCreditCd)) || (ytdAmount.isNegative() && KFSConstants.GL_DEBIT_CODE.equals(objectTypeDebitCreditCd))) {
121 reportLine.setCreditAmount(ytdAmount.abs());
122
123 totalCredit = totalCredit.add(reportLine.getCreditAmount());
124 }
125 else if ((ytdAmount.isPositive() && KFSConstants.GL_DEBIT_CODE.equals(objectTypeDebitCreditCd)) || (ytdAmount.isNegative() && KFSConstants.GL_CREDIT_CODE.equals(objectTypeDebitCreditCd))) {
126 reportLine.setDebitAmount(ytdAmount.abs());
127
128 totalDebit = totalDebit.add(reportLine.getDebitAmount());
129 }
130 report.add(reportLine);
131 }
132
133
134 if (!report.isEmpty()) {
135 reportLine = new TrialBalanceReport();
136 reportLine.setIndex(index++);
137 reportLine.setChartOfAccountsCode("Total");
138 reportLine.setDebitAmount(totalDebit);
139 reportLine.setCreditAmount(totalCredit);
140 report.add(reportLine);
141 }
142 return null;
143 }
144 });
145 return report;
146 }
147
148 }