TiHSVfQm

1DROP FUNCTION IF EXISTS public.efficiency_department(INTEGER, VARCHAR, VARCHAR);
2
3CREATE OR REPLACE FUNCTION public.efficiency_department(IN p_user_id INTEGER, IN p_date_start VARCHAR, IN p_date_end VARCHAR)
4    RETURNS TABLE(
5        department_id               INTEGER,
6        department_name             VARCHAR,
7        new                         BIGINT,
8        open                        BIGINT,
9        closed                      BIGINT,
10        pending                     BIGINT,
11        transfer                    BIGINT,
12        backlog                     BIGINT
13    ) AS
14$BODY$
15DECLARE
16    query                           TEXT := '';
17    departments                     INTEGER[];
18BEGIN
19    SELECT ARRAY(SELECT ud.department_id FROM user_departments(p_user_id) ud) INTO departments;
20    IF array_length(departments, 1) = 0 THEN
21        RAISE EXCEPTION 'El usuario no está asociado a ningún departamento'
22             USING HINT = 'Por favor modifique el usuario';
23    END IF;
24
25    query := '
26    WITH last_movement_without_timer AS(
27        SELECT
28            ttt.department_id,
29            ttt.ticket_id,
30            MAX(ttt.created_at) AS last_movement_without_timer
31        FROM tickets_time_travel ttt
32        WHERE ttt.department_id IS NOT NULL
33          AND ttt.created_at::DATE >= '''|| p_date_start ||'''::DATE
34          AND ttt.created_at::DATE <= '''|| p_date_end ||'''::DATE
35        GROUP BY ttt.department_id, ttt.ticket_id
36    ), last_movement_with_timer AS(
37        SELECT
38            ttt.department_id,
39            ttt.ticket_id,
40            MAX(ttt.created_at) AS last_movement_with_timer
41        FROM tickets_time_travel ttt
42        WHERE ttt.department_id IS NOT NULL
43          AND ttt.timer_id IS NOT NULL
44          AND ttt.created_at::DATE >= '''|| p_date_start ||'''::DATE
45          AND ttt.created_at::DATE <= '''|| p_date_end ||'''::DATE
46        GROUP BY ttt.department_id, ttt.ticket_id
47    ), all_new_tickets AS(
48        SELECT
49            t.department_id,
50            COUNT(t.id) AS new
51        FROM tickets t
52        WHERE t.department_id IS NOT NULL
53          AND t.created_at::DATE >= '''|| p_date_start ||'''::DATE
54          AND t.created_at::DATE <= '''|| p_date_end ||'''::DATE
55          GROUP BY t.department_id
56    ), all_open_tickets AS(
57        SELECT
58            lmwt.department_id,
59            COUNT(lmwt.*) AS open
60        FROM last_movement_with_timer lmwt
61        GROUP BY lmwt.department_id
62    ), all_closed_tickets AS(
63        SELECT
64            ttt.department_id,
65            COUNT(ts.ticket_state_type_id) AS closed
66        FROM last_movement_without_timer lmwot
67        INNER JOIN tickets_time_travel ttt ON lmwot.department_id = ttt.department_id AND lmwot.last_movement_without_timer = ttt.created_at
68        LEFT JOIN ticket_states ts ON ttt.ticket_state_id = ts.id
69        WHERE ts.ticket_state_type_id = 3
70        GROUP BY ttt.department_id
71    ), last_movement_with_timer_before AS(
72        SELECT
73            ttt.department_id,
74            ttt.ticket_id,
75            MAX(ttt.created_at)
76        FROM tickets_time_travel ttt
77        LEFT JOIN last_movement_with_timer lmwt ON ttt.department_id = lmwt.department_id AND ttt.ticket_id = lmwt.ticket_id
78        WHERE ttt.created_at::DATE < '''|| p_date_start ||'''::DATE
79          AND ttt.timer_id IS NULL
80          AND lmwt.ticket_id IS NULL
81        GROUP BY ttt.department_id, ttt.ticket_id
82    ), all_pending_tickets AS(
83        SELECT
84            lmwtb.department_id,
85            COUNT(lmwtb.*) AS pending
86        FROM last_movement_with_timer_before lmwtb
87        GROUP BY lmwtb.department_id
88    ), all_transfer_tickets AS(
89        SELECT
90            ttt.previous_department_id AS department_id,
91            COUNT(ttt.*) AS transfer
92        FROM tickets_time_travel ttt
93        WHERE ttt.previous_department_id IS NOT NULL
94          AND ttt.created_at::DATE >= '''|| p_date_start ||'''::DATE
95          AND ttt.created_at::DATE <= '''|| p_date_end ||'''::DATE
96        GROUP BY ttt.previous_department_id
97    )
98    SELECT
99        d.id AS department_id,
100        d.department_name,
101        COALESCE(ant.new, 0) AS new,
102        COALESCE(aot.open, 0) AS open,
103        COALESCE(act.closed, 0) AS closed,
104        COALESCE(apt.pending, 0) AS pending,
105        COALESCE(att.transfer, 0) AS pending,
106        (COALESCE(aot.open, 0) + COALESCE(apt.pending, 0) - COALESCE(act.closed, 0)) AS backlog
107    FROM departments d
108    LEFT JOIN all_new_tickets ant ON d.id = ant.department_id
109    LEFT JOIN all_open_tickets aot ON d.id = aot.department_id
110    LEFT JOIN all_closed_tickets act ON d.id = act.department_id
111    LEFT JOIN all_pending_tickets apt ON d.id = apt.department_id
112    LEFT JOIN all_transfer_tickets att ON d.id = att.department_id
113    WHERE d.id IN('|| array_to_string(departments, ',') ||')';
114
115    RETURN QUERY EXECUTE query;
116END;
117$BODY$ LANGUAGE plpgsql IMMUTABLE STRICT SECURITY DEFINER COST 10 ROWS 1000;