function sortCatalog($sortItem, $sortByField, $orderBy = 'asc')
{
set_time_limit(86400);
ignore_user_abort(true);
switch ($sortItem) {
case 'manufacturers':
switch ($sortByField) {
case 'manufacturers_name':
$query_array = array();
$query_array['select'][] = 'm.manufacturers_id';
$query_array['from'][] = 'tx_multishop_manufacturers m';
$query_array['where'][] = 'm.status=1';
//$query_array['order_by'][]='SUBSTRING_INDEX(m.manufacturers_name, " ", 1) ASC, CAST(SUBSTRING_INDEX(m.manufacturers_name, " ", -1) AS SIGNED) '.$orderBy;
$query_array['order_by'][] = 'm.manufacturers_name ' . $orderBy;
$str = $GLOBALS['TYPO3_DB']->SELECTquery(is_array($query_array['select']) ? implode(",", $query_array['select']) : '', is_array($query_array['from']) ? implode(",", $query_array['from']) : '', is_array($query_array['where']) ? implode(" and ", $query_array['where']) : '', is_array($query_array['group_by']) ? implode(",", $query_array['group_by']) : '', is_array($query_array['order_by']) ? implode(",", $query_array['order_by']) : '', is_array($query_array['limit']) ? implode(",", $query_array['limit']) : '');
$qry = $GLOBALS['TYPO3_DB']->sql_query($str);
$counter = 0;
$content .= '<div class="main-heading"><h2>Sorting Manufacturers on alphabet ' . $orderBy . ' done</h2></div>';
while ($row = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($qry)) {
$updateArray = array();
$updateArray['sort_order'] = $counter;
$query = $GLOBALS['TYPO3_DB']->UPDATEquery('tx_multishop_manufacturers', 'manufacturers_id=' . $row['manufacturers_id'], $updateArray);
$res = $GLOBALS['TYPO3_DB']->sql_query($query);
$counter++;
}
break;
}
break;
case 'categories':
switch ($sortByField) {
case 'categories_name':
case 'categories_name_natural':
$content .= '<div class="main-heading"><h2>Sorting categories on name ' . $orderBy . ' done</h2></div>';
$query_array = array();
$query_array['select'][] = 'c.categories_id,cd.categories_name';
$query_array['from'][] = 'tx_multishop_categories c, tx_multishop_categories_description cd';
//$query_array['where'][]='c.status=1 and c.parent_id=\''.$this->categoriesStartingPoint.'\' and c.page_uid=\''.$this->showCatalogFromPage.'\' and c.categories_id=cd.categories_id';
$query_array['where'][] = 'c.status=1 and c.page_uid=\'' . $this->showCatalogFromPage . '\' and c.categories_id=cd.categories_id';
$str = $GLOBALS['TYPO3_DB']->SELECTquery(is_array($query_array['select']) ? implode(",", $query_array['select']) : '', is_array($query_array['from']) ? implode(",", $query_array['from']) : '', is_array($query_array['where']) ? implode(" and ", $query_array['where']) : '', is_array($query_array['group_by']) ? implode(",", $query_array['group_by']) : '', is_array($query_array['order_by']) ? implode(",", $query_array['order_by']) : '', is_array($query_array['limit']) ? implode(",", $query_array['limit']) : '');
$qry = $GLOBALS['TYPO3_DB']->sql_query($str);
$valuesArray = array();
while ($item = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($qry)) {
$values_name = $item['categories_name'];
// if the first char is not alphanumeric we cut it off, so we can sort much better
if ($values_name and !preg_match("/^[a-z0-9]/i", $values_name)) {
do {
$values_name = substr($values_name, 1, strlen($values_name));
} while ($values_name and !preg_match("/^[a-z0-9]/i", $values_name));
}
// we now have a name that starts with alphanumeric
$valuesArray[$item['categories_id']] = $values_name;
}
// now let PHP sort the array
natcasesort($valuesArray);
switch ($orderBy) {
case 'desc':
$valuesArray = array_reverse($valuesArray);
break;
}
$sort = 1;
// iterate each value and save the new sort order number to DB
foreach ($valuesArray as $categories_id => $values_name) {
$updateArray = array();
$updateArray['sort_order'] = $sort;
$query = $GLOBALS['TYPO3_DB']->UPDATEquery('tx_multishop_categories', 'categories_id=' . $categories_id, $updateArray);
$GLOBALS['TYPO3_DB']->sql_query($query);
$sort++;
}
break;
case 'categories_name_old':
$query_array = array();
$query_array['select'][] = 'c.categories_id';
$query_array['from'][] = 'tx_multishop_categories c, tx_multishop_categories_description cd';
$query_array['where'][] = 'c.status=1 and c.parent_id=\'0\' and c.page_uid=\'' . $this->showCatalogFromPage . '\' and c.categories_id=cd.categories_id';
$query_array['order_by'][] = 'SUBSTRING_INDEX(cd.categories_name, " ", 1) ASC, CAST(SUBSTRING_INDEX(cd.categories_name, " ", -1) AS SIGNED) ' . $orderBy;
$str = $GLOBALS['TYPO3_DB']->SELECTquery(is_array($query_array['select']) ? implode(",", $query_array['select']) : '', is_array($query_array['from']) ? implode(",", $query_array['from']) : '', is_array($query_array['where']) ? implode(" and ", $query_array['where']) : '', is_array($query_array['group_by']) ? implode(",", $query_array['group_by']) : '', is_array($query_array['order_by']) ? implode(",", $query_array['order_by']) : '', is_array($query_array['limit']) ? implode(",", $query_array['limit']) : '');
$qry = $GLOBALS['TYPO3_DB']->sql_query($str);
$counter = 0;
$content .= '<div class="main-heading"><h2>Sorting categories on alphabet ' . $orderby . ' done</h2></div>';
while ($row = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($qry)) {
$updateArray = array();
$updateArray['sort_order'] = $counter;
$query = $GLOBALS['TYPO3_DB']->UPDATEquery('tx_multishop_categories', 'categories_id=' . $row['categories_id'], $updateArray);
$res = $GLOBALS['TYPO3_DB']->sql_query($query);
$content .= $row['categories_id'] . '<br />';
$counter++;
}
$subcategories_array = array();
mslib_fe::getSubcats($subcategories_array, 0);
if (count($subcategories_array)) {
foreach ($subcategories_array as $item) {
// try to sort the subcats
$content .= $item . '<br />';
$query_array = array();
$query_array['select'][] = 'c.categories_id';
$query_array['from'][] = 'tx_multishop_categories c, tx_multishop_categories_description cd';
$query_array['where'][] = 'c.status=1 and c.parent_id=\'' . $item . '\' and c.page_uid=\'' . $this->showCatalogFromPage . '\' and c.categories_id=cd.categories_id';
$query_array['order_by'][] = 'SUBSTRING_INDEX(cd.categories_name, " ", 1) ASC, CAST(SUBSTRING_INDEX(cd.categories_name, " ", -1) AS SIGNED) ' . $orderBy;
$str = $GLOBALS['TYPO3_DB']->SELECTquery(is_array($query_array['select']) ? implode(",", $query_array['select']) : '', is_array($query_array['from']) ? implode(",", $query_array['from']) : '', is_array($query_array['where']) ? implode(" and ", $query_array['where']) : '', is_array($query_array['group_by']) ? implode(",", $query_array['group_by']) : '', is_array($query_array['order_by']) ? implode(",", $query_array['order_by']) : '', is_array($query_array['limit']) ? implode(",", $query_array['limit']) : '');
$qry = $GLOBALS['TYPO3_DB']->sql_query($str);
$counter = 0;
while ($row = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($qry)) {
$updateArray = array();
$updateArray['sort_order'] = $counter;
$query = $GLOBALS['TYPO3_DB']->UPDATEquery('tx_multishop_categories', 'categories_id=' . $row['categories_id'], $updateArray);
$res = $GLOBALS['TYPO3_DB']->sql_query($query);
$counter++;
$query_array = array();
$query_array['select'][] = 'c.categories_id';
$query_array['from'][] = 'tx_multishop_categories c, tx_multishop_categories_description cd';
$query_array['where'][] = 'c.status=1 and c.parent_id=\'' . $row['categories_id'] . '\' and c.page_uid=\'' . $this->showCatalogFromPage . '\' and c.categories_id=cd.categories_id';
$query_array['order_by'][] = 'SUBSTRING_INDEX(cd.categories_name, " ", 1) ASC, CAST(SUBSTRING_INDEX(cd.categories_name, " ", -1) AS SIGNED) ' . $orderBy;
$str2 = $GLOBALS['TYPO3_DB']->SELECTquery(is_array($query_array['select']) ? implode(",", $query_array['select']) : '', is_array($query_array['from']) ? implode(",", $query_array['from']) : '', is_array($query_array['where']) ? implode(" and ", $query_array['where']) : '', is_array($query_array['group_by']) ? implode(",", $query_array['group_by']) : '', is_array($query_array['order_by']) ? implode(",", $query_array['order_by']) : '', is_array($query_array['limit']) ? implode(",", $query_array['limit']) : '');
$qry2 = $GLOBALS['TYPO3_DB']->sql_query($str2);
$counter = 0;
while ($row2 = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($qry2)) {
$updateArray = array();
$updateArray['sort_order'] = $counter;
$query = $GLOBALS['TYPO3_DB']->UPDATEquery('tx_multishop_categories', 'categories_id=' . $row2['categories_id'], $updateArray);
$res = $GLOBALS['TYPO3_DB']->sql_query($query);
$counter++;
}
}
}
}
break;
}
break;
case 'products':
switch ($sortByField) {
case 'products_price':
$content .= '<div class="main-heading"><h2>Sorting products price ' . $orderBy . ' done</h2></div>';
// try to sort the subcats
$content .= $item . '<br />';
// try to find and sort the products
$query_array = array();
$query_array['select'][] = 'p2c.categories_id, p.products_id, IF(s.status, s.specials_new_products_price, p.products_price) as final_price';
$query_array['from'][] = 'tx_multishop_products p left join tx_multishop_specials s on p.products_id = s.products_id, tx_multishop_products_description pd, tx_multishop_products_to_categories p2c';
$query_array['where'][] = 'p.products_status=1 and p.page_uid=\'' . $this->showCatalogFromPage . '\' and p.products_id=pd.products_id and p.products_id=p2c.products_id';
$query_array['order_by'][] = 'final_price ' . $orderBy;
$str = $GLOBALS['TYPO3_DB']->SELECTquery(is_array($query_array['select']) ? implode(",", $query_array['select']) : '', is_array($query_array['from']) ? implode(",", $query_array['from']) : '', is_array($query_array['where']) ? implode(" and ", $query_array['where']) : '', is_array($query_array['group_by']) ? implode(",", $query_array['group_by']) : '', is_array($query_array['order_by']) ? implode(",", $query_array['order_by']) : '', is_array($query_array['limit']) ? implode(",", $query_array['limit']) : '');
$qry = $GLOBALS['TYPO3_DB']->sql_query($str);
$counter = 0;
while ($row = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($qry)) {
$updateArray = array();
$updateArray['sort_order'] = $counter;
$query = $GLOBALS['TYPO3_DB']->UPDATEquery('tx_multishop_products_to_categories', 'products_id=' . $row['products_id'] . ' and categories_id=' . $row['categories_id'], $updateArray);
$res = $GLOBALS['TYPO3_DB']->sql_query($query);
$query = $GLOBALS['TYPO3_DB']->UPDATEquery('tx_multishop_products', 'products_id=' . $row['products_id'], $updateArray);
$res = $GLOBALS['TYPO3_DB']->sql_query($query);
if ($this->ms['MODULES']['FLAT_DATABASE']) {
$query = $GLOBALS['TYPO3_DB']->UPDATEquery('tx_multishop_products_flat', 'products_id=' . $row['products_id'], $updateArray);
$res = $GLOBALS['TYPO3_DB']->sql_query($query);
}
$counter++;
}
// per category is not optimal when using wide products search
/*
mslib_fe::getSubcats($subcategories_array, 0);
if (count($subcategories_array)) {
foreach ($subcategories_array as $item) {
// try to sort the subcats
$content.=$item.'<br />';
// try to find and sort the products
$query_array=array();
$query_array['select'][]='p2c.categories_id, p.products_id, IF(s.status, s.specials_new_products_price, p.products_price) as final_price';
$query_array['from'][]='tx_multishop_products p left join tx_multishop_specials s on p.products_id = s.products_id, tx_multishop_products_description pd, tx_multishop_products_to_categories p2c';
$query_array['where'][]='p.products_status=1 and p.page_uid=\''.$this->showCatalogFromPage.'\' and p.products_id=pd.products_id and p.products_id=p2c.products_id and p2c.categories_id=\''.$item.'\'';
$query_array['order_by'][]='final_price '.$orderBy;
$str=$GLOBALS['TYPO3_DB']->SELECTquery((is_array($query_array['select']) ? implode(",", $query_array['select']) : ''), // SELECT ...
(is_array($query_array['from']) ? implode(",", $query_array['from']) : ''), // FROM ...
(is_array($query_array['where']) ? implode(" and ", $query_array['where']) : ''), // WHERE...
(is_array($query_array['group_by']) ? implode(",", $query_array['group_by']) : ''), // GROUP BY...
(is_array($query_array['order_by']) ? implode(",", $query_array['order_by']) : ''), // ORDER BY...
(is_array($query_array['limit']) ? implode(",", $query_array['limit']) : '') // LIMIT ...
);
$qry=$GLOBALS['TYPO3_DB']->sql_query($str);
$counter=0;
while ($row=$GLOBALS['TYPO3_DB']->sql_fetch_assoc($qry)) {
$updateArray=array();
$updateArray['sort_order']=$counter;
$query=$GLOBALS['TYPO3_DB']->UPDATEquery('tx_multishop_products_to_categories', 'products_id='.$row['products_id'].' and categories_id='.$row['categories_id'], $updateArray);
$res=$GLOBALS['TYPO3_DB']->sql_query($query);
$updateArray=array();
$updateArray['sort_order']=$counter;
$query=$GLOBALS['TYPO3_DB']->UPDATEquery('tx_multishop_products', 'products_id='.$row['products_id'], $updateArray);
$res=$GLOBALS['TYPO3_DB']->sql_query($query);
$counter++;
}
}
}
*/
break;
case 'products_name':
$content .= '<div class="main-heading"><h2>Sorting products name on alphabet ' . $orderBy . ' done</h2></div>';
$subcategories_array = array();
mslib_fe::getSubcats($subcategories_array, 0);
if (count($subcategories_array)) {
foreach ($subcategories_array as $item) {
// try to find and sort the products
$query_array = array();
$query_array['select'][] = 'pd.products_name,p2c.categories_id, p.products_id';
$query_array['from'][] = 'tx_multishop_products p left join tx_multishop_specials s on p.products_id = s.products_id, tx_multishop_products_description pd, tx_multishop_products_to_categories p2c';
$query_array['where'][] = 'p.products_status=1 and p.page_uid=\'' . $this->showCatalogFromPage . '\' and p.products_id=pd.products_id and p2c.categories_id=\'' . $item . '\' and p.products_id=p2c.products_id';
$str = $GLOBALS['TYPO3_DB']->SELECTquery(is_array($query_array['select']) ? implode(",", $query_array['select']) : '', is_array($query_array['from']) ? implode(",", $query_array['from']) : '', is_array($query_array['where']) ? implode(" and ", $query_array['where']) : '', is_array($query_array['group_by']) ? implode(",", $query_array['group_by']) : '', '', is_array($query_array['limit']) ? implode(",", $query_array['limit']) : '');
$qry = $GLOBALS['TYPO3_DB']->sql_query($str);
$counter = 0;
$valuesArray = array();
while ($row = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($qry)) {
$values_name = $row['products_name'];
// if the first char is not alphanumeric we cut it off, so we can sort much better
if ($values_name and !preg_match("/^[a-z0-9]/i", $values_name)) {
do {
$values_name = substr($values_name, 1, strlen($values_name));
} while ($values_name and !preg_match("/^[a-z0-9]/i", $values_name));
}
// we now have a name that starts with alphanumeric
$valuesArray[$row['products_id']] = $values_name;
}
// now let PHP sort the array
natcasesort($valuesArray);
switch ($orderBy) {
case 'desc':
$valuesArray = array_reverse($valuesArray);
break;
}
$sort = 1;
// iterate each value and save the new sort order number to DB
foreach ($valuesArray as $products_id => $values_name) {
$updateArray = array();
$updateArray['sort_order'] = $sort;
$query = $GLOBALS['TYPO3_DB']->UPDATEquery('tx_multishop_products_to_categories', 'products_id=' . $products_id, $updateArray);
$GLOBALS['TYPO3_DB']->sql_query($query);
$query = $GLOBALS['TYPO3_DB']->UPDATEquery('tx_multishop_products', 'products_id=' . $products_id, $updateArray);
$GLOBALS['TYPO3_DB']->sql_query($query);
$sort++;
}
}
}
break;
case 'products_name_old':
$content .= '<div class="main-heading"><h2>Sorting products name on alphabet ' . $orderBy . ' done</h2></div>';
$subcategories_array = array();
mslib_fe::getSubcats($subcategories_array, 0);
if (count($subcategories_array)) {
foreach ($subcategories_array as $item) {
// try to find and sort the products
$query_array = array();
$query_array['select'][] = 'p2c.categories_id, p.products_id, IF(s.status, s.specials_new_products_price, p.products_price) as final_price';
$query_array['from'][] = 'tx_multishop_products p left join tx_multishop_specials s on p.products_id = s.products_id, tx_multishop_products_description pd, tx_multishop_products_to_categories p2c';
$query_array['where'][] = 'p.products_status=1 and p.page_uid=\'' . $this->showCatalogFromPage . '\' and p.products_id=pd.products_id and p2c.categories_id=\'' . $item . '\' and p.products_id=p2c.products_id';
$query_array['order_by'][] = 'SUBSTRING_INDEX(pd.products_name, " ", 1) ASC, CAST(SUBSTRING_INDEX(pd.products_name, " ", -1) AS SIGNED) ' . $orderBy;
$str = $GLOBALS['TYPO3_DB']->SELECTquery(is_array($query_array['select']) ? implode(",", $query_array['select']) : '', is_array($query_array['from']) ? implode(",", $query_array['from']) : '', is_array($query_array['where']) ? implode(" and ", $query_array['where']) : '', is_array($query_array['group_by']) ? implode(",", $query_array['group_by']) : '', is_array($query_array['order_by']) ? implode(",", $query_array['order_by']) : '', is_array($query_array['limit']) ? implode(",", $query_array['limit']) : '');
$qry = $GLOBALS['TYPO3_DB']->sql_query($str);
$counter = 0;
while ($row = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($qry)) {
$updateArray = array();
$updateArray['sort_order'] = $counter;
$query = $GLOBALS['TYPO3_DB']->UPDATEquery('tx_multishop_products_to_categories', 'products_id=' . $row['products_id'] . ' and categories_id=' . $row['categories_id'], $updateArray);
$res = $GLOBALS['TYPO3_DB']->sql_query($query);
$updateArray = array();
$updateArray['sort_order'] = $counter;
$query = $GLOBALS['TYPO3_DB']->UPDATEquery('tx_multishop_products', 'products_id=' . $row['products_id'], $updateArray);
$res = $GLOBALS['TYPO3_DB']->sql_query($query);
$counter++;
}
}
}
break;
case 'products_date_added':
$content .= '<div class="main-heading"><h2>Sorting products date added ' . $orderBy . ' done</h2></div>';
$subcategories_array = array();
mslib_fe::getSubcats($subcategories_array, 0);
if (count($subcategories_array)) {
foreach ($subcategories_array as $item) {
//$content.= $item.'<br />';
// try to find and sort the products
$query_array = array();
$query_array['select'][] = 'p2c.categories_id, p.products_id, IF(s.status, s.specials_new_products_price, p.products_price) as final_price';
$query_array['from'][] = 'tx_multishop_products p left join tx_multishop_specials s on p.products_id = s.products_id, tx_multishop_products_description pd, tx_multishop_products_to_categories p2c';
$query_array['where'][] = 'p.products_status=1 and p.page_uid=\'' . $this->showCatalogFromPage . '\' and p.products_id=pd.products_id and p2c.categories_id=\'' . $item . '\' and p.products_id=p2c.products_id';
$query_array['order_by'][] = 'p.products_date_added ' . $orderBy;
$str = $GLOBALS['TYPO3_DB']->SELECTquery(is_array($query_array['select']) ? implode(",", $query_array['select']) : '', is_array($query_array['from']) ? implode(",", $query_array['from']) : '', is_array($query_array['where']) ? implode(" and ", $query_array['where']) : '', is_array($query_array['group_by']) ? implode(",", $query_array['group_by']) : '', is_array($query_array['order_by']) ? implode(",", $query_array['order_by']) : '', is_array($query_array['limit']) ? implode(",", $query_array['limit']) : '');
$qry = $GLOBALS['TYPO3_DB']->sql_query($str);
$no = time();
while ($row = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($qry)) {
$updateArray = array();
$updateArray['sort_order'] = $no;
$query = $GLOBALS['TYPO3_DB']->UPDATEquery('tx_multishop_products_to_categories', 'products_id=' . $row['products_id'] . ' and categories_id=' . $row['categories_id'], $updateArray);
$res = $GLOBALS['TYPO3_DB']->sql_query($query);
if ($this->conf['debugEnabled'] == '1') {
$logString = 'Resort catalog (' . $sortByField . '). Query: ' . $query;
\TYPO3\CMS\Core\Utility\GeneralUtility::devLog($logString, 'multishop', 0);
}
$updateArray = array();
$updateArray['sort_order'] = $no;
$query = $GLOBALS['TYPO3_DB']->UPDATEquery('tx_multishop_products', 'products_id=' . $row['products_id'], $updateArray);
$res = $GLOBALS['TYPO3_DB']->sql_query($query);
if ($this->conf['debugEnabled'] == '1') {
$logString = 'Resort catalog (' . $sortByField . '). Query: ' . $query;
\TYPO3\CMS\Core\Utility\GeneralUtility::devLog($logString, 'multishop', 0);
}
if ($this->ms['MODULES']['PRODUCTS_LISTING_SORT_ORDER_OPTION'] == 'desc') {
$no--;
} else {
$no++;
}
}
}
}
break;
case 'products_main_categories':
$content .= '<div class="main-heading"><h2>Sorting products main categories ' . $orderBy . ' done</h2></div>';
//$content.= $item.'<br />';
// try to find and sort the products
$query_array = array();
$query_array['select'][] = 'c.sort_order, p2c.categories_id, p.products_id';
$query_array['from'][] = 'tx_multishop_products p, tx_multishop_products_to_categories p2c, tx_multishop_categories c';
$query_array['where'][] = 'p.products_status=1 and p.page_uid=\'' . $this->showCatalogFromPage . '\' and c.parent_id=\'0\' and p2c.page_uid=p.page_uid and p.products_id=p2c.products_id and p2c.node_id=c.categories_id';
$query_array['order_by'][] = 'c.sort_order ' . $orderBy;
$query_array['group_by'][] = 'p.products_id';
$str = $GLOBALS['TYPO3_DB']->SELECTquery(is_array($query_array['select']) ? implode(",", $query_array['select']) : '', is_array($query_array['from']) ? implode(",", $query_array['from']) : '', is_array($query_array['where']) ? implode(" and ", $query_array['where']) : '', is_array($query_array['group_by']) ? implode(",", $query_array['group_by']) : '', is_array($query_array['order_by']) ? implode(",", $query_array['order_by']) : '', is_array($query_array['limit']) ? implode(",", $query_array['limit']) : '');
$qry = $GLOBALS['TYPO3_DB']->sql_query($str);
while ($row = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($qry)) {
$updateArray = array();
$updateArray['sort_order'] = $row['sort_order'];
$query = $GLOBALS['TYPO3_DB']->UPDATEquery('tx_multishop_products_to_categories', 'products_id=' . $row['products_id'] . ' and page_uid=' . $this->showCatalogFromPage, $updateArray);
$res = $GLOBALS['TYPO3_DB']->sql_query($query);
if ($this->conf['debugEnabled'] == '1') {
$logString = 'Resort catalog (' . $sortByField . '). Query: ' . $query;
\TYPO3\CMS\Core\Utility\GeneralUtility::devLog($logString, 'multishop', 0);
}
$updateArray = array();
$updateArray['sort_order'] = $no;
$query = $GLOBALS['TYPO3_DB']->UPDATEquery('tx_multishop_products', 'products_id=' . $row['products_id'], $updateArray);
$res = $GLOBALS['TYPO3_DB']->sql_query($query);
if ($this->conf['debugEnabled'] == '1') {
$logString = 'Resort catalog (' . $sortByField . '). Query: ' . $query;
\TYPO3\CMS\Core\Utility\GeneralUtility::devLog($logString, 'multishop', 0);
}
}
break;
case 'products_deepest_categories':
$content .= '<div class="main-heading"><h2>Sorting products deepest categories ' . $orderBy . ' done</h2></div>';
//$content.= $item.'<br />';
// try to find and sort the products
$query_array = array();
$query_array['select'][] = 'c.sort_order, p2c.categories_id, p.products_id';
$query_array['from'][] = 'tx_multishop_products p, tx_multishop_products_to_categories p2c, tx_multishop_categories c';
$query_array['where'][] = 'p.products_status=1 and p.page_uid=\'' . $this->showCatalogFromPage . '\' and p2c.is_deepest=\'1\' and p2c.page_uid=p.page_uid p.products_id=p2c.products_id and p2c.categories_id=c.categories_id';
$query_array['order_by'][] = 'c.sort_order ' . $orderBy;
$query_array['group_by'][] = 'p.products_id';
$str = $GLOBALS['TYPO3_DB']->SELECTquery(is_array($query_array['select']) ? implode(",", $query_array['select']) : '', is_array($query_array['from']) ? implode(",", $query_array['from']) : '', is_array($query_array['where']) ? implode(" and ", $query_array['where']) : '', is_array($query_array['group_by']) ? implode(",", $query_array['group_by']) : '', is_array($query_array['order_by']) ? implode(",", $query_array['order_by']) : '', is_array($query_array['limit']) ? implode(",", $query_array['limit']) : '');
$qry = $GLOBALS['TYPO3_DB']->sql_query($str);
$no = time();
while ($row = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($qry)) {
$updateArray = array();
$updateArray['sort_order'] = $row['sort_order'];
$query = $GLOBALS['TYPO3_DB']->UPDATEquery('tx_multishop_products_to_categories', 'products_id=' . $row['products_id'] . ' and page_uid=' . $this->showCatalogFromPage, $updateArray);
$res = $GLOBALS['TYPO3_DB']->sql_query($query);
if ($this->conf['debugEnabled'] == '1') {
$logString = 'Resort catalog (' . $sortByField . '). Query: ' . $query;
\TYPO3\CMS\Core\Utility\GeneralUtility::devLog($logString, 'multishop', 0);
}
$updateArray = array();
$updateArray['sort_order'] = $no;
$query = $GLOBALS['TYPO3_DB']->UPDATEquery('tx_multishop_products', 'products_id=' . $row['products_id'], $updateArray);
$res = $GLOBALS['TYPO3_DB']->sql_query($query);
if ($this->conf['debugEnabled'] == '1') {
$logString = 'Resort catalog (' . $sortByField . '). Query: ' . $query;
\TYPO3\CMS\Core\Utility\GeneralUtility::devLog($logString, 'multishop', 0);
}
}
break;
}
break;
case 'attribute_values':
switch ($sortByField) {
case 'products_options_values_name':
// manually (naturally) sort all attribute values
$str = "select * from tx_multishop_products_options where language_id='0' order by sort_order";
$qry = $GLOBALS['TYPO3_DB']->sql_query($str);
$rows = $GLOBALS['TYPO3_DB']->sql_num_rows($qry);
if ($rows) {
while (($row = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($qry)) != false) {
$query_array = array();
$query_array['select'][] = '*';
$query_array['from'][] = 'tx_multishop_products_options_values_to_products_options povp, tx_multishop_products_options_values pov';
$query_array['where'][] = 'povp.products_options_id=\'' . $row['products_options_id'] . '\' and pov.language_id=\'0\' and povp.products_options_values_id=pov.products_options_values_id';
$query_array['order_by'][] = 'SUBSTRING_INDEX(pov.products_options_values_name, " ", 1) ASC, CAST(SUBSTRING_INDEX(pov.products_options_values_name, " ", -1) AS SIGNED) ' . $orderBy;
$str2 = $GLOBALS['TYPO3_DB']->SELECTquery(is_array($query_array['select']) ? implode(",", $query_array['select']) : '', is_array($query_array['from']) ? implode(",", $query_array['from']) : '', is_array($query_array['where']) ? implode(" and ", $query_array['where']) : '', is_array($query_array['group_by']) ? implode(",", $query_array['group_by']) : '', is_array($query_array['order_by']) ? implode(",", $query_array['order_by']) : '', is_array($query_array['limit']) ? implode(",", $query_array['limit']) : '');
$qry2 = $GLOBALS['TYPO3_DB']->sql_query($str2);
$counter = 0;
while (($row2 = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($qry2)) != false) {
$counter++;
$updateArray = array();
$updateArray['sort_order'] = $counter;
$query3 = $GLOBALS['TYPO3_DB']->UPDATEquery('tx_multishop_products_options_values_to_products_options', 'products_options_id=\'' . $row2['products_options_id'] . '\' and products_options_values_id=\'' . $row2['products_options_values_id'] . '\'', $updateArray);
$res3 = $GLOBALS['TYPO3_DB']->sql_query($query3);
// product level
$where = "options_id = " . $row2['products_options_id'] . " and options_values_id=" . $row2['products_options_values_id'] . " and page_uid=" . $this->showCatalogFromPage;
$updateArray = array();
$updateArray = array('sort_order_option_value' => $counter);
$query = $GLOBALS['TYPO3_DB']->UPDATEquery('tx_multishop_products_attributes', $where, $updateArray);
$res = $GLOBALS['TYPO3_DB']->sql_query($query);
}
// update sort eof
}
}
$content .= 'Attribute value sorting completed';
break;
case 'products_options_values_name_natural':
// get all attribute options
$options_ids = mslib_befe::getRecords('0', 'tx_multishop_products_options', 'language_id');
//$options_ids=array();
//test
//$options_ids[0]=array('products_options_id'=>'17');
foreach ($options_ids as $options_id) {
$valuesArray = array();
$values_id = array();
// iterate each attribute option and get the values
$sql = "select pov2po.*, pov.products_options_values_name, pov.products_options_values_id from tx_multishop_products_options_values_to_products_options pov2po, tx_multishop_products_options_values pov where pov2po.products_options_id = " . $options_id['products_options_id'] . " and pov.products_options_values_id = pov2po.products_options_values_id";
$qry = $GLOBALS['TYPO3_DB']->sql_query($sql);
$values_id = array();
while ($row = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($qry)) {
$values_name = $row['products_options_values_name'];
// if the first char is not alphanumeric we cut it off, so we can sort much better
if ($values_name and !preg_match("/^[a-z0-9]/i", $values_name)) {
do {
$values_name = substr($values_name, 1, strlen($values_name));
} while ($values_name and !preg_match("/^[a-z0-9]/i", $values_name));
}
// we now have a name that starts with alphanumeric
$valuesArray[$row['products_options_values_to_products_options_id']] = $values_name;
$values_id[$values_name] = $row['products_options_values_id'];
}
// now let PHP sort the array
natcasesort($valuesArray);
switch ($orderBy) {
case 'desc':
$valuesArray = array_reverse($valuesArray);
break;
}
$sort = 1;
// iterate each value and save the new sort order number to DB
foreach ($valuesArray as $pov2po_row_id => $values_name) {
$updateArray = array();
$updateArray['sort_order'] = $sort;
$query = $GLOBALS['TYPO3_DB']->UPDATEquery('tx_multishop_products_options_values_to_products_options', "products_options_values_to_products_options_id = " . $pov2po_row_id . " and products_options_id = " . $options_id['products_options_id'], $updateArray);
$GLOBALS['TYPO3_DB']->sql_query($query);
// product level
$where = "options_id = " . $options_id['products_options_id'] . " and options_values_id=" . $values_id[$values_name] . " and page_uid=" . $this->showCatalogFromPage;
$updateArray = array();
$updateArray = array('sort_order_option_value' => $sort);
$query = $GLOBALS['TYPO3_DB']->UPDATEquery('tx_multishop_products_attributes', $where, $updateArray);
$res = $GLOBALS['TYPO3_DB']->sql_query($query);
//
$sort++;
}
}
$content .= 'Attribute value sorting (natural) completed';
break;
}
break;
case 'attribute_names':
switch ($sortByField) {
case 'products_options_name':
$query_array = array();
$query_array['select'][] = '*';
$query_array['from'][] = 'tx_multishop_products_options po';
$query_array['where'][] = 'po.language_id=\'0\'';
$query_array['order_by'][] = 'SUBSTRING_INDEX(po.products_options_name, " ", 1) ASC, CAST(SUBSTRING_INDEX(po.products_options_name, " ", -1) AS SIGNED) ' . $orderBy;
$str = $GLOBALS['TYPO3_DB']->SELECTquery(is_array($query_array['select']) ? implode(",", $query_array['select']) : '', is_array($query_array['from']) ? implode(",", $query_array['from']) : '', is_array($query_array['where']) ? implode(" and ", $query_array['where']) : '', is_array($query_array['group_by']) ? implode(",", $query_array['group_by']) : '', is_array($query_array['order_by']) ? implode(",", $query_array['order_by']) : '', is_array($query_array['limit']) ? implode(",", $query_array['limit']) : '');
$qry = $GLOBALS['TYPO3_DB']->sql_query($str);
$counter = 0;
while (($row = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($qry)) != false) {
$counter++;
$where = "products_options_id = " . $row['products_options_id'];
$updateArray = array('sort_order' => $counter);
$query = $GLOBALS['TYPO3_DB']->UPDATEquery('tx_multishop_products_options', $where, $updateArray);
$res = $GLOBALS['TYPO3_DB']->sql_query($query);
// products level
$where = "options_id = " . $row['products_options_id'] . " and page_uid='" . $this->showCatalogFromPage . "'";
$updateArray = array();
$updateArray = array('sort_order_option_name' => $counter);
$query = $GLOBALS['TYPO3_DB']->UPDATEquery('tx_multishop_products_attributes', $where, $updateArray);
$res = $GLOBALS['TYPO3_DB']->sql_query($query);
}
$content .= 'Attribute name sorting completed';
break;
case 'products_options_name_natural':
$valuesArray = array();
// iterate each attribute option and get the values
$sql = "select po.* from tx_multishop_products_options po";
$qry = $GLOBALS['TYPO3_DB']->sql_query($sql);
$values_id = array();
while ($row = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($qry)) {
$values_name = $row['products_options_name'];
// if the first char is not alphanumeric we cut it off, so we can sort much better
if ($values_name and !preg_match("/^[a-z0-9]/i", $values_name)) {
do {
$values_name = substr($values_name, 1, strlen($values_name));
} while ($values_name and !preg_match("/^[a-z0-9]/i", $values_name));
}
// we now have a name that starts with alphanumeric
$valuesArray[$row['products_options_id']] = $values_name;
}
// now let PHP sort the array
natcasesort($valuesArray);
switch ($orderBy) {
case 'desc':
$valuesArray = array_reverse($valuesArray);
break;
}
$sort = 1;
// iterate each value and save the new sort order number to DB
foreach ($valuesArray as $pov2po_row_id => $values_name) {
$where = "products_options_id = " . $pov2po_row_id;
$updateArray = array('sort_order' => $sort);
$query = $GLOBALS['TYPO3_DB']->UPDATEquery('tx_multishop_products_options', $where, $updateArray);
$res = $GLOBALS['TYPO3_DB']->sql_query($query);
// products level
$where = "options_id = " . $pov2po_row_id . " and page_uid='" . $this->showCatalogFromPage . "'";
$updateArray = array();
$updateArray = array('sort_order_option_name' => $sort);
$query = $GLOBALS['TYPO3_DB']->UPDATEquery('tx_multishop_products_attributes', $where, $updateArray);
$res = $GLOBALS['TYPO3_DB']->sql_query($query);
$sort++;
}
$content .= 'Attribute name sorting (natural) completed';
break;
}
break;
}
return $content;
}
public function getSubcats(&$subcategories_array, $parent_id = 0, $page_uid = '')
{
if (!is_numeric($page_uid)) {
$page_uid = $this->showCatalogFromPage;
}
$qry = $GLOBALS['TYPO3_DB']->SELECTquery('categories_id', 'tx_multishop_categories', 'page_uid=\'' . $page_uid . '\' and status = \'1\' and parent_id = \'' . $parent_id . '\'', '', '', '');
$subcategories_query = $GLOBALS['TYPO3_DB']->sql_query($qry);
while ($subcategories = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($subcategories_query)) {
$subcategories_array[sizeof($subcategories_array)] = $subcategories['categories_id'];
if ($subcategories['categories_id'] != $parent_id) {
mslib_fe::getSubcats($subcategories_array, $subcategories['categories_id']);
}
}
}
if (!$this->cacheLifeTime) {
$this->cacheLifeTime = $this->ms['MODULES']['CACHE_TIME_OUT_LISTING_PAGES'];
}
$options = array('caching' => true, 'cacheDir' => $this->DOCUMENT_ROOT . 'uploads/tx_multishop/tmp/cache/', 'lifeTime' => $this->cacheLifeTime);
$Cache_Lite = new Cache_Lite($options);
$string = $this->cObj->data['uid'] . '_' . $parent_id . '_' . $this->server['REQUEST_URI'] . $this->server['QUERY_STRING'];
}
if (!$this->ms['MODULES']['CACHE_FRONT_END'] or !($output_array = $Cache_Lite->get($string))) {
$str = '';
$filter = array();
if ($parent_id) {
if ($this->ms['MODULES']['FLAT_DATABASE']) {
$filter[] = "(categories_id_0='" . $parent_id . "' or categories_id_1='" . $parent_id . "' or categories_id_2='" . $parent_id . "' or categories_id_3='" . $parent_id . "' or categories_id_4='" . $parent_id . "' or categories_id_5='" . $parent_id . "')";
} else {
$subcategories_array = array();
mslib_fe::getSubcats($subcategories_array, $parent_id);
if (count($subcategories_array) > 0) {
$where = '';
$filter[] = "p2c.categories_id IN (" . implode(",", $subcategories_array) . ")";
} else {
$filter[] = "p2c.categories_id IN (" . $parent_id . ")";
}
}
} elseif ($this->get['skeyword']) {
if ($this->ms['MODULES']['FLAT_DATABASE']) {
$tbl = '';
} else {
$tbl = 'pd.';
}
$filter[] = "(" . $tbl . "products_name like '%" . addslashes($this->get['skeyword']) . "%' or " . $tbl . "products_description like '%" . addslashes($this->get['skeyword']) . "%')";
}
<?php
// THIS CATEGORIES LISTING TYPE DIRECTLY PRINTS THE PRODUCTS
if (is_array($current) && $current['categories_id'] == $this->conf['categoriesStartingPoint']) {
$categories_id = $current['categories_id'];
$current['categories_name'] = '';
} else {
$categories_id = $this->conf['categoriesStartingPoint'];
}
if (!is_numeric($categories_id)) {
// FALLBACK TO NORMAL CATEGORIES LISTING
require \TYPO3\CMS\Core\Utility\ExtensionManagementUtility::extPath('multishop') . 'scripts/front_pages/includes/categories_listing/default.php';
} else {
// get all subcategories
$cats = array();
mslib_fe::getSubcats($cats, $categories_id);
if ($this->productsLimit) {
$this->ms['MODULES']['PRODUCTS_LISTING_LIMIT'] = $this->productsLimit;
}
$default_limit_page = $this->ms['MODULES']['PRODUCTS_LISTING_LIMIT'];
if ($this->get['tx_multishop_pi1']['limitsb']) {
if ($this->get['tx_multishop_pi1']['limitsb'] and $this->get['tx_multishop_pi1']['limitsb'] != $this->cookie['limitsb']) {
$this->cookie['limitsb'] = $this->get['tx_multishop_pi1']['limitsb'];
$this->ms['MODULES']['PRODUCTS_LISTING_LIMIT'] = $this->cookie['limitsb'];
$GLOBALS['TSFE']->fe_user->setKey('ses', 'tx_multishop_cookie', $this->cookie);
$GLOBALS['TSFE']->storeSessionData();
}
}
if ($this->get['tx_multishop_pi1']['sortbysb']) {
if ($this->get['tx_multishop_pi1']['sortbysb'] and $this->get['tx_multishop_pi1']['sortbysb'] != $this->cookie['sortbysb']) {
$this->cookie['sortbysb'] = $this->get['tx_multishop_pi1']['sortbysb'];
