/**
* Runs a query to get all the dates for meetings based on SearchAttributeData. Then the data is processed
* and @returns a data array of dates and quantity. Quantity stands for how many meetings in a given date.
* (non-PHPdoc)
* @see CalendarDataProvider::getData()
*/
public function getData()
{
$sql = $this->makeSqlQuery();
$rows = ZurmoRedBean::getAll($sql);
$data = array();
foreach ($rows as $row) {
$localTimeZoneAdjustedDate = DateTimeUtil::convertDbFormattedDateTimeToLocaleFormattedDisplay($row['startdatetime'], 'medium', null);
if (isset($data[$localTimeZoneAdjustedDate])) {
$data[$localTimeZoneAdjustedDate]['quantity'] = $data[$localTimeZoneAdjustedDate]['quantity'] + 1;
} else {
$data[$localTimeZoneAdjustedDate] = array('date' => $localTimeZoneAdjustedDate, 'quantity' => 1, 'dbDate' => $row['startdatetime']);
}
}
foreach ($data as $key => $item) {
if ($item['quantity'] == 1) {
$label = Zurmo::t('MeetingsModule', '{quantity} MeetingsModuleSingularLabel', array_merge(LabelUtil::getTranslationParamsForAllModules(), array('{quantity}' => $item['quantity'])));
} else {
$label = Zurmo::t('MeetingsModule', '{quantity} MeetingsModulePluralLabel', array_merge(LabelUtil::getTranslationParamsForAllModules(), array('{quantity}' => $item['quantity'])));
}
$data[$key]['label'] = $label;
if ($item['quantity'] > 5) {
$quantityClassSuffix = 6;
} else {
$quantityClassSuffix = $item['quantity'];
}
$data[$key]['className'] = 'calendar-events-' . $quantityClassSuffix;
}
return $data;
}
public function testProcessStatusAndMessagesForEachRow()
{
Yii::app()->user->userModel = User::getByUsername('super');
$import = new Import();
$serializedData['importRulesType'] = 'ImportModelTestItem';
$import->serializedData = serialize($serializedData);
$this->assertTrue($import->save());
$testTableName = $import->getTempTableName();
$this->assertTrue(ImportTestHelper::createTempTableByFileNameAndTableName('importTest.csv', $testTableName, true));
$count = ImportDatabaseUtil::getCount($testTableName);
$this->assertEquals(5, $count);
//Now add import results.
$resultsUtil = new ImportResultsUtil($import);
$rowDataResultsUtil = new ImportRowDataResultsUtil(2);
$rowDataResultsUtil->setStatusToUpdated();
$rowDataResultsUtil->addMessage('the first message');
$resultsUtil->addRowDataResults($rowDataResultsUtil);
$rowDataResultsUtil = new ImportRowDataResultsUtil(3);
$rowDataResultsUtil->setStatusToCreated();
$rowDataResultsUtil->addMessage('the second message');
$resultsUtil->addRowDataResults($rowDataResultsUtil);
$rowDataResultsUtil = new ImportRowDataResultsUtil(4);
$rowDataResultsUtil->setStatusToError();
$rowDataResultsUtil->addMessage('the third message');
$resultsUtil->addRowDataResults($rowDataResultsUtil);
$resultsUtil->processStatusAndMessagesForEachRow();
$sql = 'select * from ' . $testTableName . ' where id != 1';
$tempTableData = ZurmoRedBean::getAll($sql);
$compareData = array(array('id' => 2, 'column_0' => 'abc', 'column_1' => '123', 'column_2' => 'a', 'status' => 1, 'serializedMessages' => serialize(array('the first message')), 'analysisStatus' => null, 'serializedAnalysisMessages' => null), array('id' => 3, 'column_0' => 'def', 'column_1' => '563', 'column_2' => 'b', 'status' => 2, 'serializedMessages' => serialize(array('the second message')), 'analysisStatus' => null, 'serializedAnalysisMessages' => null), array('id' => 4, 'column_0' => 'efg', 'column_1' => '456', 'column_2' => 'a', 'status' => 3, 'serializedMessages' => serialize(array('the third message')), 'analysisStatus' => null, 'serializedAnalysisMessages' => null), array('id' => 5, 'column_0' => 'we1s', 'column_1' => null, 'column_2' => 'b', 'status' => null, 'serializedMessages' => null, 'analysisStatus' => null, 'serializedAnalysisMessages' => null));
$this->assertEquals($compareData, $tempTableData);
}
/**
* @return array
*/
protected function makeCombinedData()
{
$combinedRows = array();
//todo: should fix and get proper table name of attribute instead of passing in item
$groupBy = $this->resolveGroupBy('EmailMessage', 'sentDateTime');
$beginDateTime = DateTimeUtil::convertDateIntoTimeZoneAdjustedDateTimeBeginningOfDay($this->beginDate);
$endDateTime = DateTimeUtil::convertDateIntoTimeZoneAdjustedDateTimeEndOfDay($this->endDate);
if ($this->marketingList == null) {
$searchAttributeData = static::makeCampaignsSearchAttributeData('createdDateTime', $beginDateTime, $endDateTime, $this->campaign);
$sql = static::makeCampaignsSqlQuery($searchAttributeData, $groupBy);
$rows = ZurmoRedBean::getAll($sql);
foreach ($rows as $row) {
$this->addNewRowToCombinedRows($row, $combinedRows);
}
}
if ($this->campaign == null) {
$searchAttributeData = static::makeAutorespondersSearchAttributeData('createdDateTime', $beginDateTime, $endDateTime, $this->marketingList);
$sql = static::makeAutorespondersSqlQuery($searchAttributeData, $groupBy);
$rows = ZurmoRedBean::getAll($sql);
foreach ($rows as $row) {
$this->addNewRowToCombinedRows($row, $combinedRows);
}
}
return $combinedRows;
}
public static function findGameTableRowsThatAreDuplicatedByPersonKey($tableName)
{
assert('is_string($tableName)');
// Begin Not Coding Standard
$sql = "SELECT count(*) as count, person_item_id " . "FROM `" . $tableName . "` " . "GROUP BY person_item_id having count(person_item_id) > 1";
// End Not Coding Standard
return ZurmoRedBean::getAll($sql);
}
/**
* @return int
*/
public function calculateTotalItemCount()
{
$selectQueryAdapter = new RedBeanModelSelectQueryAdapter();
$sql = $this->makeSqlQueryForFetchingTotalItemCount($selectQueryAdapter);
$rows = ZurmoRedBean::getAll($sql);
$count = count($rows);
return $count;
}
public static function isCategoryInUseById($categoryId)
{
$categories = Category::getById($categoryId);
$rows = ZurmoRedBean::getAll('SELECT * FROM customfieldvalue WHERE multiplevaluescustomfield_id IN (SELECT category_multiplevaluescustomfield_id FROM costbook) AND VALUE="' . $categories->name . '"');
if (count($rows) > 0) {
return true;
} else {
return false;
}
}
public static function isUnitofmeasureInUseById($unitofmeasureId)
{
$unitofmeasures = Unitofmeasure::getById($unitofmeasureId);
$rows = ZurmoRedBean::getAll('SELECT * FROM customfield WHERE id IN (SELECT unitofmeasure_customfield_id FROM costbook) AND VALUE="' . $unitofmeasures->name . '"');
if (count($rows) > 0) {
return true;
} else {
return false;
}
}
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 = '" . DatabaseCompatibilityUtil::escape($layoutId) . "' and _user_id = " . DatabaseCompatibilityUtil::escape($userId) . ' order by id;';
foreach (ZurmoRedBean::getAll($sql) as $row) {
$portlet = Portlet::getById(intval($row['id']));
$portletCollection[$row['id']] = $portlet;
}
return $portletCollection;
}
public function getChartData()
{
$customFieldData = CustomFieldDataModelUtil::getDataByModelClassNameAndAttributeName('Opportunity', 'stage');
$labels = CustomFieldDataUtil::getDataIndexedByDataAndTranslatedLabelsByLanguage($customFieldData, Yii::app()->language);
$sql = static::makeChartSqlQuery();
$rows = ZurmoRedBean::getAll($sql);
$chartData = array();
foreach ($rows as $row) {
$chartData[] = array('value' => $this->resolveCurrencyValueConversionRateForCurrentUserForDisplay($row['amount']), 'displayLabel' => static::resolveLabelByValueAndLabels($row['stage'], $labels));
}
return $chartData;
}
public static function getAddProductSearchData($category, $costOfGoods)
{
$allAssemblySql = 'SELECT A.*, D.value AS UnitOfMeasure,B.value AS Category FROM
costbook A
LEFT JOIN customfieldvalue AS B ON B.multiplevaluescustomfield_id=A.category_multiplevaluescustomfield_id
LEFT JOIN customfield AS C ON C.id =A.costofgoodssold_customfield_id
LEFT JOIN customfield AS D ON A.unitofmeasure_customfield_id =D.id
WHERE
IF("ALL"="' . $category . '",TRUE,B.VALUE="' . $category . '")
AND IF("ALL"="' . $costOfGoods . '",TRUE,C.VALUE="' . $costOfGoods . '")';
return ZurmoRedBean::getAll($allAssemblySql);
}
/**
* @param string $type
* @param int $startingRank
* @param null|int $offset
* @param null|int $count
* @return array
*/
public static function getUserLeaderboardData($type, $startingRank = 1, $offset = null, $count = null, User $user = null)
{
assert('is_string($type)');
assert('$offset === null || is_integer($offset) && $offset >= 0');
assert('$count === null || is_integer($count) && $count >= 1');
$sql = static::makeUserLeaderboardSqlQuery($type, $offset, $count);
$rows = ZurmoRedBean::getAll($sql);
$rank = $startingRank;
$leaderboardData = array();
foreach ($rows as $row) {
if ($user == null || $user->id == $row['userid']) {
$leaderboardData[$row['userid']] = array('rank' => StringUtil::resolveOrdinalIntegerAsStringContent(intval($rank)), 'userLabel' => strval(User::getById(intval($row['userid']))), 'points' => intval($row['points']));
}
$rank++;
}
return $leaderboardData;
}
/**
* Make sure the query actually runs correctly.
*/
public function testASingleAttributeThatRunsFrozenQueryCorrectly()
{
$q = DatabaseCompatibilityUtil::getQuote();
$joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('ReportModelTestItem');
$builder = new FiltersReportQueryBuilder($joinTablesAdapter, '1');
$filter = new FilterForReportForm('AccountsModule', 'Account', Report::TYPE_ROWS_AND_COLUMNS);
$filter->attributeIndexOrDerivedType = 'ReadOptimization';
$content = $builder->makeQueryContent(array($filter));
$compareContent = "({$q}ownedsecurableitem{$q}.{$q}securableitem_id{$q} = (select securableitem_id " . "from {$q}account_read{$q} where {$q}securableitem_id{$q} = {$q}ownedsecurableitem" . "{$q}.{$q}securableitem_id{$q} and {$q}munge_id{$q} in ('U" . self::$superUserId . "', 'G" . self::$everyoneGroupId . "') limit 1))";
$this->assertEquals($compareContent, $content);
$this->assertEquals(1, $joinTablesAdapter->getFromTableJoinCount());
$this->assertEquals(0, $joinTablesAdapter->getLeftTableJoinCount());
$selectQueryAdapter = new RedBeanModelSelectQueryAdapter();
$selectQueryAdapter->addClause(Account::getTableName(), 'id');
$sql = SQLQueryUtil::makeQuery(Account::getTableName(), $selectQueryAdapter, $joinTablesAdapter, null, null, $content, null, null);
$rows = ZurmoRedBean::getAll($sql);
$this->assertEquals(0, count($rows));
}
public function getCountDataByGroupByColumnName($groupbyColumnName, $where = null)
{
assert(is_string($groupbyColumnName));
// Not Coding Standard
assert('is_string($where) || $where == null');
$sql = "select count(*) count, {$groupbyColumnName} from {$this->tableName} ";
$this->resolveWhereClause($where);
if ($where != null) {
$sql .= 'where ' . $where . ' ';
}
$sql .= 'group by ' . $groupbyColumnName;
return ZurmoRedBean::getAll($sql);
}
public function testOnCreateOwnerChangeAndDeleteAccountModel()
{
$super = User::getByUsername('super');
$billy = self::$billy;
Yii::app()->user->userModel = $super;
$job = new ReadPermissionSubscriptionUpdateForAccountFromBuildTableJob();
Yii::app()->jobQueue->deleteAll();
// Clean contact table
$sql = "SELECT * FROM account_read_subscription";
$rows = ZurmoRedBean::getAll($sql);
$this->assertTrue(empty($rows));
$account1 = AccountTestHelper::createAccountByNameForOwner('First Account', $super);
sleep(1);
$queuedJobs = Yii::app()->jobQueue->getAll();
$this->assertEquals(1, count($queuedJobs[5]));
$this->assertEquals('ReadPermissionSubscriptionUpdateForAccountFromBuildTable', $queuedJobs[5][0]['jobType']);
Yii::app()->jobQueue->deleteAll();
$this->assertTrue($job->run());
$sql = "SELECT * FROM account_read_subscription order by userid";
$rows = ZurmoRedBean::getAll($sql);
$this->assertEquals(2, count($rows));
$this->assertEquals($super->id, $rows[0]['userid']);
$this->assertEquals($account1->id, $rows[0]['modelid']);
$this->assertEquals(ReadPermissionsSubscriptionUtil::TYPE_ADD, $rows[0]['subscriptiontype']);
$this->assertEquals($billy->id, $rows[1]['userid']);
$this->assertEquals($account1->id, $rows[1]['modelid']);
$this->assertEquals(ReadPermissionsSubscriptionUtil::TYPE_ADD, $rows[1]['subscriptiontype']);
sleep(1);
// Test deletion
$account1->delete();
sleep(1);
$queuedJobs = Yii::app()->jobQueue->getAll();
$this->assertEquals(1, count($queuedJobs[5]));
$this->assertEquals('ReadPermissionSubscriptionUpdateForAccountFromBuildTable', $queuedJobs[5][0]['jobType']);
Yii::app()->jobQueue->deleteAll();
$this->assertTrue($job->run());
$sql = "SELECT * FROM account_read_subscription order by userid";
$rows2 = ZurmoRedBean::getAll($sql);
$this->assertEquals(2, count($rows2));
$this->assertEquals($super->id, $rows2[0]['userid']);
$this->assertEquals($account1->id, $rows2[0]['modelid']);
$this->assertEquals(ReadPermissionsSubscriptionUtil::TYPE_DELETE, $rows2[0]['subscriptiontype']);
$this->assertNotEquals($rows[0]['modifieddatetime'], $rows2[0]['modifieddatetime']);
$this->assertEquals($billy->id, $rows2[1]['userid']);
$this->assertEquals($account1->id, $rows2[1]['modelid']);
$this->assertEquals(ReadPermissionsSubscriptionUtil::TYPE_DELETE, $rows2[1]['subscriptiontype']);
$this->assertNotEquals($rows[1]['modifieddatetime'], $rows2[1]['modifieddatetime']);
// Test owner change, but when both users have permissions to access the account
$sql = "DELETE FROM account_read_subscription";
ZurmoRedBean::exec($sql);
$sql = "SELECT * FROM account_read_subscription";
$rows = ZurmoRedBean::getAll($sql);
$this->assertTrue(empty($rows));
$account2 = AccountTestHelper::createAccountByNameForOwner('Second Account', $super);
sleep(1);
$queuedJobs = Yii::app()->jobQueue->getAll();
$this->assertEquals(1, count($queuedJobs[5]));
$this->assertEquals('ReadPermissionSubscriptionUpdateForAccountFromBuildTable', $queuedJobs[5][0]['jobType']);
Yii::app()->jobQueue->deleteAll();
$this->assertTrue($job->run());
$sql = "SELECT * FROM account_read_subscription order by userid";
$rows = ZurmoRedBean::getAll($sql);
$this->assertEquals(2, count($rows));
$this->assertEquals($super->id, $rows[0]['userid']);
$this->assertEquals($account2->id, $rows[0]['modelid']);
$this->assertEquals(ReadPermissionsSubscriptionUtil::TYPE_ADD, $rows[0]['subscriptiontype']);
$this->assertEquals($billy->id, $rows[1]['userid']);
$this->assertEquals($account2->id, $rows[1]['modelid']);
$this->assertEquals(ReadPermissionsSubscriptionUtil::TYPE_ADD, $rows[1]['subscriptiontype']);
sleep(1);
$account2->owner = self::$billy;
$this->assertTrue($account2->save());
sleep(1);
$queuedJobs = Yii::app()->jobQueue->getAll();
$this->assertEquals(1, count($queuedJobs[5]));
$this->assertEquals('ReadPermissionSubscriptionUpdateForAccountFromBuildTable', $queuedJobs[5][0]['jobType']);
Yii::app()->jobQueue->deleteAll();
$this->assertTrue($job->run());
$sql = "SELECT * FROM account_read_subscription order by userid";
$rows = ZurmoRedBean::getAll($sql);
$this->assertEquals(2, count($rows));
$this->assertEquals($super->id, $rows[0]['userid']);
$this->assertEquals($account2->id, $rows[0]['modelid']);
$this->assertEquals(ReadPermissionsSubscriptionUtil::TYPE_ADD, $rows[0]['subscriptiontype']);
$this->assertEquals(self::$billy->id, $rows[1]['userid']);
$this->assertEquals($account2->id, $rows[1]['modelid']);
$this->assertEquals(ReadPermissionsSubscriptionUtil::TYPE_ADD, $rows[1]['subscriptiontype']);
// Clean account table
$accounts = Account::getAll();
foreach ($accounts as $account) {
$account->delete();
}
$sql = "DELETE FROM account_read_subscription";
ZurmoRedBean::exec($sql);
$johnny = self::$johnny;
$account3 = AccountTestHelper::createAccountByNameForOwner('Third Account', $johnny);
sleep(1);
$queuedJobs = Yii::app()->jobQueue->getAll();
$this->assertEquals(1, count($queuedJobs[5]));
$this->assertEquals('ReadPermissionSubscriptionUpdateForAccountFromBuildTable', $queuedJobs[5][0]['jobType']);
Yii::app()->jobQueue->deleteAll();
$this->assertTrue($job->run());
$sql = "SELECT * FROM account_read_subscription order by userid";
$rows = ZurmoRedBean::getAll($sql);
$this->assertEquals(3, count($rows));
$this->assertEquals($super->id, $rows[0]['userid']);
$this->assertEquals($account3->id, $rows[0]['modelid']);
$this->assertEquals(ReadPermissionsSubscriptionUtil::TYPE_ADD, $rows[0]['subscriptiontype']);
$this->assertEquals($billy->id, $rows[1]['userid']);
$this->assertEquals($account3->id, $rows[1]['modelid']);
$this->assertEquals(ReadPermissionsSubscriptionUtil::TYPE_ADD, $rows[1]['subscriptiontype']);
$this->assertEquals($johnny->id, $rows[2]['userid']);
$this->assertEquals($account3->id, $rows[2]['modelid']);
$this->assertEquals(ReadPermissionsSubscriptionUtil::TYPE_ADD, $rows[2]['subscriptiontype']);
$account3Id = $account3->id;
$account3->forgetAll();
$account3 = Account::getById($account3Id);
$this->assertTrue($account3->save());
$account3->forgetAll();
PermissionsCache::forgetAll();
$account3 = Account::getById($account3Id);
$account3->owner = $super;
$this->assertTrue($account3->save());
sleep(1);
$queuedJobs = Yii::app()->jobQueue->getAll();
$this->assertEquals(1, count($queuedJobs[5]));
$this->assertEquals('ReadPermissionSubscriptionUpdateForAccountFromBuildTable', $queuedJobs[5][0]['jobType']);
Yii::app()->jobQueue->deleteAll();
$this->assertTrue($job->run());
$sql = "SELECT * FROM account_read_subscription order by userid";
$rows = ZurmoRedBean::getAll($sql);
$this->assertEquals(3, count($rows));
$this->assertEquals($super->id, $rows[0]['userid']);
$this->assertEquals($account3->id, $rows[0]['modelid']);
$this->assertEquals(ReadPermissionsSubscriptionUtil::TYPE_ADD, $rows[0]['subscriptiontype']);
$this->assertEquals($billy->id, $rows[1]['userid']);
$this->assertEquals($account3->id, $rows[1]['modelid']);
$this->assertEquals(ReadPermissionsSubscriptionUtil::TYPE_ADD, $rows[1]['subscriptiontype']);
$this->assertEquals($johnny->id, $rows[2]['userid']);
$this->assertEquals($account3->id, $rows[2]['modelid']);
$this->assertEquals(ReadPermissionsSubscriptionUtil::TYPE_DELETE, $rows[2]['subscriptiontype']);
}
public function testDateTimeHinting()
{
$bean = ZurmoRedBean::dispense("wukka");
// Not Coding Standard
$bean->setMeta("hint", array("prop" => "datetime"));
// Not Coding Standard
$bean->prop = "2010-01-01 10:00:00";
// Not Coding Standard
ZurmoRedBean::store($bean);
// Not Coding Standard
$rows = ZurmoRedBean::getAll('desc wukka');
$this->assertEquals('prop', $rows[3]['Field']);
$this->assertEquals('datetime', $rows[3]['Type']);
}
public static function dropStoredFunctionsAndProcedures()
{
assert('RedBeanDatabase::isSetup()');
if (RedBeanDatabase::getDatabaseType() == 'mysql') {
try {
$rows = ZurmoRedBean::getAll("select routine_name, routine_type from information_schema.routines;");
foreach ($rows as $row) {
ZurmoRedBean::exec("drop {$row['routine_type']} if exists {$row['routine_name']}");
}
} catch (Exception $e) {
if (isset($row)) {
echo "Failed to drop {$row['routine_type']} {$row['routine_name']}.\n";
}
throw $e;
}
if (YII_DEBUG) {
ZurmoRedBean::exec("drop procedure if exists write_log");
}
} else {
throw new NotSupportedException();
}
}
/**
* @param null | int $offset
* @param null | int $limit
* @return array
*/
protected function runQueryAndGetResolveResultsData($offset, $limit)
{
assert('is_int($offset) || $offset == null');
assert('is_int($limit) || $limit == null');
$selectQueryAdapter = new RedBeanModelSelectQueryAdapter();
$sql = $this->makeSqlQueryForFetchingData($selectQueryAdapter, $offset, $limit);
$rows = $this->getRowsData($sql);
$resultsData = array();
$idByOffset = 0;
$calculationsCount = $this->getDisplayCalculationsCount();
$xAxisGroupByDataValuesCount = $this->getXAxisGroupByDataValuesCount() * $calculationsCount;
$xAxisGroupingsColumnNamesData = $this->makeXAxisGroupingsForColumnNamesData();
$displayAttributesThatAreYAxisGroupBys = $this->getDisplayAttributesThatAreYAxisGroupBys();
$previousYAxisDisplayAttributesUniqueIndex = $this->resolveYAxisDisplayAttributesUniqueIndex($rows[0], $displayAttributesThatAreYAxisGroupBys);
$resultsData[$idByOffset] = new ReportResultsRowData($this->resolveDisplayAttributes(), 0);
$this->addDefaultColumnNamesAndValuesToReportResultsRowData($resultsData[$idByOffset], $xAxisGroupByDataValuesCount);
$sqlForRowTotals = $this->makeSqlQueryForRowTotals();
$rowTotals = ZurmoRedBean::getAll($sqlForRowTotals);
foreach ($rows as $row) {
$currentYAxisDisplayAttributesUniqueIndex = $this->resolveYAxisDisplayAttributesUniqueIndex($row, $displayAttributesThatAreYAxisGroupBys);
if ($previousYAxisDisplayAttributesUniqueIndex != $currentYAxisDisplayAttributesUniqueIndex) {
$idByOffset++;
$resultsData[$idByOffset] = new ReportResultsRowData($this->resolveDisplayAttributes(), $idByOffset);
$this->addDefaultColumnNamesAndValuesToReportResultsRowData($resultsData[$idByOffset], $xAxisGroupByDataValuesCount);
}
$tempData = $xAxisGroupingsColumnNamesData;
foreach ($this->resolveDisplayAttributes() as $displayAttribute) {
$value = $row[$displayAttribute->columnAliasName];
if ($this->isDisplayAttributeAnXAxisGroupBy($displayAttribute)) {
$tempData = $tempData[$value];
} elseif ($this->isDisplayAttributeAnYAxisGroupBy($displayAttribute)) {
$resolvedColumnAliasName = static::resolveHeaderColumnAliasName($displayAttribute->columnAliasName);
$resultsData[$idByOffset]->addSelectedColumnNameAndValue($resolvedColumnAliasName, $value);
$resultsData[$idByOffset]->addSelectedColumnNameAndLabel($resolvedColumnAliasName, $displayAttribute->resolveValueAsLabelForHeaderCell($value));
}
}
//At this point $tempData is at the final level, where the actual display calculations are located
foreach ($this->resolveDisplayAttributes() as $displayAttribute) {
if (!$displayAttribute->queryOnly) {
$value = $row[$displayAttribute->columnAliasName];
$columnAliasName = $tempData[$displayAttribute->attributeIndexOrDerivedType];
$resultsData[$idByOffset]->addSelectedColumnNameAndValue($columnAliasName, $value);
//Adds the totalRows column
$totalValue = $rowTotals[$idByOffset][$displayAttribute->columnAliasName];
$resolvedColumnAliasName = static::resolveTotalColumnAliasName($displayAttribute->columnAliasName);
$resultsData[$idByOffset]->addSelectedColumnNameAndValue($resolvedColumnAliasName, $totalValue);
}
}
$previousYAxisDisplayAttributesUniqueIndex = $currentYAxisDisplayAttributesUniqueIndex;
}
$this->resolveRowSpansForResultsData($resultsData);
return $resultsData;
}
/**
* Test cases when:
* 1. user is created
* 2. permissions for user to access account are added
* 3. permissions for user to access account are removed
* 4. permissions for user to access account are added after they are being removed
*/
public function testUserCreationsAndPermissions()
{
$super = User::getByUsername('super');
Yii::app()->user->userModel = $super;
$this->deleteAllModelsAndRecordsFromReadPermissionTable('Account');
Yii::app()->jobQueue->deleteAll();
$job = new ReadPermissionSubscriptionUpdateForAccountJob();
$jobBasedOnBuildTable = new ReadPermissionSubscriptionUpdateForAccountFromBuildTableJob();
$account = AccountTestHelper::createAccountByNameForOwner('First Account For Users', $super);
Yii::app()->jobQueue->deleteAll();
sleep(1);
$user = new User();
$user->username = '******';
$user->lastName = 'Smitson';
$user->setPassword(strtolower('password'));
$this->assertTrue($user->save());
$queuedJobs = Yii::app()->jobQueue->getAll();
$this->assertEquals(1, count($queuedJobs[5]));
$this->assertEquals('ReadPermissionSubscriptionUpdateForAccount', $queuedJobs[5][0]['jobType']);
Yii::app()->jobQueue->deleteAll();
$this->assertTrue($job->run());
$sql = "SELECT * FROM account_read_subscription order by userid";
$rows = ZurmoRedBean::getAll($sql);
$this->assertEquals(1, count($rows));
$this->assertEquals($super->id, $rows[0]['userid']);
$this->assertEquals($account->id, $rows[0]['modelid']);
$this->assertEquals(ReadPermissionsSubscriptionUtil::TYPE_ADD, $rows[0]['subscriptiontype']);
// Test add read permissions to user for account
$account->addPermissions($user, Permission::READ);
$this->assertTrue($account->save());
ReadPermissionsOptimizationUtil::rebuild();
$queuedJobs = Yii::app()->jobQueue->getAll();
$this->assertEquals(1, count($queuedJobs[5]));
$this->assertEquals('ReadPermissionSubscriptionUpdateForAccountFromBuildTable', $queuedJobs[5][0]['jobType']);
Yii::app()->jobQueue->deleteAll();
$this->assertTrue($jobBasedOnBuildTable->run());
// Check if everything is added correctly
$sql = "SELECT * FROM account_read_subscription order by userid";
$rows = ZurmoRedBean::getAll($sql);
$this->assertEquals(2, count($rows));
$this->assertEquals($super->id, $rows[0]['userid']);
$this->assertEquals($account->id, $rows[0]['modelid']);
$this->assertEquals(ReadPermissionsSubscriptionUtil::TYPE_ADD, $rows[0]['subscriptiontype']);
$this->assertEquals($user->id, $rows[1]['userid']);
$this->assertEquals($account->id, $rows[1]['modelid']);
$this->assertEquals(ReadPermissionsSubscriptionUtil::TYPE_ADD, $rows[1]['subscriptiontype']);
// Test remove read permissions from user for account
$account->removePermissions($user, Permission::READ);
$this->assertTrue($account->save());
ReadPermissionsOptimizationUtil::rebuild();
$queuedJobs = Yii::app()->jobQueue->getAll();
$this->assertEquals(1, count($queuedJobs[5]));
$this->assertEquals('ReadPermissionSubscriptionUpdateForAccountFromBuildTable', $queuedJobs[5][0]['jobType']);
Yii::app()->jobQueue->deleteAll();
$this->assertTrue($jobBasedOnBuildTable->run());
// Because user is added to group, and group have read access to account, this account should be in
// read permission table for user
$sql = "SELECT * FROM account_read_subscription order by userid";
$rows = ZurmoRedBean::getAll($sql);
$this->assertEquals(2, count($rows));
$this->assertEquals($super->id, $rows[0]['userid']);
$this->assertEquals($account->id, $rows[0]['modelid']);
$this->assertEquals(ReadPermissionsSubscriptionUtil::TYPE_ADD, $rows[0]['subscriptiontype']);
$this->assertEquals($user->id, $rows[1]['userid']);
$this->assertEquals($account->id, $rows[1]['modelid']);
$this->assertEquals(ReadPermissionsSubscriptionUtil::TYPE_DELETE, $rows[1]['subscriptiontype']);
// Test add read permissions AGAIN to user for account
$account->addPermissions($user, Permission::READ);
$this->assertTrue($account->save());
ReadPermissionsOptimizationUtil::rebuild();
$queuedJobs = Yii::app()->jobQueue->getAll();
$this->assertEquals(1, count($queuedJobs[5]));
$this->assertEquals('ReadPermissionSubscriptionUpdateForAccountFromBuildTable', $queuedJobs[5][0]['jobType']);
Yii::app()->jobQueue->deleteAll();
$this->assertTrue($jobBasedOnBuildTable->run());
// Check if everything is added correctly
$sql = "SELECT * FROM account_read_subscription order by userid";
$rows = ZurmoRedBean::getAll($sql);
$this->assertEquals(2, count($rows));
$this->assertEquals($super->id, $rows[0]['userid']);
$this->assertEquals($account->id, $rows[0]['modelid']);
$this->assertEquals(ReadPermissionsSubscriptionUtil::TYPE_ADD, $rows[0]['subscriptiontype']);
$this->assertEquals($user->id, $rows[1]['userid']);
$this->assertEquals($account->id, $rows[1]['modelid']);
$this->assertEquals(ReadPermissionsSubscriptionUtil::TYPE_ADD, $rows[1]['subscriptiontype']);
}
/**
* Gets used color by user.
* @param User $user
* @param string $modelClassName
* @param string $attributeName
* @return array
*/
public static function getUsedCalendarColorsByUser(User $user, $modelClassName, $attributeName)
{
assert('is_string($modelClassName)');
assert('is_string($attributeName)');
$selectDistinct = false;
$joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter($modelClassName);
$selectQueryAdapter = new RedBeanModelSelectQueryAdapter($selectDistinct);
$selectQueryAdapter->addClause($modelClassName::getTableName(), 'color');
$metadata = array();
$metadata['clauses'] = array(1 => array('attributeName' => $attributeName, 'relatedAttributeName' => 'id', 'operatorType' => 'equals', 'value' => $user->id));
$metadata['structure'] = '1';
$where = RedBeanModelDataProvider::makeWhere($modelClassName, $metadata, $joinTablesAdapter);
$sql = SQLQueryUtil::makeQuery($modelClassName::getTableName(), $selectQueryAdapter, $joinTablesAdapter, null, null, $where);
$records = ZurmoRedBean::getAll($sql);
$colors = array();
foreach ($records as $record) {
if ($record['color'] != null && $record['color'] != '') {
$colors[] = $record['color'];
}
}
return $colors;
}
public function testCreateStarredTables()
{
$sql = "SHOW TABLES LIKE '%starred'";
$allStarredTableRows = ZurmoRedBean::getAll($sql);
$this->assertCount(4, $allStarredTableRows);
}
protected static function runQueryBySqlAndGetCountData($sql)
{
assert('is_string($sql)');
$countData = array();
$rows = ZurmoRedBean::getAll($sql);
foreach ($rows as $row) {
$countData[$row['attribute']] = $row['count'];
}
return $countData;
}
/**
* @depends testChangeOwnerOfModelInReadSubscriptionTableByModelIdAndModelClassNameAndUser
*/
public function testDeleteUserItemsFromAllReadSubscriptionTables()
{
$super = User::getByUsername('super');
$david = UserTestHelper::createBasicUser('david');
Yii::app()->user->userModel = $super;
// Clean contact table
$contacts = Contact::getAll();
foreach ($contacts as $contact) {
$contact->delete();
}
$sql = "DELETE FROM contact_read_subscription";
ZurmoRedBean::exec($sql);
$sql = "SELECT * FROM contact_read_subscription";
$rows = ZurmoRedBean::getAll($sql);
$this->assertTrue(empty($rows));
$contact1 = ContactTestHelper::createContactByNameForOwner('Ray', $david);
$sql = "SELECT * FROM contact_read_subscription";
$rows = ZurmoRedBean::getAll($sql);
$this->assertEquals(0, count($rows));
ReadPermissionsSubscriptionUtil::updateReadSubscriptionTableByModelClassNameAndUser('Contact', $super, time(), true, new MessageLogger());
ReadPermissionsSubscriptionUtil::updateReadSubscriptionTableByModelClassNameAndUser('Contact', $david, time(), true, new MessageLogger());
$sql = "SELECT * FROM contact_read_subscription";
$rows = ZurmoRedBean::getAll($sql);
$this->assertEquals(1, count($rows));
$this->assertEquals($david->id, $rows[0]['userid']);
$this->assertEquals($contact1->id, $rows[0]['modelid']);
$this->assertEquals(ReadPermissionsSubscriptionUtil::TYPE_ADD, $rows[0]['subscriptiontype']);
$david->delete();
$sql = "SELECT * FROM contact_read_subscription";
$rows = ZurmoRedBean::getAll($sql);
$this->assertEquals(0, count($rows));
}
/**
* Returns true if the attribute value does not already exist in
* the database. This is used in the unique validator, but on saving
* RedBean can still throw because the unique constraint on the column
* has been violated because it was concurrently updated between the
* Yii validator being called and the save actually occuring.
*/
public function isUniqueAttributeValue($attributeName, $value)
{
assert("\$this->isAttribute('{$attributeName}')");
assert('$value !== null');
if (!static::isRelation($attributeName)) {
$modelClassName = $this->attributeNameToBeanAndClassName[$attributeName][1];
$tableName = $modelClassName::getTableName();
$rows = ZurmoRedBean::getAll('select id from ' . $tableName . " where {$attributeName} = ?", array($value));
return count($rows) == 0 || count($rows) == 1 && $rows[0]['id'] == $this->id;
} else {
$model = $this->{$attributeName};
if ($model->id == 0) {
return true;
}
$relationAndOwns = static::getRelationNameToRelationTypeModelClassNameAndOwnsForModel();
$modelClassName = $relationAndOwns[$attributeName][1];
$tableName = $modelClassName::getTableName();
$rows = ZurmoRedBean::getAll('select id from ' . $tableName . ' where id = ?', array($model->id));
return count($rows) == 0 || count($rows) == 1 && $rows[0]['id'] == $this->id;
}
}
protected function getGrandTotalsRowsData()
{
$sql = $this->makeSqlQueryForGrandTotals();
if ($sql !== null) {
return ZurmoRedBean::getAll($sql);
}
}
/**
* Performance function to grab all summation data by type for a given user.
* @param User $user
* @return array of summation points indexed by the level type
*/
public static function getSummationPointsDataByUserIndexedByLevelType(User $user)
{
assert('$user->id > 0');
$sql = "select type, sum(value) sum from gamepoint where person_item_id = " . $user->getClassId('Item') . " group by type";
$rows = ZurmoRedBean::getAll($sql);
$indexedData = array();
foreach ($rows as $row) {
$indexedData[$row['type']] = $row['sum'];
}
return $indexedData;
}
/**
* Given a unioned sql statement, make the models for the beans returned. The modelClassName is a column
* name that must be in the select part of the sql statement for each unioned select.
* @param string $sql
* @return array of models
*/
protected function makeModelsBySql($sql)
{
assert('is_string($sql)');
$models = array();
$idsAndModelClassNames = ZurmoRedBean::getAll($sql);
foreach ($idsAndModelClassNames as $data) {
$modelClassName = $data['modelClassName'];
$tableName = $modelClassName::getTableName();
$bean = ZurmoRedBean::load($tableName, $data['id']);
$models[] = $modelClassName::makeModel($bean, $modelClassName);
}
return $models;
}
public function disabled_testAShootLoadOfAccounts()
{
$super = User::getByUsername('super');
foreach (array(20, 50, 100, 200, 500, 1000, 10000, 100000, 200000) as $shootLoad) {
echo "\nNuking existing accounts...\n";
Yii::app()->user->userModel = $super;
$this->nukeExistingAccounts();
echo "Creating {$shootLoad} accounts...\n";
echo " - Giving every 10th to Betty, giving Benny read\n";
echo " on overy 8th, and giving Sales Staff read on\n";
echo " every 12th.\n";
list($time, $countThatBennyCanRead, $accountIdsThatBennyCanRead) = $this->createAccounts($shootLoad);
echo 'Created accounts in ' . round($time, 1) . " seconds.\n";
echo "Benny can read {$countThatBennyCanRead} of them.\n";
echo 'The first few... ';
for ($i = 0; $i < 10 && $i < count($accountIdsThatBennyCanRead); $i++) {
echo "{$accountIdsThatBennyCanRead[$i]}|";
}
echo "\n";
$startTime = microtime(true);
ReadPermissionsOptimizationUtil::rebuild(true, true);
$endTime = microtime(true);
if ($this->isDebug()) {
echo 'Rebuilt the munge in php in ' . round($endTime - $startTime, 1) . ' seconds, ' . self::getAccountMungeRowCount() . " rows.\n";
}
$phpRows = ZurmoRedBean::getAll('select munge_id, securableitem_id, count from account_read order by munge_id, securableitem_id, count');
// If $securityOptimized is false in debug.php the second one will just do the php again.
$startTime = microtime(true);
ReadPermissionsOptimizationUtil::rebuild();
$endTime = microtime(true);
if ($this->isDebug()) {
echo 'Rebuilt the munge ' . (SECURITY_OPTIMIZED ? 'optimized' : 'in php') . ' in ' . round($endTime - $startTime, 1) . ' seconds, ' . self::getAccountMungeRowCount() . " rows.\n";
}
$otherRows = ZurmoRedBean::getAll('select munge_id, securableitem_id, count from account_read order by munge_id, securableitem_id, count');
if (count(array_diff($phpRows, $otherRows)) > 0) {
echo "PHP & optimized munges don't match.\n";
echo "--------\n";
foreach ($phpRows as $row) {
echo join(', ', array_values($row)) . "\n";
}
echo "--------\n";
foreach ($otherRows as $row) {
echo join(', ', array_values($row)) . "\n";
}
echo "--------\n";
}
$this->assertEquals(count($phpRows), count($otherRows));
$this->assertEquals($phpRows, $otherRows);
Yii::app()->user->userModel = User::getByUsername('benny');
$count = Account::getCount();
$startTime = microtime(true);
$accounts = Account::getSubset(null, 0, 20);
$endTime = microtime(true);
echo 'As Benny retrieved 1 - ' . count($accounts) . " of {$count} in " . round($endTime - $startTime, 2) . " seconds.\n";
unset($accounts);
$offset = intval($count * 0.75);
$startTime = microtime(true);
$accounts = Account::getSubset(null, $offset, 20);
$endTime = microtime(true);
echo "As Benny retrieved {$offset} - " . ($offset + count($accounts)) . " of {$count} in " . round($endTime - $startTime, 3) . " seconds.\n";
unset($accounts);
echo "Done.\n";
echo "\n-------------------------------\n";
}
}
/**
* Get all added model names and ids from read permission subscription table
* @param $serviceName
* @param $modelClassName
* @param $lastUpdateTimestamp
* @param $user
* @return array
*/
public static function getAddedModelNamesAndIdsFromReadSubscriptionTable($serviceName, $modelClassName, $lastUpdateTimestamp, $user)
{
assert('$user instanceof User');
$tableName = self::getSubscriptionTableName($modelClassName);
$modelTableName = $modelClassName::getTableName();
$dateTime = DateTimeUtil::convertTimestampToDbFormatDateTime($lastUpdateTimestamp);
$sql = "SELECT {$tableName}.modelid, {$modelTableName}.name FROM {$tableName}" . " left join " . ModelCreationApiSyncUtil::TABLE_NAME . " isct " . " on isct.modelid = {$tableName}.modelid" . " AND isct.servicename = '" . $serviceName . "'" . " AND isct.modelclassname = '" . $modelClassName . "'" . " left join {$modelTableName} on {$modelTableName}.id = {$tableName}.modelid" . " WHERE {$tableName}.userid = " . $user->id . " AND {$tableName}.subscriptiontype = " . self::TYPE_ADD . " AND {$tableName}.modifieddatetime >= '" . $dateTime . "'" . " AND isct.modelid is null" . " order by {$tableName}.modifieddatetime ASC, {$tableName}.modelid ASC";
$modelIdsRows = ZurmoRedBean::getAll($sql);
$modelIds = array();
if (is_array($modelIdsRows) && !empty($modelIdsRows)) {
foreach ($modelIdsRows as $modelIdRow) {
$modelIds[$modelIdRow['modelid']] = $modelIdRow['name'];
}
}
return $modelIds;
}
public static function getAccountMungeRows(SecurableItem $securableItem = null)
{
if ($securableItem === null) {
$rows = ZurmoRedBean::getAll('select name, munge_id, count
from account_read, ownedsecurableitem, account
where account_read.securableitem_id = ownedsecurableitem.securableitem_id and
ownedsecurableitem.id = account.ownedsecurableitem_id
order by name, munge_id, account_read.securableitem_id, count');
} else {
$securableItemId = $securableItem->getClassId('SecurableItem');
$rows = ZurmoRedBean::getAll("select munge_id, count\n from account_read\n where securableitem_id = {$securableItemId}\n order by munge_id, count");
}
$rowsWithValues = array();
foreach ($rows as $row) {
$row = array_values($row);
array_walk($row, array('self', 'stripFullStops'));
$rowsWithValues[] = $row;
}
return $rowsWithValues;
}
public function validateUniqueNameByModelClassName($attribute, $params)
{
assert('$attribute == "name"');
if ($this->{$attribute} != null) {
$tableName = DerivedAttributeMetadata::getTableName();
$sql = 'select id from ' . $tableName . " where name = '{$this->{$attribute}}' and ";
$sql .= "modelclassname = '" . $this->modelClassName . "'";
$rows = ZurmoRedBean::getAll($sql);
if (count($rows) == 0 || count($rows) == 1 && $rows[0]['id'] == $this->getClassId('DerivedAttributeMetadata')) {
return;
}
$message = Zurmo::t('Core', '{attribute} "{value}" is already in use.', array('{attribute}' => $attribute, '{name}' => $this->{$attribute}));
$this->addError('name', $message);
}
}