Group Pricing Per Item - sort products by price issue, suggested code to fix
Hi all,
[FONT=Arial][FONT=Arial]Recently a client has asked me about whether the standard product column display can sort products by price asc/desc based on group prices listed rather than the default retail price for products?
From what I could see the price column sorts using the values from the field ‘products_price_sorter’ from the products table, code for this [/FONT][/FONT][FONT=Arial][FONT=Arial]in /zc/includes/index_filters/default_filter.php , particularily this code[/FONT][/FONT]
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;
[FONT=Arial][FONT=Arial]From what I can see the field ‘products_price_sorter’ value is based on a products retail price and the ‘products_price_sorter’ field value can be updated/recalculated globally anytime via [/FONT][/FONT]Admin->Tools->Store Manager[FONT=Arial][FONT=Arial] or individually when inserted or updated, etc[/FONT][/FONT]
[FONT=Arial][FONT=Arial]Knowing all this doesn’t help the sorting of products via the ‘price’ column when using the wholesale group price per item module.[/FONT][/FONT]
[FONT=Arial][FONT=Arial]Test Scenario: [/FONT][/FONT]
[FONT=Arial][FONT=Arial]You may note that if the default retail price, (which ‘products_price_sorter’ is based on), is used to sort by price then the products in your cart will likely 'appear' sorted asc or desc BUT what happens if you have products with pricing like this?
[/FONT][/FONT] - - - - - - - - [FONT=Arial][FONT=Arial]- Retail - Group A - Group B[/FONT][/FONT]
[FONT=Arial][FONT=Arial] Product A - - - 70 - - - - 65[/FONT][/FONT][FONT=Arial][FONT=Arial] - - - - [/FONT][/FONT][FONT=Arial][FONT=Arial]65[/FONT][/FONT]
[FONT=Arial][FONT=Arial] Product B - - - 80 - - - - 75[/FONT][/FONT][FONT=Arial][FONT=Arial] - - - - [/FONT][/FONT][FONT=Arial][FONT=Arial]75[/FONT][/FONT]
[FONT=Arial][FONT=Arial] Product C - - - 90 - - - - 85[/FONT][/FONT][FONT=Arial][FONT=Arial] - - - - [/FONT][/FONT][FONT=Arial][FONT=Arial] 70[/FONT][/FONT]
[FONT=Arial][FONT=Arial]Since Group A discounts are respectively inline with the retail price then the returned listed products will appear to sort ok for them
ie p.A, p.B, p.C[/FONT][/FONT]
[FONT=Arial][FONT=Arial]but if my assumption is correct about products being sorted by retail price rather than actual group price then Group B customers will also see products sorted like p.A, p.B, p.C
whereas as they should see p.A, p.C, p.B[/FONT][/FONT]
[FONT=Arial][FONT=Arial]So if 'sorting by price' when using module 'group pricing per item', then consider the following code in next post...
(which I tested on an old zc install v1.3.6)
[/FONT][/FONT]
Re: Group Pricing Per Item - sort products by price issue, suggested code to fix
[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...
:cool:
Re: Group Pricing Per Item - sort products by price issue, suggested code to fix
Thanks for posting the details on how you managed this listing for the Price sorts on Group Pricing ... this is of great help to others ... :smile:
