7.1. Requêtes SQL
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