DROP TABLE IF EXISTS `shipmode`;
CREATE TABLE `shipmode` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(256) NOT NULL,
  `minweight` double DEFAULT NULL,
  `hd` enum('0','1') NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  KEY `hd` (`hd`),
  KEY `minweight` (`minweight`)
) ENGINE=MyISAM AUTO_INCREMENT=33 DEFAULT CHARSET=latin1;

INSERT INTO `shipmode` VALUES (1,'[e]Pickup in our store[/e][d]Abholung in unserem Geschäft[/d]',NULL,'0'),(2,'[e]Pickup in our store[/e][d]Abholung in unserem Geschäft[/d]',NULL,'1'),(3,'[e]Camion[/e][d]Spediteur[/d]',0.0001,'1'),(4,'[e]Transport bus rental[/e][d]Miete Transportbus[/d]',0.0001,'1'),(5,'[e]Personal delivery 1 person incl car and 15 min work[/e][d]Persönliche Lieferung 1 Person inkl Weg und 15min Arbeit[/d]',0.0001,'1'),(6,'[e]Personal delivery 2 persons incl car and 15 min work[/e][d]Persönliche Lieferung 2 Personen inkl Weg und 15min Arbeit[/d]',0.0001,'1'),(7,'[e]Installation at home 1 person incl car and 60 min work[/e][d]Heiminstallation 1 Person inkl Weg und 1 Stunde Arbeit[/d]',0.0001,'1'),(8,'[e]Installation at home 2 person incl car and 60 min work[/e][d]Heiminstallation 2 Person inkl Weg und 1 Stunde Arbeit[/d]',0.0001,'1'),(9,'[e]direct delivery[/e][d]Direktlieferung[/d]',NULL,'1');

DROP TABLE IF EXISTS `shipprice`;
CREATE TABLE `shipprice` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `shipzonecountry_code` varchar(32) NOT NULL,
  `shipmode_id` int(11) NOT NULL,
  `maxweight` double NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `shipzonecountry_code` (`shipzonecountry_code`,`shipmode_id`,`maxweight`),
  KEY `shipzonecountry_code_2` (`shipzonecountry_code`),
  KEY `shipmode_id` (`shipmode_id`),
  KEY `maxweight` (`maxweight`)
) ENGINE=MyISAM AUTO_INCREMENT=559 DEFAULT CHARSET=latin1;

INSERT INTO `shipprice` VALUES (1,'domestic',26,0.25),(2,'domestic',27,0.25),(3,'domestic',19,2),(4,'domestic',19,5),(5,'domestic',19,10),(6,'domestic',19,20),(7,'domestic',19,30),(8,'domestic',20,2),(9,'domestic',20,5),(10,'domestic',20,10),(11,'domestic',20,20),(12,'domestic',20,30),(13,'domestic',23,2),(14,'domestic',23,5),(15,'domestic',23,10),(16,'domestic',23,20),(17,'domestic',23,30),(18,'domestic',24,2),(19,'domestic',24,5),(20,'domestic',24,10),(21,'domestic',24,20),(22,'domestic',24,30),(23,'domestic',17,2),(24,'domestic',17,5),(25,'domestic',17,10),(26,'domestic',17,20),(27,'domestic',17,30),(28,'domestic',18,2),(29,'domestic',18,5),(30,'domestic',18,10),(31,'domestic',18,20),(32,'domestic',18,30),(33,'domestic',28,2),(34,'domestic',28,5),(35,'domestic',28,10),(36,'domestic',28,20),(37,'domestic',28,30),(38,'domestic',29,30),(39,'foreign',25,200),(40,'domestic',3,100),(41,'foreign',10,30),(42,'foreign',22,0),(43,'foreign',11,30),(44,'foreign',12,30),(45,'all',1,10000),(46,'all',2,10000),(47,'domestic',9,10000),(48,'domestic',4,500),(49,'domestic',5,500),(50,'domestic',6,500),(51,'domestic',7,500),(52,'domestic',8,500),(53,'domestic',21,3.90000009536743),(54,'domestic',21,4.90000009536743),(55,'domestic',21,9.89999961853027),(56,'domestic',21,19.8999996185303),(57,'domestic',21,30),(58,'plfcz1',16,0.5),(59,'plfcz2',16,0.5),(132,'ppfcz2',15,15),(127,'ppfcz2',15,10),(122,'ppfcz2',15,5),(119,'ppfcz2',15,2),(90,'ppfcz1',15,2),(93,'ppfcz1',15,5),(98,'ppfcz1',15,10),(103,'ppfcz1',15,15),(108,'ppfcz1',15,20),(113,'ppfcz1',15,25),(118,'ppfcz1',15,30),(137,'ppfcz2',15,20),(142,'ppfcz2',15,25),(147,'ppfcz2',15,30),(148,'ppfcz3',15,2),(151,'ppfcz3',15,5),(156,'ppfcz3',15,10),(161,'ppfcz3',15,15),(166,'ppfcz3',15,20),(171,'ppfcz3',15,25),(176,'ppfcz3',15,30),(177,'ppfcz4',15,2),(180,'ppfcz4',15,5),(185,'ppfcz4',15,10),(190,'ppfcz4',15,15),(195,'ppfcz4',15,20),(200,'ppfcz4',15,25),(205,'ppfcz4',15,30),(206,'ppfcz5',15,2),(209,'ppfcz5',15,5),(214,'ppfcz5',15,10),(219,'ppfcz5',15,15),(224,'ppfcz5',15,20),(229,'ppfcz5',15,25),(234,'ppfcz5',15,30),(235,'ppfcz1',14,2),(238,'ppfcz1',14,5),(243,'ppfcz1',14,10),(248,'ppfcz1',14,15),(253,'ppfcz1',14,20),(258,'ppfcz1',14,25),(263,'ppfcz1',14,30),(264,'ppfcz2',14,2),(267,'ppfcz2',14,5),(272,'ppfcz2',14,10),(277,'ppfcz2',14,15),(282,'ppfcz2',14,20),(287,'ppfcz2',14,25),(292,'ppfcz2',14,30),(293,'ppfcz3',14,2),(296,'ppfcz3',14,5),(301,'ppfcz3',14,10),(306,'ppfcz3',14,15),(311,'ppfcz3',14,20),(316,'ppfcz3',14,25),(321,'ppfcz3',14,30),(322,'ppfcz4',14,2),(325,'ppfcz4',14,5),(330,'ppfcz4',14,10),(335,'ppfcz4',14,15),(340,'ppfcz4',14,20),(345,'ppfcz4',14,25),(350,'ppfcz4',14,30),(351,'ppfcz5',14,2),(354,'ppfcz5',14,5),(359,'ppfcz5',14,10),(364,'ppfcz5',14,15),(369,'ppfcz5',14,20),(374,'ppfcz5',14,25),(379,'ppfcz5',14,30),(380,'ppfcz1',30,0.5),(557,'domestic',3,200),(383,'ppfcz1',30,2),(385,'ppfcz1',30,3),(387,'ppfcz1',30,4),(389,'ppfcz1',30,5),(394,'ppfcz1',30,10),(399,'ppfcz1',30,15),(404,'ppfcz1',30,20),(409,'ppfcz1',30,25),(414,'ppfcz1',30,30),(415,'ppfcz2',30,0.5),(416,'ppfcz2',30,1),(418,'ppfcz2',30,2),(420,'ppfcz2',30,3),(422,'ppfcz2',30,4),(424,'ppfcz2',30,5),(429,'ppfcz2',30,10),(434,'ppfcz2',30,15),(439,'ppfcz2',30,20),(444,'ppfcz2',30,25),(449,'ppfcz2',30,30),(450,'ppfcz3',30,0.5),(451,'ppfcz3',30,1),(453,'ppfcz3',30,2),(455,'ppfcz3',30,3),(457,'ppfcz3',30,4),(459,'ppfcz3',30,5),(464,'ppfcz3',30,10),(469,'ppfcz3',30,15),(474,'ppfcz3',30,20),(479,'ppfcz3',30,25),(484,'ppfcz3',30,30),(485,'ppfcz4',30,0.5),(486,'ppfcz4',30,1),(488,'ppfcz4',30,2),(490,'ppfcz4',30,3),(492,'ppfcz4',30,4),(494,'ppfcz4',30,5),(499,'ppfcz4',30,10),(504,'ppfcz4',30,15),(509,'ppfcz4',30,20),(514,'ppfcz4',30,25),(519,'ppfcz4',30,30),(520,'ppfcz5',30,0.5),(521,'ppfcz5',30,1),(523,'ppfcz5',30,2),(525,'ppfcz5',30,3),(527,'ppfcz5',30,4),(529,'ppfcz5',30,5),(534,'ppfcz5',30,10),(539,'ppfcz5',30,15),(544,'ppfcz5',30,20),(549,'ppfcz5',30,25),(554,'ppfcz5',30,30),(555,'foreign',13,30),(556,'all',32,10000);

DROP TABLE IF EXISTS `shipzone`;
CREATE TABLE `shipzone` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `shipmode_id` int(11) NOT NULL,
  `shipzonecountry_code` varchar(32) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `shipmode_id` (`shipmode_id`,`shipzonecountry_code`),
  KEY `shipzonecountry_code` (`shipzonecountry_code`),
  KEY `shipmode_id_2` (`shipmode_id`)
) ENGINE=MyISAM AUTO_INCREMENT=53 DEFAULT CHARSET=latin1;

INSERT INTO `shipzone` VALUES (1,26,'domestic'),(44,2,'all'),(3,3,'domestic'),(4,4,'domestic'),(5,5,'domestic'),(6,6,'domestic'),(7,7,'domestic'),(8,8,'domestic'),(51,20,'domestic'),(50,19,'domestic'),(31,17,'domestic'),(32,18,'domestic'),(35,21,'domestic'),(36,22,'all'),(37,23,'domestic'),(38,24,'domestic'),(40,27,'domestic'),(42,29,'domestic'),(43,1,'all'),(52,32,'all');

SELECT
    s.`id`,
    (SELECT CONCAT(SUBSTR(`id`,0,0),`maxweight`)+0 FROM `shipprice` AS sp WHERE 
      sp.`shipzonecountry_code`=sz.`shipzonecountry_code` AND 
      sp.`shipmode_id`=s.`id` 
      ORDER BY sp.`maxweight` DESC LIMIT 1) AS correct,
    (SELECT `maxweight` FROM `shipprice` AS sp WHERE 
      sp.`shipzonecountry_code`=sz.`shipzonecountry_code` AND 
      sp.`shipmode_id`=s.`id` 
      ORDER BY sp.`maxweight` DESC LIMIT 1) AS incorrect,
    REPLACE(REPLACE(CONCAT(SUBSTR(s.`name`,1,POSITION('[e]' IN s.`name`)-1),SUBSTRING_INDEX(s.`name`,'[/e]',-1)),'[d]',''),'[/d]','') AS sortname
  FROM
    (`shipmode` AS s,
    `shipzone` AS sz)
  WHERE
    sz.`shipmode_id`=s.`id`;
	
alter table shipprice drop index maxweight;

SELECT
    s.`id`,
    (SELECT CONCAT(SUBSTR(`id`,0,0),`maxweight`)+0 FROM `shipprice` AS sp WHERE 
      sp.`shipzonecountry_code`=sz.`shipzonecountry_code` AND 
      sp.`shipmode_id`=s.`id` 
      ORDER BY sp.`maxweight` DESC LIMIT 1) AS correct,
    (SELECT `maxweight` FROM `shipprice` AS sp WHERE 
      sp.`shipzonecountry_code`=sz.`shipzonecountry_code` AND 
      sp.`shipmode_id`=s.`id` 
      ORDER BY sp.`maxweight` DESC LIMIT 1) AS incorrect,
    REPLACE(REPLACE(CONCAT(SUBSTR(s.`name`,1,POSITION('[e]' IN s.`name`)-1),SUBSTRING_INDEX(s.`name`,'[/e]',-1)),'[d]',''),'[/d]','') AS sortname
  FROM
    (`shipmode` AS s,
    `shipzone` AS sz)
  WHERE
    sz.`shipmode_id`=s.`id`;