Page 1 of 1

Hide product based on value

Posted: Wed Nov 10, 2010 5:53 am
by MrConn
Hi,
Im currently working on a mod making it possible to only allow customers from a certain zone to see/buy a product...

IE:
Your store sells gift-items. You would like the whole world to buy these!
But you also sell baked goods. You would not want to send those half way around the world! :P

So what my mod does is this:
When you add (or edit) a product you can select a specified zone for that product. (If none is selected, product will be visible to all)
Then when a customer visits your store he will only see the items he can purchase in his zone! (And all items that dont have any zone specified)

But I have a problem...
When I hide a item cause of the zone, it just "blanks" out... IE: In Latest Products there are by default 9 items, and if one of them is hidden because of the zone that item "blanks" out, and there are only shown 8 items! :-/
Live example

My code (catalog\view\theme\default\template\module\latest_home.tpl)

Code: Select all

<?php if ($products) { ?>
<div class="top">
  <div class="left"></div>
  <div class="right"></div>
  <div class="center">
    <div class="heading"><?php echo $heading_title; ?></div>
  </div>
</div>
<div class="middle">
  <table class="list">

  <?php for ($i = 0; $i < sizeof($products); $i = $i + 4) { ?>
    <tr>
      <?php for ($j = $i; $j < ($i + 4); $j++) { ?>
      <td style="width: 25%;"><?php if (isset($products[$j])) { ?>
        <!-- Zone-check -->
      	<?php if (($address['zone_id'] == $products[$j]['zone']) || ($products[$j]['zone'] == '0')) { ?>
        <!-- Zone-check end -->
      <a href="<?php echo str_replace('&', '&', $products[$j]['href']); ?>"><img src="<?php echo $products[$j]['thumb']; ?>" title="<?php echo $products[$j]['name']; ?>" alt="<?php echo $products[$j]['name']; ?>" /></a><br />
      <a href="<?php echo str_replace('&', '&', $products[$j]['href']); ?>"><?php echo $products[$j]['name']; ?></a><br />
      <span style="color: #999; font-size: 11px;"><?php echo $products[$j]['model']; ?></span><br />
      <?php if ($display_price) { ?>
      <?php if (!$products[$j]['special']) { ?>
      <span style="color: #900; font-weight: bold;"><?php echo $products[$j]['price']; ?></span>
      <?php } else { ?>
      <span style="color: #900; font-weight: bold; text-decoration: line-through;"><?php echo $products[$j]['price']; ?></span> <span style="color: #F00;"><?php echo $products[$j]['special']; ?></span>
      <?php } ?>
      <a class="button_add_small" href="<?php echo $products[$j]['add']; ?>" title="<?php echo $button_add_to_cart; ?>" >&nbsp;</a>
      <?php } ?>
      <br />
      <?php if ($products[$j]['rating']) { ?>
      <img src="catalog/view/theme/default/image/stars_<?php echo $products[$j]['rating'] . '.png'; ?>" alt="<?php echo $products[$j]['stars']; ?>" />
      <?php } ?>
      <!-- Zone-check -->
      <?php } ?>
      <!-- Zone-check end -->
      <?php } ?></td>
      <?php } ?>
    </tr>
    <?php } ?>
  </table>
</div>

<div class="bottom">
  <div class="left"></div>
  <div class="right"></div>
  <div class="center"></div>
</div>
<?php } ?>

Re: Hide product based on value

Posted: Wed Nov 10, 2010 6:17 am
by Qphoria
I would think it might be better to handle this as the model level so that all queries that return products could add a zone check

Re: Hide product based on value

Posted: Wed Nov 10, 2010 6:27 am
by MrConn
Yeah, I thought about that... But I dont know how...

I guess I have to change "public function getProducts()" in model/catalog/product.php somehow...
Could you give me a pointer?
I have made two new fields in products in mysql; country_id and zone_id

Re: Hide product based on value

Posted: Thu Nov 11, 2010 12:38 am
by MrConn
Any chance anyone can give me a litte help here?

Re: Hide product based on value

Posted: Thu Nov 11, 2010 12:47 am
by Qphoria
Well first you'd need the geozone parameter in the product. You can do this a few different ways
1. use an existing unused field like location to enter the geozone ids (sloppy but easier)
2. create a proper multi-select dropdown of geozones and create a new product_to_geozone table (best but advanced)

Then you would add

Code: Select all

LEFT JOIN " . DB_PREFIX . "product_to_geozone ptg ON (p.product_id == ptg.product_id)..... WHERE ptg.geozone == $geozone_id
To all product queries within the catalog/model/catalog/product.php file. There are about 12 places I think.
You'd have to pass in the geozone_id as well to that function by argument or cheat and use a session variable


Then it would only pull the items that are valid for the customer's geozone

Re: Hide product based on value

Posted: Thu Nov 11, 2010 2:23 am
by MrConn
OK... I think I've got most of it... But I cant quite get my head around the model-file... The SQL is a little advanced for me... :-/

In the "Data"-tab in the admin-backend I've made a new field: "Allowed Zone"... Here you got the multi-select dropdown of countries and zones. These dropdowns stores to the database in "product" under the new fields "country_id" and "zone_id".

On the frontend I call the customers zone as "$address".

What should I write in the SQL in catalog/model/catalog/product.php ?

Code: Select all

LEFT JOIN " . DB_PREFIX . "zone_id ptg ON (p.product_id == ptg.product_id)..... WHERE ptg.zone_id == $address
I don't have a clue! ???

Re: Hide product based on value

Posted: Thu Nov 11, 2010 4:13 am
by Qphoria
It is a bit limiting to use the exact country and zone tho.. since you can only select one. That is why I recommended the geozone with product_to_geozone table.

But if you want it just to be a single country/zone then you can use that.

Instead of the LEFT JOIN stuff you can simply add:

Code: Select all

WHERE p.country_id = '" . $this->session->data['country_id'] . "' AND p.zone_id = '" . $this->session->data['zone_id'] . "'  

Re: Hide product based on value

Posted: Thu Nov 11, 2010 8:48 am
by MrConn
Thanx!
(Almost) everything works great now! But ofcourse I have a issue... :P

I manage to hide the products on all modules just fine. But in the module "Bestseller" the limit is set in a query before I can set my own limitations... So if a product in bestsellers is hidden, the module just drops a item...
I've tried to move the limit parameter down to the second query, but then it had no effect at all... (It showed ALL items that is marked sold)

This is my current code for bestsellers:

Code: Select all

	public function getBestSellerProducts($limit) {
		$product_data = $this->cache->get('product.bestseller.' . $this->config->get('config_language_id') . '.' . (int)$this->config->get('config_store_id') . '.' . $limit);

		if (!$product_data) { 
			$product_data = array();
			
			$query = $this->db->query("SELECT op.product_id, SUM(op.quantity) AS total FROM " . DB_PREFIX . "order_product op LEFT JOIN `" . DB_PREFIX . "order` o ON (op.order_id = o.order_id) WHERE o.order_status_id > '0' GROUP BY op.product_id ORDER BY total DESC LIMIT " . (int)$limit);
			
			foreach ($query->rows as $result) {
				$product_query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) WHERE p.product_id = '" . (int)$result['product_id'] . "' AND p.status = '1' AND p.date_available <= NOW() AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' AND (p.zone_id = '" . $this->session->data['zone_id'] . "' OR p.zone_id = '0')");
				
				if ($product_query->num_rows) {
					$product_data[] = $product_query->row;
				}
			}

			$this->cache->set('product.bestseller.' . $this->config->get('config_language_id') . '.' . (int)$this->config->get('config_store_id') . '.' . $limit, $product_data);
		}
		
		return $product_data;
	}

Re: Hide product based on value

Posted: Fri Nov 12, 2010 12:46 am
by MrConn
Finally got it!
Had to learn about the JOIN statement first :P

Re: Hide product based on value

Posted: Thu Dec 23, 2010 9:14 pm
by Dazzle
Hi,
we sell chilled and non-chilled food and this would be very useful - did you create a mod or is it a case of changing lots of core files? Would love to try it out.
thanks
Dazzle

Re: Hide product based on value

Posted: Thu Dec 23, 2010 11:45 pm
by SapporoGuy
This will change core files.