functions.sql

    INSERT INTO `submodules`
        (ModuleID, MaxRate, OrderNum, Name, Description, Type)
        VALUES  (pModuleID, pMaxRate, vOrderNum, pName, vDescription, pControlType);

    RETURN  LAST_INSERT_ID();
END //



DROP FUNCTION IF EXISTS SwapSubmoduleOrder//
CREATE FUNCTION `SwapSubmoduleOrder` (
        `pTeacherID` INT,
        `pSubmoduleID1` INT,
        `pSubmoduleID2` INT
    ) RETURNS int(11)
    NO SQL
BEGIN
    DECLARE vDisciplineID, vOrder1, vOrder2,
            vModule1, vModule2 INT DEFAULT -1;

    SELECT  submodules.OrderNum,
            submodules.ModuleID,
            disciplines.ID
        INTO vOrder1, vModule1, vDisciplineID
        FROM `submodules`
        INNER JOIN `modules`        ON  submodules.ModuleID = modules.ID
        INNER JOIN `disciplines`    ON  disciplines.ID = modules.DisciplineID
        WHERE   disciplines.AuthorID = pTeacherID AND
                submodules.ID = pSubmoduleID1
        LIMIT 1;

    SELECT  submodules.OrderNum,
            submodules.ModuleID
        INTO vOrder2, vModule2
        FROM `submodules`
        INNER JOIN `modules`        ON  submodules.ModuleID = modules.ID
        INNER JOIN `disciplines`    ON  disciplines.ID = modules.DisciplineID
        WHERE   disciplines.AuthorID = pTeacherID AND
                submodules.ID = pSubmoduleID2
        LIMIT 1;

    # check, that modules exists and belong to one discipline
    IF  vModule1 <= 0 OR vModule1 != vModule2 OR
        InternalIsMapLocked(vDisciplineID)
    THEN
        RETURN -1;
    END IF;

    # swap
    UPDATE `submodules`
        SET submodules.OrderNum     = 271828
        WHERE submodules.ID = pSubmoduleID1
        LIMIT 1;
    UPDATE `submodules`
        SET submodules.OrderNum     = vOrder1
        WHERE submodules.ID = pSubmoduleID2
        LIMIT 1;
    UPDATE `submodules`
        SET submodules.OrderNum     = vOrder2
        WHERE submodules.ID = pSubmoduleID1
        LIMIT 1;
    RETURN 0;
END //


# -------------------------------------------------------------------------------------------
# Label: rating
# -------------------------------------------------------------------------------------------


# Вычисление максимального балла для submodule
DROP FUNCTION IF EXISTS CalculateMaxRateForExtra//
CREATE FUNCTION `CalculateMaxRateForExtra` (
        `pDisciplineID` INT,
        `pStudentID` INT
    ) RETURNS int(11)
    NO SQL
BEGIN
    DECLARE vExamType enum('exam', 'credit') DEFAULT NULL;
    DECLARE vDiscID, vLim, vResult INT DEFAULT 0;

    # get disc type
    SET vExamType = GetDisciplineProperty(pDisciplineID, 'type');

    # submodule doesn't exists
    IF vExamType IS NULL OR vExamType <= 0 THEN
        RETURN -1;
    END IF;
    SET vLim = IF(vExamType = 'exam', 38, 60);

    SELECT SUM(IF(view_roadmap.ModuleType = 'regular', rating_table.Rate, 0)) INTO vResult
        FROM `view_roadmap`
        LEFT JOIN `rating_table` ON rating_table.StudentID = pStudentID AND
                                    rating_table.SubmoduleID = view_roadmap.SubmoduleID
        WHERE view_roadmap.DisciplineID = pDisciplineID
        LIMIT 1;
    RETURN vLim - vResult;
END //


DROP FUNCTION IF EXISTS SetStudentRate//
CREATE FUNCTION `SetStudentRate` (
        `pTeacherID` INT,
        `pStudentID` INT,
        `pSubmoduleID` INT,
        `pRate` INT
    ) RETURNS int(11)
    NO SQL
BEGIN
    DECLARE vDisciplineID, vMaxRate, vModuleType, vSemesterID INT DEFAULT -1;
    DECLARE vIsOver, vIsLocked, vIsUsed BOOLEAN DEFAULT FALSE;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    IF pRate < 0 THEN
        INSERT INTO `logs_rating`
            (StudentID, SubmoduleID, TeacherID, Rate, Action )
            VALUES  (pStudentID, pSubmoduleID, pTeacherID, pRate, 'delete');

        # TODO: extract method log rate
        DELETE FROM `rating_table`
            WHERE   rating_table.StudentID = pStudentID AND
                    rating_table.SubmoduleID = pSubmoduleID
            LIMIT 1;

        RETURN ROW_COUNT()-1;
    END IF;

    SET vIsOver = TRUE;
    SELECT  disciplines.ID,
            disciplines.SemesterID,
            disciplines.IsLocked,
            disciplines.Milestone,
            submodules.IsUsed,
            submodules.maxRate,
            modules.Type
        INTO vDisciplineID, vSemesterID, vIsLocked, vIsOver, vIsUsed, vMaxRate, vModuleType
        FROM `submodules`
        INNER JOIN `modules`                ON  submodules.ModuleID = modules.ID
        INNER JOIN `disciplines`            ON  modules.DisciplineID = disciplines.ID
        WHERE   submodules.ID = pSubmoduleID
        LIMIT 1;

    # correct max rate for extra module
    IF vModuleType = 4 THEN # 4 - extra
        SET vMaxRate = CalculateMaxRateForExtra(vDisciplineID, pStudentID);
    END IF;

    # 1) check rights
    # 2) check, you can't rate regular and bonus after milestone
    # 3) check, max rate exceeding
    IF  NOT InternalIsStudentAttached(pStudentID, vDisciplineID, vSemesterID) OR
        NOT InternalIsTeacherBound(pTeacherID, vDisciplineID) OR
        pRate > vMaxRate OR
        (vIsOver AND (vModuleType = 1 OR vModuleType = 3)) # 1 - regular, 3 - bonus
    THEN
        RETURN -2;
    END IF;

    # add rate, or update old
    SET @tmp = 0;
    INSERT INTO `rating_table`
        (StudentID, TeacherID, SubmoduleID, Rate, Date)
        VALUES  ( pStudentID, pTeacherID, pSubmoduleID, pRate, CURDATE())
        ON DUPLICATE KEY UPDATE
            rating_table.TeacherID  = (@tmp := pTeacherID),
            rating_table.Rate       = pRate,
            rating_table.Date       = CURDATE();

    # log rate
    INSERT INTO `logs_rating`
        (StudentID, SubmoduleID, TeacherID, Rate, Action )
        VALUES  (pStudentID, pSubmoduleID, pTeacherID, pRate,
                        CASE WHEN @tmp > 0 THEN 'add' ELSE 'change' END);

    # lock discipline for structure editing
    IF NOT vIsLocked THEN
        UPDATE `disciplines`
        SET disciplines.IsLocked = TRUE
        WHERE disciplines.ID = vDisciplineID
        LIMIT 1;
    END IF;

    # add submodule to max rate counting
    IF NOT vIsUsed THEN
        UPDATE `submodules`
            SET submodules.IsUsed = TRUE
            WHERE submodules.ID = pSubmoduleID
            LIMIT 1;
    END IF;
    RETURN 0;
END //





# -------------------------------------------------------------------------------------------
# Label: requests
# -------------------------------------------------------------------------------------------



DROP FUNCTION IF EXISTS SetRequestStatus//
CREATE FUNCTION `SetRequestStatus` (
        `pRequestID` INT,
        `pStatus` enum('opened','processed','closed')
    ) RETURNS int(11)
    NO SQL
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;
    UPDATE `requests`
        SET requests.Status = pStatus
        WHERE requests.ID = pRequestID
        LIMIT 1;
    RETURN ROW_COUNT()-1;
END//

DROP FUNCTION IF EXISTS CreateRequest//
CREATE FUNCTION `CreateRequest`
    (   `pAccountID` INT,
        `pTitle` VARCHAR(50) CHARSET utf8,
        `pDescription` TEXT CHARSET utf8,
        `pImage` BOOLEAN
    ) RETURNS int(11)
NO SQL
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    INSERT INTO `requests`
    (AccountID, Title, Description, Status, HasImage)
    VALUES  (pAccountID, pTitle, pDescription, 'opened', pImage);
    RETURN LAST_INSERT_ID();
END//

DROP FUNCTION IF EXISTS UpdateRequest//
CREATE FUNCTION `UpdateRequest` (
    `pRequestID` INT,
    `pTitle` VARCHAR(50) CHARSET utf8,
    `pDescription` TEXT CHARSET utf8
) RETURNS int(11)
NO SQL
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    UPDATE `requests`
        SET requests.Description = pDescription,
            requests.Title = pTitle,
            requests.Date = NOW()
        WHERE   requests.ID = pRequestID AND
                requests.Description IS NULL AND
                requests.Title IS NULL
        LIMIT 1;
    RETURN ROW_COUNT()-1;
END//



# -------------------------------------------------------------------------------------------
# Label: recovery
# -------------------------------------------------------------------------------------------



DROP FUNCTION IF EXISTS CreateRecoveryToken//
CREATE FUNCTION `CreateRecoveryToken` (
    `pAccountOrEMail` VARCHAR(255) CHARSET utf8,
    `pToken` VARCHAR(100) CHARSET utf8
) RETURNS VARCHAR(255) charset utf8
    NO SQL
BEGIN
    DECLARE vAccountID INT DEFAULT -1;
    DECLARE vUserFullName TEXT charset utf8;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -2;

    # get account ID
    SELECT accounts.ID INTO vAccountID
        FROM `accounts`
        WHERE accounts.EMail = pAccountOrEMail OR
              accounts.Login = pAccountOrEMail
        LIMIT 1;
    IF vAccountID <= 0 THEN
        RETURN '';
    END IF;

    SET vUserFullName = GetUserFullNameByAccountID(vAccountID);
    IF vUserFullName IS NULL OR vUserFullName = '' THEN
        RETURN '';
    END IF;

    # transform all unused recovery tokens into used
    UPDATE `recovery_tokens`
    SET recovery_tokens.isUsed = 1
    WHERE   recovery_tokens.isUsed = 0 AND
            recovery_tokens.AccountID = vAccountID;

    # handle catch constraints violations
    INSERT INTO `recovery_tokens`
        ( AccountID, Token )
        VALUES  (vAccountID, pToken);
    RETURN vUserFullName;
END//

DROP FUNCTION IF EXISTS GetUserFullNameByAccountID//
CREATE FUNCTION `GetUserFullNameByAccountID` (
        `pAccountID` INT(11)
    ) RETURNS VARCHAR(255) charset utf8
NO SQL
BEGIN
    DECLARE vUserFullName VARCHAR(255) charset utf8;
    DECLARE vChecker INT DEFAULT -1;

    # try to find student with that account id
    SELECT students.ID, CONCAT(students.LastName,' ',students.FirstName,' ',students.SecondName)
        INTO vChecker, vUserFullName
        FROM `students`
        WHERE students.AccountID = pAccountID
        LIMIT 1;

    IF vChecker <= 0 THEN # try to find teacher with that account id
        SELECT teachers.ID, CONCAT(teachers.LastName,' ',teachers.FirstName,' ',teachers.SecondName)
            INTO vChecker, vUserFullName
            FROM `teachers`
            WHERE teachers.AccountID = pAccountID
            LIMIT 1;

        IF vChecker <= 0 THEN
            RETURN '';
        END IF;
    END IF;

    RETURN vUserFullName;
END//

DROP FUNCTION IF EXISTS UseRecoveryToken//
CREATE FUNCTION `UseRecoveryToken` (
        `pToken` VARCHAR(100) CHARSET utf8
    ) RETURNS int(11)
    NO SQL
BEGIN
    DECLARE vChecker INT DEFAULT -1;

    # set token used
    UPDATE `recovery_tokens`
        SET recovery_tokens.IsUsed = 1
        WHERE recovery_tokens.Token = pToken
        LIMIT 1;
    RETURN ROW_COUNT()-1;
END//

DROP FUNCTION IF EXISTS SaveSession //
CREATE FUNCTION SaveSession
    (   pSessionID VARCHAR(40),
        pUserLogin VARCHAR(50) CHARSET utf8,
        pUserPassword VARCHAR(64)
    ) RETURNS INT(11)
    NO SQL
BEGIN
    INSERT INTO sessions (SessionID, Login, Password)
        VALUES(pSessionID, pUserLogin, pUserPassword);
    RETURN LAST_INSERT_ID();
END //

DELIMITER ;