Stored procedure, holidays
Hello, I've been working on a small project to check the amount of day's people take a leave of absence from their work. now i have created an execution procedure that stores data and put's it in a cursor loop(which is used to grab multiple entries in the database. (after my explaination and question i will show both.)
and a procedure that used the data given by the execution procedure to create a check that;
- checks what the amount of days is.
- checks what day it falls in.
- checks if it has any days that fall under weekend and does not count them as leave of absence.
- checks if any holidays fall between those dates. (our national holidays)
- shows all types of data after all calculations.
the problem is that it does not register the holidays and does not use them. so if a holiday is in the leave of absence time it does not make it a free day.
can someone help me with this?
Here are the two procedures.
PS: i know the procedure can be done much easier, yet this is the way i learned it to myself.
CREATE DEFINER=`root`@`localhost` PROCEDURE `Exec A_Check`(IN `w_id` int)
BEGIN
DECLARE noMoreRows INT DEFAULT 1;
DECLARE v_id INT;
DECLARE cur CURSOR FOR SELECT idverlofdagen FROM verlofdagen WHERE werknemer_idwerknemer = w_id;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET noMoreRows = 0;
OPEN cur;
testLoop: LOOP
FETCH cur INTO v_id;
IF noMoreRows = 0 THEN
CLOSE cur;
LEAVE testLoop;
END IF;
CALL A_Check(v_id);
END LOOP testLoop;
END$$
-----------------------------------------------------
CREATE DEFINER=`root`@`localhost` PROCEDURE `A_check`(IN `v_id` integer)
BEGIN
#Definieren alle variabelen.
DECLARE diff INT DEFAULT 0;
DECLARE total INT DEFAULT 0;
DECLARE daysoff INT DEFAULT 0;
DECLARE dayow int;
DECLARE dayown VARCHAR(255);
DECLARE dayow2 int;
DECLARE dayown2 VARCHAR(255);
DECLARE firstdays DATE;
DECLARE tester VARCHAR(255);
DECLARE namen VARCHAR(255);
DECLARE lastdays DATE;
DECLARE totaaldagen INT DEFAULT 0;
DECLARE vrijedagen INT DEFAULT 0;
#Het vastzetten van de standaardwaarden, dit kan zijn een database variabele of een standaard waarde die later ingevult moet worden.
SET @lastdays = (SELECT eindD FROM verlofdagen WHERE idverlofdagen = v_id);
SET @namen = (SELECT naam FROM werknemer, verlofdagen WHERE werknemer.idwerknemer = verlofdagen.werknemer_idwerknemer AND verlofdagen.idverlofdagen = v_id);
SET @diff = (SELECT DATEDIFF(eindD, startD) AS diffdate FROM verlofdagen WHERE idverlofdagen = v_id);
SET @firstdays = (SELECT startD FROM verlofdagen WHERE idverlofdagen = v_id);
SET @dayow = DAYOFWEEK(@firstdays);
SET @dayow2 = DAYOFWEEK(@lastdays);
SET @total = 0;
SET @daysoff = 1;
SET @tester = '';
SET @totaaldagen = (SELECT saldo FROM werknemer, verlofdagen WHERE werknemer.idwerknemer = verlofdagen.werknemer_idwerknemer AND verlofdagen.idverlofdagen = v_id);
SELECT COUNT(*) INTO vrijedagen FROM feestdagen WHERE feestD BETWEEN firstdays AND lastdays AND dayow <5;
#Bepaaling voor de eerste dag van de Vakantieaanvraag.
IF @dayow = 1 THEN
SET @dayown = 'Maandag';
ELSEIF @dayow = 2 THEN
SET @dayown = 'Dinsdag';
ELSEIF @dayow = 3 THEN
SET @dayown = 'Woensdag';
ELSEIF @dayow = 4 THEN
SET @dayown = 'Donderdag';
ELSEIF @dayow = 5 THEN
SET @dayown = 'Vrijdag';
ELSEIF @dayow = 6 THEN
SET @dayown = 'Zaterdag';
ELSE
SET @dayown = 'Zondag';
END IF;
#Bepaaling voor de laatste dag van de Vakantieaanvraag.
IF @dayow2 = 1 THEN
SET @dayown2 = 'Maandag';
ELSEIF @dayow2 = 2 THEN
SET @dayown2 = 'Dinsdag';
ELSEIF @dayow2 = 3 THEN
SET @dayown2 = 'Woensdag';
ELSEIF @dayow2 = 4 THEN
SET @dayown2 = 'Donderdag';
ELSEIF @dayow2 = 6 THEN
SET @dayown2 = 'Vrijdag';
ELSEIF @dayow2 = 7 THEN
SET @dayown2 = 'Zaterdag';
ELSE
SET @dayown2 = 'Zondag';
END IF;
#begin van de loop om te bepalen welke dagen er doordeweeks zijn en bij het vrije dagen vakje
myloop: LOOP
IF @dayow= 1 THEN
SET @tester = CONCAT(@tester,@dayow,',');
SET @total = @total + 1;
SET @dayow = @dayow + 1;
ELSEIF @dayow= 7 THEN
SET @tester = CONCAT(@tester,@dayow, ',');
SET @total = @total + 1;
SET @dayow = 1;
ELSE
SET @tester = CONCAT(@tester,@dayow,',');
SET @total = @total + 1;
SET @daysoff = @daysoff + 1;
SET @dayow = @dayow + 1;
END IF;
IF @total = @diff THEN
LEAVE myloop;
END IF;
END LOOP myloop;
SET @totaaldagen = @totaaldagen - @daysoff;
SET @daysoff = @daysoff - @vrijedagen;
SELECT @namen AS Naam, @daysoff AS "Opgenomen dagen", @firstdays AS "Start datum", @dayown AS "Start Dag" , @lastdays AS "Eind datum", @dayown2 AS "Eind Dag", @totaaldagen AS "Vrije Dagen Over";
END$$
(the part that doesn't work is this one):
SELECT COUNT(*) INTO vrijedagen FROM feestdagen WHERE feestD BETWEEN firstdays AND lastdays AND dayow <5;