/**
* Find all the year records in a Calendar, it need not be a standard period (used in MRP)
*
* @param C_Calendar_ID calendar
* @param ctx context
* @param trx trx
* @return MYear[]
*/
public static MYear[] getAllYearsInCalendar(int C_Calendar_ID, Ctx ctx, Trx trx) {
List<MYear> years = new ArrayList<MYear>();
String sql = "SELECT * FROM C_Year WHERE " + "IsActive='Y' AND C_Calendar_ID = ? ";
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt = DB.prepareStatement(sql, trx);
pstmt.setInt(1, C_Calendar_ID);
rs = pstmt.executeQuery();
while (rs.next()) years.add(new MYear(ctx, rs, trx));
} catch (Exception e) {
s_log.log(Level.SEVERE, sql, e);
} finally {
DB.closeResultSet(rs);
DB.closeStatement(pstmt);
}
MYear[] retValue = new MYear[years.size()];
years.toArray(retValue);
return retValue;
}
/**
* Find the periods in a calendar year it need not be a standard period (used in MRP)
*
* @param C_Year_ID Year
* @param periodType Period Type
* @param ctx context
* @param trx trx
* @return MPeriod[]
*/
public static MPeriod[] getAllPeriodsInYear(int C_Year_ID, String periodType, Ctx ctx, Trx trx) {
List<MPeriod> periods = new ArrayList<MPeriod>();
String sql = "SELECT * FROM C_Period WHERE IsActive='Y'";
sql = sql + " AND C_Year_ID = ?";
if (periodType != null) sql = sql + " AND PeriodType = ? ";
sql = sql + " order by StartDate ";
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt = DB.prepareStatement(sql, trx);
pstmt.setInt(1, C_Year_ID);
if (periodType != null) pstmt.setString(2, periodType);
rs = pstmt.executeQuery();
while (rs.next()) periods.add(new MPeriod(ctx, rs, trx));
} catch (Exception e) {
s_log.log(Level.SEVERE, sql, e);
} finally {
DB.closeResultSet(rs);
DB.closeStatement(pstmt);
}
MPeriod[] retValue = new MPeriod[periods.size()];
periods.toArray(retValue);
return retValue;
}
public MetadataBlob[] select(String selectString) {
PreparedStatement stmt = null;
try {
stmt =
conn.prepareStatement(
"select writingKey, mapkey, blobdata from metadatablobs " + selectString + ";");
ResultSet rs = stmt.executeQuery();
List<MetadataBlob> list = new ArrayList<MetadataBlob>();
while (rs.next()) {
MetadataBlob f =
new MetadataBlob(
rs.getString("writingkey"), rs.getString("mapkey"), rs.getString("blobdata"));
list.add(f);
}
return list.toArray(new MetadataBlob[0]);
} catch (SQLException sqe) {
System.err.println("Error selecting: " + selectString);
sqe.printStackTrace();
return null;
} finally {
if (stmt != null)
try {
stmt.close();
} catch (SQLException sqe2) {
sqe2.printStackTrace();
}
}
}
public Collection<User> findUsers(Set<String> fields, String query)
throws UnsupportedOperationException {
if (fields.isEmpty()) {
return Collections.emptyList();
}
if (!getSearchFields().containsAll(fields)) {
throw new IllegalArgumentException("Search fields " + fields + " are not valid.");
}
if (query == null || "".equals(query)) {
return Collections.emptyList();
}
// SQL LIKE queries don't map directly into a keyword/wildcard search like we want.
// Therefore, we do a best approximiation by replacing '*' with '%' and then
// surrounding the whole query with two '%'. This will return more data than desired,
// but is better than returning less data than desired.
query = "%" + query.replace('*', '%') + "%";
if (query.endsWith("%%")) {
query = query.substring(0, query.length() - 1);
}
List<String> usernames = new ArrayList<String>(50);
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
try {
con = DbConnectionManager.getConnection();
stmt = con.createStatement();
StringBuilder sql = new StringBuilder();
sql.append("SELECT username FROM jiveUser WHERE");
boolean first = true;
if (fields.contains("Username")) {
sql.append(" username LIKE '").append(StringUtils.escapeForSQL(query)).append("'");
first = false;
}
if (fields.contains("Name")) {
if (!first) {
sql.append(" AND");
}
sql.append(" name LIKE '").append(StringUtils.escapeForSQL(query)).append("'");
first = false;
}
if (fields.contains("Email")) {
if (!first) {
sql.append(" AND");
}
sql.append(" email LIKE '").append(StringUtils.escapeForSQL(query)).append("'");
}
rs = stmt.executeQuery(sql.toString());
while (rs.next()) {
usernames.add(rs.getString(1));
}
} catch (SQLException e) {
Log.error(e);
} finally {
DbConnectionManager.closeConnection(rs, stmt, con);
}
return new UserCollection(usernames.toArray(new String[usernames.size()]));
}
@Override
public int update(String sql, List<Object> values) throws Exception {
consoleSql(sql, values);
if (values != null && values.size() > 0) {
return getWirteJdbc().update(sql, values.toArray());
} else {
return getWirteJdbc().update(sql);
}
}
@Override
public <T> List<T> searchForList(String sql, Class<T> clazz, List<Object> params)
throws Exception {
consoleSql(sql, params);
return getReadJdbc()
.query(
sql,
params.toArray(),
new ModelPropertyRowMapper<T>(clazz, ClassUtils.propToColumnMap.get(clazz.getName())));
}
@Override
public long getLong(String sql, List<Object> values) {
consoleSql(sql, values);
if (StringUtils.isEmpty(sql)) {
return 0;
}
if (values == null) {
values = new ArrayList<Object>();
}
return this.getReadJdbc().queryForLong(sql, values.toArray());
}
public static String[] getSchemas(Connection connection) throws Exception {
if (connection != null) {
List<String> schemaList = new ArrayList<String>();
DatabaseMetaData mObject = connection.getMetaData();
ResultSet schemas = mObject.getSchemas();
while (schemas.next()) {
schemaList.add(schemas.getString(DBConstants.DataServiceGenerator.TABLE_SCHEM));
}
String str[] = schemaList.toArray(new String[schemaList.size()]);
return str;
} else {
return null;
}
}
protected String[] listFileResources(URL dirURL, String schemaPath, FilenameFilter filter)
throws URISyntaxException, IOException {
String[] files;
String[] tempFiles;
List<String> filesList = new ArrayList<>();
File dirFile = new File(dirURL.toURI());
tempFiles = dirFile.list(filter);
files = new String[tempFiles.length];
for (String fileName : tempFiles) {
filesList.add(schemaPath + "/" + fileName);
}
files = filesList.toArray(files);
return files;
}
public static String[] getAllCourses(String company_id) {
List<String> lsAllCourse = new ArrayList<String>();
for (Item topItem : Item.getTopItems()) {
java.util.List<Item> lsItem = topItem.getChildren();
for (Item item : lsItem) {
if (JUtil.convertNull(item.getCompany_id()).length() > 0
&& !item.getCompany_id().equals(company_id)) continue;
lsAllCourse.add(item.getItem_name());
}
}
String[] arrAllCourse = new String[lsAllCourse.size()];
lsAllCourse.toArray(arrAllCourse);
return arrAllCourse;
}
public static String[] getTableList(Connection connection, String dbName, String[] schemas)
throws SQLException {
if (connection != null) {
List<String> tableList = new ArrayList<String>();
// String dbName = connection.getCatalog();
DatabaseMetaData mObject = connection.getMetaData();
if (schemas.length != 0) {
tableList = getTableNamesList(mObject, dbName, schemas[0]);
if (tableList.size()
== 0) { // for some drivers catalog is not same as the dbname (eg: DB2). In that case
// search tables only using schema.
tableList = getTableNamesList(mObject, null, schemas[0]);
}
} else {
tableList = getTableNamesList(mObject, dbName, null);
}
String str[] = tableList.toArray(new String[tableList.size()]);
return str;
} else {
return null;
}
}
public Collection<User> getUsers(int startIndex, int numResults) {
List<String> usernames = new ArrayList<String>(numResults);
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
con = DbConnectionManager.getConnection();
pstmt = DbConnectionManager.createScrollablePreparedStatement(con, ALL_USERS);
rs = pstmt.executeQuery();
DbConnectionManager.setFetchSize(rs, startIndex + numResults);
DbConnectionManager.scrollResultSet(rs, startIndex);
int count = 0;
while (rs.next() && count < numResults) {
usernames.add(rs.getString(1));
count++;
}
} catch (SQLException e) {
Log.error(e);
} finally {
DbConnectionManager.closeConnection(rs, pstmt, con);
}
return new UserCollection(usernames.toArray(new String[usernames.size()]));
}
public RowData[] select() {
PreparedStatement stmt = null;
try {
stmt = conn.prepareStatement(selectStatement());
ResultSet rs = stmt.executeQuery();
List<RowData> list = new ArrayList<>();
while (rs.next()) {
String username = rs.getString("name");
String b64string = rs.getString(b64DataName());
list.add(new UserData(username, b64string));
}
return list.toArray(new RowData[0]);
} catch (SQLException sqe) {
sqe.printStackTrace();
return null;
} finally {
if (stmt != null)
try {
stmt.close();
} catch (SQLException sqe2) {
sqe2.printStackTrace();
}
}
}
@Override
public List<Map<String, Object>> searchForListMap(String sql, List<Object> params)
throws Exception {
consoleSql(sql, params);
return getReadJdbc().queryForList(sql, params.toArray());
}
private String[] toArray(List<String> list) {
return list == null ? null : list.toArray(new String[0]);
}