/*1.	Stored procedure η οποία δέχεται ως είσοδο τον κωδικό μιας επιχείρησης και θα εκτυπώνει την επωνυμία της και τον τίτλο της,  την επωνυμία  και τον τίτλο της γονικής επιχείρησης κοκ μέχρι τη ρίζα του δέντρου των επιχειρήσεων.*/

DROP PROCEDURE IF EXISTS getPathToRoot;
DELIMITER $
CREATE PROCEDURE getPathToRoot(leafId INT)
BEGIN
	DECLARE bname VARCHAR(250);
	DECLARE btitle VARCHAR(250);
	DECLARE parentId INT;

	SELECT branch INTO parentId FROM business WHERE id=leafId;
	SELECT title INTO btitle FROM business WHERE id=leafId;
	SELECT businessname INTO bname FROM business WHERE id=leafId;

	WHILE parentId IS NOT NULL
	DO
		SELECT bname, btitle;
		SELECT businessname INTO bname FROM business WHERE id=parentId;
		SELECT title INTO btitle FROM business WHERE id=parentId;
		SELECT branch INTO parentId FROM business WHERE id=parentId;

	END WHILE;

	SELECT bname, btitle;
END$
DELIMITER ;
CALL getPathToRoot(54879);

/* 2. Stored procedure η οποία δέχεται ως είσοδο τον κωδικό μιας ΔΟΥ, εκτυπώνει τον κωδικό, την επωνυμία και το διακριτικό τίτλο κάθε επιχείρησης χωριστά από αυτή τη ΔΟΥ και για κάθε επωνυμία εκτυπώνει τη λίστα των ελεγκτών που την έχουν ελέγξει (ονοματεπώνυμο, ΑΦΜ, Σ.Ο.Ε.Λ., τύπος).*/

DROP PROCEDURE IF EXISTS getAuditorsPerBusiness;
DELIMITER $
CREATE PROCEDURE getAuditorsPerBusiness(doyId INT)
BEGIN
	DECLARE bid INT;
	DECLARE not_found INT;
	
	DECLARE bcursor CURSOR FOR
	SELECT id FROM business 
	WHERE doy_id=doyId;
	
	DECLARE CONTINUE HANDLER FOR NOT FOUND
	SET not_found=1;
	
	SET not_found=0;
	OPEN bcursor;
	
	REPEAT
		FETCH bcursor INTO bid;
		IF(not_found=0)
		THEN
			SELECT id, businessname, title FROM business WHERE id=bid;
			SELECT name, afm, soel, type
			FROM auditor_check
			INNER JOIN auditor ON afm_auditor=person_afm
			INNER JOIN person ON person_afm=afm
			WHERE bus_id=bid;
		END IF;
	UNTIL(not_found=1)
	END REPEAT;
	
	CLOSE bcursor;
END$
DELIMITER ;
CALL getAuditorsPerBusiness(3);



/* 3. Stored procedure η οποία να δέχεται ως είσοδο τον κωδικό μιας επιχείρησης για να την διαγράψει. Πριν πραγματοποιήσει την διαγραφή, μεταφέρει τα υποκαταστήματα αυτής της επιχείρησης στη γονική της επιχείρηση.*/

DROP PROCEDURE IF EXISTS deleteBusiness;

DELIMITER $

CREATE PROCEDURE deleteBusiness(IN bid INT)
BEGIN
	DECLARE parent INT;
	DECLARE child INT;
	DECLARE not_found INT;
	
	DECLARE bcursor CURSOR FOR
	SELECT id FROM business WHERE branch=bid;
	
	DECLARE CONTINUE HANDLER FOR NOT FOUND
	SET not_found=1;
	
	SELECT branch INTO parent FROM business WHERE id=bid;
	
	SET not_found=0;
	OPEN bcursor;
	
	REPEAT
		FETCH bcursor INTO child;
		IF(not_found=0)
		THEN
			UPDATE business SET branch=parent WHERE id=child;
		END IF;
	UNTIL(not_found=1)
	END REPEAT;
	
	DELETE FROM business WHERE id=bid;
	
END$

DELIMITER ;

SELECT child.id AS ChildId, parent.id AS ParentId
FROM business AS child
LEFT JOIN business AS parent ON child.branch=parent.id;
CALL deleteBusiness(54876);
SELECT child.id AS ChildId, parent.id AS ParentId
FROM business AS child
LEFT JOIN business AS parent ON child.branch=parent.id;

/* 4. Stored Procedure η οποία καλείται με παράμετρο τον κωδικό μιας επιχείρησης, ελέγχει όλα τα ποσοστά μετοχών που έχουν εισαχθεί για αυτή την επιχείρηση και εξετάζει αν έχουν άθροισμα λιγότερο ή ίσο με 100%. Αν όχι, τότε επιχειρεί να διορθώσει τα ποσοστά διαγράφοντας την καταλληλότερη εγγραφή μετοχών. Ως καταλληλότερη εγγραφή θεωρείται αυτή της οποίας η διαγραφή θα φέρει το ποσοστό όσο πιο κοντά στο 100% γίνεται, ανεξάρτητα με το αν είναι πάνω ή κάτω απ' το 100%. Αν το συνολικό ποσοστό δεν πέσει κάτω από το 100% με τη διαγραφή, τότε κάνει και δεύτερη, τρίτη κοκ έως ότου το ποσοστό να είναι μικρότερο ή ίσο του 100%.
Σημείωση: Το ποσοστό κατά σύμβαση αποθηκεύεται στο percentage στη μορφή μέχρι την εκατοντάδα. Δηλαδή 45.00 για το 45% και όχι 0.45.
*/ 

DROP PROCEDURE IF EXISTS handlePercentages;
DELIMITER $

CREATE PROCEDURE handlePercentages(bid INT)
BEGIN
	DECLARE totalPerc FLOAT(5,2);
	DECLARE afmToErase CHAR(9);
	DECLARE currPerc FLOAT(5,2);
	DECLARE currAfm CHAR(9);
	DECLARE currDiff FLOAT(5,2);
	DECLARE minDiff FLOAT(5,2);
	
	DECLARE not_found INT;
	DECLARE share_cursor CURSOR FOR
	SELECT afm_shareholder, percentage FROM share WHERE  bus_id=bid;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET not_found = 1;
	
	SET totalPerc=0.0;
	SELECT SUM(percentage) INTO totalPerc
	FROM share WHERE bus_id=bid;
		
	SELECT totalPerc as 'Arxiko Synoliko Pososto';
	
	IF(totalPerc>100.0) THEN
		
		WHILE(totalPerc>100.0) DO
			SET minDiff = totalPerc+1;
			SET afmToErase=NULL;
			
			SET not_found=0;
			OPEN share_cursor;
	
			REPEAT
				SET currAfm=NULL;
				SET currPerc=0.0;
				FETCH share_cursor INTO currAfm, currPerc;
				IF(not_found=0)
				THEN
					SET currDiff = ABS(100.0 - (totalPerc-currPerc));
					IF( currDiff < minDiff) THEN
						SET afmToErase = currAfm;
						SET minDiff = currDiff;
						/*SELECT afmToErase as 'Selected Afm',currPerc as 'Pososto' ,minDiff as 'Diafora';*/
					END IF;
				END IF;
			UNTIL(not_found=1)
			END REPEAT; 
	
			CLOSE share_cursor;
			
			SELECT percentage INTO currPerc FROM share WHERE afm_shareholder=afmToErase AND bus_id=bid;
			SELECT afmToErase as 'Deleting AFM', currPerc as 'Deleting Percentage';
			
			DELETE FROM share WHERE afm_shareholder=afmToErase AND bus_id=bid;
			
			SELECT SUM(percentage) INTO totalPerc
			FROM share WHERE bus_id=bid;
			SELECT totalPerc as 'Neo Synoliko Pososto';
		END WHILE;
	END IF;
	
END$

DELIMITER ;

CALL handlePercentages(18777);




/* 1. Trigger ο οποίος κατά την εισαγωγή ενός φυσικού προσώπου ελέγχει αν το email είναι σε σωστή μορφή (<κάτι>@<κάτιάλλο>.<ext>). Αν όχι, αποτρέπει την εισαγωγή.*/

DROP TRIGGER IF EXISTS checkEmail;

DELIMITER $

CREATE TRIGGER checkEmail BEFORE INSERT ON person
FOR EACH ROW 
BEGIN
	IF(NEW.email IS NOT NULL AND NEW.email NOT LIKE '%_@%_.%_')
	THEN
		INSERT INTO doy(name) VALUES (NULL);
	END IF;
END$

DELIMITER ;

/* 2. Trigger ο οποίος κατά την εισαγωγή μιας επιχείρησης σε ένα επιμελητήριο ελέγχει εαν ο τίτλος είναι μοναδικός για τις επιχειρήσεις του επιμελητηρίου στο οποίο ανήκει. Αν δεν είναι μοναδικός, αποτρέπει την εισαγωγή.*/
DROP TRIGGER IF EXISTS checkBusinessTitle;

DELIMITER $

CREATE TRIGGER checkBusinessTitle BEFORE INSERT ON belongs
FOR EACH ROW
BEGIN
	DECLARE sameTitleCount INT;
	SET sameTitleCount=0;
	
	SELECT COUNT(*) INTO sameTitleCount 
	FROM business
	INNER JOIN belongs ON belongs.bus_id=business.id
	WHERE ch_name=NEW.ch_name
	AND business.title IN (
		SELECT title FROM business 
		WHERE id=NEW.bus_id
	);
	
	IF(sameTitleCount>0) THEN
		INSERT INTO chamber(name) VALUES (NULL);
	END IF;
END$
DELIMITER ;


INSERT INTO business VALUES 
('Tzatzadaki Maria', 'Tzatzadakis','1998-08-04 00:00',31202,'Patra',20100,180,NULL,3);
INSERT INTO belongs VALUES(31202,'Epimelitirio Axaias');

/* 3. Trigger ο οποίος κατά την εισαγωγή των πληροφοριών ενός ελέγχου από έναν ελεγκτή, ελέγχει εάν η ημερομηνία έναρξης και η ημερομηνία λήξης απέχουν τουλάχιστον μια εβδομάδα. Αν όχι, μεταβάλλει την ημερομηνία λήξης έτσι ώστε να απέχει ακριβώς μια εβδομάδα από την ημερομηνία έναρξης.

Σημείωση: Δείτε τις συναρτήσεις για Date και Time της MySQL (http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html)*/ 

DROP TRIGGER IF EXISTS checkAuditDates;

DELIMITER $
CREATE TRIGGER checkAuditDates BEFORE INSERT ON auditor_check
FOR EACH ROW
BEGIN
	DECLARE currDiff INT;
	
	SET currDiff = 0;
	
	SET currDiff=DATEDIFF(NEW.startdate,NEW.enddate);
	IF(currDiff<7) THEN
		SET NEW.enddate=NEW.startdate + INTERVAL 7 DAY;
	END IF;
END$
DELIMITER ;

SELECT * FROM auditor_check;
INSERT INTO auditor_check VALUES 
(31201,'200000333','2012-12-11 00:00','2012-12-13 00:00');