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;
}
