add database entry in product_list.tpl

madmega · May 21, 2014

Hi, i am trying to program an extra stock item into product_list.tpl but it is not working.

I have stock display visible when i do a search, but now i want to add a line from another part of the sql database, but it is not fetching it, well let me show you

			<!--p class="product_desc"><a href="{$product.link|escape:'htmlall':'UTF-8'}" title="{$product.description_short|strip_tags:'UTF-8'|truncate:360:'...'}">{$product.description_short|truncate:360:'...'|strip_tags:'UTF-8'}</a></p-->
            {elseif isset($product.reduction) && $product.reduction && isset($product.show_price) && $product.show_price && !$PS_CATALOG_MODE}<span class="discount">{l s='Reduced price!'}</span>{/if}
            {if isset($product.online_only) && $product.online_only}<span class="online_only">{l s='Online only!'}</span>{/if*}
            {if (!$PS_CATALOG_MODE AND ((isset($product.show_price) && $product.show_price) || (isset($product.available_for_order) && $product.available_for_order)))}
            <div>
			    {if isset($product.show_price) && $product.show_price && !isset($restricted_country_mode)}<p class="price_container"><span class="price">{if !$priceDisplay}{convertPrice price=$product.price}{else}{convertPrice price=$product.price_tax_exc}{/if}</span></p>{/if}
                {if $product.vms_expected_arrival_date && $product.quantity <= 0 && ($product.vms_supplier_stock neq 'Y' && $product.vms_supplier_stock neq 'HIGH' && $product.vms_supplier_stock neq 'High' && $product.vms_supplier_stock lt 1)}
                    <a id="fancy_{$product.id_product}" href="{$product.link|escape:'htmlall':'UTF-8'}" class="availability_icon st_verylow" title="{l s='Shipping time'} - {l s='Expected'}: {$product.vms_expected_arrival_date|escape:'htmlall':'UTF-8'}">{l s='Expected'}: {$product.vms_expected_arrival_date|escape:'htmlall':'UTF-8'}</a>
                
				{elseif $product.quantity <= 0 && ($product.vms_supplier_stock eq 'Y' || $product.vms_supplier_stock eq 'HIGH' || $product.vms_supplier_stock eq 'High' || $product.vms_supplier_stock eq 'Medium' || $product.vms_supplier_stock eq 'Low' || $product.vms_supplier_stock gt 0)}
                    <a id="fancy_{$product.id_product}" href="{$product.link|escape:'htmlall':'UTF-8'}" class="availability_icon st_medium" title="{l s='Shipping time'} - {l s='5 - 10 working days'}">{l s='5 - 10 working days'}</a>
                
				{elseif !$product.vms_expected_arrival_date && $product.quantity <= 0 && !$product.vms_supplier_stock}
                    <a id="fancy_{$product.id_product}" href="{$product.link|escape:'htmlall':'UTF-8'}" class="availability_icon st_low" title="{l s='Shipping time'} - {l s='Available soon'}">{l s='Available soon'}</a>
                
				{elseif !$product.vms_expected_arrival_date && $product.quantity <= 0 && ($product.vms_supplier_stock neq 'Y' && $product.vms_supplier_stock neq 'HIGH' && $product.vms_supplier_stock neq 'High' && $product.vms_supplier_stock lt 1)}
                    <a id="fancy_{$product.id_product}" href="{$product.link|escape:'htmlall':'UTF-8'}" class="availability_icon st_outofstock" title="{l s='Shipping time'} - {l s='On request'}">{l s='On request'}</a>
 
{elseif $vms_stock.id_store == 6 && vms_stock.quantity >0}
<a id="fancy_{$product.id_product}" href="{$product.link|escape:'htmlall':'UTF-8'}" class="availability_icon st_veryhigh" title="{l s='Shipping time'} - {l s='Ordered before 4pm, shipped the same day'}">{l s='Shipped Today'}</a>TEST 1
 
{else}
				
<a id="fancy_{$product.id_product}" href="{$product.link|escape:'htmlall':'UTF-8'}" class="availability_icon st_high" title="{l s='Shipping time'} - {l s='2 - 5 working days'}">{l s='2 - 5 working days'}</a>

            {/if}

the line:
{elseif $vms_stock.id_store == 6 && vms_stock.quantity >0}

is just being skipped, as if it doesn't want to look into the ps_vms_stock entry of the database.

Do i have to edit a class file ?

CartExpert.net · May 22, 2014

Hi.

This line is incorrect:

{elseif $vms_stock.id_store == 6 && vms_stock.quantity >0}

What is this $vms_stock, did you assign it do the template? As the tpl file is looping through the products, I assume this is also an array?

Regards.

Robin.

The CartExpert Team

madmega · May 23, 2014

Hi vms_stock is actually ps_vms_stock, an entry in the database, where i have to access columns id_store and quantity.
I do not not know where to assign as it seems that product_list.tpl only seems to have access to ps_product and no other entry in the database.

CartExpert.net · May 23, 2014

Hi.

product_list.tpl doesn't have access to any tables as such.

$products is an array and $product is an item of that array, not the table ps_product.

$products contains the result of an SQL query. This SQL query retrieves data from multiple tables: ps_product, ps_product_lang etc.

Regards.

Robin.

The CartExpert Team

madmega · May 24, 2014

Hi thank you for your answer, but how can i make it possible, i really need the data of the two columns of that table.

In products.tpl i mamaged to pull it off but just not in product_list.tpl

bellini13 · May 24, 2014

You should be overriding the CategoryController module to obtain the information you need, and then add those objects to the smarty engine so it can be used by the product_list.tpl template file.

madmega · May 24, 2014

I believe the controller for the product_list.tpl was classes/search.php ?

bellini13 · May 24, 2014

that is not a controller, that is the data model.

As I said, the controller is CategoryController and this is where you should perform your data query, and transformation from the data model to the smarty engine.

madmega · May 25, 2014

I am sorry but in my categorycontroller.php i just cannot find how to do this :-(

Edited May 25, 2014 by madmega (see edit history)

bellini13 · May 26, 2014

so why don't you try to explain what you are trying to do exactly, without giving us coding, tell us exactly what you are attempting

CartExpert.net · May 26, 2014

The getProducts method of the category class in retrieving the products from the DB.

You need to edit the SQL query there.

Regards.

Robin.

The CartExpert Team

madmega · May 27, 2014

Okay i'll try to explain what i did and what i want to do.

In our products.tpl, i managed to show the stock of 1 of our shops, our webshop, with a status that if ordered before 4pm, shipped the same day.
That actually worked, and what i did to do that was this code.

{foreach from=$stock_location item=stock}
{if $stock['name'] == 'Westzaan'}
{if $stock['quantity'] > 0}

<span class="availability_icon" id="st_veryhigh" title="{l s='Ordered before 4pm, shipped the same day'}">
<a href="../levertijdindicatie.jpg" class="iframe">{l s='Shipped Today'}</a></span>
				
{else}
				
                <span class="availability_icon" id="st_high" title="{l s='This product is on stock in our warehouse and has a shipping time of 2-5 working days.'}">
                <a href="../levertijdindicatie.jpg" class="iframe">{l s='2-5 working days'}</a></span>



{/if}{/if}
{/foreach}

And this is only part of the code, but somehow it will check in the sql database if there's shop called Westzaan and if it has more than 0 stock.

So that worked, but this doesn't work in product_list.tpl, somehow it doesn't understand that code, so i tried something else, to get it directly from the SQL table.

And that is where this code came in:

{elseif $vms_stock.id_store == 6 && vms_stock.quantity >0}
<a id="fancy_{$product.id_product}" href="{$product.link|escape:'htmlall':'UTF-8'}" class="availability_icon st_veryhigh" title="{l s='Shipping time'} - {l s='Ordered before 4pm, shipped the same day'}">{l s='Shipped Today'}</a>TEST 1
 
{else}
				
<a id="fancy_{$product.id_product}" href="{$product.link|escape:'htmlall':'UTF-8'}" class="availability_icon st_high" title="{l s='Shipping time'} - {l s='2 - 5 working days'}">{l s='2 - 5 working days'}</a>

            {/if}

Where vms_stock is the entry in the database and id_store the column.
The same for vms_stock.quantity
Hope you guys can understand ?

bellini13 · May 27, 2014

And where have you placed your code to execute the vms_stock queries? As I said before, CategoryController is where Prestashop triggers the query to obtain products within 'this' category.

So either you need to edit the existing query to join your stock table, or you should perform your own query and then just merge the 2 result sets together.

madmega · May 27, 2014

Let me show you the complete product_list.tpl

{*
* 2007-2012 PrestaShop
*
* NOTICE OF LICENSE
*
* This source file is subject to the Academic Free License (AFL 3.0)
* that is bundled with this package in the file LICENSE.txt.
* It is also available through the world-wide-web at this URL:
* http://opensource.org/licenses/afl-3.0.php
* If you did not receive a copy of the license and are unable to
* obtain it through the world-wide-web, please send an email
* to [email protected] so we can send you a copy immediately.
*
* DISCLAIMER
*
* Do not edit or add to this file if you wish to upgrade PrestaShop to newer
* versions in the future. If you wish to customize PrestaShop for your
* needs please refer to http://www.prestashop.com for more information.
*
*  @author PrestaShop SA <[email protected]>
*  @copyright  2007-2012 PrestaShop SA
*  @version  Release: $Revision: 14008 $
*  @license    http://opensource.org/licenses/afl-3.0.php  Academic Free License (AFL 3.0)
*  International Registered Trademark & Property of PrestaShop SA
*}

{if isset($products)}
	<!-- Products list -->
	<ul id="product_list" class="clear">
	{foreach from=$products item=product name=products}
		<li class="ajax_block_product {if $smarty.foreach.products.first}first_item{elseif $smarty.foreach.products.last}last_item{/if}{if $smarty.foreach.products.iteration % 4 == 0} last_item_of_line{/if}{if $smarty.foreach.products.index % 2} alternate_item{else} item{/if}">

{if isset($product.sales_price) && isset($product.action_price) && ($product.sales_price > $product.action_price)} <img style="vertical-align: top; float:right; top:57px;" src="../img/hotdeals.png" width="50" />
{else}
{if isset($product.price) && isset($product.vms_sales_price_ex) && ($product.price < $product.vms_sales_price_ex)}<img style="vertical-align: top; float:right; top:5px;" src="../img/hotdeals.png" width="50" />{/if}{/if}

		<h3><a href="{$product.link|escape:'htmlall':'UTF-8'}" title="{$product.name|escape:'htmlall':'UTF-8'}">{$product.name|truncate:35:'...'|escape:'htmlall':'UTF-8'}</a></h3>

			<a href="{$product.link|escape:'htmlall':'UTF-8'}" class="product_img_link" title="{$product.name|escape:'htmlall':'UTF-8'}"><img src="{$link->getImageLink($product.link_rewrite, $product.id_image, 'home')}" alt="{$product.legend|escape:'htmlall':'UTF-8'}" {if isset($homeSize)} width="{$homeSize.width}" height="{$homeSize.height}"{/if} />{if isset($product.new) && $product.new == 1}<span class="new">{l s='New'}</span>{/if}{if $product.media}<span class="media icon_{$product.media}">{l s='Media'}</span>{/if}</a>
						
			<!--p class="product_desc"><a href="{$product.link|escape:'htmlall':'UTF-8'}" title="{$product.description_short|strip_tags:'UTF-8'|truncate:360:'...'}">{$product.description_short|truncate:360:'...'|strip_tags:'UTF-8'}</a></p-->
            {*if isset($product.on_sale) && $product.on_sale && isset($product.show_price) && $product.show_price && !$PS_CATALOG_MODE}<span class="on_sale">{l s='On sale!'}</span>
            {elseif isset($product.reduction) && $product.reduction && isset($product.show_price) && $product.show_price && !$PS_CATALOG_MODE}<span class="discount">{l s='Reduced price!'}</span>{/if}
            {if isset($product.online_only) && $product.online_only}<span class="online_only">{l s='Online only!'}</span>{/if*}
            {if (!$PS_CATALOG_MODE AND ((isset($product.show_price) && $product.show_price) || (isset($product.available_for_order) && $product.available_for_order)))}
            <div>
			    {if isset($product.show_price) && $product.show_price && !isset($restricted_country_mode)}<p class="price_container"><span class="price">{if !$priceDisplay}{convertPrice price=$product.price}{else}{convertPrice price=$product.price_tax_exc}{/if}</span></p>{/if}
                {if $product.vms_expected_arrival_date && $product.quantity <= 0 && ($product.vms_supplier_stock neq 'Y' && $product.vms_supplier_stock neq 'HIGH' && $product.vms_supplier_stock neq 'High' && $product.vms_supplier_stock lt 1)}
                    <a id="fancy_{$product.id_product}" href="{$product.link|escape:'htmlall':'UTF-8'}" class="availability_icon st_verylow" title="{l s='Shipping time'} - {l s='Expected'}: {$product.vms_expected_arrival_date|escape:'htmlall':'UTF-8'}">{l s='Expected'}: {$product.vms_expected_arrival_date|escape:'htmlall':'UTF-8'}</a>
                
				{elseif $product.quantity <= 0 && ($product.vms_supplier_stock eq 'Y' || $product.vms_supplier_stock eq 'HIGH' || $product.vms_supplier_stock eq 'J' || $product.vms_supplier_stock eq 'High' || $product.vms_supplier_stock eq 'Medium' || $product.vms_supplier_stock eq 'Low' || $product.vms_supplier_stock gt 0)}
                    <a id="fancy_{$product.id_product}" href="{$product.link|escape:'htmlall':'UTF-8'}" class="availability_icon st_medium" title="{l s='Shipping time'} - {l s='5 - 10 working days'}">{l s='5 - 10 working days'}</a>
                
				{elseif !$product.vms_expected_arrival_date && $product.quantity <= 0 && !$product.vms_supplier_stock}
                    <a id="fancy_{$product.id_product}" href="{$product.link|escape:'htmlall':'UTF-8'}" class="availability_icon st_low" title="{l s='Shipping time'} - {l s='Available soon'}">{l s='Available soon'}</a>
                
				{elseif !$product.vms_expected_arrival_date && $product.quantity <= 0 && ($product.vms_supplier_stock neq 'Y' && $product.vms_supplier_stock neq 'HIGH' && $product.vms_supplier_stock eq 'N' && $product.vms_supplier_stock neq 'High' && $product.vms_supplier_stock lt 1)}
                    <a id="fancy_{$product.id_product}" href="{$product.link|escape:'htmlall':'UTF-8'}" class="availability_icon st_outofstock" title="{l s='Shipping time'} - {l s='On request'}">{l s='On request'}</a>
 
{elseif $vms_stock->id_store == 6 && $vms_stock->quantity > 0}
<a id="fancy_{$product.id_product}" href="{$product.link|escape:'htmlall':'UTF-8'}" class="availability_icon st_veryhigh" title="{l s='Shipping time'} - {l s='Ordered before 4pm, shipped the same day'}">{l s='Shipped Today'}</a>TEST 1
 
{else}
				
<a id="fancy_{$product.id_product}" href="{$product.link|escape:'htmlall':'UTF-8'}" class="availability_icon st_high" title="{l s='Shipping time'} - {l s='2 - 5 working days'}">{l s='2 - 5 working days'}</a>

            {/if}			
				
				
            </div>
			
            {/if}
            <a class="button" href="{$product.link|escape:'htmlall':'UTF-8'}" title="{l s='Info & Order'}">{l s='Info & Order'}</a>
            {*if ($product.id_product_attribute == 0 || (isset($add_prod_display) && ($add_prod_display == 1))) && $product.available_for_order && !isset($restricted_country_mode) && $product.minimal_quantity <= 1 && $product.customizable != 2 && !$PS_CATALOG_MODE}
                {if ($product.allow_oosp || $product.quantity > 0)}
                    <a class="button ajax_add_to_cart_button exclusive" rel="ajax_id_product_{$product.id_product|intval}" href="{$link->getPageLink('cart.php')}?add&id_product={$product.id_product|intval}{if isset($static_token)}&token={$static_token}{/if}" title="{l s='Add to cart'}">{l s='Add to cart'}</a>
                {else}
                        <span class="exclusive">{l s='Add to cart'}</span>
                {/if}
            {/if*}
            {if isset($comparator_max_item) && $comparator_max_item}
                <p class="compare"><input type="checkbox" class="comparator" id="comparator_item_{$product.id_product}" value="comparator_item_{$product.id_product}" {if isset($compareProducts) && in_array($product.id_product, $compareProducts)}checked{/if}/> <label for="comparator_item_{$product.id_product}">{l s='Select to compare'}</label></p>
            {/if}
            {if $smarty.foreach.products.iteration % 4 == 0}<li class="line"><!-- --></li>{/if}
		</li>
	{/foreach}
	</ul>
	<script type="text/javascript">
    {literal}$(function() {
        $("{/literal}{foreach from=$products item=product name=products}#fancy_{$product.id_product}, {/foreach}{literal}#fancy").tooltip({
            track: true,
            delay: 0,
            showURL: false,
            fixPNG: true,
            showBody: " - ",
            extraClass: "fancy",
            top: 20,
            fade: 250,
            left: 20
        });
    });{/literal}
    </script>
	<!-- /Products list -->
{/if}

I have searched inside the categorycontroller but couldn't find it :-(

bellini13 · May 27, 2014

I am asking you where you have made changes to include your query for vms data?

For example, in your product_list.tpl you are referencing variables like $product.vms_sales_price_ex and $product.vms_expected_arrival_date

Where have you defined these attributes?

Regarding CategoryController, it fetches the Products that will appear using the following code.

$this->cat_products = $this->category->getProducts($this->context->language->id, (int)$this->p, (int)$this->n, $this->orderBy, $this->orderWay);

So you either need to alter the Category::getProducts function (as Robin from Cart Expert already suggested), or you need to merge your data into the cat_products array that the CategoryController already has.

madmega · May 28, 2014

They are defined in a few places.

productcontroller.php

if ($this->product->vms_expected_arrival_date)
$this->product->vms_expected_arrival_date = date('d-m-Y', $this->product->vms_expected_arrival_date);

in searchcontroller.php

            {
               if ($product['vms_expected_arrival_date'] != '0')
                   $search[$key]['vms_expected_arrival_date'] = date('d-m-Y', $product['vms_expected_arrival_date']);
           }

In manufacturercontroller.php, newproductscontroller.php, pricesdropcontroller.php, webserviceoutputbuilder.php

madmega · May 28, 2014

But the entries i want are from another table, i think in the categorycontroller.php it only fetches data from the PS_category in the databse

bellini13 · May 28, 2014

CategoryController retrieves product data for all products in the category being viewed, then displays that product data using product_list.tpl

So again and for the last time, if you want your data to appear on the product listing, then you need to add your custom logic as I have already explained above.

madmega · May 28, 2014

I am sorry, i am trying to understand how to program it

madmega · May 28, 2014

How are the classes being used, what is the class file that goes with the product_List.tpl file ?

madmega · June 3, 2014

I have this entry in product-list.tpl

{if $product.media}<span class="media icon_{$product.media}">{l s='Media'}</span>{/if}

It checks wether a product has a media entry in the database.

I checked classes/category.php and found this:

SELECT p.*, md.`type` AS media, pa.`id_product_attribute`, pl.`description`, pl.`description_short`, pl.`available_now`, pl.`available_later`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, i.`id_image`, il.`legend`, m.`name` AS manufacturer_name, tl.`name` AS tax_name, t.`rate`, cl.`name` AS category_default, DATEDIFF(p.`date_add`, DATE_SUB(NOW(), INTERVAL '.(Validate::isUnsignedInt(Configuration::get('PS_NB_DAYS_NEW_PRODUCT')) ? Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20).' DAY)) > 0 AS new,
			(p.`price` * IF(t.`rate`,((100 + (t.`rate`))/100),1)) AS orderprice
		FROM `'._DB_PREFIX_.'category_product` cp
		LEFT JOIN `'._DB_PREFIX_.'product` p ON p.`id_product` = cp.`id_product`
		LEFT JOIN `'._DB_PREFIX_.'product_attribute` pa ON (p.`id_product` = pa.`id_product` AND default_on = 1)
		LEFT JOIN `'._DB_PREFIX_.'category_lang` cl ON (p.`id_category_default` = cl.`id_category` AND cl.`id_lang` = '.(int)($id_lang).')
		LEFT JOIN `'._DB_PREFIX_.'product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = '.(int)($id_lang).')
		LEFT JOIN `'._DB_PREFIX_.'image` i ON (i.`id_product` = p.`id_product` AND i.`cover` = 1)
		LEFT JOIN `'._DB_PREFIX_.'image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = '.(int)($id_lang).')
		LEFT JOIN `'._DB_PREFIX_.'tax_rule` tr ON (p.`id_tax_rules_group` = tr.`id_tax_rules_group`
		                                           AND tr.`id_country` = '.(int)Country::getDefaultCountryId().'
	                                           	   AND tr.`id_state` = 0)
	    LEFT JOIN `'._DB_PREFIX_.'tax` t ON (t.`id_tax` = tr.`id_tax`)
		LEFT JOIN `'._DB_PREFIX_.'tax_lang` tl ON (t.`id_tax` = tl.`id_tax` AND tl.`id_lang` = '.(int)($id_lang).')
		LEFT JOIN `'._DB_PREFIX_.'manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer`
		LEFT JOIN `'._DB_PREFIX_.'vms_media` md ON md.`vms_guid` = p.`vms_guid`

I tried to add

vs.`id_store` AS store, vs2.`quantity` AS quantity2,

and added

		LEFT JOIN `'._DB_PREFIX_.'vms_stock` vs ON vs.`id_product` = p.`id_product`
		LEFT JOIN `'._DB_PREFIX_.'vms_stock` vs2 ON vs2.`quantity` = p.`quantity`

than call this from product-list.tpl

{if $product.store == 6 && $product.quantity2 > 0}

but it doesn't seem to work...am i missing something, because i tried to look inside the categorycontroller but i do not understand how to get this working.

in the database PS_VMS_MEDIA,

add database entry in product_list.tpl

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in