[ic] show only the items in stock in promo component

[Greg Hanson]

On 10/8/2014 2:25 PM, EA3KZ Salvador Caballé wrote:
> El 08/10/14 21:02, Greg Hanson ha escrit:
>> On 10/8/2014 11:49 AM, Salvador Caballe wrote:
>>> I just want to show the items in stock, in the promo component
>>> (standard catalog) but I don't know how modify the search to look
>>> inventory table and merchandising table.
>>>
>>>
>>> this is the promo component code:
>>>
>>>
>>> [query arrayref=main
>>> sql="
>>> SELECT sku,timed_promotion,start_date,finish_date
>>> FROM [either]__UI_MERCH_TABLE__[or]merchandising[/either]
>>> WHERE featured = '[control promo_type specials]'
>>
>> One way to do it:
>>
>> SELECT m.sku, m.timed_promotion, m.start_date, m.finish_date
>> FROM [either]__UI_MERCH_TABLE__[or]merchandising[/either] m, inventory i
>> WHERE m.featured = '[control promo_type specials]'
>> AND i.sku = m.sku
>> AND i.quantity >= 1
>>
>>
>>
>
> Greg,
>
> thanks for your fast answer
> it works, but shows the inactive items.

Yes, of course.. this query just looks at the merchandising table and 
the inventory table ... it will not take into account something from 
another table... the perl in the block filters out the date ranges... so ...

If your inactive field is in the products table, you would have to add 
another table to the query:

SELECT m.sku, m.timed_promotion, m.start_date, m.finish_date
FROM [either]__UI_MERCH_TABLE__[or]merchandising[/either] m, inventory 
i, products p
WHERE m.featured = '[control promo_type specials]'
AND i.sku = m.sku
AND m.sku = p.sku
AND i.quantity >= 1
AND p.inactive <> 1

If you are using the normal interchange products inactive column setup 
where it has a Yes/no widget... otherwise adjust as needed.

Greg
>
> Salvador
>
>
>
> _______________________________________________
> interchange-users mailing list
> interchange-users at icdevgroup.org
> http://www.icdevgroup.org/mailman/listinfo/interchange-users
>


-- 
Greg Hanson
Interchange Consulting
Perusion
1506 E Gilbert Ave
Coeur d'Alene, ID 83815

Email		greg at perusion.com
Phone		208-214-4306

Please Note NEW TOLL FREE NUMBER:
Toll Free	1-855-444-1949

Fax		775-256-2231
Web		http://www.perusion.com
PGP Key		http://www.perusion.com/Greg_Hanson.asc

The one who says it can't be done should never interrupt the one doing it..
Ask us about job opportunities.