Функции выгрузки журнала

CREATE OR REPLACE FUNCTION get_timetable_lessons1(date_begin date, date_end date)
  RETURNS TABLE (
    s_timetable_subject timetable_subject.id%type,
    s_department        timetable_subject.s_department%type,
    "time"              TIME,
    "date"              DATE,
    s_group             profile_student_group.id%type,
    s_subject           spr_subject.id%type,
    s_vfur              spr_vfur.id%type,
    half_year           SMALLINT,
    year_begin          SMALLINT
  )
  LANGUAGE SQL
AS $$
  WITH
    dates AS (
      SELECT
        date.date::date as date,
        getweektype(date.date::date) AS s_weektype,
        date_part('dow', date.date) AS s_dayweek
      FROM generate_series(date_begin, date_end, interval '1 day') date
    )
  SELECT
    ts.id AS s_timetable_subject,
    ts.s_department,
    coalesce(ts.time_begin, pair.time_begin) AS time,
    dates.date,
    ts.s_student_group AS s_group,
    ts.s_subject,
    ts.s_vfur,
    ts.half_year,
    getBeginYear(dates.date) AS year_begin
  FROM timetable_subject ts
    INNER JOIN timetable_subject_date ts_date ON ts.id = ts_date.s_timetable_subject
    INNER JOIN spr_pair pair ON ts.s_pair = pair.id
    INNER JOIN dates ON dates.date BETWEEN ts_date.date_begin AND ts_date.date_end
    AND (ts.s_weektype = 3 OR ts.s_weektype = dates.s_weektype)
    AND ts.s_dayweek = dates.s_dayweek
  WHERE ts.s_vfur NOTNULL;
$$;

CREATE OR REPLACE FUNCTION insert_group_book_columns1(
  date_begin date,
  date_end date,
  OUT pages_inserted INT,
  OUT cols_inserted INT,
  OUT cells_inserted INT,
  OUT signs_inserted INT,
  OUT rows_inserted INT
)
  LANGUAGE plpgsql
AS
$$
DECLARE
  v_page_type group_book_page_type.id%type;
  v_col_type group_book_column_type.id%type;
  v_content_empty group_book_cell_content.id%type;
  v_signer_teacher group_book_column_signer.id%type;
  v_signer_student group_book_column_signer.id%type;
  v_year_begin INT;
  v_dopcps_id INT;
BEGIN
  SELECT id INTO v_page_type FROM group_book_page_type WHERE code = 'university';
  SELECT id INTO v_col_type FROM group_book_column_type WHERE code = 'lesson';
  SELECT id INTO v_content_empty FROM group_book_cell_content WHERE code = 'empty';
  SELECT id INTO v_signer_teacher FROM group_book_column_signer WHERE code = 'teacher';
  SELECT id INTO v_signer_student FROM group_book_column_signer WHERE code = 'group_headman';

  v_year_begin := getbeginyear();
  v_dopcps_id := getdopcpsid();

  pages_inserted := 0;
  cols_inserted := 0;
  cells_inserted := 0;
  signs_inserted := 0;
  rows_inserted := 0;

  -- Вставка страниц
  INSERT INTO group_book_page (s_group, s_vfur, s_subject, half_year, year_begin, s_type)
  SELECT DISTINCT
   sg.id s_group,
   es.s_vfur,
   es.s_subject,
   es.half_year,
   es.b_year year_begin,
   v_page_type s_type
  FROM profile_emp_subject es
    INNER JOIN profile_emp_student_group esg ON esg.s_emp_subject=es.id
    INNER JOIN profile_student_group sg ON esg.s_student_group=sg.id
    INNER JOIN spr_speciality s ON sg.s_speciality=s.id
    INNER JOIN spr_qualif_level ql ON s.s_qualif_level=ql.id
    INNER JOIN spr_vfur vf ON es.s_vfur=vf.id
    LEFT OUTER JOIN group_book_page p ON p.half_year=es.half_year
      AND p.s_group = sg.id
      AND p.s_subject = es.s_subject
      AND p.s_type = v_page_type
      AND p.s_vfur = es.s_vfur
      AND p.year_begin =es.b_year
  WHERE ql.codestring IN ('bachelor', 'specialist', 'master')
    AND es.s_group_lessons = 1 AND vf.is_reporting = 0
    AND es.s_chair <> v_dopcps_id AND es.b_year = v_year_begin
    AND p.id IS NULL;

  GET DIAGNOSTICS pages_inserted = ROW_COUNT;

  -- Вставка колонок
  WITH
    lesson AS (
      SELECT * FROM get_timetable_lessons1(date_begin, date_end)
        WHERE s_department <> v_dopcps_id
    ),
    t AS (
      SELECT
        p.id s_page,
        v_col_type s_type,
        l.date,
        l.time,
        TRUE is_imported
      FROM group_book_page p
      INNER JOIN lesson l ON p.s_group = l.s_group
        AND p.s_vfur = l.s_vfur
        AND p.s_subject = l.s_subject
        AND p.half_year = l.half_year
        AND p.year_begin = l.year_begin
    ),
    col_ins AS (
      INSERT INTO group_book_column (s_page, s_type, date, "time", is_imported)
      SELECT DISTINCT t.s_page, t.s_type, t.date, t.time, t.is_imported
      FROM t
        LEFT OUTER JOIN group_book_column c ON c.s_page = t.s_page
          AND c.s_type = t.s_type
          AND c.date = t.date
          AND c.time = t.time
      WHERE c.id IS NULL
      RETURNING
        group_book_column.id,
        group_book_column.s_page,
        group_book_column.s_type,
        group_book_column."date",
        group_book_column."time",
        group_book_column.is_imported
    ),
    sign_ins AS (
      INSERT INTO group_book_column_sign (s_signer, s_column, subgroup)
      SELECT
        v_signer_teacher AS s_signer,
        col.id AS s_column,
        pesg.subgroup
      FROM group_book_page page
        INNER JOIN col_ins col ON page.id = col.s_page
        INNER JOIN profile_emp_subject pes
          ON page.s_vfur = pes.s_vfur
            AND page.s_subject = pes.s_subject
            AND page.half_year = pes.half_year
            AND page.year_begin = pes.b_year
        INNER JOIN profile_emp_student_group pesg ON pes.id = pesg.s_emp_subject
          AND page.s_group = pesg.s_student_group
      UNION SELECT
        v_signer_student AS s_signer,
        col.id as s_column,
        NULL as subgroup
      FROM col_ins col
      RETURNING group_book_column_sign.id
    )
  SELECT
    (SELECT count(id) FROM col_ins) as cols_ins,
    (SELECT count(id) FROM sign_ins) as signs_ins
  INTO cols_inserted, signs_inserted;


  --Вставка студентов
  WITH
    s AS (
      SELECT s.*
      FROM profile_student s
        INNER JOIN spr_qualif_level ql ON s.s_qualif_level = ql.id
        INNER JOIN sys_user u ON u.id = s.s_user
        INNER JOIN spr_choose c ON c.id = u.activity AND c.code = 'yes'
      WHERE ql.codestring IN ('bachelor', 'specialist', 'master')
    ),
    t AS (
      SELECT
        s.s_user s_student_user,
        s.id s_student_profile,
        CASE WHEN s.s_student_status = 1
          THEN TRUE
          ELSE FALSE
        END is_active,
        s.s_student_group s_group,
        r.id r_id,
        r.s_student_user r_s_student_user,
        r.s_student_profile r_s_student_profile,
        COALESCE(r.is_active, FALSE) r_is_active,
        r.s_group r_s_group
      FROM s
      FULL OUTER JOIN group_book_row r ON r.s_student_profile = s.id
        AND r.s_group = s.s_student_group
      WHERE CASE WHEN s.s_student_status = 1
        THEN TRUE
        ELSE FALSE
        END <> COALESCE(r.is_active, FALSE)
    ),
    ins AS (
      INSERT INTO group_book_row (s_student_user, s_student_profile, is_active, s_group)
      SELECT s_student_user, s_student_profile, is_active, s_group
      FROM t
      WHERE is_active = TRUE AND r_id IS NULL
      RETURNING group_book_row.id
    ),
    upd AS (
      UPDATE group_book_row r SET is_active = t.is_active
      FROM t
      WHERE t.r_id=r.id
    )
  SELECT count(id) INTO rows_inserted FROM ins;

  --Вставка строк в журнал
  INSERT INTO group_book_page_row (s_page,s_row)
  SELECT p.id s_page, r.id s_row
  FROM group_book_page p
    INNER JOIN group_book_row r ON r.s_group = p.s_group
    LEFT OUTER JOIN group_book_page_row pr ON pr.s_page = p.id AND pr.s_row = r.id
  WHERE pr.s_row IS NULL;

  -- Вставка ячеек в журнал
  INSERT INTO group_book_cell (s_col,s_row,s_content)
  SELECT
    c.id s_col,
    pr.s_row,
    v_content_empty s_content
  FROM group_book_column c
    INNER JOIN group_book_page_row pr ON pr.s_page = c.s_page
    INNER JOIN group_book_row row ON pr.s_row = row.id AND row.is_active IS TRUE
    LEFT OUTER JOIN group_book_cell cell ON cell.s_col = c.id AND cell.s_row = pr.s_row
    LEFT OUTER JOIN (
        -- уже подписанные колонки
        SELECT DISTINCT s_column
        FROM group_book_column_sign
        WHERE s_user IS NOT NULL AND created_at IS NOT NULL

        UNION SELECT DISTINCT s_column
        FROM group_book_column_history h
          INNER JOIN group_book_column_status status ON h.s_status = status.id
            AND status.code IN ('closed', 'completed', 'failed')
      ) s ON s.s_column = c.id
  WHERE s.s_column IS NULL AND cell.id IS NULL;

  GET DIAGNOSTICS cells_inserted = ROW_COUNT;
END
$$;