public static function getTailEvents($count)
{
assert('is_int($count)');
$sql = "select id\n from\n (select id\n from auditevent\n order by id desc\n limit {$count}) as temp\n order by id";
$ids = ZurmoRedBean::getCol($sql);
$beans = ZurmoRedBean::batch('auditevent', $ids);
return self::makeModels($beans, __CLASS__);
}
public static function getUserExternalSystemIds()
{
$columnName = ExternalSystemIdUtil::EXTERNAL_SYSTEM_ID_COLUMN_NAME;
$userTableName = User::getTableName();
ExternalSystemIdUtil::addExternalIdColumnIfMissing($userTableName);
$sql = 'select ' . $columnName . ' from ' . $userTableName;
return ZurmoRedBean::getCol($sql);
}
public static function getTailDistinctEventsByEventName($eventName, User $user, $count)
{
assert('is_string($eventName)');
assert('is_int($count)');
$sql = "select id\n from ( select id, modelclassname, modelid, datetime from auditevent where _user_id = {$user->id}\n AND eventname = '{$eventName}' order by id desc ) auditevent\n group by concat(modelclassname, modelid) order by datetime desc limit {$count}";
$ids = ZurmoRedBean::getCol($sql);
$beans = ZurmoRedBean::batch('auditevent', $ids);
return self::makeModels($beans, __CLASS__);
}
public static function getKeys(RedBean_OODBBean $bean, $typeName, $name = null)
{
$fieldName = self::getLinkField($typeName, $name);
$id = (int) $bean->{$fieldName};
$columnPrefix = self::resolveColumnPrefix($name);
$columnName = $columnPrefix . $bean->getMeta("type") . '_id';
$ids = ZurmoRedBean::getCol("select id from {$typeName} where " . $columnName . " = {$bean->id}");
return $ids;
}
/**
* @param int $layoutId
* @param User $user
* @throws NotFoundException
*/
public static function getByLayoutIdAndUser($layoutId, $user)
{
assert('is_integer($layoutId) && $layoutId >= 1');
assert('$user instanceof User && $user->id > 0');
$sql = 'select dashboard.id id ' . 'from dashboard, ownedsecurableitem ' . 'where ownedsecurableitem.owner__user_id = ' . $user->id . ' and dashboard.ownedsecurableitem_id = ownedsecurableitem.id ' . ' and layoutid = ' . $layoutId . ' order by layoutId;';
$ids = ZurmoRedBean::getCol($sql);
assert('count($ids) <= 1');
if (count($ids) == 0) {
if ($layoutId == Dashboard::DEFAULT_USER_LAYOUT_ID) {
return Dashboard::setDefaultDashboardForUser($user);
}
throw new NotFoundException();
}
$bean = ZurmoRedBean::load(Dashboard::getTableName(), $ids[0]);
assert('$bean === false || $bean instanceof RedBean_OODBBean');
if ($bean === false) {
throw new NotFoundException();
}
return self::makeModel($bean);
}
/**
* @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)));
}
/**
* @param $requiredPermissions
* @param OwnedSecurableItem $ownedSecurableItem
* @param User $user
* @return bool
* @throws NotSupportedException
* @throws AccessDeniedSecurityException
*/
protected static function checkPermissionsHasRead($requiredPermissions, OwnedSecurableItem $ownedSecurableItem, User $user)
{
$modelClassName = get_class($ownedSecurableItem);
$moduleClassName = $modelClassName::getModuleClassName();
$permission = PermissionsUtil::getActualPermissionDataForReadByModuleNameForUser($moduleClassName, $user);
if ($permission == Permission::NONE) {
$mungeIds = static::getMungeIdsByUser($user);
if (count($mungeIds) > 0 && $permission == Permission::NONE) {
$quote = DatabaseCompatibilityUtil::getQuote();
$mungeTableName = ReadPermissionsOptimizationUtil::getMungeTableName($modelClassName);
$sql = "select id from " . $mungeTableName . " where {$quote}securableitem_id{$quote} = " . $ownedSecurableItem->getClassId('SecurableItem') . " and {$quote}munge_id{$quote} in ('" . join("', '", $mungeIds) . "') limit 1";
$id = ZurmoRedBean::getCol($sql);
if (!empty($id)) {
return true;
} else {
throw new AccessDeniedSecurityException($user, $requiredPermissions, Permission::NONE);
}
} else {
throw new NotSupportedException();
}
} elseif ($permission == Permission::DENY) {
throw new AccessDeniedSecurityException($user, $requiredPermissions, Permission::DENY);
} else {
return true;
}
}
/**
* Get array odf all account ids from account temp build table
* @return array
*/
protected static function getAccountIdsArrayFromBuildTable()
{
$tableName = static::getAccountSubscriptionTempBuildTableName();
$sql = "select accountid from " . $tableName;
return ZurmoRedBean::getCol($sql);
}
/**
* @param User $user
* @param Role $role
*/
public static function userBeingRemovedFromRole(User $user, Role $role)
{
foreach (PathUtil::getAllMungableModelClassNames() as $modelClassName) {
$mungeTableName = self::getMungeTableName($modelClassName);
$userId = $user->id;
$sql = "select securableitem_id\n from ownedsecurableitem\n where owner__user_id = {$userId}";
$securableItemIds = ZurmoRedBean::getCol($sql);
self::bulkDecrementParentRolesCounts($mungeTableName, $securableItemIds, $role);
$sql = "select {$mungeTableName}.securableitem_id\n from {$mungeTableName}, _group__user\n where {$mungeTableName}.munge_id = concat('G', _group__user._group_id) and\n _group__user._user_id = {$userId}";
$securableItemIds = ZurmoRedBean::getCol($sql);
self::bulkDecrementParentRolesCounts($mungeTableName, $securableItemIds, $role);
/*
* This additional step I don't think is needed because the sql query above actually traps
* the upstream explicit securableItems because the lower level groups will already have a point for
* each of them.
What groups are the user part of and what groups are those groups children of recursively?
For any models that have that group explicity for read, subtract 1 point for the user's
upstream roles from the disconnected role.
*/
self::garbageCollect($mungeTableName);
}
}
/**
* Given a table name, count, and offset get an array of beans.
* @param string $tableName
* @param integer $count
* @param integer $offset
* @return array of RedBean_OODBBean beans.
*/
public static function getSubset($tableName, $where = null, $count = null, $offset = null)
{
assert('is_string($tableName)');
assert('$offset === null || is_integer($offset) && $offset >= 0');
assert('$offset === null || is_integer($count) && $count >= 1');
$sql = 'select id from ' . $tableName;
if ($where != null) {
$sql .= ' where ' . $where;
}
if ($count !== null) {
$sql .= " limit {$count}";
}
if ($offset !== null) {
$sql .= " offset {$offset}";
}
$ids = ZurmoRedBean::getCol($sql);
return ZurmoRedBean::batch($tableName, $ids);
}
/**
* Gets a range of model ids from the database of the named model type.
* @param $modelClassName
* @param $joinTablesAdapter null or instance of joinTablesAdapter.
* @param $offset The zero based index of the first model to be returned.
* @param $count The number of models to be returned.
* @param $where
* @param $orderBy - sql string. Example 'a desc' or 'a.b desc'. Currently only supports non-related attributes
* @param $modelClassName Pass only when getting it at runtime gets the wrong name.
* @return An array of models ids
*/
public static function getSubsetIds(RedBeanModelJoinTablesQueryAdapter $joinTablesAdapter = null, $offset = null, $count = null, $where = null, $orderBy = null, $modelClassName = null, $selectDistinct = false)
{
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('$modelClassName === null || is_string($modelClassName) && $modelClassName != ""');
if ($modelClassName === null) {
$modelClassName = get_called_class();
}
if ($joinTablesAdapter == null) {
$joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter($modelClassName);
}
$tableName = $modelClassName::getTableName();
$sql = static::makeSubsetOrCountSqlQuery($tableName, $joinTablesAdapter, $offset, $count, $where, $orderBy, false, $selectDistinct);
$ids = ZurmoRedBean::getCol($sql);
return $ids;
}
/**
* Tries to find the value in the system. If found, returns true, otherwise false.
* @param string $value
* @return boolean
*/
protected function resolveFoundExternalSystemIdByValue($value)
{
assert('is_int($value) || is_string($value) || $value == null');
if ($value == null) {
return false;
}
$modelClassName = $this->attributeModelClassName;
$columnName = ExternalSystemIdUtil::EXTERNAL_SYSTEM_ID_COLUMN_NAME;
$sql = 'select id from ' . $modelClassName::getTableName() . ' where ' . $columnName . ' = \'' . $value . '\' limit 1';
$ids = ZurmoRedBean::getCol($sql);
assert('count($ids) <= 1');
if (count($ids) == 0) {
return false;
}
return true;
}
