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); } }
/** * 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; } }
public function info() { ClassLoader::importNow("application.helper.getDateFromString"); $product = Product::getInstanceById($this->request->get('id'), ActiveRecord::LOAD_DATA, array('DefaultImage' => 'ProductImage', 'Manufacturer', 'Category')); $thisMonth = date('m'); $lastMonth = date('Y-m', strtotime(date('m') . '/15 -1 month')); $periods = array('_last_1_h' => "-1 hours | now", '_last_3_h' => "-3 hours | now", '_last_6_h' => "-6 hours | now", '_last_12_h' => "-12 hours | now", '_last_24_h' => "-24 hours | now", '_last_3_d' => "-3 days | now", '_this_week' => "w:Monday | now", '_last_week' => "w:Monday ~ -1 week | w:Monday", '_this_month' => $thisMonth . "/1 | now", '_last_month' => $lastMonth . "-1 | " . $lastMonth . "/1", '_this_year' => "January 1 | now", '_last_year' => "January 1 last year | January 1", '_overall' => "now | now"); $purchaseStats = array(); $prevCount = 0; foreach ($periods as $key => $period) { list($from, $to) = explode(' | ', $period); $cond = new EqualsCond(new ARFieldHandle('OrderedItem', 'productID'), $product->getID()); if ('now' != $from) { $cond->addAND(new EqualsOrMoreCond(new ARFieldHandle('CustomerOrder', 'dateCompleted'), getDateFromString($from))); } if ('now' != $to) { $cond->addAnd(new EqualsOrLessCond(new ARFieldHandle('CustomerOrder', 'dateCompleted'), getDateFromString($to))); } $f = new ARSelectFilter($cond); $f->mergeCondition(new EqualsCond(new ARFieldHandle('CustomerOrder', 'isFinalized'), true)); $f->removeFieldList(); $f->addField('SUM(OrderedItem.count)'); $query = new ARSelectQueryBuilder(); $query->setFilter($f); $query->includeTable('OrderedItem'); $query->joinTable('CustomerOrder', 'OrderedItem', 'ID', 'customerOrderID'); if (($count = array_shift(array_shift(ActiveRecordModel::getDataBySql($query->getPreparedStatement(ActiveRecord::getDBConnection()))))) && ($count > $prevCount || '_overall' == $key)) { $purchaseStats[$key] = $count; } if ($count > $prevCount) { $prevCount = $count; } } $response = new ActionResponse(); $response->set('together', $product->getProductsPurchasedTogether(10)); $response->set('product', $product->toArray()); $response->set('purchaseStats', $purchaseStats); return $response; }
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); } } }