/**
* Test to confirm joins are working correctly when ordering by an attribute on item.
* Testing when the join adapter has already added a display attribute from a related account
*/
public function testOrderByNoteCreatedDateTimeAndConfirmJoinsAreCorrect()
{
$q = DatabaseCompatibilityUtil::getQuote();
$joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('Note');
$selectQueryAdapter = new RedBeanModelSelectQueryAdapter();
$builder = new DisplayAttributesReportQueryBuilder($joinTablesAdapter, $selectQueryAdapter);
$displayAttribute = new DisplayAttributeForReportForm('NotesModule', 'Note', Report::TYPE_ROWS_AND_COLUMNS);
$displayAttribute->attributeIndexOrDerivedType = 'Account__activityItems__Inferred___name';
//First build the display attributes from clauses
$builder->makeQueryContent(array($displayAttribute));
$builder = new OrderBysReportQueryBuilder($joinTablesAdapter);
$orderBy = new OrderByForReportForm('NotesModule', 'Note', Report::TYPE_ROWS_AND_COLUMNS);
$orderBy->attributeIndexOrDerivedType = 'createdDateTime';
//Second build the order by from clauses
$builder->makeQueryContent(array($orderBy));
$fromTablesAndAliases = $joinTablesAdapter->getFromTablesAndAliases();
$this->assertEquals('activity', $fromTablesAndAliases[0]['tableAliasName']);
$this->assertEquals('note', $fromTablesAndAliases[0]['onTableAliasName']);
$this->assertEquals('ownedsecurableitem1', $fromTablesAndAliases[1]['tableAliasName']);
$this->assertEquals('activity', $fromTablesAndAliases[1]['onTableAliasName']);
$this->assertEquals('securableitem1', $fromTablesAndAliases[2]['tableAliasName']);
$this->assertEquals('ownedsecurableitem1', $fromTablesAndAliases[2]['onTableAliasName']);
$this->assertEquals('item1', $fromTablesAndAliases[3]['tableAliasName']);
$this->assertEquals('securableitem1', $fromTablesAndAliases[3]['onTableAliasName']);
}
public function testDisplayCalculationInferredRelationModelAttributeWithCastingHintToNotCastDowButAlsoWithFullCastDown()
{
$q = DatabaseCompatibilityUtil::getQuote();
$joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('Meeting');
$selectQueryAdapter = new RedBeanModelSelectQueryAdapter();
$builder = new DisplayAttributesReportQueryBuilder($joinTablesAdapter, $selectQueryAdapter);
$displayAttribute = new DisplayAttributeForReportForm('MeetingsModule', 'Meeting', Report::TYPE_SUMMATION);
$displayAttribute->attributeIndexOrDerivedType = 'Account__activityItems__Inferred___createdDateTime__Maximum';
$displayAttribute2 = new DisplayAttributeForReportForm('MeetingsModule', 'Meeting', Report::TYPE_SUMMATION);
$displayAttribute2->attributeIndexOrDerivedType = 'Account__activityItems__Inferred___name';
$content = $builder->makeQueryContent(array($displayAttribute, $displayAttribute2));
$compareContent = "select max({$q}item{$q}.{$q}createddatetime{$q}) col0, " . "{$q}account{$q}.{$q}id{$q} accountid ";
$this->assertEquals($compareContent, $content);
$leftTablesAndAliases = $joinTablesAdapter->getLeftTablesAndAliases();
$fromTablesAndAliases = $joinTablesAdapter->getFromTablesAndAliases();
$this->assertEquals(1, $joinTablesAdapter->getFromTableJoinCount());
$this->assertEquals(5, $joinTablesAdapter->getLeftTableJoinCount());
$this->assertEquals('activity_item', $leftTablesAndAliases[0]['tableAliasName']);
$this->assertEquals('activity', $leftTablesAndAliases[0]['onTableAliasName']);
$this->assertEquals('item', $leftTablesAndAliases[1]['tableAliasName']);
$this->assertEquals('activity_item', $leftTablesAndAliases[1]['onTableAliasName']);
$this->assertEquals('securableitem', $leftTablesAndAliases[2]['tableAliasName']);
$this->assertEquals('item', $leftTablesAndAliases[2]['onTableAliasName']);
$this->assertEquals('ownedsecurableitem', $leftTablesAndAliases[3]['tableAliasName']);
$this->assertEquals('securableitem', $leftTablesAndAliases[3]['onTableAliasName']);
$this->assertEquals('account', $leftTablesAndAliases[4]['tableAliasName']);
$this->assertEquals('ownedsecurableitem', $leftTablesAndAliases[4]['onTableAliasName']);
}
/**
* @param RedBeanModelJoinTablesQueryAdapter $joinTablesAdapter
* @param RedBeanModelSelectQueryAdapter $selectQueryAdapter
*/
protected function makeDisplayAttributes(RedBeanModelJoinTablesQueryAdapter $joinTablesAdapter, RedBeanModelSelectQueryAdapter $selectQueryAdapter)
{
$builder = new DisplayAttributesReportQueryBuilder($joinTablesAdapter, $selectQueryAdapter, $this->report->getCurrencyConversionType());
$builder->makeQueryContent($this->resolveDisplayAttributes());
}
public function testCreatedDateTimeFilterWithInferredRelationModelAttributeAsDisplay()
{
$q = DatabaseCompatibilityUtil::getQuote();
$selectQueryAdapter = new RedBeanModelSelectQueryAdapter();
$joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('Meeting');
$builder = new DisplayAttributesReportQueryBuilder($joinTablesAdapter, $selectQueryAdapter, Report::CURRENCY_CONVERSION_TYPE_ACTUAL);
$displayAttribute = new DisplayAttributeForReportForm('MeetingsModule', 'Meeting', Report::TYPE_ROWS_AND_COLUMNS);
$displayAttribute->attributeIndexOrDerivedType = 'Account__activityItems__Inferred___name';
$builder->makeQueryContent(array($displayAttribute));
$builder = new FiltersReportQueryBuilder($joinTablesAdapter, '1');
$filter = new FilterForReportForm('MeetingsModule', 'Meeting', Report::TYPE_ROWS_AND_COLUMNS);
$filter->attributeIndexOrDerivedType = 'createdDateTime';
$filter->valueType = MixedDateTypesSearchFormAttributeMappingRules::TYPE_BETWEEN;
$filter->value = '1991-05-05';
$filter->secondValue = '1991-06-05';
$filter->availableAtRunTime = true;
$content = $builder->makeQueryContent(array($filter));
$compareContent = "((({$q}item1{$q}.{$q}createddatetime{$q} >= '1991-05-05 00:00:00') " . "and ({$q}item1{$q}.{$q}createddatetime{$q} <= '1991-06-05 23:59:59')))";
$this->assertEquals($compareContent, $content);
$this->assertEquals(4, $joinTablesAdapter->getFromTableJoinCount());
$this->assertEquals(5, $joinTablesAdapter->getLeftTableJoinCount());
$builder = new FiltersReportQueryBuilder($joinTablesAdapter, 'not 1');
$content = $builder->makeQueryContent(array($filter));
$compareContent = "(not (({$q}item1{$q}.{$q}createddatetime{$q} >= '1991-05-05 00:00:00') " . "and ({$q}item1{$q}.{$q}createddatetime{$q} <= '1991-06-05 23:59:59')))";
$this->assertEquals($compareContent, $content);
}
protected function makeSqlQueryForRowTotals()
{
$selectQueryAdapter = new RedBeanModelSelectQueryAdapter();
$moduleClassName = $this->report->getModuleClassName();
$modelClassName = $moduleClassName::getPrimaryModelName();
$joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter($modelClassName);
$builder = new DisplayAttributesReportQueryBuilder($joinTablesAdapter, $selectQueryAdapter, $this->report->getCurrencyConversionType());
$builder->makeQueryContent($this->getDisplayAttributesForGrandTotals());
$where = $this->makeFiltersContent($joinTablesAdapter);
$orderBy = null;
$builder = new GroupBysReportQueryBuilder($joinTablesAdapter);
$groupBy = $builder->makeQueryContent($this->getYAxisGroupBys());
$offset = null;
$limit = null;
return SQLQueryUtil::makeQuery($modelClassName::getTableName($modelClassName), $selectQueryAdapter, $joinTablesAdapter, $offset, $limit, $where, $orderBy, $groupBy);
}