/** * 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