
SET SESSION sql_mode = '';

	/* Temporary table for remapping the StoID */
CREATE TEMPORARY TABLE IF NOT EXISTS `tmp_wp_Sto`
	(KEY `parent` (`Paechter`, `StoNr`, `StoV`))
	ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci
	AS SELECT
		DISTINCT `StoID`, `Paechter`, `GJ`, `StoNr`, `StoV`
	FROM `wp_Sto`
	WHERE
		`GJ` BETWEEN YEAR(CURDATE()) AND YEAR(CURDATE()) + 1;

	/* Function for remapping the StoID */
DROP FUNCTION IF EXISTS `remapSto`;
DELIMITER //
CREATE FUNCTION `remapSto` (
		Paechter SMALLINT(6),
		GJ SMALLINT(6),
		StoNr bigint(20),
		StoV TINYINT(4)
	)
	RETURNS INT(11)
	READS SQL DATA
BEGIN
	DECLARE var_StoID INT(11);
	SET var_StoID = NULL;
	SELECT
		`StoID` INTO var_StoID
	FROM `tmp_wp_Sto`
	WHERE
		`tmp_wp_Sto`.`Paechter` = Paechter AND
		`tmp_wp_Sto`.`StoNr` = StoNr AND
		`tmp_wp_Sto`.`StoV` = StoV AND
		`tmp_wp_Sto`.`GJ` BETWEEN YEAR(CURDATE()) AND YEAR(CURDATE()) + 1 AND
		`tmp_wp_Sto`.`GJ` != GJ
	LIMIT 1;
	RETURN var_StoID;
END //
DELIMITER ;

	/* Function for getting ComAW locations */
DROP FUNCTION IF EXISTS `getComAW`;
DELIMITER //
CREATE FUNCTION `getComAW` (
		Paechter SMALLINT(6),
		LWert4 INT(11)
	)
	RETURNS TINYINT(4)
BEGIN
	DECLARE var_ComAW TINYINT(4);
	IF (Paechter = 222 AND LWert4 = 220) THEN
		SET var_ComAW = -1;
	ELSE
		SET var_ComAW = 0;
	END IF;
	RETURN var_ComAW;
END //
DELIMITER ;

	/* Function for getting the Standort */
DROP FUNCTION IF EXISTS `getStandort`;
DELIMITER //
CREATE FUNCTION `getStandort` (
		StellenOrt SMALLINT(5),
		PLZ VARCHAR(5),
		HS CHAR(3),
		US CHAR(3),
		Standort VARCHAR(255),
		Ortname VARCHAR(40)
	)
	RETURNS VARCHAR(80)
BEGIN
	DECLARE var_Standort VARCHAR(255);
	IF (Standort LIKE 'ZZ\_%') THEN
		SET var_Standort = TRIM(REPLACE(Standort, 'ZZ_', ''));
	ELSEIF (HS = 'VI' AND US = 'NZ') THEN
		SET var_Standort = CONCAT('CLP Netz mit ', StellenOrt, ' Stellen in ', PLZ, ' ', TRIM(Ortname));
		IF (Standort IS NOT NULL AND Standort != '') THEN 
		/* AND Ortname NOT LIKE CONCAT('%', Standort, '%')) THEN */
			SET var_Standort = CONCAT(var_Standort, ' (', TRIM(Standort), ')');
		END IF;
	ELSE
		SET var_Standort = TRIM(Standort);
	END IF;
	RETURN var_Standort;
END //
DELIMITER ;

	/* Function for getting the PLZ */
DROP FUNCTION IF EXISTS `getPLZ`;
DELIMITER //
CREATE FUNCTION `getPLZ` (
		StoPLZ VARCHAR(5),
		OrtPLZ VARCHAR(5)
	)
	RETURNS VARCHAR(5)
BEGIN
	DECLARE var_PLZ VARCHAR(5);
	IF (StoPLZ = '0' OR StoPLZ = '') THEN
		SET var_PLZ = TRIM(OrtPLZ);
	ELSE
		SET var_PLZ = TRIM(StoPLZ);
	END IF;
	RETURN var_PLZ;
END //
DELIMITER ;

	/* Function for getting the Anschlagart */
DROP FUNCTION IF EXISTS `getAnschlagart`;
DELIMITER //
CREATE FUNCTION `getAnschlagart` (
		BezeichnungUS VARCHAR(40),
		BezeichnungHS VARCHAR(40)
	)
	RETURNS VARCHAR(40)
BEGIN
	DECLARE var_Anschlagart VARCHAR(40);
	IF (BezeichnungUS IS NOT NULL AND BezeichnungUS != '') THEN
		SET var_Anschlagart = BezeichnungUS;
	ELSE
		SET var_Anschlagart = BezeichnungHS;
	END IF;
	RETURN var_Anschlagart;
END //
DELIMITER ;

	/* Function for getting the Sperre */
DROP FUNCTION IF EXISTS `getSperre`;
DELIMITER //
CREATE FUNCTION `getSperre` (
		Paechter SMALLINT(6),
		GJ SMALLINT(6),
		StoNr bigint(20),
		StoV SMALLINT(6),
		StoB SMALLINT(6)
	)
	RETURNS TEXT
	READS SQL DATA
BEGIN
	DECLARE var_Sperre TEXT;
	SET var_Sperre = '';
	SELECT
		IFNULL(GROUP_CONCAT(IFNULL(`wp_StoSperre`.`Sperre`, 0)), 0) INTO var_Sperre
	FROM `wp_StoSperre`
	WHERE
		`wp_StoSperre`.`Paechter` = Paechter AND
		`wp_StoSperre`.`GJ` = GJ AND
		`wp_StoSperre`.`StoNr` = StoNr AND
		`wp_StoSperre`.`StoV` BETWEEN StoV AND StoB;
	RETURN var_Sperre;
END //
DELIMITER ;

	/* Function for getting the Politik */
DROP FUNCTION IF EXISTS `getPolitik`;
DELIMITER //
CREATE FUNCTION `getPolitik` (
		Paechter SMALLINT(6),
		VMNr bigint(20),
		Sperre TEXT,
		US VARCHAR(2)
	)
	RETURNS TINYINT(4)
BEGIN
	DECLARE var_Politik TINYINT(4);
	SET var_Politik = 0;
	IF (Paechter IN(115, 400, 500) OR CONCAT(',', Sperre, ',') LIKE '%,260,%' OR US IN('EK', 'ES')) THEN
		SET var_Politik = 1;
	END IF;
	RETURN var_Politik;
END //
DELIMITER ;

	/* Function for getting the Netz */
DROP FUNCTION IF EXISTS `getNetz`;
DELIMITER //
CREATE FUNCTION `getNetz` (
		Paechter SMALLINT(6),
		HS CHAR(3),
		US CHAR(3),
		Netz INT(11)
	)
	RETURNS INT(11)
BEGIN
	DECLARE var_NETZ INT(11);
	SET var_Netz = Netz;
	IF (HS = 'VI' AND US = 'NZ' AND Netz = 0) THEN
		SET var_Netz = -1;
	ELSEIF (HS = 'RS' AND US = 'NZ' AND Netz = 0) THEN
		SET var_Netz = -1;
	/* deactivated: Delete dummy mega-light locations
	ELSEIF (Paechter IN(220, 222, 368) AND HS = 'GV' AND Netz = 0) THEN
		SET var_Netz = -1;
	*/
	END IF;
	RETURN var_Netz;
END //
DELIMITER ;

	/* Function for getting the Bewertung */
DROP FUNCTION IF EXISTS `getBewertung`;
DELIMITER //
CREATE FUNCTION `getBewertung` (
		Bewertung FLOAT(4,3)
	)
	RETURNS FLOAT(4,3)
BEGIN
	DECLARE var_Bewertung FLOAT(6,3);
	SET var_Bewertung = Bewertung;
	IF (Bewertung IS NOT NULL AND Bewertung BETWEEN 0 AND 1) THEN
		SET var_Bewertung = 1;
	ELSEIF (Bewertung IS NULL OR Bewertung < 1 OR Bewertung > 6) THEN
		SET var_Bewertung = 6;
	END IF;
	RETURN var_Bewertung;
END //
DELIMITER ;

	/* Function for getting the Typ flag */
DROP FUNCTION IF EXISTS `getTyp`;
DELIMITER //
CREATE FUNCTION `getTyp` (
		HS CHAR(3),
		US CHAR(2)
	)
	RETURNS VARCHAR(3)
BEGIN
	DECLARE var_Typ VARCHAR(3);
	SET var_Typ = TRIM(HS);
	IF (HS = 'GF' AND US = 'VB') THEN
		SET var_Typ = 'ZZ';
	ELSEIF (HS = 'GF' AND US = 'VD') THEN
		SET var_Typ = 'ZZ';
	ELSEIF (HS = 'GF2') THEN
		SET var_Typ = 'PF';
	ELSEIF (HS = 'GF' AND US = 'SP') THEN
		SET var_Typ = 'SP';
	ELSEIF (HS = 'GF' AND US IN('CP', 'PB')) THEN
		SET var_Typ = 'GV';
	ELSEIF (HS = 'PB') THEN
		SET var_Typ = 'GV';
	ELSEIF (HS = 'VI' AND US = 'NZ') THEN
		SET var_Typ = 'VN';
	ELSEIF (HS = 'GV' AND US = 'NZ') THEN
		SET var_Typ = 'GN';
	ELSEIF (HS = 'RS' AND US = 'NZ') THEN
		SET var_Typ = 'RN';
	ELSEIF (HS = 'VD' OR HS = 'GD') THEN
		SET var_Typ = 'ZZ';
	END IF;
	RETURN var_Typ;
END //
DELIMITER ;

	/* Function for getting the Dauerwerbung flag */
DROP FUNCTION IF EXISTS `getDauerwerbung`;
DELIMITER //
CREATE FUNCTION `getDauerwerbung` (
		HS CHAR(3),
		US CHAR(2),
		Netz INT(11),
		Sperre TEXT,
		StellenOrt SMALLINT(5)
	)
	RETURNS TINYINT(4)
BEGIN
	DECLARE var_Dauerwerbung TINYINT(4);
	DECLARE var_Sperre TEXT;
	SET var_Sperre = REPLACE(CONCAT(',', Sperre, ','), ',', ',,');
	SET var_Dauerwerbung = 1;
	IF (HS = 'GF' AND US = 'VB') THEN
		SET var_Dauerwerbung = 0;
	ELSEIF (HS = 'VI' AND US = 'VB') THEN
		SET var_Dauerwerbung = 0;
	ELSEIF (HS = 'VI' AND US = 'NZ') THEN
		SET var_Dauerwerbung = 0;
	ELSEIF (HS = 'GV' AND US = 'NZ') THEN
		SET var_Dauerwerbung = 0;
	ELSEIF (Netz != 0 AND HS != 'RS') THEN
		SET var_Dauerwerbung = 0;
	ELSEIF (Sperre IS NOT NULL AND Sperre != '0' AND GREATEST(StellenOrt, 1) <= ROUND((LENGTH(var_Sperre) - LENGTH(REPLACE(var_Sperre, ',241,', ','))) / LENGTH(',241'))) THEN
		SET var_Dauerwerbung = 0;
	END IF;
	RETURN var_Dauerwerbung;
END //
DELIMITER ;


	/* Create the main table 123_StoFZ */
DROP VIEW IF EXISTS `123_StoFZ`;

CREATE TABLE `123_StoFZ` 
	(PRIMARY KEY (`StoID`), KEY StoData (`Paechter`, `GJ`, `StoNr`, `StoV`), KEY HsUs (`HS`, `US`), KEY Typ (`Typ`), KEY AutoSelect (`Bewertung`,`ComAW`))
	ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci
	SELECT
		`wp_Sto`.`StoID`, `wp_Sto`.`Paechter`, `wp_Sto`.`GJ`, `wp_Sto`.`StoNr`, `wp_Sto`.`StoV`, `wp_Sto`.`StoB`, `wp_Sto`.`StellenOrt`, `wp_Sto`.`Versand`, TRIM(`wp_Sto`.`Staat`) AS `Staat`, `wp_Sto`.`OrtNr`, `wp_Ort`.`EW`, TRIM(`wp_Ort`.`Ortname`) AS `Ortname`,
			/* Postleitzahl (Sto- oder Ort-Tabelle) */
		@PLZ := getPLZ(`wp_Sto`.`PLZ`, `wp_Ort`.`PLZ`) AS `PLZ`,
		TRIM(`wp_Sto`.`HS`) AS `HS`, TRIM(`wp_Sto`.`US`) AS `US`,
			/* Anschlagart */
		getAnschlagart(`wp_dbo_US`.`Bezeichnung`, `wp_dbo_HS`.`Bezeichnung`) AS `Anschlagart`,
		TRIM(`wp_Sto`.`Belegung`) AS `Belegung`, `wp_Sto`.`Preis`, TRIM(`wp_Sto`.`Beleuchtung`) AS `Beleuchtung`, `wp_Sto`.`VMNr`, TRIM(`wp_VMarkt`.`VMName`) AS `VMName`, TRIM(`wp_VMarkt`.`VMKette`) AS `VMKette`, TRIM(`wp_Sto`.`Ortsteil`) AS `Ortsteil`,
			/* Standortbezeichnung */
		getStandort(`wp_Sto`.`StellenOrt`, @PLZ, `wp_Sto`.`HS`, `wp_Sto`.`US`, `wp_Sto`.`Standort`, `wp_Ort`.`Ortname`) AS `Standort`,
		TRIM(`wp_Sto`.`Textfeld`) AS `Textfeld`, TRIM(`wp_Sto`.`FotoName`) AS `FotoName`,
			/* Netze, Anzahl, Bewertung & Typ */
		@Netz := getNetz(`wp_Sto`.`Paechter`, `wp_Sto`.`HS`, `wp_Sto`.`US`, `wp_Sto`.`Netz`) AS `Netz`, `wp_LWTD`.`Leistungswert` AS `PpS`, getBewertung(`wp_Sto`.`Bewertung`) AS `Bewertung`, getTyp(`wp_Sto`.`HS`, `wp_Sto`.`US`) AS `Typ`,
			/* Sperrvermerk */
		@Sperre := getSperre(`wp_Sto`.`Paechter`, `wp_Sto`.`GJ`, `wp_Sto`.`StoNr`, `wp_Sto`.`StoV`, `wp_Sto`.`StoB`) AS `Sperre`,
			/* Politische/ religioese Werbung */
		getPolitik(`wp_Sto`.`Paechter`, `wp_Sto`.`VMNr`, @Sperre, `wp_Sto`.`US`) AS `Politik`,
			/* Dauerwerbung */
		getDauerwerbung(`wp_Sto`.`HS`, `wp_Sto`.`US`, @Netz, @Sperre, `wp_Sto`.`StellenOrt`) AS `Dauerwerbung`,
			/* Remap StoID from another Year */
		remapSto(`wp_Sto`.`Paechter`, `wp_Sto`.`GJ`, `wp_Sto`.`StoNr`, `wp_Sto`.`StoV`) AS `RemapID`,
		getComAW(`wp_Sto`.`Paechter`, `wp_Sto`.`LWert4`) AS `ComAW`,
			/* Freizahlen */
		ifnull(`wp_FZ`.`ZR1`,0) AS `ZR1`, 
		ifnull(`wp_FZ`.`ZR2` ,0) AS `ZR2`, 
		ifnull(`wp_FZ`.`ZR3` ,0) AS `ZR3`, 
		ifnull(`wp_FZ`.`ZR4` ,0) AS `ZR4`, 
		ifnull(`wp_FZ`.`ZR5` ,0) AS `ZR5`, 
		ifnull(`wp_FZ`.`ZR6` ,0) AS `ZR6`, 
		ifnull(`wp_FZ`.`ZR7` ,0) AS `ZR7`, 
		ifnull(`wp_FZ`.`ZR8` ,0) AS `ZR8`, 
		ifnull(`wp_FZ`.`ZR9` ,0) AS `ZR9`, 
		ifnull(`wp_FZ`.`ZR10` ,0) AS `ZR10`, 
		ifnull(`wp_FZ`.`ZR11` ,0) AS `ZR11`, 
		ifnull(`wp_FZ`.`ZR12` ,0) AS `ZR12`, 
		ifnull(`wp_FZ`.`ZR13` ,0) AS `ZR13`, 
		ifnull(`wp_FZ`.`ZR14` ,0) AS `ZR14`, 
		ifnull(`wp_FZ`.`ZR15` ,0) AS `ZR15`, 
		ifnull(`wp_FZ`.`ZR16` ,0) AS `ZR16`, 
		ifnull(`wp_FZ`.`ZR17` ,0) AS `ZR17`, 
		ifnull(`wp_FZ`.`ZR18` ,0) AS `ZR18`, 
		ifnull(`wp_FZ`.`ZR19` ,0) AS `ZR19`, 
		ifnull(`wp_FZ`.`ZR20` ,0) AS `ZR20`, 
		ifnull(`wp_FZ`.`ZR21` ,0) AS `ZR21`, 
		ifnull(`wp_FZ`.`ZR22` ,0) AS `ZR22`, 
		ifnull(`wp_FZ`.`ZR23` ,0) AS `ZR23`, 
		ifnull(`wp_FZ`.`ZR24` ,0) AS `ZR24`, 
		ifnull(`wp_FZ`.`ZR25` ,0) AS `ZR25`, 
		ifnull(`wp_FZ`.`ZR26` ,0) AS `ZR26`, 
		ifnull(`wp_FZ`.`ZR27` ,0) AS `ZR27`, 
		ifnull(`wp_FZ`.`ZR28` ,0) AS `ZR28`, 
		ifnull(`wp_FZ`.`ZR29` ,0) AS `ZR29`, 
		ifnull(`wp_FZ`.`ZR30` ,0) AS `ZR30`, 
		ifnull(`wp_FZ`.`ZR31` ,0) AS `ZR31`, 
		ifnull(`wp_FZ`.`ZR32` ,0) AS `ZR32`, 
		ifnull(`wp_FZ`.`ZR33` ,0) AS `ZR33`, 
		ifnull(`wp_FZ`.`ZR34` ,0) AS `ZR34`,
		ifnull(`wp_FZ`.`ZR35` ,0) AS `ZR35`, 
		ifnull(`wp_FZ`.`ZR36` ,0) AS `ZR36`, 
		ifnull(`wp_FZ`.`ZR37` ,0) AS `ZR37`, 
		ifnull(`wp_FZ`.`ZR38` ,0) AS `ZR38`, 
		ifnull(`wp_FZ`.`ZR39` ,0) AS `ZR39`, 
		ifnull(`wp_FZ`.`ZR40` ,0) AS `ZR40`, 
		ifnull(`wp_FZ`.`ZR41` ,0) AS `ZR41`, 
		ifnull(`wp_FZ`.`ZR42` ,0) AS `ZR42`, 
		ifnull(`wp_FZ`.`ZR43` ,0) AS `ZR43`, 
		ifnull(`wp_FZ`.`ZR44` ,0) AS `ZR44`, 
		ifnull(`wp_FZ`.`ZR45` ,0) AS `ZR45`, 
		ifnull(`wp_FZ`.`ZR46` ,0) AS `ZR46`,
 		ifnull(`wp_FZ`.`ZR47` ,0) AS `ZR47`, 
		ifnull(`wp_FZ`.`ZR48` ,0) AS `ZR48`, 
		ifnull(`wp_FZ`.`ZR49` ,0) AS `ZR49`, 
		ifnull(`wp_FZ`.`ZR50` ,0) AS `ZR50`, 
		ifnull(`wp_FZ`.`ZR51` ,0) AS `ZR51`, 
		ifnull(`wp_FZ`.`ZR52` ,0) AS `ZR52`, 
		ifnull(`wp_FZ`.`ZR53` ,0) AS `ZR53`,
		ifnull(`wp_FZ`.`PZR1`,`Preis`) AS `PZR1`, 
		ifnull(`wp_FZ`.`PZR2`,`Preis`) AS `PZR2`, 
		ifnull(`wp_FZ`.`PZR3`,`Preis`) AS `PZR3`, 
		ifnull(`wp_FZ`.`PZR4`,`Preis`) AS `PZR4`, 
		ifnull(`wp_FZ`.`PZR5`,`Preis`) AS `PZR5`, 
		ifnull(`wp_FZ`.`PZR6`,`Preis`) AS `PZR6`, 
		ifnull(`wp_FZ`.`PZR7`,`Preis`) AS `PZR7`, 
		ifnull(`wp_FZ`.`PZR8`,`Preis`) AS `PZR8`, 
		ifnull(`wp_FZ`.`PZR9`,`Preis`) AS `PZR9`, 
		ifnull(`wp_FZ`.`PZR10`,`Preis`) AS `PZR10`, 
		ifnull(`wp_FZ`.`PZR11`,`Preis`) AS `PZR11`, 
		ifnull(`wp_FZ`.`PZR12`,`Preis`) AS `PZR12`, 
		ifnull(`wp_FZ`.`PZR13`,`Preis`) AS `PZR13`, 
		ifnull(`wp_FZ`.`PZR14`,`Preis`) AS `PZR14`, 
		ifnull(`wp_FZ`.`PZR15`,`Preis`) AS `PZR15`, 
		ifnull(`wp_FZ`.`PZR16`,`Preis`) AS `PZR16`, 
		ifnull(`wp_FZ`.`PZR17`,`Preis`) AS `PZR17`, 
		ifnull(`wp_FZ`.`PZR18`,`Preis`) AS `PZR18`, 
		ifnull(`wp_FZ`.`PZR19`,`Preis`) AS `PZR19`, 
		ifnull(`wp_FZ`.`PZR20`,`Preis`) AS `PZR20`, 
		ifnull(`wp_FZ`.`PZR21`,`Preis`) AS `PZR21`, 
		ifnull(`wp_FZ`.`PZR22`,`Preis`) AS `PZR22`, 
		ifnull(`wp_FZ`.`PZR23`,`Preis`) AS `PZR23`, 
		ifnull(`wp_FZ`.`PZR24`,`Preis`) AS `PZR24`, 
		ifnull(`wp_FZ`.`PZR25`,`Preis`) AS `PZR25`, 
		ifnull(`wp_FZ`.`PZR26`,`Preis`) AS `PZR26`, 
		ifnull(`wp_FZ`.`PZR27`,`Preis`) AS `PZR27`, 
		ifnull(`wp_FZ`.`PZR28`,`Preis`) AS `PZR28`, 
		ifnull(`wp_FZ`.`PZR29`,`Preis`) AS `PZR29`, 
		ifnull(`wp_FZ`.`PZR30`,`Preis`) AS `PZR30`, 
		ifnull(`wp_FZ`.`PZR31`,`Preis`) AS `PZR31`, 
		ifnull(`wp_FZ`.`PZR32`,`Preis`) AS `PZR32`, 
		ifnull(`wp_FZ`.`PZR33`,`Preis`) AS `PZR33`, 
		ifnull(`wp_FZ`.`PZR34`,`Preis`) AS `PZR34`, 
		ifnull(`wp_FZ`.`PZR35`,`Preis`) AS `PZR35`, 
		ifnull(`wp_FZ`.`PZR36`,`Preis`) AS `PZR36`, 
		ifnull(`wp_FZ`.`PZR37`,`Preis`) AS `PZR37`, 
		ifnull(`wp_FZ`.`PZR38`,`Preis`) AS `PZR38`, 
		ifnull(`wp_FZ`.`PZR39`,`Preis`) AS `PZR39`, 
		ifnull(`wp_FZ`.`PZR40`,`Preis`) AS `PZR40`, 
		ifnull(`wp_FZ`.`PZR41`,`Preis`) AS `PZR41`, 
		ifnull(`wp_FZ`.`PZR42`,`Preis`) AS `PZR42`, 
		ifnull(`wp_FZ`.`PZR43`,`Preis`) AS `PZR43`, 
		ifnull(`wp_FZ`.`PZR44`,`Preis`) AS `PZR44`, 
		ifnull(`wp_FZ`.`PZR45`,`Preis`) AS `PZR45`, 
		ifnull(`wp_FZ`.`PZR46`,`Preis`) AS `PZR46`, 
		ifnull(`wp_FZ`.`PZR47`,`Preis`) AS `PZR47`, 
		ifnull(`wp_FZ`.`PZR48`,`Preis`) AS `PZR48`, 
		ifnull(`wp_FZ`.`PZR49`,`Preis`) AS `PZR49`, 
		ifnull(`wp_FZ`.`PZR50`,`Preis`) AS `PZR50`, 
		ifnull(`wp_FZ`.`PZR51`,`Preis`) AS `PZR51`, 
		ifnull(`wp_FZ`.`PZR52`,`Preis`) AS `PZR52`, 
		ifnull(`wp_FZ`.`PZR53`,`Preis`) AS `PZR53`,
		TRIM(`wp_Sto`.`UTMLA`) AS `UTMLA`, 
		TRIM(`wp_Sto`.`UTMBA`) AS `UTMBA`,
		cast(null as char(5)) AS `Format`,
		`wp_Sto`.`Einzelstellenvermarktung`
 
	FROM `wp_Sto`
		INNER JOIN `wp_FZ` ON(`wp_Sto`.`StoID` = `wp_FZ`.`StoID`)
		LEFT JOIN `wp_Ort` ON(`wp_Sto`.`Staat` = `wp_Ort`.`Staat` AND `wp_Sto`.`OrtNr` = `wp_Ort`.`OrtNr`)
		LEFT JOIN `wp_dbo_US` ON(`wp_Sto`.`HS` = `wp_dbo_US`.`HS` AND `wp_Sto`.`US` = `wp_dbo_US`.`US`)
		LEFT JOIN `wp_dbo_HS` ON(`wp_Sto`.`HS` = `wp_dbo_HS`.`HS`)
		LEFT JOIN `wp_VMarkt` ON(`wp_Sto`.`GJ` = `wp_VMarkt`.`GJ` AND `wp_Sto`.`Paechter` = `wp_VMarkt`.`Paechter` AND `wp_Sto`.`VMNr` = `wp_VMarkt`.`VMNr`)
		LEFT JOIN `wp_LWTD` ON(`wp_Sto`.`StoID` = `wp_LWTD`.`StoID`)
	WHERE
		`wp_Sto`.`GJ` >= YEAR(CURDATE()) AND
		`wp_Sto`.`Staat` = 'D' AND
		`wp_Sto`.`Belegung` NOT IN ('D', '') AND
		`wp_Sto`.`StatusIst` = '' AND
		`wp_Sto`.`StatusNeu` = '' AND
		`wp_Sto`.`StatusWechsel` = 0 AND
		`wp_Ort`.`Sperre` = -1
	;

/* Update Format for Videoboards */
UPDATE `123_StoFZ` SET `123_StoFZ`.`Format` = '16:9' WHERE `123_StoFZ`.`HS` = 'GD';
UPDATE `123_StoFZ` SET `123_StoFZ`.`Format` = '9:16' WHERE `123_StoFZ`.`HS` = 'VD';
UPDATE `123_StoFZ` SET `123_StoFZ`.`Format` = '9:16' WHERE  `123_StoFZ`.`HS` = 'GF' AND US='VD' AND `123_StoFZ`.`Typ` = 'ZZ'; 
UPDATE `123_StoFZ` SET `123_StoFZ`.`Format` = '4:3' WHERE  `123_StoFZ`.`HS` = 'GF' AND NOT US='VD' AND `123_StoFZ`.`Typ` = 'ZZ'; 
/* Update geo coordinates for net locations */
UPDATE `123_StoFZ`
	SET
		`123_StoFZ`.`UTMLA` = (SELECT `wp_Ort`.`UTMLA` FROM `wp_Ort` WHERE `wp_Ort`.`OrtNr` = `123_StoFZ`.`OrtNr` LIMIT 1),
		`123_StoFZ`.`UTMBA` = (SELECT `wp_Ort`.`UTMBA` FROM `wp_Ort` WHERE `wp_Ort`.`OrtNr` = `123_StoFZ`.`OrtNr` LIMIT 1)
	WHERE
		(
			(
				`123_StoFZ`.`HS` IN('GV', 'VI', 'RS') AND
				`123_StoFZ`.`US` = 'NZ'
			) OR (
				`123_StoFZ`.`HS` = 'GF' AND
				`123_StoFZ`.`US` = 'VB' AND
				`123_StoFZ`.`Standort` LIKE '%netz%'
				
			)
		) AND
		`123_StoFZ`.`UTMLA` = '0' AND
		`123_StoFZ`.`UTMBA` = '0';

/* Update mega-light and "other" locations */
SET @StoID = (SELECT `123_StoFZ`.`StoID` FROM `123_StoFZ` ORDER BY `123_StoFZ`.`StoID` DESC LIMIT 1);
INSERT INTO `123_StoFZ` 
	SELECT
		(@StoID := @StoID + 1) AS `StoID`, 
		`123_StoFZ`.`Paechter`,
		`123_StoFZ`.`GJ`,
		`123_StoFZ`.`StoNr`,
		MIN(`123_StoFZ`.`StoV`) AS `StoV`,
		MAX(`123_StoFZ`.`StoB`) AS `StoB`,
		COUNT(`123_StoFZ`.`StellenOrt`) AS `StellenOrt`,
		`123_StoFZ`.`Versand`,
		`123_StoFZ`.`Staat`,
		`123_StoFZ`.`OrtNr`,
		`123_StoFZ`.`EW`,
		`123_StoFZ`.`Ortname`,
		`123_StoFZ`.`PLZ`,
		`123_StoFZ`.`HS`,
		`123_StoFZ`.`US`,
		`123_StoFZ`.`Anschlagart`,
		`123_StoFZ`.`Belegung`,
		MAX(`123_StoFZ`.`Preis`) AS `Preis`,
		`123_StoFZ`.`Beleuchtung`,
		`123_StoFZ`.`VMNr`,
		`123_StoFZ`.`VMName`,
		`123_StoFZ`.`VMKette`,
		`123_StoFZ`.`Ortsteil`,
		`123_StoFZ`.`Standort`,
		`123_StoFZ`.`Textfeld`,
		`123_StoFZ`.`FotoName`,
		'-1' AS `Netz`,
		`123_StoFZ`.`PpS`,
		AVG(NULLIF(`123_StoFZ`.`Bewertung`, 0)) AS `Bewertung`,
		`123_StoFZ`.`Typ`,
		IFNULL(GROUP_CONCAT(IFNULL(`123_StoFZ`.`Sperre`, 0)), 0) AS `Sperre`,
		MAX(`123_StoFZ`.`Politik`) AS `Politik`,
		MAX(`123_StoFZ`.`Dauerwerbung`) AS `Dauerwerbung`,
		0 AS `RemapID`,
		MAX(`123_StoFZ`.`ComAW`) AS `ComAW`,
		MAX(`123_StoFZ`.`ZR1`) AS `ZR1`,
		MAX(`123_StoFZ`.`ZR2`) AS `ZR2`,
		MAX(`123_StoFZ`.`ZR3`) AS `ZR3`,
		MAX(`123_StoFZ`.`ZR4`) AS `ZR4`,
		MAX(`123_StoFZ`.`ZR5`) AS `ZR5`,
		MAX(`123_StoFZ`.`ZR6`) AS `ZR6`,
		MAX(`123_StoFZ`.`ZR7`) AS `ZR7`,
		MAX(`123_StoFZ`.`ZR8`) AS `ZR8`,
		MAX(`123_StoFZ`.`ZR9`) AS `ZR9`,
		MAX(`123_StoFZ`.`ZR10`) AS `ZR10`,
		MAX(`123_StoFZ`.`ZR11`) AS `ZR11`,
		MAX(`123_StoFZ`.`ZR12`) AS `ZR12`,
		MAX(`123_StoFZ`.`ZR13`) AS `ZR13`,
		MAX(`123_StoFZ`.`ZR14`) AS `ZR14`,
		MAX(`123_StoFZ`.`ZR15`) AS `ZR15`,
		MAX(`123_StoFZ`.`ZR16`) AS `ZR16`,
		MAX(`123_StoFZ`.`ZR17`) AS `ZR17`,
		MAX(`123_StoFZ`.`ZR18`) AS `ZR18`,
		MAX(`123_StoFZ`.`ZR19`) AS `ZR19`,
		MAX(`123_StoFZ`.`ZR20`) AS `ZR20`,
		MAX(`123_StoFZ`.`ZR21`) AS `ZR21`,
		MAX(`123_StoFZ`.`ZR22`) AS `ZR22`,
		MAX(`123_StoFZ`.`ZR23`) AS `ZR23`,
		MAX(`123_StoFZ`.`ZR24`) AS `ZR24`,
		MAX(`123_StoFZ`.`ZR25`) AS `ZR25`,
		MAX(`123_StoFZ`.`ZR26`) AS `ZR26`,
		MAX(`123_StoFZ`.`ZR27`) AS `ZR27`,
		MAX(`123_StoFZ`.`ZR28`) AS `ZR28`,
		MAX(`123_StoFZ`.`ZR29`) AS `ZR29`,
		MAX(`123_StoFZ`.`ZR30`) AS `ZR30`,
		MAX(`123_StoFZ`.`ZR31`) AS `ZR31`,
		MAX(`123_StoFZ`.`ZR32`) AS `ZR32`,
		MAX(`123_StoFZ`.`ZR33`) AS `ZR33`,
		MAX(`123_StoFZ`.`ZR34`) AS `ZR34`,
		MAX(`123_StoFZ`.`ZR35`) AS `ZR35`,
		MAX(`123_StoFZ`.`ZR36`) AS `ZR36`,
		MAX(`123_StoFZ`.`ZR37`) AS `ZR37`,
		MAX(`123_StoFZ`.`ZR38`) AS `ZR38`,
		MAX(`123_StoFZ`.`ZR39`) AS `ZR39`,
		MAX(`123_StoFZ`.`ZR40`) AS `ZR40`,
		MAX(`123_StoFZ`.`ZR41`) AS `ZR41`,
		MAX(`123_StoFZ`.`ZR42`) AS `ZR42`,
		MAX(`123_StoFZ`.`ZR43`) AS `ZR43`,
		MAX(`123_StoFZ`.`ZR44`) AS `ZR44`,
		MAX(`123_StoFZ`.`ZR45`) AS `ZR45`,
		MAX(`123_StoFZ`.`ZR46`) AS `ZR46`,
		MAX(`123_StoFZ`.`ZR47`) AS `ZR47`,
		MAX(`123_StoFZ`.`ZR48`) AS `ZR48`,
		MAX(`123_StoFZ`.`ZR49`) AS `ZR49`,
		MAX(`123_StoFZ`.`ZR50`) AS `ZR50`,
		MAX(`123_StoFZ`.`ZR51`) AS `ZR51`,
		MAX(`123_StoFZ`.`ZR52`) AS `ZR52`,
		MAX(`123_StoFZ`.`ZR53`) AS `ZR53`,
		MAX(`123_StoFZ`.`PZR1`) AS `PZR1`,
		MAX(`123_StoFZ`.`PZR2`) AS `PZR2`,
		MAX(`123_StoFZ`.`PZR3`) AS `PZR3`,
		MAX(`123_StoFZ`.`PZR4`) AS `PZR4`,
		MAX(`123_StoFZ`.`PZR5`) AS `PZR5`,
		MAX(`123_StoFZ`.`PZR6`) AS `PZR6`,
		MAX(`123_StoFZ`.`PZR7`) AS `PZR7`,
		MAX(`123_StoFZ`.`PZR8`) AS `PZR8`,
		MAX(`123_StoFZ`.`PZR9`) AS `PZR9`,
		MAX(`123_StoFZ`.`PZR10`) AS `PZR10`,
		MAX(`123_StoFZ`.`PZR11`) AS `PZR11`,
		MAX(`123_StoFZ`.`PZR12`) AS `PZR12`,
		MAX(`123_StoFZ`.`PZR13`) AS `PZR13`,
		MAX(`123_StoFZ`.`PZR14`) AS `PZR14`,
		MAX(`123_StoFZ`.`PZR15`) AS `PZR15`,
		MAX(`123_StoFZ`.`PZR16`) AS `PZR16`,
		MAX(`123_StoFZ`.`PZR17`) AS `PZR17`,
		MAX(`123_StoFZ`.`PZR18`) AS `PZR18`,
		MAX(`123_StoFZ`.`PZR19`) AS `PZR19`,
		MAX(`123_StoFZ`.`PZR20`) AS `PZR20`,
		MAX(`123_StoFZ`.`PZR21`) AS `PZR21`,
		MAX(`123_StoFZ`.`PZR22`) AS `PZR22`,
		MAX(`123_StoFZ`.`PZR23`) AS `PZR23`,
		MAX(`123_StoFZ`.`PZR24`) AS `PZR24`,
		MAX(`123_StoFZ`.`PZR25`) AS `PZR25`,
		MAX(`123_StoFZ`.`PZR26`) AS `PZR26`,
		MAX(`123_StoFZ`.`PZR27`) AS `PZR27`,
		MAX(`123_StoFZ`.`PZR28`) AS `PZR28`,
		MAX(`123_StoFZ`.`PZR29`) AS `PZR29`,
		MAX(`123_StoFZ`.`PZR30`) AS `PZR30`,
		MAX(`123_StoFZ`.`PZR31`) AS `PZR31`,
		MAX(`123_StoFZ`.`PZR32`) AS `PZR32`,
		MAX(`123_StoFZ`.`PZR33`) AS `PZR33`,
		MAX(`123_StoFZ`.`PZR34`) AS `PZR34`,
		MAX(`123_StoFZ`.`PZR35`) AS `PZR35`,
		MAX(`123_StoFZ`.`PZR36`) AS `PZR36`,
		MAX(`123_StoFZ`.`PZR37`) AS `PZR37`,
		MAX(`123_StoFZ`.`PZR38`) AS `PZR38`,
		MAX(`123_StoFZ`.`PZR39`) AS `PZR39`,
		MAX(`123_StoFZ`.`PZR40`) AS `PZR40`,
		MAX(`123_StoFZ`.`PZR41`) AS `PZR41`,
		MAX(`123_StoFZ`.`PZR42`) AS `PZR42`,
		MAX(`123_StoFZ`.`PZR43`) AS `PZR43`,
		MAX(`123_StoFZ`.`PZR44`) AS `PZR44`,
		MAX(`123_StoFZ`.`PZR45`) AS `PZR45`,
		MAX(`123_StoFZ`.`PZR46`) AS `PZR46`,
		MAX(`123_StoFZ`.`PZR47`) AS `PZR47`,
		MAX(`123_StoFZ`.`PZR48`) AS `PZR48`,
		MAX(`123_StoFZ`.`PZR49`) AS `PZR49`,
		MAX(`123_StoFZ`.`PZR50`) AS `PZR50`,
		MAX(`123_StoFZ`.`PZR51`) AS `PZR51`,
		MAX(`123_StoFZ`.`PZR52`) AS `PZR52`,
		MAX(`123_StoFZ`.`PZR53`) AS `PZR53`,
		`123_StoFZ`.`UTMLA`,
		`123_StoFZ`.`UTMBA`,
		null as Format,
		1 as `Einzelstellenvermarktung`
	FROM `123_StoFZ`
	WHERE
		`123_StoFZ`.`Paechter` IN(220, 222, 368, 400) AND
		(	
			
			(
				`123_StoFZ`.`HS` = 'GV' AND
				`123_StoFZ`.`US` != 'NZ'
			) OR (
				`123_StoFZ`.`HS` = 'SO' AND
				`123_StoFZ`.`US` IN('GL', 'S4')
			) OR (
				`123_StoFZ`.`HS` = 'VI' AND
				`123_StoFZ`.`US` = 'WX'
			)
		) AND
		`123_StoFZ`.`Netz` = 0
	GROUP BY
		`123_StoFZ`.`Paechter`,
		`123_StoFZ`.`GJ`,
		`123_StoFZ`.`StoNr`,
		`123_StoFZ`.`OrtNr`,
		`123_StoFZ`.`HS`,
		IF(`123_StoFZ`.`Paechter` IN(368, 400), SUBSTRING(LPAD(`123_StoFZ`.`StoV`, 2, '0'), 1, 1), `123_StoFZ`.`US`)
	;


DROP FUNCTION IF EXISTS `remapSto`;
DROP FUNCTION IF EXISTS `getStandort`;
DROP FUNCTION IF EXISTS `getComAW`;
DROP FUNCTION IF EXISTS `getPLZ`;
DROP FUNCTION IF EXISTS `getAnschlagart`;
DROP FUNCTION IF EXISTS `getSperre`;
DROP FUNCTION IF EXISTS `getPolitik`;
DROP FUNCTION IF EXISTS `getNetz`;
DROP FUNCTION IF EXISTS `getBewertung`;
DROP FUNCTION IF EXISTS `getTyp`;
DROP FUNCTION IF EXISTS `getDauerwerbung`;

DROP TEMPORARY TABLE IF EXISTS `tmp_wp_Sto`;


	/* Create the main table 123_Ort */
DROP VIEW IF EXISTS `123_Ort`;

CREATE TABLE `123_Ort`
	(PRIMARY KEY (`OrtNr`))
	ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci
	SELECT
		`wp_Ort`.`OrtNr`, `wp_Ort`.`Ortname`, `wp_Ort`.`PLZ`, `wp_Ort`.`Kreis`, `wp_Ort`.`EW`, `wp_Ort`.`Belegung`, `wp_Ort`.`UTMLA`, `wp_Ort`.`UTMBA`
	FROM `wp_Ort`
	WHERE
		`wp_Ort`.`Staat` = 'D' AND
		`wp_Ort`.`OrtNr` > 1000000 AND
		`wp_Ort`.`Sperre` = -1
	;


	/* Create the permanent ad price table 123_StoDW */
DROP VIEW IF EXISTS `123_StoDW`;

CREATE TABLE `123_StoDW`
	(PRIMARY KEY (`StoID`), KEY StoData (`Paechter`, `StoNr`, `StoV`))
	ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci
	SELECT
		`wp_StoDwStoe`.`StoID`, `wp_StoDwStoe`.`Paechter`, `wp_StoDwStoe`.`StoNr`, `wp_StoDwStoe`.`StoV`, `wp_StoDwStoe`.`PreisDW1`, `wp_StoDwStoe`.`PreisDW2`, `wp_StoDwStoe`.`PreisDW3`, `wp_StoDwStoe`.`PreisDW4`
	FROM `wp_StoDwStoe`
	WHERE 1
	;

/* Update CLP price surcharge */
DROP PROCEDURE IF EXISTS `updateStoPrices`;
DELIMITER //
CREATE PROCEDURE `updateStoPrices`(Paechter SMALLINT(6), newPrice DECIMAL(7,3), HS CHAR(3), Surcharge BOOLEAN, Additional BOOLEAN)
BEGIN
	UPDATE `123_StoFZ`
		SET
			`Preis` = IF(Surcharge, ROUND(`Preis` * newPrice, 2), IF (Additional, ROUND(`Preis` + newPrice, 2), newPrice)),
			`PZR1` = IF(Surcharge, ROUND(`PZR1` * newPrice, 2), IF (Additional, ROUND(`PZR1` + newPrice, 2), newPrice)),
			`PZR2` = IF(Surcharge, ROUND(`PZR2` * newPrice, 2), IF (Additional, ROUND(`PZR2` + newPrice, 2), newPrice)),
			`PZR3` = IF(Surcharge, ROUND(`PZR3` * newPrice, 2), IF (Additional, ROUND(`PZR3` + newPrice, 2), newPrice)),
			`PZR4` = IF(Surcharge, ROUND(`PZR4` * newPrice, 2), IF (Additional, ROUND(`PZR4` + newPrice, 2), newPrice)),
			`PZR5` = IF(Surcharge, ROUND(`PZR5` * newPrice, 2), IF (Additional, ROUND(`PZR5` + newPrice, 2), newPrice)),
			`PZR6` = IF(Surcharge, ROUND(`PZR6` * newPrice, 2), IF (Additional, ROUND(`PZR6` + newPrice, 2), newPrice)),
			`PZR7` = IF(Surcharge, ROUND(`PZR7` * newPrice, 2), IF (Additional, ROUND(`PZR7` + newPrice, 2), newPrice)),
			`PZR8` = IF(Surcharge, ROUND(`PZR8` * newPrice, 2), IF (Additional, ROUND(`PZR8` + newPrice, 2), newPrice)),
			`PZR9` = IF(Surcharge, ROUND(`PZR9` * newPrice, 2), IF (Additional, ROUND(`PZR9` + newPrice, 2), newPrice)),
			`PZR10` = IF(Surcharge, ROUND(`PZR10` * newPrice, 2), IF (Additional, ROUND(`PZR10` + newPrice, 2), newPrice)),
			`PZR11` = IF(Surcharge, ROUND(`PZR11` * newPrice, 2), IF (Additional, ROUND(`PZR11` + newPrice, 2), newPrice)),
			`PZR12` = IF(Surcharge, ROUND(`PZR12` * newPrice, 2), IF (Additional, ROUND(`PZR12` + newPrice, 2), newPrice)),
			`PZR13` = IF(Surcharge, ROUND(`PZR13` * newPrice, 2), IF (Additional, ROUND(`PZR13` + newPrice, 2), newPrice)),
			`PZR14` = IF(Surcharge, ROUND(`PZR14` * newPrice, 2), IF (Additional, ROUND(`PZR14` + newPrice, 2), newPrice)),
			`PZR15` = IF(Surcharge, ROUND(`PZR15` * newPrice, 2), IF (Additional, ROUND(`PZR15` + newPrice, 2), newPrice)),
			`PZR16` = IF(Surcharge, ROUND(`PZR16` * newPrice, 2), IF (Additional, ROUND(`PZR16` + newPrice, 2), newPrice)),
			`PZR17` = IF(Surcharge, ROUND(`PZR17` * newPrice, 2), IF (Additional, ROUND(`PZR17` + newPrice, 2), newPrice)),
			`PZR18` = IF(Surcharge, ROUND(`PZR18` * newPrice, 2), IF (Additional, ROUND(`PZR18` + newPrice, 2), newPrice)),
			`PZR19` = IF(Surcharge, ROUND(`PZR19` * newPrice, 2), IF (Additional, ROUND(`PZR19` + newPrice, 2), newPrice)),
			`PZR20` = IF(Surcharge, ROUND(`PZR20` * newPrice, 2), IF (Additional, ROUND(`PZR20` + newPrice, 2), newPrice)),
			`PZR21` = IF(Surcharge, ROUND(`PZR21` * newPrice, 2), IF (Additional, ROUND(`PZR21` + newPrice, 2), newPrice)),
			`PZR22` = IF(Surcharge, ROUND(`PZR22` * newPrice, 2), IF (Additional, ROUND(`PZR22` + newPrice, 2), newPrice)),
			`PZR23` = IF(Surcharge, ROUND(`PZR23` * newPrice, 2), IF (Additional, ROUND(`PZR23` + newPrice, 2), newPrice)),
			`PZR24` = IF(Surcharge, ROUND(`PZR24` * newPrice, 2), IF (Additional, ROUND(`PZR24` + newPrice, 2), newPrice)),
			`PZR25` = IF(Surcharge, ROUND(`PZR25` * newPrice, 2), IF (Additional, ROUND(`PZR25` + newPrice, 2), newPrice)),
			`PZR26` = IF(Surcharge, ROUND(`PZR26` * newPrice, 2), IF (Additional, ROUND(`PZR26` + newPrice, 2), newPrice)),
			`PZR27` = IF(Surcharge, ROUND(`PZR27` * newPrice, 2), IF (Additional, ROUND(`PZR27` + newPrice, 2), newPrice)),
			`PZR28` = IF(Surcharge, ROUND(`PZR28` * newPrice, 2), IF (Additional, ROUND(`PZR28` + newPrice, 2), newPrice)),
			`PZR29` = IF(Surcharge, ROUND(`PZR29` * newPrice, 2), IF (Additional, ROUND(`PZR29` + newPrice, 2), newPrice)),
			`PZR30` = IF(Surcharge, ROUND(`PZR30` * newPrice, 2), IF (Additional, ROUND(`PZR30` + newPrice, 2), newPrice)),
			`PZR31` = IF(Surcharge, ROUND(`PZR31` * newPrice, 2), IF (Additional, ROUND(`PZR31` + newPrice, 2), newPrice)),
			`PZR32` = IF(Surcharge, ROUND(`PZR32` * newPrice, 2), IF (Additional, ROUND(`PZR32` + newPrice, 2), newPrice)),
			`PZR33` = IF(Surcharge, ROUND(`PZR33` * newPrice, 2), IF (Additional, ROUND(`PZR33` + newPrice, 2), newPrice)),
			`PZR34` = IF(Surcharge, ROUND(`PZR34` * newPrice, 2), IF (Additional, ROUND(`PZR34` + newPrice, 2), newPrice)),
			`PZR35` = IF(Surcharge, ROUND(`PZR35` * newPrice, 2), IF (Additional, ROUND(`PZR35` + newPrice, 2), newPrice)),
			`PZR36` = IF(Surcharge, ROUND(`PZR36` * newPrice, 2), IF (Additional, ROUND(`PZR36` + newPrice, 2), newPrice)),
			`PZR37` = IF(Surcharge, ROUND(`PZR37` * newPrice, 2), IF (Additional, ROUND(`PZR37` + newPrice, 2), newPrice)),
			`PZR38` = IF(Surcharge, ROUND(`PZR38` * newPrice, 2), IF (Additional, ROUND(`PZR38` + newPrice, 2), newPrice)),
			`PZR39` = IF(Surcharge, ROUND(`PZR39` * newPrice, 2), IF (Additional, ROUND(`PZR39` + newPrice, 2), newPrice)),
			`PZR40` = IF(Surcharge, ROUND(`PZR40` * newPrice, 2), IF (Additional, ROUND(`PZR40` + newPrice, 2), newPrice)),
			`PZR41` = IF(Surcharge, ROUND(`PZR41` * newPrice, 2), IF (Additional, ROUND(`PZR41` + newPrice, 2), newPrice)),
			`PZR42` = IF(Surcharge, ROUND(`PZR42` * newPrice, 2), IF (Additional, ROUND(`PZR42` + newPrice, 2), newPrice)),
			`PZR43` = IF(Surcharge, ROUND(`PZR43` * newPrice, 2), IF (Additional, ROUND(`PZR43` + newPrice, 2), newPrice)),
			`PZR44` = IF(Surcharge, ROUND(`PZR44` * newPrice, 2), IF (Additional, ROUND(`PZR44` + newPrice, 2), newPrice)),
			`PZR45` = IF(Surcharge, ROUND(`PZR45` * newPrice, 2), IF (Additional, ROUND(`PZR45` + newPrice, 2), newPrice)),
			`PZR46` = IF(Surcharge, ROUND(`PZR46` * newPrice, 2), IF (Additional, ROUND(`PZR46` + newPrice, 2), newPrice)),
			`PZR47` = IF(Surcharge, ROUND(`PZR47` * newPrice, 2), IF (Additional, ROUND(`PZR47` + newPrice, 2), newPrice)),
			`PZR48` = IF(Surcharge, ROUND(`PZR48` * newPrice, 2), IF (Additional, ROUND(`PZR48` + newPrice, 2), newPrice)),
			`PZR49` = IF(Surcharge, ROUND(`PZR49` * newPrice, 2), IF (Additional, ROUND(`PZR49` + newPrice, 2), newPrice)),
			`PZR50` = IF(Surcharge, ROUND(`PZR50` * newPrice, 2), IF (Additional, ROUND(`PZR50` + newPrice, 2), newPrice)),
			`PZR51` = IF(Surcharge, ROUND(`PZR51` * newPrice, 2), IF (Additional, ROUND(`PZR51` + newPrice, 2), newPrice)),
			`PZR52` = IF(Surcharge, ROUND(`PZR52` * newPrice, 2), IF (Additional, ROUND(`PZR52` + newPrice, 2), newPrice)),
			`PZR53` = IF(Surcharge, ROUND(`PZR53` * newPrice, 2), IF (Additional, ROUND(`PZR53` + newPrice, 2), newPrice))
		WHERE 
			`123_StoFZ`.`Paechter` = Paechter AND
			`123_StoFZ`.`HS` = HS AND
			`123_StoFZ`.`US` != 'NZ' AND
			`123_StoFZ`.`Netz` = 0
		;
END //
DELIMITER ;
/*CALL `updateStoPrices`(368, 1.15, 'VI', TRUE, FALSE);*/
CALL `updateStoPrices`(118, 30.00, 'VI', FALSE, FALSE);
CALL `updateStoPrices`(449, 14.286, 'VD', FALSE, TRUE);
DROP PROCEDURE IF EXISTS `updateStoPrices`;

/*COMMIT;*/

