/**
* Searching Many To Many on a custom field (dropdown)
*/
public function testManyManyCustomFieldSearch()
{
$quote = DatabaseCompatibilityUtil::getQuote();
$searchAttributeData = array();
$searchAttributeData['clauses'] = array(1 => array('attributeName' => 'opportunities', 'relatedModelData' => array('attributeName' => 'stage', 'relatedAttributeName' => 'value', 'operatorType' => 'oneOf', 'value' => array(0 => 'something'))));
$searchAttributeData['structure'] = '1';
//Build the query 'where' and 'joins'. Confirm they are as expected
$joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('Contact');
$where = ModelDataProviderUtil::makeWhere('Contact', $searchAttributeData, $joinTablesAdapter);
$compareWhere = "({$quote}customfield{$quote}.{$quote}value{$quote} IN('something'))";
$this->assertEquals($compareWhere, $where);
$this->assertEquals(0, $joinTablesAdapter->getFromTableJoinCount());
$this->assertEquals(3, $joinTablesAdapter->getLeftTableJoinCount());
$leftTables = $joinTablesAdapter->getLeftTablesAndAliases();
$this->assertEquals('contact_opportunity', $leftTables[0]['tableName']);
$this->assertEquals('opportunity', $leftTables[1]['tableName']);
$this->assertEquals('customfield', $leftTables[2]['tableName']);
$this->assertTrue($joinTablesAdapter->getSelectDistinct());
//Now test that the subsetSQL query produced is correct.
$subsetSql = Contact::makeSubsetOrCountSqlQuery('contact', $joinTablesAdapter, 1, 5, $where, null, false, $joinTablesAdapter->getSelectDistinct());
$compareSubsetSql = "select distinct {$quote}contact{$quote}.{$quote}id{$quote} id ";
$compareSubsetSql .= "from {$quote}contact{$quote} ";
$compareSubsetSql .= "left join {$quote}contact_opportunity{$quote} on ";
$compareSubsetSql .= "{$quote}contact_opportunity{$quote}.{$quote}contact_id{$quote} = {$quote}contact{$quote}.{$quote}id{$quote} ";
$compareSubsetSql .= "left join {$quote}opportunity{$quote} on ";
$compareSubsetSql .= "{$quote}opportunity{$quote}.{$quote}id{$quote} = {$quote}contact_opportunity{$quote}.{$quote}opportunity_id{$quote} ";
$compareSubsetSql .= "left join {$quote}customfield{$quote} on ";
$compareSubsetSql .= "{$quote}customfield{$quote}.{$quote}id{$quote} = {$quote}opportunity{$quote}.{$quote}stage_customfield_id{$quote} ";
$compareSubsetSql .= "where " . $compareWhere . ' ';
$compareSubsetSql .= 'limit 5 offset 1';
$this->assertEquals($compareSubsetSql, $subsetSql);
//Make sure the sql runs properly.
$data = Contact::getSubset($joinTablesAdapter, 0, 5, $where, null, null, $joinTablesAdapter->getSelectDistinct());
}
protected static function getGroupBySqlPart($tableName, $columnName)
{
assert('$tableName');
assert('$columnName');
$quote = DatabaseCompatibilityUtil::getQuote();
return "{$quote}{$tableName}{$quote}.{$quote}{$columnName}{$quote}";
}
public function testRun()
{
$quote = DatabaseCompatibilityUtil::getQuote();
$super = User::getByUsername('super');
Yii::app()->user->userModel = $super;
$box = EmailBox::resolveAndGetByName(EmailBox::NOTIFICATIONS_NAME);
$folder = EmailFolder::getByBoxAndType($box, EmailFolder::TYPE_SENT);
//Create 2 sent notifications, and set one with a date over a week ago (8 days ago) for the modifiedDateTime
$emailMessage = EmailMessageTestHelper::createDraftSystemEmail('My Email Message', $super);
$emailMessage->folder = $folder;
$saved = $emailMessage->save();
$this->assertTrue($saved);
$modifiedDateTime = DateTimeUtil::convertTimestampToDbFormatDateTime(time() - 60 * 60 * 24 * 8);
$sql = "Update item set modifieddatetime = '" . $modifiedDateTime . "' where id = " . $emailMessage->getClassId('Item');
ZurmoRedBean::exec($sql);
$emailMessage2 = EmailMessageTestHelper::createDraftSystemEmail('My Email Message 2', $super);
$emailMessage2->folder = $folder;
$saved = $emailMessage2->save();
$this->assertTrue($saved);
$this->assertEquals(2, EmailMessage::getCount());
$job = new ClearSentNotificationsEmailJob();
$this->assertTrue($job->run());
$emailMessages = EmailMessage::getAll();
$this->assertEquals(1, count($emailMessages));
$this->assertEquals($emailMessage2->id, $emailMessages[0]->id);
}
protected static function makeSqlQuery($searchAttributeData)
{
$quote = DatabaseCompatibilityUtil::getQuote();
$where = null;
$selectDistinct = false;
$autoresponderTableName = Autoresponder::getTableName('Autoresponder');
$autoresponderItemTableName = AutoresponderItem::getTableName('AutoresponderItem');
$emailMessageTableName = EmailMessage::getTableName('EmailMessage');
$sentDateTimeColumnName = EmailMessage::getColumnNameByAttribute('sentDateTime');
$joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('Autoresponder');
$selectQueryAdapter = new RedBeanModelSelectQueryAdapter($selectDistinct);
$queuedEmailsSelectPart = "sum(CASE WHEN {$quote}{$emailMessageTableName}{$quote}.{$quote}{$sentDateTimeColumnName}" . $quote . " = '0000-00-00 00:00:00' OR {$quote}{$emailMessageTableName}{$quote}" . ".{$quote}{$sentDateTimeColumnName}{$quote} IS NULL THEN 1 ELSE 0 END)";
// Not Coding Standard
$sentEmailsSelectPart = "sum(CASE WHEN {$quote}{$emailMessageTableName}{$quote}.{$quote}{$sentDateTimeColumnName}" . $quote . " > '0000-00-00 00:00:00' THEN 1 ELSE 0 END)";
$uniqueOpensSelectPart = static::resolveAutoresponderTypeSubQuery(EmailMessageActivity::TYPE_OPEN);
$uniqueClicksSelectPart = static::resolveAutoresponderTypeSubQuery(EmailMessageActivity::TYPE_CLICK);
$bouncedSelectPart = static::resolveAutoresponderTypeSubQuery(EmailMessageActivity::TYPE_BOUNCE);
$optedOutSelectPart = static::resolveAutoresponderTypeSubQuery(EmailMessageActivity::TYPE_UNSUBSCRIBE);
static::addEmailMessageDayDateClause($selectQueryAdapter, $sentDateTimeColumnName);
static::addEmailMessageFirstDayOfWeekDateClause($selectQueryAdapter, $sentDateTimeColumnName);
static::addEmailMessageFirstDayOfMonthDateClause($selectQueryAdapter, $sentDateTimeColumnName);
$selectQueryAdapter->addNonSpecificCountClause();
$selectQueryAdapter->addClauseByQueryString($queuedEmailsSelectPart, static::QUEUED);
$selectQueryAdapter->addClauseByQueryString($sentEmailsSelectPart, static::SENT);
$selectQueryAdapter->addClauseByQueryString("count((" . $uniqueOpensSelectPart . "))", static::UNIQUE_OPENS);
$selectQueryAdapter->addClauseByQueryString("count((" . $uniqueClicksSelectPart . "))", static::UNIQUE_CLICKS);
$selectQueryAdapter->addClauseByQueryString("count((" . $bouncedSelectPart . "))", static::BOUNCED);
$selectQueryAdapter->addClauseByQueryString("count((" . $optedOutSelectPart . "))", static::UNSUBSCRIBED);
$joinTablesAdapter->addLeftTableAndGetAliasName($autoresponderItemTableName, 'id', $autoresponderTableName, 'autoresponder_id');
$joinTablesAdapter->addLeftTableAndGetAliasName($emailMessageTableName, 'emailmessage_id', $autoresponderItemTableName, 'id');
$where = RedBeanModelDataProvider::makeWhere('Autoresponder', $searchAttributeData, $joinTablesAdapter);
$sql = SQLQueryUtil::makeQuery($autoresponderTableName, $selectQueryAdapter, $joinTablesAdapter, null, null, $where);
return $sql;
}
protected static function generateCampaignItems($campaign, $pageSize)
{
if ($pageSize == null) {
$pageSize = self::DEFAULT_CAMPAIGNITEMS_TO_CREATE_PAGE_SIZE;
}
$contacts = array();
$quote = DatabaseCompatibilityUtil::getQuote();
$marketingListMemberTableName = RedBeanModel::getTableName('MarketingListMember');
$campaignItemTableName = RedBeanModel::getTableName('CampaignItem');
$sql = "select {$quote}{$marketingListMemberTableName}{$quote}.{$quote}contact_id{$quote} from {$quote}{$marketingListMemberTableName}{$quote}";
// Not Coding Standard
$sql .= "left join {$quote}{$campaignItemTableName}{$quote} on ";
$sql .= "{$quote}{$campaignItemTableName}{$quote}.{$quote}contact_id{$quote} ";
$sql .= "= {$quote}{$marketingListMemberTableName}{$quote}.{$quote}contact_id{$quote}";
$sql .= "AND {$quote}{$campaignItemTableName}{$quote}.{$quote}campaign_id{$quote} = " . $campaign->id . " ";
$sql .= "where {$quote}{$marketingListMemberTableName}{$quote}.{$quote}marketinglist_id{$quote} = " . $campaign->marketingList->id;
$sql .= " and {$quote}{$campaignItemTableName}{$quote}.{$quote}id{$quote} is null limit " . $pageSize;
$ids = R::getCol($sql);
foreach ($ids as $contactId) {
$contacts[] = Contact::getById((int) $contactId);
}
if (!empty($contacts)) {
//todo: if the return value is false, then we might need to catch that since it didn't go well.
CampaignItem::registerCampaignItemsByCampaign($campaign, $contacts);
if (count($ids) < $pageSize) {
return true;
}
} else {
return true;
}
return false;
}
/**
* @param string $tableAliasName
* @param string $columnName
* @return string
*/
public static function makeColumnNameWithTableAlias($tableAliasName, $columnName)
{
assert('is_string($tableAliasName)');
assert('is_string($columnName)');
$quote = DatabaseCompatibilityUtil::getQuote();
return $quote . $tableAliasName . $quote . '.' . $quote . $columnName . $quote;
}
public function testRun()
{
$quote = DatabaseCompatibilityUtil::getQuote();
//Create 2 imports, and set one with a date over a week ago (8 days ago) for the modifiedDateTime
$import = new Import();
$serializedData['importRulesType'] = 'ImportModelTestItem';
$import->serializedData = serialize($serializedData);
$this->assertTrue($import->save());
ImportTestHelper::createTempTableByFileNameAndTableName('importAnalyzerTest.csv', $import->getTempTableName(), true);
$modifiedDateTime = DateTimeUtil::convertTimestampToDbFormatDateTime(time() - 60 * 60 * 24 * 8);
$sql = "Update item set modifieddatetime = '" . $modifiedDateTime . "' where id = " . $import->getClassId('Item');
ZurmoRedBean::exec($sql);
$staleImportId = $import->id;
$import2 = new Import();
$serializedData['importRulesType'] = 'ImportModelTestItem';
$import2->serializedData = serialize($serializedData);
$this->assertTrue($import2->save());
ImportTestHelper::createTempTableByFileNameAndTableName('importAnalyzerTest.csv', $import2->getTempTableName(), true);
$this->assertEquals(2, Import::getCount());
$tableExists = ZurmoRedBean::$writer->doesTableExist($import->getTempTableName());
$this->assertTrue($tableExists);
$job = new ImportCleanupJob();
$this->assertTrue($job->run());
$tableExists = ZurmoRedBean::$writer->doesTableExist($import->getTempTableName());
$this->assertFalse($tableExists);
$imports = Import::getAll();
$this->assertEquals(1, count($imports));
$this->assertEquals($import2->id, $imports[0]->id);
}
protected function resolveExtraSql(RedBeanModelJoinTablesQueryAdapter &$joinTablesAdapter, &$where)
{
if ($where != '') {
$where .= ' and ';
}
$user = Yii::app()->user->userModel;
$quote = DatabaseCompatibilityUtil::getQuote();
$starredTableName = StarredUtil::getStarredTableName($this->modelClassName);
$extraOnQueryPart = "and {$quote}{$starredTableName}{$quote}.{$quote}user_id{$quote} = {$user->id}";
$starredTableAliasName = $joinTablesAdapter->addLeftTableAndGetAliasName($starredTableName, 'id', null, 'model_id', $extraOnQueryPart);
$where .= "{$quote}{$starredTableAliasName}{$quote}.{$quote}user_id{$quote} = {$user->id}";
}
public static function getByLayoutIdAndUserSortedById($layoutId, $userId)
{
$portletCollection = array();
assert('is_integer($userId) && $userId >= 1');
$quote = DatabaseCompatibilityUtil::getQuote();
$sql = "select id, {$quote}column{$quote}, position " . 'from portlet ' . "where layoutid = '{$layoutId}' and _user_id = {$userId} " . 'order by id;';
foreach (R::getAll($sql) as $row) {
$portlet = Portlet::getById(intval($row['id']));
$portletCollection[$row['id']] = $portlet;
}
return $portletCollection;
}
public static function getByLayoutId($layoutId)
{
$portletCollection = array();
assert('is_string($layoutId)');
$quote = DatabaseCompatibilityUtil::getQuote();
$sql = "select id, {$quote}column{$quote}, position " . 'from portlet ' . "where layoutid = '{$layoutId}'" . 'order by id;';
foreach (R::getAll($sql) as $row) {
$portlet = Portlet::getById(intval($row['id']));
$portletCollection[$row['id']] = $portlet;
}
return $portletCollection;
}
/**
* Given an id of a currency model, determine if any currency values are using this currency.
* @return true if at least one currency value model is using this currency.
* @param integer $currencyId
*/
public static function isCurrencyInUseById($currencyId)
{
assert('is_int($currencyId)');
$columnName = RedBeanModel::getForeignKeyName('CurrencyValue', 'currency');
$quote = DatabaseCompatibilityUtil::getQuote();
$where = "{$quote}{$columnName}{$quote} = '{$currencyId}'";
$count = CurrencyValue::getCount(null, $where);
if ($count > 0) {
return true;
}
return false;
}
public function testRun()
{
$quote = DatabaseCompatibilityUtil::getQuote();
$super = User::getByUsername('super');
Yii::app()->user->userModel = $super;
$this->assertEquals(0, count(EmailMessage::getAll()));
$job = new TestOutboundEmailJob();
$this->assertTrue($job->run());
$emailMessages = EmailMessage::getAll();
$this->assertEquals(1, count($emailMessages));
$this->assertEquals(EmailFolder::TYPE_SENT, $emailMessages[0]->folder->type);
}
protected function addReadOptimizationWhereClause(&$where, $whereKey, $tableAliasName)
{
assert('is_array($where)');
assert('is_int($whereKey)');
assert('is_string($tableAliasName)');
$q = DatabaseCompatibilityUtil::getQuote();
$columnWithTableAlias = self::makeColumnNameWithTableAlias($tableAliasName, $this->modelAttributeToDataProviderAdapter->getColumnName());
$mungeTableName = ReadPermissionsOptimizationUtil::getMungeTableName($this->modelAttributeToDataProviderAdapter->getModelClassName());
$mungeIds = ReadPermissionsOptimizationUtil::getMungeIdsByUser(Yii::app()->user->userModel);
$whereContent = $columnWithTableAlias . " " . SQLOperatorUtil::getOperatorByType('equals') . " ";
$whereContent .= "(select securableitem_id from {$q}{$mungeTableName}{$q} " . "where {$q}securableitem_id{$q} = {$columnWithTableAlias} and {$q}munge_id{$q}" . " in ('" . join("', '", $mungeIds) . "') limit 1)";
$where[$whereKey] = $whereContent;
}
public function testFullNameOnContactsSearchFormSearch()
{
$super = User::getByUsername('super');
Yii::app()->user->userModel = $super;
$_FAKEPOST['Contact'] = array();
$_FAKEPOST['Contact']['fullName'] = 'Jackie Tyler';
$metadataAdapter = new SearchDataProviderMetadataAdapter(new ContactsSearchForm(new Contact(false)), 1, $_FAKEPOST['Contact']);
$searchAttributeData = $metadataAdapter->getAdaptedMetadata();
$compareData = array('clauses' => array(1 => array('attributeName' => 'firstName', 'operatorType' => 'startsWith', 'value' => 'Jackie Tyler'), 2 => array('attributeName' => 'lastName', 'operatorType' => 'startsWith', 'value' => 'Jackie Tyler'), 3 => array('concatedAttributeNames' => array('firstName', 'lastName'), 'operatorType' => 'startsWith', 'value' => 'Jackie Tyler')), 'structure' => '(1 or 2 or 3)');
$this->assertEquals($compareData, $searchAttributeData);
$joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('Contact');
$quote = DatabaseCompatibilityUtil::getQuote();
$where = RedBeanModelDataProvider::makeWhere('Contact', $searchAttributeData, $joinTablesAdapter);
$compareWhere = "(({$quote}person{$quote}.{$quote}firstname{$quote} like 'Jackie Tyler%') or ";
$compareWhere .= "({$quote}person{$quote}.{$quote}lastname{$quote} like 'Jackie Tyler%') or ";
$compareWhere .= "(concat({$quote}person{$quote}.{$quote}firstname{$quote}, ' ', ";
$compareWhere .= "{$quote}person{$quote}.{$quote}lastname{$quote}) like 'Jackie Tyler%'))";
$this->assertEquals($compareWhere, $where);
//Now test that the joinTablesAdapter has correct information.
$this->assertEquals(1, $joinTablesAdapter->getFromTableJoinCount());
$this->assertEquals(0, $joinTablesAdapter->getLeftTableJoinCount());
$fromTables = $joinTablesAdapter->getFromTablesAndAliases();
$this->assertEquals('person', $fromTables[0]['tableName']);
//Make sure the sql runs properly.
$dataProvider = new RedBeanModelDataProvider('Contact', null, false, $searchAttributeData);
$data = $dataProvider->getData();
$this->assertEquals(0, count($data));
ContactTestHelper::createContactByNameForOwner('Dino', $super);
$dataProvider->getTotalItemCount(true);
//refreshes the total item count
$data = $dataProvider->getData();
$this->assertEquals(0, count($data));
ContactTestHelper::createContactByNameForOwner('Jackie', $super);
$dataProvider->getTotalItemCount(true);
//refreshes the total item count
$data = $dataProvider->getData();
$this->assertEquals(0, count($data));
ContactsModule::loadStartingData();
$contact = new Contact();
$contact->firstName = 'Jackie';
$contact->lastName = 'Tyler';
$contact->owner = $super;
$contact->state = ContactsUtil::getStartingState();
$this->assertTrue($contact->save());
$dataProvider->getTotalItemCount(true);
//refreshes the total item count
$data = $dataProvider->getData(true);
$this->assertEquals(1, count($data));
$this->assertEquals($contact->id, $data[0]->id);
}
/**
* Gets the date of all meetings as defined by the searchAttributeData
*/
public function makeSqlQuery()
{
assert('get_class($this->model) == "Meeting"');
$modelClassName = 'Meeting';
$quote = DatabaseCompatibilityUtil::getQuote();
$joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter($modelClassName);
$where = ModelDataProviderUtil::makeWhere($modelClassName, $this->searchAttributeData, $joinTablesAdapter);
$selectDistinct = false;
$modelClassName::resolveReadPermissionsOptimizationToSqlQuery(Yii::app()->user->userModel, $joinTablesAdapter, $where, $selectDistinct);
$selectQueryAdapter = new RedBeanModelSelectQueryAdapter($selectDistinct);
$selectQueryAdapter->addClause('meeting', 'startdatetime');
$sql = SQLQueryUtil::makeQuery('meeting', $selectQueryAdapter, $joinTablesAdapter, null, null, $where);
return $sql;
}
public static function getPrimaryByAccountIdAndContactId($accountId, $contactId)
{
assert('is_int($accountId)');
assert('is_int($contactId)');
$searchAttributeData = array();
$searchAttributeData['clauses'] = array(1 => array('attributeName' => 'account', 'operatorType' => 'equals', 'value' => $accountId), 2 => array('attributeName' => 'contact', 'operatorType' => 'equals', 'value' => $contactId));
$searchAttributeData['structure'] = '(1 and 2)';
$joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('AccountContactAffiliation');
$where = RedBeanModelDataProvider::makeWhere('AccountContactAffiliation', $searchAttributeData, $joinTablesAdapter);
$tableName = AccountContactAffiliation::getTableName();
$q = DatabaseCompatibilityUtil::getQuote();
$where .= " and {$q}{$tableName}{$q}.{$q}primary{$q} = '1'";
return self::getSubset($joinTablesAdapter, null, null, $where);
}
public function testRun()
{
$quote = DatabaseCompatibilityUtil::getQuote();
//Create 2 export items, and set one with a date over a week ago (8 days ago) for the modifiedDateTime
$exportItem = new ExportItem();
$exportItem->isCompleted = 0;
$exportItem->exportFileType = 'csv';
$exportItem->exportFileName = 'test';
$exportItem->modelClassName = 'Account';
$exportItem->serializedData = serialize(array('test', 'test2'));
$this->assertTrue($exportItem->save());
$fileContent = new FileContent();
$fileContent->content = 'test';
$exportFileModel = new ExportFileModel();
$exportFileModel->fileContent = $fileContent;
$exportFileModel->name = $exportItem->exportFileName . ".csv";
$exportFileModel->type = 'application/octet-stream';
$exportFileModel->size = strlen($fileContent->content);
$this->assertTrue($exportFileModel->save());
$exportFileModel1Id = $exportFileModel->id;
$exportItem->exportFileModel = $exportFileModel;
$this->assertTrue($exportItem->save());
$modifiedDateTime = DateTimeUtil::convertTimestampToDbFormatDateTime(time() - 60 * 60 * 24 * 8);
$sql = "Update item set modifieddatetime = '" . $modifiedDateTime . "' where id = " . $exportItem->getClassId('Item');
ZurmoRedBean::exec($sql);
// Second exportItem, that shouldn't be deleted.
$exportItem2 = new ExportItem();
$exportItem2->isCompleted = 0;
$exportItem2->exportFileType = 'csv';
$exportItem2->exportFileName = 'test';
$exportItem2->modelClassName = 'Account';
$exportItem2->serializedData = serialize(array('test', 'test2'));
$this->assertTrue($exportItem2->save());
$fileContent2 = new FileContent();
$fileContent2->content = 'test';
$exportFileModel2 = new ExportFileModel();
$exportFileModel2->fileContent = $fileContent2;
$exportFileModel2->name = $exportItem->exportFileName . ".csv";
$exportFileModel2->type = 'application/octet-stream';
$exportFileModel2->size = strlen($fileContent->content);
$this->assertTrue($exportFileModel2->save());
$exportFileModel2Id = $exportFileModel2->id;
$exportItem2->exportFileModel = $exportFileModel2;
$this->assertTrue($exportItem2->save());
$job = new ExportCleanupJob();
$this->assertTrue($job->run());
$exportItems = ExportItem::getAll();
$this->assertEquals(1, count($exportItems));
$this->assertEquals($exportItem2->id, $exportItems[0]->id);
}
public function testMakeQueryWithGroupBy()
{
$quote = DatabaseCompatibilityUtil::getQuote();
//Test simple query
$selectQueryAdapter = new RedBeanModelSelectQueryAdapter();
$selectQueryAdapter->addClause('a', 'b', 'c');
$joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('A');
$sql = SQLQueryUtil::makeQuery('myTable', $selectQueryAdapter, $joinTablesAdapter, 2, 4, 'a = x', null, 'a.group');
$compareSql = "select {$quote}a{$quote}.{$quote}b{$quote} c ";
$compareSql .= "from {$quote}myTable{$quote} ";
$compareSql .= "where a = x ";
$compareSql .= "group by a.group ";
$compareSql .= 'limit 4 offset 2';
$this->assertEquals($compareSql, $sql);
}
public function testResolveSortAttributeColumnName()
{
$quote = DatabaseCompatibilityUtil::getQuote();
//Test a standard non-relation attribute on I
$joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('I');
$modelAttributeToDataProviderAdapter = new RedBeanModelAttributeToDataProviderAdapter('I', 'iMember');
$sort = ModelDataProviderUtil::resolveSortAttributeColumnName($modelAttributeToDataProviderAdapter, $joinTablesAdapter);
$this->assertEquals("{$quote}i{$quote}.{$quote}imember{$quote}", $sort);
$this->assertEquals(0, $joinTablesAdapter->getFromTableJoinCount());
$this->assertEquals(0, $joinTablesAdapter->getLeftTableJoinCount());
//Test a standard casted up attribute on H from I
$joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('I');
$modelAttributeToDataProviderAdapter = new RedBeanModelAttributeToDataProviderAdapter('I', 'name');
$sort = ModelDataProviderUtil::resolveSortAttributeColumnName($modelAttributeToDataProviderAdapter, $joinTablesAdapter);
$this->assertEquals("{$quote}h{$quote}.{$quote}name{$quote}", $sort);
$this->assertEquals(1, $joinTablesAdapter->getFromTableJoinCount());
$this->assertEquals(0, $joinTablesAdapter->getLeftTableJoinCount());
//Test a relation attribute G->g from H
$joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('H');
$modelAttributeToDataProviderAdapter = new RedBeanModelAttributeToDataProviderAdapter('H', 'castUpHasOne', 'g');
$sort = ModelDataProviderUtil::resolveSortAttributeColumnName($modelAttributeToDataProviderAdapter, $joinTablesAdapter);
$this->assertEquals("{$quote}g{$quote}.{$quote}g{$quote}", $sort);
$this->assertEquals(0, $joinTablesAdapter->getFromTableJoinCount());
$this->assertEquals(1, $joinTablesAdapter->getLeftTableJoinCount());
$leftTables = $joinTablesAdapter->getLeftTablesAndAliases();
$this->assertEquals('g', $leftTables[0]['tableName']);
//Test a relation attribute G->g where casted up from I
$joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('I');
$modelAttributeToDataProviderAdapter = new RedBeanModelAttributeToDataProviderAdapter('I', 'castUpHasOne', 'g');
$sort = ModelDataProviderUtil::resolveSortAttributeColumnName($modelAttributeToDataProviderAdapter, $joinTablesAdapter);
$this->assertEquals("{$quote}g{$quote}.{$quote}g{$quote}", $sort);
$this->assertEquals(1, $joinTablesAdapter->getFromTableJoinCount());
$this->assertEquals(1, $joinTablesAdapter->getLeftTableJoinCount());
$fromTables = $joinTablesAdapter->getFromTablesAndAliases();
$this->assertEquals('h', $fromTables[0]['tableName']);
$leftTables = $joinTablesAdapter->getLeftTablesAndAliases();
$this->assertEquals('g', $leftTables[0]['tableName']);
//Test a customField like TestCustomFieldsModel->industry
$joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('TestCustomFieldsModel');
$modelAttributeToDataProviderAdapter = new RedBeanModelAttributeToDataProviderAdapter('TestCustomFieldsModel', 'industry', 'value');
$sort = ModelDataProviderUtil::resolveSortAttributeColumnName($modelAttributeToDataProviderAdapter, $joinTablesAdapter);
$this->assertEquals("{$quote}customfield{$quote}.{$quote}value{$quote}", $sort);
$this->assertEquals(0, $joinTablesAdapter->getFromTableJoinCount());
$this->assertEquals(1, $joinTablesAdapter->getLeftTableJoinCount());
$leftTables = $joinTablesAdapter->getLeftTablesAndAliases();
$this->assertEquals('customfield', $leftTables[0]['tableName']);
}
public static function updateValueByDataIdAndOldValueAndNewValue($customFieldDataId, $oldValue, $newValue)
{
$quote = DatabaseCompatibilityUtil::getQuote();
$customFieldTableName = CustomField::getTableName();
$baseCustomFieldTableName = BaseCustomField::getTableName();
$baseCustomFieldJoinColumnName = $baseCustomFieldTableName . '_id';
$valueAttributeColumnName = 'value';
$dataAttributeColumnName = static::getForeignKeyName('BaseCustomField', 'data');
$sql = "update {$quote}{$customFieldTableName}{$quote}, {$quote}{$baseCustomFieldTableName}{$quote} ";
$sql .= "set {$quote}{$valueAttributeColumnName}{$quote} = '{$newValue}' ";
$sql .= "where {$quote}{$customFieldTableName}{$quote}.{$baseCustomFieldJoinColumnName} = ";
// Not Coding Standard
$sql .= "{$quote}{$baseCustomFieldTableName}{$quote}.id ";
$sql .= "AND {$quote}{$dataAttributeColumnName}{$quote} = {$customFieldDataId} ";
$sql .= "AND {$quote}{$valueAttributeColumnName}{$quote} = '{$oldValue}'";
ZurmoRedBean::exec($sql);
}
protected function resolveExtraSql(RedBeanModelJoinTablesQueryAdapter &$joinTablesAdapter, &$where)
{
$user = Yii::app()->user->userModel;
$quote = DatabaseCompatibilityUtil::getQuote();
if ($where != '') {
$where .= ' and ';
}
$modelClassName = $this->modelClassName;
$starredClassName = StarredUtil::getStarredModelClassName($modelClassName);
$starredTableName = $starredClassName::getTableName();
$modelIdColumnName = $modelClassName::getTableName() . '_id';
$baseStarredTableName = BaseStarredModel::getTableName();
$baseStarredColumnName = $baseStarredTableName . '_id';
$starredTableAliasName = $joinTablesAdapter->addLeftTableAndGetAliasName($starredTableName, 'id', null, $modelIdColumnName);
$baseStarredTableAliasName = $joinTablesAdapter->addFromTableAndGetAliasName($baseStarredTableName, $baseStarredColumnName, $starredTableAliasName);
$where .= "{$quote}{$baseStarredTableAliasName}{$quote}.{$quote}_user_id{$quote} = {$user->id}";
}
public static function makeQuery($tableName, RedBeanModelSelectQueryAdapter $selectQueryAdapter, RedBeanModelJoinTablesQueryAdapter $joinTablesAdapter, $offset = null, $count = null, $where = null, $orderBy = null, $groupBy = null)
{
assert('is_string($tableName) && $tableName != ""');
assert('$offset === null || is_integer($offset) && $offset >= 0');
assert('$count === null || is_integer($count) && $count >= 1');
assert('$where === null || is_string ($where) && $where != ""');
assert('$orderBy === null || is_string ($orderBy) && $orderBy != ""');
assert('$groupBy === null || is_string ($groupBy) && $groupBy != ""');
$quote = DatabaseCompatibilityUtil::getQuote();
$sql = $selectQueryAdapter->getSelect();
$sql .= "from ";
//Added ( ) around from tables to ensure precedence over joins.
$joinFromPart = $joinTablesAdapter->getJoinFromQueryPart();
if ($joinFromPart !== null) {
$sql .= "(";
$sql .= "{$quote}{$tableName}{$quote}";
$sql .= ", {$joinFromPart}) ";
} else {
$sql .= "{$quote}{$tableName}{$quote}";
$sql .= ' ';
}
$sql .= $joinTablesAdapter->getJoinQueryPart();
$joinWherePart = $joinTablesAdapter->getJoinWhereQueryPart();
if ($where !== null) {
$sql .= "where {$where}";
if ($joinWherePart != null) {
$sql .= " and {$joinWherePart}";
}
} elseif ($joinWherePart != null) {
$sql .= " where {$joinWherePart}";
}
if ($groupBy !== null) {
$sql .= " group by {$groupBy}";
}
if ($orderBy !== null) {
$sql .= " order by {$orderBy}";
}
if ($count !== null) {
$sql .= " limit {$count}";
}
if ($offset !== null) {
$sql .= " offset {$offset}";
}
return $sql;
}
protected static function makeChartSqlQuery()
{
$quote = DatabaseCompatibilityUtil::getQuote();
$where = null;
$selectDistinct = false;
$joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('Opportunity');
Opportunity::resolveReadPermissionsOptimizationToSqlQuery(Yii::app()->user->userModel, $joinTablesAdapter, $where, $selectDistinct);
$selectQueryAdapter = new RedBeanModelSelectQueryAdapter($selectDistinct);
$sumPart = "{$quote}currencyvalue{$quote}.{$quote}value{$quote} ";
$sumPart .= "* {$quote}currencyvalue{$quote}.{$quote}ratetobase{$quote}";
$selectQueryAdapter->addClause('customfield', 'value', 'stage');
$selectQueryAdapter->addClauseByQueryString("sum({$sumPart})", 'amount');
$joinTablesAdapter->addFromTableAndGetAliasName('customfield', 'stage_customfield_id', 'opportunity');
$joinTablesAdapter->addFromTableAndGetAliasName('currencyvalue', 'amount_currencyvalue_id', 'opportunity');
$groupBy = "{$quote}customfield{$quote}.{$quote}value{$quote}";
$sql = SQLQueryUtil::makeQuery('opportunity', $selectQueryAdapter, $joinTablesAdapter, null, null, $where, null, $groupBy);
return $sql;
}
/**
* This test specifically looks at when searching a note's owner. Because note extends mashableactivity which
* does not have a bean, the query is constructed slightly different than if mashableactivity had a bean.
*/
public function testQueryIsProperlyGeneratedForNoteWithRelatedOwnerSearch()
{
Yii::app()->user->userModel = User::getByUsername('super');
$_FAKEPOST = array('Note' => array('owner' => array('id' => Yii::app()->user->userModel->id)));
$metadataAdapter = new SearchDataProviderMetadataAdapter(new Note(false), 1, $_FAKEPOST['Note']);
$_GET['Note_sort'] = 'description.desc';
$searchAttributeData = $metadataAdapter->getAdaptedMetadata();
$quote = DatabaseCompatibilityUtil::getQuote();
$joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('Note');
$where = RedBeanModelDataProvider::makeWhere('Note', $searchAttributeData, $joinTablesAdapter);
$orderByColumnName = RedBeanModelDataProvider::resolveSortAttributeColumnName('Note', $joinTablesAdapter, 'description');
$subsetSql = Note::makeSubsetOrCountSqlQuery('note', $joinTablesAdapter, 1, 5, $where, $orderByColumnName);
$compareSubsetSql = "select {$quote}note{$quote}.{$quote}id{$quote} id ";
$compareSubsetSql .= "from ({$quote}note{$quote}, {$quote}activity{$quote}, {$quote}ownedsecurableitem{$quote})";
$compareSubsetSql .= " where ({$quote}ownedsecurableitem{$quote}.{$quote}owner__user_id{$quote} = " . Yii::app()->user->userModel->id . ")";
$compareSubsetSql .= " and {$quote}activity{$quote}.{$quote}id{$quote} =";
$compareSubsetSql .= " {$quote}note{$quote}.{$quote}activity_id{$quote}";
$compareSubsetSql .= " and {$quote}ownedsecurableitem{$quote}.{$quote}id{$quote} = {$quote}activity{$quote}.{$quote}ownedsecurableitem_id{$quote}";
$compareSubsetSql .= " order by {$quote}note{$quote}.{$quote}description{$quote} limit 5 offset 1";
$this->assertEquals($compareSubsetSql, $subsetSql);
}
protected static function generateCampaignItems($campaign, $pageSize)
{
if ($pageSize == null) {
$pageSize = self::DEFAULT_CAMPAIGNITEMS_TO_CREATE_PAGE_SIZE;
}
$contacts = array();
$quote = DatabaseCompatibilityUtil::getQuote();
$sql = "select {$quote}marketinglistmember{$quote}.{$quote}contact_id{$quote} from {$quote}marketinglistmember{$quote}\n left join {$quote}campaignitem{$quote} on {$quote}campaignitem{$quote}.{$quote}contact_id{$quote} " . "= {$quote}marketinglistmember{$quote}.{$quote}contact_id{$quote} " . "AND {$quote}campaignitem{$quote}.{$quote}campaign_id{$quote} = " . $campaign->id . " where {$quote}marketinglistmember{$quote}.{$quote}marketinglist_id{$quote} = " . $campaign->marketingList->id . " and {$quote}campaignitem{$quote}.{$quote}id{$quote} IS NULL limit " . $pageSize;
$ids = R::getCol($sql);
foreach ($ids as $contactId) {
$contacts[] = Contact::getById((int) $contactId);
}
if (!empty($contacts)) {
//todo: if the return value is false, then we might need to catch that since it didn't go well.
CampaignItem::registerCampaignItemsByCampaign($campaign, $contacts);
if (count($ids) < $pageSize) {
return true;
}
} else {
return true;
}
}
/**
* @param string $beginDateTime
* @param array $searchAttributeData
* @return string
*/
protected static function makeColumnSqlQuery($beginDateTime, $searchAttributeData)
{
assert('is_string($beginDateTime)');
$quote = DatabaseCompatibilityUtil::getQuote();
$where = null;
$selectDistinct = false;
$marketingListTableName = MarketingList::getTableName();
$marketingListMemberTableName = MarketingListMember::getTableName();
$createdDateTimeColumnName = MarketingListMember::getColumnNameByAttribute('createdDateTime');
$unsubscribedColumnName = MarketingListMember::getColumnNameByAttribute('unsubscribed');
$joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('MarketingList');
$where = RedBeanModelDataProvider::makeWhere('MarketingList', $searchAttributeData, $joinTablesAdapter);
MarketingList::resolveReadPermissionsOptimizationToSqlQuery(Yii::app()->user->userModel, $joinTablesAdapter, $where, $selectDistinct);
$selectQueryAdapter = new RedBeanModelSelectQueryAdapter($selectDistinct);
$newSubscriberSelectPart = "sum(CASE WHEN {$quote}{$marketingListMemberTableName}{$quote}.{$quote}{$createdDateTimeColumnName}" . $quote . " >= '{$beginDateTime}' THEN 1 ELSE 0 END)";
$existingSubscriberSelectPart = "sum(CASE WHEN {$quote}{$marketingListMemberTableName}{$quote}.{$quote}{$createdDateTimeColumnName}" . $quote . " < '{$beginDateTime}' AND " . "{$quote}{$marketingListMemberTableName}{$quote}.{$quote}" . "{$unsubscribedColumnName}{$quote}=0 THEN 1 ELSE 0 END)";
// Not Coding Standard
$selectQueryAdapter->addClauseByQueryString($newSubscriberSelectPart, static::NEW_SUBSCRIBERS_COUNT);
$selectQueryAdapter->addClauseByQueryString($existingSubscriberSelectPart, static::EXISTING_SUBSCRIBERS_COUNT);
$joinTablesAdapter->addLeftTableAndGetAliasName($marketingListMemberTableName, 'id', $marketingListTableName, 'marketinglist_id');
$sql = SQLQueryUtil::makeQuery($marketingListTableName, $selectQueryAdapter, $joinTablesAdapter, null, null, $where);
return $sql;
}
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']);
}
public function testGetQuote()
{
if (RedBeanDatabase::getDatabaseType() == 'pgsql') {
$quoteCharacter = '"';
} else {
$quoteCharacter = '`';
}
$this->assertEquals($quoteCharacter, DatabaseCompatibilityUtil::getQuote());
}
/**
* @param string $modelClassName
* @param string $attributeName
* @return string
* @throws NotSupportedException
*/
protected function resolveGroupBy($modelClassName, $attributeName)
{
assert('is_string($modelClassName)');
assert('is_string($attributeName)');
$quote = DatabaseCompatibilityUtil::getQuote();
$tableName = $modelClassName::getTableName();
$columnName = $modelClassName::getColumnNameByAttribute($attributeName);
$groupByColumnString = "{$quote}{$tableName}{$quote}.{$quote}{$columnName}{$quote}";
if ($this->groupBy == MarketingOverallMetricsForm::GROUPING_TYPE_DAY) {
return "DATE({$groupByColumnString})";
} elseif ($this->groupBy == MarketingOverallMetricsForm::GROUPING_TYPE_WEEK) {
return "YEARWEEK(" . $groupByColumnString . ")";
} elseif ($this->groupBy == MarketingOverallMetricsForm::GROUPING_TYPE_MONTH) {
return "extract(YEAR_MONTH from " . $groupByColumnString . ")";
} else {
throw new NotSupportedException();
}
}
/**
* @depends testSetAttributesWithPostForCustomField
*/
public function testUpdateValueOnCustomFieldRows()
{
$values = array('A', 'B', 'C');
$customFieldData = CustomFieldData::getByName('updateItems');
$customFieldData->serializedData = serialize($values);
$this->assertTrue($customFieldData->save());
$id = $customFieldData->id;
$customField = new CustomField();
$customField->value = 'A';
$customField->data = $customFieldData;
$this->assertTrue($customField->save());
$customField = new CustomField();
$customField->value = 'B';
$customField->data = $customFieldData;
$this->assertTrue($customField->save());
$customField = new CustomField();
$customField->value = 'C';
$customField->data = $customFieldData;
$this->assertTrue($customField->save());
$customField = new CustomField();
$customField->value = 'C';
$customField->data = $customFieldData;
$this->assertTrue($customField->save());
$quote = DatabaseCompatibilityUtil::getQuote();
$customFieldTableName = CustomField::getTableName();
$baseCustomFieldTableName = BaseCustomField::getTableName();
$valueAttributeColumnName = 'value';
$dataAttributeColumnName = RedBeanModel::getForeignKeyName('CustomField', 'data');
$sql = "select {$quote}{$customFieldTableName}{$quote}.id from {$quote}{$customFieldTableName}{$quote} ";
$sql .= "left join {$quote}{$baseCustomFieldTableName}{$quote} on ";
$sql .= "{$quote}{$baseCustomFieldTableName}{$quote}.id = ";
$sql .= "{$quote}{$customFieldTableName}{$quote}.basecustomfield_id ";
$sql .= "where {$quote}{$dataAttributeColumnName}{$quote} = {$id}";
$ids = ZurmoRedBean::getCol($sql);
$beans = ZurmoRedBean::batch($customFieldTableName, $ids);
$customFields = RedBeanModel::makeModels($beans, 'CustomField');
$this->assertEquals(4, count($customFields));
$sql = "select {$quote}{$customFieldTableName}{$quote}.id from {$quote}{$customFieldTableName}{$quote} ";
$sql .= "left join {$quote}{$baseCustomFieldTableName}{$quote} on ";
$sql .= "{$quote}{$baseCustomFieldTableName}{$quote}.id = ";
$sql .= "{$quote}{$customFieldTableName}{$quote}.basecustomfield_id ";
$sql .= "where {$quote}{$dataAttributeColumnName}{$quote} = {$id} ";
$sql .= "and {$quote}{$valueAttributeColumnName}{$quote} = 'B'";
$this->assertEquals(1, count(ZurmoRedBean::getCol($sql)));
$sql = "select {$quote}{$customFieldTableName}{$quote}.id from {$quote}{$customFieldTableName}{$quote} ";
$sql .= "left join {$quote}{$baseCustomFieldTableName}{$quote} on ";
$sql .= "{$quote}{$baseCustomFieldTableName}{$quote}.id = ";
$sql .= "{$quote}{$customFieldTableName}{$quote}.basecustomfield_id ";
$sql .= "where {$quote}{$dataAttributeColumnName}{$quote} = {$id} ";
$sql .= "and {$quote}{$valueAttributeColumnName}{$quote} = 'C'";
$this->assertEquals(2, count(ZurmoRedBean::getCol($sql)));
$sql = "select {$quote}{$customFieldTableName}{$quote}.id from {$quote}{$customFieldTableName}{$quote} ";
$sql .= "left join {$quote}{$baseCustomFieldTableName}{$quote} on ";
$sql .= "{$quote}{$baseCustomFieldTableName}{$quote}.id = ";
$sql .= "{$quote}{$customFieldTableName}{$quote}.basecustomfield_id ";
$sql .= "where {$quote}{$dataAttributeColumnName}{$quote} = {$id} ";
$sql .= "and {$quote}{$valueAttributeColumnName}{$quote} = 'E'";
$this->assertEquals(0, count(ZurmoRedBean::getCol($sql)));
CustomField::updateValueByDataIdAndOldValueAndNewValue($id, 'C', 'E');
$sql = "select {$quote}{$customFieldTableName}{$quote}.id from {$quote}{$customFieldTableName}{$quote} ";
$sql .= "left join {$quote}{$baseCustomFieldTableName}{$quote} on ";
$sql .= "{$quote}{$baseCustomFieldTableName}{$quote}.id = ";
$sql .= "{$quote}{$customFieldTableName}{$quote}.basecustomfield_id ";
$sql .= "where {$quote}{$dataAttributeColumnName}{$quote} = {$id} ";
$sql .= "and {$quote}{$valueAttributeColumnName}{$quote} = 'B'";
$this->assertEquals(1, count(ZurmoRedBean::getCol($sql)));
$sql = "select {$quote}{$customFieldTableName}{$quote}.id from {$quote}{$customFieldTableName}{$quote} ";
$sql .= "left join {$quote}{$baseCustomFieldTableName}{$quote} on ";
$sql .= "{$quote}{$baseCustomFieldTableName}{$quote}.id = ";
$sql .= "{$quote}{$customFieldTableName}{$quote}.basecustomfield_id ";
$sql .= "where {$quote}{$dataAttributeColumnName}{$quote} = {$id} ";
$sql .= "and {$quote}{$valueAttributeColumnName}{$quote} = 'C'";
$this->assertEquals(0, count(ZurmoRedBean::getCol($sql)));
$sql = "select {$quote}{$customFieldTableName}{$quote}.id from {$quote}{$customFieldTableName}{$quote} ";
$sql .= "left join {$quote}{$baseCustomFieldTableName}{$quote} on ";
$sql .= "{$quote}{$baseCustomFieldTableName}{$quote}.id = ";
$sql .= "{$quote}{$customFieldTableName}{$quote}.basecustomfield_id ";
$sql .= "where {$quote}{$dataAttributeColumnName}{$quote} = {$id} ";
$sql .= "and {$quote}{$valueAttributeColumnName}{$quote} = 'E'";
$this->assertEquals(2, count(ZurmoRedBean::getCol($sql)));
}
