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 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 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 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 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 ClassInfoData[] select(
ConnectionProvider connectionProvider,
String className,
int firstRegister,
int numberRegisters)
throws ServletException {
String strSql = "";
strSql =
strSql
+ " SELECT mo.action AS TYPE, (CASE mo.action WHEN 'X' THEN mo.ad_form_id"
+ " WHEN 'P' THEN mo.ad_process_id"
+ " WHEN 'R' THEN mo.ad_process_id"
+ " WHEN 'S' THEN mo.ad_reference_id"
+ " WHEN 'C' THEN mo.ad_callout_id"
+ " ELSE null END) AS ID,"
+ " m.name AS NAME, /*Name is only maintained for backwards compatibility, now REPORT_TITLE is automatically filled*/"
+ " COALESCE((CASE mo.action WHEN 'X' THEN f.ad_module_id"
+ " WHEN 'P' THEN p.ad_module_id"
+ " WHEN 'R' THEN p.ad_module_id"
+ " WHEN 'S' THEN r.ad_module_id"
+ " WHEN 'C' THEN c.ad_module_id"
+ " ELSE null END), mo.ad_module_id) AS ad_module_id"
+ " FROM ad_model_object mo"
+ " LEFT JOIN ad_menu m ON mo.ad_process_id = m.ad_process_id"
+ " AND mo.action IN ('P', 'R')"
+ " AND m.action IN ('P', 'R')"
+ " LEFT JOIN ad_form f ON mo.ad_form_id = f.ad_form_id"
+ " LEFT JOIN ad_process p ON mo.ad_process_id = p.ad_process_id"
+ " LEFT JOIN ad_reference r ON mo.ad_reference_id = r.ad_reference_id"
+ " LEFT JOIN ad_callout c ON mo.ad_callout_id = c.ad_callout_id"
+ " WHERE mo.classname = ?"
+ " AND mo.isdefault = 'Y'"
+ " AND mo.isactive = '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);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, className);
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++;
ClassInfoData objectClassInfoData = new ClassInfoData();
objectClassInfoData.type = UtilSql.getValue(result, "type");
objectClassInfoData.id = UtilSql.getValue(result, "id");
objectClassInfoData.name = UtilSql.getValue(result, "name");
objectClassInfoData.adModuleId = UtilSql.getValue(result, "ad_module_id");
objectClassInfoData.InitRecordNumber = Integer.toString(firstRegister);
vector.addElement(objectClassInfoData);
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();
}
}
ClassInfoData objectClassInfoData[] = new ClassInfoData[vector.size()];
vector.copyInto(objectClassInfoData);
return (objectClassInfoData);
}
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 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 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);
}
public static RptCBpartnerData[] selectAccount(
ConnectionProvider connectionProvider,
String cBpartnerId,
int firstRegister,
int numberRegisters)
throws ServletException {
String strSql = "";
strSql =
strSql
+ " SELECT A.ACCOUNTNO, A.BANK_NAME AS NAME"
+ " FROM C_BP_BANKACCOUNT A";
strSql =
strSql
+ ((cBpartnerId == null || cBpartnerId.equals(""))
? ""
: " WHERE C_BPARTNER_ID IN " + cBpartnerId);
ResultSet result;
Vector<java.lang.Object> vector = new Vector<java.lang.Object>(0);
PreparedStatement st = null;
try {
st = connectionProvider.getPreparedStatement(strSql);
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.accountno = UtilSql.getValue(result, "accountno");
objectRptCBpartnerData.name = UtilSql.getValue(result, "name");
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 ReportProjectProgressData[] select(
ConnectionProvider connectionProvider,
String adLanguage,
String adUserClient,
String adUserOrg,
String dateStartFrom,
String dateStartTo,
String dateContractFrom,
String dateContractTo,
String dateEndingFrom,
String dateEndingTo,
String cProjectId,
String cProjectStatus,
String cBPartnerId,
String projectResponsible,
String olderFirst,
int firstRegister,
int numberRegisters)
throws ServletException {
String strSql = "";
strSql =
strSql
+ " SELECT"
+ " PR.C_PROJECT_ID AS PROJECTID, PR.NAME AS PROJECTNAME, PR.DESCRIPTION AS PROJECTDESCRIPTION, PR.NOTE AS PROJECTCOMMENTS,"
+ " REFLISTV.NAME AS PROJECTSTATUS, BPCUSTOMER.NAME AS CUSTOMERNAME, BPRESPONSIBLE.NAME AS PERSONINCHARGE,"
+ " PR.STARTDATE AS STARTINGDATE, PR.DATECONTRACT AS CONTRACTDATE, PR.DATEFINISH AS ENDINGDATE,"
+ " '' AS DAYSELAPSED, '' AS PROJECTCONTRACTDURATION, '' AS DAYSDELAYED,"
+ " '' AS TIMEBURNED, ''AS COMPLETIONPERC, '' AS CUMDAYSDELAYED,"
+ " PRP.C_PROJECTPHASE_ID AS PHASEID, PRP.NAME AS PHASENAME, PRP.STARTDATE AS PHASESTARTINGDATE, PRP.DATECONTRACT AS PHASECONTRACTDATE, '' AS PHASECONTRACTDURATION, "
+ " AD_MESSAGE_GET2(PRP.ISCOMPLETE,?) AS PHASECOMPLETE, PRP.ISCOMPLETE AS PHASECOMP, PRP.ENDDATE AS PHASEENDINGDATE, '' AS PHASEDAYSDELAYED,"
+ " PRT.C_PROJECTTASK_ID AS TASKID, PRT.NAME AS TASKNAME, PRT.STARTDATE AS TASKSTARTINGDATE, PRT.DATECONTRACT AS TASKCONTRACTDATE, '' AS TASKCONTRACTDURATION,"
+ " AD_MESSAGE_GET2(PRT.ISCOMPLETE,?) AS TASKCOMPLETE, PRT.ISCOMPLETE AS TASKCOMP, PRT.ENDDATE AS TASKENDINGDATE, '' AS TASKDAYSDELAYED"
+ " FROM AD_REF_LIST_V REFLISTV, C_BPARTNER BPCUSTOMER, C_PROJECT PR"
+ " LEFT JOIN C_BPARTNER BPRESPONSIBLE ON PR.RESPONSIBLE_ID = BPRESPONSIBLE.C_BPARTNER_ID"
+ " LEFT JOIN C_PROJECTPHASE PRP ON PR.C_PROJECT_ID = PRP.C_PROJECT_ID"
+ " LEFT JOIN C_PROJECTTASK PRT ON PRP.C_PROJECTPHASE_ID = PRT.C_PROJECTPHASE_ID"
+ " WHERE PR.C_BPARTNER_ID = BPCUSTOMER.C_BPARTNER_ID"
+ " AND REFLISTV.AD_REFERENCE_ID = '800002'"
+ " AND REFLISTV.AD_LANGUAGE = ?"
+ " AND REFLISTV.VALUE = PR.PROJECTSTATUS"
+ " AND PR.AD_CLIENT_ID IN (";
strSql = strSql + ((adUserClient == null || adUserClient.equals("")) ? "" : adUserClient);
strSql = strSql + ")" + " AND PR.AD_ORG_ID IN (";
strSql = strSql + ((adUserOrg == null || adUserOrg.equals("")) ? "" : adUserOrg);
strSql = strSql + ")" + " AND 1=1";
strSql =
strSql
+ ((dateStartFrom == null || dateStartFrom.equals(""))
? ""
: " AND PR.STARTDATE >= TO_DATE(?) ");
strSql =
strSql
+ ((dateStartTo == null || dateStartTo.equals(""))
? ""
: " AND PR.STARTDATE < TO_DATE(?) ");
strSql =
strSql
+ ((dateContractFrom == null || dateContractFrom.equals(""))
? ""
: " AND PR.DATECONTRACT >= TO_DATE(?) ");
strSql =
strSql
+ ((dateContractTo == null || dateContractTo.equals(""))
? ""
: " AND PR.DATECONTRACT < TO_DATE(?) ");
strSql =
strSql
+ ((dateEndingFrom == null || dateEndingFrom.equals(""))
? ""
: " AND PR.DATEFINISH >= TO_DATE(?) ");
strSql =
strSql
+ ((dateEndingTo == null || dateEndingTo.equals(""))
? ""
: " AND PR.DATEFINISH < TO_DATE(?) ");
strSql =
strSql
+ ((cProjectId == null || cProjectId.equals("")) ? "" : " AND PR.C_PROJECT_ID = ? ");
strSql =
strSql
+ ((cProjectStatus == null || cProjectStatus.equals(""))
? ""
: " AND PR.PROJECTSTATUS IN" + cProjectStatus);
strSql =
strSql
+ ((cBPartnerId == null || cBPartnerId.equals(""))
? ""
: " AND PR.C_BPARTNER_ID = ? ");
strSql =
strSql
+ ((projectResponsible == null || projectResponsible.equals(""))
? ""
: " AND PR.RESPONSIBLE_ID = ? ");
strSql = strSql + " ORDER BY PR.NAME";
strSql = strSql + ((olderFirst == null || olderFirst.equals("")) ? "" : olderFirst);
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 (adUserClient != null && !(adUserClient.equals(""))) {}
if (adUserOrg != null && !(adUserOrg.equals(""))) {}
if (dateStartFrom != null && !(dateStartFrom.equals(""))) {
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, dateStartFrom);
}
if (dateStartTo != null && !(dateStartTo.equals(""))) {
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, dateStartTo);
}
if (dateContractFrom != null && !(dateContractFrom.equals(""))) {
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, dateContractFrom);
}
if (dateContractTo != null && !(dateContractTo.equals(""))) {
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, dateContractTo);
}
if (dateEndingFrom != null && !(dateEndingFrom.equals(""))) {
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, dateEndingFrom);
}
if (dateEndingTo != null && !(dateEndingTo.equals(""))) {
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, dateEndingTo);
}
if (cProjectId != null && !(cProjectId.equals(""))) {
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, cProjectId);
}
if (cProjectStatus != null && !(cProjectStatus.equals(""))) {}
if (cBPartnerId != null && !(cBPartnerId.equals(""))) {
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, cBPartnerId);
}
if (projectResponsible != null && !(projectResponsible.equals(""))) {
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, projectResponsible);
}
if (olderFirst != null && !(olderFirst.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++;
ReportProjectProgressData objectReportProjectProgressData = new ReportProjectProgressData();
objectReportProjectProgressData.projectid = UtilSql.getValue(result, "projectid");
objectReportProjectProgressData.projectname = UtilSql.getValue(result, "projectname");
objectReportProjectProgressData.projectdescription =
UtilSql.getValue(result, "projectdescription");
objectReportProjectProgressData.projectcomments =
UtilSql.getValue(result, "projectcomments");
objectReportProjectProgressData.projectstatus = UtilSql.getValue(result, "projectstatus");
objectReportProjectProgressData.customername = UtilSql.getValue(result, "customername");
objectReportProjectProgressData.personincharge = UtilSql.getValue(result, "personincharge");
objectReportProjectProgressData.startingdate =
UtilSql.getDateValue(result, "startingdate", "dd-MM-yyyy");
objectReportProjectProgressData.contractdate =
UtilSql.getDateValue(result, "contractdate", "dd-MM-yyyy");
objectReportProjectProgressData.endingdate =
UtilSql.getDateValue(result, "endingdate", "dd-MM-yyyy");
objectReportProjectProgressData.dayselapsed = UtilSql.getValue(result, "dayselapsed");
objectReportProjectProgressData.projectcontractduration =
UtilSql.getValue(result, "projectcontractduration");
objectReportProjectProgressData.daysdelayed = UtilSql.getValue(result, "daysdelayed");
objectReportProjectProgressData.timeburned = UtilSql.getValue(result, "timeburned");
objectReportProjectProgressData.completionperc = UtilSql.getValue(result, "completionperc");
objectReportProjectProgressData.cumdaysdelayed = UtilSql.getValue(result, "cumdaysdelayed");
objectReportProjectProgressData.phaseid = UtilSql.getValue(result, "phaseid");
objectReportProjectProgressData.phasename = UtilSql.getValue(result, "phasename");
objectReportProjectProgressData.phasestartingdate =
UtilSql.getDateValue(result, "phasestartingdate", "dd-MM-yyyy");
objectReportProjectProgressData.phasecontractdate =
UtilSql.getDateValue(result, "phasecontractdate", "dd-MM-yyyy");
objectReportProjectProgressData.phasecontractduration =
UtilSql.getValue(result, "phasecontractduration");
objectReportProjectProgressData.phasecomplete = UtilSql.getValue(result, "phasecomplete");
objectReportProjectProgressData.phasecomp = UtilSql.getValue(result, "phasecomp");
objectReportProjectProgressData.phaseendingdate =
UtilSql.getDateValue(result, "phaseendingdate", "dd-MM-yyyy");
objectReportProjectProgressData.phasedaysdelayed =
UtilSql.getValue(result, "phasedaysdelayed");
objectReportProjectProgressData.taskid = UtilSql.getValue(result, "taskid");
objectReportProjectProgressData.taskname = UtilSql.getValue(result, "taskname");
objectReportProjectProgressData.taskstartingdate =
UtilSql.getDateValue(result, "taskstartingdate", "dd-MM-yyyy");
objectReportProjectProgressData.taskcontractdate =
UtilSql.getDateValue(result, "taskcontractdate", "dd-MM-yyyy");
objectReportProjectProgressData.taskcontractduration =
UtilSql.getValue(result, "taskcontractduration");
objectReportProjectProgressData.taskcomplete = UtilSql.getValue(result, "taskcomplete");
objectReportProjectProgressData.taskcomp = UtilSql.getValue(result, "taskcomp");
objectReportProjectProgressData.taskendingdate =
UtilSql.getDateValue(result, "taskendingdate", "dd-MM-yyyy");
objectReportProjectProgressData.taskdaysdelayed =
UtilSql.getValue(result, "taskdaysdelayed");
objectReportProjectProgressData.InitRecordNumber = Integer.toString(firstRegister);
vector.addElement(objectReportProjectProgressData);
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();
}
}
ReportProjectProgressData objectReportProjectProgressData[] =
new ReportProjectProgressData[vector.size()];
vector.copyInto(objectReportProjectProgressData);
return (objectReportProjectProgressData);
}
public static UpdateReferenceDataData[] select(
ConnectionProvider connectionProvider, int firstRegister, int numberRegisters)
throws ServletException {
String strSql = "";
strSql =
strSql
+ " SELECT '' as id, '' as NAME, '' AS HASCHARTOFACCOUNTS, '' AS ISTRANSLATIONMODULE,"
+ " '' AS AD_MODULE_ID, '' AS PATH, '' AS JAVAPACKAGE, '' AS DATASETNAME, '' AS VERSION, '' AS CHECKSUM"
+ " 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++;
UpdateReferenceDataData objectUpdateReferenceDataData = new UpdateReferenceDataData();
objectUpdateReferenceDataData.id = UtilSql.getValue(result, "id");
objectUpdateReferenceDataData.name = UtilSql.getValue(result, "name");
objectUpdateReferenceDataData.haschartofaccounts =
UtilSql.getValue(result, "haschartofaccounts");
objectUpdateReferenceDataData.istranslationmodule =
UtilSql.getValue(result, "istranslationmodule");
objectUpdateReferenceDataData.adModuleId = UtilSql.getValue(result, "ad_module_id");
objectUpdateReferenceDataData.path = UtilSql.getValue(result, "path");
objectUpdateReferenceDataData.javapackage = UtilSql.getValue(result, "javapackage");
objectUpdateReferenceDataData.datasetname = UtilSql.getValue(result, "datasetname");
objectUpdateReferenceDataData.version = UtilSql.getValue(result, "version");
objectUpdateReferenceDataData.checksum = UtilSql.getValue(result, "checksum");
objectUpdateReferenceDataData.InitRecordNumber = Integer.toString(firstRegister);
vector.addElement(objectUpdateReferenceDataData);
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();
}
}
UpdateReferenceDataData objectUpdateReferenceDataData[] =
new UpdateReferenceDataData[vector.size()];
vector.copyInto(objectUpdateReferenceDataData);
return (objectUpdateReferenceDataData);
}
public static UpdateReferenceDataData[] selectModules(
ConnectionProvider connectionProvider,
String modules,
String organization,
int firstRegister,
int numberRegisters)
throws ServletException {
String strSql = "";
strSql =
strSql
+ " SELECT AD_MODULE.NAME, AD_MODULE.HASCHARTOFACCOUNTS, AD_MODULE.ISTRANSLATIONMODULE, "
+ " AD_MODULE.AD_MODULE_ID , AD_MODULE.JAVAPACKAGE, AD_DATASET.NAME AS DATASETNAME, AD_MODULE.VERSION, AD_DATASET.CHECKSUM"
+ " FROM AD_MODULE, AD_DATASET"
+ " WHERE AD_MODULE.AD_MODULE_ID = AD_DATASET.AD_MODULE_ID "
+ " AND AD_MODULE.AD_MODULE_ID IN ";
strSql = strSql + ((modules == null || modules.equals("")) ? "" : modules);
strSql =
strSql
+ " AND AD_DATASET.ISACTIVE='Y'"
+ " AND ((? <> '0' AND AD_DATASET.ACCESSLEVEL IN ('3','1')) OR (? = '0' AND AD_DATASET.ACCESSLEVEL IN ('3','6')))"
+ " ORDER BY AD_MODULE.AD_MODULE_ID, AD_DATASET.SEQNO, AD_DATASET.AD_DATASET_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);
if (modules != null && !(modules.equals(""))) {}
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, organization);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, organization);
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++;
UpdateReferenceDataData objectUpdateReferenceDataData = new UpdateReferenceDataData();
objectUpdateReferenceDataData.name = UtilSql.getValue(result, "name");
objectUpdateReferenceDataData.haschartofaccounts =
UtilSql.getValue(result, "haschartofaccounts");
objectUpdateReferenceDataData.istranslationmodule =
UtilSql.getValue(result, "istranslationmodule");
objectUpdateReferenceDataData.adModuleId = UtilSql.getValue(result, "ad_module_id");
objectUpdateReferenceDataData.javapackage = UtilSql.getValue(result, "javapackage");
objectUpdateReferenceDataData.datasetname = UtilSql.getValue(result, "datasetname");
objectUpdateReferenceDataData.version = UtilSql.getValue(result, "version");
objectUpdateReferenceDataData.checksum = UtilSql.getValue(result, "checksum");
objectUpdateReferenceDataData.InitRecordNumber = Integer.toString(firstRegister);
vector.addElement(objectUpdateReferenceDataData);
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();
}
}
UpdateReferenceDataData objectUpdateReferenceDataData[] =
new UpdateReferenceDataData[vector.size()];
vector.copyInto(objectUpdateReferenceDataData);
return (objectUpdateReferenceDataData);
}
public static WarehouseComboData[] select(
ConnectionProvider connectionProvider,
String rol,
String client,
int firstRegister,
int numberRegisters)
throws ServletException {
String strSql = "";
strSql =
strSql
+ " SELECT M_Warehouse.M_Warehouse_ID, M_Warehouse.Name AS Name "
+ " FROM M_Warehouse, AD_ORG , AD_ROLE_ORGACCESS"
+ " WHERE M_Warehouse.AD_ORG_ID = AD_ORG.AD_ORG_ID"
+ " AND AD_ORG.AD_ORG_ID = AD_ROLE_ORGACCESS.AD_ORG_ID"
+ " AND M_Warehouse.IsActive='Y' "
+ " AND AD_ROLE_ORGACCESS.AD_ROLE_ID = ? "
+ " AND M_Warehouse.AD_Client_ID = ? "
+ " ORDER BY M_Warehouse.Value";
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, rol);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, client);
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++;
WarehouseComboData objectWarehouseComboData = new WarehouseComboData();
objectWarehouseComboData.mWarehouseId = UtilSql.getValue(result, "M_WAREHOUSE_ID");
objectWarehouseComboData.name = UtilSql.getValue(result, "NAME");
objectWarehouseComboData.InitRecordNumber = Integer.toString(firstRegister);
vector.addElement(objectWarehouseComboData);
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();
}
}
WarehouseComboData objectWarehouseComboData[] = new WarehouseComboData[vector.size()];
vector.copyInto(objectWarehouseComboData);
return (objectWarehouseComboData);
}
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);
}
public static RptCBpartnerData[] selectDiscount(
ConnectionProvider connectionProvider,
String cBpartnerId,
int firstRegister,
int numberRegisters)
throws ServletException {
String strSql = "";
strSql =
strSql
+ " SELECT (CASE BD.CASCADE WHEN 'Y' THEN 'Si' ELSE 'No' END) AS CASCADE, BD.LINE AS SEQNO, D.NAME, D.DISCOUNT"
+ " FROM C_BPARTNER_DISCOUNT BD, C_DISCOUNT D"
+ " WHERE BD.C_DISCOUNT_ID = D.C_DISCOUNT_ID";
strSql =
strSql
+ ((cBpartnerId == null || cBpartnerId.equals(""))
? ""
: " AND BD.C_BPARTNER_ID IN " + cBpartnerId);
strSql = strSql + " ORDER BY SEQNO";
ResultSet result;
Vector<java.lang.Object> vector = new Vector<java.lang.Object>(0);
PreparedStatement st = null;
try {
st = connectionProvider.getPreparedStatement(strSql);
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.cascade = UtilSql.getValue(result, "cascade");
objectRptCBpartnerData.seqno = UtilSql.getValue(result, "seqno");
objectRptCBpartnerData.name = UtilSql.getValue(result, "name");
objectRptCBpartnerData.discount = UtilSql.getValue(result, "discount");
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);
}
/** 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[] 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 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 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 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 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);
}
/** 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);
}
/** Select Organization */
public static OrganizationData[] select(
ConnectionProvider connectionProvider,
String paramLanguage,
String adUserClient,
String adOrgClient,
int firstRegister,
int numberRegisters)
throws ServletException {
String strSql = "";
strSql =
strSql
+ " SELECT "
+ " AD_OrgInfo.AD_Client_ID, "
+ " (CASE WHEN AD_OrgInfo.AD_Client_ID IS NULL THEN '' ELSE (TO_CHAR(client.Name) ) END) AS AD_Client_IDR, "
+ " AD_OrgInfo.AD_Org_ID, "
+ " (CASE WHEN AD_OrgInfo.AD_Org_ID IS NULL THEN '' ELSE (TO_CHAR(org.Name) ) END) AS AD_Org_IDR, "
+ " COALESCE(AD_OrgInfo.IsActive, 'N') AS IsActive, "
+ " AD_OrgInfo.C_Location_ID, "
+ " (CASE WHEN AD_OrgInfo.C_Location_ID IS NULL THEN '' ELSE (TO_CHAR(loc.Address1) || ' - ' || TO_CHAR(loc.Address2) || ' - ' || TO_CHAR(loc.Postal) || ' - ' || TO_CHAR(loc.City) || ' - ' || TO_CHAR(reg.Name) || ' - ' || TO_CHAR((CASE WHEN countryTrl.Name IS NULL THEN TO_CHAR(country.Name) ELSE TO_CHAR(countryTrl.Name) END)) ) END) AS C_Location_IDR, "
+ " AD_OrgInfo.DUNS, "
+ " AD_OrgInfo.TaxID, "
+ " AD_OrgInfo.C_BPartner_ID, "
+ " (CASE WHEN AD_OrgInfo.C_BPartner_ID IS NULL THEN '' ELSE (TO_CHAR(bpart.Name) ) END) AS C_BPartner_IDR, "
+ " AD_OrgInfo.Logo, "
+ " COALESCE(AD_OrgInfo.Reference_Order, 'N') AS Reference_Order, "
+ " '' AS TR_BGCOLOR , "
+ " AD_OrgInfo.Created, "
+ " AD_OrgInfo.CreatedBy, "
+ " AD_OrgInfo.Updated, "
+ " AD_OrgInfo.UpdatedBy, ? AS LANGUAGE "
+ " FROM "
+ " AD_OrgInfo "
+ " left join (select AD_Client_ID, Name from AD_Client) client on (AD_OrgInfo.AD_Client_ID = client.AD_Client_ID) "
+ " left join (select AD_Org_ID, Name from AD_Org) org on (AD_OrgInfo.AD_Org_ID = org.AD_Org_ID) "
+ " left join (select C_Location_ID, Address1, Address2, Postal, City, C_Region_ID, C_Country_ID from C_Location) loc on (AD_OrgInfo.C_Location_ID = loc.C_Location_ID) "
+ " left join (select C_Region_ID, Name from C_Region) reg on (loc.C_Region_ID = reg.C_Region_ID) "
+ " left join (select C_Country_ID, Name from C_Country) country on (loc.C_Country_ID = country.C_Country_ID) "
+ " left join (select C_Country_ID,AD_Language, Name from C_Country_TRL) countryTrl on (country.C_Country_ID = countryTrl.C_Country_ID and countryTrl.AD_Language = ?) "
+ " left join (select C_BPartner_ID, Name from C_BPartner) bpart on (AD_OrgInfo.C_BPartner_ID = bpart.C_BPartner_ID)"
+ " WHERE "
+ " AD_OrgInfo.AD_Client_ID IN (";
strSql = strSql + ((adUserClient == null || adUserClient.equals("")) ? "" : adUserClient);
strSql = strSql + ") " + " AND AD_OrgInfo.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, paramLanguage);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, paramLanguage);
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++;
OrganizationData objectOrganizationData = new OrganizationData();
objectOrganizationData.adClientId = UtilSql.getValue(result, "AD_CLIENT_ID");
objectOrganizationData.adClientIdr = UtilSql.getValue(result, "AD_CLIENT_IDR");
objectOrganizationData.adOrgId = UtilSql.getValue(result, "AD_ORG_ID");
objectOrganizationData.adOrgIdr = UtilSql.getValue(result, "AD_ORG_IDR");
objectOrganizationData.isactive = UtilSql.getValue(result, "ISACTIVE");
objectOrganizationData.cLocationId = UtilSql.getValue(result, "C_LOCATION_ID");
objectOrganizationData.cLocationIdr = UtilSql.getValue(result, "C_LOCATION_IDR");
objectOrganizationData.duns = UtilSql.getValue(result, "DUNS");
objectOrganizationData.taxid = UtilSql.getValue(result, "TAXID");
objectOrganizationData.cBpartnerId = UtilSql.getValue(result, "C_BPARTNER_ID");
objectOrganizationData.cBpartnerIdr = UtilSql.getValue(result, "C_BPARTNER_IDR");
objectOrganizationData.logo = UtilSql.getValue(result, "LOGO");
objectOrganizationData.referenceOrder = UtilSql.getValue(result, "REFERENCE_ORDER");
objectOrganizationData.trBgcolor = UtilSql.getValue(result, "TR_BGCOLOR");
objectOrganizationData.created = UtilSql.getDateValue(result, "CREATED", "dd-MM-yyyy");
objectOrganizationData.createdby = UtilSql.getValue(result, "CREATEDBY");
objectOrganizationData.updated = UtilSql.getDateValue(result, "UPDATED", "dd-MM-yyyy");
objectOrganizationData.updatedby = UtilSql.getValue(result, "UPDATEDBY");
objectOrganizationData.language = UtilSql.getValue(result, "LANGUAGE");
objectOrganizationData.InitRecordNumber = Integer.toString(firstRegister);
vector.addElement(objectOrganizationData);
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();
}
}
OrganizationData objectOrganizationData[] = new OrganizationData[vector.size()];
vector.copyInto(objectOrganizationData);
return (objectOrganizationData);
}
