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);
}
