Jump to content

Setting a cron for promotions


neymarinho

Recommended Posts

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

Link to comment
Share on other sites

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
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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

Link to comment
Share on other sites

  • 2 weeks later...

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.

Link to comment
Share on other sites

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...

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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';
Link to comment
Share on other sites

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>

Link to comment
Share on other sites

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 by neymarinho (see edit history)
Link to comment
Share on other sites

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;



Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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
);
Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...