SET SESSION sql_mode = '';

DELETE FROM `wp_Sto`
	WHERE `wp_Sto`.`Netz` = '-1';

SET @StoID = (SELECT `wp_Sto`.`StoID` FROM `wp_Sto` ORDER BY `wp_Sto`.`StoID` DESC LIMIT 1);

/* Normal net locations */
INSERT INTO `wp_Sto` 
	SELECT
		(@StoID := @StoID + 1) AS `StoID`, 
		`wp_Sto`.`Paechter`,
		`wp_Sto`.`GJ`,
		`wp_Sto`.`Netz` AS `StoNr`,
		'1' AS `StoV`,
		'1' AS `StoB`,
		SUM(`wp_Sto`.`StellenOrt`) AS `StellenOrt`,
		`wp_Sto`.`Versand`,
		`wp_Sto`.`Staat`,
		`wp_Sto`.`OrtNr`,
		`wp_Ort`.`PLZ`,
		`wp_Sto`.`HS`,
		`wp_Sto`.`US`,
		`wp_Sto`.`Belegung`,
		`wp_Sto`.`Preis`,
		MAX(`wp_Sto`.`Beleuchtung`) AS `Beleuchtung`,
		'0' AS `VMNr`,
		'' AS `Ortsteil`,
		`wp_dbo_US`.`Bezeichnung` AS `Standort`,
		'-1' AS `Netz`,
		'' AS `StatusIst`,
		'' AS `StatusNeu`,
		'0' AS `StatusWechsel`,
		CONCAT_WS('-', `wp_Sto`.`HS`, `wp_Sto`.`US`) AS `FotoName`,
		'' AS `LKlasse1`,
		'0' AS `LWert1`,
		'' AS `BQuelle1`,
		'' AS `LKlasse2`,
		'0' AS `LWert2`,
		'' AS `BQuelle2`,
		'' AS `LKlasse3`,
		'0' AS `LWert3`,
		'' AS `BQuelle3`,
		'' AS `LKlasse4`,
		'0' AS `LWert4`,
		'' AS `BQuelle4`,
		'0' AS `MIALA`,
		'0' AS `MIABA`,
		'0' AS `UTMLA`,
		'0' AS `UTMBA`,
		GROUP_CONCAT(CONCAT_WS('|', TRIM(`wp_Sto`.`StoNr`), TRIM(`wp_Sto`.`StoV`), TRIM(`wp_Sto`.`StoB`), TRIM(REPLACE(`wp_Sto`.`Standort`, '|', '/')), TRIM(`wp_Sto`.`FotoName`), TRIM(`Beleuchtung`), TRIM(`wp_Sto`.`VMNr`), TRIM(REPLACE(`wp_Sto`.`Ortsteil`, '|', '/')), TRIM(`wp_Sto`.`UTMLA`), TRIM(`wp_Sto`.`UTMBA`), TRIM(REPLACE(`wp_Sto`.`Textfeld`, '|', '/'))) ORDER BY `wp_Sto`.`Textfeld` SEPARATOR "\n") AS `Textfeld`,
		AVG(NULLIF(`Bewertung`, 0)) AS `Bewertung`
	FROM `wp_Sto`
		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`)
	WHERE 
		`wp_Sto`.`Netz` NOT IN('0', '-1') AND
		`wp_Sto`.`HS` NOT IN('TA') AND
		`wp_Sto`.`StatusIst` = '' AND
		`wp_Sto`.`StatusNeu` = '' AND
		`wp_Sto`.`Statuswechsel` = '0'
	GROUP BY `wp_Sto`.`GJ`, `wp_Sto`.`OrtNr`, `wp_Sto`.`Paechter`, `wp_Sto`.`Netz`, `wp_Sto`.`HS`, `wp_Sto`.`US`;

/* Net locations without net flag */
DROP PROCEDURE IF EXISTS `insertAL`;
DELIMITER //
CREATE PROCEDURE `insertAL`(format INT, type VARCHAR(3), utype VARCHAR(255))
BEGIN
	INSERT INTO `wp_Sto` 
		SELECT
			(@StoID := @StoID + 1) AS `StoID`, 
			`wp_Sto`.`Paechter`,
			`wp_Sto`.`GJ`,
			`wp_Sto`.`OrtNr` AS `StoNr`,
			format AS `StoV`,
			format AS `StoB`,
			COUNT(`wp_Sto`.`StoNr`) AS `StellenOrt`,
			`wp_Sto`.`Versand`,
			`wp_Sto`.`Staat`,
			`wp_Sto`.`OrtNr`,
			`wp_Ort`.`PLZ`,
			`wp_Sto`.`HS`,
			`wp_Sto`.`US`,
			`wp_Sto`.`Belegung`,
			format * `wp_Sto`.`Preis` AS `Preis`,
			MAX(`wp_Sto`.`Beleuchtung`) AS `Beleuchtung`,
			'0' AS `VMNr`,
			'' AS `Ortsteil`,
			MAX(`wp_dbo_US`.`Bezeichnung`) AS `Standort`,
			'-1' AS `Netz`,
			'' AS `StatusIst`,
			'' AS `StatusNeu`,
			'0' AS `StatusWechsel`,
			CONCAT_WS('-', `wp_Sto`.`HS`, format) AS `FotoName`,
			'' AS `LKlasse1`,
			'0' AS `LWert1`,
			'' AS `BQuelle1`,
			'' AS `LKlasse2`,
			'0' AS `LWert2`,
			'' AS `BQuelle2`,
			'' AS `LKlasse3`,
			'0' AS `LWert3`,
			'' AS `BQuelle3`,
			'' AS `LKlasse4`,
			'0' AS `LWert4`,
			'' AS `BQuelle4`,
			'0' AS `MIALA`,
			'0' AS `MIABA`,
			'0' AS `UTMLA`,
			'0' AS `UTMBA`,
			GROUP_CONCAT(CONCAT_WS('|', TRIM(`wp_Sto`.`StoNr`), TRIM(`wp_Sto`.`StoV`), TRIM(`wp_Sto`.`StoB`), TRIM(REPLACE(`wp_Sto`.`Standort`, '|', '/')), TRIM(`wp_Sto`.`FotoName`), TRIM(`Beleuchtung`), TRIM(`wp_Sto`.`VMNr`), TRIM(REPLACE(`wp_Sto`.`Ortsteil`, '|', '/')), TRIM(`wp_Sto`.`UTMLA`), TRIM(`wp_Sto`.`UTMBA`), TRIM(REPLACE(`wp_Sto`.`Textfeld`, '|', '/'))) ORDER BY `wp_Sto`.`Textfeld` SEPARATOR "\n") AS `Textfeld`,
			AVG(NULLIF(`Bewertung`, 0)) AS `Bewertung`
		FROM `wp_Sto`
			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`)
		WHERE 
			`wp_Sto`.`Netz` = '0' AND
			`wp_Sto`.`HS` = type AND
			`wp_Sto`.`US` REGEXP utype AND
			`wp_Sto`.`StatusIst` = '' AND
			`wp_Sto`.`StatusNeu` = '' AND
			`wp_Sto`.`Statuswechsel` = '0'
		GROUP BY `wp_Sto`.`GJ`, `wp_Sto`.`OrtNr`, `wp_Sto`.`Paechter`, `wp_Sto`.`Belegung`, `wp_Sto`.`Preis`;
END //
DELIMITER ;
CALL `insertAL`(1, 'AL', '.*');
CALL `insertAL`(2, 'AL', 'ST|WH');
CALL `insertAL`(4, 'AL', 'ST|WH');
CALL `insertAL`(6, 'AL', 'ST|WH');
DROP PROCEDURE IF EXISTS `insertAL`;

/* Set "do not use" flag for net, clock and "other" locations for stroeer */
UPDATE `wp_Sto`
	SET `wp_Sto`.`Statuswechsel` = '-1'
	WHERE
		(
			(
				`wp_Sto`.`Netz` NOT IN('0', '-1') AND
				`wp_Sto`.`HS` NOT IN('TA') /*OR
				(`wp_Sto`.`Netz` = '0' AND `wp_Sto`.`HS` = 'AL')*/
			) AND
			`wp_Sto`.`StatusIst` = '' AND
			`wp_Sto`.`StatusNeu` = '' AND
			`wp_Sto`.`Statuswechsel` = '0'
		) OR (
			`wp_Sto`.`Paechter` IN('368', '400') AND (
				`wp_Sto`.`HS` = 'US' /*OR (
					`wp_Sto`.`HS` = 'SO' AND `wp_Sto`.`US` IN('GL', 'S4')
				)*/
			) AND
			`wp_Sto`.`StatusIst` = '' AND
			`wp_Sto`.`StatusNeu` = '' AND
			`wp_Sto`.`Statuswechsel` = '0'
		);

/* Update clock and "other" locations for stroeer */
INSERT INTO `wp_Sto` 
	SELECT
		(@StoID := @StoID + 1) AS `StoID`,
		`wp_Sto`.`Paechter`,
		`wp_Sto`.`GJ`,
		`wp_Sto`.`StoNr`,
		'1' AS `StoV`,
		'1' AS `StoB`,
		COUNT(DISTINCT `wp_Sto`.`StoV`) AS `StellenOrt`,
		`wp_Sto`.`Versand`,
		`wp_Sto`.`Staat`,
		`wp_Sto`.`OrtNr`,
		`wp_Sto`.`PLZ`,
		`wp_Sto`.`HS`,
		`wp_Sto`.`US`,
		`wp_Sto`.`Belegung`,
		`wp_Sto`.`Preis`,
		`wp_Sto`.`Beleuchtung`,
		`wp_Sto`.`VMNr`,
		`wp_Sto`.`Ortsteil`,
		`wp_Sto`.`Standort`,
		`wp_Sto`.`Netz`,
		`wp_Sto`.`StatusIst`,
		`wp_Sto`.`StatusNeu`,
		'0' AS `StatusWechsel`,
		`wp_Sto`.`FotoName`,
		`wp_Sto`.`LKlasse1`,
		`wp_Sto`.`LWert1`,
		`wp_Sto`.`BQuelle1`,
		`wp_Sto`.`LKlasse2`,
		`wp_Sto`.`LWert2`,
		`wp_Sto`.`BQuelle2`,
		`wp_Sto`.`LKlasse3`,
		`wp_Sto`.`LWert3`,
		`wp_Sto`.`BQuelle3`,
		`wp_Sto`.`LKlasse4`,
		`wp_Sto`.`LWert4`,
		`wp_Sto`.`BQuelle4`,
		`wp_Sto`.`MIALA`,
		`wp_Sto`.`MIABA`,
		`wp_Sto`.`UTMLA`,
		`wp_Sto`.`UTMBA`,
		GROUP_CONCAT(DISTINCT `wp_Sto`.`StoV` ORDER BY `wp_Sto`.`StoV` SEPARATOR ",") AS `Textfeld`,
		AVG(NULLIF(`Bewertung`, 0)) AS `Bewertung`
	FROM `wp_Sto`
	WHERE 
		`wp_Sto`.`Paechter` IN('368', '400') AND (
			`wp_Sto`.`HS` = 'US' /*OR (
				`wp_Sto`.`HS` = 'SO' AND `wp_Sto`.`US` IN('GL', 'S4')
			)*/) AND
		`wp_Sto`.`StatusIst` = '' AND
		`wp_Sto`.`StatusNeu` = '' AND
		`wp_Sto`.`Statuswechsel` = '-1'
	GROUP BY `wp_Sto`.`Paechter`, `wp_Sto`.`GJ`, `wp_Sto`.`StoNr`, `wp_Sto`.`OrtNr`, `wp_Sto`.`HS`, `wp_Sto`.`US`;

/* Set stretchboard US */
UPDATE `wp_Sto`
	SET `wp_Sto`.`US` = 'SB', `wp_Sto`.`LKlasse1` = `wp_Sto`.`US`
	WHERE 
		`wp_Sto`.`Paechter` = '135' AND
		`wp_Sto`.`StoNr` LIKE '40%' AND
		LENGTH(`wp_Sto`.`StoNr`) > '5' AND
		`wp_Sto`.`US` != 'PB' AND
		`wp_Sto`.`Netz` = '0';

/* Delete dummy locations */
DELETE FROM `wp_Sto`
	WHERE 
		`wp_Sto`.`HS` = 'GF2' AND
		`wp_Sto`.`US` IN('CS', 'CI', 'CP', 'PB', 'SB');

