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'];