public static SEPeriodNoData[] getPeriodNo(
ConnectionProvider connectionProvider, String CYearId, int firstRegister, int numberRegisters)
throws ServletException {
String strSql = "";
strSql =
strSql
+ " SELECT C_Period_ID AS ID, Name AS NAME"
+ " FROM C_Period"
+ " WHERE C_Year_ID=?"
+ " ORDER BY StartDate ASC";
ResultSet result;
Vector<java.lang.Object> vector = new Vector<java.lang.Object>(0);
PreparedStatement st = null;
int iParameter = 0;
try {
st = connectionProvider.getPreparedStatement(strSql);
QueryTimeOutUtil.getInstance().setQueryTimeOut(st, SessionInfo.getQueryProfile());
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, CYearId);
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++;
SEPeriodNoData objectSEPeriodNoData = new SEPeriodNoData();
objectSEPeriodNoData.id = UtilSql.getValue(result, "ID");
objectSEPeriodNoData.name = UtilSql.getValue(result, "NAME");
objectSEPeriodNoData.InitRecordNumber = Integer.toString(firstRegister);
vector.addElement(objectSEPeriodNoData);
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();
}
}
SEPeriodNoData objectSEPeriodNoData[] = new SEPeriodNoData[vector.size()];
vector.copyInto(objectSEPeriodNoData);
return (objectSEPeriodNoData);
}
public static String existsOrgModule(
ConnectionProvider connectionProvider,
String AD_Client_ID,
String AD_Org_ID,
String AD_Module_ID,
String version)
throws ServletException {
String strSql = "";
strSql =
strSql
+ " SELECT COUNT(*)"
+ " FROM AD_ORGMODULE"
+ " WHERE AD_CLIENT_ID = ?"
+ " AND AD_ORG_ID = ?"
+ " AND AD_MODULE_ID = ?"
+ " AND VERSION = ?";
ResultSet result;
String strReturn = null;
PreparedStatement st = null;
int iParameter = 0;
try {
st = connectionProvider.getPreparedStatement(strSql);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, AD_Client_ID);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, AD_Org_ID);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, AD_Module_ID);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, version);
result = st.executeQuery();
if (result.next()) {
strReturn = UtilSql.getValue(result, "count");
}
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();
}
}
return (strReturn);
}
public static UpdateTransactionTypeTransactionTabData[] select(
ConnectionProvider connectionProvider, int firstRegister, int numberRegisters)
throws ServletException {
String strSql = "";
strSql = strSql + " SELECT 1 as name from dual";
ResultSet result;
Vector<java.lang.Object> vector = new Vector<java.lang.Object>(0);
PreparedStatement st = null;
try {
st = connectionProvider.getPreparedStatement(strSql);
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++;
UpdateTransactionTypeTransactionTabData objectUpdateTransactionTypeTransactionTabData =
new UpdateTransactionTypeTransactionTabData();
objectUpdateTransactionTypeTransactionTabData.name = UtilSql.getValue(result, "name");
objectUpdateTransactionTypeTransactionTabData.InitRecordNumber =
Integer.toString(firstRegister);
vector.addElement(objectUpdateTransactionTypeTransactionTabData);
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();
}
}
UpdateTransactionTypeTransactionTabData objectUpdateTransactionTypeTransactionTabData[] =
new UpdateTransactionTypeTransactionTabData[vector.size()];
vector.copyInto(objectUpdateTransactionTypeTransactionTabData);
return (objectUpdateTransactionTypeTransactionTabData);
}
public static String selectHasDefaults(
ConnectionProvider connectionProvider,
String tablename,
String parentClause,
String currentClause,
String orgId)
throws ServletException {
String strSql = "";
strSql = strSql + " SELECT count(*) as total" + " FROM ";
strSql = strSql + ((tablename == null || tablename.equals("")) ? "" : tablename);
strSql = strSql + " WHERE isDefault = 'Y'" + " AND 1=1 ";
strSql = strSql + ((parentClause == null || parentClause.equals("")) ? "" : parentClause);
strSql = strSql + " AND 3=3 ";
strSql = strSql + ((currentClause == null || currentClause.equals("")) ? "" : currentClause);
strSql = strSql + " AND AD_Org_ID = ?";
ResultSet result;
String strReturn = null;
PreparedStatement st = null;
int iParameter = 0;
try {
st = connectionProvider.getPreparedStatement(strSql);
QueryTimeOutUtil.getInstance().setQueryTimeOut(st, SessionInfo.getQueryProfile());
if (tablename != null && !(tablename.equals(""))) {}
if (parentClause != null && !(parentClause.equals(""))) {}
if (currentClause != null && !(currentClause.equals(""))) {}
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, orgId);
result = st.executeQuery();
if (result.next()) {
strReturn = UtilSql.getValue(result, "TOTAL");
}
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();
}
}
return (strReturn);
}
/** deprecated */
public static boolean isRoleOrg(ConnectionProvider connectionProvider, String rol, String org)
throws ServletException {
String strSql = "";
strSql =
strSql
+ " SELECT COUNT(*) AS TOTAL "
+ " FROM AD_ROLE_ORGACCESS A_R_O, AD_ROLE R "
+ " WHERE A_R_O.ISACTIVE = 'Y' "
+ " AND A_R_O.AD_ROLE_ID = R.AD_ROLE_ID "
+ " AND R.ISACTIVE = 'Y' "
+ " AND (LPAD(TO_CHAR(R.USERLEVEL), 1, '1')='S' OR A_R_O.AD_ORG_ID <> '0') "
+ " AND A_R_O.AD_ROLE_ID = ? "
+ " AND A_R_O.AD_ORG_ID = ?";
ResultSet result;
boolean boolReturn = false;
PreparedStatement st = null;
int iParameter = 0;
try {
st = connectionProvider.getPreparedStatement(strSql);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, rol);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, org);
result = st.executeQuery();
if (result.next()) {
boolReturn = !UtilSql.getValue(result, "total").equals("0");
}
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();
}
}
return (boolReturn);
}
public static String existsCost(
ConnectionProvider connectionProvider, String date, String mProductId)
throws ServletException {
String strSql = "";
strSql =
strSql
+ " SELECT COUNT(M_COSTING_ID)"
+ " FROM M_COSTING"
+ " WHERE TO_DATE(?) BETWEEN DATEFROM AND DATETO"
+ " AND COSTTYPE NOT IN ('STA', 'AVA')"
+ " AND M_PRODUCT_ID = ?";
ResultSet result;
String strReturn = null;
PreparedStatement st = null;
int iParameter = 0;
try {
st = connectionProvider.getPreparedStatement(strSql);
QueryTimeOutUtil.getInstance().setQueryTimeOut(st, SessionInfo.getQueryProfile());
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, date);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, mProductId);
result = st.executeQuery();
if (result.next()) {
strReturn = UtilSql.getValue(result, "COUNT(M_COSTING_ID)");
}
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();
}
}
return (strReturn);
}
public static boolean selectTables(
ConnectionProvider connectionProvider, String acctSchemaId, String tableId)
throws ServletException {
String strSql = "";
strSql =
strSql
+ " select count(*) as name"
+ " from c_acctschema_table where c_acctschema_id = ?"
+ " and ad_table_id = ?";
ResultSet result;
boolean boolReturn = false;
PreparedStatement st = null;
int iParameter = 0;
try {
st = connectionProvider.getPreparedStatement(strSql);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, acctSchemaId);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, tableId);
result = st.executeQuery();
if (result.next()) {
boolReturn = !UtilSql.getValue(result, "name").equals("0");
}
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();
}
}
return (boolReturn);
}
/** return the parent ID */
public static String selectParentID(ConnectionProvider connectionProvider, String key)
throws ServletException {
String strSql = "";
strSql =
strSql
+ " SELECT M_ProductionPlan.M_Production_ID AS NAME"
+ " FROM M_ProductionPlan"
+ " WHERE M_ProductionPlan.M_ProductionPlan_ID = ?";
ResultSet result;
String strReturn = "";
PreparedStatement st = null;
int iParameter = 0;
try {
st = connectionProvider.getPreparedStatement(strSql);
QueryTimeOutUtil.getInstance().setQueryTimeOut(st, SessionInfo.getQueryProfile());
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, key);
result = st.executeQuery();
if (result.next()) {
strReturn = UtilSql.getValue(result, "NAME");
}
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();
}
}
return (strReturn);
}
public static String getCurrentDBTimestamp(ConnectionProvider connectionProvider, String id)
throws ServletException {
String strSql = "";
strSql =
strSql
+ " SELECT to_char(Updated, 'YYYYMMDDHH24MISS') as Updated_Time_Stamp"
+ " FROM M_ProductionPlan"
+ " WHERE M_ProductionPlan.M_ProductionPlan_ID = ? ";
ResultSet result;
String strReturn = null;
PreparedStatement st = null;
int iParameter = 0;
try {
st = connectionProvider.getPreparedStatement(strSql);
QueryTimeOutUtil.getInstance().setQueryTimeOut(st, SessionInfo.getQueryProfile());
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, id);
result = st.executeQuery();
if (result.next()) {
strReturn = UtilSql.getValue(result, "UPDATED_TIME_STAMP");
}
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();
}
}
return (strReturn);
}
/** Select for auxiliar field */
public static String selectDef802026(ConnectionProvider connectionProvider, String MA_WRPhase_ID)
throws ServletException {
String strSql = "";
strSql =
strSql
+ " SELECT (QUANTITY - DONEQUANTITY) AS DefaultValue FROM MA_WRPhase WHERE MA_WRPhase_ID = ? ";
ResultSet result;
String strReturn = "";
PreparedStatement st = null;
int iParameter = 0;
try {
st = connectionProvider.getPreparedStatement(strSql);
QueryTimeOutUtil.getInstance().setQueryTimeOut(st, SessionInfo.getQueryProfile());
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, MA_WRPhase_ID);
result = st.executeQuery();
if (result.next()) {
strReturn = UtilSql.getValue(result, "DEFAULTVALUE");
}
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();
}
}
return (strReturn);
}
/** Select for parent field */
public static String selectParentTrl(ConnectionProvider connectionProvider, String mProductionId)
throws ServletException {
String strSql = "";
strSql =
strSql
+ " SELECT (TO_CHAR(COALESCE(TO_CHAR(table1.Name), '')) || ' - ' || TO_CHAR(COALESCE(TO_CHAR(table1.DocumentNo), '')) || ' - ' || TO_CHAR(table1.MovementDate, 'DD-MM-YYYY')) AS NAME FROM M_Production left join (select M_Production_ID, Name, DocumentNo, MovementDate from M_Production) table1 on (M_Production.M_Production_ID = table1.M_Production_ID) WHERE M_Production.M_Production_ID = ? ";
ResultSet result;
String strReturn = "";
PreparedStatement st = null;
int iParameter = 0;
try {
st = connectionProvider.getPreparedStatement(strSql);
QueryTimeOutUtil.getInstance().setQueryTimeOut(st, SessionInfo.getQueryProfile());
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, mProductionId);
result = st.executeQuery();
if (result.next()) {
strReturn = UtilSql.getValue(result, "NAME");
}
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();
}
}
return (strReturn);
}
/** Select for auxiliar field */
public static String selectDef4761_1(ConnectionProvider connectionProvider, String UpdatedByR)
throws ServletException {
String strSql = "";
strSql =
strSql
+ " SELECT ( COALESCE(TO_CHAR(TO_CHAR(COALESCE(TO_CHAR(table2.Name), ''))), '') ) as UpdatedBy FROM AD_User left join (select AD_User_ID, Name from AD_User) table2 on (AD_User.AD_User_ID = table2.AD_User_ID) WHERE AD_User.isActive='Y' AND AD_User.AD_User_ID = ? ";
ResultSet result;
String strReturn = "";
PreparedStatement st = null;
int iParameter = 0;
try {
st = connectionProvider.getPreparedStatement(strSql);
QueryTimeOutUtil.getInstance().setQueryTimeOut(st, SessionInfo.getQueryProfile());
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, UpdatedByR);
result = st.executeQuery();
if (result.next()) {
strReturn = UtilSql.getValue(result, "UPDATEDBY");
}
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();
}
}
return (strReturn);
}
public static boolean isExecuted(ConnectionProvider connectionProvider) throws ServletException {
String strSql = "";
strSql =
strSql
+ " SELECT count(*) as exist"
+ " FROM DUAL"
+ " WHERE EXISTS (SELECT 1 FROM ad_preference"
+ " WHERE attribute = 'UpdatedTransactionTypeV2')";
ResultSet result;
boolean boolReturn = false;
PreparedStatement st = null;
try {
st = connectionProvider.getPreparedStatement(strSql);
result = st.executeQuery();
if (result.next()) {
boolReturn = !UtilSql.getValue(result, "exist").equals("0");
}
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();
}
}
return (boolReturn);
}
public static String selectTercero(ConnectionProvider connectionProvider, String cBparnterId)
throws ServletException {
String strSql = "";
strSql = strSql + " SELECT NAME FROM C_BPARTNER WHERE C_BPARTNER_ID = ?";
ResultSet result;
String strReturn = "";
PreparedStatement st = null;
int iParameter = 0;
try {
st = connectionProvider.getPreparedStatement(strSql);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, cBparnterId);
result = st.executeQuery();
if (result.next()) {
strReturn = UtilSql.getValue(result, "name");
}
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();
}
}
return (strReturn);
}
public static String getDate(ConnectionProvider connectionProvider, String dateFormat)
throws ServletException {
String strSql = "";
strSql = strSql + " SELECT TO_CHAR(NOW(),TO_CHAR(?)) AS CURRENTDATE FROM DUAL";
ResultSet result;
String strReturn = null;
PreparedStatement st = null;
int iParameter = 0;
try {
st = connectionProvider.getPreparedStatement(strSql);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, dateFormat);
result = st.executeQuery();
if (result.next()) {
strReturn = UtilSql.getValue(result, "currentdate");
}
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();
}
}
return (strReturn);
}
public static ReportCashflowForecastData[] select(
ConnectionProvider connectionProvider,
String language,
String dateMax,
String bankaccount,
String adUserClient,
String adUserOrg,
int firstRegister,
int numberRegisters)
throws ServletException {
String strSql = "";
strSql =
strSql
+ " SELECT AD_COLUMN_IDENTIFIER(TO_CHAR('C_BankAccount'), TO_CHAR(BANKACCOUNTID), TO_CHAR(?)) as BANKACCOUNT, "
+ " INITIALBALANCE,"
+ " TRUNC(NOW()) AS CURRENTDATE, "
+ " INCOME, "
+ " PAYMENT, "
+ " INCOME-PAYMENT AS INCPAY, "
+ " INITIALBALANCE+INCOME-PAYMENT AS "
+ " FINALSUMMARY, C_BANKACCOUNT_ID, "
+ " '' AS C_DEBT_PAYMENT_ID, "
+ " '' AS DATEPLANNED, "
+ " '' AS BPARTNER,"
+ " '' AS DESCRIPTION, "
+ " '' AS INVOICENO, "
+ " '' AS DATEINVOICED,"
+ " '' AS AMOUNT,"
+ " '' AS ISRECEIPT,"
+ " '' AS URL,"
+ " '' AS ISRECEIPTMESSAGE FROM ( "
+ " SELECT BA.C_BANKACCOUNT_ID AS BANKACCOUNTID,"
+ " SUM(c_currency_convert(STMTAMT, BL.C_CURRENCY_ID, BA.C_CURRENCY_ID, BL.VALUTADATE, NULL, ba.ad_client_id, ba.ad_org_id)) AS INITIALBALANCE,"
+ " COALESCE((SELECT SUM(c_currency_convert(DP.AMOUNT, dp.C_CURRENCY_ID, ba.c_Currency_ID, dp.dateplanned,null, ba.ad_client_id, ba.ad_org_id))"
+ " FROM C_DEBT_PAYMENT_V DP"
+ " WHERE C_SETTLEMENT_CANCEL_ID IS NULL"
+ " AND DP.C_BANKACCOUNT_ID IS NOT NULL "
+ " AND C_DEBT_PAYMENT_STATUS(C_SETTLEMENT_CANCEL_ID, CANCEL_PROCESSED, GENERATE_PROCESSED, DP.ISPAID, ISVALID, C_CASHLINE_ID, C_BANKSTATEMENTLINE_ID) = 'P'"
+ " AND ISRECEIPT = 'Y'"
+ " AND DP.C_BANKACCOUNT_ID = BA.C_BANKACCOUNT_ID"
+ " AND 1=1";
strSql =
strSql
+ ((dateMax == null || dateMax.equals(""))
? ""
: " AND DP.DATEPLANNED <= TO_DATE(?) ");
strSql =
strSql
+ " AND NOT EXISTS (SELECT 1 "
+ " FROM C_REMITTANCELINE RL,"
+ " C_REMITTANCE R,"
+ " C_REMITTANCE_TYPE RT"
+ " WHERE R.C_REMITTANCE_ID = RL.C_REMITTANCE_ID"
+ " AND R.C_REMITTANCE_TYPE_ID = RT.C_REMITTANCE_TYPE_ID"
+ " AND CONSOLIDATE = 'Y' "
+ " AND (C_DEBT_PAYMENT_ID = DP.C_DEBT_PAYMENT_ID"
+ " OR C_DEBT_PAYMENT_CANCELLED = DP.C_DEBT_PAYMENT_ID)"
+ " AND NOT EXISTS (SELECT 1 /*IF CANCEL DP IS IN BANK (IT IS RETURNED) IT HAS TO APPEAR*/"
+ " FROM C_BANKSTATEMENTLINE BSL,"
+ " C_BANKSTATEMENT B"
+ " WHERE BSL.C_DEBT_PAYMENT_ID = RL.C_DEBT_PAYMENT_CANCELLED"
+ " AND BSL.C_BANKSTATEMENT_ID = B.C_BANKSTATEMENT_ID"
+ " AND B.PROCESSED='Y'))"
+ " ),0) AS INCOME,"
+ " -COALESCE((SELECT SUM(c_currency_convert(DP.AMOUNT, dp.C_CURRENCY_ID, ba.c_Currency_ID, dp.dateplanned,null, ba.ad_client_id, ba.ad_org_id))"
+ " FROM C_DEBT_PAYMENT_V DP"
+ " WHERE C_SETTLEMENT_CANCEL_ID IS NULL"
+ " AND DP.C_BANKACCOUNT_ID IS NOT NULL "
+ " AND C_DEBT_PAYMENT_STATUS(C_SETTLEMENT_CANCEL_ID, CANCEL_PROCESSED, GENERATE_PROCESSED, DP.ISPAID, ISVALID, C_CASHLINE_ID, C_BANKSTATEMENTLINE_ID) = 'P'"
+ " AND ISRECEIPT='N'"
+ " AND DP.C_BANKACCOUNT_ID = BA.C_BANKACCOUNT_ID"
+ " AND 2=2";
strSql =
strSql
+ ((dateMax == null || dateMax.equals(""))
? ""
: " AND DP.DATEPLANNED <= TO_DATE(?) ");
strSql =
strSql
+ " AND NOT EXISTS (SELECT 1 "
+ " FROM C_REMITTANCELINE RL,"
+ " C_REMITTANCE R,"
+ " C_REMITTANCE_TYPE RT"
+ " WHERE R.C_REMITTANCE_ID = RL.C_REMITTANCE_ID"
+ " AND R.C_REMITTANCE_TYPE_ID = RT.C_REMITTANCE_TYPE_ID"
+ " AND CONSOLIDATE = 'Y' "
+ " AND (C_DEBT_PAYMENT_ID = DP.C_DEBT_PAYMENT_ID"
+ " OR C_DEBT_PAYMENT_CANCELLED = DP.C_DEBT_PAYMENT_ID)"
+ " AND NOT EXISTS (SELECT 1 /*IF CANCEL DP IS IN BANK (IT IS RETURNED) IT HAS TO APPEAR*/"
+ " FROM C_BANKSTATEMENTLINE BSL,"
+ " C_BANKSTATEMENT B"
+ " WHERE BSL.C_DEBT_PAYMENT_ID = RL.C_DEBT_PAYMENT_CANCELLED"
+ " AND BSL.C_BANKSTATEMENT_ID = B.C_BANKSTATEMENT_ID"
+ " AND B.PROCESSED='Y'))"
+ " ),0) AS PAYMENT,"
+ " BA.C_BANKACCOUNT_ID"
+ " FROM C_BANKSTATEMENTLINE BL,"
+ " C_BANKSTATEMENT BS,"
+ " C_BANKACCOUNT BA"
+ " WHERE BS.C_BANKSTATEMENT_ID = BL.C_BANKSTATEMENT_ID"
+ " AND BA.C_BANKACCOUNT_ID = BS.C_BANKACCOUNT_ID"
+ " AND BS.PROCESSED='Y' "
+ " AND BS.STATEMENTDATE <= NOW()"
+ " AND 3=3";
strSql =
strSql
+ ((bankaccount == null || bankaccount.equals(""))
? ""
: " AND BA.C_BANKACCOUNT_ID = ? ");
strSql = strSql + " AND BA.AD_CLIENT_ID IN (";
strSql = strSql + ((adUserClient == null || adUserClient.equals("")) ? "" : adUserClient);
strSql = strSql + ")" + " AND BA.AD_ORG_ID IN (";
strSql = strSql + ((adUserOrg == null || adUserOrg.equals("")) ? "" : adUserOrg);
strSql =
strSql
+ ") "
+ " GROUP BY BA.C_BANKACCOUNT_ID, ba.c_Currency_ID, ba.ad_client_id, ba.ad_org_id "
+ " ) AAA "
+ " ORDER BY 1";
ResultSet result;
Vector<java.lang.Object> vector = new Vector<java.lang.Object>(0);
PreparedStatement st = null;
int iParameter = 0;
try {
st = connectionProvider.getPreparedStatement(strSql);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, language);
if (dateMax != null && !(dateMax.equals(""))) {
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, dateMax);
}
if (dateMax != null && !(dateMax.equals(""))) {
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, dateMax);
}
if (bankaccount != null && !(bankaccount.equals(""))) {
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, bankaccount);
}
if (adUserClient != null && !(adUserClient.equals(""))) {}
if (adUserOrg != null && !(adUserOrg.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++;
ReportCashflowForecastData objectReportCashflowForecastData =
new ReportCashflowForecastData();
objectReportCashflowForecastData.bankaccount = UtilSql.getValue(result, "bankaccount");
objectReportCashflowForecastData.initialbalance =
UtilSql.getValue(result, "initialbalance");
objectReportCashflowForecastData.currentdate =
UtilSql.getDateValue(result, "currentdate", "dd-MM-yyyy");
objectReportCashflowForecastData.income = UtilSql.getValue(result, "income");
objectReportCashflowForecastData.payment = UtilSql.getValue(result, "payment");
objectReportCashflowForecastData.incpay = UtilSql.getValue(result, "incpay");
objectReportCashflowForecastData.finalsummary = UtilSql.getValue(result, "finalsummary");
objectReportCashflowForecastData.cBankaccountId =
UtilSql.getValue(result, "c_bankaccount_id");
objectReportCashflowForecastData.cDebtPaymentId =
UtilSql.getValue(result, "c_debt_payment_id");
objectReportCashflowForecastData.dateplanned = UtilSql.getValue(result, "dateplanned");
objectReportCashflowForecastData.bpartner = UtilSql.getValue(result, "bpartner");
objectReportCashflowForecastData.description = UtilSql.getValue(result, "description");
objectReportCashflowForecastData.invoiceno = UtilSql.getValue(result, "invoiceno");
objectReportCashflowForecastData.dateinvoiced = UtilSql.getValue(result, "dateinvoiced");
objectReportCashflowForecastData.amount = UtilSql.getValue(result, "amount");
objectReportCashflowForecastData.isreceipt = UtilSql.getValue(result, "isreceipt");
objectReportCashflowForecastData.url = UtilSql.getValue(result, "url");
objectReportCashflowForecastData.isreceiptmessage =
UtilSql.getValue(result, "isreceiptmessage");
objectReportCashflowForecastData.rownum = Long.toString(countRecord);
objectReportCashflowForecastData.InitRecordNumber = Integer.toString(firstRegister);
vector.addElement(objectReportCashflowForecastData);
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();
}
}
ReportCashflowForecastData objectReportCashflowForecastData[] =
new ReportCashflowForecastData[vector.size()];
vector.copyInto(objectReportCashflowForecastData);
return (objectReportCashflowForecastData);
}
public static ReportCashflowForecastData[] selectAllLines(
ConnectionProvider connectionProvider,
String dateFormat,
String language,
String bankaccount,
String dateMax,
String order,
int firstRegister,
int numberRegisters)
throws ServletException {
String strSql = "";
strSql =
strSql
+ " SELECT (B.CODEBANK || '/' || B.CODEBRANCH || B.DIGITCONTROL || BA.CODEACCOUNT || '.' || BA.DIGITCONTROL) AS BANKACCOUNT,"
+ " DATEPLANNED, "
+ " ISRECEIPT, "
+ " COALESCE(mt.msgtext,m.msgtext) as IsReceiptMessage, "
+ " DP.C_DEBT_PAYMENT_ID, "
+ " BP.NAME AS BPARTNER,"
+ " DP.DESCRIPTION, "
+ " I.DOCUMENTNO AS INVOICENO, "
+ " TO_CHAR(I.DATEINVOICED,TO_CHAR(?)) AS DATEINVOICED,"
+ " c_currency_convert(DP.AMOUNT, dp.C_CURRENCY_ID, ba.c_Currency_ID, dp.dateplanned,null, ba.ad_client_id, ba.ad_org_id) AS AMOUNT,"
+ " (CASE WHEN DP.C_Invoice_ID IS NOT NULL"
+ " THEN (CASE ISRECEIPT WHEN 'Y' THEN 'SalesInvoice/Payment_Relation.html' WHEN 'N' THEN 'PurchaseInvoice/Payment_Relation.html' END) "
+ " ELSE"
+ " CASE WHEN DP.C_Order_ID IS NOT NULL"
+ " THEN (CASE ISRECEIPT WHEN 'Y' THEN 'SalesOrder/Payment_Relation.html' WHEN 'N' THEN 'PurchaseOrder/Payment_Relation.html' END)"
+ " ELSE"
+ " CASE WHEN DP.C_Settlement_Generate_ID IS NOT NULL"
+ " THEN 'Settlement/CreatedPayments_Relation.html'"
+ " END "
+ " END"
+ " END) AS URL,"
+ " (SELECT SUM(c_currency_convert(STMTAMT, BL.C_CURRENCY_ID, BA.C_CURRENCY_ID, BL.VALUTADATE, NULL, ba.ad_client_id, ba.ad_org_id)) AS BALANCE "
+ " FROM C_BANKSTATEMENTLINE BL,"
+ " C_BANKSTATEMENT BS"
+ " WHERE BS.C_BANKSTATEMENT_ID = BL.C_BANKSTATEMENT_ID"
+ " AND BS.PROCESSED='Y' "
+ " AND BS.STATEMENTDATE <= NOW()"
+ " AND BS.C_BANKACCOUNT_ID = DP.C_BANKACCOUNT_ID ) as initialbalance"
+ " FROM C_DEBT_PAYMENT_V DP LEFT JOIN C_INVOICE I ON DP.C_INVOICE_ID = I.C_INVOICE_ID,"
+ " AD_MESSAGE M LEFT JOIN AD_MESSAGE_TRL MT ON m.ad_message_id = mt.ad_message_id "
+ " and mt.ad_language = ?,"
+ " C_BPARTNER BP,"
+ " C_BANKACCOUNT BA,"
+ " C_BANK B "
+ " WHERE BA.C_BANKACCOUNT_ID = DP.C_BANKACCOUNT_ID"
+ " AND C_SETTLEMENT_CANCEL_ID IS NULL"
+ " AND BA.C_BANK_ID = B.C_BANK_ID "
+ " AND DP.C_BANKACCOUNT_ID IS NOT NULL "
+ " AND C_DEBT_PAYMENT_STATUS(C_SETTLEMENT_CANCEL_ID, CANCEL_PROCESSED, GENERATE_PROCESSED, DP.ISPAID, ISVALID, C_CASHLINE_ID, C_BANKSTATEMENTLINE_ID) = 'P'"
+ " AND BP.C_BPARTNER_ID = DP.C_BPARTNER_ID"
+ " AND 2=2";
strSql =
strSql
+ ((bankaccount == null || bankaccount.equals(""))
? ""
: " AND DP.C_BANKACCOUNT_ID = ? ");
strSql =
strSql
+ " AND M.VALUE = (CASE DP.ISRECEIPT WHEN 'Y' THEN 'Income' WHEN 'N' THEN 'Payments' END)"
+ " AND NOT EXISTS (SELECT 1 "
+ " FROM C_REMITTANCELINE RL,"
+ " C_REMITTANCE R,"
+ " C_REMITTANCE_TYPE RT"
+ " WHERE R.C_REMITTANCE_ID = RL.C_REMITTANCE_ID"
+ " AND R.C_REMITTANCE_TYPE_ID = RT.C_REMITTANCE_TYPE_ID"
+ " AND CONSOLIDATE = 'Y' "
+ " AND (C_DEBT_PAYMENT_ID = DP.C_DEBT_PAYMENT_ID"
+ " OR C_DEBT_PAYMENT_CANCELLED = DP.C_DEBT_PAYMENT_ID)"
+ " AND NOT EXISTS (SELECT 1 /*IF CANCEL DP IS IN BANK (IT IS RETURNED) IT HAS TO APPEAR*/"
+ " FROM C_BANKSTATEMENTLINE BSL,"
+ " C_BANKSTATEMENT B"
+ " WHERE BSL.C_DEBT_PAYMENT_ID = RL.C_DEBT_PAYMENT_CANCELLED"
+ " AND BSL.C_BANKSTATEMENT_ID = B.C_BANKSTATEMENT_ID"
+ " AND B.PROCESSED='Y'))"
+ " AND 1=1";
strSql =
strSql
+ ((dateMax == null || dateMax.equals(""))
? ""
: " AND DP.DATEPLANNED <= TO_DATE(?) ");
strSql = strSql + " ORDER BY ";
strSql = strSql + ((order == null || order.equals("")) ? "" : order);
ResultSet result;
Vector<java.lang.Object> vector = new Vector<java.lang.Object>(0);
PreparedStatement st = null;
int iParameter = 0;
try {
st = connectionProvider.getPreparedStatement(strSql);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, dateFormat);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, language);
if (bankaccount != null && !(bankaccount.equals(""))) {
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, bankaccount);
}
if (dateMax != null && !(dateMax.equals(""))) {
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, dateMax);
}
if (order != null && !(order.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++;
ReportCashflowForecastData objectReportCashflowForecastData =
new ReportCashflowForecastData();
objectReportCashflowForecastData.bankaccount = UtilSql.getValue(result, "bankaccount");
objectReportCashflowForecastData.dateplanned =
UtilSql.getDateValue(result, "dateplanned", "dd-MM-yyyy");
objectReportCashflowForecastData.isreceipt = UtilSql.getValue(result, "isreceipt");
objectReportCashflowForecastData.isreceiptmessage =
UtilSql.getValue(result, "isreceiptmessage");
objectReportCashflowForecastData.cDebtPaymentId =
UtilSql.getValue(result, "c_debt_payment_id");
objectReportCashflowForecastData.bpartner = UtilSql.getValue(result, "bpartner");
objectReportCashflowForecastData.description = UtilSql.getValue(result, "description");
objectReportCashflowForecastData.invoiceno = UtilSql.getValue(result, "invoiceno");
objectReportCashflowForecastData.dateinvoiced = UtilSql.getValue(result, "dateinvoiced");
objectReportCashflowForecastData.amount = UtilSql.getValue(result, "amount");
objectReportCashflowForecastData.url = UtilSql.getValue(result, "url");
objectReportCashflowForecastData.initialbalance =
UtilSql.getValue(result, "initialbalance");
objectReportCashflowForecastData.rownum = Long.toString(countRecord);
objectReportCashflowForecastData.InitRecordNumber = Integer.toString(firstRegister);
vector.addElement(objectReportCashflowForecastData);
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();
}
}
ReportCashflowForecastData objectReportCashflowForecastData[] =
new ReportCashflowForecastData[vector.size()];
vector.copyInto(objectReportCashflowForecastData);
return (objectReportCashflowForecastData);
}
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);
}
public static SLIsDefaultData[] select(
ConnectionProvider connectionProvider, String tableId, int firstRegister, int numberRegisters)
throws ServletException {
String strSql = "";
strSql =
strSql
+ " SELECT TableName, ColumnName, '' as total"
+ " FROM AD_TABLE T, AD_Column C"
+ " WHERE T.AD_Table_ID = ?"
+ " AND C.AD_Table_ID = T.AD_Table_ID"
+ " AND (C.Iskey = 'Y' or C.IsSecondaryKey = 'Y') ";
ResultSet result;
Vector<java.lang.Object> vector = new Vector<java.lang.Object>(0);
PreparedStatement st = null;
int iParameter = 0;
try {
st = connectionProvider.getPreparedStatement(strSql);
QueryTimeOutUtil.getInstance().setQueryTimeOut(st, SessionInfo.getQueryProfile());
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, tableId);
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++;
SLIsDefaultData objectSLIsDefaultData = new SLIsDefaultData();
objectSLIsDefaultData.tablename = UtilSql.getValue(result, "TABLENAME");
objectSLIsDefaultData.columnname = UtilSql.getValue(result, "COLUMNNAME");
objectSLIsDefaultData.total = UtilSql.getValue(result, "TOTAL");
objectSLIsDefaultData.InitRecordNumber = Integer.toString(firstRegister);
vector.addElement(objectSLIsDefaultData);
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();
}
}
SLIsDefaultData objectSLIsDefaultData[] = new SLIsDefaultData[vector.size()];
vector.copyInto(objectSLIsDefaultData);
return (objectSLIsDefaultData);
}
/** Select for edit */
public static ProductionRunData[] selectEdit(
ConnectionProvider connectionProvider,
String dateTimeFormat,
String paramLanguage,
String mProductionId,
String key,
String adUserClient,
String adOrgClient,
int firstRegister,
int numberRegisters)
throws ServletException {
String strSql = "";
strSql =
strSql
+ " SELECT to_char(M_ProductionPlan.Created, ?) as created, "
+ " (SELECT NAME FROM AD_USER u WHERE AD_USER_ID = M_ProductionPlan.CreatedBy) as CreatedByR, "
+ " to_char(M_ProductionPlan.Updated, ?) as updated, "
+ " to_char(M_ProductionPlan.Updated, 'YYYYMMDDHH24MISS') as Updated_Time_Stamp, "
+ " M_ProductionPlan.UpdatedBy, "
+ " (SELECT NAME FROM AD_USER u WHERE AD_USER_ID = M_ProductionPlan.UpdatedBy) as UpdatedByR,"
+ " M_ProductionPlan.Line, "
+ "M_ProductionPlan.MA_Wrphase_ID, "
+ "(CASE WHEN M_ProductionPlan.MA_Wrphase_ID IS NULL THEN '' ELSE (COALESCE(TO_CHAR(TO_CHAR(COALESCE(TO_CHAR(table2.DocumentNo), ''))),'') || ' - ' || COALESCE(TO_CHAR(TO_CHAR(table2.StartDate, 'DD-MM-YYYY')),'') || ' - ' || COALESCE(TO_CHAR(TO_CHAR(COALESCE(TO_CHAR(table1.SeqNo), ''))),'') || ' - ' || COALESCE(TO_CHAR(TO_CHAR(COALESCE(TO_CHAR(table3.Name), ''))),'') ) END) AS MA_Wrphase_IDR, "
+ "TO_CHAR(M_ProductionPlan.Starttime, ?) AS Starttime, "
+ "TO_CHAR(M_ProductionPlan.Endtime, ?) AS Endtime, "
+ "M_ProductionPlan.MA_Costcenter_Version_ID, "
+ "(CASE WHEN M_ProductionPlan.MA_Costcenter_Version_ID IS NULL THEN '' ELSE (COALESCE(TO_CHAR(TO_CHAR(COALESCE(TO_CHAR(table4.DocumentNo), ''))),'') || ' - ' || COALESCE(TO_CHAR(TO_CHAR(COALESCE(TO_CHAR(table5.Name), ''))),'') || ' - ' || COALESCE(TO_CHAR(TO_CHAR(table4.ValidFrom, 'DD-MM-YYYY')),'') ) END) AS MA_Costcenter_Version_IDR, "
+ "M_ProductionPlan.Neededquantity, "
+ "M_ProductionPlan.ProductionQty, "
+ "M_ProductionPlan.Secondaryunit, "
+ "M_ProductionPlan.Conversionrate, "
+ "M_ProductionPlan.Secondaryqty, "
+ "M_ProductionPlan.Rejectedquantity, "
+ "M_ProductionPlan.MA_Costcenteruse, "
+ "M_ProductionPlan.Usedmaterial, "
+ "COALESCE(M_ProductionPlan.Outsourced, 'N') AS Outsourced, "
+ "COALESCE(M_ProductionPlan.Processed, 'N') AS Processed, "
+ "M_ProductionPlan.Estimatedtime, "
+ "M_ProductionPlan.Runtime, "
+ "COALESCE(M_ProductionPlan.Closephase, 'N') AS Closephase, "
+ "M_ProductionPlan.Description, "
+ "M_ProductionPlan.M_Product_ID, "
+ "M_ProductionPlan.M_Locator_ID, "
+ "M_ProductionPlan.AD_Client_ID, "
+ "M_ProductionPlan.AD_Org_ID, "
+ "M_ProductionPlan.M_Production_ID, "
+ "M_ProductionPlan.M_ProductionPlan_ID, "
+ "COALESCE(M_ProductionPlan.IsActive, 'N') AS IsActive, "
+ " ? AS LANGUAGE "
+ " FROM M_ProductionPlan left join (select MA_Wrphase_ID, MA_Workrequirement_ID, SeqNo, MA_Process_ID from MA_Wrphase) table1 on (M_ProductionPlan.MA_Wrphase_ID = table1.MA_Wrphase_ID) left join (select MA_Workrequirement_ID, DocumentNo, StartDate from MA_Workrequirement) table2 on (table1.MA_Workrequirement_ID = table2.MA_Workrequirement_ID) left join (select MA_Process_ID, Name from MA_Process) table3 on (table1.MA_Process_ID = table3.MA_Process_ID) left join (select MA_Costcenter_Version_ID, DocumentNo, MA_Costcenter_ID, ValidFrom from MA_Costcenter_Version) table4 on (M_ProductionPlan.MA_Costcenter_Version_ID = table4.MA_Costcenter_Version_ID) left join (select MA_Costcenter_ID, Name from MA_Costcenter) table5 on (table4.MA_Costcenter_ID = table5.MA_Costcenter_ID)"
+ " WHERE 2=2 "
+ " AND 1=1 ";
strSql =
strSql
+ ((mProductionId == null || mProductionId.equals(""))
? ""
: " AND M_ProductionPlan.M_Production_ID = ? ");
strSql =
strSql
+ " AND M_ProductionPlan.M_ProductionPlan_ID = ? "
+ " AND M_ProductionPlan.AD_Client_ID IN (";
strSql = strSql + ((adUserClient == null || adUserClient.equals("")) ? "" : adUserClient);
strSql = strSql + ") " + " AND M_ProductionPlan.AD_Org_ID IN (";
strSql = strSql + ((adOrgClient == null || adOrgClient.equals("")) ? "" : adOrgClient);
strSql = strSql + ") ";
ResultSet result;
Vector<java.lang.Object> vector = new Vector<java.lang.Object>(0);
PreparedStatement st = null;
int iParameter = 0;
try {
st = connectionProvider.getPreparedStatement(strSql);
QueryTimeOutUtil.getInstance().setQueryTimeOut(st, SessionInfo.getQueryProfile());
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, dateTimeFormat);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, dateTimeFormat);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, dateTimeFormat);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, dateTimeFormat);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, paramLanguage);
if (mProductionId != null && !(mProductionId.equals(""))) {
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, mProductionId);
}
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, key);
if (adUserClient != null && !(adUserClient.equals(""))) {}
if (adOrgClient != null && !(adOrgClient.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++;
ProductionRunData objectProductionRunData = new ProductionRunData();
objectProductionRunData.created = UtilSql.getValue(result, "CREATED");
objectProductionRunData.createdbyr = UtilSql.getValue(result, "CREATEDBYR");
objectProductionRunData.updated = UtilSql.getValue(result, "UPDATED");
objectProductionRunData.updatedTimeStamp = UtilSql.getValue(result, "UPDATED_TIME_STAMP");
objectProductionRunData.updatedby = UtilSql.getValue(result, "UPDATEDBY");
objectProductionRunData.updatedbyr = UtilSql.getValue(result, "UPDATEDBYR");
objectProductionRunData.line = UtilSql.getValue(result, "LINE");
objectProductionRunData.maWrphaseId = UtilSql.getValue(result, "MA_WRPHASE_ID");
objectProductionRunData.maWrphaseIdr = UtilSql.getValue(result, "MA_WRPHASE_IDR");
objectProductionRunData.starttime = UtilSql.getValue(result, "STARTTIME");
objectProductionRunData.endtime = UtilSql.getValue(result, "ENDTIME");
objectProductionRunData.maCostcenterVersionId =
UtilSql.getValue(result, "MA_COSTCENTER_VERSION_ID");
objectProductionRunData.maCostcenterVersionIdr =
UtilSql.getValue(result, "MA_COSTCENTER_VERSION_IDR");
objectProductionRunData.neededquantity = UtilSql.getValue(result, "NEEDEDQUANTITY");
objectProductionRunData.productionqty = UtilSql.getValue(result, "PRODUCTIONQTY");
objectProductionRunData.secondaryunit = UtilSql.getValue(result, "SECONDARYUNIT");
objectProductionRunData.conversionrate = UtilSql.getValue(result, "CONVERSIONRATE");
objectProductionRunData.secondaryqty = UtilSql.getValue(result, "SECONDARYQTY");
objectProductionRunData.rejectedquantity = UtilSql.getValue(result, "REJECTEDQUANTITY");
objectProductionRunData.maCostcenteruse = UtilSql.getValue(result, "MA_COSTCENTERUSE");
objectProductionRunData.usedmaterial = UtilSql.getValue(result, "USEDMATERIAL");
objectProductionRunData.outsourced = UtilSql.getValue(result, "OUTSOURCED");
objectProductionRunData.processed = UtilSql.getValue(result, "PROCESSED");
objectProductionRunData.estimatedtime = UtilSql.getValue(result, "ESTIMATEDTIME");
objectProductionRunData.runtime = UtilSql.getValue(result, "RUNTIME");
objectProductionRunData.closephase = UtilSql.getValue(result, "CLOSEPHASE");
objectProductionRunData.description = UtilSql.getValue(result, "DESCRIPTION");
objectProductionRunData.mProductId = UtilSql.getValue(result, "M_PRODUCT_ID");
objectProductionRunData.mLocatorId = UtilSql.getValue(result, "M_LOCATOR_ID");
objectProductionRunData.adClientId = UtilSql.getValue(result, "AD_CLIENT_ID");
objectProductionRunData.adOrgId = UtilSql.getValue(result, "AD_ORG_ID");
objectProductionRunData.mProductionId = UtilSql.getValue(result, "M_PRODUCTION_ID");
objectProductionRunData.mProductionplanId = UtilSql.getValue(result, "M_PRODUCTIONPLAN_ID");
objectProductionRunData.isactive = UtilSql.getValue(result, "ISACTIVE");
objectProductionRunData.language = UtilSql.getValue(result, "LANGUAGE");
objectProductionRunData.adUserClient = "";
objectProductionRunData.adOrgClient = "";
objectProductionRunData.createdby = "";
objectProductionRunData.trBgcolor = "";
objectProductionRunData.totalCount = "";
objectProductionRunData.InitRecordNumber = Integer.toString(firstRegister);
vector.addElement(objectProductionRunData);
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();
}
}
ProductionRunData objectProductionRunData[] = new ProductionRunData[vector.size()];
vector.copyInto(objectProductionRunData);
return (objectProductionRunData);
}
public static RptCBpartnerData[] select(
ConnectionProvider connectionProvider,
String adLanguage,
String cBpartnerId,
int firstRegister,
int numberRegisters)
throws ServletException {
String strSql = "";
strSql =
strSql
+ " SELECT BP.C_BPARTNER_ID, BP.VALUE, AD_MESSAGE_GET2(BP.ISACTIVE, ?) AS ISACTIVE, TO_DATE(BP.CREATED) AS CREATED, TO_DATE(BP.UPDATED) AS UPDATED, "
+ " BP.NAME, BP.NAME2, G.NAME AS GROUPNAME, BP.TAXID, '' AS ACCOUNTNO, AD_MESSAGE_GET2(BP.ISEMPLOYEE, ?) AS ISEMPLOYEE, AD_MESSAGE_GET2(BP.ISSALESREP, ?) AS ISSALESREP, "
+ " ISCUSTOMER, ISVENDOR, '' AS SEQNO, '' AS CASCADE, '' AS DISCOUNT, '' AS QTY, '' AS CLASS_TREE"
+ " FROM C_BPARTNER BP, C_BP_GROUP G"
+ " WHERE BP.C_BP_GROUP_ID = G.C_BP_GROUP_ID";
strSql =
strSql
+ ((cBpartnerId == null || cBpartnerId.equals(""))
? ""
: " AND BP.C_BPARTNER_ID IN " + cBpartnerId);
ResultSet result;
Vector<java.lang.Object> vector = new Vector<java.lang.Object>(0);
PreparedStatement st = null;
int iParameter = 0;
try {
st = connectionProvider.getPreparedStatement(strSql);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, adLanguage);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, adLanguage);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, adLanguage);
if (cBpartnerId != null && !(cBpartnerId.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++;
RptCBpartnerData objectRptCBpartnerData = new RptCBpartnerData();
objectRptCBpartnerData.cBpartnerId = UtilSql.getValue(result, "c_bpartner_id");
objectRptCBpartnerData.value = UtilSql.getValue(result, "value");
objectRptCBpartnerData.isactive = UtilSql.getValue(result, "isactive");
objectRptCBpartnerData.created = UtilSql.getDateValue(result, "created", "dd-MM-yyyy");
objectRptCBpartnerData.updated = UtilSql.getDateValue(result, "updated", "dd-MM-yyyy");
objectRptCBpartnerData.name = UtilSql.getValue(result, "name");
objectRptCBpartnerData.name2 = UtilSql.getValue(result, "name2");
objectRptCBpartnerData.groupname = UtilSql.getValue(result, "groupname");
objectRptCBpartnerData.taxid = UtilSql.getValue(result, "taxid");
objectRptCBpartnerData.accountno = UtilSql.getValue(result, "accountno");
objectRptCBpartnerData.isemployee = UtilSql.getValue(result, "isemployee");
objectRptCBpartnerData.issalesrep = UtilSql.getValue(result, "issalesrep");
objectRptCBpartnerData.iscustomer = UtilSql.getValue(result, "iscustomer");
objectRptCBpartnerData.isvendor = UtilSql.getValue(result, "isvendor");
objectRptCBpartnerData.seqno = UtilSql.getValue(result, "seqno");
objectRptCBpartnerData.cascade = UtilSql.getValue(result, "cascade");
objectRptCBpartnerData.discount = UtilSql.getValue(result, "discount");
objectRptCBpartnerData.qty = UtilSql.getValue(result, "qty");
objectRptCBpartnerData.classTree = UtilSql.getValue(result, "class_tree");
objectRptCBpartnerData.position = Long.toString(countRecord);
objectRptCBpartnerData.InitRecordNumber = Integer.toString(firstRegister);
vector.addElement(objectRptCBpartnerData);
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();
}
}
RptCBpartnerData objectRptCBpartnerData[] = new RptCBpartnerData[vector.size()];
vector.copyInto(objectRptCBpartnerData);
return (objectRptCBpartnerData);
}
public static RptCBpartnerData[] selectTemplateDetail(
ConnectionProvider connectionProvider,
String cBpartnerId,
String typeTemplate,
int firstRegister,
int numberRegisters)
throws ServletException {
String strSql = "";
strSql =
strSql
+ " SELECT P.NAME, PT.QTY"
+ " FROM M_PRODUCT_TEMPLATE PT, M_PRODUCT P"
+ " WHERE PT.M_PRODUCT_ID = P.M_PRODUCT_ID"
+ " AND PT.C_BPARTNER_ID = ?"
+ " AND PT.TYPE_TEMPLATE = ?"
+ " 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);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, cBpartnerId);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, typeTemplate);
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++;
RptCBpartnerData objectRptCBpartnerData = new RptCBpartnerData();
objectRptCBpartnerData.name = UtilSql.getValue(result, "name");
objectRptCBpartnerData.qty = UtilSql.getValue(result, "qty");
objectRptCBpartnerData.position = Long.toString(countRecord);
objectRptCBpartnerData.InitRecordNumber = Integer.toString(firstRegister);
vector.addElement(objectRptCBpartnerData);
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();
}
}
RptCBpartnerData objectRptCBpartnerData[] = new RptCBpartnerData[vector.size()];
vector.copyInto(objectRptCBpartnerData);
return (objectRptCBpartnerData);
}
public static DocInOutData[] select(
ConnectionProvider connectionProvider, int firstRegister, int numberRegisters)
throws ServletException {
String strSql = "";
strSql =
strSql
+ " SELECT '' AS M_INOUT_ID, '' AS AD_CLIENT_ID, '' AS AD_ORG_ID, '' AS ISACTIVE, '' AS CREATED,"
+ " '' AS CREATEDBY, '' AS UPDATED, '' AS UPDATEDBY, '' AS ISSOTRX, '' AS DOCUMENTNO, '' AS DOCACTION,"
+ " '' AS DOCSTATUS, '' AS POSTED, '' AS PROCESSING, '' AS PROCESSED, '' AS C_DOCTYPE_ID, '' AS DESCRIPTION,"
+ " '' AS C_ORDER_ID, '' AS DATEORDERED, '' AS ISPRINTED, '' AS MOVEMENTTYPE, '' AS MOVEMENTDATE, '' AS DATEACCT,"
+ " '' AS C_BPARTNER_ID, '' AS C_BPARTNER_LOCATION_ID, '' AS M_WAREHOUSE_ID, '' AS POREFERENCE, '' AS DELIVERYRULE,"
+ " '' AS FREIGHTCOSTRULE, '' AS FREIGHTAMT, '' AS DELIVERYVIARULE, '' AS M_SHIPPER_ID, '' AS C_CHARGE_ID,"
+ " '' AS CHARGEAMT, '' AS PRIORITYRULE, '' AS DATEPRINTED, '' AS C_INVOICE_ID, '' AS CREATEFROM, '' AS GENERATETO,"
+ " '' AS SENDEMAIL, '' AS AD_USER_ID, '' AS SALESREP_ID, '' AS NOPACKAGES, '' AS PICKDATE, '' AS SHIPDATE,"
+ " '' AS TRACKINGNO, '' AS AD_ORGTRX_ID, '' AS C_PROJECT_ID, '' AS C_CAMPAIGN_ID, '' AS C_ACTIVITY_ID, '' AS USER1_ID,"
+ " '' AS USER2_ID, '' AS C_COSTCENTER_ID, '' AS UPDATELINES, '' AS M_COSTING_ID, '' AS stdprecision"
+ " FROM DUAL";
ResultSet result;
Vector<java.lang.Object> vector = new Vector<java.lang.Object>(0);
PreparedStatement st = null;
try {
st = connectionProvider.getPreparedStatement(strSql);
QueryTimeOutUtil.getInstance().setQueryTimeOut(st, SessionInfo.getQueryProfile());
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++;
DocInOutData objectDocInOutData = new DocInOutData();
objectDocInOutData.mInoutId = UtilSql.getValue(result, "M_INOUT_ID");
objectDocInOutData.adClientId = UtilSql.getValue(result, "AD_CLIENT_ID");
objectDocInOutData.adOrgId = UtilSql.getValue(result, "AD_ORG_ID");
objectDocInOutData.isactive = UtilSql.getValue(result, "ISACTIVE");
objectDocInOutData.created = UtilSql.getValue(result, "CREATED");
objectDocInOutData.createdby = UtilSql.getValue(result, "CREATEDBY");
objectDocInOutData.updated = UtilSql.getValue(result, "UPDATED");
objectDocInOutData.updatedby = UtilSql.getValue(result, "UPDATEDBY");
objectDocInOutData.issotrx = UtilSql.getValue(result, "ISSOTRX");
objectDocInOutData.documentno = UtilSql.getValue(result, "DOCUMENTNO");
objectDocInOutData.docaction = UtilSql.getValue(result, "DOCACTION");
objectDocInOutData.docstatus = UtilSql.getValue(result, "DOCSTATUS");
objectDocInOutData.posted = UtilSql.getValue(result, "POSTED");
objectDocInOutData.processing = UtilSql.getValue(result, "PROCESSING");
objectDocInOutData.processed = UtilSql.getValue(result, "PROCESSED");
objectDocInOutData.cDoctypeId = UtilSql.getValue(result, "C_DOCTYPE_ID");
objectDocInOutData.description = UtilSql.getValue(result, "DESCRIPTION");
objectDocInOutData.cOrderId = UtilSql.getValue(result, "C_ORDER_ID");
objectDocInOutData.dateordered = UtilSql.getValue(result, "DATEORDERED");
objectDocInOutData.isprinted = UtilSql.getValue(result, "ISPRINTED");
objectDocInOutData.movementtype = UtilSql.getValue(result, "MOVEMENTTYPE");
objectDocInOutData.movementdate = UtilSql.getValue(result, "MOVEMENTDATE");
objectDocInOutData.dateacct = UtilSql.getValue(result, "DATEACCT");
objectDocInOutData.cBpartnerId = UtilSql.getValue(result, "C_BPARTNER_ID");
objectDocInOutData.cBpartnerLocationId = UtilSql.getValue(result, "C_BPARTNER_LOCATION_ID");
objectDocInOutData.mWarehouseId = UtilSql.getValue(result, "M_WAREHOUSE_ID");
objectDocInOutData.poreference = UtilSql.getValue(result, "POREFERENCE");
objectDocInOutData.deliveryrule = UtilSql.getValue(result, "DELIVERYRULE");
objectDocInOutData.freightcostrule = UtilSql.getValue(result, "FREIGHTCOSTRULE");
objectDocInOutData.freightamt = UtilSql.getValue(result, "FREIGHTAMT");
objectDocInOutData.deliveryviarule = UtilSql.getValue(result, "DELIVERYVIARULE");
objectDocInOutData.mShipperId = UtilSql.getValue(result, "M_SHIPPER_ID");
objectDocInOutData.cChargeId = UtilSql.getValue(result, "C_CHARGE_ID");
objectDocInOutData.chargeamt = UtilSql.getValue(result, "CHARGEAMT");
objectDocInOutData.priorityrule = UtilSql.getValue(result, "PRIORITYRULE");
objectDocInOutData.dateprinted = UtilSql.getValue(result, "DATEPRINTED");
objectDocInOutData.cInvoiceId = UtilSql.getValue(result, "C_INVOICE_ID");
objectDocInOutData.createfrom = UtilSql.getValue(result, "CREATEFROM");
objectDocInOutData.generateto = UtilSql.getValue(result, "GENERATETO");
objectDocInOutData.sendemail = UtilSql.getValue(result, "SENDEMAIL");
objectDocInOutData.adUserId = UtilSql.getValue(result, "AD_USER_ID");
objectDocInOutData.salesrepId = UtilSql.getValue(result, "SALESREP_ID");
objectDocInOutData.nopackages = UtilSql.getValue(result, "NOPACKAGES");
objectDocInOutData.pickdate = UtilSql.getValue(result, "PICKDATE");
objectDocInOutData.shipdate = UtilSql.getValue(result, "SHIPDATE");
objectDocInOutData.trackingno = UtilSql.getValue(result, "TRACKINGNO");
objectDocInOutData.adOrgtrxId = UtilSql.getValue(result, "AD_ORGTRX_ID");
objectDocInOutData.cProjectId = UtilSql.getValue(result, "C_PROJECT_ID");
objectDocInOutData.cCampaignId = UtilSql.getValue(result, "C_CAMPAIGN_ID");
objectDocInOutData.cActivityId = UtilSql.getValue(result, "C_ACTIVITY_ID");
objectDocInOutData.user1Id = UtilSql.getValue(result, "USER1_ID");
objectDocInOutData.user2Id = UtilSql.getValue(result, "USER2_ID");
objectDocInOutData.cCostcenterId = UtilSql.getValue(result, "C_COSTCENTER_ID");
objectDocInOutData.updatelines = UtilSql.getValue(result, "UPDATELINES");
objectDocInOutData.mCostingId = UtilSql.getValue(result, "M_COSTING_ID");
objectDocInOutData.stdprecision = UtilSql.getValue(result, "STDPRECISION");
objectDocInOutData.InitRecordNumber = Integer.toString(firstRegister);
vector.addElement(objectDocInOutData);
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();
}
}
DocInOutData objectDocInOutData[] = new DocInOutData[vector.size()];
vector.copyInto(objectDocInOutData);
return (objectDocInOutData);
}
public static DocInOutData[] selectRegistro(
ConnectionProvider connectionProvider,
String client,
String id,
int firstRegister,
int numberRegisters)
throws ServletException {
String strSql = "";
strSql =
strSql
+ " SELECT I.AD_CLIENT_ID, I.AD_ORG_ID, I.C_BPARTNER_ID, I.AD_ORGTRX_ID, I.C_PROJECT_ID,"
+ " I.C_CAMPAIGN_ID, I.C_ACTIVITY_ID, I.USER1_ID, I.USER2_ID, I.C_COSTCENTER_ID, I.DOCUMENTNO, I.DATEACCT,"
+ " I.C_DOCTYPE_ID, I.C_CHARGE_ID, I.CHARGEAMT, I.POSTED, I.MOVEMENTDATE, I.C_BPARTNER_LOCATION_ID"
+ " FROM M_INOUT I"
+ " WHERE AD_Client_ID=? "
+ " AND M_INOUT_ID=?";
ResultSet result;
Vector<java.lang.Object> vector = new Vector<java.lang.Object>(0);
PreparedStatement st = null;
int iParameter = 0;
try {
st = connectionProvider.getPreparedStatement(strSql);
QueryTimeOutUtil.getInstance().setQueryTimeOut(st, SessionInfo.getQueryProfile());
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, client);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, id);
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++;
DocInOutData objectDocInOutData = new DocInOutData();
objectDocInOutData.adClientId = UtilSql.getValue(result, "AD_CLIENT_ID");
objectDocInOutData.adOrgId = UtilSql.getValue(result, "AD_ORG_ID");
objectDocInOutData.cBpartnerId = UtilSql.getValue(result, "C_BPARTNER_ID");
objectDocInOutData.adOrgtrxId = UtilSql.getValue(result, "AD_ORGTRX_ID");
objectDocInOutData.cProjectId = UtilSql.getValue(result, "C_PROJECT_ID");
objectDocInOutData.cCampaignId = UtilSql.getValue(result, "C_CAMPAIGN_ID");
objectDocInOutData.cActivityId = UtilSql.getValue(result, "C_ACTIVITY_ID");
objectDocInOutData.user1Id = UtilSql.getValue(result, "USER1_ID");
objectDocInOutData.user2Id = UtilSql.getValue(result, "USER2_ID");
objectDocInOutData.cCostcenterId = UtilSql.getValue(result, "C_COSTCENTER_ID");
objectDocInOutData.documentno = UtilSql.getValue(result, "DOCUMENTNO");
objectDocInOutData.dateacct = UtilSql.getDateValue(result, "DATEACCT", "dd-MM-yyyy");
objectDocInOutData.cDoctypeId = UtilSql.getValue(result, "C_DOCTYPE_ID");
objectDocInOutData.cChargeId = UtilSql.getValue(result, "C_CHARGE_ID");
objectDocInOutData.chargeamt = UtilSql.getValue(result, "CHARGEAMT");
objectDocInOutData.posted = UtilSql.getValue(result, "POSTED");
objectDocInOutData.movementdate =
UtilSql.getDateValue(result, "MOVEMENTDATE", "dd-MM-yyyy");
objectDocInOutData.cBpartnerLocationId = UtilSql.getValue(result, "C_BPARTNER_LOCATION_ID");
objectDocInOutData.InitRecordNumber = Integer.toString(firstRegister);
vector.addElement(objectDocInOutData);
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();
}
}
DocInOutData objectDocInOutData[] = new DocInOutData[vector.size()];
vector.copyInto(objectDocInOutData);
return (objectDocInOutData);
}
public static CreateAccountingConfigurationData[] selectAcctSchema(
ConnectionProvider connectionProvider, int firstRegister, int numberRegisters)
throws ServletException {
String strSql = "";
strSql =
strSql
+ " SELECT C_AcctSchema_ID, ad_client_id, '' as ad_org_id, '' as ad_table_id, '' as name, '' as c_period_id, '' as value, '' as status,"
+ " '' as isdefaultacct"
+ " FROM C_AcctSchema";
ResultSet result;
Vector<java.lang.Object> vector = new Vector<java.lang.Object>(0);
PreparedStatement st = null;
try {
st = connectionProvider.getPreparedStatement(strSql);
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++;
CreateAccountingConfigurationData objectCreateAccountingConfigurationData =
new CreateAccountingConfigurationData();
objectCreateAccountingConfigurationData.cAcctschemaId =
UtilSql.getValue(result, "c_acctschema_id");
objectCreateAccountingConfigurationData.adClientId =
UtilSql.getValue(result, "ad_client_id");
objectCreateAccountingConfigurationData.adOrgId = UtilSql.getValue(result, "ad_org_id");
objectCreateAccountingConfigurationData.adTableId = UtilSql.getValue(result, "ad_table_id");
objectCreateAccountingConfigurationData.name = UtilSql.getValue(result, "name");
objectCreateAccountingConfigurationData.cPeriodId = UtilSql.getValue(result, "c_period_id");
objectCreateAccountingConfigurationData.value = UtilSql.getValue(result, "value");
objectCreateAccountingConfigurationData.status = UtilSql.getValue(result, "status");
objectCreateAccountingConfigurationData.isdefaultacct =
UtilSql.getValue(result, "isdefaultacct");
objectCreateAccountingConfigurationData.InitRecordNumber = Integer.toString(firstRegister);
vector.addElement(objectCreateAccountingConfigurationData);
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();
}
}
CreateAccountingConfigurationData objectCreateAccountingConfigurationData[] =
new CreateAccountingConfigurationData[vector.size()];
vector.copyInto(objectCreateAccountingConfigurationData);
return (objectCreateAccountingConfigurationData);
}
public static RptCBpartnerData[] selectTemplate(
ConnectionProvider connectionProvider,
String adLanguage,
String cBpartnerId,
int firstRegister,
int numberRegisters)
throws ServletException {
String strSql = "";
strSql =
strSql
+ " SELECT AD_REF_LIST_V.VALUE AS VALUE, AD_REF_LIST_V.NAME AS NAME2, 'datawarehouseclose' AS CLASS_TREE"
+ " FROM M_PRODUCT_TEMPLATE PT, AD_REF_LIST_V "
+ " WHERE PT.TYPE_TEMPLATE = AD_REF_LIST_V.VALUE"
+ " AND AD_REF_LIST_V.AD_LANGUAGE = ?"
+ " AND AD_REF_LIST_V.AD_REFERENCE_ID = '800009'";
strSql =
strSql
+ ((cBpartnerId == null || cBpartnerId.equals(""))
? ""
: " AND PT.C_BPARTNER_ID IN " + cBpartnerId);
strSql = strSql + " ORDER BY name2";
ResultSet result;
Vector<java.lang.Object> vector = new Vector<java.lang.Object>(0);
PreparedStatement st = null;
int iParameter = 0;
try {
st = connectionProvider.getPreparedStatement(strSql);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, adLanguage);
if (cBpartnerId != null && !(cBpartnerId.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++;
RptCBpartnerData objectRptCBpartnerData = new RptCBpartnerData();
objectRptCBpartnerData.value = UtilSql.getValue(result, "value");
objectRptCBpartnerData.name2 = UtilSql.getValue(result, "name2");
objectRptCBpartnerData.classTree = UtilSql.getValue(result, "class_tree");
objectRptCBpartnerData.position = Long.toString(countRecord);
objectRptCBpartnerData.InitRecordNumber = Integer.toString(firstRegister);
vector.addElement(objectRptCBpartnerData);
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();
}
}
RptCBpartnerData objectRptCBpartnerData[] = new RptCBpartnerData[vector.size()];
vector.copyInto(objectRptCBpartnerData);
return (objectRptCBpartnerData);
}
public static String countRows(
ConnectionProvider connectionProvider,
String rownum,
String language,
String adUserClient,
String adUserOrg,
String key,
String name,
String bpartner,
String pgLimit,
String oraLimit1,
String oraLimit2)
throws ServletException {
String strSql = "";
strSql = strSql + " SELECT COUNT(*) AS VALUE FROM ( SELECT ";
strSql = strSql + ((rownum == null || rownum.equals("")) ? "" : rownum);
strSql =
strSql
+ " AS RN1, B.* FROM ( "
+ " SELECT 1"
+ " 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 + " AND 1=1";
strSql = strSql + ((pgLimit == null || pgLimit.equals("")) ? "" : " LIMIT " + pgLimit);
strSql = strSql + " ) B";
strSql =
strSql
+ ((oraLimit1 == null || oraLimit1.equals("")) ? "" : " WHERE ROWNUM <= " + oraLimit1);
strSql = strSql + " ) A ";
strSql =
strSql
+ ((oraLimit2 == null || oraLimit2.equals(""))
? ""
: " WHERE RN1 BETWEEN " + oraLimit2);
ResultSet result;
String strReturn = null;
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 (pgLimit != null && !(pgLimit.equals(""))) {}
if (oraLimit1 != null && !(oraLimit1.equals(""))) {}
if (oraLimit2 != null && !(oraLimit2.equals(""))) {}
result = st.executeQuery();
if (result.next()) {
strReturn = UtilSql.getValue(result, "value");
}
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();
}
}
return (strReturn);
}
/** deprecated */
public static OrganizationComboData[] select(
ConnectionProvider connectionProvider, String rol, int firstRegister, int numberRegisters)
throws ServletException {
String strSql = "";
strSql =
strSql
+ " SELECT A_O.AD_ORG_ID, A_O.NAME, '' AS ID "
+ " FROM AD_ORG A_O, AD_ROLE_ORGACCESS A_R_O, AD_ROLE R "
+ " WHERE A_R_O.AD_ORG_ID = A_O.AD_ORG_ID "
+ " AND A_R_O.ISACTIVE = 'Y' "
+ " AND A_R_O.AD_ROLE_ID = R.AD_ROLE_ID "
+ " AND R.ISACTIVE = 'Y' "
+ " AND (LPAD(TO_CHAR(R.USERLEVEL), 1, '1')='S' OR A_O.AD_ORG_ID <> '0') "
+ " AND A_R_O.AD_ROLE_ID = ? "
+ " ORDER BY A_O.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);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, rol);
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++;
OrganizationComboData objectOrganizationComboData = new OrganizationComboData();
objectOrganizationComboData.adOrgId = UtilSql.getValue(result, "ad_org_id");
objectOrganizationComboData.name = UtilSql.getValue(result, "name");
objectOrganizationComboData.id = UtilSql.getValue(result, "id");
objectOrganizationComboData.InitRecordNumber = Integer.toString(firstRegister);
vector.addElement(objectOrganizationComboData);
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();
}
}
OrganizationComboData objectOrganizationComboData[] = new OrganizationComboData[vector.size()];
vector.copyInto(objectOrganizationComboData);
return (objectOrganizationComboData);
}
public static ReportDebtPaymentData[] selectNoBpartner(
ConnectionProvider connectionProvider,
String cCurrencyConv,
String adLanguage,
String adUserClient,
String adUserOrg,
String cBpartnerId,
String dateFrom,
String dateTo,
String AmountFrom,
String AmountTo,
String paymentRule,
String isReceipt,
String status,
String isSettle,
String cbankaccount,
String ord,
int firstRegister,
int numberRegisters)
throws ServletException {
String strSql = "";
strSql =
strSql
+ " SELECT (BANK.NAME || '/' || BANK.CODEBRANCH || BANK.DIGITCONTROL || BA.CODEACCOUNT || '.' || BA.DIGITCONTROL) AS BANKACC,"
+ " (CASE BA.showspanish WHEN 'Y' then BA.CODEACCOUNT ELSE "
+ " CASE BA.SHOWGENERIC when 'Y' then GENERICACCOUNT ELSE "
+ " CASE BA.SHOWIBAN WHEN 'Y' THEN BA.IBAN END END END) AS ACCOUNTSTR,"
+ " (CASE WHEN I.C_INVOICE_ID IS NULL THEN '0' ELSE I.C_INVOICE_ID END) AS C_INVOICE_ID, (CASE WHEN DP.C_DEBT_PAYMENT_ID IS NULL THEN '0' ELSE DP.C_DEBT_PAYMENT_ID END) AS C_DEBT_PAYMENT_ID, B.NAME AS BPARTNER, (CASE WHEN I.DOCUMENTNO IS NOT NULL THEN I.DOCUMENTNO||' ('||I.DATEINVOICED||')' END) AS INVOICE, DP.DATEPLANNED,"
+ " DP.AMOUNT, DP.WRITEOFFAMT, C.ISO_CODE AS CURRENCY,"
+ " (CASE WHEN S1.DOCUMENTNO IS NOT NULL THEN S1.DOCUMENTNO||' ('||S1.DATETRX||')' END) AS DEBTCANCEL, (CASE WHEN S2.DOCUMENTNO IS NOT NULL THEN S2.DOCUMENTNO||' ('||S2.DATETRX||')' END) AS DEBTGENERATE, COALESCE(RLT.NAME, RL.NAME) AS PAYMENTRULE,"
+ " C_CURRENCY_CONVERT(DP.AMOUNT, DP.C_CURRENCY_ID, ?, TO_DATE(I.DATEINVOICED), NULL, I.AD_CLIENT_ID, I.AD_ORG_ID) AS CONVAMOUNT,"
+ " C_CURRENCY_SYMBOL(?, 0, 'Y') AS CONVSYM, "
+ " C_CURRENCY_ISOSYM(?) AS CONVISOSYM, "
+ " (CASE C_DEBT_PAYMENT_STATUS(DP.C_Settlement_Cancel_ID, DP.CANCEL_PROCESSED, DP.GENERATE_PROCESSED, DP.ISPAID, DP.ISVALID, "
+ " DP.C_CASHLINE_ID, DP.C_BANKSTATEMENTLINE_ID) WHEN 'I' THEN AD_MESSAGE_GET2('DPR_INVALID',?) WHEN 'C' THEN AD_MESSAGE_GET2('DPR_CONCILIATED',?) WHEN 'W' THEN AD_MESSAGE_GET2('DPR_CANCELLED',?) WHEN 'A' THEN AD_MESSAGE_GET2('DPR_SETTLE',?) WHEN "
+ " 'P' THEN AD_MESSAGE_GET2('DPR_PENDING',?) END) AS STATUS,"
+ " DP.DESCRIPTION, AD_MESSAGE_GET2(DP.ISPAID, ?) AS ISPAID, "
+ " (CASE DP.ISRECEIPT WHEN 'Y' THEN 'SalesInvoice' ELSE 'PurchaseInvoice' END) AS INVOICE_TYPE, "
+ " (CASE s2.SETTLEMENTTYPE WHEN 'I' THEN '../ManualSettlement/CreatePayment_Relation.html' ELSE '../Settlement/CreatedPayments_Relation.html' END) AS PATH, COALESCE(RLT2.NAME, RL2.NAME) AS DPSTATUS, URS.NAME AS SALESREPNAME"
+ " FROM C_DEBT_PAYMENT DP left join C_SETTLEMENT S1 on DP.C_SETTLEMENT_CANCEL_ID = S1.C_SETTLEMENT_ID"
+ " left join C_SETTLEMENT S2 on DP.C_SETTLEMENT_GENERATE_ID = S2.C_SETTLEMENT_ID"
+ " left join C_BPARTNER B on DP.C_BPARTNER_ID = B.C_BPARTNER_ID"
+ " left join C_BANKACCOUNT BA on DP.C_BANKACCOUNT_ID = BA.C_BANKACCOUNT_ID"
+ " left join C_BANK BANK ON BA.C_BANK_ID = BANK.C_BANK_ID"
+ " left join C_INVOICE I on DP.C_INVOICE_ID = I.C_INVOICE_ID left join AD_USER URS on I.SALESREP_ID = URS.AD_USER_ID,"
+ " AD_REF_LIST RL left join AD_REF_LIST_TRL RLT on RL.AD_REF_LIST_ID = RLT.AD_REF_LIST_ID"
+ " AND RLT.AD_LANGUAGE = ?,"
+ " AD_REF_LIST RL2 left join AD_REF_LIST_TRL RLT2 on RL2.AD_REF_LIST_ID = RLT2.AD_REF_LIST_ID"
+ " AND RLT2.AD_LANGUAGE = ?,"
+ " C_CURRENCY C"
+ " WHERE DP.C_CURRENCY_ID = C.C_CURRENCY_ID "
+ " AND DP.PAYMENTRULE = RL.VALUE"
+ " AND RL.AD_REFERENCE_ID = '195'"
+ " AND DP.STATUS = RL2.VALUE"
+ " AND RL2.AD_REFERENCE_ID = '800070'"
+ " AND DP.AD_Client_ID IN (";
strSql = strSql + ((adUserClient == null || adUserClient.equals("")) ? "" : adUserClient);
strSql = strSql + ") " + " AND DP.AD_Org_ID IN (";
strSql = strSql + ((adUserOrg == null || adUserOrg.equals("")) ? "" : adUserOrg);
strSql = strSql + ") " + " AND DP.IsActive='Y'" + " AND DP.IsValid='Y'";
strSql =
strSql
+ ((cBpartnerId == null || cBpartnerId.equals(""))
? ""
: " AND dp.C_BPARTNER_ID IN " + cBpartnerId);
strSql =
strSql
+ ((dateFrom == null || dateFrom.equals(""))
? ""
: " AND dp.Dateplanned >= to_date(?) ");
strSql =
strSql
+ ((dateTo == null || dateTo.equals("")) ? "" : " AND dp.Dateplanned < to_date(?) ");
strSql =
strSql
+ ((AmountFrom == null || AmountFrom.equals(""))
? ""
: " AND dp.amount >= TO_NUMBER(?) ");
strSql =
strSql
+ ((AmountTo == null || AmountTo.equals(""))
? ""
: " AND dp.amount <= TO_NUMBER(?) ");
strSql =
strSql
+ ((paymentRule == null || paymentRule.equals("")) ? "" : " AND dp.paymentrule = ? ");
strSql = strSql + ((isReceipt == null || isReceipt.equals("")) ? "" : " AND dp.isreceipt=? ");
strSql = strSql + ((status == null || status.equals("")) ? "" : " AND dp.status=? ");
strSql =
strSql
+ ((isSettle == null || isSettle.equals(""))
? ""
: " AND C_DEBT_PAYMENT_STATUS(DP.C_Settlement_Cancel_ID, DP.CANCEL_PROCESSED, DP.GENERATE_PROCESSED, DP.ISPAID, DP.ISVALID, DP.C_CASHLINE_ID, DP.C_BANKSTATEMENTLINE_ID) IN"
+ isSettle);
strSql =
strSql
+ ((cbankaccount == null || cbankaccount.equals(""))
? ""
: " AND DP.C_BANKACCOUNT_ID = ? ");
strSql =
strSql
+ " GROUP BY (BANK.NAME || '/' || BANK.CODEBRANCH || BANK.DIGITCONTROL || BA.CODEACCOUNT || '.' || BA.DIGITCONTROL), (CASE BA.showspanish WHEN 'Y' then BA.CODEACCOUNT ELSE CASE BA.SHOWGENERIC when 'Y' then GENERICACCOUNT ELSE CASE BA.SHOWIBAN WHEN 'Y' THEN BA.IBAN END END END),"
+ " CASE WHEN I.C_INVOICE_ID IS NULL THEN '0' ELSE I.C_INVOICE_ID END, (CASE WHEN DP.C_DEBT_PAYMENT_ID IS NULL THEN '0' ELSE DP.C_DEBT_PAYMENT_ID END), B.NAME, (CASE WHEN I.DOCUMENTNO IS NOT NULL THEN I.DOCUMENTNO||' ('||I.DATEINVOICED||')' END), DP.DATEPLANNED, DP.AMOUNT,"
+ " DP.C_CURRENCY_ID, I.AD_CLIENT_ID, I.AD_ORG_ID, I.DATEINVOICED,"
+ " DP.WRITEOFFAMT, C.ISO_CODE, (CASE WHEN S1.DOCUMENTNO IS NOT NULL THEN S1.DOCUMENTNO||' ('||S1.DATETRX||')' END), (CASE WHEN S2.DOCUMENTNO IS NOT NULL THEN S2.DOCUMENTNO||' ('||S2.DATETRX||')' END), RLT.NAME, RL.NAME, RLT2.NAME, RL2.NAME, DP.C_Settlement_Cancel_ID,"
+ " DP.CANCEL_PROCESSED, DP.GENERATE_PROCESSED, DP.ISPAID, DP.ISVALID, DP.C_CASHLINE_ID, DP.C_BANKSTATEMENTLINE_ID, DP.DESCRIPTION, (CASE DP.ISRECEIPT WHEN 'Y' THEN 'SalesInvoice' ELSE 'PurchaseInvoice' END),s2.SETTLEMENTTYPE, DP.STATUS, URS.NAME"
+ " ORDER BY ";
strSql = strSql + ((ord == null || ord.equals("")) ? "" : ord);
strSql = strSql + ",STATUS, DP.DATEPLANNED";
ResultSet result;
Vector<java.lang.Object> vector = new Vector<java.lang.Object>(0);
PreparedStatement st = null;
int iParameter = 0;
try {
st = connectionProvider.getPreparedStatement(strSql);
QueryTimeOutUtil.getInstance().setQueryTimeOut(st, SessionInfo.getQueryProfile());
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, cCurrencyConv);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, cCurrencyConv);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, cCurrencyConv);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, adLanguage);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, adLanguage);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, adLanguage);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, adLanguage);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, adLanguage);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, adLanguage);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, adLanguage);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, adLanguage);
if (adUserClient != null && !(adUserClient.equals(""))) {}
if (adUserOrg != null && !(adUserOrg.equals(""))) {}
if (cBpartnerId != null && !(cBpartnerId.equals(""))) {}
if (dateFrom != null && !(dateFrom.equals(""))) {
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, dateFrom);
}
if (dateTo != null && !(dateTo.equals(""))) {
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, dateTo);
}
if (AmountFrom != null && !(AmountFrom.equals(""))) {
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, AmountFrom);
}
if (AmountTo != null && !(AmountTo.equals(""))) {
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, AmountTo);
}
if (paymentRule != null && !(paymentRule.equals(""))) {
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, paymentRule);
}
if (isReceipt != null && !(isReceipt.equals(""))) {
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, isReceipt);
}
if (status != null && !(status.equals(""))) {
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, status);
}
if (isSettle != null && !(isSettle.equals(""))) {}
if (cbankaccount != null && !(cbankaccount.equals(""))) {
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, cbankaccount);
}
if (ord != null && !(ord.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++;
ReportDebtPaymentData objectReportDebtPaymentData = new ReportDebtPaymentData();
objectReportDebtPaymentData.bankacc = UtilSql.getValue(result, "BANKACC");
objectReportDebtPaymentData.accountstr = UtilSql.getValue(result, "ACCOUNTSTR");
objectReportDebtPaymentData.cInvoiceId = UtilSql.getValue(result, "C_INVOICE_ID");
objectReportDebtPaymentData.cDebtPaymentId = UtilSql.getValue(result, "C_DEBT_PAYMENT_ID");
objectReportDebtPaymentData.bpartner = UtilSql.getValue(result, "BPARTNER");
objectReportDebtPaymentData.invoice = UtilSql.getValue(result, "INVOICE");
objectReportDebtPaymentData.dateplanned =
UtilSql.getDateValue(result, "DATEPLANNED", "dd-MM-yyyy");
objectReportDebtPaymentData.amount = UtilSql.getValue(result, "AMOUNT");
objectReportDebtPaymentData.writeoffamt = UtilSql.getValue(result, "WRITEOFFAMT");
objectReportDebtPaymentData.currency = UtilSql.getValue(result, "CURRENCY");
objectReportDebtPaymentData.debtcancel = UtilSql.getValue(result, "DEBTCANCEL");
objectReportDebtPaymentData.debtgenerate = UtilSql.getValue(result, "DEBTGENERATE");
objectReportDebtPaymentData.paymentrule = UtilSql.getValue(result, "PAYMENTRULE");
objectReportDebtPaymentData.convamount = UtilSql.getValue(result, "CONVAMOUNT");
objectReportDebtPaymentData.convsym = UtilSql.getValue(result, "CONVSYM");
objectReportDebtPaymentData.convisosym = UtilSql.getValue(result, "CONVISOSYM");
objectReportDebtPaymentData.status = UtilSql.getValue(result, "STATUS");
objectReportDebtPaymentData.description = UtilSql.getValue(result, "DESCRIPTION");
objectReportDebtPaymentData.ispaid = UtilSql.getValue(result, "ISPAID");
objectReportDebtPaymentData.invoiceType = UtilSql.getValue(result, "INVOICE_TYPE");
objectReportDebtPaymentData.path = UtilSql.getValue(result, "PATH");
objectReportDebtPaymentData.dpstatus = UtilSql.getValue(result, "DPSTATUS");
objectReportDebtPaymentData.salesrepname = UtilSql.getValue(result, "SALESREPNAME");
objectReportDebtPaymentData.rownum = Long.toString(countRecord);
objectReportDebtPaymentData.InitRecordNumber = Integer.toString(firstRegister);
vector.addElement(objectReportDebtPaymentData);
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();
}
}
ReportDebtPaymentData objectReportDebtPaymentData[] = new ReportDebtPaymentData[vector.size()];
vector.copyInto(objectReportDebtPaymentData);
return (objectReportDebtPaymentData);
}