[SOLVED] Deleting cart rule automatically when it expires?

[Beluga]

I'm planning to abuse cart rules for displaying certain elements for groups only at certain time periods and I'm wondering, how to set up automatic deletion of cart rules after they reach their "to" date.

 

I just printed the PS dev & theme docs and plan to study them over the weekend so I hope to be able to understand any advice better than a total newbie soon

[vekia]

i think that you can do it easily with simple php scripts and for example cron job.

in php script create mysql query which will check voucher exipry date - and if voucher will be expired - just remove it 

 

run cron job one per day, or as often as you like

[Beluga]

Thanks for the tip! I'm going to use OVH for hosting and they have Crontab even for shared hosting. I will leave this unsolved and come back when I solve it.

[vekia]

if you will have any question related to the php script and sql query - feel free to write

[Beluga]

I do appreciate pre-emptive advice

 

I wondered about how I could delete multiple expired rules at once and found that PDO or MySQLi are the preferred methods for putting things into lists that I can use to destroy the rows from all the cart rule related tables.

 

I found this cool bit of code that I modified to suit my date-sniffing needs:

$host = "";
$user = "";
$pass = "";
$dbnm = "";
$thismoment = "2013-10-20 21:00:00";

// Connect to PDO MySQL
$dsn =  "mysql:dbhost=$host;dbname=$dbnm";
$db  = new PDO($dsn, $user, $pass);

$stmt = $db->prepare("SELECT id_cart_rule FROM ps_cart_rule WHERE date_to < '$thismoment'");
$stmt->execute(); // execute the prepared query

// FETCH one COLUMN from ALL rows in result IN one operation into array
// then implode array into one LINE using Comma+space between
$line = implode( ',', $stmt->fetchAll (PDO::FETCH_COLUMN) );

// Test display the string
echo $line; //"1, 2, 3, 4, 5, 6, 7, 8, 9, 10"

exit('<hr />done!');

Then I guess I can delete stuff with this type of query:

DELETE FROM whatever_table WHERE id_cart_rule IN ($line)

Any thoughts are appreciated! I'll continue tomorrow afternoon or evening probably.

 

Btw., is it possible to use the datepicker in a template? I have read about its use in HelperForm, so I was wondering..

I would actually want to try to have group leaders (that would be members of a group called 'leader') who would be able to specify the start and end dates of periods when the group is allowed to order (otherwise "Add to cart" and the cart are hidden). This will be implemented to serve food clubs.

 

I also had a little question about overrides in my post about automatic group assigment.

edit: it was just a case of deleting cache/class_index.php to get the overrides to work!!

Edited September 22, 2013 by Beluga (see edit history)

[Beluga]

Ok now I have the purging script done, I changed this to use the SQL NOW() and added conditions so it doesn't select real cart rules with discounts:

$stmt = $db->prepare("SELECT id_cart_rule FROM ps_cart_rule WHERE date_to < NOW() AND free_shipping = 0 AND reduction_percent = 0 AND reduction_amount = 0");

and I added this to delete everything that is needed:

$ruledel = $db->prepare("DELETE FROM ps_cart_rule WHERE id_cart_rule IN ($line);DELETE FROM ps_cart_rule_group WHERE id_cart_rule IN ($line);DELETE FROM ps_cart_rule_lang WHERE id_cart_rule IN ($line)");
$ruledel->execute();

This can be marked as solved, I guess I'll create a new topic for allowing certain customers to create cart rules (either as a plea for help or as a how-to, if I manage to crack it myself). Edited September 22, 2013 by Beluga (see edit history)

[vekia]

thank you for your solutions above

it will be really helpful for other merchants with smiliar problem

it is also great idea to create free module for this!

 

im going to mark topic as solved exactly as you suggested

 

with regards,

Milos

[siomosp]

thank you for your solutions above

it will be really helpful for other merchants with smiliar problem

it is also great idea to create free module for this!

 

im going to mark topic as solved exactly as you suggested

 

with regards,

Milos

 

Hello! can you develop a module for this? ( mass delete expired vouchers)

Thanks   ,  Panagiotis