/** PaySelect changed - load Bank */
public void loadPaySelectInfo(int C_PaySelection_ID) {
// load Banks from PaySelectLine
m_C_BankAccount_ID = -1;
String sql =
"SELECT ps.C_BankAccount_ID, b.Name || ' ' || ba.AccountNo," // 1..2
+ " c.ISO_Code, CurrentBalance, ba.PaymentExportClass " // 3..5
+ "FROM C_PaySelection ps"
+ " INNER JOIN C_BankAccount ba ON (ps.C_BankAccount_ID=ba.C_BankAccount_ID)"
+ " INNER JOIN C_Bank b ON (ba.C_Bank_ID=b.C_Bank_ID)"
+ " INNER JOIN C_Currency c ON (ba.C_Currency_ID=c.C_Currency_ID) "
+ "WHERE ps.C_PaySelection_ID=? AND ps.Processed='Y' AND ba.IsActive='Y'";
try {
PreparedStatement pstmt = DB.prepareStatement(sql, null);
pstmt.setInt(1, C_PaySelection_ID);
ResultSet rs = pstmt.executeQuery();
if (rs.next()) {
m_C_BankAccount_ID = rs.getInt(1);
bank = rs.getString(2);
currency = rs.getString(3);
balance = rs.getBigDecimal(4);
m_PaymentExportClass = rs.getString(5);
} else {
m_C_BankAccount_ID = -1;
bank = "";
currency = "";
balance = Env.ZERO;
m_PaymentExportClass = null;
log.log(Level.SEVERE, "No active BankAccount for C_PaySelection_ID=" + C_PaySelection_ID);
}
rs.close();
pstmt.close();
} catch (SQLException e) {
log.log(Level.SEVERE, sql, e);
}
} // loadPaySelectInfo
private static ElementBean getTrl(String sql) {
PreparedStatement pstmt = DB.prepareStatement(sql, null);
ElementBean retElementBean = null;
try {
ResultSet rs = pstmt.executeQuery();
if (rs.next()) {
retElementBean = new ElementBean();
retElementBean.setColumnName(rs.getString(1));
retElementBean.setName(rs.getString(2));
retElementBean.setPrintName(rs.getString(3));
retElementBean.setDescription(rs.getString(4));
retElementBean.setHelp(rs.getString(5));
} else {
return null;
}
rs.close();
} catch (Exception ex) {
log.severe("Could retrieve element translation with sql: " + sql);
} finally {
try {
pstmt.close();
} catch (Exception ex) {
}
}
return retElementBean;
}
/**
* Get Accessible Goals
*
* @param ctx context
* @return array of goals
*/
public static MGoal[] getGoals(Ctx ctx) {
ArrayList<MGoal> list = new ArrayList<MGoal>();
String sql = "SELECT * FROM PA_Goal WHERE IsActive='Y' " + "ORDER BY SeqNo";
sql =
MRole.getDefault(ctx, false)
.addAccessSQL(sql, "PA_Goal", false, true); // RW to restrict Access
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt = DB.prepareStatement(sql, (Trx) 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.log(Level.SEVERE, sql, e);
} finally {
DB.closeStatement(pstmt);
DB.closeResultSet(rs);
}
MGoal[] retValue = new MGoal[list.size()];
list.toArray(retValue);
return retValue;
} // getGoals
/**
* 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 Details
*
* @return array of details
*/
public MCommissionDetail[] getDetails() {
String sql = "SELECT * FROM C_CommissionDetail WHERE C_CommissionAmt_ID=?";
ArrayList<MCommissionDetail> list = new ArrayList<MCommissionDetail>();
PreparedStatement pstmt = null;
try {
pstmt = DB.prepareStatement(sql, get_TrxName());
pstmt.setInt(1, getC_CommissionAmt_ID());
ResultSet rs = pstmt.executeQuery();
while (rs.next()) list.add(new MCommissionDetail(getCtx(), rs, get_TrxName()));
rs.close();
pstmt.close();
pstmt = null;
} catch (Exception e) {
log.error(sql, e);
}
try {
if (pstmt != null) pstmt.close();
pstmt = null;
} catch (Exception e) {
pstmt = null;
}
// Convert
MCommissionDetail[] retValue = new MCommissionDetail[list.size()];
list.toArray(retValue);
return retValue;
} // getDetails
/**
* Find all the year records in a Calendar, it need not be a standard period (used in MRP)
*
* @param C_Calendar_ID calendar
* @param ctx context
* @param trx trx
* @return MYear[]
*/
public static MYear[] getAllYearsInCalendar(int C_Calendar_ID, Ctx ctx, Trx trx) {
List<MYear> years = new ArrayList<MYear>();
String sql = "SELECT * FROM C_Year WHERE " + "IsActive='Y' AND C_Calendar_ID = ? ";
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt = DB.prepareStatement(sql, trx);
pstmt.setInt(1, C_Calendar_ID);
rs = pstmt.executeQuery();
while (rs.next()) years.add(new MYear(ctx, rs, trx));
} catch (Exception e) {
s_log.log(Level.SEVERE, sql, e);
} finally {
DB.closeResultSet(rs);
DB.closeStatement(pstmt);
}
MYear[] retValue = new MYear[years.size()];
years.toArray(retValue);
return retValue;
}
/**
* 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
void dynDepartament() {
KeyNamePair cat = (KeyNamePair) categoryCombo.getSelectedItem();
departmentCombo.removeActionListener(this);
departmentCombo.removeAllItems();
String sql = "SELECT XX_VMR_DEPARTMENT_ID, VALUE||'-'||NAME " + " FROM XX_VMR_DEPARTMENT ";
if (cat != null && cat.getKey() != -1) {
sql += " WHERE XX_VMR_CATEGORY_ID = " + cat.getKey();
}
sql += " ORDER BY VALUE||'-'||NAME ";
sql = MRole.getDefault().addAccessSQL(sql, "", MRole.SQL_NOTQUALIFIED, MRole.SQL_RO);
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt = DB.prepareStatement(sql, null);
rs = pstmt.executeQuery();
departmentCombo.addItem(new KeyNamePair(-1, null));
while (rs.next()) {
departmentCombo.addItem(new KeyNamePair(rs.getInt(1), rs.getString(2)));
}
rs.close();
pstmt.close();
departmentCombo.addActionListener(this);
departmentCombo.setEnabled(true);
departmentCombo.setEditable(true);
} catch (SQLException e) {
log.log(Level.SEVERE, sql, e);
} finally {
DB.closeResultSet(rs);
DB.closeStatement(pstmt);
}
}
/**
* 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
/**
* 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
/**
* 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 Table Name
*
* @param ctx context
* @return table name
*/
public String getTableName(Properties ctx) {
if (m_tableName == null) {
String sql = "SELECT TableName FROM AD_Table WHERE AD_Table_ID=?";
PreparedStatement pstmt = null;
try {
pstmt = DB.prepareStatement(sql, null);
pstmt.setInt(1, getAD_Table_ID());
ResultSet rs = pstmt.executeQuery();
if (rs.next()) {
m_tableName = rs.getString(1);
}
rs.close();
pstmt.close();
pstmt = null;
} catch (Exception e) {
log.error(sql, e);
}
try {
if (pstmt != null) pstmt.close();
pstmt = null;
} catch (Exception e) {
pstmt = null;
}
// Get Clear Text
String realName = Msg.translate(ctx, m_tableName + "_ID");
if (!realName.equals(m_tableName + "_ID")) m_tableName = realName;
}
return m_tableName;
} // getTableName
/**
* Returns the previous period
*
* @param period MPeriod
* @param periodCount Count
* @param trx trx
* @param ctx Ctx
* @return MPeriod
*/
public static MPeriod getPreviousPeriod(MPeriod period, Ctx ctx, Trx trx) {
MPeriod newPeriod = null;
String sql =
"SELECT * FROM C_Period WHERE "
+ "C_Period.IsActive='Y' AND PeriodType='S' "
+ "AND C_Period.C_Year_ID IN "
+ "(SELECT C_Year_ID FROM C_Year WHERE C_Year.C_Calendar_ID = ? ) "
+ "AND ((C_Period.C_Year_ID * 1000) + C_Period.PeriodNo) "
+ " < ((? * 1000) + ?) ORDER BY C_Period.C_Year_ID DESC, C_Period.PeriodNo DESC";
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt = DB.prepareStatement(sql, trx);
pstmt.setInt(1, period.getC_Calendar_ID());
pstmt.setInt(2, period.getC_Year_ID());
pstmt.setInt(3, period.getPeriodNo());
rs = pstmt.executeQuery();
if (rs.next()) newPeriod = new MPeriod(ctx, rs, trx);
} catch (Exception e) {
s_log.log(Level.SEVERE, sql, e);
} finally {
DB.closeResultSet(rs);
DB.closeStatement(pstmt);
}
return newPeriod;
}
/**
* 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
/**
* Find the periods in a calendar year it need not be a standard period (used in MRP)
*
* @param C_Year_ID Year
* @param periodType Period Type
* @param ctx context
* @param trx trx
* @return MPeriod[]
*/
public static MPeriod[] getAllPeriodsInYear(int C_Year_ID, String periodType, Ctx ctx, Trx trx) {
List<MPeriod> periods = new ArrayList<MPeriod>();
String sql = "SELECT * FROM C_Period WHERE IsActive='Y'";
sql = sql + " AND C_Year_ID = ?";
if (periodType != null) sql = sql + " AND PeriodType = ? ";
sql = sql + " order by StartDate ";
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt = DB.prepareStatement(sql, trx);
pstmt.setInt(1, C_Year_ID);
if (periodType != null) pstmt.setString(2, periodType);
rs = pstmt.executeQuery();
while (rs.next()) periods.add(new MPeriod(ctx, rs, trx));
} catch (Exception e) {
s_log.log(Level.SEVERE, sql, e);
} finally {
DB.closeResultSet(rs);
DB.closeStatement(pstmt);
}
MPeriod[] retValue = new MPeriod[periods.size()];
periods.toArray(retValue);
return retValue;
}
/**
* ************************************************************************ Create Missing
* Document Types
*
* @param ctx context
* @param AD_Client_ID client
* @param sp server process
* @param trx transaction
*/
public static void createDocumentTypes(Ctx ctx, int AD_Client_ID, SvrProcess sp, Trx trx) {
s_log.info("AD_Client_ID=" + AD_Client_ID);
String sql =
"SELECT rl.Value, rl.Name "
+ "FROM AD_Ref_List rl "
+ "WHERE rl.AD_Reference_ID=183"
+ " AND rl.IsActive='Y' AND NOT EXISTS "
+ " (SELECT * FROM C_DocType dt WHERE dt.AD_Client_ID=? AND rl.Value=dt.DocBaseType)";
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt = DB.prepareStatement(sql, trx);
pstmt.setInt(1, AD_Client_ID);
rs = pstmt.executeQuery();
while (rs.next()) {
String name = rs.getString(2);
String value = rs.getString(1);
s_log.config(name + "=" + value);
MDocType dt = new MDocType(ctx, value, name, trx);
if (dt.save()) {
if (sp != null) sp.addLog(0, null, null, name);
else s_log.fine(name);
} else {
if (sp != null) sp.addLog(0, null, null, "Not created: " + name);
else s_log.warning("Not created: " + name);
}
}
} catch (Exception e) {
s_log.log(Level.SEVERE, sql, e);
} finally {
DB.closeResultSet(rs);
DB.closeStatement(pstmt);
}
} // createDocumentTypes
/**
* @author Fernando de O Moraes (fernando.moraes @ faire.com.br)
* @return
*/
private MCity[] getCCity() {
ArrayList<MCity> list = new ArrayList<MCity>();
String sql = "SELECT * FROM C_City WHERE C_Region_ID=? ORDER BY Name";
try {
PreparedStatement stmt = DB.prepareStatement(sql, null);
stmt.setInt(1, m_location.getC_Region_ID());
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
MCity r = new MCity(Env.getCtx(), rs, null);
list.add(r);
}
rs.close();
stmt.close();
} catch (SQLException e) {
log.log(Level.SEVERE, sql, e);
}
MCity[] retValue = new MCity[list.size()];
list.toArray(retValue);
// Arrays.sort(retValue);
return retValue;
}
/**
* 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
/**
* 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()]);
}
/**
* Get Project Phases
*
* @return Array of phases
*/
public MProjectPhase[] getPhases() {
ArrayList<MProjectPhase> list = new ArrayList<MProjectPhase>();
String sql = "SELECT * FROM C_ProjectPhase WHERE C_Project_ID=? ORDER BY SeqNo";
PreparedStatement pstmt = null;
try {
pstmt = DB.prepareStatement(sql, get_TrxName());
pstmt.setInt(1, getC_Project_ID());
ResultSet rs = pstmt.executeQuery();
while (rs.next()) list.add(new MProjectPhase(getCtx(), rs, get_TrxName()));
rs.close();
pstmt.close();
pstmt = null;
} catch (SQLException ex) {
log.log(Level.SEVERE, sql, ex);
}
try {
if (pstmt != null) pstmt.close();
} catch (SQLException ex1) {
}
pstmt = null;
//
MProjectPhase[] retValue = new MProjectPhase[list.size()];
list.toArray(retValue);
return retValue;
} // getPhases
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
/**
* Before Save
*
* @param newRecord new
* @return
*/
@Override
protected boolean beforeSave(boolean newRecord) {
setName(get_ValueAsString("Name").toUpperCase());
String SQL =
"SELECT * FROM XX_VMR_LongCharacteristic "
+ "WHERE XX_VMR_Department_ID="
+ getXX_VMR_Department_ID()
+ " AND XX_VMR_Line_ID="
+ getXX_VMR_Line_ID()
+ " AND XX_VMR_Section_ID="
+ getXX_VMR_Section_ID()
+ " AND isactive='Y' AND name = '"
+ getName()
+ "'";
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt = DB.prepareStatement(SQL, null);
rs = pstmt.executeQuery();
if (rs.next()) {
log.saveError("", Msg.getMsg(getCtx(), "XX_CharacteristicRepeated"));
return false;
}
} catch (Exception e) {
e.getMessage();
}
return true;
} // beforeSave
/**
* 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();
}
/**
* 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;
}
/**
* 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_Trx());
pstmt.setInt(1, getPA_Goal_ID());
rs = pstmt.executeQuery();
while (rs.next()) list.add(new MGoalRestriction(getCtx(), rs, get_Trx()));
} catch (Exception e) {
log.log(Level.SEVERE, sql, e);
} finally {
DB.closeStatement(pstmt);
DB.closeResultSet(rs);
}
//
m_restrictions = new MGoalRestriction[list.size()];
list.toArray(m_restrictions);
return m_restrictions;
} // getRestrictions
/**
* Find first Year Period of DateAcct based on Client Calendar
*
* @param ctx context
* @param C_Calendar_ID calendar
* @param DateAcct date
* @return active first Period
*/
public static MPeriod getFirstInYear(Ctx ctx, int C_Calendar_ID, Timestamp DateAcct) {
MPeriod retValue = null;
String sql =
"SELECT * "
+ "FROM C_Period "
+ "WHERE C_Year_ID IN "
+ "(SELECT p.C_Year_ID "
+ "FROM C_Year y"
+ " INNER JOIN C_Period p ON (y.C_Year_ID=p.C_Year_ID) "
+ "WHERE y.C_Calendar_ID=?"
+ " AND ? BETWEEN StartDate AND EndDate)"
+ " AND IsActive='Y' AND PeriodType='S' "
+ "ORDER BY StartDate";
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt = DB.prepareStatement(sql, (Trx) null);
pstmt.setInt(1, C_Calendar_ID);
pstmt.setTimestamp(2, DateAcct);
rs = pstmt.executeQuery();
if (rs.next()) // first only
retValue = new MPeriod(ctx, rs, null);
} catch (SQLException e) {
s_log.log(Level.SEVERE, sql, e);
} finally {
DB.closeStatement(pstmt);
DB.closeResultSet(rs);
}
return retValue;
} // getFirstInYear
@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);
}
public ArrayList<KeyNamePair> getPaySelectionData() {
ArrayList<KeyNamePair> data = new ArrayList<KeyNamePair>();
log.config("");
int AD_Client_ID = Env.getAD_Client_ID(Env.getCtx());
// Load PaySelect
String sql =
"SELECT C_PaySelection_ID, Name || ' - ' || TotalAmt FROM C_PaySelection "
+ "WHERE AD_Client_ID=? AND Processed='Y' AND IsActive='Y'"
+ "ORDER BY PayDate DESC";
try {
PreparedStatement pstmt = DB.prepareStatement(sql, null);
pstmt.setInt(1, AD_Client_ID);
ResultSet rs = pstmt.executeQuery();
//
while (rs.next()) {
KeyNamePair pp = new KeyNamePair(rs.getInt(1), rs.getString(2));
data.add(pp);
}
rs.close();
pstmt.close();
} catch (SQLException e) {
log.log(Level.SEVERE, sql, e);
}
return data;
}