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. Далее

9.2. Создание объекта 1204 Входящие письма