Пример #1
0
  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);
  }
Пример #2
0
  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);
  }