Examples with Query org.compiere.model.Query used on opensource projects

Example 91 with Query

use of org.compiere.model.Query in project adempiere by adempiere.

the class QueryTest method testSetClient_ID.

public void testSetClient_ID() throws Exception {
    int AD_Client_ID = Env.getAD_Client_ID(getCtx());
    String sql = "SELECT COUNT(*) FROM C_Invoice WHERE IsActive='Y' AND AD_Client_ID=" + AD_Client_ID;
    int targetCount = DB.getSQLValue(null, sql);
    //
    int count = new Query(getCtx(), "C_Invoice", "1=1", getTrxName()).setOnlyActiveRecords(true).setClient_ID().count();
    assertEquals("Invoice # not match", targetCount, count);
}

Example 92 with Query

use of org.compiere.model.Query in project adempiere by adempiere.

the class QueryTest method testFirstId.

public void testFirstId() throws Exception {
    int id = new Query(getCtx(), "AD_Table", "TableName IN (?,?)", getTrxName()).setParameters(new Object[] { "C_Invoice", "M_InOut" }).setOrderBy("TableName").firstId();
    // C_Invoice
    int expectedId = 318;
    assertEquals("Invalid ID", expectedId, id);
}

Example 93 with Query

use of org.compiere.model.Query in project adempiere by adempiere.

the class QueryTest method testAggregate.

public void testAggregate() throws Exception {
    final int AD_Client_ID = Env.getAD_Client_ID(getCtx());
    final String sqlFrom = "FROM C_InvoiceLine WHERE IsActive='Y' AND AD_Client_ID=" + AD_Client_ID;
    final Query query = new Query(getCtx(), "C_InvoiceLine", null, getTrxName()).setOnlyActiveRecords(true).setClient_ID();
    //
    // Test COUNT:
    assertEquals("COUNT not match", DB.getSQLValueBDEx(getTrxName(), "SELECT COUNT(*) " + sqlFrom), query.aggregate(null, Query.AGGREGATE_COUNT));
    //
    // Test SUM:
    assertEquals("SUM not match", DB.getSQLValueBDEx(getTrxName(), "SELECT SUM(LineNetAmt+TaxAmt) " + sqlFrom), query.aggregate("LineNetAmt+TaxAmt", Query.AGGREGATE_SUM));
    //
    // Test MIN:
    assertEquals("MIN not match", DB.getSQLValueBDEx(getTrxName(), "SELECT MIN(LineNetAmt) " + sqlFrom), query.aggregate("LineNetAmt", Query.AGGREGATE_MIN));
    //
    // Test MAX:
    assertEquals("MAX not match", DB.getSQLValueBDEx(getTrxName(), "SELECT MAX(LineNetAmt) " + sqlFrom), query.aggregate("LineNetAmt", Query.AGGREGATE_MAX));
    //
    // Test aggregate (String) - FR [ 2726447 ]
    assertEquals("MAX not match (String)", DB.getSQLValueStringEx(getTrxName(), "SELECT MAX(Description) " + sqlFrom), (String) query.aggregate("Description", Query.AGGREGATE_MAX, String.class));
    //
    // Test aggregate (Timestamp) - FR [ 2726447 ]
    assertEquals("MAX not match (Timestamp)", DB.getSQLValueTSEx(getTrxName(), "SELECT MAX(Updated) " + sqlFrom), (Timestamp) query.aggregate("Updated", Query.AGGREGATE_MAX, Timestamp.class));
    //
    // Test Exception : No Aggregate Function defined
    assertExceptionThrown("No Aggregate Function defined", DBException.class, new Runnable() {

        public void run() {
            query.aggregate("*", null);
        }
    });
    //
    // Test Exception : No Expression defined
    assertExceptionThrown("No Expression defined", DBException.class, new Runnable() {

        public void run() {
            query.aggregate(null, Query.AGGREGATE_SUM);
        }
    });
}

Example 94 with Query

use of org.compiere.model.Query in project adempiere by adempiere.

the class QueryTest method testCount.

public void testCount() throws Exception {
    int count = new Query(getCtx(), "AD_Table", "TableName IN (?,?)", getTrxName()).setParameters(new Object[] { "C_Invoice", "M_InOut" }).setOrderBy("TableName").count();
    assertEquals("Invalid count", 2, count);
}

Example 95 with Query

use of org.compiere.model.Query in project adempiere by adempiere.

the class QueryTest method testOnlySelection.

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>();
    // AD_Element_ID=102 => AD_Client_ID
    elements.add(102);
    // AD_Element_ID=104 => AD_Column_ID
    elements.add(104);
    DB.executeUpdateEx("DELETE FROM T_Selection WHERE AD_PInstance_ID=" + AD_PInstance_ID, getTrxName());
    DB.createT_Selection(AD_PInstance_ID, elements, getTrxName());
    // some dummy where clause
    String whereClause = "1=1";
    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]);
    }
}