# Bazy danych i zarządzanie informacją
# WPPT, Informatyka 2009
# 
# Tue, 17 Nov 2009 09:46:12 +0100
#
# Bartosz Chodorowski <bartosz.chodorowski.pwr.wroc.pl>
#
# Zadania zostały rozwiązane w języky MySQL.
#


# Lista 2, zadanie 01
#
DELIMITER $$

DROP PROCEDURE IF EXISTS diff;
CREATE PROCEDURE diff(IN date1 DATE, IN date2 DATE,
	OUT d INT, out h INT, out s INT)
BEGIN
	SET s = UNIX_TIMESTAMP(date2) - UNIX_TIMESTAMP(date1);
	SET h = s/3600;
	SET d = h/24;
END
$$

DELIMITER ;


# Lista 2, zadanie 02
#
DELIMITER $$

DROP PROCEDURE IF EXISTS oblicz_kwartaly;
CREATE PROCEDURE oblicz_kwartaly()
BEGIN

	(
	SELECT 1 AS kwartal, 2004 AS rok, sum(wynik) AS wynik FROM rok2004
		WHERE miesiac=1 OR miesiac=2 OR miesiac=3
	)
	UNION
	(
	SELECT 2 as kwartal, 2004 AS rok, sum(wynik) AS wynik FROM rok2004
		WHERE miesiac=4 OR miesiac=5 OR miesiac=6
	)
	UNION
	(
	SELECT 3 AS kwartal, 2004 AS rok, sum(wynik) AS wynik FROM rok2004
		WHERE miesiac=7 OR miesiac=8 OR miesiac=9
	)
	UNION
	(
	SELECT 4 as kwartal, 2004 AS rok, sum(wynik) AS wynik FROM rok2004
		WHERE miesiac=10 OR miesiac=11 OR miesiac=12
	)
	UNION
	(
	SELECT 1 AS kwartal, 2005 AS rok, sum(wynik) AS wynik FROM rok2005
		WHERE miesiac=1 OR miesiac=2 OR miesiac=3
	)
	UNION
	(
	SELECT 2 as kwartal, 2005 AS rok, sum(wynik) AS wynik FROM rok2005
		WHERE miesiac=4 OR miesiac=5 OR miesiac=6
	)
	UNION
	(
	SELECT 3 AS kwartal, 2005 AS rok, sum(wynik) AS wynik FROM rok2005
		WHERE miesiac=7 OR miesiac=8 OR miesiac=9
	)
	UNION
	(
	SELECT 4 as kwartal, 2005 AS rok, sum(wynik) AS wynik FROM rok2005
		WHERE miesiac=10 OR miesiac=11 OR miesiac=12
	)
	;
END
$$

DELIMITER ;


# Lista 2, zadanie 03
#
DELIMITER $$

DROP PROCEDURE IF EXISTS computeMax;
CREATE PROCEDURE computeMax()
BEGIN

	DROP TABLE IF EXISTS t1;
	CREATE TEMPORARY TABLE t1 AS
	SELECT tabela AS c1, kolumna AS c2 FROM lista;

	SET @i = 0;
	WHILE @i<(SELECT COUNT(*) FROM t1)
	DO
		SET @ins = CONCAT('SELECT c1 INTO @s FROM t1 LIMIT ',
			@i, ',1');
		PREPARE stmt1 FROM @ins; 
		EXECUTE stmt1;
		DEALLOCATE PREPARE stmt1;

		SET @ins2 = CONCAT('SELECT c2 INTO @s2 FROM t1 LIMIT ',
			@i, ',1');
		PREPARE stmt2 FROM @ins2; 
		EXECUTE stmt2;
		DEALLOCATE PREPARE stmt2;

		SET @ins3 = CONCAT('SELECT MAX(', @s2 ,') INTO @s3 FROM ',
			@s);
		PREPARE stmt3 FROM @ins3;
		EXECUTE stmt3;
		DEALLOCATE PREPARE stmt3;

		SET @ins4 = CONCAT('UPDATE lista SET maximum=', @s3,
			' WHERE tabela=''', @s, ''' AND kolumna=''', @s2,'''');
		PREPARE stmt4 FROM @ins4;
		EXECUTE stmt4;
		DEALLOCATE PREPARE stmt4;

		SET @i = @i+1;
	END WHILE;
 
	DROP TABLE IF EXISTS t1;
END
$$

DELIMITER ;


# Lista 2, zadanie 05
#
DELIMITER $$

DROP TRIGGER IF EXISTS insertTrigger;
CREATE TRIGGER insertTrigger BEFORE INSERT ON obroty
FOR EACH ROW
BEGIN
	SET @a = NULL;
	SELECT Adres INTO @a FROM sklep WHERE NEW.NazwaSklepu = sklep.Nazwa;

	IF @a IS NOT NULL
	THEN
		IF @a != NEW.Adres
		THEN
			INSERT INTO sklep(error) VALUES ('error');
		END IF;
	ELSE
		INSERT INTO sklep VALUES (NEW.NazwaSklepu, NEW.Adres);
	END IF;
	


	SET @a = NULL;
	SELECT Producent INTO @a FROM towar WHERE NEW.NazwaTowaru = towar.Nazwa;

	IF @a IS NOT NULL
	THEN
		IF @a != NEW.Producent
		THEN
			INSERT INTO towar(error) VALUES ('error');
		END IF;
	ELSE
		INSERT INTO towar VALUES (NEW.NazwaTowaru, NEW.Producent);
	END IF;


	INSERT INTO sprzedaz VALUES (NEW.NazwaSklepu, NEW.NazwaTowaru,
		NEW.Data, NEW.Cena);
END;



DROP TRIGGER IF EXISTS updateTrigger;
CREATE TRIGGER updateTrigger BEFORE UPDATE ON obroty
FOR EACH ROW
BEGIN
	INSERT INTO sprzedaz(error) VALUES ('error');
END;



DROP TRIGGER IF EXISTS deleteTrigger;
CREATE TRIGGER deleteTrigger BEFORE DELETE ON obroty
FOR EACH ROW
BEGIN
	DELETE FROM sprzedaz
		WHERE NazwaSklepu=OLD.NazwaSklepu
			AND NazwaTowaru=OLD.NazwaTowaru
			AND Data=OLD.Data
			AND Cena=OLD.CENA
		LIMIT 1;

	IF NOT EXISTS (SELECT * FROM sprzedaz WHERE NazwaSklepu=OLD.NazwaSklepu)
	THEN
		DELETE FROM sklep WHERE Nazwa=OLD.NazwaSklepu;
	END IF;

	IF NOT EXISTS (SELECT * FROM sprzedaz WHERE NazwaTowaru=OLD.NazwaTowaru)
	THEN
		DELETE FROM towar WHERE Nazwa=OLD.NazwaTowaru;
	END IF;
END;



DELIMITER ;