public function testGroupBy()
{
$objItems = Project::QueryArray(QQ::All(), QQ::Clause(QQ::GroupBy(QQN::Project()->Id), QQ::Count(QQN::Project()->PersonAsTeamMember->PersonId, 'team_member_count')));
$this->assertEqual(sizeof($objItems), 4, "4 projects found");
$this->assertEqual($objItems[0]->Name, "ACME Website Redesign", "Project " . $objItems[0]->Name . " found");
$this->assertEqual($objItems[0]->GetVirtualAttribute('team_member_count'), 5, "5 team members found for project " . $objItems[0]->Name);
$this->assertEqual($objItems[1]->Name, "State College HR System", "Project " . $objItems[1]->Name . " found");
$this->assertEqual($objItems[1]->GetVirtualAttribute('team_member_count'), 6, "6 team members found for project " . $objItems[1]->Name);
$this->assertEqual($objItems[2]->Name, "Blueman Industrial Site Architecture", "Project " . $objItems[2]->Name . " found");
$this->assertEqual($objItems[2]->GetVirtualAttribute('team_member_count'), 5, "5 team members found for project " . $objItems[2]->Name);
}
public function testAlias3()
{
$emptySelect = QQ::Select();
$emptySelect->SetSkipPrimaryKey(true);
$nVoyel = QQ::Alias(QQN::Person()->ProjectAsManager->Milestone, 'voyel');
$nConson = QQ::Alias(QQN::Person()->ProjectAsManager->Milestone, 'conson');
$objPersonArray = Person::QueryArray(QQ::IsNotNull($nConson->Id), QQ::Clause(QQ::Expand($nVoyel, QQ::In($nVoyel->Name, array('Milestone A', 'Milestone E', 'Milestone I')), $emptySelect), QQ::Expand($nConson, QQ::NotIn($nConson->Name, array('Milestone A', 'Milestone E', 'Milestone I')), $emptySelect), QQ::GroupBy(QQN::Person()->Id), QQ::Minimum($nVoyel->Name, 'min_voyel'), QQ::Minimum($nConson->Name, 'min_conson'), QQ::Expand(QQN::Person()->ProjectAsManager, null, $emptySelect), QQ::Minimum(QQN::Person()->ProjectAsManager->Id, 'dummy'), QQ::Select(QQN::Person()->FirstName, QQN::Person()->LastName)));
$this->assertEquals(3, sizeof($objPersonArray));
$obj = $this->verifyObjectPropertyHelper($objPersonArray, 'LastName', 'Doe');
$this->assertNull($obj->GetVirtualAttribute('min_voyel'));
$this->assertEquals('Milestone F', $obj->GetVirtualAttribute('min_conson'));
$obj = $this->verifyObjectPropertyHelper($objPersonArray, 'LastName', 'Ho');
$this->assertEquals('Milestone E', $obj->GetVirtualAttribute('min_voyel'));
$this->assertEquals('Milestone D', $obj->GetVirtualAttribute('min_conson'));
$obj = $this->verifyObjectPropertyHelper($objPersonArray, 'LastName', 'Wolfe');
$this->assertEquals('Milestone A', $obj->GetVirtualAttribute('min_voyel'));
$this->assertEquals('Milestone B', $obj->GetVirtualAttribute('min_conson'));
}
public function testHaving()
{
$objItems = Project::QueryArray(QQ::All(), QQ::Clause(QQ::GroupBy(QQN::Project()->Id), QQ::Count(QQN::Project()->PersonAsTeamMember->PersonId, 'team_member_count'), QQ::Having(QQ::SubSql('COUNT({1}) > 5', QQN::Project()->PersonAsTeamMember->PersonId)), QQ::OrderBy(QQN::Project()->Id)));
$this->assertEqual(sizeof($objItems), 2, "2 projects found");
$this->assertEqual($objItems[0]->Name, "State College HR System", "Project " . $objItems[0]->Name . " found");
$this->assertEqual($objItems[0]->GetVirtualAttribute('team_member_count'), 6, "6 team members found for project " . $objItems[0]->Name);
}
_p('<li>' . $objPerson->FirstName . ' ' . $objPerson->LastName . '</li>', false);
}
?>
</ul>
<h2>Select all Projects and the Count of Team Members (if applicable)</h2>
<p><em>GROUP BY in action</em></p>
<ul>
<?php
$objProjectArray = Project::QueryArray(QQ::All(), QQ::Clause(QQ::GroupBy(QQN::Project()->Id), QQ::Count(QQN::Project()->PersonAsTeamMember->PersonId, 'team_member_count')));
foreach ($objProjectArray as $objProject) {
_p('<li>' . $objProject->Name . ' (' . $objProject->GetVirtualAttribute('team_member_count') . ' team members)' . '</li>', false);
}
?>
</ul>
<h2>Select all Projects with more than 5 team members. </h2>
<p><em>Using a Having clause to further limit group functions</em></p>
<ul>
<?php
$objProjectArray = Project::QueryArray(QQ::All(), QQ::Clause(QQ::GroupBy(QQN::Project()->Id), QQ::Count(QQN::Project()->PersonAsTeamMember->PersonId, 'team_member_count'), QQ::Having(QQ::SubSql('COUNT({1}) > 5', QQN::Project()->PersonAsTeamMember->PersonId))));
foreach ($objProjectArray as $objProject) {
_p($objProject->Name . ' (' . $objProject->GetVirtualAttribute('team_member_count') . ' team members)');
_p('<br/>', false);
}
?>
</ul>
</div>
<?php
require '../includes/footer.inc.php';
public function __get($strName)
{
switch ($strName) {
case 'Votes':
$mixResult = NarroSuggestionVote::QuerySingle(QQ::Equal(QQN::NarroSuggestionVote()->SuggestionId, $this->SuggestionId), array(QQ::Sum(QQN::NarroSuggestionVote()->VoteValue, 'votes'), QQ::GroupBy(QQN::NarroSuggestionVote()->SuggestionId)));
if ($mixResult instanceof NarroSuggestionVote) {
return $mixResult->GetVirtualAttribute('votes');
} else {
return 0;
}
default:
try {
return parent::__get($strName);
break;
} catch (QCallerException $objExc) {
$objExc->IncrementOffset();
throw $objExc;
}
}
}
public function testVirtualAttributeAliases()
{
$clauses = [QQ::GroupBy(QQN::Project()->ProjectStatusTypeId), QQ::Sum(QQN::Project()->Budget, 'Budget Amount'), QQ::Expand(QQ::Virtual('Balance', QQ::Func('SUM', QQ::Sub(QQN::Project()->Budget, QQN::Project()->Spent))))];
$cond = QQ::Equal(QQN::Project()->ProjectStatusTypeId, ProjectStatusType::Open);
$objProject = Project::QuerySingle($cond, $clauses);
$amount1 = $objProject->GetVirtualAttribute('Budget Amount');
$this->assertEquals(83000, $amount1);
$amount2 = $objProject->GetVirtualAttribute('Balance');
$this->assertEquals(5599.5, $amount2);
}
public function dtgLanguage_Bind()
{
if ($this->txtSearch->Text != '') {
$objSearchCondition = QQ::Like(QQN::NarroLanguage()->LanguageName, sprintf('%%%s%%', $this->txtSearch->Text));
} else {
$objSearchCondition = QQ::All();
}
switch ($this->lstFilter->SelectedValue) {
/**
* Only active
*/
case 1:
$objFilterCondition = QQ::AndCondition($objSearchCondition, QQ::Equal(QQN::NarroLanguage()->Active, 1));
break;
/**
* 0 - show all
*/
/**
* 0 - show all
*/
default:
$objFilterCondition = $objSearchCondition;
}
// Because we want to enable pagination AND sorting, we need to setup the $objClauses array to send to LoadAll()
// Remember! We need to first set the TotalItemCount, which will affect the calcuation of LimitClause below
$this->dtgLanguage->TotalItemCount = NarroLanguage::QueryCount($objFilterCondition);
// Setup the $objClauses Array
$objClauses = array(QQ::Expand(QQ::Virtual('last_translation', QQ::SubSql('SELECT MAX(created) FROM narro_suggestion WHERE language_id={1}', QQN::NarroLanguage()->LanguageId))), QQ::Count(QQN::NarroLanguage()->NarroSuggestionAsLanguage->SuggestionId, 'translations_count'), QQ::GroupBy(QQN::NarroLanguage()->LanguageId));
// If a column is selected to be sorted, and if that column has a OrderByClause set on it, then let's add
// the OrderByClause to the $objClauses array
if ($objClause = $this->dtgLanguage->OrderByClause) {
array_push($objClauses, $objClause);
}
// Add the LimitClause information, as well
if ($objClause = $this->dtgLanguage->LimitClause) {
array_push($objClauses, $objClause);
}
// Set the DataSource to be the array of all NarroLanguage objects, given the clauses above
$this->dtgLanguage->DataSource = NarroLanguage::QueryArray($objFilterCondition, $objClauses);
QApplication::ExecuteJavaScript('highlight_datagrid();');
}
$objPersonArray = Person::QueryArray(QQ::All(), QQ::Clause(QQ::OrderBy(QQN::Person()->LastName, QQN::Person()->FirstName)));
foreach ($objPersonArray as $objPerson) {
_p($objPerson->FirstName . ' ' . $objPerson->LastName);
_p('<br/>', false);
}
?>
<h3>Select all People, Ordered by Last Name then First Name, Limited to the first 4 results</h3>
<?php
$objPersonArray = Person::QueryArray(QQ::All(), QQ::Clause(QQ::OrderBy(QQN::Person()->LastName, QQN::Person()->FirstName), QQ::LimitInfo(4)));
foreach ($objPersonArray as $objPerson) {
_p($objPerson->FirstName . ' ' . $objPerson->LastName);
_p('<br/>', false);
}
?>
<h3>Select all Projects and the Count of Team Members (if applicable)</h3>
<?php
$objProjectArray = Project::QueryArray(QQ::All(), QQ::Clause(QQ::GroupBy(QQN::Project()->Id), QQ::Count(QQN::Project()->PersonAsTeamMember->PersonId, 'team_member_count')));
foreach ($objProjectArray as $objProject) {
_p($objProject->Name . ' (' . $objProject->GetVirtualAttribute('team_member_count') . ' team members)');
_p('<br/>', false);
}
?>
<?php
require __INCLUDES__ . '/examples/footer.inc.php';
}
?>
<h2>Example 3: Managers having one least a project with a conson milestone, and for each manager, the first voyel milestone and the first conson one</h2>
<?php
$emptySelect = QQ::Select();
$emptySelect->SetSkipPrimaryKey(true);
$nVoyel = QQ::Alias(QQN::Person()->ProjectAsManager->Milestone, 'voyel');
$nConson = QQ::Alias(QQN::Person()->ProjectAsManager->Milestone, 'conson');
$objPersonArray = Person::QueryArray(QQ::IsNotNull($nConson->Id), QQ::Clause(QQ::Expand(QQN::Person()->ProjectAsManager, null, $emptySelect), QQ::Expand($nVoyel, QQ::In($nVoyel->Name, array('Milestone A', 'Milestone E', 'Milestone I')), $emptySelect), QQ::Expand($nConson, QQ::NotIn($nConson->Name, array('Milestone A', 'Milestone E', 'Milestone I')), $emptySelect), QQ::GroupBy(QQN::Person()->Id), QQ::Minimum($nVoyel->Name, 'min_voyel'), QQ::Minimum($nConson->Name, 'min_conson'), QQ::Expand(QQN::Person()->ProjectAsManager, null, $emptySelect), QQ::Minimum(QQN::Person()->ProjectAsManager->Id, 'dummy'), QQ::Select(QQN::Person()->FirstName, QQN::Person()->LastName)));
foreach ($objPersonArray as $objManager) {
_p($objManager->FirstName . ' ' . $objManager->LastName . " (" . $objManager->GetVirtualAttribute('min_voyel') . ', ' . $objManager->GetVirtualAttribute('min_conson') . ")");
_p('<br/>', false);
}
?>
<h2>Example 4: Projects with, for each one, the "min" city from the addresses containing 'r' and the "min" city from the addresses NOT containing 'r' </h2>
<?php
$nWithR = QQ::Alias(QQN::Project()->PersonAsTeamMember->Person->Address, 'with_r');
$nWithoutR = QQ::Alias(QQN::Project()->PersonAsTeamMember->Person->Address, 'without_r');
$objProjectArray = Project::QueryArray(QQ::All(), QQ::Clause(QQ::Expand($nWithR, QQ::Like($nWithR->Street, '%r%')), QQ::Expand($nWithoutR, QQ::NotLike($nWithoutR->Street, '%r%')), QQ::GroupBy(QQN::Project()->Id), QQ::Minimum($nWithR->City, 'min_city_r'), QQ::Minimum($nWithoutR->City, 'min_city_wor')));
foreach ($objProjectArray as $objProject) {
_p($objProject->Name . " (" . $objProject->GetVirtualAttribute('min_city_r') . ', ' . $objProject->GetVirtualAttribute('min_city_wor') . ")");
_p('<br/>', false);
}
QApplication::$Database[1]->OutputProfiling();
?>
</div>
<?php
require '../includes/footer.inc.php';
public static function CountByFileName($strFileName, $intFilter, $objExtraCondition = null)
{
if (!is_object($objExtraCondition)) {
$objExtraCondition = QQ::All();
}
if (trim($strFileName) == '') {
$objSearchCondition = QQ::All();
} elseif (preg_match("/^'.*'\$/", $strFileName)) {
$objSearchCondition = QQ::Equal(QQN::NarroContextInfo()->Context->File->FileName, substr($strFileName, 1, -1));
} else {
$objSearchCondition = QQ::Like(QQN::NarroContextInfo()->Context->File->FileName, '%' . $strFileName . '%');
}
switch ($intFilter) {
case NarroTextListForm::SHOW_UNTRANSLATED_TEXTS:
$objFilterCondition = QQ::Equal(QQN::NarroContextInfo()->HasSuggestions, 0);
break;
case NarroTextListForm::SHOW_APPROVED_TEXTS:
$objFilterCondition = QQ::IsNotNull(QQN::NarroContextInfo()->ValidSuggestionId);
break;
case NarroTextListForm::SHOW_TEXTS_THAT_REQUIRE_APPROVAL:
$objFilterCondition = QQ::AndCondition(QQ::IsNull(QQN::NarroContextInfo()->ValidSuggestionId), QQ::Equal(QQN::NarroContextInfo()->HasSuggestions, 1));
break;
default:
// no filters
$objFilterCondition = QQ::All();
}
$intContextCount = NarroContextInfo::QueryCount(QQ::AndCondition($objSearchCondition, $objFilterCondition, $objExtraCondition), array(QQ::GroupBy(QQN::NarroContextInfo()->ContextId)));
return $intContextCount;
}
public function __construct($strDate, $objParentObject, NarroProject $objProject = null, $strControlId = null)
{
// Call the Parent
try {
parent::__construct($objParentObject, $strControlId);
} catch (QCallerException $objExc) {
$objExc->IncrementOffset();
throw $objExc;
}
$this->strTemplate = dirname(__FILE__) . '/' . __CLASS__ . '.tpl.php';
$this->blnAutoRenderChildren = true;
// Tabs
$this->tabTop = new QTabs($this);
$pnlTranslators = new NarroUserDataGrid($this->tabTop);
$pnlTranslators->CssClass = 'datagrid';
$colUsername = $pnlTranslators->MetaAddColumn(QQN::NarroUser()->Username);
$colUsername->HtmlEntities = false;
$colUsername->Html = '<?=NarroLink::UserProfile($_ITEM->UserId, $_ITEM->Username)?>';
$colCnt = new QDataGridColumn(t('Translations'));
$colCnt->Html = '<?=$_ITEM->GetVirtualAttribute("suggestion_cnt")?>';
$pnlTranslators->SortColumnIndex = 1;
$pnlTranslators->ShowHeader = false;
$pnlTranslators->SortDirection = 1;
$pnlTranslators->AddColumn($colCnt);
$pnlTranslators->AdditionalConditions = QQ::AndCondition(QQ::GreaterOrEqual(QQN::NarroUser()->NarroSuggestionAsUser->Created, $strDate), QQ::Equal(QQN::NarroUser()->NarroSuggestionAsUser->LanguageId, QApplication::GetLanguageId()), QQ::NotEqual(QQN::NarroUser()->UserId, NarroUser::ANONYMOUS_USER_ID), $objProject ? QQ::Equal(QQN::NarroUser()->NarroSuggestionAsUser->Text->NarroContextAsText->ProjectId, $objProject->ProjectId) : QQ::All());
$pnlTranslators->AdditionalClauses = array(QQ::Count(QQN::NarroUser()->NarroSuggestionAsUser->SuggestionId, 'suggestion_cnt'), QQ::GroupBy(QQN::NarroUser()->UserId));
$pnlReviewers = new NarroUserDataGrid($this->tabTop);
$pnlReviewers->CssClass = 'datagrid';
$colUsername = $pnlReviewers->MetaAddColumn(QQN::NarroUser()->Username);
$colUsername->HtmlEntities = false;
$colUsername->Html = '<?=NarroLink::UserProfile($_ITEM->UserId, $_ITEM->Username)?>';
$colCnt = new QDataGridColumn(t('Reviewers'));
$colCnt->Html = '<?=$_ITEM->GetVirtualAttribute("suggestion_cnt")?>';
$pnlReviewers->SortColumnIndex = 1;
$pnlReviewers->ShowHeader = false;
$pnlReviewers->SortDirection = 1;
$pnlReviewers->AddColumn($colCnt);
$pnlReviewers->AdditionalConditions = QQ::AndCondition(QQ::GreaterOrEqual(QQN::NarroUser()->NarroContextInfoAsValidatorUser->Modified, $strDate), QQ::Equal(QQN::NarroUser()->NarroContextInfoAsValidatorUser->LanguageId, QApplication::GetLanguageId()), QQ::NotEqual(QQN::NarroUser()->UserId, NarroUser::ANONYMOUS_USER_ID), $objProject ? QQ::Equal(QQN::NarroUser()->NarroContextInfoAsValidatorUser->Context->ProjectId, $objProject->ProjectId) : QQ::All());
$pnlReviewers->AdditionalClauses = array(QQ::Count(QQN::NarroUser()->NarroContextInfoAsValidatorUser->ValidSuggestionId, 'suggestion_cnt'), QQ::GroupBy(QQN::NarroUser()->UserId));
$pnlVoters = new NarroUserDataGrid($this->tabTop);
$pnlVoters->CssClass = 'datagrid';
$colUsername = $pnlVoters->MetaAddColumn(QQN::NarroUser()->Username);
$colUsername->HtmlEntities = false;
$colUsername->Html = '<?=NarroLink::UserProfile($_ITEM->UserId, $_ITEM->Username)?>';
$colCnt = new QDataGridColumn(t('Reviewers'));
$colCnt->Html = '<?=$_ITEM->GetVirtualAttribute("suggestion_cnt")?>';
$pnlVoters->SortColumnIndex = 1;
$pnlVoters->ShowHeader = false;
$pnlVoters->SortDirection = 1;
$pnlVoters->AddColumn($colCnt);
$pnlVoters->AdditionalConditions = QQ::AndCondition(QQ::GreaterOrEqual(QQN::NarroUser()->NarroSuggestionVoteAsUser->Created, $strDate), QQ::Equal(QQN::NarroUser()->NarroSuggestionVoteAsUser->Suggestion->LanguageId, QApplication::GetLanguageId()), QQ::NotEqual(QQN::NarroUser()->UserId, NarroUser::ANONYMOUS_USER_ID), $objProject ? QQ::Equal(QQN::NarroUser()->NarroSuggestionVoteAsUser->Suggestion->Text->NarroContextAsText->ProjectId, $objProject->ProjectId) : QQ::All());
$pnlVoters->AdditionalClauses = array(QQ::Count(QQN::NarroUser()->NarroSuggestionVoteAsUser->SuggestionId, 'suggestion_cnt'), QQ::GroupBy(QQN::NarroUser()->UserId));
$pnlComments = new NarroUserDataGrid($this->tabTop);
$pnlComments->CssClass = 'datagrid';
$colUsername = $pnlComments->MetaAddColumn(QQN::NarroUser()->Username);
$colUsername->HtmlEntities = false;
$colUsername->Html = '<?=NarroLink::UserProfile($_ITEM->UserId, $_ITEM->Username)?>';
$colCnt = new QDataGridColumn(t('Reviewers'));
$colCnt->Html = '<?=$_ITEM->GetVirtualAttribute("suggestion_cnt")?>';
$pnlComments->SortColumnIndex = 1;
$pnlComments->ShowHeader = false;
$pnlComments->SortDirection = 1;
$pnlComments->AddColumn($colCnt);
$pnlComments->AdditionalConditions = QQ::AndCondition(QQ::GreaterOrEqual(QQN::NarroUser()->NarroTextCommentAsUser->Created, $strDate), QQ::Equal(QQN::NarroUser()->NarroTextCommentAsUser->LanguageId, QApplication::GetLanguageId()), QQ::NotEqual(QQN::NarroUser()->UserId, NarroUser::ANONYMOUS_USER_ID));
$pnlComments->AdditionalClauses = array(QQ::Count(QQN::NarroUser()->NarroTextCommentAsUser->TextCommentId, 'suggestion_cnt'), QQ::GroupBy(QQN::NarroUser()->UserId));
$this->tabTop->Headers = array(t('Translators'), t('Reviewers'), t('Voters'), t('Comments'));
}
