Toutes les Requêtes SQL

CREATE DATABASE support; -- NOTICE! first create a database, then connect to created database and run all of the SQL-requests below.

CREATE TABLE t_users

(

user_id serial NOT NULL, -- Row ID

dttmcr timestamp with time zone NOT NULL DEFAULT now(), -- Date and time when the row was created

dttmup timestamp with time zone, -- Date and time when the row was updated

dttmcl timestamp with time zone, -- Date and time when the row was closed

userid integer, -- ID of the user who created row

user_name text, -- User first name

user_surname text, -- User last name

user_login text, -- Login

user_password text, -- Password

user_signature text, -- User signature

CONSTRAINT pk_t_users_user_id PRIMARY KEY (user_id)

);

COMMENT ON TABLE t_users

IS 'Keeps users data';

COMMENT ON COLUMN t_users.user_id IS 'Row ID';

COMMENT ON COLUMN t_users.dttmcr IS 'Date and time when the row was created';

COMMENT ON COLUMN t_users.dttmup IS 'Date and time when the row was updated';

COMMENT ON COLUMN t_users.dttmcl IS 'Date and time when the row was closed';

COMMENT ON COLUMN t_users.userid IS 'ID of the user who created row';

COMMENT ON COLUMN t_users.user_name IS 'User first name';

COMMENT ON COLUMN t_users.user_surname IS 'User last name';

COMMENT ON COLUMN t_users.user_login IS 'Login';

COMMENT ON COLUMN t_users.user_password IS 'Password';

COMMENT ON COLUMN t_users.user_signature IS 'User signature';

CREATE OR REPLACE VIEW vw_users AS

SELECT t_users.user_id,

t_users.dttmcr,

t_users.dttmup,

t_users.dttmcl,

t_users.userid,

t_users.user_name,

t_users.user_surname,

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

t_users.user_login,

t_users.user_password,

t_users.user_signature

FROM t_users;

CREATE TABLE t_clients

(

client_id serial NOT NULL, -- Row ID

dttmcr timestamp with time zone NOT NULL DEFAULT now(), -- Date and time when the row was created

dttmup timestamp with time zone, -- Date and time when the row was updated

dttmcl timestamp with time zone, -- Date and time when the row was closed

userid integer, -- ID of the user who created row

client_name text, -- Customer first name

client_surname text, -- Customer last name

CONSTRAINT pk_t_clients_client_id PRIMARY KEY (client_id)

);

COMMENT ON TABLE t_clients

IS 'Keeps customer data';

COMMENT ON COLUMN t_clients.client_id IS 'Row ID';

COMMENT ON COLUMN t_clients.dttmcr IS 'Date and time when the row was created';

COMMENT ON COLUMN t_clients.dttmup IS 'Date and time when the row was updated';

COMMENT ON COLUMN t_clients.dttmcl IS 'Date and time when the row was closed';

COMMENT ON COLUMN t_clients.userid IS 'ID of the user who created row';

COMMENT ON COLUMN t_clients.client_name IS 'Customer first name';

COMMENT ON COLUMN t_clients.client_surname IS 'Customer last name';

CREATE OR REPLACE VIEW vw_clients AS

SELECT t_clients.client_id,

t_clients.dttmcr,

t_clients.dttmup,

t_clients.dttmcl,

t_clients.userid,

t_clients.client_name,

t_clients.client_surname,

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

FROM t_clients;

CREATE TABLE t_status_types

(

status_type_id serial NOT NULL, -- Row ID

dttmcr timestamp with time zone NOT NULL DEFAULT now(), -- Date and time when the row was created

dttmup timestamp with time zone, -- Date and time when the row was updated

dttmcl timestamp with time zone, -- Date and time when the row was closed

userid integer, -- ID of the user who created row

status_type_name text, -- Status type

CONSTRAINT pk_t_status_types_status_type_id PRIMARY KEY (status_type_id)

);

COMMENT ON TABLE t_status_types

IS 'Keep status types data';

COMMENT ON COLUMN t_status_types.status_type_id IS 'Row ID';

COMMENT ON COLUMN t_status_types.dttmcr IS 'Date and time when the row was created';

COMMENT ON COLUMN t_status_types.dttmup IS 'Date and time when the row was updated';

COMMENT ON COLUMN t_status_types.dttmcl IS 'Date and time when the row was closed';

COMMENT ON COLUMN t_status_types.userid IS 'ID of the user who created row';

COMMENT ON COLUMN t_status_types.status_type_name IS 'Status type';

CREATE TABLE t_comment_types

(

comment_type_id serial NOT NULL, -- Row ID

dttmcr timestamp with time zone NOT NULL DEFAULT now(), -- Date and time when the row was created

dttmup timestamp with time zone, -- Date and time when the row was updated

dttmcl timestamp with time zone, -- Date and time when the row was closed

userid integer, -- ID of the user who created row

comment_type_name text, -- Comment type name

CONSTRAINT pk_t_comment_types_comment_type_id PRIMARY KEY (comment_type_id)

);

COMMENT ON TABLE t_comment_types

IS 'Keeps comment types';

COMMENT ON COLUMN t_comment_types.comment_type_id IS 'ID записи';

COMMENT ON COLUMN t_comment_types.dttmcr IS 'Date and time when the row was created';

COMMENT ON COLUMN t_comment_types.dttmup IS 'Date and time when the row was updated';

COMMENT ON COLUMN t_comment_types.dttmcl IS 'Date and time when the row was closed';

COMMENT ON COLUMN t_comment_types.userid IS 'ID of the user who created row';

COMMENT ON COLUMN t_comment_types.comment_type_name IS 'Comment type name';

INSERT INTO t_status_types (dttmcr, dttmup, userid, status_type_name) VALUES

(now(), now(), 1, 'New'),

(now(), now(), 1, 'In progress'),

(now(), now(), 1, 'Answered'),

(now(), now(), 1, 'Closed');

INSERT INTO t_comment_types (dttmcr, dttmup, userid, comment_type_name) VALUES

(now(), now(), 1, 'Incoming mail'),

(now(), now(), 1, 'Outgoing mail'),

(now(), now(), 1, 'Comment');

CREATE SEQUENCE t_objects_object_ref_nr_seq

INCREMENT 1

MINVALUE 1

MAXVALUE 9223372036854775807

START 300001

CACHE 1;

CREATE TABLE t_objects

(

object_id serial NOT NULL, -- Row ID

dttmcr timestamp with time zone NOT NULL DEFAULT now(), -- Date and time when the row was created

dttmup timestamp with time zone, -- Date and time when the row was updated

dttmcl timestamp with time zone, -- Date and time when the row was closed

userid integer, -- ID of the user who created row

object_ref_nr integer DEFAULT nextval('t_objects_object_ref_nr_seq'::regclass), -- Ticket number

object_cur_status_id integer, -- Current status ID - this is updated with trigger when a new status is inserted into t_statuses table

client_id integer, -- Customer ID - FK t_clients.client_id

object_to_id integer, -- Employee ID - FK t_users.user_id

object_remark text, -- Ticket comment

CONSTRAINT pk_t_objects_object_id PRIMARY KEY (object_id)

);

COMMENT ON TABLE t_objects

IS 'Keeps tickets';

COMMENT ON COLUMN t_objects.object_id IS 'Row ID';

COMMENT ON COLUMN t_objects.dttmcr IS 'Date and time when the row was created';

COMMENT ON COLUMN t_objects.dttmup IS 'Date and time when the row was updated';

COMMENT ON COLUMN t_objects.dttmcl IS 'Date and time when the row was closed';

COMMENT ON COLUMN t_objects.userid IS 'ID of the user who created row';

COMMENT ON COLUMN t_objects.object_ref_nr IS 'Ticket number';

COMMENT ON COLUMN t_objects.object_cur_status_id IS 'Current status ID - this is updated with trigger when a new status is inserted into t_statuses table';

COMMENT ON COLUMN t_objects.client_id IS 'Customer ID - FK t_clients.client_id';

COMMENT ON COLUMN t_objects.object_to_id IS 'Employee ID - FK t_users.user_id';

COMMENT ON COLUMN t_objects.object_remark IS 'Ticket comment';

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;

CREATE TABLE t_statuses

(

status_id serial NOT NULL, -- Row ID

dttmcr timestamp with time zone NOT NULL DEFAULT now(), -- Date and time when the row was created

dttmup timestamp with time zone, -- Date and time when the row was updated

dttmcl timestamp with time zone, -- Date and time when the row was closed

userid integer, -- ID of the user who created row

object_id integer NOT NULL, -- Ticket ID - FK t_objects.object_id

status_type_id integer NOT NULL, -- Status type ID

CONSTRAINT pk_t_statuses_status_id PRIMARY KEY (status_id)

);

COMMENT ON TABLE t_statuses

IS 'Keeps ticket statuses';

COMMENT ON COLUMN t_statuses.status_id IS 'Row ID';

COMMENT ON COLUMN t_statuses.dttmcr IS 'Date and time when the row was created';

COMMENT ON COLUMN t_statuses.dttmup IS 'Date and time when the row was updated';

COMMENT ON COLUMN t_statuses.dttmcl IS 'Date and time when the row was closed';

COMMENT ON COLUMN t_statuses.userid IS 'ID of the user who created row';

COMMENT ON COLUMN t_statuses.object_id IS 'Ticket ID - FK t_objects.object_id';

COMMENT ON COLUMN t_statuses.status_type_id IS 'Status type ID';

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);

CREATE TABLE t_comments

(

comment_id serial NOT NULL, -- Row ID

dttmcr timestamp with time zone NOT NULL DEFAULT now(), -- Date and time when the row was created

dttmup timestamp with time zone, -- Date and time when the row was updated

dttmcl timestamp with time zone, -- Date and time when the row was closed

userid integer, -- ID of the user who created row

object_id integer, -- Ticket ID - FK t_objects.object_id

comment_type_id integer NOT NULL, -- Comment type ID - FK t_comment_type.comment_type_id

comment_from text, -- From

comment_to text, -- To

comment_cc text, -- Cc

comment_bcc text, -- Bcc

comment_subject text, -- Subject

comment_text text, -- Body

comment_diag_mes text, -- System message which is generated upon subject analysis when a message is uploaded to the system

CONSTRAINT pk_t_comments_comment_id PRIMARY KEY (comment_id)

);

COMMENT ON TABLE t_comments

IS 'Keeps ticket comments';

COMMENT ON COLUMN t_comments.comment_id IS 'Row ID';

COMMENT ON COLUMN t_comments.dttmcr IS 'Date and time when the row was created';

COMMENT ON COLUMN t_comments.dttmup IS 'Date and time when the row was updated';

COMMENT ON COLUMN t_comments.dttmcl IS 'Date and time when the row was closed';

COMMENT ON COLUMN t_comments.userid IS 'ID of the user who created row';

COMMENT ON COLUMN t_comments.object_id IS 'Ticket ID - FK t_objects.object_id';

COMMENT ON COLUMN t_comments.comment_type_id IS 'Comment type ID - FK t_comment_type.comment_type_id';

COMMENT ON COLUMN t_comments.comment_from IS 'From';

COMMENT ON COLUMN t_comments.comment_to IS 'To';

COMMENT ON COLUMN t_comments.comment_cc IS 'Cc';

COMMENT ON COLUMN t_comments.comment_bcc IS 'Bcc';

COMMENT ON COLUMN t_comments.comment_subject IS 'Subject';

COMMENT ON COLUMN t_comments.comment_text IS 'Body';

COMMENT ON COLUMN t_comments.comment_diag_mes IS 'System message which is generated upon subject analysis when a message is uploaded to the system';

CREATE TABLE t_files

(

file_id serial NOT NULL, -- Row ID

dttmcr timestamp with time zone NOT NULL DEFAULT now(), -- Date and time when the row was created

dttmup timestamp with time zone, -- Date and time when the row was updated

dttmcl timestamp with time zone, -- Date and time when the row was closed

userid integer, -- ID of the user who created row

comment_id integer, -- Comment ID - FK t_comments.comment_id

comment_file bytea, -- File

CONSTRAINT pk_t_files_file_id PRIMARY KEY (file_id)

);

COMMENT ON TABLE t_files

IS 'Keeps files';

COMMENT ON COLUMN t_files.file_id IS 'Row ID';

COMMENT ON COLUMN t_files.dttmcr IS 'Date and time when the row was created';

COMMENT ON COLUMN t_files.dttmup IS 'Date and time when the row was updated';

COMMENT ON COLUMN t_files.dttmcl IS 'Date and time when the row was closed';

COMMENT ON COLUMN t_files.userid IS 'ID of the user who created rowь';

COMMENT ON COLUMN t_files.comment_id IS 'Comment ID - FK t_comments.comment_id';

COMMENT ON COLUMN t_files.comment_file IS 'File';

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 'Yes'::text

END AS comment_file_present

FROM t_comments c

LEFT JOIN t_comment_types ct USING (comment_type_id);

CREATE OR REPLACE FUNCTION trig_t_objects_i_new_status()

RETURNS trigger AS

$BODY$

DECLARE

var_status_id integer := 1; -- ID of the "New" status;

BEGIN

INSERT INTO t_statuses (userid, object_id, status_type_id)

VALUES (NEW.userid, NEW.object_id, var_status_id);

RETURN NEW;

END;

$BODY$

LANGUAGE plpgsql VOLATILE;

CREATE OR REPLACE FUNCTION trig_t_statuses_iu_status()

RETURNS trigger AS

$BODY$

BEGIN

UPDATE t_objects

SET object_cur_status_id = NEW.status_type_id

WHERE t_objects.object_id = NEW.object_id;

RETURN NEW;

END;

$BODY$

LANGUAGE plpgsql VOLATILE;

CREATE TRIGGER trig_t_objects_i_new_status

AFTER INSERT

ON t_objects

FOR EACH ROW

EXECUTE PROCEDURE trig_t_objects_i_new_status();

CREATE TRIGGER trig_t_statuses_iu_status

AFTER INSERT

ON t_statuses

FOR EACH ROW

EXECUTE PROCEDURE trig_t_statuses_iu_status();

CREATE OR REPLACE VIEW vw_latest_comments AS

SELECT c.comment_id,

c.dttmcr,

c.dttmup,

c.dttmcl,

c.userid,

o.object_ref_nr,

st.status_type_name,

ct.comment_type_name,

c.comment_from,

c.comment_text,

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 'Yes'::text

END AS comment_file_present

FROM t_comments c

LEFT JOIN t_comment_types ct USING (comment_type_id)

LEFT JOIN t_objects o USING (object_id)

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

WHERE o.object_id IS NOT NULL AND o.dttmcl IS NULL AND c.dttmcl IS NULL

ORDER BY c.dttmcr DESC;

CREATE FUNCTION f_start_processing(

pdb_userid integer,

"1002.object_id" integer,

"1002.object_cur_status_id" integer,

var_timestamp timestamp with time zone)

RETURNS integer AS

$BODY$

DECLARE

var_status_id integer;

var_status_type_id integer:= 2; -- ID of the "In progress" status

BEGIN

/* Setting responsible employee */

UPDATE t_objects

SET object_to_id = pdb_userid

WHERE object_id = "1002.object_id";

/* If current status is "New", change to "In progress" */

IF "1002.object_cur_status_id" = 1 THEN

/* If time is passed to function, insert values + time */

IF var_timestamp IS NOT NULL THEN

INSERT INTO t_statuses (dttmcr, userid, object_id, status_type_id)

VALUES (var_timestamp, pdb_userid, "1002.object_id", var_status_type_id)

RETURNING status_id INTO var_status_id;

ELSE

/* If no time is passed, just insert values */

INSERT INTO t_statuses (userid, object_id, status_type_id)

VALUES (pdb_userid, "1002.object_id", var_status_type_id)

RETURNING status_id INTO var_status_id;

END IF;

/* Return row ID from support.objects */

RETURN var_status_id;

END;

$BODY$

LANGUAGE plpgsql VOLATILE;

CREATE OR REPLACE FUNCTION f_get_filename(_filename bytea)

RETURNS text AS

$BODY$

declare

len integer;

begin

len := "position"(_filename, E'\\000'::bytea)-1;

if len > 0 then

return convert_from("substring"(_filename, 1, len ), 'WIN1251');

end if;

return NULL::text;

end;

$BODY$

LANGUAGE plpgsql VOLATILE;

CREATE OR REPLACE VIEW vw_files AS

SELECT t_files.file_id,

t_files.dttmcr,

t_files.dttmup,

t_files.dttmcl,

t_files.userid,

t_files.comment_id,

f_get_filename(t_files.comment_file) AS filename

FROM t_files;

CREATE OR REPLACE FUNCTION f_get_file("1210.file_id" integer)

RETURNS SETOF bytea AS

$BODY$

BEGIN

RETURN QUERY

SELECT comment_file

FROM t_files

WHERE file_id = $1;

END;

$BODY$

LANGUAGE plpgsql VOLATILE;

CREATE OR REPLACE FUNCTION f_upload_file(

pdb_userid integer,

"1202.comment_id" integer,

pdb_file_data bytea)

RETURNS boolean AS

$BODY$

BEGIN

INSERT INTO t_files (userid, comment_id, comment_file)

VALUES (pdb_userid, "1202.comment_id", pdb_file_data);

RETURN true;

END

$BODY$

LANGUAGE plpgsql VOLATILE;

CREATE OR REPLACE FUNCTION f_close_file("1210.file_id" integer)

RETURNS boolean AS

$BODY$

BEGIN

UPDATE t_files

SET dttmcl = now() WHERE t_files.file_id = "1210.file_id";

RETURN true;

END

$BODY$

LANGUAGE plpgsql VOLATILE;

CREATE TYPE parsed_email_subject AS

(subject_type_code integer,

object_id integer,

rest_email_subject text,

diag_mes text);

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;

CREATE OR REPLACE FUNCTION f_is_integer(text)

RETURNS boolean AS

$BODY$

SELECT $1 ~ '^[0-9]+$'

$BODY$

LANGUAGE sql VOLATILE;

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;

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;

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;

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;

CREATE OR REPLACE FUNCTION f_email_out_to_comments(

IN pdb_userid integer,

IN pdb_email_uid integer,

IN pdb_email_date timestamp with time zone,

IN pdb_email_from text,

IN pdb_email_to text,

IN pdb_email_cc text,

IN pdb_email_bcc text,

IN pdb_email_subject text,

IN pdb_email_body text,

IN object_id integer,

IN f_register_object integer,

IN "1001.object_id" integer)

RETURNS TABLE(r_object_id integer, r_comment_id integer, r_comment_type_id integer) AS

$BODY$

DECLARE

var_comment_type_id integer:= 2; -- Outgoing mail;

var_comment_remark text; -- Comment

var_object_id integer; -- Ticket ID;

BEGIN

var_object_id := COALESCE(object_id, f_register_object, "1001.object_id");

/* Body */

var_comment_remark:= COALESCE(pdb_email_body,'Body is empty.');

INSERT INTO t_comments(dttmcr, userid, object_id, comment_type_id, comment_text, comment_from, comment_to, comment_cc, comment_subject, comment_bcc)

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, pdb_email_bcc)

RETURNING comment_id INTO r_comment_id;

/* Comment type ID to be returned */

r_comment_type_id := var_comment_type_id;

/* Ticket ID to be returned */

r_object_id := var_object_id;

RETURN QUERY

SELECT

r_object_id,

r_comment_id,

r_comment_type_id;

END;

$BODY$

LANGUAGE plpgsql VOLATILE;

CREATE OR REPLACE FUNCTION f_increase_quote(orig_text text)

RETURNS text AS

$BODY$

DECLARE

result text;

BEGIN

SELECT '> '||replace(orig_text, chr(10), chr(10)||'> ')

INTO result;

RETURN result;

END

$BODY$

LANGUAGE plpgsql VOLATILE;

CREATE OR REPLACE FUNCTION f_body_forward(

var_text text,

pdb_userid integer,

var_comment_id integer)

RETURNS text AS

$BODY$

DECLARE

var_date timestamp; -- Date of original message

var_time time; -- Time

var_from text; -- Sender of original message

var_theme text; -- Original message subject

var_to text; -- Message receiver

var_orig_text text; -- Original message text

var_signature text; -- Signature

var_new_text text; -- New text

var_result text; -- Result

BEGIN

/* Original message date */

var_date := (SELECT dttmcr FROM t_comments WHERE comment_id = var_comment_id);

/* Original message time */

var_time := (SELECT dttmcr::time FROM t_comments WHERE comment_id = var_comment_id);

/* Sender of original message */

var_from := (SELECT comment_from FROM t_comments WHERE comment_id = var_comment_id);

/* Original message subject */

var_theme := (SELECT comment_subject FROM t_comments WHERE comment_id = var_comment_id);

/* Message receiver */

var_to := (SELECT comment_to FROM t_comments WHERE comment_id = var_comment_id);

/* Original message text */

var_orig_text := (SELECT comment_text FROM t_comments WHERE comment_id = var_comment_id);

/* Signature */

var_signature := (SELECT user_signature FROM t_users WHERE user_id = pdb_userid);

SELECT

'Begin forwarded message:'||

chr(10)||chr(10)||

'From: '||COALESCE(var_from, 'sender unknown')||chr(10)||

'Subject: '||COALESCE(var_theme, '-')||chr(10)||

'Date: '||COALESCE(var_date::text, 'date unknown')||chr(10)||

'To: '||COALESCE(var_to, '-')||

chr(10)||chr(10)||

COALESCE(var_orig_text, 'Forwarded message body is empty.')

INTO var_new_text;

SELECT

COALESCE(var_text, '')||

chr(10)||chr(10)||chr(10)||

COALESCE(var_signature, '')||

chr(10)||chr(10)||chr(10)||

f_increase_quote(var_new_text)

INTO var_result;

RETURN var_result;

END

$BODY$

LANGUAGE plpgsql VOLATILE;

CREATE OR REPLACE FUNCTION f_body_reply(

var_text text,

pdb_userid integer,

var_comment_id integer)

RETURNS text AS

$BODY$

DECLARE

var_date date; -- Date of original message

var_time time; -- Time

var_from text; -- Sender of original message

var_orig_text text; -- Original message text

var_signature text; -- Signature

var_new_text text; -- New text

var_result text; -- Result

BEGIN

/* Date of original message */

var_date := (SELECT dttmcr::date FROM t_comments WHERE comment_id = var_comment_id);

/* Original message time */

var_time := (SELECT dttmcr::time FROM t_comments WHERE comment_id = var_comment_id);

/* Sender of original message */

var_from := (SELECT comment_from FROM t_comments WHERE comment_id = var_comment_id);

/* Original message text */

var_orig_text := (SELECT comment_text FROM t_comments WHERE comment_id = var_comment_id);

/* Signature */

var_signature := (SELECT user_signature FROM t_users WHERE user_id = pdb_userid);

/* Result */

SELECT

COALESCE(var_date::text, 'date unknown')||', at '||COALESCE(var_time::text, 'time unknown')||', '||COALESCE(var_from, 'sender unknown')||' wrote:'||

chr(10)||chr(10)||

COALESCE(var_orig_text, 'Original message body is empty.')

INTO var_new_text;

SELECT

COALESCE(var_text, '')||

chr(10)||chr(10)||chr(10)||

COALESCE(var_signature, '')||

chr(10)||chr(10)||chr(10)||

f_increase_quote(var_new_text)

INTO var_result;

RETURN var_result;

END

$BODY$

LANGUAGE plpgsql VOLATILE;