[FONT=Arial]Potential method to resolve-fix sorting by group price per item...
[/FONT][FONT=Arial][FONT=Arial](note: tested on an old zc install v1.3.6, so some original code referenced here maybe a little different)[/FONT][/FONT]
[FONT=Arial]
After the following updates to a copy of a live site, i was able to see updates to the new group price sorter fields in dbase when 'inserting' or 'updating' a product and also when using the global price sort updater located in Admin->Tools->Store Manager
Step 1a) insert new fields in PRODUCTS table after `products_sort_order`
[/FONT]
Code:
ALTER TABLE `products` ADD `products_group_a_price_sorter` DECIMAL( 15, 4 ) NOT NULL AFTER `products_price_sorter` ,
ADD `products_group_b_price_sorter` DECIMAL( 15, 4 ) NOT NULL AFTER `products_group_a_price_sorter` ,
ADD `products_group_c_price_sorter` DECIMAL( 15, 4 ) NOT NULL AFTER `products_group_b_price_sorter` ,
ADD `products_group_d_price_sorter` DECIMAL( 15, 4 ) NOT NULL AFTER `products_group_c_price_sorter` ;
[FONT=Arial]
Step 1b) MODIFY function zen_update_products_price_sorter($product_id)
IN file
a) /admin/includes/functions/general.php
AND ALSO IN file
b) /includes/functions/functions_prices.php
[/FONT]REPLACE THIS CODE[FONT=Arial]
[/FONT]
PHP Code:
function zen_update_products_price_sorter($product_id) {
global $db;
$products_price_sorter = zen_get_products_actual_price($product_id);
$db->Execute("update " . TABLE_PRODUCTS . " set
products_price_sorter='" . zen_db_prepare_input($products_price_sorter) . "'
where products_id='" . (int)$product_id . "'");
}
WITH THIS CODE
PHP Code:
// set the products_price_sorter
function zen_update_products_price_sorter($product_id) {
global $db;
$products_price_sorter = zen_get_products_actual_price($product_id);
$db->Execute("update " . TABLE_PRODUCTS . " set
products_price_sorter='" . zen_db_prepare_input($products_price_sorter) . "'
where products_id='" . (int)$product_id . "'");
// the following is to set group price per item for sorting by price in the front end
$product_check = $db->Execute("select products_price, products_group_a_price, products_group_b_price, products_group_c_price, products_group_d_price from " . TABLE_PRODUCTS . " where products_id = '" . (int)$product_id . "'");
if ($product_check->fields['products_group_a_price'] != 0) {
$products_group_a_price_sorter = $product_check->fields['products_group_a_price'];
} else {
$products_group_a_price_sorter = zen_get_products_actual_price($product_id);
}
if ($product_check->fields['products_group_b_price'] != 0) {
$products_group_b_price_sorter = $product_check->fields['products_group_b_price'];
} else {
$products_group_b_price_sorter = zen_get_products_actual_price($product_id);
}
if ($product_check->fields['products_group_c_price'] != 0) {
$products_group_c_price_sorter = $product_check->fields['products_group_c_price'];
} else {
$products_group_c_price_sorter = zen_get_products_actual_price($product_id);
}
if ($product_check->fields['products_group_d_price'] != 0) {
$products_group_d_price_sorter = $product_check->fields['products_group_d_price'];
} else {
$products_group_d_price_sorter = zen_get_products_actual_price($product_id);
}
$db->Execute("update " . TABLE_PRODUCTS . " set
products_group_a_price_sorter='" . zen_db_prepare_input($products_group_a_price_sorter) . "',
products_group_b_price_sorter='" . zen_db_prepare_input($products_group_b_price_sorter) . "',
products_group_c_price_sorter='" . zen_db_prepare_input($products_group_c_price_sorter) . "',
products_group_d_price_sorter='" . zen_db_prepare_input($products_group_d_price_sorter) . "'
where products_id='" . (int)$product_id . "'");
}
Step 1c) Edit file
/includes/index_filters/default_filter.php
MODIFY all $listing_sql queries, include the 4 new fields similar to `products_group_a_price_sorter`, eg
PHP Code:
approx line 28
$listing_sql = "select " . $select_column_list . " p.products_id, p.products_type, p.manufacturers_id, p.products_price, p.products_tax_class_id, pd.products_description, if(s.status = 1, s.specials_new_products_price, NULL) AS specials_new_products_price, IF(s.status = 1, s.specials_new_products_price, p.products_price) as final_price, p.products_sort_order, p.product_is_call, p.product_is_always_free_shipping, p.products_qty_box_status
approx line 43
$listing_sql = "select " . $select_column_list . " p.products_id, p.products_type, p.manufacturers_id, p.products_price, p.products_tax_class_id, pd.products_description, IF(s.status = 1, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status = 1, s.specials_new_products_price, p.products_price) as final_price, p.products_sort_order, p.product_is_call, p.product_is_always_free_shipping, p.products_qty_box_status
approx line 58
$listing_sql = "select " . $select_column_list . " p.products_id, p.products_type, p.manufacturers_id, p.products_price, p.products_tax_class_id, pd.products_description, IF(s.status = 1, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status = 1, s.specials_new_products_price, p.products_price) as final_price, p.products_sort_order, p.product_is_call, p.product_is_always_free_shipping, p.products_qty_box_status
approx line 73
$listing_sql = "select " . $select_column_list . " p.products_id, p.products_type, p.manufacturers_id, p.products_price, p.products_tax_class_id, pd.products_description, IF(s.status = 1, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status =1, s.specials_new_products_price, p.products_price) as final_price, p.products_sort_order, p.product_is_call, p.product_is_always_free_shipping, p.products_qty_box_status
REPLACE ALL ABOVE LINES WITH CODE
PHP Code:
$listing_sql = "select " . $select_column_list . " p.products_id, p.products_type, p.manufacturers_id, p.products_price, p.products_tax_class_id, pd.products_description, IF(s.status = 1, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status = 1, s.specials_new_products_price, p.products_price) as final_price, p.products_sort_order, p.products_group_a_price_sorter, p.products_group_b_price_sorter, p.products_group_c_price_sorter, p.products_group_d_price_sorter, p.product_is_call, p.product_is_always_free_shipping, p.products_qty_box_status
AND ALSO in file /includes/index_filters/default_filter.php
REPLACE THIS CODE
PHP Code:
case 'PRODUCT_LIST_PRICE':
// $listing_sql .= "final_price " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";
$listing_sql .= "p.products_price_sorter " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";
break
WITH THIS CODE
PHP Code:
case 'PRODUCT_LIST_PRICE':
// $listing_sql .= "final_price " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";
if($_SESSION['customer_id']) {
$customer_group_query = "select gp.group_name
from " . TABLE_CUSTOMERS . " cu
left join " . TABLE_GROUP_PRICING . " gp on cu.customers_group_pricing=gp.group_id
where cu.customers_id = " . $_SESSION['customer_id'];
if($customer_group = $db->Execute($customer_group_query)) {
$customers_group=$customer_group->fields['group_name'];
}
}
if($customers_group) {
if ($customers_group == GROUP_PRICE_PER_ITEM1) {
$listing_sql .= "p.products_group_a_price_sorter " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";
}
if ($customers_group == GROUP_PRICE_PER_ITEM2) {
$listing_sql .= "p.products_group_b_price_sorter " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";
}
if ($customers_group == GROUP_PRICE_PER_ITEM3) {
$listing_sql .= "p.products_group_c_price_sorter " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";
}
if ($customers_group == GROUP_PRICE_PER_ITEM4) {
$listing_sql .= "p.products_group_d_price_sorter " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";
}
} else {
$listing_sql .= "p.products_price_sorter " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";
}
break;
Hope this was helpful to someone...
Bookmarks