4.1. SQL-queries

 

4.1.1. Before Starting

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

 

 

4.1.2. Creating Table to Store Customers Data

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

 

 

4.1.3. Creating View to Display Customers Data 

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;

 

 

4.1.4. Creating Table to Store Status Types 

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

 

 

4.1.5. Creating Table to Store Comment Types 

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

 

 

4.1.6. Adding Status Types Used in the System 

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

 

 

4.1.7. Adding Comment Types Used in the System 

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

 

 

4.1.8. Next

4.2. Creating Object 1301 Customer