public function getChildProductIDs() { $f = new ARSelectFilter(new INCond(new ARFieldHandle('Product', 'parentID'), $this->getRecordIDs())); $q = new ARSelectQueryBuilder($f); $q->includeTable('Product'); $q->setFilter($f); $q->removeFieldList(); $q->addField('ID'); $q->addField('parentID'); $ids = array(); foreach (ActiveRecordModel::getDataByQuery($q) as $row) { $ids[$row['parentID']][] = $row['ID']; } return $ids; }
private function getPage($class, $page, ARSelectFilter $f, $fields) { $f->setLimit(self::MAX_URLS, $page * self::MAX_URLS); $query = new ARSelectQueryBuilder(); $query->setFilter($f); $query->includeTable($class); foreach ($fields as $field) { $query->addField($field); } return ActiveRecord::fetchDataFromDB($query); }
public function getCountByManufacturers($includeAppliedFilters) { $selectFilter = $this->productFilter->getSelectFilter(!$includeAppliedFilters); $selectFilter->removeFieldList(); $selectFilter->setLimit(0); $selectFilter->setOrder(new ARExpressionHandle('cnt'), 'DESC'); $selectFilter->setGrouping(new ARFieldHandle('Product', 'manufacturerID')); $selectFilter->mergeHavingCondition(new MoreThanCond(new ARExpressionHandle('cnt'), 0)); $selectFilter->mergeHavingCondition(new NotEqualsCond(new ARFieldHandle('Manufacturer', 'name'), '')); $selectFilter->resetOrder(); $selectFilter->setOrder(new ARFieldHandle('Manufacturer', 'name')); $query = new ARSelectQueryBuilder(); $query->includeTable('Product'); $query->joinTable('Manufacturer', 'Product', 'ID', 'manufacturerID'); $query->joinTable('Category', 'Product', 'ID', 'categoryID'); $query->addField('COUNT(Product.manufacturerID)', null, 'cnt'); $query->addField('ID', 'Manufacturer'); $query->addField('name', 'Manufacturer'); $query->setFilter($selectFilter); ActiveRecordModel::getApplication()->processInstancePlugins('manufacturerCountQuery', $query); $data = ActiveRecordModel::getDataBySQL($query->getPreparedStatement(ActiveRecord::getDBConnection())); return $data; }
public function bestsellingProductsBlock() { ClassLoader::import('application.model.product.ProductFilter'); $cache = $this->application->getCache(); $key = array('bestsellers', $this->getCategory()->getID() . '_' . $days); if (!$cache->get($key)) { $category = $this->getCategory(); $filter = new ProductFilter($category, new ARSelectFilter()); $filter->includeSubcategories(); $filter->setEnabledOnly(); $selectFilter = $filter->getSelectFilter(); $selectFilter->setLimit($this->config->get('BESTSELLING_ITEMS_COUNT')); $selectFilter->setOrder(new ARExpressionHandle('cnt'), 'DESC'); $q = new ARSelectQueryBuilder(); $q->includeTable('Product'); $q->joinTable('Category', 'Product', 'ID', 'categoryID'); $q->addField('Product.ID'); $q->addField(new ARExpressionHandle('(SELECT SUM(count) FROM OrderedItem LEFT JOIN CustomerOrder ON OrderedItem.customerOrderID=CustomerOrder.ID WHERE productID=Product.ID AND CustomerOrder.isPaid=1 AND CustomerOrder.dateCompleted > "' . ARSerializableDateTime::createFromTimeStamp(strtotime('-' . $this->config->get('BESTSELLING_ITEMS_DAYS') . ' days')) . '")'), null, 'cnt'); $q->setFilter($selectFilter); $cache->set($key, ActiveRecord::getDataByQuery($q)); } $products = $cache->get($key); if (!$products) { return; } $ids = array(); foreach ($products as $id) { $ids[] = $id['ID']; } $products = ActiveRecord::getRecordSetArray('Product', select(IN('Product.ID', $ids)), array('DefaultImage' => 'ProductImage')); ProductPrice::loadPricesForRecordSetArray($products); if ($products) { return new BlockResponse('products', $products); } }
public function autoComplete() { $f = new ARSelectFilter(); $f->setLimit(20); $resp = array(); $field = $this->request->get('field'); if (in_array($field, array('sku', 'URL', 'keywords'))) { $c = new LikeCond(new ARFieldHandle('Product', $field), $this->request->get($field) . '%'); $f->setCondition($c); $f->setOrder(new ARFieldHandle('Product', $field), 'ASC'); $query = new ARSelectQueryBuilder(); $query->setFilter($f); $query->includeTable('Product'); $query->addField('DISTINCT(Product.' . $field . ')'); $results = ActiveRecordModel::getDataBySQL($query->createString()); foreach ($results as $value) { $resp[] = $value[$field]; } } else { if ('name' == $field) { $c = new LikeCond(new ARFieldHandle('Product', $field), '%:"' . $this->request->get($field) . '%'); $f->setCondition($c); $locale = $this->locale->getLocaleCode(); $langCond = new LikeCond(Product::getLangSearchHandle(new ARFieldHandle('Product', 'name'), $locale), $this->request->get($field) . '%'); $c->addAND($langCond); $f->setOrder(Product::getLangSearchHandle(new ARFieldHandle('Product', 'name'), $locale), 'ASC'); $results = ActiveRecordModel::getRecordSet('Product', $f); foreach ($results as $value) { $resp[$value->getValueByLang('name', $locale, Product::NO_DEFAULT_VALUE)] = true; } $resp = array_keys($resp); } else { if ('specField_' == substr($field, 0, 10)) { list($foo, $id) = explode('_', $field); $handle = new ARFieldHandle('SpecificationStringValue', 'value'); $locale = $this->locale->getLocaleCode(); $searchHandle = MultiLingualObject::getLangSearchHandle($handle, $locale); $f->setCondition(new EqualsCond(new ARFieldHandle('SpecificationStringValue', 'specFieldID'), $id)); $f->mergeCondition(new LikeCond($handle, '%:"' . $this->request->get($field) . '%')); $f->mergeCondition(new LikeCond($searchHandle, $this->request->get($field) . '%')); $f->setOrder($searchHandle, 'ASC'); $results = ActiveRecordModel::getRecordSet('SpecificationStringValue', $f); foreach ($results as $value) { $resp[$value->getValueByLang('value', $locale, Product::NO_DEFAULT_VALUE)] = true; } $resp = array_keys($resp); } } } return new AutoCompleteResponse($resp); }
public function getProductCount(ProductFilter $productFilter) { $query = new ARSelectQueryBuilder(); $query->includeTable('Product'); $query->addField('COUNT(*) AS cnt'); $filter = $this->getProductsFilter($productFilter); $filter->setLimit(0); $query->setFilter($filter); $data = ActiveRecord::getDataBySQL($query->getPreparedStatement(ActiveRecord::getDBConnection())); return $data[0]['cnt']; }
private function getOrderArray(ARSet $orders) { $orderArray = $orders->toArray(); $ids = array(); foreach ($orderArray as $key => $order) { $ids[$order['ID']] = $key; } ClassLoader::import('application.model.order.OrderNote'); $f = new ARSelectFilter(new INCond(new ARFieldHandle('OrderNote', 'orderID'), empty($ids) ? array(-1) : array_keys($ids))); $f->mergeCondition(new EqualsCond(new ARFieldHandle('OrderNote', 'isAdmin'), 1)); $f->mergeCondition(new EqualsCond(new ARFieldHandle('OrderNote', 'isRead'), 0)); $f->setGrouping(new ARFieldHandle('OrderNote', 'orderID')); $query = new ARSelectQueryBuilder(); $query->setFilter($f); $query->includeTable('OrderNote'); $query->removeFieldList(); $query->addField('COUNT(*)', null, 'cnt'); $query->addField('orderID'); foreach (ActiveRecordModel::getDataBySQL($query->getPreparedStatement(ActiveRecord::getDBConnection())) as $res) { $orderArray[$ids[$res['orderID']]]['unreadMessageCount'] = $res['cnt']; } return $orderArray; }
/** * Narrow search results by categories */ private function getSubcategoriesBySearchQuery(ARSelectFilter $selectFilter, $subCategories) { if (count($subCategories) > 0) { $case = new ARCaseHandle(); $index = array(); foreach ($subCategories as $key => $cat) { if (Category::ROOT_ID == $cat['ID']) { continue; } $cond = new EqualsOrMoreCond(new ARFieldHandle('Category', 'lft'), $cat['lft']); $cond->addAND(new EqualsOrLessCond(new ARFieldHandle('Category', 'rgt'), $cat['rgt'])); $case->addCondition($cond, new ARExpressionHandle($cat['ID'])); $index[$cat['ID']] = $key; } $query = new ARSelectQueryBuilder(); $query->includeTable('Product'); $filter = clone $selectFilter; $filter->setLimit(0); $filter->resetOrder(); $filter->setOrder(new ARExpressionHandle('cnt'), 'DESC'); $filter->setGrouping(new ARExpressionHandle('ID')); foreach ($this->filters as $f) { $f->defineJoin($filter); } $query->setFilter($filter); $query->addField($case->toString(), null, 'ID'); $query->addField('COUNT(*)', null, 'cnt'); $query->joinTable('Category', 'Product', 'ID', 'categoryID'); $query->joinTable('ProductPrice', 'Product', 'productID AND (ProductPrice.currencyID = "' . $this->application->getDefaultCurrencyCode() . '")', 'ID'); $count = $query->getPreparedStatement(ActiveRecord::getDBConnection())->executeQuery(); $categoryNarrow = array(); foreach ($count as $cat) { if (!isset($index[$cat['ID']])) { continue; } $data = $subCategories[$index[$cat['ID']]]; $data['searchCount'] = $cat['cnt']; $categoryNarrow[] = $data; } return $categoryNarrow; } }
protected static function joinReferencedTables(ARSchema $schema, ARSelectQueryBuilder $query, &$loadReferencedRecords = false) { // do not use auto-references for single-table one level joins if (!is_string($loadReferencedRecords)) { $loadReferencedRecords = self::addAutoReferences($schema, $loadReferencedRecords); } $tables = is_array($loadReferencedRecords) ? self::array_invert($loadReferencedRecords) : $loadReferencedRecords; $referenceList = $schema->getReferencedForeignKeyList(); $schemaName = $schema->getName(); foreach ($referenceList as $name => $field) { $foreignClassName = $field->getForeignClassName(); $tableAlias = $field->getReferenceName(); $foreignSchema = self::getSchemaInstance($foreignClassName); $foreignTableName = $foreignSchema->getName(); $aliasParts = explode('_', $tableAlias); $aliasName = isset($aliasParts[1]) ? $aliasParts[1] : ''; $isSameSchema = $schema === $foreignSchema; $notRequiredForInclusion = is_array($tables) && !isset($tables[$foreignClassName]); $isAliasSpecified = is_array($tables) && isset($tables[$foreignClassName]) && !is_numeric($tables[$foreignClassName]); if ($isAliasSpecified) { $classNamesDoNotMatch = $tables[$foreignClassName] != $aliasName; $notReferencedAsArray = !is_array($tables[$foreignClassName]); $notInReferencedArray = is_array($tables[$foreignClassName]) && !in_array($aliasName, $tables[$foreignClassName]); } if ($tables !== $schemaName) { if ($isSameSchema || $notRequiredForInclusion || $isAliasSpecified && $classNamesDoNotMatch && ($notReferencedAsArray || $notInReferencedArray) || is_string($tables) && $tables != $schemaName) { continue; } } if (!$query->getJoinsByClassName($foreignTableName)) { $tableAlias = $foreignTableName; } $joined = $query->joinTable($foreignTableName, $schemaName, $field->getForeignFieldName(), $name, $tableAlias); if ($joined) { foreach ($foreignSchema->getFieldList() as $foreignFieldName => $foreignField) { $query->addField($foreignFieldName, $tableAlias, $tableAlias . "_" . $foreignFieldName); } self::getLogger()->logQuery('Joining ' . $foreignClassName . ' on ' . $schemaName); self::joinReferencedTables($foreignSchema, $query, $loadReferencedRecords); } } }
protected function addDateFieldToQuery(ARSelectQueryBuilder $q, $sqlFunction, $alias) { $f = $q->getFilter(); $q->addField(new ARExpressionHandle($sqlFunction), null, $alias); if (self::PIE != $this->chartType) { $f->setGrouping(new ARExpressionHandle($alias)); } $f->setOrder(new ARExpressionHandle($alias)); }