Remove featured and specials from new products

Hello,

This is not a question, is a code contribution for an issue i was having and that i couldn't find on the forums nor in the admin options (maybe i overlooked the solution). Hope this helps someone, i'm new to Zencart and forum and this kind of stuff i believe do help starters like me.
It's such a small tweak that making it a plugin contribution is like too much. The template override system is perfect for this kind of small changes. There are for sure many other ways to accomplish this but this one is very simple and straight forward.

If you have activated the new products, featured products and specials products center modules in the index page or any other page then by default you'll notice that featured and specials products repeat in the new products. This can be kind of inconsistent/redundant for most cart cases and other different products could be taking advantage of the place that is being used by a product that is already displayed 2 rows below. Not to mention that it may break some designs.

Changing a little sql sentence can fix this. All you need to do is make a copy of:
Zencart -> includes -> modules -> new_products.php
to
Zencart -> includes -> modules -> %MY_TEMPLATE -> %new_products.php

Now edit your newly copied file and find the only two sql strings in the file (they are almost at the beggining of the file), you'll need to paste the following 2 lines in both sql strings:

and coalesce((select f.status from " . TABLE_FEATURED . " f where f.products_id = p.products_id), 0) = 0
and coalesce((select s.status from " . TABLE_SPECIALS . " s where s.products_id = p.products_id), 0) = 0

The first line will remove the featured products and the second the specials ones.

Your file will result in something like this:

if ( (($manufacturers_id > 0 && $_GET['filter_id'] == 0) || $_GET['music_genre_id'] > 0 || $_GET['record_company_id'] > 0) || (!isset($new_products_category_id) || $new_products_category_id == '0') ) {
  $new_products_query = "select distinct p.products_id, p.products_image, p.products_tax_class_id, pd.products_name,
                                p.products_date_added, p.products_price, p.products_type, p.master_categories_id
                           from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd
                           where p.products_id = pd.products_id
                           and pd.language_id = '" . (int)$_SESSION['languages_id'] . "'
                           and coalesce((select f.status from " . TABLE_FEATURED . " f where f.products_id = p.products_id), 0) = 0
                           and coalesce((select s.status from " . TABLE_SPECIALS . " s where s.products_id = p.products_id), 0) = 0
                           and   p.products_status = 1 " . $display_limit;
} else {
  // get all products and cPaths in this subcat tree
  $productsInCategory = zen_get_categories_products_list( (($manufacturers_id > 0 && $_GET['filter_id'] > 0) ? zen_get_generated_category_path_rev($_GET['filter_id']) : $cPath), false, true, 0, $display_limit);

  if (is_array($productsInCategory) && sizeof($productsInCategory) > 0) {
    // build products-list string to insert into SQL query
    foreach($productsInCategory as $key => $value) {
      $list_of_products .= $key . ', ';
    }
    $list_of_products = substr($list_of_products, 0, -2); // remove trailing comma

    $new_products_query = "select distinct p.products_id, p.products_image, p.products_tax_class_id, pd.products_name,
                                  p.products_date_added, p.products_price, p.products_type, p.master_categories_id
                           from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd
                           where p.products_id = pd.products_id
                           and pd.language_id = '" . (int)$_SESSION['languages_id'] . "'
                           and coalesce((select f.status from " . TABLE_FEATURED . " f where f.products_id = p.products_id), 0) = 0
                           and coalesce((select s.status from " . TABLE_SPECIALS . " s where s.products_id = p.products_id), 0) = 0
                           and p.products_status = 1
                           and p.products_id in (" . $list_of_products . ")";
  }
}

Your new products will now appear clean, featured products will display in featured products only and specials on their respective place only.