Geeks With Blogs

Biff Martin
Have you ever wanted to programmatically insert hundreds or more discount codes into your Magento 1.4 shopping cart and did not want to insert them one by one?  I will present the solution to your troubles here:

We will be programatically writing out hundreds of records into the salesrule and salesrule_template tables.

We will offer 3 different kinds of discount codes, one for 120 off, one for 200 off, and one for 500 off.  Each will contains a random suffix. There is one of each, we will give one per customer. Before you do anything, get shell console access to your magento server, so you can run some perl scripts;

1) First you will need a template for three separate discount codes. This is 3 lines of text. save it to a file called salesrule.template.txt
(_IDX_,'AMA12NNNNN','Such and Such Discount code 120 off','2011-12-17','2011-12-25','AMA12NNNNN',1,1,'0,1,2,3',1,'a:6:{s:4:\"type\";s:32:\"salesrule/rule_condition_combine\";s:9:\"attribute\";N;s:8:\"operator\";N;s:5:\"value\";s:1:\"1\";s:18:\"is_value_processed\";N;s:10:\"aggregator\";s:3:\"all\";}','a:6:{s:4:\"type\";s:40:\"salesrule/rule_condition_product_combine\";s:9:\"attribute\";N;s:8:\"operator\";N;s:5:\"value\";s:1:\"1\";s:18:\"is_value_processed\";N;s:10:\"aggregator\";s:3:\"all\";}',1,1,'',1,'cart_fixed','120.0000',NULL,0,0,0,0,0,'1'),
(_IDX_,'AMA20NNNNN','Such and Such Discount code 200 off','2011-12-17','2011-12-25','AMA20NNNNN',1,1,'0,1,2,3',1,'a:6:{s:4:\"type\";s:32:\"salesrule/rule_condition_combine\";s:9:\"attribute\";N;s:8:\"operator\";N;s:5:\"value\";s:1:\"1\";s:18:\"is_value_processed\";N;s:10:\"aggregator\";s:3:\"all\";}','a:6:{s:4:\"type\";s:40:\"salesrule/rule_condition_product_combine\";s:9:\"attribute\";N;s:8:\"operator\";N;s:5:\"value\";s:1:\"1\";s:18:\"is_value_processed\";N;s:10:\"aggregator\";s:3:\"all\";}',1,1,'',1,'cart_fixed','200.0000',NULL,0,0,0,0,0,'1'),
(_IDX_,'AMA50NNNNN','Such and Such Discount code 500 off','2011-12-17','2011-12-25','AMA50NNNNN',1,1,'0,1,2,3',1,'a:6:{s:4:\"type\";s:32:\"salesrule/rule_condition_combine\";s:9:\"attribute\";N;s:8:\"operator\";N;s:5:\"value\";s:1:\"1\";s:18:\"is_value_processed\";N;s:10:\"aggregator\";s:3:\"all\";}','a:6:{s:4:\"type\";s:40:\"salesrule/rule_condition_product_combine\";s:9:\"attribute\";N;s:8:\"operator\";N;s:5:\"value\";s:1:\"1\";s:18:\"is_value_processed\";N;s:10:\"aggregator\";s:3:\"all\";}',1,1,'',1,'cart_fixed','500.0000',NULL,0,0,0,0,0,'1'),

2) Next you will need a perl script to make your random discount codes and perform template subsitition on the above template.

my $template = "salesrule.template.txt";
my $header1 = "INSERT INTO `salesrule` VALUES";
my $header2 = "INSERT INTO `salesrule_label` VALUES";
my $delete1 = "delete from salesrule where rule_id >= 20;";
my $delete2 = "delete from salesrule_label where rule_id >= 20;";
my $outsql1 = "salesrule.import.sql";
my $outsql2 = "salesrule_label.import.sql";
my $labeltemplate = "(_LABELID_,_IDX_,0,'Such and Such \$_AMT_ Offer')";

open(FILE, $template) or die("unable to open file");

open(OUTFILE1, ">".$outsql1) or die("unable to open file");
printf(OUTFILE1 "%s\n", $delete1);
printf(OUTFILE1 "%s\n", $header1);

open(OUTFILE2, ">".$outsql2) or die("unable to open file");
printf(OUTFILE2 "%s\n", $delete2);
printf(OUTFILE2 "%s\n", $header2);

printf("ID  CODE      AMT\n");

# read file into an array
@data = <FILE>;

# close file

# print file contents
my $icount=0;
foreach $line (@data)

## see the random number generator now.

# these are the random characters
my @hchars=split(",", "R,S,T,U,V,W,X,Y,Z,A,B,C,D,E,F,G,H,I,J,K,M");

my $icount=1;
for ($i=0; $i<900; $i++) {
  $suffix = $hchars[$irb].$hchars[$irc].$ira;

  if (($i >= 0) && ($i < 300)) {
     $aline = $data[0];
  } elsif (($i >= 300) && ($i < 600)) {
     $aline = $data[1];
  } elsif (($i >= 600) && ($i < 900)) {
     $aline = $data[2];

  $aline =~ s/_IDX_/$icount/g;
  $aline =~ s/NNNNN/$suffix/g;
  $sqline = substr($aline, 1, -3);


  $disccode =~ s/'//g;
  $discamt =~ s/'//g;
  $discamt =~ s/\.0000//g;
  printf("%d %s %s\n", $icount, $disccode, $discamt);
  printf(OUTFILE1 "%s", $aline);

  $rulelabel = $labeltemplate;
  $labelid = $icount-2;
  $rulelabel =~ s/_LABELID_/$labelid/;
  $rulelabel =~ s/_IDX_/$icount/g;
  $rulelabel =~ s/_DCODE_/$disccode/g;
  $rulelabel =~ s/_AMT_/$discamt/g;
  printf(OUTFILE2 "%s", $rulelabel);
  if ($i < 899) { printf(OUTFILE2 ",\n"); }
   else { printf(OUTFILE2 ";\n"); }

## after this, just import these sql files into your mysql database
## source <name> to import them

3) Just logon to your mysql command line and import the two sql files
% mysql -u <username> -p<password> -D <magentodbname>
mysql> source "salesrule.import.sql";
mysql> source "salesrule_label.import.sql";

in that order.

Posted on Tuesday, December 20, 2011 6:10 PM | Back to top

Comments on this post: Programmatically Posting Discount Codes in the Magento SQL database with perl

No comments posted yet.
Your comment:
 (will show your gravatar)

Copyright © nematoad | Powered by: