5.1. SQL-запросы

 

5.1.1. Предварительные действия

Прежде, чем приступать к действиям, описанным далее, следует выполнить SQL-запросы к данному шагу.

 

 

5.1.2. Создание последовательности для присвоения номера обращению

CREATE SEQUENCE t_objects_object_ref_nr_seq

INCREMENT 1

MINVALUE 1

MAXVALUE 9223372036854775807

START 300001

CACHE 1;

 

 

5.1.3. Создание таблицы для хранения данных обращений

CREATE TABLE t_objects

(

object_id serial NOT NULL, -- ID записи

dttmcr timestamp with time zone NOT NULL DEFAULT now(), -- Дата и время создания записи

dttmup timestamp with time zone, -- Дата и время изменения записи

dttmcl timestamp with time zone, -- Дата и время закрытия записи

userid integer, -- ID пользователя, создавшего запись

object_ref_nr integer DEFAULT nextval('t_objects_object_ref_nr_seq'::regclass), -- Реферативный номер обращения

object_cur_status_id integer, -- ID текущего статуса - регулируется триггером при добавлении нового статуса в таблицу t_statuses.

client_id integer, -- ID клиента - FK t_clients.client_id

object_to_id integer, -- ID ответственного сотрудника - FK t_users.user_id

object_remark text, -- Примечание к обращению

CONSTRAINT pk_t_objects_object_id PRIMARY KEY (object_id)

);

COMMENT ON TABLE t_objects

IS 'Таблица для хранения обращений';

COMMENT ON COLUMN t_objects.object_id IS 'ID записи';

COMMENT ON COLUMN t_objects.dttmcr IS 'Дата и время создания записи';

COMMENT ON COLUMN t_objects.dttmup IS 'Дата и время изменения записи';

COMMENT ON COLUMN t_objects.dttmcl IS 'Дата и время закрытия записи';

COMMENT ON COLUMN t_objects.userid IS 'ID пользователя, создавшего запись';

COMMENT ON COLUMN t_objects.object_ref_nr IS 'Реферативный номер обращения';

COMMENT ON COLUMN t_objects.object_cur_status_id IS 'ID текущего статуса - регулируется триггером при добавлении нового статуса в таблицу t_statuses.';

COMMENT ON COLUMN t_objects.client_id IS 'ID клиента - FK t_clients.client_id';

COMMENT ON COLUMN t_objects.object_to_id IS 'ID ответственного сотрудника - FK t_users.user_id';

COMMENT ON COLUMN t_objects.object_remark IS 'Примечание к обращению'; 

 

 

5.1.4. Создание представления для отображения информации об обращениях

CREATE OR REPLACE VIEW vw_objects AS 

SELECT o.object_id,

o.dttmcr,

o.dttmup,

o.dttmcl,

o.userid,

o.object_ref_nr,

o.object_cur_status_id,

o.object_remark,

st.status_type_name,

(COALESCE(c.client_name, ''::text) || ' '::text) || COALESCE(c.client_surname, ''::text) AS client_fullname,

(COALESCE(u.user_name, ''::text) || ' '::text) || COALESCE(u.user_surname, ''::text) AS user_fullname

FROM t_objects o

LEFT JOIN t_status_types st ON o.object_cur_status_id = st.status_type_id

LEFT JOIN t_clients c USING (client_id)

LEFT JOIN t_users u ON o.object_to_id = u.user_id;

 

 

5.1.5. Создание таблицы для хранения статусов обращений

CREATE TABLE t_statuses

(

status_id serial NOT NULL,

dttmcr timestamp with time zone NOT NULL DEFAULT now(), -- Дата и время создания записи

dttmup timestamp with time zone, -- Дата и время изменения записи

dttmcl timestamp with time zone, -- Дата и время закрытия записи

userid integer, -- ID пользователя, создавшего запись

object_id integer NOT NULL, -- ID обращения - FK t_objects.object_id

status_type_id integer NOT NULL, -- ID типа статуса

CONSTRAINT pk_t_statuses_status_id PRIMARY KEY (status_id)

);

COMMENT ON TABLE t_statuses

IS 'Таблица для хранения статусов';

COMMENT ON COLUMN t_statuses.dttmcr IS 'Дата и время создания записи';

COMMENT ON COLUMN t_statuses.dttmup IS 'Дата и время изменения записи';

COMMENT ON COLUMN t_statuses.dttmcl IS 'Дата и время закрытия записи';

COMMENT ON COLUMN t_statuses.userid IS 'ID пользователя, создавшего запись';

COMMENT ON COLUMN t_statuses.object_id IS 'ID обращения - FK t_objects.object_id';

COMMENT ON COLUMN t_statuses.status_type_id IS 'ID типа статуса'; 

 

 

5.1.6. Создание представления для отображения статусов обращений

CREATE OR REPLACE VIEW vw_statuses AS 

SELECT s.status_id,

s.dttmcr,

s.dttmup,

s.dttmcl,

s.userid,

s.object_id,

st.status_type_name

FROM t_statuses s

LEFT JOIN t_status_types st USING (status_type_id);

 

 

5.1.7. Создание таблицы для хранения комментариев к обращениям

CREATE TABLE t_comments

(

comment_id serial NOT NULL,

dttmcr timestamp with time zone NOT NULL DEFAULT now(), -- Дата и время создания записи

dttmup timestamp with time zone, -- Дата и время изменения записи

dttmcl timestamp with time zone, -- Дата и время закрытия записи

userid integer, -- ID пользователя, создавшего запись

object_id integer, -- ID обращения - FK t_objects.object_id

comment_type_id integer NOT NULL, -- ID типа комментария - FK t_comment_type.comment_type_id

comment_from text, -- Отправитель

comment_to text, -- Получатель

comment_cc text, -- Копия

comment_bcc text, -- Скрытая копия

comment_subject text, -- Тема

comment_text text, -- Тело

comment_diag_mes text, -- Диагностическое сообщение, формируемое при анализе темы письма

CONSTRAINT pk_t_comments_comment_id PRIMARY KEY (comment_id)

);

COMMENT ON TABLE t_comments

IS 'Таблица для хранения комментариев';

COMMENT ON COLUMN t_comments.dttmcr IS 'Дата и время создания записи';

COMMENT ON COLUMN t_comments.dttmup IS 'Дата и время изменения записи';

COMMENT ON COLUMN t_comments.dttmcl IS 'Дата и время закрытия записи';

COMMENT ON COLUMN t_comments.userid IS 'ID пользователя, создавшего запись';

COMMENT ON COLUMN t_comments.object_id IS 'ID обращения - FK t_objects.object_id';

COMMENT ON COLUMN t_comments.comment_type_id IS 'ID типа комментария - FK t_comment_type.comment_type_id';

COMMENT ON COLUMN t_comments.comment_from IS 'Отправитель';

COMMENT ON COLUMN t_comments.comment_to IS 'Получатель';

COMMENT ON COLUMN t_comments.comment_cc IS 'Копия';

COMMENT ON COLUMN t_comments.comment_bcc IS 'Скрытая копия';

COMMENT ON COLUMN t_comments.comment_subject IS 'Тема';

COMMENT ON COLUMN t_comments.comment_text IS 'Тело';

COMMENT ON COLUMN t_comments.comment_diag_mes IS 'Диагностическое сообщение, формируемое при анализе темы письма';

 

 

5.1.8. Создание таблицы для хранения вложений к комментариям

CREATE TABLE t_files

(

file_id serial NOT NULL, -- ID записи

dttmcr timestamp with time zone NOT NULL DEFAULT now(), -- Дата и время создания записи

dttmup timestamp with time zone, -- Дата и время изменения записи

dttmcl timestamp with time zone, -- Дата и время закрытия записи

userid integer, -- ID пользователя, создавшего запись

comment_id integer, -- ID комментария - FK t_comments.comment_id

comment_file bytea, -- Файл

CONSTRAINT pk_t_files_file_id PRIMARY KEY (file_id)

);

COMMENT ON TABLE t_files

IS 'Таблица для хранения файлов';

COMMENT ON COLUMN t_files.file_id IS 'ID записи';

COMMENT ON COLUMN t_files.dttmcr IS 'Дата и время создания записи';

COMMENT ON COLUMN t_files.dttmup IS 'Дата и время изменения записи';

COMMENT ON COLUMN t_files.dttmcl IS 'Дата и время закрытия записи';

COMMENT ON COLUMN t_files.userid IS 'ID пользователя, создавшего запись';

COMMENT ON COLUMN t_files.comment_id IS 'ID комментария - FK t_comments.comment_id';

COMMENT ON COLUMN t_files.comment_file IS 'Файл';

 

 

5.1.9. Создание представления для отображения комментариев к обращениям

CREATE OR REPLACE VIEW vw_comments AS 

SELECT c.comment_id,

c.dttmcr,

c.dttmup,

c.dttmcl,

c.userid,

c.object_id,

c.comment_type_id,

c.comment_from,

c.comment_to,

c.comment_cc,

c.comment_bcc,

c.comment_subject,

c.comment_diag_mes,

c.comment_text,

ct.comment_type_name,

CASE

WHEN (( SELECT DISTINCT t_files.comment_id

FROM t_files

WHERE t_files.comment_id = c.comment_id AND t_files.dttmcl IS NULL)) IS NULL THEN '-'::text

ELSE 'Да'::text

END AS comment_file_present

FROM t_comments c

LEFT JOIN t_comment_types ct USING (comment_type_id);

 

 

5.1.10. Далее

5.2. Создание объекта 1001 Обращение (основа)

© ProjectDB, 1996 — 2024