public static ReportCashflowForecastData[] select(
      ConnectionProvider connectionProvider,
      String language,
      String dateMax,
      String bankaccount,
      String adUserClient,
      String adUserOrg,
      int firstRegister,
      int numberRegisters)
      throws ServletException {
    String strSql = "";
    strSql =
        strSql
            + "      SELECT AD_COLUMN_IDENTIFIER(TO_CHAR('C_BankAccount'), TO_CHAR(BANKACCOUNTID), TO_CHAR(?)) as BANKACCOUNT, "
            + "             INITIALBALANCE,"
            + "             TRUNC(NOW()) AS CURRENTDATE, "
            + "             INCOME, "
            + "             PAYMENT, "
            + "             INCOME-PAYMENT AS INCPAY, "
            + "             INITIALBALANCE+INCOME-PAYMENT AS "
            + "             FINALSUMMARY, C_BANKACCOUNT_ID,             "
            + "             '' AS C_DEBT_PAYMENT_ID, "
            + "             '' AS DATEPLANNED, "
            + "             '' AS BPARTNER,"
            + "             '' AS DESCRIPTION, "
            + "             '' AS INVOICENO, "
            + "             '' AS DATEINVOICED,"
            + "             '' AS AMOUNT,"
            + "             '' AS ISRECEIPT,"
            + "             '' AS URL,"
            + "             '' AS ISRECEIPTMESSAGE FROM (     "
            + "            SELECT BA.C_BANKACCOUNT_ID AS BANKACCOUNTID,"
            + "             SUM(c_currency_convert(STMTAMT, BL.C_CURRENCY_ID, BA.C_CURRENCY_ID, BL.VALUTADATE, NULL, ba.ad_client_id, ba.ad_org_id)) AS INITIALBALANCE,"
            + "             COALESCE((SELECT SUM(c_currency_convert(DP.AMOUNT, dp.C_CURRENCY_ID, ba.c_Currency_ID, dp.dateplanned,null, ba.ad_client_id, ba.ad_org_id))"
            + "                FROM C_DEBT_PAYMENT_V DP"
            + "               WHERE C_SETTLEMENT_CANCEL_ID IS NULL"
            + "                 AND DP.C_BANKACCOUNT_ID IS NOT NULL         "
            + "                 AND C_DEBT_PAYMENT_STATUS(C_SETTLEMENT_CANCEL_ID, CANCEL_PROCESSED, GENERATE_PROCESSED, DP.ISPAID, ISVALID, C_CASHLINE_ID, C_BANKSTATEMENTLINE_ID) = 'P'"
            + "                 AND ISRECEIPT = 'Y'"
            + "                 AND DP.C_BANKACCOUNT_ID = BA.C_BANKACCOUNT_ID"
            + "                 AND 1=1";
    strSql =
        strSql
            + ((dateMax == null || dateMax.equals(""))
                ? ""
                : "  AND DP.DATEPLANNED <= TO_DATE(?)  ");
    strSql =
        strSql
            + "                 AND NOT EXISTS (SELECT 1 "
            + "                           FROM C_REMITTANCELINE  RL,"
            + "                                C_REMITTANCE      R,"
            + "                                C_REMITTANCE_TYPE RT"
            + "                          WHERE R.C_REMITTANCE_ID = RL.C_REMITTANCE_ID"
            + "                            AND R.C_REMITTANCE_TYPE_ID = RT.C_REMITTANCE_TYPE_ID"
            + "                            AND CONSOLIDATE = 'Y' "
            + "                            AND (C_DEBT_PAYMENT_ID  = DP.C_DEBT_PAYMENT_ID"
            + "                             OR C_DEBT_PAYMENT_CANCELLED = DP.C_DEBT_PAYMENT_ID)"
            + "                            AND NOT EXISTS (SELECT 1   /*IF CANCEL DP IS IN BANK (IT IS RETURNED) IT HAS TO APPEAR*/"
            + "                                              FROM C_BANKSTATEMENTLINE BSL,"
            + "                                                   C_BANKSTATEMENT B"
            + "                                            WHERE BSL.C_DEBT_PAYMENT_ID = RL.C_DEBT_PAYMENT_CANCELLED"
            + "                                              AND BSL.C_BANKSTATEMENT_ID = B.C_BANKSTATEMENT_ID"
            + "                                              AND B.PROCESSED='Y'))"
            + "              ),0) AS INCOME,"
            + "            -COALESCE((SELECT SUM(c_currency_convert(DP.AMOUNT, dp.C_CURRENCY_ID, ba.c_Currency_ID, dp.dateplanned,null, ba.ad_client_id, ba.ad_org_id))"
            + "                FROM C_DEBT_PAYMENT_V DP"
            + "               WHERE C_SETTLEMENT_CANCEL_ID IS NULL"
            + "                 AND DP.C_BANKACCOUNT_ID IS NOT NULL         "
            + "                 AND C_DEBT_PAYMENT_STATUS(C_SETTLEMENT_CANCEL_ID, CANCEL_PROCESSED, GENERATE_PROCESSED, DP.ISPAID, ISVALID, C_CASHLINE_ID, C_BANKSTATEMENTLINE_ID) = 'P'"
            + "                 AND ISRECEIPT='N'"
            + "                 AND DP.C_BANKACCOUNT_ID = BA.C_BANKACCOUNT_ID"
            + "                 AND 2=2";
    strSql =
        strSql
            + ((dateMax == null || dateMax.equals(""))
                ? ""
                : "  AND DP.DATEPLANNED <= TO_DATE(?)  ");
    strSql =
        strSql
            + "                 AND NOT EXISTS (SELECT 1 "
            + "                           FROM C_REMITTANCELINE  RL,"
            + "                                C_REMITTANCE      R,"
            + "                                C_REMITTANCE_TYPE RT"
            + "                          WHERE R.C_REMITTANCE_ID = RL.C_REMITTANCE_ID"
            + "                            AND R.C_REMITTANCE_TYPE_ID = RT.C_REMITTANCE_TYPE_ID"
            + "                            AND CONSOLIDATE = 'Y' "
            + "                            AND (C_DEBT_PAYMENT_ID  = DP.C_DEBT_PAYMENT_ID"
            + "                             OR C_DEBT_PAYMENT_CANCELLED = DP.C_DEBT_PAYMENT_ID)"
            + "                            AND NOT EXISTS (SELECT 1   /*IF CANCEL DP IS IN BANK (IT IS RETURNED) IT HAS TO APPEAR*/"
            + "                                              FROM C_BANKSTATEMENTLINE BSL,"
            + "                                                   C_BANKSTATEMENT B"
            + "                                            WHERE BSL.C_DEBT_PAYMENT_ID = RL.C_DEBT_PAYMENT_CANCELLED"
            + "                                              AND BSL.C_BANKSTATEMENT_ID = B.C_BANKSTATEMENT_ID"
            + "                                              AND B.PROCESSED='Y'))"
            + "               ),0) AS PAYMENT,"
            + "             BA.C_BANKACCOUNT_ID"
            + "      FROM C_BANKSTATEMENTLINE BL,"
            + "           C_BANKSTATEMENT     BS,"
            + "           C_BANKACCOUNT       BA"
            + "      WHERE BS.C_BANKSTATEMENT_ID = BL.C_BANKSTATEMENT_ID"
            + "        AND BA.C_BANKACCOUNT_ID = BS.C_BANKACCOUNT_ID"
            + "        AND BS.PROCESSED='Y'  "
            + "        AND BS.STATEMENTDATE <= NOW()"
            + "        AND 3=3";
    strSql =
        strSql
            + ((bankaccount == null || bankaccount.equals(""))
                ? ""
                : "  AND BA.C_BANKACCOUNT_ID = ?  ");
    strSql = strSql + "        AND BA.AD_CLIENT_ID IN (";
    strSql = strSql + ((adUserClient == null || adUserClient.equals("")) ? "" : adUserClient);
    strSql = strSql + ")" + "        AND BA.AD_ORG_ID IN (";
    strSql = strSql + ((adUserOrg == null || adUserOrg.equals("")) ? "" : adUserOrg);
    strSql =
        strSql
            + ") "
            + "       GROUP BY BA.C_BANKACCOUNT_ID, ba.c_Currency_ID, ba.ad_client_id, ba.ad_org_id "
            + "       ) AAA  "
            + "       ORDER BY 1";

    ResultSet result;
    Vector<java.lang.Object> vector = new Vector<java.lang.Object>(0);
    PreparedStatement st = null;

    int iParameter = 0;
    try {
      st = connectionProvider.getPreparedStatement(strSql);
      iParameter++;
      UtilSql.setValue(st, iParameter, 12, null, language);
      if (dateMax != null && !(dateMax.equals(""))) {
        iParameter++;
        UtilSql.setValue(st, iParameter, 12, null, dateMax);
      }
      if (dateMax != null && !(dateMax.equals(""))) {
        iParameter++;
        UtilSql.setValue(st, iParameter, 12, null, dateMax);
      }
      if (bankaccount != null && !(bankaccount.equals(""))) {
        iParameter++;
        UtilSql.setValue(st, iParameter, 12, null, bankaccount);
      }
      if (adUserClient != null && !(adUserClient.equals(""))) {}
      if (adUserOrg != null && !(adUserOrg.equals(""))) {}

      result = st.executeQuery();
      long countRecord = 0;
      long countRecordSkip = 1;
      boolean continueResult = true;
      while (countRecordSkip < firstRegister && continueResult) {
        continueResult = result.next();
        countRecordSkip++;
      }
      while (continueResult && result.next()) {
        countRecord++;
        ReportCashflowForecastData objectReportCashflowForecastData =
            new ReportCashflowForecastData();
        objectReportCashflowForecastData.bankaccount = UtilSql.getValue(result, "bankaccount");
        objectReportCashflowForecastData.initialbalance =
            UtilSql.getValue(result, "initialbalance");
        objectReportCashflowForecastData.currentdate =
            UtilSql.getDateValue(result, "currentdate", "dd-MM-yyyy");
        objectReportCashflowForecastData.income = UtilSql.getValue(result, "income");
        objectReportCashflowForecastData.payment = UtilSql.getValue(result, "payment");
        objectReportCashflowForecastData.incpay = UtilSql.getValue(result, "incpay");
        objectReportCashflowForecastData.finalsummary = UtilSql.getValue(result, "finalsummary");
        objectReportCashflowForecastData.cBankaccountId =
            UtilSql.getValue(result, "c_bankaccount_id");
        objectReportCashflowForecastData.cDebtPaymentId =
            UtilSql.getValue(result, "c_debt_payment_id");
        objectReportCashflowForecastData.dateplanned = UtilSql.getValue(result, "dateplanned");
        objectReportCashflowForecastData.bpartner = UtilSql.getValue(result, "bpartner");
        objectReportCashflowForecastData.description = UtilSql.getValue(result, "description");
        objectReportCashflowForecastData.invoiceno = UtilSql.getValue(result, "invoiceno");
        objectReportCashflowForecastData.dateinvoiced = UtilSql.getValue(result, "dateinvoiced");
        objectReportCashflowForecastData.amount = UtilSql.getValue(result, "amount");
        objectReportCashflowForecastData.isreceipt = UtilSql.getValue(result, "isreceipt");
        objectReportCashflowForecastData.url = UtilSql.getValue(result, "url");
        objectReportCashflowForecastData.isreceiptmessage =
            UtilSql.getValue(result, "isreceiptmessage");
        objectReportCashflowForecastData.rownum = Long.toString(countRecord);
        objectReportCashflowForecastData.InitRecordNumber = Integer.toString(firstRegister);
        vector.addElement(objectReportCashflowForecastData);
        if (countRecord >= numberRegisters && numberRegisters != 0) {
          continueResult = false;
        }
      }
      result.close();
    } catch (SQLException e) {
      log4j.error("SQL error in query: " + strSql + "Exception:" + e);
      throw new ServletException(
          "@CODE=" + Integer.toString(e.getErrorCode()) + "@" + e.getMessage());
    } catch (Exception ex) {
      log4j.error("Exception in query: " + strSql + "Exception:" + ex);
      throw new ServletException("@CODE=@" + ex.getMessage());
    } finally {
      try {
        connectionProvider.releasePreparedStatement(st);
      } catch (Exception ignore) {
        ignore.printStackTrace();
      }
    }
    ReportCashflowForecastData objectReportCashflowForecastData[] =
        new ReportCashflowForecastData[vector.size()];
    vector.copyInto(objectReportCashflowForecastData);
    return (objectReportCashflowForecastData);
  }
  public static ReportCashflowForecastData[] selectAllLines(
      ConnectionProvider connectionProvider,
      String dateFormat,
      String language,
      String bankaccount,
      String dateMax,
      String order,
      int firstRegister,
      int numberRegisters)
      throws ServletException {
    String strSql = "";
    strSql =
        strSql
            + "      SELECT (B.CODEBANK || '/' || B.CODEBRANCH || B.DIGITCONTROL || BA.CODEACCOUNT || '.' || BA.DIGITCONTROL) AS BANKACCOUNT,"
            + "              DATEPLANNED, "
            + "              ISRECEIPT, "
            + "              COALESCE(mt.msgtext,m.msgtext) as IsReceiptMessage, "
            + "              DP.C_DEBT_PAYMENT_ID, "
            + "              BP.NAME AS BPARTNER,"
            + "              DP.DESCRIPTION, "
            + "              I.DOCUMENTNO AS INVOICENO, "
            + "              TO_CHAR(I.DATEINVOICED,TO_CHAR(?)) AS DATEINVOICED,"
            + "              c_currency_convert(DP.AMOUNT, dp.C_CURRENCY_ID, ba.c_Currency_ID, dp.dateplanned,null, ba.ad_client_id, ba.ad_org_id) AS AMOUNT,"
            + "              (CASE WHEN DP.C_Invoice_ID IS NOT NULL"
            + "                THEN (CASE ISRECEIPT WHEN 'Y' THEN 'SalesInvoice/Payment_Relation.html' WHEN 'N' THEN 'PurchaseInvoice/Payment_Relation.html' END) "
            + "               ELSE"
            + "                CASE WHEN DP.C_Order_ID IS NOT NULL"
            + "                  THEN (CASE ISRECEIPT WHEN 'Y' THEN 'SalesOrder/Payment_Relation.html' WHEN 'N' THEN 'PurchaseOrder/Payment_Relation.html' END)"
            + "                ELSE"
            + "                  CASE WHEN DP.C_Settlement_Generate_ID IS NOT NULL"
            + "                    THEN 'Settlement/CreatedPayments_Relation.html'"
            + "                  END                  "
            + "                END"
            + "               END) AS URL,"
            + "              (SELECT SUM(c_currency_convert(STMTAMT, BL.C_CURRENCY_ID, BA.C_CURRENCY_ID, BL.VALUTADATE, NULL, ba.ad_client_id, ba.ad_org_id)) AS BALANCE             "
            + "                 FROM C_BANKSTATEMENTLINE BL,"
            + "                      C_BANKSTATEMENT     BS"
            + "                 WHERE BS.C_BANKSTATEMENT_ID = BL.C_BANKSTATEMENT_ID"
            + "                   AND BS.PROCESSED='Y'  "
            + "                   AND BS.STATEMENTDATE <= NOW()"
            + "                   AND BS.C_BANKACCOUNT_ID = DP.C_BANKACCOUNT_ID ) as initialbalance"
            + "        FROM C_DEBT_PAYMENT_V DP LEFT JOIN C_INVOICE I ON DP.C_INVOICE_ID = I.C_INVOICE_ID,"
            + "             AD_MESSAGE M        LEFT JOIN AD_MESSAGE_TRL MT ON m.ad_message_id = mt.ad_message_id         "
            + "                                                            and mt.ad_language = ?,"
            + "             C_BPARTNER BP,"
            + "             C_BANKACCOUNT       BA,"
            + "             C_BANK              B             "
            + "       WHERE BA.C_BANKACCOUNT_ID = DP.C_BANKACCOUNT_ID"
            + "         AND C_SETTLEMENT_CANCEL_ID IS NULL"
            + "         AND BA.C_BANK_ID = B.C_BANK_ID "
            + "         AND DP.C_BANKACCOUNT_ID IS NOT NULL         "
            + "         AND C_DEBT_PAYMENT_STATUS(C_SETTLEMENT_CANCEL_ID, CANCEL_PROCESSED, GENERATE_PROCESSED, DP.ISPAID, ISVALID, C_CASHLINE_ID, C_BANKSTATEMENTLINE_ID) = 'P'"
            + "         AND BP.C_BPARTNER_ID = DP.C_BPARTNER_ID"
            + "         AND 2=2";
    strSql =
        strSql
            + ((bankaccount == null || bankaccount.equals(""))
                ? ""
                : " AND DP.C_BANKACCOUNT_ID = ? ");
    strSql =
        strSql
            + "         AND M.VALUE = (CASE DP.ISRECEIPT WHEN 'Y' THEN 'Income' WHEN 'N' THEN 'Payments' END)"
            + "         AND NOT EXISTS (SELECT 1 "
            + "                           FROM C_REMITTANCELINE  RL,"
            + "                                C_REMITTANCE      R,"
            + "                                C_REMITTANCE_TYPE RT"
            + "                          WHERE R.C_REMITTANCE_ID = RL.C_REMITTANCE_ID"
            + "                            AND R.C_REMITTANCE_TYPE_ID = RT.C_REMITTANCE_TYPE_ID"
            + "                            AND CONSOLIDATE = 'Y' "
            + "                            AND (C_DEBT_PAYMENT_ID  = DP.C_DEBT_PAYMENT_ID"
            + "                             OR C_DEBT_PAYMENT_CANCELLED = DP.C_DEBT_PAYMENT_ID)"
            + "                            AND NOT EXISTS (SELECT 1   /*IF CANCEL DP IS IN BANK (IT IS RETURNED) IT HAS TO APPEAR*/"
            + "                                              FROM C_BANKSTATEMENTLINE BSL,"
            + "                                                   C_BANKSTATEMENT B"
            + "                                            WHERE BSL.C_DEBT_PAYMENT_ID = RL.C_DEBT_PAYMENT_CANCELLED"
            + "                                              AND BSL.C_BANKSTATEMENT_ID = B.C_BANKSTATEMENT_ID"
            + "                                              AND B.PROCESSED='Y'))"
            + "         AND 1=1";
    strSql =
        strSql
            + ((dateMax == null || dateMax.equals(""))
                ? ""
                : "  AND DP.DATEPLANNED <= TO_DATE(?) ");
    strSql = strSql + "       ORDER BY ";
    strSql = strSql + ((order == null || order.equals("")) ? "" : order);

    ResultSet result;
    Vector<java.lang.Object> vector = new Vector<java.lang.Object>(0);
    PreparedStatement st = null;

    int iParameter = 0;
    try {
      st = connectionProvider.getPreparedStatement(strSql);
      iParameter++;
      UtilSql.setValue(st, iParameter, 12, null, dateFormat);
      iParameter++;
      UtilSql.setValue(st, iParameter, 12, null, language);
      if (bankaccount != null && !(bankaccount.equals(""))) {
        iParameter++;
        UtilSql.setValue(st, iParameter, 12, null, bankaccount);
      }
      if (dateMax != null && !(dateMax.equals(""))) {
        iParameter++;
        UtilSql.setValue(st, iParameter, 12, null, dateMax);
      }
      if (order != null && !(order.equals(""))) {}

      result = st.executeQuery();
      long countRecord = 0;
      long countRecordSkip = 1;
      boolean continueResult = true;
      while (countRecordSkip < firstRegister && continueResult) {
        continueResult = result.next();
        countRecordSkip++;
      }
      while (continueResult && result.next()) {
        countRecord++;
        ReportCashflowForecastData objectReportCashflowForecastData =
            new ReportCashflowForecastData();
        objectReportCashflowForecastData.bankaccount = UtilSql.getValue(result, "bankaccount");
        objectReportCashflowForecastData.dateplanned =
            UtilSql.getDateValue(result, "dateplanned", "dd-MM-yyyy");
        objectReportCashflowForecastData.isreceipt = UtilSql.getValue(result, "isreceipt");
        objectReportCashflowForecastData.isreceiptmessage =
            UtilSql.getValue(result, "isreceiptmessage");
        objectReportCashflowForecastData.cDebtPaymentId =
            UtilSql.getValue(result, "c_debt_payment_id");
        objectReportCashflowForecastData.bpartner = UtilSql.getValue(result, "bpartner");
        objectReportCashflowForecastData.description = UtilSql.getValue(result, "description");
        objectReportCashflowForecastData.invoiceno = UtilSql.getValue(result, "invoiceno");
        objectReportCashflowForecastData.dateinvoiced = UtilSql.getValue(result, "dateinvoiced");
        objectReportCashflowForecastData.amount = UtilSql.getValue(result, "amount");
        objectReportCashflowForecastData.url = UtilSql.getValue(result, "url");
        objectReportCashflowForecastData.initialbalance =
            UtilSql.getValue(result, "initialbalance");
        objectReportCashflowForecastData.rownum = Long.toString(countRecord);
        objectReportCashflowForecastData.InitRecordNumber = Integer.toString(firstRegister);
        vector.addElement(objectReportCashflowForecastData);
        if (countRecord >= numberRegisters && numberRegisters != 0) {
          continueResult = false;
        }
      }
      result.close();
    } catch (SQLException e) {
      log4j.error("SQL error in query: " + strSql + "Exception:" + e);
      throw new ServletException(
          "@CODE=" + Integer.toString(e.getErrorCode()) + "@" + e.getMessage());
    } catch (Exception ex) {
      log4j.error("Exception in query: " + strSql + "Exception:" + ex);
      throw new ServletException("@CODE=@" + ex.getMessage());
    } finally {
      try {
        connectionProvider.releasePreparedStatement(st);
      } catch (Exception ignore) {
        ignore.printStackTrace();
      }
    }
    ReportCashflowForecastData objectReportCashflowForecastData[] =
        new ReportCashflowForecastData[vector.size()];
    vector.copyInto(objectReportCashflowForecastData);
    return (objectReportCashflowForecastData);
  }