I am working on a solution to complement the “Avail Until” mod released here:
[url]http://forum.cs-cart.com/showthread.php?t=16014[/url]
The Avail Until mod adds a avail_until field, so there is an avail_since field and an avail_until field to designate a date range when a product is available for purchase.
I am trying to write a mysql command that will switch the product status to H when the current date is not in that range, or to A when the current date is within that range. I am not a programmer so I am hoping for some pointers. 
I added another field called ‘avail_hide’ that can be checked Y or N. This is to designate products that should be hidden when they are not available.
I want to write a cron job that will run daily to toggle product status between H and A…
To activate a product…
UPDATE cscart_products set status = A WHERE avail_hide = Y AND avail_since =[COLOR=“Red”] today’s date??[/COLOR];
OR is would it be better to use syntax that would check for dates between the avail_since and avail_hide values?
UPDATE cscart_products set status = A WHERE avail_hide = Y AND [COLOR=“Red”]current time is between avail_since and avail_until[/COLOR];
To hide a product…
UPDATE cscart_products set status = A WHERE avail_hide = Y AND avail_until = [COLOR=“Red”]yesterday’s date or something in the past??[/COLOR];
same question about between…
Does that make sense? What would be the best approach? I see that in the cscart_products table the avail_since and avail_until fields have the 10-digit UNIX date. Hmmm…
[url]MySQL :: MySQL 8.0 Reference Manual :: 12.7 Date and Time Functions ;
yes somebody help him, or even cs-cart team help him please
This is the basic condition structure you’re looking for. Obviously, you’ll need to edit this to fit your query.```php
WHERE (from_unixtime( unix_timestamp( ) )
BETWEEN from_unixtime( cscart_products.avail_since )
AND from_unixtime( cscart_products.avail_until ))
```
Thank you, Glen!! I will see what I can come up with.
```php
-- Activate Disabled Current Products
UPDATE CSCARTDBNAME.cscart_products
SET status = ‘A’
WHERE status = ‘D’
AND avail_since > 0
AND avail_until > 0
AND (from_unixtime( unix_timestamp( ) )
BETWEEN from_unixtime( avail_since )
AND from_unixtime( avail_until ));
– Activate Disabled Future BIA Products
UPDATE CSCARTDBNAME.cscart_products
SET status = ‘A’
WHERE status = ‘D’
AND buy_in_advance = ‘Y’
AND avail_since > 0
AND avail_until > 0
AND (from_unixtime( unix_timestamp( ) )
<= from_unixtime( avail_since ));
– Disable Active Future non-BIA Products
UPDATE CSCARTDBNAME.cscart_products
SET status = ‘D’
WHERE status = ‘A’
AND buy_in_advance = ‘N’
AND avail_since > 0
AND avail_until > 0
AND (from_unixtime( unix_timestamp( ) )
<= from_unixtime( avail_since ));
– Disable Active Expired Products
UPDATE CSCARTDBNAME.cscart_products
SET status = ‘D’
WHERE status = ‘A’
AND avail_since > 0
AND avail_until > 0
AND (from_unixtime( unix_timestamp( ) )
from_unixtime( avail_until ));
```
The “> 0” lines are optional. I felt that products with incomplete scheduling should be ignored.
cheers,
Glen
Thanks again, Glen!
I experimented with some different things and finally came up with something that works. This is simply set up as a cron job that can be run at whatever time the products should become available or become hidden.
UPDATE `cscart_products` set `status` = 'A' WHERE `avail_hide` = 'Y' AND `avail_since` <= UNIX_TIMESTAMP() AND `avail_until` >= UNIX_TIMESTAMP();
UPDATE `cscart_products` set `status` = 'H' WHERE `avail_hide` = 'Y' AND (`avail_until` <= UNIX_TIMESTAMP() OR `avail_since` >= UNIX_TIMESTAMP());
…not fully tested, of course, but I think it does what I need it to do and that’s all that matters. 
I guess I will put all this together into a post for an availability mod that will complement the Avail Until mod.
EDITED: I think I need to add 86400 to something… gotta fix that first.
My updated command… I think this is correct.
UPDATE `cscart_products` set `status` = 'A' WHERE `avail_hide` = 'Y' AND (`avail_since` != 0 AND `avail_until` != 0) AND `avail_since` <= UNIX_TIMESTAMP() AND (`avail_until` + 86400 >= UNIX_TIMESTAMP());
UPDATE `cscart_products` set `status` = 'H' WHERE `avail_hide` = 'Y' AND (`avail_since` != 0 AND `avail_until` != 0) AND ((`avail_until` + 86400 <= UNIX_TIMESTAMP()) OR `avail_since` >= UNIX_TIMESTAMP());