@Test public void testGroupSelectionFilter() throws Exception { DataSet result = dataSetManager.lookupDataSet( DataSetLookupFactory.newDataSetLookupBuilder() .dataset(EXPENSE_REPORTS) .filter(COLUMN_AMOUNT, FilterFactory.greaterThan(500)) .group(COLUMN_DEPARTMENT).select("Engineering") .group(COLUMN_CITY).select("Westford") .buildLookup()); //printDataSet(result); assertThat(result.getRowCount()).isEqualTo(1); assertDataSetValue(result, 0, 0, "26.00"); }
@Test public void testGroupByQuarter() throws Exception { DataSet result = dataSetManager.lookupDataSet( DataSetLookupFactory.newDataSetLookupBuilder() .dataset(EXPENSE_REPORTS) .group(COLUMN_DATE).fixed(QUARTER, true).select("1") .buildLookup()); //printDataSet(result); assertThat(result.getRowCount()).isEqualTo(14); }
@Test public void testFilterAfterSelect() throws Exception { DataSet result = dataSetManager.lookupDataSet( DataSetLookupFactory.newDataSetLookupBuilder() .dataset(EXPENSE_REPORTS) .group(COLUMN_DEPARTMENT).select("Support") .group(COLUMN_DATE).dynamic(DateIntervalType.YEAR, true).select("2012") .column(COLUMN_ID) .column(COLUMN_DEPARTMENT) .column(COLUMN_DATE) .buildLookup()); //printDataSet(result); assertThat(result.getRowCount()).isEqualTo(4); }
@Test public void testGroupSelectionFilter() throws Exception { DataSet result = dataSetManager.lookupDataSet( DataSetFactory.newDataSetLookupBuilder() .dataset(EXPENSE_REPORTS) .filter(COLUMN_AMOUNT, FilterFactory.greaterThan(500)) .group(COLUMN_DEPARTMENT).select("Engineering") .group(COLUMN_CITY).select("Westford") .buildLookup()); //printDataSet(result); assertThat(result.getRowCount()).isEqualTo(1); assertDataSetValue(result, 0, 0, "26.00"); }
@Test public void testFilterAfterSelect() throws Exception { DataSet result = dataSetManager.lookupDataSet( DataSetLookupFactory.newDataSetLookupBuilder() .dataset(EXPENSE_REPORTS) .group(COLUMN_DEPARTMENT).select("Support") .group(COLUMN_DATE).dynamic(DateIntervalType.YEAR, true).select("2012") .column(COLUMN_ID) .column(COLUMN_DEPARTMENT) .column(COLUMN_DATE) .buildLookup()); //printDataSet(result); assertThat(result.getRowCount()).isEqualTo(4); }
@Test public void testThreeNestedLevels() throws Exception { DataSet result = dataSetManager.lookupDataSet( DataSetLookupFactory.newDataSetLookupBuilder() .dataset(EXPENSE_REPORTS) .group(COLUMN_DEPARTMENT).select("Services", "Engineering") .group(COLUMN_CITY).select("Madrid", "Barcelona") .group(COLUMN_DATE).fixed(DateIntervalType.MONTH, true) .column(COLUMN_DATE) .column(COLUMN_AMOUNT, SUM, "total") .buildLookup()); //printDataSet(result); assertDataSetValues(result, dataSetFormatter, new String[][]{ {"1", "0.00"}, {"2", "0.00"}, {"3", "0.00"}, {"4", "0.00"}, {"5", "0.00"}, {"6", "911.11"}, {"7", "800.80"}, {"8", "152.25"}, {"9", "300.00"}, {"10", "340.34"}, {"11", "900.10"}, {"12", "1,220.45"} }, 0); }
@Test public void testThreeNestedLevels() throws Exception { DataSet result = dataSetManager.lookupDataSet( DataSetFactory.newDataSetLookupBuilder() .dataset(EXPENSE_REPORTS) .group(COLUMN_DEPARTMENT).select("Services", "Engineering") .group(COLUMN_CITY).select("Madrid", "Barcelona") .group(COLUMN_DATE).fixed(DateIntervalType.MONTH, true) .column(COLUMN_DATE) .column(COLUMN_AMOUNT, SUM, "total") .buildLookup()); //printDataSet(result); assertDataSetValues(result, dataSetFormatter, new String[][] { {"1", "0.00"}, {"2", "0.00"}, {"3", "0.00"}, {"4", "0.00"}, {"5", "0.00"}, {"6", "911.11"}, {"7", "800.80"}, {"8", "152.25"}, {"9", "300.00"}, {"10", "340.34"}, {"11", "900.10"}, {"12", "1,220.45"} }, 0); }
@Test public void testGroupByQuarter() throws Exception { DataSet result = dataSetManager.lookupDataSet( DataSetFactory.newDataSetLookupBuilder() .dataset(EXPENSE_REPORTS) .group(COLUMN_DATE).fixed(QUARTER, true).select("1") .buildLookup()); //printDataSet(result); assertThat(result.getRowCount()).isEqualTo(14); }
@Test public void testFilterWithNullEntries() throws Exception { // Insert a null entry into the dataset DataSet expensesDataSet = dataSetManager.getDataSet(EXPENSE_REPORTS); int column = expensesDataSet.getColumnIndex(expensesDataSet.getColumnById(COLUMN_DEPARTMENT)); expensesDataSet.setValueAt(0, column, null); // Group by department in order to force the indexing of the result DataSet result = dataSetManager.lookupDataSet( DataSetLookupFactory.newDataSetLookupBuilder() .dataset(EXPENSE_REPORTS) .group(COLUMN_DEPARTMENT) .column(COLUMN_DEPARTMENT) .column(COLUMN_AMOUNT, SUM) .sort(COLUMN_DEPARTMENT, SortOrder.ASCENDING) .buildLookup()); assertThat(result.getRowCount()).isEqualTo(6); result = dataSetManager.lookupDataSet( DataSetLookupFactory.newDataSetLookupBuilder() .dataset(EXPENSE_REPORTS) .group(COLUMN_DEPARTMENT).select("Engineering") .sort(COLUMN_DEPARTMENT, SortOrder.ASCENDING) .buildLookup()); assertThat(result.getRowCount()).isEqualTo(18); }
@Test public void testNestedGroupFromMultipleSelection() throws Exception { DataSet result = dataSetManager.lookupDataSet( DataSetFactory.newDataSetLookupBuilder() .dataset(EXPENSE_REPORTS) .group(COLUMN_DEPARTMENT, "Department").select("Services", "Engineering") .group(COLUMN_CITY, "City") .column(COLUMN_CITY) .column(COUNT, "Occurrences") .column(COLUMN_AMOUNT, MIN, "min") .column(COLUMN_AMOUNT, MAX, "max") .column(COLUMN_AMOUNT, AVERAGE, "average") .column(COLUMN_AMOUNT, SUM, "total") .sort(COLUMN_CITY, "asc") .buildLookup()); //printDataSet(result); assertDataSetValues(result, dataSetFormatter, new String[][] { {"Barcelona", "6.00", "120.35", "1,100.10", "485.52", "2,913.14"}, {"Brno", "4.00", "159.01", "800.24", "364.86", "1,459.45"}, {"London", "3.00", "333.45", "868.45", "535.40", "1,606.20"}, {"Madrid", "2.00", "800.80", "911.11", "855.96", "1,711.91"}, {"Raleigh", "4.00", "209.55", "401.40", "284.38", "1,137.53"}, {"Westford", "5.00", "1.10", "600.34", "265.29", "1,326.43"} }, 0); }
@Test public void testNestedGroupFromMultipleSelection() throws Exception { DataSet result = dataSetManager.lookupDataSet( DataSetLookupFactory.newDataSetLookupBuilder() .dataset(EXPENSE_REPORTS) .group(COLUMN_DEPARTMENT, "Department").select("Services", "Engineering") .group(COLUMN_CITY, "City") .column(COLUMN_CITY) .column(COUNT, "Occurrences") .column(COLUMN_AMOUNT, MIN, "min") .column(COLUMN_AMOUNT, MAX, "max") .column(COLUMN_AMOUNT, AVERAGE, "average") .column(COLUMN_AMOUNT, SUM, "total") .sort(COLUMN_CITY, "asc") .buildLookup()); //printDataSet(result); assertDataSetValues(result, dataSetFormatter, new String[][]{ {"Barcelona", "6.00", "120.35", "1,100.10", "485.52", "2,913.14"}, {"Brno", "4.00", "159.01", "800.24", "364.86", "1,459.45"}, {"London", "3.00", "333.45", "868.45", "535.40", "1,606.20"}, {"Madrid", "2.00", "800.80", "911.11", "855.96", "1,711.91"}, {"Raleigh", "4.00", "209.55", "401.40", "284.38", "1,137.53"}, {"Westford", "5.00", "1.10", "600.34", "265.29", "1,326.43"} }, 0); }
@Test public void testDataSetLookupMarshalling() { DataSetLookup original = DataSetLookupFactory.newDataSetLookupBuilder() .dataset("mydataset") .filter(OR(notEqualsTo("department", "IT"), greaterOrEqualsTo("amount", 100d))) .filter("department", notEqualsTo("IT")) .filter("amount", between(100d, 200d)) .filter("date", greaterThan(jsonMarshaller.parseDate("2018-01-01 00:00:00"))) .filter("country", isNull()) .group("department").select("Services") .group("date", "year").dynamic(DateIntervalType.YEAR, true) .column("date") .column("amount", AggregateFunctionType.SUM, "total") .sort("date", SortOrder.ASCENDING) .buildLookup(); JsonObject _jsonObj = jsonMarshaller.toJson(original); assertNotNull(_jsonObj.toString()); DataSetLookup unmarshalled = jsonMarshaller.fromJson(_jsonObj); assertEquals(unmarshalled, original); }
@Test public void testFilterWithNullEntries() throws Exception { // Insert a null entry into the dataset DataSet expensesDataSet = dataSetManager.getDataSet(EXPENSE_REPORTS); int column = expensesDataSet.getColumnIndex(expensesDataSet.getColumnById(COLUMN_DEPARTMENT)); expensesDataSet.setValueAt(0, column, null); // Group by department in order to force the indexing of the result DataSet result = dataSetManager.lookupDataSet( DataSetLookupFactory.newDataSetLookupBuilder() .dataset(EXPENSE_REPORTS) .group(COLUMN_DEPARTMENT) .column(COLUMN_DEPARTMENT) .column(COLUMN_AMOUNT, SUM) .sort(COLUMN_DEPARTMENT, SortOrder.ASCENDING) .buildLookup()); assertThat(result.getRowCount()).isEqualTo(6); result = dataSetManager.lookupDataSet( DataSetLookupFactory.newDataSetLookupBuilder() .dataset(EXPENSE_REPORTS) .group(COLUMN_DEPARTMENT).select("Engineering") .sort(COLUMN_DEPARTMENT, SortOrder.ASCENDING) .buildLookup()); assertThat(result.getRowCount()).isEqualTo(18); }
@Test public void testDataSetLookupMarshalling() { DataSetLookup original = DataSetFactory.newDataSetLookupBuilder() .dataset("mydataset") .filter(OR(notEqualsTo("department", "IT"), greaterOrEqualsTo("amount", 100d))) .filter("department", notEqualsTo("IT")) .filter("amount", between(100d, 200d)) .filter("date", greaterThan(jsonMarshaller.parseDate("2018-01-01 00:00:00"))) .filter("country", isNull()) .group("department").select("Services") .group("date", "year").dynamic(DateIntervalType.YEAR, true) .column("date") .column("amount", AggregateFunctionType.SUM, "total") .sort("date", SortOrder.ASCENDING) .buildLookup(); JsonObject _jsonObj = jsonMarshaller.toJson(original); assertNotNull(_jsonObj.toString()); DataSetLookup unmarshalled = jsonMarshaller.fromJson(_jsonObj); assertEquals(unmarshalled, original); }