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