public function index()
{
// get filter to select manufacturers of active products only
$rootCat = Category::getRootNode();
$f = new ARSelectFilter();
$productFilter = new ProductFilter($rootCat, $f);
$ids = $counts = array();
foreach (ActiveRecordModel::getDataBySQL('SELECT DISTINCT(manufacturerID), COUNT(*) AS cnt FROM Product ' . $f->createString() . ' GROUP BY manufacturerID') as $row) {
$ids[] = $row['manufacturerID'];
$counts[$row['manufacturerID']] = $row['cnt'];
}
$f = new ARSelectFilter(new InCond(new ARFieldHandle('Manufacturer', 'ID'), $ids));
$f->mergeCondition(new NotEqualsCond(new ARFieldHandle('Manufacturer', 'name'), ''));
$f->setOrder(new ARFieldHandle('Manufacturer', 'name'));
$manufacturers = ActiveRecordModel::getRecordSetArray('Manufacturer', $f);
foreach ($manufacturers as &$manufacturer) {
$manufacturer['url'] = $this->getManufacturerFilterUrl($manufacturer);
}
$this->addBreadCrumb($this->translate('_manufacturers'), '');
$response = new ActionResponse();
$response->setReference('manufacturers', $manufacturers);
$response->set('counts', $counts);
$response->set('rootCat', $rootCat->toArray());
return $response;
}
protected function getDownloadCounts($itemIDs)
{
if (!$itemIDs) {
return array();
}
$sql = 'SELECT orderedItemID, SUM(timesDownloaded) AS cnt FROM OrderedFile WHERE orderedItemID IN (' . implode(',', $itemIDs) . ') GROUP BY orderedItemID';
$out = array();
foreach (ActiveRecordModel::getDataBySQL($sql) as $item) {
$out[$item['orderedItemID']] = $item['cnt'];
}
return $out;
}
private function getRecipientCount($data)
{
$query = $this->getRecipientQuery($data, false);
if (!$query) {
return 0;
}
$query = 'SELECT COUNT(*) FROM (' . $query . ') AS recipCount';
return array_shift(array_shift(ActiveRecordModel::getDataBySQL($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;
}
private static function fetchRawSpecificationData($class, $objectIDs, $fullSpecification = false)
{
if (!$objectIDs) {
return array();
}
$fieldClass = call_user_func(array($class, 'getFieldClass'));
$groupClass = $fieldClass . 'Group';
$fieldColumn = call_user_func(array($fieldClass, 'getFieldIDColumnName'));
$objectColumn = call_user_func(array($fieldClass, 'getObjectIDColumnName'));
$stringClass = call_user_func(array($fieldClass, 'getStringValueClass'));
$numericClass = call_user_func(array($fieldClass, 'getNumericValueClass'));
$dateClass = call_user_func(array($fieldClass, 'getDateValueClass'));
$valueItemClass = call_user_func(array($fieldClass, 'getSelectValueClass'));
$valueClass = call_user_func(array($valueItemClass, 'getValueClass'));
$valueColumn = call_user_func(array($valueItemClass, 'getValueIDColumnName'));
$groupColumn = strtolower(substr($groupClass, 0, 1)) . substr($groupClass, 1) . 'ID';
$cond = '
LEFT JOIN
' . $fieldClass . ' ON ' . $fieldColumn . ' = ' . $fieldClass . '.ID
LEFT JOIN
' . $groupClass . ' ON ' . $fieldClass . '.' . $groupColumn . ' = ' . $groupClass . '.ID
WHERE
' . $objectColumn . ' IN (' . implode(', ', $objectIDs) . ')' . ($fullSpecification ? '' : ' AND ' . $fieldClass . '.isDisplayedInList = 1');
$group = $groupClass . '.position AS SpecFieldGroupPosition, ' . $groupClass . '.name AS SpecFieldGroupName, ';
$query = '
SELECT ' . $dateClass . '.*, NULL AS valueID, NULL AS specFieldValuePosition, ' . $group . $fieldClass . '.* /* as valueID */ FROM ' . $dateClass . ' ' . $cond . '
UNION
SELECT ' . $stringClass . '.*, NULL, NULL AS specFieldValuePosition, ' . $group . $fieldClass . '.* /* as valueID */ FROM ' . $stringClass . ' ' . $cond . '
UNION
SELECT ' . $numericClass . '.*, NULL, NULL AS specFieldValuePosition, ' . $group . $fieldClass . '.* /* as valueID */ FROM ' . $numericClass . ' ' . $cond . '
UNION
SELECT ' . $valueItemClass . '.' . $objectColumn . ', ' . $valueItemClass . '.' . $fieldColumn . ', ' . $valueClass . '.value, ' . $valueClass . '.ID, ' . $valueClass . '.position, null, null, ' . $group . $fieldClass . '.*
FROM ' . $valueItemClass . '
LEFT JOIN ' . $valueClass . ' ON ' . $valueItemClass . '.' . $valueColumn . ' = ' . $valueClass . '.ID
' . str_replace('ON ' . $fieldColumn, 'ON ' . $valueItemClass . '.' . $fieldColumn, $cond) . ' ORDER BY ' . $objectColumn . ', SpecFieldGroupPosition, position, specFieldValuePosition';
return ActiveRecordModel::getDataBySQL($query);
}
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);
}
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;
}
public function executeTextReplaceInSnapshot(ClonedStore $store)
{
$replace = array();
$f = select(eq(f('ClonedStoreRule.type'), ClonedStoreRule::TYPE_TEXT));
$f->setOrder(f('ClonedStoreRule.ID'));
foreach ($store->getRelatedRecordSet('ClonedStoreRule', $f) as $rule) {
list($entity, $field) = explode('.', $rule->field->get());
$ruleData = $rule->toArray();
$ruleData['entity'] = $entity;
$ruleData['field'] = $field;
$replace[$entity][] = $ruleData;
}
echo '<span style="font-size: 2px;">';
foreach ($replace as $table => $rules) {
echo '<!--';
var_dump($rules);
echo '-->';
flush();
//exit;
$this->flush($table);
$this->flush($rules);
if ('specField' == $table) {
$table = 'SpecificationStringValue';
}
$extraFields = '';
if (in_array($table, array('SpecificationStringValue', 'Product'))) {
foreach (ActiveRecordModel::getDataBySQL('SELECT * FROM ' . $this->importDatabase . '.SpecField') as $field) {
$extraFields .= ',(SELECT SpecificationStringValue.value FROM ' . $this->importDatabase . '.SpecificationStringValue WHERE productID=Product.ID AND SpecificationStringValue.specFieldID=' . $field['ID'] . ') AS field_' . $field['handle'];
}
}
foreach (array('name', 'longDescription', 'shortDescription') as $parentField) {
$extraFields .= ', IF(Product.parentID IS NOT NULL, ParentProduct.' . $parentField . ', Product.' . $parentField . ') AS ' . $parentField;
}
if ('SpecificationStringValue' == $table) {
$sql = 'SELECT Product.*, Product.ID AS productID, Product.parentID AS parentProductID, SpecField.ID AS specFieldID, (SELECT value FROM SpecificationStringValue WHERE SpecificationStringValue.productID=Product.ID AND SpecificationStringValue.specFieldID=SpecField.ID) AS value, (SELECT productID FROM SpecificationStringValue WHERE SpecificationStringValue.productID=Product.ID AND SpecificationStringValue.specFieldID=SpecField.ID) AS fieldValueExists' . $extraFields . ' FROM ' . $this->importDatabase . '.Product INNER JOIN ' . $this->importDatabase . '.SpecField LEFT JOIN ' . $this->importDatabase . '.Product AS ParentProduct ON (Product.parentID=ParentProduct.ID)';
//$sql = 'SELECT *' . $extraFields . ' FROM ' . $this->importDatabase . '.SpecField LEFT JOIN ' . $this->importDatabase . '.' . $table . ' LEFT JOIN Product ON productID=Product.ID';
} else {
if ('Product' == $table) {
$sql = 'SELECT Product.*' . $extraFields . ' FROM ' . $this->importDatabase . '.' . $table . ' LEFT JOIN Product AS ParentProduct ON (Product.parentID=ParentProduct.ID)';
} else {
$sql = 'SELECT * FROM ' . $this->importDatabase . '.' . $table;
}
}
$offset = 0;
$batch = 5000;
$modified = array();
do {
$select = $sql . ' LIMIT ' . $batch * $offset . ',' . $batch;
$offset++;
$rows = ActiveRecordModel::getDataBySQL($select);
foreach ($rows as $row) {
$original = $row;
$id = array();
$values = array();
foreach ($row as $key => $val) {
if (@unserialize($val)) {
$data = unserialize($val);
$value = array_shift($data);
} else {
$value = $val;
}
$values[$key] = $value;
}
if (!empty($modified[$values['ID']])) {
$values = array_merge($values, $modified[$values['ID']]);
}
$hasQuery = false;
foreach ($rules as $rule) {
$f = $rule['field'];
$identifier = $f;
// var_dump($f);
if ($rule['query']) {
$productID = 'SpecificationStringValue' == $table ? $original['productID'] : $original['ID'];
$count = ActiveRecordModel::getDataBySQL('SELECT COUNT(*) AS cnt FROM ' . $this->importDatabase . '.Product WHERE ID=' . $productID . ' AND ' . $rule['query']);
$count = array_pop($count);
if (!$count['cnt']) {
continue;
}
$hasQuery = true;
}
if ('SpecificationStringValue' == $table) {
if ($row['specFieldID'] != $f) {
continue;
} else {
$f = 'value';
}
if ($rule['field']) {
try {
$spFld = SpecField::getInstanceByID($rule['field'], true);
if ($spFld) {
$identifier = 'field_' . $spFld->handle->get();
}
} catch (Exception $e) {
$identifier = '';
}
}
}
if (@unserialize($row[$f])) {
$data = unserialize($row[$f]);
foreach ($data as $key => $value) {
$data[$key] = $this->replace($rule, $value, $values);
$values[$identifier] = $data[$key];
$modified[$values['ID']][$identifier] = $data[$key];
}
$row[$f] = serialize($data);
} else {
$row[$f] = $this->replace($rule, $row[$f], $values);
$values[$identifier] = $row[$f];
$modified[$values['ID']][$identifier] = $row[$f];
if ('SpecificationStringValue' == $table) {
$row[$f] = serialize(array('en' => $row[$f]));
}
}
}
$row = array_diff($row, $original);
if (!$row) {
continue;
}
foreach ($row as $field => $value) {
$value = "'" . addslashes($value) . "'";
if (substr($field, 0, 6) != 'field_') {
$row[$field] = $field . '=' . $value;
} else {
unset($row[$field]);
}
}
foreach ($original as $field => $value) {
if (strpos($field, 'ID') !== false && $value) {
$value = "'" . addslashes($value) . "'";
$id[] = $field . '=' . $value;
}
}
if ('SpecificationStringValue' == $table) {
$exists = $original['fieldValueExists'];
$row = array('value' => $row['value']);
$id = array('productID' => 'productID=' . $original['productID'], 'specFieldID' => 'specFieldID=' . $original['specFieldID']);
$row = array_merge($row, $id);
if ($exists) {
$query = 'UPDATE ' . $this->importDatabase . '.' . $table . ' SET ' . implode(', ', $row) . ' WHERE ' . implode(' AND ', $id);
} else {
$query = 'INSERT INTO ' . $this->importDatabase . '.' . $table . ' SET ' . implode(', ', $row);
}
if (17 == $original['specFieldID']) {
//echo '<pre>' . htmlspecialchars($query) . '</pre>' . '<Br /><Br />';
}
try {
ActiveRecordModel::executeUpdate($query);
} catch (Exception $e) {
echo '<div style="color:red;">' . htmlspecialchars($query) . '</div><Br /><Br />';
}
} else {
$query = 'UPDATE ' . $this->importDatabase . '.' . $table . ' SET ' . implode(', ', $row) . ' WHERE ' . implode(' AND ', $id);
ActiveRecordModel::executeUpdate($query);
}
echo '. ';
flush();
}
} while ($rows);
}
echo '</span>';
}
public static function getActiveProductManufacturerFirstLetters()
{
$letters = $ids = array();
$f = new ARSelectFilter();
$sql = !self::getApplication()->getConfig()->get('MANUFACTURER_PAGE_DISPLAY_ACTIVE') ? 'SELECT DISTINCT(ID) as manufacturerID FROM Manufacturer ' . $f->createString() . ' GROUP BY manufacturerID' : 'SELECT DISTINCT(manufacturerID) FROM Product ' . $f->createString() . ' GROUP BY manufacturerID';
foreach (ActiveRecordModel::getDataBySQL($sql) as $row) {
$ids[] = $row['manufacturerID'];
}
$f = new ARSelectFilter(new InCond(new ARFieldHandle('Manufacturer', 'ID'), $ids));
$f->addField('UPPER(LEFT(TRIM(Manufacturer.name),1))', '', 'FirstLetter');
$f->mergeCondition(new NotEqualsCond(new ARFieldHandle('Manufacturer', 'name'), ''));
$f->setOrder(new ARFieldHandle('Manufacturer', 'name'));
$manufacturers = ActiveRecordModel::getRecordSetArray('Manufacturer', $f);
foreach ($manufacturers as $item) {
$letters[$item['FirstLetter']] = $item['FirstLetter'];
}
return $letters;
}
