[Sommaire] [Imprimer]

   1  # This script is intended to assist when upgrading a database from the old
   2  # country region-based tax scheme to the new geographical tax zones.
   3  
   4  #DROP TABLE zones_to_geo_zones;
   5  #DROP TABLE geo_zones;
   6  
   7  CREATE TABLE zones_to_geo_zones (
   8  association_id int(5) NOT NULL auto_increment,
   9  zone_country_id int(5) NOT NULL,
  10  zone_id int(5) NULL,
  11  geo_zone_id int(5) NULL,
  12  last_modified datetime NULL,
  13  date_added datetime NOT NULL,
  14  PRIMARY KEY (association_id)
  15  );
  16  
  17  CREATE TABLE geo_zones (
  18  geo_zone_id int(5) NOT NULL auto_increment,
  19  geo_zone_name varchar(32) NOT NULL,
  20  geo_zone_description varchar(255) NOT NULL,
  21  last_modified datetime NULL,
  22  date_added datetime NOT NULL,
  23  PRIMARY KEY (geo_zone_id)
  24  );
  25  ALTER TABLE tax_rates ADD COLUMN tax_priority int(5) NOT NULL DEFAULT 1 after tax_class_id;
  26  
  27  
  28  INSERT INTO geo_zones (geo_zone_id,geo_zone_name,geo_zone_description,last_modified,date_added) 
  29      SELECT tr.tax_zone_id,zone_name,zone_name,NULL,now() from tax_rates tr,zones z,countries c 
  30      WHERE tr.tax_zone_id=z.zone_id AND c.countries_id=z.zone_country_id 
  31      GROUP BY tr.tax_zone_id;
  32  
  33  INSERT INTO zones_to_geo_zones (zone_country_id,zone_id,geo_zone_id,date_added) 
  34      SELECT z.zone_country_id, z.zone_id,tr.tax_zone_id,now() FROM tax_rates tr, zones z WHERE z.zone_id=tr.tax_zone_id
  35      GROUP BY tr.tax_zone_id;