/* Stored procedures code */
SET TERM ^ ;


CREATE OR ALTER PROCEDURE BUILD_FIO (
  I$F VARCHAR(400) CHARACTER SET UTF8 COLLATE UTF8, 
  I$I VARCHAR(400) CHARACTER SET UTF8 COLLATE UTF8, 
  I$O VARCHAR(400) CHARACTER SET UTF8 COLLATE UTF8)
RETURNS (
  FULL_NAME VARCHAR(400) CHARACTER SET UTF8 COLLATE UTF8)
AS
begin

  FULL_NAME = I$F;
  IF(I$I IS NOT NULL AND I$I <> '') THEN FULL_NAME = FULL_NAME || ' ' || SUBSTRING(I$I FROM 1 FOR 1) || '.';
  IF(I$O IS NOT NULL AND I$O <> '') THEN FULL_NAME = FULL_NAME || SUBSTRING(I$O FROM 1 FOR 1) || '.';

  SUSPEND;

end
^

COMMIT WORK^


CREATE OR ALTER PROCEDURE DATE2ISO (
  D TIMESTAMP, 
  FULL_TIME SMALLINT = 1)
RETURNS (
  VAL VARCHAR(64) CHARACTER SET UTF8 COLLATE UTF8)
AS
declare variable S varchar(10);
begin

  VAL = EXTRACT(YEAR FROM D) || '-';

  S = EXTRACT(MONTH FROM D);
  IF(CHAR_LENGTH(S) = 1) THEN S = '0' || S;
  VAL = VAL || S || '-';

  S = EXTRACT(DAY FROM D);
  IF(CHAR_LENGTH(S) = 1) THEN S = '0' || S;
  VAL = VAL || S;

  IF(FULL_TIME <> 0) THEN
  BEGIN

      VAL = VAL || 'T';

      S = EXTRACT(HOUR FROM D);
      IF(CHAR_LENGTH(S) = 1) THEN S = '0' || S;
      VAL = VAL || S || ':';

      S = EXTRACT(MINUTE FROM D);
      IF(CHAR_LENGTH(S) = 1) THEN S = '0' || S;
      VAL = VAL || S || ':';

      S = TRUNC(EXTRACT(SECOND FROM D));
      IF(CHAR_LENGTH(S) = 1) THEN S = '0' || S;
      VAL = VAL || S;

      IF(FULL_TIME = 2) THEN
      BEGIN
          VAL = VAL || '.';

          S = EXTRACT(MILLISECOND FROM D);
          IF(POSITION('.' IN S) > 0) THEN S = SUBSTRING(S FROM 1 FOR POSITION('.' IN S) - 1);
          IF(POSITION(',' IN S) > 0) THEN S = SUBSTRING(S FROM 1 FOR POSITION(',' IN S) - 1);

          IF(CHAR_LENGTH(S) = 1) THEN S = '00' || S;
          ELSE IF(CHAR_LENGTH(S) = 2) THEN S = '0' || S;
          VAL = VAL || S;  -- || 'Z'
      END

  END

  SUSPEND;

end
^

COMMIT WORK^


CREATE OR ALTER PROCEDURE EXPLODE_STR (
  S VARCHAR(4000) CHARACTER SET UTF8 COLLATE UTF8, 
  DELIM VARCHAR(1) CHARACTER SET UTF8 COLLATE UTF8 = ';')
RETURNS (
  ID VARCHAR(100) CHARACTER SET UTF8 COLLATE UTF8, 
  V VARCHAR(1000) CHARACTER SET UTF8 COLLATE UTF8)
AS
declare variable P integer;
begin

  WHILE(CHAR_LENGTH(S) > 0) DO
  BEGIN
      P = POSITION(DELIM IN S);
      IF(P <= 0) THEN P = CHAR_LENGTH(S) + 1;

      V = SUBSTRING(S FROM 1 FOR P - 1);
      S = SUBSTRING(S FROM P + 1);

      P = POSITION(':' IN V);
      IF(P > 0) THEN
      BEGIN
          ID = SUBSTRING(V FROM 1 FOR P - 1);
          V = SUBSTRING(V FROM P + 1);
      END
      ELSE
      BEGIN
          ID = V;
          V = NULL;
      END

      SUSPEND;
  END

end
^

COMMIT WORK^


CREATE OR ALTER PROCEDURE IMPORT_LISTVALS
AS
declare variable V$LISTVAL_ID bigint;
declare variable V$PARENT_ID bigint;
declare variable V$VAL varchar(400);
declare variable V$IS_DEL smallint;
declare variable V$SO smallint;
declare variable DB varchar(400);
begin

  SELECT MON$DATABASE_NAME FROM MON$DATABASE INTO :DB;
  DB = REPLACE(DB, 'DOC.FDB', 'BASE.FDB');

  FOR
    EXECUTE STATEMENT 'SELECT LISTVAL_ID, PARENT_ID, VAL, IS_DEL, SORT_ORDER FROM LISTVALS_LIST(256)'
    ON EXTERNAL :DB
    WITH COMMON TRANSACTION
    INTO :V$LISTVAL_ID, :V$PARENT_ID, :V$VAL, :V$IS_DEL, :V$SO
  DO
  BEGIN
    UPDATE OR INSERT INTO BLANK_TYPES
    (LISTVAL_ID, PARENT_ID, VAL, IS_DEL, SORT_ORDER)
    VALUES (:V$LISTVAL_ID, :V$PARENT_ID, :V$VAL, :V$IS_DEL, :V$SO);
  END

  FOR
    EXECUTE STATEMENT 'SELECT LISTVAL_ID, PARENT_ID, VAL, IS_DEL, SORT_ORDER FROM LISTVALS_LIST(19)'
    ON EXTERNAL :DB
    WITH COMMON TRANSACTION
    INTO :V$LISTVAL_ID, :V$PARENT_ID, :V$VAL, :V$IS_DEL, :V$SO
  DO
  BEGIN
    UPDATE OR INSERT INTO STUDY_LEVELS
    (LISTVAL_ID, PARENT_ID, VAL, IS_DEL, SORT_ORDER)
    VALUES (:V$LISTVAL_ID, :V$PARENT_ID, :V$VAL, :V$IS_DEL, :V$SO);
  END

  FOR
    EXECUTE STATEMENT 'SELECT LISTVAL_ID, PARENT_ID, VAL, IS_DEL, SORT_ORDER FROM LISTVALS_LIST(29)'
    ON EXTERNAL :DB
    WITH COMMON TRANSACTION
    INTO :V$LISTVAL_ID, :V$PARENT_ID, :V$VAL, :V$IS_DEL, :V$SO
  DO
  BEGIN
    UPDATE OR INSERT INTO STUDY_YEARS
    (LISTVAL_ID, PARENT_ID, VAL, IS_DEL, SORT_ORDER)
    VALUES (:V$LISTVAL_ID, :V$PARENT_ID, :V$VAL, :V$IS_DEL, :V$SO);
  END

end
^

COMMIT WORK^


CREATE OR ALTER PROCEDURE SDOCS_ADD_NUMS (
  I$BLANK_ID BIGINT, 
  I$DOC_ID BIGINT)
RETURNS (
  ADD_NUMS VARCHAR(200) CHARACTER SET UTF8 COLLATE UTF8)
AS
declare variable S varchar(100);
begin

  ADD_NUMS = '';

  FOR
    SELECT P.SERIE || ' ' || B.BLANK_NUM
    FROM BLANKS B
    JOIN BLANK_PACKS P ON P.DATE_DEL IS NULL AND P.OBJ_ID = B.PACK_ID
    WHERE B.DATE_DEL IS NULL AND B.FRONT_ID = :I$BLANK_ID
    ORDER BY 1
    INTO :S
  DO
  BEGIN
    IF(ADD_NUMS <> '') THEN ADD_NUMS = ADD_NUMS || ',';
    ADD_NUMS = ADD_NUMS || S;
  END

  SUSPEND;

end
^

COMMIT WORK^


CREATE OR ALTER PROCEDURE SDOCS_APPLY_NUMBERS (
  I$ORDER VARCHAR(8000) CHARACTER SET UTF8 COLLATE UTF8, 
  I$STAGE_ID BIGINT, 
  I$DATE DATE)
RETURNS (
  CNT INTEGER)
AS
declare variable P integer;
declare variable V$PERSON_ID bigint;
declare variable N integer;
declare variable V$YEAR_ID bigint;
declare variable V$ID bigint;
declare variable V$TYPE1 bigint;
declare variable V$TYPE2 bigint;
declare variable V$TYPE3 bigint;
declare variable V$ORDER_ID bigint;
declare variable V$ORDER_STATE bigint;
declare variable V$DATE date;
declare variable V varchar(400);
declare variable V$DOC_TYPE_ID bigint;
declare variable V$SERIE varchar(40);
declare variable V$NUM varchar(40);
declare variable V$PREV_ID bigint;
declare variable V$BS smallint;
declare variable V$DUB smallint;
declare variable V$ST bigint;
begin

  SELECT FIRST 1 D.YEAR_ID
  FROM DOCUMENTS D
  WHERE D.DATE_DEL IS NULL AND D.SEQ_NO IS NULL AND D.STATE_ID = 1
  INTO :V$YEAR_ID;

  -- Список учеников у нас общий по всем этапам, но присвоение номеров идет по уровням образования
  IF(I$STAGE_ID = 26) THEN
  BEGIN
    V$TYPE1 = 5377;
    V$TYPE2 = 5378;
    V$TYPE3 = 5385;
  END
  IF(I$STAGE_ID = 27) THEN
  BEGIN
    V$TYPE1 = 5379;
    V$TYPE2 = 5380;
  END
  IF(I$STAGE_ID = 29) THEN
  BEGIN
    V$TYPE1 = 12766;
    V$TYPE2 = 12767;
  END
  IF(I$STAGE_ID = -1) THEN
  BEGIN
    -- Особый случай - присваиваем номера только в пределах одного типа за раз
    SELECT FIRST 1 D.DOC_TYPE_ID
    FROM DOCUMENTS D
    WHERE D.DATE_DEL IS NULL AND D.SEQ_NO IS NULL AND D.YEAR_ID = :V$YEAR_ID AND D.IS_DUBL = 1 AND D.STATE_ID = 1
    INTO :V$ID;

    IF(V$ID IN (5377, 5378, 5385)) THEN
    BEGIN
      V$TYPE1 = 5377;
      V$TYPE2 = 5378;
      V$TYPE3 = 5385;
    END
    ELSE IF(V$ID IN (5379, 5380)) THEN
    BEGIN
      V$TYPE1 = 5379;
      V$TYPE2 = 5380;
    END
    ELSE IF(V$ID IN (12766, 12767)) THEN
    BEGIN
      V$TYPE1 = 12766;
      V$TYPE2 = 12767;
    END
    ELSE EXCEPTION DATA_ERROR 'Не осталось дубликатов, еще не внесенных в книгу выдачи';
  END

  --IF(ABS(DATEDIFF(DAY, I$DATE, CURRENT_DATE)) > 30) THEN EXCEPTION DATA_ERROR 'Дата записи не может быть более чем 30 дней от текущей даты';
  IF(ABS(DATEDIFF(DAY, I$DATE, CURRENT_DATE)) > 180) THEN EXCEPTION DATA_ERROR 'Дата записи не может быть более чем 180 дней от текущей даты';

  N = 1;
  CNT = 0;

  SELECT MAX(D.SEQ_NO)
  FROM DOCUMENTS D
  WHERE D.DATE_DEL IS NULL AND D.SEQ_NO IS NOT NULL AND D.YEAR_ID = :V$YEAR_ID AND (D.DOC_TYPE_ID = :V$TYPE1 OR D.DOC_TYPE_ID = :V$TYPE2 OR D.DOC_TYPE_ID = :V$TYPE3)
  INTO :N;

  IF(N IS NULL) THEN N = 1; ELSE N = N + 1;

  SELECT MON$DATABASE_NAME
  FROM MON$DATABASE
  INTO :V;

  V = REPLACE(V, 'DOC.FDB', 'BASE.FDB');

  WHILE(CHAR_LENGTH(I$ORDER) > 0) DO
  BEGIN

    P = POSITION(',' IN I$ORDER);
    IF(P <= 0) THEN P = CHAR_LENGTH(I$ORDER) + 1;
    V$PERSON_ID = SUBSTRING(I$ORDER FROM 1 FOR P - 1);
    I$ORDER = SUBSTRING(I$ORDER FROM P + 1);

    -- Кроме статуса 3 - в нем документ живет без регистрационного номера
    FOR
      SELECT D.OBJ_ID, D.ORDER_ID, D.DOC_TYPE_ID, BP.SERIE, B.BLANK_NUM, D.PARENT_ID, B.STATE_ID, D.IS_DUBL
      FROM DOCUMENTS D
      LEFT JOIN BLANKS B ON B.OBJ_ID = D.BLANK_ID AND B.DATE_DEL IS NULL
      LEFT JOIN BLANK_PACKS BP ON BP.OBJ_ID = B.PACK_ID AND BP.DATE_DEL IS NULL
      WHERE D.DATE_DEL IS NULL AND D.YEAR_ID = :V$YEAR_ID AND (D.DOC_TYPE_ID = :V$TYPE1 OR D.DOC_TYPE_ID = :V$TYPE2 OR D.DOC_TYPE_ID = :V$TYPE3)
        AND D.PERSON_ID = :V$PERSON_ID AND D.SEQ_NO IS NULL AND D.STATE_ID = 1 AND ((:I$STAGE_ID >= 0 AND D.IS_DUBL = 0) OR (:I$STAGE_ID = -1 AND D.IS_DUBL = 1))
      ORDER BY D.STATE_ID DESC, D.OBJ_ID
      INTO :V$ID, :V$ORDER_ID, :V$DOC_TYPE_ID, :V$SERIE, :V$NUM, :V$PREV_ID, :V$BS, :V$DUB
    DO
    BEGIN
      -- Не даем присваивать номер, пока приказ не закрыт
      -- Проверяем дату приказа, чтобы она была не раньше даты выдачи
      V$ORDER_STATE = NULL;
      EXECUTE STATEMENT 'SELECT FIRST 1 STATE_ID, ORDER_DATE FROM ORDERS WHERE DATE_DEL IS NULL AND OBJ_ID = ' || V$ORDER_ID
      ON EXTERNAL :V
      WITH COMMON TRANSACTION
      INTO :V$ORDER_STATE, :V$DATE;

      IF(V$ORDER_STATE IS NULL OR V$ORDER_STATE <> 11715) THEN EXCEPTION DATA_ERROR 'По всем документам со статусом "распечатан" должен быть закрыт проект приказа';
      IF(V$DATE > I$DATE) THEN EXCEPTION DATA_ERROR 'Дата приказа позже, чем указанная дата записи';

      -- Создаем или обновляем запись о документе в личном деле
      IF(V$DUB = 1) THEN V$ST = 5440; -- дубликат
      ELSE IF(V$BS = 2) THEN V$ST = 5441; -- испорченный
      ELSE IF(V$PREV_ID IS NULL) THEN V$ST = 5438; -- первичный
      ELSE V$ST = 5439; -- взамен испорченного

      EXECUTE STATEMENT 'EXECUTE PROCEDURE SDOCS_APPLY_DOC(' || V$PERSON_ID || ', ' || V$ID || ', ' || V$DOC_TYPE_ID || ', ' || V$ORDER_ID || ', ''' || V$SERIE || ''', ''' || V$NUM || ''', ' || N || ', ' || V$ST || ')'
      ON EXTERNAL :V
      WITH COMMON TRANSACTION;

      -- Если это - дубликат, отмечаем предыдующий документ как невалидный
      IF(V$DUB = 1) THEN
      EXECUTE STATEMENT 'EXECUTE PROCEDURE SDOCS_APPLY_DUB(' || V$PERSON_ID || ', ' || V$ID || ', ' || V$PREV_ID || ')'
      ON EXTERNAL :V
      WITH COMMON TRANSACTION;

      UPDATE DOCUMENTS SET SEQ_NO = :N, REC_DATE = :I$DATE WHERE OBJ_ID = :V$ID;
      N = N + 1;
      CNT = CNT + 1;
    END

  END

  SUSPEND;

end
^

COMMIT WORK^


CREATE OR ALTER PROCEDURE SDOCS_BLANKS_ENUM (
  I$TYPE_ID BIGINT, 
  I$USED_FL SMALLINT = 0)
RETURNS (
  SERIE VARCHAR(10) CHARACTER SET UTF8 COLLATE UTF8, 
  NUM VARCHAR(40) CHARACTER SET UTF8 COLLATE UTF8, 
  STATE SMALLINT, 
  SUBTYPE_ID SMALLINT)
AS
declare variable V$BP_ID bigint;
declare variable V$TYPE bigint;
declare variable V$SIZE integer;
declare variable N1 bigint;
declare variable N2 bigint;
declare variable N bigint;
begin

  FOR
    SELECT P.OBJ_ID, P.NUM_FROM, P.NUM_TO, P.SERIE, P.TYPE_ID, P.SUBTYPE_ID
    FROM BLANK_PACKS P
    WHERE P.DATE_DEL IS NULL AND P.TYPE_ID = :I$TYPE_ID AND P.BLANKS_USED < P.BLANKS_NUM
    ORDER BY P.SERIE, P.ACCEPT_DATE
    INTO :V$BP_ID, :N1, :N2, :SERIE, :V$TYPE, :SUBTYPE_ID
  DO
  BEGIN

    IF(V$TYPE IN (12766, 12767, 99990)) THEN V$SIZE = 7; ELSE V$SIZE = 9;

    N = N1;
    WHILE(N <= N2) DO
    BEGIN
      NUM = N;
      WHILE(CHAR_LENGTH(NUM) < V$SIZE) DO NUM = '0' || NUM;

      STATE = NULL;
      SELECT FIRST 1 B.STATE_ID
      FROM BLANKS B
      LEFT JOIN DOCUMENTS D ON D.DATE_DEL IS NULL AND D.BLANK_ID = B.OBJ_ID
      WHERE B.DATE_DEL IS NULL AND B.PACK_ID = :V$BP_ID AND B.BLANK_INDEX = :N
      INTO :STATE;
      IF(STATE IS NULL) THEN STATE = 0;

      IF(STATE = 0) THEN SUSPEND;

      N = N + 1;
    END

  END

end
^

COMMIT WORK^


CREATE OR ALTER PROCEDURE SDOCS_BLANK_APPLY (
  I$OBJ_ID BIGINT, 
  I$SERIE VARCHAR(10) CHARACTER SET UTF8 COLLATE UTF8, 
  I$NUM VARCHAR(40) CHARACTER SET UTF8 COLLATE UTF8, 
  I$NAME_LAST VARCHAR(200) CHARACTER SET UTF8 COLLATE UTF8, 
  I$NAME_FIRST VARCHAR(200) CHARACTER SET UTF8 COLLATE UTF8, 
  I$NAME_MID VARCHAR(200) CHARACTER SET UTF8 COLLATE UTF8, 
  I$USE_DATE DATE, 
  I$USER_ID VARCHAR(40) CHARACTER SET UTF8 COLLATE UTF8)
RETURNS (
  BLANK_ID BIGINT)
AS
declare variable V$TYPE_ID bigint;
declare variable V$BP_ID bigint;
declare variable N bigint;
declare variable SN varchar(40);
declare variable V$N1 varchar(200);
declare variable V$N2 varchar(200);
declare variable V$N3 varchar(200);
declare variable V$BLANK_STATE smallint;
declare variable V$SIZE integer;
declare variable V$NP integer;
begin

  SELECT FIRST 1 D.DOC_TYPE_ID, D.BLANK_ID, D.NAME_LAST, D.NAME_FIRST, D.NAME_MID
  FROM DOCUMENTS D
  WHERE D.OBJ_ID = :I$OBJ_ID AND D.DATE_DEL IS NULL
  INTO :V$TYPE_ID, :BLANK_ID, :V$N1, :V$N2, :V$N3;

  IF(V$TYPE_ID IS NULL) THEN EXCEPTION DATA_ERROR 'Документ не найден';
  IF(BLANK_ID IS NOT NULL) THEN EXCEPTION DATA_ERROR 'С данным документом уже связан бланк';
  IF(EXTRACT(YEAR FROM I$USE_DATE) < 2000) THEN EXCEPTION DATA_ERROR 'Вы не указали или некорректно указали дату в приказе';

  N = I$NUM;

  -- Проверяем, что такой бланк еще не использован и не зарезервирован
  -- приложения тут не выбираются !
  SELECT FIRST 1 P.OBJ_ID, B.OBJ_ID, B.STATE_ID
  FROM BLANK_PACKS P
  LEFT JOIN BLANKS B ON B.DATE_DEL IS NULL AND B.PACK_ID = P.OBJ_ID AND B.BLANK_INDEX = :N
  WHERE P.DATE_DEL IS NULL AND P.SERIE = :I$SERIE AND CAST(P.NUM_FROM AS BIGINT) <= :N AND CAST(P.NUM_TO AS BIGINT) >= :N
    AND P.TYPE_ID <> 99990 AND P.TYPE_ID = :V$TYPE_ID
  INTO :V$BP_ID, :BLANK_ID, :V$BLANK_STATE;

  IF(V$BP_ID IS NULL) THEN EXCEPTION DATA_ERROR 'Не удалось найти пакет бланков для указанных серии и номера';
  IF(BLANK_ID IS NOT NULL AND V$BLANK_STATE <> 0) THEN EXCEPTION DATA_ERROR 'Бланк уже использован или зарезервирован в другом документе';

  -- Проверяем что пакет нашелся единственный с таким номером
  SELECT COUNT(*)
  FROM BLANK_PACKS P
  WHERE P.DATE_DEL IS NULL AND P.SERIE = :I$SERIE AND CAST(P.NUM_FROM AS BIGINT) <= :N AND CAST(P.NUM_TO AS BIGINT) >= :N
    AND P.TYPE_ID <> 99990 AND P.TYPE_ID = :V$TYPE_ID
  INTO :V$NP;

  IF(V$NP > 1) THEN EXCEPTION DATA_ERROR 'В систему внесено несколько пакетов бланков с пересекающейся нумерацией';

  IF(V$TYPE_ID IN (12766, 12767, 99990)) THEN V$SIZE = 7; ELSE V$SIZE = 9;
  SN = N;
  WHILE(CHAR_LENGTH(SN) < V$SIZE) DO SN = '0' || SN;

  IF(BLANK_ID IS NULL) THEN
  BEGIN
    INSERT INTO BLANKS
    (PACK_ID, BLANK_NUM, BLANK_INDEX, USE_DATE, STATE_ID)
    VALUES (:V$BP_ID, :SN, :N, :I$USE_DATE, 1)
    RETURNING OBJ_ID INTO :BLANK_ID;
  END
  ELSE
  BEGIN
    UPDATE BLANKS
    SET STATE_ID = 1
    WHERE OBJ_ID = :BLANK_ID;
  END

  IF(I$NAME_LAST IS NULL) THEN
  BEGIN
    IF(I$NAME_FIRST IS NOT NULL OR I$NAME_MID IS NOT NULL) THEN EXCEPTION DATA_ERROR 'Для получателя аттестата либо требуется заполнить ФИО, либо оставить все поля незаполненными';
    I$NAME_LAST = V$N1;
    I$NAME_FIRST = V$N2;
    I$NAME_MID = V$N3;
  END

  SELECT COUNT(*)
  FROM BLANKS B
  WHERE B.DATE_DEL IS NULL AND B.PACK_ID = :V$BP_ID
  INTO :N;

  UPDATE BLANK_PACKS
  SET BLANKS_USED = :N
  WHERE OBJ_ID = :V$BP_ID;

  UPDATE DOCUMENTS
  SET BLANK_ID = :BLANK_ID, GIVEN_TO_LAST = :I$NAME_LAST, GIVEN_TO_FIRST = :I$NAME_FIRST, GIVEN_TO_MID = :I$NAME_MID
  WHERE OBJ_ID = :I$OBJ_ID;

  SUSPEND;

end
^

COMMIT WORK^


CREATE OR ALTER PROCEDURE SDOCS_BLANK_DISCARD (
  I$OBJ_ID BIGINT, 
  I$BLANK_ID BIGINT, 
  I$ACTION SMALLINT, 
  I$USER_ID VARCHAR(40) CHARACTER SET UTF8 COLLATE UTF8)
RETURNS (
  DOC_ID BIGINT)
AS
declare variable V$STATE_ID smallint;
declare variable V$BLANK_ID bigint not null;
declare variable V$BP_ID bigint;
declare variable V$NO integer;
declare variable V$BD date;
declare variable V$PERSON_ID bigint;
declare variable V$YEAR_ID bigint;
declare variable V$ORDER_ID bigint;
declare variable V$ORDER_STATE bigint;
declare variable N integer;
declare variable V varchar(400);
begin

  -- ACTION: 0 = освободить, 1 = аннулировать, 11 = для аннулированного бланка пересоздать документ

  SELECT FIRST 1 D.STATE_ID, D.BLANK_ID, D.SEQ_NO, D.PERSON_ID, D.YEAR_ID, D.ORDER_ID
  FROM DOCUMENTS D
  WHERE D.DATE_DEL IS NULL AND D.OBJ_ID = :I$OBJ_ID
  INTO :V$STATE_ID, :V$BLANK_ID, :V$NO, :V$PERSON_ID, :V$YEAR_ID, :V$ORDER_ID;

  IF(V$STATE_ID IS NULL) THEN EXCEPTION DATA_ERROR 'Документ не найден';
  IF(V$STATE_ID > 1) THEN EXCEPTION DATA_ERROR 'Статус документа не позволяет вносить изменения в бланк';
  -- IF(I$ACTION = 0 AND V$NO IS NOT NULL) THEN EXCEPTION DATA_ERROR 'Нельзя очищать номер бланка после распечатки книги выдачи';
  IF(V$BLANK_ID IS NULL OR I$BLANK_ID <> V$BLANK_ID) THEN EXCEPTION DATA_ERROR 'Бланк уже отозван, или указанный бланк не соответствует документу';

  V$STATE_ID = NULL;
  SELECT FIRST 1 B.STATE_ID, B.PACK_ID
  FROM BLANKS B
  WHERE B.DATE_DEL IS NULL AND B.OBJ_ID = :I$BLANK_ID
  INTO :V$STATE_ID, :V$BP_ID;

  IF(V$STATE_ID IS NULL) THEN EXCEPTION DATA_ERROR 'Системный сбой. Бланк не зарегистрирован в реестре бланков';

  SELECT MON$DATABASE_NAME
  FROM MON$DATABASE
  INTO :V;

  V = REPLACE(V, 'DOC.FDB', 'BASE.FDB');

  -- Обязательно убеждаемся, что документ последний в цепочке
  IF(EXISTS(
    SELECT FIRST 1 1
    FROM DOCUMENTS D
    WHERE D.DATE_DEL IS NULL AND D.PARENT_ID = :I$OBJ_ID
  ))
  THEN
    EXCEPTION DATA_ERROR 'Нельзя изменить статус бланка в середине цепочки дубликатов. ' ||
      'Изменять статус или пересоздавать документ можно только для последней версии документа';

  -- Приказ должен быть в открытом статусе
  EXECUTE STATEMENT
    'SELECT FIRST 1 STATE_ID
    FROM ORDERS
    WHERE DATE_DEL IS NULL AND OBJ_ID = ' || V$ORDER_ID
  ON EXTERNAL :V
  WITH COMMON TRANSACTION
  INTO :V$ORDER_STATE;

  IF(V$ORDER_STATE IS NULL OR V$ORDER_STATE <> 11716) THEN
    EXCEPTION DATA_ERROR 'Статус бланка можно менять только пока приказ находится в статусе проекта';

  IF(I$ACTION = 0) THEN
  BEGIN
    -- освободить бланк
    UPDATE BLANKS
    SET DATE_DEL = CURRENT_TIMESTAMP
    WHERE OBJ_ID = :I$BLANK_ID;

    -- Зачищаем бланки приложений, которые были связаны с этим основным бланком
    UPDATE BLANKS
    SET DATE_DEL = CURRENT_TIMESTAMP
    WHERE FRONT_ID = :I$BLANK_ID AND DATE_DEL IS NULL;

    -- Очищаем бланк в документе
    UPDATE DOCUMENTS
    SET BLANK_ID = NULL, PRINTED_STATE = 0
    WHERE OBJ_ID = :I$OBJ_ID;

    DOC_ID = I$OBJ_ID;
  END
  ELSE
  BEGIN
    -- бланк испорчен
    UPDATE BLANKS
    SET STATE_ID = 2
    WHERE OBJ_ID = :I$BLANK_ID AND DATE_DEL IS NULL AND STATE_ID <> 2;

    -- Отмечаем как испорченные бланки приложений, которые были связаны с этим основным бланком
    UPDATE BLANKS
    SET STATE_ID = 2
    WHERE FRONT_ID = :I$BLANK_ID AND DATE_DEL IS NULL AND STATE_ID <> 2;

    -- Создаем копию документа и зачищаем ссылку на бланк
    -- если старый документ является дубликатом, то и новый будет дубликатом

    -- Специальная коррекция: дату рождения берем не из старого бланка, а из ученика
    EXECUTE STATEMENT
      'SELECT FIRST 1 VAL
      FROM VALD
      WHERE IS_DEL = 0 AND PARAM_ID IN (89, 830) AND OBJ_ID = ' || V$PERSON_ID
    ON EXTERNAL :V
    WITH COMMON TRANSACTION
    INTO :V$BD;

    IF(V$BD IS NULL) THEN EXCEPTION DATA_ERROR 'Проверьте заполнение даты рождения у обучающегося';

    INSERT INTO DOCUMENTS
    (OU_ID, STATE_ID, ORDER_ID, BLANK_ID, PERSON_ID, NAME_LAST, NAME_FIRST, NAME_MID, BIRTH_DATE, YEAR_ID, SEQ_NO, DOC_TYPE_ID, DOC_DATA,
      FORMAT_ID, PRINT_DATE, GRAD_YEAR, PARENT_ID, PRINTED_STATE, GIVEN_TO_LAST, GIVEN_TO_FIRST, GIVEN_TO_MID, REG_NUM,
      REC_DATE, IS_DUBL, CLASS_NAME, PROF_NAME, SPEC_NAME, OP_DUR, PREV_DOC, GAK_DATE, GAK_FIO, UO, QUALIFICATION,
      ORDER_DATE, ACTION_DATE, ORDER_NUM, DPL_DATA, N_PAGES)
    SELECT OU_ID, 0, ORDER_ID, NULL, PERSON_ID, NAME_LAST, NAME_FIRST, NAME_MID, :V$BD, YEAR_ID, NULL, DOC_TYPE_ID, DOC_DATA,
      FORMAT_ID, PRINT_DATE, GRAD_YEAR, :I$OBJ_ID, 0, GIVEN_TO_LAST, GIVEN_TO_FIRST, GIVEN_TO_MID, REG_NUM,
      REC_DATE, IS_DUBL, CLASS_NAME, PROF_NAME, SPEC_NAME, OP_DUR, PREV_DOC, GAK_DATE, GAK_FIO, UO, QUALIFICATION,
      ORDER_DATE, ACTION_DATE, ORDER_NUM, DPL_DATA, N_PAGES
    FROM DOCUMENTS D
    WHERE D.OBJ_ID = :I$OBJ_ID
    RETURNING OBJ_ID INTO :DOC_ID;

    -- Отмечаем старый документ как распечатанный
    UPDATE DOCUMENTS
    SET STATE_ID = 1
    WHERE OBJ_ID = :I$OBJ_ID;
  END

  SELECT COUNT(*)
  FROM BLANKS B
  WHERE B.DATE_DEL IS NULL AND B.PACK_ID = :V$BP_ID
  INTO :N;

  UPDATE BLANK_PACKS
  SET BLANKS_USED = :N
  WHERE OBJ_ID = :V$BP_ID;

  FOR
    SELECT DISTINCT B.PACK_ID
    FROM BLANKS B
    WHERE B.FRONT_ID = :I$BLANK_ID
    ORDER BY 1
    INTO :V$BP_ID
  DO
  BEGIN
    SELECT COUNT(*)
    FROM BLANKS B
    WHERE B.DATE_DEL IS NULL AND B.PACK_ID = :V$BP_ID
    INTO :N;

    UPDATE BLANK_PACKS
    SET BLANKS_USED = :N
    WHERE OBJ_ID = :V$BP_ID;
  END

  SUSPEND;

end
^

COMMIT WORK^


CREATE OR ALTER PROCEDURE SDOCS_BLANK_DROP (
  I$BP_ID BIGINT, 
  I$FROM BIGINT, 
  I$TO BIGINT, 
  I$USER_ID VARCHAR(40) CHARACTER SET UTF8 COLLATE UTF8)
AS
declare variable V$BP_FROM bigint;
declare variable V$BP_TO bigint;
declare variable V$TYPE bigint;
declare variable V$SIZE integer;
declare variable N bigint;
declare variable S varchar(40);
begin

  SELECT BP.NUM_FROM, BP.NUM_TO, BP.TYPE_ID
  FROM BLANK_PACKS BP
  WHERE BP.OBJ_ID = :I$BP_ID AND BP.DATE_DEL IS NULL
  INTO :V$BP_FROM, :V$BP_TO, :V$TYPE;

  IF(V$BP_FROM IS NULL) THEN EXCEPTION DATA_ERROR 'Пакет бланков не найден';
  IF(I$TO IS NULL) THEN I$TO = I$FROM;

  IF(I$FROM IS NULL OR I$FROM < V$BP_FROM OR I$FROM > V$BP_TO) THEN
    EXCEPTION DATA_ERROR 'Не указан начальный номер бланка, либо номер находится за пределами номеров в пакете';
  IF(I$TO > V$BP_TO OR I$TO < I$FROM) THEN
    EXCEPTION DATA_ERROR 'Конечный номер бланка находится за допустимыми пределами';
  IF((I$TO - I$FROM) > 100) THEN
    EXCEPTION DATA_ERROR 'Нельзя аннулировать более 100 бланков за раз. Если у вас появилась необходимость аннулировать вагон бланков - вероятно где-то имеется проблема';

  -- Проверяем
  SELECT FIRST 1 B.BLANK_NUM
  FROM BLANKS B
  WHERE B.PACK_ID = :I$BP_ID AND B.DATE_DEL IS NULL AND B.BLANK_INDEX >= :I$FROM AND B.BLANK_INDEX <= :I$TO
  INTO :S;

  IF(S IS NOT NULL) THEN EXCEPTION DATA_ERROR 'Бланк с номером ' || S || ' уже зафиксирован в системе, нельзя аннулировать';

  IF(V$TYPE IN (12766, 12767, 99990)) THEN V$SIZE = 7; ELSE V$SIZE = 9;

  -- Вносим
  N = I$FROM;
  WHILE(N <= I$TO) DO
  BEGIN
    S = N;
    WHILE(CHAR_LENGTH(S) < V$SIZE) DO S = '0' || S;

    INSERT INTO BLANKS
    (PACK_ID, STATE_ID, BLANK_INDEX, BLANK_NUM)
    VALUES (:I$BP_ID, 3, :N, :S);

    N = N + 1;
  END

  SELECT COUNT(*)
  FROM BLANKS B
  WHERE B.DATE_DEL IS NULL AND B.PACK_ID = :I$BP_ID
  INTO :N;

  UPDATE BLANK_PACKS
  SET BLANKS_USED = :N
  WHERE OBJ_ID = :I$BP_ID;

end
^

COMMIT WORK^


CREATE OR ALTER PROCEDURE SDOCS_BLANK_GET (
  I$OBJ_ID BIGINT, 
  I$USER_ID VARCHAR(40) CHARACTER SET UTF8 COLLATE UTF8)
RETURNS (
  TAG_NAME VARCHAR(100) CHARACTER SET UTF8 COLLATE UTF8, 
  VAL VARCHAR(1000) CHARACTER SET UTF8 COLLATE UTF8)
AS
declare variable V$ID bigint;
declare variable V$TEXT varchar(1000);
declare variable V$OBJ_ID bigint;
declare variable V$ACCEPT_DATE date;
declare variable V$ACCEPT_FROM varchar(1000);
declare variable V$SERIE varchar(40);
declare variable V$NUM_FROM varchar(40);
declare variable V$NUM_TO varchar(40);
declare variable V$TYPE_ID bigint;
declare variable V$STAGE_ID bigint;
declare variable V$BLANKS_NUM integer;
declare variable V$BLANKS_USED integer;
declare variable V$SUBTYPE smallint;
begin

  SELECT B.OBJ_ID, B.ACCEPT_DATE, B.ACCEPT_FROM, B.SERIE, B.NUM_FROM, B.NUM_TO, B.TYPE_ID, B.STAGE_ID, B.BLANKS_NUM, B.BLANKS_USED, B.SUBTYPE_ID, B.COMMENTS
  FROM BLANK_PACKS B
  WHERE B.DATE_DEL IS NULL AND B.OBJ_ID = :I$OBJ_ID
  INTO :V$OBJ_ID, :V$ACCEPT_DATE, :V$ACCEPT_FROM, :V$SERIE, :V$NUM_FROM, :V$NUM_TO, :V$TYPE_ID, :V$STAGE_ID, :V$BLANKS_NUM, :V$BLANKS_USED, :V$SUBTYPE, :V$TEXT;

  IF(I$OBJ_ID IS NOT NULL AND V$OBJ_ID IS NULL) THEN EXCEPTION DATA_ERROR 'Пакет бланков не найден или был удален';

  TAG_NAME = 'id';
  VAL = I$OBJ_ID;
  SUSPEND;

  TAG_NAME = 'accept_date';
  VAL = (SELECT VAL FROM DATE2ISO(:V$ACCEPT_DATE, 0));
  SUSPEND;

  TAG_NAME = 'accept_from';
  VAL = V$ACCEPT_FROM;
  SUSPEND;

  TAG_NAME = 'serie';
  VAL = V$SERIE;
  SUSPEND;

  TAG_NAME = 'num_from';
  VAL = V$NUM_FROM;
  SUSPEND;

  TAG_NAME = 'num_to';
  VAL = V$NUM_TO;
  SUSPEND;

  TAG_NAME = 'blanks_num';
  VAL = V$BLANKS_NUM;
  SUSPEND;

  TAG_NAME = 'blanks_used';
  VAL = V$BLANKS_USED;
  SUSPEND;

  TAG_NAME = 'comments';
  VAL = V$TEXT;
  SUSPEND;

  -- Только для СОО с отличием
  TAG_NAME = 'subtype_id:selection::json&colon;array';
  VAL = 1;
  SUSPEND;
    TAG_NAME = 'subtype_id:selection:r';
    VAL = NULL;
    SUSPEND;
    TAG_NAME = 'subtype_id:selection:r:id';
    VAL = 1;
    SUSPEND;
    TAG_NAME = 'subtype_id:selection:r:value';
    VAL = 'Красный (к медали I степени)';
    SUSPEND;
    TAG_NAME = 'subtype_id:selection:r';
    VAL = NULL;
    SUSPEND;
    TAG_NAME = 'subtype_id:selection:r:id';
    VAL = 2;
    SUSPEND;
    TAG_NAME = 'subtype_id:selection:r:value';
    VAL = 'Сине-голубой (к медали II степени)';
    SUSPEND;
    TAG_NAME = 'subtype_id:selection:r';
    VAL = NULL;
    SUSPEND;
    TAG_NAME = 'subtype_id:selection:r:id';
    VAL = 0;
    SUSPEND;
    TAG_NAME = 'subtype_id:selection:r:value';
    VAL = 'Образца до 2024 года';
    SUSPEND;
  TAG_NAME = 'subtype_id:values:value';
  VAL = V$SUBTYPE;
  SUSPEND;

  -- Список уровней образования, без фильтров
  TAG_NAME = 'stage_id:selection::json&colon;array';
  VAL = 1;
  SUSPEND;
  FOR
    SELECT L.LISTVAL_ID, L.VAL
    FROM STUDY_LEVELS L
    WHERE L.IS_DEL = 0 AND L.LISTVAL_ID IN (22, 26, 27, 29, 13129)
    ORDER BY L.SORT_ORDER
    INTO :V$ID, :V$TEXT
  DO
  BEGIN
    TAG_NAME = 'stage_id:selection:r';
    VAL = NULL;
    SUSPEND;
    TAG_NAME = 'stage_id:selection:r:id';
    VAL = V$ID;
    SUSPEND;
    TAG_NAME = 'stage_id:selection:r:value';
    VAL = V$TEXT;
    SUSPEND;
  END
  TAG_NAME = 'stage_id:values:value';
  VAL = V$STAGE_ID;
  SUSPEND;

  -- Список типов документов, фильтр по уровню
  TAG_NAME = 'type_id:selection::json&colon;array';
  VAL = 1;
  SUSPEND;
  FOR
    SELECT T.LISTVAL_ID, T.VAL
    FROM BLANK_TYPES T
    WHERE T.IS_DEL = 0 AND T.LISTVAL_ID <> 99999
    ORDER BY T.SORT_ORDER
    INTO :V$ID, :V$TEXT
  DO
  BEGIN
    TAG_NAME = 'type_id:selection:r';
    VAL = NULL;
    SUSPEND;
    TAG_NAME = 'type_id:selection:r:id';
    VAL = V$ID;
    SUSPEND;
    TAG_NAME = 'type_id:selection:r:value';
    VAL = V$TEXT;
    SUSPEND;
  END
  TAG_NAME = 'type_id:values:value';
  VAL = V$TYPE_ID;
  SUSPEND;

  TAG_NAME = 'blanks::json&colon;array';
  VAL = 1;
  SUSPEND;
  FOR
    SELECT B.OBJ_ID, B.BLANK_NUM, B.STATE_ID, IIF(D2.OBJ_ID IS NULL, D.NAME_LAST || ' ' || D.NAME_FIRST || COALESCE(' ' || D.NAME_MID, ''),
      D2.NAME_LAST || ' ' || D2.NAME_FIRST || COALESCE(' ' || D2.NAME_MID, '')), B.USE_DATE
    FROM BLANKS B
    LEFT JOIN DOCUMENTS D ON D.DATE_DEL IS NULL AND D.BLANK_ID = B.OBJ_ID
    LEFT JOIN BLANKS B2 ON B2.DATE_DEL IS NULL AND B2.OBJ_ID = B.FRONT_ID AND B.FRONT_ID IS NOT NULL
    LEFT JOIN DOCUMENTS D2 ON D2.DATE_DEL IS NULL AND D2.BLANK_ID = B2.OBJ_ID AND B2.OBJ_ID IS NOT NULL
    WHERE B.DATE_DEL IS NULL AND B.PACK_ID = :I$OBJ_ID
    ORDER BY B.BLANK_INDEX, B.BLANK_NUM
    INTO :V$ID, :V$SERIE, :V$STAGE_ID, :V$TEXT, :V$ACCEPT_DATE
  DO
  BEGIN
    TAG_NAME = 'blanks:r';
    VAL = NULL;
    SUSPEND;
    TAG_NAME = 'blanks:r:id';
    VAL = V$ID;
    SUSPEND;
    TAG_NAME = 'blanks:r:state';
    VAL = V$STAGE_ID;
    SUSPEND;
    TAG_NAME = 'blanks:r:number';
    VAL = V$SERIE;
    SUSPEND;
    TAG_NAME = 'blanks:r:name';
    VAL = V$TEXT;
    SUSPEND;
    TAG_NAME = 'blanks:r:use_date';
    VAL = (SELECT VAL FROM DATE2ISO(:V$ACCEPT_DATE, 0));
    SUSPEND;
  END

end
^

COMMIT WORK^


CREATE OR ALTER PROCEDURE SDOCS_BLANK_MASK (
  I$OBJ_ID BIGINT NOT NULL, 
  I$PAGE INTEGER, 
  I$MARKS_ALL SMALLINT, 
  I$BLANKS VARCHAR(400) CHARACTER SET UTF8 COLLATE UTF8, 
  I$USER_ID VARCHAR(40) CHARACTER SET UTF8 COLLATE UTF8)
RETURNS (
  STATE_ID BIGINT, 
  ADD_NUMS VARCHAR(400) CHARACTER SET UTF8 COLLATE UTF8)
AS
declare variable V$MASK bigint;
declare variable V$TYPE bigint;
declare variable V$FULLMASK bigint;
declare variable V$PRINT_DATE date;
declare variable V$PRINTED smallint;
declare variable V$SEQ integer;
declare variable V$YEAR_ID bigint;
declare variable V$FILLED varchar(200);
declare variable V$REG_NUM varchar(100);
declare variable V$PROF_NAME varchar(400);
declare variable V$OP_DUR varchar(100);
declare variable V$PREV_DOC varchar(100);
declare variable V$QUALIFICATION varchar(200);
declare variable V$GAK_DATE date;
declare variable V$GAK_FIO varchar(200);
declare variable V$BLANK_ID bigint;
declare variable V$NP integer;
declare variable V$ADD_NUM varchar(100);
declare variable V$BP_ID bigint;
declare variable V$B_ID bigint;
declare variable V$F_ID bigint;
declare variable N integer;
declare variable V$N1 bigint;
declare variable V$N2 bigint;
declare variable V$N bigint;
declare variable V$Y integer;
begin

  SELECT FIRST 1 D.DOC_TYPE_ID, D.STATE_ID, COALESCE(D.PRINTED_STATE, 0), D.DOC_TYPE_ID, D.PRINT_DATE, D.SEQ_NO, D.YEAR_ID, D.GRAD_YEAR,
    D.SPO_REGNUM, COALESCE(D.PROF_NAME, D.SPEC_NAME), D.OP_DUR, D.PREV_DOC, D.QUALIFICATION, D.GAK_DATE, D.GAK_FIO, D.N_PAGES, D.BLANK_ID
  FROM DOCUMENTS D
  WHERE D.DATE_DEL IS NULL AND D.OBJ_ID = :I$OBJ_ID
  INTO :V$TYPE, :STATE_ID, :V$MASK, :V$TYPE, :V$PRINT_DATE, :V$SEQ, :V$YEAR_ID, :V$Y,
    :V$REG_NUM, :V$PROF_NAME, :V$OP_DUR, :V$PREV_DOC, :V$QUALIFICATION, :V$GAK_DATE, :V$GAK_FIO, :V$NP, :V$BLANK_ID;

  IF(STATE_ID IS NULL) THEN EXCEPTION DATA_ERROR 'Документ не найден';
  IF(STATE_ID <> 0) THEN EXCEPTION DATA_ERROR 'Статус документа не позволяет вносить изменения в бланк';

  IF(V$TYPE IN (5385, 99999)) THEN V$FULLMASK = 1; ELSE V$FULLMASK = 7;

  V$PRINTED = 0;
  V$FILLED = NULL;

  -- Обязательные поля для диплома
  -- Разрешаем пустые поля для года окончания ранее 2020
  IF(V$TYPE IN (12766, 12767)) THEN
  BEGIN
    IF(V$REG_NUM IS NULL) THEN V$FILLED = 'Регистрационный номер';
    ELSE IF(V$PROF_NAME IS NULL) THEN V$FILLED = 'Профессия/специальность';
    ELSE IF(V$OP_DUR IS NULL AND V$Y >= 2020) THEN V$FILLED = 'Срок освоения ОП';
    ELSE IF(V$PREV_DOC IS NULL) THEN V$FILLED = 'Предыдущий документ';
    ELSE IF(V$QUALIFICATION IS NULL AND V$Y >= 2020) THEN V$FILLED = 'Квалификация';
    ELSE IF(V$GAK_DATE IS NULL AND V$Y >= 2020) THEN V$FILLED = 'Дата решения ГЭК';
    ELSE IF(V$GAK_FIO IS NULL AND V$Y >= 2020) THEN V$FILLED = 'ФИО председателя ГЭК';
    ELSE IF(V$NP IS NULL) THEN V$FILLED = 'Неизвестно количество листов приложений';
  END

  IF(I$PAGE IS NULL) THEN
  BEGIN

    V$MASK = V$FULLMASK;

    -- Гасим весь аттестат
    IF(STATE_ID <> 1) THEN
    BEGIN
      IF(I$MARKS_ALL = 0) THEN EXCEPTION DATA_ERROR 'Не все отметки в приложении выставлены';
      IF(V$FILLED IS NOT NULL) THEN EXCEPTION DATA_ERROR 'Не все обязательные поля заполнены: ' || V$FILLED;
      STATE_ID = 1;
      IF(V$PRINT_DATE IS NULL) THEN V$PRINT_DATE = CURRENT_DATE;
      IF(V$TYPE = 99999) THEN V$PRINTED = 1;
    END

  END
  ELSE
  BEGIN

    V$MASK = BIN_OR(V$MASK, BIN_SHL(1, I$PAGE));

    -- Нельзя отметить титульный лист, если есть проблемы с данными
    IF(I$PAGE = 0 AND V$FILLED IS NOT NULL) THEN EXCEPTION DATA_ERROR 'Не все обязательные поля заполнены: ' || V$FILLED;

    -- Гасим когда все страницы распечатаны
    IF(V$MASK = V$FULLMASK AND STATE_ID <> 1) THEN
    BEGIN
      IF(I$MARKS_ALL = 0) THEN EXCEPTION DATA_ERROR 'Не все отметки в приложении выставлены';
      IF(V$FILLED IS NOT NULL) THEN EXCEPTION DATA_ERROR 'Не все обязательные поля заполнены: ' || V$FILLED;
      STATE_ID = 1;
      IF(V$PRINT_DATE IS NULL) THEN V$PRINT_DATE = CURRENT_DATE;
      IF(V$TYPE = 99999) THEN V$PRINTED = 1;
    END

  END

  -- Бланки приложений для диплома
  IF(V$TYPE IN (12766, 12767)) THEN
  BEGIN
    WHILE(CHAR_LENGTH(I$BLANKS) > 0) DO
    BEGIN
      N = POSITION(',' IN I$BLANKS);
      IF(N <= 0) THEN N = CHAR_LENGTH(I$BLANKS) + 1;
      V$ADD_NUM = SUBSTRING(I$BLANKS FROM 1 FOR N - 1);
      I$BLANKS = SUBSTRING(I$BLANKS FROM N + 1);

      -- Серия и номер отделены символом #
      N = POSITION('#' IN V$ADD_NUM);
      IF(N <= 0) THEN EXCEPTION DATA_ERROR 'Неверный формат номера и серии бланка';
      V$N = SUBSTRING(V$ADD_NUM FROM N + 1);
      V$BP_ID = NULL;
      V$B_ID = NULL;
      SELECT FIRST 1 P.OBJ_ID, P.NUM_FROM, P.NUM_TO
      FROM BLANK_PACKS P
      WHERE P.DATE_DEL IS NULL AND P.SERIE = SUBSTRING(:V$ADD_NUM FROM 1 FOR :N - 1) AND P.TYPE_ID = 99990
        AND CAST(P.NUM_FROM AS BIGINT) <= :V$N AND CAST(P.NUM_TO AS BIGINT) >= :V$N
      INTO :V$BP_ID, :V$N1, :V$N2;

      IF(V$BP_ID IS NULL) THEN EXCEPTION DATA_ERROR 'Не найден пакет бланков с серией ' || SUBSTRING(V$ADD_NUM FROM 1 FOR N - 1);

      SELECT FIRST 1 B.OBJ_ID, B.FRONT_ID
      FROM BLANKS B
      WHERE B.DATE_DEL IS NULL AND B.PACK_ID = :V$BP_ID AND B.BLANK_INDEX = :V$N
      INTO :V$B_ID, :V$F_ID;

      IF(V$B_ID IS NULL) THEN
      BEGIN
        INSERT INTO BLANKS
        (PACK_ID, STATE_ID, BLANK_NUM, BLANK_INDEX, USE_DATE, FRONT_ID)
        VALUES (:V$BP_ID, 1, SUBSTRING(:V$ADD_NUM FROM :N + 1), :V$N, CURRENT_DATE, :V$BLANK_ID)
        RETURNING OBJ_ID INTO :V$B_ID;

        -- Обновляем счетчики использованных
        SELECT COUNT(*)
        FROM BLANKS B
        WHERE B.DATE_DEL IS NULL AND B.PACK_ID = :V$BP_ID
        INTO :N;

        UPDATE BLANK_PACKS
        SET BLANKS_USED = :N
        WHERE OBJ_ID = :V$BP_ID;
      END
      ELSE IF(V$F_ID IS NOT NULL AND V$F_ID <> V$BLANK_ID) THEN
      BEGIN
        EXCEPTION DATA_ERROR 'Бланк ' || V$ADD_NUM || ' уже зарезервирован за другим дипломом';
      END

    END
  END

  SELECT COUNT(*)
  FROM BLANKS B
  WHERE B.DATE_DEL IS NULL AND B.FRONT_ID = :V$BLANK_ID
  INTO :N;

  IF(N > V$NP) THEN EXCEPTION DATA_ERROR 'Вы указали дополнительных бланков (' || N || ') больше, чем реальных страниц в документе (' || V$NP || ')';

  -- Для дипломов проверяем, что все бланки приложений погашены
  IF(STATE_ID = 1) THEN
  BEGIN
    IF(V$BLANK_ID IS NULL AND V$TYPE <> 99999) THEN EXCEPTION DATA_ERROR 'Не привязан основной бланк документа';

    IF(V$TYPE IN (12766, 12767)) THEN
    BEGIN
      IF(N <> V$NP) THEN EXCEPTION DATA_ERROR 'Не все бланки приложений погашены: в документе ' || V$NP || ', погашено ' || N;
    END
  END

  UPDATE DOCUMENTS
  SET PRINTED_STATE = :V$MASK, STATE_ID = :STATE_ID, PRINT_DATE = :V$PRINT_DATE
  WHERE OBJ_ID = :I$OBJ_ID;

  -- Для справок сразу присваиваем номер
  IF(V$SEQ IS NULL AND V$PRINTED = 1) THEN
  BEGIN
    SELECT MAX(D.SEQ_NO)
    FROM DOCUMENTS D
    WHERE D.DATE_DEL IS NULL AND D.YEAR_ID = :V$YEAR_ID AND D.DOC_TYPE_ID = 99999 AND D.SEQ_NO IS NOT NULL
    INTO :V$SEQ;

    IF(V$SEQ IS NULL) THEN V$SEQ = 1; ELSE V$SEQ = V$SEQ + 1;

    UPDATE DOCUMENTS
    SET SEQ_NO = :V$SEQ
    WHERE OBJ_ID = :I$OBJ_ID;
  END

  SELECT B.ADD_NUMS
  FROM SDOCS_ADD_NUMS(:V$BLANK_ID, :I$OBJ_ID) B
  INTO :ADD_NUMS;

  SUSPEND;

end
^

COMMIT WORK^


CREATE OR ALTER PROCEDURE SDOCS_BLANK_REVERT (
  I$BP_ID BIGINT, 
  I$NUM BIGINT, 
  I$USER_ID VARCHAR(40) CHARACTER SET UTF8 COLLATE UTF8)
AS
declare variable V$BP_FROM bigint;
declare variable V$BP_TO bigint;
declare variable V$TYPE bigint;
declare variable V$OBJ_ID bigint;
declare variable V$STATE integer;
declare variable V$DOC_ID bigint;
declare variable V$DOC_STATE integer;
declare variable V$SEQ integer;
declare variable V$NEXT_DOC bigint;
declare variable V$NEXT_BLANK bigint;
declare variable V$ORDER_ID bigint;
declare variable V$PERSON_ID bigint;
declare variable N integer;
declare variable V varchar(400);
begin

  SELECT MON$DATABASE_NAME
  FROM MON$DATABASE
  INTO :V;

  V = REPLACE(V, 'DOC.FDB', 'BASE.FDB');

  SELECT BP.NUM_FROM, BP.NUM_TO, BP.TYPE_ID
  FROM BLANK_PACKS BP
  WHERE BP.OBJ_ID = :I$BP_ID AND BP.DATE_DEL IS NULL
  INTO :V$BP_FROM, :V$BP_TO, :V$TYPE;

  IF(V$BP_FROM IS NULL) THEN EXCEPTION DATA_ERROR 'Пакет бланков не найден';

  IF(I$NUM IS NULL OR I$NUM < V$BP_FROM OR I$NUM > V$BP_TO) THEN
    EXCEPTION DATA_ERROR 'Не указан номер бланка, либо номер находится за пределами номеров в пакете';

  -- Проверяем
  SELECT FIRST 1 B.OBJ_ID, B.STATE_ID
  FROM BLANKS B
  WHERE B.PACK_ID = :I$BP_ID AND B.DATE_DEL IS NULL AND B.BLANK_INDEX = :I$NUM
  INTO :V$OBJ_ID, :V$STATE;

  IF(V$OBJ_ID IS NULL) THEN EXCEPTION DATA_ERROR 'Бланк с номером ' || I$NUM || ' в системе не найден. Неверно указан номер, либо бланк уже восстановлен';

  IF(V$STATE = 3) THEN
  BEGIN
    -- Аннулирован
    -- Просто удаляем аннулированный бланк
    UPDATE BLANKS
    SET DATE_DEL = CURRENT_TIMESTAMP
    WHERE OBJ_ID = :V$OBJ_ID AND DATE_DEL IS NULL AND STATE_ID = :V$STATE;

    -- Обновляем счетчики использованных
    SELECT COUNT(*)
    FROM BLANKS B
    WHERE B.DATE_DEL IS NULL AND B.PACK_ID = :I$BP_ID
    INTO :N;

    UPDATE BLANK_PACKS
    SET BLANKS_USED = :N
    WHERE OBJ_ID = :I$BP_ID;
  END
  ELSE IF(V$STATE = 2) THEN
  BEGIN
    -- Испорчен
    -- Удаляем сам бланк, удаляем выписанный экземпляр аттестата
    SELECT FIRST 1 D.OBJ_ID, D.STATE_ID, D.SEQ_NO, D.ORDER_ID, D.PERSON_ID
    FROM DOCUMENTS D
    WHERE D.DATE_DEL IS NULL AND D.BLANK_ID = :V$OBJ_ID
    INTO :V$DOC_ID, :V$DOC_STATE, :V$SEQ, :V$ORDER_ID, :V$PERSON_ID;

    IF(V$DOC_ID IS NULL) THEN EXCEPTION DATA_ERROR 'Не найден документ об образовании, связанный с выбранным бланком';
    IF(V$SEQ IS NOT NULL) THEN EXCEPTION DATA_ERROR 'Документ об образовании уже включен в книгу выдачи, нельзя восстановить бланк';

    -- Смотрим, если ли следующий документ
    SELECT FIRST 1 D.OBJ_ID, D.BLANK_ID
    FROM DOCUMENTS D
    WHERE D.DATE_DEL IS NULL AND D.PARENT_ID = :V$DOC_ID
    INTO :V$NEXT_DOC, :V$NEXT_BLANK;

    IF(V$NEXT_BLANK IS NOT NULL) THEN EXCEPTION DATA_ERROR 'Документ, выпущенный на замену испорченному, уже связан с бланком';

    -- Проверяем что проект приказа не закрыт, и ученик есть в проекте приказа
    EXECUTE STATEMENT
      'SELECT COUNT(*)
      FROM ORDERS D
      JOIN ORDER_DATA OD ON OD.DATE_DEL IS NULL AND OD.ORDER_ID = D.OBJ_ID AND OD.REF_ID = ' || V$PERSON_ID ||
      'WHERE D.DATE_DEL IS NULL AND D.TYPE_ID = 13326 AND D.STATE_ID = 11716 AND D.OBJ_ID = ' || V$ORDER_ID
    ON EXTERNAL :V
    WITH COMMON TRANSACTION
    INTO :N;

    IF(N = 0) THEN EXCEPTION DATA_ERROR 'Приказ о печати документов не в статусе проекта, либо обучающийся отсутствует в приказе';

    -- Удаляем следующий документ
    IF(V$NEXT_DOC IS NOT NULL) THEN
    BEGIN
      -- Если ли следующий и к этому тоже ?
      -- !!!

      UPDATE DOCUMENTS
      SET DATE_DEL = CURRENT_TIMESTAMP
      WHERE OBJ_ID = :V$NEXT_DOC AND DATE_DEL IS NULL;
    END

    -- Меняем статус бланка с испорченного на рабочий
    UPDATE BLANKS
    SET STATE_ID = 1
    WHERE OBJ_ID = :V$OBJ_ID AND DATE_DEL IS NULL AND STATE_ID = :V$STATE;

    -- Обновляем счетчики использованных
    SELECT COUNT(*)
    FROM BLANKS B
    WHERE B.DATE_DEL IS NULL AND B.PACK_ID = :I$BP_ID
    INTO :N;

    UPDATE BLANK_PACKS
    SET BLANKS_USED = :N
    WHERE OBJ_ID = :I$BP_ID;
  END
  ELSE
  BEGIN
    EXCEPTION DATA_ERROR 'Статус указанного бланка не предусматривает восстановления';
  END

end
^

COMMIT WORK^


CREATE OR ALTER PROCEDURE SDOCS_BLANK_SET (
  I$OBJ_ID BIGINT, 
  I$USER_ID VARCHAR(40) CHARACTER SET UTF8 COLLATE UTF8, 
  I$OU_ID BIGINT, 
  I$ACCEPT_DATE DATE, 
  I$ACCEPT_FROM VARCHAR(1000) CHARACTER SET UTF8 COLLATE UTF8, 
  I$SERIE VARCHAR(40) CHARACTER SET UTF8 COLLATE UTF8, 
  I$NUM_FROM VARCHAR(40) CHARACTER SET UTF8 COLLATE UTF8, 
  I$NUM_TO VARCHAR(40) CHARACTER SET UTF8 COLLATE UTF8, 
  I$TYPE_ID BIGINT, 
  I$SUBTYPE_ID BIGINT, 
  I$COMMENTS VARCHAR(200) CHARACTER SET UTF8 COLLATE UTF8)
RETURNS (
  OBJ_ID BIGINT)
AS
declare variable V$STAGE_ID bigint;
declare variable V$BLANKS_NUM bigint;
declare variable V$NUM_FROM bigint;
declare variable V$NUM_TO bigint;
declare variable V$ACCEPT_DATE date;
declare variable V$ACCEPT_FROM varchar(1000);
declare variable V$TYPE_ID bigint;
declare variable V$SERIE varchar(40);
declare variable V$NUM_FROM_OLD varchar(40);
declare variable V$NUM_TO_OLD varchar(40);
declare variable V$BLANK_ID bigint;
declare variable V$BLANK_INDEX bigint;
declare variable V$BP_ID bigint;
declare variable V$SUBTYPE smallint;
declare variable V$COMMENTS varchar(200);
begin

  IF(I$USER_ID = 'Веб-администратор') THEN I$USER_ID = 1;

  -- Нумерация бланков указывается включительно
  V$NUM_FROM = I$NUM_FROM;
  V$NUM_TO = I$NUM_TO;
  V$BLANKS_NUM = V$NUM_TO - V$NUM_FROM + 1;
  IF(V$BLANKS_NUM < 1 OR V$BLANKS_NUM > 10000) THEN EXCEPTION DATA_ERROR 'Ошибка в нумерации бланков, пакет не может быть менее чем из 1 и более чем из 10000 бланков';

  IF(I$TYPE_ID IN (0)) THEN V$STAGE_ID = 22;
  ELSE IF(I$TYPE_ID IN (5377, 5378, 5385)) THEN V$STAGE_ID = 26;
  ELSE IF(I$TYPE_ID IN (5379, 5380)) THEN V$STAGE_ID = 27;
  ELSE IF(I$TYPE_ID IN (12766, 12767, 99990)) THEN V$STAGE_ID = 29;
  ELSE EXCEPTION DATA_ERROR 'Не указан или недопустимый тип документ об образовании';

  IF(I$ACCEPT_DATE IS NULL) THEN EXCEPTION DATA_ERROR 'Не заполнена дата поставки';
  IF(I$ACCEPT_FROM IS NULL OR CHAR_LENGTH(I$ACCEPT_FROM) < 2) THEN EXCEPTION DATA_ERROR 'Не заполнено название поставщика';

  IF(I$TYPE_ID = 5380) THEN
  BEGIN
    IF(I$SUBTYPE_ID IS NULL) THEN EXCEPTION DATA_ERROR 'Не указан подтип бланка (документа)';
  END
  ELSE BEGIN
    I$SUBTYPE_ID = 0;
  END

  I$SERIE = REPLACE(TRIM(I$SERIE), ' ', '');
  IF(I$SERIE IS NULL OR CHAR_LENGTH(I$SERIE) < 2 OR CHAR_LENGTH(I$SERIE) > 6) THEN EXCEPTION DATA_ERROR 'Длина поля Серия выходит за пределы 2-6 символов';

  I$NUM_FROM = REPLACE(TRIM(I$NUM_FROM), ' ', '');
  I$NUM_TO = REPLACE(TRIM(I$NUM_TO), ' ', '');
  IF(I$NUM_FROM IS NULL OR CHAR_LENGTH(I$NUM_FROM) < 2 OR CHAR_LENGTH(I$NUM_FROM) > 9
    OR I$NUM_TO IS NULL OR CHAR_LENGTH(I$NUM_TO) < 2 OR CHAR_LENGTH(I$NUM_TO) > 9) THEN
      EXCEPTION DATA_ERROR 'Длина поля Номер бланка (с, по) выходит за пределы 2-9 символов';

  IF(CAST(I$NUM_FROM AS BIGINT) > CAST(I$NUM_TO AS BIGINT)) THEN EXCEPTION DATA_ERROR 'Конечный номер бланка должен быть не меньше начального';

  IF(I$OBJ_ID IS NULL) THEN
  BEGIN

    INSERT INTO BLANK_PACKS
    (OU_ID, ACCEPT_DATE, ACCEPT_FROM, SERIE, NUM_FROM, NUM_TO, USER_ID, TYPE_ID, STAGE_ID, BLANKS_NUM, BLANKS_USED, SUBTYPE_ID, COMMENTS)
    VALUES (:I$OU_ID, :I$ACCEPT_DATE, :I$ACCEPT_FROM, :I$SERIE, :I$NUM_FROM, :I$NUM_TO, :I$USER_ID, :I$TYPE_ID, :V$STAGE_ID, :V$BLANKS_NUM, 0, :I$SUBTYPE_ID, :I$COMMENTS)
    RETURNING OBJ_ID INTO :OBJ_ID;

  END
  ELSE BEGIN

    SELECT P.OBJ_ID, P.ACCEPT_DATE, P.ACCEPT_FROM, P.TYPE_ID, P.SERIE, P.NUM_FROM, P.NUM_TO, P.SUBTYPE_ID, P.COMMENTS
    FROM BLANK_PACKS P
    WHERE P.DATE_DEL IS NULL AND P.OBJ_ID = :I$OBJ_ID
    INTO :OBJ_ID, :V$ACCEPT_DATE, :V$ACCEPT_FROM, :V$TYPE_ID, :V$SERIE, :V$NUM_FROM_OLD, :V$NUM_TO_OLD, :V$SUBTYPE, :V$COMMENTS;

    IF(OBJ_ID IS NULL) THEN EXCEPTION DATA_ERROR 'Запись не найдена';

    -- Изменять серию или тип нельзя, если бланки уже пошли в работу
    IF((I$SERIE <> V$SERIE OR I$TYPE_ID <> V$TYPE_ID OR I$SUBTYPE_ID <> V$SUBTYPE) AND EXISTS(SELECT FIRST 1 1 FROM BLANKS B WHERE B.DATE_DEL IS NULL AND B.PACK_ID = :I$OBJ_ID))
      THEN EXCEPTION DATA_ERROR 'В пакете уже есть использованные бланки, изменить тип, подтип бланка или серию нельзя';

    -- Для приложений переносим занесенные не туда бланки в нужный пакет
    IF(I$TYPE_ID = 99990) THEN
    BEGIN
      FOR
        SELECT B.OBJ_ID, B.BLANK_INDEX
        FROM BLANKS B
        WHERE B.DATE_DEL IS NULL AND B.PACK_ID = :I$OBJ_ID AND (B.BLANK_INDEX > CAST(:V$NUM_TO_OLD AS BIGINT) OR B.BLANK_INDEX < CAST(:V$NUM_FROM_OLD AS BIGINT))
        ORDER BY 2
        INTO :V$BLANK_ID, :V$BLANK_INDEX
      DO
      BEGIN
        V$BP_ID = NULL;
        SELECT FIRST 1 BP.OBJ_ID
        FROM BLANK_PACKS BP
        WHERE BP.DATE_DEL IS NULL AND BP.TYPE_ID = :I$TYPE_ID AND CAST(BP.NUM_FROM AS BIGINT) <= :V$BLANK_INDEX AND CAST(BP.NUM_TO AS BIGINT) >= :V$BLANK_INDEX
        INTO :V$BP_ID;

        IF(V$BP_ID IS NULL) THEN EXCEPTION DATA_ERROR 'Коллизия нумерации. Для бланка ' || V$BLANK_INDEX || ' не найдено пакета с совпадающей нумерацией. Базу нужно передать для ручной правки';

        UPDATE BLANKS
        SET PACK_ID = :V$BP_ID
        WHERE OBJ_ID = :V$BLANK_ID;

        UPDATE BLANK_PACKS
        SET BLANKS_USED = (SELECT COUNT(*) FROM BLANKS WHERE BLANKS.PACK_ID = BLANK_PACKS.OBJ_ID AND BLANKS.DATE_DEL IS NULL)
        WHERE OBJ_ID = :V$BP_ID;

        UPDATE BLANK_PACKS
        SET BLANKS_USED = (SELECT COUNT(*) FROM BLANKS WHERE BLANKS.PACK_ID = BLANK_PACKS.OBJ_ID AND BLANKS.DATE_DEL IS NULL)
        WHERE OBJ_ID = :I$OBJ_ID;
      END
    END

    -- Проверяем что нумерация не задевает уже использованные бланки
    IF(I$NUM_FROM <> V$NUM_FROM_OLD OR I$NUM_TO <> V$NUM_TO_OLD) THEN
    BEGIN
      IF(V$NUM_FROM > CAST(V$NUM_FROM_OLD AS BIGINT) AND
        EXISTS(SELECT FIRST 1 1 FROM BLANKS B WHERE B.DATE_DEL IS NULL AND B.PACK_ID = :I$OBJ_ID AND B.BLANK_INDEX < :V$NUM_FROM))
          THEN EXCEPTION DATA_ERROR 'В пакете уже есть использованные бланки с номером бланка меньше чем ' || V$NUM_FROM;
      IF(V$NUM_TO < CAST(V$NUM_TO_OLD AS BIGINT) AND
        EXISTS(SELECT FIRST 1 1 FROM BLANKS B WHERE B.DATE_DEL IS NULL AND B.PACK_ID = :I$OBJ_ID AND B.BLANK_INDEX > :V$NUM_TO))
          THEN EXCEPTION DATA_ERROR 'В пакете уже есть использованные бланки с номером бланка больше чем ' || V$NUM_TO;
    END

    IF(I$ACCEPT_DATE <> V$ACCEPT_DATE OR I$ACCEPT_FROM <> V$ACCEPT_FROM OR I$TYPE_ID <> V$TYPE_ID OR COALESCE(I$SUBTYPE_ID, 0) <> COALESCE(V$SUBTYPE, 0) OR
      COALESCE(I$COMMENTS, '') <> COALESCE(V$COMMENTS, '') OR I$SERIE <> V$SERIE OR I$NUM_FROM <> V$NUM_FROM_OLD OR I$NUM_TO <> V$NUM_TO_OLD) THEN
    BEGIN

      UPDATE BLANK_PACKS
      SET ACCEPT_DATE = :I$ACCEPT_DATE, ACCEPT_FROM = :I$ACCEPT_FROM, SERIE = :I$SERIE, TYPE_ID = :I$TYPE_ID,
        NUM_FROM = :I$NUM_FROM, NUM_TO = :I$NUM_TO, BLANKS_NUM = :V$BLANKS_NUM, SUBTYPE_ID = :I$SUBTYPE_ID, COMMENTS = :I$COMMENTS,
        DATE_MODIFY = CURRENT_TIMESTAMP
      WHERE OBJ_ID = :I$OBJ_ID;

    END

    -- Правим нумерацию уже созданных бланков
    IF(I$TYPE_ID IN (12766, 12767, 99990)) THEN
    BEGIN
      UPDATE BLANKS
      SET BLANK_NUM = SUBSTRING(BLANK_NUM FROM CHAR_LENGTH(BLANK_NUM) - 6 FOR 7)
      WHERE DATE_DEL IS NULL AND PACK_ID = :I$OBJ_ID AND CHAR_LENGTH(BLANK_NUM) > 7;

      UPDATE BLANKS
      SET BLANK_NUM = '0' || BLANK_NUM
      WHERE DATE_DEL IS NULL AND PACK_ID = :I$OBJ_ID AND CHAR_LENGTH(BLANK_NUM) = 6;
    END

  END

  SUSPEND;

end
^

COMMIT WORK^


CREATE OR ALTER PROCEDURE SDOCS_CLASS_INFO (
  I$PERSON_ID BIGINT, 
  I$DATE DATE)
RETURNS (
  RUK_NAME VARCHAR(200) CHARACTER SET UTF8 COLLATE UTF8)
AS
declare variable V varchar(400);
declare variable V$DATE date;
begin

  SELECT MON$DATABASE_NAME
  FROM MON$DATABASE
  INTO :V;

  V = REPLACE(V, 'DOC.FDB', 'BASE.FDB');

  IF(I$DATE IS NULL) THEN I$DATE = CURRENT_DATE;
  V$DATE = DATEADD(-1 MONTH TO I$DATE);

  EXECUTE STATEMENT
    'SELECT FIRST 1 V59.VAL || '' '' || SUBSTRING(V60.VAL FROM 1 FOR 1) || ''.'' || COALESCE(SUBSTRING(V61.VAL FROM 1 FOR 1) || ''.'', '''')
    FROM PERSON_MOVEMENTS M
    JOIN OBJECTS G ON G.OBJ_ID = M.GROUP_ID AND G.TYPE_ID IN (9, 125, 187)
    JOIN VALR V53 ON V53.OBJ_ID = M.GROUP_ID AND V53.PARAM_ID = 53 AND V53.IS_DEL = 0
    JOIN VALS V59 ON V59.OBJ_ID = V53.VAL AND V59.PARAM_ID = 59 AND V59.IS_DEL = 0
    JOIN VALS V60 ON V60.OBJ_ID = V53.VAL AND V60.PARAM_ID = 60 AND V60.IS_DEL = 0
    LEFT JOIN VALS V61 ON V61.OBJ_ID = V53.VAL AND V61.PARAM_ID = 61 AND V61.IS_DEL = 0
    WHERE M.DATE_DEL IS NULL AND M.PERSON_ID = ' || I$PERSON_ID || ' AND M.DATE_IN < ''' || I$DATE || ''' AND (M.DATE_OUT IS NULL OR M.DATE_OUT > ''' || V$DATE || ''')'
  ON EXTERNAL :V
  -- ROLE 'INISSOFT_ROLE'
  WITH COMMON TRANSACTION
  INTO :RUK_NAME;

  SUSPEND;

end
^

COMMIT WORK^


CREATE OR ALTER PROCEDURE SDOCS_CLOSE_YEAR (
  I$STAGE_ID BIGINT, 
  I$USER_ID VARCHAR(40) CHARACTER SET UTF8 COLLATE UTF8)
AS
declare variable V$TYPE1 bigint;
declare variable V$TYPE2 bigint;
declare variable V$TYPE3 bigint;
declare variable V varchar(400);
declare variable N integer;
begin

  IF(I$STAGE_ID = 26) THEN
  BEGIN
    V$TYPE1 = 5377;
    V$TYPE2 = 5378;
    V$TYPE3 = 5385;
  END
  ELSE IF(I$STAGE_ID = 27) THEN
  BEGIN
    V$TYPE1 = 5379;
    V$TYPE2 = 5380;
  END
  ELSE IF(I$STAGE_ID = 29) THEN
  BEGIN
    V$TYPE1 = 12766;
    V$TYPE2 = 12767;
  END
  ELSE IF(I$STAGE_ID IS NOT NULL) THEN
  BEGIN
    EXCEPTION DATA_ERROR 'Для данного уровня закрытие года не предусмотрено';
  END

  SELECT MON$DATABASE_NAME
  FROM MON$DATABASE
  INTO :V;

  V = REPLACE(V, 'DOC.FDB', 'BASE.FDB');

  -- Все ли приказы закрыты ?
  EXECUTE STATEMENT
    'SELECT COUNT(*)
    FROM ORDERS
    WHERE DATE_DEL IS NULL AND TYPE_ID = 13326 AND STATE_ID = 11716'
  ON EXTERNAL :V
  WITH COMMON TRANSACTION
  INTO :N;

  IF(N > 0) THEN EXCEPTION DATA_ERROR 'Нельзя закрывать годовую печать, пока не все проекты приказов по аттестатам/дипломам закрыты';

  -- Все ли аттестаты закрыты ?
  IF(EXISTS(
    SELECT FIRST 1 1
    FROM DOCUMENTS D
    WHERE D.DATE_DEL IS NULL AND D.STATE_ID = 0 AND (:I$STAGE_ID IS NULL OR D.DOC_TYPE_ID = :V$TYPE1 OR D.DOC_TYPE_ID = :V$TYPE2 OR D.DOC_TYPE_ID = :V$TYPE3)
  ))
  THEN EXCEPTION DATA_ERROR 'В системе остались не завершенные проекты аттестатов/дипломов';

  -- Гасим все аттестаты, переводим в статус 4
  UPDATE DOCUMENTS
  SET STATE_ID = 4
  WHERE DATE_DEL IS NULL AND STATE_ID = 1 AND (:I$STAGE_ID IS NULL OR DOC_TYPE_ID = :V$TYPE1 OR DOC_TYPE_ID = :V$TYPE2 OR DOC_TYPE_ID = :V$TYPE3);

end
^

COMMIT WORK^


CREATE OR ALTER PROCEDURE SDOCS_COPY_TEMPLATE (
  I$OBJ_ID BIGINT, 
  I$OU_ID BIGINT, 
  I$NAME VARCHAR(400) CHARACTER SET UTF8 COLLATE UTF8, 
  I$USER_ID VARCHAR(40) CHARACTER SET UTF8 COLLATE UTF8)
RETURNS (
  OBJ_ID BIGINT)
AS
begin

  I$NAME = TRIM(I$NAME);
  IF(CHAR_LENGTH(I$NAME) <= 3) THEN EXCEPTION DATA_ERROR 'Название шаблона должно быть не менее 3 символов';

  IF(EXISTS(
    SELECT FIRST 1 1
    FROM PRINT_TEMPLATES
    WHERE DATE_DEL IS NULL AND OBJ_NAME = :I$NAME
  ))
  THEN EXCEPTION DATA_ERROR 'Шаблон с таким названием уже есть';

  INSERT INTO PRINT_TEMPLATES
  (OBJ_NAME, OU_ID, TYPE_ID, FMT_DATA, SIZE_X, SIZE_Y, SIZE_XA, SIZE_YA, BG_DATA)
  SELECT :I$NAME, :I$OU_ID, TYPE_ID, FMT_DATA, SIZE_X, SIZE_Y, SIZE_XA, SIZE_YA, BG_DATA
  FROM PRINT_TEMPLATES WHERE OBJ_ID = :I$OBJ_ID
  RETURNING OBJ_ID INTO :OBJ_ID;

  SUSPEND;

end
^

COMMIT WORK^


CREATE OR ALTER PROCEDURE SDOCS_DELETE_BLANK (
  I$OBJ_ID BIGINT, 
  I$USER_ID VARCHAR(40) CHARACTER SET UTF8 COLLATE UTF8)
AS
declare variable V$OBJ_ID bigint;
begin

  SELECT OBJ_ID
  FROM BLANK_PACKS
  WHERE DATE_DEL IS NULL AND OBJ_ID = :I$OBJ_ID
  INTO :V$OBJ_ID;

  IF(V$OBJ_ID IS NULL) THEN EXCEPTION DATA_ERROR 'Запись не найдена';

  IF(EXISTS(SELECT FIRST 1 1 FROM BLANKS B WHERE B.DATE_DEL IS NULL AND B.PACK_ID = :I$OBJ_ID)) THEN
    EXCEPTION DATA_ERROR 'Нельзя удалить пакет бланков при наличии использованных или аннулированных бланков из этого пакета';

  UPDATE BLANK_PACKS
  SET DATE_DEL = CURRENT_TIMESTAMP
  WHERE OBJ_ID = :I$OBJ_ID AND DATE_DEL IS NULL;

end
^

COMMIT WORK^


CREATE OR ALTER PROCEDURE SDOCS_DELETE_DOC (
  I$OBJ_ID BIGINT, 
  I$USER_ID VARCHAR(40) CHARACTER SET UTF8 COLLATE UTF8)
RETURNS (
  OBJ_ID BIGINT)
AS
declare variable V$TYPE bigint;
declare variable V$STATE_ID smallint;
declare variable V$SEQ_NO integer;
declare variable V$BLANK_ID bigint;
begin

  SELECT FIRST 1 D.OBJ_ID, D.DOC_TYPE_ID, D.STATE_ID, D.SEQ_NO, D.BLANK_ID
  FROM DOCUMENTS D
  WHERE D.DATE_DEL IS NULL AND D.OBJ_ID = :I$OBJ_ID
  INTO :OBJ_ID, :V$TYPE, :V$STATE_ID, :V$SEQ_NO, :V$BLANK_ID;

  IF(OBJ_ID IS NULL) THEN EXCEPTION DATA_ERROR 'Документ не найден';
  IF(V$BLANK_ID IS NOT NULL) THEN EXCEPTION DATA_ERROR 'К документу уже привязан бланк, удалить нельзя';
  IF(V$TYPE <> 99999 AND V$SEQ_NO IS NOT NULL) THEN EXCEPTION DATA_ERROR 'Докумен уже включен в книгу регистрации, удалить нельзя';
  -- Статус 3 все равно разрешим удалить, если были удалены дубликаты
  -- IF(V$STATE_ID = 3) THEN EXCEPTION DATA_ERROR 'Нельзя удалить документ, внесенный как ранее выданный. Для удаления - удалите дубликат';

  IF(EXISTS(
    SELECT FIRST 1 1
    FROM DOCUMENTS D
    WHERE D.DATE_DEL IS NULL AND D.PARENT_ID = :I$OBJ_ID
  ))
  THEN
    EXCEPTION DATA_ERROR 'Нельзя удалить документ, к которому был выписан дубликат';

  UPDATE DOCUMENTS
  SET DATE_DEL = CURRENT_TIMESTAMP
  WHERE DATE_DEL IS NULL AND OBJ_ID = :I$OBJ_ID AND BLANK_ID IS NULL AND (SEQ_NO IS NULL OR DOC_TYPE_ID = 99999) ;

  SUSPEND;

end
^

COMMIT WORK^


CREATE OR ALTER PROCEDURE SDOCS_DELETE_TEMPLATE (
  I$OBJ_ID BIGINT, 
  I$USER_ID VARCHAR(40) CHARACTER SET UTF8 COLLATE UTF8)
RETURNS (
  OBJ_ID BIGINT)
AS
declare variable V$BASE integer;
declare variable V$OU_ID integer;
declare variable V varchar(400);
begin

  -- Удалять шаблоны может только супер-админ, и только в пределах своей ОУ
  SELECT FIRST 1 T.OBJ_ID, T.OU_ID
  FROM PRINT_TEMPLATES T
  WHERE T.OBJ_ID = :I$OBJ_ID AND T.DATE_DEL IS NULL
  INTO :OBJ_ID, :V$OU_ID;

  IF(OBJ_ID IS NULL) THEN EXCEPTION DATA_ERROR 'Шаблон печати не найден';

  -- Номер лицензии ?
  SELECT MON$DATABASE_NAME
  FROM MON$DATABASE
  INTO :V;

  V = REPLACE(V, 'DOC.FDB', 'BASE.FDB');
  EXECUTE STATEMENT 'SELECT FIRST 1 GEN FROM GEN' ON EXTERNAL :V WITH COMMON TRANSACTION
  INTO :V$BASE;

  IF(V$OU_ID = 0 AND V$BASE <> 0) THEN EXCEPTION DATA_ERROR 'Вы не можете удалить предустановленный шаблон';
  IF(I$USER_ID <> 'Веб-администратор') THEN EXCEPTION DATA_ERROR 'Только суперадминистратор может удалять шаблоны';

  UPDATE PRINT_TEMPLATES
  SET DATE_DEL = CURRENT_TIMESTAMP
  WHERE OBJ_ID = :I$OBJ_ID;

  SUSPEND;

end
^

COMMIT WORK^


CREATE OR ALTER PROCEDURE SDOCS_DOCUMENT_DUB (
  I$PERSON_ID BIGINT, 
  I$DOC_TYPE_ID BIGINT, 
  I$ORDER_ID BIGINT, 
  I$PARENT_ID BIGINT, 
  I$PREV_NUM VARCHAR(200) CHARACTER SET UTF8 COLLATE UTF8, 
  I$PREV_DATE DATE, 
  I$NAME_LAST VARCHAR(400) CHARACTER SET UTF8 COLLATE UTF8, 
  I$NAME_FIRST VARCHAR(400) CHARACTER SET UTF8 COLLATE UTF8, 
  I$NAME_MID VARCHAR(400) CHARACTER SET UTF8 COLLATE UTF8, 
  I$BIRTH_DATE DATE, 
  I$GRAD_YEAR INTEGER, 
  I$YEAR_ID BIGINT, 
  I$DUB_STATE SMALLINT, 
  I$USER_ID VARCHAR(40) CHARACTER SET UTF8 COLLATE UTF8)
RETURNS (
  TAG_NAME VARCHAR(100) CHARACTER SET UTF8 COLLATE UTF8, 
  VAL VARCHAR(8000) CHARACTER SET UTF8 COLLATE UTF8)
AS
declare variable V$OU_ID bigint;
declare variable V$OBJ_ID bigint;
begin

  V$OU_ID = I$PERSON_ID / 1000000000;

  -- Создаем запись предыдущей версии документа, если она не была выбрана
  -- А если была выбрана - подтягиваем из нее старое содержимое
  IF(I$PARENT_ID IS NULL) THEN
  BEGIN
    -- YEAR_ID ставим по текущему году, статус 3 = внесен взамен отсутствующей записи
    INSERT INTO DOCUMENTS
    (OU_ID, STATE_ID, ORDER_ID, PERSON_ID, NAME_LAST, NAME_FIRST, NAME_MID, BIRTH_DATE, YEAR_ID, DOC_TYPE_ID, DOC_DATA, GRAD_YEAR, REG_NUM, PRINT_DATE, ACTION_DATE, DUB_STATE)
    VALUES (:V$OU_ID, 3, NULL, :I$PERSON_ID, :I$NAME_LAST, :I$NAME_FIRST, :I$NAME_MID, :I$BIRTH_DATE, :I$YEAR_ID, :I$DOC_TYPE_ID, NULL, :I$GRAD_YEAR, :I$PREV_NUM, :I$PREV_DATE, :I$PREV_DATE, :I$DUB_STATE)
    RETURNING OBJ_ID INTO :I$PARENT_ID;
  END
  ELSE
  BEGIN
    -- Фиксируем статус старого документа - возвращен или утрачен
    UPDATE DOCUMENTS
    SET DUB_STATE = :I$DUB_STATE
    WHERE OBJ_ID = :I$PARENT_ID;

    SELECT D.DOC_DATA
    FROM DOCUMENTS D
    WHERE D.OBJ_ID = :I$PARENT_ID
    INTO :VAL;
  END

  INSERT INTO DOCUMENTS
  (OU_ID, PARENT_ID, STATE_ID, ORDER_ID, PERSON_ID, NAME_LAST, NAME_FIRST, NAME_MID, BIRTH_DATE, YEAR_ID, DOC_TYPE_ID, DOC_DATA, GRAD_YEAR, IS_DUBL)
  VALUES (:V$OU_ID, :I$PARENT_ID, 0, :I$ORDER_ID, :I$PERSON_ID, :I$NAME_LAST, :I$NAME_FIRST, :I$NAME_MID, :I$BIRTH_DATE, :I$YEAR_ID, :I$DOC_TYPE_ID, :VAL, :I$GRAD_YEAR, 1)
  RETURNING OBJ_ID INTO :V$OBJ_ID;

  -- Переносим дополнительные данные для дипломов
  VAL = NULL;
  SELECT D.DPL_DATA
  FROM DOCUMENTS D
  WHERE D.OBJ_ID = :I$PARENT_ID
  INTO :VAL;

  IF(VAL IS NOT NULL) THEN
  UPDATE DOCUMENTS
  SET DPL_DATA = :VAL
  WHERE OBJ_ID = :V$OBJ_ID;

  FOR
    SELECT TAG_NAME, VAL
    FROM SDOCS_DOCUMENT_GET(NULL, :V$OBJ_ID, 1)
    INTO :TAG_NAME, :VAL
  DO
  BEGIN
    SUSPEND;
  END

end
^

COMMIT WORK^


CREATE OR ALTER PROCEDURE SDOCS_DOCUMENT_GET (
  I$PERSON_ID BIGINT, 
  I$OBJ_ID BIGINT, 
  I$USER_ID VARCHAR(40) CHARACTER SET UTF8 COLLATE UTF8)
RETURNS (
  TAG_NAME VARCHAR(200) CHARACTER SET UTF8 COLLATE UTF8, 
  VAL VARCHAR(8000) CHARACTER SET UTF8 COLLATE UTF8)
AS
declare variable V$OBJ_ID bigint;
declare variable V$STATE_ID smallint;
declare variable V$ORDER_ID bigint;
declare variable V$PERSON_ID bigint;
declare variable V$BLANK_ID bigint;
declare variable V$NAME_LAST varchar(400);
declare variable V$NAME_FIRST varchar(200);
declare variable V$NAME_MID varchar(200);
declare variable V$BIRTH_DATE date;
declare variable V$YEAR_ID bigint;
declare variable V$SEQ_NO integer;
declare variable V$DOC_TYPE_ID bigint;
declare variable V$FORMAT_ID bigint;
declare variable V$BP_ID bigint;
declare variable V$BLANK_NUM varchar(20);
declare variable V$BLANK_SERIE varchar(20);
declare variable V$OU_ID integer;
declare variable V$ID bigint;
declare variable V$TID bigint;
declare variable V$DEF smallint;
declare variable V$PSTATE bigint;
declare variable V$GRAD_YEAR integer;
declare variable V$PARENT_ID bigint;
declare variable V$GIVEN_TO_LAST varchar(200);
declare variable V$GIVEN_TO_FIRST varchar(200);
declare variable V$GIVEN_TO_MID varchar(200);
declare variable V$REG_NUM varchar(100);
declare variable V$PRINT_DATE date;
declare variable V$CN varchar(100);
declare variable S varchar(400);
declare variable V$NP integer;
declare variable V$IS_DUBL smallint;
declare variable V$BSTATE integer;
declare variable V$SPO_REGNUM varchar(100);
declare variable V varchar(400);
declare variable V$IS_GIA smallint;
declare variable V$SUBTYPE smallint;
begin

  TAG_NAME = 'data';
  SELECT FIRST 1 D.OBJ_ID, D.STATE_ID, D.ORDER_ID, D.BLANK_ID, B.BLANK_NUM, B.PACK_ID, BP.SERIE, BP.SUBTYPE_ID, D.PERSON_ID, D.NAME_LAST, D.NAME_FIRST, D.NAME_MID, D.BIRTH_DATE,
    D.YEAR_ID, D.SEQ_NO, D.DOC_TYPE_ID, D.FORMAT_ID, D.DOC_DATA, D.PRINTED_STATE, D.GRAD_YEAR, D.PARENT_ID,
    D.GIVEN_TO_LAST, D.GIVEN_TO_FIRST, D.GIVEN_TO_MID, D.REG_NUM, D.PRINT_DATE, D.CLASS_NAME, D.N_PAGES, D.IS_DUBL, B.STATE_ID, D.SPO_REGNUM
  FROM DOCUMENTS D
  LEFT JOIN BLANKS B ON B.OBJ_ID = D.BLANK_ID
  LEFT JOIN BLANK_PACKS BP ON BP.OBJ_ID = B.PACK_ID
  WHERE D.DATE_DEL IS NULL AND D.OBJ_ID = :I$OBJ_ID
  INTO :V$OBJ_ID, :V$STATE_ID, :V$ORDER_ID, :V$BLANK_ID, :V$BLANK_NUM, :V$BP_ID, :V$BLANK_SERIE, :V$SUBTYPE, :V$PERSON_ID, :V$NAME_LAST, :V$NAME_FIRST, :V$NAME_MID, :V$BIRTH_DATE,
    :V$YEAR_ID, :V$SEQ_NO, :V$DOC_TYPE_ID, :V$FORMAT_ID, :VAL, :V$PSTATE, :V$GRAD_YEAR, :V$PARENT_ID,
    :V$GIVEN_TO_LAST, :V$GIVEN_TO_FIRST, V$GIVEN_TO_MID, :V$REG_NUM, :V$PRINT_DATE, :V$CN, :V$NP, :V$IS_DUBL, :V$BSTATE, :V$SPO_REGNUM;
  IF(I$OBJ_ID IS NOT NULL AND V$OBJ_ID IS NULL) THEN EXCEPTION DATA_ERROR 'Запись аттестата не найдена';

  -- Если аттестата нет, проверяем не второгодник ли это и подтягиваем данные испорченного или удаленного аттестата, если он был
  IF(V$OBJ_ID IS NULL AND I$PERSON_ID IS NOT NULL) THEN
  BEGIN
    SELECT MON$DATABASE_NAME
    FROM MON$DATABASE
    INTO :V;

    V = REPLACE(V, 'DOC.FDB', 'BASE.FDB');
    V$IS_GIA = 0;

    EXECUTE STATEMENT
      'SELECT FIRST 1 1 FROM LINKS WHERE PARENT_TYPE_ID = 125 AND OBJ_TYPE_ID = 10 AND DATE_DEL IS NULL AND OBJ_ID = ' || I$PERSON_ID
    ON EXTERNAL :V
    -- ROLE 'INISSOFT_ROLE'
    WITH COMMON TRANSACTION
    INTO :V$IS_GIA;

    IF(V$IS_GIA = 1) THEN
    SELECT FIRST 1 D.DOC_DATA
    FROM DOCUMENTS D
    WHERE D.PERSON_ID = :I$PERSON_ID
    ORDER BY D.DATE_ADD DESC
    INTO :VAL;

  END
  SUSPEND;

  -- Напрямую OU_ID тут не узнать, поэтому шаманим
  V$OU_ID = NULL;
  V$ID = (SELECT COUNT(DISTINCT T.OU_ID) FROM PRINT_TEMPLATES T WHERE T.OU_ID <> 0 AND T.DATE_DEL IS NULL);
  IF(V$ID = 1) THEN
  BEGIN
    SELECT FIRST 1 T.OU_ID
    FROM PRINT_TEMPLATES T
    WHERE T.OU_ID <> 0 AND T.DATE_DEL IS NULL
    INTO :V$OU_ID;
  END

  TAG_NAME = 'formats::json&colon;array';
  VAL = '1';
  SUSPEND;
  FOR
    SELECT T.OBJ_ID, T.TYPE_ID, T.IS_DEFAULT, T.OBJ_NAME
    FROM PRINT_TEMPLATES T
    WHERE (T.DATE_DEL IS NULL OR T.OBJ_ID = :V$FORMAT_ID) AND (T.OU_ID = 0 OR T.OU_ID = :V$OU_ID)
    ORDER BY 4
    INTO :V$ID, :V$TID, :V$DEF, :S
  DO
  BEGIN
    TAG_NAME = 'formats:r';
    VAL = NULL;
    SUSPEND;
    TAG_NAME = 'formats:r:id';
    VAL = V$ID;
    SUSPEND;
    TAG_NAME = 'formats:r:type';
    VAL = V$TID;
    SUSPEND;
    TAG_NAME = 'formats:r:is_default';
    VAL = V$DEF;
    SUSPEND;
    TAG_NAME = 'formats:r:name';
    VAL = S;
    SUSPEND;
  END

  TAG_NAME = 'doc_id';
  VAL = V$OBJ_ID;
  SUSPEND;
  TAG_NAME = 'parent_id';
  VAL = V$PARENT_ID;
  SUSPEND;
  TAG_NAME = 'state_id';
  VAL = V$STATE_ID;
  SUSPEND;
  TAG_NAME = 'order_id';
  VAL = V$ORDER_ID;
  SUSPEND;
  TAG_NAME = 'bp_id';
  VAL = V$BP_ID;
  SUSPEND;
  TAG_NAME = 'blank_id';
  VAL = V$BLANK_ID;
  SUSPEND;
  TAG_NAME = 'blank_state';
  VAL = V$BSTATE;
  SUSPEND;
  TAG_NAME = 'blank_subtype';
  VAL = V$SUBTYPE;
  SUSPEND;
  TAG_NAME = 'blank_serie';
  VAL = V$BLANK_SERIE;
  SUSPEND;
  TAG_NAME = 'blank_num';
  VAL = V$BLANK_NUM;
  SUSPEND;
  TAG_NAME = 'blanknum'; -- полный номер аттестата
  VAL = V$BLANK_SERIE || V$BLANK_NUM;
  SUSPEND;
  TAG_NAME = 'add_nums';
  VAL = (SELECT ADD_NUMS FROM SDOCS_ADD_NUMS(:V$BLANK_ID, NULL));
  SUSPEND;
  TAG_NAME = 'person_id';
  VAL = V$PERSON_ID;
  SUSPEND;
  TAG_NAME = 'name_last';
  VAL = V$NAME_LAST;
  SUSPEND;
  TAG_NAME = 'name_first';
  VAL = V$NAME_FIRST;
  SUSPEND;
  TAG_NAME = 'name_mid';
  VAL = V$NAME_MID;
  SUSPEND;
  TAG_NAME = 'fio';
  VAL = V$NAME_LAST || ASCII_CHAR(10) || V$NAME_FIRST || COALESCE(' ' || V$NAME_MID, '');
  SUSPEND;
  TAG_NAME = 'given_to_last';
  VAL = V$GIVEN_TO_LAST;
  SUSPEND;
  TAG_NAME = 'given_to_first';
  VAL = V$GIVEN_TO_FIRST;
  SUSPEND;
  TAG_NAME = 'given_to_mid';
  VAL = V$GIVEN_TO_MID;
  SUSPEND;
  TAG_NAME = 'given_to';
  VAL = V$GIVEN_TO_LAST || ' ' || SUBSTRING(V$GIVEN_TO_FIRST FROM 1 FOR 1) || '.' || COALESCE(SUBSTRING(V$GIVEN_TO_MID FROM 1 FOR 1) || '.', '');
  SUSPEND;
  TAG_NAME = 'birth_date';
  VAL = (SELECT VAL FROM DATE2ISO(:V$BIRTH_DATE, 0));
  SUSPEND;
  TAG_NAME = 'year_id';
  VAL = V$YEAR_ID;
  SUSPEND;
  TAG_NAME = 'year_name';
  VAL = NULL;
  SELECT Y.VAL
  FROM STUDY_YEARS Y
  WHERE Y.LISTVAL_ID = :V$YEAR_ID
  INTO :VAL;
  SUSPEND;
  TAG_NAME = 'seq_no';
  VAL = V$SEQ_NO;
  SUSPEND;
  TAG_NAME = 'reg_num';
  VAL = V$REG_NUM;
  SUSPEND;
  TAG_NAME = 'reg_date_old';
  VAL = V$PRINT_DATE;
  SUSPEND;
  TAG_NAME = 'doc_type_id';
  VAL = V$DOC_TYPE_ID;
  SUSPEND;
  TAG_NAME = 'format_id';
  VAL = V$FORMAT_ID;
  SUSPEND;
  TAG_NAME = 'print_mask';
  VAL = V$PSTATE;
  SUSPEND;
  TAG_NAME = 'grad_year';
  VAL = V$GRAD_YEAR;
  SUSPEND;
  TAG_NAME = 'class_name';
  VAL = V$CN;
  SUSPEND;

  IF(V$DOC_TYPE_ID = 99999) THEN
  BEGIN
    TAG_NAME = 'ref_num';
    VAL = V$REG_NUM;
    SUSPEND;
    TAG_NAME = 'action_date';
    VAL = (SELECT VAL FROM DATE2ISO(:V$PRINT_DATE, 0));
    SUSPEND;
  END
  ELSE IF(V$DOC_TYPE_ID IN (12766, 12767)) THEN
  BEGIN
    TAG_NAME = 'spo_regnum';
    VAL = V$SPO_REGNUM;
    SUSPEND;
  END

  IF(V$PARENT_ID IS NOT NULL) THEN
  BEGIN
    TAG_NAME = 'prev_num';
    VAL = NULL;
    S = NULL;
    SELECT COALESCE(D.REG_NUM, BP.SERIE || B.BLANK_NUM), B.STATE_ID
    FROM DOCUMENTS D
    LEFT JOIN BLANKS B ON B.OBJ_ID = D.BLANK_ID
    LEFT JOIN BLANK_PACKS BP ON BP.OBJ_ID = B.PACK_ID
    WHERE D.OBJ_ID = :V$PARENT_ID
    INTO :VAL, :S;
    SUSPEND;
    TAG_NAME = 'prev_state';
    VAL = S;
    SUSPEND;
  END

  VAL = NULL;
  S = NULL;
  V$NAME_FIRST = NULL;
  V$NAME_LAST = NULL;
  V$NAME_MID = NULL;
  V$GIVEN_TO_LAST = NULL;
  V$GIVEN_TO_FIRST = NULL;
  V$BIRTH_DATE = NULL;
  V$GIVEN_TO_MID = NULL;
  SELECT FIRST 1 D.PREV_DOC, D.PROF_NAME, D.SPEC_NAME, D.OP_DUR, D.QUALIFICATION, D.UO, D.GAK_DATE, D.GAK_FIO, D.DPL_DATA
  FROM DOCUMENTS D
  WHERE D.OBJ_ID = :I$OBJ_ID
  INTO :V$NAME_FIRST, :S, :V$NAME_LAST, :V$NAME_MID, :V$GIVEN_TO_LAST, :V$GIVEN_TO_FIRST, :V$BIRTH_DATE, :V$GIVEN_TO_MID, :VAL;

  TAG_NAME = 'dpldata';
  -- VAL
  SUSPEND;

  TAG_NAME = 'prof';
  VAL = S;
  SUSPEND;

  TAG_NAME = 'prof_or_spec';
  IF(VAL IS NULL OR VAL = '') THEN VAL = 'специальности' || ASCII_CHAR(13) || V$NAME_LAST;
  ELSE VAL = 'профессии' || ASCII_CHAR(13) || VAL;
  SUSPEND;

  TAG_NAME = 'spec';
  VAL = V$NAME_LAST;
  SUSPEND;

  TAG_NAME = 'op_dur';
  VAL = V$NAME_MID;
  SUSPEND;

  TAG_NAME = 'qual';
  VAL = V$GIVEN_TO_LAST;
  SUSPEND;

  TAG_NAME = 'uo';
  VAL = V$GIVEN_TO_FIRST;
  SUSPEND;

  TAG_NAME = 'prev_doc';
  VAL = V$NAME_FIRST;
  SUSPEND;

  TAG_NAME = 'gak_date';
  VAL = (SELECT VAL FROM DATE2ISO(:V$BIRTH_DATE));
  SUSPEND;

  TAG_NAME = 'gak_fio';
  VAL = V$GIVEN_TO_MID;
  SUSPEND;

  TAG_NAME = 'n_pages';
  VAL = V$NP;
  SUSPEND;

  TAG_NAME = 'is_dubl';
  VAL = COALESCE(V$IS_DUBL, 0);
  SUSPEND;

  TAG_NAME = 'dubl_text';
  IF(V$IS_DUBL = 1) THEN VAL = 'ДУБЛИКАТ'; ELSE VAL = NULL;
  SUSPEND;

end
^

COMMIT WORK^


CREATE OR ALTER PROCEDURE SDOCS_DOCUMENT_GN (
  I$PERSON_ID BIGINT, 
  I$NAME_LAST VARCHAR(400) CHARACTER SET UTF8 COLLATE UTF8, 
  I$NAME_FIRST VARCHAR(400) CHARACTER SET UTF8 COLLATE UTF8, 
  I$NAME_MID VARCHAR(400) CHARACTER SET UTF8 COLLATE UTF8, 
  I$BIRTH_DATE DATE, 
  I$GRAD_YEAR INTEGER, 
  I$YEAR_ID BIGINT, 
  I$ACTION_DATE DATE, 
  I$REF_NUM VARCHAR(100) CHARACTER SET UTF8 COLLATE UTF8, 
  I$USER_ID VARCHAR(40) CHARACTER SET UTF8 COLLATE UTF8)
RETURNS (
  TAG_NAME VARCHAR(100) CHARACTER SET UTF8 COLLATE UTF8, 
  VAL VARCHAR(8000) CHARACTER SET UTF8 COLLATE UTF8)
AS
declare variable V$OU_ID bigint;
declare variable V$OBJ_ID bigint;
declare variable V varchar(400);
declare variable V$CLASS_NAME varchar(400);
declare variable V$D varchar(20);
begin

  V$OU_ID = I$PERSON_ID / 1000000000;

  IF(EXISTS(
    SELECT FIRST 1 1
    FROM DOCUMENTS D
    WHERE D.DATE_DEL IS NULL AND D.DOC_TYPE_ID = 99999 AND D.PERSON_ID = :I$PERSON_ID AND D.STATE_ID = 0
  ))
  THEN EXCEPTION DATA_ERROR 'Нельзя создать повторно справку об обучении, пока не завершена печать уже имеющейся справки';

  V$D = '01.09.' || (I$GRAD_YEAR - 1);
  IF(I$GRAD_YEAR IS NULL) THEN
  BEGIN
    I$GRAD_YEAR = EXTRACT(YEAR FROM I$ACTION_DATE);
    V$D = I$ACTION_DATE;
  END
  V$D = '''' || V$D || '''';

  SELECT MON$DATABASE_NAME
  FROM MON$DATABASE
  INTO :V;

  V = REPLACE(V, 'DOC.FDB', 'BASE.FDB');

  EXECUTE STATEMENT
    'SELECT FIRST 1 C.CAPTION
    FROM PERSON_MOVEMENTS M
    JOIN OBJECTS G ON G.OBJ_ID = M.GROUP_ID AND G.TYPE_ID IN (9, 187)
    LEFT JOIN OBJECT_CAPTION_GET(M.GROUP_ID) C ON 1 = 1
    WHERE M.DATE_DEL IS NULL AND M.PERSON_ID = ' || I$PERSON_ID || ' AND M.DATE_IN <= ' || V$D || ' AND (M.DATE_OUT IS NULL OR M.DATE_OUT >= ' || V$D || ') ORDER BY M.DATE_OUT DESC NULLS FIRST'
  ON EXTERNAL :V
  -- ROLE 'INISSOFT_ROLE'
  WITH COMMON TRANSACTION
  INTO :V$CLASS_NAME;

  -- Ищем класс, где персона была в тот год обучения
  IF(V$CLASS_NAME IS NULL) THEN
  BEGIN
  END

  INSERT INTO DOCUMENTS
  (OU_ID, STATE_ID, PERSON_ID, CLASS_NAME, NAME_LAST, NAME_FIRST, NAME_MID, BIRTH_DATE, YEAR_ID, DOC_TYPE_ID, GRAD_YEAR, PRINT_DATE, REG_NUM)
  VALUES (:V$OU_ID, 0, :I$PERSON_ID, :V$CLASS_NAME, :I$NAME_LAST, :I$NAME_FIRST, :I$NAME_MID, :I$BIRTH_DATE, :I$YEAR_ID, 99999, :I$GRAD_YEAR, :I$ACTION_DATE, :I$REF_NUM)
  RETURNING OBJ_ID INTO :V$OBJ_ID;

  FOR
    SELECT TAG_NAME, VAL
    FROM SDOCS_DOCUMENT_GET(NULL, :V$OBJ_ID, 1)
    INTO :TAG_NAME, :VAL
  DO
  BEGIN
    SUSPEND;
  END

end
^

COMMIT WORK^


CREATE OR ALTER PROCEDURE SDOCS_DOCUMENT_SET (
  I$OBJ_ID BIGINT, 
  I$OU_ID INTEGER, 
  I$YEAR_ID BIGINT, 
  I$ORDER_ID BIGINT, 
  I$PERSON_ID BIGINT NOT NULL, 
  I$NAME_LAST VARCHAR(200) CHARACTER SET UTF8 NOT NULL COLLATE UTF8, 
  I$NAME_FIRST VARCHAR(200) CHARACTER SET UTF8 NOT NULL COLLATE UTF8, 
  I$NAME_MID VARCHAR(200) CHARACTER SET UTF8 COLLATE UTF8, 
  I$BIRTH_DATE DATE NOT NULL, 
  I$DOC_TYPE_ID BIGINT NOT NULL, 
  I$BP_ID BIGINT, 
  I$BLANK_NUM VARCHAR(40) CHARACTER SET UTF8 COLLATE UTF8, 
  I$FORMAT_ID BIGINT, 
  I$GRAD_YEAR SMALLINT, 
  I$DATA VARCHAR(8000) CHARACTER SET UTF8 COLLATE UTF8, 
  I$USER_ID VARCHAR(40) CHARACTER SET UTF8 COLLATE UTF8, 
  I$REF_NUM VARCHAR(100) CHARACTER SET UTF8 COLLATE UTF8 = null, 
  I$ACTION_DATE DATE = null, 
  I$REG_NUM VARCHAR(100) CHARACTER SET UTF8 COLLATE UTF8 = null)
RETURNS (
  OBJ_ID BIGINT, 
  BLANK_ID BIGINT)
AS
declare variable V$STATE_ID smallint;
declare variable V$PERSON_ID bigint;
declare variable V$BP_ID bigint;
declare variable V$BLANK_NUM varchar(40);
declare variable V$BLANK_STATE smallint;
declare variable V$NUM_FROM bigint;
declare variable V$NUM_TO bigint;
declare variable V$N bigint;
declare variable V$YEAR_ID bigint;
declare variable V$CLASS_NAME varchar(100);
declare variable V varchar(400);
begin

  SELECT FIRST 1 D.STATE_ID, D.BLANK_ID, D.PERSON_ID, B.PACK_ID, B.BLANK_NUM, D.YEAR_ID
  FROM DOCUMENTS D
  LEFT JOIN BLANKS B ON B.OBJ_ID = D.BLANK_ID
  WHERE D.OBJ_ID = :I$OBJ_ID AND D.DATE_DEL IS NULL
  INTO :V$STATE_ID, :BLANK_ID, :V$PERSON_ID, :V$BP_ID, :V$BLANK_NUM, :V$YEAR_ID;

  IF(I$OBJ_ID IS NOT NULL AND V$STATE_ID IS NULL) THEN EXCEPTION DATA_ERROR 'Аттестат не найден или был удален';

  IF(I$OBJ_ID IS NOT NULL) THEN
  BEGIN
    IF(I$PERSON_ID <> V$PERSON_ID) THEN EXCEPTION DATA_ERROR 'Нельзя менять персону в документе';
    IF(I$YEAR_ID <> V$YEAR_ID) THEN EXCEPTION DATA_ERROR 'Нельзя менять учебный год, удалите документ и создайте новый';

    IF(I$BP_ID IS NOT NULL OR I$BLANK_NUM IS NOT NULL) THEN
    BEGIN
      IF(COALESCE(I$BP_ID, 0) <> COALESCE(V$BP_ID, 0) OR COALESCE(I$BLANK_NUM, '') <> COALESCE(V$BLANK_NUM, '')) THEN
        EXCEPTION DATA_ERROR 'Замена бланка в аттестате выполняется только через аннулирование или отмену статуса старого бланка';
    END
  END

  -- Ищем запись бланка, создаем экземпляр
  IF(I$BP_ID IS NOT NULL AND V$BP_ID IS NULL) THEN
  BEGIN
    SELECT FIRST 1 B.STATE_ID
    FROM BLANKS B
    WHERE B.DATE_DEL IS NULL AND B.PACK_ID = :I$BP_ID AND B.BLANK_NUM = :I$BLANK_NUM
    INTO :V$BLANK_STATE;

    IF(V$BLANK_STATE = 1) THEN EXCEPTION DATA_ERROR 'Указанный номер бланка отмечен как использованный';
    IF(V$BLANK_STATE = 2) THEN EXCEPTION DATA_ERROR 'Указанный номер бланка отмечен как аннулированный';

    IF(V$BLANK_STATE IS NULL) THEN
    BEGIN
      SELECT P.NUM_FROM, P.NUM_TO
      FROM BLANK_PACKS P
      WHERE P.DATE_DEL IS NULL AND P.OBJ_ID = :I$BP_ID
      INTO :V$NUM_FROM, :V$NUM_TO;

      IF(V$NUM_FROM IS NULL) THEN EXCEPTION DATA_ERROR 'Пакет бланков не найден';
      V$N = I$BLANK_NUM;
      IF(V$N < V$NUM_FROM OR V$N > V$NUM_TO) THEN EXCEPTION DATA_ERROR 'Номер бланка выходит за пределы допустимых в пакете';

      INSERT INTO BLANKS
      (PACK_ID, STATE_ID, BLANK_NUM, BLANK_INDEX)
      VALUES (:I$BP_ID, 0, :I$BLANK_NUM, :V$N)
      RETURNING OBJ_ID INTO :BLANK_ID;
    END
  END

  IF(I$GRAD_YEAR < 1900 OR I$GRAD_YEAR > EXTRACT(YEAR FROM CURRENT_DATE)) THEN EXCEPTION DATA_ERROR 'Неверный год окончания';

  IF(I$OBJ_ID IS NULL) THEN
  BEGIN
    -- Нумерацию в книге выдачи делаем не тут, а уже после печати
    IF(EXISTS(
      SELECT FIRST 1 1
      FROM DOCUMENTS D
      WHERE D.DATE_DEL IS NULL AND D.YEAR_ID = :I$YEAR_ID AND D.PERSON_ID = :I$PERSON_ID AND D.STATE_ID = 0
    ))
    THEN EXCEPTION DATA_ERROR 'Системная ошибка. Запись документа для данного обучающегося уже есть';

    SELECT MON$DATABASE_NAME
    FROM MON$DATABASE
    INTO :V;

    V = REPLACE(V, 'DOC.FDB', 'BASE.FDB');

    EXECUTE STATEMENT
      'SELECT FIRST 1 C.CAPTION
      FROM LINKS LP9
      LEFT JOIN OBJECT_CAPTION_GET(LP9.PARENT_ID) C ON 1 = 1
      WHERE LP9.DATE_DEL IS NULL AND LP9.PARENT_TYPE_ID IN (9, 125, 187) AND LP9.OBJ_ID = ' || I$PERSON_ID
    ON EXTERNAL :V
    -- ROLE 'INISSOFT_ROLE'
    WITH COMMON TRANSACTION
    INTO :V$CLASS_NAME;

    -- При добавлении REF_DATE и ACTION_DATE не используются

    INSERT INTO DOCUMENTS
    (OU_ID, STATE_ID, ORDER_ID, BLANK_ID, PERSON_ID, NAME_LAST, NAME_FIRST, NAME_MID, BIRTH_DATE, YEAR_ID, DOC_TYPE_ID, DOC_DATA, FORMAT_ID, GRAD_YEAR, CLASS_NAME, PRINT_DATE, SPO_REGNUM)
    VALUES (:I$OU_ID, 0, :I$ORDER_ID, :BLANK_ID, :I$PERSON_ID, :I$NAME_LAST, :I$NAME_FIRST, :I$NAME_MID, :I$BIRTH_DATE, :I$YEAR_ID, :I$DOC_TYPE_ID, :I$DATA, :I$FORMAT_ID, :I$GRAD_YEAR, :V$CLASS_NAME, NULL, :I$REG_NUM)
    RETURNING OBJ_ID INTO :OBJ_ID;
  END
  ELSE
  BEGIN
    -- IF(V$STATE_ID <> 0)

    UPDATE DOCUMENTS
    SET ORDER_ID = :I$ORDER_ID, BLANK_ID = BLANK_ID, NAME_LAST = :I$NAME_LAST, NAME_FIRST = :I$NAME_FIRST, NAME_MID = :I$NAME_MID, BIRTH_DATE = :I$BIRTH_DATE, DOC_TYPE_ID = :I$DOC_TYPE_ID, DOC_DATA = :I$DATA,
      FORMAT_ID = :I$FORMAT_ID, GRAD_YEAR = :I$GRAD_YEAR, SPO_REGNUM = :I$REG_NUM
    WHERE OBJ_ID = :I$OBJ_ID;

    IF(I$DOC_TYPE_ID = 99999) THEN
    UPDATE DOCUMENTS
    SET REG_NUM = :I$REF_NUM, PRINT_DATE = :I$ACTION_DATE
    WHERE OBJ_ID = :I$OBJ_ID;

    OBJ_ID = :I$OBJ_ID;
  END

  SUSPEND;

end
^

COMMIT WORK^


CREATE OR ALTER PROCEDURE SDOCS_DOCUMENT_SET_SPO (
  I$DOC_ID BIGINT, 
  I$PROF VARCHAR(400) CHARACTER SET UTF8 COLLATE UTF8, 
  I$SPEC VARCHAR(400) CHARACTER SET UTF8 COLLATE UTF8, 
  I$OP_DUR VARCHAR(100) CHARACTER SET UTF8 COLLATE UTF8, 
  I$QUAL VARCHAR(200) CHARACTER SET UTF8 COLLATE UTF8, 
  I$UO VARCHAR(100) CHARACTER SET UTF8 COLLATE UTF8, 
  I$PREV_DOC VARCHAR(100) CHARACTER SET UTF8 COLLATE UTF8, 
  I$GAK_DATE DATE, 
  I$GAK_FIO VARCHAR(200) CHARACTER SET UTF8 COLLATE UTF8, 
  I$DPLDATA VARCHAR(4000) CHARACTER SET UTF8 COLLATE UTF8)
AS
begin

  UPDATE DOCUMENTS
  SET PROF_NAME = :I$PROF, SPEC_NAME = :I$SPEC, OP_DUR = :I$OP_DUR, QUALIFICATION = :I$QUAL, UO = :I$UO, PREV_DOC = :I$PREV_DOC,
    GAK_DATE = :I$GAK_DATE, GAK_FIO = :I$GAK_FIO, DPL_DATA = :I$DPLDATA
  WHERE OBJ_ID = :I$DOC_ID;

end
^

COMMIT WORK^


CREATE OR ALTER PROCEDURE SDOCS_DUB_DATA (
  I$ORDER_ID BIGINT, 
  I$PERSON_ID BIGINT)
RETURNS (
  PREV_FIO VARCHAR(400) CHARACTER SET UTF8 COLLATE UTF8, 
  PREV_BD DATE, 
  NEW_FIO VARCHAR(400) CHARACTER SET UTF8 COLLATE UTF8, 
  NEW_BD DATE, 
  GRAD_YEAR INTEGER, 
  PREV_DATE DATE, 
  PREV_NUM VARCHAR(100) CHARACTER SET UTF8 COLLATE UTF8, 
  BLANK_NUM VARCHAR(100) CHARACTER SET UTF8 COLLATE UTF8)
AS
declare variable V$PARENT_ID bigint;
begin
  -- Функция вызывается если в приказе один человек, но это необязательно выдача дубликата
  SELECT FIRST 1 D.NAME_LAST || ' ' || D.NAME_FIRST || COALESCE(' ' || D.NAME_MID, ''), D.BIRTH_DATE, D.PARENT_ID, D.GRAD_YEAR, BP.SERIE || B.BLANK_NUM
  FROM DOCUMENTS D
  LEFT JOIN BLANKS B ON B.OBJ_ID = D.BLANK_ID AND B.DATE_DEL IS NULL
  LEFT JOIN BLANK_PACKS BP ON BP.OBJ_ID = B.PACK_ID AND BP.DATE_DEL IS NULL
  WHERE D.DATE_DEL IS NULL AND D.ORDER_ID = :I$ORDER_ID AND D.PERSON_ID = :I$PERSON_ID AND (D.PARENT_ID IS NOT NULL OR D.IS_DUBL = 1)
  INTO :NEW_FIO, :NEW_BD, :V$PARENT_ID, :GRAD_YEAR, :BLANK_NUM;

  -- Если нет родительского документа, значит это не дубликат
  -- скидываем GRAD_YEAR, что будет означать что приказ обычный
  IF(V$PARENT_ID IS NULL) THEN
  BEGIN
    GRAD_YEAR = NULL;
  END
  ELSE
  BEGIN
    SELECT D.NAME_LAST || ' ' || D.NAME_FIRST || COALESCE(' ' || D.NAME_MID, ''), D.BIRTH_DATE, COALESCE(BP.SERIE || B.BLANK_NUM, D.REG_NUM), D.ACTION_DATE
    FROM DOCUMENTS D
    LEFT JOIN BLANKS B ON B.OBJ_ID = D.BLANK_ID AND B.DATE_DEL IS NULL
    LEFT JOIN BLANK_PACKS BP ON BP.OBJ_ID = B.PACK_ID AND BP.DATE_DEL IS NULL
    WHERE D.OBJ_ID = :V$PARENT_ID AND D.DATE_DEL IS NULL
    INTO :PREV_FIO, :PREV_BD, :PREV_NUM, :PREV_DATE;
  END

  SUSPEND;
end
^

COMMIT WORK^


CREATE OR ALTER PROCEDURE SDOCS_EXPORT_FRDO (
  I$YEAR_ID BIGINT, 
  I$STAGE_ID BIGINT, 
  I$VALID INTEGER, 
  I$DATE_FROM DATE, 
  I$DATE_TO DATE)
RETURNS (
  DOC_TYPE_ID BIGINT, 
  DOC_NAME VARCHAR(100) CHARACTER SET UTF8 COLLATE UTF8, 
  DOC_TYPE_NAME VARCHAR(100) CHARACTER SET UTF8 COLLATE UTF8, 
  DOC_STATUS VARCHAR(30) CHARACTER SET UTF8 COLLATE UTF8, 
  F_LOSS VARCHAR(10) CHARACTER SET UTF8 COLLATE UTF8, 
  F_CHANGE VARCHAR(10) CHARACTER SET UTF8 COLLATE UTF8, 
  F_DROP VARCHAR(10) CHARACTER SET UTF8 COLLATE UTF8, 
  STAGE VARCHAR(100) CHARACTER SET UTF8 COLLATE UTF8, 
  DOC_SERIE VARCHAR(20) CHARACTER SET UTF8 COLLATE UTF8, 
  DOC_NUM VARCHAR(40) CHARACTER SET UTF8 COLLATE UTF8, 
  GIVE_DATE DATE, 
  REG_NUM VARCHAR(40) CHARACTER SET UTF8 COLLATE UTF8, 
  YEAR_START INTEGER, 
  YEAR_FINISH INTEGER, 
  NAME_LAST VARCHAR(200) CHARACTER SET UTF8 COLLATE UTF8, 
  NAME_FIRST VARCHAR(200) CHARACTER SET UTF8 COLLATE UTF8, 
  NAME_MID VARCHAR(200) CHARACTER SET UTF8 COLLATE UTF8, 
  BIRTH_DATE DATE, 
  SNILS VARCHAR(20) CHARACTER SET UTF8 COLLATE UTF8, 
  CITIZENSHIP VARCHAR(40) CHARACTER SET UTF8 COLLATE UTF8, 
  EDU_AVAIL VARCHAR(40) CHARACTER SET UTF8 COLLATE UTF8, 
  GENDER VARCHAR(20) CHARACTER SET UTF8 COLLATE UTF8, 
  PAY_SRC VARCHAR(100) CHARACTER SET UTF8 COLLATE UTF8, 
  EDU_FORM VARCHAR(50) CHARACTER SET UTF8 COLLATE UTF8, 
  PREV_DOC_NAME VARCHAR(50) CHARACTER SET UTF8 COLLATE UTF8, 
  PREV_SERIE VARCHAR(20) CHARACTER SET UTF8 COLLATE UTF8, 
  PREV_NUM VARCHAR(20) CHARACTER SET UTF8 COLLATE UTF8, 
  PREV_REG_NUM VARCHAR(40) CHARACTER SET UTF8 COLLATE UTF8, 
  PREV_DATE DATE, 
  PREV_NAME_LAST VARCHAR(200) CHARACTER SET UTF8 COLLATE UTF8, 
  PREV_NAME_FIRST VARCHAR(200) CHARACTER SET UTF8 COLLATE UTF8, 
  PREV_NAME_MID VARCHAR(200) CHARACTER SET UTF8 COLLATE UTF8, 
  PROF_NAME VARCHAR(400) CHARACTER SET UTF8 COLLATE UTF8, 
  SPEC_NAME VARCHAR(400) CHARACTER SET UTF8 COLLATE UTF8, 
  QUALIFICATION VARCHAR(200) CHARACTER SET UTF8 COLLATE UTF8)
AS
DECLARE VARIABLE V$OBJ_ID BIGINT;
DECLARE VARIABLE V$PREV_ID BIGINT;
DECLARE VARIABLE V$PERSON_ID BIGINT;
DECLARE VARIABLE V$IS_DUBL SMALLINT;
DECLARE VARIABLE V$REG_PREFIX VARCHAR(20);
DECLARE VARIABLE V$ORDER_ID BIGINT;
DECLARE VARIABLE V$EDU_AVAIL BIGINT;
DECLARE VARIABLE V$PAY_ID BIGINT;
DECLARE VARIABLE V$OU_ID BIGINT;
DECLARE VARIABLE V$EDU_FORM BIGINT;
DECLARE VARIABLE V VARCHAR(400);
DECLARE VARIABLE V$OU_LEVEL VARCHAR(400);
DECLARE VARIABLE V$PREV_NUM VARCHAR(40);
DECLARE VARIABLE V$PREV_TYPE BIGINT;
DECLARE VARIABLE V$PREV_SEQ VARCHAR(40);
DECLARE VARIABLE V$PREV_YEAR VARCHAR(40);
DECLARE VARIABLE V$TYPE1 BIGINT;
DECLARE VARIABLE V$TYPE2 BIGINT;
DECLARE VARIABLE V$TYPE3 BIGINT;
DECLARE VARIABLE V$DUB_STATE SMALLINT;
DECLARE VARIABLE V$FAILED SMALLINT;
begin

  -- Документы в статусе 3, которые внесены без полной регистрации при выдаче дубликатов доисторических документов - не выгружаем
  -- DOC_TYPE_ID нужен чисто для фильтрации

  SELECT Y.VAL
  FROM STUDY_YEARS Y
  WHERE Y.LISTVAL_ID = :I$YEAR_ID
  INTO :V$REG_PREFIX;

  SELECT MON$DATABASE_NAME
  FROM MON$DATABASE
  INTO :V;

  V = REPLACE(V, 'DOC.FDB', 'BASE.FDB');

  EXECUTE STATEMENT
    'SELECT FIRST 1 L2.OBJ_ID, V.V
    FROM LINKS L2
    LEFT JOIN VAL_GET(L2.OBJ_ID, 8) V ON 1 = 1
    WHERE L2.PARENT_ID IS NULL AND L2.OBJ_TYPE_ID = 2 AND L2.DATE_DEL IS NULL'
  ON EXTERNAL :V
  WITH COMMON TRANSACTION
  INTO :V$OU_ID, :V$OU_LEVEL;

  IF(I$STAGE_ID = 26) THEN
  BEGIN
    V$TYPE1 = 5377;
    V$TYPE2 = 5378;
    V$TYPE3 = 5385;
  END
  IF(I$STAGE_ID = 27) THEN
  BEGIN
    V$TYPE1 = 5379;
    V$TYPE2 = 5380;
  END
  IF(I$STAGE_ID = 29) THEN
  BEGIN
    V$TYPE1 = 12766;
    V$TYPE2 = 12767;
  END

  -- Не выдаем в выборку данные о старых аттестатах, которые были внесены в систему частично при выдаче дубликатов
  -- NULL - все
  -- 1 - действующие не-дубликаты
  -- 0 - не действующие не-дубликаты
  -- 2 - дубликаты

  -- Документы, которые были испорчены, вообще не выгружаются никуда

  FOR
    SELECT D.OBJ_ID, D.DOC_TYPE_ID, D.PERSON_ID, D.ORDER_ID, D.SEQ_NO, P.SERIE, B.BLANK_NUM, D.NAME_LAST, D.NAME_FIRST, COALESCE(D.NAME_MID, 'Нет'), D.BIRTH_DATE, D.GRAD_YEAR,
      D.PARENT_ID, D.IS_DUBL, COALESCE(D.DUB_STATE, 0), D.PROF_NAME, D.SPEC_NAME, D.QUALIFICATION, IIF(B.STATE_ID = 2, 1, 0)
    FROM DOCUMENTS D
    JOIN BLANKS B ON B.OBJ_ID = D.BLANK_ID
    JOIN BLANK_PACKS P ON P.OBJ_ID = B.PACK_ID
    WHERE D.DATE_DEL IS NULL AND D.SEQ_NO IS NOT NULL AND D.YEAR_ID = :I$YEAR_ID AND D.ORDER_ID IS NOT NULL AND
      (
        (D.STATE_ID IN (1, 4) AND B.STATE_ID IN (0, 1) AND (:I$VALID IS NULL OR :I$VALID IN (1, 2)))
        OR ((D.STATE_ID = 2 OR B.STATE_ID IN (2, 3)) AND (:I$VALID IS NULL OR :I$VALID IN (0, 2)))
      )
      AND ((D.IS_DUBL = 0 AND (:I$VALID IS NULL OR :I$VALID IN (0, 1))) OR (D.IS_DUBL = 1 AND (:I$VALID IS NULL OR :I$VALID = 2)))
      AND (D.DOC_TYPE_ID = :V$TYPE1 OR D.DOC_TYPE_ID = :V$TYPE2 OR D.DOC_TYPE_ID = :V$TYPE3 OR :V$TYPE1 IS NULL)
      AND (:I$DATE_FROM IS NULL OR D.ACTION_DATE >= :I$DATE_FROM)
      AND (:I$DATE_TO IS NULL OR D.ACTION_DATE <= :I$DATE_TO)
    ORDER BY D.OBJ_ID
    INTO :V$OBJ_ID, :DOC_TYPE_ID, :V$PERSON_ID, :V$ORDER_ID, :REG_NUM, :DOC_SERIE, :DOC_NUM, :NAME_LAST, :NAME_FIRST, :NAME_MID, :BIRTH_DATE, :YEAR_FINISH,
      :V$PREV_ID, :V$IS_DUBL, :V$DUB_STATE, :PROF_NAME, :SPEC_NAME, :QUALIFICATION, :V$FAILED
  DO
  BEGIN
    SELECT CAST(LV.VAL AS VARCHAR(100)), LEFT(LV.VAL, POSITION(' ', LV.VAL) - 1)
    FROM BLANK_TYPES LV
    WHERE LV.LISTVAL_ID = :DOC_TYPE_ID
    INTO :DOC_TYPE_NAME, :DOC_NAME;

    -- Уровень образования
    IF(DOC_TYPE_ID IN (5377, 5378, 5385)) THEN STAGE = 'Основное общее образование';
    ELSE IF(DOC_TYPE_ID IN (5379, 5380)) THEN STAGE = 'Среднее общее образование';
    ELSE IF(DOC_TYPE_ID IN (12766, 12767)) THEN STAGE = 'Среднее профессиональное образование';
    ELSE STAGE = '';

    IF(V$IS_DUBL = 0) THEN DOC_STATUS = 'Оригинал'; ELSE DOC_STATUS = 'Дубликат';

    -- Регистрационный номер формируем из порядкового номера и номера учебного года
    REG_NUM = V$REG_PREFIX || '/' || REG_NUM;

    F_LOSS = 'Нет'; -- Подтверждение утраты
    F_CHANGE = 'Нет'; -- Подтверждение обмена
    F_DROP = 'Нет'; -- Подтверждение уничтожения, не ставим никогда

    -- проверяем наличие дубликата, выписанного к этому документу (либо перевыпуск бланка)
    IF(V$IS_DUBL = 1) THEN
    BEGIN
      F_LOSS = 'Да'; -- Подтверждение утраты
      F_CHANGE = 'Да'; -- Подтверждение обмена
    END

    GIVE_DATE = NULL;
    EXECUTE STATEMENT
      'SELECT D.ACTION_DATE FROM ORDERS D WHERE D.OBJ_ID = ' || V$ORDER_ID
    ON EXTERNAL :V
    WITH COMMON TRANSACTION
    INTO :GIVE_DATE;

    -- Пытаемся найти год поступления
    YEAR_START = NULL;
    EXECUTE STATEMENT
      'SELECT FIRST 1 EXTRACT(YEAR FROM M.DATE_IN)
      FROM PERSON_MOVEMENTS M
      JOIN OBJECTS G ON G.OBJ_ID = M.GROUP_ID AND G.TYPE_ID = 9
      WHERE M.DATE_DEL IS NULL AND M.PERSON_ID = ' || V$PERSON_ID || ' AND M.REASON_IN IN (805, 806, 3140, 5322)
      ORDER BY M.DATE_IN'
    ON EXTERNAL :V
    WITH COMMON TRANSACTION
    INTO :YEAR_START;

    GENDER = NULL;
    EXECUTE STATEMENT
      'SELECT UPPER(LEFT(V, 1)) || SUBSTRING(V FROM 2 FOR 2) FROM VAL_GET (' || V$PERSON_ID || ', 88)'
    ON EXTERNAL :V
    WITH COMMON TRANSACTION
    INTO :GENDER;

    CITIZENSHIP = NULL;
    EXECUTE STATEMENT
      'SELECT LV.TAG FROM VALL V91 JOIN LISTVALS LV ON LV.LISTVAL_ID = V91.LISTVAL_ID WHERE V91.OBJ_ID = ' || V$PERSON_ID || ' AND V91.PARAM_ID = 91 AND V91.IS_DEL = 0'
    ON EXTERNAL :V
    WITH COMMON TRANSACTION
    INTO :CITIZENSHIP;

    SNILS = NULL;
    EXECUTE STATEMENT
      'SELECT V FROM VAL_GET (' || V$PERSON_ID || ', 1409)'
    ON EXTERNAL :V
    WITH COMMON TRANSACTION
    INTO :SNILS;

    -- Форма получения образования
    V$EDU_AVAIL = NULL;
    EXECUTE STATEMENT
      'SELECT FIRST 1 V1888.LISTVAL_ID FROM VALL V1888 WHERE V1888.OBJ_ID = ' || V$PERSON_ID || ' AND V1888.PARAM_ID = 1888 AND V1888.IS_DEL = 0'
    ON EXTERNAL :V
    WITH COMMON TRANSACTION
    INTO :V$EDU_AVAIL;

    IF(V$EDU_AVAIL = 5421) THEN EDU_AVAIL = 'вне образовательной организации'; ELSE EDU_AVAIL = 'в образовательной организации';

    -- Форма финансирования
    -- в школах если не указано = смотрим подчинение
    V$PAY_ID = NULL;
    EXECUTE STATEMENT
      'SELECT FIRST 1 V1812.LISTVAL_ID FROM VALL V1812 WHERE V1812.OBJ_ID = ' || V$PERSON_ID || ' AND V1812.PARAM_ID = 1812 AND V1812.IS_DEL = 0'
    ON EXTERNAL :V
    WITH COMMON TRANSACTION
    INTO :V$PAY_ID;

    IF(V$PAY_ID IS NULL) THEN
    BEGIN
      IF(POSITION('едерал' IN V$OU_LEVEL) > 0) THEN V$PAY_ID = 5257;
      ELSE IF(POSITION('егион' IN V$OU_LEVEL) > 0) THEN V$PAY_ID = 5258;
      ELSE IF(POSITION('униципа' IN V$OU_LEVEL) > 0) THEN V$PAY_ID = 5259;
      ELSE V$PAY_ID = 0;
    END

    IF(V$PAY_ID = 5257) THEN PAY_SRC = 'Федеральный бюджет';
    ELSE IF(V$PAY_ID = 5258) THEN PAY_SRC = 'Региональный бюджет';
    ELSE IF(V$PAY_ID = 5259) THEN PAY_SRC = 'Местный бюджет';
    ELSE PAY_SRC = '';

    -- Форма обучения
    V$EDU_FORM = NULL;
    EXECUTE STATEMENT
      'SELECT FIRST 1 V80.LISTVAL_ID FROM VALL V80 WHERE V80.OBJ_ID = ' || V$PERSON_ID || ' AND V80.PARAM_ID = 80 AND V80.IS_DEL = 0'
    ON EXTERNAL :V
    WITH COMMON TRANSACTION
    INTO :V$EDU_FORM;

    IF(V$EDU_FORM = 5418) THEN EDU_FORM = 'Очная';
    ELSE IF(V$EDU_FORM = 5419) THEN EDU_FORM = 'Очно-заочная (вечерняя)';
    ELSE IF(V$EDU_FORM = 5420) THEN EDU_FORM = 'Заочная';
    ELSE EDU_FORM = '';

    -- Изменения 2023, начиная с 2021 года выдачи серию пишем "Нет"
    IF(DOC_TYPE_ID NOT IN (12766, 12767) AND EXTRACT(YEAR FROM GIVE_DATE) >= 2021) THEN
    BEGIN
      DOC_NUM = DOC_SERIE || DOC_NUM;
      DOC_SERIE = 'Нет';
    END

    -- Данные по предыдущему документу
    PREV_DOC_NAME = NULL;
    PREV_SERIE = NULL;
    PREV_NUM = NULL;
    PREV_REG_NUM = NULL;
    PREV_DATE = NULL;
    PREV_NAME_LAST = NULL;
    PREV_NAME_FIRST = NULL;
    PREV_NAME_MID = NULL;
    V$ORDER_ID = NULL;
    V$PREV_NUM = NULL;
    V$PREV_SEQ = NULL;
    V$PREV_YEAR = NULL;

    IF(V$IS_DUBL = 1) THEN
    BEGIN

      SELECT D.DOC_TYPE_ID, D.NAME_LAST, D.NAME_FIRST, COALESCE(D.NAME_MID, 'Нет'), D.ORDER_ID, D.PRINT_DATE, P.SERIE, B.BLANK_NUM, D.REG_NUM, D.SEQ_NO, Y.VAL
      FROM DOCUMENTS D
      LEFT JOIN BLANKS B ON B.OBJ_ID = D.BLANK_ID
      LEFT JOIN BLANK_PACKS P ON P.OBJ_ID = B.PACK_ID
      LEFT JOIN STUDY_YEARS Y ON Y.LISTVAL_ID = D.YEAR_ID
      WHERE D.OBJ_ID = :V$PREV_ID
      INTO :V$PREV_TYPE, :PREV_NAME_LAST, :PREV_NAME_FIRST, :PREV_NAME_MID, :V$ORDER_ID, :PREV_DATE, :PREV_SERIE, :PREV_NUM, :V$PREV_NUM, :V$PREV_SEQ, :V$PREV_YEAR;

      -- Достаем номер из внесенного вручную, а с серией пусть сами разбираются
      IF(V$PREV_NUM IS NOT NULL AND PREV_SERIE IS NULL) THEN
      BEGIN
        PREV_NUM = TRIM(V$PREV_NUM);
        IF(POSITION(' ' IN PREV_NUM) > 0) THEN
        BEGIN
          PREV_SERIE = SUBSTRING(PREV_NUM FROM 1 FOR POSITION(' ' IN PREV_NUM) - 1);
          PREV_NUM = SUBSTRING(PREV_NUM FROM POSITION(' ' IN PREV_NUM) + 1);
        END
      END

      -- Если есть ORDER_ID, то дату берем из приказа. Если нет, то оставляем из PRINT_DATE
      IF(V$ORDER_ID IS NOT NULL) THEN
      BEGIN
        EXECUTE STATEMENT
          'SELECT D.ACTION_DATE FROM ORDERS D WHERE D.OBJ_ID = ' || V$ORDER_ID
        ON EXTERNAL :V
        WITH COMMON TRANSACTION
        INTO :PREV_DATE;
      END

      SELECT LV.VAL--LEFT(LV.VAL, POSITION(' ', LV.VAL))
      FROM BLANK_TYPES LV
      WHERE LV.LISTVAL_ID = :V$PREV_TYPE
      INTO :PREV_DOC_NAME;

      IF(V$PREV_SEQ IS NOT NULL) THEN PREV_REG_NUM = V$PREV_YEAR || '/' || V$PREV_SEQ;

    END

    SUSPEND;
  END

end
^

COMMIT WORK^


CREATE OR ALTER PROCEDURE SDOCS_MIGRATE_BLANK (
  I$TYPE_ID BIGINT, 
  I$OU_ID BIGINT, 
  I$SERIE VARCHAR(40) CHARACTER SET UTF8 COLLATE UTF8, 
  I$NUM VARCHAR(40) CHARACTER SET UTF8 COLLATE UTF8, 
  I$NFROM VARCHAR(40) CHARACTER SET UTF8 COLLATE UTF8, 
  I$NTO VARCHAR(40) CHARACTER SET UTF8 COLLATE UTF8, 
  I$DATE DATE, 
  I$SUPPLIER VARCHAR(4000) CHARACTER SET UTF8 COLLATE UTF8, 
  I$STATE BIGINT)
RETURNS (
  OBJ_ID BIGINT)
AS
declare variable V$OBJ_ID bigint;
declare variable V$N1 bigint;
declare variable V$N2 bigint;
declare variable V$N bigint;
declare variable V$STATE_ID bigint;
declare variable V$STAGE bigint;
begin
  SELECT FIRST 1 BP.OBJ_ID
  FROM BLANK_PACKS BP
  WHERE BP.DATE_DEL IS NULL AND BP.SERIE = :I$SERIE AND BP.NUM_FROM = :I$NFROM AND BP.TYPE_ID = :I$TYPE_ID -- AND BP.NUM_TO = :I$NTO
  INTO :V$OBJ_ID;

  V$N1 = I$NFROM;
  V$N2 = I$NTO;
  V$N = I$NUM;

  IF(V$OBJ_ID IS NULL) THEN
  BEGIN
    IF(I$TYPE_ID IN (5377, 5378, 5385)) THEN V$STAGE = 26;
    ELSE IF(I$TYPE_ID IN (5379, 5380)) THEN V$STAGE = 27;
    ELSE IF(I$TYPE_ID IN (12766, 12767)) THEN V$STAGE = 29;

    INSERT INTO BLANK_PACKS
    (OU_ID, ACCEPT_DATE, ACCEPT_FROM, SERIE, NUM_FROM, NUM_TO, TYPE_ID, STAGE_ID, BLANKS_NUM, BLANKS_USED)
    VALUES (:I$OU_ID, :I$DATE, :I$SUPPLIER, :I$SERIE, :I$NFROM, :I$NTO, :I$TYPE_ID, :V$STAGE, :V$N2 - :V$N1 + 1, 0)
    RETURNING OBJ_ID INTO :V$OBJ_ID;
  END
  ELSE
  BEGIN
    UPDATE BLANK_PACKS
    SET NUM_TO = :I$NTO, BLANKS_NUM = :V$N2 - :V$N1 + 1,
      BLANKS_USED = (SELECT COUNT(*) FROM BLANKS WHERE BLANKS.PACK_ID = BLANK_PACKS.OBJ_ID AND BLANKS.DATE_DEL IS NULL)
    WHERE OBJ_ID = :V$OBJ_ID;
  END

  SELECT FIRST 1 OBJ_ID, STATE_ID
  FROM BLANKS B
  WHERE B.DATE_DEL IS NULL AND B.PACK_ID = :V$OBJ_ID AND B.BLANK_INDEX = :V$N
  INTO :OBJ_ID, :V$STATE_ID;

  -- Статус бланка смотрим через I$STATE_ID (параметр 1880)
  IF(I$STATE = 5441) THEN I$STATE = 2; -- испорчен
  ELSE IF(I$STATE = 13149) THEN I$STATE = 3; -- аннулирован, но тут такого случая точно быть не может
  ELSE I$STATE = 1; -- все остальные случаи

  IF(OBJ_ID IS NULL) THEN
  BEGIN
    INSERT INTO BLANKS
    (PACK_ID, BLANK_INDEX, BLANK_NUM, STATE_ID, USE_DATE)
    VALUES (:V$OBJ_ID, :V$N, :I$NUM, :I$STATE, NULL)
    RETURNING OBJ_ID INTO :OBJ_ID;

    UPDATE BLANK_PACKS
    SET BLANKS_USED = (SELECT COUNT(*) FROM BLANKS WHERE BLANKS.DATE_DEL IS NULL AND BLANKS.PACK_ID = :V$OBJ_ID)
    WHERE OBJ_ID = :V$OBJ_ID;
  END
  ELSE
  BEGIN
    -- IF(I$STATE <> V$STATE_ID) THEN EXCEPTION DATA_ERROR 'Бланк ' || I$SERIE || ' ' || I$NUM || ' уже есть в системе, и отличается по статусу';

    UPDATE BLANKS
    SET STATE_ID = :I$STATE
    WHERE OBJ_ID = :OBJ_ID AND STATE_ID <> :I$STATE;
  END

  SUSPEND;
end
^

COMMIT WORK^


CREATE OR ALTER PROCEDURE SDOCS_MIGRATE_DOC (
  I$DOC_TYPE BIGINT, 
  I$OU_ID BIGINT, 
  I$PERSON_ID BIGINT, 
  I$ORDER_ID BIGINT, 
  I$BLANK_ID BIGINT, 
  I$STATE_ID BIGINT, 
  I$NAME_LAST VARCHAR(100) CHARACTER SET UTF8 COLLATE UTF8, 
  I$NAME_FIRST VARCHAR(100) CHARACTER SET UTF8 COLLATE UTF8, 
  I$NAME_MID VARCHAR(100) CHARACTER SET UTF8 COLLATE UTF8, 
  I$DOB DATE, 
  I$SEQ_NO INTEGER, 
  I$GRAD_YEAR INTEGER, 
  I$DOC_DATA VARCHAR(8000) CHARACTER SET UTF8 COLLATE UTF8, 
  I$CLASS_NAME VARCHAR(100) CHARACTER SET UTF8 COLLATE UTF8, 
  I$PROF_NAME VARCHAR(400) CHARACTER SET UTF8 COLLATE UTF8, 
  I$SPEC_NAME VARCHAR(400) CHARACTER SET UTF8 COLLATE UTF8, 
  I$OP_DUR VARCHAR(100) CHARACTER SET UTF8 COLLATE UTF8, 
  I$GAK_DATE DATE, 
  I$GAK_FIO VARCHAR(200) CHARACTER SET UTF8 COLLATE UTF8, 
  I$UO VARCHAR(100) CHARACTER SET UTF8 COLLATE UTF8, 
  I$QUALIFICATION VARCHAR(200) CHARACTER SET UTF8 COLLATE UTF8, 
  I$ORDER_DATE DATE, 
  I$ACTION_DATE DATE, 
  I$ORDER_NUM VARCHAR(100) CHARACTER SET UTF8 COLLATE UTF8, 
  I$REC_DATE DATE, 
  I$PREV_NUM VARCHAR(40) CHARACTER SET UTF8 COLLATE UTF8)
RETURNS (
  OBJ_ID BIGINT)
AS
declare variable V$YEAR_ID bigint;
declare variable V$IS_DUBL smallint;
declare variable V$PREV_DOC varchar(100);
declare variable V$STATE_ID smallint;
declare variable V$FORMAT_ID bigint;
declare variable V$PARENT_ID bigint;
declare variable V$OLD_PARENT bigint;
begin
  -- id в таблице не по порядку !
  V$YEAR_ID = NULL;
  SELECT FIRST 1 Y.LISTVAL_ID
  FROM STUDY_YEARS Y
  WHERE Y.IS_DEL = 0 AND :I$ACTION_DATE >= CAST('01.09.' || SUBSTRING(Y.VAL FROM 1 FOR 4) AS DATE)
  ORDER BY CAST(SUBSTRING(Y.VAL FROM 1 FOR 4) AS INTEGER) DESC
  INTO :V$YEAR_ID;

  SELECT FIRST 1 D.OBJ_ID, D.PARENT_ID
  FROM DOCUMENTS D
  WHERE D.DATE_DEL IS NULL AND D.PERSON_ID = :I$PERSON_ID AND D.ORDER_ID = :I$ORDER_ID AND D.BLANK_ID = :I$BLANK_ID AND D.YEAR_ID = :V$YEAR_ID
  INTO :OBJ_ID, :V$OLD_PARENT;

  -- Удаляем аттестат, если занесло не в тот год
  UPDATE DOCUMENTS
  SET DATE_DEL = CURRENT_TIMESTAMP
  WHERE DATE_DEL IS NULL AND PERSON_ID = :I$PERSON_ID AND ORDER_ID = :I$ORDER_ID AND BLANK_ID = :I$BLANK_ID AND YEAR_ID <> :V$YEAR_ID;

  IF(V$OLD_PARENT IS NULL) THEN
  BEGIN
    -- Если статус = взамен испорченного, то выставляем ссылку на старый документ
    -- если портили много раз - то ищем первый еще не связанный
    IF(I$STATE_ID = 5439) THEN
    SELECT FIRST 1 D.OBJ_ID
    FROM DOCUMENTS D
    JOIN BLANKS B ON B.DATE_DEL IS NULL AND B.OBJ_ID = D.BLANK_ID AND B.STATE_ID = 2
    LEFT JOIN DOCUMENTS D2 ON D2.DATE_DEL IS NULL AND D2.PARENT_ID = D.OBJ_ID
    WHERE D.DATE_DEL IS NULL AND D.PERSON_ID = :I$PERSON_ID AND D.YEAR_ID = :V$YEAR_ID AND D.DOC_TYPE_ID = :I$DOC_TYPE AND D2.OBJ_ID IS NULL AND D.ACTION_DATE <= :I$ACTION_DATE AND D.OBJ_ID <> :OBJ_ID
    ORDER BY 1
    INTO :V$PARENT_ID;
    -- дубликат - смотрим и нормальные бланки, и испорченные
    ELSE IF(I$STATE_ID = 5440) THEN
    SELECT FIRST 1 D.OBJ_ID
    FROM DOCUMENTS D
    JOIN BLANKS B ON B.DATE_DEL IS NULL AND B.OBJ_ID = D.BLANK_ID AND B.STATE_ID IN (1, 2)
    JOIN BLANK_PACKS BP ON BP.DATE_DEL IS NULL AND BP.OBJ_ID = B.PACK_ID
    WHERE D.DATE_DEL IS NULL AND D.PERSON_ID = :I$PERSON_ID AND D.DOC_TYPE_ID = :I$DOC_TYPE AND D.ACTION_DATE <= :I$ACTION_DATE AND D.OBJ_ID <> :OBJ_ID
      AND (BP.SERIE || B.BLANK_NUM = :I$PREV_NUM OR :I$PREV_NUM IS NULL)
    ORDER BY 1
    INTO :V$PARENT_ID;
  END

  IF(OBJ_ID IS NULL) THEN
  BEGIN
    -- По-умолчанию первичный бланк, не-дубликат
    V$STATE_ID = 4;
    V$IS_DUBL = 0;
    -- Дубликат ?
    IF(I$STATE_ID = 5440) THEN
    BEGIN
      V$IS_DUBL = 1;
    END

    -- Первый подходящий формат
    SELECT FIRST 1 T.OBJ_ID
    FROM PRINT_TEMPLATES T
    WHERE T.DATE_DEL IS NULL AND T.TYPE_ID = :I$DOC_TYPE
    ORDER BY T.IS_DEFAULT DESC, T.OBJ_ID
    INTO :V$FORMAT_ID;

    -- Подставляем 2000 учебный год
    IF(V$YEAR_ID IS NULL AND I$ACTION_DATE < '01.09.2000') THEN V$YEAR_ID = 167;

    INSERT INTO DOCUMENTS
    (OU_ID, STATE_ID, ORDER_ID, BLANK_ID, PERSON_ID, NAME_LAST, NAME_FIRST, NAME_MID, BIRTH_DATE, YEAR_ID, SEQ_NO, DOC_TYPE_ID, DOC_DATA,
      FORMAT_ID, PRINT_DATE, GRAD_YEAR, PRINTED_STATE, REC_DATE, IS_DUBL, CLASS_NAME, PROF_NAME, SPEC_NAME, OP_DUR, PREV_DOC, GAK_DATE, GAK_FIO, UO,
      QUALIFICATION, ORDER_DATE, ACTION_DATE, ORDER_NUM, PARENT_ID)
    VALUES (:I$OU_ID, :V$STATE_ID, :I$ORDER_ID, :I$BLANK_ID, :I$PERSON_ID, :I$NAME_LAST, :I$NAME_FIRST, :I$NAME_MID, :I$DOB, :V$YEAR_ID, :I$SEQ_NO, :I$DOC_TYPE, :I$DOC_DATA,
      :V$FORMAT_ID, :I$ACTION_DATE /* !!! */, :I$GRAD_YEAR, 15, :I$REC_DATE, :V$IS_DUBL, :I$CLASS_NAME, :I$PROF_NAME, :I$SPEC_NAME, :I$OP_DUR, :V$PREV_DOC, :I$GAK_DATE, :I$GAK_FIO, :I$UO,
      :I$QUALIFICATION, :I$ORDER_DATE, :I$ACTION_DATE, :I$ORDER_NUM, :V$PARENT_ID)
    RETURNING OBJ_ID INTO :OBJ_ID;
  END
  ELSE
  BEGIN

    -- Обновляем
    UPDATE DOCUMENTS
    SET DOC_DATA = :I$DOC_DATA, YEAR_ID = :V$YEAR_ID, PARENT_ID = :V$PARENT_ID, CLASS_NAME = :I$CLASS_NAME
    WHERE OBJ_ID = :OBJ_ID;

  END

  SUSPEND;
end
^

COMMIT WORK^


CREATE OR ALTER PROCEDURE SDOCS_PARENT_INFO (
  I$PARENT_ID BIGINT, 
  I$PERSON_ID BIGINT = null)
RETURNS (
  PARENT_BLANK_NUM VARCHAR(40) CHARACTER SET UTF8 COLLATE UTF8, 
  PARENT_DATE DATE, 
  PARENT_SEQ_NO INTEGER, 
  REG_ID VARCHAR(100) CHARACTER SET UTF8 COLLATE UTF8)
AS
declare variable V$ORDER_ID bigint;
declare variable V varchar(400);
begin

  SELECT FIRST 1 COALESCE(BP.SERIE || B.BLANK_NUM, D.REG_NUM), D.SEQ_NO, D.PRINT_DATE, D.ORDER_ID
  FROM DOCUMENTS D
  LEFT JOIN BLANKS B ON B.OBJ_ID = D.BLANK_ID
  LEFT JOIN BLANK_PACKS BP ON BP.OBJ_ID = B.PACK_ID
  WHERE D.OBJ_ID = :I$PARENT_ID
  INTO :PARENT_BLANK_NUM, :PARENT_SEQ_NO, :PARENT_DATE, :V$ORDER_ID;

  SELECT MON$DATABASE_NAME
  FROM MON$DATABASE
  INTO :V;

  V = REPLACE(V, 'DOC.FDB', 'BASE.FDB');

  -- Если есть дата приказа, достаем дату оттуда
  IF(V$ORDER_ID IS NOT NULL) THEN
  BEGIN
    EXECUTE STATEMENT
      'SELECT FIRST 1 ACTION_DATE
      FROM ORDERS
      WHERE OBJ_ID = ' || V$ORDER_ID
    ON EXTERNAL :V
    -- ROLE 'INISSOFT_ROLE'
    WITH COMMON TRANSACTION
    INTO :PARENT_DATE;
  END

  IF(I$PERSON_ID IS NOT NULL) THEN
  BEGIN
    EXECUTE STATEMENT
      'SELECT V FROM VAL_GET(' || I$PERSON_ID || ', 2912)'
    ON EXTERNAL :V
    -- ROLE 'INISSOFT_ROLE'
    WITH COMMON TRANSACTION
    INTO :REG_ID;

    IF(POSITION(':' IN REG_ID) > 0) THEN REG_ID = SUBSTRING(REG_ID FROM 1 FOR POSITION(':' IN REG_ID) - 1);
  END

  SUSPEND;

end
^

COMMIT WORK^


CREATE OR ALTER PROCEDURE SDOCS_PRE_NUMBERS (
  I$STAGE_ID BIGINT)
RETURNS (
  TAG_NAME VARCHAR(100) CHARACTER SET UTF8 COLLATE UTF8, 
  VAL VARCHAR(400) CHARACTER SET UTF8 COLLATE UTF8)
AS
declare variable V$YEAR_ID bigint;
declare variable V$GRADE_NAME varchar(400);
declare variable V$DT_ID varchar(400);
declare variable N integer;
begin

  SELECT FIRST 1 D.YEAR_ID
  FROM DOCUMENTS D
  WHERE D.DATE_DEL IS NULL AND D.SEQ_NO IS NULL AND D.STATE_ID = 1
  INTO :V$YEAR_ID;

  TAG_NAME = 'items::json&colon;array';
  VAL = '1';
  SUSPEND;
  FOR
    SELECT COALESCE(R.GRADE_NAME, ''), T.VAL, COUNT(*)
    FROM SDOCS_PRE_NUMBERS_ROWS(:I$STAGE_ID, :V$YEAR_ID) R
    LEFT JOIN BLANK_TYPES T ON T.LISTVAL_ID = R.DOC_TYPE_ID
    GROUP BY 1, 2
    ORDER BY 1, 2
    INTO :V$GRADE_NAME, :V$DT_ID, :N
  DO
  BEGIN
    TAG_NAME = 'items:r';
    VAL = NULL;
    SUSPEND;
    TAG_NAME = 'items:r:grade';
    VAL = V$GRADE_NAME;
    SUSPEND;
    TAG_NAME = 'items:r:type';
    VAL = V$DT_ID;
    SUSPEND;
    TAG_NAME = 'items:r:value';
    VAL = N;
    SUSPEND;
  END

end
^

COMMIT WORK^


CREATE OR ALTER PROCEDURE SDOCS_PRE_NUMBERS_ROWS (
  I$STAGE_ID BIGINT, 
  I$YEAR_ID BIGINT)
RETURNS (
  PERSON_ID BIGINT, 
  GRADE_NAME VARCHAR(400) CHARACTER SET UTF8 COLLATE UTF8, 
  ORDER_ID BIGINT, 
  ORDER_DATE DATE, 
  ORDER_NUM VARCHAR(100) CHARACTER SET UTF8 COLLATE UTF8, 
  DOC_TYPE_ID BIGINT)
AS
declare variable V$TYPE1 bigint;
declare variable V$TYPE2 bigint;
declare variable V$TYPE3 bigint;
declare variable V$ORDER_STATE bigint;
declare variable V varchar(400);
begin

  IF(I$STAGE_ID = 0) THEN
  BEGIN
    -- Справка об обучении - эта ветка нафиг не нужна, у нас справки сразу после фиксации распечатки сами нумеруются
    V$TYPE1 = 0;
  END
  IF(I$STAGE_ID = 1) THEN
  BEGIN
    -- Свидетельство об обучении - этой ветки уже нет, перенесено в ООО
    V$TYPE1 = 5385;
  END
  IF(I$STAGE_ID = 26) THEN
  BEGIN
    V$TYPE1 = 5377;
    V$TYPE2 = 5378;
    V$TYPE3 = 5385;
  END
  IF(I$STAGE_ID = 27) THEN
  BEGIN
    V$TYPE1 = 5379;
    V$TYPE2 = 5380;
  END
  IF(I$STAGE_ID = 29) THEN
  BEGIN
    V$TYPE1 = 12766;
    V$TYPE2 = 12767;
  END
  IF(I$STAGE_ID = -1) THEN
  BEGIN
    -- Дубликаты документов, особый случай
  END

  SELECT MON$DATABASE_NAME
  FROM MON$DATABASE
  INTO :V;

  V = REPLACE(V, 'DOC.FDB', 'BASE.FDB');

  FOR
    SELECT D.PERSON_ID, D.ORDER_ID, D.DOC_TYPE_ID, D.CLASS_NAME, D.ORDER_DATE, D.ORDER_NUM, D.CLASS_NAME
    FROM DOCUMENTS D
    WHERE D.DATE_DEL IS NULL AND D.YEAR_ID = :I$YEAR_ID AND (D.DOC_TYPE_ID = :V$TYPE1 OR D.DOC_TYPE_ID = :V$TYPE2 OR D.DOC_TYPE_ID = :V$TYPE3 OR :I$STAGE_ID = -1)
      AND D.STATE_ID = 1 AND D.SEQ_NO IS NULL AND ((:I$STAGE_ID >= 0 AND D.IS_DUBL = 0) OR (:I$STAGE_ID = -1 AND D.IS_DUBL = 1))
    INTO :PERSON_ID, :ORDER_ID, :DOC_TYPE_ID, :GRADE_NAME, :ORDER_DATE, :ORDER_NUM, :GRADE_NAME
  DO
  BEGIN
    V$ORDER_STATE = NULL;
    EXECUTE STATEMENT 'SELECT FIRST 1 STATE_ID, ORDER_DATE, ORDER_NUM FROM ORDERS WHERE DATE_DEL IS NULL AND OBJ_ID = ' || ORDER_ID
    ON EXTERNAL :V
    WITH COMMON TRANSACTION
    INTO :V$ORDER_STATE, :ORDER_DATE, :ORDER_NUM;

    IF(V$ORDER_STATE IS NULL OR V$ORDER_STATE <> 11715) THEN EXCEPTION DATA_ERROR 'По всем документам со статусом "распечатан" должен быть закрыт проект приказа';

    SUSPEND;
  END

end
^

COMMIT WORK^


CREATE OR ALTER PROCEDURE SDOCS_RESET_TEMPLATE (
  I$OBJ_ID BIGINT, 
  I$USER_ID VARCHAR(40) CHARACTER SET UTF8 COLLATE UTF8)
AS
declare variable V$BASE_ID bigint;
declare variable V$SIZE_X float;
declare variable V$SIZE_Y float;
declare variable V$SIZE_XA float;
declare variable V$SIZE_YA float;
declare variable V$FMT_DATA blob sub_type 1 segment size 80;
declare variable V$BG_DATA blob sub_type 1 segment size 80;
begin

  SELECT FIRST 1 T2.OBJ_ID, T2.SIZE_X, T2.SIZE_Y, T2.SIZE_XA, T2.SIZE_YA, T2.FMT_DATA, T2.BG_DATA
  FROM PRINT_TEMPLATES T
  JOIN PRINT_TEMPLATES T2 ON T2.DATE_DEL IS NULL AND T2.TYPE_ID = T.TYPE_ID AND T2.OU_ID = 0
  WHERE T.OBJ_ID = :I$OBJ_ID AND T.DATE_DEL IS NULL
  ORDER BY T2.DATE_ADD DESC
  INTO :V$BASE_ID, :V$SIZE_X, :V$SIZE_Y, :V$SIZE_XA, :V$SIZE_YA, :V$FMT_DATA, :V$BG_DATA;

  IF(V$BASE_ID IS NULL) THEN EXCEPTION DATA_ERROR 'Не удалось найти эталонный шаблон для данного типа';

  UPDATE PRINT_TEMPLATES
  SET SIZE_X = :V$SIZE_X, SIZE_Y = :V$SIZE_Y, SIZE_XA = :V$SIZE_XA, SIZE_YA = :V$SIZE_YA, FMT_DATA = :V$FMT_DATA, BG_DATA = :V$BG_DATA
  WHERE OBJ_ID = :I$OBJ_ID;

end
^

COMMIT WORK^




SET TERM ; ^


