function fetchClassListByGroups( $groupFilter, $groupFilterType = 'include' ) { $notIn = ( $groupFilterType == 'exclude' ); if ( is_array( $groupFilter ) && count( $groupFilter ) > 0 ) { $db = eZDB::instance(); $groupFilter = $db->generateSQLINStatement( $groupFilter, 'ccg.group_id', $notIn ); $classNameFilter = eZContentClassName::sqlFilter( 'cc' ); $version = eZContentClass::VERSION_STATUS_DEFINED; $sql = "SELECT DISTINCT cc.*, $classNameFilter[nameField] " . "FROM ezcontentclass cc, ezcontentclass_classgroup ccg, $classNameFilter[from] " . "WHERE cc.version = $version" . " AND cc.id = ccg.contentclass_id" . " AND $groupFilter" . " AND $classNameFilter[where] " . "ORDER BY $classNameFilter[nameField] ASC"; $rows = $db->arrayQuery( $sql ); $classes = eZPersistentObject::handleRows( $rows, 'eZContentClass', true ); } else { $classes = eZContentClass::fetchList( eZContentClass::VERSION_STATUS_DEFINED, true, false, array( 'name' => 'asc' ) ); } return array( 'result' => $classes ); }
static function fetchListByOrder($orderID, $asObject = true) { $db = eZDB::instance(); $orderID = (int) $orderID; $rows = $db->arrayQuery("SELECT ezorder_status_history.*, ezorder_status.name AS status_name " . "FROM ezorder_status_history, ezorder_status " . "WHERE order_id = {$orderID} AND" . " ezorder_status.status_id = ezorder_status_history.status_id " . "ORDER BY ezorder_status_history.modified DESC"); return eZPersistentObject::handleRows($rows, 'eZOrderStatusHistory', $asObject); }
function canCreateClassList($asObject = false, $includeFilter = true, $groupList = false, $fetchID = false) { $ini = eZINI::instance(); $groupArray = array(); $languageCodeList = eZContentLanguage::fetchLocaleList(); $allowedLanguages = array('*' => array()); $user = eZUser::currentUser(); $accessResult = $user->hasAccessTo('content', 'create'); $accessWord = $accessResult['accessWord']; $classIDArray = array(); $classList = array(); $fetchAll = false; if ($accessWord == 'yes') { $fetchAll = true; $allowedLanguages['*'] = $languageCodeList; } else { if ($accessWord == 'no') { // Cannot create any objects, return empty list. return $classList; } else { foreach ($accessResult['policies'] as $policy) { $policyArray = $this->classListFromPolicy($policy, $languageCodeList); if (empty($policyArray)) { continue; } // Wildcard on all classes if ($policyArray['classes'] == '*') { $fetchAll = true; $allowedLanguages['*'] = array_unique(array_merge($allowedLanguages['*'], $policyArray['language_codes'])); // we remove individual class ids that are overriden in all languages by the wildcard (#EZP-20933) foreach ($allowedLanguages as $classId => $classLanguageCodes) { if ($classId == '*') { continue; } if (!count(array_diff($classLanguageCodes, $allowedLanguages['*']))) { unset($allowedLanguages[$classId]); } } } else { if (is_array($policyArray['classes']) && $this->hasCurrentSubtreeLimitation($policy)) { foreach ($policyArray['classes'] as $class) { if (isset($allowedLanguages[$class])) { $allowedLanguages[$class] = array_unique(array_merge($allowedLanguages[$class], $policyArray['language_codes'])); } else { // we don't add class identifiers that are already covered by the 'all classes' in a language if (!empty($allowedLanguages['*'])) { if (!count(array_diff($policyArray['language_codes'], $allowedLanguages['*']))) { continue; } } $allowedLanguages[$class] = $policyArray['language_codes']; } } $classIDArray = array_merge($classIDArray, array_diff($policyArray['classes'], $classIDArray)); } } } } } $db = eZDB::instance(); $filterTableSQL = ''; $filterSQL = ''; // Create extra SQL statements for the class group filters. if (is_array($groupList)) { if (count($groupList) == 0) { return $classList; } $filterTableSQL = ', ezcontentclass_classgroup ccg'; $filterSQL = " AND" . " cc.id = ccg.contentclass_id AND" . " "; $filterSQL .= $db->generateSQLINStatement($groupList, 'ccg.group_id', !$includeFilter, true, 'int'); } $classNameFilter = eZContentClassName::sqlFilter('cc'); if ($fetchAll) { // If $asObject is true we fetch all fields in class $fields = $asObject ? "cc.*, {$classNameFilter['nameField']}" : "cc.id, {$classNameFilter['nameField']}"; $rows = $db->arrayQuery("SELECT DISTINCT {$fields} " . "FROM ezcontentclass cc{$filterTableSQL}, {$classNameFilter['from']} " . "WHERE cc.version = " . eZContentClass::VERSION_STATUS_DEFINED . " {$filterSQL} AND {$classNameFilter['where']} " . "ORDER BY {$classNameFilter['nameField']} ASC"); $classList = eZPersistentObject::handleRows($rows, 'eZContentClass', $asObject); } else { // If the constrained class list is empty we are not allowed to create any class if (count($classIDArray) == 0) { return $classList; } $classIDCondition = $db->generateSQLINStatement($classIDArray, 'cc.id'); // If $asObject is true we fetch all fields in class $fields = $asObject ? "cc.*, {$classNameFilter['nameField']}" : "cc.id, {$classNameFilter['nameField']}"; $rows = $db->arrayQuery("SELECT DISTINCT {$fields} " . "FROM ezcontentclass cc{$filterTableSQL}, {$classNameFilter['from']} " . "WHERE {$classIDCondition} AND" . " cc.version = " . eZContentClass::VERSION_STATUS_DEFINED . " {$filterSQL} AND {$classNameFilter['where']} " . "ORDER BY {$classNameFilter['nameField']} ASC"); $classList = eZPersistentObject::handleRows($rows, 'eZContentClass', $asObject); } if ($asObject) { foreach ($classList as $key => $class) { $id = $class->attribute('id'); if (isset($allowedLanguages[$id])) { $languageCodes = array_unique(array_merge($allowedLanguages['*'], $allowedLanguages[$id])); } else { $languageCodes = $allowedLanguages['*']; } $classList[$key]->setCanInstantiateLanguages($languageCodes); } } eZDebugSetting::writeDebug('kernel-content-class', $classList, "class list fetched from db"); return $classList; }
static function fetchClassList($contentclass_version, $group_id, $asObject = true, $orderByArray = array('name')) { $versionCond = ''; $orderByClause = ''; $group_id = (int) $group_id; $classNameSqlFilter = eZContentClassName::sqlEmptyFilter(); if ($contentclass_version !== null) { $contentclass_version = (int) $contentclass_version; $versionCond = "AND class_group.contentclass_version='{$contentclass_version}'\n AND contentclass.version='{$contentclass_version}'\n"; } if ($orderByArray) { foreach (array_keys($orderByArray) as $key) { if (strcasecmp($orderByArray[$key], 'name') === 0) { $classNameSqlFilter = eZContentClassName::sqlAppendFilter('contentclass'); $orderByArray[$key] = $classNameSqlFilter['orderBy']; } } $orderByClause = 'ORDER BY ' . implode(', ', $orderByArray); } $db = eZDB::instance(); $sql = "SELECT contentclass.* {$classNameSqlFilter['nameField']}\n FROM ezcontentclass contentclass, ezcontentclass_classgroup class_group {$classNameSqlFilter['from']}\n WHERE contentclass.id=class_group.contentclass_id\n {$versionCond}\n AND class_group.group_id='{$group_id}' {$classNameSqlFilter['where']}\n {$orderByClause}"; $rows = $db->arrayQuery($sql); return eZPersistentObject::handleRows($rows, "eZContentClass", $asObject); }
/** * Finds all classes that the current user can create objects from and returns. * It is also possible to filter the list event more with $includeFilter and $groupList. * * @param bool $asObject If true then it return eZContentClass objects, if not it will be an associative array * @param bool $includeFilter If true then it will include only from class groups defined in $groupList, if not it will exclude those groups. * @param bool $groupList An array with class group IDs that should be used in filtering, use false if you do not wish to filter at all. * @param bool $fetchID A unique name for the current fetch, this must be supplied when filtering is used if you want caching to work. * @return array|eZPersistentObject[] */ function canCreateClassList( $asObject = false, $includeFilter = true, $groupList = false, $fetchID = false ) { $ini = eZINI::instance(); $groupArray = array(); $languageCodeList = eZContentLanguage::fetchLocaleList(); $allowedLanguages = array( '*' => array() ); $user = eZUser::currentUser(); $accessResult = $user->hasAccessTo( 'content' , 'create' ); $accessWord = $accessResult['accessWord']; $classIDArray = array(); $classList = array(); $fetchAll = false; if ( $accessWord == 'yes' ) { $fetchAll = true; $allowedLanguages['*'] = $languageCodeList; } else if ( $accessWord == 'no' ) { // Cannot create any objects, return empty list. return $classList; } else { $policies = $accessResult['policies']; foreach ( $policies as $policyKey => $policy ) { $policyArray = $this->classListFromPolicy( $policy, $languageCodeList ); if ( empty( $policyArray ) ) { continue; } $classIDArrayPart = $policyArray['classes']; $languageCodeArrayPart = $policyArray['language_codes']; // No class limitation for this policy AND no previous limitation(s) if ( $classIDArrayPart == '*' && empty( $classIDArray ) ) { $fetchAll = true; $allowedLanguages['*'] = array_unique( array_merge( $allowedLanguages['*'], $languageCodeArrayPart ) ); } else if ( is_array( $classIDArrayPart ) ) { $fetchAll = false; foreach( $classIDArrayPart as $class ) { if ( isset( $allowedLanguages[$class] ) ) { $allowedLanguages[$class] = array_unique( array_merge( $allowedLanguages[$class], $languageCodeArrayPart ) ); } else { $allowedLanguages[$class] = $languageCodeArrayPart; } } $classIDArray = array_merge( $classIDArray, array_diff( $classIDArrayPart, $classIDArray ) ); } } } $db = eZDB::instance(); $filterTableSQL = ''; $filterSQL = ''; // Create extra SQL statements for the class group filters. if ( is_array( $groupList ) ) { if ( count( $groupList ) == 0 ) { return $classList; } $filterTableSQL = ', ezcontentclass_classgroup ccg'; $filterSQL = ( " AND" . " cc.id = ccg.contentclass_id AND" . " " ); $filterSQL .= $db->generateSQLINStatement( $groupList, 'ccg.group_id', !$includeFilter, true, 'int' ); } $classNameFilter = eZContentClassName::sqlFilter( 'cc' ); if ( $fetchAll ) { // If $asObject is true we fetch all fields in class $fields = $asObject ? "cc.*, $classNameFilter[nameField]" : "cc.id, $classNameFilter[nameField]"; $rows = $db->arrayQuery( "SELECT DISTINCT $fields " . "FROM ezcontentclass cc$filterTableSQL, $classNameFilter[from] " . "WHERE cc.version = " . eZContentClass::VERSION_STATUS_DEFINED . " $filterSQL AND $classNameFilter[where] " . "ORDER BY $classNameFilter[nameField] ASC" ); $classList = eZPersistentObject::handleRows( $rows, 'eZContentClass', $asObject ); } else { // If the constrained class list is empty we are not allowed to create any class if ( count( $classIDArray ) == 0 ) { return $classList; } $classIDCondition = $db->generateSQLINStatement( $classIDArray, 'cc.id' ); // If $asObject is true we fetch all fields in class $fields = $asObject ? "cc.*, $classNameFilter[nameField]" : "cc.id, $classNameFilter[nameField]"; $rows = $db->arrayQuery( "SELECT DISTINCT $fields " . "FROM ezcontentclass cc$filterTableSQL, $classNameFilter[from] " . "WHERE $classIDCondition AND" . " cc.version = " . eZContentClass::VERSION_STATUS_DEFINED . " $filterSQL AND $classNameFilter[where] " . "ORDER BY $classNameFilter[nameField] ASC" ); $classList = eZPersistentObject::handleRows( $rows, 'eZContentClass', $asObject ); } if ( $asObject ) { foreach ( $classList as $key => $class ) { $id = $class->attribute( 'id' ); if ( isset( $allowedLanguages[$id] ) ) { $languageCodes = array_unique( array_merge( $allowedLanguages['*'], $allowedLanguages[$id] ) ); } else { $languageCodes = $allowedLanguages['*']; } $classList[$key]->setCanInstantiateLanguages( $languageCodes ); } } eZDebugSetting::writeDebug( 'kernel-content-class', $classList, "class list fetched from db" ); return $classList; }
public static function fetchByPath($uriString, $glob = false) { $uriString = eZURLAliasML::cleanURL($uriString); $db = eZDB::instance(); if ($uriString == '' && $glob !== false) { $elements = array(); } else { $elements = explode('/', $uriString); } $len = count($elements); $i = 0; $selects = array(); $tables = array(); $conds = array(); $prevTable = false; foreach ($elements as $element) { $table = "e" . $i; $langMask = trim(eZContentLanguage::languagesSQLFilter($table, 'lang_mask')); if ($glob === false && $i == $len - 1) { $selects[] = eZURLAliasML::generateFullSelect($table); } else { $selects[] = eZURLAliasML::generateSelect($table, $i, $len); } $tables[] = "ezurlalias_ml " . $table; $conds[] = eZURLAliasML::generateCond($table, $prevTable, $i, $langMask, $element); $prevTable = $table; ++$i; } if ($glob !== false) { ++$len; $table = "e" . $i; $langMask = trim(eZContentLanguage::languagesSQLFilter($table, 'lang_mask')); $selects[] = eZURLAliasML::generateFullSelect($table); $tables[] = "ezurlalias_ml " . $table; $conds[] = eZURLAliasML::generateGlobCond($table, $prevTable, $i, $langMask, $glob); $prevTable = $table; ++$i; } $elementOffset = $i - 1; $query = "SELECT DISTINCT " . join(", ", $selects) . " FROM " . join(", ", $tables) . " WHERE " . join(" AND ", $conds); $pathRows = $db->arrayQuery($query); $elements = array(); if (count($pathRows) > 0) { foreach ($pathRows as $pathRow) { $redirectLink = false; $table = "e" . $elementOffset; $element = array('id' => $pathRow[$table . "_id"], 'parent' => $pathRow[$table . "_parent"], 'lang_mask' => $pathRow[$table . "_lang_mask"], 'text' => $pathRow[$table . "_text"], 'action' => $pathRow[$table . "_action"], 'link' => $pathRow[$table . "_link"]); $path = array(); $lastID = false; for ($i = 0; $i < $len; ++$i) { $table = "e" . $i; $id = $pathRow[$table . "_id"]; $link = $pathRow[$table . "_link"]; $path[] = $pathRow[$table . "_text"]; if ($link != $id) { // Mark the redirect link $redirectLink = $link; $redirectOffset = $i; } $lastID = $link; } if ($redirectLink) { $newLinkID = $redirectLink; // Resolve new links until a real element is found. // TODO: Add max redirection count? while ($newLinkID) { $query = "SELECT id, parent, lang_mask, text, link FROM ezurlalias_ml WHERE id={$newLinkID}"; $rows = $db->arrayQuery($query); if (count($rows) == 0) { return false; } $newLinkID = false; if ($rows[0]['id'] != $rows[0]['link']) { $newLinkID = (int) $rows[0]['link']; } } $id = (int) $newLinkID; $path = array(); // Fetch path 'text' elements of correct parent path while ($id != 0) { $query = "SELECT parent, lang_mask, text FROM ezurlalias_ml WHERE id={$id}"; $rows = $db->arrayQuery($query); if (count($rows) == 0) { break; } $result = eZURLAliasML::choosePrioritizedRow($rows); if (!$result) { $result = $rows[0]; } $id = (int) $result['parent']; array_unshift($path, $result['text']); } // Fill in end of path elements for ($i = $redirectOffset; $i < $len; ++$i) { $table = "e" . $i; $path[] = $pathRow[$table . "_text"]; } } $element['path'] = implode('/', $path); $elements[] = $element; } } $rows = array(); $ids = array(); // Discard duplicates foreach ($elements as $element) { $id = (int) $element['id']; if (isset($ids[$id])) { continue; } $ids[$id] = true; $rows[] = $element; } $objectList = eZPersistentObject::handleRows($rows, 'eZURLAliasML', true); return $objectList; }
static function fetchListByRelatedContentObject($contentObjectID, $status = eZNewsletter::StatusPublished, $asObject = true) { $db = eZDB::instance(); $condSQL = 'object_relations LIKE \'%/' . $db->escapeString($contentObjectID) . '/%\' AND status = \'' . $db->escapeString($status) . '\''; $sql = 'SELECT * FROM eznewsletter WHERE ' . $condSQL; $rows = $db->arrayQuery($sql); $definition = eZNewsletter::definition(); $className = $definition['class_name']; return eZPersistentObject::handleRows($rows, $className, $asObject); }
/** * Fetch all Newsletter user with extended field * whith option to Filter data * * * @param array $filterArray condtions which will be combined with 'AND' * @param integer $limit * @param integer $offset * @param boolean $asObject * @return array with CjwNewsletterUser objects */ static function fetchUserListByFilter($filterArray, $limit = 50, $offset = 0, $asObject = true) { // var_dump( $filterArray ); $db = eZDB::instance(); $field_filters = null; $conditions = null; $sorts = null; // $limit = null; // $asObject = true; $grouping = false; $custom_fields = null; $custom_tables = null; $custom_conds = null; $def = self::definition(); $fields = $def["fields"]; $tables = $def["name"]; $class_name = $def["class_name"]; $sqlFieldArray = array('DISTINCT( cjwnl_user.email )', 'cjwnl_user.*'); $sqlTableArray = array('cjwnl_user'); $sqlCondArray = array(); $sqlCondArray[] = 'cjwnl_user.id = cjwnl_subscription.newsletter_user_id'; $sqlTableArray[] = 'cjwnl_subscription'; /* if ( $userStatus ) { $conditions = array( 'status' => (int) $userStatus ); $sqlCondAndArray[] = 'cjwnl_user.status=' . (int) $userStatus; }*/ // var_dump( $conditionArray ); // $subscriptionListIdArray = $filterArray[ 'cjwnl_subscription.list_contentobject_id' ]; // $subscriptionListStatus = $filterArray[ 'cjwnl_subscription.status' ]; // $countSubscriptionListIdArray = count( $subscriptionListIdArray ); // merge subscription list to filter subscriptions /* if ( $countSubscriptionListIdArray > 0 || $subscriptionListStatus !== false ) { $sqlTableArray[] = 'cjwnl_subscription'; $sqlCondAndArray[] = 'cjwnl_user.id = cjwnl_subscription.newsletter_user_id'; if ( $subscriptionListStatus ) { $sqlCondAndArray[] = 'cjwnl_subscription.status = '. $db->escapeString( $subscriptionListStatus ); } if ( $countSubscriptionListIdArray > 0 ) { $sqlCondAndArray[] = 'cjwnl_subscription.list_contentobject_id '. $db->generateSQLINStatement( $subscriptionListIdArray ); } } */ // $sqlCondAndArray[] = 'cjwnl_user.email like "%@%"'; if ((int) $limit != 0) { $limit = array('limit' => $limit, 'offset' => $offset); } // $field_filters = array( 'cjwnl_user.id' ); // $custom_tables = array( 'cjwnl_subscription ' ); // $custom_conds = ' AND cjwnl_user.id = cjwnl_subscription.newsletter_user_id'; $sqlFieldString = ''; if (count($sqlFieldArray) > 0) { $sqlFieldString = implode(', ', $sqlFieldArray); } $sqlTableString = ''; if (count($sqlTableArray) > 0) { $sqlTableString = implode(', ', $sqlTableArray); } foreach ($filterArray as $filter) { $sqlCondArray[] = self::filterText($filter); } // var_dump( $sqlCondArray ); $sqlCondAndString = ''; if (count($sqlCondArray) > 0) { $sqlCondAndString = 'WHERE ' . implode("\n AND ", $sqlCondArray) . ' '; } /* $sql = 'SELECT COUNT( cjwnl_subscription.id ) as count, cjwnl_user.* FROM cjwnl_user, cjwnl_subscription WHERE cjwnl_user.id = cjwnl_subscription.newsletter_user_id' . $sqlCondAndString . ' GROUP BY cjwnl_user.id';*/ /* $sql = 'SELECT DISTINCT( cjwnl_user.email ), cjwnl_user.* FROM cjwnl_user, cjwnl_subscription WHERE cjwnl_user.id = cjwnl_subscription.newsletter_user_id' . $sqlCondAndString;*/ $sql = "SELECT {$sqlFieldString}\n FROM {$sqlTableString}\n {$sqlCondAndString}"; //eZPersistentObject::replaceFieldsWithShortNames( $db, $fields, $conditions ); /* $conditions['cjwnl_user.email'][0] = '>='; $conditions['cjwnl_user.email'][1] = 'fe'; //$conditions['cjwnl_user.email'] = array( 'like', '.de' ); $conditions['cjwnl_user.name'] = array( 'like', '%fe%' ); */ /* $conditionText = eZPersistentObject::conditionText( $conditions ); echo $conditionText;*/ //echo '<hr>'; // echo $sql; eZDebug::writeDebug(print_r($filterArray, true)); eZDebug::writeDebug(print_r($sqlCondArray, true)); eZDebug::writeDebug($sql); //$db->arrayQuery( $sql ); $rows = $db->arrayQuery($sql, $limit); $objectList = eZPersistentObject::handleRows($rows, $class_name, $asObject); return $objectList; /* $objectList = eZPersistentObject::fetchObjectList( self::definition(), $field_filters, $conds, $sorts, $limit, $asObject, $grouping, $custom_fields, $custom_tables, $custom_conds ); return $objectList; */ }
/** * Creates an SQL query out of the different parameters and returns an array with the result. * * A full example: * <code> * $filter = array( 'id', 'name' ); * $conds = array( 'type' => 5, * 'size' => array( false, array( 200, 500 ) ) ); * $sorts = array( 'name' => 'asc' ); * $limit = array( 'offset' => 50, 'length' => 10 ); * eZPersistentObject::fetchObjectList( $def, $filter, $conds, $sorts, $limit, true, false, null ) * </code> * * Counting number of elements. * <code> * $custom = array( array( 'operation' => 'count( id )', * 'name' => 'count' ) ); * // Here $field_filters is set to an empty array, that way only count is used in fields * $rows = eZPersistentObject::fetchObjectList( $def, array(), null, null, null, false, false, $custom ); * return $rows[0]['count']; * </code> * * Counting elements per type using grouping * <code> * $custom = array( array( 'operation' => 'count( id )', * 'name' => 'count' ) ); * $group = array( 'type' ); * $rows = eZPersistentObject::fetchObjectList( $def, array(), null, null, null, false, $group, $custom ); * return $rows[0]['count']; * </code> * * Example to fetch a result with custom conditions. The following example will fetch the attributes to * the contentobject with id 1 and add the contentobject.name in each attribute row with the array key * contentobject_name. * <code> * $objectDef = eZContentObject::definition(); * $objectAttributeDef = eZContentObjectAttribute::definition(); * * $fields = array(); * $conds = array( $objectDef['name'] . '.id' => 1 ); * $sorts = array( $objectAttributeDef['name'] . '.sort_key_string' => 'asc' ); * * $limit = null; * $asObject = false; * $group = false; * * $customFields = array( $objectAttributeDef['name'] . '.*', * array( 'operation' => $objectDef['name'] . '.name', * 'name' => 'contentobject_name' ) ); * * $customTables = array( $objectDef['name'] ); * * $languageCode = 'eng-GB'; * $customConds = ' AND ' . $objectDef['name'] . '.current_version=' . $objectAttributeDef['name'] . '.version' . * ' AND ' . $objectDef['name'] . '.id=' . $objectAttributeDef['name'] . '.contentobject_id' . * ' AND ' . $objectAttributeDef['name'] . '.language_code=\'' . $languageCode . '\''; * * $rows = eZPersistentObject::fetchObjectList( $objectAttributeDef, $fields, $conds, $sorts, $limit, $asObject, * $group, $customFields, $customTables, $customConds ); * </code> * * @param array $def A definition array of all fields, table name and sorting (see {@link eZPersistentObject::definition()} for more info) * @param array|null $field_filters If defined determines the fields which are extracted (array of field names), if not all fields are fetched * @param array|null $conds null for no special condition or an associative array of fields to filter on. * Syntax is FIELD => CONDITION * CONDITION can be one of: * - Scalar value: Creates a condition where FIELD must match the value, e.g * <code>array( 'id' => 5 )</code> generates <code>SQL id = 5</code> * - Array with two scalar values: The first value is the match operator, the second is the scalar value * <code>array( 'priority' => array( '>', 5 ) )</code> generates SQL <code>priority > 5</code> * - Array with range: The first value is <code>false</code>, the second value is an array with start and stop of range in array * <code>array( 'type' => array( false, array( 1, 5 ) ) )</code> generates SQL <code>type BETWEEN 1 AND 5</code> * - Array with multiple elements: The first value is the field identifier, the second is an array with scalar values * <code>array( 'id' => array( array( 1, 5, 7 ) ) )</code> generates SQL <code>id IN ( 1, 5, 7 )</code> * @param array|null|bool $sorts An associative array of sorting conditions, if set to false ignores settings in $def, if set to null uses settingss in $def. * Syntax is FIELD => DIRECTION. * DIRECTION must either be 'asc' for ascending or 'desc' for descending. * @param array|null $limit An associative array with limitiations, can contain * - 'offset': Numerical value defining the start offset for the fetch * - 'length': Numerical value defining the max number of items to return * @param bool $asObject If true then it will return an array with objects, objects are created from class defined in $def. * If falseit will just return the rows fetch from database. * @param array|null|bool $grouping An array of fields to group by or null to use grouping in defintion $def. * @param null $custom_fields Array of FIELD elements to add to SQL, can be used to perform custom fetches, e.g counts. * FIELD is an associative array containing: * - 'operation': A text field which is included in the field list * - 'name': If present it adds <code>AS name</code> to the operation. * @param array|null $custom_tables Array of additional tables * @param string|null $custom_conds String with sql conditions for 'WHERE' clause. * @return eZPersistentObject[]|array|null An array of objects or rows, null on error */ public static function fetchObjectList($def, $field_filters = null, $conds = null, $sorts = null, $limit = null, $asObject = true, $grouping = false, $custom_fields = null, $custom_tables = null, $custom_conds = null) { $db = eZDB::instance(); $fields = $def["fields"]; $tables = $def["name"]; $class_name = $def["class_name"]; if (is_array($custom_tables)) { foreach ($custom_tables as $custom_table) { $tables .= ', ' . $db->escapeString($custom_table); } } eZPersistentObject::replaceFieldsWithShortNames($db, $fields, $conds); if (is_array($field_filters)) { $field_array = array_unique(array_intersect($field_filters, array_keys($fields))); } else { $field_array = array_keys($fields); } if ($custom_fields !== null and is_array($custom_fields)) { foreach ($custom_fields as $custom_field) { if (is_array($custom_field)) { $custom_text = $custom_field["operation"]; if (isset($custom_field["name"])) { $field_name = $custom_field["name"]; $custom_text .= " AS {$field_name}"; } } else { $custom_text = $custom_field; } $field_array[] = $custom_text; } } eZPersistentObject::replaceFieldsWithShortNames($db, $fields, $field_array); $field_text = ''; $i = 0; foreach ($field_array as $field_item) { if ($i % 7 == 0 and $i > 0) { $field_text .= ", "; } else { if ($i > 0) { $field_text .= ', '; } } $field_text .= $field_item; ++$i; } $where_text = eZPersistentObject::conditionText($conds); if ($custom_conds) { $where_text .= $custom_conds; } $sort_text = ""; if ($sorts !== false and (isset($def["sort"]) or is_array($sorts))) { $sort_list = array(); if (is_array($sorts)) { $sort_list = $sorts; } else { if (isset($def['sort'])) { $sort_list = $def["sort"]; } } if (count($sort_list) > 0) { $sort_text = " ORDER BY "; $i = 0; foreach ($sort_list as $sort_id => $sort_type) { if ($i > 0) { $sort_text .= ", "; } if ($sort_type == "desc") { $sort_text .= "{$sort_id} DESC"; } else { $sort_text .= "{$sort_id} ASC"; } ++$i; } } } $grouping_text = ""; if (isset($def["grouping"]) or is_array($grouping) and count($grouping) > 0) { $grouping_list = isset($def["grouping"]) ? $def["grouping"] : array(); if (is_array($grouping)) { $grouping_list = $grouping; } if (count($grouping_list) > 0) { $grouping_text = " GROUP BY "; $i = 0; foreach ($grouping_list as $grouping_id) { if ($i > 0) { $grouping_text .= ", "; } $grouping_text .= "{$grouping_id}"; ++$i; } } } $db_params = array(); if (is_array($limit)) { if (isset($limit["offset"])) { $db_params["offset"] = $limit["offset"]; } if (isset($limit['limit'])) { $db_params["limit"] = $limit["limit"]; } else { $db_params["limit"] = $limit["length"]; } } $sqlText = "SELECT {$field_text}\n FROM {$tables}" . $where_text . $grouping_text . $sort_text; $rows = $db->arrayQuery($sqlText, $db_params); // Indicate that a DB error occured. if ($rows === false) { return null; } $objectList = eZPersistentObject::handleRows($rows, $class_name, $asObject); return $objectList; }
/** * Fetch all Newsletter user with extended field * whith option to Filter data * * * @param array $filterArray condtions which will be combined with 'AND' * @param integer $limit * @param integer $offset * @param boolean $asObject * @return array with CjwNewsletterUser objects */ static function fetchByFilter($filterInternalArray, $filterExternalArray, $limit = 50, $offset = 0, $asObject = true, $isCount = false) { // var_dump( $filterArray ); /* -- alle Frauen -- zw 30 und 40 Jahre -- die 'sport' aboniert haben -- plz bereich 9xxxx -- SELECT count( cjwnl_subscription.id ) SELECT cjwnl_subscription.* FROM cjwnl_user, cjwnl_subscription -- , clubuser WHERE cjwnl_user.id = cjwnl_subscription.newsletter_user_id -- AND cjwnl_user.external_user_id = clubuser.id AND cjwnl_subscription.list_contentobject_id IN ( '109' ) -- AND cjwnl_user.email like '%@%gmx.de' -- AND clubuser_optin.name = 'Oldie 95 Newsletter' -- AND clubuser.anrede = 'Frau' AND cjwnl_user.salutation = 2 -- filter auf externe tabellen nicht cjwnl -- nur nutzen wenn ein externer Filter ausgewählt wurde AND EXISTS ( SELECT clubuser.id AS external_user_id -- , clubuser.* FROM clubuser_optin, clubuser_optin_relation, clubuser WHERE clubuser.id = clubuser_optin_relation.clubuserid AND clubuser_optin_relation.optinid = clubuser_optin.id AND cjwnl_user.external_user_id = clubuser.id -- AND clubuser_optin.name = 'Oldie 95 Newsletter' -- AND clubuser.anrede = 'Frau' -- AND cjwnl_user.salutation = 2 -- age 30 - 40 AND clubuser.geburtsdatum <= DATE_SUB(curdate(), INTERVAL 30 YEAR) AND clubuser.geburtsdatum >= DATE_SUB(curdate(), INTERVAL 40 YEAR) -- optin sport (35) -- AND clubuser_optin.name = 'sport' AND clubuser_optin.id = 35 -- plz 9x AND clubuser.plz like '9%' ); */ $db = eZDB::instance(); $field_filters = null; $conditions = null; $sorts = null; // $limit = null; // $asObject = true; $grouping = false; $custom_fields = null; $custom_tables = null; $custom_conds = null; $def = self::definition(); $fields = $def["fields"]; $tables = $def["name"]; $class_name = $def["class_name"]; if ((int) $limit != 0) { $limit = array('limit' => $limit, 'offset' => $offset); } $sqlInternal = self::getFilterInternalSql($filterInternalArray); $sqlExternalCondAndString = self::getFilterExternalSql($filterExternalArray); $sql = "{$sqlInternal}\n {$sqlExternalCondAndString}"; eZDebug::writeDebug($sql); //$db->arrayQuery( $sql ); if ($isCount) { $rows = $db->arrayQuery($sql); return $rows[0]['count']; } else { $rows = $db->arrayQuery($sql, $limit); $objectList = eZPersistentObject::handleRows($rows, $class_name, $asObject); return $objectList; } /* $objectList = eZPersistentObject::fetchObjectList( self::definition(), $field_filters, $conds, $sorts, $limit, $asObject, $grouping, $custom_fields, $custom_tables, $custom_conds ); return $objectList; */ }
static public function makeList( $rows ) { if ( !is_array( $rows ) || count( $rows ) == 0 ) return array(); $list = array(); $maxNumberOfLanguages = eZContentLanguage::maxCount(); foreach ( $rows as $row ) { $row['always_available'] = $row['lang_mask'] % 2; $mask = $row['lang_mask'] & ~1; for ( $i = 1; $i < $maxNumberOfLanguages; ++$i ) { $newMask = (1 << $i); if ( ($newMask & $mask) > 0 ) { $row['lang_mask'] = (1 << $i); $list[] = $row; } } } $objectList = eZPersistentObject::handleRows( $list, 'eZPathElement', true ); return $objectList; }
/** * Returns the next queued item, excluding those for which another version of the same content is being published. * @return ezpContentPublishingProcess|false */ private static function getNextItem() { $pendingStatusValue = ezpContentPublishingProcess::STATUS_PENDING; $workingStatusValue = ezpContentPublishingProcess::STATUS_WORKING; $sql = <<<SQL SELECT * FROM ezpublishingqueueprocesses p, ezcontentobject_version v WHERE p.status = {$pendingStatusValue} AND p.ezcontentobject_version_id = v.id AND v.contentobject_id NOT IN ( SELECT v.contentobject_id FROM ezpublishingqueueprocesses p, ezcontentobject_version v WHERE p.ezcontentobject_version_id = v.id AND p.status = {$workingStatusValue} ) ORDER BY p.created, p.ezcontentobject_version_id ASC SQL; $db = eZDB::instance(); $rows = $db->arrayQuery($sql, array('offset' => 0, 'limit' => 1)); if (count($rows) == 0) { return false; } /** @var ezpContentPublishingProcess[] $persistentObjects */ $persistentObjects = eZPersistentObject::handleRows($rows, 'ezpContentPublishingProcess', true); return $persistentObjects[0]; }
/** * Fetches block attached to this zone * * Only blocks that matches the current enviornment bitmask will be * returned. * * @param int $currentEnvId Int of current enviornment * @param string $clusterIdentifier * @return MMBlock[] Array */ public function fetchBlocks( $currentEnvId, $clusterIdentifier ) { $db = MMDB::instance(); $query = "SELECT block.* FROM mm_block AS block JOIN mm_homepage_zone_has_block AS join_tbl ON join_tbl.block_id = block.id WHERE join_tbl.zone_id = {$this->ID} AND join_tbl.environment & {$currentEnvId} AND join_tbl.cluster_identifier = '{$clusterIdentifier}' ORDER BY join_tbl.position"; $rows = $db->arrayQuery( $query ); if ( $rows && count( $rows ) >= 1 ) return eZPersistentObject::handleRows( $rows, "MMBlock", true ); }
/** * Fetches all blocks related to this page. * * The results are sorted by the 'position' coloumn on the M2M table. * * @param string $blockType The block type (COLUMN or SCRIPT) * @return MMStaticPageBlock[] **/ public function fetchBlocks( $blockType ) { $db = MMDB::instance(); $blockType = $db->escapeString( $blockType ); $query = "SELECT block.* FROM mm_static_page_block AS block JOIN mm_static_page_has_block AS join_tbl ON join_tbl.static_block_id = block.id WHERE join_tbl.static_page_id = {$this->ID} AND block.block_type = '{$blockType}' ORDER BY join_tbl.position"; $rows = $db->arrayQuery( $query ); if ( $rows && count( $rows ) >= 1 ) return eZPersistentObject::handleRows( $rows, "MMStaticPageBlock", true ); return false; }
static function fetchAllClasses($asObject = true, $includeFilter = true, $groupList = false) { $filterTableSQL = ''; $filterSQL = ''; if (is_array($groupList)) { $filterTableSQL = ', ezcontentclass_classgroup ccg'; $filterSQL = " AND" . " cc.id = ccg.contentclass_id AND" . " ccg.group_id "; $groupText = implode(', ', $groupList); if ($includeFilter) { $filterSQL .= "IN ( {$groupText} )"; } else { $filterSQL .= "NOT IN ( {$groupText} )"; } } $classNameFilter = eZContentClassName::sqlFilter('cc'); $classList = array(); $db = eZDb::instance(); // If $asObject is true we fetch all fields in class $fields = $asObject ? "cc.*" : "cc.id, {$classNameFilter['nameField']}"; $rows = $db->arrayQuery("SELECT DISTINCT {$fields} " . "FROM ezcontentclass cc{$filterTableSQL}, {$classNameFilter['from']} " . "WHERE cc.version = " . eZContentClass::VERSION_STATUS_DEFINED . "{$filterSQL} AND {$classNameFilter['where']}" . "ORDER BY {$classNameFilter['nameField']} ASC"); $classList = eZPersistentObject::handleRows($rows, 'eZContentClass', $asObject); return $classList; }
public static function makeList($rows) { if (!is_array($rows) || count($rows) == 0) { return array(); } $list = array(); foreach ($rows as $row) { $row['always_available'] = $row['lang_mask'] % 2; $mask = $row['lang_mask'] & ~1; for ($i = 1; $i < 30; ++$i) { $newMask = 1 << $i; if (($newMask & $mask) > 0) { $row['lang_mask'] = 1 << $i; $list[] = $row; } } } $objectList = eZPersistentObject::handleRows($list, 'eZPathElement', true); return $objectList; }
static function fetchUserClassList($asObject = false, $fields = false) { // Get names of user classes if (!$asObject and is_array($fields) and !empty($fields)) { $fieldsFilter = ''; $i = 0; foreach ($fields as $fieldName) { if ($i > 0) { $fieldsFilter .= ', '; } $fieldsFilter .= 'ezcontentclass.' . $fieldName; $i++; } } else { $fieldsFilter = 'ezcontentclass.*'; } $db = eZDB::instance(); $userClasses = $db->arrayQuery("SELECT {$fieldsFilter}\n FROM ezcontentclass, ezcontentclass_attribute\n WHERE ezcontentclass.id = ezcontentclass_attribute.contentclass_id AND\n ezcontentclass.version = " . eZContentClass::VERSION_STATUS_DEFINED . " AND\n ezcontentclass_attribute.version = 0 AND\n ezcontentclass_attribute.data_type_string = 'ezuser'"); return eZPersistentObject::handleRows($userClasses, "eZContentClass", $asObject); }
static function subTree($parameters = array()) { $parameters = array_merge(array('parent_group_id' => false, 'depth' => false, 'sort_by' => false, 'as_object' => true, 'offset' => false, 'limit' => false), $parameters); $parentGroupID = $parameters['parent_group_id']; $depth = $parameters['depth']; $asObject = $parameters['as_object']; $offset = $parameters['offset']; $limit = $parameters['limit']; $group = null; if ($parentGroupID > 0) { $group = eZCollaborationGroup::fetch($parentGroupID); } $sortCount = 0; $sortList = $parameters['sort_by']; if (is_array($sortList) and count($sortList) > 0) { if (count($sortList) > 1 and !is_array($sortList[0])) { $sortList = array($sortList); } } if ($sortList !== false) { $sortingFields = ''; foreach ($sortList as $sortBy) { if (is_array($sortBy) and count($sortBy) > 0) { if ($sortCount > 0) { $sortingFields .= ', '; } $sortField = $sortBy[0]; switch ($sortField) { case 'path': $sortingFields .= 'path_string'; break; case 'created': $sortingFields .= 'created'; break; case 'modified': $sortingFields .= 'modified'; break; case 'depth': $sortingFields .= 'depth'; break; case 'priority': $sortingFields .= 'priority'; break; case 'title': $sortingFields .= 'title'; break; default: eZDebug::writeWarning('Unknown sort field: ' . $sortField, __METHOD__); continue; } $sortOrder = true; // true is ascending if (isset($sortBy[1])) { $sortOrder = $sortBy[1]; } $sortingFields .= $sortOrder ? " ASC" : " DESC"; ++$sortCount; } } } if ($sortCount == 0) { $sortingFields = " path_string ASC"; } $pathString = ''; if ($group !== null) { $pathString = $group->attribute('path_string'); } $depthSQL = ""; if ($depth !== false) { $depthSQL = "depth <= '{$depth}' AND"; } $pathSQL = ''; if ($pathString != '') { $pathSQL = "path_string like '{$pathString}%' AND"; } $user = eZUser::currentUser(); $userID = $user->attribute('contentobject_id'); $sql = "SELECT *\n FROM\n ezcollab_group\n WHERE\n {$pathSQL}\n {$depthSQL}\n id != '{$parentGroupID}' AND\n user_id = '{$userID}'\n ORDER BY {$sortingFields}"; $db = eZDB::instance(); $sqlParameters = array(); if ($offset !== false and $limit !== false) { $sqlParameters['offset'] = $offset; $sqlParameters['limit'] = $limit; } $groupListArray = $db->arrayQuery($sql, $sqlParameters); $returnGroupList = eZPersistentObject::handleRows($groupListArray, 'eZCollaborationGroup', $asObject); eZDebugSetting::writeDebug('collaboration-group-tree', $returnGroupList); return $returnGroupList; }
static function fetchListTool($parameters = array(), $asCount) { $parameters = array_merge(array('as_object' => true, 'offset' => false, 'parent_group_id' => false, 'limit' => false, 'is_active' => null, 'is_read' => null, 'status' => false, 'sort_by' => false), $parameters); $asObject = $parameters['as_object']; $offset = $parameters['offset']; $limit = $parameters['limit']; $statusTypes = $parameters['status']; $isRead = $parameters['is_read']; $isActive = $parameters['is_active']; $parentGroupID = $parameters['parent_group_id']; $sortText = ''; if (!$asCount) { $sortCount = 0; $sortList = $parameters['sort_by']; if (is_array($sortList) and count($sortList) > 0) { if (count($sortList) > 1 and !is_array($sortList[0])) { $sortList = array($sortList); } } if ($sortList !== false) { $sortingFields = ''; foreach ($sortList as $sortBy) { if (is_array($sortBy) and count($sortBy) > 0) { if ($sortCount > 0) { $sortingFields .= ', '; } $sortField = $sortBy[0]; switch ($sortField) { case 'created': $sortingFields .= 'ezcollab_item_group_link.created'; break; case 'modified': $sortingFields .= 'ezcollab_item_group_link.modified'; break; default: eZDebug::writeWarning('Unknown sort field: ' . $sortField, __METHOD__); continue; } $sortOrder = true; // true is ascending if (isset($sortBy[1])) { $sortOrder = $sortBy[1]; } $sortingFields .= $sortOrder ? ' ASC' : ' DESC'; ++$sortCount; } } } if ($sortCount == 0) { $sortingFields = ' ezcollab_item_group_link.modified DESC'; } $sortText = "ORDER BY {$sortingFields}"; } $parentGroupText = ''; if ($parentGroupID > 0) { $parentGroupText = "ezcollab_item_group_link.group_id = '{$parentGroupID}' AND"; } $isReadText = ''; if ($isRead !== null) { $isReadValue = $isRead ? 1 : 0; $isReadText = "ezcollab_item_status.is_read = '{$isReadValue}' AND"; } $isActiveText = ''; if ($isActive !== null) { $isActiveValue = $isActive ? 1 : 0; $isActiveText = "ezcollab_item_status.is_active = '{$isActiveValue}' AND"; } $userID = eZUser::currentUserID(); $statusText = ''; if ($statusTypes === false) { $statusTypes = array(self::STATUS_ACTIVE, self::STATUS_INACTIVE); } $statusText = implode(', ', $statusTypes); if ($asCount) { $selectText = 'count( ezcollab_item.id ) as count'; } else { $selectText = 'ezcollab_item.*, ezcollab_item_status.is_read, ezcollab_item_status.is_active, ezcollab_item_status.last_read'; } $sql = "SELECT {$selectText}\n FROM\n ezcollab_item,\n ezcollab_item_status,\n ezcollab_item_group_link\n WHERE ezcollab_item.status IN ( {$statusText} ) AND\n {$isReadText}\n {$isActiveText}\n ezcollab_item.id = ezcollab_item_status.collaboration_id AND\n ezcollab_item.id = ezcollab_item_group_link.collaboration_id AND\n {$parentGroupText}\n ezcollab_item_status.user_id = '{$userID}' AND\n ezcollab_item_group_link.user_id = '{$userID}'\n {$sortText}"; $db = eZDB::instance(); if (!$asCount) { $sqlParameters = array(); if ($offset !== false and $limit !== false) { $sqlParameters['offset'] = $offset; $sqlParameters['limit'] = $limit; } $itemListArray = $db->arrayQuery($sql, $sqlParameters); foreach ($itemListArray as $key => $value) { $itemData =& $itemListArray[$key]; $statusObject = eZCollaborationItemStatus::create($itemData['id'], $userID); $statusObject->setAttribute('is_read', $itemData['is_read']); $statusObject->setAttribute('is_active', $itemData['is_active']); $statusObject->setAttribute('last_read', $itemData['last_read']); $statusObject->updateCache(); } $returnItemList = eZPersistentObject::handleRows($itemListArray, 'eZCollaborationItem', $asObject); eZDebugSetting::writeDebug('collaboration-item-list', $returnItemList); return $returnItemList; } else { $itemCount = $db->arrayQuery($sql); return $itemCount[0]['count']; } }
static function fetchListByUserID($userID, $offset = 0, $limit = 10, $status = eZXApproveStatus::StatusInApproval) { $db = eZDB::instance(); $sql = 'SELECT DISTINCT ezx_approve_status.* FROM ezx_approve_status, ezx_approve_status_user_link WHERE ezx_approve_status_user_link.user_id = \'' . $db->escapeString($userID) . '\' AND ezx_approve_status.id = ezx_approve_status_user_link.approve_id AND ezx_approve_status.approve_status = \'' . $db->escapeString($status) . '\''; $result = $db->arrayQuery($sql, array('limit' => $limit, 'offset' => $offset)); if (!$result || count($result) == 0) { return false; } return eZPersistentObject::handleRows($result, 'eZXApproveStatus', true); }