CREATE TABLE DOC$CS$STATEMENT (
    ID                               DOM$KEY NOT NULL,
    ID_CREATOR                       DOM$KEY NOT NULL,
    ID_REGISTRATOR                   DOM$KEY NOT NULL,
    ID_CS                            DOM$KEY NOT NULL,
    ID_TYPE_WORK                     DOM$KEY NOT NULL,
    ID_STATE                         DOM$KEY NOT NULL,
    ID_SETTLEMENT                    DOM$KEY NOT NULL,
    ID_STREET                        DOM$KEY,
    ID_NUMBER_BUILDING               DOM$KEY,
    ID_ABONENT                       DOM$KEY,
    ID_GENDER                        DOM$KEY,
    ID_PASSPORT_ISSUE                DOM$KEY,
    ID_FEED_METHOD                   DOM$KEY,
    ID_CURRENT_EXECUTOR              DOM$KEY,
    ID_RESULT                        DOM$KEY,
    ID_REASON_RESULT                 DOM$KEY,
    ID_METER_MODEL                   DOM$KEY,
    ID_METER_VERIFICATION_INTERVAL   DOM$KEY,
    ID_USER_PRINT                    DOM$KEY,
    ID_REASON_INSTALL_CAP            DOM$KEY,
    ID_COEF_REMOTENESS               DOM$KEY NOT NULL,
    TIMESTAMP_CREATION               DOM$CURRENT_TIMESTAMP NOT NULL,
    TIMESTAMP_PRINT                  DOM$TIMESTAMP,
    NAME_STATEMENT                   COMPUTED BY (IIF(NUMBER IS NOT NULL, '№' || NUMBER || ' от ' || FORMAT_DATE(DATE_REGISTER) || '. ', '')
    || ADDRESS_FOR_PRINT
    || TRIM(TRAILING FROM IIF(ID_ABONENT IS NOT NULL, ' (' || 'л/счет ' || ID_ABONENT || ')', ''))
    || IIF(SURNAME_WITH_INITIALS IS NOT NULL, ', ' || SURNAME_WITH_INITIALS, '')),
    NUMBER_HOUSE                     DOM$NUMBER_HOUSE,
    NUMBER_FLAT                      DOM$FLAT,
    ADDRESS                          DOM$ADDRESS NOT NULL,
    ADDRESS_FOR_PRINT                DOM$ADDRESS NOT NULL,
    DATE_REGISTER                    DOM$DATE,
    TIME_REGISTER                    DOM$TIME,
    NUMBER                           D_NUMBER,
    NUMBER_IN_ENTERPRISE             DOM$NUMBER_STATEMENT_FOR_CS,
    SURNAME                          DOM$CITIZEN_SURNAME,
    SURNAME_IN_GENETIVE              DOM$CITIZEN_SURNAME,
    NAME                             DOM$CITIZEN_NAME,
    PATRONYMIC                       DOM$CITIZEN_PATRONYMIC,
    SURNAME_WITH_INITIALS            COMPUTED BY (IIF(SURNAME IS NOT NULL, SURNAME ||
    IIF(NAME IS NOT NULL, ' ' ||  LEFT(NAME, 1) || '.' ||
        IIF(PATRONYMIC IS NOT NULL, LEFT(PATRONYMIC, 1) || '.', ''), ''), NULL)),
    SURNAME_GENETIVE_WITH_INITIALS   COMPUTED BY (IIF(SURNAME_IN_GENETIVE IS NOT NULL, SURNAME_IN_GENETIVE ||
    IIF(NAME IS NOT NULL, ' ' ||  LEFT(NAME, 1) || '.' ||
        IIF(PATRONYMIC IS NOT NULL, LEFT(PATRONYMIC, 1) || '.', ''), ''), NULL)),
    NUMBER_REQUEST_ORGANIZATION      D_VARCHAR_10,
    DATE_REQUEST_ORGANIZATION        DOM$DATE,
    PASSPORT_SERIES                  DOM$PASSPORT_SERIES,
    PASSPORT_NUMBER                  DOM$PASSPORT_NUMBER,
    PASSPORT_DATE_ISSUE              DOM$DATE,
    DATE_BIRTH                       DOM$DATE,
    ADDRESS_REGISTER                 DOM$ADDRESS,
    IS_OWNER                         DOM$BOOLEAN NOT NULL,
    LANDLINE_PHONE                   DOM$PHONE_HOME,
    CELLULAR_PHONE_1                 DOM$PHONE_CELLULAR,
    CELLULAR_PHONE_2                 DOM$PHONE_CELLULAR,
    CELLULAR_PHONE_3                 DOM$PHONE_CELLULAR,
    AMOUNT_REQUESTED_WORK            DOM$AMOUNT NOT NULL,
    AMOUNT_REQUESTED_MATERIAL        DOM$AMOUNT NOT NULL,
    AMOUNT_REQUESTED_TOTAL           COMPUTED BY (AMOUNT_REQUESTED_WORK + AMOUNT_REQUESTED_MATERIAL),
    AMOUNT_ACTUAL_WORK               DOM$AMOUNT NOT NULL,
    AMOUNT_ACTUAL_MATERIAL           DOM$AMOUNT NOT NULL,
    AMOUNT_ACTUAL_TOTAL              COMPUTED BY (AMOUNT_ACTUAL_WORK + AMOUNT_ACTUAL_MATERIAL),
    PAYMENT                          DOM$AMOUNT NOT NULL,
    BALANCE                          COMPUTED BY (AMOUNT_ACTUAL_TOTAL - PAYMENT),
    NAME_STATE                       COMPUTED BY ((SELECT S.NAME FROM DIR$CS$STATE S WHERE S.ID = DOC$CS$STATEMENT.ID_STATE)),
    COMMENT                          DOM$COMMENT,
    REQUESTED_WORKS                  COMPUTED BY (CAST(FIRST_LETTER_UPPER((SELECT
    LIST(T.NAME, '; ')
FROM
    (SELECT
        FIRST_LETTER_LOWER(RW.NAME) AS NAME
    FROM
        DOC$CS$REQUESTED_WORK DRW INNER JOIN DIR$CS$REQUESTED_WORK RW ON RW.ID = DRW.ID_REQUESTED_WORK
    WHERE
        DRW.ID_STATEMENT = DOC$CS$STATEMENT.ID
    ORDER BY
        RW.POS
    ) T
)) AS D_VARCHAR_1000)),
    PHONES                           COMPUTED BY (NULLIF(TRIM(IIF(DOC$CS$STATEMENT.LANDLINE_PHONE IS NOT NULL, FORMAT_CITY_PHONE(DOC$CS$STATEMENT.LANDLINE_PHONE), '')
    || TRIM(LEADING FROM IIF((DOC$CS$STATEMENT.LANDLINE_PHONE IS NOT NULL) AND (DOC$CS$STATEMENT.CELLULAR_PHONE_1 IS NOT NULL), ', ', ''))
    || TRIM(IIF(DOC$CS$STATEMENT.CELLULAR_PHONE_1 IS NOT NULL, FORMAT_MOBILE_PHONE(DOC$CS$STATEMENT.CELLULAR_PHONE_1), ''))
    || TRIM(LEADING FROM IIF((DOC$CS$STATEMENT.CELLULAR_PHONE_1 IS NOT NULL) AND (DOC$CS$STATEMENT.CELLULAR_PHONE_2 IS NOT NULL), ', ', ''))
    || TRIM(IIF(DOC$CS$STATEMENT.CELLULAR_PHONE_2 IS NOT NULL, FORMAT_MOBILE_PHONE(DOC$CS$STATEMENT.CELLULAR_PHONE_2), ''))
    || TRIM(LEADING FROM IIF((DOC$CS$STATEMENT.CELLULAR_PHONE_2 IS NOT NULL) AND (DOC$CS$STATEMENT.CELLULAR_PHONE_3 IS NOT NULL), ', ', ''))
    || TRIM(IIF(DOC$CS$STATEMENT.CELLULAR_PHONE_3 IS NOT NULL, FORMAT_MOBILE_PHONE(DOC$CS$STATEMENT.CELLULAR_PHONE_3), ''))
), '')),
    CODE_DOOR_ENTRANCE               DOM$CODE_DOOR_ENTRANCE,
    INTERCOM_NOT_WORK                DOM$BOOLEAN,
    NUMBER_ENTRANCE                  DOM$NUMBER_ENTRANCE,
    NUMBER_FLOOR                     DOM$NUMBER_FLOOR,
    URGENTLY                         DOM$BOOLEAN NOT NULL,
    EMAIL                            DOM$EMAIL,
    REQUIRED_DATE_END_EXECUTION      DOM$DATE,
    DAYS_REST_FOR_EXECUTE            COMPUTED BY (CAST(IIF(IS_DEFERRED = 0, DATEDIFF(DAY, CURRENT_DATE, REQUIRED_DATE_END_EXECUTION), DATEDIFF(DAY, CURRENT_DATE, DATE_DEFERRED)) AS INTEGER)),
    DATE_RETURN_TO_CS                DOM$DATE,
    DATE_CLOSE                       DOM$DATE,
    DATE_DEFERRED                    DOM$DATE,
    QUANTITY_DOCUMENT                COMPUTED BY ((SELECT COUNT(*) FROM DOC$CS$DOC_FOR_STATEMENT D WHERE D.ID_STATEMENT = DOC$CS$STATEMENT.ID)),
    QUANTITY_CHANGE                  COMPUTED BY ((SELECT COUNT(*) FROM DOC$CS$CHANGE C WHERE C.ID_STATEMENT = DOC$CS$STATEMENT.ID)),
    QUANTITY_TASK                    COMPUTED BY ((SELECT COUNT(*) FROM DOC$CS$TASK T WHERE T.ID_STATEMENT = DOC$CS$STATEMENT.ID)),
    QUANTITY_PHONE_CALL              COMPUTED BY ((SELECT COUNT(*) FROM DOC$CS$PHONE_CALL PC WHERE PC.ID_STATEMENT = DOC$CS$STATEMENT.ID)),
    QUANTITY_COMMENT                 COMPUTED BY ((SELECT COUNT(*) FROM DOC$CS$COMMENT C WHERE C.ID_STATEMENT = DOC$CS$STATEMENT.ID)),
    QUANTITY_AVAILABLE               COMPUTED BY ((SELECT COUNT(*) FROM DOC$CS$AVAILABLE A WHERE A.ID_STATEMENT = DOC$CS$STATEMENT.ID)),
    STATE_TERM_EXEC_STATEMENT        COMPUTED BY (IIF(IS_CLOSED = 1, 7,
    IIF(ID_STATE = (SELECT ID_STATE_DEFERRED FROM SYS$LINK$CENTRE_SERVICES), 6,
        IIF(REQUIRED_DATE_END_EXECUTION IS NULL, 5,
            IIF(REQUIRED_DATE_END_EXECUTION < CURRENT_DATE, 4,
                IIF(DATEDIFF(DAY, CURRENT_DATE, REQUIRED_DATE_END_EXECUTION) <= 2, 3,
                    IIF(DATEDIFF(DAY, CURRENT_DATE, REQUIRED_DATE_END_EXECUTION) <= 1, 2, 1))))))),
    STATE_TERM_EXEC_STATEMENT_HINT   COMPUTED BY (IIF(IS_CLOSED = 1, 'Закрыто',
    IIF(IS_DEFERRED = 0,
        IIF(REQUIRED_DATE_END_EXECUTION IS NOT NULL, 'Исполнить до ' || FORMAT_DATE(REQUIRED_DATE_END_EXECUTION), 'Срок исполнения не указан'),
        IIF(DATE_DEFERRED IS NOT NULL, 'Отложено до ' || FORMAT_DATE(DATE_DEFERRED), 'Отложено на неопределенный срок'))
    || IIF(DAYS_REST_FOR_EXECUTE IS NOT NULL, ASCII_CHAR(13) || ASCII_CHAR(10) || TRIM(IIF(DAYS_REST_FOR_EXECUTE >= 0, 'Осталось', 'Просрочено')) || ' дней: ' || ABS(DAYS_REST_FOR_EXECUTE), ''))),
    STATE_TERM_TRANSF_TSERVICE       COMPUTED BY (IIF(IS_CLOSED = 1, 1,
    IIF(ID_CURRENT_EXECUTOR IS NULL, 2,
        IIF((SELECT TIMESTAMP_ACCEPT FROM DOC$CS$EXECUTOR WHERE ID = DOC$CS$STATEMENT.ID_CURRENT_EXECUTOR) IS NOT NULL, 3,
            CASE (SELECT RESULT FROM CALC$DAY$DAYS_BETWEEN_DATES((SELECT TIMESTAMP_TRANSFERENCE_FOR_EXEC FROM DOC$CS$EXECUTOR WHERE ID = DOC$CS$STATEMENT.ID_CURRENT_EXECUTOR), CURRENT_DATE))
                WHEN 0 THEN 4
                WHEN 1 THEN 5
                WHEN 2 THEN 6
                ELSE 7
            END)))),
    STATE_TERM_TRANSF_TSERVICE_HINT  COMPUTED BY (NAME_TERM_TRANSF_TSERVICE
    || TRIM(TRAILING FROM
        CASE STATE_TERM_TRANSF_TSERVICE
            WHEN 3 THEN ' ' || (SELECT RESULT FROM CALC$DAY$NAME_DATETIME((SELECT E.TIMESTAMP_ACCEPT FROM DOC$CS$EXECUTOR E WHERE E.ID = DOC$CS$STATEMENT.ID_CURRENT_EXECUTOR)))
            WHEN 4 THEN ', в ' || (SELECT FORMAT_TIME(CAST(E.TIMESTAMP_TRANSFERENCE_FOR_EXEC AS TIME)) FROM DOC$CS$EXECUTOR E WHERE E.ID = DOC$CS$STATEMENT.ID_CURRENT_EXECUTOR)
            WHEN 5 THEN ', ' || (SELECT FORMAT_TIME(CAST(E.TIMESTAMP_TRANSFERENCE_FOR_EXEC AS TIME)) FROM DOC$CS$EXECUTOR E WHERE E.ID = DOC$CS$STATEMENT.ID_CURRENT_EXECUTOR)
            WHEN 6 THEN ', ' || (SELECT FORMAT_TIME(CAST(E.TIMESTAMP_TRANSFERENCE_FOR_EXEC AS TIME)) FROM DOC$CS$EXECUTOR E WHERE E.ID = DOC$CS$STATEMENT.ID_CURRENT_EXECUTOR)
            WHEN 7 THEN ', ' || (SELECT FORMAT_DATETIME(E.TIMESTAMP_TRANSFERENCE_FOR_EXEC) FROM DOC$CS$EXECUTOR E WHERE E.ID = DOC$CS$STATEMENT.ID_CURRENT_EXECUTOR)
            ELSE ''
        END
        )),
    STATE_TERM_EXEC_EXECUTOR         COMPUTED BY (CASE
    WHEN IS_CLOSED = 1 THEN 9
    WHEN IS_DEFERRED = 1 THEN 8
    WHEN ID_CURRENT_EXECUTOR IS NULL THEN 1
    WHEN (SELECT DATE_END_EXECUTION FROM DOC$CS$EXECUTOR WHERE ID = ID_CURRENT_EXECUTOR) IS NOT NULL THEN 7
    WHEN (SELECT DATE_BEGIN_EXECUTION FROM DOC$CS$EXECUTOR WHERE ID = ID_CURRENT_EXECUTOR) IS NOT NULL THEN 6
    ELSE
        CASE DATEDIFF(DAY, CURRENT_DATE, (SELECT DATE_END_EXECUTION_REQUIRED FROM DOC$CS$EXECUTOR WHERE ID = ID_CURRENT_EXECUTOR))
            WHEN 0 THEN 4
            WHEN 1 THEN 4
            WHEN 2 THEN 3
            ELSE
                IIF(DATEDIFF(DAY, CURRENT_DATE, (SELECT DATE_END_EXECUTION_REQUIRED FROM DOC$CS$EXECUTOR WHERE ID = ID_CURRENT_EXECUTOR)) < 0, 5, 2)
        END
END),
    STATE_TERM_EXEC_EXECUTOR_HINT    COMPUTED BY (IIF((STATE_TERM_EXEC_EXECUTOR = 6) AND ((SELECT FORMAT_DATE(E.DATE_BEGIN_EXECUTION) FROM DOC$CS$EXECUTOR E WHERE E.ID = DOC$CS$STATEMENT.ID_CURRENT_EXECUTOR) > CURRENT_DATE), 'Будет исполнено ' || (SELECT FORMAT_DATE(E.DATE_BEGIN_EXECUTION) FROM DOC$CS$EXECUTOR E WHERE E.ID = DOC$CS$STATEMENT.ID_CURRENT_EXECUTOR),
    NAME_TERM_EXEC_EXECUTOR
        || TRIM(TRAILING FROM CASE STATE_TERM_EXEC_EXECUTOR
            WHEN 6 THEN ' с ' || (SELECT FORMAT_DATE(E.DATE_BEGIN_EXECUTION) FROM DOC$CS$EXECUTOR E WHERE E.ID = DOC$CS$STATEMENT.ID_CURRENT_EXECUTOR)
            WHEN 7 THEN ' ' || (SELECT FORMAT_DATE(E.DATE_END_EXECUTION) FROM DOC$CS$EXECUTOR E WHERE E.ID = DOC$CS$STATEMENT.ID_CURRENT_EXECUTOR)
            ELSE ''
        END))),
    STATE_BALANCE                    COMPUTED BY ((SELECT RESULT FROM GET$CS$STATE_BALANCE(ID_STATE, AMOUNT_ACTUAL_TOTAL, PAYMENT))),
    STATE_BALANCE_HINT               COMPUTED BY (NAME_BALANCE),
    STATE_PRINT                      COMPUTED BY (IIF(TIMESTAMP_PRINT IS NULL, 1, 2)),
    STATE_PRINT_HINT                 COMPUTED BY (NAME_PRINT),
    COLOR_TERM_EXEC_STATEMENT        COMPUTED BY (CAST(CASE STATE_TERM_EXEC_STATEMENT
    WHEN 1 THEN 1
    WHEN 2 THEN 3
    WHEN 3 THEN 4
    WHEN 4 THEN 5
    WHEN 5 THEN 0
    WHEN 6 THEN 0
    WHEN 7 THEN 0
    ELSE 2 END AS DOM$SEMAPHORE)),
    COLOR_TERM_TRANSF_TSERVICE       COMPUTED BY (CASE STATE_TERM_TRANSF_TSERVICE
    WHEN 1 THEN 0
    WHEN 2 THEN 0
    WHEN 3 THEN 1
    WHEN 4 THEN 2
    WHEN 5 THEN 3
    WHEN 6 THEN 4
    WHEN 7 THEN 5
    END),
    COLOR_TERM_EXEC_EXECUTOR         COMPUTED BY ((CAST(CASE STATE_TERM_EXEC_EXECUTOR
    WHEN 1 THEN 0
    WHEN 2 THEN 1
    WHEN 3 THEN 3
    WHEN 4 THEN 4
    WHEN 5 THEN 5
    WHEN 6 THEN 2
    WHEN 7 THEN 2
    WHEN 8 THEN 0
    WHEN 9 THEN 0
    ELSE 5
END AS DOM$SEMAPHORE))),
    COLOR_BALANCE                    COMPUTED BY ((SELECT RESULT FROM GET$CS$COLOR_BALANCE(STATE_BALANCE))),
    COLOR_PRINT                      COMPUTED BY (((CAST(CASE STATE_PRINT
    WHEN 1 THEN 0
    WHEN 2 THEN 1
    ELSE 5
END AS DOM$SEMAPHORE)))),
    NAME_TERM_EXEC_STATEMENT         COMPUTED BY (TRIM(CASE STATE_TERM_EXEC_STATEMENT
    WHEN 1 THEN 'Без просрочки'
    WHEN 2 THEN 'Внимание'
    WHEN 3 THEN 'Опасно'
    WHEN 4 THEN 'Просрочено'
    WHEN 5 THEN 'Без срока исполнения'
    WHEN 6 THEN 'Отложено'
    WHEN 7 THEN 'Закрыто'
    ELSE '<Неизвестно>' END)),
    NAME_TERM_TRANSF_TSERVICE        COMPUTED BY (TRIM(CASE STATE_TERM_TRANSF_TSERVICE
    WHEN 1 THEN 'Закрыто'
    WHEN 2 THEN 'Не передано'
    WHEN 3 THEN 'Получено'
    WHEN 4 THEN 'Передано сегодня'
    WHEN 5 THEN 'Передано вчера'
    WHEN 6 THEN 'Передано позавчера'
    WHEN 7 THEN 'Передано более двух дней назад'
    ELSE '<Неизвестно>'
    END)),
    NAME_TERM_EXEC_EXECUTOR          COMPUTED BY ((TRIM(CASE STATE_TERM_EXEC_EXECUTOR
    WHEN 1 THEN 'Не передано'
    WHEN 2 THEN 'Без просрочки'
    WHEN 3 THEN 'Внимание'
    WHEN 4 THEN 'Опасно'
    WHEN 5 THEN 'Просрочено'
    WHEN 6 THEN 'Исполняется'
    WHEN 7 THEN 'Исполнено'
    WHEN 8 THEN 'Отложено'
    WHEN 9 THEN 'Закрыто'
    ELSE '<Неизвестно>'
    END))),
    NAME_BALANCE                     COMPUTED BY ((SELECT RESULT FROM GET$CS$STATE_BALANCE_HINT(STATE_BALANCE, AMOUNT_ACTUAL_TOTAL, PAYMENT))),
    NAME_PRINT                       COMPUTED BY (TRIM(CASE STATE_PRINT
    WHEN 1 THEN 'Не напечатано'
    WHEN 2 THEN 'Напечатано ' || FORMAT_DATETIME(TIMESTAMP_PRINT) || ', ' || (SELECT U.SURNAME_WITH_INITIALS FROM DIR$SECURITY$DB_USER U WHERE U.ID = ID_USER_PRINT)
    ELSE '<Неизвестно>'
END)),
    IS_DEFERRED                      COMPUTED BY (IIF(ID_STATE = (SELECT ID_STATE_DEFERRED FROM SYS$LINK$CENTRE_SERVICES), 1, 0)),
    IS_EXECUTED                      DOM$BOOLEAN NOT NULL,
    IS_CLOSED                        DOM$BOOLEAN NOT NULL,
    METER_SERIAL                     DOM$SERIAL_EQUIPMENT,
    METER_INITIAL_READING            DOM$READING,
    METER_DATE_VERIFICATION          DOM$DATE,
    TERM_EXECUTION                   D_NATURAL_MORE_ZERO
);