9.1. Requêtes SQL
9.1.1. Before Starting
Before proceeding to actions described further, SQL-queries for this step should be performed.
9.1.2. Creating a Type for Storing Parsed Email Subject
CREATE TYPE parsed_email_subject AS
(subject_type_code integer,
object_id integer,
rest_email_subject text,
diag_mes text);
9.1.3. Creating a Function to Analyze Email Subject
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 code
subject_type_for_check text; -- Subject type code for check if it is integer
subject_code_is_present boolean; -- Subject type code is present in table "subject_types";
object_id integer; -- Object ID
object_ref_for_check text; -- Object number for check if it is integer
object_ref_is_present boolean; -- Object number is present in table "objects";
rest_email_subject text; -- Rest of the email subject
pos_left_bracket int; -- Left bracket position
pos_right_bracket int; -- Right bracket position
pos_number_sign int; -- # sign position
email_subject_length int; -- Email subject length
parsed_email_subject parsed_email_subject; -- Parsed email subject
diag_mes text; -- Diagnostic message
BEGIN
/* Getting the position of signs */
pos_left_bracket := position(left_bracket in email_subject); -- Left bracket position
pos_number_sign := position(number_sign in email_subject); -- # sign position
pos_right_bracket := position(right_bracket in email_subject); -- Right bracket position
email_subject_length := char_length(email_subject); -- Email subject length
/* Runs if all of the three signs are present */
IF pos_left_bracket <> 0 -- If left bracket is present
AND pos_right_bracket <> 0 -- and right bracket is present
AND pos_number_sign <> 0 -- and # sign is present
AND pos_number_sign BETWEEN pos_left_bracket AND pos_right_bracket -- In this order: left bracket, # sign, right bracket
THEN
/* Object number for check */
object_ref_for_check:=
trim(substring(email_subject from (pos_number_sign + 1 ) for (pos_right_bracket - pos_number_sign - 1)));
/* Checks if object number is present in table "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;
/* Runs if object number is integer and is present in table "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 - Object number is found';
/* Object number is not found */
ELSE rest_email_subject:= email_subject;
diag_mes:= '1 - Object number is not found';
END IF;
/* Unable to parse email subject */
ELSE rest_email_subject:= email_subject;
diag_mes:= '2 - Unable to parse email subject';
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. Creating a Function to Test for an Integer Value
CREATE OR REPLACE FUNCTION f_is_integer(text)
RETURNS boolean AS
$BODY$
SELECT $1 ~ '^[0-9]+$'
$BODY$
LANGUAGE sql VOLATILE;
9.1.5. Create a Function to Automatically Update Ticket Status based on the Type of Comment
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'); -- Is passed to function "f_start_processing" to create a 1 second gap between "In progress" and "Answered" statuses;
BEGIN
/* If comment type is Outgoing mail (2),
and current status is in Progress (2), change status to Answered (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);
/* If comment type is Outgoing mail (2), and currect status is New (1),
first change status to In Progress (2) and set responsible employee, then change status to Answered (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);
/* If comment type is Incoming mail (1) and
current status is Answered (3), change status to In Progress (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 a Function for Downloading Emails
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 of the new entry in table "comments"
var_comment_type_id integer:= 1; -- Incoming mail
var_comment_remark text; -- Comment remark
var_object_id integer; -- Object ID
parsed_email_subject parsed_email_subject; -- Parsed email subject
var_comment_diag_mes text; -- Diagnostic message, formed upon parsing mail subject
var_object_cur_status_id integer; -- Current status ID
temp_id integer;
BEGIN
/* Parses email subject */
parsed_email_subject:= f_parse_subject(pdb_email_subject);
/* Forms remark to comment */
var_comment_remark:= COALESCE(pdb_email_body,'Empty body.');
/* Assigns object ID */
var_object_id:= parsed_email_subject.object_id;
/* Assigns diagnostic message */
var_comment_diag_mes:= parsed_email_subject.diag_mes;
/* Object ID is found and object is not closed */
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);
/* Object ID is not found or object is closed */
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 a Function to Save Attachments
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 a Function for Creating Ticket based on an Incoming Email
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; -- Object ID after a new object is created;
BEGIN
/* Runs when a new object is created on the basis of new incoming letter */
IF "1005.comment_id" IS NOT NULL THEN
/* Creates new object */
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;
/* Appends letter to new object */
UPDATE t_comments
SET object_id = r_object_id
WHERE comment_id = "1005.comment_id";
END IF;
/* Returns ID of the new object */
RETURN QUERY
SELECT r_object_id;
END
$BODY$
LANGUAGE plpgsql VOLATILE;
9.1.9. Next