/**
* Generate filters according to specification fields names and types
*
* @role update
*
* @return JSONResponse
*/
public function generate()
{
$filters = array();
foreach (SpecField::getInstanceByID((int) $this->request->get('specFieldID'), true)->getValuesList() as $value) {
$filters[$value['ID']] = array('name' => $value['value'], 'specFieldValueID' => $value['ID']);
}
return new JSONResponse(array("filters" => $filters), 'success', $this->translate('_filters_were_successfully_generated'));
}
public function getNextProduct()
{
if (!$this->productSql) {
foreach ($this->languages as $code => $id) {
list($join[], $langs[]) = $this->joinProductFields($id, $code);
}
foreach ($this->attributes as $attr) {
$join[] = 'LEFT JOIN ' . $this->getTablePrefix() . 'extra_field_values AS extra_' . $attr['fieldid'] . ' ON (extra_' . $attr['fieldid'] . '.productid=' . $this->getTablePrefix() . 'products.productid AND extra_' . $attr['fieldid'] . '.fieldid=' . $attr['fieldid'] . ')';
$langs[] = 'extra_' . $attr['fieldid'] . '.value AS extrafield_' . $attr['fieldid'];
}
$this->productSql = 'SELECT ' . $this->getTablePrefix() . 'products.*, ' . $this->getTablePrefix() . 'products_categories.categoryid ' . ($langs ? ',' : '') . implode(', ', $langs) . ', (SELECT price FROM ' . $this->getTablePrefix() . 'pricing WHERE ' . $this->getTablePrefix() . 'pricing.productid=' . $this->getTablePrefix() . 'products.productid ORDER BY quantity ASC LIMIT 1) AS price FROM ' . $this->getTablePrefix() . 'products ' . implode(' ', $join) . ' LEFT JOIN ' . $this->getTablePrefix() . 'products_categories ON (' . $this->getTablePrefix() . 'products.productid=' . $this->getTablePrefix() . 'products_categories.productid AND ' . $this->getTablePrefix() . 'products_categories.main="Y")';
}
if (!($data = $this->loadRecord($this->productSql))) {
return null;
}
$rec = Product::getNewInstance(Category::getInstanceById($this->getRealId('Category', $data['categoryid'])));
$rec->setID($data['productid']);
$rec->keywords->set($data['keywords']);
$rec->setValueByLang('name', $this->defLang, $data['product']);
$rec->setValueByLang('longDescription', $this->defLang, $data['fulldescr']);
$rec->setValueByLang('shortDescription', $this->defLang, $data['descr']);
foreach ($this->languages as $code => $id) {
$rec->setValueByLang('name', $code, $data['name_' . $code]);
$rec->setValueByLang('longDescription', $code, $data['fulldescr_' . $code]);
$rec->setValueByLang('shortDescription', $code, $data['descr_' . $code]);
}
foreach ($this->attributes as $attr) {
if (!empty($data['extrafield_' . $attr['fieldid']])) {
$rec->setAttributeValueByLang(SpecField::getInstanceByID($this->getRealId('SpecField', $attr['fieldid']), SpecField::LOAD_DATA), $this->defLang, $data['extrafield_' . $attr['fieldid']]);
}
}
if ($data['manufacturerid']) {
$rec->manufacturer->set(Manufacturer::getInstanceById($this->getRealId('Manufacturer', $data['manufacturerid'])));
}
foreach (array('sku' => 'productcode', 'shippingWeight' => 'weight', 'stockCount' => 'avail', 'shippingSurchargeAmount' => 'shipping_freight', 'minimumQuantity' => 'min_amount', 'dateCreated' => 'add_date') as $lc => $xc) {
$rec->{$lc}->set($data[$xc]);
}
$rec->isEnabled->set('Y' == $data['forsale']);
$rec->setPrice($this->getDefaultCurrency(), $data['price']);
//images
$images = array_merge($this->getDataBySQL('SELECT * FROM ' . $this->getTablePrefix() . 'images_t WHERE id=' . $data['productid'] . ' ORDER BY orderby ASC'), $this->getDataBySQL('SELECT * FROM ' . $this->getTablePrefix() . 'images_d WHERE id=' . $data['productid'] . ' ORDER BY orderby ASC'));
foreach ($images as $image) {
$this->importProductImage($rec, $this->path . '/' . $image['image_path']);
}
$rec->rawData = $data;
return $rec;
}
protected function setGridResponse()
{
$res = parent::setGridResponse();
$displayedColumns = $res['displayedColumns'];
$availableColumns = $res['availableColumns'];
foreach ($displayedColumns as $column => $type) {
$parts = explode('.', $column);
if ('specField' == array_shift($parts)) {
$field = SpecField::getInstanceByID(array_shift($parts));
if ($field->isSelector()) {
$displayedColumns[$column] = $field->isMultiValue->get() ? 'multi-select' : 'select';
$values = array();
foreach ($field->getValuesList() as $value) {
$values[$value['ID']] = $value['value_lang'];
}
$availableColumns[$column]['values'] = $values;
}
}
}
$res['displayedColumns'] = $displayedColumns;
$res['availableColumns'] = $availableColumns;
return $res;
}
/**
* Validates filter group form
*
* @param array $values List of values to validate.
* @return array List of all errors
*/
private function validate($values = array(), $languageCodes)
{
$errors = array();
if (!isset($values['name']) || $values['name'][$languageCodes[0]] == '') {
$errors['name[' . $languageCodes[0] . ']'] = '_error_name_empty';
}
$specField = SpecField::getInstanceByID((int) $values['specFieldID']);
if (!$specField->isLoaded()) {
$specField->load();
}
if (isset($values['filters']) && !$specField->isSelector()) {
$filtersCount = count($values['filters']);
$i = 0;
foreach ($values['filters'] as $key => $v) {
$i++;
// If emty last new filter, ignore it
if ($filtersCount == $i && $v['name'][$languageCodes[0]] == '' && preg_match("/new/", $key)) {
continue;
}
switch ($specField->getFieldValue('type')) {
case SpecField::TYPE_NUMBERS_SIMPLE:
if (!isset($v['rangeStart']) || !is_numeric($v['rangeStart']) | !isset($v['rangeEnd']) || !is_numeric($v['rangeEnd'])) {
$errors['filters[' . $key . '][rangeStart]'] = '_error_filter_value_is_not_a_number';
}
break;
case SpecField::TYPE_TEXT_DATE:
if (!isset($v['rangeDateStart']) || !isset($v['rangeDateEnd']) || count($sdp = explode('-', $v['rangeDateStart'])) != 3 || count($edp = explode('-', $v['rangeDateEnd'])) != 3 || !checkdate($edp[1], $edp[2], $edp[0]) || !checkdate($sdp[1], $sdp[2], $sdp[0])) {
$errors['filters[' . $key . '][rangeDateStart_show]'] = '_error_illegal_date';
}
break;
}
if ($v['name'][$languageCodes[0]] == '') {
$errors['filters[' . $key . '][name][' . $languageCodes[0] . ']'] = '_error_filter_name_empty';
}
}
}
return $errors;
}
/**
* Apply selected product sort order to ARSelectFilter instance
*/
private function applySortOrder(ARSelectFilter $selectFilter, $order)
{
$dir = array_pop(explode('_', $order)) == 'asc' ? 'ASC' : 'DESC';
if (substr($order, 0, 12) == 'product_name') {
$selectFilter->setOrder(Product::getLangOrderHandle(new ARFieldHandle('Product', 'name')), $dir);
} else {
if (substr($order, 0, 5) == 'price') {
$selectFilter->setOrder(new ARFieldHandle('ProductPrice', 'price'), $dir);
$selectFilter->joinTable('ProductPrice', 'Product', 'productID AND (ProductPrice.currencyID = "' . $this->application->getDefaultCurrencyCode() . '")', 'ID');
} else {
if (substr($order, 0, 3) == 'sku') {
$selectFilter->setOrder(new ARFieldHandle('ProductPrice', 'price'), $dir);
$selectFilter->joinTable('ProductPrice', 'Product', 'productID AND (ProductPrice.currencyID = "' . $this->application->getDefaultCurrencyCode() . '")', 'ID');
} else {
if ('newest_arrivals' == $order) {
$selectFilter->setOrder(new ARFieldHandle('Product', 'dateCreated'), 'DESC');
} else {
if (in_array($order, array('rating', 'sku'))) {
$selectFilter->setOrder(new ARFieldHandle('Product', $order), $dir);
} else {
if ('sales_rank' == $order) {
Product::updateSalesRank();
$selectFilter->setOrder(new ARFieldHandle('Product', 'salesRank'), 'DESC');
} else {
if (is_numeric($fieldID = array_shift(explode('-', $order))) && !SpecField::getInstanceByID($fieldID, true)->isMultiValue->get()) {
$field = SpecField::getInstanceByID($fieldID);
$field->defineJoin($selectFilter);
$f = $field->getJoinAlias() . ($field->isSelector() ? '_value' : '') . '.value';
$selectFilter->setOrder(new ARExpressionHandle($f . ' IS NOT NULL'), 'DESC');
$selectFilter->setOrder(new ARExpressionHandle($f . ' != ""'), 'DESC');
$f = new ARExpressionHandle($f);
if ($field->isSelector()) {
$f = MultiLingualObject::getLangOrderHandle($f);
}
$selectFilter->setOrder($f, array_pop(explode('_', $order)) == 'desc' ? 'DESC' : 'ASC');
} else {
$selectFilter->setOrder(new ARFieldHandle('Product', 'isFeatured'), 'DESC');
$selectFilter->setOrder(new ARFieldHandle('Product', 'salesRank'), 'DESC');
$selectFilter->setOrder(new ARFieldHandle('Product', 'position'), 'DESC');
}
}
}
}
}
}
}
}
public function getNextProduct()
{
if (!$this->productSql) {
$fields = array($this->getTablePrefix() . 'products.*');
$join = array();
foreach ($this->attributes as $attr) {
$join[] = 'LEFT JOIN ' . $this->getTablePrefix() . 'product_options_values AS extra_' . $attr['optionID'] . ' ON (extra_' . $attr['optionID'] . '.productID=' . $this->getTablePrefix() . 'products.productID AND extra_' . $attr['optionID'] . '.optionID=' . $attr['optionID'] . ')';
$fields[] = 'extra_' . $attr['optionID'] . '.option_value AS extrafield_' . $attr['optionID'];
}
$validCats = implode(',', $this->categoryIds);
$this->productSql = 'SELECT ' . implode(',', $fields) . ' FROM ' . $this->getTablePrefix() . 'products ' . implode(' ', $join) . ' LEFT JOIN ' . $this->getTablePrefix() . 'categories AS cat ON cat.categoryID=' . $this->getTablePrefix() . 'products.categoryID WHERE cat.categoryID IS NOT NULL AND cat.categoryID IN (' . $validCats . ')';
}
if (!($data = $this->loadRecord($this->productSql))) {
return null;
}
$rec = Product::getNewInstance(Category::getInstanceById($this->getRealId('Category', $data['categoryID'])));
$rec->setID($data['productID']);
$rec->keywords->set($data['meta_keywords']);
$rec->setValueByLang('name', $this->defLang, $data['name']);
$rec->setValueByLang('longDescription', $this->defLang, $data['description']);
$rec->setValueByLang('shortDescription', $this->defLang, $data['brief_description']);
foreach ($this->attributes as $attr) {
if (!empty($data['extrafield_' . $attr['optionID']])) {
$rec->setAttributeValueByLang(SpecField::getInstanceByID($this->getRealId('SpecField', $attr['optionID']), SpecField::LOAD_DATA), $this->defLang, $data['extrafield_' . $attr['optionID']]);
}
}
$data['voteSum'] = round($data['customers_rating'] * $data['customer_votes']);
if ($data['product_code']) {
$rec->sku->set($data['product_code']);
}
foreach (array('shippingWeight' => 'weight', 'stockCount' => 'in_stock', 'shippingSurchargeAmount' => 'shipping_freight', 'minimumQuantity' => 'min_order_amount', 'dateCreated' => 'date_added', 'ratingSum' => 'voteSum', 'rating' => 'customers_rating', 'ratingCount' => 'customer_votes') as $lc => $xc) {
$rec->{$lc}->set($data[$xc]);
}
$rec->isEnabled->set(1 == $data['enabled']);
$rec->setPrice($this->getDefaultCurrency(), $data['Price']);
if ($data['list_price']) {
$price = $rec->getPricingHandler()->getPriceByCurrencyCode($this->getDefaultCurrency());
$price->listPrice->set($data['list_price']);
}
// images
foreach ($this->getDataBySQL('SELECT * FROM ' . $this->getTablePrefix() . 'product_pictures WHERE productID=' . $data['productID'] . ' ORDER BY (photoID=' . (int) $data['default_picture'] . '), photoID ASC') as $image) {
$file = $image['enlarged'] ? $image['enlarged'] : $image['filename'];
$this->importProductImage($rec, $this->path . '/products_pictures/' . $file);
}
$rec->rawData = $data;
return $rec;
}
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>';
}