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; }