001 /*
002 * Copyright 2011 The Kuali Foundation.
003 *
004 * Licensed under the Educational Community License, Version 2.0 (the "License");
005 * you may not use this file except in compliance with the License.
006 * You may obtain a copy of the License at
007 *
008 * http://www.opensource.org/licenses/ecl2.php
009 *
010 * Unless required by applicable law or agreed to in writing, software
011 * distributed under the License is distributed on an "AS IS" BASIS,
012 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
013 * See the License for the specific language governing permissions and
014 * limitations under the License.
015 */
016 package org.kuali.kfs.module.bc.document.dataaccess.impl;
017
018 import java.sql.Date;
019 import java.util.List;
020
021 import org.apache.log4j.Logger;
022 import org.kuali.kfs.module.bc.BCParameterKeyConstants;
023 import org.kuali.kfs.module.bc.document.BudgetConstructionDocument;
024 import org.kuali.kfs.module.bc.util.BudgetParameterFinder;
025 import org.kuali.rice.kns.dao.jdbc.PlatformAwareDaoBaseJdbc;
026 import org.kuali.rice.kns.service.ParameterService;
027
028
029 /**
030 * create methods for building SQL useful to all extenders
031 */
032 public class BudgetConstructionDaoJdbcBase extends PlatformAwareDaoBaseJdbc {
033
034 ParameterService parameterService;
035
036 private static Logger LOG = org.apache.log4j.Logger.getLogger(BudgetConstructionDaoJdbcBase.class);
037
038 private String ojbPlatform;
039 private String ojbOraclePlatform;
040
041 private StringBuilder[] oracleSubString = {new StringBuilder("SUBSTR("),new StringBuilder(","),new StringBuilder(","), new StringBuilder(")")};
042 private StringBuilder[] ansi92SubString = {new StringBuilder("SUBSTRING("),new StringBuilder(" FROM "), new StringBuilder(" FOR "), new StringBuilder(")")};
043 private String dateFetcher = new String("SELECT MIN(UNIV_DT) FROM SH_UNIV_DATE_T WHERE (UNIV_FISCAL_YR = ?)");
044
045 protected void clearTempTableByUnvlId(String tableName, String personUnvlIdColumn, String principalName) {
046 getSimpleJdbcTemplate().update("DELETE from " + tableName + " WHERE " + personUnvlIdColumn + " = ?", principalName);
047 }
048
049 protected void clearTempTableBySesId(String tableName, String SesIdColumn, String sessionId) {
050 getSimpleJdbcTemplate().update("DELETE from " + tableName + " WHERE " + SesIdColumn + " = ?", sessionId);
051 }
052
053 /**
054 *
055 * build a string of placeholders for a parameterized java.sql IN clause
056 * @param parameterCount the number of parameters in the IN clause
057 * @return the String (?,?,?) with the correct nubmer of parameters
058 */
059 protected String inString(Integer parameterCount)
060 {
061 // there should be at least one parameter in the IN string
062 // but allow people to screw up and have an IN condition which is never satisfied
063 if (parameterCount == 0)
064 {
065 return new String("('')");
066 }
067 StringBuffer sb = new StringBuffer(20);
068 sb = sb.append("(?");
069 for (int i = 1; i < parameterCount; i++)
070 {
071 sb.append(",?");
072 }
073 sb.append(")");
074 return sb.toString();
075 }
076
077 /**
078 *
079 * build a SQL IN clause from the array of parameters passed in
080 * @param inListValues: components of the IN list
081 * @return an empty string if the IN list will be empty
082 */
083 protected String inString (List<String> inListValues)
084 {
085 // the delimiter for strings in the DB is assumed to be a single quote.
086 // this is the ANSI-92 standard.
087 // if the ArrayList input is empty, IN ('') is returned.
088 StringBuffer inBuilder = new StringBuffer(150);
089
090 inBuilder.append("('");
091 if (! inListValues.isEmpty())
092 {
093 inBuilder.append(inListValues.get(0));
094 }
095 else
096 {
097 // for an empty list, return an empty string
098 return new String("");
099 }
100 for (int idx = 1; idx < inListValues.size(); idx++)
101 {
102 inBuilder.append("','");
103 inBuilder.append(inListValues.get(idx));
104 }
105 inBuilder.append("')");
106
107 return inBuilder.toString();
108 }
109
110 /**
111 *
112 * given a fiscal year, get the first day of that fiscal year
113 * @param universityFiscalYear = fiscal year (must be in the table)
114 * @return the date on which the fiscal year passed as a parameter starts
115 */
116 protected Date getFiscalYearStartDate(Integer universityFiscalYear)
117 {
118 return getSimpleJdbcTemplate().queryForObject(dateFetcher, Date.class, universityFiscalYear);
119 }
120
121 /**
122 *
123 * return a SQL IN list containing the budget construction expenditure object types
124 * @return a null string if the system parameter does not exist or is empty
125 */
126 protected String getExpenditureINList()
127 {
128 if (! parameterService.parameterExists(BudgetConstructionDocument.class,BCParameterKeyConstants.EXPENDITURE_OBJECT_TYPES))
129 {
130 LOG.warn(String.format("\n***Budget Construction Application Error***\nSQL will not be valid\nparameter %s does not exist\n",BCParameterKeyConstants.EXPENDITURE_OBJECT_TYPES));
131 IllegalArgumentException ioex = new IllegalArgumentException("parameter "+BCParameterKeyConstants.EXPENDITURE_OBJECT_TYPES+" does not exist");
132 throw (ioex);
133 }
134
135 List<String> expenditureObjectTypes = BudgetParameterFinder.getExpenditureObjectTypes();
136 if (expenditureObjectTypes.isEmpty())
137 {
138 LOG.warn(String.format("\n***Budget Construction Application Error***\nSQL will not be valid\nparameter %s is empty\n",BCParameterKeyConstants.EXPENDITURE_OBJECT_TYPES));
139 IllegalArgumentException bfex = new IllegalArgumentException("parameter "+BCParameterKeyConstants.EXPENDITURE_OBJECT_TYPES+" is empty");
140 throw (bfex);
141 }
142 return inString(expenditureObjectTypes);
143 }
144
145 /**
146 *
147 * return a SQL IN list containing the budget construction revenue object types
148 * @return a null string if the system parameter does not exist or is empty
149 */
150 protected String getRevenueINList()
151 {
152 if (! parameterService.parameterExists(BudgetConstructionDocument.class,BCParameterKeyConstants.REVENUE_OBJECT_TYPES))
153 {
154 LOG.warn(String.format("\n***Budget Construction Application Error***\nSQL will not be valid\nparameter %s does not exist\n",BCParameterKeyConstants.REVENUE_OBJECT_TYPES));
155 IllegalArgumentException ioex = new IllegalArgumentException("parameter "+BCParameterKeyConstants.REVENUE_OBJECT_TYPES+" does not exist");
156 throw (ioex);
157 }
158 List<String> revenueObjectTypes = BudgetParameterFinder.getRevenueObjectTypes();
159 if (revenueObjectTypes.isEmpty())
160 {
161 LOG.warn(String.format("\n***Budget Construction Application Error***\nSQL will not be valid\nparameter %s is empty\n",BCParameterKeyConstants.REVENUE_OBJECT_TYPES));
162 IllegalArgumentException bfex = new IllegalArgumentException("parameter "+BCParameterKeyConstants.REVENUE_OBJECT_TYPES+" is empty");
163 throw (bfex);
164 }
165 return inString(revenueObjectTypes);
166 }
167
168 /**
169 *
170 * return a substring function that is Oracle-specific if the DB Platform is Oracle, and an ANSI-92 compliant function otherwise
171 * Oracle's syntax is not ANSI-92 compliant
172 * @param fieldName = string representing the name of the DB field (possibly qualified)
173 * @param startLocation = starting location of the substring
174 * @param substringLength = length of the substring
175 * @return the substring function
176 */
177 protected StringBuilder getSqlSubStringFunction(String fieldName, Integer startLocation, Integer substringLength){
178 boolean oracleDB = ojbPlatform.equals(ojbOraclePlatform);
179 StringBuilder subStringer = new StringBuilder(40);
180 String start = startLocation.toString();
181 String span = substringLength.toString();
182 if (oracleDB)
183 {
184 subStringer.append(oracleSubString[0]);
185 subStringer.append(fieldName);
186 subStringer.append(oracleSubString[1]);
187 subStringer.append(start);
188 subStringer.append(oracleSubString[2]);
189 subStringer.append(span);
190 subStringer.append(oracleSubString[3]);
191 }
192 else
193 {
194 subStringer.append(ansi92SubString[0]);
195 subStringer.append(fieldName);
196 subStringer.append(ansi92SubString[1]);
197 subStringer.append(start);
198 subStringer.append(ansi92SubString[2]);
199 subStringer.append(span);
200 subStringer.append(ansi92SubString[3]);
201 }
202 return subStringer;
203 }
204
205
206 public void setOjbPlatform(String ojbPlatform)
207 {
208 this.ojbPlatform = ojbPlatform;
209 }
210
211 public void setOjbOraclePlatform(String ojbOraclePlatform)
212 {
213 this.ojbOraclePlatform = ojbOraclePlatform;
214 }
215
216 public void setParameterService(ParameterService parameterService) {
217 this.parameterService = parameterService;
218 }
219
220
221
222 }
223