Exemple #1
0
  @Override
  public void loadData(Object... params) {
    if (sql == null)
      throw new IllegalStateException(
          "Table not initialized. Please use prepareTable method first");

    int selectedId = getSelectedId();

    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
      pstmt = DB.prepareStatement(sql, null);
      DB.setParameters(pstmt, params);
      rs = pstmt.executeQuery();
      this.loadTable(rs);
    } catch (Exception e) {
      log.log(Level.SEVERE, sql, e);
    } finally {
      DB.close(rs, pstmt);
      rs = null;
      pstmt = null;
    }

    selectById(selectedId);
  }
  /**
   * Retrieves all entity types from underlying data source.
   *
   * @return all entity types as a map of EntityType to {@link EntityTypeEntry}.
   */
  @VisibleForTesting
  Map<String, EntityTypeEntry> retrieveEntityTypeEntries() {
    final Map<String, EntityTypeEntry> entityTypeEntries = new HashMap<>(50);
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    final String sql = "SELECT * FROM AD_EntityType WHERE IsActive=? ORDER BY AD_EntityType_ID";
    final Object[] params = new Object[] {true};
    try {
      pstmt = DB.prepareStatement(sql, ITrx.TRXNAME_None);
      DB.setParameters(pstmt, params);
      rs = pstmt.executeQuery();
      while (rs.next()) {
        final EntityTypeEntry entry = loadEntityTypeEntry(rs);
        entityTypeEntries.put(entry.getEntityType(), entry);
      }
    } catch (SQLException e) {
      throw new DBException(e, sql, params);
    } finally {
      DB.close(rs, pstmt);
      rs = null;
      pstmt = null;
    }

    return entityTypeEntries;
  }
  public static void updateTreeNode(PO po) {
    int nodeId = po.get_ID();
    if (nodeId < 0) {
      return; // nothing to do, because our PO has no ID to match against a tree node
    }
    final int AD_Table_ID = po.get_Table_ID();
    if (!MTree.hasTree(AD_Table_ID)) {
      return;
    }
    final String nodeTableName = MTree.getNodeTableName(AD_Table_ID);
    if (nodeTableName == null) {
      return;
    }

    //
    // services
    final IPOTreeSupportFactory treeSupportFactory = Services.get(IPOTreeSupportFactory.class);

    final String trxName = po.get_TrxName();
    final IPOTreeSupport treeSupport = treeSupportFactory.get(po.get_TableName());

    if (po.is_ValueChanged("IsSummary") && !po.get_ValueAsBoolean("IsSummary")) {
      // Move all its children to parent
      final List<MTreeNode> children =
          fetchNodes(AD_Table_ID, "Parent_ID=?", new Object[] {nodeId}, trxName);
      if (children.size() > 0) {
        throw new AdempiereException("TreeNodeHasChildren"); // TODO: translate
      }
    }

    if (treeSupport.isParentChanged(po)) {
      int parentId = treeSupport.getParent_ID(po);
      int oldParentId = treeSupport.getOldParent_ID(po);
      int seqNo = -1; // compute

      final String sql =
          "SELECT AD_Tree_ID, TreeType FROM AD_Tree WHERE AD_Table_ID=? AND AD_Client_ID=?";
      PreparedStatement pstmt = null;
      ResultSet rs = null;
      try {
        pstmt = DB.prepareStatement(sql, trxName);
        DB.setParameters(pstmt, new Object[] {AD_Table_ID, po.getAD_Client_ID()});
        rs = pstmt.executeQuery();
        while (rs.next()) {
          int AD_Tree_ID = rs.getInt(COLUMNNAME_AD_Tree_ID);
          String treeType = rs.getString(COLUMNNAME_TreeType);
          updateNode(
              po, AD_Tree_ID, treeType, AD_Table_ID, nodeId, parentId, oldParentId, seqNo, trxName);
        }
      } catch (SQLException e) {
        throw new DBException(e);
      } finally {
        DB.close(rs, pstmt);
        rs = null;
        pstmt = null;
      }
    }
  }
  private static List<MTreeNode> fetchNodes(
      int AD_Table_ID, String whereClause, Object[] params, String trxName) {
    List<MTreeNode> list = new ArrayList<MTreeNode>();
    final String nodeTableName = MTree.getNodeTableName(AD_Table_ID);
    if (nodeTableName == null) return list;

    PreparedStatement pstmt = null;
    ResultSet rs = null;
    StringBuffer sql =
        new StringBuffer("SELECT Node_ID, Parent_ID, SeqNo, AD_Tree_ID ")
            .append(" FROM ")
            .append(nodeTableName)
            .append(" WHERE ")
            .append(" EXISTS (SELECT * FROM AD_Tree t ")
            .append("WHERE t.AD_Tree_ID=" + nodeTableName + ".AD_Tree_ID AND t.AD_Table_ID=")
            .append(AD_Table_ID)
            .append(")")
            .append(" AND (")
            .append(whereClause)
            .append(")")
            .append(" ORDER BY AD_Tree_ID, Parent_ID, Node_ID");
    try {
      pstmt = DB.prepareStatement(sql.toString(), trxName);
      DB.setParameters(pstmt, params);
      rs = pstmt.executeQuery();
      while (rs.next()) {
        int node_ID = rs.getInt("Node_ID");
        int parent_ID = rs.getInt("Node_ID");
        int seqNo = rs.getInt("SeqNo");
        int AD_Tree_ID = rs.getInt("AD_Tree_ID");
        MTreeNode node =
            new MTreeNode(node_ID, seqNo, null, null, parent_ID, false, null, false, null);
        node.setAD_Tree_ID(AD_Tree_ID);
        node.setAD_Table_ID(AD_Table_ID);
        list.add(node);
      }
    } catch (SQLException e) {
      throw new DBException(e);
    } finally {
      DB.close(rs, pstmt);
      rs = null;
      pstmt = null;
    }
    return list;
  }
  private final PO retrievePO(
      final Properties ctx,
      final String tableName,
      final String whereClause,
      final Object[] params,
      final String trxName) {
    if (whereClause == null || whereClause.length() == 0) {
      return null;
    }

    //
    PO po = null;
    final POInfo info = POInfo.getPOInfo(tableName);
    if (info == null) {
      return null;
    }

    final StringBuilder sqlBuffer = info.buildSelect();
    sqlBuffer.append(" WHERE ").append(whereClause);
    final String sql = sqlBuffer.toString();

    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
      pstmt = DB.prepareStatement(sql, trxName);
      DB.setParameters(pstmt, params);

      rs = pstmt.executeQuery();
      if (rs.next()) {
        po = getPO(ctx, tableName, rs, trxName);
      }
    } catch (Exception e) {
      log.error(sql, e);
      MetasfreshLastError.saveError(log, "Error", e);
    } finally {
      DB.close(rs, pstmt);
    }

    return po;
  }
  /**
   * Introduced to avoid false index qualification on before save.
   *
   * <p>Before this functionality was introduced, ADempiere was identifying which indexes will be
   * affected by a GridTab save only based on which columns were modified. In case of a new record,
   * all columns are considered as modified.
   *
   * <p>The index's SQL where clause was not considered. That is the source of our issue.
   *
   * <p>To avoid this case, we introduced this method which actually generates a dummy SELECT on
   * "dual" table and apply the where clause on it.
   *
   * <p>For example, consider a table called "MyTable" with Column1, Column2... ColumnN columns.
   *
   * <p>Our index has following where clause: Column1='Y' and Column2='N'.
   *
   * <p>In this case, this method will try to check if the where clause matches our GridTab data by
   * generating following SQL SELECT:
   *
   * <pre>
   * SELECT * FROM (SELECT ? AS Column1, ? AS Column2, ..., ? AS ColumnN) MyTable
   * WHERE
   *     Column1='Y' AND Column2='N'
   * </pre>
   *
   * @task 02627
   */
  private boolean isWhereClauseMatched(final GridTab tab) {
    final String trxName = null;

    final String whereClause = getWhereClause();
    if (Check.isEmpty(whereClause, true)) {
      return true;
    }

    final StringBuffer sql = new StringBuffer();
    final List<Object> params = new ArrayList<Object>();
    for (int i = 0; i < tab.getFieldCount(); i++) {
      final GridField field = tab.getField(i);
      if (field.isVirtualColumn()) {
        continue;
      }

      final String columnName = field.getColumnName();
      final Object value = field.getValue();

      if (log.isDebugEnabled()) {
        log.debug("column: " + columnName + "=" + value + ", index=" + i);
      }

      if (sql.length() > 0) {
        sql.append(", ");
      }

      if (value == null) {

        // NOTE: If we are not specify the datatype, we get
        // "org.postgresql.util.PSQLException: ERROR: could not determine data type of parameter..."
        // see http://archives.postgresql.org/pgsql-jdbc/2006-08/msg00163.php
        // this case is for when we have those null fields in where clause
        if (DisplayType.isText(field.getDisplayType())) sql.append("NULL::text");
        else if (DisplayType.isID(field.getDisplayType())
            || DisplayType.Integer == field.getDisplayType()) sql.append("NULL::integer");
        else if (DisplayType.isNumeric(field.getDisplayType())) sql.append("NULL::numeric");
        else sql.append("NULL");
      } else if (value instanceof java.util.Date) {
        // NOTE: If we are not specify the datatype, we get
        // "org.postgresql.util.PSQLException: ERROR: could not determine data type of parameter..."
        // see http://archives.postgresql.org/pgsql-jdbc/2006-08/msg00163.php
        sql.append("?::timestamp");
        params.add(value);
      } else {
        sql.append("?");
        params.add(value);
      }
      sql.append(" AS ").append(columnName);
    }

    if (sql.length() <= 0) {
      return true;
    }

    sql.insert(0, "SELECT * FROM (SELECT ")
        .append(") ")
        .append(tab.getTableName())
        .append(" WHERE ")
        .append(getWhereClause());

    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
      pstmt = DB.prepareStatement(sql.toString(), trxName);
      DB.setParameters(pstmt, params);
      rs = pstmt.executeQuery();
      final boolean matched = rs.next();
      return matched;
    } catch (final SQLException e) {
      throw new DBException(e, sql.toString(), params);
    } finally {
      DB.close(rs, pstmt);
      rs = null;
      pstmt = null;
    }
  }
Exemple #7
0
  /**
   * @param R_Group_ID
   * @param trxName
   * @return true if update, false if status remains the same
   */
  public static boolean updateCCM_Bundle_Status(int R_Group_ID, String trxName) {
    final boolean isActive =
        "Y"
            .equals(
                DB.getSQLValueStringEx(
                    trxName, "SELECT IsActive FROM R_Group WHERE R_Group_ID=?", R_Group_ID));
    //
    final String sql =
        "SELECT"
            + "  b.IsActive"
            + ", b."
            + R_Group_CCM_Bundle_Status
            + ", COALESCE(rs.IsClosed, 'N') AS IsClosed"
            + ", COUNT(*) AS cnt"
            + " FROM R_Group b"
            + " INNER JOIN "
            + MRGroupProspect.Table_Name
            + " c ON (c.R_Group_ID=b.R_Group_ID)"
            + " LEFT OUTER JOIN R_Request rq ON (rq.R_Request_ID=c.R_Request_ID)"
            + " LEFT OUTER JOIN R_Status rs ON (rs.R_Status_ID=rq.R_Status_ID)"
            + " WHERE b.R_Group_ID=?"
            + " GROUP BY "
            + "  b.IsActive"
            + ", b."
            + R_Group_CCM_Bundle_Status
            + ", COALESCE(rs.IsClosed, 'N')";
    int countAll = 0;
    int countClosed = 0;
    String oldStatus = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
      pstmt = DB.prepareStatement(sql, trxName);
      DB.setParameters(pstmt, new Object[] {R_Group_ID});
      rs = pstmt.executeQuery();
      while (rs.next()) {
        oldStatus = rs.getString(2);
        final boolean isClosed = "Y".equals(rs.getString(3));
        final int no = rs.getInt(4);
        if (isClosed) countClosed += no;
        countAll += no;
      }
    } catch (SQLException e) {
      throw new DBException(e, sql);
    } finally {
      DB.close(rs, pstmt);
      rs = null;
      pstmt = null;
    }
    //
    final String newStatus;
    // Closed = field "active" is unchecked
    if (!isActive) {
      newStatus = CCM_Bundle_Status_Closed;
    }
    //  New = no contacts added yet
    else if (countAll == 0) {
      newStatus = CCM_Bundle_Status_New;
    }
    // In Progress = contacts added and there are still open requests
    else if (countAll > 0 && countAll != countClosed) {
      newStatus = CCM_Bundle_Status_InProgress;
    }
    // Done(Completed) = all requests are closed
    else if (countAll > 0 && countAll == countClosed) {
      newStatus = CCM_Bundle_Status_Done;
    } else {
      // should not happen
      throw new AdempiereException(
          "Unhandled status. Please contact your System Administrator"
              + " (CountAll="
              + countAll
              + ", CountClosed="
              + countClosed
              + ", R_Status_ID="
              + R_Group_ID
              + ")");
    }

    // If status not found, try it directly
    // Case: the bundle has no contacts
    if (oldStatus == null) {
      oldStatus =
          DB.getSQLValueStringEx(
              trxName,
              "SELECT " + R_Group_CCM_Bundle_Status + " FROM R_Group WHERE R_Group_ID=?",
              R_Group_ID);
    }

    boolean updated = false;
    if (oldStatus == null || newStatus.compareTo(oldStatus) != 0) {
      int no =
          DB.executeUpdateEx(
              "UPDATE R_Group SET " + R_Group_CCM_Bundle_Status + "=? WHERE R_Group_ID=?",
              new Object[] {newStatus, R_Group_ID},
              trxName);
      if (no != 1) throw new AdempiereException("Cannot update bundle " + R_Group_ID);
      updated = true;
    }

    return updated;
  }
 /** Called from {@link Info.Worker} only. */
 @Override
 protected void setParameters(final PreparedStatement pstmt, final boolean IGNORED)
     throws SQLException {
   DB.setParameters(pstmt, sqlWhereParams.get());
 }