issue389_remote_sendtasks.sql

CREATE SEQUENCE seq_nicknames
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

select setval('seq_nicknames', 1, true);

CREATE TABLE nicknames
(
    id          integer DEFAULT nextval('seq_nicknames'::regclass) NOT NULL,
    name        character varying(50)                              NOT NULL,
    accountid   integer                                            NOT NULL,
    servicename character varying(20)                              NOT NULL
);

ALTER TABLE ONLY nicknames
    ADD CONSTRAINT nicknames_name_service_key UNIQUE (name, servicename);

ALTER TABLE ONLY nicknames
    ADD CONSTRAINT nicknames_ibfk_1 FOREIGN KEY (accountid) REFERENCES accounts (id);

DROP FUNCTION IF EXISTS public.getrecordbookforsubjectandnick(pnick character varying, psubject character varying, psemesterid integer, pservice character varying);
CREATE OR REPLACE FUNCTION public.getrecordbookforsubjectandnick(pnick character varying, psubject character varying, psemesterid integer, pservice character varying)
    RETURNS TABLE("RecordBookID" integer, "DisciplineID" integer)
    LANGUAGE sql
AS $function$
SELECT  record_books.ID AS "RecordBookID",
        disciplines.ID AS "DisciplineID"
FROM record_books
         JOIN record_books_plans on record_books.id = record_books_plans.recordbookid
         JOIN disciplines_study_plans on disciplines_study_plans.studyplanid = record_books_plans.studyplanid
         JOIN disciplines on disciplines.id = disciplines_study_plans.disciplineid
         JOIN subjects on disciplines.subjectid = subjects.id
         JOIN students on record_books.studentid = students.id
         JOIN accounts on accounts.id = students.accountid
         JOIN nicknames on nicknames.accountid = accounts.id
WHERE subjects.name like '%'||psubject||'%'
  AND disciplines.semesterid = psemesterid
  AND nicknames.name = pnick
  AND nicknames.servicename = pservice
  LIMIT 1;
$function$;

DROP FUNCTION IF EXISTS public.GetSubmoduleByNumber(pdiscipline integer, pnumber integer);
CREATE OR REPLACE FUNCTION public.GetSubmoduleByNumber(pdiscipline integer, pnumber integer)
    RETURNS integer
    LANGUAGE plpgsql
AS $function$
DECLARE vSubmoduleID integer DEFAULT NULL;
BEGIN

    SELECT submodules.id into vSubmoduleID
    FROM submodules
             JOIN modules on modules.id = submodules.moduleid
             JOIN disciplines on modules.disciplineid = disciplines.id
    WHERE disciplines.id = pdiscipline AND modules."type" = 'regular'
    ORDER by modules.ordernum asc,
             submodules.ordernum asc
        OFFSET pnumber
        limit 1;

    RETURN vSubmoduleID;
    EXCEPTION
       when others
          then RETURN -1;
end
$function$;