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