---

use db_4991_07_pr;

DELIMITER $$
CREATE FUNCTION summa(a INT, b INT)
RETURNS INT
DETERMINISTIC
BEGIN
RETURN a + b;
END $$
DELIMITER ;

SELECT summa(5,7);

DELIMITER $$
CREATE FUNCTION sumN(a INT, b INT)
RETURNS INT
DETERMINISTIC
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE s INT DEFAULT 0;
WHILE i <= a DO
SET s = s + i;
SET i = i + 1;
END WHILE;
RETURN s;
END $$
DELIMITER ;

SELECT sumN(5,1)

DELIMITER $$
CREATE FUNCTION prN(n INT)
RETURNS INT
DETERMINISTIC
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE p INT DEFAULT 1;
WHILE i <= n DO
SET p = p * i;
SET i = i + 1;
END WHILE;
RETURN p;
END $$
DELIMITER ;

SELECT prN(5);

DELIMITER $$
CREATE FUNCTION Kol_Dney(d1 DATE, d2 DATE)
RETURNS INT
DETERMINISTIC
BEGIN
RETURN DATEDIFF(d2,d1);
END $$
DELIMITER ;

SELECT Kol_Dney('2024-01-01','2024-01-10');

DELIMITER $$
CREATE FUNCTION NazvM(m INT)
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
RETURN ELT(m, 'Январь', 'Февраль', 'Март', 'Апрель', 'Май', 'Июнь', 'Июль', 'Агуст', 'Сентябрь', 'Октябрь', 'Декабрь');
END $$
DELIMITER ;

DELIMITER $$
CREATE FUNCTION YearMonthFunc(d DATE)
RETURNS VARCHAR(10)
DETERMINISTIC
BEGIN
RETURN EXTRACT(YEAR_MONTH FROM d);
END $$
DELIMITER ;

DELIMITER $$
CREATE FUNCTION Nazv_Pr(sh INT)
RETURNS VARCHAR(100)
DETERMINISTIC
BEGIN
    DECLARE res VARCHAR(100);

    SELECT project INTO res
    FROM PROJECT
    WHERE shifr = sh;

    RETURN res;
END $$
DELIMITER ;

DELIMITER $$
CREATE FUNCTION KolRabot(teamName VARCHAR(50))
RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE res INT;

    SELECT COUNT(*) INTO res
    FROM WORK w
    JOIN TEAM t ON w.team = t.id
    WHERE t.team = teamName;

    RETURN res;
END $$
DELIMITER ;

DELIMITER $$
CREATE FUNCTION Pr_mes(m INT)
RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE res INT;

    SELECT COUNT(*) INTO res
    FROM PROJECT
    WHERE MONTH(dateStart) = m;

    RETURN res;
END $$
DELIMITER ;

DELIMITER $$
CREATE FUNCTION Nazv_MaxPr(teamName VARCHAR(50))
RETURNS VARCHAR(100)
DETERMINISTIC
BEGIN
    DECLARE res VARCHAR(100);

    SELECT p.project INTO res
    FROM PROJECT p
    JOIN WORK w ON p.shifr = w.project
    JOIN TEAM t ON w.team = t.id
    WHERE t.team = teamName
    ORDER BY p.cost DESC
    LIMIT 1;

    RETURN res;
END $$
DELIMITER ;

DELIMITER $$
CREATE FUNCTION Dni_pr(sh INT)
RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE d DATE;

    SELECT dateStart INTO d
    FROM PROJECT
    WHERE shifr = sh;

    RETURN Kol_Dney(d, CURDATE());
END $$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE PrF()
BEGIN
    SHOW FUNCTION STATUS WHERE Db = DATABASE();
END $$
DELIMITER ;

CALL PrF();

DELIMITER $$
CREATE FUNCTION ProjectsRange(a INT, b INT)
RETURNS TEXT
DETERMINISTIC
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE res TEXT DEFAULT '';
    DECLARE pr INT;

    DECLARE cur CURSOR FOR
        SELECT shifr FROM PROJECT
        WHERE cost BETWEEN a AND b;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO pr;
        IF done THEN
            LEAVE read_loop;
        END IF;
        SET res = CONCAT(res, pr, ', ');
    END LOOP;

    CLOSE cur;

    RETURN res;
END $$
DELIMITER ;