/**
  * 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();
  }
Exemple #3
0
 /**
  * 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;
  }
Exemple #6
0
  /**
   * 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
Exemple #11
0
 /**
  * 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();
  }
Exemple #13
0
  @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
Exemple #15
0
  /**
   * 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);
    }
  }
Exemple #17
0
  /**
   * 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
Exemple #21
0
  /**
   * 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
Exemple #23
0
  /**
   * 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;
  }
Exemple #27
0
 /**
  * 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
Exemple #28
0
 /**
  * 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
Exemple #29
0
  @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