function xos_get_subcategories(&$subcategories_array, $parent_id = 0)
{
$subcategories_query = xos_db_query("select categories_or_pages_id from " . TABLE_CATEGORIES_OR_PAGES . " where parent_id = '" . (int) $parent_id . "' and categories_or_pages_status = '1'");
while ($subcategories = xos_db_fetch_array($subcategories_query)) {
$subcategories_array[sizeof($subcategories_array)] = $subcategories['categories_or_pages_id'];
if ($subcategories['categories_or_pages_id'] != $parent_id) {
xos_get_subcategories($subcategories_array, $subcategories['categories_or_pages_id']);
}
}
}
$from_str = "from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS_INFO . " mi on (p.manufacturers_id = mi.manufacturers_id and mi.languages_id = '" . (int) $_SESSION['languages_id'] . "') left join " . TABLE_PRODUCTS_PRICES . " ppz on p.products_id = ppz.products_id and ppz.customers_group_id = '0' left join " . TABLE_PRODUCTS_PRICES . " pp on p.products_id = pp.products_id and pp.customers_group_id = '" . $customer_group_id . "' left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id and s.customers_group_id = '" . $customer_group_id . "'";
if ($_SESSION['sppc_customer_group_show_tax'] == '1' && $_SESSION['sppc_customer_group_tax_exempt'] != '1') {
if (!isset($_SESSION['customer_id'])) {
$customer_country_id = STORE_COUNTRY;
$customer_zone_id = STORE_ZONE;
} else {
$customer_country_id = $_SESSION['customer_country_id'];
$customer_zone_id = $_SESSION['customer_zone_id'];
}
$from_str .= " left join " . TABLE_ZONES_TO_GEO_ZONES . " gz on (gz.zone_country_id is null or gz.zone_country_id = '0' or gz.zone_country_id = '" . (int) $customer_country_id . "') and (gz.zone_id is null or gz.zone_id = '0' or gz.zone_id = '" . (int) $customer_zone_id . "') left join " . TABLE_TAX_RATES_FINAL . " tr on p.products_tax_class_id = tr.tax_class_id and gz.geo_zone_id = tr.tax_zone_id";
}
$from_str .= ", " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_CATEGORIES_OR_PAGES . " c, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c";
$where_str = " where p.products_status = '1' and c.categories_or_pages_status = '1' and p.products_id = pd.products_id and pd.language_id = '" . (int) $_SESSION['languages_id'] . "' and p.products_id = p2c.products_id and p2c.categories_or_pages_id = c.categories_or_pages_id ";
if (isset($_GET['copid']) && xos_not_null($_GET['copid'])) {
$subcategories_array = array();
xos_get_subcategories($subcategories_array, $_GET['copid']);
$where_str .= " and p2c.products_id = p.products_id and p2c.products_id = pd.products_id and (p2c.categories_or_pages_id = '" . (int) $_GET['copid'] . "'";
for ($i = 0, $n = sizeof($subcategories_array); $i < $n; $i++) {
$where_str .= " or p2c.categories_or_pages_id = '" . (int) $subcategories_array[$i] . "'";
}
$where_str .= ")";
}
if (isset($_GET['_m']) && xos_not_null($_GET['_m'])) {
$where_str .= " and mi.manufacturers_id = '" . (int) $_GET['_m'] . "'";
}
if (isset($search_keywords) && sizeof($search_keywords) > 0) {
$where_str .= " and (";
for ($i = 0, $n = sizeof($search_keywords); $i < $n; $i++) {
switch ($search_keywords[$i]) {
case '(':
case ')':
$select_column_list .= 'p.products_image, ';
break;
case 'PRODUCT_LIST_WEIGHT':
$select_column_list .= 'p.products_weight, ';
break;
}
}
if (!isset($_SESSION['customer_id'])) {
$customer_country_id = STORE_COUNTRY;
$customer_zone_id = STORE_ZONE;
} else {
$customer_country_id = $_SESSION['customer_country_id'];
$customer_zone_id = $_SESSION['customer_zone_id'];
}
$subcategories_array = array();
xos_get_subcategories($subcategories_array, $current_category_id);
$subcategories_str = " and (p2c.categories_or_pages_id = '" . (int) $current_category_id . "'";
for ($i = 0, $n = sizeof($subcategories_array); $i < $n; $i++) {
$subcategories_str .= " or p2c.categories_or_pages_id = '" . (int) $subcategories_array[$i] . "'";
}
$subcategories_str .= ")";
// show the products in a given categorie
if (isset($_GET['filter']) && xos_not_null($_GET['filter'])) {
// We are asked to show only specific catgeory
if ($_SESSION['sppc_customer_group_show_tax'] == '1' && $_SESSION['sppc_customer_group_tax_exempt'] != '1') {
$listing_sql = "select " . $select_column_list . " p.products_id, p.products_delivery_time_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, (IF(s.status, s.specials_new_products_price, IF(pp.customers_group_price >= 0, pp.customers_group_price, ppz.customers_group_price)) * if(tr.tax_rate_final is null, 1, 1 + (tr.tax_rate_final / 100))) as final_price, tr.tax_rate_final from " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS_INFO . " mi on (p.manufacturers_id = mi.manufacturers_id and mi.languages_id = '" . (int) $_SESSION['languages_id'] . "') left join " . TABLE_PRODUCTS_PRICES . " ppz on p.products_id = ppz.products_id and ppz.customers_group_id = '0' left join " . TABLE_PRODUCTS_PRICES . " pp on p.products_id = pp.products_id and pp.customers_group_id = '" . $customer_group_id . "' left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id and s.customers_group_id = '" . $customer_group_id . "' left join " . TABLE_ZONES_TO_GEO_ZONES . " gz on (gz.zone_country_id is null or gz.zone_country_id = '0' or gz.zone_country_id = '" . (int) $customer_country_id . "') and (gz.zone_id is null or gz.zone_id = '0' or gz.zone_id = '" . (int) $customer_zone_id . "') left join " . TABLE_TAX_RATES_FINAL . " tr on p.products_tax_class_id = tr.tax_class_id and gz.geo_zone_id = tr.tax_zone_id," . TABLE_PRODUCTS_TO_CATEGORIES . " p2c left join " . TABLE_CATEGORIES_OR_PAGES . " c on p2c.categories_or_pages_id = c.categories_or_pages_id where c.categories_or_pages_status = '1' and p.products_status = '1' and mi.manufacturers_id = '" . (int) $_GET['filter'] . "' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int) $_SESSION['languages_id'] . "' " . $subcategories_str . " group by p.products_id";
} else {
$listing_sql = "select " . $select_column_list . " p.products_id, p.products_delivery_time_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, IF(pp.customers_group_price >= 0, pp.customers_group_price, ppz.customers_group_price)) as final_price from " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS_INFO . " mi on (p.manufacturers_id = mi.manufacturers_id and mi.languages_id = '" . (int) $_SESSION['languages_id'] . "') left join " . TABLE_PRODUCTS_PRICES . " ppz on p.products_id = ppz.products_id and ppz.customers_group_id = '0' left join " . TABLE_PRODUCTS_PRICES . " pp on p.products_id = pp.products_id and pp.customers_group_id = '" . $customer_group_id . "' left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id and s.customers_group_id = '" . $customer_group_id . "'," . TABLE_PRODUCTS_TO_CATEGORIES . " p2c left join " . TABLE_CATEGORIES_OR_PAGES . " c on p2c.categories_or_pages_id = c.categories_or_pages_id where c.categories_or_pages_status = '1' and p.products_status = '1' and mi.manufacturers_id = '" . (int) $_GET['filter'] . "' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int) $_SESSION['languages_id'] . "' " . $subcategories_str . "";
}
} else {
// We show them all