/**
* Devuelve un array con todos los payments vinculados a la cabecera
*
* @param trxName
* @return MPayment[] array con los pagos vinculados al documento
* @throws SQLException
*/
public MPayment[] getPayments(String trxName) throws SQLException {
// TODO - Analize genereate a cache for this payments
List<MPayment> pays = new ArrayList<MPayment>();
String sql = "SELECT * FROM C_Payment WHERE LAR_PaymentHeader_ID = ?";
PreparedStatement pstmt;
pstmt = DB.prepareStatement(sql, trxName);
ResultSet rs = null;
try {
pstmt.setInt(1, getLAR_PaymentHeader_ID());
rs = pstmt.executeQuery();
while (rs.next()) pays.add(new MPayment(getCtx(), rs, trxName));
} catch (SQLException e) {
DB.close(rs, pstmt);
throw e;
} finally {
DB.close(rs, pstmt);
rs = null;
pstmt = null;
}
return pays.toArray(new MPayment[pays.size()]);
}
/**
* BankStmt - Payment. Update Transaction Amount when payment is selected
*
* @param ctx context
* @param WindowNo window no
* @param mTab tab
* @param mField field
* @param value value
* @return null or error message
*/
public String payment(
Properties ctx, int WindowNo, GridTab mTab, GridField mField, Object value) {
Integer C_Payment_ID = (Integer) value;
if (C_Payment_ID == null || C_Payment_ID.intValue() == 0) return "";
//
BigDecimal stmt = (BigDecimal) mTab.getValue("StmtAmt");
if (stmt == null) stmt = Env.ZERO;
String sql = "SELECT PayAmt FROM C_Payment_v WHERE C_Payment_ID=?"; // 1
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt = DB.prepareStatement(sql, null);
pstmt.setInt(1, C_Payment_ID.intValue());
rs = pstmt.executeQuery();
if (rs.next()) {
BigDecimal bd = rs.getBigDecimal(1);
mTab.setValue("TrxAmt", bd);
if (stmt.compareTo(Env.ZERO) == 0) mTab.setValue("StmtAmt", bd);
}
} catch (SQLException e) {
log.log(Level.SEVERE, "BankStmt_Payment", e);
return e.getLocalizedMessage();
} finally {
DB.close(rs, pstmt);
rs = null;
pstmt = null;
}
// Recalculate Amounts
amount(ctx, WindowNo, mTab, mField, value);
return "";
} // payment
@Test
public void testScroll() throws Exception {
POResultSet<MTable> rs =
new Query(getCtx(), "AD_Table", "TableName IN (?,?)", getTrxName())
.setParameters(new Object[] {"C_Invoice", "M_InOut"})
.setOrderBy("TableName")
.scroll();
try {
int i = 0;
while (rs.hasNext()) {
MTable t = rs.next();
if (i == 0) {
assertEquals("Invalid object " + i, "C_Invoice", t.getTableName());
} else if (i == 1) {
assertEquals("Invalid object " + i, "M_InOut", t.getTableName());
} else {
assertFalse("More objects retrived than expected", true);
}
i++;
}
} finally {
DB.close(rs);
rs = null;
}
}
/**
* Get Allocations of Invoice
*
* @param ctx context
* @param C_Invoice_ID payment
* @return allocations of payment
* @param trxName transaction
*/
public static MAllocationHdr[] getOfInvoice(Properties ctx, int C_Invoice_ID, String trxName) {
String sql =
"SELECT * FROM C_AllocationHdr h "
+ "WHERE IsActive='Y'"
+ " AND EXISTS (SELECT * FROM C_AllocationLine l "
+ "WHERE h.C_AllocationHdr_ID=l.C_AllocationHdr_ID AND l.C_Invoice_ID=?)";
ArrayList<MAllocationHdr> list = new ArrayList<MAllocationHdr>();
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt = DB.prepareStatement(sql, trxName);
pstmt.setInt(1, C_Invoice_ID);
rs = pstmt.executeQuery();
while (rs.next()) list.add(new MAllocationHdr(ctx, rs, trxName));
} catch (Exception e) {
s_log.log(Level.SEVERE, sql, e);
} finally {
DB.close(rs, pstmt);
rs = null;
pstmt = null;
}
MAllocationHdr[] retValue = new MAllocationHdr[list.size()];
list.toArray(retValue);
return retValue;
} // getOfInvoice
/**
* Get Labor Cost from Expense Report
*
* @param as Account Schema
* @return Unit Labor Cost
*/
private BigDecimal getLaborCost(MAcctSchema as) {
// Todor Lulov 30.01.2008
BigDecimal retValue = Env.ZERO;
BigDecimal qty = Env.ZERO;
String sql =
"SELECT ConvertedAmt, Qty FROM S_TimeExpenseLine "
+ " WHERE S_TimeExpenseLine.S_TimeExpenseLine_ID = ?";
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt = DB.prepareStatement(sql.toString(), getTrxName());
pstmt.setInt(1, m_issue.getS_TimeExpenseLine_ID());
rs = pstmt.executeQuery();
if (rs.next()) {
retValue = rs.getBigDecimal(1);
qty = rs.getBigDecimal(2);
retValue = retValue.multiply(qty);
log.fine("ExpLineCost = " + retValue);
} else log.warning("Not found for S_TimeExpenseLine_ID=" + m_issue.getS_TimeExpenseLine_ID());
} catch (Exception e) {
log.log(Level.SEVERE, sql.toString(), e);
} finally {
DB.close(rs, pstmt);
pstmt = null;
rs = null;
}
return retValue;
} // getLaborCost
@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);
}
/**
* Get Logs
*
* @return logs
*/
public AdempiereProcessorLog[] getLogs() {
ArrayList<MLdapProcessorLog> list = new ArrayList<MLdapProcessorLog>();
String sql =
"SELECT * "
+ "FROM AD_LdapProcessorLog "
+ "WHERE AD_LdapProcessor_ID=? "
+ "ORDER BY Created DESC";
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt = DB.prepareStatement(sql, get_TrxName());
pstmt.setInt(1, getAD_LdapProcessor_ID());
rs = pstmt.executeQuery();
while (rs.next()) list.add(new MLdapProcessorLog(getCtx(), rs, get_TrxName()));
} catch (Exception e) {
log.error(sql, e);
} finally {
DB.close(rs, pstmt);
rs = null;
pstmt = null;
}
MLdapProcessorLog[] retValue = new MLdapProcessorLog[list.size()];
list.toArray(retValue);
return retValue;
} // getLogs
/**
* 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;
}
/**
* Load Invoice Line
*
* @param prod production
* @return DoaLine Array
*/
private DocLine[] loadLines(X_M_Production prod) {
ArrayList<DocLine> list = new ArrayList<DocLine>();
mQtyProduced = new HashMap<>();
String sqlPL = null;
if (prod.isUseProductionPlan()) {
// Production
// -- ProductionLine - the real level
sqlPL =
"SELECT * FROM "
+ " M_ProductionLine pro_line INNER JOIN M_ProductionPlan plan ON pro_line.M_ProductionPlan_id = plan.M_ProductionPlan_id "
+ " INNER JOIN M_Production pro ON pro.M_Production_id = plan.M_Production_id "
+ " WHERE pro.M_Production_ID=? "
+ " ORDER BY plan.M_ProductionPlan_id, pro_line.Line";
} else {
// Production
// -- ProductionLine - the real level
sqlPL =
"SELECT * FROM M_ProductionLine pl " + "WHERE pl.M_Production_ID=? " + "ORDER BY pl.Line";
}
PreparedStatement pstmtPL = null;
ResultSet rsPL = null;
try {
pstmtPL = DB.prepareStatement(sqlPL, getTrxName());
pstmtPL.setInt(1, get_ID());
rsPL = pstmtPL.executeQuery();
while (rsPL.next()) {
X_M_ProductionLine line = new X_M_ProductionLine(getCtx(), rsPL, getTrxName());
if (line.getMovementQty().signum() == 0) {
if (log.isLoggable(Level.INFO)) log.info("LineQty=0 - " + line);
continue;
}
DocLine docLine = new DocLine(line, this);
docLine.setQty(line.getMovementQty(), false);
// Identify finished BOM Product
if (prod.isUseProductionPlan())
docLine.setProductionBOM(
line.getM_Product_ID() == line.getM_ProductionPlan().getM_Product_ID());
else docLine.setProductionBOM(line.getM_Product_ID() == prod.getM_Product_ID());
if (docLine.isProductionBOM()) {
manipulateQtyProduced(
mQtyProduced, line, prod.isUseProductionPlan(), line.getMovementQty());
}
//
if (log.isLoggable(Level.FINE)) log.fine(docLine.toString());
list.add(docLine);
}
} catch (Exception ee) {
log.log(Level.SEVERE, sqlPL, ee);
} finally {
DB.close(rsPL, pstmtPL);
rsPL = null;
pstmtPL = null;
}
DocLine[] dl = new DocLine[list.size()];
list.toArray(dl);
return dl;
} // loadLines
/**
* Get Translation
*
* @param AD_Language language
* @return trl
*/
private MMailTextTrl getTranslation(String AD_Language) {
MMailTextTrl trl = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String sql = "SELECT * FROM R_MailText_Trl WHERE R_MailText_ID=? AND AD_Language=?";
try {
pstmt = DB.prepareStatement(sql, null);
pstmt.setInt(1, getR_MailText_ID());
pstmt.setString(2, AD_Language);
rs = pstmt.executeQuery();
if (rs.next()) {
trl = new MMailTextTrl();
trl.AD_Language = rs.getString("AD_Language");
trl.MailHeader = rs.getString("MailHeader");
trl.MailText = rs.getString("MailText");
trl.MailText2 = rs.getString("MailText2");
trl.MailText3 = rs.getString("MailText3");
}
} catch (Exception e) {
log.log(Level.SEVERE, sql, e);
} finally {
DB.close(rs, pstmt);
rs = null;
pstmt = null;
}
return trl;
} // getTranslation
/**
* Get Lines
*
* @param requery if true requery
* @return lines
*/
public MAllocationLine[] getLines(boolean requery) {
if (m_lines != null && m_lines.length != 0 && !requery) {
set_TrxName(m_lines, get_TrxName());
return m_lines;
}
//
String sql = "SELECT * FROM C_AllocationLine WHERE C_AllocationHdr_ID=?";
ArrayList<MAllocationLine> list = new ArrayList<MAllocationLine>();
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt = DB.prepareStatement(sql, get_TrxName());
pstmt.setInt(1, getC_AllocationHdr_ID());
rs = pstmt.executeQuery();
while (rs.next()) {
MAllocationLine line = new MAllocationLine(getCtx(), rs, get_TrxName());
line.setParent(this);
list.add(line);
}
} catch (Exception e) {
log.log(Level.SEVERE, sql, e);
} finally {
DB.close(rs, pstmt);
rs = null;
pstmt = null;
}
//
m_lines = new MAllocationLine[list.size()];
list.toArray(m_lines);
return m_lines;
} // getLines
public boolean validateSql() {
PreparedStatement pstmt = null;
ResultSet rs = null;
String sql = getSql();
try {
String countSql = Msg.parseTranslation(Env.getCtx(), sql.toString()); // Variables
countSql =
MRole.getDefault()
.addAccessSQL(
countSql,
MTable.getTableName(Env.getCtx(), getAD_Table_ID()), // getTableName(),
MRole.SQL_FULLYQUALIFIED,
MRole.SQL_RO);
pstmt = DB.prepareStatement(countSql, null);
// pstmt.setString(1, p_tableName);
rs = pstmt.executeQuery();
while (rs.next()) {
break;
}
} catch (SQLException e) {
// ADialog.error(WindowNo, c, AD_Message)
// Env.getUi().showError(0, null, sql.toString() + "<br> " + e.getMessage());
log.log(Level.SEVERE, sql, e);
// String error = e.toString();
// show error to user and return: TODO
return false;
} finally {
DB.close(rs, pstmt);
rs = null;
pstmt = null;
}
return true;
} // validate sql
/**
* Fill m_Values with Ref_List values
*
* @param AD_Reference_ID reference
*/
private void readReference(int AD_Reference_ID) {
m_values = new HashMap<String, String>();
String SQL;
if (Env.isBaseLanguage(Env.getCtx(), "AD_Ref_List"))
SQL = "SELECT Value, Name FROM AD_Ref_List WHERE AD_Reference_ID=?";
else
SQL =
"SELECT l.Value, t.Name FROM AD_Ref_List l, AD_Ref_List_Trl t "
+ "WHERE l.AD_Ref_List_ID=t.AD_Ref_List_ID"
+ " AND t.AD_Language='"
+ Env.getAD_Language(Env.getCtx())
+ "'"
+ " AND l.AD_Reference_ID=?";
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt = DB.prepareStatement(SQL, null);
pstmt.setInt(1, AD_Reference_ID);
rs = pstmt.executeQuery();
while (rs.next()) {
String value = rs.getString(1);
String name = rs.getString(2);
m_values.put(value, name);
}
} catch (SQLException e) {
logger.log(Level.SEVERE, SQL, e);
} finally {
DB.close(rs, pstmt);
}
} // readReference
/**
* Get PO Costs in Currency of AcctSchema
*
* @param as Account Schema
* @return Unit PO Cost
*/
private BigDecimal getPOCost(MAcctSchema as) {
BigDecimal retValue = null;
// Uses PO Date
String sql =
"SELECT currencyConvert(ol.PriceActual, o.C_Currency_ID, ?, o.DateOrdered, o.C_ConversionType_ID, ?, ?) "
+ "FROM C_OrderLine ol"
+ " INNER JOIN M_InOutLine iol ON (iol.C_OrderLine_ID=ol.C_OrderLine_ID)"
+ " INNER JOIN C_Order o ON (o.C_Order_ID=ol.C_Order_ID) "
+ "WHERE iol.M_InOutLine_ID=?";
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt = DB.prepareStatement(sql, getTrxName());
pstmt.setInt(1, as.getC_Currency_ID());
pstmt.setInt(2, getAD_Client_ID());
pstmt.setInt(3, getAD_Org_ID());
pstmt.setInt(4, m_issue.getM_InOutLine_ID());
rs = pstmt.executeQuery();
if (rs.next()) {
retValue = rs.getBigDecimal(1);
log.fine("POCost = " + retValue);
} else log.warning("Not found for M_InOutLine_ID=" + m_issue.getM_InOutLine_ID());
} catch (Exception e) {
log.log(Level.SEVERE, sql, e);
} finally {
DB.close(rs, pstmt);
pstmt = null;
rs = null;
}
return retValue;
} // getPOCost();
/**
* Get Accessible Goals
*
* @param ctx context
* @return array of goals
*/
public static MGoal[] getGoals(Properties ctx) {
ArrayList<MGoal> list = new ArrayList<MGoal>();
String sql = "SELECT * FROM PA_Goal WHERE IsActive='Y' " + "ORDER BY SeqNo";
sql =
Env.getUserRolePermissions(ctx)
.addAccessSQL(sql, "PA_Goal", false, true); // RW to restrict Access
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt = DB.prepareStatement(sql, null);
rs = pstmt.executeQuery();
while (rs.next()) {
MGoal goal = new MGoal(ctx, rs, null);
goal.updateGoal(false);
list.add(goal);
}
} catch (Exception e) {
s_log.error(sql, e);
} finally {
DB.close(rs, pstmt);
rs = null;
pstmt = null;
}
MGoal[] retValue = new MGoal[list.size()];
list.toArray(retValue);
return retValue;
} // getGoals
/**
* Get Restriction Lines
*
* @param reload reload data
* @return array of lines
*/
public MGoalRestriction[] getRestrictions(boolean reload) {
if (m_restrictions != null && !reload) return m_restrictions;
ArrayList<MGoalRestriction> list = new ArrayList<MGoalRestriction>();
//
String sql =
"SELECT * FROM PA_GoalRestriction "
+ "WHERE PA_Goal_ID=? AND IsActive='Y' "
+ "ORDER BY Org_ID, C_BPartner_ID, M_Product_ID";
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt = DB.prepareStatement(sql, get_TrxName());
pstmt.setInt(1, getPA_Goal_ID());
rs = pstmt.executeQuery();
while (rs.next()) list.add(new MGoalRestriction(getCtx(), rs, get_TrxName()));
} catch (Exception e) {
log.error(sql, e);
} finally {
DB.close(rs, pstmt);
rs = null;
pstmt = null;
}
//
m_restrictions = new MGoalRestriction[list.size()];
list.toArray(m_restrictions);
return m_restrictions;
} // getRestrictions
/**
* Loop detection of product category tree.
*
* @param productCategoryId
* @param newParentCategoryId
* @param newParentCategoryId New Parent Category
* @return "" or error message
*/
public boolean hasLoopInTree() {
int productCategoryId = getM_Product_Category_ID();
int newParentCategoryId = getM_Product_Category_Parent_ID();
// get values
ResultSet rs = null;
PreparedStatement pstmt = null;
String sql =
" SELECT M_Product_Category_ID, M_Product_Category_Parent_ID FROM M_Product_Category";
final Vector<SimpleTreeNode> categories = new Vector<SimpleTreeNode>(100);
try {
pstmt = DB.prepareStatement(sql, null);
rs = pstmt.executeQuery();
while (rs.next()) {
if (rs.getInt(1) == productCategoryId)
categories.add(new SimpleTreeNode(rs.getInt(1), newParentCategoryId));
categories.add(new SimpleTreeNode(rs.getInt(1), rs.getInt(2)));
}
if (hasLoop(newParentCategoryId, categories, productCategoryId)) return true;
} catch (SQLException e) {
s_log.log(Level.SEVERE, sql, e);
return true;
} finally {
DB.close(rs, pstmt);
rs = null;
pstmt = null;
}
return false;
} // hasLoopInTree
/** Refresh Query */
private void refresh() {
String sql = m_sql;
int pos = m_sql.lastIndexOf(" ORDER BY ");
if (!showAll.isSelected()) {
sql = m_sql.substring(0, pos) + s_sqlWhereSameWarehouse;
if (s_sqlMinLife.length() > 0) sql += s_sqlMinLife;
sql += m_sql.substring(pos);
}
//
log.finest(sql);
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt = DB.prepareStatement(sql, null);
pstmt.setInt(1, m_M_Product_ID);
if (!showAll.isSelected()) {
pstmt.setInt(2, m_M_Warehouse_ID);
}
rs = pstmt.executeQuery();
m_table.loadTable(rs);
} catch (Exception e) {
log.log(Level.SEVERE, sql, e);
} finally {
DB.close(rs, pstmt);
rs = null;
pstmt = null;
}
enableButtons();
}
/**
* Get Plain Text Report (old functionality)
*
* @param rule (ignored)
* @param sql sql select
* @param trxName transaction
* @param attachments (ignored)
* @return list of rows & values
* @throws Exception
* @deprecated
*/
private String getPlainTextReport(
MAlertRule rule, String sql, String trxName, Collection<File> attachments) throws Exception {
StringBuffer result = new StringBuffer();
PreparedStatement pstmt = null;
ResultSet rs = null;
Exception error = null;
try {
pstmt = DB.prepareStatement(sql, trxName);
rs = pstmt.executeQuery();
ResultSetMetaData meta = rs.getMetaData();
while (rs.next()) {
result.append("------------------").append(Env.NL);
for (int col = 1; col <= meta.getColumnCount(); col++) {
result.append(meta.getColumnLabel(col)).append(" = ");
result.append(rs.getString(col));
result.append(Env.NL);
} // for all columns
}
if (result.length() == 0) log.fine("No rows selected");
} catch (Throwable e) {
log.log(Level.SEVERE, sql, e);
if (e instanceof Exception) error = (Exception) e;
else error = new Exception(e.getMessage(), e);
} finally {
DB.close(rs, pstmt);
rs = null;
pstmt = null;
}
// Error occurred
if (error != null) throw new Exception("(" + sql + ") " + Env.NL + error.getLocalizedMessage());
return result.toString();
}
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 DBIndex getDBIndex(String tableName, String indexName, final String trxName)
throws SQLException {
final String[] indexColsFromDB = new String[30];
int indexLengthDB = 0;
DBIndex dbIndex = null;
final DatabaseMetaData md = Trx.get(trxName, true).getConnection().getMetaData();
if (md.storesUpperCaseIdentifiers()) tableName = tableName.toUpperCase();
else if (md.storesLowerCaseIdentifiers()) tableName = tableName.toLowerCase();
final String catalog = "REFERENCE";
final String schema = null;
ResultSet rs = null;
try {
rs = md.getIndexInfo(catalog, schema, tableName, false, true);
while (rs.next()) {
final String dbIndexName = rs.getString("INDEX_NAME");
if (dbIndexName != null && indexName.equalsIgnoreCase(dbIndexName)) {
if (dbIndex == null) {
dbIndex = new DBIndex();
dbIndex.name = dbIndexName;
dbIndex.isUnique = true;
}
String columnName = rs.getString("COLUMN_NAME");
int pos = rs.getShort("ORDINAL_POSITION");
if (pos > 0) {
// EDB returns varchar index columns wrapped with double
// quotes, hence comparing
// after stripping the quotes
if (columnName.startsWith("\"") && columnName.endsWith("\"")) {
columnName = columnName.substring(1, columnName.length() - 1);
}
indexColsFromDB[pos - 1] = columnName;
if (pos > indexLengthDB) indexLengthDB = pos;
}
boolean isNonUnique = rs.getBoolean("NON_UNIQUE");
if (isNonUnique) dbIndex.isUnique = false;
dbIndex.filterCondition = rs.getString("FILTER_CONDITION");
}
}
} finally {
DB.close(rs);
rs = null;
}
//
if (dbIndex == null || indexLengthDB <= 0) return null;
//
dbIndex.columnNames = new String[indexLengthDB];
for (int i = 0; i < indexLengthDB; i++) {
dbIndex.columnNames[i] = indexColsFromDB[i];
}
// Arrays.sort(dbIndex.columnNames);
//
return dbIndex;
}
/**
* Get class for given display type and reference
*
* @param displayType
* @param AD_Reference_ID
* @return class
*/
public static Class<?> getClass(String columnName, int displayType, int AD_Reference_ID) {
// Handle Posted
// TODO: hardcoded
if (columnName.equalsIgnoreCase("Posted")
|| columnName.equalsIgnoreCase("Processed")
|| columnName.equalsIgnoreCase("Processing")) {
return Boolean.class;
}
// Record_ID
// TODO: hardcoded
else if (columnName.equalsIgnoreCase("Record_ID")) {
return Integer.class;
}
// Reference Table
else if ((DisplayType.Table == displayType || DisplayType.Search == displayType)
&& AD_Reference_ID > 0) {
String sql =
"SELECT c.AD_Reference_ID, c.AD_Reference_Value_ID"
+ " FROM AD_Ref_Table rt"
+ " INNER JOIN AD_Column c ON (c.AD_Column_ID=rt.AD_Key)"
+ " WHERE rt.AD_Reference_ID=?";
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt = DB.prepareStatement(sql, null);
pstmt.setInt(1, AD_Reference_ID);
rs = pstmt.executeQuery();
if (rs.next()) {
displayType = rs.getInt(1);
AD_Reference_ID = rs.getInt(2);
} else {
throw new IllegalStateException(
"Not found AD_Ref_Table/AD_Column - DisplayType="
+ displayType
+ ", AD_Reference_ID="
+ AD_Reference_ID);
}
} catch (SQLException e) {
throw new DBException(e, sql);
} finally {
DB.close(rs, pstmt);
rs = null;
pstmt = null;
}
//
return getClass(
columnName, displayType, AD_Reference_ID); // recursive call with new parameters
} else {
return DisplayType.getClass(displayType, true);
}
}
/**
* Create Window Tabs
*
* @param mWindowVO Window Value Object
* @return true if tabs were created
*/
private static boolean createTabs(GridWindowVO mWindowVO) {
mWindowVO.Tabs = new ArrayList<GridTabVO>();
String sql = GridTabVO.getSQL(mWindowVO.ctx);
int TabNo = 0;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
// create statement
pstmt = DB.prepareStatement(sql, null);
pstmt.setInt(1, mWindowVO.AD_Window_ID);
rs = pstmt.executeQuery();
boolean firstTab = true;
while (rs.next()) {
if (mWindowVO.AD_Table_ID == 0) mWindowVO.AD_Table_ID = rs.getInt("AD_Table_ID");
// Create TabVO
GridTabVO mTabVO =
GridTabVO.create(
mWindowVO,
TabNo,
rs,
mWindowVO.WindowType.equals(WINDOWTYPE_QUERY), // isRO
mWindowVO.WindowType.equals(WINDOWTYPE_TRX)); // onlyCurrentRows
if (mTabVO == null && firstTab) break; // don't continue if first tab is null
if (mTabVO != null) {
if (!mTabVO.IsReadOnly && "N".equals(mWindowVO.IsReadWrite)) mTabVO.IsReadOnly = true;
mWindowVO.Tabs.add(mTabVO);
TabNo++; // must be same as mWindow.getTab(x)
firstTab = false;
}
}
} catch (SQLException e) {
CLogger.get().log(Level.SEVERE, "createTabs", e);
return false;
} finally {
DB.close(rs, pstmt);
rs = null;
pstmt = null;
}
// No Tabs
if (TabNo == 0 || mWindowVO.Tabs.size() == 0) {
CLogger.get()
.log(Level.SEVERE, "No Tabs - AD_Window_ID=" + mWindowVO.AD_Window_ID + " - " + sql);
return false;
}
// Put base table of window in ctx (for VDocAction)
Env.setContext(mWindowVO.ctx, mWindowVO.WindowNo, "BaseTable_ID", mWindowVO.AD_Table_ID);
return true;
} // createTabs
/**
* Invoice Header - DocType. - PaymentRule - temporary Document Context: - DocSubTypeSO -
* HasCharges - (re-sets Business Partner info of required)
*
* @param ctx context
* @param WindowNo window no
* @param mTab tab
* @param mField field
* @param value value
* @return null or error message
*/
public String docType(
Properties ctx, int WindowNo, GridTab mTab, GridField mField, Object value) {
Integer C_DocType_ID = (Integer) value;
if (C_DocType_ID == null || C_DocType_ID.intValue() == 0) return "";
String sql =
"SELECT d.HasCharges,'N',d.IsDocNoControlled," // 1..3
+ "s.CurrentNext, d.DocBaseType, " // 4..5
+ "s.StartNewYear, s.DateColumn, s.AD_Sequence_ID " // 6..8
+ "FROM C_DocType d, AD_Sequence s "
+ "WHERE C_DocType_ID=?" // 1
+ " AND d.DocNoSequence_ID=s.AD_Sequence_ID(+)";
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt = DB.prepareStatement(sql, null);
pstmt.setInt(1, C_DocType_ID.intValue());
rs = pstmt.executeQuery();
if (rs.next()) {
// Charges - Set Context
Env.setContext(ctx, WindowNo, "HasCharges", rs.getString(1));
// DocumentNo
if (rs.getString(3).equals("Y")) {
if ("Y".equals(rs.getString(6))) {
String dateColumn = rs.getString(7);
mTab.setValue(
"DocumentNo",
"<" + MSequence.getPreliminaryNoByYear(mTab, rs.getInt(8), dateColumn, null) + ">");
} else mTab.setValue("DocumentNo", "<" + rs.getString(4) + ">");
}
// DocBaseType - Set Context
String s = rs.getString(5);
Env.setContext(ctx, WindowNo, "DocBaseType", s);
// AP Check & AR Credit Memo
/*if (s.startsWith("AP"))
mTab.setValue("PaymentRule", "S"); // Check*/
if (s.startsWith("AP"))
mTab.setValue("PaymentRule", "P"); // AB 14-09 by default choose On credit
else if (s.endsWith("C")) mTab.setValue("PaymentRule", "P"); // OnCredit
}
} catch (SQLException e) {
log.log(Level.SEVERE, sql, e);
return e.getLocalizedMessage();
} finally {
DB.close(rs, pstmt);
}
return "";
} // docType
/**
* Validate table data and throw exception if any error occur
*
* @param trxName
* @throws DBUniqueConstraintException if not unique data found
*/
public void validateData(String trxName) {
if (!isActive()) {
return;
}
if (!isUnique()) {
return;
}
//
final StringBuffer sqlGroupBy = new StringBuffer();
for (final MIndexColumn c : getColumns()) {
if (sqlGroupBy.length() > 0) {
sqlGroupBy.append(",");
}
sqlGroupBy.append(c.getColumnName());
}
if (sqlGroupBy.length() == 0) {
return;
}
//
final StringBuffer sql = new StringBuffer("SELECT " + sqlGroupBy + " FROM " + getTableName());
final String whereClause = getWhereClause();
if (!Check.isEmpty(whereClause, true)) {
sql.append(" WHERE ").append(whereClause);
}
//
sql.append(" GROUP BY ").append(sqlGroupBy);
sql.append(" HAVING COUNT(1) > 1");
//
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt = DB.prepareStatement(sql.toString(), trxName);
rs = pstmt.executeQuery();
if (rs.next()) {
throw new DBUniqueConstraintException(this);
}
} catch (final SQLException e) {
throw new DBException(e, sql.toString());
} finally {
DB.close(rs, pstmt);
rs = null;
pstmt = null;
}
}
/**
* Find Price List Version and update context
*
* @param M_PriceList_ID price list
* @return M_PriceList_Version_ID price list version
*/
private int findPLV(int M_PriceList_ID) {
Timestamp priceDate = null;
// Sales Order Date
String dateStr = Env.getContext(Env.getCtx(), p_WindowNo, "DateOrdered");
if (dateStr != null && dateStr.length() > 0)
priceDate = Env.getContextAsDate(Env.getCtx(), p_WindowNo, "DateOrdered");
else // Invoice Date
{
dateStr = Env.getContext(Env.getCtx(), p_WindowNo, "DateInvoiced");
if (dateStr != null && dateStr.length() > 0)
priceDate = Env.getContextAsDate(Env.getCtx(), p_WindowNo, "DateInvoiced");
}
// Today
if (priceDate == null) priceDate = new Timestamp(System.currentTimeMillis());
//
log.config("M_PriceList_ID=" + M_PriceList_ID + " - " + priceDate);
int retValue = 0;
String sql =
"SELECT plv.M_PriceList_Version_ID, plv.ValidFrom "
+ "FROM M_PriceList pl, M_PriceList_Version plv "
+ "WHERE pl.M_PriceList_ID=plv.M_PriceList_ID"
+ " AND plv.IsActive='Y'"
+ " AND pl.M_PriceList_ID=? " // 1
+ "ORDER BY plv.ValidFrom DESC";
// find newest one
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt = DB.prepareStatement(sql, null);
pstmt.setInt(1, M_PriceList_ID);
rs = pstmt.executeQuery();
while (rs.next() && retValue == 0) {
Timestamp plDate = rs.getTimestamp(2);
if (!priceDate.before(plDate)) retValue = rs.getInt(1);
}
} catch (SQLException e) {
log.log(Level.SEVERE, sql, e);
} finally {
DB.close(rs, pstmt);
rs = null;
pstmt = null;
}
Env.setContext(Env.getCtx(), p_WindowNo, "M_PriceList_Version_ID", retValue);
return retValue;
} // findPLV
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;
}
/**
* Get active activities count
*
* @return int
*/
public int getActivitiesCount() {
int count = 0;
String sql =
"SELECT count(*) FROM AD_WF_Activity a "
+ "WHERE a.Processed='N' AND a.WFState='OS' AND ("
// Owner of Activity
+ " a.AD_User_ID=?" // #1
// Invoker (if no invoker = all)
+ " OR EXISTS (SELECT * FROM AD_WF_Responsible r WHERE a.AD_WF_Responsible_ID=r.AD_WF_Responsible_ID"
+ " AND COALESCE(r.AD_User_ID,0)=0 AND COALESCE(r.AD_Role_ID,0)=0 AND (a.AD_User_ID=? OR a.AD_User_ID IS NULL))" // #2
// Responsible User
+ " OR EXISTS (SELECT * FROM AD_WF_Responsible r WHERE a.AD_WF_Responsible_ID=r.AD_WF_Responsible_ID"
+ " AND r.AD_User_ID=?)" // #3
// Responsible Role
+ " OR EXISTS (SELECT * FROM AD_WF_Responsible r INNER JOIN AD_User_Roles ur ON (r.AD_Role_ID=ur.AD_Role_ID)"
+ " WHERE a.AD_WF_Responsible_ID=r.AD_WF_Responsible_ID AND ur.AD_User_ID=?))"; // #4
//
// + ") ORDER BY a.Priority DESC, Created";
int AD_User_ID = Env.getAD_User_ID(Env.getCtx());
MRole role = MRole.get(Env.getCtx(), Env.getAD_Role_ID(Env.getCtx()));
sql = role.addAccessSQL(sql, "a", true, false);
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt = DB.prepareStatement(sql, null);
pstmt.setInt(1, AD_User_ID);
pstmt.setInt(2, AD_User_ID);
pstmt.setInt(3, AD_User_ID);
pstmt.setInt(4, AD_User_ID);
rs = pstmt.executeQuery();
if (rs.next()) {
count = rs.getInt(1);
}
} catch (Exception e) {
log.log(Level.SEVERE, sql, e);
} finally {
DB.close(rs, pstmt);
rs = null;
pstmt = null;
}
return count;
}
/** Fill Table */
private Vector<Vector<Object>> fillTable(String sql, int parameter) {
if (log.isLoggable(Level.FINE)) log.fine(sql + "; Parameter=" + parameter);
Vector<Vector<Object>> data = new Vector<Vector<Object>>();
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt = DB.prepareStatement(sql, null);
pstmt.setInt(1, parameter);
rs = pstmt.executeQuery();
while (rs.next()) {
Vector<Object> line = new Vector<Object>(6);
// 0-Name, 1-PriceActual, 2-QtyInvoiced, 3-Discount, 4-DocumentNo, 5-DateInvoiced
line.add(rs.getString(1)); // Name
line.add(rs.getBigDecimal(2)); // Price
line.add(rs.getString(3)); // Currency
line.add(new Double(rs.getDouble(5))); // Qty
BigDecimal discountBD = rs.getBigDecimal(9);
if (discountBD == null) {
double priceList = rs.getDouble(4);
double priceActual = rs.getDouble(2);
if (priceList != 0) {
discountBD = BigDecimal.valueOf((priceList - priceActual) / priceList * 100);
// Rounding:
int precision = MPriceList.getStandardPrecision(Env.getCtx(), rs.getInt(10));
if (discountBD.scale() > precision)
discountBD = discountBD.setScale(precision, RoundingMode.HALF_UP);
} else discountBD = Env.ZERO;
}
line.add(discountBD); // Discount
line.add(rs.getString(7)); // DocNo
line.add(rs.getTimestamp(6)); // Date
line.add(rs.getString(8)); // Org/Warehouse
data.add(line);
}
} catch (SQLException e) {
log.log(Level.SEVERE, sql, e);
} finally {
DB.close(rs, pstmt);
rs = null;
pstmt = null;
}
if (log.isLoggable(Level.FINE)) log.fine("#" + data.size());
return data;
} // fillTable
/**
* Get Active LDAP Server
*
* @return array of Servers
*/
public static MLdapProcessor[] getActive(Properties ctx) {
ArrayList<MLdapProcessor> list = new ArrayList<MLdapProcessor>();
String sql = "SELECT * FROM AD_LdapProcessor WHERE IsActive='Y'";
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt = DB.prepareStatement(sql, null);
rs = pstmt.executeQuery();
while (rs.next()) list.add(new MLdapProcessor(ctx, rs, null));
} catch (Exception e) {
log.error(sql, e);
} finally {
DB.close(rs, pstmt);
rs = null;
pstmt = null;
}
MLdapProcessor[] retValue = new MLdapProcessor[list.size()];
list.toArray(retValue);
return retValue;
} // getActive