public static function getProducts($lang_id, $limit_start, $limit, $order_by = null, $order_way = null, $get_total = false, $published = true, $random = false, $random_number_products = 1, $check_access = true, JeproshopContext $context = null) { if (!$context) { $context = JeproshopContext::getContext(); } $db = JFactory::getDBO(); $app = JFactory::getApplication(); jimport('joomla.html.pagination'); //if ($check_access && !$context->controller->checkAccess($context->customer->customer_id, $category_id)){ return false; } if ($limit_start < 1) { $limit_start = 0; } if (empty($order_by)) { $order_by = 'position'; } else { /* Fix for all modules which are now using lowercase values for 'orderBy' parameter */ $order_by = strtolower($order_by); } if (empty($order_way)) { $order_way = "DESC"; } $order_by_prefix = false; if ($order_by == 'product_id' || $order_by == 'date_add' || $order_by == 'date_upd') { $order_by_prefix = "product"; } elseif ($order_by == 'name') { $order_by_prefix = "product_lang"; } elseif ($order_by == 'manufacturer') { $order_by_prefix = "manufacturer"; $order_by = "name"; } elseif ($order_by == 'position') { $order_by_prefix = "product"; } if ($order_by == 'price') { $order_by = 'order_price'; } if (!JeproshopTools::isOrderBy($order_by) || !JeproshopTools::isOrderWay($order_way)) { die(JError::raiseError()); } $supplier_id = (int) $app->input->get('supplier_id'); /* Return only the number of products */ $query = "SELECT COUNT(product." . $db->quoteName('product_id') . ") AS total FROM " . $db->quoteName('#__jeproshop_product') . " AS product "; $query .= JeproshopShopModelShop::addSqlAssociation('product') . " WHERE product_shop." . $db->quoteName('visibility') . " IN ('both', 'catalog')"; $query .= " AND product_shop." . $db->quoteName('published') . " = 1 " . ($supplier_id ? " AND product.supplier_id = " . (int) $supplier_id : ""); $db->setQuery($query); $total = (int) $db->loadResult(); $number_days_new_product = JeproshopSettingModelSetting::getValue('number_days_new_product'); $query = "SELECT product.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity, MAX(product_attribute_shop.product_attribute_id) AS "; $query .= "product_attribute_id, product_attribute_shop.minimal_quantity AS product_attribute_minimal_quantity, product_lang." . $db->quoteName('description'); $query .= ", product_lang." . $db->quoteName('short_description') . ", product_lang." . $db->quoteName('available_now') . ", product_lang." . $db->quoteName('available_later'); $query .= ", product_lang." . $db->quoteName('link_rewrite') . ", product_lang." . $db->quoteName('meta_description') . ", product_lang." . $db->quoteName('meta_keywords'); $query .= ", product_lang." . $db->quoteName('meta_title') . ", product_lang." . $db->quoteName('name') . ", MAX(image_shop." . $db->quoteName('image_id') . " ) AS "; $query .= "image_id, image_lang." . $db->quoteName('legend') . ", manufacturer." . $db->quoteName('name') . " AS manufacturer_name, DATEDIFF(product_shop." . $db->quoteName('date_add'); $query .= ", DATE_SUB(NOW(), INTERVAL " . (JeproshopTools::isUnsignedInt($number_days_new_product) ? $number_days_new_product : 20) . "\tDAY)) > 0 AS new, product_shop.price AS"; $query .= " order_price FROM " . $db->quoteName('#__jeproshop_product') . " AS product " . JeproshopShopModelShop::addSqlAssociation('product') . " LEFT JOIN "; $query .= $db->quoteName('#__jeproshop_product_attribute') . " AS product_attribute ON (product." . $db->quoteName('product_id') . " = product_attribute." . $db->quoteName('product_id'); $query .= ") " . JeproshopShopModelShop::addSqlAssociation('product_attribute', false, 'product_attribute_shop.`default_on` = 1'); $query .= JeproshopProductModelProduct::sqlStock('product', 'product_attribute_shop', false, $context->shop) . " LEFT JOIN " . $db->quoteName('#__jeproshop_product_lang'); $query .= " AS product_lang ON (product." . $db->quoteName('product_id') . " = product_lang." . $db->quoteName('product_id') . " AND product_lang." . $db->quoteName('lang_id') . " = " . (int) $lang_id . JeproshopShopModelShop::addSqlRestrictionOnLang('product_lang'); $query .= ") LEFT JOIN " . $db->quoteName('#__jeproshop_image') . " AS image ON (image." . $db->quoteName('product_id') . " = product." . $db->quoteName('product_id') . ") " . JeproshopShopModelShop::addSqlAssociation('image', false, 'image_shop.cover=1') . " LEFT JOIN "; $query .= $db->quoteName('#__jeproshop_image_lang') . " AS image_lang ON (image_shop." . $db->quoteName('image_id') . " = image_lang." . $db->quoteName('image_id') . " AND image_lang." . $db->quoteName('lang_id') . " = " . (int) $lang_id . ") LEFT JOIN "; $query .= $db->quoteName('#__jeproshop_manufacturer') . " AS manufacturer ON manufacturer." . $db->quoteName('manufacturer_id') . " = product." . $db->quoteName('manufacturer_id') . "\tWHERE product_shop." . $db->quoteName('shop_id') . " = " . (int) $context->shop->shop_id; $query .= " AND product_shop." . $db->quoteName('published') . " = 1 AND product_shop." . $db->quoteName('visibility') . " IN ('both', 'catalog') "; $query .= ($supplier_id ? " AND product.supplier_id = " . (int) $supplier_id : "") . " GROUP BY product_shop.product_id"; if ($random === true) { $query .= " ORDER BY RAND() LIMIT " . (int) $random_number_products; } else { //$query .= " ORDER BY " . (!empty($order_by_prefix) ? $order_by_prefix. "." : "") . $db->quoteName($order_by); $query .= " " . $order_way . " LIMIT " . (int) $limit_start . ", " . (int) $limit; } $db->setQuery($query); $result = $db->loadObjectList(); if ($order_by == 'order_price') { JeproshopTools::orderbyPrice($result, $order_way); } if (!$result) { return array(); } self::$_pagination = new JPagination($total, $limit_start, $limit); /* Modify SQL result */ return JeproshopProductModelProduct::getProductsProperties($lang_id, $result); }
public function getCategoriesList() { jimport('joomla.html.pagination'); $db = JFactory::getDBO(); $app = JFactory::getApplication(); $option = $app->input->get('option'); $view = $app->input->get('view'); $context = JeproshopContext::getContext(); $limit = $app->getUserStateFromRequest('global.list.limit', 'limit', $app->getCfg('list_limit'), 'int'); $limitstart = $app->getUserStateFromRequest($option . $view . '.limit_start', 'limit_start', 0, 'int'); $lang_id = $app->getUserStateFromRequest($option . $view . '.lang_id', 'lang_id', $context->language->lang_id, 'int'); $shop_id = $app->getUserStateFromRequest($option . $view . '.shop_id', 'shop_id', $context->shop->shop_id, 'int'); $shop_group_id = $app->getUserStateFromRequest($option . $view . '.shop_group_id', 'shop_group_id', $context->shop->shop_group_id, 'int'); $category_id = $app->getUserStateFromRequest($option . $view . '.category_id', 'category_id', 0, 'int'); $order_by = $app->getUserStateFromRequest($option . $view . '.order_by', 'order_by', 'date_add', 'string'); $order_way = $app->getUserStateFromRequest($option . $view . '.order_way', 'order_way', 'ASC', 'string'); $published = $app->getUserStateFromRequest($option . $view . '.published', 'published', 0, 'string'); $count_categories_without_parent = count(JeproshopCategoryModelCategory::getCategoriesWithoutParent()); $top_category = JeproshopCategoryModelCategory::getTopCategory(); $parent_id = 0; if ($category_id) { $category = new JeproshopCategoryModelCategory($category_id); $parent_id = $category->category_id; } elseif (!JeproshopShopModelShop::isFeaturePublished() && $count_categories_without_parent > 1) { $parent_id = $top_category->category_id; } elseif (JeproshopShopModelShop::isFeaturePublished() && $count_categories_without_parent == 1) { $parent_id = JeproshopSettingModelSetting::getValue('root_category'); } elseif (JeproshopShopModelShop::isFeaturePublished() && $count_categories_without_parent > 1 && JeproshopShopModelShop::getShopContext() != JeproshopShopModelShop::CONTEXT_SHOP) { if (JeproshopSettingModelSetting::getValue('multishop_feature_active') && count(JeproshopShopModelShop::getShops(true, null, true)) == 1) { $parent_id = $context->shop->category_id; } else { $parent_id = $top_category->category_id; } } $explicitSelect = true; /* Manage default params values */ $use_limit = true; if ($limit === false) { $use_limit = false; } $join = " LEFT JOIN " . $db->quoteName('#__jeproshop_category_shop') . " AS category_shop ON (category." . $db->quoteName('category_id') . " = category_shop." . $db->quoteName('category_id') . " AND "; if (JeproshopShopModelShop::getShopContext() == JeproshopShopModelShop::CONTEXT_SHOP) { $join .= " category_shop.shop_id = " . (int) $context->shop->shop_id . ") "; } else { $join .= " category_shop.shop_id = category.default_shop_id)"; } // we add restriction for shop if (JeproshopShopModelShop::getShopContext() == JeproshopShopModelShop::CONTEXT_SHOP && JeproshopShopModelShop::isFeaturePublished()) { $where = " AND category_shop." . $db->quoteName('shop_id') . " = " . (int) JeproshopContext::getContext()->shop->shop_id; } /* Check params validity */ if (!JeproshopTools::isOrderBy($order_by) || !JeproshopTools::isOrderWay($order_way) || !is_numeric($limitstart) || !is_numeric($limit) || !JeproshopTools::isUnsignedInt($lang_id)) { echo JError::raiseError(500, 'get list params is not valid'); } /* Cache */ if (preg_match('/[.!]/', $order_by)) { $order_by_split = preg_split('/[.!]/', $order_by); $order_by = bqSQL($order_by_split[0]) . '.`' . bqSQL($order_by_split[1]) . '`'; } elseif ($order_by) { $order_by = $db->quoteName($db->escape($order_by)); } // Add SQL shop restriction $shopLinkType = ""; $select_shop = $join_shop = $where_shop = ''; /*if ($shopLinkType){ $select_shop = ", shop.shop_name as shop_name "; $join_shop = " LEFT JOIN " ._DB_PREFIX_.$this->shopLinkType.' shop ON a.id_'.$this->shopLinkType.' = shop.id_'.$this->shopLinkType; $where_shop = JeproshopShopModelShop::addSqlRestriction('1', 'category'); }*/ if ($context->controller->multishop_context && JeproshopShopModelShop::isTableAssociated('category')) { if (JeproshopShopModelShop::getShopContext() != JeproshopShopModelShop::CONTEXT_ALL || !$context->employee->isSuperAdmin()) { $test_join = !preg_match('/`?' . preg_quote('#__jeproshop_category_shop') . '`? *category_shop/', $join); if (JeproshopShopModelShop::isFeaturePublished() && $test_join && JeproshopShopModelShop::isTableAssociated('category')) { $where .= " AND category.category_id IN ( SELECT category_shop.category_id FROM "; $where .= $db->quoteName('#__jeproshop_category__shop') . " AS category_shop WHERE category_shop."; $where .= "shop_id IN (" . implode(', ', JeproshopShopModelShop::getContextListShopIds()) . ") )"; } } } $select = ", category_shop.position AS position "; $tmpTableFilter = ""; /* Query in order to get results with all fields */ $lang_join = " LEFT JOIN " . $db->quoteName('#__jeproshop_category_lang') . " AS category_lang ON ("; $lang_join .= "category_lang." . $db->quoteName('category_id') . " = category." . $db->quoteName('category_id'); $lang_join .= " AND category_lang." . $db->quoteName('lang_id') . " = " . (int) $lang_id; if ($context->shop->shop_id) { if (!JeproshopShopModelShop::isFeaturePublished()) { $lang_join .= " AND category_lang." . $db->quoteName('shop_id') . " = 1"; } elseif (JeproshopShopModelShop::getShopContext() == JeproshopShopModelShop::CONTEXT_SHOP) { $lang_join .= " AND category_lang." . $db->quoteName('shop_id') . " = " . (int) $context->shop->shop_id; } else { $lang_join .= " AND category_lang." . $db->quoteName('shop_id') . " = category.default_shop_id"; } } $lang_join .= ") "; $having_clause = ''; if (isset($this->_filterHaving) || isset($this->_having)) { $having_clause = ' HAVING '; if (isset($this->_filterHaving)) { $having_clause .= ltrim($this->_filterHaving, ' AND '); } if (isset($this->_having)) { $having_clause .= $this->_having . ' '; } } do { $query = "SELECT SQL_CALC_FOUND_ROWS " . ($tmpTableFilter ? " * FROM (SELECT " : ""); if ($explicitSelect) { $query .= "category." . $db->quoteName('category_id') . ", category_lang." . $db->quoteName('name') . ", category_lang." . $db->quoteName('description'); $query .= " , category." . $db->quoteName('position') . " AS category_position, " . $db->quoteName('published'); } else { $query .= ($lang_id ? " category_lang.*," : "") . " category.*"; } $query .= (isset($select) ? rtrim($select, ", ") : "") . $select_shop . " FROM " . $db->quoteName('#__jeproshop_category') . " AS category " . $lang_join . (isset($join) ? $join . " " : ""); $query .= $join_shop . " WHERE 1 " . (isset($where) ? $where . " " : "") . ($this->deleted_category ? " AND category." . $db->quoteName('deleted') . " = 0 " : "") . "AND " . $db->quoteName('parent_id'); $query .= "= " . (int) $parent_id . $where_shop . (isset($group) ? $group . " " : "") . $having_clause . " ORDER BY " . (str_replace('`', '', $order_by) == 'category_id' ? "category." : "") . " category."; $query .= $order_by . " " . $db->escape($order_way) . ($tmpTableFilter ? ") tmpTable WHERE 1" . $tmpTableFilter : ""); $db->setQuery($query); $total = count($db->loadObjectList()); $query .= $use_limit === true ? " LIMIT " . (int) $limitstart . ", " . (int) $limit : ""; $db->setQuery($query); $categories = $db->loadObjectList(); if ($use_limit === true) { $limitstart = (int) $limitstart - (int) $limit; if ($limitstart < 0) { break; } } else { break; } } while (empty($categories)); if (!empty($categories)) { foreach ($categories as $item) { $category_tree = JeproshopCategoryModelCategory::getChildren((int) $item->category_id, $context->language->lang_id); $item->set_view = count($category_tree) ? 1 : 0; } } $this->pagination = new JPagination($total, $limitstart, $limit); return $categories; }