Setting a cron for promotions

[neymarinho]

Hello everyone,

 

Here is my problem: I would like to set up a daily promotion that starts at 2 in the afternoon every day and finishes at 11. So I thought of setting up a cron that would create a promotion everyday at 2 and delete it at 11. Could somebody please help me with this? (the promotion would be of a discount of 5% on my prices on a specific manufacturer).

Thank you in advance,

neymarinho

[doekia]

Hi,

I will be settings a specific price with Prestashop regarding your promotion, then my cron will simply change de start/end date each time it runs.

You did not mentionned your version, so assuming >= 1.4

the table is specific_price

UPDATE `'._DB_PREFIX_.'specific_price` SET `from`=NOW(), `to`=DATE_ADD(NOW(), INTERVAL 9 HOUR) WHERE `id_product` = '.(int)$id_product

[neymarinho]

Perfect ! thank you very much and how can I change the price from x$ to y$ with this formula?

And which id_product should i put when i want it to affect all the products of a specific manufacturer?

[doekia]

Perfect ! thank you very much and how can I change the price from x$ to y$ with this formula?

Don't. Prepare your price reduction with Prestashop as usual. The cron will just activate the period for this promotion.

 

And which id_product should i put when i want it to affect all the products of a specific manufacturer?

 

UPDATE `'._DB_PREFIX_.'specific_price` sp SET sp.`from`=NOW(), sp.`to`=DATE_ADD(NOW(), INTERVAL 9 HOUR)
INNER JOIN  `'._DB_PREFIX_.'product` p ON (sp.id_product = p.id_product)
WHERE p.`id_manufacturer` = '.(int)$id_manufacturer

[neymarinho]

Great ! and so what do i replace to put my manufacturer?

Otherwise, I should run my promotions  so at 9 if i want it to go until 6 right? and on prestashop i set it up for how long? like it has to be an old promotion or can it have an indefinite date of running ?

THANK YOU VERY MUCH

[doekia]

you kick off your cron every day at 2 as you mentienned earlier, with promotion life span of 9 this mean the promotion will end at 11.

 

You run your cron every day

[neymarinho]

I tried the following: 

 

<?php

$hostname="localhost";

$username="username";

$password="password";

$dbname="dbname";

//Connect to the database

$connection = mysql_connect($hostname, $username, $password);

mysql_select_db($dbname, $connection);

 

//Setup our query

$query = "UPDATE `'._DB_PREFIX_.'specific_price` sp SET sp.`from`=NOW(), sp.`to`=DATE_ADD(NOW(), INTERVAL 19 HOUR)

INNER JOIN  `'._DB_PREFIX_.'product` p ON (sp.id_product = p.id_product)

WHERE p.`id_manufacturer` = '.(int)$id_manufacturer";

//Run the Query

$result = mysql_query($query);

?>

 

Not working at all. How can I solve this?

Thank you in advance.

[doekia]

Sure you don't have Prestashop minimal config loaded...

 

Better include the config file, (set STDIN first if CLI mode)

Then use Prestashop's object when you can rather than brute access the db thru mysql functions.

 

The prestashop abstraction provides many security / constraints mechanismes preferable to this...

[neymarinho]

What do you mean the minimal configuration loaded? (I replaced the values of username, password...)

How could i use prestashop objects with a simple function?

I would like something simple and easily customizable; how can i dothis?

[doekia]

include the Prestashop config file (config/config.inc.php)

 

If your code meant to be run in CLI mode define STDIN up front.

 

Once done, no need to change / populate yet another file with database credentials.

Simply use Db::getInstance()->execute('UPDATE...') or ->executeS('SELECT...')

PS does the login phase for you including necessary setting of the connection such as utf8 ....

 

You can also use regular _DB_USER_, _DB_PASSWD_, _DB_NAME_, _DB_SERVER_, but it sound overkilling to me.

 

 

 

PS: In your code, ask yourself where _DB_PREFIX_ get declared... and even better $id_manufacturer... sounds you are cutting too much corner here. Don't expect something to work that way

[neymarinho]

Okay, thank's I'll do it that way then. 

For the following code, as i don't master mysql, could you help me out with it please?

How should I set it up?

I don't understand what to do with dbprefix and id_manufacturer

UPDATE `'._DB_PREFIX_.'specific_price` sp SET sp.`from`=NOW(), sp.`to`=DATE_ADD(NOW(), INTERVAL 19 HOUR)

INNER JOIN  `'._DB_PREFIX_.'product` p ON (sp.id_product = p.id_product)

WHERE p.`id_manufacturer` = '.(int)$id_manufacturer';

[doekia]

You SQL is fine, how do you run the task? CLI mode? WEB based?

 

The question is how to pass the required argument $id_manufacturer.

 

If web based ... $id_manufacturer = Tools::getValue('id_manufacturer'); and you run the task by http://tld/url/task.php&id_manufacturer=<your_value_here>

 

If cli mode $id_manufacturer = $_ARGV[1] and you run the task by /usr/bin/php -q /path_of_task/task.php <your _value_here>

[neymarinho]

I run it CLI mode.

Okay so if the manufacturer is "Manufacturer" I have the code:

 

$id_manufacturer = $_ARGV[1]

$query = "UPDATE `'._DB_PREFIX_.'specific_price` sp SET sp.`from`=NOW(), sp.`to`=DATE_ADD(NOW(), INTERVAL 19 HOUR)

INNER JOIN  `'._DB_PREFIX_.'product` p ON (sp.id_product = p.id_product)

WHERE p.`id_manufacturer` = '.(int)$id_manufacturer";

//Run the Query

$result = mysql_query($query);

?>

 

And I set up the cron as: "/usr/bin/php -q /path_of_task/task.php <Manufacturer>"

 

Right?

Edited December 24, 2013 by neymarinho (see edit history)

[doekia]

Man!!

#!/usr/bin/php -q
<?php

if (!defined('STDIN')) {
 // Prevent this to be runned in no CLI mode
 slleep(3600); // Make it painfull
 die();
}

if (!isset($argv) || !isset($argv[1] || (int)$argv[1] == 0) {
 die('Your manufacturer id is missing or invalid!');
}

$id_manufacturer = (int)$argv[1];

require_once('/path_of_prestashop_root/config/config.inc.php');

$retval = Db::getInstance()->execute('
  UPDATE `'._DB_PREFIX_.'specific_price` sp SET sp.`from`=NOW(), sp.`to`=DATE_ADD(NOW(), INTERVAL 9 HOUR)
  INNER JOIN  `'._DB_PREFIX_.'product` p ON (sp.id_product = p.id_product)
  WHERE p.`id_manufacturer` = '.(int)$id_manufacturer
);

echo 'Done with return: '.print_r($retval,1).PHP_EOL;

[neymarinho]

Thank you a lot for your help ! 

I ran the cron and tried to solve the problem but I don't get it.

Here is what i get when I run the following cron with the code you gave me (i changed the path to the root obviously).

 

Fatal error: Uncaught You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INNER JOIN  `ps_product` p ON (sp.id_product = p.id_product)
  WHERE p.`id_manuf' at line 2<br /><br /><pre>
  UPDATE `ps_specific_price` sp SET sp.`from`=NOW(), sp.`to`=DATE_ADD(NOW(), INTERVAL 19 HOUR)
  INNER JOIN  `ps_product` p ON (sp.id_product = p.id_product)
  WHERE p.`id_manufacturer` = 5</pre>
  thrown in /home/foodforb/public_html/classes/db/Db.php on line 613

 

What should I do?

[doekia]

My bad the sql should be:

 

UPDATE `ps_specific_price` sp, `ps_product` p SET sp.`from`=NOW(), sp.`to`=DATE_ADD(NOW(), INTERVAL 19 HOUR) WHERE p.id_product = sp.id_product AND p.`id_manufacturer` = 5

$retval = Db::getInstance()->execute('
  UPDATE `'._DB_PREFIX_.'specific_price` sp,  `'._DB_PREFIX_.'product` p
   SET sp.`from`=NOW(), sp.`to`=DATE_ADD(NOW(), INTERVAL 19 HOUR)
  WHERE p.`id_product` = sp.`id_product`
  AND p.`id_manufacturer` = '.(int)$id_manufacturer
);

[neymarinho]

^{It gives me "}Done with return: 1" now but the promotion was not applied. My promotion is the following: 

1 Manufacturer Promotion My shop -- -- -- 1 Percentage 5.00 12/23/2013 12/23/2013
 

As you can see, it did not change and the was not reactivated by the cron. What should i do?

[doekia]

I don't how did you check your database because if you display the date + time it does this

id_specific_price	id_specific_price_rule	id_cart	id_product	id_shop	id_shop_group	id_currency	id_country	id_group	id_customer	id_product_attribute	price	from_quantity	reduction	reduction_type	from	to
1	0	0	1	0	0	0	0	0	0	0	124.164733	1	0.050000	percentage	2013-12-24 14:08:50	2013-12-25 09:08:50
2	0	0	1	0	0	0	0	0	0	0	-0.996667	1	0.150000	percentage	2013-12-24 14:08:50	2013-12-25 09:08:50

This is now() and now()+19hours

[neymarinho]

Not affecting the promotions...

When I change interval 19 Hour to "now()+19hours" it makes this:

Fatal error: Uncaught You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOW()+19HOUR) WHERE p.`id_product` = sp.`id_product` AND p.`id_manufacturer`' at line 2<br /><br /><pre> UPDATE `ps_specific_price` sp, `ps_product` p SET sp.`from`=NOW(), sp.`to`=DATE_ADD(NOW(), NOW()+19HOUR) WHERE p.`id_product` = sp.`id_product` AND p.`id_manufacturer` = 0</pre> thrown in /home/foodforb/public_html/classes/db/Db.php on line 613

[doekia]

Could you stop misunderstanding every thing

 

I dot not meant you should change the query. The query is just fine and does what it should do.

 

The way you check your record in the database is wrong. Displays the time along with the date.

 

On top I got the feeling you to not attack the proper database so you certainly did not load the proper config file.

 

You display database record of the 23rd, we are the 24th!!

 

put a print_t(array(_DB_NAME_,_DB_USER_,_DB_PASSWD_)); the line after the require_once().

 

The displays will tels you which database your cron applies the query/update to

[neymarinho]

No I was talking about the catalog price rules tab in prestashop. 

It has applied the promotion on the store but it doesn't display on the admin panel. Is there a way to control this?

Thank you a lot. 

[doekia]

Which version of PS do you use?

On 1.5.6 the period change is reflected in the BO (price)

 

-- All combinations All shops All currencies All countries All groups All customers 124,16 € - 5 % From 2013-12-24 14:08:50

To 2013-12-25 09:08:50 1 Delete

-- All combinations All shops All currencies All countries All groups All customers -1,00 € - 15 % From 2013-12-24 14:08:50

To 2013-12-25 09:08:50 1

 

On 1.4.11 the period change is reflected in the BO (specifi price)

Toutes les devises Tous les pays Tous les groupes 0,00 € 5 % Du 2013-12-24 17:28:01

Au 2013-12-25 12:28:01 1 158,86 €

 

PS: Sorry you talk about catalog price rule, but what we have done is specific price on the product... Catalog > Product > Price tab

[doekia]

To change a catalog specific rule the cron needs to be changed.

The most sensible way is to use the catalog_rule_id as selector

 

#!/usr/bin/php -q
<?php
 
if (!defined('STDIN')) {
// Prevent this to be runned in no CLI mode
slleep(3600); // Make it painfull
die();
}
 
if (!isset($argv) || !isset($argv[1] || (int)$argv[1] == 0) {
die('Your specific_rule id is missing or invalid!');
}
 
$id_specific_price_rule = (int)$argv[1];
 
require_once('/path_of_prestashop_root/config/config.inc.php');
 
$retval = Db::getInstance()->execute('
UPDATE `'._DB_PREFIX_.'specific_price_rule` spr SET spr.`from`=NOW(), spr.`to`=DATE_ADD(NOW(), INTERVAL 9 HOUR)
WHERE spr.`id_specific_price_rule` = '.(int)$specific_price_rule
);
 
echo 'Done with return: '.print_r($retval,1).PHP_EOL;

Once runned, to see the time you need to enter the rule ... only date is displayed in the list