コード例 #1
0
ファイル: MColumn.java プロジェクト: metasfresh/metasfresh
  /**
   * Get Table Constraint
   *
   * @param tableName table name
   * @return table constraint
   */
  public String getConstraint(String tableName) {
    if (isKey()) {
      final String constraintName = tableName + "_Key";
      return "CONSTRAINT " + constraintName + " PRIMARY KEY (" + getColumnName() + ")";
    } else if (DisplayType.isID(getAD_Reference_ID()) && !isDDL_NoForeignKey()) {
      // gh #539 Add missing FK constraints
      // create a FK-constraint, using the same view we also used to "manually" create
      // FK-constraints in the past.

      // get an FK-constraint for this table, if any
      // this returns something like
      // "ALTER TABLE A_Asset_Change ADD CONSTRAINT ADepreciationCalcT_AAssetChang FOREIGN KEY
      // (A_Depreciation_Calc_Type) REFERENCES A_Depreciation_Method DEFERRABLE INITIALLY DEFERRED;"
      final String fkConstraintDDL =
          DB.getSQLValueStringEx(
              ITrx.TRXNAME_None,
              "SELECT SqlText FROM db_columns_fk WHERE TableName=? AND ColumnName=?",
              tableName,
              getColumnName());
      if (!Check.isEmpty(fkConstraintDDL, true)) {
        // remove the "ALTER TABLE ... ADD" and the trailing ";"
        // the (?iu) means the the patters is created with Pattern.CASE_INSENSITIVE |
        // Pattern.UNICODE_CASE
        // thanks to https://blogs.oracle.com/xuemingshen/entry/case_insensitive_matching_in_java
        final String constraint =
            fkConstraintDDL
                .replaceFirst("(?iu)ALTER *TABLE *" + tableName + " *ADD *", "")
                .replaceFirst(";$", "");
        return constraint;
      }
    }
    return "";
  } // getConstraint
コード例 #2
0
ファイル: QueryTest.java プロジェクト: metasfresh/metasfresh
 @Test
 public void testAggregate() throws Exception {
   final String sqlFrom = "FROM C_InvoiceLine WHERE IsActive='Y'";
   final Query query =
       new Query(getCtx(), "C_InvoiceLine", null, getTrxName()).setOnlyActiveRecords(true);
   //
   // Test COUNT:
   assertEquals(
       "COUNT not match",
       DB.getSQLValueBDEx(getTrxName(), "SELECT COUNT(*) " + sqlFrom),
       query.aggregate(null, Query.AGGREGATE_COUNT));
   //
   // Test SUM:
   assertEquals(
       "SUM not match",
       DB.getSQLValueBDEx(getTrxName(), "SELECT COALESCE(SUM(LineNetAmt+TaxAmt),0) " + sqlFrom),
       query.aggregate("LineNetAmt+TaxAmt", Query.AGGREGATE_SUM));
   //
   // Test MIN:
   assertEquals(
       "MIN not match",
       DB.getSQLValueBDEx(getTrxName(), "SELECT MIN(LineNetAmt) " + sqlFrom),
       query.aggregate("LineNetAmt", Query.AGGREGATE_MIN));
   //
   // Test MAX:
   assertEquals(
       "MAX not match",
       DB.getSQLValueBDEx(getTrxName(), "SELECT MAX(LineNetAmt) " + sqlFrom),
       query.aggregate("LineNetAmt", Query.AGGREGATE_MAX));
   //
   // Test aggregate (String) - FR [ 2726447 ]
   assertEquals(
       "MAX not match (String)",
       DB.getSQLValueStringEx(getTrxName(), "SELECT MAX(Description) " + sqlFrom),
       query.aggregate("Description", Query.AGGREGATE_MAX, String.class));
   //
   // Test aggregate (Timestamp) - FR [ 2726447 ]
   assertEquals(
       "MAX not match (Timestamp)",
       DB.getSQLValueTSEx(getTrxName(), "SELECT MAX(Updated) " + sqlFrom),
       query.aggregate("Updated", Query.AGGREGATE_MAX, Timestamp.class));
 }
コード例 #3
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;
  }
コード例 #4
0
  private AttributesImpl createColumnBinding(AttributesImpl atts, X_AD_Column m_Column) {
    String sql = null;
    String name = null;
    atts.clear();
    if (m_Column.getAD_Column_ID() <= PackOut.MAX_OFFICIAL_ID)
      atts.addAttribute(
          "", "", "AD_Column_ID", "CDATA", Integer.toString(m_Column.getAD_Column_ID()));
    if (m_Column.getAD_Column_ID() > 0) {
      sql = "SELECT ColumnName FROM AD_Column WHERE AD_Column_ID=?";
      name = DB.getSQLValueString(null, sql, m_Column.getAD_Column_ID());
      atts.addAttribute("", "", "ADColumnNameID", "CDATA", name);
    } else atts.addAttribute("", "", "ADColumnNameID", "CDATA", "");
    //
    if (m_Column.getAD_Process_ID() > 0) {
      sql = "SELECT Value FROM AD_Process WHERE AD_Process_ID=?";
      name = DB.getSQLValueStringEx(null, sql, m_Column.getAD_Process_ID());
      atts.addAttribute("", "", "ADProcessNameID", "CDATA", name);
    } else {
      atts.addAttribute("", "", "ADProcessNameID", "CDATA", "");
    }
    // Element - this info is not needed since we search for element based on ColumnName
    if (m_Column.getAD_Element_ID() > 0) {
      sql = "SELECT ColumnName FROM AD_Element WHERE AD_Element_ID=?";
      name = DB.getSQLValueStringEx(null, sql, m_Column.getAD_Element_ID());
      atts.addAttribute("", "", "ADElementNameID", "CDATA", name);
    } else {
      atts.addAttribute("", "", "ADElementNameID", "CDATA", "");
    }
    //
    if (m_Column.getAD_Reference_ID() > 0) {
      sql = "SELECT Name FROM AD_Reference WHERE AD_Reference_ID=?";
      name = DB.getSQLValueString(null, sql, m_Column.getAD_Reference_ID());
      atts.addAttribute("", "", "ADReferenceNameID", "CDATA", name);
    } else atts.addAttribute("", "", "ADReferenceNameID", "CDATA", "");
    if (m_Column.getAD_Reference_Value_ID() > 0) {
      sql = "SELECT Name FROM AD_Reference WHERE AD_Reference_ID=?";
      name = DB.getSQLValueString(null, sql, m_Column.getAD_Reference_Value_ID());
      atts.addAttribute("", "", "ADReferenceNameValueID", "CDATA", name);
    } else atts.addAttribute("", "", "ADReferenceNameValueID", "CDATA", "");
    if (m_Column.getAD_Table_ID() > 0) {
      sql = "SELECT TableName FROM AD_Table WHERE AD_Table_ID=?";
      name = DB.getSQLValueString(null, sql, m_Column.getAD_Table_ID());
      atts.addAttribute("", "", "ADTableNameID", "CDATA", name);
    } else atts.addAttribute("", "", "ADTableNameID", "CDATA", "");
    if (m_Column.getAD_Val_Rule_ID() > 0) {
      sql = "SELECT Name FROM AD_Val_Rule WHERE AD_Val_Rule_ID=?";
      name = DB.getSQLValueString(null, sql, m_Column.getAD_Val_Rule_ID());
      atts.addAttribute("", "", "ADValRuleNameID", "CDATA", name);
    } else atts.addAttribute("", "", "ADValRuleNameID", "CDATA", "");
    atts.addAttribute(
        "", "", "Callout", "CDATA", (m_Column.getCallout() != null ? m_Column.getCallout() : ""));
    atts.addAttribute(
        "",
        "",
        "ColumnSQL",
        "CDATA",
        (m_Column.getColumnSQL() != null ? m_Column.getColumnSQL() : ""));
    atts.addAttribute(
        "",
        "",
        "ColumnName",
        "CDATA",
        (m_Column.getColumnName() != null ? m_Column.getColumnName() : ""));
    atts.addAttribute(
        "",
        "",
        "DefaultValue",
        "CDATA",
        (m_Column.getDefaultValue() != null ? m_Column.getDefaultValue() : ""));
    atts.addAttribute(
        "",
        "",
        "Description",
        "CDATA",
        (m_Column.getDescription() != null ? m_Column.getDescription() : ""));
    atts.addAttribute(
        "",
        "",
        "EntityType",
        "CDATA",
        (m_Column.getEntityType() != null ? m_Column.getEntityType() : ""));
    atts.addAttribute(
        "",
        "",
        "FieldLength",
        "CDATA",
        (m_Column.getFieldLength() > 0 ? "" + m_Column.getFieldLength() : "0"));
    atts.addAttribute(
        "", "", "Help", "CDATA", (m_Column.getHelp() != null ? m_Column.getHelp() : ""));
    atts.addAttribute(
        "",
        "",
        "isAlwaysUpdateable",
        "CDATA",
        (m_Column.isAlwaysUpdateable() == true ? "true" : "false"));
    // atts.addAttribute("","","isEncrypted","CDATA",(m_Column.getIsEncrypted()==
    // true ? "true":"false"));
    atts.addAttribute(
        "", "", "isIdentifier", "CDATA", (m_Column.isIdentifier() == true ? "true" : "false"));
    atts.addAttribute("", "", "isKey", "CDATA", (m_Column.isKey() == true ? "true" : "false"));
    atts.addAttribute(
        "", "", "isMandatory", "CDATA", (m_Column.isMandatory() == true ? "true" : "false"));
    atts.addAttribute(
        "", "", "isParent", "CDATA", (m_Column.isParent() == true ? "true" : "false"));
    atts.addAttribute(
        "",
        "",
        "isSelectionColumn",
        "CDATA",
        (m_Column.isSelectionColumn() == true ? "true" : "false"));
    atts.addAttribute(
        "", "", "isActive", "CDATA", (m_Column.isActive() == true ? "true" : "false"));
    atts.addAttribute(
        "", "", "isTranslated", "CDATA", (m_Column.isTranslated() == true ? "true" : "false"));
    atts.addAttribute(
        "", "", "isUpdateable", "CDATA", (m_Column.isUpdateable() == true ? "true" : "false"));
    atts.addAttribute(
        "", "", "Name", "CDATA", (m_Column.getName() != null ? m_Column.getName() : ""));
    atts.addAttribute("", "", "getIsSyncDatabase", "CDATA", "Y");
    atts.addAttribute(
        "",
        "",
        "ReadOnlyLogic",
        "CDATA",
        (m_Column.getReadOnlyLogic() != null ? m_Column.getReadOnlyLogic() : ""));
    atts.addAttribute(
        "", "", "SeqNo", "CDATA", (m_Column.getSeqNo() > 0 ? "" + m_Column.getSeqNo() : "0"));
    atts.addAttribute(
        "", "", "VFormat", "CDATA", (m_Column.getVFormat() != null ? m_Column.getVFormat() : ""));
    atts.addAttribute(
        "",
        "",
        "ValueMax",
        "CDATA",
        (m_Column.getValueMax() != null ? m_Column.getValueMax() : ""));
    atts.addAttribute(
        "",
        "",
        "ValueMin",
        "CDATA",
        (m_Column.getValueMin() != null ? m_Column.getValueMin() : ""));
    atts.addAttribute(
        "",
        "",
        "Version",
        "CDATA",
        (m_Column.getVersion() != null ? "" + m_Column.getVersion() : "0.0"));
    atts.addAttribute(
        "",
        "",
        "InfoFactoryClass",
        "CDATA",
        (m_Column.getInfoFactoryClass() != null ? m_Column.getInfoFactoryClass() : ""));

    return atts;
  }