Combining Salemaker and Specials in Category List

[Guest]

As far as I can tell Specials in Category List is pretty straight forward here are the install instructions:

Step 1:

Using the Admin tool create a "Specials" category at the top level of the categories tree.  

Do NOT put any products in this category!!!!

Make a note of the category id number.  You can find this out by clicking on the Specials link and looking at the URL in your browser where it says "......index.php?cPath=xx&....."  the xx is the category id.

+++++++++++++++++++++++++++++++
Step 2:

Open up \catalog\index.php in your editor

At line 15 immediately after:

 require('includes/application_top.php');

add:

// Flag for SPECIALS category - change this number to your specials category id number
 if ($cPath == 139) {		   change 30 to xx from your URL
  $AHspecials = 1;
 }
+++++++++++++++++++++++++++++++
Step 3:

at about line 186 find:

  } else {
// show the products in a given categories

immediately after 
} else {   

add:
  if ($AHspecials == 1) { 
// We show all specials
	$listing_sql = "select " . $select_column_list . " p.products_id, pd.short_desc, 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, p.products_price) as final_price from " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where s.status = '1' and p.products_id = p2c.products_id and p.products_id = s.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id .  "'";
  } else {

It pulls information form Specials table

HOWEVER....Salemaker has its own database salemaker_sales with the following fields

 

 

sale_id, sale_status, sale_name, sale_deduction_value, sale_deduction_type, sale_pricerange_from, sale_pricerange_to sale_specials_condition, sale_categories_selected, sale_categories_all, sale_date_start, sale_date_end, sale_date_added, sale_date_last_modified, sale_date_status_change

 

Since it doesn't pull from the specials table you would think that this would make a nightmare, but it gets around that by (I think) this function in general.php it comments out this

// Return a product's special price (returns nothing if there is no offer)
// TABLES: products
//  function tep_get_products_special_price($product_id) {
//   $product_query = tep_db_query("select specials_new_products_price from " . TABLE_SPECIALS . " where products_id = '" . (int)$product_id . "' and status");
//	$product = tep_db_fetch_array($product_query);

//	return $product['specials_new_products_price']; }

and replaces it with

function tep_get_products_special_price($product_id) {
$product_query = tep_db_query("select products_price, products_model from " . TABLE_PRODUCTS . " where products_id = '" . $product_id . "'");
if (tep_db_num_rows($product_query)) {
  $product = tep_db_fetch_array($product_query);
  $product_price = $product['products_price'];
} else {
  return false;
}

$specials_query = tep_db_query("select specials_new_products_price from " . TABLE_SPECIALS . " where products_id = '" . $product_id . "' and status");
if (tep_db_num_rows($specials_query)) {
  $special = tep_db_fetch_array($specials_query);
  $special_price = $special['specials_new_products_price'];
} else {
  $special_price = false;
}

if(substr($product['products_model'], 0, 4) == 'GIFT') {	//Never apply a salededuction to Ian Wilson's Giftvouchers
  return $special_price;
}

$product_to_categories_query = tep_db_query("select categories_id from " . TABLE_PRODUCTS_TO_CATEGORIES . " where products_id = '" . $product_id . "'");
$product_to_categories = tep_db_fetch_array($product_to_categories_query);
$category = $product_to_categories['categories_id'];

$sale_query = tep_db_query("select sale_specials_condition, sale_deduction_value, sale_deduction_type from " . TABLE_SALEMAKER_SALES . " where sale_categories_all like '%," . $category . ",%' and sale_status = '1' and (sale_date_start <= now() or sale_date_start = '0000-00-00') and (sale_date_end >= now() or sale_date_end = '0000-00-00') and (sale_pricerange_from <= '" . $product_price . "' or sale_pricerange_from = '0') and (sale_pricerange_to >= '" . $product_price . "' or sale_pricerange_to = '0')");
if (tep_db_num_rows($sale_query)) {
  $sale = tep_db_fetch_array($sale_query);
} else {
  return $special_price;
}

No that not all of it, but it is enough to see what it is doing. This seems to allow the specials to pretend to be in the specials table, when they aren't really (at least that is what I have surmized since there is nothing in my specials table). The biggest difference between the old way and the Salemaker seems to be that there are not individual products on sale. SaleMaker since it can claim a whole category and its nested categories to the Sale, does not have individual product on sale. The way I perceive it is that it calculates the amount off during the query (more on the fly than the old way). Instead of having dozens of items on sale in your Specials table you have 1 sale based on categories.

 

So what I want I'd like to know if I can do is have all the items that are on sale through Salemaker show up in my Specials Category - A quick reference for customers to see what all is on sale. I think it will just be a query in Specials to refer the sale items to the new category as it did when it flagged them in step 2 at the top of this page.

If anyone has any ideas or suggestions, I'd love to hear from you.

Thanks

Teresa

[Guest]

Okay, so no response - Yikes...could someone at least tell me if I need to make a Module for it, say like

new_products or product_listing?....I'm thinking not as I think that is making things to complicated. I'm thinking still that this is just a query in catalog/specials.php and/or some adjustments to catalog/classes/specials.php, but I am not sure about the later, haven't really figured out what classes do yet...that's my task for today.

 

Again I'd sure love a push in the right direction.

 

Thanks again

Teresa

[Guest]

Okay DUH there is no specials in classes, I really did know that, I was thinking of boxes and that I do know what it does...more or less just a link with picture. Brain is a little mushy this morning.

Teresa

[Guest]

Still No One has any suggestions, huh?!

Well I have added this to specials.php from products_new.php to see if it is a start buy I have some kind of syntax problem and was hoping someone might tell me what it is. Considering I pulled it from another page it should work for the most part...I'm guessing it has to do with the MAX_DISPLAY stuff....Below is the error...

 

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from products where products_status = '1' order by products_date

 

select (products_id, products_image, products_tax_class_id, products_price from products where products_status = '1' order by products_date_added desc limit 9

 

 

AND Here is the code in specials.php that I put in and messed with.

 

<?php
//ADD SALEMAKER BOF
if ( (!isset($new_products_category_id)) || ($new_products_category_id == '0') ) {
$specials_products_query = tep_db_query("select (products_id, products_image, products_tax_class_id, products_price from " . TABLE_PRODUCTS . " where products_status = '1' order by products_date_added desc limit " . MAX_DISPLAY_SPECIAL_PRODUCTS);
 } else {
$specials_products_query = tep_db_query("select (p.products_id, p.products_image, p.products_tax_class_id, p.products_price from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_CATEGORIES . " c where p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and c.parent_id = '" . $new_products_category_id . "' and p.products_status = '1' order by p.products_date_added desc limit " . MAX_DISPLAY_SPECIAL_PRODUCTS);
 }

//ADD SALEMAKER EOF

 $specials_split = new splitPageResults($specials_products_query, MAX_DISPLAY_SPECIAL_PRODUCTS);

 if (($specials_split->number_of_rows > 0) && ((PREV_NEXT_BAR_LOCATION == '1') || (PREV_NEXT_BAR_LOCATION == '3'))) {
?>
  <tr>
	<td><table border="0" width="100%" cellspacing="0" cellpadding="2">
	  <tr>
		<td class="smallText"><?php echo $specials_split->display_count(TEXT_DISPLAY_NUMBER_OF_SPECIALS); ?></td>
		<td align="right" class="smallText"><?php echo TEXT_RESULT_PAGE . ' ' . $specials_split->display_links(MAX_DISPLAY_PAGE_LINKS, tep_get_all_get_params(array('page', 'info', 'x', 'y'))); ?></td>
	  </tr>
	</table></td>
  </tr>
  <tr>
	<td><?php echo tep_draw_separator('pixel_trans.gif', '100%', '10'); ?></td>
  </tr>
<?php
 }
?>
  <tr>
	<td><table border="0" width="100%" cellspacing="0" cellpadding="2">
	  <tr>
<?php
$row = 0;
$specials_query = tep_db_query($specials_split->sql_query);
while ($specials = tep_db_fetch_array($specials_query)) {
//TRYING TO MAKE SPECIALS SHOW UP WITH SALEMAKER INSTEAD OF SPECIALS
  $special_price = tep_get_products_special_price($specials['products_id']);
if ($special_price) {
  $products_price = '<s>' .  $currencies->display_price($specials['products_price'], tep_get_tax_rate($new_products['products_tax_class_id'])) . '</s>  <span class="productSpecialPrice">' . $currencies->display_price($special_price, tep_get_tax_rate($specials['products_tax_class_id'])) . '</span>';
  } else {
  $products_price = $currencies->display_price($specials['products_price'], tep_get_tax_rate($new_products['products_tax_class_id']));
}

  $row++;

//  echo '			<td align="center" width="33%" class="smallText"><a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $specials['products_id']) . '">' . tep_image(DIR_WS_IMAGES . $specials['products_image'], $specials['products_name'], SMALL_IMAGE_WIDTH, SMALL_IMAGE_HEIGHT) . '</a><br><a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $specials['products_id']) . '">' . $specials['products_name'] . '</a><br><s>' . $currencies->display_price($specials['products_price'], tep_get_tax_rate($specials['products_tax_class_id'])) . '</s><br><span class="productSpecialPrice">' . $currencies->display_price($specials['specials_new_products_price'], tep_get_tax_rate($specials['products_tax_class_id'])) . '</span></td>' . "\n";
//ABOVE REPLACED WITH BELOW
//ADDED SALEMAKER BOF
  echo '			<td align="center" width="33%" class="smallText"><a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $specials['products_id']) . '">' . tep_image(DIR_WS_IMAGES . $specials['products_image'], $specials['products_name'], SMALL_IMAGE_WIDTH, SMALL_IMAGE_HEIGHT) . '</a><br><a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $specials['products_id']) . '">' . $specials['products_name'] . '</a><br><s>' . $currencies->display_price($specials['products_price'], tep_get_tax_rate($specials['products_tax_class_id'])) . '</s><br><span class="productSpecialPrice">' . $currencies->display_price(tep_get_products_special_price($specials['products_id']), tep_get_tax_rate($specials['products_tax_class_id'])) . '</span></td>' . "\n";

//ADDED SALEMAKER EOF
  if ((($row / 3) == floor($row / 3))) {
?>

 

Please...Any help would be appreciated.

Teresa