10.1. SQL-queries

 

10.1.1. Before Starting

Before proceeding to actions described further, SQL-queries for this step should be performed.

 

 

10.1.2. Creating Function to Save Sent Message to a Database 

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; 

 

 

10.1.3. Creating a Function to Increase the Level of Quoted Text 

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;

 

 

10.1.4. Creating a Function that Forms the Body of the Message when Redirected 

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; 

 

 

10.1.5. Creating a Function that Forms the Body of a Message when Replying 

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; 

 

 

10.1.6. Next

10.2. Creating Action 1221 New Email