3rd one

use school_sport_clubs;
 drop procedure testProc;

 delimiter $$
 CREATE PROCEDURE  testProc(IN param int)
  out_block:
  BEGIN
   DECLARE res int;
   SET res = param;
       inner_block:
         BEGIN
           IF (res = 1)
	   THEN
	   LEAVE inner_block;
	   END IF;
       SELECT 'This will be excuted only if param is 0';
       END inner_block;
  SELECT 'End of program';
  END out_block;
  $$
  delimiter ;

call testProc(1);

select now();
select month(now());
select year(now());

use school_sport_clubs;
drop procedure if exists monthSalaryPayment;
delimiter |
create procedure monthSalaryPayment(IN monthOfPayment INT, in yearOFpayment INT, OUT success bit)
procLab: begin
    declare tempCoachSalary decimal;
    declare currentcoachSalary decimal;
    declare iterator int;
    declare countOfCoaches int;
    declare tempCoachId int;
    declare updatedSalaryCounter int;
    declare countOfCoachesWithoutMonthSalary int;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SELECT 'SQLException occured';

create temporary table tempCoach(
id int auto_increment primary key,
coach_id int not null,
month_salary int not null
)Engine = Memory;

INSERT INTO tempCoach(coach_id,month_salary)
    SELECT coaches.id, coaches.month_salary
    FROM coaches
    WHERE month_salary IS NOT NULL ;

    SELECT COUNT(*)
    INTO countOfCoaches
    FROM tempCoach;

    SET countOfCoachesWithoutMonthSalary = 0;
    set iterator = 1;
    WHILE(iterator >= 1 AND iterator <= countOfCoaches) # преброяваме колко са треньорите, които не са си полчуили заплатата все още.
	DO
	SELECT coach_id, month_salary
        INTO tempCoachId, tempCoachSalary
        FROM tempCoach
        where id = iterator;

        SELECT salaryAmount
        INTO  currentCoachSalary #има ли заплата вече преведена за този месец и тази година този треньор
        FROM salarypayments
        WHERE `month` = monthOfPayment
        AND `year` = yearOFpayment
        AND `coach_id` = tempCoachId;

    IF(IFNULL(currentCoachSalary,0) <= tempCoachSalary)
THEN
    SET countOfCoachesWithoutMonthSalary = countOfCoachesWithoutMonthSalary +1;
 END IF;
 SET iterator = iterator +1;
 end while;
IF(countOfCoachesWithoutMonthSalary = 0) # ако няма треньори, които си чакат превод на заплатата
THEN
   drop table tempCoach;
   set success = 0;
LEAVE procLab;
 ELSE set iterator = 1;
 SET updatedSalaryCounter = 0;
 START TRANSACTION;
 WHILE(iterator >= 1 AND iterator <= countOfCoaches)
	DO
	SELECT coach_id, month_salary
        INTO tempCoachId, tempCoachSalary
        FROM tempCoach
        where id = iterator;

        SELECT salaryAmount
        INTO  currentCoachSalary #има ли заплата вече преведена за този месец и тази година този треньор
        FROM salarypayments
        WHERE `month` = monthOfPayment
        AND `year` = yearOFpayment
        AND `coach_id` = tempCoachId;


	IF(IFNULL(currentCoachSalary,0) < tempCoachSalary)
	THEN
        INSERT INTO salarypayments(`coach_id`,`month`,`year`,`salaryAmount`,`dateOfPayment`)
VALUES (tempCoachId, monthOfPayment,yearOFpayment, tempCoachSalary, NOW())
        ON duplicate key update   #ако вече хонорарите му са преведени, то към тези пари да се добави и месечната му заплата.
        salaryAmount = salaryAmount + tempCoachSalary,
        dateOfPayment = NOW();
        set updatedSalaryCounter = updatedSalaryCounter + 1;
   ELSE
        SELECT 'The coach has a month salary yet!';
   END IF;
        SET iterator = iterator +1;
  end while;
	IF(countOfCoachesWithoutMonthSalary = updatedSalaryCounter) # преведени са толкова заплати, колкото е трябвало
	THEN
          commit;
	  SET success = 1;
          drop table tempCoach;
    ELSE
	rollback;
	SET success = 0;
        drop table tempCoach;
     END if;
END IF;
end
|
DELIMITER ;

-- proc 2
use school_sport_clubs;

drop procedure if exists monthHonorariumPayment;

delimiter |
create procedure monthHonorariumPayment(IN monthOfPayment INT, in yearOFpayment INT)
procLabel: begin
declare countOfCoaches int;
declare iterator int;
declare countOfRowsBeforeUpdate int;
declare countOfRowsAfterUpdate int;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SELECT 'SQLException occured';


create temporary table tempCoaches(
id int auto_increment primary key,
coach_id int not null,
monthHourSum int
)Engine = Memory;


create temporary table tempTbl(
id int auto_increment primary key,
coach_id int,
number_of_hours int,
pay_for_hour decimal,
amount decimal,
paymentMonth int,
paymentYear int
)Engine = Memory;

	#Плащане на редовна месечна заплата:
    SET @RESULT =0;
	SELECt @RESULT;
    call monthSalaryPayment(monthOfPayment,yearOFpayment,@RESULT);

   	SELECT COUNT(*)
    INTO countOfRowsBeforeUpdate
    FROM coach_work
	where month(coach_work.date) = monthOfPayment
	AND YEAR(coach_work.date) = yearOFpayment
    AND isPayed = 0;

INSERT INTO tempCoaches(coach_id, monthHourSum)  #записваме всички треньори и сумата от техните часове в табличка за дадения месец на дадената година
	SELECT  coach_id, SUM(number_of_hours)
	FROM coach_work
	where month(coach_work.date) = monthOfPayment
	AND YEAR(coach_work.date) = yearOFpayment
    AND isPayed = 0
	GROUP BY coach_work.coach_id;

	set iterator = 1;
	SELECT COUNT(*)
	INTO countOfCoaches
	FROM tempCoaches;

IF(ifnull(countOfCoaches,0) = 0) #Няма треньори, за които да се извършва плащане
	THEN SELECT 'No coaches and hours for this payment period';
		drop table tempTbl;
		drop table tempCoaches;
		LEAVE procLabel;
ELSE
	START TRANSACTION;
	WHILE(iterator >= 1 AND iterator <= countOfCoaches)
	DO
	INSERT INTO tempTbl(coach_id, number_of_hours, pay_for_hour, amount, paymentMonth,paymentYear)
	SELECT tc.coach_id, tc.monthHourSum, c.hour_salary, tc.monthHourSum*c.hour_salary, monthOfPayment, yearOFpayment
        FROM tempCoaches as tc JOIN coaches as c
	ON tc.coach_id = c.id
	WHERE tc.id = iterator;
	SET iterator = iterator + 1;

	END WHILE;

	INSERT INTO salarypayments(`coach_id`, `month`,`year`,`salaryAmount`,`dateOfPayment`)
        SELECT coach_id, paymentMonth, paymentYear, amount, NOW()
	FROM tempTbl
        ON DUPLICATE KEY UPDATE
        salaryAmount = salaryAmount + amount,
        dateOfPayment = NOW();


	UPDATE coach_work
        SET isPayed = 1
	WHERE month(coach_work.date) = monthOfPayment
	AND YEAR(coach_work.date) = yearOFpayment
        AND isPayed = 0;

        SELECT  ROW_COUNT() INTO countOfRowsAfterUpdate;

       IF(countOfRowsBeforeUpdate = countOfRowsAfterUpdate)
      THEN
		commit;
      ELSE
		rollback;
      END IF;

	drop table tempTbl;
	drop table tempCoaches;
END IF;
END
|
DELIMITER ;

call monthHonorariumPayment(3, 2022);


--           курсори
use school_sport_clubs;
drop procedure if exists  CursorTest;
delimiter |
create procedure CursorTest()
begin
declare finished int;
declare tempName varchar(100);
declare tempEgn varchar(10);
declare coachCursor CURSOR for
SELECT name, egn
from coaches
where month_salary is not null;
declare continue handler FOR NOT FOUND set finished = 1;
set finished = 0;
OPEN coachCursor;
coach_loop: while( finished = 0)
DO
FETCH coachCursor INTO tempName,tempEgn;
       IF(finished = 1)
       THEN
       LEAVE coach_loop;
       END IF;
       SELECT tempName,tempEgn; # or do something with these variables...
end while;
CLOSE coachCursor;
SET finished = 0;
SELECT 'Finished!';
end;
|
delimiter |

-- 			TRIGGERS
delimiter |
CREATE TRIGGER after_salarypayment_update AFTER UPDATE ON salarypayments
FOR EACH ROW
BEGIN
INSERT INTO salarypayments_log(operation,
old_coach_id,
new_coach_id,
old_month,
new_month,
old_year,
new_year,
old_salaryAmount,
new_salaryAmount,
old_dateOfPayment,
new_dateOfPayment,
dateOfLog)
VALUES ('UPDATE',OLD.coach_id,NEW.coach_id,OLD.month,NEW.month,
OLD.year,NEW.year,OLD.salaryAmount,NEW.salaryAmount,OLD.dateOfPayment,NEW.dateOfPayment,NOW());
END;
|
Delimiter ;
select * from salarypayments;
select * from salarypayments_log;

UPDATE `salarypayments` SET `salaryAmount`='2000' WHERE `id`='13';


-- 1. Създайте VIEW, с което извеждате информация за името на треньора, името
-- на спорта, чийто тренировки води, информация за група, която тренира,
-- както и сумата, която е получил за текущия месец. Резултатната таблица
-- трябва да изглежда така:
drop view if exists coachinfo;

CREATE VIEW coachinfo(name, groupInfo, sport, year, month, salaryAmaounth) as
SELECT coaches.name, concat(sportgroups.id, ' - ', sportgroups.location), sports.name, year(now()), month(now()), salarypayments.salaryAmount
FROM coaches
JOIN sportgroups
	ON coaches.id = sportgroups.coach_id
JOIN sports
	ON sports.id = sportgroups.sport_id
JOIN salarypayments
	ON coaches.id = salarypayments.coach_id
WHERE salarypayments.year = year(now())
	AND salarypayments.month = month(now());

    select * from coachinfo;

   --  2. Създайте тригер, който при изтриване на информация от таблицата
-- salarypayments записва изтритата информация в таблицата
-- salarypayments_log.

delimiter |
CREATE TRIGGER after_salarypayment_delete BEFORE DELETE ON salarypayments
FOR EACH ROW BEGIN
INSERT INTO salarypayments_log(operation,
old_coach_id,
new_coach_id,
old_month,
new_month,
old_year,
new_year,
old_salaryAmount,
new_salaryAmount,
old_dateOfPayment,
new_dateOfPayment,
dateOfLog)
VALUES ('DELETE',OLD.coach_id,null,OLD.month,null,
OLD.year,null,OLD.salaryAmount,null,OLD.dateOfPayment,null,NOW());
END |
Delimiter ;


-- 3. Изтрийте цялата информация от таблицата salarypayments и напишете
-- заявка, с която я възстановявате.
INSERT INTO salarypayments (coach_id, month, year, salaryAmount, dateOfPayment)
	select DISTINCT old_coach_id, old_month, old_year, old_salaryAmount, old_dateOfPayment
    FROM salarypayments_log
    WHERE operation LIKE 'DELETE';


-- 4. Използвайте базата данни transaction_test. Създайте процедура, която
-- конвертира суми от лева в евро и обратно по курса на БНБ. за прехвърляне
-- на пари от една сметка в друга.

DELIMITER $

CREATE PROCEDURE changeMoney(INOUT amount_of_money DOUBLE, IN course_out VARCHAR(50),IN course_in VARCHAR(50))
BEGIN
	IF(course_out = 'BGN' && course_in='EUR') THEN SET amount_of_money = amount_of_money*0.51;
    ELSEIF(course_out = 'EUR' && course_in='BGN') THEN SET amount_of_money = amount_of_money*1.96;
    ELSEIF(course_out = 'BGN' && course_in='BGN') THEN SET amount_of_money = amount_of_money;
    ELSEIF(course_out = 'EUR' && course_in='EUR') THEN SET amount_of_money = amount_of_money;
    END IF;
END $

DELIMITER ;
DROP PROCEDURE changeMoney;

SET @amount_of_money  = 5 ;
CALL changeMoney(@amount_of_money,'EUR','BGN');
SELECT @amount_of_money;


-- 5. Създайте процедура за прехвърляне на пари от една сметка в друга. Нека
-- процедурата да извежда съобщение за грешка ако няма достатъчно пари, за
-- да се осъществи успешно трансакцията или ако трансакцията е неуспешна.
-- Направете проверка за вида на валутите, в които са сметките – ако са в лева
-- или евро – извикайте процедурата от предишната задача, в противен случай
-- изведете съобщение за грешка и прекратете процедурата. Нека тя да
-- получава като параметри ID на сметката от която се прехвърля, ID на
-- сметката на получателя и сумата, която трябва да се преведе.

DELIMITER $$
CREATE PROCEDURE transfer_money_in_anotherAcc(customer_OUT INT, customer_IN INT, amount_to_add DOUBLE)
BEGIN
	START TRANSACTION;
		IF((SELECT ca.amount FROM customer_accounts AS ca WHERE ca.id = customer_OUT) < amount_to_add OR amount_to_add<0)  THEN ROLLBACK;

        ELSEIF((SELECT currency FROM customer_accounts WHERE id=customer_OUT) NOT IN('BGN','EUR')) THEN ROLLBACK;

        ELSEIF((SELECT currency FROM customer_accounts WHERE id=customer_IN) NOT IN('BGN','EUR')) THEN ROLLBACK;

        ELSE
			UPDATE customer_accounts
			SET amount = amount - amount_to_add
			WHERE customer_accounts.id = customer_OUT;
			IF (ROW_COUNT() != 1) THEN ROLLBACK;

			ELSE
			CALL changeMoney(amount_to_add,(SELECT currency FROM customer_accounts WHERE id=customer_OUT),
            (SELECT currency FROM customer_accounts WHERE id=customer_IN));
			UPDATE customer_accounts
			SET amount = amount + amount_to_add
			WHERE customer_accounts.id = customer_IN;
				IF (ROW_COUNT() != 1) THEN ROLLBACK;
				END IF;
            END IF;
		END IF;
        IF (ROW_COUNT() != 1) THEN SELECT 'FAIL';
        END IF;
	COMMIT;
END $$
DELIMITER ;

DROP PROCEDURE transfer_money_in_anotherAcc;
SET @amount_of_money  = 6.11;
CALL transfer_money_in_anotherAcc(1,2,@amount_of_money);
select * from customer_accounts;