9.1. SQL-queries

 

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 employeethen 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

9.2. Creating Object 1204 Incoming mail