public function getCountByPrices($includeAppliedFilters) { // get price filters $k = 0; $filters = array(); $config = $this->application->getConfig(); while ($config->isValueSet('PRICE_FILTER_NAME_' . ++$k)) { if ($config->get('PRICE_FILTER_NAME_' . $k) && !is_array($config->get('PRICE_FILTER_NAME_' . $k))) { $from = $config->get('PRICE_FILTER_FROM_' . $k); $to = $config->get('PRICE_FILTER_TO_' . $k); if ($to) { $filters[$k] = array($from, $to); } } } if (!$filters) { return array(); } // get product counts $selectFilter = $this->productFilter->getSelectFilter(!$includeAppliedFilters); $selectFilter->removeFieldList(); $selectFilter->setLimit(0); $query = new ARSelectQueryBuilder(); $query->includeTable('Product'); $query->joinTable('ProductPrice', 'Product', 'productID AND (ProductPrice.currencyID = "' . $this->application->getDefaultCurrencyCode() . '")', 'ID'); $query->joinTable('Category', 'Product', 'ID', 'categoryID'); foreach ($filters as $key => $filter) { $query->addField('SUM(ProductPrice.price >= ' . $filter[0] . ' AND ProductPrice.price <= ' . $filter[1] . ')', null, $key); } $query->setFilter($selectFilter); $data = ActiveRecordModel::getDataBySQL($query->getPreparedStatement(ActiveRecordModel::getDBConnection())); //$data = array_diff($data[0], array(0)); return $data[0]; }
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; }
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; } }
public static function getDataByQuery(ARSelectQueryBuilder $query) { return self::getDataBySQL($query->getPreparedStatement(self::getDBConnection())); }