functions.sql

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

DELIMITER //

DROP FUNCTION IF EXISTS GetGradeID//
DROP FUNCTION IF EXISTS SetCurSemesterID//
DROP FUNCTION IF EXISTS SetHashKey//

drop function if exists InternalIsTeacherBinded//
DROP FUNCTION IF EXISTS GetSemesterID//

DROP FUNCTION IF EXISTS GetRateForDiscExtra//
DROP FUNCTION IF EXISTS GetRateForDiscBonus//
DROP FUNCTION IF EXISTS GetRateForDiscSemester//
DROP FUNCTION IF EXISTS GetRateForDiscExamNum//
DROP FUNCTION IF EXISTS GetRateForDiscExam//
DROP FUNCTION IF EXISTS InternalNotify//
DROP FUNCTION IF EXISTS GetDisciplineMaxRate//
DROP FUNCTION IF EXISTS OrderModuleTypesForSession//

DROP FUNCTION IF EXISTS CreateStudyGroup//

DROP FUNCTION IF EXISTS GetAccCountByCode//
DROP FUNCTION IF EXISTS GetAccCountByMail//
DROP FUNCTION IF EXISTS GetAccCountByLogin//

DROP FUNCTION IF EXISTS ChangePassword//
DROP FUNCTION IF EXISTS ChangeLogin//
DROP FUNCTION IF EXISTS ChangeMail//

DROP FUNCTION IF EXISTS GetRateForDisc//

drop function if exists InternalIsTeacherBounded//
DROP FUNCTION IF EXISTS SetSemesterID//
DROP FUNCTION IF EXISTS AddDiscipline//

DROP FUNCTION IF EXISTS GetMaxRateForDisc//

DROP FUNCTION IF EXISTS BindTeacher//

DROP FUNCTION IF EXISTS GetDisciplineSemesterID//

DROP FUNCTION IF EXISTS SetBitmaskByPagename//
DROP FUNCTION IF EXISTS GetBitmaskByPagename//
DROP FUNCTION IF EXISTS SetSettings//

DROP FUNCTION IF EXISTS CountRatings//


DROP FUNCTION IF EXISTS UpdateRequest//


# -------------------------------------------------------------------------------------------
# Label: abbreviations
# -------------------------------------------------------------------------------------------

# abbreviation: abbr
# specialization: spec
# department: dep

# -------------------------------------------------------------------------------------------
# Label: internals
# -------------------------------------------------------------------------------------------

# actually check for first scoring, in this case you cannot yet edit discipline
# "SetRate" stored procedure can change isLocked flag
DROP FUNCTION IF EXISTS InternalIsMapLocked//
CREATE FUNCTION `InternalIsMapLocked` (
        `pDisciplineID` INT
    ) RETURNS BOOLEAN
    NO SQL
BEGIN
    DECLARE vChecker BOOLEAN DEFAULT FALSE;

    SELECT disciplines.IsLocked INTO vChecker
        FROM `disciplines`
        WHERE disciplines.ID = pDisciplineID
        LIMIT 1;

    RETURN vChecker;
END //


# check, that student really take this course
DROP FUNCTION IF EXISTS InternalIsStudentAttached//
CREATE FUNCTION `InternalIsStudentAttached` (
    `pStudentID` INT,
    `pDisciplineID` INT,
    `pSemesterID` INT
) RETURNS BOOLEAN
    NO SQL
BEGIN
    DECLARE vAttachType enum('attach','detach') DEFAULT NULL;

    SELECT disciplines_students.Type INTO vAttachType
        FROM `disciplines_students`
        WHERE   disciplines_students.StudentID = pStudentID AND
                disciplines_students.DisciplineID = pDisciplineID
        LIMIT 1;
    IF vAttachType IS NOT NULL THEN # attached or detached
        RETURN (vAttachType = 'attach');
    END IF;

    RETURN EXISTS(
        SELECT * FROM `students_groups`
        INNER JOIN `disciplines_groups` ON  disciplines_groups.DisciplineID = pDisciplineID AND
                                            disciplines_groups.GroupID = students_groups.GroupID
        WHERE   students_groups.StudentID = pStudentID AND
                students_groups.SemesterID = pSemesterID AND
                students_groups.State != 'expulsion'
        LIMIT 1
    );
END //



# check, that teacher teach this course
DROP FUNCTION IF EXISTS InternalIsTeacherBound//
CREATE FUNCTION InternalIsTeacherBound (
    `pTeacherID` INT,
    `pDisciplineID` INT
) RETURNS BOOLEAN
NO SQL
BEGIN
    RETURN EXISTS (
        SELECT * FROM `disciplines_teachers`
            WHERE   disciplines_teachers.TeacherID = pTeacherID AND
                    disciplines_teachers.DisciplineID = pDisciplineID
            LIMIT 1
    );
END //


DROP FUNCTION IF EXISTS InternalIsTeacherAuthor//
CREATE FUNCTION `InternalIsTeacherAuthor` (
    `pTeacherID` INT,
    `pDisciplineID` INT
) RETURNS BOOLEAN
NO SQL
BEGIN
    DECLARE vAuthorID INT DEFAULT -1;

    SELECT disciplines.AuthorID INTO vAuthorID
        FROM `disciplines`
        WHERE disciplines.ID = pDisciplineID
        LIMIT 1;

    RETURN ( vAuthorID = pTeacherID );
END //



DROP FUNCTION IF EXISTS SetExamPeriodOption//
CREATE FUNCTION `SetExamPeriodOption` (
    `pStudentID` INT,
    `pSubmoduleID` INT,
    `pType` enum('absence','pass')
) RETURNS int(11)
NO SQL
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    INSERT INTO `exam_period_options`
        (StudentID, SubmoduleID, Type) VALUES(pStudentID, pSubmoduleID, pType)
        ON DUPLICATE KEY UPDATE
            exam_period_options.Type = pType;
    RETURN  0;
END //


# check, regular + exam rate == 100
DROP FUNCTION IF EXISTS InternalIsMapCreated//
CREATE FUNCTION `InternalIsMapCreated` (
    `pDisciplineID` INT
) RETURNS int(11)
NO SQL
BEGIN
    DECLARE vMaxRate INT DEFAULT -1;

    SELECT disciplines.MaxRate INTO vMaxRate
        FROM disciplines
        WHERE disciplines.ID = pDisciplineID
        LIMIT 1;
    RETURN ( vMaxRate = 100 );
END //

# ordering helper
DROP FUNCTION IF EXISTS InternalOrderModuleTypesForSession//
CREATE FUNCTION `InternalOrderModuleTypesForSession` (
        `pModuleType` INT
    ) RETURNS INT(3)
    NO SQL
BEGIN