Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2006
    Posts
    13
    Plugin Contributions
    0

    Default 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]

  2. #2
    Join Date
    Sep 2006
    Posts
    13
    Plugin Contributions
    0

    Default 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_idp.products_typep.manufacturers_idp.products_pricep.products_tax_class_idpd.products_description, IF(s.status 1s.specials_new_products_priceNULL) as specials_new_products_price, IF(s.status 1s.specials_new_products_pricep.products_price) as final_pricep.products_sort_orderp.product_is_callp.product_is_always_free_shippingp.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_idp.products_typep.manufacturers_idp.products_pricep.products_tax_class_idpd.products_description, IF(s.status 1s.specials_new_products_priceNULL) as specials_new_products_price, IF(s.status =1s.specials_new_products_pricep.products_price) as final_pricep.products_sort_orderp.product_is_callp.product_is_always_free_shippingp.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...

  3. #3
    Join Date
    Sep 2003
    Location
    Ohio
    Posts
    67,209
    Plugin Contributions
    6

    Default 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 ...
    Linda McGrath
    If you have to think ... you haven't been zenned ...

    Did YOU buy the Zen Cart Team a cup of coffee and a donut today? Just click here to support the Zen Cart Team!!

    Are you using the latest? Perhaps you've a problem that's fixed in the latest version: [Upgrade today: v1.5.3]
    Officially PayPal-Certified! Just click here

    Try our Zen Cart Recommended Services - Hosting, Payment and more ...

 

 

Similar Threads

  1. Group Pricing Per Item Issue
    By gdrudy in forum All Other Contributions/Addons
    Replies: 135
    Last Post: 29 Jan 2014, 06:21 PM
  2. Group Pricing per Item with Price by Attribute
    By RFree190 in forum All Other Contributions/Addons
    Replies: 0
    Last Post: 7 Mar 2010, 08:30 PM
  3. How to make an item Free in Group Pricing (Per Item)?
    By lyricwings in forum Discounts/Coupons, Gift Certificates, Newsletters, Ads
    Replies: 2
    Last Post: 1 Sep 2008, 09:41 PM
  4. Group Pricing (per Item) Issue
    By daschenbrener in forum Discounts/Coupons, Gift Certificates, Newsletters, Ads
    Replies: 2
    Last Post: 8 Jan 2007, 02:42 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •