Post by MrConn » Wed Nov 10, 2010 5:53 am

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 } ?>

Image
Nemmelig.no - Billige Gadgets!


Active Member

Posts

Joined
Tue Mar 23, 2010 8:08 am

Post by Qphoria » Wed Nov 10, 2010 6:17 am

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

Image


User avatar
Administrator

Posts

Joined
Tue Jul 22, 2008 3:02 am

Post by MrConn » Wed Nov 10, 2010 6:27 am

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

Image
Nemmelig.no - Billige Gadgets!


Active Member

Posts

Joined
Tue Mar 23, 2010 8:08 am

Post by MrConn » Thu Nov 11, 2010 12:38 am

Any chance anyone can give me a litte help here?

Image
Nemmelig.no - Billige Gadgets!


Active Member

Posts

Joined
Tue Mar 23, 2010 8:08 am

Post by Qphoria » Thu Nov 11, 2010 12:47 am

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

Image


User avatar
Administrator

Posts

Joined
Tue Jul 22, 2008 3:02 am

Post by MrConn » Thu Nov 11, 2010 2:23 am

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! ???

Image
Nemmelig.no - Billige Gadgets!


Active Member

Posts

Joined
Tue Mar 23, 2010 8:08 am

Post by Qphoria » Thu Nov 11, 2010 4:13 am

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'] . "'  

Image


User avatar
Administrator

Posts

Joined
Tue Jul 22, 2008 3:02 am

Post by MrConn » Thu Nov 11, 2010 8:48 am

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;
	}

Image
Nemmelig.no - Billige Gadgets!


Active Member

Posts

Joined
Tue Mar 23, 2010 8:08 am

Post by MrConn » Fri Nov 12, 2010 12:46 am

Finally got it!
Had to learn about the JOIN statement first :P

Image
Nemmelig.no - Billige Gadgets!


Active Member

Posts

Joined
Tue Mar 23, 2010 8:08 am

Post by Dazzle » Thu Dec 23, 2010 9:14 pm

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

New member

Posts

Joined
Tue Nov 02, 2010 8:48 pm

Post by SapporoGuy » Thu Dec 23, 2010 11:45 pm

This will change core files.

930sc ... because it is fun!


User avatar
Active Member

Posts

Joined
Mon Nov 01, 2010 7:29 pm
Who is online

Users browsing this forum: No registered users and 105 guests