/**
* 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
@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));
}
/**
* @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;
}
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;
}