9.1. SQL-запросы
9.1.1. Предварительные действия
Прежде, чем приступать к действиям, описанным далее, следует выполнить SQL-запросы к данному шагу.
9.1.2. Создание типа для хранения разобранной темы письма
CREATE TYPE parsed_email_subject AS
(subject_type_code integer,
object_id integer,
rest_email_subject text,
diag_mes text);
9.1.3. Создание функции для анализа темы письма
CREATE OR REPLACE FUNCTION f_parse_subject(email_subject text)
RETURNS parsed_email_subject AS
$BODY$
DECLARE
left_bracket text:= '[';
right_bracket text:= ']';
number_sign text:= '#';
subject_type_code int; -- Код типа субъекта
subject_type_for_check text; -- Код типа субъекта для проверки, является ли он числовым значением
subject_code_is_present boolean; -- Наличие кода типа субъекта в таблице subject_types;
object_id integer; -- Номер объекта
object_ref_for_check text; -- Номер объекта для проверки, является ли он числовым значением
object_ref_is_present boolean; -- Наличие номера объекта в таблице objects;
rest_email_subject text; -- Остальная часть темы письма
pos_left_bracket int; -- Позиция левой скобки
pos_right_bracket int; -- Позиция правой скобки
pos_number_sign int; -- Позиция знакая номера
email_subject_length int; -- Длина темы письма
parsed_email_subject parsed_email_subject; -- Разбитая тема письма
diag_mes text; -- Диагностическое сообщение
BEGIN
/* Определяем позиции необходимых знаков */
pos_left_bracket := position(left_bracket in email_subject); -- Определение позиции левой скобки
pos_number_sign := position(number_sign in email_subject); -- Определение позиции знака номера
pos_right_bracket := position(right_bracket in email_subject); -- Определение позиции правой скобки
email_subject_length := char_length(email_subject); -- Определение длины темы
/* Эта часть кода выполняется, если в теме присутствуют все три знака */
IF pos_left_bracket <> 0 -- Если присутствуют левая скобка
AND pos_right_bracket <> 0 -- и правая скобка
AND pos_number_sign <> 0 -- а также знак номера
AND pos_number_sign BETWEEN pos_left_bracket AND pos_right_bracket -- Последовательность: левая скобка, знак номера, правая скобка
THEN
/* Номер объекта для проверки */
object_ref_for_check:=
trim(substring(email_subject from (pos_number_sign + 1 ) for (pos_right_bracket - pos_number_sign - 1)));
/* Определяем, присутствует ли номер объекта в таблице objects */
IF f_is_integer(object_ref_for_check) THEN
object_ref_is_present:= (SELECT COUNT(*) FROM t_objects WHERE t_objects.object_ref_nr = object_ref_for_check::integer) != 0;
END IF;
/* Номер объекта является целым числовым значением и присутствует в таблице objects */
IF object_ref_for_check IS NOT NULL AND f_is_integer(object_ref_for_check) AND object_ref_is_present THEN
object_id:= (SELECT t_objects.object_id FROM t_objects WHERE t_objects.object_ref_nr = object_ref_for_check::integer);
rest_email_subject:=trim(substring(email_subject from (pos_right_bracket + 1) for (email_subject_length + 1 - pos_right_bracket)));
diag_mes:= '0 - Номер объекта определен';
/* Номер объекта не определен */
ELSE rest_email_subject:= email_subject;
diag_mes:= '1 - Номер объекта не определен';
END IF;
/* Тему письма невозможно разобрать согласно правилам */
ELSE rest_email_subject:= email_subject;
diag_mes:= '2 - Тему письма невозможно разобрать согласно правилам';
END IF;
SELECT subject_type_code, object_id, rest_email_subject, diag_mes
INTO parsed_email_subject.subject_type_code, parsed_email_subject.object_id, parsed_email_subject.rest_email_subject, parsed_email_subject.diag_mes;
RETURN parsed_email_subject;
END
$BODY$
LANGUAGE plpgsql VOLATILE;
9.1.4. Создание функции для проверки на целочисленное значение
CREATE OR REPLACE FUNCTION f_is_integer(text)
RETURNS boolean AS
$BODY$
SELECT $1 ~ '^[0-9]+$'
$BODY$
LANGUAGE sql VOLATILE;
9.1.5. Создание функции для автоматического обновления статуса в зависимости от типа комментария
CREATE OR REPLACE FUNCTION f_update_status(
pdb_userid integer,
"1001.object_id" integer,
"1001.object_cur_status_id" integer,
"1202.comment_id" integer,
comment_type_id integer)
RETURNS boolean AS
$BODY$
DECLARE
var_timestamp timestamp with time zone := (SELECT now() - interval '1 second'); -- Передается в функцию f_start_processing для создания разницы в 1 секунду между статусом В обработке и Ответ;
BEGIN
/* Если тип комментария - Исходящее письмо (2),
а также текущий статус - В обработке (2), выставить статус Ответ (3) */
IF comment_type_id IN (2) AND "1001.object_cur_status_id" IN (2) THEN
INSERT INTO t_statuses (userid, object_id, status_type_id)
VALUES (pdb_userid, "1001.object_id", 3);
/* Если тип комментария - Исходящее письмо (2), а также текущий статус - Принято (1),
выставить cначала статус В обработке (2) и указать ответственное лицо, затем выставить статус Ответ (3) */
ELSIF comment_type_id IN (2) AND "1001.object_cur_status_id" IN (1) THEN
PERFORM f_start_processing (pdb_userid, "1001.object_id", "1001.object_cur_status_id", var_timestamp);
INSERT INTO t_statuses (userid, object_id, status_type_id)
VALUES (pdb_userid, "1001.object_id", 3);
/* Если тип комментария - Входящее письмо (1) и
текущий статус - Ответ (3), выставить статус В обработке (2) */
ELSIF comment_type_id IN (1) AND "1001.object_cur_status_id" IN (3) THEN
INSERT INTO t_statuses (userid, object_id, status_type_id)
VALUES (pdb_userid, "1001.object_id", 2);
END IF;
RETURN true;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;
9.1.6. Создание функции для загрузки писем
CREATE OR REPLACE FUNCTION f_email_in_to_comments(
pdb_userid integer,
pdb_email_uid integer,
pdb_email_date timestamp with time zone,
pdb_email_from text,
pdb_email_to text,
pdb_email_cc text,
pdb_email_subject text,
pdb_email_mime text,
pdb_email_body text)
RETURNS integer AS
$BODY$
DECLARE
entry_id integer; -- ID новой записи в таблицу comments
var_comment_type_id integer:= 1; -- Входящее письмо
var_comment_remark text; -- Примечание к комментарию
var_object_id integer; -- ID объекта
parsed_email_subject parsed_email_subject; -- Разобранная тема письма
var_comment_diag_mes text; -- Диагностическое сообщение, формируемое при анализе темы письма
var_object_cur_status_id integer; -- ID текущего статуса дела
temp_id integer;
BEGIN
/* Разбирает тему письма */
parsed_email_subject:= f_parse_subject(pdb_email_subject);
/* Формирует примечание к комментарию */
var_comment_remark:= COALESCE(pdb_email_body,'Тело письма не содержит текст.');
/* Присваевает номер объекта */
var_object_id:= parsed_email_subject.object_id;
/* Определяет диагностическое сообщение после разбора темы */
var_comment_diag_mes:= parsed_email_subject.diag_mes;
/* Номер объекта определен и объект с указанным в теме письма номером не закрыт */
IF var_object_id IS NOT NULL AND (SELECT dttmcl FROM t_objects WHERE object_id = var_object_id) IS NULL THEN
INSERT INTO t_comments(dttmcr, userid, object_id, comment_type_id, comment_text, comment_from, comment_to, comment_cc, comment_subject, comment_diag_mes)
VALUES(pdb_email_date, pdb_userid, var_object_id, var_comment_type_id, var_comment_remark, pdb_email_from, pdb_email_to, pdb_email_cc, pdb_email_subject, var_comment_diag_mes)
RETURNING comment_id INTO entry_id;
SELECT object_cur_status_id FROM t_objects WHERE object_id = var_object_id INTO var_object_cur_status_id;
PERFORM f_update_status (pdb_userid, var_object_id, var_object_cur_status_id, entry_id, var_comment_type_id);
/* Номер объекта не определен или объект с указанным в теме письма номером закрыт */
ELSE
INSERT INTO t_comments(dttmcr, userid, comment_type_id, comment_text, comment_from, comment_to, comment_cc, comment_subject, comment_diag_mes)
VALUES(pdb_email_date, pdb_userid, var_comment_type_id, var_comment_remark, pdb_email_from, pdb_email_to, pdb_email_cc, pdb_email_subject, var_comment_diag_mes)
RETURNING comment_id INTO entry_id;
END IF;
RETURN entry_id;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;
9.1.7. Создание функции для сохранения вложений
CREATE OR REPLACE FUNCTION f_save_files(
pdb_userid integer,
pdb_email_file bytea,
pdb_docum_file bytea,
f_email_in_to_comments integer,
r_comment_id integer)
RETURNS boolean AS
$BODY$
DECLARE
BEGIN
IF f_email_in_to_comments IS NOT NULL THEN
INSERT INTO t_files(userid, comment_id, comment_file)
VALUES (pdb_userid, f_email_in_to_comments, pdb_email_file);
ELSE
INSERT INTO t_files(userid, comment_id, comment_file)
VALUES (pdb_userid, r_comment_id, pdb_docum_file);
END IF;
RETURN true;
END
$BODY$
LANGUAGE plpgsql VOLATILE;
9.1.8. Создание функции для регистрации обращения на основании входящего письма
CREATE OR REPLACE FUNCTION f_register_object(
pdb_userid integer,
"1005.comment_id" integer,
"1005.client_id" integer,
"1005.object_to_id" integer,
"1005.object_remark" text)
RETURNS SETOF integer AS
$BODY$
DECLARE
r_object_id integer; -- ID объекта после добавления;
BEGIN
/* Выполняется при регистрации обращения на основании входящего электронного письма */
IF "1005.comment_id" IS NOT NULL THEN
/* Создает обращение */
INSERT INTO t_objects (userid, client_id, object_to_id, object_remark)
VALUES (pdb_userid, "1005.client_id", "1005.object_to_id", "1005.object_remark")
RETURNING object_id INTO r_object_id;
/* Прикрепляет письмо к созданному обращению */
UPDATE t_comments
SET object_id = r_object_id
WHERE comment_id = "1005.comment_id";
END IF;
/* Возвращает ID нового обращения */
RETURN QUERY
SELECT r_object_id;
END
$BODY$
LANGUAGE plpgsql VOLATILE;
9.1.9. Далее