7.1. SQL-queries

 

7.1.1. Before Starting

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

 

 

7.1.2. Creating a View for Displaying the Latest Comments

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;

 

 

7.1.3. Creating a Function for Taking Tickets Into Processing 

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;

END IF;          

 

/* Return row ID from support.objects */

RETURN var_status_id;

 

END;

$BODY$

LANGUAGE plpgsql VOLATILE;

 

 

7.1.4. Next

7.2. Creating Object 1003 Closed Tickets