/**
* 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 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();
}
/**
* 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
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
private AttributesImpl createWorkflowAccessBinding(
AttributesImpl atts, int workflow_id, int role_id) {
String sql = null;
String name = null;
atts.clear();
sql = "SELECT Name FROM AD_Workflow WHERE AD_Workflow_ID=?";
name = DB.getSQLValueString(null, sql, workflow_id);
atts.addAttribute("", "", "workflowname", "CDATA", name);
sql = "SELECT Name FROM AD_Role WHERE AD_Role_ID=?";
name = DB.getSQLValueString(null, sql, role_id);
atts.addAttribute("", "", "rolename", "CDATA", name);
sql =
"SELECT isActive FROM AD_Workflow_Access WHERE AD_Workflow_ID="
+ workflow_id
+ " and AD_Role_ID=?";
String TrueFalse = DB.getSQLValueString(null, sql, role_id);
atts.addAttribute("", "", "isActive", "CDATA", TrueFalse);
sql =
"SELECT isReadWrite FROM AD_Workflow_Access WHERE AD_Workflow_ID="
+ workflow_id
+ " and AD_Role_ID=?";
String isReadWrite = DB.getSQLValueString(null, sql, role_id);
atts.addAttribute("", "", "isReadWrite", "CDATA", isReadWrite);
return atts;
}
/**
* 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;
}
/**
* 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()]);
}
private void createPInstance() {
DB.saveConstraints();
try {
DB.getConstraints().setOnlyAllowedTrxNamePrefixes(false);
final IADPInstanceDAO adPInstanceDAO = Services.get(IADPInstanceDAO.class);
this.pinstance =
adPInstanceDAO.createAD_PInstance(helper.getCtx(), processId, tableId, recordId);
//
// Add parameters:
final List<ProcessInfoParameter> piParams = new ArrayList<>();
for (Map.Entry<String, Object> e : parameters.entrySet()) {
final String name = e.getKey();
final Object value = e.getValue();
if (value == null) {
continue;
}
piParams.add(ProcessInfoParameter.ofValueObject(name, value));
}
adPInstanceDAO.saveParameterToDB(pinstance.getAD_PInstance_ID(), piParams);
} finally {
DB.restoreConstraints();
}
}
/**
* 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
/**
* 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
/** 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();
}
@Test
public void testOnlySelection() throws Exception {
// Get one AD_PInstance_ID
int AD_PInstance_ID = DB.getSQLValueEx(null, "SELECT MAX(AD_PInstance_ID) FROM AD_PInstance");
assertTrue(AD_PInstance_ID > 0);
// Create selection list
List<Integer> elements = new ArrayList<Integer>();
elements.add(102); // AD_Element_ID=102 => AD_Client_ID
elements.add(104); // AD_Element_ID=104 => AD_Column_ID
DB.executeUpdateEx(
"DELETE FROM T_Selection WHERE AD_PInstance_ID=" + AD_PInstance_ID, getTrxName());
DB.createT_Selection(AD_PInstance_ID, elements, getTrxName());
String whereClause = "1=1"; // some dummy where clause
int[] ids =
new Query(getCtx(), X_AD_Element.Table_Name, whereClause, getTrxName())
.setOnlySelection(AD_PInstance_ID)
.setOrderBy(X_AD_Element.COLUMNNAME_AD_Element_ID)
.getIDs();
assertEquals("Resulting number of elements differ", elements.size(), ids.length);
for (int i = 0; i < elements.size(); i++) {
int expected = elements.get(i);
assertEquals("Element " + i + " not equals", expected, ids[i]);
}
}
/**
* 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
/**
* 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;
}
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);
}
}
/**
* 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;
}
/**
* 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 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
/**
* 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
/**
* 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
/**
* ************************************************************************ 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
/**
* See the comment in {@link #updateHeaderTax()}.
*
* @param orderId
*/
private static void updateHeader0(final int orderId) {
// Update Order Header: TotalLines
{
final String sql =
"UPDATE C_Order i"
+ " SET TotalLines="
+ "(SELECT COALESCE(SUM(LineNetAmt),0) FROM C_OrderLine il WHERE i.C_Order_ID=il.C_Order_ID) "
+ "WHERE C_Order_ID="
+ orderId;
final int no = DB.executeUpdateEx(sql, ITrx.TRXNAME_ThreadInherited);
if (no != 1) {
new AdempiereException("Updating TotalLines failed for C_Order_ID=" + orderId);
}
}
// Update Order Header: GrandTotal
{
final String sql =
"UPDATE C_Order i "
+ " SET GrandTotal=TotalLines+"
// SUM up C_OrderTax.TaxAmt only for those lines which does not have Tax Included
+ "(SELECT COALESCE(SUM(TaxAmt),0) FROM C_OrderTax it WHERE i.C_Order_ID=it.C_Order_ID AND it.IsActive='Y' AND it.IsTaxIncluded='N') "
+ "WHERE C_Order_ID="
+ orderId;
final int no = DB.executeUpdateEx(sql, ITrx.TRXNAME_ThreadInherited);
if (no != 1) {
new AdempiereException("Updating GrandTotal failed for C_Order_ID=" + orderId);
}
}
}
/**
* 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
/**
* After Save
*
* @param newRecord new
* @param success success
* @return success
*/
@Override
protected boolean afterSave(boolean newRecord, boolean success) {
// also used for afterDelete
String sql =
"UPDATE M_AttributeSet mas"
+ " SET IsInstanceAttribute='Y' "
+ "WHERE M_AttributeSet_ID= ? "
+ " AND IsInstanceAttribute='N'"
+ " AND (IsSerNo='Y' OR IsLot='Y' OR IsGuaranteeDate='Y'"
+ " OR EXISTS (SELECT * FROM M_AttributeUse mau"
+ " INNER JOIN M_Attribute ma ON (mau.M_Attribute_ID=ma.M_Attribute_ID) "
+ "WHERE mau.M_AttributeSet_ID=mas.M_AttributeSet_ID"
+ " AND mau.IsActive='Y' AND ma.IsActive='Y'"
+ " AND ma.IsInstanceAttribute='Y')"
+ ")";
int no = DB.executeUpdate(get_Trx(), sql, getM_AttributeSet_ID());
if (no != 0) log.fine("afterSave - Set Instance Attribute");
//
sql =
"UPDATE M_AttributeSet mas"
+ " SET IsInstanceAttribute='N' "
+ "WHERE M_AttributeSet_ID=? "
+ " AND IsInstanceAttribute='Y'"
+ " AND IsSerNo='N' AND IsLot='N' AND IsGuaranteeDate='N'"
+ " AND NOT EXISTS (SELECT * FROM M_AttributeUse mau"
+ " INNER JOIN M_Attribute ma ON (mau.M_Attribute_ID=ma.M_Attribute_ID) "
+ "WHERE mau.M_AttributeSet_ID=mas.M_AttributeSet_ID"
+ " AND mau.IsActive='Y' AND ma.IsActive='Y'"
+ " AND ma.IsInstanceAttribute='Y')";
no = DB.executeUpdate(get_Trx(), sql, getM_AttributeSet_ID());
if (no != 0) log.fine("afterSave - Reset Instance Attribute");
return success;
} // afterSave
/**
* 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);
}
/**
* 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
