public static ProjectData[] select(
ConnectionProvider connectionProvider,
String rownum,
String language,
String adUserClient,
String adUserOrg,
String key,
String name,
String bpartner,
String orderBy,
String oraLimit,
String pgLimit,
int firstRegister,
int numberRegisters)
throws ServletException {
String strSql = "";
strSql = strSql + " SELECT * FROM ( SELECT ";
strSql = strSql + ((rownum == null || rownum.equals("")) ? "" : rownum);
strSql =
strSql
+ " AS RN1, A.* FROM ( "
+ " SELECT P.C_PROJECT_ID, P.VALUE, P.NAME, P.C_BPARTNER_ID, "
+ " B.NAME AS BPARTNER, L.NAME AS PROJECTSTATUS,"
+ " p.c_Project_Id || '@_##_@' || p.value || ' - ' || p.name as rowkey"
+ " FROM C_PROJECT P, C_BPARTNER B, AD_REF_LIST_V L "
+ " WHERE P.C_BPARTNER_ID = B.C_BPARTNER_ID "
+ " AND P.PROJECTSTATUS = L.VALUE "
+ " AND L.AD_REFERENCE_ID = '800002' "
+ " AND L.AD_LANGUAGE = ? "
+ " AND P.AD_CLIENT_ID IN (";
strSql = strSql + ((adUserClient == null || adUserClient.equals("")) ? "" : adUserClient);
strSql = strSql + ") " + " AND P.AD_ORG_ID IN (";
strSql = strSql + ((adUserOrg == null || adUserOrg.equals("")) ? "" : adUserOrg);
strSql = strSql + ") " + " AND P.ISACTIVE = 'Y'";
strSql =
strSql
+ ((key == null || key.equals("") || key.equals("%"))
? ""
: " AND UPPER(P.Value) LIKE UPPER(?) ");
strSql =
strSql
+ ((name == null || name.equals("") || name.equals("%"))
? ""
: " AND UPPER(P.Name) LIKE UPPER(?) ");
strSql =
strSql + ((bpartner == null || bpartner.equals("")) ? "" : " AND P.C_BPARTNER_ID = ? ");
strSql = strSql + " ORDER BY ";
strSql = strSql + ((orderBy == null || orderBy.equals("")) ? "" : orderBy);
strSql = strSql + " ) A ) B" + " WHERE 1=1";
strSql =
strSql + ((oraLimit == null || oraLimit.equals("")) ? "" : " AND RN1 BETWEEN " + oraLimit);
strSql = strSql + ((pgLimit == null || pgLimit.equals("")) ? "" : " LIMIT " + pgLimit);
ResultSet result;
Vector<java.lang.Object> vector = new Vector<java.lang.Object>(0);
PreparedStatement st = null;
int iParameter = 0;
try {
st = connectionProvider.getPreparedStatement(strSql);
if (rownum != null && !(rownum.equals(""))) {}
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, language);
if (adUserClient != null && !(adUserClient.equals(""))) {}
if (adUserOrg != null && !(adUserOrg.equals(""))) {}
if (key != null && !(key.equals("")) && !(key.equals("%"))) {
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, key);
}
if (name != null && !(name.equals("")) && !(name.equals("%"))) {
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, name);
}
if (bpartner != null && !(bpartner.equals(""))) {
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, bpartner);
}
if (orderBy != null && !(orderBy.equals(""))) {}
if (oraLimit != null && !(oraLimit.equals(""))) {}
if (pgLimit != null && !(pgLimit.equals(""))) {}
result = st.executeQuery();
long countRecord = 0;
long countRecordSkip = 1;
boolean continueResult = true;
while (countRecordSkip < firstRegister && continueResult) {
continueResult = result.next();
countRecordSkip++;
}
while (continueResult && result.next()) {
countRecord++;
ProjectData objectProjectData = new ProjectData();
objectProjectData.rn1 = UtilSql.getValue(result, "rn1");
objectProjectData.cProjectId = UtilSql.getValue(result, "c_project_id");
objectProjectData.value = UtilSql.getValue(result, "value");
objectProjectData.name = UtilSql.getValue(result, "name");
objectProjectData.cBpartnerId = UtilSql.getValue(result, "c_bpartner_id");
objectProjectData.bpartner = UtilSql.getValue(result, "bpartner");
objectProjectData.projectstatus = UtilSql.getValue(result, "projectstatus");
objectProjectData.rowkey = UtilSql.getValue(result, "rowkey");
objectProjectData.position = Long.toString(countRecord);
objectProjectData.InitRecordNumber = Integer.toString(firstRegister);
vector.addElement(objectProjectData);
if (countRecord >= numberRegisters && numberRegisters != 0) {
continueResult = false;
}
}
result.close();
} catch (SQLException e) {
log4j.error("SQL error in query: " + strSql + "Exception:" + e);
throw new ServletException(
"@CODE=" + Integer.toString(e.getErrorCode()) + "@" + e.getMessage());
} catch (Exception ex) {
log4j.error("Exception in query: " + strSql + "Exception:" + ex);
throw new ServletException("@CODE=@" + ex.getMessage());
} finally {
try {
connectionProvider.releasePreparedStatement(st);
} catch (Exception ignore) {
ignore.printStackTrace();
}
}
ProjectData objectProjectData[] = new ProjectData[vector.size()];
vector.copyInto(objectProjectData);
return (objectProjectData);
}
public static ProjectData[] selectKey(
ConnectionProvider connectionProvider,
String adUserClient,
String adUserOrg,
String bpartner,
String key,
int firstRegister,
int numberRegisters)
throws ServletException {
String strSql = "";
strSql =
strSql
+ " SELECT P.C_PROJECT_ID, P.VALUE, P.NAME, P.C_BPARTNER_ID, B.NAME AS BPARTNER"
+ " FROM C_PROJECT P, C_BPARTNER B "
+ " WHERE P.C_BPARTNER_ID = B.C_BPARTNER_ID "
+ " AND P.AD_CLIENT_ID IN (";
strSql = strSql + ((adUserClient == null || adUserClient.equals("")) ? "" : adUserClient);
strSql = strSql + ") " + " AND P.AD_ORG_ID IN (";
strSql = strSql + ((adUserOrg == null || adUserOrg.equals("")) ? "" : adUserOrg);
strSql = strSql + ") " + " AND P.ISACTIVE = 'Y'";
strSql =
strSql + ((bpartner == null || bpartner.equals("")) ? "" : " AND P.C_BPARTNER_ID = ? ");
strSql = strSql + " AND UPPER(P.VALUE) LIKE UPPER(?)" + " ORDER BY P.NAME";
ResultSet result;
Vector<java.lang.Object> vector = new Vector<java.lang.Object>(0);
PreparedStatement st = null;
int iParameter = 0;
try {
st = connectionProvider.getPreparedStatement(strSql);
if (adUserClient != null && !(adUserClient.equals(""))) {}
if (adUserOrg != null && !(adUserOrg.equals(""))) {}
if (bpartner != null && !(bpartner.equals(""))) {
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, bpartner);
}
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, key);
result = st.executeQuery();
long countRecord = 0;
long countRecordSkip = 1;
boolean continueResult = true;
while (countRecordSkip < firstRegister && continueResult) {
continueResult = result.next();
countRecordSkip++;
}
while (continueResult && result.next()) {
countRecord++;
ProjectData objectProjectData = new ProjectData();
objectProjectData.cProjectId = UtilSql.getValue(result, "c_project_id");
objectProjectData.value = UtilSql.getValue(result, "value");
objectProjectData.name = UtilSql.getValue(result, "name");
objectProjectData.cBpartnerId = UtilSql.getValue(result, "c_bpartner_id");
objectProjectData.bpartner = UtilSql.getValue(result, "bpartner");
objectProjectData.InitRecordNumber = Integer.toString(firstRegister);
vector.addElement(objectProjectData);
if (countRecord >= numberRegisters && numberRegisters != 0) {
continueResult = false;
}
}
result.close();
} catch (SQLException e) {
log4j.error("SQL error in query: " + strSql + "Exception:" + e);
throw new ServletException(
"@CODE=" + Integer.toString(e.getErrorCode()) + "@" + e.getMessage());
} catch (Exception ex) {
log4j.error("Exception in query: " + strSql + "Exception:" + ex);
throw new ServletException("@CODE=@" + ex.getMessage());
} finally {
try {
connectionProvider.releasePreparedStatement(st);
} catch (Exception ignore) {
ignore.printStackTrace();
}
}
ProjectData objectProjectData[] = new ProjectData[vector.size()];
vector.copyInto(objectProjectData);
return (objectProjectData);
}
